Laravel统计一段时间间隔的数据方法

发布:smiling　来源: PHP粉丝网　添加日期：2021-12-28 20:18:25　浏览: 评论：0　

今天小编就为大家分享一篇Laravel统计一段时间间隔的数据方法，具有很好的参考价值，希望对大家有所帮助，一起跟随小编过来看看吧。

获取七天以前到现在的数据：

$days = Input::get('days', 7);

$range = \Carbon\Carbon::now()->subDays($days);

$stats = User::where('created_at', '>=', $range)

->groupBy('date')

->orderBy('date', 'DESC')

->get([

DB::raw('Date(created_at) as date'),

DB::raw('COUNT(*) as value')

]);

Laravel统计时间间隔

SELECT

sum(case when `EmailSource`='FM' then 1 else 0 end) as FM_Statistic,

sum(case when `EmailSource`='UOC' then 1 else 0 end) as UOC_Statistic,

sum(case when `EmailSource`='OC' then 1 else 0 end) as OC_Statistic,

DATE_FORMAT(Date,'%Y-%m-%d') AS `DateTime`

FROM `user_performance`

WHERE Email != '' AND Email != 'TOTAL'

AND (DATE_FORMAT(Date,'%Y-%m-%d') >= DATE_FORMAT('2011-02-5','%Y-%m-%d'))

AND (DATE_FORMAT(Date,'%Y-%m-%d') <= DATE_FORMAT('2011-03-07','%Y-%m-%d'))

GROUP BY `Date`

public function getNumber()

{

$data = [];

$customers = Customer::all(['id', 'customer_type', 'created_at']);

#今天数据

$data['customer_today'] = Customer::where('customer_type', 1)->where('created_at', Carbon::today())->count();

$data['teacher_today'] = Customer::where('customer_type', 2)->where('created_at', Carbon::today())->count();

#昨天数据

$data['customer_yesterday'] = Customer::where('customer_type', 1)->where('created_at', Carbon::yesterday())->count();

$data['teacher_yesterday'] = Customer::where('customer_type', 2)->where('created_at', Carbon::yesterday())->count();

$data['today'] = $data['customer_today'] + $data['teacher_today'];

$data['yesterday'] = $data['customer_yesterday'] + $data['teacher_yesterday'];

// 本周数据

$this_week = [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()];

$data['customer_this_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $this_week)->count();

$data['teacher_this_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $this_week)->count();

// 上周数据

$last_week = [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()];

$data['customer_last_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $last_week)->count();

$data['teacher_last_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $last_week)->count();

$data['this_week'] = $data['customer_this_week'] + $data['teacher_this_week'];

$data['last_week'] = $data['customer_last_week'] + $data['teacher_last_week'];

// 本月数据

$data['customer_this_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->month)->count();

$data['teacher_this_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->month)->count();

// 上月数据

$data['customer_last_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();

$data['teacher_last_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();

$data['this_month'] = $data['customer_this_month'] + $data['teacher_this_month'];

$data['last_month'] = $data['customer_last_month'] + $data['teacher_last_month'];

// 本年数据

$data['customer_this_year'] = Customer::where('customer_type', 1)->whereYear('created_at', Carbon::now()->year)->count();

$data['teacher_this_year'] = Customer::where('customer_type', 2)->whereYear('created_at', Carbon::now()->year)->count();

$data['today_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::today())

  ->groupBy('customer_id')

  ->orderBy('customer_id')

  ->count();

$data['yesterday_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::yesterday())

  ->groupBy('customer_id')

  ->orderBy('customer_id')

  ->count();

$data['this_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->month)

  ->groupBy('customer_id')

  ->orderBy('customer_id')

  ->count();

$data['last_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->subMonth()->month)

  ->groupBy('customer_id')

  ->orderBy('customer_id')

  ->count();

return $data;

}

public function numberCount()

{

$days = request('days', 7);

$range = Carbon::today()->subDays($days);

$day_stats = Customer::where('created_at', '>=', $range)

  ->groupBy('date')

  ->orderBy('date', 'DESC')

  ->get([

   \DB::raw('DATE_FORMAT(created_at,\'%Y-%m-%d\') as date,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer,SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),

  ])

  ->toJSON();

$week_stats = Customer::groupBy('week')

  ->orderBy('week', 'DESC')

  ->get([

   \DB::raw('DATE_FORMAT(created_at,\'%Y W%u\') as week,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer, SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),

  ])

  ->toJSON();

// dd($week_stats);

// \DB::enableQueryLog();

$month_stats = Customer::groupBy('month')

  ->orderBy('month', 'DESC')

  ->get([

   \DB::raw('DATE_FORMAT(created_at,\'%Y-%m\') as month,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer,SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),

  ])

  ->toJSON();

// dd(\DB::getQueryLog());

// dd($week_stats, $month_stats);

$data = $this->getNumber();

// dd($day_stats, $week_stats, $month_stats, $data);

return view('admin.numberCount', compact('day_stats', 'week_stats', 'month_stats', 'data'));

}