source: branches/dev/html/admin/batch/daily.php @ 13274

Revision 13274, 10.8 KB checked in by naka, 15 years ago (diff)
Line 
1<?php
2/*
3 * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
4 *
5 * http://www.lockon.co.jp/
6 */
7$daily_php_dir = realpath(dirname( __FILE__));
8require_once($daily_php_dir . "/../require.php");
9
10$term = 0;
11$start = 1; // ½¸·×´ü´Ö¤Ï¡¢$start¢·$term¤Î´Ö¤È¤Ê¤ë¡£Ä̾ïÁ°Æüʬ¤«¤é¡£
12$command = false;
13
14// ½¸·×Âоݴü´Ö¤Î¼èÆÀ¡Ê»ØÄêÆüʬ¤µ¤«¤Î¤Ü¤ë)
15if (sfIsInt($argv[1]) && $argv[1] <= 365) {
16    $term = $argv[1];
17    $command = true;
18}
19
20// ½¸·×³«»ÏÆü
21if (sfIsInt($argv[2]) && $argv[2] <= 365) {
22    $start = $argv[2];
23    $command = true;
24}
25
26if($term > 0) {
27    // ½¸·×¤Î³«»Ï
28    lfStartDailyTotal($term, $start, $command);
29}
30
31// ½¸·×¤Î³«»Ï
32function lfStartDailyTotal($term, $start, $command = false) {
33       
34    $now_time = time();
35       
36    // ¥°¥é¥Õ²èÁü¤Îºï½ü
37    $path = GRAPH_DIR . "*.png";
38    system ("rm -rf $path");
39   
40    // ºï½ü¤µ¤ì¤¿¼õÃí¥Ç¡¼¥¿¤Î¼õÃí¾ÜºÙ¾ðÊó¤Îºï½ü
41    $objQuery = new SC_Query();
42    $where = "order_id IN (SELECT order_id FROM dtb_order WHERE del_flg = 1)";
43    $objQuery->delete("dtb_order_detail", $where);
44   
45    // ºÇ¸å¤Ë¹¹¿·¤µ¤ì¤¿ÆüÉÕ¤ò¼èÆÀ
46    $ret = $objQuery->max("dtb_bat_order_daily", "create_date");
47    list($batch_last) = split("\.", $ret);
48    $pass = $now_time - strtotime($batch_last);
49       
50    // ºÇ¸å¤Î¥Ð¥Ã¥Á¼Â¹Ô¤«¤éLOAD_BATCH_PASSÉ÷вᤷ¤Æ¤¤¤Ê¤¤¤È¼Â¹Ô¤·¤Ê¤¤¡£
51    if($pass < LOAD_BATCH_PASS) {
52        gfPrintLog("LAST BATCH " . $arrRet[0]['create_date'] . " > " . $batch_pass . " -> EXIT BATCH $batch_date");
53        return;
54    }
55
56    // ½¸·×
57    for ($i = $start; $i < $term; $i++) {
58        // ´ðËÜ»þ´Ö¤«¤é$iÆüʬ¤µ¤«¤Î¤Ü¤ë
59        $tmp_time = $now_time - ($i * 24 * 3600);
60               
61        $batch_date = date("Y/m/d", $tmp_time);
62        gfPrintLog("LOADING BATCH $batch_date");
63                   
64        lfBatOrderDaily($tmp_time);
65        lfBatOrderDailyHour($tmp_time);
66        lfBatOrderAge($tmp_time);
67       
68        // ¥Ö¥é¥¦¥¶¤«¤é¤Î¼Â¹Ô¤Î¾ì¹ç
69        if(!$command) {
70            // ¥¿¥¤¥à¥¢¥¦¥È¤òËɤ°
71            sfFlush();
72        } else {
73            print("LOADING BATCH $batch_date\n");
74        }
75    }
76}
77
78// ¥ê¥¢¥ë¥¿¥¤¥à¤Ç½¸·×¤ò¼Â»Ü¤¹¤ë¡£½¸·×¤¬½ªÎ»¤·¤Æ¤¤¤ë¥ì¥³¡¼¥É¤Ï¼Â»Ü¤·¤Ê¤¤¡£
79/*
80    $sdate:YYYY-MM-DD hh:mm:ss·Á¼°¤ÎÆüÉÕ
81    $edate:YYYY-MM-DD hh:mm:ss·Á¼°¤ÎÆüÉÕ
82*/
83function lfRealTimeDailyTotal($sdate, $edate) {
84    $pass = strtotime($edate) - strtotime($sdate);
85    $loop = intval($pass / 86400);
86   
87    for($i = 0; $i <= $loop; $i++) {
88        $tmp_time = strtotime($sdate) + ($i * 86400);
89        $batch_date = date("Y/m/d H:i:s", $tmp_time);
90        $objQuery = new SC_Query();
91        $arrRet = $objQuery->select("order_date, create_date", "dtb_bat_order_daily", "order_date = ?", array($batch_date));
92        // ¤¹¤Ç¤Ë¥Ð¥Ã¥Á½èÍý¤¬½ªÎ»¤·¤Æ¤¤¤ë¤«¥Á¥§¥Ã¥¯¤¹¤ë¡£
93        if(count($arrRet) > 0) {
94            list($create_date) = split("\.", $arrRet[0]['create_date']);
95            list($order_date) = split("\.", $arrRet[0]['order_date']);
96            $create_time = strtotime($create_date);
97            $order_time = strtotime($order_date);
98            // ½¸·×³«»ÏÆü¤è¤ê1Æü°Ê¾å¸å¤Ë½¸·×¤µ¤ì¤Æ¤¤¤ë¾ì¹ç¤Ï½¸·×¤·¤Ê¤ª¤µ¤Ê¤¤
99            if($order_time + 86400 < $create_time || $tmp_time > time()) {
100                gfPrintLog("EXIT BATCH $batch_date $tmp_time" . " " . time());
101                continue;
102            }
103        }
104        gfPrintLog("LOADING BATCH $batch_date");
105        lfBatOrderDaily($tmp_time);
106        lfBatOrderDailyHour($tmp_time);
107        lfBatOrderAge($tmp_time);
108    }
109}
110
111// ¥Ð¥Ã¥Á½¸·×ÍѤÎSQLʸ¤ò¼èÆÀ¤¹¤ë¡£
112function lfGetOrderDailySQL($start, $end) {
113    $from = " FROM dtb_order AS T1 LEFT JOIN dtb_customer AS T2 USING ( customer_id ) ";
114    $where = " WHERE T1.del_flg = 0 AND T1.status <> " . ORDER_CANCEL . " AND T1.create_date BETWEEN '$start' AND '$end' ";
115
116/*  mysql¤Ç¤âÌäÂê¤Ê¤¤¤è¤¦¤Ë½¤Àµ
117    $sql = "SELECT ";
118    $sql.= "COUNT(*) AS total_order, ";
119    $sql.= "SUM((SELECT COUNT(*) WHERE customer_id = 0)) AS nonmember, ";
120    $sql.= "SUM((SELECT COUNT(*) WHERE customer_id <> 0 GROUP BY customer_id)) AS member, ";
121    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1)) AS men, ";
122    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2)) AS women, ";
123    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1 AND customer_id <> 0)) AS men_member, ";
124    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1 AND customer_id = 0)) AS men_nonmember, ";
125    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2 AND customer_id <> 0)) AS women_member, ";
126    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2 AND customer_id = 0)) AS women_nonmember, ";
127    $sql.= "SUM(total) AS total, ";
128    $sql.= "int8(AVG(total)) AS total_average ";
129    $sql.= $from;
130    $sql.= $where;      // ¼õÃíºîÀ®Æü¤Ç¸¡º÷¤¹¤ë
131*/
132    $sql = "SELECT ";
133    $sql.= "COUNT(*) AS total_order, ";
134    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.customer_id = 0) AS nonmember ) AS nonmember, ";
135    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.customer_id <> 0) AS member ) AS member, ";
136    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 1) AS men ) AS men, ";
137    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 2) AS women ) AS women, ";
138    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 1 AND T2.customer_id <> 0) AS men_member ) AS men_member, ";
139    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 1 AND T1.customer_id = 0) AS men_nonmember ) AS men_nonmember, ";
140    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 2 AND T2.customer_id <> 0) AS women_member ) AS women_member, ";
141    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 2 AND T1.customer_id = 0) AS women_nonmember ) AS women_nonmember, ";
142    $sql.= "SUM(total) AS total, ";
143    $sql.= "(AVG(total)) AS total_average ";
144    $sql.= $from;
145    $sql.= $where;      // ¼õÃíºîÀ®Æü¤Ç¸¡º÷¤¹¤ë
146
147    return $sql;
148}
149
150// Çä¾å¤²½¸·×¥Ð¥Ã¥Á½èÍý(ÆüÊÌ)
151function lfBatOrderDaily($time) {
152    global $arrWDAY;
153   
154    // ½¸·×ÂоÝÆü¤ò¼èÆÀ¤¹¤ë
155    $date = date("Y-m-d", $time);
156   
157    $start = $date . " 00:00:00";
158    $end = $date . " 23:59:59";
159   
160    $sql = lfGetOrderDailySQL($start,$end);
161   
162    $objQuery = new SC_Query();
163    $arrRet = $objQuery->getall($sql); 
164   
165    $sqlval = $arrRet[0];
166   
167    // ¶õʸ»ú¤ò"0"¤ËÊÑ´¹
168    foreach($sqlval as $key => $val) {
169        if ($val == "") {
170            $sqlval[$key] = "0";
171        }
172    }
173   
174    $sqlval['create_date'] = 'now()';
175    $sqlval['order_date'] = $start;
176    $sqlval['year'] = date("Y", $time);
177    $sqlval['month'] = date("m", $time);
178    $sqlval['day'] = date("d", $time);
179    $sqlval['wday'] = date("w", $time);
180    $sqlval['key_day'] = sprintf("%02d/%02d/%02d", substr($sqlval['year'],2), $sqlval['month'], $sqlval['day']);
181    $sqlval['key_month'] = sprintf("%02d/%02d", substr($sqlval['year'],2), $sqlval['month']);
182    $sqlval['key_year'] = sprintf("%d", $sqlval['year']);
183    $sqlval['key_wday'] = sprintf("%s", $arrWDAY[$sqlval['wday']]);
184   
185    $objQuery->delete("dtb_bat_order_daily", "order_date = ?", array($start));
186    $objQuery->insert("dtb_bat_order_daily", $sqlval);
187}
188
189// Çä¾å¤²½¸·×¥Ð¥Ã¥Á½èÍý(»þ´ÖÊÌ)
190function lfBatOrderDailyHour($time) {
191   
192    // ½¸·×ÂоÝÆü¤ò¼èÆÀ¤¹¤ë
193    $date = date("Y-m-d", $time);
194    $objQuery = new SC_Query();
195   
196    $start = $date . " 00:00:00";
197    $objQuery->delete("dtb_bat_order_daily_hour", "order_date = ?", array($start));
198   
199    // 1»þ´ÖËè¤Ë½¸·×¤¹¤ë¡£
200    for($i = 0; $i < 24; $i++) {
201        $sdate = sprintf("%s %02d:00:00", $date, $i);
202        $edate = sprintf("%s %02d:59:59", $date, $i);
203        $sql = lfGetOrderDailySQL($sdate, $edate);
204        $arrRet = $objQuery->getall($sql);
205        $sqlval = $arrRet[0];
206        // ¶õʸ»ú¤ò"0"¤ËÊÑ´¹
207        foreach($sqlval as $key => $val) {
208            if ($val == "") {
209                $sqlval[$key] = "0";
210            }
211        }
212        $sqlval['create_date'] = "now()";
213        $sqlval['order_date'] = $start;
214        $sqlval['hour'] = "$i";
215        $objQuery->insert("dtb_bat_order_daily_hour", $sqlval);
216    }   
217}
218
219// Çä¾å¤²½¸·×¥Ð¥Ã¥Á½èÍý(ǯÎðÊÌ)
220function lfBatOrderAge($time) {
221   
222    $age_loop = intval(BAT_ORDER_AGE / 10);
223   
224    // ǯÎð¤ÎÈϰϤò»ØÄꤷ¤Æ¥Ç¡¼¥¿Ãê½Ð
225    $sql.= "SELECT COUNT(*) AS order_count, SUM(total) AS total, (AVG(total)) AS total_average ";
226    $sql.= "FROM dtb_order ";
227   
228    // ½¸·×ÂоÝÆü¤ò¼èÆÀ¤¹¤ë
229    $date = date("Y-m-d", $time);
230   
231    $start = $date . " 00:00:00";
232    $end = $date . " 23:59:59";
233   
234    $objQuery = new SC_Query();
235    $objQuery->delete("dtb_bat_order_daily_age", "order_date = ?", array($start));
236
237    /* ²ñ°÷½¸·× */
238
239    $base_where = "WHERE (create_date BETWEEN ? AND ?) AND customer_id <> 0 AND del_flg = 0 AND status <> " . ORDER_CANCEL;
240
241    $end_date = date("Y/m/d", time());
242    $start_date = date("Y/m/d",strtotime("-10 year" ,strtotime($end_date)));
243    $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($end_date)));
244    // ǯÎðËè¤Ë½¸·×¤¹¤ë¡£
245    for($i = 0; $i <= $age_loop; $i++) {
246        $where = $base_where . " AND order_birth >= cast('$start_date' as date)";
247        $start_age = $i * 10;
248        if($i < $age_loop) {
249            $end_age = $start_age+9;
250            $where = $where . " AND order_birth < cast('$end_date' as date)";
251        }else{
252            $where = $base_where . " AND order_birth < cast('$end_date' as date)";
253            $end_age = 999;
254        }
255        lfBatOrderAgeSub($sql . $where, $start, $end, $start_age, $end_age, 1);
256        $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($start_date)));
257        $start_date = date("Y/m/d",strtotime("-10 year",strtotime($start_date)));
258    }
259   
260    // ÃÂÀ¸ÆüÆþÎϤʤ·
261    $where = $base_where . " AND order_birth IS NULL ";
262    lfBatOrderAgeSub($sql . $where, $start, $end, NULL, NULL, 1);
263
264    /* Èó²ñ°÷½¸·× */
265   
266    $base_where = "WHERE (create_date BETWEEN ? AND ?) AND customer_id = 0 AND del_flg = 0 AND status <> " . ORDER_CANCEL;
267    $where = $base_where . " AND (to_number(to_char(age(current_timestamp, order_birth), 'YYY'), 999) BETWEEN ? AND ?) ";
268
269    $end_date = date("Y/m/d", time());
270    $start_date = date("Y/m/d",strtotime("-10 year" ,strtotime($end_date)));
271    $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($end_date)));
272    // ǯÎðËè¤Ë½¸·×¤¹¤ë¡£
273    for($i = 0; $i <= $age_loop; $i++) {
274        $where = $base_where . " AND order_birth >= cast('$start_date' as date)";
275        $start_age = $i * 10;
276        if($i < $age_loop) {
277            $end_age = $start_age+9;
278            $where = $where . " AND order_birth < cast('$end_date' as date)";
279        }else{
280            $where = $base_where . " AND order_birth < cast('$end_date' as date)";
281            $end_age = 999;
282        }
283        lfBatOrderAgeSub($sql . $where, $start, $end, $start_age, $end_age, 0);
284        $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($start_date)));
285        $start_date = date("Y/m/d",strtotime("-10 year",strtotime($start_date)));
286    }
287   
288    // ÃÂÀ¸ÆüÆþÎϤʤ·
289    $where = $base_where . " AND order_birth IS NULL AND del_flg = 0";
290    lfBatOrderAgeSub($sql . $where, $start, $end, NULL, NULL, 0);   
291}
292
293// Çä¾å¤²½¸·×¥Ð¥Ã¥Á½èÍý(ǯÎðÊÌ) ÅÐÏ¿Éôʬ
294function lfBatOrderAgeSub($sql, $start, $end, $start_age, $end_age, $member) {
295    $objQuery = new SC_Query();
296   
297    $arrRet = $objQuery->getall($sql, array($start, $end));
298    $sqlval = $arrRet[0];
299   
300    // ¶õʸ»ú¤ò"0"¤ËÊÑ´¹
301    foreach($sqlval as $key => $val) {
302        if ($val == "") {
303            $sqlval[$key] = "0";
304        }
305    }
306
307    $sqlval['create_date'] = "now()";
308    $sqlval['order_date'] = $start;
309    $sqlval['start_age'] = "$start_age";
310    $sqlval['end_age'] = "$end_age";
311    $sqlval['member'] = "$member";
312
313    $objQuery->insert("dtb_bat_order_daily_age", $sqlval);
314}
315
316// ʸ»úÎó¤ËSingleQuotation¤òÉÕÍ¿¤¹¤ë
317function lfSingleQuot($value){
318    $ret = "";
319    if (DB_TYPE == "mysql") {
320        $ret = $value;
321    }else{
322        $ret = "'" . $value . "'";
323    }
324   
325    return $ret;
326}
327
328?>
Note: See TracBrowser for help on using the repository browser.