<?php
/**
*-----------------------------------------------------------------------------
* common DAO class
*-----------------------------------------------------------------------------
* 2014-09-01 initial version by ushizawa
*
* いろいろを扱うDAO
*/
class common_dao extends DAO {
var $tablename = "";
var $idname = "";
var $operator_id = "";
function __construct() {
if(!empty($_SESSION["data"]->_attribute["operator_id"]))
$this->operator_id = $_SESSION["data"]->_attribute["operator_id"];
}
/**
* (non-PHPdoc)
* @see DAO::query()
* queryのオーバーライド。不適切なSQLだったら、SQL文を表示して終了。
*/
function query($sql){
$affected_row = parent::query($sql);
if($affected_row==-1) {
echo "SQL error:<br />" . $sql ."<br />";
exit;
}
}
/**
* (non-PHPdoc)
* @see DAO::search_query()
* search_queryのオーバーライド。不適切なSQLだったら、SQL文を表示して終了。
*/
function search_query($sql){
$con = parent::search_query($sql);
//echored($sql);
if(empty($con->mtst)) {
echo mysql_errno($con->dbid) . ": " . mysql_error($con->dbid) . "<br />" . $sql ."<br />";
exit;
}
return $con;
}
/**
* 文字エンコード
* @param array $data
* @return array $encode_data
* HTMLのエンコードはUTF-8なのに、この管理画面はEUC-JPのため・・・
*/
function setEncode($data, $charset="UTF-8"){
if(is_array($data)){
foreach($data as $key => $val){
if($charset=="UTF-8") {
$encode_data[$key] = mb_convert_encoding($val, "UTF-8", "EUC-JP");
} else {
$encode_data[$key] = mb_convert_encoding($val, "EUC-JP", "UTF-8");
}
}
} else {
if($charset=="UTF-8") {
$encode_data = mb_convert_encoding($data, "UTF-8", "EUC-JP");
} else {
$encode_data = mb_convert_encoding($data, "EUC-JP", "UTF-8");
}
}
return $encode_data;
}
/**
*
* @param int $id
* @param string $tablename
* @param string $idname
* @return array data
*/
function getData($id, $tablename="",$idname="") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
return $this->getRow("*", $tablename, $idname."=".$id);
}
/**
* テーブル名を指定して情報を取得
* @return array 一覧
*/
function getAll($cols="", $tablename="", $where_str="", $order_str="", $groupby_str="") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($cols)) $cols = "*";
// sql
$sql = "select ". $cols ." from " . $tablename ;
if(!empty($where_str)) $sql .= " WHERE ".$where_str;
if(!empty($order_str)) $sql .= " ORDER BY " . $order_str;
if(!empty($groupby_str)) $sql .= " GROUP BY " . $groupby_str;
$con = $this->search_query($sql);
$result = array();
for ($i = 0; $i < $con->numrows(); $i++) {
$result[] = mysql_fetch_assoc($con->mtst);
}
return $result;
}
/**
* 結果の一行を返す
* @param string $cols
* @param string $tablename
* @param string $where_str
* @return array row
*/
function getRow($cols, $tablename, $where_str = "") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($cols)) $cols = "*";
// sql
$sql = "select ". $cols ." from " . $tablename ;
if(!empty($where_str)) $sql .= " WHERE ".$where_str;
$con = $this->search_query($sql);
return $this->setEncode(mysql_fetch_assoc($con->mtst));
}
/**
*
* @param string $col
* @param string $tablename
* @param string $where_str
* @return one
*/
function getOne($cols, $tablename, $where_str = "") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($cols)) $cols = "*";
// sql
$sql = "select ". $cols ." from " . $tablename ;
if(!empty($where_str)) $sql .= " WHERE ".$where_str;
$con = $this->search_query($sql);
$res = $con->fetch();
return $this->setEncode($res[0]);
}
/**
*
* @param string $cols
* @param string $tablename
* @param string $where_str
* @param string $order_str
* @param bool $del_flg=true 削除フラグがたっているデータも取得するときはfalse
* @return boolean|Ambigous <mixed, boolean, multitype:>
*/
function getArray($cols, $tablename, $where_str = "", $order_str = "", $del_flg=false) {
if(parent::hasField("del_flg", $tablename) && $del_flg) {
if(!empty($where_str)){
$where_str .= " AND del_flg=0";
} else {
$where_str = "del_flg=0";
}
}
$all = $this->getAll($cols, $tablename, $where_str);
$arr = array();
if(!empty($all)){
// カラム名を取得
$keys = array_keys($all[0]);
$id_str = trim($keys[0]);
$name_str = trim($keys[1]);
foreach($all as $key => $val){
$arr[$val[$id_str]] = $val[$name_str];
}
}
return $arr;
}
/**
* m_xxxxのマスターデータを配列で取得
* @param unknown $tablename
* @param string $where_str
* @return multitype:Ambigous <Ambigous <mixed, boolean, multitype:>>
*/
function getMaster($tablename, $where_str = "") {
return $this->getArray("id, name", $tablename, "", "sort ASC");
}
/**
* ログを書く
* @param int $log_cd
* @param boolean $has_request
* @param string $note
* @return void|boolean
*/
function writeLog($log_id, $has_request=false, $note=null) {
// $_REQUESTのあるアクセスはログとらない(初回のアクセスのみ）
if(!empty($_REQUEST) && !$has_request) return;
// ログページのログはとらない
if($_SERVER['PHP_SELF']=="/operation/log.html" || $_SERVER['PHP_SELF']=="/operation/log_update.html") return;
// loginの場合、インスタンス生成したあとにセッションを設定しているので・・
$this->operator_id = $_SESSION["data"]->_attribute["operator_id"];
if(empty($this->operator_id)) return;
$savedata = array();
$savedata['create_date'] = $this->addQuote(date("Y-m-d H:i:s"));
$savedata['create_id'] = $this->addQuote($this->operator_id) ;
$savedata['log_id'] = $this->addQuote($log_id);
$savedata['ip'] = $this->addQuote($_SERVER['REMOTE_ADDR']);
$savedata['file_name'] = $this->addQuote($_SERVER['PHP_SELF']);
if(!empty($note)) $savedata['note'] = $this->addQuote($note);
// sql
$sql = "INSERT INTO log ( ".implode(",", array_keys($savedata)).") ".
"VALUES (".implode(",", $savedata).")";
// insert クエリー実行！
$res = $this->query($sql);
return true;
}
/**
* 更新ログを書く
* @param array $data
* @return void|boolean
*/
function writeUpdateLog($data, $tablename="", $idname="") {
if(empty($data) || empty($data[$idname])) return ;
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
$id = $data[$idname];
// 変更前データを取得
$data_ex = $this->getRow("*", $tablename, "$idname='{$id}'");
// 差異がないかチェック
foreach($data_ex As $key=>$val) {
if(isset($data[$key]) && $data[$key]!=$val) {
if($key=="update_date") continue;
$data_modified[$key] = array($val, $data[$key]);
}
}
// 差異があったデータをlog_updatesに入れる
if(!empty($data_modified)) {
foreach($data_modified as $column => $val) {
$savedata = array();
$savedata['create_date'] = $this->addQuote(date("Y-m-d H:i:s"));
$savedata['ip'] = $this->addQuote($_SERVER['REMOTE_ADDR']);
$savedata['update_id'] = $this->addQuote($this->operator_id);
$savedata['table_name'] = $this->addQuote($this->tablename);
$savedata['column_name'] = $this->addQuote($column);
$savedata['id'] = $this->addQuote($id);
$savedata['data_from'] = $this->addQuote(mb_convert_encoding($val[0], "EUC-JP", "UTF-8"));
$savedata['data_to'] = $this->addQuote(mb_convert_encoding($val[1], "EUC-JP", "UTF-8"));
// sql
$sql = "INSERT INTO log_update ( ".implode(",", array_keys($savedata)).") ";
$sql.= "VALUES (".implode(",", $savedata).")";
//echored($sql);exit;
// insert クエリー実行！
$res = $this->query($sql);
}
}
return true;
}
/**
*
* @param string $q
* @return string
*/
function addQuote($q){
$q = str_replace("\\'","’",$q);
return "'".mysql_escape_string($q)."'";
}
/**
* insert / update
* 「id」があるかないかで insertとupdateの処理を分ける
* @param array $data
* @return int $id
*/
function save($data, $tablename="", $idname="") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
// INSERTかUPDATEか判定。
// idnameで検索して既存データがあるかどうかを調べる
$insert_flg = false;
if(!empty($idname)){
//if(!empty($idname) && empty($data[$idname])){
$where_str = $idname."='".$data[$idname]."'";
if($this->hasField("del_flg", $tablename)){
$where_str .= " AND del_flg<>1";
}
$cnt = $this->getOne("count(".$idname.")", $tablename, $where_str);
//echoblue($cnt);
if(empty($cnt)) $insert_flg=true;
}
////////////////////////////////////////////////////////////////
// INSERT
////////////////////////////////////////////////////////////////
if($insert_flg) {
$this->insert($data, $tablename, $idname);
////////////////////////////////////////////////////////////////
// UPDATE
////////////////////////////////////////////////////////////////
} else {
$this->update($data, $tablename, $idname);
}
}
/**
* INSERT
* @param array $data
* @param string $tablename
* @return boolean
*/
function insert($data, $tablename="", $idname="") {
if(empty($data)) return false;
if(empty($tablename)) $tablename = $this->tablename;
// テーブルのフィールド情報を取得
$fields = parent::getFields($tablename);
// POSTされたデータで、テーブルの項目にないものは省く。
foreach($fields As $field=>$val) {
if(array_key_exists($field, $data)){
$savedata[$field] = $data[$field];
}
}
// 作成日、更新日
if(array_key_exists("create_date", $fields)) $savedata['create_date'] = date("Y-m-d H:i:s");
if(array_key_exists("update_date", $fields)) $savedata['update_date'] = date("Y-m-d H:i:s");
if(array_key_exists("create_id", $fields)) $savedata['create_id'] = $this->operator_id;
// SORT
if(array_key_exists("sort", $fields) && empty($savedata["sort"]))
$savedata["sort"] = $this->getNewSort($tablename);
// quote
foreach($savedata as $key => $val) {
if(empty($val) && strcmp($val, 0)!=0) {
$data2[$key] = "Null";
} else {
$val = mb_convert_encoding($val, "EUC-JP", "UTF-8");
$data2[$key] = $this->addQuote($val);
}
}
// sql
$sql = "INSERT INTO {$tablename} ( ".implode(",", array_keys($data2)).") ".
"VALUES (".implode(",", $data2).")";
// insert クエリー実行！
$this->query($sql);
//pr($savedata);
//echored($idname);
// log
if($tablename!="dtb_log" && $tablename!="dtb_log_update"){
$this->writeLog(4, true, $tablename." ".$savedata[$idname]); // log insert
}
}
/**
* UPDATE
* @param array $data
* @param string $tablename
* @return boolean
*/
function update($data, $tablename="", $idname="") {
if(empty($data)) return false;
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
$id = $data[$idname];
// テーブルのフィールド情報を取得
$fields = parent::getFields($tablename);
foreach($fields As $field=>$val) {
if(array_key_exists($field, $data)) {
$savedata[$field] = $data[$field];
// 登録画像削除チェックボックス
if(array_key_exists($field."_del", $data)) {
$savedata[$field] = null; // Nullにする
//echoblue($field."---".$data[$field]);
}
}
}
// 更新日
if(array_key_exists("update_date", $fields)) $savedata['update_date'] = date("Y-m-d H:i:s");
$this->writeUpdateLog($savedata, $tablename, $idname);
// quote
foreach($savedata as $key => $val) {
if(empty($val) && strcmp($val, 0)!=0) {
$data2[$key] = $key."=NULL";
} else {
$val = mb_convert_encoding($val, "EUC-JP", "UTF-8");
$data2[$key] = $key."=".$this->addQuote($val);
}
}
// UPDATE SQL
$sql = "UPDATE {$tablename} SET ".implode(",",$data2)." WHERE " . $idname . "='".$id."'";
$res = $this->query($sql);
// log
$this->writeLog(5, true, $tablename." ".$id);
}
/**
* レコードを削除する
* @param int $id
*/
function delete($id, $tablename="", $idname="") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
$this->query("DELETE FROM ".$tablename." WHERE " . $idname."='".$id."'");
$this->writeLog(6, true, $id);
}
/**
* 削除フラグをたてる
* @param int $id
*/
function del_flg($id, $tablename="", $idname="") {
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
$sql = "UPDATE ".$tablename." SET del_flg=1, update_date='".date("Y-m-d H:i:s")."' WHERE " . $idname."='".$id."'";
$this->query($sql);
$this->writeLog(6, true, $id);
}
function getCount($where_str="", $tablename="") {
if(empty($tablename)) $tablename = $this->tablename;
return $this->getOne("count(*)", $tablename, $where_str);
}
/**
* 順位の移動
* @param string $tablename
* @param string $idname
* @param int $sort
* @param string $cmt (up/down)
* @return
*/
function updateSort($sort, $cmd, $tablename="", $idname=""){
if(empty($tablename)) $tablename = $this->tablename;
if(empty($idname)) $idname = $this->idname;
$new_sort = ($cmd == "up") ? ($sort - 1) : ($sort + 1);
// id取得
$id = $this->getOne($idname, $tablename ,"sort=".$sort);
$id_move = $this->getOne($idname ,$tablename ,"sort=".$new_sort);
$sql1 = "update {$tablename} set sort={$new_sort} where $idname = '{$id}'";
$sql2 = "update {$tablename} set sort={$sort} where $idname = '{$id_move}'";
$this->query($sql1);
$this->query($sql2);
}
/**
* ランクを取得
* @param string $tablename
* @return int new_sort
*/
function getNewSort($tablename="") {
if(empty($tablename)) $tablename = $this->tablename;
$new_sort = $this->getOne("Max(sort)+1", $tablename);
if(empty($new_sort)) $new_sort = 1;
return $new_sort;
}
}
?>