<?php

class Invoice_Model extends MY_Model
{
    public $_table_name;
    public $_order_by;
    public $_primary_key;

    public function extract_total_tax_amount($invoice_total_tax)
    {
        if (empty($invoice_total_tax)) {
            return 0;
        }

        if (is_numeric($invoice_total_tax)) {
            return (float) $invoice_total_tax;
        }

        $tax_info = is_string($invoice_total_tax) ? json_decode($invoice_total_tax) : $invoice_total_tax;
        if (is_numeric($tax_info)) {
            return (float) $tax_info;
        }
        if (!is_object($tax_info) || empty($tax_info->total_tax)) {
            return 0;
        }

        $tax = 0;
        foreach ((array) $tax_info->total_tax as $value) {
            $tax += (float) $value;
        }

        return $tax;
    }

    public function resolve_invoice_status_from_summary($invoice_info, $payment_made, $due)
    {
        if (empty($invoice_info)) {
            return lang('not_paid');
        }

        $status = strtolower(trim((string) $invoice_info->status));
        if ($status === 'draft') {
            return lang('draft');
        } elseif ($status === 'cancelled') {
            return lang('cancelled');
        } elseif ((float) $payment_made < 1) {
            return lang('not_paid');
        } elseif ((float) $due <= 0) {
            return lang('fully_paid');
        }

        return lang('partially_paid');
    }

    public function get_invoice_financial_map($invoice_rows)
    {
        $financial_map = array();
        if (empty($invoice_rows)) {
            return $financial_map;
        }

        $invoice_index = array();
        $invoice_ids = array();
        foreach ($invoice_rows as $invoice_row) {
            if (empty($invoice_row->invoices_id)) {
                continue;
            }
            $invoice_id = (int) $invoice_row->invoices_id;
            $invoice_ids[] = $invoice_id;
            $invoice_index[$invoice_id] = $invoice_row;
        }

        $invoice_ids = array_values(array_unique($invoice_ids));
        if (empty($invoice_ids)) {
            return $financial_map;
        }

        $item_totals = array();
        $item_rows = $this->db
            ->select('invoices_id, SUM(total_cost) AS item_total')
            ->from('tbl_items')
            ->where_in('invoices_id', $invoice_ids)
            ->group_by('invoices_id')
            ->get()
            ->result();
        foreach ($item_rows as $item_row) {
            $item_totals[(int) $item_row->invoices_id] = (float) $item_row->item_total;
        }

        $payment_totals = array();
        $payment_rows = $this->db
            ->select('invoices_id, SUM(amount) AS payment_total')
            ->from('tbl_payments')
            ->where_in('invoices_id', $invoice_ids)
            ->group_by('invoices_id')
            ->get()
            ->result();
        foreach ($payment_rows as $payment_row) {
            $payment_totals[(int) $payment_row->invoices_id] = (float) $payment_row->payment_total;
        }

        foreach ($invoice_index as $invoice_id => $invoice_row) {
            $item_total = isset($item_totals[$invoice_id]) ? $item_totals[$invoice_id] : 0;
            $payment_total = isset($payment_totals[$invoice_id]) ? $payment_totals[$invoice_id] : 0;
            $discount_total = isset($invoice_row->discount_total) ? (float) $invoice_row->discount_total : 0;
            $adjustment = isset($invoice_row->adjustment) ? (float) $invoice_row->adjustment : 0;
            $tax_total = $this->extract_total_tax_amount(isset($invoice_row->total_tax) ? $invoice_row->total_tax : null);

            $invoice_total = $item_total - $discount_total + $tax_total + $adjustment;
            if ($invoice_total < 0) {
                $invoice_total = 0;
            }

            $invoice_due = $invoice_total - $payment_total;
            if ($invoice_due < 0) {
                $invoice_due = 0;
            }

            $financial_map[$invoice_id] = array(
                'item_total' => $item_total,
                'tax_total' => $tax_total,
                'discount_total' => $discount_total,
                'adjustment' => $adjustment,
                'paid_amount' => $payment_total,
                'total' => $invoice_total,
                'due' => $invoice_due,
                'status' => $this->resolve_invoice_status_from_summary($invoice_row, $payment_total, $invoice_due),
            );
        }

        return $financial_map;
    }

    private function apply_invoice_sidebar_scope($search = '')
    {
        $total_expr = "GREATEST((COALESCE((SELECT SUM(tbl_items.total_cost) FROM tbl_items WHERE tbl_items.invoices_id = tbl_invoices.invoices_id), 0) - COALESCE(tbl_invoices.discount_total, 0) + COALESCE(tbl_invoices.adjustment, 0) + COALESCE((SELECT SUM(tbl_items.item_tax_total) FROM tbl_items WHERE tbl_items.invoices_id = tbl_invoices.invoices_id), 0)), 0)";
        $paid_expr = "COALESCE((SELECT SUM(tbl_payments.amount) FROM tbl_payments WHERE tbl_payments.invoices_id = tbl_invoices.invoices_id), 0)";

        $this->db->from('tbl_invoices');
        $this->db->join('tbl_client', 'tbl_client.client_id = tbl_invoices.client_id', 'left');
        $this->staff_query('tbl_invoices');

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

        return array($total_expr, $paid_expr);
    }

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

