public function getData(array $inputs)
{
$quantity_cond = '';
if ($inputs['sale_type'] == 'sales') {
$quantity_cond = 'and quantity_purchased > 0';
} elseif ($inputs['sale_type'] == 'returns') {
$quantity_cond = 'and quantity_purchased < 0';
}
if ($inputs['location_id'] != 'all') {
$quantity_cond .= 'and item_location = ' . $this->db->escape($inputs['location_id']);
}
if ($this->config->item('tax_included')) {
$total = "1";
$subtotal = "(100/(100+percent))";
$tax = "(1 - (100/(100 +percent)))";
} else {
$tax = "(percent/100)";
$total = "(1+(percent/100))";
$subtotal = "1";
}
$decimals = totals_decimals();
$query = $this->db->query("SELECT percent, count(*) AS count, SUM(subtotal) AS subtotal, SUM(total) AS total, SUM(tax) AS tax\n\t\t\tFROM (SELECT name, CONCAT(ROUND(percent, {$decimals}), '%') AS percent,\n\t\t\tROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent /100) * {$subtotal}, {$decimals}) AS subtotal,\n\t\t\tROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent /100) * {$total}, {$decimals}) AS total,\n\t\t\tROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent /100) * {$tax}, {$decimals}) AS tax\n\t\t\tFROM " . $this->db->dbprefix('sales_items_taxes') . "\n\t\t\tJOIN " . $this->db->dbprefix('sales_items') . " ON " . $this->db->dbprefix('sales_items') . '.sale_id=' . $this->db->dbprefix('sales_items_taxes') . '.sale_id' . " AND " . $this->db->dbprefix('sales_items') . '.item_id=' . $this->db->dbprefix('sales_items_taxes') . '.item_id' . " AND " . $this->db->dbprefix('sales_items') . '.line=' . $this->db->dbprefix('sales_items_taxes') . '.line' . " JOIN " . $this->db->dbprefix('sales') . " ON " . $this->db->dbprefix('sales_items_taxes') . ".sale_id=" . $this->db->dbprefix('sales') . ".sale_id\n\t\t\tWHERE date(sale_time) BETWEEN " . $this->db->escape($inputs['start_date']) . " AND " . $this->db->escape($inputs['end_date']) . " {$quantity_cond}) AS temp_taxes\n\t\t\tGROUP BY percent");
return $query->result_array();
}
public function create_temp_table()
{
if ($this->config->item('tax_included')) {
$total = '1';
$subtotal = '(1 - (SUM(1 - 100 / (100 + sales_items_taxes.percent))))';
$tax = '(SUM(1 - 100 / (100 + sales_items_taxes.percent)))';
} else {
$tax = '(SUM(sales_items_taxes.percent) / 100)';
$total = '(1 + (SUM(sales_items_taxes.percent / 100)))';
$subtotal = '1';
}
$sale_total = '(sales_items.item_unit_price * sales_items.quantity_purchased - sales_items.item_unit_price * sales_items.quantity_purchased * sales_items.discount_percent / 100)';
$sale_cost = '(sales_items.item_cost_price * sales_items.quantity_purchased)';
$decimals = totals_decimals();
$this->db->query('CREATE TEMPORARY TABLE IF NOT EXISTS ' . $this->db->dbprefix('sales_items_temp') . '(
SELECT
DATE(sales.sale_time) AS sale_date,
sales.sale_time,
sales.sale_id,
sales.comment,
sales.invoice_number,
sales.customer_id,
CONCAT(customer_p.first_name, " ", customer_p.last_name) AS customer_name,
customer_p.first_name AS customer_first_name,
customer_p.last_name AS customer_last_name,
customer_p.email AS customer_email,
customer_p.comments AS customer_comments,
customer.company_name AS customer_company_name,
sales.employee_id,
CONCAT(employee.first_name, " ", employee.last_name) AS employee_name,
items.item_id,
items.name,
items.category,
items.supplier_id,
sales_items.quantity_purchased,
sales_items.item_cost_price,
sales_items.item_unit_price,
sales_items.discount_percent,
sales_items.line,
sales_items.serialnumber,
sales_items.item_location,
sales_items.description,
payments.payment_type,
IFNULL(payments.sale_payment_amount, 0) AS sale_payment_amount,
SUM(sales_items_taxes.percent) AS item_tax_percent,
' . "\n\t\t\t\t\tROUND({$sale_total} * {$total}, {$decimals}) AS total,\n\t\t\t\t\tROUND({$sale_total} * {$tax}, {$decimals}) AS tax,\n\t\t\t\t\tROUND({$sale_total} * {$subtotal}, {$decimals}) AS subtotal,\n\t\t\t\t\tROUND({$sale_total} - {$sale_cost}, {$decimals}) AS profit,\n\t\t\t\t\tROUND({$sale_cost}, {$decimals}) AS cost\n\t\t\t\t\t" . '
FROM ' . $this->db->dbprefix('sales_items') . ' AS sales_items
INNER JOIN ' . $this->db->dbprefix('sales') . ' AS sales
ON sales_items.sale_id = sales.sale_id
INNER JOIN ' . $this->db->dbprefix('items') . ' AS items
ON sales_items.item_id = items.item_id
LEFT OUTER JOIN (
SELECT sale_id,
SUM(payment_amount) AS sale_payment_amount,
GROUP_CONCAT(CONCAT(payment_type, " ", payment_amount) SEPARATOR ", ") AS payment_type
FROM ' . $this->db->dbprefix('sales_payments') . '
GROUP BY sale_id
) AS payments
ON sales_items.sale_id = payments.sale_id
LEFT OUTER JOIN ' . $this->db->dbprefix('suppliers') . ' AS supplier
ON items.supplier_id = supplier.person_id
LEFT OUTER JOIN ' . $this->db->dbprefix('people') . ' AS customer_p
ON sales.customer_id = customer_p.person_id
LEFT OUTER JOIN ' . $this->db->dbprefix('customers') . ' AS customer
ON sales.customer_id = customer.person_id
LEFT OUTER JOIN ' . $this->db->dbprefix('people') . ' AS employee
ON sales.employee_id = employee.person_id
LEFT OUTER JOIN ' . $this->db->dbprefix('sales_items_taxes') . ' AS sales_items_taxes
ON sales_items.sale_id = sales_items_taxes.sale_id AND sales_items.item_id = sales_items_taxes.item_id AND sales_items.line = sales_items_taxes.line
GROUP BY sales.sale_id, items.item_id, sales_items.line
)');
//Update null item_tax_percents to be 0 instead of null
$this->db->where('item_tax_percent IS NULL');
$this->db->update('sales_items_temp', array('item_tax_percent' => 0));
//Update null tax to be 0 instead of null
$this->db->where('tax IS NULL');
$this->db->update('sales_items_temp', array('tax' => 0));
//Update null subtotals to be equal to the total as these don't have tax
$this->db->query('UPDATE ' . $this->db->dbprefix('sales_items_temp') . ' SET total = subtotal WHERE total IS NULL');
}