<?php

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

class Stock_Model extends MY_Model {

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

    public function get_medicine_stock_list($limit, $offset, $keyword, $type_id, $category_id, $generic_id, $manufacturer_id, $medicine_id, $count) {
        $this->db->select('M.*, SUM(MPD.quantity) as total_in_quantity, MF.name as manufacturer');
        $this->db->from('medicines AS M');
        $this->db->join('medicine_purchase_details AS MPD', 'MPD.medicine_id = M.id', 'left');
        $this->db->join('medicine_purchases AS MP', 'MP.id = MPD.purchase_id', 'left');
        $this->db->join('manufacturers AS MF', 'MF.id = M.manufacturer_id', 'left');
        if ($keyword) {
            $this->db->like('M.medicine_name', $keyword, 'both');
        }
        if ($type_id) {
            $this->db->where('M.type_id', $type_id);
        }
        if ($category_id) {
            $this->db->where('M.category_id', $category_id);
        }
        if ($generic_id) {
            $this->db->where('M.generic_id', $generic_id);
        }
        if ($manufacturer_id) {
            $this->db->where('MF.id', $manufacturer_id);
        }
        if ($medicine_id) {
            $this->db->where('M.id', $medicine_id);
        }
        $this->db->where('MP.payment_status', 'complete');
        $this->db->group_by('M.id');
        $this->db->order_by('M.id', 'ASC');
        if ($count) {
            return $this->db->count_all_results();
        } else {
            $this->db->limit($limit, $offset);
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                return $query->result();
            }
        }
        return array();
    }

    public function get_sale_medicine($medicine_id) {
        $this->db->select('ID.*, SUM(ID.quantity) as total_sold_quantity');
        $this->db->from('medicine_invoice_details AS ID');
        $this->db->join('medicine_invoices AS I', 'I.id = ID.invoice_id', 'left');
        $this->db->where('ID.medicine_id', $medicine_id);
        $this->db->where('I.payment_status', 'complete');
        $this->db->group_by('ID.medicine_id');
        return $this->db->get()->row();
    }

    public function get_refund_medicine($medicine_id) {
        $this->db->select('MR.*, SUM(MR.quantity) as total_refund_quantity');
        $this->db->from('medicine_refund_details AS MR');
        $this->db->join('medicine_refunds AS R', 'R.id = MR.invoice_id', 'left');
        $this->db->where('MR.medicine_id', $medicine_id);
        $this->db->where('R.payment_status', 'complete');
        $this->db->group_by('MR.medicine_id');
        return $this->db->get()->row();
    }

    public function get_single_medicine($medicine_id) {
        $this->db->select('M.*, G.name as generic, MF.name as manufacturer, U.name as unit');
        $this->db->from('medicines AS M');
        $this->db->join('medicine_generics AS G', 'G.id = M.generic_id ', 'left');
        $this->db->join('manufacturers AS MF', 'MF.id = M.manufacturer_id ', 'left');
        $this->db->join('medicine_units AS U', 'U.id = M.unit_id ', 'left');
        $this->db->where('M.id', $medicine_id);
        return $this->db->get()->row();
    }

    public function get_total_purchase($medicine_id) {
        $this->db->select('SUM(ID.quantity) AS total_purchase');
        $this->db->from('medicine_purchase_details AS ID');
        $this->db->join('medicine_purchases AS MP', 'MP.id = ID.purchase_id', 'left');
        $this->db->where('ID.medicine_id', $medicine_id);
        $this->db->where('MP.payment_status', 'complete');
        return $this->db->get()->row()->total_purchase;
    }

    public function get_total_sale($medicine_id) {
        $this->db->select('SUM(ID.quantity) AS total_sale');
        $this->db->from('medicine_invoice_details AS ID');
        $this->db->join('medicine_invoices AS I', 'I.id = ID.invoice_id', 'left');
        $this->db->where('ID.medicine_id', $medicine_id);
        $this->db->where('I.payment_status', 'complete');
        return $this->db->get()->row()->total_sale;
    }

    public function get_purchase_report_list($limit, $offset, $search, $count) {
        $this->db->select('PD.*, P.id as invoice_id, P.custom_invoice_id, P.purchase_date, M.medicine_name, MF.name as manufacturer');
        $this->db->from('medicine_purchase_details AS PD');
        $this->db->join('medicine_purchases AS P', 'P.id = PD.purchase_id', 'left');
        $this->db->join('medicines AS M', 'M.id = PD.medicine_id', 'left');
        $this->db->join('manufacturers AS MF', 'MF.id = P.manufacturer_id', 'left');
        if ($search) {
            $keyword = $search['keyword'];
            if ($keyword) {
                $this->db->group_start();
                $this->db->like('M.medicine_name', $keyword, 'both');
                $this->db->or_like('P.custom_invoice_id', $keyword, 'both');
                $this->db->or_like('MF.name', $keyword, 'both');
                $this->db->or_like('PD.batch_no', $keyword, 'both');
                $this->db->or_like('PD.expiry_date', $keyword, 'both');
                $this->db->group_end();
            }
        }
        $this->db->where('PD.medicine_id', $this->uri->segment(4));
        $this->db->where('P.payment_status', 'complete');
        $this->db->order_by('P.id', 'DESC');
        if ($count) {
            return $this->db->count_all_results();
        } else {
            $this->db->limit($limit, $offset);
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                return $query->result();
            }
        }
        return array();
    }

    public function get_sale_report_list($limit, $offset, $search, $count) {
        $this->db->select('ID.*, I.id as invoice_id, I.custom_invoice_id, I.bill_date, I.walking, I.customer_id, I.customer_name, M.medicine_name, MF.name as manufacturer, P.name as customer, P.patient_unique_id');
        $this->db->from('medicine_invoice_details AS ID');
        $this->db->join('medicine_invoices AS I', 'I.id = ID.invoice_id', 'left');
        $this->db->join('medicines AS M', 'M.id = ID.medicine_id', 'left');
        $this->db->join('manufacturers AS MF', 'MF.id = M.manufacturer_id', 'left');
        $this->db->join('patients AS P', 'P.user_id = I.customer_id', 'left');
        if ($search) {
            $keyword = $search['keyword'];
            if ($keyword) {
                $this->db->group_start();
                $this->db->like('P.patient_unique_id', $keyword);
                $this->db->or_like('P.name', $keyword, 'both');
                $this->db->or_like('M.medicine_name', $keyword, 'both');
                $this->db->or_like('I.custom_invoice_id', $keyword, 'both');
                $this->db->group_end();
            }
        }
        $this->db->where('ID.medicine_id', $this->uri->segment(4));
        $this->db->where('I.payment_status', 'complete');
        $this->db->order_by('I.id', 'DESC');
        if ($count) {
            return $this->db->count_all_results();
        } else {
            $this->db->limit($limit, $offset);
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                return $query->result();
            }
        }
        return array();
    }
}
