playlet/app/Service/CustomerStag...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2022/11/23
 * Time: 21:07
 */

namespace App\Service;


use App\Log;
use App\Models\CustomerDetails;
use App\Models\DjCustomerStage;
use App\Models\DjCustomerStageDailyReport;
use App\Models\Tag;
use App\RedisModel;

class CustomerStageService
{
    public static function setStageRule($ruleId, $params)
    {
        $params['stage_rule'] = is_array($params['stage_rule']) ? json_encode($params['stage_rule'], 256)
            : $params['stage_rule'];

        try {
            // 验证每种规则只可以添加一次
            $code = DjCustomerStage::validateRule($params['stage_rule']);
            if(0 != $code) return $code;

            if($ruleId){    // 编辑群发规则
                $result = DjCustomerStage::query()->where('id', $ruleId)->update($params);
            } else {
                # 设置新群发规则
                $customerStageModel = new DjCustomerStage();
                $customerStageModel->creator_id   = \Auth::id();
                $customerStageModel->corpid       = $params['corpid'];
                $customerStageModel->sys_group_id = $params['sys_group_id'];
                $customerStageModel->stage_title  = $params['stage_title'];
                $customerStageModel->stage_desc   = $params['stage_desc'];
                $customerStageModel->stage_rule   = $params['stage_rule'];
                $customerStageModel->deep         = 1;

                # 开启事务
                \DB::begintransaction();
                $result = $customerStageModel->save();

                // 将现有阶段深度全部自增
                $updateRes = DjCustomerStage::query()->where('corpid', $params['corpid'])
                    ->where('sys_group_id', $params['sys_group_id'])->where('enable', 1)
                    ->increment('deep');

                if($updateRes && $result) {
                    \DB::commit();
                } else {
                    \DB::rollBack();
                    return 3702;
                }
            }

            if(!$result) return 3701;

        } catch (\Exception $e) {
            Log::logError('客户阶段规则设置发生异常', [
                'line'   => $e->getLine(),
                'msg'    => $e->getMessage(),
                'trace'  => $e->getTraceAsString(),
                'params' => $params
            ], 'SetCustomerStageRule');

            return 3702;
        }

        return 0;
    }

    public static function stageRuleList($corpid, $sysGroupId, $page, $pageSize)
    {
        $offset = ($page - 1) * $pageSize;

        $customerStageQuery = DjCustomerStage::query()->where('sys_group_id', $sysGroupId)
            ->where('corpid', $corpid)->where('enable', 1);

        $countQuery = clone $customerStageQuery;

        $count = $countQuery->count();
        $data = $customerStageQuery->selectRaw('id as rule_id, stage_title, stage_desc, stage_rule, deep')
            ->offset($offset)->limit($pageSize)->orderBy('deep', 'asc')->get();

        return [$data, $count];
    }
    