    public function get_invoice_sidebar_page($limit = 12, $offset = 0, $search = '')
    {
        list($total_expr, $paid_expr) = $this->apply_invoice_sidebar_scope($search);
        $this->db->select('tbl_invoices.invoices_id, tbl_invoices.client_id, tbl_invoices.reference_no, tbl_invoices.status, tbl_invoices.emailed, tbl_invoices.invoice_date, tbl_invoices.due_date, tbl_invoices.currency, tbl_client.name AS client_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_invoices.invoices_id', 'DESC');
        $this->db->limit((int) $limit, (int) $offset);
        return $this->db->get()->result();
    }

    public function get_invoice_sidebar_invoice($invoice_id)
    {
        if (empty($invoice_id)) {
            return null;
        }

        list($total_expr, $paid_expr) = $this->apply_invoice_sidebar_scope('');
        $this->db->select('tbl_invoices.invoices_id, tbl_invoices.client_id, tbl_invoices.reference_no, tbl_invoices.status, tbl_invoices.emailed, tbl_invoices.invoice_date, tbl_invoices.due_date, tbl_invoices.currency, tbl_client.name AS client_name', false);
        $this->db->select($total_expr . ' AS total_amount', false);
        $this->db->select($paid_expr . ' AS paid_amount', false);
        $this->db->where('tbl_invoices.invoices_id', (int) $invoice_id);
        return $this->db->get()->row();
    }

    public function count_invoice_payments($invoice_id)
    {
        return (int) $this->db
            ->where('invoices_id', (int) $invoice_id)
            ->count_all_results('tbl_payments');
    }

    public function get_invoice_payment_page($invoice_id, $limit = 15, $offset = 0)
    {
        return $this->db
            ->select('tbl_payments.*, tbl_accounts.account_name, tbl_payment_methods.method_name AS payment_method_name')
            ->from('tbl_payments')
            ->join('tbl_accounts', 'tbl_accounts.account_id = tbl_payments.account_id', 'left')
            ->join('tbl_payment_methods', 'tbl_payment_methods.payment_methods_id = tbl_payments.payment_method', 'left')
            ->where('tbl_payments.invoices_id', (int) $invoice_id)
            ->order_by('tbl_payments.payments_id', 'DESC')
            ->limit((int) $limit, (int) $offset)
            ->get()
            ->result();
    }

    public function count_invoice_activities($invoice_id)
    {
        return (int) $this->db
            ->where(array(
                'module' => 'invoice',
                'module_field_id' => (int) $invoice_id,
            ))
            ->count_all_results('tbl_activities');
    }

    public function get_invoice_activity_page($invoice_id, $limit = 20, $offset = 0)
    {
        return $this->db
            ->select('tbl_activities.*, tbl_account_details.fullname AS actor_name, tbl_account_details.user_id AS actor_user_id')
            ->from('tbl_activities')
            ->join('tbl_account_details', 'tbl_account_details.user_id = tbl_activities.user', 'left')
            ->where(array(
                'tbl_activities.module' => 'invoice',
                'tbl_activities.module_field_id' => (int) $invoice_id,
            ))
            ->order_by('tbl_activities.activity_date', 'DESC')
            ->limit((int) $limit, (int) $offset)
            ->get()
            ->result();
    }

    public function get_payment_status($invoice_id)
    {
        $invoice_info = $this->check_by(array('invoices_id' => $invoice_id), 'tbl_invoices');
        $payment_made = round($this->get_invoice_paid_amount($invoice_id), 2);
        $due = $this->get_invoice_due_amount($invoice_id);
        return $this->resolve_invoice_status_from_summary($invoice_info, $payment_made, $due);
    }


    public function invoice_payable($id)
    {
        return ($this->get_invoice_cost($id) + $this->get_invoice_tax_amount($id) - $this->get_invoice_discount($id));
    }


    public function invoice_perc($invoice)
    {
        $invoice_payment = $this->invoice_payment($invoice);
        $invoice_payable = $this->invoice_payable($invoice);
        if ($invoice_payable < 1 or $invoice_payment < 1) {
            $perc_paid = 0;
        } else {
            $perc_paid = ($invoice_payment / $invoice_payable) * 100;
        }
        return round($perc_paid);
    }


    public function invoice_payment($invoice)
    {
        $this->ci->db->where('invoice', $invoice);
        $this->ci->db->select_sum('amount');
        $query = $this->ci->db->get('payments');
        if ($query->num_rows() > 0) {
            $row = $query->row();
            return $row->amount;
        }
    }

