/**
* コンストラクタ.
*
* @param string $dsn データソース名
* @param boolean $force_run エラーが発生しても処理を続行する場合 true
* @param boolean $new 新規に接続を行うかどうか
*/
function SC_Query($dsn = "", $force_run = false, $new = false)
{
if ($dsn == "") {
$dsn = array('phptype' => DB_TYPE, 'username' => DB_USER, 'password' => DB_PASSWORD, 'protocol' => 'tcp', 'hostspec' => DB_SERVER, 'port' => DB_PORT, 'database' => DB_NAME);
}
// Debugモード指定
// 常時ONにするとメモリが解放されない。
// 連続クエリ実行時に問題が生じる。
if (DEBUG_MODE) {
$options['debug'] = PEAR_DB_DEBUG;
} else {
$options['debug'] = 0;
}
// 持続的接続オプション
$options['persistent'] = PEAR_DB_PERSISTENT;
// バッファリング trueにするとメモリが解放されない。
// 連続クエリ実行時に問題が生じる。
$options['result_buffering'] = false;
if ($new) {
$this->conn = MDB2::connect($dsn, $options);
} else {
$this->conn = MDB2::singleton($dsn, $options);
}
if (!PEAR::isError($this->conn)) {
// $this->conn->setCharset("utf8"); XXX SQL Azure では使用できないらしい
$this->conn->setFetchMode(MDB2_FETCHMODE_ASSOC);
}
$this->dbFactory = SC_DB_DBFactory_Ex::getInstance();
$this->force_run = $force_run;
}
function SC_DbConn($dsn = "", $err_disp = true, $new = false)
{
global $objDbConn;
// Debugモード指定
$options['debug'] = PEAR_DB_DEBUG;
// 持続的接続オプション
$options['persistent'] = PEAR_DB_PERSISTENT;
// 既に接続されていないか、新規接続要望の場合は接続する。
if (!isset($objDbConn->connection) || $new) {
if ($dsn != "") {
$objDbConn = DB::connect($dsn, $options);
$this->dsn = $dsn;
} else {
if (defined('DEFAULT_DSN')) {
$objDbConn = DB::connect(DEFAULT_DSN, $options);
$this->dsn = DEFAULT_DSN;
} else {
return;
}
}
}
$this->conn = $objDbConn;
$this->error_mail_to = DB_ERROR_MAIL_TO;
$this->error_mail_title = DB_ERROR_MAIL_SUBJECT;
$this->err_disp = $err_disp;
$this->dbFactory = SC_DB_DBFactory_Ex::getInstance();
}
/**
* @param array $arrSelfInfo
*/
function __construct($arrSelfInfo)
{
parent::__construct($arrSelfInfo);
$this->objQuery = SC_Query_Ex::getSingletonInstance();
$this->objDb = new SC_Helper_DB_Ex();
$this->masterdata = new SC_DB_MasterData_Ex();
$this->objCategory = new SC_Helper_Category_Ex();
$this->objManager = $this->objQuery->conn->loadModule('Manager');
$this->objDbFuctory = SC_DB_DBFactory_Ex::getInstance(DB_TYPE);
}
/**
* Page のプロセス.
*
* @return void
*/
public function process($mode)
{
$objLog = new LC_Upgrade_Helper_LOG();
$objJson = new LC_Upgrade_Helper_Json();
$objLog->start($mode);
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$arrSystemInfo = array('eccube_version' => ECCUBE_VERSION, 'php_version' => phpversion(), 'db_version' => $dbFactory->sfGetDBVersion());
$objJson->setSuccess($arrSystemInfo);
$objJson->display();
$objLog->end();
}
/**
* Page のプロセス.
*
* @return void
*/
function process($mode)
{
$objLog = new LC_Upgrade_Helper_LOG();
$objJson = new LC_Upgrade_Helper_Json();
$objLog->start($mode);
if ($this->isValidIP() !== true) {
$objJson->setError(OSTORE_E_C_INVALID_ACCESS);
$objJson->display();
$objLog->error(OSTORE_E_C_INVALID_ACCESS);
return;
}
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$arrSystemInfo = array('eccube_version' => ECCUBE_VERSION, 'php_version' => phpversion(), 'db_version' => $dbFactory->sfGetDBVersion());
$objJson->setSuccess($arrSystemInfo);
$objJson->display();
$objLog->end();
}
/**
* カラムの存在チェックと作成を行う.
*
* チェック対象のテーブルに, 該当のカラムが存在するかチェックする.
* 引数 $add が true の場合, 該当のカラムが存在しない場合は, カラムの生成を行う.
* カラムの生成も行う場合は, $col_type も必須となる.
*
* @param string $tableName テーブル名
* @param string $colType カラムのデータ型
* @param string $dsn データソース名
* @param bool $add カラムの作成も行う場合 true
* @return bool カラムが存在する場合とカラムの生成に成功した場合 true,
* テーブルが存在しない場合 false,
* 引数 $add == false でカラムが存在しない場合 false
*/
public function sfColumnExists($tableName, $colName, $colType = '', $dsn = '', $add = false)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$dsn = $dbFactory->getDSN($dsn);
$objQuery =& SC_Query_Ex::getSingletonInstance($dsn);
// テーブルが無ければエラー
if (!in_array($tableName, $objQuery->listTables())) {
return false;
}
// 正常に接続されている場合
if (!$objQuery->isError()) {
// カラムリストを取得
$columns = $objQuery->listTableFields($tableName);
if (in_array($colName, $columns)) {
return true;
}
}
// カラムを追加する
if ($add) {
return $this->sfColumnAdd($tableName, $colName, $colType);
}
return false;
}
/**
* システム情報を取得する.
*
* @return array システム情報
*/
public function getSystemInfo()
{
$objDB = SC_DB_DBFactory_Ex::getInstance();
$arrSystemInfo = array(array('title' => 'EC-CUBE', 'value' => ECCUBE_VERSION), array('title' => 'サーバーOS', 'value' => php_uname()), array('title' => 'DBサーバー', 'value' => $objDB->sfGetDBVersion()), array('title' => 'WEBサーバー', 'value' => $_SERVER['SERVER_SOFTWARE']));
$value = phpversion() . ' (' . implode(', ', get_loaded_extensions()) . ')';
$arrSystemInfo[] = array('title' => 'PHP', 'value' => $value);
if (extension_loaded('GD') || extension_loaded('gd')) {
$arrValue = array();
foreach (gd_info() as $key => $val) {
$arrValue[] = "{$key} => {$val}";
}
$value = '有効 (' . implode(', ', $arrValue) . ')';
} else {
$value = '無効';
}
$arrSystemInfo[] = array('title' => 'GD', 'value' => $value);
$arrSystemInfo[] = array('title' => 'HTTPユーザーエージェント', 'value' => $_SERVER['HTTP_USER_AGENT']);
return $arrSystemInfo;
}
/**
* データの存在チェックを行う.
*
* @param string $tableName テーブル名
* @param string $where データを検索する WHERE 句
* @param array $arrWhereVal WHERE句のプレースホルダ値
* @return bool データが存在する場合 true, データの追加に成功した場合 true,
* $add == false で, データが存在しない場合 false
*/
public function sfDataExists($tableName, $where, $arrWhereVal)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$dsn = $dbFactory->getDSN($dsn);
$objQuery =& SC_Query_Ex::getSingletonInstance();
$exists = $objQuery->exists($tableName, $where, $arrWhereVal);
// データが存在する場合 TRUE
if ($exists) {
return TRUE;
} else {
return FALSE;
}
}
function lfGetOrderTerm($type, $sdate, $edate)
{
$objQuery = SC_Query_Ex::getSingletonInstance();
list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate);
$where .= ' AND del_flg = 0 AND status <> ?';
$arrWhereVal[] = ORDER_CANCEL;
switch ($type) {
case 'month':
$xtitle = t('c_(By month)_01');
$ytitle = t('c_(Total sales)_01');
$format = '%m';
break;
case 'year':
$xtitle = t('c_(By year)_01');
$ytitle = t('c_(Total sales)_01');
$format = '%Y';
break;
case 'wday':
$xtitle = t('c_(By day)_01');
$ytitle = t('c_(Total sales)_01');
$format = '%a';
break;
case 'hour':
$xtitle = t('c_(By time)_01');
$ytitle = t('c_(Total sales)_01');
$format = '%H';
break;
default:
$xtitle = t('c_(By date)_01');
$ytitle = t('c_(Total sales)_01');
$format = '%Y-%m-%d';
$xincline = true;
break;
}
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
// todo postgres
$col = $dbFactory->getOrderTotalDaysWhereSql($type);
$objQuery->setGroupBy('str_date');
$objQuery->setOrder('str_date');
// 検索結果の取得
$arrTotalResults = $objQuery->select($col, 'dtb_order', $where, $arrWhereVal);
$arrTotalResults = $this->lfAddBlankLine($arrTotalResults, $type, $sdate, $edate);
// todo GDない場合の処理
$tpl_image = $this->lfGetGraphLine($arrTotalResults, 'str_date', 'term_' . $type, $xtitle, $ytitle, $sdate, $edate, $xincline);
$arrTotalResults = $this->lfAddTotalLine($arrTotalResults);
return array($arrTotalResults, $tpl_image);
}
function __construct($array, $mode = '')
{
parent::__construct($array);
$objDb = new SC_Helper_DB_Ex();
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
if ($mode == '') {
// 会員本登録会員で削除していない会員
$this->setWhere('status = 2 AND del_flg = 0 ');
// 登録日を示すカラム
$regdate_col = 'dtb_customer.update_date';
}
if ($mode == 'customer') {
$this->setWhere(' del_flg = 0 ');
// 登録日を示すカラム
$regdate_col = 'dtb_customer.update_date';
}
// 会員ID
if (!isset($this->arrSql['search_customer_id'])) {
$this->arrSql['search_customer_id'] = '';
}
if (strlen($this->arrSql['search_customer_id']) > 0) {
$this->setWhere('customer_id = ?');
$this->arrVal[] = $this->arrSql['search_customer_id'];
}
// 名前
if (!isset($this->arrSql['search_name'])) {
$this->arrSql['search_name'] = '';
}
if (strlen($this->arrSql['search_name']) > 0) {
$this->setWhere('(' . $dbFactory->concatColumn(array('name01', 'name02')) . ' LIKE ?)');
$searchName = $this->addSearchStr($this->arrSql['search_name']);
$this->arrVal[] = mb_ereg_replace('[ ]+', '', $searchName);
}
// 名前(フリガナ)
if (!isset($this->arrSql['search_kana'])) {
$this->arrSql['search_kana'] = '';
}
if (strlen($this->arrSql['search_kana']) > 0) {
$this->setWhere('(' . $dbFactory->concatColumn(array('kana01', 'kana02')) . ' LIKE ?)');
$searchKana = $this->addSearchStr($this->arrSql['search_kana']);
$this->arrVal[] = mb_ereg_replace('[ ]+', '', $searchKana);
}
// 都道府県
if (!isset($this->arrSql['search_pref'])) {
$this->arrSql['search_pref'] = '';
}
if (strlen($this->arrSql['search_pref']) > 0) {
$this->setWhere('pref = ?');
$this->arrVal[] = $this->arrSql['search_pref'];
}
// 電話番号
if (!isset($this->arrSql['search_tel'])) {
$this->arrSql['search_tel'] = '';
}
if (is_numeric($this->arrSql['search_tel'])) {
$this->setWhere('(' . $dbFactory->concatColumn(array('tel01', 'tel02', 'tel03')) . ' LIKE ?)');
$searchTel = $this->addSearchStr($this->arrSql['search_tel']);
$this->arrVal[] = str_replace('-', '', $searchTel);
}
// 性別
if (!isset($this->arrSql['search_sex'])) {
$this->arrSql['search_sex'] = '';
}
if (is_array($this->arrSql['search_sex'])) {
$arrSexVal = $this->setItemTerm($this->arrSql['search_sex'], 'sex');
foreach ($arrSexVal as $data) {
$this->arrVal[] = $data;
}
}
// 職業
if (!isset($this->arrSql['search_job'])) {
$this->arrSql['search_job'] = '';
}
if (is_array($this->arrSql['search_job'])) {
if (in_array(t('c_Unknown_01'), $this->arrSql['search_job'])) {
$arrJobVal = $this->setItemTermWithNull($this->arrSql['search_job'], 'job');
} else {
$arrJobVal = $this->setItemTerm($this->arrSql['search_job'], 'job');
}
if (is_array($arrJobVal)) {
foreach ($arrJobVal as $data) {
$this->arrVal[] = $data;
}
}
}
// E-MAIL
if (!isset($this->arrSql['search_email'])) {
$this->arrSql['search_email'] = '';
}
if (strlen($this->arrSql['search_email']) > 0) {
//カンマ区切りで複数の条件指定可能に
$this->arrSql['search_email'] = explode(',', $this->arrSql['search_email']);
$sql_where = '';
foreach ($this->arrSql['search_email'] as $val) {
$val = trim($val);
//検索条件を含まない
if ($this->arrSql['not_emailinc'] == '1') {
if ($sql_where == '') {
$sql_where .= 'dtb_customer.email NOT ILIKE ? ';
} else {
$sql_where .= 'AND dtb_customer.email NOT ILIKE ? ';
}
} else {
if ($sql_where == '') {
$sql_where .= 'dtb_customer.email ILIKE ? ';
} else {
$sql_where .= 'OR dtb_customer.email ILIKE ? ';
}
}
$searchEmail = $this->addSearchStr($val);
$this->arrVal[] = $searchEmail;
}
$this->setWhere($sql_where);
}
// E-MAIL(mobile)
if (!isset($this->arrSql['search_email_mobile'])) {
$this->arrSql['search_email_mobile'] = '';
}
if (strlen($this->arrSql['search_email_mobile']) > 0) {
//カンマ区切りで複数の条件指定可能に
$this->arrSql['search_email_mobile'] = explode(',', $this->arrSql['search_email_mobile']);
$sql_where = '';
foreach ($this->arrSql['search_email_mobile'] as $val) {
$val = trim($val);
//検索条件を含まない
if ($this->arrSql['not_email_mobileinc'] == '1') {
if ($sql_where == '') {
$sql_where .= 'dtb_customer.email_mobile NOT ILIKE ? ';
} else {
$sql_where .= 'AND dtb_customer.email_mobile NOT ILIKE ? ';
}
} else {
if ($sql_where == '') {
$sql_where .= 'dtb_customer.email_mobile ILIKE ? ';
} else {
$sql_where .= 'OR dtb_customer.email_mobile ILIKE ? ';
}
}
$searchemail_mobile = $this->addSearchStr($val);
$this->arrVal[] = $searchemail_mobile;
}
$this->setWhere($sql_where);
}
// メールマガジンの場合
if ($mode == 'customer') {
// メルマガ受け取りの選択項目がフォームに存在する場合
if (isset($this->arrSql['search_htmlmail'])) {
$this->setWhere('status = 2');
if (SC_Utils_Ex::sfIsInt($this->arrSql['search_htmlmail'])) {
$this->setWhere('mailmaga_flg = ?');
$this->arrVal[] = $this->arrSql['search_htmlmail'];
} else {
// メルマガ購読拒否は省く
$this->setWhere('mailmaga_flg <> 3');
}
}
}
// 配信メールアドレス種別
if ($mode == 'customer') {
if (isset($this->arrSql['search_mail_type'])) {
$sqlEmailMobileIsEmpty = "(dtb_customer.email_mobile IS NULL OR dtb_customer.email_mobile = '')";
switch ($this->arrSql['search_mail_type']) {
// PCメールアドレス
case 1:
$this->setWhere("(dtb_customer.email <> dtb_customer.email_mobile OR {$sqlEmailMobileIsEmpty})");
break;
// 携帯メールアドレス
// 携帯メールアドレス
case 2:
$this->setWhere("NOT {$sqlEmailMobileIsEmpty}");
break;
// PCメールアドレス (携帯メールアドレスを登録している会員は除外)
// PCメールアドレス (携帯メールアドレスを登録している会員は除外)
case 3:
$this->setWhere($sqlEmailMobileIsEmpty);
break;
// 携帯メールアドレス (PCメールアドレスを登録している会員は除外)
// 携帯メールアドレス (PCメールアドレスを登録している会員は除外)
case 4:
$this->setWhere('dtb_customer.email = dtb_customer.email_mobile');
break;
}
}
}
// 購入金額指定
if (!isset($this->arrSql['search_buy_total_from'])) {
$this->arrSql['search_buy_total_from'] = '';
}
if (!isset($this->arrSql['search_buy_total_to'])) {
$this->arrSql['search_buy_total_to'] = '';
}
if (is_numeric($this->arrSql['search_buy_total_from']) || is_numeric($this->arrSql['search_buy_total_to'])) {
$arrBuyTotal = $this->selectRange($this->arrSql['search_buy_total_from'], $this->arrSql['search_buy_total_to'], 'buy_total');
foreach ($arrBuyTotal as $data) {
$this->arrVal[] = $data;
}
}
// 購入回数指定
if (!isset($this->arrSql['search_buy_times_from'])) {
$this->arrSql['search_buy_times_from'] = '';
}
if (!isset($this->arrSql['search_buy_times_to'])) {
$this->arrSql['search_buy_times_to'] = '';
}
if (is_numeric($this->arrSql['search_buy_times_from']) || is_numeric($this->arrSql['search_buy_times_to'])) {
$arrBuyTimes = $this->selectRange($this->arrSql['search_buy_times_from'], $this->arrSql['search_buy_times_to'], 'buy_times');
foreach ($arrBuyTimes as $data) {
$this->arrVal[] = $data;
}
}
// 誕生日期間指定
if (!isset($this->arrSql['search_b_start_year'])) {
$this->arrSql['search_b_start_year'] = '';
}
if (!isset($this->arrSql['search_b_start_month'])) {
$this->arrSql['search_b_start_month'] = '';
}
if (!isset($this->arrSql['search_b_start_day'])) {
$this->arrSql['search_b_start_day'] = '';
}
if (!isset($this->arrSql['search_b_end_year'])) {
$this->arrSql['search_b_end_year'] = '';
}
if (!isset($this->arrSql['search_b_end_month'])) {
$this->arrSql['search_b_end_month'] = '';
}
if (!isset($this->arrSql['search_b_end_day'])) {
$this->arrSql['search_b_end_day'] = '';
}
if (strlen($this->arrSql['search_b_start_year']) > 0 && strlen($this->arrSql['search_b_start_month']) > 0 && strlen($this->arrSql['search_b_start_day']) > 0 || strlen($this->arrSql['search_b_end_year']) > 0 && strlen($this->arrSql['search_b_end_month']) > 0 && strlen($this->arrSql['search_b_end_day']) > 0) {
$arrBirth = $this->selectTermRange($this->arrSql['search_b_start_year'], $this->arrSql['search_b_start_month'], $this->arrSql['search_b_start_day'], $this->arrSql['search_b_end_year'], $this->arrSql['search_b_end_month'], $this->arrSql['search_b_end_day'], 'birth');
foreach ($arrBirth as $data) {
$this->arrVal[] = $data;
}
}
// 誕生月の検索
if (!isset($this->arrSql['search_birth_month'])) {
$this->arrSql['search_birth_month'] = '';
}
if (is_numeric($this->arrSql['search_birth_month'])) {
$this->setWhere(' EXTRACT(month from birth) = ?');
$this->arrVal[] = $this->arrSql['search_birth_month'];
}
// 登録期間指定
if (!isset($this->arrSql['search_start_year'])) {
$this->arrSql['search_start_year'] = '';
}
if (!isset($this->arrSql['search_start_month'])) {
$this->arrSql['search_start_month'] = '';
}
if (!isset($this->arrSql['search_start_day'])) {
$this->arrSql['search_start_day'] = '';
}
if (!isset($this->arrSql['search_end_year'])) {
$this->arrSql['search_end_year'] = '';
}
if (!isset($this->arrSql['search_end_month'])) {
$this->arrSql['search_end_month'] = '';
}
if (!isset($this->arrSql['search_end_day'])) {
$this->arrSql['search_end_day'] = '';
}
if (strlen($this->arrSql['search_start_year']) > 0 && strlen($this->arrSql['search_start_month']) > 0 && strlen($this->arrSql['search_start_day']) > 0 || strlen($this->arrSql['search_end_year']) > 0 && strlen($this->arrSql['search_end_month']) > 0 && strlen($this->arrSql['search_end_day']) > 0) {
$arrRegistTime = $this->selectTermRange($this->arrSql['search_start_year'], $this->arrSql['search_start_month'], $this->arrSql['search_start_day'], $this->arrSql['search_end_year'], $this->arrSql['search_end_month'], $this->arrSql['search_end_day'], $regdate_col);
foreach ($arrRegistTime as $data) {
$this->arrVal[] = $data;
}
}
// 最終購入日指定
if (!isset($this->arrSql['search_buy_start_year'])) {
$this->arrSql['search_buy_start_year'] = '';
}
if (!isset($this->arrSql['search_buy_start_month'])) {
$this->arrSql['search_buy_start_month'] = '';
}
if (!isset($this->arrSql['search_buy_start_day'])) {
$this->arrSql['search_buy_start_day'] = '';
}
if (!isset($this->arrSql['search_buy_end_year'])) {
$this->arrSql['search_buy_end_year'] = '';
}
if (!isset($this->arrSql['search_buy_end_month'])) {
$this->arrSql['search_buy_end_month'] = '';
}
if (!isset($this->arrSql['search_buy_end_day'])) {
$this->arrSql['search_buy_end_day'] = '';
}
if (strlen($this->arrSql['search_buy_start_year']) > 0 && strlen($this->arrSql['search_buy_start_month']) > 0 && strlen($this->arrSql['search_buy_start_day']) > 0 || strlen($this->arrSql['search_buy_end_year']) > 0 && strlen($this->arrSql['search_buy_end_month']) > 0 && strlen($this->arrSql['search_buy_end_day']) > 0) {
$arrRegistTime = $this->selectTermRange($this->arrSql['search_buy_start_year'], $this->arrSql['search_buy_start_month'], $this->arrSql['search_buy_start_day'], $this->arrSql['search_buy_end_year'], $this->arrSql['search_buy_end_month'], $this->arrSql['search_buy_end_day'], 'last_buy_date');
foreach ($arrRegistTime as $data) {
$this->arrVal[] = $data;
}
}
// 購入商品コード
if (!isset($this->arrSql['search_buy_product_code'])) {
$this->arrSql['search_buy_product_code'] = '';
}
if (strlen($this->arrSql['search_buy_product_code']) > 0) {
$this->setWhere('customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_code LIKE ?) AND del_flg = 0)');
$search_buyproduct_code = $this->addSearchStr($this->arrSql['search_buy_product_code']);
$this->arrVal[] = $search_buyproduct_code;
}
// 購入商品名称
if (!isset($this->arrSql['search_buy_product_name'])) {
$this->arrSql['search_buy_product_name'] = '';
}
if (strlen($this->arrSql['search_buy_product_name']) > 0) {
$this->setWhere('customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_name LIKE ?) AND del_flg = 0)');
$search_buyproduct_name = $this->addSearchStr($this->arrSql['search_buy_product_name']);
$this->arrVal[] = $search_buyproduct_name;
}
// カテゴリを選択している場合のみ絞込検索を行う
if (!isset($this->arrSql['search_category_id'])) {
$this->arrSql['search_category_id'] = '';
}
if (strlen($this->arrSql['search_category_id']) > 0) {
// カテゴリで絞込検索を行うSQL文生成
list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere($this->arrSql['search_category_id']);
// カテゴリで絞込みが可能の場合
if ($tmp_where != '') {
$this->setWhere(' customer_id IN (SELECT distinct customer_id FROM dtb_order WHERE order_id IN (SELECT distinct order_id FROM dtb_order_detail WHERE product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ') AND del_flg = 0)) ');
$this->arrVal = array_merge((array) $this->arrVal, (array) $tmp_arrval);
}
}
// 会員状態
if (!isset($this->arrSql['search_status'])) {
$this->arrSql['search_status'] = '';
}
if (is_array($this->arrSql['search_status'])) {
$arrStatusVal = $this->setItemTerm($this->arrSql['search_status'], 'status');
foreach ($arrStatusVal as $data) {
$this->arrVal[] = $data;
}
}
$this->setOrder('customer_id DESC');
}
/**
* システム情報を取得する.
*
* @return array システム情報
*/
function getSystemInfo()
{
$objDB = SC_DB_DBFactory_Ex::getInstance();
$arrSystemInfo = array(array('title' => t('c_EC-CUBE_01'), 'value' => ECCUBE_VERSION), array('title' => t('c_Server OS_01'), 'value' => php_uname()), array('title' => t('c_DB server_01'), 'value' => $objDB->sfGetDBVersion()), array('title' => t('c_WEB server_01'), 'value' => $_SERVER['SERVER_SOFTWARE']));
$value = phpversion() . ' (' . implode(', ', get_loaded_extensions()) . ')';
$arrSystemInfo[] = array('title' => 'PHP', 'value' => $value);
if (extension_loaded('GD') || extension_loaded('gd')) {
$arrValue = array();
foreach (gd_info() as $key => $val) {
$arrValue[] = "{$key} => {$val}";
}
$value = t('c_Enabled_01') . ' (' . implode(', ', $arrValue) . ')';
} else {
$value = t('c_Inactive_01');
}
$arrSystemInfo[] = array('title' => t('c_GD_01'), 'value' => $value);
$arrSystemInfo[] = array('title' => t('c_HTTP user agent_01'), 'value' => $_SERVER['HTTP_USER_AGENT']);
return $arrSystemInfo;
}
function lfCheckDBError($objFormParam)
{
global $objPage;
// 入力データを渡す。
$arrRet = $objFormParam->getHashArray();
$objErr = new SC_CheckError($arrRet);
$objErr->arrErr = $objFormParam->checkError();
if (count($objErr->arrErr) == 0) {
if (!defined("DB_TYPE")) {
define("DB_TYPE", $arrRet['db_type']);
}
// 接続確認
$dsn = $arrRet['db_type'] . "://" . $arrRet['db_user'] . ":" . $arrRet['db_password'] . "@" . $arrRet['db_server'] . ":" . $arrRet['db_port'] . "/" . $arrRet['db_name'];
// Debugモード指定
$options['debug'] = PEAR_DB_DEBUG;
$objDB = MDB2::connect($dsn, $options);
// 接続成功
if (!PEAR::isError($objDB)) {
$dbFactory = SC_DB_DBFactory_Ex::getInstance($arrRet['db_type']);
// データベースバージョン情報の取得
$objPage->tpl_db_version = $dbFactory->sfGetDBVersion($dsn);
} else {
$objErr->arrErr['all'] = ">> " . $objDB->message . "<br />";
// エラー文を取得する
ereg("\\[(.*)\\]", $objDB->userinfo, $arrKey);
$objErr->arrErr['all'] .= $arrKey[0] . "<br />";
GC_Utils_Ex::gfPrintLog($objDB->userinfo, INSTALL_LOG);
}
}
return $objErr->arrErr;
}
/**
* 昨日のレビュー書き込み数の取得
*
* @return integer 昨日のレビュー書き込み数
*/
function lfGetReviewYesterday()
{
$objQuery =& SC_Query_Ex::getSingletonInstance();
// TODO: DBFactory使わないでも共通化できそうな気もしますが
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$sql = $dbFactory->getReviewYesterdaySql();
return $objQuery->getOne($sql);
}
/**
* データの存在チェックを行う.
*
* @param string $table_name テーブル名
* @param string $where データを検索する WHERE 句
* @param string $dsn データソース名
* @param string $sql @deprecated データの追加を行う場合の SQL文
* @param bool $add @deprecated データの追加も行う場合 true
* @return bool データが存在する場合 true, データの追加に成功した場合 true,
* $add == false で, データが存在しない場合 false
*/
function sfDataExists($table_name, $where, $arrWhereVal, $dsn = '', $sql = '', $add = false)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$dsn = $dbFactory->getDSN($dsn);
$objQuery =& SC_Query_Ex::getSingletonInstance();
$exists = $objQuery->exists($table_name, $where, $arrWhereVal);
// データが存在する場合 TRUE
if ($exists) {
return TRUE;
// $add が TRUE の場合はデータを追加する
} elseif ($add) {
return $objQuery->exec($sql);
// $add が FALSE で、データが存在しない場合 FALSE
} else {
return FALSE;
}
}
/**
* SELECT文を実行する.
*
* @param string $col カラム名. 複数カラムの場合はカンマ区切りで書く
* @param string $table テーブル名
* @param string $where WHERE句
* @param array $arrval プレースホルダ
* @return array|null
*/
function select($col, $table, $where = "", $arrval = array())
{
$sqlse = $this->getsql($col, $table, $where);
// DBに依存した SQL へ変換
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$sqlse = $dbFactory->sfChangeMySQL($sqlse);
$ret = $this->conn->getAll($sqlse, $arrval);
return $ret;
}
function lfCheckDBError($objDBParam)
{
global $objPage;
// 入力データを渡す。
$arrRet = $objDBParam->getHashArray();
$objErr = new SC_CheckError($arrRet);
$objErr->arrErr = $objDBParam->checkError();
if (count($objErr->arrErr) == 0) {
$arrDsn = getArrayDsn($objDBParam);
// Debugモード指定
$options['debug'] = PEAR_DB_DEBUG;
$objDB = MDB2::connect($arrDsn, $options);
// 接続成功
if (!PEAR::isError($objDB)) {
$dbFactory = SC_DB_DBFactory_Ex::getInstance($arrDsn['phptype']);
// データベースバージョン情報の取得
$objPage->tpl_db_version = $dbFactory->sfGetDBVersion($arrDsn);
} else {
$objErr->arrErr['all'] = '>> ' . $objDB->message . '<br />';
// エラー文を取得する
preg_match('/\\[(.*)\\]/', $objDB->userinfo, $arrKey);
$objErr->arrErr['all'] .= $arrKey[0] . '<br />';
GC_Utils_Ex::gfPrintLog($objDB->userinfo, INSTALL_LOG);
}
}
return $objErr->arrErr;
}
/**
* DBに商品データを登録する
*
* @param object $objUpFile SC_UploadFileインスタンス
* @param object $objDownFile SC_UploadFileインスタンス
* @param array $arrList フォーム入力パラメーター配列
* @return integer 登録商品ID
*/
function lfRegistProduct(&$objUpFile, &$objDownFile, $arrList)
{
$objQuery =& SC_Query_Ex::getSingletonInstance();
$objDb = new SC_Helper_DB_Ex();
// 配列の添字を定義
$checkArray = array('name', 'status', 'main_list_comment', 'main_comment', 'deliv_fee', 'comment1', 'comment2', 'comment3', 'comment4', 'comment5', 'comment6', 'sale_limit', 'deliv_date_id', 'maker_id', 'note');
$arrList = SC_Utils_Ex::arrayDefineIndexes($arrList, $checkArray);
// INSERTする値を作成する。
$sqlval['name'] = $arrList['name'];
$sqlval['status'] = $arrList['status'];
$sqlval['main_list_comment'] = $arrList['main_list_comment'];
$sqlval['main_comment'] = $arrList['main_comment'];
$sqlval['comment1'] = $arrList['comment1'];
$sqlval['comment2'] = $arrList['comment2'];
$sqlval['comment3'] = $arrList['comment3'];
$sqlval['comment4'] = $arrList['comment4'];
$sqlval['comment5'] = $arrList['comment5'];
$sqlval['comment6'] = $arrList['comment6'];
$sqlval['deliv_date_id'] = $arrList['deliv_date_id'];
$sqlval['maker_id'] = $arrList['maker_id'];
$sqlval['note'] = $arrList['note'];
$sqlval['update_date'] = 'CURRENT_TIMESTAMP';
$sqlval['creator_id'] = $_SESSION['member_id'];
$arrRet = $objUpFile->getDBFileList();
$sqlval = array_merge($sqlval, $arrRet);
for ($cnt = 1; $cnt <= PRODUCTSUB_MAX; $cnt++) {
$sqlval['sub_title' . $cnt] = $arrList['sub_title' . $cnt];
$sqlval['sub_comment' . $cnt] = $arrList['sub_comment' . $cnt];
}
$objQuery->begin();
// 新規登録(複製時を含む)
if ($arrList['product_id'] == '') {
$product_id = $objQuery->nextVal('dtb_products_product_id');
$sqlval['product_id'] = $product_id;
// INSERTの実行
$sqlval['create_date'] = 'CURRENT_TIMESTAMP';
$objQuery->insert('dtb_products', $sqlval);
$arrList['product_id'] = $product_id;
// カテゴリを更新
$objDb->updateProductCategories($arrList['category_id'], $product_id);
// 複製商品の場合には規格も複製する
if ($arrList['copy_product_id'] != '' && SC_Utils_Ex::sfIsInt($arrList['copy_product_id'])) {
if (!$arrList['has_product_class']) {
//規格なしの場合、複製は価格等の入力が発生しているため、その内容で追加登録を行う
$this->lfCopyProductClass($arrList, $objQuery);
} else {
//規格がある場合の複製は複製元の内容で追加登録を行う
// dtb_products_class のカラムを取得
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$arrColList = $objQuery->listTableFields('dtb_products_class');
$arrColList_tmp = array_flip($arrColList);
// 複製しない列
unset($arrColList[$arrColList_tmp['product_class_id']]);
//規格ID
unset($arrColList[$arrColList_tmp['product_id']]);
//商品ID
unset($arrColList[$arrColList_tmp['create_date']]);
// 複製元商品の規格データ取得
$col = SC_Utils_Ex::sfGetCommaList($arrColList);
$table = 'dtb_products_class';
$where = 'product_id = ?';
$objQuery->setOrder('product_class_id');
$arrProductsClass = $objQuery->select($col, $table, $where, array($arrList['copy_product_id']));
// 規格データ登録
$objQuery =& SC_Query_Ex::getSingletonInstance();
foreach ($arrProductsClass as $arrData) {
$sqlval = $arrData;
$sqlval['product_class_id'] = $objQuery->nextVal('dtb_products_class_product_class_id');
$sqlval['deliv_fee'] = $arrList['deliv_fee'];
$sqlval['point_rate'] = $arrList['point_rate'];
$sqlval['sale_limit'] = $arrList['sale_limit'];
$sqlval['product_id'] = $product_id;
$sqlval['create_date'] = 'CURRENT_TIMESTAMP';
$sqlval['update_date'] = 'CURRENT_TIMESTAMP';
$objQuery->insert($table, $sqlval);
}
}
}
// 更新
} else {
$product_id = $arrList['product_id'];
// 削除要求のあった既存ファイルの削除
$arrRet = $this->lfGetProductData_FromDB($arrList['product_id']);
// TODO: SC_UploadFile::deleteDBFileの画像削除条件見直し要
$objImage = new SC_Image_Ex($objUpFile->temp_dir);
$arrKeyName = $objUpFile->keyname;
$arrSaveFile = $objUpFile->save_file;
$arrImageKey = array();
foreach ($arrKeyName as $key => $keyname) {
if ($arrRet[$keyname] && !$arrSaveFile[$key]) {
$arrImageKey[] = $keyname;
$has_same_image = $this->lfHasSameProductImage($arrList['product_id'], $arrImageKey, $arrRet[$keyname]);
if (!$has_same_image) {
$objImage->deleteImage($arrRet[$keyname], $objUpFile->save_dir);
}
}
}
$objDownFile->deleteDBDownFile($arrRet);
// UPDATEの実行
$where = 'product_id = ?';
$objQuery->update('dtb_products', $sqlval, $where, array($product_id));
// カテゴリを更新
$objDb->updateProductCategories($arrList['category_id'], $product_id);
}
// 商品登録の時は規格を生成する。複製の場合は規格も複製されるのでこの処理は不要。
if ($arrList['copy_product_id'] == '') {
// 規格登録
if ($objDb->sfHasProductClass($product_id)) {
// 規格あり商品（商品規格テーブルのうち、商品登録フォームで設定するパラメーターのみ更新）
$this->lfUpdateProductClass($arrList);
} else {
// 規格なし商品（商品規格テーブルの更新）
$this->lfInsertDummyProductClass($arrList);
}
}
// 商品ステータス設定
$objProduct = new SC_Product_Ex();
$objProduct->setProductStatus($product_id, $arrList['product_status']);
// 関連商品登録
$this->lfInsertRecommendProducts($objQuery, $arrList, $product_id);
$objQuery->commit();
return $product_id;
}
/** 年代別集計 **/
public function lfGetOrderAge($type, $sdate, $edate)
{
if (DB_TYPE != 'sqlsrv') {
return parent::lfGetOrderAge($type, $sdate, $edate);
}
$objQuery = SC_Query_Ex::getSingletonInstance();
list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$col = "age";
$col .= ',COUNT(order_id) AS order_count';
$col .= ',SUM(total) AS total';
$col .= ',AVG(total) AS total_average';
$from = "(" . $dbFactory->getOrderTotalAgeColSql() . ") as t1";
$where .= ' AND del_flg = 0 AND status <> ?';
$arrWhereVal[] = ORDER_CANCEL;
$objQuery->setGroupBy('age');
$objQuery->setOrder('age DESC');
$arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal);
foreach ($arrTotalResults as $key => $value) {
$arrResult =& $arrTotalResults[$key];
$age_key = $arrResult['age'];
if ($age_key != '') {
$arrResult['age_name'] = $arrResult['age'] . '代';
} else {
$arrResult['age_name'] = '未回答';
}
}
$tpl_image = $this->lfGetGraphBar($arrTotalResults, 'age_name', 'age_' . $type, '(年齢)', '(売上合計)', $sdate, $edate);
return array($arrTotalResults, $tpl_image);
}
/**
* クエリを構築する.
*
* 検索条件のキーに応じた WHERE 句と, クエリパラメーターを構築する.
* クエリパラメーターは, SC_FormParam の入力値から取得する.
*
* 構築内容は, 引数の $where 及び $arrValues にそれぞれ追加される.
*
* @param string $key 検索条件のキー
* @param string $where 構築する WHERE 句
* @param array $arrValues 構築するクエリパラメーター
* @param SC_FormParam $objFormParam SC_FormParam インスタンス
* @return void
*/
function buildQuery($key, &$where, &$arrValues, &$objFormParam)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
switch ($key) {
case 'search_product_name':
$where .= ' AND EXISTS (SELECT 1 FROM dtb_order_detail od WHERE od.order_id = dtb_order.order_id AND od.product_name LIKE ?)';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_order_name':
$where .= ' AND ' . $dbFactory->concatColumn(array('order_name01', 'order_name02')) . ' LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_order_kana':
$where .= ' AND ' . $dbFactory->concatColumn(array('order_kana01', 'order_kana02')) . ' LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_order_id1':
$where .= ' AND order_id >= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_order_id2':
$where .= ' AND order_id <= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_order_sex':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if (SC_Utils_Ex::isBlank($tmp_where)) {
$tmp_where .= ' AND (order_sex = ?';
} else {
$tmp_where .= ' OR order_sex = ?';
}
$arrValues[] = $element;
}
}
if (!SC_Utils_Ex::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
case 'search_order_tel':
$where .= ' AND (' . $dbFactory->concatColumn(array('order_tel01', 'order_tel02', 'order_tel03')) . ' LIKE ?)';
$arrValues[] = sprintf('%%%d%%', preg_replace('/[()-]+/', '', $objFormParam->getValue($key)));
break;
case 'search_order_email':
$where .= ' AND order_email LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_payment_id':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if ($tmp_where == '') {
$tmp_where .= ' AND (payment_id = ?';
} else {
$tmp_where .= ' OR payment_id = ?';
}
$arrValues[] = $element;
}
}
if (!SC_Utils_Ex::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
case 'search_total1':
$where .= ' AND total >= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_total2':
$where .= ' AND total <= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_sorderyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_sorderyear'), $objFormParam->getValue('search_sordermonth'), $objFormParam->getValue('search_sorderday'));
$where .= ' AND create_date >= ?';
$arrValues[] = $date;
break;
case 'search_eorderyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_eorderyear'), $objFormParam->getValue('search_eordermonth'), $objFormParam->getValue('search_eorderday'), true);
$where .= ' AND create_date <= ?';
$arrValues[] = $date;
break;
case 'search_supdateyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_supdateyear'), $objFormParam->getValue('search_supdatemonth'), $objFormParam->getValue('search_supdateday'));
$where .= ' AND update_date >= ?';
$arrValues[] = $date;
break;
case 'search_eupdateyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_eupdateyear'), $objFormParam->getValue('search_eupdatemonth'), $objFormParam->getValue('search_eupdateday'), true);
$where .= ' AND update_date <= ?';
$arrValues[] = $date;
break;
case 'search_sbirthyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_sbirthyear'), $objFormParam->getValue('search_sbirthmonth'), $objFormParam->getValue('search_sbirthday'));
$where .= ' AND order_birth >= ?';
$arrValues[] = $date;
break;
case 'search_ebirthyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_ebirthyear'), $objFormParam->getValue('search_ebirthmonth'), $objFormParam->getValue('search_ebirthday'), true);
$where .= ' AND order_birth <= ?';
$arrValues[] = $date;
break;
case 'search_order_status':
$where .= ' AND status = ?';
$arrValues[] = $objFormParam->getValue($key);
break;
default:
break;
}
}
/**
* マスターデータ名を配列で取得する.
*
* @access private
* @param array $ignores 取得しないマスターデータ名の配列
* @return array マスターデータ名の配列
*/
public function getMasterDataNames($ignores = array())
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$arrMasterDataName = $dbFactory->findTableNames('mtb_');
$i = 0;
foreach ($arrMasterDataName as $val) {
foreach ($ignores as $ignore) {
if ($val == $ignore) {
unset($arrMasterDataName[$i]);
}
}
$i++;
}
return $arrMasterDataName;
}
/**
* 商品情報の読み込みを行う.
*
* @param integer $customer_id 会員ID
* @param integer $order_id 受注ID
* @param integer $product_class_id 商品規格ID
* @return string 商品情報の配列
*/
public function lfGetRealFileName($customer_id, $order_id, $product_class_id)
{
$objQuery =& SC_Query_Ex::getSingletonInstance();
$col = <<<__EOS__
pc.down_realfilename AS down_realfilename,
pc.down_filename AS down_filename
__EOS__;
$table = <<<__EOS__
dtb_order AS o
JOIN dtb_order_detail AS od USING(order_id)
JOIN dtb_products_class AS pc USING(product_id, product_class_id)
__EOS__;
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$where = 'o.customer_id = ? AND o.order_id = ? AND od.product_class_id = ?';
$where .= ' AND ' . $dbFactory->getDownloadableDaysWhereSql('o');
$where .= ' = 1';
$arrWhereVal = array($customer_id, $order_id, $product_class_id);
$arrRet = $objQuery->select($col, $table, $where, $arrWhereVal);
return $arrRet[0];
}
/**
* 商品情報の読み込みを行う.
*
* @param integer $customer_id 会員ID
* @param integer $order_id 受注ID
* @param integer $product_id 商品ID
* @param integer $product_class_id 商品規格ID
* @return array 商品情報の配列
*/
function lfGetRealFileName($customer_id, $order_id, $product_id, $product_class_id)
{
$objQuery =& SC_Query_Ex::getSingletonInstance();
$col = <<<__EOS__
pc.product_id AS product_id,
pc.product_class_id AS product_class_id,
pc.down_realfilename AS down_realfilename,
pc.down_filename AS down_filename,
o.order_id AS order_id,
o.customer_id AS customer_id,
o.payment_date AS payment_date,
o.status AS status
__EOS__;
$table = <<<__EOS__
dtb_products_class pc,
dtb_order_detail od,
dtb_order o
__EOS__;
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$where = 'o.customer_id = ? AND o.order_id = ? AND pc.product_id = ? AND pc.product_class_id = ?';
$where .= ' AND ' . $dbFactory->getDownloadableDaysWhereSql('o');
$where .= ' = 1';
$arrRet = $objQuery->select($col, $table, $where, array($customer_id, $order_id, $product_id, $product_class_id));
return $arrRet[0];
}
/**
* 受注詳細を取得する.
*
* @param integer $order_id 受注ID
* @param boolean $has_order_status 対応状況, 入金日も含める場合 true
* @return array 受注詳細の配列
*/
public function getOrderDetail($order_id, $has_order_status = true)
{
$objQuery =& SC_Query_Ex::getSingletonInstance();
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$col = <<<__EOS__
T3.product_id,
T3.product_class_id as product_class_id,
T3.product_type_id AS product_type_id,
T2.product_code,
T2.product_name,
T2.classcategory_name1 AS classcategory_name1,
T2.classcategory_name2 AS classcategory_name2,
T2.price,
T2.quantity,
T2.point_rate,
T2.tax_rate,
T2.tax_rule,
__EOS__;
if ($has_order_status) {
$col .= 'T1.status AS status, T1.payment_date AS payment_date,';
}
$col .= <<<__EOS__
CASE WHEN
EXISTS(
SELECT * FROM dtb_products
WHERE product_id = T3.product_id
AND del_flg = 0
AND status = 1
)
THEN '1'
ELSE '0'
END AS enable,
__EOS__;
$col .= $dbFactory->getDownloadableDaysWhereSql('T1') . ' AS effective';
$from = <<<__EOS__
dtb_order T1
JOIN dtb_order_detail T2
ON T1.order_id = T2.order_id
LEFT JOIN dtb_products_class T3
ON T2.product_class_id = T3.product_class_id
__EOS__;
$objQuery->setOrder('T2.order_detail_id');
return $objQuery->select($col, $from, 'T1.order_id = ?', array($order_id));
}
/**
* 商品詳細の SQL を取得する.
*
* @param string $where_products_class 商品規格情報の WHERE 句
* @return string 商品詳細の SQL
*/
public function alldtlSQL($where_products_class = '')
{
$objDBFactory = SC_DB_DBFactory_Ex::getInstance();
return $objDBFactory->alldtlSQL($where_products_class);
}
function lfGetOrderTerm($type, $sdate, $edate)
{
if (DB_TYPE != 'sqlsrv') {
return parent::lfGetOrderTerm($type, $sdate, $edate);
} else {
$objQuery = SC_Query_Ex::getSingletonInstance();
list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate);
$where .= " AND del_flg = 0 AND status <> " . ORDER_CANCEL;
switch ($type) {
case 'month':
$xtitle = "(月別)";
$ytitle = "(売上合計)";
$format = '%m';
break;
case 'year':
$xtitle = "(年別)";
$ytitle = "(売上合計)";
$format = '%Y';
break;
case 'wday':
$xtitle = "(曜日別)";
$ytitle = "(売上合計)";
$format = '%a';
break;
case 'hour':
$xtitle = "(時間別)";
$ytitle = "(売上合計)";
$format = '%H';
break;
default:
$xtitle = "(日別)";
$ytitle = "(売上合計)";
$format = '%Y-%m-%d';
break;
}
// TODO リファクタリング
switch ($type) {
case 'month':
$format = 'datepart(mm, create_date)';
break;
case 'year':
$format = 'datepart(yyyy, create_date)';
break;
case 'wday':
$format = 'datename(weekday, create_date)';
break;
case 'hour':
$format = 'datepart(hh, create_date)';
break;
default:
$format = 'convert(varchar(10), create_date, 111)';
break;
}
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
// todo postgres
$col = $dbFactory->getOrderTotalDaysWhereSql($format);
$objQuery->setGroupBy($format);
$objQuery->setOrder($format);
// 検索結果の取得
$arrTotalResults = $objQuery->select($col, 'dtb_order', $where);
$arrTotalResults = $this->lfAddBlankLine($arrTotalResults, $type, $sdate, $edate);
// todo GDない場合の処理
$tpl_image = $this->lfGetGraphLine($arrTotalResults, 'str_date', "term_" . $type, $xtitle, $ytitle, $sdate, $edate);
$arrTotalResults = $this->lfAddTotalLine($arrTotalResults);
return array($arrTotalResults, $tpl_image);
}
}
function lfRegistProduct($arrList)
{
$objQuery = new SC_Query();
$objDb = new SC_Helper_DB_Ex();
$objQuery->begin();
// 配列の添字を定義
$checkArray = array("name", "status", "product_flag", "main_list_comment", "main_comment", "point_rate", "deliv_fee", "comment1", "comment2", "comment3", "comment4", "comment5", "comment6", "main_list_comment", "sale_limit", "sale_unlimited", "deliv_date_id", "note");
$arrList = SC_Utils_Ex::arrayDefineIndexes($arrList, $checkArray);
// INSERTする値を作成する。
$sqlval['name'] = $arrList['name'];
$sqlval['status'] = $arrList['status'];
$sqlval['product_flag'] = $arrList['product_flag'];
$sqlval['main_list_comment'] = $arrList['main_list_comment'];
$sqlval['main_comment'] = $arrList['main_comment'];
$sqlval['point_rate'] = $arrList['point_rate'];
$sqlval['deliv_fee'] = $arrList['deliv_fee'];
$sqlval['comment1'] = $arrList['comment1'];
$sqlval['comment2'] = $arrList['comment2'];
$sqlval['comment3'] = $arrList['comment3'];
$sqlval['comment4'] = $arrList['comment4'];
$sqlval['comment5'] = $arrList['comment5'];
$sqlval['comment6'] = $arrList['comment6'];
$sqlval['main_list_comment'] = $arrList['main_list_comment'];
$sqlval['sale_limit'] = $arrList['sale_limit'];
$sqlval['sale_unlimited'] = $arrList['sale_unlimited'];
$sqlval['deliv_date_id'] = $arrList['deliv_date_id'];
$sqlval['note'] = $arrList['note'];
$sqlval['update_date'] = "Now()";
$sqlval['creator_id'] = $_SESSION['member_id'];
$arrRet = $this->objUpFile->getDBFileList();
$sqlval = array_merge($sqlval, $arrRet);
$arrList['category_id'] = unserialize($arrList['category_id']);
for ($cnt = 1; $cnt <= PRODUCTSUB_MAX; $cnt++) {
$sqlval['sub_title' . $cnt] = $arrList['sub_title' . $cnt];
$sqlval['sub_comment' . $cnt] = $arrList['sub_comment' . $cnt];
}
if ($arrList['product_id'] == "") {
// product_id 取得（PostgreSQLの場合）
if (DB_TYPE == 'pgsql') {
$product_id = $objQuery->nextval("dtb_products", "product_id");
$sqlval['product_id'] = $product_id;
}
// INSERTの実行
$sqlval['create_date'] = "Now()";
$objQuery->insert("dtb_products", $sqlval);
// product_id 取得（MySQLの場合）
if (DB_TYPE == 'mysql') {
$product_id = $objQuery->nextval("dtb_products", "product_id");
}
// カテゴリを更新
$objDb->updateProductCategories($arrList['category_id'], $product_id);
// コピー商品の場合には規格もコピーする
if ($_POST["copy_product_id"] != "" and SC_Utils_Ex::sfIsInt($_POST["copy_product_id"])) {
if ($this->tpl_nonclass) {
//規格なしの場合、コピーは価格等の入力が発生しているため、その内容で追加登録を行う
$arrList['product_id'] = $product_id;
$this->lfCopyProductClass($arrList, $objQuery);
} else {
//規格がある場合のコピーは複製元の内容で追加登録を行う
// dtb_products_class のカラムを取得
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$arrColList = $dbFactory->sfGetColumnList("dtb_products_class", $objQuery);
$arrColList_tmp = array_flip($arrColList);
// コピーしない列
unset($arrColList[$arrColList_tmp["product_class_id"]]);
//規格ID
unset($arrColList[$arrColList_tmp["product_id"]]);
//商品ID
unset($arrColList[$arrColList_tmp["create_date"]]);
$col = SC_Utils_Ex::sfGetCommaList($arrColList);
$objQuery->query("INSERT INTO dtb_products_class (product_id, create_date, " . $col . ") SELECT ?, now(), " . $col . " FROM dtb_products_class WHERE product_id = ? ORDER BY product_class_id", array($product_id, $_POST["copy_product_id"]));
}
}
} else {
$product_id = $arrList['product_id'];
// 削除要求のあった既存ファイルの削除
$arrRet = $this->lfGetProduct($arrList['product_id']);
$this->objUpFile->deleteDBFile($arrRet);
// UPDATEの実行
$where = "product_id = ?";
$objQuery->update("dtb_products", $sqlval, $where, array($product_id));
// カテゴリを更新
$objDb->updateProductCategories($arrList['category_id'], $product_id);
}
//商品登録の時は規格を生成する。複製の場合は規格も複製されるのでこの処理は不要。
if ($_POST["copy_product_id"] == "") {
// 規格登録
SC_Utils_Ex::sfInsertProductClass($objQuery, $arrList, $product_id, $arrList['product_class_id']);
}
// おすすめ商品登録
$this->lfInsertRecommendProducts($objQuery, $arrList, $product_id);
$objQuery->commit();
return $product_id;
}
/**
* 構築した SELECT 文を取得する.
*
* クラス変数から WHERE 句を組み立てる場合、$arrWhereVal を経由してプレースホルダもクラス変数のもので上書きする。
* @param string $cols SELECT 文に含めるカラム名
* @param string $from SELECT 文に含めるテーブル名
* @param string $where SELECT 文に含める WHERE 句
* @param mixed $arrWhereVal プレースホルダ(参照)
* @return string 構築済みの SELECT 文
*/
function getSql($cols, $from = '', $where = '', &$arrWhereVal = null)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$sqlse = "SELECT {$cols}";
if (strlen($from) === 0) {
$sqlse .= ' ' . $dbFactory->getDummyFromClauseSql();
} else {
$sqlse .= " FROM {$from}";
}
// 引数の$whereを優先する。
if (strlen($where) >= 1) {
$sqlse .= " WHERE {$where}";
} elseif (strlen($this->where) >= 1) {
$sqlse .= ' WHERE ' . $this->where;
// 実行時と同じくキャストしてから評価する (空文字を要素1の配列と評価させる意図)
$arrWhereValForEval = (array) $arrWhereVal;
if (empty($arrWhereValForEval)) {
$arrWhereVal = $this->arrWhereVal;
}
}
$sqlse .= ' ' . $this->groupby . ' ' . $this->order . ' ' . $this->option;
return $sqlse;
}
/**
* データの存在チェックを行う.
*
* @param string $table_name テーブル名
* @param string $where データを検索する WHERE 句
* @param string $dsn データソース名
* @param string $sql データの追加を行う場合の SQL文
* @param bool $add データの追加も行う場合 true
* @return bool データが存在する場合 true, データの追加に成功した場合 true,
* $add == false で, データが存在しない場合 false
*/
function sfDataExists($table_name, $where, $arrval, $dsn = "", $sql = "", $add = false)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
$dsn = $dbFactory->getDSN($dsn);
$objQuery =& SC_Query_Ex::getSingletonInstance();
$count = $objQuery->count($table_name, $where, $arrval);
if ($count > 0) {
$ret = true;
} else {
$ret = false;
}
// データを追加する
if (!$ret && $add) {
$objQuery->exec($sql);
}
return $ret;
}
/**
* クエリを構築する.
*
* 検索条件のキーに応じた WHERE 句と, クエリパラメーターを構築する.
* クエリパラメーターは, SC_FormParam の入力値から取得する.
*
* 構築内容は, 引数の $where 及び $arrValues にそれぞれ追加される.
*
* @param string $key 検索条件のキー
* @param string $where 構築する WHERE 句
* @param array $arrValues 構築するクエリパラメーター
* @param SC_FormParam $objFormParam SC_FormParam インスタンス
* @param SC_FormParam $objDb SC_Helper_DB_Ex インスタンス
* @return void
*/
function buildQuery($key, &$where, &$arrValues, &$objFormParam, &$objDb)
{
$dbFactory = SC_DB_DBFactory_Ex::getInstance();
switch ($key) {
// 商品ID
case 'search_product_id':
$where .= ' AND product_id = ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
// 商品コード
// 商品コード
case 'search_product_code':
$where .= ' AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code ILIKE ?)';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
// 商品名
// 商品名
case 'search_name':
$where .= ' AND name LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
// カテゴリ
// カテゴリ
case 'search_category_id':
list($tmp_where, $tmp_Values) = $objDb->sfGetCatWhere($objFormParam->getValue($key));
if ($tmp_where != '') {
$where .= ' AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
$arrValues = array_merge((array) $arrValues, (array) $tmp_Values);
}
break;
// 種別
// 種別
case 'search_status':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if (SC_Utils_Ex::isBlank($tmp_where)) {
$tmp_where .= ' AND (status = ?';
} else {
$tmp_where .= ' OR status = ?';
}
$arrValues[] = $element;
}
}
if (!SC_Utils_Ex::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
// 登録・更新日(開始)
// 登録・更新日(開始)
case 'search_startyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_startyear'), $objFormParam->getValue('search_startmonth'), $objFormParam->getValue('search_startday'));
$where .= ' AND update_date >= ?';
$arrValues[] = $date;
break;
// 登録・更新日(終了)
// 登録・更新日(終了)
case 'search_endyear':
$date = SC_Utils_Ex::sfGetTimestamp($objFormParam->getValue('search_endyear'), $objFormParam->getValue('search_endmonth'), $objFormParam->getValue('search_endday'), true);
$where .= ' AND update_date <= ?';
$arrValues[] = $date;
break;
// 商品ステータス
// 商品ステータス
case 'search_product_statuses':
$arrPartVal = $objFormParam->getValue($key);
$count = count($arrPartVal);
if ($count >= 1) {
$where .= ' ' . 'AND product_id IN (' . ' SELECT product_id FROM dtb_product_status WHERE product_status_id IN (' . SC_Utils_Ex::repeatStrWithSeparator('?', $count) . ')' . ')';
$arrValues = array_merge($arrValues, $arrPartVal);
}
break;
default:
break;
}
}
/**
* Page のプロセス.
*
* @return void
*/
function process()
{
$objView = new SC_AdminView();
$objDbFactory = SC_DB_DBFactory_Ex::getInstance();
$objCSV = new SC_Helper_CSV_Ex();
$this->arrSubnavi = $objCSV->arrSubnavi;
// 認証可否の判定
$objSess = new SC_Session();
SC_Utils_Ex::sfIsSuccess($objSess);
if (!isset($_POST['mode'])) {
$_POST['mode'] = "";
}
if (!isset($_POST['sql_id'])) {
$_POST['sql_id'] = "";
}
if (!isset($_GET['sql_id'])) {
$_GET['sql_id'] = "";
}
if (!isset($_POST['selectTable'])) {
$_POST['selectTable'] = "";
}
// SQL_IDの取得
if ($_POST['sql_id'] != "") {
$sql_id = $_POST['sql_id'];
} elseif ($_GET['sql_id'] != "") {
$sql_id = $_GET['sql_id'];
} else {
$sql_id = "";
}
$mode = $_POST['mode'];
switch ($_POST['mode']) {
// データの登録
case "confirm":
// エラーチェック
$this->arrErr = $this->lfCheckError($_POST);
if (count($this->arrErr) <= 0) {
// データの更新
$sql_id = $this->lfUpdData($sql_id, $_POST);
// 完了メッセージ表示
$this->tpl_onload = "alert('登録が完了しました。');";
}
break;
// 確認画面
// 確認画面
case "preview":
// SQL文表示
$sql = "SELECT \n" . $_POST['csv_sql'];
// FIXME
$this->sql = $sql;
// エラー表示
$objErrMsg = $this->lfCheckSQL($_POST);
if ($objErrMsg != "") {
$errMsg = $objErrMsg->message . "\n" . $objErrMsg->userinfo;
}
$this->sqlerr = isset($errMsg) ? $errMsg : "";
$this->objView = $objView;
// 画面の表示
$objView->assignobj($this);
$objView->display('contents/csv_sql_view.tpl');
exit;
break;
// 新規作成
// 新規作成
case "new_page":
$this->sendRedirect($this->getLocation("./csv_sql.php"));
exit;
break;
// データ削除
// データ削除
case "delete":
$this->lfDelData($sql_id);
$this->sendRedirect($this->getLocation("./csv_sql.php"));
exit;
break;
case "csv_output":
// CSV出力データ取得
$arrCsvData = $this->lfGetSqlList(" WHERE sql_id = ?", array($_POST['csv_output_id']));
$objQuery = new SC_Query();
$arrCsvOutputData = $objQuery->getall("SELECT " . $arrCsvData[0]['csv_sql']);
if (count($arrCsvOutputData) > 0) {
$arrKey = array_keys(SC_Utils_Ex::sfSwapArray($arrCsvOutputData));
$i = 0;
$header = "";
foreach ($arrKey as $data) {
if ($i != 0) {
$header .= ", ";
}
$header .= $data;
$i++;
}
$header .= "\r\n";
$data = SC_Utils_Ex::getCSVData($arrCsvOutputData, $arrKey);
// CSV出力
SC_Utils_Ex::sfCSVDownload($header . $data);
exit;
break;
} else {
$this->tpl_onload = "alert('出力データがありません。');";
$sql_id = "";
$_POST = "";
}
break;
}
// mode が confirm 以外のときは完了メッセージは出力しない
if ($mode != "confirm" and $mode != "csv_output") {
$this->tpl_onload = "";
}
// 登録済みSQL一覧取得
$arrSqlList = $this->lfGetSqlList();
// 編集用SQLデータの取得
if ($sql_id != "") {
$arrSqlData = $this->lfGetSqlList(" WHERE sql_id = ?", array($sql_id));
}
// テーブル一覧を取得する
$arrTableList = $this->lfGetTableList();
$arrTableList = SC_Utils_Ex::sfSwapArray($arrTableList);
// 現在選択されているテーブルを取得する
if ($_POST['selectTable'] == "") {
$selectTable = $arrTableList['table_name'][0];
} else {
$selectTable = $_POST['selectTable'];
}
// カラム一覧を取得する
$arrColList = $this->lfGetColumnList($selectTable);
$arrColList = SC_Utils_Ex::sfSwapArray($arrColList);
// 表示させる内容を編集
foreach ($arrTableList['description'] as $key => $val) {
$arrTableList['description'][$key] = $arrTableList['table_name'][$key] . "：" . $arrTableList['description'][$key];
}
foreach ($arrColList['description'] as $key => $val) {
$arrColList['description'][$key] = $arrColList['column_name'][$key] . "：" . $arrColList['description'][$key];
}
$arrDiff = array_diff($objDbFactory->sfGetColumnList($selectTable), $arrColList["column_name"]);
$arrColList["column_name"] = array_merge($arrColList["column_name"], $arrDiff);
$arrColList["description"] = array_merge($arrColList["description"], $arrDiff);
// テンプレートに出力するデータをセット
$this->arrSqlList = $arrSqlList;
// SQL一覧
$this->arrTableList = SC_Utils_Ex::sfarrCombine($arrTableList['table_name'], $arrTableList['description']);
// テーブル一覧
$this->arrColList = SC_Utils_Ex::sfarrCombine($arrColList['column_name'], $arrColList['description']);
// カラム一覧
$this->selectTable = $selectTable;
// 選択されているテーブル
$this->sql_id = $sql_id;
// 選択されているSQL
// POSTされたデータをセットする
if (isset($_POST['sql_name']) && isset($_POST['csv_sql'])) {
$arrSqlData[0]['sql_name'] = isset($_POST['sql_name']) ? $_POST['sql_name'] : "";
$arrSqlData[0]['csv_sql'] = isset($_POST['csv_sql']) ? $_POST['csv_sql'] : "";
}
$this->arrSqlData = $arrSqlData[0];
// 選択されているSQLデータ
// 画面の表示
$objView->assignobj($this);
$objView->display(MAIN_FRAME);
}
