EVOLUTION-NINJA

Edit File: Reports_model.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class Reports_model extends Model{

protected $table = 'gss_login'; // Main Table
    protected $primaryKey = 'user_id';
    protected $allowedFields = ['executive_id','land_owner_id', 'user_type_id','user_type','username', 'email', 'password','mail_password','signature','roles','delete_status','created_at', 'update_at'];

public function admin_login($email, $password)
    {
        return $this->db->table('gss_login A')
                        ->select('A.land_owner_id, A.executive_id, A.user_id, A.user_type_id, A.username, A.password, A.email, B.user_type_id, B.user_type')
                        ->join('gss_user_type B', 'B.user_type_id = A.user_type_id')
                        ->where('A.email', $email)
                        ->where('A.password', $password)
                        ->where('A.delete_status', 'ACTIVE')
                        ->get()
                        ->getRow();
    }

public function get_where_result($table, $where)
    {
        return $this->db->table($table)->where($where)->get()->getResult();
    }

public function get_where_row($table, $where)
    {
        return $this->db->table($table)->where($where)->get()->getRow();
    }
    
    
    public function get_where_result_orderby_asc($table, $where, $order_by)
    {
        //
        $builder = $this->db->table($table);
        $builder->where($where);
        $builder->orderBy($order_by, 'ASC');
        $query = $builder->get();
        return $query->getResult();
    }
    
    public function get_projects_ownerwise($admin_id, $land_owner_id)
    {
        
        $builder = $this->db->table('gss_new_projects A');
        if ($land_owner_id != 0)
        {
            $builder->where('A.land_owner_id', $land_owner_id);
        }
       
        $builder->where('A.project_status', 'ONGOING');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->orderBy('A.project_name', 'ASC');
        $query = $builder->get();
        return $query->getResult();
    }
    
    public function get_projects_landownerwise($admin_id, $land_owner_id, $status)
    {
        
        $builder = $this->db->table('gss_new_projects A');
        if ($land_owner_id != 0) {
            $builder->where('A.land_owner_id', $land_owner_id);
        }
        if ($status != "ALL") {
            $builder->where('A.project_status', $status);
        }
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->orderBy('A.project_name', 'ASC');
        $query = $builder->get();

return $query->getResult();
    }
    
    public function get_total_project_sqft($project)
    {
        
        $builder = $this->db->table('gss_new_sites E');

$builder->select('SUM(E.total_in_sqft) as total_in_sqft');

$builder->where('E.delete_status', 'ACTIVE');
        $builder->where('E.project_id', $project);

$builder->groupBy('E.project_id');

$query = $builder->get();

return $query->getResult();
    }
    
    public function get_booked_project_sqft($project)
    {
        
        $builder = $this->db->table('gss_booking_details E');

$builder->select('SUM(E.dimension) as booked_sqft');

$builder->join('gss_bookings F', 'F.booking_id = E.booking_id and F.project_id = E.project_id');

$builder->where('E.delete_status', 'ACTIVE');
        $builder->where('F.delete_status', 'ACTIVE');
        $builder->where('F.booking_status', 'BOOKED');
        $builder->where('E.project_id', $project);

$builder->groupBy('E.project_id');

$query = $builder->get();

return $query->getResult();
    }
    
 public function booking_reports($from_date, $to_date, $project, $reference, $logistic, $associate, $ported)
    {
        $session = session();
        $user_type = $session->get('user_type');
        $user_type_id = $session->get('user_type_id');
        $user_id = $session->get('admin_id');
        $executive_id = $session->get('executive_id');
        
        // Load database connection
$builder = $this->db->table('gss_bookings A');

// Select columns
$builder->select('A.logistics_two, A.reference_two, A.nominee_contact, A.booking_id, A.project_id, A.customer_name, A.address, A.webportal, A.logistics, A.reference, A.khata_status, 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, A.source_type, D.status as d_status');

// Join with other tables
$builder->join('gss_new_projects B', 'B.project_id = A.project_id');
$builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
$builder->join('gss_new_sites D', 'D.project_id = C.project_id AND D.site_number = C.site_number');

// Add conditions
if (!empty($from_date)) {
    $builder->where('C.booking_date1 >=', $from_date);
}

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

if ($project > 0) {
    $builder->where('A.project_id', $project);
}

if ($logistic > 0) {
    $builder->where('A.logistics', $logistic);
}

if ($associate > 0) {
    $builder->where('A.associate', $associate);
}

if ($reference > 0) {
    $builder->where('A.reference', $reference);
}

if ($ported > 0) {
    $builder->where('A.webportal', $ported);
}

if ($user_type == 'Sales') {
    $builder->where('A.reference', $executive_id);
}

// Additional conditions
$builder->where('A.booking_status', 'BOOKED');
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->where('C.delete_status', 'ACTIVE');

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

// Execute the query
$query = $builder->get();
$result = $query->getResult();

// Process the result
$array = [];
$total_sqft_array = [];

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

// Assuming a method for querying `gss_plot_payments`
    $reg_result = $this->db->table('gss_plot_payments')
                            ->select('registration_date')
                            ->where('delete_status', 'ACTIVE')
                            ->where('booking_id', $val->booking_id)
                            ->get()
                            ->getRow();

if ($reg_result && !empty($reg_result->registration_date) && $reg_result->registration_date != '0') {
    try {
        $datetime = new \DateTime($reg_result->registration_date);
        $data['registration_date'] = $datetime->format('d-m-Y');
    } catch (\Exception $e) {
        $data['registration_date'] = ''; // fallback on failure
    }
} else {
    $data['registration_date'] = '';
}

// Assign other values
    $data['booking_id'] = $val->booking_id;
    $data['customer_name'] = $val->customer_name;
    $data['address'] = $val->address;
    $data['mobile1'] = $val->mobile1 . PHP_EOL . $val->mobile2;
    $data['nominee_contact'] = $val->nominee_contact;
    $data['khata_status'] = $val->d_status;
    $data['customer_email'] = $val->customer_email;
    $data['project_name'] = $val->project_name;
    $data['site_number'] = $val->site_number;
    $data['booking_dimension'] = $val->dimension;
    $data['source_type'] = $val->source_type;

// Fetch dimension and total sqft for the site
    $site_result = $this->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();

if ($site_result) {
        $data['dimension'] = $site_result->total_in_sqft;
        $total_sqft = (float) $site_result->total_in_sqft;
    } else {
        $data['dimension'] = '';
        $total_sqft = 0;
    }

// Fetch total sqft for the project and site
    $site_result1 = $this->db->table('gss_new_sites')
                             ->selectSum('total_in_sqft', 'total_in_sqft')
                             ->where('delete_status', 'ACTIVE')
                             ->where('project_id', $val->project_id)
                             ->where('site_number', $val->site_number)
                             ->get()
                             ->getRow();

if ($site_result1) {
        $data['total_sqft'] = (float) $site_result1->total_in_sqft;
    } else {
        $data['total_sqft'] = '';
    }

array_push($total_sqft_array, $total_sqft);

// Balance sqft calculation
    $data['balance_sqft'] = (float) $data['total_sqft'] - (float) $data['booking_dimension'];

// Format the booking date
    $datetime = new \DateTime($val->booked_on);
    $data['booking_date'] = $datetime->format('d-m-Y');

// Fetch web portal details if available
    if ($val->webportal > 0) {
        $web_result = $this->db->table('gss_webportals A')
                               ->join('gss_bookings B', 'B.webportal = A.portal_id')
                               ->select('A.Webportal')
                               ->where('B.delete_status', 'ACTIVE')
                               ->where('A.portal_id', $val->webportal)
                               ->get()
                               ->getRow();

$data['web_portal'] = $web_result ? $web_result->Webportal : '';
    } else {
        $data['web_portal'] = '';
    }

// Fetch logistics details
    $logistics = $this->getBrokerDetails($val->logistics);
    $logistics_two = $this->getBrokerDetails($val->logistics_two);

$data['logistics'] = $logistics . PHP_EOL . $logistics_two;

// Fetch reference details
    $reference = $this->getBrokerDetails($val->reference);
    $reference_two = $this->getBrokerDetails($val->reference_two);

$data['reference'] = $reference . PHP_EOL . $reference_two;

// Fetch associate details
    $data['associate'] = $this->getBrokerDetails($val->associate);

// Fetch subassociate details
    $data['subassociate'] = $this->getBrokerDetails($val->subassociate);

// Fetch agreement date
    $agreement_date = $this->db->table('gss_plot_payments')
                               ->select('agreement_date')
                               ->where('delete_status', 'ACTIVE')
                               ->where('booking_id', $val->booking_id)
                               ->get()
                               ->getRow();

$data['agreement_date'] = $agreement_date ? $agreement_date->agreement_date : '';

array_push($array, $data);
}

// Return the results
return $array;

}

private function getBrokerDetails($broker_id)
{
    if ($broker_id == 0) {
        return "";
    }

$result = $this->db->table('gss_brokers A')
                       ->join('gss_bookings B', 'B.reference = A.broker_id')
                       ->select('A.associate_name as reference')
                       ->where('B.delete_status', 'ACTIVE')
                       ->where('A.broker_id', $broker_id)
                       ->get()
                       ->getRow();

return $result ? $result->reference: '';
}

public function payment_reports_receivable($from_date,$to_date,$p_poject)
 {
  $builder = $this->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.khata_status, A.mobile1, A.mobile2, A.created_at, C.booking_date1 as booked_on, B.project_name, C.site_number, C.dimension, C.booking_date1, C.registration_due_amount, C.sales_agreement_due_amount, C.tsv, C.sales_agreement_due_date, C.registration_due_date, C.gss_amount, C.land_owner_amount, D.status as d_status')
    ->join('gss_new_projects B', 'B.project_id = A.project_id')
    ->join('gss_booking_details C', 'C.booking_id = A.booking_id')
    ->join('gss_new_sites D', 'D.project_id = C.project_id AND D.site_number = C.site_number')
    ->where('A.booking_status', 'BOOKED')
    ->where('A.delete_status', 'ACTIVE')
    ->where('B.delete_status', 'ACTIVE')
    ->where('C.delete_status', 'ACTIVE');

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

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

if (!empty($p_poject) && $p_poject > 0) {
    $builder->where('A.project_id', $p_poject);
}

$builder->groupBy('C.booking_id');
$builder->orderBy('B.project_name', 'ASC');
$builder->orderBy('ABS(C.site_number)', 'ASC'); // Note: ABS might not work directly in builder

$query = $builder->get();
$result = $query->getResult(); // or getResultArray() if you want an array

$array = [];

foreach ($result as $val) {
    $data = [];
     $booking_id              = $val->booking_id;
    $data['booking_id']         = $val->booking_id;
    $data['booking_date']       = !empty($val->booking_date1) ? date('d-m-Y', strtotime($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['khata_status']       = $val->d_status;
    $data['project_name']       = $val->project_name;
    $data['site_number']        = $val->site_number;
    $data['land_owner_amount1'] = $val->land_owner_amount;
    $data['gss_amount1']        = $val->gss_amount;
    $data['tsv1']               = $val->tsv;

// Future logic for land_owner_amount or rest units can be added here.
    $num = $val->land_owner_amount;
    $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 => $unit) {
        if($i == 0) {
            $explrestunits .= (int)$unit . ","; // removes leading 0 if added
        } else {
            $explrestunits .= $unit . ",";
        }
    }

$land_owner_amount_total = $explrestunits . $lastthree;
} else {
    $land_owner_amount_total = $num;
}

$data['land_owner_amount'] = $land_owner_amount_total;

$num = $val->gss_amount;
$explrestunits = "";

for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }

$gss_amount_total = $explrestunits . $lastthree;
} else {
    $gss_amount_total = $num;
}

$data['gss_amount']         = $gss_amount_total;
$data['site_number']        = $val->site_number;
$data['booking_dimension']  = $booking_dimension = $val->dimension;

$db = \Config\Database::connect();

$builder = $db->table('gss_new_sites A');
$builder->select('A.total_in_sqft, A.site_id, A.site_number');
$builder->where('A.project_id', $val->project_id);
$builder->where('A.site_number', $val->site_number);
$builder->where('A.delete_status', 'ACTIVE');

$query = $builder->get();
$res   = $query->getRow();
$total_sqft = 0;

if ($res) {
    $data['dimension'] = $total_sqft = $res->total_in_sqft;
} else {
    $data['dimension'] = '';
}
$data['balance_sqft'] = $total_sqft - (float) $booking_dimension;

$data['booked_on_date'] = $val->booked_on;
$datetime = new \DateTime($data['booked_on_date']);
$c_date = $datetime->format('d-m-Y');
$data['booked_on'] = $c_date;

$num = $val->tsv;
$explrestunits = "";

if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $tsv_total = $explrestunits . $lastthree;
} else {
 $tsv_total = $num;
}
$data['tsv'] = $tsv_total;

$num = $val->registration_due_amount;
$explrestunits = "";

for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int) $expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }

$registration_due_amount_total = $explrestunits . $lastthree;
} else {
    $registration_due_amount_total = $num;
}

$data['registration_due'] = $registration_due_amount_total;

$num = $val->sales_agreement_due_amount;
$explrestunits = "";

for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int) $expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }

$sales_agreement_due_amount_total = $explrestunits . $lastthree;
} else {
    $sales_agreement_due_amount_total = $num;
}
$data['aggreement_due']   = $sales_agreement_due_amount_total;
$data['aggr_due_date']    = $val->sales_agreement_due_date;
$data['regn_due_date']    = $val->registration_due_date;
$data['booking_status']   = $val->booking_status;

$tsv = (int) $val->tsv;

$instal_due_sql = "SELECT installment_due_amount, installment_due_date 
                   FROM gss_booking_installments 
                   WHERE delete_status = 'ACTIVE' AND booking_id = '$booking_id'";
$res = $this->db->query($instal_due_sql)->getRow();

if ($res) {
    $num = $res->installment_due_amount;
    $explrestunits = "";

for ($i = 0; $i < sizeof($expunit); $i++) {
 $explrestunits .= ($i == 0) ? (int) $expunit[$i] . "," : $expunit[$i] . ",";
 }

$installment_due_amount_total = $explrestunits . $lastthree;
    } else {
        $installment_due_amount_total = $num;
    }

$data['installment_due']   = $installment_due_amount_total;
    $data['install_due_date']  = $res->installment_due_date;
} else {
    $data['installment_due']   = "";
    $data['install_due_date']  = "";
}
// --------------------------------------------------------------------------------------------------------------------------------------
$booking_sql = "SELECT booking_amount1, booking_amount2, 
                (SUM(booking_amount1) + SUM(booking_amount2)) AS total_booking_amount 
                FROM gss_booking_details 
                WHERE delete_status = 'ACTIVE' AND booking_id = '$booking_id'";

$result1 = $this->db->query($booking_sql)->getRow();

$data['booking_amount1'] = $result1->total_booking_amount;

$num = $result1->total_booking_amount;
$explrestunits = "";

for ($i = 0; $i < count($expunit); $i++) {
 $explrestunits .= ($i == 0) ? (int) $expunit[$i] . "," : $expunit[$i] . ",";
 }

$total_booking_amount_total = $explrestunits . $lastthree;
} else {
    $total_booking_amount_total = $num;
}

$data['booking_amount'] = $total_booking_amount_total;
// -------------------------------------------------------------------------------------------------------------------//

$regn_sql1 = "SELECT SUM(reg_amount) AS registration_amount  
              FROM gss_registration_amount_details 
              WHERE delete_status = 'ACTIVE' AND booking_id = '$booking_id'";

$db = \Config\Database::connect();
$result5 = $db->query($regn_sql1)->getResult();
$regn_sums = '';

foreach ($result5 as $val) {
    $payment_registration_sum = $val->registration_amount;

$num = $val->registration_amount;
 $explrestunits = "";
 if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $registration_amount_total = $explrestunits . $lastthree;
 } else {
 $registration_amount_total = $num;
 }

$regn_sums = $registration_amount_total;
}

//--------------------------------------------------------------------------------------------------------------//
$regn_sql = "SELECT registration_amount, registration_value 
             FROM gss_plot_payments 
             WHERE delete_status = 'ACTIVE' 
             AND registration_delete_status = 'ACTIVE' 
             AND booking_id = '$booking_id'";

$db = \Config\Database::connect();
$result2 = $db->query($regn_sql)->getResult();

$regn_sum = 0;
$reg_value = 0;
$reg_amount = 0;

foreach ($result2 as $val) {
    $payment_regis_amount = $val->registration_amount;

//---------------------------------------------------//
 $num = $val->registration_amount;
 $explrestunits = "";
 if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $registration_amount_total = $explrestunits . $lastthree;
 } else {
 $registration_amount_total = $num;
 }
}
//------------------------------------------------------------------------------------------------//
$regn_sum = $registration_amount_total;

//--------------------------------------------------//
$num = isset($val->registration_value) ? $val->registration_value : 0;

$explrestunits = "";

if(strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for($i = 0; $i < sizeof($expunit); $i++) {
 if($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $registration_value_total = $explrestunits . $lastthree;
} else {
 $registration_value_total = $num;
}

$reg_value = $registration_value_total; // ✅ Store it
//------------------------------------------------------------------------------//

$reg_value = $registration_value_total;

if ($regn_sums > 0) {
    $reg_amount = $payment_registration_sum + $payment_regis_amount;
    $num = $reg_amount;
    $explrestunits = "";

$data['registration_amount'] = $payment_registration_value_total;
} else {
    $reg_amount = $payment_regis_amount;
    $num = $reg_amount;
    $explrestunits = "";

$data['registration_amount'] = $payment_regis_value_total;
}

$data['registration_value'] = $reg_value;
//------------------------------------------------------------//
$aggr_sql = "SELECT agreement_amount 
             FROM gss_plot_payments 
             WHERE delete_status = 'ACTIVE' 
             AND agreement_delete_status = 'ACTIVE' 
             AND booking_id = '$booking_id'";

$result3 = $this->db->query($aggr_sql)->getResult();

$aggr_sum = 0;
$aggr_sum1 = 0;
$aggr_sum2 = 0;

foreach ($result3 as $val) {
       $agreement_amount=$val->agreement_amount;
       $aggr_sum2 = $val->agreement_amount;
       $explrestunits = "" ;

if (strlen($agreement_amount) > 3) {
        $lastthree = substr($agreement_amount, -3);
        $restunits = substr($agreement_amount, 0, -3);
        $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
        $expunit = str_split($restunits, 2);
        $explrestunits = "";

foreach ($expunit as $i => $unit) {
            $explrestunits .= ($i == 0 ? (int)$unit : $unit) . ",";
        }

$agreement_amount_total = $explrestunits . $lastthree;
    } else {
        $agreement_amount_total = $agreement_amount;
    }

$aggr_sum = $agreement_amount_total;
}

$data['agreement_amount'] = $aggr_sum;
$data['agreement_amount2'] = $aggr_sum1;
//--------------------------------------------------------------------------------//

// Load the DB connection
$db = \Config\Database::connect();

// CI4 query builder (raw SQL)
$instal_sql = "
    SELECT 
        installment_amount1,
        installment_amount2,
        SUM(installment_amount1) AS total_instal_amount 
    FROM 
        gss_plot_payments 
    WHERE 
        delete_status = 'ACTIVE' 
        AND install_delete_status = 'ACTIVE' 
        AND booking_id = '$booking_id'
";

$result4 = $db->query($instal_sql)->getResult();

$instal_sum = 0;

foreach ($result4 as $val) {
    $instal_sum += $val->total_instal_amount;
    $num = $instal_sum;
    $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);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 $explrestunits .= ($i == 0) ? (int)$expunit[$i] . "," : $expunit[$i] . ",";
 }
 $total_instal_amount_total = $explrestunits . $lastthree;
 } else {
 $total_instal_amount_total = $num;
 }
}

$data['installment_amount'] = $total_instal_amount_total;

//------------------------------------------------------------//

$data['receivable'] = $tsv - ($instal_sum + $result1->total_booking_amount);

$subtotal = (int)$instal_sum + (int)$result1->total_booking_amount + (int)$payment_registration_sum + (int)$aggr_sum2;

$data['subtotal2'] = $subtotal;

$num = $subtotal;
$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);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 $explrestunits .= ($i == 0) ? (int)$expunit[$i] . "," : $expunit[$i] . ",";
 }
 $subtotal_total = $explrestunits . $lastthree;
} else {
 $subtotal_total = $num;
}

$data['subtotal'] = $subtotal_total;

$balance = $tsv - $subtotal;

$data['balance1'] = $balance;

