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

namespace App\Console\Commands\LyhTest;

use App\Helper\Common;
use App\Models\Com\V6WeeklyReport;
use App\Models\Product\Category;
use App\Models\Product\CategoryRelated;
use App\Models\Product\Product;
use App\Models\ProjectAssociation\ProjectAssociation;
use App\Models\RouteMap\RouteMap;
use App\Models\Visit\Visit;
use App\Models\Visit\VisitItem;
use App\Models\Workchat\MessagePush;
use App\Services\ProjectServer;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '导出项目数据';

    public function handle(){
        echo date('Y-m-d H:i:s') . 'start' . PHP_EOL;
        ProjectServer::useProject(535);
        $this->model = new Visit();
        $data = $this->importVisit();
        dd($data);
        DB::disconnect('custom_mysql');
        echo date('Y-m-d H:i:s') . 'end' . PHP_EOL;
        return true;
    }

    /**
     * @remark :导出明细
     * @name   :importVisit
     * @author :lyh
     * @method :post
     * @time   :2025/4/8 11:34
     */
    public function importVisit()
    {
        $lists = $this->model->list();
        if(!empty($lists) && !empty($lists['list'])){
            foreach ($lists as $v){
                $customer_visit_id[] = $v['id'];
            }
            $itemModel = new VisitItem();
            $itemList = $itemModel->list(['customer_visit_id'=>['in',$customer_visit_id]],['customer_visit_id','url']);
            foreach ($lists as $key => $value){
                $sub = [];
                foreach ($itemList as $sonValue){
                    if($value['id'] == $sonValue['customer_visit_id']){
                        $sub[] = $sonValue;
                    }
                }
                $value['sub'] = $sub;
                $lists[$key] = $value;
            }
        }
        return $lists;
    }

    /**
     * @remark :导出访问明细
     * @name   :exportData
     * @author :lyh
     * @method :post
     * @time   :2025/4/8 11:32
     */
    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', '国家ip');
        $sheet->setCellValue('F1', '深度访问页数');
        $sheet->setCellValue('G1', '国家');
        $rowCount = 2;
        foreach ($data as $v) {
            $sheet->setCellValue('A' . $rowCount, $v['created_at']);
            $sheet->setCellValue('B' . $rowCount, $v['referrer_url']);
            $sheet->setCellValue('C' . $rowCount, $v['url']);
            $sheet->setCellValue('D' . $rowCount, $v['device_text']);
            $sheet->setCellValue('E' . $rowCount, $v['ip']);
            $sheet->setCellValue('F' . $rowCount, $v['depth']);
            $sheet->setCellValue('G' . $rowCount, $v['country']);
            $rowCount++;
        }
        // 创建一个新的 Excel Writer 对象
        $writer = new Xlsx($spreadsheet);
        $filename = time().'.xlsx';
        // 设置导出文件的保存路径和文件名
        $filePath = public_path('upload/excel/'.$filename);
        // 导出 Excel 文件
        $writer->save($filePath);
        echo date('Y-m-d H:i:s') . 'file_link:'.url('upload/excel/'.$filename) . PHP_EOL;
        // 返回导出文件的响应
        return ['file_link'=>url('upload/excel/'.$filename)];
    }

//    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', '产品seo_title');
////        $sheet->setCellValue('I1', '产品seo_keyword');
////        $sheet->setCellValue('J1', '产品seo_title');
//        $rowCount = 2;
//        foreach ($data as $v) {
//            $sheet->setCellValue('A' . $rowCount, $v['title']);
////            $sheet->setCellValue('B' . $rowCount, $v['intro']);
////            $sheet->setCellValue('C' . $rowCount, $v['content']);
////            $sheet->setCellValue('D' . $rowCount, $v['url']);
////            $sheet->setCellValue('E' . $rowCount, $v['category_id_text']);
////            $sheet->setCellValue('F' . $rowCount, '发布中');
////            $sheet->setCellValue('G' . $rowCount, $v['images']);
////            $sheet->setCellValue('H' . $rowCount, $v['seo_mate']['title']);
////            $sheet->setCellValue('I' . $rowCount, $v['seo_mate']['keyword']);
////            $sheet->setCellValue('J' . $rowCount, $v['seo_mate']['description']);
//            $rowCount++;
//        }
//        // 创建一个新的 Excel Writer 对象
//        $writer = new Xlsx($spreadsheet);
//        $filename = time().'.xlsx';
//        // 设置导出文件的保存路径和文件名
//        $filePath = public_path('upload/excel/'.$filename);
//        // 导出 Excel 文件
//        $writer->save($filePath);
//        echo date('Y-m-d H:i:s') . 'file_link:'.url('upload/excel/'.$filename) . PHP_EOL;
//        // 返回导出文件的响应
//        return ['file_link'=>url('upload/excel/'.$filename)];
//    }
//
//    public function downloadItem($filed = ['id','depth','created_at','referrer_url','url','device_port','country','ip']){
//        $visitModel = new Visit();
//        $page = 1;
//        $pageSize = 3000;
//        $lists = $visitModel->lists(['updated_date'=>['between',['2025-02-01','2025-02-31']]],$page,$pageSize,'id',$filed);
//        foreach ($lists as $v){
//            $customer_visit_id[] = $v['id'];
//        }
//        $itemModel = new VisitItem();
//        $itemList = $itemModel->list(['customer_visit_id'=>['in',$customer_visit_id]],['customer_visit_id','url']);
//        foreach ($lists as $key => $value){
//            $sub = [];
//            foreach ($itemList as $sonValue){
//                if($value['id'] == $sonValue['customer_visit_id']){
//                    $sub[] = $sonValue;
//                }
//            }
//            $value['sub'] = $sub;
//            $lists[$key] = $value;
//        }
//        return $lists;
//    }

}