Propduct calculation total based on order status and catoegories

[marcopolo]

Trying to calculate my production of a certain product based on orders in processing.

So for example need to know out of all the orders in processing that are from a certain category what is the total for that product. The attributes can be different for so for example:

Product A
12 count

Product B
40 count

If a customer buys 1 of product A and 1 of Product B the total count would be 52


I believe the data would be pulled from:

Check orders_id from TABLE orders with orders_status of xx
Then TABLE orders_products with above orders_id then gather all products_id
Then TABLE products_to_categories needs to be referenced and if the products_id above are from categories_id xx,xx,xx
Then take those products_id with orders_id and get the orders_products_id
The reference TABLE orders_products_attributes and orders_products_id with products_options_values_id xx mutiple qty order by xx (12 count example) Then do it again for the 40 count

So for example products_options_values_id 79 is for the 12 count and products_options_values_id 83 is for the 40 count

I'm not fully sure that covers it all but if anyone would like to take a shot at turning that into code I can try that would be much appreciated.

[lat9]

I believe that the following query will do what you want. Change the xx to the orders-status value and xx, xx, xx to the list of categories you're looking for. The query will return the sum of the products ordered from orders with status of xx, where the products are in categories xx, xx, xx and where the product has an attribute with value-id of 79.

SELECT SUM(op.products_quantity)
  FROM orders_products op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = xx
        INNER JOIN products_to_categories p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (xx, xx, xx)
        INNER JOIN orders_products_attributes opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 79;

[swguy]

Take a look at the Sales Report; you might be able to get the data you want from it directly.

https://www.zen-cart.com/downloads.php?do=file&id=9

[mc12345678]

Neither one of those specifically identifies the "real quantity" at hand where purchasing one count of Product A is actually 12 items and one count of Product B is actually 40 items...

If not mistaken though this is the same quest as otherwise posted to determine the actual quantity on hand or sold of product based on attribute(s) selected...

[marcopolo]

I believe that the following query will do what you want. Change the xx to the orders-status value and xx, xx, xx to the list of categories you're looking for. The query will return the sum of the products ordered from orders with status of xx, where the products are in categories xx, xx, xx and where the product has an attribute with value-id of 79.

SELECT SUM(op.products_quantity)
  FROM orders_products op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = xx
        INNER JOIN products_to_categories p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (xx, xx, xx)
        INNER JOIN orders_products_attributes opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 79;

Thank you lat9 the query works if I run it directly from MySQL. I was trying to get it to work from the admin changed the code to:

$test_total = $db->Execute('SELECT SUM(op.products_quantity) * 12
      FROM orders_products op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = 2
        INNER JOIN products_to_categories p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (81, 82)
        INNER JOIN orders_products_attributes opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 79'); 

But it's giving me the following error:

PHP Catchable fatal error: Object of class queryFactoryResult could not be converted to string

I'm still playing with it but really not sure that the issue is, if it works from MySQL it should be working this way as well right?

I also tried writing it this way but gives same error:

$test_total2 = $db->Execute('SELECT SUM(op.products_quantity) * 40
      FROM ' . TABLE_ORDERS_PRODUCTS . ' op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = 2
        INNER JOIN ' . TABLE_PRODUCTS_TO_CATEGORIES . ' p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (81, 82)
        INNER JOIN ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 83'); 

[DrByte]

https://docs.zen-cart.com/dev/code/database_querying/

[marcopolo]

https://docs.zen-cart.com/dev/code/database_querying/

Thank you DrByte. Following the instructions on that page I changed the code to:

$test_sql  = "SELECT SUM(op.products_quantity) * 12
      FROM orders_products op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = 2
        INNER JOIN products_to_categories p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (81, 82)
        INNER JOIN orders_products_attributes opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 79";

global $db;
$test_sql = $db->bindVars($test_sql, ':lang_id', $_SESSION['languages_id'], 'integer');
$test_total = $db->Execute($test_sql); 

Also tried this way:

$test_sql2 = "SELECT SUM(op.products_quantity) * 40
      FROM " . TABLE_ORDERS_PRODUCTS . " op
        INNER JOIN orders o
            ON o.orders_id = op.orders_id
           AND o.orders_status = 2
        INNER JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
            ON p2c.products_id = op.products_id
           AND p2c.categories_id IN (81, 82)
        INNER JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa
            ON opa.orders_products_id = op.orders_products_id
           AND opa.products_options_values_id = 83";

global $db;
$test_sql2 = $db->bindVars($test_sql2, ':lang_id', $_SESSION['languages_id'], 'integer');
$test_total2 = $db->Execute($test_sql2); 

Same error:
Catchable fatal error: Object of class queryFactoryResult could not be converted to string

Is the code wrong?

[DrByte]

You wrote:

$test_total=$db->Execute($test_sql);

I think you're not posting the line that's actually triggering the Catchable error.

You're probably doing something else with $test_total afterward, which is causing the error.
You can't "echo" it to the screen, because it's not a string or printable value.

Since you're SELECTing values, it is returning an object of database results. You can iterate over it using a foreach() loop, as per the doc I mentioned.

[marcopolo]

You wrote:

$test_total=$db->Execute($test_sql);

I think you're not posting the line that's actually triggering the Catchable error.

You're probably doing something else with $test_total afterward, which is causing the error.
You can't "echo" it to the screen, because it's not a string or printable value.

Since you're SELECTing values, it is returning an object of database results. You can iterate over it using a foreach() loop, as per the doc I mentioned.

This is how I'm calling it:

<div class="header">Production Info</div>
<?php
    echo '<div class="row"><span class="left">FRESH CUPS</span><span class="rigth"> ' . $test_total2 . '</span></div>';
?>
  </div>

[carlwhat]

This is how I'm calling it:

<div class="header">Production Info</div>
<?php
    echo '<div class="row"><span class="left">FRESH CUPS</span><span class="rigth"> ' . $test_total2 . '</span></div>';
?>
  </div>

you can not echo $test_total2. that is your error.

you can print_r($test_total2)...

if as you suspect the record count is one you can echo $test_total2->fields.... wait a minute.... if you want to do a * 40 in your sql, i would add a 'AS FIELDNAME'. and you can then echo $test_total2->fields['FIELDNAME'].

i hope that gives you some ideas as to where you have gone adrift...

also, as DrB mentioned above, you can do a foreach loop as well...

best.