記錄異動前的欄位記錄

CodeCharge Studio 2.x, 3.x, 4.x, 5.x
回覆文章
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

記錄異動前的欄位記錄

文章 yehlu »

Classes.php

function SetDBValue($DBValue)

代碼: 選擇全部

$_SESSION['db_log'][$_SERVER["SCRIPT_FILENAME"]][$this->Name] = $DBValue; 
共有2個
CCS51:777,1512
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

寫入資料庫欄位異動記錄

文章 yehlu »

Common.php

代碼: 選擇全部

//CCBuildUpdate @0-E2594C39
function CCBuildUpdate($table, & $Fields, & $Connection) {
    //參數
    global $db;
    $not_record = array('mod_stamp','mod_user');
    $php_name = $_SERVER["SCRIPT_FILENAME"];
    $tbl_where = $Connection->Where;
	//EditableGrid 判斷
    if (is_array($Connection->CachedColumns)) {
        $key_field = array_keys($Connection->CachedColumns);
    }
    if (isset($key_field)) {
        $key_id =  $Connection->CachedColumns[$key_field[0]];
    }else{
        $a = explode('=',$tbl_where);
        $key_id = $a[1];     
    }
    $pairs = array();
    foreach ($Fields as $Field) {
        if (!isset($Field["OmitIfEmpty"]) || !$Field["OmitIfEmpty"] || !is_null($Field["Value"])) {
            if ($Field["DataType"] == ccsMemo && ($Connection->DB == "Oracle" || $Connection->DB == "OracleOCI")) {
                $value = ":" . $Field["Name"];
                $Connection->Bind($Field["Name"], $Field["Value"], -1);
            } else {
                $value = $Connection->ToSQL($Field["Value"], $Field["DataType"]);
            }
            $pairs[] = $Field["Name"] . " = " . $value;
            if (isset($key_field)) {
                //異動欄位
                if ($_SESSION['db_log1'][$key_id][$Field["Name"]]<>$Field["Value"]) {
                    if (!in_array($Field["Name"],$not_record)) {
                        $mod[$Field["Name"]]['before_data'] = $_SESSION['db_log1'][$key_id][$Field["Name"]];
                        $mod[$Field["Name"]]['after_data'] = $Field["Value"];
                    }
                }
            } else {
                //異動欄位
                if ($_SESSION['db_log'][$php_name][$Field["Name"]]<>$Field["Value"]) {
                    if (!in_array($Field["Name"],$not_record)) {
                        $mod[$Field["Name"]]['before_data'] = $_SESSION['db_log'][$php_name][$Field["Name"]];
                        $mod[$Field["Name"]]['after_data'] = $Field["Value"];
                    }
                }
            }
        }
    }
    //異動記錄
    if (count($mod)>0) {
        if (isset($_SESSION['EosID'])) {
            $user_id = $_SESSION['EosID'];
            $store_id = 1;
        } else {
            $user_id = $_SESSION['StoreSess']['user_id'];
            $store_id = $_SESSION['StoreSess']['store_id'];
        }
        $now = date('Y-m-d H:i:s');
        foreach($mod as $key => $value) {
            $query = "insert db_log set store_id = '$store_id',user_id = '$user_id',tbl_name = '$table',tbl_field = '$key',tbl_where = '$tbl_where',before_data = '".$value['before_data']."',after_data = '".$value['after_data']."',change_time = '$now',php_name = '$php_name',tbl_id = '$key_id' ";
            $db->query($query);
        }
        if (isset($key_field)) {
            unset($_SESSION['db_log1'][$key_id]);
        } else {
            unset($_SESSION['db_log'][$php_name]);
        }
    }
    return count($pairs) ? "UPDATE " . $table . " SET " . implode(", ", $pairs) : "";
}

//End CCBuildUpdate
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

資料庫結構

文章 yehlu »

代碼: 選擇全部

