EVOLUTION-NINJA

Edit File: Reports.php

<?php

namespace App\Controllers;
use App\Models\Reports_model;
use App\Models\Branch_model;
use CodeIgniter\I18n\Time;

class Reports extends BaseController
{
    
      public function __construct()
    {
        $this->db = \Config\Database::connect();
    }
    
    public function access_details()
    {
       
        $gssModel = new Reports_model();
        
        
        $id = session()->get('admin_id');
        
        
        $table = 'gss_login';
        $where = ['user_id' => $id];
        $user = $gssModel->get_where_row($table, $where);
        
        
        $user_type_id = $user->user_type_id;

$table = 'gss_access_controls';
        $where = ['department_id' => $user_type_id];

return $gssModel->get_where_result($table, $where);
    }
    
    public function access_id()
    {
        // Initialize the GssModel
        $gssModel = new Reports_model();
        
        // Get the admin ID from the session
        $id = session()->get('admin_id');
        
        // Get user details from gss_login table
        $table = 'gss_login';
        $where = ['user_id' => $id];
        $user = $gssModel->get_where_row($table, $where);
        
        // Return the user_type_id
        return $user->user_type_id;
    }
    
    
    public function reports()
    {
        // Get session data
        $admin_id      = session()->get('admin_id');
        $land_owner_id = session()->get('land_owner_id');
        
        // Check if the user is logged in
        if ($admin_id)
        {
            // Initialize the GssModel
            $gssModel = new Reports_model();
            
            // Define tables and where conditions
            $broker_table       = 'gss_brokers';
            $web_table          = 'gss_webportals';
            $project_table      = 'gss_new_projects';
            $land_table1        = 'gss_login';
            $where_associate    = ['delete_status' => 'ACTIVE', 'type' => 'Associate'];
            $where_subassociate = ['delete_status' => 'ACTIVE', 'type' => 'Sub Associate'];
            $where_logistics    = ['delete_status' => 'ACTIVE', 'type' => 'Logistic'];
            $where_reference    = ['delete_status' => 'ACTIVE', 'type' => 'Executives'];
            $where_portal       = ['delete_status' => 'ACTIVE'];
            $where_project      = ['delete_status' => 'ACTIVE'];
            $where1             = ['delete_status' => 'ACTIVE', 'user_type_id' => '5'];
            
            // Define order by clauses
            $portals_order_by   = 'webportal';
            $associate_order_by = 'associate_name';
            $projects_order_by  = 'project_name';
            $order_by1          = 'username';
            
            // Fetch data from the database using the model
            $data['webportals']   = $gssModel->get_where_result_orderby_asc($web_table, $where_portal, $portals_order_by);
            $data['associates']   = $gssModel->get_where_result_orderby_asc($broker_table, $where_associate, $associate_order_by);
            $data['subassociates'] = $gssModel->get_where_result_orderby_asc($broker_table, $where_subassociate, $associate_order_by);
            $data['logistics']    = $gssModel->get_where_result_orderby_asc($broker_table, $where_logistics, $associate_order_by);
            $data['reference']    = $gssModel->get_where_result_orderby_asc($broker_table, $where_reference, $associate_order_by);
            $data['users']        = $gssModel->get_where_result_orderby_asc($land_table1, $where1, $order_by1);
            $data['projects']     = $gssModel->get_projects_ownerwise($admin_id, $land_owner_id);
            
            // Additional data
            $data['user_type_id'] = $this->access_id();
            $data['access']       = $this->access_details();
            
            // Load the view
            return view('admin/reports', $data);
        }
        else
        {
            return redirect()->to('/');
        }
    }
    
    public function check_project_type_status()
    {
        
        $status = service('request')->getPost('value');
        
        
        if ($status == "ALL") {
            $where = ['delete_status' => 'ACTIVE'];
        } else {
            $where = ['project_status' => $status, 'delete_status' => 'ACTIVE'];
        }

$order_by = 'project_name';
        $table = 'gss_new_projects';

$admin_id = session()->get('admin_id');
        $land_owner_id = session()->get('land_owner_id');

$gssModel = new Reports_model();
        $check_status = $gssModel->get_projects_landownerwise($admin_id, $land_owner_id, $status);

if ($check_status) {
            return $this->response->setJSON(['result' => 1, 'message' => $check_status]);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'No Data Found']);
        }
    }
    
    public function get_balance_sqft()
    {
      
        $project = service('request')->getPost('project_id');

$gssModel = new Reports_model();

$tot_project_sqft = $gssModel->get_total_project_sqft($project);
        $booked_sqft = $gssModel->get_booked_project_sqft($project);

$booking_sqft = 0;
        $balance = 0;

foreach ($tot_project_sqft as $tps) {
            $total_project_sqft = $tps->total_in_sqft;

foreach ($booked_sqft as $bs) {
                $booking_sqft = $bs->booked_sqft;
            }

$balance = $total_project_sqft - $booking_sqft;
        }

if ($balance) {
            return $this->response->setJSON(['result' => 1, 'balance_sqft' => $balance]);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'No data found']);
        }
    }
    
    public function get_booking_reports()
    {
        // Get input data from the GET request using CodeIgniter 4 service
        $from_date = service('request')->getGet('from_date');
        if ($from_date != "") {
            $date = new \DateTime($from_date);
            $from_date = $date->format('Y-m-d');
        }
        
        $to_date = service('request')->getGet('to_date');
        if ($to_date != "") {
            $date = new \DateTime($to_date);
            $to_date = $date->format('Y-m-d');
        }
        
        $project = service('request')->getGet('project');
        $reference = service('request')->getGet('reference', FILTER_SANITIZE_NUMBER_INT);
        $logistic = service('request')->getGet('logistic', FILTER_SANITIZE_NUMBER_INT);
        $associate = service('request')->getGet('associate', FILTER_SANITIZE_NUMBER_INT);
        $ported = service('request')->getGet('ported', FILTER_SANITIZE_NUMBER_INT);
        
        // Set default values if the parameters are not set
        $reference = $reference != "" ? $reference : 0;
        $logistic = ($logistic != 'undefined') ? $logistic : 0;
        $associate = ($associate != 'undefined') ? $associate : 0;
        $ported = ($ported != 'undefined') ? $ported : 0;
        
       
        $gssModel = new Reports_model();
        $result = $gssModel->booking_reports($from_date, $to_date, $project, $reference, $logistic, $associate, $ported);
        
        
        
     
        return $this->response->setJSON($result);
    }
    
    public function get_payment_reports()
    {
  
        $gssModel = new Reports_model();

$from_date = $this->request->getGet('from_date');
        $to_date = $this->request->getGet('to_date');
        $p_poject = $this->request->getGet('p_poject');

if (!empty($from_date)) {
            $date = new \DateTime($from_date);
            $from_date = $date->format('Y-m-d');
        }

if (!empty($to_date)) {
            $date = new \DateTime($to_date);
            $to_date = $date->format('Y-m-d');
        }

$result = $gssModel->payment_reports_receivable($from_date, $to_date, $p_poject);

return $this->response->setJSON($result);
    }

