<?php

namespace App\Http\Controllers\Reseller;

use App\Country;
use App\DataTables\CampaignDatatable;
use App\DataTables\ChimpReportDataTable;
use App\DataTables\Report2wayDataTable;
use App\DataTables\ReportDailyReportUserDataTable;
use App\DataTables\ReportingDataTable;
use App\DataTables\ResellerClientEmailReportDataTable;
use App\DataTables\ResellerClientRegistrationDataTable;
use App\DataTables\ResellerClientSocialReportDataTable;
use App\DataTables\ResellerClientUssdReportDataTable;
use App\DataTables\ResellerDailyReportDTDataTable;
use App\DataTables\ResellerInvoiceDataTable;
use App\DataTables\ResellerOperationalCostDataTable;
use App\DataTables\ResellerReportingDataTable;
use App\DataTables\ResellerSmsClientDataTable;
use App\DataTables\ResellerSpecificReportDataTable;
use App\DataTables\ResellerTopClientsDataTable;
use App\DataTables\SmsLog24DataTable;
use App\DataTables\SpecificDataTable;
use App\DataTables\SpecifyPeriodDataTable;
use App\DataTables\ResellerDeptStatusReportDataTable;
use App\Http\Controllers\Controller;
use App\Report;
use App\Role;
use App\Setting;
use App\Traits\ChimpTrait;
use App\User;
use App\UserEmailCampaign;
use App\UserSenderId;
use Carbon\Carbon;
use Illuminate\Http\Request;

use App\Http\Requests;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redirect;
use Illuminate\Support\Facades\Session;

class ReportController extends Controller
{
    public function get_sms_report(ResellerSmsClientDataTable $sms)
    {
        return $sms->render('reseller.reports.sms_clients');
//		return $dataTable->render('reseller.reports.daily_for_user', [
//            'total_sms_delivered' => $total_sms_delivered,
//            'total_sms_undelivered' => $total_sms_undelivered,
//            'total_sms_expired' => $total_sms_expired,
//            'total_sms_ack' => $total_sms_ack,
//            'username' => $username
//        ]);
    }

    public function get_sms_client_report($client)
    {
        return view('reseller.reports.sms_client', ['client_id' => $client]);
    }

    public function client_social_report(ResellerClientSocialReportDataTable $email)
    {
        return $email->render('reseller.reports.social.index');
    }

    public function client_ussd_report(ResellerClientUSSDReportDataTable $email)
    {
        return $email->render('reseller.reports.ussd.index');
    }

    public function get_sms_report_api($filter = null)
    {
        $filter     = $filter != null ? strtolower($filter) : 'today';
        $conn       = DB::connection('kannel');
        $start      = null;
        $today      = Carbon::today();

        switch ($filter)
        {
            case 'today':
                $start = strtotime($today);
                break;
            case 'week':
                $start = strtotime($today->subWeek());
                break;
            case 'month':
                $start = strtotime($today->subMonth());
                break;
            default:
                $start = strtotime($today);
        }

        $data = [];

        if (in_array(strtolower($filter), ['today', 'week', 'month']))
        {
            $users = $conn->select("select distinct(session_esme) as name from MT where msg_timestamp > $start");

            foreach ($users as $user)
            {
                $name = $user->name;

                $cs = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start");
                $cd = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%DELIVRD%'");
                $cu = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%UNDELIV%'");
                $cr = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%REJECTD%'");
                $ce = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%EXPIRED%'");
                $ca = $cs[0]->t - ($cd[0]->t + $cu[0]->t + $cr[0]->t + $ce[0]->t);

                $data[] = [strtoupper($name), $cs[0]->t, $cd[0]->t, $cu[0]->t, $cr[0]->t, $ce[0]->t, $ca];
            }
        }

        $response = ['data' => $data];
        return response($response)->header('Content-Type', 'text/json');
    }


    public function get_sms_report_api_2($filter = null)
    {
        $filter     = $filter != null ? strtolower($filter) : 'today';
        $conn       = DB::connection('kannel');
        $start      = null;
        $today      = Carbon::today();

        switch ($filter)
        {
            case 'today':
                $start = strtotime($today);
                break;
            case 'week':
                $start = strtotime($today->subWeek());
                break;
            case 'month':
                $start = strtotime($today->subMonth());
                break;
            default:
                $start = strtotime($today);
        }

        $data = [];

        if (in_array(strtolower($filter), ['today', 'week', 'month']))
        {
            $users = $conn->select("select distinct(session_esme) as name from MT where msg_timestamp > $start");

            foreach ($users as $user)
            {
                $name = $user->name;

                $cs = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start");
                $cd = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%DELIVRD%'");
                $cu = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%UNDELIV%'");
                $cr = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%REJECTD%'");
                $ce = $conn->select("select count(state) as t from MT where session_esme = '$name' and msg_timestamp > $start and state like '%EXPIRED%'");
                $ca = $cs[0]->t - ($cd[0]->t + $cu[0]->t + $cr[0]->t + $ce[0]->t);

                $data[] = [strtoupper($name), $cs[0]->t, $cd[0]->t, $cu[0]->t, $cr[0]->t, $ce[0]->t, $ca];
            }
        }

        $response = ['data' => $data];
        return response($response)->header('Content-Type', 'text/json');
    }

    public function invoice(Request $request, ResellerInvoiceDataTable $dataTable, $id) {
        $user = User::where([
            ['created_by', '=', Auth::user()->username]
        ])->findOrFail($id);

        if($request->method()=='GET' && empty($request['draw'])) {
            $countries = Country::orderBy('name', 'asc')->get(['id', 'name']);
            return view('reseller.reports.invoice', ['countries' => $countries, 'user'=>$user]);
        } else {
            $temp = $request['operator'];
            return $dataTable->forInputs($request->all(), $id)->render('reseller.reports.post_invoice', [
                'from_date' => $request['from_date'],
                'to_date' => $request['to_date'],
                'country' => $request['country'],
                'user'=>$user,
                'operator' => is_array($temp) ? implode(',', $temp) : $temp,
            ]);
        }
    }

