EVOLUTION-NINJA

Edit File: GssModel.php

<?php 
namespace App\Models;

use CodeIgniter\Model;

class GssModel extends Model
{
    protected $db;

public function __construct()
    {
        parent::__construct();
        $this->db = \Config\Database::connect();
    }

public function test_details($project_id, $site_number)
{
    // Access the database connection
    $builder = $this->db->table('gss_bookings A');

// Perform a JOIN operation
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');

// Add WHERE conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('C.site_number', $site_number);
    $builder->where('C.project_id', $project_id);
    $builder->where('A.booking_status', 'BOOKED');

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

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

// Return the first row of the result (similar to row() in CI3)
    return $result->getRow();
}
public function get_payments_types_result($booking_id, $detail_id)
{
    // Access the database connection and set the table
    $builder = $this->db->table('gss_plot_payments A');

// Perform a JOIN operation
    $builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id');

// Add WHERE conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);
    $builder->where('A.detail_id', $detail_id);

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

// Return all rows (similar to result() in CI3)
    return $result->getResult();
}
public function get_payments_types($booking_id, $detail_id)
{
    // Access the database connection and set the table
    $builder = $this->db->table('gss_plot_payments A');

// Perform a LEFT JOIN operation
    $builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id', 'left');

// Add WHERE conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);
    $builder->where('A.detail_id', $detail_id);

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

// Return a single row (similar to row() in CI3)
    return $result->getRow();
}
public function get_registration_amount($booking_id)
{
    // Access the database connection and set the table
    $builder = $this->db->table('gss_registration_amount_details A');

// Add WHERE conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);

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

// Return a single row (similar to row() in CI3)
    return $result->getRow();
}

public function registration_details_get($booking_id, $detail_id)
{
    $builder = $this->db->table('gss_registration_amount_details A');

$builder->select('A.*, A.registration_confirming_type as reg_confirming_type');
    $builder->join('gss_plot_payments B', 'B.booking_id = A.booking_id');
    $builder->where('A.booking_id', $booking_id);
    $builder->where('A.detail_id', $detail_id);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('B.registration_delete_status', 'ACTIVE');
    $builder->groupBy('A.id');

$result = $builder->get();

return $result->getResult(); // Equivalent to result() in CI3
}
public function project_details($site_number, $project_id)
{
    $builder = $this->db->table('gss_new_sites A');

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

$result = $builder->get();

return $result->getRow(); // CI4 equivalent of row()
}

public function payment_booking_details($site_number, $project_id, $booking_id)
{
    $builder = $this->db->table('gss_booking_details A');

$builder->select('A.*, B.*, C.total_in_sqft, D.no_of_years');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_new_sites C', 'C.site_number = A.site_number AND C.project_id = A.project_id');
    $builder->join('gss_new_projects D', 'D.project_id = B.project_id');
    
    $builder->where('A.project_id', $project_id);
    $builder->where('A.site_number', $site_number);
    $builder->where('A.booking_id', $booking_id);
    $builder->where('B.booking_status', 'BOOKED');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();
    return $query->getRow(); // Equivalent of CI3's row()
}
public function due_amounts($site_number, $project_id)
{
    $builder = $this->db->table('gss_bookings A');

$builder->select('A.booking_id, C.sales_agreement_due_amount, C.registration_due_amount, D.installment_due_amount');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    $builder->join('gss_booking_installments D', 'D.booking_detal_id = C.detail_id');
    
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('C.site_number', $site_number);
    $builder->where('C.project_id', $project_id);
    
    $builder->orderBy('A.booking_id', 'DESC');

$result = $builder->get();
    return $result->getRow(); // CI4 equivalent of CI3's row()
}
public function get_agreement_amount_details($booking_id, $detail_id)
{
    $builder = $this->db->table('gss_plot_payments A');

$builder->select('A.*, B.*');
    $builder->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);
    $builder->where('A.detail_id', $detail_id);
    $builder->where('A.agreement_delete_status', 'ACTIVE');
    $builder->groupBy('A.booking_id');

$result = $builder->get();
    return $result->getResult(); // CI4 equivalent of result()
}
public function registration_payment_details($booking_id)
{
    $builder = $this->db->table('gss_plot_payments A');

$builder->select('A.*, B.*, C.*, D.*, E.*, 
                      C.paytm_ref_no as paytm_num, 
                      C.paytm_ref_no2 as paytm_num2, 
                      C.upi_ref_no as upi_num, 
                      C.upi_ref_no2 as upi_num2');

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

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

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

$result = $builder->get();
    return $result->getRow(); // CI4 equivalent of row()
}
public function get_installment_amount_details($booking_id, $detail_id)
{
    $builder = $this->db->table('gss_plot_payments A');

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

public function user_site_booking_details1($booking_id)
{
    $builder = $this->db->table('gss_bookings A');
    
    // Selecting columns
    $builder->select('A.*, B.*, C.*, D.registration_date, A.booking_status as a_booking_status, B.booking_status as b_booking_status');
    
    // Joining tables
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    $builder->join('gss_plot_payments D', 'D.booking_id = A.booking_id', 'left');
    
    // Adding where conditions
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('A.booking_id', $booking_id);
    
    // Ordering the result
    $builder->orderBy('A.booking_id', 'DESC');
    
    // Getting the result
    $result = $builder->get()->getRow();
    
    if (empty($result)) {
        return []; // Returning an empty array if no result found
    }
    
    // Prepare data array
    $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' => ($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,
    ];

// Second query to get site details
    $builder = $this->db->table('gss_new_sites A');
    $builder->select('A.total_in_sqft, A.status');
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.project_id', $result->project_id);
    $builder->where('A.site_number', $result->site_number);
    $result_1 = $builder->get()->getRow();

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

// Other fields
    $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;
    $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;

// Handle confirming parties and other conditions
    $data['confirming_party1'] = ($result->confirming_party1 == 'owner') ? 'Owner' : 'Confirming Party';
    $data['confirming_party2'] = ($result->confirming_party2 == 'owner') ? 'Owner' : 'Confirming Party';
    
    if ($result->webportal != 0) {
        $builder = $this->db->table('gss_webportals A');
        $builder->select('A.*, B.*, A.webportal as portal');
        $builder->join('gss_bookings B', 'B.webportal = A.portal_id');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('A.portal_id', $result->webportal);
        $web_result = $builder->get()->getRow();
        
        $data['webportal'] = ($web_result) ? $web_result->portal : '';
    } else {
        $data['webportal'] = '';
    }

// Repeat for reference, logistics, associate, and subassociate with similar approach
    
    $array[] = $data;
    return $array;
}

// use CodeIgniter\Database\QueryBuilder;

public function get_where_row($table, $where)
{
    // Get the database connection
    $db = \Config\Database::connect();
    
    // Perform the query using the Query Builder
    $query = $db->table($table)
                ->where($where)
                ->get();
    
    // Return the first result (row)
    return $query->getRow();
}

// use CodeIgniter\Database\QueryBuilder;

public function get_where_installments($id)
{
    // Get the database connection
    $db = \Config\Database::connect();
    
    // Perform the query using the Query Builder
    $query = $db->table('gss_plot_payments A')
                ->select('*')
                ->join('gss_plot_payment_types B', 'B.payment_id = A.payment_id')
                ->where('A.delete_status', 'ACTIVE')
                ->where('B.delete_status', 'ACTIVE')
                ->where('A.payment_id', $id)
                ->get();
    
    // Return the first result (row)
    return $query->getRow();
}
 
public function get_where_result($table, $where)
{
    // Get the builder instance for the provided table
    $builder = $this->db->table($table);
    
    // Apply the WHERE condition
    $builder->select('*')->where($where);
    
    // Execute the query and fetch the results as an array of objects
    $query = $builder->get();

// Return the result as an array of objects
    return $query->getResult();
}

public function cancellation_details2($cancellation_id)
{
    // Using the builder method in CodeIgniter 4 for retrieving data
    $builder = $this->db->table('gss_cancellations A');
    
    // Performing the join operations
    $builder->select('A.*, B.*, C.*, E.*, F.*');
    $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 E', 'E.project_id = B.project_id');
    $builder->join('gss_new_sites F', 'F.project_id = E.project_id AND C.site_number = F.site_number');
    
    // Adding conditions for the query
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.cancellation_id', $cancellation_id);
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('E.delete_status', 'ACTIVE');
    $builder->where('F.delete_status', 'ACTIVE');
    
    // Running the query and fetching the result
    $query = $builder->get(); // This will run the query
    
    // Getting the first (and possibly only) row of the result
    return $query->getRow();
}

public function cancellation_payment_details($cancellation_id)
{
    $builder = $this->db->table('gss_cancellations A');

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

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

$builder->where('A.cancellation_id', $cancellation_id);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('C.install_delete_status', 'ACTIVE');
    $builder->where('D.delete_status', 'ACTIVE');
    $builder->where('E.delete_status', 'ACTIVE');
    $builder->where('F.delete_status', 'ACTIVE');

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

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

public function cancellation_agreement_details($cancellation_id)
{
    $builder = $this->db->table('gss_cancellations A');

$builder->select('*');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_plot_payments C', 'C.booking_id = B.booking_id');
    $builder->join('gss_plot_payment_types F', 'F.payment_id = C.payment_id');

$builder->where('A.cancellation_id', $cancellation_id);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('C.agreement_delete_status', 'ACTIVE');
    $builder->where('F.delete_status', 'ACTIVE');

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

$query = $builder->get();
    return $query->getResult();
}
public function cancellation_total_amount($cancellation_id)
{
    $builder = $this->db->table('gss_cancellations A');

$builder->select('A.*, B.*, C.*, D.*, D.booking_amount1, D.booking_amount2, C.agreement_amount, C.installment_amount1, C.installment_amount2');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->join('gss_plot_payments C', 'C.booking_id = B.booking_id');
    $builder->join('gss_booking_details D', 'D.detail_id = C.detail_id');

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

public function cancellation_details_no_refunds($cancellation_id)
{
    $builder = $this->db->table('gss_cancellations A');
    $builder->select('A.*, B.*, C.*, E.*, F.*');
    $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 E', 'E.project_id = B.project_id');
    $builder->join('gss_new_sites F', 'F.project_id = E.project_id AND C.site_number = F.site_number');

$builder->where('A.delete_status', 'ACTIVE');
    $builder->where('A.cancellation_id', $cancellation_id);
    $builder->where('B.delete_status', 'ACTIVE');
    $builder->where('C.delete_status', 'ACTIVE');
    $builder->where('E.delete_status', 'ACTIVE');
    $builder->where('F.delete_status', 'ACTIVE');

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

if (!$res) return [];

$data = [
        'customer_name' => $res->customer_name,
        'project_name' => $res->project_name,
        'site_number' => $res->site_number,
        'total_in_sqft' => $res->total_in_sqft,
        'reference' => '',
        'associate' => '',
        'refunded' => $res->refunded,
        'booking_date1' => $res->booking_date1,
        'booking_amount1' => $res->booking_amount1,
        'booking_date2' => $res->booking_date2,
        'booking_amount2' => $res->booking_amount2,
        'sales_agreement_due_date' => $res->sales_agreement_due_date,
        'sales_agreement_due_amount' => $res->sales_agreement_due_amount,
        'refunded_request_letter' => $res->refunded_request_letter,
        'cancellation_agreement_image' => $res->cancellation_agreement_image,
        'booking_payment_type' => $res->booking_payment_type,
        'check_no' => $res->check_no,
        'check_date' => $res->check_date,
        'bank_name' => $res->bank_name,
        'vtr_no' => $res->vtr_no,
        'online_date' => $res->online_date,
        'paytm_ref_no' => $res->paytm_ref_no,
        'paytm_online_date' => $res->paytm_online_date,
        'upi_ref_no' => $res->upi_ref_no,
        'upi_online_date' => $res->upi_online_date,
        'paytm_ref_no2' => $res->paytm_ref_no2,
        'paytm_online_date2' => $res->paytm_online_date2,
        'upi_ref_no2' => $res->upi_ref_no2,
        'upi_online_date2' => $res->upi_online_date2,
        'dd_no' => $res->dd_no,
        'dd_date' => $res->dd_date,
        'dd_bank' => $res->dd_bank,
        'booking_payment_type2' => $res->booking_payment_type2,
        'confirming_party1' => $res->confirming_party1,
        'confirming_party2' => $res->confirming_party2,
        'check_no2' => $res->check_no2,
        'check_date2' => $res->check_date2,
        'bank_name2' => $res->bank_name2,
        'vtr_no2' => $res->vtr_no2,
        'online_date2' => $res->online_date2,
        'dd_no2' => $res->dd_no2,
        'dd_date2' => $res->dd_date2,
        'dd_bank2' => $res->dd_bank2,
    ];

// Get Reference
    if ($res->reference != 0) {
        $refBuilder = $this->db->table('gss_brokers A');
        $refBuilder->select('A.*, B.*, A.associate_name as reference');
        $refBuilder->join('gss_bookings B', 'B.reference = A.broker_id');
        $refBuilder->where('A.broker_id', $res->reference);
        $refBuilder->where('B.delete_status', 'ACTIVE');
        $reference_result = $refBuilder->get()->getRow();

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

// Get Associate
    if ($res->associate != 0) {
        $assBuilder = $this->db->table('gss_brokers A');
        $assBuilder->select('A.*, B.*, A.associate_name as associate');
        $assBuilder->join('gss_bookings B', 'B.associate = A.broker_id');
        $assBuilder->where('A.broker_id', $res->associate);
        $assBuilder->where('B.delete_status', 'ACTIVE');
        $associate_result = $assBuilder->get()->getRow();

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

return $data;
}

protected $table = 'gss_cancellations';
protected $primaryKey = 'cancellation_id';

public function cancellation_details1($cancellation_id)
{
    // Correctly using the query builder provided by $this->db
    $builder = $this->db->table('gss_cancellations A');
    $builder->select('A.*, B.booking_id, B.delete_status, B.booking_status');
    $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
    $builder->where('A.cancellation_id', $cancellation_id);
    $builder->where('A.delete_status', 'ACTIVE');
    $builder->where('B.delete_status', 'ACTIVE');
    $query = $builder->get();
    $row = $query->getRow();

if ($row->booking_status == 'REFUNDED') {
        $builder = $this->db->table('gss_cancellations A');
        $builder->select('A.*, B.*, C.*, E.*, F.total_in_sqft');
        $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
        $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
        $builder->join('gss_new_projects E', 'E.project_id = B.project_id');
        $builder->join('gss_new_sites F', 'F.project_id = E.project_id and C.site_number = F.site_number');
        $builder->where('A.cancellation_id', $cancellation_id);
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('C.delete_status', 'ACTIVE');
        $builder->where('E.delete_status', 'ACTIVE');
        $builder->groupBy('B.booking_id');
        $query = $builder->get();
        $res = $query->getRow();

$data = [
            'customer_name' => $res->customer_name,
            'project_name' => $res->project_name,
            'site_number' => $res->site_number,
            'total_in_sqft' => $res->total_in_sqft,
            'reference' => $res->reference == 0 ? "" : $this->getBrokerName($res->reference, 'reference'),
            'associate' => $res->associate == 0 ? "" : $this->getBrokerName($res->associate, 'associate'),
            'refunded' => $res->refunded,
            'refunded_date' => $res->refunded_date,
            'refunded_amount' => $res->refunded_amount,
            'note' => $res->note,
            'due_amount' => $res->due_amount,
            'total' => $res->total,
            'due_with' => $res->due_with,
            'booking_date1' => $res->booking_date1,
            'booking_amount1' => $res->booking_amount1,
            'booking_date2' => $res->booking_date2,
            'booking_amount2' => $res->booking_amount2,
            'sales_agreement_due_date' => $res->sales_agreement_due_date,
            'sales_agreement_due_amount' => $res->sales_agreement_due_amount,
            'refunded_request_letter' => $res->refunded_request_letter,
            'cancellation_agreement_image' => $res->cancellation_agreement_image,
            'booking_payment_type' => $res->booking_payment_type,
            'check_no' => $res->check_no,
            'check_date' => $res->check_date,
            'bank_name' => $res->bank_name,
            'vtr_no' => $res->vtr_no,
            'online_date' => $res->online_date,
            'paytm_ref_no' => $res->paytm_ref_no,
            'paytm_online_date' => $res->paytm_online_date,
            'upi_ref_no' => $res->upi_ref_no,
            'upi_online_date' => $res->upi_online_date,
            'dd_no' => $res->dd_no,
            'dd_date' => $res->dd_date,
            'dd_bank' => $res->dd_bank,
        ];

// Fetch multiple refunds
        $builder = $this->db->table('gss_cancellation_refunds');
        $builder->where('cancellation_id', $res->cancellation_id);
        $builder->where('delete_status', 'ACTIVE');
        $query1 = $builder->get();
        $result1 = $query1->getResult();
        $data['mul_refunds'] = $result1;

return $data;
    } elseif ($row->booking_status == 'REFUND_PENDING') {
        // Similar block as above for 'REFUND_PENDING'
        $builder = $this->db->table('gss_cancellations A');
        $builder->select('A.*, B.*, C.*, E.*, F.total_in_sqft');
        $builder->join('gss_bookings B', 'B.booking_id = A.booking_id');
        $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
        $builder->join('gss_new_projects E', 'E.project_id = B.project_id');
        $builder->join('gss_new_sites F', 'F.project_id = E.project_id and C.site_number = F.site_number');
        $builder->where('A.cancellation_id', $cancellation_id);
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('C.delete_status', 'ACTIVE');
        $builder->where('E.delete_status', 'ACTIVE');
        $builder->groupBy('B.booking_id');
        $query = $builder->get();
        $res = $query->getRow();

$data = [
            'customer_name' => $res->customer_name,
            'project_name' => $res->project_name,
            'site_number' => $res->site_number,
            'total_in_sqft' => $res->total_in_sqft,
            'reference' => $res->reference == 0 ? "" : $this->getBrokerName($res->reference, 'reference'),
            'associate' => $res->associate == 0 ? "" : $this->getBrokerName($res->associate, 'associate'),
            'refunded' => $res->refunded,
            'refunded_date' => $res->refunded_date,
            'refunded_amount' => $res->refunded_amount,
            'note' => $res->note,
            'due_amount' => $res->due_amount,
            'total' => $res->total,
            'due_with' => $res->due_with,
            'booking_date1' => $res->booking_date1,
            'booking_amount1' => $res->booking_amount1,
            'booking_date2' => $res->booking_date2,
            'booking_amount2' => $res->booking_amount2,
            'sales_agreement_due_date' => $res->sales_agreement_due_date,
            'sales_agreement_due_amount' => $res->sales_agreement_due_amount,
            'refunded_request_letter' => $res->refunded_request_letter,
            'cancellation_agreement_image' => $res->cancellation_agreement_image,
            'booking_payment_type' => $res->booking_payment_type,
            'check_no' => $res->check_no,
            'check_date' => $res->check_date,
            'bank_name' => $res->bank_name,
            'vtr_no' => $res->vtr_no,
            'online_date' => $res->online_date,
            'dd_no' => $res->dd_no,
            'dd_date' => $res->dd_date,
            'dd_bank' => $res->dd_bank,
            'paytm_ref_no' => $res->paytm_ref_no,
            'paytm_online_date' => $res->paytm_online_date,
            'upi_ref_no' => $res->upi_ref_no,
            'upi_online_date' => $res->upi_online_date,
        ];

return $data;
    } else {
        // Handle other cases if needed
        return null;
    }
}

private function getBrokerName($broker_id, $type)
{
    $builder = $this->db->table('gss_brokers A');
    $builder->select("A.associate_name as $type");
    $builder->where('A.broker_id', $broker_id);
    $query = $builder->get();
    $result = $query->getRow();
    return $result ? $result->$type : '';
}

public function project_details1($site_id, $project_id)
{
    // Get the builder instance for the 'gss_new_sites' table
    $builder = $this->db->table('gss_new_sites A');
    
    // Join 'gss_new_projects' table
    $builder->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_id', $site_id)
            ->where('A.project_id', $project_id);

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

return $result;
}
public function individual_payment_site_booking_details($site_number, $project_id, $booking_id)
{
    // Get the builder instance for the 'gss_bookings' table
    $builder = $this->db->table('gss_bookings A');

// Join the 'gss_new_projects' and 'gss_booking_details' tables
    $builder->select('*')
            ->join('gss_new_projects B', 'B.project_id = A.project_id')
            ->join('gss_booking_details C', 'C.booking_id = A.booking_id')
            ->where('A.booking_id', $booking_id)
            ->where('A.delete_status', 'ACTIVE')
            ->where('B.delete_status', 'ACTIVE')
            ->where('C.delete_status', 'ACTIVE')
            ->where('C.site_number', $site_number)
            ->where('C.project_id', $project_id)
            ->orderBy('A.booking_id', 'ASC');

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

return $result;
}
public function get_where_payment_id_result($table, $where)
{
    // Get the builder instance for the specified table
    $builder = $this->db->table($table);

// Select the 'payment_id' column
    $builder->select('payment_id');

// Add the 'where' condition
    $builder->where($where);

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

// Return the result as an array of objects
    return $query->getResult();
}

public function get_where_multireg_id_result($table, $where)
{
    // Get the builder instance for the specified table
    $builder = $this->db->table($table);

// Select the 'id' column
    $builder->select('id');

// Add the 'where' condition
    $builder->where($where);

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

// Return the result as an array of objects
    return $query->getResult();
}
public function check_notification($booking_id, $detail_id)
{
    // Get the builder instance for the 'gss_commission_notifications' table
    $builder = $this->db->table('gss_commission_notifications');

// Select all columns (equivalent to 'A.*' in CI3)
    $builder->select('*');

// Add the where conditions
    $builder->where('delete_status', 'ACTIVE');
    $builder->where('booking_id', $booking_id);
    $builder->where('detail_id', $detail_id);

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

// Return the first row of the result (equivalent to row() in CI3)
    return $query->getRow();
}

public function update($id = null, $row = null): bool
{
    // Check if the $id and $row are provided
    if ($id && is_array($row)) {
        // Get the builder instance for the table
        $builder = $this->db->table($this->table); // Optionally use a specific table

// Perform the update with the $id and $row data
        $builder->where($id); // $id can be an array or a condition
        $builder->update($row);

// Check if the update was successful
        return $builder->affectedRows() > 0;
    }

// Return false if the data or condition is not valid
    return false;
}

public function insert($row = null, bool $returnID = true)
{
    // Check if table and data are provided
    if (is_array($row) && $returnID) {
        $builder = $this->db->table($this->table); // Use the table property or specify a table
        $builder->insert($row);

// Return the inserted ID if needed
        return $returnID ? $this->db->insertID() : true;
    }

// Handle scenarios where no data is provided
    return false;
}

public function delete($id = null, bool $purge = false): bool
{
    if ($id === null) {
        // If no ID is provided, you can return false or handle as needed
        return false;
    }

// Get the builder instance for the table (optional: specify a table)
    $builder = $this->db->table($this->table); // You may want to specify the table if it's not dynamic

// Apply the where condition to specify the row to delete
    $builder->where($id);

// Perform the delete operation
    $result = $builder->delete();

// Return the success status (whether any rows were deleted)
    return $result;
}
public function get_call_p_details($where)
{
    $builder = $this->db->table('gss_bookings A');
    $builder->select('A.customer_name, A.address, B.project_id, B.project_name, B.handled_by, C.site_number, C.tsv, C.tsv_per_sft_rs, C.detail_id');
    $builder->join('gss_new_projects B', 'B.project_id = A.project_id');
    $builder->join('gss_booking_details C', 'C.booking_id = A.booking_id');
    // $builder->join('gss_follow_ups D', 'D.detail_id = C.detail_id'); // Uncomment if needed

$builder->where('C.detail_id', $where);
    $query = $builder->get();

return $query->getRow();
}

public function get_call_details($where, $where1)
{
    $builder = $this->db->table('gss_new_projects A');
    $builder->select('E.id, E.user_id, E.detail_id, E.conversation, E.user_reply, E.reminder, E.close_status, E.comment, E.site_number, E.created_at, 
                      A.handled_by, A.project_name, A.project_id, 
                      B.user_id, 
                      C.detail_id, C.tsv, C.tsv_per_sft_rs, C.updated_at, 
                      D.customer_name, D.address');
    
    $builder->join('gss_login B', 'B.user_id = A.handled_by');
    $builder->join('gss_booking_details C', 'C.project_id = A.project_id');
    $builder->join('gss_bookings D', 'D.booking_id = C.booking_id');
    $builder->join('gss_follow_ups E', 'C.detail_id = E.detail_id');

$builder->where('C.detail_id', $where);
    $builder->orderBy('E.id', 'DESC');

$query = $builder->get();
    return $query->getResult();
}
public function get_comment_details($where, $where1)
{
    $builder = $this->db->table('gss_comment E');
    $builder->select('E.id, E.detail_id, E.user_id, E.comment, E.comment_date, E.site_number, F.id AS conversation_id');
    $builder->join('gss_follow_ups F', 'F.id = E.conversation_id');
    $builder->where('E.detail_id', $where);
    $builder->orderBy('E.id', 'ASC');

$query = $builder->get();
    return $query->getResult();
}
public function get_reply_details($where)
{
    $builder = $this->db->table('gss_reply E');
    $builder->select('E.id, E.detail_id, E.user_id, E.user_reply, E.reply_date, F.id AS comment_id, F.conversation_id');
    $builder->join('gss_comment F', 'F.id = E.comment_id');
    $builder->where('E.detail_id', $where);
    $builder->orderBy('E.id', 'ASC');

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

}
?>