public function get_cancellation_reports()
{
      
        $gss_model = new Reports_model();
    $from_date = $this->request->getGet('from_date');
    $to_date   = $this->request->getGet('to_date');
    $project_id = $this->request->getGet('project');

if (!empty($from_date)) {
        $date      = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

if (!empty($to_date)) {
        $date    = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$result = $gss_model->cancellation_reports($from_date, $to_date, $project_id);

return $this->response->setJSON($result);
}

public function get_associate_reports()
    {
        $db = \Config\Database::connect();
        $request = \Config\Services::request();

$from_date = $this->request->getGet('from_date');
        $to_date = $this->request->getGet('to_date');

if (!empty($from_date)) {
            $from_date = date('Y-m-d', strtotime($from_date));
        }

if (!empty($to_date)) {
            $to_date = date('Y-m-d', strtotime($to_date));
        }

// Build main query
        $builder = $db->table('gss_bookings A')
            ->select("
                A.booking_status, A.booking_id, A.project_id, A.customer_name, A.address, A.webportal,
                A.logistics, A.reference, A.associate, A.subassociate, A.email as customer_email,
                A.mobile1, A.mobile2, C.booking_date1 as booked_on, B.project_name, C.site_number,
                C.dimension, C.booking_date1, D.management_id, D.total_commission_rs, 
                E.payment_date, E.commission_type, E.payment_type, E.tds, E.amount, 
                E.with_tax, E.bank_name
            ")
            ->join('gss_new_projects B', 'B.project_id = A.project_id')
            ->join('gss_booking_details C', 'C.booking_id = A.booking_id')
            ->join('gss_management D', 'D.booking_id = A.booking_id')
            ->join('gss_commission_payments E', 'D.management_id = E.management_id')
            ->where('A.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('C.delete_status', 'ACTIVE')
            ->where('D.delete_status', 'ACTIVE')
            ->where('E.delete_status', 'ACTIVE');

if (!empty($from_date)) {
            $builder->where('E.payment_date >=', $from_date);
        }

if (!empty($to_date)) {
            $builder->where('E.payment_date <=', $to_date);
        }

$builder->orderBy('B.project_name', 'ASC');
        $builder->orderBy('ABS(C.site_number)', 'ASC');

$results = $builder->get()->getResult();

$array = [];

foreach ($results as $val) {
            $data = [];

$data['booking_id'] = $val->booking_id;
            $data['booking_date'] = $val->booking_date1;
            $data['customer_name'] = $val->customer_name;
            $data['address'] = $val->address;
            $data['customer_mobile'] = $val->mobile1;
            $data['customer_mobile2'] = $val->mobile2;
            $data['customer_email'] = $val->customer_email;
            $data['project_name'] = $val->project_name;
            $data['site_number'] = $val->site_number;

// Get dimension from gss_new_sites
            $site_builder = $db->table('gss_new_sites')
                ->select('total_in_sqft')
                ->where([
                    'delete_status' => 'ACTIVE',
                    'project_id' => $val->project_id,
                    'site_number' => $val->site_number
                ])->get()->getRow();

$data['dimension'] = $site_builder ? $site_builder->total_in_sqft : '';

$data['booked_on'] = $val->booked_on;
            $data['commission'] = $val->total_commission_rs;
            $data['booking_status'] = $val->booking_status;
            $data['commission_type'] = $val->commission_type;
            $data['payment_type'] = $val->payment_type;
            $data['grass_wc'] = $val->amount;
            $data['grass'] = $this->format_amount($val->amount);
            $data['tds'] = $val->tds;
            $data['net_amount_wc'] = $val->with_tax;
            $data['net_amount'] = $this->format_amount($val->with_tax);
            $data['bank'] = $val->bank_name;

// Get subcommission if exists
            $sub = $db->table('gss_sub_commission_payments')
                ->where('delete_status', 'ACTIVE')
                ->where('management_id', $val->management_id)
                ->get()->getRow();

if ($sub) {
                $data['subgrass_wc'] = $sub->amount;
                $data['subgrass'] = $this->format_amount($sub->amount);
                $data['subtds'] = $sub->tds;
                $data['subpayment_type'] = $sub->payment_type;
                $data['subwith_tax_wc'] = $sub->with_tax;
                $data['subwith_tax'] = $this->format_amount($sub->with_tax);
                $data['subbank'] = $sub->bank_name;
                $data['subcommission_type'] = $sub->commission_type;
            } else {
                $data['subgrass'] = "";
                $data['subtds'] = "";
                $data['subpayment_type'] = "";
                $data['subwith_tax'] = "";
                $data['subbank'] = "";
                $data['subcommission_type'] = "";
            }

// Get associate name
            if ($val->associate != 0) {
                $associate = $db->table('gss_brokers A')
                    ->select('A.associate_name as associate')
                    ->join('gss_bookings B', 'B.associate = A.broker_id')
                    ->where('A.broker_id', $val->associate)
                    ->where('B.delete_status', 'ACTIVE')
                    ->get()->getRow();
                $data['associate'] = $associate ? $associate->associate : "";
            } else {
                $data['associate'] = "";
            }

// Get subassociate name
            if ($val->subassociate != 0) {
                $subassociate = $db->table('gss_brokers A')
                    ->select('A.associate_name as subassociate')
                    ->join('gss_bookings B', 'B.subassociate = A.broker_id')
                    ->where('A.broker_id', $val->subassociate)
                    ->where('B.delete_status', 'ACTIVE')
                    ->get()->getRow();
                $data['subassociate'] = $subassociate ? $subassociate->subassociate : "";
            } else {
                $data['subassociate'] = "";
            }

$array[] = $data;
        }

return $this->response->setJSON($array);
    }

private function format_amount($num)
    {
        if (strlen($num) > 3) {
            $lastthree = substr($num, -3);
            $restunits = substr($num, 0, -3);
            $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
            $expunit = str_split($restunits, 2);
            $explrestunits = "";
            foreach ($expunit as $i => $val) {
                $explrestunits .= ($i == 0) ? (int)$val . "," : $val . ",";
            }
            return $explrestunits . $lastthree;
        } else {
            return $num;
        }
}

public function get_client_reports()
	{
		$from_date  = $this->request->getGet('from_date');
		   $gss_model=new Reports_model();
		if($from_date != "")
		{
			$date       = new \DateTime($from_date);
        	$from_date  = $date->format('Y-m-d');
		}
		
		$to_date    = $this->request->getGet('to_date');
		if($to_date != "")
		{
			$date       = new \DateTime($to_date);
        	$to_date    = $date->format('Y-m-d');
		}
	
		$c_project = $this->request->getGet('project');
		
		$reference = $this->request->getGet('reference');
				
		$result     = $gss_model->client_reports($from_date,$to_date,$c_project,$reference);
return $this->response->setJSON($result ?: ['result' => 0]);
}

//preethi

public function get_maintenance_due_reports()
{
    $project_id      = $this->request->getGet('project_id');
    $m_project_type  = strtoupper($this->request->getGet('m_project_type'));

$gss_model = new Reports_model;
    $result    = $gss_model->get_maintenance_due_reports($project_id, $m_project_type);

if ($result) {
        return $this->response->setJSON($result);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}

public function get_maintenance_reports()
{
    $gss_model=new Reports_model();
    $project_id      = $this->request->getGet('project_id');
    $m_project_type  = $this->request->getGet('m_project_type');

$result = $gss_model->get_maintenance_reports($project_id, $m_project_type);

return $this->response->setJSON($result ?: ['result' => 0]);
}

public function get_follow_ups_reports()
{
    $gss_model = new Reports_model();

$project = $this->request->getGet('project');
    $reminder_date = $this->request->getGet('reminder_date');

if (!empty($reminder_date)) {
        $date = new \DateTime($reminder_date);
        $reminder_date = $date->format('d-m-Y');
    }

$result = $gss_model->get_follow_ups_reports($project, $reminder_date);

if ($result) {
        return $this->response->setJSON($result); // Already an array of results
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}

public function single_site_details()
        {
            $gss_model=new Reports_model();
             $db = \Config\Database::connect();

$project_id = $this->request->getUri()->getSegment(2);
            $site_number = $this->request->getUri()->getSegment(3);
            $details      = $gss_model->test($project_id,$site_number);
        
            if(empty($details))
            {
                $data['bookin_details']         = '';
                $data['payment_ind']            = '';
                $data['agreement_details']      = '';
                $data['installment_details']    = '';
                $data['registration1_details']  = '';
                $data['others']                 = '';
                
                /*?>
                    <script>alert('This Site is Not Booked');
                    window.location = '<?php echo site_url("reports")?>';
                    </script>
                <?php*/
                            
            }
            else
            {
                $data['bookin_details']=$details;
                //print_r($data['bookin_details']);die();
                $booking_id      = $data['bookin_details']->b_id;
                
                $data['payment_ind'] = $gss_model->payment_ind($booking_id);
                $data['agreement_details']      = $gss_model->agreement_payment_details($booking_id);
                $data['installment_details']    = $gss_model->installment_payment_details($booking_id);
                $data['registration1_details']  = $gss_model->registration_payment_details($booking_id);
                $data['others']                 = $gss_model->payment_withoutinstall($booking_id);
                //print_r($data['others']);die();
    
                $tbale='gss_registration_amount_details';
                $condition=array('booking_id'=>$booking_id,'delete_status'=>'ACTIVE');
                $get_reg_amt=$gss_model->get_where_result($tbale,$condition);
                if($get_reg_amt)
                {
                    $data['more_registration_details']=$get_reg_amt;
                }
                else
                {
                    $data['more_registration_details']='';
                }
        
            }
            
            $table='gss_new_projects';
            $where=array('project_id'=>$project_id);

$data['project_name']=$gss_model->get_where_row($table,$where);
            $data['site_no']=$site_number ;

$data['project_id'] = $project_id;
            $data['site_number'] = $site_number;

$data['user_type_id'] = $this->access_id();
            $data['access'] = $this->access_details();

$data['user_type_id'] = $this->access_id();
            $data['access'] = $this->access_details();
            
            return view('admin/report_status_details',$data);
            
        }
        
        public function single_project_sites()
        {
            $gss_model=new Reports_model();
            $db = \Config\Database::connect();
            $table       = 'gss_new_sites';
            $project_id  = $this->request->getpost('project_id');
            $where   	 = array('project_id' => $project_id);
            $order_by    = 'site_number';
            $result 		= $gss_model->single_project_sites($table,$where,$order_by);
            //print_r($result);die();
            if($result)
            {
                return $this->response->setJSON(['result'=>1,'sites'=>$result]);
            }
            else
            {
                return $this->response->setJSON(['result'=>0]);
            }
        }
        
        public function view_cancelation_details()
        {
            $admin_id              = session()->get('admin_id');
        
            if($admin_id)
            {
                $gss_model=new Reports_model();
                $db = \Config\Database::connect();
                $project = $this->request->getUri()->getSegment(2);
                $site_number = $this->request->getUri()->getSegment(3);
                //print_r($site_number);die();
                $broker_table       = 'gss_brokers';
                $project_table      = 'gss_new_projects';
                $where_project      = array('delete_status'=>'ACTIVE','project_status'=>'ONGOING','project_id'=>$project);
                $where_reference    = array('delete_status'=>'ACTIVE','type'=>'Executives');
                $where_associate    = array('delete_status'=>'ACTIVE','type'=>'Associate');
                $data['associates'] = $gss_model->get_where_result($broker_table,$where_associate);
                $data['projects']   = $gss_model->get_where_result($project_table,$where_project);
                $data['reference']  = $gss_model->get_where_result($broker_table,$where_reference);
                $data['user_type_id'] = $this->access_id();
                $data['access'] = $this->access_details();

$data['project_id'] = $project;
$data['site_number'] = $site_number;

return view('admin/view_cancelation_details',$data);
            }
            else
            {
                redirect('/');
            }	
        }
        
        public function site_cancellation_list()
        {
            $gss_model=new Reports_model();
            $db = \Config\Database::connect();
            $project_id = $_GET['project_id'];
            $site_number=$_GET['site_number'];
            //print_r($site_number);
            $result = $gss_model->site_cancellation_list($project_id,$site_number);
            //print_r($result);die();
            if($result)
            {
                return $this->response->setJSON([$result]);
            }
            else
            {
                return $this->response->setJSON(['result'=>0]);
            }
        }
        
        
         public function send_site_reports()
        {
        
            
            $admin_id = session()->get('admin_id');
            if($admin_id)
            {
                $gss_model=new Reports_model();
                $project_id          = $this->request->getpost('project_id');
                $site_number         = $this->request->getpost('site_number');
                $booking_id         = $this->request->getpost('booking_id');
                
                $total_amount_1         = $this->request->getpost('total_amount_1');
                $balance_amount_1  = $this->request->getpost('balance_amount_1');
                
                $table ='gss_login';
                $where = array('user_id'=>$admin_id);
                $user  = $gss_model->get_where_row($table,$where);
                
                $logged_in_email = $user->email;
        
                $where_b_id = array('booking_id'=>$booking_id);
                $booking_table = 'gss_bookings';
                $client = $gss_model->get_where_row($booking_table,$where_b_id);
             
                $booking_detail_table = 'gss_booking_details';
                $client_site = $gss_model->get_where_row($booking_detail_table,$where_b_id);
                $client_email = $client->email;
               
                
                $project_table = 'gss_new_projects';
                $project_id = $client->project_id;
                $where_project = array('project_id'=>$project_id);
                $project = $gss_model->get_where_row($project_table,$where_project);
                
                $land_owner            = $project->land_owner_id;
                $land_owner_table      = 'gss_land_owners';
                $where_land_owner      = array('owner_id'=>$land_owner);
                $get_land_owner        = $gss_model->get_where_row($land_owner_table,$where_land_owner);
                $land_owner_address    = $get_land_owner->address;
               
                $client_name = $client->customer_name;
                $project_name = $project->project_name;
                $site_number = $client_site->site_number;
             
                $particulars = 'Site Details Report';
              
                $subject = $project_name.', '.$site_number.' and '.$client_name.' - '.$particulars;
                
                $message = 'Dear '.$client_name.', Please find enclosed the Receipt Details of '.$particulars;
                
                $table         = 'gss_email_details';
                
                $date = new \DateTime('now', new \DateTimeZone('Asia/Kolkata'));
                 $created_at       = $date->format('Y-m-d H:i:s');
                 
                 $today             = $date->format('d-m-Y');
                 
                     $details      = $gss_model->test($project_id,$site_number);
                if(empty($details))
                {
                    $data['bookin_details']='';
                    $data['payment_list']='';
                    $data['cancellation']='';
                    $data['payment_ind'] ='';
                    $data['tsv_list'] ='';
                    $data['without_install'] ='';
                    $data['without_agree'] ='';
                    ?>
                        <script>alert('This Site is Not Booked');
                        window.location = '<?php echo site_url("reports")?>';
                        </script>
                    <?php
                            
                }
                else
                {
                    $bookin_details = $details;
                    $booking_id      = $bookin_details->booking_id;
                    //$data['payment'] = $this->gss_model->payment_details($booking_id);
                    
                    $payment_ind = $gss_model->payment_ind($booking_id);
                    if(!empty($payment_ind->sales_agreement_due_date))
                    {
                    $sales_agreement_due_date = date("d-m-Y", strtotime($payment_ind->sales_agreement_due_date));
                    }
                    else
                    {
                      $sales_agreement_due_date = "";  
                    }
                    if(!empty($payment_ind->installment_due_date))
                    {
                    $installment_due_date = date("d-m-Y", strtotime($payment_ind->installment_due_date));
                    }
                    else
                    {
                       $installment_due_date = ""; 
                    }
                    if(!empty($payment_ind->registration_due_date))
                    {
                    $registration_due_date =  date("d-m-Y", strtotime($payment_ind->registration_due_date));
                    }
                    else
                    {
                       $registration_due_date=""; 
                    }
        
                    $payment = $gss_model->payment_details($booking_id);
        
                    $data['payment_list'] = $gss_model->payment_detailsgroup($booking_id);
        
                    $without_install = $gss_model->payment_withoutinstall($booking_id);
        
                    $without_agree = $gss_model->payment_withoutagree($booking_id);
        
                    $data['tsv_list'] = $gss_model->payment_details_ind($booking_id);
        
                    $data['cancellation']    = $gss_model->get_cancellation_types_result($booking_id);
        
                    $data['commission']   = $gss_model->commission($booking_id);
        
                    $data['user']         = $gss_model->single_user_account_management_list($booking_id);
        
                    $tbale='gss_registration_amount_details';
                    $condition=array('booking_id'=>$booking_id,'delete_status'=>'ACTIVE');
                    $get_reg_amt=$gss_model->get_where_result($tbale,$condition);
                    if($get_reg_amt)
                    {
                        $data['get_reg_amt']=$get_reg_amt;
                    }
                    else
                    {
                        $data['get_reg_amt']='';
                    }
                
                }
             $num=     $bookin_details->tsv;$explrestunits = "" ;
            if(strlen($num)>3) {
            $lastthree = substr($num, strlen($num)-3, strlen($num));
            $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
            $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
            $expunit = str_split($restunits, 2);
            for($i=0; $i<sizeof($expunit); $i++) {

if($i==0) {
                    $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                } else {
                    $explrestunits .= $expunit[$i].",";
                }
            }
            $tsv_total = $explrestunits.$lastthree;
             } else {
            $tsv_total = $num;
            } 
    
            $tsv = $tsv_total;
            
        
                $site_no=$site_number ;
        
                $number = "";
                $mode_date = "";
                $bank = "";
                
                $data = array('user_email'=>$logged_in_email,
                               'client_email' => $client_email,
                               'client_name' => $client_name,
                               'project_name' => $project_name,
                               'site_number' => $site_number,
                               'particulars' => $particulars,
                               'subject' => $subject,
                               'created_at' => $created_at,
                               'message' => $message);
                               
                $insert_result = $gss_model->insert1($table,$data);
                if($insert_result)
                {
            
          
            
              require_once APPPATH . '../vendor/autoload.php';
                    $mpdf = new \Mpdf\Mpdf();
                    
                    $html = '';
                    $index = 1;
            
   
                    
                     $email_content = '';
                     $email_content.= '<html>';
                     $email_content.= '<head>';
                     $email_content.= '</head>';
                     $email_content.= '<body style="width:900px;display:block;margin:auto !important; border-left:10px solid #E3000F !important;padding-left: 100px !important;padding-right: 100px !important;padding-top: 50px !important;padding-bottom: 50px !important;">';
                     $email_content.= '<div style="padding-bottom:10px;clear:both;">';
                     if($get_land_owner->name == 'GSS Project Consultants')
                     {
                        $email_content.= '<h2 style="text-align:center;padding-bottom:5px;">GSS Project Consultants Pvt. Ltd.</h2>';
                     }
                     else
                     {
                        $email_content.= '<h2 style="text-align:center;padding-bottom:5px;">Acknowledgement</h2>';	 
    
                     }
                         
                     $email_content.= '<img src="assets/images/header_line.png" style="display:block;margin:auto;padding-left:45%;">';
                     $email_content.= "<h2 style='font-size: 28px;line-height: 1.3;text-align:center;border-top:10px solid #E3000F !important;'>Project Name: $project_name <br>Address: $land_owner_address";
                     $email_content.= '</div>';
                
                    $email_content.= '<div style="padding-bottom:15px;padding-top:15px;margin-bottom: 5px;position:relative;clear:both;">';
                    $email_content.= '<img src="assets/images/line.png" style="display:block;margin:auto;width: 100%;">';		
                    $email_content.= '<div style="padding-top:10px;padding-bottom:10px;">';
                    $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                    $email_content.= '<tr>';
                    $email_content.= "<td><p style='margin:0;font-size:18px;font-weight:bold;padding-top: 3px;'>Date: $today</p></td>";
                    $email_content.= "<td><h3 style='margin-bottom:0px;margin-top:0px;text-align:center;color:#E40025;font-weight:bold;'><p>TSV: $tsv</p></h3></td>";
                    $email_content.= '</tr>';
                    $email_content.= '</table>';		
                    $email_content.= '</div>';		
                    $email_content.= '<img src="assets/images/line.png" style="display:block;margin:auto;width: 100%;">';
                    $email_content.= '</div>';
                    
                    $email_content.= '<div style="padding-bottom:20px;clear:both;">';
                    $email_content.= '<div>';
                    $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                    $email_content.= '<tr>';
                    $email_content.= "<td><p style='padding-left: 50px;margin:0;font-size:18px;font-weight:bold;padding-top: 3px;'>Client Name: $client_name</p></td>";
                    $email_content.= "<td><p style='padding-right:50px;margin:0;text-align:right;font-size:18px;font-weight:bold;padding-top: 3px;'>Site No: $site_no</p></td>";
                    $email_content.= '</tr>';
                    $email_content.= '</table>';		
                    $email_content.= '</div>';
                    $email_content.= '</div>';
                    
                  
                    
                    $email_content.= '<h3 style="margin-left:6%;">Received Details</h3>';
            
                    $total=0;
                    
                    if($without_agree->booking_amount1 >0)
                    {
                        $without_agree->booking_amount1;
                        $num=$without_agree->booking_amount1;
                        $explrestunits = "" ;
                        if(strlen($num)>3)
                        {
                            $lastthree = substr($num, strlen($num)-3, strlen($num));
                            $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                            $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                            $expunit = str_split($restunits, 2);
                            for($i=0; $i<sizeof($expunit); $i++)
                            {
                            // creates each of the 2's group and adds a comma to the end
                                if($i==0)
                                {
                                    $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                }
                                else
                                {
                                    $explrestunits .= $expunit[$i].",";
                                }

$thecash = $explrestunits . $lastthree;
                            }
                        }
                        else
                        {
                            $thecash = $num;
                        }
                        $total += $num;
                    }
                    $booking_particularss2 = '';
                    $booking_date1 ="";
                    if(!empty($without_agree->booking_date1))
                    {
                            $booking_date1 = date("d-m-Y", strtotime($without_agree->booking_date1));
                    }
                    else
                    {
                        $booking_date1 ="";
                    }
            
                    $booking_particularss = $without_agree->booking_payment_type;
                    if($booking_particularss == "Online Payment")
                    {
                        $booking_particulars = 'NEFT / RTGS';
                    }
                    else if($booking_particularss == "Paytm Payment")
                    {
                        $booking_particulars = 'UPI / Direct payment';
                    }
                    else if($booking_particularss == "UPI Payment")
                    {
                        $booking_particulars = 'Credit / debit Payment';
                    }
                    else
                    {
                        $booking_particulars = $booking_particularss;
                    }
            
                    if($booking_particularss == "Cheque")
                    {
                        $number = $without_agree->check_no;
                        $mode_date = date("d-m-Y", strtotime($without_agree->check_date));
                        $bank =$without_agree->bank_name;
                        //$b_particulars = 'Payment Mode: Cheque,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
                        $b_particulars1 = 'Payment Mode: Cheque';
                        $b_particulars11 =   ' No: '.$number;
                         $b_particulars12 = 'Date: '.$mode_date;
                        $b_particulars13= 'Bank: '.$bank;
                    }
                    else if($booking_particularss == "Online Payment")
                    {
                        $number = $without_agree->vtr_no;
                        $mode_date = date("d-m-Y", strtotime($without_agree->online_date));
                        $bank = '';
                        //$b_particulars = 'Payment Mode: NEFT/RTGS,  No: '.$number.',  Date: '.$mode_date;
                        $b_particulars1 = 'Payment Mode: NEFT/RTGS';
                        $b_particulars11 =  'No: '.$number;
                         $b_particulars12 = 'Date: '.$mode_date;
                        $b_particulars13 ='';
                    }
                    else if($booking_particularss == "DD")
                    {
                        $number = $without_agree->dd_no;
                        $mode_date = date("d-m-Y", strtotime($without_agree->dd_date));
                        $bank = $without_agree->dd_bank;
                        //$b_particulars = 'Payment Mode: DD,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
                        $b_particulars1 = 'Payment Mode: DD';
                        $b_particulars11 =  'No: '.$number;
                         $b_particulars12 = 'Date: '.$mode_date;
                        $b_particulars13 = ' Bank: '.$bank;
                    }
                    else if($booking_particularss == "Paytm Payment")
                    {
                        $number = $without_agree->paytm_ref_no;
                        $mode_date = date("d-m-Y", strtotime($without_agree->paytm_online_date));
                        $bank = '';
                        //$b_particulars = 'Payment Mode: UPI / Direct Payment,  No: '.$number.',  Date: '.$mode_date;
                        $b_particulars1 = 'Payment Mode: UPI / Direct Payment';
                        $b_particulars11 =  'No: '.$number;
                        $b_particulars12 = ' Date: '.$mode_date;
                        $b_particulars13 = '';
                    }
                    else if($booking_particularss == "UPI Payment")
                    {
                        $number = $without_agree->upi_ref_no;
                        $mode_date = date("d-m-Y", strtotime($without_agree->upi_online_date));
                        $bank = '';
                        //$b_particulars = 'Payment Mode: Credit / Debit Payment ,  No: '.$number.',  Date: '.$mode_date;
                        $b_particulars1 = 'Payment Mode: Credit / Debit Payment';
                        $b_particulars11 =  'No: '.$number;
                         $b_particulars12 = 'Date: '.$mode_date;
                        $b_particulars13 = '';
                    }
    
                    else if($booking_particularss == "Cash")
                    {
                        $b_particulars1 = 'Cash'	;
                        $b_particulars11 ='';
                        $b_particulars12 ='';
                        $b_particulars13 ='';
    
                    }
                    else if($booking_particularss == "Swipe")
                    {
                        $b_particulars1 = 'Swipe'	;
                        $b_particulars11 ='';
                        $b_particulars12 ='';
                        $b_particulars13 ='';
                    }
            
                    $total=0;
                    if($without_agree->booking_amount2 >0)
                    {
                        $without_agree->booking_amount2;
                        $num=$without_agree->booking_amount2;
                        $explrestunits = "" ;
                        if(strlen($num)>3)
                        {
                            $lastthree = substr($num, strlen($num)-3, strlen($num));
                            $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                            $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                            $expunit = str_split($restunits, 2);
                            for($i=0; $i<sizeof($expunit); $i++)
                            {
                            // creates each of the 2's group and adds a comma to the end
                                if($i==0)
                                {
                                    $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                }
                                else
                                {
                                    $explrestunits .= $expunit[$i].",";
                                }
            
                                $thecash1 = $explrestunits.$lastthree;
                            }
                        }
                        else
                        {
                           $thecash1 = $num;
                        }
                        $total=$total+$num;
                    }
                    
                
                    $booking_date2="";
                        if(!empty($without_agree->booking_date2))
                    {
                        if($booking_date2 != '30-11-0001')
                        {
                         $booking_date2 = date("d-m-Y", strtotime($without_agree->booking_date2));
                        }
                    }
                    else
                    {
                       $booking_date2="";
                    }
            
                        $booking_particularss2 = $without_agree->booking_payment_type2;
                        if($booking_particularss2 == "Online Payment")
                        {
                            $booking_particulars2 = 'NEFT / RTGS';
                        }
                        else if($booking_particularss2 == "Paytm Payment")
                        {
                            $booking_particulars2 = 'UPI / Direct Payment';
                        }
                        else if($booking_particularss2 == "UPI Payment")
                        {
                            $booking_particulars2 = 'Credit / Debit payment';
                        }
                        else
                        {
                            $booking_particulars2 = $booking_particularss2;
                        }
            
                        if($booking_particularss2 == "Cheque")
                        {
                            $number = $without_agree->check_no2;
                            $mode_date = date("d-m-Y", strtotime($without_agree->check_date2));
                            $bank =$without_agree->bank_name2;
                            //$b_particulars2 = 'Payment Mode: Cheque, No: '.$number.', Date: '.$mode_date.', Bank: '.$bank;
    
                            $b_particulars2 = 'Payment Mode: Cheque';
                            $b_particulars21 =   'No: '.$number;
                             $b_particulars22 = 'Date: '.$mode_date;
                            $b_particulars23= 'Bank: '.$bank;
                        }
                        else if($booking_particularss2 == "Online Payment")
                        {
                            $number = $without_agree->vtr_no2;
                            $mode_date = date("d-m-Y", strtotime($without_agree->online_date2));
                            $bank = '';
                            //$b_particulars2 = 'Payment Mode: NEFT/RTGS,  No: '.$number.', Date: '.$mode_date;
    
                            $b_particulars2 = 'Payment Mode: NEFT/RTGS';
                        $b_particulars21 =  'No: '.$number;
                         $b_particulars22 = 'Date: '.$mode_date;
                        $b_particulars23 ='';
                        }
                        else if($booking_particularss2 == "DD")
                        {
                            $number = $without_agree->dd_no2;
                            $mode_date = date("d-m-Y", strtotime($without_agree->dd_date2));
                            $bank = $without_agree->dd_bank2;
                            $b_particulars2 = 'Payment Mode: DD,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                            $b_particulars2 = 'Payment Mode: DD';
                            $b_particulars21 =  'No: '.$number;
                            $b_particulars22 = 'Date: '.$mode_date;
                            $b_particulars23 = ' Bank: '.$bank;
                        }
                        else if($booking_particularss2 == "Paytm Payment")
                        {
                            $number = $without_agree->paytm_ref_no2;
                            $mode_date = date("d-m-Y", strtotime($without_agree->paytm_online_date2));
                            $bank = '';
                            //$b_particulars2 = 'Payment Mode: UPI / Direct Payment,  No: '.$number.', Date: '.$mode_date;
    
                            $b_particulars2 = 'Payment Mode: UPI / Direct Payment';
                            $b_particulars21 =  'No: '.$number;
                            $b_particulars22 = 'Date: '.$mode_date;
                            $b_particulars23 = '';
                        }
                        else if($booking_particularss2 == "UPI Payment")
                        {
                            $number = $without_agree->upi_ref_no2;
                            $mode_date = date("d-m-Y", strtotime($without_agree->upi_online_date2));
                            $bank = '';
                            //$b_particulars2 = 'Payment Mode: Credit / Debit Payment,  No: '.$number.', Date: '.$mode_date;
                            $b_particulars2 = 'Payment Mode: Credit / Debit Payment';
                            $b_particulars21 =  'No: '.$number;
                            $b_particulars22 = 'Date: '.$mode_date;
                            $b_particulars23 = '';
                        }
                        else if($booking_particularss2 == "Cash")
                        {
                            //$b_particulars2 = 'Cash'	;
                            $b_particulars2 = 'Cash'	;
                            $b_particulars21 ='';
                            $b_particulars22 ='';
                            $b_particulars23 ='';
                        }
                        else if($booking_particularss2 == "Swipe")
                        {
                            $b_particulars2 = 'Swipe'	;
                            $b_particulars21 ='';
                            $b_particulars22 ='';
                            $b_particulars23 ='';
                        }
            
                
            
                    
                    // Booking //
                    $booking_amount1 = $without_agree->booking_amount1 ?? 0;
$booking_amount2 = $without_agree->booking_amount2 ?? 0;

$thecash = number_format($booking_amount1); // for first row
$thecash1 = number_format($booking_amount2); // for second row

$email_content.= '<div style="clear:both;">';
                    $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                    $email_content.= '<tr>';
                    $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Booking Date</td>';
                    $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Booking Amount</td>';
                    $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Particulars</td>';
                    $email_content.= '</tr>';	
                    $email_content.= '<tr>';
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$booking_date1</td>";
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash /-</td>";
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>
                    <p>$b_particulars1</p> <p>$b_particulars11</p> <p>$b_particulars12</p> <p>$b_particulars13</p></td>";
                    $email_content.= '</tr>';
                    if($without_agree->booking_amount2 != 0)
                    {
                        $email_content.= '<tr>';
                        $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$booking_date2</td>";
                        $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash1 /-</td>";
                        $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'><p>$b_particulars2</p> <p>$b_particulars21</p> <p>$b_particulars22</p> <p>$b_particulars23</p></td>";
                        $email_content.= '</tr>';
                    }
                    $email_content.= '</table>';
                    $email_content.= '</div>';
                    $email_content.= '<br>';        
                    $email_content.= '<br>';
            
                    // Agreement //
            
                    if(empty($without_install))
                    {
                        
                    }
                    else 
                    {
                        if( $without_install->agreement_amount != 0 || $without_install->agreement_payment_mode != 0)
                        {
                            $without_install->agreement_amount;
                            $num=$without_install->agreement_amount;$explrestunits = "" ;
                            if(strlen($num)>3)
                            {
                                $lastthree = substr($num, strlen($num)-3, strlen($num));
                                $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                                $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                                $expunit = str_split($restunits, 2);
                                for($i=0; $i<sizeof($expunit); $i++) {
                                    // creates each of the 2's group and adds a comma to the end
                                    if($i==0) {
                                        $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                    } else {
                                        $explrestunits .= $expunit[$i].",";
                                    }
                                }
                                $thecash = $explrestunits.$lastthree;
                            }
                            else
                            {
                                $thecash = $num;
                            }
                            
                              $total=$total+$num;
                        }
                    }
            
                    $agreement_date = date("d-m-Y", strtotime($without_install->agreement_date));
            
            
                    $agreement_particularss = $without_install->agreement_payment_mode;
                    if($agreement_particularss == "Online Payment")
                    {
                        $agreement_particulars = 'NEFT / RTGS';
                    }
                    else if($agreement_particularss == "Paytm Payment")
                    {
                        $agreement_particulars = 'UPI / Direct Payment';
                    }
                    else if($agreement_particularss == "UPI Payment")
                    {
                        $agreement_particulars = 'Credit / Debit Payment';
                    }
                    else
                    {
                        $agreement_particulars = $agreement_particularss;
                    }
            
                    if($agreement_particularss == "Cheque")
                    {
                        $number = $without_install->agreement_cheque_no;
                        $mode_date = date("d-m-Y", strtotime($without_install->agreement_cheque_date));
                        $bank =$without_install->agreement_bank;
                        //$a_particulars = 'Payment Mode: Cheque,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                        $a_particulars = 'Payment Mode: Cheque';
                        $a_particulars1 =  'No: '.$number;
                         $a_particulars2 = 'Date: '.$mode_date;
                        $a_particulars3= ' Bank: '.$bank;
                    }
                    else if($agreement_particularss == "Online Payment")
                    {
                        $number = $without_install->agreement_vtr_no;
                        $mode_date = date("d-m-Y", strtotime($without_install->agreement_online_date));
                        $bank = '';
                        //$a_particulars = 'Payment Mode: NEFT/RTGS,  No: '.$number.',  Date: '.$mode_date;
                        $a_particulars = 'Payment Mode: NEFT/RTGS';
                        $a_particulars1 =  'No: '.$number;
                         $a_particulars2 = 'Date: '.$mode_date;
                        $a_particulars3= '';
                    }
                    else if($agreement_particularss == "DD")
                    {
                        $number = $without_install->agreement_dd_no;
                        $mode_date = date("d-m-Y", strtotime($without_install->agreement_dd_date));
                        $bank = $without_install->agreement_dd_bank;
                        //$a_particulars = 'Payment Mode: DD,  No: '.$number.',  Date: '.$mode_date;
                        $a_particulars = 'Payment Mode: DD';
                        $a_particulars1 =  'No: '.$number;
                         $a_particulars2 = 'Date: '.$mode_date;
                        $a_particulars3= ' Bank: '.$bank;
                    }
                    else if($agreement_particularss == "Paytm Payment")
                    {
                        $number = $without_install->agreementpaytm_ref_no;
                        $mode_date = date("d-m-Y", strtotime($without_install->agreementpaytm_online_date1));
                        $bank = '';
                        //$a_particulars = 'Payment Mode: UPI / Direct Payment,  No: '.$number.',  Date: '.$mode_date;
    
                        $a_particulars = 'Payment Mode: UPI / Direct Payment';
                        $a_particulars1 =  'No: '.$number;
                         $a_particulars2 = 'Date: '.$mode_date;
                        $a_particulars3= '';
                    }
                    else if($agreement_particularss == "UPI Payment")
                    {
                        $number = $without_install->agreementupi_online_date1;
                        $mode_date = date("d-m-Y", strtotime($without_install->agreementupi_ref_no));
                        $bank = '';
                        //$a_particulars = 'Payment Mode: Credit / Debit Payment,  No: '.$number.',  Date: '.$mode_date;
    
                        $a_particulars = 'Payment Mode: Credit / Debit Payment';
                        $a_particulars1 =  'No: '.$number;
                         $a_particulars2 = 'Date: '.$mode_date;
                        $a_particulars3= '';
                    }
                    else if($agreement_particularss == "Cash")
                    {
                        $a_particulars = 'Cash'	;
                        $a_particulars1= '';
                        $a_particulars2= '';
                        $a_particulars3= '';
                    }
                    else if($agreement_particularss == "Swipe")
                    {
                        $a_particulars = 'Swipe'	;
                        $a_particulars1= '';
                        $a_particulars2= '';
                        $a_particulars3= '';
                    }
            
            
                    $email_content.= '<div style="clear:both;">';
                    $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                    $email_content.= '<tr>';
                    $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Agreement Date</td>';
                    $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Agreement Amount</td>';
                     $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Particulars</td>';
                    $email_content.= '</tr>';		
                    $email_content.= '<tr>';
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$agreement_date</td>";
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash /-</td>";
                    $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>
                        <p>$a_particulars</p> <p>$a_particulars1</p> <p>$a_particulars2</p> <p>$a_particulars3</p>
    
                    </td>";
                    $email_content.= '</tr>';
                    $email_content.= '</table>';
                    $email_content.= '</div>';
                    $email_content.= '<br>';
                    $email_content.= '<br>';
            
                    // Installment //
            
                    $i = 1;
                    
                    if(empty($payment))
                    {
                        
                    }
                    else 
                    {
                        $h=1;
                        foreach($payment as $payment)
                        {
                            
                            if($payment->installment_amount1 != 0 || $payment->installment_payment_mode1 != 0)
                            {
                                $payment->installment_amount1;
                                $num=$payment->installment_amount1;
                                $explrestunits = "" ;
                                if(strlen($num)>3)
                                {
                                    $lastthree = substr($num, strlen($num)-3, strlen($num));
                                    $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                                    $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                                    $expunit = str_split($restunits, 2);
                                    for($i=0; $i<sizeof($expunit); $i++) {
                                        // creates each of the 2's group and adds a comma to the end
                                        if($i==0) {
                                            $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                        } else {
                                            $explrestunits .= $expunit[$i].",";
                                        }
                                    }
                                    $thecash = $explrestunits.$lastthree;
                                }
                                else
                                {
                                    $thecash = $num;
                                }
            
                                $total = $total + (float)$num;
            
                                $installment1_date = date("d-m-Y", strtotime($payment->installment1_date));
            
                                $installment_particularss = $payment->installment_payment_mode1;
            
                                if($installment_particularss == "Online Payment")
                                {
                                    $installment_particulars = 'NEFT / RTGS';
                                }
                                else if($installment_particularss == "Paytm Payment")
                                {
                                    $installment_particulars = 'UPI / Direct Payment';
                                }
                                else if($installment_particularss == "UPI Payment")
                                {
                                    $installment_particulars = 'Credit / Debit Payment';
                                }
                                else
                                {
                                    $installment_particulars = $installment_particularss;
                                }
            
                                if($installment_particularss == "Cheque")
                                {
                                    $number = $payment->install_cheque_no1;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_cheque_date1));
                                    $bank =$payment->install_bank1;
                                    //$i_particulars = 'Payment Mode: Cheque,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                                    $i_particulars1 = 'Payment Mode: UPI / Direct Payment';
                                    $i_particulars11 =  'No: '.$number;
                                     $i_particulars12 = 'Date: '.$mode_date;
                                    $i_particulars13= 'Bank: '.$bank;
                                }
                                else if($installment_particularss == "Online Payment")
                                {
                                    $number = $payment->install_vtr_no1;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_online_date1));
                                    $bank = '';
                                    //$i_particulars = 'Payment Mode: NEFT/RTGS,  No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars1 = 'Payment Mode: NEFT/RTGS';
                                    $i_particulars11 =  ' No: '.$number;
                                     $i_particulars12 = 'Date: '.$mode_date;
                                    $i_particulars13= '';
                                }
                                else if($installment_particularss == "DD")
                                {
                                    $number = $payment->install_dd_no1;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_dd_date1));
                                    $bank = $payment->install_dd_bank1;
                                    //$i_particulars = 'Payment Mode: DD,  No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                                    $i_particulars1 = 'Payment Mode: DD';
                                    $i_particulars11 =  ' No: '.$number;
                                     $i_particulars12 = 'Date: '.$mode_date;
                                    $i_particulars13= ' Bank: '.$bank;
                                }
                                else if($installment_particularss == "Paytm Payment")
                                {
                                    $number = $payment->paytm_num;
                                    $mode_date = date("d-m-Y", strtotime($payment->paytm_date));
                                    $bank = '';
                                    //$i_particulars = 'Payment Mode: UPI / Direct Payment,  No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars1 = 'Payment Mode: UPI / Direct Payment';
                                    $i_particulars11 =  ' No: '.$number;
                                     $i_particulars12 = 'Date: '.$mode_date;
                                    $i_particulars13= '';
                                }
                                else if($installment_particularss == "UPI Payment")
                                {
                                    $number = $payment->upi_num;
                                    $mode_date = date("d-m-Y", strtotime($payment->upi_date));
                                    $bank = '';
                                    //$i_particulars = 'Payment Mode: Credit / Debit Payment,  No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars1 = 'Payment Mode: Credit / Debit Payment';
                                    $i_particulars11 =  ' No: '.$number;
                                     $i_particulars12 = 'Date: '.$mode_date;
                                    $i_particulars13= '';
                                }
                                else if($installment_particularss == "Cash")
                                {
                                    $i_particulars1 = 'Cash';
                                    $i_particulars11 =  '';
                                     $i_particulars12 = '';
                                    $i_particulars13= '';
                                }
                                else if($installment_particularss == "Swipe")
                                {
                                    $i_particulars1 = 'Swipe';
                                    $i_particulars11 =  '';
                                    $i_particulars12 = '';
                                    $i_particulars13= '';
                                }

$email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'> <p>$i_particulars1</p> <p>$i_particulars11</p> <p>$i_particulars12</p> <p>$i_particulars13</p></td>";
                                $email_content.= '</tr>';
            
                                $email_content.= '</table>';
                                $email_content.= '</div>';
                                $email_content.= '<br>';
                                $email_content.= '<br>';
                                
            
                            }
                            else if($payment->installment_amount2 != 0 || $payment->installment_payment_mode2 != 0)
                            {
                                $payment->installment_amount2;
                                $num=$payment->installment_amount2;$explrestunits = "" ;
                                $explrestunits = "" ;
                                if(strlen($num)>3)
                                {
                                    $lastthree = substr($num, strlen($num)-3, strlen($num));
                                    $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                                    $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                                    $expunit = str_split($restunits, 2);
                                    for($i=0; $i<sizeof($expunit); $i++) {
                                        // creates each of the 2's group and adds a comma to the end
                                        if($i==0) {
                                            $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                        } else {
                                            $explrestunits .= $expunit[$i].",";
                                        }
                                    }
                                    $thecash2 = $explrestunits.$lastthree;
                                }
                                else
                                {
                                    $thecash2 = $num;
                                }
                                $total=$total+$num;
            
                                $installment2_date = date("d-m-Y", strtotime($payment->installment2_date));
            
            
                                $installment_particularss2 = $payment->agreement_payment_mode;
                                if($installment_particularss2 == "Online Payment")
                                {
                                    $installment_particulars2 = 'NEFT / RTGS';
                                }
                                else if($installment_particularss == "Paytm Payment")
                                {
                                    $installment_particulars = 'UPI / Direct Payment';
                                }
                                else if($installment_particularss == "UPI Payment")
                                {
                                    $installment_particulars = 'Credit / Debit Payment';
                                }
                                else
                                {
                                    $installment_particulars2 = $installment_particularss2;
                                }
            
                                if($installment_particularss2 == "Cheque")
                                {
                                    $number = $payment->install_cheque_no2;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_cheque_date2));
                                    $bank =$payment->install_bank2;
                                    //$i_particulars2 = 'Payment Mode: Cheque, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                                    $i_particulars2 = 'Payment Mode: Cheque';
                                    $i_particulars21 =  'No: '.$number;
                                     $i_particulars22 = 'Date: '.$mode_date;
                                    $i_particulars23= 'Bank: '.$bank;
                                }
                                else if($installment_particularss2 == "Online Payment")
                                {
                                    $number = $payment->install_vtr_no2;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_online_date2));
                                    $bank = '';
                                    //$i_particulars2 = 'Payment Mode: NEFT/RTGS, No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars2 = 'Payment Mode: NEFT/RTGS';
                                    $i_particulars21 =  'No: '.$number;
                                     $i_particulars22 = 'Date: '.$mode_date;
                                    $i_particulars23= '';
                                }
                                else if($installment_particularss2 == "DD")
                                {
                                    $number = $payment->install_dd_no2;
                                    $mode_date = date("d-m-Y", strtotime($payment->install_dd_date2));
                                    $bank = $payment->install_dd_bank2;
                                    //$i_particulars2 = 'Payment Mode: DD, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                                    $i_particulars2 = 'Payment Mode: DD';
                                    $i_particulars21 =  'No: '.$number;
                                     $i_particulars22 = 'Date: '.$mode_date;
                                    $i_particulars23= 'Bank: '.$bank;
                                }
                                else if($installment_particularss2 == "Paytm Payment")
                                {
                                    $number = $payment->paytm_num2;
                                    $mode_date = date("d-m-Y", strtotime($payment->paytm_date2));
                                    $bank = '';
                                    //$i_particulars2 = 'Payment Mode: UPI / Direct Payment, No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars2 = 'Payment Mode: UPI / Direct Payment';
                                    $i_particulars21 =  'No: '.$number;
                                     $i_particulars22 = 'Date: '.$mode_date;
                                    $i_particulars23= '';
                                }
                                else if($installment_particularss2 == "UPI Payment")
                                {
                                    $number = $payment->upi_num2;
                                    $mode_date = date("d-m-Y", strtotime($payment->upi_date2));
                                    $bank = '';
                                    //$i_particulars2 = 'Payment Mode: Credit / Debit Payment, No: '.$number.',  Date: '.$mode_date;
    
                                    $i_particulars2 = 'Payment Mode: Credit / Debit Payment';
                                    $i_particulars21 =  'No: '.$number;
                                     $i_particulars22 = 'Date: '.$mode_date;
                                    $i_particulars23= '';
                                }
                                else if($installment_particularss2 == "Cash")
                                {
                                    $i_particulars2 = 'Cash';
                                    $i_particulars21 =  '';
                                     $i_particulars22 = '';
                                    $i_particulars23= '';
                                }
                                else if($installment_particularss2 == "Swipe")
                                {
                                    $i_particulars2 = 'Swipe';
                                    $i_particulars21 =  '';
                                     $i_particulars22 = '';
                                    $i_particulars23= '';
                                }
            
                                $email_content.= '<div style="clear:both;">';
                                $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                                $email_content.= '<tr>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Installment Date</td>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Installment Amount</td>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Particulars</td>';
                                $email_content.= '</tr>';
                                    
                                $email_content.= '<tr>';
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$installment2_date</td>";
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash2 /-</td>";
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'><p>$i_particulars2</p> <p>$i_particulars21</p> <p>$i_particulars22</p> <p>$i_particulars23</p></td>";
                                $email_content.= '</tr>';
            
                                $email_content.= '</table>';
                                $email_content.= '</div>';
            
                                $email_content.= '<br>';
                                $email_content.= '<br>';
                            }
            
                            
                        $h++; }
            
            
                    }
            
                   
            
                     // registration //
                    if(empty($without_install))
                    {
                        
                    }
                    else 
                    {	
                        if($without_install->registration_amount != 0 || $without_install->registration_payment_mode != 0)
                        {
            
                            $without_install->registration_amount;
                            $num=$without_install->registration_amount;$explrestunits = "" ;
                            if(strlen($num)>3)
                            {
                                $lastthree = substr($num, strlen($num)-3, strlen($num));
                                $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                                $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                                $expunit = str_split($restunits, 2);
                                for($i=0; $i<sizeof($expunit); $i++) {
                                    // creates each of the 2's group and adds a comma to the end
                                    if($i==0) {
                                        $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                    } else {
                                        $explrestunits .= $expunit[$i].",";
                                    }
                                }
                                $thecash = $explrestunits.$lastthree;
                            }
                            else
                            {
                                $thecash2 = $num;
                            }
                            $total=$total+$num;
            
                            $registration_date = date("d-m-Y", strtotime($without_install->registration_date));
                            if($registration_date == '30-11--0001')
                            {
                                $registration_date = '00-00-0000';
                            }
                            else
                            {
                                $registration_date = date("d-m-Y", strtotime($without_install->registration_date));
                            }
            
                            $reg_particularss = $without_install->registration_payment_mode;
                                if($reg_particularss == "Online Payment")
                                {
                                    $reg_particulars = 'NEFT / RTGS';
                                }
                                else if($reg_particularss == "Paytm Payment")
                                {
                                    $reg_particulars = 'UPI / Direct payment';
                                }
                                else if($reg_particularss == "UPI Payment")
                                {
                                    $reg_particulars = 'Credit / Debit payment';
                                }
                                else
                                {
                                    $reg_particulars = $reg_particularss;
                                }
            
                                if($reg_particularss == "Cheque")
                                {
                                    $number = $without_install->regn_cheque_no;
                                    $mode_date = date("d-m-Y", strtotime($without_install->regn_cheque_date));
                                    $bank =$without_install->regn_bank;
                                    //$reg_particulars = 'Payment Mode: Cheque, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
                                    $reg_particulars = 'Payment Mode: Cheque';
                                    $reg_particulars1 =  'No: '.$number;
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3= 'Bank: '.$bank;
                                }
                                else if($reg_particularss == "Online Payment")
                                {
                                    $number = $without_install->regn_vtr_no;
                                    $mode_date = date("d-m-Y", strtotime($without_install->regn_online_date));
                                    $bank = '';
                                    //$reg_particulars = 'Payment Mode: NEFT/RTGS, No: '.$number.',  Date: '.$mode_date;
                                    $reg_particulars = 'Payment Mode: NEFT/RTGS';
                                    $reg_particulars1 =  'No: '.$number;
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = '';
                                }
                                else if($reg_particularss == "DD")
                                {
                                    $number = $without_install->regn_dd_no;
                                    $mode_date = date("d-m-Y", strtotime($without_install->regn_dd_date));
                                    $bank = $without_install->regn_dd_bank;
                                    //$reg_particulars = 'Payment Mode: DD, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
                                    $reg_particulars = 'Payment Mode: DD';
                                    $reg_particulars1 =  'No: '.$number;
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = 'Bank: '.$bank;
                                }
                                else if($reg_particularss == "Paytm Payment")
                                {
                                    $number = $without_install->regn_ref_no;
                                    $mode_date = date("d-m-Y", strtotime($without_install->regn_paytm_date));
                                    $bank = '';
                                    //$reg_particulars = 'Payment Mode: UPI / Direct Payment, No: '.$number.',  Date: '.$mode_date;
    
                                    $reg_particulars = 'Payment Mode: UPI / Direct Payment';
                                    $reg_particulars1 =  'No: '.$number;
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = '';
                                }
                                else if($reg_particularss == "UPI Payment")
                                {
                                    $number = $without_install->regn_upiref_no;
                                    $mode_date = date("d-m-Y", strtotime($without_install->regn_upi_date));
                                    $bank = '';
                                    //$reg_particulars = 'Payment Mode: Credit / Debit Payment, No: '.$number.',  Date: '.$mode_date;
    
                                    $reg_particulars = 'Payment Mode: Credit / Debit Payment';
                                    $reg_particulars1 =  'No: '.$number;
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = '';
                                }
                                else if($reg_particularss == "Cash")
                                {
                                    $number = '';
                                    $mode_date = date("d-m-Y", strtotime($without_install->cash_date));
                                    $bank = '';
                                    //$reg_particulars = 'Payment Mode: Cash,  Date: '.$mode_date;
    
                                    $reg_particulars = 'Payment Mode: Cash';
                                    $reg_particulars1 =  '';
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = '';
                                }
                                    else if($reg_particularss == "Swipe")
                                {
                                    $number = '';
                                    $mode_date = date("d-m-Y", strtotime($without_install->swipe_date));
                                    $bank = '';
                                    //$reg_particulars = 'Payment Mode: Swipe,  Date: '.$mode_date;
    
                                    $reg_particulars = 'Payment Mode: Swipe';
                                    $reg_particulars1 =  '';
                                     $reg_particulars2 = 'Date: '.$mode_date;
                                    $reg_particulars3 = '';
                                }
            
                                $email_content.= '<div style="clear:both;">';
                                $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                                $email_content.= '<tr>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Sl No.</td>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Registration Date</td>';
                                $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Registration Amount</td>';
                                 $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Particulars</td>';
                                $email_content.= '</tr>';		
                                $email_content.= '<tr>';
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>1</td>";
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$registration_date</td>";
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash /-</td>";
                                $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'><p>$reg_particulars</p> <p>$reg_particulars1</p> <p>$reg_particulars2</p> <p>$reg_particulars3</p></td>";
                                $email_content.= '</tr>';
                                $email_content.= '</table>';
                                $email_content.= '</div>';
                                $email_content.= '<br>';
            
                        }
                    }
                     
                    if($get_reg_amt != "")
                    {
                        $a=1;
                        foreach($get_reg_amt as $get_reg_amt)
                        {
                            $get_reg_amt->reg_amount;
                            $num=$get_reg_amt->reg_amount;$explrestunits = "" ;
                            $explrestunits = "" ;
                            if(strlen($num)>3)
                            {
                                $lastthree = substr($num, strlen($num)-3, strlen($num));
                                $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
                                $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
                                $expunit = str_split($restunits, 2);
                                for($i=0; $i<sizeof($expunit); $i++) {
                                    // creates each of the 2's group and adds a comma to the end
                                    if($i==0) {
                                        $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                                    } else {
                                        $explrestunits .= $expunit[$i].",";
                                    }
                                }
                                $thecash2 = $explrestunits.$lastthree;
                            }
                            else
                            {
                                $thecash2 = $num;
                            }
                            $total=$total+$num;
            
                            /*$reg_date = date("d-m-Y", strtotime($get_reg_amt->reg_date));
                            if($reg_date == '30-11--0001')
                            {
                                $reg_date = '00-00-0000';
                            }
                            else
                            {
                                $reg_date = date("d-m-Y", strtotime($get_reg_amt->reg_date));
                            }*/
            
                            $r_particularss = $get_reg_amt->reg_payment_mode;
                            if($r_particularss == "Online Payment")
                            {
                                $r_particulars = 'NEFT / RTGS';
                            }
                            if($r_particularss == "Paytm Payment")
                            {
                                $r_particulars = 'UPI / Direct Payment';
                            }
                            if($r_particularss == "UPI Payment")
                            {
                                $r_particulars = 'Credit / Debit Payment';
                            }
                            else
                            {
                                $r_particulars = $r_particularss;
                            }
            
                            if($r_particularss == "Cheque")
                            {
                                $number = $get_reg_amt->number;
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank =$get_reg_amt->branch_name;
                                //$r_particulars = 'Payment Mode: Cheque, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
                                $r_particulars = 'Payment Mode: Cheque';
                                    $r_particulars1 =  'No: '.$number;
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = ' Bank: '.$bank;
                            }
                            else if($r_particularss == "Online Payment")
                            {
                                $number = $get_reg_amt->number;
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = '';
                                //$r_particulars = 'Payment Mode: NEFT/RTGS, No: '.$number.',  Date: '.$mode_date;
                                $r_particulars = 'Payment Mode:  NEFT/RTGS';
                                    $r_particulars1 =  'No: '.$number;
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = '';
                            }
                            else if($r_particularss == "Paytm Payment")
                            {
                                $number = '';
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = '';
                                //$r_particulars = 'Payment Mode: UPI / Direct Payment, Date: '.$mode_date;
    
                                $r_particulars = 'Payment Mode: UPI / Direct Payment';
                                    $r_particulars1 =  'No: '.$number;
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = '';
                            }
                            else if($r_particularss == "UPI Payment")
                            {
                                $number = '';
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = '';
                                //$r_particulars = 'Payment Mode: Credit / Debit Payment, Date: '.$mode_date;
    
                                $r_particulars = 'Payment Mode: Credit / Debit Payment';
                                    $r_particulars1 =  'No: '.$number;
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = '';
                            }
                            else if($r_particularss == "DD")
                            {
                                $number = $get_reg_amt->number;
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = $get_reg_amt->branch_name;
                                //$r_particulars = 'Payment Mode: DD, No: '.$number.',  Date: '.$mode_date.', Bank: '.$bank;
    
                                $r_particulars = 'Payment Mode: DD';
                                    $r_particulars1 =  'No: '.$number;
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = ' Bank: '.$bank;
                            }
                            else if($r_particularss == "Cash")
                            {
                                $number = '';
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = '';
                                //$r_particulars = 'Payment Mode:Cash, Date: '.$mode_date;
    
                                $r_particulars = 'Payment Mode: Cash';
                                    $r_particulars1 =  '';
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = '';
                            }
                            else if($r_particularss == "Swipe")
                            {
                                $number = '';
                                $mode_date = date("d-m-Y", strtotime($get_reg_amt->mode_date));
                                $bank = '';
                                //$r_particulars = 'Payment Mode: Swipe, Date: '.$mode_date;
    
                                $r_particulars = 'Payment Mode: Swipe';
                                    $r_particulars1 =  '';
                                     $r_particulars2 = 'Date: '.$mode_date;
                                    $r_particulars3 = '';
                            }
                            $a++;
            
                             $email_content.= '<div style="clear:both;">';
                            $email_content.= '<table style="width:100%;border-collapse: collapse;border-color:red;" border="1">';
                            $email_content.= '<tr>';
                            $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Sl No.</td>';
                            /*$email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Registration Date</td>';*/
                            $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Registration Amount</td>';
                             $email_content.= '<td style="border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;">Particulars</td>';
                            $email_content.= '</tr>';		
                            $email_content.= '<tr>';
                            $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$a</td>";
                           /* $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>$reg_date</td>";*/
                            $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'>Rs. $thecash2 /-</td>";
                            $email_content.= "<td style='border: 3px solid #e40025 !important;padding:8px 5px;font-weight:bold;font-size:16px;text-align:center;'><p>$r_particulars</p> <p>$r_particulars1</p> <p>$r_particulars2</p> <p>$r_particulars3</p></td>";
                            $email_content.= '</tr>';
                            $email_content.= '</table>';
                            $email_content.= '</div>';
                            $email_content.= '<br>';
                        }
            
            
                    }
            
            
                    $email_content.= '<div style="clear:both;">';
                    $email_content.= "<p style='color:green;'>$balance_amount_1</p>";
                    $email_content.= '</div>';
            
                    $email_content.= '<div style="clear:both;">';
                    $email_content.= "<p style='color:blue;'>$total_amount_1</p>";
                    $email_content.= '</div>';
            
                    $email_content.= '</body>';
                    $email_content.= '</html>';
                    
                    
                    // $mpdf->SetDisplayMode('fullpage');
                    // $mpdf->watermark_font = 'DejaVuSansCondensed';
                    // $mpdf->showWatermarkText = true;
                    // $mpdf->WriteHTML($email_content);
                    $mpdf->WriteHTML($email_content);
                    $name  = 'site_report'.$insert_result.'.pdf';
                   
                     
                    $data = array('email_receipt'=>$name);
                    $where = array('id'=>$insert_result);
                    $result = $gss_model->update1($table,$where,$data);
                    
                    $pdf   = $mpdf->Output("public/email_receipts/".$name, 'F');
                    $db = \Config\Database::connect();
                    if ($db->affectedRows() > 0) 
                    {
                       return $this->response->setJSON(['result'=>1 ,'message'=>$insert_result]);
                    } 
                    else
                    {
                        return $this->response->setJSON(['result'=>0,'message'=>"Data could not be added"]);
                    }
                }
                else
                {
                    return $this->response->setJSON(['result'=>0,'message'=>"Data could not be added"]);
                }  
            }
            else
            {
                redirect('/');
            } 
    
        }
        
        
         public function view_mail_content()
        {
            
           $admin_id = session()->get('admin_id');
            if($admin_id)
            {
                $gss_model=new Reports_model();
                $id = service('request')->getUri()->getSegment(2);
                $table = 'gss_email_details';
                $where = array('id'=>$id);
                $data['details'] = $gss_model->get_where_row($table,$where);

$data['user_type_id'] = $this->access_id();
                $data['access'] = $this->access_details();
                return view('admin/send_email',$data);
            }
            else
            {
                redirect('/');
            } 
        }
        
