<?php

namespace App\Http\Controllers\Admin;
use App\Company;
use App\Country;
use App\DataTables\AdminDailyReportDTDataTable;
use App\DataTables\AdminDailyReportUserDataTable;
use App\DataTables\AdminUSSDReportDataTable;
use App\DataTables\AdminDeptStatusReportDataTable;
use App\DataTables\AdminInvoiceDataTable;
use App\DataTables\AdminSmsReportDataTable;
use App\DataTables\OperationalCostDataTable;
use App\DataTables\SpecificReportDataTable;
use App\DataTables\Admin\FinancialAnalysisReportDataTable;
use App\DataTables\Admin\SocialMediaReportDatatable;
use App\DataTables\Admin\EmailReportDatatable;
use App\Http\Controllers\Controller;
use App\Log;
use App\Role;
use App\Setting;
use App\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use PDF;

class ReportController extends Controller
{
    public function generate_invoice_form()
    {
        return view('admin.report.generate_invoice_form');
    }

    public function generate_invoice(Request $request)
    {
        $inputs = $request->all();
        $this->validate($request, [
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'type' => 'required',
            'user' => 'required'
        ]);
        $username = $inputs['user'];

        $fromDate = Carbon::parse($inputs['from_date']);
        $fromDate->setTime(0,0,0);

        $toDate = Carbon::parse($inputs['to_date']);
        $toDate->setTime(23, 59, 59);

        if($inputs['type']=='esme') {
            if($username == 'mtulip')
                $records = DB::connection('kannel')->select('select count(*) as quantity,
                   unit_prices.*
                    from logs LEFT JOIN unit_prices ON logs.username=unit_prices.username
                    where (submit_date between ? and ?) and logs.username LIKE ?
                    ', [
                            $fromDate->toDateTimeString(),
                            $toDate->toDateTimeString(),
                            $username.'%'
                        ]);
            else
                $records = DB::connection('kannel')->select('select count(*) as quantity,
                   unit_prices.*
                    from logs LEFT JOIN unit_prices ON logs.username=unit_prices.username
                    where (submit_date between ? and ?) and logs.username=?
                    ', [
                    $fromDate->toDateTimeString(),
                    $toDate->toDateTimeString(),
                    $username
                ]);

