<?php

namespace App\Models;

class Invoices_model extends Crud_model {

    protected $table = null;

    function __construct() {
        $this->table = 'invoices';
        parent::__construct($this->table);
    }

    function get_details($options = array()) {
        $invoices_table = $this->db->prefixTable('invoices');
        $clients_table = $this->db->prefixTable('clients');
        $projects_table = $this->db->prefixTable('projects');
        $taxes_table = $this->db->prefixTable('taxes');
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $users_table = $this->db->prefixTable('users');

        $tolarance = get_paid_status_tolarance();

        $where = "";
        $id = $this->_get_clean_value($options, "id");
        if ($id) {
            $where .= " AND $invoices_table.id=$id";
        }
        $type = $this->_get_clean_value($options, "type");
        if ($type) {
            $where .= " AND $invoices_table.type='$type'";
        }
        $client_id = $this->_get_clean_value($options, "client_id");
        if ($client_id) {
            $where .= " AND $invoices_table.client_id=$client_id";
        }
        $subscription_id = $this->_get_clean_value($options, "subscription_id");
        if ($subscription_id) {
            $where .= " AND $invoices_table.subscription_id=$subscription_id";
        }

        $exclude_draft = $this->_get_clean_value($options, "exclude_draft");
        if ($exclude_draft) {
            $where .= " AND $invoices_table.status!='draft' ";
        }

        $project_id = $this->_get_clean_value($options, "project_id");
        if ($project_id) {
            $where .= " AND $invoices_table.project_id=$project_id";
        }

        $order_id = $this->_get_clean_value($options, "order_id");
        if ($order_id) {
            $where .= " AND $invoices_table.order_id=$order_id";
        }

        $start_date = $this->_get_clean_value($options, "start_date");
        $end_date = $this->_get_clean_value($options, "end_date");

        $generate_reports_based_on = "$invoices_table.due_date";
        if (get_setting("generate_reports_based_on") == "bill_date") {
            $generate_reports_based_on = "$invoices_table.bill_date";
        }

        if ($start_date && $end_date) {
            $where .= " AND ($generate_reports_based_on BETWEEN '$start_date' AND '$end_date') ";
        }

        $reminder_due_date = $this->_get_clean_value($options, "reminder_due_date");
        $reminder_due_date2 = $this->_get_clean_value($options, "reminder_due_date2");
        if ($reminder_due_date && $reminder_due_date2) {
            $where .= " AND ($invoices_table.due_date='$reminder_due_date' OR $invoices_table.due_date='$reminder_due_date2') ";
        } else if ($reminder_due_date) {
            $where .= " AND $invoices_table.due_date='$reminder_due_date' ";
        } else if ($reminder_due_date2) {
            $where .= " AND $invoices_table.due_date='$reminder_due_date2' ";
        }

        if ($reminder_due_date || $reminder_due_date2) { //ensure the client is not deleted
            $where .= " AND $clients_table.deleted=0 ";
        }

        $next_recurring_start_date = $this->_get_clean_value($options, "next_recurring_start_date");
        $next_recurring_end_date = $this->_get_clean_value($options, "next_recurring_end_date");
        if ($next_recurring_start_date && $next_recurring_end_date) {
            $where .= " AND ($invoices_table.next_recurring_date BETWEEN '$next_recurring_start_date' AND '$next_recurring_end_date') ";
        } else if ($next_recurring_start_date) {
            $where .= " AND $invoices_table.next_recurring_date >= '$next_recurring_start_date' ";
        } else if ($next_recurring_end_date) {
            $where .= " AND $invoices_table.next_recurring_date <= '$next_recurring_end_date' ";
        }

        $recurring_invoice_id = $this->_get_clean_value($options, "recurring_invoice_id");
        if ($recurring_invoice_id) {
            $where .= " AND $invoices_table.recurring_invoice_id=$recurring_invoice_id";
        }

        $now = get_my_local_time("Y-m-d");
        //  $options['status'] = "draft";
        $status = $this->_get_clean_value($options, "status");

        if ($status === "draft") {
            $where .= " AND $invoices_table.status='draft' AND IFNULL(payments_table.payment_received,0)<=0";
        } else if ($status === "not_paid") {
            $where .= " AND $invoices_table.type = 'invoice' AND $invoices_table.status ='not_paid' AND IFNULL(payments_table.payment_received,0)<=0";
        } else if ($status === "partially_paid") {
            $where .= " AND $invoices_table.type = 'invoice' AND IFNULL(payments_table.payment_received,0)>0 AND IFNULL(payments_table.payment_received,0)<$invoices_table.invoice_total-$tolarance";
        } else if ($status === "fully_paid") {
            $where .= " AND $invoices_table.type = 'invoice' AND TRUNCATE(IFNULL(payments_table.payment_received,0),2)>=$invoices_table.invoice_total-$tolarance AND $invoices_table.status ='not_paid'";
        } else if ($status === "overdue") {
            $where .= " AND $invoices_table.type = 'invoice' AND $invoices_table.status ='not_paid' AND $invoices_table.due_date<'$now' AND TRUNCATE(IFNULL(payments_table.payment_received,0),2)<$invoices_table.invoice_total-$tolarance";
        } else if ($status === "cancelled") {
            $where .= " AND $invoices_table.type = 'invoice' AND $invoices_table.status='cancelled' ";
        } else if ($status == "not_paid_and_partially_paid") {
            $where .= " AND $invoices_table.type = 'invoice' AND ($invoices_table.status ='not_paid' AND IFNULL(payments_table.payment_received,0)<=0 OR (IFNULL(payments_table.payment_received,0)>0 AND IFNULL(payments_table.payment_received,0)<$invoices_table.invoice_total-$tolarance))";
        } else if ($status == "credited") {
            $where .= " AND $invoices_table.type = 'invoice' AND $invoices_table.status='credited' ";
        }


        $recurring = $this->_get_clean_value($options, "recurring");
        if ($recurring) {
            $where .= " AND $invoices_table.recurring=1";
        }

        $currency = $this->_get_clean_value($options, "currency");
        if ($currency) {
            $where .= $this->_get_clients_of_currency_query($currency, $invoices_table, $clients_table);
        }

        $exclude_due_reminder_date = $this->_get_clean_value($options, "exclude_due_reminder_date");
        if ($exclude_due_reminder_date) {
            $where .= " AND ($invoices_table.due_reminder_date IS NULL OR $invoices_table.due_reminder_date !='$exclude_due_reminder_date') ";
        }

        $exclude_recurring_reminder_date = $this->_get_clean_value($options, "exclude_recurring_reminder_date");
        if ($exclude_recurring_reminder_date) {
            $where .= " AND ($invoices_table.recurring_reminder_date IS NULL OR $invoices_table.recurring_reminder_date !='$exclude_recurring_reminder_date') ";
        }

        $show_own_client_invoice_user_id = get_array_value($options, "show_own_client_invoice_user_id");
        if ($show_own_client_invoice_user_id) {
            $where .= " AND $clients_table.owner_id = $show_own_client_invoice_user_id";
        }

        $show_own_invoices_only_user_id = get_array_value($options, "show_own_invoices_only_user_id");
        if ($show_own_invoices_only_user_id) {
            $where .= " AND $invoices_table.created_by = $show_own_invoices_only_user_id";
        }

        $select_labels_data_query = $this->get_labels_data_query();

        //prepare custom fild binding query
        $custom_fields = get_array_value($options, "custom_fields");
        $custom_field_filter = get_array_value($options, "custom_field_filter");
        $custom_field_query_info = $this->prepare_custom_field_query_string("invoices", $custom_fields, $invoices_table, $custom_field_filter);
        $select_custom_fieds = get_array_value($custom_field_query_info, "select_string");
        $join_custom_fieds = get_array_value($custom_field_query_info, "join_string");
        $custom_fields_where = get_array_value($custom_field_query_info, "where_string");

        $sql = "SELECT $invoices_table.*, $clients_table.currency, $clients_table.currency_symbol, $clients_table.company_name, $clients_table.vat_number, $clients_table.gst_number, $projects_table.title AS project_title, credit_note_table.id AS credit_note_id, credit_note_table.display_id AS credit_note_display_id, main_invoice_table.display_id AS main_invoice_display_id, recurring_invoice_table.display_id AS recurring_invoice_display_id,
           $invoices_table.invoice_total AS invoice_value, IFNULL(payments_table.payment_received,0) AS payment_received, tax_table.percentage AS tax_percentage, tax_table2.percentage AS tax_percentage2, tax_table3.percentage AS tax_percentage3, CONCAT($users_table.first_name, ' ',$users_table.last_name) AS cancelled_by_user, $select_labels_data_query $select_custom_fieds
        FROM $invoices_table
        LEFT JOIN (
            SELECT $invoices_table.id, $invoices_table.main_invoice_id, $invoices_table.display_id
            FROM $invoices_table 
            WHERE $invoices_table.deleted=0 AND $invoices_table.main_invoice_id!=0
        ) AS credit_note_table ON credit_note_table.main_invoice_id=$invoices_table.id
        LEFT JOIN (
            SELECT $invoices_table.id, $invoices_table.main_invoice_id, $invoices_table.display_id
            FROM $invoices_table
            WHERE $invoices_table.deleted=0
        ) AS main_invoice_table ON main_invoice_table.id=$invoices_table.main_invoice_id
        LEFT JOIN (
            SELECT $invoices_table.id, $invoices_table.recurring_invoice_id, $invoices_table.display_id
            FROM $invoices_table
            WHERE $invoices_table.deleted=0
        ) AS recurring_invoice_table ON recurring_invoice_table.id=$invoices_table.recurring_invoice_id
        LEFT JOIN $clients_table ON $clients_table.id= $invoices_table.client_id
        LEFT JOIN $projects_table ON $projects_table.id= $invoices_table.project_id
        LEFT JOIN $users_table ON $users_table.id= $invoices_table.cancelled_by
        LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table ON tax_table.id = $invoices_table.tax_id
        LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table2 ON tax_table2.id = $invoices_table.tax_id2
        LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table3 ON tax_table3.id = $invoices_table.tax_id3
        LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id
        $join_custom_fieds
        WHERE $invoices_table.deleted=0 $where $custom_fields_where";
        return $this->db->query($sql);
    }

