novelPromote/app/Services/PlayletServi...

<?php


namespace App\Services;


use App\Log;
use App\Models\AccountPromoteConfig;
use App\Models\AdsReport;
use App\Models\PlayletStatisticsData;
use App\Models\VpAccount;
use App\Models\VpOrder;
use App\RedisModel;

class PlayletService
{
    const DAYS = 100;
    const ACTIVATE_FANS_DAYS = 100;
    const PAID_FANS_DAYS = 100;
    const FANS_ACTIVE = 'NOVEL:VpFansActive_';
    const CELL_LABEL = array('A','B','C','D','E','F','G','H','I','J','K', 'L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK', 'AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK', 'BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK', 'DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ');

    /**
     * 短剧数据趋势
     * @param $keyword string 公众号昵称关键词
     * @param $startDate string 查询起始日期
     * @param $endDate string 查询截止日期
     * @param $page integer 当前页码数
     * @param $pageSize integer 每页显示条数
     * */
    public static function dataTrend($keyword, $startDate, $endDate, $page, $pageSize)
    {
        $appIdLists = false;

        if(!empty($keyword)) {
            # 获取关键词对应的公众号AppId
//            $appIdLists = VpAccount::getAccountList(null, ['app_id'], ['nickname'=>$keyword, 'platform_id' => 1]);
            $appIdLists = VpAccount::getAccountList(null, ['app_id'], ['nickname'=>$keyword]);
        }

        # 获取不同时间段内新增的粉丝充值信息
        list($fansData, $total) = VpOrder::dataTrend($appIdLists, $startDate, $endDate, $page, $pageSize);

        # 获取公众号数据
        $appIds = $fansData->pluck('app_id');
        $accountList = VpAccount::selectRaw("distinct(app_id) as app_id, nickname")->whereIn('app_id', $appIds)->get();

        # 获取列表内的日期
        $dateList = $fansData->pluck('subscribed_date');
        # 获取各日期对应的花费
        $paidList = AdsReport::getPaidData($appIds, $dateList);

        foreach($fansData as $trend) {
            $trend->charge_total = $trend->charge_total / 10000;

            # 公众号信息
            $accountInfo = $accountList->where('app_id', $trend->app_id)->first();
            $trend->name = isset($accountInfo->nickname) ? $accountInfo->nickname : '';

            # 当日消耗
            $paidInfo = $paidList->where('app_id', $trend->app_id)
                ->where('ref_date', $trend->subscribed_date)
                ->first();

            $trend->day_paid = isset($paidInfo->paid) ? ($paidInfo->paid / 100) : 0;

            # 获取当日新用户充值情况
            $newUserCharge = VpOrder::getNewUserChargeFirstDay($trend->subscribed_date, $trend->app_id);
            $trend->new_user_charge = isset($newUserCharge->new_user_charge) ? ($newUserCharge->new_user_charge / 10000): 0;
            $trend->new_user_charge_uv = isset($newUserCharge->new_user_charge_uv) ? $newUserCharge->new_user_charge_uv : 0;
            $trend->new_user_charge_pv = isset($newUserCharge->new_user_charge_pv) ? $newUserCharge->new_user_charge_pv : 0;

            # 首日ROI
            $trend->first_day_roi = $trend->day_paid != 0 ? ($trend->new_user_charge / $trend->day_paid) : 0;

            # 企微关注数
            $trend->scan_follow_count = isset($paidInfo->scan_follow_count) ? $paidInfo->scan_follow_count : 0;
            # 企微关注成本
            $trend->follow_cost = $trend->scan_follow_count > 0 ? round($trend->day_paid / $trend->scan_follow_count, 3) : 0;

            # 新增粉丝数
            $trend->fans_increase = isset($paidInfo->follow_uv) ? $paidInfo->follow_uv : 0;
            # 首日用户成本
            $trend->charge_user_cost = $trend->new_user_charge_uv != 0 ? ($trend->day_paid / $trend->new_user_charge_uv) : 0;
            # 回本率
            $trend->cost_cover_rate = $trend->day_paid != 0 ? ($trend->charge_total / $trend->day_paid) : 0;

            # 获取当日关注用户的累计激活数据
            $newUserChargeCollect = VpOrder::getNewUserCharge($trend->subscribed_date, $trend->app_id);
            $trend->new_user_charge_total = isset($newUserChargeCollect->new_user_charge) ? $newUserChargeCollect->new_user_charge / 10000 : 0;
            $trend->new_user_charge_uv_count = isset($newUserChargeCollect->new_user_charge_uv) ? $newUserChargeCollect->new_user_charge_uv : 0;
            $trend->new_user_charge_pv_count = isset($newUserChargeCollect->new_user_charge_pv) ? $newUserChargeCollect->new_user_charge_pv : 0;

            // 充增回倍
            $trend->chargeData = self::chargeAfterSubscribed(
                $trend->subscribed_date, self::DAYS, $trend->app_id, $trend->first_day_roi, $trend->day_paid
            );
        }

        return [$fansData, $total];
    }

