/**
* Генератор INSERT/UPDATE/DELETE процедур для таблицы
* @param $action
*/
private function __storedProcedureGeneration($action)
{
$proc_query = '';
$table_name = $this->getTable();
$prefix = $this->getProcPrefix();
$query = 'CALL show_table_columns(:tableName)';
$params = array(':tableName' => $table_name);
$fields = PdoWrap::select($query, $params);
$fields_count = count($fields);
if ($action == 'insert') {
$proc_query = 'CREATE DEFINER=`' . PdoWrap::getCurrentDbName() . '`@`' . PdoWrap::getCurrentDbHost() . '` PROCEDURE `' . $prefix . '_' . $action . '`(';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
$proc_query .= '
IN `field_' . $fields[$i]['Field'] . '` ' . Text::strtoup(preg_replace('/unsigned/', '', $fields[$i]['Type'])) . ',';
}
}
$proc_query = substr($proc_query, 0, -1);
$proc_query .= '
)
BEGIN
SET';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
$proc_query .= '
@p' . $i . ' = field_' . $fields[$i]['Field'] . ',';
}
}
$proc_query = substr($proc_query, 0, -1) . ';';
$proc_query .= '
INSERT INTO ' . $table_name . ' (';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
$proc_query .= '`' . $fields[$i]['Field'] . '`,';
}
}
$proc_query = substr($proc_query, 0, -1) . ')';
$proc_query .= '
VALUES (';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
if ($fields[$i]['Field'] == 'date_add') {
$proc_query .= 'UNIX_TIMESTAMP(),';
} else {
$proc_query .= '@p' . $i . ',';
}
}
}
$proc_query = substr($proc_query, 0, -1) . ');';
$proc_query .= '
SELECT last_insert_id() AS `id`;
END;';
} elseif ($action == 'update') {
$proc_query = 'CREATE DEFINER=`' . PdoWrap::getCurrentDbName() . '`@`' . PdoWrap::getCurrentDbHost() . '` PROCEDURE `' . $prefix . '_' . $action . '` (';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_add') {
$proc_query .= '
IN `field_' . $fields[$i]['Field'] . '` ' . Text::strtoup(preg_replace('/unsigned/', '', $fields[$i]['Type'])) . ',';
}
}
$proc_query = substr($proc_query, 0, -1);
$proc_query .= '
)
BEGIN
SET';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_add') {
$proc_query .= '
@p' . $i . ' = field_' . $fields[$i]['Field'] . ',';
}
}
$proc_query = substr($proc_query, 0, -1) . ';';
$proc_query .= '
UPDATE `' . $table_name . '` SET';
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'user_add') {
if ($fields[$i]['Field'] == 'date_edit') {
$proc_query .= '
`' . $fields[$i]['Field'] . '` = UNIX_TIMESTAMP(),';
} else {
$proc_query .= '
`' . $fields[$i]['Field'] . '` = @p' . $i . ',';
}
} elseif ($fields[$i]['Extra'] == 'auto_increment') {
$update_id = '@p' . $i;
}
}
$proc_query = substr($proc_query, 0, -1);
$proc_query .= '
WHERE `id` = ' . $update_id . ';
END;';
}
if (!empty($proc_query)) {
PdoWrap::execute($proc_query);
}
}
/**
* Генерация процедуры поиска
* @param $table
*/
protected function __generateSearchStoredProc($table)
{
$proc_query = '';
$table_name = $this->__getPrefixTableName($table);
$prefix = $table;
$query = 'CALL get_table_columns(:tableName)';
$params = array(':tableName' => $table_name);
$fields = PdoWrap::select($query, $params);
$fields_count = count($fields);
$proc_query = "CREATE DEFINER=`" . PdoWrap::getCurrentDbName() . "`@`localhost` PROCEDURE `search_by_" . $prefix . "`(";
$proc_query .= "\n IN group_id INT (6),\n IN sort CHAR(20),\n IN sort_type CHAR(4),\n IN table_limit INT(4),\n IN page_num INT(6),\n IN search_query TEXT,\n IN active_field TINYINT(1)\n )\n DETERMINISTIC\n COMMENT 'Get table list with sort and page navigation'\n BEGIN\n\n SET @p1 = table_limit*(page_num-1),\n @p2 = table_limit,\n @p3 = search_query,\n @active = '',\n @group_value = '';\n ";
for ($i = 0; $i < $fields_count; $i++) {
if ($fields[$i]['Field'] == 'group_id') {
$proc_query .= "\n IF group_id > 0 THEN SET @group_value = CONCAT(' AND `c`.`group_id` = ', group_id); END IF;\n ";
}
if ($fields[$i]['Field'] == 'active') {
$proc_query .= "\n IF active_field = 0 THEN SET @active = ' AND `c`.`active` = 0'; ELSEIF active_field = 1 THEN SET @active = ' AND `c`.`active` = 1'; END IF;\n ";
}
}
$proc_query .= "\n SET @count_query = CONCAT('\n SELECT\n COUNT(*) INTO @rows_count\n FROM\n `" . $this->__getPrefixTableName($table) . "` `c`\n WHERE\n (";
for ($i = 0; $i < $fields_count; $i++) {
$proc_query .= "\n `" . $fields[$i]['Field'] . "` LIKE ? " . ($i + 1 == $fields_count ? "" : "OR");
}
$proc_query .= "\n )\n ', @active, '\n ', @group_value, '\n ');\n\n PREPARE `count_statement` FROM @count_query;\n EXECUTE `count_statement` USING ";
for ($i = 0; $i < $fields_count; $i++) {
$proc_query .= "@p3" . ($i + 1 == $fields_count ? ";" : ", ");
}
$proc_query .= "\n SET @count := @rows_count;\n\n # get sorting field\n IF sort = '' THEN\n SET @sorting := ' `c`.`date_add` DESC, `c`.`name` ASC';\n ELSE\n SET @sorting := CONCAT('`c`.`', sort,'` ', sort_type);\n END IF;\n\n SET @query = CONCAT('\n SELECT\n `c`.*,\n IF (`c`.`date_add` = 0, \"\", CONCAT(FROM_UNIXTIME(`c`.`date_add`, \"%d.%m.%Y %H:%i\"), \" \", `u`.`family`)) AS `date_add`,\n IF (`c`.`date_edit` = 0, \"\", CONCAT(FROM_UNIXTIME(`c`.`date_edit`, \"%d.%m.%Y %H:%i\"), \" \", `u2`.`family`)) AS `date_edit`\n FROM\n `" . $this->__getPrefixTableName($table) . "` `c`\n LEFT JOIN\n `cms_users` `u`\n ON\n `c`.`user_add` = `u`.`id`\n LEFT JOIN\n `cms_users` `u2`\n ON\n `c`.`user_edit` = `u2`.`id`\n WHERE\n (\n ";
for ($i = 0; $i < $fields_count; $i++) {
$proc_query .= "\n `c`.`" . $fields[$i]['Field'] . "` LIKE ? " . ($i + 1 == $fields_count ? "" : "OR");
}
$proc_query .= "\n )\n ', @active, '\n ', @group_value, '\n ORDER BY ', @sorting, '\n LIMIT ?, ?\n ');\n\n PREPARE `statement` FROM @query;\n EXECUTE `statement` USING ";
for ($i = 0; $i < $fields_count; $i++) {
$proc_query .= "@p3, ";
}
$proc_query .= "@p1, @p2;";
$proc_query .= "\n END;";
if (!empty($proc_query)) {
PdoWrap::execute($proc_query);
}
}
