<?php

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

class Dashboard_Model extends MY_Model {

    function __construct() {
        parent::__construct();
    }

    public function total_opd_bill() {
        $this->db->select('SUM(OP.doctor_charge) AS total_opd_bill');
        $this->db->from('opd_patients AS OP');
        $this->db->where('OP.payment_status', 'complete');
        return $this->db->get()->row()->total_opd_bill;
    }

    public function total_ipd_bill() {
        $this->db->select('SUM(IP.total_bill) AS total_ipd_bill');
        $this->db->from('ipd_invoices AS IP');
        $this->db->where('IP.paid_status', 'paid');
        $this->db->where('IP.payment_status', 'complete');
        return $this->db->get()->row()->total_ipd_bill;
    }

    public function total_pharmacy_bill() {
        $this->db->select('SUM(MI.total_bill) AS total_pharmacy_bill');
        $this->db->from('medicine_invoices AS MI');
        $this->db->where('MI.paid_status', 'paid');
        $this->db->where('MI.payment_status', 'complete');
        return $this->db->get()->row()->total_pharmacy_bill;
    }

    public function total_investigation_bill() {
        $this->db->select('SUM(I.total_bill) AS total_investigation_bill');
        $this->db->from('invoices AS I');
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_investigation_bill;
    }

    public function total_pathology_bill() {
        $this->db->select('SUM(ID.total_rate) AS total_pathology_bill');
        $this->db->from('invoice_details AS ID');
        $this->db->join('invoices AS I', 'I.id = ID.invoice_id', 'left');
        $this->db->join('diagnostic_tests AS T', 'T.id = ID.investagation_id', 'left');
        $this->db->where('T.department_id', '1');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_pathology_bill;
    }

    public function total_radiology_bill() {
        $this->db->select('SUM(ID.total_rate) AS total_radiology_bill');
        $this->db->from('invoice_details AS ID');
        $this->db->join('invoices AS I', 'I.id = ID.invoice_id', 'left');
        $this->db->join('diagnostic_tests AS T', 'T.id = ID.investagation_id', 'left');
        $this->db->where('T.department_id', '2');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_radiology_bill;
    }

    // Total Income
    public function get_total_income() {
        $this->db->select('SUM(I.amount) AS get_total_income');
        $this->db->from('incomes AS I');
        $this->db->where('I.status', '1');
        return $this->db->get()->row()->get_total_income;
    }

    public function get_total_invoice() {
        $this->db->select('SUM(I.advanced) AS get_total_invoice');
        $this->db->from('invoices AS I');
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_invoice;
    }

    public function get_total_ipd_invoice() {
        $this->db->select('SUM(I.advanced) AS get_total_ipd_invoice');
        $this->db->from('ipd_invoices AS I');
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_ipd_invoice;
    }
    public function get_total_opd_patient() {
        $this->db->select('SUM(OP.hospital_commission) AS get_total_opd_patient');
        $this->db->from('opd_patients AS OP');
        $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();
        $this->db->where('OP.payment_status', 'complete');
        return $this->db->get()->row()->get_total_opd_patient;
    }
    public function get_total_medicine_invoice() {
        $this->db->select('SUM(I.advanced) AS get_total_medicine_invoice');
        $this->db->from('medicine_invoices AS I');
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_medicine_invoice;
    }


    // Total Expense
    public function get_total_expense() {
        $this->db->select('SUM(E.amount) AS get_total_expense');
        $this->db->from('expenditures AS E');
        $this->db->where('E.status', '1');
        return $this->db->get()->row()->get_total_expense;
    }

    public function get_total_payment() {
        $this->db->select('SUM(P.payable_amount) AS get_total_payment');
        $this->db->from('payments AS P');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row()->get_total_payment;
    }

    public function get_total_medicine_purchase() {
        $this->db->select('SUM(P.advanced) AS get_total_medicine_purchase');
        $this->db->from('medicine_purchases AS P');
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row()->get_total_medicine_purchase;
    }

