/**
* Returns a user friendly string date format for date string or Carbon date object, with default
* format or given - BUT also returns "Never" if Carbon date is max/min - or null.
* @param mixed $date - a date parsable by Carbon, including a Carbon Date object
* @param string $format
*/
function friendlyCarbonDate($date = null, $format = 'M j, Y') {
// American style m/d/y: 'n/j/y'
if (!$date) return "Never/None";
$date = new Carbon($date);
$min = Carbon::minValue();
$max = Carbon::maxValue();
if ($max->eq($date) || $min->eq($date)) return "Never/None";
return $date->format($format);
}
/**
* Constructor to initialize attributes
* @param $from
* @param $to
*/
public function __construct($from, $to)
{
$this->from = $from == null ? Carbon::minValue() : new Carbon($from);
$this->to = $to == null ? Carbon::maxValue() : new Carbon($to);
$this->from = $this->from->firstOfMonth();
$this->to = $this->to->lastOfMonth();
$this->sales = $this->sales();
$this->opening_inventory = $this->openingInventory();
$this->closing_inventory = $this->closingInventory();
$this->purchases = $this->purchases();
$this->profit = $this->profit();
$this->total_debtors = $this->totalDebtors();
$this->total_creditors = $this->totalCreditors();
$this->cash = $this->cash();
$this->bank = $this->bank();
}
/**
* Stash\Item constructor, runs on object creation
*
* @param mixed $data Item data
* @param int $minutes Time in minutes until item expires
*/
public function __construct($data, $minutes = 0)
{
$this->data = $data;
$this->expires = $minutes == 0 ? Carbon::maxValue() : Carbon::now()->addMinutes($minutes);
}
public function testMaxValue()
{
$this->assertGreaterThanOrEqual(2147483647, Carbon::maxValue()->getTimestamp());
}
public function scopeDateRangeFrom($query, $from)
{
return $this->scopeDateRange($query, $from, Carbon::maxValue());
}
public function rptWork($code)
{
$report = $this->getReport($code);
if (!is_object($report)) {
$this->showError();
}
$clients = $this->getClients($report->getClients());
if ($clients === false) {
$this->showError();
}
$projects = $this->getProjects($clients);
$stagesAll = $this->generateProjectsStages($projects);
$clientsAll = $this->generateClients($clients);
$clientsOrderAll = $this->generateProjectsClientsOrder($projects);
$lastStagesAll = $this->generaProjectsLastStages($projects);
$finishedClientsAll = $this->generateProjectsFinishedClients($projects, $clientsAll, $lastStagesAll);
$eventsAll = $this->generateProjectsEvents($projects, $clientsAll);
$balanceAll = $this->countBalance($projects);
if ($report->getPeriod_from() != "0000-00-00") {
$param_from = \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", $report->getPeriod_from() . " 00:00:00");
} else {
$param_from = \Carbon\Carbon::minValue();
}
if ($report->getPeriod_to() != "0000-00-00") {
$param_to = \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", $report->getPeriod_to() . " 23:59:59");
} else {
$param_to = \Carbon\Carbon::maxValue();
}
foreach ($eventsAll as $key => $projectsF) {
//PROJECTS
foreach ($projectsF as $key1 => $clientsF) {
//CLIENTS
foreach ($clientsF as $key2 => $eventF) {
//EVENTS
if (is_null($eventF->getR_date())) {
$eventF->show = false;
continue;
}
if ($eventF->getState() != 2) {
$eventF->show = false;
continue;
}
$r_dateObj = \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", $eventF->getR_date());
if ($r_dateObj->gte($param_to) || $r_dateObj->lte($param_from)) {
$eventF->show = false;
}
}
}
}
$balanceSummary = array("income" => 0, "cost" => 0, "profit" => 0, "wasted_time" => 0);
$balanceProject = array("income" => 0, "cost" => 0, "profit" => 0, "wasted_time" => 0);
$balanceClient = array("income" => 0, "cost" => 0, "profit" => 0, "wasted_time" => 0);
foreach ($eventsAll as $keyP => $projectsF) {
//PROJECTS
$Pincome = 0;
$Pcost = 0;
$Pprofit = 0;
$Pwasted_time = 0;
foreach ($projectsF as $key => $clientsF) {
//CLIENTS
$income = 0;
$cost = 0;
$profit = 0;
$wasted_time = 0;
foreach ($clientsF as $eventF) {
//EVENTS
if (!$eventF->show) {
continue;
}
$income += !is_null($eventF->getIncome()) ? $eventF->getIncome() : 0;
$cost += !is_null($eventF->getCost()) ? $eventF->getCost() : 0;
$profit += !is_null($eventF->getProfit()) ? $eventF->getProfit() : 0;
$wasted_time += !is_null($eventF->getWasted_time()) ? $eventF->getWasted_time() : 0;
$balanceSummary["income"] += !is_null($eventF->getIncome()) ? $eventF->getIncome() : 0;
$balanceSummary["cost"] += !is_null($eventF->getCost()) ? $eventF->getCost() : 0;
$balanceSummary["profit"] += !is_null($eventF->getProfit()) ? $eventF->getProfit() : 0;
$balanceSummary["wasted_time"] += !is_null($eventF->getWasted_time()) ? $eventF->getWasted_time() : 0;
}
$balanceClient[$key] = array("income" => $income, "cost" => $cost, "profit" => $profit, "wasted_time" => $wasted_time);
$Pincome += $income;
$Pcost += $cost;
$Pprofit += $profit;
$Pwasted_time += $wasted_time;
}
$balanceProject[$keyP] = array("income" => $Pincome, "cost" => $Pcost, "profit" => $Pprofit, "wasted_time" => $Pwasted_time);
}
$clientsCount = 0;
foreach ($clientsAll as $cl) {
$clientsCount += count($cl);
}
$rurls = array();
$rurls["refresh"] = new SimpleUrl();
$aurls = $this->actionUrls;
$texts = $this->texts;
include "./view/report/reportView.php";
}
private function number()
{
return rand(Carbon::now()->timestamp, Carbon::maxValue()->timestamp);
}
/**
* @return array
*/
private static function fileConfig() : array
{
return ['visibility' => Filesystem::VISIBILITY_PUBLIC, 'CacheControl' => 'max-age=315360000, public', 'Expires' => Carbon::maxValue()->format(DATE_RFC850)];
}
/**
* 模糊查询用户信息
* @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);
}
