<?php
// +----------------------------------------------------------------------
// | 康比特
// +----------------------------------------------------------------------
declare (strict_types=1);

namespace app\store\service\statistics\data;

use app\common\service\BaseService;
use app\store\model\OrderGoods as OrderGoodsModel;
use app\common\database\Dm;
use app\common\enum\order\OrderStatus as OrderStatusEnum;
use app\common\enum\order\PayStatus as OrderPayStatusEnum;

/**
 * 数据统计-商品销售榜
 * Class GoodsRanking
 * @package app\store\service\statistics\data
 */
class GoodsRanking extends BaseService
{
    /**
     * 商品销售榜
     * @return mixed
     */
    public function getGoodsRanking()
    {
        $query = (new OrderGoodsModel)->alias('o_goods');
        $isDm = $query->getConnection() instanceof Dm;
        $query->field([
                'goods_id',
                // 这里采用聚合函数获取goods_name, 因为mysql5.7使用group的报错问题
                'MAX(goods_name) AS goods_name',
                $isDm
                    ? "TO_CHAR(SUM(total_pay_price), 'FM99999999999999999999999999999999999990.00') AS sales_volume"
                    : 'SUM(total_pay_price) AS sales_volume',
                $isDm
                    ? 'CAST(SUM(total_num) AS VARCHAR(40)) AS total_sales_num'
                    : 'SUM(total_num) AS total_sales_num'
            ])
            ->join('order ord', 'ord.order_id = o_goods.order_id')
            ->where('ord.pay_status', '=', OrderPayStatusEnum::SUCCESS)
            ->where('ord.order_status', '<>', OrderStatusEnum::CANCELLED)
            // 这里如果写入goods_name的话，会出现同商品ID不同name的数据
            ->group('goods_id');
        // Sort by the numeric aggregate, not the DM text projection.
        if ($isDm) {
            $query->orderRaw('SUM(total_pay_price) DESC');
        } else {
            $query->order(['sales_volume' => 'DESC']);
        }
        return $query->limit(10)->select();
    }
}