    public function get_sms_client_report_api($name = null, $filter = null)
    {
        $filter     = $filter != null ? strtolower($filter) : 'today';
        $conn       = DB::connection('kannel');
        $start      = null;
        $today      = Carbon::today();
        $client     = strtolower($name);

        switch ($filter)
        {
            case 'today':
                $start = strtotime($today);
                break;
            case 'week':
                $start = strtotime($today->subWeek());
                break;
            case 'month':
                $start = strtotime($today->subMonth());
                break;
            default:
                $start = strtotime($today);
        }

        $data = [];
        if (in_array(strtolower($filter), ['today', 'week', 'month']))
        {
            $res = $conn->select("select session_esme as client, msg_smsc_id as route, smpp_source_add as sender,
    count(smpp_source_add) as total FROM MT WHERE msg_timestamp > $start and session_esme = '$client'
    GROUP BY smpp_source_add ORDER BY session_esme");

            foreach ($res as $rpt)
                $data[] = [$rpt->client, $rpt->sender, $rpt->route, $rpt->total];
        }

        $response = ['data' => $data];
        return response($response)->header('Content-Type', 'text/json');
    }

    public function get_reports_daily(ResellerDailyReportDTDataTable $dataTable) {

        return $dataTable->render('reseller.reports.daily_sms_clients');
    }

    public function get_report_dept_status(ResellerDeptStatusReportDataTable $dataTable){

       return $dataTable->render('reseller.reports.dept_status');

    }


    public function get_reports_daily_for_user($username, ReportDailyReportUserDataTable $dataTable) {

        $user = Auth::user();
        $_start_date = Carbon::now()->toDateString();
        $_end_date = Carbon::parse($_start_date)->addDay()->addSeconds(-1)->toDateTimeString();

        return $dataTable->render('reseller.reports.daily_for_user', [
            'username' => $username
        ]);
    }

    public function get_comparison_report() {
        $user = Auth::user();
        $users = User::where([
            ['created_by', '=', $user->username]
        ])->orderBy('full_name', 'asc')->get(['id', 'full_name']);

        return view('reseller.reports.comparison', [
            'users' => $users
        ]);
    }

    public function post_comparison_report(Request $request) {
        $this->validate($request, [
            'type' => 'required',
            'client1' => 'required',
            'client2' => 'required'
        ]);

        $client1 = User::where('created_by', Auth::user()->username)->find($request['client1']);
        $client2 = User::where('created_by', Auth::user()->username)->find($request['client2']);

        if(empty($client1)) {
            return Redirect::back()->withInput()->withErrors(array('error' => 'Unable to find client with id '.$request['client1']));
        }
        if(empty($client2)) {
            return Redirect::back()->withInput()->withErrors(array('error' => 'Unable to find client with id '.$request['client1']));
        }

        $user = $request->user();
        $date = Carbon::now();
        $from_date = Carbon::parse($date->toDateString());
        $to_date = clone $from_date;
        $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

        $from_date_2 = $from_date->copy();
        $to_date_2 = $to_date->copy();
        $date_str1 = '';
        $date_str2 = '';

        $type = $request['type'];
        if($type=='years') {
            $from_date->setDate($request['year1'], 1, 1);
            $to_date = clone $from_date;
            $to_date = $to_date->addYears(1)->addSeconds(-1);

            $from_date_2->setDate($request['year2'], 1, 1);
            $to_date_2 = clone $from_date_2;
            $to_date_2 = $to_date_2->addYears(1)->addSeconds(-1);

            $date_str1 = 'Year '.$request['year1'];
            $date_str2 = 'Year '.$request['year2'];
        }
        else if($type=='months') {
            $from_date->setDate($request['year1'], $request['month1'], 1);
            $to_date = $from_date->copy()->addMonth()->addSeconds(-1);

            $from_date_2->setDate($request['year2'], $request['month2'], 1);
            $to_date_2 = $from_date_2->copy()->addMonth()->addSeconds(-1);

            $date_str1 = $from_date->format('F'). ' '.$request['year1'];
            $date_str2 = $from_date_2->format('F'). ' '.$request['year2'];
        }
        else if($type=='days') {
            $from_date->setDate($request['year1'], $request['month1'], $request['day1']);
            $to_date = $from_date->copy()->addDay()->addSeconds(-1);

            $from_date_2->setDate($request['year2'], $request['month2'], $request['day2']);
            $to_date_2 = $from_date_2->copy()->addDay()->addSeconds(-1);

            $date_str1 = $request['day1'].' '.$from_date->format('F'). ' '.$request['year1'];
            $date_str2 = $request['day2'].' '.$from_date_2->format('F'). ' '.$request['year2'];
        }
        else if($type=='weeks') {

            $to_date = $this->get_end_of_week($request['year1'], $request['month1'], $request['week1']);
            $from_date = $this->get_start_of_week($to_date);

            $to_date_2 = $this->get_end_of_week($request['year2'], $request['month2'], $request['week2']);
            $from_date_2 = $this->get_start_of_week($to_date_2);

            $date_str1 = 'Week '.$request['week1'].', '.$from_date->format('F'). ' '.$request['year1'];
            $date_str2 = 'Week '.$request['week2'].', '.$from_date_2->format('F'). ' '.$request['year2'];
        }

        $data = DB::select('select `status`, count(*) as total from `logs`
                            where `user_id`=? AND (`submit_date` between ? and ?)
                            GROUP BY `status`', [
            $client1->id,
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString()
        ]);

        $tempArray = [];
        foreach ($data as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray["{$status}"] = $total;
        }

        $data2 = DB::select('select `status`, count(*) as total from `logs`
                            where `user_id`=? AND (`submit_date` between ? and ?)
                            GROUP BY `status`', [
            $client2->id,
            $from_date_2->toDateTimeString(),
            $to_date_2->toDateTimeString()
        ]);

        $tempArray2 = [];
        foreach ($data2 as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray2["{$status}"] = $total;
        }

        $total_sms_sent = $client1->logs()->whereBetween('submit_date', [
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString()
        ])->count();

        $total_sms_sent_2 = $client2->logs()->whereBetween('submit_date', [
            $from_date_2->toDateTimeString(),
            $to_date_2->toDateTimeString()
        ])->count();

        $total_sms_delivered = isset($tempArray['DELIVRD']) ? $tempArray['DELIVRD']: 0;
        $total_sms_ack = isset($tempArray['ACK/']) ? $tempArray['ACK/'] : 0;
        $total_sms_undelivered = isset($tempArray['UNDELIV']) ? $tempArray['UNDELIV'] : 0;
        $total_sms_expired = isset($tempArray['EXPIRED']) ? $tempArray['EXPIRED'] : 0;

        $total_sms_delivered_2 = isset($tempArray2['DELIVRD']) ? $tempArray2['DELIVRD']: 0;
        $total_sms_ack_2 = isset($tempArray2['ACK/']) ? $tempArray2['ACK/'] : 0;
        $total_sms_undelivered_2 = isset($tempArray2['UNDELIV']) ? $tempArray2['UNDELIV'] : 0;
        $total_sms_expired_2 = isset($tempArray2['EXPIRED']) ? $tempArray2['EXPIRED'] : 0;

