novelPromote/app/Console/Commands/ExportTask.php

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2021/6/7
 * Time: 17:13
 */

namespace App\Console\Commands;

use App\Services\NovelService;
use App\Services\OmDataService;
use App\Services\OrderService;
use App\Services\PlayletService;
use App\Services\PromoterService;
use App\Services\WxAccountService;
use Illuminate\Console\Command;
use App\Models\ExportTask as Task;
use Maatwebsite\Excel\Facades\Excel;

class ExportTask extends Command
{
    protected $signature = 'ExportTask';
    protected $description = '导出任务';

    protected $taskId = null;

    public function infoMessage($message)
    {
        echo date('H:i:s') . $message . "\r\n";
    }

    public function handle()
    {
        $this->infoMessage(' 开始执行;内存占用:'.round(memory_get_usage()/1024/1024, 2).'MB');

        \DB::connection()->disableQueryLog();

        $time = time();
        while(1) {
            if(time() - $time >= 30*60) {
                //每隔半小时自动杀死
                die;
            }

            $taskList = $this->getTaskList();
            if(empty($taskList)) {
                sleep(60);
                continue;
            }

            $this->handleData($taskList);
        }
    }

    public function getTaskList()
    {
        //获取待执行导出任务列表
        $taskList = Task::where('status', 1)->get()->toArray();
        return $taskList;
    }

    public function handleData($taskList)
    {
        try{
            $typeData = Task::$typeData;
            $typeArr = array_keys($typeData);
            foreach($taskList as $task) {
                if(!in_array($task['data_type'], $typeArr)) {
                    continue;
                }

                $this->taskId = $task['id'];

                switch($task['data_type']) {
                    case 1: # 公众号总数据
                        $this->dataCollectExport($task);
                        break;
                    case 2: # 广告投放数据
                        $this->adServingExport($task);
                        break;
                    case 3: # 今日消耗排行榜
                        $this->expendExport($task);
                        break;
                    case 4: # 付费粉丝激活趋势
                        $this->paidFansExport($task);
                        break;
                    case 5: # 公众号付费趋势
                        $this->paymentTrendExport($task);
                        break;
                    case 6: # 小说数据趋势
                        $this->novelDataExport($task);
                        break;
                    case 7: # 投手大盘数据
                        $this->promoteCondition($task);
                        break;
                    case 8: # 每日付费统计
                        $this->orderList($task);
                        break;
                    case 9: # 每日运营数据汇总
                        $this->omDataExport($task);
                        break;
                    case 10: # 小说人气数据汇总
                        $this->novelPopularityExport($task);
                        break;
                    case 11: # 短剧粉丝激活趋势
                        $this->activateTrendExport($task);
                        break;
                    case 12: # 公众号数据趋势
                        $this->npDataTrend($task);
                        break;
                    case 13: # 短剧数据趋势
                        $this->playletDataTreand($task);
                }

                sleep(60);
            }
        } catch (\Exception $exception) {
            $this->updateTaskStatus($this->taskId, -1, json_encode([
                'file'=>$exception->getFile(),
                'line'=>$exception->getLine(),
                'message'=>$exception->getMessage()])
            );
            $this->infoMessage('error#file:'.$exception->getFile().';line:'.$exception->getLine().';message:'.$exception->getMessage());
        }
    }

    public function playletDataTreand($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);
        if($params['start_date'] || $params['end_date']) {
            list($data, $count) = PlayletService::playletData($params['playlet_id'], $params['start_date'], $params['end_date'], 1, 5000, $params['min_day_paid'], $params['max_day_paid']);
        } else {
            list($data, $count) = PlayletService::playletDataTotal($params['playlet_id'], 1, 5000, $params['min_day_paid'], $params['max_day_paid']);
        }

        $data = json_decode(json_encode($data), 1);

