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')
    ]);

这里写图片描述

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

效果图:

这里写图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

SHUIPING_YANG

你的鼓励是我创作的最大动力。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值