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

    protected function execute(Input $input, Output $output)
    {
        $redis = Cache::store('redis');
        $iscgd = $redis->get("qrd");
        if($iscgd==0){
            $redis->set("qrd",1,1200);
        }else{
            return;
        }
        $this->db =Db::connect("mysql2");
        $data = $this->GetData();
        foreach ($data as $value){
            $qrd = Db::name("qrd")->where("sequenceNo","=",$value['sequenceNo'])->find();
                $info = Db::name("qrd_info")->where("ShortText1617650701648","=",$value['sequenceNo'])
                    ->field(' ifnull(round(sum(Number1618249146997),2),0) as apay_fee ,ifnull(round(sum(number1618249149738),2),0) as 
                    wpay_fee,ifnull(round(sum(Number1618249202608),2),0) as ainv_fee,ifnull(round(sum(Number1618249205231),2),0) as winv_fee,sum(inving_fee) as inv_fee,sum(paying_fee) as pay_fee,
                    ShortText1617650701648')->group('ShortText1617650701648')->find();
                if(empty($info)){
                    continue;
                }
                $value['wpay_fee']=$info['wpay_fee'];
                $value['apay_fee']=$info['apay_fee'];
                $value['winv_fee']=$info['winv_fee'];
                $value['ainv_fee']=$info['ainv_fee'];
            $value['inv_fee']=$info['inv_fee'];
            $value['pay_fee']=$info['pay_fee'];
            $value['total_fee'] = round($value['total_fee'],2);
            $value['pay_status'] = $info['wpay_fee']+$info['pay_fee']==0 ? 3:($info['wpay_fee']+$info['pay_fee']<$value['total_fee']?2:1);
            $value['inv_status'] = $info['winv_fee']+$info['inv_fee']==0 ? 3:($info['winv_fee']+$info['inv_fee']<$value['total_fee']?2:1);
            var_dump($value['name']);
            if(!$qrd){
                Db::name("qrd")->insert($value);
            }else{
              Db::name("qrd")->where("sequenceNo","=",$value['sequenceNo'])->save($value);
          }
        }
        $redis->set("qrd",0);
    }

    private  function GetData(){
        $date = date("Y-m-d H:i:s",strtotime("-3 day"));
        $sql = "SELECT 
	NAME name,
	sequenceNo,
	createdTime,
	(SELECT
		`b_`.`name` 
	FROM
		`h_org_user` `b_` 
	WHERE
		(
		CONVERT ( `b_`.`id` USING utf8mb4 ) = json_extract( `StaffSelector1617031034341`, '$[0].id' ))) as sales_name,
	
	(SELECT
		`b_`.`name` 
	FROM
		`h_org_department` `b_` 
	WHERE
		(
		CONVERT ( `b_`.`id` USING utf8mb4 ) = json_extract( `StaffSelector1617031050718`, '$[0].id' ))) as sales_depart,
	Number1617648665166 as total_fee,
	ShortText1617648743587 mobile,
	ShortText1617648745795 contactor, 
	Date1617672422547 as qrddate,
	ShortText1617774189420 as customer,
	text1617774207439 as companyNo,
	ShortText1618266846625 supplerName,
	ShortText1619406464190 addr,
	ifnull(ShortText1619541934512,'') depart,
	1 as type,
	1 inv_status,
	0 ainv_fee,
	Number1617648665166 winv_fee,
	Number1617648665166 wpay_fee,
	0 apay_fee,
	1 pay_status,
	1	status,
	createdTime addtime,
	modifiedTime updatetime
FROM
	il150_querendan
	where sequenceStatus = 'COMPLETED' and modifiedTime>='{$date}'";
        $data=$this->db->query($sql);
        return $data;
    }
}