public function get_status_report_balance_agree_amt()
        {
            $gss_model=new Reports_model();
              $db = \Config\Database::connect();
            $project                    = $this->request->getpost('project_id');
            
            $tot_agree_due_amt           = $gss_model->get_total_agree_due_amt($project);
            
            $total_agreement_due_amount = 0;
            $total_registration_due_amount = 0;
            foreach($tot_agree_due_amt as $tada)
            {
               $total_agreement_due_amt = $tada->sales_agreement_due_amount;
               $total_agreement_due_amount += $total_agreement_due_amt;
               $total_reg_due_amt = $tada->registration_due_amount;
               $total_registration_due_amount += $total_reg_due_amt;
               
            }
            
            $received_agreement_amt       = $gss_model->get_received_agree_amt($project);
            
            $received_agreement_amount = 0;
            $received_registration_amount = 0;
            foreach($received_agreement_amt as $raa)
            {
               $received_agree_amt = $raa->agreement_amount;
               $received_agreement_amount += is_numeric($received_agree_amt);
               $received_due_amt = $raa->registration_amount;
               $received_registration_amount += is_numeric($received_due_amt);
               
            }
            
       
            $from_date = "";
            $to_date = "";
            $total_paid = 0;
            $tot_agr_amt = 0;
            $tot_booking_amt = 0;
            $total_paid_reg = $gss_model->payment_reports_receivable($from_date,$to_date,$project);
            foreach($total_paid_reg as $tpr)
            {
                $tot_booking = $tpr['booking_amount1'];
                $tot_booking_amt += $tot_booking;
                
                $tot_agr = $tpr['agreement_amount2'];
                $tot_agr_amt += is_numeric($tot_agr);
                
                $tot_paid = $tpr['subtotal2'];
                $total_paid += $tot_paid;
                
            }
            
            $received_agr_amount = $tot_booking_amt+$tot_agr_amt;
            
            $received_reg_amount = $total_paid;
          
            $balance_agree_amount = $total_agreement_due_amount - $received_agreement_amount;
            
            $balance_reg_amount = $total_registration_due_amount - $received_registration_amount;
    
            $total_dimn_result  = $gss_model->total_marketing_dimension_report($project); 
            $booked_dimn_result = $gss_model->total_booked_dimension_report($project); 
    
         
              return $this->response->setJSON(['result'=>1,'total_dimension'=>$total_dimn_result,'booked_dimnsion'=>$booked_dimn_result, 'balance_agreement_amount'=>$balance_agree_amount,'received_reg_amount'=>$received_reg_amount,'received_registration_amount'=>$received_registration_amount,'balance_reg_amount'=>$balance_reg_amount,'received_agreement_amount'=>$received_agreement_amount,'received_agr_amount'=>$received_agr_amount]);   
           
        }
        
        public function add_report_status()
        {
            $gss_model=new Reports_model();
            $table     = 'gss_report_status';
            $gss_model->table_truncate($table);
            $project_id_datewise = $this->request->getpost('project_id_datewise');
            $agr_type = $this->request->getpost('agr_type');
            $from_date_datewise = $this->request->getpost('from_date_datewise');
            $to_date_datewise = $this->request->getpost('to_date_datewise');
            $budget_from_date_datewise = $this->request->getpost('budget_from_date_datewise');
            $budget_to_date_datewise = $this->request->getpost('budget_to_date_datewise');
            $khata_status = $this->request->getpost('khata_status');
            $project_ownership = $this->request->getpost('project_ownership');
            $s1_project_type = $this->request->getpost('s1_project_type');
            $handled_by = $this->request->getpost('handled_by');
            $reference = $this->request->getpost('reference');
            $logistic = $this->request->getpost('logistic');
            
            $data      = array('project_status'=>$s1_project_type,'project'=>$project_id_datewise,'from_date'=>$from_date_datewise,'type'=>$agr_type,'to_date'=>$to_date_datewise,'budget_from'=>$budget_from_date_datewise,'budget_to'=>$budget_to_date_datewise,'project_ownership'=>$project_ownership,'khata_status'=>$khata_status,'handled_by'=>$handled_by,'reference'=>$reference,'logistic'=>$logistic,'delete_status'=>'ACTIVE');
            
            $result    = $gss_model->insert1($table,$data);
    
            if($result)
            {
           return $this->response->setJSON(['result'=>1,'message'=>$result]);
            }
            
            else
            {
               return $this->response->setJSON(['result'=>0,'message'=>'Data Already Exist']);
            }
    
        }
        
        
         public function get_status_report_type()
        {
            $gss_model=new Reports_model();
    
            $table=   'gss_report_status';
            $id = $this->request->getpost('id');
            $where   = array('id'=>$id,'delete_status'=>'ACTIVE');
            $result  = $gss_model->get_where_row($table,$where);
            //print_r($result);die();
            if($result)
            {
               return $this->response->setJSON(['details'=>$result,'result'=>1]);
            }
            else
            {
                return $this->response->setJSON(['result'=>0]);
            }		
        }
        
        
        public function get_report_status_details()
        {
            $admin_id              = session()->get('admin_id');
        
            if($admin_id)
            {
                //$project=$this->uri->segement(2);
                $data['user_type_id'] = $this->access_id();
                $data['access'] = $this->access_details();
               return view('admin/report_status_grid',$data);
            }
            else
            {
                redirect('/');
            }	
        }
    
     public function get_report_status_unbooked_details()
        {
            $admin_id              = session()->get('admin_id');
        
            if($admin_id)
            {
                //$project=$this->uri->segement(2);
                $data['user_type_id'] = $this->access_id();
                $data['access'] = $this->access_details();
               return view('admin/report_status_unbooked_grid',$data);
            }
            else
            {
                redirect('/');
            }	
 }
        