$num = $balance;
$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);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 $explrestunits .= ($i == 0) ? (int)$expunit[$i] . "," : $expunit[$i] . ",";
 }
 $balance_total = $explrestunits . $lastthree;
} else {
 $balance_total = $num;
}

$data['balance'] = $balance_total;

array_push($array, $data);
}
return $array;

}

public function cancellation_reports($from_date, $to_date, $project_id)
{
    $db = \Config\Database::connect();

$select = "select 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.cancellation_id,D.refunded_date,D.refunded_amount,D.refunded,D.cheque_no,D.cheque_date,D.bank_name,D.refunded_payment_mode,D.created_at,D.due_amount,D.due_with,D.note,D.cancellation_date";
    $from   = " from gss_bookings A,gss_new_projects B,gss_booking_details C,gss_cancellations D";
    $where  = " where ";
    $where .= "B.project_id = A.project_id and C.booking_id = A.booking_id and D.booking_id=A.booking_id and";

if ($from_date != '') {
        $where .= " DATE(D.cancellation_date) >= '$from_date' and ";
    }

if ($to_date != '') {
 $where .= " DATE(D.cancellation_date) <= '$to_date' and ";
 }

if ($project_id > 0) {
        $where .= " A.project_id = '$project_id' and ";
    }

$where .= " A.delete_status = 'ACTIVE' and ";
    $where .= " B.delete_status = 'ACTIVE' and ";
    $where .= " C.delete_status = 'ACTIVE' and ";
    $where .= " D.delete_status = 'ACTIVE' order by B.project_name ASC, ABS(C.site_number) ASC";
    $sql    = $select . $from . $where;

$result = $db->query($sql)->getResult();

$refunded_amount = 0;
    $array = [];

foreach ($result as $val) {
        $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;

$builder = $db->table('gss_new_sites E');
        $builder->select('E.total_in_sqft');
        $builder->where('E.delete_status', 'ACTIVE');
        $builder->where('E.project_id', $val->project_id);
        $builder->where('E.site_number', $val->site_number);
        $site_result = $builder->get()->getRow();

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

$data['booked_on'] = $val->booked_on;
        $data['refunded'] = $val->refunded;
        $data['payment_type'] = $val->refunded_payment_mode;

$cancellation_id = $val->cancellation_id;

$builder = $db->table('gss_cancellation_refunds E');
        $builder->select('refunded_amount');
        $builder->where('delete_status', 'ACTIVE');
        $builder->where('cancellation_id', $cancellation_id);
        $c_result = $builder->get()->getResult();

if ($c_result) {
            foreach ($c_result as $r_amt) {
                $refunded_amount += (int)$r_amt->refunded_amount;

if ($refunded_amount) {
                    $data['refunded_amount'] = $refunded_amount;
                    $num = $refunded_amount;
                    $explrestunits = "";

if (strlen($num) > 3) {
                        $lastthree = substr($num, strlen($num) - 3);
                        $restunits = substr($num, 0, strlen($num) - 3);
                        $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
                        $expunit = str_split($restunits, 2);
                        foreach ($expunit as $i => $unit) {
                            $explrestunits .= ($i == 0) ? (int)$unit . "," : $unit . ",";
                        }
                        $refunded_amount_total = $explrestunits . $lastthree;
                    } else {
                        $refunded_amount_total = $num;
                    }
                    $data['refunded_amount11'] = $refunded_amount_total;
                }
            }
        } else {
            $data['refunded_amount'] = $val->refunded_amount;
            $num = $val->refunded_amount;
            $explrestunits = "";

if (strlen($num) > 3) {
                $lastthree = substr($num, strlen($num) - 3);
                $restunits = substr($num, 0, strlen($num) - 3);
                $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
                $expunit = str_split($restunits, 2);
                foreach ($expunit as $i => $unit) {
                    $explrestunits .= ($i == 0) ? (int)$unit . "," : $unit . ",";
                }
                $refunded_amount_total = $explrestunits . $lastthree;
            } else {
                $refunded_amount_total = $num;
            }
            $data['refunded_amount11'] = $refunded_amount_total;
        }

$data['refunded_date'] = $val->refunded_date;
        $data['cheque_no']     = $val->cheque_no;
        $data['cheque_date']   = $val->cheque_date;
        $data['bank_name']     = $val->bank_name;
        $data['cancelled_on']  = $val->cancellation_date;

if ($val->refunded == "Yes") {
            $data['due_amount'] = $val->due_amount;
            $data['due_with'] = $val->due_with;
            $data['note'] = $val->note;
        } else {
            $data['due_amount'] = "";
            $data['due_with'] = "";
        }

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

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

// Reference
        if ($val->reference == 0) {
            $data['executive'] = "";
        } else {
            $builder = $db->table('gss_brokers A');
            $builder->select('A.*,B.*,A.associate_name as reference');
            $builder->join('gss_bookings B', 'B.reference=A.broker_id');
            // $builder->where('A.delete_status', 'ACTIVE'); // commented like original
            $builder->where('B.delete_status', 'ACTIVE');
            $builder->where('A.broker_id', $val->reference);
            $reference_result = $builder->get()->getRow();
            $data['executive'] = $reference_result ? $reference_result->reference : "";
        }

array_push($array, $data);
    }

return $array;
}

//preethi

public function get_maintenance_reports($project_id = null)
    {
        $builder = $this->db->table('gss_maintenance_details A');
        $builder->select('A.*, B.*, C.*');
        $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
        $builder->join('gss_new_projects C', 'C.project_id = B.project_id');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('B.booking_status', 'BOOKED');

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

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

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

$array = [];

foreach ($result as $val) {
            $data = [
                'project_id'       => $val->project_id,
                'customer_name'    => $val->client_name,
                'project_name'     => $val->project_name,
                'dimension'        => $val->dimension,
                'per_sq_ft'        => $val->per_sq_ft,
                'maintainance_amount' => $val->main_amount,
                'site'             => $val->site,
                'no_of_yrs'        => $val->no_of_yrs,
            ];

// Format amount
            $num = $val->main_amount;
            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 => $part) {
                    $explrestunits .= ($i === 0) ? ((int)$part) . "," : $part . ",";
                }
                $main_amount = $explrestunits . $lastThree;
            } else {
                $main_amount = $num;
            }

$data['main_amount'] = $main_amount;

// Format payment mode
            $payment_mode = $val->payment_mode;
            switch ($payment_mode) {
                case 'Online Payment':
                    $data['payment_mode'] = 'NEFT / RTGS';
                    $data['num'] = $val->vtr_no;
                    $data['ref'] = "";
                    $data['date'] = date('d-m-Y', strtotime($val->vtr_date));
                    $data['bank'] = "";
                    break;
                case 'Paytm Payment':
                    $data['payment_mode'] = 'UPI/Direct Payment';
                    $data['num'] = "";
                    $data['ref'] = $val->paytm_ref_no;
                    $data['date'] = date('d-m-Y', strtotime($val->paytm_date));
                    $data['bank'] = "";
                    break;
                case 'UPI Payment':
                    $data['payment_mode'] = 'Credit / Debit Payment';
                    $data['num'] = "";
                    $data['ref'] = $val->upi_ref_no;
                    $data['date'] = date('d-m-Y', strtotime($val->upi_date));
                    $data['bank'] = "";
                    break;
                case 'Cheque':
                    $data['num'] = $val->check_no;
                    $data['ref'] = "";
                    $data['date'] = date('d-m-Y', strtotime($val->check_date));
                    $data['bank'] = $val->check_bank;
                    break;
                case 'DD':
                    $data['num'] = $val->dd_no;
                    $data['ref'] = "";
                    $data['date'] = date('d-m-Y', strtotime($val->dd_date));
                    $data['bank'] = $val->dd_bank;
                    break;
                case 'Cash':
                case 'Swipe':
                    $data['num'] = "";
                    $data['ref'] = "";
                    $data['date'] = "";
                    $data['bank'] = "";
                    break;
                default:
                    $data['payment_mode'] = $payment_mode;
                    $data['num'] = "";
                    $data['ref'] = "";
                    $data['date'] = "";
                    $data['bank'] = "";
                    break;
            }

$array[] = $data;
        }

return $array;
    }

public function get_maintenance_due_reports($project_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_bookings A');
    
    $builder->select('A.*, B.*, C.*, C.no_of_years as c_years, D.*, B.booking_id as b_booking_id');
    $builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
    $builder->join('gss_new_projects C', 'C.project_id = B.project_id');
    $builder->join('gss_new_sites D', 'D.project_id = B.project_id AND D.site_number = B.site_number');
    
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_status', 'BOOKED');
    $builder->where('B.maintain_status !=', 'RECEIVED');

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

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

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

$array = [];

foreach ($res as $val) {
        $booking_amount = $val->booking_amount1 + $val->booking_amount2;
        $tsv = $val->tsv;
        $booking_id = $val->booking_id;

// Fetch payment details
        $pay_builder = $db->table('gss_plot_payments A');
        $pay_builder->select('A.booking_id as b_booking_id, A.agreement_amount, A.installment_amount1, A.registration_amount');
        $pay_builder->where([
            'A.booking_id' => $booking_id,
            'A.agreement_delete_status' => 'ACTIVE',
            'A.install_delete_status'   => 'ACTIVE',
            'A.registration_delete_status' => 'ACTIVE',
            'A.delete_status' => 'ACTIVE'
        ]);
        $pay_result = $pay_builder->get()->getResult();

$tot_agree = 0;
        $tot_inst = 0;
        $tot_reg = 0;

foreach ($pay_result as $pay) {
            $tot_agree = $pay->agreement_amount ?? 0;
            $tot_inst += (float)($pay->installment_amount1 ?? 0);
            $tot_reg = $pay->registration_amount ?? 0;
            break;
        }

// Get extra registration amounts
        $reg_builder = $db->table('gss_registration_amount_details');
        $reg_builder->select('reg_amount');
        $reg_builder->where(['booking_id' => $booking_id, 'delete_status' => 'ACTIVE']);
        $reg_results = $reg_builder->get()->getResult();

$tot_reg1 = 0;
        foreach ($reg_results as $reg) {
            $tot_reg1 += $reg->reg_amount ?? 0;
        }

$tot_regs = $tot_reg + $tot_reg1;

$cal_tot = $booking_amount + $tot_agree + $tot_inst + $tot_regs;

if ($tsv == $cal_tot) {
            $data = [
                'project_id' => $val->project_id,
                'project_name' => $val->project_name,
                'site_number' => $val->site_number,
                'customer_name' => $val->customer_name,
                'dimension' => $val->total_in_sqft,
            ];

$main_amount = round((float)$val->c_years * (float)$val->total_in_sqft * (float)$val->maintenance_per_sqft);
            $data['main_amount'] = number_format($main_amount, 0, '.', ',');

$array[] = $data;
        }
    }

return $array;
}

public function get_executive_incentive_reports($from_date, $to_date, $executive, $month, $year)
{
    $builder = $this->db->table('gss_executive_incentives A');
    $builder->select('*')
            ->where('A.delete_status', 'ACTIVE');

if (!empty($executive)) {
        $builder->where('A.broker_id', $executive);
    }

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

$final = [];

foreach ($result as $res) {
        $data = [
            'id'         => $res->id,
            'created_at' => date('d-m-Y', strtotime($res->created_at)),
            'remark'     => $res->exe_remark,
            'basic_target' => $this->formatNumberIndian($res->basic_target),
            'total_target' => $this->formatNumberIndian($res->total_target),
            'duration'     => $res->duration,
            'achieved'     => $res->achieved,
            'cancelled'    => $res->cancelled,
            'excess'       => $res->excess,
            'shortage'     => (!empty($res->shortage) && $res->shortage !== "NaN") ? $res->shortage : 0,
            'net_achieved' => (!empty($res->net_achieved) && $res->net_achieved !== "NaN") ? $res->net_achieved : 0,
        ];

$project_sites = json_decode($res->projects_sites);
        foreach ($project_sites as $pro_sites) {
            $project_ids = explode(',', $pro_sites->project_id);
            $site_numbers = explode(',', $pro_sites->site_number);

foreach ($project_ids as $key => $project_id) {
                $site_number = $site_numbers[$key];

$bookingBuilder = $this->db->table('gss_bookings A');
                $bookingBuilder->select('A.reference, A.booking_id, A.customer_name, A.created_at AS a_created_at, A.booking_status,
                    B.shared_executive_amount, B.project_id, B.site_number, B.dimension, B.booking_date1,
                    C.broker_id, C.associate_name,
                    E.agreement_date, E.registration_date,
                    D.project_id, D.project_name,
                    F.incentive_status');
                $bookingBuilder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
                $bookingBuilder->join('gss_brokers C', 'C.broker_id = A.reference', 'left');
                $bookingBuilder->join('gss_new_projects D', 'D.project_id = B.project_id');
                $bookingBuilder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
                $bookingBuilder->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number');
                $bookingBuilder->where('A.booking_status !=', 'ENQUIRED')
                               ->where('F.project_id', $project_id)
                               ->where('F.site_number', $site_number)
                               ->where('A.booking_status', 'BOOKED')
                               ->where('A.delete_status', 'ACTIVE')
                               ->where('B.delete_status', 'ACTIVE')
                               ->where('D.delete_status', 'ACTIVE');

if (!empty($from_date)) {
 $bookingBuilder->where('DATE(B.booking_date1) >=', $from_date);
 }
 if (!empty($to_date)) {
 $bookingBuilder->where('DATE(B.booking_date1) <=', $to_date);
 }
 if (!empty($month)) {
 $bookingBuilder->where('MONTH(B.booking_date1)', $month);
 }
 if (!empty($year)) {
 $bookingBuilder->where('YEAR(B.booking_date1)', $year);
 }
 if (!empty($executive)) {
 $bookingBuilder->where('A.reference', $executive);
 }

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

$bookings = $bookingBuilder->get()->getResult();

foreach ($bookings as $booking) {
                    $data['month'] = $this->getMonthName($month);
                    $data['booking_id'] = $booking->booking_id;
                    $data['booking_date'] = date('d-m-Y', strtotime($booking->booking_date1));
                    $data['booking_status'] = $booking->booking_status;
                    $data['project_id'] = $booking->project_id;
                    $data['project_name'] = $booking->project_name;
                    $data['site_number'] = $booking->site_number;
                    $data['customer_name'] = $booking->customer_name;
                    $data['dimension'] = $booking->dimension;
                    $data['associate_name'] = $booking->associate_name;
                    $data['incentive_status'] = $booking->incentive_status;

$details = $this->db->table('gss_booking_details')
                                        ->where('booking_id', $booking->booking_id)
                                        ->get()->getRow();

if (!empty($details->shared_executive_amount)) {
                        $data['net_amt'] = $this->formatNumberIndian($details->shared_executive_amount);
                    } else {
                        $broker = $this->db->table('gss_brokers')
                                           ->where('broker_id', $executive)
                                           ->get()->getRow();
                        $data['net_amt'] = $this->formatNumberIndian($broker->incentive_rupees ?? 0);
                    }

$final[] = $data;
                }
            }
        }
    }

return $final;
}

private function formatNumberIndian($num)
{
    $num = (string) $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);
        $formatted = '';
        foreach ($expUnit as $i => $unit) {
            $formatted .= ($i == 0 ? (int)$unit : $unit) . ',';
        }
        return $formatted . $lastThree;
    } else {
        return $num;
    }
}

private function getMonthName($month)
{
    $months = [
        '01' => 'January', '02' => 'February', '03' => 'March',
        '04' => 'April', '05' => 'May', '06' => 'June',
        '07' => 'July', '08' => 'August', '09' => 'September',
        '10' => 'October', '11' => 'November', '12' => 'December'
    ];
    return $months[str_pad($month, 2, '0', STR_PAD_LEFT)] ?? '';
}

public function get_follow_ups_reports($project, $reminderDate)
{
    $db = \Config\Database::connect();
    $session = session();
    $adminId = $session->get('admin_id');

$builder = $db->table('gss_bookings A');
    $builder->select('A.*, A.booking_id as a_bookings_id, B.*, B.booking_id as b_booking_id, C.*, C.booking_id as c_booking_id, D.project_name');
    $builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
    $builder->join('gss_status_conversation C', 'C.booking_id = A.booking_id', 'left');
    $builder->join('gss_new_projects D', 'D.project_id = A.project_id');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->where('B.site_number !=', '');

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

if (!empty($reminderDate)) {
        $builder->where('C.reminder_date', $reminderDate);
    }

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

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

$array = [];

foreach ($result as $res) {
        $data = [];
        $data['selected_department'] = $res->selected_department;

$remarks = '';
        $bookingId = $res->a_bookings_id;

$isSelf = $res->selected_department === 'self';

$sql = "
            SELECT A.*, B.*, A.created_at as latest_date, C.username
            FROM gss_status_conversation A
            JOIN gss_bookings B ON B.booking_id = A.booking_id
            JOIN gss_login C ON C.user_id = A.created_by
            WHERE A.booking_id = ? 
                AND A.selected_department " . ($isSelf ? "= 'self'" : "!= 'self'") . "
                AND A.delete_status = 'ACTIVE'
            ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
        ";

$convnResult = $db->query($sql, [$bookingId])->getRow();

if (!empty($convnResult)) {
            $sqlReply = "
                SELECT A.*, B.*, A.created_at as latest_date, C.username
                FROM gss_status_conversation_replies A
                JOIN gss_bookings B ON B.booking_id = A.booking_id
                JOIN gss_login C ON C.user_id = A.created_by
                JOIN gss_status_conversation D ON A.status_conversation_id = D.id
                WHERE A.booking_id = ?
                    AND A.status_conversation_id = ?
                    AND D.selected_department " . ($isSelf ? "= 'self'" : "!= 'self'") . "
                    AND A.delete_status = 'ACTIVE'
                ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
            ";

$convnReplyResult = $db->query($sqlReply, [$bookingId, $convnResult->id])->getRow();

if (!empty($convnReplyResult)) {
                $remarks = $convnReplyResult->reply;
            } else {
                $remarks = $convnResult->conversation;
            }
        }

$data['remarks']       = $remarks;
        $data['booking_id']    = $res->a_bookings_id;
        $data['site_number']   = $res->site_number;
        $data['customer_name'] = $res->customer_name;
        $data['dimension']     = $res->dimension;
        $data['project_name']  = $res->project_name;

$array[] = $data;
    }

return $array;
}

public function client_reports($from_date, $to_date, $c_project, $reference)
{
  
    $db = \Config\Database::connect();
    $builder = $db->table('gss_bookings A');
    $builder->select("A.booking_status,A.booking_id,A.dob,A.doa,A.project_id,A.customer_name,A.address,A.email as customer_email,A.mobile1,A.mobile2,A.created_at,B.project_name,C.site_number,C.dimension,A.reference,D.broker_id");
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    $builder->join('gss_brokers D', 'A.reference = D.broker_id');

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

if ($from_date != '') {
        $builder->where('A.created_at >=', $from_date);
    }

if ($to_date != '') {
 $builder->where('A.created_at <=', $to_date);
 }

if ($c_project > 0) {
        $builder->where('A.project_id', $c_project);
    }

if ($reference != '') {
        $builder->where('A.reference', $reference);
    }

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

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

$array = [];

foreach ($result as $val) {
        $created_date = $val->created_at;
        $datetime = new \DateTime($created_date);
        $c_date = $datetime->format('d-m-Y');

$data = [];
        $data['booking_id'] = $val->booking_id;
        $data['customer_name'] = $val->customer_name;

if ($val->dob > '0000-00-00') {
            $datetime = new \DateTime($val->dob);
            $data['dob'] = $datetime->format('d-m-Y');
        } else {
            $data['dob'] = '';
        }

if ($val->doa > '0000-00-00') {
            $datetime = new \DateTime($val->doa);
            $data['doa'] = $datetime->format('d-m-Y');
        } else {
            $data['doa'] = '';
        }

$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
        $siteBuilder = $db->table('gss_new_sites E');
        $siteBuilder->select('E.total_in_sqft');
        $siteBuilder->where('E.delete_status', 'ACTIVE');
        $siteBuilder->where('E.project_id', $val->project_id);
        $siteBuilder->where('E.site_number', $val->site_number);
        $siteQuery = $siteBuilder->get();
        $site_result = $siteQuery->getRow();

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

$data['created_at'] = $c_date;
        $data['booking_status'] = $val->booking_status;

if ($val->reference == 0) {
            $data['executive'] = '';
        } else {
            $refBuilder = $db->table('gss_brokers A');
            $refBuilder->select('A.*, B.*, A.associate_name as reference');
            $refBuilder->join('gss_bookings B', 'B.reference = A.broker_id');
            $refBuilder->where('B.delete_status', 'ACTIVE');
            $refBuilder->where('A.broker_id', $val->reference);
            $refQuery = $refBuilder->get();
            $reference_result = $refQuery->getRow();

$data['executive'] = $reference_result ? $reference_result->reference : '';
        }

$array[] = $data;
    }

return $array;
}

