[Erp5-report] r19107 - in /erp5/trunk/bt5/erp5_accounting: SkinTemplateItem/portal_skins/er...
nobody at svn.erp5.org
nobody at svn.erp5.org
Wed Feb 6 16:48:22 CET 2008
Author: yusei
Date: Wed Feb 6 16:48:22 2008
New Revision: 19107
URL: http://svn.erp5.org?rev=19107&view=rev
Log:
Optimize query for calculation of debit and credit.
Fix sorting bug of operation accounting date column.
Modified:
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_countAccountingTransactionList.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_getAccountingTransactionList.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceCredit.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceDebit.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_credit.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_debit.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_operation_date.xml
erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_zGetAccountingTransactionList.xml
erp5/trunk/bt5/erp5_accounting/bt/revision
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_countAccountingTransactionList.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_countAccountingTransactionList.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_countAccountingTransactionList.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_countAccountingTransactionList.xml Wed Feb 6 16:48:22 2008
@@ -71,8 +71,18 @@
kw[\'omit_input\'] = 0\n
kw[\'omit_output\'] = 0\n
\n
-if kw.get(\'delivery_start_date\'):\n
- kw[\'delivery.start_date\'] = kw[\'delivery_start_date\']\n
+from Products.ZSQLCatalog.SQLCatalog import Query\n
+if kw.get(\'operation_date\', {}).get(\'query\'):\n
+ operation_date_dict = kw[\'operation_date\']\n
+ ss_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.start_date\':operation_date_dict[\'query\']})\n
+ kw[\'source_section_where_expression\'] = ss_q()[\'where_expression\']\n
+ ds_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.stop_date\':operation_date_dict[\'query\']})\n
+ kw[\'destination_section_where_expression\'] = ds_q()[\'where_expression\']\n
+ del kw[\'operation_date\']\n
\n
return context.AccountingTransactionModule_zGetAccountingTransactionList( selection=None,\n
selection_params=kw,\n
@@ -126,8 +136,13 @@
<string>kw</string>
<string>_getattr_</string>
<string>_write_</string>
+ <string>Products.ZSQLCatalog.SQLCatalog</string>
+ <string>Query</string>
<string>_getitem_</string>
+ <string>operation_date_dict</string>
<string>_apply_</string>
+ <string>ss_q</string>
+ <string>ds_q</string>
<string>context</string>
<string>None</string>
</tuple>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_getAccountingTransactionList.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_getAccountingTransactionList.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_getAccountingTransactionList.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_getAccountingTransactionList.xml Wed Feb 6 16:48:22 2008
@@ -70,6 +70,9 @@
# XXX workarounds for DTML limitation\n
# Because DTML cannot do <dtml-if node or resource or...>\n
# If Python is used, it does not check the existence of a variable automatically\n
+\n
+from Products.ZSQLCatalog.SQLCatalog import Query\n
+\n
if \'node\' not in params:\n
params[\'node\'] = []\n
if \'resource\' not in params:\n
@@ -88,9 +91,17 @@
params[\'omit_input\'] = 0\n
params[\'omit_output\'] = 0\n
\n
-# XXX this workaround is probably no longer needed\n
-if params.get(\'delivery_start_date\'):\n
- params[\'delivery.start_date\'] = params[\'delivery_start_date\']\n
+if params.get(\'operation_date\', {}).get(\'query\'):\n
+ operation_date_dict = params[\'operation_date\']\n
+ ss_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.start_date\':operation_date_dict[\'query\']})\n
+ params[\'source_section_where_expression\'] = ss_q()[\'where_expression\']\n
+ ds_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.stop_date\':operation_date_dict[\'query\']})\n
+ params[\'destination_section_where_expression\'] = ds_q()[\'where_expression\']\n
+ del params[\'operation_date\']\n
\n
# this method can be used to build another complex query, for example\n
# as a catalog.parent_uid filter with SimulationTool.getMovementList.\n
@@ -150,15 +161,20 @@
<value>
<tuple>
<string>params</string>
+ <string>Products.ZSQLCatalog.SQLCatalog</string>
+ <string>Query</string>
<string>_write_</string>
<string>_getattr_</string>
<string>_getitem_</string>
+ <string>operation_date_dict</string>
+ <string>_apply_</string>
+ <string>ss_q</string>
+ <string>ds_q</string>
<string>context</string>
<string>src__</string>
<string>no_limit</string>
<string>None</string>
<string>search_result_keys</string>
- <string>_apply_</string>
</tuple>
</value>
</item>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceCredit.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceCredit.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceCredit.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceCredit.xml Wed Feb 6 16:48:22 2008
@@ -70,8 +70,20 @@
params[\'stat\'] = 1\n
params[\'omit_input\'] = 1\n
params[\'omit_output\'] = 0\n
-if params.get(\'delivery_start_date\'):\n
- params[\'delivery.start_date\'] = params[\'delivery_start_date\']\n
+\n
+from Products.ZSQLCatalog.SQLCatalog import Query\n
+\n
+if params.get(\'operation_date\', {}).get(\'query\'):\n
+ operation_date_dict = params[\'operation_date\']\n
+ ss_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.start_date\':operation_date_dict[\'query\']})\n
+ params[\'source_section_where_expression\'] = ss_q()[\'where_expression\']\n
+ ds_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.stop_date\':operation_date_dict[\'query\']})\n
+ params[\'destination_section_where_expression\'] = ds_q()[\'where_expression\']\n
+ del params[\'operation_date\']\n
\n
result = context.AccountingTransactionModule_zGetAccountingTransactionList(\n
selection=selection,\n
@@ -128,8 +140,13 @@
<string>context</string>
<string>params</string>
<string>_write_</string>
+ <string>Products.ZSQLCatalog.SQLCatalog</string>
+ <string>Query</string>
<string>_getitem_</string>
+ <string>operation_date_dict</string>
<string>_apply_</string>
+ <string>ss_q</string>
+ <string>ds_q</string>
<string>result</string>
<string>row</string>
<string>float</string>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceDebit.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceDebit.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceDebit.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_statSourceDebit.xml Wed Feb 6 16:48:22 2008
@@ -70,8 +70,20 @@
params[\'stat\'] = 1\n
params[\'omit_output\'] = 1\n
params[\'omit_input\'] = 0\n
-if params.get(\'delivery_start_date\'):\n
- params[\'delivery.start_date\'] = params[\'delivery_start_date\']\n
+\n
+from Products.ZSQLCatalog.SQLCatalog import Query\n
+\n
+if params.get(\'operation_date\', {}).get(\'query\'):\n
+ operation_date_dict = params[\'operation_date\']\n
+ ss_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.start_date\':operation_date_dict[\'query\']})\n
+ params[\'source_section_where_expression\'] = ss_q()[\'where_expression\']\n
+ ds_q = Query(format=operation_date_dict[\'format\'],\n
+ type=operation_date_dict[\'type\'],\n
+ **{\'delivery.stop_date\':operation_date_dict[\'query\']})\n
+ params[\'destination_section_where_expression\'] = ds_q()[\'where_expression\']\n
+ del params[\'operation_date\']\n
\n
result = context.AccountingTransactionModule_zGetAccountingTransactionList(\n
selection=selection, selection_params = params, **params)\n
@@ -127,8 +139,13 @@
<string>context</string>
<string>params</string>
<string>_write_</string>
+ <string>Products.ZSQLCatalog.SQLCatalog</string>
+ <string>Query</string>
<string>_getitem_</string>
+ <string>operation_date_dict</string>
<string>_apply_</string>
+ <string>ss_q</string>
+ <string>ds_q</string>
<string>result</string>
<string>row</string>
<string>float</string>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_credit.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_credit.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_credit.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_credit.xml Wed Feb 6 16:48:22 2008
@@ -10,12 +10,6 @@
<pickle>
<dictionary>
<item>
- <key> <string>_owner</string> </key>
- <value>
- <none/>
- </value>
- </item>
- <item>
<key> <string>id</string> </key>
<value> <string>listbox_credit</string> </value>
</item>
@@ -261,18 +255,15 @@
<record id="2" aka="AAAAAAAAAAI=">
<pickle>
<tuple>
- <tuple>
- <string>Products.Formulator.TALESField</string>
- <string>TALESMethod</string>
- </tuple>
- <none/>
+ <global name="TALESMethod" module="Products.Formulator.TALESField"/>
+ <tuple/>
</tuple>
</pickle>
<pickle>
<dictionary>
<item>
<key> <string>_text</string> </key>
- <value> <string>cell/AccountingTransactionModule_getSourceCredit</string> </value>
+ <value> <string>cell/credit|cell/AccountingTransactionModule_getSourceCredit</string> </value>
</item>
</dictionary>
</pickle>
@@ -280,11 +271,8 @@
<record id="3" aka="AAAAAAAAAAM=">
<pickle>
<tuple>
- <tuple>
- <string>Products.Formulator.TALESField</string>
- <string>TALESMethod</string>
- </tuple>
- <none/>
+ <global name="TALESMethod" module="Products.Formulator.TALESField"/>
+ <tuple/>
</tuple>
</pickle>
<pickle>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_debit.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_debit.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_debit.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_debit.xml Wed Feb 6 16:48:22 2008
@@ -10,12 +10,6 @@
<pickle>
<dictionary>
<item>
- <key> <string>_owner</string> </key>
- <value>
- <none/>
- </value>
- </item>
- <item>
<key> <string>id</string> </key>
<value> <string>listbox_debit</string> </value>
</item>
@@ -261,18 +255,15 @@
<record id="2" aka="AAAAAAAAAAI=">
<pickle>
<tuple>
- <tuple>
- <string>Products.Formulator.TALESField</string>
- <string>TALESMethod</string>
- </tuple>
- <none/>
+ <global name="TALESMethod" module="Products.Formulator.TALESField"/>
+ <tuple/>
</tuple>
</pickle>
<pickle>
<dictionary>
<item>
<key> <string>_text</string> </key>
- <value> <string>cell/AccountingTransactionModule_getSourceDebit</string> </value>
+ <value> <string>cell/debit|cell/AccountingTransactionModule_getSourceDebit</string> </value>
</item>
</dictionary>
</pickle>
@@ -280,11 +271,8 @@
<record id="3" aka="AAAAAAAAAAM=">
<pickle>
<tuple>
- <tuple>
- <string>Products.Formulator.TALESField</string>
- <string>TALESMethod</string>
- </tuple>
- <none/>
+ <global name="TALESMethod" module="Products.Formulator.TALESField"/>
+ <tuple/>
</tuple>
</pickle>
<pickle>
Modified: erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_operation_date.xml
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_operation_date.xml?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_operation_date.xml (original)
+++ erp5/trunk/bt5/erp5_accounting/SkinTemplateItem/portal_skins/erp5_accounting/AccountingTransactionModule_viewAccountingTransactionList/listbox_operation_date.xml Wed Feb 6 16:48:22 2008
@@ -9,12 +9,6 @@
</pickle>
<pickle>
<dictionary>
- <item>
- <key> <string>_owner</string> </key>
- <value>
- <none/>
- </value>
- </item>
<item>
<key> <string>id</string> </key>
<value> <string>listbox_operation_date</string> </value>
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=19107&r1=19106&r2=19107&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 Wed Feb 6 16:48:22 2008
@@ -126,6 +126,12 @@
</value>
</item>
<item>
+ <key> <string>destination_section_where_expression</string> </key>
+ <value>
+ <dictionary/>
+ </value>
+ </item>
+ <item>
<key> <string>entity</string> </key>
<value>
<dictionary>
@@ -261,6 +267,12 @@
</item>
<item>
<key> <string>selection_report</string> </key>
+ <value>
+ <dictionary/>
+ </value>
+ </item>
+ <item>
+ <key> <string>source_section_where_expression</string> </key>
<value>
<dictionary/>
</value>
@@ -338,6 +350,8 @@
<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>
@@ -380,7 +394,9 @@
specific_reference\r\n
no_limit\r\n
search_result_keys=\'\'\r\n
-delivery_mirror_section_title=""</string> </value>
+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>
@@ -618,9 +634,13 @@
<dtml-elif count>\n
SELECT COUNT(DISTINCT uid) AS count FROM (\n
<dtml-else>\n
- SELECT * from (\n
+ SELECT catalog.*,\n
+ <dtml-let accounting_movement_list="portal_url.getPortalObject().getPortalAccountingMovementTypeList()">\n
+ ( SELECT SUM(stock.total_price) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = catalog.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 -SUM(stock.total_price) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = catalog.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
+ </dtml-let>\n
</dtml-if>\n
-\n
\n
<dtml-comment>\n
##################\n
@@ -680,6 +700,9 @@
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
@@ -859,6 +882,9 @@
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
@@ -1250,9 +1276,13 @@
<dtml-elif count>\n
SELECT COUNT(DISTINCT uid) AS count FROM (\n
<dtml-else>\n
- SELECT * from (\n
+ SELECT catalog.*,\n
+ <dtml-let accounting_movement_list="portal_url.getPortalObject().getPortalAccountingMovementTypeList()">\n
+ ( SELECT SUM(stock.total_price) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = catalog.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 -SUM(stock.total_price) FROM stock, catalog AS child WHERE child.uid = stock.uid AND child.parent_uid = catalog.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
+ </dtml-let>\n
</dtml-if>\n
-\n
\n
<dtml-comment>\n
##################\n
@@ -1312,6 +1342,9 @@
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
@@ -1492,6 +1525,9 @@
<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
Modified: erp5/trunk/bt5/erp5_accounting/bt/revision
URL: http://svn.erp5.org/erp5/trunk/bt5/erp5_accounting/bt/revision?rev=19107&r1=19106&r2=19107&view=diff
==============================================================================
--- erp5/trunk/bt5/erp5_accounting/bt/revision (original)
+++ erp5/trunk/bt5/erp5_accounting/bt/revision Wed Feb 6 16:48:22 2008
@@ -1,1 +1,1 @@
-585
+588
More information about the Erp5-report
mailing list