<?php

/**
 * Description of purchase_model
 *
 * @author NaYeM
 */
class Purchase_model extends MY_Model
{

    public $_table_name;
    public $_order_by;
    public $_primary_key;

    private function apply_purchase_sidebar_scope($search = '')
    {
        $total_expr = "GREATEST((COALESCE((SELECT SUM(tbl_purchase_items.total_cost) FROM tbl_purchase_items WHERE tbl_purchase_items.purchase_id = tbl_purchases.purchase_id), 0) - COALESCE(tbl_purchases.discount_total, 0) + COALESCE(tbl_purchases.adjustment, 0) + COALESCE((SELECT SUM(tbl_purchase_items.item_tax_total) FROM tbl_purchase_items WHERE tbl_purchase_items.purchase_id = tbl_purchases.purchase_id), 0)), 0)";
        $paid_expr = "COALESCE((SELECT SUM(tbl_purchase_payments.amount) FROM tbl_purchase_payments WHERE tbl_purchase_payments.purchase_id = tbl_purchases.purchase_id), 0)";

        $this->db->from('tbl_purchases');
        $this->db->join('tbl_suppliers', 'tbl_suppliers.supplier_id = tbl_purchases.supplier_id', 'left');
        $this->staff_query('tbl_purchases');

        if ($search !== '') {
            $this->db->group_start()
                ->like('tbl_purchases.reference_no', $search)
                ->or_like('tbl_suppliers.name', $search)
                ->group_end();
        }

        return array($total_expr, $paid_expr);
    }

    public function count_purchase_sidebar($search = '')
    {
        $this->apply_purchase_sidebar_scope($search);
        return (int) $this->db->count_all_results();
    }

    public function get_purchase_sidebar_page($limit = 12, $offset = 0, $search = '')
    {
        list($total_expr, $paid_expr) = $this->apply_purchase_sidebar_scope($search);
        $this->db->select('tbl_purchases.purchase_id, tbl_purchases.reference_no, tbl_purchases.status, tbl_purchases.purchase_date, tbl_purchases.due_date, tbl_suppliers.name AS supplier_name', false);
        $this->db->select($total_expr . ' AS total_amount', false);
        $this->db->select($paid_expr . ' AS paid_amount', false);
        $this->db->order_by('tbl_purchases.purchase_id', 'DESC');
        $this->db->limit((int) $limit, (int) $offset);
        return $this->db->get()->result();
    }

    public function get_purchase_sidebar_purchase($purchase_id)
    {
        if (empty($purchase_id)) {
            return null;
        }
        list($total_expr, $paid_expr) = $this->apply_purchase_sidebar_scope('');
        $this->db->select('tbl_purchases.purchase_id, tbl_purchases.reference_no, tbl_purchases.status, tbl_purchases.purchase_date, tbl_purchases.due_date, tbl_suppliers.name AS supplier_name', false);
        $this->db->select($total_expr . ' AS total_amount', false);
        $this->db->select($paid_expr . ' AS paid_amount', false);
        $this->db->where('tbl_purchases.purchase_id', (int) $purchase_id);
        return $this->db->get()->row();
    }

    public function get_payment_status($purchase_id, $unmark = null)
    {
        if (!empty($purchase_id)) {
            $tax = $this->get_purchase_tax_amount($purchase_id);
            $discount = $this->get_purchase_discount($purchase_id);
            $invoice_cost = $this->get_purchase_cost($purchase_id);
            $payment_made = round($this->get_purchase_paid_amount($purchase_id), 2);
            $due = round(((($invoice_cost - $discount) + $tax) - $payment_made));
            $purchase_info = $this->check_by(array('purchase_id' => $purchase_id), 'tbl_purchases');
            if ($purchase_info->status == 'Cancelled' && empty($unmark)) {
                return ('cancelled');
            } elseif ($payment_made < 1) {
                return ('not_paid');
            } elseif ($due <= 0) {
                return ('fully_paid');
            } else {
                return ('partially_paid');
            }
        }
    }

    function calculate_to($value, $purchase_id)
    {
        switch ($value) {
            case 'purchase_cost':
                return $this->get_purchase_cost($purchase_id);
                break;
            case 'tax':
                return $this->get_purchase_tax_amount($purchase_id);
                break;
            case 'discount':
                return $this->get_purchase_discount($purchase_id);
                break;
            case 'paid_amount':
                return $this->get_purchase_paid_amount($purchase_id);
                break;
            case 'purchase_due':
                return $this->get_purchase_due_amount($purchase_id);
                break;
            case 'total':
                return $this->get_purchase_total_amount($purchase_id);
                break;
        }
    }

