playlet/app/Service/CustomerLoss...

<?php


namespace App\Service;


use App\Models\AuthorizeCorp;
use App\Models\Report\CustomerLossTrendData;
use App\Models\System\AdminManageCorp;

class CustomerLossService
{
    /**
     * 账号客户流失数据趋势
     * */
    public static function getFansLossTrend($params, $sortField, $sortType, $page, $pageSize)
    {
        # 表头处理
        $commonHeader = config('customerLoss.basic');
        # 获取day1至day150表头
        $daysHeader = [];
        for($i = 0;$i < CustomerLossTrendData::CUSTOMER_LOSS_TREND_DAYS;$i++) {
            $title = [
                'name' => 'DAY'.($i+1),
                'column' => 'day'.($i+1),
            ];

            $daysHeader[] = $title;
        }
        $header = array_merge($commonHeader, $daysHeader);

        # 数据项处理
        if(isset($params['corpid']) && $params['corpid']) {
            list($list, $count) = CustomerLossTrendData::getFansLossTrend($params, $sortField, $sortType, $page, $pageSize);
        } else {
            # 获取账号可见的corpid
            $corpIds = AdminManageCorp::where('sys_user_id', $params['sys_group_id'])->pluck('corpid');
            $accountList = AuthorizeCorp::select('corpid')->where('enable', 1)
                ->whereIn('id', $corpIds)
                ->distinct()->pluck('corpid');

            if(empty($accountList)) return [[], 0, $header];
            list($list, $count) = CustomerLossTrendData::getFansLossDataSummary(
                $params, $accountList, $sortField, $sortType, $page, $pageSize
            );
        }

        if(empty($list)) return [[], 0, $header];

        # 企微信息处理
        $corpIds = $list->pluck('corpid')->unique();
        $accountData = AuthorizeCorp::select('corpid', 'corp_name')->whereIn('corpid', $corpIds)->get();

        foreach ($list as $item) {
            # 企微名称
            $accountInfo = $accountData->where('corpid', $item->corpid)->first();
            $item->corp_name = $accountInfo->corp_name ?? '-';

            # 消耗金额单位处理
            $item->paid = round($item->paid / 100, 2);

            # 企微关注成本
            $item->follow_cost = round($item->follow_cost / 100, 1);

            # 流失率
            $item->first_loss_rate = $item->first_loss_rate * 100 . '%';
            $item->third_loss_rate = $item->third_loss_rate * 100 . '%';
            $item->seventh_loss_rate = $item->seventh_loss_rate * 100 . '%';
            $item->fifteenth_loss_rate = $item->fifteenth_loss_rate * 100 . '%';
            $item->thirtieth_loss_rate = $item->thirtieth_loss_rate * 100 . '%';
            $item->loss_rate = $item->loss_rate * 100 . '%';

            # 查询60天数据
            $dayInfo = [];
            if(isset($params['corpid']) && $params['corpid']) {
                $dayData = CustomerLossTrendData::where('enable', 1)
                    ->where('corpid', $params['corpid'])
                    ->where('expense_date', $item->expense_date)
                    ->where('ref_date', '<=', date('Y-m-d', strtotime($item->expense_date.' +59 day')))
                    ->selectRaw("day_loss_user_count, ref_date, days_type")
                    ->groupBy(['days_type'])
                    ->get();

                if(!empty($dayData)){
                    foreach($dayData as $val){
                        $nd = (strtotime($val['ref_date']) - strtotime($item->expense_date)) / 86400;
                        $lossCountInfo = $dayData->where('days_type', '=', ($nd+1))->first();
                        $lossCount = $lossCountInfo->day_loss_user_count ?? 0;
                        $lossTotal = $dayData->where('days_type', '<=', ($nd+1))->sum('day_loss_user_count');
                        $newLossRate = $item->add_user_count ? round($lossCount / $item->add_user_count, 4) * 100 . '%' : '0%';
                        $totalLossRate = $item->add_user_count ? round($lossTotal / $item->add_user_count, 4) * 100 . '%' : '0%';

                        $dayInfo[$nd] = [
                            'days' => 'day' . ($nd + 1),
                            'loss_count' => $lossCount,
                            'new_loss_rate' => $newLossRate,
                            'total_loss_rate'=> $totalLossRate,
                        ];
                    }
                }
            } else {
                $dayData = CustomerLossTrendData::where('enable', 1)
                    ->where('corpid', $item->corpid)
                    ->where('expense_date', '>=', $item->expense_date)
                    ->where('expense_date', '<=', $item->expense_date_end)
                    ->where('ref_date', '<=', strtotime($item->expense_date_end.' +59 day'))
                    ->selectRaw("sum(day_loss_user_count) as day_loss_user_count,ref_date, expense_date, days_type")
                    ->groupBy(['days_type'])
                    ->get();

                $paidDataMid = [];
                $lossUserTotal = 0;
                if(!empty($dayData)){
                    foreach($dayData as $val){
                        $nd = (strtotime($val['ref_date']) - strtotime($val['expense_date'])) / 86400;
                        $lossUserTotal += $val['day_loss_user_count'];
                        if(isset($paidDataMid[$nd])) {
                            $paidDataMid[$nd]['loss_user'] += $val['day_loss_user_count'];
                            $paidDataMid[$nd]['loss_user_total'] += $lossUserTotal;
                        } else {
                            $paidDataMid[$nd]['loss_user'] = $val['day_loss_user_count'];
                            $paidDataMid[$nd]['loss_user_total'] = $lossUserTotal;
                        }
                    }

                    foreach ($paidDataMid as $i=>$v) {
                        $lossCount = $v['loss_user'] ?? 0;
                        $lossTotal  =$v['loss_user_total'] ?? 0;
                        $newLossRate = $item->add_user_count ? round( $lossCount / $item->add_user_count, 4) * 100 . '%' : '0%';
                        $totalLossRate = $item->add_user_count ? round( $lossTotal / $item->add_user_count, 4) * 100 . '%' : '0%';

                        $dayInfo[$i] = [
                            'days' => 'day' . ($i + 1),
                            'loss_count' => $lossCount,
                            'new_loss_rate' => $newLossRate,
                            'total_loss_rate' => $totalLossRate,
                        ];
                    }
                }
            }

            $item->day_info = $dayInfo;
        }

        return [$list, $count, $header];
    }

