eloquent()
->addColumn('last', function ($query){
return date('l M d, Y', strtotime($query->last));
})
->addColumn('issues', function($query) {
if ($query->issues >0) {
return '';
}else {
return '';
}
})
->addColumn('action', function($query){
return '
';
}) ->rawColumns(['issues', 'action','status']);}
//
/**
* Get query source of dataTable.
*
* @param \App\App\UsersDataTable $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query()
{
$query = DB::select("select distinct(coi_users.id) as id,coi_users.name as name,coi_users.dept as dept,coi_users.level as level,coi_users.division as divv,(select count(helpdesk.id) from helpdesk where adminid is null and userid = coi_users.id) as issues,(select updated_at from helpdesk where userid = coi_users.id order by id desc limit 1) as last from coi_users,helpdesk where coi_users.id= helpdesk.userid group by name,dept,level,divv,last,id
");
return $this->applyScopes($query);
}
/**
* Optional method if you want to use html builder.
*
* @return \Yajra\DataTables\Html\Builder
*/
public function html()
{
return $this->builder()
->setTableId('usersdatatable-table')
->columns($this->getColumns())
->minifiedAjax()
// ->dom('Bfrtip')
->orderBy(0)
// ->buttons(
// Button::make('export'),
// Button::make('print'),
// Button::make('reset'),
// Button::make('reload')
// )
;
}
/**
* Get columns.
*
* @return array
*/
protected function getColumns()
{
return [
Column::make('id')->visible(false)->exportable(false)
->printable(false),
Column::make('name'),
Column::make('dept')->title('Department'),
Column::make('divv')->title('Division'),
Column::make('level'),
Column::make('issues'),
Column::make('last')->title('Date Last Engaged'),
Column::computed('action')
->exportable(false)
->printable(false)
->width(60)
->addClass('text-center'),
];
}
/**
* Get filename for export.
*
* @return string
*/
protected function filename()
{
return 'Users_' . date('YmdHis');
}
}