/**
  * 検索条件のwhere文とかを取得
  *
  * @return array
  */
 public function lfGetSearchCondition($arrSearchData)
 {
     $searchCondition = array('where' => '', 'arrval' => array(), 'where_category' => '', 'arrvalCategory' => array());
     // カテゴリからのWHERE文字列取得
     if ($arrSearchData['category_id'] != 0) {
         list($searchCondition['where_category'], $searchCondition['arrvalCategory']) = SC_Helper_DB_Ex::sfGetCatWhere($arrSearchData['category_id']);
     }
     // ▼対象商品IDの抽出
     // 商品検索条件の作成(未削除、表示)
     $searchCondition['where'] = 'alldtl.del_flg = 0 AND alldtl.status = 1 ';
     if (strlen($searchCondition['where_category']) >= 1) {
         $searchCondition['where'] .= ' AND EXISTS (SELECT * FROM dtb_product_categories WHERE ' . $searchCondition['where_category'] . ' AND product_id = alldtl.product_id)';
         $searchCondition['arrval'] = array_merge($searchCondition['arrval'], $searchCondition['arrvalCategory']);
     }
     // 商品名をwhere文に
     $name = $arrSearchData['name'];
     $name = str_replace(',', '', $name);
     // 全角スペースを半角スペースに変換
     $name = str_replace(' ', ' ', $name);
     // スペースでキーワードを分割
     $names = preg_split('/ +/', $name);
     // 分割したキーワードを一つずつwhere文に追加
     foreach ($names as $val) {
         if (strlen($val) > 0) {
             $searchCondition['where'] .= ' AND ( alldtl.name ILIKE ? OR alldtl.comment3 ILIKE ? OR A.name ILIKE ? OR B.name ILIKE ?) ';
             $searchCondition['arrval'][] = "%{$val}%";
             $searchCondition['arrval'][] = "%{$val}%";
             $searchCondition['arrval'][] = "%{$val}%";
             $searchCondition['arrval'][] = "%{$val}%";
         }
     }
     // メーカーらのWHERE文字列取得
     if ($arrSearchData['maker_id']) {
         $searchCondition['where'] .= ' AND alldtl.maker_id = ? ';
         $searchCondition['arrval'][] = $arrSearchData['maker_id'];
     }
     $searchCondition['where_for_count'] = $searchCondition['where'];
     // 在庫無し商品の非表示
     if (NOSTOCK_HIDDEN) {
         $searchCondition['where'] .= ' AND EXISTS(SELECT * FROM dtb_products_class WHERE product_id = alldtl.product_id AND del_flg = 0 AND (stock >= 1 OR stock_unlimited = 1))';
         $searchCondition['where_for_count'] .= ' AND EXISTS(SELECT * FROM dtb_products_class WHERE product_id = alldtl.product_id AND del_flg = 0 AND (stock >= 1 OR stock_unlimited = 1))';
     }
     return $searchCondition;
 }
 /**
  * @param SC_Helper_DB_Ex $objDb
  */
 public function lfGetProducts(&$objDb)
 {
     $where = 'del_flg = 0';
     $arrWhereVal = array();
     /* 入力エラーなし */
     foreach ($this->arrForm as $key => $val) {
         if ($val == '') {
             continue;
         }
         switch ($key) {
             case 'search_name':
                 $where .= ' AND name ILIKE ?';
                 $arrWhereVal[] = "%{$val}%";
                 break;
             case 'search_category_id':
                 list($tmp_where, $arrTmp) = $objDb->sfGetCatWhere($val);
                 if ($tmp_where != '') {
                     $where .= ' AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
                     $arrWhereVal = array_merge((array) $arrWhereVal, (array) $arrTmp);
                 }
                 break;
             case 'search_product_code':
                 $where .= ' AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code LIKE ?)';
                 $arrWhereVal[] = "{$val}%";
                 break;
             default:
                 break;
         }
     }
     $order = 'update_date DESC, product_id DESC ';
     $objQuery =& SC_Query_Ex::getSingletonInstance();
     // 行数の取得
     $linemax = $objQuery->count('dtb_products', $where, $arrWhereVal);
     $this->tpl_linemax = $linemax;
     // 何件が該当しました。表示用
     // ページ送りの処理
     $page_max = SC_Utils_Ex::sfGetSearchPageMax($_POST['search_page_max']);
     // ページ送りの取得
     $objNavi = new SC_PageNavi_Ex($_POST['search_pageno'], $linemax, $page_max, 'eccube.moveSearchPage', NAVI_PMAX);
     $this->tpl_strnavi = $objNavi->strnavi;
     // 表示文字列
     $startno = $objNavi->start_row;
     // 取得範囲の指定(開始行番号、行数のセット)
     $objQuery->setLimitOffset($page_max, $startno);
     // 表示順序
     $objQuery->setOrder($order);
     // 検索結果の取得
     // FIXME 商品コードの表示
     $arrProducts = $objQuery->select('*', SC_Product_Ex::alldtlSQL(), $where, $arrWhereVal);
     return $arrProducts;
 }
 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $conn = new SC_DBConn();
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objDb = new SC_Helper_DB_Ex();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     if ($_POST['mode'] == "search") {
         // POST値の引き継ぎ
         $this->arrForm = $_POST;
         // 入力文字の強制変換
         $this->lfConvertParam();
         $where = "del_flg = 0 AND status = 1";
         /* 入力エラーなし */
         foreach ($this->arrForm as $key => $val) {
             if ($val == "") {
                 continue;
             }
             switch ($key) {
                 case 'search_name':
                     $where .= " AND name ILIKE ?";
                     $arrval[] = "%{$val}%";
                     break;
                 case 'search_category_id':
                     list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere($val);
                     if ($tmp_where != "") {
                         $where .= " AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE " . $tmp_where . ")";
                         $arrval = array_merge((array) $arrval, (array) $tmp_arrval);
                     }
                     break;
                 case 'search_product_code':
                     $where .= " AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code LIKE ? GROUP BY product_id)";
                     $arrval[] = "{$val}%";
                     break;
                 default:
                     break;
             }
         }
         $order = "update_date DESC, product_id DESC";
         // 読み込む列とテーブルの指定
         $col = "product_id, name, category_id, main_list_image, status, product_code, price01, stock, stock_unlimited";
         $from = "vw_products_nonclass AS noncls ";
         $objQuery = new SC_Query();
         // 行数の取得
         if (empty($arrval)) {
             $arrval = array();
         }
         $linemax = $objQuery->count("dtb_products", $where, $arrval);
         $this->tpl_linemax = $linemax;
         // 何件が該当しました。表示用
         // ページ送りの処理
         if (isset($_POST['search_page_max']) && is_numeric($_POST['search_page_max'])) {
             $page_max = $_POST['search_page_max'];
         } else {
             $page_max = SEARCH_PMAX;
         }
         // ページ送りの取得
         $objNavi = new SC_PageNavi($_POST['search_pageno'], $linemax, $page_max, "fnNaviSearchOnlyPage", NAVI_PMAX);
         $this->tpl_strnavi = $objNavi->strnavi;
         // 表示文字列
         $startno = $objNavi->start_row;
         // 取得範囲の指定(開始行番号、行数のセット)
         $objQuery->setlimitoffset($page_max, $startno);
         // 表示順序
         $objQuery->setorder($order);
         // 検索結果の取得
         $this->arrProducts = $objQuery->select($col, $from, $where, $arrval);
     }
     // カテゴリ取得
     $this->arrCatList = $objDb->sfGetCategoryList();
     //---- ページ表示
     $objView->assignobj($this);
     $objView->display($this->tpl_mainpage);
 }
 /**
  *
  * POSTされた値からSQLのWHEREとBINDを配列で返す。
  * @return array        ('where' => where string, 'bind' => databind array)
  * @param  SC_FormParam $objFormParam
  * @param SC_Helper_DB_Ex $objDb
  */
 public function createWhere(&$objFormParam, &$objDb)
 {
     $arrForm = $objFormParam->getHashArray();
     $where = 'alldtl.del_flg = 0';
     $bind = array();
     foreach ($arrForm as $key => $val) {
         if ($val == '') {
             continue;
         }
         switch ($key) {
             case 'search_name':
                 $where .= ' AND name ILIKE ?';
                 $bind[] = '%' . $val . '%';
                 break;
             case 'search_category_id':
                 list($tmp_where, $tmp_bind) = $objDb->sfGetCatWhere($val);
                 if ($tmp_where != '') {
                     $where .= ' AND alldtl.product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
                     $bind = array_merge((array) $bind, (array) $tmp_bind);
                 }
                 break;
             case 'search_product_code':
                 $where .= ' AND alldtl.product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code LIKE ? AND del_flg = 0 GROUP BY product_id)';
                 $bind[] = '%' . $val . '%';
                 break;
             default:
                 break;
         }
     }
     return array('where' => $where, 'bind' => $bind);
 }
