lachang/app/api/model/GoodsSaleStatistics.php

<?php

namespace app\api\model;

use app\common\model\GoodsSaleStatistics as GoodsSaleStatisticsModel;
use think\facade\Db;

/**
 * 商品销售数据每日统计模型
 * @package app\api\model
 */
class GoodsSaleStatistics extends GoodsSaleStatisticsModel
{

    /**
     * 批量写入每日/每周/每月数据
     * 发布上线时，只需执行一次
     */
    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));
        } elseif ($ftype == 2) {
            $startTime = strtotime("{$date['s']} 00:00:00");
            $endTime = strtotime("{$date['e']} 23:59:59");
            $refDate = "{$date['s']}--{$date['e']}";
        } elseif ($ftype == 3) {
            $startTime = strtotime("{$date['s']} 00:00:00");
            $endTime = strtotime("{$date['e']} 23:59:59");
            $refDate = "{$date['s']}--{$date['e']}";
        } else {
            return ;
        }




        $payData = self::getPayData($startTime, $endTime);
        $orderData = self::getOrderData($startTime, $endTime);
        $firstOrder = self::getFirstOrder($startTime, $endTime);
        $reorder = self::getReorder($startTime, $endTime);
        self::formatData($ftype, $refDate, $startTime, $endTime, $payData,$orderData, $firstOrder, $reorder);
    }

    /**
     * @param $date
     * @param $payData
     * @param array $pvUv
     * @return mixed
     */
    private static function formatData($ftype, $refDate, $startTime, $endTime, $payData, $orderData = [], $firstOrder = [], $reorder = [])
    {
//        $idArr = array_unique(array_merge(array_keys($payData), array_keys($orderData), array_keys($firstOrder), array_keys($reorder)));
        $idArr = Db::name('goods')->where('is_delete', '=', 0)->column('goods_id');

        if (empty($idArr)) {
            return;
        }
        foreach ($idArr as $id) {
            $payCount = 0;
            $payGoodsCount = 0;
            $payUserCount = 0;
            $orderAmount = 0;
            $orderCount = 0;
            $orderUserCount = 0;
            $firstOrderUsers = 0;
            $recorderUsers = 0;
            if (isset($payData[$id])) {
                $payCount = $payData[$id]['pay_count'];
                $payGoodsCount = $payData[$id]['pay_goods_count'];
                $payUserCount = $payData[$id]['pay_user_count'];
                $orderAmount = $payData[$id]['order_amount'];
            }
            if (isset($orderData[$id])) {
                $orderCount = $orderData[$id]['order_count'];
                $orderUserCount = $orderData[$id]['order_user_count'];
            }
            if (isset($firstOrder[$id])) {
                $firstOrderUsers = $firstOrder[$id]['first_order_users'];
            }
            if (isset($reorder[$id])) {
                $recorderUsers = $reorder[$id]['recorder_users'];
            }
            $insert = [
                'goods_id' => $id,
                'ftype' => $ftype,
                'ref_date' => $refDate,
                'start_time' => $startTime,
                'end_time' => $endTime,
                'pay_count' => $payCount ?? 0,
                'pay_goods_count' => $payGoodsCount ?? 0,
                'pay_user_count' => $payUserCount ?? 0,
                'order_amount' => $orderAmount ?? 0,
                'order_count' => $orderCount ?? 0,
                'order_user_count' => $orderUserCount ?? 0,
                'first_order_users' => $firstOrderUsers ?? 0,
                'recorder_users' => $recorderUsers ?? 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 getPayData($startTime, $endTime)
    {
        $data = Db::name('order')->alias('order')
            ->whereBetween('order.pay_time', [$startTime, $endTime])
            ->where('order.is_delete', '=', 0)
            ->where('og.goods_type', '<>', 20) // // 排除赠品
            ->leftJoin('order_goods og', 'og.order_id=order.order_id')
            ->group('og.goods_id')
            ->column('og.goods_id,count(distinct(og.order_id)) as pay_count,sum(og.total_num) as pay_goods_count,count(distinct(og.user_id)) as pay_user_count,sum(og.total_pay_price+og.rice_card_money) as order_amount','og.goods_id');

        return $data;
    }

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

        return $data;
    }

    //⑧首次下单人数：统计首次下单为该商品的用户人数
    private static function getFirstOrder($startTime, $endTime)
    {
        $data = Db::query("select og.goods_id,count(o.user_id) as first_order_users 
            from (select user_id,min(pay_time) as mp from `yoshop_order` group by user_id) as ump,`yoshop_order` as o 
            left join yoshop_order_goods as og on og.order_id=o.order_id where ump.user_id=o.user_id and ump.mp=o.pay_time 
           and pay_time >= ? and pay_time < ? and ump.mp > 0 group by og.goods_id order by goods_id;", [$startTime, $endTime]);

        if (empty($data)) {
            return [];
        }
        $res = [];
        foreach ($data as $item) {
            $res[$item['goods_id']] = $item;
        }
        return $res;
    }

    //⑨复购人数：统计用户购买该商品次数≥2的用户人数
    private static function getReorder($startTime, $endTime)
    {
        $data = Db::query("select og.goods_id,count(o.user_id) as recorder_users,
            sum(og.total_pay_price+og.rice_card_money) as amount,ump.mp 
            from (select user_id,min(pay_time) as mp from `yoshop_order` group by user_id) as ump,`yoshop_order` as o 
            left join yoshop_order_goods as og on og.order_id=o.order_id where ump.user_id=o.user_id and ump.mp=o.pay_time 
           and pay_time >= ? and pay_time < ? and ump.mp > 0 group by og.goods_id having count(o.user_id) > 1 order by goods_id;", [$startTime, $endTime]);

        if (empty($data)) {
            return [];
        }
        $res = [];
        foreach ($data as $item) {
            $res[$item['goods_id']] = $item;
        }
        return $res;
    }

}