qimall/common/logic/statistics/UserTeamStatisticsLogic.php

<?php
namespace common\logic\statistics;


use common\enums\RabbitMqEnum;
use common\enums\RedisKeyEnum;
use common\enums\StatusEnum;
use common\helpers\FormatHelper;
use common\models\statistics\UserStatisticsTotal;
use common\models\statistics\UserTeamStatistics;
use common\models\user\User;
use common\models\user\UserPartitionRelationship;
use common\traits\ErrorTrait;
use Yii;
use yii\db\Exception;
use yii\db\Expression;

/**
 * 用户团队数据统计
 * @Author lun
 * @DateTime 2021-11-16 19:07:32
 * @copyright: Copyright (c) 2020 广东七件事集团
 */
class UserTeamStatisticsLogic{
    use ErrorTrait;
    /**
     * 订单创建统计
     * @Author: lun
     * @DateTime: 2021/11/20 0020 15:47
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param null $params
     */
    public function execute($mall_id)
    {
        // 订单创建统计处理
        Yii::$app->db->close();
        try {
            // 初始化分片参数
            $part = 1;
            $limit = 1000;
            $select = "id,mall_id";
            $part_where = [['=','mall_id',$mall_id], ['=','status',StatusEnum::ENABLED]];
            $params = ['where' => $part_where, 'select' => $select];
            while ($part_data = User::batchGetUsers($params, $part, $limit)){// 查询user表的
                $ids = implode(',', array_column($part_data,'id'));
                if ($ids) {
                    $data = ['mall_id' => $mall_id, 'part' => $part, 'ids' => $ids];
//                    Yii::$app->services->rabbitMq->push(RabbitMqEnum::EXCHANGE_TASK, RabbitMqEnum::TASK_QUEUE, $data, self::class, 'async_handle');
                    self::async_handle($data);
                }
                $part ++;
            }
            return true;
        } catch (\Exception $e) {
            $exception = FormatHelper::exception($e, "用户团队数据-每日统计");
            echo $exception;
            Yii::error($exception);
            $this->setError($exception);
            return false;
        }
    }


