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)
    {
    	    $now=date("Y-m-d H:i:s");
    	    ini_set("memory_limit", "2048M");
            $info = Cache::store('redis')->handler()->rpop($this->key);
        try {
            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;
                         //【库存报表】-库存品出库明细
                    case 'L':
                        $res = $this->NewOut($info['start'], $info['end']);
                        break;
                        //每日库存数据更新
                    case 'M':
                        $res = $this->EveryDayStock($info['start'], $info['end']);
                        break;
                        //商品成本信息
                    case 'N':
                        $res = $this->GetGoodNake();
                        break;
                        //商品售价信息
                     case 'P':
                        $res = $this->GetGoodLadder();
                        break;
                    case 'Q':
                        $res = $this->GetBid($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("[$now]".'【' . $info['id'] . '】该预约记录处理成功');

            }

        } catch (Exception $exception) {
        	Db::name('exec')
	          ->where(['id' => $info['id'], 'status' => 1])//status==1 待处理
	          ->update([
			        'status' => 0, //status==2 处理完成
			        'down_url' => '',
			        'updatetime' => date('Y-m-d H:i:s'),
			        'expiretime' => date('Y-m-d H:i:s', strtotime('+7 day'))
		        ]);
            $output->writeln("[$now]".$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 '付款方式'
	")->order("wtd.addtime")->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){
                $good= Db::name("good_zixun")->where(["spuCode"=>$value['产品编码']])->find();
            }else{
                $good= Db::name("good_basic")->where(["spuCode"=>$value['产品编码']])->find();
            }
            if($value['单位']==1&& $value['退货采购货款']==0){
            	$returninfo = Db::name("return_info")->where(["returnCode"=>$value['退货单号']])->field("sum(origin_price * num) as cgd_total")->find();
            	$value['退货采购货款'] = $returninfo['cgd_total']??0;
            }
            if($value['采购员']=='' ||$value['供应商名称']==''){
            	$supplier= Db::name("supplier")->where(["code"=>$good['supplierNo']])->field("name,person")->find();
            	$value['采购员']=$supplier['person']??'';
            	$value['供应商名称']=$supplier['name']??'';
            }
            $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)
            ->where('wpo.order_type',"<>", 1)
            ->whereBetween('woo.sendtime', [$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_source"=>0])->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.bnCode 'BN批次单号',
                            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=a.good_code 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 '采购单号',
                            wgb.spuCode '商品编号',
                            '' as '一级分类',
                            '' as '二级分类',
                            wgb.cat_id as '三级分类',
                            wgb.cat_id as '财务核算编号',
                            wgb.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("mysql_cxinv");
        $list =$db->name("cgd_info")
          ->where(['cgdSource'=>10,"is_del"=>0])->whereBetweenTime('createdTime',$start,$end)->field(
            "year(createdTime) AS `年`,
					month(createdTime) AS `月`,
					dayofmonth(createdTime) AS `日`,
					companyName '公司名称',
					department '业务部门',
					ownerName '业务人员',
					'网络部采购单' '订单类型',
					'' as '商品类型',
					'' as '发货编号',
					sendNum '本次发货数量',
					createdTime '本次发货时间',
					qrdCode '确认单编号',
					goodNo '商品编号',
					firstCat '一级分类',
					secCat as  '二级分类',
					thirdCat as  '三级分类',
					 fundCode as '财务核算编码',
					 goodName as '商品名称',
					 goodUnit as '单位',
					 goodNum '下单数量',
					 '' AS `销售货款`,
					 sequenceNo as '采购单单号',
					 ownerName '采购员',
		            barePrice AS `采购裸价`,
		            markPrice AS `加标费`,
		            packPrice AS `包装费`,
		            certPrice AS `证书费`,
		            openPrice AS `开模费`,
		            costPrice AS `工艺费`,
		            deliveryPrice AS `物流费`,
		            goodPrice AS `成本合计`,
		            totalPrice AS `采购货款`,
		            tax/100 AS `采购税点`, 
				    supplierName AS `供应商名称`,
					'否'as '是否库存品',
		            '包邮' as '发货方式',
		            '' AS `退货编号`,
					thNum AS `退货数量`,
					th_fee as '退货金额',
					'' as '售前退货数量',
					'' as '售前退货金额'
                    ")->cursor();
        $data=[];
        foreach($list as $value){
            $value['销售货款'] = $db->name("qrd_info")->where(['sequenceNo'=>$value['确认单编号']])->value('totalPrice','');
//            $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, 'u.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.sendtime', [$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_source"=>0])
                ->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.sendtime','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;
    }
	/**
	* 新出库单明细
	* @param string $start
	* @param string $end
	 * @return \Generator
	* @throws \think\db\exception\DataNotFoundException
	* @throws \think\db\exception\DbException
	* @throws \think\db\exception\ModelNotFoundException
	 *
	 */
    private function NewOut(string $start,string $end)
    {
		$data = Db::name("order_out")
		->alias("a")
		->leftJoin("sale b","a.orderCode=b.orderCode")
		->leftJoin("depart_user du"," b.apply_id=du.uid and du.is_del=0")
		->field("b.supplierNo '业务公司',a.orderCode AS '销售单号',outCode '出库单号',
	b.good_code '商品编号',b.cat_id ,'' as '一级分类','' as '二级分类','' as  '三级分类','' as '财务核算编号',
	b.good_name '商品名称',a.send_num '出库数量','' as '出库金额',a.sendtime '本次出库时间',
    ''as '税率', b.apply_id as '业务部门',a.apply_name as '业务人员',cgder as '采购员','' as '成本裸价',
	'' as '供应商名称','' as '出库退货数量',b.order_type")
	->where([["a.status",">=",2],["a.is_del","=",0],["b.order_type","=",1],['a.sendtime','between',[$start, $end]]])
	->cursor();
		$list=[];
		foreach ($data 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['cat_id']);
			 $value['财务核算编号'] = Db::name("cat")->where(["id"=>$value['cat_id']])->value("fund_code",'');
			 unset($value['cat_id']);
             $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
             $value['二级分类'] = isset($cat[1]['name']) ? $cat[1]['name'] : "";
             $value['三级分类'] = isset($cat[2]['name']) ? $cat[2]['name'] : "";
			if($value["order_type"]==3||$value["order_type"]==4){
				$good =Db::name("good_zixun")->where(["spuCode"=>$value['商品编号']])->find();
			}else{
				$good =Db::name("good")->where(["spuCode"=>$value['商品编号']])->find();
			}
			$saleinfo =Db::name("sale_info")->where(["orderCode"=>$value['销售单号']])->field("num,origin_price,num*origin_price as total_price")->select()
			->toArray();
			if(empty($saleinfo)){
				$value['成本裸价'] = Db::name("order_out")->alias("a")
				->leftJoin("order_send b","a.outCode=b.outCode")
				->leftJoin("purchease_order c","b.cgdNo=c.cgdNo")
				->where(["a.outCode"=>$value['出库单号']])->value("c.good_price",'0');
			}else{
				$num=array_sum(array_column($saleinfo,"num"));
				if($num==0){
					$value['成本裸价']=0;
				}else{
					$value['成本裸价'] = round(array_sum(array_column($saleinfo,"total_price"))/ array_sum(array_column($saleinfo,"num"))
						,2) ;
				}
			}
			$value['出库金额'] = $value['出库数量']*$value['成本裸价'];
			$value['税率'] = $good['tax']."%";
			$value['供应商名称']=Db::name("supplier")->where(["code"=>$good['supplierNo']])->value("name","");
			$value["出库退货数量"] = Db::name("order_back")->where(["outCode"=>$value['出库单号']])->sum("return_num");
			unset($value['order_type']);
			yield $list[] = $value;
		}
    }
	/**每日库存记录
	* @param string $start
	* @param string $end
	 * @return \Generator
	* @throws \think\db\exception\DataNotFoundException
	* @throws \think\db\exception\DbException
	* @throws \think\db\exception\ModelNotFoundException
	 */
    private function EveryDayStock(string $start,string $end){
		$list = Db::name("good_stock")->alias("b")
//		->leftJoin("good_stock b","a.stockid=b.id")
//		->leftJoin("purchease_in c","c.bnCode=a.bnCode")
//		->leftJoin("purchease_order d","c.cgdNo=d.cgdNo")
//		->leftJoin("purchease e","d.bkcode=e.bk_code")
		->leftJoin("good f","b.spuCode=f.spuCode")
		->leftJoin("warehouse_info j","b.wsm_code=j.wsm_code")
		->field("	f.companyNo '业务公司',
		f.spuCode '产品编号' ,
		f.cat_id '一级分类' ,
		f.good_name '产品名称' ,
		f.cat_id,
		'' as '财务核算编号',
		f.tax as '税率',
		'' as '成本单价',
		b.wait_in_stock '待入库数量',
		b.wait_out_stock '待出库数量',
		(b.wait_out_stock+b.usable_stock)  '当前库存数量',
		b.usable_stock '可用库存数量',
		f.supplierNo '采购员',
		'' as '供应商名称',
		j.`name` '仓库名称',
		''as '最新备库时间'
		")
		->where(["f.is_stock"=>1])
//		->whereBetween("b.updatetime",[$start,$end])
		->cursor();
		foreach ($list as $value){
//			$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['业务公司'] = Db::name("business")->where(["companyNo" => $value['业务公司']])->value('company', '');
			$cat = made($value['cat_id']);
			 $value['财务核算编号'] = Db::name("cat")->where(["id"=>$value['cat_id']])->value("fund_code",'');
			 unset($value['cat_id']);
             $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";
             $supplier = Db::name("supplier")->where(["code"=>$value['采购员']])->field("person,name")->find();
             $value['供应商名称']= $supplier['name']??"";
             $value['采购员']= $supplier['person']??"";
             $value['最新备库时间']= Db::name("purchease_order")->where(["spuCode"=>$value['产品编号'],"status"=>[2,3]])
             ->order("id desc")->value("addtime",'');
             yield $value;
		}
    }
	/**获取商品成本列表
	* @return \Generator
	 */
    private  function  GetGoodNake(){
    	$list = Db::name("good_platform")->alias("a")
    	->leftJoin("good b","a.spuCode=b.spuCode")
    	->leftJoin("good_nake c","b.spuCode=c.spuCode and c.is_del=0")
    	->field("	
    	a.skuCode '上线商品编号',
		a.spuCode '成本商品编号',
		a.plat_code '平台商品编号',
		b.good_name '商品名称',
		b.brand_id '品牌',
		b.cat_id '分类',
		b.companyNo '业务企业名称',
		a.creater '申请人',
		platform_code '上线平台',
		online_time '上线时间',
		exam_status '上线状态',
		if(b.is_stock=1,'库存','非库存') '是否库存',
		if(b.good_type=1,'定制','非定制')'是否定制',
		'' as '规格',
		'' as '型号',
		b.good_unit  '单位',
		b.good_remark '商品备注',
		b.craft_desc '工艺说明',
		b.noble_metal '贵金属信息',
		b.cgd_gold_price '供应商采购金价',
		b.demo_fee '打样费',
		b.open_fee '开模费',
		b.sample_fee '调样费',
		c.min_num '起订量',
		c.nake_fee '成本单价',
		c.cost_fee '工艺费',
		c.delivery_fee '物流费',
		c.cert_fee '证书费',
		c.mark_fee '加标费',
		c.package_fee '包装费',
		c.other_fee '其他费用',
		c.nake_total '成本合计',
		b.tax '税点',
		if(b.supply_area=1,'全国','全国除偏远地区') '供货区域',
	    b.supplierNo '供应商名称'
	    ")->cursor();
    	foreach ( $list as $value){
		$value["品牌"]=Db::name("brand")->where("id",$value["品牌"])->value("brand_name",'');
	    $value["分类"]=Db::name("cat")->where("id",$value["分类"])->value("cat_name",'');
	    $value["业务企业名称"]=Db::name("business")->where("companyNo",$value["业务企业名称"])->value("company","");
		$value["上线平台"]=Db::name("platform")->where("id", $value["上线平台"])->value("platform_name","");
		$arr=["待提交",'待完善成本','待产品审核','待财务定价','待财务审核定价','待上线','已上线','审核失败','已下线'];
		$value["上线状态"]=$arr[$value["上线状态"]];
		$value['单位']=Db::name("unit")->where("id",$value['单位'])->value("unit",'');
		$noble = [ "0"=>"",
				    "1"=>"18K",
				    "2"=>"24K",
				    "3"=>"白银"];
		 $value['贵金属信息']=$noble[$value['贵金属信息']];


		 $value['供应商名称']=Db::name("supplier")->where("code",$value['供应商名称'])->value("name",'');
    		yield $value;
    	}
    }

    /**获取商品阶梯售价列表
	* @return \Generator
	 */
    private  function  GetGoodLadder(){
    	$list = Db::name("good_platform")->alias("a")
    	->leftJoin("good b","a.spuCode=b.spuCode")
    	->leftJoin("good_ladder c","a.skuCode=c.skuCode and c.is_del=0")
    	->field("	
    	a.skuCode '上线商品编号',
		a.spuCode '成本商品编号',
		a.plat_code '平台商品编号',
		b.good_name '商品名称',
		b.brand_id '品牌',
		b.cat_id '分类',
		b.companyNo '业务企业名称',
		a.creater '申请人',
		platform_code '上线平台',
		online_time '上线时间',
		exam_status '上线状态',
		if(b.is_stock=1,'库存','非库存') '是否库存',
		if(b.good_type=1,'定制','非定制')'是否定制',
		'' as '规格',
		'' as '型号',
		b.good_unit  '单位',
		b.good_remark '商品备注',
		b.craft_desc '工艺说明',
		b.noble_metal '贵金属信息',
		b.cgd_gold_price '供应商采购金价',
		b.demo_fee '打样费',
		b.open_fee '开模费',
		b.sample_fee '调样费',
		c.min_num '起订量',
		c.sale_price '售价',
		c.cost_fee '工艺费',
		c.market_price '物流费',
		c.market_platform '对比平台',
		b.tax '税点',
		if(b.supply_area=1,'全国','全国除偏远地区') '供货区域',
	    b.supplierNo '供应商名称'
	    ")->cursor();
    	foreach ( $list as $value){
		$value["品牌"]=Db::name("brand")->where("id",$value["品牌"])->value("brand_name",'');
	    $value["分类"]=Db::name("cat")->where("id",$value["分类"])->value("cat_name",'');
	    $value["业务企业名称"]=Db::name("business")->where("companyNo",$value["业务企业名称"])->value("company","");
		$value["上线平台"]=Db::name("platform")->where("id", $value["上线平台"])->value("platform_name","");
		$arr=["待提交",'待完善成本','待产品审核','待财务定价','待财务审核定价','待上线','已上线','审核失败','已下线'];
		$value["上线状态"]=$arr[$value["上线状态"]];
		$value['单位']=Db::name("unit")->where("id",$value['单位'])->value("unit",'');
		$noble = [ "0"=>"",
				    "1"=>"18K",
				    "2"=>"24K",
				    "3"=>"白银"];
		 $value['贵金属信息']=$noble[$value['贵金属信息']];
		 $value['供应商名称']=Db::name("supplier")->where("code",$value['供应商名称'])->value("name",'');
    		yield $value;
    	}
    }

    private function GetBid(string $start,string $end){
    	$list=Db::name("consult_info")->alias("a")
    	->leftJoin("consult_bids d","a.infoNo=d.infoNo")
    	->leftJoin("consult_order c","a.zxNo=c.zxNo")
    	->leftJoin("bargain_order b","d.bidNo=b.bidsNo and b.is_del=0")
    	->field("c.companyNo '业务公司',
					d.addtime '竞价时间',
					d.infoNo '竞价单编号',
					a.`status` '状态',
					c.creater '申请人',
					c.createrid '申请人部门',
					c.khNo '客户名称',
					c.platform_code '所属平台',
					if(a.pgNo='','咨询','项目') as '竞价类型',
					a.cat_id '商品分类',
					a.good_name '商品名称',
					a.budget_price '预算单价',
					a.unit '单位',
					a.num '数量',
					a.budget_price*a.num '竞价单总额',
					d.remark '商品备注',
					d.addtime '反馈时间',
					d.creater '反馈人',
					d.bidNo '反馈商品编码',
					d.status '反馈商品状态',
					d.good_name '反馈商品名称',
					d.sale_price*a.num '反馈单总额',
					d.cat_id '反馈商品分类',
					d.unit_id '反馈商品单位',
					d.tax '反馈商品税点',
					c.endtime '竞价有效期',
					b.bargainNo '议价单编号',
					b.sale_price '议价前售价',
					b.after_price '议价后售价',
					b.before_rate '议价前毛利率',
					b.after_rate '议价后毛利率',
					b.`status` '议价状态',
					b.bargain_remark '议价备注',
					b.result_info_id '议价原因'")
					->where("a.is_del",0)
					->whereBetween("a.addtime",[$start,$end])
					->cursor();
    	foreach ( $list as $value){
//		$value["品牌"]=Db::name("brand")->where("id",$value["品牌"])->value("brand_name",'');
	    $value["商品分类"]=Db::name("cat")->where("id",$value["商品分类"])->value("cat_name",'');
	    $value["反馈商品分类"]=Db::name("cat")->where("id",$value["反馈商品分类"])->value("cat_name",'');
	    $value["反馈商品单位"]=Db::name("unit")->where("id",$value["反馈商品单位"])->value("unit",'');
	    $value["业务公司"]=Db::name("business")->where("companyNo",$value["业务公司"])->value("company","");
		$value["所属平台"]=Db::name("platform")->where("id", $value["所属平台"])->value("platform_name","");
		$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', '');
		//状态 ；1招标进行中 2 招标已结束  3 等待议价结果   4.确认商品  5成功转单 6 取消转单 7 招标已暂
		$arr=["待提交",'招标进行中','招标已结束','等待议价结果','确认商品','成功转单','取消转单','招标已暂停'];
		$value["状态"]=$arr[$value["状态"]]??"";
		//状态0 待提交 1 提交待确认 2 发起议价 3 财务定价4 主管定价5 确认采购
		$arr=["待提交", "待任务结束", "待咨询确认", "议价待财务定价", "待主管确认", "待咨询确认定价", "咨询确认"];
		$value["反馈商品状态"]=$arr[$value["反馈商品状态"]]??"";
		//状态  1 待主管审批   2.待财务专员审批  3. 待财务主管审批  4 审批通过 5 主管驳回  6 财务主管驳回  7 已取消议价
		$arr= ['待业务主管审核', '业务主管通过', '待财务专员审核', '业务驳回', '财务专员通过','财务专员驳回','财务主管通过','待boss审核','财务主管驳回','boss审核通过','boss越线通过','boss驳回','取消议价'];
		$value["议价状态"]=$arr[$value["议价状态"]]??"";
		$value['单位']=Db::name("unit")->where("id",$value['单位'])->value("unit",'');
		$value['议价原因']=Db::name("result_info")->where("id",$value['议价原因'])->value("result_desc",'');
		$value['客户名称']=Db::name("customer_info")->where("companyNo",$value['客户名称'])->value("companyName",'');
    		yield $value;
    	}
    }
}