    /**
     * 短剧粉丝激活趋势
     * @param $keyword string 公众号昵称关键词
     * @param $startDate string 查询起始日期
     * @param $endDate string 查询截止日期
     * @param $page integer 当前页码数
     * @param $pageSize integer 每页显示条数
     * */
    public static function activateTrend($keyword, $startDate, $endDate, $page, $pageSize)
    {
        $appIdLists = false;

        if(!empty($keyword)) {
            # 获取关键词对应的公众号AppId
            $appIdLists = VpAccount::getAccountList(null, ['app_id'], ['nickname'=>$keyword]);
        }

        # 获取日期范围内充值信息
        list($activateData, $total) = VpOrder::subscribedFansData($appIdLists, $startDate, $endDate, $page, $pageSize);

        # 获取公众号数据
        $appIds = $activateData->pluck('app_id');
        $accountList = VpAccount::selectRaw("distinct(app_id) as app_id, nickname")->whereIn('app_id', $appIds)->get();

        # 获取列表内的日期
        $dateList = $activateData->pluck('subscribed_date');
        # 获取各日期对应的花费
        $paidList = AdsReport::getPaidData($appIds, $dateList);

        foreach ($activateData as $paid) {
            $paid->charge_total = $paid->charge_total / 10000;

            # 公众号信息
            $accountInfo = $accountList->where('app_id', $paid->app_id)->first();
            $paid->name = isset($accountInfo->nickname) ? $accountInfo->nickname : '';

            # 当日消耗
            $paidInfo = $paidList->where('app_id', $paid->app_id)
                ->where('ref_date', $paid->subscribed_date)
                ->first();

            $paid->day_paid = isset($paidInfo->paid) ? ($paidInfo->paid / 100) : 0;

            # 回本率
            $paid->cost_cover_rate = $paid->day_paid != 0 ? ($paid->charge_total / $paid->day_paid) : 0;

            # 获取当日新用户累计充值情况
            $newUserCharge = VpOrder::getNewUserCharge($paid->subscribed_date, $paid->app_id);
            $paid->new_user_charge = isset($newUserCharge->new_user_charge) ? $newUserCharge->new_user_charge / 10000 : 0;
            $paid->new_user_charge_uv = isset($newUserCharge->new_user_charge_uv) ? $newUserCharge->new_user_charge_uv : 0;
            $paid->new_user_charge_pv = isset($newUserCharge->new_user_charge_pv) ? $newUserCharge->new_user_charge_pv : 0;

            # 企微关注数
            $paid->scan_follow_count = isset($paidInfo->scan_follow_count) ? $paidInfo->scan_follow_count : 0;
            # 企微关注成本
            $paid->follow_cost = $paid->scan_follow_count > 0 ? round($paid->day_paid / $paid->scan_follow_count, 3) : 0;

            # 总充值数据
            $paid->charge_uv_all = VpOrder::getChargeUvOfTime($paid->app_id, $paid->subscribed_date, date("Y-m-d"));

            # 首日用户成本
            $paid->charge_user_cost = $paid->new_user_charge_uv != 0 ? ($paid->day_paid / $paid->new_user_charge_uv) : 0;

            # D1~Dn人数、成本、占比
            $paid->activeData = self::activeAfterSubscribed(
                $paid->subscribed_date, self::ACTIVATE_FANS_DAYS, $paid->app_id, $paid->day_paid
            );

            # 计算累计充值人数
            $paid->active_fans_total = collect($paid->activeData)->sum('fans');

            # 累计用户成本
            $paid->charge_user_cost_all = $paid->active_fans_total != 0 ? ($paid->day_paid / $paid->active_fans_total) : 0;
        }

        return [$activateData, $total];
    }

