nhance_partner_be/app/Models/EnquiryModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;
use DateTime;
class EnquiryModel extends Model
{
    protected $table            = 'partner_enquiry';
    protected $primaryKey       = 'id';
    protected $returnType       = 'array';
    protected $allowedFields    = [
        'agent_id',
        'name',
        'mobile',
        'email',
        'reg_no',
        'vehicle_type_id',
        'rc_file_name',
        'id_proof_file_name',
        'previous_policy_file_name',
        'remarks',
        'status',
        'is_active',
        'manager_id',
        'assigned_to',
        'assigned_to_datetime',
        'insurer_id',
        'insurer_branch_id',
        'created_by',
        'updated_by',
        'is_data_created_by_staff',
        'is_data_created_by_manager',
        'broker_id',
        'enquiry_status',
        'is_quick_quote',
        'enquiry_created_on'
    ];

    protected $useTimestamps = true;
    protected $createdField  = 'created_on';
    protected $updatedField  = 'updated_on';


    public function getEnquiry($getBy = null , $Id = null , $only_policy_data = false, $show_policy_report = 'All',$insurer_id  = null, $from_date = null, $to_date = null , $status = null , $enquiry_status = null)
    {
                $data = $this->select('partner_enquiry.*, partner_enquiry.name as insured_name,
                                VT.vehicle_type as vehicle_type,
                                A.name as agent_name, A.agent_code,
                                I.name as insurer_name,I.short_name as insurer_short_name,
                                S.name as assigned_to_name,
                                Q.id as quotation_id,
                                P.payment_mode,
                                P.premium_amount,
                                P.policy_number,
                                P.id as policy_id,
                                P.policy_pdf_file_name,
                                P.is_data_accuracy_checked,
                                PB.name as broker_name,
                                pm.id as payment_mode_id , 
                                pm.value as payment_mode_value,
                                ipti.insurance_plan_type
                            ')
                            ->select('CASE WHEN P.client_id > 0 THEN 1 ELSE 0 END AS is_bds_pushed', false)
                ->join('vehicle_type VT', 'VT.id = partner_enquiry.vehicle_type_id', 'left')
                ->join('partner_agent A', 'A.id = partner_enquiry.agent_id', 'left')
                ->join('partner_staff S', 'S.id = partner_enquiry.assigned_to', 'left')
                ->join('partner_quotation Q', 'Q.enquiry_id = partner_enquiry.id AND Q.status = "Accepted" ', 'left')
                ->join('insurers I', 'I.id = partner_enquiry.insurer_id', 'left')
                ->join('partner_policy P', 'P.quotation_id = Q.id', 'left')
                ->join('partner_brokers PB', 'PB.id = partner_enquiry.broker_id', 'left')
                ->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left')
                ->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left')
                ->where('partner_enquiry.is_active', 1)
                ->where('partner_enquiry.is_quick_quote', 0)
                ->orderBy('partner_enquiry.created_on', 'DESC');

                if($status != null){
                    $data->where('partner_enquiry.status' , $status );
                }

                if($enquiry_status != null){
                    if($enquiry_status == 'OtherThenCompleted')
                    {
                        $data->where('partner_enquiry.enquiry_status !=' , 'Completed' );
                    }else{
                        $data->where('partner_enquiry.enquiry_status' , $enquiry_status );
                    }
                }
                

                if($only_policy_data){

                    $data->where('P.id IS NOT NULL');

                    if($insurer_id != null){
                        $data->where('partner_enquiry.insurer_id', $insurer_id);
                    }

                    if($from_date != null){
                    $data->where("P.created_on >=", $from_date)
                         ->where("P.created_on <=", $to_date);
                    }

                    if($show_policy_report == 'Verified')
                    {
                        $data->where("P.is_data_accuracy_checked", "1");
                    }else if($show_policy_report == 'Not Verified')
                    {
                        $data->where("P.is_data_accuracy_checked", "0");
                    }
                }else{
                    if($from_date != null){
                    // $data->where("partner_enquiry.created_on >=", $from_date)
                    //      ->where("partner_enquiry.created_on <=", $to_date);

                     $data->groupStart()
                            // Date filter
                            ->groupStart()
                                ->where("partner_enquiry.created_on >=", $from_date)
                                ->where("partner_enquiry.created_on <=", $to_date)
                            ->groupEnd()

                            // OR pending cases (ignore date)
                            ->orGroupStart()
                                ->where("partner_enquiry.enquiry_status !=", "Completed")
                            ->groupEnd()

                        ->groupEnd();
                    }
                }

                    
                if($getBy == 'ALL')
                {
                    return $data->findAll();
                }
                else if($getBy == 'AGENT')
                {
                    return $data->where('partner_enquiry.agent_id',$Id)->findAll();
                }
                else if($getBy == 'STAFF')
                {
                    return $data->where('partner_enquiry.assigned_to',$Id)->findAll();
                }
                else if($getBy == 'ENQUERY')
                {
                    return $data->where('partner_enquiry.id', $Id)->first();
                }
                else if($getBy == 'MANAGER')
                {
                    return $data->where('partner_enquiry.manager_id', $Id)->findAll();
                }
                else if($getBy == 'HANDLER')
                {
                    return $data->whereIn('partner_enquiry.assigned_to', $Id)->findAll();
                }
                else if($getBy == 'STAFF&MANAGER')
                {
                    $staffId = $Id[0];
                    $managerId = $Id[1];
                    return $data->where('partner_enquiry.manager_id', $managerId)->where('partner_enquiry.assigned_to',$staffId)->findAll();

                }
                
    }
}