Merge branch 'master' of github.com:webnotes/erpnext
diff --git a/accounts/search_criteria/purchase_register/purchase_register.py b/accounts/search_criteria/purchase_register/purchase_register.py
index c423b65..e955815 100644
--- a/accounts/search_criteria/purchase_register/purchase_register.py
+++ b/accounts/search_criteria/purchase_register/purchase_register.py
@@ -1,5 +1,10 @@
# add expense head columns
-expense_acc = [c[0] for c in sql("select distinct expense_head from `tabPV Detail` where parenttype='Payable Voucher' and docstatus=1 order by idx asc")]
+expense_acc = [c[0] for c in sql("""select distinct expense_head
+ from `tabPV Detail`
+ where parenttype='Payable Voucher'
+ and docstatus=1
+ order by expense_head asc""")]
+
expense_acc.append('Net Total')
for i in expense_acc:
@@ -9,7 +14,14 @@
coloptions.append('')
# Add tax head columns
-tax_acc = [c[0] for c in sql("select distinct account_head from `tabPurchase Tax Detail` where parenttype='Payable Voucher' and category in ('For Total', 'For Both') and add_deduct_tax = 'Add' and docstatus=1 order by idx asc")]
+tax_acc = [c[0] for c in sql("""select distinct account_head
+ from `tabPurchase Tax Detail`
+ where parenttype = 'Payable Voucher'
+ and add_deduct_tax = 'Add'
+ and category in ('For Total', 'For Both')
+ and docstatus=1
+ order by account_head asc""")]
+
tax_acc.append('Total Tax')
tax_acc.append('GrandTotal')
@@ -26,24 +38,60 @@
# add the values
for r in res:
+ #Get amounts for expense heads
+ exp_head_amount = sql("""select expense_head, sum(amount)
+ from `tabPV Detail`
+ where parent = %s and parenttype='Payable Voucher'
+ group by expense_head""", (r[col_idx['ID']],))
+
+ #convert the result to dictionary for easy retrieval
+ exp_head_amount_dict = {}
+ for e in exp_head_amount:
+ exp_head_amount_dict[e[0]] = e[1]
+
+ exp_head_keys = exp_head_amount_dict.keys()
+
net_total = 0
# get expense amount
for i in expense_acc:
- val = sql("select sum(amount) from `tabPV Detail` where parent = %s and parenttype='Payable Voucher' and expense_head = %s", (r[col_idx['ID']], i))
- val = flt(val and val[0][0] or 0)
+ val = 0
+
+ #check if expense head exists in dict
+ if i in exp_head_keys:
+ val = exp_head_amount_dict[i]
+ val = flt(val and val or 0)
net_total += val
r.append(val)
+
r.append(net_total)
+ #Get tax for account heads
+ acc_head_tax = sql("""select account_head, tax_amount
+ from `tabPurchase Tax Detail`
+ where parent = '%s'
+ and parenttype = 'Payable Voucher'
+ and add_deduct_tax = 'Add'
+ and category in ('For Total', 'For Both')""" %(r[col_idx['ID']],))
+
+ #Convert the result to dictionary for easy retrieval
+ acc_head_tax_dict = {}
+ for a in acc_head_tax:
+ acc_head_tax_dict[a[0]] = a[1]
+
+ acc_head_keys = acc_head_tax_dict.keys()
+
# get tax amount
total_tax = 0
grand_total = 0
for c in tax_acc:
- if c:
- val = sql("select tax_amount from `tabPurchase Tax Detail` where parent = %s and parenttype='Payable Voucher' and account_head = %s and category in ('For Total', 'For Both') and add_deduct_tax = 'Add'", (r[col_idx['ID']], c))
- val = flt(val and val[0][0] or 0)
+ val = 0
+ if c:
+ #check if account head exists in dict
+ if c in acc_head_keys:
+ val = acc_head_tax_dict[c]
+ val = flt(val and val or 0)
total_tax += val
r.append(val)
r.append(total_tax)
- r.append(total_tax+net_total) # grand total
+ r.append(flt(total_tax)+ flt(net_total)) # grand total
\ No newline at end of file
diff --git a/accounts/search_criteria/sales_register/sales_register.py b/accounts/search_criteria/sales_register/sales_register.py
index be74df3..2cd1d4b 100644
--- a/accounts/search_criteria/sales_register/sales_register.py
+++ b/accounts/search_criteria/sales_register/sales_register.py
@@ -1,44 +1,86 @@
# add additional columns
-cl = [c[0] for c in sql("select distinct account_head from `tabRV Tax Detail` where parenttype='Receivable Voucher' and docstatus=1 order by idx asc")]
+cl = [c[0] for c in sql("""select distinct account_head
+ from `tabRV Tax Detail`
+ where parenttype='Receivable Voucher'
+ and docstatus=1
+ order by account_head asc""")]
-income_acc = [c[0] for c in sql("select distinct income_account from `tabRV Detail` where parenttype='Receivable Voucher' and docstatus=1 order by idx asc")]
+income_acc = [c[0] for c in sql("""select distinct income_account
+ from `tabRV Detail`
+ where parenttype='Receivable Voucher'
+ and docstatus=1
+ order by income_account asc""")]
income_acc.append('Net Total')
for i in income_acc:
- colnames.append(i)
- coltypes.append('Currency')
- colwidths.append('100px')
- coloptions.append('')
+ colnames.append(i)
+ coltypes.append('Currency')
+ colwidths.append('100px')
+ coloptions.append('')
cl.append('Total Tax')
cl.append('Grand Total')
for c in cl:
- colnames.append(c)
- coltypes.append('Currency')
- colwidths.append('100px')
- coloptions.append('')
-
+ colnames.append(c)
+ coltypes.append('Currency')
+ colwidths.append('100px')
+ coloptions.append('')
+
income_acc = income_acc[:-1]
cl = cl[:-2]
# add the values
for r in res:
- net_total = 0
- for i in income_acc:
- val = sql("select sum(amount) from `tabRV Detail` where parent = %s and parenttype='Receivable Voucher' and income_account = %s", (r[col_idx['ID']], i))
- val = flt(val and val[0][0] or 0)
- net_total += val
- r.append(val)
- r.append(net_total)
-
- total_tax = 0
- for c in cl:
- val = sql("select tax_amount from `tabRV Tax Detail` where parent = %s and parenttype='Receivable Voucher' and account_head = %s", (r[col_idx['ID']], c))
- val = flt(val and val[0][0] or 0)
- total_tax += val
- r.append(val)
- r.append(total_tax)
- r.append(net_total+total_tax)
\ No newline at end of file
+
+ #Get amounts for income account
+ income_acc_list = sql("""select income_account, sum(amount)
+ from `tabRV Detail`
+ where parent = %s
+ and parenttype='Receivable Voucher'
+ group by income_account""", (r[col_idx['ID']],))
+
+ #convert the result to dictionary for easy retrieval
+ income_acc_dict = {}
+ for ia in income_acc_list:
+ income_acc_dict[ia[0]] = ia[1]
+
+ income_acc_keys = income_acc_dict.keys()
+
+ net_total = 0
+ for i in income_acc:
+ val = 0
+ #check if income account exists in dict
+ if i in income_acc_keys:
+ val = income_acc_dict[i]
+ val = flt(val and val or 0)
+ net_total += val
+ r.append(val)
+ r.append(net_total)
+
+ #Get tax for account heads
+ acc_head_tax = sql("""select account_head, tax_amount
+ from `tabRV Tax Detail`
+ where parent = '%s'
+ and parenttype = 'Receivable Voucher'""" %(r[col_idx['ID']],))
+
+ #Convert the result to dictionary for easy retrieval
+ acc_head_tax_dict = {}
+ for a in acc_head_tax:
+ acc_head_tax_dict[a[0]] = a[1]
+
+ acc_head_keys = acc_head_tax_dict.keys()
+
+ total_tax = 0
+ for c in cl:
+ val = 0
+ #check if account head exists in dict
+ if c in acc_head_keys:
+ val = acc_head_tax_dict[c]
+ val = flt(val and val or 0)
+ total_tax += val
+ r.append(val)
+ r.append(total_tax)
+ r.append(net_total+total_tax)
\ No newline at end of file