<?php

namespace Modules\Report\Controllers;

use App\Controllers\BaseController;
use App\Libraries\UserCheck;
use Modules\Trip\Models\TripModel;
use Modules\Ticket\Models\TicketModel;

use Modules\Trip\Models\SubtripModel;
use Modules\Trip\Models\PickdropModel;

use Modules\Location\Models\LocationModel;

use Modules\Fleet\Models\FleetModel;
use Modules\Fleet\Models\VehicleModel;
use Modules\Schedule\Models\ScheduleModel;
use Modules\Location\Models\StandModel;

use Modules\Agent\Models\AgentModel;
use Modules\Agent\Models\Agentcommission;

use Modules\User\Models\UserModel;
use Modules\User\Models\UserDetailModel;
use Modules\Company\Models\CompanyModel;





class Report extends BaseController
{

    protected $Viewpath;
    protected $tripModel;
    protected $subtripModel;
    protected $locationModel;
    protected $fleetTypeModel;
    protected $scheduleeModel;
    protected $vehicleModel;
    protected $standModel;
    protected $picdropModel;

    protected $ticketModel;
    protected $agenttModel;

    protected $agentCommissionModel;

    protected $userModel;
    protected $userDetailModel;
    protected $companyModel;


    protected $db;

    public function __construct()
    {
        $this->Viewpath = "Modules\Report\Views";
        $this->tripModel = new TripModel();
        $this->subtripModel = new SubtripModel();
        $this->locationModel = new LocationModel();
        $this->fleetTypeModel = new FleetModel();
        $this->vehicleModel = new VehicleModel();
        $this->scheduleeModel = new ScheduleModel();
        $this->standModel = new StandModel();
        $this->picdropModel = new PickdropModel();

        $this->ticketModel = new TicketModel();
        $this->agenttModel = new AgentModel();
        $this->agentCommissionModel = new Agentcommission();

        $this->userModel = new UserModel();
        $this->userDetailModel = new UserDetailModel();
        $this->companyModel = new CompanyModel();

        $this->db      = \Config\Database::connect();
    }

