<?php
/**
 * @remark :
 * @name   :CountProject.php
 * @author :lyh
 * @method :post
 * @time   :2024/9/26 14:19
 */

namespace App\Console\Commands\Project;

use App\Models\Project\Project;
use Illuminate\Console\Command;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class countProject extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'month_count_project';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '项目数据统计生成文件';

    public function handle(){
        $start = '2023-10';
        $end = '2024-09';
        $data = $this->exportDataProject($start,$end);
        $result = $this->exportData($data);
        echo date('Y-m-d H:i:s') . ' ' . json_encode($result) . PHP_EOL;
        return $result;

    }

    public function exportData($data){
        // 创建一个新的 Excel 电子表格实例
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 添加表头
        $sheet->setCellValue('A1', '月份');
        $sheet->setCellValue('B1', '开始时间');
        $sheet->setCellValue('C1', '结束时间');
        $sheet->setCellValue('D1', '创建项目数量');
        $sheet->setCellValue('E1', '上线项目数量');
        $sheet->setCellValue('F1', '上线比例');
        $sheet->setCellValue('G1', '项目总数');
        $sheet->setCellValue('H1', '上线项目总数');
        $sheet->setCellValue('I1', '推广项目总数');
        $sheet->setCellValue('J1', '建站项目总数');
        $sheet->setCellValue('K1', '未上线项目数量');
        $sheet->setCellValue('L1', '删除项目数量');
        $sheet->setCellValue('M1', '上线最快时间');
        $sheet->setCellValue('N1', '上线最慢');
        $sheet->setCellValue('O1', '平均上线天数');
        $rowCount = 2;
//        $allData = $this->countAll();
        foreach ($data as $v) {
            $sheet->setCellValue('A' . $rowCount, $v['month']);
            $sheet->setCellValue('B' . $rowCount, $v['start']);
            $sheet->setCellValue('C' . $rowCount, $v['end']);
            $sheet->setCellValue('D' . $rowCount, $v['month_create_project_count']);
            $sheet->setCellValue('E' . $rowCount, $v['month_project_go_online_count']);
            $sheet->setCellValue('F' . $rowCount, $v['month_project_online_rate']);
            $sheet->setCellValue('G' . $rowCount, $v['count']);
            $sheet->setCellValue('H' . $rowCount, $v['go_online_count']);
            $sheet->setCellValue('I' . $rowCount, $v['promotion_web_count']);
            $sheet->setCellValue('J' . $rowCount, $v['create_web_count']);
            $sheet->setCellValue('K' . $rowCount, $v['no_go_oline_count']);
            $sheet->setCellValue('L' . $rowCount, $v['delete_project_count']);
            $sheet->setCellValue('M' . $rowCount, $v['min_project_count']);
            $sheet->setCellValue('N' . $rowCount, $v['max_project_count']);
            $sheet->setCellValue('O' . $rowCount, $v['average']);
            $rowCount++;
        }
        // 创建一个新的 Excel Writer 对象
        $writer = new Xlsx($spreadsheet);
        $filename = time().'.xlsx';
        // 设置导出文件的保存路径和文件名
        $filePath = public_path('upload/excel/'.$filename);
        // 导出 Excel 文件
        $writer->save($filePath);
        // 返回导出文件的响应
        return ['file_link'=>url('upload/excel/'.$filename)];
    }
    /**
     * @remark :获取所有月份
     * @name   :getMonthsBetween
     * @author :lyh
     * @method :post
     * @time   :2024/9/26 10:43
     */
    public function getMonthsBetween($startDate, $endDate)
    {
        // 创建 DateTime 对象
        $start = new \DateTime($startDate);
        $end = new \DateTime($endDate);
        // 确保结束时间是该月份的最后一天，以包含结束月份
        $end->modify('first day of next month');
        // 用于存储所有月份
        $months = [];
        // 循环遍历每个月
        while ($start < $end) {
            $months[] = $start->format('Y-m');  // 格式化成 YYYY-MM
            $start->modify('+1 month');         // 增加一个月
        }
        return $months;
    }

    /**
     * @remark :根据时间获取开始时间+结束时间
     * @name   :getStartAndEndOfMonth
     * @author :lyh
     * @method :post
     * @time   :2024/9/26 10:44
     */
    public function getStartAndEndOfMonth($month) {
        // 创建指定月份的 DateTime 对象
        $start = new \DateTime($month . '-01');  // 月份的第一天
        // 复制开始日期并获取该月的最后一天
        $end = clone $start;
        $end->modify('last day of this month');  // 修改为该月的最后一天
        // 格式化成 'Y-m-d H:i:s'
        $startTime = $start->format('Y-m-d 00:00:00');  // 开始时间，精确到天的开始
        $endTime = $end->format('Y-m-d 23:59:59');      // 结束时间，精确到天的结束
        return [
            'start' => $startTime,
            'end' => $endTime,
        ];
    }

    /**
     * @remark :导出数据
     * @name   :exportData
     * @author :lyh
     * @method :post
     * @time   :2023/8/1 16:45
     */
    public function exportDataProject($start_month,$end_month){
        $data = [];
        $monthData = $this->getMonthsBetween($start_month,$end_month);
        $projectModel = new Project();
        foreach ($monthData as $v){
            $data[$v]['month'] = $v;
            $timeArr = $this->getStartAndEndOfMonth($v);
            $start_time = $timeArr['start'];
            $end_time = $timeArr['end'];
            $data[$v]['start'] = $start_time;
            $data[$v]['end'] = $end_time;
            //每月创建项目数据
            $data[$v]['month_create_project_count'] = $projectModel->counts(['created_at'=>['between',[$start_time,$end_time]],'deleted_at'=>0]);//每月创建项目数量
            $data[$v]['month_project_go_online_count'] = $projectModel->counts(['uptime'=>['between',[$start_time,$end_time]],'deleted_at'=>0]);//当月上线项目数量
            $data[$v]['month_project_online_rate'] = 0;
            if($data[$v]['month_create_project_count'] != 0){
                $data[$v]['month_project_online_rate'] = round($data[$v]['month_project_go_online_count'] / $data[$v]['month_create_project_count'],2);//比例
            }
            $data[$v]['count'] = $projectModel->counts(['delete_status'=>0]);//所有项目总数
            $data[$v]['go_online_count'] = $projectModel->counts(['uptime'=>['!=',null],'delete_status'=>0]);//上线项目总数
            $data[$v]['promotion_web_count'] = $projectModel->counts(['type'=>3,'delete_status'=>0,'created_at'=>['between',[$start_time,$end_time]]]);//推广项目总数
            $data[$v]['create_web_count'] = $projectModel->counts(['type'=>2,'delete_status'=>0,'created_at'=>['between',[$start_time,$end_time]]]);//建站项目总数
            $data[$v]['no_go_oline_count'] = $projectModel->counts(['uptime'=>null,'delete_status'=>0,'created_at'=>['between',[$start_time,$end_time]]]);//未上线项目数量
            $data[$v]['delete_project_count'] = $projectModel->counts(['delete_status'=>1,'created_at'=>['between',[$start_time,$end_time]]]);//删除
            $min_info = $projectModel->select('*')
                ->selectRaw('DATEDIFF(STR_TO_DATE(uptime, "%Y-%m-%d"), STR_TO_DATE(created_at, "%Y-%m-%d")) AS diff')
                ->whereNotNull('uptime')   // 确保 uptime 字段不为空
                ->where('created_at','<=',$end_time)
                ->whereBetween('created_at', [$start_time,$end_time])
                ->orderByRaw('diff ASC')
                ->first();
            $data[$v]['min_project_count'] = $min_info['diff'];
            $max_info = $projectModel->select('*')
                ->selectRaw('DATEDIFF(STR_TO_DATE(uptime, "%Y-%m-%d"), STR_TO_DATE(created_at, "%Y-%m-%d")) AS diff')
                ->whereNotNull('uptime')   // 确保 uptime 字段不为空
                ->whereBetween('created_at', [$start_time,$end_time])
                ->orderByRaw('diff DESC')
                ->first();
            $data[$v]['max_project_count'] = $max_info['diff'];
            $data[$v]['average'] = ceil(($max_info['diff'] + $min_info['diff']) / 2);
        }
        return $data;
    }

    /**
     * @remark :所有项目总数
     * @name   :countAll
     * @author :lyh
     * @method :post
     * @time   :2024/9/26 15:11
     */
    public function countAll(){
        $projectModel = new Project();
        $data['count'] = $projectModel->counts(['deleted_at'=>0]);//所有项目总数
        $data['go_online_count'] = $projectModel->counts(['uptime'=>['!=',null],'delete_status'=>0]);//上线项目总数
        $data['promotion_web_count'] = $projectModel->counts(['type'=>3,'delete_status'=>0]);//推广项目总数
        $data['create_web_count'] = $projectModel->counts(['type'=>2,'delete_status'=>0]);//建站项目总数
        $data['no_go_oline_count'] = $projectModel->counts(['uptime'=>null,'delete_status'=>0]);//未上线项目数量
        $data['delete_project_count'] = $projectModel->counts(['delete_status'=>1]);//删除项目数量
        $min_info = $projectModel->select('*')
            ->selectRaw('DATEDIFF(STR_TO_DATE(uptime, "%Y-%m-%d"), STR_TO_DATE(created_at, "%Y-%m-%d")) AS diff')
            ->whereNotNull('uptime')   // 确保 uptime 字段不为空
            ->orderByRaw('diff ASC')
            ->first();
        $data['min_project_count'] = $min_info['diff'];
        $max_info = $projectModel->select('*')
            ->selectRaw('DATEDIFF(STR_TO_DATE(uptime, "%Y-%m-%d"), STR_TO_DATE(created_at, "%Y-%m-%d")) AS diff')
            ->whereNotNull('uptime')   // 确保 uptime 字段不为空
            ->orderByRaw('diff DESC')
            ->first();
        $data['max_project_count'] = $max_info['diff'];
        $data['average'] = ceil(($max_info['diff'] + $min_info['diff']) / 2);
        return $data;
    }
}