wanyue_app/app/admin/controller/finance/GoodsSalesController.php

<?php

namespace app\admin\controller\finance;

use app\model\OrderSheet;
use app\model\SysCategory;
use support\Db;
use support\Request;

class GoodsSalesController
{

    public function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $days = $request->get('days', []);
        $classify = $request->get('classify','');
        $categoryId = $request->get('category_id','');
        $goodsName = $request->get('goods_name','');
        $premisesId = $request->get('premises_id','');
        $payType = $request->get('pay_type','');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
            if (date('m', $days[0]) != date('m', $days[1])) {
                return json_fail('暂不支持跨月查询');
            }

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }
        
        $categorySearchIds = '';
        if (!empty($categoryId)){
            $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
            $categorySearchPath .= '#'. $categoryId.'#';
            
            $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
            $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
            $categorySearchIds = implode(',',$categorySearchIds);
        }

        $recordStart = ($page - 1) * 20;

        $where = "where 1";
        if (!empty($days)) {
            $where .= " and di.data_income_addtimes between {$days[0]} and {$days[1]}";
        }
        if (!empty($classify)){
            $where .= " and di.data_income_classify = '{$classify}'";
        }
        if (!empty($categorySearchIds)){
            $where .= " and g.join_goods_category_id in({$categorySearchIds})";
        }
        if (!empty($goodsName)){
            $where .= " and g.goods_name like '%{$goodsName}%' ";
        }
        if (!empty($premisesId)){
            $where .= " and di.join_data_income_dept_id = {$premisesId}";
        }
        if (!empty($payType)){
            $where .= " and di.data_income_pay_type = '{$payType}'";
        }

        $totalSql = "
            select count(*) as total from (
                select 
                    data_income_classify 
                from app_data_income_{$month} as di
                    left join app_goods as g on REPLACE(JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].goods_id'),'\"','') = g.goods_id 
                    {$where}
                group by JSON_EXTRACT(join_data_income_object_json,'$.sheet[0].goods_id'), JSON_EXTRACT(join_data_income_object_json,'$.sheet[0].goods_sku_id'),data_income_classify
            ) COUNT
        ";
        $total = Db::select($totalSql);

        $sql = "select 
            g.join_goods_category_id,di.data_income_classify,g.goods_id,g.goods_name,g.goods_classify, gs.goods_sku_specs_json,gs.goods_sku_id,gs.goods_sku_sales_price,MAX(di.data_income_addtimes) as addtimes,
            SUM(JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].order_sheet_num')) as num,SUM(di.data_income_amount) as amount,COUNT(*) as count
            from app_data_income_{$month} as di 
                left join app_goods as g on REPLACE(JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].goods_id'),'\"','') = CAST(g.goods_id AS CHAR)
                left join app_goods_sku as gs on JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].goods_sku_id') = gs.goods_sku_id 
                {$where} 
            group by g.goods_id,gs.goods_sku_id,di.data_income_classify 
            order by addtimes desc 
            limit " . $recordStart . ',' . $pageSize;
        $rows = Db::select($sql);
        $rows = json_decode(json_encode($rows), true);

        $categoryIds = array_unique(array_filter(array_column($rows, 'join_goods_category_id')));
        $categorys = SysCategory::whereIn('category_id', $categoryIds)->pluck('category_name', 'category_id')->toArray();
        foreach ($rows as &$row) {
            if (!empty($row['join_goods_category_id']) && isset($categorys[$row['join_goods_category_id']])) {
                $row['category_name'] = $categorys[$row['join_goods_category_id']];
            }
            if (!empty($row['goods_sku_specs_json'])) {
                $specsJson = json_decode($row['goods_sku_specs_json'], true);
                $skuTitle = '';
                foreach ($specsJson as $k => $value) {
                    $value = is_array($value) ? $value[0] : $value;
                    $skuTitle .= $k . ':' . $value . ';';
                }
                $row['sku'] = rtrim($skuTitle, ';');
            }
        }

        return json_success('', [
            'page' => $page,
            'pageSize' => $pageSize,
            'total' => $total[0]->total,
            'rows' => $rows
        ]);
    }

    public function statistics(Request $request)
    {
        $days = $request->get('days', []);
        $classify = $request->get('classify','');
        $categoryId = $request->get('category_id','');
        $goodsName = $request->get('goods_name','');
        $premisesId = $request->get('premises_id','');
        $payType = $request->get('pay_type','');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
            if (date('m', $days[0]) != date('m', $days[1])) {
                return json_fail('暂不支持跨月查询');
            }

            $month = date('Ym', $days[0]);
            $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
        } else {
            $month = date('Ym');
        }
        
        $categorySearchIds = '';
        if (!empty($categoryId)){
            $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
            $categorySearchPath .= '#'. $categoryId.'#';
            
            $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
            $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
            $categorySearchIds = implode(',',$categorySearchIds);
        }

        $where = "where 1";
        if (!empty($days)) {
            $where .= " and di.data_income_addtimes between {$days[0]} and {$days[1]}";
        }
        if (!empty($classify)){
            $where .= " and di.data_income_classify = '{$classify}'";
        }
        if (!empty($categorySearchIds)){
            $where .= " and g.join_goods_category_id in({$categorySearchIds})";
        }
        if (!empty($goodsName)){
            $where .= " and g.goods_name like '%{$goodsName}%' ";
        }
        if (!empty($premisesId)){
            $where .= " and di.join_data_income_dept_id = {$premisesId}";
        }
        if (!empty($payType)){
            $where .= " and di.data_income_pay_type = '{$payType}'";
        }

        $statisticsSql = "
            select 
                SUM(JSON_EXTRACT(join_data_income_object_json,'$.sheet[0].order_sheet_num')) as num,SUM(data_income_amount) as amount,COUNT(*) as count 
            from app_data_income_{$month} as di
                left join app_goods as g on REPLACE(JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].goods_id'),'\"','') = g.goods_id 
                {$where}
        ";
        $statistics = [];
        $category = ['all', 'entity', 'service', 'package'];
        foreach ($category as $item) {
            $execSql = $statisticsSql;
            if ($item == 'entity') {
                $execSql .= " and data_income_classify in('GOODS')";
            } elseif ($item == 'service') {
                $execSql .= " and data_income_classify in('MEALS','CHNMED')";
            } elseif ($item == 'package') {
                $execSql .= " and data_income_classify = 'PACKAGE'";
            }
            $res = Db::select($execSql . ' limit 1');
            $statistics[$item] = $res[0];
        }

        return json_success('', $statistics);
    }
}