<?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, STAFF, SUPERVISOR));
        } else {
            $this->db->where_in('U.role_id', array(ADMIN, ACCOUNTANT, RECEPTIONIST, STAFF, SUPERVISOR));
        }
        $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_incomes($form_date, $to_date, $created_by)
    {
        $this->db->select('COUNT(I.id) as total_income_quantity, SUM(I.amount) as total_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.status', 1);
        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_opd_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.type', 'opd');
        $this->db->where('I.status', 1);
        return $this->db->get()->row();
    }

    public function get_income_head_ipd_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.type', 'ipd');
        $this->db->where('I.status', 1);
        return $this->db->get()->row();
    }

    public function get_income_head_other_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.type', 'other');
        $this->db->where('I.status', 1);
        return $this->db->get()->row();
    }

    public function get_expenditures($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.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_opd_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.type', 'opd');
        $this->db->where('E.status', 1);
        return $this->db->get()->row();
    }

    public function get_expenditure_head_ipd_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.type', 'ipd');
        $this->db->where('E.status', 1);
        return $this->db->get()->row();
    }

    public function get_expenditure_head_other_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.type', 'other');
        $this->db->where('E.status', 1);
        return $this->db->get()->row();
    }

    // transaction report

    public function get_opd_transaction($form_date, $to_date, $created_by)
    {
        $this->db->select('COUNT(OP.id) as total_opd_quantity, SUM(OP.hospital_commission) as total_opd_transaction');
        $this->db->from('opd_patients AS OP');
        if ($form_date && $to_date) {
            $this->db->where("DATE(OP.created_at) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('OP.created_by', $created_by);
        }
        $this->db->where('OP.hospital_commission >', '0');
        $this->db->where('OP.payment_status', 'complete');
        return $this->db->get()->row();
    }

    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_investigations($type, $form_date, $to_date, $reference_id, $created_by)
    {
        $this->db->select('
            I.id, I.custom_invoice_id, I.bill_date, I.total_bill, I.discount, I.net_payable, I.advanced, I.due_amount,
            C.name as commission,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address');
        $this->db->from('invoices AS I');
        $this->db->join('patients AS P', 'P.user_id = I.patient_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = I.created_by', '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.status', 1);
        $this->db->where('I.payment_status', 'complete');
        $this->db->order_by('I.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_due_investigations($bill_type, $type, $form_date, $to_date, $reference_id, $created_by)
    {
        $this->db->select('
            IP.id, IP.collection_type,IP.payment_date,IP.amount,IP.second_less,IP.due,
            C.name as creator,
            I.id, I.custom_invoice_id, I.bill_date, I.total_bill, I.discount, I.net_payable, I.advanced, I.due_amount,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address');
        $this->db->from('invoice_payments AS IP');
        $this->db->join('invoices AS I', 'I.id = IP.invoice_id', 'left');
        $this->db->join('patients AS P', 'P.user_id = I.patient_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = IP.created_by', '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(IP.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('IP.created_by', $created_by);
        }
        $this->db->where('IP.status', 1);
        $this->db->where('IP.collection_type', $bill_type);
        $this->db->where('I.payment_status', 'complete');
        $this->db->order_by('IP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_vd_payments($type, $form_date, $to_date, $reference_id, $created_by)
    {
        $this->db->select('
            PM.id, PM.commission_amount,PM.payable_amount,PM.due_amount,
            D.name as doctor,R.name as reference,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address');
        $this->db->from('payments AS PM');
        $this->db->join('invoices AS I', 'I.id = PM.invoice_id', 'left');
        $this->db->join('patients AS P', 'P.user_id = PM.patient_id', 'left');
        $this->db->join('employees AS D', 'D.user_id = I.ref_dr_id', 'left');
        $this->db->join('employees AS R', 'R.user_id = PM.commission_user_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(PM.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('PM.created_by', $created_by);
        }
        $this->db->where('PM.status', 1);
        $this->db->where('PM.invoice_type', 'investigation');
        $this->db->where('PM.payment_status', 'complete');
        $this->db->order_by('PM.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_opd_other_incomes($form_date, $to_date, $created_by)
    {
        $this->db->select('
            I.id, I.custom_invoice_id,I.name,I.amount,I.date,
            IH.name as income_head,
            C.name as creator');
        $this->db->from('incomes AS I');
        $this->db->join('income_heads AS IH', 'IH.id = I.income_head_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = I.created_by', '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->where('I.type', 'opd');
        $this->db->order_by('I.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_opd_other_expenses($form_date, $to_date, $created_by)
    {
        $this->db->select('
            I.id, I.custom_invoice_id,I.name,I.amount,I.date,
            IH.name as income_head,
            C.name as creator');
        $this->db->from('expenditures AS I');
        $this->db->join('expenditure_heads AS IH', 'IH.id = I.expenditure_head_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = I.created_by', '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->where('I.type', 'opd');
        $this->db->order_by('I.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_invoices($form_date, $to_date, $created_by)
    {
        $this->db->select('
            IP.id, IP.payment_date,IP.amount,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            D.name as doctor,
            R.name as reference,
            I.total_bill,I.discount,I.advanced,I.due_amount,
            C.name as creator');
        $this->db->from('ipd_payments AS IP');
        $this->db->join('ipd_invoices AS I', 'I.id = IP.invoice_id', 'left');
        $this->db->join('patients AS P', 'P.user_id = I.patient_id', 'left');
        $this->db->join('ipd_patients AS IPP', 'IPP.id = IP.ipd_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = IP.created_by', 'left');
        $this->db->join('employees AS R', 'R.user_id = IPP.reference_id', 'left');
        $this->db->join('employees AS D', 'D.user_id = IPP.doctor_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(IP.payment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('IP.created_by', $created_by);
        }
        $this->db->where('IP.payment_status', 'complete');
        $this->db->where('I.payment_status', 'complete');
        $this->db->where('IP.collection_type', 1);
        $this->db->order_by('IP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_advance_payments($form_date, $to_date, $created_by)
    {
        $this->db->select('
            IAP.id, IAP.payment_date,IAP.amount,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            C.name as creator');
        $this->db->from('ipd_advance_payments AS IAP');
        $this->db->join('patients AS P', 'P.user_id = IAP.patient_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = IAP.created_by', '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_type', 0);
        $this->db->where('IAP.payment_status', 'complete');
        $this->db->order_by('IAP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_due_invoices($form_date, $to_date, $created_by)
    {
        $this->db->select('
            IP.id, IP.payment_date,IP.amount,IP.second_less,IP.due,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            C.name as creator');
        $this->db->from('ipd_payments AS IP');
        $this->db->join('ipd_invoices AS I', 'I.id = IP.invoice_id', 'left');
        $this->db->join('patients AS P', 'P.user_id = I.patient_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = IP.created_by', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(IP.payment_date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('IP.created_by', $created_by);
        }
        $this->db->where('IP.collection_type', 2);
        $this->db->where('IP.payment_status', 'complete');
        $this->db->where('I.payment_status', 'complete');
        $this->db->order_by('IP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_other_incomes($form_date, $to_date, $created_by)
    {
        $this->db->select('
            I.id, I.custom_invoice_id,I.name,I.amount,I.date,I.payment_method,
            IH.name as income_head,
            C.name as creator');
        $this->db->from('incomes AS I');
        $this->db->join('income_heads AS IH', 'IH.id = I.income_head_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = I.created_by', '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->where('I.type', 'ipd');
        $this->db->order_by('I.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_other_expenses($form_date, $to_date, $created_by)
    {
        $this->db->select('
            I.id, I.custom_invoice_id,I.name,I.amount,I.date,I.payment_method,
            IH.name as income_head,
            C.name as creator');
        $this->db->from('expenditures AS I');
        $this->db->join('expenditure_heads AS IH', 'IH.id = I.expenditure_head_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = I.created_by', '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->where('I.type', 'ipd');
        $this->db->order_by('I.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_surgeons($form_date, $to_date, $created_by)
    {
        $this->db->select('
            SP.custom_invoice_id,SP.commission_amount,SP.payable_amount,SP.due_amount,SP.date,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            D.name as doctor, DC.ot_charge as doctor_charge');
        $this->db->from('payments AS SP');
        $this->db->join('patients AS P', 'P.user_id = SP.patient_id', 'left');
        $this->db->join('employees AS D', 'D.user_id = SP.commission_user_id', 'left');
        $this->db->join('doctor_charges AS DC', 'DC.doctor_id = SP.commission_user_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(SP.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('SP.created_by', $created_by);
        }
        $this->db->where('SP.invoice_type', 'surgeon');
        $this->db->where('SP.paid_status', 'paid');
        $this->db->where('SP.payment_status', 'complete');
        $this->db->order_by('SP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_anesthetists($form_date, $to_date, $created_by)
    {
        $this->db->select('
            SP.custom_invoice_id,SP.commission_amount,SP.payable_amount,SP.due_amount,SP.date,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            D.name as doctor, D.charge as doctor_charge');
        $this->db->from('payments AS SP');
        $this->db->join('patients AS P', 'P.user_id = SP.patient_id', 'left');
        $this->db->join('anesthetists AS D', 'D.user_id = SP.commission_user_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(SP.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('SP.created_by', $created_by);
        }
        $this->db->where('SP.invoice_type', 'anesthesia');
        $this->db->where('SP.paid_status', 'paid');
        $this->db->where('SP.payment_status', 'complete');
        $this->db->order_by('SP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_assistants($form_date, $to_date, $created_by)
    {
        $this->db->select('
            SP.custom_invoice_id,SP.commission_amount,SP.payable_amount,SP.due_amount,SP.date,
            P.patient_unique_id, P.name as patient_name, P.phone, P.address,
            D.name as doctor, D.charge as doctor_charge');
        $this->db->from('payments AS SP');
        $this->db->join('patients AS P', 'P.user_id = SP.patient_id', 'left');
        $this->db->join('assistants AS D', 'D.user_id = SP.commission_user_id', 'left');
        if ($form_date && $to_date) {
            $this->db->where("DATE(SP.date) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($created_by) {
            $this->db->where('SP.created_by', $created_by);
        }
        $this->db->where('SP.invoice_type', 'assistant');
        $this->db->where('SP.paid_status', 'paid');
        $this->db->where('SP.payment_status', 'complete');
        $this->db->order_by('SP.id', 'ASC');
        return $this->db->get()->result();
    }

    public function get_visitor_list($search, $form_date, $to_date, $pregnancy, $treatment, $card, $package, $land, $operation, $test, $created_by)
    {
        $this->db->select('V.*, COUNT(V.id) as total_visit, SUM(V.p_number) as total_family_patient, COUNT(V.yes_no2) as total_pregnancy, COUNT(V.yes_no3) as total_emergency, COUNT(V.yes_no4) as total_health_card, 
        COUNT(V.yes_no5) as total_health_package, COUNT(V.yes_no6) as total_medicine, COUNT(V.yes_no7) as total_land, COUNT(V.yes_no9) as total_test, COUNT(V.yes_no8) as total_surgery, C.name as created_by');
        $this->db->from('visitors AS V');
        $this->db->join('visitor_purposes AS VP', 'VP.id = V.purpose_id', 'left');
        $this->db->join('employees AS C', 'C.user_id = V.created_by', 'left');

        if ($this->session->userdata('role_id') !== SUPER_ADMIN  && $this->session->userdata('role_id') !== ADMIN) {
            $this->db->where('V.created_by', $this->session->userdata('user_id'));
        }
        if ($form_date && $to_date) {
            $this->db->where("DATE(V.created_at) BETWEEN '$form_date' AND '$to_date'");
        }
        if ($pregnancy) {
            $this->db->where('V.yes_no2', 'yes');
        }
        if ($treatment) {
            $this->db->where('V.yes_no3', $treatment);
        }
        if ($card) {
            $this->db->where('V.yes_no4', $card);
        }
        if ($package) {
            $this->db->where('V.yes_no5', $package);
        }
        if ($land) {
            $this->db->where('V.yes_no7', $land);
        }
        if ($operation) {
            $this->db->where('V.yes_no8', $operation);
        }
        if ($test) {
            $this->db->where('V.yes_no9', $test);
        }
        if ($created_by) {
            $this->db->where('V.created_by', $created_by);
        }
        if ($search) {
            $this->db->like('V.phone', $search);
            $this->db->or_like('V.name', $search);
            $this->db->or_like('V.date', $search);
            $this->db->or_like('VP.name', $search);
        }
        $this->db->group_by('V.created_by');
        $this->db->order_by('V.id', 'DESC');
        return $this->db->get()->result();
    }
}
