<?php

namespace App\Console\Commands\MonthlyCount;

use App\Helper\FormGlobalsoApi;
use App\Models\Domain\DomainInfo;
use App\Models\Inquiry\InquiryFormData;
use App\Models\Project\Project;
use App\Services\ProjectServer;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class InquiryMonthlyCount extends Command
{
    const STATUS_ERROR = 400;
    public $error = 0;
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'month_count';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '询盘月报告统计';

    /**
     * @remark :询盘月报告
     * @name   :handle
     * @author :lyh
     * @method :post
     * @time   :2023/6/30 9:32
     */
    public function handle(){
        $list = DB::table('gl_project')->where('gl_project.extend_type','=',0)
            ->where('gl_project.type','!=',0)
            ->leftJoin('gl_project_deploy_build', 'gl_project.id', '=', 'gl_project_deploy_build.project_id')
            ->leftJoin('gl_project_deploy_optimize', 'gl_project.id', '=', 'gl_project_deploy_optimize.project_id')
            ->select($this->selectParam())->get()->toArray();
        // 获取上个月的开始时间
        $startTime = Carbon::now()->subMonth()->startOfMonth()->toDateString();
        // 获取上个月的结束时间
        $endTime = Carbon::now()->subMonth()->endOfMonth()->toDateString();
        $domainInfo = new DomainInfo();
        foreach ($list as  $value){
            $value = (array)$value;
            if($value['type'] == Project::TYPE_ZERO){
                continue;
            }
            if(!empty($value['domain'])){
                $info = $domainInfo->read(['id'=>$value['domain']]);
                if($info !== false){
                    $value['test_domain'] = $info['domain'];
                }
            }
            $arr = [];
            //按月统计询盘记录
            $arr = $this->inquiryCount($arr,$startTime,$endTime,$value['test_domain'],$value['project_id']);
            $arr = $this->flowCount($arr,$startTime,$endTime,$value['project_id']);
            ProjectServer::useProject($value['project_id']);
            $arr = $this->sourceCount($arr,$value['test_domain'],$startTime,$endTime);
            DB::disconnect('custom_mysql');
            $arr['created_at'] = date('Y-m-d H:i:s');
            $arr['updated_at'] = date('Y-m-d H:i:s');
            $arr['project_id'] = $value['project_id'];
            // 获取当前日期时间
            $arr['month'] = Carbon::now()->subMonth()->format('Y-m');
            DB::table('gl_month_count')->insert($arr);
        }
        return true;
    }

    /**
     * @param $domain
     * @param $project_id
     * @remark :询盘按月统计
     * @name   :inquiryCount
     * @author :lyh
     * @method :post
     * @time   :2023/6/30 14:29
     */
    public function inquiryCount(&$arr,&$startTime,&$endTime,$domain,$project_id){
        $inquiry_list = (new FormGlobalsoApi())->getInquiryList($domain,'',1,100000000);
        //总数
        $arr['total'] = $inquiry_list['data']['total'] ?? 0;
        //数据详情
        $data = $inquiry_list['data']['data'] ?? 0;
        $arr['month_total'] = 0;
        $countryArr = [];
        $arr['country'] = "";
        if(!empty($data)){
            foreach ($data as $v){
                if(($startTime.' 00:00:00' <= $v['submit_time']) && $v['submit_time'] <= $endTime.' 23:59:59'){
                    $arr['month_total']++;
                    if(isset($countryArr[$v['country']])){
                        $countryArr[$v['country']]++;
                    }else{
                        $countryArr[$v['country']] = 1;
                    }
                }
            }
        }
        //加上其他询盘
        ProjectServer::useProject($project_id);
        $arr['total'] += InquiryFormData::count();
        $arr['month_total'] += InquiryFormData::whereBetween('submit_at',[$startTime, $endTime])->count();
        $countryData = InquiryFormData::whereBetween('submit_at',[$startTime, $endTime])
            ->select("country",DB::raw('COUNT(*) as count'))
            ->groupBy('country')->get()->toArray();
        foreach ($countryData as $v1){
            if(isset($countryArr[$v1['country']])){
                $countryArr[$v1['country']] += $v1['count'];
            }else{
                $countryArr[$v1['country']] = $v1['count'];
            }
        }

        arsort($countryArr);
        $top20 = array_slice($countryArr, 0, 15, true);
        $arr['country'] = json_encode($top20);
        return $arr;
    }

    /**
     * @remark :流量统计
     * @name   :flowCount
     * @author :lyh
     * @method :post
     * @time   :2023/6/30 14:31
     */
    public function flowCount(&$arr,&$startTime,&$endTime,$project_id){
        $pv_ip = DB::table('gl_count')
            ->where(['project_id'=>$project_id])
            ->where('date','>=',$startTime.' 00:00:00')
            ->where('date','<=',$endTime.' 23:59:59')
            ->select(DB::raw('SUM(pv_num) as pv_num'), DB::raw('SUM(ip_num) as ip_num'))
            ->first();
        $arr['pv'] = $pv_ip->pv_num;
        $arr['ip'] = $pv_ip->ip_num;
        if($arr['ip'] != 0){
            $arr['rate'] = round(($arr['month_total'] / $arr['ip']) * 10,2);
        }
        return $arr;
    }

    /**
     * @remark :来源访问前8
     * @name   :sourceCount
     * @author :lyh
     * @method :post
     * @time   :2023/6/30 16:14
     */
    public function sourceCount(&$arr,$domain,$startTime,$endTime){
        //访问来源前10
        $source = DB::connection('custom_mysql')->table('gl_customer_visit')
            ->select('referrer_url', DB::raw('COUNT(*) as count'))
            ->groupBy('referrer_url')
            ->whereBetween('updated_date', [$startTime,$endTime])
            ->orderByDesc('count')->limit(10)->get()->toArray();
        $arr['source'] = json_encode($source);
        //访问国家前15
        $source_country = DB::connection('custom_mysql')->table('gl_customer_visit')
            ->select('country',DB::raw('COUNT(*) as ip'),DB::raw('SUM(depth) as pv'))
            ->groupBy('country')
            ->whereBetween('updated_date', [$startTime,$endTime])
            ->orderBy('ip','desc')->limit(15)->get()->toArray();
        $arr['source_country'] = json_encode($source_country);
        //受访界面前15
        $referrer_url = DB::connection('custom_mysql')->table('gl_customer_visit')
            ->select('url',DB::raw('COUNT(*) as num'))
            ->orderBy('num','desc')
            ->whereBetween('updated_date', [$startTime,$endTime])
            ->groupBy('url')
            ->limit(15)->get()->toArray();
        $arr['referrer_url'] = json_encode($referrer_url);
        //访问端口
        $referrer_port = DB::connection('custom_mysql')->table('gl_customer_visit')
            ->select('device_port',DB::raw('COUNT(*) as num'))
            ->orderBy('num','desc')
            ->whereBetween('updated_date', [$startTime,$endTime])
            ->groupBy('device_port')
            ->limit(15)->get()->toArray();
        $arr['referrer_port'] = json_encode($referrer_port);
        return $arr;
    }

    /**
     * @name   :(查询参数设置)selectParam
     * @author :lyh
     * @method :post
     * @time   :2023/6/14 15:00
     */
    public function selectParam(){
        $select = [
            'gl_project.id AS id',
            'gl_project.type AS type',
            'gl_project.extend_type AS extend_type',
            'gl_project_deploy_build.test_domain AS test_domain',
            'gl_project_deploy_optimize.domain AS domain',
            'gl_project_deploy_build.project_id AS project_id',
            'gl_project.cooperate_date AS cooperate_date',
            'gl_project_deploy_build.service_duration AS service_duration',
        ];
        return $select;
    }
}