source: temp/trunk/html/admin/batch/daily.php @ 4952

Revision 4952, 10.6 KB checked in by kakinaka, 20 years ago (diff)

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