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', []);
        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');
        }

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

        $where = "";
        if (!empty($days)) {
            $where .= " where di.data_income_addtimes between {$days[0]} and {$days[1]}";
        }

        $totalSql = "
            select count(*) as total from (
                select 
                    data_income_classify 
                from app_data_income_{$month} as di
                {$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
            from app_data_income_{$month} as di 
                left join app_goods as g on JSON_EXTRACT(di.join_data_income_object_json,'$.sheet[0].goods_id') = g.goods_id 
                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', []);
        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]);
        } else {
            $month = date('Ym');
        }

        $statisticsSql = "
            select 
                SUM(JSON_EXTRACT(join_data_income_object_json,'$.sheet[0].order_sheet_num')) as num,SUM(data_income_amount) as amount
            from app_data_income_{$month}
        ";
        $statistics = [];
        $category = ['all', 'entity', 'service', 'package'];
        foreach ($category as $item) {
            $execSql = $statisticsSql;
            if ($item == 'entity') {
                $execSql .= " where data_income_classify in('GOODS')";
            } elseif ($item == 'service') {
                $execSql .= " where data_income_classify in('MEALS','CHNMED')";
            } elseif ($item == 'package') {
                $execSql .= " where data_income_classify = 'PACKAGE'";
            }
            $res = Db::select($execSql . ' limit 1');
            $statistics[$item] = $res[0];
        }

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