[Erp5-report] r19026 - in /erp5/trunk/bt5/erp5_accounting: SkinTemplateItem/portal_skins/er...
nobody at svn.erp5.org
nobody at svn.erp5.org
Mon Feb 4 14:45:30 CET 2008
Author: yusei
Date: Mon Feb 4 14:45:29 2008
New Revision: 19026
URL: http://svn.erp5.org?rev=19026&view=rev
Log:
2008-2-4 yusei
* Optimize the query of accounting module list view.
Modified:
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml
erp5/trunk/bt5/erp5_accounting/bt/change_log
erp5/trunk/bt5/erp5_accounting/bt/revision
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml?rev=19026&r1=19025&r2=19026&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml Mon Feb 4 14:45:29 2008
@@ -300,32 +300,32 @@
<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>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>
</list>
</value>
</item>
@@ -411,167 +411,560 @@
<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
\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
- <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
- 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
+\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
+ <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
+ 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-if>\n
+\n
+ <dtml-if count>\n
+ SELECT COUNT(DISTINCT uid) AS count FROM (\n
+ </dtml-if>\n
+\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
+ <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
+ ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\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
+ 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>\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>\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
+ -- SOURCE 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.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
- 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
+ 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
+ <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
+ ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
+ </dtml-if>\n
+\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
+ 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>\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>\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
+ -- DESTINATION 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.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
+ \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-if>\n
+\n
+ <dtml-if "stat">\n
+ ) as sb\n
+ ) as catalog\n
+ </dtml-if>\n
+\n
+ <dtml-if "not (stat or count)">\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
+ </dtml-if>\n
+\n
+</dtml-if>\n
+\n
+\n
+\n
</dtml-let></dtml-let></dtml-let>\n
+\n
<dtml-comment> vim: syntax=dtml\n
</dtml-comment>\n
@@ -618,167 +1011,560 @@
<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
\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
- <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
- 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
+\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
+ <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
+ 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-if>\n
+\n
+ <dtml-if count>\n
+ SELECT COUNT(DISTINCT uid) AS count FROM (\n
+ </dtml-if>\n
+\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
+ <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
+ ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\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
+ 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>\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>\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
+ -- SOURCE 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.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
- 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
+ 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
+ <dtml-if "not (stat or count) and query[\'order_by_expression\']">\n
+ ,<dtml-var "query[\'order_by_expression\'].split(\' \')[0]">\n
+ </dtml-if>\n
+\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
+ 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>\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>\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
+ -- DESTINATION 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.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
+ \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-if>\n
+\n
+ <dtml-if "stat">\n
+ ) as sb\n
+ ) as catalog\n
+ </dtml-if>\n
+\n
+ <dtml-if "not (stat or count)">\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
+ </dtml-if>\n
+\n
+</dtml-if>\n
+\n
+\n
+\n
</dtml-let></dtml-let></dtml-let>\n
+\n
<dtml-comment> vim: syntax=dtml\n
</dtml-comment>\n
Modified: erp5/trunk/bt5/erp5_accounting/bt/change_log
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/bt/change_log?rev=19026&r1=19025&r2=19026&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/bt/change_log (original)
+++ erp5/trunk/bt5/erp5_accounting/bt/change_log Mon Feb 4 14:45:29 2008
@@ -1,3 +1,6 @@
+2008-2-4 yusei
+* Optimize the query of accounting module list view.
+
2008-1-28 yusei
* Show translated id in accounting transaction line listbox.
Modified: erp5/trunk/bt5/erp5_accounting/bt/revision
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/bt/revision?rev=19026&r1=19025&r2=19026&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/bt/revision (original)
+++ erp5/trunk/bt5/erp5_accounting/bt/revision Mon Feb 4 14:45:29 2008
@@ -1,1 +1,1 @@
-562
+563
More information about the Erp5-report
mailing list