    // Start Monthly Income SQL

    public function get_total_monthly_income($year, $month) {
        $this->db->select('SUM(I.amount) AS get_total_income');
        $this->db->from('incomes AS I');
        $this->db->where("YEAR(I.date)", $year);
        $this->db->where("MONTH(I.date)", $month);
        $this->db->where('I.status', '1');
        return $this->db->get()->row()->get_total_income;
    }

    public function get_total_monthly_invoice($year, $month) {
        $this->db->select('SUM(I.advanced) AS get_total_invoice');
        $this->db->from('invoices AS I');
        $this->db->where("YEAR(I.bill_date)", $year);
        $this->db->where("MONTH(I.bill_date)", $month);
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_invoice;
    }

    public function get_total_monthly_ipd_invoice($year, $month) {
        $this->db->select('SUM(I.advanced) AS get_total_ipd_invoice');
        $this->db->from('ipd_invoices AS I');
        $this->db->where("YEAR(I.bill_date)", $year);
        $this->db->where("MONTH(I.bill_date)", $month);
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_ipd_invoice;
    }

    public function get_total_monthly_opd_patient($year, $month) {
        $this->db->select('SUM(OP.hospital_commission) AS get_total_opd_patient');
        $this->db->from('opd_patients AS OP');
        $this->db->where("YEAR(OP.appointment_date)", $year);
        $this->db->where("MONTH(OP.appointment_date)", $month);
        $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();
        $this->db->where('OP.payment_status', 'complete');
        return $this->db->get()->row()->get_total_opd_patient;
    }

    public function get_total_monthly_medicine_invoice($year, $month) {
        $this->db->select('SUM(I.advanced) AS get_total_medicine_invoice');
        $this->db->from('medicine_invoices AS I');
        $this->db->where("YEAR(I.bill_date)", $year);
        $this->db->where("MONTH(I.bill_date)", $month);
        $this->db->where('I.paid_status', 'paid');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->get_total_medicine_invoice;
    }

    // End Monthly Income SQL


    // Start Monthly Expense SQL
    public function get_total_monthly_expenditure($year, $month) {
        $this->db->select('SUM(E.amount) AS get_total_expenditure');
        $this->db->from('expenditures AS E');
        $this->db->where("YEAR(E.date)", $year);
        $this->db->where("MONTH(E.date)", $month);
        $this->db->where('E.status', '1');
        return $this->db->get()->row()->get_total_expenditure;
    }

    public function get_total_monthly_payment($year, $month) {
        $this->db->select('SUM(P.payable_amount) AS get_total_payment');
        $this->db->from('payments AS P');
        $this->db->where("YEAR(P.date)", $year);
        $this->db->where("MONTH(P.date)", $month);
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row()->get_total_payment;
    }

    public function get_total_monthly_medicine_purchase($year, $month) {
        $this->db->select('SUM(P.advanced) AS get_total_medicine_purchase');
        $this->db->from('medicine_purchases AS P');
        $this->db->where("YEAR(P.purchase_date)", $year);
        $this->db->where("MONTH(P.purchase_date)", $month);
        $this->db->where('P.paid_status', 'paid');
        $this->db->where('P.payment_status', 'complete');
        return $this->db->get()->row()->get_total_medicine_purchase;
    }
    // End Monthly Expense SQL

    public function get_total_admin() {
        $this->db->select('COUNT(E.id) AS total_admin');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', ADMIN);
        return $this->db->get()->row()->total_admin;
    }

    public function get_total_doctor() {
        $this->db->select('COUNT(E.id) AS total_doctor');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', DOCTOR);
        return $this->db->get()->row()->total_doctor;
    }

    public function get_total_patient() {
        $this->db->select('COUNT(P.id) AS total_patient');
        $this->db->from('patients AS P');
        $this->db->join('users AS U', 'U.id = P.user_id', 'left');
        $this->db->where('P.status', 1);
        $this->db->where('U.role_id', PATIENT);
        return $this->db->get()->row()->total_patient;
    }

