<?php

namespace app\controller;

use support\Request;
use support\Db;
/**
相关重要索引
ALTER TABLE `gsp_smart`.`copy_ask_quotation_number` ADD INDEX (`factory`, `number`); 
ALTER TABLE `gsp_smart`.`copy_ask_quotation_item` ADD INDEX (`no`); 
ALTER TABLE `gsp_smart`.`copy_ask_quotation` ADD INDEX (`company_id`); 
ALTER TABLE `gsp_smart`.`copy_ask_crm_company` ADD INDEX (`no`); 
ALTER TABLE `gsp_smart`.`api_crm_customer` ADD INDEX (`AccountNumber`); 
*/
class AskController
{
    public $pdcFactoryList = [];
    public $askFactoryList = [];
    public function create_smt_ask(Request $request)
    {
        set_time_limit(3600 * 100);
        ini_set('memory_limit', '4096M');
        $startTime = time();

		$data = [];
        //return response(put($this->pdcFactoryList));
        print_r("create_smt_ask: 0\r\n");
        $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([
            //['pf_is_gsp','=',0],
            //['copy_ask_quotation.create_time','>=','2023-01-01'],
            //['copy_ask_quotation.create_time','<=','2023-06-30'],
         ])
        ->whereNull('copy_ask_quotation_number.gsp_number')
        //->orderBy('pf_id')
        ->distinct()
        //->offset(0)
        //->limit(10)
        ->get();
        //->toSql();
        //return response(put($rows));

        $facRows = Db::table('copy_ask_pro_c_factory')->get();
        foreach($facRows as $k=>$facRow){
            $this->askFactoryList[$facRow->name] = $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);

        print_r("create_smt_ask: 1 rowsCount:".count($rows)."\r\n");
        //return response(put($rows[0]));
        $insertData = [];
        Db::table('smt_ask')->truncate();
        foreach($rows as $k=>$row){
           $row->pf_id = $this->pdcFactoryList[$row->factory] ?? 0;
           $askRows = $this->getAskRows($row);
           foreach($askRows as $askRow){
                $insertData[] = $askRow;
                if(count($insertData)>=1000){
                    echo "create_smt_ask: 2 insertData:".count($insertData)."\r\n";
                    Db::table('smt_ask')->insert($insertData);
                    $insertData=[];
                }
            }
        }
        //return response(put([$insertData]));
        print_r("create_smt_ask: 2 insertData:".count($insertData)."\r\n");
        if(!empty($insertData)) Db::table('smt_ask')->insert($insertData);
        print_r("create_smt_ask: 3\r\n");
		return response(put(['ok',time()-$startTime]));
    }
   
    public function getAskRows($row)
    {   
        $sql = "SELECT smtComp.c_id,smtComp.c_country,smtComp.c_au_id,factory,number,askHead.no askno,create_time
        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."'
        ";
        $result = Db::select($sql);
        $proRows = $this->getProRows($row);
        $countArr = [
            'c_id_count'=> [],
            'ask_count'=> count($result),
            'minTime'=> [],
            'maxTime'=> [],
        ];
        $dataset = [];
        foreach($result as $k=>$v){
           $v = (array)$v;
           foreach($proRows as $proRow){
                //备注合并
                $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<>''");
                }
                $v['cn_remark'] = '';
                $v['en_remark'] = '';
                if(!empty($remarkRows)){
                    foreach($remarkRows as $remarkRow){
                        if(!empty($v['cn_remark'])) $v['cn_remark'].="; \r\n";
                        if(!empty($v['en_remark'])) $v['en_remark'].="; \r\n";
                        $v['cn_remark'] .= $remarkRow->remark; 
                        $v['en_remark'] .= $remarkRow->remark_en; 
                    }
                }          
                $v['pdc_factory'] = $proRow->pf_name ? $proRow->pf_name : $row->factory;
                $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;
                $key = $v['c_id'].','.$v['smt_p_id']; //key去重
                $dataset[$key] = $v;
                //统计客户数量
                $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'];
            }
        }
        foreach($dataset as $k=>$v){
            $v['c_id_count'] = count($countArr['c_id_count']);
            $v['ask_count'] = $countArr['ask_count'];
            $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 = ".$row->pf_id." AND pn_formart= '".$row->number."'
        ORDER BY smt_p_id limit 3
        ";
        $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."'
            ORDER BY smt_p_id limit 3
            ";
            $result = Db::select($sql);
        }
        return $result;
    }
        
}