EVOLUTION-NINJA

Edit File: Gss_model.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class Gss_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 po_bill_details($ids)
{
    $db = \Config\Database::connect(); // Get database connection
    $builder = $db->table('gss_civil_po'); // Set main table

$builder->select('*');
    $builder->join('gss_from_master_table', 'gss_civil_po.mid = gss_from_master_table.mid');
    $builder->where('gss_civil_po.id', $ids);
    $builder->where('gss_civil_po.delete_status', 'ACTIVE');
    $builder->where('gss_from_master_table.delete_status', 'ACTIVE');

$query = $builder->get();
    return $query->getRow(); // Fetch single row as object
}

public function fetch_where_subgrid_data1($table, $where)
{
    $db = \Config\Database::connect(); // Get database connection
    $builder = $db->table($table); // Set table
    $query = $builder->where($where)->get(); // Apply conditions and fetch data
    return $query->getResult(); // Return result as object array
}

public function get_where_result_distinct($table, $where, $order_by)
{
    $db = \Config\Database::connect(); // Load database connection
    $builder = $db->table($table);

$builder->distinct();
    $builder->select('po_no');
    $builder->where($where);
    $builder->orderBy($order_by, "ASC");

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

public function get_where_distinct_po($table, $where)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);
    
    $builder->select('*');
    $builder->where($where);
    $builder->groupBy('po_no');

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

public function get_civil_po_order($table, $from_date, $to_date, $project, $po_no)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);
    
    $builder->select('*');

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

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

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

$builder->where('delete_status', 'ACTIVE');
    $builder->groupBy('po_no');
    $builder->orderBy('id', 'ASC');

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

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

$builder->select('*');
    $builder->where($where);
    $builder->where('delete_status', 'ACTIVE');
    $builder->groupBy(['year', 'po_no']);

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

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

public function get_civil_purchase_order_dates($from_date, $to_date)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_civil_po');

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

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

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

$builder->groupBy(['po_no', 'mid']);
    $builder->orderBy('id', 'ASC');

$query = $builder->get();
    $results = $query->getResultArray(); // Fetch data as an array

return $results;
}

public function get_civil_po($from_date, $to_date, $project, $po_no)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_civil_po');

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

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

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

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

if (!empty($po_no)) {
        $builder->where('po_no', $po_no);
    }

$builder->groupBy('po_no');
    $builder->orderBy('id', 'ASC');

return $builder->get()->getResultArray();
}

public function get_civil_work_order_dates($from_date, $to_date)
{
    $builder = $this->db->table('gss_civil_wo');

// Select required columns (avoid using "SELECT *" for better performance)
    $builder->select([
        'id', 'po_no', 'vendor', 'contact_person', 'contact_number', 
        'email', 'date', 'year', 'currency', 'vendor_address', 
        'product_name', 'indent', 'project_name', 'purpose_no', 
        'cancel_status', 'inr_value', 'sgst', 'cgst', 'igst', 
        'total_value', 'total_amt', 'discount', 'grand_total', 
        'material_delivery', 'gst_no', 'terms_of_payment', 
        'bank_details', 'ot_charges', 'ot_inr_value', 'ot_discount', 
        'ot_sgst', 'ot_cgst', 'ot_igst', 'ot_total_value', 
        'note', 'remarks', 'otc_remarks'
    ]);

// Apply conditions dynamically
    if (!empty($from_date)) {
        $builder->where('date >=', $from_date);
    }

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

// Apply fixed conditions
    $builder->where('delete_status', 'ACTIVE');
    $builder->groupBy(['po_no', 'mid']);
    $builder->orderBy('id', 'ASC');

// Execute query and return result as an array
    return $builder->get()->getResultArray();
}

public function get_civil_work_order_project($project, $wo_no)
{
    $builder = $this->db->table('gss_civil_wo')
                        ->select('*')
                        ->where('delete_status', 'ACTIVE')
                        ->groupBy('po_no')
                        ->orderBy('date', 'ASC');

if (!empty($project)) {
        $builder->where('project_name', $project);
    }
    
    if (!empty($wo_no)) {
        $builder->where('po_no', $wo_no);
    }

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

public function get_civil_products_list($ids)
{
    return $this->db->table('gss_civil_product_details')
                    ->whereIn('id', $ids)
                    ->get()
                    ->getResult();
}

public function get_where_result1111($land_table)
{
    return $this->db->table($land_table)
                    ->where('delete_status', 'ACTIVE')
                    ->get()
                    ->getResult();
}

public function get_civil_purchase_order()
{
    return $this->db->table('gss_civil_po')
                    ->selectMax('po_no')
                    ->where('delete_status', 'ACTIVE')
                    ->get()
                    ->getRow();
}

public function get_whererow($table, $where, $name)
{
      $db = \Config\Database::connect();
    return $db->table($table)
                    ->where($where)
                    ->like('vendor_name', $name)
                    ->get()
                    ->getRow();
}

public function get_where_count($table, $where)
{
      $db = \Config\Database::connect();
    return $db->table($table)
                    ->select('mid')
                    ->where($where)
                    ->groupBy('po_no')
                    ->countAllResults();
}

public function get_civil_work_order()
{
    $db = \Config\Database::connect(); // Connect to the database

$query = $db->table('gss_civil_wo')
                ->selectMax('po_no')
                ->where('delete_status', 'ACTIVE')
                ->get();

return $query->getRowArray() ?? ['po_no' => null]; // Return as an array
}

public function get_civil_service_list($ids)
{
    $db = \Config\Database::connect();

$query = $db->table('gss_civil_service_details')
                ->whereIn('id', $ids)
                ->get()
                ->getResultArray();

return $query;
}

public function get_where_reults_work_order_list($id)
    {
      $db = \Config\Database::connect();

$query = $db->table('gss_product_details')
                    ->whereIn('id', $id)
                    ->get();

// Return the result
        return $query->getResult();
    }

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

$query = $db->table('gss_po_generate')
                    ->selectMax('po_no')
                    ->where('delete_status', 'ACTIVE')
                    ->get();

// Return the result
        return $query->getRow();
    }
    
    public function get_purchase_order_dates($from_date, $to_date)
{
    $builder = $this->db->table('gss_po_generate')
                        ->select('*')
                        ->where('delete_status', 'ACTIVE');

if (!empty($from_date)) {
        $builder->where('created_at >=', $from_date . ' 00:00:00'); // Full day range
    }

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

$builder->groupBy('po_no')->orderBy('id', 'ASC');

$query = $builder->get();
    return $query->getResultArray(); // Returns an array instead of objects
}

public function get_civil_purchase_order_project($project, $wo_no)
{
    $builder = $this->db->table('gss_civil_po')
                        ->select('*')
                        ->where('delete_status', 'ACTIVE');

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

if (!empty($wo_no)) {
        $builder->where('po_no', $wo_no);
    }

$builder->groupBy('po_no')->orderBy('id', 'ASC');

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

return array_map(function ($row) {
        return [
            'po_no'                => $row->po_no,
            'id'                   => $row->id,
            'vendor'               => $row->vendor,
            'contact_person'       => $row->contact_person,
            'contact_number'       => $row->contact_number,
            'email'                => $row->email,
            'date'                 => $row->date,
            'year'                 => $row->year,
            'currency'             => $row->currency,
            'vendor_address'       => $row->vendor_address,
            'product_name'         => $row->product_name,
            'indent'               => $row->indent,
            'project_name'         => $row->project_name,
            'purpose_no'           => $row->purpose_no,
            'cancel_status'        => $row->cancel_status,
            'quentity'             => $row->quentity,
            'unit_price'           => $row->unit_price,
            'inr_value'            => $row->inr_value,
            'sgst'                 => $row->sgst,
            'cgst'                 => $row->cgst,
            'igst'                 => $row->igst,
            'total_value'          => $row->total_value,
            'total_amt'            => $row->total_amt,
            'discount'             => $row->discount,
            'grand_total'          => $row->grand_total,
            'material_delivery'    => $row->material_delivery,
            'gst_no'               => $row->gst_no,
            'terms_of_payment'     => $row->terms_of_payment,
            'bank_details'         => $row->bank_details,
            'ot_charges'           => $row->ot_charges,
            'ot_unit'              => $row->ot_unit,
            'ot_quantity'          => $row->ot_quantity,
            'ot_inr_value'         => $row->ot_inr_value,
            'ot_discount'          => $row->ot_discount,
            'ot_sgst'              => $row->ot_sgst,
            'ot_cgst'              => $row->ot_cgst,
            'ot_igst'              => $row->ot_igst,
            'ot_total_value'       => $row->ot_total_value,
            'note'                 => $row->note,
            'remarks'              => $row->remarks,
            'otc_remarks'          => $row->otc_remarks
        ];
    }, $result);
}

public function get_where_work_order($ids)
{
    $db = \Config\Database::connect();

return $db->table('gss_service_details')
              ->whereIn('id', $ids)
              ->get()
              ->getResult();
}

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

return $db->table('gss_wo_generate')
              ->selectMax('po_no')
              ->where('delete_status', 'ACTIVE')
              ->get()
              ->getRow();
}

public function get_work_order_dates($fromDate = null, $toDate = null)
{
    $builder = $this->db->table('gss_wo_generate')
                        ->select('*')
                        ->where('delete_status', 'ACTIVE');

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

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

$builder->groupBy('po_no')->orderBy('id', 'ASC');

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

public function get_purchase_order_project($project, $wo_no)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_po_generate');

$builder->select('*');

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

if (!empty($wo_no)) {
        $builder->where('po_no', $wo_no);
    }

$builder->where('delete_status', 'ACTIVE');
    $builder->groupBy('po_no');
    $builder->orderBy('date', 'ASC');

return $builder->get()->getResultArray();
}

public function get_civil_po_wo_order($table, $from_date, $to_date, $project, $po_no)
{
    $builder = $this->db->table($table);
    
    // Select all fields
    $builder->select('*');

// Apply filters dynamically
    if (!empty($from_date)) {
        $builder->where('DATE(created_at) >=', $from_date);
    }
    if (!empty($to_date)) {
        $builder->where('DATE(created_at) <=', $to_date);
    }
    if (!empty($project)) {
        $builder->where('project_name', $project);
    }

// Apply delete status condition
    $builder->where('delete_status', 'ACTIVE');

// Group by po_no and order by id ASC
    $builder->groupBy('po_no');
    $builder->orderBy('id', 'ASC');

// Execute the query and return the result
    return $builder->get()->getResult();
}

public function get_wo($from_date, $to_date, $project, $po_no)
{
    $builder = $this->db->table('gss_wo_generate');
    
    // Select all fields
    $builder->select('*');

// Apply delete status condition
    $builder->where('delete_status', 'ACTIVE');

// Group by po_no and order by id ASC
    $builder->groupBy('po_no');
    $builder->orderBy('id', 'ASC');

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

// Transform results into an array
    $array = [];
    foreach ($result as $row) {
        $array[] = [
            'po_no'              => $row->po_no,
            'id'                 => $row->id,
            'vendor'             => $row->vendor,
            'contact_person'     => $row->contact_person,
            'contact_number'     => $row->contact_number,
            'email'              => $row->email,
            'date'               => $row->date,
            'currency'           => $row->currency,
            'vendor_address'     => $row->vendor_address,
            'product_name'       => $row->product_name,
            'indent'             => $row->indent,
            'project_name'       => $row->project_name,
            'purpose_no'         => $row->purpose_no,
            'cancel_status'      => $row->cancel_status,
            'inr_value'          => $row->inr_value,
            'sgst'               => $row->sgst,
            'cgst'               => $row->cgst,
            'igst'               => $row->igst,
            'total_value'        => $row->total_value,
            'total_amt'          => $row->total_amt,
            'discount'           => $row->discount,
            'grand_total'        => $row->grand_total,
            'material_delivery'  => $row->material_delivery,
            'gst_no'             => $row->gst_no,
            'terms_of_payment'   => $row->terms_of_payment,
            'bank_details'       => $row->bank_details,
            'ot_charges'         => $row->ot_charges,
            'ot_inr_value'       => $row->ot_inr_value,
            'ot_discount'        => $row->ot_discount,
            'ot_sgst'            => $row->ot_sgst,
            'ot_cgst'            => $row->ot_cgst,
            'ot_igst'            => $row->ot_igst,
            'ot_total_value'     => $row->ot_total_value,
            'note'               => $row->note,
        ];
    }

return $array;
}

public function get_work_order_project($project, $wo_no)
{
    $builder = $this->db->table('gss_wo_generate');

// Select all fields
    $builder->select('*');

// Apply filters dynamically
    if (!empty($project)) {
        $builder->where('project_name', $project);
    }
    if (!empty($wo_no)) {
        $builder->where('po_no', $wo_no);
    }

// Apply delete status condition
    $builder->where('delete_status', 'ACTIVE');

// Group by po_no and order by date ASC
    $builder->groupBy('po_no');
    $builder->orderBy('date', 'ASC');

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

return $array;
}

//preethi

public function get_where_result_orderby_asc($table, $where, $orderByColumn = 'name')
    {
        if (is_array($orderByColumn)) {
            $orderByColumn = implode(', ', $orderByColumn);
        }

return $this->db->table($table)
                        ->where($where)
                        ->orderBy($orderByColumn, 'ASC')
                        ->get()
                        ->getResult();
    }
    
     public function get_where_result_alphabetical($table, $where, $orderByColumn = 'name')
    {
        if (is_array($orderByColumn)) {
            $orderByColumn = implode(', ', $orderByColumn);
        }

return $this->db->table($table)
                        ->where($where)
                        ->orderBy($orderByColumn, 'ASC')
                        ->get()
                        ->getResult();
    }
    
    public function get_where_alluser($land_table, $where)
{
    return $this->db->table('gss_new_projects A')
                    ->select('A.*, B.user_id, B.username, C.site_number')
                    ->join('gss_login B', 'B.user_id = A.handled_by')
                    ->join('gss_follow_ups C', 'C.project_id = A.project_id')
                    ->where('C.created_at', $where)
                    ->groupBy('A.project_id')
                    ->get()
                    ->getResult(); // Returns an array of objects
}

public function get_projects_ownerwise($admin_id, $land_owner_id)
    {
        $builder = $this->db->table('gss_new_projects A')
                            ->select('A.project_id, A.project_name')
                            ->where('A.project_status', 'ONGOING')
                            ->where('A.delete_status', 'ACTIVE');

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

return $builder->orderBy('A.project_name', 'ASC')
                       ->get()
                       ->getResult(); // Returns an array of objects
    }
    
    public function get_projects_landownerwise($admin_id, $land_owner_id, $status)
{
    $builder = $this->db->table('gss_new_projects A')
                        ->select('A.project_id, A.project_name')
                        ->where('A.delete_status', 'ACTIVE')
                        ->orderBy('A.project_name', 'ASC');

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

if ($status != "ALL") {
        $builder->where('A.project_status', $status);
    }

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

public function get_where_results_project_name()
{
    return $this->db->table('gss_new_projects A') // ✅ Use table() instead of from()
                    ->select('A.*')
                    ->join('gss_land_owners B', 'B.owner_id = A.land_owner_id')
                    ->where('B.delete_status', 'ACTIVE')
                    ->where('A.delete_status', 'ACTIVE')
                    ->where('B.name !=', 'Gss')
                    ->groupBy('A.project_id') // ✅ Use groupBy() correctly
                    ->get()
                    ->getResult();
}

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 check_project($project)
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table('gss_new_projects'); // ✅ Select the table

$builder->select('*')
            ->like('LOWER(project_name)', strtolower($project))
            ->where('delete_status', 'ACTIVE')
            ->where('project_status', 'ONGOING');

return $builder->get()->getResult(); // ✅ Fetch results
}

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(); // ✅ Return the last inserted ID
}

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