            $data = [];
            $total = 0;
            $address = '';
            $city = '';
            $company = '';
            $currency = '';
            foreach ($records as $rec) {
                $currency = $rec->currency;
                $company = $rec->company;
                $address = $rec->address;
                $city = $rec->city;
                $unit_price = $rec->unit_price;
                $data[] = [
                    'quantity' => number_format($rec->quantity, 0, '.', ','),
                    'description' => 'TOTAL SMS, '.strtoupper($fromDate->format('d M Y')).' - '.strtoupper($toDate->format('d M Y')),
                    'unit_price' => $currency.' '.$unit_price,
                    'amount' => $currency.' '.(number_format($unit_price * $rec->quantity, 2, '.', ',')),
                ];
                $total += ($unit_price * $rec->quantity);
            }
            $tax = 0;
            $record = [
                'receiptNo' => date('ymdHis'),
                'name' => $username,
                'company' => $address,
                'address' => $company,
                'city' => $city,
                'comment' => 'None',
                'data' => $data,
                'sub_total' => $currency.' '.(number_format($total, 2, '.', ',')),
                'cst' => '',
                'vat' => '0.00',
                'tax' => (number_format($tax, 2, '.', ',')),
                'shipping' => '-',
                'total' => $currency.' '.(number_format($total+$tax, 2, '.', ',')),
            ];
        }

        else {
            $user = User::where('username',$username)->first();
            $currency = $user->currency;
            /** @noinspection SqlDialectInspection */
            $records = DB::select('

            select count(*) as quantity, sum(logs.sms_count) as page,  network, networks.sms_price as unit_price, user_id
            from logs LEFT JOIN networks ON networks.nice_name=logs.network where (submit_date between ? and ?) and username=?
            group by network,networks.sms_price,user_id', [$fromDate->toDateTimeString(), $toDate->toDateTimeString(), $username]

            );

//            $records =   DB::table('logs')
//                ->leftJoin('networks', 'networks.nice_name', '=', 'logs.network')
//                ->whereBetween('submit_date', [$fromDate->toDateTimeString(), $toDate->toDateTimeString()])
//                ->where('logs.username',$username)
////                ->select('logs.*','country_id','code','logs.sms_price','prefix','nice_name','full_name',DB::raw('sum(logs.sms_count) as page'))
//                ->groupBy(['network','networks.sms_price','user_id'])->get()->all();

//            dd($records);

            $data = [];
            $userId = 0;
            $total = 0;
            foreach ($records as $rec) {

                $userId = $rec->user_id;
                $usrNetwork = DB::select('select user_networks.sms_price as sms_price
        from networks LEFT JOIN user_networks ON networks.id=user_networks.network_id
        where networks.nice_name=? and user_networks.user_id=?', [$rec->network, $rec->user_id]);
                $unit_price = !empty($usrNetwork) && !empty($usrNetwork[0]) ? $usrNetwork[0]->sms_price : $rec->unit_price;
                $data[] = [
//                    sum(logs.sms_count) as page
                    'quantity' => number_format($rec->page, 0, '.', ','),
                    'description' => 'TOTAL SMS FROM '.strtoupper($fromDate->format('d M Y')).'. TO '.strtoupper($toDate->format('d M Y')).'. ('.$rec->network.')',
                    'unit_price' => $unit_price.' '.$currency,
                    'amount' => (number_format($unit_price * $rec->quantity, 2, '.', ',')).' '.$currency,
                ];
                $total += ($unit_price * $rec->quantity);
            }

            $tax = 17.5*$total/100.0;
            $record = [
                'receiptNo' => date('ymdHis').$userId,
                'name' => $user ? ucwords($user->full_name):'',
                'company' => $user ? ucwords($user->organisation):'',
                'address' => '',
                'city' => '',
                'comment' => 'None',
                'data' => $data,
                'sub_total' => (number_format($total, 2, '.', ',')).' '.$currency,
                'cst' => '',
                'vat' => '17.50',
                'tax' => (number_format($tax, 2, '.', ',')),
                'shipping' => '-',
                'total' => (number_format($total+$tax, 2, '.', ',')).' '.$currency,
            ];
        }

        return view('admin.report.generate_invoice', [
            'record' => $record,
            'username' => $username,
            'inputs' => $inputs,
        ]);
    }

    public function download_invoice(Request $request)
    {
        $inputs = $request->all();
        $this->validate($request, [
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'type' => 'required',
            'user' => 'required'
        ]);
        $username = $inputs['user'];

        $fromDate = Carbon::parse($inputs['from_date']);
        $fromDate->setTime(0,0,0);

        $toDate = Carbon::parse($inputs['to_date']);
        $toDate->setTime(23, 59, 59);

        if($inputs['type']=='esme') {
            if($username == 'mtulip')
                $records = DB::connection('kannel')->select('select count(*) as quantity,
                   unit_prices.*
                    from logs LEFT JOIN unit_prices ON logs.username=unit_prices.username
                    where (submit_date between ? and ?) and logs.username LIKE ?
                    ', [
                        $fromDate->toDateTimeString(),
                        $toDate->toDateTimeString(),
                        $username.'%'
                    ]);
            else
                $records = DB::connection('kannel')->select('select count(*) as quantity,
                   unit_prices.*
                    from logs LEFT JOIN unit_prices ON logs.username=unit_prices.username
                    where (submit_date between ? and ?) and logs.username=?
                    ', [
                            $fromDate->toDateTimeString(),
                            $toDate->toDateTimeString(),
                            $username
                        ]);

            $data = [];
            $total = 0;
            $address = '';
            $city = '';
            $company = '';
            $name = '';
            $currency = '';
            foreach ($records as $rec) {
                $name = $rec->name;
                $currency = $rec->currency;
                $company = $rec->company;
                $address = $rec->address;
                $city = $rec->city;
                $unit_price = $rec->unit_price;
                $data[] = [
                    'quantity' => number_format($rec->quantity, 0, '.', ','),
                    'description' => 'TOTAL SMS, '.strtoupper($fromDate->format('d M Y')).' - '.strtoupper($toDate->format('d M Y')),
                    'unit_price' => $currency.' '.$unit_price,
                    'amount' => $currency.' '.(number_format($unit_price * $rec->quantity, 2, '.', ',')),
                ];
                $total += ($unit_price * $rec->quantity);
            }

            $tax = 0;
            $record = [
                'receiptNo' => date('ymdHis'),
                'name' => empty($name) ? $username : ucwords($name),
                'company' => $address,
                'address' => $company,
                'city' => $city,
                'comment' => 'None',
                'data' => $data,
                'sub_total' => $currency.' '.(number_format($total, 2, '.', ',')),
                'cst' => '',
                'vat' => '0.00',
                'tax' => (number_format($tax, 2, '.', ',')),
                'shipping' => '-',
                'total' => $currency.' '.(number_format($total+$tax, 2, '.', ',')),
            ];
        } else {
            $user = User::where('username',$username)->first();
            $currency = $user->currency;
            $records = DB::select('select count(*) as quantity,
       network, networks.sms_price as unit_price, user_id
        from logs LEFT JOIN networks ON networks.nice_name=logs.network
        where (submit_date between ? and ?) and username=?
        group by network,networks.sms_price, user_id', [
                $fromDate->toDateTimeString(),
                $toDate->toDateTimeString(),
                $username
            ]);

            $data = [];
            $userId = 0;
            $total = 0;
            foreach ($records as $rec) {
                $userId = $rec->user_id;
                $usrNetwork = DB::select('select user_networks.sms_price as sms_price
        from networks LEFT JOIN user_networks ON networks.id=user_networks.network_id
        where networks.nice_name=? and user_networks.user_id=?', [$rec->network, $rec->user_id]);
                $unit_price = !empty($usrNetwork) && !empty($usrNetwork[0]) ? $usrNetwork[0]->sms_price : $rec->unit_price;
                $data[] = [
                    'quantity' => number_format($rec->quantity, 0, '.', ','),
                    'description' => 'TOTAL SMS FROM '.strtoupper($fromDate->format('d M Y')).'. TO '.strtoupper($toDate->format('d M Y')).'. ('.$rec->network.')',
                    'unit_price' => $unit_price.' '.$currency,
                    'amount' => (number_format($unit_price * $rec->quantity, 2, '.', ',')).' '.$currency,
                ];
                $total += ($unit_price * $rec->quantity);
            }

            $tax = 17.5*$total/100.0;
            $record = [
                'receiptNo' => date('ymdHis').$userId,
                'name' => $user ? ucwords($user->full_name):'',
                'company' => $user ? ucwords($user->organisation):'',
                'address' => '',
                'city' => '',
                'comment' => 'None',
                'data' => $data,
                'sub_total' => (number_format($total, 2, '.', ',')).' '.$currency,
                'cst' => '',
                'vat' => '17.50',
                'tax' => (number_format($tax, 2, '.', ',')).' '.$currency,
                'shipping' => '-',
                'total' => (number_format($total+$tax, 2, '.', ',')).' '.$currency,
            ];
        }

