datatables ->queryBuilder($this->query()) ->addColumn('date', function($row) { return "$row->date"; }) ->addColumn('count', function($row) { return number_format($row->count); }) ->addColumn('total', function($row) { $user = Auth::user(); $cnf = \App\Setting::where('user_id', $user->id)->first(); $expenses = 0.00; if ($cnf != null) $expenses = $cnf->water + $cnf->electricity + $cnf->nhil + $cnf->vat + $cnf->others; return 'GHC ' . number_format(($row->mtn + $row->airtel + $row->tigo + $row->vodafone + $expenses), 3); }) ->make(true); } /** * Get the query object to be processed by dataTables. * * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder|\Illuminate\Support\Collection */ public function query() { $start = $this->request()->get('from_date', 0); $end = $this->request()->get('to_date', 0); if ($start == null || $end == null) { $start = new Carbon('first day of this month'); $end = new Carbon('last day of this month'); } else { $start = Carbon::parse($start)->toDateString(); $end = Carbon::parse($end)->addDay(1)->toDateString(); } $query = DB::table('sms_log') ->select(DB::raw("date(submit_date) as date, sum(sms_count) as count, sum(if(network = 'mtn', sms_count * 0.013, 0)) as mtn, sum(if(network = 'airtel', (sms_count * 0.014), 0)) as airtel, sum(if(network = 'tigo', (sms_count * 0.00833), 0)) as tigo, sum(if(network = 'vodafone', (sms_count * 0.010), 0)) as vodafone")) ->whereBetween('submit_date', [$start, $end]) ->groupBy(DB::raw('DATE(sms_log.submit_date)')) ->orderBy('date', 'desc'); 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' => [[0, 'desc']] ]); } /** * Get columns. * * @return array */ protected function getColumns() { return [ 'date' => ['title' => 'Date'], 'count' => ['title' => 'Total Traffic'], 'total' ]; } /** * Get filename for export. * * @return string */ protected function filename() { return 'operational_costs_' . time(); } }