query($query) ->editColumn('user', function ($row){ return ''.$row->username.''; })->rawColumns(['user']); } /** * Get the query object to be processed by dataTables. * * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder|Collection */ public function query() { $now = Carbon::parse(now())->format('Y-m-d'); $roleId = Role::where('name', 'user')->firstOrFail()->id; $query = DB::table('logs') ->join('users', 'users.id', '=', "logs.user_id") ->whereRaw("(users.role_id='{$roleId}')") ->whereDate('submit_date',$now) ->groupBy(DB::Raw('user_id,users.full_name,users.username,logs.user_id')) ->select(DB::Raw('users.full_name as full_name, users.username as username, logs.user_id as user_id, count(*) as sent, SUM(IF(logs.`status`="DELIVRD", 1,0)) AS delivrd, SUM(IF(logs.`status`="UNDELIV", 1,0)) AS undeliv, SUM(IF(logs.`status`="REJECTD", 1,0)) AS rejectd, SUM(IF(logs.`status`="EXPIRED", 1,0)) AS expired, SUM(IF(logs.`status`="ACK/" or logs.`status`="ACCEPTD", 1,0)) AS ack')); return $this->applyScopes($query); } /** * Optional method if you want to use html builder. * * @return Builder */ public function html() { return $this->builder() ->columns($this->getColumns()) ->ajax('') ->parameters([ 'dom' => 'Bfrtip', 'buttons' => ['csv', 'excel', 'pdf', 'print', 'reload'], 'order' => [[0, 'asc']] ]); } /** * Get columns. * * @return array */ protected function getColumns() { return [ 'full_name' => ['title'=>'Name'], 'username', 'sent', 'delivrd', 'undeliv', 'rejectd', 'expired', 'ack' ]; } /** * Get filename for export. * * @return string */ protected function filename() { return 'admindaily_' . time(); } }