public function get_loansreports($project_status, $project_id)
{
    $db = \Config\Database::connect();

$select = "SELECT B.project_status, A.booking_id, A.project_id, A.customer_name, A.address, A.webportal, A.logistics, 
        A.reference, A.associate, A.email AS customer_email, A.mobile1, A.mobile2, A.booking_status, A.khata_status, 
        C.booking_date1 AS booked_on, B.project_name, C.site_number, C.dimension, C.booking_date1, 
        D.sale_agreement_amount AS l_sale_agreement_amount, D.loan_amount_applied, D.financial_institution, 
        D.processing_fees, D.applied_on, D.sanctioned_date, D.disbursed_date, D.disbursed_cheque_amount, 
        D.remarks, E.registration_date, F.status AS f_status";

$from = " FROM gss_bookings A, gss_new_projects B, gss_booking_details C, gss_loans D, gss_plot_payments E, gss_new_sites F";

$where = " WHERE 
        B.project_id = A.project_id AND 
        C.booking_id = A.booking_id AND 
        D.booking_id = A.booking_id AND 
        E.booking_id = A.booking_id AND 
        F.project_id = C.project_id AND 
        F.site_number = C.site_number AND ";

if ($project_status !== "ALL") {
        $where .= "B.project_status = '$project_status' AND ";
    }

if ($project_id != 0) {
        $where .= "A.project_id = '$project_id' AND ";
    }

$where .= "D.delete_status = 'ACTIVE' AND 
               A.delete_status = 'ACTIVE' AND 
               B.delete_status = 'ACTIVE' AND 
               C.delete_status = 'ACTIVE' 
               GROUP BY D.booking_id 
               ORDER BY B.project_name ASC, ABS(C.site_number) ASC";

$sql = $select . $from . $where;

$query = $db->query($sql);
    $results = $query->getResult();

$dataArray = [];

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

$data['booking_id'] = $val->booking_id;
        $data['project_status'] = $val->project_status;
        $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;
        $data['khata_status'] = $val->khata_status;

// Dimension from gss_new_sites
        $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();

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

// Dates and formatting
        $data['booked_on'] = (new \DateTime($val->booked_on))->format('d-m-Y');
        $data['booking_status'] = $val->booking_status;

// Comma formatting for disbursed_cheque_amount
        $num = $val->disbursed_cheque_amount;
        $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 => $unit) {
                $explrestunits .= ($i == 0 ? (int)$unit : $unit) . ",";
            }
            $data['disbursed_cheque_amount'] = $explrestunits . $lastthree;
        } else {
            $data['disbursed_cheque_amount'] = $num;
        }

$data['financial_institution'] = $val->financial_institution;
        $data['applied_on'] = (new \DateTime($val->applied_on))->format('d-m-Y');
        $data['sanctioned_date'] = (new \DateTime($val->sanctioned_date))->format('d-m-Y');
        $data['disbursed_date'] = (new \DateTime($val->disbursed_date))->format('d-m-Y');
        $data['remarks'] = $val->remarks;

$data['registration_date'] = (new \DateTime($val->registration_date))->format('d-m-Y');

$dataArray[] = $data;
    }

return $dataArray;
}
public function get_reception_reports($from_date, $to_date, $project_id)
{
    $db = \Config\Database::connect();

$select = "SELECT DISTINCT B.project_name, A.client_name, A.booking_id_fk, A.detail_id_fk, A.project_id, A.site_number, A.dispatched_type, A.dispatched_date, A.client_khatha, A.created_at AS reception_at";
    $from   = " FROM gss_reception A, gss_new_projects B";
    $where  = " WHERE A.project_id = B.project_id AND";

if ($from_date != '') {
        $where .= " DATE(A.created_at) >= '$from_date' AND";
    }

if ($to_date != '') {
 $where .= " DATE(A.created_at) <= '$to_date' AND";
 }

if ($project_id > 0) {
        $where .= " A.project_id = '$project_id' AND";
    }

$where .= " A.delete_status = 'ACTIVE' AND";
    $where .= " B.delete_status = 'ACTIVE'";

// Trim the last AND (in case it's there before adding ORDER BY)
    $where = rtrim($where, 'AND');

$order = " ORDER BY B.project_name ASC, ABS(A.site_number) ASC";

$sql = $select . $from . $where . $order;

$query  = $db->query($sql);
    $result = $query->getResult();

$array = [];
    $data  = [];

foreach ($result as $val) {
        $data['booking_id']       = '';
        $data['customer_name']    = $val->client_name;
        $data['address']          = '';
        $data['customer_mobile']  = '';
        $data['customer_email']   = '';
        $data['project_name']     = $val->project_name;
        $data['site_number']      = $val->site_number;
        $data['dimension']        = '';
        $data['booked_on']        = '';
        $data['booking_status']   = '';
        $data['dispatched_type']  = $val->dispatched_type;
        $data['dispatched_date']  = $val->dispatched_date;
        $data['reception_at']     = $val->reception_at;
        $data['client_khatha']    = $val->client_khatha;

if ($val->booking_id_fk > 0 && $val->detail_id_fk > 0) {
            $builder2 = $db->table('gss_bookings A');
            $builder2->select('A.booking_id, A.customer_name, A.address, A.mobile1, A.mobile2, A.email, A.booking_status, B.dimension, B.booking_date1');
            $builder2->join('gss_booking_details B', 'A.booking_id = B.booking_id');
            $builder2->where('A.delete_status', 'ACTIVE');
            $builder2->where('B.delete_status', 'ACTIVE');
            $builder2->where('A.booking_status', 'BOOKED');
            $builder2->where('B.site_number', $val->site_number);
            $builder2->where('B.project_id', $val->project_id);

$result1 = $builder2->get()->getRow();

if ($result1) {
                $data['booking_id']        = $result1->booking_id;
                $data['customer_name']     = $result1->customer_name;
                $data['address']           = $result1->address;
                $data['customer_mobile']   = $result1->mobile1;
                $data['customer_mobile2']  = $result1->mobile2;
                $data['customer_email']    = $result1->email;
                $data['dimension']         = $result1->dimension;
                $data['booked_on']         = $result1->booking_date1;
                $data['booking_status']    = $result1->booking_status;
            }
        }

if (!empty($data)) {
            $array[] = $data;
        }
    }

return $array;
}

public function executive_payment_reports($id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_executive_incentives A');
    $builder->select('*');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.id', $id);

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

$array = [];

foreach ($result as $res) {
        $data = [];

$data['payment_type'] = ($res->payment_type == 'Online Payment') ? 'NEFT' : $res->payment_type;
        $data['cheque_number'] = $res->cheque_number;

// Format cheque amount
        $num = $res->cheque_amount;
        $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 . ",";
            }
            $cheque_amount_total = $explrestunits . $lastthree;
        } else {
            $cheque_amount_total = $num;
        }
        $data['cheque_amount'] = $cheque_amount_total;

$data['cheque_date'] = $res->cheque_date;
        $data['bank_name'] = $res->bank_name;

// Format cash amount
        $num = $res->cash_amount;
        $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 . ",";
            }
            $cash_amount_total = $explrestunits . $lastthree;
        } else {
            $cash_amount_total = $num;
        }
        $data['cash_amount'] = $cash_amount_total;

$data['cash_date'] = $res->cash_date;
        $data['utr_number'] = $res->utr_number;
        $data['online_date'] = $res->online_date;
        $data['neft_cheque'] = $res->neft_cheque;

// Format NEFT cheque amount
        $num = $res->neft_cheque_amt;
        $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 . ",";
            }
            $neft_cheque_amt_total = $explrestunits . $lastthree;
        } else {
            $neft_cheque_amt_total = $num;
        }

$data['neft_cheque_amt'] = $neft_cheque_amt_total;
        $data['neft_cheque_date'] = $res->neft_cheque_date;

$array[] = $data;
    }

return $array;
}

public function formatIndianCurrency($num)
{
    $num = (string)$num;
    $explrestunits = "";

foreach ($expunit as $i => $val) {
            $explrestunits .= ($i == 0) ? (int)$val . "," : $val . ",";
        }

return $explrestunits . $lastthree;
    } else {
        return $num;
    }
}

public function get_export_print_executive_incentive_reports($from_date, $to_date, $executive, $month, $year)
{
$builder = $this->db->table('gss_executive_incentives A');
$builder->select('*');
$builder->where('A.delete_status', 'ACTIVE');

if ($executive != "") {
 $builder->where('A.broker_id', $executive);
}
if ($from_date != "") {
 $builder->where('DATE(A.created_at) >=', $from_date);
}
if ($to_date != "") {
 $builder->where('DATE(A.created_at) <=', $to_date);
}
if ($month != "") {
 $builder->where('MONTH(A.created_at)', $month);
}
if ($year != "") {
 $builder->where('YEAR(A.created_at)', $year);
}

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

$array = array();

foreach ($result as $res) {
        $data['id']             = $res->id;
        $data['created_at']     = date('d-m-Y', strtotime($res->created_at));
        $data['remark']         = $res->exe_remark;

// Format basic target
        $num = $res->basic_target;
        $explrestunits = "";
        if (strlen($num) > 3) {
            $lastthree = substr($num, strlen($num) - 3, strlen($num));
            $restunits = substr($num, 0, strlen($num) - 3);
            $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
            $expunit = str_split($restunits, 2);
            foreach ($expunit as $i => $unit) {
                $explrestunits .= ($i == 0 ? (int)$unit : $unit) . ",";
            }
            $basic_target_total = $explrestunits . $lastthree;
        } else {
            $basic_target_total = $num;
        }
        $data['basic_target'] = $basic_target_total;

$data['achieved'] = $res->achieved;
        $data['cancelled'] = $res->cancelled;
        $data['excess'] = $res->excess;
        $data['shortage'] = ($res->shortage != "" && $res->shortage != "NaN") ? $res->shortage : 0;
        $data['net_achieved'] = ($res->net_achieved != "" && $res->net_achieved != "NaN") ? $res->net_achieved : 0;

// Format net amount
        $num = ($res->net_amt == '') ? $res->amount : $res->net_amt;
        $explrestunits = "";
        if (strlen($num) > 3) {
            $lastthree = substr($num, strlen($num) - 3, strlen($num));
            $restunits = substr($num, 0, strlen($num) - 3);
            $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
            $expunit = str_split($restunits, 2);
            foreach ($expunit as $i => $unit) {
                $explrestunits .= ($i == 0 ? (int)$unit : $unit) . ",";
            }
            $amount_formatted = $explrestunits . $lastthree;
        } else {
            $amount_formatted = $num;
        }
        $data['net_amt'] = $amount_formatted;

// Payment type info
        if ($res->payment_type == 'Online Payment') {
            $data['payment_type'] = 'NEFT';
            $data['number'] = $res->utr_number;
            $data['date'] = $res->online_date;
            $data['cheque_number'] = $res->neft_cheque;
            $data['amount'] = $res->neft_cheque_amt;
            $data['cheque_date'] = $res->neft_cheque_date;
        } elseif ($res->payment_type == 'Cash') {
            $data['payment_type'] = 'CASH';
            $data['amount'] = $res->cash_amount;
            $data['date'] = $res->cash_date;
            $data['cheque_number'] = "";
            $data['cheque_date'] = "";
            $data['bank_name'] = "";
            $data['number'] = "";
        } elseif ($res->payment_type == 'Cheque') {
            $data['payment_type'] = 'CHEQUE';
            $data['cheque_number'] = $res->cheque_number;
            $data['amount'] = $res->cheque_amount;
            $data['cheque_date'] = $res->cheque_date;
            $data['bank_name'] = $res->bank_name;
            $data['number'] = "";
            $data['date'] = "";
        }

// Project and site info
        $project_sites = json_decode($res->projects_sites);
        foreach ($project_sites as $pro_sites) {
            $project_ids_exploded = explode(",", $pro_sites->project_id);
            $site_numbers_exploded = explode(",", $pro_sites->site_number);

foreach ($project_ids_exploded as $key => $vals) {
                $project_id = $vals;
                $site_numbers = $site_numbers_exploded[$key];
$builder = $this->db->table('gss_bookings A');
$builder->select('A.reference,A.booking_id,A.customer_name,A.created_at as a_created_at,A.booking_status, 
    B.project_id,B.site_number,B.dimension,B.booking_date1,
    C.broker_id,C.associate_name,
    E.agreement_date,E.registration_date,
    D.project_id,D.project_name,
    F.incentive_status');

$builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
$builder->join('gss_brokers C', 'C.broker_id = A.reference', 'left');
$builder->join('gss_new_projects D', 'D.project_id = B.project_id');
$builder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
$builder->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number');

$builder->where('A.booking_status !=', 'ENQUIRED');
$builder->where('F.project_id', $project_id);
$builder->where('F.site_number', $site_numbers);
$builder->where('A.booking_status', 'BOOKED');
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->where('D.delete_status', 'ACTIVE');

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

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

foreach ($result_booking as $res_booking) {
                    $months = date("F", mktime(0, 0, 0, (int)$month, 10)); // Better way to get month name
                    $data['month'] = $months;
                    $data['booking_id'] = $res_booking->booking_id;
                    $data['booking_date'] = date('d-m-Y', strtotime($res_booking->booking_date1));
                    $data['booking_status'] = $res_booking->booking_status;
                    $data['project_id'] = $res_booking->project_id;
                    $data['project_name'] = $res_booking->project_name;
                    $data['site_number'] = $res_booking->site_number;
                    $data['customer_name'] = $res_booking->customer_name;
                    $array[] = $data;
                }
            }
        }
    }

return $array;
}
public function conversation_booking_details($bookingId)
{
    return $this->db->table('gss_bookings A')
        ->select('A.*, B.*, C.*')
        ->join('gss_new_projects B', 'B.project_id = A.project_id')
        ->join('gss_booking_details C', 'C.booking_id = A.booking_id')
        ->where('A.delete_status', 'ACTIVE')
        ->where('B.delete_status', 'ACTIVE')
        ->where('C.delete_status', 'ACTIVE')
        ->where('A.booking_id', $bookingId)
        ->get()
        ->getRow(); // use getResult() for multiple rows
}

public function get_conversation_replies($bookingId)
{
    $db = \Config\Database::connect();
    $session = session();
    $loginId = $session->get('admin_id');

$builder = $db->table('gss_status_conversation A');
    $builder->select('A.*, B.user_id, B.username');
    $builder->join('gss_login B', 'B.user_id = A.created_by');
    $builder->where([
        'A.booking_id' => $bookingId,
        'A.delete_status' => 'ACTIVE'
    ]);
    $query = $builder->get();
    $res = $query->getResult();

$array = [];
    $lastKey = array_key_last($res);

foreach ($res as $key => $value) {
        $data = [];

$data['conv_avail'] = ($key === $lastKey) ? 'yes' : 'no';
        $data['conv_id'] = $value->id;
        $data['booking_id'] = $value->booking_id;
        $data['created_by'] = $value->created_by;
        $data['created_at'] = $value->created_at;
        $data['username'] = $value->username;
        $data['source_type'] = $value->source_type;
        $data['user_type_id'] = $value->user_type_id;
        $data['reminder_date'] = $value->reminder_date;
        $data['selected_department'] = $value->selected_department;

// Check department visibility
        $builder2 = $db->table('gss_status_conversation A');
        $builder2->where('A.id', $value->id);
        if ($value->created_by == $loginId && $value->selected_department == "self") {
            $builder2->where('A.selected_department', 'self');
            $builder2->where('A.created_by', $loginId);
        } else {
            $builder2->where('A.selected_department', 'all');
        }

$getRes = $builder2->get()->getRow();
        $data['conversation'] = $getRes ? $value->conversation : "";

// Replies
        $builder3 = $db->table('gss_status_conversation_replies A');
        $builder3->select('A.*, B.user_id, B.username, C.id as conv_id');
        $builder3->join('gss_login B', 'B.user_id = A.created_by');
        $builder3->join('gss_status_conversation C', 'C.id = A.status_conversation_id');
        $builder3->where([
            'A.status_conversation_id' => $value->id,
            'A.booking_id' => $value->booking_id,
            'A.delete_status' => 'ACTIVE'
        ]);

$replyQuery = $builder3->get();
        $replyRes = $replyQuery->getResult();

if (!empty($replyRes)) {
            $lastReplyKey = array_key_last($replyRes);
            foreach ($replyRes as $k => $v) {
                $data['reply_avail'] = ($k === $lastReplyKey) ? 'yes' : 'no';
            }
            $data['reply'] = $replyRes;
        } else {
            $data['reply'] = "";
        }

$array[] = $data;
    }

return $array;
}

public function get_logistic_incentive_reports($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_logistic_incentives A');
    $builder->select('*');
    $builder->where('A.delete_status', 'ACTIVE');

if (!empty($executive)) {
        $builder->where('A.broker_id', $executive);
    }

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

$array = [];

foreach ($result as $res) {
        $data = [];
        $data['remark'] = $res->log_remark;
        $project_sites = json_decode($res->projects_sites);

foreach ($project_sites as $pro_sites) {
            $project_ids_exploded = explode(",", $pro_sites->project_id);
            $site_numbers_exploded = explode(",", $pro_sites->site_number);

foreach ($project_ids_exploded as $key => $vals) {
                $project_id = $vals;
                $site_numbers = $site_numbers_exploded[$key];

$subBuilder = $db->table('gss_bookings A');
                $subBuilder->select('
                    A.logistics, A.reference, A.booking_id, A.customer_name,
                    A.created_at as a_created_at, A.booking_status,
                    B.shared_between_logistic, B.shared_logistic_amount,
                    B.booking_date1, C.incentive_target_rs,
                    B.project_id, B.site_number, B.dimension,
                    C.broker_id, C.associate_name,
                    E.agreement_date, E.registration_date,
                    D.project_id, D.project_name,
                    F.log_incentive_status
                ');
                $subBuilder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
                $subBuilder->join('gss_brokers C', 'C.broker_id = A.logistics', 'left');
                $subBuilder->join('gss_new_projects D', 'D.project_id = B.project_id');
                $subBuilder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
                $subBuilder->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number');

$subBuilder->where('A.booking_status !=', 'ENQUIRED');
                $subBuilder->where('F.project_id', $project_id);
                $subBuilder->where('F.site_number', $site_numbers);

if (!empty($from_date)) {
                    $subBuilder->where('DATE(B.booking_date1) >=', $from_date);
                }

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

if (!empty($executive)) {
                    $subBuilder->where('A.logistics', $executive);
                }

$subBuilder->where('A.booking_status', 'BOOKED');
                $subBuilder->where('A.delete_status', 'ACTIVE');
                $subBuilder->where('B.delete_status', 'ACTIVE');
                $subBuilder->where('D.delete_status', 'ACTIVE');

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

$query = $subBuilder->get();
                $subResults = $query->getResult();

foreach ($subResults as $res) {
                    // Fetch logistic name
                    $data['logistic_name'] = '';
                    if (!empty($res->logistics)) {
                        $logisticRow = $db->table('gss_brokers')->where('broker_id', $res->logistics)->get()->getRow();
                        $data['logistic_name'] = $logisticRow ? $logisticRow->associate_name : '';
                    }

// Fetch executive name
                    $data['executive_name'] = '';
                    if (!empty($res->reference)) {
                        $executiveRow = $db->table('gss_brokers')->where('broker_id', $res->reference)->get()->getRow();
                        $data['executive_name'] = $executiveRow ? $executiveRow->associate_name : '';
                    }

$data['booking_id'] = $res->booking_id;
                    $data['booking_date'] = date('d-m-Y', strtotime($res->booking_date1));
                    $data['booking_status'] = $res->booking_status;
                    $data['project_id'] = $res->project_id;
                    $data['project_name'] = $res->project_name;
                    $data['site_number'] = $res->site_number;
                    $data['customer_name'] = $res->customer_name;
                    $data['dimension'] = $res->dimension;
                    $data['associate_name'] = $res->associate_name;
                    $data['log_incentive_status'] = $res->log_incentive_status;

// Shared logistic name
                    if ($res->shared_between_logistic) {
                        $shared = $db->table('gss_brokers')->where('broker_id', $res->shared_between_logistic)->get()->getRow();
                        $data['shared_between_logistic'] = $shared ? $shared->associate_name : '';
                    } else {
                        $data['shared_between_logistic'] = '';
                    }

// Amount formatting
                    $num = $res->shared_logistic_amount ?: $res->incentive_target_rs;
                    $shared_logistic_amount_total = $this->formatIndianCurrency($num);
                    $data['shared_logistic_amount'] = $shared_logistic_amount_total;

$array[] = $data;
                }
            }
        }
    }

return $array;
}

public function associate_payment_reports($id)
{
    $builder = $this->db->table('gss_management A');
    $builder->select('A.*, B.*');
    $builder->join('gss_commission_payments B', 'A.management_id = B.management_id');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $id);
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

foreach ($result as $val) {
        $data = [];
        $data['management_id'] = $val->management_id;
        $data['payment_type'] = $val->payment_type;
        $data['tds'] = $val->tds;
        $data['amount_wc'] = $val->amount;
        $data['amount'] = $this->formatIndianCurrency1($val->amount);

$data['with_tax_wc'] = $val->with_tax;
        $data['with_tax'] = $this->formatIndianCurrency1($val->with_tax);

switch ($val->payment_type) {
            case 'Cheque':
                $data['num'] = $val->cheque_no;
                $data['date'] = $val->cheque_date;
                $data['bank_name'] = $val->bank_name;
                break;
            case 'DD':
                $data['num'] = $val->dd_no;
                $data['date'] = $val->dd_date;
                $data['bank_name'] = $val->dd_bank;
                break;
            case 'Online Payment':
                $data['num'] = $val->utr_no;
                $data['date'] = $val->online_date;
                $data['bank_name'] = '';
                break;
            case 'Cash':
                $data['num'] = '';
                $data['date'] = '';
                $data['bank_name'] = '';
                break;
        }

// Fetch sub commission payment
        $sub = $this->db->table('gss_sub_commission_payments C')
            ->select('C.payment_type as subpaymenttype, C.cheque_no as subchequeno, C.cheque_date as subcheque_date, C.bank_name as subbankname, 
                      C.utr_no as subvtrno, C.online_date as subonlinedate, C.dd_no as subddno, C.dd_date as subdddate, C.dd_bank as subddbank, 
                      C.amount as subamount, C.tds as subtds, C.with_tax as subnet')
            ->where('C.delete_status', 'ACTIVE')
            ->where('C.management_id', $val->management_id)
            ->get()
            ->getRow();

if ($sub) {
            $data['subpaymenttype'] = $sub->subpaymenttype;
            $data['subtds'] = $sub->subtds;
            $data['subamount_wc'] = $sub->subamount;
            $data['subamount'] = $this->formatIndianCurrency1($sub->subamount);
            $data['subwith_tax_wc'] = $sub->subnet;
            $data['subwith_tax'] = $this->formatIndianCurrency1($sub->subnet);

switch ($sub->subpaymenttype) {
                case 'Cheque':
                    $data['subnum'] = $sub->subchequeno;
                    $data['subdate'] = $sub->subcheque_date;
                    $data['subbank_name'] = $sub->subbankname;
                    break;
                case 'DD':
                    $data['subnum'] = $sub->subddno;
                    $data['subdate'] = $sub->subdddate;
                    $data['subbank_name'] = $sub->subddbank;
                    break;
                case 'Online Payment':
                    $data['subnum'] = $sub->subvtrno;
                    $data['subdate'] = $sub->subonlinedate;
                    $data['subbank_name'] = '';
                    break;
                case 'Cash':
                    $data['subnum'] = '';
                    $data['subdate'] = '';
                    $data['subbank_name'] = '';
                    break;
            }
        }

$array[] = $data;
    }

return $array;
}

private function formatIndianCurrency1($num)
{
    $explrestunits = "";
    $num = (string)$num;

foreach ($expunit as $i => $unit) {
            $explrestunits .= ($i == 0) ? (int)$unit . "," : $unit . ",";
        }

return $explrestunits . $lastthree;
    } else {
        return $num;
    }
}
public function associate_reports($from_date, $to_date)
{
    $format_amount = function($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 => $unit) {
                $explrestunits .= ($i == 0) ? (int)$unit."," : $unit.",";
            }
            return $explrestunits.$lastthree;
        } else {
            return $num;
        }
    };

