lachang/app/store/service/Home.php

<?php
// +----------------------------------------------------------------------
// | 萤火商城系统 [ 致力于通过产品和服务，帮助商家高效化开拓市场 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2021 https://www.yiovo.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
// +----------------------------------------------------------------------
// | Author: 萤火科技 <admin@yiovo.com>
// +----------------------------------------------------------------------
declare (strict_types = 1);

namespace app\store\service;

use app\common\enum\order\refund\RefundStatus as RefundStatusEnum;
use app\common\library\helper;
use app\common\model\Cart;
use app\common\service\BaseService;
use app\store\model\OrderRefund;
use app\store\model\User as UserModel;
use app\store\model\Goods as GoodsModel;
use app\store\model\ShopGoods as ShopGoodsModel;
use app\store\model\Order as OrderModel;
use app\store\model\OrderGoods as OrderGoodsModel;
use app\store\model\Receipt as ReceiptModel;
use app\store\model\OrderRefund as OrderRefundModel;
use app\store\model\UserCoupon;
use app\store\model\VisitRecord;
use app\common\enum\order\OrderStatus as OrderStatusEnum;
use app\common\enum\order\PayStatus as PayStatusEnum;
use think\facade\Db;

/**
 * 商户数据服务类
 * Class Store
 * @package app\store\service
 */
class Home extends BaseService
{
    /* @var GoodsModel $GoodsModel */
    private $GoodsModel;

    /* @var OrderModel $GoodsModel */
    private $OrderModel;

    private $OrderGoodsModel;

    /* @var UserModel $GoodsModel */
    private $UserModel;

    private $ReceiptModel;

    /**
     * 构造方法
     */
    public function __construct()
    {
        parent::__construct();
        /* 初始化模型 */
        $this->GoodsModel = new GoodsModel;
        $this->OrderModel = new OrderModel;
        $this->OrderGoodsModel = new OrderGoodsModel;
        $this->UserModel = new UserModel;
        $this->ReceiptModel = new ReceiptModel;
    }

