lachang/app/store/model/GoodsSaleStatistics.php

<?php

namespace app\store\model;

use app\common\library\helper;
use app\common\model\GoodsSaleStatistics as GoodsSaleStatisticsModel;

/**
 * 商品销售统计模型
 * @package app\store\model
 */
class GoodsSaleStatistics extends GoodsSaleStatisticsModel
{
    /**
     * 获取列表
     * @param $param
     * @return mixed
     */
    public function getList($param = [])
    {
        $model = $this->setQueryFilter($param);
        // 排序条件
        $sort = $this->setQuerySort($param);
        return $model->alias('st')
            ->field('sum(st.order_count) as order_count,sum(st.order_user_count) as order_user_count,sum(pay_count) as pay_count,sum(pay_goods_count) as pay_goods_count,sum(pay_user_count) as pay_user_count,sum(order_amount) as order_amount,sum(first_order_users) as first_order_users,sum(recorder_users) as recorder_users,st.goods_id,g.goods_name,g.goods_no,g.create_time,convert((sum(st.order_amount)/sum(st.pay_user_count)),decimal(10,2)) as user_unit_price')
            ->leftJoin('goods g', 'g.goods_id=st.goods_id')
            ->group('st.goods_id')
            ->order($sort)
            ->paginate(15)
            ->each(function ($item) {
                $item['order_amount'] = helper::number2($item['order_amount']);
                $item['user_unit_price'] = helper::number2($item['user_unit_price']);
            });
    }

    /**
     * 检索排序条件
     * @param array $param
     * @return array|string[]
     */
    private function setQuerySort(array $param = [])
    {
        $params = $this->setQueryDefaultValue($param, [
            'sortField' => '',    // 排序类型 默认空：按销售总额倒序 order_count-总订单数 order_user_count-总下单人数 pay_count-实付订单数 pay_goods_count-实付商品件数 pay_user_count-实付买家数 order_amount-销售总额 first_order_users-首次下单人数 recorder_users-复购人数 user_unit_price-客单价
            'sortOrder' => 'descend',   // 排序方式 (descend-高到低 ascend-低到高)
        ]);
        // 排序规则
        $sort = [];
        if (!empty($params['sortField'])) {
            $sort = $params['sortOrder'] == 'descend' ? [$params['sortField'] => 'desc'] : [$params['sortField'] => 'asc'];
        } else {
            $sort = ['order_amount' => 'desc'];
        }
        return array_merge($sort, [$this->getPk() => 'desc']);
    }

    /**
     * 检索查询条件
     * @param array $param
     * @return \think\db\BaseQuery
     */
    private function setQueryFilter(array $param)
    {
        // 实例化查询对象
        $query = $this->getNewQuery();
        // 查询参数
        $params = $this->setQueryDefaultValue($param, [
            'ftype' => -1 // -1全部 1按日 2按周 3按月
        ]);

        $filter[] = ['g.is_delete', '=', 0];
        $params['ftype']>-1 && $filter[] = ['st.ftype', '=', (int)$params['ftype']];

        // 起止时间
        if (!empty($params['betweenTime'])) {
            $times = between_time($params['betweenTime']);
            $filter[] = ["st.start_time", '>=', $times['start_time']];
            $filter[] = ["st.end_time", '<=', $times['end_time']];
        }
        $filter[] = ['g.goods_id', '>', 0];
        $query->where($filter);
        return $query;
    }

    /**
     * 导出记录
     */
    public function export(array $param)
    {
        $data['header'] = ['商品ID', '商品名称','商品编码', '发布时间','总订单数', '总下单人数', '实付订单数','实付商品件数','实付买家数',
            '销售总额(元)','客单价(元)','首次下单人数','复购人数'];
        $data['filename'] = '商品销售数据导出';
        $data['data'] = [];
        $model = $this->setQueryFilter($param);
        // 排序条件
        $sort = $this->setQuerySort($param);
        $list = $model->alias('st')
            ->field('sum(st.order_count) as order_count,sum(st.order_user_count) as order_user_count,
            sum(pay_count) as pay_count,sum(pay_goods_count) as pay_goods_count,sum(pay_user_count) as pay_user_count,
            sum(order_amount) as order_amount,sum(first_order_users) as first_order_users,sum(recorder_users) as recorder_users,st.goods_id,g.goods_name,g.goods_no,g.create_time,convert((sum(st.order_amount)/sum(st.pay_user_count)),decimal(10,2)) as user_unit_price')
            ->leftJoin('goods g', 'g.goods_id=st.goods_id')
            ->group('st.goods_id')
            ->order($sort)
            ->select();
        foreach ($list as $arr){
            $new_list['goods_id'] = $arr['goods_id'];
            $new_list['goods_name'] = $arr['goods_name'];
            $new_list['goods_no'] = $arr['goods_no'];
            $new_list['create_time'] = $arr['create_time'];
            $new_list['order_count'] = $arr['order_count'];
            $new_list['order_user_count'] = $arr['order_user_count'];
            $new_list['pay_count'] = $arr['pay_count'];
            $new_list['pay_goods_count'] = $arr['pay_goods_count'];
            $new_list['pay_user_count'] = $arr['pay_user_count'];
            $new_list['order_amount'] = helper::number2($arr['order_amount']);
            $new_list['user_unit_price'] = helper::number2($arr['user_unit_price']);
            $new_list['first_order_users'] = $arr['first_order_users'];
            $new_list['recorder_users'] = $arr['recorder_users'];
            $data['data'][] = $new_list;
        }

        return $data;
    }
    
}