source: branches/feature-module-update/html/admin/batch/daily.php @ 15532

Revision 15532, 11.1 KB checked in by nanasess, 17 years ago (diff)

svn:mime-type 修正

  • Property svn:keywords set to Id
  • Property svn:mime-type set to text/x-httpd-php; charset=UTF-8
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        sfFlush();
70    }
71}
72
73// リアルタイムで集計を実施する。集計が終了しているレコードは実施しない。
74/*
75    $sdate:YYYY-MM-DD hh:mm:ss形式の日付
76    $edate:YYYY-MM-DD hh:mm:ss形式の日付
77*/
78function lfRealTimeDailyTotal($sdate, $edate) {
79    $pass = strtotime($edate) - strtotime($sdate);
80    $loop = intval($pass / 86400);
81   
82    for($i = 0; $i <= $loop; $i++) {
83        $tmp_time = strtotime($sdate) + ($i * 86400);
84        $batch_date = date("Y/m/d H:i:s", $tmp_time);
85        $objQuery = new SC_Query();
86        $arrRet = $objQuery->select("order_date, create_date", "dtb_bat_order_daily", "order_date = ?", array($batch_date));
87        // すでにバッチ処理が終了しているかチェックする。
88        if(count($arrRet) > 0) {
89            list($create_date) = split("\.", $arrRet[0]['create_date']);
90            list($order_date) = split("\.", $arrRet[0]['order_date']);
91            $create_time = strtotime($create_date);
92            $order_time = strtotime($order_date);
93            // オーダー開始日より一日以上後に集計されている場合は集計しなおさない
94            if($order_time + 86400 < $create_time || $tmp_time > time()) {
95                gfPrintLog("EXIT BATCH $batch_date $tmp_time" . " " . time());
96                continue;
97            }
98        }
99        gfPrintLog("LOADING BATCH $batch_date");
100        lfBatOrderDaily($tmp_time);
101        lfBatOrderDailyHour($tmp_time);
102        lfBatOrderAge($tmp_time);
103    }
104}
105
106// バッチ集計用のSQL文を取得する。
107function lfGetOrderDailySQL($start, $end) {
108    $from = " FROM dtb_order AS T1 LEFT JOIN dtb_customer AS T2 USING ( customer_id ) ";
109    $where = " WHERE T1.del_flg = 0 AND T1.status <> " . ORDER_CANCEL . " AND T1.create_date BETWEEN '$start' AND '$end' ";
110
111/*  mysqlでも問題ないように修正
112    $sql = "SELECT ";
113    $sql.= "COUNT(*) AS total_order, ";
114    $sql.= "SUM((SELECT COUNT(*) WHERE customer_id = 0)) AS nonmember, ";
115    $sql.= "SUM((SELECT COUNT(*) WHERE customer_id <> 0 GROUP BY customer_id)) AS member, ";
116    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1)) AS men, ";
117    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2)) AS women, ";
118    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1 AND customer_id <> 0)) AS men_member, ";
119    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 1 AND customer_id = 0)) AS men_nonmember, ";
120    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2 AND customer_id <> 0)) AS women_member, ";
121    $sql.= "SUM((SELECT COUNT(*) WHERE order_sex = 2 AND customer_id = 0)) AS women_nonmember, ";
122    $sql.= "SUM(total) AS total, ";
123    $sql.= "int8(AVG(total)) AS total_average ";
124    $sql.= $from;
125    $sql.= $where;      // 受注作成日で検索する
126*/
127    $sql = "SELECT ";
128    $sql.= "COUNT(*) AS total_order, ";
129    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.customer_id = 0) AS nonmember ) AS nonmember, ";
130    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.customer_id <> 0) AS member ) AS member, ";
131    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 1) AS men ) AS men, ";
132    $sql.= "(SELECT sum(cnt) FROM (SELECT COUNT(*) AS cnt $from $where AND T1.order_sex = 2) AS women ) AS women, ";
133    $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, ";
134    $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, ";
135    $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, ";
136    $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, ";
137    $sql.= "SUM(total) AS total, ";
138    $sql.= "(AVG(total)) AS total_average ";
139    $sql.= $from;
140    $sql.= $where;      // 受注作成日で検索する
141
142    return $sql;
143}
144
145// 売上げ集計バッチ処理(日別)
146function lfBatOrderDaily($time) {
147    global $arrWDAY;
148   
149    // 集計対象日を取得する
150    $date = date("Y-m-d", $time);
151   
152    $start = $date . " 00:00:00";
153    $end = $date . " 23:59:59";
154   
155    $sql = lfGetOrderDailySQL($start,$end);
156   
157    $objQuery = new SC_Query();
158    $arrRet = $objQuery->getall($sql); 
159   
160    $sqlval = $arrRet[0];
161   
162    // 空文字を"0"に変換
163    foreach($sqlval as $key => $val) {
164        if ($val == "") {
165            $sqlval[$key] = "0";
166        }
167    }
168   
169    $sqlval['create_date'] = 'now()';
170    $sqlval['order_date'] = $start;
171    $sqlval['year'] = date("Y", $time);
172    $sqlval['month'] = date("m", $time);
173    $sqlval['day'] = date("d", $time);
174    $sqlval['wday'] = date("w", $time);
175    $sqlval['key_day'] = sprintf("%02d/%02d/%02d", substr($sqlval['year'],2), $sqlval['month'], $sqlval['day']);
176    $sqlval['key_month'] = sprintf("%02d/%02d", substr($sqlval['year'],2), $sqlval['month']);
177    $sqlval['key_year'] = sprintf("%d", $sqlval['year']);
178    $sqlval['key_wday'] = sprintf("%s", $arrWDAY[$sqlval['wday']]);
179   
180    $objQuery->delete("dtb_bat_order_daily", "order_date = ?", array($start));
181    $objQuery->insert("dtb_bat_order_daily", $sqlval);
182}
183
184// 売上げ集計バッチ処理(時間別)
185function lfBatOrderDailyHour($time) {
186   
187    // 集計対象日を取得する
188    $date = date("Y-m-d", $time);
189    $objQuery = new SC_Query();
190   
191    $start = $date . " 00:00:00";
192    $objQuery->delete("dtb_bat_order_daily_hour", "order_date = ?", array($start));
193   
194    // 1時間毎に集計する。
195    for($i = 0; $i < 24; $i++) {
196        $sdate = sprintf("%s %02d:00:00", $date, $i);
197        $edate = sprintf("%s %02d:59:59", $date, $i);
198        $sql = lfGetOrderDailySQL($sdate, $edate);
199        $arrRet = $objQuery->getall($sql);
200        $sqlval = $arrRet[0];
201        // 空文字を"0"に変換
202        foreach($sqlval as $key => $val) {
203            if ($val == "") {
204                $sqlval[$key] = "0";
205            }
206        }
207        $sqlval['create_date'] = "now()";
208        $sqlval['order_date'] = $start;
209        $sqlval['hour'] = "$i";
210        $objQuery->insert("dtb_bat_order_daily_hour", $sqlval);
211    }   
212}
213
214// 売上げ集計バッチ処理(年齢別)
215function lfBatOrderAge($time) {
216   
217    $age_loop = intval(BAT_ORDER_AGE / 10);
218   
219    // 年齢の範囲を指定してデータ抽出
220    $sql.= "SELECT COUNT(*) AS order_count, SUM(total) AS total, (AVG(total)) AS total_average ";
221    $sql.= "FROM dtb_order ";
222   
223    // 集計対象日を取得する
224    $date = date("Y-m-d", $time);
225   
226    $start = $date . " 00:00:00";
227    $end = $date . " 23:59:59";
228   
229    $objQuery = new SC_Query();
230    $objQuery->delete("dtb_bat_order_daily_age", "order_date = ?", array($start));
231
232    /* 会員集計 */
233
234    $base_where = "WHERE (create_date BETWEEN ? AND ?) AND customer_id <> 0 AND del_flg = 0 AND status <> " . ORDER_CANCEL;
235
236    $end_date = date("Y/m/d", time());
237    $start_date = date("Y/m/d",strtotime("-10 year" ,strtotime($end_date)));
238    $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($end_date)));
239    // 年齢毎に集計する。
240    for($i = 0; $i <= $age_loop; $i++) {
241        $where = $base_where . " AND order_birth >= cast('$start_date' as date)";
242        $start_age = $i * 10;
243        if($i < $age_loop) {
244            $end_age = $start_age+9;
245            $where = $where . " AND order_birth < cast('$end_date' as date)";
246        }else{
247            $where = $base_where . " AND order_birth < cast('$end_date' as date)";
248            $end_age = 999;
249        }
250        lfBatOrderAgeSub($sql . $where, $start, $end, $start_age, $end_age, 1);
251        $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($start_date)));
252        $start_date = date("Y/m/d",strtotime("-10 year",strtotime($start_date)));
253    }
254   
255    // 誕生日入力なし
256    $where = $base_where . " AND order_birth IS NULL ";
257    lfBatOrderAgeSub($sql . $where, $start, $end, NULL, NULL, 1);
258
259    /* 非会員集計 */
260   
261    $base_where = "WHERE (create_date BETWEEN ? AND ?) AND customer_id = 0 AND del_flg = 0 AND status <> " . ORDER_CANCEL;
262    $where = $base_where . " AND (to_number(to_char(age(current_timestamp, order_birth), 'YYY'), 999) BETWEEN ? AND ?) ";
263
264    $end_date = date("Y/m/d", time());
265    $start_date = date("Y/m/d",strtotime("-10 year" ,strtotime($end_date)));
266    $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($end_date)));
267    // 年齢毎に集計する。
268    for($i = 0; $i <= $age_loop; $i++) {
269        $where = $base_where . " AND order_birth >= cast('$start_date' as date)";
270        $start_age = $i * 10;
271        if($i < $age_loop) {
272            $end_age = $start_age+9;
273            $where = $where . " AND order_birth < cast('$end_date' as date)";
274        }else{
275            $where = $base_where . " AND order_birth < cast('$end_date' as date)";
276            $end_age = 999;
277        }
278        lfBatOrderAgeSub($sql . $where, $start, $end, $start_age, $end_age, 0);
279        $end_date = date("Y/m/d",strtotime("1 day" ,strtotime($start_date)));
280        $start_date = date("Y/m/d",strtotime("-10 year",strtotime($start_date)));
281    }
282   
283    // 誕生日入力なし
284    $where = $base_where . " AND order_birth IS NULL AND del_flg = 0";
285    lfBatOrderAgeSub($sql . $where, $start, $end, NULL, NULL, 0);   
286}
287
288// 売上げ集計バッチ処理(年齢別) 登録部分
289function lfBatOrderAgeSub($sql, $start, $end, $start_age, $end_age, $member) {
290    $objQuery = new SC_Query();
291   
292    $arrRet = $objQuery->getall($sql, array($start, $end));
293    $sqlval = $arrRet[0];
294   
295    // 空文字を"0"に変換
296    foreach($sqlval as $key => $val) {
297        if ($val == "") {
298            $sqlval[$key] = "0";
299        }
300    }
301
302    $sqlval['create_date'] = "now()";
303    $sqlval['order_date'] = $start;
304    $sqlval['start_age'] = "$start_age";
305    $sqlval['end_age'] = "$end_age";
306    $sqlval['member'] = "$member";
307
308    $objQuery->insert("dtb_bat_order_daily_age", $sqlval);
309}
310
311// 文字列にSingleQuotationを付与する
312function lfSingleQuot($value){
313    $ret = "";
314    if (DB_TYPE == "mysql") {
315        $ret = $value;
316    }else{
317        $ret = "'" . $value . "'";
318    }
319   
320    return $ret;
321}
322
323?>
Note: See TracBrowser for help on using the repository browser.