lachang/app/api/model/ChannelSaleStatistics.php

<?php

namespace app\api\model;

use app\common\model\ChannelSaleStatistics as ChannelSaleStatisticsModel;
use think\facade\Db;

/**
 * 渠道销售统计模型
 * @package app\api\model
 */
class ChannelSaleStatistics extends ChannelSaleStatisticsModel
{

    /**
     * 批量写入每日数据
     * 发布上线时，只需执行一次
     */
    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");

        $payData = self::getPayData($startTime, $endTime);
        $singleRefundData = self::getSingleRefundData($startTime, $endTime);
        $multiRefundData = self::getMultiRefundData($startTime, $endTime);

        self::formatData($startTime, $endTime, $payData, $singleRefundData, $multiRefundData);
    }

    private static function formatData($startTime, $endTime, $payData, $singleRefundData = [], $multiRefundData = [])
    {
//        $idArr = array_unique(array_merge(array_keys($payData), array_keys($singleRefundData), array_keys($multiRefundData)));
        $idArr = Db::name('shops')->where('is_delete', '=', 0)->column('shop_id');
        if (empty($idArr)) {
            return;
        }

        $idArr[0] = 0; // 其他渠道
        foreach ($idArr as $id) {
            $payAmount = 0;
            $payCount = 0;
            $payGoodsCount = 0;
            $payUserCount = 0;
            $refundAmount = 0;
            $refundOrderCount = 0;
            $refundGoodsCount = 0;
            if (isset($payData[$id])) {
                $payAmount = $payData[$id]['pay_amount'];
                $payCount = $payData[$id]['pay_count'];
                $payGoodsCount = $payData[$id]['pay_goods_count'];
                $payUserCount = $payData[$id]['pay_user_count'];
            }
            if (isset($singleRefundData[$id])) {
                $refundAmount += $singleRefundData[$id]['refund_amount'];
                $refundOrderCount += $singleRefundData[$id]['refund_order_count'];
                $refundGoodsCount += $singleRefundData[$id]['refund_goods_count'];
            }
            if (isset($multiRefundData[$id])) {
                $refundAmount += $multiRefundData[$id]['refund_amount'];
                $refundOrderCount += $multiRefundData[$id]['refund_order_count'];
                $refundGoodsCount += $multiRefundData[$id]['refund_goods_count'];
            }
            $insert = [
                'start_time' => $startTime,
                'end_time' => $endTime,
                'shop_id' => $id,
                'pay_amount' => $payAmount,
                'pay_count' => $payCount,
                'pay_goods_count' => $payGoodsCount,
                'pay_user_count' => $payUserCount,
                'refund_amount' => $refundAmount,
                'refund_order_count' => $refundOrderCount,
                'refund_goods_count' => $refundGoodsCount,
            ];
            $info = self::where('shop_id', $id)->where('start_time', $startTime)->where('end_time', $endTime)->find();
            if (empty($info)) {
                (new self)->save($insert);
            } else {
                $info->where('id', $info['id'])->update($insert);
            }
        }
    }

    /**
     * 订单信息
     * @param $startTime
     * @param $endTime
     * @return array
     */
    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('order.staff_shop_id')
            ->column('order.staff_shop_id as shop_id,count(distinct(order.order_id)) as pay_count,sum(distinct(order.pay_price+order.rice_card_money)) as pay_amount,
            sum(og.total_num) as pay_goods_count,count(distinct(order.user_id)) as pay_user_count', 'order.staff_shop_id');
        return $data;
    }

    /**
     * 单个退款
     * @param $startTime
     * @param $endTime
     * @return array
     */
    private static function getSingleRefundData($startTime, $endTime)
    {
        $data = Db::name('order_refund')->alias('r')
            ->where('r.refund_succ_time', '<=', $endTime)
            ->where('r.refund_succ_time', '>', $startTime)
            ->where('r.order_goods_id', '>', 0)
            ->where('r.status', '=', 20)
            ->where('r.finance_refund', '=', 10)
            ->leftJoin('order_goods og', 'og.order_goods_id=r.order_goods_id')
            ->leftJoin('order', 'order.order_id=r.order_id')
            ->group('order.staff_shop_id')
            ->column('sum(r.refund_money) as refund_amount,count(distinct(r.order_id)) as refund_order_count,sum(r.goods_num) as refund_goods_count', 'order.staff_shop_id');
        return $data;
    }

    /**
     * 整单退款
     * @param $startTime
     * @param $endTime
     * @return array
     */
    private static function getMultiRefundData($startTime, $endTime)
    {
        $data = Db::name('order_refund')->alias('r')
            ->where('r.refund_succ_time', '<=', $endTime)
            ->where('r.refund_succ_time', '>', $startTime)
            ->where('r.order_goods_id', '=', 0)
            ->where('r.status', '=', 20)
            ->where('r.finance_refund', '=', 10)
            ->leftJoin('order_goods og', 'og.order_id=r.order_id')
            ->leftJoin('order', 'order.order_id=r.order_id')
            ->group('order.staff_shop_id')
            ->column('sum(r.refund_money) as refund_amount,count(distinct(r.order_id)) as refund_order_count,sum(r.goods_num) as refund_goods_count', 'order.staff_shop_id');
        return $data;
    }
}