Changeset 18388 for branches/version-2_4
- Timestamp:
- 2009/11/20 10:45:14 (17 years ago)
- Location:
- branches/version-2_4
- Files:
-
- 4 edited
-
data/class/SC_Query.php (modified) (1 diff)
-
data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php (modified) (2 diffs)
-
data/class/pages/products/LC_Page_Products_List.php (modified) (4 diffs)
-
html/install/sql/create_view.sql (modified) (3 diffs)
Legend:
- Unmodified
- Added
- Removed
-
branches/version-2_4/data/class/SC_Query.php
r17642 r18388 432 432 } 433 433 // SQL文の実行 434 return $this->conn->getCol($sqlse, $col, $arrval);434 return $this->conn->getCol($sqlse, 0, $arrval); 435 435 } 436 436 -
branches/version-2_4/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
r17939 r18388 272 272 */ 273 273 function viewToSubQuery() { 274 $sql['vw_products_allclass_detail'] =<<< __EOS__ 275 ( 276 SELECT 277 dtb_products.product_id, 278 dtb_products.name, 279 dtb_products.deliv_fee, 280 dtb_products.sale_limit, 281 dtb_products.sale_unlimited, 282 dtb_products.rank, 283 dtb_products.status, 284 dtb_products.product_flag, 285 dtb_products.point_rate, 286 dtb_products.comment1, 287 dtb_products.comment2, 288 dtb_products.comment3, 289 dtb_products.comment4, 290 dtb_products.comment5, 291 dtb_products.comment6, 292 dtb_products.note, 293 dtb_products.file1, 294 dtb_products.file2, 295 dtb_products.file3, 296 dtb_products.file4, 297 dtb_products.file5, 298 dtb_products.file6, 299 dtb_products.main_list_comment, 300 dtb_products.main_list_image, 301 dtb_products.main_comment, 302 dtb_products.main_image, 303 dtb_products.main_large_image, 304 dtb_products.sub_title1, 305 dtb_products.sub_comment1, 306 dtb_products.sub_image1, 307 dtb_products.sub_large_image1, 308 dtb_products.sub_title2, 309 dtb_products.sub_comment2, 310 dtb_products.sub_image2, 311 dtb_products.sub_large_image2, 312 dtb_products.sub_title3, 313 dtb_products.sub_comment3, 314 dtb_products.sub_image3, 315 dtb_products.sub_large_image3, 316 dtb_products.sub_title4, 317 dtb_products.sub_comment4, 318 dtb_products.sub_image4, 319 dtb_products.sub_large_image4, 320 dtb_products.sub_title5, 321 dtb_products.sub_comment5, 322 dtb_products.sub_image5, 323 dtb_products.sub_large_image5, 324 dtb_products.sub_title6, 325 dtb_products.sub_comment6, 326 dtb_products.sub_image6, 327 dtb_products.sub_large_image6, 328 dtb_products.del_flg, 329 dtb_products.creator_id, 330 dtb_products.create_date, 331 dtb_products.update_date, 332 dtb_products.deliv_date_id, 333 T4.product_code_min, 334 T4.product_code_max, 335 T4.price01_min, 336 T4.price01_max, 337 T4.price02_min, 338 T4.price02_max, 339 T4.stock_min, 340 T4.stock_max, 341 T4.stock_unlimited_min, 342 T4.stock_unlimited_max, 343 T4.class_count 344 FROM 345 dtb_products 346 LEFT JOIN 347 ( 348 SELECT 349 product_id, 350 MIN(product_code) AS product_code_min, 351 MAX(product_code) AS product_code_max, 352 MIN(price01) AS price01_min, 353 MAX(price01) AS price01_max, 354 MIN(price02) AS price02_min, 355 MAX(price02) AS price02_max, 356 MIN(stock) AS stock_min, 357 MAX(stock) AS stock_max, 358 MIN(stock_unlimited) AS stock_unlimited_min, 359 MAX(stock_unlimited) AS stock_unlimited_max, 360 COUNT(*) as class_count 361 FROM dtb_products_class 362 GROUP BY product_id 363 ) AS T4 364 ON dtb_products.product_id = T4.product_id 365 ) 366 __EOS__; 367 274 368 return array( 275 369 "vw_cross_class" => ' … … 365 459 ON T1.product_id = T2.product_id_sub) ', 366 460 367 "vw_products_allclass" => ' 368 (SELECT 369 T2.product_id 370 ,T1.product_code_min 371 ,T1.product_code_max 372 ,T1.price01_min 373 ,T1.price01_max 374 ,T1.price02_min 375 ,T1.price02_max 376 ,T1.stock_min 377 ,T1.stock_max 378 ,T1.stock_unlimited_min 379 ,T1.stock_unlimited_max 380 ,T2.del_flg 381 ,T2.status 382 ,T2.name 383 ,T2.comment1 384 ,T2.comment2 385 ,T2.comment3 386 ,T2.main_list_comment 387 ,T2.main_image 388 ,T2.main_list_image 389 ,T2.product_flag 390 ,T2.deliv_date_id 391 ,T2.sale_limit 392 ,T2.point_rate 393 ,T2.sale_unlimited 394 ,T2.create_date 395 ,T2.deliv_fee 396 ,T3.rank 397 ,T4.rank AS category_rank 398 ,T4.category_id 461 "vw_products_allclass" => " 462 ( 463 SELECT 464 alldtl.*, 465 dtb_category.rank AS category_rank, 466 T2.category_id, 467 T2.rank AS product_rank 399 468 FROM 400 ( 401 (dtb_products AS T2 RIGHT JOIN 402 (SELECT 403 product_id AS product_id_sub 404 ,MIN(product_code) AS product_code_min 405 ,MAX(product_code) AS product_code_max 406 ,MIN(price01) AS price01_min 407 ,MAX(price01) AS price01_max 408 ,MIN(price02) AS price02_min 409 ,MAX(price02) AS price02_max 410 ,MIN(stock) AS stock_min 411 ,MAX(stock) AS stock_max 412 ,MIN(stock_unlimited) AS stock_unlimited_min 413 ,MAX(stock_unlimited) AS stock_unlimited_max 414 FROM dtb_products_class GROUP BY product_id 415 ) AS T1 ON T1.product_id_sub = T2.product_id 416 ) LEFT JOIN dtb_product_categories AS T3 ON T2.product_id = T3.product_id 417 ) LEFT JOIN dtb_category AS T4 ON T3.category_id = T4.category_id 418 ) ', 419 420 "vw_products_allclass_detail" => ' 421 (SELECT product_id,price01_min,price01_max,price02_min,price02_max,stock_min,stock_max,stock_unlimited_min,stock_unlimited_max, 422 del_flg,status,name,comment1,comment2,comment3,deliv_fee,main_comment,main_image,main_large_image, 423 sub_title1,sub_comment1,sub_image1,sub_large_image1, 424 sub_title2,sub_comment2,sub_image2,sub_large_image2, 425 sub_title3,sub_comment3,sub_image3,sub_large_image3, 426 sub_title4,sub_comment4,sub_image4,sub_large_image4, 427 sub_title5,sub_comment5,sub_image5,sub_large_image5, 428 product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id 429 FROM ( SELECT * FROM (dtb_products AS T1 RIGHT JOIN 430 (SELECT 431 product_id AS product_id_sub, 432 MIN(price01) AS price01_min, 433 MAX(price01) AS price01_max, 434 MIN(price02) AS price02_min, 435 MAX(price02) AS price02_max, 436 MIN(stock) AS stock_min, 437 MAX(stock) AS stock_max, 438 MIN(stock_unlimited) AS stock_unlimited_min, 439 MAX(stock_unlimited) AS stock_unlimited_max 440 FROM dtb_products_class GROUP BY product_id) AS T2 441 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 442 ON T3.category_id = T4.sub_category_id) ', 469 {$sql['vw_products_allclass_detail']} AS alldtl 470 LEFT JOIN 471 dtb_product_categories AS T2 472 ON alldtl.product_id = T2.product_id 473 LEFT JOIN 474 dtb_category 475 ON T2.category_id = dtb_category.category_id 476 ) ", 477 478 "vw_products_allclass_detail" => $sql['vw_products_allclass_detail'], 443 479 444 480 "vw_product_class" => ' -
branches/version-2_4/data/class/pages/products/LC_Page_Products_List.php
r18113 r18388 380 380 $objDb = new SC_Helper_DB_Ex(); 381 381 $this->tpl_pageno = defined("MOBILE_SITE") ? @$_GET['pageno'] : @$_POST['pageno']; 382 383 //表示順序 384 switch($orderby) { 385 386 //価格順 387 case 'price': 388 $col = "DISTINCT price02_min, product_id, product_code_min, product_code_max," 389 . " name, comment1, comment2, comment3," 390 . " main_list_comment, main_image, main_list_image," 391 . " price01_min, price01_max, price02_max," 392 . " stock_min, stock_max, stock_unlimited_min, stock_unlimited_max," 393 . " point_rate, sale_limit, sale_unlimited, deliv_date_id, deliv_fee," 394 . " status, product_flag, create_date, del_flg"; 395 $from = "vw_products_allclass AS T1"; 396 $order = "price02_min, product_id"; 397 break; 398 399 //新着順 400 case 'date': 401 $col = "DISTINCT create_date, product_id, product_code_min, product_code_max," 402 . " name, comment1, comment2, comment3," 403 . " main_list_comment, main_image, main_list_image," 404 . " price01_min, price01_max, price02_min, price02_max," 405 . " stock_min, stock_max, stock_unlimited_min, stock_unlimited_max," 406 . " point_rate, sale_limit, sale_unlimited, deliv_date_id, deliv_fee," 407 . " status, product_flag, del_flg"; 408 $from = "vw_products_allclass AS T1"; 409 $order = "create_date DESC, product_id"; 410 break; 411 412 default: 413 $col = "DISTINCT T1.product_id, product_code_min, product_code_max," 414 . " price01_min, price01_max, price02_min, price02_max," 415 . " stock_min, stock_max, stock_unlimited_min," 416 . " stock_unlimited_max, del_flg, status, name, comment1," 417 . " comment2, comment3, main_list_comment, main_image," 418 . " main_list_image, product_flag, deliv_date_id, sale_limit," 419 . " point_rate, sale_unlimited, create_date, deliv_fee, " 420 . " T4.product_rank, T4.category_rank"; 421 $from = "vw_products_allclass AS T1" 422 . " JOIN (" 423 . " SELECT max(T3.rank) AS category_rank," 424 . " max(T2.rank) AS product_rank," 425 . " T2.product_id" 426 . " FROM dtb_product_categories T2" 427 . " JOIN dtb_category T3 USING (category_id)" 428 . " GROUP BY product_id) AS T4 USING (product_id)"; 429 $order = "T4.category_rank DESC, T4.product_rank DESC"; 430 break; 431 } 432 382 $arrval = array(); 383 $arrval_order = array(); 384 $arrval_category = array(); 385 386 // カテゴリからのWHERE文字列取得 387 388 if ( $category_id ) { 389 list($tmp_where, $arrval_category) = $objDb->sfGetCatWhere($category_id); 390 if (strlen($tmp_where) >= 1) { 391 $where_category = "AND $tmp_where"; 392 //並び替え用（MySQL4.1対応） 393 $where_category_order = "AND " ."T2.".trim($tmp_where); 394 } 395 } 396 397 // ▼対象商品IDの抽出 433 398 // 商品検索条件の作成（未削除、表示） 434 399 $where = "del_flg = 0 AND status = 1 "; 435 // カテゴリからのWHERE文字列取得 436 if ( $category_id ) { 437 list($tmp_where, $arrval) = $objDb->sfGetCatWhere($category_id); 438 if($tmp_where != "") { 439 $where.= " AND $tmp_where"; 440 } 400 401 // 在庫無し商品の非表示 402 if (NOSTOCK_HIDDEN === true) { 403 $where .= ' AND (stock_max >= 1 OR stock_unlimited_max = 1)'; 404 } 405 406 if (strlen($where_category) >= 1) { 407 $where.= " $where_category"; 408 $arrval = array_merge($arrval, $arrval_category); 441 409 } 442 410 … … 457 425 } 458 426 459 if (empty($arrval)) { 460 $arrval = array(); 427 $arrProduct_id = $objQuery->getCol('vw_products_allclass AS allcls', 'DISTINCT product_id', $where, $arrval); 428 // ▲対象商品IDの抽出 429 430 // 行数の取得 431 $linemax = count($arrProduct_id); 432 433 $this->tpl_linemax = $linemax; // 何件が該当しました。表示用 434 435 // ページ送りの取得 436 $this->objNavi = new SC_PageNavi($this->tpl_pageno, $linemax, $disp_num, "fnNaviPage", NAVI_PMAX); 437 438 $strnavi = $this->objNavi->strnavi; 439 $strnavi = str_replace('onclick="fnNaviPage', 'onclick="form1.mode.value=\''.'\'; fnNaviPage', $strnavi); 440 // 表示文字列 441 $this->tpl_strnavi = empty($strnavi) ? " " : $strnavi; 442 $startno = $this->objNavi->start_row; // 開始行 443 444 // ▼商品詳細取得 445 $col = <<< __EOS__ 446 product_id 447 ,product_code_min 448 ,product_code_max 449 ,name 450 ,comment1 451 ,comment2 452 ,comment3 453 ,main_list_comment 454 ,main_image 455 ,main_list_image 456 ,price01_min 457 ,price01_max 458 ,price02_min 459 ,price02_max 460 ,stock_min 461 ,stock_max 462 ,stock_unlimited_min 463 ,stock_unlimited_max 464 ,point_rate 465 ,sale_limit 466 ,sale_unlimited 467 ,deliv_date_id 468 ,deliv_fee 469 ,status 470 ,product_flag 471 ,del_flg 472 __EOS__; 473 474 $from = "vw_products_allclass_detail AS alldtl"; 475 476 // WHERE 句 477 $where = '0=0'; 478 if (is_array($arrProduct_id) && !empty($arrProduct_id)) { 479 $where .= ' AND product_id IN (' . implode(',', $arrProduct_id) . ')'; 480 } 481 482 //表示順序 483 switch($orderby) { 484 485 //販売価格順 486 case 'price': 487 $order = "price02_min, product_id"; 488 break; 489 490 //新着順 491 case 'date': 492 $order = "create_date DESC, product_id"; 493 break; 494 495 default: 496 $order = <<< __EOS__ 497 ( 498 SELECT 499 T3.rank 500 FROM 501 dtb_product_categories T2 502 JOIN dtb_category T3 503 USING (category_id) 504 WHERE T2.product_id = alldtl.product_id 505 $where_category_order 506 ORDER BY T3.rank DESC, T2.rank DESC 507 LIMIT 1 508 ) DESC 509 ,( 510 SELECT 511 T2.rank 512 FROM 513 dtb_product_categories T2 514 JOIN dtb_category T3 515 USING (category_id) 516 WHERE T2.product_id = alldtl.product_id 517 $where_category_order 518 ORDER BY T3.rank DESC, T2.rank DESC 519 LIMIT 1 520 ) DESC 521 ,product_id 522 __EOS__; 523 $arrval_order = array_merge($arrval_category, $arrval_category); 524 break; 525 } 526 527 // 取得範囲の指定(開始行番号、行数のセット) 528 $objQuery->setlimitoffset($disp_num, $startno); 529 // 表示順序 530 $objQuery->setorder($order); 531 532 // 検索結果の取得 533 $this->arrProducts = $objQuery->select($col, $from, $where, $arrval_order); 534 // ▲商品詳細取得 535 536 // 規格名一覧 537 $arrClassName = $objDb->sfGetIDValueList("dtb_class", "class_id", "name"); 538 // 規格分類名一覧 539 $arrClassCatName = $objDb->sfGetIDValueList("dtb_classcategory", "classcategory_id", "name"); 540 // 規格セレクトボックス設定 541 if($disp_num == 15) { 542 for($i = 0; $i < count($this->arrProducts); $i++) { 543 $this->lfMakeSelect($this->arrProducts[$i]['product_id'], $arrClassName, $arrClassCatName); 544 // 購入制限数を取得 545 $this->lfGetSaleLimit($this->arrProducts[$i]); 546 } 547 } 548 } 549 550 /* 商品一覧の表示 */ 551 function lfDispProductsList2($category_id, $name, $disp_num, $orderby) { 552 553 $objQuery = new SC_Query(); 554 $objDb = new SC_Helper_DB_Ex(); 555 $this->tpl_pageno = defined("MOBILE_SITE") ? @$_GET['pageno'] : @$_POST['pageno']; 556 557 $arrval = array(); 558 $arrval_order = array(); 559 $arrval_category = array(); 560 561 // カテゴリからのWHERE文字列取得 562 if ( $category_id ) { 563 list($tmp_where, $arrval_category) = $objDb->sfGetCatWhere($category_id); 564 if (strlen($tmp_where) >= 1) { 565 $where_category = "AND $tmp_where"; 566 } 567 } 568 569 // 商品検索条件の作成（未削除、表示） 570 $where = "del_flg = 0 AND status = 1 "; 571 572 // 在庫無し商品の非表示 573 if (NOSTOCK_HIDDEN === true) { 574 $where .= ' AND (allcls.stock_max >= 1 OR allcls.stock_unlimited_max = 1)'; 575 } 576 577 if (strlen($where_category) >= 1) { 578 $where.= " $where_category"; 579 $arrval = array_merge($arrval, $arrval_category); 580 } 581 582 // 商品名をwhere文に 583 $name = ereg_replace(",", "", $name);// XXX 584 // 全角スペースを半角スペースに変換 585 $name = str_replace(' ', ' ', $name); 586 // スペースでキーワードを分割 587 $names = preg_split("/ +/", $name); 588 // 分割したキーワードを一つずつwhere文に追加 589 foreach ($names as $val) { 590 if ( strlen($val) > 0 ){ 591 $where .= " AND ( name ILIKE ? OR comment3 ILIKE ?) "; 592 $ret = SC_Utils_Ex::sfManualEscape($val); 593 $arrval[] = "%$ret%"; 594 $arrval[] = "%$ret%"; 595 } 461 596 } 462 597 … … 478 613 $startno = $this->objNavi->start_row; // 開始行 479 614 615 // ▼商品詳細取得 616 $col = <<< __EOS__ 617 product_id 618 ,product_code_min 619 ,product_code_max 620 ,name 621 ,comment1 622 ,comment2 623 ,comment3 624 ,main_list_comment 625 ,main_image 626 ,main_list_image 627 ,price01_min 628 ,price01_max 629 ,price02_min 630 ,price02_max 631 ,stock_min 632 ,stock_max 633 ,stock_unlimited_min 634 ,stock_unlimited_max 635 ,point_rate 636 ,sale_limit 637 ,deliv_date_id 638 ,deliv_fee 639 ,status 640 ,product_flag 641 ,del_flg 642 __EOS__; 643 //$from = "vw_products_allclass AS allcls"; 644 $from = "vw_products_allclass_detail AS alldtl"; 645 646 // WHERE 句 647 $where = '0=0'; 648 if (is_array($arrProduct_id) && !empty($arrProduct_id)) { 649 $where .= ' AND product_id IN (' . implode(',', $arrProduct_id) . ')'; 650 } 651 652 //表示順序 653 switch($orderby) { 654 655 //販売価格順 656 case 'price': 657 $order = "price02_min, product_id"; 658 break; 659 660 //新着順 661 case 'date': 662 $order = "create_date DESC, product_id"; 663 break; 664 665 default: 666 $order = <<< __EOS__ 667 ( 668 SELECT 669 T3.rank 670 FROM 671 dtb_product_categories T2 672 JOIN dtb_category T3 673 USING (category_id) 674 WHERE T2.product_id = alldtl.product_id 675 $where_category 676 ORDER BY T3.rank DESC, T2.rank DESC 677 LIMIT 1 678 ) DESC 679 ,( 680 SELECT 681 T2.rank 682 FROM 683 dtb_product_categories T2 684 JOIN dtb_category T3 685 USING (category_id) 686 WHERE T2.product_id = alldtl.product_id 687 $where_category 688 ORDER BY T3.rank DESC, T2.rank DESC 689 LIMIT 1 690 ) DESC 691 ,product_id 692 __EOS__; 693 $arrval_order = array_merge($arrval_category, $arrval_category); 694 break; 695 } 696 480 697 // 取得範囲の指定(開始行番号、行数のセット) 481 698 $objQuery->setlimitoffset($disp_num, $startno); … … 484 701 485 702 // 検索結果の取得 486 $this->arrProducts = $objQuery->select($col, $from, $where, $arrval); 703 $this->arrProducts = $objQuery->select($col, $from, $where, $arrval_order); 704 // ▲商品詳細取得 487 705 488 706 // 規格名一覧 -
branches/version-2_4/html/install/sql/create_view.sql
r16546 r18388 47 47 ON T1.product_id = T2.product_id_sub; 48 48 49 CREATE VIEW vw_products_allclass_detail AS 50 SELECT 51 dtb_products.product_id, 52 dtb_products."name", 53 dtb_products.deliv_fee, 54 dtb_products.sale_limit, 55 dtb_products.rank, 56 dtb_products.status, 57 dtb_products.product_flag, 58 dtb_products.point_rate, 59 dtb_products.sale_unlimited, 60 dtb_products.comment1, 61 dtb_products.comment2, 62 dtb_products.comment3, 63 dtb_products.comment4, 64 dtb_products.comment5, 65 dtb_products.comment6, 66 dtb_products.note, 67 dtb_products.file1, 68 dtb_products.file2, 69 dtb_products.file3, 70 dtb_products.file4, 71 dtb_products.file5, 72 dtb_products.file6, 73 dtb_products.main_list_comment, 74 dtb_products.main_list_image, 75 dtb_products.main_comment, 76 dtb_products.main_image, 77 dtb_products.main_large_image, 78 dtb_products.sub_title1, 79 dtb_products.sub_comment1, 80 dtb_products.sub_image1, 81 dtb_products.sub_large_image1, 82 dtb_products.sub_title2, 83 dtb_products.sub_comment2, 84 dtb_products.sub_image2, 85 dtb_products.sub_large_image2, 86 dtb_products.sub_title3, 87 dtb_products.sub_comment3, 88 dtb_products.sub_image3, 89 dtb_products.sub_large_image3, 90 dtb_products.sub_title4, 91 dtb_products.sub_comment4, 92 dtb_products.sub_image4, 93 dtb_products.sub_large_image4, 94 dtb_products.sub_title5, 95 dtb_products.sub_comment5, 96 dtb_products.sub_image5, 97 dtb_products.sub_large_image5, 98 dtb_products.sub_title6, 99 dtb_products.sub_comment6, 100 dtb_products.sub_image6, 101 dtb_products.sub_large_image6, 102 dtb_products.del_flg, 103 dtb_products.creator_id, 104 dtb_products.create_date, 105 dtb_products.update_date, 106 dtb_products.deliv_date_id, 107 T4.product_code_min, 108 T4.product_code_max, 109 T4.price01_min, 110 T4.price01_max, 111 T4.price02_min, 112 T4.price02_max, 113 T4.stock_min, 114 T4.stock_max, 115 T4.stock_unlimited_min, 116 T4.stock_unlimited_max, 117 T4.class_count 118 FROM 119 dtb_products 120 LEFT JOIN 121 ( 122 SELECT 123 product_id, 124 MIN(product_code) AS product_code_min, 125 MAX(product_code) AS product_code_max, 126 MIN(price01) AS price01_min, 127 MAX(price01) AS price01_max, 128 MIN(price02) AS price02_min, 129 MAX(price02) AS price02_max, 130 MIN(stock) AS stock_min, 131 MAX(stock) AS stock_max, 132 MIN(stock_unlimited) AS stock_unlimited_min, 133 MAX(stock_unlimited) AS stock_unlimited_max, 134 COUNT(*) as class_count 135 FROM dtb_products_class 136 GROUP BY product_id 137 ) AS T4 138 ON dtb_products.product_id = T4.product_id 139 ; 140 49 141 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 FROM 81 ((SELECT T1.product_id, 82 T1.del_flg, 83 T1.status, 84 T1.name, 85 T1.comment1, 86 T1.comment2, 87 T1.comment3, 88 T1.main_list_comment, 89 T1.main_image, 90 T1.main_list_image, 91 T1.product_flag, 92 T1.deliv_date_id, 93 T1.sale_limit, 94 T1.point_rate, 95 T1.sale_unlimited, 96 T1.create_date, 97 T1.deliv_fee, 98 T2.category_id, 99 T1.rank 100 FROM dtb_products AS T1 101 LEFT JOIN dtb_product_categories AS T2 102 ON T1.product_id = T2.product_id) AS T3 103 RIGHT JOIN 104 (SELECT product_id AS product_id_sub, 105 MIN(product_code) AS product_code_min, 106 MAX(product_code) AS product_code_max, 107 MIN(price01) AS price01_min, 108 MAX(price01) AS price01_max, 109 MIN(price02) AS price02_min, 110 MAX(price02) AS price02_max, 111 MIN(stock) AS stock_min, 112 MAX(stock) AS stock_max, 113 MIN(stock_unlimited) AS stock_unlimited_min, 114 MAX(stock_unlimited) AS stock_unlimited_max 115 FROM dtb_products_class 116 GROUP BY product_id) AS T4 117 ON T3.product_id = T4.product_id_sub) AS T5 118 LEFT JOIN 119 (SELECT rank AS category_rank, 120 category_id AS sub_category_id 121 FROM dtb_category) AS T6 122 ON T5.category_id = T6.sub_category_id; 123 124 CREATE VIEW vw_products_allclass_detail AS 125 SELECT product_id, 126 price01_min, 127 price01_max, 128 price02_min, 129 price02_max, 130 stock_min, 131 stock_max, 132 stock_unlimited_min, 133 stock_unlimited_max, 134 del_flg, 135 status, 136 name, 137 comment1, 138 comment2, 139 comment3, 140 deliv_fee, 141 main_comment, 142 main_image, 143 main_large_image, 144 sub_title1, 145 sub_comment1, 146 sub_image1, 147 sub_large_image1, 148 sub_title2, 149 sub_comment2, 150 sub_image2, 151 sub_large_image2, 152 sub_title3, 153 sub_comment3, 154 sub_image3, 155 sub_large_image3, 156 sub_title4, 157 sub_comment4, 158 sub_image4, 159 sub_large_image4, 160 sub_title5, 161 sub_comment5, 162 sub_image5, 163 sub_large_image5, 164 product_flag, 165 deliv_date_id, 166 sale_limit, 167 point_rate, 168 sale_unlimited, 169 file1,file2, 170 category_id 171 FROM (dtb_products AS T1 172 RIGHT JOIN 173 (SELECT 174 product_id AS product_id_sub, 175 MIN(price01) AS price01_min, 176 MAX(price01) AS price01_max, 177 MIN(price02) AS price02_min, 178 MAX(price02) AS price02_max, 179 MIN(stock) AS stock_min, 180 MAX(stock) AS stock_max, 181 MIN(stock_unlimited) AS stock_unlimited_min, 182 MAX(stock_unlimited) AS stock_unlimited_max 183 FROM dtb_products_class 184 GROUP BY product_id) AS T2 185 ON T1.product_id = T2.product_id_sub) AS T3 186 LEFT JOIN (SELECT rank AS category_rank, 187 category_id AS sub_category_id 188 FROM dtb_category) AS T4 189 ON T3.category_id = T4.sub_category_id; 142 SELECT 143 alldtl.*, 144 dtb_category.rank AS category_rank, 145 T2.category_id, 146 T2.rank AS product_rank 147 FROM 148 vw_products_allclass_detail AS alldtl 149 LEFT JOIN 150 dtb_product_categories AS T2 151 ON alldtl.product_id = T2.product_id 152 LEFT JOIN 153 dtb_category 154 ON T2.category_id = dtb_category.category_id 155 ; 190 156 191 157 CREATE VIEW vw_product_class AS … … 205 171 stock_unlimited, 206 172 product_code 207 FROM (dtb_products_class AS T1 173 FROM (dtb_products_class AS T1 208 174 LEFT JOIN dtb_classcategory AS T2 209 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 175 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 210 176 LEFT JOIN dtb_classcategory AS T4 211 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 177 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 212 178 LEFT JOIN dtb_products AS T6 213 179 ON product_id_sub = T6.product_id; … … 220 186 T1.rank, 221 187 T2.product_count 222 FROM dtb_category AS T1 188 FROM dtb_category AS T1 223 189 LEFT JOIN dtb_category_total_count AS T2 224 190 ON T1.category_id = T2.category_id
Note: See TracChangeset
for help on using the changeset viewer.
