collection($query)
->addColumn('is_director', function($query) {
if ($query->is_director ==1) {
return 'Yes';
}else {
return 'No';
}
})
// ->filterColumn('division', function ($query, $keyword){
// $correctt = Department::query()
// ->where('division',"like", "%{$keyword}%")->first();
//
// if ($correctt) {
// // return null;
// $correct = UsersDb::query()
// ->where('dept',$correctt->name)->first();
// return $query->where('userid',$correct->id) ?? '';
// //return $correctt !== null ? $correctt->name : null;
// }else {
// return null;
// }
// })
// ->filterColumn('division', function ($query, $keyword){
// $correctt = Department::query()
// ->where('division',"like", "%{$keyword}%")->first();
//
// if ($correctt) {
// $correct = UsersDb::query()
// ->where('dept',$correctt->name)->first();
// return $query->where('userid',$correct->id)?? '';
// }else {
// return null;
// }
// })
//
// ->addColumn('coi_status', function($query) {
// if($query->coi_status() !== null){
// if ($query->coi_status['coi_stat']=='Agree') {
// return '';
// }else{
// return '';
// }
//
// }else{
// return null;
// }
// })
// ->addColumn('status', function($query) {
// if ($query->status !==null) {
// if ($query->status=='Pending') {
// return '';
// }else if ($query->status=='Completed'){
// return '';
// }else if ($query->status=='Updated'){
// return '';
// }
// }else{
// return null;
// }
//
//
// })
// ->addColumn('action', function($query){
//
// return '
//
//
// ';
//
//
// })
->rawColumns([]);
}
/**
* Get query source of dataTable.
*
* @param \App\App\AnnexDatatable $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query()
{
$current_year = Carbon::now()->year;
// $query = CoiUsers::query()->where('coi_status','Agree')->where('status','Pending')->whereNull('deleted_at');
// $query = DB::select("select distinct(email) from coi_users where YEAR(created_at) >= YEAR(CURDATE())
// and deleted_at is null AND status = 'Pending' OR status is not null");
// $query = CoiUsers::whereYear('created_at', '>=', $current_year)
// ->whereNotNull('coi_status')->whereNull('deleted_at')
// ->where('status', '=', 'Pending')->orWhereNull('status')->groupBy('email');
// $query = DB::table('coi_users')
// ->whereYear('created_at', '>=', $current_year)
// ->where(function ($query) {
// $query->where('status', '=', 'Pending')
// ->orWhereNull('status');
// })
// ->whereNotNull('coi_status')
// ->whereNull('deleted_at')
// // ->select(['id', 'email', 'name', 'phone', 'dept', 'is_director', 'division'])
// ->get()->unique('email');
$query = DB::select("SELECT * FROM `coi_users` where dept IS NOT NULL AND deleted_at IS NULL AND YEAR(created_at) = $current_year AND (coi_status = 'Agree' AND status = 'Pending') OR (coi_status IS NULL AND status = 'Pending')");
return collect($query);
// 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('annexdatatable-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')->title('Name'),
Column::make('phone')->title('Phone Number'),
Column::make('email')->title('Email'),
Column::make('division')->title('Division'),
Column::make('dept')->title('Department'),
Column::make('is_director')->title('Is He/She a Director'),
// Column::computed('action')
// ->exportable(false)
// ->printable(false)
// ->width(60)
// ->addClass('text-center'),
];
}
/**
* Get filename for export.
*
* @return string
*/
protected function filename()
{
return 'Annex_' . date('YmdHis');
}
}