// ✅ Main Query
    $builder->select('A.*, A.project_name as land_project, B.project_ownership, A.project_ownership as a_project_ownership')
            ->join('gss_project_ownership B', 'B.id = A.project_ownership', 'left')
            ->where('A.delete_status', 'ACTIVE')
            ->orderBy('A.project_name', 'ASC');

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

$array = [];

foreach ($result as $val) {
        $data = [
            'checkbox' => ($val->mobile_status == 'SELECTED') 
                ? '<input type="checkbox" class="cbox" value="' . $val->project_id . '" checked/>'
                : '<input type="checkbox" class="cbox" value="' . $val->project_id . '"/>',
            'project_id'          => $val->project_id,
            'mobile_status'       => $val->mobile_status,
            'land_project'        => $val->land_project,
            'land_owner_id'       => $val->land_owner_id,
            'marketing'           => $val->marketing,
            'project_status'      => $val->project_status,
            'site_type'           => $val->site_type,
            'nick_name'           => $val->nick_name,
            'conversion_image'    => $val->conversion_image,
            'approval_image'      => $val->approval_image,
            'land_owner_address'  => $val->land_owner_address,
            'maintenance_per_sqft'=> $val->maintenance_per_sqft,
            'project_ownership'   => (!empty($val->project_ownership)) ? $val->project_ownership : $val->a_project_ownership,
            'no_of_years'         => $val->no_of_years,
            'owner'               => ''
        ];

// ✅ Get Land Owner Name
        if ($val->land_owner_id != 0) {
            $ownerBuilder = $db->table('gss_land_owners B')
                               ->select('B.name as owner')
                               ->where('B.delete_status', 'ACTIVE')
                               ->where('B.owner_id', $val->land_owner_id);
            $ownerResult = $ownerBuilder->get()->getRow();
            $data['owner'] = $ownerResult ? $ownerResult->owner : "";
        }

$array[] = $data;
    }

return $array;
}

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

$builder->select('A.*, B.project_name')
            ->join('gss_new_projects B', 'B.project_id = A.project_id')
            ->where('A.delete_status', 'ACTIVE');

return $builder->get()->getResult(); // ✅ Fetch all results as an array of objects
}

public function delete1($table, $where)
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table($table); // ✅ Get table reference

if (!is_array($where) || empty($where)) {
        throw new \Exception("Where condition must be a valid array.");
    }

$builder->where($where)->delete(); // ✅ Execute DELETE query

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

public function check_enquiries($name, $mobile)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_enquiries');

$builder->select('*')
            ->where('mobile', $mobile)
            ->where('delete_status', 'ACTIVE')
            ->like('LOWER(customer_name)', strtolower($name)); // ✅ Case-insensitive LIKE

return $builder->get()->getResult(); // ✅ Fetch all results as an array of objects
}

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

$result = $builder->select('*')
                      ->where($where)
                      ->orderBy($order_by, 'ASC')
                      ->get()
                      ->getResult();

$array = [];
    foreach ($result as $val) {
        $array[] = [
            'broker_id' => $val->broker_id,
            'type' => $val->type,
            'name_or_id' => $val->name_or_id,
            'associate_name' => $val->associate_name,
            'email' => $val->email,
            'mobile' => !empty($val->mobile2) ? $val->mobile . "," . $val->mobile2 : $val->mobile,
            'address' => $val->address,
            'pan_number' => $val->pan_number,
            'reference_details' => $val->reference_details,
            'adhar_number' => $val->adhar_number,
        ];
    }

return $array;
}

public function edit_new_project($project_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_new_projects A');

return $builder->select('A.*, B.conversion_order')
                   ->join('gss_project_conversion_orders B', 'B.project_id = A.project_id', 'left')
                   ->where('A.project_id', $project_id)
                   ->where('A.delete_status', 'ACTIVE')
                   ->get()
                   ->getRow();
}

public function get_where_result1($table, $from_date, $to_date, $customer_name)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);

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

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

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

if (!empty($customer_name)) {
        $builder->where('customer_name', $customer_name);
    }

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