//        return view('print.client_invoice', [
//            'record' => $record
//        ]);

        $pdf = PDF::loadView('print.client_invoice', [
            'record' => $record
        ]);
        return $pdf->download('print.pdf');
    }

    public function get_operational_costs(OperationalCostDataTable $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"))
            ->whereBetween('submit_date', [$start, $end])
            ->groupBy(DB::raw('DATE(logs.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('admin.report.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"))->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();

        return view('admin.report.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)]
            ]
        ]);
    }

    public function get_reports_daily(AdminDailyReportDTDataTable $dataTable) {
        $_start_date = Carbon::now()->toDateString();
        $_end_date = Carbon::parse($_start_date)->addDay()->addSeconds(-1)->toDateTimeString();

        $data = DB::table('logs')
            ->whereBetween('logs.submit_date', [$_start_date, $_end_date])
            ->groupBy(['logs.status','logs.submit_date'])
            ->orderBy('logs.submit_date', 'desc')
            ->select(DB::raw('count(*) as total, logs.status'))
            ->get();

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

        $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;

        return $dataTable->render('admin.report.daily', [
            'total_sms_delivered' => $total_sms_delivered,
            'total_sms_undelivered' => $total_sms_undelivered,
            'total_sms_expired' => $total_sms_expired,
            'total_sms_ack' => $total_sms_ack
        ]);
    }


    public function get_reports_dept_status(AdminDeptStatusReportDataTable $dataTable)
    {

        return $dataTable->render('admin.report.dept_status');
    }

    public function get_reports_ussd_client(AdminUSSDReportDataTable $dataTable)
    {
        return $dataTable->render('admin.report.ussd_client');
        // return view('admin.report.ussd_client');
    }

    public function get_reports_daily_for_user($username, AdminDailyReportUserDataTable $dataTable)
    {
        $_start_date = Carbon::now()->toDateString();
        $_end_date = Carbon::parse($_start_date)->addDay()->addSeconds(-1)->toDateTimeString();

        $data = DB::table('logs')
            ->join('users', 'users.id', '=', 'logs.user_id')
            ->where('users.username', $username)
            ->whereBetween('logs.submit_date', [$_start_date, $_end_date])
            ->groupBy(['logs.status'])
            ->orderBy('logs.submit_date', 'desc')
            ->select(DB::raw('count(*) as total, logs.status'))
            ->get()
        ;
        $tempArray = [];
        foreach ($data as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray["{$status}"] = $total;
        }

        $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;

        return $dataTable->render('admin.report.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_reports_specific(SpecificReportDataTable $specific)
    {

        $users      = User::query()->where('role_id', '')->get(['id', 'full_name']);
        return $specific->render('admin.report.specific', [
            'users' => $users,
        ]);
    }

    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'])
                ->orderBy('logs.submit_date', 'desc')
                ->select(DB::raw('count(logs.id) as total, logs.status, logs.sender as sender, full_name'))
                ->get()
            ;
        }

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


    public function get_financial_analysis_report(FinancialAnalysisReportDataTable $report)
    {
        return $report->render('admin.report.finance.financial_analysis');
    }

    public function getSocialMediaReport(SocialMediaReportDatatable $datatable)
    {
        return $datatable->render('admin.report.social_media_report');
    }

    public function getEmailPortalReport(EmailReportDatatable $datatable)
    {
        return $datatable->render('admin.report.email_report');
    }

    public function sms_report(AdminSmsReportDataTable $dataTable) {
        return $dataTable->render('admin.report.sms_report');
    }

    public function invoice(Request $request, AdminInvoiceDataTable $dataTable, $id) {
        $user = User::findOrFail($id);

        if($request->method()=='GET' && empty($request['draw'])) {
            $countries = Country::orderBy('name', 'asc')->get(['id', 'name']);
            return view('admin.report.invoice', ['countries' => $countries, 'user'=>$user]);
        } else {
            $temp = $request['operator'];
            return $dataTable->forInputs($request->all(), $id)->render('admin.report.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_comparison_report() {
        return view('admin.report.comparison', [

        ]);
    }

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

        $client1 = User::find($request['user1']);
        $client2 = User::find($request['user2']);


        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']));
        }

        $sender1 = $request->get('sender1');
        $sender2 = $request->get('sender2');

        $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 ?)
                             '.(!empty($sender1) ? " and `sender`='$sender1'" :'').'
                            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 ?)
                            '.(!empty($sender2) ? " and `sender`='$sender2'" :'').'
                            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()
        ]);
        if(!empty($sender1)) {
            $total_sms_sent = $total_sms_sent->where('sender', $sender1);
        }

        $total_sms_sent = $total_sms_sent->count();


        $total_sms_sent_2 = $client2->logs()->whereBetween('submit_date', [
            $from_date_2->toDateTimeString(),
            $to_date_2->toDateTimeString()
        ]);
        if(!empty($sender2)) {
            $total_sms_sent_2 = $total_sms_sent_2->where('sender', $sender2);
        }
        $total_sms_sent_2 = $total_sms_sent_2->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('admin.report.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,
        ]);
    }

    // get sales
    public function get_sales($start = null, $end = null)
    {
        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"))
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('date(submit_date), originated'))->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('admin.report.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)
    {
        $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"))
            ->where(DB::raw("date(submit_date)"), '=', $date)
            ->groupBy(DB::raw('date(submit_date),originated'))
            ->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('admin.report.finance.sale', [
            'date' => $date,
            'data' => $data,
            'total' => $total
        ]);
    }

    // profitability
    public function get_profitability($start = null, $end = null)
    {
        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"))
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('DATE(logs.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"))
            ->whereBetween('submit_date', [$start, Carbon::parse($end)->addDay(1)->toDateString()])
            ->groupBy(DB::raw('date(submit_date), originated'))->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('admin.report.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)
    {
        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
                $client1 = $data->get('client1');
                $client2 = $data->get('client2');
                $sender1 = $data->get('sender1');
                $sender2 = $data->get('sender2');

                // sender
                if ($sender1 != null && $sender2 != null || ($client1 != null && $sender1 != null) && ($client2 != null && $sender2 != null))
                {
                    // bench 1
                    $sa = $this->salex($from_date, $to_date, true, null, $sender1);
                    $sa = $sa != null ? (object) $sa[$ka] : (object) ['count' => 0, 'total' => 0];
                    $oa = $this->opex($from_date, $to_date, true, null, $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, null, $sender2);
                    $sb = $sb != null ? (object) $sb[$kb] : (object) ['count' => 0, 'total' => 0];
                    $ob = $this->opex($from_date_2, $to_date_2, true, null, $sender2);
                    $ob = $ob != null ? (object) $ob[$kb] : (object) ['count' => 0, 'total' => 0];
                    $pb = $sb->total - $ob->total;
                }

                // client
                if (($client1 != null && $sender1 == null) && ($client2 != null && $sender2 == null))
                {
                    // bench 1
                    $sa = $this->salex($from_date, $to_date, true, $client1, null);
                    $sa = $sa != null ? (object) $sa[$ka] : (object) ['count' => 0, 'total' => 0];
                    $oa = $this->opex($from_date, $to_date, true, $client1, null);
                    $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, $client2, null);
                    $sb = $sb != null ? (object) $sb[$kb] : (object) ['count' => 0, 'total' => 0];
                    $ob = $this->opex($from_date_2, $to_date_2, true, $client2, null);
                    $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),
                    'sc'    => number_format($sa->count),
                    'ssl'   => number_format($sa->total),
                    'prf'   => number_format($pa)
                ],
                'cb' => (object) [
                    'date'  => $kb,
                    'oc'    => number_format($ob->count),
                    'opt'   => number_format($ob->total),
                    'sc'    => number_format($sb->count),
                    'ssl'   => number_format($sb->total),
                    'prf'   => number_format($pb)
                ]
            ];

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

        $senders = DB::table('users')
            ->select(DB::raw("users.full_name as name, users.id, users.username"))
            ->leftJoin('roles', 'roles.id', '=', 'users.role_id')
            ->where('roles.name', 'like', '%reseller%')->orderBy('name', 'asc')->get();

        return view('admin.report.finance.compare_profit_index', [
            'senders' => $senders
        ]);
    }

    /**
     * @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;
    }

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

        if ($zoom)
        {
            if ($client != null)
            { // client
                $client = \App\User::find($client);

                $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"))
                    ->where('user_id', '=', $client->id)
                    ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                    ->groupBy(DB::raw('user, date'))->orderBy('date', 'desc')->get();
            }
            else // sender
            {
                $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"))
                    ->where('sender', '=', $sender)
                    ->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"))
                ->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, $client = null, $sender = null)
    {
        $user = Auth::user();
        $cfg  = \App\Setting::where('user_id', $user->id)->first();
        $opq  = [];

        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 ($client != null)
            {
                $client = \App\User::find($client);

                $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"))
                    ->where('user_id', '=', $client->id)
                    ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                    ->groupBy(DB::raw('DATE(logs.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"))
                    ->where('sender', '=', $sender)
                    ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                    ->groupBy(DB::raw('DATE(logs.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"))
                ->whereBetween('submit_date', [$from, Carbon::parse($to)->addDay(1)->toDateString()])
                ->groupBy(DB::raw('DATE(logs.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;
    }
}