<dtml-comment>
title:
connection_id:erp5_sql_connection
max_rows:1000
max_cache:100
cache_time:0
class_name:InventoryListBrain
class_file:InventoryBrain.py
</dtml-comment>
<params>resource_uid:list
resource:list
from_date
to_date
node_uid
section_uid
node
section
variation_text=None
node_category
section_category
resource_category
simulation_state
query
omit_input
omit_output
omit_simulation</params>
SELECT
	catalog.path as path,
	catalog.uid as uid,
	catalog.relative_url as relative_url,
	movement.start_date AS start_date,
	movement.stop_date AS stop_date,
	movement.source_uid AS source_uid,
	movement.destination_uid AS destination_uid,
	movement.price AS price,
	movement.total_price AS total_price,
	SUM(stock.quantity) AS quantity,
	node.uid AS node_uid,
	node.title AS node_title,
	node.relative_url AS node_relative_url,
	section.uid AS section_uid,
	section.title AS section_title,
	section.relative_url AS section_relative_url,
	resource.title AS resource_title,
	resource.uid AS resource_uid,
	resource.relative_url AS resource_relative_url,
	movement.variation_text AS variation_text,
	movement.delivery_uid AS explanation_uid,
	catalog.simulation_state AS simulation_state
FROM
	movement
        LEFT JOIN catalog ON (movement.uid = catalog.uid)
        LEFT JOIN stock ON (movement.uid = stock.uid),
        catalog AS node, catalog AS section, catalog AS resource <dtml-if node_category>, catalog AS node_c, catalog AS node_bc, category AS node_membership </dtml-if> <dtml-if section_category>, catalog AS section_c, catalog AS section_bc, category AS section_membership</dtml-if> <dtml-if resource_category>, catalog AS resource_c, catalog AS resource_bc, category AS resource_membership </dtml-if>  <dtml-if query>, category </dtml-if> 
WHERE
	catalog.uid = movement.uid
AND	catalog.uid = stock.uid
AND	node.uid = stock.node_uid
AND	section.uid = stock.section_uid
AND	movement.resource_uid = resource.uid
<dtml-if resource>AND	(<dtml-in resource> resource.relative_url = <dtml-sqlvar sequence-item type="string"><dtml-if sequence-end><dtml-else> OR </dtml-if></dtml-in>)
</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>AND	movement.is_accountable = 1
<dtml-if node_uid>AND	stock.node_uid  = <dtml-sqlvar node_uid type="int">
</dtml-if><dtml-if section_uid>AND	stock.section_uid  = <dtml-sqlvar section_uid type="int">
</dtml-if><dtml-if from_date>AND	movement.stop_date >= <dtml-sqlvar from_date type="string">
</dtml-if><dtml-if to_date>AND	movement.stop_date < <dtml-sqlvar to_date type="string">
</dtml-if><dtml-if "variation_text is not None">AND	movement.variation_text = <dtml-sqlvar variation_text type="string">
</dtml-if><dtml-if omit_simulation>AND	catalog.portal_type != "Simulation Movement"
</dtml-if><dtml-if omit_input>AND	stock.quantity < 0 AND movement.source_uid <> movement.destination_uid 
</dtml-if><dtml-if omit_output>AND	stock.quantity > 0 AND movement.source_uid <> movement.destination_uid 
</dtml-if><dtml-if node_category>AND	node_c.relative_url = <dtml-sqlvar node_category type="string">
AND	node_membership.category_uid = node_c.uid
AND	node_membership.base_category_uid = node_bc.uid
AND	node_membership.uid = node.uid
</dtml-if><dtml-if section_category>AND	section_c.relative_url = <dtml-sqlvar section_category type="string">
AND	section_membership.category_uid =  section_c.uid
AND	section_membership.base_category_uid = section_bc.uid
AND	section_membership.uid = section.uid
</dtml-if><dtml-if resource_category>AND	resource_c.relative_url = <dtml-sqlvar resource_category type="string">
AND	resource_membership.category_uid =  resource_c.uid
AND	resource_membership.base_category_uid = resource_bc.uid
AND	resource_membership.uid = resource.uid
</dtml-if><dtml-if simulation_state>AND	(<dtml-in simulation_state> catalog.simulation_state = <dtml-sqlvar sequence-item type="string"><dtml-if sequence-end><dtml-else> OR </dtml-if></dtml-in>)
</dtml-if><dtml-if query>AND	category.uid = node.uid
AND	<dtml-var query></dtml-if>
GROUP BY
	movement.delivery_uid, resource.uid, movement.variation_text, node.uid, section.uid, catalog.simulation_state
ORDER BY
	movement.start_date, node.title, resource.title, movement.quantity