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

namespace App\Console\Commands\LyhTest;

use App\Enums\Common\Code;
use App\Helper\Common;
use App\Http\Requests\Aside\WorkOrder\AsideTicketListRequest;
use App\Models\Blog\Blog;
use App\Models\Com\V6WeeklyReport;
use App\Models\Manage\Manage;
use App\Models\Manage\ManageHr;
use App\Models\News\News;
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\Template\BTemplate;
use App\Models\Visit\Visit;
use App\Models\Visit\VisitItem;
use App\Models\Workchat\MessagePush;
use App\Models\WorkOrder\Tickets;
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') . 'start1' . PHP_EOL;
//        ProjectServer::useProject(671);
        $this->downloadTicket();
//        DB::disconnect('custom_mysql');
        echo date('Y-m-d H:i:s') . 'end1' . PHP_EOL;
        return true;
    }

    /**
     * @remark :新闻导入到博客
     * @name   :newsImportBlog
     * @author :lyh
     * @method :post
     * @time   :2025/6/7 11:12
     */
    public function newsImportBlog(){
        $newsModel = new News();
        $newsLists = $newsModel->list(['status'=>1]);
        $blogModel = new Blog();
        foreach ($newsLists as $item){
            $data = [
                'name'=>$item['name'],
                'status'=>1,
                'remark'=>$item['remark'],
                'text'=>$item['text'],
                'image'=>$item['image'],
                'url'=>$item['url'],
                'seo_title'=>$item['seo_title'],
                'seo_description'=>$item['seo_title'],
                'seo_keywords'=>$item['seo_title'],
                'project_id'=>671,
                'release_at'=>$item['release_at'],
                'og_image'=>$item['og_image']
            ];
            $id = $blogModel->addReturnId($data);
            RouteMap::delRoute(RouteMap::SOURCE_NEWS,$item['id'],671);
            $route = RouteMap::setRoute($item['url'],RouteMap::SOURCE_BLOG,$id,671);
            $blogModel->edit(['url'=>$route],['id'=>$id]);
            //更新当前的可视化归宿
            $templateModel = new BTemplate();
            $templateModel->edit(['source'=>3,'source_id'=>$id],['source'=>4,'source_id'=>$item['id'],'is_custom'=>0,'is_list'=>0]);
        }
        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   :downloadTicketList
     * @author :lyh
     * @method :post
     * @time   :2025/9/13 15:24
     */
    /**
     * A端查看所有工单
     *
     * @return \Illuminate\Http\Response
     */
    public function downloadTicket()
    {
        $this->param['dept_id'] = 2;
        $this->param['entry_position'] = 2;
        $search = '资料上传';
        $query = Tickets::with([
            'logs.engineer',
            'project.pm',
            'project.projectV6',
        ]);
        $query = $query->where('title', 'like', '%' . $search . '%')
            ->orWhereHas('project', function ($q1) use ($search) {
                $q1->where('title', 'like', '%' . $search . '%')
                    ->orWhere('company_name', 'like', '%' . $search . '%');
            });
        $query = $query->where('status', '!=' ,Tickets::STATUS_COMPLETED)->where('plan_end_at','<',date("Y-m-d H:i:s", strtotime("-72 hours")));
        $query = $query->where('status', 0)->where('plan_end_at','<',date("Y-m-d H:i:s", strtotime("-120 hours")));//超过120个小时未处理的工单
        //TODO::用户部门搜索
        if(isset($this->param['dept_id']) && !empty($this->param['dept_id'])){
            $manageHrModel = new ManageHr();
            //售后优化+技术搜索
            if($this->param['dept_id'] == 2 && !empty($this->param['entry_position'])) {//售后部
                if ($this->param['entry_position'] == 1) {
                    $manageIdArr = $manageHrModel->selectField(['entry_position' => ['in', [44, 46, 49]], 'status' => 1], 'manage_id');
                } else {
                    //售后技术
                    $manageIdArr = $manageHrModel->selectField(['entry_position' => ['in', [42, 43, 45, 48, 51]], 'status' => 1], 'manage_id');
                }
                $query = $query->whereHas('logs', function ($q) use ($manageIdArr) {
                    $q->whereIn('engineer_id', $manageIdArr);
                });
            }else{
                $manageIdArr = $manageHrModel->selectField(['dept_id'=>$this->param['dept_id'],'status'=>1],'manage_id');
                $query = $query->whereHas('logs', function ($q) use ($manageIdArr) {
                    $q->whereIn('engineer_id', $manageIdArr);
                });
            }
        }
        // 添加排序功能
        $query->orderBy('status', 'asc');
        $lists = $query->get()->toArray();
        return $this->exportData($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', '测试域名');
        $sheet->setCellValue('F1', '提交人');
        $sheet->setCellValue('G1', '执行技术');
        $rowCount = 2;
        foreach ($data as $v) {
            foreach ($v['logs'] as $key => $value) {
                if($value['is_engineer'] == 1){
                    $is_engineer = $value['engineer']['name'];
                }
            }
            $sheet->setCellValue('A' . $rowCount, $v['project']['company_name']);
            $sheet->setCellValue('B' . $rowCount, $v['created_at']);
            $sheet->setCellValue('C' . $rowCount, $v['content']);
            $sheet->setCellValue('D' . $rowCount, $v['project']['website']);
            $sheet->setCellValue('E' . $rowCount, $v['project']['test_website']);
            $sheet->setCellValue('F' . $rowCount, $v['submit_username']);
            $sheet->setCellValue('G' . $rowCount, $is_engineer ?? '');
            $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)];
    }

    /**
     * @remark :导入产品
     * @name   :exportProductData
     * @author :lyh
     * @method :post
     * @time   :2025/6/30 16:46
     */
    public function exportProductData($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)];
    }
}