1614 lines
81 KiB
PHP
1614 lines
81 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_Reports
|
|
*/
|
|
class Mdl_Reports extends CI_Model
|
|
{
|
|
/**
|
|
* @param null $from_date
|
|
* @param null $to_date
|
|
* @return mixed
|
|
*/
|
|
public function sales_by_client($from_date = null, $to_date = null,$a = null, $b = null, $c = null, $client = null)
|
|
{
|
|
$this->db->select('client_id, client_name, client_surname, CONCAT(client_name," ", client_surname) AS client_namesurname');
|
|
|
|
if ($from_date and $to_date and $client) {
|
|
|
|
$from_date = date_to_mysql($from_date);
|
|
$to_date = date_to_mysql($to_date);
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE invoice_date_created >=' . $this->db->escape($from_date) . '
|
|
AND invoice_date_created <= ' . $this->db->escape($to_date) . ' and client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
|
|
)');
|
|
|
|
}
|
|
|
|
else if ($from_date and $to_date) {
|
|
|
|
$from_date = date_to_mysql($from_date);
|
|
$to_date = date_to_mysql($to_date);
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND invoice_date_created >= " . $this->db->escape($from_date) . "
|
|
AND invoice_date_created <= " . $this->db->escape($to_date) . "
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE invoice_date_created >=' . $this->db->escape($from_date) . '
|
|
AND invoice_date_created <= ' . $this->db->escape($to_date) . ' and invoice_status_id != 1 and invoice_status_id != 5
|
|
)');
|
|
|
|
} else if ($a and $b and $c and $client){
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND
|
|
case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = ' . $this->db->escape($c) . ' and client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
else if ($client and $c){
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
else if ($a and $b and $client){
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND
|
|
case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
|
|
and case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
else if ($a and $b and $c){
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND
|
|
case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
|
|
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
AND monthname(invoice_date_created) = ' . $this->db->escape($c) . ' and invoice_status_id != 1 and invoice_status_id != 5
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
else if ($a and $b){
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND
|
|
case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE invoice_status_id != 1 and invoice_status_id != 5
|
|
and case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
else if($client){
|
|
// $a = date_to_mysql($a);
|
|
// $b = date_to_mysql($b);
|
|
// $c = date_to_mysql($c);
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT COUNT(*) FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND client_name = " . $this->db->escape($client) . "
|
|
|
|
) AS invoice_count");
|
|
$this->db->select("
|
|
(
|
|
select sum(item_quantity) from ip_invoice_items it
|
|
|
|
WHERE it.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND client_name = " . $this->db->escape($client) . "
|
|
|
|
)
|
|
|
|
) AS quantity");
|
|
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND client_name = " . $this->db->escape($client) . "
|
|
)
|
|
) AS sales");
|
|
|
|
$this->db->select("
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE ip_invoice_amounts.invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
|
|
AND client_name = " . $this->db->escape($client) . "
|
|
)
|
|
) AS sales_with_tax");
|
|
|
|
$this->db->where('
|
|
client_id IN
|
|
(
|
|
SELECT client_id FROM ip_invoices
|
|
WHERE client_name = ' . $this->db->escape($client) . ' and invoice_status_id != 1 and invoice_status_id != 5
|
|
|
|
)');
|
|
|
|
|
|
|
|
}
|
|
//$this->db->group_by('client_namesurname,quantity,invoice_count');
|
|
$this->db->order_by('client_namesurname');
|
|
|
|
return $this->db->get('ip_clients')->result();
|
|
}
|
|
|
|
/**
|
|
* @param null $from_date
|
|
* @param null $to_date
|
|
* @return mixed
|
|
*/
|
|
public function payment_history($from_date = null, $to_date = null)
|
|
{
|
|
$this->load->model('payments/mdl_payments');
|
|
|
|
if ($from_date and $to_date) {
|
|
$from_date = date_to_mysql($from_date);
|
|
$to_date = date_to_mysql($to_date);
|
|
|
|
$this->mdl_payments->where('payment_date >=', $from_date);
|
|
$this->mdl_payments->where('payment_date <=', $to_date);
|
|
}
|
|
|
|
return $this->mdl_payments->get()->result();
|
|
}
|
|
|
|
/**
|
|
* @return mixed
|
|
*/
|
|
// public function invoice_a()
|
|
// {
|
|
|
|
// $sql="SELECT invoice_amount_id,invoice_id,invoice_sign,invoice_item_subtotal,invoice_tax_total,invoice_total FROM kasiram9_InvoiceDEV.ip_invoice_amounts;";
|
|
// $query = $this->db->query($sql);
|
|
// return $query->result();
|
|
// print_r($query);
|
|
// }
|
|
public function invoice_aging()
|
|
{
|
|
$this->db->select('client_name, client_surname');
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_balance) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
|
|
AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 1 DAY)
|
|
AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 30 DAY)
|
|
)
|
|
) AS range_1', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_balance) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
|
|
AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 30 DAY)
|
|
AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 60 DAY)
|
|
)
|
|
) AS range_2', false);
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_balance) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
|
|
AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 60 DAY)
|
|
AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 90 DAY)
|
|
)
|
|
) AS range_3', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_balance) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
|
|
AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 90 DAY)
|
|
)
|
|
) AS range_4', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_balance) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices
|
|
WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
|
|
AND invoice_date_due <= DATE_SUB(NOW(), INTERVAL 1 DAY)
|
|
)
|
|
) AS total_balance', false);
|
|
|
|
$this->db->having('range_1 >', 0);
|
|
$this->db->or_having('range_2 >', 0);
|
|
$this->db->or_having('range_3 >', 0);
|
|
$this->db->or_having('range_4 >', 0);
|
|
$this->db->or_having('total_balance >', 0);
|
|
|
|
return $this->db->get('ip_clients')->result();
|
|
}
|
|
|
|
|
|
/**
|
|
* @param null $from_date
|
|
* @param null $to_date
|
|
* @param null $minQuantity
|
|
* @param null $maxQuantity
|
|
* @param bool $taxChecked
|
|
* @return mixed
|
|
*/
|
|
public function sales_by_year($from_date = null, $to_date = null, $minQuantity = null, $maxQuantity = null, $taxChecked = False)
|
|
{
|
|
if ($minQuantity == "") {
|
|
$minQuantity = 0;
|
|
}
|
|
|
|
if ($from_date == "") {
|
|
$from_date = date("Y-m-d");
|
|
} else {
|
|
$from_date = date_to_mysql($from_date);
|
|
}
|
|
|
|
if ($to_date == "") {
|
|
$to_date = date("Y-m-d");
|
|
} else {
|
|
$to_date = date_to_mysql($to_date);
|
|
}
|
|
|
|
$from_date_year = intval(substr($from_date, 0, 4));
|
|
$to_date_year = intval(substr($to_date, 0, 4));
|
|
|
|
$this->db->select('client_name as Name');
|
|
$this->db->select('client_name');
|
|
$this->db->select('client_surname');
|
|
$this->db->select('CONCAT(client_name," ", client_surname) AS client_namesurname');
|
|
|
|
if ($taxChecked == false) {
|
|
|
|
if ($maxQuantity) {
|
|
|
|
$this->db->select('client_id');
|
|
$this->db->select('client_vat_id AS VAT_ID');
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
)
|
|
) AS total_payment', false);
|
|
|
|
for ($index = $from_date_year; $index <= $to_date_year; $index++) {
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-01-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
|
|
)
|
|
)
|
|
) AS payment_t1_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-04-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
|
|
)
|
|
)
|
|
) AS payment_t2_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-07-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
|
|
)
|
|
)
|
|
) AS payment_t3_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-10-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
|
|
)
|
|
)
|
|
) AS payment_t4_' . $index . '', false);
|
|
|
|
}
|
|
|
|
$this->db->where('
|
|
(
|
|
SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created
|
|
AND ' . $minQuantity . ' <=
|
|
(
|
|
SELECT SUM(amounts2.invoice_item_subtotal) FROM ip_invoice_amounts amounts2
|
|
WHERE amounts2.invoice_id IN
|
|
(
|
|
SELECT inv2.invoice_id FROM ip_invoices inv2
|
|
WHERE inv2.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
|
|
)
|
|
) AND ' . $maxQuantity . ' >=
|
|
(
|
|
SELECT SUM(amounts3.invoice_item_subtotal) FROM ip_invoice_amounts amounts3
|
|
WHERE amounts3.invoice_id IN
|
|
(
|
|
SELECT inv3.invoice_id FROM ip_invoices inv3
|
|
WHERE inv3.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv3.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv3.invoice_date_created
|
|
)
|
|
)
|
|
)
|
|
) <>0');
|
|
|
|
} else {
|
|
|
|
$this->db->select('client_id');
|
|
$this->db->select('client_vat_id AS VAT_ID');
|
|
$this->db->select('client_name as Name');
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
)
|
|
) AS total_payment', false);
|
|
|
|
for ($index = $from_date_year; $index <= $to_date_year; $index++) {
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-01-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
|
|
)
|
|
)
|
|
) AS payment_t1_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-04-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
|
|
)
|
|
)
|
|
) AS payment_t2_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-07-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
|
|
)
|
|
)
|
|
) AS payment_t3_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-10-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
|
|
)
|
|
)
|
|
) AS payment_t4_' . $index . '', false);
|
|
|
|
}
|
|
|
|
$this->db->where('
|
|
(
|
|
SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created
|
|
AND ' . $minQuantity . ' <=
|
|
(
|
|
SELECT SUM(amounts2.invoice_item_subtotal) FROM ip_invoice_amounts amounts2
|
|
WHERE amounts2.invoice_id IN
|
|
(
|
|
SELECT inv2.invoice_id FROM ip_invoices inv2
|
|
WHERE inv2.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
|
|
)
|
|
)
|
|
)
|
|
) <>0');
|
|
|
|
}
|
|
|
|
} else if ($taxChecked == true) {
|
|
|
|
if ($maxQuantity) {
|
|
|
|
$this->db->select('client_id');
|
|
$this->db->select('client_vat_id AS VAT_ID');
|
|
$this->db->select('client_name as Name');
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
)
|
|
) AS total_payment', false);
|
|
|
|
for ($index = $from_date_year; $index <= $to_date_year; $index++) {
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-01-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
|
|
)
|
|
)
|
|
) AS payment_t1_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-04-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
|
|
)
|
|
)
|
|
) AS payment_t2_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-07-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
|
|
)
|
|
)
|
|
) AS payment_t3_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-10-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
|
|
)
|
|
)
|
|
) AS payment_t4_' . $index . '', false);
|
|
|
|
}
|
|
|
|
$this->db->where('
|
|
(
|
|
SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created
|
|
AND ' . $minQuantity . ' <=
|
|
(
|
|
SELECT SUM(amounts2.invoice_total) FROM ip_invoice_amounts amounts2
|
|
WHERE amounts2.invoice_id IN
|
|
(
|
|
SELECT inv2.invoice_id FROM ip_invoices inv2
|
|
WHERE inv2.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
|
|
)
|
|
) AND ' . $maxQuantity . ' >=
|
|
(
|
|
SELECT SUM(amounts3.invoice_total) FROM ip_invoice_amounts amounts3
|
|
WHERE amounts3.invoice_id IN
|
|
(
|
|
SELECT inv3.invoice_id FROM ip_invoices inv3
|
|
WHERE inv3.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv3.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv3.invoice_date_created
|
|
)
|
|
)
|
|
)
|
|
) <>0');
|
|
|
|
} else {
|
|
|
|
$this->db->select('client_id');
|
|
$this->db->select('client_vat_id AS VAT_ID');
|
|
$this->db->select('client_name as Name');
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
)
|
|
) AS total_payment', false);
|
|
|
|
for ($index = $from_date_year; $index <= $to_date_year; $index++) {
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-01-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
|
|
)
|
|
)
|
|
) AS payment_t1_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-04-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
|
|
)
|
|
)
|
|
) AS payment_t2_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-07-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
|
|
)
|
|
)
|
|
) AS payment_t3_' . $index . '', false);
|
|
|
|
$this->db->select('
|
|
(
|
|
SELECT SUM(invoice_total) FROM ip_invoice_amounts
|
|
WHERE invoice_id IN
|
|
(
|
|
SELECT invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
|
|
AND
|
|
(
|
|
inv.invoice_date_created LIKE \'%' . $index . '-10-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\'
|
|
OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
|
|
)
|
|
)
|
|
) AS payment_t4_' . $index . '', false);
|
|
|
|
}
|
|
|
|
$this->db->where('
|
|
(
|
|
SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts
|
|
WHERE amounts.invoice_id IN
|
|
(
|
|
SELECT inv.invoice_id FROM ip_invoices inv
|
|
WHERE inv.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created
|
|
AND ' . $minQuantity . ' <=
|
|
(
|
|
SELECT SUM(amounts2.invoice_total) FROM ip_invoice_amounts amounts2
|
|
WHERE amounts2.invoice_id IN
|
|
(
|
|
SELECT inv2.invoice_id FROM ip_invoices inv2
|
|
WHERE inv2.client_id=ip_clients.client_id
|
|
AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created
|
|
AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
|
|
)
|
|
)
|
|
)
|
|
) <>0');
|
|
|
|
}
|
|
|
|
}
|
|
|
|
$this->db->order_by('client_namesurname');
|
|
return $this->db->get('ip_clients')->result();
|
|
}
|
|
public function cumulative_report()
|
|
{
|
|
if (date('m') >= 4) {
|
|
$yearl = date('Y').'-'.(date('Y')+1);
|
|
} else {
|
|
$yearl = (date('Y')-1).'-'.date('Y');
|
|
}
|
|
$ab=$yearl;
|
|
$fa=substr($ab,0,-5);
|
|
$aa=substr($ab,5,5);
|
|
//echo $aa;
|
|
$sql="select distinct Year.client_id,Year.client_name,Year.product_id,Year.product_description,Year.invoice_count,Year.Qty,Year.Value,Month.Qty as MQty,Month.Value as MValue,Today.Qty as TQty,Today.Value as TValue from
|
|
(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where (invoice.invoice_date_created >= '".$fa."-04-01' and invoice.invoice_date_created <= '".$aa."-03-31') and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
|
|
group by client_id,pro.product_id) as Year left join
|
|
|
|
(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where month(invoice.invoice_date_created)=month(CURRENT_DATE()) and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
|
|
group by client_id,pro.product_id) as Month on Year.client_name = Month.client_name and Year.product_id = Month.product_id
|
|
|
|
left join
|
|
(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where invoice.invoice_date_created=CURRENT_DATE and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
|
|
group by client_id,pro.product_id) as Today on Year.client_name = Today.client_name and Year.product_id = Today.product_id
|
|
|
|
group by Year.client_id,Year.product_id;";
|
|
//echo $sql;
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
public function cumulative_report_year($clientid,$productid)
|
|
{
|
|
if (date('m') >= 4) {
|
|
$yearl = date('Y').'-'.(date('Y')+1);
|
|
} else {
|
|
$yearl = (date('Y')-1).'-'.date('Y');
|
|
}
|
|
$ab=$yearl;
|
|
$fa=substr($ab,0,-5);
|
|
$aa=substr($ab,5,5);
|
|
$sql = "SELECT invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as Qty,amt.item_subtotal as Value from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where (invoice.invoice_date_created >= '".$fa."-04-01' and invoice.invoice_date_created <= '".$aa."-03-31')
|
|
and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5 and invoice.client_id = ? and items.item_product_id = ?";
|
|
|
|
|
|
$query = $this->db->query($sql,array($clientid,$productid));
|
|
return $query->result();
|
|
|
|
|
|
}
|
|
|
|
public function cumulative_report_month($clientid,$productid)
|
|
{
|
|
$sql = "SELECT invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as MQty,amt.item_subtotal as MValue from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where month(invoice.invoice_date_created)=month(CURRENT_DATE()) and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
|
|
and invoice.client_id = ? and items.item_product_id = ?";
|
|
|
|
$query = $this->db->query($sql,array($clientid,$productid));
|
|
return $query->result();
|
|
}
|
|
|
|
public function cumulative_report_day($clientid,$productid)
|
|
{
|
|
|
|
$sql = "SELECT invoice.invoice_number as invoice_number,invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as TQty,amt.item_subtotal as TValue from ip_invoices invoice
|
|
join ip_clients client on invoice.client_id =client.client_id
|
|
left join ip_invoice_items items on invoice.invoice_id = items.invoice_id
|
|
left join ip_products pro on items.item_product_id = pro.product_id
|
|
left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
|
|
where invoice.invoice_date_created=CURRENT_DATE and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5 and
|
|
invoice.client_id = ? and items.item_product_id = ?
|
|
";
|
|
$query = $this->db->query($sql,array($clientid,$productid));
|
|
return $query->result();
|
|
|
|
}
|
|
public function sales($cust=null,$prod=null,$a=null,$b=null,$c=null,$from=null,$to=null)
|
|
{
|
|
|
|
$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal,
|
|
sum(ifnull(((sgst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100),0))
|
|
as sgst,
|
|
sum(ifnull(((iit.item_quantity * iit.item_price) * cgst.tax_rate_percent)/100,0))
|
|
as cgst,
|
|
sum(ifnull((igst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100,0))
|
|
as igst,
|
|
sum((iia.item_subtotal + ifnull(((sgst.tax_rate_percent * iia.item_subtotal)/100),0)
|
|
+
|
|
ifnull(((iia.item_subtotal * cgst.tax_rate_percent)/100),0)
|
|
+
|
|
ifnull(((igst.tax_rate_percent * iia.item_subtotal)/100),0)))
|
|
as total
|
|
from ip_invoices ii
|
|
join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
|
|
join ip_invoice_items iit on iit.invoice_id = ii.invoice_id
|
|
join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
|
|
join ip_clients ic on ic.client_id = ii.client_id
|
|
join ip_products ip on ip.product_id = iit.item_product_id
|
|
left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id
|
|
left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id
|
|
left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id
|
|
where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 and ii.invoice_status_id != 5";
|
|
|
|
|
|
if ($prod!= ''){
|
|
|
|
$sql.="and ip.product_description = '".$prod."'";
|
|
|
|
}
|
|
|
|
if ($cust!= '')
|
|
{
|
|
$sql.="and ic.client_name = '".$cust."'";
|
|
}
|
|
if ($a and $b != ''){
|
|
|
|
$sql.=" and (ii.invoice_date_created >= '".$a."-04-01' and ii.invoice_date_created <= '".$b."-03-31')";
|
|
|
|
}
|
|
|
|
if ($c!= ''){
|
|
|
|
$sql.="and monthname(ii.invoice_date_created) = '".$c."'";
|
|
|
|
}
|
|
|
|
if ($from and $to != ''){
|
|
$fromdate= date("Y-m-d",strtotime($from));
|
|
$todate=date("Y-m-d",strtotime($to));
|
|
|
|
$sql.="and ii.invoice_date_created >= '".$fromdate."'
|
|
and ii.invoice_date_created <= '".$todate."'";
|
|
|
|
}
|
|
|
|
|
|
// echo $fromdate;
|
|
// echo $sql;
|
|
$sql.="group by invoice_number,product_description";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
|
|
}
|
|
public function sales_l($cname=null,$fa=null,$aa=null,$m=null,$frm=null,$t=null,$prod=null,$datec=null,$dmon=null)
|
|
{
|
|
|
|
$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal,
|
|
sum(ifnull(((sgst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100),0))
|
|
as sgst,
|
|
sum(ifnull(((iit.item_quantity * iit.item_price) * cgst.tax_rate_percent)/100,0))
|
|
as cgst,
|
|
sum(ifnull((igst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100,0))
|
|
as igst,
|
|
sum((iia.item_subtotal + ifnull(((sgst.tax_rate_percent * iia.item_subtotal)/100),0)
|
|
+
|
|
ifnull(((iia.item_subtotal * cgst.tax_rate_percent)/100),0)
|
|
+
|
|
ifnull(((igst.tax_rate_percent * iia.item_subtotal)/100),0)))
|
|
as total
|
|
from ip_invoices ii
|
|
join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
|
|
join ip_invoice_items iit on iit.invoice_id = ii.invoice_id
|
|
join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
|
|
join ip_clients ic on ic.client_id = ii.client_id
|
|
join ip_products ip on ip.product_id = iit.item_product_id
|
|
left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id
|
|
left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id
|
|
left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id
|
|
where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 ";
|
|
|
|
|
|
|
|
if ($cname!= ''){
|
|
|
|
$sql.="and ic.client_name = '".$cname."'";
|
|
|
|
}
|
|
|
|
|
|
|
|
if ($fa and $aa != ''){
|
|
|
|
$sql.=" and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31')";
|
|
|
|
}
|
|
if ($m!= ''){
|
|
|
|
$sql.="and monthname(ii.invoice_date_created) = '".$m."'";
|
|
|
|
}
|
|
|
|
if ($frm and $t != ''){
|
|
$fromd= date("Y-m-d",strtotime($frm));
|
|
$tod=date("Y-m-d",strtotime($t));
|
|
|
|
$sql.="and ii.invoice_date_created >= '".$fromd."'
|
|
and ii.invoice_date_created <= '".$tod."'";
|
|
|
|
}
|
|
if ($prod!= ''){
|
|
|
|
$sql.=" and ip.product_description = '".$prod."' ";
|
|
|
|
}
|
|
if ($dmon!= ''){
|
|
$mon=substr($dmon,0,-5);
|
|
$mony=substr($dmon,3,4);
|
|
|
|
$sql.="and year(ii.invoice_date_created) = year(CURRENT_DATE()) and month(ii.invoice_date_created) = MONTH(CURRENT_DATE())";
|
|
|
|
}
|
|
if ($datec!= ''){
|
|
|
|
$sql.="and ii.invoice_date_created = '".$datec."'";
|
|
|
|
}
|
|
|
|
|
|
// echo $mon;
|
|
// echo $mony;
|
|
// echo $sql;
|
|
$sql.=" group by invoice_number,product_description";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
|
|
}
|
|
public function sales_dashboard()
|
|
{
|
|
|
|
$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal, sum(iia.item_sgst_amt) as sgst,sum(iia.item_cgst_amt) as cgst, sum(iia.item_igst_amt) as igst,
|
|
sum(ifnull(tcs.tax_rate_percent * iia.item_total/100,0))
|
|
as tcs,
|
|
|
|
|
|
sum(ifnull(tcs.tax_rate_percent * iia.item_total/100,0))
|
|
+sum(iia.item_total)
|
|
as total
|
|
from ip_invoices ii
|
|
left join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
|
|
|
|
left join ip_invoice_tax_rates tc on tc.invoice_id = ii.invoice_id
|
|
|
|
left join ip_invoice_items iit on iit.invoice_id = ii.invoice_id
|
|
left join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
|
|
left join ip_clients ic on ic.client_id = ii.client_id
|
|
left join ip_products ip on ip.product_id = iit.item_product_id
|
|
left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id
|
|
left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id
|
|
left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id
|
|
left join ip_tax_rates tcs on tcs.tax_rate_id = tc.tax_rate_id
|
|
where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 and ii.invoice_date_created = CURRENT_DATE";
|
|
$sql.=" group by invoice_number,product_description";
|
|
$query = $this->db->query($sql);
|
|
// print_r($this->db->last_query());
|
|
return $query->result();
|
|
|
|
|
|
}
|
|
public function consolidate_sales($fa,$aa,$cname=null)
|
|
{
|
|
|
|
$sql = "select ic.client_name as client_name,ip.product_description as product_description,
|
|
SUM(IF(month(ii.invoice_date_created) = 4, iit.item_quantity, 0)) AS April,
|
|
SUM(IF(month(ii.invoice_date_created) = 5, iit.item_quantity, 0)) AS May,
|
|
SUM(IF(month(ii.invoice_date_created) = 6, iit.item_quantity, 0)) AS June,
|
|
SUM(IF(month(ii.invoice_date_created) = 7, iit.item_quantity, 0)) AS July,
|
|
SUM(IF(month(ii.invoice_date_created) = 8, iit.item_quantity, 0)) AS August,
|
|
SUM(IF(month(ii.invoice_date_created) = 9, iit.item_quantity, 0)) AS September,
|
|
SUM(IF(month(ii.invoice_date_created) = 10, iit.item_quantity, 0)) AS October,
|
|
SUM(IF(month(ii.invoice_date_created) = 11, iit.item_quantity, 0)) AS November,
|
|
SUM(IF(month(ii.invoice_date_created) = 12, iit.item_quantity, 0)) AS December,
|
|
SUM(IF(month(ii.invoice_date_created) = 1, iit.item_quantity, 0)) AS January,
|
|
SUM(IF(month(ii.invoice_date_created) = 2, iit.item_quantity, 0)) AS February,
|
|
SUM(IF(month(ii.invoice_date_created) = 3, iit.item_quantity, 0)) AS March,
|
|
sum(iit.item_quantity) as qtotal,
|
|
SUM(IF(month(ii.invoice_date_created) = 4, iit.item_quantity * iit.item_price, 0)) AS vApril,
|
|
SUM(IF(month(ii.invoice_date_created) = 5, iit.item_quantity * iit.item_price, 0)) AS vMay,
|
|
SUM(IF(month(ii.invoice_date_created) = 6, iit.item_quantity * iit.item_price, 0)) AS vJune,
|
|
SUM(IF(month(ii.invoice_date_created) = 7, iit.item_quantity * iit.item_price, 0)) AS vJuly,
|
|
SUM(IF(month(ii.invoice_date_created) = 8, iit.item_quantity * iit.item_price, 0)) AS vAugust,
|
|
SUM(IF(month(ii.invoice_date_created) = 9, iit.item_quantity * iit.item_price, 0)) AS vSeptember,
|
|
SUM(IF(month(ii.invoice_date_created) = 10, iit.item_quantity * iit.item_price, 0)) AS vOctober,
|
|
SUM(IF(month(ii.invoice_date_created) = 11, iit.item_quantity * iit.item_price, 0)) AS vNovember,
|
|
SUM(IF(month(ii.invoice_date_created) = 12, iit.item_quantity * iit.item_price, 0)) AS vDecember,
|
|
SUM(IF(month(ii.invoice_date_created) = 1, iit.item_quantity * iit.item_price, 0)) AS vJanuary,
|
|
SUM(IF(month(ii.invoice_date_created) = 2, iit.item_quantity * iit.item_price, 0)) AS vFebruary,
|
|
SUM(IF(month(ii.invoice_date_created) = 3, iit.item_quantity * iit.item_price, 0)) AS vMarch,
|
|
sum(iit.item_quantity * iit.item_price) as vtotal
|
|
from ip_invoices ii
|
|
join ip_invoice_items iit on iit.invoice_id = ii.invoice_id
|
|
join ip_clients ic on ic.client_id = ii.client_id
|
|
join ip_products ip on ip.product_id = iit.item_product_id
|
|
where ii.invoice_status_id != 1 and ii.invoice_status_id != 5";
|
|
|
|
if ($fa and $aa != ''){
|
|
|
|
$sql.=" and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31')";
|
|
|
|
}
|
|
if ($cname!= ''){
|
|
|
|
$sql.="and ic.client_name = '".$cname."'";
|
|
|
|
}
|
|
$sql.=" group by client_name,product_description";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
|
|
}
|
|
|
|
}
|