the_mechanic/app/Models/JobcardModel.php

<?php 
namespace App\Models;
use CodeIgniter\Model;
class JobcardModel extends Model
{
    protected $table = 'job_card';
    protected $primaryKey = 'job_card_id';
    protected $allowedFields = ['job_card_id','inv_no','vehicle_id','branch_id',
    'order_number','client_mobile_no','client_name',
    'billing_address','billing_city','billing_state','billing_country',
    'billing_postal_code','fuel_qty', 'meter', 'delivery_date', 'note',
    'status','assigned_to', 'is_rework','subtotal','tax', 'discount','total','isactive','mode_of_payment','trial_mechanic','type'];



    public function getJobCardsWithProductsAndService($logged_user_branch_id,$data)
    {
        $this->select('job_card.*, COUNT(job_card_product.job_card_id) AS product_count,COUNT(job_card_os.job_card_id) AS os_count, vehicle.reg_no');
        $this->join('job_card_product', 'job_card_product.job_card_id = job_card.job_card_id', 'left');
        $this->join('job_card_os', 'job_card_os.job_card_id = job_card.job_card_id', 'left');
        $this->join('vehicle', 'vehicle.vehicle_id = job_card.vehicle_id', 'left');
        $this->groupBy('job_card.job_card_id');
        $this->where('job_card.isactive', 1);
        if ($data['role'] == 'Senior Mechanic' || $data['role'] == 'Mechanic') {
            $this->where("JSON_CONTAINS(assigned_to, '" . json_encode($data['id']) . "')", null, false);
        }
        $this->where('job_card.branch_id',$logged_user_branch_id);
        $this->orderBy('job_card.job_card_id','DESC');
        return $this->findAll();
    }

    public function service_data($job_card_id, $logged_user_branch_id)
    {
        $this->select('job_card.job_card_id, job_card_service.*');
        $this->join('job_card_service', 'job_card_service.job_card_id = job_card.job_card_id');
        $this->where('job_card_service.isactive', 1);
        $this ->where('job_card.job_card_id',$job_card_id);
        $this ->where('job_card.branch_id',$logged_user_branch_id);
        return $this->findAll();
    }

    public function complaint_data($job_card_id, $logged_user_branch_id)
    {
        $this->select('job_card.job_card_id, job_card_complaint.*');
        $this->join('job_card_complaint', 'job_card_complaint.job_card_id = job_card.job_card_id');
        $this->where('job_card_complaint.isactive', 1);
        $this->where('job_card.job_card_id',$job_card_id);
        $this->where('job_card.branch_id',$logged_user_branch_id);
        return $this->findAll();
    }

    public function os_data($job_card_id, $logged_user_branch_id)
    {
        $this->select('job_card.job_card_id, job_card_os.*');
        $this->join('job_card_os', 'job_card_os.job_card_id = job_card.job_card_id');
        $this->where('job_card_os.is_active', 1);
        $this->where('job_card.job_card_id',$job_card_id);
        $this->where('job_card.branch_id',$logged_user_branch_id);
        return $this->findAll();
    }

    public function ccomplaint_data($job_card_id, $logged_user_branch_id)
    {
        $this->select('job_card.job_card_id, job_card_custom_complaint.* , job_card_custom_complaint.complaint_name as name');
        $this->join('job_card_custom_complaint', 'job_card_custom_complaint.job_card_id = job_card.job_card_id');
        $this->where('job_card_custom_complaint.isactive', 1);
        $this ->where('job_card.job_card_id',$job_card_id);
        $this ->where('job_card.branch_id',$logged_user_branch_id);
        return $this->findAll();
    }

    public function get_jobcard_vehicle_details($job_id)
    {
        $this->select('job_card.job_card_id, job_card.order_number, job_card.vehicle_id as jobcard_vehicle_id, vehicle.*, make.make as bike_company, model.model_name as bike_name, client.client_name');
        $this->join('vehicle', 'vehicle.vehicle_id = job_card.vehicle_id');
        $this->join('client', 'client.client_id = vehicle.client_id');
        $this->join('make', 'make.make_id = vehicle.make');
        $this->join('model', 'model.model_id = vehicle.model');
        $this->where('job_card.job_card_id', $job_id);
        $this->where('job_card.isactive', 1);
        return $this->findAll();
    }

    public function get_jobcard_products_details($job_id)
    {
        $this->select('job_card_product.*, products.product_name, products.per,products.hsn_sac');
        $this->join('job_card_product', 'job_card_product.job_card_id = job_card.job_card_id');
        $this->join('products', 'products.product_id = job_card_product.product_id');
        $this->where('job_card_product.is_active', 1);
        $this->where('job_card_product.job_card_id', $job_id);
        return $this->findAll();
    }