$sql = "
        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
        FROM gss_bookings A
        JOIN gss_new_projects B ON B.project_id = A.project_id
        JOIN gss_booking_details C ON C.booking_id = A.booking_id
        JOIN gss_management D ON D.booking_id = A.booking_id
        JOIN gss_commission_payments E ON D.management_id = E.management_id
        WHERE A.delete_status = 'ACTIVE'
            AND B.delete_status = 'ACTIVE'
            AND C.delete_status = 'ACTIVE'
            AND D.delete_status = 'ACTIVE'
            AND E.delete_status = 'ACTIVE'
    ";

if (!empty($from_date)) {
        $sql .= " AND E.payment_date >= '$from_date'";
    }

if (!empty($to_date)) {
 $sql .= " AND E.payment_date <= '$to_date'";
 }

$sql .= " ORDER BY B.project_name ASC, ABS(C.site_number) ASC";

$result = $this->db->query($sql)->getResultArray();

$array = [];

foreach($result 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'];

$site = $this->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 ? $site->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'] = $format_amount($val['amount']);
$data['tds'] = $val['tds'];

$data['net_amount_wc'] = $val['with_tax'];
$data['net_amount'] = $format_amount($val['with_tax']);
$data['bank'] = $val['bank_name'];

$sub = $this->db->table('gss_sub_commission_payments')
    ->select('*')
    ->where([
        'delete_status' => 'ACTIVE',
        'management_id' => $val['management_id']
    ])
    ->get()
    ->getRowArray(); // Use getRow() if you prefer object

if ($sub) {
            $data['subgrass_wc'] = $sub["amount"];
            $data['subgrass'] = $format_amount($sub["amount"]);
            $data['subtds'] = $sub["tds"];
            $data['subpayment_type'] = $sub["payment_type"];
            $data['subwith_tax_wc'] = $sub["with_tax"];
            $data['subwith_tax'] = $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'] = "";
        }

// Associate name
if ($val['associate'] != 0) {
    $associate = $this->db->table('gss_brokers A')
        ->select('A.associate_name as associate')
        ->join('gss_bookings B', 'B.associate = A.broker_id')
        ->where([
            'B.delete_status' => 'ACTIVE',
            'A.broker_id' => $val['associate']
        ])
        ->get()
        ->getRow(); // use getRowArray() if you're working with arrays

$data['associate'] = $associate ? $associate->associate : "";
} else {
    $data['associate'] = "";
}

// Subassociate name
if ($val['subassociate'] != 0) {
    $subassociate = $this->db->table('gss_brokers A')
        ->select('A.associate_name as subassociate')
        ->join('gss_bookings B', 'B.subassociate = A.broker_id')
        ->where([
            'B.delete_status' => 'ACTIVE',
            'A.broker_id' => $val['subassociate']
        ])
        ->get()
        ->getRow(); // use getRowArray() if working with arrays

$data['subassociate'] = $subassociate ? $subassociate->subassociate : "";
} else {
    $data['subassociate'] = "";
}

$array[] = $data;
    }

return $array;
}
public function enquiry_reports($from_date, $to_date, $type, $reference)
{
    $builder = $this->db->table('gss_enquiries A');

$builder->select([
        'A.enquiry_id',
        'A.customer_name',
        'A.email as customer_email',
        'A.mobile as customer_mobile',
        'A.mobile2 as customer_mobile1',
        'A.address as customer_address',
        'A.web_portal_address as portal',
        'A.reference',
        'A.created_at as allocated_date',
        'A.status'
    ]);

// Apply filters
    if (!empty($from_date)) {
        $builder->where('A.created_at >=', $from_date);
    }

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

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

$builder->where('A.web_portal_address !=', '');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->orderBy('A.created_at', 'ASC');

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

$array = [];

foreach ($results as $val) {
        $data['enquiry_id']       = $val->enquiry_id;
        $data['customer_name']    = $val->customer_name;
        $data['customer_email']   = $val->customer_email;
        $data['customer_mobile']  = $val->customer_mobile;
        $data['mobile2']          = $val->customer_mobile1;
        $data['customer_address'] = $val->customer_address;
        $data['allocated_on_date'] = $val->allocated_date;

$datetime = new \DateTime($val->allocated_date);
        $data['allocated_date'] = $datetime->format('d-m-Y');

$data['web_data'] = $val->portal;
        $data['reference'] = $val->reference;
        $data['status'] = $val->status;

$array[] = $data;
    }

return $array;
}
public function enquiry_databasereports($from_date, $to_date, $type, $reference)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_enquiries A');
    $builder->select([
        'A.enquiry_id',
        'A.customer_name',
        'A.email AS customer_email',
        'A.mobile AS customer_mobile',
        'A.mobile2 AS customer_mobile1',
        'A.address AS customer_address',
        'A.database_name AS portal',
        'A.reference',
        'A.created_at AS allocated_date',
        'A.status'
    ]);

// WHERE clauses
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.database_name !=', '');

if (!empty($from_date)) {
        $builder->where('DATE(A.created_at) >=', $from_date);
    }

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

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

$builder->orderBy('A.created_at', 'ASC');

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

$array = [];

foreach ($results as $val) {
        $allocatedDate = new \DateTime($val->allocated_date);
        $array[] = [
            'enquiry_id'        => $val->enquiry_id,
            'customer_name'     => $val->customer_name,
            'customer_email'    => $val->customer_email,
            'customer_mobile'   => $val->customer_mobile,
            'mobile2'           => $val->customer_mobile1,
            'customer_address'  => $val->customer_address,
            'allocated_on_date' => $val->allocated_date,
            'allocated_date'    => $allocatedDate->format('d-m-Y'),
            'web_data'          => $val->portal,
            'reference'         => $val->reference,
            'status'            => $val->status,
        ];
    }

return $array;
}
public function test($project_id, $site_number)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_booking_details A');
    $builder->select('A.*, A.detail_id as d_id, B.*, B.booking_id as b_id, C.*, C.booking_id as c_bid, A.confirming_party1 as confirming_party, D.status as d_status');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_plot_payments C', 'C.booking_id = A.booking_id', 'left');
    $builder->join('gss_new_sites D', 'D.project_id = A.project_id AND D.site_number = A.site_number');

$builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('B.booking_status', 'BOOKED');
    $builder->where('A.site_number', $site_number);
    $builder->where('A.project_id', $project_id);

$query = $builder->get();
    return $query->getRow();
}
public function payment_ind($booking_id)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_bookings A');
    $builder->select('A.*, C.*, D.*');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    $builder->join('gss_booking_installments D', 'D.booking_id = A.booking_id');
    $builder->where([
        'A.delete_status' => 'ACTIVE',
        'C.delete_status' => 'ACTIVE',
        'D.delete_status' => 'ACTIVE',
        'A.booking_id'    => $booking_id
    ]);

$result = $builder->get()->getRow();

if (!$result) {
        return [];
    }

$data['booking_id'] = $result->booking_id;
    $data['tsv'] = $this->formatAmount($result->tsv);
    $data['sales_agreement_due_date'] = $result->sales_agreement_due_date;
    $data['sales_agreement_due_amount'] = $this->formatAmount($result->sales_agreement_due_amount);
    $data['installment_due_date'] = $result->installment_due_date;
    $data['installment_due_amount'] = $this->formatAmount($result->installment_due_amount);
    $data['registration_due_date'] = $result->registration_due_date;
    $data['registration_due_amount'] = $this->formatAmount($result->registration_due_amount);

return $data;
}
private function formatAmount($num)
{
    if (!$num || !is_numeric($num)) return '0/-';

$num = (string) $num;
    $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 . '/-';
    }
}
public function agreement_payment_details($booking_id)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_plot_payments A');
    $builder->select('
        A.*, B.*, C.*, D.*, E.*,
        C.confirming_party1 AS booking1_confirming_type,
        C.confirming_party2 AS booking2_confirming_type,
        C.paytm_ref_no AS paytm_num,
        C.upi_ref_no AS upi_num,
        C.paytm_ref_no2 AS paytm_num2,
        C.upi_ref_no2 AS upi_num2
    ');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.detail_id = A.detail_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id');
    $builder->where([
        'A.delete_status'           => 'ACTIVE',
        'A.agreement_delete_status' => 'ACTIVE',
        'B.delete_status'           => 'ACTIVE',
        'C.delete_status'           => 'ACTIVE',
        'D.delete_status'           => 'ACTIVE',
        'E.delete_status'           => 'ACTIVE',
        'A.booking_id'              => $booking_id
    ]);
    $builder->orderBy('A.payment_id', 'ASC');

$result = $builder->get();

return $result->getRow();  // returns single row object
}
public function installment_payment_details($booking_id)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_plot_payments A');
    $builder->select('
        A.*, B.*, C.*, D.*, E.*,
        C.paytm_ref_no AS paytm_num,
        C.paytm_ref_no2 AS paytm_num2,
        C.upi_ref_no AS upi_num,
        C.upi_ref_no2 AS upi_num2
    ');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.detail_id = A.detail_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id');
    $builder->where([
        'A.delete_status'          => 'ACTIVE',
        'A.install_delete_status'  => 'ACTIVE',
        'B.delete_status'          => 'ACTIVE',
        'C.delete_status'          => 'ACTIVE',
        'D.delete_status'          => 'ACTIVE',
        'E.delete_status'          => 'ACTIVE',
        'A.booking_id'             => $booking_id
    ]);
    $builder->orderBy('A.payment_id', 'ASC');

$result = $builder->get();

return $result->getResult(); // returns an array of objects
}
public function registration_payment_details($booking_id)
{
    $db = \Config\Database::connect();

$builder->where([
        'A.delete_status'               => 'ACTIVE',
        'A.registration_delete_status' => 'ACTIVE',
        'B.delete_status'              => 'ACTIVE',
        'C.delete_status'              => 'ACTIVE',
        'D.delete_status'              => 'ACTIVE',
        'E.delete_status'              => 'ACTIVE',
        'A.booking_id'                 => $booking_id
    ]);

$builder->orderBy('A.payment_id', 'ASC');

$result = $builder->get();

return $result->getRow(); // single row object
}
public function payment_withoutinstall($booking_id)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_plot_payments A');
    $builder->select('
        A.*, B.*, C.*, D.*, 
        E.*,
        C.confirming_party1 AS booking1_confirming_type,
        C.confirming_party2 AS booking2_confirming_type,
        C.paytm_ref_no AS paytm_num,
        C.upi_ref_no AS upi_num,
        C.paytm_ref_no2 AS paytm_num2,
        C.upi_ref_no2 AS upi_num2,
        C.upi_online_date2 AS upi_date,
        C.paytm_online_date2 AS paytm_date
    ');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.detail_id = A.detail_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id', 'left');

$builder->where([
        'A.delete_status' => 'ACTIVE',
        'B.delete_status' => 'ACTIVE',
        'C.delete_status' => 'ACTIVE',
        'D.delete_status' => 'ACTIVE',
        'A.booking_id'    => $booking_id
    ]);

$builder->orderBy('A.payment_id', 'DESC');

$result = $builder->get();

return $result->getRow(); // Return single row
}
public function site_cancellation_list($project_id, $site_number)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_cancellations A');

$builder->select('
        E.total_in_sqft,
        A.*,
        B.*,
        C.*,
        D.*,
        F.*,
        A.cancellation_id as cancel_id
    ');

$builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.booking_id = B.booking_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_new_sites E', 'E.project_id = D.project_id AND C.site_number = E.site_number');
    $builder->join('gss_cancellation_refunds F', 'F.booking_id = A.booking_id AND F.cancellation_id = A.cancellation_id', 'left');

$builder->where('B.booking_status !=', 'BOOKED');
    $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('B.project_id', $project_id);
    $builder->where('E.site_number', $site_number);
    
    $builder->orderBy('A.cancellation_id', 'DESC');

$query = $builder->get();
    return $query->getResult();
}
public function payment_details($booking_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_plot_payments A');

$builder->select('
        A.*, 
        B.*, 
        C.*, 
        D.*, 
        E.*, 
        E.paytm_ref_no as paytm_num, 
        E.paytm_online_date1 as paytm_date, 
        E.upi_ref_no as upi_num, 
        E.upi_online_date1 as upi_date, 
        E.paytm_ref_no2 as paytm_num2, 
        E.paytm_online_date2 as paytm_date2, 
        E.upi_ref_no2 as upi_num2, 
        E.upi_online_date2 as upi_date2
    ');

$builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.detail_id = A.detail_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id');

$builder->where([
        'A.delete_status' => 'ACTIVE',
        'A.install_delete_status' => 'ACTIVE',
        'B.delete_status' => 'ACTIVE',
        'C.delete_status' => 'ACTIVE',
        'D.delete_status' => 'ACTIVE',
        'E.delete_status' => 'ACTIVE',
        'A.booking_id' => $booking_id
    ]);

$builder->orderBy('A.payment_id', 'ASC');

$query = $builder->get();
    return $query->getResult();
}

public function payment_detailsgroup($booking_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_plot_payments A');

$builder->select('A.*, B.*, C.*, D.*');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_booking_details C', 'C.detail_id = A.detail_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id');

$builder->orderBy('A.payment_id', 'DESC');

$query = $builder->get();
    return $query->getRow();
}
public function payment_withoutagree($booking_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_bookings A');

$builder->select('A.*, C.*, D.*');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    $builder->join('gss_new_projects D', 'D.project_id = A.project_id');

$builder->where([
        'A.delete_status' => 'ACTIVE',
        'C.delete_status' => 'ACTIVE',
        'D.delete_status' => 'ACTIVE',
        'A.booking_id' => $booking_id
    ]);

$query = $builder->get();
    return $query->getRow();
}
public function payment_details_ind($booking_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_plot_payments A');

$builder->select('A.*, E.*');
    $builder->join('gss_plot_payment_types E', 'E.payment_id = A.payment_id');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);
    $builder->orderBy('A.payment_id', 'DESC');

$query = $builder->get();
    return $query->getRow();
}
public function get_cancellation_types_result($booking_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_cancellations');

$builder->select('*');
    $builder->where('booking_id', $booking_id);

$query = $builder->get();
    return $query->getResult();
}
public function commission($booking_id)
{
    $db = \Config\Database::connect();

return $db->table('gss_management A')
              ->select('A.*')
              ->where([
                  'A.delete_status' => 'ACTIVE',
                  'A.booking_id'    => $booking_id
              ])
              ->get()
              ->getRow();
}
public function single_user_account_management_list($booking_id)
{
    $db = \Config\Database::connect();

return $db->table('gss_bookings B')
              ->select('F.total_in_sqft, B.*, C.*, D.*, E.*, B.mobile1 as customer_contact')
              ->join('gss_booking_details C', 'C.booking_id = B.booking_id')
              ->join('gss_new_projects D', 'D.project_id = B.project_id')
              ->join('gss_brokers E', 'E.broker_id = B.associate')
              ->join('gss_new_sites F', 'F.project_id = D.project_id AND C.site_number = F.site_number')
              ->where([
                  'B.delete_status' => 'ACTIVE',
                  'C.delete_status' => 'ACTIVE',
                  'D.delete_status' => 'ACTIVE',
                  'B.booking_id'    => $booking_id
              ])
              ->get()
              ->getRow();
}
public function insert1($table, $data)
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table($table); // ✅ Get table reference

if (!is_array($data) || empty($data)) {
        throw new \Exception("Data must be a non-empty array.");
    }

$builder->insert($data); // ✅ Insert data

return $db->insertID();
}

public function get_total_agree_due_amt($project)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_booking_details A');
    $builder->select('A.sales_agreement_due_amount, A.registration_due_amount');
    $builder->join('gss_bookings C', 'C.booking_id = A.booking_id');
    $builder->where('A.project_id', $project);
    $builder->where('C.booking_status', 'BOOKED');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');

$query = $builder->get();
    return $query->getResult();
}

public function get_received_agree_amt($project)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_plot_payments A');
    $builder->select('A.agreement_amount, A.registration_amount, B.booking_amount1, B.booking_amount2, A.installment_amount1');
    $builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
    $builder->join('gss_bookings C', 'C.booking_id = A.booking_id');
    $builder->where('B.project_id', $project);
    $builder->where('C.booking_status', 'BOOKED');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->groupBy('A.booking_id');

$query = $builder->get();
    return $query->getResult();
}

public function table_truncate($table)
{
    $db = \Config\Database::connect();
    $db->table($table)->truncate();
}

