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; } }