public function get_all_bookings()
{
    $db = \Config\Database::connect(); // ✅ Connect to database
    $builder = $db->table('gss_bookings A');

$builder->select('A.*, B.*, C.*, A.booking_status as a_booking_status, 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.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('C.delete_status', 'ACTIVE')
            ->where('D.delete_status', 'ACTIVE')
            ->where('A.status', '0')
            ->orderBy('A.booking_id', 'DESC');

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

public function user_site_booking_details1($booking_id)
{
     $db = \Config\Database::connect(); 
$builder = $db->table('gss_bookings A');
$builder->select('A.*, B.*, C.*, D.registration_date, A.booking_status as a_booking_status, B.booking_status as b_booking_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_plot_payments D', 'D.booking_id = A.booking_id', 'left');
$builder->where('A.delete_status', 'ACTIVE');
$builder->where('B.delete_status', 'ACTIVE');
$builder->where('A.booking_id', $booking_id);
$builder->orderBy('A.booking_id', 'DESC');

$query = $builder->get();
$result = $query->getRow();
if (!empty($result)) {
    $data['booking_id']         = $result->booking_id;
    $data['booking_type']       = $result->booking_type;
    $data['booking_status']     = $result->a_booking_status;
    $data['customer_name']      = $result->customer_name;
    $data['relation']           = $result->relation;
    $data['father_or_husband']  = $result->father_or_husband;
    $data['email']              = $result->email;
    $data['alternative_email']  = $result->alternative_email;

$data['mobile'] = !empty($result->mobile2)
        ? $result->mobile1 . ',' . $result->mobile2
        : $result->mobile1;

$data['office_number']      = $result->office_number;
    $data['dob']                = $result->dob;
    $data['doa']                = $result->doa;
    $data['address']            = $result->address;
    $data['idproof_name']       = $result->idproof_name;
    $data['nominee_name']       = $result->nominee_name;
    $data['idproof_number']     = $result->idproof_number;
    $data['nominee_contact']    = $result->nominee_contact;
    $data['project_name']       = $result->project_name;
    $data['site_number']        = $result->site_number;
    $data['registration_date']  = $result->registration_date;
    
    
   $builder2 = $db->table('gss_new_sites A');
   $builder2->select('A.total_in_sqft, A.status');
   $builder2->where('A.delete_status', 'ACTIVE');
   $builder2->where('A.project_id', $result->project_id);
   $builder2->where('A.site_number', $result->site_number);
   $query2 = $builder2->get();
   $result_1 = $query2->getRow();

if ($result_1) {
    $data['dimension']     = $result_1->total_in_sqft;
    $data['khata_status']  = $result_1->status;
   } else {
    $data['dimension']     = '';
    $data['khata_status']  = '';
   }

$data['booking_date1']              = $result->booking_date1;
$data['booking_amount1']           = $result->booking_amount1;
$data['booking_date2']              = $result->booking_date2;
$data['booking_amount2']           = $result->booking_amount2;
$data['registration_due_date']     = $result->registration_due_date;
$data['registration_due_amount']   = $result->registration_due_amount;
$data['sales_agreement_due_date']  = $result->sales_agreement_due_date;
$data['sales_agreement_due_amount']= $result->sales_agreement_due_amount;
$data['booking_payment_type']      = $result->booking_payment_type;
$data['check_no']                  = $result->check_no;
$data['check_date']                = $result->check_date;
$data['bank_name']                 = $result->bank_name;
$data['dd_no']                     = $result->dd_no;
$data['dd_date']                   = $result->dd_date;
$data['dd_bank']                   = $result->dd_bank;
$data['vtr_no']                    = $result->vtr_no;
$data['online_date']               = $result->online_date;
$data['paytm_ref_no']              = $result->paytm_ref_no;
$data['paytm_online_date']         = $result->paytm_online_date;
$data['upi_ref_no']                = $result->upi_ref_no;
$data['upi_online_date']           = $result->upi_online_date;

$data['booking_payment_type2']     = $result->booking_payment_type2;
$data['check_no2']                 = $result->check_no2;
$data['check_date2']               = $result->check_date2;
$data['bank_name2']                = $result->bank_name2;
$data['dd_no2']                    = $result->dd_no2;
$data['dd_date2']                  = $result->dd_date2;
$data['dd_bank2']                  = $result->dd_bank2;
$data['vtr_no2']                   = $result->vtr_no2;
$data['online_date2']              = $result->online_date2;
$data['paytm_ref_no2']             = $result->paytm_ref_no2;
$data['paytm_online_date2']        = $result->paytm_online_date2;
$data['upi_ref_no2']               = $result->upi_ref_no2;

if (!empty($result->upi_online_date2) && $result->upi_online_date2 !== '1970-01-01') {
    $data['upi_online_date2'] = $result->upi_online_date2;
} else {
    $data['upi_online_date2'] = '';
}

$data['tsv']                        = $result->tsv;
            $data['tsv_per_sft_rs']             = $result->tsv_per_sft_rs;
            $data['land_owner_rate']            = $result->land_owner_rate;
            $data['land_owner_amount']          = $result->land_owner_amount;
            $data['gss_rate']                   = $result->gss_rate;
            $data['gss_amount']                 = $result->gss_amount;
            $data['idproof_image']              = $result->idproof_image;
            $data['application_scan']           = $result->application_scan;
            $data['logistics']                  = $result->logistics;
            $data['reference']                  = $result->reference;
            $data['associate']                  = $result->associate;
            $data['source_type']                = $result->source_type;
            $data['subassociate']                  = $result->subassociate;
            $data['source_bank_name']                = $result->source_bank_name;
            $data['reg_date_type']                = $result->reg_date_type;
            
            if ($result->confirming_party1 == 'owner') {
             $data['confirming_party1'] = 'Owner';
               } else {
                $data['confirming_party1'] = 'Confirming Party';
            }

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

if ($result->webportal == 0) {
    $data['webportal'] = "";
} else {
    // Query for the webportal details
    $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.delete_status', 'ACTIVE')
                           ->where('B.delete_status', 'ACTIVE')
                           ->where('A.portal_id', $result->webportal)
                           ->get()
                           ->getRow();

if ($web_result) {
        $data['webportal'] = $web_result->portal;
    } else {
        $data['webportal'] = "";
    }
}
if ($result->reference == 0) {
    $data['reference'] = "";
} else {
    // Query for the reference details
    $reference_result = $this->db->table('gss_brokers A')
                                 ->select('A.*, B.*, A.associate_name as reference')
                                 ->join('gss_bookings B', 'B.reference = A.broker_id')
                                 ->where('A.delete_status', 'ACTIVE')
                                 ->where('B.delete_status', 'ACTIVE')
                                 ->where('A.broker_id', $result->reference)
                                 ->get()
                                 ->getRow();

if ($reference_result) {
        $data['reference'] = $reference_result->reference;
    } else {
        $data['reference'] = "";
    }
}

if ($result->reference_two == 0) {
    $data['reference_two'] = "";
} else {
    // Query for the second reference details
    $reference_result2 = $this->db->table('gss_brokers A')
                                  ->select('A.*, B.*, A.associate_name as reference_two')
                                  ->join('gss_bookings B', 'B.reference_two = A.broker_id')
                                  ->where('A.delete_status', 'ACTIVE')
                                  ->where('B.delete_status', 'ACTIVE')
                                  ->where('A.broker_id', $result->reference_two)
                                  ->get()
                                  ->getRow();

if ($reference_result2) {
        $data['reference_two'] = $reference_result2->reference_two;
    } else {
        $data['reference_two'] = "";
    }
}
if ($result->logistics == 0) {
    $data['logistics'] = "";
} else {
    // Query for the logistics details
    $logistic_result = $this->db->table('gss_brokers A')
                                ->select('A.*, B.*, A.associate_name as logistic')
                                ->join('gss_bookings B', 'B.logistics = A.broker_id')
                                ->where('A.delete_status', 'ACTIVE')
                                ->where('B.delete_status', 'ACTIVE')
                                ->where('A.broker_id', $result->logistics)
                                ->get()
                                ->getRow();

if ($logistic_result) {
        $data['logistics'] = $logistic_result->logistic;
    } else {
        $data['logistics'] = "";
    }
}
if ($result->logistics_two == 0) {
    $data['logistics_two'] = "";
} else {
    // Query for the logistics_two details
    $logistic_result = $this->db->table('gss_brokers A')
                                ->select('A.*, B.*, A.associate_name as logistics_two')
                                ->join('gss_bookings B', 'B.logistics_two = A.broker_id')
                                ->where('A.delete_status', 'ACTIVE')
                                ->where('B.delete_status', 'ACTIVE')
                                ->where('A.broker_id', $result->logistics_two)
                                ->get()
                                ->getRow();

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

if ($associate_result) {
        $data['associate'] = $associate_result->associate;
    } else {
        $data['associate'] = "";
    }
}

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

if ($associate_result) {
        $data['subassociate'] = $associate_result->subassociate;
    } else {
        $data['subassociate'] = "";
    }
}
$array[] = $data;
return $array;

}
}
    public function userSiteBookingDetails1($booking_id)
    {
        $db = \Config\Database::connect();

// Main Query
        $builder = $db->table('gss_bookings A')
            ->select('A.*, B.*, C.*, D.registration_date, 
                     A.booking_status AS a_booking_status, 
                     B.booking_status AS b_booking_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_plot_payments D', 'D.booking_id = A.booking_id', 'left')
            ->where('A.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('A.booking_id', $booking_id)
            ->orderBy('A.booking_id', 'DESC');

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

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

// Prepare Data
        $data = [
            'booking_id'                 => $result->booking_id,
            'booking_type'               => $result->booking_type,
            'booking_status'             => $result->a_booking_status,
            'customer_name'              => $result->customer_name,
            'relation'                   => $result->relation,
            'father_or_husband'          => $result->father_or_husband,
            'email'                      => $result->email,
            'alternative_email'          => $result->alternative_email,
            'mobile'                     => !empty($result->mobile2) ? "{$result->mobile1}, {$result->mobile2}" : $result->mobile1,
            'office_number'              => $result->office_number,
            'dob'                        => $result->dob,
            'doa'                        => $result->doa,
            'address'                    => $result->address,
            'idproof_name'               => $result->idproof_name,
            'nominee_name'               => $result->nominee_name,
            'idproof_number'             => $result->idproof_number,
            'nominee_contact'            => $result->nominee_contact,
            'project_name'               => $result->project_name,
            'site_number'                => $result->site_number,
            'registration_date'          => $result->registration_date,
        ];

// Fetch Site Details
        $siteQuery = $db->table('gss_new_sites')
            ->select('total_in_sqft, status')
            ->where(['delete_status' => 'ACTIVE', 'project_id' => $result->project_id, 'site_number' => $result->site_number])
            ->get()->getRow();

$data['dimension'] = $siteQuery ? $siteQuery->total_in_sqft : '';
        $data['khata_status'] = $siteQuery ? $siteQuery->status : '';

// Booking Details
        $data += [
            'booking_date1'              => $result->booking_date1,
            'booking_amount1'            => $result->booking_amount1,
            'booking_date2'              => $result->booking_date2,
            'booking_amount2'            => $result->booking_amount2,
            'registration_due_date'      => $result->registration_due_date,
            'registration_due_amount'    => $result->registration_due_amount,
            'sales_agreement_due_date'   => $result->sales_agreement_due_date,
            'sales_agreement_due_amount' => $result->sales_agreement_due_amount,
            'booking_payment_type'       => $result->booking_payment_type,
            'check_no'                   => $result->check_no,
            'check_date'                 => $result->check_date,
            'bank_name'                  => $result->bank_name,
            'dd_no'                      => $result->dd_no,
            'dd_date'                    => $result->dd_date,
            'dd_bank'                    => $result->dd_bank,
            'vtr_no'                     => $result->vtr_no,
            'online_date'                => $result->online_date,
            'paytm_ref_no'               => $result->paytm_ref_no,
            'paytm_online_date'          => $result->paytm_online_date,
            'upi_ref_no'                 => $result->upi_ref_no,
            'upi_online_date'            => ($result->upi_online_date == '1970-01-01') ? '' : $result->upi_online_date,
        ];

// Web Portal
        $data['webportal'] = $this->getPortalDetails($db, $result->webportal);

// Reference & Associate
        $data['reference'] = $this->getBrokerDetails($db, $result->reference);
        $data['logistics'] = $this->getBrokerDetails($db, $result->logistics);
        $data['associate'] = $this->getBrokerDetails($db, $result->associate);
        $data['subassociate'] = $this->getBrokerDetails($db, $result->subassociate);

return $data;
    }

// Helper function to fetch Web Portal details
    private function getPortalDetails($db, $portal_id)
    {
        if ($portal_id == 0) return '';

$portal = $db->table('gss_webportals')
            ->select('webportal')
            ->where(['delete_status' => 'ACTIVE', 'portal_id' => $portal_id])
            ->get()->getRow();

return $portal ? $portal->webportal : '';
    }

// Helper function to fetch Broker Details (Reference, Associate, Logistics)
    private function getBrokerDetails($db, $broker_id)
    {
        if ($broker_id == 0) return '';

$broker = $db->table('gss_brokers')
            ->select('associate_name')
            ->where(['delete_status' => 'ACTIVE', 'broker_id' => $broker_id])
            ->get()->getRow();

return $broker ? $broker->associate_name : '';
    }

public function single_booking_details($booking_id)
    {
        $db = \Config\Database::connect(); // ✅ Get Database Connection
    
        $builder = $db->table('gss_bookings A');
        $builder->select('
            E.total_in_sqft, A.*, B.*, C.*, D.*, 
            C.check_no as booking_cheque_no, 
            C.check_date as booking_cheque_date, 
            C.bank_name as booking_bank_name, 
            C.vtr_no as booking_vtr_no, 
            C.online_date as booking_online_date, 
            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_new_sites E', 'E.project_id = B.project_id AND C.site_number = E.site_number');
        $builder->join('gss_booking_installments D', 'D.booking_detal_id = C.detail_id');
        
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('A.booking_id', $booking_id);
        $builder->orderBy('A.booking_id', 'DESC');
    
        $result = $builder->get()->getRow(); // ✅ Get a single row
        
        return $result;
    }

public function project_details($site_number, $project_id)
    {
        return $this->db->table('gss_new_sites A')
                        ->select('A.*, B.*')
                        ->join('gss_new_projects B', 'B.project_id = A.project_id')
                        ->where('A.delete_status', 'ACTIVE')
                        ->where('B.delete_status', 'ACTIVE')
                        ->where('A.site_number', $site_number)
                        ->where('A.project_id', $project_id)
                        ->get()
                        ->getRow(); // Fetch single row
    }

public function payment_site_booking_details1($site_number, $project_id)
    {
        return $this->db->table('gss_booking_details A')
                        ->select('*')
                        ->join('gss_bookings B', 'B.booking_id = A.booking_id')
                        ->join('gss_new_sites E', 'E.project_id = B.project_id AND E.site_number = A.site_number') // Adjusted join condition
                        ->where('A.delete_status', 'ACTIVE')
                        ->where('B.delete_status', 'ACTIVE')
                        ->where('E.delete_status', 'ACTIVE')
                        ->where('B.project_id', $project_id)
                        ->where('A.site_number', $site_number)
                        ->get()
                        ->getRow(); // Fetch a single row
    }

public function payment_site_booking_details2($siteNumber, $projectId)
{
    return $this->db->table('gss_new_projects A')
        ->select('*')
        ->join('gss_new_sites B', 'B.project_id = A.project_id')
        ->where('A.delete_status', 'ACTIVE')
        ->where('B.delete_status', 'ACTIVE')
        ->where('A.project_id', $projectId)
        ->where('B.site_number', $siteNumber)
        ->get()
        ->getRow();
}

public function get_all_clients()
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table('gss_bookings A'); // ✅ Set base table

// ✅ Select columns
    $builder->select('A.*, B.*, C.*');
    
    // ✅ Joins
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');

// ✅ Where conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.status', 1);
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');

// ✅ Order by
    $builder->orderBy('A.booking_id', 'DESC');

// ✅ Execute query and return result
    return $builder->get()->getResult();
}

public function client_site_booking_details1($booking_id)
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table('gss_bookings A'); // ✅ Set base table

// ✅ Select columns
    $builder->select('A.*, B.project_id, B.project_name, C.booking_id, C.site_number, C.dimension, D.broker_id, D.associate_name');

// ✅ Joins
    $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', 'D.broker_id = A.reference');

// ✅ Where conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);

// ✅ Execute query and return a single row
    return $builder->get()->getRow();
}

public function client_booking_details($booking_id)
{
    $db = \Config\Database::connect(); // ✅ Connect to the database
    $builder = $db->table('gss_bookings A'); // ✅ Start Query

$builder->select('A.*, B.project_id, B.project_name, C.booking_id, C.site_number, C.dimension, D.broker_id, D.associate_name');
    $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', 'D.broker_id = A.reference');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);

$query = $builder->get(); // ✅ Execute Query

return $query->getRow(); // ✅ Return Single Row
}

public function get_address_list($p_poject)
{
    // Use the database connection in CI4
    $db = \Config\Database::connect();  // Get the DB connection
    $builder = $db->table('gss_bookings A');  // Initialize the query builder for 'gss_bookings'

// Select required columns
    $builder->select('A.customer_name, A.address, A.mobile1, A.project_id, A.booking_id, B.site_number, C.project_name');

// Join related tables
    $builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
    $builder->join('gss_new_projects C', 'C.project_id = A.project_id');

// Add conditions
    $builder->where('A.delete_status', 'ACTIVE');
    // $builder->where('B.delete_status', 'ACTIVE'); // Uncomment if needed
    $builder->where('A.project_id', $p_poject);

// Order by site number
    $builder->orderBy('ABS(B.site_number)', 'ASC');

// Get the results
    $query = $builder->get();
    
    // Return the result as an array of objects
    return $query->getResult();
}

public function check_land_owners($project, $name, $mobile)
{
    // Get the database connection
    $db = \Config\Database::connect();  // Use CI4's database connection
    $builder = $db->table('gss_land_owners');  // Initialize the query builder for 'gss_land_owners'

// Select all columns
    $builder->select('*');

// Add where condition for mobile
    $builder->where('mobile', $mobile);

// Use `like` for case-insensitive matching on name and project_name
    $builder->like('LOWER(name)', strtolower($name));
    $builder->like('LOWER(project_name)', strtolower($project));

// Add delete_status condition
    $builder->where('delete_status', 'ACTIVE');

// Execute the query
    $query = $builder->get();
    
    // Return the results as an array of objects
    return $query->getResult();
}

public function sites_status_list($project_id)
{
    $db = \Config\Database::connect(); // Connect to the database
    $builder = $db->table('gss_new_sites D'); // Set the table for the query

// Query the sites based on project ID and active status
    $builder->select('D.*');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->where('D.project_id', $project_id);
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

// Loop through each site to check the booking status
    foreach ($result as $val)
    {
        $data['site_id'] = $val->site_id;
        $data['site_number'] = $val->site_number;

// Query to fetch booking details for the site
        $builder = $db->table('gss_bookings A');
        $builder->select('A.*, B.*, C.*');
        $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->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('C.delete_status', 'ACTIVE');
        $builder->where('C.site_number', $val->site_number);
        $builder->where('C.project_id', $project_id);
        $builder->where('A.booking_status !=', 'CANCELLED');
        $builder->where('A.booking_status !=', 'REFUNDED');
        $site_result = $builder->get()->getRow(); // Get the first result (row)

if ($site_result)
        {
            $data['site_status'] = $site_result->booking_status;
        }
        else
        {
            $data['site_status'] = $val->status;  // Use the site status if no booking found
        }

// Push the data for each site to the result array
        $array[] = $data;
    }

return $array;
}

public function get_new_sites($table, $where)
{
    $db = \Config\Database::connect(); // Connect to the database
    $builder = $db->table($table); // Set the table for the query

$builder->select('*'); // Select all columns
    $builder->where($where); // Apply the 'where' condition
    $builder->orderBy('ABS(site_number)', 'ASC'); // Order by site_number (absolute value)

$query = $builder->get(); // Execute the query
    return $query->getResult(); // Return the results
}

public function check_enquired_user($customer_name, $mobile1)
{
    $db = \Config\Database::connect(); // Connect to the database
    $builder = $db->table('gss_enquiries'); // Set the table

$builder->select('*') // Select all columns
            ->where('mobile', $mobile1)
            ->where('customer_name', $customer_name)
            ->where('delete_status', 'ACTIVE');

$query = $builder->get(); // Execute query
    return $query->getRow(); // Return a single row object
}

public function booking_status($result)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_booking_details A');
    
    $builder->select('A.*, B.project_id');
    $builder->join('gss_new_sites B', 'B.project_id = A.project_id');
    $builder->where('A.detail_id', $result);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();
    return $query->getRow(); // Returns a single row as an object
}

public function get_where_orderby_row($table, $where, $order_by)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);
    
    $builder->select('*');
    $builder->where($where);
    $builder->orderBy($order_by, "DESC");

$query = $builder->get();
    return $query->getRow(); // Returns a single row as an object
}

public function booking_receipt_site_data($booking_id)
{
    $db = \Config\Database::connect(); // Connect to database
    $builder = $db->table('gss_booking_details B');

$builder->select('B.*, C.*, D.*')
            ->join('gss_bookings C', 'C.booking_id = B.booking_id')
            ->join('gss_new_projects D', 'D.project_id = B.project_id')
            ->where('B.delete_status', 'ACTIVE')
            ->where('C.delete_status', 'ACTIVE')
            ->where('D.delete_status', 'ACTIVE')
            ->where('C.booking_id', $booking_id);

$query = $builder->get();
    return $query->getRow(); // Fetch a single row as an object
}

public function check_notification($booking_id, $detail_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_commission_notifications');

return $builder->where([
        'delete_status' => 'ACTIVE',
        'booking_id'    => $booking_id,
        'detail_id'     => $detail_id
    ])->get()->getRow();
}

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

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

$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('A.final_status', 'PENDING');
    $builder->where('A.agreement_status', 'APPROVED');

// ✅ Corrected `orWhere()` logic
    $builder->groupStart()
            ->where('A.agreement_status', 'APPROVED')
            ->orWhere('A.registration_status', 'APPROVED')
            ->groupEnd();

$builder->groupBy('D.site_number');

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

// public function account_management_list()
//     {
//         $db = \Config\Database::connect();
//         $builder = $db->table('gss_management A');

//         $builder->select('A.*, B.*, C.*, D.*, E.*, B.mobile1 as customer_contact');
//         $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_brokers E', 'E.broker_id = B.associate');

//         $builder->where('A.delete_status', 'ACTIVE');
//         $builder->where('B.delete_status', 'ACTIVE');
//         $builder->where('C.delete_status', 'ACTIVE');
//         $builder->where('D.delete_status', 'ACTIVE');

//         // ✅ Corrected `groupBy` and `orderBy` syntax
//         $builder->groupBy('A.booking_id');
//         $builder->orderBy('A.management_id', 'DESC');

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

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

$builder->select('A.payment_on_agreement, A.agreement_executed_on, A.agreement_status');
        $builder->where('A.delete_status', 'ACTIVE');
        // $builder->where('A.status', 'APPROVED'); // Uncomment if needed
        $builder->where('A.booking_id', $booking_id);
        $builder->where('A.payment_on_agreement !=', '');
        $builder->where('A.agreement_executed_on !=', '0000-00-00');

$query = $builder->get();
        return $query->getRow(); // ✅ CI4 equivalent of `result()->row()`
    }

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

$builder->select('A.payment_on_registration, A.registration_on, A.registration_status');
        $builder->where('A.delete_status', 'ACTIVE');
        // $builder->where('A.status', 'APPROVED'); // Uncomment if needed
        $builder->where('A.booking_id', $booking_id);
        $builder->where('A.payment_on_registration !=', '');
        $builder->where('A.registration_on !=', '0000-00-00');

$query = $builder->get();
        return $query->getRow(); // ✅ CI4 equivalent of `result()->row()`
    }

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

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

$query = $builder->get();
        return $query->getRow(); // ✅ CI4 equivalent of `$result->row()`
    }

public function single_user_account_management_list($booking_id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('gss_bookings B');

$builder->select('F.total_in_sqft, B.*, C.*, D.*, E.*, B.mobile1 as customer_contact');
        $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_brokers E', 'E.broker_id = B.associate');
        $builder->join('gss_new_sites F', 'F.project_id = D.project_id AND C.site_number = F.site_number', 'left'); // ✅ Ensuring proper join condition

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

$query = $builder->get();
        return $query->getRow(); // ✅ CI4 equivalent of `$result->row()`
    }

public function subsingle_user_account_management_list($booking_id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('gss_bookings B');

$builder->select('B.*, C.*, D.*, E.*, B.mobile1 as customer_contact');
        $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_brokers E', 'E.broker_id = B.subassociate', 'left'); // ✅ Using 'left' join to handle null subassociates

$query = $builder->get();
        return $query->getRow(); // ✅ CI4 equivalent of `$result->row()`
    }

public function get_where_row_orderby($table, $where, $order_by)
    {
        $db = \Config\Database::connect();
        
        $builder = $db->table($table); // ✅ Set table dynamically

$builder->select('*');
        $builder->where($where);
        $builder->orderBy($order_by, "DESC");

$query = $builder->get();
        return $query->getResult(); // ✅ CI4 equivalent of `$query->result()`
    }

