<?php
/**
 * @remark :
 * @name   :TicketCount.php
 * @author :lyh
 * @method :post
 * @time   :2025/8/7 17:42
 */

namespace App\Console\Commands\Ticket;

use App\Models\Manage\BelongingGroup;
use App\Models\Manage\ManageHr;
use App\Models\Ticket\TicketDailyCount;
use App\Models\Ticket\TicketDailyDeptCount;
use App\Models\Ticket\TicketDailyManageCount;
use App\Models\Ticket\TicketMonthDeptCount;
use App\Models\Ticket\TicketMonthManageCount;
use App\Models\Ticket\TicketWeekDeptCount;
use App\Models\Ticket\TicketWeekManageCount;
use App\Models\WorkOrder\TicketLog;
use App\Models\WorkOrder\TicketProject;
use App\Models\WorkOrder\Tickets;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class TicketCount extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'ticket_count {action}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '日统计工单';

    public $belong = [
        1 => [1,2,3,4,5,6,7,8,9],
        2 => [10,11,12],
    ];

    /**
     * @remark :统计脚本
     * @name   :handle
     * @author :lyh
     * @method :post
     * @time   :2025/8/7 17:45
     */
    public function handle(){
        $action = $this->argument('action');
        if($action == 'manage_action'){
            $this->manage_action();
        }
        if($action == 'manage_week_action'){
            $startOfLastWeek = Carbon::now()->subWeek()->startOfWeek(); // 上周一 00:00:00
            $endOfLastWeek   = Carbon::now()->subWeek()->endOfWeek();   // 上周日 23:59:59
            $this->manage_week_month_action($startOfLastWeek,$endOfLastWeek,(new TicketWeekManageCount()));
        }
        if($action == 'manage_month_action'){
            $startOfLastMonth = Carbon::now()->subMonth()->startOfMonth(); // 上个月 1号 00:00:00
            $endOfLastMonth   = Carbon::now()->subMonth()->endOfMonth();   // 上个月最后一天 23:59:59
            $this->manage_week_month_action($startOfLastMonth,$endOfLastMonth,(new TicketMonthManageCount()));
        }
        if($action == 'dept_action'){
            $this->dept_action();
        }
        if($action == 'dept_week_action'){
            $startOfLastWeek = Carbon::now()->subWeek()->startOfWeek(); // 上周一 00:00:00
            $endOfLastWeek   = Carbon::now()->subWeek()->endOfWeek();   // 上周日 23:59:59
            $this->dept_week_month_action($startOfLastWeek,$endOfLastWeek,(new TicketWeekDeptCount()));
        }
        if($action == 'dept_month_action'){
            $startOfLastMonth = Carbon::now()->subMonth()->startOfMonth(); // 上个月 1号 00:00:00
            $endOfLastMonth   = Carbon::now()->subMonth()->endOfMonth();   // 上个月最后一天 23:59:59
            $this->dept_week_month_action($startOfLastMonth,$endOfLastMonth,(new TicketMonthDeptCount()));
        }
        if($action == 'yesterday_daily_action'){
            $this->yesterday_daily_action();
        }
    }

    /**
     * @remark :按管理员统计（只统计技术组）
     * @name   :manage_action
     * @author :lyh
     * @method :post
     * @time   :2025/8/7 17:45
     */
    public function manage_action(){
        $manageHrModel = new ManageHr();
        $manageList = $manageHrModel->list(['status'=>1,'dept_id'=>['in',[1,2]]],'id',['id','dept_id','manage_id','name','nickname']);
        $date = Carbon::yesterday()->toDateString(); // 昨日时间
        $ticketManageCountModel = new TicketDailyManageCount();
        foreach ($manageList as $item){
            $average_time = null;
            $timeout_ratio = null;
            $this->output('按人员统计：执行的人员名称/id：'.$item['name'].'/'.$item['manage_id']);
            $ticketLogModel = new TicketLog();
            $ticket_num = $ticketLogModel->counts(['engineer_id'=>$item['manage_id'],'is_engineer'=>1,'status'=>['!=',9]]);
            //工单总时长
            $timeCount = $ticketLogModel->formatQuery(['engineer_id'=>$item['manage_id'],'is_engineer'=>1,'status'=>['!=',9]])->sum('end_time');
            $complete_num = $ticketLogModel->counts(['engineer_id'=>$item['manage_id'],'status'=>['!=',9],'is_engineer'=>1,'end_at'=>['!=',null]]);
            if(!empty($timeCount)){
                $average_time = round($timeCount / $complete_num, 3);
            }
            //最快完成的时间
            $fastest_time = $ticketLogModel->formatQuery(['engineer_id'=>$item['manage_id'],'status'=>['!=',9],'is_engineer'=>1,'end_at'=>['!=',null]])->min('end_time');
            //最快完成时间
            if(!$fastest_time){
                $fastest_time = null;
            }
            //超时工单数量
            $timeout_num = $ticketLogModel
                ->where('engineer_id', $item['manage_id'])->where('is_engineer', 1)->where('status','!=',9)
                ->where(function ($query) {
                    $query->where(function ($q) {
                        $q->whereNotNull('end_at')->whereColumn('plan_end_at', '<', 'end_at');
                    })->orWhere(function ($q) {
                        $q->whereNull('end_at')->where('plan_end_at', '<', now());
                    });
                })
                ->count();
            if(!empty($timeout_num)){
                $timeout_ratio = round($timeout_num / $ticket_num, 3);
            }
            $date = date('Y-m-d');
            $data = [
                'date'=>$date,
                'manage_id'=>$item['id'],
                'manage_name'=>$item['name'],
                'ticket_num'=>$ticket_num,//工单总数量
                'average_time'=>$average_time ?? null,//平均完成工单时长
                'fastest_time'=>$fastest_time ?? null,//最快完成工单时间
                'timeout_num'=>$timeout_num,//超时工单数量
                'complete_num'=>$complete_num,//完成工单数量
                'dept_id'=>$item['dept_id'],
                'timeout_ratio'=>$timeout_ratio ?? null,
                'complete_ratio'=>round($timeCount / $complete_num, 2)
            ];
            //查询当前用户是否当日已有记录
            $ticketManageInfo = $ticketManageCountModel->read(['date'=>$date,'manage_id'=>$item['id']],['id']);
            if($ticketManageInfo === false){
                //TODO::执行新增
                $ticketManageCountModel->addReturnId($data);
            }else{
                //TODO::执行编辑
                $ticketManageCountModel->edit($data,['id'=>$ticketManageInfo['id']]);
            }
        }
        return true;
    }

    /**
     * @remark :按周/月统计数据
     * @name   :manage_week_action
     * @author :lyh
     * @method :post
     * @time   :2025/8/30 9:36
     */
    public function manage_week_month_action($startOfLast,$endOfLast,$model){
        $manageHrModel = new ManageHr();
        $manageList = $manageHrModel->list(['status'=>1,'dept_id'=>['in',[1,2]]],'id',['id','dept_id','manage_id','name','nickname']);
        $ticketLogModel = new TicketLog();
        foreach ($manageList as $item){
            $timeout_ratio = $average_time = null;
            $this->output('按人员统计：执行的人员名称/id：'.$item['name'].'/'.$item['manage_id']);
            //上一周新增工单总数
            $add_num = $ticketLogModel->counts(['created_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>$item['manage_id'],'is_engineer'=>1,'status'=>['!=',9]]);
            //上一周完成工单(创建+完成都在当周，算一条有效数据)
            $complete_num = $ticketLogModel->counts(['created_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>$item['manage_id'],'status'=>['!=',9],'is_engineer'=>1,'end_at'=>['between',[$startOfLast,$endOfLast]]]);
            //上一周最快完成时长
            $fastest_time = $ticketLogModel->formatQuery(['engineer_id'=>$item['manage_id'],'status'=>['!=',9],'is_engineer'=>1,'end_at'=>['between',[$startOfLast,$endOfLast]]])->min('end_time');
            //上一周完成工单总时长
            $timeCount = $ticketLogModel->formatQuery(['created_at'=>['between',[$startOfLast,$endOfLast]],'end_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>$item['manage_id'],'is_engineer'=>1,'status'=>['!=',9]])->sum('end_time');
            if(!empty($timeCount)){
                $average_time = round($timeCount / $complete_num, 3);
            }
            $data = [
                'manage_id'=>$item['id'],
                'manage_name'=>$item['name'],
                'add_num'=>$add_num,//上一周新增工单数量
                'average_time'=>$average_time ?? null,//平均完成工单时长
                'fastest_time'=>$fastest_time ?? null,//最快完成工单时间
                'complete_num'=>$complete_num,//完成工单数量
                'dept_id'=>$item['dept_id'],
                'timeout_ratio'=>$timeout_ratio ?? null,
                'start_at'=>$startOfLast,
                'end_at'=>$endOfLast
            ];
            //查询当前用户是否当日已有记录
            $ticketManageInfo = $model->read(['start_at'=>$startOfLast,'end_at'=>$endOfLast,'manage_id'=>$item['id']],['id']);
            if($ticketManageInfo === false){
                //TODO::执行新增
                $model->addReturnId($data);
            }else{
                //TODO::执行编辑
                $model->edit($data,['id'=>$ticketManageInfo['id']]);
            }
        }
        return true;
    }

    /**
     * @remark :按技术组统计数据
     * @name   :dept_action
     * @author :lyh
     * @method :post
     * @time   :2025/8/8 11:48
     */
    public function dept_action(){
        $belongingGroupModel = new BelongingGroup();
        $groupList = $belongingGroupModel->list(['id'=>['in',[1,2,3,4,5,6,7,8,9]]],'id',['id','name']);
        $manageHrModel = new ManageHr();
        $ticketLogModel = new TicketLog();
        $ticketDailyDeptModel = new TicketDailyDeptCount();
        $date = Carbon::yesterday()->toDateString(); // "2025-08-07"
        foreach ($groupList as $item){
            $timeout_ratio = $average_time = null;
            $this->output('组统计：执行的组/id：'.$item['name'].'/'.$item['id']);
            $manageIdArr = $manageHrModel->selectField(['belong_group'=>$item['id'],'status'=>['!=',9],'status'=>1,'dept_id'=>1],'manage_id');
            $ticket_num = $ticketLogModel->counts(['engineer_id'=>['in',$manageIdArr],'is_engineer'=>1,'status'=>['!=',9]]);
            $timeCount = $ticketLogModel->formatQuery(['engineer_id'=>['in',$manageIdArr],'status'=>['!=',9],'is_engineer'=>1])->sum('end_time');
            if(!empty($timeCount)){
                $average_time = round($timeCount / $ticket_num, 3);
            }
            //超期工单数量
            $timeout_num = $ticketLogModel
                ->whereIn('engineer_id', $manageIdArr)
                ->where('is_engineer', 1)
                ->where('status','!=',9)//排除掉作废工单
                ->where(function ($query) {
                    $query->where(function ($q) {
                        $q->whereNotNull('end_at')
                            ->whereColumn('plan_end_at', '<', 'end_at');
                    })->orWhere(function ($q) {
                        $q->whereNull('end_at')
                            ->where('plan_end_at', '<', now());
                    });
                })
                ->count();
            if(!empty($timeout_num)){
                $timeout_ratio = round($timeout_num / $ticket_num, 3);
            }
            $data = [
                'date'=>$date,
                'dept_id'=>$item['id'],
                'dept_name'=>$item['name'],
                'ticket_num'=>$ticket_num ?? 0,
                'average_time'=>$average_time ?? null,
                'timeout_ratio'=>$timeout_ratio ?? null,
                'timeout_num'=>$timeout_num
            ];
            $deptInfo = $ticketDailyDeptModel->read(['date'=>$date,'dept_id'=>$item['id']],['id']);
            if($deptInfo === false){
                $ticketDailyDeptModel->addReturnId($data);
            }else{
                $ticketDailyDeptModel->edit($data,['id'=>$deptInfo['id']]);
            }
        }
        return true;
    }

    /**
     * @remark :技术组按周统计
     * @name   :dept_week_month_action
     * @author :lyh
     * @method :post
     * @time   :2025/8/30 14:01
     */
    public function dept_week_month_action($startOfLast,$endOfLast,$model){
        $belongingGroupModel = new BelongingGroup();
        $groupList = $belongingGroupModel->list(['id'=>['in',[1,2,3,4,5,6,7,8,9]]],'id',['id','name']);
        $manageHrModel = new ManageHr();
        $ticketLogModel = new TicketLog();
        foreach ($groupList as $item){
            $timeout_ratio = $average_time = null;
            $this->output('组统计：执行的组/id：'.$item['name'].'/'.$item['id']);
            $manageIdArr = $manageHrModel->selectField(['belong_group'=>$item['id'],'status'=>['!=',9],'status'=>1,'dept_id'=>1],'manage_id');
            //本周新增工单
            $add_num = $ticketLogModel->counts(['created_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>['in',$manageIdArr],'is_engineer'=>1,'status'=>['!=',9]]);
            $complete_num = $ticketLogModel->counts(['end_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>['in',$manageIdArr],'is_engineer'=>1,'status'=>['!=',9]]);
            $timeCount = $ticketLogModel->formatQuery(['end_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>['in',$manageIdArr],'status'=>['!=',9],'is_engineer'=>1])->sum('end_time');
            if(!empty($timeCount)){
                $average_time = round($timeCount / $complete_num, 3);
            }
            //超期工单数量
            $timeout_num = $ticketLogModel
                ->whereIn('engineer_id', $manageIdArr)
                ->where('is_engineer', 1)
                ->where('status','!=',9)//排除掉作废工单
                ->where(function ($query) use ($startOfLast,$endOfLast) {
                    $query->where(function ($q) use ($startOfLast,$endOfLast) {
                        $q->whereBetween('plan_end_at',[$startOfLast,$endOfLast])->whereNotNull('end_at')->whereColumn('plan_end_at', '<', 'end_at');
                    })->orWhere(function ($q) {
                        $q->whereNull('end_at')->where('plan_end_at', '<', now());
                    });
                })
                ->count();
            //预期结束时间在本周的所有工单
            $ticket_num = $ticketLogModel->counts(['plan_end_at'=>['between',[$startOfLast,$endOfLast]],'engineer_id'=>['in',$manageIdArr],'is_engineer'=>1,'status'=>['!=',9]]);
            if(!empty($timeout_num)){
                $timeout_ratio = round($timeout_num / $ticket_num, 3);
            }
            $data = [
                'dept_id'=>$item['id'],
                'dept_name'=>$item['name'],
                'add_num'=>$add_num ?? 0,
                'complete_num'=>$complete_num ?? 0,
                'average_time'=>$average_time ?? null,
                'timeout_ratio'=>$timeout_ratio ?? null,
                'timeout_num'=>$timeout_num,
                'start_at'=>$startOfLast,
                'end_at'=>$endOfLast
            ];
            //查询当前用户是否当日已有记录
            $ticketManageInfo = $model->read(['start_at'=>$startOfLast,'end_at'=>$endOfLast,'dept_id'=>$item['id']],['id']);
            if($ticketManageInfo === false){
                //TODO::执行新增
                $model->addReturnId($data);
            }else{
                //TODO::执行编辑
                $model->edit($data,['id'=>$ticketManageInfo['id']]);
            }
        }
        return true;
    }
    /**
     * @remark :技术组所有工单记录
     * @name   :daily_action
     * @author :lyh
     * @method :post
     * @time   :2025/8/8 14:33
     */
    public function yesterday_daily_action(){
        $ticketModel = new Tickets();
        $ticketLogModel = new TicketLog();
        $ticketLogModel->whereIn('ticket_id', function ($query) {
            $query->select('id')->from('gl_tickets')->where('status', 9);
        })->update(['status' => 9]);
        $date = Carbon::yesterday()->toDateString(); // "2025-08-07"
        $ticket_num = $ticketModel->counts(['id'=>['!=',0]]);
        $time_end_num = $ticketModel->counts(['end_at'=>['!=',null]]);//已完成的工单
        $time_end_count = $ticketModel->formatQuery(['end_at'=>['!=',null]])->sum('end_time');//已完成工单时长
        if(!empty($time_end_count)){
            $average_time = round($time_end_count / $time_end_num, 3);
        }
        $add_num = $ticketModel->counts(['created_at'=>['between',[$date.' 00:00:00',$date.' 23:59:59']]]);
        $untreated_num = $ticketModel->counts(['end_at'=>null]);
        $processed_num = $ticketModel->counts(['end_at'=>['between',[$date.' 00:00:00',$date.' 23:59:59']]]);
        $submit_a_side = $ticketModel->formatQuery(['submit_side'=>1])->sum('submit_side');
        $submit_b_side = $ticketModel->formatQuery(['submit_side'=>2])->sum('submit_side');
        $dbResult = DB::table('gl_ticket_projects as p')->leftJoin('gl_tickets as t', 'p.id', '=', 't.project_id')
            ->select(
                'p.project_cate', DB::raw('COUNT(t.id) as ticket_count')
            )->groupBy('p.project_cate')->pluck('ticket_count', 'project_cate');
        $timeout_num = $ticketModel->where('status','!=',9)
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->whereNotNull('end_at')->whereColumn('plan_end_at', '<', 'end_at');
                })->orWhere(function ($q) {
                    $q->whereNull('end_at')->where('plan_end_at', '<', now());
                });
            })
            ->count();
        $timeout_ratio = null;
        if(!empty($timeout_num)){
            $timeout_ratio = round($timeout_num / $ticket_num, 3);
        }
        $data = [
            'date' => $date,
            'ticket_num'=>$ticket_num,
            'add_num'=>$add_num,
            'untreated_num'=>$untreated_num,
            'processed_num'=>$processed_num,
            'average_time'=>$average_time ?? null,
            'source'=>json_encode(['a'=>$submit_a_side,'b'=>$submit_b_side],true),
            'source_type'=>json_encode($dbResult,true),
            'timeout_num'=>$timeout_num,
            'timeout_ratio'=>$timeout_ratio ?? null
        ];
        $ticketDailyModel = new TicketDailyCount();
        $ticketDailyInfo = $ticketDailyModel->read(['date'=>$date],['id']);
        if($ticketDailyInfo === false){
            $ticketDailyModel->addReturnId($data);
        }else{
            $ticketDailyModel->edit($data,['id'=>$ticketDailyInfo['id']]);
        }
        return true;
    }

    /**
     * @remark :日志
     * @name   :output
     * @author :lyh
     * @method :post
     * @time   :2025/8/8 11:43
     */
    public function output($message)
    {
//        Log::channel('ticket_log')->info($message);
        echo date('Y-m-d H:i:s') . ' ' . $message . PHP_EOL;
    }
}