How to write a piece of code for a product id total?

[marcopolo]

Running a fundraiser and would like to display total funds collected. We will be selling a product for example $22 dollars which $12 dollars of the sale will be donated. I created a side banner with our goal amount we are trying to achieve but will need to populate the amount collect for it to display the graph.

So for reference the product id is 1200, I will need to retrieve total sales based on a certain date range for that product id and minus $10 for each qty unit sold then out out that to a number.

Have no idea how to write that piece of code correctly, if anyone can help I would great appreciate it. Thanks in advance for any help!

[marcopolo]

Function just needs to check qty sold of a single item in this case product_id 1200 then multiple qty sold by $12. Help anybody?

[mc12345678]

Running a fundraiser and would like to display total funds collected. We will be selling a product for example $22 dollars which $12 dollars of the sale will be donated. I created a side banner with our goal amount we are trying to achieve but will need to populate the amount collect for it to display the graph.

So for reference the product id is 1200, I will need to retrieve total sales based on a certain date range for that product id and minus $10 for each qty unit sold then out out that to a number.

Have no idea how to write that piece of code correctly, if anyone can help I would great appreciate it. Thanks in advance for any help!

Total funds collected = quantity of products_id sold within a date range * (cost of products_id in the then current currency - 10 (in applicable current currency)) displayed in the current currency.

Quantity of products_id sold:

$start_datetime = '2020-03-20 05:00:00";
$end_datetime = date(DATE_TIME_FORMAT, time()); // Is an example of checking up until now although that portion could be left off if a running total was wanted and just need to return all values greater than the start date. Else, put in the end date as desired.
$quantity_sold_query = "SELECT op.products_quantity, op.products_price, op.products_tax, op.final_price, o.currency, o.currency_value FROM " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o WHERE op.products_id = :products_id: AND op.orders_id = o.orders_id AND o.date_purchased > STR_TO_DATE(:start_datetime:, :datetimeformat:) AND o.date_purchased < STR_TO_DATE(:end_datetime:, :datetimeformat:) ORDER BY o.date_purchased ASC"; 
// ORDER BY ASC provides list with oldest purchase (by date) at the top and newest at the bottom. 

$quantity_sold_query = $db->bindVars($quantity_sold_query, ":products_id:", 1200, 'integer');
$quantity_sold_query = $db->bindVars($quantity_sold_query, ":start_datetime:", $start_datetime, 'string');
$quantity_sold_query = $db->bindVars($quantity_sold_query, ":end_datetime:", $end_datetime, 'string');
$quantity_sold_query = $db->bindVars($quantity_sold_query, ":datetimeformat:", DATE_TIME_FORMAT, 'string');

$quantity_sold = $db->Execute($quantity_sold_query);

//Since ZC version is not identified, a generic loop is offered instead of the options available as of ZC 1.5.5 where one can 
//  loop on foreach instead of while (!$quantity_sold->EOF)

$money = 0;
$subtract_amount = 10;
while (!quantity_sold->EOF) {
  $amount_sale = $currencies->value($quantity_sold->fields['final_price'] * $quantity_sold->fields['products_quantity'], true, $quantity_sold->fields['currency'], $quantity_sold->fields['currency_value']);
  $amount_deducted = $subtract_amount * $quantity_sold->fields['products_quantity'];
  $amount_sale -= $currencies->value($amount_deducted, true, DEFAULT_CURRENCY);
  // If item was free or some other characteristic caused it to be free, then do not remove more from the funds collected.
  if ($amount_sale < 0) {
    $amount_sale = 0;
  }
  $money += $amount_sale;
}

echo $currencies->format($money);

Ok, so the above is untested and is using code that looks like it is compatible with Zen Cart 1.5.6. I have not gone back to validate when fields such as o.currency and o.currency_value began being stored to possibly support the above conversions and I'm not even sure if multiple currencies are an issue, but I wanted to try to provide as complete of a solution as I could...