    function get_purchase_cost($purchase_id)
    {
        $this->db->select_sum('total_cost');
        $this->db->where('purchase_id', $purchase_id);
        $this->db->from('tbl_purchase_items');
        $query_result = $this->db->get();
        $cost = $query_result->row();
        if (!empty($cost->total_cost)) {
            $result = $cost->total_cost;
        } else {
            $result = '0';
        }
        return $result;
    }

    public function get_purchase_tax_amount($purchase_id)
    {
        $purchase_info = $this->check_by(array('purchase_id' => $purchase_id), 'tbl_purchases');
        $tax = 0;
        if (!empty($purchase_info->total_tax)) {
            $tax_info = json_decode($purchase_info->total_tax);

            if (is_object($tax_info) && !empty($tax_info->total_tax) && is_array($tax_info->total_tax)) {
                foreach ($tax_info->total_tax as $v_tax_info) {
                    $tax += (float) $v_tax_info;
                }
            } elseif (is_array($tax_info) && !empty($tax_info['total_tax']) && is_array($tax_info['total_tax'])) {
                foreach ($tax_info['total_tax'] as $v_tax_info) {
                    $tax += (float) $v_tax_info;
                }
            } elseif (is_numeric($tax_info)) {
                $tax = (float) $tax_info;
            } elseif (is_numeric($purchase_info->total_tax)) {
                $tax = (float) $purchase_info->total_tax;
            }
        }
        return $tax;
    }

    public function get_purchase_discount($purchase_id)
    {
        $purchase_info = $this->check_by(array('purchase_id' => $purchase_id), 'tbl_purchases');
        if (!empty($purchase_info)) {
            return $purchase_info->discount_total;
        }
    }

    public function get_purchase_paid_amount($purchase_id)
    {

        $this->db->select_sum('amount');
        $this->db->where('purchase_id', $purchase_id);
        $this->db->from('tbl_purchase_payments');
        $query_result = $this->db->get();
        $amount = $query_result->row();
        //        $tax = $this->get_purchase_tax_amount($purchase_id);
        if (!empty($amount->amount)) {
            $result = $amount->amount;
        } else {
            $result = '0';
        }
        return $result;
    }

    public function get_purchase_due_amount($purchase_id)
    {

        $purchase_info = $this->check_by(array('purchase_id' => $purchase_id), 'tbl_purchases');
        if (!empty($purchase_info)) {
            $tax = $this->get_purchase_tax_amount($purchase_id);
            $discount = $this->get_purchase_discount($purchase_id);
            $purchase_cost = $this->get_purchase_cost($purchase_id);
            $payment_made = $this->get_purchase_paid_amount($purchase_id);
            $due_amount = (($purchase_cost - $discount) + $tax) - $payment_made + $purchase_info->adjustment;
            if ($due_amount <= 0) {
                $due_amount = 0;
            }
        } else {
            $due_amount = 0;
        }
        return $due_amount;
    }

    public function get_purchase_total_amount($purchase_id)
    {

        $purchase_info = $this->check_by(array('purchase_id' => $purchase_id), 'tbl_purchases');
        $tax = $this->get_purchase_tax_amount($purchase_id);
        $discount = $this->get_purchase_discount($purchase_id);
        $purchase_cost = $this->get_purchase_cost($purchase_id);
        //        $payment_made = $this->get_purchase_paid_amount($purchase_id);

        $total_amount = $purchase_cost - $discount + $tax + $purchase_info->adjustment;
        if ($total_amount <= 0) {
            $total_amount = 0;
        }
        return $total_amount;
    }

    function ordered_items_by_id($id, $json = null)
    {
        $rows = $this->db->where('purchase_id', $id)->order_by('order', 'asc')->get('tbl_purchase_items')->result();
        if (!empty($json)) {
            if (!empty($rows)) {
                foreach ($rows as $row) {                    
                    $row->qty = $row->quantity;
                    $row->rate = $row->unit_cost;
                    $row->cost_price = $row->unit_cost;
                    $row->new_itmes_id = $row->saved_items_id;
                    $row->taxname = !empty($row->item_tax_name) ? json_decode((string) $row->item_tax_name) : array();
                    $pr[] = $row;
                }
                return json_encode($pr);
            }
        } else {
            return $rows;
        }
    }
}
