external script needs to find price paid for each item in an order

[crunch]

When an order is made, is the actual price paid for each product recorded in the database anywhere in the order detail? I could not find this in PS 1.4.3.

 

I have an external script which does something with orders - transfers a CSV list of recent ones to another site at regular intervals. (The script is external because I run it via cron - otherwise I guess I could just use a pre-existing module to export orders).

 

Between 1.3.3 and 1.4.3 (just upgraded) there seems to be a difference in the way prices are recorded in the database for order details in regards to the price of the product and the actual price paid in the order.

I can't see anywhere in the 'order_detail' database table the *actual* price paid for a particular product. In other words, it looks like some function still has to calculate this based on discounts and taxes etc. Maybe I'm wrong about that - I'd be happy to be wrong!

For instance, in PS 1.4.3, i have an item for sale for $10, and have added a discount on this item of $11 ( which is the price + tax ie effectively making the item $0, free). The order detail still shows the item up as being $10. In my previous version of PS, the recorded price would be $0.00 ie the discounts are applied and it is the actual paid price.

 

But this is where my problem is.

 

How can I calculate the actual amount paid for each product in an order, via an external script that is querying the database?

 

If I wrote a new script to do this, is there a logical process to follow in how I would gather these values?

 

The output file I have to create (CSV) is quite specific. I'm looking for an overview of how to get the correct order details I'm looking for.

 

Sorry if this question is not clear enough or I am asking for a complex answer - I'm trying to get to grips with the core in ways I have not had to previously.

 

Thanks

 

 

Using Prestashop 1.4.3

[crunch]

gee, thanks for all the replies!

 

I figured it out by digging into the order history page as it displays these details about an order. So most of the code could be found in the 'Order' class. I've adapted it into a small script below, that can grab orders details based on an array of order id's supplied. In my case, I was logging the details to a CSV file on a periodic basis, and then marking the orders are 'loggedToCSV' - which was a custom column I created in the orders table in the database. This way, each time the script is called, it only processed orders that had not previously been processed.

 

This script grabs the necessary information, what you do with it is up to you. My script was quite specific - formatting it for entry into another system, so its not appropriate to have that here. Hence, I give you an adaptable script for use when working with orders. Hope its of use to someone!

 

<?php
/* note: you must change this to the path for your own config file */
require_once('/path/to/your/config/config.inc.php');

/* fetching the orders */

/*	please note: 
additional db column 'loggedToCSV' in order table that has been created 
to log whether this order has been processed by this script or not 
You should adapt this to your own needs - it exists here to grab an array of order ids     	
*/

$orders = array(); // holds the ids of all orders we are concerned with
$sql  = "SELECT id_order FROM "._DB_PREFIX_."orders WHERE loggedToCSV = 0 AND valid = 1 ORDER BY id_order;";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
	$orders[] = $row['id_order']; 
}
}			


foreach( $orders AS $order_id ){

/* get the order information */
/* ------------------------- */

$order = new Order($order_id);

$id_order_state = (int)($order->getCurrentState());
$carrier = new Carrier((int)($order->id_carrier), (int)($order->id_lang));

$addressInvoice = new Address((int)($order->id_address_invoice));
$addressDelivery = new Address((int)($order->id_address_delivery));

$inv_adr_fields = AddressFormat::getOrderedAddressFields($addressInvoice->id_country);
$dlv_adr_fields = AddressFormat::getOrderedAddressFields($addressDelivery->id_country);

$invoiceAddressFormatedValues = AddressFormat::getFormattedAddressFieldsValues($addressInvoice, $inv_adr_fields);
$deliveryAddressFormatedValues = AddressFormat::getFormattedAddressFieldsValues($addressDelivery, $dlv_adr_fields);

$state = State::getNameById($addressDelivery->id_state);

if ($order->total_discounts > 0) {
 	$total_discounts = (float)($order->total_paid - $order->total_discounts));				
}

$products = $order->getProducts();

$customizedDatas = Product::getAllCustomizedDatas((int)($order->id_cart));
Product::addCustomizationPrice($products, $customizedDatas);

$customer = new Customer($order->id_customer);

/* handle each product line in the order */
/* ------------------------------------- */

foreach($products as $product) {
	// your code to process each product array
}

}

[crunch]

You can also do a dump of all the object/variables in this script to see what is held within. This is useful if you need to know how the arrays/ojbects are structured to build your own functions on top.

 

echo '<h1>ORDER id: '.$order_id.'</h1>';
echo '<pre>';
print_r($order);
echo '</pre>';				

echo '<h1>addressDelivery</h1>';
echo '<pre>';
print_r($addressDelivery);
echo '</pre>';

echo '<h1>dlv_adr_fields</h1>';
echo '<pre>';
print_r($dlv_adr_fields);
echo '</pre>';

echo '<h1>products</h1>';
echo '<pre>';
print_r($products);
echo '</pre>';

echo '<h1>customer</h1>';
echo '<pre>';
print_r($customer);
echo '</pre>';

echo '<h1>customizedDatas</h1>';
echo '<pre>';
print_r($customizedDatas);
echo '</pre>';

echo '<h2>State: '.$state.'</h2>';