phpstock/app/command/NowReportHandle.php

<?php
declare (strict_types=1);

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Exception;
use think\facade\Cache;
use think\facade\Db;

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

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

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

        try {

            $info = Cache::store('redis')->handler()->rpop($this->key);
            if ($info) {

                $info = json_decode($info, true);

                //不同的方法处理不同的脚本
                switch ($info['code']) {
                    //退货台账-业务口径
                    case 'A':
                        $res = $this->A($info['start'], $info['end']);
                        break;

                    //库存日报及预警汇总表
                    case 'B':
                        $res = $this->B($info['start'], $info['end']);
                        break;

                    //退货台账
                    case 'C':
                        $res = $this->C($info['start'], $info['end']);
                        break;

                    //
                    case 'D':
                        $res = $this->D($info['start'], $info['end']);
                        break;

                    default:
                        throw new Exception('暂不支持这个报表');
                }

                $file = excelSaveFile($res, $info['name'] . date('YmdHis'));

                Db::name('exec')
                    ->where(['id' => $info['id'], 'status' => 1])//status==1 待处理
                    ->update([
                        'status' => 2, //status==2 处理完成
                        'down_url' => $file,
                        'updatetime' => date('Y-m-d H:i:s'),
                        'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day'))
                    ]);

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

            } 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 退货发起日期,b.company 公司名称, ci1.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 确认单下单时间,ci2.name 业务人员所在部门,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('business b', 'b.companyNo = s.supplierNo')
            ->leftJoin('depart_user du1', 'du1.uid = sr.apply_id')
            ->leftJoin('company_item ci1', 'ci1.id = du1.itemid')
            ->leftJoin('depart_user du2', 'du2.uid = s.apply_id')
            ->leftJoin('company_item ci2', 'ci2.id = du2.itemid')
            ->where('sr.is_del', 0)
            ->whereBetween('sr.addtime', [$start_date, $end_date])
            ->order('sr.addtime', 'desc')
            ->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('good_stock')
            ->alias('gs')
            ->field('b.company 公司名称,pi.updatetime 最近一次入库时间,pi.apply_name 采购员,gs.spuCode 产品编码,"" 一级分类,gb.good_name 产品名称,"" 成本单价,gs.wait_out_stock 待出库量,gs.wait_in_stock 待入库量,gs.usable_stock 可用库存数量,gs.total_stock 当前库存量,"" 可用库存金额,"" 当前库存金额,"" 保质期时间,"" 库存天数,po.supplier_name 供应商名称,wi.name 仓库名称,gb.cat_id')
            ->leftJoin('purchease_in pi', 'pi.wsm_code = gs.wsm_code AND pi.status=4')
            ->leftJoin('purchease_order po', 'po.cgdNo = pi.cgdNo')
            ->leftJoin('warehouse_info wi', 'wi.wsm_code = gs.wsm_code')
            ->leftJoin('business b', 'b.companyNo = po.companyNo')
            ->leftJoin('good_basic gb', 'gb.spuCode = gs.spuCode')
            ->where('gs.is_del', 0)
            ->whereBetween('gs.addtime', [$start_date, $end_date])
            ->order('gs.addtime', 'desc')
            ->cursor();

        $list = [];
        foreach ($data as $value) {

            $value['一级分类'] = implode('/', array_column(made($value['cat_id']), 'name'));

            $value['成本单价'] = Db::name('good_nake')
                ->where('spuCode', $value['产品编码'])
                ->order('min_num', 'asc')
                ->value('nake_fee', '');

            $value['可用库存金额'] = bcmul((string)$value['可用库存数量'] ?? '0', (string)$value['成本单价'] ?? '0', 2);
            $value['当前库存金额'] = bcmul((string)$value['当前库存量'] ?? '0', (string)$value['成本单价'] ?? '0', 2);
            if (!empty($value['最近一次入库时间'])) {
                $value['保质期时间'] = date('Y-m-d H:i:s', strtotime($value['最近一次入库时间']) + 31536000);//365天之后
                $value['库存天数'] = bcdiv((string)(time() - strtotime($value['最近一次入库时间'])), (string)(24 * 3600));//365天之后
            }

            unset($value['cat_id']);

            yield $list[] = $value;
        }

        return $list;

    }

    //退货台账
    private function C(string $start_date = '', string $end_date = '')
    {

        $data = Db::name('sale_return')
            ->alias('sr')
            ->field('DATE_FORMAT(sr.addtime,"%Y") 年,DATE_FORMAT(sr.addtime,"%m") 月,DATE_FORMAT(sr.addtime,"%d") 日,b.company 公司名称,sr.returnCode 退货单号,sr.status 流程进度,ci2.name 业务人员所在部门,s.apply_name 业务人员,sr.orderCode 订单编号,p.platform_name 平台名称,s.workNo 平台订单号,"" 一级组织,"" 二级组织,"" 三级组织,c.companyName 客户名称,gp.spuCode 产品编码,sr.good_name 产品名称,"" 一级分类,"" 二级分类,"" 三级分类,cat.fund_code 财务核算码,u.unit 单位,sr.num 退货数量,s.sale_price 销售单价,round(sale_price*sr.num,2) 退货销售货款,sr.remark 退货备注,on.cgdNo 采购单号,po.cgder 采购员,po.nake_fee 成本裸价,po.pakge_fee 包装费,po.delivery_fee 物流费,po.cert_fee 证书费,po.mark_fee 加标费,po.open_fee 开模费,po.diff_weight 工差,po.diff_fee 采购工差金额,po.good_price 采购单价,round(sr.num*po.good_price-po.diff_fee,2) 退货采购货款,gb.tax 采购税点,supp.name 供应商名称,wi.name 库房名称,pi.sendtype 发货方式,supp.pay_type 结算方式,c.itemid,s.cat_id')
            ->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('business b', 'b.companyNo = s.supplierNo')
            ->leftJoin('depart_user du2', 'du2.uid = s.apply_id')
            ->leftJoin('company_item ci2', 'ci2.id = du2.itemid')
            ->leftJoin('good_platform gp', 'gp.skuCode = s.skuCode')
            ->leftJoin('good_basic gb', 'gb.spuCode = gp.spuCode')
            ->leftJoin('unit u', 'u.id = gb.good_unit')
            ->leftJoin('order_num on', 'on.orderCode =sr.orderCode')
            ->leftJoin('cat cat', 'cat.id =s.cat_id')
            ->leftJoin('purchease_order po', 'po.cgdNo =on.cgdNo')
            ->leftJoin('purchease_in pi', 'pi.cgdNo =on.cgdNo')
            ->leftJoin('supplier supp', 'supp.code =s.supplierNo')
            ->leftJoin('warehouse_info wi', 'wi.wsm_code =po.wsm_code')
            ->where('sr.is_del', 0)
            ->whereBetween('sr.addtime', [$start_date, $end_date])
            ->order('sr.addtime', 'desc')
            ->cursor();

        $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过'];
        $all_sendtype = [1 => '公司自提', 2 => '供应商包邮'];
        $list = [];
        foreach ($data as $value) {

            $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : '';
            $value['发货方式'] = isset($all_sendtype[$value['发货方式']]) ? $all_sendtype[$value['发货方式']] : '';

            $top = made($value['cat_id']);
            $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';
            $value['二级分类'] = isset($top[1]['name']) ? $top[1]['name'] : '';
            $value['三级分类'] = isset($top[2]['name']) ? $top[2]['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;

    }

    public function D($start,$end){
        $list =Db::name("order_out")->alias('woo')->leftJoin("sale a","a.orderCode=woo.orderCode")
            ->leftJoin("order_back wor","wor.outCode=woo.outCode and status=4")
            ->leftJoin("order_num won","won.orderCode=a.orderCode")
            ->leftJoin("purchease_order wpo","wpo.cgdNo=won.cgdNo")
            ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode")
            ->leftJoin("good_zixun wgz","wgz.spuCode=wpo.spuCode")
            ->leftJoin("supplier ws","ws.code=wpo.supplierNo")
            ->where('woo.status',">=", 2)
            ->whereBetween('woo.addtime', [$start, $end])
            ->field(" year(`a`.`addtime`) AS `年`,month(`a`.`addtime`) AS `月`,dayofmonth(`a`.`addtime`) AS `日`,a.supplierNo
             as '公司名称', a.apply_id '业务部门名称' ,a.apply_name as '业务人员',a.order_type as '订单类型',a.good_type as '商品类型',woo.outCode '发货编号',
             `woo`.`send_num` AS `本次发货数量`,
            `woo`.`sendtime` AS `本次发货时间`,
            `woo`.`orderCode` AS `确认单编号`,
            `a`.`good_code` AS `商品编号`,'' as '一级分类','' as '二级分类',a.cat_id as '三级分类',
            '' as '财务核算编码',a.good_name as '商品名称', ifnull( `wgb`.`good_unit`, `wgz`.`good_unit` ) as '单位',
            `a`.`good_num` AS `下单数量`,
            `a`.`total_price` AS `销售货款`,
            `wpo`.`cgdNo` AS `采购单单号`,
            `wpo`.`cgder` AS `采购员`,
            `wpo`.`nake_fee` AS `采购裸价`,
            `wpo`.`mark_fee` AS `加标费`,
            `wpo`.`pakge_fee` AS `包装费`,
            `wpo`.`cert_fee` AS `证书费`,
            `wpo`.`open_fee` AS `开模费`,
            `wpo`.`teach_fee` AS `工艺费`,
            `wpo`.`delivery_fee` AS `物流费`,
            `wpo`.`good_price` AS `成本合计`,
            `wpo`.`total_fee` AS `采购货款`,
            ifnull( `wgb`.`tax`, `wgz`.`tax` ) AS `采购税点`,
            `ws`.`name` AS `供应商名称`,
             `wgb`.`is_stock`  as '是否库存品'
              if (`wgb`.`is_stock`=1,wpo.bkcode,wpo.cgdNo) as '发货方式',
              ifnull( `wor`.`thNo`, '' ) AS `退货编号`,
		ifnull( `wor`.`return_num`, '' ) AS `退货数量`,
		round(( ifnull( `wor`.`return_num`, 0 ) * `a`.`sale_price` ), 2 ) as '退货金额',
		'' as '售前退货数量',
		`a`.`sale_price`  as '售前退货金额'
              ")->cursor();
            $order_tyepe=["库存销售",'非库存销售','咨询销售','项目销售','平台库存销售','平台非库存销售'];
            $good_type=["常规商品",'赠品','样品'];
            foreach($list as $value){
                $value['公司名称'] = Db::name("business")->where(["companyNo"=>$value['公司名称']])->value('company','');
                $value['业务部门名称'] = Db::name("depart_user")->alias("a")->leftJoin("company_item ci","a.itemid=ci.id")
                    ->where(["a.uid"=>$value['业务部门名称'],"status"=>1,"is_del"=>0])->value('ci.name','');
                $value['订单类型'] = $order_tyepe[ $value['订单类型']-1];
                $value['商品类型'] = $good_type[ $value['商品类型']-1];
                $cat =made($value['三级分类']);
                $value['财务核算编码'] =Db::name("cat")->where(["id"=>$value['三级分类']])->value("fund_code",'');
                $value['一级分类']=isset($cat[0]['name'])?$cat[0]['name']:"";
                $value['二级分类']=isset($cat[1]['name'])?$cat[1]['name']:"";
                $value['三级分类']=isset($cat[2]['name'])?$cat[2]['name']:"";
                $value['单位']=Db::name("unit")->where(["id"=>$value['单位']])->value("unit",'');
                if($value['is_stock']==1){
                    $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo")
                        ->where(["bkcode"=>$value['发货方式'],"order_type"=>[1,5]])->order("b.addtime desc")->value("b
                        .sendtype",'');
                }else{
                    $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo")
                        ->where(["cgdNo"=>$value['发货方式']])->order("b.addtime desc")->value("b.sendtype",'');
                }
                $value['发货方式'] = $sendtype=1 ? '包邮':$sendtype=2 ? '自提':"";
                $value['售前退货数量'] = Db::name("sale_return")->where(["orderCode"=>$value['确认单编号']])->sum('num');
                $value['售前退货金额'] =round($value['售前退货数量']* $value['售前退货金额'],2);
                yield $list[] = $value;
            }
            return $list;
        }

}