public function update($id, $arr)
{
//var_dump($_SESSION);
//$objUser= new Users();
global $objUser;
$user = $objUser->getCurrentUser();
$arr['updatedBy'] = $user['us01uin'];
$arr['updatedOn'] = date('Y-m-d H:i:s');
//var_dump('arr',$arr);
return parent::update($id, $arr);
//$this->update_core($id);
}
public function update($id, $arr)
{
//var_dump($_SESSION);
//$objUser= new Users();
global $objUser, $objDb;
$user = $objUser->getCurrentUser();
$row = $this->getByID($arr['uin']);
if ($arr['create']) {
$action = 'INSERT';
$qryFields = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . DB_NAME . "' AND TABLE_NAME = '" . $row['log01table'] . "'";
$targeFields = $this->Query($qryFields);
$qryCheck = "select trigger_name from information_schema.triggers where event_object_table='" . $row['log01table'] . "';";
$triggerList = $this->Query($qryCheck);
if (!$triggerList) {
$sqlTrigger = "CREATE TRIGGER `trigger" . date('Y-m-d H:i:s') . "` AFTER " . $action . " ON `" . $row['log01table'] . "`\nFOR EACH ROW\nBEGIN ";
foreach ($targeFields as $field) {
$sqlTrigger .= "IF( NEW." . $field['COLUMN_NAME'] . " ) THEN\nINSERT INTO log02log( `log02action` , `log02tablename` ,`log02field` , `log02before` , `log02after` ) VALUES ( 'insert', '" . $row['log01table'] . "', '" . $field['COLUMN_NAME'] . "', NEW." . $field['COLUMN_NAME'] . " );END IF ;";
}
$sqlTrigger .= "END;";
$this->Query($sqlTrigger);
}
foreach ($triggerList as $trigger) {
$triggerName = $trigger['trigger_name'];
$sqlTriggerType = "select TRIGGER_NAME from information_schema.triggers where event_object_table='" . $row['log01table'] . "' and event_manipulation in('" . $action . "');";
$triggerType = $this->Query($sqlTriggerType);
if (!$triggerType) {
$arr['updatedBy'] = $user['us01uin'];
$arr['updatedOn'] = date('Y-m-d H:i:s');
$sqlTrigger = "CREATE TRIGGER `trigger" . date('Y-m-d H:i:s') . "` AFTER " . $action . " ON `" . $row['log01table'] . "`\nFOR EACH ROW\nBEGIN ";
foreach ($targeFields as $field) {
$sqlTrigger .= "IF( NEW." . $field['COLUMN_NAME'] . " ) THEN\nINSERT INTO log02log( `log02action` , `log02tablename` ,`log02field` , `log02before` , `log02after` ) VALUES ( 'insert', '" . $row['log01table'] . "', '" . $field['COLUMN_NAME'] . "', NEW." . $field['COLUMN_NAME'] . " );END IF ;";
}
$sqlTrigger .= "END;";
$this->Query($sqlTrigger);
}
}
} elseif (!isset($arr['create'])) {
$action = 'INSERT';
$qryCheck = "select trigger_name from information_schema.triggers where event_object_table='" . $row['log01table'] . "';";
$triggerList = $this->Query($qryCheck);
foreach ($triggerList as $trigger) {
$triggerName = $trigger['trigger_name'];
$sqlTriggerType = "select TRIGGER_NAME from information_schema.triggers where event_object_table='" . $row['log01table'] . "' and event_manipulation in('" . $action . "');";
$triggerType = $this->Query($sqlTriggerType);
if ($triggerType) {
$sqlDropTrigger = "Drop trigger `" . $triggerName . "`";
$result = $this->Query($sqlDropTrigger);
}
}
$arr['create'] = '0';
}
if ($arr['update']) {
$action = 'UPDATE';
$qryCheck = "select trigger_name from information_schema.triggers where event_object_table='" . $row['log01table'] . "';";
$triggerList = $this->Query($qryCheck);
if (!$triggerList) {
$sqlTrigger = "CREATE TRIGGER `trigger" . date('Y-m-d H:i:s') . "` AFTER " . $action . " ON `" . $row['log01table'] . "`\nFOR EACH ROW\nBEGIN ";
foreach ($targeFields as $field) {
$sqlTrigger .= "IF( NEW." . $field['COLUMN_NAME'] . " ) THEN\nINSERT INTO log02log( `log02action` , `log02tablename` ,`log02field` , `log02before` , `log02after` ) VALUES ( 'insert', '" . $row['log01table'] . "', '" . $field['COLUMN_NAME'] . "', NEW." . $field['COLUMN_NAME'] . " );END IF ;";
}
$sqlTrigger .= "END;";
$this->Query($sqlTrigger);
}
foreach ($triggerList as $trigger) {
$triggerName = $trigger['trigger_name'];
$sqlTriggerType = "select TRIGGER_NAME from information_schema.triggers where event_object_table='" . $row['log01table'] . "' and event_manipulation in('" . $action . "');";
$triggerType = $this->Query($sqlTriggerType);
var_dump('inserting update trigger', $triggerType);
if (!$triggerType) {
$qryFields = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . DB_NAME . "' AND TABLE_NAME = '" . $row['log01table'] . "'";
$targeFields = $this->Query($qryFields);
$arr['updatedBy'] = $user['us01uin'];
$arr['updatedOn'] = date('Y-m-d H:i:s');
$sqlTrigger = "CREATE TRIGGER `trigger" . date('Y-m-d H:i:s') . "` AFTER " . $action . " ON `" . $row['log01table'] . "`\nFOR EACH ROW\nBEGIN ";
foreach ($targeFields as $field) {
$sqlTrigger .= "IF( NEW." . $field['COLUMN_NAME'] . " ) THEN\nINSERT INTO log02log( `log02action` , `log02tablename` ,`log02field` , `log02before` , `log02after` ) VALUES ( 'insert', '" . $row['log01table'] . "', '" . $field['COLUMN_NAME'] . "', NEW." . $field['COLUMN_NAME'] . " );END IF ;";
}
$sqlTrigger .= "END;";
$this->Query($sqlTrigger);
}
}
} elseif (!isset($arr['udpate'])) {
$action = 'UPDATE';
$qryCheck = "select trigger_name from information_schema.triggers where event_object_table='" . $row['log01table'] . "';";
$triggerList = $this->Query($qryCheck);
//var_dump('here', $triggerList, $arr);
foreach ($triggerList as $trigger) {
$triggerName = $trigger['trigger_name'];
$sqlTriggerType = "select TRIGGER_NAME from information_schema.triggers where event_object_table='" . $row['log01table'] . "' and event_manipulation in('" . $action . "');";
$triggerType = $this->Query($sqlTriggerType);
if ($triggerType) {
$sqlDropTrigger = "Drop trigger `" . $triggerName . "`";
$result = $this->Query($sqlDropTrigger);
}
}
$arr['update'] = '0';
}
return parent::update($id, $arr);
}
