lachang/app/api/model/ActEffectStatistics.php

<?php

namespace app\api\model;

use app\common\enum\order\OrderSource as OrderSourceEnum;
use app\common\model\ActEffectStatistics as ActEffectStatisticsModel;
use think\facade\Db;

/**
 * @package app\api\model
 */
class ActEffectStatistics extends ActEffectStatisticsModel
{
    /**
     * @return mixed
     */
    public static function addInitData()
    {
        $begin_date = '2021-10-01'; // 生成初始数据开始时间，建议时间为：小程序上线时间
        $end_date = date('Y-m-d', strtotime('-1 days')); // 生成初始数据结束时间，建议时间为：昨天
        $date_arr = period_date(strtotime($begin_date), strtotime($end_date), 'Y-m-d');
        foreach ($date_arr as $date) {
            self::addData($date);
            echo $date . PHP_EOL;
        }
        return true;
    }

    public static function addData($date)
    {
        $startTime = strtotime("{$date} 00:00:00");
        $endTime = strtotime("{$date} 23:59:59");

        // 拼团活动
        $ptPvUv = self::getPvUv(1, $startTime, $endTime);
        // 新用户
        $ptUvNew = self::getUvNew(User::ACT_SOURCE_GROUP_BUY, $startTime, $endTime);

        //拼团裂变活动 //可能初使
        $ptlbPvUv = self::getPvUv(10, $startTime, $endTime);
        // 新用户
        $ptlbUvNew = self::getUvNew(User::ACT_SOURCE_GROUP_BUY_LB, $startTime, $endTime);

        // 支付订单
        $ptPayData = self::getPayData(1, $startTime, $endTime);
        self::formatData(self::ACT_TYPE_GROUP_BUY, $date, $ptPayData, $ptPvUv, $ptUvNew);

        // 砍价活动
        $kjPvUv = self::getPvUv(2, $startTime, $endTime);
        // 新用户
        $kjUvNew = self::getUvNew(User::ACT_SOURCE_KJ, $startTime, $endTime);
        // 支付订单
        $kjPayData = self::getPayData(2, $startTime, $endTime);
        self::formatData(self::ACT_TYPE_KJ, $date, $kjPayData, $kjPvUv, $kjUvNew);

        // N件X折活动
        // 支付订单
        $discountPayData = self::getPayData(3, $startTime, $endTime);
        self::formatData(self::ACT_TYPE_DISCOUNT, $date, $discountPayData);

        // 满件送活动
        // 支付订单
        $mjSendPayData = self::getPayData(4, $startTime, $endTime);
        self::formatData(self::ACT_TYPE_MJ_SEND, $date, $mjSendPayData);

        // 满就送活动
        // 支付订单
        $fullSendPayData = self::getPayData(5, $startTime, $endTime);
        self::formatData(self::ACT_TYPE_FULL_SEND, $date, $fullSendPayData);


        //秒杀活动
        $miaoshaPayData = self::getPayData(9,$startTime, $endTime);
        // var_dump($miaoshaPayData);
        // die();
        self::formatData(self::ACT_TYPE_MIAOSHA, $date, $miaoshaPayData);

        //拼团裂变活动
        $groupbuylbData = self::getPayData(self::ACT_TYPE_GROUP_BUY_LB,$startTime,$endTime);
        self::formatData(self::ACT_TYPE_GROUP_BUY_LB, $date, $groupbuylbData,$ptlbPvUv,$ptUvNew);
    }

    /**
     * @param $type
     * @param $date
     * @param $payData
     * @param array $pvUv
     * @param array $uvNew
     * @return mixed
     */
    private static function formatData($type, $date, $payData, $pvUv = [], $uvNew = [])
    {
        $actIdArr = array_unique(array_merge(array_column($pvUv, 'act_id'), array_keys($uvNew), array_keys($payData)));

        foreach ($actIdArr as $actId) {
            $pv = 0;
            $uv = 0;
            $uvNewV = 0;
            $payOrderCount = 0;
            $payUserCount = 0;
            $payAmount = 0;
            foreach ($pvUv as $pvUvV) {
                if ($pvUvV['act_id'] == $actId) {
                    $pv = $pvUvV['pv'];
                    $uv = $pvUvV['uv'];
                }
            }
            if (isset($uvNew[$actId])) {
                $uvNewV = $uvNew[$actId]['uv_new'];
            }
            if (isset($payData[$actId])) {
                $payOrderCount = $payData[$actId]['pay_order_count'];
                $payUserCount = $payData[$actId]['pay_user_count'];
                $payAmount = $payData[$actId]['pay_amount'];
            }
            $insert = [
                'act_id' => $actId,
                'act_type' => $type,
                'ref_date' => date('Ymd', strtotime($date)),
                'pv' => (int)$pv,
                'uv' => (int)$uv,
                'uv_new' => $uvNewV,
                'pay_order_count' => $payOrderCount,
                'pay_user_count' => $payUserCount,
                'pay_amount' => $payAmount,
            ];
            $info = self::where('act_id', $actId)->where('act_type', $type)->where('ref_date', date('Ymd', strtotime($date)))->find();
            if (empty($info)) {
                (new self)->save($insert);
            } else {
                $info->where('id', $info['id'])->update($insert);
            }
        }
    }