    public function ticketSaleLoad()
    {
        $data['selectedMainTrips'] = "";
        $data['selectedSubTrips'] = "";
        $logged_company_id = 0;
        if(session()->get('role_id') == 7){
            $logged_company_id = session()->get('company_id');
        }
        $data['logged_company_id'] = $logged_company_id;
        


        $companyId = $this->request->getVar('company_id');
        $maintripId = $this->request->getVar('trip_id');
        $subTripId = $this->request->getVar('subtrip_id');

        $trip = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->where('trips.status', 1);
        
        if(session()->get('role_id') == 7){
            $trip->where('trips.company_id', $logged_company_id);
        }
        $trip = $trip->findAll();

        $data['trip'] = $trip;

        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        
        $trip_list = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('companies d', 'trips.company_id = d.id')
            ->where('trips.status', 1)
            ->where('d.status', 1);
        
        if(session()->get('role_id') == 7){
            $trip_list->where('trips.company_id', $logged_company_id);
        }
        $trip_list = $trip_list->findAll();

        $data['trip_list'] = $trip_list;

        $subtrip_list = $this->subtripModel->select('subtrips.id as subtripsid,subtrips.*,a.name as pickup_location_name,b.name as drop_location_name, c.company_id')
            ->join('locations a', 'a.id = subtrips.pick_location_id', 'left')
            ->join('locations b', 'b.id = subtrips.drop_location_id', 'left')
            ->join('trips c', 'c.id = subtrips.trip_id')
            ->join('companies d', 'd.id = c.company_id')
            ->where('c.status', 1)
            ->where('d.status', 1)
            ->where('subtrips.status', 1);
        
        if(session()->get('role_id') == 7){
            $subtrip_list->where('c.company_id', $logged_company_id);
        }
        $subtrip_list = $subtrip_list->findAll();

        $data['subtrip_list'] = $subtrip_list;

        $userRole = $this->session->get('role_id');
        $data['filepath'] =  $this->Viewpath;
        $ticket = array();
        $data['ticket'] = $ticket;

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.ticket_sold");

        $heading = lang("Localize.ticket") . ' ' . lang("Localize.report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        
        
        $data['s_company_id'] = ($userRole == 7) ? $this->session->get("company_id") : "all";
        $data['s_trip_id'] = "all";
        $data['s_subtrip_id'] = "all";
        $data['s_startdate'] = "";
        $data['s_enddate'] = "";
        $data['ticketType'] = "normal";
        

        echo view($this->Viewpath . '\report/ticketsold', $data);
    }

    public function ticketSaleData()
    {
         /* $data = $this->request->getVar();
         var_dump($data);
         exit; */
        $companyId = $this->request->getVar('company_id');
        $fromDate = date("Y-m-d", strtotime($this->request->getVar('start_date')));
        $toDate = date("Y-m-d", strtotime($this->request->getVar('end_date')));
        $maintripId = $this->request->getVar('trip_id');
        $subTripId = $this->request->getVar('subtrip_id');
        $ticketType = $this->request->getVar('type');

        $main_trips = $this->request->getVar('main_trips');
        $subtrips = $this->request->getVar('subtrips');

        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        
        $tripQuery = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('companies d', 'trips.company_id = d.id')
            ->where('trips.status', 1)
            ->where('d.status', 1);

        if ($companyId != "all") {
            $tripQuery->where('trips.company_id', $companyId);
        }
        if ($maintripId != "all") {
            $tripQuery->where('trips.id', $maintripId);
        }

        $data['trip_list'] = $tripQuery->findAll();

        $subtripQuery = $this->subtripModel->select('subtrips.id as subtripsid,subtrips.*,a.name as pickup_location_name,b.name as drop_location_name, c.company_id')
                ->join('locations a', 'a.id = subtrips.pick_location_id', 'left')
                ->join('locations b', 'b.id = subtrips.drop_location_id', 'left')
                ->join('trips c', 'c.id = subtrips.trip_id')
                ->join('companies d', 'd.id = c.company_id');
        
        if ($companyId != "all") {
            $subtripQuery->where('d.id', $companyId);
        }
        if ($maintripId != "all") {
            $subtripQuery->where('c.id', $maintripId);
        }
        $data['subtrip_list'] = $subtripQuery->where('c.status', 1)
                            ->where('d.status', 1)
                            ->where('subtrips.status', 1)
                            ->findAll();


        
        $userRole = $this->session->get('role_id');
        if ($companyId && $companyId != "all") {
            //$this->ticketModel->where('trips.company_id', $companyId);
        }

        $ticketQuery = $this->ticketModel->select('tickets.created_at as date,tickets.*,trips.company_id, trips.id as tripid,trips.*,subtrips.id as subtripid,subtrips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name,c.name as sub_pickup_location_name,d.name as sub_drop_location_name, e.name AS company_name, g.first_name AS booked_by_f_name, g.last_name AS booked_by_l_name, h.first_name AS agent_fname, h.last_name As agent_lname');

        if ($ticketType === 'refund') {
            $ticketQuery = $ticketQuery
                ->select('i.created_at AS refund_date, i.refund_fee AS refund_cancel_fee, i.detail AS refund_details')
                ->join('refunds i', 'i.booking_id = tickets.booking_id', 'left');
        } elseif($ticketType === 'cancel') {
            $ticketQuery = $ticketQuery
                ->select('j.created_at AS cancel_date, j.cancel_fee AS refund_cancel_fee')
                ->join('cancels j', 'j.booking_id = tickets.booking_id', 'left');
        }
        
        $ticketQuery->join('trips', 'trips.id = tickets.trip_id')
            ->join('subtrips', 'subtrips.id = tickets.subtrip_id')
            ->join('schedules', 'schedules.id = trips.schedule_id')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('locations c', 'c.id = subtrips.pick_location_id', 'left')
            ->join('locations d', 'd.id = subtrips.drop_location_id', 'left')
            ->join('companies e', 'e.id = trips.company_id')
            ->join('users f', 'f.id = tickets.bookby_user_id', 'left')
            ->join('user_details g', 'g.user_id = f.id', 'left')
            ->join('agents h', 'h.user_id = f.id', 'left')
            ->where('trips.status', 1)
            ->where('subtrips.status', 1)
            ->where('e.status', 1);

        if ($ticketType == "normal") {

            $ticketQuery->where('tickets.refund', 0)->where('tickets.cancel_status', 0);
        }
        if ($ticketType == "refund") {

            $ticketQuery->where('tickets.refund', 1);
        }
        if ($ticketType == "cancel") {
            $ticketQuery->where('tickets.cancel_status', 1);
        }
        if ($companyId != "all") {
            $ticketQuery->where('trips.company_id', $companyId);
        }
        
        $selectedSubTrips = '';
        $selectedMainTrips = '';

        if (is_array($main_trips) && !empty($main_trips)) {
            $selectedMainTrips = $main_trips; 
            $ticketQuery->whereIn('tickets.trip_id', $main_trips);
        }
        $flatsubtrips = [];
        if (is_array($subtrips) && !empty($subtrips)) {
            
            foreach ($subtrips as $subArray) {
                $flatsubtrips = array_merge($flatsubtrips, $subArray);
            }
            $selectedSubTrips  = $flatsubtrips;
            $ticketQuery->whereIn('tickets.subtrip_id', $flatsubtrips);
        }

        $data['selectedMainTrips'] = $selectedMainTrips;
        $data['selectedSubTrips'] = $selectedSubTrips;

        /* if ($subTripId != "all") {
            $ticketQuery->where('tickets.subtrip_id', $subTripId);
        } */
        //echo $fromDate." => ".$toDate;//exit;
        $ticketQuery->where('DATE(tickets.created_at) >=', $fromDate)->where('DATE(tickets.created_at) <=', $toDate);
        $ticket = $ticketQuery->findAll();
        /* echo $ticketQuery->getLastQuery(); // Shows the actual executed SQL
    exit; */

        //echo "<pre>";print_r($ticket);exit;
        // dd($ticket);
        $data['filepath'] =  $this->Viewpath;

        $data['ticket'] = $ticket;
        $data['ticketType'] = $ticketType;

        $data['trip'] = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->where('trips.status', 1)
            ->findAll();

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.ticket_sold");

        $heading = lang("Localize.ticket") . ' ' . lang("Localize.report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        $data['s_company_id'] = $companyId;
        $data['s_trip_id'] = $maintripId;
        $data['s_subtrip_id'] = $subTripId;
        $data['s_startdate'] = $fromDate;
        $data['s_enddate'] = $toDate;
        //echo "<pre>";print_r($data);exit;

        echo view($this->Viewpath . '\report/ticketsold', $data);
    }

    public function ticketSaleDataLastOld()
    {
         /* $data = $this->request->getVar();
         var_dump($data);
         exit; */
        $companyId = $this->request->getVar('company_id');
        $fromDate = date("Y-m-d", strtotime($this->request->getVar('start_date')));
        $toDate = date("Y-m-d", strtotime($this->request->getVar('end_date')));
        $maintripId = $this->request->getVar('trip_id');
        $subTripId = $this->request->getVar('subtrip_id');
        $ticketType = $this->request->getVar('type');

        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        
        $tripQuery = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('companies d', 'trips.company_id = d.id')
            ->where('trips.status', 1)
            ->where('d.status', 1);

        if ($companyId != "all") {
            $tripQuery->where('trips.company_id', $companyId);
        }
        if ($maintripId != "all") {
            $tripQuery->where('trips.id', $maintripId);
        }

        $data['trip_list'] = $tripQuery->findAll();

        $subtripQuery = $this->subtripModel->select('subtrips.id as subtripsid,subtrips.*,a.name as pickup_location_name,b.name as drop_location_name, c.company_id')
                ->join('locations a', 'a.id = subtrips.pick_location_id', 'left')
                ->join('locations b', 'b.id = subtrips.drop_location_id', 'left')
                ->join('trips c', 'c.id = subtrips.trip_id')
                ->join('companies d', 'd.id = c.company_id');
        
        if ($companyId != "all") {
            $subtripQuery->where('d.id', $companyId);
        }
        if ($maintripId != "all") {
            $subtripQuery->where('c.id', $maintripId);
        }
        $data['subtrip_list'] = $subtripQuery->where('c.status', 1)
                            ->where('d.status', 1)
                            ->where('subtrips.status', 1)
                            ->findAll();


        
        $userRole = $this->session->get('role_id');
        if ($companyId && $companyId != "all") {
            //$this->ticketModel->where('trips.company_id', $companyId);
        }

        $ticketQuery = $this->ticketModel->select('tickets.created_at as date,tickets.*,trips.company_id, trips.id as tripid,trips.*,subtrips.id as subtripid,subtrips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name,c.name as sub_pickup_location_name,d.name as sub_drop_location_name, e.name AS company_name, g.first_name AS booked_by_f_name, g.last_name AS booked_by_l_name, h.first_name AS agent_fname, h.last_name As agent_lname');

        if ($ticketType === 'refund') {
            $ticketQuery = $ticketQuery
                ->select('i.created_at AS refund_date, i.refund_fee AS refund_cancel_fee, i.detail AS refund_details')
                ->join('refunds i', 'i.booking_id = tickets.booking_id', 'left');
        } elseif($ticketType === 'cancel') {
            $ticketQuery = $ticketQuery
                ->select('j.created_at AS cancel_date, j.cancel_fee AS refund_cancel_fee')
                ->join('cancels j', 'j.booking_id = tickets.booking_id', 'left');
        }
        
        $ticketQuery->join('trips', 'trips.id = tickets.trip_id')
            ->join('subtrips', 'subtrips.id = tickets.subtrip_id')
            ->join('schedules', 'schedules.id = trips.schedule_id')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('locations c', 'c.id = subtrips.pick_location_id', 'left')
            ->join('locations d', 'd.id = subtrips.drop_location_id', 'left')
            ->join('companies e', 'e.id = trips.company_id')
            ->join('users f', 'f.id = tickets.bookby_user_id', 'left')
            ->join('user_details g', 'g.user_id = f.id', 'left')
            ->join('agents h', 'h.user_id = f.id', 'left')
            ->where('trips.status', 1)
            ->where('subtrips.status', 1)
            ->where('e.status', 1);

        if ($ticketType == "normal") {

            $ticketQuery->where('tickets.refund', 0)->where('tickets.cancel_status', 0);
        }
        if ($ticketType == "refund") {

            $ticketQuery->where('tickets.refund', 1);
        }
        if ($ticketType == "cancel") {
            $ticketQuery->where('tickets.cancel_status', 1);
        }
        
        if ($companyId != "all") {
            $ticketQuery->where('trips.company_id', $companyId);
        }
        if ($maintripId != "all") {
            $ticketQuery->where('tickets.trip_id', $maintripId);
        }
        if ($subTripId != "all") {
            $ticketQuery->where('tickets.subtrip_id', $subTripId);
        }
        //echo $fromDate." => ".$toDate;//exit;
        $ticketQuery->where('DATE(tickets.created_at) >=', $fromDate)->where('DATE(tickets.created_at) <=', $toDate);
        $ticket = $ticketQuery->findAll();

        //echo "<pre>";print_r($ticket);exit;
        // dd($ticket);
        $data['filepath'] =  $this->Viewpath;

        $data['ticket'] = $ticket;
        $data['ticketType'] = $ticketType;

        $data['trip'] = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->where('trips.status', 1)
            ->findAll();

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.ticket_sold");

        $heading = lang("Localize.ticket") . ' ' . lang("Localize.report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        $data['s_company_id'] = $companyId;
        $data['s_trip_id'] = $maintripId;
        $data['s_subtrip_id'] = $subTripId;
        $data['s_startdate'] = $fromDate;
        $data['s_enddate'] = $toDate;
        //echo "<pre>";print_r($data);exit;

        echo view($this->Viewpath . '\report/ticketsold', $data);
    }

    public function ticketSaleDataOld()
    {
         /* $data = $this->request->getVar();
         var_dump($data);
         exit; */
        $companyId = $this->request->getVar('company_id');
        $fromDate = $this->request->getVar('start_date');
        $toDate = $this->request->getVar('end_date');
        $maintripId = $this->request->getVar('trip_id');
        $subTripId = $this->request->getVar('subtrip_id');
        $ticketType = $this->request->getVar('type');

        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        
        $tripQuery = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('companies d', 'trips.company_id = d.id')
            ->where('trips.status', 1)
            ->where('d.status', 1);

        if ($companyId != "all") {
            $tripQuery->where('trips.company_id', $companyId);
        }
        if ($maintripId != "all") {
            $tripQuery->where('trips.id', $maintripId);
        }

        $data['trip_list'] = $tripQuery->findAll();

        $subtripQuery = $this->subtripModel->select('subtrips.id as subtripsid,subtrips.*,a.name as pickup_location_name,b.name as drop_location_name, c.company_id')
                ->join('locations a', 'a.id = subtrips.pick_location_id', 'left')
                ->join('locations b', 'b.id = subtrips.drop_location_id', 'left')
                ->join('trips c', 'c.id = subtrips.trip_id')
                ->join('companies d', 'd.id = c.company_id');
        
        if ($companyId != "all") {
            $subtripQuery->where('d.id', $companyId);
        }
        if ($maintripId != "all") {
            $subtripQuery->where('c.id', $maintripId);
        }
        $data['subtrip_list'] = $subtripQuery->where('c.status', 1)
                            ->where('d.status', 1)
                            ->where('subtrips.status', 1)
                            ->findAll();


        
        $userRole = $this->session->get('role_id');
        if ($companyId && $companyId != "all") {
            //$this->ticketModel->where('trips.company_id', $companyId);
        }

        if ($ticketType == "normal") {

            $this->ticketModel->where('refund', 0)->where('cancel_status', 0);
        }
        if ($ticketType == "refund") {

            $this->ticketModel->where('refund', 1);
        }
        if ($ticketType == "cancel") {
            $this->ticketModel->where('cancel_status', 1);
        }

        if ($maintripId == "all") {
            // $this->ticketModel->where('refund',0)->where('cancel_status',0);

        } else {
            if ($subTripId == "all") {

                $this->ticketModel->where('tickets.trip_id', $maintripId);
            } else {
                $this->ticketModel->where('tickets.trip_id', $maintripId)->where('subtrip_id', $subTripId);
            }
        }

        $this->ticketModel->where('DATE(tickets.created_at) >=', $fromDate)->where('DATE(tickets.created_at) <=', $toDate);


        $ticketQuery = $this->ticketModel->select('tickets.created_at as date,tickets.*,trips.company_id, trips.id as tripid,trips.*,subtrips.id as subtripid,subtrips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name,c.name as sub_pickup_location_name,d.name as sub_drop_location_name, e.name AS company_name')

            ->join('trips', 'trips.id = tickets.trip_id')
            ->join('subtrips', 'subtrips.id = tickets.subtrip_id')
            ->join('schedules', 'schedules.id = trips.schedule_id')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('locations c', 'c.id = subtrips.pick_location_id', 'left')
            ->join('locations d', 'd.id = subtrips.drop_location_id', 'left')
            ->join('companies e', 'e.id = trips.company_id')
            ->where('trips.status', 1)
            ->where('subtrips.status', 1)
            ->where('e.status', 1);

        if ($companyId != "all") {
            $ticketQuery->where('trips.company_id', $companyId);
        }
        if ($maintripId != "all") {
            $ticketQuery->where('trips.id', $maintripId);
        }
        $ticket = $ticketQuery->findAll();

        // dd($ticket);
        $data['filepath'] =  $this->Viewpath;

        $data['ticket'] = $ticket;

        $data['trip'] = $this->tripModel->select('trips.id as tripid,trips.*,schedules.*,a.name as pickup_location_name,b.name as drop_location_name')
            ->join('schedules', 'schedules.id = trips.schedule_id', 'left')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->where('trips.status', 1)
            ->findAll();

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.ticket_sold");

        $heading = lang("Localize.ticket") . ' ' . lang("Localize.report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        $data['s_company_id'] = $companyId;
        $data['s_trip_id'] = $maintripId;
        $data['s_subtrip_id'] = $subTripId;
        //echo "<pre>";print_r($data);exit;

        echo view($this->Viewpath . '\report/ticketsold', $data);
    }

    public function agentCommissionLoad()
    {
        $userchek = new UserCheck();
        $agentData = "";
        $userType = $userchek->getUserType();

        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        $userRole = $this->session->get('role_id');
        if ($userType == 2) {
            $agentId =  $this->session->get('user_id');
            $agentData = $this->agenttModel->where('user_id', $agentId)->findAll();
        } else {
            $agentData = $this->agenttModel->findAll();
        }

        $data['agentList'] = $agentData;
        $data['userType'] = $userType;
        $data['filepath'] =  $this->Viewpath;
        $commission = array();
        $data['commission'] = $commission;
        
        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.agent_report");

        $heading = lang("Localize.agent") . ' ' . lang("Localize.report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        return view($this->Viewpath . '\report\agentcommission', $data);
    }


    public function agentCommissionDetails()
    {
        $userchek = new UserCheck();
        $userType = $userchek->getUserType();

        $fromDate = $this->request->getVar('start_date');
        $toDate = $this->request->getVar('end_date');
        $companyId = $this->request->getVar('company_id_for_agent');
        // $subTripId = $this->request->getVar('subtrip_id');
        
        $agentid = $this->request->getVar('agent_id');
        $userRole = $this->session->get('role_id');
        if ($agentid != "all") {
            $this->agentCommissionModel->where('agentcommissions.agent_id', $agentid);
        }

        $this->agentCommissionModel->where('DATE(agentcommissions.created_at) >=', $fromDate);
        $this->agentCommissionModel->where('DATE(agentcommissions.created_at) <=', $toDate);

        $passangerinfo = $this->userDetailModel->findAll();

        // if ($companyId && $companyId !== 'all') {
        //     $this->agentCommissionModel->where('companies.id', $companyId);
        // }

        $commission = $this->agentCommissionModel
            ->select('agentcommissions.*,agents.*,agentcommissions.id as commissionid,
                agentcommissions.user_id as commission_user_id,
                agentcommissions.commission as commissionamount,
                agents.id as agentid,tickets.payment_status,tickets.refund as is_refund, tickets.cancel_status as is_cancel, refunds.refund_fee,cancels.cancel_fee,
                companies.name as company_name',
            )
            ->join('agents', 'agents.id  = agentcommissions.agent_id')
            ->join('subtrips', 'subtrips.id   = agentcommissions.subtrip_id')
            ->join('trips', 'trips.id = subtrips.trip_id')
            ->join('tickets', 'tickets.booking_id = agentcommissions.booking_id','left')
            ->join('refunds', 'refunds.booking_id = agentcommissions.booking_id','left')
            ->join('cancels', 'cancels.booking_id = agentcommissions.booking_id','left')
            ->join('companies', 'companies.id = trips.company_id');
        if ($companyId != "all") {
            $this->agentCommissionModel->where('companies.id', $companyId);
        }
        $commission = $commission->findAll();
        

        foreach ($commission as $key => $cvalue) {
            foreach ($passangerinfo as $nkey => $pvalue) {
                if ($pvalue->user_id == $cvalue->commission_user_id) {
                    $commission[$key]->commission_user_id = $pvalue->first_name . ' ' . $pvalue->last_name;
                }
            }

            $commission[$key]->first_name = $cvalue->first_name . ' ' . $cvalue->last_name;
        }
        $data['commission'] = $commission;

        if ($userType == 2) {
            $agentId =  $this->session->get('user_id');
            $agentData = $this->agenttModel->where('user_id', $agentId)->findAll();
        } else {
            $agentData = $this->agenttModel->findAll();
        }

        $data['companies'] = $this->companyModel->select('id, name')->findAll();
        $data['agentList'] = $agentData;
        $data['userType'] = $userType;
        $data['filepath'] =  $this->Viewpath;
        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.agent_report");
        $data['currency_symbol']  =    $this->session->get('currency_symbol');
        $data['role_id'] = (int) $userRole;
        $data['s_agent_id'] = $agentid;
        $data['s_company_id'] = $companyId;
        $data['s_startdate'] = $fromDate;
        $data['s_enddate'] = $toDate;
        return view($this->Viewpath . '\report/agentcommission', $data);
    }

    public function agentSumReportLoad()
    {

        $userRole = $this->session->get('role_id');
        $data['role_id'] = (int) $userRole;
        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();
        $userchek = new UserCheck();
        $agentData = "";
        $userType = $userchek->getUserType();

        if ($userType == 2) {
            $agentId =  $this->session->get('user_id');
            $agentData = $this->agenttModel->where('user_id', $agentId)->findAll();
        } else {
            $agentData = $this->agenttModel->findAll();
        }

        $data['agentList'] = $agentData;
        $data['userType'] = $userType;
        $data['filepath'] =  $this->Viewpath;

        $data['ticket'] = array();
        $data['expenseticket'] = array();

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.sum_report");

        $heading = lang("Localize.agent") . ' ' . lang("Localize.sum_report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');

        return view($this->Viewpath . '\sumreport\sumreport', $data);
    }

    public function agentSumReportDetails()
    {
        $userRole = $this->session->get('role_id');
        $data['role_id'] = (int) $userRole;
        $data['companies'] = $this->companyModel->select('id, name')->where('status', 1)->findAll();

        $userchek = new UserCheck();
        $agentData = "";
        $userType = $userchek->getUserType();

        if ($userType == 2) {
            $agentId =  $this->session->get('user_id');
            $agentData = $this->agenttModel->where('user_id', $agentId)->findAll();
        } else {
            $agentData = $this->agenttModel->findAll();
        }

        $data['agentList'] = $agentData;
        $data['userType'] = $userType;


        $fromDate = $this->request->getVar('start_date');
        $toDate = $this->request->getVar('end_date');
        // $maintripId = $this->request->getVar('trip_id');
        // $subTripId = $this->request->getVar('subtrip_id');
        $ticketType = $this->request->getVar('type');
        $agentid = $this->request->getVar('agent_id');
        $companyId = $this->request->getVar('company_id');

        $agentDetail = $this->agenttModel->find($agentid);
        // dd($agentDetail->user_id);
        if ($agentid != "all") {

            $this->ticketModel->where('tickets.bookby_user_id ', $agentDetail->user_id);
        }

        // if ($ticketType == "normal") {

        // 	$this->ticketModel->where('refund',0)->where('cancel_status',0);
        // }
        // if ($ticketType == "refund") {

        // 	$this->ticketModel->where('refund',1);
        // }
        // if ($ticketType == "cancel") {
        // 	$this->ticketModel->where('cancel_status',1);
        // }

        // if($maintripId == "all")
        // {


        // }

        $this->ticketModel->where('refund', 0)->where('cancel_status', 0);
        
        $this->ticketModel->where('DATE(tickets.created_at) >=', $fromDate)->where('DATE(tickets.created_at) <=', $toDate);


        $ticket = $this->ticketModel
            ->select('MAX(tickets.created_at) as date,
                MAX(schedules.start_time) AS start_time, MAX(schedules.end_time) AS end_time,
                MAX(a.name) as pickup_location_name, MAX(b.name) as drop_location_name,
                MAX(c.name) as sub_pickup_location_name, MAX(d.name) as sub_drop_location_name')
            ->selectSum('price')
            ->selectSum('totalseat')
            ->selectSum('discount')
            ->selectSum('totaltax')
            ->selectSum('paidamount')
            ->groupBy('trips.id')
            ->join('trips', 'trips.id = tickets.trip_id')
            ->join('companies', 'companies.id = trips.company_id')
            ->join('subtrips', 'subtrips.id = tickets.subtrip_id')
            ->join('schedules', 'schedules.id = trips.schedule_id')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('locations c', 'c.id = subtrips.pick_location_id', 'left')
            ->join('locations d', 'd.id = subtrips.drop_location_id', 'left')
            ->where('trips.status', 1);

        $ticket = $ticket->findAll();

        $data['filepath'] =  $this->Viewpath;

        $data['ticket'] = $ticket;


        // for refund and cancle

        $this->ticketModel->groupStart();
        $this->ticketModel->where('refund', 1);
        $this->ticketModel->orwhere('cancel_status', 1);
        $this->ticketModel->groupEnd();



        $agentid = $this->request->getVar('agent_id');

        $agentDetail = $this->agenttModel->find($agentid);
        
        if ($agentid != "all") {
            $this->ticketModel->where('tickets.bookby_user_id ', $agentDetail->user_id);
        }

        $this->ticketModel->where('DATE(tickets.created_at) >=', $fromDate)->where('DATE(tickets.created_at) <=', $toDate);

        $expenseticket = $this->ticketModel
            // ->select('tickets.*,
            //     trips.id as tripid,
            //     trips.*,
            //     subtrips.id as subtripid,
            //     subtrips.*,
            //     schedules.*,
            //     a.name as pickup_location_name,
            //     b.name as drop_location_name,
            //     c.name as sub_pickup_location_name,
            //     d.name as sub_drop_location_name')

            ->select('MAX(tickets.booking_id) AS booking_id,
                MAX(tickets.
                created_at) AS date,
                MAX(schedules.start_time) AS start_time, MAX(schedules.end_time) AS end_time,
                MAX(a.name) as pickup_location_name, MAX(b.name) as drop_location_name,
                MAX(c.name) as sub_pickup_location_name, MAX(d.name) as sub_drop_location_name')
            ->selectSum('price')
            ->selectSum('totalseat')
            ->selectSum('discount')
            ->selectSum('totaltax')
            ->selectSum('paidamount')
            ->groupBy('trips.id')
            ->join('trips', 'trips.id = tickets.trip_id')
            ->join('subtrips', 'subtrips.id = tickets.subtrip_id')
            ->join('schedules', 'schedules.id = trips.schedule_id')
            ->join('locations a', 'a.id = trips.pick_location_id', 'left')
            ->join('locations b', 'b.id = trips.drop_location_id', 'left')
            ->join('locations c', 'c.id = subtrips.pick_location_id', 'left')
            ->join('locations d', 'd.id = subtrips.drop_location_id', 'left')
            ->where('trips.status', 1)

            ->findAll();
            //echo "<pre>";print_r($expenseticket);exit;


        $data['expenseticket'] = $expenseticket;

        $data['module'] =    lang("Localize.report");
        $data['title']  =    lang("Localize.ticket_sold");

        $heading = lang("Localize.agent") . ' ' . lang("Localize.sum_report");
        $data['pageheading'] = $heading;

        $data['currency_symbol']  =    $this->session->get('currency_symbol');

        $data['s_agent_id'] = $agentid;
        $data['s_company_id'] = $companyId;
        $data['s_startdate'] = $fromDate;
        $data['s_enddate'] = $toDate;

        echo view($this->Viewpath . '\sumreport\sumreport', $data);
    }
}