public function get_where_result_site_ids($project_id)
{
    $db = \Config\Database::connect();
    $array = [];

// Corrected SQL Query
    $query = $db->query("
        SELECT A.booking_id, A.project_id, 
               B.project_name, C.site_number, 
               C.dimension, C.detail_id, 
               D.registration_date, E.site_id, 
               C.tsv, A.booking_status, E.billing_status
        FROM gss_new_sites E
        JOIN gss_bookings A ON A.project_id = E.project_id 
        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_plot_payments D ON D.booking_id = A.booking_id
        WHERE E.project_id = ?
        AND A.delete_status = 'ACTIVE'
        AND B.delete_status = 'ACTIVE'
        AND E.delete_status = 'ACTIVE'
        AND D.registration_date > '0000-00-00'
        AND C.delete_status = 'ACTIVE'
        GROUP BY C.site_number
        ORDER BY ABS(C.site_number) ASC
    ", [$project_id]);

$result = $query->getResult();

foreach ($result as $val) {
        $tables = 'gss_new_sites';
        $condition = ['project_id' => $val->project_id, 'site_number' => $val->site_number, 'delete_status' => 'ACTIVE'];

$get_details = $db->table($tables)->where($condition)->get()->getRow();

if (!empty($get_details) && ($get_details->billing_status == '')) {
            $data = [
                'project_name' => $val->project_name,
                'site_number' => $val->site_number,
                'dimension' => $get_details->total_in_sqft,
                'project_id' => $val->project_id,
                'registration_date' => $val->registration_date,
                'tsv' => $val->tsv,
                'billing_status' => $val->billing_status,
                'site_id' => $val->site_id,
            ];

// Fetching registration amount
            $regn_result = $db->table('gss_plot_payments')
                ->select('registration_amount')
                ->where(['delete_status' => 'ACTIVE', 'booking_id' => $val->booking_id, 'detail_id' => $val->detail_id])
                ->get()
                ->getRow();

if ($regn_result) {
                $data['booking_status'] = ($regn_result->registration_amount != 0) ? 'REGISTERED' : $val->booking_status;
            } else {
                $data['booking_status'] = $val->booking_status;
            }

array_push($array, $data);
        }
    }

return $array;
}

public function get_where_result_sites_sqft($project_id, $site_id)
{
    $db = \Config\Database::connect();
    
    return $db->table('gss_new_sites A')
        ->select('A.total_in_sqft, A.site_id, A.site_number')
        ->where('A.project_id', $project_id)
        ->where('A.site_number', $site_id)
        ->where('A.delete_status', 'ACTIVE')
        ->get()
        ->getRow();
}

public function get_invoice_id($type, $holder)
{
    $db = \Config\Database::connect();
    
    return $db->table('gss_invoice_details')
        ->selectMax('invoice_number')
        ->where('invoice_type', $type)
        ->where('bank_name', $holder)
        ->where('delete_status', 'ACTIVE')
        ->get()
        ->getRow();
}

public function get_all_bank_details($table, $where)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);
    
    $query = $builder->where($where)->get();
    return $query->getResult();
}

public function get_where_row_for_print($id)
    {
        $db = \Config\Database::connect();
        // Initialize the builder object for the invoice details table
        $builder = $this->db->table('gss_invoice_details A');

// Join with the other tables
        $builder->select('A.*, A.id as in_id, B.project_name, C.*');
        $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
        $builder->join('gss_bank_details C', 'C.holder_name = A.bank_name'); // Ensure this join condition is correct
        
        // Add the where conditions
        $builder->where('A.id', $id);
        $builder->where('A.delete_status', 'ACTIVE');

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

// Check if rows exist and return the first row
        if ($query->getNumRows() > 0) {
            return $query->getRow(); // Return the first row
        }

// If no rows found, return null
        return null;
    }

public function get_shared_executive_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    $builder = $this->db->table('gss_booking_details A');
    $builder->select('A.*, A.booking_id as a_booking_id, B.*, C.associate_name, C.broker_id, C.*, D.project_name, E.agreement_date, E.registration_date, F.incentive_status');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_brokers C', 'C.broker_id = A.shared_between_executive');
    $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 = A.site_number');
    $builder->where('A.shared_between_executive', $executive);
    $builder->where('A.selected_incentive_status !=', 'YES');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->groupBy('A.booking_id');
    
    $query = $builder->get();
    $result = $query->getResult();
    $array = [];

foreach ($result as $res) {
        $data['tsv'] = $res->tsv;
        $data['booking_id'] = $res->booking_id;
        $data['booking_date'] = date('d-m-Y', strtotime($res->booking_date1));
        $data['booking_status'] = $res->booking_status;

if (in_array($res->booking_status, ['CANCELLED', 'REFUNDED', 'REFUND_PENDING'])) {
            $builder = $this->db->table('gss_cancellations');
            $builder->where('delete_status', 'ACTIVE');
            $builder->where('booking_id', $res->booking_id);
            $query1 = $builder->get();
            $result1 = $query1->getRow();

$data['cancelled_date'] = !empty($result1) ? date('d-m-Y', strtotime($result1->cancellation_date)) : "";
        } else {
            $data['cancelled_date'] = "";
        }

$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['agreement_date'] = !empty($res->agreement_date) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";
        
        $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));

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

// Get associate name for reference
        $builder = $this->db->table('gss_brokers');
        $builder->where('broker_id', $res->reference);
        $query = $builder->get();
        $ass_name = $query->getRow();

$data['associate_name'] = $ass_name->associate_name ?? '';
        $data['associate_broker_id'] = $ass_name->broker_id ?? '';

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

$data['incentive_percent'] = !empty($res->incentive_percent) ? $res->incentive_percent : "0";
        $data['incentive_rupees'] = !empty($res->incentive_rupees) ? $res->incentive_rupees : "0";

// Get shared executive details
        if ($res->shared_between_executive) {
            $builder = $this->db->table('gss_brokers');
            $builder->where('broker_id', $res->shared_between_executive);
            $query = $builder->get();
            $result = $query->getRow();

$data['shared_between_executive'] = $result->associate_name ?? "";
        } else {
            $data['shared_between_executive'] = "";
        }

$data['shared_executive_amount'] = !empty($res->shared_executive_amount) ? $res->shared_executive_amount : $res->incentive_rupees;
        $data['shared_executive_percentage'] = !empty($res->shared_executive_percentage) ? $res->shared_executive_percentage : $res->incentive_percent;

$array[] = $data;
    }

return $array;
}

public function get_selected_executive_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    // Query to fetch executive incentives
    $builder = $this->db->table('gss_executive_incentives A');
    $builder->select('A.*');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.broker_id', $executive);
    $query = $builder->get();
    $result = $query->getResult();
    $array = [];

foreach ($result as $res) {
        $data['id'] = $res->id;
        $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];

// Query for booking details based on the project and site
                $builder = $this->db->table('gss_booking_details A');
                $builder->select('A.*, B.*, B.booking_id as b_booking_id, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status');
                $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
                $builder->join('gss_brokers C', 'C.broker_id = B.reference', 'left');
                $builder->join('gss_new_projects D', 'D.project_id = A.project_id');
                $builder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
                $builder->join('gss_new_sites F', 'F.project_id = A.project_id AND F.site_number = A.site_number');
                $builder->where('A.project_id', $project_id);
                $builder->where('A.site_number', $site_numbers);
                $builder->where('A.delete_status', 'ACTIVE');
                $builder->where('B.booking_status', 'BOOKED');
                $builder->groupBy('A.booking_id');
                $builder->where('F.incentive_status', 'YES');
                
                $query = $builder->get();
                $result1 = $query->getResult();

foreach ($result1 as $res1) {
                    $data['booking_id'] = $res1->booking_id;
                    $data['booking_date'] = date('d-m-Y', strtotime($res1->booking_date1));
                    $data['booking_status'] = $res1->booking_status;
                    $data['project_id'] = $res1->project_id;
                    $data['project_name'] = $res1->project_name;
                    $data['site_number'] = $res1->site_number;
                    $data['customer_name'] = $res1->customer_name;
                    $data['dimension'] = $res1->dimension;

// Handle agreement and registration dates
                    $data['agreement_date'] = !empty($res1->agreement_date) ? date('d-m-Y', strtotime($res1->agreement_date)) : "00-00-0000";
                    $data['registration_date'] = ($res1->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res1->registration_date));

$data['associate_name'] = $res1->associate_name;
                    $data['incentive_status'] = $res1->incentive_status;
                    $data['shared_between_executive'] = $res1->shared_between_executive;
                    $data['selected_incentive_status'] = $res1->selected_incentive_status;

// Add the data to the result array
                    $array[] = $data;
                }
            }
        }
    }

return $array;
}

public function get_sixty_perc_executive_incentive($from_date, $to_date, $executive)
{
    // Initial query for executive incentives with percentage 60
    $builder = $this->db->table('gss_booking_details A');
    $builder->select('B.reference_two, G.associate_name as reference_two_name, A.*, B.*, B.booking_id as b_booking_id, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status, H.insentive_perc');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_brokers C', 'C.broker_id = B.reference', 'left');
    $builder->join('gss_new_projects D', 'D.project_id = A.project_id');
    $builder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
    $builder->join('gss_new_sites F', 'F.project_id = A.project_id AND F.site_number = A.site_number');
    $builder->join('gss_brokers G', 'G.broker_id = B.reference_two', 'left');
    $builder->join('gss_incentive_reports H', 'H.booking_id2 = A.booking_id');
    
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.booking_status', 'BOOKED');
    $builder->groupBy('A.booking_id');
    $builder->where('H.insentive_perc_sixty2', '60%');
    $builder->where('H.delete_status', 'ACTIVE');
    $builder->where('H.sixty_status2 !=', 'sixty');
    
    if ($from_date != "") {
        $builder->where('DATE(A.booking_date1) >=', $from_date);
    }
    
    if ($to_date != "") {
        $builder->where('DATE(A.booking_date1) <=', $to_date);
    }
    
    if ($executive != "") {
        $builder->where('A.shared_between_executive', $executive);
    }

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

$array = [];
    $array2 = [];

foreach ($result1 as $res1) {
        $data = [];
        $data['booking_id'] = $res1->booking_id;
        $data['booking_date'] = date('d-m-Y', strtotime($res1->booking_date1));
        $data['booking_status'] = $res1->booking_status;
        $data['project_id'] = $res1->project_id;
        $data['project_name'] = $res1->project_name;
        $data['site_number'] = $res1->site_number;
        $data['customer_name'] = $res1->customer_name;
        $data['dimension'] = $res1->dimension;
        
        $data['agreement_date'] = !empty($res1->agreement_date) ? date('d-m-Y', strtotime($res1->agreement_date)) : "00-00-0000";
        $data['registration_date'] = ($res1->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res1->registration_date));
        
        // Combine associate names if both are available
        if ($res1->reference_two != 0) {
            $data['associate_name'] = $res1->associate_name . PHP_EOL . $res1->reference_two_name;
        } else {
            $data['associate_name'] = $res1->associate_name;
        }
        
        $data['incentive_status'] = $res1->incentive_status;
        $data['shared_between_executive'] = $res1->shared_between_executive;
        $data['selected_incentive_status'] = $res1->selected_incentive_status;
        
        // Query for incentive report based on booking_id2
        $builder2 = $this->db->table('gss_incentive_reports A');
        $builder2->where('A.booking_id2', $res1->booking_id);
        $builder2->where('A.insentive_perc_sixty2', '60%');
        $builder2->where('A.delete_status', 'ACTIVE');
        $query3 = $builder2->get();
        $result3 = $query3->getRow();
        
        if ($result3) {
            $data['total_sqft'] = $result3->total_sqft2;
            $data['rate_per_sqft'] = $result3->rate_per_sqft2;
            $data['total_amount_fixed'] = $result3->total_amount_fixed2;
            $data['incentive_on_agmnt'] = $result3->incentive_on_agmnt2;
            $data['incentive_on_regn'] = $result3->incentive_on_regn2;
            $data['insentive_perc'] = $result3->insentive_perc2;
            $data['amount_tobe_paid'] = $result3->balance2;
            $data['balance'] = $result3->balance2;
            $data['source'] = $result3->source2;
            $data['insentive_perc_sixty'] = $result3->insentive_perc_sixty2;
        } else {
            $data['total_sqft'] = "0";
            $data['rate_per_sqft'] = "0";
            $data['total_amount_fixed'] = "0";
            $data['incentive_on_agmnt'] = "0";
            $data['incentive_on_regn'] = "0";
            $data['insentive_perc'] = "0";
            $data['amount_tobe_paid'] = "0";
            $data['balance'] = "0";
            $data['source'] = "";
        }

$array2[] = $data;
    }
    $db = \Config\Database::connect();
    // Query for the second set of incentives for the same executive
    $builder = $this->db->table('gss_booking_details A');
    $builder->select('B.reference_two, G.associate_name as reference_two_name, A.*, B.*, B.booking_id as b_booking_id, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status, H.insentive_perc');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_brokers C', 'C.broker_id = B.reference', 'left');
    $builder->join('gss_new_projects D', 'D.project_id = A.project_id');
    $builder->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
    $builder->join('gss_new_sites F', 'F.project_id = A.project_id AND F.site_number = A.site_number');
    $builder->join('gss_brokers G', 'G.broker_id = B.reference_two', 'left');
    $builder->join('gss_incentive_reports H', 'H.booking_id = A.booking_id');
    
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.booking_status', 'BOOKED');
    $builder->groupBy('A.booking_id');
    $builder->where('C.broker_id', $executive);
    $builder->where('H.insentive_perc_sixty', '60%');
    $builder->where('H.delete_status', 'ACTIVE');
    $builder->where('H.sixty_status !=', 'sixty');
    
    if ($from_date != "") {
        $builder->where('DATE(A.booking_date1) >=', $from_date);
    }
    
    if ($to_date != "") {
        $builder->where('DATE(A.booking_date1) <=', $to_date);
    }

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

$data['agreement_date'] = !empty($res1->agreement_date) ? date('d-m-Y', strtotime($res1->agreement_date)) : "00-00-0000";
        $data['registration_date'] = ($res1->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res1->registration_date));
        
        // Combine associate names if both are available
        if ($res1->reference_two != 0) {
            $data['associate_name'] = $res1->associate_name . PHP_EOL . $res1->reference_two_name;
        } else {
            $data['associate_name'] = $res1->associate_name;
        }

$data['incentive_status'] = $res1->incentive_status;
        $data['shared_between_executive'] = $res1->shared_between_executive;
        $data['selected_incentive_status'] = $res1->selected_incentive_status;

// Query for incentive report based on booking_id
        $builder2 = $this->db->table('gss_incentive_reports A');
        $builder2->where('A.booking_id', $res1->booking_id);
        $builder2->where('A.insentive_perc_sixty', '60%');
        $builder2->where('A.delete_status', 'ACTIVE');
        $query3 = $builder2->get();
        $result3 = $query3->getRow();

