qimall/common/models/statistics/UserStatistics.php

<?php

namespace common\models\statistics;

use common\enums\StatusEnum;
use common\models\base\BaseActiveRecord;
use common\enums\RedisKeyEnum;
use common\models\census\CensusMallSingleDay;
use common\models\census\CensusMallUser;
use Yii;
use yii\redis\Connection;
use common\models\common\Download;
use common\enums\DownloadEnum;
use common\components\export\CsvTool;
use common\models\user\User;
use common\enums\OrderStatusEnum;
use common\models\order\Order;
use yii\db\Expression;

/**
 *
 * This is the model class for table "{{%user_statistics}}".
 * @property int $id 记录ID
 * @property int $mall_id 商城ID
 * @property int $user_id 用户ID
 * @property int $order_num 下单数
 * @property int $pay_num 支付数
 * @property float $pay_money 支付金额
 * @property float $average_money 客单价
 * @property int $finish_order_num 已完成订单数
 * @property float $finish_order_money 已完成订单金额
 * @property int $refund_order_num 退款订单数
 * @property float $refund_order_money 退款订单金额
 * @property float $recharge_money 充值金额
 * @property float $pay_selling_price 已支付的订单售价总额（商品价格）
 * @property float $finish_selling_price 已完成的订单售价总额（商品价格）
 * @property float $user_goods_price_total 支付金额不包含运费
 * @property float $user_order_num 订单总数
 * @property int $date 日期，年月日
 * @property int $status 状态【1启用 0禁用 -1删除】
 * @property int $created_at 创建时间
 * @property int $updated_at 更新事件
 */
class UserStatistics extends BaseActiveRecord
{

    const ORDER_NUM = 'order_num';//订单数量
    const PAY_NUM = 'pay_num';//支付数量
    const TOTAL_MONEY = 'total_money';//总金额
    const TODAY_MONEY = 'today_money';//今日收益


