/**
* Генерация процедуры добавления/редактирования записей в таблицу
* @param string $table
* @param string $action - Название действия процедуры INSERT или UPDATE
*/
protected function __storedProcedureGeneration($table, $action)
{
$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);
if ($action == 'insert') {
$proc_query = 'CREATE DEFINER=`' . PdoWrap::getCurrentUserName() . '`@`localhost` 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::getCurrentUserName() . '`@`localhost` 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);
}
}
