Test Cases - Journal Voucher, Purchase Invoice, Period Closing Voucher frappe/frappe#478
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index b096cbf..e317e11 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -16,7 +16,7 @@
def get_fiscal_year(date=None, fiscal_year=None, label="Date", verbose=1):
return get_fiscal_years(date, fiscal_year, label, verbose)[0]
-
+
def get_fiscal_years(date=None, fiscal_year=None, label="Date", verbose=1):
# if year start date is 2012-04-01, year end date should be 2013-03-31 (hence subdate)
cond = ""
@@ -27,16 +27,16 @@
(date, date)
fy = frappe.db.sql("""select name, year_start_date, year_end_date
from `tabFiscal Year` where %s order by year_start_date desc""" % cond)
-
+
if not fy:
error_msg = """%s %s not in any Fiscal Year""" % (label, formatdate(date))
- error_msg = """{msg}: {date}""".format(msg=_("Fiscal Year does not exist for date"),
+ error_msg = """{msg}: {date}""".format(msg=_("Fiscal Year does not exist for date"),
date=formatdate(date))
if verbose: frappe.msgprint(error_msg)
raise FiscalYearError, error_msg
-
+
return fy
-
+
def validate_fiscal_year(date, fiscal_year, label="Date"):
years = [f[0] for f in get_fiscal_years(date, label=label)]
if fiscal_year not in years:
@@ -52,14 +52,14 @@
if not account and frappe.form_dict.get("account"):
account = frappe.form_dict.get("account")
date = frappe.form_dict.get("date")
-
+
cond = []
if date:
cond.append("posting_date <= '%s'" % date)
else:
# get balance of all entries that exist
date = nowdate()
-
+
try:
year_start_date = get_fiscal_year(date, verbose=0)[1]
except FiscalYearError, e:
@@ -71,15 +71,15 @@
# this indicates that it is a date older than any existing fiscal year.
# hence, assuming balance as 0.0
return 0.0
-
+
acc = frappe.db.get_value('Account', account, \
['lft', 'rgt', 'report_type', 'group_or_ledger'], as_dict=1)
-
+
# for pl accounts, get balance within a fiscal year
if acc.report_type == 'Profit and Loss':
cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \
% year_start_date)
-
+
# different filter for group and ledger - improved performance
if acc.group_or_ledger=="Group":
cond.append("""exists (
@@ -88,9 +88,9 @@
)""" % (acc.lft, acc.rgt))
else:
cond.append("""gle.account = "%s" """ % (account.replace('"', '\"'), ))
-
+
bal = frappe.db.sql("""
- SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
+ SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
FROM `tabGL Entry` gle
WHERE %s""" % " and ".join(cond))[0][0]
@@ -102,7 +102,7 @@
if not args:
args = frappe.local.form_dict
args.pop("cmd")
-
+
ac = frappe.get_doc(args)
ac.doctype = "Account"
ac.old_parent = ""
@@ -115,7 +115,7 @@
if not args:
args = frappe.local.form_dict
args.pop("cmd")
-
+
cc = frappe.get_doc(args)
cc.doctype = "Cost Center"
cc.old_parent = ""
@@ -134,13 +134,14 @@
'Sales Invoice' : 'against_invoice',
'Purchase Invoice' : 'against_voucher'
}
-
+
d['against_fld'] = against_fld[d['against_voucher_type']]
# cancel JV
jv_obj = frappe.get_doc('Journal Voucher', d['voucher_no'])
+
jv_obj.make_gl_entries(cancel=1, adv_adj=1)
-
+
# update ref in JV Detail
update_against_doc(d, jv_obj)
@@ -156,13 +157,13 @@
check if jv is submitted
"""
ret = frappe.db.sql("""
- select t2.%(dr_or_cr)s from `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
- where t1.name = t2.parent and t2.account = '%(account)s'
- and ifnull(t2.against_voucher, '')=''
+ select t2.%(dr_or_cr)s from `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
+ where t1.name = t2.parent and t2.account = '%(account)s'
+ and ifnull(t2.against_voucher, '')=''
and ifnull(t2.against_invoice, '')='' and ifnull(t2.against_jv, '')=''
and t1.name = '%(voucher_no)s' and t2.name = '%(voucher_detail_no)s'
and t1.docstatus=1 and t2.%(dr_or_cr)s = %(unadjusted_amt)s""" % args)
-
+
if not ret:
throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
@@ -170,15 +171,12 @@
"""
Updates against document, if partial amount splits into rows
"""
+ jv_detail = jv_obj.get("entries", {"name": d["voucher_detail_no"]})[0]
+ jv_detail.set(d["dr_or_cr"], d["allocated_amt"])
+ jv_detail.set(d["against_fld"], d["against_voucher"])
- frappe.db.sql("""
- update `tabJournal Voucher Detail` t1, `tabJournal Voucher` t2
- set t1.%(dr_or_cr)s = '%(allocated_amt)s',
- t1.%(against_fld)s = '%(against_voucher)s', t2.modified = now()
- where t1.name = '%(voucher_detail_no)s' and t1.parent = t2.name""" % d)
-
if d['allocated_amt'] < d['unadjusted_amt']:
- jvd = frappe.db.sql("""select cost_center, balance, against_account, is_advance
+ jvd = frappe.db.sql("""select cost_center, balance, against_account, is_advance
from `tabJournal Voucher Detail` where name = %s""", d['voucher_detail_no'])
# new entry with balance amount
ch = jv_obj.append("entries")
@@ -190,90 +188,93 @@
ch.against_account = cstr(jvd[0][2])
ch.is_advance = cstr(jvd[0][3])
ch.docstatus = 1
- ch.save(1)
-
+
+ # will work as update after submit
+ jv_obj.ignore_validate_update_after_submit = True
+ jv_obj.save()
+
def get_account_list(doctype, txt, searchfield, start, page_len, filters):
if not filters.get("group_or_ledger"):
filters["group_or_ledger"] = "Ledger"
conditions, filter_values = build_filter_conditions(filters)
-
- return frappe.db.sql("""select name, parent_account from `tabAccount`
- where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
- (conditions, searchfield, "%s", "%s", "%s"),
+
+ return frappe.db.sql("""select name, parent_account from `tabAccount`
+ where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
+ (conditions, searchfield, "%s", "%s", "%s"),
tuple(filter_values + ["%%%s%%" % txt, start, page_len]))
-
+
def get_cost_center_list(doctype, txt, searchfield, start, page_len, filters):
if not filters.get("group_or_ledger"):
filters["group_or_ledger"] = "Ledger"
conditions, filter_values = build_filter_conditions(filters)
-
- return frappe.db.sql("""select name, parent_cost_center from `tabCost Center`
- where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
- (conditions, searchfield, "%s", "%s", "%s"),
+
+ return frappe.db.sql("""select name, parent_cost_center from `tabCost Center`
+ where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
+ (conditions, searchfield, "%s", "%s", "%s"),
tuple(filter_values + ["%%%s%%" % txt, start, page_len]))
-
+
def remove_against_link_from_jv(ref_type, ref_no, against_field):
- linked_jv = frappe.db.sql_list("""select parent from `tabJournal Voucher Detail`
+ linked_jv = frappe.db.sql_list("""select parent from `tabJournal Voucher Detail`
where `%s`=%s and docstatus < 2""" % (against_field, "%s"), (ref_no))
-
- if linked_jv:
+
+ if linked_jv:
frappe.db.sql("""update `tabJournal Voucher Detail` set `%s`=null,
modified=%s, modified_by=%s
- where `%s`=%s and docstatus < 2""" % (against_field, "%s", "%s", against_field, "%s"),
+ where `%s`=%s and docstatus < 2""" % (against_field, "%s", "%s", against_field, "%s"),
(now(), frappe.session.user, ref_no))
-
+
frappe.db.sql("""update `tabGL Entry`
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, '')""",
(now(), frappe.session.user, ref_type, ref_no))
-
+
frappe.msgprint("{msg} {linked_jv}".format(msg = _("""Following linked Journal Vouchers \
made against this transaction has been unlinked. You can link them again with other \
transactions via Payment Reconciliation Tool."""), linked_jv="\n".join(linked_jv)))
-
+
@frappe.whitelist()
def get_company_default(company, fieldname):
value = frappe.db.get_value("Company", company, fieldname)
-
+
if not value:
- throw(_("Please mention default value for '") +
- _(frappe.get_meta("Company").get_label(fieldname) +
+ throw(_("Please mention default value for '") +
+ _(frappe.get_meta("Company").get_label(fieldname) +
_("' in Company: ") + company))
-
+
return value
def fix_total_debit_credit():
- vouchers = frappe.db.sql("""select voucher_type, voucher_no,
- sum(debit) - sum(credit) as diff
- from `tabGL Entry`
+ vouchers = frappe.db.sql("""select voucher_type, voucher_no,
+ sum(debit) - sum(credit) as diff
+ from `tabGL Entry`
group by voucher_type, voucher_no
having sum(ifnull(debit, 0)) != sum(ifnull(credit, 0))""", as_dict=1)
-
+
for d in vouchers:
if abs(d.diff) > 0:
dr_or_cr = d.voucher_type == "Sales Invoice" and "credit" or "debit"
-
+
frappe.db.sql("""update `tabGL Entry` set %s = %s + %s
where voucher_type = %s and voucher_no = %s and %s > 0 limit 1""" %
- (dr_or_cr, dr_or_cr, '%s', '%s', '%s', dr_or_cr),
+ (dr_or_cr, dr_or_cr, '%s', '%s', '%s', dr_or_cr),
(d.diff, d.voucher_type, d.voucher_no))
-
+
def get_stock_and_account_difference(account_list=None, posting_date=None):
from erpnext.stock.utils import get_stock_balance_on
-
+
if not posting_date: posting_date = nowdate()
-
+
difference = {}
-
- account_warehouse = dict(frappe.db.sql("""select name, master_name from tabAccount
- where account_type = 'Warehouse' and ifnull(master_name, '') != ''
+
+ account_warehouse = dict(frappe.db.sql("""select name, master_name from tabAccount
+ where account_type = 'Warehouse' and ifnull(master_name, '') != ''
and name in (%s)""" % ', '.join(['%s']*len(account_list)), account_list))
-
+
for account, warehouse in account_warehouse.items():
account_balance = get_balance_on(account, posting_date)
stock_value = get_stock_balance_on(warehouse, posting_date)
@@ -286,24 +287,24 @@
args = frappe._dict(args)
if frappe.db.get_value("Account", {"name": args.account, "report_type": "Profit and Loss"}):
budget = frappe.db.sql("""
- select bd.budget_allocated, cc.distribution_id
+ select bd.budget_allocated, cc.distribution_id
from `tabCost Center` cc, `tabBudget Detail` bd
where cc.name=bd.parent and cc.name=%s and account=%s and bd.fiscal_year=%s
""", (args.cost_center, args.account, args.fiscal_year), as_dict=True)
-
+
if budget and budget[0].budget_allocated:
- yearly_action, monthly_action = frappe.db.get_value("Company", args.company,
+ yearly_action, monthly_action = frappe.db.get_value("Company", args.company,
["yearly_bgt_flag", "monthly_bgt_flag"])
action_for = action = ""
if monthly_action in ["Stop", "Warn"]:
- budget_amount = get_allocated_budget(budget[0].distribution_id,
+ budget_amount = get_allocated_budget(budget[0].distribution_id,
args.posting_date, args.fiscal_year, budget[0].budget_allocated)
-
- args["month_end_date"] = frappe.db.sql("select LAST_DAY(%s)",
+
+ args["month_end_date"] = frappe.db.sql("select LAST_DAY(%s)",
args.posting_date)[0][0]
action_for, action = "Monthly", monthly_action
-
+
elif yearly_action in ["Stop", "Warn"]:
budget_amount = budget[0].budget_allocated
action_for, action = "Monthly", yearly_action
@@ -311,44 +312,44 @@
if action_for:
actual_expense = get_actual_expense(args)
if actual_expense > budget_amount:
- throw(action_for + _(" budget ") + cstr(budget_amount) +
- _(" for account ") + args.account + _(" against cost center ") +
- args.cost_center + _(" will exceed by ") +
+ throw(action_for + _(" budget ") + cstr(budget_amount) +
+ _(" for account ") + args.account + _(" against cost center ") +
+ args.cost_center + _(" will exceed by ") +
cstr(actual_expense - budget_amount) + _(" after this transaction.")
, exc=BudgetError if action=="Stop" else False)
-
+
def get_allocated_budget(distribution_id, posting_date, fiscal_year, yearly_budget):
if distribution_id:
distribution = {}
- for d in frappe.db.sql("""select bdd.month, bdd.percentage_allocation
+ for d in frappe.db.sql("""select bdd.month, bdd.percentage_allocation
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd
where bdd.parent=bd.name and bd.fiscal_year=%s""", fiscal_year, as_dict=1):
distribution.setdefault(d.month, d.percentage_allocation)
dt = frappe.db.get_value("Fiscal Year", fiscal_year, "year_start_date")
budget_percentage = 0.0
-
+
while(dt <= getdate(posting_date)):
if distribution_id:
budget_percentage += distribution.get(getdate(dt).strftime("%B"), 0)
else:
budget_percentage += 100.0/12
-
+
dt = add_months(dt, 1)
-
+
return yearly_budget * budget_percentage / 100
-
+
def get_actual_expense(args):
args["condition"] = " and posting_date<='%s'" % args.month_end_date \
if args.get("month_end_date") else ""
-
+
return frappe.db.sql("""
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
- where account='%(account)s' and cost_center='%(cost_center)s'
+ where account='%(account)s' and cost_center='%(cost_center)s'
and fiscal_year='%(fiscal_year)s' and company='%(company)s' %(condition)s
""" % (args))[0][0]
-
+
def rename_account_for(dt, olddn, newdn, merge, company):
old_account = get_account_for(dt, olddn)
if old_account:
@@ -358,29 +359,29 @@
new_account = frappe.rename_doc("Account", old_account, newdn)
else:
existing_new_account = get_account_for(dt, newdn)
- new_account = frappe.rename_doc("Account", old_account,
+ new_account = frappe.rename_doc("Account", old_account,
existing_new_account or newdn, merge=True if existing_new_account else False)
frappe.db.set_value("Account", new_account or old_account, "master_name", newdn)
-
+
def add_abbr_if_missing(dn, company):
from erpnext.setup.doctype.company.company import get_name_with_abbr
return get_name_with_abbr(dn, company)
-
+
def get_account_for(account_for_doctype, account_for):
if account_for_doctype in ["Customer", "Supplier"]:
account_for_field = "master_type"
elif account_for_doctype == "Warehouse":
account_for_field = "account_type"
-
- return frappe.db.get_value("Account", {account_for_field: account_for_doctype,
+
+ return frappe.db.get_value("Account", {account_for_field: account_for_doctype,
"master_name": account_for})
-
+
def get_currency_precision(currency=None):
if not currency:
- currency = frappe.db.get_value("Company",
+ currency = frappe.db.get_value("Company",
frappe.db.get_default("company"), "default_currency")
currency_format = frappe.db.get_value("Currency", currency, "number_format")
-
+
from frappe.utils import get_number_format_info
return get_number_format_info(currency_format)[2]