    /**
     * 后台首页数据
     * @return array
     */
    public function getData(): array
    {
        // 今天的日期
        $today = date('Y-m-d');
//        $today = '2022-06-16';
        // 昨天的日期
        $yesterday = date('Y-m-d', strtotime('-1 day'));
//        $yesterday = '2022-06-15';
        // 最近七天日期
        $lately7days = $this->getLately7days();

        $tdayPayRatio = $this->getuVTotal($today) > 0 ?
            helper::number2($this->getPayOrderUserTotal($today)/$this->getuVTotal($today)*100) : '0.00';
        $ytdPayRatio = $this->getuVTotal($yesterday) > 0 ?
            helper::number2($this->getPayOrderUserTotal($yesterday)/$this->getuVTotal($yesterday)*100) : '0.00';
        $tdayUserUnitPrice = $this->getPayOrderUserTotal($today) > 0 ?
            helper::number2($this->getOrderTotalPrice($today)/$this->getPayOrderUserTotal($today)) : '0.00';
        $ytdUserUnitPrice = $this->getPayOrderUserTotal($yesterday) > 0 ?
            helper::number2($this->getOrderTotalPrice($yesterday)/$this->getPayOrderUserTotal($yesterday)) : '0.00';

        $data = [
            // 实时概况
            'overview' => [
                // 支付金额(元)
                'orderTotalPrice' => [
                    'tday' => $this->getOrderTotalPrice($today),
                    'ytd' => $this->getOrderTotalPrice($yesterday),
                    'tb' => $this->formatTb($this->getOrderTotalPrice($today), $this->getOrderTotalPrice($yesterday)),
                ],
                // 新客成交金额（元）
                'newUserOrderAmount' => $this->getNewUserOrderAmount($today),

                // 老客成交金额（元）
                'oldUserOrderAmount' => $this->getOldUserOrderAmount($today),

                // 支付人数
                'consumeUserTotal' => [
                    'tday' => $this->getPayOrderUserTotal($today),
                    'ytd' => $this->getPayOrderUserTotal($yesterday),
                    'tb' => $this->formatTb($this->getPayOrderUserTotal($today), $this->getPayOrderUserTotal($yesterday))
                ],

                // 支付订单数
                'orderTotal' => [
                    'tday' => $this->getPayOrderTotal($today),
                    'ytd' => $this->getPayOrderTotal($yesterday),
                    'tb' => $this->formatTb($this->getPayOrderTotal($today), $this->getPayOrderTotal($yesterday))
                ],
                // 支付商品件数
                'orderGoodsTotal' => [
                    'tday' => $this->getPayOrderGoodsTotal($today),
                    'ytd' => $this->getPayOrderGoodsTotal($yesterday),
                    'tb' => $this->formatTb($this->getPayOrderGoodsTotal($today), $this->getPayOrderGoodsTotal($yesterday))
                ],

                // 新增会员数
                'newUserTotal' => [
                    'tday' => $this->getUserTotal($today),
                    'ytd' => $this->getUserTotal($yesterday)
                ],
                //领券用户数
                'couponUserTotal' => [
                    'tday' => $this->getCouponUserTotal($today),
                    'ytd' => $this->getCouponUserTotal($yesterday)
                ],

                //加购用户数
                'cartUserTotal' => [
                    'tday' => $this->getCartUserTotal($today),
                    'ytd' => $this->getCartUserTotal($yesterday)
                ],

                //退款订单数
                'refundOrderTotal' => [
                    'tday' => $this->getRefundOrderTotal($today),
                    'ytd' => $this->getRefundOrderTotal($yesterday)
                ],

                //退款商品件数
                'refundGoodsTotal' => [
                    'tday' => $this->getRefundGoodsTotal($today),
                    'ytd' => $this->getRefundGoodsTotal($yesterday)
                ],

                //退款金额
                'refundMoneyTotal' => [
                    'tday' => $this->getRefundMoneyTotal($today),
                    'ytd' => $this->getRefundMoneyTotal($yesterday)
                ],
                // 访客数
                'uvTotal' => [
                    'tday' => $this->getuVTotal($today),
                    'ytd' => $this->getuVTotal($yesterday),
                    'tb' => $this->formatTb($this->getuVTotal($today), $this->getuVTotal($yesterday))
                ],

                // 浏览量
                'pvTotal' => [
                    'tday' => $this->getpVTotal($today),
                    'ytd' => $this->getpVTotal($yesterday),
                    'tb' => $this->formatTb($this->getpVTotal($today), $this->getpVTotal($yesterday))
                ],

                // 支付转化率
                'payRatio' => [
                    'tday' => $tdayPayRatio,
                    'ytd' => $ytdPayRatio,
                    'tb' => $this->formatTb($tdayPayRatio, $ytdPayRatio)
                ],

                // 客单价
                'userUnitPrice' => [
                    'tday' => $tdayUserUnitPrice,
                    'ytd' => $ytdUserUnitPrice,
                    'tb' => $this->formatTb($tdayUserUnitPrice, $ytdUserUnitPrice)
                ],

            ],
            // 数据统计
            'statistics' => [
                // 在售商品总数量
                'onSaleGoodsTotal' => $this->getOnSaleGoodsTotal(),
                // 会员总人数
                'userTotal' => $this->getUserTotal(),
                // 本月付款订单总量
                'orderTotal' => $this->getMonthPayOrderTotal(),
                // 本月消费总人数
                'consumeUserTotal' => $this->getMonthPayUserTotal()
            ],
            // 待办事项
            'pending' => [
                // 待发货订单
                'deliverOrderTotal' => $this->getNotDeliveredOrderTotal(),
                'deliverShopOrderTotal' => $this->getNotDeliveredShopOrderTotal(),//待提货订单
                // 待处理售后单
                'refundTotal' => $this->getRefundTotal(),
                // 待付款订单(笔)
                'paidOrderTotal' => $this->getNotPayOrderTotal(),
                // 已售罄商品数量
                'soldoutGoodsTotal' => $this->getSoldoutGoodsTotal(),
                // 库存预警商品(SKU)
                'alarmGoodsTotal' => $this->getAlarmGoodsTotal(),
                // 门店库存预警商品(SKU)
                'alarmShopGoodsTotal' => $this->getAlarmShopGoodsTotal(),
                // 待开发票
                'waitReceiptTotal' => $this->getWaitReceiptTotal()
            ],
            // 交易走势
            'tradeTrend' => [
                // 最近七天日期
                'date' => $lately7days,
                'orderTotal' => $this->getOrderTotalByDate($lately7days),
                'orderTotalPrice' => $this->getOrderTotalPriceByDate($lately7days)
            ]
        ];
        return $data;
    }

