[Erp5-dev] Delivery getTotalPrice and getTotalQuantity

[Alexandre Boeglin]

Dear all,

Please do not update ERP5/Document/Delivery to version 2.28 for now.

I'll post what you need for it to work in some minutes.

Regards,
Alex

[Alexandre Boeglin]

Post by Alexandre Boeglin
Please do not update ERP5/Document/Delivery to version 2.28 for now.

Okay, for the Delivery methods to work with buildSQLQuery, you'll need
to modify this :

- in portal_skins/erp5_trade, replace Delivery_zGetTotal with the
atached one,

- in portal_catalog/erp5_mysql add the (attached)
z_related_resource_type method,

-in portal_catalog/erp5_mysql properties, add this line to
sql_catalog_related_keys :

relatedResourceType |
movement,catalog/portal_type/z_related_resource_type

Regards,
Alex
-------------- next part --------------
<dtml-comment>
title:
connection_id:erp5_sql_connection
max_rows:1000
max_cache:100
cache_time:0
class_name:
class_file:
</dtml-comment>
<params>uid
resource_uid
variation_text
from_table_list:list
where_expression
order_by_expression</params>
SELECT
SUM(movement.quantity) AS inventory,
SUM(movement.quantity) AS total_quantity,
SUM(movement.total_price) AS total_price,
SUM(movement.target_quantity) AS target_total_quantity,
SUM(movement.target_total_price) AS target_total_price,
AVG(movement.price) AS average_price

FROM
<dtml-in from_table_list> <dtml-var sequence-item> AS <dtml-var sequence-key><dtml-if sequence-end><dtml-else>,</dtml-if></dtml-in>

WHERE
1=1
<dtml-if where_expression> AND <dtml-var where_expression> </dtml-if>
<dtml-if resource_uid> AND (<dtml-in resource_uid> movement.resource_uid = <dtml-sqlvar sequence-item type="int"><dtml-if sequence-end><dtml-else> OR </dtml-if></dtml-in>) </dtml-if>
<dtml-if variation_text> AND movement.variation_text = <dtml-sqlvar variation_text type="string"> </dtml-if>
AND movement.is_accountable = 1
AND catalog.portal_type <> "Container"
AND catalog.portal_type <> "Simulation Movement"

<dtml-if order_by_expression>
ORDER BY <dtml-var order_by_expression>
</dtml-if>
-------------- next part --------------
<dtml-comment>
title:
connection_id:erp5_sql_connection
max_rows:1000
max_cache:100
cache_time:0
class_name:
class_file:
</dtml-comment>
<params>table_0
table_1</params>
<dtml-var table_0>.uid = catalog.uid
AND <dtml-var table_1>.uid = <dtml-var table_0>.resource_uid

[Alexandre Boeglin]

Post by Alexandre Boeglin
- in portal_catalog/erp5_mysql add the (attached)
z_related_resource_type method,
-in portal_catalog/erp5_mysql properties, add this line to
relatedResourceType |
movement,catalog/portal_type/z_related_resource_type

Actualy, these are only usefull if you want to be avble to get subtotals
based on the portal type of the resource.

[Alexandre Boeglin]

Post by Alexandre Boeglin
- in portal_skins/erp5_trade, replace Delivery_zGetTotal with the
atached one,

Actually, it's even easier, as the variation_text and resource_uid
parameters are handled by the buildSQLQuery too.

Regards,
Alex
-------------- next part --------------
<dtml-comment>
title:
connection_id:erp5_sql_connection
max_rows:1000
max_cache:100
cache_time:0
class_name:
class_file:
</dtml-comment>
<params>from_table_list:list
where_expression
order_by_expression</params>
SELECT
SUM(movement.quantity) AS inventory,
SUM(movement.quantity) AS total_quantity,
SUM(movement.total_price) AS total_price,
SUM(movement.target_quantity) AS target_total_quantity,
SUM(movement.target_total_price) AS target_total_price,
AVG(movement.price) AS average_price

FROM
<dtml-in from_table_list> <dtml-var sequence-item> AS <dtml-var sequence-key><dtml-if sequence-end><dtml-else>,</dtml-if></dtml-in>

WHERE
1=1
<dtml-if where_expression> AND <dtml-var where_expression> </dtml-if>
AND movement.is_accountable = 1
AND catalog.portal_type <> "Container"
AND catalog.portal_type <> "Simulation Movement"

<dtml-if order_by_expression>
ORDER BY <dtml-var order_by_expression>
</dtml-if>

[Romain Courteaud]

I made some change on Delivery_zGetTotal too.

First, I removed target references, which are not used anymore.

Then, total_price can be calculated with price*quantity.

And finally, the condition movement.is_accountable = 1 does not mean
anything. It is better to use has_cell_content = 0.

Regards.

Romain

Post by Alexandre Boeglin

Post by Alexandre Boeglin
- in portal_skins/erp5_trade, replace Delivery_zGetTotal with the
atached one,

Actually, it's even easier, as the variation_text and resource_uid
parameters are handled by the buildSQLQuery too.
Regards,
Alex

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: Delivery_zGetTotal
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20050324/2df67798/attachment.asc>