public function total_marketing_dimension_report($project)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_new_sites');
    $builder->select('SUM(total_in_sqft) as total_in_sqft');
    $builder->where('delete_status', 'ACTIVE');
    $builder->where('project_id', $project);
    $builder->groupBy('project_id');

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

$array = [];

foreach ($result as $value) {
        $rps = $value->total_in_sqft;
        $data['total_in_sqft'] = number_format($rps, 2, '.', '');
        array_push($array, $data);
    }

return $array;
}

public function total_booked_dimension_report($project)
{
    $db = \Config\Database::connect();

$builder = $db->table('gss_booking_details E');
    $builder->select('SUM(G.total_in_sqft) as booked_sqft');
    $builder->join('gss_bookings F', 'F.booking_id = E.booking_id AND F.project_id = E.project_id');
    $builder->join('gss_new_sites G', 'G.project_id = E.project_id AND G.site_number = E.site_number');
    $builder->where('E.delete_status', 'ACTIVE');
    $builder->where('F.delete_status', 'ACTIVE');
    $builder->where('F.booking_status', 'BOOKED');
    $builder->where('E.project_id', $project);
    $builder->groupBy('E.project_id');

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

$array = [];

foreach ($result as $value) {
        $rps = $value->booked_sqft;
        $data['booked_sqft'] = number_format($rps, 2, '.', '');
        array_push($array, $data);
    }

return $array;
}

public function update1($table, $where, $data)
{
    $db = \Config\Database::connect();  
    $builder = $db->table($table);

if (!is_array($data)) {
        throw new \Exception("Data must be an array, " . gettype($data) . " given.");
    }

$builder->where($where);
    $builder->update($data);

return $db->affectedRows(); // ✅ Return number of affected rows
}

public function get_status_agree_due_reports($project)
    {
         $db = \Config\Database::connect();
        $admin_id = session()->get('admin_id');

$builder = $db->table('gss_bookings A');
$builder->select('
    A.booking_id as a_booking_id,
    A.project_id,
    A.customer_name,
    A.address,
    A.webportal,
    A.source_type,
    A.source_bank_name,
    A.logistics,
    A.reference,
    A.khata_status,
    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,
    C.sales_agreement_due_date,
    C.sales_agreement_due_amount,
    C.registration_due_date,
    C.booking_amount1,
    C.booking_amount2,
    C.tsv,
    C.gss_amount,
    D.booking_id as d_booking_id,
    D.*,
    E.status as e_status
');
$builder->join('gss_new_projects B', 'B.project_id = A.project_id');
$builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
$builder->join('gss_status_conversation D', 'D.booking_id = A.booking_id', 'left');
$builder->join('gss_new_sites E', 'E.project_id = C.project_id AND E.site_number = C.site_number');

$builder->where('A.delete_status', 'ACTIVE');

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

$builder->whereNotIn('A.booking_status', ['CANCELLED', 'REFUNDED', 'ENQUIRED', 'REFUND_PENDING']);
$builder->groupBy('A.booking_id');
$builder->orderBy('B.project_name', 'ASC');
$builder->orderBy('ABS(C.site_number)', 'ASC'); // MySQL-specific ordering

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

$array = [];
$date = date('Y-m-d');

foreach ($result as $res) {
    $data['selected_department'] = $res->selected_department;

$data['khata_status'] = ($res->e_status == '0') ? '' : $res->e_status;
    $data['booking_id'] = $booking_id = $res->a_booking_id;

if ($res->selected_department == 'self') {
        $sql = "
            SELECT A.*, B.*, A.created_at AS latest_date, C.username
            FROM gss_status_conversation A
            JOIN gss_bookings B ON B.booking_id = A.booking_id
            JOIN gss_login C ON C.user_id = A.created_by
            WHERE A.booking_id = ?
              AND A.selected_department = 'self'
              AND A.user_type_id = 4
              AND A.created_by = ?
              AND A.delete_status = 'ACTIVE'
            ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
        ";

$convn_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 4
          AND A.created_by = ?
          AND A.status_conversation_id = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $admin_id, $convn_result->id])->getRow();

if ($convn_reply_result) {
        $data['management'] = strtoupper($convn_reply_result->username) . ' ' .
            date('d-m-Y', strtotime($convn_reply_result->latest_date)) . ' ' .
            $convn_reply_result->reply;
    } else {
        $data['management'] = strtoupper($convn_result->username) . ' ' .
            date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' .
            $convn_result->conversation;
    }
} else {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 4
          AND A.created_by = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
      AND A.selected_department = 'self'
      AND A.user_type_id = 6
      AND A.created_by = ?
      AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 6
          AND A.created_by = ?
          AND A.status_conversation_id = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $admin_id, $convn_result->id])->getRow();

} else {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 6
          AND A.created_by = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
      AND A.selected_department = 'self'
      AND A.user_type_id = 5
      AND A.created_by = ?
      AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND A.status_conversation_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 5
          AND A.created_by = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";
    
    $convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id, $admin_id])->getRow();

if ($convn_reply_result) {
        $data['documentation'] = strtoupper($convn_reply_result->username) . ' ' . date('d-m-Y', strtotime($convn_reply_result->latest_date)) . ' ' . $convn_reply_result->reply;
    } else {
        $data['documentation'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_result->conversation;
    }

} else {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department = 'self'
          AND A.user_type_id = 5
          AND A.created_by = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND A.status_conversation_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 4
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

} else {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 4
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id])->getRow();

$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
      AND A.selected_department != 'self'
      AND A.user_type_id = 6
      AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

// First, conversation for user_type_id = 6 and selected_department != 'self'
$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
      AND A.selected_department != 'self'
      AND A.user_type_id = 6
      AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

if (!empty($convn_result)) {
    // Replies related to that conversation
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 6
          AND A.status_conversation_id = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

if ($convn_reply_result) {
        $data['loans'] = strtoupper($convn_reply_result->username) . ' ' . date('d-m-Y', strtotime($convn_reply_result->latest_date)) . ' ' . $convn_reply_result->reply;
    } else {
        $data['loans'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_result->conversation;
    }
} else {
    // No direct conversation, check for any reply
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 6
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id])->getRow();

// Next, another conversation block for user_type_id = 5
$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
      AND A.selected_department != 'self'
      AND A.user_type_id = 5
      AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 5
          AND A.status_conversation_id = ?
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

} else {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
          AND D.selected_department != 'self'
          AND A.user_type_id = 5
          AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id])->getRow();

$data['customer_name']     = $res->customer_name;
$data['address']           = $res->address;

// Handle customer mobile (concatenating if both exist)
$data['customer_mobile']   = !empty($res->mobile2)
                            ? $res->mobile1 . ', ' . $res->mobile2
                            : $res->mobile1;

$data['customer_email']    = $res->customer_email;
$data['project_name']      = $res->project_name;
$data['site_number']       = $res->site_number;
$data['dimension']         = $res->dimension;

// GSS Amount
$data['gss_amount']        = $res->gss_amount . ' /-';
$data['gss_amount1']       = $res->gss_amount . ' /-';

// Booking Amount
$booking_amount1 = $res->booking_amount1;
$booking_amount2 = $res->booking_amount2;
$data['booking_amount_without_comma'] = $booking_amount1 + $booking_amount2;

// Booked Date
if (!empty($res->booked_on)) {
    $data['booked_on'] = date('d-m-Y', strtotime($res->booked_on));
} else {
    $data['booked_on'] = '';
}

$data['source_type']       = $res->source_type;
$data['source_bank_name']  = $res->source_bank_name;

// Indian number format (lakhs/crores with comma separation)
$num = $res->gss_amount;
$total = $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);
    $formatted = "";

foreach ($expunit as $i => $unit) {
        $formatted .= ($i == 0) ? (int)$unit . "," : $unit . ",";
    }

$total = $formatted . $lastThree;
}

$data['gss_amount_in_format'] = $total . " /-";

// helper('format'); // Load helper (assuming it's named format_helper.php)

$data['gss_amount'] = $this->formatIndianCurrency($res->gss_amount) . ' /-';

$booking_amount1 = $res->booking_amount1;
$booking_amount2 = $res->booking_amount2;

// TSV
$data['tsv']  = $this->formatIndianCurrency($res->tsv) . ' /-';
$data['tsv2'] = $res->tsv . ' /-'; // Raw format

// Booking amount formatted
$booking_amount = $booking_amount1 + $booking_amount2;
$data['booking_amount'] = $this->formatIndianCurrency($booking_amount) . ' /-';

// Web Portal
if ($res->webportal == 0) {
    $data['web_portal'] = '';
} else {
    $web_result = $db->table('gss_webportals A')
        ->select('A.*, B.*, A.webportal as portal')
        ->join('gss_bookings B', 'B.webportal = A.portal_id')
        ->where('B.delete_status', 'ACTIVE')
        ->where('A.portal_id', $res->webportal)
        ->get()
        ->getRow();

$data['web_portal'] = $web_result ? $web_result->portal : '';
}

// Logistics
if ($res->logistics == 0) {
    $data['logistics'] = '';
} else {
    $logistic_result = $db->table('gss_brokers A')
        ->select('A.*, B.*, A.associate_name as logistic')
        ->join('gss_bookings B', 'B.logistics = A.broker_id')
        ->where('B.delete_status', 'ACTIVE')
        ->where('A.broker_id', $res->logistics)
        ->get()
        ->getRow();

$data['logistics'] = $logistic_result ? $logistic_result->associate_name : '';
}

// Reference
if ($res->reference == 0) {
    $data['reference'] = "";
} else {
    $builder = $db->table('gss_brokers A');
    $builder->select('A.*, B.*, A.associate_name as reference');
    $builder->join('gss_bookings B', 'B.reference = A.broker_id');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.broker_id', $res->reference);
    $reference_result = $builder->get()->getRow();
    
    $data['reference'] = $reference_result ? $reference_result->reference : "";
}

// Associate
if ($res->associate == 0) {
    $data['associate'] = "";
} else {
    $builder = $db->table('gss_brokers A');
    $builder->select('A.*, B.*, A.associate_name as associate');
    $builder->join('gss_bookings B', 'B.associate = A.broker_id');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.broker_id', $res->associate);
    $associate_result = $builder->get()->getRow();

$data['associate'] = $associate_result ? $associate_result->associate : "";
}

// Subassociate
if ($res->subassociate == 0) {
    $data['subassociate'] = "";
} else {
    $builder = $db->table('gss_brokers A');
    $builder->select('A.*, B.*, A.associate_name as subassociate');
    $builder->join('gss_bookings B', 'B.subassociate = A.broker_id');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.broker_id', $res->subassociate);
    $subassociate_result = $builder->get()->getRow();

$data['subassociate'] = $subassociate_result ? $subassociate_result->subassociate : "";
}

$builder = $db->table('gss_plot_payments A');
$builder->select('A.*');
$builder->where('A.booking_id', $booking_id);
$builder->where('A.agreement_delete_status', 'ACTIVE');
$builder->where('A.delete_status', 'ACTIVE');
$payment_result = $builder->get()->getRow();

if (!empty($payment_result)) {
    if ($payment_result->agreement_amount) {
        $data['agreement_amount_wc'] = $payment_result->agreement_amount . " /-";

$num = $payment_result->agreement_amount;
        $data['agreement_amount'] = $this->formatIndianCurrency($num) . " /-";

} else {
        $data['agreement_due_amount_wc'] = $res->sales_agreement_due_amount . " /-";

$num = $res->sales_agreement_due_amount;
        $data['agreement_due_amount'] = $this->formatIndianCurrency($num) . " /-";
    }
}

$builder = $db->table('gss_plot_payments');
$builder->select('*');
$builder->where([
    'booking_id' => $booking_id,
    'agreement_delete_status' => 'ACTIVE',
    'delete_status' => 'ACTIVE',
]);
$payment_result = $builder->get()->getRow();

$dueDate = date("d-m-Y", strtotime($res->sales_agreement_due_date));

if (!empty($payment_result)) {
    $agDate = $payment_result->agreement_date;

$data['sales_agreement_due_date'] = "Received Date : $agDate Due Date : $dueDate";

// Agreement amount formatting
    $agreementAmount = $payment_result->agreement_amount;
    $data['agreement_amount_wc'] = $agreementAmount . " /-";
    $formattedAgreementAmount = $this->formatIndianCurrency($agreementAmount) . " /-";

// Due amount formatting
    $dueAmount = $res->sales_agreement_due_amount;
    $data['agreement_due_amount_wc'] = $dueAmount . " /-";
    $formattedDueAmount = $this->formatIndianCurrency($dueAmount) . " /-";

$data['sales_agreement_due_amount'] = "Received Amount : $formattedAgreementAmount Due Amount : $formattedDueAmount ";
 $data['agreement_amount'] = $formattedAgreementAmount;
 $data['agreement_due_amount'] = $formattedDueAmount;

} else {
 $data['sales_agreement_due_date'] = "Due Date : $dueDate";

$dueAmount = $res->sales_agreement_due_amount;
    $data['agreement_due_amount_wc'] = $dueAmount . " /-";
    $formattedDueAmount = $this->formatIndianCurrency($dueAmount) . " /-";

$data['sales_agreement_due_amount'] = "Due Amount : $formattedDueAmount";
 $data['agreement_due_amount'] = $formattedDueAmount;
 $data['agreement_amount'] = '0';
 $data['agreement_amount_wc'] = '0';
}

$builder = $db->table('gss_plot_payments');
$builder->select('A.*');
$builder->from('gss_plot_payments A');
$builder->where([
    'A.booking_id' => $booking_id,
    'A.registration_delete_status' => 'ACTIVE',
    'A.delete_status' => 'ACTIVE',
]);

$payment_result = $builder->get()->getRow();

if (!empty($payment_result)) {
    if ($payment_result->registration_amount > 0) {
        $data['registration_due_date'] = 'Done';
    } else {
        $originalDate = $res->registration_due_date;
        $data['registration_due_date'] = date("d-m-Y", strtotime($originalDate));
    }
} else {
    $originalDate = $res->registration_due_date;
    $data['registration_due_date'] = date("d-m-Y", strtotime($originalDate));
}

array_push($array, $data);

return $array;
}
}}

public function get_status_reg_due_reports($project)
    {
        $builder = $this->db->table('gss_bookings A');
$builder->select('
    A.booking_id as a_booking_id,
    A.project_id,
    A.customer_name,
    A.khata_status,
    A.address,
    A.webportal,
    A.source_type,
    A.source_bank_name,
    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.detail_id,
    C.site_number,
    C.dimension,
    C.booking_date1,
    C.sales_agreement_due_date,
    C.sales_agreement_due_amount,
    C.registration_due_date,
    C.booking_amount1,
    C.booking_amount2,
    C.tsv,
    C.gss_amount,
    D.booking_id as d_booking_id,
    D.*,
    E.status as e_status
');
$builder->join('gss_new_projects B', 'B.project_id = A.project_id');
$builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
$builder->join('gss_status_conversation D', 'D.booking_id = A.booking_id', 'left');
$builder->join('gss_new_sites E', 'E.project_id = C.project_id AND E.site_number = C.site_number');
$builder->where('A.delete_status', 'ACTIVE');
$builder->whereNotIn('A.booking_status', ['CANCELLED', 'REFUNDED', 'ENQUIRED', 'REFUND_PENDING']);

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

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

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

$date = date('Y-m-d');
$dataList = [];

foreach ($result as $val) {
    $booking_id = $val->a_booking_id;
    $data = [
        'selected_department' => $val->selected_department,
        'khata_status' => $val->e_status,
        'booking_id' => $booking_id,
    ];

if ($val->selected_department == 'self') {
        $conversationBuilder = $db->table('gss_status_conversation A');
        $conversationBuilder->select('A.*, B.*, A.created_at as latest_date, C.username');
        $conversationBuilder->join('gss_bookings B', 'B.booking_id = A.booking_id');
        $conversationBuilder->join('gss_login C', 'C.user_id = A.created_by');
        $conversationBuilder->where([
            'A.booking_id' => $booking_id,
            'A.selected_department' => 'self',
            'A.user_type_id' => 4,
            'A.created_by' => $admin_id,
            'A.delete_status' => 'ACTIVE',
        ]);
        $conversationBuilder->orderBy('A.id', 'DESC');
        $conversationBuilder->orderBy('STR_TO_DATE(A.created_at, "%d-%m-%Y %h:%i:%s")', 'DESC');

$convnResult = $conversationBuilder->get()->getRow();

if ($convnResult) {
            $replyBuilder = $db->table('gss_status_conversation_replies A');
            $replyBuilder->select('A.*, B.*, A.created_at as latest_date, C.username');
            $replyBuilder->join('gss_bookings B', 'B.booking_id = A.booking_id');
            $replyBuilder->join('gss_login C', 'C.user_id = A.created_by');
            $replyBuilder->join('gss_status_conversation D', 'A.status_conversation_id = D.id');
            $replyBuilder->where([
                'A.booking_id' => $booking_id,
                'D.selected_department' => 'self',
                'A.user_type_id' => 4,
                'A.created_by' => $admin_id,
                'A.status_conversation_id' => $convnResult->id,
                'A.delete_status' => 'ACTIVE',
            ]);
            $replyBuilder->orderBy('A.id', 'DESC');
            $replyBuilder->orderBy('STR_TO_DATE(A.created_at, "%d-%m-%Y %h:%i:%s")', 'DESC');

$convnReply = $replyBuilder->get()->getRow();

if ($convnReply) {
                $data['management'] = strtoupper($convnResult->username) . ' ' . date('d-m-Y', strtotime($convnResult->latest_date)) . ' ' . $convnReply->reply;
            } else {
                $data['management'] = strtoupper($convnResult->username) . ' ' . date('d-m-Y', strtotime($convnResult->latest_date)) . ' ' . $convnResult->conversation;
            }
        } else {
            $data['management'] = '';
        }

$mgmtBuilder = $db->table('gss_status_conversation A');
        $mgmtBuilder->select('A.*, B.*, A.created_at as latest_date, C.username');
        $mgmtBuilder->join('gss_bookings B', 'B.booking_id = A.booking_id');
        $mgmtBuilder->join('gss_login C', 'C.user_id = A.created_by');
        $mgmtBuilder->where([
            'A.booking_id' => $booking_id,
            'A.selected_department' => 'self',
            'A.user_type_id' => 6,
            'A.created_by' => $admin_id,
            'A.delete_status' => 'ACTIVE',
        ]);
        $mgmtBuilder->orderBy('A.id', 'DESC');
        $mgmtBuilder->orderBy('STR_TO_DATE(A.created_at, "%d-%m-%Y %h:%i:%s")', 'DESC');

$convnResult = $mgmtBuilder->get()->getRow();
    }
    
    if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at as latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON D.id = A.status_conversation_id
        WHERE A.booking_id = ?
        AND A.status_conversation_id = ?
        AND D.selected_department = 'self'
        AND A.user_type_id = 6
        AND A.created_by = ?
        AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id, $admin_id])->getRow();

if ($convn_reply_result) {
        $data['loans'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_reply_result->reply;
    } else {
        $data['loans'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_result->conversation;
    }
} else {
    $data['loans'] = '';
}

$sql = "
    SELECT A.*, B.*, A.created_at as latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
    AND A.selected_department = 'self'
    AND A.user_type_id = 5
    AND A.created_by = ?
    AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id, $admin_id])->getRow();

// If conversation result is available
if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
        AND D.selected_department = 'self'
        AND C.user_type_id = 5
        AND A.status_conversation_id = ?
        AND A.created_by = ?
        AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id, $admin_id])->getRow();

if ($convn_reply_result) {
        $data['documentation'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_reply_result->reply;
    } else {
        $data['documentation'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_result->conversation;
    }
} else {
    $data['documentation'] = '';
}

// ELSE BLOCK: selected_department != 'self'
$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
    AND A.selected_department != 'self'
    AND C.user_type_id = 4
    AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
        AND D.selected_department != 'self'
        AND C.user_type_id = 4
        AND A.status_conversation_id = ?
        AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

if ($convn_reply_result) {
        $data['management'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_reply_result->reply;
    } else {
        $data['management'] = strtoupper($convn_result->username) . ' ' . date('d-m-Y', strtotime($convn_result->latest_date)) . ' ' . $convn_result->conversation;
    }
} else {
    $data['management'] = '';
}

// Additional fetch for user_type_id = 6 in other departments
$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
    AND A.selected_department != 'self'
    AND C.user_type_id = 6
    AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
        AND D.selected_department != 'self'
        AND A.user_type_id = 6
        AND A.status_conversation_id = ?
        AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

if ($convn_reply_result) {
        $data['loans'] = strtoupper($convn_result->username).' '.date('d-m-Y', strtotime($convn_result->latest_date)).' '.$convn_reply_result->reply;
    } else {
        $data['loans'] = strtoupper($convn_result->username).' '.date('d-m-Y', strtotime($convn_result->latest_date)).' '.$convn_result->conversation;
    }
} else {
    $data['loans'] = '';
}

