public static function exec($sql, $binds = array())
{
// make sure we have a connection
if (is_null(static::$dbh)) {
static::connect();
}
// check binds
if (!is_array($binds)) {
$binds = array($binds);
}
// prepare
$sth = static::$dbh->prepare($sql);
// get results
$result = $sth->execute($binds);
// update affected rows
static::$affected_rows = $sth->rowCount();
// return result
return $result;
}
/**
* Manipulatig database records with centralized logging of previous value
* @global array $user
* @param string $table Database table ID
* @param string $task Task: INSERT, UPDATE, DELETE
* @param array $new New values (array of modified columns)
* @param ID $id ID of entity (optional), $new can contain it
* @return type ID of manuipulated row
*/
public static function save($table, $task, $new, $id = "")
{
$key = static::keyName($table);
if ($id == "" && isset($new[$key])) {
$id = $new[$key];
}
$old = static::get($table, $id);
static::$db->query("START TRANSACTION;");
list($nextEventID) = static::$db->query("SELECT MAX(logEventID)+1 FROM Log")->fetch_row();
switch ($task) {
case "INSERT_UPDATE":
$insert_update = true;
case "INSERT":
$query = "INSERT INTO `{$table}` SET ";
$query_set = "";
foreach ($new as $k => $v) {
//Language INDEPENDENT columns goes into the main table
if (!preg_match("/(?<col>[^_]*)_(?<lang>[A-Z][A-Z])/", $k, $matches)) {
$query_set .= "{$k}='" . static::$db->real_escape_string($v) . "', ";
}
}
$query_set .= " opID='" . static::$userID . "', opDate=NOW()";
//
if ($insert_update) {
$query .= $query_set . " ON DUPLICATE KEY UPDATE " . $query_set;
} else {
$query .= "";
}
static::$db->query($query) or die(static::$db->last_error . "<br>{$query}<br>" . __FILE__ . ": " . __LINE__);
static::$affected_rows = static::$db->affected_rows;
// ha nem kaptunk id-t akkor megnézzük, hogy lett a beszurt értéke
if (empty($id)) {
$id = static::$db->insert_id;
}
static::$db->query("INSERT INTO `Log` SET logEventID='{$nextEventID}', opID='" . static::$userID . "', opDate=NOW(), " . "logTable='{$table}', logEvent='INSERT', logItemID='{$id}', logColumn='', logValue='" . static::$db->real_escape_string(serialize($new)) . "' ") or die(__FILE__ . " at line " . __LINE__ . ": " . static::$db->error);
//Language DEPENDENT columns goes into tableLang
foreach ($new as $k => $v) {
if (preg_match("/(?<col>[^_]*)_(?<lang>[A-Z][A-Z])/", $k, $matches)) {
$_set = "{$matches['col']} = '" . static::$db->real_escape_string($v) . "', opID='" . static::$userID . "', opDate=NOW()";
static::$db->query("INSERT INTO `{$table}Lang` SET {$_set}, {$key}='{$id}', langID='{$matches['lang']}' ON DUPLICATE KEY UPDATE {$_set}") or die(__FILE__ . " at line " . __LINE__ . ": " . static::$db->error);
}
}
break;
case "UPDATE":
if ($id == "") {
print_r($new);
exit("Hiba: a frissítendő elem azonoisítója ismeretlen.");
}
$query = "UPDATE `{$table}` SET ";
$c = 0;
foreach ($new as $k => $v) {
if ($k != $key && $old[$k] != $v) {
// || $old[$k] == ""
$c++;
//Nyelvfüggő oszlopok külön táblába mennek
if (preg_match("/(?<col>[^_]*)_(?<lang>[A-Z][A-Z])/", $k, $matches)) {
$old_lang = static::$db->query("SELECT {$key},{$matches['col']} FROM {$table}Lang WHERE {$key}='{$id}' AND langID='{$matches['lang']}'")->fetch_assoc();
if ($old_lang[$matches['col']] != $v) {
//Csak akkor logolunk, ha volt változás
static::$db->query("INSERT INTO Log SET logEventID='{$nextEventID}', opID='" . static::$userID . "', opDate=NOW(), " . "logTable='{$table}', logEvent='UPDATE', logItemID='{$id}', logColumn='{$k}', logValue='" . static::$db->real_escape_string($old_lang[$matches['col']]) . "' ") or die(__FILE__ . " on line " . __LINE__ . ": " . static::$db->error);
}
if ($old_lang[$key] != $id) {
static::$db->query("INSERT INTO {$table}Lang SET {$matches['col']} = '" . static::$db->real_escape_string($v) . "', opID='" . static::$userID . "', opDate=NOW(), {$key}='{$id}', langID='{$matches['lang']}'") or die(__FILE__ . " at line " . __LINE__ . ": " . static::$db->error);
} else {
static::$db->query("UPDATE {$table}Lang SET {$matches['col']} = '" . static::$db->real_escape_string($v) . "', opID='" . static::$userID . "', opDate=NOW() WHERE {$key}='{$id}' AND langID='{$matches['lang']}'") or die(__FILE__ . " at line " . __LINE__ . ": " . static::$db->error);
}
} else {
//Nyelv független oszlopok
$query .= "{$k}='" . static::$db->real_escape_string($v) . "', ";
if ($old[$k] != $v) {
//Csak a megváltozottakat logoljuk
static::$db->queryDie("INSERT INTO Log SET logEventID='{$nextEventID}', opID='" . static::$userID . "', opDate=NOW(), " . "logTable='{$table}', logEvent='UPDATE', logItemID='{$id}', logColumn='{$k}', logValue='" . static::$db->real_escape_string($old[$k]) . "' ");
}
}
}
}
//Csak akkor frissítünk ha történt is változás.
if ($c == 0) {
break;
}
$query .= " opID='" . static::$userID . "', opDate=NOW() ";
$query .= " WHERE {$key}='{$id}'";
if (static::$debug) {
echo "<pre>{$query}</pre>";
}
static::$db->query($query) or die(static::$db->last_error . "\nQUERY: {$query}" . print_r($new, 1));
break;
case "DELETE":
//TODO@torokp: Rekord törlésekor a kapcsolódó nyelvi rekordokat is törölni kellene
$serialized = serialize($old);
static::$db->queryDie("INSERT INTO Log SET logEventID='{$nextEventID}', opID='" . static::$userID . "', opDate=NOW(), " . "logTable='{$table}', logEvent='DELETE', logItemID='{$id}', logColumn='_ALL', logValue='" . static::$db->real_escape_string($serialized) . "' ");
static::$db->queryDie("DELETE FROM {$table} WHERE {$key}='{$id}' LIMIT 1");
break;
}
static::$db->query("COMMIT;");
return $id;
}
public static function exec($sql, $binds = array())
{
// make sure we have a connection
if (is_null(static::$dbh)) {
static::connect();
}
// check binds
if (!is_array($binds)) {
$binds = array($binds);
}
// profile in debug mode
if (static::$debug) {
$start = microtime(true);
}
// prepare
$sth = static::$dbh->prepare($sql);
// bind params
$reflector = new ReflectionMethod('PDOStatement', 'bindValue');
foreach ($binds as $index => $value) {
$key = is_int($index) ? $index + 1 : $index;
$type = is_bool($value) ? PDO::PARAM_BOOL : (is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
$reflector->invokeArgs($sth, array($key, $value, $type));
}
// get results
$result = $sth->execute();
// profile in debug mode
if (static::$debug) {
static::profiling($sth, $binds, $start);
}
// update affected rows
static::$affected_rows = $sth->rowCount();
// return result
return $result;
}