if ($result3) {
            $data['total_sqft'] = $result3->total_sqft;
            $data['rate_per_sqft'] = $result3->rate_per_sqft;
            $data['total_amount_fixed'] = $result3->total_amount_fixed;
            $data['incentive_on_agmnt'] = $result3->incentive_on_agmnt;
            $data['incentive_on_regn'] = $result3->incentive_on_regn;
            $data['insentive_perc'] = $result3->insentive_perc;
            $data['amount_tobe_paid'] = $result3->balance;
            $data['balance'] = $result3->balance;
            $data['source'] = $result3->source;
            $data['insentive_perc_sixty'] = $result3->insentive_perc_sixty;
        } else {
            $data['total_sqft'] = "0";
            $data['rate_per_sqft'] = "0";
            $data['total_amount_fixed'] = "0";
            $data['incentive_on_agmnt'] = "0";
            $data['incentive_on_regn'] = "0";
            $data['insentive_perc'] = "0";
            $data['amount_tobe_paid'] = "0";
            $data['balance'] = "0";
            $data['source'] = "";
        }

$array[] = $data;
    }

// Merge both results
    $final_array = array_merge($array, $array2);
    return $final_array;
}

public function get_executive_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    // Start building the query
    $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.tsv, B.booking_date1, B.project_id, B.site_number, B.dimension, B.shared_between_executive, B.shared_executive_percentage, B.shared_executive_amount, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status, C.incentive_percent, C.incentive_rupees');
    $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('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->groupBy('A.booking_id');
    
    if ($from_date != "") {
        $builder->where('DATE(A.created_at) >=', $from_date);
    }
    
    if ($to_date != "") {
        $builder->where('DATE(A.created_at) <=', $to_date);
    }
    
    if ($executive != "") {
        $builder->where('A.reference', $executive);
    }

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

$array = [];
    
    foreach ($result as $res) {
        $data = [];
        $data['tsv'] = $res->tsv;
        $data['booking_id'] = $res->booking_id;
        $data['booking_date'] = date('d-m-Y', strtotime($res->booking_date1));
        $data['booking_status'] = $res->booking_status;
        
        // Handle cancellation if applicable
        if (in_array($res->booking_status, ['CANCELLED', 'REFUNDED', 'REFUND_PENDING'])) {
            $builder1 = $this->db->table('gss_cancellations');
            $builder1->where('delete_status', 'ACTIVE');
            $builder1->where('booking_id', $res->booking_id);
            $query1 = $builder1->get();
            $result1 = $query1->getRow();
            
            $data['cancelled_date'] = $result1 ? date('d-m-Y', strtotime($result1->cancellation_date)) : "";
        } else {
            $data['cancelled_date'] = "";
        }
        
        // Other details
        $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;
        
        // Agreement date
        $data['agreement_date'] = !empty($res->agreement_date) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";
        
        // Registration date
        $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));
        
        // Broker details
        $data['broker_id'] = $res->broker_id;
        $data['associate_name'] = $res->associate_name;
        
        // Incentive details
        $data['incentive_status'] = $res->incentive_status;
        $data['incentive_percent'] = !empty($res->incentive_percent) ? $res->incentive_percent : "0";
        $data['incentive_rupees'] = !empty($res->incentive_rupees) ? $res->incentive_rupees : "0";
        
        // Shared executive details
        if ($res->shared_between_executive) {
            $builder2 = $this->db->table('gss_brokers');
            $builder2->where('broker_id', $res->shared_between_executive);
            $query2 = $builder2->get();
            $result2 = $query2->getRow();
            
            $data['shared_between_executive'] = $result2 ? $result2->associate_name : "";
        } else {
            $data['shared_between_executive'] = "";
        }
        
        $data['shared_executive_amount'] = $res->shared_executive_amount ?: $res->incentive_rupees;
        $data['shared_executive_percentage'] = $res->shared_executive_percentage ?: $res->incentive_percent;
        
        // Add the data to the final result array
        $array[] = $data;
    }
    
    return $array;
}

public function get_pending_executive_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    // Start by fetching data from the executive incentives table
    $builder = $this->db->table('gss_executive_incentives A');
    $builder->select('A.*');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.broker_id', $executive);
    $query = $builder->get();
    $results = $query->getResult();
    
    // Prepare an array to hold the final result
    $array = [];
    
    foreach ($results as $ress) {
        // Now, fetch bookings related to this executive
        $builder2 = $this->db->table('gss_bookings A');
        $builder2->select('A.reference, A.booking_id, A.customer_name, A.created_at as a_created_at, A.booking_status, B.tsv, B.booking_date1, B.project_id, B.site_number, B.dimension, B.shared_between_executive, B.shared_executive_percentage, B.shared_executive_amount, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status, C.incentive_percent, C.incentive_rupees');
        $builder2->join('gss_booking_details B', 'B.booking_id = A.booking_id');
        $builder2->join('gss_brokers C', 'C.broker_id = A.reference', 'left');
        $builder2->join('gss_new_projects D', 'D.project_id = B.project_id');
        $builder2->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left');
        $builder2->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number');
        $builder2->where('A.booking_status !=', 'ENQUIRED');
        $builder2->where('A.delete_status', 'ACTIVE');
        $builder2->where('B.delete_status', 'ACTIVE');
        $builder2->where('D.delete_status', 'ACTIVE');
        $builder2->where('B.selected_incentive_status !=', 'YES');
        $builder2->groupBy('A.booking_id');
        
        if ($from_date != "") {
            $builder2->where('DATE(B.booking_date1) >=', $from_date);
        }
        
        if ($to_date != "") {
            $builder2->where('DATE(B.booking_date1) <=', $to_date);
        }
        
        if ($executive != "") {
            $builder2->where('A.reference', $ress->broker_id);
        }
        
        $query2 = $builder2->get();
        $result = $query2->getResult();
        
        // Process the result for each booking
        foreach ($result as $res) {
            $data = [];
            $data['tsv'] = $res->tsv;
            $data['booking_id'] = $res->booking_id;
            $data['booking_date'] = date('d-m-Y', strtotime($res->booking_date1));
            $data['booking_status'] = $res->booking_status;
            
            // Handle cancellation status
            if (in_array($res->booking_status, ['CANCELLED', 'REFUNDED', 'REFUND_PENDING'])) {
                $builder3 = $this->db->table('gss_cancellations');
                $builder3->where('delete_status', 'ACTIVE');
                $builder3->where('booking_id', $res->booking_id);
                $query3 = $builder3->get();
                $result1 = $query3->getRow();
                
                $data['cancelled_date'] = $result1 ? date('d-m-Y', strtotime($result1->cancellation_date)) : "";
            } else {
                $data['cancelled_date'] = "";
            }

// Additional project, customer, and incentive details
            $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['agreement_date'] = !empty($res->agreement_date) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";
            $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));
            $data['broker_id'] = $res->broker_id;
            $data['associate_name'] = $res->associate_name;
            $data['incentive_status'] = $res->incentive_status;
            $data['incentive_percent'] = !empty($res->incentive_percent) ? $res->incentive_percent : "0";
            $data['incentive_rupees'] = !empty($res->incentive_rupees) ? $res->incentive_rupees : "0";
            
            // Shared executive information
            if ($res->shared_between_executive) {
                $builder4 = $this->db->table('gss_brokers');
                $builder4->where('broker_id', $res->shared_between_executive);
                $query4 = $builder4->get();
                $result2 = $query4->getRow();
                
                $data['shared_between_executive'] = $result2 ? $result2->associate_name : "";
            } else {
                $data['shared_between_executive'] = "";
            }
            
            $data['shared_executive_amount'] = $res->shared_executive_amount ?: $res->incentive_rupees;
            $data['shared_executive_percentage'] = $res->shared_executive_percentage ?: $res->incentive_percent;
            
            // Add to final array
            $array[] = $data;
        }
    }
    
    return $array;
}

public function get_paidpending_executive_incentives($from_date = "", $to_date = "", $executive = "")
{
    $db = \Config\Database::connect();
    $builder = $this->db->table('gss_bookings A');
    $builder->select('A.reference_two, G.associate_name as reference_two_name, A.reference, A.booking_id, A.customer_name, A.created_at as a_created_at, A.booking_status, B.tsv, B.booking_date1, B.project_id, B.site_number, B.dimension, B.shared_between_executive, B.shared_executive_percentage, B.shared_executive_amount, C.broker_id, C.associate_name, E.agreement_date, E.registration_date, D.project_id, D.project_name, F.incentive_status, C.incentive_percent, C.incentive_rupees, B.selected_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_brokers G', 'G.broker_id = A.reference_two', '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->join('gss_incentive_reports H', 'H.booking_id = A.booking_id');
    $builder->where('A.booking_status !=', 'ENQUIRED');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->where('H.insentive_perc_both', 'Both');
    $builder->where('H.delete_status', 'ACTIVE');
    $builder->where('B.both_status !=', 'both');
    $builder->groupBy('A.booking_id');

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

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

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

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

$final_array = [];
    foreach ($result as $res) {
        $incentiveQuery = $this->db->table('gss_incentive_reports A');
        $incentiveQuery->where('A.booking_id', $res->booking_id);
        $incentiveQuery->where('A.delete_status', 'ACTIVE');
        $incentiveQuery->where('A.insentive_perc_both', 'Both');
        $incentiveResult = $incentiveQuery->get()->getRow();

if ($incentiveResult) {
            $data = [
                'total_sqft'         => $incentiveResult->total_sqft,
                'rate_per_sqft'      => $incentiveResult->rate_per_sqft,
                'total_amount_fixed' => $incentiveResult->total_amount_fixed,
                'incentive_on_agmnt' => $incentiveResult->incentive_on_agmnt,
                'incentive_on_regn'  => $incentiveResult->incentive_on_regn,
                'insentive_perc'     => $incentiveResult->insentive_perc,
                'amount_tobe_paid'   => $incentiveResult->balance,
                'balance'            => $incentiveResult->balance,
                'source'             => $incentiveResult->source,
                'tsv'                => $res->tsv,
                'booking_id'         => $res->booking_id,
                'booking_date'       => date('d-m-Y', strtotime($res->booking_date1)),
                'booking_status'     => $res->booking_status
            ];

// Add Web Portal info
            $webportalQuery = $this->db->table('gss_webportals B');
            $webportalQuery->join('gss_bookings A', 'B.portal_id = A.webportal');
            $webportalQuery->where('A.booking_id', $res->booking_id);
            $webportalResult = $webportalQuery->get()->getRow();

$data["webportal"] = $webportalResult ? $webportalResult->webportal : "";

// Handle cancellations
            if (in_array($res->booking_status, ['CANCELLED', 'CANCELLED PAYMENT'])) {
                $data['cancelled'] = "Cancelled";
            }

$final_array[] = $data;
        }
    }

return $final_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();
}

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

// Building the main query
    $builder = $db->table('gss_booking_details A');
    $builder->select('A.*, A.booking_id as a_booking_id, B.*, C.associate_name, C.broker_id, C.*, D.project_name, E.agreement_date, E.registration_date, F.incentive_status');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_brokers C', 'C.broker_id = A.shared_between_logistic');
    $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 = A.site_number');
    $builder->where('A.shared_between_logistic', $logistic);
    $builder->where('A.selected_incentive_status !=', 'YES');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->groupBy('A.booking_id');

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

// If booking status is CANCELLED, REFUNDED, or REFUND_PENDING, get cancellation details
        if ($res->booking_status == 'CANCELLED' || $res->booking_status == 'REFUNDED' || $res->booking_status == 'REFUND_PENDING') {
            $builder = $db->table('gss_cancellations');
            $builder->where('delete_status', 'ACTIVE');
            $builder->where('booking_id', $res->booking_id);
            $query1 = $builder->get();
            $result1 = $query1->getRow();

if ($result1) {
                $data['cancelled_date'] = date('d-m-Y', strtotime($result1->cancellation_date));
            } else {
                $data['cancelled_date'] = "";
            }
        } else {
            $data['cancelled_date'] = "";
        }

// Check for agreement date and set default if empty
        $data['agreement_date'] = !empty($res->agreement_date) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";

// Check for registration date
        $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));

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

// Get associate name based on reference
        $builder = $db->table('gss_brokers A');
        $builder->where('broker_id', $res->reference);
        $query = $builder->get();
        $ass_name = $query->getRow();
        $data['associate_name'] = $ass_name ? $ass_name->associate_name : "";

$data['incentive_status'] = $res->incentive_status;
        $data['incentive_percent'] = !empty($res->incentive_percent) ? $res->incentive_percent : "0";
        $data['incentive_rupees'] = !empty($res->incentive_rupees) ? $res->incentive_rupees : "0";

// Get shared logistic details
        if ($res->shared_between_logistic) {
            $shared_id = $res->shared_between_logistic;
            $builder = $db->table('gss_brokers');
            $builder->where('broker_id', $shared_id);
            $query = $builder->get();
            $result = $query->getRow();
            $data['shared_between_logistic'] = $result ? $result->associate_name : "";
        } else {
            $data['shared_between_logistic'] = "";
        }

// Set shared logistic amount if available, else set incentive rupees
        $data['shared_logistic_amount'] = !empty($res->shared_logistic_amount) ? $res->shared_logistic_amount : $res->incentive_rupees;

array_push($array, $data);
    }

return $array;
}

public function get_selected_logistic_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    $array = [];

// Fetch logistic incentives for given executive
    $query = $db->table('gss_logistic_incentives A')
                ->where('A.broker_id', $executive)
                ->where('A.delete_status', 'ACTIVE')
                ->get();
    
    $result = $query->getResult();

foreach ($result as $res) {
        $data['id'] = $res->id;
        $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 => $project_id) {
                $site_number = $site_numbers_exploded[$key];

// Fetch booking details
                $query1 = $db->table('gss_booking_details A')
                    ->select('A.*, B.*, B.booking_id as b_booking_id, C.broker_id, C.associate_name, 
                              E.agreement_date, E.registration_date, D.project_id, D.project_name, 
                              F.log_incentive_status')
                    ->join('gss_bookings B', 'B.booking_id = A.booking_id')
                    ->join('gss_brokers C', 'C.broker_id = B.reference', 'left')
                    ->join('gss_new_projects D', 'D.project_id = A.project_id')
                    ->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left')
                    ->join('gss_new_sites F', 'F.project_id = A.project_id AND F.site_number = A.site_number')
                    ->where('A.project_id', $project_id)
                    ->where('A.site_number', $site_number)
                    ->where('A.delete_status', 'ACTIVE')
                    ->where('B.booking_status', 'BOOKED')
                    ->where('F.log_incentive_status', 'YES')
                    ->groupBy('A.booking_id')
                    ->get();

$result1 = $query1->getResult();

foreach ($result1 as $res) {
                    $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['shared_between_logistic'] = $res->shared_between_logistic;
                    $data['selected_incentive_status'] = $res->selected_incentive_status;
                    $data['agreement_date'] = (!empty($res->agreement_date)) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";
                    $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));
                    $data['associate_name'] = $res->associate_name;
                    $data['log_incentive_status'] = $res->log_incentive_status;

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

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

$array[] = $data;
                }
            }
        }
    }
    
    return $array;  // Just return the data

}

public function get_logistic_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    $array = [];