    /**
     * 格式化输出较前一日对比
     *
     * @param float|int $tday 今日
     * @param float|int $ytd 明日
     * @return array
     */
    private function formatTb($tday, $ytd)
    {
        if ($tday == 0 && $ytd > 0) {
            $type = 2;
            $percent = '--';
        } elseif ($tday > 0 && $ytd == 0) {
            $type = 1;
            $percent = '--';
        } elseif ($tday == $ytd) {
            $type = 3;
            $percent = '持平';
        } else {
            $percent = helper::bcdiv(abs($tday-$ytd), $ytd, 3) * 100;
            $percent = $percent.'%';
            if ($tday > $ytd) {
                $type = 1;
            } else {
                $type = 2;
            }
        }

        return [
            'type' => $type,
            'percent' => $percent
        ];
    }

    /**
     * 最近七天日期
     */
    private function getLately7days()
    {
        // 获取当前周几
        $date = [];
        for ($i = 0; $i < 7; $i++) {
            $date[] = date('Y-m-d', strtotime('-' . $i . ' days'));
        }
        return array_reverse($date);
    }

    /**
     * 获取商品总量
     * @return string
     */
    private function getOnSaleGoodsTotal()
    {
        return number_format($this->GoodsModel->getOnSaleGoodsTotal());
    }

    /**
     * 会员总人数
     * @param string $date 注册日期
     * @param true $isConsume 是否已消费
     * @return string
     */
    private function getUserTotal(string $date = null, $isConsume = null)
    {
        return number_format($this->UserModel->getUserTotal(compact('date', 'isConsume')));
    }

    /**
     * 获取某天的领券用户数
     * @param $date
     */
    private function getCouponUserTotal($date)
    {
        $startTime = strtotime($date);

        $data = UserCoupon::where('create_time', '>=', $startTime)
            ->where('create_time', '<', $startTime + 86400)
            ->group('user_id')->count();
        return $data;
    }

    /**
     * 获取某天的加购用户数
     * @param string $date
     * @return float|int
     */
    public function getCartUserTotal(string $date)
    {
        $startTime = strtotime($date);
        return Cart::field('user_id')
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<', $startTime + 86400)
            ->where('is_delete', '=', '0')
            ->group('user_id')
            ->count();
    }

    /**
     * 获取某天的退款订单数
     * @param $date
     */
    private function getRefundOrderTotal($date)
    {
        $startTime = strtotime($date);
        $filter[] = ['status', '=', RefundStatusEnum::COMPLETED];
        $filter[] = ['finance_refund', '=', 10];
        $filter[] = ['refund_succ_time', '>=', $startTime];
        $filter[] = ['refund_succ_time', '<', $startTime + 86400];
        $data = OrderRefund::where($filter)->group('order_id')->count();
        return $data;
    }

    /**
     * 获取某天的退款商品件数
     * @param $date
     */
    private function getRefundGoodsTotal($date)
    {
        $startTime = strtotime($date);
        $filter[] = ['status', '=', RefundStatusEnum::COMPLETED];
        $filter[] = ['finance_refund', '=', 10];
        $filter[] = ['refund_succ_time', '>=', $startTime];
        $filter[] = ['refund_succ_time', '<', $startTime + 86400];
        $data = OrderRefund::where($filter)->sum('goods_num');
        return $data;
    }

    /**
     * 获取某天的退款金额
     * @param $date
     */
    private function getRefundMoneyTotal($date)
    {
        $startTime = strtotime($date);
        $filter[] = ['status', '=', RefundStatusEnum::COMPLETED];
        $filter[] = ['finance_refund', '=', 10];
        $filter[] = ['refund_succ_time', '>=', $startTime];
        $filter[] = ['refund_succ_time', '<', $startTime + 86400];
        $res = OrderRefund::where($filter)->sum('refund_money');
        return helper::number2($res);
    }

    /**
     * 获取某天的uv
     * @param $date
     * @return int
     */
    private function getuVTotal($date)
    {
        $startTime = strtotime($date);
        $filter[] = ['v_type', '=', 1]; // 0-pv 1-uv
        $filter[] = ['create_time', '>=', $startTime];
        $filter[] = ['create_time', '<', $startTime + 86400];
        $data = VisitRecord::where($filter)->count();
        return $data;
    }

    /**
     * 获取某天的pv
     * @param $date
     * @return int
     */
    private function getpVTotal($date)
    {
        $startTime = strtotime($date);
        $filter[] = ['v_type', '=', 0]; // 0-pv 1-uv
//        $filter[] = ['visit_type', '=', 0]; // 0-首页
        $filter[] = ['create_time', '>=', $startTime];
        $filter[] = ['create_time', '<', $startTime + 86400];
        $data = VisitRecord::where($filter)->count();
        return $data;
    }