// === 2. Main conversation by user_type_id = 5 (Documentation)
$sql = "
    SELECT A.*, B.*, A.created_at AS latest_date, C.username
    FROM gss_status_conversation A
    JOIN gss_bookings B ON B.booking_id = A.booking_id
    JOIN gss_login C ON C.user_id = A.created_by
    WHERE A.booking_id = ?
    AND A.selected_department != 'self'
    AND A.user_type_id = 5
    AND A.delete_status = 'ACTIVE'
    ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
";

$convn_result = $db->query($sql, [$booking_id])->getRow();

if (!empty($convn_result)) {
    $sql = "
        SELECT A.*, B.*, A.created_at AS latest_date, C.username
        FROM gss_status_conversation_replies A
        JOIN gss_bookings B ON B.booking_id = A.booking_id
        JOIN gss_login C ON C.user_id = A.created_by
        JOIN gss_status_conversation D ON A.status_conversation_id = D.id
        WHERE A.booking_id = ?
        AND D.selected_department != 'self'
        AND A.user_type_id = 5
        AND A.status_conversation_id = ?
        AND A.delete_status = 'ACTIVE'
        ORDER BY A.id DESC, STR_TO_DATE(A.created_at, '%d-%m-%Y %h:%i:%s') DESC
    ";

$convn_reply_result = $db->query($sql, [$booking_id, $convn_result->id])->getRow();

if ($convn_reply_result) {
        $data['documentation'] = strtoupper($convn_result->username).' '.date('d-m-Y', strtotime($convn_result->latest_date)).' '.$convn_reply_result->reply;
    } else {
        $data['documentation'] = strtoupper($convn_result->username).' '.date('d-m-Y', strtotime($convn_result->latest_date)).' '.$convn_result->conversation;
    }
} else {
    $data['documentation'] = '';
}

// === 3. Latest Reply
$res = $db->table('gss_status_conversation_replies A')
    ->where('A.booking_id', $booking_id)
    ->orderBy('id', 'DESC')
    ->get()
    ->getRow();

$data['reply'] = $res ? $res->reply : '';

// === 4. Latest Conversation
$res = $db->table('gss_status_conversation A')
    ->where('A.booking_id', $booking_id)
    ->orderBy('id', 'DESC')
    ->get()
    ->getRow();

$data['conversation'] = $res ? $res->conversation : '';

// === 5. Customer Info
$data['customer_name']   = $val->customer_name;
$data['address']         = $val->address;
$data['customer_mobile'] = $val->mobile2 != "" ? $val->mobile1 . "," . $val->mobile2 : $val->mobile1;
$data['customer_email']  = $val->customer_email;
$data['project_name']    = $val->project_name;
$data['site_number']     = $val->site_number;
$data['dimension']       = $val->dimension;

if ($val->booked_on <= $date) {
 $data['booked_on'] = date('d-m-Y', strtotime($val->booked_on));
} else {
 $originalDate = $val->booked_on;
 $data['booked_on'] = date('d-m-Y', strtotime($originalDate));
}

// Source details
$data['source_type']      = $val->source_type;
$data['source_bank_name'] = $val->source_bank_name;

// GSS Amount
$data['gss_amount1'] = $val->gss_amount . " /-";
$data['gss_amount']  = formatIndianCurrency($val->gss_amount);

// TSV amount
$data['tsv1'] = $val->tsv . " /-";
$data['tsv']  = formatIndianCurrency($val->tsv);
$tsv111       = $val->tsv;

// Booking amount calculation
$booking_amount1         = $val->booking_amount1;
$booking_amount2         = $val->booking_amount2;
$booking_amount_total    = $booking_amount1 + $booking_amount2;
$booking_amount111       = $booking_amount_total;
$data['booking_amount']  = formatIndianCurrency($booking_amount_total);

$payment_result = $this->db->table('gss_plot_payments A')
    ->select('A.*')
    ->where('A.booking_id', $booking_id)
    ->where('A.delete_status', 'ACTIVE')
    ->get()
    ->getRow();

if ($payment_result) {
    // Re-query for agreement-specific status
    $payment_result = $this->db->table('gss_plot_payments A')
        ->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.agreement_delete_status', 'ACTIVE')
        ->where('A.delete_status', 'ACTIVE')
        ->get()
        ->getRow();

if (!empty($payment_result->agreement_amount)) {
 $agreementFormatted = formatIndianCurrency($payment_result->agreement_amount);
 $data['sales_agreement_due_amount'] = "{$agreementFormatted}";
 } else {
 $data['sales_agreement_due_amount'] = "0 /-";
 }

if (!empty($payment_result->agreement_amount)) {
        $agr_amt = $payment_result->agreement_amount;
        $data['agreement_due_amount'] = formatIndianCurrency($agr_amt);
    } else {
        $data['agreement_due_amount'] = "0 /-";
    }

// Get latest installment info
    $payment_result1 = $this->db->table('gss_plot_payments A')
        ->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.install_delete_status', 'ACTIVE')
        ->where('A.delete_status', 'ACTIVE')
        ->orderBy('A.payment_id', 'DESC')
        ->get()
        ->getRow();

if (!empty($payment_result1->installment1_date)) {
 $installment_date = date('d-m-Y', strtotime($payment_result1->installment1_date));
 $data['installment_date'] = "{$installment_date}";
 } else {
 // Fallback to booking installments
 $res = $this->db->table('gss_booking_installments A')
 ->select('*')
 ->where('A.booking_id', $booking_id)
 ->where('A.booking_detal_id', $val->detail_id)
 ->where('A.delete_status', 'ACTIVE')
 ->get()
 ->getRow();
 
 if ($res) {
 $installment_date = date("d-m-Y", strtotime($res->installment_due_date));

if (
 $installment_date === '30-11--0001' || 
 $installment_date === '0000-00-00' || 
 $installment_date === ''
 ) {
 $data['installment_date'] = "00-00-0000";
 } else {
 $data['installment_date'] = "{$installment_date}";
 }
} else {
 $data['installment_date'] = '';
}

// Get registration info
$payment_result = $this->db->table('gss_plot_payments A')
    ->select('A.*')
    ->where('A.booking_id', $booking_id)
    ->where('A.registration_delete_status', 'ACTIVE')
    ->where('A.delete_status', 'ACTIVE')
    ->orderBy('A.payment_id', 'DESC')
    ->get()
    ->getRow();

if (!empty($payment_result->registration_date) && $payment_result->registration_date != '0000-00-00') {
 $reg_due_date = date("d-m-Y", strtotime($payment_result->registration_date));
 if (
 $reg_due_date === '30-11--0001' || 
 $reg_due_date === '0000-00-00' || 
 $reg_due_date === '' || 
 $reg_due_date === '01-01-1970'
 ) {
 $data['registration_due_date'] = "";
 } else {
 $data['registration_due_date'] = "{$reg_due_date}";
 }
} else {
 $originalDate = $val->registration_due_date ?? null;
 $reg_date = date("d-m-Y", strtotime($originalDate));
 if (
 $reg_date === '30-11--0001' || 
 $reg_date === '0000-00-00' || 
 $reg_date === '' || 
 $reg_date === '01-01-1970'
 ) {
 $data['registration_due_date'] = "";
 } else {
 $data['registration_due_date'] = "{$reg_date}";
 }
}

$payment_result1 = $this->db->table('gss_plot_payments A')
    ->select('A.*')
    ->where('A.booking_id', $booking_id)
    ->where('A.install_delete_status', 'ACTIVE')
    ->where('A.delete_status', 'ACTIVE')
    ->orderBy('A.payment_id', 'DESC')
    ->get()
    ->getResult();

$installment_amount1 = '';
$tot_inst = 0;

foreach ($payment_result1 as $pr) {
    $installment_amount1 = $pr->installment_amount1;
    $tot_inst += $installment_amount1;
}

if ($tot_inst) {
 $data['installment_amount'] = "" . formatIndianCurrency($tot_inst) . " /-";
} else {
 $res = $this->db->table('gss_booking_installments A')
 ->select('*')
 ->where('A.booking_id', $booking_id)
 ->where('A.booking_detal_id', $val->detail_id)
 ->where('A.delete_status', 'ACTIVE')
 ->get()
 ->getRow();
}

if ($res) {
 $num = $res->installment_due_amount;
 $installment_due_amount = formatIndianCurrency($num) . " /-";
 $data['installment_amount'] = "$installment_due_amount";
} else {
 $data['installment_amount'] = '';
}

if ($tot_inst) {
    $data['ins_amt'] = $tot_inst . " /-";
} else {
    $res = $this->db->table('gss_booking_installments A')
        ->select('*')
        ->where('A.booking_id', $booking_id)
        ->where('A.booking_detal_id', $val->detail_id)
        ->where('A.delete_status', 'ACTIVE')
        ->get()
        ->getRow();

if ($res) {
        $data['ins_amt'] = $res->installment_due_amount . " /-";
    } else {
        $data['ins_amt'] = '';
    }
}

$builder = $this->db->table('gss_plot_payments A');
$builder->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.registration_delete_status', 'ACTIVE')
        ->where('A.delete_status', 'ACTIVE')
        ->groupBy('A.booking_id')
        ->orderBy('A.payment_id', 'DESC');
$payment_result1 = $builder->get()->getResult();

$registration_amount = '';
$tot_reg = 0;
$tot_reg1 = 0;

foreach ($payment_result1 as $pr) {
    $tot_reg = $registration_amount = $pr->registration_amount;

$regDetails = $this->db->table('gss_registration_amount_details A')
        ->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.delete_status', 'ACTIVE')
        ->get()
        ->getResult();

foreach ($regDetails as $pr2) {
        $tot_reg1 += $pr2->reg_amount;
    }

$tot_regs = $tot_reg1 + $tot_reg;
    $cal_tot = $booking_amount111 + $agr_amt + $tot_inst + $tot_regs;
    $reg_due_amt = $tsv111 - $cal_tot;

if ($reg_due_amt == 0) {
 $data['registration_due_amount'] = "0 /-";
 $data['reg_due_amount'] = '0';
 } else {
 $reg_due_amt1 = formatIndianCurrency($reg_due_amt) . " /-";
 $data['registration_due_amount'] = "$reg_due_amt1";
 $data['reg_due_amount'] = $reg_due_amt;
 }
}

if (empty($payment_result1)) {
    if ($val->webportal == 0) {
        $data['web_portal'] = "";
    } else {
        $web_result = $this->db->table('gss_webportals A')
            ->select('A.*, B.*, A.webportal as portal')
            ->join('gss_bookings B', 'B.webportal = A.portal_id')
            ->where('A.portal_id', $val->webportal)
            ->where('B.delete_status', 'ACTIVE')
            ->get()
            ->getRow();

$data['web_portal'] = $web_result ? $web_result->portal : "";
    }
}

if ($val->logistics == 0) {
    $data['logistics'] = "";
} else {
    $builder = $db->table('gss_brokers A');
    $builder->select('A.*, B.*, A.associate_name as logistic')
            ->join('gss_bookings B', 'B.logistics = A.broker_id')
            ->where('B.delete_status', 'ACTIVE')
            ->where('A.broker_id', $val->logistics);
    $logistic_result = $builder->get()->getRow();
    $data['logistics'] = $logistic_result ? $logistic_result->associate_name : "";
}

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

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

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

$originalDate = $val->registration_due_date;
$reg_date = date("d-m-Y", strtotime($originalDate));

if (
 $reg_date == '30-11--0001' ||
 $reg_date == '0000-00-00' ||
 $reg_date == '' ||
 $reg_date == '01-01-1970'
) {
 $data['registration_due_date'] = "";
} else {
 $data['registration_due_date'] = "$reg_date";
}

// Agreement Amount
$agr_amt = 0;
$builder = $db->table('gss_plot_payments A');
$builder->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.agreement_delete_status', 'ACTIVE')
        ->where('A.delete_status', 'ACTIVE');

$payment_result = $builder->get()->getRow();
if (!empty($payment_result->booking_id)) {
    $agr_amt = ($payment_result->agreement_amount != '0') ? $payment_result->agreement_amount : 0;
}

// Installment Amounts
$tot_inst = 0;
$builder = $db->table('gss_plot_payments A');
$builder->select('A.*')
        ->where('A.booking_id', $booking_id)
        ->where('A.install_delete_status', 'ACTIVE')
        ->where('A.delete_status', 'ACTIVE')
        ->orderBy('A.payment_id', 'DESC');

$payment_result1 = $builder->get()->getResult();
foreach ($payment_result1 as $pr) {
    $tot_inst += $pr->installment_amount1;
}

// Registration Amounts
$tot_reg = 0;
$total = 0;
$builder = $db->table('gss_plot_payments A');
$builder->select('A.*')
        ->where('A.registration_delete_status', 'ACTIVE')
        ->where('A.booking_id', $booking_id)
        ->where('A.delete_status', 'ACTIVE')
        ->orderBy('A.payment_id', 'DESC');

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

foreach ($payment_result1 as $pr) {
    $registration_amount = $pr->registration_amount;

$builder2 = $db->table('gss_registration_amount_details A');
    $builder2->select('A.*')
            ->where('A.booking_id', $booking_id)
            ->where('A.delete_status', 'ACTIVE');

$payment_result2 = $builder2->get()->getResult();

if (!empty($payment_result2)) {
        foreach ($payment_result2 as $r) {
            $registration_amount1 = $r->reg_amount;
            $tot_reg += $registration_amount1;
        }
        $total = $tot_reg + $registration_amount;
    } else {
        $registration_amount1 = $registration_amount;
        $total = $registration_amount1;
    }
}

// Final Calculations
$cal_tot = $booking_amount111 + $agr_amt + $tot_inst + $total;
$reg_due_amt = $tsv111 - $cal_tot;
$reg_due = $reg_due_amt;

// Format registration due amount (Indian format)
$num = $reg_due_amt;
$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 . ",";
    }
    $reg_due_amt_total = $explrestunits . $lastthree;
} else {
    $reg_due_amt_total = $num;
}

$data['registration_due_amount'] = "$reg_due_amt_total/-";
$data['reg_due_amount'] = $reg_due;

$data['agreement_due_amount'] = '';
$data['sales_agreement_due_amount'] = '';

// Booking Installments Info
$builder = $db->table('gss_booking_installments A');
$builder->select('*')
        ->where('A.booking_id', $booking_id)
        ->where('A.booking_detal_id', $val->detail_id)
        ->where('A.delete_status', 'ACTIVE');

$res = $builder->get()->getRow();

if ($res) {
 $installment_date = date("d-m-Y", strtotime($res->installment_due_date));
 if (
 $installment_date == '30-11--0001' ||
 $installment_date == '0000-00-00' ||
 $installment_date == ''
 ) {
 $data['installment_date'] = "00-00-0000";
 } else {
 $data['installment_date'] = "$installment_date";
 }
} else {
 $data['installment_date'] = '';
}

// Get installment amount again
$builder = $db->table('gss_booking_installments A');
$builder->select('*')
    ->where('A.booking_id', $booking_id)
    ->where('A.booking_detal_id', $val->detail_id)
    ->where('A.delete_status', 'ACTIVE');

$res = $builder->get()->getRow();

if (!empty($res)) {
 $data['installment_amount'] = "$res->installment_due_amount";
 $data['ins_amt'] = $res->installment_due_amount . " /-";
} else {
 $data['installment_amount'] = '';
 $data['ins_amt'] = '';
}

array_push($array, $data);

return $array;
}}}}