    /**
     * 异步处理单个分片数据
     * @Author: lun
     * @DateTime: 2021/12/6 0006 10:25
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param null $params
     */
    public static function async_handle($params = null) {
        try {
            $t1 = time();
            $ids = explode(',', $params['ids']);
            $mall_id = $params['mall_id'];
            $has_parent_list = User::find()->select('parent_id')->where(['parent_id'=>$ids])->groupBy('parent_id')->asArray()->all();
            if (empty($has_parent_list)) return true;
            // 用户数据统计查询字段
            $statistics_field = "sum(pay_num) pay_num,sum(pay_money) pay_money";
            /** 活跃用户：七天内有登录 **/
            /** 新增会员：七天内注册的用户 **/
            $seven_day = strtotime('-7 days');
            /** 流失用户：30天内没登录过 **/
            $thirty_day = strtotime('-30 days');

            // 条件初始化
            $where = ['=', 'status', StatusEnum::ENABLED];

            foreach ($has_parent_list as $k => $user){
                // 更新sql语句初始化
                $insert = "INSERT INTO `qimall_user_team_statistics` (`mall_id`, `user_id`, `active_user_team_total`, `loss_user_team_total`, `user_team_order_total`, `user_team_order_price_total`,
                        `active_user_direct_total`, `loss_user_direct_total`, `new_user_direct_total`, `user_direct_order_total`, `user_direct_order_price_total`, 
                        `active_user_indirect_total`, `loss_user_indirect_total`, `user_indirect_order_total`, `user_indirect_order_price_total`, `created_at`, `updated_at`) VALUES (";
                $update = "UPDATE `qimall_user_team_statistics` SET ";
                $is_insert = $is_update = false;

                $user_id = $user['parent_id'];
                // 参数初始化
                $data = [
                    'mall_id' => $mall_id,
                    'user_id' => $user_id,
                    'active_user_team_total' => 0,
                    'loss_user_team_total' => 0,
                    'user_team_order_total' => 0,
                    'user_team_order_price_total' => 0,
                    'active_user_direct_total' => 0,
                    'loss_user_direct_total' => 0,
                    'new_user_direct_total' => 0,
                    'user_direct_order_total' => 0,
                    'user_direct_order_price_total' => 0,
                    'active_user_indirect_total' => 0,
                    'loss_user_indirect_total' => 0,
                    'user_indirect_order_total' => 0,
                    'user_indirect_order_price_total' => 0,
                ];

                // 分片获取用户团队数据
                $team_offset = 1;$team_limit = 1000;
                list($tree, $self_deep, $all_tree, $all_tree_arr) = UserPartitionRelationship::getTree($user_id);// 获取本人当前tree数据
                $team_params = [];
                while ($team_data = UserPartitionRelationship::batchGetTeamData($team_offset, $team_limit, $tree, $team_params)){// 查询关系链表
                    $team_uids = $direct_uids = $indirect_uids = [];
                    foreach ($team_data as $item) {
                        // 获取该用户的所有下级
                        $team_uids[] = $item['user_id'];// 团队用户
                        if ($self_deep + 1 == $item['deep']) $direct_uids[] = $item['user_id'];// 直推用户
                        if ($self_deep + 2 == $item['deep']) $indirect_uids[] = $item['user_id'];// 间推用户
                    }
//                $last_primary_id = $item['id'];
//                $team_params['where'][] = ['>','id',$last_primary_id];
                    $team_offset++;

                    /** 团队用户数据查询 - 开始 **/
                    // sqi = `user_team_total`, `active_user_team_total`, `loss_user_team_total`, `user_team_order_total`, `user_team_order_price_total`,
                    if (!empty($team_uids)) {
                        // 团队活跃会员总数
                        $data['active_user_team_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$team_uids])->andWhere(['>=', 'last_login_at', $seven_day])->count();

                        // 团队流失用户
                        $data['loss_user_team_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$team_uids])->andWhere(['<=', 'last_login_at', $thirty_day])->count();

                        // 团队会员订单统计
                        $team_order = UserStatisticsTotal::find()->select($statistics_field)->andWhere($where)->andWhere(['=','mall_id',$mall_id])->andWhere(['in','user_id',$team_uids])->asArray()->one();
                        $data['user_team_order_total'] += $team_order['pay_num'] ?? 0;// 团队会员订单总数
                        $data['user_team_order_price_total'] += $team_order['pay_money'] ?? 0;// 团队会员订单总额
                    }
                    /** 团队用户数据查询 - 结束 **/

                    /** 直推用户数据查询 - 开始 **/
                    // sqi = `user_direct_total`, `active_user_direct_total`, `loss_user_direct_total`, `new_user_direct_total`, `user_direct_order_total`, `user_direct_order_price_total`,
                    if (!empty($direct_uids)) {
                        // 直推活跃会员总数
                        $data['active_user_direct_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$direct_uids])->andWhere(['>=', 'last_login_at', $seven_day])->count();

                        // 直推流失用户总数
                        $data['loss_user_direct_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$direct_uids])->andWhere(['<=', 'last_login_at', $thirty_day])->count();

                        // 直推近七天新增用户 - 仅直推才有
                        $data['new_user_direct_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$direct_uids])->andWhere(['>=', 'created_at', $seven_day])->count();

                        // 直推会员订单统计
                        $direct_order = UserStatisticsTotal::find()->select($statistics_field)->andWhere(['=','mall_id',$mall_id])->andWhere(['in','user_id',$direct_uids])->andWhere($where)->asArray()->one();
                        $data['user_direct_order_total'] += $direct_order['pay_num'] ?? 0;// 直推会员订单总数
                        $data['user_direct_order_price_total'] += $direct_order['pay_money'] ?? 0;// 直推会员订单总额
                    }
                    /** 直推用户数据查询 - 结束 **/

                    /** 间推用户数据查询 - 开始 **/
                    // sqi = `active_user_indirect_total`, `loss_user_indirect_total`, `user_indirect_order_total`, `user_indirect_order_price_total`,
                    if (!empty($indirect_uids)) {
                        // 间推活跃会员总数
                        $data['active_user_indirect_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$indirect_uids])->andWhere(['>=', 'last_login_at', $seven_day])->count();

                        // 间推流失会员总数
                        $data['loss_user_indirect_total'] += User::find()->select("id")->andWhere($where)->andWhere(['in','id',$indirect_uids])->andWhere(['<=', 'last_login_at', $thirty_day])->count();

                        // 间推会员订单统计
                        $indirect_order = UserStatisticsTotal::find()->select($statistics_field)->andWhere(['=','mall_id',$mall_id])->andWhere(['in','user_id',$indirect_uids])->andWhere($where)->asArray()->one();
                        $data['user_indirect_order_total'] += $indirect_order['pay_num'] ?? 0;// 间推会员订单总数
                        $data['user_indirect_order_price_total'] += $indirect_order['pay_money'] ?? 0;// 间推会员订单总额
                    }
                    /** 间推用户数据查询 - 结束 **/
                }

                // 判断是否已有用户数据
                $model = UserTeamStatistics::findOne(['mall_id' => $mall_id, 'user_id' => $user_id, 'status' => StatusEnum::ENABLED]);
                foreach ($data as $key => $val) {
                    if (empty($model)) {
                        $insert.= $val . ",";
                        $is_insert = true;
                    } else {
                        if ($key == 'mall_id' || $key == 'user_id') continue;
                        $update.= $key . "=" . $val . ",";
                        $is_update = true;
                    }
                }
                $insert = rtrim($insert, ',') . ")";
                $is_update && $update = rtrim($update, ',') . " where id=" . $model->id;

                // 批量执行sql语句
                $connection = Yii::$app->db;
                $trans = $connection->beginTransaction();
                try {
                    $is_insert && $connection->createCommand($insert)->execute();
                    $is_update && $connection->createCommand($update)->execute();
                    $trans->commit();
                } catch(\Exception $e){// 如果有一条查询失败，则会抛出异常
                    self::setStaticError($e->getMessage());
                    $trans->rollBack();
                }
            }
            $t2 = time();
            echo "part={$params['part']},时间：" .($t2-$t1). "，执行成功".PHP_EOL;
        } catch (\Exception $e) {
            $exception = FormatHelper::exception($e, "part={$params['part']},mall={$params['mall_id']},用户团队数据-每日统计");
            echo $exception;
            Yii::error($exception);
        }
    }


    /**
     * 用户上级订单统计维护
     * $params = ['user_id' => 12219, 'pay_money' => 100.25];
     * @Author: lun
     * @DateTime: 2021/12/10 0010 16:56
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param null $params
     * @return bool
     */
    public static function incBatchParentDataByUid($params = null)
    {
        $trans = Yii::$app->db->beginTransaction();
        try {
            if (empty($params))throw new Exception('传参有误');
            $user_id = $params['user_id'];$time = time();
            $pay_money = $params['pay_money'];
            $original_order_money = $params['original_order_money'];

            // 查询此用户是否有上级
            $parent_list = UserPartitionRelationship::getParentIdArr($user_id);// 获取本人当前tree数据
            if (empty($parent_list)) {
                $trans->commit();
                return true;
            }
            $mall_id = User::find()->select('mall_id')->where(['id'=>$user_id])->scalar();
            $parent_list = array_reverse($parent_list);// 数组倒叙

            $field = "id,mall_id,user_id,user_team_order_total,user_direct_order_total,user_indirect_order_total,user_team_order_price_total,user_direct_order_price_total,user_indirect_order_price_total," .
                "user_direct_pay_selling_price, user_indirect_pay_selling_price, team_pay_selling_price";
            foreach ($parent_list as $key => $pid) {
                $model = UserTeamStatistics::find()->select($field)->where(['user_id' => $pid, 'status' => StatusEnum::ENABLED])->one();
                $emptyModel = empty($model);
                if ($emptyModel) {
                    $model = new UserTeamStatistics();
                    $model->mall_id = $mall_id;
                    $model->user_id = $pid;
                    $model->loadDefaultValues();
                } else {
                    if ($key > 1) $inc_uid[] = $pid;
                }

                // 新增用户或直推间推上级
                if ($emptyModel || in_array($key,[0,1])) {
                    // 上级团队
                    $model->user_team_order_total = $model->user_team_order_total + 1;
                    $model->user_team_order_price_total = $model->user_team_order_price_total + $pay_money;
                    $model->team_pay_selling_price = $model->team_pay_selling_price + $original_order_money;
                    if ($key == 0) {// 直推上级
                        $model->user_direct_order_total = $model->user_direct_order_total + 1;
                        $model->user_direct_order_price_total = $model->user_direct_order_price_total + $pay_money;
                        $model->user_direct_pay_selling_price = $model->user_direct_pay_selling_price + $original_order_money;
                    } elseif ($key == 1) {// 间推上级
                        $model->user_indirect_order_total = $model->user_indirect_order_total + 1;
                        $model->user_indirect_order_price_total = $model->user_indirect_order_price_total + $pay_money;
                        $model->user_indirect_pay_selling_price = $model->user_indirect_pay_selling_price + $original_order_money;
                    }
                    if (!$model->save()) throw new Exception($model->getErrorMessage());
                }
            }

            // 批量修改数据 - 除开直推间推的所有需要修改的上级用户
            if (!empty($inc_uid)) {
                $res = UserTeamStatistics::updateAll(
                    [
                        'user_team_order_total' => new Expression('user_team_order_total+1'),
                        'user_team_order_price_total' => new Expression('user_team_order_price_total+' . $pay_money),
                        'team_pay_selling_price' => new Expression('team_pay_selling_price+' . $original_order_money),
                        'updated_at' => $time
                    ],
                    ['user_id' => $inc_uid]
                );
                if ($res == false) throw new Exception(UserTeamStatistics::getStaticError());
            }
            $trans->commit();
            return true;
        } catch (\Exception $e) {
            $trans->rollBack();
            $exception = FormatHelper::exception($e, "用户上级订单数据维护");
            echo $exception;
            return false;
        }
    }


    /**
     * 用户上级订单统计维护
     * $params = ['user_id' => 12219, 'pay_money' => 100.25];
     * @Author: lun
     * @DateTime: 2021/12/10 0010 16:56
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param null $params
     * @return bool
     */
    public static function decBatchParentDataByUid($params = null)
    {
        $trans = Yii::$app->db->beginTransaction();
        try {
            if (empty($params) || empty($params['dec_update_field']))throw new Exception('传参有误');

            $user_id = $params['user_id'];
            $time = time();

            // 查询此用户是否有上级
            $parent_list = UserPartitionRelationship::getParentIdArr($user_id);// 获取本人当前tree数据
            if (empty($parent_list)) {
                $trans->commit();
                return true;
            }

            // 批量修改数据
            if (!empty($parent_list)) {
                $updateData = [];

                foreach ($params['dec_update_field'] as $key => $val) {
                    $updateData[$key] = new Expression($key . '-' . $val);
                }

                if ($updateData) {
                    $updateData['updated_at'] = $time;
                    $res = UserTeamStatistics::updateAll($updateData, ['user_id' => $parent_list]);
                    if ($res == false) throw new Exception(UserTeamStatistics::getStaticError());
                }
            }
            $trans->commit();
            return true;
        } catch (\Exception $e) {
            $trans->rollBack();
            $exception = FormatHelper::exception($e, "用户上级订单数据维护：decBatchParentDataByUid");
            echo $exception;
            return false;
        }
    }


    /**
     * @notes:完成订单增加上级业绩处理
     * @param $params
     * @return bool
     * @author: lun
     * @Time: 2022/11/7 17:17
     */
    public static function incBatchParentFinishOrderByUid($params = null)
    {
        $trans = Yii::$app->db->beginTransaction();
        try {
            if (empty($params))throw new Exception('传参有误');
            $user_id = $params['user_id'];$time = time();
            $pay_money = $params['pay_money'];
            $selling_price = $params['selling_price'];

            // 查询此用户是否有上级
            $parent_list = UserPartitionRelationship::getParentIdArr($user_id);// 获取本人当前tree数据
            if (empty($parent_list)) {
                $trans->commit();
                return true;
            }
            $parent_list = array_reverse($parent_list);// 数组倒叙

            /* 新增直推和间推的已完成的售价总额 */
            // 直推
            if (isset($parent_list[0])) {
                UserTeamStatistics::updateAllCounters(['user_direct_finish_selling_price' => $selling_price], ['user_id' => $parent_list[0]]);
            }
            // 间推
            if (isset($parent_list[1])) {
                UserTeamStatistics::updateAllCounters(['user_indirect_finish_selling_price' => $selling_price], ['user_id' => $parent_list[1]]);
            }

            // 批量修改数据 - 除开直推间推的所有需要修改的上级用户
            if (!empty($parent_list)) {
                $res = UserTeamStatistics::updateAll(
                    [
                        'team_finish_order_total' => new Expression('team_finish_order_total+1'),
                        'team_finish_order_price_total' => new Expression('team_finish_order_price_total+' . $pay_money),
                        'team_finish_selling_price' => new Expression('team_finish_selling_price+' . $selling_price),
                        'updated_at' => $time
                    ],
                    ['user_id' => $parent_list]
                );
                if ($res == false) throw new Exception(UserTeamStatistics::getStaticError());
            }
            $trans->commit();
            return true;
        } catch (\Exception $e) {
            $trans->rollBack();
            $exception = FormatHelper::exception($e, "用户上级订单数据维护");
            echo $exception;
            return false;
        }
    }

    /**
     * @notes:老用户数据数据完成团队数据修改
     * @param $params
     * @return bool
     * @author: lun
     * @Time: 2022/11/8 13:45
     */
    public static function countOldUserTeamAchievement($params)
    {
        $mall_id = $params['mall_id'];
        $redis = Yii::$app->redis;
        $cacheKey = RedisKeyEnum::suffix(UserTeamStatistics::COUNT_FINISH_ORDER_KEY, 'mall_id_' . $mall_id);
        try {
            // 防止多次执行
            $checkCount = $redis->get($cacheKey);
            if ($checkCount && $checkCount == 1) return true;
            $redis->set($cacheKey, 1);
            $time = time();
            $part = 1;
            $limit = 1000;
            while ($list = UserTeamStatistics::find()->where(['mall_id' => $mall_id, 'status' => StatusEnum::ENABLED])->select('user_id')->orderBy('id asc')
                ->offset(($part - 1) * $limit)->limit($limit)->all()) {
                foreach ($list as $item) {
                    // 获取当前用户的下级团队
                    $team_offset = 1;$team_limit = 1000;
                    $team_finish_order_total = 0;// 团队完成订单数量
                    $team_finish_order_price_total = 0;// 团队完成订单总额
                    list($tree, $self_deep, $all_tree, $all_tree_arr) = UserPartitionRelationship::getTree($item['user_id']);// 获取本人当前tree数据
                    while ($team_data = UserPartitionRelationship::batchGetTeamData($team_offset, $team_limit, $tree)) {// 查询关系链表
                        $team_uids = array_column($team_data, 'user_id');
                        $finish_order_num = UserStatisticsTotal::find()->where(['user_id' => $team_uids, 'status' => StatusEnum::ENABLED])->sum('finish_order_num') ?? 0;
                        $finish_order_money = UserStatisticsTotal::find()->where(['user_id' => $team_uids, 'status' => StatusEnum::ENABLED])->sum('finish_order_money') ?? 0;
                        $team_finish_order_total += $finish_order_num;
                        $team_finish_order_price_total += $finish_order_money;
                        $team_offset++;
                    }

                    if (empty($team_finish_order_total)) continue;
                    UserTeamStatistics::updateAll([
                        'team_finish_order_total' => $team_finish_order_total,
                        'team_finish_order_price_total' => $team_finish_order_price_total,
                        'updated_at' => $time,
                    ],['user_id' => $item['user_id'], 'status' => StatusEnum::ENABLED]);
                }
                $part ++;
            }
            echo '执行成功';
            return true;
        } catch (\Exception $e) {
            $redis->set($cacheKey, 0);// 失败解锁
            self::setStaticError($e->getMessage());
            echo '执行失败：'.$e->getMessage();
            return false;
        }
    }

}