<?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\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(){
        $v6WeekModel = new V6WeeklyReport();
        $lists = $v6WeekModel->list([],'id',['*'],'desc',100);
        echo date('Y-m-d H:i:s') . 'start' . PHP_EOL;
        foreach ($lists as $data){
            $this->workChatMessage($data,$data['project_id']);
        }
        echo date('Y-m-d H:i:s') . 'end' . PHP_EOL;
        return true;
    }

    public function downloadProduct()
    {
        $product = new Product();
//        $filed = ['id', 'project_id', 'title' ,'thumb' , 'route' ,'intro','content',
//            'category_id', 'status','seo_mate'];
        $filed = ['id','title'];
        $this->order = 'sort';
        $lists = $product->list(['status'=>1],'id',$filed);
        if(!empty($lists)){
//            $cate_data = $this->getCategoryList();//分类
            foreach ($lists as $k => $v){
                echo date('Y-m-d H:i:s') . '产品id:'.$v['id'] . PHP_EOL;
//                $v['url'] = 'https://www.autsikinta.com/' . getRouteMap(RouteMap::SOURCE_PRODUCT,$v['id']);
//                $v['category_id_text'] = $this->categoryName($v['id'],$cate_data);
//                //ToDo::处理图片及文件
//                if(!empty($v['thumb']) && !empty($v['thumb']['url'])){
//                    $v['images'] = getImageUrl($v['thumb']['url']);
//                }else{
//                    $v['images'] = '';
//                }
                $lists[$k] = $v;
            }
        }
        return $lists;
    }
    public function categoryName($product_id,$data){
        $cateRelatedModel = new CategoryRelated();
        $category_id = $cateRelatedModel->where('product_id',$product_id)->pluck('cate_id')->toArray();
        $category_name = '';
        if(!empty($category_id) && !empty($data)){
            foreach ($category_id as $v){
                if(isset($data[$v])){
                    $category_name .= $data[$v].',';
                }
            }
            $category_name = trim($category_name,',');
        }
        return $category_name;
    }
    /**
     * @remark :获取所有分类
     * @name   :getCategoryList
     * @author :lyh
     * @method :post
     * @time   :2023/9/14 13:56
     */
    public function getCategoryList(){
        $data = Common::get_user_cache('product_category',1225);
        if(empty($data)){
            $categoryModel = new Category();
            $data = [];
            $cateList = $categoryModel->list(['project_id'=>1225],['id','title']);
            if(!empty($cateList)){
                foreach ($cateList as $value){
                    $data[$value['id']] = $value['title'];
                }
            }
            Common::set_user_cache($data,'product_category',1225);
        }
        return $data;
    }