    /**
     * 账号客户流失数据趋势汇总
     * */
    public static function getFansLossSummary($params)
    {
        # 表头处理
        $header = config('customerLoss.basic');

        # 数据处理
        if(isset($params['corpid']) && $params['corpid']) {
            $accountList = [$params['corpid']];
        } else {
            if(isset($params['app_id']) && $params['app_id']) {
                $accountList = [$params['account_id']];
            } else {
                # 获取账号可见的corpid
                $corpIds = AdminManageCorp::where('sys_user_id', $params['sys_group_id'])->pluck('corpid');
                $accountList = AuthorizeCorp::select('corpid')->where('enable', 1)
                    ->whereIn('id', $corpIds)
                    ->distinct()->pluck('corpid');
            }

            if(empty($accountList)) return [[], $header];
        }

        $data = CustomerLossTrendData::getFansLossSummary($params, $accountList);
        # 消耗金额单位处理
        $data->paid = round($data->paid / 100, 2);
        # 企微关注成本
        $data->follow_cost = round($data->follow_cost / 100, 1);
        # 流失率
        $data->first_loss_rate = $data->first_loss_rate * 100 . '%';
        $data->third_loss_rate = $data->third_loss_rate * 100 . '%';
        $data->seventh_loss_rate = $data->seventh_loss_rate * 100 . '%';
        $data->fifteenth_loss_rate = $data->fifteenth_loss_rate * 100 . '%';
        $data->thirtieth_loss_rate = $data->thirtieth_loss_rate * 100 . '%';
        $data->loss_rate = $data->loss_rate * 100 . '%';
        if(empty($data)) return [[], $header];

        return [$data, $header];
    }

    /**
     * 账号客户流失趋势曲线
     * */
    public static function getFansLossCurve($params)
    {
        if(isset($params['corpid']) && $params['corpid']) {
            $accountList = [$params['corpid']];
        } else {
            # 获取账号可见的corpid
            $corpIds = AdminManageCorp::where('sys_user_id', $params['sys_group_id'])->pluck('corpid');
            $accountList = AuthorizeCorp::select('corpid')->where('enable', 1)
                ->whereIn('id', $corpIds)
                ->distinct()->pluck('corpid');

            if(empty($accountList)) return [];
        }

        # 客户流失数据汇总曲线
        $data = CustomerLossTrendData::getFansLossCurve($params, $accountList);
        # 数据处理
        foreach($data as $item) {
            # 消耗金额
            $item->paid = round($item->paid / 100, 2);
        }

        return $data;
    }

}