[cleanup] [minor] deprecated budget control and rewritten budget related code
diff --git a/accounts/doctype/budget_control/README.md b/accounts/doctype/budget_control/README.md
deleted file mode 100644
index 2821015..0000000
--- a/accounts/doctype/budget_control/README.md
+++ /dev/null
@@ -1 +0,0 @@
-Backend scripts for Budget Management.
\ No newline at end of file
diff --git a/accounts/doctype/budget_control/__init__.py b/accounts/doctype/budget_control/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/accounts/doctype/budget_control/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/accounts/doctype/budget_control/budget_control.py b/accounts/doctype/budget_control/budget_control.py
deleted file mode 100644
index 0aa64c8..0000000
--- a/accounts/doctype/budget_control/budget_control.py
+++ /dev/null
@@ -1,97 +0,0 @@
-# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd.
-# License: GNU General Public License v3. See license.txt
-
-from __future__ import unicode_literals
-import webnotes
-from webnotes.utils import cstr, flt, getdate
-from webnotes import msgprint
-
-class DocType:
- def __init__(self,d,dl):
- self.doc, self.doclist = d, dl
-
- # Get monthly budget
- #-------------------
- def get_monthly_budget(self, distribution_id, cfy, st_date, post_dt, budget_allocated):
-
- # get month_list
- st_date, post_dt = getdate(st_date), getdate(post_dt)
-
- if distribution_id:
- if st_date.month <= post_dt.month:
- tot_per_allocated = webnotes.conn.sql("select ifnull(sum(percentage_allocation),0) from `tabBudget Distribution Detail` where parent='%s' and idx between '%s' and '%s'" % (distribution_id, st_date.month, post_dt.month))[0][0]
-
- if st_date.month > post_dt.month:
-
- tot_per_allocated = flt(webnotes.conn.sql("select ifnull(sum(percentage_allocation),0) from `tabBudget Distribution Detail` where parent='%s' and idx between '%s' and '%s'" % (distribution_id, st_date.month, 12 ))[0][0])
- tot_per_allocated = flt(tot_per_allocated) + flt(webnotes.conn.sql("select ifnull(sum(percentage_allocation),0) from `tabBudget Distribution Detail` where parent='%s' and idx between '%s' and '%s'" % (distribution_id, 1, post_dt.month))[0][0])
-
- return (flt(budget_allocated) * flt(tot_per_allocated)) / 100
- period_diff = webnotes.conn.sql("select PERIOD_DIFF('%s','%s')" % (post_dt.strftime('%Y%m'), st_date.strftime('%Y%m')))
-
- return (flt(budget_allocated) * (flt(period_diff[0][0]) + 1)) / 12
-
- def validate_budget(self, acct, cost_center, actual, budget, action):
- # action if actual exceeds budget
- if flt(actual) > flt(budget):
- msgprint("Your monthly expense "+ cstr((action == 'stop') and "will exceed" or "has exceeded") +" budget for <b>Account - "+cstr(acct)+" </b> under <b>Cost Center - "+ cstr(cost_center) + "</b>"+cstr((action == 'Stop') and ", you can not have this transaction." or "."))
- if action == 'Stop': raise Exception
-
- def check_budget(self,gle,cancel):
- # get allocated budget
-
- bgt = webnotes.conn.sql("""select t1.budget_allocated, t1.actual, t2.distribution_id
- from `tabBudget Detail` t1, `tabCost Center` t2
- where t1.account='%s' and t1.parent=t2.name and t2.name = '%s'
- and t1.fiscal_year='%s'""" %
- (gle['account'], gle['cost_center'], gle['fiscal_year']), as_dict =1)
-
- curr_amt = flt(gle['debit']) - flt(gle['credit'])
- if cancel: curr_amt = -1 * curr_amt
-
- if bgt and bgt[0]['budget_allocated']:
- # check budget flag in Company
- bgt_flag = webnotes.conn.sql("""select yearly_bgt_flag, monthly_bgt_flag
- from `tabCompany` where name = '%s'""" % gle['company'], as_dict =1)
-
- if bgt_flag and bgt_flag[0]['monthly_bgt_flag'] in ['Stop', 'Warn']:
- # get start date and last date
- start_date = webnotes.conn.get_value('Fiscal Year', gle['fiscal_year'], \
- 'year_start_date').strftime('%Y-%m-%d')
- end_date = webnotes.conn.sql("select LAST_DAY('%s')" % gle['posting_date'])
-
- # get Actual
- actual = self.get_period_difference(gle['account'] +
- '~~~' + cstr(start_date) + '~~~' + cstr(end_date[0][0]), gle['cost_center'])
-
- # Get Monthly budget
- budget = self.get_monthly_budget(bgt and bgt[0]['distribution_id'] or '' , \
- gle['fiscal_year'], start_date, gle['posting_date'], bgt[0]['budget_allocated'])
-
- # validate monthly budget
- self.validate_budget(gle['account'], gle['cost_center'], \
- flt(actual) + flt(curr_amt), budget, bgt_flag[0]['monthly_bgt_flag'])
-
- # update actual against budget allocated in cost center
- webnotes.conn.sql("""update `tabBudget Detail` set actual = ifnull(actual,0) + %s
- where account = '%s' and fiscal_year = '%s' and parent = '%s'""" %
- (curr_amt, gle['account'],gle['fiscal_year'], gle['cost_center']))
-
-
- def get_period_difference(self, arg, cost_center =''):
- # used in General Ledger Page Report
- # used for Budget where cost center passed as extra argument
- acc, f, t = arg.split('~~~')
- c, fy = '', webnotes.conn.get_defaults()['fiscal_year']
-
- det = webnotes.conn.sql("select debit_or_credit, lft, rgt, is_pl_account from tabAccount where name=%s", acc)
- if f: c += (' and t1.posting_date >= "%s"' % f)
- if t: c += (' and t1.posting_date <= "%s"' % t)
- if cost_center: c += (' and t1.cost_center = "%s"' % cost_center)
- bal = webnotes.conn.sql("select sum(ifnull(t1.debit,0))-sum(ifnull(t1.credit,0)) from `tabGL Entry` t1 where t1.account='%s' %s" % (acc, c))
- bal = bal and flt(bal[0][0]) or 0
-
- if det[0][0] != 'Debit':
- bal = (-1) * bal
-
- return flt(bal)
\ No newline at end of file
diff --git a/accounts/doctype/budget_control/budget_control.txt b/accounts/doctype/budget_control/budget_control.txt
deleted file mode 100644
index 13adf4b..0000000
--- a/accounts/doctype/budget_control/budget_control.txt
+++ /dev/null
@@ -1,19 +0,0 @@
-[
- {
- "creation": "2012-03-27 14:35:41",
- "docstatus": 0,
- "modified": "2013-07-10 14:54:06",
- "modified_by": "Administrator",
- "owner": "nabin@webnotestech.com"
- },
- {
- "doctype": "DocType",
- "issingle": 1,
- "module": "Accounts",
- "name": "__common__"
- },
- {
- "doctype": "DocType",
- "name": "Budget Control"
- }
-]
\ No newline at end of file
diff --git a/accounts/doctype/budget_detail/budget_detail.txt b/accounts/doctype/budget_detail/budget_detail.txt
index f16190d..f53ff52 100644
--- a/accounts/doctype/budget_detail/budget_detail.txt
+++ b/accounts/doctype/budget_detail/budget_detail.txt
@@ -2,7 +2,7 @@
{
"creation": "2013-03-07 11:55:04",
"docstatus": 0,
- "modified": "2013-07-10 14:54:06",
+ "modified": "2013-08-22 17:27:59",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -20,7 +20,8 @@
"parent": "Budget Detail",
"parentfield": "fields",
"parenttype": "DocType",
- "permlevel": 0
+ "permlevel": 0,
+ "reqd": 1
},
{
"doctype": "DocType",
@@ -35,7 +36,6 @@
"oldfieldname": "account",
"oldfieldtype": "Link",
"options": "Account",
- "reqd": 1,
"search_index": 1
},
{
@@ -45,18 +45,7 @@
"label": "Budget Allocated",
"oldfieldname": "budget_allocated",
"oldfieldtype": "Currency",
- "options": "Company:company:default_currency",
- "reqd": 1
- },
- {
- "doctype": "DocField",
- "fieldname": "actual",
- "fieldtype": "Currency",
- "label": "Actual",
- "oldfieldname": "actual",
- "oldfieldtype": "Currency",
- "options": "Company:company:default_currency",
- "read_only": 1
+ "options": "Company:company:default_currency"
},
{
"doctype": "DocField",
@@ -67,7 +56,6 @@
"oldfieldname": "fiscal_year",
"oldfieldtype": "Select",
"options": "link:Fiscal Year",
- "reqd": 1,
"search_index": 1
}
]
\ No newline at end of file
diff --git a/accounts/doctype/budget_distribution/test_budget_distribution.py b/accounts/doctype/budget_distribution/test_budget_distribution.py
index 7ac835d..629fd87 100644
--- a/accounts/doctype/budget_distribution/test_budget_distribution.py
+++ b/accounts/doctype/budget_distribution/test_budget_distribution.py
@@ -1,4 +1,70 @@
# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd.
# License: GNU General Public License v3. See license.txt
-test_records = []
\ No newline at end of file
+test_records = [
+ [{
+ "doctype": "Budget Distribution",
+ "distribution_id": "_Test Distribution",
+ "fiscal_year": "_Test Fiscal Year 2013",
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "April",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "May",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "June",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "July",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "August",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "September",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "October",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "November",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "December",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "January",
+ "percentage_allocation": "8"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "February",
+ "percentage_allocation": "10"
+ }, {
+ "doctype": "Budget Distribution Detail",
+ "parentfield": "budget_distribution_details",
+ "month": "March",
+ "percentage_allocation": "10"
+ }]
+]
\ No newline at end of file
diff --git a/accounts/doctype/cost_center/test_cost_center.py b/accounts/doctype/cost_center/test_cost_center.py
index 7c63d7c..056755e 100644
--- a/accounts/doctype/cost_center/test_cost_center.py
+++ b/accounts/doctype/cost_center/test_cost_center.py
@@ -7,6 +7,13 @@
"cost_center_name": "_Test Cost Center",
"parent_cost_center": "_Test Company - _TC",
"company": "_Test Company",
- "group_or_ledger": "Ledger"
+ "group_or_ledger": "Ledger",
+ "distribution_id": "_Test Distribution",
+ }, {
+ "doctype": "Budget Detail",
+ "parentfield": "budget_details",
+ "account": "_Test Account Cost for Goods Sold - _TC",
+ "budget_allocated": 100000,
+ "fiscal_year": "_Test Fiscal Year 2013"
}],
]
\ No newline at end of file
diff --git a/accounts/doctype/gl_entry/gl_entry.txt b/accounts/doctype/gl_entry/gl_entry.txt
index 2f20a35..90b2ed4 100644
--- a/accounts/doctype/gl_entry/gl_entry.txt
+++ b/accounts/doctype/gl_entry/gl_entry.txt
@@ -2,7 +2,7 @@
{
"creation": "2013-01-10 16:34:06",
"docstatus": 0,
- "modified": "2013-07-05 14:39:07",
+ "modified": "2013-08-22 17:12:13",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -173,17 +173,6 @@
},
{
"doctype": "DocField",
- "fieldname": "is_cancelled",
- "fieldtype": "Select",
- "in_filter": 1,
- "label": "Is Cancelled",
- "oldfieldname": "is_cancelled",
- "oldfieldtype": "Select",
- "options": "No\nYes",
- "search_index": 0
- },
- {
- "doctype": "DocField",
"fieldname": "is_opening",
"fieldtype": "Select",
"in_filter": 1,
diff --git a/accounts/doctype/journal_voucher/test_journal_voucher.py b/accounts/doctype/journal_voucher/test_journal_voucher.py
index 30e3ada..2065232 100644
--- a/accounts/doctype/journal_voucher/test_journal_voucher.py
+++ b/accounts/doctype/journal_voucher/test_journal_voucher.py
@@ -31,6 +31,21 @@
self.assertTrue(not webnotes.conn.sql("""select name from `tabJournal Voucher Detail`
where against_jv=%s""", jv_invoice.doc.name))
+
+ def test_budget(self):
+ from accounts.utils import BudgetError
+ webnotes.conn.set_value("Company", "_Test Company", "monthly_bgt_flag", "Stop")
+
+ jv1 = webnotes.bean(copy=test_records[0])
+ jv1.doc.posting_date = "2013-02-12"
+ jv1.doclist[2].account = "_Test Account Cost for Goods Sold - _TC"
+ jv1.doclist[2].cost_center = "_Test Cost Center - _TC"
+ jv1.doclist[2].debit = 20000.0
+ jv1.doclist[1].credit = 20000.0
+ jv1.insert()
+
+ self.assertRaises(BudgetError, jv1.submit)
+
test_records = [
[{
diff --git a/accounts/general_ledger.py b/accounts/general_ledger.py
index 4b7e425..f2dc748 100644
--- a/accounts/general_ledger.py
+++ b/accounts/general_ledger.py
@@ -5,6 +5,7 @@
import webnotes
from webnotes.utils import flt, cstr, now
from webnotes.model.doc import Document
+from accounts.utils import validate_expense_against_budget
def make_gl_entries(gl_map, cancel=False, adv_adj=False, merge_entries=True,
update_outstanding='Yes'):
@@ -12,7 +13,6 @@
if merge_entries:
gl_map = merge_similar_entries(gl_map)
- check_budget(gl_map, cancel)
save_entries(gl_map, adv_adj, update_outstanding)
else:
delete_gl_entries(gl_map, adv_adj, update_outstanding)
@@ -54,20 +54,21 @@
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))
+ def _swap(entry):
+ entry["debit"], entry["credit"] = abs(flt(entry["credit"])), abs(flt(entry["debit"]))
for entry in gl_map:
- gle = Document('GL Entry', fielddata=entry)
-
# round off upto 2 decimal
- gle.debit = flt(gle.debit, 2)
- gle.credit = flt(gle.credit, 2)
+ entry["debit"] = flt(entry["debit"], 2)
+ entry["credit"] = flt(entry["credit"], 2)
# toggle debit, credit if negative entry
- if flt(gle.debit) < 0 or flt(gle.credit) < 0:
- _swap(gle)
+ if flt(entry["debit"]) < 0 or flt(entry["credit"]) < 0:
+ _swap(entry)
+ validate_expense_against_budget(entry)
+
+ gle = Document('GL Entry', fielddata=entry)
gle_obj = webnotes.get_obj(doc=gle)
gle_obj.validate()
gle.save(1)
@@ -96,10 +97,9 @@
for entry in gl_entries:
validate_freezed_account(entry["account"], adv_adj)
check_negative_balance(entry["account"], adv_adj)
+ validate_expense_against_budget(entry)
+
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
+ entry.get("against_voucher"))
\ No newline at end of file
diff --git a/accounts/utils.py b/accounts/utils.py
index d91c4db..4110c99 100644
--- a/accounts/utils.py
+++ b/accounts/utils.py
@@ -4,7 +4,7 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import nowdate, nowtime, cstr, flt, now
+from webnotes.utils import nowdate, nowtime, cstr, flt, now, getdate, add_months
from webnotes.model.doc import addchild
from webnotes import msgprint, _
from webnotes.utils import formatdate
@@ -12,6 +12,8 @@
class FiscalYearError(webnotes.ValidationError): pass
+class BudgetError(webnotes.ValidationError): pass
+
def get_fiscal_year(date=None, fiscal_year=None, label="Date", verbose=1):
return get_fiscal_years(date, fiscal_year, label, verbose=1)[0]
@@ -373,7 +375,6 @@
return difference
-
def validate_expense_against_budget(args):
args = webnotes._dict(args)
if webnotes.conn.get_value("Account", {"name": args.account, "is_pl_account": "Yes",
@@ -385,18 +386,60 @@
""", (args.cost_center, args.account, args.fiscal_year), as_dict=True)
if budget and budget[0].budget_allocated:
- action = webnotes.conn.get_value("Company", args.company,
+ yearly_action, monthly_action = webnotes.conn.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,
+ args.posting_date, args.fiscal_year, budget[0].budget_allocated)
- args["month_end_date"] = webnotes.conn.sql("select LAST_DAY(%s)", args.posting_date)
-
- expense_upto_date = get_actual_expense(args)
+ month_end_date = webnotes.conn.sql("select LAST_DAY(%s)", args.posting_date)
+ args["condition"] = " and posting_date<='%s'" % month_end_date
+ action_for, action = "Monthly", monthly_action
+
+ elif yearly_action in ["Stop", "Warn"]:
+ budget_amount = budget[0].budget_allocated
+ action_for, action = "Monthly", yearly_action
+ print budget_amount
+ if action_for:
+ actual_expense = get_actual_expense(args)
+ print actual_expense
+ if actual_expense > budget_amount:
+ webnotes.msgprint(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.")
+ , raise_exception=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 webnotes.conn.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)
+ print distribution
+ dt = webnotes.conn.get_value("Fiscal Year", fiscal_year, "year_start_date")
+ budget_percentage = 0.0
+
+ while(dt <= getdate(posting_date)):
+ print dt, posting_date
+ if distribution_id:
+ print getdate(dt).month
+ print distribution.get(getdate(dt).month)
+ budget_percentage += distribution.get(getdate(dt).month, 0)
+ else:
+ budget_percentage += 100.0/12
+
+ dt = add_months(dt, 1)
+
+ return yearly_budget * budget_percentage / 100
def get_actual_expense(args):
return webnotes.conn.sql("""
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where account=%(account)s and cost_center=%(cost_center)s
- and posting_date<=%(month_end_date)s
- and fiscal_year=%(fiscal_year)s and company=%(company)s
+ and fiscal_year=%(fiscal_year)s and company=%(company)s %(condition)s
""", (args))[0][0]
\ No newline at end of file
diff --git a/patches/august_2013/p06_deprecate_cancelled_sl_entry.py b/patches/august_2013/p06_deprecate_is_cancelled.py
similarity index 67%
rename from patches/august_2013/p06_deprecate_cancelled_sl_entry.py
rename to patches/august_2013/p06_deprecate_is_cancelled.py
index abb9d68..693a1ae 100644
--- a/patches/august_2013/p06_deprecate_cancelled_sl_entry.py
+++ b/patches/august_2013/p06_deprecate_is_cancelled.py
@@ -5,4 +5,7 @@
webnotes.reload_doc("stock", "report", "stock_ledger")
webnotes.conn.sql("""delete from `tabStock Ledger Entry`
- where ifnull(is_cancelled, 'No') = 'Yes'""")
\ No newline at end of file
+ where ifnull(is_cancelled, 'No') = 'Yes'""")
+
+ webnotes.reload_doc("stock", "doctype", "gl_entry")
+ webnotes.conn.sql("""delete from `tabGL Entry` where ifnull(is_cancelled, 'No') = 'Yes'""")
\ No newline at end of file
diff --git a/patches/patch_list.py b/patches/patch_list.py
index 46a40ac..8dd4c97 100644
--- a/patches/patch_list.py
+++ b/patches/patch_list.py
@@ -260,4 +260,6 @@
"patches.august_2013.p06_deprecate_cancelled_sl_entry",
"patches.august_2013.p06_fix_sle_against_stock_entry",
"execute:webnotes.bean('Style Settings').save() #2013-08-20",
+ "patches.august_2013.p06_deprecate_is_cancelled",
+ "execute:webnotes.delete_doc('DocType', 'Budget Control')",
]
\ No newline at end of file