ball/app/adminapi/logic/data_analysis/DataAnalysisLogic.php

<?php
namespace app\adminapi\logic\data_analysis;

use app\common\logic\BaseLogic;
use app\common\model\goods_category\GoodsCategory;
use think\facade\Db;


/**
 * DataAnalysis逻辑
 * Class DataAnalysisLogic
 * @package app\adminapi\logic
 */
class DataAnalysisLogic extends BaseLogic
{

    public static function serviceOrdersNumber(array $params = [])
    {
        $condition = self::paramsToSelectCondition($params);
        return Db::name('service_work')->alias('a')->field($condition['field'])
            ->where($condition['where'])
            ->group($condition['group'])
            ->order($condition['order'])
            ->select()->column('y', 'x');
    }
    public static function paramsToSelectCondition(array $params = [])
    {
        $where = [];
        $field = [];
        $group = [];
        $order = [];
        if($params['type'] == 'hour'){
            $params = $params['hour'];
            $field[] = Db::raw("HOUR(FROM_UNIXTIME(a.create_time)) AS x");
            if($params['date_range']){
                list($start_time,$end_time) = $params['date_range'];
                $where[] = ['a.create_time','between',[strtotime($start_time),strtotime($end_time)+86400-1]];
            }
            $group[] = Db::raw("HOUR(FROM_UNIXTIME(a.create_time))");
            $order[] = 'x';
        }else{
            $params = $params['category_type'];
            switch ($params['segmented']){
                case 1:
                    $field[] = Db::raw("FROM_UNIXTIME(a.create_time,'%Y-%m-%d') AS x");
                    if($params['date_range']){
                        list($start_time,$end_time) = $params['date_range'];
                        $where[] = ['a.create_time','between',[strtotime($start_time),strtotime($end_time)+86400-1]];
                    }
                    $group[] = Db::raw("x");
                    $order[] = 'x';
                    break;
                case 2:
                    $field[] = Db::raw("FLOOR((a.create_time - UNIX_TIMESTAMP('".$params['monthdate']."-01')) / (7 * 24 * 60 * 60)) AS x");
                    if($params['monthdate']){
                        $dateTime = new \DateTime($params['monthdate']);
                        $dateTime->modify('last day of this month');
                        $where[] = ['a.create_time','between',[strtotime($params['monthdate'].'-01'),strtotime($dateTime->format('Y-m-d'))+86400-1]];
                    }
                    $group[] = Db::raw("x");
                    $order[] = 'x';
                    break;
                case 3:
                    $field[] = Db::raw("FROM_UNIXTIME(a.create_time,'%m') AS x");
                    if($params['yeardate']){
                        $where[] = ['a.create_time','between',[strtotime($params['yeardate'].'-01-01'),strtotime($params['yeardate'].'-12-31')+86400-1]];
                    }
                    $group[] = Db::raw("x");
                    $order[] = 'x';
                    break;
            }
        }
        $field[] = Db::raw("COUNT(a.id) AS y");
        return [
            'where' => $where,
            'field' => $field,
            'group' => $group,
            'order' => $order
        ];
    }



    public static function serviceOrdersTotal(array $params = [])
    {
        $res = [
            'total_flow' => 0,
            'proxy_order_flow' => 0,
            'free_class_flow' => 0,
            'goods_category_flow' => [],
        ];
        list($start_time,$end_time) = $params['start_end_time'];
        $time = [strtotime($start_time), strtotime($end_time)+86400-1];

        $res['total_flow'] =   Db::name('service_work')
            ->field([Db::raw('SUM(work_total) as work_total')])
            ->where([['service_status','=',3],['approval','=',1]])
            ->where([['create_time', 'between', $time]])
            ->select()->toArray()[0]['work_total'];

        $res['proxy_order_flow'] =   Db::name('property_order')->alias('a')
            ->field([Db::raw('SUM(b.work_total) as work_total')])
            ->leftJoin('service_work b', 'a.work_id = b.id')
            ->where([['b.service_status','=',3],['b.approval','=',1]])
            ->where([['b.create_time', 'between', $time]])
            ->select()->toArray()[0]['work_total'];


        $res['free_class_flow'] = Db::name('master_worker_commission_notice')->alias('n')
            ->leftJoin('master_worker mw', 'n.master_worker_id = mw.id')
            ->leftJoin('service_work sw', 'n.master_worker_id = sw.master_worker_id AND sw.create_time >= n.signing_date AND sw.create_time <= n.signing_date_end')
            ->field([Db::raw("SUM(CASE WHEN sw.service_status = 3 THEN sw.work_total ELSE null END) AS work_total")])
            ->where([['sw.create_time', 'between', $time]])
            ->select()->toArray()[0]['work_total'];


        $goodsCategoryObj = GoodsCategory::where(['status'=>1])->order(['pid' => 'asc','weigh' => 'desc', 'id' => 'desc'])->select();
        $res['goods_category_flow'] = Db::name('service_work')
            ->field(Db::raw("goods_category_ids,goods_category_id,SUM(work_total) as work_total_flow"))
            ->where('service_status',3)
            ->where([['create_time', 'between', $time]])
            ->group('goods_category_id')
            ->order('work_total_flow', 'desc')
            ->select()->toArray();
        foreach ($res['goods_category_flow'] as &$item) {
            $item['goods_category_ids'] = array_map("intval",json_decode($item['goods_category_ids'],true));
            $item['goods_category_ids_str'] = implode(' / ',$goodsCategoryObj->whereIn('id', $item['goods_category_ids'])->column('name'));
        }
        return $res;
    }





}