qimall/common/models/statistics/GoodsStatistics.php

<?php

namespace common\models\statistics;

use common\models\base\BaseActiveRecord;
use common\enums\RedisKeyEnum;
use common\enums\StatusEnum;
use common\models\census\CensusMallGoods;
use common\models\goods\Goods;
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;

/**
 *
 * @property int $mall_id
 * @property int $goods_id
 * @property int $sales_num
 * @property int $order_num
 * @property int $date
 */
class GoodsStatistics extends BaseActiveRecord
{
    //默认展示10条
    private static $limit = 10;


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

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['mall_id', 'goods_id', 'sales_num', 'order_num', 'date'], 'required'],
            [['mall_id', 'goods_id', 'sales_num', 'order_num', 'date'], 'integer'],
            [['pay_money'], 'number']
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'mall_id' => '商城id',
            'goods_id' => '商品id',
            'pay_money' => '支付金额',
            'sales_num' => '销量',
            'order_num' => '订单量',
            'date' => '日期',
        ];
    }

    public function behaviors() {
        return [];
    }

    /**
     * 读取数据
     * 
     * @param int $mall_id
     * @param string $stor desc:取最大N条 asc:取最小N条
     * @param string $type sales_num:销量排行数据 pay_money:支付金额排行数据  
     * @author hpei
     * @return array
     */
    public static function getData($mall_id, $stor = 'desc', $type = 'sales_num') {
        try {
            $redis = \Yii::$app->redis;
            if ($type == 'sales_num') {
                $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_SALE, 'mall_id_'. $mall_id);
            } else if ($type == 'pay_money') {
                $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_MONEY, 'mall_id_' . $mall_id);
            }
            if ($stor == 'desc') {
                $goodsCache = $redis->zrevrange($cacheKey, 0, self::$limit - 1, 'WITHSCORES');
            } else if ($stor == 'asc') {
                $goodsCache = $redis->zrange($cacheKey, 0, self::$limit - 1, 'WITHSCORES');
            }
            if (empty($goodsCache)) {
                $goodsStatistics = [];
                foreach (self::find() 
                    ->select(["sum(sales_num) as sales_num", "sum(pay_money) as pay_money",'goods_id'])
                    ->where(['mall_id' => $mall_id])
                    ->orderBy($type . ' ' . $stor)->indexBy('goods_id')
                    ->asArray()->batch(500) as $val) {
                    $goodsStatistics = array_merge($goodsStatistics, $val);
                }
                if (count($goodsStatistics) == 1 && isset($goodsStatistics[''])) return [];
                $goodsStatistics = array_column($goodsStatistics, null, 'goods_id');
                $goodsIds = array_column($goodsStatistics, 'goods_id');
                self::setCache($mall_id, $goodsStatistics);
            } else {
                $goodsStatistics = [];
                $goodsIds = [];
                for ($i = 0; $i <= count($goodsCache); $i++) {
                    if (strpos($goodsCache[$i], ':')) {
                        $id = explode(':', $goodsCache[$i])[1];
                        $goodsStatistics[$i]['goods_id'] =  $id;
                        $goodsIds[] = $id;
                    } else {
                        if (isset($goodsStatistics[$i - 1])) $goodsStatistics[$i - 1][$type] = $goodsCache[$i];
                    }
                }
            }
            $param['where'][] = ['in', 'id', $goodsIds];
            $param['where'][] = ['=', 'status', StatusEnum::ENABLED];
            $param['select'] = ['goods_name', 'cover_pic', 'id'];
            $param['index'] = 'id';
            $goodsList = Goods::lists($param);
            if (!empty($goodsList)) {
                $goodsStatistics = array_column(array_slice($goodsStatistics, 0, self::$limit), null, 'goods_id');
                foreach ($goodsStatistics as $goods_id => &$goods) {
                    if (isset($goodsList[$goods_id])) {
                        $goods['goods_name'] = $goodsList[$goods['goods_id']]['goods_name'];
                        $goods['cover_pic'] = $goodsList[$goods['goods_id']]['cover_pic'];
                    } else {
                        $goods['goods_name'] = '';
                        $goods['cover_pic'] = '';
                    }
                }
            }
            return array_values($goodsStatistics);
        } catch (\Exception $e) {
            Yii::error('读取商品统计缓存异常：' . $e->getMessage());
            return [];
        }
            
    }

    /**
     * 保存数据
     * 
     * @param array $data [['goods_id'=>$goods_id,'sales_num'=>$sales_num,'order_num'=>$order_num,'pay_money'=>$pay_money]]
     * @author hpei
     * @return bool
     */
    public static function saveData($mall_id, $data) {
        try {
            $date = date('Ymd');
            if (!$data) {
                return;
            }
            $goodsData = array_column($data, null, 'goods_id');
            $goodsStatistics = self::find()->where(['mall_id' => $mall_id, 'date' => $date])
                ->andWhere(['in', 'goods_id', array_keys($goodsData)])->indexBy('goods_id')->all();
            $installData = [];
            foreach ($goodsData as $goods_id => $goods) {
                if (isset($goodsStatistics[$goods_id])) {
                    $model = $goodsStatistics[$goods_id];
                    $model->sales_num += $goods['sales_num'] ?? 0;
                    $model->order_num += $goods['order_num'] ?? 0;
                    $model->pay_money += $goods['pay_money'] ?? 0;
                    if (!$model->save()) {
                        self::$static_error = $model->getErrorMessage();
                        return false;
                    }
                } else {
                    if (!$goods_id) continue;
                    $sales_num = $goods['sales_num'] ?? 0;
                    $order_num = $goods['order_num'] ?? 0;
                    $pay_money = $goods['pay_money'] ?? 0;
                    $installData[] = [
                        'mall_id' => $mall_id, 'goods_id' => $goods_id, 'pay_money' => $pay_money, 'sales_num' => $sales_num, 'order_num' => $order_num, 'date' => $date
                    ];
                }
            }
            if (!empty($installData)) self::batch($installData);
            $config = \Yii::$app->getComponents()['redis'];
            unset($config['class']);
            $redis = new Connection($config);
            $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_SALE, 'mall_id_'. $mall_id);
            //如果没有缓存，查询全表
            $count = $redis->zcard($cacheKey);
            if (!$count) {
                $goodsData = self::find()
                    ->where(['mall_id' => $mall_id])
                    ->select(['sum(sales_num) as sales_num', 'sum(order_num) as order_num', 'sum(pay_money) as pay_money','goods_id'])
                    ->indexBy('goods_id')->asArray()->groupBy('goods_id')->all();
            }
            self::setCache($mall_id, $goodsData);
            return true;
        } catch (\Exception $e) {
            self::$static_error = $e->getMessage() . 'GoodsStatistics.saveData $data = '.json_encode($data, JSON_UNESCAPED_UNICODE);
            return false;
        }
    }

    /**
     * 设置stored set
     * 
     * sales_num,order_num 
     * @param array $data ['goods_id' => [sales_num=>$sales_num,order_num=>$order_num,pay_money=>$pay_money]]
     * @author hpei
     * @return void
     */
    public static function setCache($mall_id, $data) {
        $config = \Yii::$app->getComponents()['redis'];
        unset($config['class']);
        $redis = new Connection($config);
        try {
            $saleCacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_SALE, 'mall_id_'. $mall_id);
            $moneyCacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_MONEY, 'mall_id_' . $mall_id);
            foreach ($data as $goods_id => $info) {
                if (isset($info['sales_num'])) {
                    $saleScore = $redis->zscore($saleCacheKey, 'goods_id:' . $goods_id);
                    if ($saleScore) {
                        $redis->zincrby($saleCacheKey, $info['sales_num'], 'goods_id:' . $goods_id);
                    } else {
                        $redis->zadd($saleCacheKey, $info['sales_num'], 'goods_id:' . $goods_id);
                    }
                }
                if (isset($info['pay_money'])) {
                    $moneyScore = $redis->zscore($moneyCacheKey, 'goods_id:' . $goods_id);
                    if ($moneyScore) {
                        $redis->zincrby($moneyCacheKey, $info['pay_money'], 'goods_id:' . $goods_id);
                    } else {
                        $redis->zadd($moneyCacheKey, $info['pay_money'], 'goods_id:' . $goods_id);
                    }
                }
            }
            $redis->close();
        } catch (\Exception $e) {
            Yii::error('设置商品统计缓存异常:'.$e->getMessage());
            $redis->close();
        }
           
    }


    /**
     * 批量插入
     * @author hpei
     * @return void
     */
    public static function batch($data) {
        $field = ['mall_id', 'goods_id', 'pay_money', 'sales_num', 'order_num', 'date'];
        \Yii::$app->db->createCommand()->batchInsert(self::tableName(), $field, $data)->execute();
    }

    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();
                $mall_id = $params['mall_id'];
                $cacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_SALE, 'mall_id_'. $mall_id);
                $config = \Yii::$app->getComponents()['redis'];
                unset($config['class']);
                $redis = new Connection($config);
                $goodsCache = $redis->zrevrange($cacheKey, 0, 99, 'WITHSCORES');
                if (!empty($goodsCache)) {
                    $goodsRank = [];
                    for ($i = 0; $i <= count($goodsCache); $i++) {
                        if (strpos($goodsCache[$i], ':')) {
                            $goods_id = explode(':', $goodsCache[$i])[1];
                            $goods_ids[] = $goods_id;
                            $money = $redis->zscore(RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_MONEY, 'mall_id_' . $mall_id), 'goods_id:' . $goods_id);
                            $goodsRank[$i]['goods_id'] = $goods_id;
                            $goodsRank[$i]['goods_name'] = '';
                            $goodsRank[$i]['pay_money'] = $money;
                        } else {
                            if (isset($goodsRank[$i - 1])) $goodsRank[$i - 1]['sales_num'] = $goodsCache[$i];
                        }

                    }
                    $goodsList = Goods::find()->where(['id' => $goods_ids])->select(['goods_name'])->indexBy('id')->column();
                    foreach ($goodsRank as &$goods) {
                        if (isset($goodsList[$goods['goods_id']])) $goods['goods_name'] = $goodsList[$goods['goods_id']];
                        $goods['pay_money'] = $goods['pay_money'] ?? 0;
                        $goods['sales_num'] = $goods['sales_num'] ?? 0;
                        $goods['goods_name'] = $goodsList[$goods['goods_id']] ?? '';
                    }
                    $csv->filename = $filename;
                    $csv->file_dirname = DownloadEnum::getTypeStorageDirMap($type);
                    $csv->export_mode = CsvTool::EXPORT_MODE_ASYNC;
                    $csv->header = ['商品ID', '商品名称', '支付金额', '销量'];
                    $csv->data = array_values($goodsRank);
                    $csv->export();
                    $csv->updateDown($download, $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_sales_volume desc';
                $list = CensusMallGoods::lists($params);
                if(!empty($list)){
                    $rows = [];
                    $goods_ids = array_column($list,'goods_id');
                    $goods_list = Goods::lists(['where'=>[['mall_id'=>$mall_id],['id'=>$goods_ids]],'index'=>'id']);
                    foreach ($list as &$item){
                        $row['goods_id'] = $item['goods_id'];
                        $row['goods_name'] = '';
                        $row['pay_money'] = $item['total_pay_money'];
                        $row['sales_num'] = $item['total_sales_volume'];
                        if(isset($goods_list[$item['goods_id']])){
                            $row['goods_name'] = $goods_list[$item['goods_id']]['goods_name'];
                        }
                        $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, $mall_id);
                    return true;
                }
            } catch(\Exception $e){
                $download->status = 3;
                $res = $download->save();
                self::setStaticError($e->getMessage());
                return false;
            }
        }
    }

    /**
     * 重新生成缓存
     */
    public static function resetCache($mall_id) {
        try {
            $goodsStatistics = [];
            foreach (self::find() 
                ->select(["sum(sales_num) as sales_num", "sum(pay_money) as pay_money","goods_id"])
                ->where(['mall_id' => $mall_id])->groupBy('goods_id')
                ->asArray()->batch(500) as $val) {
                $goodsStatistics = array_merge($goodsStatistics, $val);
            }
            $goodsStatistics = array_column($goodsStatistics, null, 'goods_id');
            $config = \Yii::$app->getComponents()['redis'];
            unset($config['class']);
            $redis = new Connection($config);
            $moneyCacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_MONEY, 'mall_id_' . $mall_id);
            $saleCacheKey = RedisKeyEnum::suffix(RedisKeyEnum::GOODS_STATISTICS_SALE, 'mall_id_'. $mall_id);
            $redis->del($moneyCacheKey, $saleCacheKey);
            self::setCache($mall_id, $goodsStatistics);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }           
    }

}