playlet/app/Service/PopularizSer...

<?php

namespace App\Service;

use App\Log;
use App\Models\AdqAccountBindPidConf;
use App\Models\DjOrder;
use App\Models\MassPopularizData;
use App\Models\DjUser;
use App\Models\MassMsg;
use App\Models\MassMsgRecord;
use App\Models\MomentTask;
use App\Models\MomentRecord;
use App\Models\MomentFansInteract;
use App\Models\Report\DjPromotionDataStat;
use App\Models\System\Users;
use App\Models\TencentAdDailyReport;
use App\Models\View\CustomerDetail;

class PopularizService
{
    /**
     * 获取群发列表
     * */
    public static function ruleList($creatorId, $sendTimeStart, $sendTimeEnd,$sortColumn, $page, $pageSize, $is_system_admin,
        $group_admin_id, &$errno, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = StatisticsService::getAuthCorpids($group_admin_id);
//        }
        $corpids = StatisticsService::getAuthCorpids($sys_group_id);
        if(empty($corpids)) {
            return [[], 0];
        }

        try {
            list($list, $count) = MassMsg::getDataRuleLists($corpids, $creatorId, $sendTimeStart, $sendTimeEnd, $sortColumn, $page, $pageSize);

            # 获取创建人信息
            $adminIds = $list->pluck('admin_id');
            $adminData = Users::select(['id','name'])->whereIn('id', $adminIds)->get();

            # 统计发送信息
            $ruleIds = $list->pluck('rule_id');
            $sendStatData = MassMsgRecord::selectRaw("rule_id, sum(send_success) as send_success, sum(send_fail) as send_fail, sum(send_total) as send_total")
                ->where('type', 1)
                ->whereIn('rule_id', $ruleIds)->groupBy(['rule_id'])->get();

            #统计回收信息
            $pids = [];
            $pidVals = $list->pluck('pids')->all();
            foreach ($pidVals as $pidVal) {
                if (empty($pidVal)) continue;

                $itemPids = json_decode($pidVal, true);
                $pids = array_merge($pids, $itemPids);
            }

            if( !empty($pids) ){
                $popu_data = MassPopularizData::select('viewPv', 'viewUv', 'payPv', 'payUv', 'payMoney', 'pid', 'playletActivityId')
                    ->whereIn('pid', $pids)
                    ->get();

                $playletActivityIdList = $popu_data->pluck('playletActivityId')->all();
                $playletActivityIdList = array_filter($playletActivityIdList);

                if(!empty($playletActivityIdList)) {
                    # 统计新老客户数据
                    $orderData  = DjOrder::query()
                        ->select(['pid'])
                        ->selectRaw("COUNT(DISTINCT IF(p_customer_type = 1, unionid, NULL)) AS newPayUv")
                        ->selectRaw("COUNT(IF(p_customer_type = 1, unionid, NULL)) AS newPayPv")
                        ->selectRaw("SUM(IF(p_customer_type = 1, pay_money, 0)) AS newPayAmount")
                        ->selectRaw("SUM(IF(p_customer_type = 2, pay_money, 0)) AS oldPayAmount")
                        ->whereIn('pid', $playletActivityIdList)
                        ->where('is_ad_user', 1)
                        ->where('pay_status', 1)
                        ->groupBy('pid')
                        ->get()
                        ->keyBy('pid')
                        ->toArray();

                    // 非投放回收金额
                    $otherOrderData = DjOrder::query()
                        ->select(['pid'])
                        ->selectRaw('sum(pay_money) as otherPayAmount')
                        ->whereIn('pid', $playletActivityIdList)
                        ->where('is_ad_user', 0)
                        ->where('pay_status', 1)
                        ->groupBy('pid')
                        ->get()
                        ->keyBy('pid')
                        ->toArray();
                }
            }

            # 处理数据
            foreach($list as $datum) {
                # 消息条数
                $contentCount = empty($datum->content) ? 0 : 1;
                $attachmentCount = empty($datum->attachments) ? 0 : count(json_decode($datum->attachments, true));
                $datum->msg_count = $contentCount + $attachmentCount;

                # 创建人信息
                $adminInfo = $adminData->where('id', $datum->admin_id)->first();
                $datum->creator = isset($adminInfo->name) ? $adminInfo->name : '';

                # 统计发送情况
                $sendStatInfo = $sendStatData->where('rule_id', $datum->rule_id)->first();
                $datum->send_success = isset($sendStatInfo->send_success) ? $sendStatInfo->send_success : 0;
                $datum->send_fail = isset($sendStatInfo->send_fail) ? $sendStatInfo->send_fail : 0;
                $datum->send_total = isset($sendStatInfo->send_total) ? $sendStatInfo->send_total : 0;
                unset($datum->admin_id);

                $datumPids = json_decode($datum->pids, true);
                #统计 总播放人数（：总uv）  充值人数 充值次数（今日） 充值金额（今日）   付费率 点击率
                if($datumPids){
                    $datum->payAvg = $datum->payUv>0 ? round($datum->payPv/$datum->payUv, 2) : null;
                    $datum->payRate = $datum->viewUv>0 ? round($datum->payUv/$datum->viewUv*100, 2) .'%' : null;
                    $datum->clickRate = $datum->send_success>0 ? round($datum->viewUv/$datum->send_success*100, 2) .'%' : null;

                    $popu_list = MassPopularizData::select('viewPv', 'viewUv', 'payPv', 'payUv', 'payMoney', 'pid', 'playletActivityId')
                        ->whereIn('pid', $datumPids)
                        ->get();
                    if ($popu_list->isNotEmpty()) {
                        $datum->viewUv         = 0;
                        $datum->payUv          = 0;
                        $datum->payPv          = 0;
                        $datum->payMoney       = 0;
                        $datum->newPayUv       = 0;
                        $datum->newPayPv       = 0;
                        $datum->newPayAmount   = 0;
                        $datum->oldPayAmount   = 0;
                        $datum->otherPayAmount = 0;
                        foreach ($popu_list as $popu_info) {
                            $datum->viewUv   += ($popu_info->viewUv ?? 0);
                            $datum->payUv    += ($popu_info->payUv ?? 0);
                            $datum->payPv    += ($popu_info->payPv ?? 0);
                            $datum->payMoney += ($popu_info->payMoney ?? 0);

                            if(isset($popu_info->playletActivityId)) {
                                $datum->newPayUv       += ($orderData[$popu_info->playletActivityId]['newPayUv'] ?? 0);
                                $datum->newPayPv       += ($orderData[$popu_info->playletActivityId]['newPayPv'] ?? 0);
                                $datum->newPayAmount   += ($orderData[$popu_info->playletActivityId]['newPayAmount'] ?? 0);
                                $datum->oldPayAmount   += ($orderData[$popu_info->playletActivityId]['oldPayAmount'] ?? 0);
                                $datum->otherPayAmount += ($otherOrderData[$popu_info->playletActivityId]['otherPayAmount'] ?? 0);
                            }
                        }

                        $datum->newPayAmount   = round($datum->newPayAmount/10000, 2);
                        $datum->oldPayAmount   = round($datum->oldPayAmount/10000, 2);
                        $datum->otherPayAmount = round($datum->otherPayAmount/10000, 2);
                        $datum->newPayAvg      = $datum->newPayUv>0 ? round($datum->newPayPv/$datum->newPayUv, 2) : null;
                    } else {
                        $datum->viewUv         = null;
                        $datum->payUv          = null;
                        $datum->payPv          = null;
                        $datum->payMoney       = null;
                        $datum->newPayUv       = null;
                        $datum->newPayPv       = null;
                        $datum->newPayAmount   = null;
                        $datum->oldPayAmount   = null;
                        $datum->otherPayAmount = null;
                        $datum->newPayAvg      = null;
                    }
                } else {
                    $datum->viewUv = null;
                    $datum->payUv = null;
                    $datum->payPv = null;
                    $datum->payMoney = null;
                    $datum->payAvg = null;
                    $datum->payRate = null;
                    $datum->clickRate = null;
                    $datum->newPayUv = null;
                    $datum->newPayPv = null;
                    $datum->newPayAvg = null;
                    $datum->newPayAmount = null;
                    $datum->oldPayAmount = null;
                    $datum->otherPayAmount = null;
                }
            }
        } catch (\Exception $e) {
            Log::logError('获取推广群发列表过程发生异常', [
                'line' => $e->getLine(),
                'msg' => $e->getMessage(),
            ], 'PopularizService');

            $errno = 2105;

            return [[], 0];
        }

        return [$list, $count];
    }

