CODEWITHSUNDEEP
mysqlopencart
rusly
rusly

Reputation: 1524

How to calculate price value and tax value in query

i try to filter product price when customer insert minimun value and maximum value.

product.php

public function getProducts($data = array()) {
....

if (!empty($data['min'])) {
    $sql .= " AND p.price BETWEEN '" .$data['min']. "' AND 9999 ";
}

with this code, category page will list out all products from price x until 9999.

The problem is some product has tax and the value is store in another table(oc_tax_rate). So how to get final price of product in query ? (p.price + tax) , something like this :

if (!empty($data['min'])) {
    $sql .= " AND final_price BETWEEN '" .$data['min']. "' AND 9999 ";
}

as i check page controller/category.php

$this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax'))

above code will return final price.

OC : v1.5.6

Upvotes: 1

Views: 1333

Answers (1)

shadyyx
shadyyx

Reputation: 16055

First of all, when You use a min and max filter values, Your query should not be choosing between min and 9999 and between 0 and max but You should do this:

if (!empty($data['min'])) {
    $sql .= " AND p.price >= " . (int)$data['min'];
//                               ^^^^^ - against SQL injection!!!
}

and 

if (!empty($data['max'])) {
    $sql .= " AND p.price <= " . (int)$data['max'];
//                               ^^^^^ - against SQL injection!!!
}

Also, as mentioned in the comment, do not forget to cure Your code against SQL injection!

Now, to Your real question: It is not possible to get the taxes and calculate taxed price along with products getting in one query. But You could do this in Your model - find this part of code

foreach ($query->rows as $result) {
    $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
}

and we will modify it this way:

foreach ($query->rows as $result) {
    if (!empty($data['min']) 
            && $this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax')) >= $data['min']) {
        $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
    } else if (!empty($data['max']) 
            && $this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax')) <= $data['max']) {
        $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
    } else {
        $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
    }
}

Upvotes: 1

Related Questions