qimall/common/models/census/CensusMallSingleHour.php

<?php

namespace common\models\census;


use common\enums\RedisKeyEnum;
use common\enums\StatusEnum;
use common\models\base\BaseActiveRecord;
use Yii;

/**
 * This is the model class for table "qimall_census_mall_single_hour".
 *
 * @property int $id ID
 * @property int $mall_id 商城ID
 * @property string $hour 小时
 * @property string $date 日期
 * @property float $total_pay_money 交易额
 * @property int|null $status 状态[-1:删除;0:禁用;1启用;]
 * @property int $created_at 创建时间
 * @property int $updated_at 上次更新数据时间
 * @property int $add_pay_orders
 * @property int $add_pay_nums
 * @property int $add_pay_people_nums
 */
class CensusMallSingleHour extends BaseActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return '{{%census_mall_single_hour}}';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['mall_id', 'status', 'created_at', 'updated_at','add_pay_orders','add_pay_nums','add_pay_people_nums'], 'integer'],
            [['total_pay_money'], 'number'],
            [['hour'], 'string', 'max' => 5],
            [['date'], 'string', 'max' => 20],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'mall_id' => '商城ID',
            'hour' => '小时',
            'date' => '日期',
            'total_pay_money' => '交易额',
            'status' => '状态[-1:删除;0:禁用;1启用;]',
            'created_at' => '创建时间',
            'updated_at' => '上次更新数据时间',
        ];
    }
    public static function setData($params)
    {
        try {
            if (empty($params['mall_id'])&&empty($params['hour'])&&empty($params['date'])&&empty($params['pay_money'])) {
                return false;
            }
            $model = self::findOne(['mall_id' => $params['mall_id'],'date' => $params['date'],
                'hour'=>$params['hour'], 'status' => [StatusEnum::ENABLED, StatusEnum::DISABLED]]);
            if (empty($model)) {
                $model = new self();
                $model->mall_id = $params['mall_id'];
                $model->hour = $params['hour'];
                $model->date = $params['date'];
                $model->loadDefaultValues();
            }
            if (!empty($params['pay_money'])) $model->total_pay_money += $params['pay_money'];
            if (!empty($params['add_pay_orders'])) $model->add_pay_orders += $params['add_pay_orders'];
            if (!empty($params['add_pay_nums'])) $model->add_pay_nums += $params['add_pay_nums'];
            if (!empty($params['add_pay_people_nums'])) $model->add_pay_people_nums += $params['add_pay_people_nums'];
            if (!$model->save()) throw new \Exception($model->getErrorMessage());
            return $model;
        } catch (\Exception $e) {
            self::$static_error = $e->getMessage();
            return false;
        }
    }
    public static function addData($params)
    {
        try {
            if (empty($params['mall_id'])&&empty($params['hour'])&&empty($params['date'])) {
                return false;
            }
            $model = self::findOne(['mall_id' => $params['mall_id'],'date' => $params['date'],
                'hour'=>$params['hour'], 'status' => [StatusEnum::ENABLED, StatusEnum::DISABLED]]);
            if (empty($model)) {
                $model = new self();
                $model->mall_id = $params['mall_id'];
                $model->hour = $params['hour'];
                $model->date = $params['date'];
                $model->loadDefaultValues();
            }
            if (!empty($params['total_pay_money'])) $model->total_pay_money = $params['total_pay_money'];
            if (!empty($params['add_pay_orders'])) $model->add_pay_orders = $params['add_pay_orders'];
            if (!empty($params['add_pay_nums'])) $model->add_pay_nums = $params['add_pay_nums'];
            if (!empty($params['add_pay_people_nums'])) $model->add_pay_people_nums = $params['add_pay_people_nums'];
            if (!$model->save()) throw new \Exception($model->getErrorMessage());
            return $model;
        } catch (\Exception $e) {
            self::$static_error = $e->getMessage();
            return false;
        }
    }


    public function fixPayOrdersNumByHour($mall_id, $date, $start_time, $end_time)
    {
        $sql = "
SELECT
FROM_UNIXTIME(od.created_at,'%H') as hour,
sum(od.num) as counts,od.mall_id FROM
            qimall_order_detail as od LEFT JOIN qimall_order as o on od.order_id = o.id where od.mall_id = {$mall_id} and  od.mch_id = 0 and od.store_id=0 and o.pay_status =1
         and od.created_at between {$start_time} and {$end_time}
        GROUP BY hour
        ;
        ";
        $list = \Yii::$app->db->createCommand($sql)->queryAll();
        $rows = [];
        foreach ($list as $item) {
            $rows[$item['hour']] = $item;
        }
        for ($i = 0; $i < 24; $i++) {
            $temp = $i;
            if ($i <= 9) $temp = '0' . $i;
            $params = [
                'mall_id' => $mall_id,
                'date' => $date,
                'hour' => (string)$temp,
                'add_pay_nums' => 0,
            ];

            if (isset($rows[$temp])) {
                $params['add_pay_nums'] = $rows[$temp]['counts'];
            }
            $res = CensusMallSingleHour::addData($params);
        }
        var_dump('fixPayOrdersNumByHour执行结束');


    }

    public function fixAddPayOrdersByHour($mall_id, $date, $start_time, $end_time)
    {
        $sql = "
        SELECT
FROM_UNIXTIME(created_at,'%H') as hour,
count(*) as counts,sum(pay_money) as total_pay_money,mall_id FROM
            qimall_order where mall_id = {$mall_id} and  mch_id = 0 and store_id=0 and pay_status =1
         and created_at between {$start_time} and {$end_time}
        GROUP BY hour 
        ";
        $list = \Yii::$app->db->createCommand($sql)->queryAll();
        $rows = [];
        foreach ($list as $item) {
            $rows[$item['hour']] = $item;
        }
        for ($i = 0; $i < 24; $i++) {
            $temp = $i;
            if ($i <= 9) $temp = '0' . $i;
            $params = [
                'mall_id' => $mall_id,
                'date' => $date,
                'hour' => (string)$temp,
                'add_pay_orders' => 0,
                'total_pay_money' => 0,
            ];

            if (isset($rows[$temp])) {
                $params['add_pay_orders'] = $rows[$temp]['counts'];
                $params['total_pay_money'] = $rows[$temp]['total_pay_money'];
            }
            $res = CensusMallSingleHour::addData($params);
        }
        var_dump('fixAddPayOrdersByHour执行结束');

    }

    public function fixAddPayPeopleNumsByHour($mall_id, $date, $start_time, $end_time)
    {
        $sql = "
        SELECT t.`hour`,t.mall_id,count(t.user_id) as counts	 FROM (
SELECT
FROM_UNIXTIME(created_at,'%H') as hour,
user_id,mall_id FROM
            qimall_order where mall_id = {$mall_id} and  mch_id = 0 and store_id=0 and pay_status =1
         and created_at between {$start_time} and {$end_time}
        GROUP BY hour,user_id
				)t GROUP BY t.`hour`

        ";
        $list = \Yii::$app->db->createCommand($sql)->queryAll();
        $rows = [];
        foreach ($list as $item) {
            $rows[$item['hour']] = $item;
        }
        for ($i = 0; $i < 24; $i++) {
            $temp = $i;
            if ($i <= 9) $temp = '0' . $i;
            $params = [
                'mall_id' => $mall_id,
                'date' => $date,
                'hour' => (string)$temp,
                'add_pay_people_nums' => 0,
            ];

            if (isset($rows[$temp])) {
                $params['add_pay_people_nums'] = $rows[$temp]['counts'];
            }
            $res = CensusMallSingleHour::addData($params);
        }


        $sql = "
            SELECT
            FROM_UNIXTIME(created_at,'%H') as hour,
            user_id,mall_id FROM
                        qimall_order where mall_id = {$mall_id} and  mch_id = 0 and store_id=0 and pay_status =1
                     and created_at between {$start_time} and {$end_time}
                    GROUP BY hour,user_id
        ";
        $list = \Yii::$app->db->createCommand($sql)->queryAll();
        $date = date('Y-m-d', $start_time);
        foreach ($list as $val){
            $hour = $date.' '.$val['hour'].':00:00';
            $key = RedisKeyEnum::CENSUS_MALL_SINGLE_HOUR .':'. $mall_id.':' .strtotime($hour);
            $redis_key = RedisKeyEnum::suffix($key, '', true);
            $redis = \Yii::$app->redis;
            $redis->setbit($redis_key, $val['user_id'], 1);
        }
        var_dump('fixAddPayPeopleNumsByHour执行结束');
    }


}