<?xml version="1.0"?>
<ZopeData>
  <record id="1" aka="AAAAAAAAAAE=">
    <pickle>
      <tuple>
        <global name="SQL" module="Products.ZSQLMethods.SQL"/>
        <tuple/>
      </tuple>
    </pickle>
    <pickle>
      <dictionary>
        <item>
            <key> <string>_Use_Database_Methods_Permission</string> </key>
            <value>
              <list>
                <string>Member</string>
              </list>
            </value>
        </item>
        <item>
            <key> <string>__ac_local_roles__</string> </key>
            <value>
              <none/>
            </value>
        </item>
        <item>
            <key> <string>_arg</string> </key>
            <value>
              <object>
                <klass>
                  <global name="Args" module="Shared.DC.ZRDB.Aqueduct"/>
                </klass>
                <tuple/>
                <state>
                  <dictionary>
                    <item>
                        <key> <string>_data</string> </key>
                        <value>
                          <dictionary>
                            <item>
                                <key> <string>amount</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>amount_range_max</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>amount_range_min</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>at_date</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>count</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>creation_date_range_max</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>creation_date_range_min</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>delivery_mirror_section_title</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>destination_section_where_expression</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>entity</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>from_date</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>no_limit</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>node</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>omit_input</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>omit_output</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>resource</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>search_result_keys</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string encoding="base64">Jyc=</string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>section_category</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>select_expression</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>selection</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>selection_domain</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>selection_name</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string>None</string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>selection_params</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>selection_report</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>source_section_where_expression</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>specific_reference</string> </key>
                                <value>
                                  <dictionary/>
                                </value>
                            </item>
                            <item>
                                <key> <string>stat</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>to_date</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                            <item>
                                <key> <string>transaction_uid</string> </key>
                                <value>
                                  <dictionary>
                                    <item>
                                        <key> <string>default</string> </key>
                                        <value> <string></string> </value>
                                    </item>
                                  </dictionary>
                                </value>
                            </item>
                          </dictionary>
                        </value>
                    </item>
                    <item>
                        <key> <string>_keys</string> </key>
                        <value>
                          <list>
<string>selection</string>
<string>selection_name</string>
<string>selection_domain</string>
<string>selection_report</string>
<string>selection_params</string>
<string>select_expression</string>
<string>from_date</string>
<string>at_date</string>
<string>to_date</string>
<string>node</string>
<string>resource</string>
<string>entity</string>
<string>stat</string>
<string>omit_input</string>
<string>omit_output</string>
<string>amount</string>
<string>amount_range_min</string>
<string>amount_range_max</string>
<string>creation_date_range_min</string>
<string>creation_date_range_max</string>
<string>section_category</string>
<string>count</string>
<string>transaction_uid</string>
<string>specific_reference</string>
<string>no_limit</string>
<string>search_result_keys</string>
<string>delivery_mirror_section_title</string>
<string>source_section_where_expression</string>
<string>destination_section_where_expression</string>
                          </list>
                        </value>
                    </item>
                  </dictionary>
                </state>
              </object>
            </value>
        </item>
        <item>
            <key> <string>_owner</string> </key>
            <value>
              <none/>
            </value>
        </item>
        <item>
            <key> <string>allow_simple_one_argument_traversal</string> </key>
            <value>
              <none/>
            </value>
        </item>
        <item>
            <key> <string>arguments_src</string> </key>
            <value> <string>selection\r\n