    function get_invoice_total_summary($invoice_id) {
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $clients_table = $this->db->prefixTable('clients');
        $invoices_table = $this->db->prefixTable('invoices');

        $invoice_id = $this->_get_clean_value($invoice_id);

        $result = $this->get_invoice_total_meta($invoice_id);

        $client_sql = "SELECT $clients_table.currency_symbol, $clients_table.currency FROM $clients_table WHERE $clients_table.id=(SELECT $invoices_table.client_id FROM $invoices_table WHERE $invoices_table.id=$invoice_id LIMIT 1)";
        $client = $this->db->query($client_sql)->getRow();

        $result->currency_symbol = $client->currency_symbol ? $client->currency_symbol : get_setting("currency_symbol");
        $result->currency = $client->currency ? $client->currency : get_setting("default_currency");

        $payment_sql = "SELECT SUM($invoice_payments_table.amount) AS total_paid
        FROM $invoice_payments_table
        WHERE $invoice_payments_table.deleted=0 AND $invoice_payments_table.invoice_id=$invoice_id";
        $payment = $this->db->query($payment_sql)->getRow();

        $result->total_paid = is_null($payment->total_paid) ? 0 : $payment->total_paid;
        $result->balance_due = number_format($result->invoice_total, 2, ".", "") - number_format($result->total_paid, 2, ".", "");

        return $result;
    }

