<?php

class Estimates_Model extends MY_Model
{

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

    function estimate_calculation($estimate_value, $estimates_id)
    {
        switch ($estimate_value) {
            case 'estimate_cost':
                return $this->get_estimate_cost($estimates_id);
                break;
            case 'tax':
                return $this->get_estimate_tax_amount($estimates_id);
                break;
            case 'discount':
                return $this->get_estimate_discount($estimates_id);
                break;
            case 'estimate_amount':
                return $this->get_estimate_amount($estimates_id);
                break;
            case 'total':
                return $this->get_total_estimate_amount($estimates_id);
                break;
        }
    }

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

    function get_estimate_tax_amount($estimates_id)
    {
        $invoice_info = $this->check_by(array('estimates_id' => $estimates_id), 'tbl_estimates');
        $tax = 0;
        if (empty($invoice_info) || !isset($invoice_info->total_tax) || $invoice_info->total_tax === '' || $invoice_info->total_tax === null) {
            return $tax;
        }

        $raw_tax = $invoice_info->total_tax;
        if (is_numeric($raw_tax)) {
            return (float) $raw_tax;
        }

        $tax_info = json_decode($raw_tax, true);
        if (json_last_error() !== JSON_ERROR_NONE || empty($tax_info)) {
            return is_numeric($raw_tax) ? (float) $raw_tax : 0;
        }

        if (isset($tax_info['total_tax'])) {
            $total_tax = $tax_info['total_tax'];
            if (is_array($total_tax)) {
                foreach ($total_tax as $v_tax_info) {
                    $tax += (float) $v_tax_info;
                }
            } elseif (is_numeric($total_tax)) {
                $tax += (float) $total_tax;
            }
        } elseif (is_numeric($tax_info)) {
            $tax += (float) $tax_info;
        }

        return $tax;
    }

    function get_estimate_discount($estimates_id)
    {
        $invoice_info = $this->check_by(array('estimates_id' => $estimates_id), 'tbl_estimates');
        return $invoice_info->discount_total;
    }

    function get_estimate_amount($estimates_id)
    {

        $tax = $this->get_estimate_tax_amount($estimates_id);
        $discount = $this->get_estimate_discount($estimates_id);
        $estimate_cost = $this->get_estimate_cost($estimates_id);
        return (($estimate_cost - $discount) + $tax);
    }

    function get_total_estimate_amount($estimates_id)
    {
        $invoice_info = $this->check_by(array('estimates_id' => $estimates_id), 'tbl_estimates');
        $tax = $this->get_estimate_tax_amount($estimates_id);
        $discount = $this->get_estimate_discount($estimates_id);
        $estimate_cost = $this->get_estimate_cost($estimates_id);
        return (($estimate_cost - $discount) + $tax + $invoice_info->adjustment);
    }

    function ordered_items_by_id($id, $json = null)
    {
        $rows = $this->db->where('estimates_id', $id)->order_by('order', 'asc')->get('tbl_estimate_items')->result();
        if (!empty($rows)) {
            foreach ($rows as $row) {
                $row->items_id = $row->estimate_items_id;
                $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($row->item_tax_name) : array();
            }
        }
        if (!empty($json)) {
            if (!empty($rows)) {
                $pr = array();
                foreach ($rows as $row) {
                    $pr[] = $row;
                }
                return json_encode($pr);
            }
            return json_encode(array());
        } else {
            return $rows;
        }
    }


    public function check_for_merge_invoice($client_id, $current_estimate)
    {

        $estimate_info = $this->db->where('client_id', $client_id)->get('tbl_estimates')->result();

        foreach ($estimate_info as $v_estimate) {
            if ($v_estimate->estimates_id != $current_estimate) {
                if (strtolower($v_estimate->status) == 'pending' || $v_estimate->status == 'draft') {
                    $estimate[] = $v_estimate;
                }
            }
        }
        if (!empty($estimate)) {
            return $estimate;
        } else {
            return array();
        }
    }

