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

<?php

namespace app\admin\controller\finance;

use app\admin\service\goods\GoodsSkuService;
use app\model\DataInout;
use app\model\Goods;
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', '');
        $payType = $request->get('pay_type', '');
        if (!empty($days)) {
            $days[0] = strtotime($days[0]);
            $days[1] = strtotime($days[1]);
        }
        $searchGoodsIds = [];
        if (!empty($classify)) {
            if ($classify == 'SERVICE') {
                $classify = ['SERVICE', 'CHNMED', 'CHNNCD'];
            } else {
                $classify = [$classify];
            }
            $searchGoodsIds = Goods::whereIn('goods_classify', $classify)->pluck('goods_id')->toArray();
        }
        if (!empty($categoryId)) {
            $categorySuperPath = SysCategory::where('category_id', $categoryId)->value('category_super_path');
            if (empty($categorySuperPath)) {
                $categorySuperPath = '#' . $categoryId . '#';
            }
            $searchCategoryIds = SysCategory::where('category_super_path', 'like', $categorySuperPath . '%')->pluck('category_id')->toArray();
            $searchCategoryIds[] = $categoryId;
            // 按产品分类筛出来的ID
            $oldSearchGoodsIds = $searchGoodsIds;

            $searchGoodsIds = Goods::whereIn('join_goods_category_id', $searchCategoryIds)->pluck('goods_id')->toArray();
            if (!empty($oldSearchGoodsIds)){
                $searchGoodsIds = array_intersect($searchGoodsIds,$oldSearchGoodsIds);
            }
        }

        $dataInOuts = DataInout::where('data_inout_status', 'VALID')
            ->where('data_inout_classify', 'IN')
            ->whereBetween('data_inout_addtimes', $days)
            ->when(!empty($payType), function ($query) use ($payType) {
                $query->where('data_inout_pay_type', $payType);
            })->when(!empty($goodsName), function ($query) use ($goodsName) {
                $query->where('join_data_inout_object_json', 'like', "'" . $goodsName . "'");
            })->select('join_data_inout_object_json')
            ->get()
            ->toArray();

        $goods = [];
        $goodsIds = [];
        $statistics = [
            'total' => 0,
            'amount' => 0,
            'goods' => ['total' => 0, 'amount' => 0],
            'service' => ['total' => 0, 'amount' => 0],
            'chnmed' => ['total' => 0, 'amount' => 0],
            'chnncd' => ['total' => 0, 'amount' => 0],
            'package' => ['total' => 0, 'amount' => 0],
            'meals' => ['total' => 0, 'amount' => 0],
            'dishes' => ['total' => 0, 'amount' => 0],
            'vip' => ['total' => 0, 'amount' => 0],
            'recharge' => ['total' => 0, 'amount' => 0],
            'combine' => ['total' => 0, 'amount' => 0],
            'partner' => ['total' => 0, 'amount' => 0],
            'referrer' => ['total' => 0, 'amount' => 0],
        ];
        foreach ($dataInOuts as $dataInOut) {
            if (empty($dataInOut['join_data_inout_object_json'])) {
                continue;
            }
            $intoObjectJson = json_decode($dataInOut['join_data_inout_object_json'], true);
            if (empty($intoObjectJson['order'])) {
                continue;
            }
            foreach ($intoObjectJson['order'] as $order) {
                if (empty($order['goods'])) {
                    continue;
                }
                foreach ($order['goods'] as $good) {
                    $goodKey = $good['goods_id'];
                    if (!empty($good['goods_sku_id'])) {
                        $goodKey .= '_' . $good['goods_sku_id'];
                    }
                    if (!key_exists($goodKey, $goods)) {
                        $goodsIds[] = $good['goods_id'];
                        $goods[$goodKey] = [
                            'goods_id' => $good['goods_id'],
                            'goods_name' => $good['goods_name'],
                            'goods_sku_id' => $good['goods_sku_id'] ?? '',
                            'order_sheet_num' => floatval($good['order_sheet_num']),
                            'order_sheet_pay' => floatval($good['order_sheet_pay'])
                        ];
                    } else {
                        $goods[$goodKey]['order_sheet_num'] += $good['order_sheet_num'];
                        $goods[$goodKey]['order_sheet_pay'] += $good['order_sheet_pay'];
                    }

                    $statistics['total'] = round($good['order_sheet_num'] + $statistics['total'], 2);
                    $statistics['amount'] = round($good['order_sheet_pay'] + $statistics['amount'], 2);

                    $orderClassify = strtolower($order['classify']);
                    $statistics[$orderClassify]['total'] = round($good['order_sheet_num'] + $statistics[$orderClassify]['total'], 2);
                    $statistics[$orderClassify]['amount'] = round($good['order_sheet_pay'] + $statistics[$orderClassify]['amount'], 2);
                }
            }
        }
        $statistics['service']['total'] = $statistics['service']['total'] + $statistics['chnmed']['total'] + $statistics['chnncd']['total'];
        $statistics['service']['amount'] = $statistics['service']['amount'] + $statistics['chnmed']['total'] + $statistics['chnncd']['amount'];

        // 交集
        if (!empty($classify) || !empty($categoryId)) {
            $goodsIds = array_intersect($goodsIds, $searchGoodsIds);

            $goods = array_filter($goods, function ($item) use ($goodsIds) {
                return in_array($item['goods_id'], $goodsIds);
            });
        }

        // 排序
        usort($goods, function ($a, $b) {
            return $b['order_sheet_num'] <=> $a['order_sheet_num'];
        });
        // 分页

        $total = count($goods);
        $start = ($page - 1) * $pageSize;
        $rows = array_slice($goods, $start, $pageSize);
        foreach ($rows as &$row) {
            $rowGoods = Goods::with([
                'category',
                'skuOne' => function ($query) use ($row) {
                    $query->where('goods_sku_id', $row['goods_sku_id']);
                }
            ])->where('goods_id', $row['goods_id'])->first();
            $row['goods_classify'] = !empty($rowGoods->goods_classify) ? $rowGoods->goods_classify : '';
            $row['goods_sales_price'] = !empty($rowGoods->goods_sales_price) ? $rowGoods->goods_sales_price : '';
            $row['category'] = !empty($rowGoods->category) ? $rowGoods->category : '';
            if (!empty($rowGoods->skuOne)) {
                $row['goods_sku_title'] = GoodsSkuService::getSkuTitle($rowGoods->skuOne->goods_sku_specs_json);
                $row['goods_sku_sales_price'] = $rowGoods->skuOne->goods_sku_sales_price;
            }

            $row['order_sheet_pay'] = sprintf('%.2f', $row['order_sheet_pay']);
        }


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

    }

    public function listOld(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);
    }
}