<?php

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

class Reagent_Model extends MY_Model {

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

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

    public function get_total_manufacturer() {
        $this->db->select('COUNT(M.id) AS total_manufacture');
        $this->db->from('reagent_manufacturers AS M');
        $this->db->where('M.status', 1);
        return $this->db->get()->row()->total_manufacture;
    }

    public function get_total_medicine() {
        $this->db->select('COUNT(M.id) AS total_medicine');
        $this->db->from('reagent_medicines AS M');
        $this->db->where('M.status', 1);
        return $this->db->get()->row()->total_medicine;
    }

    public function get_total_medicine_purchase() {
        $this->db->select('SUM(MD.quantity) AS total_medicine_purchase');
        $this->db->from('reagent_purchase_details AS MD');
        $this->db->join('reagent_purchases AS MP', 'MP.id = MD.purchase_id', 'left');
        $this->db->where('MP.payment_status', 'complete');
        return $this->db->get()->row()->total_medicine_purchase;
    }

    public function get_total_medicine_sale() {
        $this->db->select('SUM(ID.quantity) AS total_medicine_sale');
        $this->db->from('reagent_invoice_details AS ID');
        $this->db->join('reagent_invoices AS I', 'I.id = ID.invoice_id ', 'left');
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_medicine_sale;
    }

    public function get_total_medicine_refund() {
        $this->db->select('SUM(RD.quantity) AS total_medicine_refund');
        $this->db->from('reagent_refund_details AS RD');
        $this->db->join('reagent_refunds AS R', 'R.id = RD.invoice_id ', 'left');
        $this->db->where('R.payment_status', 'complete');
        return $this->db->get()->row()->total_medicine_refund;
    }

    public function get_total_medicine_expire() {
        $this->db->select('SUM(MD.quantity) AS total_medicine_expire');
        $this->db->from('reagent_purchase_details AS MD');
        $this->db->join('reagent_purchases AS MP', 'MP.id = MD.purchase_id', 'left');
        $this->db->where('MD.expiry_date <', date('Y-m-d'));
        $this->db->where('MP.payment_status', 'complete');
        return $this->db->get()->row()->total_medicine_expire;
    }

    public function get_total_medicine_invoice() {
        $this->db->select('COUNT(I.id) AS total_medicine_invoice');
        $this->db->from('reagent_invoices AS I');
        $this->db->where('I.status', 1);
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_medicine_invoice;
    }

    public function get_total_sale() {
        $this->db->select('SUM(I.net_payable) AS total_sale');
        $this->db->from('reagent_invoices AS I');
        $this->db->where("MONTH(I.bill_date)", date('m'));
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_sale;
    }

    public function get_total_purchase() {
        $this->db->select('SUM(MP.net_payable) AS total_purchase');
        $this->db->from('reagent_purchases AS MP');
        $this->db->where("MONTH(MP.purchase_date)", date('m'));
        $this->db->where('MP.payment_status', 'complete');
        return $this->db->get()->row()->total_purchase;
    }

    public function get_invoice_list() {
        $this->db->select('I.*, P.patient_unique_id, P.name as customer');
        $this->db->from('reagent_invoices AS I');
        $this->db->join('patients AS P', 'P.user_id = I.customer_id', 'left');
        $this->db->where('I.payment_status', 'complete');
        $this->db->limit('10');
        $this->db->order_by('I.id', 'DESC');
        return $this->db->get()->result();
    }
}
