lachang/app/store/model/ChannelSaleStatistics.php

<?php

namespace app\store\model;

use app\common\library\helper;
use app\common\model\ChannelSaleStatistics as ChannelSaleStatisticsModel;

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

    public function getLst($param = [], $returnTotal = false, $offset = 0, $perPage = 15)
    {
        $model = $this->setQueryFilter($param);
        $query = $model->alias('st')
            ->field('shops.shop_id,shops.is_virtual as shop_type,shops.shop_name,sum(st.pay_amount) as pay_amount,sum(st.pay_count) as pay_count,
            sum(pay_goods_count) as pay_goods_count,sum(pay_user_count) as pay_user_count,
            sum(refund_amount) as refund_amount,sum(refund_order_count) as refund_order_count,sum(refund_goods_count) as refund_goods_count')
            ->leftJoin('shops', 'shops.shop_id=st.shop_id')
            ->where('is_virtual', 'in', [Shops::SHOP_TYPE_VIRTUAL, Shops::SHOP_TYPE_DIFF]) // 筛选 虚拟门店、异业合作
            ->whereOr('st.shop_id', '=', 0)
            ->group('st.shop_id')
            ->order(['shops.is_virtual' => 'asc', 'pay_amount' => 'desc']);
        if ($returnTotal) {
            return $query->count();
        } else {
            return $query->limit($offset, $perPage)
                ->select()->toArray();
        }
    }
    /**
     * 获取列表
     * @param $param
     * @return mixed
     */
    public function getList($param = [])
    {

        $other = $this->getOtherData($param);
        $otherCount = count($other);

        $oPerPage = $perPage = 15;
        $curPage = $param['page'] ?? 1;
        $offset = ($curPage - 1) * $perPage - $otherCount;
        $perPage = $perPage - $otherCount;
        if ($curPage == 1) { // 第一页只取13条数据
//            $perPage = $perPage - $otherCount;
            $offset = ($curPage - 1) * $perPage;
        }

        $total = $this->getLst($param, true) + $otherCount;
        $data = $this->getLst($param, false, $offset, $perPage);


        if ($curPage == 1) { // 第一页只取13条数据
            $lastPage = ceil(($total  + $perPage - 1) / $oPerPage);
        } else {
            $lastPage = ceil(($total - $otherCount + $oPerPage - 1) / $oPerPage);
        }

        if ($curPage == 1 && $total <= $oPerPage) {
            $lastPage = 1;
        }

        $result = $curPage == 1 ? array_merge($other, $data) : $data;
        if (!empty($result)) {
            foreach ($result as &$item) {
                $text = Shops::SHOP_TYPE[$item['shop_type']] ?? '其他渠道';
                if (in_array($item['shop_type'], [Shops::SHOP_TYPE_VIRTUAL, Shops::SHOP_TYPE_DIFF])) {
                    $text .= "({$item['shop_name']})";
                } else {
                    $item['shop_id'] = 0;
                    $item['shop_name'] = '';
                }
                $item['shop_type_text'] = $text;
            }
        }
        return [
            'current_page' => (int)$curPage,
            'data' => $result,
            'last_page' => $lastPage,
            'per_page' => $oPerPage,
            'total' => $total,
        ];
    }

    public function getOtherData($param = [])
    {
        $model = $this->setQueryFilter($param);
        // 排序条件
        $data = $model->alias('st')
            ->field('st.shop_id,shops.is_virtual as shop_type,sum(st.pay_amount) as pay_amount,sum(st.pay_count) as pay_count,
            sum(pay_goods_count) as pay_goods_count,sum(pay_user_count) as pay_user_count,
            sum(refund_amount) as refund_amount,sum(refund_order_count) as refund_order_count,sum(refund_goods_count) as refund_goods_count')
            ->leftJoin('shops', 'shops.shop_id=st.shop_id')
            ->where('is_virtual', 'in', [Shops::SHOP_TYPE_JOIN, Shops::SHOP_TYPE_SELF]) // // 筛选 自营店、加盟店
            ->where('st.shop_id', '>', 0)
            ->group('shops.is_virtual')
            ->order(['shops.is_virtual' => 'asc'])
            ->select()->toArray();

        return $data;
    }

    /**
     * 检索查询条件
     * @param array $param
     * @return \think\db\BaseQuery
     */
    private function setQueryFilter(array $param)
    {
        // 实例化查询对象
        $query = $this->getNewQuery();
        // 查询参数
        $params = $this->setQueryDefaultValue($param, []);

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

        $query->where($filter);
        return $query;
    }

    /**
     * 导出记录
     */
    public function export(array $param)
    {
        $data['header'] = ['渠道类型', '支付金额（元）', '支付订单数', '支付商品件数', '支付买家数', '退货金额（元）', '退货订单数', '退商品件数'];
        $data['filename'] = '渠道销售数据导出';
        $data['data'] = [];

        $model = $this->setQueryFilter($param);

        $other = $this->getOtherData($param);
        $query = $model->alias('st')
            ->field('shops.shop_id,shops.is_virtual as shop_type,shops.shop_name,sum(st.pay_amount) as pay_amount,sum(st.pay_count) as pay_count,
            sum(pay_goods_count) as pay_goods_count,sum(pay_user_count) as pay_user_count,
            sum(refund_amount) as refund_amount,sum(refund_order_count) as refund_order_count,sum(refund_goods_count) as refund_goods_count')
            ->leftJoin('shops', 'shops.shop_id=st.shop_id')
            ->where('shops.is_virtual', 'in', [Shops::SHOP_TYPE_VIRTUAL, Shops::SHOP_TYPE_DIFF]) // 筛选 虚拟门店、异业合作
            ->whereOr('st.shop_id', '=', 0)
            ->group('st.shop_id')
            ->order(['shops.is_virtual' => 'asc', 'pay_amount' => 'desc']);
        $lst = $query->select()->toArray();

        $list = array_merge($other, $lst);

        foreach ($list as $arr) {
            $text = Shops::SHOP_TYPE[$arr['shop_type']] ?? '其他渠道';
            if (in_array($arr['shop_type'], [Shops::SHOP_TYPE_VIRTUAL, Shops::SHOP_TYPE_DIFF])) {
                $text .= "({$arr['shop_name']})";
            }

            $new_list['shop_type_text'] = $text;
            $new_list['pay_amount'] = helper::number2($arr['pay_amount']);
            $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['refund_amount'] = helper::number2($arr['refund_amount']);
            $new_list['refund_order_count'] = $arr['refund_order_count'];
            $new_list['refund_goods_count'] = $arr['refund_goods_count'];
            $data['data'][] = $new_list;
        }

        return $data;
    }

}