效果实现：
use Illuminate\Http\Request; use Illuminate\Pagination\LengthAwarePaginator; use Illuminate\Pagination\Paginator; /** * 注册途径来源分布 * @param Request $request * @return $this */ public function RegisterSourceView(Request $request){ $qq = ServiceUtils::REGISTER_PLATFORM_QQ; $wx = ServiceUtils::REGISTER_PLATFORM_WX; $sina = ServiceUtils::REGISTER_PLATFORM_SINA; $phone = ServiceUtils::REGISTER_PLATFORM_SMS; $pageSize=ConstantUtils::PAGE_SIZE; $data['start_time'] = $request->input('start_time', date('Y-m-d 00:00:00',strtotime("-15 day"))); $data['end_time']= $request->input('end_time', date('Y-m-d 23:59:59')); $start_month = date('Ym',strtotime($data['start_time'])); $end_month = date('Ym',strtotime($data['end_time'])); $table_prefix = 'd_user_register'; //获得查询表集合 $select_set = []; for($i=$start_month;$i<=$end_month;$i++){ $select_set[] = intval($i); } //获得已有表集合 $table_prefix_length = strlen($table_prefix); $tables = DB::connection('log')->select("show tables like 'd_user_register%'"); $register_tables_set = []; foreach($tables as $key=>$value){ $value = (array)$value; $table = $value['Tables_in_dingdlog (d_user_register%)']; $res = (int)substr($table,$table_prefix_length); array_push($register_tables_set,$res); } $register_tables_set = array_filter($register_tables_set); sort($register_tables_set); //获取最终查询表交集 $register_tables = array_values(array_intersect($register_tables_set,$select_set)); $register_data = []; //循环查询 if(count($register_tables)<=1){ //一个月 $register_data = DB::connection('log')->table($table_prefix.$register_tables[0])->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone ")) ->where('time','>=',$data['start_time']) ->where('time','<=',$data['end_time']) ->groupBy('day') ->get(); }else{ //多个月 //确定开始和结束时间 $first_table = current($register_tables); $end_table = end($register_tables); $first = date('Y-m-d 00:00:00',strtotime($first_table.'01')); $end = date('Y-m-d 23:59:59',strtotime($end_table."01 +1month -1day")); foreach($register_tables as $k=>$v){ if($v==$first_table){ if(strtotime($data['start_time'])>strtotime($first)){ $list = DB::connection('log')->table($table_prefix.$v)->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone "))->where('time','>=',$data['start_time'])->groupBy('day')->get(); }else{ $list = DB::connection('log')->table($table_prefix.$v)->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone "))->groupBy('day')->get(); } }elseif($v==$end_table) { if(strtotime($data['end_time'])<strtotime($end)){ $list = DB::connection('log')->table($table_prefix.$v)->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone "))->where('time','<=',$data['end_time'])->groupBy('day')->get(); }else{ $list = DB::connection('log')->table($table_prefix.$v)->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone "))->groupBy('day')->get(); } }else{ $list = DB::connection('log')->table($table_prefix.$v)->select(DB::raw("DATE_FORMAT(time,'%Y%m%d') as day,sum(case authorizationType when ".$wx." then 1 else 0 end) as wx,sum(case authorizationType when ".$qq." then 1 else 0 end) as qq,sum(case authorizationType when ".$sina." then 1 else 0 end) as xlwb,sum(case authorizationType when ".$phone." then 1 else 0 end) as phone "))->groupBy('day')->get(); } array_push($register_data,$list); } } $result = []; if(count($register_tables)<=1){ $result = $register_data; }else{ foreach($register_data as $k=>$v){ foreach($v as $j){ array_push($result,$j); } } } //倒序显示 rsort($result); //分页显示 $perPage = $pageSize; if ($request->has('page')) { $current_page = $request->input('page'); $current_page = $current_page <= 0 ? 1 :$current_page; } else { $current_page = 1; } $item = array_slice($result, ($current_page-1)*$perPage, $perPage); //注释1 $total = count($result); $result =new LengthAwarePaginator($item, $total, $perPage, $current_page, [ 'path' => Paginator::resolveCurrentPath(), //注释2 'pageName' => 'page', ]); return view('chart/registerSourceView') ->with('data', $data) ->with('register_Source_Data',$result); } <!DOCTYPE html> <html lang="zh-cn"> @include('layouts.head') <body> <div class="container-fluid"> <div class="panel panel-default"> <div class="panel-heading"> 邀请注册 </div> <div class="panel-body"> <form action="{{action('Home\ChartController@inviteRegisterView')}}" method="post" role="form"> <div class="row"> <div class="col-md-6 form-inline"> <div class="form-group form-inline"> <label for="">开始时间</label> <input type="text" name="start_time" class="form-control form_date" value="{{$data['start_time']}}" readonly placeholder="请输入id"/> </div> <div class="form-group form-inline"> <label for="">结束时间</label> <input type="text" name="end_time" class="form-control form_date" value="{{$data['end_time']}}" readonly placeholder="请输入id"/> </div> </div> <div class="col-md-3"> <label for=""> </label> <div class="form-group"> <input type="submit" class="btn btn-sm btn-primary" value="查询"/> {{--<input type="submit" class="btn btn-sm btn-primary" value="导出"/>--}} </div> </div> </div> </form> </div> </div> <table class="table table-responsive table-bordered"> <thead> <th>日期</th> <th>新增总人数</th> <th>邀请注册人数</th> </thead> <tbody> @if($result) @foreach($result as $key=>$value) <tr> <td>{{$value->day}}</td> <td>{{$value->register_total}}</td> <td>{{$value->register_total-$value->self}}</td> </tr> @endforeach @else <tr> <td colspan="4">暂无数据</td> </tr> @endif </tbody> </table> <nav class="pull-right"> @if($result) {!! $result->appends($data)->render() !!} @endif </nav> </div> <script type="text/javascript"> $('.form_date').datetimepicker({ language: 'zh-CN', /*加载日历语言包，可自定义*/ weekStart: 1, /*星期*/ todayBtn: 0, /*当天*/ autoclose: true,//选中之后自动隐藏日期选择框 todayHighlight: 1, /*今天高亮显示*/ startView: 2, /*4年3月2日1小时*/ minView: 2, /*0分1小时2天*/ format: 'yyyy-mm-dd', forceParse: 0, showMeridian:true }); </script> </body> </html>
