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 = 'cxreport';

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

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

        try {
            ini_set("memory_limit", "516M");
            $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;
                    //【财务报表】库存品入库明细
                    case 'E':
                        $res = $this->E($info['start'], $info['end']);
                        break;
                    //【财务报表】库存品出库明细
                    case 'F':
                        $res = $this->F($info['start'], $info['end']);
                        break;
                    //【销售】订单表导出
                    case 'G':
                        $res = $this->G($info['start'], $info['end']);
                        break;
                    //【库存报表】-库存日报及预警汇总表
                    case 'H':
                        $res = $this->H($info['start'], $info['end']);
                        break;
                    //【库存报表】-出入库明细
                    case 'I':
                        $res = $this->I($info['start'], $info['end']);
                        break;
                    //【库存报表】-库存品入库明细
                    case 'J':
                        $res = $this->J($info['start'], $info['end']);
                        break;
                    //【库存报表】-库存品出库明细
                    case 'K':
                        $res = $this->K($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('order_back')
            ->alias('ob')
            ->field('ob.thNo 退货单号,ob.status 流程进度,ob.addtime 退货发起日期,"" 退货人所在部门,ob.apply_name 退货人,ob.return_num 退货数量,ob.total_fee 退货销售货款,ob.remark 退货备注,c.companyName 客户名称,ob.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台名称,s.poNo PO编号,s.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,"" 业务人员所在部门,ob.cgder 业务人员,s.good_code 确认单产品编号,ob.good_name 产品名称,s.skuCode 产品编码,"" 一级分类,0 售前记录总数,ob.apply_id,ob.cgderid,s.cat_id,c.itemid')
            ->leftJoin('sale s', 's.orderCode=ob.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo=ob.customer_code AND c.is_del=0')
            ->leftJoin('platform p', 'p.id=ob.platform_id')
            ->where(['ob.is_del' => 0, 'ob.status' => 4])
            ->whereBetween('ob.addtime', [$start_date, $end_date])
            ->cursor();

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

            $value['流程进度'] = isset($all_status[$value['流程进度']]) ? $all_status[$value['流程进度']] : '';

            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;
                    }
                }
            }

            $value['退货人所在部门'] = get_company_name_by_uid($value['apply_id']);
            $value['业务人员所在部门'] = get_company_name_by_uid($value['cgderid']);
            $value['确认单类型'] = isset($all_order_type[$value['确认单类型']]) ? $all_order_type[$value['确认单类型']] : '';
            $top = made($value['cat_id']);
            $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';
            $value['售前记录总数'] = Db::name('sale_return')->where(['orderCode' => $value['确认单编号'], 'is_del' => 0, 'status' => 5])->count('id');
            unset($value['cat_id']);
            unset($value['itemid']);
            unset($value['apply_id']);
            unset($value['cgderid']);

            yield $list[] = $value;
        }

        return $list;

    }

    //【财务报表】库存日报及预警汇总表
    private function B(string $start_date = '', string $end_date = '')
    {
        $data = Db::name('good_stock')
            ->alias('wgs')
            ->field("	wb.company '公司名称',
							wpo.bkcode as '备库单号',
							wpo.cgdNo as '采购单号',
							wp.apply_id as '申请部门',
							wp.apply_name as '申请人',
						    cat_id '一级分类',
							wgb.good_name '产品名称',
							wp.good_num '备库数量',
							wgs.spuCode '产品编号',
							wpo.good_price as '成本单价',
							wait_in_stock '待入库存数量',
							wait_out_stock '待出库存数量',
							usable_stock '可用库存数量',
							wait_out_stock+usable_stock '当前库存数量',
							'' as '可用库存金额',
							'' as '当前库存金额',
							'' as '保质期时间',
							'' as '库存天数',
							wgb.creater as 'CGD.采购员',
							ws.`name` as '供应商名称',
							wwi.`name` as '仓库名称',
							wp.addtime as '备库创建时间' ")
            ->leftJoin('purchease_order wpo', 'wgs.spuCode = wpo.spuCode and order_source=0')
            ->leftJoin('purchease wp', 'wp.bk_code = wpo.bkcode')
            ->leftJoin('warehouse_info wwi', 'wwi.wsm_code = wgs.wsm_code')
            ->leftJoin('business wb', 'wb.companyNo = wwi.companyNo')
            ->leftJoin('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
            ->leftJoin('supplier ws', 'wgb.supplierNo = ws.`code`')
            ->where('wgs.is_del', 0)
            ->where('wgb.is_stock', 1)
            ->whereBetween('wgs.updatetime', [$start_date, $end_date])
            ->order('wgs.updatetime', 'desc')
            ->cursor();

        $list = [];
        foreach ($data as $value) {
            $cat = made($value['一级分类']);
            $value['申请部门'] = get_company_name_by_uid(intval($value['申请部门']));
            $value['一级分类'] = isset($cat[0]['name'])?$cat[0]['name']:"";
            $value['可用库存金额'] = bcmul((string)$value['可用库存数量'] ?? '0', (string)$value['成本单价'] ?? '0', 2);
            $value['当前库存金额'] = bcmul((string)$value['当前库存数量'] ?? '0', (string)$value['成本单价'] ?? '0', 2);
//            $value['最近入库时间'] =Db::name("purchease_order")->alias("a")->leftJoin("purchease_in c","a.cgdNo=c.cgdNo")->where(["spuCode"=>$value['产品编号'],"order_type"=>1,"order_source"=>0,"c.status"=>[4,6]])->order("a.addtime desc")->value("c.addtime","");
            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天之后
            }
            yield $list[] = $value;
        }

        return $list;

    }

    //【财务报表】退货台账
    private function C(string $start_date = '', string $end_date = '')
    {
        $data = Db::name('th_data')
            ->alias('wtd')
           ->leftJoin('sale_return wsr', 'wtd.thCode = wsr.returnCode and wtd.th_type=1')
            ->leftJoin('order_return wor', 'wtd.thCode = wor.returnCode and wtd.th_type in (2,3)')
            ->leftJoin('sale ws', 'ws.orderCode=wtd.orderCode and ws.is_del=0')
            ->leftJoin('business wb', ' ws.supplierNo=wb.companyNo')
            ->leftJoin('order_num won', 'won.orderCode=wtd.orderCode')
            ->leftJoin('purchease_order wpo', 'wpo.cgdNo=won.cgdNo and wpo.is_del=0')
            ->leftJoin('supplier wps', 'wps.code=wpo.supplierNo')
            ->leftJoin('customer_info wci', 'wci.companyNo =ws.customer_code')
            ->whereBetween('wtd.addtime', [$start_date, $end_date])
            ->field("	year(wtd.addtime) '年',
	month(wtd.addtime) '月',
	DAYOFMONTH(wtd.addtime) '日',
	wtd.addtime '退货单创建时间',
	wb.company '公司名称',
	thCode '退货单号',
	if(wtd.th_type=1,'售前','售后') '退货类型',
	'已完成' as '流程进度',
  wtd.apply_id '业务部门',
  wtd.apply_name '业务人员',
	wtd.orderCode '订单编号',
	ws.platform_id '平台类型',
	ws.platform_order '平台订单号',
	'' as '客户属性',
	wci.itemid  as '分公司',
	wci.companyName '客户名称',
	wtd.spuCode '产品编码',
	'' as '财务核算码',
	'' as `一级分类`,
	'' as `二级分类`,
	wtd.cat_id as `三级分类`,
	wtd.good_name as '商品名称',
	ws.order_type as `单位`,
	wtd.th_num  '退货数量',
	ws.sale_price '销售单价',
	wtd.th_fee '退货金额',
	 ifnull(wsr.remark,wor.error_remark) as '退货备注',
	won.cgdNo '采购单单号',
	wpo.cgder '采购员',
	wpo.nake_fee '采购裸价',
	wpo.pakge_fee '包装费',
	wpo.delivery_fee '物流费',
	wpo.cert_fee '证书费',
	wpo.mark_fee '加标费',
	wpo.open_fee '开模费',
	wpo.diff_weight '工差',
	wpo.diff_fee '采购工差金额',
	wpo.good_price '成本合计',
	(wpo.good_price * wtd.th_num) '退货采购货款',
	'' as '税点',
	wpo.supplier_name '供应商名称',
	if(ws.is_stock=1,'是','否') '是否库存',
	'' as '发货方式',
	if(ifnull(wsr.is_th,wor.is_th)=0,'否','是') as '供应商是否同意退货',
	if(wps.pay_type='0','现结',if(wps.pay_type='1','月结',if(wps.pay_type='2','双月结',wps.pay_type))) as '付款方式'
	")->cursor();

        $com = [];
        foreach ($data as $value) {
            $value['业务部门'] = get_company_name_by_uid(intval($value['业务部门']));
            $value['财务核算码']=Db::name("cat")->where(["id"=>$value['三级分类']])->value("fund_code",'');
            $comp =get_top_customer_org($value['分公司']);
            $value['客户属性']=isset($comp[0]['name'])?$comp[0]['name']:"";
            $value['分公司']=isset($comp[1]['name'])?$comp[1]['name']:"";
            $top = made($value['三级分类']);
            $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($value['单位']==3||$value['单位']==4){
                $good= Db::name("good_zixun")->where(["spuCode"=>$value['产品编码']])->find();
            }else{
                $good= Db::name("good_basic")->where(["spuCode"=>$value['产品编码']])->find();
            }
            $value['单位']=isset($good['good_unit'])?Db::name("unit")->where(["id"=>$good['good_unit']])->value('unit',''):"";
            $value['税点']=isset($good['tax'])?$good['tax'].'%':"";
            $value['平台类型']= Db::name("platform")->where(["id"=>$value['平台类型']])->value("platform_name",'');
            yield $com[] = $value;
        }

        return $com;

    }

    //【财务报表】出入库明细
    public function D($start,$end){
        $list =Db::name("order_out")->alias('woo')->leftJoin("sale a","a.orderCode=woo.orderCode and a.is_del=0")
            ->leftJoin("order_back wor","wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
            ->leftJoin("order_num won","won.orderCode=a.orderCode")
            ->leftJoin("purchease_order wpo","wpo.cgdNo=won.cgdNo and wpo.is_del=0")
            ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode and wgb.is_del=0")
            ->leftJoin("good_zixun wgz","wgz.spuCode=wpo.spuCode and wgz.is_del=0")
            ->leftJoin("supplier ws","ws.code=wpo.supplierNo")
            ->where('woo.status',">=", 2)
            ->where('woo.is_del',"=", 0)
            ->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` )/100 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=["常规商品",'赠品','样品'];
            $data=[];
            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['业务部门名称'],"a.status"=>1,"a.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['是否库存品']==1){
                    $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo and b.is_del=0")
                        ->where(["bkcode"=>$value['发货方式'],"order_type"=>1])->order("b.addtime desc")
                        ->value("b.sendtype",'');
                    $value['是否库存品']='是';
                }else{
                    $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b","a.cgdNo=b.cgdNo  and b.is_del=0")
                        ->where(["a.cgdNo"=>$value['发货方式']])->order("b.addtime desc")->value("b.sendtype",'');
                    $value['是否库存品']='否';
                }
                $value['发货方式'] = $sendtype=1 ? '包邮':$sendtype=2 ? '自提':"";
                $value['售前退货数量'] = Db::name("sale_return")->where(["orderCode"=>$value['确认单编号']])->sum('num');
                $value['售前退货金额'] =round($value['售前退货数量']* $value['售前退货金额'],2);
                yield $data[] = $value;
            }
            $cgd =$this->GetWlb($start,$end);
            foreach ($cgd as $key=>$value){
                yield $data[] = $value;
            }
            return $data;
        }

    //【财务报表】库存品入库明细
    public function E($start,$end){
        $list =Db::name("purchease_in")->alias('wpi')->leftJoin("purchease_order wpo","wpo.cgdNo=wpi.cgdNo")
            ->leftJoin("purchease wp","wpo.bkcode=wp.bk_code and wp.is_del=0")
            ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode and wgb.is_del=0")
            ->leftJoin("warehouse_info wwi","wwi.wsm_code = wpo.wsm_code")
            ->leftJoin("supplier ws","ws.code=wpo.supplierNo")
            ->where('wpi.status',"in", [4,6])
            ->where('wpi.is_del',"=", 0)
            ->where('wpo.order_type',"=",1)
            ->where('wpo.order_source',"=",0)
            ->whereBetween('wpi.addtime', [$start, $end])
            ->field("wpo.companyNo '业务公司',
                            bkcode '备库单号',
                            wpi.wsm_in_code '入库单号',
                            wpi.cgdNo '采购单号',
                              wpo.spuCode '商品编号',
                               '' as '一级分类',
                               '' as '二级分类',
                                wgb.cat_id as '三级分类',
                                wgb.cat_id as '财务核算编号',
                               wpo.good_name as '商品名称',
                            if(wpi.status=4,wpi.send_num,wpi.wsm_num) '入库数量',
	                        round( if(wpi.status=4,wpi.send_num,wpi.wsm_num) * good_price, 2 ) '入库金额',
                               wpi.updatetime '本次入库时间',
                            wgb.tax/100 '采购税率',
                            '' as '不含税入库金额',
                            ''  as '税额',
                               wp.apply_id as '业务部门',
                               wp.apply_name as '业务员',
                              wpo.cgder as '采购员',
                               wpo.good_price '成本裸价',
                               wsm_type '仓库类型',
                               ws.name '供应商名称',
                               wwi.name '仓库名称',
                               wp.addtime '备库创建时间'
             ")->cursor();
        $data=[];
        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['业务部门'],"a.status"=>1,"a.is_del"=>0])->value('ci.name','');
            $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['不含税入库金额']= round($value['入库金额']/(1+$value['采购税率']),2);
            $value['税额']= round(($value['入库金额']/(1+$value['采购税率'])) *$value['采购税率'],2);
            $value['仓库类型'] = Db::name("warehouse_type")->where(["id"=>$value['仓库类型']])->value("name",'');
            yield $data[] = $value;
        }
        return  $data;
    }
    //【财务报表】库存品出库明细
    public function F($start,$end){
        $list =Db::name("order_out")->alias('woo')->leftJoin("sale a","a.orderCode=woo.orderCode and a.is_del=0")
            ->leftJoin("order_num won","won.orderCode=a.orderCode")
            ->leftJoin("order_back wor","wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
            ->leftJoin("purchease_order wpo","wpo.cgdNo=won.cgdNo")
            ->leftJoin("good_basic wgb","wgb.spuCode=wpo.spuCode and wgb.is_del=0")
            ->leftJoin("supplier ws","ws.code=wpo.supplierNo")
            ->leftJoin("warehouse_info wwi","wwi.wsm_code = wpo.wsm_code")
            ->where([['woo.status',">=", 2],['woo.is_del',"=",0],['a.order_type',"=", 1],['woo.sendtime',"between",[$start, $end]]])
            ->field("wpo.companyNo '业务公司',
                            bkcode '备库单号',
                            woo.orderCode '销售单号',
                            woo.outCode '出库单号',
                             wpo.cgdNo '采购单号',
                               wpo.spuCode '商品编号',
                               '' as '一级分类',
                               '' as '二级分类',
                                wgb.cat_id as '三级分类',
                                wgb.cat_id as '财务核算编号',
                               wpo.good_name as '商品名称',
                                woo.send_num'出库数量',
                               round( woo.send_num* wpo.good_price,2) '出库金额',
                               woo.sendtime '本次出库时间',
                                wgb.tax/100 '税率',
                            '' as '不含税出库金额',
                            ''as '税额',
                               a.apply_id as '业务部门',
                               a.apply_name as '业务员',
                              wpo.cgder as '采购员',
                               wpo.good_price '成本裸价',
                               wor.return_num  '出库退货数量',
                               wwi.wsm_type '仓库类型',
                               ws.name '供应商名称',
                               wwi.name '仓库名称'
             ")->cursor();
        $data=[];
        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['业务部门'],"a.status"=>1,"a.is_del"=>0])->value('ci.name','');
            $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['不含税出库金额']= round($value['出库金额']/(1+$value['税率']),2);
            $value['税额']= round(($value['出库金额']/(1+$value['税率'])) *$value['税率'],2);
            $value['仓库类型'] = Db::name("warehouse_type")->where(["id"=>$value['仓库类型']])->value("name",'');
            yield $data[] = $value;
        }
        return $data;
    }
    //网络部出入库
    public function  GetWlb($start,$end){
        $db =Db::connect("mysql3");
        $list =$db->name("cgd_info")
          ->where(['ShortText1617866362204'=>'客服确认单'])->whereBetweenTime('createdTime',$start,$end)->field(
            "year(createdTime) AS `年`,
month(createdTime) AS `月`,
dayofmonth(createdTime) AS `日`,
ShortText1618270466672 '公司名称',
department '业务部门',
ownerName '业务人员',
ShortText1617866362204 '订单类型',
'' as '商品类型',
'' as '发货编号',
Number1618240600907 '本次发货数量',
createdTime '本次发货时间',
ShortText1617866360004 '确认单编号',
ShortText1617861001482 '商品编号',
ShortText1617865626160 '一级分类',
''as  '二级分类',
'' as  '三级分类',
 '' as '财务核算编码',
 ShortText1617861966146 as '商品名称',
 ShortText1617865685744 as '单位',
 Number1618240600907 '下单数量',
 '' AS `销售货款`,
 ShortText1618315935182 as '采购单单号',
 ownerName '采购员',
            Number1617865804813 AS `采购裸价`,
           Number1617865807879 AS `加标费`,
            Number1617865810822 AS `包装费`,
          Number1617865813284 AS `证书费`,
          Number1617865816181 AS `开模费`,
          number1618240204358 AS `工艺费`,
          number1618240287778 AS `物流费`,
          Number1617865818517 AS `成本合计`,
          Number1618240685904 AS `采购货款`,
         ShortText1617865688485 AS `采购税点`, 
		 ShortText1620399144946 AS `供应商名称`,
							'否'as '是否库存品',
              '' as '发货方式',
              '' AS `退货编号`,
		'' AS `退货数量`,
		'' as '退货金额',
		'' as '售前退货数量',
		'' as '售前退货金额'
 ")->cursor();
        $data=[];
        foreach($list as $value){
            $value['销售货款'] = $db->name("qrd_info")->where(['sequenceNo'=>$value['确认单编号']])->value('Number1618248813613','');
            $value['财务核算编码'] = $db->name("cat")->where(['cat_name'=>$value['一级分类']])->value('fund_code','');
            yield $data[] = $value;
        }
        return $data;
    }

    //【销售】订单表导出
    public function G(string $start_date = '', string $end_date = '')
    {
        $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售库存', 6 => '平台部订单销售非库存'];//订单类型
        $all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];//订单状态(sale表里的status)
        $all_stock = [0 => '非库存品', 1 => '库存品'];//是否库存品
        $all_is_activity = [0 => '不参与活动', 1 => '参与活动'];
        $all_good_type = [1 => '正常商品', 2 => '赠品', 3 => '样品'];

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.addtime 确认单下单时间,s.orderCode 订单编号,s.order_type 订单类型,s.status 订单状态,s.apply_name 创建人,ci.name 部门,b.company 销售方公司,"" 一级组织,"" 二级组织,csi.companyName 购买方公司,p.platform_name 所属平台,s.platform_order 平台订单号,s.poNo 其他单号,s.paytime 承诺回款时间,ou.order_use 订单用途,"" 活动类型,s.good_name 产品名称,"" 一级分类,gb.tax 税率,s.sale_price 销售单价,s.good_num 下单数量,s.total_price 销售总额,po.cgder 采购人,gb.customized 工期时间,s.arrive_time 要求到货时间,s.remark 备注,s.is_stock 是否库存品,csi.itemid,s.cat_id,s.is_activity,s.good_type')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('business b', 'b.companyNo=s.supplierNo')
            ->leftJoin('platform p', 'p.id=s.platform_id')
            ->leftJoin('good_basic gb', 'gb.spuCode=s.good_code')
            ->leftJoin('order_use ou', 'ou.id=s.use_order')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id')
            ->leftJoin('company_item ci', 'ci.id=u.itemid')
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
            ->where('s.is_del', 0)
            ->whereBetween('s.addtime', [$start_date, $end_date])
            ->order('s.addtime', 'desc')
            ->cursor();

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

            $value['订单类型'] = isset($all_order_type[$value['订单类型']]) ? $all_order_type[$value['订单类型']] : '';
            $value['订单状态'] = isset($all_sale_status[$value['订单状态']]) ? $all_sale_status[$value['订单状态']] : '';

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            $cat = made($value['cat_id']);
            $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';
            $value['是否库存品'] = isset($all_stock[$value['是否库存品']]) ? $all_stock[$value['是否库存品']] : '';

            $is_activity = isset($all_is_activity[$value['is_activity']]) ? $all_is_activity[$value['is_activity']] : '';
            $good_type = isset($all_good_type[$value['good_type']]) ? $all_good_type[$value['good_type']] : '';

            $value['活动类型'] = $is_activity . '/' . $good_type;
            unset($value['good_code']);
            unset($value['itemid']);
            unset($value['cat_id']);
            unset($value['is_activity']);
            unset($value['good_type']);

            yield $list[] = $value;
        }

        return $list;

    }

    //【库存报表】库存日报及预警汇总表
    private function H(string $start_date = '', string $end_date = '')
    {
        $data = Db::name('good_stock')
            ->alias('wgs')
            ->field("	 wb.company '公司名称',wgs.spuCode '产品编号',cat_id '一级分类',good_name '产品名称',wait_in_stock '待入库存数量',wait_out_stock '待出库存数量',usable_stock '可用库存数量',wait_out_stock+usable_stock '当前库存','' as '保质期时间','' as '库存天数',wgb.creater as 'CGD.采购员','' as '备库单号',ws.`name` as '供应商名称',wwi.`name` as '仓库名称','' as '最近入库时间'")
            ->leftJoin('warehouse_info wwi', 'wwi.wsm_code = wgs.wsm_code')
            ->leftJoin('business wb', 'wb.companyNo = wwi.companyNo')
            ->leftJoin('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
            ->leftJoin('supplier ws', 'wgb.supplierNo = ws.`code`')
            ->where('wgs.is_del', 0)
            ->where('wgb.is_stock', 1)
            ->whereBetween('wgs.addtime', [$start_date, $end_date])
            ->order('wgs.addtime', 'desc')
            ->cursor();

        $list = [];
        foreach ($data as $value) {
            $cat = made($value['一级分类']);
            $value['一级分类'] = isset($cat[0]['name'])?$cat[0]['name']:"";

            $value['最近入库时间'] =Db::name("purchease_order")->alias("a")->leftJoin("purchease_in c","a.cgdNo=c.cgdNo")->where(["spuCode"=>$value['产品编号'],"order_type"=>1,"order_source"=>0,"c.status"=>[4,6]])->order("a.addtime desc")->value("c.addtime","");
            $value['备库单号']=Db::name("purchease_order")->alias("a")->where(["spuCode"=>$value['产品编号'], "order_type"=>1,"order_source"=>0])->order("a.addtime desc")->value("a.bkcode","");
            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天之后
            }
            yield $list[] = $value;
        }

        return $list;

    }

    //【库存报表】出入库明细
    public function I(string $start= '', string $end= '')
    {
        $list = Db::name("order_out")
            ->alias('woo')
            ->leftJoin("sale a", "a.orderCode=woo.orderCode")
            ->leftJoin("order_back wor", "wor.outCode=woo.outCode and wor.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 `下单数量`,`wpo`.`cgdNo` AS `采购单单号`,`wpo`.`cgder` 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 `退货数量`,'' as '售前退货数量'")
            ->cursor();
        $order_tyepe = ["库存销售", '非库存销售', '咨询销售', '项目销售', '平台库存销售', '平台非库存销售'];
        $good_type = ["常规商品", '赠品', '样品'];
        $data = [];
        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['业务部门名称'], "a.status" => 1, "a.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['是否库存品'] == 1) {
                $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b", "a.cgdNo=b.cgdNo")->where(["bkcode" => $value['发货方式'], "order_type" => 1])->order("b.addtime desc")->value("b.sendtype", '');
                $value['是否库存品'] = '是';
            } else {
                $sendtype = Db::name("purchease_order")->alias("a")->leftJoin("purchease_in b", "a.cgdNo=b.cgdNo")->where(["a.cgdNo" => $value['发货方式']])->order("b.addtime desc")->value("b.sendtype", '');
                $value['是否库存品'] = '否';
            }
            $value['发货方式'] = $sendtype = 1 ? '包邮' : $sendtype = 2 ? '自提' : "";
            $value['售前退货数量'] = Db::name("sale_return")->where(["orderCode" => $value['确认单编号']])->sum('num');
            yield $data[] = $value;
        }
        $cgd = $this->GetWlb($start, $end);
        foreach ($cgd as $key => $value) {
            yield $data[] = $value;
        }
        return $data;
    }

    //【库存报表】-库存品入库明细
    public function J( string $start= '', string $end= '')
    {
        $list = Db::name("purchease_in")->alias('wpi')->leftJoin("purchease_order wpo", "wpo.cgdNo=wpi.cgdNo")
            ->leftJoin("purchease wp", "wpo.bkcode=wp.bk_code")
            ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode")
            ->leftJoin("warehouse_info wwi", "wwi.wsm_code = wpo.wsm_code")
            ->leftJoin("supplier ws", "ws.code=wpo.supplierNo")
            ->where('wpi.status', "in", [4, 6])
            ->where('wpo.order_type', "=", 1)
            ->where('wpo.order_source', "=", 0)
            ->whereBetween('wpi.addtime', [$start, $end])
            ->field("wpo.companyNo '业务公司',bkcode '备库单号',wpi.wsm_in_code '入库单号',wpi.cgdNo '采购单号',wpo.spuCode '商品编号','' as '一级分类','' as '二级分类',wgb.cat_id as '三级分类',wgb.cat_id as '财务核算编号',wpo.good_name as '商品名称',if(wpi.status=4,wpi.send_num,wpi.wsm_num) '入库数量',wpo.updatetime '本次入库时间',wp.apply_id as '业务部门',wp.apply_name as '业务员',wpo.cgder as '采购员',wsm_type '仓库类型',ws.name '供应商名称',wwi.name '仓库名称'")
            ->cursor();
        $data = [];
        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['业务部门'], "a.status" => 1, "a.is_del" => 0])->value('ci.name', '');
            $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("warehouse_type")->where(["id" => $value['仓库类型']])->value("name", '');
            yield $data[] = $value;
        }
        return $data;
    }

    //【库存报表】库存品出库明细
    public function K(string $start = '', string $end = '')
    {
        $list = Db::name("order_out")
            ->alias('woo')
            ->leftJoin("sale a", "a.orderCode=woo.orderCode and a.is_del=0")
            ->leftJoin("order_num won", "won.orderCode=a.orderCode")
            ->leftJoin("order_back wor", "wor.outCode=woo.outCode and wor.status=4 and wor.is_del=0")
            ->leftJoin("purchease_order wpo", "wpo.cgdNo=won.cgdNo and wpo.is_del=0")
            ->leftJoin("good_basic wgb", "wgb.spuCode=wpo.spuCode and wgb.is_del=0")
            ->leftJoin("supplier ws", "ws.code=wpo.supplierNo")
            ->leftJoin("warehouse_info wwi", "wwi.wsm_code = wpo.wsm_code")
            ->where([['woo.status', ">=", 2],['woo.is_del',"=", 0],['a.order_type', "=", 1],['woo.addtime','between',[$start, $end]]])
            ->field("wpo.companyNo '业务公司',bkcode '备库单号',woo.orderCode '销售单号',woo.outCode '出库单号',wpo.cgdNo '采购单号',wpo.spuCode '商品编号','' as '一级分类','' as '二级分类',wgb.cat_id as '三级分类',wpo.good_name as '商品名称',woo.send_num'出库数量',woo.sendtime '本次出库时间',a.apply_id as '业务部门',a.apply_name as '业务员',wpo.cgder as '采购员',wor.return_num  '出库退货数量',wwi.wsm_type '仓库类型',ws.name '供应商名称',wwi.name '仓库名称'")
            ->cursor();
        $data = [];
        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['业务部门'], "a.status" => 1, "a.is_del" => 0])->value('ci.name', '');
            $cat = made($value['三级分类']);
            $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("warehouse_type")->where(["id" => $value['仓库类型']])->value("name", '');
            yield $data[] = $value;
        }
        return $data;
    }


}