cxinv/app/command/handleCxData.php

<?php

namespace app\command;

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

//将cfp_caixiao_data里面的数据，分别处理到cfp_qrd_info，cfp_cgd_info(若未对账)，或者cfp_caixiao_wait(已对账)
class handleCxData extends command
{

    private $interval = 15000 * 60;//执行间隔，单位：分（每次同步前5分钟的数据）

    protected function configure()
    {
        parent::configure(); // TODO: Change the autogenerated stub

        $this->setName('handleCxData')->setDescription('处理中间表的采销数据');

    }

    protected function execute(Input $input, Output $output)
    {

        $date = date('Y-m-d H:i:s');

		Db::startTrans();
        $result = Db::name('caixiao_data')
//            ->json(["data"])
            ->withAttr("data",function ($v){return json_decode(str_replace(array("\r\n", "\r", "\n"), '', $v),true);})
            ->where("status",1)
            ->where("addtime",">=",date("Y-m-d H:i:s",strtotime(time()-1800)))
            ->lock(true)
            ->findOrEmpty();
        if ($result) {
            try {
            	$data = $result['data']??[];
                $qrd_insert_data = $wait_insert_data = $cgd_insert_data = [];
					$data['cat_name'] = is_array($data['cat_name'])?$data['cat_name']: json_decode($data['cat_name'],
					true);
                    if ($result['order_type'] == 1) {
                    	$data['total_price'] = $data['total_price'] - $data['th_fee'] - $data['diff_fee'];
                    	$exitReport= Db::name("report_code")->where('qrdNo', $data['orderCode'])->findOrEmpty();
                    	$repostinsert= [
                    			    "qrdNo"=>$data['orderCode'],
                            		"seller_id"=>$data['apply_id'],
                            		"seller"=>$data['apply_name'],
                            		"customerName"=>$data['customer_name'],
                            		"customerNo"=>$data['customer_code'],
                            		"companyNo"=>$data['supplierNo'],
                            		"companyName"=>$data['supplier_name'],
                            		"cgdNo"=>$data['cgdNo'],
                            		];
                    	if(empty($exitReport)) Db::name("report_code")->insert($repostinsert);
                    	else Db::name("report_code")->where($exitReport)->update($repostinsert);
						$exists_qrd = Db::name('qrd_info')
	                    ->whereIn('sequenceNo', $data['orderCode'])
	                    ->field('id,status')->findOrEmpty();
	                    $all_branch = Db::name('customer_info')
	                    ->whereIn('companyNo', $data['customer_code'])
	                    ->column('branch,parent', 'companyNo');
                        //已存在
                        if (!empty($exists_qrd)) {
                            if ($exists_qrd['status'] == 0) {
                            	$report= [
                            		"qrdNo"=>$data['orderCode'],
                            		"seller_id"=>$data['apply_id'],
                            		"seller"=>$data['apply_name'],
                            		"customerName"=>$data['customer_name'],
                            		"customerNo"=>$data['customer_code'],
                            		"companyNo"=>$data['supplierNo'],
                            		"companyName"=>$data['supplier_name'],
                            		"cgdNo"=>$data['cgdNo'],
                            		];
                               $re= Db::name('qrd_info')
                                    ->where('id', $exists_qrd['id'])
                                    ->update([
                                        'name' => $data['orderCode'],//确认单名称,
                                        'sequenceNo' => $data['orderCode'],//确认单编号,
                                        'ownerName' => $data['apply_name'],//销售员,
                                        'ownerid' => $data['apply_id'],//销售员id,
                                        'department' => $data['depart'],//部门,
                                        'createdTime' => $data['addtime'],//销售单创建时间,
                                        'qrdType' => $data['order_type'],//销售单类型,
                                        'qrdSource' => $data['order_source'],//销售单来源,
                                        'companyNo' => $data['supplierNo'],//业务企业编号,
                                        'companyName' => $data['supplier_name'],//业务企业名称,
                                        'customerNo' => $data['customer_code'],//客户编号,
                                        'customerName' => $data['customer_name'],//客户名称,
                                        'poCode' => $data['poNo'],//po编号,
                                        'platName' => $data['platform_id'],//平台名称,
                                        'workCode' => $data['workNo'],//业务编号,
                                        'zxCode' => $data['zxNo'],//咨询单编号,
                                        'goodNo' => $data['good_code'],//商品编号,
                                        'goodName' => $data['good_name'],//商品名称,
                                        'goodBrand' => $data['brand'],//商品品牌,
                                        'firstCat' => $data['cat_name'][0]['cat_name']??"",//一级分类,
                                        'secCat' => $data['cat_name'][1]['cat_name']??"",//二级分类,
                                        'thirdCat' => $data['cat_name'][2]['cat_name']??"",//三级分类,
                                        'catInfo' => json_encode($data['cat_name'],JSON_UNESCAPED_UNICODE),//分类详情,
                                        'fundCode' => $data['cat_name'][2]['fund_code']??"",//核算编码,
                                        'goodMaterial' => '',//商品材质,
                                        'goodUnit' => $data['unit'],//商品单位,
                                        'goodDesc' => $data['cost_desc'],//工艺说明,
                                        'goodType' => $data['good_type'],//商品类型,
                                        'goodModel' => '',//商品型号,
                                        'isStock' => $data['is_stock'],//是否库存品,
                                        'metalsType' => $data['noble_metal'],//贵金属种类,
                                        'weight' => $data['good_weight'],//商品重量,
                                        'goldPrice' => $data['gold_price'],//贵金属单价,
                                        'deliveryDay' => $data['delivery_day'],//物流天数,
                                        'workDay' => $data['lead_time'],//工期,
                                        'tax' => $data['tax'],//税率,
                                        'goodNum' => $data['good_num'],//销售数量,
                                        'goodPrice' => $data['sale_price'],//产品单价,
                                        'totalPrice' => $data['total_price'],//货款总额,
                                        'total_origin_price' => $data['total_origin_price'],//成本总额,
                                        'sendNum' => $data['send_num'],//已发货数量,
                                        'wsendNum' => $data['wsend_num'],//未发货数量,
                                        'apay_fee' => 0,//已付款,
                                        'pay_fee' => 0,//付款中
                                        'wpay_fee' => $data['total_price'],//未付款,
                                        'inv_fee' => 0,//回票中
                                        'ainv_fee' => 0,//已开票,
                                        'winv_fee' => $data['total_price'],//未开票,
                                        'is_diff' => $data['is_diff'],//是否有工差,
                                        'sendType' => $data['send_type'],//发货方式,
                                        'sendStatus' => $data['send_status'],//发货状态,
                                        'pay_status' =>$data['total_price']==0?3:1,//收款状态,1未付，2部分，3完结
                                        'inv_status' => $data['total_price']==0?3:1,//开票状态,,1未付，2部分，3完结
                                        'remark' =>"",//备注,
                                        'cxCode' => $data['oldCode']??"",//备注,
                                        'invtime' => null,
                                        'paytime' => null,
                                        'diff_weight' => $data['diff_weight'],//工差重量,
                                        'diff_fee' => $data['diff_fee'],//工差金额,
                                        'area' => $data['customer_name'],//公司,
                                        'customerAttr' =>$all_branch[$data['customer_code']]['parent'] ?? '',//客户属性,
                                        'branch' => $all_branch[$data['customer_code']]['branch'] ?? '',//分公司,从客户表中查
                                        'cgdNo' => $data['cgdNo'],//采购单号,
                                        'thNum' => $data['th_num'],//退货数量,
                                        'th_fee' => $data['th_fee'],//退货金额,
                                        'pay_source' => $data['pay_source']??"",//支付渠道,
                                        'is_del' => 0,//是否删除,
                                        'addtime' => $date,
                                        'updatetime' => $date,
                                        'inv_tag' => 0,//开票标签,
                                        'pay_tag' => 0,//回款标签,
                                        'inv_tag_fee' => 0,//开票票标签金额,
                                        'pay_tag_fee' => 0,//回款标签金额,

                                    ]);

                            } else {
                            	    $wait_insert_data=[
                            	    	"order_type"=>$result['order_type'],
                            	    	"data"=>json_encode($result['data'],JSON_UNESCAPED_UNICODE),
                            	    	"orderCode"=>$data['orderCode'],
                            	    	"status"=>1,
                            	    ];
                            }

                        } else {
                            //新增
                            $qrd_insert_data[] = [
                                'name' => $data['orderCode'],//确认单名称,
                                'sequenceNo' => $data['orderCode'],//确认单编号,
                                'ownerName' => $data['apply_name'],//销售员,
                                'ownerid' => $data['apply_id'],//销售员id,
                                'department' => $data['depart'],//部门,
                                'createdTime' => $data['addtime'],//销售单创建时间,
                                'qrdType' => $data['order_type'],//销售单类型,
                                'qrdSource' => $data['order_source'],//销售单来源,
                                'companyNo' => $data['supplierNo'],//业务企业编号,
                                'companyName' => $data['supplier_name'],//业务企业名称,
                                'customerNo' => $data['customer_code'],//客户编号,
                                'customerName' => $data['customer_name'],//客户名称,
                                'poCode' => $data['poNo'],//po编号,
                                'platName' => $data['platform_id'],//平台名称,
                                'workCode' => $data['workNo'],//业务编号,
                                'zxCode' => $data['zxNo'],//咨询单编号,
                                'goodNo' => $data['good_code'],//商品编号,
                                'goodName' => $data['good_name'],//商品名称,
                                'goodBrand' => $data['brand'],//商品品牌,
                                'firstCat' => $data['cat_name'][0]['cat_name']??"",//一级分类,
                                'secCat' => $data['cat_name'][1]['cat_name']??"",//二级分类,
                                'thirdCat' => $data['cat_name'][2]['cat_name']??"",//三级分类,
                                'catInfo' => json_encode($data['cat_name'],JSON_UNESCAPED_UNICODE),//分类详情,
                                'fundCode' => $data['cat_name'][2]['fund_code']??"",//核算编码,
                                'goodMaterial' => '',//商品材质,
                                'goodUnit' => $data['unit'],//商品单位,
                                'goodDesc' => $data['cost_desc'],//工艺说明,
                                'goodType' => $data['good_type'],//商品类型,
                                'goodModel' => '',//商品型号,
                                'isStock' => $data['is_stock'],//是否库存品,
                                'metalsType' => $data['noble_metal'],//贵金属种类,
                                'weight' => $data['good_weight'],//商品重量,
                                'goldPrice' => $data['gold_price'],//贵金属单价,
                                'deliveryDay' => $data['delivery_day'],//物流天数,
                                'workDay' => $data['lead_time'],//工期,
                                'tax' => $data['tax'],//税率,
                                'goodNum' => $data['good_num'],//销售数量,
                                'goodPrice' => $data['sale_price'],//产品单价,
                                'totalPrice' => $data['total_price'],//货款总额,
                                'total_origin_price' => $data['total_origin_price'],//成本总额,
                                'sendNum' => $data['send_num'],//已发货数量,
                                'wsendNum' => $data['wsend_num'],//未发货数量,
                                'apay_fee' => 0,//已付款,
                                'pay_fee' => 0,//付款中
                                'wpay_fee' => $data['total_price'],//未付款,
                                'inv_fee' => 0,//回票中
                                'ainv_fee' => 0,//已开票,
                                'winv_fee' => $data['total_price'],//未开票,
                                'is_diff' => $data['is_diff'],//是否有工差,
                                'sendType' => $data['send_type'],//发货方式,
                                'sendStatus' => $data['send_status'],//发货状态,
                                'pay_status' => $data['total_price']==0?3: 1,//收款状态,1未付，2部分，3完结
                                'inv_status' => $data['total_price']==0?3: 1,//开票状态,,1未付，2部分，3完结
                                'remark' => "",//备注,
                                'cxCode' => $data['oldCode']??"",//备注,
                                'invtime' => null,
                                'paytime' => null,
                                'diff_weight' => $data['diff_weight'],//工差重量,
                                'diff_fee' => $data['diff_fee'],//工差金额,
                                'area' => $data['customer_name'],//公司,
                                'customerAttr' => $all_branch[$data['customer_code']]['parent'],//客户属性,
                                'branch' => $all_branch[$data['customer_code']]['branch'] ?? '',//分公司,从客户表中查
                                'cgdNo' => $data['cgdNo'],//采购单号,
                                'thNum' => $data['th_num'],//退货数量,
                                'th_fee' => $data['th_fee'],//退货金额,
                                'pay_source' => $data['pay_source']??"",//支付渠道,
                                'is_del' => 0,//是否删除,
                                'addtime' => $date,
                                'updatetime' => $date,
                                'inv_tag' => 0,//开票标签,
                                'pay_tag' => 0,//回款标签,
                                'inv_tag_fee' => 0,//开票票标签金额,
                                'pay_tag_fee' => 0,//回款标签金额,

                            ];
                        }


                    } else {
                        //已存在
                        $data['total_fee'] = $data['total_fee'] - $data['th_fee'] - $data['diff_fee'];
                        $exitReport= Db::name("report_code")->where('cgdNo', $data['cgdNo'])->findOrEmpty();
                    	$repostinsert= [
                    			    "qrdNo"=>$data['qrdNo'],
                            		"cgder_id"=>$data['cgder_id'],
                            		"cgder"=>$data['cgder'],
                            		"supplierName"=>$data['supplier_name'],
                            		"supplierNo"=>$data['supplierNo'],
                            		"companyNo"=>$data['companyNo'],
                            		"companyName"=>$data['companyName'],
                            		"cgdNo"=>$data['cgdNo'],
                            		];
                    	if(empty($exitReport)) Db::name("report_code")->insert($repostinsert);
                    	else Db::name("report_code")->where($exitReport)->update($repostinsert);
                    $exists_cgd = Db::name('cgd_info')
                    ->whereIn('sequenceNo',  $data['cgdNo'])
                    ->field('id,status')->findOrEmpty();
                        if (!empty($exists_cgd )) {

                            //未对账，更新
                            if ($exists_cgd['status'] == 0) {

                                Db::name('cgd_info')
                                    ->where('id', $exists_cgd['id'])
                                    ->update([
                                        'name' => $data['cgdNo'],//采购单名称',
                                        'sequenceNo' => $data['cgdNo'],//采购单编号',
                                        'ownerName' => $data['cgder'],//采购员名称',
                                        'ownerid' => $data['cgder_id'],//采购员id',
                                        'department' => $data['depart'],//部门名称',
                                        'createdTime' => $data['addtime'],//订单创建时间',
                                        'cgdType' => $data['order_type'],//采购单类型',
                                        'cgdSource' => $data['order_source'],//采购单来源',
                                        'companyNo' => $data['companyNo'],//业务公司编号',
                                        'companyName' => $data['companyName'],//业务公司',
                                        'cgdTime' => $data['addtime'],//采购下单时间',
                                        'bkCode' => $data['bkcode'],//备库单编号',
                                        'qrdCode' => $data['qrdNo'],//确认单编号',
                                        'goodNo' => $data['spuCode'],//商品编号',
                                        'goodName' => $data['good_name'],//商品名称',
                                        'goodType' => $data['good_type'],//商品类型',
                                        'goodBrand' => $data['brand'],//商品品牌',
                                        'goodModel' => '',//商品型号',
                                        'firstCat' => $data['cat_name'][0]['cat_name'],//商品一级分类',
                                        'secCat' => $data['cat_name'][1]['cat_name'],//二级分类',
                                        'thirdCat' => $data['cat_name'][2]['cat_name'],//三级分类',
                                        'catInfo' => json_encode($data['cat_name'],JSON_UNESCAPED_UNICODE),//分类明细',
                                        'fundCode' => $data['cat_name'][2]['fund_code'],//NOT NULL,
                                        'goodMaterial' => '',//商品材质',
                                        'goodUnit' => $data['unit'],//商品单位',
                                        'goodDesc' => $data['cost_desc'],//工艺说明',
                                        'metalsType' => $data['noble_metal'],//贵金属种类',
                                        'weight' => $data['weight'],//商品重量',
                                        'goldPrice' => $data['gold_price'],//贵金属实时金价',
                                        'is_diff' => $data['is_diff'],//是否有工差',
                                        'deliveryDay' => $data['delivery_day'],//物流天数',
                                        'workDay' => $data['lead_time'],//产品工期',
                                        'tax' => $data['tax'],//税点',
                                        'barePrice' => $data['nake_fee'],//裸价',
                                        'markPrice' => $data['mark_fee'],//加标费',
                                        'packPrice' => $data['pakage_fee'],//包装费',
                                        'certPrice' => $data['cert_fee'],//证书费',
                                        'openPrice' => $data['open_fee'],//开模费',
                                        'costPrice' => $data['teach_fee'],//工艺费',
                                        'deliveryPrice' => $data['delivery_fee'],//物流费',
                                        'goodPrice' => $data['good_price'],//成本合计/单价',
                                        'isStock' => $data['is_stock'],//是否库存品',
                                        'goodNum' => $data['good_num'],//下单数量',
                                        'totalPrice' => $data['total_fee'],//采购总货款',
                                        'supplierNo' => $data['supplierNo'],//供应商编号',
                                        'supplierName' => $data['supplier_name'],//供应商名称',
                                        'apay_fee' => 0,//已付款金额',
                                        'wpay_fee' => $data['total_fee'],//未付款金额',
                                        'ainv_fee' => 0,//已开票金额',
                                        'winv_fee' => $data['total_fee'],//未开票金额',
                                        'sendType' => $data['send_type'],//发货方式',
                                        'bkCreater' => $data['bkcreater'],//备库申请人',
                                        'sendStatus' => $data['send_status'],//发货状态',
                                        'wsendNum' => $data['wsend_num'],//未发货数量',
                                        'sendNum' => $data['send_num'],//已发货数量',
                                        'wareHouse' => $data['wsm_code'],//仓库名称',
                                        'wsmCode' => $data['wsm_code'],//仓库编号',
                                        'remark' => '',//备注',
                                        'cxCode' => $data['oldCode']??"",//备注,
                                        'pay_status' => $data['total_fee']==0?3: 1,//付款情况',
                                        'inv_status' => $data['total_fee']==0?3: 1,//开票情况',
                                        'diff_weight' => $data['diff_weight'],//工差重量',
                                        'diff_fee' => $data['diff_fee'],//工差金额',
                                        'thNum' => $data['th_num'],//退货数量',
                                        'th_fee' => $data['th_fee'],//退货金额',
                                        'check_rate' => '',//修正后的税率',
                                        'is_del' => 0,
                                        'addtime' => $date,
                                        'updatetime' => $date,
                                    ]);

                            } else {
								$wait_insert_data=[
                            	    	"order_type"=>$result['order_type'],
                            	    	"data"=>json_encode($result['data'],JSON_UNESCAPED_UNICODE),
                            	    	"orderCode"=>$data['cgdNo'],
                            	    	"status"=>1,
                            	    ];

                            }
                        } else {
                            //新增
                            $cgd_insert_data[] = [
                                'name' => $data['cgdNo'],//采购单名称',
                                'sequenceNo' => $data['cgdNo'],//采购单编号',
                                'ownerName' => $data['cgder'],//采购员名称',
                                'ownerid' => $data['cgder_id'],//采购员id',
                                'department' => $data['depart'],//部门名称',
                                'createdTime' => $data['addtime'],//订单创建时间',
                                'cgdType' => $data['order_type'],//采购单类型',
                                'cgdSource' => $data['order_source'],//采购单来源',
                                'companyNo' => $data['companyNo'],//业务公司编号',
                                'companyName' => $data['companyName'],//业务公司',
                                'cgdTime' => $data['addtime'],//采购下单时间',
                                'bkCode' => $data['bkcode'],//备库单编号',
                                'qrdCode' => $data['qrdNo'],//确认单编号',
                                'goodNo' => $data['spuCode'],//商品编号',
                                'goodName' => $data['good_name'],//商品名称',
                                'goodType' => $data['good_type'],//商品类型',
                                'goodBrand' => $data['brand'],//商品品牌',
                                'goodModel' => '',//商品型号',
                                'firstCat' => $data['cat_name'][0]['cat_name']??"",//商品一级分类',
                                'secCat' => $data['cat_name'][1]['cat_name'],//二级分类',
                                'thirdCat' => $data['cat_name'][2]['cat_name'],//三级分类',
                                'catInfo' => json_encode($data['cat_name'],JSON_UNESCAPED_UNICODE),//分类明细',
                                'fundCode' => $data['cat_name'][2]['fund_code'],//NOT NULL,
                                'goodMaterial' => '',//商品材质',
                                'goodUnit' => $data['unit'],//商品单位',
                                'goodDesc' => $data['cost_desc'],//工艺说明',
                                'metalsType' => $data['noble_metal'],//贵金属种类',
                                'weight' => $data['weight'],//商品重量',
                                'goldPrice' => $data['gold_price'],//贵金属实时金价',
                                'is_diff' => $data['is_diff'],//是否有工差',
                                'deliveryDay' => $data['delivery_day'],//物流天数',
                                'workDay' => $data['lead_time'],//产品工期',
                                'tax' => $data['tax'],//税点',
                                'barePrice' => $data['nake_fee'],//裸价',
                                'markPrice' => $data['mark_fee'],//加标费',
                                'packPrice' => $data['pakage_fee'],//包装费',
                                'certPrice' => $data['cert_fee'],//证书费',
                                'openPrice' => $data['open_fee'],//开模费',
                                'costPrice' => $data['teach_fee'],//工艺费',
                                'deliveryPrice' => $data['delivery_fee'],//物流费',
                                'goodPrice' => $data['good_price'],//成本合计/单价',
                                'isStock' => $data['is_stock'],//是否库存品',
                                'goodNum' => $data['good_num'],//下单数量',
                                'totalPrice' => $data['total_fee'],//采购总货款',
                                'supplierNo' => $data['supplierNo'],//供应商编号',
                                'supplierName' => $data['supplier_name'],//供应商名称',
                                'apay_fee' => 0,//已付款金额',
                                'wpay_fee' => $data['total_fee'],//未付款金额',
                                'ainv_fee' => 0,//已开票金额',
                                'winv_fee' => $data['total_fee'],//未开票金额',
                                'sendType' => $data['send_type'],//发货方式',
                                'bkCreater' => $data['bkcreater'],//备库申请人',
                                'sendStatus' => $data['send_status'],//发货状态',
                                'wsendNum' => $data['wsend_num'],//未发货数量',
                                'sendNum' => $data['send_num'],//已发货数量',
                                'wareHouse' => $data['wsm_code'],//仓库名称',
                                'wsmCode' => $data['wsm_code'],//仓库编号',
                                'remark' => '',//备注',
                                'pay_status' =>$data['total_fee']==0?3: 1,//付款情况',
                                'inv_status' =>$data['total_fee']==0?3: 1,//开票情况',
                                'diff_weight' => $data['diff_weight'],//工差重量',
                                'diff_fee' => $data['diff_fee'],//工差金额',
                                'thNum' => $data['th_num'],//退货数量',
                                'th_fee' => $data['th_fee'],//退货金额',
                                'check_rate' => '',//修正后的税率',
                                'cxCode' => $data['oldCode']??"",//备注,
                                'is_del' => 0,
                                'addtime' => $date,
                                'updatetime' => $date,
                            ];
                        }
                    }

                if ($qrd_insert_data) Db::name('qrd_info')->insertAll($qrd_insert_data);
                if ($cgd_insert_data) Db::name('cgd_info')->insertAll($cgd_insert_data);
                if ($wait_insert_data) Db::name('caixiao_wait')->insert($wait_insert_data);
                Db::name("caixiao_data")->where('uniqkey',$result['uniqkey'])->update(['status'=>0]);
				Db::commit();
                $output->writeln('处理成功');

            } catch (Exception $exception) {
            	Db::rollback();
                $output->writeln($exception->getMessage());
            }

        }
		Db::rollback();
    }

}