// Fetch logistic incentives data
    $query = $db->table('gss_bookings A')
        ->select('A.reference, A.booking_id, A.customer_name, A.created_at as a_created_at, A.booking_status, 
                  B.booking_date1, B.project_id, B.site_number, B.dimension, B.shared_between_logistic, B.shared_logistic_amount, 
                  C.broker_id, C.associate_name, C.incentive_target_rs, 
                  E.agreement_date, E.registration_date, 
                  D.project_id, D.project_name, F.log_incentive_status')
        ->join('gss_booking_details B', 'B.booking_id = A.booking_id')
        ->join('gss_brokers C', 'C.broker_id = A.logistics', 'left')
        ->join('gss_new_projects D', 'D.project_id = B.project_id')
        ->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left')
        ->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number')
        ->where('A.booking_status !=', 'ENQUIRED')
        ->where('A.delete_status', 'ACTIVE')
        ->where('B.delete_status', 'ACTIVE')
        ->where('D.delete_status', 'ACTIVE')
        ->groupBy('A.booking_id');

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

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

foreach ($result as $res) {
        $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['broker_id'] = $res->broker_id;
        $data['log_incentive_status'] = $res->log_incentive_status;
        $data['incentive_target_rs'] = $res->incentive_target_rs;

// Handle agreement date
        $data['agreement_date'] = (!empty($res->agreement_date)) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000";

// Handle registration date
        $data['registration_date'] = ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date));

// Fetch cancellation date if applicable
        if (in_array($res->booking_status, ['CANCELLED', 'REFUNDED', 'REFUND_PENDING'])) {
            $cancel_query = $db->table('gss_cancellations')
                ->select('cancellation_date')
                ->where('delete_status', 'ACTIVE')
                ->where('booking_id', $res->booking_id)
                ->get()
                ->getRow();
            
            $data['cancelled_date'] = (!empty($cancel_query)) ? date('d-m-Y', strtotime($cancel_query->cancellation_date)) : "";
        } else {
            $data['cancelled_date'] = "";
        }

$data['executive'] = $executive_query->associate_name ?? "";
        } else {
            $data['executive'] = "";
        }

// Fetch shared logistic name
        if (!empty($res->shared_between_logistic)) {
            $shared_query = $db->table('gss_brokers')
                ->select('associate_name')
                ->where('broker_id', $res->shared_between_logistic)
                ->get()
                ->getRow();
            
            $data['shared_between_logistic'] = $shared_query->associate_name ?? "";
        } else {
            $data['shared_between_logistic'] = "";
        }

// Calculate shared logistic amount
        $data['shared_logistic_amount'] = (!empty($res->shared_logistic_amount)) ? $res->shared_logistic_amount : $res->incentive_target_rs;

$array[] = $data;
    }

return $array;  // Just return the data

}

public function get_pending_logistic_incentives($from_date, $to_date, $executive)
{
    $db = \Config\Database::connect();
    $array = [];

// Fetch pending logistic incentives
    $logisticIncentives = $db->table('gss_logistic_incentives A')
        ->select('A.*')
        ->where('A.delete_status', 'ACTIVE')
        ->where('A.broker_id', $executive)
        ->get()
        ->getResult();

foreach ($logisticIncentives as $ress) {
        $query = $db->table('gss_bookings A')
            ->select('A.reference, A.booking_id, A.customer_name, A.created_at as a_created_at, A.booking_status, 
                      B.booking_date1, B.project_id, B.site_number, B.dimension, B.shared_between_logistic, B.shared_logistic_amount, 
                      C.broker_id, C.associate_name, C.incentive_target_rs, 
                      E.agreement_date, E.registration_date, 
                      D.project_id, D.project_name, F.log_incentive_status')
            ->join('gss_booking_details B', 'B.booking_id = A.booking_id')
            ->join('gss_brokers C', 'C.broker_id = A.logistics', 'left')
            ->join('gss_new_projects D', 'D.project_id = B.project_id')
            ->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left')
            ->join('gss_new_sites F', 'F.project_id = B.project_id AND F.site_number = B.site_number')
            ->where('A.booking_status !=', 'ENQUIRED')
            ->where('A.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('D.delete_status', 'ACTIVE')
            ->where('B.selected_incentive_status !=', 'YES')
            ->groupBy('A.booking_id');

if (!empty($from_date)) {
            $query->where('DATE(B.booking_date1) >=', $from_date);
        }
        if (!empty($to_date)) {
            $query->where('DATE(B.booking_date1) <=', $to_date);
        }
        if (!empty($executive)) {
            $query->where('A.logistics', $ress->broker_id);
        }

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

foreach ($results as $res) {
            $data = [
                'booking_id'     => $res->booking_id,
                'booking_date'   => date('d-m-Y', strtotime($res->booking_date1)),
                'booking_status' => $res->booking_status,
                'project_id'     => $res->project_id,
                'project_name'   => $res->project_name,
                'site_number'    => $res->site_number,
                'customer_name'  => $res->customer_name,
                'dimension'      => $res->dimension,
                'associate_name' => $res->associate_name,
                'broker_id'      => $res->broker_id,
                'log_incentive_status' => $res->log_incentive_status,
                'incentive_target_rs' => $res->incentive_target_rs,
                'agreement_date' => (!empty($res->agreement_date)) ? date('d-m-Y', strtotime($res->agreement_date)) : "00-00-0000",
                'registration_date' => ($res->registration_date == '0000-00-00') ? "00-00-0000" : date('d-m-Y', strtotime($res->registration_date)),
            ];

// Fetch cancellation date if applicable
            if (in_array($res->booking_status, ['CANCELLED', 'REFUNDED', 'REFUND_PENDING'])) {
                $cancel_query = $db->table('gss_cancellations')
                    ->select('cancellation_date')
                    ->where('delete_status', 'ACTIVE')
                    ->where('booking_id', $res->booking_id)
                    ->get()
                    ->getRow();
                
                $data['cancelled_date'] = (!empty($cancel_query)) ? date('d-m-Y', strtotime($cancel_query->cancellation_date)) : "";
            } else {
                $data['cancelled_date'] = "";
            }

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

// Fetch shared logistic name
            if (!empty($res->shared_between_logistic)) {
                $shared_query = $db->table('gss_brokers')
                    ->select('associate_name')
                    ->where('broker_id', $res->shared_between_logistic)
                    ->get()
                    ->getRow();
                
                $data['shared_between_logistic'] = $shared_query->associate_name ?? "";
            } else {
                $data['shared_between_logistic'] = "";
            }

// Calculate shared logistic amount
            $data['shared_logistic_amount'] = (!empty($res->shared_logistic_amount)) ? $res->shared_logistic_amount : $res->incentive_target_rs;

$array[] = $data;
        }
    }

// return $this->response->setJSON($array);
    return $array;  // Just return the data

}

public function get_where_result_tax_invoice_list($from_date, $to_date, $holder_name)
{
    $query = $this->db->table('gss_invoice_details A')
        ->select('A.*, A.delete_status as status, B.project_name')
        ->join('gss_new_projects B', 'B.project_id = A.project_id')
        ->where('A.invoice_type', 'Tax Invoice')
        ->where('A.delete_status', 'ACTIVE')
        ->orderBy('A.created_at', 'ASC');

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

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

if (!empty($holder_name)) {
        $query->where('A.bank_name', $holder_name);
    }

$results = $query->get()->getResultArray();
    $array = [];

foreach ($results as $val) {
        $data = [
            'id' => $val['id'],
            'project_name' => $val['project_name'],
            'tax_status' => ($val['tax_status'] == 1) ? 'Converted' : '',
            'site_numbers' => is_array(json_decode($val['site_numbers'], true)) 
            ? array_map('intval', json_decode($val['site_numbers'], true)) 
            : [],

'invoice_no' => $val['invoice_no'],
            'invoice_number' => $val['invoice_number'],
            'invoice_type' => $val['invoice_type'],
            'invoice_to' => $val['invoice_to'],
            'total_sqft' => $val['total_sqft'],
            'rate_per_sqft' => $val['rate_per_sqft'],
            'without_gst_total_amt' => $val['without_gst_total_amt'],
            'sgst' => $val['sgst'],
            'cgst' => $val['cgst'],
            'total_cgst' => $val['total_cgst'],
            'total_sgst' => $val['total_sgst'],
            'with_gst_total_amt' => $val['with_gst_total_amt'],
            'bank_name' => $val['bank_name'],
            'created_at' => $val['created_at'],
            'delete_status' => ($val['status'] == 'INACTIVE') ? 'DELETED' : '',
        ];

$array[] = $data;
    }

return $array; // ✅ Now, the full dataset is returned
}

public function get_where_result_proforma_invoice_list($from_date, $to_date, $holder_name)
{
    $query = $this->db->table('gss_invoice_details A')
        ->select('A.*, A.delete_status as status, B.project_name')
        ->join('gss_new_projects B', 'B.project_id = A.project_id')
        ->where('A.invoice_type', 'Proforma Invoice')
        ->where('A.delete_status', 'ACTIVE')
        ->orderBy('A.created_at', 'ASC');

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

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

if (!empty($holder_name)) {
        $query->where('A.bank_name', $holder_name);
    }

$results = $query->get()->getResultArray();
    $array = [];

foreach ($results as $val) {
        $data = [
            'id' => $val['id'],
            'project_name' => $val['project_name'],
            'site_numbers' => is_array(json_decode($val['site_numbers'], true)) 
                ? array_map('intval', json_decode($val['site_numbers'], true)) 
                : [],  
            'invoice_no' => $val['invoice_no'],
            'invoice_number' => $val['invoice_number'],
            'invoice_type' => $val['invoice_type'],
            'invoice_to' => $val['invoice_to'],
            'total_sqft' => $val['total_sqft'],
            'rate_per_sqft' => $val['rate_per_sqft'],
            'without_gst_total_amt' => $val['without_gst_total_amt'],
            'sgst' => $val['sgst'],
            'cgst' => $val['cgst'],
            'total_cgst' => $val['total_cgst'],
            'total_sgst' => $val['total_sgst'],
            'with_gst_total_amt' => $val['with_gst_total_amt'],
            'bank_name' => $val['bank_name'],
            'created_at' => $val['created_at'],
            'delete_status' => ($val['status'] === 'INACTIVE') ? 'DELETED' : '',
        ];

$array[] = $data;
    }

return $array;
}

public function get_tax_invoice_details($where1)
{
    $db = \Config\Database::connect();

$query = $db->table('gss_invoice_details A')
        ->select('A.*, B.project_name')
        ->join('gss_new_projects B', 'B.project_id = A.project_id')
        ->where('A.invoice_type', 'Tax Invoice')
        ->where('A.delete_status', 'ACTIVE')
        ->where($where1)
        ->get();

$res = $query->getResult();

// Use array_map to format results
    return array_map(function ($val) {
        return [
            'id'                  => $val->id,
            'project_name'        => $val->project_name,
            'project_id'          => $val->project_id,
            'site_numbers'        => $val->site_numbers,
            'invoice_no'          => $val->invoice_no,
            'invoice_number'      => $val->invoice_number,
            'invoice_type'        => $val->invoice_type,
            'invoice_to'          => $val->invoice_to,
            'total_sqft'          => $val->total_sqft,
            'rate_per_sqft'       => $val->rate_per_sqft,
            'without_gst_total_amt' => $val->without_gst_total_amt,
            'sgst'                => $val->sgst,
            'cgst'                => $val->cgst,
            'total_cgst'          => $val->total_cgst,
            'total_sgst'          => $val->total_sgst,
            'with_gst_total_amt'  => $val->with_gst_total_amt,
            'bank_name'           => $val->bank_name,
            'created_at'          => $val->created_at ? date('Y-m-d', strtotime($val->created_at)) : null,
        ];
    }, $res);
}

// public function get_where_row_for_print($id)
// {        
//     $db = \Config\Database::connect();

//     $query = $db->table('gss_invoice_details A')
//         ->select('A.*, A.id as in_id, B.project_name, C.*')
//         ->join('gss_new_projects B', 'B.project_id = A.project_id')
//         ->join('gss_bank_details C', 'C.holder_name = A.bank_name')
//         ->where('A.id', $id)
//         ->where('A.delete_status', 'ACTIVE')
//         ->get();

//     return $query->getRow(); // Returns a single row object
// }

public function get_where_result_site_ids_edit($project_id, $inv)
{
    $db = \Config\Database::connect();
    $gss_model = new Gss_model();

$query = $db->query("
        SELECT 
            A.booking_id, 
            A.project_id, 
            B.project_name, 
            C.site_number, 
            C.dimension, 
            C.detail_id, 
            D.registration_date, 
            E.site_id, 
            C.tsv, 
            A.booking_status, 
            E.billing_status
        FROM 
            gss_new_sites E
        JOIN gss_new_projects B ON E.project_id = B.project_id
        JOIN gss_bookings A ON A.project_id = B.project_id
        JOIN gss_booking_details C ON C.booking_id = A.booking_id
        JOIN gss_plot_payments D ON D.booking_id = A.booking_id
        WHERE 
            E.project_id = ? 
            AND A.delete_status = 'ACTIVE'
            AND B.delete_status = 'ACTIVE'
            AND E.delete_status = 'ACTIVE'
            AND D.registration_date > '0000-00-00'
            AND C.delete_status = 'ACTIVE'
        GROUP BY 
            C.site_number
        ORDER BY 
            D.registration_date DESC
    ", [$project_id]);

$results = $query->getResult();
    $array = [];

foreach ($results as $val) {
        $tables = 'gss_new_sites';
        $condition = ['project_id' => $val->project_id, 'site_number' => $val->site_number];
        $get_details = $gss_model->get_where_row($tables, $condition);

if ((!empty($get_details) && ($get_details->billing_status == '')) || ($get_details->invoice_no == $inv)) {
            $data = [
                'project_name'      => $val->project_name,
                'site_number'       => $val->site_number,
                'dimension'         => $val->dimension,
                'project_id'        => $val->project_id,
                'registration_date' => $val->registration_date,
                'tsv'               => $val->tsv,
                'billing_status'    => $val->billing_status,
                'site_id'           => $val->site_id,
                'booking_status'    => $val->booking_status
            ];

// Get registration amount
            $regn_result = $db->table('gss_plot_payments A')
                ->select('A.registration_amount')
                ->where('A.delete_status', 'ACTIVE')
                ->where('A.booking_id', $val->booking_id)
                ->where('A.detail_id', $val->detail_id)
                ->get()
                ->getRow();

if ($regn_result) {
                $data['booking_status'] = ($regn_result->registration_amount != 0) ? 'REGISTERED' : $val->booking_status;
            }

$array[] = $data;
        }
    }}

public function get_tax_id($holder)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_invoice_details');

$builder->selectMax('invoice_number', 'invoice_number'); // Ensure aliasing
    $builder->where('invoice_type', 'Tax Invoice');
    $builder->where('bank_name', $holder);
    $builder->where('delete_status', 'ACTIVE');

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

// 🔍 Debugging: Check result before returning
    if (!$result || empty($result->invoice_number)) {
        return null; // ❌ Prevents errors when accessing properties
    }

return $result;
}

public function get_proforma_invoice_details($where1)
{
    $db = \Config\Database::connect(); // CI4 Database Connection
    $builder = $db->table('gss_invoice_details A');
    
    $builder->select('A.*, B.project_name');
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->where('A.invoice_type', 'Proforma Invoice');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where($where1); // Dynamic conditions

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

$array = [];

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

$data = [
            'id' => $val->id,
            'project_name' => $val->project_name,
            'project_id' => $val->project_id,
            'site_numbers' => $val->site_numbers,
            'invoice_no' => $val->invoice_no,
            'invoice_number' => $val->invoice_number,
            'invoice_type' => $val->invoice_type,
            'invoice_to' => $val->invoice_to,
            'total_sqft' => $val->total_sqft,
            'rate_per_sqft' => $val->rate_per_sqft,
            'without_gst_total_amt' => $val->without_gst_total_amt,
            'sgst' => $val->sgst,
            'cgst' => $val->cgst,
            'total_cgst' => $val->total_cgst,
            'total_sgst' => $val->total_sgst,
            'with_gst_total_amt' => $val->with_gst_total_amt,
            'bank_name' => $val->bank_name,
            'created_at' => $c_date
        ];

$array[] = $data;
    }

return $array;
}

public function get_dob_list()
    {
        $query = $this->db->table('gss_bookings A')
                          ->select('A.customer_name, A.dob, A.mobile1, A.mobile2, A.email, B.project_name, A.booking_id')
                          ->join('gss_new_projects B', 'B.project_id = A.project_id')
                          //->where('A.dob !=', '0000-00-00')
                          ->where('(A.booking_status = "BOOKED" OR A.booking_status = "ENQUIRED")')
                          ->where('A.delete_status', 'ACTIVE')
                          ->where('B.delete_status', 'ACTIVE')
                          ->get()
                          ->getResultArray(); // Convert to an array

// Process data
        $array = [];
        foreach ($query as $val) {
            $array[] = [
                'booking_id'    => $val['booking_id'],
                'mobile'        => $val['mobile1'],
                'mobile2'       => $val['mobile2'],
                'customer_name' => $val['customer_name'],
                'dob'           => date('m-d', strtotime($val['dob']))
            ];
        }

return $array;
    }

public function user_site_booking_detail($booking_id)
{
    $builder = $this->db->table('gss_bookings');
    $builder->select('booking_id, booking_type, booking_status, customer_name, relation, father_or_husband, email, alternative_email, mobile1, office_number, dob, doa, address, idproof_name, nominee_name, idproof_number, nominee_contact, logistics, reference, associate, source_type, subassociate');
    $builder->where('delete_status', 'ACTIVE');
    $builder->where('booking_id', $booking_id);
    $builder->where('status', 1);
    
    $result = $builder->get()->getRow();

if (!$result) {
        return null; // Return null if no record is found
    }

$data = [
        'booking_id'       => $result->booking_id,
        'booking_type'     => $result->booking_type,
        'booking_status'   => $result->booking_status,
        'customer_name'    => $result->customer_name,
        'relation'         => $result->relation,
        'father_or_husband'=> $result->father_or_husband,
        'email'            => $result->email,
        'alternative_email'=> $result->alternative_email,
        'mobile'           => $result->mobile1,
        'office_number'    => $result->office_number,
        'dob'              => $result->dob,
        'doa'              => $result->doa,
        'address'          => $result->address,
        'idproof_name'     => $result->idproof_name,
        'nominee_name'     => $result->nominee_name,
        'idproof_number'   => $result->idproof_number,
        'nominee_contact'  => $result->nominee_contact,
        'logistics'        => $result->logistics,
        'reference'        => '',
        'associate'        => $result->associate,
        'source_type'      => $result->source_type,
        'subassociate'     => $result->subassociate
    ];

// Fetch Reference Name if applicable
    if ($result->reference != 0) {
        $builder = $this->db->table('gss_brokers A');
        $builder->select('A.associate_name as reference');
        $builder->join('gss_bookings B', 'B.reference = A.broker_id');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('A.broker_id', $result->reference);

$reference_result = $builder->get()->getRow();
        if ($reference_result) {
            $data['reference'] = $reference_result->reference;
        }
    }

// print_r($data);die();
    return $data;
    
    
    
}
public function get_doa_list()
{
    $builder = $this->db->table('gss_bookings A');
    $builder->select('A.customer_name, A.doa, A.mobile1, A.mobile2, A.email, B.project_name, A.booking_id');
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->where('A.doa !=', '0000-00-00');
    $builder->where('(A.booking_status = "BOOKED" OR A.booking_status = "ENQUIRED")');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');

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

$array = [];

foreach ($result as $val) {
        $array[] = [
            'booking_id'    => $val->booking_id,
            'mobile'        => $val->mobile1,
            'mobile2'       => $val->mobile2,
            'customer_name' => $val->customer_name,
            'doa'           => date('m-d', strtotime($val->doa))
        ];
    }

return $array;
}

public function get_loan_details($booking_id)
{
    $db = \Config\Database::connect();
    
    $builder = $db->table('gss_loans A');
    $builder->select('A.*, B.*, C.*, D.*, A.sale_agreement_amount as l_sale_agreement_amount, A.bank_name as l_bank_name, A.cheque_no as l_cheque_no, A.cheque_date as l_cheque_date');
    $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->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);

$query = $builder->get();
    return $query->getRow(); // CI4 version of CI3's $result->row()
}

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

