<?php

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

class Sale_Model extends MY_Model {

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

    function duplicate_check($name, $phone, $id = null) {
        if ($id) {
            $this->db->where_not_in('user_id', $id);
        }
        $this->db->where('name', $name);
        $this->db->where('phone', $phone);
        return $this->db->get('patients')->num_rows();
    }

    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, PHARMACIST));
        } else {
            $this->db->where_in('U.role_id', array(ADMIN, ACCOUNTANT, PHARMACIST));
        }
        $this->db->order_by('E.id ', 'DESC');
        return $this->db->get()->result();
    }

    public function get_sale_invoice_list($limit, $offset, $keyword, $type, $form_date, $to_date, $created_by, $count) {
        $this->db->select('I.*, C.patient_unique_id, C.name as customer, E.name as employee');
        $this->db->from('medicine_invoices AS I');
        $this->db->join('patients AS C', 'C.user_id = I.customer_id', 'left');
        $this->db->join('employees AS E', 'E.user_id = I.created_by', 'left');
        if ($keyword) {
            $this->db->like('C.patient_unique_id ', $keyword);
            $this->db->or_like('I.custom_invoice_id ', $keyword);
            $this->db->or_like('I.customer_name', $keyword, 'both');
        }
        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->where('I.walking', '1');
        }
        if ($type == 4) {
            $this->db->where('I.customer_id >', '0');
        }
        if ($type == 5) {
            $this->db->where('I.payment_status', 'complete');
        }
        if ($type == 6) {
            $this->db->where('I.payment_status', 'rejected');
        }
        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->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();
    }

    public function get_medicine_search($search) {
        $this->db->select('M.*, MP.batch_no');
        $this->db->from('medicine_purchase_details AS MP');
        $this->db->join('medicines AS M', 'M.id = MP.medicine_id', 'left');
        if ($search) {
            $this->db->like('M.medicine_name', $search, 'both');
            $this->db->or_like('M.code', $search, 'both');
        }
        $this->db->order_by('M.medicine_name', 'ASC');
        $this->db->limit(10);
        $data = $this->db->get()->result();
        $new_array = [];
        $used = [];
        foreach ($data as $key => $item) {
            $sale = $this->get_total_sale($item->id, $item->batch_no);
            $sale = ($sale && isset($sale->total)) ? (int) $sale->total : 0;
            $stock = $this->get_total_purchase($item->id, $item->batch_no);
            $stock = ($stock && isset($stock->total)) ? (int) $stock->total : 0;
            $batch = [
                'bid' => $item->batch_no,
                'mid' => $item->id,
                'sale' => (int) $sale,
                'stock' => (int) $stock,
            ];
            if (!in_array($item->id, $used)) {
                $used[] = $item->id;
                $new_array[$item->id] = $item;
                $new_array[$item->id]->batches = [];
            }
            if (($stock - $sale) > 0) {
                $new_array[$item->id]->batches[] = $batch;
            }
        }
        $data = [];
        foreach ($new_array as $item) {
            $data[] = $item;
        }
        return $data;
    }

    public function get_total_sale($medicine_id, $batch_no) {
        $this->db->select('SUM(MD.quantity) as total');
        $this->db->from('medicine_invoice_details AS MD');
        $this->db->join('medicine_invoices AS MI', 'MI.id = MD.invoice_id ', 'left');
        $this->db->where('MD.medicine_id', $medicine_id);
        $this->db->where('MD.batch_no', $batch_no);
        $this->db->where('MI.payment_status', 'complete');
        return $this->db->get()->row();
    }
    public function get_total_purchase($medicine_id, $batch_no) {
        $this->db->select('SUM(MD.quantity) as total');
        $this->db->from('medicine_purchase_details AS MD');
        $this->db->join('medicine_purchases AS MP', 'MP.id = MD.purchase_id ', 'left');
        $this->db->where('MD.medicine_id', $medicine_id);
        $this->db->where('MD.batch_no', $batch_no);
        $this->db->where('MP.payment_status', 'complete');
        return $this->db->get()->row();
    }

    public function get_customer_search($search) {
        $this->db->select('P.*');
        $this->db->from('patients AS P');
        if ($search) {
            $this->db->like('P.patient_unique_id', $search);
            $this->db->or_like('P.name', $search, 'both');
        }
        $this->db->order_by('P.name', 'ASC');
        $this->db->limit(10);
        return $this->db->get()->result();
    }

    public function get_medicine_invoice_list($invoice_id) {
        $this->db->select('MD.*, M.medicine_name, C.name as category, PD.expiry_date');
        $this->db->from('medicine_invoice_details AS MD');
        $this->db->join('medicines AS M', 'M.id = MD.medicine_id', 'left');
        $this->db->join('medicine_categories AS C', 'C.id = M.category_id', 'left');
        $this->db->join('medicine_purchase_details AS PD', 'PD.medicine_id = MD.medicine_id AND PD.batch_no = MD.batch_no', 'left');
        $this->db->where('MD.invoice_id ', $invoice_id);
        $this->db->order_by('MD.id ', 'ASC');
        return $this->db->get()->result();
    }

    public function get_medicine_sale_payment_list($invoice_id) {
        $this->db->select('P.*, E.name as employee');
        $this->db->from('medicine_invoice_payments AS P');
        $this->db->join('employees AS E', 'E.user_id = P.created_by', 'left');
        $this->db->where('P.invoice_id ', $invoice_id);
        $this->db->order_by('P.id ', 'DESC');
        return $this->db->get()->result();
    }
}