Ejemplo n.º 5
0
 function SC_CustomerList($array, $mode = '')
 {
     parent::SC_SelectSql($array);
     $masterData = new SC_DB_MasterData_Ex();
     $arrMobileDomain = $masterData->getMasterData("mtb_mobile_domain");
     $objDb = new SC_Helper_DB_Ex();
     if ($mode == "") {
         // 会員本登録会員で削除していない会員
         $this->setWhere("status = 2 AND del_flg = 0 ");
         // 登録日を示すカラム
         $regdate_col = 'dtb_customer.update_date';
     }
     if ($mode == "customer") {
         // 管理者ページ顧客検索の場合仮登録会員も検索
         //$this->setWhere( "(status = 1 OR status = 2) AND del_flg = 0 ");
         $this->setWhere(" del_flg = 0 ");
         // 登録日を示すカラム
         $regdate_col = 'dtb_customer.update_date';
     }
     // メールマガジンの場合
     if ($mode == "magazine") {
         $this->setWhere("(del_flg = 0 OR del_flg IS NULL)");
         $this->setWhere("status = 2");
         // 登録日を示すカラム
         $regdate_col = 'dtb_customer.create_date';
     }
     // 顧客ID
     if (!isset($this->arrSql['customer_id'])) {
         $this->arrSql['customer_id'] = "";
     }
     if (strlen($this->arrSql['customer_id']) > 0) {
         $this->setWhere("customer_id =  ?");
         $this->arrVal[] = $this->arrSql['customer_id'];
     }
     // 名前
     if (!isset($this->arrSql['name'])) {
         $this->arrSql['name'] = "";
     }
     if (strlen($this->arrSql['name']) > 0) {
         if (DB_TYPE == "pgsql") {
             $this->setWhere("(name01 || name02 LIKE ?)");
         } elseif (DB_TYPE == "mysql") {
             $this->setWhere("concat(name01,name02) LIKE ?");
         }
         $searchName = $this->addSearchStr($this->arrSql['name']);
         $this->arrVal[] = mb_ereg_replace("[  ]+", "", $searchName);
     }
     // 名前(カナ)
     if (!isset($this->arrSql['kana'])) {
         $this->arrSql['kana'] = "";
     }
     if (strlen($this->arrSql['kana']) > 0) {
         if (DB_TYPE == "pgsql") {
             $this->setWhere("(kana01 || kana02 LIKE ?)");
         } elseif (DB_TYPE == "mysql") {
             $this->setWhere("concat(kana01,kana02) LIKE ?");
         }
         $searchKana = $this->addSearchStr($this->arrSql['kana']);
         $this->arrVal[] = mb_ereg_replace("[  ]+", "", $searchKana);
     }
     // 都道府県
     if (!isset($this->arrSql['pref'])) {
         $this->arrSql['pref'] = "";
     }
     if (strlen($this->arrSql['pref']) > 0) {
         $this->setWhere("pref = ?");
         $this->arrVal[] = $this->arrSql['pref'];
     }
     // 電話番号
     if (!isset($this->arrSql['tel'])) {
         $this->arrSql['tel'] = "";
     }
     if (is_numeric($this->arrSql['tel'])) {
         if (DB_TYPE == "pgsql") {
             $this->setWhere("(tel01 || tel02 || tel03 LIKE ?)");
         } elseif (DB_TYPE == "mysql") {
             $this->setWhere("concat(tel01,tel02,tel03) LIKE ?");
         }
         $searchTel = $this->addSearchStr($this->arrSql['tel']);
         $this->arrVal[] = ereg_replace("-", "", $searchTel);
     }
     //性別
     if (!isset($this->arrSql['sex'])) {
         $this->arrSql['sex'] = "";
     }
     if (is_array($this->arrSql['sex'])) {
         $arrSexVal = $this->setItemTerm($this->arrSql['sex'], "sex");
         foreach ($arrSexVal as $data) {
             $this->arrVal[] = $data;
         }
     }
     //職業
     if (!isset($this->arrSql['job'])) {
         $this->arrSql['job'] = "";
     }
     if (is_array($this->arrSql['job'])) {
         if (in_array("不明", $this->arrSql['job'])) {
             $arrJobVal = $this->setItemTermWithNull($this->arrSql['job'], "job");
         } else {
             $arrJobVal = $this->setItemTerm($this->arrSql['job'], "job");
         }
         if (is_array($arrJobVal)) {
             foreach ($arrJobVal as $data) {
                 $this->arrVal[] = $data;
             }
         }
     }
     // E-MAIL
     if (!isset($this->arrSql['email'])) {
         $this->arrSql['email'] = "";
     }
     if (strlen($this->arrSql['email']) > 0) {
         //カンマ区切りで複数の条件指定可能に
         $this->arrSql['email'] = explode(",", $this->arrSql['email']);
         $sql_where = "";
         foreach ($this->arrSql['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['email_mobile'])) {
         $this->arrSql['email_mobile'] = "";
     }
     if (strlen($this->arrSql['email_mobile']) > 0) {
         //カンマ区切りで複数の条件指定可能に
         $this->arrSql['email_mobile'] = explode(",", $this->arrSql['email_mobile']);
         $sql_where = "";
         foreach ($this->arrSql['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 == 'magazine') {
         if (!isset($this->arrSql['mail_type'])) {
             $this->arrSql['mail_type'] = "";
         }
         // PCサイトメールが指定されている場合
         if (strlen($this->arrSql['mail_type']) > 0 && $this->arrSql['mail_type'] == 1) {
             // 携帯ドメインを外す。
             foreach ($arrMobileDomain as $mobile_domain) {
                 $this->setWhere(" dtb_customer.email NOT ILIKE '%{$mobile_domain}' ");
             }
             // 携帯サイトメールが指定されている場合
         } else {
             if (strlen($this->arrSql['mail_type']) > 0 && $this->arrSql['mail_type'] == 2) {
                 $this->setWhere(" dtb_customer.email_mobile <> ''  ");
             }
         }
     }
     // HTML-mail
     if ($mode == 'magazine') {
         if (!isset($this->arrSql['htmlmail'])) {
             $this->arrSql['htmlmail'] = "";
         }
         if (strlen($this->arrSql['htmlmail']) > 0) {
             $this->setWhere(" mailmaga_flg = ? ");
             $this->arrVal[] = $this->arrSql['htmlmail'];
         } else {
             $this->setWhere(" (mailmaga_flg = 1 or mailmaga_flg = 2) ");
         }
     }
     // 購入金額指定
     if (!isset($this->arrSql['buy_total_from'])) {
         $this->arrSql['buy_total_from'] = "";
     }
     if (!isset($this->arrSql['buy_total_to'])) {
         $this->arrSql['buy_total_to'] = "";
     }
     if (is_numeric($this->arrSql["buy_total_from"]) || is_numeric($this->arrSql["buy_total_to"])) {
         $arrBuyTotal = $this->selectRange($this->arrSql["buy_total_from"], $this->arrSql["buy_total_to"], "buy_total");
         foreach ($arrBuyTotal as $data1) {
             $this->arrVal[] = $data1;
         }
     }
     // 購入回数指定
     if (!isset($this->arrSql['buy_times_from'])) {
         $this->arrSql['buy_times_from'] = "";
     }
     if (!isset($this->arrSql['buy_times_to'])) {
         $this->arrSql['buy_times_to'] = "";
     }
     if (is_numeric($this->arrSql["buy_times_from"]) || is_numeric($this->arrSql["buy_times_to"])) {
         $arrBuyTimes = $this->selectRange($this->arrSql["buy_times_from"], $this->arrSql["buy_times_to"], "buy_times");
         foreach ($arrBuyTimes as $data2) {
             $this->arrVal[] = $data2;
         }
     }
     // 誕生日期間指定
     if (!isset($this->arrSql['b_start_year'])) {
         $this->arrSql['b_start_year'] = "";
     }
     if (!isset($this->arrSql['b_start_month'])) {
         $this->arrSql['b_start_month'] = "";
     }
     if (!isset($this->arrSql['b_start_day'])) {
         $this->arrSql['b_start_day'] = "";
     }
     if (!isset($this->arrSql['b_end_year'])) {
         $this->arrSql['b_end_year'] = "";
     }
     if (!isset($this->arrSql['b_end_month'])) {
         $this->arrSql['b_end_month'] = "";
     }
     if (!isset($this->arrSql['b_end_day'])) {
         $this->arrSql['b_end_day'] = "";
     }
     if (strlen($this->arrSql['b_start_year']) > 0 && strlen($this->arrSql['b_start_month']) > 0 && strlen($this->arrSql['b_start_day']) > 0 || strlen($this->arrSql['b_end_year']) > 0 && strlen($this->arrSql['b_end_month']) > 0 && strlen($this->arrSql['b_end_day']) > 0) {
         $arrBirth = $this->selectTermRange($this->arrSql['b_start_year'], $this->arrSql['b_start_month'], $this->arrSql['b_start_day'], $this->arrSql['b_end_year'], $this->arrSql['b_end_month'], $this->arrSql['b_end_day'], "birth");
         if (is_array($arrBirth)) {
             foreach ($arrBirth as $data3) {
                 $this->arrVal[] = $data3;
             }
         }
     }
     // 誕生月の検索
     if (!isset($this->arrSql['birth_month'])) {
         $this->arrSql['birth_month'] = "";
     }
     if (is_numeric($this->arrSql["birth_month"])) {
         $this->setWhere(" EXTRACT(month from birth) = ?");
         $this->arrVal[] = $this->arrSql["birth_month"];
     }
     // 登録期間指定
     if (!isset($this->arrSql['start_year'])) {
         $this->arrSql['start_year'] = "";
     }
     if (!isset($this->arrSql['start_month'])) {
         $this->arrSql['start_month'] = "";
     }
     if (!isset($this->arrSql['start_day'])) {
         $this->arrSql['start_day'] = "";
     }
     if (!isset($this->arrSql['end_year'])) {
         $this->arrSql['end_year'] = "";
     }
     if (!isset($this->arrSql['end_month'])) {
         $this->arrSql['end_month'] = "";
     }
     if (!isset($this->arrSql['end_day'])) {
         $this->arrSql['end_day'] = "";
     }
     if (strlen($this->arrSql['start_year']) > 0 && strlen($this->arrSql['start_month']) > 0 && strlen($this->arrSql['start_day']) > 0 || strlen($this->arrSql['end_year']) > 0 && strlen($this->arrSql['end_month']) > 0 && strlen($this->arrSql['end_day']) > 0) {
         $arrRegistTime = $this->selectTermRange($this->arrSql['start_year'], $this->arrSql['start_month'], $this->arrSql['start_day'], $this->arrSql['end_year'], $this->arrSql['end_month'], $this->arrSql['end_day'], $regdate_col);
         if (is_array($arrRegistTime)) {
             foreach ($arrRegistTime as $data4) {
                 $this->arrVal[] = $data4;
             }
         }
     }
     // 最終購入日指定
     if (!isset($this->arrSql['buy_start_year'])) {
         $this->arrSql['buy_start_year'] = "";
     }
     if (!isset($this->arrSql['buy_start_month'])) {
         $this->arrSql['buy_start_month'] = "";
     }
     if (!isset($this->arrSql['buy_start_day'])) {
         $this->arrSql['buy_start_day'] = "";
     }
     if (!isset($this->arrSql['buy_end_year'])) {
         $this->arrSql['buy_end_year'] = "";
     }
     if (!isset($this->arrSql['buy_end_month'])) {
         $this->arrSql['buy_end_month'] = "";
     }
     if (!isset($this->arrSql['buy_end_day'])) {
         $this->arrSql['buy_end_day'] = "";
     }
     if (strlen($this->arrSql['buy_start_year']) > 0 && strlen($this->arrSql['buy_start_month']) > 0 && strlen($this->arrSql['buy_start_day']) > 0 || strlen($this->arrSql['buy_end_year']) > 0 && strlen($this->arrSql['buy_end_month']) > 0 && strlen($this->arrSql['buy_end_day']) > 0) {
         $arrRegistTime = $this->selectTermRange($this->arrSql['buy_start_year'], $this->arrSql['buy_start_month'], $this->arrSql['buy_start_day'], $this->arrSql['buy_end_year'], $this->arrSql['buy_end_month'], $this->arrSql['buy_end_day'], "last_buy_date");
         if (is_array($arrRegistTime)) {
             foreach ($arrRegistTime as $data4) {
                 $this->arrVal[] = $data4;
             }
         }
     }
     //購入商品コード
     if (!isset($this->arrSql['buy_product_code'])) {
         $this->arrSql['buy_product_code'] = "";
     }
     if (strlen($this->arrSql['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 ? ))");
         $search_buyproduct_code = $this->addSearchStr($this->arrSql['buy_product_code']);
         $this->arrVal[] = $search_buyproduct_code;
     }
     //購入商品名称
     if (!isset($this->arrSql['buy_product_name'])) {
         $this->arrSql['buy_product_name'] = "";
     }
     if (strlen($this->arrSql['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 ? ))");
         $search_buyproduct_name = $this->addSearchStr($this->arrSql['buy_product_name']);
         $this->arrVal[] = $search_buyproduct_name;
     }
     //カテゴリーを選択している場合のみ絞込検索を行う
     if (!isset($this->arrSql['category_id'])) {
         $this->arrSql['category_id'] = "";
     }
     if (strlen($this->arrSql['category_id']) != "") {
         //カテゴリーで絞込検索を行うSQL文生成
         list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere(SC_Utils_Ex::sfManualEscape($this->arrSql['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 . " ))) ");
             $this->arrVal = array_merge((array) $this->arrVal, (array) $tmp_arrval);
         }
     }
     //携帯電話番号
     if (!isset($this->arrSql['cell'])) {
         $this->arrSql['cell'] = "";
     }
     if (is_numeric($this->arrSql['cell'])) {
         $this->setWhere("(cell01 || cell02 || cell03 LIKE ?)");
         $searchTel = $this->addSearchStr($this->arrSql['cell']);
         $this->arrVal[] = ereg_replace("-", "", $searchTel);
     }
     //キャンペーン
     if (!isset($this->arrSql['campaign_id'])) {
         $this->arrSql['campaign_id'] = "";
     }
     if (is_numeric($this->arrSql['campaign_id'])) {
         $this->setWhere(" customer_id IN (SELECT distinct customer_id FROM dtb_campaign_order WHERE campaign_id = ?)");
         $this->arrVal[] = $this->arrSql['campaign_id'];
     }
     //会員状態
     if (!isset($this->arrSql['status'])) {
         $this->arrSql['status'] = "";
     }
     if (is_array($this->arrSql['status'])) {
         $arrStatusVal = $this->setItemTerm($this->arrSql['status'], "status");
         foreach ($arrStatusVal as $data) {
             $this->arrVal[] = $data;
         }
     }
     $this->setOrder("customer_id DESC");
 }
Ejemplo n.º 6
0
 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');
 }
Ejemplo n.º 7
0
 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_AdminView();
     $objDb = new SC_Helper_DB_Ex();
     $objDate = new SC_Date();
     // 登録・更新検索開始年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrStartYear = $objDate->getYear();
     $this->arrStartMonth = $objDate->getMonth();
     $this->arrStartDay = $objDate->getDay();
     // 登録・更新検索終了年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrEndYear = $objDate->getYear();
     $this->arrEndMonth = $objDate->getMonth();
     $this->arrEndDay = $objDate->getDay();
     // 認証可否の判定
     $objSess = new SC_Session();
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     //キャンペーンの編集時
     if (isset($_POST['campaign_id']) && SC_Utils_Ex::sfIsInt($_POST['campaign_id']) && $_POST['mode'] == "camp_search") {
         $objQuery = new SC_Query();
         $search_data = $objQuery->get("dtb_campaign", "search_condition", "campaign_id = ? ", array($_POST['campaign_id']));
         $arrSearch = unserialize($search_data);
         foreach ($arrSearch as $key => $val) {
             $_POST[$key] = $val;
         }
     }
     // POST値の引き継ぎ
     $this->arrForm = $_POST;
     // 検索ワードの引き継ぎ
     foreach ($_POST as $key => $val) {
         if (ereg("^search_", $key) || ereg("^campaign_", $key)) {
             switch ($key) {
                 case 'search_product_flag':
                 case 'search_status':
                     $this->arrHidden[$key] = SC_Utils_Ex::sfMergeParamCheckBoxes($val);
                     if (!is_array($val)) {
                         $this->arrForm[$key] = split("-", $val);
                     }
                     break;
                 default:
                     $this->arrHidden[$key] = $val;
                     break;
             }
         }
     }
     // ページ送り用
     $this->arrHidden['search_pageno'] = isset($_POST['search_pageno']) ? $_POST['search_pageno'] : "";
     // 商品削除
     if ($_POST['mode'] == "delete") {
         if ($_POST['category_id'] != "") {
             // ランク付きレコードの削除
             $where = "category_id = " . SC_Utils_Ex::sfQuoteSmart($_POST['category_id']);
             $objDb->sfDeleteRankRecord("dtb_products", "product_id", $_POST['product_id'], $where);
         } else {
             $objDb->sfDeleteRankRecord("dtb_products", "product_id", $_POST['product_id']);
         }
         // 子テーブル(商品規格)の削除
         $objQuery = new SC_Query();
         $objQuery->delete("dtb_products_class", "product_id = ?", array($_POST['product_id']));
         // 件数カウントバッチ実行
         $objDb->sfCategory_Count($objQuery);
     }
     if ($_POST['mode'] == "search" || $_POST['mode'] == "csv" || $_POST['mode'] == "delete" || $_POST['mode'] == "delete_all" || $_POST['mode'] == "camp_search") {
         // 入力文字の強制変換
         $this->lfConvertParam();
         // エラーチェック
         $this->arrErr = $this->lfCheckError();
         $where = "del_flg = 0";
         $view_where = "del_flg = 0";
         // 入力エラーなし
         if (count($this->arrErr) == 0) {
             $arrval = array();
             foreach ($this->arrForm as $key => $val) {
                 $val = SC_Utils_Ex::sfManualEscape($val);
                 if ($val == "") {
                     continue;
                 }
                 switch ($key) {
                     case 'search_product_id':
                         // 商品ID
                         $where .= " AND product_id = ?";
                         $view_where .= " AND product_id = ?";
                         $arrval[] = $val;
                         break;
                     case 'search_product_class_name':
                         //規格名称
                         $where_in = " (SELECT classcategory_id FROM dtb_classcategory WHERE class_id IN (SELECT class_id FROM dtb_class WHERE name LIKE ?)) ";
                         $where .= " AND product_id IN (SELECT product_id FROM dtb_products_class WHERE classcategory_id1 IN " . $where_in;
                         $where .= " OR classcategory_id2 IN" . $where_in . ")";
                         $view_where .= " AND product_id IN (SELECT product_id FROM dtb_products_class WHERE classcategory_id1 IN " . $where_in;
                         $view_where .= " OR classcategory_id2 IN" . $where_in . ")";
                         $arrval[] = "%{$val}%";
                         $arrval[] = "%{$val}%";
                         $view_where = $where;
                         break;
                     case 'search_name':
                         // 商品名
                         $where .= " AND name ILIKE ?";
                         $view_where .= " AND name ILIKE ?";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_category_id':
                         // カテゴリー
                         list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere($val);
                         if ($tmp_where != "") {
                             $where .= " AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE " . $tmp_where . ")";
                             $view_where .= " AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE " . $tmp_where . ")";
                             $arrval = array_merge((array) $arrval, (array) $tmp_arrval);
                         }
                         break;
                     case 'search_product_code':
                         // 商品コード
                         $where .= " AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code ILIKE ? GROUP BY product_id)";
                         $view_where .= " AND EXISTS (SELECT product_id FROM dtb_products_class as cls WHERE cls.product_code ILIKE ? AND dtb_products.product_id = cls.product_id GROUP BY cls.product_id )";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_startyear':
                         // 登録更新日(FROM)
                         $date = SC_Utils_Ex::sfGetTimestamp($_POST['search_startyear'], $_POST['search_startmonth'], $_POST['search_startday']);
                         $where .= " AND update_date >= '" . $_POST['search_startyear'] . "/" . $_POST['search_startmonth'] . "/" . $_POST['search_startday'] . "'";
                         $view_where .= " AND update_date >= '" . $_POST['search_startyear'] . "/" . $_POST['search_startmonth'] . "/" . $_POST['search_startday'] . "'";
                         break;
                     case 'search_endyear':
                         // 登録更新日(TO)
                         $date = SC_Utils_Ex::sfGetTimestamp($_POST['search_endyear'], $_POST['search_endmonth'], $_POST['search_endday']);
                         $date = date('Y/m/d', strtotime($date) + 86400);
                         $where .= " AND update_date < date('" . $date . "')";
                         $view_where .= " AND update_date < date('" . $date . "')";
                         break;
                     case 'search_product_flag':
                         //種別
                         global $arrSTATUS;
                         $search_product_flag = SC_Utils_Ex::sfSearchCheckBoxes($val);
                         if ($search_product_flag != "") {
                             $where .= " AND product_flag LIKE ?";
                             $view_where .= " AND product_flag LIKE ?";
                             $arrval[] = $search_product_flag;
                         }
                         break;
                     case 'search_status':
                         // ステータス
                         $tmp_where = "";
                         foreach ($val as $element) {
                             if ($element != "") {
                                 if ($tmp_where == "") {
                                     $tmp_where .= "AND (status = ? ";
                                 } else {
                                     $tmp_where .= "OR status = ? ";
                                 }
                                 $arrval[] = $element;
                             }
                         }
                         if ($tmp_where != "") {
                             $tmp_where .= ")";
                             $where .= " {$tmp_where}";
                             $view_where .= " {$tmp_where}";
                         }
                         break;
                     default:
                         break;
                 }
             }
             $order = "update_date DESC, product_id DESC";
             $objQuery = new SC_Query();
             switch ($_POST['mode']) {
                 case 'csv':
                     require_once CLASS_EX_PATH . "helper_extends/SC_Helper_CSV_Ex.php";
                     $objCSV = new SC_Helper_CSV_Ex();
                     // オプションの指定
                     $option = "ORDER BY {$order}";
                     // CSV出力タイトル行の作成
                     $arrOutput = SC_Utils_Ex::sfSwapArray($objCSV->sfgetCsvOutput(1, " WHERE csv_id = 1 AND status = 1"));
                     if (count($arrOutput) <= 0) {
                         break;
                     }
                     $arrOutputCols = $arrOutput['col'];
                     $arrOutputTitle = $arrOutput['disp_name'];
                     $head = SC_Utils_Ex::sfGetCSVList($arrOutputTitle);
                     $data = $objCSV->lfGetProductsCSV($where, $option, $arrval, $arrOutputCols);
                     // CSVを送信する。
                     SC_Utils_Ex::sfCSVDownload($head . $data);
                     exit;
                     break;
                 case 'delete_all':
                     // 検索結果の取得
                     $col = "product_id";
                     $from = "vw_products_nonclass AS noncls ";
                     $arrProducts = $objQuery->select($col, $from, $where, $arrval);
                     // 検索結果をすべて削除
                     $sqlval['del_flg'] = 1;
                     $where = "product_id = ?";
                     if (count($arrProducts) > 0) {
                         foreach ($arrProducts as $key => $val) {
                             $objQuery->update("dtb_products", $sqlval, $where, array($arrProducts[$key]["product_id"]));
                         }
                     }
                     break;
                 default:
                     // 読み込む列とテーブルの指定
                     $col = "product_id, name, category_id, main_list_image, status, product_code, price01, price02, stock, stock_unlimited";
                     $from = "vw_products_nonclass AS noncls ";
                     // 行数の取得
                     $linemax = $objQuery->count("dtb_products", $view_where, $arrval);
                     $this->tpl_linemax = $linemax;
                     // 何件が該当しました。表示用
                     // ページ送りの処理
                     if (is_numeric($_POST['search_page_max'])) {
                         $page_max = $_POST['search_page_max'];
                     } else {
                         $page_max = SEARCH_PMAX;
                     }
                     // ページ送りの取得
                     $objNavi = new SC_PageNavi($this->arrHidden['search_pageno'], $linemax, $page_max, "fnNaviSearchPage", NAVI_PMAX);
                     $startno = $objNavi->start_row;
                     $this->arrPagenavi = $objNavi->arrPagenavi;
                     //キャンペーン商品検索時は、全結果の商品IDを変数に格納する
                     if (isset($_POST['search_mode']) && $_POST['search_mode'] == 'campaign') {
                         $arrRet = $objQuery->select($col, $from, $where, $arrval);
                         if (count($arrRet) > 0) {
                             $arrRet = sfSwapArray($arrRet);
                             $pid = implode("-", $arrRet['product_id']);
                             $this->arrHidden['campaign_product_id'] = $pid;
                         }
                     }
                     // 取得範囲の指定(開始行番号、行数のセット)
                     //                    if(DB_TYPE != "mysql") $objQuery->setlimitoffset($page_max, $startno);
                     $objQuery->setlimitoffset($page_max, $startno);
                     // 表示順序
                     $objQuery->setorder($order);
                     // 検索結果の取得
                     $this->arrProducts = $objQuery->select($col, $from, $where, $arrval);
                     // 各商品ごとのカテゴリIDを取得
                     if (count($this->arrProducts) > 0) {
                         foreach ($this->arrProducts as $key => $val) {
                             $this->arrProducts[$key]["categories"] = $objDb->sfGetCategoryId($val["product_id"]);
                             $objDb->g_category_on = false;
                         }
                     }
             }
         }
     }
     // カテゴリの読込
     list($this->arrCatKey, $this->arrCatVal) = $objDb->sfGetLevelCatList(false);
     $this->arrCatList = $this->lfGetIDName($this->arrCatKey, $this->arrCatVal);
     // 画面の表示
     $objView->assignobj($this);
     $objView->display(MAIN_FRAME);
 }
Ejemplo n.º 8
0
 /**
  * 検索条件のwhere文とかを取得
  *
  * @return array
  */
 function lfGetSearchCondition($arrSearchData)
 {
     $searchCondition = array('where' => "", 'arrval' => array(), "where_category" => "", 'arrvalCategory' => array());
     // カテゴリからのWHERE文字列取得
     if ($arrSearchData["category_id"] != 0) {
         list($searchCondition["where_category"], $searchCondition['arrvalCategory']) = SC_Helper_DB_Ex::sfGetCatWhere($arrSearchData["category_id"]);
     }
     // ▼対象商品IDの抽出
     // 商品検索条件の作成(未削除、表示)
     $searchCondition['where'] = "alldtl.del_flg = 0 AND alldtl.status = 1 ";
     // 在庫無し商品の非表示
     if (NOSTOCK_HIDDEN === true) {
         $searchCondition['where'] .= ' AND (stock >= 1 OR stock_unlimited = 1)';
     }
     if (strlen($searchCondition["where_category"]) >= 1) {
         $searchCondition['where'] .= " AND T2." . $searchCondition["where_category"];
         $searchCondition['arrval'] = array_merge($searchCondition['arrval'], $searchCondition['arrvalCategory']);
     }
     // 商品名をwhere文に
     $name = $arrSearchData['name'];
     $name = str_replace(",", "", $name);
     // 全角スペースを半角スペースに変換
     $name = str_replace(' ', ' ', $name);
     // スペースでキーワードを分割
     $names = preg_split("/ +/", $name);
     // 分割したキーワードを一つずつwhere文に追加
     foreach ($names as $val) {
         if (strlen($val) > 0) {
             $searchCondition['where'] .= " AND ( alldtl.name LIKE ? OR alldtl.comment3 LIKE ?) ";
             $searchCondition['arrval'][] = "%{$val}%";
             $searchCondition['arrval'][] = "%{$val}%";
         }
     }
     // メーカーらのWHERE文字列取得
     if ($arrSearchData['maker_id']) {
         $searchCondition['where'] .= " AND alldtl.maker_id = ? ";
         $searchCondition['arrval'][] = $arrSearchData['maker_id'];
     }
     return $searchCondition;
 }
 function __construct($array, $mode = true)
 {
     parent::__construct($array);
     GC_Utils_Ex::gfPrintLog(print_r($this->arrSql, true), DEBUG_LOG_REALFILE);
     $this->arrWhereVal = array();
     $objDb = new SC_Helper_DB_Ex();
     $this->setEquals("0", "del_flg", "is_numeric");
     if ($mode && ($_REQUEST["mode"] == "pre_edit" || $_REQUEST["mode"] == "preEdit")) {
         // FIXME 端末ID(編集対象 )
         $this->setEquals($this->arrSql["edit_device_id"], "device_id", "is_numeric");
         return;
     }
     // FIXME 端末ID
     $this->setEquals($this->arrSql["search_device_id"], "device_id", "is_numeric");
     // FIXME 端末名
     $this->setLike($this->arrSql["search_device_name"], "device_name");
     // FIXME OS(発売)
     $this->setFromTo($this->arrSql["search_os_min_version_min"], $this->arrSql["search_os_min_version_max"], "os_min_version");
     // FIXME OS(最新)
     $this->setFromTo($this->arrSql["search_os_max_version_min"], $this->arrSql["search_os_max_version_max"], "os_max_version");
     // FIXME OS(発売|最新)
     $this->setFromTo($this->arrSql["search_os_version_min"], $this->arrSql["search_os_version_max"], array("os_min_version", "os_max_version"));
     // FIXME サイズ(幅)
     if (is_numeric($this->arrSql["search_display_width_min"]) || is_numeric($this->arrSql["search_display_width_max"])) {
         $this->setFromTo($this->arrSql["search_display_width_min"], $this->arrSql["search_display_width_max"], "display_width");
     }
     // FIXME サイズ(高)
     if (is_numeric($this->arrSql["search_display_height_min"]) || is_numeric($this->arrSql["search_display_height_max"])) {
         $this->setFromTo($this->arrSql["search_display_height_min"], $this->arrSql["search_display_height_max"], "display_height");
     }
     $base_table = $this->table;
     // FIXME カテゴリーID
     if (is_numeric($this->arrSql["search_category_id"])) {
         list($where, $arrRet) = $objDb->sfGetCatWhere($this->arrSql["search_category_id"]);
         $this->setWhere("    EXISTS (SELECT 1 FROM cp_dtb_device_categories A WHERE A.device_id = {$base_table}.device_id AND {$where})");
         $this->arrWhereVal = array_merge($this->arrWhereVal, $arrRet);
     } elseif ($this->arrSql["search_category_id_unset"] == "1" || $this->arrSql["search_category_id_unset"][0] == "1") {
         $this->setWhere("NOT EXISTS (SELECT 1 FROM cp_dtb_device_categories A WHERE A.device_id = {$base_table}.device_id)");
     }
     // FIXME 商品ID
     if (is_numeric($this->arrSql["search_product_id"])) {
         $id = $this->arrSql["search_product_id"];
         $objProduct = new SC_Product_Ex();
         if ($objProduct->isValidProductId($id)) {
             $arrCategory_id = $objProduct->getCategoryIds($id);
             foreach ($arrCategory_id as $category_id) {
                 list($where, $arrRet) = $objDb->sfGetCatWhere($category_id);
                 // TODO cp_dtb_device_products 連携
                 $this->setWhere("    EXISTS (SELECT 1 FROM cp_dtb_device_categories A WHERE A.device_id = {$base_table}.device_id AND {$where})");
                 $this->arrWhereVal = array_merge($this->arrWhereVal, $arrRet);
             }
         }
     }
     // FIXME キャリア
     if (is_numeric($this->arrSql["search_carrier"])) {
         $this->setEquals($this->arrSql["search_carrier"], 'carrier');
     }
     // FIXME 状態
     if (is_array($this->arrSql["search_status"])) {
         $this->setItemTerm($this->arrSql["search_status"], 'status');
     } elseif (is_numeric($this->arrSql["search_status"])) {
         $this->setEquals($this->arrSql["search_status"], 'status');
     }
     // FIXME UA
     $this->setEquals($this->arrSql["search_device_user_agent_word"], array('device_user_agent_word_1', 'device_user_agent_word_2', 'device_user_agent_word_3'));
     // END
     GC_Utils_Ex::gfPrintLog(print_r($this->arrWhereVal, true), DEBUG_LOG_REALFILE);
 }
Ejemplo n.º 10
0
 function lfDispProductsList($category_id, $name, $disp_num, $orderby)
 {
     $objQuery = new SC_Query();
     $objDb = new SC_Helper_DB_Ex();
     $this->tpl_pageno = defined("MOBILE_SITE") ? @$_GET['pageno'] : @$_POST['pageno'];
     //表示順序
     switch ($orderby) {
         //価格順
         case 'price':
             $col = "DISTINCT price02_min, product_id, product_code_min, product_code_max," . " name, comment1, comment2, comment3," . " main_list_comment, main_image, main_list_image," . " price01_min, price01_max, price02_max," . " stock_min, stock_max, stock_unlimited_min, stock_unlimited_max," . " point_rate, sale_limit, sale_unlimited, deliv_date_id, deliv_fee," . " status, product_flag, create_date, del_flg";
             $from = "vw_products_allclass AS T1";
             $order = "price02_min, product_id";
             break;
             //新着順
         //新着順
         case 'date':
             $col = "DISTINCT create_date, product_id, product_code_min, product_code_max," . " name, comment1, comment2, comment3," . " main_list_comment, main_image, main_list_image," . " price01_min, price01_max, price02_min, price02_max," . " stock_min, stock_max, stock_unlimited_min, stock_unlimited_max," . " point_rate, sale_limit, sale_unlimited, deliv_date_id, deliv_fee," . " status, product_flag, del_flg";
             $from = "vw_products_allclass AS T1";
             $order = "create_date DESC, product_id";
             break;
         default:
             $col = "DISTINCT T1.product_id, product_code_min, product_code_max," . " price01_min, price01_max, price02_min, price02_max," . " stock_min, stock_max, stock_unlimited_min," . " stock_unlimited_max, del_flg, status, name, comment1," . " comment2, comment3, main_list_comment, main_image," . " main_list_image, product_flag, deliv_date_id, sale_limit," . " point_rate, sale_unlimited, create_date, deliv_fee, " . " T4.product_rank, T4.category_rank";
             $from = "vw_products_allclass AS T1" . " JOIN (" . " SELECT max(T3.rank) AS category_rank," . "        max(T2.rank) AS product_rank," . "        T2.product_id" . "   FROM dtb_product_categories T2" . "   JOIN dtb_category T3 USING (category_id)" . " GROUP BY product_id) AS T4 USING (product_id)";
             $order = "T4.category_rank DESC, T4.product_rank DESC";
             break;
     }
     // 商品検索条件の作成(未削除、表示)
     $where = "del_flg = 0 AND status = 1 ";
     // カテゴリからのWHERE文字列取得
     if ($category_id) {
         list($tmp_where, $arrval) = $objDb->sfGetCatWhere($category_id);
         if ($tmp_where != "") {
             $where .= " AND {$tmp_where}";
         }
     }
     // 商品名をwhere文に
     $name = ereg_replace(",", "", $name);
     // XXX
     // 全角スペースを半角スペースに変換
     $name = str_replace(' ', ' ', $name);
     // スペースでキーワードを分割
     $names = preg_split("/ +/", $name);
     // 分割したキーワードを一つずつwhere文に追加
     foreach ($names as $val) {
         if (strlen($val) > 0) {
             $where .= " AND ( name ILIKE ? OR comment3 ILIKE ?) ";
             $ret = SC_Utils_Ex::sfManualEscape($val);
             $arrval[] = "%{$ret}%";
             $arrval[] = "%{$ret}%";
         }
     }
     if (empty($arrval)) {
         $arrval = array();
     }
     // 行数の取得
     $linemax = count($objQuery->getAll("SELECT DISTINCT product_id " . "FROM vw_products_allclass AS allcls " . (!empty($where) ? " WHERE " . $where : ""), $arrval));
     $this->tpl_linemax = $linemax;
     // 何件が該当しました。表示用
     // ページ送りの取得
     $this->objNavi = new SC_PageNavi($this->tpl_pageno, $linemax, $disp_num, "fnNaviPage", NAVI_PMAX);
     $strnavi = $this->objNavi->strnavi;
     $strnavi = str_replace('onclick="fnNaviPage', 'onclick="form1.mode.value=\'' . '\'; fnNaviPage', $strnavi);
     // 表示文字列
     $this->tpl_strnavi = empty($strnavi) ? "&nbsp;" : $strnavi;
     $startno = $this->objNavi->start_row;
     // 開始行
     // 取得範囲の指定(開始行番号、行数のセット)
     $objQuery->setlimitoffset($disp_num, $startno);
     // 表示順序
     $objQuery->setorder($order);
     // 検索結果の取得
     $this->arrProducts = $objQuery->select($col, $from, $where, $arrval);
     // 規格名一覧
     $arrClassName = $objDb->sfGetIDValueList("dtb_class", "class_id", "name");
     // 規格分類名一覧
     $arrClassCatName = $objDb->sfGetIDValueList("dtb_classcategory", "classcategory_id", "name");
     // 規格セレクトボックス設定
     if ($disp_num == 15) {
         for ($i = 0; $i < count($this->arrProducts); $i++) {
             $this->lfMakeSelect($this->arrProducts[$i]['product_id'], $arrClassName, $arrClassCatName);
             // 購入制限数を取得
             $this->lfGetSaleLimit($this->arrProducts[$i]);
         }
     }
 }
 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $conn = new SC_DBConn();
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objDb = new SC_Helper_DB_Ex();
     $objQuery = new SC_Query();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     if ($_GET['no'] != '') {
         $this->tpl_no = strval($_GET['no']);
     } elseif ($_POST['no'] != '') {
         $this->tpl_no = strval($_POST['no']);
     }
     if ($_POST['mode'] == "search") {
         // POST値の引き継ぎ
         $this->arrForm = $_POST;
         // 入力文字の強制変換
         $this->lfConvertParam();
         $where = "del_flg = 0";
         /* 入力エラーなし */
         foreach ($this->arrForm as $key => $val) {
             if ($val == "") {
                 continue;
             }
             switch ($key) {
                 case 'search_name':
                     $where .= " AND name ILIKE ?";
                     $arrval[] = "%{$val}%";
                     break;
                 case 'search_category_id':
                     list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere($val);
                     if ($tmp_where != "") {
                         $where .= " AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE " . $tmp_where . ")";
                         $arrval = array_merge((array) $arrval, (array) $tmp_arrval);
                     }
                     break;
                 case 'search_product_code':
                     $where .= " AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code LIKE ? GROUP BY product_id)";
                     $arrval[] = "{$val}%";
                     break;
                 default:
                     break;
             }
         }
         /*
         $order = "update_date DESC, product_id DESC ";
         
         // 読み込む列とテーブルの指定
         $col = "product_id, name, category_id, main_list_image, status, product_code, price01, stock, stock_unlimited";
         $from = "vw_products_nonclass AS noncls ";
         */
         $col = "DISTINCT T1.product_id, product_code_min, product_code_max," . " price01_min, price01_max, price02_min, price02_max," . " stock_min, stock_max, stock_unlimited_min," . " stock_unlimited_max, del_flg, status, name, comment1," . " comment2, comment3, main_list_comment, main_image," . " main_list_image, product_flag, deliv_date_id, sale_limit," . " point_rate, sale_unlimited, create_date, deliv_fee, " . " T4.product_rank, T4.category_rank";
         $from = "vw_products_allclass AS T1" . " JOIN (" . " SELECT max(T3.rank) AS category_rank," . "        max(T2.rank) AS product_rank," . "        T2.product_id" . "   FROM dtb_product_categories T2" . "   JOIN dtb_category T3 USING (category_id)" . " GROUP BY product_id) AS T4 USING (product_id)";
         $order = "T4.category_rank DESC, T4.product_rank DESC";
         $objQuery = new SC_Query();
         // 行数の取得
         if (empty($arrval)) {
             $arrval = array();
         }
         $linemax = $objQuery->count("dtb_products", $where, $arrval);
         $this->tpl_linemax = $linemax;
         // 何件が該当しました。表示用
         // ページ送りの処理
         if (isset($_POST['search_page_max']) && is_numeric($_POST['search_page_max'])) {
             $page_max = $_POST['search_page_max'];
         } else {
             $page_max = SEARCH_PMAX;
         }
         // ページ送りの取得
         $objNavi = new SC_PageNavi($_POST['search_pageno'], $linemax, $page_max, "fnNaviSearchOnlyPage", NAVI_PMAX);
         $this->tpl_strnavi = $objNavi->strnavi;
         // 表示文字列
         $startno = $objNavi->start_row;
         // 取得範囲の指定(開始行番号、行数のセット)
         if (DB_TYPE != "mysql") {
             $objQuery->setlimitoffset($page_max, $startno);
         }
         // 表示順序
         $objQuery->setorder($order);
         // viewも絞込みをかける(mysql用)
         //sfViewWhere("&&noncls_where&&", $where, $arrval, $objQuery->order . " " .  $objQuery->setlimitoffset($page_max, $startno, true));
         // 検索結果の取得
         $this->arrProducts = $objQuery->select($col, $from, $where, $arrval);
         // 規格名一覧
         $arrClassName = $objDb->sfGetIDValueList("dtb_class", "class_id", "name");
         // 規格分類名一覧
         $arrClassCatName = $objDb->sfGetIDValueList("dtb_classcategory", "classcategory_id", "name");
         // 規格セレクトボックス設定
         for ($i = 0; $i < count($this->arrProducts); $i++) {
             $this->lfMakeSelect($this->arrProducts[$i]['product_id'], $arrClassName, $arrClassCatName);
             // 購入制限数を取得
             $this->lfGetSaleLimit($this->arrProducts[$i]);
         }
     }
     // カテゴリ取得
     $this->arrCatList = $objDb->sfGetCategoryList();
     //---- ページ表示
     $objView->assignobj($this);
     $objView->display($this->tpl_mainpage);
 }