$query = $db->table('gss_commission_notifications A')
                ->select([
                    'A.*', 'B.*', 'C.*', 'D.*', 'E.*', 'F.*',
                    'A.delete_status AS ad_s', 'B.delete_status AS b_ds',
                    'C.delete_status AS cd_s', 'D.delete_status AS dd_s', 'E.delete_status AS ed_s',
                    'E.registration_date', 'E.agreement_date', 'B.associate',
                    'F.associate_name', 'A.status AS statuss'
                ])
                ->join('gss_bookings B', 'B.booking_id = A.booking_id', 'left')
                ->join('gss_new_projects C', 'C.project_id = B.project_id', 'left')
                ->join('gss_booking_details D', 'D.detail_id = A.detail_id', 'left')
                ->join('gss_plot_payments E', 'E.booking_id = A.booking_id', 'left')
                ->join('gss_brokers F', 'F.broker_id = B.associate', 'left')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.delete_status', 'ACTIVE')
                ->where('C.delete_status', 'ACTIVE')
                ->where('D.delete_status', 'ACTIVE')
                ->where('E.delete_status', 'ACTIVE')
                ->where('F.delete_status', 'ACTIVE')
                ->where('A.status', 'PENDING')
                ->groupStart()  // Start OR condition block
                    ->where('A.commission_agreement', 'Available')
                    ->orWhere('A.commission_registration', 'Available')
                ->groupEnd()    // End OR condition block
                ->where('B.booking_status', 'BOOKED')
                ->groupBy('E.booking_id')
                ->orderBy('A.notification_id', 'DESC')
                ->get();

return $query->getResultArray();
}

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

$query = $db->table('gss_management A')
                ->select([
                    'A.*', 'B.*', 'C.*', 'D.*', 'E.*',
                    'B.mobile1 AS customer_contact'
                ])
                ->join('gss_bookings B', 'B.booking_id = A.booking_id', 'left')
                ->join('gss_booking_details C', 'C.detail_id = A.detail_id', 'left')
                ->join('gss_new_projects D', 'D.project_id = B.project_id', 'left')
                ->join('gss_brokers E', 'E.broker_id = B.associate', 'left')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.delete_status', 'ACTIVE')
                ->where('C.delete_status', 'ACTIVE')
                ->where('D.delete_status', 'ACTIVE')
                ->groupBy('A.booking_id')
                ->orderBy('A.management_id', 'DESC')
                ->get();

return $query->getResultArray();
}

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

$query = $db->table('gss_reception A')
                ->select([
                    'A.reception_id', 
                    'A.client_name AS customer_name', 
                    'A.client_khatha', 
                    'A.dispatched_type', 
                    'A.dispatched_date', 
                    'B.project_name', 
                    'A.site_number AS site_number'
                ])
                ->join('gss_new_projects B', 'B.project_id = A.project_id', 'left')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.delete_status', 'ACTIVE')
                ->orderBy('A.site_number', 'ASC')
                ->get();

return $query->getResultArray();
}

public function get_loans_total()
{
    $builder = $this->db->table('gss_bookings A');
    $builder->select('A.*, B.*, A.created_at as booked_on, C.*');
    $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->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_status', 'BOOKED');
    $builder->orderBy('A.booking_id', 'DESC');
    $result = $builder->get()->getResult();

$array = [];

foreach ($result as $val) {
        $booking_id = $val->booking_id;
        $data['booking_id'] = $val->booking_id;
        $data['customer_name'] = $val->customer_name;
        $data['address'] = $val->address;
        $data['customer_mobile1'] = $val->mobile1;
        $data['customer_mobile2'] = $val->mobile2;
        $data['customer_email'] = $val->email;
        $data['project_name'] = $val->project_name;
        $data['site_number'] = $val->site_number;
        $data['dimension'] = $val->dimension;
        $data['booked_on'] = $val->booked_on;
        $data['booking_status'] = $val->booking_status;
        $data['agreement_due_date'] = $val->sales_agreement_due_date;
        $data['source_type'] = $val->source_type;

// agreement executed on
        $aggr_sql = "SELECT agreement_date FROM gss_plot_payments WHERE delete_status = 'ACTIVE' AND booking_id = '$booking_id' AND agreement_date != '0000-00-00'";
        $result2 = $this->db->query($aggr_sql)->getRow();
        $data['agreement_executed_on'] = $result2 ? $result2->agreement_date : "";

// web portal
        if ($val->webportal == 0) {
            $data['web_portal'] = "";
        } else {
            $web = $this->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', $val->webportal)
                ->get()->getRow();
            $data['web_portal'] = $web ? $web->portal : "";
        }

// logistics
        if ($val->logistics == 0) {
            $data['logistics'] = "";
        } else {
            $log = $this->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', $val->logistics)
                ->get()->getRow();
            $data['logistics'] = $log ? $log->associate_name : "";
        }

// reference
        if ($val->reference == 0) {
            $data['reference'] = "";
        } else {
            $ref = $this->db->table('gss_brokers A')
                ->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)
                ->get()->getRow();
            $data['reference'] = $ref ? $ref->reference : "";
        }

// associate
        if ($val->associate == 0) {
            $data['associate'] = "";
        } else {
            $assoc = $this->db->table('gss_brokers A')
                ->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)
                ->get()->getRow();
            $data['associate'] = $assoc ? $assoc->associate : "";
        }

$array[] = $data;
    }

return $array;
}

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

return $db->table('gss_bookings')
        ->where('dob !=', '0000-00-00')
        ->where('booking_status', 'BOOKED')
        ->where('delete_status', 'ACTIVE')
        ->countAllResults();
}

public function get_doa_count()
{
        $db = \Config\Database::connect();
    $builder = $this->db->table('gss_bookings');
    $builder->where('doa !=', '0000-00-00');
    $builder->where('booking_status', 'BOOKED');
    $builder->where('delete_status', 'ACTIVE');

return $builder->countAllResults();
}

