Example #1
0
 /**
  * コンストラクタ.
  *
  * @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;
 }
Example #2
0
 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();
 }
Example #6
0
 /**
  * カラムの存在チェックと作成を行う.
  *
  * チェック対象のテーブルに, 該当のカラムが存在するかチェックする.
  * 引数 $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;
 }
Example #12
0
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);
 }
Example #14
0
 /**
  * データの存在チェックを行う.
  *
  * @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;
     }
 }
Example #15
0
 /**
  * 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));
    }
Example #24
0
 /**
  * 商品詳細の 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;
 }
Example #27
0
 /**
  * 構築した 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;
 }
Example #28
0
 /**
  * データの存在チェックを行う.
  *
  * @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);
 }