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); + // ▲商品詳細取得 // 規格名一覧 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); }