//    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', '深度访问页数');
//        $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']);
//            $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;
//    }

    public function workChatMessage($data,$project_id){
        $data['main_lang_id'] = 8;
        $arr = [];
        $content = '';
        $content1 = '';
        if(!empty($data['inquiry_total'])){
            $content1 .= '项目共计已收到询盘 '.$data['inquiry_total'].'条,';
            if(!empty($data['week_inquiry_total'])){
                $content1 .= '本周新收 '.$data['week_inquiry_total'].' 封询盘。';
            }
            if(!empty($data['inquiry_country'])){
                $data['inquiry_country'] = json_decode($data['inquiry_country'],true);
                arsort($data['inquiry_country']);
                $data['inquiry_country'] = array_slice($data['inquiry_country'], 0, 4, true);
                $country = '';
                foreach ($data['inquiry_country'] as $k => $v){
                    $country .= $k.',';
                };
                $country = trim($country,',');
                if(!empty($country)){
                    $content1 .= '询盘主要来源于'.$country.'等国家地区';
                }
            }
            $content1 .= '如有高质量客户，请您密切关注与跟进;';
        }
        if(!empty($content1)){
            $arr[] = $content1;
        }
        $content2 = '';
        if(!empty($data['google_indexed_num']) || !empty($data['google_links_num']) || !empty($data['keyword_home_num']) || !empty($data['keyword_three_num']) || !empty($data['keyword_five_num']) || !empty($data['keyword_ten_num']) || !empty($data['daily_average_num'])){
            $content2 .= '项目截止目前';
            if($data['main_lang_id'] == 8){
                $title = 'Yandex';
            }else{
                $title = '谷歌';
            }
            if(!empty($data['google_indexed_num'])){
                $content2 .= $title.'收录量：'.$data['google_indexed_num'].'条，';
            }
            if(!empty($data['google_links_num'])){
                //获取上一次的外链数
                $latestRecord = V6WeeklyReport::where(['project_id'=>$project_id])->orderBy('id', 'desc')->first();
                if(!empty($latestRecord)){
                    if($latestRecord['google_links_num'] != $data['google_links_num']){
                        $content2 .= '外链量：'.$data['google_links_num'].'条，';
                    }
                }else{
                    $content2 .= '外链量：'.$data['google_links_num'].'条，';
                }
            }
            if(!empty($data['keyword_home_num']) || !empty($data['keyword_three_num']) || !empty($data['keyword_five_num']) || !empty($data['keyword_ten_num'])){
                $content2 .= $title.'搜索排名';
                if(!empty($data['keyword_home_num'])){
                    $content2 .= '首页关键词数量为：'.$data['keyword_home_num'].'个，';
                }
                if(!empty($data['keyword_three_num'])){
                    $content2 .= '前三页关键词数量为：'.$data['keyword_three_num'].'个，';
                }
                if(!empty($data['keyword_five_num'])){
                    $content2 .= '前五页关键词数量为：'.$data['keyword_five_num'].'个，';
                }
                if(!empty($data['keyword_ten_num'])){
                    $content2 .= '前十页关键词数量为：'.$data['keyword_ten_num'].'个，';
                }
            }
            if(!empty($data['daily_average_num'])){
                $content2 .= '本周日均访客量：'.$data['daily_average_num'].'+。';
            }
            $content2 .= '全球搜建议用户持续分析、选择、添加企业、产品、服务等相关关键词进行优化和监控，以覆盖更多相关排名和流量；';
        }
        if(!empty($content2)){
            $arr[] = $content2;
        }
        $content3 = '';
        if(!empty($data['product_num']) || !empty($data['news_num']) || !empty($data['week_product_num']) || !empty($data['week_news_num'])){
            if(!empty($data['product_num']) || !empty($data['news_num'])){
                $content3 .= '项目截止目前';
                if(!empty($data['product_num'])){
                    $content3 .= '发布产品：'.$data['product_num'].'条，';
                }
                if(!empty($data['news_num'])){
                    $content3 .= '发布新闻：'.$data['news_num'].'条。';
                }
            }
            if(!empty($data['week_product_num']) || !empty($data['week_news_num'])){
                $content3 .= '本周新增';
                if(!empty($data['week_product_num'])){
                    $content3 .= '产品：'.$data['week_product_num'].'条，';
                }
                if(!empty($data['week_news_num'])){
                    $content3 .= '新闻：'.$data['week_news_num'].'条。';
                }
            }
            $content3 .= '全球搜建议用户保持网站内容的持续更新与完善，可参考谷歌关于创建实用、可靠、以用户为中心的内容的相关建议：https://developers.google.com/search/docs/fundamentals/creating-helpful-content?hl=zh-cn；';
        }
        if(!empty($content3)){
            $arr[] = $content3;
        }
        $content4 = '';
        if(!empty($data['main_update_num'])){
            $content4 .= '网站加载速度维护及主站页面更新'.$data['main_update_num'].'次。';
        }
        if(!empty($data['aggregation_update_num'])){
            $content4 .= '聚合页主站页面更新'.$data['aggregation_update_num'].'次。';
        }
        if(!empty($data['aggregation_update_num'])){
            $content4 .= '聚合页主站页面更新'.$data['aggregation_update_num'].'次。';
        }
        if(!empty($data['minor_update_num'])){
            $content4 .= '小语种站页面更新'.$data['minor_update_num'].'次。';
        }
        if(!empty($data['aggregation_minor_update_num'])){
            $content4 .= '聚合页小语种站页面'.$data['aggregation_minor_update_num'].'次。';
        }
        if(!empty($content4)){
            $content4 = '本周主要优化工作包括：TDK、H标签、Img标签等优化设置排查与进一步完善，Sitemap更新与网页收录提交，外链新增与排查。'.$content4;
        }
        if(!empty($content4)){
            $arr[] = $content4;
        }
        if(empty($arr)){
            return true;
        }
        foreach ($arr as $key => $val){
            $content .= ($key+1).','.$val.PHP_EOL;
        }
        $timestamp = strtotime('tomorrow 9:00 AM');
        $tomorrowNineAM = date('Y-m-d H:i:s', $timestamp);
        if(empty($content)){
            return true;
        }
        $tips = 'Tips：'.PHP_EOL.'1、全球搜V6.0系统提供网页TDK、H标签、Img标签等用户自定义编辑接口且辅以AI创作工具，用户可进一步对相关优化设置进行精细化优化与调整；'.PHP_EOL.'2、全球搜V6.0系统提供小语种页面精准校对翻译功能，用户可进一步对已翻译小语种页面进行人工翻译校对；'.PHP_EOL.'3、全球搜V6.0系统支持绑定Facebook、LinkedIn、X（原Twitter）等社媒账号，可一键同步转发网站上发布的产品和新闻至社媒账号动态，建议用户用起来哦；'.PHP_EOL.'4、如用户有较丰富的企业、产品、服务相关视频素材，全球搜建议用户及时创建YouTube主页，并在YouTube和网站相关网页上同步发布视频；';
        $param = [
            'project_id'=>$project_id,
            'friend_id'=>17667,
            'type'=>MessagePush::TYPE_WEEK,
            'content'=>"【全球搜V6.0周报】--项目id:$project_id".PHP_EOL.$content.PHP_EOL.$tips,
            'ref_ids'=>'',
            'send_time'=>$tomorrowNineAM,
            'status'=>0,
        ];
        //写入一条推送消息  自动消费
        $messagePushModel = new MessagePush();
        $messagePushModel->add($param);
        echo date('Y-m-d H:i:s') . '数据:'. $content . PHP_EOL;
        return true;
    }
}