<dtml-comment>
title:
connection_id:MySQL
max_rows:1000
max_cache:100
cache_time:0
class_name:
class_file:
</dtml-comment>
<params>query
node:list
simulation_state:list
from_date
to_date
resource:list
section_category
transaction
omit_input
omit_output</params>
SELECT
  SUM(stock.quantity) AS quantity
FROM
  catalog
  , catalog AS child
  , movement
  , stock
<dtml-if resource>
  , catalog AS resource
</dtml-if>
<dtml-if node>
  , catalog AS node
</dtml-if>
<dtml-if section_category>
  , catalog AS section_c
  , catalog AS section_bc
  , category AS section_membership
  , catalog AS section
</dtml-if>
<dtml-if query>
  , category
  , roles_and_users
  , subject
</dtml-if>
WHERE
  catalog.uid = child.parent_uid
  AND child.uid = stock.uid
  AND child.uid = movement.uid
  AND movement.is_accountable = 1
<dtml-if transaction>
  AND catalog.uid = <dtml-sqlvar transaction type="int">
</dtml-if>
<dtml-if resource>
  AND movement.resource_uid = resource.uid
  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 node>
  AND stock.node_uid = node.uid
  AND (
  <dtml-in node>
    node.relative_url = <dtml-sqlvar sequence-item type="string"><dtml-if sequence-end><dtml-else> OR </dtml-if>
  </dtml-in>
  )
</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 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 section_category>
  AND section.uid = stock.section_uid
  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 omit_input>
  AND stock.quantity < 0
</dtml-if>
<dtml-if omit_output>
  AND stock.quantity > 0
</dtml-if>
<dtml-if query>
  AND catalog.uid = category.uid
  AND catalog.uid = roles_and_users.uid
  AND catalog.uid = subject.uid
  AND <dtml-var query>
</dtml-if>
<dtml-if sort_on>
  ORDER BY <dtml-var sort_on>
</dtml-if>