示例#1
0
 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();
 }
示例#2
0
    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');
    }