CREATE TABLE IF NOT EXISTS `db_log` (
  `db_log_id` int(11) NOT NULL auto_increment,
  `store_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `tbl_id` int(11) NOT NULL,
  `tbl_name` varchar(50) NOT NULL,
  `tbl_field` varchar(50) NOT NULL,
  `tbl_where` varchar(255) default NULL,
  `before_data` varchar(255) NOT NULL,
  `after_data` varchar(255) NOT NULL,
  `change_time` datetime NOT NULL,
  `php_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`db_log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='資料庫異動記錄' AUTO_INCREMENT=1 ;
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

EditableGrid 需修改程式

文章 yehlu »

function SetValues()

代碼: 選擇全部

        $data = $this->UpdateFields;
        foreach($data as $key => $value) {
            $_SESSION['db_log1'][$this->f("prod_id")][$key] = $this->f($key);
        }
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

加入按取消清除 SESSION

文章 yehlu »

必需有共用的 header 檔

加在

function Class_Terminate() 或 Before Unload 的 event

代碼: 選擇全部

        global $Redirect;
        if($Redirect<>''){
            unset($_SESSION['db_log'][$_SERVER["SCRIPT_FILENAME"]]);
        }
yehlu
Site Admin
文章: 3245
註冊時間: 2004-04-15 17:20:21
來自: CodeCharge Support Engineer

Re: 記錄異動前的欄位記錄

文章 yehlu »

CCS51 PHP 5.4

代碼: 選擇全部

function CCBuildUpdate($table, & $Fields, & $Connection) {
    //參數
    global $db;
    $not_record = array('mod_stamp','mod_user','mod_users');
    $php_name = $_SERVER["SCRIPT_FILENAME"];
    $tbl_where = $Connection->Where;
   //EditableGrid 判斷
    if (isset($Connection->CachedColumns) and is_array($Connection->CachedColumns)) {
        $key_field = array_keys($Connection->CachedColumns);
    }
    if (isset($key_field)) {
        $key_id =  $Connection->CachedColumns[$key_field[0]];
    }else{
        $a = explode('=',$tbl_where);
        $key_id = $a[1];     
    }
    $pairs = array();
    $mod = array();
    foreach ($Fields as $Field) {
        if (!isset($Field["OmitIfEmpty"]) || !$Field["OmitIfEmpty"] || !is_null($Field["Value"])) {
            if ($Field["DataType"] == ccsMemo && ($Connection->DB == "Oracle" || $Connection->DB == "OracleOCI")) {
                $value = ":" . $Field["Name"];
                $Connection->Bind($Field["Name"], $Field["Value"], -1);
            } else {
                $value = $Connection->ToSQL($Field["Value"], $Field["DataType"]);
            }
            $pairs[] = $Field["Name"] . " = " . $value;
            if (isset($key_field)) {
                //異動欄位
                if (isset($_SESSION['db_log1'][$key_id][$Field["Name"]]) and $_SESSION['db_log1'][$key_id][$Field["Name"]]<>$Field["Value"]) {
                    if (!in_array($Field["Name"],$not_record)) {
                        $mod[$Field["Name"]]['before_data'] = $_SESSION['db_log1'][$key_id][$Field["Name"]];
                        $mod[$Field["Name"]]['after_data'] = $Field["Value"];
                    }
                }
            } else {
                //異動欄位
                if (isset($_SESSION['db_log'][$php_name][$Field["Name"]]) and $_SESSION['db_log'][$php_name][$Field["Name"]]<>$Field["Value"]) {
                    if (!in_array($Field["Name"],$not_record)) {
                        if($Field["Value"]==$_SESSION['db_log'][$php_name][$Field["Name"]].' 00:00:00'){
                        }else{
                            $mod[$Field["Name"]]['before_data'] = $_SESSION['db_log'][$php_name][$Field["Name"]];
                            $mod[$Field["Name"]]['after_data'] = $Field["Value"];
                        }
                    }    
                }
            }
        }
    }
    //異動記錄
    if (count($mod)>0) {
        $user_id = CCGETUserID();
        $store_id = 1;
        $now = date('Y-m-d H:i:s');
        foreach($mod as $key => $value) {
            $query = "insert db_log set store_id = '$store_id',user_id = '$user_id',tbl_name = '$table',tbl_field = '$key',tbl_where = '$tbl_where',before_data = '".$value['before_data']."',after_data = '".$value['after_data']."',change_time = '$now',php_name = '$php_name',tbl_id = '$key_id' ";
            $db->query($query);
        }
        if (isset($key_field)) {
            unset($_SESSION['db_log1'][$key_id]);
        } else {
            unset($_SESSION['db_log'][$php_name]);
        }
    }
    return count($pairs) ? "UPDATE " . $table . " SET " . implode(", ", $pairs) : "";
}
回覆文章

回到「CodeCharge Studio」