<?php

namespace App\DataTables;

use App\User;
use Illuminate\Support\Facades\DB;
use Yajra\Datatables\Services\DataTable;

class SalesDataTable extends DataTable
{
    /**
     * Display ajax response.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function ajax()
    {
        return $this->datatables
            ->queryBuilder($this->query())
            ->addColumn('action', 'path.to.action.view')
            ->addColumn('total', function ($row) {
                return round($row->apitotal + $row->guitotal, 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()
    {
        $query = DB::table('sms_log')->
        select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, 
        sum(if(originated = 'mtnuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'mtnuser', sms_count * 0.024, 0)) as guitotal"))
            ->groupBy(DB::raw('date(submit_date), originated'))->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',
            'count',
            'user',
            'total'
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'sales_datatables_' . time();
    }
}