<?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 cgdmx extends Command
{
    protected $db="";
    protected function configure()
    {
        // 指令配置
        $this->setName('cgdmx')
            ->setDescription('the cgdmx command');
    }

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

    private function GetData(){
        $date =date("Y-m-d",strtotime("-12 day"));
      $sql="SELECT
    b.sequenceNo As cgdjlNo, 
	b.ShortText1618315935182 AS cgdNo, 
	b.ShortText1618270466672 AS company, 
	b.Date1618315953443 AS cgdtime, 
	b.ShortText1618270412964 AS cgdtype, 
    b.ShortText1618465932373 as delivery_type,
    b.ShortText1617866144054 as is_stock,
	(
	SELECT
		`b_`.`name` 
	FROM
		`h_org_user` `b_` 
	WHERE
	( CONVERT ( `b_`.`id` USING utf8mb4 ) = `b`.`creater` )) AS cgd_saler, 
	b.ShortText1620399144946 AS supplier, 
	b.ShortText1617861287265 AS supplierNo, 
	b.ShortText1619625566608 AS paking, 
	b.ShortText1617865688485 AS cgd_tax, 
	round( `b`.`Number1617865810822`, 2 ) AS pakage_fee, 
	round( `b`.`Number1617865813284`, 2 ) AS cert_fee, 
	round( `b`.`Number1617865807879`, 2 ) AS mark_fee, 
	round( `b`.`Number1617865816181`, 2 ) AS open_fee, 
	round( `b`.`number1618240204358`, 2 ) AS cost_fee, 
	round( `b`.`Number1617865804813`, 2 ) AS naked_fee, 
	round( `b`.`number1618240287778`, 2 ) AS delivery_fee, 
	round(`b`.`Number1619632830397`, 2 ) AS cgd_diff_weight, 
	round( `b`.`Number1619632826654`, 2 ) AS diff_fee, 
	round( `b`.`Number1617865818517`, 2 ) AS cgd_price, 
	cast( `b`.`Number1618240600907` AS signed ) AS cgd_num, 
	round( `b`.`Number1618240685904`, 2 ) AS cgd_total, 
	round( 0, 2 ) AS cgd_cost, 
	b.ShortText1618859161646 AS bkNo, 
	b.Date1618859180209 AS bktime, 
	(
	SELECT
		`b_`.`name` 
	FROM
		`h_org_user` `b_` 
	WHERE
		(
		CONVERT ( `b_`.`id` USING utf8mb4 ) = json_extract( `b`.`StaffSelector1618885082387`, '$[0].id' ))) AS saler, 
	b.ShortText1618900704399 AS warehouse, 
	b.ShortText1617861966146 AS cgd_good_name, 
	b.ShortText1617861001482 AS cgd_good_no, 
	b.ShortText1617865626160 AS cgd_cat_f, 
	c.text1615776646788 AS cgd_cat_s, 
	c.ShortText1618558317611 AS cgd_cat_t, 
	ifnull(c.ShortText1618678132819,'') AS financial, 
	b.ShortText1617865685744 AS cgd_unit, 
	b.ShortText1618240134229 AS glod_type, 
	round( `b`.`Number1618240458074`, 2 ) AS glod_price, 
	round( `b`.`Number1618240480148`, 2 ) AS product_weight, 
round((
		SELECT
				ifnull(sum( `d_`.`number1617817090573` ),0)
		FROM
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '付款' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	) AS `apay_fee`,
	round((
		SELECT
			ifnull(sum( `d_`.`number1617817077461` ),0) 
		FROM
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '回票' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	) AS `inv_fee`,
	round((
		SELECT
			ROUND(`b`.`Number1618240685904`,2)-ifnull(sum( `d_`.`number1617817090573` ),0)-round(ifnull(b.Number1619625483510,0),2) as total
		FROM 
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '付款' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	) AS `wpay_fee`,
	
		round((
		SELECT
			ROUND(`b`.`Number1618240685904`,2)-ifnull(sum( `d_`.`number1617817077461` ),0)-round(ifnull(b.Number1619625483510,0),2)  as total
		FROM
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '回票' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	) AS `winv_fee`,
	
	if(round((
		SELECT
			ROUND(`b`.`Number1618240685904`,2)-ifnull(sum( `d_`.`number1617817090573` ),0)-round(ifnull(b.Number1619625483510,0),2) as total
		FROM 
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '付款' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	)=0,'已付','未付') AS pay_status,
		if(round((
		SELECT
			ROUND(`b`.`Number1618240685904`,2)-ifnull(sum( `d_`.`number1617817077461` ),0)-round(ifnull(b.Number1619625483510,0),2)  as total
		FROM
			`iw48m_CGFKHPMX_form` `d_` 
		WHERE
			((
					`d_`.`ShortText1617816811172` = `b`.`ShortText1618315935182` 
					) 
				AND ( `d_`.`ShortText1615789379545` = '回票' ) 
				AND (
				`d_`.`sequenceStatus` IN ( 'PROCESSING', 'COMPLETED' )))),
		2 
	)=0,'已回','未回') AS inv_status,
	cast( `b`.`Number1618316157066` AS signed ) AS delivery_send, 
	cast( `b`.`Number1618316171848` AS signed ) AS delivery_wsend, 
	b.ShortText1618859321070 AS cgd_delivery_status
	/**b.Number1619625483510 as th_fee,
	b.Number1619625470651 as th_num
	b.modifiedTime updatetime, 
	b.createdTime addtime**/
FROM
	(
		iw48m_CGDJL AS b
		LEFT JOIN
		ibt0b_commodity AS c
		ON 
			(
				(
					c.sequenceNo = b.ShortText1617861001482
				)
			)
	)
WHERE
	(
		(
			1 = 1
		) AND
		(
			b.ShortText1618270412964 IN ('确认单采购','备库采购')
		)
	AND
		date_format( `b`.`modifiedTime`, '%Y-%m-%d' ) >= '{$date}'
	)
ORDER BY
	b.ShortText1618315935182 ASC";
        $data=$this->db->query($sql);
        return $data;
    }
}