<?php

namespace App\DataTables;

use App\Network;
use App\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Services\DataTable;

class ResellerInvoiceDataTable extends DataTable
{
    protected $inputs;
    protected $client_id;

    public function dataTable($query)
    {
        return datatables()
            ->query($query);
//            ->orderColumn('submit_date', 'submit_date $1');


    }

    public function forInputs($inputs, $client_id) {
        $this->inputs = $inputs;
        $this->client_id = $client_id;
        return $this;
    }

    /**
     * Get the query object to be processed by dataTables.
     *
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder|\Illuminate\Support\Collection
     */
    public function query()
    {
        $user = User::where([
            ['created_by', '=', Auth::user()->username]
        ])->findOrFail($this->client_id);

        if(isset($this->inputs['operator']) && isset($this->inputs['from_date']) && isset($this->inputs['to_date'])) {
            $from_date = Carbon::parse(Carbon::parse($this->inputs['from_date'])->toDateString());
            $to_date = Carbon::parse(Carbon::parse($this->inputs['to_date'])->addDay()->toDateString());
            $to_date = $to_date->subSecond();

            $operators = $this->inputs['operator'];
            $operators = explode(',', $operators);
            $opsStr = ' (';
            foreach ($operators as $operator) {
                $nice_name = Network::find($operator)->nice_name;
                $opsStr .= "'".$nice_name."',";
            }
            if(count($operators)>0) {
                $opsStr = substr($opsStr, 0, -1);
            }
            $opsStr .= ') ';


            if ($from_date->gt($to_date)) {
                //TODO: return error
            }
            $query = DB::table('logs')
                    ->join('credits', 'credits.user_id', '=', "logs.user_id")
                    ->whereRaw("(logs.user_id='{$user->id}' and (submit_date between '$from_date' and '$to_date' ) and network IN $opsStr)")
                    ->groupBy(DB::Raw('network,credits.price'))
                    ->select(DB::Raw('network, \'Ghana\' as country, COUNT(*) as no_of_message,
                    SUM(sms_count) as message_parts, credits.price as cost_per_sms, SUM(sms_count * credits.price) as total_price'))
            ;
        }
        else
        {
            $query = DB::table('logs')->where('id', '0');
        }

        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 [
            'country' => ['searchable'=>false, 'orderable'=>false],
            'network',
            'message_parts' => ['searchable'=>false, 'orderable'=>false],
            'no_of_message' => ['searchable'=>false, 'orderable'=>false],
            'cost_per_sms' => ['searchable'=>false, 'orderable'=>false],
            'total_price' => ['searchable'=>false, 'orderable'=>false]
        ];
    }

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