Changeset 17549 for branches/comu-ver2
- Timestamp:
- 2008/08/21 00:02:54 (16 years ago)
- Location:
- branches/comu-ver2
- Files:
-
- 5 edited
-
data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (modified) (4 diffs)
-
data/class/helper/SC_Helper_DB.php (modified) (1 diff)
-
data/class/pages/admin/products/LC_Page_Admin_Products.php (modified) (1 diff)
-
html/install/sql/create_view.sql (modified) (2 diffs)
-
html/install/sql/drop_view.sql (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
branches/comu-ver2/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
r17537 r17549 169 169 170 170 /** 171 * SQL の中の View の存在をチェックする.172 *173 * @access private174 * @param string $sql SQL 文175 * @return bool Viewが存在しない場合 false176 */177 function sfInArray($sql){178 $arrView = $this->viewToSubQuery();179 180 foreach($arrView as $key => $val){181 if (strcasecmp($sql, $val) == 0){182 $changesql = eregi_replace("($key)", "$val", $sql);183 $this->sfInArray($changesql);184 }185 }186 return false;187 }188 189 /**190 171 * View をインラインビューに変換する. 191 172 * … … 195 176 */ 196 177 function sfChangeView($sql){ 197 178 198 179 $arrViewTmp = $this->viewToSubQuery(); 199 200 // viewのwhereを変換180 181 // viewのwhereを変換 201 182 foreach($arrViewTmp as $key => $val){ 202 183 $arrViewTmp[$key] = strtr($arrViewTmp[$key], $this->getWhereConverter()); 203 184 } 204 205 // viewを変換185 186 // viewを変換 206 187 $changesql = strtr($sql, $arrViewTmp); 207 188 208 189 return $changesql; 209 190 } … … 258 239 */ 259 240 function viewToSubQuery() { 241 $sql['vw_products_allclass_detail'] =<<< __EOS__ 242 ( 243 SELECT 244 dtb_products.product_id, 245 dtb_products.name, 246 dtb_products.deliv_fee, 247 dtb_products.sale_limit, 248 dtb_products.sale_unlimited, 249 dtb_products.maker_id, 250 dtb_products.rank, 251 dtb_products.status, 252 dtb_products.product_flag, 253 dtb_products.point_rate, 254 dtb_products.comment1, 255 dtb_products.comment2, 256 dtb_products.comment3, 257 dtb_products.comment4, 258 dtb_products.comment5, 259 dtb_products.comment6, 260 dtb_products.note, 261 dtb_products.file1, 262 dtb_products.file2, 263 dtb_products.file3, 264 dtb_products.file4, 265 dtb_products.file5, 266 dtb_products.file6, 267 dtb_products.main_list_comment, 268 dtb_products.main_list_image, 269 dtb_products.main_comment, 270 dtb_products.main_image, 271 dtb_products.main_large_image, 272 dtb_products.sub_title1, 273 dtb_products.sub_comment1, 274 dtb_products.sub_image1, 275 dtb_products.sub_large_image1, 276 dtb_products.sub_title2, 277 dtb_products.sub_comment2, 278 dtb_products.sub_image2, 279 dtb_products.sub_large_image2, 280 dtb_products.sub_title3, 281 dtb_products.sub_comment3, 282 dtb_products.sub_image3, 283 dtb_products.sub_large_image3, 284 dtb_products.sub_title4, 285 dtb_products.sub_comment4, 286 dtb_products.sub_image4, 287 dtb_products.sub_large_image4, 288 dtb_products.sub_title5, 289 dtb_products.sub_comment5, 290 dtb_products.sub_image5, 291 dtb_products.sub_large_image5, 292 dtb_products.sub_title6, 293 dtb_products.sub_comment6, 294 dtb_products.sub_image6, 295 dtb_products.sub_large_image6, 296 dtb_products.del_flg, 297 dtb_products.creator_id, 298 dtb_products.create_date, 299 dtb_products.update_date, 300 dtb_products.deliv_date_id, 301 T4.product_code_min, 302 T4.product_code_max, 303 T4.price01_min, 304 T4.price01_max, 305 T4.price02_min, 306 T4.price02_max, 307 T4.stock_min, 308 T4.stock_max, 309 T4.stock_unlimited_min, 310 T4.stock_unlimited_max, 311 T4.class_count 312 FROM 313 dtb_products 314 LEFT JOIN 315 ( 316 SELECT 317 product_id, 318 MIN(product_code) AS product_code_min, 319 MAX(product_code) AS product_code_max, 320 MIN(price01) AS price01_min, 321 MAX(price01) AS price01_max, 322 MIN(price02) AS price02_min, 323 MAX(price02) AS price02_max, 324 MIN(stock) AS stock_min, 325 MAX(stock) AS stock_max, 326 MIN(stock_unlimited) AS stock_unlimited_min, 327 MAX(stock_unlimited) AS stock_unlimited_max, 328 COUNT(*) as class_count 329 FROM dtb_products_class 330 GROUP BY product_id 331 ) AS T4 332 ON dtb_products.product_id = T4.product_id 333 ) 334 __EOS__; 335 260 336 return array( 261 337 "vw_cross_class" => ' … … 350 426 ON T1.product_id = T2.product_id_sub) ', 351 427 352 "vw_products_allclass" => ' 353 (SELECT T1.product_id, 354 product_code_min, 355 product_code_max, 356 price01_min, 357 price01_max, 358 price02_min, 359 price02_max, 360 stock_min, 361 stock_max, 362 stock_unlimited_min, 363 stock_unlimited_max, 364 del_flg, 365 status, 366 name, 367 comment1, 368 comment2, 369 comment3, 370 main_list_comment, 371 main_image, 372 main_list_image, 373 product_flag, 374 deliv_date_id, 375 sale_limit, 376 point_rate, 377 sale_unlimited, 378 create_date, 379 deliv_fee, 380 update_date, 381 rank 382 ,(SELECT rank AS category_rank 383 FROM dtb_category AS T4 384 WHERE T1.category_id = T4.category_id) as category_rank 385 ,(SELECT category_id AS sub_category_id 386 FROM dtb_category T4 387 WHERE T1.category_id = T4.category_id) as category_id 388 FROM (SELECT T0.product_id, 389 T0.del_flg, 390 T0.status, 391 T0.name, 392 T0.comment1, 393 T0.comment2, 394 T0.comment3, 395 T0.main_list_comment, 396 T0.main_image, 397 T0.main_list_image, 398 T0.product_flag, 399 T0.deliv_date_id, 400 T0.sale_limit, 401 T0.point_rate, 402 T0.sale_unlimited, 403 T0.create_date, 404 T0.deliv_fee, 405 T00.category_id, 406 T0.rank, 407 T0.update_date 408 FROM dtb_products AS T0 409 LEFT JOIN dtb_product_categories AS T00 410 USING (product_id)) AS T1 411 RIGHT JOIN (SELECT product_id as product_id_sub, 412 MIN(product_code) AS product_code_min, 413 MAX(product_code) AS product_code_max, 414 MIN(price01) AS price01_min, 415 MAX(price01) AS price01_max, 416 MIN(price02) AS price02_min, 417 MAX(price02) AS price02_max, 418 MIN(stock) AS stock_min, 419 MAX(stock) AS stock_max, 420 MIN(stock_unlimited) AS stock_unlimited_min, 421 MAX(stock_unlimited) AS stock_unlimited_max 422 FROM dtb_products_class GROUP BY product_id) AS T2 423 ON T1.product_id = T2.product_id_sub 424 ) ', 425 426 "vw_products_allclass_detail" => ' 427 (SELECT product_id,price01_min,price01_max,price02_min,price02_max,stock_min,stock_max,stock_unlimited_min,stock_unlimited_max, 428 del_flg,status,name,comment1,comment2,comment3,deliv_fee,main_comment,main_image,main_large_image, 429 sub_title1,sub_comment1,sub_image1,sub_large_image1, 430 sub_title2,sub_comment2,sub_image2,sub_large_image2, 431 sub_title3,sub_comment3,sub_image3,sub_large_image3, 432 sub_title4,sub_comment4,sub_image4,sub_large_image4, 433 sub_title5,sub_comment5,sub_image5,sub_large_image5, 434 product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id 435 FROM ( SELECT * FROM (dtb_products AS T1 RIGHT JOIN 436 (SELECT 437 product_id AS product_id_sub, 438 MIN(price01) AS price01_min, 439 MAX(price01) AS price01_max, 440 MIN(price02) AS price02_min, 441 MAX(price02) AS price02_max, 442 MIN(stock) AS stock_min, 443 MAX(stock) AS stock_max, 444 MIN(stock_unlimited) AS stock_unlimited_min, 445 MAX(stock_unlimited) AS stock_unlimited_max 446 FROM dtb_products_class GROUP BY product_id) AS T2 447 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 448 ON T3.category_id = T4.sub_category_id) ', 428 "vw_products_allclass" => " 429 ( 430 SELECT 431 alldtl.*, 432 dtb_category.rank AS category_rank, 433 T2.category_id 434 FROM 435 {$sql['vw_products_allclass_detail']} AS alldtl 436 LEFT JOIN 437 dtb_product_categories AS T2 438 ON alldtl.product_id = T2.product_id 439 LEFT JOIN 440 dtb_category 441 ON T2.category_id = dtb_category.category_id 442 ) ", 443 444 "vw_products_allclass_detail" => $sql['vw_products_allclass_detail'], 449 445 450 446 "vw_product_class" => ' -
branches/comu-ver2/data/class/helper/SC_Helper_DB.php
r17509 r17549 928 928 $sql .= " FROM dtb_category AS T1 LEFT JOIN dtb_product_categories AS T2"; 929 929 $sql .= " ON T1.category_id = T2.category_id "; 930 $sql .= " LEFT JOIN vw_products_allclass AS allcls";931 $sql .= " ON T2.product_id = all cls.product_id";932 $sql .= " WHERE all cls.del_flg = 0 AND allcls.status = 1 ";930 $sql .= " LEFT JOIN vw_products_allclass_detail AS alldtl"; 931 $sql .= " ON T2.product_id = alldtl.product_id"; 932 $sql .= " WHERE alldtl.del_flg = 0 AND alldtl.status = 1 "; 933 933 934 934 // 在庫無し商品の非表示 935 935 if (NOSTOCK_HIDDEN === true) { 936 $sql .= ' AND (all cls.stock_max >= 1 OR allcls.stock_unlimited_max = 1)';936 $sql .= ' AND (alldtl.stock_max >= 1 OR alldtl.stock_unlimited_max = 1)'; 937 937 } 938 938 -
branches/comu-ver2/data/class/pages/admin/products/LC_Page_Admin_Products.php
r17548 r17549 273 273 case 'delete_all': 274 274 // 検索結果をすべて削除 275 $where = "product_id IN (SELECT product_id FROM vw_products_allclass AS allclsWHERE $where)";275 $where = "product_id IN (SELECT product_id FROM vw_products_allclass_detail AS alldtl WHERE $where)"; 276 276 $sqlval['del_flg'] = 1; 277 277 $objQuery->update("dtb_products", $sqlval, $where, $arrval); 278 $objQuery->delete("dtb_customer_favorite_products", $where );278 $objQuery->delete("dtb_customer_favorite_products", $where, $arrval); 279 279 break; 280 280 default: 281 281 // 読み込む列とテーブルの指定 282 // XXX DISTINCT は応急処置。update_date は DISTINCT のため。 283 $col = "DISTINCT product_id, name, main_list_image, status, product_code_min, product_code_max, price02_min, price02_max, stock_min, stock_max, stock_unlimited_min, stock_unlimited_max, update_date"; 284 $from = "vw_products_allclass AS allcls "; 282 $col = "product_id, name, main_list_image, status, product_code_min, product_code_max, price02_min, price02_max, stock_min, stock_max, stock_unlimited_min, stock_unlimited_max, update_date"; 283 $from = "vw_products_allclass_detail AS alldtl "; 285 284 286 285 // 行数の取得 -
branches/comu-ver2/html/install/sql/create_view.sql
r17537 r17549 1 1 CREATE VIEW vw_cross_class as 2 SELECT T1.class_id AS class_id1, 3 T2.class_id AS class_id2, 4 T1.classcategory_id AS classcategory_id1, 5 T2.classcategory_id AS classcategory_id2, 6 T1.name AS name1, 7 T2.name AS name2, 8 T1.rank AS rank1, 9 T2.rank AS rank2 10 FROM dtb_classcategory AS T1, 11 dtb_classcategory AS T2; 2 SELECT 3 T1.class_id AS class_id1, 4 T2.class_id AS class_id2, 5 T1.classcategory_id AS classcategory_id1, 6 T2.classcategory_id AS classcategory_id2, 7 T1.name AS name1, 8 T2.name AS name2, 9 T1.rank AS rank1, 10 T2.rank AS rank2 11 FROM 12 dtb_classcategory AS T1, 13 dtb_classcategory AS T2 14 ; 12 15 13 16 CREATE VIEW vw_cross_products_class AS 14 SELECT T1.class_id1, 15 T1.class_id2, 16 T1.classcategory_id1, 17 T1.classcategory_id2, 18 T2.product_id, 19 T1.name1, 20 T1.name2, 21 T2.product_code, 22 T2.stock, 23 T2.price01, 24 T2.price02, 25 T1.rank1, 26 T1.rank2 27 FROM vw_cross_class AS T1 28 LEFT JOIN dtb_products_class AS T2 29 ON T1.classcategory_id1 = T2.classcategory_id1 30 AND T1.classcategory_id2 = T2.classcategory_id2; 17 SELECT 18 T1.class_id1, 19 T1.class_id2, 20 T1.classcategory_id1, 21 T1.classcategory_id2, 22 T2.product_id, 23 T1.name1, 24 T1.name2, 25 T2.product_code, 26 T2.stock, 27 T2.price01, 28 T2.price02, 29 T1.rank1, 30 T1.rank2 31 FROM 32 vw_cross_class AS T1 33 LEFT JOIN dtb_products_class AS T2 34 ON T1.classcategory_id1 = T2.classcategory_id1 35 AND T1.classcategory_id2 = T2.classcategory_id2 36 ; 31 37 32 38 CREATE VIEW vw_products_nonclass AS 33 SELECT * 34 FROM dtb_products AS T1 LEFT JOIN 35 (SELECT 36 product_id AS product_id_sub, 37 product_code, 38 price01, 39 price02, 40 stock, 41 stock_unlimited, 42 classcategory_id1, 43 classcategory_id2 44 FROM dtb_products_class 45 WHERE classcategory_id1 = 0 46 AND classcategory_id2 = 0) AS T2 47 ON T1.product_id = T2.product_id_sub; 39 SELECT * 40 FROM 41 dtb_products AS T1 42 LEFT JOIN 43 ( 44 SELECT 45 product_id AS product_id_sub, 46 product_code, 47 price01, 48 price02, 49 stock, 50 stock_unlimited, 51 classcategory_id1, 52 classcategory_id2 53 FROM dtb_products_class 54 WHERE 55 classcategory_id1 = 0 56 AND classcategory_id2 = 0 57 ) AS T2 58 ON T1.product_id = T2.product_id_sub 59 ; 60 61 CREATE VIEW vw_products_allclass_detail AS 62 SELECT 63 dtb_products.product_id, 64 dtb_products."name", 65 dtb_products.deliv_fee, 66 dtb_products.sale_limit, 67 dtb_products.sale_unlimited, 68 dtb_products.maker_id, 69 dtb_products.rank, 70 dtb_products.status, 71 dtb_products.product_flag, 72 dtb_products.point_rate, 73 dtb_products.comment1, 74 dtb_products.comment2, 75 dtb_products.comment3, 76 dtb_products.comment4, 77 dtb_products.comment5, 78 dtb_products.comment6, 79 dtb_products.note, 80 dtb_products.file1, 81 dtb_products.file2, 82 dtb_products.file3, 83 dtb_products.file4, 84 dtb_products.file5, 85 dtb_products.file6, 86 dtb_products.main_list_comment, 87 dtb_products.main_list_image, 88 dtb_products.main_comment, 89 dtb_products.main_image, 90 dtb_products.main_large_image, 91 dtb_products.sub_title1, 92 dtb_products.sub_comment1, 93 dtb_products.sub_image1, 94 dtb_products.sub_large_image1, 95 dtb_products.sub_title2, 96 dtb_products.sub_comment2, 97 dtb_products.sub_image2, 98 dtb_products.sub_large_image2, 99 dtb_products.sub_title3, 100 dtb_products.sub_comment3, 101 dtb_products.sub_image3, 102 dtb_products.sub_large_image3, 103 dtb_products.sub_title4, 104 dtb_products.sub_comment4, 105 dtb_products.sub_image4, 106 dtb_products.sub_large_image4, 107 dtb_products.sub_title5, 108 dtb_products.sub_comment5, 109 dtb_products.sub_image5, 110 dtb_products.sub_large_image5, 111 dtb_products.sub_title6, 112 dtb_products.sub_comment6, 113 dtb_products.sub_image6, 114 dtb_products.sub_large_image6, 115 dtb_products.del_flg, 116 dtb_products.creator_id, 117 dtb_products.create_date, 118 dtb_products.update_date, 119 dtb_products.deliv_date_id, 120 T4.product_code_min, 121 T4.product_code_max, 122 T4.price01_min, 123 T4.price01_max, 124 T4.price02_min, 125 T4.price02_max, 126 T4.stock_min, 127 T4.stock_max, 128 T4.stock_unlimited_min, 129 T4.stock_unlimited_max, 130 T4.class_count 131 FROM 132 dtb_products 133 LEFT JOIN 134 ( 135 SELECT 136 product_id, 137 MIN(product_code) AS product_code_min, 138 MAX(product_code) AS product_code_max, 139 MIN(price01) AS price01_min, 140 MAX(price01) AS price01_max, 141 MIN(price02) AS price02_min, 142 MAX(price02) AS price02_max, 143 MIN(stock) AS stock_min, 144 MAX(stock) AS stock_max, 145 MIN(stock_unlimited) AS stock_unlimited_min, 146 MAX(stock_unlimited) AS stock_unlimited_max, 147 COUNT(*) as class_count 148 FROM dtb_products_class 149 GROUP BY product_id 150 ) AS T4 151 ON dtb_products.product_id = T4.product_id 152 ; 48 153 49 154 CREATE VIEW vw_products_allclass AS 50 SELECT T5.product_id, 51 product_code_min, 52 product_code_max, 53 price01_min, 54 price01_max, 55 price02_min, 56 price02_max, 57 stock_min, 58 stock_max, 59 stock_unlimited_min, 60 stock_unlimited_max, 61 category_rank, 62 T5.category_id, 63 T5.del_flg, 64 T5.status, 65 T5.name, 66 T5.comment1, 67 T5.comment2, 68 T5.comment3, 69 T5.rank, 70 T5.main_list_comment, 71 T5.main_image, 72 T5.main_list_image, 73 T5.product_flag, 74 T5.deliv_date_id, 75 T5.sale_limit, 76 T5.point_rate, 77 T5.sale_unlimited, 78 T5.create_date, 79 T5.deliv_fee, 80 T5.update_date 81 FROM 82 ((SELECT T1.product_id, 83 T1.del_flg, 84 T1.status, 85 T1.name, 86 T1.comment1, 87 T1.comment2, 88 T1.comment3, 89 T1.main_list_comment, 90 T1.main_image, 91 T1.main_list_image, 92 T1.product_flag, 93 T1.deliv_date_id, 94 T1.sale_limit, 95 T1.point_rate, 96 T1.sale_unlimited, 97 T1.create_date, 98 T1.deliv_fee, 99 T2.category_id, 100 T1.rank, 101 T1.update_date 102 FROM dtb_products AS T1 103 LEFT JOIN dtb_product_categories AS T2 104 ON T1.product_id = T2.product_id) AS T3 105 RIGHT JOIN 106 (SELECT product_id AS product_id_sub, 107 MIN(product_code) AS product_code_min, 108 MAX(product_code) AS product_code_max, 109 MIN(price01) AS price01_min, 110 MAX(price01) AS price01_max, 111 MIN(price02) AS price02_min, 112 MAX(price02) AS price02_max, 113 MIN(stock) AS stock_min, 114 MAX(stock) AS stock_max, 115 MIN(stock_unlimited) AS stock_unlimited_min, 116 MAX(stock_unlimited) AS stock_unlimited_max 117 FROM dtb_products_class 118 GROUP BY product_id) AS T4 119 ON T3.product_id = T4.product_id_sub) AS T5 120 LEFT JOIN 121 (SELECT rank AS category_rank, 122 category_id AS sub_category_id 123 FROM dtb_category) AS T6 124 ON T5.category_id = T6.sub_category_id; 125 126 CREATE VIEW vw_products_allclass_detail AS 127 SELECT product_id, 128 price01_min, 129 price01_max, 130 price02_min, 131 price02_max, 132 stock_min, 133 stock_max, 134 stock_unlimited_min, 135 stock_unlimited_max, 136 del_flg, 137 status, 138 name, 139 comment1, 140 comment2, 141 comment3, 142 deliv_fee, 143 main_comment, 144 main_image, 145 main_large_image, 146 sub_title1, 147 sub_comment1, 148 sub_image1, 149 sub_large_image1, 150 sub_title2, 151 sub_comment2, 152 sub_image2, 153 sub_large_image2, 154 sub_title3, 155 sub_comment3, 156 sub_image3, 157 sub_large_image3, 158 sub_title4, 159 sub_comment4, 160 sub_image4, 161 sub_large_image4, 162 sub_title5, 163 sub_comment5, 164 sub_image5, 165 sub_large_image5, 166 product_flag, 167 deliv_date_id, 168 sale_limit, 169 point_rate, 170 sale_unlimited, 171 file1,file2, 172 category_id 173 FROM (dtb_products AS T1 174 RIGHT JOIN 175 (SELECT 176 product_id AS product_id_sub, 177 MIN(price01) AS price01_min, 178 MAX(price01) AS price01_max, 179 MIN(price02) AS price02_min, 180 MAX(price02) AS price02_max, 181 MIN(stock) AS stock_min, 182 MAX(stock) AS stock_max, 183 MIN(stock_unlimited) AS stock_unlimited_min, 184 MAX(stock_unlimited) AS stock_unlimited_max 185 FROM dtb_products_class 186 GROUP BY product_id) AS T2 187 ON T1.product_id = T2.product_id_sub) AS T3 188 LEFT JOIN (SELECT rank AS category_rank, 189 category_id AS sub_category_id 190 FROM dtb_category) AS T4 191 ON T3.category_id = T4.sub_category_id; 155 SELECT 156 alldtl.*, 157 dtb_category.rank AS category_rank, 158 T2.category_id 159 FROM 160 vw_products_allclass_detail AS alldtl 161 LEFT JOIN 162 dtb_product_categories AS T2 163 ON alldtl.product_id = T2.product_id 164 LEFT JOIN 165 dtb_category 166 ON T2.category_id = dtb_category.category_id 167 ; 192 168 193 169 CREATE VIEW vw_product_class AS … … 225 201 LEFT JOIN dtb_category_total_count AS T2 226 202 ON T1.category_id = T2.category_id 227 203 ; -
branches/comu-ver2/html/install/sql/drop_view.sql
r15079 r17549 1 1 DROP VIEW vw_category_count; 2 2 DROP VIEW vw_product_class; 3 DROP VIEW vw_products_allclass; 3 4 DROP VIEW vw_products_allclass_detail; 4 DROP VIEW vw_products_allclass;5 5 DROP VIEW vw_products_nonclass; 6 6 DROP VIEW vw_cross_products_class;
Note: See TracChangeset
for help on using the changeset viewer.
