cxinv/app/command/Report.php

<?php

namespace app\command;

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

class Report extends Command
{
		//相关字段的文字转换
	private $key='Reportexec';
	private $param=[];
	private $qrdType = [1 => '库存品', 2 => '非库存品', 3 => '采购反馈'];
	private $qrdSource = [1 => '直接下单', 2 => '竞价转单', 3 => '项目转单', 4 => '平台导入', 5 => '有赞平台', 6 => '售后补换货',7=>'报备转单',8=>'支付渠道拆单',9=>"采销补录",10=>"结算补录"];
	private $cgdSource = [0=>"备库单",1 => '直接下单', 2 => '竞价转单', 3 => '项目转单', 4 => '平台导入', 5 => '有赞平台', 6 => '售后补换货',7=>'报备转单',
	8=>'支付渠道拆单',9=>"采销补录",10=>"结算补录"];
	private $sendType = [1 => '包邮', 2 => '自提'];
	private $pay_status = [1 => '未回', 2 => '部分回款', 3 => '已回'];
	private $inv_status = [1 => '未开', 2 => '部分开票', 3 => '已开'];
	private $cgd_inv_status = [1 => '未回', 2 => '部分回票', 3 => '已回'];
	private $cgd_pay_status = [1 => '未付', 2 => '部分付款', 3 => '已付'];
	private $invoice_pool_status = [1 => '财务审核通过', 2 => '待财务上传发票', 3 => '金税开票中/验票中', 4 => '开票成功', 5 => '开票失败', 6 => '发票退票/废弃', 7 => '取消申请', 8 => '财务驳回', 9 => '验票失败'];
	private $cgdType = [1 => '库存', 2 => '非库存', 3 => '咨询'];
	private $invoice_return_status = [0 => '待财务审核', 1 => '退票中', 2 => '退票成功', 3 => '审核驳回', 4 => '退票失败'];
	private $sendStatus = [1=>'未发货',2=>'部分发货',3=>'全部发货'];
	private $TagName = [1=>'采购单付款',2=>'采购单回票',3=>'销售回款',4=>"销售开票"];

    protected function configure()
    {
        // 指令配置
        $this->setName('report')
            ->setDescription('the report command');
    }

    protected function execute(Input $input, Output $output)
    {
        // 指令输出

        $Command = Cache::store('redis')->handler()->get('ExecCommand');
        if($Command>=3) return '';
		$info = Cache::store('redis')->handler()->rpop($this->key);
		if($info==false) return '';
		Cache::store('redis')->handler()->incrby('ExecCommand',1);
        Db::startTrans();
        try{
        $info = json_decode($info,true);
	    ini_set ('memory_limit', '2048M') ;
        $date=date("Y-m-d H:i:s");
        $param = Db::name("exec_log")->where("id",$info['id'])->findOrEmpty();
        if(isset($param) && !empty($param)){
        if($param['is_del']==1) throw new \Exception("脚本记录已删除");
        switch ($param['action']){
			case 'A':
				$method="A";
				$file ='销售发票申请信息导出';
				break;
			case 'B':
				$method="B";
				$file ='进项发票登记导出';
				break;
			case 'C':
				$method="C";
				$file ='资金认领导出';
				break;
			case 'D':
				$method="D";
				$file = '回款核销明细表';
				break;
			case 'E':
				$method="E";
				$file='回票明细表';
				break;
			case 'F':
				$method="F";
				$file='经营分析报表';
				break;
			case 'G':
				$method="G";
				$file='收入成本明细表';
				break;
			case 'I':
				$method="I";
				$file='应收台账表';
				break;
			case 'J':
				$method="J";
				$file='用友销票表';
				break;
			case 'K':
				$method="K";
				$file='采购单明细表';
				break;
			case 'M':
				$method="M";
				$file='对账单汇总表';
				break;
            case 'N':
                $method="N";
                $file='退货台账';
                break;
            case 'Q':
                $method="Q";
                $file='产品部门销售业绩';
                break;
            case 'R':
                $method="R";
                $file='采购部门销售业绩';
                break;
			default:
				$file='暂无数据';
				$method="";
				break;
		}

            if ($method == '') throw new Exception('该报表暂无对应的处理方法');

		$data =$this->$method($param);
            $url = excelSaveFile($data, $file . date('_YmdHis'));//加时间戳，防止同名文件覆盖
		 Db::name('exec_log')
                    ->where(['id' => $param['id'], 'status' => 4])//status==1 待处理
                    ->update([
                        'status' => 2, //status==2 处理完成
                        'down_url' => $url,
                        'updatetime' => date('Y-m-d H:i:s')
                    ]);
		 $end = date("Y-m-d H:i:s");
		$output->writeln("[$date]【{$param['name']}】[{$param['apply_name']}]预约记录处理完成 end:【{$end}】");

        	}

        }catch (\Exception $e){
			Db::name('exec_log')
                    ->where(['id' => $info['id'], 'status' =>4])//status==1 待处理
                    ->update([
                        'status' => 3, //status==2 处理失败
                        'down_url' => '',
                        "remark"=>$e->getMessage(),
                        'updatetime' => date('Y-m-d H:i:s')
                    ]);
			  $output->writeln("[$date]【{$info['name']}】[{$info['apply_name']}]预约记录处理失败 message:【{$e->getMessage()}|{$e->getFile()}|{$e->getCode()}】");
        }
       Cache::store('redis')->handler()->decrby('ExecCommand',1);
		Db::commit();
    }