    public function get_invoice_filter()
    {
        $this->db->select('invoice_year');
        $this->db->group_by('invoice_year');
        $result = $this->db->get('tbl_invoices')->result();

        $statuses = array(
            array(
                'id' => 1,
                'value' => 'draft',
                'name' => lang('draft'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'cancelled',
                'name' => lang('cancelled'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'expired',
                'name' => lang('expired'),
                'order' => 1,
            ),
            array(
                'id' => 4,
                'value' => 'declined',
                'name' => lang('declined'),
                'order' => 4,
            ),
            array(
                'id' => 4,
                'value' => 'accepted',
                'name' => lang('accepted'),
                '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,
            )
        );
        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;
    }

    public function get_estimate_filter()
    {
        $this->db->select('estimate_year');
        $this->db->group_by('estimate_year');
        $result = $this->db->get('tbl_estimates')->result();

        $statuses = array(
            array(
                'id' => 1,
                'value' => 'draft',
                'name' => lang('draft'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'cancelled',
                'name' => lang('cancelled'),
                'order' => 1,
            ), array(
                'id' => 1,
                'value' => 'expired',
                'name' => lang('expired'),
                'order' => 1,
            ),
            array(
                'id' => 4,
                'value' => 'declined',
                'name' => lang('declined'),
                'order' => 4,
            ),
            array(
                'id' => 4,
                'value' => 'accepted',
                'name' => lang('accepted'),
                '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,
            )
        );
        if (!empty($result)) {
            foreach ($result as $v_year) {
                $test = array(
                    'id' => 1,
                    'value' => '_' . $v_year->estimate_year,
                    'name' => $v_year->estimate_year,
                    'order' => 1
                );
                if (!empty($test)) {
                    array_push($statuses, $test);
                }
            }
        }
        return $statuses;
    }

    private function normalize_estimate_sidebar_row($estimate)
    {
        if (empty($estimate)) {
            return null;
        }

        $estimate_id = (int) $estimate->estimates_id;
        $client_name = 'General Estimate';
        if (!empty($estimate->client_id)) {
            $client_info = $this->check_by(array('client_id' => $estimate->client_id), 'tbl_client');
            if (!empty($client_info->name)) {
                $client_name = $client_info->name;
            }
        }

        $currency = $this->client_currency_symbol($estimate->client_id);
        if (empty($currency)) {
            $currency = $this->check_by(array('code' => config_item('default_currency')), 'tbl_currencies');
        }

        return (object) array(
            'estimates_id' => $estimate_id,
            'client_id' => (int) $estimate->client_id,
            'client_name' => $client_name,
            'reference_no' => $estimate->reference_no,
            'status' => $estimate->status,
            'emailed' => $estimate->emailed,
            'invoiced' => $estimate->invoiced,
            'estimate_date' => $estimate->estimate_date,
            'total_amount' => (float) $this->estimate_calculation('estimate_amount', $estimate_id),
            'currency_symbol' => !empty($currency->symbol) ? $currency->symbol : default_currency(),
        );
    }

    private function estimate_sidebar_matches_search($estimate, $search = '')
    {
        $search = trim(mb_strtolower((string) $search));
        if ($search === '') {
            return true;
        }

        $haystack = array(
            $estimate->reference_no,
            $estimate->client_name,
            $estimate->status,
            display_date($estimate->estimate_date),
            number_format((float) $estimate->total_amount, 2, '.', ''),
            (string) $estimate->estimates_id,
        );

        return strpos(mb_strtolower(implode(' ', array_filter($haystack))), $search) !== false;
    }

    public function count_estimate_sidebar($search = '')
    {
        $all_estimates = $this->get_permission('tbl_estimates', null, null, false, array('estimates_id' => 'desc'));
        if (empty($all_estimates)) {
            return 0;
        }

        $count = 0;
        foreach ($all_estimates as $estimate) {
            $row = $this->normalize_estimate_sidebar_row($estimate);
            if (!empty($row) && $this->estimate_sidebar_matches_search($row, $search)) {
                $count++;
            }
        }

        return $count;
    }

    public function get_estimate_sidebar_page($limit = 12, $offset = 0, $search = '')
    {
        $all_estimates = $this->get_permission('tbl_estimates', null, null, false, array('estimates_id' => 'desc'));
        if (empty($all_estimates)) {
            return array();
        }

        $rows = array();
        foreach ($all_estimates as $estimate) {
            $row = $this->normalize_estimate_sidebar_row($estimate);
            if (!empty($row) && $this->estimate_sidebar_matches_search($row, $search)) {
                $rows[] = $row;
            }
        }

        return array_slice($rows, (int) $offset, (int) $limit);
    }

    public function get_estimate_sidebar_estimate($estimate_id)
    {
        $estimate = $this->check_by(array('estimates_id' => $estimate_id), 'tbl_estimates');
        if (empty($estimate)) {
            return null;
        }

        return $this->normalize_estimate_sidebar_row($estimate);
    }

    public function count_estimate_activities($estimate_id)
    {
        return (int) $this->db
            ->where(array(
                'module' => 'estimates',
                'module_field_id' => (int) $estimate_id,
            ))
            ->count_all_results('tbl_activities');
    }

    public function get_estimate_activity_page($estimate_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' => 'estimates',
                'tbl_activities.module_field_id' => (int) $estimate_id,
            ))
            ->order_by('tbl_activities.activity_date', 'DESC')
            ->limit((int) $limit, (int) $offset)
            ->get()
            ->result();
    }

    public function get_estimates($filterBy = null, $client_id = null)
    {
        if (!empty($client_id)) {
            $all_invoice = get_result('tbl_estimates', array('client_id' => $client_id));
        } else {
            $all_invoice = $this->get_permission('tbl_estimates');
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            return $all_invoice;
        } else {
            if (!empty($all_invoice)) {
                $all_invoice = array_reverse($all_invoice);
                foreach ($all_invoice as $v_invoices) {

                    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->estimate_month) == safe_strtotime($month)) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'expired') {
                        if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $v_invoices->status == ('pending') || safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $v_invoices->status == ('draft')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == $v_invoices->status) {
                        $invoice[] = $v_invoices;
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if (safe_strtotime($v_invoices->estimate_year) == safe_strtotime($year)) {
                            $invoice[] = $v_invoices;
                        }
                    }
                }
            }
        }
        if (!empty($invoice)) {
            return $invoice;
        } else {
            return array();
        }
    }

    public function get_client_estimates($filterBy = null, $client_id = null)
    {
        if (!empty($client_id)) {
            $all_invoice = get_result('tbl_estimates', array('client_id' => $client_id, 'status !=' => 'draft'));
        } else {
            $all_invoice = $this->get_permission('tbl_estimates');
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            return $all_invoice;
        } else {
            if (!empty($all_invoice)) {
                $all_invoice = array_reverse($all_invoice);
                foreach ($all_invoice as $v_invoices) {

                    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->estimate_month) == safe_strtotime($month)) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == 'expired') {
                        if (safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $v_invoices->status == ('pending') || safe_strtotime($v_invoices->due_date) < safe_strtotime(date('Y-m-d')) && $v_invoices->status == ('draft')) {
                            $invoice[] = $v_invoices;
                        }
                    } else if ($filterBy == $v_invoices->status) {
                        $invoice[] = $v_invoices;
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if (safe_strtotime($v_invoices->estimate_year) == safe_strtotime($year)) {
                            $invoice[] = $v_invoices;
                        }
                    }
                }
            }
        }
        if (!empty($invoice)) {
            return $invoice;
        } else {
            return array();
        }
    }

    public function get_estimate_report($filterBy = null, $range = null)
    {
        if (!empty($filterBy) && is_numeric($filterBy)) {
            $estimates = $this->db->where('client_id', $filterBy)->get('tbl_estimates')->result();
        } else {
            $all_estimates = $this->get_permission('tbl_estimates');
        }
        if (empty($filterBy) || !empty($filterBy) && $filterBy == 'all') {
            $estimates = $all_estimates;
        } else {
            if (!empty($all_estimates)) {
                $all_estimates = array_reverse($all_estimates);
                foreach ($all_estimates as $v_estimate) {
                    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_estimate->estimate_month) == safe_strtotime($month)) {
                            $estimates[] = $v_estimate;
                        }
                    } else if ($filterBy == 'expired') {
                        if (safe_strtotime($v_estimate->due_date) < safe_strtotime(date('Y-m-d')) && $v_estimate->status == ('pending') || safe_strtotime($v_estimate->due_date) < safe_strtotime(date('Y-m-d')) && $v_estimate->status == ('draft')) {
                            $estimates[] = $v_estimate;
                        }
                    } else if ($filterBy == $v_estimate->status) {
                        $estimates[] = $v_estimate;
                    } else if (strstr($filterBy, '_')) {
                        $year = str_replace('_', '', $filterBy);
                        if (safe_strtotime($v_estimate->estimate_year) == safe_strtotime($year)) {
                            $estimates[] = $v_estimate;
                        }
                    }
                }
            }
        }
        if (!empty($estimates)) {
            $estimate_info = array();
            if (!empty($range[0])) {
                foreach ($estimates as $v_estimate) {
                    if ($v_estimate->estimate_date >= $range[0] && $v_estimate->estimate_date <= $range[1]) {
                        array_push($estimate_info, $v_estimate);
                    }
                }
                return $estimate_info;
            } else {
                return $estimates;
            }
        } else {
            return array();
        }
    }
}