    /**
     * 获取已付款订单总量 (批量)
     * @param array $days
     * @return array
     */
    private function getOrderTotalByDate(array $days)
    {
        $data = [];
        foreach ($days as $day) {
            $data[] = $this->getPayOrderTotal($day);
        }
        return $data;
    }

    /**
     * 获取订单总金额(指定日期)
     * @param string $day
     * @return string
     */
    private function getOrderTotalPrice(string $day = null)
    {
        return helper::number2($this->OrderModel->getOrderTotalPrice($day, $day));
    }

    /**
     * 获取新客成交金额（指定日期）
     * 新客成交金额（元）：统计当日新客（指未在公明腊肠小程序下过单的用户）实际成交金额
     * 占总成交额：统计新客成交金额占当日新老客总成交金额的百分比，四舍五入保留1位小数
     * 如果一个新客当天先后下单2次，第1次算新客成交金额，第2次算老客成交金额
     * @param $date
     * @return array
     */
    private function getNewUserOrderAmount($date)
    {
        $startTime = strtotime($date);
        // 总销售额
        $totalAmount = OrderModel::where('pay_status', '=', PayStatusEnum::SUCCESS)
            ->where('order_status', '<>', OrderStatusEnum::CANCELLED)
            ->where('is_delete', '=', 0)
            ->where('pay_time', '>=', $startTime)
            ->where('pay_time', '<', $startTime + 86400)
            ->field('sum(pay_price+rice_card_money) as total_amount')
            ->find();
        $totalAmount = $totalAmount->total_amount ?? 0;

        // 获取新客成交金额 历史未下单
//        $newAmount = OrderModel::alias('o')->where('o.pay_status', '=', PayStatusEnum::SUCCESS)
//            ->where('o.order_status', '<>', OrderStatusEnum::CANCELLED)
//            ->where('o.is_delete', '=', 0)
//            ->whereNotExists("select 1 from yoshop_order where user_id=o.user_id and pay_status=20 and pay_time < {$startTime}")
//            ->where('pay_time', '>=', $startTime)
//            ->where('pay_time', '<', $startTime + 86400)
//            ->order('pay_time', 'desc')
//            ->group('o.user_id')
//            ->field('min(o.pay_time) pay_time,sum(o.pay_price+o.rice_card_money) as total_amount')
//            ->find();
//        $newAmount = $newAmount->total_amount ?? 0;
        $newAmountArr = Db::query("select o.user_id,sum(o.pay_price+o.rice_card_money) as total_amount
            from (select user_id,min(pay_time) as mp from `yoshop_order` group by user_id) as ump,`yoshop_order` as o
            where not exists(select 1 from yoshop_order where user_id=o.user_id and pay_status=20 and pay_time < ?)
            and ump.user_id=o.user_id and ump.mp=o.pay_time
              and pay_status = 20 and is_delete=0 and order_status <> 20
           and pay_time >= ? and pay_time < ? and ump.mp > 0 group by o.user_id order by pay_time;", [$startTime, $startTime, $startTime + 86400]);

        $newAmount = array_sum(array_column($newAmountArr,'total_amount'));
        return [
            'tday' => helper::number2($newAmount),
            'percent' => $totalAmount > 0 ? helper::number2($newAmount / $totalAmount * 100) : '0.00'
        ];
    }

    /**
     * 获取老客成交金额（指定日期）
     * 老客成交金额（元）：统计当日老客（指已在公明腊肠小程序下过单的用户）实际成交金额
     * 占总成交额：统计老客成交金额占当日新老客总成交金额的百分比，四舍五入保留1位小数
     * @param $date
     * @return array
     */
    private function getOldUserOrderAmount($date)
    {
        $startTime = strtotime($date);
        // 总销售额
        $totalAmount = OrderModel::where('pay_status', '=', PayStatusEnum::SUCCESS)
            ->where('order_status', '<>', OrderStatusEnum::CANCELLED)
            ->where('is_delete', '=', 0)
            ->where('pay_time', '>=', $startTime)
            ->where('pay_time', '<', $startTime + 86400)
            ->field('sum(pay_price+rice_card_money) as total_amount')
            ->find();
        $totalAmount = $totalAmount->total_amount ?? 0;

        // 获取老客成交金额
        $oldAmount = OrderModel::alias('o')->where('o.pay_status', '=', PayStatusEnum::SUCCESS)
            ->where('o.order_status', '<>', OrderStatusEnum::CANCELLED)
            ->where('o.is_delete', '=', 0)
            ->whereExists("select 1 from yoshop_order where user_id=o.user_id and pay_status=20 and pay_time < {$startTime}")
            ->where('pay_time', '>=', $startTime)
            ->where('pay_time', '<', $startTime + 86400)
            ->order('pay_time', 'desc')
            ->group('o.user_id')
            ->field('sum(o.pay_price+o.rice_card_money) as total_amount')
            ->find();

        // 当日新客消费大于1次的成交金额
        $newAmountFirst = $this->getNewUserOrderAmount($date)['tday'] ?? 0;

        // 当日新客消费总金额
        $newAmount = OrderModel::alias('o')->where('o.pay_status', '=', PayStatusEnum::SUCCESS)
            ->where('o.order_status', '<>', OrderStatusEnum::CANCELLED)
            ->where('o.is_delete', '=', 0)
            ->whereNotExists("select 1 from yoshop_order where user_id=o.user_id and pay_status=20 and pay_time < {$startTime}")
            ->where('pay_time', '>=', $startTime)
            ->where('pay_time', '<', $startTime + 86400)
            ->order('pay_time', 'desc')
            ->field('sum(o.pay_price+o.rice_card_money) as total_amount')
            ->find();
        $newAmount = $newAmount->total_amount ?? 0;

        $oldAmount = $oldAmount->total_amount ?? 0;
        $oldAmount = $oldAmount + ($newAmount - $newAmountFirst);

        return [
            'tday' => helper::number2($oldAmount),
            'percent' => $totalAmount > 0 ? helper::number2($oldAmount / $totalAmount * 100) : '0.00'
        ];
    }

    /**
     * 获取订单总金额 (批量)
     * @param array $days
     * @return array
     */
    private function getOrderTotalPriceByDate(array $days)
    {
        $data = [];
        foreach ($days as $day) {
            $data[] = $this->getOrderTotalPrice($day);
        }
        return $data;
    }

    /**
     * 获取某天的下单用户数
     * @param string $day
     * @return float|int
     */
    private function getPayOrderUserTotal(string $day)
    {
        return number_format($this->OrderModel->getPayOrderUserTotal($day));
    }

    /**
     * 获取订单总量
     * @param string $day
     * @return string
     */
    private function getPayOrderTotal(string $day = null)
    {
        return number_format($this->OrderModel->getPayOrderTotal($day, $day));
    }

    /**
     * 获取支付商品件数
     * @param string $day
     * @return string
     */
    private function getPayOrderGoodsTotal(string $day = null)
    {
        return number_format($this->OrderModel->getPayOrderGoodsTotal($day, $day));
    }

    /**
     * 获取本月已付款订单总量
     * @return string
     */
    private function getMonthPayOrderTotal()
    {
        return number_format($this->OrderModel->getMonthPayOrderTotal());
    }

    public function getMonthPayUserTotal() {
        return number_format($this->OrderModel->getMonthPayUserTotal());
    }

    // 获取未发货订单数量
    private function getNotDeliveredOrderTotal()
    {
        return number_format($this->OrderGoodsModel->getNotDeliveredOrderTotal());
    }

    //获取未自提订单数量
    private function getNotDeliveredShopOrderTotal(){
        return number_format($this->OrderGoodsModel->getNotDeliveredShopOrderTotal());
    }

    // 获取未付款订单数量
    private function getNotPayOrderTotal()
    {
        return number_format($this->OrderModel->getNotPayOrderTotal());
    }

    // 获取已售罄的商品
    private function getSoldoutGoodsTotal()
    {
        return number_format($this->GoodsModel->getSoldoutGoodsTotal());
    }

    // 获取库存预警的商品(SKU)
    private function getAlarmGoodsTotal()
    {
        return number_format($this->GoodsModel->getAlarmGoodsTotal());
    }

    // 获取门店库存预警的商品(SKU)
    private function getAlarmShopGoodsTotal()
    {
        return number_format((new ShopGoodsModel())->getAlarmGoodsTotal());
    }

    private function getWaitReceiptTotal()
    {
        return number_format($this->ReceiptModel->getWaitReceiptTotal());
    }

    // 获取待处理售后单数量
    private function getRefundTotal()
    {
        $model = new OrderRefundModel;
        return number_format($model->getRefundTotal());
    }

}