/** * 検索条件の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); }
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"); }
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'); }
/** * 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); }
/** * 検索条件の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); }
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) ? " " : $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); }