lachang/app/store/model/GoodsVisitStatistics.php

<?php

namespace app\store\model;

use app\common\library\helper;
use app\common\model\GoodsVisitStatistics as GoodsVisitStatisticsModel;

/**
 * 商品浏览统计模型
 * @package app\store\model
 */
class GoodsVisitStatistics extends GoodsVisitStatisticsModel
{
    /**
     * 获取列表
     * @param $param
     * @return mixed
     */
    public function getList($param = [])
    {
        $model = $this->setQueryFilter($param);
        // 排序条件
        $sort = $this->setQuerySort($param);
        return $model->alias('st')
            ->field('sum(st.pv) as pv,sum(st.uv) as uv,sum(stay_time) as stay_time,sum(cart_users) as cart_users,sum(fav_users) as fav_users,sum(share_users) as share_users,sum(pay_users) as pay_users,st.goods_id,g.goods_name,g.goods_no,g.create_time,convert((sum(st.pay_users)/sum(st.uv))*100,decimal(10,2)) as ratio')
            ->leftJoin('goods g', 'g.goods_id=st.goods_id')
            ->group('st.goods_id')
            ->order($sort)
            ->paginate(15)
            ->each(function ($item) {
                $item['stay_time'] = $item['uv'] > 0 ? gmdate('I:s', intval($item['stay_time'] / $item['uv'])) : '0:00';
                $item['ratio'] = helper::number2($item['ratio']);
            });
    }

    /**
     * 检索排序条件
     * @param array $param
     * @return array|string[]
     */
    private function setQuerySort(array $param = [])
    {
        $params = $this->setQueryDefaultValue($param, [
            'sortField' => '',    // 排序类型 默认空：按页面UV倒序 页面PV-pv 页面UV-uv 加购人数-cart_users 收藏人数-fav_users 分享人数-share_users 支付人数-pay_users 支付转化率-ratio
            'sortOrder' => 'descend',   // 排序方式 (descend-高到低 ascend-低到高)
        ]);
        // 排序规则
        $sort = [];
        if (!empty($params['sortField'])) {
            $sort = $params['sortOrder'] == 'descend' ? [$params['sortField'] => 'desc'] : [$params['sortField'] => 'asc'];
        } else {
            $sort = ['uv' => '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按月
        ]);

        $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', '商品名称','商品编码', '发布时间','页面PV', '页面UV', '平均停留时长','加购人数','收藏人数','分享人数','支付人数','支付转化率'];
        $data['filename'] = '商品浏览数据导出';
        $data['data'] = [];
        $model = $this->setQueryFilter($param);
        // 排序条件
        $sort = $this->setQuerySort($param);
        $list = $model->alias('st')
            ->field('sum(st.pv) as pv,sum(st.uv) as uv,sum(stay_time) as stay_time,sum(cart_users) as cart_users,sum(fav_users) as fav_users,sum(share_users) as share_users,sum(pay_users) as pay_users,st.goods_id,g.goods_name,g.goods_no,g.create_time,convert((sum(st.pay_users)/sum(st.uv))*100,decimal(10,2)) as ratio')
            ->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['pv'] = $arr['pv'];
            $new_list['uv'] = $arr['uv'];
            $new_list['stay_time'] = $arr['uv'] > 0 ? gmdate('I:s', intval($arr['stay_time'] / $arr['uv'])) : '0:00';
            $new_list['cart_users'] = $arr['cart_users'];
            $new_list['fav_users'] = $arr['fav_users'];
            $new_list['share_users'] = $arr['share_users'];
            $new_list['pay_users'] = $arr['pay_users'];
            $new_list['ratio'] = helper::number2($arr['ratio']);
            $data['data'][] = $new_list;
        }

        return $data;
    }
    
}