    /**
     *  计算某账号某日新增充值粉丝在后N天的充值数据
     *  @param $paid float 关注当日消耗
     *  @param $subscribedAt string 关注日期
     *  @param $days int 关注后的后几日数据
     *  @param $appId string 公众号AppId
     *  @return array
     * */
    public static function activeAfterSubscribed($subscribedAt, $days, $appId, $paid)
    {
        # 校验是否是日期以及$days的合法性
        if((date("Y-m-d", strtotime($subscribedAt)) != $subscribedAt) || !is_int($days)) {
            Log::logError('日期格式非法',[
                'subscribedAt' => $subscribedAt,
                'days' => $days,
            ], 'ActiveAfterSubscribed');
            return [];
        }

        # 日期处理
        $expiryAt = date("Y-m-d", strtotime('+'.$days. ' days', strtotime($subscribedAt)));
        if($expiryAt >date("Y-m-d")) $expiryAt = date("Y-m-d");
        $activeData = RedisModel::getAfterDecode(self::FANS_ACTIVE . $appId . '_' . $subscribedAt . '_' . $expiryAt);
        if(empty($chargeData)) {
            # 首次充值数据
            $data = VpOrder::getActiveAfterSubscribed($appId, $subscribedAt, $expiryAt, $subscribedAt);
            # 获取后N天每天的激活人数
            $activeData = [];
            $activeFansTotal = 0;
            for($i=0; $i<$days; $i++) {
                $refDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($subscribedAt)));
                if($refDate>date("Y-m-d")) {
                    $activeData[$i] = [];
                    continue;
                }
                # 第N天的激活人数
                $activeFans = $data->where('paid_at', '>=', $refDate . ' 00:00:00')
                    ->where('paid_at', '<=', $refDate . ' 23:59:59')
                    ->count();

                $activeFansTotal += $activeFans;

                $activeData[$i] = [
                    'fans' => $activeFans,
                    'cost' => $activeFansTotal != 0 ? ($paid / $activeFansTotal) : 0,
                ];
            }

//            RedisModel::setAfterEncode(self::FANS_ACTIVE . $appId . '_' . $subscribedAt . '_' . $expiryAt, $activeData);
//            RedisModel::expire(self::FANS_ACTIVE . $appId . '_' . $subscribedAt . '_' . $expiryAt, 86400);
        }

        return $activeData;
    }

    /**
     *  计算某账号某日新增充值粉丝在后N天的充值数据
     *  @param $paid float 关注当日消耗
     *  @param $subscribedAt string 关注日期
     *  @param $days int 关注后的后几日数据
     *  @param $appId string 公众号AppId
     *  @param $roi string 首日ROI
     *  @return array
     * */
    public static function chargeAfterSubscribed($subscribedAt, $days, $appId, $roi, $paid)
    {
        # 校验是否是日期以及$days的合法性
        if((date("Y-m-d", strtotime($subscribedAt)) != $subscribedAt) || !is_int($days)) {
            Log::logError('日期格式非法',[
                'subscribedAt' => $subscribedAt,
                'days' => $days,
                'appId' => $appId
            ], 'ChargeAfterSubscribed');
            return [];
        }

        # 日期处理
        $expiryAt = date("Y-m-d", strtotime('+'.$days. ' days', strtotime($subscribedAt)));
        if($expiryAt >date("Y-m-d")) $expiryAt = date("Y-m-d");
//        $chargeData = RedisModel::getAfterDecode(self::CHARGE_PAID . $appId . '_' . $subscribedAt . '_' . $expiryAt);
        $chargeData = [];
        if(empty($chargeData)) {
            # 充值数据
            $data = VpOrder::getChargeAfterSubscribed($appId, $subscribedAt, $expiryAt, $subscribedAt);

            # 获取后N天每天的充值数据及截止第N天的统计数据
            $chargeData = [];
            $chargePriceTotal = 0;
            for($i=0; $i<$days; $i++) {
                $refDate = date("Y-m-d", strtotime('+'.$i. ' days', strtotime($subscribedAt)));

                if($refDate>date("Y-m-d")) {
                    $chargeData[$i] = [];
                    continue;
                }

                # 第N天的充值数据
                $chargePrice = $data->where('paid_at', '>=', $refDate . ' 00:00:00')
                    ->where('paid_at', '<=', $refDate . ' 23:59:59')
                    ->sum('price');
                $chargePrice = $chargePrice / 10000;

                $chargePriceTotal += $chargePrice;

                $chargeData[$i] = [
                    'day_charge' => $chargePrice,
                    'charge_total' => $chargePriceTotal,
                    'day_paid' => $paid,
                    'day_add' => $paid != 0 ? ($chargePrice / $paid) : 0,
                    'day_cover' => $paid != 0 ? ($chargePriceTotal / $paid) : 0,
                    'day_times' => ($paid * $roi) != 0 ? ($chargePriceTotal / ($paid * $roi)) : 0
                ];
            }

        }

        return $chargeData;
    }

    public static function playletDataTotal($playletId, $page, $pageSize, $minDayPaid, $maxDayPaid)
    {
        $offset = ($page - 1) * $pageSize;
        $date = date('Y-m-d');
        $playletList = PlayletStatisticsData::where(function($query) use ($playletId, $minDayPaid, $maxDayPaid) {
            if($playletId) $query->where('playlet_id', $playletId);
            if($minDayPaid) $query->where('day_paid', '>=', $minDayPaid);
            if($maxDayPaid) $query->where('day_paid', '<=', $maxDayPaid);
        })->where('created_date', $date)->orderBy('start_date', 'desc')->offset($offset)->limit($pageSize)->get();

        $playletCount = PlayletStatisticsData::where(function($query) use ($playletId, $minDayPaid, $maxDayPaid) {
            if($playletId) $query->where('playlet_id', $playletId);
            if($minDayPaid) $query->where('day_paid', '>=', $minDayPaid);
            if($maxDayPaid) $query->where('day_paid', '<=', $maxDayPaid);
        })->where('created_date', $date)->count();

        foreach($playletList as $playlet) {
            $playlet->charge_data = json_decode($playlet->charge_data, 1);
            $playlet->subscribe_date = $playlet->start_date.'~'.$playlet->end_date;
        }

        return [$playletList, $playletCount];
    }

    public static function playletData($playletId, $startDate, $endDate, $page, $pageSize, $minDayPaid, $maxDayPaid)
    {
        $offset = ($page - 1) * $pageSize;
        $date = date('Y-m-d');
        $playletList = PlayletStatisticsData::where(function($query) use ($startDate, $endDate, $playletId, $minDayPaid, $maxDayPaid) {
            if($playletId) $query->where('playlet_id', $playletId);
            if($endDate) $query->where('start_date', '<=', $endDate);
            if($startDate) $query->where('start_date', '>=', $startDate);
            if($minDayPaid) $query->where('day_paid', '>=', $minDayPaid);
            if($maxDayPaid) $query->where('day_paid', '<=', $maxDayPaid);
        })->where('created_date', $date)->orderBy('start_date', 'desc')->offset($offset)->limit($pageSize)->get();

        $playletCount = PlayletStatisticsData::where(function($query) use ($startDate, $endDate, $playletId, $minDayPaid, $maxDayPaid) {
            if($playletId) $query->where('playlet_id', $playletId);
            if($endDate) $query->where('start_date', '<=', $endDate);
            if($startDate) $query->where('start_date', '>=', $startDate);
            if($minDayPaid) $query->where('day_paid', '>=', $minDayPaid);
            if($maxDayPaid) $query->where('day_paid', '<=', $maxDayPaid);
        })->where('created_date', $date)->count();

        foreach($playletList as $playlet) {
            $playlet->charge_data = json_decode($playlet->charge_data, 1);
            $playlet->subscribe_date = $playlet->start_date;
        }

        return [$playletList, $playletCount];
    }

    /*
     * 日期范围汇总数据
     */
    public static function singlePlayletData($playletId, $startDate, $endDate, $deadLine, $minDayPaid, $maxDayPaid)
    {
        # 按条件查询小说基础数据
        $playletData = PlayletStatisticsData::where(function($query) use ($playletId, $startDate, $endDate, $minDayPaid, $maxDayPaid) {
            if($playletId) $query->where('playlet_id', $playletId);
            if($startDate) $query->where('start_date', '>=', $startDate);
            if($endDate) $query->where('start_date', '<=', $endDate);
            if($minDayPaid) $query->where('day_paid', '>=', $minDayPaid);
            if($maxDayPaid) $query->where('day_paid', '<=', $maxDayPaid);
        })->get();

        if(empty($playletData)) {
            return [];
        }

        $playletData = $playletData->toArray();

        # 循环累加 因为首日roi无法通过sum求和计算
        $data = [
            'subscribe_date' => '',
            'playlet_name' => '',
            'day_paid' => 0,
            'charge_total' => 0,
            'fans_increase' => 0,
            'new_user_charge_uv' => 0,
            'new_user_charge_pv' => 0,
            'first_day_roi' => 0,
            'cost_cover_rate' => 0,
            'new_user_cost' => 0,
            'charge_user_cost' => 0,
        ];
        $realStartDate = null;
        $realEndDate = null;
        foreach($playletData as $playlet) {
            $item = isset($item) ? $item : ['day_charge' => 0, 'charge_total' => 0, 'day_paid' => 0];
            $data['subscribe_date'] = $startDate.'~'.$endDate;
            if(empty($realStartDate) || (!empty($realStartDate) && ($realStartDate > $playlet['start_date']))) {
                $realStartDate = $playlet['start_date'];
            }
            if(empty($realEndDate) || (!empty($realEndDate) && ($playlet['start_date'] > $realEndDate))) {
                $realEndDate = $playlet['start_date'];
            }

            $data['playlet_name'] = $playlet['playlet_name'];
            $data['day_paid'] = isset($data['day_paid']) ? $data['day_paid'] + $playlet['day_paid'] : 0;
            $data['charge_total'] = isset($data['charge_total']) ? $data['charge_total'] + $playlet['charge_total'] : 0;
            $data['fans_increase'] = isset($data['fans_increase']) ? $data['fans_increase'] + $playlet['fans_increase'] : 0;
            $data['new_user_charge_uv'] = isset($data['new_user_charge_uv']) ? $data['new_user_charge_uv'] + $playlet['new_user_charge_uv'] : 0;
            $data['new_user_charge_pv'] = isset($data['new_user_charge_pv']) ? $data['new_user_charge_pv'] + $playlet['new_user_charge_pv'] : 0;
            $data['cost_cover_rate'] = $data['day_paid'] != 0 ? ($data['charge_total'] / $data['day_paid']) : 0;
            $data['new_user_cost'] = $data['fans_increase'] != 0 ? ($data['day_paid'] / $data['fans_increase']) : 0;
            $data['charge_user_cost'] = $data['new_user_charge_uv'] != 0 ? ($data['day_paid'] / $data['new_user_charge_uv']) : 0;

            $chargeData = json_decode($playlet['charge_data'], 1);
            foreach($chargeData as $key => $charge) {
                if(0 == $key) {
                    $item['day_charge'] = isset($charge['day_charge']) ? $item['day_charge'] + $charge['day_charge'] : $item['day_charge'];
                    $item['charge_total'] = isset($charge['charge_total']) ? $item['charge_total'] + $charge['charge_total'] : $item['charge_total'];
                    $item['day_paid'] = isset($charge['day_paid']) ? $item['day_paid'] + $charge['day_paid'] : $item['day_paid'];
                    $data['first_day_roi'] = isset($item['day_paid']) && isset($item['day_charge']) && $item['day_paid'] != 0 ? ($item['day_charge'] / $item['day_paid']) : 0;
                }
            }
        }

        /*
         * 如果有收益截止日期 用截止日期来计算当日新用户累计充值以及当回本率
         * 然后那个（当日新用户累计充值）字段的用户订单金额  是只要（起止时间）内注册的用户充值数据
         * 超过（起止时间）内注册的用户不去计算
         */
        if($deadLine) {
            $start = $realStartDate > $startDate ? $realStartDate : $startDate;
            $end = $realEndDate < $endDate ? $realEndDate : $endDate;
            $data['charge_total'] = 0;
            while($start <= $end){
                $appIdList = AccountPromoteConfig::getAppIdsByPlayletId($playletId, $start, $start);
                if(!empty($appIdList)){
                    $charge = VpOrder::getDeadLineChargeData($appIdList, $start, $start, $deadLine);
                    $data['charge_total'] = $data['charge_total'] + (!empty($charge) ? $charge->charge_total / 10000 : 0);
                }
                $start = date('Y-m-d', strtotime($start.' +1 day'));
            }

            $data['cost_cover_rate'] = $data['day_paid'] != 0 ? ($data['charge_total'] / $data['day_paid']) : 0;
        }

        return $data;
    }
}