    public static function stageRuleSort($params)
    {
        try{
            // 查询被操作规则原始排序值
            $operatorRule = DjCustomerStage::query()->where('id', $params['operate_rule_id'])->first();
            $operatorRuleOriginalSortOrder = $operatorRule->deep;

            if(!empty($params['behind_rule_id']) && empty($params['front_rule_id'])) {
                // 上移到第一位，判断是否有调整后的排在调整规则后面的规则id
                $behindRule = DjCustomerStage::query()->where('id', $params['behind_rule_id'])->first();
                $behindRuleOriginalSortOrder = $behindRule->deep;

                list($data, $code) = self::ruleMoveUp($params, $behindRuleOriginalSortOrder, $operatorRuleOriginalSortOrder);
                return [$data, $code];

            } else if(!empty($params['front_rule_id']) && empty($params['behind_rule_id'])) {
                // 下移到最后一位，判断是否有调整后的排在调整规则前面的规则id
                $frontRule = DjCustomerStage::query()->where('id', $params['front_rule_id'])->first();
                $frontRuleOriginalSortOrder = $frontRule->deep;

                list($data, $code) = self::ruleMoveDown($params, $frontRuleOriginalSortOrder, $operatorRuleOriginalSortOrder);
                return [$data, $code];
            } else if(!empty($params['front_rule_id']) && !empty($params['behind_rule_id'])) {
                $frontRule = DjCustomerStage::query()->where('id', $params['front_rule_id'])->first();
                $frontRuleOriginalSortOrder = $frontRule->deep;

                $behindGroup = DjCustomerStage::query()->where('id', $params['behind_rule_id'])->first();
                $behindGroupOriginalSortOrder = $behindGroup->deep;

                if($operatorRuleOriginalSortOrder < $frontRuleOriginalSortOrder) {
                    // 下移
                    list($data, $code) = self::ruleMoveDown($params, $frontRuleOriginalSortOrder, $operatorRuleOriginalSortOrder);
                } else {
                    // 上移
                    list($data, $code) = self::ruleMoveUp($params, $behindGroupOriginalSortOrder, $operatorRuleOriginalSortOrder);
                }

                return [$data, $code];
            } else {
                Log::logError('stageRuleSort', [
                    'params' => $params,
                    'message' => '被操作规则前后规则id不可同时为空',
                ],'interface');
                return ['被操作规则前后规则id不可同时为空', 3405];
            }
        } catch (\Exception $exception) {
            Log::logError('stageRuleSort', [
                'params'  => $params,
                'file'    => $exception->getFile(),
                'line'    => $exception->getLine(),
                'message' => $exception->getMessage(),
                'trace'   => $exception->getTraceAsString(),
            ], 'interface');

            return ['移动失败', 400];
        }
    }

    // 上移
    public static function ruleMoveUp($params, $behindGroupOriginalSortOrder, $operatorRuleOriginalSortOrder)
    {
        \DB::begintransaction();
        $moveRes = DjCustomerStage::query()
            ->where('corpid', $params['corpid'])->where('sys_group_id', $params['sys_group_id'])
            ->where('enable', 1)
            ->where('deep', '<', $operatorRuleOriginalSortOrder)
            ->where('deep', '>=', $behindGroupOriginalSortOrder)->increment('deep');// 自增1

        $operatorRes = DjCustomerStage::query()->where('id', $params['operate_rule_id'])
            ->update(['deep' => $behindGroupOriginalSortOrder]);

        if($moveRes && $operatorRes) {
            \DB::commit();
            return ['移动成功', 0];
        } else {
            \DB::rollBack();
            Log::logError('stageRuleSort', [
                'type' => '上移',
                'params' => $params,
                'message' => '移动失败',
                'move_res' => $moveRes,
                'operator_res' => $operatorRes,
                'operator_deep' => $operatorRuleOriginalSortOrder,
                'behind_deep' => $behindGroupOriginalSortOrder,
            ],'interface');
            return ['移动失败', 400];
        }
    }

    // 下移
    public static function ruleMoveDown($params, $frontRuleOriginalSortOrder, $operatorRuleOriginalSortOrder)
    {
        \DB::begintransaction();
        $moveRes = DjCustomerStage::query()->where('corpid', $params['corpid'])->where('enable', 1)
            ->where('sys_group_id', $params['sys_group_id'])
            ->where('deep', '>', $operatorRuleOriginalSortOrder)
            ->where('deep', '<=', $frontRuleOriginalSortOrder)
            ->decrement('deep');// 自减1

        $operatorRes = DjCustomerStage::query()->where('id', $params['operate_rule_id'])
            ->update(['deep' => $frontRuleOriginalSortOrder]);

        if($moveRes && $operatorRes) {
            \DB::commit();
            return ['移动成功', 0];
        } else {
            \DB::rollBack();
            Log::logError('stageRuleSort', [
                'type' => '下移',
                'params' => $params,
                'message' => '移动失败',
                'move_res' => $moveRes,
                'operator_res' => $operatorRes,
                'operator_deep' => $operatorRuleOriginalSortOrder,
                'front_deep' => $frontRuleOriginalSortOrder,
            ],'interface');
            return ['移动失败', 400];
        }
    }