    /**
     * {@inheritdoc}
     */
    public static function tableName() {
        return '{{%user_statistics}}';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['mall_id', 'user_id'], 'required'],
            [['mall_id', 'user_id', 'order_num', 'pay_num', 'date', 'status', 'finish_order_num', 'refund_order_num', 'pay_goods_num', 'cancel_order_num', 'finish_goods_num', 'cancel_goods_num', 'refund_goods_num'], 'integer'],
            [['finish_order_money', 'refund_order_money', 'pay_money', 'average_money', 'recharge_money', 'original_order_money', 'order_money', 'balance_pay', 'wechat_pay', 'ali_pay', 'cancel_order_money',
                 'pay_selling_price', 'finish_selling_price'],'number']
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'mall_id' => '商城id',
            'user_id' => '用户id',
            'pay_num' => '支付量',
            'order_num' => '订单量',
            'pay_money' => '支付金额',
            'average_money' => '客单价',
            'pay_goods_num' => '支付商品件数',
            'date' => '日期',
            'order_money' => '下单金额',
            'cancel_order_num' => '已取消订单数',
            'cancel_order_money' => '已取消订单金额',
            'cancel_goods_num' => '已取消商品数',
            'original_order_money' => '支付订单原金额',
            'finish_order_num' => '已完成订单数',
            'finish_order_money' => '已完成订单金额',
            'finish_goods_num' => '已完成商品数',
            'refund_order_num' => '退款订单数',
            'refund_order_money' => '退款订单金额',
            'refund_goods_num' => '退款商品数',
            'recharge_money' => '充值金额',
            'balance_pay' => '余额支付金额',
            'wechat_pay' => '微信支付金额',
            'ali_pay' => '支付宝支付金额',
            'pay_selling_price' => '已支付的订单售价总额（商品价格）',
            'finish_selling_price' => '已完成的订单售价总额（商品价格）',
            'user_goods_price_total' => '支付金额不包含运费',
            'user_order_num' => '订单总数',
            'status' => '状态'
        ];
    }

    /**
     * 商城首页订单统计
     * 
     * @param int $mall_id
     * @author hpei
     * @return array
     */
    public static function getUserStatistics($mall_id) {
        try {
            $redis = \Yii::$app->redis;
            //获取会员来源统计
            $cacheKeySource = RedisKeyEnum::suffix(RedisKeyEnum::SOURCE_STATISTICS, 'mall_id_' . $mall_id);
            $sourceStatistics = $redis->get($cacheKeySource);
            if (empty($sourceStatistics)) {
                $sourceStatistics = self::staticsUserSource($mall_id);
                self::setUserSourceStatisticsCache($mall_id, $sourceStatistics);
            }
            //用户购买力统计 staticsUserBuy
            $cacheKeyBuy = RedisKeyEnum::suffix(RedisKeyEnum::BUY_STATISTICS, 'mall_id_' . $mall_id);
            $buyStatistics = $redis->get($cacheKeyBuy);
            if (empty($buyStatistics)) {
                $buyStatistics = self::staticsUserBuy($mall_id);
                self::setUserBuyStatisticsCache($mall_id, $buyStatistics);
            }
            //会员订单统计
            $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::ORDER_STATISTICS, 'mall_id_' . $mall_id);
            $orderStatistics = $redis->hgetall($cacheKey);
            if (empty($orderStatistics)) {
                $orderStatistics = self::createCacheData($mall_id);
                self::setUserStatisticsCache($mall_id, $orderStatistics);
            } else {
                $num = 0;
                $fields = [];
                $values = [];
                foreach ($orderStatistics as $val) {
                    if ($num % 2 === 0) {
                        $fields[] = $val;
                    } else {
                        $values[] = $val;
                    }
                    $num++;
                }
                $orderStatistics = array_combine($fields, $values);
            }
            if(!empty($sourceStatistics)) $orderStatistics['source'] = json_decode($sourceStatistics);
            if(!empty($buyStatistics)) $orderStatistics['buy'] = json_decode($buyStatistics);
            return $orderStatistics;
        } catch (\Exception $e) {
            Yii::error('读取订单统计缓存异常：' . $e->getMessage());
            return self::getDefaultData();
        }
    }

    /**
     * 设置数据
     * 
     * @param int $mall_id
     * @param int $user_id
     * @param array $data [order_num=>$order_num,pay_num=>$pay_num,pay_money=>$pay_money]
     * @author hpei
     * @return bool
     */
    public static function setUserStatistics($mall_id, $user_id, $data) {
        try {
            $date = date('Ymd');
            if (isset($data['ymd'])) {
                $date = $data['ymd'];
                unset($data['ymd']);
            }
            $orderMdl = self::find()->where(['mall_id' => $mall_id, 'date' => $date, 'user_id' => $user_id])->indexBy('date')->one();
            if (empty($orderMdl)) {
                $orderMdl = new self();
                $orderMdl->mall_id = $mall_id;
                $orderMdl->date = $date;
                $orderMdl->user_id = $user_id;
            }
            // 循环赋值
            foreach ($orderMdl as $key => $value) {
                if (isset($data[$key]) && !empty($data[$key]) && $key != 'goods_num_data') $orderMdl->$key += $data[$key];
            }

            // 客单价处理
            !empty($data['pay_money']) && $orderMdl->average_money = bcdiv($orderMdl->pay_money, $orderMdl->pay_num, 2);

            $chcheData = [
                'pay_money' => isset($data['pay_money']) ? $data['pay_money'] : 0.00,
                'pay_num' => isset($data['pay_num']) ? $data['pay_num'] : 0,
                'order_num' => isset($data['order_num']) ? $data['order_num'] : 0,
            ];
            if (!$orderMdl->save()) {
                self::$static_error = $orderMdl->getErrorMessage();
                return false;
            }
            if ($chcheData['pay_money'] > 0 || $chcheData['pay_num'] > 0 || $chcheData['order_num'] > 0) {
                self::setUserStatisticsCache($mall_id, $chcheData, true);
            }
            return true;
        } catch (\Exception $e) {
            self::$static_error = $e->getMessage();
            return false;
        }
    }

    /**
     * 设置缓存
     * 
     * @param int $mall_id
     * @param array $data
     * @author hpei
     * @return void
     */
    public static function setUserStatisticsCache($mall_id, $data, $is_edit = false) {
        $config = \Yii::$app->getComponents()['redis'];
        unset($config['class']);
        $redis = new Connection($config);
        $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::ORDER_STATISTICS, 'mall_id_' . $mall_id);
        if ($is_edit) {
            if (isset($data['pay_money']) && !empty($data['pay_money'])) {
                $redis->hincrbyfloat($cacheKey, self::TOTAL_MONEY, $data['pay_money']);
                $redis->hincrbyfloat($cacheKey, self::TODAY_MONEY, $data['pay_money']);
            }
            if (isset($data['pay_num']) && !empty($data['pay_num'])) $redis->hincrby($cacheKey, self::PAY_NUM, $data['pay_num']);
            if (isset($data['order_num']) && !empty($data['order_num'])) $redis->hincrby($cacheKey, self::ORDER_NUM, $data['order_num']);
        } else {
            $redis->hmset($cacheKey, 
                self::ORDER_NUM, $data[self::ORDER_NUM], 
                self::PAY_NUM, $data[self::PAY_NUM], 
                self::TOTAL_MONEY, $data[self::TOTAL_MONEY], 
                self::TODAY_MONEY, $data[self::TODAY_MONEY]
            );
        }
        //$redis->expire($cacheKey, strtotime("+1 day 0:0:0") - time());
        $redis->close();
    }

    /**
     * 缓存用户来源数据
     * @Author: ltm
     * @DateTime: 2022/3/16 0016 14:35
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param $data
     * @return string
     */
    public static function setUserSourceStatisticsCache($mall_id, $data) {
        $config = \Yii::$app->getComponents()['redis'];
        unset($config['class']);
        $redis = new Connection($config);
        $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::SOURCE_STATISTICS, 'mall_id_' . $mall_id);
        $redis->set($cacheKey,$data);
        $redis->expire($cacheKey, strtotime("+1 day 0:0:0") - time());
        $redis->close();
    }
    /**
     * 缓存用户购买力数据
     * @Author: ltm
     * @DateTime: 2022/3/16 0016 14:35
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param $data
     * @return string
     */
    public static function setUserBuyStatisticsCache($mall_id, $data) {
        $config = \Yii::$app->getComponents()['redis'];
        unset($config['class']);
        $redis = new Connection($config);
        $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::BUY_STATISTICS, 'mall_id_' . $mall_id);
        $redis->set($cacheKey,$data);
        $redis->expire($cacheKey, strtotime("+1 day 0:0:0") - time());
        $redis->close();
    }
    /**
     * 生成缓存数据
     */
    public static function createCacheData($mall_id) {
        try {
            $orderStatistics = self::find()
                ->select(["sum(pay_num) as pay_num", 'sum(order_num) as order_num', 'sum(pay_money) as total_money'])
                ->asArray()
                ->where(['mall_id' => $mall_id])
                ->one();
            if (empty($orderStatistics['total_money'])) $orderStatistics['total_money'] = 0;
            if (empty($orderStatistics['pay_num'])) $orderStatistics['pay_num'] = 0;
            if (empty($orderStatistics['order_num'])) $orderStatistics['order_num'] = 0;
            $todayMoney = self::find()->select('pay_money')->where(['mall_id' => $mall_id, 'date' => date('Ymd')])->column();
            $orderStatistics['today_money'] = empty($todayMoney) ? 0 : $todayMoney[0];
            return $orderStatistics;
        } catch (\Exception $e) {
            throw new \Exception('用户缓存。生成缓存数据' . $e->getMessage() . ',行数:' . $e->getLine());
        }
    }

    /**
     * 设置默认展示数据
     * 
     * @author hpei
     * @return array
     */
    public static function getDefaultData() {
        return [self::ORDER_NUM => 0, self::PAY_NUM => 0, self::TOTAL_MONEY => 0, self::TODAY_MONEY => 0];
    }

    /**
     * 资产概况统计
     * 
     * @param array $params
     * @author hpei
     * @return array
     */
    public static function userOrderStatistics($params, $mall_id) {
        $params['group'] = 'mall_id';
        $params['select'] = [
            'sum(order_money) as order_money',                      //下单总金额
            'sum(original_order_money) as original_order_money',    //优惠前订单总金额(已支付)
            'sum(pay_money) as pay_money',                          //支付金额
            'sum(refund_order_money) as refund_order_money',        //已完成订单金额
            'sum(recharge_money) as recharge_money',                //充值金额
            'sum(balance_pay) as balance',                      //余额支付金额
            'sum(wechat_pay) as wechat',                        //微信支付金额
            'sum(ali_pay) as alipay'                               //支付宝支付金额
        ];
        $model = self::find();
        self::paramPack($params, $model);
        $list = $model->asArray()->one();
        $list['deduction_money'] = $list['original_order_money'] - $list['pay_money'];//抵扣金额
        $orderWhere = [];
        foreach ($params['where'] as $key => $where) {
            if ($where[1] == 'created_at' && $where[0] == '>=') {
//                $orderWhere['where'][] = ['>=', 'created_at', strtotime(substr($where[2], 0, 4) . '-' . substr($where[2], 4, 2) . '-' . substr($where[2], 6, 2).'00:00:00')];
                $orderWhere['where'][] = ['>=', 'created_at', $where[2]];
            }
            if ($where[1] == 'created_at' && $where[0] == '<=') {
                $orderWhere['where'][] = ['<=', 'created_at', $where[2]];
            }
        }
        $orderWhere['where'][] = ['=', 'order_status', OrderStatusEnum::NOT_PAY];
        $orderWhere['where'][] = ['=', 'mall_id', $mall_id];
        $orderMdl = Order::find();
        Order::paramPack($orderWhere, $orderMdl);
        $list['not_pay_money'] = $orderMdl->sum('pay_money');
        array_walk($list, function(&$v) {
            $v = round($v, 2);
        });
        return $list;
    }

    public static function getOrderStatistics($params, array $select = [], $group = false, $limit = 0, $order = '') {
        if ($group != false) $params['group'] = $group;
        if (!empty($select)) $params['select'] = $select;
        if (!empty($order)) $params['order'] = $order;
        $model = self::find();
        if ($limit > 0) {
            $model->limit($limit);
        }
        self::paramPack($params, $model);
        $list = $model->asArray()->all();
        return $list ?? [];
    }

    /**
     * 导出排行榜
     */
    public static function exportRankHandleOld(array $data) {
        $download_id = $data['download_id'] ?? 0;
        $download = Download::findOne(['id' => $download_id]);
        if($download) {
            try {
                $params = json_decode($download->params,true);
                $filename = $download->name;
                $type = $download->type;
                $csv = new CsvTool();
                $where['where'][] = ['=', 'mall_id', $params['mall_id']];
                $list = self::getOrderStatistics($where, ['user_id', new Expression('0 as nickname'), 'sum(pay_money) as pay_money', 'sum(pay_num) as pay_num', 'sum(pay_goods_num) as pay_goods_num'], 'user_id', 100, 'pay_money desc');
                if (!empty($list)) {
                    $user_ids = array_column($list, 'user_id');
                    $userList = User::find()->where(['id' => $user_ids])->select(['avatar_url','nickname','id'])->indexBy('id')->asArray()->all();
                    foreach ($list as &$goods) {
                        if (isset($userList[$goods['user_id']]['nickname'])) {
                            $goods['nickname'] = $userList[$goods['user_id']]['nickname'];
                        } else {
                            $goods['nickname'] = '';
                        }
                        $goods['pay_money'] = $goods['pay_money'] ?? 0;
                        $goods['pay_money'] = $goods['pay_money'] ?? 0;
                        $goods['pay_money'] = $goods['pay_money'] ?? 0;
                    }
                    $csv->filename = $filename;
                    $csv->file_dirname = DownloadEnum::getTypeStorageDirMap($type);
                    $csv->export_mode = CsvTool::EXPORT_MODE_ASYNC;
                    $csv->header = ['用户ID', '用户昵称' , '销售额', '销量','支付件数'];
                    $csv->data = $list;
                    $csv->export();
                    $csv->updateDown($download, $params['mall_id']);
                }
                return true;
            } catch(\Exception $e){
                $download->status = 3;
                $res = $download->save();
                self::setStaticError($e->getMessage());
                return false;
            }
        }
    }
    public static function exportRankHandle(array $data) {
        $download_id = $data['download_id'] ?? 0;
        $download = Download::findOne(['id' => $download_id]);
        if($download) {
            try {
                $params = json_decode($download->params,true);
                $filename = $download->name;
                $type = $download->type;
                $csv = new CsvTool();
                $mall_id = $params['mall_id'];
                $where = [];
                $where[]=['mall_id'=>$mall_id];
                $params['where'] = $where;
                $params['limit'] = 100;
                $params['order'] = 'total_pay_money desc';
                $list = CensusMallUser::lists($params);
                if(!empty($list)){
                    $rows = [];
                    $user_ids = array_column($list,'user_id');
                    $user_arr = User::find()->where(['id' => $user_ids])->select(['avatar_url','nickname','id'])->indexBy('id')->asArray()->all();
                    foreach ($list as &$item){
                        $row['user_id'] = $item['user_id'];
                        $row['nickname'] = '';
                        $row['pay_money'] = $item['total_pay_money'];
                        $row['pay_num'] = $item['add_orders'];
                        $row['pay_goods_num'] = $item['add_pay_nums'];
                        if(isset($user_arr[$item['user_id']])){
                            $row['nickname'] = $user_arr[$item['user_id']]['nickname'];
                        }
                        $rows[]=$row;
                    }
                    $csv->filename = $filename;
                    $csv->file_dirname = DownloadEnum::getTypeStorageDirMap($type);
                    $csv->export_mode = CsvTool::EXPORT_MODE_ASYNC;
                    $csv->header = ['用户ID', '用户昵称' , '销售额', '销量','支付件数'];
                    $csv->data = $rows;
                    $csv->export();
                    $csv->updateDown($download, $params['mall_id']);
                    return true;
                }
                return true;
            } catch(\Exception $e){
                $download->status = 3;
                $res = $download->save();
                self::setStaticError($e->getMessage());
                return false;
            }
        }
    }

    /**
     * 导出销量统计
     */
    public static function exportSaleStatisticsHandleOld(array $data) {
        $download_id = $data['download_id'] ?? 0;
        $download = Download::findOne(['id' => $download_id]);
        if($download) {
            try {
                $params = json_decode($download->params,true);
                $where['where'][] = ['=', 'mall_id', $params['mall_id']];
                $model = self::find()
                    ->where(['mall_id' => $params['mall_id']])
                    ->select(['date', 'count(user_id) user_num', 'sum(pay_num) pay_num', 'sum(pay_goods_num) pay_goods_num', 'sum(pay_money) pay_money'])
                    ->groupBy('date')
                    ->orderBy('date desc')
                    ->asArray();
                $list = [];
                foreach ($model->each(500) as $l) {
                    $l['date'] = substr($l['date'], 0, 4) . '-' . substr($l['date'], 4, 2) . '-' . substr($l['date'], 6, 2);
                    $l['user_num'] = $l['user_num'] ?? 0;
                    $l['pay_num'] = $l['pay_num'] ?? 0;
                    $l['pay_goods_num'] = $l['pay_goods_num'] ?? 0;
                    $l['pay_money'] = $l['pay_money'] ?? 0;
                    $list[] = $l;
                }
                if (!empty($list)) {
                    $csv = new CsvTool();
                    $filename = $download->name;
                    $type = $download->type;
                    $csv->filename = $filename;
                    $csv->file_dirname = DownloadEnum::getTypeStorageDirMap($type);
                    $csv->export_mode = CsvTool::EXPORT_MODE_ASYNC;
                    $csv->header = ['日期', '付款人数', '付款订单数','付款件数','付款金额'];
                    $csv->data = $list;
                    $csv->export();
                    $csv->updateDown($download, $params['mall_id']);
                }
                return true;
            } catch (\Exception $e) {
                $download->status = 3;
                $res = $download->save();
                self::setStaticError($e->getMessage());
                return $e->getMessage();
            }
        }
    }
    public static function exportSaleStatisticsHandle(array $data) {
        $download_id = $data['download_id'] ?? 0;
        $download = Download::findOne(['id' => $download_id]);
        if($download) {
            try {
                $params = json_decode($download->params,true);
                $mallId = $params['mall_id'];
                $where[] = ['=', 'mall_id', $params['mall_id']];
                $params['where'] = $where;
                $select = 'date,add_pay_people_nums as user_num,add_pay_orders as pay_num,add_pay_nums as pay_goods_num,total_pay_money as pay_money';
                $params = array( 'where' => $where, 'select'=>$select,'order'=>'created_at desc', 'limit' => 10000);
                $list = CensusMallSingleDay::lists($params);
                $rows = [];
                foreach ($list as $l) {
                    $row['date'] = $l['date'];
                    $row['user_num'] = $l['user_num'];
                    $row['pay_num'] = $l['pay_num'];
                    $row['pay_goods_num'] = $l['pay_goods_num'];
                    $row['pay_money'] = $l['pay_money'];
                    $rows[] = $row;
                }
                if (!empty($rows)) {
                    $csv = new CsvTool();
                    $filename = $download->name;
                    $type = $download->type;
                    $csv->filename = $filename;
                    $csv->file_dirname = DownloadEnum::getTypeStorageDirMap($type);
                    $csv->export_mode = CsvTool::EXPORT_MODE_ASYNC;
                    $csv->header = ['日期', '付款人数', '付款订单数','付款件数','付款金额'];
                    $csv->data = $rows;
                    $csv->export();
                    $csv->updateDown($download, $mallId);
                }
                return true;
            } catch (\Exception $e) {
                $download->status = 3;
                $res = $download->save();
                self::setStaticError($e->getMessage());
                return $e->getMessage();
            }
        }
    }


    /**
     * 重新生成缓存
     */
    public static function resetCache($mall_id) {
        try {
            $orderStatistics = self::createCacheData($mall_id);
            self::setUserStatisticsCache($mall_id, $orderStatistics);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     *
     * @Author: ltm
     * @DateTime: 2022/3/16 0016 14:17
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param $data
     * @return string
     */
    public static function staticsUserSource($mall_id){
        try {
            $where[] = ['=','mall_id',$mall_id];
            $where[] = ['=','status',StatusEnum::ENABLED];
            $select = 'source,count(*) as source_num';
            $group = "source";
            $params = ['where' => $where,'select' =>$select,'group' => $group];
            $source = User::lists($params,true);
            return json_encode(array_column($source,"source_num","source"),true);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 购买力分析
     * @Author: ltm
     * @DateTime: 2022/3/16 0016 15:41
     * @Copyright: copyright (c) 2021 广东七件事集团
     * @param $data
     * @return string
     */
    public static function staticsUserBuy($mall_id){
        try {
            $num = 6;
            $number = [0,100,1000,10000,100000,1000000,10000000];
            $user_buy = [];
            for ($i=0; $i<$num;$i++){
                $buy_item = [];
                $where = [];
                $where[] = ['=','mall_id',$mall_id];
                $where[] = ['=','status',StatusEnum::ENABLED];
                if($i==5){
                    $where[] = ['>','pay_money',$number[($i+1)]];
                    $buy_item['name'] = $number[$i]."+";
                }else{
                    $where[] = ['between','pay_money',$number[$i],$number[($i+1)]];
                    $buy_item['name'] = $number[$i]."-".$number[($i+1)];
                }
                $select = 'count(*) as pay_money_num';
                $buy_count = UserStatisticsTotal::getOne($where,true,'','',$select);
                $buy_item['num'] = $buy_count['pay_money_num']??0;
                $user_buy[] = $buy_item;
            }

            return json_encode($user_buy,true);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }
}