    function ordered_items_by_id($id, $type = 'invoices', $json = null)
    {

        $table = ($type == 'invoices' ? '' : 'estimate_') . 'tbl_items';
        $rows = $this->db->where($type . '_id', $id)->order_by('order', 'asc')->get($table)->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 = json_decode($row->item_tax_name);
                    $pr[] = $row;
                }
                return json_encode($pr);
            }
        } else {
            return $rows;
        }
    }

    function calculate_to($invoice_value, $invoice_id)
    {
        switch ($invoice_value) {
            case 'invoice_cost':
                return $this->get_invoice_cost($invoice_id);
                break;
            case 'tax':
                return $this->get_invoice_tax_amount($invoice_id);
                break;
            case 'discount':
                return $this->get_invoice_discount($invoice_id);
                break;
            case 'paid_amount':
                return $this->get_invoice_paid_amount($invoice_id);
                break;
            case 'invoice_due':
                return $this->get_invoice_due_amount($invoice_id);
                break;
            case 'total':
                return $this->get_invoice_total_amount($invoice_id);
                break;
        }
    }

    function get_invoice_cost($invoice_id)
    {
        $this->db->select_sum('total_cost');
        $this->db->where('invoices_id', $invoice_id);
        $this->db->from('tbl_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_invoice_tax_amount($invoice_id)
    {
        $invoice_info = $this->check_by(array('invoices_id' => $invoice_id), 'tbl_invoices');
        return $this->extract_total_tax_amount(!empty($invoice_info->total_tax) ? $invoice_info->total_tax : null);
    }

    public function get_invoice_discount($invoice_id)
    {
        $invoice_info = $this->check_by(array('invoices_id' => $invoice_id), 'tbl_invoices');
        if (empty($invoice_info)) {
            $invoice_info = new stdClass();
            $invoice_info->discount_total = 0;
        }
        return $invoice_info->discount_total;
    }

    public function get_invoice_paid_amount($invoice_id)
    {

        $this->db->select_sum('amount');
        $this->db->where('invoices_id', $invoice_id);
        $this->db->from('tbl_payments');
        $query_result = $this->db->get();
        $amount = $query_result->row();
        $tax = $this->get_invoice_tax_amount($invoice_id);
        if (!empty($amount->amount)) {
            $result = $amount->amount;
        } else {
            $result = '0';
        }
        return $result;
    }

    public function get_invoice_due_amount($invoice_id)
    {
        $invoice_info = $this->check_by(array('invoices_id' => $invoice_id), 'tbl_invoices');
        if (empty($invoice_info)) {
            $invoice_info = new stdClass();
            $invoice_info->adjustment = 0;
        }
        $tax = $this->get_invoice_tax_amount($invoice_id);
        $discount = $this->get_invoice_discount($invoice_id);
        $invoice_cost = round($this->get_invoice_cost($invoice_id));
        $payment_made = $this->get_invoice_paid_amount($invoice_id);
        $due_amount = (($invoice_cost - $discount) + $tax) - $payment_made + $invoice_info->adjustment;
        if ($due_amount <= 0) {
            $due_amount = 0;
        }
        $data['total_amount'] = $due_amount;
        $data['invoice_info'] = $invoice_info;
        $due_amount = apply_filters('add_more_invoice_total_amount', $data);
        return $due_amount['total_amount'];
    }

    public function get_invoice_total_amount($invoice_id)
    {
        $invoice_info = $this->check_by(array('invoices_id' => $invoice_id), 'tbl_invoices');
        $tax = $this->get_invoice_tax_amount($invoice_id);
        $discount = $this->get_invoice_discount($invoice_id);
        $invoice_cost = $this->get_invoice_cost($invoice_id);
        $payment_made = $this->get_invoice_paid_amount($invoice_id);
        if (empty($invoice_info)) {
            $invoice_info = new stdClass();
            $invoice_info->adjustment = 0;
        }
        $total_amount = $invoice_cost - $discount + $tax + $invoice_info->adjustment;
        if ($total_amount <= 0) {
            $total_amount = 0;
        }
        $data['total_amount'] = $total_amount;
        $data['invoice_info'] = $invoice_info;
        $total_amount = apply_filters('add_more_invoice_total_amount', $data);
        return $total_amount['total_amount'];
    }

    function all_invoice_amount()
    {
        $invoices = $this->db->get('tbl_invoices')->result();
        $cost = array();
        foreach ($invoices as $invoice) {
            $tax = round($this->get_invoice_tax_amount($invoice->invoices_id));
            $discount = round($this->get_invoice_discount($invoice->invoices_id));
            $invoice_cost = round($this->get_invoice_cost($invoice->invoices_id));
            $cost[] = ($invoice_cost + $tax) - $discount;
        }
        if (is_array($cost) && !empty($cost)) {
            return round(array_sum($cost), 2);
        } else {
            return 0;
        }
    }

    function all_outstanding()
    {
        $invoices = $this->db->where(array('status !=' => 'draft'))->get('tbl_invoices')->result();
        $due = array();
        foreach ($invoices as $invoice) {
            $due[] = $this->get_invoice_due_amount($invoice->invoices_id);
        }
        if (is_array($due) && !empty($due)) {
            return round(array_sum($due), 2);
        } else {
            return 0;
        }
    }

    function client_outstanding($client_id, $project_id = null)
    {
        $due = array();
        if (!empty($project_id)) {
            $invoices_info = $this->db->where(array('project_id' => $project_id, 'status !=' => 'draft'))->get('tbl_invoices')->result();
        } else {
            $invoices_info = $this->db->where(array('client_id' => $client_id, 'status !=' => 'draft'))->get('tbl_invoices')->result();
        }

        foreach ($invoices_info as $v_invoice) {
            $due[] = $this->get_invoice_due_amount($v_invoice->invoices_id);
        }
        if (is_array($due) && !empty($due)) {
            return round(array_sum($due), 2);
        } else {
            return 0;
        }
    }

    public function check_for_merge_invoice($client_id, $current_invoice)
    {

        $invoice_info = $this->db->where('client_id', $client_id)->get('tbl_invoices')->result();

        foreach ($invoice_info as $v_invoice) {
            if ($v_invoice->invoices_id != $current_invoice) {
                $payment_status = $this->get_payment_status($v_invoice->invoices_id);
                if ($payment_status == lang('not_paid') || $payment_status == lang('draft')) {
                    $invoice[] = $v_invoice;
                }
            }
        }
        if (!empty($invoice)) {
            return $invoice;
        } else {
            return array();
        }
    }

    public function get_invoice_filter()
    {

        $statuses = array(
            array(
                'id' => 1,
                'value' => 'paid',
                'name' => lang('paid'),
                'order' => 1,
            ),
            array(
                'id' => 2,
                'value' => 'not_paid',
                'name' => lang('not_paid'),
                'order' => 2,
            ),
            array(
                'id' => 3,
                'value' => 'partially_paid',
                'name' => lang('partially_paid'),
                'order' => 3,
            ),
            array(
                'id' => 1,
                'value' => 'draft',
                'name' => lang('draft'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'cancelled',
                'name' => lang('cancelled'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'overdue',
                'name' => lang('overdue'),
                'order' => 1,
            ),
            array(
                'id' => 4,
                'value' => 'recurring',
                'name' => lang('recurring'),
                'order' => 4,
            ),
            array(
                'id' => 4,
                'value' => 'last_month',
                'name' => lang('last_month'),
                'order' => 4,
            ),
            array(
                'id' => 4,
                'value' => 'this_months',
                'name' => lang('this_months'),
                'order' => 4,
            )
        );
        $this->db->select('invoice_year');
        $this->db->group_by('invoice_year');
        $result = $this->db->get('tbl_invoices')->result();
        if (!empty($result)) {
            foreach ($result as $v_year) {
                $test = array(
                    'id' => 1,
                    'value' => '_' . $v_year->invoice_year,
                    'name' => $v_year->invoice_year,
                    'order' => 1
                );
                if (!empty($test)) {
                    array_push($statuses, $test);
                }
            }
        }
        return $statuses;
    }

    // Get a list of recurring invoices
    public function recurring_invoices($client_id = null)
    {
        if (!empty($client_id)) {
            return $this->db->where(array('recurring' => 'Yes', 'client_id' => $client_id))->get('tbl_invoices')->result();
        } else {
            return $this->db->where(array('recurring' => 'Yes', 'invoices_id >' => 0))->get('tbl_invoices')->result();
        }
    }

    public function get_client_invoices($filterBy = null, $client_id = null)
    {
        $all_invoice = get_result('tbl_invoices', array('client_id' => $client_id, 'status !=' => 'draft'));
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            return $all_invoice;
        } elseif ($filterBy == 'recurring') {
            return $this->recurring_invoices($client_id);
        } else {
            if (!empty($all_invoice)) {
                $all_invoice = array_reverse($all_invoice);
                foreach ($all_invoice as $v_invoices) {
                    if ($filterBy == 'paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('fully_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'not_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('not_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'draft') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('draft')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'partially_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('partially_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'cancelled') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'overdue') {
                        $payment_status = $this->get_payment_status($v_invoices->invoices_id);
                        if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $payment_status != lang('fully_paid') && $payment_status != lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'last_month' || $filterBy == 'this_months') {
                        if ($filterBy == 'last_month') {
                            $month = date('Y-m', safe_strtotime('-1 months'));
                        } else {
                            $month = date('Y-m');
                        }
                        if ((string) $v_invoices->invoice_month === (string) $month) {
                            $invoice[] = $v_invoices;
                        }
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if ((string) $v_invoices->invoice_year === (string) $year) {
                            $invoice[] = $v_invoices;
                        }
                    }
                }
            }
        }
        if (!empty($invoice)) {
            return $invoice;
        } else {
            return array();
        }
    }

    /*    public function get_invoices($filterBy = null, $client_id = null)
        {
            if (!empty($client_id)) {
                $all_invoice = get_result('tbl_invoices', array('client_id' => $client_id));
            } else {
                $all_invoice = $this->get_permission('tbl_invoices');
            }
            if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
                return $all_invoice;
            } elseif ($filterBy == 'recurring') {
                return $this->recurring_invoices($client_id);
            } else {
                if (!empty($all_invoice)) {
                    $all_invoice = array_reverse($all_invoice);
                    foreach ($all_invoice as $v_invoices) {
                        if ($filterBy == 'paid') {
                            if ($this->get_payment_status($v_invoices->invoices_id) == lang('fully_paid')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'not_paid') {
                            if ($this->get_payment_status($v_invoices->invoices_id) == lang('not_paid')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'draft') {
                            if ($this->get_payment_status($v_invoices->invoices_id) == lang('draft')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'partially_paid') {
                            if ($this->get_payment_status($v_invoices->invoices_id) == lang('partially_paid')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'cancelled') {
                            if ($this->get_payment_status($v_invoices->invoices_id) == lang('cancelled')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'overdue') {
                            $payment_status = $this->get_payment_status($v_invoices->invoices_id);
                            if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $payment_status != lang('fully_paid')) {
                                $invoice[] = $v_invoices;
                            }
                        } else if ($filterBy == 'last_month' || $filterBy == 'this_months') {
                            if ($filterBy == 'last_month') {
                                $month = date('Y-m', safe_strtotime('-1 months'));
                            } else {
                                $month = date('Y-m');
                            }
                            if (safe_strtotime($v_invoices->invoice_month) == safe_strtotime($month)) {
                                $invoice[] = $v_invoices;
                            }
                        } else if (strstr($filterBy, '_')) {
                            $year = str_replace('_', '', $filterBy);
                            if (safe_strtotime($v_invoices->invoice_year) == safe_strtotime($year)) {
                                $invoice[] = $v_invoices;
                            }
                        }

                    }
                }
            }
            if (!empty($invoice)) {
                return $invoice;
            } else {
                return array();
            }
        }    */

    public function get_invoices($filterBy = null, $client_id = null)
    {
        if (!empty($client_id)) {
            $all_invoice = get_result('tbl_invoices', array('client_id' => $client_id));
        } else {
            $all_invoice = $this->get_permission('tbl_invoices');
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            return $all_invoice;
        } elseif ($filterBy == 'recurring') {
            return $this->recurring_invoices($client_id);
        } else {
            if (!empty($all_invoice)) {
                $all_invoice = array_reverse($all_invoice);
                foreach ($all_invoice as $v_invoices) {
                    if ($filterBy == 'paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('fully_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'not_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('not_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'draft') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('draft')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'partially_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('partially_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'cancelled') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'overdue') {
                        $payment_status = $this->get_payment_status($v_invoices->invoices_id);
                        if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $payment_status != lang('fully_paid') && $payment_status != lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'last_month' || $filterBy == 'this_months') {
                        if ($filterBy == 'last_month') {
                            $month = date('Y-m', safe_strtotime('-1 months'));
                        } else {
                            $month = date('Y-m');
                        }
                        if ((string) $v_invoices->invoice_month === (string) $month) {
                            $invoice[] = $v_invoices;
                        }
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if ((string) $v_invoices->invoice_year === (string) $year) {
                            $invoice[] = $v_invoices;
                        }
                    }
                }
            }
        }

        if (!empty($invoice)) {
            return $invoice;
        } else {
            return array();
        }
    }

    public function get_client_wise_invoice()
    {
        $all_invoice = $this->get_permission('tbl_invoices');
        $client_invoice = array();
        if (!empty($all_invoice)) {
            $all_invoice = array_reverse($all_invoice);
            foreach ($all_invoice as $v_invoices) {
                $due = $this->calculate_to('invoice_due', $v_invoices->invoices_id);
                if ($due != 0) {
                    $client_invoice[$v_invoices->client_id][] = $v_invoices;
                }
            }
            return $client_invoice;
        }
    }

    private function apply_batch_payment_scope($client_id = null)
    {
        $expressions = $this->get_invoice_report_expressions();

        $this->db->from('tbl_invoices');
        $this->db->join('tbl_client', 'tbl_client.client_id = tbl_invoices.client_id', 'left');
        $this->staff_query('tbl_invoices');
        $this->db->where($expressions['due_expr'] . ' > 0', null, false);

        if (!empty($client_id)) {
            $this->db->where('tbl_invoices.client_id', (int) $client_id);
        }

        return $expressions;
    }

    public function count_batch_payment_invoices($client_id = null)
    {
        $this->apply_batch_payment_scope($client_id);
        return (int) $this->db->count_all_results();
    }

    public function get_batch_payment_invoice_page($limit = 25, $offset = 0, $client_id = null)
    {
        $expressions = $this->apply_batch_payment_scope($client_id);
        $this->db->select('tbl_invoices.invoices_id, tbl_invoices.client_id, tbl_invoices.reference_no, tbl_invoices.invoice_date, tbl_invoices.due_date, tbl_invoices.currency, tbl_client.name AS client_name', false);
        $this->db->select($expressions['due_expr'] . ' AS due_amount', false);
        $this->db->order_by('tbl_invoices.invoices_id', 'DESC');
        $this->db->limit((int) $limit, (int) $offset);
        return $this->db->get()->result();
    }

    public function get_batch_payment_clients()
    {
        $expressions = $this->apply_batch_payment_scope(null);
        $this->db->select('tbl_invoices.client_id, tbl_client.name AS client_name, COUNT(*) AS unpaid_count', false);
        $this->db->group_by(array('tbl_invoices.client_id', 'tbl_client.name'));
        $this->db->order_by('tbl_client.name', 'ASC');
        return $this->db->get()->result();
    }

    public function get_invoice_payment()
    {
        $all_invoice = $this->db->get('tbl_payments')->result();
        $all_method = $this->db->get('tbl_payment_methods')->result();
        if (!empty($all_invoice)) {
            $all_invoice = array_reverse($all_invoice);
            foreach ($all_invoice as $v_invoices) {
                $years[] = $v_invoices->year_paid;
            }
        }
        if (!empty($years)) {
            $result = array_unique($years);
        }

        $statuses = array(
            array(
                'id' => 4,
                'value' => 'last_month',
                'name' => lang('last_month'),
                'order' => 4,
            ),
            array(
                'id' => 4,
                'value' => 'this_months',
                'name' => lang('this_months'),
                'order' => 4,
            )
        );
        if (!empty($result)) {
            foreach ($result as $v_year) {
                $year = array(
                    'id' => 1,
                    'value' => '_' . $v_year,
                    'name' => $v_year,
                    'order' => 1
                );
                if (!empty($year)) {
                    array_push($statuses, $year);
                }
            }
        }
        if (!empty($all_method)) {
            foreach ($all_method as $v_method) {
                $method = array(
                    'id' => 1,
                    'value' => $v_method->payment_methods_id,
                    'name' => $v_method->method_name,
                    'order' => 1
                );
                if (!empty($method)) {
                    array_push($statuses, $method);
                }
            }
        }
        return $statuses;
    }

    public function get_payments($filterBy = null, $client_id = null)
    {
        if (!empty($client_id)) {
            $all_payments = $this->db->where('paid_by', $client_id)->get('tbl_payments')->result();
        } else {
            $all_payments = $this->db->get('tbl_payments')->result();
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            return $all_payments;
        } else {
            if (!empty($all_payments)) {
                foreach ($all_payments as $v_payments) {
                    if (is_numeric($filterBy)) {
                        if ($v_payments->payment_method == $filterBy) {
                            $payment[] = $v_payments;
                        }
                    } else if ($filterBy == 'last_month' || $filterBy == 'this_months') {
                        if ($filterBy == 'last_month') {
                            $month = date('Y-m', safe_strtotime('-1 months'));
                        } else {
                            $month = date('Y-m');
                        }
                        $month_paid = $v_payments->month_paid . '-' . $v_payments->year_paid;
                        if ($month_paid == $month) {
                            $payment[] = $v_payments;
                        }
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if (safe_strtotime($v_payments->year_paid) == safe_strtotime($year)) {
                            $payment[] = $v_payments;
                        }
                    }
                }
            }
        }

        if (!empty($payment)) {
            return $payment;
        } else {
            return array();
        }
    }

    public function total_sales($filter = null)
    {

        $total = 0;
        $all_payments = get_result('tbl_payments');
        //        $currency = get_row(array('symbol' => $payment->currency));
        foreach ($all_payments as $payment) {
            $amount = $payment->amount;
            //            if ($payment->currency != config_item('default_currency')) {
            //                $amount = convert_currency($p->currency, $amount);
            //            }
            $total += $amount;
        }
        return $total;
    }

    public function paid_by_date($year, $month = null)
    {
        $total = 0;
        if (!empty($month)) {
            $where = array('year_paid' => $year, 'month_paid' => $month);
        } else {
            $where = array('year_paid' => $year);
        }
        $payments = $this->db->where($where)->get('tbl_payments')->result();

        foreach ($payments as $p) {
            $amount = $p->amount;
            //            if ($p->currency != config_item('default_currency')) {
            //                $amount = Applib::convert_currency($p->currency, $amount);
            //            }
            $total += $amount;
        }
        return $total;
    }

    public function get_invoice_report($filterBy = null, $range = null)
    {
        if (!empty($filterBy) && is_numeric($filterBy)) {
            $invoice = $this->db->where('client_id', $filterBy)->get('tbl_invoices')->result();
        } else {
            $all_data = $this->get_permission('tbl_invoices');
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            $invoice = $all_data;
        } elseif ($filterBy == 'recurring') {
            $invoice = $this->recurring_invoices();
        } else {
            if (!empty($all_data)) {
                $all_data = array_reverse($all_data);
                foreach ($all_data as $v_invoices) {
                    if ($filterBy == 'paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('fully_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'not_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('not_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'draft') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('draft')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'partially_paid') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('partially_paid')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'cancelled') {
                        if ($this->get_payment_status($v_invoices->invoices_id) == lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'overdue') {
                        $payment_status = $this->get_payment_status($v_invoices->invoices_id);
                        if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $payment_status != lang('fully_paid') && $payment_status != lang('cancelled')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'last_month' || $filterBy == 'this_months') {
                        if ($filterBy == 'last_month') {
                            $month = date('Y-m', safe_strtotime('-1 months'));
                        } else {
                            $month = date('Y-m');
                        }
                        if (safe_strtotime($v_invoices->invoice_month) == safe_strtotime($month)) {
                            $invoice[] = $v_invoices;
                        }
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if (safe_strtotime($v_invoices->invoice_year) == safe_strtotime($year)) {
                            $invoice[] = $v_invoices;
                        }
                    }
                }
            }
        }

        if (!empty($invoice)) {
            $invoices = array();

            if (!empty($range[0])) {
                foreach ($invoice as $v_invoice) {
                    if ($v_invoice->invoice_date >= $range[0] && $v_invoice->invoice_date <= $range[1]) {
                        array_push($invoices, $v_invoice);
                    }
                }
                return $invoices;
            } else {
                return $invoice;
            }
        } else {
            return array();
        }
    }

    private function get_invoice_report_expressions()
    {
        $item_total_expr = 'COALESCE((SELECT SUM(tbl_items.total_cost) FROM tbl_items WHERE tbl_items.invoices_id = tbl_invoices.invoices_id), 0)';
        $tax_total_expr = 'COALESCE((SELECT SUM(tbl_items.item_tax_total) FROM tbl_items WHERE tbl_items.invoices_id = tbl_invoices.invoices_id), 0)';
        $paid_total_expr = 'COALESCE((SELECT SUM(tbl_payments.amount) FROM tbl_payments WHERE tbl_payments.invoices_id = tbl_invoices.invoices_id), 0)';
        $discount_expr = 'COALESCE(tbl_invoices.discount_total, 0)';
        $adjustment_expr = 'COALESCE(tbl_invoices.adjustment, 0)';
        $total_expr = 'GREATEST((' . $item_total_expr . ' - ' . $discount_expr . ' + ' . $adjustment_expr . ' + ' . $tax_total_expr . '), 0)';
        $due_expr = 'GREATEST((' . $total_expr . ' - ' . $paid_total_expr . '), 0)';
        $payment_status_expr = "CASE
            WHEN tbl_invoices.status = 'draft' THEN 'draft'
            WHEN tbl_invoices.status = 'cancelled' THEN 'cancelled'
            WHEN " . $paid_total_expr . " < 1 THEN 'not_paid'
            WHEN " . $due_expr . " <= 0 THEN 'fully_paid'
            ELSE 'partially_paid'
        END";

        return array(
            'item_total_expr' => $item_total_expr,
            'tax_total_expr' => $tax_total_expr,
            'paid_total_expr' => $paid_total_expr,
            'discount_expr' => $discount_expr,
            'adjustment_expr' => $adjustment_expr,
            'total_expr' => $total_expr,
            'due_expr' => $due_expr,
            'payment_status_expr' => $payment_status_expr,
        );
    }

    private function apply_invoice_report_scope($filterBy = null, $range = null)
    {
        $expressions = $this->get_invoice_report_expressions();

        $this->db->from('tbl_invoices');
        $this->db->join('tbl_client', 'tbl_client.client_id = tbl_invoices.client_id', 'left');
        $this->staff_query('tbl_invoices');

        if (!empty($range[0]) && !empty($range[1])) {
            $this->db->where('tbl_invoices.invoice_date >=', $range[0]);
            $this->db->where('tbl_invoices.invoice_date <=', $range[1]);
        }

        if (empty($filterBy) || $filterBy === 'all') {
            return $expressions;
        }

        if (is_numeric($filterBy)) {
            $this->db->where('tbl_invoices.client_id', (int) $filterBy);
            return $expressions;
        }

        if ($filterBy === 'recurring') {
            $this->db->where('tbl_invoices.recurring', 'Yes');
            return $expressions;
        }

        if ($filterBy === 'draft') {
            $this->db->where('tbl_invoices.status', 'draft');
            return $expressions;
        }

        if ($filterBy === 'cancelled') {
            $this->db->where('tbl_invoices.status', 'cancelled');
            return $expressions;
        }

        $status_exclusions = array('draft', 'cancelled');
        $not_paid_expr = $expressions['paid_total_expr'] . ' < 1';
        $paid_expr = $expressions['paid_total_expr'] . ' >= 1 AND ' . $expressions['paid_total_expr'] . ' >= ' . $expressions['total_expr'];
        $partial_expr = $expressions['paid_total_expr'] . ' >= 1 AND ' . $expressions['paid_total_expr'] . ' < ' . $expressions['total_expr'];
        $overdue_expr = "tbl_invoices.due_date < '" . date('Y-m-d') . "' AND " . $expressions['due_expr'] . ' > 0';

        if ($filterBy === 'paid') {
            $this->db->where_not_in('tbl_invoices.status', $status_exclusions);
            $this->db->where($paid_expr, null, false);
        } elseif ($filterBy === 'not_paid') {
            $this->db->where_not_in('tbl_invoices.status', $status_exclusions);
            $this->db->where($not_paid_expr, null, false);
        } elseif ($filterBy === 'partially_paid') {
            $this->db->where_not_in('tbl_invoices.status', $status_exclusions);
            $this->db->where($partial_expr, null, false);
        } elseif ($filterBy === 'overdue') {
            $this->db->where_not_in('tbl_invoices.status', $status_exclusions);
            $this->db->where($overdue_expr, null, false);
        }

        return $expressions;
    }

    public function get_invoice_report_page_data($filterBy = null, $range = null, $limit = 10, $offset = 0)
    {
        $expressions = $this->apply_invoice_report_scope($filterBy, $range);

        $this->db->select('COUNT(*) AS invoice_count', false);
        $this->db->select('COALESCE(SUM(' . $expressions['total_expr'] . '), 0) AS invoice_total', false);
        $this->db->select('COALESCE(SUM(' . $expressions['tax_total_expr'] . '), 0) AS total_tax', false);
        $this->db->select('COALESCE(SUM(' . $expressions['due_expr'] . '), 0) AS due_total', false);
        $summary_row = $this->db->get()->row();

        $this->db->reset_query();
        $expressions = $this->apply_invoice_report_scope($filterBy, $range);
        $this->db->select('tbl_invoices.*, tbl_client.name AS client_name', false);
        $this->db->select($expressions['total_expr'] . ' AS invoice_total', false);
        $this->db->select($expressions['tax_total_expr'] . ' AS tax_amount', false);
        $this->db->select($expressions['paid_total_expr'] . ' AS paid_amount', false);
        $this->db->select($expressions['due_expr'] . ' AS due_amount', false);
        $this->db->select($expressions['payment_status_expr'] . ' AS payment_status_key', false);
        $this->db->order_by('tbl_invoices.invoices_id', 'DESC');
        $this->db->limit((int) $limit, (int) $offset);
        $rows = $this->db->get()->result();

        $invoice_count = !empty($summary_row->invoice_count) ? (int) $summary_row->invoice_count : 0;
        $invoice_total = !empty($summary_row->invoice_total) ? (float) $summary_row->invoice_total : 0;
        $total_tax = !empty($summary_row->total_tax) ? (float) $summary_row->total_tax : 0;
        $due_total = !empty($summary_row->due_total) ? (float) $summary_row->due_total : 0;

        return array(
            'rows' => $rows,
            'invoice_count' => $invoice_count,
            'invoice_total' => $invoice_total,
            'total_tax' => $total_tax,
            'due_total' => $due_total,
            'average_total' => $invoice_count > 0 ? ($invoice_total / $invoice_count) : 0,
        );
    }

    public function get_payment_report($client_id = null, $range = null)
    {

        if (!empty($range[0])) {
            $where = array('paid_by' => $client_id, 'payment_date >=' => $range[0], 'payment_date <=' => $range[1]);
        } else if (!empty($client_id) && is_numeric($client_id)) {
            $where = array('paid_by' => $client_id);
        }
        if (!empty($where)) {
            return $this->db->where($where)->get('tbl_payments')->result();
        } else {
            return $this->db->get('tbl_payments')->result();
        }
    }

    public function send_invoice_sms($invoices_id)
    {

        $inv_info = $this->check_by(array('invoices_id' => $invoices_id), 'tbl_invoices');
        $client_info = $this->check_by(array('client_id' => $inv_info->client_id), 'tbl_client');

        if (!empty($client_info)) {
            $currency = $this->client_currency_symbol($client_info->client_id);;
            $mobile = $client_info->mobile;
        } else {
            $currency = $this->check_by(array('code' => config_item('default_currency')), 'tbl_currencies');
            $mobile = null;
        }

        $message = config_item('sms_overdue_invoice_template');

        $client = str_replace("{client}", client_name($client_info->client_id), $message);
        $reference_no = str_replace("{invoice_number}", $inv_info->reference_no, $client);
        $invoice_link = str_replace("{invoice_link}", base_url('admin/client/manage_invoice/invoice_details/' . $invoices_id), $reference_no);
        $invoice_duedate = str_replace("{invoice_duedate}", display_date($inv_info->due_date), $invoice_link);
        $invoice_date = str_replace("{invoice_date}", display_date($inv_info->invoice_date), $invoice_duedate);
        $subtotal = str_replace("{invoice_subtotal}", display_money($this->invoice_model->calculate_to('invoice_cost', $inv_info->invoices_id), $currency->symbol), $invoice_date);
        $total = str_replace("{invoice_total}", display_money($this->invoice_model->calculate_to('total', $inv_info->invoices_id), $currency->symbol), $subtotal);
        $status = str_replace("{invoice_status}", lang($this->invoice_model->get_payment_status($inv_info->invoices_id)), $total);
        $message = str_replace("{site_name}", config_item('company_name'), $status);

        if (!empty($mobile)) {
            $twilio_status = config_item('twilio_status');
            if ($twilio_status == 1 && client_can_received_sms($client_info->client_id)) {
                if (function_exists('twilio_send_sms')) {
                    $retval = call_user_func_array('twilio_send_sms', array($mobile, clear_textarea_breaks($message)));
                    return $retval;
                }
            }
        }
    }

    public function all_payments_client_wise()
    {
        return $this->db->query("SELECT tbl_payments.payments_id, tbl_payments.payment_date,  tbl_payments.amount, tbl_invoices.client_id, tbl_client.name
                                FROM tbl_payments
                                LEFT JOIN tbl_invoices ON tbl_payments.invoices_id = tbl_invoices.invoices_id
                                LEFT JOIN tbl_client ON tbl_invoices.client_id = tbl_client.client_id")->result();
    }

    public function get_sale_layout()
    {
        $layouts = array(
            'default' => lang('default'),
            'classic' => lang('classic'),
            'modern' => lang('modern'),
            // 'simple' => lang('simple'),
            'updates' => lang('updates'),
            'leader' => lang('leader'),
            'clean' => lang('clean'),
            'wonder' => lang('wonder'),
            'elegant' => lang('elegant'),
            // 'professional' => lang('professional'),
            // 'cool' => lang('cool'),
            // 'flat' => lang('flat'),
            // 'okey' => lang('okey'),
        );
        return $layouts;
    }
}
