<?php
namespace process;

use FilesystemIterator;
use Workerman\Timer;
use support\Db;
use support\Log;
use support\ApiTask;
use support\LibDb;

class Ask
{
    private $task;
    public function onWorkerStart()
    {
        set_time_limit(3600 * 100);
        ini_set('memory_limit', '20480M');
        
        // 每隔60秒检查一次
        // 执行一次大概15秒
        Timer::add(15, function(){
            $this->task = new ApiTask('ASK');
    		$data = $this->task->get();
    		if($data->enable 
    		   && time()>strtotime($data->begin_start_time) //开始时间
    		   && (empty($data->access_time) || time()-strtotime($data->access_time) > $data->interval)
    		){
                $nowTime = getNowTime();
                if($data->interval >= 86400){ //达到或超过1天时
                    $t = explode(' ',$data->begin_start_time); 
                    $jgTime = $t[1];
                    $t = explode(' ',getNowTime()); 
                    $date = $t[0];
                    $nowTime  = $date .' '. $jgTime;
                }
    			$this->task->begin($nowTime);
                //$beginDate = '1970-01-01';
                //$endDate = '2028-06-30';
                $beginDate = config('app.ask_begin_time');//'2023-01-01 00:00:00';
                $endDate = config('app.ask_end_time');//'2023-06-30 23:59:59';
    			$this->create_smt_ask($beginDate,$endDate);
                $this->task->complete(getNowTime());
    		}
        });
    }
        
    public function create_smt_ask($beginDate,$endDate)
    {
/*
        $db = new LibDb();
        $sql = sprintf("
select distinct * from `copy_ask_quotation_number` 
inner join `copy_pdc_pro_numidx` on (`pn_formart` = `number`) 
inner join `copy_ask_quotation_item` on (`copy_ask_quotation_number`.`quo_item_id` = `copy_ask_quotation_item`.`id`) 
inner join `copy_ask_quotation` on (`copy_ask_quotation`.`no` = `copy_ask_quotation_item`.`no`) 
where (`copy_ask_quotation`.`create_time` >= '%s' and `copy_ask_quotation`.`create_time` <= '%s') 
and (copy_ask_quotation_number.gsp_number is null or copy_ask_quotation_number.gsp_number = '无')
",'2002-01-01','2023-06-30');
        $rows  = $db->query($sql)->fetchAll(\PDO::FETCH_ASSOC);

*/

        $startTime = time();
        //return response(put($this->pdcFactoryList));
        $rows = Db::table('copy_ask_quotation_number')
        //->join('copy_pdc_pro_factory', 'pf_name', '=', 'factory')
        //->join('copy_pdc_pro_numidx', [['pn_pf_id', '=', 'pf_id'],['pn_formart', '=', 'number']])
        ->join('copy_pdc_pro_numidx', [['pn_formart', '=', 'number']])
        ->join('copy_ask_quotation_item', [['copy_ask_quotation_number.quo_item_id', '=', 'copy_ask_quotation_item.id']])
        ->join('copy_ask_quotation', [['copy_ask_quotation.no', '=', 'copy_ask_quotation_item.no']])
        //->select('pf_id','factory','number')
        ->where([
            ['copy_ask_quotation.create_time','>=',$beginDate],
            ['copy_ask_quotation.create_time','<=',$endDate],
         ])
        ->whereIn('copy_ask_quotation_item.result_type',[4,8])
        ->where('copy_ask_quotation.state','>',1)
        ->where(function($q){
            //$q->orWhereRaw("copy_ask_quotation_number.gsp_number is null");
            //$q->orWhereRaw("copy_ask_quotation_number.gsp_number = '无'");
            $q->orWhereRaw("copy_ask_quotation_item.gsp_number is null");
            $q->orWhereRaw("copy_ask_quotation_item.gsp_number = '无'");
        })
        //->where('number','16267N')
        //->orderBy('pf_id')
        ->distinct()
        //->offset(0)
        //->limit(10)
        ->get();
        $facRows = Db::table('copy_ask_pro_c_factory')->get();
        foreach($facRows as $k=>$facRow){
            $facRow->name = str_replace("'","\'",$facRow->name);
            $this->askFactoryList[$facRow->name] = $facRow->id;
            if($facRow->alias){
                $tmpArr = explode(';',$facRow->alias);
                foreach($tmpArr as $tv){
                    if($tv){
                        $tv = str_replace("'","\'",$tv);
                        $this->askFactoryList[$tv] = $facRow->id;
                    }
                }
            }
        }    
        unset($facRows);
       // $facRows = Db::table('copy_pdc_pro_factory')->where('pf_is_gsp','=',0)->get();
        $facRows = Db::table('copy_pdc_pro_factory')->get();
        foreach($facRows as $k=>$facRow){
            $this->pdcFactoryList[$facRow->pf_name] = $facRow->pf_id;
        }    
        unset($facRows);

        //return response(put($rows[0]));
        $insertData = [];
        Db::table('smt_ask')->truncate();
        $i=1;
        $caches=[];
        foreach($rows as $k=>$row){
            if(empty($row->factory)) $row->factory = '';
            $row->factory = str_replace("'","\'",$row->factory);

            //加上互换品牌
            $pdcFactoryArr = [];
            foreach($this->askFactoryList as $tk=>$tv){
                if(isset($this->askFactoryList[$row->factory]) 
                        && $tv==$this->askFactoryList[$row->factory]
                        && isset($this->pdcFactoryList[$tk])){
                    $pdcFactoryArr[] = $this->pdcFactoryList[$tk];
                }
            }
            if(empty($pdcFactoryArr)) $pdcFactoryArr = [0];
            //$row->pf_id = $this->pdcFactoryList[$row->factory] ?? 0;
            $pdcFactoryArr = array_unique($pdcFactoryArr);
            $row->pf_id = implode(',',$pdcFactoryArr);

            $askRows = $this->getAskRows($beginDate,$endDate,$row,$caches);
            foreach($askRows as $askRow){
                if(empty($askRow)) continue;
                $insertData[] = $askRow;
                if(count($insertData)>=1000){
                    Db::table('smt_ask')->insert($insertData);
                    $insertData=[];
                    echo $i."\r\n";
                }
            }
            unset($askRows);
            $i++;
        }
        if(!empty($insertData)) Db::table('smt_ask')->insert($insertData);
        echo "End.";
        unset($rows);
        return;
    }
   
