query($query) ->addColumn('username', function ($row){ return ''.$row->username.''; })->rawColumns(['username']); } /** * Get the query object to be processed by dataTables. * * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder|\Illuminate\Support\Collection */ public function query() { $username = Auth::user()->username; $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") ->join('companies','users.company_id','companies.id') ->where('users.created_by',$username) ->whereRaw("(users.role_id='{$roleId}')") ->whereDate('submit_date',$now) ->groupBy(DB::Raw('user_id,users.full_name,name,users.username,logs.user_id')) ->select(DB::Raw('users.full_name as full_name, users.username as username, companies.name as name, 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 \Yajra\Datatables\Html\Builder */ public function html() { return $this->builder() ->columns($this->getColumns()) ->ajax('') ->parameters([ 'dom' => 'Bfrtip', 'buttons' => ['csv', 'excel', 'pdf', 'print', 'reload'], 'order' => [[1, 'desc']] ]); } /** * Get columns. * * @return array */ protected function getColumns() { return [ 'full_name' => ['title'=>'Name'], 'name' => ['title'=>'Organisation'], 'username', 'sent', 'delivrd', 'undeliv', 'rejectd', 'expired', 'ack' ]; } /** * Get filename for export. * * @return string */ protected function filename() { return 'resellerdaily_' . time(); } }