我如何运行laravel雄辩的查询以检索每天,每周,每月和Yeaily的收入报告。
表:收入
id |频道|金额|create_at
1 |mabble |3400 |9/10/12
2 |黄金|3400 |8/10/12
3 |mabble |4500 |07/10/12
4 |银|3200 |22/09/12
5 |银|3400 |9/10/12
6 |银|790 |22/09/12
7 |黄金|1000 |9/08/12
8 |mabble |9000 |22/09/12
预期结果:以下是一个示例结果不准确
日期|频道|每周收入|每月收入|年收入|总收入
2017年2月2日|黄金|200 |5400 |3244 |90222
日期|银|600 |4300 |983 |10000
我想总结上面解释的金额(收入(,也许是按频道和创建_at(日期(分组的。如何按使用create_at之间的日期编写雄辩的查询和过滤。它应该在控制器中完成。
如果频道有限(例如5-20(,请使用以下代码:
<?php
$items = Revenue::groupBy('channel')->get(['channel']);
$re = [];
foreach ($items as $key => $value) {
$re['daily'][$value] = Revenue::where('channel',$value)->whereDate('created_at',date('Y-m-d'))->sum('amount');
$re['weekly'][$value] = Revenue::where('channel',$value)->whereBetween('date', [
Carbon::parse('last monday')->startOfDay(),
Carbon::parse('next friday')->endOfDay(),
])->sum('amount');
$re['monthly'][$value] = Revenue::where('channel',$value)->whereMonth('created_at',date('m'))->sum('amount');
$re['yearly'][$value] = Revenue::where('channel',$value)->whereYear('created_at',date('Y'))->sum('amount');
}
var_dump($re);
在此代码中,我们首先将通道分开,然后使用它们来计算所需的结果。顺便说一句,代码将进行测试。