        $titleArr = [
            "时间",
            "短剧",
            "首日ROI",
            "当天消耗",
            "当日新用户累计充值",
            '回本率',
            "新增粉丝数",
            "新增粉丝成本",
            "充值人数",
            "充值次数",
            "充值用户成本"
        ];
        $DArr = [];
        for($i=1;$i<=WxAccountService::DAYS;$i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = $extra = [];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item['start_date'].'~'.$item['end_date'],
                $item['playlet_name'],
                (round($item['first_day_roi'], 4)) * 100 . '%',
                $item['day_paid'],
                $item['charge_total'],
                (round($item['cost_cover_rate'], 4)) * 100 .'%',
                $item['fans_increase'],
                round($item['new_user_cost'], 2),
                $item['new_user_charge_uv'],
                $item['new_user_charge_pv'],
                round($item['charge_user_cost'], 2),
            ];
            $chargeData = isset($item['charge_data']) ? $item['charge_data'] : [];

            $chargeDataNew = [];

            foreach ($chargeData as $key=>$val) {
                $charge = isset($val['day_charge']) ? $val['day_charge'] : 0;
                $add = isset($val['day_add']) ? (round($val['day_add'], 4)) *100 . '%' : 0;
                $cover = isset($val['day_cover']) ? (round($val['day_cover'], 4)) *100 . '%' : 0;
                $times = isset($val['day_times']) ? (round($val['day_times'], 2)) : 0;

                $chargeDataNew[$key] = ['充:'.$charge, '增:'.$add, '回:'.$cover, '倍:'.$times];
            }