    //销售发票申请信息导出
	private function A($param=[])
	{
//		$param = $this->request->only(['start' => '', 'end' => '', 'status' => '', 'inv_type' => '', 'inv_out' => '', 'invNo' => '', 'relaComNo' => '', 'inv_number' => '', 'inv_code' => '', 'inv_company' => '', 'buyer_name' => '', 'apply_id' => '', 'apply_name' => '',], 'post', 'trim');
		$where = [['a.is_del', '=', 0], ['e.status', 'in', [1, 2]]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.addtime', 'between', [$param['start'], $param['end']]];
		if (($param['companyNo'] != '') && ($param['companyNo'] != '')) $where[] = ["a.inv_out","=",$param['companyNo']];
		$open_type = [1 => '金税开票', 2 => '金税线下', 3 => '纯线下'];//开票方式
		$inv_type = config('invoiceType.invoiceName');//开票种类(发票类型)

		$list = Db::name('invoice_pool')
		          ->alias('a')
		          ->field('a.inv_out 卖方公司编码,
		          a.inv_company 卖方公司名称,
		          a.invNo 申请编号,
		          f.department 申请部门,
		          a.apply_name 申请人,
		          b.buyer_title 发票买方公司名称,
		          concat(b.buyer_code," ") 发票买方企业纳税识别号,
		          b.buyer_addr 发票买方企业地址,
		          b.buyer_mobile 发票买方企业联系方式,
		          b.buyer_bank 发票买方企业收款银行,
		          concat(b.buyer_bankNo," ") 发票买方企业财务账户,
		          d.orderCode 公司订单号,
		          b.buyer_title 订单企业客户,
		          d.goodName 订单商品名,
		          concat(g.inv_cat_code," ") 税收分类编码,
		          g.inv_cat_name 税收分类名称,
		          d.goodName 发票明细商品名称,
		          CONCAT(d.catName,"*",d.goodName) 发票货物或应税劳务、服务名称,
		          d.unitName 单位,
		          d.goodNum 数量,
		          d.goodPrice 单价,
		          d.tax 税率,
		          d.totalTax 总价,
		          a.inv_type 开票种类,
		          a.open_type 开票方式,
		          a.exam_remark 申请备注,
		          a.remark 发票备注,
		          if(f.cxCode="",f.sequenceNo,f.cxCode) 销售主单号,
		          f.goodPrice 商品单价')
		          ->leftJoin('invoice_pool_info b', 'a.invNo=b.invNo')
		          ->leftJoin('invoice_ticket c', 'a.invNo=c.invNo AND c.type=0 AND c.status=1')
		          ->leftJoin('invoice_good d', 'd.invNo=a.invNo')
		          ->leftJoin('assoc e', 'e.viceCode=d.invNo AND e.orderCode=d.orderCode AND e.is_del=0')
		          ->leftJoin('qrd_info f', 'f.sequenceNo=e.orderCode')
		          ->leftJoin('good g', 'g.spuCode=d.goodNo')
		          ->where($where)
		          ->order('a.addtime desc')
		          ->cursor();
		foreach ($list as $value){
			$value["开票方式"] = $open_type[$value["开票方式"]] ?? '';
			$value["开票种类"] = $inv_type[$value["开票种类"]] ?? '';
			yield $value;
		}
	}

	//进项发票登记导出
	private function B($param=[])
	{
//		$param = $this->request->only(['start' => '', 'end' => '', 'invType' => '', 'company' => '', 'relaComNo' => '', 'supplierNo' => '', 'hpNo' => '', 'payNo' => '', 'invoiceType' => '', 'status' => '', 'invoiceNumber' => '', 'checkApi' => '', 'invoiceCode' => '', 'open_start' => '', 'open_end' => '', 'apply_id' => '', 'apply_name' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.addtime', 'between', [$param['start'], $param['end']]];
		if (($param['companyNo'] != '') && ($param['companyNo'] != '')) $where[] = ["b.companyNo","=",$param['companyNo']];
		$status = [1 => '待系统验证', 2 => '买方公司审核', 3 => '待买方公司认证', 4 => '认证成功', 5 => '验证失败', 6 => '买方审核驳回', 7 => '认证失败', 8 => '回票流程终止', 9 => '验证超次数', 10 => '回票已退'];//认证状态

		$invoiceType = config('invoiceType.invoiceName');//开票种类(发票类型)

		$list = Db::name('pay_invoice')
		          ->alias('a')
		          ->leftJoin('pay b', 'a.payNo=b.payNo and b.status=2')
		          ->leftJoin('invoice_info c', 'c.hpNo=a.hpNo AND c.status=1')
		          ->field('"" 序号,
		          a.payNo 对账编号,
		          a.hpNo 回票申请编号,
		          DATE_FORMAT(a.addtime,\'%Y\') 年,
		          DATE_FORMAT(a.addtime,\'%m\') 月,
		          a.invoiceNumber 发票号,
		          c.total 总额,
		          c.subtotal_amount 金额,
		          c.subtotal_tax 税额,		          
		          a.open_time 开票日期,
		          b.supplierName 供应商,
		          a.invoiceType 发票类型,
		          "" as 业务类型,
		          a.status 认证状态,
		          a.updatetime 认证时间,
		          a.remark 备注')
		          ->where($where)
		          ->order('a.addtime desc')
		          ->cursor();

		$i=1;
		foreach ($list as $item) {
				$item['序号'] = $i;
				$item['业务类型'] = '采购回票';
				$item['认证状态'] = $status[$item['认证状态']]??"";
				$item['发票类型'] = $invoiceType[$item['发票类型']]??"";
				$i++;
				yield  $item;

		}
	}

	//资金认领导出
	private function C($param=[])
	{
//		$param = $this->request->only(['start' => '', 'end' => '', 'name' => '', 'bank' => '', 'status' => '', 'tradNo' => '', 'company' => '', 'userd_lower' => '', 'used_upper' => ''], 'post', 'trim');
		$where = [['a.is_del', '=', 0]];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['b.addtime', 'between', [$param['start'],$param['end']]];
		if (($param['companyNo'] != '') && ($param['companyNo'] != '')) $where[] = ["a.companyNo","=",$param['companyNo']];
		$status = [1 => '未认领', 2 => '部分认领', 3 => '全部认领'];//状态
		$b_status = [1 => '待审批', 2 => '审批通过', 3 => '审批驳回', 4 => '退款', 5 => '解除认领'];//认领审批状态

		$list = Db::name('trade')
		          ->alias('a')
		          ->field('
		          a.companyNo 收款方公司编码,
		          a.trade_in 收款单位名称,
		          a.trade_account 付款银行单位账号,
		          a.trade_out 付款银行单位名称,
		          a.trade_used 付款备注,
		          a.trade_time 交易时间,
		          a.tradNo 资金编号,
		          a.status 状态,
		          a.total_fee 收入金额,
		          a.balance 未认领金额,
		          b.total_fee 已认领金额,
		          b.logNo 资金认领编号,
		          b.customerNo 认领企业,
		          c.orderCode 订单编号,
		          d.cxCode 销售主单号,
		          d.poCode 平台编号,
		          d.goodName 产品名称,
		          d.ownerName 资金创建人,
		          c.apply_name 认领创建人,
		          b.status 认领审批状态,
		          b.remark 驳回原因')
		          ->leftJoin('trade_pool b', 'b.tradNo=a.tradNo and b.is_del=0')
		          ->leftJoin('assoc c', 'c.viceCode=b.logNo and c.type=2 and c.status<>3')
		          ->leftJoin('qrd_info d', 'd.sequenceNo=c.orderCode')
		          ->where($where)
		          ->order('a.trade_time desc')
		          ->cursor();
		foreach ($list as $value){
			$value['状态'] =  $status[$value['状态']] ?? '';
			$value['认领审批状态'] =  $b_status[$value['认领审批状态']] ?? '';
			$value["付款银行单位账号"] .=' ';
	        $value["认领企业"] =Db::name("customer_info")->where(["companyNo"=>$value["认领企业"]])->value("companyName","");

			yield $value;
		}
	}

	//回款核销明细表
	private function D($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0],['b.status',">=",2],["b.type","=",2]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['b.addtime', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['a.companyNo', '=', $param['companyNo']];

		$data = Db::name('assoc')
		          ->alias('b')
		          ->field('DATE_FORMAT(b.addtime,\'%Y\') 年,DATE_FORMAT(b.addtime,\'%m\') 月,DATE_FORMAT(b.addtime,\'%d\') 日,a.companyName 业务公司名称,a.companyNo 业务公司编号,b.viceCode 资金认领编号,c.tradNo 资金编号,c.total_fee 本次核销金额,c.addtime 本次核销时间,c.status 资金认领状态,a.sequenceNo 确认单编号,a.department 业务员部门,a.ownerName 业务员,a.platName 平台类型,a.qrdSource 确认单类型,a.qrdType 商品类型,a.poCode 平台订单号,a.customerAttr 客户属性,a.branch 分公司,a.customerName 客户名称,a.firstCat 一级分类,a.goodName 产品名称,round(a.tax/100,2) 税点,(a.goodNum-a.thNum) 下单数量,a.goodPrice 销售单价,(a.totalPrice-a.th_fee) 销售总额')
		          ->leftJoin('qrd_info a', 'b.orderCode=a.sequenceNo')
		          ->leftJoin('trade_pool c', 'c.logNo=b.viceCode')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();

		//资金认领状态
		$status = [1 => '待审批', 2 => '审批通过', 3 => '审批驳回', 4 => '已解除认领', 5 => '已取消认领'];

		//        $list = [];

		foreach ($data as $value) {
			$value['资金认领状态'] = $status[$value['资金认领状态']] ?? '';
			$value['确认单类型'] = $this->qrdSource[$value['确认单类型']] ?? '';
			$value['商品类型'] = $this->qrdType[$value['商品类型']] ?? '';
			yield $value;
		}
	}

	//回票明细表
	private function E($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0],['a.status', 'in',[3,4]],['b.is_del', '=', 0], ['c.is_del', '=', 0], ['d.is_del', '=', 0]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.check_time', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['b.companyNo', '=', $param['companyNo']];

		$data = Db::name('pay_invoice')
		          ->alias('a')
		          ->field('d.cgdTime 采购单下单日期,
		          b.companyName 业务公司名称,
		          b.companyNo 业务公司编号,
		          a.hpNo 对账回票编号,
		          a.payNo 对账单号,
		          c.cgdNo 采购单编号,
		          a.check_time 本次回票时间,
		          a.invoiceNumber 发票号,
		          a.inv_amount 本次回票金额,
		          d.totalPrice 采购单回票金额,
		          "" 不含税采购成本,
	                "" 税额,
                    d.companyName 公司回票抬头,
                    d.goodNo 商品编号,
                    d.firstCat 一级分类,
                    d.fundCode 核算码,
                    d.goodName 商品名称,
                    d.goodNum 商品数量,
                    d.goodUnit 单位,
                    d.cgdSource 采购单类型,
                    d.ownerName 采购员,
                    d.supplierName 供应商名称,
                    d.supplierNo 供应商编号,
                    d.goodType 商品类型,
                    round(d.tax/100,2) 税率,
                    d.packPrice 包装费,
                    d.certPrice 证书费,
                    d.markPrice 加标费,
                    d.openPrice 开模费,
                    d.costPrice 成本工艺费,
                    d.deliveryPrice 物流费,
                    d.barePrice 成本单价,
                    d.diff_weight 工差,
                    d.diff_fee 工差金额,
                    d.goodPrice 单价,
                    d.totalPrice 采购总货款,
                    d.qrdCode 确认单编号,
                    d.bkCode 备库编号,
                    d.thNum 退货数量,
                    d.th_fee 退货金额,
                    a.exam_remark 回票审核备注,
                    a.remark 回票申请备注')
		          ->leftJoin('pay b', 'a.payNo=b.payNo')
		          ->leftJoin('pay_info c', 'c.payNo=a.payNo and c.status=1 and c.is_del=0')
		          ->leftJoin('cgd_info d', 'd.sequenceNo=c.cgdNo')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();

		//采购单类型
//		$cgdSource = [1 => '直接下单', 2 => '咨询', 3 => '项目', 4 => '平台', 5 => '有赞'];
		//商品类型
		$goodType = [1 => '正常商品', 2 => '赠品', 3 => '样品'];

		//        $list = [];

		foreach ($data as $value) {
			$value['采购单类型'] = $this->cgdSource[$value['采购单类型']] ?? '';
			$value['商品类型'] = $goodType[$value['商品类型']] ?? '';
			$value['不含税采购成本'] = round($value['采购总货款']/(1+$value['税率']),2) ;
			$value['税额'] = round($value['不含税采购成本']*$value['税率'],2) ;

			yield $value;
		}
	}

	//经营分析报表
	private function F($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.createdTime', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['a.companyNo', '=', $param['companyNo']];

		$data = Db::name('qrd_info')
		          ->alias('a')
		          ->field('
            DATE_FORMAT(a.createdTime,\'%Y\') 年,
            DATE_FORMAT(a.createdTime,\'%m\') 月,
            DATE_FORMAT(a.createdTime,\'%d\') 日,
            a.companyName 业务公司名称,
            a.companyNo 业务公司编号,
            a.department 销售部门,
            a.ownerName 销售员,
            a.qrdSource 订单来源,
            a.pay_source 支付来源,
            "" 活动类型,
            a.sequenceNo 确认单编号,
            if(a.cxCode="",a.sequenceNo,a.cxCode) 销售订单号,
            b.bkCode 备库单编号,
            b.sequenceNo 采购单单号,
            a.platName 平台类型,
            a.poCode 平台订单号,
            a.customerAttr 客户属性,
            a.branch 分公司,
            a.customerName 客户名称,
            a.goodNo 商品编码,
            a.firstCat 一级分类,
            a.secCat 二级分类,
            a.thirdCat 三级分类,
            a.goodName 产品名称,
            a.goodUnit 单位,
            a.goodNum 下单数量,
            a.goodPrice 销售单价,
            (a.totalPrice+a.th_fee) 销售总额,
            round(a.tax/100,2) 税点,
            b.ownerName 采购员,
            b.goodPrice 采购裸价,
            b.markPrice 加标费,
            b.packPrice 包装费,
            b.certPrice 证书费,
            b.openPrice 开模费,
            b.costPrice 工艺费,
            b.deliveryPrice 物流费,
            b.goodPrice 采购单价合计,
            (ifnull(b.totalPrice,a.total_origin_price)+ifnull(b.th_fee,0)) 采购总货款,
            (a.totalPrice -ifnull(b.totalPrice,a.total_origin_price))  毛利润,
            round((a.totalPrice -ifnull(b.totalPrice,a.total_origin_price))/a.totalPrice,2) 毛利率,
            round(b.tax/100,2) 采购税点,
            round(b.totalPrice/(1+ round(b.tax/100,2)),2) 不含税采购成本,
            a.qrdType 库存性质,
            b.supplierName 供应商名称,
            b.sendType 发货方式,
            a.thNum 退货数量,
            a.fundCode 财务核算编码')
		          ->leftJoin('cgd_info b', 'b.sequenceNo=a.cgdNo and b.is_del=0')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();

		$list = [];

		foreach ($data as $value) {
			$value['订单来源'] = $this->qrdSource[$value['订单来源']] ?? '';
			$value['库存性质'] = $this->qrdType[$value['库存性质']] ?? '';
			$value['发货方式'] = $this->sendType[$value['发货方式']] ?? '';
			$value['毛利润'] = round(bcsub($value['销售总额'], $value['采购总货款'], 3), 2);
			$value['毛利率'] = $value['销售总额'] == 0 ? 0 : round(bcmul(bcsub(1, bcdiv($value['采购总货款'], $value['销售总额'], 5),5), 100,2), 2) . '%';
			$value['不含税采购成本'] = round(bcdiv($value['采购总货款'], bcadd(1,$value['采购税点'],3), 3), 2);

			yield $value;
		}
	}

	//收入成本明细表
	private function G($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0], ['a.status', '>=', 4]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['c.open_date', 'between', [$param['start'],$param['end']]];
		if ($param['companyNo'] != '') $where[] = ['a.inv_out', '=', $param['companyNo']];

		$data = Db::name('invoice_pool')
		          ->alias('a')
		          ->field('
            a.invNo 发票申请编号,
            m.orderCode 确认单编号,
            if(e.cxCode="",m.orderCode,e.cxCode) 销售主单编号,
            e.qrdSource 销售来源,
            b.seller_title 销售方抬头,
            e.department 业务部门,
            e.ownerName 业务人员,
            m.goodNum 本次开票数量,
            m.totalTax  本次开票金额,
            e.goodPrice 销售单价,
            c.open_date 本次开票时间,
            c.inv_number 发票号,
            m.tax 开票税点,
            a.status 开票状态,
            e.customerName 客户名称,
            e.fundCode 财务核算码,
            e.firstCat 一级分类,
            e.secCat 二级分类,
            e.thirdCat 三级分类,
            e.goodNo 产品编号,
            e.goodName 产品名称,
            f.sequenceNo 采购单编号,
            f.supplierName 卖出方公司,
            f.sendType 发货方式,
            e.qrdType 商品类型,
            f.ownerName 采购员,
            ifnull(f.goodNum,e.goodNum) 采购下单数量,
            f.barePrice 采购裸价,            
            f.markPrice 加标费,
            f.packPrice 包装费,
            f.certPrice 证书费,
            f.openPrice 开模费,
            f.costPrice 工艺费,
            f.deliveryPrice 物流费,
            f.goodPrice 成本合计,
            ifnull(f.totalPrice,e.total_origin_price) 采购货款,
            round(f.tax/100,2) 采购单税点,
            g.returnCode 退票编号,
            if(g.returnCode<>"",a.inv_value,"") 退票金额,
            g.status 退票状态,
            g.return_type 退票方式,
            f.thNum 退货数量,
            f.th_fee 退货金额            
            ')
		          ->leftJoin('invoice_pool_info b', 'b.invNo=a.invNo')
		          ->leftJoin('invoice_ticket c', 'c.invNo=a.invNo and c.type=0 and c.status in (0,1,2,3)')
		          ->leftJoin("invoice_good m","m.invNo=a.invNo and m.invtype=0 and m.is_del=0")
//		          ->leftJoin('assoc d', 'd.viceCode=a.invNo and d.type=1')
		          ->leftJoin('qrd_info e', 'e.sequenceNo=m.orderCode')
		          ->leftJoin('cgd_info f', 'f.sequenceNo=e.cgdNo')
		          ->leftJoin('invoice_return g', 'g.invNo=a.invNo and g.status=2')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();

		$list = [];

		foreach ($data as $value) {
			$value['开票状态'] = $this->invoice_pool_status[$value['开票状态']] ?? '';
			$value['发货方式'] = $this->sendType[$value['发货方式']] ?? '';
			$value['商品类型'] = $this->qrdType[$value['商品类型']] ?? '';
			$value['退票状态'] = $this->invoice_return_status[$value['退票状态']] ?? '';
			$value['销售来源'] = $this->qrdSource[$value['销售来源']] ?? '';
			yield $value;
		}
	}

	//应收台账表
	private function I($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.createdTime', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['a.companyNo', '=', $param['companyNo']];

		$data = Db::name('qrd_info')
		          ->alias('a')
		          ->field('
            a.createdTime 下单时间,        
            a.companyName 业务公司名称,
            a.companyNo 业务公司编号,
            a.sequenceNo 业务订单编号,
            if(a.cxCode="",a.sequenceNo,a.cxCode) 销售订单主编号,
            a.department 销售部门,
            a.ownerName 销售员,
            a.qrdSource 订单来源,
            a.platName 平台类型,
            a.poCode 平台订单号,
            a.customerName 客户名称,
            a.customerAttr 客户属性,
            a.branch 客户分公司,
            a.firstCat 一级分类,
            a.goodName 产品名称,
            round(a.tax/100,2) 税点,
            (a.goodNum - a.thNum) 下单数量,
            a.goodPrice 销售单价,
            a.totalPrice 销售总额,
            a.th_fee 退货总额,
            a.pay_status 回款状态,
            a.apay_fee 总已回款,
            a.pay_fee 回款中,
            a.wpay_fee 总未回款,
            a.pay_tag_fee 回款标签金额,
            a.inv_status 开票状态,
            a.ainv_fee 总已开票,
            a.inv_fee 开票中,
            a.winv_fee 总未开票,
            a.inv_tag_fee 开票标签金额,
            a.invtime 最近开票时间,
            "" 业务提票时间,
            a.paytime 最近回款时间,
            0 账期,
            0 比率,
            "" 订单是否超期,
            "" 开票是否超期,
            a.sendStatus 发货状态,
            "" 最近一次发货时间            
            ')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();
		//        echo Db::name('qrd_info')->getLastSql();die;
		$time = time();

		foreach ($data as $value) {
//			$value['订单来源'] = $this->qrdSource[$value['订单来源']] ?? '';
//			$value['回款状态'] = $this->pay_status[$value['回款状态']] ?? '';
//			$value['开票状态'] = $this->inv_status[$value['开票状态']] ?? '';
//			$value['发货状态'] = $this->sendStatus[$value['发货状态']] ?? '';
			$value['业务提票时间'] =Db::name("assoc")->where(["type"=>1,"status"=>[1,2],"is_del"=>0,'orderCode'=>$value['业务订单编号']])
			                    ->order("id desc")->value('addtime','');
			//计算账期
			$value['账期'] = bcdiv(bcsub($time,strtotime($value['最近回款时间']==''?$value['下单时间']:$value['最近回款时间']), 3), 86400, 0);
			$value['比率'] = round(bcdiv($value['账期'], 30, 4),3);
			$value['订单是否超期'] = $value['比率'] > 6 ? '是' : '否';
			
			$value['开票是否超期'] = round(bcdiv(bcsub($time, strtotime($value['最近开票时间']==''?$value['下单时间']:$value['最近开票时间']), 1), 86400, 1)
			) >
			15 ? '是' : '否';
			
            $value['最近一次发货时间'] = Db::table('fh_source')
                ->whereIn('qrdNo', [$value['业务订单编号'], $value['销售订单主编号']])
                ->order(['id' => 'desc'])
                ->value('send_date', '');
            $value['销售回款'] = Db::name("tag_log")->alias("a")->leftJoin("order_tag b","a.tag_id=b.id")
                ->where(["a.code"=>$value['业务订单编号'],"a.status"=>1,"b.type"=>3])->value("b.tag_name",'');
             $value['销售开票'] = Db::name("tag_log")->alias("a")->leftJoin("order_tag b","a.tag_id=b.id")
                ->where(["a.code"=>$value['业务订单编号'],"a.status"=>1,"b.type"=>4])->value("b.tag_name",'');
			if($value['订单来源']==10)$value['最近一次发货时间']=$value['下单时间'];
			$value['订单来源'] = $this->qrdSource[$value['订单来源']] ?? '';
			$value['回款状态'] = $this->pay_status[$value['回款状态']] ?? '';
			$value['开票状态'] = $this->inv_status[$value['开票状态']] ?? '';
			$value['发货状态'] = $this->sendStatus[$value['发货状态']] ?? '';
			yield $value;
		}

	}

	//用友销票表
	private function J($param=[])
	{
//		$param = $this->request->only(['company' => '', 'start' => '', 'end' => ''], 'post', 'trim');

		$where = [['a.is_del', '=', 0],["a.status","in",[3,4]], ['e.is_del','=', 0]];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.check_time', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['e.companyNo', '=', $param['companyNo']];

		$data = Db::name('pay_invoice')
		          ->alias('a')
		          ->field('
            e.companyName 回票业务公司名称,
            a.hpNo 回票编码,
            e.supplierName 供应商名称,
            a.invoiceNumber 发票号,
            b.item_list,
            "" 发票货物或应税劳务、服务名称,
            "" 回票数量,
            "" 本次回票价款,
            "" 本次回票税额,
            "" 回票税率,
            b.total 本次回票总金额,
            a.check_time 本次回票时间
            ')
		          ->leftJoin('invoice_info b', 'b.hpNo=a.hpNo and b.status=1')
		          ->leftJoin('pay e', 'e.payNo=a.payNo')
		          ->where($where)
		          ->order(['a.id' => 'desc'])
		          ->cursor();

		$list = [];

		foreach ($data as $value) {

			$item_list = json_decode($value['item_list'], true);
			unset($value['item_list']);
			foreach ($item_list as $item) {
                yield $list[] = array_merge($value, [
					'发票货物或应税劳务、服务名称' => $item['name'] ?? '',
					'回票数量' => $item['quantity'] ?? '',
					'本次回票价款' => $item['amount'] ?? '',
					'本次回票税额' => $item['tax'] ?? '',
					'回票税率' => $item['tax_rate'] ?? '',
				]);
			}
		}
//		return $list;
	}

	/** 采购单管理数据
	 * @throws \PHPExcel_Exception
	 * @throws \PHPExcel_Reader_Exception
	 * @throws \PHPExcel_Writer_Exception
	 * @throws \think\db\exception\DataNotFoundException
	 * @throws \think\db\exception\DbException
	 * @throws \think\db\exception\ModelNotFoundException
	 */
	private function K($param=[])
	{
//		$param = $this->request->only(['companyNo' => '', 'start' => '', 'end' => '', "supplierNo" => ''], 'post', 'trim');

		$where = [];

		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['createdTime', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['companyNo', '=', $param['companyNo']];
		$data = Db::name("cgd_info")->where($where)
		          ->field("
        sequenceNo '采购单编号',
        companyName '业务企业',
        supplierName '供应商名称',
        ownerName '采购员',
        department '采购部门',
        createdTime '采购时间',
        cgdType '采购单类型',
        if(qrdCode='',bkCode,qrdCode) '确认单编号/备库单编号',
        if(cxCode='',if(qrdCode='',bkCode,qrdCode),cxCode) '销售单主单号',
        goodNo '商品编号',
        goodName '商品名称',
        firstCat '一级分类',
        secCat '二级分类',
        thirdCat '三级分类',
        fundCode '核算码',
       round(tax/100,2) '税率',
        barePrice '裸价',
        markPrice '加标费',
        certPrice '证书费',
        openPrice '开模费',
        packPrice '包装费',
        costPrice '工艺费',
        deliveryPrice '物流费',
        goodPrice '单价',
        isStock '是否库存',
        diff_fee '工差金额',
        goodNum '商品数量',
        totalPrice '总价',
        sendStatus '发货状态',
        '' as '对账编号',
        '' as '付款状态',
        '' as '回票状态'
        ")->cursor();
		$list = [];
		foreach ($data as $value) {

			$value['采购单类型'] = $this->cgdType[$value['采购单类型']];
			$payinfo = Db::name("pay_info")->alias("a")
			             ->leftJoin("pay b", "a.payNo=b.payNo and b.status=2")
			             ->where(["a.status" => 1, "a.is_del" => 0,'cgdNo'=>$value['采购单编号']])
			             ->field("b.payNo,b.inv_status,b.pay_status")->find();
			$value['对账编号'] = $payinfo['payNo'] ?? "";
			$value['付款状态'] = $this->cgd_pay_status[$payinfo['pay_status'] ?? "1"];
			$value['回票状态'] = $this->cgd_inv_status[$payinfo['inv_status'] ?? "1"];
			$value['是否库存'] = $value['是否库存']==1?'是':'否';
			$value['发货状态'] = $this->sendStatus[$value['发货状态']];
			yield $value;
		}
	}

	/**
	 * 对账单汇总表
	 */
	private function M($param=[])
	{
		$where = [["is_del", "=", 0], ["status", "=", 2]];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['addtime', 'between', [$param['start'], $param['end']]];
		if ($param['companyNo'] != '') $where[] = ['companyNo', '=', $param['companyNo']];
		$data = Db::name("pay")->where($where)
		          ->field("
        companyName '业务公司',
        apply_name '申请人',
        addtime '申请时间',
        supplierName '供应商名称',
         '' as '付款时间',
         payNo '对账编号',
         total_fee '对账总额',
         apay_fee '已付款',
         pay_fee '付款中',
         wpay_fee '未付款',
         pay_status '付款状态',
         pay_tag_fee '付款加签金额',
         pay_tag as '付款加签时间',
         '' as '付款标签名称',
         ainv_fee '已回票',
         inv_fee '回票中',
         winv_fee '未回票',
         inv_status '回票状态',
         inv_tag_fee '回票加签金额',
         inv_tag as '回票加签时间',
         '' as '回票标签名称',
         remark '备注'
        ")->cursor();
		foreach ($data as $value) {
			$tag = Db::name("tag_log")->alias("a")->leftJoin("order_tag b", "a.tag_id=b.id")->where(["a.code" =>
				$value['对账编号'], "a.status" => 1])->column("a.addtime,b.tag_name", "b.type");
			$value['付款加签时间'] = $tag[1]['addtime'] ?? "";
			$value['回票加签时间'] = $tag[2]['addtime'] ?? "";
			$value['回票标签名称'] = $tag[2]['tag_name'] ?? "";
			$value['付款标签名称'] = $tag[1]['tag_name']?? "";
			$value['付款状态'] = $this->pay_status[$value['付款状态']];
			$value['回票状态'] = $this->inv_status[$value['回票状态']];
			$value['付款时间'] = Db::name("pay_payment")->where(['payNo'=>$value['对账编号'],"is_del"=>0])->order("id desc")
			                   ->value('return_time','');

			yield $value;
		}
	}

	//退货台账
	private  function N($param=[]){
		$where = [];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.createtime', 'between', [$param['start'],$param['end']]];
		if ($param['companyNo'] != '') $where[] = ['b.companyNo', '=', $param['companyNo']];
		$list = Db::name("th_source")
		->alias("a")
		->leftJoin("qrd_info b","a.th_qrdNo=b.sequenceNo or a.th_qrdNo=b.cxCode")
		->leftJoin("cgd_info c","b.cgdNo=c.sequenceNo")
		->where($where)
		->field("
	DATE_FORMAT( a.createtime, '%Y' ) 年,
	DATE_FORMAT( a.createtime, '%m' ) 月,
	DATE_FORMAT( a.createtime, '%d' ) 日,
	b.companyName 退货公司名称,
	b.companyNo 退货公司编号,
	b.department 销售部门,
	b.ownerName 销售员,
	b.qrdSource 销售来源,
	b.pay_source 支付渠道,
	'' 活动类型,
	b.sequenceNo 订单编号,
	if(b.cxCode='',b.sequenceNo,b.cxCode) 销售订单编号,
	a.thNo 退货单号,
	b.cgdNo 采购单号,
	b.platName 平台类型,
	b.poCode 平台订单号,
	b.customerAttr 客户属性,
	b.area 分公司,
	b.customerName 客户名称,
	b.goodNo 商品编号,
	b.firstCat 一级分类,
	b.secCat 二级分类,
	b.thirdCat 三级分类,
	b.goodName 商品名称,
	b.goodUnit 商品单位,
	a.th_num 退货数量,
	b.goodPrice 销售单价,
	a.th_qrd_fee 退货金额,
	ifnull(b.tax,0)/100 税点,
	c.ownerName 采购员,
	c.barePrice 采购裸价,
	c.markPrice 加标费,
	c.packPrice 包装费,
	c.certPrice 证书费,
	c.openPrice 开模费,
	c.costPrice 工艺费,
	c.deliveryPrice 物流费,
	c.goodPrice 采购单价,
	round(a.th_num * ifnull(c.goodPrice,b.total_origin_price/b.goodNum),2) 退货采购货款,
	ifnull(c.tax,0)/100 采购税点,
	if(b.isStock=1,'是','否') 是否库存,
	c.supplierName 供应商名称,
	c.sendType 发货方式,
	if(a.is_th=1,'是','否') 供应商是否同意退货,
	'' as 付款方式,
	b.fundCode 财务核算码,
	a.th_remark 退货备注,
	c.bkCode 备库单号
		")->cursor();
		foreach ($list as $value) {
			$value['销售来源']= $this->qrdSource[$value['销售来源']]??"";
			$value['发货方式']= $this->sendType[$value['发货方式']]??"";
			yield $value;
		}
	}
	//产品部门销售业绩
	private function Q($param=[]){
		$where = [["is_del","=",0],["status","in",[0,1,2]]];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['addtime', 'between', [date("Y-m-d 00:00:00",strtotime($param['start'])),date("Y-m-d 23:59:59",
		strtotime($param['end']))]];
//		if ($param['companyNo'] != '') $where[] = ['supplierNo', '=', $param['companyNo']];
		$Db= Db::connect("mysql_wsm");
		$list =$Db->name("sale")->field("orderCode 销售单号,apply_name 业务人员,apply_id 业务部门,cat_id 分类,total_price 产品货款总额,addtime 日期")
		->where($where)
		->cursor();
		foreach ($list as $value){
			$value['业务部门'] = $Db->name("depart_user")->alias("a")->leftjoin("company_item b","a.itemid=b.id")
			->where(["a.is_del"=>0,"a.status"=>1,"a.uid"=>$value['业务部门']])->value("b.name",'');
			$value['分类'] = $Db->name("cat")->where(["id"=>$value['分类']])->value("search","");
			yield $value;
		}
	}

		//采购部门销售业绩
	private function R($param=[]){
		$where = [["a.is_del","=",0],["a.status","=",2],["b.is_stock","=",0]];
		if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.sendtime', 'between', [date("Y-m-d 00:00:00",
		strtotime($param['start'])),date("Y-m-d 23:59:59",strtotime($param['end']))]];
//		if ($param['companyNo'] != '') $where[] = ['supplierNo', '=', $param['companyNo']];
		$Db= Db::connect("mysql_wsm");
		$list =$Db->name("order_out")->alias("a")
		->leftjoin("sale b","a.orderCode=b.orderCode")
		->leftjoin("order_send c","a.outCode=c.outCode")
		->leftjoin("purchease_order d","c.cgdNo=d.cgdNo")
		->field("
			a.outCode 发货单号,
			c.cgdNo 采购单号,
			a.orderCode 销售单号,
			b.platform_id 平台类型,
			d.cgder_id 业务部门,
			d.cgder 采购人员,
			b.order_source 咨询类型,
			a.sendtime 发货时间,
			b.cat_id `一级分类`,
			b.good_name 商品名称,
			a.post_name 物流公司,
			a.post_code 物流单号,
			a.send_num 本次发货数量,
			a.send_num*b.sale_price 本次发货金额,
			b.sale_price 销售单价,
			b.good_num-b.th_num 销售数量,
			b.total_price-b.th_fee 销售金额,
			d.supplier_name `供应商名称`
		")
		->where($where)
		->cursor();
		foreach ($list as $value){
			$value['业务部门'] = $Db->name("depart_user")->alias("a")->leftjoin("company_item b","a.itemid=b.id")
			->where(["a.is_del"=>0,"a.status"=>1,"a.uid"=>$value['业务部门']])->value("b.name",'');
			$value['一级分类'] = $Db->name("cat")->where(["id"=>$value['一级分类']])->value("search","");
			$value['平台类型'] = $Db->name("platform")->where(["id"=>$value['平台类型']])->value("platform_name","");
			$value['咨询类型']=$this->qrdSource[$value['咨询类型']]??"";
			$value['物流单号'].='';
			yield $value;
		}
	}
}