    public static function stageRuleDel($ruleId, $corpid, $sysGroupId)
    {
        \DB::begintransaction();
        $res = DjCustomerStage::query()->where('id', $ruleId)->update(['enable' => 0]);

        # 当前阶段是被删除阶段的情况
        $customerCount1 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('now_stage', $ruleId)
            ->count();
        if($customerCount1 > 0) {
            $customerRes1 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)
                ->where('now_stage', $ruleId)->update([
                    'now_stage' => 0, 'last_stage' => 0, 'stage_change_date' => null
                ]);
        } else {
            $customerRes1 = true;
        }

        # 上一阶段是当前被删除的阶段，且当前客户未流失
        $customerCount2 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('last_stage', $ruleId)
            ->where('loss_status', 1)->count();
        if($customerCount2 > 0) {
            $customerRes2 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('last_stage', $ruleId)
                ->where('loss_status', 1)
                ->update([
                    'now_stage' => 0, 'last_stage' => 0, 'stage_change_date' => null,
                ]);
        } else {
            $customerRes2 = true;
        }

        # 上一阶段是当前被删除的阶段，且当前客户已流失
        $customerCount3 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('last_stage', $ruleId)
            ->where('loss_status', 0)->count();

        if($customerCount3 > 0) {
            $customerRes3 = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('last_stage', $ruleId)
                ->where('loss_status', 0)
                ->update([
                    'last_stage' => 0
                ]);
        } else {
            $customerRes3 = true;
        }