    public function getAskRows($beginDate,$endDate,$row,&$caches)
    {   
        $sql = "SELECT smtComp.c_id,smtComp.c_country,smtComp.c_au_id,
        factory,number,display_number,askHead.no askno,create_time,
        number_remark_cn cn_remark,number_remark_en en_remark
        FROM `copy_ask_quotation_number` askNum
        JOIN `copy_ask_quotation_item` askItem ON askNum.quo_item_id=askItem.id
        JOIN `copy_ask_quotation` askHead ON askItem.no = askHead.no
        JOIN `copy_ask_crm_company` askComp ON askComp.id = askHead.company_id
        JOIN `api_crm_customer` crmComp ON crmComp.AccountNumber = askComp.no
        JOIN `smt_customer` smtComp ON crmComp.guid = smtComp.c_id
        WHERE  factory = '".$row->factory."' AND number= '".$row->number."'
        AND askItem.result_type IN(4,8)
        AND askHead.state > 1
        AND askHead.create_time>='".$beginDate."' AND askHead.create_time<='".$endDate."'
        ";
        $result = Db::select($sql);
        $proRows = $this->getProRows($row);
        $countArr = [
            'c_id_count'=> [],
            'c_id_qty'=> [],
            'ask_count'=> count($result),
            'minTime'=> [],
            'maxTime'=> [],
        ];
        $dataset = [];
        foreach($result as $k=>$v){
            $v = (array)$v;

            if(!isset($countArr['c_id_qty'][$v['c_id']])) $countArr['c_id_qty'][$v['c_id']] = 0;
            $countArr['c_id_qty'][$v['c_id']]++;

            foreach($proRows as $proRow){
                $key = $v['c_id'].'_,_'.$v['factory'].'_,_'.$v['number'].'_,_'.$proRow->smt_p_id; //key去重
                //备注合并
                $remarkRows = [];
                if (isset($this->askFactoryList[$v['factory']]))
                {
                    $remarkRows = Db::select("SELECT remark,remark_en FROM `copy_ask_pro_c_num` WHERE fac_id="
                                .$this->askFactoryList[$v['factory']]." AND number='".$v['number']."' AND remark<>''");
                }
                $cn_remark = '';
                $en_remark = '';
                if(!empty($remarkRows)){
                    foreach($remarkRows as $remarkRow){
                        if(!empty($cn_remark)) $cn_remark.="; \r\n";
                        if(!empty($en_remark)) $en_remark.="; \r\n";
                        $cn_remark .= $remarkRow->remark; 
                        $en_remark .= $remarkRow->remark_en; 
                    }
                }  
                $isSmart = 0;
                $findRow = Db::table('smt_ask_skip')
                        ->where('sas_c_id',$v['c_id'])
                        ->where('sas_p_outnum',$proRow->smt_p_outnum)
                         ->where('sas_asknum',$v['display_number'])
                       ->first();
                if($findRow){
                    $isSmart = 1;
                }else{
                    $findRow = Db::table('smt_report')
                            ->where('smt_report_cid',$v['c_id'])
                            ->where('smt_report_outnum',$proRow->smt_p_outnum)
                            ->where('smt_report_export_time','>=',$v['create_time'])
                            ->where('smt_report_export',1)
                            ->first();
                    if($findRow){
                         $isSmart = 1;
                    }
                }
                $v['cn_remark'] = empty($cn_remark) ? $v['cn_remark'] : $cn_remark;
                $v['en_remark'] = empty($en_remark) ? $v['en_remark'] : $en_remark;
       
                //$v['pdc_factory'] = $proRow->pf_name ? $proRow->pf_name : $row->factory;
                $v['pdc_factory'] = $proRow->pf_name ? $proRow->pf_name : '';
                $v['smt_p_id'] = $proRow->smt_p_id;
                $v['smt_p_cate'] = $proRow->smt_p_cate;
                $v['smt_p_innum'] = $proRow->smt_p_innum;
                $v['smt_p_outnum'] = $proRow->smt_p_outnum;
                $v['find'] = $proRow->find;
                $v['isSmart'] = $isSmart;

                //统计客户数量
                $countArr['c_id_count'][$v['c_id']] = true;
                //统计询单时间段
                if(empty($countArr['minTime'][$v['c_id']]) || $v['create_time'] < $countArr['minTime'][$v['c_id']]) 
                    $countArr['minTime'][$v['c_id']] = $v['create_time'];
                if(empty($countArr['maxTime'][$v['c_id']]) || $v['create_time'] > $countArr['maxTime'][$v['c_id']]) 
                    $countArr['maxTime'][$v['c_id']] = $v['create_time'];

                $dataset[$key] = $v;
            }
        }
        foreach($dataset as $k=>$v){
            if(empty($v['cn_remark'])) $v['cn_remark'] = '';
            if(empty($v['en_remark'])) $v['en_remark'] = '';
            $v['number'] = $v['number'];
            $v['display_number'] = $v['display_number'];
            $v['c_id_count'] = count($countArr['c_id_count']);
            $v['ask_count'] = $countArr['c_id_qty'][$v['c_id']];
            $v['minTime'] = $countArr['minTime'][$v['c_id']];
            $v['maxTime'] = $countArr['maxTime'][$v['c_id']];
            $v['cn_remark'] = substr($v['cn_remark'],0,2048);
            $v['en_remark'] = substr($v['en_remark'],0,2048);
            unset($v['create_time']);
            $dataset[$k] = $v;
        }
        return $dataset;
    }
   