public function follow_up_status_conversation()
{
    $session = session();
    $user_type_id = $session->get('user_type_id');

if ($user_type_id) {
      $booking_id = $this->request->getUri()->getSegment(2);

$gss_model=new Reports_model();

$data['detail']            = $gss_model->get_conversation_replies($booking_id);
        $data['due_type_detail']   = "";
        $data['project_details']   = $gss_model->conversation_booking_details($booking_id);
        $data['user_type_id']      = $this->access_id();
        $data['access']            = $this->access_details();

return view('admin/conversation', $data);
    } else {
        return redirect()->to('/');
    }
}  
        
 public function logistic_incentive_reports()
{
    $from_date = $this->request->getGet('from_date');
    if (!empty($from_date)) {
        $date = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

$to_date = $this->request->getGet('to_date');
    if (!empty($to_date)) {
        $date = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$executive = $this->request->getGet('reference');

$model=new Reports_model(); // adjust model path if needed
    $result = $model->get_logistic_incentive_reports($from_date, $to_date, $executive);

if ($result) {
        return $this->response->setJSON($result);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}

public function executive_incentive_reports()
{

$from_date = $this->request->getGet('from_date');
    if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

$to_date = $this->request->getGet('to_date');
    if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }
    $model=new Reports_model();
    $executive = $this->request->getGet('reference');
    $month     = $this->request->getGet('months');
    $year      = $this->request->getGet('year');

$result = $model->get_executive_incentive_reports($from_date, $to_date, $executive, $month, $year);

return $this->response->setJSON($result ?: ['result' => 0]);
}

public function get_executive_payments()
{
     $model=new Reports_model();
    $id = $this->request->getGet('id');
    
    $result = $model->executive_payment_reports($id);
    
    return $this->response->setJSON($result);
}
public function export_print_executive_incentive_reports()
{
    $from_date = $this->request->getGet('from_date');
    if (!empty($from_date)) {
        $date = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

$to_date = $this->request->getGet('to_date');
    if (!empty($to_date)) {
        $date = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$executive = $this->request->getGet('reference');
    $month     = $this->request->getGet('months');
    $year      = $this->request->getGet('year');

$model=new Reports_model(); // Adjust the namespace if different
    $result = $model->get_export_print_executive_incentive_reports($from_date, $to_date, $executive, $month, $year);

if ($result) {
        return $this->response->setJSON($result);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}
public function get_booking_contact_reports()
{
    $from_date = $this->request->getGet('from_date');
    if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }
    $model=new Reports_model(); 
    $to_date = $this->request->getGet('to_date');
    if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$project    = $this->request->getGet('project');
    $reference  = $this->request->getGet('reference');
    $logistic   = $this->request->getGet('logistic');
    $associate  = $this->request->getGet('associate');
    $ported     = $this->request->getGet('ported');

$result = $model->booking_reports($from_date, $to_date, $project, $reference, $logistic, $associate, $ported);

return $this->response->setJSON($result);
}

public function get_booking_email_reports()
    {
        $model = new Reports_model();

$from_date = $this->request->getGet('from_date');
        if (!empty($from_date)) {
            $from_date = (new \DateTime($from_date))->format('Y-m-d');
        }

$to_date = $this->request->getGet('to_date');
        if (!empty($to_date)) {
            $to_date = (new \DateTime($to_date))->format('Y-m-d');
        }

$project    = $this->request->getGet('project');
        $reference  = $this->request->getGet('reference');
        $logistic   = $this->request->getGet('logistic');
        $associate  = $this->request->getGet('associate');
        $ported     = $this->request->getGet('ported');

$result = $model->booking_reports($from_date, $to_date, $project, $reference, $logistic, $associate, $ported);

return $this->response->setJSON($result);
    }
public function get_payment_contact_reports()
{
    $from_date = $this->request->getGet('from_date');
    $to_date   = $this->request->getGet('to_date');
    $p_project = $this->request->getGet('p_poject'); // keeping your original GET param

if (!empty($from_date)) {
        $date      = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }
   $model = new Reports_model();
    if (!empty($to_date)) {
        $date    = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$result = $model->payment_reports_receivable($from_date, $to_date, $p_project);

return $this->response->setJSON($result);
}
public function get_payment_email_reports()
{  $model = new Reports_model();
    $from_date = $this->request->getGet('from_date');
    $to_date   = $this->request->getGet('to_date');
    $p_project = $this->request->getGet('p_poject'); // keeping your param name as-is

if (!empty($from_date)) {
        $date      = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

if (!empty($to_date)) {
        $date    = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$result = $model->payment_reports_receivable($from_date, $to_date, $p_project);

return $this->response->setJSON($result);
}
public function get_payment_particulars_reports()
    {
       $model = new Reports_model();

$from_date = $this->request->getGet('from_date');
        if (!empty($from_date)) {
            $from_date = (new \DateTime($from_date))->format('Y-m-d');
        }

$to_date = $this->request->getGet('to_date');
        if (!empty($to_date)) {
            $to_date = (new \DateTime($to_date))->format('Y-m-d');
        }

$project = $this->request->getGet('p_poject');

$result = $model->get_payment_particulars_reports($from_date, $to_date, $project);

if (!empty($result)) {
            return $this->response->setJSON(['result' => '1', 'message' => $result]);
        } else {
            return $this->response->setJSON(['result' => '0', 'message' => 'No data found!']);
        }
    }
    
public function get_cancellation_contact_reports()
{
    $from_date  = $this->request->getGet('from_date');
    $to_date    = $this->request->getGet('to_date');
    $project_id = $this->request->getGet('project');
    $model = new Reports_model();
    if (!empty($from_date)) {
        $date      = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

if (!empty($to_date)) {
        $date    = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$result = $model->cancellation_reports($from_date, $to_date, $project_id);

return $this->response->setJSON($result);
}
public function get_cancellation_email_reports()
{
    $from_date  = $this->request->getGet('from_date');
    $to_date    = $this->request->getGet('to_date');
    $project_id = $this->request->getGet('project');
  $model = new Reports_model();
    if (!empty($from_date)) {
        $date      = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

if (!empty($to_date)) {
        $date    = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$result = $model->cancellation_reports($from_date, $to_date, $project_id);

return $this->response->setJSON($result);
}
public function get_associate_payments()
    {
        $model =new Reports_model();

$booking_id = $this->request->getGet('id');

$result = $model->associate_payment_reports($booking_id);

return $this->response->setJSON($result);
}
public function get_associate_contact_reports()
{
    $from_date = $this->request->getGet('from_date');
    if (!empty($from_date)) {
        $date = new \DateTime($from_date);
        $from_date = $date->format('Y-m-d');
    }

$to_date = $this->request->getGet('to_date');
    if (!empty($to_date)) {
        $date = new \DateTime($to_date);
        $to_date = $date->format('Y-m-d');
    }

$model =new Reports_model();
    $result = $model->associate_reports($from_date, $to_date);

return $this->response->setJSON($result);
}
    
public function get_associate_email_reports()
{
    $request = service('request');

$from_date = $request->getGet('from_date');
    if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

$to_date = $request->getGet('to_date');
    if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$model = new Reports_model();
    $result = $model->associate_reports($from_date, $to_date);

return $this->response->setJSON($result);
}
 public function get_enquiry_reports()
{
    $request = service('request');

$web_portal = $request->getGet('type');
    $reference  = $request->getGet('reference');

$from_date = $request->getGet('from_date');
    if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

$to_date = $request->getGet('to_date');
    if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$model = new Reports_model();
    $result = $model->enquiry_reports($from_date, $to_date, $web_portal, $reference);

return $this->response->setJSON($result);

}

public function get_dataenquiry_reports()
{
    $request = \Config\Services::request();
   $model = new Reports_model();
    $web_portal = $request->getGet('type');
    $from_date  = $request->getGet('from_date');
    $to_date    = $request->getGet('to_date');
    $reference  = $request->getGet('reference');

if (!empty($from_date)) {
        $date       = new \DateTime($from_date);
        $from_date  = $date->format('Y-m-d');
    }

if (!empty($to_date)) {
        $date      = new \DateTime($to_date);
        $to_date   = $date->format('Y-m-d');
    }

if ($web_portal === 'Webportal') {
        $result = $this->gss_model->enquiry_reports($from_date, $to_date, $web_portal, $reference);
    } elseif ($web_portal === 'Database') {
        $result = $model->enquiry_databasereports($from_date, $to_date, $web_portal, $reference);
    } else {
        $result = [];
    }

if (!empty($result)) {
        return $this->response->setJSON($result);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}
 
 public function get_enquiry_email_reports()
{
    $request = \Config\Services::request();

$web_portal = $request->getGet('type');
    $from_date  = $request->getGet('from_date');
    $to_date    = $request->getGet('to_date');
    $reference  = $request->getGet('reference');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$model = new Reports_model();

if ($web_portal === 'Webportal') {
        $result = $model->enquiry_reports($from_date, $to_date, $web_portal, $reference);
    } elseif ($web_portal === 'Database') {
        $result = $model->enquiry_databasereports($from_date, $to_date, $web_portal, $reference);
    } else {
        $result = [];
    }

return $this->response->setJSON(!empty($result) ? $result : ['result' => 0]);
}

public function get_enquiry_contact_reports()
{
    $request = \Config\Services::request();

$web_portal = $request->getGet('type');
    $from_date  = $request->getGet('from_date');
    $to_date    = $request->getGet('to_date');
    $reference  = $request->getGet('reference');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$model = new Reports_model();

return $this->response->setJSON(!empty($result) ? $result : ['result' => 0]);
}

public function get_loans_reports()
{
    $request = \Config\Services::request();

$project_status = $request->getGet('project_status');
    $project_id     = $request->getGet('project');

$model = new Reports_model();
    $result = $model->get_loansreports($project_status, $project_id);

return $this->response->setJSON(!empty($result) ? $result : ['result' => 0]);
}

public function get_loans_contact_reports()
{
    $request = \Config\Services::request();

$project_status = $request->getGet('project_status');
    $project_id     = $request->getGet('project');
   $model = new Reports_model();
    $result = $model->get_loansreports($project_status, $project_id);

return $this->response->setJSON(!empty($result) ? $result : ['result' => 0]);
}
public function get_loans_email_reports()
{
    $request = \Config\Services::request();

$project_status = $request->getGet('project_status');
    $project_id     = $request->getGet('project');

$model = new Reports_model();
    $result = $model->get_loansreports($project_status, $project_id);

return $this->response->setJSON(!empty($result) ? $result : ['result' => 0]);
}

public function get_status_agree_due_reports()
{
    $gss_model = new \App\Models\Reports_model(); // Add namespace if not already included

$project = $this->request->getGet('project');

$result = $gss_model->get_status_agree_due_reports($project);

if ($result) {
        return $this->response->setJSON($result); // Don't wrap result inside an array
    }

return $this->response->setJSON(['status' => false, 'message' => 'No data found']);
}

public function get_status_reports_datewise_agree_done()
{
       $gss_model=new Reports_model();
    $result = $gssModel->get_status_reports_datewise_agree_done();

if (!empty($result)) {
        $siteNumber = array_column($result, 'site_number');
        array_multisort($siteNumber, SORT_ASC, $result);

return $this->response->setJSON($result);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}
 
 
 public function get_payment_particulars_reports_export()
{
    $fromDate = $this->request->getGet('from_date');
    $toDate = $this->request->getGet('to_date');
    $project = $this->request->getGet('p_poject');

if (!empty($fromDate)) {
        $date = new \DateTime($fromDate);
        $fromDate = $date->format('Y-m-d');
    }

if (!empty($toDate)) {
        $date = new \DateTime($toDate);
        $toDate = $date->format('Y-m-d');
    }

$model=new Reports_model();
    $result = $model->get_payment_particulars_reports_export($fromDate, $toDate, $project);

if (!empty($result)) {
        return $this->response->setJSON(['result' => 1, 'message' => $result]);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No data found!']);
    }
}
public function get_total_dimension_for_unbooked_sites()
{
    $project = $this->request->getPost('project_id');

$model=new Reports_model();

$tot_project_sqft = $model->total_marketing_dimension($project); // Assuming it accepts $project
    $booked_sqft      = $model->total_booked_dimension($project);    // Assuming it accepts $project

if ($tot_project_sqft !== null) {
        return $this->response->setJSON([
            'result'           => 1,
            'total_marketing'  => $tot_project_sqft,
            'booked_sqft'      => $booked_sqft
        ]);
    }
}

// graph
public function executive_sales_for_chart()
{
    $db = \Config\Database::connect();

$from_date = $this->request->getPost('from_date');
    $to_date = $this->request->getPost('to_date');
    $executive = $this->request->getPost('reference');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

// Get list of ONGOING projects
    $projects = $db->table('gss_new_projects')
        ->select('project_id, project_name, project_status')
        ->where('delete_status', 'ACTIVE')
        ->where('project_status', 'ONGOING')
        ->get()
        ->getResult();

$output = [];

foreach ($projects as $project) {
        $dimensionQuery = $db->table('gss_booking_details A')
            ->selectSum('A.dimension', 'total')
            ->join('gss_bookings C', 'A.booking_id = C.booking_id')
            ->where('A.project_id', $project->project_id)
            ->where('A.delete_status', 'ACTIVE')
            ->where('C.delete_status', 'ACTIVE')
            ->where('C.booking_status', 'BOOKED')
            ->where('C.reference', $executive);

if (!empty($from_date)) {
            $dimensionQuery->where('DATE(A.booking_date1) >=', $from_date);
        }
        if (!empty($to_date)) {
            $dimensionQuery->where('DATE(A.booking_date1) <=', $to_date);
        }

$dimensionResult = $dimensionQuery->get()->getRow();

$output[] = [
            'project'         => $project->project_name,
            'project_status'  => $project->project_status,
            'dimension'       => $dimensionResult->total ?? 0
        ];
    }

return $this->response->setJSON(
        !empty($output)
            ? ['result' => 1, 'all_sales' => $output, 'total' => count($output)]
            : ['result' => 0]
    );
}

// Regd Sites
public function get_sites_reports()
{
    $db = \Config\Database::connect();

$project_id = $this->request->getGet('project_id');
    $from_date  = $this->request->getGet('from_date');
    $to_date    = $this->request->getGet('to_date');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$builder = $db->table('gss_bookings');
    $builder->select('
        gss_bookings.booking_id as book_id,
        gss_bookings.project_id,
        gss_bookings.customer_name,
        gss_bookings.address,
        gss_bookings.webportal,
        gss_bookings.logistics,
        gss_bookings.reference,
        gss_bookings.associate,
        gss_bookings.subassociate,
        gss_bookings.email as customer_email,
        gss_bookings.mobile1,
        gss_bookings.mobile2,
        gss_bookings.booking_status,
        gss_booking_details.booking_date1 as booked_on,
        gss_new_projects.project_name,
        gss_booking_details.site_number,
        gss_booking_details.dimension,
        gss_booking_details.booking_date1,
        gss_booking_details.detail_id,
        gss_plot_payments.registration_date,
        gss_plot_payments.registration_value,
        gss_plot_payments.registration_payment_mode,
        gss_plot_payment_types.regn_cheque_no,
        gss_plot_payment_types.regn_cheque_date,
        gss_plot_payment_types.regn_bank,
        gss_plot_payment_types.regn_vtr_no,
        gss_plot_payment_types.regn_online_date,
        gss_plot_payment_types.regn_dd_no,
        gss_plot_payment_types.regn_dd_date,
        gss_plot_payment_types.regn_dd_bank,
        gss_plot_payment_types.regn_ref_no,
        gss_plot_payment_types.regn_paytm_date,
        gss_plot_payment_types.regn_upiref_no,
        gss_plot_payment_types.regn_upi_date,
        gss_maintenance_details.main_amount
    ');
    $builder->join('gss_new_projects', 'gss_bookings.project_id = gss_new_projects.project_id');
    $builder->join('gss_booking_details', 'gss_bookings.booking_id = gss_booking_details.booking_id');
    $builder->join('gss_plot_payments', 'gss_bookings.booking_id = gss_plot_payments.booking_id');
    $builder->join('gss_plot_payment_types', 'gss_plot_payments.payment_id = gss_plot_payment_types.payment_id');
    $builder->join('gss_maintenance_details', 'gss_maintenance_details.booking_id = gss_bookings.booking_id', 'left');

// Filter conditions
    $builder->where('gss_bookings.delete_status', 'ACTIVE');
    $builder->where('gss_new_projects.delete_status', 'ACTIVE');
    $builder->where('gss_booking_details.delete_status', 'ACTIVE');
    $builder->where('gss_bookings.booking_status !=', 'CANCELLED');
    $builder->where('gss_plot_payments.registration_date >', '0000-00-00');
    $builder->where('gss_plot_payments.registration_delete_status', 'ACTIVE');
    $builder->where('gss_plot_payment_types.registration_delete_status', 'ACTIVE');

if (!empty($project_id)) {
        $builder->where('gss_new_projects.project_id', $project_id);
    }

if (!empty($from_date)) {
        $builder->where('gss_plot_payments.registration_date >=', $from_date);
    }

if (!empty($to_date)) {
        $builder->where('gss_plot_payments.registration_date <=', $to_date);
    }

$builder->groupBy('gss_plot_payments.booking_id');
    $builder->orderBy('ABS(gss_booking_details.site_number)', 'ASC');
    $builder->orderBy('gss_new_projects.project_name', 'ASC');

$results = $builder->get()->getResult();

$output = [];

foreach ($results as $val) {
        $data = [
            'booking_id'        => $val->book_id,
            'detail_id'         => $val->detail_id,
            'customer_name'     => $val->customer_name,
            'address'           => $val->address,
            'customer_mobile'   => $val->mobile1,
            'customer_mobile2'  => $val->mobile2,
            'customer_email'    => $val->customer_email,
            'project_name'      => $val->project_name,
            'site_number'       => $val->site_number,
            'booked_on'         => $val->booked_on,
            'registration_on_date' => $val->registration_date,
            'registration_date' => (new \DateTime($val->registration_date))->format('d-m-Y'),
            'main_amount'       => $this->formatIndianCurrency($val->main_amount),
            'registration_value1' => $val->registration_value,
            'registration_value'  => $this->formatIndianCurrency($val->registration_value),
            'registration_payment_mode' => $this->mapPaymentMode($val->registration_payment_mode),
            'num'  => '',
            'date' => '',
            'bank' => '',
        ];

// Assign number, date, bank based on payment mode
        switch ($val->registration_payment_mode) {
            case 'Cheque':
                $data['num'] = $val->regn_cheque_no;
                $data['date'] = date('d-m-Y', strtotime($val->regn_cheque_date));
                $data['bank'] = $val->regn_bank;
                break;
            case 'DD':
                $data['num'] = $val->regn_dd_no;
                $data['date'] = date('d-m-Y', strtotime($val->regn_dd_date));
                $data['bank'] = $val->regn_dd_bank;
                break;
            case 'Online Payment':
                $data['num'] = $val->regn_vtr_no;
                $data['date'] = date('d-m-Y', strtotime($val->regn_online_date));
                break;
            case 'Paytm Payment':
                $data['num'] = $val->regn_ref_no;
                $data['date'] = date('d-m-Y', strtotime($val->regn_paytm_date));
                break;
            case 'UPI Payment':
                $data['num'] = $val->regn_upiref_no;
                $data['date'] = date('d-m-Y', strtotime($val->regn_upi_date));
                break;
        }

// Get dimension
        $site_dim = $db->table('gss_new_sites')
            ->select('total_in_sqft')
            ->where([
                'delete_status' => 'ACTIVE',
                'project_id' => $val->project_id,
                'site_number' => $val->site_number
            ])
            ->get()
            ->getRow();
        $data['dimension'] = $site_dim ? $site_dim->total_in_sqft : '';

// Get extra names
        $data['web_portal']   = $this->get_name_by_id($db, 'gss_webportals', 'portal_id', $val->webportal, 'webportal');
        $data['logistics']    = $this->get_name_by_id($db, 'gss_brokers', 'broker_id', $val->logistics, 'associate_name');
        $data['reference']    = $this->get_name_by_id($db, 'gss_brokers', 'broker_id', $val->reference, 'associate_name');
        $data['associate']    = $this->get_name_by_id($db, 'gss_brokers', 'broker_id', $val->associate, 'associate_name');
        $data['subassociate'] = $this->get_name_by_id($db, 'gss_brokers', 'broker_id', $val->subassociate, 'associate_name');

$output[] = $data;
    }

return $this->response->setJSON($output);
}

private function formatIndianCurrency($num)
{
    if (empty($num)) return '';
    $explrestunits = '';
    if (strlen($num) > 3) {
        $lastthree = substr($num, -3);
        $restunits = substr($num, 0, -3);
        $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
        $expunit = str_split($restunits, 2);
        foreach ($expunit as $i => $val) {
            $explrestunits .= ($i == 0 ? (int)$val : $val) . ",";
        }
        return $explrestunits . $lastthree;
    } else {
        return $num;
    }
}

private function mapPaymentMode($mode)
{
    return [
        'Online Payment' => 'NEFT / RTGS',
        'Paytm Payment'  => 'UPI / Direct Payment',
        'UPI Payment'    => 'Credit / Debit Payment',
    ][$mode] ?? $mode;
}

private function get_name_by_id($db, $table, $key, $value, $column)
{
    if (!$value) return '';
    $row = $db->table($table)->select($column)->where($key, $value)->get()->getRow();
    return $row ? $row->$column : '';
}

public function get_mul_reg_amount_sites_reports()
{
    $db = \Config\Database::connect();
    $id = $this->request->getGet('id');
    $builder = $db->table('gss_bookings A');
    $builder->select('A.*,B.*,C.*,D.*,E.*');
    $builder->join('gss_new_projects B', 'A.project_id=B.project_id');
    $builder->join('gss_booking_details C', 'C.booking_id=A.booking_id');
    $builder->join('gss_plot_payments D', 'D.booking_id=C.booking_id');
    $builder->join('gss_registration_amount_details E', 'E.detail_id=D.detail_id');
    $builder->where('D.booking_id', $id);
    $builder->where('A.booking_status !=', 'CANCELLED');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->where('E.delete_status', 'ACTIVE');
    $builder->orderBy('B.project_name', 'ASC');
    $builder->orderBy('ABS(C.site_number)', 'ASC');
    $builder->groupBy('E.id');
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

foreach ($result as $val) {
        $data = [];
        $data['booking_id']    = $val->booking_id;
        $data['detail_id']     = $val->detail_id;
        $data['project_name']  = $val->project_name;
        $data['site_number']   = $val->site_number;

// Get dimension from gss_new_sites
        $site = $db->table('gss_new_sites')
            ->select('total_in_sqft')
            ->where('delete_status', 'ACTIVE')
            ->where('project_id', $val->project_id)
            ->where('site_number', $val->site_number)
            ->get()->getRow();

$data['dimension'] = $site ? $site->total_in_sqft : '';

// Currency formatting
        $data['reg_amount'] = $this->formatIndianCurrency($val->reg_amount);
        $data['reg_amt'] = $val->reg_amount;

// Payment mode mapping
        $modeMap = [
            'Online Payment' => 'NEFT / RTGS',
            'Paytm Payment'  => 'UPI / Direct Payment',
            'UPI Payment'    => 'Credit / Debit Payment'
        ];
        $data['reg_payment_mode'] = $modeMap[$val->reg_payment_mode] ?? $val->reg_payment_mode;

// Payment details
        $data['number'] = $val->number ?? '';
        $data['mode_date'] = !empty($val->mode_date) ? date('d-m-Y', strtotime($val->mode_date)) : '';
        $data['branch_name'] = in_array($val->reg_payment_mode, ['Cheque', 'DD']) ? $val->branch_name : '';

$data['reg_date'] = $val->reg_date != '0000-00-00' ? $val->reg_date : '';
        $data['loan_type'] = $val->loan_type;
        $data['loan_from'] = $val->loan_from;

array_push($array, $data);
    }

// Additional details from gss_plot_payments and gss_plot_payment_types
    $builder = $db->table('gss_plot_payments F');
    $builder->select('F.registration_payment_mode,F.registration_date,F.registration_amount,
        G.regn_cheque_no,G.regn_cheque_date,G.regn_bank,G.regn_vtr_no,G.regn_online_date,
        G.regn_dd_no,G.regn_dd_date,G.regn_dd_bank,G.regn_ref_no,G.regn_paytm_date,
        G.regn_upiref_no,G.regn_upi_date,G.cash_date,G.swipe_date');
    $builder->join('gss_plot_payment_types G', 'G.payment_id=F.payment_id');
    $builder->where('F.delete_status', 'ACTIVE');
    $builder->where('G.delete_status', 'ACTIVE');
    $builder->where('F.booking_id', $id);
    $extra = $builder->get()->getRow();

if ($extra) {
        $last = [];
        $payment_mode = $extra->registration_payment_mode;
        $map = [
            'Online Payment' => 'NEFT / RTGS',
            'Paytm Payment'  => 'UPI / Direct Payment',
            'UPI Payment'    => 'Credit / Debit Payment'
        ];
        $last['reg_payment_mode'] = $map[$payment_mode] ?? $payment_mode;

// Dates and numbers based on mode
        switch ($payment_mode) {
            case 'Cheque':
                $last['number'] = $extra->regn_cheque_no;
                $last['mode_date'] = date('d-m-Y', strtotime($extra->regn_cheque_date));
                $last['branch_name'] = $extra->regn_bank;
                break;
            case 'DD':
                $last['number'] = $extra->regn_dd_no;
                $last['mode_date'] = date('d-m-Y', strtotime($extra->regn_dd_date));
                $last['branch_name'] = $extra->regn_dd_bank;
                break;
            case 'Online Payment':
                $last['number'] = $extra->regn_vtr_no;
                $last['mode_date'] = date('d-m-Y', strtotime($extra->regn_online_date));
                $last['branch_name'] = '';
                break;
            case 'Paytm Payment':
                $last['number'] = $extra->regn_ref_no;
                $last['mode_date'] = date('d-m-Y', strtotime($extra->regn_paytm_date));
                $last['branch_name'] = '';
                break;
            case 'UPI Payment':
                $last['number'] = $extra->regn_upiref_no;
                $last['mode_date'] = date('d-m-Y', strtotime($extra->regn_upi_date));
                $last['branch_name'] = '';
                break;
            case 'Cash':
                $last['number'] = '';
                $last['mode_date'] = date('d-m-Y', strtotime($extra->cash_date));
                $last['branch_name'] = '';
                break;
            case 'Swipe':
                $last['number'] = '';
                $last['mode_date'] = date('d-m-Y', strtotime($extra->swipe_date));
                $last['branch_name'] = '';
                break;
            default:
                $last['number'] = '';
                $last['mode_date'] = '';
                $last['branch_name'] = '';
        }

$last['reg_amount'] = $this->formatIndianCurrency($extra->registration_amount);
        $last['reg_amt'] = $extra->registration_amount;

array_push($array, $last);
    }

return $this->response->setJSON($array);
}
public function get_sites_email_reports()
{
    $db = \Config\Database::connect();
    $project_id = $this->request->getGet('project_id');
    $from_date = $this->request->getGet('from_date');
    $to_date = $this->request->getGet('to_date');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

// Base Query
    $builder = $db->table('gss_bookings A');
    $builder->select("
        A.booking_id AS book_id, A.project_id, A.customer_name, A.address, A.webportal, A.logistics, A.reference, 
        A.associate, A.subassociate, A.email AS customer_email, A.mobile1, A.mobile2, A.booking_status,
        B.project_name, C.site_number, C.dimension, C.booking_date1 AS booked_on, C.detail_id,
        D.registration_date, D.registration_value, D.registration_payment_mode,
        E.regn_cheque_no, E.regn_cheque_date, E.regn_bank, E.regn_vtr_no, E.regn_online_date,
        E.regn_dd_no, E.regn_dd_date, E.regn_dd_bank, E.regn_ref_no, E.regn_paytm_date,
        E.regn_upiref_no, E.regn_upi_date, F.main_amount
    ");
    $builder->join('gss_new_projects B', 'A.project_id = B.project_id');
    $builder->join('gss_booking_details C', 'A.booking_id = C.booking_id');
    $builder->join('gss_plot_payments D', 'A.booking_id = D.booking_id');
    $builder->join('gss_plot_payment_types E', 'D.payment_id = E.payment_id');
    $builder->join('gss_maintenance_details F', 'F.booking_id = A.booking_id', 'left');

$builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('D.registration_delete_status', 'ACTIVE');
    $builder->where('E.registration_delete_status', 'ACTIVE');
    $builder->where('A.booking_status !=', 'CANCELLED');
    $builder->where('D.registration_date >', '0000-00-00');

if (!empty($project_id)) {
        $builder->where('B.project_id', $project_id);
    }
    if (!empty($from_date)) {
        $builder->where('D.registration_date >=', $from_date);
    }
    if (!empty($to_date)) {
        $builder->where('D.registration_date <=', $to_date);
    }

$builder->groupBy('D.booking_id');
    $builder->orderBy('ABS(C.site_number)', 'ASC');
    $builder->orderBy('B.project_name', 'ASC');

$query = $builder->get();
    $results = $query->getResult();

$data = [];

foreach ($results as $val) {
        $row = [
            'booking_id'         => $val->book_id,
            'detail_id'          => $val->detail_id,
            'customer_name'      => $val->customer_name,
            'address'            => $val->address,
            'customer_mobile'    => $val->mobile1,
            'customer_mobile2'   => $val->mobile2,
            'customer_email'     => $val->customer_email,
            'project_name'       => $val->project_name,
            'site_number'        => $val->site_number,
            'booked_on'          => $val->booked_on,
            'registration_on_date' => $val->registration_date,
            'registration_value1'  => $val->registration_value,
            'registration_value'   => $this->formatINRCurrency($val->registration_value),
            'main_amount'        => $this->formatINRCurrency($val->main_amount ?? ''),
            'registration_date'  => date('d-m-Y', strtotime($val->registration_date)),
            'dimension'          => ''
        ];

// Format registration payment mode
        $paymentMode = $val->registration_payment_mode;
        $row['registration_payment_mode'] = match($paymentMode) {
            'Online Payment' => 'NEFT / RTGS',
            'Paytm Payment'  => 'UPI / Direct Payment',
            'UPI Payment'    => 'Credit / Debit Payment',
            default          => $paymentMode
        };

// Mode-specific fields
        switch ($paymentMode) {
            case 'Cheque':
                $row['num'] = $val->regn_cheque_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_cheque_date));
                $row['bank'] = $val->regn_bank;
                break;
            case 'DD':
                $row['num'] = $val->regn_dd_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_dd_date));
                $row['bank'] = $val->regn_dd_bank;
                break;
            case 'Online Payment':
                $row['num'] = $val->regn_vtr_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_online_date));
                $row['bank'] = '';
                break;
            case 'Paytm Payment':
                $row['num'] = $val->regn_ref_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_paytm_date));
                $row['bank'] = '';
                break;
            case 'UPI Payment':
                $row['num'] = $val->regn_upiref_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_upi_date));
                $row['bank'] = '';
                break;
            default:
                $row['num'] = '';
                $row['date'] = '';
                $row['bank'] = '';
        }

// Get site dimension
        $site = $db->table('gss_new_sites')
            ->select('total_in_sqft')
            ->where([
                'delete_status' => 'ACTIVE',
                'project_id'    => $val->project_id,
                'site_number'   => $val->site_number
            ])
            ->get()->getRow();
        $row['dimension'] = $site->total_in_sqft ?? '';

// Determine booking status
        $regAmtRow = $db->table('gss_plot_payments')
            ->select('registration_amount')
            ->where([
                'delete_status' => 'ACTIVE',
                'booking_id'    => $val->book_id,
                'detail_id'     => $val->detail_id
            ])
            ->get()->getRow();

$row['booking_status'] = (!empty($regAmtRow) && $regAmtRow->registration_amount != 0)
            ? 'REGISTERED'
            : $val->booking_status;

// Get registration amount details
        $regDetails = $db->table('gss_registration_amount_details')
            ->selectSum('reg_amount', 'amount')
            ->where([
                'delete_status' => 'ACTIVE',
                'booking_id'    => $val->book_id,
                'detail_id'     => $val->detail_id
            ])
            ->get()->getRow();

$totalRegAmt = ($regDetails->amount ?? 0) + ($regAmtRow->registration_amount ?? 0);
        $row['reg_amount'] = $this->formatINRCurrency($totalRegAmt);
        $row['reg_amount1'] = $totalRegAmt;

// Optionally, add other joins (web_portal, logistics, etc.) here like in your original

$data[] = $row;
    }

return $this->response->setJSON(!empty($data) ? $data : ['result' => 0]);
}

// Utility to format INR-style currency
private function formatINRCurrency($num)
{
    if (!is_numeric($num)) return '';
    $num = (string)$num;
    if (strlen($num) <= 3) return $num;
    $last3 = substr($num, -3);
    $rest = substr($num, 0, -3);
    $rest = (strlen($rest) % 2 === 1) ? "0$rest" : $rest;
    $split = str_split($rest, 2);
    $result = '';
    foreach ($split as $i => $chunk) {
        $result .= ($i === 0 ? (int)$chunk : $chunk) . ",";
    }
    return $result . $last3;
}
public function get_sites_contact_reports()
{
    $db = \Config\Database::connect();
    $project_id = $this->request->getGet('project_id');
    $from_date = $this->request->getGet('from_date');
    $to_date = $this->request->getGet('to_date');

if (!empty($from_date)) {
        $from_date = (new \DateTime($from_date))->format('Y-m-d');
    }

if (!empty($to_date)) {
        $to_date = (new \DateTime($to_date))->format('Y-m-d');
    }

$builder = $db->table('gss_bookings A');
    $builder->select("
        A.booking_id AS book_id, A.project_id, A.customer_name, A.address, A.webportal, A.logistics,
        A.reference, A.associate, A.subassociate, A.email AS customer_email, A.mobile1, A.mobile2,
        A.booking_status, B.project_name, C.site_number, C.dimension, C.booking_date1 AS booked_on,
        C.detail_id, D.registration_date, D.registration_value, D.registration_payment_mode,
        E.regn_cheque_no, E.regn_cheque_date, E.regn_bank, E.regn_vtr_no, E.regn_online_date,
        E.regn_dd_no, E.regn_dd_date, E.regn_dd_bank, E.regn_ref_no, E.regn_paytm_date,
        E.regn_upiref_no, E.regn_upi_date, F.main_amount
    ");
    $builder->join('gss_new_projects B', 'A.project_id = B.project_id');
    $builder->join('gss_booking_details C', 'A.booking_id = C.booking_id');
    $builder->join('gss_plot_payments D', 'A.booking_id = D.booking_id');
    $builder->join('gss_plot_payment_types E', 'D.payment_id = E.payment_id');
    $builder->join('gss_maintenance_details F', 'F.booking_id = A.booking_id', 'left');

if (!empty($project_id)) {
        $builder->where('B.project_id', $project_id);
    }

if (!empty($from_date)) {
        $builder->where('D.registration_date >=', $from_date);
    }

if (!empty($to_date)) {
        $builder->where('D.registration_date <=', $to_date);
    }

$builder->groupBy('D.booking_id');
    $builder->orderBy('ABS(C.site_number)', 'ASC');
    $builder->orderBy('B.project_name', 'ASC');

$results = $builder->get()->getResult();
    $data = [];

foreach ($results as $val) {
        $formatINR = function ($num) {
            if (!is_numeric($num)) return '';
            $num = (string) $num;
            if (strlen($num) <= 3) return $num;
            $last3 = substr($num, -3);
            $rest = substr($num, 0, -3);
            $rest = (strlen($rest) % 2 === 1) ? "0$rest" : $rest;
            $expunit = str_split($rest, 2);
            $formatted = '';
            foreach ($expunit as $i => $unit) {
                $formatted .= ($i === 0 ? (int) $unit : $unit) . ",";
            }
            return $formatted . $last3;
        };

$main_amount = $val->main_amount ?? '';
        $reg_value = $val->registration_value ?? 0;

$row = [
            'booking_id'         => $val->book_id,
            'detail_id'          => $val->detail_id,
            'customer_name'      => $val->customer_name,
            'address'            => $val->address,
            'customer_mobile'    => $val->mobile1,
            'customer_mobile2'   => $val->mobile2,
            'customer_email'     => $val->customer_email,
            'project_name'       => $val->project_name,
            'site_number'        => $val->site_number,
            'booked_on'          => $val->booked_on,
            'registration_date'  => date('d-m-Y', strtotime($val->registration_date)),
            'registration_value1'=> $reg_value,
            'registration_value' => $formatINR($reg_value),
            'main_amount'        => $formatINR($main_amount),
        ];

// Registration mode name
        $paymentMode = $val->registration_payment_mode;
        $row['registration_payment_mode'] = match($paymentMode) {
            'Online Payment' => 'NEFT / RTGS',
            'Paytm Payment' => 'UPI / Direct Payment',
            'UPI Payment' => 'Credit / Debit Payment',
            default => $paymentMode
        };

// Mode details (num, date, bank)
        $row['num'] = '';
        $row['date'] = '';
        $row['bank'] = '';
        switch ($paymentMode) {
            case 'Cheque':
                $row['num'] = $val->regn_cheque_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_cheque_date));
                $row['bank'] = $val->regn_bank;
                break;
            case 'DD':
                $row['num'] = $val->regn_dd_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_dd_date));
                $row['bank'] = $val->regn_dd_bank;
                break;
            case 'Online Payment':
                $row['num'] = $val->regn_vtr_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_online_date));
                break;
            case 'Paytm Payment':
                $row['num'] = $val->regn_ref_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_paytm_date));
                break;
            case 'UPI Payment':
                $row['num'] = $val->regn_upiref_no;
                $row['date'] = date('d-m-Y', strtotime($val->regn_upi_date));
                break;
        }

// Booking dimension
        $siteData = $db->table('gss_new_sites')
            ->select('total_in_sqft')
            ->where([
                'delete_status' => 'ACTIVE',
                'project_id' => $val->project_id,
                'site_number' => $val->site_number
            ])->get()->getRow();
        $row['dimension'] = $siteData->total_in_sqft ?? '';

// Booking status check
        $regn = $db->table('gss_plot_payments')
            ->select('registration_amount')
            ->where([
                'booking_id' => $val->book_id,
                'detail_id' => $val->detail_id,
                'delete_status' => 'ACTIVE'
            ])->get()->getRow();
        $row['booking_status'] = (!empty($regn) && $regn->registration_amount > 0) ? 'REGISTERED' : $val->booking_status;

// Reg amount total
        $regAmt = $db->table('gss_registration_amount_details')
            ->selectSum('reg_amount', 'amount')
            ->where([
                'booking_id' => $val->book_id,
                'detail_id' => $val->detail_id,
                'delete_status' => 'ACTIVE'
            ])->get()->getRow();

$totalReg = ($regAmt->amount ?? 0) + ($regn->registration_amount ?? 0);
        $row['reg_amount'] = $formatINR($totalReg);
        $row['reg_amount1'] = $totalReg;

$data[] = $row;
    }

return $this->response->setJSON(!empty($data) ? $data : ['result' => 0]);
}
   
}?>