    public function get_jobcard_service($job_id)
    {
        $this->select('job_card.*, job_card_service.*, services.service_name as product_name, job_card_service.labour_cost as labour_amount'); 
        $this->join('job_card_service', 'job_card_service.job_card_id = job_card.job_card_id');
        $this->join('services', 'services.service_id = job_card_service.service_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }
    public function get_jobcard_sub_service($job_id)
    {
        $this->select("job_card_sub_service.*, services.service_name as product_name , 
        CASE 
            WHEN job_card.type = 'General' THEN services.wg_labour_cost
            WHEN job_card.type = 'Complaints' THEN services.wog_labour_cost
            ELSE 0
        END as labour_amount"); 
        $this->join('job_card_sub_service', 'job_card_sub_service.job_card_id = job_card.job_card_id');
        $this->join('services', 'services.service_id = job_card_sub_service.sub_service_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }

    public function get_jobcard_complaint($job_id)
    {
        $this->select('job_card.*, job_card_complaint.*, complaints.name as product_name, job_card_complaint.labour_cost as labour_amount'); 
        $this->join('job_card_complaint', 'job_card_complaint.job_card_id = job_card.job_card_id');
        $this->join('complaints', 'complaints.complaint_id = job_card_complaint.complaint_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }

    public function get_jobcard_os($job_id)
    {
        $this->select('job_card.*, job_card_os.*, out_source.name as product_name, job_card_os.labour_cost as labour_amount'); 
        $this->join('job_card_os', 'job_card_os.job_card_id = job_card.job_card_id');
        $this->join('out_source', 'out_source.id = job_card_os.os_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }

    public function get_jobcard_custom_complaint($job_id)
    {
        $this->select('job_card.*, job_card_custom_complaint.*, job_card_custom_complaint.complaint_name as product_name, job_card_custom_complaint.labour_cost as labour_amount'); 
        $this->join('job_card_custom_complaint', 'job_card_custom_complaint.job_card_id = job_card.job_card_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }

    public function get_jobcard_labourcost($job_id)
    {
        $this->select("job_card.*, job_card_service.job_card_id, job_card_service.service_id, job_card_complaint.job_card_id, job_card_complaint.complaint_id,  complaints.labour_charge,services.tax ,complaints.tax,  complaints.labour_charge ,
        CASE 
            WHEN job_card.type = 'General' THEN services.wg_labour_cost
            WHEN job_card.type = 'Complaints' THEN services.wog_labour_cost
            ELSE 0
        END as labour_amount");
        $this->join('job_card_service', 'job_card_service.job_card_id = job_card.job_card_id');
        $this->join('services', 'services.service_id = job_card_service.service_id');
        $this->join('job_card_complaint', 'job_card_complaint.job_card_id = job_card.job_card_id');
        $this->join('complaints', 'complaints.complaint_id = job_card_complaint.complaint_id');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.job_card_id', $job_id);
        return $this->findAll();
    }
    
    // $this->session->get('logged_user_branch_id')
    public function download_os($logged_user_branch_id)
    {

        $this->select(
            'job_card.job_card_id,job_card.created_at,job_card.branch_id,job_card.isactive,job_card.vehicle_id, 
            job_card_os.job_card_id,job_card_os.os_id,job_card_os.product_id,  
            out_source.name,
            vehicle.reg_no, vehicle.make, vehicle.colour,vehicle.model, 
            make.make as bike_name, model.model_name as bike_modal_name,job_card.delivery_date'
        );
        // $this->from('job_card');
        $this->join('job_card_os', 'job_card_os.job_card_id = job_card.job_card_id');
        $this->join('out_source', 'out_source.id = job_card_os.os_id', 'left');
        $this->join('vehicle', 'vehicle.vehicle_id = job_card.vehicle_id', 'left');
        $this->join('make', 'make.make_id = vehicle.make', 'left');
        $this->join('model', 'model.model_id = vehicle.model', 'left');
        $this->where('job_card.isactive', 1);
        $this->where('job_card.branch_id',$logged_user_branch_id);
        $current_date = date('Y-m-d');
        $this->where('DATE(job_card.created_at)', $current_date);
        // $this->groupBy('job_card.job_card_id');
        $this->orderBy('job_card.job_card_id','DESC');
        return $this->findAll();
    }
    

    
    
    
}