Accounts Payable report improved: some query written outside the for-loop
Supplier: supplier_status field has been deleted, not used by anyone except janak
index patch modified
diff --git a/accounts/search_criteria/accounts_payable/accounts_payable.py b/accounts/search_criteria/accounts_payable/accounts_payable.py
index 53fff08..8c07b68 100644
--- a/accounts/search_criteria/accounts_payable/accounts_payable.py
+++ b/accounts/search_criteria/accounts_payable/accounts_payable.py
@@ -2,134 +2,145 @@
# ------------------------------------------------------------------
if not filter_values.get('posting_date') or not filter_values.get('posting_date1'):
- msgprint("Please select From Posting Date and To Posting Date ")
- raise Exception
+ msgprint("Please select From Posting Date and To Posting Date ")
+ raise Exception
else:
- from_date = filter_values.get('posting_date')
- to_date = filter_values.get('posting_date1')
+ from_date = filter_values.get('posting_date')
+ to_date = filter_values.get('posting_date1')
if not filter_values['range_1'] or not filter_values['range_2'] or not filter_values['range_3'] or not filter_values['range_4']:
- msgprint("Please select aging ranges in no of days in 'More Filters' ")
- raise Exception
+ msgprint("Please select aging ranges in no of days in 'More Filters' ")
+ raise Exception
# validate Range
range_list = ['range_1','range_2','range_3','range_4']
for r in range(len(range_list)-1):
- if not cint(filter_values[range_list[r]]) < cint(filter_values[range_list[r + 1]]):
- msgprint("Range %s should be less than Range %s." % (cstr(r+1),cstr(r+2)))
- raise Exception
+ if not cint(filter_values[range_list[r]]) < cint(filter_values[range_list[r + 1]]):
+ msgprint("Range %s should be less than Range %s." % (cstr(r+1),cstr(r+2)))
+ raise Exception
-
+
# Add columns
# ------------------------------------------------------------------
data = [['Aging Date','Date','80px',''],
- ['Transaction Date','Date','80px',''],
- ['Account','Data','120px',''],
- ['Against Voucher Type','Data','120px',''],
- ['Against Voucher','Data','120px',''],
- ['Voucher Type','Data','120px',''],
- ['Voucher No','Data','120px',''],
- ['Remarks','Data','160px',''],
- ['Supplier Type', 'Data', '80px', ''],
- ['Due Date', 'Data', '80px', ''],
- ['Bill No','Data','80px',''],
- ['Bill Date','Data','80px',''],
- ['Opening Amt','Currency','120px',''],
- ['Outstanding Amt','Currency','120px',''],
- ['Age (Days)', 'Currency', '150px', ''],
- ['0-'+cstr(filter_values['range_1']),'Currency','100px',''],
- [cstr(cint(filter_values['range_1']) + 1)+ '-' +cstr(filter_values['range_2']),'Currency','100px',''],
- [cstr(cint(filter_values['range_2']) + 1)+ '-' +cstr(filter_values['range_3']),'Currency','100px',''],
- [cstr(cint(filter_values['range_3']) + 1)+ '-' +cstr(filter_values['range_4']),'Currency','100px',''],
- [cstr(filter_values['range_4']) + '-Above','Currency','100px','']]
-
+ ['Transaction Date','Date','80px',''],
+ ['Account','Data','120px',''],
+ ['Against Voucher Type','Data','120px',''],
+ ['Against Voucher','Data','120px',''],
+ ['Voucher Type','Data','120px',''],
+ ['Voucher No','Data','120px',''],
+ ['Remarks','Data','160px',''],
+ ['Supplier Type', 'Data', '80px', ''],
+ ['Due Date', 'Data', '80px', ''],
+ ['Bill No','Data','80px',''],
+ ['Bill Date','Data','80px',''],
+ ['Opening Amt','Currency','120px',''],
+ ['Outstanding Amt','Currency','120px',''],
+ ['Age (Days)', 'Currency', '150px', ''],
+ ['0-'+cstr(filter_values['range_1']),'Currency','100px',''],
+ [cstr(cint(filter_values['range_1']) + 1)+ '-' +cstr(filter_values['range_2']),'Currency','100px',''],
+ [cstr(cint(filter_values['range_2']) + 1)+ '-' +cstr(filter_values['range_3']),'Currency','100px',''],
+ [cstr(cint(filter_values['range_3']) + 1)+ '-' +cstr(filter_values['range_4']),'Currency','100px',''],
+ [cstr(filter_values['range_4']) + '-Above','Currency','100px','']]
+
for d in data:
- colnames.append(d[0])
- coltypes.append(d[1])
- colwidths.append(d[2])
- coloptions.append(d[3])
- col_idx[d[0]] = len(colnames)-1
-
+ colnames.append(d[0])
+ coltypes.append(d[1])
+ colwidths.append(d[2])
+ coloptions.append(d[3])
+ col_idx[d[0]] = len(colnames)-1
+
# ageing based on
# ------------------------------------------------------------------
aging_based_on = 'Aging Date'
if filter_values.has_key('aging_based_on') and filter_values['aging_based_on']:
- aging_based_on = filter_values['aging_based_on'].split(NEWLINE)[-1]
+ aging_based_on = filter_values['aging_based_on'].split(NEWLINE)[-1]
-if len(res) > 600 and from_export == 0:
- msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
- raise Exception
+if len(res) > 600 and from_export == 0:
+ msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
+ raise Exception
# ------------------------------------------------------------------
# main loop starts here
# ------------------------------------------------------------------
+# get supplier type
+supp_type_dict = {}
+for each in sql("select t2.name, t1.supplier_type from tabSupplier t1, tabAccount t2 where t1.name = t2.account_name group by t2.name"):
+ supp_type_dict[each[0]] = each[1]
+
+# get due_date, bill_no, bill_date from PV
+pv_dict = {}
+for t in sql("select name, due_date, bill_no, bill_date from `tabPayable Voucher` group by name"):
+ pv_dict[t[0]] = [cstr(t[1]), t[2], cstr(t[3])]
+
+# pv outside this period
+pv_outside_period = [d[0] for d in sql("select distinct name from `tabPayable Voucher` where (posting_date < '%s' or posting_date > '%s') and docstatus = 1" % (from_date, to_date))]
+
+
out = []
total_booking_amt, total_outstanding_amt = 0,0
for r in res:
- # get supplier type
- supplier_type = sql("select t1.supplier_type from tabSupplier t1, tabAccount t2 where t1.name = t2.account_name and t2.name = '%s'" % r[col_idx['Account']])
- r.append(supplier_type and cstr(supplier_type[0][0]) or '')
+ outstanding_amt, due_date, bill_no, bill_date, cond = 0, '','','', ''
+ booking_amt = r.pop(8)
+
+ # supplier type
+ r.append(supp_type_dict.get(r[col_idx['Account']], ''))
+
+ # if entry against Payable Voucher
+ if r[col_idx['Against Voucher']] and r[col_idx['Voucher Type']] == 'Payable Voucher':
+ r += pv_dict.get(r[col_idx['Voucher No']], ['', '', ''])
+ cond = " and ifnull(against_voucher, '') = '%s'" % r[col_idx['Against Voucher']]
- outstanding_amt, booking_amt, due_date, bill_no, bill_date, cond = 0,0, '','','', ''
+ # if entry against JV & and not adjusted within period
+ elif r[col_idx['Against Voucher Type']] == 'Payable Voucher' and r[col_idx['Against Voucher']] in pv_outside_period:
+ booking_amt = 0
+ cond = " and voucher_no = '%s' and ifnull(against_voucher, '') = '%s'" % (r[col_idx['Voucher No']], r[col_idx['Against Voucher']])
+
+ # if un-adjusted
+ elif not r[col_idx['Against Voucher']]:
+ booking_amt = 0
+ cond = " and ((voucher_no = '%s' and ifnull(against_voucher, '') = '') or (ifnull(against_voucher, '') = '%s' and voucher_type = 'Journal Voucher'))" % (r[col_idx['Voucher No']], r[col_idx['Voucher No']])
- # if entry against Payable Voucher
- if r[col_idx['Against Voucher']] and r[col_idx['Voucher Type']] == 'Payable Voucher':
- due_date, bill_no, bill_date = [cstr(t) for t in sql("select due_date,bill_no,bill_date from `tabPayable Voucher` where name = %s", r[col_idx['Voucher No']])[0]]
+ if cond:
+ outstanding_amt = flt(sql("select sum(ifnull(credit, 0))-sum(ifnull(debit, 0)) from `tabGL Entry` where account = '%s' and ifnull(is_cancelled, 'No') = 'No' and posting_date <= '%s' %s" % (r[col_idx['Account']], to_date, cond))[0][0] or 0)
- # get opening
- booking_amt = sql("select credit from `tabGL Entry` where account = %s and voucher_no = %s and is_cancelled = 'No'", (r[col_idx['Account']], r[col_idx['Voucher No']]))
- booking_amt = booking_amt and flt(booking_amt[0][0]) or 0
+ # add to total outstanding
+ total_outstanding_amt += flt(outstanding_amt)
- cond = " and against_voucher = '%s' and against_voucher is not null" % r[col_idx['Against Voucher']]
+ # add to total booking amount
+ if outstanding_amt and r[col_idx['Voucher Type']] == 'Payable Voucher' and r[col_idx['Against Voucher']]:
+ total_booking_amt += flt(booking_amt)
- # if entry against JV & and not adjusted within period
- elif r[col_idx['Against Voucher Type']] == 'Payable Voucher' and sql("select name from `tabPayable Voucher` where name = '%s' and (posting_date < '%s' or posting_date > '%s') and docstatus = 1" % (r[col_idx['Against Voucher']], from_date, to_date)):
- cond = " and voucher_no = '%s' and ifnull(against_voucher, '') = '%s'" % (r[col_idx['Voucher No']], r[col_idx['Against Voucher']])
-
- # if un-adjusted
- elif not r[col_idx['Against Voucher']]:
- cond = " and ((voucher_no = '%s' and ifnull(against_voucher, '') = '') or (ifnull(against_voucher, '') = '%s' and voucher_type = 'Journal Voucher'))" % (r[col_idx['Voucher No']], r[col_idx['Voucher No']])
+ r += [booking_amt, outstanding_amt]
+
+ # split into date ranges
+ val_l1 = val_l2 = val_l3 = val_l4 = val_l5_above= 0
+ if r[col_idx[aging_based_on]]:
+ diff = (getdate(to_date) - getdate(r[col_idx[aging_based_on]])).days
+ if diff < cint(filter_values['range_1']):
+ val_l1 = outstanding_amt
+ if diff >= cint(filter_values['range_1']) and diff < cint(filter_values['range_2']):
+ val_l2 = outstanding_amt
+ if diff >= cint(filter_values['range_2']) and diff < cint(filter_values['range_3']):
+ val_l3 = outstanding_amt
+ if diff >= cint(filter_values['range_3']) and diff < cint(filter_values['range_4']):
+ val_l4 = outstanding_amt
+ if diff >= cint(filter_values['range_4']):
+ val_l5_above = outstanding_amt
- if cond:
- outstanding_amt = flt(sql("select sum(ifnull(credit, 0))-sum(ifnull(debit, 0)) from `tabGL Entry` where account = '%s' and ifnull(is_cancelled, 'No') = 'No' and posting_date <= '%s' %s" % (r[col_idx['Account']], to_date, cond))[0][0] or 0)
+ r += [diff, val_l1, val_l2, val_l3, val_l4, val_l5_above]
- # add to total outstanding
- total_outstanding_amt += flt(outstanding_amt)
-
- # add to total booking amount
- if outstanding_amt and r[col_idx['Voucher Type']] == 'Payable Voucher' and r[col_idx['Against Voucher']]:
- total_booking_amt += flt(booking_amt)
-
- r += [due_date, bill_no, bill_date, booking_amt, outstanding_amt]
-
- # split into date ranges
- val_l1 = val_l2 = val_l3 = val_l4 = val_l5_above= 0
- if r[col_idx[aging_based_on]]:
- diff = (getdate(to_date) - getdate(r[col_idx[aging_based_on]])).days
- if diff < cint(filter_values['range_1']):
- val_l1 = outstanding_amt
- if diff >= cint(filter_values['range_1']) and diff < cint(filter_values['range_2']):
- val_l2 = outstanding_amt
- if diff >= cint(filter_values['range_2']) and diff < cint(filter_values['range_3']):
- val_l3 = outstanding_amt
- if diff >= cint(filter_values['range_3']) and diff < cint(filter_values['range_4']):
- val_l4 = outstanding_amt
- if diff >= cint(filter_values['range_4']):
- val_l5_above = outstanding_amt
-
- r += [diff, val_l1, val_l2, val_l3, val_l4, val_l5_above]
-
- # Only show that entry which has outstanding
- if abs(flt(outstanding_amt)) > 0.001:
- out.append(r)
-
-if len(out) > 300 and from_export == 0:
- msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
- raise Exception
+ # Only show that entry which has outstanding
+ if abs(flt(outstanding_amt)) > 0.001:
+ out.append(r)
+
+if len(out) > 300 and from_export == 0:
+ msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
+ raise Exception
# Append Extra rows to RES
diff --git a/accounts/search_criteria/accounts_payable/accounts_payable.sql b/accounts/search_criteria/accounts_payable/accounts_payable.sql
index d2dba06..2054b1e 100644
--- a/accounts/search_criteria/accounts_payable/accounts_payable.sql
+++ b/accounts/search_criteria/accounts_payable/accounts_payable.sql
@@ -1,4 +1,4 @@
-SELECT DISTINCT `tabGL Entry`.`Aging_date`,`tabGL Entry`.`transaction_date`,`tabGL Entry`.`account`, `tabGL Entry`.`against_voucher_type`, `tabGL Entry`.`against_voucher`,`tabGL Entry`.`voucher_type`,`tabGL Entry`.`voucher_no`, `tabGL Entry`.remarks
+SELECT DISTINCT `tabGL Entry`.`Aging_date`,`tabGL Entry`.`transaction_date`,`tabGL Entry`.`account`, `tabGL Entry`.`against_voucher_type`, `tabGL Entry`.`against_voucher`,`tabGL Entry`.`voucher_type`,`tabGL Entry`.`voucher_no`, `tabGL Entry`.`remarks`, `tabGL Entry`.`credit`
FROM `tabGL Entry`,`tabAccount`
WHERE `tabGL Entry`.`posting_date`>= '%(posting_date)s'
AND `tabGL Entry`.`posting_date`<= '%(posting_date1)s'
diff --git a/buying/doctype/supplier/supplier.txt b/buying/doctype/supplier/supplier.txt
index b7797e5..3995c92 100644
--- a/buying/doctype/supplier/supplier.txt
+++ b/buying/doctype/supplier/supplier.txt
@@ -5,7 +5,7 @@
{
'creation': '2010-08-08 17:09:26',
'docstatus': 0,
- 'modified': '2011-05-10 17:42:21',
+ 'modified': '2011-07-19 16:18:58',
'modified_by': 'Administrator',
'owner': 'Administrator'
},
@@ -25,7 +25,7 @@
'show_in_menu': 0,
'subject': ' ',
'tag_fields': 'supplier_type',
- 'version': 86
+ 'version': 87
},
# These values are common for all DocField
@@ -166,21 +166,8 @@
# DocField
{
'doctype': 'DocField',
- 'fieldname': 'supplier_status',
- 'fieldtype': 'Select',
- 'idx': 6,
- 'label': 'Supplier Status',
- 'oldfieldname': 'supplier_status',
- 'oldfieldtype': 'Select',
- 'options': '\nApproved\nUnapproved',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': 'DocField',
'fieldtype': 'Section Break',
- 'idx': 7,
+ 'idx': 6,
'label': 'Address & Contacts',
'oldfieldtype': 'Column Break',
'permlevel': 0
@@ -192,7 +179,7 @@
'depends_on': 'eval:doc.__islocal',
'doctype': 'DocField',
'fieldtype': 'HTML',
- 'idx': 8,
+ 'idx': 7,
'label': 'Address Desc',
'options': '<em>Addresses will appear only when you save the supplier</em>',
'permlevel': 0
@@ -203,7 +190,7 @@
'colour': 'White:FFF',
'doctype': 'DocField',
'fieldtype': 'HTML',
- 'idx': 9,
+ 'idx': 8,
'label': 'Address HTML',
'permlevel': 1
},
@@ -212,7 +199,7 @@
{
'doctype': 'DocField',
'fieldtype': 'Column Break',
- 'idx': 10,
+ 'idx': 9,
'permlevel': 0,
'width': '50%'
},
@@ -223,7 +210,7 @@
'depends_on': 'eval:doc.__islocal',
'doctype': 'DocField',
'fieldtype': 'HTML',
- 'idx': 11,
+ 'idx': 10,
'label': 'Contact Desc',
'options': '<em>Contact Details will appear only when you save the supplier</em>',
'permlevel': 0
@@ -233,7 +220,7 @@
{
'doctype': 'DocField',
'fieldtype': 'HTML',
- 'idx': 12,
+ 'idx': 11,
'label': 'Contact HTML',
'permlevel': 1
},
@@ -242,7 +229,7 @@
{
'doctype': 'DocField',
'fieldtype': 'Section Break',
- 'idx': 13,
+ 'idx': 12,
'label': 'More Info',
'oldfieldtype': 'Section Break',
'permlevel': 0
@@ -255,7 +242,7 @@
'doctype': 'DocField',
'fieldname': 'company',
'fieldtype': 'Link',
- 'idx': 14,
+ 'idx': 13,
'in_filter': 1,
'label': 'Company',
'oldfieldname': 'company',
@@ -273,7 +260,7 @@
'doctype': 'DocField',
'fieldname': 'supplier_details',
'fieldtype': 'Text',
- 'idx': 15,
+ 'idx': 14,
'label': 'Supplier Details',
'oldfieldname': 'supplier_details',
'oldfieldtype': 'Code',
@@ -284,7 +271,7 @@
{
'doctype': 'DocField',
'fieldtype': 'Column Break',
- 'idx': 16,
+ 'idx': 15,
'permlevel': 0,
'width': '50%'
},
@@ -294,7 +281,7 @@
'doctype': 'DocField',
'fieldname': 'credit_days',
'fieldtype': 'Int',
- 'idx': 17,
+ 'idx': 16,
'label': 'Credit Days',
'permlevel': 0
},
@@ -304,7 +291,7 @@
'doctype': 'DocField',
'fieldname': 'website',
'fieldtype': 'Data',
- 'idx': 18,
+ 'idx': 17,
'label': 'Website',
'oldfieldname': 'website',
'oldfieldtype': 'Data',
@@ -317,7 +304,7 @@
'depends_on': 'eval:!doc.__islocal',
'doctype': 'DocField',
'fieldtype': 'Section Break',
- 'idx': 19,
+ 'idx': 18,
'label': 'Transaction History',
'oldfieldtype': 'Section Break',
'permlevel': 0
@@ -329,7 +316,7 @@
'depends_on': 'eval:!doc.__islocal',
'doctype': 'DocField',
'fieldtype': 'HTML',
- 'idx': 20,
+ 'idx': 19,
'label': 'History HTML',
'oldfieldtype': 'HTML',
'permlevel': 0
@@ -340,7 +327,7 @@
'doctype': 'DocField',
'fieldname': 'trash_reason',
'fieldtype': 'Small Text',
- 'idx': 21,
+ 'idx': 20,
'label': 'Trash Reason',
'oldfieldname': 'trash_reason',
'oldfieldtype': 'Small Text',
diff --git a/patches/index_patch.py b/patches/index_patch.py
index 8f6c4f2..9daf4fb 100644
--- a/patches/index_patch.py
+++ b/patches/index_patch.py
@@ -102,7 +102,7 @@
'File Group': ['parent_group'],
'Maintenance Visit Detail': ['item_code', 'service_person'],
'Support Ticket Response': [],
- 'PV Detail': ['item_code', 'purchase_order', 'po_detail', 'purchase_receipt', 'pr_detail', 'expense_head', 'cost_center'],
+ 'PV Detail': ['item_code', 'purchase_order', 'purchase_receipt', 'expense_head', 'cost_center'],
'Timesheet Detail': ['project_name', 'task_id', 'customer_name'],
'Holiday List Detail': [],
'Workflow Rule Detail': [],
@@ -125,7 +125,7 @@
'Declaration Detail': [],
'Holiday List': ['fiscal_year'],
'Sales Person': ['lft', 'rgt', 'parent_sales_person'],
- 'RV Detail': ['item_code', 'sales_order', 'so_detail', 'delivery_note', 'dn_detail', 'cost_center', 'income_account'],
+ 'RV Detail': ['item_code', 'sales_order', 'delivery_note', 'cost_center', 'income_account'],
'Module Def Item': [],
'TDS Category': [],
'DocTrigger': [],
diff --git a/patches/patch.py b/patches/patch.py
index 384ee2a..ad7eb7d 100644
--- a/patches/patch.py
+++ b/patches/patch.py
@@ -1,7 +1,7 @@
# REMEMBER to update this
# ========================
-last_patch = 326
+last_patch = 327
#-------------------------------------------
@@ -1298,3 +1298,6 @@
# load the new billing page
if cint(webnotes.conn.get_value('Control Panel',None,'sync_with_gateway')):
reload_doc('server_tools','page','billing')
+ elif patch_no == 327:
+ if webnotes.conn.get_value('Control Panel', None, 'account_id') != 'axjanak2011':
+ sql("delete from `tabDocField` where fieldname = 'supplier_status' and parent = 'Supplier'")