    private static function getPvUv($type, $startTime, $endTime)
    {
        // 访问量
        $data = Db::name('visit_record')->where('visit_type', $type)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->field('sum(IF(v_type=0, 1, 0)) as pv,sum(IF(v_type=1, 1, 0)) as uv,visit_type_id as act_id')
            ->group('act_id')
            ->select()->toArray();
        return $data;
    }

    private static function getUvNew($type, $startTime, $endTime)
    {
        $data = User::where('act_source', $type)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->group('act_source_id')
            ->column('count(act_source_id) as uv_new,act_source_id', 'act_source_id');
        return $data;
    }

    private static function getPayData($type, $startTime, $endTime)
    {
        switch ($type) {
            case self::ACT_TYPE_GROUP_BUY_LB:
              $data = Db::name('order')->alias('order')->where('order.order_source', OrderSourceEnum::GROUPBUYLB)
                    ->leftJoin('group_buy_lb_activity_help help', 'help.id=order.order_source_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('help.group_buy_lb_activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(order.pay_price + order.rice_card_money) as pay_amount,count(order.order_id) as pay_order_count,help.group_buy_lb_activity_id', 'help.group_buy_lb_activity_id');
                break;
            case self::ACT_TYPE_GROUP_BUY:
                $data = Db::name('order')->alias('order')->where('order.order_source', OrderSourceEnum::GROUPBUY)
                    ->leftJoin('group_buy_activity_help help', 'help.id=order.order_source_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('help.group_buy_activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(order.pay_price + order.rice_card_money) as pay_amount,count(order.order_id) as pay_order_count,help.group_buy_activity_id', 'help.group_buy_activity_id');
                break;
            case self::ACT_TYPE_MIAOSHA:
                $data = Db::name('order')->alias('order')->where('order.order_source', OrderSourceEnum::MIAOSHA)
                    ->leftJoin('ms_activity_goods goods', 'goods.id=order.order_source_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('goods.ms_activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(order.pay_price + order.rice_card_money) as pay_amount,count(order.order_id) as pay_order_count,goods.ms_activity_id', 'goods.ms_activity_id');
                break;

            case self::ACT_TYPE_KJ:
                $data = Db::name('order')->alias('order')->where('order.order_source', OrderSourceEnum::BARGAIN)
                    ->leftJoin('kj_activity_join join', 'join.id=order.order_source_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('join.activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(order.pay_price + order.rice_card_money) as pay_amount,count(order.order_id) as pay_order_count,join.activity_id', 'join.activity_id');
                break;
            case self::ACT_TYPE_DISCOUNT:
                $data = Db::name('order_goods')->alias('og')->where('og.is_activity_discount', 1)
                    ->leftJoin('order', 'order.order_id=og.order_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('og.activity_discount_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(og.total_pay_price + og.rice_card_money) as pay_amount,count(distinct(order.order_id)) as pay_order_count,og.activity_discount_id', 'og.activity_discount_id');
                break;
            case self::ACT_TYPE_MJ_SEND:
                $data = Db::name('order_goods')->alias('og')->where('og.is_mj_send', 1)
                    ->leftJoin('order', 'order.order_id=og.order_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('og.mj_send_activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(og.total_pay_price + og.rice_card_money) as pay_amount,count(distinct(order.order_id)) as pay_order_count,og.mj_send_activity_id', 'og.mj_send_activity_id');
                break;
            case self::ACT_TYPE_FULL_SEND:
                $data = Db::name('order_goods')->alias('og')->where('og.is_full_send', 1)
                    ->leftJoin('order', 'order.order_id=og.order_id')
                    ->whereBetween('order.pay_time', [$startTime, $endTime])
                    ->where('order.is_delete', '=', 0)
                    ->group('og.full_send_activity_id')
                    ->column('count(distinct(order.user_id)) as pay_user_count,sum(og.total_pay_price + og.rice_card_money) as pay_amount,count(distinct(order.order_id)) as pay_order_count,og.full_send_activity_id', 'og.full_send_activity_id');
                break;

            default:
                $data = [];
                break;
        }

        return $data;
    }

}