        return view('reseller.reports.comparison_report', [
            'total_sms' => $date_str1.'. '.$client1->full_name,
            'total_sms_sent' => $total_sms_sent,
            'total_sms_delivered' => $total_sms_delivered,
            'total_sms_ack' => $total_sms_ack,
            'total_sms_undelivered' => $total_sms_undelivered,
            'total_sms_expired' => $total_sms_expired,

            'total_sms_2' => $date_str2.'. '.$client2->full_name,
            'total_sms_sent_2' => $total_sms_sent_2,
            'total_sms_delivered_2' => $total_sms_delivered_2,
            'total_sms_ack_2' => $total_sms_ack_2,
            'total_sms_undelivered_2' => $total_sms_undelivered_2,
            'total_sms_expired_2' => $total_sms_expired_2,

            'client1' =>$client1,
            'client2' =>$client2,
        ]);
    }

//    public function get_reports_specific(ResellerSpecificReportDataTable $specific,Request $request)
//    {
//        $user = Auth::user();
//
//		if (strtoupper($request->method()) == 'POST')
//        {
//
//            $to     = Carbon::parse($request->get('to'));
//            $from     = Carbon::parse($request->get('from'));
//
//            $data = DB::table('logs')
//                ->join('users', 'users.id', '=', 'logs.user_id')
//                ->where('users.created_by', $user->username);
//            $userId = $request->get('user');
//            if(!empty($userId)) {
//                $data = $data->where('logs.user_id', $userId);
//            }
//            $status = $request->get('status');
//            if(!empty($status)) {
//                $data = $data->where('logs.status', $status);
//            }
//            $sender = $request->get('sender');
//            if(!empty($sender)) {
//                $data = $data->where('logs.sender', $sender);
//            }
//            $phone = $request->get('phone');
//            if(!empty($phone)) {
//                $data = $data->where('logs.msisdn', $phone);
//            }
//                $data = $data->whereBetween('logs.submit_date', [$from->toDateTimeString(), $to->toDateTimeString()])
//                ->groupBy(['logs.status', 'logs.network', 'logs.username', 'full_name'])
//                ->orderBy('logs.submit_date', 'desc')
//                ->select(DB::raw('count(logs.id) as total, logs.status, logs.network as network, logs.username, full_name'))
//                ->get()
//            ;
//
//            return view('reseller.reports.specific-tbl', [
//                'data' => $data
//            ]);
//        }
//        else
//        {
//            $users      = DB::select('select * from users where deleted_at is null and created_by = ? order by username ASC', [$user->username]);
//            //$statuses   = DB::select('select distinct(status) from logs where status != ""');
//
//			$statuses=array('DELIVRD','UNDELIV','ACK/','REJECTD','FAILED','EXPIRED');
//
//            return $specific->render('reseller.reports.specific', [
//                'users' => $users,
//                'statuses' => $statuses
//            ]);
//        }
//    }

    public function get_reports_specific(ResellerSpecificReportDataTable $specific,Request $request)
    {
        $user = Auth::user();

        //username.
        $username = 'admin';
        //password.
        $password = 'Overlord#$Supernova&*#7xrY';

        $url = "https://deywuroreporting.snwolley.com/generate/report";
//        $url = "https://reports.deywuro.com/generate/report";
        $url_get = "https://deywuroreporting.snwolley.com/reseller/report";
//        $url_get = "https://deywuroreporting.snwolley.com/query/report";
//        $url_get = "https://reports.deywuro.com/reseller/report";

        $sender_ids = UserSenderId::query()->where([['user_id', Auth::user()->id], ['status', 1]])->get();
        if (strtoupper($request->method()) == 'POST')
        {
            $request->validate([
                'user_id' => 'required',
                'from_date' => 'required|date',
                'to_date' => 'required|date',
                'sender' => 'nullable',
                'phone_number' => 'nullable',
                'status' => 'nullable',
            ]);

            $client = User::query()->where('id', $request->user_id)->first();

            // Post data
            $data = [
                'user_id' => $client->id,
                'search_username' => $client->username,
                'email' => $user->email,
                'msisdn' => $request->phone_number ?? '',
                'sender' => $request->sender ?? '',
                'status' => $request->status ?? '',
                'start_date' => $request->from_date,
                'end_date' => $request->to_date,
            ];

            \Illuminate\Support\Facades\Log::channel('deywuro_specify_report')->info("[reseller_username:$user->username] [reseller_id:$user->id] [user_id:$client->id] [email:$user->email] [from_date:$request->from_date] [to_date:$request->to_date]");


            $ch = curl_init();
            $headers = array();
            $headers[] = "Content-Type: application/json";
            curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
            curl_setopt($ch, CURLOPT_USERPWD, $username . ":" . $password);
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
            curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
            $result = curl_exec($ch);
            $result = json_decode($result, TRUE);
            curl_close($ch);

            if ($result) {
                if (json_decode($result['code']) == 200) {
//                    \session()->flash('success', "Your request is currently in progress. Report will be sent to {$user->email}");
                    return redirect()->back()->with('success', "Your request is currently in progress. Report will be sent to {$user->email}");

                } elseif (json_decode($result['code']) == 500) {
//                    \session()->flash('error', "Your request is failed");
                    return redirect()->back()->with('error', "Your request is failed");

                } else {
//                    \session()->flash('error', "Your request did not process through.");
                    return redirect()->back()->with('error', "Your request did not process through.");
                }
            }
            else{
//                \session()->flash('error', "Your request did not process through.");
                return redirect()->back()->with('error', "Your request did not process through.");
            }
        }
        else
        {
            $users = DB::select('select * from users where deleted_at is null and created_by = ? order by username ASC', [$user->username]);
            $statuses = array('DELIVRD','UNDELIV','ACK/','REJECTD','FAILED','EXPIRED');

            // Get data
            $data_get = [
                'email' => $user->email
            ];

            $ch = curl_init();
            $headers = array();
            $headers[] = "Content-Type: application/json";
            curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
            curl_setopt($ch, CURLOPT_USERPWD, $username . ":" . $password);
            curl_setopt($ch, CURLOPT_URL, $url_get);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
            curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data_get));
            $result_get = curl_exec($ch);
            $result_get = json_decode($result_get, TRUE);
            curl_close($ch);

            if ($result_get) {
                if (json_decode($result_get['code']) == 200) {
                    return view('reseller.reports.specific', [
                        'users' => $users,
                        'statuses' => $statuses,
                        'code' => $result_get['code'],
                        'result' => $result_get['result'],
                        'sender_ids' => $sender_ids
                    ]);
                }
            }

            $code = null;
            $result = null;
            return view('reseller.reports.specific', [
                'users' => $users,
                'statuses' => $statuses,
                'code' => $code,
                'result' => $result,
                'sender_ids' => $sender_ids
            ]);
        }
    }

    public function get_reports_specific_user($user)
    {
        $data = [];

        if ($user != null)
        {
            $data = DB::table('logs')
                ->join('users', 'users.id', '=', 'logs.user_id')
                ->where('logs.username', strtolower($user))
                ->groupBy(['logs.status', 'logs.sender', 'full_name'])
                ->select(DB::raw('count(logs.id) as total, logs.status, logs.sender as sender, full_name'))
                ->get()
            ;
        }

        return view('reseller.reports.specific_user', [
            'data' => $data
        ]);
    }


    public function get_reseller_users(Request $request) {
        $username = auth()->user()->username;

        return User::query()->whereHas('company', function ($query) use ($username) {
            $query->where('created_by',$username);
        })->with('company')->get(['id','username']);
    }

    public function get_sms_log_new_24h(SmsLog24DataTable $dataTable)
    {
        return $dataTable->render('report.sms_log_24h');
    }

    public function get_specify_period(SpecifyPeriodDataTable $dataTable)
    {
        return $dataTable->render('report.specify_period');
    }

    public function _2way(Report2wayDataTable $dataTable) {
        return $dataTable->render('report.report2way');
    }

    public function post_report(Request $request) {

        $this->validate($request, [
            'name'      => 'required',
            'filter_by' => 'required',
            'from_date' => 'required',
            'to_date'   => 'required',
            'headers'   => 'required',
            'client_id'   => 'required'
        ]);

        $inputs = $request->except(['headers', 'to_date', 'from_date']);
        $inputs['status'] = 'pending';
        $inputs['filter_by_campaign'] = json_encode($request->input('filter_by_campaign'));
        $inputs['expiry_date'] = Carbon::now()->addDays(3);
        $from = Carbon::parse($request['from_date']);
        $to = Carbon::parse($request['to_date']);

        if($from->gt($to)) {
            return Redirect::back()->withInput()->withErrors(array('error' => 'Invalid date range'));
        }

        $user = User::query()->find($request->input('client_id'));


        $inputs['from_date'] = $from->toDateString();
        $inputs['to_date'] = $to->toDateString();

        $inputs['headers'] = implode(',', $request['headers']);
        $inputs['pulled_by'] = $request->user()->username;

        //save report
        $user->reports()->create($inputs);
//        $request->user()->reports()->create($inputs);


        Session::flash('success', 'Your report is being processed!');
        return redirect()->route('reseller.report_get_reporting');
    }

    public function delete_selection(Request $request) {

    }

    public function comparison() {
        return view('report.comparison');
    }

    public function post_comparison(Request $request) {
        $this->validate($request, [
            'type' => 'required',
        ]);

        $user = $request->user();
        $date = Carbon::now();
        $from_date = Carbon::parse($date->toDateString());
        $to_date = clone $from_date;
        $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

        $from_date_2 = $from_date->copy();
        $to_date_2 = $to_date->copy();
        $date_str1 = '';
        $date_str2 = '';

        $type = $request['type'];
        if($type=='years') {
            $from_date->setDate($request['year1'], 1, 1);
            $to_date = clone $from_date;
            $to_date = $to_date->addYears(1)->addSeconds(-1);

            $from_date_2->setDate($request['year2'], 1, 1);
            $to_date_2 = clone $from_date_2;
            $to_date_2 = $to_date_2->addYears(1)->addSeconds(-1);

            $date_str1 = 'Year '.$request['year1'];
            $date_str2 = 'Year '.$request['year2'];
        }
        else if($type=='months') {
            $from_date->setDate($request['year1'], $request['month1'], 1);
            $to_date = $from_date->copy()->addMonth()->addSeconds(-1);

            $from_date_2->setDate($request['year2'], $request['month2'], 1);
            $to_date_2 = $from_date_2->copy()->addMonth()->addSeconds(-1);

            $date_str1 = $from_date->format('F'). ' '.$request['year1'];
            $date_str2 = $from_date_2->format('F'). ' '.$request['year2'];
        }
        else if($type=='days') {
            $from_date->setDate($request['year1'], $request['month1'], $request['day1']);
            $to_date = $from_date->copy()->addDay()->addSeconds(-1);

            $from_date_2->setDate($request['year2'], $request['month2'], $request['day2']);
            $to_date_2 = $from_date_2->copy()->addDay()->addSeconds(-1);

            $date_str1 = $request['day1'].' '.$from_date->format('F'). ' '.$request['year1'];
            $date_str2 = $request['day2'].' '.$from_date_2->format('F'). ' '.$request['year2'];
        }
        else if($type=='weeks') {

            $to_date = $this->get_end_of_week($request['year1'], $request['month1'], $request['week1']);
            $from_date = $this->get_start_of_week($to_date);

            $to_date_2 = $this->get_end_of_week($request['year2'], $request['month2'], $request['week2']);
            $from_date_2 = $this->get_start_of_week($to_date_2);

            $date_str1 = 'Week '.$request['week1'].', '.$from_date->format('F'). ' '.$request['year1'];
            $date_str2 = 'Week '.$request['week2'].', '.$from_date_2->format('F'). ' '.$request['year2'];
        }

        $data = DB::select('select `status`, count(*) as total from `logs`
                            where `user_id`=? AND (`submit_date` between ? and ?)
                            GROUP BY `status`', [
            Auth::user()->id,
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString()
        ]);

        $tempArray = [];
        foreach ($data as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray["{$status}"] = $total;
        }

        $data2 = DB::select('select `status`, count(*) as total from `logs`
                            where `user_id`=? AND (`submit_date` between ? and ?)
                            GROUP BY `status`', [
            Auth::user()->id,
            $from_date_2->toDateTimeString(),
            $to_date_2->toDateTimeString()
        ]);

        $tempArray2 = [];
        foreach ($data2 as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray2["{$status}"] = $total;
        }

        $total_sms_sent = $user->logs()->whereBetween('submit_date', [
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString()
        ])->count();

        $total_sms_sent_2 = $user->logs()->whereBetween('submit_date', [
            $from_date_2->toDateTimeString(),
            $to_date_2->toDateTimeString()
        ])->count();

        $total_sms_delivered = isset($tempArray['DELIVRD']) ? $tempArray['DELIVRD']: 0;
        $total_sms_ack = isset($tempArray['ACK/']) ? $tempArray['ACK/'] : 0;
        $total_sms_undelivered = isset($tempArray['UNDELIV']) ? $tempArray['UNDELIV'] : 0;
        $total_sms_expired = isset($tempArray['EXPIRED']) ? $tempArray['EXPIRED'] : 0;

        $total_sms_delivered_2 = isset($tempArray2['DELIVRD']) ? $tempArray2['DELIVRD']: 0;
        $total_sms_ack_2 = isset($tempArray2['ACK/']) ? $tempArray2['ACK/'] : 0;
        $total_sms_undelivered_2 = isset($tempArray2['UNDELIV']) ? $tempArray2['UNDELIV'] : 0;
        $total_sms_expired_2 = isset($tempArray2['EXPIRED']) ? $tempArray2['EXPIRED'] : 0;

        return view('report.comparison_report', [
            'total_sms' => $date_str1,
            'total_sms_sent' => $total_sms_sent,
            'total_sms_delivered' => $total_sms_delivered,
            'total_sms_ack' => $total_sms_ack,
            'total_sms_undelivered' => $total_sms_undelivered,
            'total_sms_expired' => $total_sms_expired,

            'total_sms_2' => $date_str2,
            'total_sms_sent_2' => $total_sms_sent_2,
            'total_sms_delivered_2' => $total_sms_delivered_2,
            'total_sms_ack_2' => $total_sms_ack_2,
            'total_sms_undelivered_2' => $total_sms_undelivered_2,
            'total_sms_expired_2' => $total_sms_expired_2,
        ]);
    }

    /**
     * @param $year
     * @param $month
     * @param $week
     * @return static
     * @throws \Exception
     */
    private function get_end_of_week($year, $month, $week)
    {
        $week_number = (int)$week;
        if($week_number <1 || $week_number>5) {
            throw new \Exception('Invalid week number');
        }
        $max_day = $week_number * 7;
        if($max_day > 30) {
            $date = Carbon::create($year, $month, 30, 23, 59, 59);
            if($date->copy()->addDay()->month == $date->month)
                $date = $date->addDay();
        } else {
            $date = Carbon::create($year, $month, $max_day, 23, 59, 59);

            while (!$date->isSunday()) {
                $date = $date->addDays(-1);
            }
        }
        return $date;
    }

    private function get_start_of_week(Carbon $endDate)
    {
        $date = $endDate->copy();
        if($date->day -6 < 1) {
            $date->day = 1;
        } else {
            while (!$date->isMonday()) {
                $date = $date->addDays(-1);
            }
        }
        $date->setTime(0, 0, 0);
        return $date;
    }

    public function reseller_new_report() {
        $user = Auth::user();
        $senders = null;
        $campaigns = null;
        $user->resellers_client()->each(function ($client) use(&$campaigns,&$senders){
            $campaigns = $client->jobs()->groupBy('name')->get(['id', 'name']);
            $senders = $client->jobs()->groupBy('sender')->get(['id', 'sender']);
        });


        $col_headers =
            [
            'To', 'Message', 'Campaign Name', 'Status', 'Username', 'From', 'Sent At', 'SMS Count', 'Network', 'Msg Id', 'Delivered At'
            ];

        return view('reseller.reports.new_report',[
            'campaigns'   => $campaigns,
            'senders'     => $senders,
            'col_headers' => $col_headers,
            'clients'     => $user->resellers_client()
        ]);
    }

    public function reseller_client_sender_campaign(User $user)
    {

        $campaigns = $user->jobs()->distinct()->take(500)->get(['name']);
        $senders = $user->logs()->distinct()->take(500)->get(['sender']);

            return response()->json(['campaigns'=>$campaigns,'senders'=>$senders]);
    }

    public function reseller_reporting(Request $request, ResellerReportingDataTable $dataTable) {

        if($request->method()=='POST') {
            $selection = $request->get('selection');
            $deleted = 0;

            if(!empty($selection)) {
                DB::beginTransaction();
                foreach ($selection as $sel) {
                    $contactToDel = $request->user()->reports()->find($sel);
                    if(!empty($contactToDel)) {
                        $contactToDel->delete();
                        $deleted++;
                    }
                }
                DB::commit();
            }
            Session::flash('success', $deleted. ' report(s) was successfully deleted!');
        }
        return $dataTable->render('reseller.reports.reporting');
    }

    public function reporting(Request $request, ReportingDataTable $dataTable) {
        if($request->method()=='POST') {
            $selection = $request->get('selection');
            $deleted = 0;

            if(!empty($selection)) {
                DB::beginTransaction();
                foreach ($selection as $sel) {
                    $contactToDel = $request->user()->reports()->find($sel);
                    if(!empty($contactToDel)) {
                        $contactToDel->delete();
                        $deleted++;
                    }
                }
                DB::commit();
            }
            Session::flash('success', $deleted. ' report(s) was successfully deleted!');
        }
        return $dataTable->render('report.reporting');
    }


    public function delete_report($id) {
        $report = Report::findOrFail($id);
        $report->delete();
        unlink($report->path);
        Session::flash('success', 'Your report '.$report->name.' was successfully deleted!');
        return redirect()->back();
    }

    public function download_report($id) {
        $report = Report::findOrFail($id);
        $path = $report->path;
        if(!empty($path) && file_exists($path))
            return response()->download($path);
        else
            abort(404);
    }


    // FINANCIAL REPORTS
    public function get_operational_costs(ResellerOperationalCostDataTable $dataTable)
    {
        $user   = Auth::user();
        $start  = new Carbon('first day of this month');
        $end    = new Carbon('last day of this month');

        $query = DB::table('logs')->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"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->whereBetween('submit_date', [$start, $end])
            ->groupBy(DB::raw('DATE(submit_date)'))
            ->orderBy('date', 'desc')->get();

        $total  = 0.00;
        $cfg    = \App\Setting::where('user_id', $user->id)->first();

        foreach ($query as $row) $total += ($row->mtn + $row->airtel + $row->tigo + $row->vodafone + 90.00 + 15.00);

        return $dataTable->render('reseller.reports.finance.operational_costs', ['total' => $total]);
    }

    // operational cost - day
    public function get_operational_cost($date)
    {
        $user   = Auth::user();
        $query  = DB::table('logs')->select(DB::raw("date(submit_date) as date, sum(sms_count) as count, sum(if(network = 'mtn', sms_count, 0)) as mtn, sum(if(network = 'airtel', sms_count, 0)) as airtel, sum(if(network = 'tigo', sms_count, 0)) as tigo, sum(if(network = 'vodafone', sms_count, 0)) as vodafone, sum(if(network = 'rsms', sms_count, 0)) as rsms"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->where(DB::raw("date(submit_date)"), '=', $date)
            ->groupBy(DB::raw('DATE(logs.submit_date)'))
            ->orderBy('date', 'desc')->get();

        $au     = 0.014; $mu = 0.013; $tu = 0.00833; $vu = 0.010; $ru = 0.013;

        $cfg    = \App\Setting::where('user_id', $user->id)->first();
        if ($cfg == null)
        {
            $cfg = new Setting();
            $cfg->user_id       = $user->id;
            $cfg->electricity   = 0.00;
            $cfg->water         = 0.00;
            $cfg->nhil          = 0.00;
            $cfg->vat           = 0.00;
            $cfg->others        = 0.00;
        }

        return view('reseller.reports.finance.operational_cost', [
            'date' => $date,
            'data' => (object)[
                'airtel'    => (object)['count' => $query[0]->airtel, 'unit' => $au, 'total' => round($query[0]->airtel * $au, 2)],
                'mtn'       => (object)['count' => $query[0]->mtn, 'unit' => $mu, 'total' => round($query[0]->mtn * $mu, 2)],
                'tigo'      => (object)['count' => $query[0]->tigo, 'unit' => $tu, 'total' => round($query[0]->tigo * $tu, 2)],
                'vodafone'  => (object)['count' => $query[0]->vodafone, 'unit' => $vu, 'total' => round($query[0]->vodafone * $vu, 2)],
                'rsms'      => (object)['count' => $query[0]->rsms, 'unit' => $ru, 'total' => round($query[0]->rsms * $ru, 2)],
                'electricity'=> (object)['total' => round($cfg->electricity, 2)],
                'water'     => (object)['total' => round($cfg->water, 2)],
                'others'    => (object)['total' => round($cfg->others, 2)],
                'expenses'  => (object)['total' => round($cfg->electricity, 2) + round($cfg->water, 2) + round($cfg->others, 2)]
            ]
        ]);
    }

    // get sales
    public function get_sales($start = null, $end = null)
    {
        $user = Auth::user();
        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('logs')->select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, sum(if(originated = 'guiuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'guiuser', sms_count * 0.024, 0)) as guitotal"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('user, date'))->orderBy('date', 'desc')->get();

        $data = [];
        foreach ($query as $item)
        {
            if (in_array($item->user, ['guiuser', 'guiuser']))
            {
                if (!array_key_exists($item->date, $data))
                {
                    $data[$item->date] = ['count' => $item->count, 'total' => $item->apitotal + $item->guitotal];
                }
                else
                {
                    $data[$item->date]['total'] += $item->apitotal + $item->guitotal;
                    $data[$item->date]['count'] += $item->count;
                }
            }
        }

        return view('reseller.reports.finance.sales', [
            'data' => $data,
            'date' => (object) ['start' => date('d-m-Y', strtotime($start)), 'end' => date('d-m-Y', strtotime($end))]
        ]);
    }

    // get sale for day
    public function get_sale($date)
    {
        $user = Auth::user();
        $query = DB::table('logs')->select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, sum(if(originated = 'guiuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'guiuser', sms_count * 0.024, 0)) as guitotal"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->where(DB::raw("date(submit_date)"), '=', $date)
            ->groupBy(DB::raw('user'))
            ->orderBy('date', 'desc')->get();

        $data   = [];
        $total  = 0;

        foreach ($query as $item)
        {
            if ($item->user == 'guiuser')
            {
                $data[] = ['user' => 'guiuser', 'count' => $item->count, 'unit' => 0.020, 'total' => $item->apitotal];
            }
            else if($item->user == 'guiuser')
            {
                $data[] = ['user' => 'guiuser', 'count' => $item->count, 'unit' => 0.024, 'total' => $item->guitotal];
            }
            $total += $item->apitotal > 0 ? $item->apitotal : $item->guitotal;
        }

        return view('reseller.reports.finance.sale', [
            'date' => $date,
            'data' => $data,
            'total' => $total
        ]);
    }

    // profitability
    public function get_profitability($start = null, $end = null)
    {
        $user = Auth::user();
        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();
        }

        // OP
        $opq = DB::table('logs')->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"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('DATE(submit_date)'))->orderBy('date', 'desc')->get();

        // SALES
        $ssq = DB::table('logs')->select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, sum(if(originated = 'guiuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'guiuser', sms_count * 0.024, 0)) as guitotal"))
            ->leftJoin('users', 'logs.user_id', '=', 'users.id')
            ->where('users.created_by', '=', $user->username)
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('user, date'))->orderBy('date', 'desc')->get();

        // OP
        $odata = [];
        foreach ($opq as $oitem)
        {
            if (!array_key_exists($oitem->date, $odata))
            {
                $odata[$oitem->date] = [
                    'count' => 0,
                    'total' => round(($oitem->mtn + $oitem->airtel + $oitem->tigo + $oitem->vodafone + 90.00 + 15.00), 2)
                ];
            }
            else
            {
                $odata[$oitem->date]['count'] += 0;
                $odata[$oitem->date]['total'] += round(($oitem->mtn + $oitem->airtel + $oitem->tigo + $oitem->vodafone + 90.00 + 15.00), 2);
            }
        }

        // SALES
        $sdata = [];
        foreach ($ssq as $sitem)
        {
            if (in_array($sitem->user, ['guiuser', 'guiuser']))
            {
                if (!array_key_exists($sitem->date, $sdata))
                {
                    $sdata[$sitem->date] = ['count' => $sitem->count, 'total' => $sitem->apitotal + $sitem->guitotal];
                }
                else
                {
                    $sdata[$sitem->date]['total'] += $sitem->apitotal + $sitem->guitotal;
                    $sdata[$sitem->date]['count'] += $sitem->count;
                }
            }
        }

        $data   = [];
        $budget = 4000;

        foreach ($sdata as $key => $sd)
        {
            if (array_key_exists($key, $odata))
            {
                $op         = $odata[$key];
                $profit     = $sd['total'] - $op['total'];
                $variance   = $profit - $budget;

                $data[] = (object) [
                    'date'  => $key,
                    'opt'   => $op['total'],
                    'ssl'   => $sd['total'],
                    'prf'   => $profit,
                    'bgt'   => $budget,
                    'var'   => $variance,
                    'per'   => ($profit / $budget) * 100
                ];
            }
        }

        // graph - specific function
        $gd = $data;
        usort($gd, function($a, $b)
        {
            return strcmp($a->date, $b->date);
        });

        return view('reseller.reports.finance.profitability', [
            'data' => $data,
            'graph' => json_encode($gd),
            'date' => (object) ['start' => date('d-m-Y', strtotime($start)), 'end' => date('d-m-Y', strtotime($end))]
        ]);
    }

    // compare sales
    public function compare_sales(Request $request)
    {
        $reseller = Auth::user();

        if (strtoupper($request->method() == 'POST'))
        {
            $this->validate($request, [
                'type1' => 'required',
            ]);

            $user = $request->user();
            $date = Carbon::now();
            $from_date = Carbon::parse($date->toDateString());
            $to_date = clone $from_date;
            $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

            $from_date_2 = $from_date->copy();
            $to_date_2 = $to_date->copy();

            $data   = $request->request;
            $typea  = $data->get('type1');
            $typeb  = $data->get('type2');

            if($typea == 'years')
            {
                $from_date->setDate($request['year1'], 1, 1);
                $to_date = clone $from_date;
                $to_date = $to_date->addYears(1)->addSeconds(-1);

                $from_date_2->setDate($request['year2'], 1, 1);
                $to_date_2 = clone $from_date_2;
                $to_date_2 = $to_date_2->addYears(1)->addSeconds(-1);
            }
            else if ($typea == 'months')
            {
                $from_date->setDate($request['year1'], $request['month1'], 1);
                $to_date = $from_date->copy()->addMonth()->addSeconds(-1);

                $from_date_2->setDate($request['year2'], $request['month2'], 1);
                $to_date_2 = $from_date_2->copy()->addMonth()->addSeconds(-1);
            }
            else if ($typea=='days')
            {
                $from_date->setDate($request['year1'], $request['month1'], $request['day1']);
                $to_date = $from_date->copy()->addDay()->addSeconds(-1);

                $from_date_2->setDate($request['year2'], $request['month2'], $request['day2']);
                $to_date_2 = $from_date_2->copy()->addDay()->addSeconds(-1);
            }
            else if ($typea == 'weeks')
            {
                $to_date = $this->get_end_of_week($request['year1'], $request['month1'], $request['week1']);
                $from_date = $this->get_start_of_week($to_date);

                $to_date_2 = $this->get_end_of_week($request['year2'], $request['month2'], $request['week2']);
                $from_date_2 = $this->get_start_of_week($to_date_2);
            }

            $ka = $from_date->toDateString();
            $kb = $from_date_2->toDateString();
            $oa = (object) ['count' => 0, 'total' => 0];
            $sa = (object) ['count' => 0, 'total' => 0];
            $ob = (object) ['count' => 0, 'total' => 0];
            $sb = (object) ['count' => 0, 'total' => 0];
            $pa = 0; $pb = 0;// default

            if ($data->get('type2') != null)
            {
                // zoom
                $sender1 = $data->get('sender1');
                $sender2 = $data->get('sender2');

                // sender
                if ($sender1 != null && $sender2 != null)
                {
                    // bench 1
                    $sa = $this->salex($from_date, $to_date, true, $sender1);
                    $sa = $sa != null ? (object) $sa[$ka] : (object) ['count' => 0, 'total' => 0];
                    $oa = $this->opex($from_date, $to_date, true, $sender1);
                    $oa = $oa != null ? (object) $oa[$ka] : (object) ['count' => 0, 'total' => 0];
                    $pa = $sa->total - $oa->total;

                    // bench 2
                    $sb = $this->salex($from_date_2, $to_date_2, true, $sender2);
                    $sb = $sb != null ? (object) $sb[$kb] : (object) ['count' => 0, 'total' => 0];
                    $ob = $this->opex($from_date_2, $to_date_2, true, $sender2);
                    $ob = $ob != null ? (object) $ob[$kb] : (object) ['count' => 0, 'total' => 0];
                    $pb = $sb->total - $ob->total;
                }
            }
            else // no zoom
            {
                // bench 1
                $ka = $from_date->toDateString();
                $sa = $this->salex($from_date, $to_date);
                $sa = $sa != null ? (object) $sa[$ka] : (object) ['count' => 0, 'total' => 0];
                $oa = $this->opex($from_date, $to_date);
                $oa = $oa != null ? (object) $oa[$ka] : (object) ['count' => 0, 'total' => 0];
                $pa = $sa->total - $oa->total;

                // bench 2
                $kb = $from_date_2->toDateString();
                $sb = $this->salex($from_date_2, $to_date_2);
                $sb = $sb != null ? (object) $sb[$kb] : (object) ['count' => 0, 'total' => 0];
                $ob = $this->opex($from_date_2, $to_date_2);
                $ob = $ob != null ? (object) $ob[$kb] : (object) ['count' => 0, 'total' => 0];
                $pb = $sb->total - $ob->total;
            }

            $data = [
                'ca' => (object) [
                    'date'  => $ka,
                    'oc'    => number_format($oa->count),
                    'opt'   => number_format($oa->total, 2),
                    'sc'    => number_format($sa->count),
                    'ssl'   => number_format($sa->total, 2),
                    'prf'   => number_format($pa, 2)
                ],
                'cb' => (object) [
                    'date'  => $kb,
                    'oc'    => number_format($ob->count),
                    'opt'   => number_format($ob->total, 2),
                    'sc'    => number_format($sb->count),
                    'ssl'   => number_format($sb->total, 2),
                    'prf'   => number_format($pb, 2)
                ]
            ];

            return view('reseller.reports.finance.compare_profit', [
                'data' => $data,
                'from' => $ka,
                'to' => $kb
            ]);
        }

        $clients = DB::table('users')
            ->select(DB::raw("users.full_name as name, users.id, users.username"))
            ->where('created_by', '=', $reseller->username)->orderBy('name', 'asc')->get();

        return view('reseller.reports.finance.compare_profit_index', [
            'clients' => $clients
        ]);
    }

    private function salex($from, $to, $zoom = false, $sender = null)
    {
        $user   = Auth::user();
        $ssq    = [];

        if ($zoom)
        {
            if ($sender != null)
            {
                $ssq = DB::table('logs')->select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, sum(if(originated = 'guiuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'guiuser', sms_count * 0.024, 0)) as guitotal"))
                    ->leftJoin('users', 'logs.user_id', '=', 'users.id')
                    ->where('sender', '=', $sender)
                    ->where('users.created_by', '=', $user->username)
                    ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                    ->groupBy(DB::raw('user, date'))->orderBy('date', 'desc')->get();
            }
        }
        else
        {
            $ssq = DB::table('logs')->select(DB::raw("date(submit_date) as date, originated as user, sum(sms_count) as count, sum(if(originated = 'guiuser', sms_count * 0.02, 0)) as apitotal, sum(if(originated = 'guiuser', sms_count * 0.024, 0)) as guitotal"))
                ->leftJoin('users', 'logs.user_id', '=', 'users.id')
                ->where('users.created_by', '=', $user->username)
                ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                ->groupBy(DB::raw('user, date'))->orderBy('date', 'desc')->get();
        }

        $sdata = [];
        foreach ($ssq as $sitem)
        {
            if (in_array($sitem->user, ['guiuser', 'guiuser']))
            {
                if (!array_key_exists($sitem->date, $sdata))
                {
                    $sdata[$sitem->date] = ['count' => $sitem->count, 'total' => $sitem->apitotal + $sitem->guitotal];
                }
                else
                {
                    $sdata[$sitem->date]['total'] += $sitem->apitotal + $sitem->guitotal;
                    $sdata[$sitem->date]['count'] += $sitem->count;
                }
            }
        }

        return $sdata;
    }

    private function opex($from, $to, $zoom = false, $sender = null)
    {
        $user   = Auth::user();
        $opq    = [];
        $cfg    = \App\Setting::where('user_id', $user->id)->first();

        if ($cfg == null)
        {
            $cfg = new Setting();
            $cfg->user_id       = $user->id;
            $cfg->electricity   = 0.00;
            $cfg->water         = 0.00;
            $cfg->nhil          = 0.00;
            $cfg->vat           = 0.00;
            $cfg->others        = 0.00;
            $cfg->save();
        }

        // op expenses
        $opex = $cfg->electricity + $cfg->water + $cfg->others;

        if ($zoom)
        {
            if ($sender != null)
            {
                $opq = DB::table('logs')->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"))
                    ->leftJoin('users', 'logs.user_id', '=', 'users.id')
                    ->where('sender', '=', $sender)
                    ->where('users.created_by', '=', $user->username)
                    ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                    ->groupBy(DB::raw('DATE(submit_date)'))->orderBy('date', 'desc')->get();
            }
        }
        else
        {
            $opq = DB::table('logs')->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"))
                ->leftJoin('users', 'logs.user_id', '=', 'users.id')
                ->where('users.created_by', '=', $user->username)
                ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                ->groupBy(DB::raw('DATE(submit_date)'))->orderBy('date', 'desc')->get();
        }

        $odata = [];
        foreach ($opq as $oitem)
        {
            if (!array_key_exists($oitem->date, $odata))
            {
                $odata[$oitem->date] = [
                    'count' => (int) $oitem->count,
                    'total' => round(($oitem->mtn + $oitem->airtel + $oitem->tigo + $oitem->vodafone + $opex), 2)
                ];
            }
            else
            {
                $odata[$oitem->date]['count'] += (int) $oitem->count;
                $odata[$oitem->date]['total'] += round(($oitem->mtn + $oitem->airtel + $oitem->tigo + $oitem->vodafone + $opex), 2);
            }
        }

        return $odata;
    }

    public function client_registrations(ResellerClientRegistrationDataTable $dataTable)
    {
        $start  = new Carbon('first day of this month');
        $end    = new Carbon('last day of this month');

        return $dataTable->render('reseller.reports.client.registrations', [
            'from_date' =>$start->format('d-m-Y'),
            'to_date' => $end->format('d-m-Y')
        ]);
    }

    public function top_clients(Request $request)
    {
        $results    = ['detail' => [], 'summary' => []];
        $user       = Auth::user();
        $from_date  = new Carbon('first day of this month');
        $to_date    = new Carbon('last day of this month');

        if (strtoupper($request->method()) == 'POST')
        {
            $data = $request->request;

            $start      = $data->get('from_date');
            $end        = $data->get('to_date');

            if ($from_date != null && $to_date != null)
            {
                $from_date  = Carbon::parse($start)->toDateTimeString();
                $to_date    = Carbon::parse($end)->addDay(1)->toDateTimeString();
            }

            $qresult = DB::table('logs')
                ->leftJoin('users', 'users.id', '=', 'logs.user_id')
                ->join('companies', 'users.id', '=', 'companies.user_id')
                ->leftJoin('credits as rev', 'rev.user_id', '=', 'logs.user_id')
                ->leftJoin('credits as prf', 'prf.username', '=', 'users.created_by')
                ->select(DB::raw("client, price, sms, page, company,cprice, rprice, cprice - rprice as profit"))
                ->select(DB::raw("users.full_name as client, companies.name as company, count(logs.id) as sms,
              sum(logs.sms_count) as page, sum(logs.sms_count * rev.price) as cprice,rev.price as price, sum(logs.sms_count * prf.price) as rprice"))
                ->whereBetween('submit_date', [$from_date, $to_date])
                ->where('users.created_by', '=', $user->username)
                ->groupBy('client')->get()->all();


            $pages          = array_sum(array_column($qresult, 'page'));
            $client_sales   = array_sum(array_column($qresult, 'cprice'));
            $reseller_sales = array_sum(array_column($qresult, 'rprice'));
            $profits        = number_format($client_sales - $reseller_sales, 2);

            $results['summary'] = [
                'pages' => $pages,
                'client_sales' => $client_sales,
                'reseller_sales' => $reseller_sales,
                'profits' => $profits
            ];

            foreach ($qresult as $item)
            {
                $profit         = $item->cprice - $item->rprice;
                $profit_share   = @($profit / $profits) === false ? 0.00 : @($profit / $profits)  * 100;
                $traffic_share  = @($item->page / $pages) === false ? 0.00 : @($item->page / $pages) * 100;

                $results['detail'][] = (object) [
                    'client'            => $item->company ?? $item->client,
                    'sms'               => number_format($item->sms),
                    'page'              => number_format($item->page),
                    'cprice'            => number_format($item->cprice, 2),
                    'rprice'            => number_format($item->rprice, 2),
                    'profit'            => number_format($profit, 2),
                    'traffic_share'     => number_format($traffic_share, 2),
                    'profit_share'      => number_format($profit_share, 2),
                    'revenue'           => number_format($item->page *$item->price, 2)
                ];
            }
        }


        return  view('reseller.reports.client.top_clients', [
            'from_date' => date('d-m-Y', strtotime($from_date)),
            'to_date' => date('d-m-Y', strtotime($to_date)),
            'results' => $results
        ]);
    }


    public function client_email_report(ResellerClientEmailReportDataTable $email)
    {
        return $email->render('reseller.reports.email.index');
    }

    public function user_campaign(ChimpReportDataTable $email,$user)
    {
        return $email->with('user',$user)->render('reseller.reports.email.campaign');
    }


    public function campaignsReportView(UserEmailCampaign $emailCampaign,User $user)
    {

        $limit = $user->subscriptionLimits();// check for subscription limits and redirect accordingly

//        if(!isset($limit))
//            return redirect()->route('email.subscription.index')->withErrors(
//                "kindly choose a package here !!"
//            );

        $report  = session()->get('rc_chimp_report') ?? $this->get_campaign_report($emailCampaign->mail_campaign_id);
        session()->put(['pc_chimp_report'=>$report]);
        $report  = $this->array_to_object($report);

        return  isset($report->id) ? view('reseller.reports.email.view',compact('report','emailCampaign')) : redirect()->back()->withErrors('CAMPAIGN RESULT COULD NOT BE FOUND');
    }

    use ChimpTrait;

}