deprecated account balance and fixed expiry msg
diff --git a/erpnext/accounts/__init__.py b/erpnext/accounts/__init__.py
index aa2af62..3fdf4ef 100644
--- a/erpnext/accounts/__init__.py
+++ b/erpnext/accounts/__init__.py
@@ -18,6 +18,7 @@
import webnotes
from webnotes.utils import flt
from webnotes.model.code import get_obj
+from accounts.utils import get_balance_on
install_docs = [
{"doctype":"Role", "role_name":"Accounts Manager", "name":"Accounts Manager"},
@@ -107,7 +108,7 @@
ret.update({
'account': account,
- 'balance': get_obj('GL Control').get_bal(account + "~~~" + fiscal_year)
+ 'balance': get_balance_on(account, doc.get("return_date"))
})
return ret
@@ -140,7 +141,8 @@
rec = {
'account': inv_ch.fields.get(ac_field),
'cost_center': inv_ch.fields.get('cost_center'),
- 'balance': get_obj('GL Control').get_bal(inv_ch.fields.get(ac_field) + "~~~" + fiscal_year)
+ 'balance': get_balance_on(inv_ch.fields.get(ac_field),
+ doc.get("return_date"))
}
rec[amt_field] = amount
accwise_list.append(rec)
diff --git a/erpnext/accounts/doctype/account/account.py b/erpnext/accounts/doctype/account/account.py
index 79ca322..4ad8cb9 100644
--- a/erpnext/accounts/doctype/account/account.py
+++ b/erpnext/accounts/doctype/account/account.py
@@ -25,14 +25,8 @@
from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
from webnotes import session, form, is_testing, msgprint, errprint
-set = webnotes.conn.set
sql = webnotes.conn.sql
get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
-
-# -----------------------------------------------------------------------------------------
-
class DocType:
def __init__(self,d,dl):
@@ -49,7 +43,6 @@
ret={'address':add[0][0]}
return ret
-
# check whether master name entered for supplier/customer
def validate_master_name(self):
if (self.doc.master_type == 'Customer' or self.doc.master_type == 'Supplier') and not self.doc.master_name:
@@ -86,7 +79,6 @@
if (self.doc.fields.get('__islocal') or (not self.doc.name)) and sql("select name from tabAccount where account_name=%s and company=%s", (self.doc.account_name, self.doc.company)):
msgprint("Account Name already exists, please rename", raise_exception=1)
- # validate root details
def validate_root_details(self):
#does not exists parent
if self.doc.account_name in ['Income','Source of Funds', 'Expenses','Application of Funds'] and self.doc.parent_account:
@@ -104,7 +96,6 @@
elif self.doc.account_name in ['Source of Funds','Application of Funds']:
self.doc.is_pl_account = 'No'
- # Convert group to ledger
def convert_group_to_ledger(self):
if self.check_if_child_exists():
msgprint("Account: %s has existing child. You can not convert this account to ledger" % (self.doc.name), raise_exception=1)
@@ -115,7 +106,6 @@
self.doc.save()
return 1
- # Convert ledger to group
def convert_ledger_to_group(self):
if self.check_gle_exists():
msgprint("Account with existing transaction can not be converted to group.",
@@ -133,47 +123,15 @@
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 ''
- # check if child exists
def check_if_child_exists(self):
return sql("select name from `tabAccount` where parent_account = %s and docstatus != 2", self.doc.name)
- # Update balance
- def update_balance(self, fy, period_det, flag = 1):
- # update in all parents
- for p in period_det:
- sql("update `tabAccount Balance` t1, `tabAccount` t2 set t1.balance = t1.balance + (%s), t1.opening = t1.opening + (%s), t1.debit = t1.debit + (%s), t1.credit = t1.credit + (%s) where t1.period = %s and t1.account = t2.name and t2.lft<=%s and t2.rgt>=%s", (flt(flag)*flt(p[1]), flt(flag)*flt(p[2]), flt(flag)*flt(p[3]), flt(flag)*flt(p[4]), p[0], self.doc.lft, self.doc.rgt))
-
-
- # change parent balance
- def change_parent_bal(self):
- period_det = []
- fy = sql("select name from `tabFiscal Year` where if(ifnull(is_fiscal_year_closed, 'No'),ifnull(is_fiscal_year_closed, 'No'), 'No') = 'No'")
- for f in fy:
- # get my opening, balance
- per = sql("select period, balance, opening, debit, credit from `tabAccount Balance` where account = %s and fiscal_year = %s", (self.doc.name, f[0]))
- for p in per:
- period_det.append([p[0], p[1], p[2], p[3], p[4]])
-
- # deduct balance from old_parent
- op = get_obj('Account',self.doc.old_parent)
- op.update_balance(fy, period_det, -1)
-
- # add to new parent_account
- flag = 1
- if op.doc.debit_or_credit != self.doc.debit_or_credit:
- flag = -1
-
- get_obj('Account', self.doc.parent_account).update_balance(fy, period_det, flag)
- msgprint('Balances updated')
-
def validate_mandatory(self):
if not self.doc.debit_or_credit:
msgprint("Debit or Credit field is mandatory", raise_exception=1)
if not self.doc.is_pl_account:
msgprint("Is PL Account field is mandatory", raise_exception=1)
-
- # VALIDATE
def validate(self):
self.validate_master_name()
self.validate_rate_for_tax()
@@ -185,27 +143,6 @@
# Defaults
if not self.doc.parent_account:
self.doc.parent_account = ''
-
- # parent changed
- if self.doc.old_parent and self.doc.parent_account and (self.doc.parent_account != self.doc.old_parent):
- self.change_parent_bal()
-
- # Add current fiscal year balance
- def set_year_balance(self):
- p = sql("select name, start_date, end_date, fiscal_year from `tabPeriod` where docstatus != 2 and period_type in ('Month', 'Year')")
- for d in p:
- if not sql("select name from `tabAccount Balance` where account=%s and period=%s", (self.doc.name, d[0])):
- ac = Document('Account Balance')
- ac.account = self.doc.name
- ac.period = d[0]
- ac.start_date = d[1].strftime('%Y-%m-%d')
- ac.end_date = d[2].strftime('%Y-%m-%d')
- ac.fiscal_year = d[3]
- ac.opening = 0
- ac.debit = 0
- ac.credit = 0
- ac.balance = 0
- ac.save(1)
# Update Node Set Model
def update_nsm_model(self):
@@ -213,13 +150,9 @@
import webnotes.utils.nestedset
webnotes.utils.nestedset.update_nsm(self)
- # ON UPDATE
def on_update(self):
-
# update nsm
self.update_nsm_model()
- # Add curret year balance
- self.set_year_balance()
# Check user role for approval process
def get_authorized_user(self):
@@ -243,49 +176,31 @@
msgprint("Total Outstanding amount (%s) for <b>%s</b> can not be greater than credit limit (%s). To change your credit limit settings, please update the <b>%s</b>" \
% (fmt_money(tot_outstanding), account, fmt_money(credit_limit), credit_limit_from), raise_exception=1)
- # Account with balance cannot be inactive
- def check_balance_before_trash(self):
+ def validate_trash(self):
+ """checks gl entries and if child exists"""
if self.check_gle_exists():
msgprint("Account with existing transaction (Sales Invoice / Purchase Invoice / Journal Voucher) can not be trashed", raise_exception=1)
if self.check_if_child_exists():
msgprint("Child account exists for this account. You can not trash this account.", raise_exception=1)
-
- # get current year balance
- def get_curr_bal(self):
- bal = sql("select balance from `tabAccount Balance` where period = '%s' and parent = '%s'" % (get_defaults()['fiscal_year'], self.doc.name),debug=0)
- return bal and flt(bal[0][0]) or 0
-
# On Trash
def on_trash(self):
- # Check balance before trash
- self.check_balance_before_trash()
+ self.validate_trash()
# rebuild tree
- from webnotes.utils.nestedset import update_remove_node
- update_remove_node('Account', self.doc.name)
+ 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)
- #delete Account Balance
- sql("delete from `tabAccount Balance` where account = %s", self.doc.name)
-
- # On restore
- def on_restore(self):
- # rebuild tree
- self.update_nsm_model()
- # intiate balances
- self.set_year_balance()
-
# on rename
def on_rename(self,newdn,olddn):
company_abbr = sql("select tc.abbr from `tabAccount` ta, `tabCompany` tc where ta.company = tc.name and ta.name=%s", olddn)[0][0]
newdnchk = newdn.split(" - ")
- if newdnchk[-1].lower() != company_abbr.lower():
+ if newdnchk[-1].lower() != company_abbr.lower():
msgprint("Please add company abbreviation <b>%s</b>" %(company_abbr), raise_exception=1)
else:
account_name = " - ".join(newdnchk[:-1])
- sql("update `tabAccount` set account_name = '%s' where name = '%s'" %(account_name,olddn))
+ sql("update `tabAccount` set account_name = '%s' where name = '%s'" %(account_name,olddn))
diff --git a/erpnext/accounts/doctype/account_balance/__init__.py b/erpnext/accounts/doctype/account_balance/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/accounts/doctype/account_balance/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/doctype/account_balance/account_balance.txt b/erpnext/accounts/doctype/account_balance/account_balance.txt
deleted file mode 100644
index 903d270..0000000
--- a/erpnext/accounts/doctype/account_balance/account_balance.txt
+++ /dev/null
@@ -1,177 +0,0 @@
-# DocType, Account Balance
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:35:40',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:35:40',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all DocType
- {
- '_last_update': u'1322549700',
- 'autoname': u'_ACB.######',
- 'colour': u'White:FFF',
- 'default_print_format': u'Standard',
- 'doctype': 'DocType',
- 'istable': 0,
- 'module': u'Accounts',
- 'name': '__common__',
- 'search_fields': u'account, period, fiscal_year, balance',
- 'section_style': u'Simple',
- 'server_code_error': u' ',
- 'show_in_menu': 0,
- 'version': 6
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Account Balance',
- 'parentfield': u'fields',
- 'parenttype': u'DocType',
- 'permlevel': 0
- },
-
- # These values are common for all DocPerm
- {
- 'amend': 0,
- 'cancel': 0,
- 'create': 0,
- 'doctype': u'DocPerm',
- 'name': '__common__',
- 'parent': u'Account Balance',
- 'parentfield': u'permissions',
- 'parenttype': u'DocType',
- 'permlevel': 0,
- 'read': 1,
- 'submit': 0,
- 'write': 0
- },
-
- # DocType, Account Balance
- {
- 'doctype': 'DocType',
- 'name': u'Account Balance'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm',
- 'role': u'Accounts User'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm',
- 'role': u'Accounts Manager'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'account',
- 'fieldtype': u'Link',
- 'in_filter': 1,
- 'label': u'Account',
- 'options': u'Account',
- 'search_index': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'period',
- 'fieldtype': u'Link',
- 'in_filter': 1,
- 'label': u'Period',
- 'oldfieldname': u'period',
- 'oldfieldtype': u'Link',
- 'options': u'Period',
- 'search_index': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'opening',
- 'fieldtype': u'Currency',
- 'label': u'Opening',
- 'oldfieldname': u'opening',
- 'oldfieldtype': u'Currency'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'debit',
- 'fieldtype': u'Currency',
- 'label': u'Debit',
- 'oldfieldname': u'debit',
- 'oldfieldtype': u'Currency'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'credit',
- 'fieldtype': u'Currency',
- 'label': u'Credit',
- 'oldfieldname': u'credit',
- 'oldfieldtype': u'Currency'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'balance',
- 'fieldtype': u'Currency',
- 'label': u'Balance',
- 'oldfieldname': u'balance',
- 'oldfieldtype': u'Currency'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'fiscal_year',
- 'fieldtype': u'Link',
- 'hidden': 1,
- 'in_filter': 1,
- 'label': u'Fiscal Year',
- 'oldfieldname': u'fiscal_year',
- 'oldfieldtype': u'Link',
- 'options': u'Fiscal Year',
- 'search_index': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'start_date',
- 'fieldtype': u'Date',
- 'hidden': 1,
- 'in_filter': 1,
- 'label': u'Start Date',
- 'oldfieldname': u'start_date',
- 'oldfieldtype': u'Date',
- 'search_index': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'end_date',
- 'fieldtype': u'Date',
- 'hidden': 1,
- 'in_filter': 1,
- 'label': u'End Date',
- 'oldfieldname': u'end_date',
- 'oldfieldtype': u'Date',
- 'search_index': 1
- }
-]
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/fiscal_year/fiscal_year.py b/erpnext/accounts/doctype/fiscal_year/fiscal_year.py
deleted file mode 100644
index 3ef2776..0000000
--- a/erpnext/accounts/doctype/fiscal_year/fiscal_year.py
+++ /dev/null
@@ -1,221 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-# Please edit this list and import only required elements
-from __future__ import unicode_literals
-import webnotes
-
-from webnotes.utils import add_days, add_months, add_years, cint, cstr, date_diff, default_fields, flt, fmt_money, formatdate, generate_hash, getTraceback, get_defaults, get_first_day, get_last_day, getdate, has_common, month_name, now, nowdate, replace_newlines, sendmail, set_default, str_esc_quote, user_format, validate_email_add
-from webnotes.model import db_exists
-from webnotes.model.doc import Document, addchild, getchildren, make_autoname
-from webnotes.model.doclist import getlist, copy_doclist
-from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
-from webnotes import session, form, is_testing, msgprint, errprint
-
-set = webnotes.conn.set
-sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
-
-# -----------------------------------------------------------------------------------------
-
-
-class DocType:
- def __init__(self, d, dl):
- self.doc, self.doclist = d,dl
-
- def repost(self):
- if not self.doc.company:
- msgprint("Please select company", raise_exception=1)
-
- if not in_transaction:
- sql("start transaction")
-
- self.rebuid_account_tree()
- self.clear_account_balances()
- self.create_account_balances()
- self.update_opening(self.doc.company)
- self.post_entries()
- sql("commit")
-
- msgprint("Account balance reposted for fiscal year: " + self.doc.name)
-
- def rebuid_account_tree(self):
- from webnotes.utils.nestedset import rebuild_tree
- rebuild_tree('Account', 'parent_account')
-
- def clear_account_balances(self):
- # balances clear - `tabAccount Balance` for fiscal year
- sql("update `tabAccount Balance` t1, tabAccount t2 set t1.opening=0, t1.balance=0, t1.debit=0, t1.credit=0 where t1.fiscal_year=%s and t2.company = %s and t1.account = t2.name", (self.doc.name, self.doc.company))
-
- def create_account_balances(self):
- # get periods
- period_list = self.get_period_list()
- cnt = 0
-
- # get accounts
- al = sql("select name from tabAccount")
-
- for a in al:
- # check
- if sql("select count(*) from `tabAccount Balance` where fiscal_year=%s and account=%s", (self.doc.name, a[0]))[0][0] < 13:
- for p in period_list:
- # check if missing
- if not sql("select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.name)):
- d = Document('Account Balance')
- d.account = a[0]
- d.period = p[0]
- d.start_date = p[1].strftime('%Y-%m-%d')
- d.end_date = p[2].strftime('%Y-%m-%d')
- d.fiscal_year = p[3]
- d.debit = 0
- d.credit = 0
- d.opening = 0
- d.balance = 0
- d.save(1)
- cnt += 1
- if cnt % 100 == 0:
- sql("commit")
- sql("start transaction")
- return cnt
-
- # Get periods(month and year)
- #=================================
- def get_period_list(self):
- periods = []
- pl = sql("SELECT name, start_date, end_date, fiscal_year FROM tabPeriod WHERE fiscal_year = '%s' and period_type in ('Month', 'Year') order by start_date ASC, end_date DESC" % self.doc.name)
- for p in pl:
- periods.append([p[0], p[1], p[2], p[3]])
- return periods
-
- # ====================================================================================
- def update_opening(self, company):
- """
- set opening from last year closing
-
- """
-
- abl = sql("select t1.account, t1.balance from `tabAccount Balance` t1, tabAccount t2 where t1.period= '%s' and t2.company= '%s' and ifnull(t2.is_pl_account, 'No') = 'No' and t1.account = t2.name for update" % (self.doc.past_year, company))
-
- cnt = 0
- for ab in abl:
- if cnt % 100 == 0:
- sql("commit")
- sql("start transaction")
-
- sql("update `tabAccount Balance` set opening=%s where period=%s and account=%s", (ab[1], self.doc.name, ab[0]))
- sql("update `tabAccount Balance` set balance=%s where fiscal_year=%s and account=%s", (ab[1], self.doc.name, ab[0]))
- cnt += 1
-
- return cnt
-
- def get_account_details(self, account):
- return sql("select debit_or_credit, lft, rgt, is_pl_account from tabAccount where name=%s", account)[0]
-
- # ====================================================================================
- def post_entries(self):
- sql("LOCK TABLE `tabGL Entry` WRITE")
- # post each gl entry (batch or complete)
- gle = sql("select name, account, debit, credit, is_opening, posting_date from `tabGL Entry` where fiscal_year=%s and ifnull(is_cancelled,'No')='No' and company=%s", (self.doc.name, self.doc.company))
- account_details = {}
-
- cnt = 0
- for entry in gle:
- # commit in batches of 100
- if cnt % 100 == 0:
- sql("commit")
- sql("start transaction")
- cnt += 1
- #print cnt
-
- if not account_details.has_key(entry[1]):
- account_details[entry[1]] = self.get_account_details(entry[1])
-
- det = account_details[entry[1]]
- diff = flt(entry[2])-flt(entry[3])
- if det[0]=='Credit': diff = -diff
-
- # build dict
- p = {
- 'debit': entry[4]=='No' and flt(entry[2]) or 0
- ,'credit': entry[4]=='No' and flt(entry[3]) or 0
- ,'opening': entry[4]=='Yes' and diff or 0
-
- # end date conditino only if it is not opening
- ,'end_date_condition':(entry[4]!='Yes' and ("and ab.end_date >= '"+entry[5].strftime('%Y-%m-%d')+"'") or '')
- ,'diff': diff
- ,'lft': det[1]
- ,'rgt': det[2]
- ,'posting_date': entry[5]
- ,'fiscal_year': self.doc.name
- }
-
- sql("""update `tabAccount Balance` ab, `tabAccount` a
- set
- ab.debit = ifnull(ab.debit,0) + %(debit)s
- ,ab.credit = ifnull(ab.credit,0) + %(credit)s
- ,ab.opening = ifnull(ab.opening,0) + %(opening)s
- ,ab.balance = ifnull(ab.balance,0) + %(diff)s
- where
- a.lft <= %(lft)s
- and a.rgt >= %(rgt)s
- and ab.account = a.name
- %(end_date_condition)s
- and ab.fiscal_year = '%(fiscal_year)s' """ % p)
-
- sql("UNLOCK TABLES")
-
-
- # Clear PV/RV outstanding
- # ====================================================================================
- def clear_outstanding(self):
- # clear o/s of current year
- sql("update `tabPurchase Invoice` set outstanding_amount = 0 where fiscal_year=%s and company=%s", (self.doc.name, self.doc.company))
- sql("update `tabSales Invoice` set outstanding_amount = 0 where fiscal_year=%s and company=%s", (self.doc.name, self.doc.company))
-
- # Update Voucher Outstanding
- def update_voucher_outstanding(self):
- # Clear outstanding
- self.clear_outstanding()
- against_voucher = sql("select against_voucher, against_voucher_type from `tabGL Entry` where fiscal_year=%s and ifnull(is_cancelled, 'No')='No' and company=%s and ifnull(against_voucher, '') != '' and ifnull(against_voucher_type, '') != '' group by against_voucher, against_voucher_type", (self.doc.name, self.doc.company))
- for d in against_voucher:
- # get voucher balance
- bal = sql("select sum(debit)-sum(credit) from `tabGL Entry` where against_voucher=%s and against_voucher_type=%s and ifnull(is_cancelled, 'No') = 'No'", (d[0], d[1]))
- bal = bal and flt(bal[0][0]) or 0.0
- if d[1] == 'Purchase Invoice':
- bal = -bal
- # set voucher balance
- sql("update `tab%s` set outstanding_amount=%s where name='%s'"% (d[1], bal, d[0]))
-
- # ====================================================================================
- # Generate periods
- def create_periods(self):
- get_obj('Period Control').generate_periods(self.doc.name)
-
- def validate(self):
- if sql("select name from `tabFiscal Year` where year_start_date < %s", self.doc.year_start_date) and not self.doc.past_year:
- msgprint("Please enter Past Year", raise_exception=1)
-
-
- # on update
- def on_update(self):
- self.create_periods()
- self.create_account_balances()
-
- if self.doc.fields.get('localname', '')[:15] == 'New Fiscal Year':
- for d in sql("select name from tabCompany"):
- self.update_opening(d[0])
diff --git a/erpnext/accounts/doctype/gl_control/gl_control.py b/erpnext/accounts/doctype/gl_control/gl_control.py
index bd10d6c..ae89a66 100644
--- a/erpnext/accounts/doctype/gl_control/gl_control.py
+++ b/erpnext/accounts/doctype/gl_control/gl_control.py
@@ -41,36 +41,6 @@
dcc = TransactionBase().get_company_currency(arg)
return dcc
- # Get current balance
- # --------------------
- def get_bal(self,arg):
- ac, fy = arg.split('~~~')
- det = webnotes.conn.sql("select t1.balance, t2.debit_or_credit from `tabAccount Balance` t1, `tabAccount` t2 where t1.period = %s and t2.name=%s and t1.account = t2.name", (fy, ac))
- bal = det and flt(det[0][0]) or 0
- dr_or_cr = det and flt(det[0][1]) or ''
- return fmt_money(bal) + ' ' + dr_or_cr
-
- def get_period_balance(self,arg):
- acc, f, t = arg.split('~~~')
- c, fy = '', 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)
- bal = webnotes.conn.sql("select sum(ifnull(t1.debit,0))-sum(ifnull(t1.credit,0)) from `tabGL Entry` t1 where t1.account='%s' and ifnull(is_opening, 'No') = 'No' %s" % (acc, c))
- bal = bal and flt(bal[0][0]) or 0
-
- if det[0][0] != 'Debit':
- bal = (-1) * bal
-
- # add opening for balance sheet accounts
- if det[0][3] == 'No':
- opening = flt(webnotes.conn.sql("select opening from `tabAccount Balance` where account=%s and period=%s", (acc, fy))[0][0])
- bal = bal + opening
-
- return flt(bal)
-
-
def get_period_difference(self,arg, cost_center =''):
# used in General Ledger Page Report
# used for Budget where cost center passed as extra argument
@@ -89,32 +59,6 @@
return flt(bal)
- # Get Children (for tree)
- # -----------------------
- def get_cl(self, arg):
-
- fy = get_defaults()['fiscal_year']
- parent, parent_acc_name, company, type = arg.split(',')
-
- # get children account details
- if type=='Account':
-
- if parent=='Root Node':
-
- cl = webnotes.conn.sql("select t1.name, t1.group_or_ledger, t1.debit_or_credit, t2.balance, t1.account_name from tabAccount t1, `tabAccount Balance` t2 where ifnull(t1.parent_account, '') = '' and t1.docstatus != 2 and t1.company=%s and t1.name = t2.account and t2.period = %s order by t1.name asc", (company, fy),as_dict=1)
- else:
- cl = webnotes.conn.sql("select t1.name, t1.group_or_ledger, t1.debit_or_credit, t2.balance, t1.account_name from tabAccount t1, `tabAccount Balance` t2 where ifnull(t1.parent_account, '')=%s and t1.docstatus != 2 and t1.company=%s and t1.name = t2.account and t2.period = %s order by t1.name asc",(parent, company, fy) ,as_dict=1)
-
- # remove Decimals
- for c in cl: c['balance'] = flt(c['balance'])
-
- # get children cost center details
- elif type=='Cost Center':
- if parent=='Root Node':
- cl = webnotes.conn.sql("select name,group_or_ledger, cost_center_name from `tabCost Center` where ifnull(parent_cost_center, '')='' and docstatus != 2 and company_name=%s order by name asc",(company),as_dict=1)
- else:
- cl = webnotes.conn.sql("select name,group_or_ledger,cost_center_name from `tabCost Center` where ifnull(parent_cost_center, '')=%s and docstatus != 2 and company_name=%s order by name asc",(parent,company),as_dict=1)
- return {'parent':parent, 'parent_acc_name':parent_acc_name, 'cl':cl}
# Add a new account
# -----------------
@@ -254,39 +198,10 @@
# set as cancelled
if cancel:
- vt, vn = self.get_val(le_map['voucher_type'], doc, doc), self.get_val(le_map['voucher_no'], doc, doc)
+ vt = self.get_val(le_map['voucher_type'], doc, doc)
+ vn = self.get_val(le_map['voucher_no'], doc, doc)
webnotes.conn.sql("update `tabGL Entry` set is_cancelled='Yes' where voucher_type=%s and voucher_no=%s", (vt, vn))
-
- # Get account balance on any date
- # -------------------------------
- def get_as_on_balance(self, account_name, fiscal_year, as_on, credit_or_debit, lft, rgt):
- # initialization
- det = webnotes.conn.sql("select start_date, opening from `tabAccount Balance` where period = %s and account = %s", (fiscal_year, account_name))
- from_date, opening, debit_bal, credit_bal, closing_bal = det and det[0][0] or getdate(nowdate()), det and flt(det[0][1]) or 0, 0, 0, det and flt(det[0][1]) or 0
-
- # prev month closing
- prev_month_det = webnotes.conn.sql("select end_date, debit, credit, balance from `tabAccount Balance` where account = %s and end_date <= %s and fiscal_year = %s order by end_date desc limit 1", (account_name, as_on, fiscal_year))
- if prev_month_det:
- from_date = getdate(add_days(prev_month_det[0][0].strftime('%Y-%m-%d'), 1))
- opening = 0
- debit_bal = flt(prev_month_det[0][1])
- credit_bal = flt(prev_month_det[0][2])
- closing_bal = flt(prev_month_det[0][3])
-
- # curr month transaction
- if getdate(as_on) >= from_date:
- curr_month_bal = webnotes.conn.sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s and ifnull(t1.is_opening, 'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_cancelled, 'No') = 'No'", (from_date, as_on, lft, rgt))
- curr_debit_amt, curr_credit_amt = flt(curr_month_bal[0][0]), flt(curr_month_bal[0][1])
- debit_bal = curr_month_bal and debit_bal + curr_debit_amt or debit_bal
- credit_bal = curr_month_bal and credit_bal + curr_credit_amt or credit_bal
-
- if credit_or_debit == 'Credit':
- curr_debit_amt, curr_credit_amt = -1*flt(curr_month_bal[0][0]), -1*flt(curr_month_bal[0][1])
- closing_bal = closing_bal + curr_debit_amt - curr_credit_amt
-
- return flt(debit_bal), flt(credit_bal), flt(closing_bal)
-
-
+
# ADVANCE ALLOCATION
#-------------------
def get_advances(self, obj, account_head, table_name,table_field_name, dr_or_cr):
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index 85670ab..563f00c 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -121,117 +121,8 @@
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 accounting entries before %s." % getdate(acc_frozen_upto).strftime('%d-%m-%Y'), raise_exception=1)
- # create new bal if not exists
- #-----------------------------
- def create_new_balances(self, det):
- # check
- if sql("select count(t1.name) from `tabAccount Balance` t1, tabAccount t2 where t1.fiscal_year=%s and t2.lft <= %s and t2.rgt >= %s and t2.name = t1.account", (self.doc.fiscal_year, det[0][0], det[0][1]))[0][0] < 13*(cint(det[0][1]) - cint(det[0][0]) +1)/2:
- period_list = self.get_period_list()
- accounts = sql("select name from tabAccount where lft <= %s and rgt >= %s" % (det[0][0], det[0][1]))
-
- for p in period_list:
- for a in accounts:
- # check if missing
- if not sql("select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.fiscal_year)):
- d = Document('Account Balance')
- d.account = a[0]
- d.period = p[0]
- d.start_date = p[1].strftime('%Y-%m-%d')
- d.end_date = p[2].strftime('%Y-%m-%d')
- d.fiscal_year = self.doc.fiscal_year
- d.debit = 0
- d.credit = 0
- d.opening = 0
- d.balance = 0
- d.save(1)
-
- # Post Balance
- # ------------
- def post_balance(self, acc, cancel):
- # get details
- det = sql("select lft, rgt, debit_or_credit from `tabAccount` where name='%s'" % acc)
-
- # amount to debit
- amt = flt(self.doc.debit) - flt(self.doc.credit)
- if det[0][2] == 'Credit': amt = -amt
-
- if cancel:
- debit = -1 * flt(self.doc.credit)
- credit = -1 * flt(self.doc.debit)
- else:
- debit = flt(self.doc.debit)
- credit = flt(self.doc.credit)
-
- self.create_new_balances(det)
-
- # build dict
- p = {
- 'debit': self.doc.is_opening=='No' and flt(debit) or 0
- ,'credit':self.doc.is_opening=='No' and flt(credit) or 0
- ,'opening': self.doc.is_opening=='Yes' and amt or 0
- # end date condition only if it is not opening
- ,'end_date_condition':(self.doc.is_opening!='Yes' and ("and ab.end_date >= '"+self.doc.posting_date+"'") or '')
- ,'diff': amt
- ,'lft': cint(det[0][0])
- ,'rgt': cint(det[0][1])
- ,'posting_date': self.doc.posting_date
- ,'fiscal_year': self.doc.fiscal_year
- }
-
- # Update account balance for current year
- sql("""update `tabAccount Balance` ab, `tabAccount` a
- set
- ab.debit = ifnull(ab.debit,0) + %(debit)s
- ,ab.credit = ifnull(ab.credit,0) + %(credit)s
- ,ab.opening = ifnull(ab.opening,0) + %(opening)s
- ,ab.balance = ifnull(ab.balance,0) + %(diff)s
- where
- a.lft <= %(lft)s
- and a.rgt >= %(rgt)s
- and ab.account = a.name
- %(end_date_condition)s
- and ab.fiscal_year = '%(fiscal_year)s' """ % p)
-
- # Future year balances
- # Update opening only where period_type is Year
- sql("""update `tabAccount Balance` ab, `tabAccount` a, `tabFiscal Year` fy
- set
- ab.opening = ifnull(ab.opening,0) + %(diff)s
- where
- a.lft <= %(lft)s
- and a.rgt >= %(rgt)s
- and ab.account = a.name
- and ifnull(a.is_pl_account, 'No') = 'No'
- and ab.period = ab.fiscal_year
- and fy.name = ab.fiscal_year
- and fy.year_start_date > '%(posting_date)s'""" % p)
-
- # Update balance for all period for future years
- sql("""update `tabAccount Balance` ab, `tabAccount` a, `tabFiscal Year` fy
- set
- ab.balance = ifnull(ab.balance,0) + %(diff)s
- where
- a.lft <= %(lft)s
- and a.rgt >= %(rgt)s
- and ab.account = a.name
- and ifnull(a.is_pl_account, 'No') = 'No'
- and fy.name = ab.fiscal_year
- and fy.year_start_date > '%(posting_date)s'""" % p)
-
-
-
-
- # Get periods(month and year)
- #-----------------------------
- def get_period_list(self):
- pl = sql("SELECT name, start_date, end_date, fiscal_year FROM tabPeriod WHERE fiscal_year = '%s' and period_type in ('Month', 'Year')" % (self.doc.fiscal_year))
- return pl
-
- # Voucher Balance
- # ---------------
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 ifnull(is_cancelled,'No') = 'No'", (self.doc.against_voucher, self.doc.against_voucher_type))[0][0] or 0.0)
tds = 0
@@ -291,10 +182,7 @@
# Posting date must be after freezing date
self.check_freezing_date(adv_adj)
-
- # Update current account balance
- self.post_balance(self.doc.account, cancel)
-
+
# Update outstanding amt on against voucher
if self.doc.against_voucher and self.doc.against_voucher_type not in ('Journal Voucher','POS') and update_outstanding == 'Yes':
self.update_outstanding_amt()
diff --git a/erpnext/accounts/doctype/journal_voucher/journal_voucher.js b/erpnext/accounts/doctype/journal_voucher/journal_voucher.js
index 7dd09c5..fe407f5 100644
--- a/erpnext/accounts/doctype/journal_voucher/journal_voucher.js
+++ b/erpnext/accounts/doctype/journal_voucher/journal_voucher.js
@@ -145,7 +145,14 @@
cur_frm.cscript.account = function(doc,dt,dn) {
var d = locals[dt][dn];
- $c_obj('GL Control','get_bal',d.account+'~~~'+doc.fiscal_year, function(r,rt) { d.balance = r.message; refresh_field('balance',d.name,'entries'); });
+ wn.call({
+ method: "accounts.utils.get_balance_on",
+ args: {account: d.account, date: doc.posting_date},
+ callback: function(r) {
+ d.balance = fmt_money(r.message);
+ refresh_field('balance',d.name,'entries');
+ }
+ });
}
cur_frm.cscript.validate = function(doc,cdt,cdn) {
diff --git a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
index 1c01700..9d28f97 100644
--- a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
+++ b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
# Please edit this list and import only required elements
from __future__ import unicode_literals
@@ -35,431 +35,431 @@
from utilities.transaction_base import TransactionBase
class DocType:
- def __init__(self,d,dl):
- self.doc, self.doclist = d,dl
- self.master_type = {}
- self.credit_days_for = {}
- self.credit_days_global = -1
- self.is_approving_authority = -1
+ def __init__(self,d,dl):
+ self.doc, self.doclist = d,dl
+ self.master_type = {}
+ self.credit_days_for = {}
+ self.credit_days_global = -1
+ self.is_approving_authority = -1
- #--------------------------------------------------------------------------------------------------------
- # Autoname
- #--------------------------------------------------------------------------------------------------------
- def autoname(self):
- self.doc.name = make_autoname(self.doc.naming_series+'.#####')
+ #--------------------------------------------------------------------------------------------------------
+ # Autoname
+ #--------------------------------------------------------------------------------------------------------
+ def autoname(self):
+ self.doc.name = make_autoname(self.doc.naming_series+'.#####')
- #--------------------------------------------------------------------------------------------------------
- # Fetch outstanding amount from RV/PV
- #--------------------------------------------------------------------------------------------------------
- def get_outstanding(self, args):
- args = eval(args)
- o_s = sql("select outstanding_amount from `tab%s` where name = '%s'" % (args['doctype'],args['docname']))
- if args['doctype'] == 'Purchase Invoice':
- return {'debit': o_s and flt(o_s[0][0]) or 0}
- if args['doctype'] == 'Sales Invoice':
- return {'credit': o_s and flt(o_s[0][0]) or 0}
+ #--------------------------------------------------------------------------------------------------------
+ # Fetch outstanding amount from RV/PV
+ #--------------------------------------------------------------------------------------------------------
+ def get_outstanding(self, args):
+ args = eval(args)
+ o_s = sql("select outstanding_amount from `tab%s` where name = '%s'" % (args['doctype'],args['docname']))
+ if args['doctype'] == 'Purchase Invoice':
+ return {'debit': o_s and flt(o_s[0][0]) or 0}
+ if args['doctype'] == 'Sales Invoice':
+ return {'credit': o_s and flt(o_s[0][0]) or 0}
- #--------------------------------------------------------------------------------------------------------
- # Create remarks
- #--------------------------------------------------------------------------------------------------------
- def create_remarks(self):
- r = []
- if self.doc.cheque_no :
- if self.doc.cheque_date:
- r.append('Via cheque #%s dated %s' % (self.doc.cheque_no, formatdate(self.doc.cheque_date)))
- else :
- msgprint("Please enter cheque date")
- raise Exception
-
- for d in getlist(self.doclist, 'entries'):
- if d.against_invoice and d.credit:
- currency = sql("select currency from `tabSales Invoice` where name = '%s'" % d.against_invoice)
- currency = currency and currency[0][0] or ''
- r.append('%s %s against Invoice: %s' % (cstr(currency), fmt_money(flt(d.credit)), d.against_invoice))
- if d.against_voucher and d.debit:
- bill_no = sql("select bill_no, bill_date, currency from `tabPurchase Invoice` where name=%s", d.against_voucher)
- if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() not in ['na', 'not applicable', 'none']:
- bill_no = bill_no and bill_no[0]
- r.append('%s %s against Bill %s dated %s' % (bill_no[2] and cstr(bill_no[2]) or '', fmt_money(flt(d.debit)), bill_no[0], bill_no[1] and formatdate(bill_no[1].strftime('%Y-%m-%d')) or ''))
- if self.doc.ded_amount:
- r.append("TDS Amount: %s" % self.doc.ded_amount)
-
- if self.doc.user_remark:
- r.append("User Remark : %s"%self.doc.user_remark)
+ #--------------------------------------------------------------------------------------------------------
+ # Create remarks
+ #--------------------------------------------------------------------------------------------------------
+ def create_remarks(self):
+ r = []
+ if self.doc.cheque_no :
+ if self.doc.cheque_date:
+ r.append('Via cheque #%s dated %s' % (self.doc.cheque_no, formatdate(self.doc.cheque_date)))
+ else :
+ msgprint("Please enter cheque date")
+ raise Exception
+
+ for d in getlist(self.doclist, 'entries'):
+ if d.against_invoice and d.credit:
+ currency = sql("select currency from `tabSales Invoice` where name = '%s'" % d.against_invoice)
+ currency = currency and currency[0][0] or ''
+ r.append('%s %s against Invoice: %s' % (cstr(currency), fmt_money(flt(d.credit)), d.against_invoice))
+ if d.against_voucher and d.debit:
+ bill_no = sql("select bill_no, bill_date, currency from `tabPurchase Invoice` where name=%s", d.against_voucher)
+ if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() not in ['na', 'not applicable', 'none']:
+ bill_no = bill_no and bill_no[0]
+ r.append('%s %s against Bill %s dated %s' % (bill_no[2] and cstr(bill_no[2]) or '', fmt_money(flt(d.debit)), bill_no[0], bill_no[1] and formatdate(bill_no[1].strftime('%Y-%m-%d')) or ''))
+ if self.doc.ded_amount:
+ r.append("TDS Amount: %s" % self.doc.ded_amount)
+
+ if self.doc.user_remark:
+ r.append("User Remark : %s"%self.doc.user_remark)
- if r:
- self.doc.remark = ("\n").join(r)
-
- # --------------------------------------------------------------------------------------------------------
- # Check user role for approval process
- # --------------------------------------------------------------------------------------------------------
- def get_authorized_user(self):
- if self.is_approving_authority==-1:
- self.is_approving_authority = 0
+ if r:
+ self.doc.remark = ("\n").join(r)
+
+ # --------------------------------------------------------------------------------------------------------
+ # Check user role for approval process
+ # --------------------------------------------------------------------------------------------------------
+ def get_authorized_user(self):
+ if self.is_approving_authority==-1:
+ self.is_approving_authority = 0
- # Fetch credit controller role
- approving_authority = sql("select value from `tabSingles` where field='credit_controller' and doctype='Global Defaults'")
- approving_authority = approving_authority and approving_authority[0][0] or ''
-
- # Check logged-in user is authorized
- if approving_authority in webnotes.user.get_roles():
- self.is_approving_authority = 1
-
+ # Fetch credit controller role
+ approving_authority = sql("select value from `tabSingles` where field='credit_controller' and doctype='Global Defaults'")
+ approving_authority = approving_authority and approving_authority[0][0] or ''
+
+ # Check logged-in user is authorized
+ if approving_authority in webnotes.user.get_roles():
+ self.is_approving_authority = 1
+
return self.is_approving_authority
-
- # get master type
- # ---------------
- def get_master_type(self, ac):
- if not self.master_type.get(ac):
- self.master_type[ac] = sql("select master_type from `tabAccount` where name=%s", ac)[0][0] or 'None'
- return self.master_type[ac]
-
- # get credit days for
- # -------------------
- def get_credit_days_for(self, ac):
+
+ # get master type
+ # ---------------
+ def get_master_type(self, ac):
+ if not self.master_type.get(ac):
+ self.master_type[ac] = sql("select master_type from `tabAccount` where name=%s", ac)[0][0] or 'None'
+ return self.master_type[ac]
+
+ # get credit days for
+ # -------------------
+ def get_credit_days_for(self, ac):
- if not self.credit_days_for.has_key(ac):
- self.credit_days_for[ac] = sql("select credit_days from `tabAccount` where name='%s'" % ac)[0][0] or 0
+ if not self.credit_days_for.has_key(ac):
+ self.credit_days_for[ac] = sql("select credit_days from `tabAccount` where name='%s'" % ac)[0][0] or 0
- if not self.credit_days_for[ac]:
- if self.credit_days_global==-1:
- self.credit_days_global = sql("select credit_days from `tabCompany` where name='%s'" % self.doc.company)[0][0] or 0
- return self.credit_days_global
- else:
- return self.credit_days_for[ac]
-
-
- # --------------------------------------------------------------------------------------------------------
- # Check Credit Days - Cheque Date can not after (Posting date + Credit Days)
- # --------------------------------------------------------------------------------------------------------
- def check_credit_days(self):
- date_diff = 0
- if self.doc.cheque_date:
- date_diff = (getdate(self.doc.cheque_date)-getdate(self.doc.posting_date)).days
-
- if date_diff <= 0: return
-
- # Get List of Customer Account
- acc_list = filter(lambda d: self.get_master_type(d.account)=='Customer', getlist(self.doclist,'entries'))
-
- for d in acc_list:
- credit_days = self.get_credit_days_for(d.account)
-
- # Check credit days
- if credit_days > 0 and not self.get_authorized_user() and cint(date_diff) > credit_days:
- msgprint("Credit Not Allowed: Cannot allow a check that is dated more than %s days after the posting date" % credit_days)
- raise Exception
-
- #--------------------------------------------------------------------------------------------------------
- # validation of debit/credit account with Debit To Account(RV) or Credit To Account (PV)
- #--------------------------------------------------------------------------------------------------------
- def check_account_against_entries(self):
- for d in getlist(self.doclist,'entries'):
- if d.against_invoice:
- acc=sql("select debit_to from `tabSales Invoice` where name='%s'"%d.against_invoice)
- if acc and acc[0][0] != d.account:
- msgprint("Debit account is not matching with receivable voucher")
- raise Exception
-
- if d.against_voucher:
- acc=sql("select credit_to from `tabPurchase Invoice` where name='%s'"%d.against_voucher)
- if acc and acc[0][0] != d.account:
- msgprint("Credit account is not matching with payable voucher")
- raise Exception
-
- #--------------------------------------------------------------------------------------------------------
- # Validate Cheque Info: Mandatory for Bank/Contra voucher
- #--------------------------------------------------------------------------------------------------------
- def validate_cheque_info(self):
- if self.doc.voucher_type in ['Bank Voucher']:
- if not self.doc.cheque_no or not self.doc.cheque_date:
- msgprint("Cheque No & Cheque Date is required for " + cstr(self.doc.voucher_type))
- raise Exception
-
- if self.doc.cheque_date and not self.doc.cheque_no:
- msgprint("Cheque No is mandatory if you entered Cheque Date")
- raise Exception
-
- #--------------------------------------------------------------------------------------------------------
- # Gives reminder for making is_advance = 'Yes' in Advance Entry
- #--------------------------------------------------------------------------------------------------------
- def validate_entries_for_advance(self):
- for d in getlist(self.doclist,'entries'):
- if not d.is_advance and not d.against_voucher and not d.against_invoice and d.against_jv:
- master_type = self.get_master_type(d.account)
- if (master_type == 'Customer' and flt(d.credit) > 0) or (master_type == 'Supplier' and flt(d.debit) > 0):
- msgprint("Message: Please check Is Advance as 'Yes' against Account %s if this is an advance entry." % d.account)
-
- #--------------------------------------------------------------------------------------------------------
- # TDS: Validate tds related fields
- #--------------------------------------------------------------------------------------------------------
- def get_tds_category_account(self):
- for d in getlist(self.doclist,'entries'):
- if flt(d.debit) > 0 and not d.against_voucher and d.is_advance == 'Yes':
- acc = sql("select tds_applicable from `tabAccount` where name = '%s'" % d.account)
- acc_tds_applicable = acc and acc[0][0] or 'No'
- if acc_tds_applicable == 'Yes':
- # TDS applicable field become mandatory for advance payment towards supplier or related party
- if not self.doc.tds_applicable:
- msgprint("Please select TDS Applicable or Not")
- raise Exception
-
- # If TDS applicable, category and supplier account bocome mandatory
- elif self.doc.tds_applicable == 'Yes':
- self.validate_category_account(d.account)
- if self.doc.ded_amount and not self.doc.tax_code:
- msgprint("Please enter Tax Code in TDS section")
- raise Exception
+ if not self.credit_days_for[ac]:
+ if self.credit_days_global==-1:
+ self.credit_days_global = sql("select credit_days from `tabCompany` where name='%s'" % self.doc.company)[0][0] or 0
+ return self.credit_days_global
+ else:
+ return self.credit_days_for[ac]
+
+
+ # --------------------------------------------------------------------------------------------------------
+ # Check Credit Days - Cheque Date can not after (Posting date + Credit Days)
+ # --------------------------------------------------------------------------------------------------------
+ def check_credit_days(self):
+ date_diff = 0
+ if self.doc.cheque_date:
+ date_diff = (getdate(self.doc.cheque_date)-getdate(self.doc.posting_date)).days
+
+ if date_diff <= 0: return
+
+ # Get List of Customer Account
+ acc_list = filter(lambda d: self.get_master_type(d.account)=='Customer', getlist(self.doclist,'entries'))
+
+ for d in acc_list:
+ credit_days = self.get_credit_days_for(d.account)
+
+ # Check credit days
+ if credit_days > 0 and not self.get_authorized_user() and cint(date_diff) > credit_days:
+ msgprint("Credit Not Allowed: Cannot allow a check that is dated more than %s days after the posting date" % credit_days)
+ raise Exception
+
+ #--------------------------------------------------------------------------------------------------------
+ # validation of debit/credit account with Debit To Account(RV) or Credit To Account (PV)
+ #--------------------------------------------------------------------------------------------------------
+ def check_account_against_entries(self):
+ for d in getlist(self.doclist,'entries'):
+ if d.against_invoice:
+ acc=sql("select debit_to from `tabSales Invoice` where name='%s'"%d.against_invoice)
+ if acc and acc[0][0] != d.account:
+ msgprint("Debit account is not matching with receivable voucher")
+ raise Exception
+
+ if d.against_voucher:
+ acc=sql("select credit_to from `tabPurchase Invoice` where name='%s'"%d.against_voucher)
+ if acc and acc[0][0] != d.account:
+ msgprint("Credit account is not matching with payable voucher")
+ raise Exception
+
+ #--------------------------------------------------------------------------------------------------------
+ # Validate Cheque Info: Mandatory for Bank/Contra voucher
+ #--------------------------------------------------------------------------------------------------------
+ def validate_cheque_info(self):
+ if self.doc.voucher_type in ['Bank Voucher']:
+ if not self.doc.cheque_no or not self.doc.cheque_date:
+ msgprint("Cheque No & Cheque Date is required for " + cstr(self.doc.voucher_type))
+ raise Exception
+
+ if self.doc.cheque_date and not self.doc.cheque_no:
+ msgprint("Cheque No is mandatory if you entered Cheque Date")
+ raise Exception
+
+ #--------------------------------------------------------------------------------------------------------
+ # Gives reminder for making is_advance = 'Yes' in Advance Entry
+ #--------------------------------------------------------------------------------------------------------
+ def validate_entries_for_advance(self):
+ for d in getlist(self.doclist,'entries'):
+ if not d.is_advance and not d.against_voucher and not d.against_invoice and d.against_jv:
+ master_type = self.get_master_type(d.account)
+ if (master_type == 'Customer' and flt(d.credit) > 0) or (master_type == 'Supplier' and flt(d.debit) > 0):
+ msgprint("Message: Please check Is Advance as 'Yes' against Account %s if this is an advance entry." % d.account)
+
+ #--------------------------------------------------------------------------------------------------------
+ # TDS: Validate tds related fields
+ #--------------------------------------------------------------------------------------------------------
+ def get_tds_category_account(self):
+ for d in getlist(self.doclist,'entries'):
+ if flt(d.debit) > 0 and not d.against_voucher and d.is_advance == 'Yes':
+ acc = sql("select tds_applicable from `tabAccount` where name = '%s'" % d.account)
+ acc_tds_applicable = acc and acc[0][0] or 'No'
+ if acc_tds_applicable == 'Yes':
+ # TDS applicable field become mandatory for advance payment towards supplier or related party
+ if not self.doc.tds_applicable:
+ msgprint("Please select TDS Applicable or Not")
+ raise Exception
+
+ # If TDS applicable, category and supplier account bocome mandatory
+ elif self.doc.tds_applicable == 'Yes':
+ self.validate_category_account(d.account)
+ if self.doc.ded_amount and not self.doc.tax_code:
+ msgprint("Please enter Tax Code in TDS section")
+ raise Exception
- #If TDS not applicable, all related fields should blank
- else:
- self.set_fields_null()
-
- # If tds amount but tds applicability not mentioned in account master
- elif self.doc.ded_amount:
- msgprint("Please select TDS Applicable = 'Yes' in account head: '%s' if you want to deduct TDS." % self.doc.supplier_account)
- raise Exception
-
-
+ #If TDS not applicable, all related fields should blank
+ else:
+ self.set_fields_null()
+
+ # If tds amount but tds applicability not mentioned in account master
+ elif self.doc.ded_amount:
+ msgprint("Please select TDS Applicable = 'Yes' in account head: '%s' if you want to deduct TDS." % self.doc.supplier_account)
+ raise Exception
+
+
- #--------------------------------------------------------------------------------------------------------
- # If TDS applicable , TDS category and supplier account should be mandatory
- #--------------------------------------------------------------------------------------------------------
- def validate_category_account(self, credit_account):
- if not self.doc.tds_category:
- msgprint("Please select TDS Category")
- raise Exception
-
- if not self.doc.supplier_account:
- self.doc.supplier_account = credit_account
- elif self.doc.supplier_account and self.doc.supplier_account != credit_account:
- msgprint("Supplier Account is not matching with the account mentioned in the table. Please select proper Supplier Account and click on 'Get TDS' button.")
- raise Exception
-
+ #--------------------------------------------------------------------------------------------------------
+ # If TDS applicable , TDS category and supplier account should be mandatory
+ #--------------------------------------------------------------------------------------------------------
+ def validate_category_account(self, credit_account):
+ if not self.doc.tds_category:
+ msgprint("Please select TDS Category")
+ raise Exception
+
+ if not self.doc.supplier_account:
+ self.doc.supplier_account = credit_account
+ elif self.doc.supplier_account and self.doc.supplier_account != credit_account:
+ msgprint("Supplier Account is not matching with the account mentioned in the table. Please select proper Supplier Account and click on 'Get TDS' button.")
+ raise Exception
+
- #--------------------------------------------------------------------------------------------------------
- # If TDS is not applicable , all related fields should blank
- #--------------------------------------------------------------------------------------------------------
- def set_fields_null(self):
- self.doc.ded_amount = 0
- self.doc.rate = 0
- self.doc.tax_code = ''
- self.doc.tds_category = ''
- self.doc.supplier_account = ''
-
- #--------------------------------------------------------------------------------------------------------
- # Get TDS amount
- #--------------------------------------------------------------------------------------------------------
- def get_tds(self):
- if cstr(self.doc.is_opening) != 'Yes':
- if self.doc.total_debit > 0:
- self.get_tds_category_account()
- if self.doc.supplier_account and self.doc.tds_category:
- get_obj('TDS Control').get_tds_amount(self)
+ #--------------------------------------------------------------------------------------------------------
+ # If TDS is not applicable , all related fields should blank
+ #--------------------------------------------------------------------------------------------------------
+ def set_fields_null(self):
+ self.doc.ded_amount = 0
+ self.doc.rate = 0
+ self.doc.tax_code = ''
+ self.doc.tds_category = ''
+ self.doc.supplier_account = ''
+
+ #--------------------------------------------------------------------------------------------------------
+ # Get TDS amount
+ #--------------------------------------------------------------------------------------------------------
+ def get_tds(self):
+ if cstr(self.doc.is_opening) != 'Yes':
+ if self.doc.total_debit > 0:
+ self.get_tds_category_account()
+ if self.doc.supplier_account and self.doc.tds_category:
+ get_obj('TDS Control').get_tds_amount(self)
-
- #--------------------------------------------------------------------------------------------------------
- # Insert new row to balance total debit and total credit
- #--------------------------------------------------------------------------------------------------------
- def get_balance(self):
- if not getlist(self.doclist,'entries'):
- msgprint("Please enter atleast 1 entry in 'GL Entries' table")
- else:
- flag, self.doc.total_debit, self.doc.total_credit = 0,0,0
- diff = flt(self.doc.difference)
-
- # If any row without amount, set the diff on that row
- for d in getlist(self.doclist,'entries'):
- if (d.credit==0 or d.credit is None) and (d.debit==0 or d.debit is None) and (flt(diff) != 0):
- if diff>0:
- d.credit = flt(diff)
- elif diff<0:
- d.debit = flt(diff)
- flag = 1
-
- # Set the diff in a new row
- if flag == 0 and (flt(diff) != 0):
- jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
- if diff>0:
- jd.credit = flt(diff)
- elif diff<0:
- jd.debit = flt(diff)
-
- # Set the total debit, total credit and difference
- for d in getlist(self.doclist,'entries'):
- self.doc.total_debit += flt(d.debit)
- self.doc.total_credit += flt(d.credit)
+
+ #--------------------------------------------------------------------------------------------------------
+ # Insert new row to balance total debit and total credit
+ #--------------------------------------------------------------------------------------------------------
+ def get_balance(self):
+ if not getlist(self.doclist,'entries'):
+ msgprint("Please enter atleast 1 entry in 'GL Entries' table")
+ else:
+ flag, self.doc.total_debit, self.doc.total_credit = 0,0,0
+ diff = flt(self.doc.difference)
+
+ # If any row without amount, set the diff on that row
+ for d in getlist(self.doclist,'entries'):
+ if (d.credit==0 or d.credit is None) and (d.debit==0 or d.debit is None) and (flt(diff) != 0):
+ if diff>0:
+ d.credit = flt(diff)
+ elif diff<0:
+ d.debit = flt(diff)
+ flag = 1
+
+ # Set the diff in a new row
+ if flag == 0 and (flt(diff) != 0):
+ jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
+ if diff>0:
+ jd.credit = flt(diff)
+ elif diff<0:
+ jd.debit = flt(diff)
+
+ # Set the total debit, total credit and difference
+ for d in getlist(self.doclist,'entries'):
+ self.doc.total_debit += flt(d.debit)
+ self.doc.total_credit += flt(d.credit)
- if self.doc.tds_applicable == 'Yes':
- self.doc.total_credit = flt(self.doc.total_credit) + flt(self.doc.ded_amount)
+ if self.doc.tds_applicable == 'Yes':
+ self.doc.total_credit = flt(self.doc.total_credit) + flt(self.doc.ded_amount)
- self.doc.difference = flt(self.doc.total_debit) - flt(self.doc.total_credit)
-
- #--------------------------------------------------------------------------------------------------------
- # Set against account
- #--------------------------------------------------------------------------------------------------------
- def get_against_account(self):
- # Debit = Credit
- debit, credit = 0.0, 0.0
- debit_list, credit_list = [], []
- for d in getlist(self.doclist, 'entries'):
- debit += flt(d.debit)
- credit += flt(d.credit)
- if flt(d.debit)>0 and (d.account not in debit_list): debit_list.append(d.account)
- if flt(d.credit)>0 and (d.account not in credit_list): credit_list.append(d.account)
+ self.doc.difference = flt(self.doc.total_debit) - flt(self.doc.total_credit)
+
+ #--------------------------------------------------------------------------------------------------------
+ # Set against account
+ #--------------------------------------------------------------------------------------------------------
+ def get_against_account(self):
+ # Debit = Credit
+ debit, credit = 0.0, 0.0
+ debit_list, credit_list = [], []
+ for d in getlist(self.doclist, 'entries'):
+ debit += flt(d.debit)
+ credit += flt(d.credit)
+ if flt(d.debit)>0 and (d.account not in debit_list): debit_list.append(d.account)
+ if flt(d.credit)>0 and (d.account not in credit_list): credit_list.append(d.account)
- self.doc.total_debit = debit
- if self.doc.tds_applicable == 'Yes':
- self.doc.total_credit = credit + flt(self.doc.ded_amount)
- else:
- self.doc.total_credit = credit
+ self.doc.total_debit = debit
+ if self.doc.tds_applicable == 'Yes':
+ self.doc.total_credit = credit + flt(self.doc.ded_amount)
+ else:
+ self.doc.total_credit = credit
- if abs(self.doc.total_debit-self.doc.total_credit) > 0.001:
- msgprint("Debit must be equal to Credit. The difference is %s" % (self.doc.total_debit-self.doc.total_credit))
- raise Exception
-
- # update against account
- for d in getlist(self.doclist, 'entries'):
- if flt(d.debit) > 0: d.against_account = ', '.join(credit_list)
- if flt(d.credit) > 0: d.against_account = ', '.join(debit_list)
+ if abs(self.doc.total_debit-self.doc.total_credit) > 0.001:
+ msgprint("Debit must be equal to Credit. The difference is %s" % (self.doc.total_debit-self.doc.total_credit))
+ raise Exception
+
+ # update against account
+ for d in getlist(self.doclist, 'entries'):
+ if flt(d.debit) > 0: d.against_account = ', '.join(credit_list)
+ if flt(d.credit) > 0: d.against_account = ', '.join(debit_list)
- # set aging date
- #---------------
- def set_aging_date(self):
- if self.doc.is_opening != 'Yes':
- self.doc.aging_date = self.doc.posting_date
- else:
- # check account type whether supplier or customer
- exists = ''
- for d in getlist(self.doclist, 'entries'):
- exists = sql("select name from tabAccount where account_type in ('Supplier', 'Customer') and name = '%s'" % d.account)
- if exists:
- break
+ # set aging date
+ #---------------
+ def set_aging_date(self):
+ if self.doc.is_opening != 'Yes':
+ self.doc.aging_date = self.doc.posting_date
+ else:
+ # check account type whether supplier or customer
+ exists = ''
+ for d in getlist(self.doclist, 'entries'):
+ exists = sql("select name from tabAccount where account_type in ('Supplier', 'Customer') and name = '%s'" % d.account)
+ if exists:
+ break
- # If cus/supp aging dt is mandatory
- if exists and not self.doc.aging_date:
- msgprint("Aging Date is mandatory for opening entry")
- raise Exception
- # otherwise aging dt = posting dt
- else:
- self.doc.aging_date = self.doc.posting_date
+ # If cus/supp aging dt is mandatory
+ if exists and not self.doc.aging_date:
+ msgprint("Aging Date is mandatory for opening entry")
+ raise Exception
+ # otherwise aging dt = posting dt
+ else:
+ self.doc.aging_date = self.doc.posting_date
- # ------------------------
- # set print format fields
- # ------------------------
- def set_print_format_fields(self):
- for d in getlist(self.doclist, 'entries'):
- #msgprint(self.doc.company)
- chk_type = sql("select master_type, account_type from `tabAccount` where name='%s'" % d.account)
- master_type, acc_type = chk_type and cstr(chk_type[0][0]) or '', chk_type and cstr(chk_type[0][1]) or ''
- if master_type in ['Supplier', 'Customer']:
- if not self.doc.pay_to_recd_from:
- self.doc.pay_to_recd_from = get_value(master_type, ' - '.join(d.account.split(' - ')[:-1]), master_type == 'Customer' and 'customer_name' or 'supplier_name')
-
- if acc_type == 'Bank or Cash':
- dcc = TransactionBase().get_company_currency(self.doc.company)
- amt = cint(d.debit) and d.debit or d.credit
- self.doc.total_amount = dcc +' '+ cstr(amt)
- self.doc.total_amount_in_words = get_obj('Sales Common').get_total_in_words(dcc, cstr(amt))
+ # ------------------------
+ # set print format fields
+ # ------------------------
+ def set_print_format_fields(self):
+ for d in getlist(self.doclist, 'entries'):
+ #msgprint(self.doc.company)
+ chk_type = sql("select master_type, account_type from `tabAccount` where name='%s'" % d.account)
+ master_type, acc_type = chk_type and cstr(chk_type[0][0]) or '', chk_type and cstr(chk_type[0][1]) or ''
+ if master_type in ['Supplier', 'Customer']:
+ if not self.doc.pay_to_recd_from:
+ self.doc.pay_to_recd_from = get_value(master_type, ' - '.join(d.account.split(' - ')[:-1]), master_type == 'Customer' and 'customer_name' or 'supplier_name')
+
+ if acc_type == 'Bank or Cash':
+ dcc = TransactionBase().get_company_currency(self.doc.company)
+ amt = cint(d.debit) and d.debit or d.credit
+ self.doc.total_amount = dcc +' '+ cstr(amt)
+ self.doc.total_amount_in_words = get_obj('Sales Common').get_total_in_words(dcc, cstr(amt))
- # --------------------------------
- # get outstanding invoices values
- # --------------------------------
- def get_values(self):
- cond = (flt(self.doc.write_off_amount) > 0) and ' and outstanding_amount <= '+self.doc.write_off_amount or ''
- if self.doc.write_off_based_on == 'Accounts Receivable':
- return sql("select name, debit_to, outstanding_amount from `tabSales Invoice` where docstatus = 1 and company = '%s' and outstanding_amount > 0 %s" % (self.doc.company, cond))
- elif self.doc.write_off_based_on == 'Accounts Payable':
- return sql("select name, credit_to, outstanding_amount from `tabPurchase Invoice` where docstatus = 1 and company = '%s' and outstanding_amount > 0 %s" % (self.doc.company, cond))
+ # --------------------------------
+ # get outstanding invoices values
+ # --------------------------------
+ def get_values(self):
+ cond = (flt(self.doc.write_off_amount) > 0) and ' and outstanding_amount <= '+self.doc.write_off_amount or ''
+ if self.doc.write_off_based_on == 'Accounts Receivable':
+ return sql("select name, debit_to, outstanding_amount from `tabSales Invoice` where docstatus = 1 and company = '%s' and outstanding_amount > 0 %s" % (self.doc.company, cond))
+ elif self.doc.write_off_based_on == 'Accounts Payable':
+ return sql("select name, credit_to, outstanding_amount from `tabPurchase Invoice` where docstatus = 1 and company = '%s' and outstanding_amount > 0 %s" % (self.doc.company, cond))
- # -------------------------
- # get outstanding invoices
- # -------------------------
- def get_outstanding_invoices(self):
- self.doclist = self.doc.clear_table(self.doclist, 'entries')
- total = 0
- for d in self.get_values():
- total += flt(d[2])
- jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
- jd.account = cstr(d[1])
- if self.doc.write_off_based_on == 'Accounts Receivable':
- jd.credit = flt(d[2])
- jd.against_invoice = cstr(d[0])
- elif self.doc.write_off_based_on == 'Accounts Payable':
- jd.debit = flt(d[2])
- jd.against_voucher = cstr(d[0])
- jd.save(1)
- jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
- if self.doc.write_off_based_on == 'Accounts Receivable':
- jd.debit = total
- elif self.doc.write_off_based_on == 'Accounts Payable':
- jd.credit = total
- jd.save(1)
+ # -------------------------
+ # get outstanding invoices
+ # -------------------------
+ def get_outstanding_invoices(self):
+ self.doclist = self.doc.clear_table(self.doclist, 'entries')
+ total = 0
+ for d in self.get_values():
+ total += flt(d[2])
+ jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
+ jd.account = cstr(d[1])
+ if self.doc.write_off_based_on == 'Accounts Receivable':
+ jd.credit = flt(d[2])
+ jd.against_invoice = cstr(d[0])
+ elif self.doc.write_off_based_on == 'Accounts Payable':
+ jd.debit = flt(d[2])
+ jd.against_voucher = cstr(d[0])
+ jd.save(1)
+ jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist)
+ if self.doc.write_off_based_on == 'Accounts Receivable':
+ jd.debit = total
+ elif self.doc.write_off_based_on == 'Accounts Payable':
+ jd.credit = total
+ jd.save(1)
- #--------------------------------------------------------------------------------------------------------
- # VALIDATE
- #--------------------------------------------------------------------------------------------------------
- def validate(self):
- if not self.doc.is_opening:
- self.doc.is_opening='No'
- self.get_against_account()
- self.validate_cheque_info()
- self.create_remarks()
- # tds
- get_obj('TDS Control').validate_first_entry(self)
- self.get_tds_category_account()
+ #--------------------------------------------------------------------------------------------------------
+ # VALIDATE
+ #--------------------------------------------------------------------------------------------------------
+ def validate(self):
+ if not self.doc.is_opening:
+ self.doc.is_opening='No'
+ self.get_against_account()
+ self.validate_cheque_info()
+ self.create_remarks()
+ # tds
+ get_obj('TDS Control').validate_first_entry(self)
+ self.get_tds_category_account()
- self.validate_entries_for_advance()
- self.set_aging_date()
-
- self.validate_against_jv()
- self.set_print_format_fields()
+ self.validate_entries_for_advance()
+ self.set_aging_date()
+
+ self.validate_against_jv()
+ self.set_print_format_fields()
- #FY and Date validation
- get_obj('Sales Common').validate_fiscal_year(self.doc.fiscal_year,self.doc.posting_date,'Posting Date')
+ #FY and Date validation
+ get_obj('Sales Common').validate_fiscal_year(self.doc.fiscal_year,self.doc.posting_date,'Posting Date')
- #--------------------------------------------------------------------------------------------------------
- # On Update - Update Feed
- #--------------------------------------------------------------------------------------------------------
- def on_update(self):
- pass
-
- #--------------------------------------------------------------------------------------------------------
- # On submit
- #--------------------------------------------------------------------------------------------------------
- def on_submit(self):
- if self.doc.voucher_type in ['Bank Voucher', 'Contra Voucher', 'Journal Entry']:
- self.check_credit_days()
- self.check_account_against_entries()
- get_obj(dt='GL Control').make_gl_entries(self.doc, self.doclist)
+ #--------------------------------------------------------------------------------------------------------
+ # On Update - Update Feed
+ #--------------------------------------------------------------------------------------------------------
+ def on_update(self):
+ pass
+
+ #--------------------------------------------------------------------------------------------------------
+ # On submit
+ #--------------------------------------------------------------------------------------------------------
+ def on_submit(self):
+ if self.doc.voucher_type in ['Bank Voucher', 'Contra Voucher', 'Journal Entry']:
+ self.check_credit_days()
+ self.check_account_against_entries()
+ get_obj(dt='GL Control').make_gl_entries(self.doc, self.doclist)
- # validate against jv no
- def validate_against_jv(self):
- for d in getlist(self.doclist, 'entries'):
- if d.against_jv:
- if d.against_jv == self.doc.name:
- msgprint("You can not enter current voucher in 'Against JV' column")
- raise Exception
- elif not sql("select name from `tabJournal Voucher Detail` where account = '%s' and docstatus = 1 and parent = '%s'" % (d.account, d.against_jv)):
- msgprint("Against JV: "+ d.against_jv + " is not valid. Please check")
- raise Exception
-
- #--------------------------------------------------------------------------------------------------------
- # On cancel reverse gl entry
- #--------------------------------------------------------------------------------------------------------
- def on_cancel(self):
- self.check_tds_payment_voucher()
- get_obj(dt='GL Control').make_gl_entries(self.doc, self.doclist, cancel=1)
+ # validate against jv no
+ def validate_against_jv(self):
+ for d in getlist(self.doclist, 'entries'):
+ if d.against_jv:
+ if d.against_jv == self.doc.name:
+ msgprint("You can not enter current voucher in 'Against JV' column")
+ raise Exception
+ elif not sql("select name from `tabJournal Voucher Detail` where account = '%s' and docstatus = 1 and parent = '%s'" % (d.account, d.against_jv)):
+ msgprint("Against JV: "+ d.against_jv + " is not valid. Please check")
+ raise Exception
+
+ #--------------------------------------------------------------------------------------------------------
+ # On cancel reverse gl entry
+ #--------------------------------------------------------------------------------------------------------
+ def on_cancel(self):
+ self.check_tds_payment_voucher()
+ get_obj(dt='GL Control').make_gl_entries(self.doc, self.doclist, cancel=1)
- # Check whether tds payment voucher has been created against this voucher
- #---------------------------------------------------------------------------
- def check_tds_payment_voucher(self):
- tdsp = sql("select parent from `tabTDS Payment Detail` where voucher_no = '%s' and docstatus = 1 and parent not like 'old%'")
- if tdsp:
- msgprint("TDS Payment voucher '%s' has been made against this voucher. Please cancel the payment voucher to proceed." % (tdsp and tdsp[0][0] or ''))
- raise Exception
+ # Check whether tds payment voucher has been created against this voucher
+ #---------------------------------------------------------------------------
+ def check_tds_payment_voucher(self):
+ tdsp = sql("select parent from `tabTDS Payment Detail` where voucher_no = '%s' and docstatus = 1 and parent not like 'old%'")
+ if tdsp:
+ msgprint("TDS Payment voucher '%s' has been made against this voucher. Please cancel the payment voucher to proceed." % (tdsp and tdsp[0][0] or ''))
+ raise Exception
diff --git a/erpnext/accounts/doctype/journal_voucher/test_journal_voucher.py b/erpnext/accounts/doctype/journal_voucher/test_journal_voucher.py
new file mode 100644
index 0000000..bb05687
--- /dev/null
+++ b/erpnext/accounts/doctype/journal_voucher/test_journal_voucher.py
@@ -0,0 +1,118 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+# Please edit this list and import only required elements
+
+import unittest
+import webnotes
+import webnotes.model
+from webnotes.utils import nowdate, flt, add_days
+from accounts.utils import get_fiscal_year, get_balance_on
+
+company = webnotes.conn.get_default("company")
+abbr = webnotes.conn.get_value("Company", company, "abbr")
+
+data = {
+ "expense_account": {
+ "doctype": "Account",
+ "account_name": "Test Expense",
+ "parent_account": "Direct Expenses - %s" % abbr,
+ "company": company,
+ "debit_or_credit": "Debit",
+ "is_pl_account": "Yes",
+ "group_or_ledger": "Ledger"
+ },
+ "supplier_account": {
+ "doctype": "Account",
+ "account_name": "Test Supplier",
+ "parent_account": "Accounts Payable - %s" % abbr,
+ "company": company,
+ "debit_or_credit": "Credit",
+ "is_pl_account": "No",
+ "group_or_ledger": "Ledger"
+ },
+ "journal_voucher": [
+ {
+ "doctype": "Journal Voucher",
+ "voucher_type": "Journal Entry",
+ "naming_series": "JV",
+ "posting_date": nowdate(),
+ "remark": "Test Journal Voucher",
+ "fiscal_year": get_fiscal_year(nowdate())[0],
+ "company": company
+ },
+ {
+ "doctype": "Journal Voucher Detail",
+ "parentfield": "entries",
+ "account": "Test Expense - %s" % abbr,
+ "debit": 5000,
+ "cost_center": "Default CC Ledger - %s" % abbr,
+ },
+ {
+ "doctype": "Journal Voucher Detail",
+ "parentfield": "entries",
+ "account": "Test Supplier - %s" % abbr,
+ "credit": 5000,
+ },
+ ]
+}
+
+def get_name(s):
+ return s + " - " + abbr
+
+class TestJournalVoucher(unittest.TestCase):
+ def setUp(self):
+ webnotes.conn.begin()
+
+ # create a dummy account
+ webnotes.model.insert([data["expense_account"]])
+ webnotes.model.insert([data["supplier_account"]])
+
+ def tearDown(self):
+ webnotes.conn.rollback()
+
+ def test_save_journal_voucher(self):
+ expense_ac_balance = get_balance_on(get_name("Test Expense"), nowdate())
+ supplier_ac_balance = get_balance_on(get_name("Test Supplier"), nowdate())
+
+ dl = webnotes.model.insert(data["journal_voucher"])
+ dl.submit()
+ dl.load_from_db()
+
+ # test submitted jv
+ self.assertTrue(webnotes.conn.exists("Journal Voucher", dl.doclist[0].name))
+ for d in dl.doclist[1:]:
+ self.assertEquals(webnotes.conn.get_value("Journal Voucher Detail",
+ d.name, "parent"), dl.doclist[0].name)
+
+ # test gl entry
+ gle = webnotes.conn.sql("""select account, debit, credit
+ from `tabGL Entry` where voucher_no = %s order by account""",
+ dl.doclist[0].name)
+
+ self.assertEquals((gle[0][0], flt(gle[0][1]), flt(gle[0][2])),
+ ('Test Expense - %s' % abbr, 5000.0, 0.0))
+ self.assertEquals((gle[1][0], flt(gle[1][1]), flt(gle[1][2])),
+ ('Test Supplier - %s' % abbr, 0.0, 5000.0))
+
+ # check balance as on today
+ self.assertEqual(get_balance_on(get_name("Test Expense"), nowdate()),
+ expense_ac_balance + 5000)
+ self.assertEqual(get_balance_on(get_name("Test Supplier"), nowdate()),
+ supplier_ac_balance + 5000)
+
+ # check previous balance
+ self.assertEqual(get_balance_on(get_name("Test Expense"), add_days(nowdate(), -1)), 0)
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/lease_agreement/__init__.py b/erpnext/accounts/doctype/lease_agreement/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/accounts/doctype/lease_agreement/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/doctype/lease_agreement/lease_agreement.js b/erpnext/accounts/doctype/lease_agreement/lease_agreement.js
deleted file mode 100644
index bf0495c..0000000
--- a/erpnext/accounts/doctype/lease_agreement/lease_agreement.js
+++ /dev/null
@@ -1,94 +0,0 @@
-// ERPNext - web based ERP (http://erpnext.com)
-// Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-//
-// This program is free software: you can redistribute it and/or modify
-// it under the terms of the GNU General Public License as published by
-// the Free Software Foundation, either version 3 of the License, or
-// (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-$.extend(cur_frm.cscript, {
- Generate: function(doc, dt, dn) {
- if(doc.installment_amount==''){
- msgprint('Set Installment Amount before generating schedule');
- return;
- }
- if(doc.no_of_installments==''){
- msgprint('Set Number of Installments before generating schedule');
- return;
- }
- if(doc.start_date==''){
- msgprint('Set Start Date before generating schedule');
- return;
- }
- cur_frm.cscript.clear_installments(doc);
- tot=0;i=0;
- while(tot<flt(doc.invoice_amount)-flt(doc.down_payment)){
- d = LocalDB.add_child(doc, 'Lease Installment', 'installments');
- d.amount = flt(doc.installment_amount) < flt(doc.invoice_amount)-flt(doc.down_payment)-tot ? flt(doc.installment_amount) : flt(doc.invoice_amount)-flt(doc.down_payment)-tot
- d.due_date = dateutil.add_months(doc.start_date, i+1);
- tot += flt(doc.installment_amount)
- i++;
- }
- cur_frm.refresh();
- },
- refresh: function(doc) {
- cur_frm.cscript.hide_show_buttons(doc);
- },
- hide_show_buttons: function(doc) {
- if(doc.docstatus==0) {
- hide_field('installment_reciept'); show_field('generate');
- } else if (doc.docstatus==1) {
- show_field('installment_reciept');hide_field('generate');
- }
- },
- clear_installments: function(doc) {
- $.each(getchildren('Lease Installment', doc.name, 'installments', 'Lease Agreement'),
- function(i, d) {
- LocalDB.delete_doc('Lease Installment', d.name);
- }
- )
- },
- no_of_installments: function(doc)
- {
- if(flt(doc.no_of_installments)!=0) {
- doc.installment_amount = (flt(doc.invoice_amount)- flt(doc.down_payment))/flt(doc.no_of_installments);
- refresh_field('installment_amount');
- }
- },
- 'Installment Reciept': function(doc, dt, dn) {
- var d = new wn.widgets.Dialog({
- width: 500,
- title: 'Add a new payment installment',
- fields: [
- {fieldtype:'Data', label:'Cheque Number', fieldname:'cheque_number', reqd:1},
- {fieldtype:'Date', label:'Cheque Date', fieldname:'cheque_date', reqd:1},
- {fieldtype:'Link', label:'Bank Account', fieldname:'bank_account', reqd:1, options:'Account'},
- {fieldtype:'Button', label:'Update',fieldname:'update'}
- ]
- })
- d.show();
- d.fields_dict.update.input.onclick = function() {
- var data = d.get_values();
-
- if(data) {
- $c_obj(make_doclist(dt,dn),'lease_installment_post',data,function(){cur_frm.refresh(); d.hide();});
- }
- }
- }
-})
-
-
-cur_frm.add_fetch('invoice','grand_total','invoice_amount');
-
-cur_frm.fields_dict.invoice.get_query=function(doc){
-
- return "SELECT tv.name FROM `tabSales Invoice` tv WHERE debit_to='"+doc.account+"' and tv.%(key)s like '%s' ORDER BY tv.name LIMIT 50"
-}
diff --git a/erpnext/accounts/doctype/lease_agreement/lease_agreement.py b/erpnext/accounts/doctype/lease_agreement/lease_agreement.py
deleted file mode 100644
index f5f444c..0000000
--- a/erpnext/accounts/doctype/lease_agreement/lease_agreement.py
+++ /dev/null
@@ -1,54 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-from __future__ import unicode_literals
-import webnotes
-from webnotes.model.doc import make_autoname, Document, addchild
-from webnotes import msgprint
-from webnotes.utils import get_defaults
-import json
-from accounts.utils import post_jv
-sql = webnotes.conn.sql
-
-class DocType:
- def __init__(self, doc, doclist):
- self.doc, self.doclist = doc, doclist
-
- def autoname(self):
- """
- Create Lease Id using naming_series pattern
- """
- self.doc.name = make_autoname(self.doc.naming_series+ '.#####')
-
- def lease_installment_post(self, args):
- """
- Posts the Installment receipt into Journal Voucher
- """
- next_inst = sql("select amount,name from `tabLease Installment` where parent=%s and ifnull(cheque_number,'')='' order by due_date limit 1",self.doc.name)
-
- data = json.loads(args)
- data['voucher_type']='Lease Receipt'
- data['naming_series']='JV'
- data['amount']=next_inst[0][0]
- data['debit_account']=data.get('bank_account')
- data['credit_account']=self.doc.account
- data['fiscal_year']=get_defaults()['fiscal_year']
- data['company']=get_defaults()['company']
- jv_name=post_jv(data)
-
- sql("update `tabLease Installment` set cheque_number=%s, cheque_date=%s, jv_number=%s where name=%s",(data.get('cheque_number'),data.get('cheque_date'),jv_name,next_inst[0][1]))
-
- self.doclist = [Document(d.doctype, d.name) for d in self.doclist]
diff --git a/erpnext/accounts/doctype/lease_agreement/lease_agreement.txt b/erpnext/accounts/doctype/lease_agreement/lease_agreement.txt
deleted file mode 100644
index c4f3e12..0000000
--- a/erpnext/accounts/doctype/lease_agreement/lease_agreement.txt
+++ /dev/null
@@ -1,276 +0,0 @@
-# DocType, Lease Agreement
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:35:44',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:35:44',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all DocType
- {
- '_last_update': u'1311555967',
- 'colour': u'White:FFF',
- 'default_print_format': u'Standard',
- 'doctype': 'DocType',
- 'module': u'Accounts',
- 'name': '__common__',
- 'section_style': u'Simple',
- 'show_in_menu': 0,
- 'version': 24
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Lease Agreement',
- 'parentfield': u'fields',
- 'parenttype': u'DocType'
- },
-
- # These values are common for all DocPerm
- {
- 'create': 1,
- 'doctype': u'DocPerm',
- 'name': '__common__',
- 'parent': u'Lease Agreement',
- 'parentfield': u'permissions',
- 'parenttype': u'DocType',
- 'read': 1,
- 'role': u'Accounts Manager'
- },
-
- # DocType, Lease Agreement
- {
- 'doctype': 'DocType',
- 'name': u'Lease Agreement'
- },
-
- # DocPerm
- {
- 'amend': 1,
- 'cancel': 1,
- 'doctype': u'DocPerm',
- 'submit': 1,
- 'write': 1
- },
-
- # DocPerm
- {
- 'amend': 0,
- 'cancel': 0,
- 'doctype': u'DocPerm',
- 'submit': 0,
- 'write': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'naming_series',
- 'fieldtype': u'Select',
- 'label': u'Naming Series',
- 'no_copy': 1,
- 'options': u'\nLA',
- 'permlevel': 0,
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'lessee_info',
- 'fieldtype': u'Section Break',
- 'label': u'Lessee Info.',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'account',
- 'fieldtype': u'Link',
- 'label': u'Account',
- 'options': u'Account',
- 'permlevel': 0,
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'lessee_father',
- 'fieldtype': u'Data',
- 'label': u'Lessee Father',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'lessee_nic',
- 'fieldtype': u'Data',
- 'label': u'Lessee NIC',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'lessee_contact',
- 'fieldtype': u'Data',
- 'label': u'Lessee Contact',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'lessee_address',
- 'fieldtype': u'Text',
- 'label': u'Lessee Address',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'vehicle_info',
- 'fieldtype': u'Section Break',
- 'label': u'Vehicle Info.',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'model_no',
- 'fieldtype': u'Data',
- 'label': u'Model No',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'engine_no',
- 'fieldtype': u'Data',
- 'label': u'Engine No',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'chassis_no',
- 'fieldtype': u'Data',
- 'label': u'Chassis No',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'invoice_and_payment_info',
- 'fieldtype': u'Section Break',
- 'label': u'Invoice and Payment Info.',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'colour': u'White:FFF',
- 'doctype': u'DocField',
- 'fieldname': u'invoice',
- 'fieldtype': u'Link',
- 'label': u'Invoice',
- 'options': u'Sales Invoice',
- 'permlevel': 0,
- 'reqd': 1,
- 'trigger': u'Client'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'invoice_amount',
- 'fieldtype': u'Currency',
- 'label': u'Invoice Amount',
- 'permlevel': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'down_payment',
- 'fieldtype': u'Currency',
- 'label': u'Down Payment',
- 'permlevel': 0,
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'start_date',
- 'fieldtype': u'Date',
- 'label': u'Start Date',
- 'permlevel': 0,
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'no_of_installments',
- 'fieldtype': u'Int',
- 'label': u'No of Installments',
- 'permlevel': 0,
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'installment_amount',
- 'fieldtype': u'Currency',
- 'label': u'Installment Amount',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'colour': u'White:FFF',
- 'doctype': u'DocField',
- 'fieldname': u'generate',
- 'fieldtype': u'Button',
- 'label': u'Generate',
- 'permlevel': 0,
- 'trigger': u'Client'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'installments',
- 'fieldtype': u'Table',
- 'label': u'Installments',
- 'options': u'Lease Installment',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'allow_on_submit': 1,
- 'colour': u'White:FFF',
- 'doctype': u'DocField',
- 'fieldname': u'installment_reciept',
- 'fieldtype': u'Button',
- 'label': u'Installment Reciept',
- 'permlevel': 0
- }
-]
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/lease_installment/__init__.py b/erpnext/accounts/doctype/lease_installment/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/accounts/doctype/lease_installment/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/doctype/lease_installment/lease_installment.txt b/erpnext/accounts/doctype/lease_installment/lease_installment.txt
deleted file mode 100644
index 475d226..0000000
--- a/erpnext/accounts/doctype/lease_installment/lease_installment.txt
+++ /dev/null
@@ -1,86 +0,0 @@
-# DocType, Lease Installment
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:35:44',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:35:44',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all DocType
- {
- 'colour': u'White:FFF',
- 'doctype': 'DocType',
- 'istable': 1,
- 'module': u'Accounts',
- 'name': '__common__',
- 'section_style': u'Simple',
- 'show_in_menu': 0,
- 'version': 5
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Lease Installment',
- 'parentfield': u'fields',
- 'parenttype': u'DocType'
- },
-
- # DocType, Lease Installment
- {
- 'doctype': 'DocType',
- 'name': u'Lease Installment'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'amount',
- 'fieldtype': u'Currency',
- 'label': u'Amount',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'due_date',
- 'fieldtype': u'Date',
- 'label': u'Due Date',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'cheque_number',
- 'fieldtype': u'Data',
- 'label': u'Cheque Number',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'cheque_date',
- 'fieldtype': u'Date',
- 'label': u'Cheque Date',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'jv_number',
- 'fieldtype': u'Link',
- 'hidden': 0,
- 'label': u'JV Number',
- 'options': u'Journal Voucher',
- 'permlevel': 1
- }
-]
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/mis_control/mis_control.py b/erpnext/accounts/doctype/mis_control/mis_control.py
index f32b3e6..c30eae9 100644
--- a/erpnext/accounts/doctype/mis_control/mis_control.py
+++ b/erpnext/accounts/doctype/mis_control/mis_control.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
# Please edit this list and import only required elements
from __future__ import unicode_literals
@@ -30,390 +30,342 @@
get_value = webnotes.conn.get_value
in_transaction = webnotes.conn.in_transaction
convert_to_lists = webnotes.conn.convert_to_lists
-
-# -----------------------------------------------------------------------------------------
+from accounts.utils import get_balance_on, get_fiscal_year
class DocType:
- def __init__(self, doc, doclist):
- self.doc = doc
- self.doclist = doclist
- self.account_list = []
- self.ac_details = {} # key: account id, values: debit_or_credit, lft, rgt
-
- self.roles = webnotes.user.get_roles()
+ def __init__(self, doc, doclist):
+ self.doc = doc
+ self.doclist = doclist
+ self.account_list = []
+ self.ac_details = {} # key: account id, values: debit_or_credit, lft, rgt
+
+ self.roles = webnotes.user.get_roles()
- self.period_list = []
- self.period_start_date = {}
- self.period_end_date = {}
+ self.period_list = []
+ self.period_start_date = {}
+ self.period_end_date = {}
- self.fs_list = []
- self.root_bal = []
- self.flag = 0
+ self.fs_list = []
+ self.root_bal = []
+ self.flag = 0
- # Get defaults on load of MIS, MIS - Comparison Report and Financial statements
- # ----------------------------------------------------
- def get_comp(self):
- ret = {}
- type = []
- comp = []
- # ------ get period -----------
- ret['period'] = ['Annual','Half Yearly','Quarterly','Monthly']
-
- # ---- get companies ---------
- res = sql("select name from `tabCompany`")
- for r in res:
- comp.append(r[0])
- #comp.append(r[0] for r in res)
- ret['company'] = comp
+ # Get defaults on load of MIS, MIS - Comparison Report and Financial statements
+ # ----------------------------------------------------
+ def get_comp(self):
+ ret = {}
+ type = []
+ comp = []
+ # ------ get period -----------
+ ret['period'] = ['Annual','Half Yearly','Quarterly','Monthly']
+
+ # ---- get companies ---------
+ res = sql("select name from `tabCompany`")
+ for r in res:
+ comp.append(r[0])
+ #comp.append(r[0] for r in res)
+ ret['company'] = comp
- #--- to get fiscal year and start_date of that fiscal year -----
- res = sql("select name, year_start_date from `tabFiscal Year`")
- ret['fiscal_year'] = [r[0] for r in res]
- ret['start_dates'] = {}
- for r in res:
- ret['start_dates'][r[0]] = str(r[1])
-
- #--- from month and to month (for MIS - Comparison Report) -------
- month_list = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
- fiscal_start_month = sql("select MONTH(year_start_date) from `tabFiscal Year` where name = %s",(get_defaults()['fiscal_year']))
- fiscal_start_month = fiscal_start_month and fiscal_start_month[0][0] or 1
- mon = ['']
- for i in range(fiscal_start_month,13): mon.append(month_list[i-1])
- for i in range(0,fiscal_start_month-1): mon.append(month_list[i])
- ret['month'] = mon
+ #--- to get fiscal year and start_date of that fiscal year -----
+ res = sql("select name, year_start_date from `tabFiscal Year`")
+ ret['fiscal_year'] = [r[0] for r in res]
+ ret['start_dates'] = {}
+ for r in res:
+ ret['start_dates'][r[0]] = str(r[1])
+
+ #--- from month and to month (for MIS - Comparison Report) -------
+ month_list = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
+ fiscal_start_month = sql("select MONTH(year_start_date) from `tabFiscal Year` where name = %s",(get_defaults()['fiscal_year']))
+ fiscal_start_month = fiscal_start_month and fiscal_start_month[0][0] or 1
+ mon = ['']
+ for i in range(fiscal_start_month,13): mon.append(month_list[i-1])
+ for i in range(0,fiscal_start_month-1): mon.append(month_list[i])
+ ret['month'] = mon
- # ------------------------ get MIS Type on basis of roles of session user ------------------------------------------
- if has_common(self.roles, ['Sales Manager']):
- type.append('Sales')
- if has_common(self.roles, ['Purchase Manager']):
- type.append('Purchase')
- ret['type'] = type
- return ret
-
- # Gets Transactions type and Group By options based on module
- #------------------------------------------------------------------
- def get_trans_group(self,module):
- ret = {}
- st,group = [],[]
- if module == 'Sales':
- st = ['Quotation','Sales Order','Delivery Note','Sales Invoice']
- group = ['Item','Item Group','Customer','Customer Group','Cost Center']
- elif module == 'Purchase':
- st = ['Purchase Order','Purchase Receipt','Purchase Invoice']
- group = ['Item','Item Group','Supplier','Supplier Type']
-
- ret['stmt_type'] = st
- ret['group_by'] = group
-
- return ret
+ # ------------------------ get MIS Type on basis of roles of session user ------------------------------------------
+ if has_common(self.roles, ['Sales Manager']):
+ type.append('Sales')
+ if has_common(self.roles, ['Purchase Manager']):
+ type.append('Purchase')
+ ret['type'] = type
+ return ret
+
+ # Gets Transactions type and Group By options based on module
+ #------------------------------------------------------------------
+ def get_trans_group(self,module):
+ ret = {}
+ st,group = [],[]
+ if module == 'Sales':
+ st = ['Quotation','Sales Order','Delivery Note','Sales Invoice']
+ group = ['Item','Item Group','Customer','Customer Group','Cost Center']
+ elif module == 'Purchase':
+ st = ['Purchase Order','Purchase Receipt','Purchase Invoice']
+ group = ['Item','Item Group','Supplier','Supplier Type']
+
+ ret['stmt_type'] = st
+ ret['group_by'] = group
+
+ return ret
- # Get Days based on month (for MIS Comparison Report)
- # --------------------------------------------------------
- def get_days(self,month):
- days = []
- ret = {}
- if month == 'Jan' or month == 'Mar' or month == 'May' or month == 'Jul' or month == 'Aug' or month == 'Oct' or month == 'Dec':
- for i in range(1,32):
- days.append(i)
- elif month == 'Apr' or month == 'Jun' or month == 'Sep' or month == 'Nov':
- for i in range(1,31):
- days.append(i)
- elif month == 'Feb':
- for i in range(1,29):
- days.append(i)
- ret['days'] = days
- return ret
+ # Get Days based on month (for MIS Comparison Report)
+ # --------------------------------------------------------
+ def get_days(self,month):
+ days = []
+ ret = {}
+ if month == 'Jan' or month == 'Mar' or month == 'May' or month == 'Jul' or month == 'Aug' or month == 'Oct' or month == 'Dec':
+ for i in range(1,32):
+ days.append(i)
+ elif month == 'Apr' or month == 'Jun' or month == 'Sep' or month == 'Nov':
+ for i in range(1,31):
+ days.append(i)
+ elif month == 'Feb':
+ for i in range(1,29):
+ days.append(i)
+ ret['days'] = days
+ return ret
- # Get from date and to date based on fiscal year (for in summary - comparison report)
- # -----------------------------------------------------------------------------------------------------
- def dates(self,fiscal_year,from_date,to_date):
- import datetime
- ret = ''
- start_date = cstr(sql("select year_start_date from `tabFiscal Year` where name = %s",fiscal_year)[0][0])
- st_mon = cint(from_date.split('-')[1])
- ed_mon = cint(to_date.split('-')[1])
- st_day = cint(from_date.split('-')[2])
- ed_day = cint(to_date.split('-')[2])
- fiscal_start_month = cint(start_date.split('-')[1])
- next_fiscal_year = cint(start_date.split('-')[0]) + 1
- current_year = ''
- next_year = ''
-
- #CASE - 1 : Jan - Mar (Valid)
- if st_mon < fiscal_start_month and ed_mon < fiscal_start_month:
- current_year = cint(start_date.split('-')[0]) + 1
- next_year = cint(start_date.split('-')[0]) + 1
-
- # Case - 2 : Apr - Dec (Valid)
- elif st_mon >= fiscal_start_month and ed_mon <= 12 and ed_mon >= fiscal_start_month:
- current_year = cint(start_date.split('-')[0])
- next_year = cint(start_date.split('-')[0])
+ # Get from date and to date based on fiscal year (for in summary - comparison report)
+ # -----------------------------------------------------------------------------------------------------
+ def dates(self,fiscal_year,from_date,to_date):
+ import datetime
+ ret = ''
+ start_date = cstr(sql("select year_start_date from `tabFiscal Year` where name = %s",fiscal_year)[0][0])
+ st_mon = cint(from_date.split('-')[1])
+ ed_mon = cint(to_date.split('-')[1])
+ st_day = cint(from_date.split('-')[2])
+ ed_day = cint(to_date.split('-')[2])
+ fiscal_start_month = cint(start_date.split('-')[1])
+ next_fiscal_year = cint(start_date.split('-')[0]) + 1
+ current_year = ''
+ next_year = ''
+
+ #CASE - 1 : Jan - Mar (Valid)
+ if st_mon < fiscal_start_month and ed_mon < fiscal_start_month:
+ current_year = cint(start_date.split('-')[0]) + 1
+ next_year = cint(start_date.split('-')[0]) + 1
+
+ # Case - 2 : Apr - Dec (Valid)
+ elif st_mon >= fiscal_start_month and ed_mon <= 12 and ed_mon >= fiscal_start_month:
+ current_year = cint(start_date.split('-')[0])
+ next_year = cint(start_date.split('-')[0])
- # Case 3 : Jan - May (Invalid)
- elif st_mon < fiscal_start_month and ed_mon >= fiscal_start_month:
- current_year = cint(start_date.split('-')[0]) + 1
- next_year = cint(start_date.split('-')[0]) + 2
+ # Case 3 : Jan - May (Invalid)
+ elif st_mon < fiscal_start_month and ed_mon >= fiscal_start_month:
+ current_year = cint(start_date.split('-')[0]) + 1
+ next_year = cint(start_date.split('-')[0]) + 2
- # check whether from date is within fiscal year
- if datetime.date(current_year, st_mon, st_day) >= datetime.date(cint(start_date.split('-')[0]), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])) and datetime.date(cint(current_year), cint(st_mon), cint(st_day)) < datetime.date((cint(start_date.split('-')[0])+1), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])):
- begin_date = cstr(current_year)+"-"+cstr(st_mon)+"-"+cstr(st_day)
- else:
- msgprint("Please enter appropriate from date.")
- raise Exception
- # check whether to date is within fiscal year
- if datetime.date(next_year, ed_mon, ed_day) >= datetime.date(cint(start_date.split('-')[0]), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])) and datetime.date(cint(next_year), cint(ed_mon), cint(ed_day)) < datetime.date(cint(start_date.split('-')[0])+1, cint(start_date.split('-')[1]), cint(start_date.split('-')[2])):
- end_date = cstr(next_year)+"-"+cstr(ed_mon)+"-"+cstr(ed_day)
- else:
- msgprint("Please enter appropriate to date.")
- raise Exception
- ret = begin_date+'~~~'+end_date
- return ret
+ # check whether from date is within fiscal year
+ if datetime.date(current_year, st_mon, st_day) >= datetime.date(cint(start_date.split('-')[0]), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])) and datetime.date(cint(current_year), cint(st_mon), cint(st_day)) < datetime.date((cint(start_date.split('-')[0])+1), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])):
+ begin_date = cstr(current_year)+"-"+cstr(st_mon)+"-"+cstr(st_day)
+ else:
+ msgprint("Please enter appropriate from date.")
+ raise Exception
+ # check whether to date is within fiscal year
+ if datetime.date(next_year, ed_mon, ed_day) >= datetime.date(cint(start_date.split('-')[0]), cint(start_date.split('-')[1]), cint(start_date.split('-')[2])) and datetime.date(cint(next_year), cint(ed_mon), cint(ed_day)) < datetime.date(cint(start_date.split('-')[0])+1, cint(start_date.split('-')[1]), cint(start_date.split('-')[2])):
+ end_date = cstr(next_year)+"-"+cstr(ed_mon)+"-"+cstr(ed_day)
+ else:
+ msgprint("Please enter appropriate to date.")
+ raise Exception
+ ret = begin_date+'~~~'+end_date
+ return ret
- # Get MIS Totals
- # ---------------
- def get_totals(self, args):
- args = eval(args)
- #msgprint(args)
- totals = sql("SELECT %s FROM %s WHERE %s %s %s %s" %(cstr(args['query_val']), cstr(args['tables']), cstr(args['company']), cstr(args['cond']), cstr(args['add_cond']), cstr(args['fil_cond'])), as_dict = 1)[0]
- #msgprint(totals)
- tot_keys = totals.keys()
- # return in flt because JSON doesn't accept Decimal
- for d in tot_keys:
- totals[d] = flt(totals[d])
- return totals
+ # Get MIS Totals
+ # ---------------
+ def get_totals(self, args):
+ args = eval(args)
+ #msgprint(args)
+ totals = sql("SELECT %s FROM %s WHERE %s %s %s %s" %(cstr(args['query_val']), cstr(args['tables']), cstr(args['company']), cstr(args['cond']), cstr(args['add_cond']), cstr(args['fil_cond'])), as_dict = 1)[0]
+ #msgprint(totals)
+ tot_keys = totals.keys()
+ # return in flt because JSON doesn't accept Decimal
+ for d in tot_keys:
+ totals[d] = flt(totals[d])
+ return totals
- # Get Statement
- # -------------
-
- def get_statement(self, arg):
- self.return_data = []
+ # Get Statement
+ # -------------
+
+ def get_statement(self, arg):
+ self.return_data = []
- # define periods
- arg = eval(arg)
- pl = ''
-
- self.define_periods(arg['year'], arg['period']) # declares 1.period_list i.e. (['Jan','Feb','Mar'...] or ['Q1','Q2'...] or ['FY2009-2010']) based on period
- # 2.period_start_date dict {'Jan':'01-01-2009'...}
- # 3.period_start_date dict {'Jan':'31-01-2009'...}
- self.return_data.append([4,'']+self.period_list)
+ # define periods
+ arg = eval(arg)
+ pl = ''
+
+ self.define_periods(arg['year'], arg['period']) # declares 1.period_list i.e. (['Jan','Feb','Mar'...] or ['Q1','Q2'...] or ['FY2009-2010']) based on period
+ # 2.period_start_date dict {'Jan':'01-01-2009'...}
+ # 3.period_start_date dict {'Jan':'31-01-2009'...}
+ self.return_data.append([4,'']+self.period_list)
-
- if arg['statement'] == 'Balance Sheet': pl = 'No'
- if arg['statement'] == 'Profit & Loss': pl = 'Yes'
-
- self.get_children('',0,pl,arg['company'], arg['year'])
-
- #self.balance_pl_statement(acct, arg['statement'])
- #msgprint(self.return_data)
- return self.return_data
-
- # Get Children
- # ------------
- def get_children(self, parent_account, level, pl, company, fy):
- cl = sql("select distinct account_name, name, debit_or_credit, lft, rgt from `tabAccount` where ifnull(parent_account, '') = %s and ifnull(is_pl_account, 'No')=%s and company=%s and docstatus != 2 order by name asc", (parent_account, pl, company))
- level0_diff = [0 for p in self.period_list]
- if pl=='Yes' and level==0: # switch for income & expenses
- cl = [c for c in cl]
- cl.reverse()
- if cl:
- for c in cl:
- self.ac_details[c[1]] = [c[2], c[3], c[4]]
- bal_list = self.get_period_balance(c[1], level, pl, company, fy)
- if level==0: # top level - put balances as totals
- self.return_data.append([level, c[0]] + ['' for b in bal_list])
- totals = bal_list
- for i in range(len(totals)): # make totals
- if c[2]=='Credit':
- level0_diff[i] += flt(totals[i])
- else:
- level0_diff[i] -= flt(totals[i])
- else:
- self.return_data.append([level, c[0]]+bal_list)
-
- if level < 2:
- self.get_children(c[1], level+1, pl, company, fy)
-
- # make totals - for top level
- # ---------------------------
- if level==0:
- # add rows for profit / loss in B/S
- if pl=='No':
- if c[2]=='Credit':
- self.return_data.append([1, 'Total Liabilities'] + totals)
- level0_diff = [-i for i in level0_diff] # convert to debit
- self.return_data.append([5, 'Profit/Loss (Provisional)'] + level0_diff)
- for i in range(len(totals)): # make totals
- level0_diff[i] = flt(totals[i]) + level0_diff[i]
- self.return_data.append([4, 'Total '+c[0]] + level0_diff)
- else:
- self.return_data.append([4, 'Total '+c[0]] + totals)
+
+ if arg['statement'] == 'Balance Sheet': pl = 'No'
+ if arg['statement'] == 'Profit & Loss': pl = 'Yes'
+
+ self.get_children('',0,pl,arg['company'], arg['year'])
+
+ #self.balance_pl_statement(acct, arg['statement'])
+ #msgprint(self.return_data)
+ return self.return_data
+
+ # Get Children
+ # ------------
+ def get_children(self, parent_account, level, pl, company, fy):
+ cl = sql("select distinct account_name, name, debit_or_credit, lft, rgt from `tabAccount` where ifnull(parent_account, '') = %s and ifnull(is_pl_account, 'No')=%s and company=%s and docstatus != 2 order by name asc", (parent_account, pl, company))
+ level0_diff = [0 for p in self.period_list]
+ if pl=='Yes' and level==0: # switch for income & expenses
+ cl = [c for c in cl]
+ cl.reverse()
+ if cl:
+ for c in cl:
+ self.ac_details[c[1]] = [c[2], c[3], c[4]]
+ bal_list = self.get_period_balance(c[1])
+ if level==0: # top level - put balances as totals
+ self.return_data.append([level, c[0]] + ['' for b in bal_list])
+ totals = bal_list
+ for i in range(len(totals)): # make totals
+ if c[2]=='Credit':
+ level0_diff[i] += flt(totals[i])
+ else:
+ level0_diff[i] -= flt(totals[i])
+ else:
+ self.return_data.append([level, c[0]]+bal_list)
+
+ if level < 2:
+ self.get_children(c[1], level+1, pl, company, fy)
+
+ # make totals - for top level
+ # ---------------------------
+ if level==0:
+ # add rows for profit / loss in B/S
+ if pl=='No':
+ if c[2]=='Credit':
+ self.return_data.append([1, 'Total Liabilities'] + totals)
+ level0_diff = [-i for i in level0_diff] # convert to debit
+ self.return_data.append([5, 'Profit/Loss (Provisional)'] + level0_diff)
+ for i in range(len(totals)): # make totals
+ level0_diff[i] = flt(totals[i]) + level0_diff[i]
+ self.return_data.append([4, 'Total '+c[0]] + level0_diff)
+ else:
+ self.return_data.append([4, 'Total '+c[0]] + totals)
- # add rows for profit / loss in P/L
- else:
- if c[2]=='Debit':
- self.return_data.append([1, 'Total Expenses (before Profit)'] + totals)
- self.return_data.append([5, 'Profit/Loss (Provisional)'] + level0_diff)
- for i in range(len(totals)): # make totals
- level0_diff[i] = flt(totals[i]) + level0_diff[i]
- self.return_data.append([4, 'Total '+c[0]] + level0_diff)
- else:
- self.return_data.append([4, 'Total '+c[0]] + totals)
-
- # Define Periods
- # --------------
-
- def define_periods(self, year, period):
-
- # get year start date
- ysd = sql("select year_start_date from `tabFiscal Year` where name=%s", year)
- ysd = ysd and ysd[0][0] or ''
+ # add rows for profit / loss in P/L
+ else:
+ if c[2]=='Debit':
+ self.return_data.append([1, 'Total Expenses (before Profit)'] + totals)
+ self.return_data.append([5, 'Profit/Loss (Provisional)'] + level0_diff)
+ for i in range(len(totals)): # make totals
+ level0_diff[i] = flt(totals[i]) + level0_diff[i]
+ self.return_data.append([4, 'Total '+c[0]] + level0_diff)
+ else:
+ self.return_data.append([4, 'Total '+c[0]] + totals)
+
+ # Define Periods
+ # --------------
+
+ def define_periods(self, year, period):
+
+ # get year start date
+ ysd = sql("select year_start_date from `tabFiscal Year` where name=%s", year)
+ ysd = ysd and ysd[0][0] or ''
- self.ysd = ysd
+ self.ysd = ysd
- # year
- if period == 'Annual':
- pn = 'FY'+year
- self.period_list.append(pn)
- self.period_start_date[pn] = ysd
- self.period_end_date[pn] = get_last_day(get_first_day(ysd,0,11))
+ # year
+ if period == 'Annual':
+ pn = 'FY'+year
+ self.period_list.append(pn)
+ self.period_start_date[pn] = ysd
+ self.period_end_date[pn] = get_last_day(get_first_day(ysd,0,11))
- # quarter
- if period == 'Quarterly':
- for i in range(4):
- pn = 'Q'+str(i+1)
- self.period_list.append(pn)
-
- self.period_start_date[pn] = get_first_day(ysd,0,i*3)
- self.period_end_date[pn] = get_last_day(get_first_day(ysd,0,((i+1)*3)-1))
+ # quarter
+ if period == 'Quarterly':
+ for i in range(4):
+ pn = 'Q'+str(i+1)
+ self.period_list.append(pn)
+
+ self.period_start_date[pn] = get_first_day(ysd,0,i*3)
+ self.period_end_date[pn] = get_last_day(get_first_day(ysd,0,((i+1)*3)-1))
- # month
- if period == 'Monthly':
- mlist = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
- for i in range(12):
- fd = get_first_day(ysd,0,i)
- pn = mlist[fd.month-1]
- self.period_list.append(pn)
-
- self.period_start_date[pn] = fd
- self.period_end_date[pn] = get_last_day(fd)
-
- # Get Balance For A Period
- # ------------------------
-
- def get_period_balance(self, acc, level, pl, company, fy):
- debit_or_credit, lft, rgt = self.ac_details[acc]
- ret = []
- for p in self.period_list:
- sd, ed = self.period_start_date[p].strftime('%Y-%m-%d'), self.period_end_date[p].strftime('%Y-%m-%d')
- cond = "and t1.voucher_type != 'Period Closing Voucher'"
- if pl=='No':
- sd = self.ysd.strftime('%Y-%m-%d')
- cond = ""
+ # month
+ if period == 'Monthly':
+ mlist = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
+ for i in range(12):
+ fd = get_first_day(ysd,0,i)
+ pn = mlist[fd.month-1]
+ self.period_list.append(pn)
+
+ self.period_start_date[pn] = fd
+ self.period_end_date[pn] = get_last_day(fd)
+
+ def get_period_balance(self, acc):
+ ret = []
+ for p in self.period_list:
+ period_end_date = self.period_end_date[p].strftime('%Y-%m-%d')
+ ret.append(get_balance_on(acc, period_end_date))
+ return ret
+
+ def get_top_5_cust(self, company):
+ rec_grp = sql("select receivables_group from tabCompany where name=%s", company)
+ if rec_grp:
+ pa_lft_rgt = sql("select lft, rgt from tabAccount where name=%s and company=%s", (rec_grp[0][0], company))[0]
+ return sql("select t1.account_name, SUM(t2.debit) from tabAccount t1, `tabGL Entry` t2 where t1.lft > %s and t1.rgt < %s and t2.account = t1.name and ifnull(t2.is_cancelled, 'No') = 'No' GROUP BY t1.name ORDER BY SUM(t2.debit) desc limit 5", (pa_lft_rgt[0], pa_lft_rgt[1]))
+ else:
+ return []
- bal = sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= '%s' AND t1.posting_date <= '%s' AND t1.company = '%s' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_opening,'No') = 'No' and ifnull(t1.is_cancelled, 'No') = 'No' %s" % (sd,ed,company,lft,rgt, cond))
-
-
- bal = bal and (flt(bal[0][0]) - flt(bal[0][1])) or 0
- if debit_or_credit == 'Credit' and bal:
- bal = -bal
- if pl=='No':
- op = sql("select opening from `tabAccount Balance` where account=%s and period=%s", (acc, fy))
- op = op and op[0][0] or 0
- bal += flt(op)
-
- ret.append(bal)
- return ret
-
- # Get Dashboard Amounts
- # ---------------------
-
- def get_balance(self, acc, sd, ed, company, fy):
- a = sql("select account_name, name, debit_or_credit, lft, rgt, is_pl_account from `tabAccount` where account_name=%s and company=%s", (acc, company), as_dict=1)
- if a:
- a = a[0]
- bal = sql("select SUM(IFNULL(t1.debit,0)), SUM(IFNULL(t1.credit,0)) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(is_opening, 'No') = 'No' and ifnull(t1.is_cancelled, 'No') = 'No'", (sd,ed,a['lft'],a['rgt']))
- if a['debit_or_credit']=='Debit':
- bal = flt(flt(bal[0][0]) - flt(bal[0][1]))
- else:
- bal = flt(flt(bal[0][1]) - flt(bal[0][0]))
+ def get_top_5_exp(self, company):
+ a = sql("select distinct account_name, name, debit_or_credit, lft, rgt from `tabAccount` where account_name=%s and company=%s", ('Expenses', company), as_dict=1)[0]
+ return sql("select t1.account_name, SUM(t2.debit) from tabAccount t1, `tabGL Entry` t2 where t1.lft>%s and t1.rgt<%s and t1.group_or_ledger = 'Ledger' and t2.account = t1.name and ifnull(t2.is_cancelled, 'No') = 'No' and t2.voucher_type != 'Period Closing Voucher' GROUP BY t1.name ORDER BY SUM(t2.debit) desc limit 5", (a['lft'],a['rgt']))
+
+ def bl(self, acc, company):
+ dt = getdate(nowdate())
- if a['is_pl_account']=='No':
- op = sql("select opening from `tabAccount Balance` where account=%s and period=%s", (acc, fy))
- op = op and op[0][0] or 0
- bal += flt(op)
+ r = []
+ # cur
+ r.append(get_balance_on(acc, get_fiscal_year(nowdate())))
+ # this month
+ r.append(get_balance_on(acc, get_last_day(dt)))
+ # last month
+ r.append(get_balance_on(acc, get_last_day(get_first_day(dt,0,-1))))
+
+ return r
- return flt(bal)
+ def bl_bs(self, acc, company, sd):
+ dt = getdate(nowdate())
+ r = []
+ # cur
+ r.append(get_balance_on(acc, get_fiscal_year(nowdate())))
+ # last month
+ r.append(self.get_balance_on(acc, get_last_day(get_first_day(dt,0,-1))))
+ # opening
+ r.append(self.get_balance_on(acc, sd))
+ return r
- else:
- msgprint("Did not find %s for %s" % (acc, company))
- return 0
+ def get_dashboard_values(self, arg=''):
+ d = get_defaults()
+ self.fiscal_year = d['fiscal_year']
+ if arg:
+ company = arg
+ else:
+ company = d['company']
- def get_cur_balance(self, acc, company):
- bal = sql("select IFNULL(t1.balance,0) from `tabAccount Balance` t1, `tabAccount` t2 where t1.account = %s and t1.period=%s and t1.account = t2.name and t2.company=%s", (acc, self.fiscal_year, company))
- return bal and flt(bal[0][0]) or 0
-
- def get_top_5_cust(self, company):
- rec_grp = sql("select receivables_group from tabCompany where name=%s", company)
- if rec_grp:
- pa_lft_rgt = sql("select lft, rgt from tabAccount where name=%s and company=%s", (rec_grp[0][0], company))[0]
- return sql("select t1.account_name, SUM(t2.debit) from tabAccount t1, `tabGL Entry` t2 where t1.lft > %s and t1.rgt < %s and t2.account = t1.name and ifnull(t2.is_cancelled, 'No') = 'No' GROUP BY t1.name ORDER BY SUM(t2.debit) desc limit 5", (pa_lft_rgt[0], pa_lft_rgt[1]))
- else:
- return []
+ r = {}
+ r['Income'] = self.bl('Income', company)
+ r['Expenses'] = self.bl('Expenses', company)
- def get_top_5_exp(self, company):
- a = sql("select distinct account_name, name, debit_or_credit, lft, rgt from `tabAccount` where account_name=%s and company=%s", ('Expenses', company), as_dict=1)[0]
- return sql("select t1.account_name, SUM(t2.debit) from tabAccount t1, `tabGL Entry` t2 where t1.lft>%s and t1.rgt<%s and t1.group_or_ledger = 'Ledger' and t2.account = t1.name and ifnull(t2.is_cancelled, 'No') = 'No' and t2.voucher_type != 'Period Closing Voucher' GROUP BY t1.name ORDER BY SUM(t2.debit) desc limit 5", (a['lft'],a['rgt']))
-
- def bl(self, acc, company):
- dt = getdate(nowdate())
+ r['Profit'] = []
+ for i in range(3):
+ r['Profit'].append(r['Income'][i] - r['Expenses'][i])
+
+ r['Current Assets'] = self.bl_bs('Current Assets', company, getdate(d['year_start_date']))
+ r['Current Liabilities'] = self.bl_bs('Current Liabilities', company, getdate(d['year_start_date']))
+
+ r['Working Capital'] = []
+ for i in range(3):
+ r['Working Capital'].append(r['Current Assets'][i] - r['Current Liabilities'][i])
- r = []
- # cur
- r.append(self.get_cur_balance(acc, company))
- # this month
- r.append(self.get_balance(acc, get_first_day(dt), get_last_day(dt), company, self.fiscal_year))
- # last month
- r.append(self.get_balance(acc, get_first_day(dt,0,-1), get_last_day(get_first_day(dt,0,-1)), company, self.fiscal_year))
- return r
-
- def bl_bs(self, acc, company, sd):
- dt = getdate(nowdate())
- r = []
- # cur
- r.append(self.get_cur_balance(acc, company))
- # last month
- r.append(self.get_balance(acc, sd, get_last_day(get_first_day(dt,0,-1)), company, self.fiscal_year))
- # opening
- r.append(self.get_balance(acc, sd, sd, company, self.fiscal_year))
- return r
-
- def get_dashboard_values(self, arg=''):
- d = get_defaults()
- self.fiscal_year = d['fiscal_year']
- if arg:
- company = arg
- else:
- company = d['company']
-
- r = {}
- r['Income'] = self.bl('Income', company)
- r['Expenses'] = self.bl('Expenses', company)
-
- r['Profit'] = []
- for i in range(3):
- r['Profit'].append(r['Income'][i] - r['Expenses'][i])
-
- r['Current Assets'] = self.bl_bs('Current Assets', company, getdate(d['year_start_date']))
- r['Current Liabilities'] = self.bl_bs('Current Liabilities', company, getdate(d['year_start_date']))
-
- r['Working Capital'] = []
- for i in range(3):
- r['Working Capital'].append(r['Current Assets'][i] - r['Current Liabilities'][i])
-
- r['Bank Accounts'] = self.bl_bs('Bank Accounts', company, getdate(d['year_start_date']))
-
- r['Top Customers'] = convert_to_lists(self.get_top_5_cust(company))
- r['Top Expenses'] = convert_to_lists(self.get_top_5_exp(company))
-
- return r
+ r['Bank Accounts'] = self.bl_bs('Bank Accounts', company, getdate(d['year_start_date']))
+
+ r['Top Customers'] = convert_to_lists(self.get_top_5_cust(company))
+ r['Top Expenses'] = convert_to_lists(self.get_top_5_exp(company))
+
+ return r
diff --git a/erpnext/accounts/doctype/multi_ledger_report/__init__.py b/erpnext/accounts/doctype/multi_ledger_report/__init__.py
deleted file mode 100755
index baffc48..0000000
--- a/erpnext/accounts/doctype/multi_ledger_report/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.js b/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.js
deleted file mode 100755
index 2dabb66..0000000
--- a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.js
+++ /dev/null
@@ -1,19 +0,0 @@
-// ERPNext - web based ERP (http://erpnext.com)
-// Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-//
-// This program is free software: you can redistribute it and/or modify
-// it under the terms of the GNU General Public License as published by
-// the Free Software Foundation, either version 3 of the License, or
-// (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-cur_frm.cscript.export_report = function(doc, cdt, cdn) {
- $c_obj_csv(make_doclist(cdt, cdn), 'get_report_data', '');
-}
diff --git a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.py b/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.py
deleted file mode 100755
index 53d011f..0000000
--- a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.py
+++ /dev/null
@@ -1,150 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-from __future__ import unicode_literals
-import webnotes
-from webnotes.utils import add_days, cint, cstr, flt, getdate
-from webnotes.model.doclist import getlist
-from webnotes.model.code import get_obj
-from webnotes import session, form, is_testing, msgprint, errprint
-
-sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-
-#---------------------------------------------------------------------
-
-class DocType:
- def __init__(self, doc, doclist=[]):
- self.doc = doc
- self.doclist = doclist
-
- # Get fiscal year based on date
- def get_year(self, dt):
- yr = sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",dt)
- return yr and yr[0][0] or ''
-
-
- def validate_date(self):
- """check for from date and to date within same year"""
- if not sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day) and %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",(self.doc.from_date, self.doc.to_date)):
- msgprint("From Date and To Date must be within same year")
- raise Exception
-
- if not self.doc.from_date or not self.doc.to_date:
- msgprint("From Date and To Date is mandatory")
- raise Exception
-
-
- def add_header(self):
- title = 'Ledger Balances Between ' + getdate(self.doc.from_date).strftime('%d-%m-%Y') + ' and ' + getdate(self.doc.to_date).strftime('%d-%m-%Y')
- return [[title], ['Account', 'Posting Date', 'Voucher Type', 'Voucher No', 'Debit', 'Credit', 'Remarks']]
-
-
-
- def get_account_subtree(self, acc):
- return sql("""
- SELECT
- CONCAT(REPEAT(' ', COUNT(parent.name) - (sub_tree.depth + 1)), node.name) as account,
- node.lft AS lft, node.rgt AS rgt,
- node.debit_or_credit as dr_or_cr, node.group_or_ledger as group_or_ledger, node.is_pl_account as is_pl_account
- FROM tabAccount AS node,
- tabAccount AS parent,
- tabAccount AS sub_parent,
- (
- SELECT node.name, (COUNT(parent.name) - 1) AS depth
- FROM tabAccount AS node, tabAccount AS parent
- WHERE node.lft BETWEEN parent.lft AND parent.rgt
- AND node.name = %s
- GROUP BY node.name
- ORDER BY node.lft
- )AS sub_tree
- WHERE node.lft BETWEEN parent.lft AND parent.rgt
- AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
- AND sub_parent.name = sub_tree.name
- GROUP BY node.name
- ORDER BY node.lft""", acc, as_dict = 1, as_utf8=1)
-
-
-
- def get_acc_summary(self, glc, acc_det):
- from_date_year = self.get_year(add_days(self.doc.from_date, -1))
- to_date_year = self.get_year(self.doc.to_date)
- acc = acc_det['account'].strip()
-
- if from_date_year == to_date_year:
- debit_on_fromdate, credit_on_fromdate, opening = glc.get_as_on_balance(acc, from_date_year, add_days(self.doc.from_date, -1), acc_det['dr_or_cr'], acc_det['lft'], acc_det['rgt']) # opening = closing of prev_date
- elif acc_det['is_pl_account'] == 'No': # if there is no previous year in system and not pl account
- opening = sql("select opening from `tabAccount Balance` where account = %s and period = %s", (acc, to_date_year))
- debit_on_fromdate, credit_on_fromdate, opening = 0, 0, flt(opening[0][0])
- else: # if pl account and there is no previous year in system
- debit_on_fromdate, credit_on_fromdate, opening = 0,0,0
-
- # closing balance
- #--------------------------------
- debit_on_todate, credit_on_todate, closing = glc.get_as_on_balance(acc, to_date_year, self.doc.to_date, acc_det['dr_or_cr'], acc_det['lft'], acc_det['rgt'])
-
- # transaction betn the period
- #----------------------------------------
- debit = flt(debit_on_todate) - flt(debit_on_fromdate)
- credit = flt(credit_on_todate) - flt(credit_on_fromdate)
-
- # Debit / Credit
- if acc_det['dr_or_cr'] == 'Credit':
- opening, closing = -1*opening, -1*closing
-
- return flt(opening>0 and opening or 0), flt(opening<0 and -opening or 0), \
- debit, credit, flt(closing>0.01 and closing or 0), flt(closing<-0.01 and -closing or 0)
-
-
- def show_gl_entries(self, acc):
- """Get gl entries for the period and account"""
- gle = sql("select posting_date, voucher_type, voucher_no, debit, credit, remarks from `tabGL Entry` WHERE account = %s and posting_date >= %s AND posting_date <= %s and ifnull(is_opening, 'No') = 'No' and ifnull(is_cancelled, 'No') = 'No'", (acc, self.doc.from_date, self.doc.to_date), as_dict=1, as_utf8=1)
- entries, dr, cr = [], 0, 0
- for d in gle:
- entries.append(['', d['posting_date'], d['voucher_type'], d['voucher_no'], d['debit'], d['credit'], d['remarks']])
- return entries
-
-
-
-
- # Get Report Data
- def get_report_data(self):
- self.validate_date()
-
- res = []
- res += self.add_header()
-
- glc = get_obj('GL Control')
-
- for d in getlist(self.doclist, 'ledger_details'):
- # Fetch acc details
- sub_tree = self.get_account_subtree(d.account)
-
- for acc_det in sub_tree:
- acc_summary = self.get_acc_summary(glc, acc_det)
- if acc_summary[0] or acc_summary[1] or acc_summary[2] or acc_summary[3] or acc_summary[4] or acc_summary[5]:
- res.append([acc_det['account']])
- # Show gl entries if account is ledger
- if acc_det['group_or_ledger'] == 'Ledger' and (acc_summary[2] or acc_summary[3]):
- gle = self.show_gl_entries(acc_det['account'].strip())
- res += gle
-
- # Totals
- res.append(['', '', '', 'Total Debit/Credit', acc_summary[2], acc_summary[3]])
- res.append(['', '', '', 'Opening Balance', acc_summary[0], acc_summary[1]])
- res.append(['', '', '', 'Closing Balance', acc_summary[4], acc_summary[5]])
-
- return res
diff --git a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.txt b/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.txt
deleted file mode 100755
index 0ef7a5d..0000000
--- a/erpnext/accounts/doctype/multi_ledger_report/multi_ledger_report.txt
+++ /dev/null
@@ -1,124 +0,0 @@
-# DocType, Multi Ledger Report
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:35:44',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:35:44',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all DocType
- {
- '_last_update': u'1306229235',
- 'allow_copy': 1,
- 'allow_email': 1,
- 'allow_print': 1,
- 'colour': u'White:FFF',
- 'doctype': 'DocType',
- 'document_type': u'Other',
- 'hide_heading': 0,
- 'issingle': 1,
- 'module': u'Accounts',
- 'name': '__common__',
- 'section_style': u'Simple',
- 'show_in_menu': 0,
- 'version': 9
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Multi Ledger Report',
- 'parentfield': u'fields',
- 'parenttype': u'DocType',
- 'permlevel': 0
- },
-
- # These values are common for all DocPerm
- {
- 'create': 1,
- 'doctype': u'DocPerm',
- 'name': '__common__',
- 'parent': u'Multi Ledger Report',
- 'parentfield': u'permissions',
- 'parenttype': u'DocType',
- 'permlevel': 0,
- 'read': 1,
- 'write': 1
- },
-
- # DocType, Multi Ledger Report
- {
- 'doctype': 'DocType',
- 'name': u'Multi Ledger Report'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm',
- 'role': u'Accounts Manager'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm',
- 'role': u'Accounts User'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'select_date_range',
- 'fieldtype': u'Column Break',
- 'label': u'Select Date Range'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'from_date',
- 'fieldtype': u'Date',
- 'label': u'From Date',
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'to_date',
- 'fieldtype': u'Date',
- 'label': u'To Date',
- 'reqd': 1
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'select_ledgers',
- 'fieldtype': u'Column Break',
- 'label': u'Select ledgers'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'ledger_details',
- 'fieldtype': u'Table',
- 'label': u'Multi Ledger Report Details',
- 'options': u'Multi Ledger Report Detail'
- },
-
- # DocField
- {
- 'colour': u'White:FFF',
- 'doctype': u'DocField',
- 'fieldname': u'export_report',
- 'fieldtype': u'Button',
- 'label': u'Export Report',
- 'trigger': u'Client'
- }
-]
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
index 860fdd1..a9b7740 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -63,7 +63,8 @@
def validate_posting_date(self):
- yr = sql("select start_date, end_date from `tabPeriod` where fiscal_year = '%s' and period_type = 'Year'" % (self.doc.fiscal_year))
+ yr = sql("""select year_start_date, adddate(year_start_date, interval 1 year)
+ from `tabFiscal Year` where name=%s""", (self.doc.fiscal_year, ))
self.year_start_date = yr and yr[0][0] or ''
self.year_end_date = yr and yr[0][1] or ''
diff --git a/erpnext/accounts/page/accounts_browser/accounts_browser.js b/erpnext/accounts/page/accounts_browser/accounts_browser.js
index 55d6be9..e5a30b0 100644
--- a/erpnext/accounts/page/accounts_browser/accounts_browser.js
+++ b/erpnext/accounts/page/accounts_browser/accounts_browser.js
@@ -122,8 +122,8 @@
+encodeURIComponent(data.value)+'">Edit</a>');
}
if (data.expandable) {
- if (wn.boot.profile.can_create.indexOf(this.ctype) !== -1 ||
- wn.boot.profile.in_create.indexOf(this.ctype) !== -1) {
+ if((wn.boot.profile.can_create.indexOf(this.ctype) !== -1) ||
+ (wn.boot.profile.in_create.indexOf(this.ctype) !== -1)) {
node_links.push('<a onclick="erpnext.account_chart.new_node();">Add Child</a>');
}
} else if (this.ctype === 'Account' && wn.boot.profile.can_read.indexOf("GL Entry") !== -1) {
diff --git a/erpnext/accounts/page/accounts_browser/accounts_browser.py b/erpnext/accounts/page/accounts_browser/accounts_browser.py
index aa955ae..76c64b7 100644
--- a/erpnext/accounts/page/accounts_browser/accounts_browser.py
+++ b/erpnext/accounts/page/accounts_browser/accounts_browser.py
@@ -1,6 +1,7 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import get_defaults, cstr
+from webnotes.utils import get_defaults, fmt_money
+from accounts.utils import get_balance_on
@webnotes.whitelist()
def get_companies():
@@ -49,16 +50,7 @@
if ctype == 'Account':
currency = webnotes.conn.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
for each in acc:
- bal = webnotes.conn.sql("select balance from `tabAccount Balance` \
- where account = %s and period = %s", (each.get('value'), get_defaults('fiscal_year')))
- bal = bal and bal[0][0] or 0
- each['balance'] = currency + ' ' + cstr(bal)
+ bal = get_balance_on(each.get("value"))
+ each['balance'] = currency + ' ' + fmt_money(bal)
return acc
-
-
-@webnotes.whitelist()
-def get_account_balance():
- args = webnotes.form_dict
- acc = args['acc']
- return 'Rs. 100'
\ No newline at end of file
diff --git a/erpnext/accounts/page/accounts_home/accounts_home.html b/erpnext/accounts/page/accounts_home/accounts_home.html
index 11cf126..d9b506b 100644
--- a/erpnext/accounts/page/accounts_home/accounts_home.html
+++ b/erpnext/accounts/page/accounts_home/accounts_home.html
@@ -60,11 +60,6 @@
</div>
<div class="section-item">
<a class="section-link"
- title="Export multiple Account Ledgers (GL) to spreadsheet (csv)"
- href="#!Form/Multi Ledger Report/Multi Ledger Report">Export Multiple Ledgers (GL)</a>
- </div>
- <div class="section-item">
- <a class="section-link"
title = "Helper for managing return of goods (sales or purchase)"
href="#!Form/Sales and Purchase Return Tool/Sales and Purchase Return Tool">Sales or Purchase Returns</a>
</div>
diff --git a/erpnext/accounts/search_criteria/bank_reconciliation_statement/bank_reconciliation_statement.py b/erpnext/accounts/search_criteria/bank_reconciliation_statement/bank_reconciliation_statement.py
index 3250f02..af1aac3 100644
--- a/erpnext/accounts/search_criteria/bank_reconciliation_statement/bank_reconciliation_statement.py
+++ b/erpnext/accounts/search_criteria/bank_reconciliation_statement/bank_reconciliation_statement.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
#get company
from __future__ import unicode_literals
@@ -20,10 +20,10 @@
# To date
if not filter_values.get('clearance_date1'):
- msgprint('Please enter To Clearance Date')
- raise Exception
+ msgprint('Please enter To Clearance Date')
+ raise Exception
else:
- to_date = filter_values['clearance_date1']
+ to_date = filter_values['clearance_date1']
#Fiscal year and year start date
@@ -31,32 +31,33 @@
ysd, fiscal_year = sql("select year_start_date, name from `tabFiscal Year` where %s between year_start_date and date_add(year_start_date,interval 1 year)",to_date)[0]
# Account
if not filter_values.get('account'):
- msgprint('Please select Account in filter section')
- raise Exception
+ msgprint('Please select Account in filter section')
+ raise Exception
else:
- acc_name = filter_values.get('account')
+ acc_name = filter_values.get('account')
if len(res) > 300 and from_export == 0:
- msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
- raise Exception
+ msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
+ raise Exception
acc = sql("select debit_or_credit, is_pl_account, lft, rgt from tabAccount where name = '%s'" % acc_name)
-opening = get_obj('GL Control').get_as_on_balance(acc_name, fiscal_year, to_date, acc[0][0], acc[0][2], acc[0][3])[2]
+from accounts.utils import get_balance_on
+opening = get_balance_on(acc_name, to_date)
total_debit, total_credit = 0,0
out = []
for r in res:
- total_debit = flt(total_debit) + flt(r[col_idx['Debit']])
- total_credit = flt(total_credit) + flt(r[col_idx['Credit']])
- out.append(r)
+ total_debit = flt(total_debit) + flt(r[col_idx['Debit']])
+ total_credit = flt(total_credit) + flt(r[col_idx['Credit']])
+ out.append(r)
if acc and acc[0][0] == 'Debit':
- bank_bal = flt(opening)-flt(total_debit)+flt(total_credit)
+ bank_bal = flt(opening)-flt(total_debit)+flt(total_credit)
else:
- bank_bal = flt(opening)+flt(total_debit)-flt(total_credit)
+ bank_bal = flt(opening)+flt(total_debit)-flt(total_credit)
out.append(['','','','','','<font color = "#000"><b>Balance as per Company Books: </b></font>', opening,'',''])
out.append(['','','','','','<font color = "#000"><b>Amounts not reflected in Bank: </b></font>', total_debit,total_credit,''])
diff --git a/erpnext/accounts/search_criteria/cash_flow_statement/__init__.py b/erpnext/accounts/search_criteria/cash_flow_statement/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/accounts/search_criteria/cash_flow_statement/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/search_criteria/cash_flow_statement/cash_flow_statement.py b/erpnext/accounts/search_criteria/cash_flow_statement/cash_flow_statement.py
deleted file mode 100644
index cf7d075..0000000
--- a/erpnext/accounts/search_criteria/cash_flow_statement/cash_flow_statement.py
+++ /dev/null
@@ -1,127 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-from __future__ import unicode_literals
-cl = [['Account','Data', '200px'],['Debit/Credit', 'Data', '100px'], ['Group/Ledger', 'Data', '100px'], ['Opening','Data', '100px'],['Closing', 'Data', '100px'],['Inc in Cash','Data','100px']]
-
-for c in cl:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append('')
- col_idx[c[0]] = len(colnames)-1
-
-
-company = filter_values['company']
-
-# transaction date
-if not filter_values.get('transaction_date') or not filter_values.get('transaction_date1'):
- msgprint("Please enter From Date and To Date")
- raise Exception
-else:
- from_date = add_days(filter_values['transaction_date'], -1)
- to_date = filter_values['transaction_date1']
-
-ysd, fiscal_year = sql("select year_start_date, name from `tabFiscal Year` where %s between year_start_date and date_add(year_start_date,interval 1 year)",from_date)[0]
-
-
-if from_export == 0 and len(res) >250:
- msgprint("This is very large report and cannot be shown in the browser as it is likely to make your browser very slow. Please click on 'Export' to open in excel")
- raise Exception
-
-total_debit, total_credit, total = 0,0,0
-glc = get_obj('GL Control')
-
-for r in res:
- acc = r[col_idx['Account']].strip()
- acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % acc)
- r.append(acc_det[0][0])
- r.append(acc_det[0][4])
-
- opening = glc.get_as_on_balance(acc, fiscal_year, from_date, acc_det[0][0], acc_det[0][2], acc_det[0][3])[2]
-
- amount = sql("select sum(t1.debit), sum(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= '%s' AND t1.posting_date <= '%s' and ifnull(t1.is_opening,'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s AND is_cancelled = 'No'" % (from_date,to_date, acc_det[0][2], acc_det[0][3]))
- if acc_det[0][0] == 'Debit':
- closing = opening + flt(amount[0][0]) - flt(amount[0][1])
- else:
- closing = opening + flt(amount[0][1]) - flt(amount[0][0])
-
- r.append(fmt_money(flt(opening)))
- r.append(fmt_money(flt(closing)))
-
- diff = flt(closing) - flt(opening)
- if acc_det[0][0]=='Debit':
- r.append(fmt_money(-diff))
- total -= diff
- else:
- r.append(fmt_money(diff))
- total += diff
-
-
-# net profit
-# ------------------
-
-acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where account_name = %s AND company=%s", ('Income',company))
-amount = sql("select sum(t1.debit), sum(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= '%s' AND t1.posting_date <= '%s' and ifnull(t1.is_opening,'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s AND is_cancelled = 'No'" % (from_date,to_date, acc_det[0][2], acc_det[0][3]))
-net_income = flt(amount[0][1]) - flt(amount[0][0])
-
-acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where account_name = %s AND company=%s", ('Expenses',company))
-amount = sql("select sum(t1.debit), sum(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= '%s' AND t1.posting_date <= '%s' and ifnull(t1.is_opening,'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s AND is_cancelled = 'No'" % (from_date,to_date, acc_det[0][2], acc_det[0][3]))
-net_expenses = flt(amount[0][0]) - flt(amount[0][1])
-
-t_row = ['' for i in range(len(colnames))]
-t_row[col_idx['Account']] = 'Net Profit'
-t_row[col_idx['Inc in Cash']] = fmt_money(net_income - net_expenses)
-
-total += net_income - net_expenses
-
-res.append(t_row)
-
-# total row
-# ------------------
-t_row = ['' for i in range(len(colnames))]
-t_row[col_idx['Account']] = 'Total Cash Generated'
-t_row[col_idx['Inc in Cash']] = fmt_money(total)
-
-res.append(t_row)
-
-# Show Inc / Dec in Bank and Cash Accounts
-# ----------------------------------------
-
-t_row = ['' for i in range(len(colnames))]
-res.append(t_row)
-
-acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger, name from tabAccount where account_type = 'Bank or Cash' AND company=%s AND level=%s", (company, cint(filter_values['level'])))
-for acc in acc_det:
- r = [acc[5],]
-
- opening = glc.get_as_on_balance(acc[5], fiscal_year, from_date, acc[0], acc[2], acc[3])[2]
-
- amount = sql("select sum(t1.debit), sum(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= '%s' AND t1.posting_date <= '%s' and ifnull(t1.is_opening,'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s AND is_cancelled = 'No'" % (from_date,to_date, acc[2], acc[3]))
- closing = opening + flt(amount[0][0]) - flt(amount[0][1])
- diff = closing - opening
-
-
- r.append(acc_det[0][0])
- r.append(acc_det[0][4])
-
- r.append(fmt_money(flt(opening)))
- r.append(fmt_money(flt(closing)))
-
- r.append(fmt_money(diff))
-
- res.append(r)
-
diff --git a/erpnext/accounts/search_criteria/creditors_ledger/creditors_ledger.py b/erpnext/accounts/search_criteria/creditors_ledger/creditors_ledger.py
index 6635922..8a3030d 100644
--- a/erpnext/accounts/search_criteria/creditors_ledger/creditors_ledger.py
+++ b/erpnext/accounts/search_criteria/creditors_ledger/creditors_ledger.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
#get company
from __future__ import unicode_literals
@@ -25,11 +25,11 @@
# Posting date, fiscal year and year start date
#-----------------------------------------------
if not filter_values.get('posting_date') or not filter_values.get('posting_date1'):
- msgprint("Please enter From Date and To Date")
- raise Exception
+ msgprint("Please enter From Date and To Date")
+ raise Exception
else:
- from_date = filter_values['posting_date']
- to_date = filter_values['posting_date1']
+ from_date = filter_values['posting_date']
+ to_date = filter_values['posting_date1']
ysd, from_date_year = sql("select year_start_date, name from `tabFiscal Year` where %s between year_start_date and date_add(year_start_date,interval 1 year)",from_date)[0]
@@ -43,11 +43,11 @@
col.append(['Credit','Currency','75px',''])
for c in col:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append(c[3])
- col_idx[c[0]] = len(colnames)
+ colnames.append(c[0])
+ coltypes.append(c[1])
+ colwidths.append(c[2])
+ coloptions.append(c[3])
+ col_idx[c[0]] = len(colnames)
total_debit, total_credit, total_opening, total_diff = 0,0,0,0
@@ -55,67 +55,69 @@
#total query
q = query.split('WHERE')[1].split('LIMIT')
if len(q) > 2:
- query_where_clause = 'LIMIT'.join(q[:-1])
+ query_where_clause = 'LIMIT'.join(q[:-1])
else:
- query_where_clause = q[0]
+ query_where_clause = q[0]
tot = sql('select sum(`tabGL Entry`.debit),sum(`tabGL Entry`.credit) from `tabGL Entry`, tabAccount where %s' % query_where_clause)
for t in tot:
- total_debit += t and flt(t[0]) or 0
- total_credit += t and flt(t[1]) or 0
+ total_debit += t and flt(t[0]) or 0
+ total_credit += t and flt(t[1]) or 0
total_diff = total_debit - total_credit
# opening
account = filter_values.get('account')
if account:
- acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
- opening_bal = get_obj('GL Control').get_as_on_balance(account, from_date_year, add_days(from_date, -1), acc_det[0][0], acc_det[0][2], acc_det[0][3])[2]
- if acc_det[0][0] == 'Credit':
- opening_bal = -1*opening_bal
-
+ acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
+ from accounts.utils import get_balance_on
+ opening_bal = get_balance_on(account, add_days(from_date, -1))
+
+ if acc_det[0][0] == 'Credit':
+ opening_bal = -1*opening_bal
+
out = []
count = 0
for r in res:
- count +=1
- det = r[1].split('~~~')
- if from_export == 1:
- a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
- else:
- a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
- r[1] = a
- out.append(r)
+ count +=1
+ det = r[1].split('~~~')
+ if from_export == 1:
+ a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
+ else:
+ a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
+ r[1] = a
+ out.append(r)
if total_debit != 0 or total_credit != 0:
- # Total debit/credit
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total'
- t_row[col_idx['Debit']-1] = total_debit
- t_row[col_idx['Credit']-1] = total_credit
- out.append(t_row)
-
- # opening
- if account:
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Opening Balance on '+ from_date
- t_row[col_idx['Debit']-1] = opening_bal
- out.append(t_row)
-
- # diffrence (dr-cr)
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total(Dr-Cr)'
- t_row[col_idx['Debit']-1] = total_diff
- out.append(t_row)
+ # Total debit/credit
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total'
+ t_row[col_idx['Debit']-1] = total_debit
+ t_row[col_idx['Credit']-1] = total_credit
+ out.append(t_row)
+
+ # opening
+ if account:
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Opening Balance on '+ from_date
+ t_row[col_idx['Debit']-1] = opening_bal
+ out.append(t_row)
+
+ # diffrence (dr-cr)
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total(Dr-Cr)'
+ t_row[col_idx['Debit']-1] = total_diff
+ out.append(t_row)
- # closing
- if account:
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Closing Balance on ' + to_date
- t_row[col_idx['Debit']-1] = flt(opening_bal) + flt(total_diff )
- out.append(t_row)
-
+ # closing
+ if account:
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Closing Balance on ' + to_date
+ t_row[col_idx['Debit']-1] = flt(opening_bal) + flt(total_diff )
+ out.append(t_row)
+
# Print Format
myheader = """<table width = '100%%'><tr><td>"""+l_head+"""</td>
</tr>
@@ -123,8 +125,8 @@
<div><h3> %(acc)s </h3></div>
<div>Ledger Between %(fdt)s and %(tdt)s </div></td></tr></table><br>
- """ % {'acc':account,
- 'fdt':from_date,
- 'tdt':to_date}
+ """ % {'acc':account,
+ 'fdt':from_date,
+ 'tdt':to_date}
page_template = myheader+"<div>%(table)s</div>"
\ No newline at end of file
diff --git a/erpnext/accounts/search_criteria/debtors_ledger/debtors_ledger.py b/erpnext/accounts/search_criteria/debtors_ledger/debtors_ledger.py
index 6635922..6ba458a 100644
--- a/erpnext/accounts/search_criteria/debtors_ledger/debtors_ledger.py
+++ b/erpnext/accounts/search_criteria/debtors_ledger/debtors_ledger.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
#get company
from __future__ import unicode_literals
@@ -25,11 +25,11 @@
# Posting date, fiscal year and year start date
#-----------------------------------------------
if not filter_values.get('posting_date') or not filter_values.get('posting_date1'):
- msgprint("Please enter From Date and To Date")
- raise Exception
+ msgprint("Please enter From Date and To Date")
+ raise Exception
else:
- from_date = filter_values['posting_date']
- to_date = filter_values['posting_date1']
+ from_date = filter_values['posting_date']
+ to_date = filter_values['posting_date1']
ysd, from_date_year = sql("select year_start_date, name from `tabFiscal Year` where %s between year_start_date and date_add(year_start_date,interval 1 year)",from_date)[0]
@@ -43,11 +43,11 @@
col.append(['Credit','Currency','75px',''])
for c in col:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append(c[3])
- col_idx[c[0]] = len(colnames)
+ colnames.append(c[0])
+ coltypes.append(c[1])
+ colwidths.append(c[2])
+ coloptions.append(c[3])
+ col_idx[c[0]] = len(colnames)
total_debit, total_credit, total_opening, total_diff = 0,0,0,0
@@ -55,67 +55,68 @@
#total query
q = query.split('WHERE')[1].split('LIMIT')
if len(q) > 2:
- query_where_clause = 'LIMIT'.join(q[:-1])
+ query_where_clause = 'LIMIT'.join(q[:-1])
else:
- query_where_clause = q[0]
+ query_where_clause = q[0]
tot = sql('select sum(`tabGL Entry`.debit),sum(`tabGL Entry`.credit) from `tabGL Entry`, tabAccount where %s' % query_where_clause)
for t in tot:
- total_debit += t and flt(t[0]) or 0
- total_credit += t and flt(t[1]) or 0
+ total_debit += t and flt(t[0]) or 0
+ total_credit += t and flt(t[1]) or 0
total_diff = total_debit - total_credit
# opening
account = filter_values.get('account')
if account:
- acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
- opening_bal = get_obj('GL Control').get_as_on_balance(account, from_date_year, add_days(from_date, -1), acc_det[0][0], acc_det[0][2], acc_det[0][3])[2]
- if acc_det[0][0] == 'Credit':
- opening_bal = -1*opening_bal
-
+ acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
+ from accounts.utils import get_balance_on
+ opening_bal = get_balance_on(account, add_days(from_date, -1))
+ if acc_det[0][0] == 'Credit':
+ opening_bal = -1*opening_bal
+
out = []
count = 0
for r in res:
- count +=1
- det = r[1].split('~~~')
- if from_export == 1:
- a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
- else:
- a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
- r[1] = a
- out.append(r)
+ count +=1
+ det = r[1].split('~~~')
+ if from_export == 1:
+ a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
+ else:
+ a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
+ r[1] = a
+ out.append(r)
if total_debit != 0 or total_credit != 0:
- # Total debit/credit
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total'
- t_row[col_idx['Debit']-1] = total_debit
- t_row[col_idx['Credit']-1] = total_credit
- out.append(t_row)
-
- # opening
- if account:
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Opening Balance on '+ from_date
- t_row[col_idx['Debit']-1] = opening_bal
- out.append(t_row)
-
- # diffrence (dr-cr)
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total(Dr-Cr)'
- t_row[col_idx['Debit']-1] = total_diff
- out.append(t_row)
+ # Total debit/credit
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total'
+ t_row[col_idx['Debit']-1] = total_debit
+ t_row[col_idx['Credit']-1] = total_credit
+ out.append(t_row)
+
+ # opening
+ if account:
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Opening Balance on '+ from_date
+ t_row[col_idx['Debit']-1] = opening_bal
+ out.append(t_row)
+
+ # diffrence (dr-cr)
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total(Dr-Cr)'
+ t_row[col_idx['Debit']-1] = total_diff
+ out.append(t_row)
- # closing
- if account:
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Closing Balance on ' + to_date
- t_row[col_idx['Debit']-1] = flt(opening_bal) + flt(total_diff )
- out.append(t_row)
-
+ # closing
+ if account:
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Closing Balance on ' + to_date
+ t_row[col_idx['Debit']-1] = flt(opening_bal) + flt(total_diff )
+ out.append(t_row)
+
# Print Format
myheader = """<table width = '100%%'><tr><td>"""+l_head+"""</td>
</tr>
@@ -123,8 +124,8 @@
<div><h3> %(acc)s </h3></div>
<div>Ledger Between %(fdt)s and %(tdt)s </div></td></tr></table><br>
- """ % {'acc':account,
- 'fdt':from_date,
- 'tdt':to_date}
+ """ % {'acc':account,
+ 'fdt':from_date,
+ 'tdt':to_date}
page_template = myheader+"<div>%(table)s</div>"
\ No newline at end of file
diff --git a/erpnext/accounts/search_criteria/general_ledger/general_ledger.py b/erpnext/accounts/search_criteria/general_ledger/general_ledger.py
index 1f02478..2533238 100644
--- a/erpnext/accounts/search_criteria/general_ledger/general_ledger.py
+++ b/erpnext/accounts/search_criteria/general_ledger/general_ledger.py
@@ -8,11 +8,11 @@
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
#get company letter head
#---------------------------------------------------------------------
@@ -24,11 +24,11 @@
# Posting date, fiscal year and year start date
#---------------------------------------------------------------------
if not filter_values.get('posting_date') or not filter_values.get('posting_date1'):
- msgprint("Please enter From Date and To Date")
- raise Exception
+ msgprint("Please enter From Date and To Date")
+ raise Exception
else:
- from_date = filter_values['posting_date']
- to_date = filter_values['posting_date1']
+ from_date = filter_values['posting_date']
+ to_date = filter_values['posting_date1']
from_date_year = sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",from_date)
if not from_date_year:
@@ -47,11 +47,11 @@
col.append(['Credit','Currency','75px',''])
for c in col:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append(c[3])
- col_idx[c[0]] = len(colnames)
+ colnames.append(c[0])
+ coltypes.append(c[1])
+ colwidths.append(c[2])
+ coloptions.append(c[3])
+ col_idx[c[0]] = len(colnames)
#total query
@@ -59,15 +59,15 @@
total_debit, total_credit, total_opening, total_diff = 0,0,0,0
q = query.split('WHERE')[1].split('LIMIT')
if len(q) > 2:
- query_where_clause = 'LIMIT'.join(q[:-1])
+ query_where_clause = 'LIMIT'.join(q[:-1])
else:
- query_where_clause = q[0]
+ query_where_clause = q[0]
tot = sql('select sum(debit),sum(credit) from `tabGL Entry` where %s' % query_where_clause)
for t in tot:
- total_debit += t and flt(t[0]) or 0
- total_credit += t and flt(t[1]) or 0
+ total_debit += t and flt(t[0]) or 0
+ total_credit += t and flt(t[1]) or 0
total_diff = total_debit - total_credit
@@ -81,12 +81,13 @@
if account and (total_debit != 0 or total_credit != 0):
acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
- opening_bal = get_obj('GL Control').get_as_on_balance(account, from_date_year, add_days(from_date, -1), acc_det[0][0], acc_det[0][2], acc_det[0][3])[2]
- closing_bal = get_obj('GL Control').get_as_on_balance(account, from_date_year, to_date, acc_det[0][0], acc_det[0][2], acc_det[0][3])[2]
+ from accounts.utils import get_balance_on
+ opening_bal = get_balance_on(account, add_days(from_date, -1))
+ closing_bal = get_balance_on(account, to_date)
if acc_det[0][0] == 'Credit':
- closing_bal = -1*closing_bal
- opening_bal = -1*opening_bal
+ closing_bal = -1*closing_bal
+ opening_bal = -1*opening_bal
# add opening row
t_row = ['' for i in range(len(colnames))]
@@ -99,38 +100,38 @@
#---------------------------------------------------------------------
count = 0
for r in res:
- count +=1
- det = r[1].split('~~~')
- if from_export == 1:
- a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
- else:
- a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
- r[1] = a
- out.append(r)
+ count +=1
+ det = r[1].split('~~~')
+ if from_export == 1:
+ a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
+ else:
+ a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
+ r[1] = a
+ out.append(r)
# Total, Difference and closing balance
#---------------------------------------------------------------------
if total_debit != 0 or total_credit != 0:
- # Total debit/credit
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total'
- t_row[col_idx['Debit']-1] = total_debit
- t_row[col_idx['Credit']-1] = total_credit
- out.append(t_row)
+ # Total debit/credit
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total'
+ t_row[col_idx['Debit']-1] = total_debit
+ t_row[col_idx['Credit']-1] = total_credit
+ out.append(t_row)
- # diffrence (dr-cr)
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Total(Dr-Cr)'
- t_row[col_idx['Debit']-1] = total_diff
- out.append(t_row)
+ # diffrence (dr-cr)
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Total(Dr-Cr)'
+ t_row[col_idx['Debit']-1] = total_diff
+ out.append(t_row)
- # closing
- if account:
- t_row = ['' for i in range(len(colnames))]
- t_row[1] = 'Closing Balance on ' + formatdate(to_date)
- t_row[col_idx['Debit']-1] = flt(closing_bal)
- out.append(t_row)
+ # closing
+ if account:
+ t_row = ['' for i in range(len(colnames))]
+ t_row[1] = 'Closing Balance on ' + formatdate(to_date)
+ t_row[col_idx['Debit']-1] = flt(closing_bal)
+ out.append(t_row)
# Print Format
@@ -141,8 +142,8 @@
<div><h3> %(acc)s </h3></div>
<div>Ledger Between %(fdt)s and %(tdt)s </div></td></tr></table><br>
- """ % {'acc':account,
- 'fdt':from_date,
- 'tdt':to_date}
+ """ % {'acc':account,
+ 'fdt':from_date,
+ 'tdt':to_date}
page_template = myheader+"<div>%(table)s</div>"
diff --git a/erpnext/accounts/search_criteria/trial_balance/__init__.py b/erpnext/accounts/search_criteria/trial_balance/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/accounts/search_criteria/trial_balance/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/accounts/search_criteria/trial_balance/trial_balance.js b/erpnext/accounts/search_criteria/trial_balance/trial_balance.js
deleted file mode 100644
index 9a26ea2..0000000
--- a/erpnext/accounts/search_criteria/trial_balance/trial_balance.js
+++ /dev/null
@@ -1,61 +0,0 @@
-// ERPNext - web based ERP (http://erpnext.com)
-// Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-//
-// This program is free software: you can redistribute it and/or modify
-// it under the terms of the GNU General Public License as published by
-// the Free Software Foundation, either version 3 of the License, or
-// (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-report.customize_filters = function() {
- this.hide_all_filters();
-
- this.add_filter({fieldname:'show_group_ledger', label:'Show Group/Ledger', fieldtype:'Select', options:'Only Groups'+NEWLINE+'Only Ledgers'+NEWLINE+'Both But Without Group Balance'+NEWLINE+'Both With Balance',ignore : 1, parent:'Account', 'report_default':'Both With Balance','in_first_page':1,single_select:1});
-
- this.add_filter({fieldname:'show_zero_balance', label:'Show Zero Balance', fieldtype:'Select', options:'Yes'+NEWLINE+'No',ignore : 1, parent:'Account', 'report_default':'Yes','in_first_page':1,single_select:1});
-
- this.add_filter({fieldname:'transaction_date', label:'Date', fieldtype:'Date', options:'',ignore : 1, parent:'Account', 'in_first_page':1});
-
- this.filter_fields_dict['Account'+FILTER_SEP +'Company'].df.filter_hide = 0;
- this.filter_fields_dict['Account'+FILTER_SEP +'From Date'].df.filter_hide = 0;
- this.filter_fields_dict['Account'+FILTER_SEP +'To Date'].df.filter_hide = 0;
-
- this.filter_fields_dict['Account'+FILTER_SEP +'From Date'].df['report_default'] = sys_defaults.year_start_date;
- this.filter_fields_dict['Account'+FILTER_SEP +'To Date'].df['report_default'] = dateutil.obj_to_str(new Date());
- this.filter_fields_dict['Account'+FILTER_SEP +'Company'].df['report_default'] = sys_defaults.company;
-
- this.filter_fields_dict['Account'+FILTER_SEP +'From Date'].df.in_first_page = 1;
- this.filter_fields_dict['Account'+FILTER_SEP +'To Date'].df.in_first_page = 1;
- this.filter_fields_dict['Account'+FILTER_SEP +'Company'].df.in_first_page = 1;
-
- this.dt.set_no_limit(1);
-}
-
-report.aftertableprint = function(t) {
- $yt(t,'*',1,{whiteSpace:'pre'});
-}
-
-$dh(this.mytabs.tabs['More Filters']);
-$dh(this.mytabs.tabs['Select Columns']);
-
-report.get_query = function() {
- var g_or_l = this.get_filter('Account', 'Show Group/Ledger').get_value();
- var comp = this.get_filter('Account', 'Company').get_value();
-
- if (g_or_l == 'Only Ledgers') {
- var q = "SELECT name FROM tabAccount WHERE group_or_ledger = 'Ledger' and company = '" + comp + "' and docstatus != 2 ORDER BY lft";
- } else if (g_or_l == 'Only Groups') {
- var q = "SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name FROM tabAccount AS node,tabAccount AS parent WHERE (node.lft BETWEEN parent.lft AND parent.rgt) and node.group_or_ledger = 'Group' and node.company = '" + comp + "' and node.docstatus != 2 GROUP BY node.name ORDER BY node.lft";
- } else {
- var q = "SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name FROM tabAccount AS node,tabAccount AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt and node.company = '" + comp + "' and node.docstatus != 2 GROUP BY node.name ORDER BY node.lft";
- }
-
- return q;
-}
diff --git a/erpnext/accounts/search_criteria/trial_balance/trial_balance.py b/erpnext/accounts/search_criteria/trial_balance/trial_balance.py
deleted file mode 100644
index 8d798fa..0000000
--- a/erpnext/accounts/search_criteria/trial_balance/trial_balance.py
+++ /dev/null
@@ -1,142 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-# Columns
-#----------
-from __future__ import unicode_literals
-cl = [['Account','Data', '200px'],['Debit/Credit', 'Data', '100px'], ['Group/Ledger', 'Data', '100px'], ['Is PL Account', 'Data', '100px'], ['Opening (Dr)','Data', '100px'], ['Opening (Cr)','Data', '100px'],['Debit', 'Data', '100px'],['Credit', 'Data', '100px'],['Closing (Dr)', 'Data', '100px'],['Closing (Cr)', 'Data', '100px']]
-for c in cl:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append('')
- col_idx[c[0]] = len(colnames)-1
-
-# transaction date
-# ------------------
-if not filter_values.get('transaction_date') or not filter_values.get('transaction_date1'):
- msgprint("Please enter From Date and To Date")
- raise Exception
-else:
- from_date = filter_values['transaction_date']
- to_date = filter_values['transaction_date1']
-
-#check for from date and to date within same year
-#------------------------------------------------
-if not sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day) and %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",(from_date, to_date)):
- msgprint("From Date and To Date must be within same year")
- raise Exception
-
-# get year of the from date and to date
-# --------------------------------------
-from_date_year = sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",add_days(from_date, -1))
-from_date_year = from_date_year and from_date_year[0][0] or ''
-
-to_date_year = sql("select name from `tabFiscal Year` where %s between year_start_date and date_sub(date_add(year_start_date,interval 1 year), interval 1 day)",to_date)
-to_date_year = to_date_year and to_date_year[0][0] or ''
-
-# if output is more than 500 lines then it will ask to export
-# ------------------------------------------------------------
-if len(res) > 1000 and from_export == 0:
- msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please click on 'Export' to open in a spreadsheet")
- raise Exception
-
-
-acc_dict = {}
-for t in sql("select name, debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where docstatus != 2 and company = %s", filter_values['company']):
- acc_dict[t[0]] = [t[1], t[2], t[3], t[4], t[5]]
-
-
-total_debit, total_credit, total_opening_dr, total_opening_cr, total_closing_dr, total_closing_cr = 0, 0, 0, 0, 0, 0
-glc = get_obj('GL Control')
-
-# Main logic
-# ----------
-for r in res:
- # Fetch account details
- acc = r[col_idx['Account']].strip()
- r.append(acc_dict[acc][0])
- r.append(acc_dict[acc][4])
- r.append(acc_dict[acc][1])
-
- #if shows group and ledger both but without group balance
- if filter_values.get('show_group_ledger') == 'Both But Without Group Balance' and acc_dict[acc][4] == 'Group':
- for i in range(4):
- r.append('')
- continue
-
- # Opening Balance
- #-----------------------------
- if from_date_year == to_date_year:
- debit_on_fromdate, credit_on_fromdate, opening = glc.get_as_on_balance(acc, from_date_year, add_days(from_date, -1), acc_dict[acc][0], acc_dict[acc][2], acc_dict[acc][3]) # opening = closing of prev_date
- elif acc_dict[acc][1] == 'No': # if there is no previous year in system and not pl account
- opening = sql("select opening from `tabAccount Balance` where account = %s and period = %s", (acc, to_date_year))
- debit_on_fromdate, credit_on_fromdate, opening = 0, 0, flt(opening[0][0])
- else: # if pl account and there is no previous year in system
- debit_on_fromdate, credit_on_fromdate, opening = 0,0,0
-
- # closing balance
- #--------------------------------
- debit_on_todate, credit_on_todate, closing = glc.get_as_on_balance(acc, to_date_year, to_date, acc_dict[acc][0], acc_dict[acc][2], acc_dict[acc][3])
-
- # transaction betn the period
- #----------------------------------------
-
- debit = flt(debit_on_todate) - flt(debit_on_fromdate)
- credit = flt(credit_on_todate) - flt(credit_on_fromdate)
-
- # Debit / Credit
- if acc_dict[acc][0] == 'Credit':
- opening, closing = -1*opening, -1*closing
-
- # Totals
- total_opening_dr += opening>0 and flt(opening) or 0
- total_opening_cr += opening<0 and -1*flt(opening) or 0
- total_debit += debit
- total_credit += credit
- total_closing_dr += closing>0 and flt(closing) or 0
- total_closing_cr += closing<0 and -1*flt(closing) or 0
-
- # Append in rows
- r.append(flt(opening>0 and opening or 0))
- r.append(flt(opening<0 and -opening or 0))
- r.append(flt(debit))
- r.append(flt(credit))
- r.append(flt(closing>0.01 and closing or 0))
- r.append(flt(closing<-0.01 and -closing or 0))
-
-
-out =[]
-for r in res:
- # Remove accounts if opening bal = debit = credit = closing bal = 0
- # ------------------------------------------------------------------
- if filter_values.get('show_zero_balance') != 'No':
- out.append(r)
- elif r[col_idx['Opening (Dr)']] or r[col_idx['Opening (Cr)']] or r[col_idx['Debit']] or r[col_idx['Credit']] or r[col_idx['Closing (Dr)']] or r[col_idx['Closing (Cr)']] or (r[col_idx['Group/Ledger']] == 'Group' and filter_values.get('show_group_ledger') == 'Both But Without Group Balance'):
- out.append(r)
-
-# Total Debit / Credit
-# --------------------------
-if filter_values.get('show_group_ledger') in ['Only Ledgers', 'Both But Without Group Balance']:
- t_row = ['' for i in range(len(colnames))]
- t_row[col_idx['Account']] = 'Total'
- t_row[col_idx['Opening (Dr)']] = '%.2f' % total_opening_dr
- t_row[col_idx['Opening (Cr)']] = '%.2f' % total_opening_cr
- t_row[col_idx['Debit']] = '%.2f' % total_debit
- t_row[col_idx['Credit']] = '%.2f' % total_credit
- t_row[col_idx['Closing (Dr)']] = '%.2f' % total_closing_dr
- t_row[col_idx['Closing (Cr)']] = '%.2f' % total_closing_cr
- out.append(t_row)
diff --git a/erpnext/accounts/search_criteria/trial_balance/trial_balance.txt b/erpnext/accounts/search_criteria/trial_balance/trial_balance.txt
deleted file mode 100644
index 175c8e6..0000000
--- a/erpnext/accounts/search_criteria/trial_balance/trial_balance.txt
+++ /dev/null
@@ -1,34 +0,0 @@
-# Search Criteria, trial_balance
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-04-03 12:49:53',
- 'docstatus': 0,
- 'modified': '2012-07-23 11:49:53',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all Search Criteria
- {
- 'columns': u'Account\x01ID',
- 'criteria_name': u'Trial Balance',
- 'dis_filters': u'transaction_date',
- 'doc_type': u'Account',
- 'doctype': 'Search Criteria',
- 'filters': u"{'Account\x01Group or Ledger':'Ledger','Account\x01Is PL Account':'','Account\x01Account Type':'','Account\x01Show Group Balance':''}",
- 'module': u'Accounts',
- 'name': '__common__',
- 'page_len': 50,
- 'sort_by': u'`tabAccount`.`name`',
- 'sort_order': u'DESC',
- 'standard': u'Yes'
- },
-
- # Search Criteria, trial_balance
- {
- 'doctype': 'Search Criteria',
- 'name': u'trial_balance'
- }
-]
\ No newline at end of file
diff --git a/erpnext/accounts/utils/__init__.py b/erpnext/accounts/utils/__init__.py
index 05d2391f..60f70fe 100644
--- a/erpnext/accounts/utils/__init__.py
+++ b/erpnext/accounts/utils/__init__.py
@@ -15,30 +15,63 @@
# along with this program. If not, see <http://www.gnu.org/licenses/>.
from __future__ import unicode_literals
-from webnotes.model.doc import make_autoname, Document, addchild
-# Posts JV
-def post_jv(data):
- jv = Document('Journal Voucher')
- jv.voucher_type = data.get('voucher_type')
- jv.naming_series = data.get('naming_series')
- jv.voucher_date = data.get('cheque_date')
- jv.posting_date = data.get('cheque_date')
- jv.cheque_no = data.get('cheque_number')
- jv.cheque_date = data.get('cheque_date')
- jv.fiscal_year = data.get('fiscal_year') # To be modified to take care
- jv.company = data.get('company')
+import webnotes
+from webnotes.utils import nowdate
- jv.save(1)
+def get_fiscal_year(date):
+ from webnotes.utils import formatdate
+ fy = webnotes.conn.sql("""select name, year_start_date,
+ adddate(year_start_date, interval 1 year)
+ from `tabFiscal Year`
+ where %s between year_start_date and adddate(year_start_date,
+ interval 1 year)""", date)
+
+ if not fy:
+ webnotes.msgprint("""%s not in any Fiscal Year""" % formatdate(date), raise_exception=1)
+
+ return fy[0]
- jc = addchild(jv,'entries','Journal Voucher Detail',0)
- jc.account = data.get('debit_account')
- jc.debit = data.get('amount')
- jc.save()
+@webnotes.whitelist()
+def get_balance_on(account=None, date=None):
+ if not account and webnotes.form_dict.get("account"):
+ account = webnotes.form_dict.get("account")
+ date = webnotes.form_dict.get("date")
+
+ cond = []
+ if date:
+ cond.append("posting_date <= '%s'" % date)
+
+ acc = webnotes.conn.get_value('Account', account, \
+ ['lft', 'rgt', 'debit_or_credit', 'is_pl_account', 'group_or_ledger'], as_dict=1)
+
+ # for pl accounts, get balance within a fiscal year
+ if acc.is_pl_account == 'Yes':
+ cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" % \
+ get_fiscal_year(date or nowdate())[1])
+
+ # different filter for group and ledger - improved performance
+ if acc.group_or_ledger=="Group":
+ cond.append("""exists (
+ select * from `tabAccount` ac where ac.name = gle.account
+ and ac.lft >= %s and ac.rgt <= %s
+ )""" % (acc.lft, acc.rgt))
+ 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]
- jc = addchild(jv,'entries','Journal Voucher Detail',0)
- jc.account = data.get('credit_account')
- jc.credit = data.get('amount')
- jc.save()
+ # if credit account, it should calculate credit - debit
+ if bal and acc.debit_or_credit == 'Credit':
+ bal = -bal
- return jv.name
\ No newline at end of file
+ # if bal is None, return 0
+ return bal or 0
\ No newline at end of file
diff --git a/erpnext/home/page/dashboard/dashboard.py b/erpnext/home/page/dashboard/dashboard.py
index 71ce805..179f717 100644
--- a/erpnext/home/page/dashboard/dashboard.py
+++ b/erpnext/home/page/dashboard/dashboard.py
@@ -152,14 +152,9 @@
print acc
raise e
- fiscal_year = self.get_fiscal_year(start)
- if fiscal_year:
- return self.glc.get_as_on_balance(acc, fiscal_year, start, debit_or_credit, lft, rgt)
- else:
- webnotes.msgprint('Please select the START DATE and END DATE such that\
- they fall within <b>fiscal year(s)</b> as defined in\
- Setup > System > Fiscal Year.', raise_exception=1)
-
+ from accounts.utils import get_fiscal_year, get_balance_on
+ fy_end_date = get_fiscal_year(start)[2]
+ return get_balance_on(acc, fy_end_date)
def get_fiscal_year(self, dt):
"""
@@ -237,10 +232,10 @@
return self.get_account_amt(opts['account'], start, end, debit_or_credit)
elif opts['type']=='receivables':
- return self.get_account_balance(self.receivables_group, end)[2]
+ return self.get_account_balance(self.receivables_group, end)
elif opts['type']=='payables':
- return self.get_account_balance(self.payables_group, end)[2]
+ return self.get_account_balance(self.payables_group, end)
elif opts['type']=='collection':
return self.get_bank_amt('credit', 'Customer', start, end)
diff --git a/erpnext/patches/before_jan_2012/index_patch.py b/erpnext/patches/before_jan_2012/index_patch.py
index 7cdc12e..2d82288 100644
--- a/erpnext/patches/before_jan_2012/index_patch.py
+++ b/erpnext/patches/before_jan_2012/index_patch.py
@@ -84,7 +84,6 @@
'Campaign': [],
'SMS Parameter': [],
'Leave Type': [],
- 'Account Balance': ['period', 'start_date', 'end_date', 'account'],
'Absent Days Detail': [],
'Tag': [],
'Raw Materials Supplied': ['raw_material'],
@@ -102,7 +101,6 @@
'Appraisal Template': [],
'Budget Distribution': ['fiscal_year'],
'Workstation': ['warehouse'],
- 'Period': [],
'Training Session Details': [],
'Sales Taxes and Charges Master': [],
'State': [],
diff --git a/erpnext/patches/before_jan_2012/repost_account_bal.py b/erpnext/patches/before_jan_2012/repost_account_bal.py
deleted file mode 100644
index a87bfe0..0000000
--- a/erpnext/patches/before_jan_2012/repost_account_bal.py
+++ /dev/null
@@ -1,39 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-
-from __future__ import unicode_literals
-def execute():
- import webnotes
- sql = webnotes.conn.sql
- from webnotes.model.code import get_obj
-
- # repost
- comp = sql("select name from tabCompany where docstatus!=2")
- fy = sql("select name from `tabFiscal Year` order by year_start_date asc")
- for c in comp:
- prev_fy = ''
- for f in fy:
- fy_obj = get_obj('Fiscal Year', f[0])
- fy_obj.doc.past_year = prev_fy
- fy_obj.doc.company = c[0]
- fy_obj.doc.save()
-
- fy_obj = get_obj('Fiscal Year', f[0])
- fy_obj.repost()
- prev_fy = f[0]
- sql("commit")
- sql("start transaction")
diff --git a/erpnext/patches/july_2012/repost_stock_due_to_wrong_packing_list.py b/erpnext/patches/july_2012/repost_stock_due_to_wrong_packing_list.py
index 40e081c..449dd1f 100644
--- a/erpnext/patches/july_2012/repost_stock_due_to_wrong_packing_list.py
+++ b/erpnext/patches/july_2012/repost_stock_due_to_wrong_packing_list.py
@@ -5,8 +5,8 @@
# add index
webnotes.conn.commit()
try:
- webnotes.conn.sql("""create index item_code_warehouse
- on `tabDelivery Note Packing Item` (item_code, warehouse)""")
+ webnotes.conn.sql("""alter table `tabDelivery Note Packing Item`
+ add index item_code_warehouse (item_code, warehouse)""")
except:
pass
webnotes.conn.begin()
diff --git a/erpnext/patches/july_2012/sync_trial_balance.py b/erpnext/patches/july_2012/sync_trial_balance.py
deleted file mode 100644
index 3755ed4..0000000
--- a/erpnext/patches/july_2012/sync_trial_balance.py
+++ /dev/null
@@ -1,5 +0,0 @@
-from __future__ import unicode_literals
-def execute():
- import webnotes
- from webnotes.modules import reload_doc
- reload_doc('accounts', 'search_criteria', 'trial_balance')
\ No newline at end of file
diff --git a/erpnext/patches/patch_list.py b/erpnext/patches/patch_list.py
index 92e48a0..1a00e99 100644
--- a/erpnext/patches/patch_list.py
+++ b/erpnext/patches/patch_list.py
@@ -513,11 +513,6 @@
},
{
'patch_module': 'patches.july_2012',
- 'patch_file': 'sync_trial_balance',
- 'description': "sync trial balance"
- },
- {
- 'patch_module': 'patches.july_2012',
'patch_file': 'blog_guest_permission',
},
{
@@ -604,4 +599,8 @@
'patch_module': 'patches.september_2012',
'patch_file': 'rebuild_trees',
},
+ {
+ 'patch_module': 'patches.september_2012',
+ 'patch_file': 'deprecate_account_balance',
+ },
]
diff --git a/erpnext/patches/september_2012/deprecate_account_balance.py b/erpnext/patches/september_2012/deprecate_account_balance.py
new file mode 100644
index 0000000..0ca2c33
--- /dev/null
+++ b/erpnext/patches/september_2012/deprecate_account_balance.py
@@ -0,0 +1,12 @@
+import webnotes
+from webnotes.model import delete_doc
+
+def execute():
+ # remove doctypes
+ for dt in ["Period", "Account Balance", "Multi Ledger Report",
+ "Multi Ledger Report Detail", "Period Control", "Reposting Tool",
+ "Lease Agreement", "Lease Installment"]:
+ delete_doc("DocType", dt)
+
+ # remove search criteria
+ delete_doc("Search Criteria", "Trial Balance")
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index 4386f2e..0e2335a 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -31,9 +31,6 @@
in_transaction = webnotes.conn.in_transaction
convert_to_lists = webnotes.conn.convert_to_lists
-# -----------------------------------------------------------------------------------------
-
-
class DocType:
def __init__(self,d,dl):
self.doc, self.doclist = d,dl
@@ -235,9 +232,6 @@
# delete gl entry
sql("delete from `tabGL Entry` where company = %s", self.doc.name)
- #delete tabAccount Balance
- sql("delete ab.* from `tabAccount Balance` ab, `tabAccount` a where ab.account = a.name and a.company = %s", self.doc.name)
-
#delete tabAccount
sql("delete from `tabAccount` where company = %s order by lft desc, rgt desc", self.doc.name)
diff --git a/erpnext/setup/doctype/global_defaults/global_defaults.py b/erpnext/setup/doctype/global_defaults/global_defaults.py
index b4b2381..13a42ed 100644
--- a/erpnext/setup/doctype/global_defaults/global_defaults.py
+++ b/erpnext/setup/doctype/global_defaults/global_defaults.py
@@ -44,13 +44,6 @@
def __init__(self, d, dl):
self.doc, self.doclist = d, dl
- def get_bal(self,arg):
- """get account balance (??)"""
- from webnotes.utils import fmt_money, flt
- bal = webnotes.conn.sql("select `tabAccount Balance`.balance,`tabAccount`.debit_or_credit from `tabAccount`,`tabAccount Balance` where `tabAccount Balance`.account=%s and `tabAccount Balance`.period=%s and `tabAccount Balance`.account=`tabAccount`.name ",(arg,self.doc.current_fiscal_year))
- if bal:
- return fmt_money(flt(bal[0][0])) + ' ' + bal[0][1]
-
def on_update(self):
"""update defaults"""
diff --git a/erpnext/setup/doctype/period/__init__.py b/erpnext/setup/doctype/period/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/setup/doctype/period/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/setup/doctype/period/period.js b/erpnext/setup/doctype/period/period.js
deleted file mode 100644
index 3cad944..0000000
--- a/erpnext/setup/doctype/period/period.js
+++ /dev/null
@@ -1,26 +0,0 @@
-// ERPNext - web based ERP (http://erpnext.com)
-// Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-//
-// This program is free software: you can redistribute it and/or modify
-// it under the terms of the GNU General Public License as published by
-// the Free Software Foundation, either version 3 of the License, or
-// (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-
-
-//--------- ONLOAD -------------
-cur_frm.cscript.onload = function(doc, cdt, cdn) {
-
-}
-
-cur_frm.cscript.refresh = function(doc, cdt, cdn) {
-
-}
\ No newline at end of file
diff --git a/erpnext/setup/doctype/period/period.txt b/erpnext/setup/doctype/period/period.txt
deleted file mode 100644
index 77ee019..0000000
--- a/erpnext/setup/doctype/period/period.txt
+++ /dev/null
@@ -1,115 +0,0 @@
-# DocType, Period
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:36:22',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:36:22',
- 'modified_by': u'Administrator',
- 'owner': u'nabin@webnotestech.com'
- },
-
- # These values are common for all DocType
- {
- 'autoname': u'field:period_name',
- 'colour': u'White:FFF',
- 'doctype': 'DocType',
- 'document_type': u'Master',
- 'in_create': 1,
- 'module': u'Setup',
- 'name': '__common__',
- 'section_style': u'Simple',
- 'server_code_error': u' ',
- 'version': 5
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Period',
- 'parentfield': u'fields',
- 'parenttype': u'DocType',
- 'permlevel': 0
- },
-
- # These values are common for all DocPerm
- {
- 'doctype': u'DocPerm',
- 'name': '__common__',
- 'parent': u'Period',
- 'parentfield': u'permissions',
- 'parenttype': u'DocType',
- 'permlevel': 0,
- 'read': 1,
- 'role': u'System Manager',
- 'write': 0
- },
-
- # DocType, Period
- {
- 'doctype': 'DocType',
- 'name': u'Period'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'period_name',
- 'fieldtype': u'Data',
- 'label': u'Period Name',
- 'oldfieldname': u'period_name',
- 'oldfieldtype': u'Data'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'start_date',
- 'fieldtype': u'Date',
- 'label': u'Start Date',
- 'oldfieldname': u'start_date',
- 'oldfieldtype': u'Date'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'end_date',
- 'fieldtype': u'Date',
- 'label': u'End Date',
- 'oldfieldname': u'end_date',
- 'oldfieldtype': u'Date'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'period_type',
- 'fieldtype': u'Data',
- 'label': u'Period Type',
- 'oldfieldname': u'period_type',
- 'oldfieldtype': u'Data'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'fiscal_year',
- 'fieldtype': u'Data',
- 'label': u'Fiscal Year',
- 'oldfieldname': u'fiscal_year',
- 'oldfieldtype': u'Data'
- }
-]
\ No newline at end of file
diff --git a/erpnext/setup/doctype/period_control/__init__.py b/erpnext/setup/doctype/period_control/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/setup/doctype/period_control/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/setup/doctype/period_control/period_control.py b/erpnext/setup/doctype/period_control/period_control.py
deleted file mode 100644
index 727664e..0000000
--- a/erpnext/setup/doctype/period_control/period_control.py
+++ /dev/null
@@ -1,78 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-# Please edit this list and import only required elements
-from __future__ import unicode_literals
-import webnotes
-
-from webnotes.utils import add_days, add_months, add_years, cint, cstr, date_diff, default_fields, flt, fmt_money, formatdate, generate_hash, getTraceback, get_defaults, get_first_day, get_last_day, getdate, has_common, month_name, now, nowdate, replace_newlines, sendmail, set_default, str_esc_quote, user_format, validate_email_add
-from webnotes.model import db_exists
-from webnotes.model.doc import Document, addchild, getchildren, make_autoname
-from webnotes.model.doclist import getlist, copy_doclist
-from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
-from webnotes import session, form, is_testing, msgprint, errprint
-
-set = webnotes.conn.set
-sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
-
-# -----------------------------------------------------------------------------------------
-
-
-class DocType:
- def __init__(self,d,dl):
- self.doc, self.doclist = d, dl
-
- # Generate Periods
- #------------------
- def generate_periods(self, fy):
- ml = ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
-
- import webnotes.utils
- from dateutil.relativedelta import relativedelta
-
-
- if not sql("select name from `tabPeriod` where fiscal_year = '%s'" % fy):
- ysd = sql("select year_start_date from `tabFiscal Year` where name = '%s'"%fy)[0][0]
-
- #create period as fiscal year record name
- #----------------------------------------------
- arg = {'pn':fy,'sd':ysd,'ed':webnotes.utils.get_last_day(ysd + relativedelta(months=11)).strftime('%Y-%m-%d'),'pt':'Year','fy':fy}
- self.create_period(arg)
-
- for i in range(12):
- msd = ysd + relativedelta(months=i)
-
- arg = {'pn':ml[cint(msd.strftime('%m'))-1] + ' ' + msd.strftime('%Y'),'sd':msd.strftime('%Y-%m-%d'),'ed':webnotes.utils.get_last_day(msd).strftime('%Y-%m-%d'),'pt':'Month','fy':fy}
- self.create_period(arg)
-
- #---------------------------------------------------------
- #create period common function
- def create_period(self,arg):
- p = Document('Period')
- p.period_name = arg['pn']
- p.start_date = arg['sd']
- p.end_date = arg['ed']
- p.period_type = arg['pt']
- p.fiscal_year = arg['fy']
-
- try:
- p.save(1)
- except NameError, e:
- msgprint('Period %s already exists' % p.period_name)
- raise Exception
\ No newline at end of file
diff --git a/erpnext/setup/doctype/period_control/period_control.txt b/erpnext/setup/doctype/period_control/period_control.txt
deleted file mode 100644
index 420b319..0000000
--- a/erpnext/setup/doctype/period_control/period_control.txt
+++ /dev/null
@@ -1,32 +0,0 @@
-# DocType, Period Control
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:36:22',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:36:22',
- 'modified_by': u'Administrator',
- 'owner': u'nabin@webnotestech.com'
- },
-
- # These values are common for all DocType
- {
- 'colour': u'White:FFF',
- 'doctype': 'DocType',
- 'in_create': 1,
- 'issingle': 1,
- 'module': u'Setup',
- 'name': '__common__',
- 'read_only': 1,
- 'section_style': u'Simple',
- 'server_code_error': u' ',
- 'version': 36
- },
-
- # DocType, Period Control
- {
- 'doctype': 'DocType',
- 'name': u'Period Control'
- }
-]
\ No newline at end of file
diff --git a/erpnext/startup/event_handlers.py b/erpnext/startup/event_handlers.py
index ffbf113..9b9c030 100644
--- a/erpnext/startup/event_handlers.py
+++ b/erpnext/startup/event_handlers.py
@@ -118,22 +118,17 @@
# if expired, stop user from logging in
from webnotes.utils import formatdate
+ msg = """Oops! Your subscription expired on <b>%s</b>.
+ <br>Nothing catastrophic.<br>""" % formatdate(conf.expires_on)
+
if 'System Manager' in webnotes.user.roles:
- webnotes.response['server_messages'] = """Oops! \
- Your subscription expired on <b>%s</b>.
-
- Nothing catastrophic.
-
- Just drop in a mail at <b>support@erpnext.com</b> and \
- we will guide you to get your account re-activated.""" % formatdate(conf.expires_on)
+ msg += """Just drop in a mail at <b>support@erpnext.com</b> and
+ we will guide you to get your account re-activated."""
else:
- webnotes.response['server_messages'] = """Oops! \
- Your subscription expired on <b>%s</b>.
-
- Nothing catastrophic.
-
- Just ask your System Manager to drop in a mail at <b>support@erpnext.com</b> and \
- we will guide him to get your account re-activated.""" % formatdate(conf.expires_on)
+ msg += """Just ask your System Manager to drop in a mail at <b>support@erpnext.com</b> and
+ we will guide him to get your account re-activated."""
+
+ webnotes.msgprint(msg)
webnotes.response['message'] = 'Account Expired'
raise webnotes.AuthenticationError
diff --git a/erpnext/stock/doctype/sales_and_purchase_return_tool/sales_and_purchase_return_tool.js b/erpnext/stock/doctype/sales_and_purchase_return_tool/sales_and_purchase_return_tool.js
index 30242d4..d57ff5d 100644
--- a/erpnext/stock/doctype/sales_and_purchase_return_tool/sales_and_purchase_return_tool.js
+++ b/erpnext/stock/doctype/sales_and_purchase_return_tool/sales_and_purchase_return_tool.js
@@ -191,7 +191,7 @@
var doclist = make_doclist(doc.doctype, doc.name);
$c('accounts.get_new_jv_details', {
doclist: JSON.stringify(doclist),
- fiscal_year: sys_defaults.fiscal_year
+ fiscal_year: sys_defaults.fiscal_year,
}, function(r, rt) {
if(!r.exc) {
cur_frm.cscript.make_jv(doc, 'Credit Note', r.message);
@@ -210,7 +210,7 @@
jv.company = sys_defaults.company;
jv.fiscal_year = sys_defaults.fiscal_year;
jv.is_opening = 'No';
- jv.posting_date = dateutil.obj_to_str(new Date());
+ jv.posting_date = doc.return_date;
jv.voucher_date = dateutil.obj_to_str(new Date());
// Add children
@@ -218,6 +218,7 @@
for(var i=0; i<children.length; i++) {
var ch = LocalDB.add_child(jv, 'Journal Voucher Detail', 'entries');
$.extend(ch, children[i]);
+ ch.balance = fmt_money(ch.balance);
}
}
diff --git a/erpnext/utilities/cleanup_data.py b/erpnext/utilities/cleanup_data.py
index d826613..95e1b84 100644
--- a/erpnext/utilities/cleanup_data.py
+++ b/erpnext/utilities/cleanup_data.py
@@ -26,7 +26,7 @@
'Purchase Invoice','Maintenance Visit','Maintenance Schedule','Leave Application', \
'Leave Allocation', 'Lead', 'Journal Voucher', 'Installation Note','Purchase Request', \
'GL Entry','Expense Claim','Opportunity','Delivery Note','Customer Issue','Bin', \
- 'Authorization Rule','Attendance','Account Balance', 'C-Form', 'Form 16A', 'Lease Agreement', \
+ 'Authorization Rule','Attendance', 'C-Form', 'Form 16A', 'Lease Agreement', \
'Lease Installment', 'TDS Payment', 'TDS Return Acknowledgement', 'Appraisal', \
'Installation Note', 'Communication'
]
@@ -62,7 +62,6 @@
'Project':'',
'Print Heading':'',
'Price List':['Default Price List'],
- 'Period':'',
'Sales Taxes and Charges Master':'',
'Letter Head':'',
'Leave Type':['Leave Without Pay', 'Privilege Leave', 'Casual Leave', 'PL', 'CL', 'LWP',
diff --git a/erpnext/utilities/doctype/reposting_tool/__init__.py b/erpnext/utilities/doctype/reposting_tool/__init__.py
deleted file mode 100644
index baffc48..0000000
--- a/erpnext/utilities/doctype/reposting_tool/__init__.py
+++ /dev/null
@@ -1 +0,0 @@
-from __future__ import unicode_literals
diff --git a/erpnext/utilities/doctype/reposting_tool/reposting_tool.js b/erpnext/utilities/doctype/reposting_tool/reposting_tool.js
deleted file mode 100644
index d44a2cd..0000000
--- a/erpnext/utilities/doctype/reposting_tool/reposting_tool.js
+++ /dev/null
@@ -1,79 +0,0 @@
-// ERPNext - web based ERP (http://erpnext.com)
-// Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-//
-// This program is free software: you can redistribute it and/or modify
-// it under the terms of the GNU General Public License as published by
-// the Free Software Foundation, either version 3 of the License, or
-// (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-var bin_list = [];
-var msg = [];
-var binidx = 0;
-
-cur_frm.cscript.repost_bin = function(doc,cdt,cdn) {
- args = {'check': 'Bin'};
- $c_obj('Reposting Tool','get_count_for_reposting', docstring(args), function(r,rt) {
- bin_list = r.message;
- repair_bin();
- });
-}
-
-function repair_single_bin(){
- $c_obj('Reposting Tool', 'repair_bin', cstr(bin_list[binidx]), function(r,rt) {
- for(i = 0; i < r.message.length ; i++){
- msg.push(r.message[i]);
- }
- repair_bin();
- });
-}
-
-function repair_bin(){
- if(binidx >= 10) {
- args = {'msg': msg, 'subject': 'Item Quantity'};
- $c_obj('Reposting Tool', 'send_mail', docstring(args));
- alert('Completed');
- return;
- }
- repair_single_bin();
- binidx ++;
-}
-
-// Batch for Account Balances
-//======================================================
-var acc_list = [];
-var accidx = 0;
-cur_frm.cscript.repost_account_balances = function(doc,cdt,cdn) {
- args = {'check': 'Account Balance'};
- $c_obj('Reposting Tool','get_count_for_reposting', docstring(args), function(r,rt) {
- acc_list = r.message;
- repair_acc_bal();
- });
-}
-
-function repair_single_acc_bal(){
- $c_obj('Reposting Tool', 'repair_acc_bal', cstr(acc_list[accidx]), function(r,rt) {
- for(i = 0; i < r.message.length; i++){
- msg.push(r.message[i]);
- }
- repair_acc_bal();
- });
-}
-
-function repair_acc_bal(){
- if(accidx >= 15) {
- args = {'msg' : msg, 'subject': 'Account Balance'};
- $c_obj('Reposting Tool', 'send_mail', docstring(args));
- alert('Completed');
- return;
- }
- repair_single_acc_bal();
- accidx ++;
-}
\ No newline at end of file
diff --git a/erpnext/utilities/doctype/reposting_tool/reposting_tool.py b/erpnext/utilities/doctype/reposting_tool/reposting_tool.py
deleted file mode 100644
index feb1ea0..0000000
--- a/erpnext/utilities/doctype/reposting_tool/reposting_tool.py
+++ /dev/null
@@ -1,211 +0,0 @@
-# ERPNext - web based ERP (http://erpnext.com)
-# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-
-# Please edit this list and import only required elements
-from __future__ import unicode_literals
-import webnotes
-
-from webnotes.utils import add_days, add_months, add_years, cint, cstr, date_diff, default_fields, flt, fmt_money, formatdate, generate_hash, getTraceback, get_defaults, get_first_day, get_last_day, getdate, has_common, month_name, now, nowdate, replace_newlines, sendmail, set_default, str_esc_quote, user_format, validate_email_add
-from webnotes.model import db_exists
-from webnotes.model.doc import Document, addchild, getchildren, make_autoname
-from webnotes.model.doclist import getlist, copy_doclist
-from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
-from webnotes import session, form, is_testing, msgprint, errprint
-
-set = webnotes.conn.set
-sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
-
-# -----------------------------------------------------------------------------------------
-
-
-class DocType:
- def __init__(self, doc, doclist=[]):
- self.doc = doc
- self.doclist = doclist
- self.msg = []
-
-
- # =============================================================================
- def get_count_for_reposting(self, args):
- args = eval(args)
- if args['check'] == 'Bin':
- return [d[0] for d in sql("select name from `tabBin` where item_code = 'Repost Item' " )]
-
- if args['check'] == 'Account Balance':
- # message
- if not self.doc.past_year:
- msgprint('<div style="color: ORANGE"> Warning: Opening balances were not imported </div>')
-
- # do not repost from same year
- if self.doc.past_year == self.doc.name:
- msgprint("Cannot import from the current year")
-
- return [d[0] for d in sql("select name from `tabAccount` ")]
-
- # =============================================================================
- def get_bin_qty(self, wh, item):
- # get actual_qty
- act_qty = sql("select sum(actual_qty) from `tabStock Ledger Entry` where warehouse = '%s' and item_code = '%s' and ifnull(is_cancelled, 'No') = 'No'" % (wh, item))
- act_qty = act_qty and flt(act_qty[0][0]) or 0
-
- # get indented_qty
- ind_qty = sql("select sum(if( ifnull(t2.qty, 0) > ifnull(t2.ordered_qty, 0), ifnull(t2.qty, 0) - ifnull(t2.ordered_qty, 0), 0) ) from `tabPurchase Request` t1, `tabPurchase Request Item`t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.warehouse = '%s' and t2.item_code = '%s' and status != 'Stopped'" % (wh, item))
- ind_qty = ind_qty and flt(ind_qty[0][0]) or 0
-
- # get ordered_qty
- ord_qty = sql("select sum(if ( ifnull(t2.qty, 0) > ifnull(t2.received_qty, 0), (ifnull(t2.qty, 0) - ifnull(t2.received_qty, 0)) * ifnull(t2.conversion_factor, 0) , 0) ) from `tabPurchase Order` t1, `tabPurchase Order Item` t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.warehouse = '%s' and t2.item_code = '%s' and status != 'Stopped'" % (wh, item))
- ord_qty = ord_qty and flt(ord_qty[0][0]) or 0
-
-
- # get reserved_qty
- res_qty =sql("select sum(if ( ifnull(t2.qty, 0) > ifnull(t2.delivered_qty, 0), ifnull(t2.qty, 0) - ifnull(t2.delivered_qty, 0) , 0) ) from `tabSales Order` t1, `tabSales Order Item` t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.reserved_warehouse = '%s' and t2.item_code = '%s' and status != 'Stopped'" % (wh, item))
- res_qty = res_qty and flt(res_qty[0][0]) or 0
-
- # get planned_qty
- plan_qty = sql("select sum(if ( ifnull(qty, 0) > ifnull(produced_qty,0), ifnull(qty, 0) - ifnull(produced_qty, 0), 0) ) from `tabProduction Order` where fg_warehouse = '%s' and production_item = '%s' and docstatus = 1" % (wh, item))
- plan_qty = plan_qty and flt(plan_qty[0][0]) or 0
-
- return {'actual_qty': act_qty, 'indented_qty': ind_qty, 'ordered_qty': ord_qty, 'reserved_qty': res_qty, 'planned_qty': plan_qty }
-
- # =============================================================================
- def check_bin_qty(self, bin_obj, qty_dict):
- label_dict = {'actual_qty': 'Actual Qty', 'indented_qty': 'Quantity Requested for Purchase', 'ordered_qty': 'Ordered Qty', 'reserved_qty': 'Reserved Qty', 'planned_qty': 'Planned Qty'}
- for f in qty_dict:
- if flt(bin_obj.doc.fields[f]) != qty_dict[f]:
- msgprint('<div style="color: RED"> Difference found in %s for Item:= %s and Warehouse:= %s (Before : %s; After : %s)</div>' % (label_dict[f], bin_obj.doc.item_code, bin_obj.doc.warehouse, cstr(bin_obj.doc.fields[f]), cstr(qty_dict[f])))
- self.msg.append('<div style="color: RED"> Difference found in %s for Item:= %s and Warehouse:= %s (Before : %s; After : %s)</div>' % (label_dict[f], bin_obj.doc.item_code, bin_obj.doc.warehouse, cstr(bin_obj.doc.fields[f]), cstr(qty_dict[f])))
-
- # Check projected qty
- projected_qty = flt(qty_dict['actual_qty']) + flt(qty_dict['indented_qty']) + flt(qty_dict['ordered_qty']) + flt(qty_dict['planned_qty']) - flt(qty_dict['reserved_qty'])
- if flt(projected_qty) != flt(bin_obj.doc.projected_qty):
- msgprint('<div style="color: RED">Difference found in Projected Qty for Item:= %s and Warehouse:= %s (Before : %s; After : %s)</div>' % (bin_obj.doc.item_code, bin_obj.doc.warehouse, bin_obj.doc.projected_qty, cstr(projected_qty)))
- self.msg.append('<div style="color: RED">Difference found in Projected Qty for Item:= %s and Warehouse:= %s (Before : %s; After : %s)</div>' % (bin_obj.doc.item_code, bin_obj.doc.warehouse, bin_obj.doc.projected_qty, cstr(projected_qty)))
-
-
- # =============================================================================
- def repair_bin(self, bin):
- import webnotes
- bin_obj = get_obj('Bin',bin)
- bin_act_qty = flt(bin_obj.doc.actual_qty)
- try:
- # udpate actual qty and item valuation
- bin_obj.update_entries_after('0000-00-00', '00:00')
- # get bin qty
- qty_dict = self.get_bin_qty(bin_obj.doc.warehouse, bin_obj.doc.item_code)
-
- # check bin qty
- self.check_bin_qty(bin_obj, qty_dict)
-
- projected_qty = flt(qty_dict['indented_qty']) + flt(qty_dict['ordered_qty']) - flt(qty_dict['reserved_qty']) + flt(qty_dict['planned_qty']) + flt(qty_dict['actual_qty'])
- # update indented_qty, ordered_qty, reserved_qty, planned_qty
- sql("update `tabBin` set indented_qty = '%s', ordered_qty = '%s', reserved_qty = '%s', planned_qty = '%s', projected_qty = '%s' where warehouse = '%s' and item_code = '%s'" % ( flt(qty_dict['indented_qty']), flt(qty_dict['ordered_qty']), flt(qty_dict['reserved_qty']), flt(qty_dict['planned_qty']), projected_qty, bin_obj.doc.warehouse, bin_obj.doc.item_code))
-
- # update projected_qty
- sql("update `tabBin` set projected_qty = ifnull(indented_qty, 0) + ifnull(ordered_qty,0) + ifnull(actual_qty, 0) + ifnull(planned_qty, 0) - ifnull(reserved_qty,0) where warehouse = '%s' and item_code = '%s' " % (bin_obj.doc.warehouse, bin_obj.doc.item_code))
- if not self.msg:
- msgprint('<div style="color: GREEN"> Reposting of Stock for Item %s and Warehouse %s completed Successfully. </div>' % (bin_obj.doc.item_code, bin_obj.doc.warehouse))
- except Exception:
- msgprint('<div style="color: RED"> Handle Item %s and Warehouse %s seprately. </div> <div style="color: RED"> ERROR: %s</div>' % (bin_obj.doc.item_code, bin_obj.doc.warehouse, str(webnotes.utils.getTraceback())))
- self.msg.append('<div style="color: RED"> ERROR: %s</div>' % (str(webnotes.utils.getTraceback())))
-
- # =============================================================================
- def repair_all_bins(self):
- bins = sql("select name from tabBin")
- cnt = 0
- for bin in bins:
- if cnt % 20 == 0:
- sql("commit")
- sql("start transaction")
- cnt += 1
-
- self.repair_bin(bin[0])
-
- # =============================================================================
- def repair_bins_for_illegal_cancelled(self, after_date = '2011-01-01'):
- bins = sql("select name from tabBin where modified >= %s", after_date)
- cnt = 0
- for bin in bins:
- if cnt % 20 == 0:
- sql("commit")
- sql("start transaction")
- cnt += 1
-
- self.repair_bin(bin[0])
- # =============================================================================
- def repair_opening_bal(self, d, acc_obj, past_yr, fiscal_yr):
- # check opening balance
- opbal = sql("select balance from `tabAccount Balance` where account=%s and period = %s", (acc_obj.doc.name, past_yr))
- if flt(d.opening) != flt(opbal and flt(opbal[0][0]) or 0):
- msgprint('<div style="color: RED"> Difference found in Opening of Account %s for Period %s in Fiscal Year %s (Before : %s; After : %s) </div>' % (acc_obj.doc.name, d.period, fiscal_yr, flt(d.opening), opbal and flt(opbal[0][0]) or 0))
- self.msg.append('<div style="color: RED"> Difference found in Opening of Account %s for Period %s in Fiscal Year %s (Before : %s; After : %s) </div>' % (acc_obj.doc.name, d.period, fiscal_yr, flt(d.opening), opbal and flt(opbal[0][0]) or 0))
- sql("update `tabAccount Balance` set opening = '%s' where period = '%s' and account = '%s' " % (opbal and flt(opbal[0][0]) or 0, fiscal_yr, acc_obj.doc.name))
-
-
- # =============================================================================
- def repair_bal(self, d, acc_obj, fiscal_yr):
- # check balances
- ysd = get_value('Fiscal Year', fiscal_yr, 'year_start_date')
- bal = get_obj('GL Control').get_as_on_balance(acc_obj.doc.name, fiscal_yr, d.end_date, acc_obj.doc.debit_or_credit, acc_obj.doc.is_pl_account, acc_obj.doc.lft, acc_obj.doc.rgt, ysd)
- if flt(d.balance) != flt(bal):
- msgprint('<div style="color: RED"> Difference found in Balance of Account %s for Period %s in Fiscal Year %s (Before : %s; After : %s) </div>' % (acc_obj.doc.name, d.period, fiscal_yr, flt(d.balance), flt(bal)))
- self.msg.append('<div style="color: RED"> Difference found in Balance of Account %s for Period %s in Fiscal Year %s (Before : %s; After : %s) </div>' % (acc_obj.doc.name, d.period, fiscal_yr, flt(d.balance), flt(bal)))
- sql("update `tabAccount Balance` set balance = '%s' where period = '%s' and account = '%s' " % (bal, d.period, acc_obj.doc.name))
-
-
- # =============================================================================
- def repair_acc_bal(self, acc, past_yr = '' , fiscal_yr = ''):
- # get account obj
- acc_obj = get_obj('Account', acc, with_children = 1)
-
- # get fiscal yr & past yr
- if not fiscal_yr:
- import webnotes.utils
- fiscal_yr = webnotes.utils.get_defaults()['fiscal_year']
- if not past_yr: past_yr = get_value('Fiscal Year', fiscal_yr, 'past_year')
-
- # Repair Opening and Balance For Account Balances
- for d in getlist(acc_obj.doclist, 'account_balances'):
- if d.fiscal_year == fiscal_yr:
- if past_yr and (past_yr != fiscal_yr) and d.period == fiscal_yr:
- self.repair_opening_bal(d, acc_obj, past_yr, fiscal_yr)
- else:
- self.repair_bal(d, acc_obj, fiscal_yr)
-
- # Acknowledge USer
- if not self.msg:
- msgprint('<div style="color: GREEN"> Openings & Balances of Account %s for Fiscal Year %s updated successfully. </div>' % ( acc_obj.doc.name, fiscal_yr))
-
- return self.msg
-
-
- # =============================================================================
- def send_mail(self, args):
- args = eval(args)
- self.msg, subject = args['msg'], args['subject']
- msgprint(self.msg)
- if self.msg:
- email_msg = """ Dear Administrator,
-
-In Account := %s User := %s has Reposted %s and following was found:-
-
-%s
-
-""" % (get_value('Control Panel', None,'account_id'), session['user'], subject, '\n'.join(self.msg))
-
- sendmail(['support@iwebnotes.com'], subject='Repair of ' + cstr(subject), parts = [('text/plain', email_msg)])
diff --git a/erpnext/utilities/doctype/reposting_tool/reposting_tool.txt b/erpnext/utilities/doctype/reposting_tool/reposting_tool.txt
deleted file mode 100644
index 453902b..0000000
--- a/erpnext/utilities/doctype/reposting_tool/reposting_tool.txt
+++ /dev/null
@@ -1,94 +0,0 @@
-# DocType, Reposting Tool
-[
-
- # These values are common in all dictionaries
- {
- 'creation': '2012-03-27 14:36:47',
- 'docstatus': 0,
- 'modified': '2012-03-27 14:36:47',
- 'modified_by': u'Administrator',
- 'owner': u'Administrator'
- },
-
- # These values are common for all DocType
- {
- 'allow_copy': 1,
- 'allow_email': 1,
- 'allow_print': 1,
- 'colour': u'Light Blue:DEF',
- 'default_print_format': u'Standard',
- 'doctype': 'DocType',
- 'hide_toolbar': 1,
- 'in_create': 0,
- 'issingle': 1,
- 'module': u'Utilities',
- 'name': '__common__',
- 'read_only': 1,
- 'section_style': u'Simple',
- 'server_code_error': u' ',
- 'show_in_menu': 1,
- 'version': 173
- },
-
- # These values are common for all DocField
- {
- 'doctype': u'DocField',
- 'name': '__common__',
- 'parent': u'Reposting Tool',
- 'parentfield': u'fields',
- 'parenttype': u'DocType',
- 'permlevel': 0
- },
-
- # These values are common for all DocPerm
- {
- 'create': 1,
- 'doctype': u'DocPerm',
- 'name': '__common__',
- 'parent': u'Reposting Tool',
- 'parentfield': u'permissions',
- 'parenttype': u'DocType',
- 'permlevel': 0,
- 'read': 1,
- 'role': u'System Manager',
- 'write': 1
- },
-
- # DocType, Reposting Tool
- {
- 'doctype': 'DocType',
- 'name': u'Reposting Tool'
- },
-
- # DocPerm
- {
- 'doctype': u'DocPerm'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'recalculate_mar_&_actual_qty',
- 'fieldtype': u'Data',
- 'label': u'Recalculate MAR & Actual Qty'
- },
-
- # DocField
- {
- 'colour': u'White:FFF',
- 'doctype': u'DocField',
- 'fieldname': u'repost_bin',
- 'fieldtype': u'Button',
- 'label': u'Repost Bin',
- 'trigger': u'Client'
- },
-
- # DocField
- {
- 'doctype': u'DocField',
- 'fieldname': u'repost_account_balances',
- 'fieldtype': u'Button',
- 'label': u'Repost Account Balances',
- 'trigger': u'Client'
- }
-]
\ No newline at end of file
diff --git a/public/js/all-app.js b/public/js/all-app.js
index 2126b37..7246387 100644
--- a/public/js/all-app.js
+++ b/public/js/all-app.js
@@ -975,7 +975,8 @@
<hr>\
<p><button class="btn btn-info btn-small" list_view_doc="%(doctype)s">\
Make a new %(doctype_label)s</button>\
- </p></div>',{doctype_label:get_doctype_label(this.doctype),doctype:this.doctype});return no_result_message;},render_row:function(row,data){data.doctype=this.doctype;this.listview.render(row,data,this);},get_query_fields:function(){return this.listview.fields;},get_args:function(){return{doctype:this.doctype,fields:this.get_query_fields(),filters:this.filter_list.get_filters(),docstatus:this.can_submit?$.map(this.$page.find('.show-docstatus :checked'),function(inp){return $(inp).attr('data-docstatus')}):[],order_by:this.listview.order_by||undefined,group_by:this.listview.group_by||undefined,}},add_delete_option:function(){var me=this;if(this.can_delete){this.add_button('Delete',function(){me.delete_items();},'icon-remove');$('<div style="padding: 4px"><input type="checkbox" name="select-all" />\
+ </p></div>',{doctype_label:get_doctype_label(this.doctype),doctype:this.doctype});return no_result_message;},render_row:function(row,data){data.doctype=this.doctype;this.listview.render(row,data,this);},get_query_fields:function(){return this.listview.fields;},get_args:function(){var args={doctype:this.doctype,fields:this.get_query_fields(),filters:this.filter_list.get_filters(),docstatus:this.can_submit?$.map(this.$page.find('.show-docstatus :checked'),function(inp){return $(inp).attr('data-docstatus')}):[],order_by:this.listview.order_by||undefined,group_by:this.listview.group_by||undefined,}
+$.each((this.listview.default_filters||[]),function(i,f){args.filters.push(f);});return args;},add_delete_option:function(){var me=this;if(this.can_delete){this.add_button('Delete',function(){me.delete_items();},'icon-remove');$('<div style="padding: 4px"><input type="checkbox" name="select-all" />\
Select all</div>').insertBefore(this.$page.find('.result-list'));this.$page.find('[name="select-all"]').click(function(){me.$page.find('.list-delete').attr('checked',$(this).attr('checked')||false);})}},delete_items:function(){var me=this;var dl=$.map(me.$page.find('.list-delete:checked'),function(e){return $(e).data('name');});if(!dl.length)
return;if(!confirm('This is PERMANENT action and you cannot undo. Continue?')){return;}
me.set_working(true);wn.call({method:'webnotes.widgets.doclistview.delete_items',args:{items:dl,doctype:me.doctype},callback:function(){me.set_working(false);me.refresh();}})},init_stats:function(){var me=this
@@ -1088,7 +1089,7 @@
<div class="progress progress-striped active">\
<div class="bar" style="width: 10%"></div></div>').appendTo(this.wrapper);},load_filter_values:function(){var me=this;$.each(this.filter_inputs,function(i,f){var opts=f.get(0).opts;if(opts.fieldtype!='Button'){me[opts.fieldname]=f.val();if(opts.fieldtype=="Date"){me[opts.fieldname]=dateutil.user_to_str(me[opts.fieldname]);}else if(opts.fieldtype=="Select"){me[opts.fieldname+'_default']=opts.default_value;}}});if(this.filter_inputs.from_date&&this.filter_inputs.to_date&&(this.to_date<this.from_date)){msgprint("From Date must be before To Date");return;}},make_name_map:function(data,key){var map={};key=key||"name";$.each(data,function(i,v){map[v[key]]=v;})
return map;},reset_item_values:function(item){var me=this;$.each(this.columns,function(i,col){if(col.formatter==me.currency_formatter){item[col.id]=0;}});},import_slickgrid:function(){wn.require('js/lib/slickgrid/slick.grid.css');wn.require('js/lib/slickgrid/slick-default-theme.css');wn.require('js/lib/slickgrid/jquery.event.drag.min.js');wn.require('js/lib/slickgrid/slick.core.js');wn.require('js/lib/slickgrid/slick.grid.js');wn.require('js/lib/slickgrid/slick.dataview.js');wn.dom.set_style('.slick-cell { font-size: 12px; }');if(this.tree_grid.show)wn.require("js/app/tree_grid.css");},refresh:function(){this.waiting.toggle(false);if(!this.grid_wrapper)
-this.make();this.show_zero=$('.show-zero input:checked').length;this.load_filter_values();this.setup_columns();this.setup_dataview_columns();this.apply_link_formatters();this.prepare_data();this.prepare_data_view();this.wrapper.find(".processing").toggle(true);this.wrapper.find(".processing").delay(2000).fadeOut(300);this.render();this.render_plot&&this.render_plot();},setup_dataview_columns:function(){this.dataview_columns=$.map(this.columns,function(col){return!col.hidden?col:null;});},make:function(){this.plot_area=$('<div class="plot" style="margin-bottom: 15px; display: none; \
+this.make();this.show_zero=$('.show-zero input:checked').length;this.load_filter_values();this.setup_columns();this.setup_dataview_columns();this.apply_link_formatters();this.prepare_data();this.prepare_data_view();this.wrapper.find(".processing").toggle(true);this.wrapper.find(".processing").delay(2000).fadeOut(300);this.render();this.render_plot&&this.render_plot();},setup_dataview_columns:function(){this.dataview_columns=$.map(this.columns,function(col){return!col.hidden?col:null;});},make:function(){var me=this;this.plot_area=$('<div class="plot" style="margin-bottom: 15px; display: none; \
height: 300px; width: 100%;"></div>').appendTo(this.wrapper);$('<div style="text-align: right;"> \
<div class="processing" style="background-color: #fec; display: none; float: left; margin: 2px"> \
Updated! </div>\