Index: /branches/version-2_4/html/install/sql/create_view.sql
===================================================================
--- /branches/version-2_4/html/install/sql/create_view.sql (revision 16546)
+++ /branches/version-2_4/html/install/sql/create_view.sql (revision 18388)
@@ -47,145 +47,111 @@
ON T1.product_id = T2.product_id_sub;
+CREATE VIEW vw_products_allclass_detail AS
+ SELECT
+ dtb_products.product_id,
+ dtb_products."name",
+ dtb_products.deliv_fee,
+ dtb_products.sale_limit,
+ dtb_products.rank,
+ dtb_products.status,
+ dtb_products.product_flag,
+ dtb_products.point_rate,
+ dtb_products.sale_unlimited,
+ dtb_products.comment1,
+ dtb_products.comment2,
+ dtb_products.comment3,
+ dtb_products.comment4,
+ dtb_products.comment5,
+ dtb_products.comment6,
+ dtb_products.note,
+ dtb_products.file1,
+ dtb_products.file2,
+ dtb_products.file3,
+ dtb_products.file4,
+ dtb_products.file5,
+ dtb_products.file6,
+ dtb_products.main_list_comment,
+ dtb_products.main_list_image,
+ dtb_products.main_comment,
+ dtb_products.main_image,
+ dtb_products.main_large_image,
+ dtb_products.sub_title1,
+ dtb_products.sub_comment1,
+ dtb_products.sub_image1,
+ dtb_products.sub_large_image1,
+ dtb_products.sub_title2,
+ dtb_products.sub_comment2,
+ dtb_products.sub_image2,
+ dtb_products.sub_large_image2,
+ dtb_products.sub_title3,
+ dtb_products.sub_comment3,
+ dtb_products.sub_image3,
+ dtb_products.sub_large_image3,
+ dtb_products.sub_title4,
+ dtb_products.sub_comment4,
+ dtb_products.sub_image4,
+ dtb_products.sub_large_image4,
+ dtb_products.sub_title5,
+ dtb_products.sub_comment5,
+ dtb_products.sub_image5,
+ dtb_products.sub_large_image5,
+ dtb_products.sub_title6,
+ dtb_products.sub_comment6,
+ dtb_products.sub_image6,
+ dtb_products.sub_large_image6,
+ dtb_products.del_flg,
+ dtb_products.creator_id,
+ dtb_products.create_date,
+ dtb_products.update_date,
+ dtb_products.deliv_date_id,
+ T4.product_code_min,
+ T4.product_code_max,
+ T4.price01_min,
+ T4.price01_max,
+ T4.price02_min,
+ T4.price02_max,
+ T4.stock_min,
+ T4.stock_max,
+ T4.stock_unlimited_min,
+ T4.stock_unlimited_max,
+ T4.class_count
+ FROM
+ dtb_products
+ LEFT JOIN
+ (
+ SELECT
+ product_id,
+ MIN(product_code) AS product_code_min,
+ MAX(product_code) AS product_code_max,
+ MIN(price01) AS price01_min,
+ MAX(price01) AS price01_max,
+ MIN(price02) AS price02_min,
+ MAX(price02) AS price02_max,
+ MIN(stock) AS stock_min,
+ MAX(stock) AS stock_max,
+ MIN(stock_unlimited) AS stock_unlimited_min,
+ MAX(stock_unlimited) AS stock_unlimited_max,
+ COUNT(*) as class_count
+ FROM dtb_products_class
+ GROUP BY product_id
+ ) AS T4
+ ON dtb_products.product_id = T4.product_id
+;
+
CREATE VIEW vw_products_allclass AS
- SELECT T5.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,
- category_rank,
- T5.category_id,
- T5.del_flg,
- T5.status,
- T5.name,
- T5.comment1,
- T5.comment2,
- T5.comment3,
- T5.rank,
- T5.main_list_comment,
- T5.main_image,
- T5.main_list_image,
- T5.product_flag,
- T5.deliv_date_id,
- T5.sale_limit,
- T5.point_rate,
- T5.sale_unlimited,
- T5.create_date,
- T5.deliv_fee
- FROM
- ((SELECT T1.product_id,
- T1.del_flg,
- T1.status,
- T1.name,
- T1.comment1,
- T1.comment2,
- T1.comment3,
- T1.main_list_comment,
- T1.main_image,
- T1.main_list_image,
- T1.product_flag,
- T1.deliv_date_id,
- T1.sale_limit,
- T1.point_rate,
- T1.sale_unlimited,
- T1.create_date,
- T1.deliv_fee,
- T2.category_id,
- T1.rank
- FROM dtb_products AS T1
- LEFT JOIN dtb_product_categories AS T2
- ON T1.product_id = T2.product_id) AS T3
- RIGHT JOIN
- (SELECT product_id AS product_id_sub,
- MIN(product_code) AS product_code_min,
- MAX(product_code) AS product_code_max,
- MIN(price01) AS price01_min,
- MAX(price01) AS price01_max,
- MIN(price02) AS price02_min,
- MAX(price02) AS price02_max,
- MIN(stock) AS stock_min,
- MAX(stock) AS stock_max,
- MIN(stock_unlimited) AS stock_unlimited_min,
- MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class
- GROUP BY product_id) AS T4
- ON T3.product_id = T4.product_id_sub) AS T5
- LEFT JOIN
- (SELECT rank AS category_rank,
- category_id AS sub_category_id
- FROM dtb_category) AS T6
- ON T5.category_id = T6.sub_category_id;
-
-CREATE VIEW vw_products_allclass_detail AS
- SELECT product_id,
- 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,
- deliv_fee,
- main_comment,
- main_image,
- main_large_image,
- sub_title1,
- sub_comment1,
- sub_image1,
- sub_large_image1,
- sub_title2,
- sub_comment2,
- sub_image2,
- sub_large_image2,
- sub_title3,
- sub_comment3,
- sub_image3,
- sub_large_image3,
- sub_title4,
- sub_comment4,
- sub_image4,
- sub_large_image4,
- sub_title5,
- sub_comment5,
- sub_image5,
- sub_large_image5,
- product_flag,
- deliv_date_id,
- sale_limit,
- point_rate,
- sale_unlimited,
- file1,file2,
- category_id
- FROM (dtb_products AS T1
- RIGHT JOIN
- (SELECT
- product_id AS product_id_sub,
- MIN(price01) AS price01_min,
- MAX(price01) AS price01_max,
- MIN(price02) AS price02_min,
- MAX(price02) AS price02_max,
- MIN(stock) AS stock_min,
- MAX(stock) AS stock_max,
- MIN(stock_unlimited) AS stock_unlimited_min,
- MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class
- GROUP BY product_id) AS T2
- ON T1.product_id = T2.product_id_sub) AS T3
- LEFT JOIN (SELECT rank AS category_rank,
- category_id AS sub_category_id
- FROM dtb_category) AS T4
- ON T3.category_id = T4.sub_category_id;
+ SELECT
+ alldtl.*,
+ dtb_category.rank AS category_rank,
+ T2.category_id,
+ T2.rank AS product_rank
+ FROM
+ vw_products_allclass_detail AS alldtl
+ LEFT JOIN
+ dtb_product_categories AS T2
+ ON alldtl.product_id = T2.product_id
+ LEFT JOIN
+ dtb_category
+ ON T2.category_id = dtb_category.category_id
+;
CREATE VIEW vw_product_class AS
@@ -205,9 +171,9 @@
stock_unlimited,
product_code
- FROM (dtb_products_class AS T1
+ FROM (dtb_products_class AS T1
LEFT JOIN dtb_classcategory AS T2
- ON T1.classcategory_id1 = T2.classcategory_id) AS T3
+ ON T1.classcategory_id1 = T2.classcategory_id) AS T3
LEFT JOIN dtb_classcategory AS T4
- ON T3.classcategory_id2 = T4.classcategory_id) AS T5
+ ON T3.classcategory_id2 = T4.classcategory_id) AS T5
LEFT JOIN dtb_products AS T6
ON product_id_sub = T6.product_id;
@@ -220,5 +186,5 @@
T1.rank,
T2.product_count
- FROM dtb_category AS T1
+ FROM dtb_category AS T1
LEFT JOIN dtb_category_total_count AS T2
ON T1.category_id = T2.category_id
Index: /branches/version-2_4/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
===================================================================
--- /branches/version-2_4/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (revision 17939)
+++ /branches/version-2_4/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (revision 18388)
@@ -272,4 +272,98 @@
*/
function viewToSubQuery() {
+ $sql['vw_products_allclass_detail'] =<<< __EOS__
+ (
+ SELECT
+ dtb_products.product_id,
+ dtb_products.name,
+ dtb_products.deliv_fee,
+ dtb_products.sale_limit,
+ dtb_products.sale_unlimited,
+ dtb_products.rank,
+ dtb_products.status,
+ dtb_products.product_flag,
+ dtb_products.point_rate,
+ dtb_products.comment1,
+ dtb_products.comment2,
+ dtb_products.comment3,
+ dtb_products.comment4,
+ dtb_products.comment5,
+ dtb_products.comment6,
+ dtb_products.note,
+ dtb_products.file1,
+ dtb_products.file2,
+ dtb_products.file3,
+ dtb_products.file4,
+ dtb_products.file5,
+ dtb_products.file6,
+ dtb_products.main_list_comment,
+ dtb_products.main_list_image,
+ dtb_products.main_comment,
+ dtb_products.main_image,
+ dtb_products.main_large_image,
+ dtb_products.sub_title1,
+ dtb_products.sub_comment1,
+ dtb_products.sub_image1,
+ dtb_products.sub_large_image1,
+ dtb_products.sub_title2,
+ dtb_products.sub_comment2,
+ dtb_products.sub_image2,
+ dtb_products.sub_large_image2,
+ dtb_products.sub_title3,
+ dtb_products.sub_comment3,
+ dtb_products.sub_image3,
+ dtb_products.sub_large_image3,
+ dtb_products.sub_title4,
+ dtb_products.sub_comment4,
+ dtb_products.sub_image4,
+ dtb_products.sub_large_image4,
+ dtb_products.sub_title5,
+ dtb_products.sub_comment5,
+ dtb_products.sub_image5,
+ dtb_products.sub_large_image5,
+ dtb_products.sub_title6,
+ dtb_products.sub_comment6,
+ dtb_products.sub_image6,
+ dtb_products.sub_large_image6,
+ dtb_products.del_flg,
+ dtb_products.creator_id,
+ dtb_products.create_date,
+ dtb_products.update_date,
+ dtb_products.deliv_date_id,
+ T4.product_code_min,
+ T4.product_code_max,
+ T4.price01_min,
+ T4.price01_max,
+ T4.price02_min,
+ T4.price02_max,
+ T4.stock_min,
+ T4.stock_max,
+ T4.stock_unlimited_min,
+ T4.stock_unlimited_max,
+ T4.class_count
+ FROM
+ dtb_products
+ LEFT JOIN
+ (
+ SELECT
+ product_id,
+ MIN(product_code) AS product_code_min,
+ MAX(product_code) AS product_code_max,
+ MIN(price01) AS price01_min,
+ MAX(price01) AS price01_max,
+ MIN(price02) AS price02_min,
+ MAX(price02) AS price02_max,
+ MIN(stock) AS stock_min,
+ MAX(stock) AS stock_max,
+ MIN(stock_unlimited) AS stock_unlimited_min,
+ MAX(stock_unlimited) AS stock_unlimited_max,
+ COUNT(*) as class_count
+ FROM dtb_products_class
+ GROUP BY product_id
+ ) AS T4
+ ON dtb_products.product_id = T4.product_id
+ )
+__EOS__;
+
return array(
"vw_cross_class" => '
@@ -365,80 +459,22 @@
ON T1.product_id = T2.product_id_sub) ',
- "vw_products_allclass" => '
- (SELECT
- T2.product_id
- ,T1.product_code_min
- ,T1.product_code_max
- ,T1.price01_min
- ,T1.price01_max
- ,T1.price02_min
- ,T1.price02_max
- ,T1.stock_min
- ,T1.stock_max
- ,T1.stock_unlimited_min
- ,T1.stock_unlimited_max
- ,T2.del_flg
- ,T2.status
- ,T2.name
- ,T2.comment1
- ,T2.comment2
- ,T2.comment3
- ,T2.main_list_comment
- ,T2.main_image
- ,T2.main_list_image
- ,T2.product_flag
- ,T2.deliv_date_id
- ,T2.sale_limit
- ,T2.point_rate
- ,T2.sale_unlimited
- ,T2.create_date
- ,T2.deliv_fee
- ,T3.rank
- ,T4.rank AS category_rank
- ,T4.category_id
+ "vw_products_allclass" => "
+ (
+ SELECT
+ alldtl.*,
+ dtb_category.rank AS category_rank,
+ T2.category_id,
+ T2.rank AS product_rank
FROM
- (
- (dtb_products AS T2 RIGHT JOIN
- (SELECT
- product_id AS product_id_sub
- ,MIN(product_code) AS product_code_min
- ,MAX(product_code) AS product_code_max
- ,MIN(price01) AS price01_min
- ,MAX(price01) AS price01_max
- ,MIN(price02) AS price02_min
- ,MAX(price02) AS price02_max
- ,MIN(stock) AS stock_min
- ,MAX(stock) AS stock_max
- ,MIN(stock_unlimited) AS stock_unlimited_min
- ,MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class GROUP BY product_id
- ) AS T1 ON T1.product_id_sub = T2.product_id
- ) LEFT JOIN dtb_product_categories AS T3 ON T2.product_id = T3.product_id
- ) LEFT JOIN dtb_category AS T4 ON T3.category_id = T4.category_id
- ) ',
-
- "vw_products_allclass_detail" => '
- (SELECT product_id,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,deliv_fee,main_comment,main_image,main_large_image,
- sub_title1,sub_comment1,sub_image1,sub_large_image1,
- sub_title2,sub_comment2,sub_image2,sub_large_image2,
- sub_title3,sub_comment3,sub_image3,sub_large_image3,
- sub_title4,sub_comment4,sub_image4,sub_large_image4,
- sub_title5,sub_comment5,sub_image5,sub_large_image5,
- product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id
- FROM ( SELECT * FROM (dtb_products AS T1 RIGHT JOIN
- (SELECT
- product_id AS product_id_sub,
- MIN(price01) AS price01_min,
- MAX(price01) AS price01_max,
- MIN(price02) AS price02_min,
- MAX(price02) AS price02_max,
- MIN(stock) AS stock_min,
- MAX(stock) AS stock_max,
- MIN(stock_unlimited) AS stock_unlimited_min,
- MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class GROUP BY product_id) AS T2
- ON T1.product_id = T2.product_id_sub ) ) AS T3 LEFT JOIN (SELECT rank AS category_rank, category_id AS sub_category_id FROM dtb_category) AS T4
- ON T3.category_id = T4.sub_category_id) ',
+ {$sql['vw_products_allclass_detail']} AS alldtl
+ LEFT JOIN
+ dtb_product_categories AS T2
+ ON alldtl.product_id = T2.product_id
+ LEFT JOIN
+ dtb_category
+ ON T2.category_id = dtb_category.category_id
+ ) ",
+
+ "vw_products_allclass_detail" => $sql['vw_products_allclass_detail'],
"vw_product_class" => '
Index: /branches/version-2_4/data/class/SC_Query.php
===================================================================
--- /branches/version-2_4/data/class/SC_Query.php (revision 17642)
+++ /branches/version-2_4/data/class/SC_Query.php (revision 18388)
@@ -432,5 +432,5 @@
}
// SQL文の実行
- return $this->conn->getCol($sqlse, $col, $arrval);
+ return $this->conn->getCol($sqlse, 0, $arrval);
}
Index: /branches/version-2_4/data/class/pages/products/LC_Page_Products_List.php
===================================================================
--- /branches/version-2_4/data/class/pages/products/LC_Page_Products_List.php (revision 18113)
+++ /branches/version-2_4/data/class/pages/products/LC_Page_Products_List.php (revision 18388)
@@ -380,63 +380,31 @@
$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;
- }
-
+ $arrval = array();
+ $arrval_order = array();
+ $arrval_category = array();
+
+ // カテゴリからのWHERE文字列取得
+
+ if ( $category_id ) {
+ list($tmp_where, $arrval_category) = $objDb->sfGetCatWhere($category_id);
+ if (strlen($tmp_where) >= 1) {
+ $where_category = "AND $tmp_where";
+ //並び替え用(MySQL4.1対応)
+ $where_category_order = "AND " ."T2.".trim($tmp_where);
+ }
+ }
+
+ // ▼対象商品IDの抽出
// 商品検索条件の作成(未削除、表示)
$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";
- }
+
+ // 在庫無し商品の非表示
+ if (NOSTOCK_HIDDEN === true) {
+ $where .= ' AND (stock_max >= 1 OR stock_unlimited_max = 1)';
+ }
+
+ if (strlen($where_category) >= 1) {
+ $where.= " $where_category";
+ $arrval = array_merge($arrval, $arrval_category);
}
@@ -457,6 +425,173 @@
}
- if (empty($arrval)) {
- $arrval = array();
+ $arrProduct_id = $objQuery->getCol('vw_products_allclass AS allcls', 'DISTINCT product_id', $where, $arrval);
+ // ▲対象商品IDの抽出
+
+ // 行数の取得
+ $linemax = count($arrProduct_id);
+
+ $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; // 開始行
+
+ // ▼商品詳細取得
+ $col = <<< __EOS__
+ 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
+__EOS__;
+
+ $from = "vw_products_allclass_detail AS alldtl";
+
+ // WHERE 句
+ $where = '0=0';
+ if (is_array($arrProduct_id) && !empty($arrProduct_id)) {
+ $where .= ' AND product_id IN (' . implode(',', $arrProduct_id) . ')';
+ }
+
+ //表示順序
+ switch($orderby) {
+
+ //販売価格順
+ case 'price':
+ $order = "price02_min, product_id";
+ break;
+
+ //新着順
+ case 'date':
+ $order = "create_date DESC, product_id";
+ break;
+
+ default:
+ $order = <<< __EOS__
+ (
+ SELECT
+ T3.rank
+ FROM
+ dtb_product_categories T2
+ JOIN dtb_category T3
+ USING (category_id)
+ WHERE T2.product_id = alldtl.product_id
+ $where_category_order
+ ORDER BY T3.rank DESC, T2.rank DESC
+ LIMIT 1
+ ) DESC
+ ,(
+ SELECT
+ T2.rank
+ FROM
+ dtb_product_categories T2
+ JOIN dtb_category T3
+ USING (category_id)
+ WHERE T2.product_id = alldtl.product_id
+ $where_category_order
+ ORDER BY T3.rank DESC, T2.rank DESC
+ LIMIT 1
+ ) DESC
+ ,product_id
+__EOS__;
+ $arrval_order = array_merge($arrval_category, $arrval_category);
+ break;
+ }
+
+ // 取得範囲の指定(開始行番号、行数のセット)
+ $objQuery->setlimitoffset($disp_num, $startno);
+ // 表示順序
+ $objQuery->setorder($order);
+
+ // 検索結果の取得
+ $this->arrProducts = $objQuery->select($col, $from, $where, $arrval_order);
+ // ▲商品詳細取得
+
+ // 規格名一覧
+ $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]);
+ }
+ }
+ }
+
+ /* 商品一覧の表示 */
+ function lfDispProductsList2($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'];
+
+ $arrval = array();
+ $arrval_order = array();
+ $arrval_category = array();
+
+ // カテゴリからのWHERE文字列取得
+ if ( $category_id ) {
+ list($tmp_where, $arrval_category) = $objDb->sfGetCatWhere($category_id);
+ if (strlen($tmp_where) >= 1) {
+ $where_category = "AND $tmp_where";
+ }
+ }
+
+ // 商品検索条件の作成(未削除、表示)
+ $where = "del_flg = 0 AND status = 1 ";
+
+ // 在庫無し商品の非表示
+ if (NOSTOCK_HIDDEN === true) {
+ $where .= ' AND (allcls.stock_max >= 1 OR allcls.stock_unlimited_max = 1)';
+ }
+
+ if (strlen($where_category) >= 1) {
+ $where.= " $where_category";
+ $arrval = array_merge($arrval, $arrval_category);
+ }
+
+ // 商品名を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%";
+ }
}
@@ -478,4 +613,86 @@
$startno = $this->objNavi->start_row; // 開始行
+ // ▼商品詳細取得
+ $col = <<< __EOS__
+ 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
+ ,deliv_date_id
+ ,deliv_fee
+ ,status
+ ,product_flag
+ ,del_flg
+__EOS__;
+ //$from = "vw_products_allclass AS allcls";
+ $from = "vw_products_allclass_detail AS alldtl";
+
+ // WHERE 句
+ $where = '0=0';
+ if (is_array($arrProduct_id) && !empty($arrProduct_id)) {
+ $where .= ' AND product_id IN (' . implode(',', $arrProduct_id) . ')';
+ }
+
+ //表示順序
+ switch($orderby) {
+
+ //販売価格順
+ case 'price':
+ $order = "price02_min, product_id";
+ break;
+
+ //新着順
+ case 'date':
+ $order = "create_date DESC, product_id";
+ break;
+
+ default:
+ $order = <<< __EOS__
+ (
+ SELECT
+ T3.rank
+ FROM
+ dtb_product_categories T2
+ JOIN dtb_category T3
+ USING (category_id)
+ WHERE T2.product_id = alldtl.product_id
+ $where_category
+ ORDER BY T3.rank DESC, T2.rank DESC
+ LIMIT 1
+ ) DESC
+ ,(
+ SELECT
+ T2.rank
+ FROM
+ dtb_product_categories T2
+ JOIN dtb_category T3
+ USING (category_id)
+ WHERE T2.product_id = alldtl.product_id
+ $where_category
+ ORDER BY T3.rank DESC, T2.rank DESC
+ LIMIT 1
+ ) DESC
+ ,product_id
+__EOS__;
+ $arrval_order = array_merge($arrval_category, $arrval_category);
+ break;
+ }
+
// 取得範囲の指定(開始行番号、行数のセット)
$objQuery->setlimitoffset($disp_num, $startno);
@@ -484,5 +701,6 @@
// 検索結果の取得
- $this->arrProducts = $objQuery->select($col, $from, $where, $arrval);
+ $this->arrProducts = $objQuery->select($col, $from, $where, $arrval_order);
+ // ▲商品詳細取得
// 規格名一覧