Rushabh Mehta | ad45e31 | 2013-11-20 12:59:58 +0530 | [diff] [blame] | 1 | # Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors |
Rushabh Mehta | e67d1fb | 2013-08-05 14:59:54 +0530 | [diff] [blame] | 2 | # License: GNU General Public License v3. See license.txt |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 3 | |
| 4 | from __future__ import unicode_literals |
| 5 | |
| 6 | import webnotes |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 7 | from webnotes.utils import nowdate, nowtime, cstr, flt, now, getdate, add_months |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 8 | from webnotes.model.doc import addchild |
| 9 | from webnotes import msgprint, _ |
Rushabh Mehta | 2e8d078 | 2013-02-05 11:31:27 +0530 | [diff] [blame] | 10 | from webnotes.utils import formatdate |
Nabin Hait | a76a068 | 2013-02-08 14:04:13 +0530 | [diff] [blame] | 11 | from utilities import build_filter_conditions |
| 12 | |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 13 | |
| 14 | class FiscalYearError(webnotes.ValidationError): pass |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 15 | class BudgetError(webnotes.ValidationError): pass |
| 16 | |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 17 | |
Nabin Hait | cfecd2b | 2013-07-11 17:49:18 +0530 | [diff] [blame] | 18 | def get_fiscal_year(date=None, fiscal_year=None, label="Date", verbose=1): |
Nabin Hait | c38527e | 2013-12-23 17:07:57 +0530 | [diff] [blame] | 19 | return get_fiscal_years(date, fiscal_year, label, verbose)[0] |
Rushabh Mehta | c2563ef | 2013-02-05 23:25:37 +0530 | [diff] [blame] | 20 | |
Nabin Hait | cfecd2b | 2013-07-11 17:49:18 +0530 | [diff] [blame] | 21 | def get_fiscal_years(date=None, fiscal_year=None, label="Date", verbose=1): |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 22 | # if year start date is 2012-04-01, year end date should be 2013-03-31 (hence subdate) |
Nabin Hait | 0930b94 | 2013-06-20 16:35:09 +0530 | [diff] [blame] | 23 | cond = "" |
| 24 | if fiscal_year: |
| 25 | cond = "name = '%s'" % fiscal_year |
| 26 | else: |
Akhilesh Darjee | ec0da0b | 2013-11-25 19:51:18 +0530 | [diff] [blame] | 27 | cond = "'%s' >= year_start_date and '%s' <= year_end_date" % \ |
Nabin Hait | 0930b94 | 2013-06-20 16:35:09 +0530 | [diff] [blame] | 28 | (date, date) |
Akhilesh Darjee | ec0da0b | 2013-11-25 19:51:18 +0530 | [diff] [blame] | 29 | fy = webnotes.conn.sql("""select name, year_start_date, year_end_date |
| 30 | from `tabFiscal Year` where %s order by year_start_date desc""" % cond) |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 31 | |
| 32 | if not fy: |
Nabin Hait | cfecd2b | 2013-07-11 17:49:18 +0530 | [diff] [blame] | 33 | error_msg = """%s %s not in any Fiscal Year""" % (label, formatdate(date)) |
Nabin Hait | a4db83a | 2014-01-03 12:13:18 +0530 | [diff] [blame] | 34 | error_msg = """{msg}: {date}""".format(msg=_("Fiscal Year does not exist for date"), |
| 35 | date=formatdate(date)) |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 36 | if verbose: webnotes.msgprint(error_msg) |
| 37 | raise FiscalYearError, error_msg |
| 38 | |
Rushabh Mehta | c2563ef | 2013-02-05 23:25:37 +0530 | [diff] [blame] | 39 | return fy |
Rushabh Mehta | 2e8d078 | 2013-02-05 11:31:27 +0530 | [diff] [blame] | 40 | |
| 41 | def validate_fiscal_year(date, fiscal_year, label="Date"): |
Nabin Hait | cfecd2b | 2013-07-11 17:49:18 +0530 | [diff] [blame] | 42 | years = [f[0] for f in get_fiscal_years(date, label=label)] |
Rushabh Mehta | c2563ef | 2013-02-05 23:25:37 +0530 | [diff] [blame] | 43 | if fiscal_year not in years: |
Rushabh Mehta | 9607582 | 2013-02-05 11:39:49 +0530 | [diff] [blame] | 44 | webnotes.msgprint(("%(label)s '%(posting_date)s': " + _("not within Fiscal Year") + \ |
Rushabh Mehta | 2e8d078 | 2013-02-05 11:31:27 +0530 | [diff] [blame] | 45 | ": '%(fiscal_year)s'") % { |
| 46 | "label": label, |
| 47 | "posting_date": formatdate(date), |
| 48 | "fiscal_year": fiscal_year |
| 49 | }, raise_exception=1) |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 50 | |
| 51 | @webnotes.whitelist() |
| 52 | def get_balance_on(account=None, date=None): |
| 53 | if not account and webnotes.form_dict.get("account"): |
| 54 | account = webnotes.form_dict.get("account") |
| 55 | date = webnotes.form_dict.get("date") |
| 56 | |
| 57 | cond = [] |
| 58 | if date: |
| 59 | cond.append("posting_date <= '%s'" % date) |
| 60 | else: |
| 61 | # get balance of all entries that exist |
| 62 | date = nowdate() |
| 63 | |
| 64 | try: |
| 65 | year_start_date = get_fiscal_year(date, verbose=0)[1] |
| 66 | except FiscalYearError, e: |
| 67 | from webnotes.utils import getdate |
| 68 | if getdate(date) > getdate(nowdate()): |
| 69 | # if fiscal year not found and the date is greater than today |
| 70 | # get fiscal year for today's date and its corresponding year start date |
| 71 | year_start_date = get_fiscal_year(nowdate(), verbose=1)[1] |
| 72 | else: |
| 73 | # this indicates that it is a date older than any existing fiscal year. |
| 74 | # hence, assuming balance as 0.0 |
| 75 | return 0.0 |
| 76 | |
| 77 | acc = webnotes.conn.get_value('Account', account, \ |
| 78 | ['lft', 'rgt', 'debit_or_credit', 'is_pl_account', 'group_or_ledger'], as_dict=1) |
| 79 | |
| 80 | # for pl accounts, get balance within a fiscal year |
| 81 | if acc.is_pl_account == 'Yes': |
| 82 | cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \ |
| 83 | % year_start_date) |
| 84 | |
| 85 | # different filter for group and ledger - improved performance |
| 86 | if acc.group_or_ledger=="Group": |
| 87 | cond.append("""exists ( |
| 88 | select * from `tabAccount` ac where ac.name = gle.account |
| 89 | and ac.lft >= %s and ac.rgt <= %s |
| 90 | )""" % (acc.lft, acc.rgt)) |
| 91 | else: |
| 92 | cond.append("""gle.account = "%s" """ % (account, )) |
| 93 | |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 94 | bal = webnotes.conn.sql(""" |
| 95 | SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) |
| 96 | FROM `tabGL Entry` gle |
Nabin Hait | 9b09c95 | 2013-08-21 17:47:11 +0530 | [diff] [blame] | 97 | WHERE %s""" % " and ".join(cond))[0][0] |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 98 | |
| 99 | # if credit account, it should calculate credit - debit |
| 100 | if bal and acc.debit_or_credit == 'Credit': |
| 101 | bal = -bal |
| 102 | |
| 103 | # if bal is None, return 0 |
Anand Doshi | 6fa5e42 | 2013-07-19 15:33:11 +0530 | [diff] [blame] | 104 | return flt(bal) |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 105 | |
| 106 | @webnotes.whitelist() |
| 107 | def add_ac(args=None): |
| 108 | if not args: |
Rushabh Mehta | 8c2e4e1 | 2013-10-02 14:34:25 +0530 | [diff] [blame] | 109 | args = webnotes.local.form_dict |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 110 | args.pop("cmd") |
Rushabh Mehta | aae4553 | 2013-02-15 14:39:34 +0530 | [diff] [blame] | 111 | |
Rushabh Mehta | c53231a | 2013-02-18 18:24:28 +0530 | [diff] [blame] | 112 | ac = webnotes.bean(args) |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 113 | ac.doc.doctype = "Account" |
| 114 | ac.doc.old_parent = "" |
| 115 | ac.doc.freeze_account = "No" |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 116 | ac.insert() |
| 117 | return ac.doc.name |
| 118 | |
| 119 | @webnotes.whitelist() |
| 120 | def add_cc(args=None): |
| 121 | if not args: |
Rushabh Mehta | 8c2e4e1 | 2013-10-02 14:34:25 +0530 | [diff] [blame] | 122 | args = webnotes.local.form_dict |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 123 | args.pop("cmd") |
| 124 | |
Rushabh Mehta | c53231a | 2013-02-18 18:24:28 +0530 | [diff] [blame] | 125 | cc = webnotes.bean(args) |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 126 | cc.doc.doctype = "Cost Center" |
| 127 | cc.doc.old_parent = "" |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 128 | cc.insert() |
| 129 | return cc.doc.name |
| 130 | |
| 131 | def reconcile_against_document(args): |
| 132 | """ |
| 133 | Cancel JV, Update aginst document, split if required and resubmit jv |
| 134 | """ |
| 135 | for d in args: |
| 136 | check_if_jv_modified(d) |
| 137 | |
| 138 | against_fld = { |
| 139 | 'Journal Voucher' : 'against_jv', |
| 140 | 'Sales Invoice' : 'against_invoice', |
| 141 | 'Purchase Invoice' : 'against_voucher' |
| 142 | } |
| 143 | |
| 144 | d['against_fld'] = against_fld[d['against_voucher_type']] |
| 145 | |
| 146 | # cancel JV |
| 147 | jv_obj = webnotes.get_obj('Journal Voucher', d['voucher_no'], with_children=1) |
| 148 | jv_obj.make_gl_entries(cancel=1, adv_adj=1) |
| 149 | |
| 150 | # update ref in JV Detail |
| 151 | update_against_doc(d, jv_obj) |
| 152 | |
| 153 | # re-submit JV |
| 154 | jv_obj = webnotes.get_obj('Journal Voucher', d['voucher_no'], with_children =1) |
| 155 | jv_obj.make_gl_entries(cancel = 0, adv_adj =1) |
| 156 | |
| 157 | |
| 158 | def check_if_jv_modified(args): |
| 159 | """ |
| 160 | check if there is already a voucher reference |
| 161 | check if amount is same |
| 162 | check if jv is submitted |
| 163 | """ |
| 164 | ret = webnotes.conn.sql(""" |
| 165 | select t2.%(dr_or_cr)s from `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2 |
| 166 | where t1.name = t2.parent and t2.account = '%(account)s' |
| 167 | and ifnull(t2.against_voucher, '')='' |
| 168 | and ifnull(t2.against_invoice, '')='' and ifnull(t2.against_jv, '')='' |
| 169 | and t1.name = '%(voucher_no)s' and t2.name = '%(voucher_detail_no)s' |
| 170 | and t1.docstatus=1 and t2.%(dr_or_cr)s = %(unadjusted_amt)s""" % args) |
| 171 | |
| 172 | if not ret: |
| 173 | msgprint(_("""Payment Entry has been modified after you pulled it. |
| 174 | Please pull it again."""), raise_exception=1) |
| 175 | |
| 176 | def update_against_doc(d, jv_obj): |
| 177 | """ |
| 178 | Updates against document, if partial amount splits into rows |
| 179 | """ |
| 180 | |
| 181 | webnotes.conn.sql(""" |
| 182 | update `tabJournal Voucher Detail` t1, `tabJournal Voucher` t2 |
| 183 | set t1.%(dr_or_cr)s = '%(allocated_amt)s', |
| 184 | t1.%(against_fld)s = '%(against_voucher)s', t2.modified = now() |
| 185 | where t1.name = '%(voucher_detail_no)s' and t1.parent = t2.name""" % d) |
| 186 | |
| 187 | if d['allocated_amt'] < d['unadjusted_amt']: |
| 188 | jvd = webnotes.conn.sql("""select cost_center, balance, against_account, is_advance |
| 189 | from `tabJournal Voucher Detail` where name = %s""", d['voucher_detail_no']) |
| 190 | # new entry with balance amount |
| 191 | ch = addchild(jv_obj.doc, 'entries', 'Journal Voucher Detail') |
| 192 | ch.account = d['account'] |
| 193 | ch.cost_center = cstr(jvd[0][0]) |
| 194 | ch.balance = cstr(jvd[0][1]) |
| 195 | ch.fields[d['dr_or_cr']] = flt(d['unadjusted_amt']) - flt(d['allocated_amt']) |
| 196 | ch.fields[d['dr_or_cr']== 'debit' and 'credit' or 'debit'] = 0 |
| 197 | ch.against_account = cstr(jvd[0][2]) |
| 198 | ch.is_advance = cstr(jvd[0][3]) |
| 199 | ch.docstatus = 1 |
Nabin Hait | a76a068 | 2013-02-08 14:04:13 +0530 | [diff] [blame] | 200 | ch.save(1) |
| 201 | |
| 202 | def get_account_list(doctype, txt, searchfield, start, page_len, filters): |
| 203 | if not filters.get("group_or_ledger"): |
| 204 | filters["group_or_ledger"] = "Ledger" |
| 205 | |
| 206 | conditions, filter_values = build_filter_conditions(filters) |
| 207 | |
| 208 | return webnotes.conn.sql("""select name, parent_account from `tabAccount` |
| 209 | where docstatus < 2 %s and %s like %s order by name limit %s, %s""" % |
| 210 | (conditions, searchfield, "%s", "%s", "%s"), |
| 211 | tuple(filter_values + ["%%%s%%" % txt, start, page_len])) |
| 212 | |
| 213 | def get_cost_center_list(doctype, txt, searchfield, start, page_len, filters): |
| 214 | if not filters.get("group_or_ledger"): |
| 215 | filters["group_or_ledger"] = "Ledger" |
| 216 | |
| 217 | conditions, filter_values = build_filter_conditions(filters) |
| 218 | |
| 219 | return webnotes.conn.sql("""select name, parent_cost_center from `tabCost Center` |
| 220 | where docstatus < 2 %s and %s like %s order by name limit %s, %s""" % |
| 221 | (conditions, searchfield, "%s", "%s", "%s"), |
Anand Doshi | 7da72dd | 2013-03-20 17:00:41 +0530 | [diff] [blame] | 222 | tuple(filter_values + ["%%%s%%" % txt, start, page_len])) |
| 223 | |
| 224 | def remove_against_link_from_jv(ref_type, ref_no, against_field): |
| 225 | webnotes.conn.sql("""update `tabJournal Voucher Detail` set `%s`=null, |
| 226 | modified=%s, modified_by=%s |
| 227 | where `%s`=%s and docstatus < 2""" % (against_field, "%s", "%s", against_field, "%s"), |
| 228 | (now(), webnotes.session.user, ref_no)) |
| 229 | |
| 230 | webnotes.conn.sql("""update `tabGL Entry` |
| 231 | set against_voucher_type=null, against_voucher=null, |
| 232 | modified=%s, modified_by=%s |
| 233 | where against_voucher_type=%s and against_voucher=%s |
Nabin Hait | 9b09c95 | 2013-08-21 17:47:11 +0530 | [diff] [blame] | 234 | and voucher_no != ifnull(against_voucher, '')""", |
Anand Doshi | 7da72dd | 2013-03-20 17:00:41 +0530 | [diff] [blame] | 235 | (now(), webnotes.session.user, ref_type, ref_no)) |
Nabin Hait | 0fc2454 | 2013-03-25 11:06:00 +0530 | [diff] [blame] | 236 | |
| 237 | @webnotes.whitelist() |
| 238 | def get_company_default(company, fieldname): |
| 239 | value = webnotes.conn.get_value("Company", company, fieldname) |
| 240 | |
| 241 | if not value: |
| 242 | msgprint(_("Please mention default value for '") + |
| 243 | _(webnotes.get_doctype("company").get_label(fieldname) + |
| 244 | _("' in Company: ") + company), raise_exception=True) |
| 245 | |
| 246 | return value |
Nabin Hait | 8b509f5 | 2013-05-28 16:52:30 +0530 | [diff] [blame] | 247 | |
| 248 | def fix_total_debit_credit(): |
| 249 | vouchers = webnotes.conn.sql("""select voucher_type, voucher_no, |
| 250 | sum(debit) - sum(credit) as diff |
| 251 | from `tabGL Entry` |
| 252 | group by voucher_type, voucher_no |
| 253 | having sum(ifnull(debit, 0)) != sum(ifnull(credit, 0))""", as_dict=1) |
| 254 | |
| 255 | for d in vouchers: |
| 256 | if abs(d.diff) > 0: |
| 257 | dr_or_cr = d.voucher_type == "Sales Invoice" and "credit" or "debit" |
| 258 | |
| 259 | webnotes.conn.sql("""update `tabGL Entry` set %s = %s + %s |
| 260 | where voucher_type = %s and voucher_no = %s and %s > 0 limit 1""" % |
| 261 | (dr_or_cr, dr_or_cr, '%s', '%s', '%s', dr_or_cr), |
Nabin Hait | cac622e | 2013-08-02 11:44:29 +0530 | [diff] [blame] | 262 | (d.diff, d.voucher_type, d.voucher_no)) |
Nabin Hait | cac622e | 2013-08-02 11:44:29 +0530 | [diff] [blame] | 263 | |
Nabin Hait | 27994c2 | 2013-08-26 16:53:30 +0530 | [diff] [blame] | 264 | def get_stock_and_account_difference(account_list=None, posting_date=None): |
| 265 | from stock.utils import get_stock_balance_on |
| 266 | |
| 267 | if not posting_date: posting_date = nowdate() |
| 268 | |
Nabin Hait | cac622e | 2013-08-02 11:44:29 +0530 | [diff] [blame] | 269 | difference = {} |
Nabin Hait | 625da79 | 2013-09-25 10:32:51 +0530 | [diff] [blame] | 270 | |
| 271 | account_warehouse = dict(webnotes.conn.sql("""select name, master_name from tabAccount |
| 272 | where account_type = 'Warehouse' and ifnull(master_name, '') != '' |
| 273 | and name in (%s)""" % ', '.join(['%s']*len(account_list)), account_list)) |
Nabin Hait | cac622e | 2013-08-02 11:44:29 +0530 | [diff] [blame] | 274 | |
Nabin Hait | 625da79 | 2013-09-25 10:32:51 +0530 | [diff] [blame] | 275 | for account, warehouse in account_warehouse.items(): |
Nabin Hait | 27994c2 | 2013-08-26 16:53:30 +0530 | [diff] [blame] | 276 | account_balance = get_balance_on(account, posting_date) |
Nabin Hait | 625da79 | 2013-09-25 10:32:51 +0530 | [diff] [blame] | 277 | stock_value = get_stock_balance_on(warehouse, posting_date) |
Nabin Hait | aa34201 | 2013-08-07 14:21:04 +0530 | [diff] [blame] | 278 | if abs(flt(stock_value) - flt(account_balance)) > 0.005: |
Nabin Hait | 469ee71 | 2013-08-07 12:33:37 +0530 | [diff] [blame] | 279 | difference.setdefault(account, flt(stock_value) - flt(account_balance)) |
Nabin Hait | aa34201 | 2013-08-07 14:21:04 +0530 | [diff] [blame] | 280 | |
Nabin Hait | cc0e2d1 | 2013-08-06 16:19:18 +0530 | [diff] [blame] | 281 | return difference |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 282 | |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 283 | def validate_expense_against_budget(args): |
| 284 | args = webnotes._dict(args) |
| 285 | if webnotes.conn.get_value("Account", {"name": args.account, "is_pl_account": "Yes", |
| 286 | "debit_or_credit": "Debit"}): |
| 287 | budget = webnotes.conn.sql(""" |
| 288 | select bd.budget_allocated, cc.distribution_id |
| 289 | from `tabCost Center` cc, `tabBudget Detail` bd |
| 290 | where cc.name=bd.parent and cc.name=%s and account=%s and bd.fiscal_year=%s |
| 291 | """, (args.cost_center, args.account, args.fiscal_year), as_dict=True) |
| 292 | |
| 293 | if budget and budget[0].budget_allocated: |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 294 | yearly_action, monthly_action = webnotes.conn.get_value("Company", args.company, |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 295 | ["yearly_bgt_flag", "monthly_bgt_flag"]) |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 296 | action_for = action = "" |
| 297 | |
| 298 | if monthly_action in ["Stop", "Warn"]: |
| 299 | budget_amount = get_allocated_budget(budget[0].distribution_id, |
| 300 | args.posting_date, args.fiscal_year, budget[0].budget_allocated) |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 301 | |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 302 | args["month_end_date"] = webnotes.conn.sql("select LAST_DAY(%s)", |
| 303 | args.posting_date)[0][0] |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 304 | action_for, action = "Monthly", monthly_action |
| 305 | |
| 306 | elif yearly_action in ["Stop", "Warn"]: |
| 307 | budget_amount = budget[0].budget_allocated |
| 308 | action_for, action = "Monthly", yearly_action |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 309 | |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 310 | if action_for: |
| 311 | actual_expense = get_actual_expense(args) |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 312 | if actual_expense > budget_amount: |
| 313 | webnotes.msgprint(action_for + _(" budget ") + cstr(budget_amount) + |
| 314 | _(" for account ") + args.account + _(" against cost center ") + |
| 315 | args.cost_center + _(" will exceed by ") + |
| 316 | cstr(actual_expense - budget_amount) + _(" after this transaction.") |
| 317 | , raise_exception=BudgetError if action=="Stop" else False) |
| 318 | |
| 319 | def get_allocated_budget(distribution_id, posting_date, fiscal_year, yearly_budget): |
| 320 | if distribution_id: |
| 321 | distribution = {} |
| 322 | for d in webnotes.conn.sql("""select bdd.month, bdd.percentage_allocation |
| 323 | from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd |
| 324 | where bdd.parent=bd.name and bd.fiscal_year=%s""", fiscal_year, as_dict=1): |
| 325 | distribution.setdefault(d.month, d.percentage_allocation) |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 326 | |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 327 | dt = webnotes.conn.get_value("Fiscal Year", fiscal_year, "year_start_date") |
| 328 | budget_percentage = 0.0 |
| 329 | |
| 330 | while(dt <= getdate(posting_date)): |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 331 | if distribution_id: |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 332 | budget_percentage += distribution.get(getdate(dt).strftime("%B"), 0) |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 333 | else: |
| 334 | budget_percentage += 100.0/12 |
| 335 | |
| 336 | dt = add_months(dt, 1) |
| 337 | |
| 338 | return yearly_budget * budget_percentage / 100 |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 339 | |
| 340 | def get_actual_expense(args): |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 341 | args["condition"] = " and posting_date<='%s'" % args.month_end_date \ |
| 342 | if args.get("month_end_date") else "" |
| 343 | |
Nabin Hait | b4418a4 | 2013-08-22 14:38:46 +0530 | [diff] [blame] | 344 | return webnotes.conn.sql(""" |
| 345 | select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) |
| 346 | from `tabGL Entry` |
Nabin Hait | a391606 | 2013-08-23 10:46:41 +0530 | [diff] [blame] | 347 | where account='%(account)s' and cost_center='%(cost_center)s' |
| 348 | and fiscal_year='%(fiscal_year)s' and company='%(company)s' %(condition)s |
Nabin Hait | 131939a | 2013-11-26 15:03:51 +0530 | [diff] [blame] | 349 | """ % (args))[0][0] |
| 350 | |
| 351 | def rename_account_for(dt, olddn, newdn, merge): |
| 352 | old_account = get_account_for(dt, olddn) |
| 353 | if old_account: |
| 354 | new_account = None |
| 355 | if not merge: |
| 356 | if old_account == olddn: |
| 357 | new_account = webnotes.rename_doc("Account", olddn, newdn) |
| 358 | else: |
| 359 | existing_new_account = get_account_for(dt, newdn) |
| 360 | new_account = webnotes.rename_doc("Account", old_account, |
| 361 | existing_new_account or newdn, merge=True if existing_new_account else False) |
| 362 | |
| 363 | if new_account: |
| 364 | webnotes.conn.set_value("Account", new_account, "master_name", newdn) |
| 365 | |
| 366 | def get_account_for(account_for_doctype, account_for): |
| 367 | if account_for_doctype in ["Customer", "Supplier"]: |
| 368 | account_for_field = "master_type" |
| 369 | elif account_for_doctype == "Warehouse": |
| 370 | account_for_field = "account_type" |
| 371 | |
| 372 | return webnotes.conn.get_value("Account", {account_for_field: account_for_doctype, |
Nabin Hait | ac9bcf7 | 2013-11-26 01:57:17 -0800 | [diff] [blame] | 373 | "master_name": account_for}) |