public function datewise_sales_for_chart($from_date, $to_date)
{
    // Get all active projects
    $projects = $this->db->query("
        SELECT project_name, project_id 
        FROM gss_new_projects 
        WHERE delete_status = 'ACTIVE'
    ")->getResult();

$result_data = [];

foreach ($projects as $project) {
        $project_id = $project->project_id;

// Use Query Builder properly
        $builder = $this->db->table('gss_booking_details');
        $builder->selectSum('dimension');
        $builder->where('project_id', $project_id);
        $builder->where('delete_status', 'ACTIVE');

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

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

$row = $builder->get()->getRow();
        $dimension_total = $row ? $row->dimension : 0;

$result_data[] = [
            'project'   => $project->project_name,
            'dimension' => (float) $dimension_total
        ];
    }

return $result_data;
}

public function get_follow_up_admin_reminders($admin_id)
{
    $builder = $this->db->table('gss_comment A');
    $builder->select('A.admin_reminder_date, A.notification_status');
    $builder->join('gss_follow_ups B', 'B.id = A.conversation_id');
    $builder->where('A.user_id', $admin_id);

$query = $builder->get();
    return $query->getResult(); // ✅ Correct method for CI4
}

public function get_follow_up_user_replies($current_date)
{
    $builder = $this->db->table('gss_reply A');
    $builder->select('A.user_reply, A.reply_date');
    $builder->where('A.reply_date', $current_date);
    $builder->orderBy('A.id', 'DESC');
    
    $query = $builder->get();
    return $query->getResult(); // ✅ CI4 compatible
}

public function get_total_follow_up_replies($current_date)
{
    $builder = $this->db->table('gss_reply A');
    $builder->select('A.user_reply, A.reply_date');
    $builder->where('A.reply_date', $current_date);
    $builder->orderBy('A.id', 'DESC');

$query = $builder->get();
    return $query->getNumRows(); // CI4 equivalent of num_rows()
}

public function get_status_notifications()
{
    $session      = session();
    $admin_id     = $session->get('admin_id');
    $user_type_id = $session->get('user_type_id');

$builder = $this->db->table('gss_status_notifications');

if ($user_type_id == 1) {
        $builder->select('*')
                ->where('delete_status', 'ACTIVE')
                ->where('admin', 'NOT_VIEWED')
                ->orderBy('id', 'DESC');

} elseif ($user_type_id == 4) {
        $builder->select('*')
                ->where('delete_status', 'ACTIVE')
                ->where('management', 'NOT_VIEWED')
                ->where('created_by !=', $admin_id)
                ->orderBy('id', 'DESC');

} elseif ($user_type_id == 5) {
        $builder = $this->db->table('gss_status_notifications A');
        $builder->select('*')
                ->join('gss_new_projects B', 'B.project_id = A.client_project_id')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.delete_status', 'ACTIVE')
                ->where('A.documentation', 'NOT_VIEWED')
                ->where('A.created_by !=', $admin_id)
                ->where('B.handled_by', $admin_id)
                ->orderBy('A.id', 'DESC');

} elseif ($user_type_id == 6) {
        $builder = $this->db->table('gss_status_notifications A');
        $builder->select('*')
                ->join('gss_status_conversation B', 'B.id = A.conversation_id', 'left')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.source_type', 'loan')
                ->where('A.loan', 'NOT_VIEWED')
                ->where('A.created_by !=', $admin_id)
                ->orderBy('A.id', 'DESC');
    } else {
        return [];
    }

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

public function updateData($table, $where, $data)
{
    $builder = $this->db->table($table);
    $builder->where($where);
    $builder->update($data);
    return $this->db->affectedRows();
}

public function get_conversation_replies($bookingId)
{
    $session = session();
    $loginId = $session->get('admin_id');

// Fetch conversation list
    $builder = $this->db->table('gss_status_conversation AS A');
    $builder->select('A.*, B.user_id, B.username');
    $builder->join('gss_login AS B', 'B.user_id = A.created_by');
    $builder->where('A.booking_id', $bookingId);
    $builder->where('A.delete_status', 'ACTIVE');
    $res = $builder->get()->getResult();

$lastConvKey = array_key_last($res);

$conversations = [];

foreach ($res as $key => $value) {
        $convAvail = ($key === $lastConvKey) ? 'yes' : 'no';

// Access rights check
        $convBuilder = $this->db->table('gss_status_conversation AS A');
        $convBuilder->select('*')->where('A.id', $value->id);

if ($value->created_by == $loginId && $value->selected_department === 'self') {
            $convBuilder->where('A.selected_department', 'self');
            $convBuilder->where('A.created_by', $loginId);
        } else {
            $convBuilder->where('A.selected_department', 'all');
        }

$getRes = $convBuilder->get()->getRow();

$conversationText = (!empty($getRes)) ? $value->conversation : "";

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

$replyRes = $replyBuilder->get()->getResult();

$replyAvail = (!empty($replyRes)) ? 'yes' : 'no';

$conversations[] = [
            'conv_id'              => $value->id,
            'booking_id'           => $value->booking_id,
            'created_by'           => $value->created_by,
            'created_at'           => $value->created_at,
            'username'             => $value->username,
            'source_type'          => $value->source_type,
            'user_type_id'         => $value->user_type_id,
            'reminder_date'        => $value->reminder_date,
            'selected_department'  => $value->selected_department,
            'conversation'         => $conversationText,
            'conv_avail'           => $convAvail,
            'reply_avail'          => $replyAvail,
            'reply'                => $replyRes
        ];
    }

return $conversations;
}

public function get_mail(string $table, array $where): array
{
    $db = \Config\Database::connect(); // CI4 way to connect DB
    $builder = $db->table($table);
    $builder->where($where);
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

foreach ($result as $row) {
        $array[] = [
            'po_no'              => $row->po_no,
            'vendor'             => $row->vendor,
            'contact_person'     => $row->contact_person,
            'contact_number'     => $row->contact_number,
            'email'              => $row->email,
            'date'               => $row->date,
            'currency'           => $row->currency,
            'vendor_address'     => $row->vendor_address,
            'product_name'       => $row->product_name,
            // 'description'        => $row->description,
            'indent'             => $row->indent,
            'project_name'       => $row->project_name,
            'purpose_no'         => $row->purpose_no,
            // 'quentity'           => $row->quentity,
            // 'unit_price'         => $row->unit_price,
            'inr_value'          => $row->inr_value,
            'sgst'               => $row->sgst,
            'cgst'               => $row->cgst,
            'igst'               => $row->igst,
            'total_value'        => $row->total_value,
            'total_amt'          => $row->total_amt,
            'discount'           => $row->discount,
            'grand_total'        => $row->grand_total,
            'material_delivery'  => $row->material_delivery,
            'gst_no'             => $row->gst_no,
            'terms_of_payment'   => $row->terms_of_payment,
            'bank_details'       => $row->bank_details,
            'ot_charges'         => $row->ot_charges,
            'ot_unit'            => $row->ot_unit,
            'ot_quantity'        => $row->ot_quantity,
            'ot_inr_value'       => $row->ot_inr_value,
            'ot_discount'        => $row->ot_discount,
            'ot_sgst'            => $row->ot_sgst,
            'ot_cgst'            => $row->ot_cgst,
            'ot_igst'            => $row->ot_igst,
            'ot_total_value'     => $row->ot_total_value,
            'note'               => $row->note,
        ];
    }

return $array;
}

public function civil_po_send_mail(string $table, array $where): array
{
    $db      = \Config\Database::connect();
    $builder = $db->table($table);
    $builder->select('*');
    $builder->where($where);
    $query = $builder->get();

$results = $query->getResult();
    $array = [];

foreach ($results as $row) {
        $data = [
            'po_no'              => $row->po_no,
            'vendor'             => $row->vendor,
            'contact_person'     => $row->contact_person,
            'contact_number'     => $row->contact_number,
            'email'              => $row->email,
            'date'               => $row->date,
            'currency'           => $row->currency,
            'vendor_address'     => $row->vendor_address,
            'product_name'       => $row->product_name,
            'indent'             => $row->indent,
            'project_name'       => $row->project_name,
            'purpose_no'         => $row->purpose_no,
            'cancel_status'      => $row->cancel_status,
            'quentity'           => $row->quentity,
            'unit_price'         => $row->unit_price,
            'inr_value'          => $row->inr_value,
            'sgst'               => $row->sgst,
            'cgst'               => $row->cgst,
            'igst'               => $row->igst,
            'total_value'        => $row->total_value,
            'total_amt'          => $row->total_amt,
            'discount'           => $row->discount,
            'grand_total'        => $row->grand_total,
            'material_delivery'  => $row->material_delivery,
            'gst_no'             => $row->gst_no,
            'terms_of_payment'   => $row->terms_of_payment,
            'bank_details'       => $row->bank_details,
            'ot_charges'         => $row->ot_charges,
            'ot_unit'            => $row->ot_unit,
            'ot_quantity'        => $row->ot_quantity,
            'ot_inr_value'       => $row->ot_inr_value,
            'ot_discount'        => $row->ot_discount,
            'ot_sgst'            => $row->ot_sgst,
            'ot_cgst'            => $row->ot_cgst,
            'ot_igst'            => $row->ot_igst,
            'ot_total_value'     => $row->ot_total_value,
            'note'               => $row->note,
            'remarks'            => $row->remarks,
            'otc_remarks'        => $row->otc_remarks,
        ];

$array[] = $data;
    }

return $array;
}
public function GetRowlog($keyword)
{
    return $this->db->table('gss_vendor_details')
        ->select('vendor_name')
        ->distinct()
        ->like('vendor_name', $keyword)
        ->where('delete_status', 'ACTIVE')
        ->orderBy('vendor_name', 'DESC')
        ->get()
        ->getResult();
}

public function get_alluser_details($current_date)
{
    return $this->db->table('gss_new_projects A')
        ->select('*')
        ->join('gss_booking_details C', 'C.project_id = A.project_id')
        ->join('gss_bookings D', 'D.booking_id = C.booking_id')
        ->where('C.updated_at', $current_date)
        ->get()
        ->getResult();
}
public function civil_wo_send_mail($table, $where)
{
    $db = \Config\Database::connect();
    $builder = $db->table($table);
    $builder->select('*');
    $builder->where($where);
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

$data['ot_inr_value'] = $row->ot_inr_value;
        $data['ot_discount'] = $row->ot_discount;
        $data['ot_sgst'] = $row->ot_sgst;
        $data['ot_cgst'] = $row->ot_cgst;
        $data['ot_igst'] = $row->ot_igst;
        $data['ot_total_value'] = $row->ot_total_value;
        $data['note'] = $row->note;
        $data['remarks'] = $row->remarks;
        $data['otc_remarks'] = $row->otc_remarks;

$array[] = $data;
    }

return $array;
}
public function get_mails($table, $where)
{
    $builder = $this->db->table($table);
    $builder->select('*');
    $builder->where($where);
    $query = $builder->get();
    $result = $query->getResult();

$array = [];

foreach ($result as $row) {
        $data['po_no']                 = $row->po_no;
        $data['vendor']                = $row->vendor;
        $data['contact_person']        = $row->contact_person;
        $data['contact_number']        = $row->contact_number;
        $data['email']                 = $row->email;
        $data['date']                  = $row->date;
        $data['currency']              = $row->currency;
        $data['vendor_address']        = $row->vendor_address;
        $data['product_name']          = $row->product_name;
        $data['description']           = $row->description;
        $data['indent']                = $row->indent;
        $data['project_name']          = $row->project_name;
        $data['purpose_no']            = $row->purpose_no;
        $data['quentity']              = $row->quentity;
        $data['unit_price']            = $row->unit_price;
        $data['inr_value']             = $row->inr_value;
        $data['sgst']                  = $row->sgst;
        $data['cgst']                  = $row->cgst;
        $data['igst']                  = $row->igst;
        $data['total_value']           = $row->total_value;
        $data['total_amt']             = $row->total_amt;
        $data['discount']              = $row->discount;
        $data['grand_total']           = $row->grand_total;
        $data['material_delivery']     = $row->material_delivery;
        $data['gst_no']                = $row->gst_no;
        $data['terms_of_payment']      = $row->terms_of_payment;
        $data['bank_details']          = $row->bank_details;
        $data['ot_charges']            = $row->ot_charges;
        $data['ot_unit']               = $row->ot_unit;
        $data['ot_quantity']           = $row->ot_quantity;
        $data['ot_inr_value']          = $row->ot_inr_value;
        $data['ot_discount']           = $row->ot_discount;
        $data['ot_sgst']               = $row->ot_sgst;
        $data['ot_cgst']               = $row->ot_cgst;
        $data['ot_igst']               = $row->ot_igst;
        $data['ot_total_value']        = $row->ot_total_value;
        $data['note']                  = $row->note;

$array[] = $data;
    }

return $array;
}

public function get_civil_vendors($keyword)
{
    $builder = $this->db->table('gss_civil_vendor_details');
    
    $builder->distinct()
            ->select('vendor_name')
            ->like('vendor_name', $keyword)
            ->where('delete_status', 'ACTIVE')
            ->orderBy('vendor_name', 'DESC');
    
    $query = $builder->get();
    
    return $query->getResult();
}

public function assoc_reports(string $type)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_brokers');

$builder->select('broker_id, type, associate_name, email, name_or_id, mobile, mobile2, address, pan_number, reference_details, adhar_number');
    $builder->where([
        'type' => $type,
        'delete_status' => 'ACTIVE'
    ]);
    $builder->orderBy('associate_name', 'ASC');

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

$array = [];

foreach ($results as $val) {
        $array[] = [
            'broker_id'         => $val->broker_id,
            'type'              => $val->type,
            'name_or_id'        => $val->name_or_id,
            'associate_name'    => $val->associate_name,
            'email'             => $val->email,
            'mobile'            => $val->mobile,
            'mobile2'           => $val->mobile2,
            'address'           => $val->address,
            'pan_number'        => $val->pan_number,
            'reference_details' => $val->reference_details,
            'adhar_number'      => $val->adhar_number,
        ];
    }

return $array;
}

public function get_all_result1_invoice_data_print_export(string $table, int $id): array
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_invoice_details A');

$builder->select('A.*, B.project_name');
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->where('A.id', $id);

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

$array = [];

foreach ($results as $val) {
        $createdDate = $val->created_at;
        $dateTime = new \DateTime($createdDate);
        $formattedDate = $dateTime->format('Y-m-d');

$array[] = [
            'id'                    => $val->id,
            'project_name'          => $val->project_name,
            'site_ids'              => $val->site_ids,
            'site_numbers'          => $val->site_numbers,
            'invoice_no'            => $val->invoice_no,
            'invoice_number'        => $val->invoice_number,
            'invoice_type'          => $val->invoice_type,
            'invoice_to'            => $val->invoice_to,
            'total_sqft'            => $val->total_sqft,
            'rate_per_sqft'         => $val->rate_per_sqft,
            'without_gst_total_amt' => $val->without_gst_total_amt,
            'sgst'                  => $val->sgst,
            'cgst'                  => $val->cgst,
            'total_cgst'            => $val->total_cgst,
            'total_sgst'            => $val->total_sgst,
            'with_gst_total_amt'    => $val->with_gst_total_amt,
            'bank_name'             => $val->bank_name,
            'created_at'            => $formattedDate,
        ];
    }

return $array;
}

public function address_list_print($p_project)
{
    $builder = $this->db->table('gss_bookings A');

$builder->select('
        A.customer_name,
        A.relation,
        A.father_or_husband,
        A.address,
        A.mobile1,
        A.office_number,
        A.project_id,
        A.booking_id,
        B.site_number,
        C.project_name
    ');

$builder->join('gss_booking_details B', 'B.booking_id = A.booking_id');
    $builder->join('gss_new_projects C', 'C.project_id = A.project_id');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.project_id', $p_project);
    $builder->orderBy('CAST(B.site_number AS UNSIGNED)', 'ASC'); // Sorted as number

$query = $builder->get();
    return $query->getResult(); // Or getResultArray() if you prefer arrays
}

public function  get_site_project_details($site_id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('gss_new_sites A');
    
    $builder->select('A.*, B.project_name')
            ->join('gss_new_projects B', 'B.project_id = A.project_id')
            ->where('A.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('A.site_id', $site_id);
    
    $query = $builder->get();
    $result = $query->getRow();

return $result ?: null; // Return null if no data found
}

public function get_all_reminders()
{
    $builder = $this->db->table('gss_bookings A')
                        ->select('A.*, B.*, C.*, D.*')
                        ->join('gss_new_projects B', 'B.project_id = A.project_id')
                        ->join('gss_booking_details C', 'C.booking_id = A.booking_id')
                        ->join('gss_booking_installments D', 'D.booking_detal_id = C.detail_id')
                        ->where('A.delete_status', 'ACTIVE')
                        ->where('A.booking_status', 'BOOKED')
                        ->where('B.delete_status', 'ACTIVE')
                        ->where('C.delete_status', 'ACTIVE')
                        ->where('D.delete_status', 'ACTIVE')
                        ->orderBy('A.booking_id', 'DESC');

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

$array = [];

foreach ($result as $val) {
        $data = [
            'booking_id'                 => $val->booking_id,
            'detail_id'                  => $val->detail_id,
            'project_name'               => $val->project_name,
            'site_number'                => $val->site_number,
            'customer_name'              => $val->customer_name,
            'mobile'                     => !empty($val->mobile2) ? $val->mobile1 . "," . $val->mobile2 : $val->mobile1,
            'reg_date_type'              => $val->reg_date_type,
            'sales_agreement_due_date'   => $val->sales_agreement_due_date,
            'registration_due_date'      => $val->registration_due_date,
            'installment_due_date'       => $val->installment_due_date,
            'sales_agreement_due_amount' => $val->sales_agreement_due_amount,
            'registration_due_amount'    => $val->registration_due_amount,
            'installment_due_amount'     => $val->installment_due_amount,
        ];

// ✅ Combine all `gss_plot_payments` queries into a single query
        $paymentBuilder = $this->db->table('gss_plot_payments A')
                                   ->select('A.agreement_amount, A.installment_amount1, A.registration_amount')
                                   ->where('A.delete_status', 'ACTIVE')
                                   ->where('A.booking_id', $val->booking_id)
                                   ->where('A.detail_id', $val->detail_id);

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

// ✅ Check each field once (avoid multiple queries)
        $data['agmnt'] = (!empty($payment_result) && !empty($payment_result->agreement_amount)) ? 'AVAILABLE' : 'NOTAVAILABLE';
        $data['instl'] = (!empty($payment_result) && !empty($payment_result->installment_amount1)) ? 'AVAILABLE' : 'NOTAVAILABLE';
        $data['regn']  = (!empty($payment_result) && !empty($payment_result->registration_amount)) ? 'AVAILABLE' : 'NOTAVAILABLE';

$array[] = $data;
    }

return $array;
}
public function get_where_result_orderby($table, $where, $order_by)
{
    $db      = \Config\Database::connect();
    $builder = $db->table($table);
    $builder->select('*');
    $builder->where($where);
    $builder->orderBy($order_by, 'DESC');
    $query = $builder->get();
    return $query->getResult(); // same as result() in CI3
}

}?>