selection_name=None\r\n
selection_domain\r\n
selection_report\r\n
selection_params=""\r\n
select_expression\r\n
from_date=""\r\n
at_date=""\r\n
to_date=""\r\n
node=""\r\n
resource=""\r\n
entity=""\r\n
stat=""\r\n
omit_input=""\r\n
omit_output=""\r\n
amount=""\r\n
amount_range_min=""\r\n
amount_range_max=""\r\n
creation_date_range_min=""\r\n
creation_date_range_max=""\r\n
section_category=""\r\n
count=""\r\n
transaction_uid=""\r\n
specific_reference\r\n
no_limit\r\n
search_result_keys=\'\'\r\n
delivery_mirror_section_title=""\r\n
source_section_where_expression\r\n
destination_section_where_expression</string> </value>
        </item>
        <item>
            <key> <string>cache_time_</string> </key>
            <value> <int>0</int> </value>
        </item>
        <item>
            <key> <string>class_file_</string> </key>
            <value> <string>ZSQLCatalog.zsqlbrain</string> </value>
        </item>
        <item>
            <key> <string>class_name_</string> </key>
            <value> <string>ZSQLBrain</string> </value>
        </item>
        <item>
            <key> <string>connection_hook</string> </key>
            <value>
              <none/>
            </value>
        </item>
        <item>
            <key> <string>connection_id</string> </key>
            <value> <string>erp5_sql_connection</string> </value>
        </item>
        <item>
            <key> <string>id</string> </key>
            <value> <string>AccountingTransactionModule_zGetAccountingTransactionList</string> </value>
        </item>
        <item>
            <key> <string>max_cache_</string> </key>
            <value> <int>100</int> </value>
        </item>
        <item>
            <key> <string>max_rows_</string> </key>
            <value> <int>0</int> </value>
        </item>
        <item>
            <key> <string>src</string> </key>
            <value> <string encoding="cdata"><![CDATA[

<dtml-let query="portal_catalog.buildSQLQuery(query=portal_catalog.getSecurityQuery(**selection_params), select_expression_key=(\'operation_date\', \'specific_reference\', \'delivery_mirror_section_title\',), **selection_params)">\n
<dtml-let search_result_keys="search_result_keys or portal_catalog.getCatalogSearchResultKeys()">\n
<dtml-let use_movement_table="from_date or to_date or at_date or resource or stat or amount or amount_range_min or amount_range_max">\n
<dtml-let accounting_movement_list="portal_url.getPortalObject().getPortalAccountingMovementTypeList()">\n
\n
\n
<dtml-comment>\n
#################################################\n
#                                               #\n
#   If both entity and section_category are NOT #\n
#                                               #\n
#################################################\n
</dtml-comment>\n
<dtml-if "not (entity or section_category)">\n
        SELECT\n
      <dtml-if stat>\n
        SUM(stock.total_price) AS total_price\n
      <dtml-elif count>\n
        COUNT(DISTINCT catalog.uid) AS count\n
      <dtml-else>\n
        DISTINCT\n
        <dtml-in "search_result_keys">\n
          <dtml-unless sequence-start>,</dtml-unless>\n
          <dtml-var sequence-item>\n
        </dtml-in>\n
        , delivery.start_date as operation_date\n
        , \'source\' as section_mark\n
        <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
      </dtml-if>\n
    \n
    FROM\n
      <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
        <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
          <dtml-var table_item> AS <dtml-var table_key>,\n
        </dtml-if>\n
      </dtml-in>\n
      <dtml-if selection_domain>\n
        <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
      </dtml-if>\n
      <dtml-if selection_report>\n
        <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
      </dtml-if>\n
      <dtml-if use_movement_table> movement,  </dtml-if>\n
      <dtml-if "stat or omit_input or omit_output or node">\n
          stock, catalog as child,\n
        <dtml-unless section_category>category,</dtml-unless>\n
      </dtml-if>\n
      <dtml-if "section_category">category, catalog as section, </dtml-if>\n
      catalog,\n
      delivery\n
    \n
    WHERE\n
      delivery.uid = catalog.uid\n
      <dtml-if "query[\'where_expression\']">\n
        AND <dtml-var "query[\'where_expression\']">\n
      </dtml-if>\n
      <dtml-if selection_domain>\n
        AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
      </dtml-if>\n
      <dtml-if selection_report>\n
        AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
      </dtml-if>\n
      <dtml-if specific_reference> AND (\n
                catalog.source_reference LIKE\n
                    <dtml-sqlvar specific_reference type="string">\n
            OR  catalog.destination_reference LIKE\n
              <dtml-sqlvar specific_reference type="string"> )</dtml-if>\n
      <dtml-if transaction_uid>\n
        AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
      <dtml-if amount>\n
        AND ABS(movement.quantity) = <dtml-sqlvar amount type="float">\n
      </dtml-if>\n
      <dtml-if amount_range_min>\n
        AND ABS(movement.quantity) >= <dtml-sqlvar amount_range_min type="float">\n
      </dtml-if>\n
      <dtml-if amount_range_max>\n
        AND ABS(movement.quantity) < <dtml-sqlvar amount_range_max type="float">\n
      </dtml-if>\n
      <dtml-if creation_date_range_min>\n
        AND catalog.creation_date >=\n
            <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
      <dtml-if creation_date_range_max>\n
        AND catalog.creation_date <\n
            <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
      <dtml-if use_movement_table>\n
        AND movement.is_accountable\n
        AND movement.explanation_uid = catalog.uid </dtml-if>\n
      <dtml-if resource> AND movement.resource_uid =\n
          <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
      <dtml-if entity> AND (\n
         delivery.destination_section_uid =\n
                      <dtml-var "restrictedTraverse(entity).getUid()">\n
         OR delivery.source_section_uid =\n
                      <dtml-var "restrictedTraverse(entity).getUid()">\n
         )</dtml-if>\n
      <dtml-if section_category> AND (\n
          (  category.uid = delivery.destination_section_uid\n
               )  OR ( category.uid = delivery.source_section_uid ) )\n
        AND section.portal_type = "Organisation"\n
        AND section.uid = category.uid\n
        AND category.category_uid =\n
        <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
      </dtml-if>\n
      \n
      <dtml-comment>\n
      -- FIXME: we want stop date when we are destination_section and start_date when\n
      --   we are source_section (one solution is to read in stock.date, but we try\n
      --   to avoid the use of stock table here)\n
      </dtml-comment>\n
      <dtml-if from_date>\n
        AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
      </dtml-if>\n
      <dtml-if to_date>\n
        AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
      </dtml-if>\n
      <dtml-if at_date>\n
        AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
      </dtml-if>\n
    \n
      <dtml-if node>\n
        <dtml-if stat>\n
          AND stock.uid IN (\n
              SELECT child.uid from catalog as child\n
               WHERE category.uid = stock.section_uid\n
                 AND child.parent_uid = catalog.uid\n
              HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                       WHERE stock.uid = child.uid \n
                         AND child.parent_uid = catalog.uid\n
                         AND (\n
                        <dtml-in node>\n
                          <dtml-unless sequence-start>OR</dtml-unless>\n
                             stock.node_uid =\n
                                <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                        </dtml-in> )\n
                    )\n
              )\n
        <dtml-else>\n
          AND category.uid = stock.section_uid\n
          AND child.uid = stock.uid\n
          AND child.parent_uid = catalog.uid\n
          AND (\n
          <dtml-in node>\n
            <dtml-unless sequence-start>OR</dtml-unless>\n
               stock.node_uid =\n
                  <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
          </dtml-in> )\n
        </dtml-if>\n
      </dtml-if>\n
    \n
      <dtml-if stat>\n
        AND category.uid = stock.section_uid\n
        AND child.uid = stock.uid\n
        AND child.parent_uid = catalog.uid\n
        <dtml-if omit_input> AND stock.total_price < 0\n
                             AND stock.uid = movement.uid\n
        </dtml-if>\n
        <dtml-if omit_output> AND stock.total_price > 0\n
                              AND stock.uid = movement.uid\n
        </dtml-if>\n
      </dtml-if>\n
      <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
        AND ( 0 = 1\n
          <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
            OR catalog.uid = <dtml-var sequence-item>\n
          </dtml-in>\n
        )\n
      </dtml-if>\n
    \n
    <dtml-unless count>\n
      <dtml-if "query[\'order_by_expression\']">\n
        ORDER BY <dtml-var "query[\'order_by_expression\']">\n
      </dtml-if>\n
      <dtml-if "query[\'limit_expression\']">\n
        LIMIT <dtml-var "query[\'limit_expression\']">\n
      <dtml-else>\n
        <dtml-unless no_limit>LIMIT 1000</dtml-unless>\n
      </dtml-if>\n
    </dtml-unless>\n
\n
</dtml-if>\n
\n
\n
\n
\n
<dtml-comment>\n
#################################################\n
#                                               #\n
#   If entity or section_category IS EXISTS     #\n
#                                               #\n
#################################################\n
</dtml-comment>\n
<dtml-if "entity or section_category">\n
\n
        <dtml-if stat>\n
          SELECT SUM(total_price) AS total_price FROM (\n
          SELECT DISTINCT total_price, uid FROM (\n
        <dtml-elif count>\n
          SELECT COUNT(DISTINCT uid) AS count FROM (\n
        <dtml-else>\n
          SELECT main.*,\n
          ( SELECT title FROM catalog as mirror_catalog WHERE mirror_catalog.uid=main.delivery_mirror_section_uid ) AS delivery_mirror_section_title,\n
          ( SELECT IFNULL(SUM(stock.total_price), 0) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = main.uid AND stock.section_uid=main.category_uid AND stock.total_price > 0 AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>) AS debit,\n
          ( SELECT IFNULL(-SUM(stock.total_price), 0) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = main.uid AND stock.section_uid=main.category_uid AND stock.total_price < 0 AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>) AS credit\n
          FROM (\n
           SELECT *\n
           FROM (\n
        </dtml-if>\n
\n
<dtml-comment>\n
         ##################\n
         #                #\n
         # Source Section #\n
         #                #\n
         ##################\n
</dtml-comment>\n
\n
        (\n
            SELECT\n
          <dtml-if stat>\n
            stock.total_price, catalog.uid\n
          <dtml-elif count>\n
            catalog.uid\n
          <dtml-else>\n
            DISTINCT\n
            <dtml-in "search_result_keys">\n
              <dtml-unless sequence-start>,</dtml-unless>\n
              <dtml-var sequence-item>\n
            </dtml-in>\n
            , delivery.start_date as operation_date\n
            , delivery.destination_section_uid as delivery_mirror_section_uid\n
            , \'source\' as section_mark\n
            , catalog.source_reference as specific_reference\n
            <dtml-if "section_category">, category.uid as category_uid</dtml-if>\n
            <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
              <dtml-let order_column="query[\'order_by_expression\'].split(\' \')[0]">\n
                <dtml-if "not order_column in (\'operation_date\', \'specific_reference\')">\n
                  ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
                </dtml-if>\n
              </dtml-let>\n
            </dtml-if>\n
            <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
          </dtml-if>\n
        \n
        FROM\n
          <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
            <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
              <dtml-var table_item> AS <dtml-var table_key>,\n
            </dtml-if>\n
          </dtml-in>\n
          <dtml-if selection_domain>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
          </dtml-if>\n
          <dtml-if use_movement_table> movement,  </dtml-if>\n
          <dtml-if "stat or omit_input or omit_output or node">\n
              stock, catalog as child, </dtml-if>\n
          <dtml-if "section_category">category, catalog as section, </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">delivery AS mirror_section_delivery, catalog AS mirror_section_catalog,</dtml-if>\n
          catalog,\n
          delivery\n
        \n
        WHERE\n
          delivery.uid = catalog.uid\n
          <dtml-if "query[\'where_expression\']">\n
            AND <dtml-var "query[\'where_expression\']">\n
          </dtml-if>\n
          <dtml-if source_section_where_expression>\n
            AND <dtml-var source_section_where_expression>\n
          </dtml-if>\n
          <dtml-if selection_domain>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
          </dtml-if>\n
          <dtml-if specific_reference>\n
            AND catalog.source_reference LIKE <dtml-sqlvar specific_reference type="string">\n
          </dtml-if>\n
          <dtml-if transaction_uid>\n
            AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
          <dtml-if "amount or amount_range_min or amount_range_max">\n
            AND EXISTS\n
              (SELECT * FROM stock, catalog as child WHERE\n
               stock.uid=child.uid AND child.parent_uid=catalog.uid AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>\n
               <dtml-if amount>\n
                 AND <dtml-sqltest amount column="ABS(stock.total_price)" type=float op=eq>\n
               </dtml-if>\n
               <dtml-if amount_range_min>\n
                 AND <dtml-sqltest amount_range_min column="ABS(stock.total_price)" type=float op=ge>\n
               </dtml-if>\n
               <dtml-if amount_range_max>\n
                 AND <dtml-sqltest amount_range_max column="ABS(stock.total_price)" type=float op=lt>\n
               </dtml-if>\n
              )\n
          </dtml-if>\n
          <dtml-if creation_date_range_min>\n
            AND catalog.creation_date >=\n
                <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
          <dtml-if creation_date_range_max>\n
            AND catalog.creation_date <\n
                <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
          <dtml-if use_movement_table>\n
            AND movement.is_accountable\n
            AND movement.explanation_uid = catalog.uid </dtml-if>\n
          <dtml-if resource> AND movement.resource_uid =\n
              <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
          -- SOURCE SECTION!!!!!\n
          <dtml-if entity>\n
            AND delivery.destination_section_uid =<dtml-var "restrictedTraverse(entity).getUid()">\n
          </dtml-if>\n
          <dtml-if section_category>\n
            AND category.uid = delivery.source_section_uid\n
            AND section.portal_type = "Organisation"\n
            AND section.uid = category.uid\n
            AND category.category_uid =\n
            <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
          </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">\n
            AND mirror_section_catalog.uid = mirror_section_delivery.destination_section_uid\n
            AND mirror_section_delivery.uid = catalog.uid\n
            AND <dtml-sqltest delivery_mirror_section_title type=string op=like column=mirror_section_catalog.title>\n
          </dtml-if>\n
          \n
          <dtml-comment>\n
          -- FIXME: we want stop date when we are destination_section and start_date when\n
          --   we are source_section (one solution is to read in stock.date, but we try\n
          --   to avoid the use of stock table here)\n
          </dtml-comment>\n
          <dtml-if from_date>\n
            AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
          </dtml-if>\n
          <dtml-if to_date>\n
            AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
          </dtml-if>\n
          <dtml-if at_date>\n
            AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
          </dtml-if>\n
        \n
          <dtml-if node>\n
            <dtml-if stat>\n
              AND stock.uid IN (\n
                  SELECT child.uid from catalog as child\n
                   WHERE category.uid = stock.section_uid\n
                     AND child.parent_uid = catalog.uid\n
                  HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                           WHERE stock.uid = child.uid \n
                             AND child.parent_uid = catalog.uid\n
                             AND (\n
                            <dtml-in node>\n
                              <dtml-unless sequence-start>OR</dtml-unless>\n
                                 stock.node_uid =\n
                                    <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                            </dtml-in> )\n
                        )\n
                  )\n
            <dtml-else>\n
              AND category.uid = stock.section_uid\n
              AND child.uid = stock.uid\n
              AND child.parent_uid = catalog.uid\n
              AND (\n
              <dtml-in node>\n
                <dtml-unless sequence-start>OR</dtml-unless>\n
                   stock.node_uid =\n
                      <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
              </dtml-in> )\n
            </dtml-if>\n
          </dtml-if>\n
        \n
          <dtml-if stat>\n
            AND category.uid = stock.section_uid\n
            AND child.uid = stock.uid\n
            AND child.parent_uid = catalog.uid\n
            <dtml-if omit_input> AND stock.total_price < 0\n
                                 AND stock.uid = movement.uid\n
            </dtml-if>\n
            <dtml-if omit_output> AND stock.total_price > 0\n
                                  AND stock.uid = movement.uid\n
            </dtml-if>\n
          </dtml-if>\n
          <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
            AND ( 0 = 1\n
              <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
                OR catalog.uid = <dtml-var sequence-item>\n
              </dtml-in>\n
            )\n
          </dtml-if>\n
\n
        )\n
\n
\n
\n
UNION\n
\n
\n
\n
<dtml-comment>\n
         #######################\n
         #                     #\n
         # Destination Section #\n
         #                     #\n
         #######################\n
</dtml-comment>\n
\n
        (\n
            SELECT\n
          <dtml-if stat>\n
            stock.total_price, catalog.uid\n
          <dtml-elif count>\n
            catalog.uid\n
          <dtml-else>\n
            DISTINCT\n
            <dtml-in "search_result_keys">\n
              <dtml-unless sequence-start>,</dtml-unless>\n
              <dtml-var sequence-item>\n
            </dtml-in>\n
            , delivery.stop_date as operation_date\n
            , delivery.source_section_uid as delivery_mirror_section_uid\n
            , \'destination\' as section_mark\n
            , catalog.destination_reference as specific_reference\n
            <dtml-if "section_category">, category.uid as category_uid</dtml-if>\n
            <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
              <dtml-let order_column="query[\'order_by_expression\'].split(\' \')[0]">\n
                <dtml-if "not order_column in (\'operation_date\', \'specific_reference\')">\n
                  ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
                </dtml-if>\n
              </dtml-let>\n
            </dtml-if>\n
            <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
          </dtml-if>\n
        \n
        FROM\n
          <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
            <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
              <dtml-var table_item> AS <dtml-var table_key>,\n
            </dtml-if>\n
          </dtml-in>\n
          <dtml-if selection_domain>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
          </dtml-if>\n
          <dtml-if use_movement_table> movement,  </dtml-if>\n
          <dtml-if "stat or omit_input or omit_output or node">\n
              stock, catalog as child, </dtml-if>\n
          <dtml-if "section_category">category, catalog as section, </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">delivery AS mirror_section_delivery, catalog AS mirror_section_catalog,</dtml-if>\n
          catalog,\n
          delivery\n
        \n
        WHERE\n
          delivery.uid = catalog.uid\n
          <dtml-if "query[\'where_expression\']">\n
            AND <dtml-var "query[\'where_expression\']">\n
          </dtml-if>\n
          <dtml-if destination_section_where_expression>\n
            AND <dtml-var destination_section_where_expression>\n
          </dtml-if>\n
          <dtml-if selection_domain>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
          </dtml-if>\n
          <dtml-if specific_reference>\n
            AND catalog.destination_reference LIKE <dtml-sqlvar specific_reference type="string">\n
          </dtml-if>\n
          <dtml-if transaction_uid>\n
            AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
          <dtml-if "amount or amount_range_min or amount_range_max">\n
            AND EXISTS\n
              (SELECT * FROM stock, catalog as child WHERE\n
               stock.uid=child.uid AND child.parent_uid=catalog.uid AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>\n
               <dtml-if amount>\n
                 AND <dtml-sqltest amount column="ABS(stock.total_price)" type=float op=eq>\n
               </dtml-if>\n
               <dtml-if amount_range_min>\n
                 AND <dtml-sqltest amount_range_min column="ABS(stock.total_price)" type=float op=ge>\n
               </dtml-if>\n
               <dtml-if amount_range_max>\n
                 AND <dtml-sqltest amount_range_max column="ABS(stock.total_price)" type=float op=lt>\n
               </dtml-if>\n
              )\n
          </dtml-if>\n
          <dtml-if creation_date_range_min>\n
            AND catalog.creation_date >=\n
                <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
          <dtml-if creation_date_range_max>\n
            AND catalog.creation_date <\n
                <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
          <dtml-if use_movement_table>\n
            AND movement.is_accountable\n
            AND movement.explanation_uid = catalog.uid </dtml-if>\n
          <dtml-if resource> AND movement.resource_uid =\n
              <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
          -- DESTINATION SECTION!!!!!\n
          <dtml-if entity>\n
            AND delivery.source_section_uid = <dtml-var "restrictedTraverse(entity).getUid()">\n
          </dtml-if>\n
          <dtml-if section_category>\n
            AND category.uid = delivery.destination_section_uid\n
            AND section.portal_type = "Organisation"\n
            AND section.uid = category.uid\n
            AND category.category_uid =\n
            <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
          </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">\n
            AND mirror_section_catalog.uid = mirror_section_delivery.source_section_uid\n
            AND mirror_section_delivery.uid = catalog.uid\n
            AND <dtml-sqltest delivery_mirror_section_title type=string op=like column=mirror_section_catalog.title>\n
          </dtml-if>\n
\n
          <dtml-comment>\n
          -- FIXME: we want stop date when we are destination_section and start_date when\n
          --   we are source_section (one solution is to read in stock.date, but we try\n
          --   to avoid the use of stock table here)\n
          </dtml-comment>\n
          <dtml-if from_date>\n
            AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
          </dtml-if>\n
          <dtml-if to_date>\n
            AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
          </dtml-if>\n
          <dtml-if at_date>\n
            AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
          </dtml-if>\n
        \n
          <dtml-if node>\n
            <dtml-if stat>\n
              AND stock.uid IN (\n
                  SELECT child.uid from catalog as child\n
                   WHERE category.uid = stock.section_uid\n
                     AND child.parent_uid = catalog.uid\n
                  HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                           WHERE stock.uid = child.uid \n
                             AND child.parent_uid = catalog.uid\n
                             AND (\n
                            <dtml-in node>\n
                              <dtml-unless sequence-start>OR</dtml-unless>\n
                                 stock.node_uid =\n
                                    <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                            </dtml-in> )\n
                        )\n
                  )\n
            <dtml-else>\n
              AND category.uid = stock.section_uid\n
              AND child.uid = stock.uid\n
              AND child.parent_uid = catalog.uid\n
              AND (\n
              <dtml-in node>\n
                <dtml-unless sequence-start>OR</dtml-unless>\n
                   stock.node_uid =\n
                      <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
              </dtml-in> )\n
            </dtml-if>\n
          </dtml-if>\n
        \n
          <dtml-if stat>\n
            AND category.uid = stock.section_uid\n
            AND child.uid = stock.uid\n
            AND child.parent_uid = catalog.uid\n
            <dtml-if omit_input> AND stock.total_price < 0\n
                                 AND stock.uid = movement.uid\n
            </dtml-if>\n
            <dtml-if omit_output> AND stock.total_price > 0\n
                                  AND stock.uid = movement.uid\n
            </dtml-if>\n
          </dtml-if>\n
          <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
            AND ( 0 = 1\n
              <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
                OR catalog.uid = <dtml-var sequence-item>\n
              </dtml-in>\n
            )\n
          </dtml-if>\n
        )\n
\n
\n
<dtml-comment>\n
         ##################\n
         #                #\n
         #  ORDER, LIMIT  #\n
         #                #\n
         ##################\n
</dtml-comment>\n
\n
\n
        <dtml-if "count">\n
        ) as catalog\n
        <dtml-elif "stat">\n
        ) as sb\n
        ) as catalog\n
        <dtml-else>\n
        ) as catalog\n
        </dtml-if>\n
