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');

        //cfp_caixiao_data
        $result = Db::name('caixiao_data')
            ->field(true)
//            ->whereBetween('addtime', [date('Y-m-d H:i:s', time() - $this->interval), $date])
            ->where("order_type",1)
            ->json(["data"])->order("addtime desc")
            ->limit(1000)
            ->select()
            ->toArray();
echo  Db::name('caixiao_data')->getLastSql();
        if ($result) {

//            Db::startTrans();

            try {


                $qrd_nos = $cgd_nos = $insert_wait = $all_customer = [];

                foreach ($result as $value) {
                	if(!isset($value['data']['id']) &&$value['data']['id']>0 ) continue;
                    if ($value['order_type'] == 1) {
                        $qrd_nos[] = $value['data']['orderCode'];
                        $all_customer[] = $value['data']['customer_code'];
                    } else $cgd_nos[] = $value['data']['cgdNo'];
                }

                $exists_qrd = Db::name('qrd_info')
                    ->whereIn('sequenceNo', $qrd_nos)
                    ->column('id,sequenceNo,status', 'sequenceNo');

                $exists_cgd = Db::name('cgd_info')
                    ->whereIn('sequenceNo', $cgd_nos)
                    ->column('id,sequenceNo,status', 'sequenceNo');

                //所有客户对应的分公司信息
                $all_branch = Db::name('customer_info')
                    ->whereIn('companyNo', $all_customer)
                    ->column('branch', 'companyNo');


                $qrd_insert_data = $cgd_insert_data = [];

                foreach ($result as $key=> $value) {
                	var_dump($value['data']['id']);
                	if(!isset($value['data']['id']) || $value['data']['id']<=0 ) continue;
                    if ($value['order_type'] == 1) {

                        //已存在
                        if (isset($exists_qrd[$value['data']['orderCode']])) {


                            if ($exists_qrd[$value['data']['orderCode']]['status'] == 0) {
                                //未对账，更新

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

                                    ]);

                            } else {
                                //在对账，存在wait表中
                                $value['id'] = null;

                                $insert_wait[] = $value;

                            }

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

                            ];
                        }


                    } else {
                        //已存在

                        if (isset($exists_cgd[$value['data']['cgdNo']])) {

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

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

                            } else {
                                //在对账，存在wait表中
                                $value['id'] = null;

                                $insert_wait[] = $value;

                            }

                        } else {
                            //新增


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

//                Db::commit();

                $output->writeln('处理成功');

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

        }

    }

}