    public function get_total_accountant() {
        $this->db->select('COUNT(E.id) AS total_accountant');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', ACCOUNTANT);
        return $this->db->get()->row()->total_accountant;
    }

    public function get_total_pathologist() {
        $this->db->select('COUNT(E.id) AS total_pathologist');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', PATHOLOGIST);
        return $this->db->get()->row()->total_pathologist;
    }

    public function get_total_radiologist() {
        $this->db->select('COUNT(E.id) AS total_radiologist');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', RADIOLOGIST);
        return $this->db->get()->row()->total_radiologist;
    }

    public function get_total_nurse() {
        $this->db->select('COUNT(E.id) AS total_nurse');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', NURSE);
        return $this->db->get()->row()->total_nurse;
    }

    public function get_total_receptionist() {
        $this->db->select('COUNT(E.id) AS total_receptionist');
        $this->db->from('employees AS E');
        $this->db->join('users AS U', 'U.id = E.user_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->where('U.role_id', RECEPTIONIST);
        return $this->db->get()->row()->total_receptionist;
    }

    public function get_total_admitted_beds() {
        $this->db->select('COUNT(B.id) AS total_admitted_bed');
        $this->db->from('beds AS B');
        $this->db->where('B.status', 1);
        $this->db->where('B.is_active', 1);
        return $this->db->get()->row()->total_admitted_bed;
    }

    public function get_total_discharged_beds() {
        $this->db->select('COUNT(B.id) AS total_discharged_bed');
        $this->db->from('beds AS B');
        $this->db->where('B.status', 1);
        $this->db->where('B.is_active', 0);
        return $this->db->get()->row()->total_discharged_bed;
    }

    public function get_bed_list($group_id) {
        $this->db->select('B.*,');
        $this->db->from('beds AS B');
        $this->db->join('bed_groups AS BG', 'BG.id = B.group_id', 'left');
        $this->db->where('B.group_id', $group_id);
        $this->db->where('B.status', 1);
        $this->db->group_by('B.id');
        $this->db->order_by('B.sort_by', 'ASC');
        return $this->db->get()->result();
    }

    public function get_ipd_patient_info($bed_group_id, $bed_id) {
        $this->db->select('IP.id as ipd_id, IP.bed_empty, P.name as patient_name');
        $this->db->from('ipd_patients AS IP');
        $this->db->join('patients AS P', 'P.user_id = IP.patient_id', 'left');
        $this->db->where('IP.bed_group_id', $bed_group_id);
        $this->db->where('IP.bed_id', $bed_id);
        $this->db->where('IP.bed_empty', 0);
        $this->db->where('IP.status', 1);
        return $this->db->get()->row();
    }

    public function get_today_total_patient() {
        $this->db->select('COUNT(P.id) AS total_patient');
        $this->db->from('patients AS P');
        $this->db->join('users AS U', 'U.id = P.user_id', 'left');
        $this->db->where('P.status', 1);
        $this->db->where('U.role_id', PATIENT);
        $this->db->where('DATE(P.created_at)', date('Y-m-d'));
        return $this->db->get()->row()->total_patient;
    }

    public function get_notice_list() {
        $this->db->select('N.*, R.name as role');
        $this->db->from('notices AS N');
        $this->db->join('roles AS R', 'R.id = N.role_id', 'left');
        $this->db->where('N.status', 1);
        $this->db->limit(5);
        $this->db->order_by('N.id', 'DESC');
        return $this->db->get()->result();
    }

    public function get_event_list() {
        $this->db->select('E.*, R.name as role');
        $this->db->from('events AS E');
        $this->db->join('roles AS R', 'R.id = E.role_id', 'left');
        $this->db->where('E.status', 1);
        $this->db->limit(5);
        $this->db->order_by('E.id', 'DESC');
        return $this->db->get()->result();
    }
}
