lachang/app/api/model/GoodsVisitStatistics.php

<?php

namespace app\api\model;

use app\common\model\GoodsVisitStatistics as GoodsVisitStatisticsModel;
use think\facade\Db;

/**
 * 商品浏览数据每日统计模型
 * @package app\api\model
 */
class GoodsVisitStatistics extends GoodsVisitStatisticsModel
{

    /**
     * 批量写入每日/每周/每月数据
     * 发布上线时，只需执行一次
     */
    public static function addInitVisit()
    {
        $begin_date = '20211001'; // 生成初始数据开始时间，建议时间为：小程序上线时间
        $end_date = date('Ymd', strtotime('-1 days')); // 生成初始数据结束时间，建议时间为：昨天

        // 每日
        $date_arr = period_date(strtotime($begin_date), strtotime($end_date), 'Ymd');
        foreach ($date_arr as $date) {
            self::addData(1, $date);
        }

        // 每周
        $date_arr = period_week(strtotime('20211001'), strtotime($end_date), true);
        foreach ($date_arr as $date) {
            self::addData(2, $date);
        }

        // 每月
        $date_arr = period_month(strtotime('20211001'), strtotime($end_date), true);
        foreach ($date_arr as $date) {
            $date = month_first_last($date);
            self::addData(3, $date);
        }
    }

    public static function addData($ftype, $date)
    {
        if ($ftype == 1) {
            $startTime = strtotime("{$date} 00:00:00");
            $endTime = strtotime("{$date} 23:59:59");
            $refDate = date('Y-m-d', strtotime($date));

            // 商品详情
            $pvUv = self::getPvUv(4, $startTime, $endTime);

            $cartUsers = self::getCartUsers($startTime, $endTime);
            $favUsers = self::getFavUsers($startTime, $endTime);
            $shareUsers = self::getShareUsers($startTime, $endTime);

            // 支付订单
            $payData = self::getPayData($startTime, $endTime);
            self::formatData($ftype, $refDate, $startTime, $endTime, $payData, $pvUv, $cartUsers, $favUsers, $shareUsers);
        } elseif ($ftype == 2) {
            $startTime = strtotime("{$date['s']} 00:00:00");
            $endTime = strtotime("{$date['e']} 23:59:59");
            $refDate = "{$date['s']}--{$date['e']}";

            $pvUv = self::getPvUv(4, $startTime, $endTime);
            $cartUsers = self::getCartUsers($startTime, $endTime);
            $favUsers = self::getFavUsers($startTime, $endTime);
            $shareUsers = self::getShareUsers($startTime, $endTime);

            // 支付订单
            $payData = self::getPayData($startTime, $endTime);
            self::formatData($ftype, $refDate, $startTime, $endTime, $payData, $pvUv, $cartUsers, $favUsers, $shareUsers);
        } elseif ($ftype == 3) {
            $startTime = strtotime("{$date['s']} 00:00:00");
            $endTime = strtotime("{$date['e']} 23:59:59");
            $refDate = "{$date['s']}--{$date['e']}";

            $pvUv = self::getPvUv(4, $startTime, $endTime);
            $cartUsers = self::getCartUsers($startTime, $endTime);
            $favUsers = self::getFavUsers($startTime, $endTime);
            $shareUsers = self::getShareUsers($startTime, $endTime);

            // 支付订单
            $payData = self::getPayData($startTime, $endTime);
            self::formatData($ftype, $refDate, $startTime, $endTime, $payData, $pvUv, $cartUsers, $favUsers, $shareUsers);
        }
    }

    /**
     * @param $date
     * @param $payData
     * @param array $pvUv
     * @return mixed
     */
    private static function formatData($ftype, $refDate, $startTime, $endTime, $payData, $pvUv = [], $cartUsers = [], $favUsers = [], $shareUsers = [])
    {
        $idArr = array_unique(array_merge(array_column($pvUv, 'visit_type_id'), array_keys($cartUsers), array_keys($favUsers), array_keys($shareUsers), array_keys($payData)));

        if (empty($idArr)) {
            return;
        }
        foreach ($idArr as $id) {
            $pv = 0;
            $uv = 0;
            $stayTime = 0;
            $cartUsersV = 0;
            $favUsersV = 0;
            $shareUsersV = 0;
            $payUsers = 0;
            foreach ($pvUv as $pvUvV) {
                if (isset($pvUvV['visit_type_id']) && $pvUvV['visit_type_id'] == $id) {
                    $pv = $pvUvV['pv'];
                    $uv = $pvUvV['uv'];
                    $stayTime = $pvUvV['stay_time'];
                }
            }
            if (isset($cartUsers[$id])) {
                $cartUsersV = $cartUsers[$id]['cart_users'];
            }
            if (isset($favUsers[$id])) {
                $favUsersV = $favUsers[$id]['fav_users'];
            }
            if (isset($shareUsers[$id])) {
                $shareUsersV = $shareUsers[$id]['share_users'];
            }

            if (isset($payData[$id])) {
                $payUsers = $payData[$id]['pay_users'];
            }
            $insert = [
                'goods_id' => $id,
                'ftype' => $ftype,
                'ref_date' => $refDate,
                'start_time' => $startTime,
                'end_time' => $endTime,
                'pv' => (int)($pv ?? 0),
                'uv' => (int)($uv ?? 0),
                'stay_time' => $stayTime ?? 0,
                'cart_users' => $cartUsersV ?? 0,
                'fav_users' => $favUsersV ?? 0,
                'share_users' => $shareUsersV ?? 0,
                'pay_users' => $payUsers ?? 0,
            ];
            $info = self::where('goods_id', $id)->where('ref_date', $refDate)->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,sum(stay_time) as stay_time,visit_type_id')
            ->group('visit_type_id')
            ->select()->toArray();
        return $data;
    }

    /**
     * 获取某天的加购用户数
     *
     * @param $startTime
     * @param $endTime
     * @return array
     */
    private static function getCartUsers($startTime, $endTime)
    {
        return Db::name('cart')->field('user_id')
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<', $endTime)
            ->where('is_delete', '=', '0')
            ->group('goods_id')
            ->column('count(distinct(user_id)) as cart_users,goods_id', 'goods_id');
    }

    /**
     * 获取某天的收藏人数
     *
     * @param $startTime
     * @param $endTime
     * @return array
     */
    private static function getFavUsers($startTime, $endTime)
    {
        return Db::name('user_favorites')->field('user_id')
            ->where('target_type', 1)
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<', $endTime)
            ->group('target_id')
            ->column('count(distinct(user_id)) as fav_users,target_id', 'target_id');
    }


    /**
     * 获取某天的分享人数
     *
     * @param $startTime
     * @param $endTime
     * @return array
     */
    private static function getShareUsers($startTime, $endTime)
    {
        return Db::name('user_share')->field('user_id')
            ->where('target_type', 1)
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<', $endTime)
            ->group('target_id')
            ->column('count(distinct(user_id)) as share_users,target_id', 'target_id');
    }

    private static function getPayData($startTime, $endTime)
    {
        $data = Db::name('order')->alias('order')
            ->whereBetween('order.pay_time', [$startTime, $endTime])
            ->where('order.is_delete', '=', 0)
            ->leftJoin('order_goods og', 'og.order_id=order.order_id')
            ->group('og.goods_id')
            ->column('count(distinct(og.user_id)) as pay_users,og.goods_id', 'og.goods_id');

        return $data;
    }
}