    public function getProRows($row)
    {   
        $sql = "SELECT smt_p_id,smt_p_cate,smt_p_innum,smt_p_outnum,1 find,'' pf_name FROM `copy_pdc_pro_numidx`
        JOIN `smt_pro` ON smt_p_id = pn_p_id
        WHERE pn_pf_id IN (".$row->pf_id.") AND pn_formart= '".$row->number."' AND smt_p_state_main IN ('Y1','Y2','Y3','Y4')
        ORDER BY smt_p_id limit 100
        ";
        $result = Db::select($sql);
        if(empty($result)){
            $sql = "SELECT smt_p_id,smt_p_cate,smt_p_innum,smt_p_outnum,0 find,pf_name  FROM `copy_pdc_pro_numidx`
            JOIN `smt_pro` ON smt_p_id = pn_p_id
            JOIN copy_pdc_pro_factory ON pn_pf_id = pf_id
            WHERE pn_formart= '".$row->number."' AND pf_is_gsp = 0 AND smt_p_state_main IN ('Y1','Y2','Y3','Y4')
            ORDER BY smt_p_id limit 100
            ";
            $result = Db::select($sql);
            //询单自定义有效品牌
            $findRow = Db::table('smt_ask_enable_number')
                ->whereRaw("UPPER(saen_factory)='".strtoupper($row->factory)."'")
                ->first();
            if(!empty($findRow)){
                foreach($result as $k=>$v){
                    $result[$k]->find = 1;
                }
            }
        }
        return $result;
    }
        
}