tmAywvvu

1<?php
2
3namespace app\models;
4
5use Yii;
6use yii\base\Model;
7use yii\db\Query;
8
9/**
10 * Description of Report
11 *
12 * @author dobro
13 */
14class Report extends Model {
15
16    public static function sellerWorkingtimeReport() {
17
18        $orderSearch = Yii::$app->request->get('OrderSearch');
19
20        $where = [];
21
22        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
23            $timestamp = strtotime($orderSearch['order_datetime_min']);
24            if ($timestamp !== false) {
25                $min_date = date('Y-m-d', $timestamp);
26                $where[] = " wt.workingtime_date>='$min_date' ";
27            }
28        }
29        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
30            $timestamp = strtotime($orderSearch['order_datetime_max']);
31            if ($timestamp !== false) {
32                $max_date = date('Y-m-d', $timestamp);
33                $where[] = " wt.workingtime_date<='$max_date' ";
34            }
35        }
36
37        if (count($where) > 0) {
38            $where = ' WHERE ' . join(' AND ', $where);
39        } else {
40            $where = '';
41        }
42
43        $sql = "SELECT wt.seller_id,
44                     SUM(wt.workingtime_seconds)/3600 AS workingtime_hours, 
45                     SUM(wt.workingtime_wage) AS workingtime_wage
46                FROM `workingtime` wt
47                $where
48                GROUP BY wt.seller_id
49              ";
50        $data = \Yii::$app->db->createCommand($sql, [])->queryAll();
51
52        $tmp = [];
53        foreach ($data as $dt) {
54            $tmp[$dt['seller_id']] = $dt;
55        }
56        return $tmp;
57    }
58
59    public static function sellerReport() {
60
61        $orderSearch = Yii::$app->request->get('OrderSearch');
62
63        $where = [];
64
65        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
66            $timestamp = strtotime($orderSearch['order_datetime_min']);
67            if ($timestamp !== false) {
68                $min_date = date('Y-m-d 00:00:00', $timestamp);
69                $where[] = " o.order_datetime>='$min_date' ";
70            }
71        }
72        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
73            $timestamp = strtotime($orderSearch['order_datetime_max']);
74            if ($timestamp !== false) {
75                $max_date = date('Y-m-d 23:59:59', $timestamp);
76                $where[] = " o.order_datetime<='$max_date' ";
77            }
78        }
79
80        if (count($where) > 0) {
81            $where = ' WHERE ' . join(' AND ', $where);
82        } else {
83            $where = '';
84        }
85
86        $sql = "SELECT o.sysuser_id,o.seller_id, o.sysuser_fullname, 
87                     SUM(o.order_total) AS order_total, 
88                     COUNT(o.order_id) AS order_count,
89                     AVG(if(o.order_total>0,o.order_total,0)) AS order_average
90              FROM `order` o
91              $where
92              GROUP BY o.sysuser_id
93              ORDER BY o.sysuser_fullname";
94        $data = \Yii::$app->db->createCommand($sql, [])->queryAll();
95        return $data;
96    }
97
98    public static function productReport($orderSearch = false)
99    {
100        $query = new Query;
101        $query->select('HOUR(o.order_datetime) AS h,
102                        product_type.product_type_id,
103                        product_type.product_type_name product_title, 
104                        SUM(packaging_product.packaging_product_quantity * order_packaging.order_packaging_number) AS total_packaging_product_quantity,
105                        product_type.`product_unit`')
106                ->from('`order` o')
107                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
108                ->innerJoin('order_packaging', 'o.order_id=order_packaging.order_id')
109                ->innerJoin('packaging_product', 'order_packaging.packaging_id=packaging_product.packaging_id')
110                ->innerJoin('product_type', 'packaging_product.product_type_id=product_type.product_type_id')
111                ->groupBy(['product_type.product_type_id', 'h']);
112        
113        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
114            $timestamp = strtotime($orderSearch['order_datetime_min']);
115            if ($timestamp !== false) {
116                $min_date = date('Y-m-d 00:00:00', $timestamp);
117                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
118            }
119        }
120        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
121            $timestamp = strtotime($orderSearch['order_datetime_max']);
122            if ($timestamp !== false) {
123                $max_date = date('Y-m-d 23:59:59', $timestamp);
124                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
125            }
126        }
127
128        if (isset($orderSearch['pos.pos_title']) && count($orderSearch['pos.pos_title']) > 0) {
129            $query->andWhere(["pos.pos_title" => $orderSearch['pos.pos_title']]);
130        }
131
132        if (isset($orderSearch['sysuser.sysuser_fullname']) && strlen($orderSearch['sysuser.sysuser_fullname']) > 0) {
133            $query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
134        }
135
136        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
137            $query->andWhere(['pos.pos_id' => $orderSearch['pos_id']]);
138        }
139
140        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
141            $query->andWhere( ['o.sysuser_id' => $orderSearch['sysuser_id']]);
142        }
143        
144        if (isset($orderSearch['product_title']) && strlen($orderSearch['product_title']) > 0) {
145            $query->andWhere(" LOCATE (:product_title_value,product_type.product_type_name) ", ['product_title_value' => $orderSearch['product_title']]);
146        }
147
148        return $query;
149    }
150
151    public static function packagingReportCount($orderSearch) {
152        $query = self::packagingReport($orderSearch);
153        $query->select('sum(order_packaging_number) AS packaging_number');
154        $query->groupBy([]);
155        $cnt = $query->one();
156        //print_r($cnt);
157        return $cnt['packaging_number'];
158    }
159
160    public static function cashflowReport($orderSearch) {
161        $query = new Query;
162        $query
163            ->select('cf.*, p.pos_title, su.sysuser_fullname, doc.document_id, doc.file_name')
164            ->from(CashFlow::tableName() . ' cf')
165            ->innerJoin(Pos::tableName(). ' p', 'cf.pos_id = p.pos_id')
166            ->leftJoin(Sysuser::tableName() .' su', 'cf.sysuser_id = su.sysuser_id')
167            ->leftJoin(Document::tableName() . ' doc', 'cf.entry_id = doc.target_id AND doc.target_type = "' . preg_replace("/\\\\/", "\\\\\\\\", CashFlow::className()) . '"');
168
169        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
170            $timestamp = strtotime($orderSearch['order_datetime_min']);
171            if ($timestamp !== false) {
172                if (isset($orderSearch['order_time_min']) && strlen($orderSearch['order_time_min']) > 0) {
173                    $min_date = date('Y-m-d '.$orderSearch['order_time_min'].':00', $timestamp);
174                } else {
175                    $min_date = date('Y-m-d 00:00:00', $timestamp);
176                }
177                $query->andWhere("cf.datetime_val>=:min_date ", ['min_date' => $min_date]);
178            }
179        }
180        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
181            $timestamp = strtotime($orderSearch['order_datetime_max']);
182            if ($timestamp !== false) {
183                if (isset($orderSearch['order_time_max']) && strlen($orderSearch['order_time_max']) > 0) {
184                    $max_date = date('Y-m-d '.$orderSearch['order_time_max'].':59', $timestamp);
185                } else {
186                    $max_date = date('Y-m-d 23:59:59', $timestamp);
187                }
188
189                $query->andWhere("cf.datetime_val<=:max_date ", ['max_date' => $max_date]);
190            }
191        }
192        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
193            $query->andWhere(['cf.pos_id' => $orderSearch['pos_id']]);
194        }
195
196        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
197            $query->andWhere( ['cf.sysuser_id' => $orderSearch['sysuser_id']]);
198        }
199
200        if (isset($orderSearch['operation_type']) && $orderSearch['operation_type'] != '-') {
201            $query->andWhere( ['cf.operation_type' => $orderSearch['operation_type']]);
202        }
203
204        return $query;
205    }
206
207    public static function packagingReport($orderSearch) {
208
209        $query = new Query;
210        $query->select('order_packaging.packaging_id, category.category_title, order_packaging.packaging_title, SUM(order_packaging_number) AS packaging_number')
211                ->from('`order` o')
212                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
213                ->innerJoin('order_packaging', 'o.order_id=order_packaging.order_id')
214                ->innerJoin('packaging', 'order_packaging.packaging_id=packaging.packaging_id')
215                ->innerJoin('category', 'category.category_id=packaging.category_id')
216                ->groupBy(['order_packaging.packaging_id'])
217        ;
218        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
219            $timestamp = strtotime($orderSearch['order_datetime_min']);
220            if ($timestamp !== false) {
221                $min_date = date('Y-m-d 00:00:00', $timestamp);
222                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
223            }
224        }
225        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
226            $timestamp = strtotime($orderSearch['order_datetime_max']);
227            if ($timestamp !== false) {
228                $max_date = date('Y-m-d 23:59:59', $timestamp);
229                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
230            }
231        }
232
233        if (isset($orderSearch['packaging_title']) && strlen($orderSearch['packaging_title']) > 0) {
234            $query->andWhere(" LOCATE (:packaging_title_value,order_packaging.packaging_title) ", ['packaging_title_value' => $orderSearch['packaging_title']]);
235        }
236        if (isset($orderSearch['category']) && strlen($orderSearch['category']) > 0 && $orderSearch['category'] > 0) {
237            $query->andWhere(" packaging.category_id=:category_id_value ", ['category_id_value' => $orderSearch['category']]);
238        }
239
240        if (isset($orderSearch['sysuser.sysuser_fullname']) && strlen($orderSearch['sysuser.sysuser_fullname']) > 0) {
241            $query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
242        }
243
244        if (isset($orderSearch['pos.pos_title']) && strlen($orderSearch['pos.pos_title']) > 0) {
245            $query->andWhere(" pos.pos_title=:pos_title_value ", ['pos_title_value' => $orderSearch['pos.pos_title']]);
246        }
247
248        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
249            $query->andWhere(['pos.pos_id' => $orderSearch['pos_id']]);
250        }
251
252        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
253            $query->andWhere( ['o.sysuser_id' => $orderSearch['sysuser_id']]);
254        }
255        
256        return $query;
257    }
258
259    public static function posIncomeReport($orderSearch = false) {
260
261        // posted data
262        if (!$orderSearch) {
263            $orderSearch = Yii::$app->request->get('OrderSearch');
264        }
265
266        $query = new Query;
267        $query->select('pos.pos_id, pos.pos_title, SUM(o.order_total) AS total, count(o.order_id) as n_orders, delivery_task.result_code as rcode')
268                ->from('`order` o')
269                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
270                ->leftJoin('delivery_task', 'o.order_id=delivery_task.order_id')
271                ->where('delivery_task.result_code is NULL or delivery_task.result_code = :rcode', [':rcode' => DeliveryTask::RESULT_COMPLETED])
272                ->groupBy(['pos.pos_id'])
273        ;
274        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
275            $timestamp = strtotime($orderSearch['order_datetime_min']);
276            if ($timestamp !== false) {
277                $min_date = date('Y-m-d 00:00:00', $timestamp);
278                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
279            }
280        }
281        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
282            $timestamp = strtotime($orderSearch['order_datetime_max']);
283            if ($timestamp !== false) {
284                $max_date = date('Y-m-d 23:59:59', $timestamp);
285                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
286            }
287        }
288        return $query;
289    }
290
291    public static function sellerIncomeReport() {
292
293        // posted data
294        $orderSearch = Yii::$app->request->get('OrderSearch');
295        $query = new Query;
296
297        $query->select('o.sysuser_id, max(o.sysuser_fullname) as sysuser_fullname, SUM(o.order_total) AS total')
298                ->from('`order` o')
299                ->groupBy(['o.sysuser_id']);
300
301        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
302            $timestamp = strtotime($orderSearch['order_datetime_min']);
303            if ($timestamp !== false) {
304                $min_date = date('Y-m-d 00:00:00', $timestamp);
305                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
306            }
307        }
308        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
309            $timestamp = strtotime($orderSearch['order_datetime_max']);
310            if ($timestamp !== false) {
311                $max_date = date('Y-m-d 23:59:59', $timestamp);
312                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
313            }
314        }
315
316        return $query;
317    }
318
319    public static function incomeByHourReport($orderSearch = false) {
320
321        $t = Array();
322        for ($i = 0; $i < 24; $i++) {
323            $t[$i] = 0;
324        }
325        // posted data
326        if (!$orderSearch) {
327            $orderSearch = Yii::$app->request->get('OrderSearch');
328        }
329
330        // select 
331        $query = new Query;
332        $query->select('HOUR(o.order_datetime) AS dt, SUM(o.order_total) AS total')
333                ->from('`order` o')
334                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
335                ->groupBy(['dt']);
336
337        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
338            $timestamp = strtotime($orderSearch['order_datetime_min']);
339            if ($timestamp !== false) {
340                $min_date = date('Y-m-d 00:00:00', $timestamp);
341                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
342            }
343        }
344
345        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
346            $timestamp = strtotime($orderSearch['order_datetime_max']);
347            if ($timestamp !== false) {
348                $max_date = date('Y-m-d 23:59:59', $timestamp);
349                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
350            }
351        }
352
353        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
354            $query->andWhere(['pos.pos_id' => $orderSearch['pos_id']]);
355        }
356
357        if (isset($orderSearch['payment_type']) && count($orderSearch['payment_type']) > 0) {
358            $query->andWhere(['in', 'o.order_payment_type', $orderSearch['payment_type']]);
359        }
360
361        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
362            $query->andWhere( ['o.sysuser_id' => $orderSearch['sysuser_id']]);
363        }
364
365        $result = $query->all();
366        foreach ($result as $res) {
367            $t[$res['dt']] = $res['total'];
368        }
369
370        return $t;
371    }
372
373    public static function incomeByWeekday($orderSearch = false) {
374
375        $t = Array();
376        for ($i = 1; $i < 8; $i++) {
377            $t[$i] = 0;
378        }
379        // posted data
380        if (!$orderSearch) {
381            $orderSearch = Yii::$app->request->get('OrderSearch');
382        }
383        // select
384        $query = new Query;
385        $query->select('DAYOFWEEK(o.order_datetime) AS dt, SUM(o.order_total) AS total')
386                ->from('`order` o')
387                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
388                ->groupBy(['dt']);
389
390        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
391            $timestamp = strtotime($orderSearch['order_datetime_min']);
392            if ($timestamp !== false) {
393                $min_date = date('Y-m-d 00:00:00', $timestamp);
394                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
395            }
396        }
397        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
398            $timestamp = strtotime($orderSearch['order_datetime_max']);
399            if ($timestamp !== false) {
400                $max_date = date('Y-m-d 23:59:59', $timestamp);
401                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
402            }
403        }
404        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
405            $query->andWhere(['pos.pos_id' => $orderSearch['pos_id']]);
406        }
407        if (isset($orderSearch['payment_type']) && count($orderSearch['payment_type']) > 0) {
408            $query->andWhere(['in', 'o.order_payment_type', $orderSearch['payment_type']]);
409        }
410        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
411            //$query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
412            $query->andWhere(['o.sysuser_id' => $orderSearch['sysuser_id']]);
413        }
414        $result = $query->all();
415        foreach ($result as $res) {
416            $t[$res['dt']] = $res['total'];
417        }
418
419        return $t;
420    }
421
422    public static function incomeDaily($orderSearch = false)
423    {
424        // posted data
425        if (!$orderSearch) {
426            $orderSearch = Yii::$app->request->get('OrderSearch');
427        }
428
429        // get date interval
430        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
431            $timestamp = strtotime($orderSearch['order_datetime_min']);
432            if ($timestamp !== false) {
433                $fromDate = $timestamp;
434            }
435        }
436        if (!isset($fromDate)) {
437            $fromDate = time() - 28 * 24 * 3600;
438        }
439
440        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
441            $timestamp = strtotime($orderSearch['order_datetime_max']);
442            if ($timestamp !== false) {
443                $toDate = $timestamp;
444            }
445        }
446        if (!isset($toDate)) {
447            $toDate = time();
448        }
449
450        //SELECT MIN(DATE(o.order_datetime)) AS mn, MAX(DATE(o.order_datetime)) AS mx FROM `order` o;
451        $query = new Query;
452        $query->select('MIN(DATE(o.order_datetime)) AS mn, MAX(DATE(o.order_datetime)) AS mx')
453                ->from('`order` o');
454        $minmax = $query->one();
455
456        if ($minmax) {
457            $timestamp = strtotime($minmax['mn']);
458            if ($fromDate < $timestamp) {
459                $fromDate = $timestamp;
460            }
461            $timestamp = strtotime($minmax['mx']);
462            if ($toDate > $timestamp) {
463                $toDate = $timestamp;
464            }
465        }
466
467        $t = [];
468        $ddt = 24 * 3600;
469        for ($dt = $fromDate; $dt <= $toDate; $dt+=$ddt) {
470            $t[date('Y-m-d', $dt)] = 0;
471        }
472
473        // select
474        $query = new Query;
475        $query->select('DATE(o.order_datetime) AS dt, SUM(o.order_total) AS total')
476                ->from('`order` o')
477                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
478                ->groupBy(['dt'])
479        ;
480        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
481            $timestamp = strtotime($orderSearch['order_datetime_min']);
482            if ($timestamp !== false) {
483                $min_date = date('Y-m-d 00:00:00', $timestamp);
484                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
485            }
486        }
487        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
488            $timestamp = strtotime($orderSearch['order_datetime_max']);
489            if ($timestamp !== false) {
490                $max_date = date('Y-m-d 23:59:59', $timestamp);
491                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
492            }
493        }
494        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
495            $query->andWhere(['pos.pos_id' => $orderSearch['pos_id']]);
496        }
497        if (isset($orderSearch['payment_type']) && count($orderSearch['payment_type']) > 0) {
498            $query->andWhere(['o.order_payment_type' => $orderSearch['payment_type']]);
499        }
500        if (isset($orderSearch['sysuser_id']) && count($orderSearch['sysuser_id']) > 0) {
501            $query->andWhere(['o.sysuser_id' => $orderSearch['sysuser_id']]);
502        }
503
504        $result = $query->all();
505        foreach ($result as $res) {
506            if(isset($t[$res['dt']])){
507               $t[$res['dt']] = $res['total'];
508            }
509        }
510        ksort($t);
511        return $t;
512    }
513
514    public static function profitDaily($orderSearch = false) {
515
516        // posted data
517        if (!$orderSearch) {
518            $orderSearch = Yii::$app->request->get('OrderSearch');
519        }
520
521        // get date interval
522        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
523            $timestamp = strtotime($orderSearch['order_datetime_min']);
524            if ($timestamp !== false) {
525                $fromDate = $timestamp;
526            }
527        }
528        if (!isset($fromDate)) {
529            $fromDate = time() - 28 * 24 * 3600;
530        }
531
532        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
533            $timestamp = strtotime($orderSearch['order_datetime_max']);
534            if ($timestamp !== false) {
535                $toDate = $timestamp;
536            }
537        }
538        if (!isset($toDate)) {
539            $toDate = time();
540        }
541
542        //SELECT MIN(DATE(o.order_datetime)) AS mn, MAX(DATE(o.order_datetime)) AS mx FROM `order` o;
543        $query = new Query;
544        $query->select('MIN(DATE(o.order_datetime)) AS mn, MAX(DATE(o.order_datetime)) AS mx')
545                ->from('`order` o')
546        ;
547        $minmax = $query->one();
548
549        if ($minmax) {
550            $timestamp = strtotime($minmax['mn']);
551            if ($fromDate < $timestamp) {
552                $fromDate = $timestamp;
553            }
554            $timestamp = strtotime($minmax['mx']);
555            if ($toDate > $timestamp) {
556                $toDate = $timestamp;
557            }
558        }
559
560        $t = Array();
561        $ddt = 24 * 3600;
562        for ($dt = $fromDate; $dt <= $toDate; $dt+=$ddt) {
563            $t[date('Y-m-d', $dt)] = 0;
564        }
565
566
567        $sql = "DROP TABLE IF EXISTS packaging_profit;";
568        \Yii::$app->db->createCommand($sql, [])->execute();
569
570        $sql = "CREATE TEMPORARY TABLE packaging_profit(
571                `packaging_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
572                `packaging_title` VARCHAR(32) DEFAULT NULL,
573                `profit` DOUBLE,
574                PRIMARY KEY (`packaging_id`)
575              ) ENGINE=MEMORY";
576        \Yii::$app->db->createCommand($sql, [])->execute();
577
578        $sql = "
579            INSERT INTO packaging_profit(packaging_id,packaging_title,profit)
580            SELECT packaging_product.packaging_id, packaging.`packaging_title`, packaging.`packaging_price` - SUM(packaging_product.`packaging_product_quantity`*product.`product_unit_price`) AS profit
581            FROM packaging_product
582            INNER JOIN product ON product.product_id=packaging_product.product_id
583            INNER JOIN packaging ON packaging_product.packaging_id=packaging.packaging_id
584            GROUP BY packaging_id;
585            ";
586        \Yii::$app->db->createCommand($sql, [])->execute();
587
588        // select
589        $query = new Query;
590        $query->select('DATE(o.order_datetime) AS dt, SUM(packaging_profit.profit*order_packaging.`order_packaging_number`) AS total')
591                ->from('packaging_profit')
592                ->innerJoin('`order_packaging`', 'packaging_profit.packaging_id=order_packaging.packaging_id')
593                ->innerJoin('`order` o', 'order_packaging.order_id=o.order_id')
594                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
595                ->innerJoin('sysuser', 'o.sysuser_id=sysuser.sysuser_id')
596                ->groupBy(['dt'])
597        ;
598        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
599            $timestamp = strtotime($orderSearch['order_datetime_min']);
600            if ($timestamp !== false) {
601                $min_date = date('Y-m-d 00:00:00', $timestamp);
602                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
603            }
604        }
605        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
606            $timestamp = strtotime($orderSearch['order_datetime_max']);
607            if ($timestamp !== false) {
608                $max_date = date('Y-m-d 23:59:59', $timestamp);
609                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
610            }
611        }
612        if (isset($orderSearch['pos.pos_title']) && strlen($orderSearch['pos.pos_title']) > 0) {
613            $query->andWhere(" pos.pos_title=:pos_title_value ", ['pos_title_value' => $orderSearch['pos.pos_title']]);
614        }
615
616        if (isset($orderSearch['sysuser.sysuser_fullname']) && strlen($orderSearch['sysuser.sysuser_fullname']) > 0) {
617            $query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
618        }
619
620        $result = $query->all();
621        foreach ($result as $res) {
622            $t[$res['dt']] = $res['total'];
623        }
624
625        return $t;
626    }
627
628    public static function profitWeekDaily($orderSearch = false) {
629
630        // posted data
631        if (!$orderSearch) {
632            $orderSearch = Yii::$app->request->get('OrderSearch');
633        }
634
635        $t = Array();
636        for ($i = 1; $i < 8; $i++) {
637            $t[$i] = 0;
638        }
639
640        $sql = "DROP TABLE IF EXISTS packaging_profit;";
641        \Yii::$app->db->createCommand($sql, [])->execute();
642
643        $sql = "CREATE TEMPORARY TABLE packaging_profit(
644                `packaging_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
645                `packaging_title` VARCHAR(32) DEFAULT NULL,
646                `profit` DOUBLE,
647                PRIMARY KEY (`packaging_id`)
648              ) ENGINE=MEMORY";
649        \Yii::$app->db->createCommand($sql, [])->execute();
650
651        $sql = "
652            INSERT INTO packaging_profit(packaging_id,packaging_title,profit)
653            SELECT packaging_product.packaging_id, packaging.`packaging_title`, packaging.`packaging_price` - SUM(packaging_product.`packaging_product_quantity`*product.`product_unit_price`) AS profit
654            FROM packaging_product
655            INNER JOIN product ON product.product_id=packaging_product.product_id
656            INNER JOIN packaging ON packaging_product.packaging_id=packaging.packaging_id
657            GROUP BY packaging_id;
658            ";
659        \Yii::$app->db->createCommand($sql, [])->execute();
660
661        $query = new Query;
662        $query->select('DAYOFWEEK(o.order_datetime) AS dt, SUM(packaging_profit.profit*order_packaging.`order_packaging_number`) AS total')
663                ->from('packaging_profit')
664                ->innerJoin('`order_packaging`', 'packaging_profit.packaging_id=order_packaging.packaging_id')
665                ->innerJoin('`order` o', 'order_packaging.order_id=o.order_id')
666                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
667                ->innerJoin('sysuser', 'o.sysuser_id=sysuser.sysuser_id')
668                ->groupBy(['dt'])
669        ;
670        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
671            $timestamp = strtotime($orderSearch['order_datetime_min']);
672            if ($timestamp !== false) {
673                $min_date = date('Y-m-d 00:00:00', $timestamp);
674                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
675            }
676        }
677        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
678            $timestamp = strtotime($orderSearch['order_datetime_max']);
679            if ($timestamp !== false) {
680                $max_date = date('Y-m-d 23:59:59', $timestamp);
681                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
682            }
683        }
684        //        if (isset($orderSearch['order_payment_type']) && strlen($orderSearch['order_payment_type']) > 0) {
685        //            $query->andWhere(" o.order_payment_type=':order_payment_type' ",['order_payment_type'=>$orderSearch['order_payment_type']] );
686        //        }
687
688        if (isset($orderSearch['pos.pos_title']) && strlen($orderSearch['pos.pos_title']) > 0) {
689            $query->andWhere(" pos.pos_title=:pos_title_value ", ['pos_title_value' => $orderSearch['pos.pos_title']]);
690        }
691
692        if (isset($orderSearch['sysuser.sysuser_fullname']) && strlen($orderSearch['sysuser.sysuser_fullname']) > 0) {
693            $query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
694        }
695
696        $result = $query->all();
697        foreach ($result as $res) {
698            $t[$res['dt']] = $res['total'];
699        }
700
701        return $t;
702    }
703
704    public static function countOrdersHourly($orderSearch = false) {
705
706        // posted data
707        if (!$orderSearch) {
708            $orderSearch = Yii::$app->request->get('OrderSearch');
709        }
710
711        $t = Array();
712        for ($i = 0; $i < 24; $i++) {
713            $t[$i] = 0;
714        }
715
716        $query = new Query;
717        $query->select('HOUR(o.order_datetime) AS dt, count(o.`order_id`) AS total')
718                ->from('`order` o')
719                ->innerJoin('pos', 'o.pos_id=pos.pos_id')
720                ->innerJoin('sysuser', 'o.sysuser_id=sysuser.sysuser_id')
721                ->groupBy(['dt']);
722
723        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
724            $timestamp = strtotime($orderSearch['order_datetime_min']);
725            if ($timestamp !== false) {
726                $min_date = date('Y-m-d 00:00:00', $timestamp);
727                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
728            }
729        }
730
731        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
732            $timestamp = strtotime($orderSearch['order_datetime_max']);
733            if ($timestamp !== false) {
734                $max_date = date('Y-m-d 23:59:59', $timestamp);
735                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
736            }
737        }
738
739        if (isset($orderSearch['pos.pos_title']) && count($orderSearch['pos.pos_title']) > 0) {
740            $query->andWhere( ['pos.pos_title' => $orderSearch['pos.pos_title']]);
741        }
742
743        if (isset($orderSearch['pos_id']) && count($orderSearch['pos_id']) > 0) {
744            $query->andWhere( ['o.pos_id' => $orderSearch['pos_id']]);
745        }
746
747        if (isset($orderSearch['payment_type']) && count($orderSearch['payment_type']) > 0) {
748            $query->andWhere( ['o.order_payment_type' => $orderSearch['payment_type']]);
749        }
750
751        if (isset($orderSearch['sysuser.sysuser_fullname']) && count($orderSearch['sysuser.sysuser_fullname']) > 0) {
752            //$query->andWhere(" LOCATE (:sysuser_fullname_value,o.sysuser_fullname) ", ['sysuser_fullname_value' => $orderSearch['sysuser.sysuser_fullname']]);
753            $query->andWhere( ['o.sysuser_fullname' => $orderSearch['sysuser.sysuser_fullname']]);
754        }
755
756        $result = $query->all();
757        foreach ($result as $res) {
758            $t[$res['dt']] = $res['total'];
759        }
760
761        return $t;
762    }
763
764    public static function customerIncomeReport() {
765
766        // posted data
767        $orderSearch = Yii::$app->request->get('OrderSearch');
768        $query = new Query;
769
770        $datelimit = date('Y-m-d H:i:s', time() - 24 * 3600 * \Yii::$app->params['activityInterval']);
771        $datePrevLimit = date('Y-m-d H:i:s', time() - 2 * 24 * 3600 * \Yii::$app->params['activityInterval']);
772        $query->select("c.customerId, c.customerMobile, c.customerName,
773                        SUM(o.order_total) total,
774                        SUM( o.order_datetime > '{$datelimit}' ) as activity,
775                        SUM( o.order_datetime > '{$datelimit}' ) - SUM( o.order_datetime >='{$datePrevLimit}' AND  o.order_datetime <='{$datelimit}' ) as dActivity
776                        ")
777                ->from('customer c')
778                ->leftJoin('`order` o', 'o.customerId=c.customerId')
779                ->groupBy(['c.customerId'])
780        ;
781
782        if (isset($orderSearch['order_datetime_min']) && strlen($orderSearch['order_datetime_min']) > 0) {
783            $timestamp = strtotime($orderSearch['order_datetime_min']);
784            if ($timestamp !== false) {
785                $min_date = date('Y-m-d 00:00:00', $timestamp);
786                $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
787            }
788        }
789        if (isset($orderSearch['order_datetime_max']) && strlen($orderSearch['order_datetime_max']) > 0) {
790            $timestamp = strtotime($orderSearch['order_datetime_max']);
791            if ($timestamp !== false) {
792                $max_date = date('Y-m-d 23:59:59', $timestamp);
793                $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
794            }
795        }
796
797        if (isset($orderSearch['customerMobile']) && strlen($orderSearch['customerMobile']) > 0) {
798            $query->andWhere(" LOCATE(:customerMobileSubstring, c.customerMobile) ", ['customerMobileSubstring' => $orderSearch['customerMobile']]);
799        }
800        if (isset($orderSearch['customerName']) && strlen($orderSearch['customerName']) > 0) {
801            $query->andWhere(" LOCATE(:customerNameSubstring, c.customerName) ", ['customerNameSubstring' => $orderSearch['customerName']]);
802        }
803
804        return $query;
805    }
806
807
808    public static function onecustomerSummary($customerId){
809        $query = new Query;
810        $query->select("
811                    SUM(o.order_total) order_total,
812                    COUNT(o.order_id) AS order_count,
813                    AVG(o.order_total) AS order_avg
814                    ")
815              ->from("`order` o")
816              ->where(['customerId'=>$customerId,])
817              ->andWhere('o.order_total>0');
818        return $query->one();
819    }
820
821    public static function onecustomerReport($customerId, $rows=5) {
822
823        // get customer info
824        $customer = Customer::findOne($customerId);
825
826
827        // query to select all customer's orders
828        // posted data
829        $orderSearch = Yii::$app->request->get('OrderSearch');
830
831        $query = new Query;
832        $query->select('o.*, pos.pos_title')
833              ->from('`order` o')
834              ->innerJoin('pos','o.pos_id=pos.pos_id')
835              ->andFilterWhere(['customerId' => $customerId,]);
836        $query->orderBy('order_datetime desc');
837        $query->limit($rows)->offset(0);
838
839        $favoritesQuery = self::favoritesQuery($customerId);
840
841        return ['customer' => $customer, 'query' => $query, 'favoritesQuery' => $favoritesQuery];
842    }
843
844    public static function favoritesQuery($customerId) {
845        $favoritesQuery = new Query;
846
847        $favoritesQuery->select('op.packaging_title, op.packaging_id, SUM(op.order_packaging_number) AS packaging_number')
848                ->from('`order` o')
849                ->innerJoin('`order_packaging` op', 'op.order_id=o.order_id')
850                ->groupBy(['op.packaging_id'])
851                ->orderBy('packaging_number DESC')
852                ->limit(10)
853        ;
854        $favoritesQuery->andWhere(" o.customerId=:customerIdValue ", ['customerIdValue' => (int) $customerId]);
855        $min_date = date('Y-m-d 00:00:00', time() - \Yii::$app->params['activityInterval'] * 3600 * 24);
856        $favoritesQuery->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
857        return $favoritesQuery;
858    }
859
860    public static function customerActivity($customerId) {
861
862        $nWeeks = 12;
863        $activityQuery = new Query;
864
865        $activityQuery->select('YEARWEEK(o.order_datetime) as order_week, count(o.order_id) as n_orders, sum(o.order_total) as order_total')
866                ->from('`order` o')
867                ->groupBy(['order_week'])
868                ->orderBy('order_week DESC')
869        ;
870        $activityQuery->andWhere(" o.customerId=:customerIdValue ", ['customerIdValue' => (int) $customerId]);
871        $min_date = date('Y-m-d', strtotime(date('Y-m-d ') . " - $nWeeks weeks"));
872        $activityQuery->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
873
874        $tmp = $activityQuery->all();
875
876        $activity = [];
877        $date = new \DateTime();
878        $oneWeek = new \DateInterval("P1W");
879        for ($i = 0; $i < $nWeeks; $i++) {
880            $key = $date->format('YW');
881            $activity[$key] = ['n_orders' => 0, 'order_total' => 0, 'order_week' => $key];
882            $date = $date->sub($oneWeek);
883        }
884        foreach ($tmp as $tm) {
885            if (isset($activity[$tm['order_week']])) {
886                $activity[$tm['order_week']] = $tm;
887            }
888        }
889
890        return array_reverse($activity);
891    }
892
893    public static function getColors() {
894        return
895                [
896                    ['#003300', '#003333'], ['#003366', '#003399'], ['#0033cc', '#0033ff'],
897                    ['#006600', '#006633'], ['#006666', '#006699'], ['#0066cc', '#0066ff'],
898                    ['#009900', '#009933'], ['#009966', '#009999'], ['#0099cc', '#0099ff'],
899                    ['#00cc00', '#00cc33'], ['#00cc66', '#00cc99'], ['#00cccc', '#00ccff'],
900                    ['#00ff00', '#00ff33'], ['#00ff66', '#00ff99'], ['#00ffcc', '#00ffff'],
901                    ['#330000', '#330033'], ['#330066', '#330099'], ['#3300cc', '#3300ff'],
902                    ['#333300', '#333333'], ['#333366', '#333399'], ['#3333cc', '#3333ff'],
903                    ['#336600', '#336633'], ['#336666', '#336699'], ['#3366cc', '#3366ff']];
904    }
905
906    public static function todaySummary() {
907        $min_date = date('Y-m-d 00:00:00');
908
909        $query = new Query;
910        $query->select('count(o.order_id) as n_orders,
911                        sum(o.order_total) as order_total,
912                        AVG(o.order_total) as order_average')
913                ->from('`order` o')
914                ->where("o.order_datetime >= :min_date ", ['min_date' => $min_date])
915                ->andWhere(['not in', 'o.order_payment_type', [Order::PAYMENT_DELIVERY]]);
916        $todaySummary = $query->one();
917
918        // число заказов уменьшается на число возвратов, так как они не должны считаться как отдельные заказы
919        // при этом сумма заказов корректируется сама, так как возвраты регистрируются с отрицательной суммой
920        $todaySummary['n_orders'] -= Order::find()
921            ->where(['order_payment_type' => [Order::PAYMENT_CASHBACK, Order::PAYMENT_CARDBACK]])
922            ->andWhere(['>=', 'order_datetime', $min_date])
923            ->count();
924
925        // число заказов уменьшается на суммы отказных заказов
926        $sql='
927            SELECT count(o.order_id) as order_num_rejected, sum(o.order_total) as order_rejected
928            FROM `order` o
929            LEFT JOIN `delivery_task` ON o.order_id = `delivery_task`.order_id
930            WHERE delivery_task.result_code = :rcode AND o.order_datetime >= :min_date';
931        $command = Yii::$app->db->createCommand($sql, [':rcode' => DeliveryTask::RESULT_REJECTED, ':min_date' => $min_date]);
932        $rejectedDeliveries = $command->queryOne();
933
934        // корректировка суммы и кол-ва заказов на суму отказных
935        $todaySummary['order_total'] -= $rejectedDeliveries['order_rejected'];
936        $todaySummary['n_orders'] -= $rejectedDeliveries['order_num_rejected'];
937
938        // средний чек тоже нужно скорректировать с учетом нового кол-ва заказов и изменения суммы заказов
939        if($todaySummary['n_orders'] > 0){
940            $todaySummary['order_average'] = $todaySummary['order_total'] / $todaySummary['n_orders'];
941        }
942
943        // статистика по закзам доставки
944        $query = new Query;
945        $query->select('count(o.order_id) as n_orders,
946                        sum(o.order_total) as order_total,
947                        AVG(o.order_total) as order_average')
948              ->from('`order` o')
949              ->where("o.order_datetime >= :min_date ", ['min_date' => $min_date])
950              ->andWhere(['in', 'o.order_payment_type', [Order::PAYMENT_DELIVERY]]);
951        $todayDeliverySummary = $query->one();
952
953        // статистика за вчера
954        $query = new Query;
955        $query->select('count(o.order_id) as n_orders, 
956                        sum(o.order_total) as order_total,
957                        AVG(o.order_total) as order_average')
958                ->from('`order` o');
959
960        $min_date = date('Y-m-d 00:00:01', strtotime(date('Y-m-d 00:00:01') . " - 1 day"));
961        $query->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
962
963        $max_date = date('Y-m-d H:i:s', strtotime(date('Y-m-d 23:59:59') . " - 1 day"));
964        $query->andWhere(" o.order_datetime<=:max_date ", ['max_date' => $max_date]);
965
966        $yesterdaySummary = $query->one();
967
968        // число заказов уменьшается на число возвратов, так как они не должны считаться как отдельные заказы
969        // при этом сумма заказов корректируется сама, так как возвраты регистрируются с отрицательной суммой
970        $yesterdaySummary['n_orders'] -= Order::find()
971            ->where(['order_payment_type' => [Order::PAYMENT_CASHBACK, Order::PAYMENT_CARDBACK]])
972            ->andWhere(['>=', 'order_datetime', $min_date])
973            ->andWhere(['<=', 'order_datetime', $max_date])
974            ->count();
975
976        $sql='
977            SELECT count(o.order_id) as order_num_rejected, sum(o.order_total) as order_rejected
978            FROM `order` o
979            LEFT JOIN `delivery_task` ON o.order_id = `delivery_task`.order_id
980            WHERE delivery_task.result_code = :rcode
981              AND o.order_datetime >= :min_date
982              AND o.order_datetime <= :max_date';
983        $command = Yii::$app->db->createCommand($sql, [
984            ':rcode' => DeliveryTask::RESULT_REJECTED,
985            ':min_date' => $min_date,
986            ':max_date' => $max_date
987        ]);
988        $rejectedDeliveries = $command->queryOne();
989
990        // корректировка суммы и кол-ва заказов на суму отказных
991        $yesterdaySummary['order_total'] -= $rejectedDeliveries['order_rejected'];
992        $yesterdaySummary['n_orders'] -= $rejectedDeliveries['order_num_rejected'];
993
994        // средний чек тоже нужно скорректировать с учетом нового кол-ва заказов
995        if($yesterdaySummary['n_orders'] > 0){
996            $yesterdaySummary['order_average'] = $yesterdaySummary['order_total'] / $yesterdaySummary['n_orders'];
997        }
998
999        $query = new Query;
1000        $query->select('count(o.order_id) as n_orders,
1001                        sum(o.order_total) as order_total,
1002                        AVG(o.order_total) as order_average')
1003              ->from('`order` o')
1004              ->where(['between', 'o.order_datetime', $min_date, $max_date])
1005              ->andWhere(['in', 'o.order_payment_type', [Order::PAYMENT_DELIVERY]]);
1006        $yesterdayDeliverySummary = $query->one();
1007
1008        return [
1009            'today' => $todaySummary,
1010            'yesterday' => $yesterdaySummary,
1011            'todayDelivery' => $todayDeliverySummary,
1012            'yesterdayDelivery' => $yesterdayDeliverySummary,
1013        ];
1014    }
1015
1016    public static function todayPosSummary() {
1017        // СТАТИСТИКА ЗА СЕГОДНЯ
1018        $query = new Query;
1019        $min_date = date('Y-m-d 00:00:00');
1020        $query->select('p.pos_id, p.pos_title, p.pos_address, count(o.order_id) as n_orders, 
1021                        sum(o.order_total) as order_total,
1022                        AVG(o.order_total) as order_average')
1023                ->from('`pos` p')
1024                ->leftJoin(" `order` o", "p.pos_id=o.pos_id and o.order_datetime>='{$min_date}' ")
1025                ->leftJoin(['dtask' => DeliveryTask::tableName()], "o.order_id = dtask.order_id")
1026                ->where('(dtask.result_code IS NULL OR dtask.result_code != :rejected) AND p.status = :active', [':rejected' => DeliveryTask::RESULT_REJECTED, ':active' => Pos::STATUS_ACTIVE])
1027                ->andWhere('o.order_payment_type != :delivery', [':delivery' => Order::PAYMENT_DELIVERY])
1028                ->groupBy(['p.pos_id'])
1029                ->orderBy('p.pos_title');
1030        $todaySummary = $query->all();
1031
1032        $posSummary = [];
1033        foreach ($todaySummary as $summ) {
1034            $posSummary[$summ['pos_id']] = ['today'=>$summ];
1035        }
1036
1037        // СТАТИСТИКА ЗА ВЧЕРА
1038        $min_date = date('Y-m-d 00:00:01', strtotime(date('Y-m-d 00:00:01') . " - 1 day"));
1039        $max_date = date('Y-m-d H:i:s', strtotime(date('Y-m-d 23:59:59') . " - 1 day"));
1040
1041        $sql = '
1042            SELECT
1043                p.pos_id,
1044                p.pos_title,
1045                p.pos_address,
1046                COUNT(o.order_id) as n_orders,
1047                SUM(`o`.`order_total`) AS order_total,
1048                AVG(o.order_total) as order_average
1049            FROM `pos` p
1050            LEFT JOIN `order` o ON p.pos_id = o.pos_id AND o.order_datetime >= :min_date AND o.order_datetime <= :max_date
1051            LEFT JOIN `delivery_task` ON o.order_id = delivery_task.order_id
1052            WHERE p.status = :active AND (delivery_task.result_code IS NULL OR delivery_task.result_code != :dtres) AND o.order_payment_type != :delivery
1053            GROUP BY p.pos_id
1054            ORDER BY p.pos_title
1055        ';
1056        $command = Yii::$app->db->createCommand($sql, [
1057            ':dtres' => DeliveryTask::RESULT_REJECTED,
1058            ':active' => Pos::STATUS_ACTIVE,
1059            ':delivery' => Order::PAYMENT_DELIVERY,
1060            ':min_date' => $min_date,
1061            ':max_date' => $max_date,
1062        ]);
1063        $yesterdaySummary = $command->queryAll();
1064        foreach ($yesterdaySummary as $summ) {
1065            $posSummary[$summ['pos_id']]['yesterday'] = $summ;
1066        }
1067
1068        return $posSummary;
1069    }
1070
1071    public static function lastOrders(){
1072        $query = new Query;
1073        // $min_date = date('Y-m-d 00:00:00');
1074        $query->select('o.*, p.pos_id, p.pos_title, p.pos_address, c.customerMobile, c.customerName')
1075                ->from('`order` o')
1076                ->innerJoin("`pos` p ", "p.pos_id=o.pos_id")
1077                ->leftJoin("`customer` c" , "c.customerId=o.customerId")
1078                ->orderBy('o.order_datetime desc')
1079                ->limit(10)
1080        ;
1081        return $query->all();
1082        // and o.order_datetime>='{$min_date}' 
1083    }
1084    
1085    public static function todayFavorites(){
1086        $favoritesQuery = new Query;
1087
1088        $favoritesQuery->select(
1089                'op.packaging_title, op.packaging_id,
1090                 SUM(op.order_packaging_number) AS packaging_number,
1091                 SUM(op.order_packaging_number* op.packaging_price) AS packaging_total
1092                ')
1093                ->from('`order` o')
1094                ->innerJoin('`order_packaging` op', 'op.order_id=o.order_id')
1095                ->groupBy(['op.packaging_id'])
1096                ->orderBy('packaging_number DESC, packaging_total DESC')
1097                ->limit(10);
1098
1099        //$min_date = date('Y-m-d 00:00:00', time() - \Yii::$app->params['activityInterval'] * 3600 * 24);
1100        $min_date = date('Y-m-d 00:00:00');
1101        $favoritesQuery->andWhere(" o.order_datetime>=:min_date ", ['min_date' => $min_date]);
1102        return $favoritesQuery->all();
1103    }
1104
1105    /**
1106     * @param int $seller_id
1107     * @param string $date Время, в виде YYYY-mm-dd
1108     * @return array ['cost', 'revenue']
1109     */
1110    public static function sellerDayIncome($seller_id, $date){
1111        return self::sellerPeriodIncome($seller_id, $date, $date);
1112    }
1113
1114    public static function sellerPeriodIncome($seller_id, $date_start, $date_finish){
1115        /* @var Seller $seller */
1116        $seller = Seller::find()->where(['seller_id' => $seller_id])->with('pos')->one();
1117        if($seller == null){
1118            return null;
1119        }
1120        // начальные рамки = время работы точки продажи
1121        $start_time = $seller->pos->pos_worktime_start;
1122        $finish_time = $seller->pos->pos_worktime_finish;
1123
1124        // если для роли заданы свои рабочие рамки, то используем их
1125        if ($seller->seller_worktime_start)  { $start_time = $seller->seller_worktime_start; }
1126        if ($seller->seller_worktime_finish) { $finish_time = $seller->seller_worktime_finish; }
1127
1128        // корректировка дня, если указана ночная смена
1129        $startDatetime =  new \DateTime($date_start.' '.$start_time);
1130        $finishDatetime = new \DateTime($date_finish.' '.$finish_time);
1131        while($startDatetime->getTimestamp() > $finishDatetime->getTimestamp()){
1132            $finishDatetime->add( new \DateInterval('P1D') );
1133        }
1134
1135        // финальные значени времени смены для $seller за дату $date
1136        $start_time  = $startDatetime->format(Log::DT_FORMAT);
1137        $finish_time = $finishDatetime->format(Log::DT_FORMAT);
1138
1139        $tmp_table = 'packaging_cost_' . time();
1140
1141        $sql = <<<TAG
1142        CREATE TEMPORARY TABLE $tmp_table (
1143                `packaging_id` BIGINT(20) NOT NULL,
1144                `packaging_cost` DOUBLE,
1145                PRIMARY KEY (`packaging_id`)
1146              ) ENGINE=MEMORY;
1147
1148        INSERT INTO $tmp_table(packaging_id, packaging_cost)
1149        SELECT
1150            packaging_product.packaging_id,
1151            SUM(packaging_product.`packaging_product_quantity`*product.`product_unit_price`)
1152        FROM packaging_product
1153        INNER JOIN product ON product.product_id = packaging_product.product_id
1154        INNER JOIN packaging ON packaging_product.packaging_id = packaging.packaging_id
1155        WHERE packaging_product.packaging_id IN (
1156            SELECT distinct(packaging_id) FROM order_packaging WHERE order_id in (
1157                SELECT order_id FROM `order` WHERE order_datetime >= '$start_time' AND order_datetime <= '$finish_time' AND seller_id = $seller->seller_id
1158            )
1159        )
1160        GROUP BY packaging_id;
1161TAG;
1162        \Yii::$app->db->createCommand($sql, [])->execute();
1163
1164        $sql = <<<TAG
1165        SELECT
1166            o.pos_id,
1167            o.seller_id,
1168            o.order_id,
1169            o.order_total,
1170            o.order_datetime,
1171            o.order_payment_type,
1172            SUM(op.order_packaging_number * pc.packaging_cost) as package_cost
1173        FROM order_packaging op
1174        INNER JOIN `order` o ON o.order_id = op.order_id
1175        INNER JOIN $tmp_table pc ON op.packaging_id = pc.packaging_id
1176        WHERE seller_id = $seller->seller_id AND order_datetime >= '$start_time' AND order_datetime <= '$finish_time'
1177        GROUP BY o.order_id;
1178TAG;
1179        $result = \Yii::$app->db->createCommand($sql, [])->queryAll();
1180
1181        $sql = "DROP TABLE IF EXISTS $tmp_table;";
1182        \Yii::$app->db->createCommand($sql, [])->execute();
1183
1184        // подсчет выручки и себестоимости
1185        $paymentsCount = 0;
1186        $cost = 0;
1187        $revenue = 0;
1188        foreach($result as $order){
1189            if($order['order_payment_type'] != Order::PAYMENT_CASHBACK && $order['order_payment_type'] != Order::PAYMENT_CARDBACK){
1190                // если это не возврат -> засчитаем как продажу
1191                $paymentsCount += 1;
1192                // учтем себестоимость
1193                $cost += $order['package_cost'];
1194            } else {
1195                // вычет себестоимости возврата так как она была суммирована при учете продажи
1196                $cost -= $order['package_cost'];
1197            }
1198            // выручка корректируется сама, так как при возврате заносится отрицательная сумма
1199            $revenue += intval($order['order_total']);
1200        }
1201
1202        return ['cost' => $cost, 'revenue' => $revenue, 'order_count' => $paymentsCount];
1203    }
1204}