public function get_payment_particulars_reports($from_date,$to_date,$project)
 {
 
 $db = \Config\Database::connect();
 $builder = $db->table('gss_booking_details A');
 
 $builder->select('A.*, A.booking_id as a_booking_id, B.*, B.customer_name, B.booking_id as b_booking_id');
 $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
 
 $builder->where('A.delete_status', 'ACTIVE');
 $builder->where('B.delete_status', 'ACTIVE');
 $builder->where('B.booking_status', 'BOOKED');
 
 if (!empty($from_date)) {
 $builder->where('A.booking_date1 >=', $from_date);
 }
 
 if (!empty($to_date)) {
 $builder->where('A.booking_date1 <=', $to_date);
 }
 
 if (!empty($project)) {
 $builder->where('A.project_id', $project);
 }
 
 $builder->orderBy('ABS(A.site_number)', 'ASC');
 $builder->groupBy('A.booking_id');
 
 $query = $builder->get();
 $result = $query->getResult(); // Same as CI3's $query->result()
 
 $booking_amt_array = []; // You can process $result into this array if needed

$array = [];

foreach ($result as $res)
        {
            $data = []; // Reset for each iteration
        
            $data['booking_id']     = $res->a_booking_id;
            $data['customer_name']  = $res->customer_name;
            $data['site_number']    = $res->site_number;
      $tsv = (float) $res->tsv;
$dimension = (float) $res->dimension;

if (!empty($dimension) && $dimension != 0) {
    $data['dimension'] = $dimension;
    $rps = $tsv / $dimension;
    $data['rate_per_sqft'] = number_format($rps, 2);
} else {
    $data['dimension'] = 0;
    $data['rate_per_sqft'] = "0";
}

$data['tsv'] = $res->tsv;

if ($res->booking_amount1 != 0) {
                $data['booking_amount1'] = $res->booking_amount1;
                $data['confirming_party1'] = ($res->confirming_party1 == 'owner') ? 'Owner' : 'Confirming Party';
                $data['booking_date1'] = date('d-m-Y', strtotime($res->booking_date1));
                $data['booking_payment_type'] = $res->booking_payment_type;
        
                if ($res->booking_payment_type == "Cheque") {
                    $data['check_no'] = $res->check_no;
                    $data['check_date'] = date('d-m-Y', strtotime($res->check_date));
                    $data['bank_name'] = $res->bank_name;
                } elseif ($res->booking_payment_type == "DD") {
                    if (!empty($res->dd_no)) {
                        $data['dd_no'] = $res->dd_no;
                        $data['dd_date'] = date('d-m-Y', strtotime($res->dd_date));
                        $data['dd_bank'] = $res->dd_bank;
                    } else {
                        $data['dd_no'] = '';
                        $data['dd_date'] = '';
                        $data['dd_bank'] = '';
                    }
                } elseif ($res->booking_payment_type == "Online Payment") {
                    $data['vtr_no'] = $res->vtr_no;
                    $data['online_date'] = ($res->online_date == "" || $res->online_date == "0000-00-00") 
                        ? "" 
                        : date('d-m-Y', strtotime($res->online_date));
                } elseif ($res->booking_payment_type == "Paytm Payment") {
                    $data['paytm_ref_no'] = $res->paytm_ref_no;
                    $data['paytm_online_date'] = ($res->paytm_online_date == "" || $res->paytm_online_date == "0000-00-00") 
                        ? "" 
                        : date('d-m-Y', strtotime($res->paytm_online_date));
                } elseif ($res->booking_payment_type == "UPI Payment") {
                    $data['upi_ref_no'] = $res->upi_ref_no;
                    $data['upi_online_date'] = ($res->upi_online_date == "" || $res->upi_online_date == "0000-00-00") 
                        ? "" 
                        : date('d-m-Y', strtotime($res->upi_online_date));
                }
            } else {
                $data['booking_amount1'] = "";
                $data['booking_date1'] = '';
                $data['booking_payment_type'] = "none";
            }

if ($res->booking_amount2 != 0) {
                $data['booking_amount2'] = $res->booking_amount2;
                $data['confirming_party2'] = ($res->confirming_party2 == 'owner') ? 'Owner' : 'Confirming Party';
                $data['booking_date2'] = date('d-m-Y', strtotime($res->booking_date2));
            
                if ($res->booking_payment_type2 != "" && $res->booking_payment_type2 != "0") {
                    $data['booking_payment_type2'] = $res->booking_payment_type2;
                } else {
                    $data['booking_payment_type2'] = "none";
                }
            
                if ($res->booking_payment_type2 == "Cheque") {
                    $data['check_no2'] = $res->check_no2;
                    $data['check_date2'] = date('d-m-Y', strtotime($res->check_date2));
                    $data['bank_name2'] = $res->bank_name2;
            
                } elseif ($res->booking_payment_type2 == "DD") {
                    $data['dd_no2'] = $res->dd_no2;
                    $data['dd_date2'] = date('d-m-Y', strtotime($res->dd_date2));
                    $data['dd_bank2'] = $res->dd_bank2;
            
                } elseif ($res->booking_payment_type2 == "Online Payment") {
                    $data['vtr_no2'] = $res->vtr_no2;
                    $data['online_date2'] = ($res->online_date2 == "" || $res->online_date2 == "0000-00-00")
                        ? ""
                        : date('d-m-Y', strtotime($res->online_date2));
            
                } elseif ($res->booking_payment_type2 == "Paytm Payment") {
                    $data['paytm_ref_no2'] = $res->paytm_ref_no2;
                    $data['paytm_online_date2'] = ($res->paytm_online_date2 == "" || $res->paytm_online_date2 == "0000-00-00")
                        ? ""
                        : date('d-m-Y', strtotime($res->paytm_online_date2));
            
                } elseif ($res->booking_payment_type2 == "UPI Payment") {
                    $data['upi_ref_no2'] = $res->upi_ref_no2;
                    $data['upi_online_date2'] = ($res->upi_online_date2 == "" || $res->upi_online_date2 == "0000-00-00")
                        ? ""
                        : date('d-m-Y', strtotime($res->upi_online_date2));
                }
            
            } else {
                $data['booking_amount2'] = "";
                $data['booking_date2'] = '';
                $data['booking_payment_type2'] = "none";
            }
            $total_amount_paid = 0;
            $booking_amount = $res->booking_amount1 + $res->booking_amount2;
            $data['booking_amount'] = $booking_amount;
            
            $total_amount_paid += $booking_amount;

//-----------------------------------agreement------------------------------------//

$builder = $this->db->table('gss_plot_payments A');
$builder->select('
    B.agreementupi_ref_no,
    B.agreementpaytm_online_date1,
    B.agreementpaytm_ref_no,
    B.agreementupi_online_date1,
    A.agreement_amount,
    A.agreement_date,
    A.agreement_payment_mode,
    A.agreement_confirming_type,
    A.registration_amount,
    A.registration_date,
    A.registration_payment_mode,
    B.agreement_cheque_no,
    B.agreement_cheque_date,
    B.agreement_bank,
    B.agreement_vtr_no,
    B.agreement_online_date,
    B.agreement_dd_no,
    B.agreement_dd_date,
    B.agreement_dd_bank,
    B.*'
);
$builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');
$builder->where('A.booking_id', $res->a_booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('A.agreement_delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->groupBy('A.booking_id');

$query = $builder->get();
$result_agr = $query->getRow();

if (!empty($result_agr)) {
       $data['agreement_date'] = ($result_agr->agreement_date == "0000-00-00" || $result_agr->agreement_date == "") ? "" : date('d-m-Y', strtotime($result_agr->agreement_date));
       
       $data['agreement_confirming_type'] = ($result_agr->agreement_confirming_type == 'owner') ? 'Owner' : 'Confirming Party';
   
       $data['agreement_amount'] = ($result_agr->agreement_amount != 0 && $result_agr->agreement_amount != "") ? $result_agr->agreement_amount : "";
   
       $total_amount_paid += $result_agr->agreement_amount;
   
       $data['agreement_payment_mode'] = ($result_agr->agreement_payment_mode == '' || $result_agr->agreement_payment_mode == "0") ? "none" : $result_agr->agreement_payment_mode;
   
       if ($result_agr->agreement_payment_mode == "Cheque") {
           $data['agreement_cheque_no'] = $result_agr->agreement_cheque_no;
           $data['agreement_cheque_date'] = date('d-m-Y', strtotime($result_agr->agreement_cheque_date));
           $data['agreement_bank'] = $result_agr->agreement_bank;
   
       } else if ($result_agr->agreement_payment_mode == "DD") {
           $data['agreement_dd_no'] = $result_agr->agreement_dd_no;
           $data['agreement_dd_date'] = date('d-m-Y', strtotime($result_agr->agreement_dd_date));
           $data['agreement_dd_bank'] = $result_agr->agreement_dd_bank;
   
       } else if ($result_agr->agreement_payment_mode == "Online Payment") {
           $data['agreement_vtr_no'] = $result_agr->agreement_vtr_no;
           $data['agreement_online_date'] = ($result_agr->agreement_online_date == "" || $result_agr->agreement_online_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->agreement_online_date));
   
       } else if ($result_agr->agreement_payment_mode == "Paytm Payment") {
           $data['agreementpaytm_ref_no'] = $result_agr->agreementpaytm_ref_no;
           $data['agreementpaytm_online_date'] = ($result_agr->agreementpaytm_online_date1 == "" || $result_agr->agreementpaytm_online_date1 == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->agreementpaytm_online_date1));
   
       } else if ($result_agr->agreement_payment_mode == "UPI Payment") {
           $data['agreementupi_ref_no'] = $result_agr->agreementupi_ref_no;
           $data['agreementupi_online_date'] = ($result_agr->agreementupi_online_date1 == "" || $result_agr->agreementupi_online_date1 == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->agreementupi_online_date1));
       }
       
   } else {
       $data['agreement_date'] = "";
       $data['agreement_amount'] = "";
       $data['agreement_payment_mode'] = "none";
   }
//---------------------------------------------------------------------------------------------------   
$builder = $this->db->table('gss_plot_payments A');
$builder->select('B.regn_paytm_date,B.regn_upi_date,B.regn_upiref_no,B.regn_ref_no,A.agreement_amount,A.agreement_date,A.agreement_payment_mode,A.registration_amount,A.registration_date,A.registration_payment_mode,A.registration_confirming_type,B.agreement_cheque_no,B.agreement_cheque_date,B.agreement_bank,B.agreement_vtr_no,B.agreement_online_date,B.agreement_dd_no,B.agreement_dd_date,B.agreement_dd_bank,B.*');
$builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');
$builder->where('A.booking_id', $res->a_booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('A.registration_delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->groupBy('A.booking_id');
$query = $builder->get();
$result_agr = $query->getRow();

if ($result_agr) {
    if ($result_agr->registration_amount == 0) {
        $data['registration_date'] = "";
        $data['registration_amount'] = "";
        $data['registration_payment_mode'] = "none";
    } else {
        $data['registration_date'] = ($result_agr->registration_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->registration_date));
        $data['registration_amount'] = ($result_agr->registration_amount != 0 || $result_agr->registration_amount != "") ? $result_agr->registration_amount : "";
        $data['registration_payment_mode'] = ($result_agr->registration_payment_mode != "" || $result_agr->registration_payment_mode != "0") ? $result_agr->registration_payment_mode : "none";
        $data['registration_confirming_type'] = ($result_agr->registration_confirming_type == 'owner') ? 'Owner' : 'Confirming Party';

$total_amount_paid += (float) $result_agr->registration_amount;

if ($result_agr->registration_payment_mode == "Cheque") {
            $data['regn_cheque_no'] = $result_agr->regn_cheque_no;
            $data['regn_cheque_date'] = date('d-m-Y', strtotime($result_agr->regn_cheque_date));
            $data['regn_bank'] = $result_agr->regn_bank;
        } else if ($result_agr->registration_payment_mode == "DD") {
            $data['regn_dd_no'] = $result_agr->regn_dd_no;
            $data['regn_dd_date'] = date('d-m-Y', strtotime($result_agr->regn_dd_date));
            $data['regn_dd_bank'] = $result_agr->regn_dd_bank;
        } else if ($result_agr->registration_payment_mode == "Online Payment") {
            $data['regn_vtr_no'] = $result_agr->regn_vtr_no;
            $data['regn_online_date'] = ($result_agr->regn_online_date == "" || $result_agr->regn_online_date == "00-00-0000") ? "" : date('d-m-Y', strtotime($result_agr->regn_online_date));
        } else if ($result_agr->registration_payment_mode == "Paytm Payment") {
            $data['regnpaytm_ref_no'] = $result_agr->regn_ref_no;
            $data['regpaytm_online_date'] = ($result_agr->regn_paytm_date == "" || $result_agr->regn_paytm_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->regn_paytm_date));
        } else if ($result_agr->registration_payment_mode == "UPI Payment") {
            $data['regnupi_ref_no'] = $result_agr->regn_upiref_no;
            $data['regnupi_online_date'] = ($result_agr->regn_upi_date == "" || $result_agr->regn_upi_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->regn_upi_date));
        } else if ($result_agr->registration_payment_mode == "Cash") {
            $data['cash_date'] = ($result_agr->cash_date == "" || $result_agr->cash_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->cash_date));
        } else if ($result_agr->registration_payment_mode == "Swipe") {
            $data['swipe_date'] = ($result_agr->swipe_date == "" || $result_agr->swipe_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($result_agr->swipe_date));
        }
    }
} else {
    $data['registration_date'] = "";
    $data['registration_amount'] = "";
    $data['registration_payment_mode'] = "none";
}
 //----------------------------registration ------------------------------//-

$builder = $db->table('gss_registration_amount_details A');
 $builder->select('A.*');
 $builder->where('A.booking_id', $res->a_booking_id);
 $builder->where('A.delete_status', 'ACTIVE');
 $query = $builder->get();
 
 $result_regss = $query->getResult();
 
 $array2 = [];
 $total_registration = 0;
 $total_amount_paid = 0;
 
 if (!empty($result_regss)) {
     foreach ($result_regss as $result_regs) {
         if (!empty($result_regs->reg_amount)) {
             $data2 = [];
 
             // Registration Date
             if ($result_regs->reg_date == "" || $result_regs->reg_date == "00-00-0000") {
                 $data2['registration_date1'] = "";
             } else {
                 $data2['registration_date1'] = date('d-m-Y', strtotime($result_regs->reg_date));
             }
 
             // Amount
             if ($result_regs->reg_amount != 0 || $result_regs->reg_amount != "") {
                 $data2['registration_amount1'] = $result_regs->reg_amount;
             } else {
                 $data2['registration_amount1'] = "";
             }
 
             // Confirming Type
             if ($result_regs->registration_confirming_type == 'owner') {
                 $data2['registration_confirming_type1'] = 'Owner';
             } else {
                 $data2['registration_confirming_type1'] = 'Confirming Party';
             }
 
             $total_amount_paid += $result_regs->reg_amount;
 
             // Payment Mode
             if ($result_regs->reg_payment_mode != "" || $result_regs->reg_payment_mode != '0') {
                 $data2['registration_payment_mode1'] = $result_regs->reg_payment_mode;
             } else {
                 $data2['registration_payment_mode1'] = "none";
             }
 
             // Mode-specific data
             $modeDate = ($result_regs->mode_date && $result_regs->mode_date != "0000-00-00" && $result_regs->mode_date != "00-00-0000") 
                         ? date('d-m-Y', strtotime($result_regs->mode_date)) : "";
 
             switch ($result_regs->reg_payment_mode) {
                 case "Cheque":
                     $data2['regn_cheque_no1'] = $result_regs->number;
                     $data2['regn_cheque_date1'] = $modeDate;
                     $data2['regn_bank1'] = $result_regs->branch_name;
                     break;
 
                 case "DD":
                     $data2['regn_dd_no1'] = $result_regs->number;
                     $data2['regn_dd_date1'] = $modeDate;
                     $data2['regn_dd_bank1'] = $result_regs->branch_name;
                     break;
 
                 case "Online Payment":
                     $data2['regn_vtr_no1'] = $result_regs->number;
                     $data2['regn_online_date1'] = $modeDate;
                     break;
 
                 case "Paytm Payment":
                     $data2['regn_paytm_ref_no'] = $result_regs->number;
                     $data2['regn_paytm_online_date'] = $modeDate;
                     break;
 
                 case "UPI Payment":
                     $data2['regn_upi_ref_no'] = $result_regs->number;
                     $data2['regn_upi_online_date'] = $modeDate;
                     break;
 
                 case "Swipe":
                     $data2['regn_swipe_date'] = $modeDate;  // Looks like you're using number as date too?
                     break;
 
                 case "Cash":
                     $data2['regn_cash_date'] = $modeDate;
                     break;
             }
 
             $array2[] = $data2;
         }
     }
 
     $data['reg_details'] = $array2;
 } else {
     $data2['registration_date1'] = "";
     $data2['registration_amount1'] = "";
     $data2['registration_payment_mode1'] = "none";
     $data['reg_details'] = "";
 }

//-----------------------------------------------------------
$builder = $this->db->table('gss_plot_payments A');
$builder->select('A.*, B.*');
$builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');
// $builder->join('gss_registration_amount_details C', 'C.booking_id = A.booking_id', 'left');

$builder->where('A.booking_id', $res->a_booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->where('A.install_delete_status', 'ACTIVE');
// $builder->where('C.delete_status', 'ACTIVE');

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

$array1 = [];
$total_amount_paid = 0;

if (!empty($result_det)) {
    foreach ($result_det as $pr) {
        if (!empty($pr->installment_amount1)) {
            $data1 = [];

// Installment date
            if ($pr->installment1_date == "" || $pr->installment1_date == "00-00-0000") {
                $data1['installment_date'] = "";
            } else {
                $data1['installment_date'] = date('d-m-Y', strtotime($pr->installment1_date));
            }

// Installment amount
            if ($pr->installment_amount1 != 0 || $pr->installment_amount1 != "") {
                $data1['installment_amount'] = $pr->installment_amount1;
            } else {
                $data1['installment_amount'] = "";
            }

// Confirming type
            $data1['installment_confirming_type'] = ($pr->installment_confirming_type == 'confirming_party') ? 'Confirming Party' : 'Owner';

$total_amount_paid += $pr->installment_amount1;

// Payment mode
            if ($pr->installment_payment_mode1 == "" || $pr->installment_payment_mode1) {
                $data1['installment_payment_mode'] = $pr->installment_payment_mode1;
            } else {
                $data1['installment_payment_mode'] = "none";
            }

// Cheque
            if ($pr->installment_payment_mode1 == "Cheque") {
                $data1['installment_cheque_no'] = $pr->install_cheque_no1;
                $data1['installment_cheque_date'] = date('d-m-Y', strtotime($pr->install_cheque_date1));
                $data1['installment_bank'] = $pr->install_bank1;
            }

// DD
            else if ($pr->installment_payment_mode1 == "DD") {
                $data1['installment_dd_no'] = $pr->install_dd_no1;
                $data1['installment_dd_date'] = date('d-m-Y', strtotime($pr->install_dd_date1));
                $data1['installment_dd_bank'] = $pr->install_dd_bank1;
            }

// Online Payment
            else if ($pr->installment_payment_mode1 == "Online Payment") {
                $data1['installment_vtr_no'] = $pr->install_vtr_no1;
                $data1['installment_online_date'] = ($pr->install_online_date1 == "" || $pr->install_online_date1 == "0000-00-00") 
                    ? "" 
                    : date('d-m-Y', strtotime($pr->install_online_date1));
            }

// Paytm
            else if ($pr->installment_payment_mode1 == "Paytm Payment") {
                $data1['installpaytm_ref_no'] = $pr->paytm_ref_no;
                $data1['installpaytm_online_date'] = ($pr->paytm_online_date1 == "" || $pr->paytm_online_date1 == "0000-00-00") 
                    ? "" 
                    : date('d-m-Y', strtotime($pr->paytm_online_date1));
            }

// UPI
            else if ($pr->installment_payment_mode1 == "UPI Payment") {
                $data1['installupi_ref_no'] = $pr->upi_ref_no;
                $data1['installupi_online_date'] = ($pr->upi_online_date1 == "" || $pr->upi_online_date1 == "0000-00-00") 
                    ? "" 
                    : date('d-m-Y', strtotime($pr->upi_online_date1));
            }

// Cash
            else if ($pr->installment_payment_mode1 == "Cash") {
                $data1['install_cash_date'] = ($pr->install_cash_date == "" || $pr->install_cash_date == "0000-00-00") 
                    ? "" 
                    : date('d-m-Y', strtotime($pr->install_cash_date));
            }

// Swipe
            else if ($pr->installment_payment_mode1 == "Swipe") {
                $data1['install_swipe_date'] = ($pr->install_swipe_date == "" || $pr->install_swipe_date == "0000-00-00") 
                    ? "" 
                    : date('d-m-Y', strtotime($pr->install_swipe_date));
            }

$array1[] = $data1;
        }
    }
    $data['particulars_details'] = $array1;
} else {
    $data1['installment_date'] = "";
    $data1['installment_amount'] = "";
    $data1['installment_payment_mode'] = "none";
    $data['particulars_details'] = "";
}
$tsv=$res->tsv;
$data['total_paid'] = $total_amount_paid;
$data['balance'] = $tsv - $total_amount_paid;

$array[] = $data;
        }
return $array;
 }

public function get_payment_particulars_reports_export($from_date,$to_date,$project)
{

$builder = $this->db->table('gss_booking_details A');
    $builder->select('A.*, A.booking_id as a_booking_id, B.*, B.customer_name, B.booking_id as b_booking_id');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');

$builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('B.booking_status', 'BOOKED');

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

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

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

$builder->groupBy('A.booking_id');

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

$booking_amt_array = [];
$array = [];

$booking1_array = [];
$booking2_array = [];

foreach ($result as $res) {
    $total_amount_paid = 0;

$data = []; // reset $data for each loop

$data['booking_id']     = $res->booking_id;
    $data['customer_name']  = $res->customer_name;
    $data['site_number']    = $res->site_number;

$dimension = (float)$res->dimension;
    $tsv       = (float)$res->tsv;

if (!empty($dimension) && $dimension != 0) {
        $data['dimension'] = $dimension;
        $rps = $tsv / $dimension;
        $data['rate_per_sqft'] = number_format($rps, 2);
    } else {
        $data['dimension'] = 0;
        $data['rate_per_sqft'] = "0";
    }

$tsv = (float) $res->tsv;
$num = (string) (int) $tsv;

$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 => $unit) {
        $explrestunits .= ($i === 0) ? (int)$unit . "," : $unit . ",";
    }
    $tsv_total = $explrestunits . $lastthree;
} else {
    $tsv_total = $num;
}

$data['tsv'] = $tsv_total . '/-';

if ($res->booking_amount2 != 0) {
    if ($res->confirming_party2 == 'owner') {
        $data['confirming_party2'] = 'Owner';
    } else {
        $data['confirming_party2'] = 'Confirming Party';
    }

$num = $res->booking_amount2;
 $explrestunits = "";
 if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $booking_amount2_total = $explrestunits . $lastthree;
 } else {
 $booking_amount2_total = $num;
 }

$data['booking_amount2'] = $booking_amount2_total . '/-';
    $data['booking_date2'] = date('d-m-Y', strtotime($res->booking_date2));
    $data['booking_payment_type2'] = $res->booking_payment_type2;

if ($res->booking_payment_type2 == "Cheque") {
        $data['check_no2'] = $res->check_no2;
        $data['check_date2'] = date('d-m-Y', strtotime($res->check_date2));
        $data['bank_name2'] = $res->bank_name2;
    } else if ($res->booking_payment_type2 == "DD") {
        $data['dd_no2'] = $res->dd_no2;
        $data['dd_date2'] = date('d-m-Y', strtotime($res->dd_date2));
        $data['dd_bank2'] = $res->dd_bank2;
    } else if ($res->booking_payment_type2 == "Online Payment") {
        $data['vtr_no2'] = $res->vtr_no2;
        $data['online_date2'] = date('d-m-Y', strtotime($res->online_date2));
    } else if ($res->booking_payment_type2 == "UPI Payment") {
        $data['upi_ref_no2'] = $res->upi_ref_no2;
        $data['upi_online_date2'] = date('d-m-Y', strtotime($res->upi_online_date2));
    } else if ($res->booking_payment_type2 == "Paytm Payment") {
        $data['paytm_ref_no2'] = $res->paytm_ref_no2;
        $data['paytm_online_date2'] = date('d-m-Y', strtotime($res->paytm_online_date2));
    }

} else {
    $data['booking_amount2'] = 0;
    $data['booking_date2'] = '00-00-0000';
    $data['booking_payment_type2'] = "NONE";
}
if ($res->booking_amount1 != 0) {
    if ($res->confirming_party1 == 'owner') {
        $data['confirming_party1'] = 'Owner';
    } else {
        $data['confirming_party1'] = 'Confirming Party';
    }

$num = $res->booking_amount1;
 $explrestunits = "";
 if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $booking_amount1_total = $explrestunits . $lastthree;
 } else {
 $booking_amount1_total = $num;
 }

