php-jiesuan/app/command/qrdmx.php

<?php
declare (strict_types = 1);

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\facade\Cache;
use think\facade\Db;

class qrdmx extends Command
{
    protected $db="";
    protected function configure()
    {
        // 指令配置
        $this->setName('qrdmx')
            ->setDescription('the qrdmx command');
    }

    protected function execute(Input $input, Output $output)
    {
        // 指令输出
        $this->db =Db::connect("mysql2");
        // 指令输出
        $redis = Cache::store('redis');
        $iscgd = $redis->get("qrdmx");
        if($iscgd==0){
            $redis->set("qrdmx",1,1200);
        }else{
            return;
        }
        $data = $this->GetData();
        foreach ($data as $value){
            $ist = Db::table("source_all")->where('productNo',"=",$value['productNo'])->find();

            array_walk($value,function (&$v,$k){
                is_null($v) ? $v="" :"";
                if($v==''&& in_array($k,['paytime','zxfktime','zxtime','cgdtime','qrd_invtime','paytime','ordertime'])){
                    $v=null;
                }
//                if(in_array($k,['qrd_ainv_fee','qrd_winv_fee','qrd_pay_status','qrd_inv_status','qrd_invNo','qrd_apay_fee','qrd_wpay_fee'])){
//                        $v=0;
//                }
            });
//            $value['qrd_winv_fee'] = $value['sale_total'];
//            $value['qrd_wpay_fee'] =$value['sale_total'];
            if($ist){
                Db::table("source_all")->where('productNo',"=",$value['productNo'])->save($value);
            }else{
                Db::table("source_all")->save($value);
            }
        }
        $redis->set("qrdmx",0);
        $output->writeln('qrdmx');
    }

    private function GetData(){
        $date =date("Y-m-d",strtotime("-3 day"));
        $sql="SELECT
	`a`.`ShortText1618447165317` AS `supperinfo`,
	`a`.`ShortText1619523892833` AS `companyName`,
	`a`.`ShortText1619542951283` AS `companyNo`,
     a.ShortText1619543001392 as middle_branch,  
     a.ShortText1619523693355 as area_branch,
       	`b`.`ShortText1620753234895` AS `return_ticket`,
	`b`.`ShortText1620753237335` AS `return_trade`,
     `a`.`ShortText1619413394855` AS `khzzxz`,
     `a`.`ShortText1619413401534` AS `khcomp`,
	`a`.`ShortText1619523689586` AS `suppitem`,
	`a`.`ShortText1617366186330` AS `itemcode`,
	`a`.`ShortText1617023369580` AS `workNo`,
	`a`.`text1617365646297` AS `poNo`,
	`a`.`ShortText1617650701648` AS `qrdNo`,
	`a`.`ShortText1617650669915` AS `qrd_type`,
	`a`.`Date1617081795606` AS `zxfktime`,
	`a`.`createdTime` AS `ordertime`,
          a.`ShortText1618887148862`  as `qrd_delivery`,
      a.`ShortText1617499210157`  as `qrd_stock`,
      IF(a.sequenceStatus='COMPLETED',1,0) as status, 
	(
	SELECT
		`b_`.`name` 
	FROM
		`h_org_user` `b_` 
	WHERE
		(
		CONVERT ( `b_`.`id` USING utf8mb4 ) = json_extract( `a`.`StaffSelector1618445844046`, '$[0].id' ))) AS `sale_name`,
	(
	SELECT
		`b_`.`name` 
	FROM
		`h_org_department` `b_` 
	WHERE
		(
		CONVERT ( `b_`.`id` USING utf8mb4 ) = json_extract( `a`.`StaffSelector1618445850209`, '$[0].id' )))  as 'depart',
       `a`.`sequenceNo` AS `productNo`,
	`a`.`ShortText1617365292699` AS `product_name`,
	`a`.`text1617499162303` AS `product_code`,
	`a`.`ShortText1617499192065` AS `cat_f`,
	`c`.`text1615776646788` AS `cat_t`,
	`c`.`ShortText1618558317611` AS `cat_d`,
	`a`.`ShortText1621217495905` AS `fund_code`,
	`a`.`text1617365597522` AS `unit`,
	`a`.`ShortText1617367958909` AS `tax`,
	`a`.`ShortText1619146965777` AS `qrdkey`,
	round( `a`.`Number1619403905454`, 2 ) AS `diff_weight`,
	round( `a`.`Number1619403908740`, 2 ) AS `diff_price`,
	`a`.`Date1619542686640` AS `paytime`,
	round( `a`.`Number1618248810624`, 2 ) AS `sale_price`,
	cast( `a`.`Number1617365688048` AS signed ) AS `order_num`,
	round( `a`.`Number1618248813613`, 2 ) AS `sale_total`,
	round((
			`a`.`Number1618248813613` -
		IF
			((
					length( `a`.`ShortText1617367958909` ) > 1 
					),(
					`a`.`Number1618248813613` * ( cast( LEFT ( `a`.`ShortText1617367958909`,( length( `a`.`ShortText1617367958909` ) - 1 )) AS DECIMAL ( 10, 0 )) / 100 )),
				0 
			)),
		2 
	) AS `income`,
	'0' AS `gross`,
	`a`.`ShortText1617111725596` AS `zxcode`,
	`a`.`Date1619626098213` AS `zxtime`,
	`a`.`ShortText1617670912205` AS `zxtype`,
	`b`.`ShortText1618315935182` AS `cgdNo`,
	`b`.`Date1618315953443` AS `cgdtime`,
	`b`.`ShortText1618270412964` AS `cgdtype`,(
	SELECT
		`b_`.`name` 
	FROM
		`h_org_user` `b_` 
	WHERE
	( CONVERT ( `b_`.`id` USING utf8mb4 ) = `b`.`creater` )) AS `cgd_saler`,
	`b`.`ShortText1620399144946` AS `suplier_name`,
	`b`.`ShortText1617861287265` AS `suppierNo`,
	round( `a`.`Number1618249202608`, 2 ) AS `qrd_ainv_fee`,
	round( `a`.`Number1618249205231`, 2 ) AS `qrd_winv_fee`,
	`a`.`ShortText1618559274859` AS `qrd_inv_status`,
	'' AS `qrd_invNo`,
	null AS `qrd_invtime`,
	round( `a`.`Number1618249146997`, 2 ) AS `qrd_apay_fee`,
	round( `a`.`number1618249149738`, 2 ) AS `qrd_wpay_fee`,
	`a`.`ShortText1618559043560` AS `qrd_pay_status`,
	'' AS `qrd_pay_code`,
    a.ShortText1618559007040 As 'delivery_status'
FROM
	(((
							`il150_querendanchanpin` `a`
							LEFT JOIN `iw48m_CGDJL` `b` ON (((
										`a`.`ShortText1617650701648` = `b`.`ShortText1617866360004` 
										) 
								AND ( `a`.`ShortText1619146965777` = `b`.`ShortText1619147112929` ))))
						LEFT JOIN `ibt0b_commodity` `c` ON ((
								`c`.`sequenceNo` = `a`.`text1617499162303` 
							)))
					) 
WHERE
	((
			`a`.`sequenceStatus` ='COMPLETED' ) 
    AND ( date_format( `a`.`modifiedTime`, '%Y-%m-%d' ) >= '{$date}' )
	    )
	
ORDER BY
	`a`.`createdTime`,
	`a`.`sequenceNo`";
        $data=$this->db->query($sql);
        return $data;
    }
}