Changeset 16723


Ignore:
Timestamp:
2007/11/06 19:54:20 (16 years ago)
Author:
satou
Message:

#106
会員別集計でキャンセル分も集計されてしまうバグを改修。

File:
1 edited

Legend:

Unmodified
Added
Removed

  • branches/beta/html/admin/total/index.php

    r15056 r16723  
    535535} 
    536536 
    537 // ²ñ°÷¡¢Èó²ñ°÷½¸·×¤ÎWHEREʬ¤ÎºîÀ® 
    538 function lfGetWhereMember($col_date, $sdate, $edate, $type, $col_member = "customer_id") { 
     537/** ´ü´ÖÊ̽¸·× **/ 
     538function lfGetOrderTerm($type, $sdate, $edate, $objPage, $graph = true) { 
     539    $tmp_col = "sum(total_order) as total_order, sum(men) as men, sum(women) as women,"; 
     540    $tmp_col.= "sum(men_member) as men_member, sum(men_nonmember) as men_nonmember,"; 
     541    $tmp_col.= "sum(women_member) as women_member, sum(women_nonmember) as women_nonmember,"; 
     542    $tmp_col.= "sum(total) as total, (avg(total_average)) as total_average"; 
     543    $objQuery = new SC_Query(); 
     544     
     545    switch($type) { 
     546    // ·îÊÌ 
     547    case 'month': 
     548        $col = $tmp_col . ",key_month"; 
     549        $objQuery->setgroupby("key_month"); 
     550        $objQuery->setOrder("key_month"); 
     551        $objPage->keyname = "key_month"; 
     552        $objPage->tpl_tail = "·î"; 
     553        $from = "dtb_bat_order_daily"; 
     554        $xtitle = "(·îÊÌ)"; 
     555        $ytitle = "(Çä¾å¹ç·×)"; 
     556        break; 
     557    // ǯÊÌ 
     558    case 'year': 
     559        $col = $tmp_col . ",key_year"; 
     560        $objQuery->setgroupby("key_year"); 
     561        $objQuery->setOrder("key_year"); 
     562        $objPage->keyname = "key_year"; 
     563        $objPage->tpl_tail = "ǯ"; 
     564        $from = "dtb_bat_order_daily"; 
     565        $xtitle = "(ǯÊÌ)"; 
     566        $ytitle = "(Çä¾å¹ç·×)"; 
     567        break; 
     568    // ÍËÆüÊÌ 
     569    case 'wday': 
     570        $col = $tmp_col . ",key_wday, wday"; 
     571        $objQuery->setgroupby("key_wday, wday"); 
     572        $objQuery->setOrder("wday"); 
     573        $objPage->keyname = "key_wday"; 
     574        $objPage->tpl_tail = "ÍËÆü"; 
     575        $from = "dtb_bat_order_daily"; 
     576        $xtitle = "(ÍËÆüÊÌ)"; 
     577        $ytitle = "(Çä¾å¹ç·×)"; 
     578        break; 
     579    // »þ´ÖÊÌ 
     580    case 'hour': 
     581        $col = $tmp_col . ",hour"; 
     582        $objQuery->setgroupby("hour"); 
     583        $objQuery->setOrder("hour"); 
     584        $objPage->keyname = "hour"; 
     585        $objPage->tpl_tail = "»þ"; 
     586        $from = "dtb_bat_order_daily_hour"; 
     587        $xtitle = "(»þ´ÖÊÌ)"; 
     588        $ytitle = "(Çä¾å¹ç·×)"; 
     589        break; 
     590    default: 
     591        $col = "*"; 
     592        $objQuery->setOrder("key_day"); 
     593        $objPage->keyname = "key_day"; 
     594        $from = "dtb_bat_order_daily"; 
     595        $xtitle = "(ÆüÊÌ)"; 
     596        $ytitle = "(Çä¾å¹ç·×)"; 
     597        break; 
     598    } 
     599     
    539600    // ¼èÆÀÆüÉդλØÄê 
    540601    if($sdate != "") { 
    … …  
    542603            $where.= " AND "; 
    543604        }            
    544         $where.= " $col_date >= '". $sdate ."'"; 
    545     } 
    546          
     605        $where.= " order_date >= '". $sdate ."'"; 
     606    } 
     607     
    547608    if($edate != "") { 
    548609        if ($where != "") { 
    549610            $where.= " AND "; 
    550611        } 
    551         $edate = date("Y/m/d",strtotime("1 day" ,strtotime($edate)));    
    552         $where.= " $col_date < date('" . $edate ."')"; 
    553     } 
    554      
    555     // ²ñ°÷¡¢Èó²ñ°÷¤ÎȽÄê 
    556     switch($type) { 
    557     // Á´ÂΠ
    558     case 'all': 
    559         break; 
    560     case 'member': 
    561         if ($where != "") { 
    562             $where.= " AND "; 
    563         } 
    564         $where.= " $col_member <> 0"; 
    565         break; 
    566     case 'nonmember': 
    567         if ($where != "") { 
    568             $where.= " AND "; 
    569         } 
    570         $where.= " $col_member = 0"; 
    571         break; 
    572     default: 
    573         break; 
    574     } 
    575      
    576     return array($where, $arrval); 
    577 } 
    578  
    579 /** ²ñ°÷Ê̽¸·× **/ 
    580 function lfGetOrderMember($type, $sdate, $edate, $objPage, $graph = true) { 
    581     global $arrSex; 
    582          
    583     list($where, $arrval) = lfGetWhereMember('create_date', $sdate, $edate, $type); 
    584      
    585     // ²ñ°÷½¸·×¤Î¼èÆÀ 
    586     $col = "COUNT(*) AS order_count, SUM(total) AS total, (AVG(total)) AS total_average, order_sex"; 
    587     $from = "dtb_order"; 
    588     $objQuery = new SC_Query(); 
    589     $objQuery->setGroupBy("order_sex"); 
    590      
    591     $tmp_where = $where . " AND customer_id <> 0 AND del_flg = 0 "; 
    592     $arrRet = $objQuery->select($col, $from, $tmp_where, $arrval); 
    593      
    594     // ²ñ°÷¹ØÆþ¤Ç¤¢¤ë¤³¤È¤òµ­Ï¿¤¹¤ë¡£ 
    595     $max = count($arrRet); 
     612        $edate_next = date("Y/m/d",strtotime("1 day" ,strtotime($edate))); 
     613        $where.= " order_date < date('" . $edate_next ."')"; 
     614    } 
     615     
     616    // ¸¡º÷·ë²Ì¤Î¼èÆÀ 
     617    $objPage->arrResults = $objQuery->select($col, $from, $where, $arrval); 
     618     
     619    // ÀÞ¤ìÀþ¥°¥é¥Õ¤ÎÀ¸À®    
     620    if($graph) { 
     621        $image_key = "term_" . $type; 
     622        $objPage->tpl_image = lfGetGraphLine($objPage->arrResults, $objPage->keyname, $image_key, $xtitle, $ytitle, $sdate, $edate); 
     623    } 
     624     
     625    // ¸¡º÷·ë²Ì¤¬0¤Ç¤Ê¤¤¾ì¹ç 
     626    if(count($objPage->arrResults) > 0) { 
     627        // ºÇ½ª½¸·×¹Ô¼èÆÀ¤¹¤ë 
     628        $col = $tmp_col; 
     629        $objQuery = new SC_Query(); 
     630        $arrRet = $objQuery->select($col, $from, $where, $arrval); 
     631        $arrRet[0][$objPage->keyname] = "¹ç·×"; 
     632        $objPage->arrResults[] = $arrRet[0]; 
     633    } 
     634     
     635    // Ê¿¶ÑÃͤη׻» 
     636    $max = count($objPage->arrResults); 
    596637    for($i = 0; $i < $max; $i++) { 
    597         $arrRet[$i]['member_name'] = '²ñ°÷'.$arrSex[$arrRet[$i]['order_sex']]; 
    598     } 
    599     $objPage->arrResults = $arrRet; 
    600      
    601     // Èó²ñ°÷½¸·×¤Î¼èÆÀ 
    602     $tmp_where = $where . " AND customer_id = 0 AND del_flg = 0 "; 
    603     $arrRet = $objQuery->select($col, $from, $tmp_where, $arrval); 
    604     // Èó²ñ°÷¹ØÆþ¤Ç¤¢¤ë¤³¤È¤òµ­Ï¿¤¹¤ë¡£ 
    605     $max = count($arrRet); 
    606     for($i = 0; $i < $max; $i++) { 
    607         $arrRet[$i]['member_name'] = 'Èó²ñ°÷'.$arrSex[$arrRet[$i]['order_sex']]; 
    608     } 
    609      
    610     $objPage->arrResults = array_merge($objPage->arrResults, $arrRet); 
    611      
    612     // ±ß¥°¥é¥Õ¤ÎÀ¸À® 
    613     if($graph) {     
    614         $image_key = "member"; 
    615         $objPage->tpl_image = lfGetGraphPie($objPage->arrResults, "member_name", $image_key, "(Çä¾åÈæΨ)", $sdate, $edate); 
     638        if($objPage->arrResults[$i]['total_order'] > 0) { 
     639            $objPage->arrResults[$i]['total_average'] = intval($objPage->arrResults[$i]['total'] / $objPage->arrResults[$i]['total_order']); 
     640        } 
    616641    } 
    617642     
    … …  
    623648    list($where, $arrval) = lfGetWhereMember('create_date', $sdate, $edate, $type); 
    624649     
    625     $where .= " and del_flg=0 and status <> " . ORDER_CANCEL; 
     650    $where .= " AND del_flg=0 AND status <> ". ORDER_CANCEL; 
    626651     
    627652    $sql = "SELECT T1.product_id, T1.product_code, T1.product_name as name, T1.products_count, T1.order_count, T1.price, T1.total "; 
    … …  
    637662     
    638663    if($mode != "csv") { 
    639         $sql.= "LIMIT " . PRODUCTS_TOTAL_MAX; 
     664        $sql.= "LIMIT ". PRODUCTS_TOTAL_MAX; 
    640665    } 
    641666     
    … …  
    645670    // ±ß¥°¥é¥Õ¤ÎÀ¸À® 
    646671    if($graph) { 
    647         $image_key = "products_" . $type; 
     672        $image_key = "products_". $type; 
    648673        $objPage->tpl_image = lfGetGraphPie($objPage->arrResults, "name", $image_key, "(Çä¾åÈæΨ)", $sdate, $edate); 
     674    } 
     675     
     676    return $objPage; 
     677} 
     678 
     679/** ǯÂåÊ̽¸·× **/ 
     680function lfGetOrderAge($type, $sdate, $edate, $objPage, $graph = true) { 
     681    list($where, $arrval) = lfGetWhereMember('order_date', $sdate, $edate, $type, "member"); 
     682     
     683    $sql = "SELECT SUM(order_count) AS order_count, SUM(total) AS total, start_age, end_age "; 
     684    $sql.= "FROM dtb_bat_order_daily_age WHERE $where "; 
     685    $sql.= "GROUP BY start_age, end_age ORDER BY start_age, end_age"; 
     686 
     687    $objQuery = new SC_Query(); 
     688    $objPage->arrResults = $objQuery->getall($sql, $arrval); 
     689     
     690    $max = count($objPage->arrResults); 
     691    for($i = 0; $i < $max; $i++) { 
     692        if($objPage->arrResults[$i]['order_count'] > 0) { 
     693            $objPage->arrResults[$i]['total_average'] = intval($objPage->arrResults[$i]['total'] / $objPage->arrResults[$i]['order_count']); 
     694        }    
     695        $start_age = $objPage->arrResults[$i]['start_age']; 
     696        $end_age = $objPage->arrResults[$i]['end_age']; 
     697        if($start_age != "" || $end_age != "") { 
     698            if($end_age != 999) { 
     699                $objPage->arrResults[$i]['age_name'] = $start_age. "¡Á" . $end_age. "ºÐ"; 
     700            } else { 
     701                $objPage->arrResults[$i]['age_name'] = $start_age. "ºÐ¡Á"; 
     702            } 
     703        } else { 
     704            $objPage->arrResults[$i]['age_name'] = "̤²óÅú"; 
     705        } 
     706    } 
     707     
     708    // ËÀ¥°¥é¥Õ¤ÎÀ¸À® 
     709    if($graph) { 
     710        $image_key = "age_" . $type; 
     711        $xtitle = "(ǯÎð)"; 
     712        $ytitle = "(Çä¾å¹ç·×)"; 
     713        $objPage->tpl_image = lfGetGraphBar($objPage->arrResults, "age_name", $image_key, $xtitle, $ytitle, $sdate, $edate); 
    649714    } 
    650715     
    … …  
    659724     
    660725    $sql = "SELECT job, count(*) AS order_count, SUM(total) AS total, (AVG(total)) AS total_average "; 
    661     $sql.= "FROM dtb_customer AS T1 LEFT JOIN dtb_order AS T2 USING ( customer_id ) WHERE $where AND T2.del_flg = 0 and T2.status <> " . ORDER_CANCEL; 
     726    $sql.= "FROM dtb_customer AS T1 LEFT JOIN dtb_order AS T2 USING ( customer_id ) WHERE $where AND T2.del_flg = 0 AND T2.status <> ". ORDER_CANCEL; 
    662727    $sql.= " GROUP BY job ORDER BY total DESC"; 
    663728     
    … …  
    684749} 
    685750 
    686 /** ǯÂåÊ̽¸·× **/ 
    687 function lfGetOrderAge($type, $sdate, $edate, $objPage, $graph = true) { 
    688  
    689     list($where, $arrval) = lfGetWhereMember('order_date', $sdate, $edate, $type, "member"); 
    690      
    691     $sql = "SELECT SUM(order_count) AS order_count, SUM(total) AS total, start_age, end_age "; 
    692     $sql.= "FROM dtb_bat_order_daily_age WHERE $where "; 
    693     $sql.= "GROUP BY start_age, end_age ORDER BY start_age, end_age"; 
    694  
     751/** ²ñ°÷Ê̽¸·× **/ 
     752function lfGetOrderMember($type, $sdate, $edate, $objPage, $graph = true) { 
     753    global $arrSex; 
     754         
     755    list($where, $arrval) = lfGetWhereMember('create_date', $sdate, $edate, $type); 
     756     
     757    // ²ñ°÷½¸·×¤Î¼èÆÀ 
     758    $col = "COUNT(*) AS order_count, SUM(total) AS total, (AVG(total)) AS total_average, order_sex"; 
     759    $from = "dtb_order"; 
    695760    $objQuery = new SC_Query(); 
    696     $objPage->arrResults = $objQuery->getall($sql, $arrval); 
    697      
    698     $max = count($objPage->arrResults); 
     761    $objQuery->setGroupBy("order_sex"); 
     762     
     763    $tmp_where = $where . " AND customer_id <> 0 AND del_flg = 0 AND status <> ". ORDER_CANCEL; 
     764    $arrRet = $objQuery->select($col, $from, $tmp_where, $arrval); 
     765     
     766    // ²ñ°÷¹ØÆþ¤Ç¤¢¤ë¤³¤È¤òµ­Ï¿¤¹¤ë¡£ 
     767    $max = count($arrRet); 
    699768    for($i = 0; $i < $max; $i++) { 
    700         if($objPage->arrResults[$i]['order_count'] > 0) { 
    701             $objPage->arrResults[$i]['total_average'] = intval($objPage->arrResults[$i]['total'] / $objPage->arrResults[$i]['order_count']); 
    702         }    
    703         $start_age = $objPage->arrResults[$i]['start_age']; 
    704         $end_age = $objPage->arrResults[$i]['end_age']; 
    705         if($start_age != "" || $end_age != "") { 
    706             if($end_age != 999) { 
    707                 $objPage->arrResults[$i]['age_name'] = $start_age . "¡Á" . $end_age . "ºÐ"; 
    708             } else { 
    709                 $objPage->arrResults[$i]['age_name'] = $start_age . "ºÐ¡Á"; 
    710             } 
    711         } else { 
    712             $objPage->arrResults[$i]['age_name'] = "̤²óÅú"; 
    713         } 
    714     } 
    715      
    716     // ËÀ¥°¥é¥Õ¤ÎÀ¸À® 
    717     if($graph) { 
    718         $image_key = "age_" . $type; 
    719         $xtitle = "(ǯÎð)"; 
    720         $ytitle = "(Çä¾å¹ç·×)"; 
    721         $objPage->tpl_image = lfGetGraphBar($objPage->arrResults, "age_name", $image_key, $xtitle, $ytitle, $sdate, $edate); 
     769        $arrRet[$i]['member_name'] = '²ñ°÷'.$arrSex[$arrRet[$i]['order_sex']]; 
     770    } 
     771    $objPage->arrResults = $arrRet; 
     772     
     773    // Èó²ñ°÷½¸·×¤Î¼èÆÀ 
     774    $tmp_where = $where . " AND customer_id = 0 AND del_flg = 0 AND status <> ". ORDER_CANCEL; 
     775    $arrRet = $objQuery->select($col, $from, $tmp_where, $arrval); 
     776    // Èó²ñ°÷¹ØÆþ¤Ç¤¢¤ë¤³¤È¤òµ­Ï¿¤¹¤ë¡£ 
     777    $max = count($arrRet); 
     778    for($i = 0; $i < $max; $i++) { 
     779        $arrRet[$i]['member_name'] = 'Èó²ñ°÷'.$arrSex[$arrRet[$i]['order_sex']]; 
     780    } 
     781     
     782    $objPage->arrResults = array_merge($objPage->arrResults, $arrRet); 
     783     
     784    // ±ß¥°¥é¥Õ¤ÎÀ¸À® 
     785    if($graph) {     
     786        $image_key = "member"; 
     787        $objPage->tpl_image = lfGetGraphPie($objPage->arrResults, "member_name", $image_key, "(Çä¾åÈæΨ)", $sdate, $edate); 
    722788    } 
    723789     
    … …  
    725791} 
    726792 
    727 /** ´ü´ÖÊ̽¸·× **/ 
    728 function lfGetOrderTerm($type, $sdate, $edate, $objPage, $graph = true) { 
    729          
    730         $tmp_col = "sum(total_order) as total_order, sum(men) as men, sum(women) as women,"; 
    731         $tmp_col.= "sum(men_member) as men_member, sum(men_nonmember) as men_nonmember,"; 
    732         $tmp_col.= "sum(women_member) as women_member, sum(women_nonmember) as women_nonmember,"; 
    733         $tmp_col.= "sum(total) as total, (avg(total_average)) as total_average"; 
    734         $objQuery = new SC_Query(); 
    735          
    736         switch($type) { 
    737         // ·îÊÌ 
    738         case 'month': 
    739             $col = $tmp_col . ",key_month"; 
    740             $objQuery->setgroupby("key_month"); 
    741             $objQuery->setOrder("key_month"); 
    742             $objPage->keyname = "key_month"; 
    743             $objPage->tpl_tail = "·î"; 
    744             $from = "dtb_bat_order_daily"; 
    745             $xtitle = "(·îÊÌ)"; 
    746             $ytitle = "(Çä¾å¹ç·×)"; 
    747             break; 
    748         // ǯÊÌ 
    749         case 'year': 
    750             $col = $tmp_col . ",key_year"; 
    751             $objQuery->setgroupby("key_year"); 
    752             $objQuery->setOrder("key_year"); 
    753             $objPage->keyname = "key_year"; 
    754             $objPage->tpl_tail = "ǯ"; 
    755             $from = "dtb_bat_order_daily"; 
    756             $xtitle = "(ǯÊÌ)"; 
    757             $ytitle = "(Çä¾å¹ç·×)"; 
    758             break; 
    759         // ÍËÆüÊÌ 
    760         case 'wday': 
    761             $col = $tmp_col . ",key_wday, wday"; 
    762             $objQuery->setgroupby("key_wday, wday"); 
    763             $objQuery->setOrder("wday"); 
    764             $objPage->keyname = "key_wday"; 
    765             $objPage->tpl_tail = "ÍËÆü"; 
    766             $from = "dtb_bat_order_daily"; 
    767             $xtitle = "(ÍËÆüÊÌ)"; 
    768             $ytitle = "(Çä¾å¹ç·×)"; 
    769             break; 
    770         // »þ´ÖÊÌ 
    771         case 'hour': 
    772             $col = $tmp_col . ",hour"; 
    773             $objQuery->setgroupby("hour"); 
    774             $objQuery->setOrder("hour"); 
    775             $objPage->keyname = "hour"; 
    776             $objPage->tpl_tail = "»þ"; 
    777             $from = "dtb_bat_order_daily_hour"; 
    778             $xtitle = "(»þ´ÖÊÌ)"; 
    779             $ytitle = "(Çä¾å¹ç·×)"; 
    780             break; 
    781         default: 
    782             $col = "*"; 
    783             $objQuery->setOrder("key_day"); 
    784             $objPage->keyname = "key_day"; 
    785             $from = "dtb_bat_order_daily"; 
    786             $xtitle = "(ÆüÊÌ)"; 
    787             $ytitle = "(Çä¾å¹ç·×)"; 
    788             break; 
    789         } 
    790          
    791  
     793// ²ñ°÷¡¢Èó²ñ°÷½¸·×¤ÎWHEREʬ¤ÎºîÀ® 
     794function lfGetWhereMember($col_date, $sdate, $edate, $type, $col_member = "customer_id") { 
    792795    // ¼èÆÀÆüÉդλØÄê 
    793         if($sdate != "") { 
    794             if ($where != "") { 
    795                 $where.= " AND "; 
    796             }            
    797             $where.= " order_date >= '". $sdate ."'"; 
    798         } 
    799          
    800         if($edate != "") { 
    801             if ($where != "") { 
    802                 $where.= " AND "; 
    803             } 
    804             $edate_next = date("Y/m/d",strtotime("1 day" ,strtotime($edate))); 
    805             $where.= " order_date < date('" . $edate_next ."')"; 
    806         } 
    807          
    808         // ¸¡º÷·ë²Ì¤Î¼èÆÀ 
    809         $objPage->arrResults = $objQuery->select($col, $from, $where, $arrval); 
    810          
    811         // ÀÞ¤ìÀþ¥°¥é¥Õ¤ÎÀ¸À®    
    812         if($graph) { 
    813             $image_key = "term_" . $type; 
    814             $objPage->tpl_image = lfGetGraphLine($objPage->arrResults, $objPage->keyname, $image_key, $xtitle, $ytitle, $sdate, $edate); 
    815         } 
    816          
    817         // ¸¡º÷·ë²Ì¤¬0¤Ç¤Ê¤¤¾ì¹ç 
    818         if(count($objPage->arrResults) > 0) { 
    819             // ºÇ½ª½¸·×¹Ô¼èÆÀ¤¹¤ë 
    820             $col = $tmp_col; 
    821             $objQuery = new SC_Query(); 
    822             $arrRet = $objQuery->select($col, $from, $where, $arrval); 
    823             $arrRet[0][$objPage->keyname] = "¹ç·×"; 
    824             $objPage->arrResults[] = $arrRet[0]; 
    825         } 
    826  
    827         // Ê¿¶ÑÃͤη׻» 
    828         $max = count($objPage->arrResults); 
    829         for($i = 0; $i < $max; $i++) { 
    830             if($objPage->arrResults[$i]['total_order'] > 0) { 
    831                 $objPage->arrResults[$i]['total_average'] = intval($objPage->arrResults[$i]['total'] / $objPage->arrResults[$i]['total_order']); 
    832             } 
    833         } 
    834          
    835         return $objPage; 
     796    if($sdate != "") { 
     797        if ($where != "") { 
     798            $where.= " AND "; 
     799        }            
     800        $where.= " $col_date >= '". $sdate ."'"; 
     801    } 
     802         
     803    if($edate != "") { 
     804        if ($where != "") { 
     805            $where.= " AND "; 
     806        } 
     807        $edate = date("Y/m/d",strtotime("1 day" ,strtotime($edate)));    
     808        $where.= " $col_date < date('" . $edate ."')"; 
     809    } 
     810     
     811    // ²ñ°÷¡¢Èó²ñ°÷¤ÎȽÄê 
     812    switch($type) { 
     813    // Á´ÂΠ
     814    case 'all': 
     815        break; 
     816    case 'member': 
     817        if ($where != "") { 
     818            $where.= " AND "; 
     819        } 
     820        $where.= " $col_member <> 0"; 
     821        break; 
     822    case 'nonmember': 
     823        if ($where != "") { 
     824            $where.= " AND "; 
     825        } 
     826        $where.= " $col_member = 0"; 
     827        break; 
     828    default: 
     829        break; 
     830    } 
     831     
     832    return array($where, $arrval); 
    836833} 
    837834 
Note: See TracChangeset for help on using the changeset viewer.