- Timestamp:
- 2012/02/28 22:03:29 (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/version-2_12-dev/data/class/pages/admin/total/LC_Page_Admin_Total.php
r21527 r21562 76 76 77 77 // 期間別集計のkey名 78 $this->arrSearchForm2 = array('search_startyear', 79 'search_startmonth', 80 'search_startday', 81 'search_endyear', 82 'search_endmonth', 83 'search_endday'); 78 $this->arrSearchForm2 = array( 79 'search_startyear', 80 'search_startmonth', 81 'search_startday', 82 'search_endyear', 83 'search_endmonth', 84 'search_endday', 85 ); 84 86 } 85 87 … … 275 277 } 276 278 switch ($key) { 277 case 'search_startyear':278 $sdate = $objFormParam->getValue('search_startyear') . '/' . $objFormParam->getValue('search_startmonth') . '/' . $objFormParam->getValue('search_startday');279 break;280 case 'search_endyear':281 $edate = $objFormParam->getValue('search_endyear') . '/' . $objFormParam->getValue('search_endmonth') . '/' . $objFormParam->getValue('search_endday');282 break;283 case 'search_startyear_m':284 list($sdate, $edate) = SC_Utils_Ex::sfTermMonth($objFormParam->getValue('search_startyear_m'),285 $objFormParam->getValue('search_startmonth_m'),286 CLOSE_DAY);287 break;288 default:289 break;279 case 'search_startyear': 280 $sdate = $objFormParam->getValue('search_startyear') . '/' . $objFormParam->getValue('search_startmonth') . '/' . $objFormParam->getValue('search_startday'); 281 break; 282 case 'search_endyear': 283 $edate = $objFormParam->getValue('search_endyear') . '/' . $objFormParam->getValue('search_endmonth') . '/' . $objFormParam->getValue('search_endday'); 284 break; 285 case 'search_startyear_m': 286 list($sdate, $edate) = SC_Utils_Ex::sfTermMonth($objFormParam->getValue('search_startyear_m'), 287 $objFormParam->getValue('search_startmonth_m'), 288 CLOSE_DAY); 289 break; 290 default: 291 break; 290 292 } 291 293 } … … 475 477 switch ($type) { 476 478 // 全体 477 case 'all':478 break;479 case 'member':480 if ($where != '') {481 $where.= ' AND ';482 }483 $where.= " $col_member <> 0";484 break;485 case 'nonmember':486 if ($where != '') {487 $where.= ' AND ';488 }489 $where.= " $col_member = 0";490 break;491 default:492 break;479 case 'all': 480 break; 481 case 'member': 482 if ($where != '') { 483 $where.= ' AND '; 484 } 485 $where.= " $col_member <> 0"; 486 break; 487 case 'nonmember': 488 if ($where != '') { 489 $where.= ' AND '; 490 } 491 $where.= " $col_member = 0"; 492 break; 493 default: 494 break; 493 495 } 494 496 … … 500 502 $objQuery = SC_Query_Ex::getSingletonInstance(); 501 503 502 list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); 503 $where .= ' AND del_flg = 0 AND status <> ' . ORDER_CANCEL; 504 list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); 505 $where .= ' AND del_flg = 0 AND status <> ?'; 506 $arrWhereVal[] = ORDER_CANCEL; 504 507 505 508 // 会員集計の取得 506 $col = "509 $col = <<< __EOS__ 507 510 COUNT(order_id) AS order_count, 508 511 SUM(total) AS total, … … 513 516 END AS member, 514 517 order_sex 515 "; 518 __EOS__; 519 516 520 $from = 'dtb_order'; 517 521 518 522 $objQuery->setGroupBy('member, order_sex'); 519 523 520 $arrTotalResults = $objQuery->select($col, $from, $where, $arr val);524 $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal); 521 525 522 526 foreach (array_keys($arrTotalResults) as $key) { … … 539 543 $objQuery = SC_Query_Ex::getSingletonInstance(); 540 544 541 list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); 542 543 $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ' . ORDER_CANCEL; 544 545 $col = " 545 list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); 546 547 $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ?'; 548 $arrWhereVal[] = ORDER_CANCEL; 549 550 $col = <<< __EOS__ 546 551 product_id, 547 552 product_code, … … 550 555 COUNT(order_id) AS order_count, 551 556 price, 552 (price * SUM(quantity)) AS total"; 557 (price * SUM(quantity)) AS total 558 __EOS__; 553 559 554 560 $from = 'dtb_order_detail JOIN dtb_order USING(order_id)'; … … 563 569 //$objQuery->setGroupBy('product_id'); 564 570 $objQuery->setOrder('total DESC'); 565 $arrTotalResults = $objQuery->select($col, $from, $where, $arr val);571 $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal); 566 572 567 573 $tpl_image = $this->lfGetGraphPie($arrTotalResults, 'product_name', 'products_' . $type, '(売上比率)', $sdate, $edate); … … 573 579 function lfGetOrderJob($type, $sdate, $edate) { 574 580 $objQuery = SC_Query_Ex::getSingletonInstance(); 575 list($where, $arr val) = $this->lfGetWhereMember('dtb_order.create_date', $sdate, $edate, $type);576 577 $col = '581 list($where, $arrWhereVal) = $this->lfGetWhereMember('dtb_order.create_date', $sdate, $edate, $type); 582 583 $col = <<< __EOS__ 578 584 job, 579 585 COUNT(order_id) AS order_count, 580 586 SUM(total) AS total, 581 587 AVG(total) AS total_average 582 ';588 __EOS__; 583 589 584 590 $from = 'dtb_order JOIN dtb_customer USING ( customer_id)'; 585 591 586 $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ' . ORDER_CANCEL; 592 $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ?'; 593 $arrWhereVal[] = ORDER_CANCEL; 587 594 588 595 $objQuery->setGroupBy('job'); 589 596 $objQuery->setOrder('total DESC'); 590 $arrTotalResults = $objQuery->select($col, $from, $where, $arr val);597 $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal); 591 598 592 599 foreach (array_keys($arrTotalResults) as $key) { … … 610 617 $objQuery = SC_Query_Ex::getSingletonInstance(); 611 618 612 list($where, $arr val) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);619 list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); 613 620 614 621 $dbFactory = SC_DB_DBFactory_Ex::getInstance(); 615 $col = $dbFactory->getOrderTotalAgeColSql() . ' AS age, 616 COUNT(order_id) AS order_count, 617 SUM(total) AS total, 618 AVG(total) AS total_average 619 '; 622 $col = $dbFactory->getOrderTotalAgeColSql() . ' AS age'; 623 $col .= ',COUNT(order_id) AS order_count'; 624 $col .= ',SUM(total) AS total'; 625 $col .= ',AVG(total) AS total_average'; 620 626 621 627 $from = 'dtb_order'; 622 628 623 $where .= ' AND del_flg = 0 AND status <> ' . ORDER_CANCEL; 629 $where .= ' AND del_flg = 0 AND status <> ?'; 630 $arrWhereVal[] = ORDER_CANCEL; 624 631 625 632 $objQuery->setGroupBy('age'); 626 633 $objQuery->setOrder('age DESC'); 627 $arrTotalResults = $objQuery->select($col, $from, $where, $arr val);634 $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal); 628 635 629 636 foreach (array_keys($arrTotalResults) as $key) { … … 647 654 $objQuery = SC_Query_Ex::getSingletonInstance(); 648 655 649 list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate); 650 $where .= ' AND del_flg = 0 AND status <> ' . ORDER_CANCEL; 656 list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate); 657 $where .= ' AND del_flg = 0 AND status <> ?'; 658 $arrWhereVal[] = ORDER_CANCEL; 651 659 652 660 switch ($type) { 653 case 'month':654 $xtitle = '(月別)';655 $ytitle = '(売上合計)';656 $format = '%m';657 break;658 case 'year':659 $xtitle = '(年別)';660 $ytitle = '(売上合計)';661 $format = '%Y';662 break;663 case 'wday':664 $xtitle = '(曜日別)';665 $ytitle = '(売上合計)';666 $format = '%a';667 break;668 case 'hour':669 $xtitle = '(時間別)';670 $ytitle = '(売上合計)';671 $format = '%H';672 break;673 default:674 $xtitle = '(日別)';675 $ytitle = '(売上合計)';676 $format = '%Y-%m-%d';677 $xincline = true;678 break;661 case 'month': 662 $xtitle = '(月別)'; 663 $ytitle = '(売上合計)'; 664 $format = '%m'; 665 break; 666 case 'year': 667 $xtitle = '(年別)'; 668 $ytitle = '(売上合計)'; 669 $format = '%Y'; 670 break; 671 case 'wday': 672 $xtitle = '(曜日別)'; 673 $ytitle = '(売上合計)'; 674 $format = '%a'; 675 break; 676 case 'hour': 677 $xtitle = '(時間別)'; 678 $ytitle = '(売上合計)'; 679 $format = '%H'; 680 break; 681 default: 682 $xtitle = '(日別)'; 683 $ytitle = '(売上合計)'; 684 $format = '%Y-%m-%d'; 685 $xincline = true; 686 break; 679 687 } 680 688 … … 686 694 $objQuery->setOrder('str_date'); 687 695 // 検索結果の取得 688 $arrTotalResults = $objQuery->select($col, 'dtb_order', $where );696 $arrTotalResults = $objQuery->select($col, 'dtb_order', $where, $arrWhereVal); 689 697 690 698 $arrTotalResults = $this->lfAddBlankLine($arrTotalResults, $type, $sdate, $edate); … … 800 808 switch ($page) { 801 809 // 商品別集計 802 case 'products':803 $arrTitleCol = array(804 '商品コード',805 '商品名',806 '購入件数',807 '数量',808 '単価',809 '金額',810 );811 $arrDataCol = array(812 'product_code',813 'product_name',814 'order_count',815 'products_count',816 'price',817 'total',818 );819 break;810 case 'products': 811 $arrTitleCol = array( 812 '商品コード', 813 '商品名', 814 '購入件数', 815 '数量', 816 '単価', 817 '金額', 818 ); 819 $arrDataCol = array( 820 'product_code', 821 'product_name', 822 'order_count', 823 'products_count', 824 'price', 825 'total', 826 ); 827 break; 820 828 // 職業別集計 821 case 'job':822 $arrTitleCol = array(823 '職業',824 '購入件数',825 '購入合計',826 '購入平均',827 );828 $arrDataCol = array(829 'job_name',830 'order_count',831 'total',832 'total_average',833 );834 break;829 case 'job': 830 $arrTitleCol = array( 831 '職業', 832 '購入件数', 833 '購入合計', 834 '購入平均', 835 ); 836 $arrDataCol = array( 837 'job_name', 838 'order_count', 839 'total', 840 'total_average', 841 ); 842 break; 835 843 // 会員別集計 836 case 'member':837 $arrTitleCol = array(838 '会員',839 '購入件数',840 '購入合計',841 '購入平均',842 );843 $arrDataCol = array(844 'member_name',845 'order_count',846 'total',847 'total_average',848 );849 break;844 case 'member': 845 $arrTitleCol = array( 846 '会員', 847 '購入件数', 848 '購入合計', 849 '購入平均', 850 ); 851 $arrDataCol = array( 852 'member_name', 853 'order_count', 854 'total', 855 'total_average', 856 ); 857 break; 850 858 // 年代別集計 851 case 'age':852 $arrTitleCol = array(853 '年齢',854 '購入件数',855 '購入合計',856 '購入平均',857 );858 $arrDataCol = array(859 'age_name',860 'order_count',861 'total',862 'total_average',863 );864 break;859 case 'age': 860 $arrTitleCol = array( 861 '年齢', 862 '購入件数', 863 '購入合計', 864 '購入平均', 865 ); 866 $arrDataCol = array( 867 'age_name', 868 'order_count', 869 'total', 870 'total_average', 871 ); 872 break; 865 873 // 期間別集計 866 default:867 $arrTitleCol = array(868 '期間',869 '購入件数',870 '男性',871 '女性',872 '男性(会員)',873 '男性(非会員)',874 '女性(会員)',875 '女性(非会員)',876 '購入合計',877 '購入平均',878 );879 $arrDataCol = array(880 'str_date',881 'total_order',882 'men',883 'women',884 'men_member',885 'men_nonmember',886 'women_member',887 'women_nonmember',888 'total',889 'total_average',890 );891 break;874 default: 875 $arrTitleCol = array( 876 '期間', 877 '購入件数', 878 '男性', 879 '女性', 880 '男性(会員)', 881 '男性(非会員)', 882 '女性(会員)', 883 '女性(非会員)', 884 '購入合計', 885 '購入平均', 886 ); 887 $arrDataCol = array( 888 'str_date', 889 'total_order', 890 'men', 891 'women', 892 'men_member', 893 'men_nonmember', 894 'women_member', 895 'women_nonmember', 896 'total', 897 'total_average', 898 ); 899 break; 892 900 } 893 901
Note: See TracChangeset
for help on using the changeset viewer.