\n
        <dtml-if "not (stat or count)">\n
            GROUP BY uid\n
          <dtml-if "query[\'order_by_expression\']">\n
            ORDER BY <dtml-var "query[\'order_by_expression\'].split(\'.\')[-1]">\n
          </dtml-if>\n
          <dtml-if "query[\'limit_expression\']">\n
            LIMIT <dtml-var "query[\'limit_expression\']">\n
          <dtml-else>\n
            <dtml-unless no_limit>LIMIT 1000</dtml-unless>\n
          </dtml-if>\n
        ) as main\n
        </dtml-if>\n
\n
</dtml-if>\n
\n
\n
\n
</dtml-let></dtml-let></dtml-let></dtml-let>\n
\n
<dtml-comment> vim: syntax=dtml\n
</dtml-comment>\n


]]></string> </value>
        </item>
        <item>
            <key> <string>template</string> </key>
            <value>
              <object>
                <klass>
                  <global name="__newobj__" module="copy_reg"/>
                </klass>
                <tuple>
                  <global name="SQL" module="Shared.DC.ZRDB.DA"/>
                </tuple>
                <state>
                  <dictionary>
                    <item>
                        <key> <string>__name__</string> </key>
                        <value> <string encoding="cdata"><![CDATA[

<string>

]]></string> </value>
                    </item>
                    <item>
                        <key> <string>_vars</string> </key>
                        <value>
                          <dictionary/>
                        </value>
                    </item>
                    <item>
                        <key> <string>globals</string> </key>
                        <value>
                          <dictionary/>
                        </value>
                    </item>
                    <item>
                        <key> <string>raw</string> </key>
                        <value> <string encoding="cdata"><![CDATA[

<dtml-let query="portal_catalog.buildSQLQuery(query=portal_catalog.getSecurityQuery(**selection_params), select_expression_key=(\'operation_date\', \'specific_reference\', \'delivery_mirror_section_title\',), **selection_params)">\n
<dtml-let search_result_keys="search_result_keys or portal_catalog.getCatalogSearchResultKeys()">\n
<dtml-let use_movement_table="from_date or to_date or at_date or resource or stat or amount or amount_range_min or amount_range_max">\n
<dtml-let accounting_movement_list="portal_url.getPortalObject().getPortalAccountingMovementTypeList()">\n
\n
\n
<dtml-comment>\n
#################################################\n
#                                               #\n
#   If both entity and section_category are NOT #\n
#                                               #\n
#################################################\n
</dtml-comment>\n
<dtml-if "not (entity or section_category)">\n
        SELECT\n
      <dtml-if stat>\n
        SUM(stock.total_price) AS total_price\n
      <dtml-elif count>\n
        COUNT(DISTINCT catalog.uid) AS count\n
      <dtml-else>\n
        DISTINCT\n
        <dtml-in "search_result_keys">\n
          <dtml-unless sequence-start>,</dtml-unless>\n
          <dtml-var sequence-item>\n
        </dtml-in>\n
        , delivery.start_date as operation_date\n
        , \'source\' as section_mark\n
        <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
      </dtml-if>\n
    \n
    FROM\n
      <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
        <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
          <dtml-var table_item> AS <dtml-var table_key>,\n
        </dtml-if>\n
      </dtml-in>\n
      <dtml-if selection_domain>\n
        <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
      </dtml-if>\n
      <dtml-if selection_report>\n
        <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
      </dtml-if>\n
      <dtml-if use_movement_table> movement,  </dtml-if>\n
      <dtml-if "stat or omit_input or omit_output or node">\n
          stock, catalog as child,\n
        <dtml-unless section_category>category,</dtml-unless>\n
      </dtml-if>\n
      <dtml-if "section_category">category, catalog as section, </dtml-if>\n
      catalog,\n
      delivery\n
    \n
    WHERE\n
      delivery.uid = catalog.uid\n
      <dtml-if "query[\'where_expression\']">\n
        AND <dtml-var "query[\'where_expression\']">\n
      </dtml-if>\n
      <dtml-if selection_domain>\n
        AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
      </dtml-if>\n
      <dtml-if selection_report>\n
        AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
      </dtml-if>\n
      <dtml-if specific_reference> AND (\n
                catalog.source_reference LIKE\n
                    <dtml-sqlvar specific_reference type="string">\n
            OR  catalog.destination_reference LIKE\n
              <dtml-sqlvar specific_reference type="string"> )</dtml-if>\n
      <dtml-if transaction_uid>\n
        AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
      <dtml-if amount>\n
        AND ABS(movement.quantity) = <dtml-sqlvar amount type="float">\n
      </dtml-if>\n
      <dtml-if amount_range_min>\n
        AND ABS(movement.quantity) >= <dtml-sqlvar amount_range_min type="float">\n
      </dtml-if>\n
      <dtml-if amount_range_max>\n
        AND ABS(movement.quantity) < <dtml-sqlvar amount_range_max type="float">\n
      </dtml-if>\n
      <dtml-if creation_date_range_min>\n
        AND catalog.creation_date >=\n
            <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
      <dtml-if creation_date_range_max>\n
        AND catalog.creation_date <\n
            <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
      <dtml-if use_movement_table>\n
        AND movement.is_accountable\n
        AND movement.explanation_uid = catalog.uid </dtml-if>\n
      <dtml-if resource> AND movement.resource_uid =\n
          <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
      <dtml-if entity> AND (\n
         delivery.destination_section_uid =\n
                      <dtml-var "restrictedTraverse(entity).getUid()">\n
         OR delivery.source_section_uid =\n
                      <dtml-var "restrictedTraverse(entity).getUid()">\n
         )</dtml-if>\n
      <dtml-if section_category> AND (\n
          (  category.uid = delivery.destination_section_uid\n
               )  OR ( category.uid = delivery.source_section_uid ) )\n
        AND section.portal_type = "Organisation"\n
        AND section.uid = category.uid\n
        AND category.category_uid =\n
        <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
      </dtml-if>\n
      \n
      <dtml-comment>\n
      -- FIXME: we want stop date when we are destination_section and start_date when\n
      --   we are source_section (one solution is to read in stock.date, but we try\n
      --   to avoid the use of stock table here)\n
      </dtml-comment>\n
      <dtml-if from_date>\n
        AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
      </dtml-if>\n
      <dtml-if to_date>\n
        AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
      </dtml-if>\n
      <dtml-if at_date>\n
        AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
      </dtml-if>\n
    \n
      <dtml-if node>\n
        <dtml-if stat>\n
          AND stock.uid IN (\n
              SELECT child.uid from catalog as child\n
               WHERE category.uid = stock.section_uid\n
                 AND child.parent_uid = catalog.uid\n
              HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                       WHERE stock.uid = child.uid \n
                         AND child.parent_uid = catalog.uid\n
                         AND (\n
                        <dtml-in node>\n
                          <dtml-unless sequence-start>OR</dtml-unless>\n
                             stock.node_uid =\n
                                <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                        </dtml-in> )\n
                    )\n
              )\n
        <dtml-else>\n
          AND category.uid = stock.section_uid\n
          AND child.uid = stock.uid\n
          AND child.parent_uid = catalog.uid\n
          AND (\n
          <dtml-in node>\n
            <dtml-unless sequence-start>OR</dtml-unless>\n
               stock.node_uid =\n
                  <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
          </dtml-in> )\n
        </dtml-if>\n
      </dtml-if>\n
    \n
      <dtml-if stat>\n
        AND category.uid = stock.section_uid\n
        AND child.uid = stock.uid\n
        AND child.parent_uid = catalog.uid\n
        <dtml-if omit_input> AND stock.total_price < 0\n
                             AND stock.uid = movement.uid\n
        </dtml-if>\n
        <dtml-if omit_output> AND stock.total_price > 0\n
                              AND stock.uid = movement.uid\n
        </dtml-if>\n
      </dtml-if>\n
      <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
        AND ( 0 = 1\n
          <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
            OR catalog.uid = <dtml-var sequence-item>\n
          </dtml-in>\n
        )\n
      </dtml-if>\n
    \n
    <dtml-unless count>\n
      <dtml-if "query[\'order_by_expression\']">\n
        ORDER BY <dtml-var "query[\'order_by_expression\']">\n
      </dtml-if>\n
      <dtml-if "query[\'limit_expression\']">\n
        LIMIT <dtml-var "query[\'limit_expression\']">\n
      <dtml-else>\n
        <dtml-unless no_limit>LIMIT 1000</dtml-unless>\n
      </dtml-if>\n
    </dtml-unless>\n
\n
</dtml-if>\n
\n
\n
\n
\n
<dtml-comment>\n
#################################################\n
#                                               #\n
#   If entity or section_category IS EXISTS     #\n
#                                               #\n
#################################################\n
</dtml-comment>\n
<dtml-if "entity or section_category">\n
\n
        <dtml-if stat>\n
          SELECT SUM(total_price) AS total_price FROM (\n
          SELECT DISTINCT total_price, uid FROM (\n
        <dtml-elif count>\n
          SELECT COUNT(DISTINCT uid) AS count FROM (\n
        <dtml-else>\n
          SELECT main.*,\n
          ( SELECT title FROM catalog as mirror_catalog WHERE mirror_catalog.uid=main.delivery_mirror_section_uid ) AS delivery_mirror_section_title,\n
          ( SELECT IFNULL(SUM(stock.total_price), 0) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = main.uid AND stock.section_uid=main.category_uid AND stock.total_price > 0 AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>) AS debit,\n
          ( SELECT IFNULL(-SUM(stock.total_price), 0) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = main.uid AND stock.section_uid=main.category_uid AND stock.total_price < 0 AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>) AS credit\n
          FROM (\n
           SELECT *\n
           FROM (\n
        </dtml-if>\n
\n
<dtml-comment>\n
         ##################\n
         #                #\n
         # Source Section #\n
         #                #\n
         ##################\n
</dtml-comment>\n
\n
        (\n
            SELECT\n
          <dtml-if stat>\n
            stock.total_price, catalog.uid\n
          <dtml-elif count>\n
            catalog.uid\n
          <dtml-else>\n
            DISTINCT\n
            <dtml-in "search_result_keys">\n
              <dtml-unless sequence-start>,</dtml-unless>\n
              <dtml-var sequence-item>\n
            </dtml-in>\n
            , delivery.start_date as operation_date\n
            , delivery.destination_section_uid as delivery_mirror_section_uid\n
            , \'source\' as section_mark\n
            , catalog.source_reference as specific_reference\n
            <dtml-if "section_category">, category.uid as category_uid</dtml-if>\n
            <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
              <dtml-let order_column="query[\'order_by_expression\'].split(\' \')[0]">\n
                <dtml-if "not order_column in (\'operation_date\', \'specific_reference\')">\n
                  ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
                </dtml-if>\n
              </dtml-let>\n
            </dtml-if>\n
            <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
          </dtml-if>\n
        \n
        FROM\n
          <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
            <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
              <dtml-var table_item> AS <dtml-var table_key>,\n
            </dtml-if>\n
          </dtml-in>\n
          <dtml-if selection_domain>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
          </dtml-if>\n
          <dtml-if use_movement_table> movement,  </dtml-if>\n
          <dtml-if "stat or omit_input or omit_output or node">\n
              stock, catalog as child, </dtml-if>\n
          <dtml-if "section_category">category, catalog as section, </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">delivery AS mirror_section_delivery, catalog AS mirror_section_catalog,</dtml-if>\n
          catalog,\n
          delivery\n
        \n
        WHERE\n
          delivery.uid = catalog.uid\n
          <dtml-if "query[\'where_expression\']">\n
            AND <dtml-var "query[\'where_expression\']">\n
          </dtml-if>\n
          <dtml-if source_section_where_expression>\n
            AND <dtml-var source_section_where_expression>\n
          </dtml-if>\n
          <dtml-if selection_domain>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
          </dtml-if>\n
          <dtml-if specific_reference>\n
            AND catalog.source_reference LIKE <dtml-sqlvar specific_reference type="string">\n
          </dtml-if>\n
          <dtml-if transaction_uid>\n
            AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
          <dtml-if "amount or amount_range_min or amount_range_max">\n
            AND EXISTS\n
              (SELECT * FROM stock, catalog as child WHERE\n
               stock.uid=child.uid AND child.parent_uid=catalog.uid AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>\n
               <dtml-if amount>\n
                 AND <dtml-sqltest amount column="ABS(stock.total_price)" type=float op=eq>\n
               </dtml-if>\n
               <dtml-if amount_range_min>\n
                 AND <dtml-sqltest amount_range_min column="ABS(stock.total_price)" type=float op=ge>\n
               </dtml-if>\n
               <dtml-if amount_range_max>\n
                 AND <dtml-sqltest amount_range_max column="ABS(stock.total_price)" type=float op=lt>\n
               </dtml-if>\n
              )\n
          </dtml-if>\n
          <dtml-if creation_date_range_min>\n
            AND catalog.creation_date >=\n
                <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
          <dtml-if creation_date_range_max>\n
            AND catalog.creation_date <\n
                <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
          <dtml-if use_movement_table>\n
            AND movement.is_accountable\n
            AND movement.explanation_uid = catalog.uid </dtml-if>\n
          <dtml-if resource> AND movement.resource_uid =\n
              <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
          -- SOURCE SECTION!!!!!\n
          <dtml-if entity>\n
            AND delivery.destination_section_uid =<dtml-var "restrictedTraverse(entity).getUid()">\n
          </dtml-if>\n
          <dtml-if section_category>\n
            AND category.uid = delivery.source_section_uid\n
            AND section.portal_type = "Organisation"\n
            AND section.uid = category.uid\n
            AND category.category_uid =\n
            <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
          </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">\n
            AND mirror_section_catalog.uid = mirror_section_delivery.destination_section_uid\n
            AND mirror_section_delivery.uid = catalog.uid\n
            AND <dtml-sqltest delivery_mirror_section_title type=string op=like column=mirror_section_catalog.title>\n
          </dtml-if>\n
          \n
          <dtml-comment>\n
          -- FIXME: we want stop date when we are destination_section and start_date when\n
          --   we are source_section (one solution is to read in stock.date, but we try\n
          --   to avoid the use of stock table here)\n
          </dtml-comment>\n
          <dtml-if from_date>\n
            AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
          </dtml-if>\n
          <dtml-if to_date>\n
            AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
          </dtml-if>\n
          <dtml-if at_date>\n
            AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
          </dtml-if>\n
        \n
          <dtml-if node>\n
            <dtml-if stat>\n
              AND stock.uid IN (\n
                  SELECT child.uid from catalog as child\n
                   WHERE category.uid = stock.section_uid\n
                     AND child.parent_uid = catalog.uid\n
                  HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                           WHERE stock.uid = child.uid \n
                             AND child.parent_uid = catalog.uid\n
                             AND (\n
                            <dtml-in node>\n
                              <dtml-unless sequence-start>OR</dtml-unless>\n
                                 stock.node_uid =\n
                                    <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                            </dtml-in> )\n
                        )\n
                  )\n
            <dtml-else>\n
              AND category.uid = stock.section_uid\n
              AND child.uid = stock.uid\n
              AND child.parent_uid = catalog.uid\n
              AND (\n
              <dtml-in node>\n
                <dtml-unless sequence-start>OR</dtml-unless>\n
                   stock.node_uid =\n
                      <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
              </dtml-in> )\n
            </dtml-if>\n
          </dtml-if>\n
        \n
          <dtml-if stat>\n
            AND category.uid = stock.section_uid\n
            AND child.uid = stock.uid\n
            AND child.parent_uid = catalog.uid\n
            <dtml-if omit_input> AND stock.total_price < 0\n
                                 AND stock.uid = movement.uid\n
            </dtml-if>\n
            <dtml-if omit_output> AND stock.total_price > 0\n
                                  AND stock.uid = movement.uid\n
            </dtml-if>\n
          </dtml-if>\n
          <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
            AND ( 0 = 1\n
              <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
                OR catalog.uid = <dtml-var sequence-item>\n
              </dtml-in>\n
            )\n
          </dtml-if>\n
\n
        )\n
\n
\n
\n
UNION\n
\n
\n
\n
<dtml-comment>\n
         #######################\n
         #                     #\n
         # Destination Section #\n
         #                     #\n
         #######################\n
</dtml-comment>\n
\n
        (\n
            SELECT\n
          <dtml-if stat>\n
            stock.total_price, catalog.uid\n
          <dtml-elif count>\n
            catalog.uid\n
          <dtml-else>\n
            DISTINCT\n
            <dtml-in "search_result_keys">\n
              <dtml-unless sequence-start>,</dtml-unless>\n
              <dtml-var sequence-item>\n
            </dtml-in>\n
            , delivery.stop_date as operation_date\n
            , delivery.source_section_uid as delivery_mirror_section_uid\n
            , \'destination\' as section_mark\n
            , catalog.destination_reference as specific_reference\n
            <dtml-if "section_category">, category.uid as category_uid</dtml-if>\n
            <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
              <dtml-let order_column="query[\'order_by_expression\'].split(\' \')[0]">\n
                <dtml-if "not order_column in (\'operation_date\', \'specific_reference\')">\n
                  ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
                </dtml-if>\n
              </dtml-let>\n
            </dtml-if>\n
            <dtml-if select_expression><dtml-var select_expression> </dtml-if>\n
          </dtml-if>\n
        \n
        FROM\n
          <dtml-in prefix="table" expr="query[\'from_table_list\']">\n
            <dtml-if "table_key not in (\'delivery\', \'catalog\')">\n
              <dtml-var table_item> AS <dtml-var table_key>,\n
            </dtml-if>\n
          </dtml-in>\n
          <dtml-if selection_domain>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_domain)">,\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            <dtml-var "portal_selections.buildSQLJoinExpressionFromDomainSelection(selection_report)">,\n
          </dtml-if>\n
          <dtml-if use_movement_table> movement,  </dtml-if>\n
          <dtml-if "stat or omit_input or omit_output or node">\n
              stock, catalog as child, </dtml-if>\n
          <dtml-if "section_category">category, catalog as section, </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">delivery AS mirror_section_delivery, catalog AS mirror_section_catalog,</dtml-if>\n
          catalog,\n
          delivery\n
        \n
        WHERE\n
          delivery.uid = catalog.uid\n
          <dtml-if "query[\'where_expression\']">\n
            AND <dtml-var "query[\'where_expression\']">\n
          </dtml-if>\n
          <dtml-if destination_section_where_expression>\n
            AND <dtml-var destination_section_where_expression>\n
          </dtml-if>\n
          <dtml-if selection_domain>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_domain)">\n
          </dtml-if>\n
          <dtml-if selection_report>\n
            AND <dtml-var "portal_selections.buildSQLExpressionFromDomainSelection(selection_report, strict_membership=1)">\n
          </dtml-if>\n
          <dtml-if specific_reference>\n
            AND catalog.destination_reference LIKE <dtml-sqlvar specific_reference type="string">\n
          </dtml-if>\n
          <dtml-if transaction_uid>\n
            AND catalog.uid = <dtml-var transaction_uid> </dtml-if>\n
          <dtml-if "amount or amount_range_min or amount_range_max">\n
            AND EXISTS\n
              (SELECT * FROM stock, catalog as child WHERE\n
               stock.uid=child.uid AND child.parent_uid=catalog.uid AND <dtml-sqltest accounting_movement_list type=string column=child.portal_type op=eq multiple>\n
               <dtml-if amount>\n
                 AND <dtml-sqltest amount column="ABS(stock.total_price)" type=float op=eq>\n
               </dtml-if>\n
               <dtml-if amount_range_min>\n
                 AND <dtml-sqltest amount_range_min column="ABS(stock.total_price)" type=float op=ge>\n
               </dtml-if>\n
               <dtml-if amount_range_max>\n
                 AND <dtml-sqltest amount_range_max column="ABS(stock.total_price)" type=float op=lt>\n
               </dtml-if>\n
              )\n
          </dtml-if>\n
          <dtml-if creation_date_range_min>\n
            AND catalog.creation_date >=\n
                <dtml-sqlvar creation_date_range_min type="datetime"> </dtml-if>\n
          <dtml-if creation_date_range_max>\n
            AND catalog.creation_date <\n
                <dtml-sqlvar "creation_date_range_max+1" type="datetime"> </dtml-if>\n
          <dtml-if use_movement_table>\n
            AND movement.is_accountable\n
            AND movement.explanation_uid = catalog.uid </dtml-if>\n
          <dtml-if resource> AND movement.resource_uid =\n
              <dtml-var "restrictedTraverse(resource).getUid()"> </dtml-if>\n
          -- DESTINATION SECTION!!!!!\n
          <dtml-if entity>\n
            AND delivery.source_section_uid = <dtml-var "restrictedTraverse(entity).getUid()">\n
          </dtml-if>\n
          <dtml-if section_category>\n
            AND category.uid = delivery.destination_section_uid\n
            AND section.portal_type = "Organisation"\n
            AND section.uid = category.uid\n
            AND category.category_uid =\n
            <dtml-var "portal_categories.restrictedTraverse(section_category).getUid()">\n
          </dtml-if>\n
          <dtml-if "delivery_mirror_section_title">\n
            AND mirror_section_catalog.uid = mirror_section_delivery.source_section_uid\n
            AND mirror_section_delivery.uid = catalog.uid\n
            AND <dtml-sqltest delivery_mirror_section_title type=string op=like column=mirror_section_catalog.title>\n
          </dtml-if>\n
\n
          <dtml-comment>\n
          -- FIXME: we want stop date when we are destination_section and start_date when\n
          --   we are source_section (one solution is to read in stock.date, but we try\n
          --   to avoid the use of stock table here)\n
          </dtml-comment>\n
          <dtml-if from_date>\n
            AND movement.start_date >= <dtml-sqlvar from_date type="datetime">\n
          </dtml-if>\n
          <dtml-if to_date>\n
            AND movement.start_date <= <dtml-sqlvar expr="to_date+1" type="datetime">\n
          </dtml-if>\n
          <dtml-if at_date>\n
            AND movement.start_date < <dtml-sqlvar at_date type="datetime">\n
          </dtml-if>\n
        \n
          <dtml-if node>\n
            <dtml-if stat>\n
              AND stock.uid IN (\n
                  SELECT child.uid from catalog as child\n
                   WHERE category.uid = stock.section_uid\n
                     AND child.parent_uid = catalog.uid\n
                  HAVING (SELECT count(stock.uid) from stock, catalog as child\n
                           WHERE stock.uid = child.uid \n
                             AND child.parent_uid = catalog.uid\n
                             AND (\n
                            <dtml-in node>\n
                              <dtml-unless sequence-start>OR</dtml-unless>\n
                                 stock.node_uid =\n
                                    <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
                            </dtml-in> )\n
                        )\n
                  )\n
            <dtml-else>\n
              AND category.uid = stock.section_uid\n
              AND child.uid = stock.uid\n
              AND child.parent_uid = catalog.uid\n
              AND (\n
              <dtml-in node>\n
                <dtml-unless sequence-start>OR</dtml-unless>\n
                   stock.node_uid =\n
                      <dtml-var "restrictedTraverse(_[\'sequence-item\']).getUid()">\n
              </dtml-in> )\n
            </dtml-if>\n
          </dtml-if>\n
        \n
          <dtml-if stat>\n
            AND category.uid = stock.section_uid\n
            AND child.uid = stock.uid\n
            AND child.parent_uid = catalog.uid\n
            <dtml-if omit_input> AND stock.total_price < 0\n
                                 AND stock.uid = movement.uid\n
            </dtml-if>\n
            <dtml-if omit_output> AND stock.total_price > 0\n
                                  AND stock.uid = movement.uid\n
            </dtml-if>\n
          </dtml-if>\n
          <dtml-if expr="selection_name is not None and portal_selections.getSelectionInvertModeFor(selection_name) and len(portal_selections.getSelectionInvertModeUidListFor(selection_name)) > 0">\n
            AND ( 0 = 1\n
              <dtml-in expr="portal_selections.getSelectionInvertModeUidListFor(selection_name)">\n
                OR catalog.uid = <dtml-var sequence-item>\n
              </dtml-in>\n
            )\n
          </dtml-if>\n
        )\n
\n
\n
<dtml-comment>\n
         ##################\n
         #                #\n
         #  ORDER, LIMIT  #\n
         #                #\n
         ##################\n
</dtml-comment>\n
\n
\n
        <dtml-if "count">\n
        ) as catalog\n
        <dtml-elif "stat">\n
        ) as sb\n
        ) as catalog\n
        <dtml-else>\n
        ) as catalog\n
        </dtml-if>\n
\n
        <dtml-if "not (stat or count)">\n
            GROUP BY uid\n
          <dtml-if "query[\'order_by_expression\']">\n
            ORDER BY <dtml-var "query[\'order_by_expression\'].split(\'.\')[-1]">\n
          </dtml-if>\n
          <dtml-if "query[\'limit_expression\']">\n
            LIMIT <dtml-var "query[\'limit_expression\']">\n
          <dtml-else>\n
            <dtml-unless no_limit>LIMIT 1000</dtml-unless>\n
          </dtml-if>\n
        ) as main\n
        </dtml-if>\n
\n
</dtml-if>\n
\n
\n
\n
</dtml-let></dtml-let></dtml-let></dtml-let>\n
\n
<dtml-comment> vim: syntax=dtml\n
</dtml-comment>\n


]]></string> </value>
                    </item>
                  </dictionary>
                </state>
              </object>
            </value>
        </item>
        <item>
            <key> <string>title</string> </key>
            <value> <string></string> </value>
        </item>
      </dictionary>
    </pickle>
  </record>
</ZopeData>