    public static function getRuleTotal($creatorId, $sendTimeStart, $sendTimeEnd, $is_system_admin, $group_admin_id, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = StatisticsService::getAuthCorpids($group_admin_id);
//        }
        $corpids = StatisticsService::getAuthCorpids($sys_group_id);
        if(empty($corpids)) {
            return [];
        }

        $sendStatData= MassMsg::getDataRuleTotal($corpids, $creatorId, $sendTimeStart, $sendTimeEnd);
        return $sendStatData;
    }

    /**
     * 朋友圈任务列表
     * */
    public static function taskList($creatorId, $sendTimeStart, $sendTimeEnd, $sortColumn, $page, $pageSize, $is_system_admin,
        $group_admin_id, &$errno, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = StatisticsService::getAuthCorpids($group_admin_id);
//        }
        $corpids = StatisticsService::getAuthCorpids($sys_group_id);
        if(empty($corpids)) {
            return [[], 0];
        }

        try {
            list($list, $count) = MomentTask::getPopuTaskLists($corpids, $creatorId, $sendTimeStart, $sendTimeEnd, $sortColumn, $page, $pageSize);

            # 获取创建人信息
            $adminIds = $list->pluck('admin_id');
            $adminData = Users::select(['id','name'])->whereIn('id', $adminIds)->get();

            # 统计发送信息
            $ruleIds = $list->pluck('rule_id');
            $publishedStatData = MomentRecord::selectRaw("sum(case when publish_status=1 then 1 else 0 end) as published_count, 
                sum(case when publish_status=0 then 1 else 0 end) as unpublished_count, count(1) as published_total, rule_id")
                ->whereIn('rule_id', $ruleIds)->groupBy(['rule_id'])->get();

            #统计回收信息
            $pids = [];
            $pidVals = $list->pluck('pids')->all();
            foreach ($pidVals as $pidVal) {
                if (empty($pidVal)) continue;

                $itemPids = json_decode($pidVal, true);
                $pids = array_merge($pids, $itemPids);
            }

            if( !empty($pids) ){
                $popu_data = MassPopularizData::select('viewPv', 'viewUv', 'payPv', 'payUv', 'payMoney', 'pid', 'playletActivityId')
                    ->whereIn('pid', $pids)
                    ->get();

                $playletActivityIdList = $popu_data->pluck('playletActivityId')->all();
                $playletActivityIdList = array_filter($playletActivityIdList);

                if(!empty($playletActivityIdList)) {
                    # 统计新老客户数据
                    $orderData  = DjOrder::query()
                        ->select(['pid'])
                        ->selectRaw("COUNT(DISTINCT IF(p_customer_type = 1, unionid, NULL)) AS newPayUv")
                        ->selectRaw("COUNT(IF(p_customer_type = 1, unionid, NULL)) AS newPayPv")
                        ->selectRaw("SUM(IF(p_customer_type = 1, pay_money, 0)) AS newPayAmount")
                        ->selectRaw("SUM(IF(p_customer_type = 2, pay_money, 0)) AS oldPayAmount")
                        ->whereIn('pid', $playletActivityIdList)
                        ->where('is_ad_user', 1)
                        ->where('pay_status', 1)
                        ->groupBy('pid')
                        ->get()
                        ->keyBy('pid')
                        ->toArray();

                    // 非投放回收金额
                    $otherOrderData = DjOrder::query()
                        ->select(['pid'])
                        ->selectRaw('sum(pay_money) as otherPayAmount')
                        ->whereIn('pid', $playletActivityIdList)
                        ->where('is_ad_user', 0)
                        ->where('pay_status', 1)
                        ->groupBy('pid')
                        ->get()
                        ->keyBy('pid')
                        ->toArray();
                }
            }

            # 处理数据
            foreach($list as $datum) {
                # 创建人信息
                $adminInfo = $adminData->where('id', $datum->admin_id)->first();
                $datum->creator = isset($adminInfo->name) ? $adminInfo->name : '';

                # 统计发送情况
                $sendStatInfo = $publishedStatData->where('rule_id', $datum->rule_id)->first();
                $datum->published_count = isset($sendStatInfo->published_count) ? $sendStatInfo->published_count : 0;
                $datum->unpublished_count = isset($sendStatInfo->unpublished_count) ? $sendStatInfo->unpublished_count : 0;
                $datum->published_total = isset($sendStatInfo->published_total) ? $sendStatInfo->published_total : 0;

                unset($datum->admin_id);

                $datumPids = json_decode($datum->pids, true);
                #统计 总播放人数（：总uv）  充值人数 充值次数（今日） 充值金额（今日）   付费率 点击率
                if($datumPids){
                    $datum->payAvg = $datum->payUv>0 ? round($datum->payPv/$datum->payUv, 2) : null;
                    $datum->payRate = $datum->viewUv>0 ? round($datum->payUv/$datum->viewUv*100, 2) .'%' : null;
                    $datum->clickRate = $datum->published_count>0 ? round($datum->viewUv/$datum->published_count*100, 2) .'%' : null;

                    $popu_list = MassPopularizData::select('viewPv', 'viewUv', 'payPv', 'payUv', 'payMoney', 'pid', 'playletActivityId')
                        ->whereIn('pid', $datumPids)
                        ->get();
                    if ($popu_list->isNotEmpty()) {
                        $datum->viewUv         = 0;
                        $datum->payUv          = 0;
                        $datum->payPv          = 0;
                        $datum->payMoney       = 0;
                        $datum->newPayUv       = 0;
                        $datum->newPayPv       = 0;
                        $datum->newPayAmount   = 0;
                        $datum->oldPayAmount   = 0;
                        $datum->otherPayAmount = 0;
                        foreach ($popu_list as $popu_info) {
                            $datum->viewUv   += ($popu_info->viewUv ?? 0);
                            $datum->payUv    += ($popu_info->payUv ?? 0);
                            $datum->payPv    += ($popu_info->payPv ?? 0);
                            $datum->payMoney += ($popu_info->payMoney ?? 0);
                            if(isset($popu_info->playletActivityId)) {
                                $datum->newPayUv       += ($orderData[$popu_info->playletActivityId]['newPayUv'] ?? 0);
                                $datum->newPayPv       += ($orderData[$popu_info->playletActivityId]['newPayPv'] ?? 0);
                                $datum->newPayAmount   += ($orderData[$popu_info->playletActivityId]['newPayAmount'] ?? 0);
                                $datum->oldPayAmount   += ($orderData[$popu_info->playletActivityId]['oldPayAmount'] ?? 0);
                                $datum->otherPayAmount += ($otherOrderData[$popu_info->playletActivityId]['otherPayAmount'] ?? 0);
                            }
                        }
                        $datum->newPayAmount   = round($datum->newPayAmount/10000, 2);
                        $datum->oldPayAmount   = round($datum->oldPayAmount/10000, 2);
                        $datum->otherPayAmount = round($datum->otherPayAmount/10000, 2);
                        $datum->newPayAvg      = $datum->newPayUv>0 ? round($datum->newPayPv/$datum->newPayUv, 2) : null;
                    } else {
                        $datum->viewUv         = 0;
                        $datum->payUv          = 0;
                        $datum->payPv          = 0;
                        $datum->payMoney       = 0;
                        $datum->newPayUv       = 0;
                        $datum->newPayPv       = 0;
                        $datum->newPayAmount   = 0;
                        $datum->oldPayAmount   = 0;
                        $datum->otherPayAmount = 0;
                        $datum->newPayAvg      = 0;
                    }
                } else {
                    $datum->viewUv = null;
                    $datum->payUv = null;
                    $datum->payPv = null;
                    $datum->payMoney = null;
                    $datum->payAvg = null;
                    $datum->payRate = null;
                    $datum->clickRate = null;
                    $datum->newPayUv = null;
                    $datum->newPayPv = null;
                    $datum->newPayAvg = null;
                    $datum->newPayAmount = null;
                    $datum->oldPayAmount = null;
                    $datum->otherPayAmount = null;
                }
            }

        } catch(\Exception $e) {
            Log::logError('获取朋友圈任务列表发生异常', [
                'line' => $e->getLine(),
                'msg' => $e->getMessage(),
            ], 'PopularizService');

            $errno = 2505;

            return [[], 0];
        }

        return [$list, $count];
    }

    public static function getMomentTotal($creatorId, $sendTimeStart, $sendTimeEnd, $is_system_admin, $group_admin_id, $sys_group_id)
    {
//        $corpids = null;
//        if($is_system_admin==0){
//            $corpids = StatisticsService::getAuthCorpids($group_admin_id);
//        }
        $corpids = StatisticsService::getAuthCorpids($sys_group_id);
        if(empty($corpids)) {
            return [];
        }
        $sendStatData= MomentTask::getPopuDataTotal($corpids, $creatorId, $sendTimeStart, $sendTimeEnd);
        return $sendStatData;
    }

    public static function getPlayletTitleByPid($pid, $mpAppId)
    {
        $playletTitle = MassPopularizData::query()
            ->where('pid', strval($pid))
            ->where('enable', 1)
            ->where(function($query) use ($mpAppId){
                if($mpAppId) $query->where('mpAppId', $mpAppId);
            })
            ->orderBy('create_time', 'desc')
            ->first();

        return $playletTitle;
    }

    public static function promotionData($platType, $sendTimeStart, $sendTimeEnd, $keyword, $page, $pageSize) {
        try {
            if(empty($platType)) {
                $platTypeList = [8, 10];
            } else {
                $platTypeList = [$platType];
            }
            $total = DjPromotionDataStat::promotionDataTotal($platTypeList, $sendTimeStart, $sendTimeEnd, $keyword);
            $totalStat['cost'] = $total->cost ?? 0;
            $totalStat['first_day_charge'] = $total->first_day_charge ?? 0;
            $totalStat['first_roi'] = $totalStat['cost'] > 0 ? round($totalStat['first_day_charge'] / $totalStat['cost'] * 100, 2) . '%' : '0%';

            list($data, $count) = DjPromotionDataStat::promotionList($platTypeList, $sendTimeStart, $sendTimeEnd, $keyword, $page, $pageSize);

            # 数据格式化
            foreach($data as $item) {
                $item->first_roi = ($item->first_roi * 100).'%';
            }

            return [$data, $count, $totalStat];
        }catch(\Exception $exception) {
            Log::logError('番茄推广数据接口异常', ['keyword' => $keyword,
                'send_time_start' => $sendTimeStart, 'send_time_end' => $sendTimeEnd, 'page' => $page, 'page_size' => $pageSize,
                'file' => $exception->getFile(), 'line' => $exception->getLine(), 'message' => $exception->getMessage(),
                'trace' => $exception->getTraceAsString()
            ], 'interface');
            return [[], 0];
        }
    }
}