$data['booking_amount1'] = $booking_amount1_total . '/-';
    $data['booking_date1'] = date('d-m-Y', strtotime($res->booking_date1));
    $data['booking_payment_type'] = $res->booking_payment_type;

if ($res->booking_payment_type == "Cheque") {
        $data['check_no'] = $res->check_no;
        $data['check_date'] = date('d-m-Y', strtotime($res->check_date));
        $data['bank_name'] = $res->bank_name;
    } else if ($res->booking_payment_type == "DD") {
        $data['dd_no'] = $res->dd_no == "" ? "" : $res->dd_no;
        $data['dd_date'] = ($res->dd_date == "" || $res->dd_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($res->dd_date));
        $data['dd_bank'] = $res->dd_bank == "" ? "" : $res->dd_bank;
    } else if ($res->booking_payment_type == "Online Payment") {
        $data['vtr_no'] = $res->vtr_no;
        $data['online_date'] = ($res->online_date == "" || $res->online_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($res->online_date));
    } else if ($res->booking_payment_type == "UPI Payment") {
        $data['upi_ref_no'] = $res->upi_ref_no;
        $data['upi_online_date'] = ($res->upi_online_date == "" || $res->upi_online_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($res->upi_online_date));
    } else if ($res->booking_payment_type == "Paytm Payment") {
        $data['paytm_ref_no'] = $res->paytm_ref_no;
        $data['paytm_online_date'] = ($res->paytm_online_date == "" || $res->paytm_online_date == "0000-00-00") ? "" : date('d-m-Y', strtotime($res->paytm_online_date));
    }
}
else {
    $data['booking_amount1'] = 0;
    $data['booking_date1']  = '00-00-0000';
    $data['booking_payment_type'] = "NONE";
}

$data['booking_amount'] = $booking_amount = $res->booking_amount1 + $res->booking_amount2;

$total_amount_paid = $total_amount_paid + $booking_amount;

//-----------------------------------agreement------------------------------------//
$db = \Config\Database::connect();
$builder = $db->table('gss_plot_payments A');
$builder->select('A.agreement_amount, A.agreement_date, A.agreement_payment_mode, A.registration_amount, A.registration_date, A.registration_payment_mode, B.agreement_cheque_no, B.agreement_cheque_date, B.agreement_bank, B.agreement_vtr_no, B.agreement_online_date, B.agreement_dd_no, B.agreement_dd_date, B.agreement_dd_bank, B.*');
$builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');
$builder->where('A.booking_id', $res->booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->groupBy('A.booking_id');
$query = $builder->get();
$result_agr = $query->getRow();

if (!empty($result_agr)) {
    // Agreement Date
    $data['agreement_date'] = ($result_agr->agreement_date == '0000-00-00' || $result_agr->agreement_date == '') 
        ? '00-00-0000' 
        : date('d-m-Y', strtotime($result_agr->agreement_date));

// Agreement Amount Formatting
    if ($result_agr->agreement_amount != 0 || $result_agr->agreement_amount != "") {
        $num = $result_agr->agreement_amount;
        $explrestunits = '';
        if (strlen($num) > 3) {
            $lastthree = substr($num, strlen($num) - 3);
            $restunits = substr($num, 0, strlen($num) - 3);
            $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
            $expunit = str_split($restunits, 2);
            foreach ($expunit as $i => $unit) {
                $explrestunits .= ($i == 0) ? (int)$unit . "," : $unit . ",";
            }
            $agreement_amount_total = $explrestunits . $lastthree;
        } else {
            $agreement_amount_total = $num;
        }
        $data['agreement_amount'] = $agreement_amount_total . '/-';
    } else {
        $data['agreement_amount'] = 0;
    }

$total_amount_paid += (int)$result_agr->agreement_amount;

// Agreement Payment Mode
    $data['agreement_payment_mode'] = ($result_agr->agreement_payment_mode == '' || $result_agr->agreement_payment_mode == '0')
        ? 'NONE'
        : $result_agr->agreement_payment_mode;

// Payment Details by Mode
    switch ($result_agr->agreement_payment_mode) {
        case 'Cheque':
            $data['agreement_cheque_no'] = $result_agr->agreement_cheque_no;
            $data['agreement_cheque_date'] = date('d-m-Y', strtotime($result_agr->agreement_cheque_date));
            $data['agreement_bank'] = $result_agr->agreement_bank;
            break;
        case 'DD':
            $data['agreement_dd_no'] = $result_agr->agreement_dd_no;
            $data['agreement_dd_date'] = date('d-m-Y', strtotime($result_agr->agreement_dd_date));
            $data['agreement_dd_bank'] = $result_agr->agreement_dd_bank;
            break;
        case 'Online Payment':
            $data['agreement_vtr_no'] = $result_agr->agreement_vtr_no;
            $data['agreement_online_date'] = ($result_agr->agreement_online_date == '' || $result_agr->agreement_online_date == '0000-00-00') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->agreement_online_date));
            break;
        case 'Paytm Payment':
            $data['agreementpaytm_ref_no'] = $result_agr->agreementpaytm_ref_no;
            $data['agreementpaytm_online_date1'] = ($result_agr->agreementpaytm_online_date1 == '' || $result_agr->agreementpaytm_online_date1 == '0000-00-00')
                ? '00-00-0000'
                : date('d-m-Y', strtotime($result_agr->agreementpaytm_online_date1));
            break;
        case 'UPI Payment':
            $data['agreementupi_ref_no'] = $result_agr->agreementupi_ref_no;
            $data['agreementupi_online_date1'] = ($result_agr->agreementupi_online_date1 == '' || $result_agr->agreementupi_online_date1 == '0000-00-00')
                ? '00-00-0000'
                : date('d-m-Y', strtotime($result_agr->agreementupi_online_date1));
            break;
    }

// Registration Date
    $data['registration_date'] = ($result_agr->registration_date == '0000-00-00')
        ? '00-00-0000'
        : date('d-m-Y', strtotime($result_agr->registration_date));

// Registration Amount Formatting
    if ($result_agr->registration_amount != 0 || $result_agr->registration_amount != "") {
        $num = $result_agr->registration_amount;
        $explrestunits = '';
        if (strlen($num) > 3) {
            $lastthree = substr($num, strlen($num) - 3);
            $restunits = substr($num, 0, strlen($num) - 3);
            $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
            $expunit = str_split($restunits, 2);
            foreach ($expunit as $i => $unit) {
                $explrestunits .= ($i == 0) ? (int)$unit . "," : $unit . ",";
            }
            $registration_amount_total = $explrestunits . $lastthree;
        } else {
            $registration_amount_total = $num;
        }
        $data['registration_amount'] = $registration_amount_total . '/-';
    } else {
        $data['registration_amount'] = 0;
    }

// Registration Payment Mode
    $data['registration_payment_mode'] = ($result_agr->registration_payment_mode != '' && $result_agr->registration_payment_mode != '0') 
        ? $result_agr->registration_payment_mode 
        : 'NONE';

$total_amount_paid += (int)$result_agr->registration_amount;

// Payment Details by Mode
    switch ($result_agr->registration_payment_mode) {
        case 'Cheque':
            $data['regn_cheque_no'] = $result_agr->regn_cheque_no;
            $data['regn_cheque_date'] = date('d-m-Y', strtotime($result_agr->regn_cheque_date));
            $data['regn_bank'] = $result_agr->regn_bank;
            break;
        case 'DD':
            $data['regn_dd_no'] = $result_agr->regn_dd_no;
            $data['regn_dd_date'] = date('d-m-Y', strtotime($result_agr->regn_dd_date));
            $data['regn_dd_bank'] = $result_agr->regn_dd_bank;
            break;
        case 'Online Payment':
            $data['regn_vtr_no'] = $result_agr->regn_vtr_no;
            $data['regn_online_date'] = ($result_agr->regn_online_date == '' || $result_agr->regn_online_date == '00-00-0000') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->regn_online_date));
            break;
        case 'Paytm Payment':
            $data['regn_ref_no'] = $result_agr->regn_ref_no;
            $data['regn_paytm_date'] = ($result_agr->regn_paytm_date == '' || $result_agr->regn_paytm_date == '00-00-0000') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->regn_paytm_date));
            break;
        case 'UPI Payment':
            $data['regn_upiref_no'] = $result_agr->regn_upiref_no;
            $data['regn_upi_date'] = ($result_agr->regn_upi_date == '' || $result_agr->regn_upi_date == '00-00-0000') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->regn_upi_date));
            break;
        case 'Cash':
            $data['cash_date'] = ($result_agr->cash_date == '' || $result_agr->cash_date == '00-00-0000') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->cash_date));
            break;
        case 'Swipe':
            $data['swipe_date'] = ($result_agr->swipe_date == '' || $result_agr->swipe_date == '00-00-0000') 
                ? '00-00-0000' 
                : date('d-m-Y', strtotime($result_agr->swipe_date));
            break;
    }
} else {
    $data['agreement_date'] = '00-00-0000';
    $data['agreement_amount'] = 0;
    $data['agreement_payment_mode'] = 'NONE';
    $data['particulars'] = 'NONE';
}
   //----------------------------registration ------------------------------//
   
   $db = \Config\Database::connect();

$builder = $db->table('gss_registration_amount_details A');
$builder->select('A.*');
$builder->where('A.booking_id', $res->booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$query = $builder->get();
$result_regss = $query->getResult();

$array2 = [];
$total_registration = 0;
$total_amount_paid = 0;

if (!empty($result_regss)) {
    foreach ($result_regss as $result_regs) {
        $data2 = [];

if ($result_regs->reg_date == "" || $result_regs->reg_date == "00-00-0000") {
            $data2['registration_date1'] = "00-00-0000";
        } else {
            $data2['registration_date1'] = date('d-m-Y', strtotime($result_regs->reg_date));
        }

if ($result_regs->reg_amount != 0 || $result_regs->reg_amount != "") {
            $num = $result_regs->reg_amount;
            $explrestunits = "";

for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }

$result_regs_total = $explrestunits . $lastthree;
            } else {
                $result_regs_total = $num;
            }

$data2['registration_amount1'] = $result_regs_total . '/-';
        } else {
            $data2['registration_amount1'] = 0;
        }

$total_amount_paid += $result_regs->reg_amount;

if ($result_regs->reg_payment_mode != "" || $result_regs->reg_payment_mode != '0') {
            $data2['registration_payment_mode1'] = $result_regs->reg_payment_mode;
        } else {
            $data2['registration_payment_mode1'] = "NONE";
        }

if ($result_regs->reg_payment_mode == "Cheque") {
            $data2['regn_cheque_no1'] = $result_regs->number;
            $data2['regn_cheque_date1'] = date('d-m-Y', strtotime($result_regs->mode_date));
            $data2['regn_bank1'] = $result_regs->branch_name;
        } elseif ($result_regs->reg_payment_mode == "DD") {
            $data2['regn_dd_no1'] = $result_regs->number;
            $data2['regn_dd_date1'] = date('d-m-Y', strtotime($result_regs->mode_date));
            $data2['regn_dd_bank1'] = $result_regs->branch_name;
        } elseif ($result_regs->reg_payment_mode == "Online Payment") {
            $data2['regn_vtr_no1'] = $result_regs->number;
            $data2['regn_online_date1'] = ($result_regs->mode_date == "" || $result_regs->mode_date == "00-00-0000")
                ? "00-00-0000" : date('d-m-Y', strtotime($result_regs->mode_date));
        } elseif ($result_regs->reg_payment_mode == "Paytm Payment") {
            $data2['regn_ref_no1'] = $result_regs->number;
            $data2['regn_paytmonline_date1'] = ($result_regs->mode_date == "" || $result_regs->mode_date == "00-00-0000")
                ? "00-00-0000" : date('d-m-Y', strtotime($result_regs->mode_date));
        } elseif ($result_regs->reg_payment_mode == "UPI Payment") {
            $data2['regn_upiref_no1'] = $result_regs->number;
            $data2['regn_upi_date1'] = ($result_regs->mode_date == "" || $result_regs->mode_date == "00-00-0000")
                ? "00-00-0000" : date('d-m-Y', strtotime($result_regs->mode_date));
        } elseif ($result_regs->reg_payment_mode == "Cash") {
            $data2['cash_date1'] = ($result_regs->mode_date == "" || $result_regs->mode_date == "00-00-0000")
                ? "00-00-0000" : date('d-m-Y', strtotime($result_regs->mode_date));
        } elseif ($result_regs->reg_payment_mode == "Swipe") {
            $data2['swipe_date1'] = ($result_regs->mode_date == "" || $result_regs->mode_date == "00-00-0000")
                ? "00-00-0000" : date('d-m-Y', strtotime($result_regs->mode_date));
        }

$array2[] = $data2;
    }

$data['reg_details'] = $array2;

} else {
    $data['registration_date1'] = "00-00-0000";
    $data['registration_amount1'] = 0;
    $data['registration_payment_mode1'] = "NONE";
    $data['reg_details'] = "";
}
// -------------------------------
$db = \Config\Database::connect();

$builder = $db->table('gss_plot_payments A');
$builder->select('A.*, B.*');
$builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');
$builder->where('A.booking_id', $res->booking_id);
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();
$result_det = $query->getResult(); // Use getResultArray() if_

if (!empty($result_det)) {
    foreach ($result_det as $pr) {
    
 if ($pr->installment1_date == "" || $pr->installment1_date == "00-00-0000") {
    $data1['installment_date'] = "00-00-0000";
} else {
    $data1['installment_date'] = date('d-m-Y', strtotime($pr->installment1_date));
}

if ($pr->installment_amount1 != 0 || $pr->installment_amount1 != "") {

$num = $pr->installment_amount1;
 $explrestunits = "";
 if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }
 $installment_amount1_total = $explrestunits . $lastthree;
 } else {
 $installment_amount1_total = $num;
 }

$data1['installment_amount'] = $installment_amount1_total . '/-';
} else {
    $data1['installment_amount'] = 0;
}
$total_amount_paid += (int)$pr->installment_amount1;

if ($pr->installment_payment_mode1 == "" || $pr->installment_payment_mode1) {
    $data1['installment_payment_mode'] = $pr->installment_payment_mode1;
} else {
    $data1['installment_payment_mode'] = "NONE";
}

if ($pr->installment_payment_mode1 == "Cheque") {
    $data1['installment_cheque_no'] = $pr->install_cheque_no1;
    $data1['installment_cheque_date'] = date('d-m-Y', strtotime($pr->install_cheque_date1));
    $data1['installment_bank'] = $pr->install_bank1;

} else if ($pr->installment_payment_mode1 == "DD") {
    $data1['installment_dd_no'] = $pr->install_dd_no1;
    $data1['installment_dd_date'] = date('d-m-Y', strtotime($pr->install_dd_date1));
    $data1['installment_dd_bank'] = $pr->install_dd_bank1;

} else if ($pr->installment_payment_mode1 == "Online Payment") {
    $data1['installment_vtr_no'] = $pr->install_vtr_no1;
    if ($pr->install_online_date1 == "" || $pr->install_online_date1 == "0000-00-00") {
        $data1['installment_online_date'] = "00-00-0000";
    } else {
        $data1['installment_online_date'] = date('d-m-Y', strtotime($pr->install_online_date1));
    }

} else if ($pr->installment_payment_mode1 == "Paytm Payment") {
    $data1['installpaytm_ref_no'] = $pr->paytm_ref_no;
    if ($pr->paytm_online_date1 == "" || $pr->paytm_online_date1 == "0000-00-00") {
        $data1['installpaytm_online_date1'] = "00-00-0000";
    } else {
        $data1['installpaytm_online_date1'] = date('d-m-Y', strtotime($pr->paytm_online_date1));
    }

} else if ($pr->installment_payment_mode1 == "UPI Payment") {
    $data1['installupi_ref_no'] = $pr->upi_ref_no;
    if ($pr->upi_online_date1 == "" || $pr->upi_online_date1 == "0000-00-00") {
        $data1['installupi_online_date1'] = "00-00-0000";
    } else {
        $data1['installupi_online_date1'] = date('d-m-Y', strtotime($pr->upi_online_date1));
    }

} else if ($pr->installment_payment_mode1 == "Cash") {
    if ($pr->install_cash_date == "" || $pr->install_cash_date == "0000-00-00") {
        $data1['install_cash_date'] = "00-00-0000";
    } else {
        $data1['install_cash_date'] = date('d-m-Y', strtotime($pr->install_cash_date));
    }

} else if ($pr->installment_payment_mode1 == "Swipe") {
    if ($pr->install_swipe_date == "" || $pr->install_swipe_date == "0000-00-00") {
        $data1['install_swipe_date'] = "00-00-0000";
    } else {
        $data1['install_swipe_date'] = date('d-m-Y', strtotime($pr->install_swipe_date));
    }
}
array_push($array1,$data1);
   }

$data['particulars_details'] = $array1;

}  
else
            {
                $data['installment_date']="00-00-0000";
                $data['installment_amount']=0;
                $data['installment_payment_mode']="NONE";
                $data['particulars_details']  = "";
}

$num = $total_amount_paid;
$explrestunits = "";

if (strlen($num) > 3) {
 $lastthree = substr($num, strlen($num) - 3, strlen($num));
 $restunits = substr($num, 0, strlen($num) - 3);
 $restunits = (strlen($restunits) % 2 == 1) ? "0" . $restunits : $restunits;
 $expunit = str_split($restunits, 2);
 
 for ($i = 0; $i < sizeof($expunit); $i++) {
 if ($i == 0) {
 $explrestunits .= (int)$expunit[$i] . ",";
 } else {
 $explrestunits .= $expunit[$i] . ",";
 }
 }

$total_amount_paid_total = $explrestunits . $lastthree;
} else {
    $total_amount_paid_total = $num;
}
$data['total_paid'] = $total_amount_paid_total . '/-';

$balance = (float)$tsv - (float)$total_amount_paid;

$num = $balance;
$explrestunits = "";

$balance_total = $explrestunits . $lastthree;
} else {
    $balance_total = $num;
}

$data['balance'] = $balance_total . '/-';

array_push($array, $data);
            
        }
     
        return $array;
    
}

public function total_marketing_dimension()
{
    $builder = $this->db->table('gss_report_status');
    $builder->where('delete_status', 'ACTIVE');
    $query = $builder->get();
    $result = $query->getRow();

if ($result) {
        $builder2 = $this->db->table('gss_new_sites');
        $builder2->select('SUM(E.total_in_sqft) as total_in_sqft');
        $builder2->from('gss_new_sites E');
        $builder2->where('E.delete_status', 'ACTIVE');

if (!empty($result->project)) {
            $builder2->where('E.project_id', $result->project);
        }

$builder2->groupBy('E.project_id');
        $site_result1 = $builder2->get()->getResult();

$array = array();
        foreach ($site_result1 as $value) {
            $rps = $value->total_in_sqft;
            $data['total_in_sqft'] = number_format($rps, 2, '.', '');
            array_push($array, $data);
        }

return $array;
    }
}

public function total_booked_dimension()
{
    $builder = $this->db->table('gss_report_status A');
    $builder->where('delete_status', 'ACTIVE');
    $query = $builder->get();
    $result = $query->getRow();

if ($result) {
        $builder2 = $this->db->table('gss_booking_details E');
        $builder2->select('SUM(G.total_in_sqft) as booked_sqft');
        $builder2->join('gss_bookings F', 'F.booking_id = E.booking_id and F.project_id = E.project_id');
        $builder2->join('gss_new_sites G', 'G.project_id = E.project_id and G.site_number = E.site_number');
        $builder2->where('E.delete_status', 'ACTIVE');
        $builder2->where('F.delete_status', 'ACTIVE');
        $builder2->where('F.booking_status', 'BOOKED');

if (!empty($result->project)) {
            $builder2->where('E.project_id', $result->project);
        }

$builder2->groupBy('E.project_id');
        $site_result1 = $builder2->get();
        $site_result2 = $site_result1->getResult();

$array = array();
        foreach ($site_result2 as $value) {
            $rps = $value->booked_sqft;
            $data['booked_sqft'] = number_format($rps, 2, '.', '');
            array_push($array, $data);
        }

return $array;
    }
}

public function get_total_sites_sqft_booked($booking_id)
{
    $db = \Config\Database::connect();
    // Create a query builder instance
    $builder = $this->db->table('gss_booking_details A');
    $builder->select('A.*, A.booking_id as a_booking_id, B.*');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->where('A.booking_id', $booking_id);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');

// Execute the query
    $query = $builder->get();
    
    // Fetch and return a single result (row)
    return $query->getRow();
}

}?>