Index: branches/version-2_5-dev/data/class/helper/SC_Helper_DB.php =================================================================== --- branches/version-2_5-dev/data/class/helper/SC_Helper_DB.php (revision 18784) +++ branches/version-2_5-dev/data/class/helper/SC_Helper_DB.php (revision 18789) @@ -1947,5 +1947,4 @@ * 指定ファイルが存在する場合 SQL として実行 * - * ・MySQL の場合、文字「;」を区切りとして、分割実行。 * XXX プラグイン用に追加。将来消すかも。 * @@ -1960,12 +1959,8 @@ if ($sqls === false) SC_Utils_Ex::sfDispException('ファイルは存在するが読み込めない'); - if (DB_TYPE == 'mysql') { - foreach (explode(';', $sqls) as $sql) { - $sql = trim($sql); - if (strlen($sql) == 0) continue; - $objQuery->query($sql); - } - } else { - $objQuery->query($sqls); + foreach (explode(';', $sqls) as $sql) { + $sql = trim($sql); + if (strlen($sql) == 0) continue; + $objQuery->query($sql); } } Index: branches/version-2_5-dev/data/class/batch/SC_Batch_Daily.php =================================================================== --- branches/version-2_5-dev/data/class/batch/SC_Batch_Daily.php (revision 18701) +++ branches/version-2_5-dev/data/class/batch/SC_Batch_Daily.php (revision 18789) @@ -360,15 +360,4 @@ $objQuery->insert("dtb_bat_order_daily_age", $sqlval); } - - // 文字列にSingleQuotationを付与する - function lfSingleQuot($value){ - $ret = ""; - if (DB_TYPE == "mysql") { - $ret = $value; - }else{ - $ret = "'" . $value . "'"; - } - return $ret; - } } ?> Index: branches/version-2_5-dev/data/class/pages/mypage/LC_Page_Mypage_DownLoad.php =================================================================== --- branches/version-2_5-dev/data/class/pages/mypage/LC_Page_Mypage_DownLoad.php (revision 18777) +++ branches/version-2_5-dev/data/class/pages/mypage/LC_Page_Mypage_DownLoad.php (revision 18789) @@ -106,4 +106,5 @@ $col = "*"; $table = "vw_download_class AS T1"; + // FIXME order_id, product_id の妥当性をチェックすべき. if (DB_TYPE == "mysql"){ $where = "T1.customer_id = " . (int)$_SESSION['customer']['customer_id'] . " AND T1.order_id = " . (int)$_GET['order_id'] . " AND T1.product_id = " . (int)$_GET['product_id'] . Index: branches/version-2_5-dev/data/class/pages/admin/mail/LC_Page_Admin_Mail_Sendmail.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/mail/LC_Page_Admin_Mail_Sendmail.php (revision 18770) +++ branches/version-2_5-dev/data/class/pages/admin/mail/LC_Page_Admin_Mail_Sendmail.php (revision 18789) @@ -83,10 +83,6 @@ } else { $where .= ' AND end_date IS NULL'; - // postgresql と mysql とでSQLをわける - if (DB_TYPE == "pgsql") { - $where .= "start_date BETWEEN current_timestamp + '- 5 minutes' AND current_timestamp + '5 minutes'"; - } else if (DB_TYPE == "mysql") { - $where .= "start_date BETWEEN date_add(now(),INTERVAL -5 minute) AND date_add(now(),INTERVAL 5 minute)"; - } + $dbFactory = SC_DB_DBFactory::getInstance(); + $where .= $dbFactory->getSendHistoryWhereStartdateSql(); // 30分毎にCronが送信時間データ確認 } Index: branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products.php (revision 18701) +++ branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products.php (revision 18789) @@ -298,5 +298,4 @@ // 取得範囲の指定(開始行番号、行数のセット) - // if(DB_TYPE != "mysql") $objQuery->setLimitOffset($page_max, $startno); $objQuery->setLimitOffset($page_max, $startno); // 表示順序 Index: branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products_ProductSelect.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products_ProductSelect.php (revision 18701) +++ branches/version-2_5-dev/data/class/pages/admin/products/LC_Page_Admin_Products_ProductSelect.php (revision 18789) @@ -131,5 +131,5 @@ // 取得範囲の指定(開始行番号、行数のセット) - if(DB_TYPE != "mysql") $objQuery->setLimitOffset($page_max, $startno); + $objQuery->setLimitOffset($page_max, $startno); // 表示順序 $objQuery->setOrder($order); Index: branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order.php (revision 18772) +++ branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order.php (revision 18789) @@ -139,33 +139,19 @@ $val = SC_Utils_Ex::sfManualEscape($val); + $dbFactory = SC_DB_DBFactory::getInstance(); switch ($key) { case 'search_product_name': - if(DB_TYPE == "pgsql"){ -// $val = mb_convert_encoding($val,"UTF-8",mb_detect_encoding($val)); $where .= " AND (SELECT COUNT(*) FROM dtb_order_detail od WHERE od.order_id = dtb_order.order_id AND od.product_name ILIKE ?) > 0"; $nonsp_val = mb_ereg_replace("[ 　]+","",$val); $arrval[] = "%$nonsp_val%"; - }elseif(DB_TYPE == "mysql"){ - $where .= " AND (SELECT COUNT(*) FROM dtb_order_detail od WHERE od.order_id = dtb_order.order_id AND od.product_name LIKE ?) > 0"; - $nonsp_val = mb_ereg_replace("[ 　]+","",$val); - $arrval[] = "%$nonsp_val%"; - } break; case 'search_order_name': - if(DB_TYPE == "pgsql"){ - $where .= " AND order_name01||order_name02 ILIKE ?"; - }elseif(DB_TYPE == "mysql"){ - $where .= " AND concat(order_name01,order_name02) ILIKE ?"; - } + $where .= " AND " . $dbFactory->concatColumn(array("order_name01", "order_name02")) . " ILIKE ?"; $nonsp_val = mb_ereg_replace("[ 　]+","",$val); $arrval[] = "%$nonsp_val%"; break; case 'search_order_kana': - if(DB_TYPE == "pgsql"){ - $where .= " AND order_kana01||order_kana02 ILIKE ?"; - }elseif(DB_TYPE == "mysql"){ - $where .= " AND concat(order_kana01,order_kana02) ILIKE ?"; - } + $where .= " AND " . $dbFactory->concatColumn(array("order_kana01", "order_kana02")) . " ILIKE ?"; $nonsp_val = mb_ereg_replace("[ 　]+","",$val); $arrval[] = "%$nonsp_val%"; @@ -198,9 +184,5 @@ break; case 'search_order_tel': - if(DB_TYPE == "pgsql"){ - $where .= " AND (order_tel01 || order_tel02 || order_tel03 ILIKE ?)"; - }elseif(DB_TYPE == "mysql"){ - $where .= " AND concat(order_tel01,order_tel02,order_tel03) ILIKE ?"; - } + $where .= " AND (" . $dbFactory->concatColumn(array("order_tel01", "order_tel02", "order_tel03")) . " ILIKE ?)"; $nonmark_val = ereg_replace("[()-]+","",$val); $arrval[] = "%$nonmark_val%"; @@ -277,5 +259,4 @@ $order = "update_date DESC"; - switch($_POST['mode']) { case 'csv': Index: branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order_ProductSelect.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order_ProductSelect.php (revision 18772) +++ branches/version-2_5-dev/data/class/pages/admin/order/LC_Page_Admin_Order_ProductSelect.php (revision 18789) @@ -149,5 +149,5 @@ // 取得範囲の指定(開始行番号、行数のセット) - if(DB_TYPE != "mysql") $objQuery->setLimitOffset($page_max, $startno); + $objQuery->setLimitOffset($page_max, $startno); // 表示順序 $objQuery->setOrder($order); Index: branches/version-2_5-dev/data/class/pages/admin/LC_Page_Admin_Home.php =================================================================== --- branches/version-2_5-dev/data/class/pages/admin/LC_Page_Admin_Home.php (revision 18772) +++ branches/version-2_5-dev/data/class/pages/admin/LC_Page_Admin_Home.php (revision 18789) @@ -132,12 +132,6 @@ function lfGetOrderYesterday(&$objQuery, $method){ if ( $method == 'SUM' or $method == 'COUNT'){ - // postgresql と mysql とでSQLをわける - if (DB_TYPE == "pgsql") { - $sql = "SELECT ".$method."(total) FROM dtb_order - WHERE del_flg = 0 AND to_char(create_date,'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD') AND status <> " . ORDER_CANCEL; - }else if (DB_TYPE == "mysql") { - $sql = "SELECT ".$method."(total) FROM dtb_order - WHERE del_flg = 0 AND cast(create_date as date) = DATE_ADD(current_date, interval -1 day) AND status <> " . ORDER_CANCEL; - } + $dbFactory = SC_DB_DBFactory::getInstance(); + $sql = $dbFactory->getOrderYesterdaySql($method); $return = $objQuery->getOne($sql); } @@ -150,14 +144,6 @@ if ( $method == 'SUM' or $method == 'COUNT'){ - // postgresql と mysql とでSQLをわける - if (DB_TYPE == "pgsql") { - $sql = "SELECT ".$method."(total) FROM dtb_order - WHERE del_flg = 0 AND to_char(create_date,'YYYY/MM') = ? - AND to_char(create_date,'YYYY/MM/DD') <> to_char(now(),'YYYY/MM/DD') AND status <> " . ORDER_CANCEL; - }else if (DB_TYPE == "mysql") { - $sql = "SELECT ".$method."(total) FROM dtb_order - WHERE del_flg = 0 AND date_format(create_date, '%Y/%m') = ? - AND date_format(create_date, '%Y/%m/%d') <> date_format(now(), '%Y/%m/%d') AND status <> " . ORDER_CANCEL; - } + $dbFactory = SC_DB_DBFactory::getInstance(); + $sql = $dbFactory->getOrderMonthSql($method); $return = $objQuery->getOne($sql, array($month)); } @@ -175,14 +161,6 @@ function lfGetReviewYesterday(&$objQuery){ - // postgresql と mysql とでSQLをわける - if (DB_TYPE == "pgsql") { - $sql = "SELECT COUNT(*) FROM dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id - WHERE A.del_flg=0 AND B.del_flg = 0 AND to_char(A.create_date, 'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD') - AND to_char(A.create_date,'YYYY/MM/DD') != to_char(now(),'YYYY/MM/DD')"; - }else if (DB_TYPE == "mysql") { - $sql = "SELECT COUNT(*) FROM dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id - WHERE A.del_flg = 0 AND B.del_flg = 0 AND cast(A.create_date as date) = DATE_ADD(current_date, interval -1 day) - AND cast(A.create_date as date) != current_date"; - } + $dbFactory = SC_DB_DBFactory::getInstance(); + $sql = $dbFactory->getReviewYesterdaySql(); $return = $objQuery->getOne($sql); return $return; Index: branches/version-2_5-dev/data/class/SC_CustomerList.php =================================================================== --- branches/version-2_5-dev/data/class/SC_CustomerList.php (revision 18701) +++ branches/version-2_5-dev/data/class/SC_CustomerList.php (revision 18789) @@ -31,9 +31,10 @@ function SC_CustomerList($array, $mode = '') { parent::SC_SelectSql($array); - + $masterData = new SC_DB_MasterData_Ex(); $arrMobileDomain = $masterData->getMasterData("mtb_mobile_domain"); - + $objDb = new SC_Helper_DB_Ex(); + $dbFactory = SC_DB_DBFactory::getInstance(); if($mode == "") { @@ -70,10 +71,5 @@ if (!isset($this->arrSql['name'])) $this->arrSql['name'] = ""; if ( strlen($this->arrSql['name']) > 0 ) { - if(DB_TYPE == "pgsql"){ - $this->setWhere("(name01 || name02 LIKE ?)" ); - }elseif(DB_TYPE == "mysql"){ - $this->setWhere("concat(name01,name02) LIKE ?" ); - } - + $this->setWhere("(" . $dbFactory->concatColumn(array("name01", "name02")) . " LIKE ?)" ); $searchName = $this->addSearchStr($this->arrSql['name']); $this->arrVal[] = mb_ereg_replace("[ 　]+","",$searchName); @@ -83,9 +79,5 @@ if (!isset($this->arrSql['kana'])) $this->arrSql['kana'] = ""; if ( strlen($this->arrSql['kana']) > 0 ) { - if(DB_TYPE == "pgsql"){ - $this->setWhere("(kana01 || kana02 LIKE ?)"); - }elseif(DB_TYPE == "mysql"){ - $this->setWhere("concat(kana01,kana02) LIKE ?" ); - } + $this->setWhere("(" . $dbFactory->concatColumn(array("kana01", "kana02")) . " LIKE ?)" ); $searchKana = $this->addSearchStr($this->arrSql['kana']); $this->arrVal[] = mb_ereg_replace("[ 　]+","",$searchKana); @@ -102,9 +94,5 @@ if (!isset($this->arrSql['tel'])) $this->arrSql['tel'] = ""; if ( is_numeric( $this->arrSql['tel'] ) ) { - if(DB_TYPE == "pgsql"){ - $this->setWhere( "(tel01 || tel02 || tel03 LIKE ?)" ); - }elseif(DB_TYPE == "mysql"){ - $this->setWhere("concat(tel01,tel02,tel03) LIKE ?" ); - } + $this->setWhere("(" . $dbFactory->concatColumn(array("tel01", "tel02", "tel03")) . " LIKE ?)" ); $searchTel = $this->addSearchStr($this->arrSql['tel']); $this->arrVal[] = ereg_replace("-", "", $searchTel); Index: branches/version-2_5-dev/data/class/util/SC_Utils.php =================================================================== --- branches/version-2_5-dev/data/class/util/SC_Utils.php (revision 18775) +++ branches/version-2_5-dev/data/class/util/SC_Utils.php (revision 18789) @@ -993,13 +993,13 @@ } - // 特殊制御文字の手動エスケープ + /** + * 特殊制御文字の手動エスケープ + * @deprecated ブレースホルダを使用すること + */ function sfManualEscape($data) { + $objQuery =& SC_Query::getSingletonInstance(); // 配列でない場合 if(!is_array($data)) { - if (DB_TYPE == "pgsql") { - $ret = pg_escape_string($data); - }else if(DB_TYPE == "mysql"){ - $ret = mysql_real_escape_string($data); - } + $ret = $objQuery->quote($data); $ret = ereg_replace("%", "\\%", $ret); $ret = ereg_replace("_", "\\_", $ret); @@ -1009,10 +1009,5 @@ // 配列の場合 foreach($data as $val) { - if (DB_TYPE == "pgsql") { - $ret = pg_escape_string($val); - }else if(DB_TYPE == "mysql"){ - $ret = mysql_real_escape_string($val); - } - + $ret = $objQuery->quote($val); $ret = ereg_replace("%", "\\%", $ret); $ret = ereg_replace("_", "\\_", $ret); Index: branches/version-2_5-dev/data/class/db/SC_DB_DBFactory.php =================================================================== --- branches/version-2_5-dev/data/class/db/SC_DB_DBFactory.php (revision 18775) +++ branches/version-2_5-dev/data/class/db/SC_DB_DBFactory.php (revision 18789) @@ -101,4 +101,42 @@ /** + * 昨日の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 昨日の売上高・売上件数を算出する SQL + */ + function getOrderYesterdaySql($method) { return null; } + + /** + * 当月の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 当月の売上高・売上件数を算出する SQL + */ + function getOrderMonthSql($method) { return null; } + + /** + * 昨日のレビュー書き込み件数を算出する SQL を返す. + * + * @return string 昨日のレビュー書き込み件数を算出する SQL + */ + function getReviewYesterdaySql() { return null; } + + /** + * メール送信履歴の start_date の検索条件の SQL を返す. + * + * @return string 検索条件の SQL + */ + function getSendHistoryWhereStartdateSql() { return null; } + + /** + * 文字列連結を行う. + * + * @param array $columns 連結を行うカラム名 + * @return string 連結後の SQL 文 + */ + function concatColumn($columns) { return null; } + + /** * インデックスの検索結果を配列で返す. * Index: branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php =================================================================== --- branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (revision 18784) +++ branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (revision 18789) @@ -98,4 +98,76 @@ /** + * 昨日の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 昨日の売上高・売上件数を算出する SQL + */ + function getOrderYesterdaySql($method) { + return "SELECT ".$method."(total) FROM dtb_order " + . "WHERE del_flg = 0 " + . "AND cast(create_date as date) = DATE_ADD(current_date, interval -1 day) " + . "AND status <> " . ORDER_CANCEL; + } + + /** + * 当月の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 当月の売上高・売上件数を算出する SQL + */ + function getOrderMonthSql($method) { + return "SELECT ".$method."(total) FROM dtb_order " + . "WHERE del_flg = 0 " + . "AND date_format(create_date, '%Y/%m') = ? " + . "AND date_format(create_date, '%Y/%m/%d') <> date_format(now(), '%Y/%m/%d') " + . "AND status <> " . ORDER_CANCEL; + } + + /** + * 昨日のレビュー書き込み件数を算出する SQL を返す. + * + * @return string 昨日のレビュー書き込み件数を算出する SQL + */ + function getReviewYesterdaySql() { + return "SELECT COUNT(*) FROM dtb_review AS A " + . "LEFT JOIN dtb_products AS B " + . "ON A.product_id = B.product_id " + . "WHERE A.del_flg = 0 " + . "AND B.del_flg = 0 " + . "AND cast(A.create_date as date) = DATE_ADD(current_date, interval -1 day) " + . "AND cast(A.create_date as date) != current_date"; + } + + /** + * メール送信履歴の start_date の検索条件の SQL を返す. + * + * @return string 検索条件の SQL + */ + function getSendHistoryWhereStartdateSql() { + return "start_date BETWEEN date_add(now(),INTERVAL -5 minute) AND date_add(now(),INTERVAL 5 minute)"; + } + + /** + * 文字列連結を行う. + * + * @param array $columns 連結を行うカラム名 + * @return string 連結後の SQL 文 + */ + function concatColumn($columns) { + $sql = "concat("; + $i = 0; + $total = count($columns); + foreach ($columns as $column) { + $sql .= $column; + if ($i < $total -1) { + $sql .= ", "; + } + $i++; + } + $sql .= ")"; + return $sql; + } + + /** * インデックスの検索結果を配列で返す. * @@ -148,6 +220,6 @@ function findTableNames($expression = "") { $objQuery =& SC_Query::getSingletonInstance(); - $sql = "SHOW TABLES LIKE ?"; - $arrColList = $objQuery->getAll($sql, array("%" . $expression . "%")); + $sql = "SHOW TABLES LIKE ". $objQuery->quote("%" . $expression . "%"); + $arrColList = $objQuery->getAll($sql); $arrColList = SC_Utils_Ex::sfswaparray($arrColList, false); return $arrColList[0]; Index: branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php =================================================================== --- branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php (revision 18784) +++ branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php (revision 18789) @@ -78,4 +78,75 @@ . " AND relname = " . $objQuery->quote($table_name) . "GROUP BY relname"; + } + + /** + * 昨日の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 昨日の売上高・売上件数を算出する SQL + */ + function getOrderYesterdaySql($method) { + return "SELECT ".$method."(total) FROM dtb_order " + . "WHERE del_flg = 0 " + . "AND to_char(create_date,'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD') " + . "AND status <> " . ORDER_CANCEL; + } + + /** + * 当月の売上高・売上件数を算出する SQL を返す. + * + * @param string $method SUM または COUNT + * @return string 当月の売上高・売上件数を算出する SQL + */ + function getOrderMonthSql($method) { + return "SELECT ".$method."(total) FROM dtb_order " + . "WHERE del_flg = 0 " + . "AND to_char(create_date,'YYYY/MM') = ? " + . "AND to_char(create_date,'YYYY/MM/DD') <> to_char(now(),'YYYY/MM/DD') " + . "AND status <> " . ORDER_CANCEL; + } + + /** + * 昨日のレビュー書き込み件数を算出する SQL を返す. + * + * @return string 昨日のレビュー書き込み件数を算出する SQL + */ + function getReviewYesterdaySql() { + return "SELECT COUNT(*) FROM dtb_review AS A " + . "LEFT JOIN dtb_products AS B " + . "ON A.product_id = B.product_id " + . "WHERE A.del_flg=0 " + . "AND B.del_flg = 0 " + . "AND to_char(A.create_date, 'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD') " + . "AND to_char(A.create_date,'YYYY/MM/DD') != to_char(now(),'YYYY/MM/DD')"; + } + + /** + * メール送信履歴の start_date の検索条件の SQL を返す. + * + * @return string 検索条件の SQL + */ + function getSendHistoryWhereStartdateSql() { + return "start_date BETWEEN current_timestamp + '- 5 minutes' AND current_timestamp + '5 minutes'"; + } + + /** + * 文字列連結を行う. + * + * @param array $columns 連結を行うカラム名 + * @return string 連結後の SQL 文 + */ + function concatColumn($columns) { + $sql = ""; + $i = 0; + $total = count($columns); + foreach ($columns as $column) { + $sql .= $column; + if ($i < $total -1) { + $sql .= " || "; + } + $i++; + } + return $sql; }