        if($res && $customerRes1 && $customerRes2 && $customerRes3) {
            \DB::commit();
            return 0;
        } else {
            \DB::rollBack();
            return 3704;
        }
    }

    public static function confirmSet($corpid, $sysGroupId)
    {
        // 将此消息加入待处理队列
        RedisModel::lPush(DjCustomerStage::CUSTOMER_STAGE_SYNC, json_encode(['corpid' => $corpid], 256));

        return 0;
    }

    public static function basicDataAnalysis($corpid)
    {
        # 添加缓存
//        $dataJson = RedisModel::get('Playlet::basicDataAnalysis-'.$corpid);
//        if(!empty($dataJson)) {
//            return json_decode($dataJson, 1);
//        }

        # 客户性别占比
        $manCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 1)->where('loss_status', 1)->first();
        $data['gender_stat']['man_cnt'] = $manCnt->count ?? 0;

        $womenCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 2)->where('loss_status', 1)->first();
        $data['gender_stat']['women_cnt'] = $womenCnt->count ?? 0;

        $unknownGenderCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 0)->where('loss_status', 1)->first();
        $data['gender_stat']['unknown_cnt'] = $unknownGenderCnt->count ?? 0;

        # 客户付费情况占比
        $payCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('pay_num', '>', 0)->where('loss_status', 1)->first();
        $data['pay_stat']['pay_cnt'] = $payCnt->count ?? 0;

        $unPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('pay_num', 0)->where('loss_status', 1)->first();
        $data['pay_stat']['unpay_cnt'] = $unPayCnt->count ?? 0;

        # 不同性别付费情况占比
        $payManCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 1)->where('loss_status', 1)->where('pay_num', '>', 0)->first();
        $data['gender_pay_stat']['man_cnt'] = $payManCnt->count ?? 0;

        $payWomenCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 2)->where('loss_status', 1)->where('pay_num', '>', 0)->first();
        $data['gender_pay_stat']['women_cnt'] = $payWomenCnt->count ?? 0;

        $unKnownGenderPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('gender', 0)->where('loss_status', 1)->where('pay_num', '>', 0)->first();
        $data['gender_pay_stat']['unknown_cnt'] = $unKnownGenderPayCnt->count ?? 0;

        # 客户付费情况分析
        $twentyPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->whereBetween('pay_num', [1, 20])->where('loss_status', 1)->first();
        $data['pay_num_stat']['twenty'] = $twentyPayCnt->count ?? 0;

        $fortyPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->whereBetween('pay_num', [21, 40])->where('loss_status', 1)->first();
        $data['pay_num_stat']['forty'] = $fortyPayCnt->count ?? 0;

        $sixtyPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->whereBetween('pay_num', [41, 60])->where('loss_status', 1)->first();
        $data['pay_num_stat']['sixty'] = $sixtyPayCnt->count ?? 0;

        $eightyPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->whereBetween('pay_num', [61, 80])->where('loss_status', 1)->first();
        $data['pay_num_stat']['eight'] = $eightyPayCnt->count ?? 0;

        $hundredPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->whereBetween('pay_num', [81, 100])->where('loss_status', 1)->first();
        $data['pay_num_stat']['hundred'] = $hundredPayCnt->count ?? 0;

        $moreThanHundredPayCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('pay_num', '>', 100)->where('loss_status', 1)->first();
        $data['pay_num_stat']['more'] = $moreThanHundredPayCnt->count ?? 0;

        # 客户添加时长
        $twoMonthAddTimeCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-2 months'))
            ->first();
        $data['add_time_stat']['two'] = $twoMonthAddTimeCnt->count ?? 0;

        $fourMonthAddTimeCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-4 months'))
            ->where('createtime', '<', strtotime('-2 months'))
            ->first();
        $data['add_time_stat']['four'] = $fourMonthAddTimeCnt->count ?? 0;

        $sixMonthAddTimeCnt =  CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-6 months'))
            ->where('createtime', '<', strtotime('-4 months'))
            ->first();
        $data['add_time_stat']['six'] = $sixMonthAddTimeCnt->count ?? 0;

        $eightMonthAddTimeCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-8 months'))
            ->where('createtime', '<', strtotime('-6 months'))
            ->first();
        $data['add_time_stat']['eight'] = $eightMonthAddTimeCnt->count ?? 0;

        $tenMonthAddTimeCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-10 months'))
            ->where('createtime', '<', strtotime('-8 months'))
            ->first();
        $data['add_time_stat']['ten'] = $tenMonthAddTimeCnt->count ?? 0;

        $twelveMonthAddTimeCnt = CustomerDetails::suffix($corpid)->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '>=', strtotime('-12 months'))
            ->where('createtime', '<', strtotime('-10 months'))
            ->first();
        $data['add_time_stat']['twelve'] = $twelveMonthAddTimeCnt->count ?? 0;

        $moreThanTwelveMonthAddTimeCnt = CustomerDetails::suffix($corpid)
            ->selectRaw('count(distinct(external_userid)) as count')
            ->where('corpid', $corpid)->where('loss_status', 1)
            ->where('createtime', '<', strtotime('-12 months'))
            ->first();
        $data['add_time_stat']['more'] = $moreThanTwelveMonthAddTimeCnt->count ?? 0;

        RedisModel::set('Playlet::basicDataAnalysis-'.$corpid, json_encode($data));
        // 缓存十分钟
        RedisModel::expire('Playlet::basicDataAnalysis-'.$corpid, 600);

        return $data;
    }

    public static function tagRankList($corpid, $page, $pageSize)
    {
        $offset = ($page - 1) * $pageSize;

        $tagQuery = Tag::query()
            ->where('corpid', $corpid)
            ->where('enable', 1);

        $countQuery = clone $tagQuery;
        $count = $countQuery->count();

        $data = $tagQuery->select(['tag_id', 'tag_name', 'customer_num'])->offset($offset)->limit($pageSize)
            ->orderBy('customer_num', 'desc')->get();

        return [$data, $count];
    }

    public static function analysis($corpid, $sysGroupId)
    {
        $data = [];
        # 客户资产
        $customerCnt = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('loss_status', 1)->count();
        $data[] = [
            'rule_id'     => 0,
            'stage_title' => '客户资产',
            'cnt'         => $customerCnt,
        ];

        # 阶段列表
        $stageList = DjCustomerStage::query()->where('corpid', $corpid)->where('sys_group_id', $sysGroupId)
            ->where('enable', 1)->select(['id', 'stage_title'])->orderBy('deep', 'asc')
            ->get();

        $stageIdList = array_column($stageList->toArray(), 'id');
        # 批量查询各个阶段客户数
        $stageCustomerCntList = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('loss_status', 1)
            ->whereIn('now_stage', $stageIdList)->selectRaw('now_stage, count(1) as count')->groupBy('now_stage')->get();

        # 各个阶段当前客户数
        foreach($stageList as $stageInfo) {
            $item = [];
            $customerInfo = $stageCustomerCntList->where('now_stage', $stageInfo->id)->first();
            $item['rule_id'] = $stageInfo->id;
            $item['stage_title'] = $stageInfo->stage_title;
            $item['cnt'] = $customerInfo->count ?? 0;

            $data[] = $item;
        }

        return $data;
    }

    public static function trend($ruleId, $corpid, $startDate, $endDate)
    {
        $list = DjCustomerStageDailyReport::query()->where('rule_id', $ruleId)->where('corpid', $corpid)
            ->whereBetween('ref_date', [$startDate, $endDate])->where('enable', 1)
            ->select(['total_cnt', 'ref_date', 'new_contact_cnt', 'loss_contact_cnt', 'net_growth_contact_cnt'
                , 'pay_contact_cnt', 'pay_money'])
            ->orderBy('ref_date', 'asc')->get();

        return $list;
    }

    public static function situation($corpid, $sysGroupId, $startDate, $endDate)
    {
        # 获取阶段列表
        $stageList = DjCustomerStage::query()->where('enable', 1)
            ->where('corpid', $corpid)->where('sys_group_id', $sysGroupId)
            ->selectRaw('id as rule_id, stage_title')->orderBy('deep', 'asc')->get();

        if($stageList->isEmpty()) {
            return [];
        }

        $stageList = $stageList->toArray();
        $total = 0;
        foreach($stageList as $key=>$stageInfo) {
            list($data, $cnt) = self::getCustomerStageConversion($stageInfo, $corpid, $sysGroupId, $startDate, $endDate);
            $stageList[$key]['conversion'] = $data;
            $stageList[$key]['cnt'] = $cnt;
            $total += $cnt;
        }

        return ['data' => $stageList, 'total' => $total];
    }

    public static function getCustomerStageConversion($stageInfo, $corpid, $sysGroupId, $startDate, $endDate)
    {
        $data = [];
        $total = 0;

        # 查询时间范围内各个阶段客户数
        $list = CustomerDetails::suffix($corpid)->where('corpid', $corpid)->where('last_stage', $stageInfo['rule_id'])
            ->whereBetween('stage_change_date', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
            ->selectRaw('now_stage, count(1) as cnt')->groupBy('now_stage')->get();

        $ruleData = DjCustomerStage::query()->where('corpid', $corpid)->where('enable', 1)
            ->where('sys_group_id', $sysGroupId)->selectRaw('id as rule_id,'
            .' stage_title')->orderBy('deep', 'asc')->get();

        $lossStat = $list->where('now_stage', -1)->first();
        $lossCnt = $lossStat->cnt ?? 0;
        $data[] = [
            'rule_id' => -1,
            'stage_title' => '已流失',
            'cnt' => $lossCnt,
        ];

        $total += $lossCnt;

        foreach($ruleData as $rule){
            $item = json_decode(json_encode($rule), 1);
            $customerStat = $list->where('now_stage', $rule->rule_id)->first();
            $cnt = $customerStat->cnt ?? 0;
            $total += $cnt;
            $item['cnt'] = $cnt;
            $data[] = $item;
        }

        return [$data, $total];
    }
}