public function getUserList(Request $request)
{
DB::connection()->enableQueryLog();
$keyword = trim($request->get('keyword', ''));
$province = $request->get('province', 0);
$thirty_days_duration = $request->get('thirty_days_duration', '');
$payment_status = $request->get('payment_status', '');
$thirty_days_boot_times = $request->get('thirty_days_boot_times', '');
$users = StudentUser::select('users.uid', 'cellphone', 'nickname', 'regdate', 'province_id', 'boot_times', 'paid', 'payable');
// 按用户名关键字查询
if (!empty($keyword)) {
$users->where('nickname', 'like', "%{$keyword}%");
}
// 按"地域"查询用户
if (!empty($province)) {
$users->where('province_id', $province);
}
// 按"总练习时间"分组
$users->leftjoin('practice', function ($join) use($thirty_days_duration, $payment_status) {
$join->on('users.uid', '=', 'practice.uid');
if ($thirty_days_duration == 'large30min' || $thirty_days_duration == 'less30min' || $payment_status == 'non-payment' || $payment_status == 'do_not_pay') {
// 下面相关变量的判断都需要记录在30天以内
$join->where('practice_date', '<', 'users.regdate + 30*24*60*60');
// 只保留30之内的练习记录
}
})->groupBy('users.uid')->addSelect(DB::raw('SUM(practice.practice_time) as practice_time_sum'));
// 按"近30天使用时间"查询
if ($thirty_days_duration == 'large30min') {
$users->havingRaw('SUM(practice_time) >= 30*60');
// 总练习时间>=30分钟
} elseif ($thirty_days_duration == 'less30min') {
$users->havingRaw('SUM(practice_time) < 30*60');
// 总练习时间<30分钟
}
// 按结算状态查询
if (!empty($payment_status)) {
switch ($payment_status) {
case 'non-payment':
// 未支付且满足支付的条件
$users->where('paid', 0)->where('boot_times', '>=', 2)->havingRaw('SUM(practice_time) >=' . 30 * 60);
// 练习时间 > 30min
break;
case 'paid':
$users->where('paid', 1);
break;
case 'do_not_pay':
// 不满足可支付的条件
$users->where('paid', 0)->where(function ($query) {
$query->havingRaw('SUM(practice.practice_time)<' . 30 * 60)->orWhere('boot_times', '<', 2);
});
break;
default:
# code...
break;
}
}
// 按"近30天启动次数"查询
if (!empty($thirty_days_boot_times)) {
switch ($thirty_days_boot_times) {
case 'more2times':
$users->where('boot_times', '>', 2);
break;
case 'less2times':
$users->where('boot_times', '<=', 2);
break;
default:
# code...
break;
}
}
// $users = $users->get();
// var_dump(DB::getQueryLog());
// return $users;
$users = $users->paginate(10)->appends($request->all());
return view('invite_new_users')->with('users', $users)->withInput($request->all());
}
/**
* 模糊查询用户信息
* @method queryUserInfo
* @param Request $request 用户请求携带的数据
* @return Json $users 数据传递给视图
*/
public function index(Request $request)
{
// DB::connection()->enableQueryLog();
$user_cellphone_email = $request->get('user_cellphone_email', '');
// 用户名|手机号|邮箱
$city_id = $request->get('area', '');
// 地域(城市ID)
$user_grade = $request->get('user_grade', '');
// 水平等级
$reg_time = $request->get('reg_time', '');
// 注册时间
$account_grade = $request->get('account_grade', '');
// 账号级别
$account_end_at = $request->get('account_end_at', '');
// 账号截止日期
$month_duration = $request->get('month_duration', '');
// 本月使用时长
$account_status = $request->get('account_status', '');
// 账号状态
$change_duration = $request->get('change_duration', '');
// 本月用时大幅变化
$liveness = $request->get('liveness', '');
// 活跃度
$reg_start_time = $request->get('from_time', '');
// 注册时间段 > 开始时间
$reg_end_time = $request->get('to_time', '');
// 注册时间段 > 结束时间
$field = $request->get('field', 'regdate');
// 排序字段
$order = $request->get('order', 'desc');
// 排序方式
$user_type = $request->get('user_type', '');
// 用户类型(手机号, 微信, QQ, 微博)
$appends_arr = ['field' => $field, 'order' => $order];
/**
* 按字段不为这的情况，进行SQL语句拼接
* "用户名"不为空
*/
$users = StudentUser::select('*');
if (!empty($user_cellphone_email)) {
$appends_arr = array_merge($appends_arr, ['user_cellphone_email' => $user_cellphone_email]);
$users->where(function ($query) use($user_cellphone_email) {
$query->where('nickname', 'like', "%{$user_cellphone_email}%")->orWhere('cellphone', 'like', "%{$user_cellphone_email}%")->orWhere('email', 'like', "%{$user_cellphone_email}%");
});
}
/**
* "地域"不为空
*/
if (!empty($city_id)) {
$appends_arr = array_merge($appends_arr, ['city_id' => $city_id]);
$users->where('city_id', $city_id);
}
/**
* "水平等级"不为空
*/
if (!empty($user_grade)) {
$appends_arr = array_merge($appends_arr, ['user_grade' => $user_grade]);
$users->where('user_grade', $user_grade);
}
/**
* "注册时间"不为空
*/
if (!empty($reg_time)) {
$appends_arr = array_merge($appends_arr, ['reg_time' => $reg_time]);
switch ($reg_time) {
case 'day':
$start_time = Carbon::now('Asia/ShangHai')->startOfDay();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
case 'week':
$start_time = Carbon::now('Asia/ShangHai')->subWeek();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
case 'month':
$start_time = Carbon::now('Asia/ShangHai')->subMonth();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
case 'half_year':
$start_time = Carbon::now('Asia/ShangHai')->subMonths(6);
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
case 'year':
$start_time = Carbon::now('Asia/ShangHai')->subyear();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
case 'one_more_year':
$start_time = Carbon::minValue();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
break;
default:
$start_time = Carbon::minValue();
$end_time = Carbon::maxValue();
break;
}
$users->whereBetween('regdate', [$start_time, $end_time]);
}
/**
* "账号级别"不为空
*/
if (!empty($account_grade)) {
$appends_arr = array_merge($appends_arr, ['account_grade' => $account_grade]);
switch ($account_grade) {
case 'free':
$account_grade = 0;
break;
case 'vip1':
$account_grade = 1;
break;
case 'vip2':
$account_grade = 2;
break;
case 'all':
break;
default:
$account_grade = 0;
break;
}
if ($account_grade == '0' || $account_grade == '1' || $account_grade == '2') {
$users->where('account_grade', $account_grade);
}
}
/**
* "账号截止日期"不为空
*/
if (!empty($account_end_at)) {
$appends_arr = array_merge($appends_arr, ['account_end_at' => $account_end_at]);
switch ($account_end_at) {
case 'week':
$start_time = Carbon::now('Asia/ShangHai');
$end_time = Carbon::now('Asia/ShangHai')->addWeek();
break;
case 'month':
$start_time = Carbon::now('Asia/ShangHai');
$end_time = Carbon::now('Asia/ShangHai')->addMonth();
break;
case 'two_months':
$start_time = Carbon::now('Asia/ShangHai');
$end_time = Carbon::now('Asia/ShangHai')->addMonths(2);
break;
default:
# code...
break;
}
$users->whereBetween('account_end_at', [$start_time, $end_time]);
}
/**
* "本月使用时长"不为空
*/
if (!empty($month_duration)) {
$appends_arr = array_merge($appends_arr, ['month_duration' => $month_duration]);
switch ($month_duration) {
case '1h':
$duration = 1 * 60;
// 1小时以内
break;
case '5h':
$duration = 5 * 60;
// 5小时以内
break;
case '10h':
$duration = 10 * 60;
// 10小时以内
break;
case '30h':
$duration = 30 * 60;
// 30小时以内
break;
case '60h':
$duration = 60 * 60;
// 60小时以内
break;
case '60h_more':
$duration = 60 * 60 + 1;
// 60小时以上
break;
case '0h':
$duration = 0;
// 未使用
break;
default:
$duration = -1;
//
break;
}
if ($duration > 0 && $duration < 10 * 60 + 1) {
// 60小时以内的所有
$users->whereHas('practice', function ($query) use($duration) {
$query->havingRaw("SUM(practice.practice_time) <= {$duration}")->groupBy('practice.uid');
});
} elseif ($duration == 0) {
// 未使用
$users->whereNotExists(function ($query) use($duration) {
$query->select(DB::raw(1))->from('practice')->whereRaw('practice.uid = users.uid');
});
} elseif ($duration == 60 * 60 + 1) {
// 60小时以上
$users->whereHas('practice', function ($query) use($duration) {
$query->groupBy('practice.uid')->havingRaw("SUM(practice.practice_time) > {$duration}");
});
} else {
$users->has('practice');
// 如果出现其它情况，则显示所有"使用过的用户"
}
}
/**
* "账号状态"不为空
*/
if (!empty($account_status)) {
$appends_arr = array_merge($appends_arr, ['account_status' => $account_status]);
switch ($account_status) {
case 'near_expire':
// 到期[过期]
$start_time = Carbon::now('Asia/ShangHai')->SubWeek();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
$users->whereIn('account_grade', [1, 2])->whereBetween('account_end_at', [$start_time, $end_time]);
break;
case 'lock':
//锁定
$users->where('isactive', 0);
break;
case 'normal':
// 正常[确保为“免费用户”，或“到期时间大于一周”的vip用户]
$users->where('isactive', 1)->where(function ($query) {
$query->where('account_grade', 0)->orwhere(function ($query_1) {
// 到期时间还剩大于一个月的vip1用户
$query_1->where(function ($query_2) {
$query_2->where('account_grade', 1)->where('account_end_at', '>', Carbon::now('Asia/ShangHai')->addMonth());
})->orWhere(function ($query_3) {
// 或者到期时间还剩大于一个周的vip2用户
$query_3->where('account_grade', 2)->where('account_end_at', '>', Carbon::now('Asia/ShangHai')->addWeek());
});
});
});
break;
case 'expire':
// 未续费[马上就要过期拉]
$users->where(function ($vip1) {
// 到期时间在一个月以内的vip1用户
$vip1->where('account_grade', 1)->whereBetween('account_end_at', [Carbon::now('Asia/ShangHai'), Carbon::now('Asia/ShangHai')->addMonth()]);
})->orWhere(function ($vip2) {
$vip2->where('account_grade', 2)->whereBetween('account_end_at', [Carbon::now('Asia/ShangHai'), Carbon::now('Asia/ShangHai')->addWeek()]);
});
break;
default:
# code...
break;
}
}
// 更新计算方式，去掉redis
switch ($change_duration) {
case 'up20h':
$compare_result = UserPrevMonthPracticeTimeSum::join('user_curr_month_practice_time_sum', 'user_prev_month_practice_time_sum.uid', '=', 'user_prev_month_practice_time_sum.uid')->where('user_curr_month_practice_time_sum.sum_pre_month', '>', 'user_prev_month_practice_time_sum')->get()->toArray();
break;
default:
# code...
break;
}
if (!empty($change_duration)) {
switch ($change_duration) {
case 'up20h':
// 这个月比上个月增加20个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
$query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month > user_prev_month_practice_time_sum.sum_pre_month+20*60*60');
})->get()->toArray();
break;
case 'up30h':
// 这个月比上个月增加30个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
$query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month > user_prev_month_practice_time_sum.sum_pre_month+30*60*60');
})->get()->toArray();
break;
case 'up50h':
// 这个月比上个月增加50个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
$query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month > user_prev_month_practice_time_sum.sum_pre_month+50*60*60');
})->get()->toArray();
break;
case 'down20h':
// 这个月比上个月减少20个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
return $query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month < user_prev_month_practice_time_sum.sum_pre_month-20*60*60');
})->get()->toArray();
// return $compare_result;
break;
case 'down30h':
// 这个月比上个月减少30个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
$query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month < user_prev_month_practice_time_sum.sum_pre_month-30*60*60');
})->get()->toArray();
break;
case 'down50h':
// 这个月比上个月减少50个练习小时的用户的id
$compare_result = UserPrevMonthPracticeTimeSum::select('uid')->whereHas('user_curr_month_practice_time_sum', function ($query) {
$query->whereRaw('user_curr_month_practice_time_sum.sum_pre_month < user_prev_month_practice_time_sum.sum_pre_month-50*60*60');
})->get()->toArray();
break;
default:
# code...
break;
}
$users->whereIn('uid', $compare_result);
}
/**
* "活跃度"不为空
*/
if (!empty($liveness)) {
$appends_arr = array_merge($appends_arr, ['liveness' => $liveness]);
switch ($liveness) {
case 'active_user':
$users->whereHas('practice', function ($query) {
$start_time = Carbon::now('Asia/ShangHai')->startOfMonth()->subMonths(2);
$end_time = Carbon::now('Asia/ShangHai');
$query->whereBetween('practice_date', [$start_time, $end_time])->groupBy('uid')->havingRaw('SUM(practice_time) > 30*60*60*3');
});
break;
case 'sleep_user':
$users->where('app_exist', 1)->whereHas('practice', function ($query) {
// app存在且超过一周未使用
$str = 'MAX(practice_date)<' . '\'' . Carbon::now('Asia/ShangHai')->subWeek() . '\'';
$query->havingRaw(DB::raw($str));
});
break;
case 'death_user':
$users->where('app_exist', 0)->orWhere(function ($query) {
// app不存在，或超过一周未使用
$query->whereHas('practice', function ($query_1) {
$str = 'MAX(practice_date)<' . '\'' . Carbon::now('Asia/ShangHai')->subMonth() . '\'';
$query_1->havingRaw($str);
});
});
break;
default:
# code...
break;
}
}
/**
* "注册时间段"不为空
*/
if (!empty($reg_start_time)) {
$appends_arr = array_merge($appends_arr, ['reg_start_time' => $reg_start_time, 'reg_end_time' => $reg_end_time]);
$start_time = Carbon::parse($reg_start_time)->startOfDay();
$end_time = Carbon::parse($reg_end_time)->endOfDay();
$users->whereBetween('regdate', [$start_time, $end_time]);
}
$start_time = Carbon::now('Asia/ShangHai')->subMonth();
$end_time = Carbon::now('Asia/ShangHai')->endOfDay();
$preMonth_start_time = Carbon::now('Asia/ShangHai')->subMonth(2);
$preMonth_end_time = Carbon::now('Asia/ShangHai')->subMonth()->endOfDay();
if ($user_type === '0' || !empty($user_type)) {
$users->where('channel', $user_type);
}
// $users->select('*');
$users->orderBy($field, $order);
$users = $users->paginate(10)->appends($request->all());
// return $users;
foreach ($users as $key => $v) {
if (!$v->isactive) {
$v->status = '锁定';
} else {
if ($v->account_grade == 0) {
$v->status = '正常';
} elseif ($v->account_grade == 1) {
// 如果为vip1用户
if (Carbon::now('Asia/ShangHai') < Carbon::parse($v->account_end_at) && Carbon::now('Asia/ShangHai')->addMonth() > Carbon::parse($v->account_end_at)) {
$v->status = '未续费';
} elseif (Carbon::now('Asia/ShangHai') > Carbon::parse($v->account_end_at)) {
$v->status = 'vip已过期';
} else {
$v->status = '正常';
}
} elseif ($v->account_grade == 2) {
if (Carbon::now('Asia/ShangHai') < Carbon::parse($v->account_end_at) && Carbon::now('Asia/ShangHai')->addWeek() > Carbon::parse($v->account_end_at)) {
$v->status = '未续费';
} elseif (Carbon::now('Asia/ShangHai') > Carbon::parse($v->account_end_at)) {
$v->status = 'vip已过期';
} else {
$v->status = '正常';
}
} else {
$v->status = '正常';
}
}
}
// var_dump(DB::getQueryLog());
return view('user')->with('users', $users);
}