Really, there may be errors including issues with currency conversion and for those I apologize, check your logs folder after trying to incorporate the above wherever it is to be added and has been viewed. Also pull out your calculator if multiple currencies are involved. I really have not given this the thought it may need to ensure that the calculations come out correctly, but I had seen this posted a while ago and wanted to at least offer some assistance.

I haven't really looked to see what type of currency data/relationship is stored to ensure that the code is properly converting from whatever is stored to what is being displayed... It may differ between ZC versions as well, but the above should provide an idea of an approach that would be expected.

I tried to also make it PHP 7.x compliant (non-complaining) by ensuring all variables were declared to an appropriate datatype/value before use.

[marcopolo]

Thank you! I will test later this evening and report back. Thanks again really appreciate it!

[marcopolo]

Ok trying to get this to work but it's causing the side box to not display. I'm thinking it's because I have to create a function file maybe? I do not know where to put the code

Here is some more info as I looked over the code I may have not been clear in stating what the output needs to be. Below is the part of the goal tracker which needs to be populated with the amount NOT in currency format but just as a number.

<script type="text/javascript">
   var currentAmount = 1367;
  </script> 

I'm running this on Zencart 1.5.5f

[mc12345678]

Ok trying to get this to work but it's causing the side box to not display. I'm thinking it's because I have to create a function file maybe? I do not know where to put the code

Here is some more info as I looked over the code I may have not been clear in stating what the output needs to be. Below is the part of the goal tracker which needs to be populated with the amount NOT in currency format but just as a number.

<script type="text/javascript">
   var currentAmount = 1367;
  </script> 

I'm running this on Zencart 1.5.5f

As I said, check the logs directory. As for changing the "output", just would echo $money instead of the format function.

There are all sorts of possible places to put the code, it has to first be above/before the code is executed to display the value. It is also suggested that it only be executed when it is intended to be used and not just on every page load. Having it encapsulated within an if statement would help prevent unnecessary processing, though the if statement needs to be "smrt" as it relates to when/where the associated code is executed.
I'll try to take a look at ZC table structure for a ZC 1.5.5 site to see if the fields included in the query all exist in the database. But the issue of content missing: effectively a partial blank screen can be addressed through this FAQ (if it still exists.): http://www.zen-cart.com/content.php?124-blank-page

[marcopolo]

Thinking about this more I see a better way of doing it. We created a product for this fundraiser and the entered a stock qty of 1000. I see Zen cart has a function that can check stock. How can the following be written:

Check current stock of products_id that = 1200
then subtract that output from 1000
Then multiple that output by 12
Then populate var currentAmount below with the number:

<script type="text/javascript">
var currentAmount = 1367;
</script>

[mc12345678]

Thinking about this more I see a better way of doing it. We created a product for this fundraiser and the entered a stock qty of 1000. I see Zen cart has a function that can check stock. How can the following be written:

Check current stock of products_id that = 1200
then subtract that output from 1000
Then multiple that output by 12
Then populate var currentAmount below with the number:

<script type="text/javascript">
var currentAmount = 1367;
</script>

Disagree that this is "better" it would provide data based off a "setting" rather than actual totals. If you wanted to provide an amount times a quantity, the same is still possible in the previous arrangement. Besides that, since no information about the issue encountered has been provided, it is likely that "the problem" would continue, the results would be less than optimal, and with additional aggravation the push to correct the issue will recede...

[marcopolo]

Ok I was able to add your code to the module yesterday without and errors being generated but it is returning 0.00 does not seem to work that is why I suggested going the stock route. It will be a one time fundraiser and since we are tracking stock would in my view be a simple way to test it out.

[marcopolo]

Is it possible for you to do the code for the stock way, I'm stuck like I said it's returning 0.00 so it's not working or giving me any errors to see why.

The fundraiser has started and I would like to have it display the amount. Below is the way I think it would work:

Check current stock of products_id that = 1200
then subtract that output from 1000
Then multiple that output by 12
Then populate var currentAmount below with the number:

<script type="text/javascript">
var currentAmount = 1367;
</script>