phpstock/app/command/NowReportHandle.php

<?php
declare (strict_types=1);

namespace app\command;

use app\admin\model\Test1;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Cache;
use think\facade\Db;

//处理报表预约记录，生成报表文件
class NowReportHandle extends Command
{
    //redis队列的key，在ReportReserve控制器中也有定义，要同步修改(轻易不要修改)
    private $key = 'wanyuhengtong_report_reserve';

    //各个报表的导出字段(这里的字段要和下面的方法中查询字段个数对应)
    private $field = [
        'A' => ['退货单号', '流程进度', '退货发起日期', '公司名称', '退货人', '退货数量', '退货销售货款', '退货备注', '客户名称', '客户编码', '一级组织', '二级组织', '三级组织', '平台名称', 'PO编号', '确认单编号', '确认单类型', '确认单下单时间', '业务人员', '确认单产品编号', '产品名称', '产品编码', '一级分类'],
        'B' => [],
        'C' => [],
    ];

    protected function configure()
    {
        // 指令配置
        $this->setName('handleData')
            ->setDescription('处理报表预约记录，生成报表文件');
    }

    //处理报表预约记录，生成报表文件
    protected function execute(Input $input, Output $output)
    {

        try {


            $pk_id = Cache::store('redis')->handler()->rpop($this->key);
            if ($pk_id) {
                //有数据，开始处理

                //查询预约记录
                $rs = Db::name('report_form_reserve')
                    ->field('id,name,code,start_time,end_time')
                    ->where(['id' => $pk_id, 'status' => 1])
                    ->find();

                if ($rs) {
                    //状态改为处理中
                    Db::name('report_form_reserve')
                        ->where(['id' => $pk_id, 'status' => 1])//status==1 待处理
                        ->update(['status' => 2, 'updatetime' => date('Y-m-d H:i:s')]);//status==2 处理中

                    //处理数据的方法
                    switch ($rs['code']) {
                        case 'A':
                            $res = $this->A($rs['start_time'], $rs['end_time']);
                            break;
                    }

                    $file = excelSaveFile($res, $this->field[$rs['code']], $rs['name'] . date('YmdHis'));

                    Db::name('report_form_reserve')
                        ->where(['id' => $pk_id, 'status' => 2])//status==2 处理中
                        ->update([
                            'status' => 3, //status==3 处理完成
                            'down_url' => $file,
                            'updatetime' => date('Y-m-d H:i:s'),
                            'expiretime' => date('Y-m-d H:i:s', strtotime('+1 month'))
                        ]);

                    $output->writeln('【' . $pk_id . '】该预约记录处理成功');


                } else $output->writeln('没有查询到该预约记录');

            } else  $output->writeln('没有可供处理的报表预约记录');

        } catch (\Exception $exception) {
            $output->writeln($exception->getMessage() . '|' . $exception->getFile() . '|' . $exception->getLine());
        }

    }


    //数据处理脚本，每个报表一个方法

    //退货台账-业务口径
    private function A(string $start_date = '', string $end_date = '')
    {

        $data = Db::name('sale_return')
            ->alias('sr')
            ->field('sr.returnCode 退货单号,sr.status 流程进度,sr.addtime 退货发起日期,sup.name 公司名称,sr.apply_name 退货人,sr.num 退货数量,sr.total_fee 退货销售货款,sr.remark 退货备注,c.companyName 客户名称,or.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台名称,s.poNo PO编号,sr.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,s.apply_name 业务人员,sr.orderCode 确认单产品编号,sr.good_name 产品名称,sr.good_code 产品编码,"" 一级分类,s.cat_id,c.itemid')
            ->leftJoin('platform p', 'p.id = sr.platform_id')
            ->leftJoin('order_return or', 'or.orderCode = sr.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo = or.customer_code')
            ->leftJoin('sale s', 's.orderCode = sr.orderCode')
            ->leftJoin('supplier sup', 'sup.code = s.supplierNo')
            ->where('sr.is_del', 0)
            ->whereBetween('sr.addtime', [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
            ->cursor();

        $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过'];
        $all_sale_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售',];
        $list = [];
        foreach ($data as $value) {

            $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : '';
            $value['确认单类型'] = isset($all_sale_order_type[$value['确认单类型']]) ? $all_sale_order_type[$value['确认单类型']] : '';

            $top = made($value['cat_id']);
            $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';

            if (!empty($value['itemid'])) {
                $customer_org1 = get_top_customer_org($value['itemid']);

                foreach ($customer_org1 as $vv) {
                    switch ($vv['level']) {
                        case 1:
                            $value['一级组织'] = $vv['name'];
                            break;
                        case 2:
                            $value['二级组织'] = $vv['name'];
                            break;
                        case 3:
                            $value['三级组织'] = $vv['name'];
                            break;
                    }
                }
            }

            unset($value['cat_id']);
            unset($value['itemid']);

            yield $list[] = $value;
        }

        return $list;

    }

    //库存预警汇总表
    private function B(string $start_date = '', string $end_date = '')
    {

        $data = Db::name('sale_return')
            ->alias('sr')
            ->field('sr.returnCode 退货单号,sr.status 流程进度,sr.addtime 退货发起日期,sup.name 公司名称,sr.apply_name 退货人,sr.num 退货数量,sr.total_fee 退货销售货款,sr.remark 退货备注,c.companyName 客户名称,or.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台名称,s.poNo PO编号,sr.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,s.apply_name 业务人员,sr.orderCode 确认单产品编号,sr.good_name 产品名称,sr.good_code 产品编码,"" 一级分类,s.cat_id,c.itemid')
            ->leftJoin('platform p', 'p.id = sr.platform_id')
            ->leftJoin('order_return or', 'or.orderCode = sr.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo = or.customer_code')
            ->leftJoin('sale s', 's.orderCode = sr.orderCode')
            ->leftJoin('supplier sup', 'sup.code = s.supplierNo')
            ->where('sr.is_del', 0)
            ->whereBetween('sr.addtime', [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
            ->cursor();

        $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过'];
        $all_sale_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售',];
        $list = [];
        foreach ($data as $value) {

            $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : '';
            $value['确认单类型'] = isset($all_sale_order_type[$value['确认单类型']]) ? $all_sale_order_type[$value['确认单类型']] : '';

            $top = made($value['cat_id']);
            $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';

            if (!empty($value['itemid'])) {
                $customer_org1 = get_top_customer_org($value['itemid']);

                foreach ($customer_org1 as $vv) {
                    switch ($vv['level']) {
                        case 1:
                            $value['一级组织'] = $vv['name'];
                            break;
                        case 2:
                            $value['二级组织'] = $vv['name'];
                            break;
                        case 3:
                            $value['三级组织'] = $vv['name'];
                            break;
                    }
                }
            }

            unset($value['cat_id']);
            unset($value['itemid']);

            yield $list[] = $value;
        }

        return $list;

    }


}