[cleanup] [accounts] delete gl entries on cancellation of accounting transactions
diff --git a/accounts/doctype/account/account.py b/accounts/doctype/account/account.py
index a6038dd..d3d467f 100644
--- a/accounts/doctype/account/account.py
+++ b/accounts/doctype/account/account.py
@@ -98,9 +98,7 @@
# Check if any previous balance exists
def check_gle_exists(self):
- exists = sql("""select name from `tabGL Entry` where account = %s
- and ifnull(is_cancelled, 'No') = 'No'""", self.doc.name)
- return exists and exists[0][0] or ''
+ return webnotes.conn.get_value("GL Entry", {"account": self.doc.name})
def check_if_child_exists(self):
return sql("""select name from `tabAccount` where parent_account = %s
@@ -173,10 +171,6 @@
self.validate_trash()
self.update_nsm_model()
- # delete all cancelled gl entry of this account
- sql("""delete from `tabGL Entry` where account = %s and
- ifnull(is_cancelled, 'No') = 'Yes'""", self.doc.name)
-
def on_rename(self, new, old, merge=False):
company_abbr = webnotes.conn.get_value("Company", self.doc.company, "abbr")
parts = new.split(" - ")
diff --git a/accounts/doctype/account/test_account.py b/accounts/doctype/account/test_account.py
index 10b3f92..7c4f466 100644
--- a/accounts/doctype/account/test_account.py
+++ b/accounts/doctype/account/test_account.py
@@ -19,6 +19,8 @@
["_Test Account Tax Assets", "Current Assets - _TC", "Group"],
["_Test Account VAT", "_Test Account Tax Assets - _TC", "Ledger"],
["_Test Account Service Tax", "_Test Account Tax Assets - _TC", "Ledger"],
+
+ ["_Test Account Reserves and Surplus", "Current Liabilities - _TC", "Ledger"],
["_Test Account Cost for Goods Sold", "Expenses - _TC", "Ledger"],
["_Test Account Excise Duty", "_Test Account Tax Assets - _TC", "Ledger"],
diff --git a/accounts/doctype/cost_center/cost_center.py b/accounts/doctype/cost_center/cost_center.py
index 6f977d7..4b18aae 100644
--- a/accounts/doctype/cost_center/cost_center.py
+++ b/accounts/doctype/cost_center/cost_center.py
@@ -46,8 +46,7 @@
return 1
def check_gle_exists(self):
- return webnotes.conn.sql("select name from `tabGL Entry` where cost_center = %s and \
- ifnull(is_cancelled, 'No') = 'No'", (self.doc.name))
+ return webnotes.conn.get_value("GL Entry", {"cost_center": self.doc.name})
def check_if_child_exists(self):
return webnotes.conn.sql("select name from `tabCost Center` where \
diff --git a/accounts/doctype/gl_entry/gl_entry.py b/accounts/doctype/gl_entry/gl_entry.py
index 1aad21f..2719926 100644
--- a/accounts/doctype/gl_entry/gl_entry.py
+++ b/accounts/doctype/gl_entry/gl_entry.py
@@ -7,56 +7,53 @@
from webnotes.utils import flt, fmt_money, getdate
from webnotes.model.code import get_obj
from webnotes import msgprint, _
-
-sql = webnotes.conn.sql
class DocType:
def __init__(self,d,dl):
self.doc, self.doclist = d, dl
- def validate(self): # not called on cancel
+ def validate(self):
self.check_mandatory()
self.pl_must_have_cost_center()
self.validate_posting_date()
- self.doc.is_cancelled = 'No' # will be reset by GL Control if cancelled
self.check_credit_limit()
self.check_pl_account()
- def on_update(self, adv_adj, cancel, update_outstanding = 'Yes'):
+ def on_update(self, adv_adj, update_outstanding = 'Yes'):
self.validate_account_details(adv_adj)
self.validate_cost_center()
- self.check_freezing_date(adv_adj)
- self.check_negative_balance(adv_adj)
+ validate_freezed_account(self.doc.account, adv_adj)
+ check_freezing_date(self.doc.posting_date, adv_adj)
+ check_negative_balance(self.doc.account, adv_adj)
# Update outstanding amt on against voucher
if self.doc.against_voucher and self.doc.against_voucher_type != "POS" \
and update_outstanding == 'Yes':
- self.update_outstanding_amt()
+ update_outstanding_amt(self.doc.account, self.doc.against_voucher_type,
+ self.doc.against_voucher)
def check_mandatory(self):
mandatory = ['account','remarks','voucher_type','voucher_no','fiscal_year','company']
for k in mandatory:
if not self.doc.fields.get(k):
- msgprint(k + _(" is mandatory for GL Entry"), raise_exception=1)
+ webnotes.throw(k + _(" is mandatory for GL Entry"))
# Zero value transaction is not allowed
if not (flt(self.doc.debit) or flt(self.doc.credit)):
- msgprint(_("GL Entry: Debit or Credit amount is mandatory for ") + self.doc.account,
- raise_exception=1)
+ webnotes.throw(_("GL Entry: Debit or Credit amount is mandatory for ") +
+ self.doc.account)
def pl_must_have_cost_center(self):
if webnotes.conn.get_value("Account", self.doc.account, "is_pl_account") == "Yes":
if not self.doc.cost_center and self.doc.voucher_type != 'Period Closing Voucher':
- msgprint(_("Cost Center must be specified for PL Account: ") + self.doc.account,
- raise_exception=1)
- else:
- if self.doc.cost_center:
- self.doc.cost_center = ""
+ webnotes.throw(_("Cost Center must be specified for PL Account: ") +
+ self.doc.account)
+ elif self.doc.cost_center:
+ self.doc.cost_center = None
def validate_posting_date(self):
from accounts.utils import validate_fiscal_year
validate_fiscal_year(self.doc.posting_date, self.doc.fiscal_year, "Posting Date")
-
def check_credit_limit(self):
master_type, master_name = webnotes.conn.get_value("Account",
@@ -65,8 +62,8 @@
tot_outstanding = 0 #needed when there is no GL Entry in the system for that acc head
if (self.doc.voucher_type=='Journal Voucher' or self.doc.voucher_type=='Sales Invoice') \
and (master_type =='Customer' and master_name):
- dbcr = sql("""select sum(debit), sum(credit) from `tabGL Entry`
- where account = '%s' and is_cancelled='No'""" % self.doc.account)
+ dbcr = webnotes.conn.sql("""select sum(debit), sum(credit) from `tabGL Entry`
+ where account = %s""", self.doc.account)
if dbcr:
tot_outstanding = flt(dbcr[0][0]) - flt(dbcr[0][1]) + \
flt(self.doc.debit) - flt(self.doc.credit)
@@ -76,30 +73,23 @@
def check_pl_account(self):
if self.doc.is_opening=='Yes' and \
webnotes.conn.get_value("Account", self.doc.account, "is_pl_account") == "Yes":
- msgprint(_("For opening balance entry account can not be a PL account"),
- raise_exception=1)
+ webnotes.throw(_("For opening balance entry account can not be a PL account"))
def validate_account_details(self, adv_adj):
"""Account must be ledger, active and not freezed"""
- ret = sql("""select group_or_ledger, docstatus, freeze_account, company
- from tabAccount where name=%s""", self.doc.account, as_dict=1)
+ ret = webnotes.conn.sql("""select group_or_ledger, docstatus, company
+ from tabAccount where name=%s""", self.doc.account, as_dict=1)[0]
- if ret and ret[0]["group_or_ledger"]=='Group':
- msgprint(_("Account") + ": " + self.doc.account + _(" is not a ledger"), raise_exception=1)
+ if ret.group_or_ledger=='Group':
+ webnotes.throw(_("Account") + ": " + self.doc.account + _(" is not a ledger"))
- if ret and ret[0]["docstatus"]==2:
- msgprint(_("Account") + ": " + self.doc.account + _(" is not active"), raise_exception=1)
+ if ret.docstatus==2:
+ webnotes.throw(_("Account") + ": " + self.doc.account + _(" is not active"))
- # Account has been freezed for other users except account manager
- if ret and ret[0]["freeze_account"]== 'Yes' and not adv_adj \
- and not 'Accounts Manager' in webnotes.user.get_roles():
- msgprint(_("Account") + ": " + self.doc.account + _(" has been freezed. \
- Only Accounts Manager can do transaction against this account"), raise_exception=1)
-
- if self.doc.is_cancelled in ("No", None) and ret and ret[0]["company"] != self.doc.company:
- msgprint(_("Account") + ": " + self.doc.account + _(" does not belong to the company") \
- + ": " + self.doc.company, raise_exception=1)
+ if ret.company != self.doc.company:
+ webnotes.throw(_("Account") + ": " + self.doc.account +
+ _(" does not belong to the company") + ": " + self.doc.company)
def validate_cost_center(self):
if not hasattr(self, "cost_center_company"):
@@ -107,70 +97,76 @@
def _get_cost_center_company():
if not self.cost_center_company.get(self.doc.cost_center):
- self.cost_center_company[self.doc.cost_center] = webnotes.conn.get_value("Cost Center",
- self.doc.cost_center, "company")
+ self.cost_center_company[self.doc.cost_center] = webnotes.conn.get_value(
+ "Cost Center", self.doc.cost_center, "company")
return self.cost_center_company[self.doc.cost_center]
- if self.doc.is_cancelled in ("No", None) and \
- self.doc.cost_center and _get_cost_center_company() != self.doc.company:
- msgprint(_("Cost Center") + ": " + self.doc.cost_center \
- + _(" does not belong to the company") + ": " + self.doc.company, raise_exception=True)
-
- def check_freezing_date(self, adv_adj):
- """
- Nobody can do GL Entries where posting date is before freezing date
- except authorized person
- """
- if not adv_adj:
- acc_frozen_upto = webnotes.conn.get_value('Accounts Settings', None, 'acc_frozen_upto')
- if acc_frozen_upto:
- bde_auth_role = webnotes.conn.get_value( 'Accounts Settings', None,'bde_auth_role')
- if getdate(self.doc.posting_date) <= getdate(acc_frozen_upto) \
- and not bde_auth_role in webnotes.user.get_roles():
- msgprint(_("You are not authorized to do/modify back dated entries before ") +
- getdate(acc_frozen_upto).strftime('%d-%m-%Y'), raise_exception=1)
+ if self.doc.cost_center and _get_cost_center_company() != self.doc.company:
+ webnotes.throw(_("Cost Center") + ": " + self.doc.cost_center +
+ _(" does not belong to the company") + ": " + self.doc.company)
- def check_negative_balance(self, adv_adj):
- if not adv_adj:
- account = webnotes.conn.get_value("Account", self.doc.account,
- ["allow_negative_balance", "debit_or_credit"], as_dict=True)
- if not account["allow_negative_balance"]:
- balance = webnotes.conn.sql("""select sum(debit) - sum(credit) from `tabGL Entry`
- where account = %s and ifnull(is_cancelled, 'No') = 'No'""", self.doc.account)
- balance = account["debit_or_credit"] == "Debit" and \
- flt(balance[0][0]) or -1*flt(balance[0][0])
-
- if flt(balance) < 0:
- msgprint(_("Negative balance is not allowed for account ") + self.doc.account,
- raise_exception=1)
+def check_negative_balance(account, adv_adj=False):
+ if not adv_adj:
+ account_details = webnotes.conn.get_value("Account", account,
+ ["allow_negative_balance", "debit_or_credit"], as_dict=True)
+ if not account_details["allow_negative_balance"]:
+ balance = webnotes.conn.sql("""select sum(debit) - sum(credit) from `tabGL Entry`
+ where account = %s""", account)
+ balance = account_details["debit_or_credit"] == "Debit" and \
+ flt(balance[0][0]) or -1*flt(balance[0][0])
+
+ if flt(balance) < 0:
+ webnotes.throw(_("Negative balance is not allowed for account ") + self.doc.account)
- def update_outstanding_amt(self):
- # get final outstanding amt
- bal = flt(sql("""select sum(debit) - sum(credit) from `tabGL Entry`
- where against_voucher=%s and against_voucher_type=%s and account = %s
- and ifnull(is_cancelled,'No') = 'No'""", (self.doc.against_voucher,
- self.doc.against_voucher_type, self.doc.account))[0][0] or 0.0)
+def check_freezing_date(posting_date, adv_adj=False):
+ """
+ Nobody can do GL Entries where posting date is before freezing date
+ except authorized person
+ """
+ if not adv_adj:
+ acc_frozen_upto = webnotes.conn.get_value('Accounts Settings', None, 'acc_frozen_upto')
+ if acc_frozen_upto:
+ bde_auth_role = webnotes.conn.get_value( 'Accounts Settings', None,'bde_auth_role')
+ if getdate(posting_date) <= getdate(acc_frozen_upto) \
+ and not bde_auth_role in webnotes.user.get_roles():
+ webnotes.throw(_("You are not authorized to do/modify back dated entries before ")
+ + getdate(acc_frozen_upto).strftime('%d-%m-%Y'))
- if self.doc.against_voucher_type == 'Purchase Invoice':
+def update_outstanding_amt(account, against_voucher_type, against_voucher, on_cancel=False):
+ # get final outstanding amt
+ bal = flt(webnotes.conn.sql("""select sum(debit) - sum(credit) from `tabGL Entry`
+ where against_voucher_type=%s and against_voucher=%s and account = %s""",
+ (against_voucher_type, against_voucher, account))[0][0] or 0.0)
+
+ if against_voucher_type == 'Purchase Invoice':
+ bal = -bal
+ elif against_voucher_type == "Journal Voucher":
+ against_voucher_amount = flt(webnotes.conn.sql("""select sum(debit) - sum(credit)
+ from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
+ and account = %s""", (against_voucher, account))[0][0])
+
+ bal = against_voucher_amount + bal
+ if against_voucher_amount < 0:
bal = -bal
- elif self.doc.against_voucher_type == "Journal Voucher":
- against_voucher_amount = flt(webnotes.conn.sql("""select sum(debit) - sum(credit)
- from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
- and account = %s""", (self.doc.against_voucher, self.doc.account))[0][0])
+ # Validation : Outstanding can not be negative
+ if bal < 0 and not on_cancel:
+ webnotes.throw(_("Outstanding for Voucher ") + gainst_voucher + _(" will become ") +
+ fmt_money(bal) + _(". Outstanding cannot be less than zero. \
+ Please match exact outstanding."))
+
+ # Update outstanding amt on against voucher
+ if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
+ webnotes.conn.sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
+ (against_voucher_type, bal, against_voucher))
- bal = against_voucher_amount + bal
- if against_voucher_amount < 0:
- bal = -bal
-
- # Validation : Outstanding can not be negative
- if bal < 0 and self.doc.is_cancelled == 'No':
- msgprint(_("Outstanding for Voucher ") + self.doc.against_voucher +
- _(" will become ") + fmt_money(bal) + _(". Outstanding cannot be less than zero. \
- Please match exact outstanding."), raise_exception=1)
-
- # Update outstanding amt on against voucher
- if self.doc.against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
- sql("update `tab%s` set outstanding_amount=%s where name='%s'"%
- (self.doc.against_voucher_type, bal, self.doc.against_voucher))
\ No newline at end of file
+def validate_freezed_account(account, adv_adj=False):
+ """Account has been freezed for other users except account manager"""
+
+ freezed_account = webnotes.conn.get_value("Account", account, "freeze_account")
+
+ if freezed_account == 'Yes' and not adv_adj \
+ and 'Accounts Manager' not in webnotes.user.get_roles():
+ webnotes.throw(_("Account") + ": " + account + _(" has been freezed. \
+ Only Accounts Manager can do transaction against this account"))
\ No newline at end of file
diff --git a/accounts/doctype/journal_voucher/journal_voucher.py b/accounts/doctype/journal_voucher/journal_voucher.py
index 27b0518..8c0c052 100644
--- a/accounts/doctype/journal_voucher/journal_voucher.py
+++ b/accounts/doctype/journal_voucher/journal_voucher.py
@@ -49,7 +49,7 @@
from accounts.utils import remove_against_link_from_jv
remove_against_link_from_jv(self.doc.doctype, self.doc.name, "against_jv")
- self.make_gl_entries(cancel=1)
+ self.make_gl_entries()
def on_trash(self):
pass
@@ -254,10 +254,10 @@
"against_voucher": d.against_voucher or d.against_invoice or d.against_jv,
"remarks": self.doc.remark,
"cost_center": d.cost_center
- }, cancel)
+ })
)
if gl_map:
- make_gl_entries(gl_map, cancel=cancel, adv_adj=adv_adj)
+ make_gl_entries(gl_map, cancel=self.doc.docstatus==2, adv_adj=adv_adj)
def get_outstanding(self, args):
args = eval(args)
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
index dc2bcc5..b91cc8b 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
@@ -20,8 +20,7 @@
def get_voucher_details(self):
total_amount = webnotes.conn.sql("""select %s from `tabGL Entry`
- where voucher_type = %s and voucher_no = %s
- and account = %s and ifnull(is_cancelled, 'No') = 'No'""" %
+ where voucher_type = %s and voucher_no = %s and account = %s""" %
(self.doc.account_type, '%s', '%s', '%s'),
(self.doc.voucher_type, self.doc.voucher_no, self.doc.account))
@@ -29,7 +28,7 @@
reconciled_payment = webnotes.conn.sql("""
select sum(ifnull(%s, 0)) - sum(ifnull(%s, 0)) from `tabGL Entry` where
against_voucher = %s and voucher_no != %s
- and account = %s and ifnull(is_cancelled, 'No') = 'No'""" %
+ and account = %s""" %
((self.doc.account_type == 'debit' and 'credit' or 'debit'), self.doc.account_type,
'%s', '%s', '%s'), (self.doc.voucher_no, self.doc.voucher_no, self.doc.account))
@@ -135,7 +134,6 @@
where gle.account = '%(acc)s'
and gle.voucher_type = '%(dt)s'
and gle.voucher_no like '%(txt)s'
- and ifnull(gle.is_cancelled, 'No') = 'No'
and (ifnull(gle.against_voucher, '') = ''
or ifnull(gle.against_voucher, '') = gle.voucher_no )
and ifnull(gle.%(account_type)s, 0) > 0
@@ -143,8 +141,7 @@
from `tabGL Entry`
where against_voucher_type = '%(dt)s'
and against_voucher = gle.voucher_no
- and voucher_no != gle.voucher_no
- and ifnull(is_cancelled, 'No') = 'No')
+ and voucher_no != gle.voucher_no)
!= abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 0)
)
%(mcond)s
diff --git a/accounts/doctype/sales_invoice/test_sales_invoice.py b/accounts/doctype/sales_invoice/test_sales_invoice.py
index a9546a4..3d7959a 100644
--- a/accounts/doctype/sales_invoice/test_sales_invoice.py
+++ b/accounts/doctype/sales_invoice/test_sales_invoice.py
@@ -325,12 +325,10 @@
# cancel
si.cancel()
- gle_count = webnotes.conn.sql("""select count(name) from `tabGL Entry`
- where voucher_type='Sales Invoice' and voucher_no=%s
- and ifnull(is_cancelled, 'No') = 'Yes'
- order by account asc""", si.doc.name)
+ gle = webnotes.conn.sql("""select * from `tabGL Entry`
+ where voucher_type='Sales Invoice' and voucher_no=%s""", si.doc.name)
- self.assertEquals(gle_count[0][0], 8)
+ self.assertFalse(gle)
def atest_pos_gl_entry_with_aii(self):
webnotes.conn.sql("delete from `tabStock Ledger Entry`")
@@ -387,12 +385,10 @@
# cancel
si.cancel()
- gl_count = webnotes.conn.sql("""select count(name)
- from `tabGL Entry` where voucher_type='Sales Invoice' and voucher_no=%s
- and ifnull(is_cancelled, 'No') = 'Yes'
- order by account asc, name asc""", si.doc.name)
+ gle = webnotes.conn.sql("""select * from `tabGL Entry`
+ where voucher_type='Sales Invoice' and voucher_no=%s""", si.doc.name)
- self.assertEquals(gl_count[0][0], 16)
+ self.assertFalse(gle)
self.assertFalse(get_stock_and_account_difference([si.doclist[1].warehouse]))
diff --git a/accounts/general_ledger.py b/accounts/general_ledger.py
index 8cfcfd9..4b7e425 100644
--- a/accounts/general_ledger.py
+++ b/accounts/general_ledger.py
@@ -8,14 +8,14 @@
def make_gl_entries(gl_map, cancel=False, adv_adj=False, merge_entries=True,
update_outstanding='Yes'):
- if merge_entries:
- gl_map = merge_similar_entries(gl_map)
-
- if cancel:
- set_as_cancel(gl_map[0]["voucher_type"], gl_map[0]["voucher_no"])
+ if not cancel:
+ if merge_entries:
+ gl_map = merge_similar_entries(gl_map)
- check_budget(gl_map, cancel)
- save_entries(gl_map, cancel, adv_adj, update_outstanding)
+ check_budget(gl_map, cancel)
+ save_entries(gl_map, adv_adj, update_outstanding)
+ else:
+ delete_gl_entries(gl_map, adv_adj, update_outstanding)
def merge_similar_entries(gl_map):
merged_gl_map = []
@@ -52,7 +52,7 @@
if acc_details[0]=="Yes" and acc_details[1]=="Debit":
webnotes.get_obj('Budget Control').check_budget(gle, cancel)
-def save_entries(gl_map, cancel, adv_adj, update_outstanding):
+def save_entries(gl_map, adv_adj, update_outstanding):
total_debit = total_credit = 0.0
def _swap(gle):
gle.debit, gle.credit = abs(flt(gle.credit)), abs(flt(gle.debit))
@@ -68,36 +68,38 @@
if flt(gle.debit) < 0 or flt(gle.credit) < 0:
_swap(gle)
- # toggled debit/credit in two separate condition because
- # both should be executed at the
- # time of cancellation when there is negative amount (tax discount)
- if cancel:
- _swap(gle)
-
gle_obj = webnotes.get_obj(doc=gle)
- # validate except on_cancel
- if not cancel:
- gle_obj.validate()
-
- # save
+ gle_obj.validate()
gle.save(1)
- gle_obj.on_update(adv_adj, cancel, update_outstanding)
+ gle_obj.on_update(adv_adj, update_outstanding)
# update total debit / credit
total_debit += flt(gle.debit)
total_credit += flt(gle.credit)
- if not cancel:
- validate_total_debit_credit(total_debit, total_credit)
+ validate_total_debit_credit(total_debit, total_credit)
def validate_total_debit_credit(total_debit, total_credit):
if abs(total_debit - total_credit) > 0.005:
- webnotes.msgprint("""Debit and Credit not equal for
- this voucher: Diff (Debit) is %s""" %
- (total_debit - total_credit), raise_exception=1)
-
-def set_as_cancel(voucher_type, voucher_no):
- webnotes.conn.sql("""update `tabGL Entry` set is_cancelled='Yes',
- modified=%s, modified_by=%s
- where voucher_type=%s and voucher_no=%s""",
- (now(), webnotes.session.user, voucher_type, voucher_no))
\ No newline at end of file
+ webnotes.throw(_("Debit and Credit not equal for this voucher: Diff (Debit) is ") +
+ cstr(total_debit - total_credit))
+
+def delete_gl_entries(gl_entries, adv_adj, update_outstanding):
+ from accounts.doctype.gl_entry.gl_entry import check_negative_balance, \
+ check_freezing_date, update_outstanding_amt, validate_freezed_account
+
+ check_freezing_date(gl_entries[0]["posting_date"], adv_adj)
+
+ webnotes.conn.sql("""delete from `tabGL Entry` where voucher_type=%s and voucher_no=%s""",
+ (gl_entries[0]["voucher_type"], gl_entries[0]["voucher_no"]))
+
+ for entry in gl_entries:
+ validate_freezed_account(entry["account"], adv_adj)
+ check_negative_balance(entry["account"], adv_adj)
+ if entry.get("against_voucher") and entry.get("against_voucher_type") != "POS" \
+ and update_outstanding == 'Yes':
+ update_outstanding_amt(entry["account"], entry.get("against_voucher_type"),
+ entry.get("against_voucher"))
+
+ # To-do
+ # Check and update budget for expense account
\ No newline at end of file
diff --git a/accounts/report/accounts_payable/accounts_payable.py b/accounts/report/accounts_payable/accounts_payable.py
index 20702fd..d9a0ca2 100644
--- a/accounts/report/accounts_payable/accounts_payable.py
+++ b/accounts/report/accounts_payable/accounts_payable.py
@@ -73,7 +73,7 @@
conditions, supplier_accounts = get_conditions(filters, before_report_date)
gl_entries = []
gl_entries = webnotes.conn.sql("""select * from `tabGL Entry`
- where ifnull(is_cancelled, 'No') = 'No' %s order by posting_date, account""" %
+ where docstatus < 2 %s order by posting_date, account""" %
(conditions), tuple(supplier_accounts), as_dict=1)
return gl_entries
@@ -126,7 +126,7 @@
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where account = %s and posting_date <= %s and against_voucher_type = %s
- and against_voucher = %s and name != %s and ifnull(is_cancelled, 'No') = 'No'""",
+ and against_voucher = %s and name != %s""",
(gle.account, report_date, gle.voucher_type, gle.voucher_no, gle.name))[0][0]
outstanding_amount = flt(gle.credit) - flt(gle.debit) - flt(payment_amount)
diff --git a/accounts/report/accounts_receivable/accounts_receivable.py b/accounts/report/accounts_receivable/accounts_receivable.py
index 3ae2223..86a2475 100644
--- a/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/accounts/report/accounts_receivable/accounts_receivable.py
@@ -65,7 +65,7 @@
def get_gl_entries(filters, upto_report_date=True):
conditions, customer_accounts = get_conditions(filters, upto_report_date)
return webnotes.conn.sql("""select * from `tabGL Entry`
- where ifnull(is_cancelled, 'No') = 'No' %s order by posting_date, account""" %
+ where docstatus < 2 %s order by posting_date, account""" %
(conditions), tuple(customer_accounts), as_dict=1)
def get_conditions(filters, upto_report_date=True):
@@ -116,7 +116,7 @@
select sum(ifnull(credit, 0)) - sum(ifnull(debit, 0))
from `tabGL Entry`
where account = %s and posting_date <= %s and against_voucher_type = %s
- and against_voucher = %s and name != %s and ifnull(is_cancelled, 'No') = 'No'""",
+ and against_voucher = %s and name != %s""",
(gle.account, report_date, gle.voucher_type, gle.voucher_no, gle.name))[0][0]
return flt(gle.debit) - flt(gle.credit) - flt(payment_amount)
@@ -130,7 +130,7 @@
payment_amount = webnotes.conn.sql("""
select sum(ifnull(credit, 0)) - sum(ifnull(debit, 0)) from `tabGL Entry`
where account = %s and posting_date <= %s and against_voucher_type = %s
- and against_voucher = %s and name != %s and ifnull(is_cancelled, 'No') = 'No'""",
+ and against_voucher = %s and name != %s""",
(gle.account, report_date, gle.voucher_type, gle.voucher_no, gle.name))[0][0]
return flt(payment_amount)
diff --git a/accounts/report/budget_variance_report/budget_variance_report.py b/accounts/report/budget_variance_report/budget_variance_report.py
index 015e2c0..ee4f6fe 100644
--- a/accounts/report/budget_variance_report/budget_variance_report.py
+++ b/accounts/report/budget_variance_report/budget_variance_report.py
@@ -87,7 +87,7 @@
return webnotes.conn.sql("""select gl.account, gl.debit, gl.credit,
gl.cost_center, MONTHNAME(gl.posting_date) as month_name
from `tabGL Entry` gl, `tabBudget Detail` bd
- where gl.fiscal_year=%s and company=%s and is_cancelled='No'
+ where gl.fiscal_year=%s and company=%s
and bd.account=gl.account""" % ('%s', '%s'),
(filters.get("fiscal_year"), filters.get("company")), as_dict=1)
diff --git a/accounts/report/gross_profit/gross_profit.py b/accounts/report/gross_profit/gross_profit.py
index 3aba234..ccc34b5 100644
--- a/accounts/report/gross_profit/gross_profit.py
+++ b/accounts/report/gross_profit/gross_profit.py
@@ -48,7 +48,7 @@
voucher_detail_no, posting_date, posting_time, stock_value,
warehouse, actual_qty as qty
from `tabStock Ledger Entry`
- where ifnull(`is_cancelled`, "No") = "No" """
+ where ifnull(`is_cancelled`, 'No') = No'"""
if filters.get("company"):
query += """ and company=%(company)s"""
diff --git a/accounts/utils.py b/accounts/utils.py
index e49d4b1..bb1e5d9 100644
--- a/accounts/utils.py
+++ b/accounts/utils.py
@@ -91,15 +91,10 @@
else:
cond.append("""gle.account = "%s" """ % (account, ))
- # join conditional conditions
- cond = " and ".join(cond)
- if cond:
- cond += " and "
-
bal = webnotes.conn.sql("""
SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
FROM `tabGL Entry` gle
- WHERE %s ifnull(is_cancelled, 'No') = 'No' """ % (cond, ))[0][0]
+ WHERE %s""" % " and ".join(cond))[0][0]
# if credit account, it should calculate credit - debit
if bal and acc.debit_or_credit == 'Credit':
@@ -236,8 +231,7 @@
set against_voucher_type=null, against_voucher=null,
modified=%s, modified_by=%s
where against_voucher_type=%s and against_voucher=%s
- and voucher_no != ifnull(against_voucher, "")
- and ifnull(is_cancelled, "No")="No" """,
+ and voucher_no != ifnull(against_voucher, '')""",
(now(), webnotes.session.user, ref_type, ref_no))
@webnotes.whitelist()
diff --git a/controllers/accounts_controller.py b/controllers/accounts_controller.py
index bbad960..4b63f3f 100644
--- a/controllers/accounts_controller.py
+++ b/controllers/accounts_controller.py
@@ -330,11 +330,8 @@
self.calculate_outstanding_amount()
- def get_gl_dict(self, args, cancel=None):
+ def get_gl_dict(self, args):
"""this method populates the common properties of a gl entry record"""
- if cancel is None:
- cancel = (self.doc.docstatus == 2)
-
gl_dict = {
'company': self.doc.company,
'posting_date': self.doc.posting_date,
@@ -342,7 +339,6 @@
'voucher_no': self.doc.name,
'aging_date': self.doc.fields.get("aging_date") or self.doc.posting_date,
'remarks': self.doc.remarks,
- 'is_cancelled': cancel and "Yes" or "No",
'fiscal_year': self.doc.fiscal_year,
'debit': 0,
'credit': 0,
diff --git a/controllers/stock_controller.py b/controllers/stock_controller.py
index 0ed2e2e..6439ade 100644
--- a/controllers/stock_controller.py
+++ b/controllers/stock_controller.py
@@ -23,7 +23,7 @@
"against": against_stock_account,
"debit": amount,
"remarks": self.doc.remarks or "Accounting Entry for Stock",
- }, self.doc.docstatus == 2),
+ }),
# account against stock in hand
self.get_gl_dict({
@@ -32,7 +32,7 @@
"credit": amount,
"cost_center": cost_center or None,
"remarks": self.doc.remarks or "Accounting Entry for Stock",
- }, self.doc.docstatus == 2),
+ }),
]
return gl_entries
diff --git a/selling/doctype/sales_common/sales_common.py b/selling/doctype/sales_common/sales_common.py
index 84f956e..9aac506 100644
--- a/selling/doctype/sales_common/sales_common.py
+++ b/selling/doctype/sales_common/sales_common.py
@@ -311,7 +311,8 @@
acc_head = webnotes.conn.sql("select name from `tabAccount` where company = '%s' and master_name = '%s'"%(obj.doc.company, obj.doc.customer))
if acc_head:
tot_outstanding = 0
- dbcr = webnotes.conn.sql("select sum(debit), sum(credit) from `tabGL Entry` where account = '%s' and ifnull(is_cancelled, 'No')='No'" % acc_head[0][0])
+ dbcr = webnotes.conn.sql("""select sum(debit), sum(credit) from `tabGL Entry`
+ where account = %s""", acc_head[0][0])
if dbcr:
tot_outstanding = flt(dbcr[0][0])-flt(dbcr[0][1])
diff --git a/setup/doctype/company/company.py b/setup/doctype/company/company.py
index 7a1d037..ea320ed 100644
--- a/setup/doctype/company/company.py
+++ b/setup/doctype/company/company.py
@@ -287,7 +287,7 @@
"""
Trash accounts and cost centers for this company if no gl entry exists
"""
- rec = webnotes.conn.sql("SELECT name from `tabGL Entry` where ifnull(is_cancelled, 'No') = 'No' and company = %s", self.doc.name)
+ rec = webnotes.conn.sql("SELECT name from `tabGL Entry` where company = %s", self.doc.name)
if not rec:
# delete gl entry
webnotes.conn.sql("delete from `tabGL Entry` where company = %s", self.doc.name)
diff --git a/setup/doctype/email_digest/email_digest.py b/setup/doctype/email_digest/email_digest.py
index 39e377a..07efd16 100644
--- a/setup/doctype/email_digest/email_digest.py
+++ b/setup/doctype/email_digest/email_digest.py
@@ -362,8 +362,8 @@
gl_entries = webnotes.conn.sql("""select `account`,
ifnull(credit, 0) as credit, ifnull(debit, 0) as debit, `against`
from `tabGL Entry`
- where company=%s and ifnull(is_cancelled, "No")="No" and
- posting_date <= %s %s""" % ("%s", "%s",
+ where company=%s
+ and posting_date <= %s %s""" % ("%s", "%s",
from_date and "and posting_date>='%s'" % from_date or ""),
(self.doc.company, to_date or self.to_date), as_dict=1)
diff --git a/startup/report_data_map.py b/startup/report_data_map.py
index e619619..54453f6 100644
--- a/startup/report_data_map.py
+++ b/startup/report_data_map.py
@@ -36,7 +36,6 @@
"GL Entry": {
"columns": ["name", "account", "posting_date", "cost_center", "debit", "credit",
"is_opening", "company", "voucher_type", "voucher_no", "remarks"],
- "conditions": ["ifnull(is_cancelled, 'No')='No'"],
"order_by": "posting_date, account",
"links": {
"account": ["Account", "name"],