408 lines
18 KiB
PHP
408 lines
18 KiB
PHP
<?php
|
|
if (!defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
/*
|
|
* InvoicePlane
|
|
*
|
|
* @author InvoicePlane Developers & Contributors
|
|
* @copyright Copyright (c) 2012 - 2017 InvoicePlane.com
|
|
* @license https://invoiceplane.com/license.txt
|
|
* @link https://invoiceplane.com
|
|
*/
|
|
|
|
/**
|
|
* Class Mdl_Invoice_Amounts
|
|
*/
|
|
class Mdl_Invoice_Amounts extends CI_Model
|
|
{
|
|
/**
|
|
* IP_INVOICE_AMOUNTS
|
|
* invoice_amount_id
|
|
* invoice_id
|
|
* invoice_item_subtotal SUM(item_subtotal)
|
|
* invoice_item_tax_total SUM(item_tax_total)
|
|
* invoice_tax_total
|
|
* invoice_total invoice_item_subtotal + invoice_item_tax_total + invoice_tax_total
|
|
* invoice_paid
|
|
* invoice_balance invoice_total - invoice_paid
|
|
*
|
|
* IP_INVOICE_ITEM_AMOUNTS
|
|
* item_amount_id
|
|
* item_id
|
|
* item_tax_rate_id
|
|
* item_subtotal item_quantity * item_price
|
|
* item_tax_total item_subtotal * tax_rate_percent
|
|
* item_total item_subtotal + item_tax_total
|
|
*
|
|
* @param $invoice_id
|
|
*/
|
|
public function calculate($invoice_id)
|
|
{
|
|
// Get the basic totals
|
|
$query = $this->db->query("
|
|
SELECT SUM(item_subtotal) AS invoice_item_subtotal,
|
|
SUM(item_tax_total) AS invoice_item_tax_total,
|
|
SUM(item_subtotal) + SUM(item_tax_total) AS invoice_total,
|
|
SUM(item_discount) AS invoice_item_discount
|
|
FROM ip_invoice_item_amounts
|
|
WHERE item_id IN (
|
|
SELECT item_id FROM ip_invoice_items WHERE invoice_id = " . $this->db->escape($invoice_id) . "
|
|
)
|
|
");
|
|
|
|
$invoice_amounts = $query->row();
|
|
|
|
$invoice_item_subtotal = $invoice_amounts->invoice_item_subtotal - $invoice_amounts->invoice_item_discount;
|
|
$invoice_subtotal = $invoice_item_subtotal + $invoice_amounts->invoice_item_tax_total;
|
|
$invoice_total = $this->calculate_discount($invoice_id, $invoice_subtotal);
|
|
|
|
// Get the amount already paid
|
|
$query = $this->db->query("
|
|
SELECT SUM(payment_amount) AS invoice_paid
|
|
FROM ip_payments
|
|
WHERE invoice_id = " . $this->db->escape($invoice_id)
|
|
);
|
|
|
|
$invoice_paid = $query->row()->invoice_paid ? floatval($query->row()->invoice_paid) : 0;
|
|
|
|
// Create the database array and insert or update
|
|
$db_array = array(
|
|
'invoice_id' => $invoice_id,
|
|
'invoice_item_subtotal' => $invoice_item_subtotal,
|
|
'invoice_item_tax_total' => $invoice_amounts->invoice_item_tax_total,
|
|
'invoice_total' => $invoice_total,
|
|
'invoice_paid' => $invoice_paid,
|
|
'invoice_balance' => $invoice_total - $invoice_paid
|
|
);
|
|
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
|
|
if ($this->db->get('ip_invoice_amounts')->num_rows()) {
|
|
// The record already exists; update it
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->update('ip_invoice_amounts', $db_array);
|
|
} else {
|
|
// The record does not yet exist; insert it
|
|
$this->db->insert('ip_invoice_amounts', $db_array);
|
|
}
|
|
|
|
// Calculate the invoice taxes
|
|
$this->calculate_invoice_taxes($invoice_id);
|
|
|
|
// Get invoice status
|
|
$this->load->model('invoices/mdl_invoices');
|
|
$invoice = $this->mdl_invoices->get_by_id($invoice_id);
|
|
$invoice_is_credit = ($invoice->creditinvoice_parent_id > 0 ? true : false);
|
|
|
|
// Set to paid if balance is zero
|
|
if ($invoice->invoice_balance == 0) {
|
|
// Check if the invoice total is not zero or negative
|
|
if ($invoice->invoice_total != 0 || $invoice_is_credit) {
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$payment = $this->db->get('ip_payments')->row();
|
|
$payment_method_id = ($payment->payment_method_id ? $payment->payment_method_id : 0);
|
|
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->set('invoice_status_id', 4);
|
|
$this->db->set('payment_method', $payment_method_id);
|
|
$this->db->update('ip_invoices');
|
|
|
|
// Set to read-only if applicable
|
|
if (
|
|
$this->config->item('disable_read_only') == false
|
|
&& $invoice->invoice_status_id == get_setting('read_only_toggle')
|
|
) {
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->set('is_read_only', 1);
|
|
$this->db->update('ip_invoices');
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param $invoice_id
|
|
* @param $invoice_total
|
|
* @return float
|
|
*/
|
|
public function calculate_discount($invoice_id, $invoice_total)
|
|
{
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$invoice_data = $this->db->get('ip_invoices')->row();
|
|
|
|
$total = (float)number_format($invoice_total, 2, '.', '');
|
|
$discount_amount = (float)number_format($invoice_data->invoice_discount_amount, 2, '.', '');
|
|
$discount_percent = (float)number_format($invoice_data->invoice_discount_percent, 2, '.', '');
|
|
|
|
$total = $total - $discount_amount;
|
|
$total = $total - round(($total / 100 * $discount_percent), 2);
|
|
|
|
return $total;
|
|
}
|
|
|
|
/**
|
|
* @param $invoice_id
|
|
*/
|
|
public function calculate_invoice_taxes($invoice_id)
|
|
{
|
|
// First check to see if there are any invoice taxes applied
|
|
$this->load->model('invoices/mdl_invoice_tax_rates');
|
|
$invoice_tax_rates = $this->mdl_invoice_tax_rates->where('invoice_id', $invoice_id)->get()->result();
|
|
|
|
if ($invoice_tax_rates) {
|
|
// There are invoice taxes applied
|
|
// Get the current invoice amount record
|
|
$invoice_amount = $this->db->where('invoice_id', $invoice_id)->get('ip_invoice_amounts')->row();
|
|
|
|
// Loop through the invoice taxes and update the amount for each of the applied invoice taxes
|
|
foreach ($invoice_tax_rates as $invoice_tax_rate) {
|
|
if ($invoice_tax_rate->include_item_tax) {
|
|
// The invoice tax rate should include the applied item tax
|
|
$invoice_tax_rate_amount = ($invoice_amount->invoice_item_subtotal + $invoice_amount->invoice_item_tax_total) * ($invoice_tax_rate->invoice_tax_rate_percent / 100);
|
|
} else {
|
|
// The invoice tax rate should not include the applied item tax
|
|
$invoice_tax_rate_amount = $invoice_amount->invoice_item_subtotal * ($invoice_tax_rate->invoice_tax_rate_percent / 100);
|
|
}
|
|
|
|
// Update the invoice tax rate record
|
|
$db_array = array(
|
|
'invoice_tax_rate_amount' => $invoice_tax_rate_amount
|
|
);
|
|
$this->db->where('invoice_tax_rate_id', $invoice_tax_rate->invoice_tax_rate_id);
|
|
$this->db->update('ip_invoice_tax_rates', $db_array);
|
|
}
|
|
|
|
// Update the invoice amount record with the total invoice tax amount
|
|
$this->db->query("
|
|
UPDATE ip_invoice_amounts
|
|
SET invoice_tax_total = (
|
|
SELECT SUM(invoice_tax_rate_amount)
|
|
FROM ip_invoice_tax_rates
|
|
WHERE invoice_id = " . $this->db->escape($invoice_id) . ")
|
|
WHERE invoice_id = " . $this->db->escape($invoice_id));
|
|
|
|
// Get the updated invoice amount record
|
|
$invoice_amount = $this->db->where('invoice_id', $invoice_id)->get('ip_invoice_amounts')->row();
|
|
|
|
// Recalculate the invoice total and balance
|
|
$invoice_total = $invoice_amount->invoice_item_subtotal + $invoice_amount->invoice_item_tax_total + $invoice_amount->invoice_tax_total;
|
|
$invoice_total = $this->calculate_discount($invoice_id, $invoice_total);
|
|
$invoice_balance = $invoice_total - $invoice_amount->invoice_paid;
|
|
|
|
// Update the invoice amount record
|
|
$db_array = array(
|
|
'invoice_total' => $invoice_total,
|
|
'invoice_balance' => $invoice_balance
|
|
);
|
|
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->update('ip_invoice_amounts', $db_array);
|
|
} else {
|
|
// No invoice taxes applied
|
|
|
|
$db_array = array(
|
|
'invoice_tax_total' => '0.00'
|
|
);
|
|
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->update('ip_invoice_amounts', $db_array);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param null $period
|
|
* @return mixed
|
|
*/
|
|
public function get_total_invoiced($period = null)
|
|
{
|
|
switch ($period) {
|
|
case 'month':
|
|
return $this->db->query("
|
|
SELECT SUM(invoice_total) AS total_invoiced
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW())
|
|
AND YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_invoiced;
|
|
case 'last_month':
|
|
return $this->db->query("
|
|
SELECT SUM(invoice_total) AS total_invoiced
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW() - INTERVAL 1 MONTH)
|
|
AND YEAR(invoice_date_created) = YEAR(NOW() - INTERVAL 1 MONTH))")->row()->total_invoiced;
|
|
case 'year':
|
|
return $this->db->query("
|
|
SELECT SUM(invoice_total) AS total_invoiced
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_invoiced;
|
|
case 'last_year':
|
|
return $this->db->query("
|
|
SELECT SUM(invoice_total) AS total_invoiced
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = YEAR(NOW() - INTERVAL 1 YEAR))")->row()->total_invoiced;
|
|
default:
|
|
return $this->db->query("SELECT SUM(invoice_total) AS total_invoiced FROM ip_invoice_amounts")->row()->total_invoiced;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param null $period
|
|
* @return mixed
|
|
*/
|
|
public function get_total_paid($period = null)
|
|
{
|
|
switch ($period) {
|
|
case 'month':
|
|
return $this->db->query("
|
|
SELECT SUM(invoice_paid) AS total_paid
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW())
|
|
AND YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_paid;
|
|
case 'last_month':
|
|
return $this->db->query("SELECT SUM(invoice_paid) AS total_paid
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW() - INTERVAL 1 MONTH)
|
|
AND YEAR(invoice_date_created) = YEAR(NOW() - INTERVAL 1 MONTH))")->row()->total_paid;
|
|
case 'year':
|
|
return $this->db->query("SELECT SUM(invoice_paid) AS total_paid
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_paid;
|
|
case 'last_year':
|
|
return $this->db->query("SELECT SUM(invoice_paid) AS total_paid
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = YEAR(NOW() - INTERVAL 1 YEAR))")->row()->total_paid;
|
|
default:
|
|
return $this->db->query("SELECT SUM(invoice_paid) AS total_paid FROM ip_invoice_amounts")->row()->total_paid;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param null $period
|
|
* @return mixed
|
|
*/
|
|
public function get_total_balance($period = null)
|
|
{
|
|
switch ($period) {
|
|
case 'month':
|
|
return $this->db->query("SELECT SUM(invoice_balance) AS total_balance
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW())
|
|
AND YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_balance;
|
|
case 'last_month':
|
|
return $this->db->query("SELECT SUM(invoice_balance) AS total_balance
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices
|
|
WHERE MONTH(invoice_date_created) = MONTH(NOW() - INTERVAL 1 MONTH)
|
|
AND YEAR(invoice_date_created) = YEAR(NOW() - INTERVAL 1 MONTH))")->row()->total_balance;
|
|
case 'year':
|
|
return $this->db->query("SELECT SUM(invoice_balance) AS total_balance
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = YEAR(NOW()))")->row()->total_balance;
|
|
case 'last_year':
|
|
return $this->db->query("SELECT SUM(invoice_balance) AS total_balance
|
|
FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(SELECT invoice_id FROM ip_invoices WHERE YEAR(invoice_date_created) = (YEAR(NOW() - INTERVAL 1 YEAR)))")->row()->total_balance;
|
|
default:
|
|
return $this->db->query("SELECT SUM(invoice_balance) AS total_balance FROM ip_invoice_amounts")->row()->total_balance;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param string $period
|
|
* @return array
|
|
*/
|
|
public function get_status_totals($period = '')
|
|
{
|
|
switch ($period) {
|
|
default:
|
|
case 'this-month':
|
|
$results = $this->db->query("
|
|
SELECT ip_invoices.invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(ip_invoice_amounts.invoice_paid) ELSE SUM(ip_invoice_amounts.invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND MONTH(ip_invoices.invoice_date_created) = MONTH(NOW())
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
case 'last-month':
|
|
$results = $this->db->query("
|
|
SELECT invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(invoice_paid) ELSE SUM(invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND MONTH(ip_invoices.invoice_date_created) = MONTH(NOW() - INTERVAL 1 MONTH)
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
case 'this-quarter':
|
|
$results = $this->db->query("
|
|
SELECT invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(ip_invoice_amounts.invoice_paid) ELSE SUM(ip_invoice_amounts.invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND QUARTER(ip_invoices.invoice_date_created) = QUARTER(NOW())
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
case 'last-quarter':
|
|
$results = $this->db->query("
|
|
SELECT invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(invoice_paid) ELSE SUM(invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND QUARTER(ip_invoices.invoice_date_created) = QUARTER(NOW() - INTERVAL 1 QUARTER)
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
case 'this-year':
|
|
$results = $this->db->query("
|
|
SELECT invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(ip_invoice_amounts.invoice_paid) ELSE SUM(ip_invoice_amounts.invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
case 'last-year':
|
|
$results = $this->db->query("
|
|
SELECT invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(invoice_paid) ELSE SUM(invoice_balance) END) AS sum_total, COUNT(*) AS num_total
|
|
FROM ip_invoice_amounts
|
|
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
|
|
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW() - INTERVAL 1 YEAR)
|
|
GROUP BY ip_invoices.invoice_status_id")->result_array();
|
|
break;
|
|
}
|
|
|
|
$return = array();
|
|
|
|
foreach ($this->mdl_invoices->statuses() as $key => $status) {
|
|
$return[$key] = array(
|
|
'invoice_status_id' => $key,
|
|
'class' => $status['class'],
|
|
'label' => $status['label'],
|
|
'href' => $status['href'],
|
|
'sum_total' => 0,
|
|
'num_total' => 0
|
|
);
|
|
}
|
|
|
|
foreach ($results as $result) {
|
|
$return[$result['invoice_status_id']] = array_merge($return[$result['invoice_status_id']], $result);
|
|
}
|
|
|
|
return $return;
|
|
}
|
|
|
|
}
|