public function deviceList($params)
{
$time = time();
$page = isset($params['page']) ? $params['page'] : 1;
$page_size = isset($params['page_size']) ? $params['page_size'] : 10;
$type = isset($params['type']) ? $params['type'] : 1;//1正常,2过期
$nickname = $params['nickname'] ?? "";
$account = $params['account'] ?? "";
$uid = $params['uid'] ?? "";
$time_type = $params['time_type'] ?? 1;//1购买时间,2有效期
$device_type = $params['device_type'] ?? "";
$device_str = $params['device_str'] ?? "";
$start_time = $params['start_time'] ?? "";
$end_time = $params['end_time'] ?? "";
$export = $params['export'] ?? "";
if ($device_type != 'asc' && $device_type != 'desc'){
$device_type = 'desc';
}
if ($device_str == 'device_days'){//设备天数
$order = 'c.expire_time '. $device_type;
}elseif ($device_str == 'defer_count'){//续费次数
$order = 'defer_count '.$device_type;
}elseif ($device_str == 'use_duration'){//使用时长
$order = 'c.use_duration '.$device_type;
}elseif ($device_str == 'surplus'){//剩余时长
$order = 'surplus '.$device_type;
}elseif ($device_str == 'use_duration_rate'){//使用占比时长
$order = 'use_duration_rate '.$device_type;
}elseif ($device_str == 'buy_time'){//购买时间
$order = 'c.valid_time '.$device_type;
}elseif ($device_str == 'expire_time'){//过期时间
$order = 'c.expire_time '.$device_type;
}else{
$order = 'c.valid_time '.$device_type;
}
$map = [];
$map_time = [];
if ($type == 1){//未过期
$map['c.expire_time'] = ['>',$time];
}else{
$map['c.expire_time'] = ['<',$time];
}
if (!empty($nickname)){
$map['u.nick_name'] = ['=',$nickname];
}
if (!empty($account)){
$map['u.account'] = ['=',$account];
}
if (!empty($uid)){
$map['c.uid'] = ['=',$uid];
}
if (!empty($uid)){
$map['c.uid'] = ['=',$uid];
}
if ($time_type == 1){
if (!empty($start_time)){
$start_time = strtotime($start_time);
$map['c.valid_time'] = ['>',$start_time];
$map_time['valid_time'] = ['>',$start_time];
}
if (!empty($end_time)){
$end_time = strtotime($end_time) + 86400;
$map['c.valid_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];
// $map['c.valid_time'] = ['<',$end_time];
$map_time['valid_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];;
}
}elseif($time_type == 2){
if (!empty($start_time)){
$start_time = strtotime($start_time);
$map['c.expire_time'] = ['>',$start_time];
$map_time['expire_time'] = ['>',$start_time];
}
if (!empty($end_time)){
$end_time = strtotime($end_time) + 86400;
$map['c.expire_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];;
$map_time['expire_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];;
}
}else{
if (!empty($start_time)){
$start_time = strtotime($start_time);
$map['c.expire_time'] = ['>',$start_time];
$map_time['create_time'] = ['>',$start_time];
}
if (!empty($end_time)){
$end_time = strtotime($end_time) + 86400;
$map['c.expire_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];;
$map_time['create_time'] = $start_time ? [['>',$start_time],['<',$end_time]] : ['<',$end_time];;
}
}
$model = new CloudDevice();
$field = [
'c.id',
'c.uid',
'c.valid_time',
'c.use_duration',
'c.last_event_time',
'c.last_event_type',
'c.defer_count',
Db::raw('(c.expire_time-c.use_duration-c.valid_time) surplus'),
Db::raw('(c.use_duration/(c.expire_time-c.valid_time)) use_duration_rate'),
'c.expire_time',
'u.nick_name',
'u.account',
];
$list = $model->alias('c')
->join('box_user u','c.uid=u.id','left')
->field($field)->where($map)->order($order)
->paginate($page_size)->toArray();
// dump($model->getLastSql());
//dump($list);die();
if (!empty($list['data'])){
foreach ($list['data'] as $key=>$value){
$list['data'][$key]['device_day'] = intval(($value['expire_time']-$value['valid_time'])/86400).'天';
if ($value['last_event_type'] == 'RUN' || $value['last_event_type'] == 'HANG'){
$value['use_duration'] += $time - $value['last_event_time'];
}
$list['data'][$key]['use_duration'] = $this->secsToStr($value['use_duration']);
$list['data'][$key]['surplus'] = $this->secsToStr($value['expire_time']-$value['use_duration']-$value['valid_time']);
$list['data'][$key]['use_duration_rate'] = round(($value['use_duration']/($value['expire_time']-$value['valid_time']))*100,2).'%';
$list['data'][$key]['valid_time'] = date('Y-m-d H:i:s',$value['valid_time']);
$list['data'][$key]['expire_time'] = date('Y-m-d H:i:s',$value['expire_time']);
}
//导出
if (!empty($export)){
$this->exportCsv($list['data']);
}
}
$valid_device = $model->where($map_time)->where('expire_time','>',$time)->count();
$valid_account = $model->where($map_time)->where('expire_time','>',$time)->group('uid')->count();
$invalid_device = $model->where($map_time)->where('expire_time','<',$time)->count();
$list['valid_device'] = $valid_device ?? 0;
$list['valid_account'] = $valid_account ?? 0;
$list['invalid_device'] = $invalid_device ?? 0;
return $list;
}
列表查询排序参数处理
最新推荐文章于 2023-10-13 21:06:51 发布