    function invoice_statistics($options = array()) {
        $invoices_table = $this->db->prefixTable('invoices');
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $clients_table = $this->db->prefixTable('clients');

        $info = new \stdClass();
        $year = get_my_local_time("Y");

        $where = "";
        $payments_where = "";
        $invoices_where = "";
        $invoice_date_where = "";

        $start_date = $this->_get_clean_value($options, "start_date");
        $end_date = $this->_get_clean_value($options, "end_date");

        $generate_reports_based_on = "$invoices_table.due_date";
        if (get_setting("generate_reports_based_on") == "bill_date") {
            $generate_reports_based_on = "$invoices_table.bill_date";
        }

        if ($start_date && $end_date) {
            $invoice_date_where .= " AND ($generate_reports_based_on BETWEEN '$start_date' AND '$end_date')";
        } else {
            $invoice_date_where .= " AND YEAR($generate_reports_based_on)=$year";
        }

        $client_id = $this->_get_clean_value($options, "client_id");
        if ($client_id) {
            $where .= " AND $invoices_table.client_id=$client_id";
        } else {
            $invoices_where = $this->_get_clients_of_currency_query($this->_get_clean_value($options, "currency"), $invoices_table, $clients_table);

            $payments_where = " AND $invoice_payments_table.invoice_id IN(SELECT $invoices_table.id FROM $invoices_table WHERE $invoices_table.deleted=0 $invoices_where)";
        }

        $payments = $this->_get_clean_value($options, "payments");
        if ($payments) {
            $payments = "SELECT SUM($invoice_payments_table.amount) AS total, MONTH($invoice_payments_table.payment_date) AS month
            FROM $invoice_payments_table
            LEFT JOIN $invoices_table ON $invoices_table.id=$invoice_payments_table.invoice_id    
            WHERE $invoice_payments_table.deleted=0 AND YEAR($invoice_payments_table.payment_date)=$year AND $invoices_table.deleted=0 $where $payments_where
            GROUP BY MONTH($invoice_payments_table.payment_date)";

            $info->payments = $this->db->query($payments)->getResult();
        }


        $invoices = "SELECT SUM($invoices_table.invoice_total) AS total, MONTH(bill_date) AS month 
            FROM $invoices_table  
            WHERE $invoices_table.deleted=0 AND $invoices_table.status='not_paid' $where $invoice_date_where $invoices_where
            GROUP BY MONTH(bill_date)";

        $info->invoices = $this->db->query($invoices)->getResult();
        $info->currencies = $this->get_used_currencies_of_client()->getResult();

        return $info;
    }

