<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Mis_Model extends MY_Model {

    function __construct() {
        parent::__construct();
    }
    public function get_doctor_list() {
        $this->db->select('C.*, E.name, E.phone, E.user_id');
        $this->db->from('doctor_charges AS C');
        $this->db->join('employees AS E', 'E.user_id = C.doctor_id', 'left');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('U.role_id', DOCTOR);
        $this->db->where('E.status', 1);
        $this->db->order_by('E.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_employee_list() {
        $this->db->select('E.*');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', '1');
        if ($this->session->userdata('role_id') == SUPER_ADMIN) {
            $this->db->where_in('U.role_id', array(SUPER_ADMIN, ADMIN, ACCOUNTANT, RECEPTIONIST));
        } else {
            $this->db->where_in('U.role_id', array(ADMIN, ACCOUNTANT, RECEPTIONIST));
        }
        $this->db->order_by('E.id ', 'DESC');
        return $this->db->get()->result();
    }

    // Credit Report
    public function get_opd_income($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(OP.id) as total_opd_quantity, SUM(OP.hospital_commission) as total_opd_income');
        $this->db->from('opd_patients AS OP');
        if ($type == 1) {
            $this->db->where('OP.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('OP.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('OP.reference_id', 0);
            $this->db->or_where('OP.reference_id', NULL);
            $this->db->or_where('OP.reference_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('OP.reference_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(OP.appointment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('OP.reference_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('OP.created_by', $created_by);
        }
        $this->db->where('OP.status', 1);
        $this->db->where('OP.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_ipd_income($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(IP.id) as total_ipd_quantity, SUM(IP.advanced) as total_ipd_income');
        $this->db->from('ipd_invoices AS IP');
        if ($type == 1) {
            $this->db->where('IP.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('IP.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('IP.commission_user_id', 0);
            $this->db->or_where('IP.commission_user_id', NULL);
            $this->db->or_where('IP.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('IP.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(IP.bill_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('IP.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('IP.created_by', $created_by);
        }
        $this->db->where('IP.status', 1);
        $this->db->where('IP.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_diagnostic_income($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(I.id) as total_diagnostic_quantity, SUM(I.advanced) as total_diagnostic_income');
        $this->db->from('invoices AS I');
        if ($type == 1) {
            $this->db->where('I.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('I.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('I.commission_user_id', 0);
            $this->db->or_where('I.commission_user_id', NULL);
            $this->db->or_where('I.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('I.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(I.bill_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('I.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.status', 1);
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_pharmacy_income($type, $form_date, $to_date, $created_by) {
        $this->db->select('COUNT(I.id) as total_pharmacy_quantity, SUM(I.advanced) as total_pharmacy_income');
        $this->db->from('medicine_invoices AS I');
        if ($type == 1) {
            $this->db->where('I.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('I.paid_status', 'unpaid');
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(I.bill_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.status', 1);
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row();
    }

    // Expense
    public function get_opd_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_opd_quantity, SUM(P.payable_amount) as total_opd_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'opd');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_ipd_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_ipd_quantity, SUM(P.payable_amount) as total_ipd_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'ipd');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_diagnostic_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_diagnostic_quantity, SUM(P.payable_amount) as total_diagnostic_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'investigation');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_surgeon_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_surgeon_quantity, SUM(P.payable_amount) as total_surgeon_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'surgeon');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_anesthesia_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_anesthesia_quantity, SUM(P.payable_amount) as total_anesthesia_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'anesthesia');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_assistant_expense($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_assistant_quantity, SUM(P.payable_amount) as total_assistant_expense');
        $this->db->from('payments AS P');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'assistant');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_income($form_date, $to_date, $created_by) {
        $this->db->select('I.*, IH.name as income_head');
        $this->db->from('incomes AS I');
        $this->db->join('income_heads AS IH', 'IH.id = I.income_head_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(I.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.status', 1);
        $this->db->group_by('IH.id');
        $this->db->order_by('IH.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_income_head_income($income_head_id, $form_date, $to_date, $created_by) {
        $this->db->select('COUNT(I.id) as total_income_quantity, SUM(I.amount) as total_head_income');
        $this->db->from('incomes AS I');
        if ($form_date && $to_date) {
            $this->db->where("DATE(I.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.income_head_id', $income_head_id);
        $this->db->where('I.status', 1);
        return $this->db->get()->row();
    }

    public function get_expenditure($form_date, $to_date, $created_by) {
        $this->db->select('E.*, EH.name as expenditure_head');
        $this->db->from('expenditures AS E');
        $this->db->join('expenditure_heads AS EH', 'EH.id = E.expenditure_head_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(E.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('E.created_by', $created_by);
        }
        $this->db->where('E.status', 1);
        $this->db->group_by('EH.id');
        $this->db->order_by('EH.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_expenditure_head_expense($expenditure_head_id, $form_date, $to_date, $created_by) {
        $this->db->select('COUNT(E.id) as total_expenditure_quantity, SUM(E.amount) as total_expenditure_expense');
        $this->db->from('expenditures AS E');
        if ($form_date && $to_date) {
            $this->db->where("DATE(E.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('E.created_by', $created_by);
        }
        $this->db->where('E.expenditure_head_id', $expenditure_head_id);
        $this->db->where('E.status', 1);
        return $this->db->get()->row();
    }

    // transaction report

    public function get_ipd_transaction($form_date, $to_date, $created_by) {
        $this->db->select('COUNT(P.id) as total_ipd_quantity, SUM(P.amount) as total_ipd_transaction');
        $this->db->from('ipd_advance_payments AS P');
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.payment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_diagnostic_transaction($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(P.id) as total_diagnostic_quantity, SUM(P.amount) as total_diagnostic_transaction');
        $this->db->from('invoice_payments AS P');
        $this->db->join('invoices AS I', 'I.id = P.invoice_id', 'left');
        if ($type == 1) {
            $this->db->where('I.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('I.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('I.commission_user_id', 0);
            $this->db->or_where('I.commission_user_id', NULL);
            $this->db->or_where('I.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('I.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.payment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('I.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('I.status', 1);
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row();
    }


    public function get_opd_transaction($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('COUNT(OP.id) as total_opd_quantity, SUM(OP.doctor_charge) as total_opd_transaction');
        $this->db->from('opd_patients AS OP');
        $this->db->join('patients AS P', 'P.user_id = OP.patient_id', 'left');
        $this->db->join('doctor_charges AS DC', 'DC.doctor_id = OP.doctor_id', 'left');
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('OP.reference_id', 0);
            $this->db->or_where('OP.reference_id', NULL);
            $this->db->or_where('OP.reference_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('OP.reference_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(OP.created_at) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('OP.reference_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('OP.created_by', $created_by);
        }
        $this->db->where('OP.payment_status', 'complete');
        $this->db->where('DC.is_opd', 1);
        return $this->db->get()->row();
    }



    // Transction Detatils Report

    public function get_diagnostic_transaction_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('I.*, P.patient_unique_id, P.name as patient');
        $this->db->from('invoices AS I');
        $this->db->join('patients AS P', 'P.user_id = I.patient_id', 'left');
        if ($type == 1) {
            $this->db->where('I.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('I.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('I.commission_user_id', 0);
            $this->db->or_where('I.commission_user_id', NULL);
            $this->db->or_where('I.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('I.commission_user_id >', 0);
        }

        if ($form_date && $to_date) {
            $this->db->where("DATE(I.bill_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('I.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_ipd_transaction_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('IAP.*, P.patient_unique_id, P.name as patient');
        $this->db->from('ipd_advance_payments AS IAP');
        $this->db->join('patients AS P', 'P.user_id = IAP.patient_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(IAP.payment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('IAP.created_by', $created_by);
        }
        $this->db->where('IAP.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_opd_transaction_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('OP.*, P.patient_unique_id, P.name as patient');
        $this->db->from('opd_patients AS OP');
        $this->db->join('patients AS P', 'P.user_id = OP.patient_id', 'left');
        $this->db->join('doctor_charges AS DC', 'DC.doctor_id = OP.doctor_id', 'left');
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('OP.reference_id', 0);
            $this->db->or_where('OP.reference_id', NULL);
            $this->db->or_where('OP.reference_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('OP.reference_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(OP.created_at) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('OP.reference_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('OP.created_by', $created_by);
        }
        $this->db->where('OP.payment_status', 'complete');
        $this->db->where('DC.is_opd', 1);
        return $this->db->get()->result();
    }

    public function get_sd_diagnostic_payment_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('P.*, E.id as sd_id, E.name as sd_name');
        $this->db->from('payments AS P');
        $this->db->join('employees AS E', 'E.user_id = P.commission_user_id', 'left');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'investigation');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_sd_ipd_payment_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('P.*, E.id as sd_id, E.name as sd_name');
        $this->db->from('payments AS P');
        $this->db->join('employees AS E', 'E.user_id = P.commission_user_id', 'left');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'ipd');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_surgeon_payment_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('P.*, E.id as surgeon_id, E.name as surgeon_name');
        $this->db->from('payments AS P');
        $this->db->join('employees AS E', 'E.user_id = P.commission_user_id', 'left');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'surgeon');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_anesthesia_payment_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('P.*, E.id as anesthesia_id, E.name as anesthesia_name');
        $this->db->from('payments AS P');
        $this->db->join('anesthetists AS E', 'E.user_id = P.commission_user_id', 'left');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'anesthesia');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_assistant_payment_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('P.*, E.id as assistant_id, E.name as assistant_name');
        $this->db->from('payments AS P');
        $this->db->join('assistants AS E', 'E.user_id = P.commission_user_id', 'left');
        if ($type == 1) {
            $this->db->where('P.paid_status', 'paid');
        }
        if ($type == 2) {
            $this->db->where('P.paid_status', 'unpaid');
        }
        if ($type == 3) {
            $this->db->group_start();
            $this->db->where('P.commission_user_id', 0);
            $this->db->or_where('P.commission_user_id', NULL);
            $this->db->or_where('P.commission_user_id', '');
            $this->db->group_end();
        }
        if ($type == 4) {
            $this->db->where('P.commission_user_id >', 0);
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(P.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($reference_id) {
            $this->db->where('P.commission_user_id', $reference_id);
        }
        if ($created_by) {
            $this->db->where('P.created_by', $created_by);
        }
        $this->db->where('P.invoice_type', 'assistant');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->result();
    }

    public function get_other_income_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('I.*, IH.name as income_head');
        $this->db->from('incomes AS I');
        $this->db->join('income_heads AS IH', 'IH.id = I.income_head_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(I.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('I.created_by', $created_by);
        }
        $this->db->where('I.status', '1');
        return $this->db->get()->result();
    }

    public function get_other_expense_detail($type, $form_date, $to_date, $reference_id, $created_by) {
        $this->db->select('E.*, EH.name as expense_head');
        $this->db->from('expenditures AS E');
        $this->db->join('expenditure_heads AS EH', 'EH.id = E.expenditure_head_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(E.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('E.created_by', $created_by);
        }
        $this->db->where('E.status', '1');
        return $this->db->get()->result();
    }
}