            $extra[] = $chargeDataNew;
        }

        $name = '短剧数据趋势_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData, $titleArr, $extra){
            $excel->sheet('', function($sheet) use ($cellData, $titleArr, $extra){
                $merge = [];
                $sheet->row(1, $titleArr);
                foreach ($cellData as $index =>$row) {
                    $line = $index*4 + 2;

                    $sheet->row($line, $row);
                    $sheet->rows([
                        ['', '', '','', '', '','', '', '','', ''],
                        ['', '', '','', '', '','', '', '','', ''],
                        ['', '', '','', '', '','', '', '','', '']
                    ]);

                    $merge[] = [$line, $line + 3];
                }
                $basicLength = 11;
                $cellLabel = PlayletService::CELL_LABEL;
                $cellLabelUse = array_slice($cellLabel, $basicLength);

                foreach ($extra as $key => $cell) {
                    foreach($cell as $k => $val) {
                        foreach ($val as $i=>$v) {
                            $sheet->cell($cellLabelUse[$k].($key*4+$i+2), function($cell) use($v) {
                                $cell->setValue($v);
                            });
                        }
                    }

                }

                $sheet->setMergeColumn(array(
                    'columns' => array('A','B','C','D','E','F','G','H','I','J','K'),
                    'rows' => $merge
                ));
            });
        })->store('xls');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    /**
     * 短剧粉丝激活趋势
     * */
    public function activateTrendExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $count) = PlayletService::activateTrend(
            $params['keyword'], $params['start_date'], $params['end_date'], 1, 10000
        );

        $titleArr = [
            "时间",
            "公众号",
            "当天消耗",
            "当日新用户累计充值",
            "回本率",
            '企微关注数',
            "企微关注成本",
            "首日用户成本",
            "累计用户成本"
        ];

        $DArr = [];
        for($i=1; $i<=PlayletService::ACTIVATE_FANS_DAYS; $i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = [
            $titleArr
        ];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item->subscribed_date,
                $item->name,
                $item->day_paid,
                $item->new_user_charge,
                (round($item->cost_cover_rate, 4)) * 100 .'%',
                $item->scan_follow_count,
                $item->follow_cost,
                round($item->charge_user_cost, 2),
                round($item->charge_user_cost_all, 2)
            ];
            $activeData = isset($item->activeData) ? $item->activeData : [];

            $extra = [];

            foreach ($activeData as $val) {
                $fans = isset($val['fans']) ? $val['fans'] : 0;
                $cost = isset($val['cost']) ? (round($val['cost'], 2)) : 0;
                $percent = 0;
                if(isset($val['active_fans_total'])) {
                    $percent = $val['active_fans_total'] != 0 ? (round($fans / $item['active_fans_total'], 4)) *100 . '%' : 0;
                }

                $extra[] = "人数: ". $fans ."\r\n"
                    . "成本: " . $cost ."\r\n"
                    . "占比: " .$percent ;
            }

            $cellData[$index+1] = array_merge($cellData[$index+1], $extra);
        }

        $name = '短剧粉丝激活趋势_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    /**
     * 小说人气数据汇总
     * */
    public function novelPopularityExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $count) = NovelService::popularityNovelData($params['keyword'], $params['sort'], $params['start_date'], $params['end_date'], 1, 10000);

        $cellData = [
            [
                "小说",
                "平台",
                "充值金额",
                "在看人数",
                "ARPU",
            ]
        ];

        $platformInfo = config('platform');

        foreach ($data as $item) {
            $cellData[] = [
                $item->from_novel,
                isset($platformInfo[$item->platform_id]) ? $platformInfo[$item->platform_id] : '',
                $item->charge_total / 100,
                $item->watching_user,
                round($item->charge_total / 100 / $item->watching_user, 3),
            ];
        }

        $name = '小说人气数据统计_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    /**
     * 每日运营数据汇总导出
     * */
    public function omDataExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total, $currentMonth, $lastYear, $condition) = OmDataService::getDataOfDay($params['start_date'], $params['end_date'], 1, 5000);

        $cellData = [
            "日期",
            "投放消耗",
            "新用户充值",
            "新用户ROI",
        ];

        $currentMonth = $currentMonth -1;
        $DArr = [];
        for($i=$currentMonth;$i>0;$i--){
            $DArr[] = $i . '月份用户回收';
        }

        $DArr[] = $lastYear . '年用户回收';

        $titleArr = array_merge($cellData, $DArr);

        $cellData = [
            $titleArr
        ];

        foreach ($data as $index => $item) {
            $lastYearData = [];
            for($i=$currentMonth; $i>0; $i--) {
                $key = 'recycle'.$i;
                $lastYearData[] = $item->$key;
            }

            $cellDataMid = [
                $item->ref_date,
                $item->paid_total,
                $item->new_user_charge,
                $item->roi,
            ];

            $midData = array_merge($cellDataMid, $lastYearData, [$item->last_year_recycle]);

            $cellData[] = $midData;
        }

        $name = '每日运营数据汇总_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function orderList($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total, $accountList, $platformList, $condition) = OrderService::getList($params['channel_id'], $params['platform_id'], $params['reg_time_st'], $params['reg_time_et'], $params['order_time_st'], $params['order_time_et'], $params['min_money'], $params['max_money'], 1, 5000);

        $cellData = [
            [
                "注册时间",
                "金额",
                "公众号",
                "下单时间",
                "书城"
            ]
        ];

        foreach ($data as $item) {
            $cellData[] = [
                $item->user_created_at,
                $item->price,
                $item->nickname,
                $item->platform_created_at,
                $item->platform
            ];
        }

        $name = '每日付费统计_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    /**
     * 投手大盘数据导出
     * */
    public function promoteCondition($task)
    {
        $this->updateTaskStatus($task['id'], 2);

        $params = json_decode($task['params'], 1);

        list($list, $count, $condition) = PromoterService::getPromoteCondition($params['promoter_id'], $params['start_date'], $params['end_date'], 1, 2000);

        $cellData = [
            [
                "公众号名称",
                "投手",
                "书籍",
                "开始日期",
                "截止日期",
                '累计消耗',
                "累计充值",
                "当日ROI",
                "总毛利额",
                "回本率"
            ]
        ];

        foreach ($list as $item) {
            $cellData[] = [
                $item->nickname,
                $item->promoter,
                $item->novel,
                $item->start_date,
                $item->end_date,
                $item->paid_total,
                $item->charge_total,
                (round($item->roi, 4) * 100) . '%',
                $item->margin_rate,
                (round($item->cost_recovery_rate, 4) * 100) . '%',
            ];
        }

        $name = '投手大盘数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function dataCollectExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total) = WxAccountService::accountDataCollect($params['keyword'], $params['enable'], 1, 2000);

        $cellData = [
            [
                "公众号名称",
                "投手",
                "最早消耗日期",
                "最晚消耗日期",
                "累计消耗",
                '累计充值',
                "总毛利额",
                "回本率",
                "总关注人数",
                "平均关注人数成本",
                "总充值人数",
                "充值转化比率",
                "充值转化成本",
                "今日充值",
                "昨日充值",
                "前日充值"
            ]
        ];

        foreach ($data as $item) {
            $cellData[] = [
                $item->name,
                $item->promoter_name,
                $item->start_paid,
                $item->end_paid,
                $item->paid_total,
                $item->charge_total,
                $item->margin_rate,
                (round($item->cost_recovery_rate, 4) * 100) . '%',
                $item->follow_total,
                round($item->cost_recovery_rate, 3),
                $item->charge_user_total,
                (round($item->charge_transform, 4) * 100) . '%',
                round($item->charge_transform_cost, 3),
                $item->yesterday,
                $item->before_yesterday,
                $item->three_das_ago
            ];
        }

        $name = '公众号总数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function adServingExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total) = WxAccountService::adServing($params['keyword'], $params['sort'], $params['start_date'], $params['end_date'], 1, 5000);

        $cellData = [
            [
                "时间",
                "公众号",
                "消耗",
                "首日充值金额",
                "首日roi",
                '首日下单人数',
                "首日下单成本",
                "点击率",
                "下单率",
                "关注率",
                "关注成本"
            ]
        ];

        foreach ($data as $item) {
            $cellData[] = [
                $item->ref_date,
                $item->name,
                $item->paid,
                $item->paid_total_new,
                (round($item->roi, 4) * 100) . '%',
                $item->charge_uv_new,
                round($item->order_cost, 2),
                ($item->ctr * 100) . '%',
                (round($item->order_rate, 4) * 100) . '%',
                (round($item->clk_com_rate, 4) * 100) . '%',
                round($item->com_cost, 2),
            ];
        }

        $name = '广告投放数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function expendExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total) = WxAccountService::expendRank($params['keyword'], $params['start_date'], $params['end_date'], 1, 5000);

        $cellData = [
            [
                "日期",
                "公众号",
                "消耗",
                "曝光量",
                "点击量",
                "点击率",
                "千次曝光成本"
            ]
        ];

        foreach ($data as $item) {
            $cellData[] = [
                $params['start_date'] . '~' . $params['end_date'],
                $item->name,
                $item->paid_total,
                $item->exp_total,
                $item->clk_total,
                round($item->clk_rate, 3),
                round($item->exp_thousand_cost)
            ];
        }

        $name = '消耗排行数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function paidFansExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total) = WxAccountService::paidFans($params['keyword'], $params['start_date'], $params['end_date'], 1, 5000);

        $titleArr = [
            "时间",
            "公众号",
            "当天消耗",
            "当日新用户累计充值",
            "回本率",
            '新增粉丝数',
            "新增粉丝成本",
            "充值人数",
            "充值次数",
            "充值用户成本",
            "7日平均激活率",
            "7日平均激活成本",
            "30日平均激活率",
            "30日平均激活成本",
            "总激活成本"
        ];
        $DArr = [];
        for($i=1;$i<=WxAccountService::DAYS;$i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = [
            $titleArr
        ];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item->subscribed_date,
                $item->name,
                $item->day_paid,
                $item->charge_total,
                (round($item->cost_cover_rate, 4)) * 100 .'%',
                $item->fans_increase,
                round($item->cost_cover_rate, 2),
                $item->new_user_charge_uv,
                $item->new_user_charge_pv,
                round($item->charge_user_cost, 2),
                round($item->active_avg_seven, 2),
                round($item->charge_user_cost_seven, 2),
                round($item->active_avg_thirty, 2),
                round($item->charge_user_cost_thirty, 2),
                round($item->charge_user_cost_all, 2)
            ];
            $activeData = isset($item->activeData) ? $item->activeData : [];

            $extra = [];

            foreach ($activeData as $val) {
                $fans = isset($val['fans']) ? $val['fans'] : 0;
                $cost = isset($val['cost']) ? (round($val['cost'], 2)) : 0;
                $percent = 0;
                if(isset($val['active_fans_total'])) {
                    $percent = $val['active_fans_total'] != 0 ? (round($fans / $item['active_fans_total'], 4)) *100 . '%' : 0;
                }

                $extra[] = "人数: ". $fans ."\r\n"
                    . "成本: " . $cost ."\r\n"
                    . "占比: " .$percent ;
            }

            $cellData[$index+1] = array_merge($cellData[$index+1], $extra);
        }

        $name = '付费粉丝激活趋势数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData){
            $excel->sheet('', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->store('csv');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    /**
     * 公众号数据趋势
     * */
    public function npDataTrend($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $count) = PlayletService::dataTrend(
            $params['keyword'], $params['start_date'], $params['end_date'], 1, 10000
        );

        $titleArr = [
            "时间",
            "公众号",
            "首日ROI",
            "当天消耗",
            '企微关注数',
            "企微关注成本",
            "当日回收",
            "累计充值金额",
            "新增用户",
            '回本率',
            "充值人数",
            "总充值人数",
            "充值次数",
            "总充值次数",
            "充值用户成本",
            ''
        ];

        $DArr = [];
        for($i=1; $i<=PlayletService::DAYS; $i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = $extra = [];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item->subscribed_date,
                $item->name,
                (round($item->first_day_roi, 4)) * 100 .'%',
                $item->day_paid,
                $item->scan_follow_count,
                $item->follow_cost,
                $item->new_user_charge,
                $item->charge_total,
                $item->fans_increase,
                (round($item->cost_cover_rate, 4)) * 100 .'%',
                $item->new_user_charge_uv,
                $item->new_user_charge_uv_count,
                $item->new_user_charge_pv,
                $item->new_user_charge_pv_count,
                round($item->charge_user_cost, 2),
                '充'
            ];
            $chargeData = isset($item->chargeData) ? $item->chargeData : [];

            $chargeDataNew = [];

            foreach ($chargeData as $key=>$val) {
                $charge = isset($val['day_charge']) ? $val['day_charge'] : 0;
                $add = isset($val['day_add']) ? (round($val['day_add'], 4)) *100 . '%' : 0;
                $cover = isset($val['day_cover']) ? (round($val['day_cover'], 4)) *100 . '%' : 0;
                $times = isset($val['day_times']) ? (round($val['day_times'], 2)) : 0;

                $chargeDataNew[$key] = [$charge, $add, $cover, $times];
            }

            $extra[] = $chargeDataNew;
        }

        $name = '短剧数据趋势_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData, $titleArr, $extra){
            $excel->sheet('', function($sheet) use ($cellData, $titleArr, $extra){
                $merge = [];
                $sheet->row(1, $titleArr);
                foreach ($cellData as $index =>$row) {
                    $line = $index*4 + 2;

                    $sheet->row($line, $row);
                    $sheet->rows([
                        ['', '', '','', '', '','', '', '','', '','','增'],
                        ['', '', '','', '', '','', '', '','', '','','回'],
                        ['', '', '','', '', '','', '', '','', '','','倍']
                    ]);

                    $merge[] = [$line, $line + 3];
                }
                $basicLength = 13;
                $cellLabel = WxAccountService::CELL_LABEL;
                $cellLabelUse = array_slice($cellLabel, $basicLength);

                foreach ($extra as $key => $cell) {
                    foreach($cell as $k => $val) {
                        foreach ($val as $i=>$v) {
                            $sheet->cell($cellLabelUse[$k].($key*4+$i+2), function($cell) use($v) {
                                $cell->setValue($v);
                            });
                        }
                    }
                }

                $sheet->setMergeColumn(array(
                    'columns' => array('A','B','C','D','E','F','G','H','I','J','K','L'),
                    'rows' => $merge
                ));
            });
        })->store('xls');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function paymentTrendExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);

        list($data, $total, $condition) = WxAccountService::paymentTrend($params['keyword'], $params['novel_id'], $params['start_date'], $params['end_date'], 1, 5000);

        $titleArr = [
            "时间",
            "公众号",
            "首日ROI",
            "当天消耗",
            "当日新用户累计充值",
            '回本率',
            "新增粉丝数",
            "新增粉丝成本",
            "充值人数",
            "充值次数",
            "充值用户成本",
            ""
        ];
        $DArr = [];
        for($i=1;$i<=WxAccountService::DAYS;$i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = $extra = [];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item->subscribed_date,
                $item->name,
                (round($item->first_day_roi, 4)) * 100 . '%',
                $item->day_paid,
                $item->charge_total,
                (round($item->cost_cover_rate, 4)) * 100 .'%',
                $item->fans_increase,
                round($item->new_user_cost, 2),
                $item->new_user_charge_uv,
                $item->new_user_charge_pv,
                round($item->charge_user_cost, 2),
                '充'
            ];
            $chargeData = isset($item->chargeData) ? $item->chargeData : [];

            $chargeDataNew = [];

            foreach ($chargeData as $key=>$val) {
                $charge = isset($val['day_charge']) ? $val['day_charge'] : 0;
                $add = isset($val['day_add']) ? (round($val['day_add'], 4)) *100 . '%' : 0;
                $cover = isset($val['day_cover']) ? (round($val['day_cover'], 4)) *100 . '%' : 0;
                $times = isset($val['day_times']) ? (round($val['day_times'], 2)) : 0;

                $chargeDataNew[$key] = [$charge, $add, $cover, $times];
            }

            $extra[] = $chargeDataNew;
        }

        $name = '粉丝付费趋势数据_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData, $titleArr, $extra){
            $excel->sheet('', function($sheet) use ($cellData, $titleArr, $extra){
                $sheet->row(1, $titleArr);
                $merge = [];
                foreach ($cellData as $index =>$row) {
                    $line = $index*4 + 2;

                    $sheet->row($line, $row);
                    $sheet->rows([
                        ['', '', '','', '', '','', '', '','', '','增'],
                        ['', '', '','', '', '','', '', '','', '','回'],
                        ['', '', '','', '', '','', '', '','', '','倍']
                    ]);

                    $merge[] = [$line, $line + 3];
                }
                $basicLength = 12;
                $cellLabel = WxAccountService::CELL_LABEL;
                $cellLabelUse = array_slice($cellLabel, $basicLength);

                foreach ($extra as $key => $cell) {
                    foreach($cell as $k => $val) {
                        foreach ($val as $i=>$v) {
                            $sheet->cell($cellLabelUse[$k].($key*4+$i+2), function($cell) use($v) {
                                $cell->setValue($v);
                            });
                        }
                    }
                }

                $sheet->setMergeColumn(array(
                    'columns' => array('A','B','C','D','E','F','G','H','I','J','K'),
                    'rows' => $merge
                ));
            });
        })->store('xls');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function novelDataExport($task)
    {
        $this->updateTaskStatus($task['id'], 2);
        $params = json_decode($task['params'], 1);
        if($params['start_date'] || $params['end_date']) {
            list($data, $count) = WxAccountService::novelData($params['novel_id'], $params['start_date'], $params['end_date'], 1, 5000, $params['min_day_paid'], $params['max_day_paid']);
        } else {
            list($data, $count) = WxAccountService::novelDataTotal($params['novel_id'], 1, 5000, $params['min_day_paid'], $params['max_day_paid']);
        }

        $data = json_decode(json_encode($data), 1);

        $titleArr = [
            "时间",
            "小说",
            "首日ROI",
            "当天消耗",
            "当日新用户累计充值",
            '回本率',
            "新增粉丝数",
            "新增粉丝成本",
            "充值人数",
            "充值次数",
            "充值用户成本"
        ];
        $DArr = [];
        for($i=1;$i<=WxAccountService::DAYS;$i++){
            $DArr[] = 'D'.$i;
        }

        $titleArr = array_merge($titleArr, $DArr);

        $cellData = $extra = [];

        foreach ($data as $index => $item) {
            $cellData[] = [
                $item['start_date'].'~'.$item['end_date'],
                $item['novel_name'],
                (round($item['first_day_roi'], 4)) * 100 . '%',
                $item['day_paid'],
                $item['charge_total'],
                (round($item['cost_cover_rate'], 4)) * 100 .'%',
                $item['fans_increase'],
                round($item['new_user_cost'], 2),
                $item['new_user_charge_uv'],
                $item['new_user_charge_pv'],
                round($item['charge_user_cost'], 2),
            ];
            $chargeData = isset($item['charge_data']) ? $item['charge_data'] : [];

            $chargeDataNew = [];

            foreach ($chargeData as $key=>$val) {
                $charge = isset($val['day_charge']) ? $val['day_charge'] : 0;
                $add = isset($val['day_add']) ? (round($val['day_add'], 4)) *100 . '%' : 0;
                $cover = isset($val['day_cover']) ? (round($val['day_cover'], 4)) *100 . '%' : 0;
                $times = isset($val['day_times']) ? (round($val['day_times'], 2)) : 0;

                $chargeDataNew[$key] = ['充:'.$charge, '增:'.$add, '回:'.$cover, '倍:'.$times];
            }

            $extra[] = $chargeDataNew;
        }

        $name = '小说数据趋势_' . date('Ymd_H_i');

        Excel::create($name, function($excel) use($cellData, $titleArr, $extra){
            $excel->sheet('', function($sheet) use ($cellData, $titleArr, $extra){
                $merge = [];
                $sheet->row(1, $titleArr);
                foreach ($cellData as $index =>$row) {
                    $line = $index*4 + 2;

                    $sheet->row($line, $row);
                    $sheet->rows([
                        ['', '', '','', '', '','', '', '','', ''],
                        ['', '', '','', '', '','', '', '','', ''],
                        ['', '', '','', '', '','', '', '','', '']
                    ]);

                    $merge[] = [$line, $line + 3];
                }
                $basicLength = 11;
                $cellLabel = WxAccountService::CELL_LABEL;
                $cellLabelUse = array_slice($cellLabel, $basicLength);

                foreach ($extra as $key => $cell) {
                    foreach($cell as $k => $val) {
                        foreach ($val as $i=>$v) {
                            $sheet->cell($cellLabelUse[$k].($key*4+$i+2), function($cell) use($v) {
                                $cell->setValue($v);
                            });
                        }
                    }

                }

                $sheet->setMergeColumn(array(
                    'columns' => array('A','B','C','D','E','F','G','H','I','J','K'),
                    'rows' => $merge
                ));
            });
        })->store('xls');

        $this->updateTaskStatus($task['id'], 3, $name);
    }

    public function updateTaskStatus($taskId, $status, $message = null)
    {
        $update['status'] = $status;
        if(-1 == $status) {
            $update['exception'] = $message;
        } else {
            if($message) {
                $update['file_url'] = $message;
            }
        }

        Task::where('id', $taskId)->update($update);
    }
}