    function get_used_currencies_of_client() {
        $clients_table = $this->db->prefixTable('clients');
        $default_currency = get_setting("default_currency");

        $sql = "SELECT $clients_table.currency, $clients_table.currency_symbol
            FROM $clients_table
            WHERE $clients_table.deleted=0 AND $clients_table.currency!='' AND $clients_table.currency!='$default_currency'
            GROUP BY $clients_table.currency";

        return $this->db->query($sql);
    }

    function get_invoices_total_and_paymnts($options = array()) {
        $invoices_table = $this->db->prefixTable('invoices');
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $clients_table = $this->db->prefixTable('clients');

        $info = new \stdClass();

        $tolarance = get_paid_status_tolarance();

        $where = "";

        $return_only = get_array_value($options, "return_only");

        $currency = $this->_get_clean_value($options, "currency");
        if ($currency) {
            $where .= $this->_get_clients_of_currency_query($currency, $invoices_table, $clients_table);
        }

        $client_id = $this->_get_clean_value($options, "client_id");
        if ($client_id) {
            $where .= " AND $invoices_table.client_id=$client_id";
        }

        $payments = "SELECT SUM($invoice_payments_table.amount) AS total,
            (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=(
                SELECT $invoices_table.client_id FROM $invoices_table WHERE $invoices_table.id=$invoice_payments_table.invoice_id
                )
            ) AS currency
            FROM $invoice_payments_table
            LEFT JOIN $invoices_table ON $invoices_table.id=$invoice_payments_table.invoice_id    
            WHERE $invoice_payments_table.deleted=0 AND $invoices_table.deleted=0 $where
            GROUP BY currency";

        $now = get_my_local_time("Y-m-d");

        $invoices = "SELECT SUM($invoices_table.invoice_total) AS total, SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table
            WHERE $invoices_table.deleted=0 AND $invoices_table.status='not_paid' $where
            GROUP BY currency";

        $draft = "SELECT SUM($invoices_table.invoice_total) AS total, SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table
            WHERE $invoices_table.deleted=0 AND $invoices_table.status='draft' $where
            GROUP BY currency";

        $fully_paid = "SELECT SUM($invoices_table.invoice_total) AS total, SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table
            LEFT JOIN (SELECT invoice_id, SUM($invoice_payments_table.amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id 
            WHERE  $invoices_table.deleted=0 AND $invoices_table.status='not_paid' AND TRUNCATE(IFNULL(payments_table.payment_received,0),2)>=($invoices_table.invoice_total-$tolarance) $where
            GROUP BY currency";

        $partially_paid = "SELECT SUM($invoices_table.invoice_total) AS total, SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table
            LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id 
            WHERE $invoices_table.deleted=0 AND $invoices_table.status='not_paid' AND IFNULL(payments_table.payment_received,0)>0 && TRUNCATE(IFNULL(payments_table.payment_received,0),2) < $invoices_table.invoice_total-$tolarance $where
            GROUP BY currency";

        $not_paid = "SELECT SUM($invoices_table.invoice_total) AS total, SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table            
            LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id 
            WHERE $invoices_table.deleted=0 AND $invoices_table.status='not_paid' AND IFNULL(payments_table.payment_received,0)<=0 $where
            GROUP BY currency";

        $overdue = "SELECT SUM($invoices_table.invoice_total - IFNULL(payments_table.payment_received,0)) AS total , SUM(1) AS count, (SELECT $clients_table.currency FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id) AS currency
            FROM $invoices_table
            LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id 
            WHERE $invoices_table.deleted=0  AND $invoices_table.status='not_paid' AND $invoices_table.due_date<'$now' AND TRUNCATE(IFNULL(payments_table.payment_received,0),2)<$invoices_table.invoice_total-$tolarance $where
            GROUP BY currency";

        $payments_total = 0;

        if (!$return_only || $return_only == "payments" || $return_only == "due") {
            $payments_result = $this->db->query($payments)->getResult();
            foreach ($payments_result as $payment) {
                if ($currency) {
                    $payments_total += $payment->total ? $payment->total : 0;  //no need to convert since user will see currency wise total. 
                } else {
                    $payments_total += get_converted_amount($payment->currency, $payment->total);
                }
            }
        }

        $invoices_total = 0;
        $invoices_count = 0;

        if (!$return_only || $return_only == "invoices" || $return_only == "due") {
            $invoices_result = $this->db->query($invoices)->getResult();
            foreach ($invoices_result as $invoice) {
                $invoices_count += $invoice->count;
                if ($currency) {
                    $invoices_total += $invoice->total ? $invoice->total : 0; //no need to convert since user will see currency wise total. 
                } else {
                    $invoices_total += get_converted_amount($invoice->currency, $invoice->total);
                }
            }
        }

        $draft_total = 0;
        $draft_count = 0;
        if (!$return_only || $return_only == "draft") {
            $drafts_result = $this->db->query($draft)->getResult();
            foreach ($drafts_result as $draft) {
                $draft_count += $draft->count;
                if ($currency) {
                    $draft_total += $draft->total ? $draft->total : 0;
                } else {
                    $draft_total += get_converted_amount($draft->currency, $draft->total);
                }
            }
        }

        $fully_paid_total = 0;
        $fully_paid_count = 0;
        if (!$return_only || $return_only == "fully_paid") {
            $fully_paid_result = $this->db->query($fully_paid)->getResult();
            foreach ($fully_paid_result as $fully_paid) {
                $fully_paid_count += $fully_paid->count;
                if ($currency) {
                    $fully_paid_total += $fully_paid->total ? $fully_paid->total : 0;
                } else {
                    $fully_paid_total += get_converted_amount($fully_paid->currency, $fully_paid->total);
                }
            }
        }

        $partially_paid_total = 0;
        $partially_paid_count = 0;
        if (!$return_only || $return_only == "partially_paid") {
            $partially_paid_result = $this->db->query($partially_paid)->getResult();
            foreach ($partially_paid_result as $partially_paid) {
                $partially_paid_count += $partially_paid->count;
                if ($currency) {
                    $partially_paid_total += $partially_paid->total ? $partially_paid->total : 0;
                } else {
                    $partially_paid_total += get_converted_amount($partially_paid->currency, $partially_paid->total);
                }
            }
        }

        $not_paid_total = 0;
        $not_paid_count = 0;
        if (!$return_only || $return_only == "not_paid") {
            $not_paid_result = $this->db->query($not_paid)->getResult();
            foreach ($not_paid_result as $not_paid) {
                $not_paid_count += $not_paid->count;
                if ($currency) {
                    $not_paid_total += $not_paid->total ? $not_paid->total : 0;
                } else {
                    $not_paid_total += get_converted_amount($not_paid->currency, $not_paid->total);
                }
            }
        }

        $overdue_total = 0;
        $overdue_count = 0;
        if (!$return_only || $return_only == "overdue") {
            $overdue_result = $this->db->query($overdue)->getResult();
            foreach ($overdue_result as $overdue) {
                $overdue_count += $overdue->count;

                if ($currency) {
                    $overdue_total += $overdue->total ? $overdue->total : 0;
                } else {
                    $overdue_total += get_converted_amount($overdue->currency, $overdue->total);
                }
            }
        }

        $info->payments_total = $payments_total;

        $info->invoices_total = $invoices_total;
        $info->invoices_count = $invoices_count;

        $info->draft_total = $draft_total;
        $info->draft_count = $draft_count;

        $info->fully_paid_total = $fully_paid_total;
        $info->fully_paid_count = $fully_paid_count;

        $info->partially_paid_total = $partially_paid_total;
        $info->partially_paid_count = $partially_paid_count;

        $info->not_paid = $not_paid_total;
        $info->not_paid_count = $not_paid_count;

        $info->overdue = $overdue_total;
        $info->overdue_count = $overdue_count;

        $info->due = ignor_minor_value($invoices_total - $payments_total);

        return $info;
    }

    //update invoice status
    function update_invoice_status($invoice_id = 0, $status = "not_paid") {
        $status = $this->_get_clean_value(array("status" => $status), "status");
        $status_data = array("status" => $status);
        return $this->ci_save($status_data, $invoice_id);
    }

    //get the recurring invoices which are ready to renew as on a given date
    function get_renewable_invoices($date) {
        $invoices_table = $this->db->prefixTable('invoices');
        $date = $this->_get_clean_value($date);

        $sql = "SELECT * FROM $invoices_table
                        WHERE $invoices_table.deleted=0 AND $invoices_table.recurring=1
                        AND $invoices_table.next_recurring_date IS NOT NULL AND $invoices_table.next_recurring_date<='$date'
                        AND ($invoices_table.no_of_cycles < 1 OR ($invoices_table.no_of_cycles_completed < $invoices_table.no_of_cycles ))";

        return $this->db->query($sql);
    }

    //get invoices dropdown list
    function get_invoices_dropdown_list($client_id = 0) {
        $invoices_table = $this->db->prefixTable('invoices');
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $clients_table = $this->db->prefixTable('clients');

        $where = "";
        $client_id = $this->_get_clean_value($client_id);
        if ($client_id) {
            $where .= " AND $invoices_table.client_id=$client_id";
        }

        $sql = "SELECT $invoices_table.id, $invoices_table.display_id, (IFNULL($invoices_table.invoice_total, 0) - IFNULL(payments_table.payment_received, 0)) AS invoice_due,
                    (SELECT $clients_table.currency_symbol FROM $clients_table WHERE $clients_table.id=$invoices_table.client_id limit 1) AS currency_symbol
                FROM $invoices_table
                LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id
                WHERE $invoices_table.deleted=0 AND $invoices_table.type = 'invoice' AND $invoices_table.status NOT IN ('credited', 'cancelled') AND IFNULL($invoices_table.invoice_total, 0) > IFNULL(payments_table.payment_received, 0) $where
                ORDER BY $invoices_table.id DESC";

        return $this->db->query($sql);
    }

    //get label suggestions
    function get_label_suggestions() {
        $invoices_table = $this->db->prefixTable('invoices');
        $sql = "SELECT GROUP_CONCAT(labels) as label_groups
        FROM $invoices_table
        WHERE $invoices_table.deleted=0";
        return $this->db->query($sql)->getRow()->label_groups;
    }


    //save initial number of invoice
    function save_initial_number_of_invoice($value) {
        $invoices_table = $this->db->prefixTable('invoices');
        $value = $this->_get_clean_value($value);

        $sql = "ALTER TABLE $invoices_table AUTO_INCREMENT=$value;";

        return $this->db->query($sql);
    }

    function get_invoice_total_meta($invoice_id) {
        $id = $this->_get_clean_value($invoice_id);

        $invoices_table = $this->db->prefixTable('invoices');
        $invoice_items_table = $this->db->prefixTable('invoice_items');
        $info = $this->get_sales_total_meta($id, $invoices_table, $invoice_items_table);
        return $info;
    }

    function update_invoice_total_meta($invoice_id) {
        $info = $this->get_invoice_total_meta($invoice_id);

        $data = array(
            "invoice_total" => $info->invoice_total,
            "invoice_subtotal" => $info->invoice_subtotal,
            "discount_total" => $info->discount_total,
            "tax" => $info->tax,
            "tax2" => $info->tax2,
            "tax3" => $info->tax3
        );

        return $this->ci_save($data, $invoice_id);
    }

    function save_invoice_and_update_total($data, $id = 0) {
        $save_id = $this->ci_save($data, $id);

        $update_total = false;
        $total_updateable_fields = array("tax_id", "tax_id2", "tax_id3", "discount_amount", "discount_amount_type", "discount_type");
        foreach ($total_updateable_fields as $field) {
            if (array_key_exists($field, $data)) {
                $update_total = true;
            }
        }

        if ($update_total) {
            $this->update_invoice_total_meta($save_id);
        }

        return $save_id;
    }

    function get_invoices_summary($options = array()) {
        $invoice_payments_table = $this->db->prefixTable('invoice_payments');
        $clients_table = $this->db->prefixTable('clients');
        $invoices_table = $this->db->prefixTable('invoices');

        $where = "";
        $start_date = $this->_get_clean_value($options, "start_date");
        $end_date = $this->_get_clean_value($options, "end_date");

        $generate_reports_based_on = "$invoices_table.due_date";
        if (get_setting("generate_reports_based_on") == "bill_date") {
            $generate_reports_based_on = "$invoices_table.bill_date";
        }

        if ($start_date && $end_date) {
            $where .= " AND ($generate_reports_based_on BETWEEN '$start_date' AND '$end_date') ";
        }

        $show_own_client_invoice_user_id = get_array_value($options, "show_own_client_invoice_user_id");
        if ($show_own_client_invoice_user_id) {
            $where .= " AND $clients_table.owner_id = $show_own_client_invoice_user_id";
        }

        $show_own_invoices_only_user_id = get_array_value($options, "show_own_invoices_only_user_id");
        if ($show_own_invoices_only_user_id) {
            $where .= " AND $invoices_table.created_by = $show_own_invoices_only_user_id";
        }

        $selected_currency = get_array_value($options, "currency");
        $default_currency = get_setting("default_currency");
        $currency = $selected_currency ? $selected_currency : get_setting("default_currency");
        $currency = $this->_get_clean_value(array("currency" => $currency), "currency");

        $where .= ($currency == $default_currency) ? " AND ($clients_table.currency='$default_currency' OR $clients_table.currency='' OR $clients_table.currency IS NULL)" : " AND $clients_table.currency='$currency'";

        $sql = "SELECT COUNT($invoices_table.id) AS invoice_count, SUM($invoices_table.invoice_total) AS invoice_total, SUM($invoices_table.discount_total) AS discount_total, SUM($invoices_table.tax) AS tax_total, SUM($invoices_table.tax2) AS tax2_total, SUM($invoices_table.tax3) AS tax3_total,
                $invoices_table.client_id, $clients_table.company_name AS client_name, $clients_table.currency, $clients_table.currency_symbol,
                SUM(payments_table.payment_received) AS payment_received
            FROM $invoices_table
            LEFT JOIN $clients_table ON $clients_table.id = $invoices_table.client_id             
            LEFT JOIN (SELECT SUM($invoice_payments_table.amount) AS payment_received, $invoice_payments_table.invoice_id FROM $invoice_payments_table WHERE $invoice_payments_table.deleted=0 GROUP BY $invoice_payments_table.invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id
            WHERE $invoices_table.deleted=0 AND $invoices_table.status = 'not_paid' $where
            GROUP BY $invoices_table.client_id";
        $result = $this->db->query($sql);

        return $result;
    }

    //get the last sequence number for a given year
    function get_last_invoice_sequence($year = 0) {
        $invoices_table = $this->db->prefixTable('invoices');
        $year = $this->_get_clean_value($year);

        $where = "";
        if ($year) {
            $where =  " AND $invoices_table.number_year=$year ";
        }

        $sql = "SELECT MAX($invoices_table.number_sequence) AS last_sequence
               FROM $invoices_table
               WHERE $invoices_table.deleted=0 $where";

        $result = $this->db->query($sql)->getRow()->last_sequence;

        return $result ? $result : 0;
    }

    function delete_permanently_with_sub_items($id) {
        if ($this->delete_permanently($id)) {
            $invoice_items_table = $this->db->prefixTable('invoice_items');
            $this->db->query("DELETE FROM $invoice_items_table WHERE $invoice_items_table.invoice_id=$id");
            return true;
        }
    }

    function get_invoice_basic_info($invoice_id) {
        $invoices_table = $this->db->prefixTable('invoices');
        $clients_table = $this->db->prefixTable('clients');

        $sql = "SELECT $invoices_table.id, $invoices_table.created_by, $clients_table.owner_id AS client_owner_id
                FROM $invoices_table
                LEFT JOIN $clients_table ON $clients_table.id = $invoices_table.client_id
                WHERE $invoices_table.id=$invoice_id";

        return $this->db->query($sql)->getRow();
    }

    function get_public_pay_invoice_url($invoice_id, $client_id, $contact_id) {
        $invoice_id = $this->_get_clean_value($invoice_id);
        $client_id = $this->_get_clean_value($client_id);
        $contact_id = $this->_get_clean_value($contact_id);

        $verification_model = model("App\Models\Verification_model");
        $verification_info = $verification_model->get_details(array("type" => "invoice_payment"))->getResult();

        // Try to match by both invoice_id and contact_id
        foreach ($verification_info as $verification) {
            $params = @unserialize($verification->params);
            if (is_array($params) && isset($params['invoice_id'], $params['contact_id']) && $params['invoice_id'] == $invoice_id && $params['contact_id'] == $contact_id) {
                return get_uri("pay_invoice/index/" . $verification->code);
            }
        }

        // If not found, create a new one
        if ($client_id && $contact_id) {
            $code = make_random_string();

            $verification_data = array(
                "type" => "invoice_payment",
                "code" => $code,
                "params" => serialize(array(
                    "invoice_id" => $invoice_id,
                    "client_id" => $client_id,
                    "contact_id" => $contact_id
                ))
            );

            $verification_model->ci_save($verification_data);
            return get_uri("pay_invoice/index/" . $code);
        }
    }
}
