phpstock/app/command/ExecByRelaComNoHandle.php

<?php declare (strict_types=1);

namespace app\command;

use app\admin\common\User;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\db\exception\PDOException;
use think\Exception;
use think\facade\Cache;
use think\facade\Db;

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

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

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


        ini_set("memory_limit", "516M");
        $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'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【财务报表】库存日报及预警汇总表
                    case 'B':
                        $res = $this->B($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【财务报表】退货台账
                    case 'C':
                        $res = $this->C($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【财务报表】出入库明细
                    case 'D':
                        $res = $this->D($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【财务报表】库存品入库明细
                    case 'E':
                        $res = $this->E($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【财务报表】库存品出库明细
                    case 'F':
                        $res = $this->F($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【销售】订单表导出
                    case 'G':
                        $res = $this->G($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【库存报表】-库存日报及预警汇总表
                    case 'H':
                        $res = $this->H($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【库存报表】-出入库明细
                    case 'I':
                        $res = $this->I($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【库存报表】-库存品入库明细
                    case 'J':
                        $res = $this->J($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【库存报表】-库存品出库明细
                    case 'K':
                        $res = $this->K($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //【库存报表】-库存品出库明细
                    case 'L':
                        $res = $this->NewOut($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //每日库存数据更新
                    case 'M':
                        $res = $this->EveryDayStock($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    //商品成本信息
                    case 'N':
                        $res = $this->GetGoodNake($info['relaComNo'], $info['relaComName']);
                        break;
                    //商品售价信息
                    case 'P':
                        $res = $this->GetGoodLadder($info['relaComNo'], $info['relaComName']);
                        break;
                    case 'Q':
                        $res = $this->GetBid($info['start'], $info['end'], $info['relaComNo'], $info['relaComName']);
                        break;
                    default:
                        throw new Exception('暂不支持这个报表');
                }

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

                Db::name('exec_rela_com_no')
                    ->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')),
                        'remark' => ''
                    ]);
            }

        } catch (Exception $exception) {
            Db::name('exec_rela_com_no')
                ->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')),
                    'remark' => $exception->getMessage()
                ]);
        }

    }

    //【财务报表】退货台账-业务口径
    private function A(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
    {
        $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 退货备注,ob.customer_name 客户名称,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')
            ->leftJoin('sale s', 's.orderCode=ob.orderCode')
            ->leftJoin('platform p', 'p.id=ob.platform_id')
            ->where(['ob.is_del' => 0, 'ob.status' => 4, 's.supplierNo' => $relaComNo])
            ->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 = [];

        $tmp_customer = $tmp = [];
        $userCommon = User::getIns();
        foreach ($data as $value) {

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

            if (!isset($tmp_customer[$value['客户编码']])) {
                $temp = $userCommon->handle('cInfo', ['companyNo' => $value['客户编码']]);
                $tmp_customer[$value['客户编码']] = $temp['data']['itemid'] ?? '';
            }

            $value['itemid'] = $tmp_customer[$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;
                    }
                }
            }

            if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
            if (!isset($tmp[$value['cgderid']])) $tmp[$value['cgderid']] = get_company_name_by_uid($value['cgderid']);
            $value['退货人所在部门'] = $tmp[$value['apply_id']] ?? '';
            $value['业务人员所在部门'] = $tmp[$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');
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            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 = '', string $relaComNo = '', string $relaComName = '')
    {
        $data = Db::name('good_stock')
            ->alias('wgs')
            ->field("	wwi.companyName as '公司名称',
							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.采购员',
							wgb.supplierName 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('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
            ->where('wgs.is_del', 0)
            ->where('wgb.is_stock', 1)
            ->where('wpo.companyNo', $relaComNo)
            ->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天之后
            }
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            yield $list[] = $value;
        }

        return $list;

    }

    //【财务报表】退货台账
    private function C(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
    {
        $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('order_num won', 'won.orderCode=wtd.orderCode')
            ->leftJoin('purchease_order wpo', 'wpo.cgdNo=won.cgdNo and wpo.is_del=0')
            ->whereBetween('wtd.addtime', [$start_date, $end_date])
            ->where('ws.supplierNo', $relaComNo)
            ->field("	year(wtd.addtime) '年',
	month(wtd.addtime) '月',
	DAYOFMONTH(wtd.addtime) '日',
	wtd.addtime '退货单创建时间',
	wsr.companyName as '公司名称',
	thCode '退货单号',
	if(wtd.th_type=1,'售前','售后') '退货类型',
	'已完成' as '流程进度',
  wtd.apply_id '业务部门',
  wtd.apply_name '业务人员',
	wtd.orderCode '订单编号',
	ws.platform_id '平台类型',
	ws.platform_order '平台订单号',
	'' as '客户属性',
	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 '供应商是否同意退货',
	'' as '付款方式',
	ws.customerName 客户名称,
	ws.customer_code
	")->order("wtd.addtime")
            ->cursor();
        $userCommon = User::getIns();

        $temp_customer = [];

        $com = [];

        $cat = Db::name('cat')->where(['is_del' => 0, 'status' => 1, 'level' => 3])->column('cat_name', 'id');
        $unit = Db::name('unit')->where('is_del', 0)->column('unit', 'id');
        $platform = Db::name('platform')->where('is_del', 0)->column('platform_name', 'id');

        foreach ($data as $value) {
            if (!isset($temp_customer[$value['customer_code']])) {
                $tmp = $userCommon->handle('cInfo', ['companyNo' => $value['customer_code']]);
                $temp_customer[$value['customer_code']] = $tmp['data']['itemid'] ?? '';
            }

            $value['分公司'] = $temp_customer[$value['customer_code']] ?? '';

            $value['业务部门'] = get_company_name_by_uid(intval($value['业务部门']));
            $value['财务核算码'] = $cat[$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")->field('supplierNo,supplierName,good_unit,tax')->where(["spuCode" => $value['产品编码']])->find();
            } else {
                $good = Db::name("good_basic")->field('supplierNo,supplierName,good_unit,tax')->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 = $userCommon->handle('sInfo', ['code' => $good['supplierNo']]);
                $value['采购员'] = $supplier['data']['person'] ?? '';
                $value['供应商名称'] = $good['supplierName'];
            }
            $value['单位'] = isset($good['good_unit']) ? $unit[$good['good_unit']] ?? '' : '';
            $value['税点'] = isset($good['tax']) ? $good['tax'] . '%' : "";
            $value['平台类型'] = $platform[$value['平台类型']] ?? '';

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            unset($value['customer_code']);
            yield $com[] = $value;
        }

        return $com;

    }

    //【财务报表】出入库明细
    public function D($start, $end, string $relaComNo = '', string $relaComName = '')
    {
        $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")
            ->where('woo.status', ">=", 2)
            ->where('woo.is_del', "=", 0)
            ->where('wpo.order_type', "<>", 1)
            ->where('a.supplierNo', $relaComNo)
            ->whereBetween('woo.sendtime', [$start, $end])
            ->field(" year(`a`.`addtime`) AS `年`,
            month(`a`.`addtime`) AS `月`,
            dayofmonth(`a`.`addtime`) AS `日`,
            a.supplierName as '公司名称', 
             a.apply_id '业务部门名称' ,
             a.apply_name as '业务人员',
             if(a.pay_id>0,'是','否') 是否拆单,
             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`.`diff_fee` AS `公差金额`,
            `wpo`.`good_price` AS `成本合计`,
            `wpo`.`total_fee` AS `采购货款`,
            ifnull( `wgb`.`tax`, `wgz`.`tax` )/100 AS `采购税点`,
            `wpo`.`supplier_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 = [];

        $fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
        $unit = Db::name('unit')->where(['is_del' => 0])->column('unit', 'id');
        $sale_return_num = Db::name('sale_return')->where('is_del', 0)->group('orderCode')->column('sum(num) ', 'orderCode');

        $temp = [];
        foreach ($list as $value) {
            if (!isset($temp[$value['业务部门名称']])) $temp[$value['业务部门名称']] = get_company_name_by_uid($value['业务部门名称']);
            $value['业务部门名称'] = $temp[$value['业务部门名称']] ?? '';
            $value['订单类型'] = $order_tyepe[$value['订单类型'] - 1];
            $value['商品类型'] = $good_type[$value['商品类型'] - 1];
            $cat = made($value['三级分类']);
            $value['财务核算编码'] = $fund_code[$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['单位'] = $unit[$value['单位']] ?? '';
            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['售前退货数量'] = $sale_return_num[$value['确认单编号']] ?? 0;
            $value['售前退货金额'] = round($value['售前退货数量'] * $value['售前退货金额'], 2);

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            yield $data[] = $value;
        }
        $cgd = $this->GetWlb($start, $end);
        foreach ($cgd as $key => $value) {
            yield $data[] = $value;
        }
        return $data;
    }

    //【财务报表】库存品入库明细
    public function E($start, $end, string $relaComNo = '', string $relaComName = '')
    {
        $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")
            ->where([
                'wpi.status' => 2,
                'wpi.is_del' => 0,
                'wpo.order_type' => 1,
                'wpo.order_source' => 0,
                'wpo.companyNo' => $relaComNo,
            ])
            ->whereBetween('wpi.addtime', [$start, $end])
            ->field("wpo.companyName '业务公司',
                            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 '商品名称',
                                wpi.send_num'入库数量',
	                        round( wpi.send_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 '仓库类型',
                               wpo.supplier_name '供应商名称',
                               wwi.name '仓库名称',
                               wp.addtime '备库创建时间'
             ")
            ->cursor();

        $data = $tmp = [];
        $all_warehouse_type = Db::name('warehouse_type')->column('name', 'id');
        $all_fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
        foreach ($list as $value) {
            if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
            $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
            $cat = made($value['三级分类']);
            $value['财务核算编号'] = $all_fund_code[$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['不含税入库金额'] = round($value['入库金额'] / (1 + $value['采购税率']), 2);
            $value['税额'] = round(($value['入库金额'] / (1 + $value['采购税率'])) * $value['采购税率'], 2);
            $value['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            yield $data[] = $value;
        }
        return $data;
    }

    //【财务报表】库存品出库明细
    public function F($start, $end, string $relaComNo = '', string $relaComName = '')
    {
        $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("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]], ['a.supplierNo', '=', $relaComNo]])
            ->field("wpo.companyName '业务公司',
                            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 '仓库类型',
                            wpo.supplier_name '供应商名称',
                            wwi.name '仓库名称'
             ")
            ->cursor();
        $data = $tmp = [];
        $all_warehouse_type = Db::name('warehouse_type')->column('name', 'id');
        $all_fund_code = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('fund_code', 'id');
        foreach ($list as $value) {
            if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
            $value['业务部门'] = $tmp[$value['业务部门']];
            $cat = made($value['三级分类']);
            $value['财务核算编号'] = $all_fund_code[$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['不含税出库金额'] = round($value['出库金额'] / (1 + $value['税率']), 2);
            $value['税额'] = round(($value['出库金额'] / (1 + $value['税率'])) * $value['税率'], 2);
            $value['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            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 '业务人员',
					if(cxCode='','否','是') '是否拆单',
					'网络部采购单' '订单类型',
					'' 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 `物流费`,
		            0 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', '');
            yield $data[] = $value;
        }
        return $data;
    }

    //【销售】订单表导出
    public function G(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
    {
        $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 创建人,"" 部门,"" 销售方公司,"" 一级组织,"" 二级组织,"" 购买方公司,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 是否库存品,s.cat_id,s.is_activity,s.good_type,s.customer_code,s.customerName 购买方公司,s.supplierName 销售方公司,s.apply_id')
            ->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('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
            ->where(['s.is_del' => 0, 's.supplierNo' => $relaComNo])
            ->whereBetween('s.addtime', [$start_date, $end_date])
            ->order('s.addtime', 'desc')
            ->cursor();

        $list = $tmp_customer = $tmp = [];

        foreach ($data as $value) {

            if (!isset($tmp[$value['apply_id']])) $tmp[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
            $value['部门'] = $tmp[$value['apply_id']];

            if (!isset($tmp_customer[$value['customer_code']])) {
                $tmp = User::getIns()->handle('cInfo', ['companyNo' => $value['customer_code']]);
                $tmp_customer[$value['customer_code']] = $tmp['data']['itemid'];
            }

            $value['itemid'] = $tmp_customer[$value['customer_code']];

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

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            unset($value['good_code']);
            unset($value['itemid']);
            unset($value['cat_id']);
            unset($value['is_activity']);
            unset($value['good_type']);
            unset($value['customer_code']);
            unset($value['supplierNo']);
            unset($value['apply_id']);

            yield $list[] = $value;
        }

        return $list;

    }

    //【库存报表】库存日报及预警汇总表
    private function H(string $start_date = '', string $end_date = '', string $relaComNo = '', string $relaComName = '')
    {
        $data = Db::name('good_stock')
            ->alias('wgs')
            ->field("	 wwi.companyName '公司名称',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 '备库单号',wgb.supplierName as '供应商名称',wwi.`name` as '仓库名称','' as '最近入库时间'")
            ->leftJoin('warehouse_info wwi', 'wwi.wsm_code = wgs.wsm_code')
            ->leftJoin('good_basic wgb', 'wgb.spuCode = wgs.spuCode')
            ->where(['wgs.is_del' => 0, 'wgb.is_stock' => 1, 'wgb.companyNo' => $relaComNo])
            ->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天之后
            }

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            yield $list[] = $value;
        }

        return $list;

    }

    //【库存报表】出入库明细
    public function I(string $start = '', string $end = '', string $relaComNo = '', string $relaComName = '')
    {
        $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")
            ->where('woo.status', ">=", 2)
            ->where('a.supplierNo', $relaComNo)
            ->whereBetween('woo.sendtime', [$start, $end])
            ->field(" year(`a`.`addtime`) AS `年`,month(`a`.`addtime`) AS `月`,dayofmonth(`a`.`addtime`) AS `日`,a.supplierName 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 `采购员`,`wpo`.`supplier_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 = $tmp = [];
        $unit = Db::name("unit")->where('is_del', 0)->column('unit', 'id');
        $fund_code = Db::name("cat")->where(["is_del" => 0, 'level' => 3])->column('fund_code', 'id');

        foreach ($list as $value) {
            if (!isset($tmp[$value['业务部门名称']])) $tmp[$value['业务部门名称']] = get_company_name_by_uid($value['业务部门名称']);
            $value['业务部门名称'] = $tmp[$value['业务部门名称']] ?? '';
            $value['订单类型'] = $order_tyepe[$value['订单类型'] - 1];
            $value['商品类型'] = $good_type[$value['商品类型'] - 1];
            $cat = made($value['三级分类']);
            $value['财务核算编码'] = $fund_code[$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['单位'] = $unit[$value['单位']] ?? '';
            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');

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            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 = '', string $relaComNo = '', string $relaComName = '')
    {
        $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")
            ->where([
                'wpi.status' => [4, 6],
                'wpo.order_type' => 1,
                'wpo.order_source' => 0,
                'wpo.companyNo' => $relaComNo,
            ])
            ->whereBetween('wpi.addtime', [$start, $end])
            ->field("wpo.companyName '业务公司',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 '仓库类型',wpo.supplier_name '供应商名称',wwi.name '仓库名称'")
            ->cursor();
        $data = $tmp = [];
        $all_warehouse_type = Db::name("warehouse_type")->column('name', 'id');
        $fund_code = Db::name("cat")->where(["is_del" => 0, 'level' => 3])->column('fund_code', 'id');

        foreach ($list as $value) {
            if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
            $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
            $cat = made($value['三级分类']);
            $value['财务核算编号'] = $fund_code[$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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            yield $data[] = $value;
        }
        return $data;
    }

    //【库存报表】库存品出库明细
    public function K(string $start = '', string $end = '', string $relaComNo = '', string $relaComName = '')
    {
        $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("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]], ['a.supplierNo', '=', $relaComNo]])
            ->field("wpo.companyName '业务公司',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 '仓库类型',wpo.supplier_name '供应商名称',wwi.name '仓库名称'")
            ->cursor();
        $data = $tmp = [];
        $all_warehouse_type = Db::name("warehouse_type")->column('name', 'id');

        foreach ($list as $value) {
            if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
            $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
            $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['仓库类型'] = $all_warehouse_type[$value['仓库类型']] ?? '';
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            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, string $relaComNo = '', string $relaComName = '')
    {
        $data = Db::name("order_out")
            ->alias("a")
            ->leftJoin("sale b", "a.orderCode=b.orderCode")
            ->field("b.supplierName '业务公司',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]], ['b.supplierNo', '=', $relaComNo]])
            ->cursor();
        $list = $tmp = [];
        $all_fund_code = Db::name("cat")->where(["is_del" => 0, 'level' => 3])->column('fund_code', 'id');
        foreach ($data as $value) {
            if (!isset($tmp[$value['业务部门']])) $tmp[$value['业务部门']] = get_company_name_by_uid($value['业务部门']);
            $value['业务部门'] = $tmp[$value['业务部门']] ?? '';
            $cat = made($value['cat_id']);
            $value['财务核算编号'] = $all_fund_code[$value['cat_id']] ?? '';
            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 (in_array($value["order_type"], [3, 4])) {
                $good = Db::name("good_zixun")->field('tax,supplierNo,supplierName')->where(["spuCode" => $value['商品编号']])->findOrEmpty();
            } else {
                $good = Db::name("good")->field('tax,supplierNo,supplierName')->where(["spuCode" => $value['商品编号']])->findOrEmpty();
            }
            $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['供应商名称'] = $good['supplierName'];
            $value["出库退货数量"] = Db::name("order_back")->where(["outCode" => $value['出库单号']])->sum("return_num");

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            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, string $relaComNo = '', string $relaComName = '')
    {
        $list = Db::name("good_stock_info")
            ->alias("a")
            ->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("	e.companyName '业务公司',
		e.apply_id '申请部门',
		e.apply_name '申请人',
		e.addtime '备库创建时间',
		e.bk_code '备库单号',
		a.bnCode 'BN编号' ,
		a.addtime '入库时间',
		c.cgdNo '采购单编号' ,
		f.spuCode '产品编号' ,
		f.good_name '产品名称' ,
		e.good_num '备库数量' ,
		f.cat_id '一级分类' ,
		f.cat_id,
		'' as '财务核算编码',
		f.tax as '税率',
		a.origin_price '成本单价',
		b.wait_in_stock '待入库数量',
		b.wait_out_stock '待出库数量',
		(b.wait_out_stock+b.usable_stock) '当前库存数量',
		b.usable_stock '可用库存数量',
			a.total_num 'BN总数',
		a.balance_num 'BN库存数',
		a.used_num 'BN已使用数量',
		DATEDIFF(NOW(),a.addtime) '库存天数',
		f.supplierNo '采购员',
		d.supplier_name '供应商名称',
		j.`name` '仓库名称'")
            ->where(["f.is_stock" => 1, 'd.companyNo' => $relaComNo])
            ->whereBetween("a.updatetime", [$start, $end])
            ->cursor();
        $all_fund_code = Db::name("cat")->where(["is_del" => 0, 'level' => 3])->column('fund_code', 'id');

        $tmp = [];
        foreach ($list as $value) {
            if (!isset($tmp[$value['申请部门']])) $tmp[$value['申请部门']] = get_company_name_by_uid($value['申请部门']);
            $value['申请部门'] = $tmp[$value['申请部门']] ?? '';
            $cat = made($value['cat_id']);
            $value['财务核算编号'] = $all_fund_code[$value['cat_id']] ?? '';
            unset($value['cat_id']);
            $value['一级分类'] = isset($cat[0]['name']) ? $cat[0]['name'] : "";

            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;

            yield $value;
        }
    }

    /**获取商品成本列表
     * @return \Generator
     */
    private function GetGoodNake(string $relaComNo = '', string $relaComName = '')
    {
        $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.companyName '业务企业名称',
		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.supplierName '供应商名称'
	    ")->where('b.companyNo', $relaComNo)->cursor();

        $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
        $unit = Db::name('unit')->where(['is_del' => 0])->column('unit', 'id');
        $platform = Db::name('platform')->where(['is_del' => 0])->column('platform_name', 'id');
        $brand = Db::name('brand')->where(['is_del' => 0])->column('brand_name', 'id');
        $noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];
        $arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];

        foreach ($list as $value) {
            $value["品牌"] = $brand[$value["品牌"]] ?? '';
            $value["分类"] = $cat[$value["分类"]] ?? '';
            $value["上线平台"] = $platform[$value["上线平台"]] ?? '';
            $value["上线状态"] = $arr[$value["上线状态"]];
            $value['单位'] = $unit[$value['单位']] ?? '';
            $value['贵金属信息'] = $noble[$value['贵金属信息']];
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            yield $value;
        }
    }

    /**获取商品阶梯售价列表
     * @return \Generator
     */
    private function GetGoodLadder(string $relaComNo = '', string $relaComName = '')
    {
        $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.companyName '业务企业名称',
		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.supplierName '供应商名称'
	    ")->where('b.companyNo', $relaComNo)->cursor();

        $arr = ["待提交", '待完善成本', '待产品审核', '待财务定价', '待财务审核定价', '待上线', '已上线', '审核失败', '已下线'];
        $noble = ["0" => "", "1" => "18K", "2" => "24K", "3" => "白银"];

        $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
        $unit = Db::name('unit')->where(['is_del' => 0])->column('unit', 'id');
        $platform = Db::name('platform')->where(['is_del' => 0])->column('platform_name', 'id');
        $brand = Db::name('brand')->where(['is_del' => 0])->column('brand_name', 'id');

        foreach ($list as $value) {
            $value["品牌"] = $brand[$value["品牌"]] ?? '';
            $value["分类"] = $cat[$value["分类"]] ?? '';
            $value["上线平台"] = $platform[$value["上线平台"]] ?? '';
            $value["上线状态"] = $arr[$value["上线状态"]];
            $value['单位'] = $unit[$value['单位']] ?? '';
            $value['贵金属信息'] = $noble[$value['贵金属信息']];
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            yield $value;
        }
    }

    private function GetBid(string $start, string $end, string $relaComNo = '', string $relaComName = '')
    {
        $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, 'c.companyNo' => $relaComNo])
            ->whereBetween("a.addtime", [$start, $end])
            ->cursor();

        $cat = Db::name('cat')->where(['is_del' => 0, 'level' => 3])->column('cat_name', 'id');
        $unit = Db::name('unit')->where(['is_del' => 0])->column('unit', 'id');
        $platform = Db::name('platform')->where(['is_del' => 0])->column('platform_name', 'id');
        $result = Db::name('result_info')->where(['is_del' => 0])->column('result_desc', 'id');
        $arr = ["待提交", '招标进行中', '招标已结束', '等待议价结果', '确认商品', '成功转单', '取消转单', '招标已暂停'];

        $arr1 = ["待提交", "待任务结束", "待咨询确认", "议价待财务定价", "待主管确认", "待咨询确认定价", "咨询确认"];
        $arr2 = ['待业务主管审核', '业务主管通过', '待财务专员审核', '业务驳回', '财务专员通过', '财务专员驳回', '财务主管通过', '待boss审核', '财务主管驳回', 'boss审核通过', 'boss越线通过', 'boss驳回', '取消议价'];

        $tmp = $temp = [];
        foreach ($list as $value) {
            $value["商品分类"] = $cat[$value["商品分类"]] ?? '';
            $value["反馈商品分类"] = $cat[$value["反馈商品分类"]] ?? '';
            $value["反馈商品单位"] = $unit[$value["反馈商品单位"]] ?? '';

            if (!isset($tmp[$value['客户名称']])) {
                $names = User::getIns()->handle('getCodeAndName', ['code' => $value['客户名称']]);
                $tmp[$value['客户名称']] = $names['data'][$value['客户名称']];
            }
            $value["业务公司"] = $relaComName;
            $value["所属平台"] = $platform[$value["所属平台"]] ?? '';

            if (!isset($temp[$value['申请人部门']])) $temp[$value['申请人部门']] = get_company_name_by_uid($value['申请人部门']);

            $value['申请人部门'] = $temp[$value['申请人部门']] ?? '';
            //状态 ；1招标进行中 2 招标已结束  3 等待议价结果   4.确认商品  5成功转单 6 取消转单 7 招标已暂

            $value["状态"] = $arr[$value["状态"]] ?? "";
            //状态0 待提交 1 提交待确认 2 发起议价 3 财务定价4 主管定价5 确认采购
            $value["反馈商品状态"] = $arr1[$value["反馈商品状态"]] ?? "";
            //状态  1 待主管审批   2.待财务专员审批  3. 待财务主管审批  4 审批通过 5 主管驳回  6 财务主管驳回  7 已取消议价

            $value["议价状态"] = $arr2[$value["议价状态"]] ?? "";
            $value['单位'] = $unit[$value["单位"]] ?? '';
            $value['议价原因'] = $result[$value['议价原因']] ?? '';
            $value['客户名称'] = $tmp[$value['客户名称']] ?? '';
            $value['业务公司编码'] = $relaComNo;
            $value['业务公司名称'] = $relaComName;
            yield $value;
        }
    }
}