def create_remarks(self):
r = []
if self.doc.cheque_no :
if self.doc.cheque_date:
r.append('Via Reference #%s dated %s' %
(self.doc.cheque_no, formatdate(self.doc.cheque_date)))
else :
msgprint("Please enter Reference date", raise_exception=1)
for d in getlist(self.doclist, 'entries'):
if d.against_invoice and d.credit:
currency = webnotes.conn.get_value("Sales Invoice", d.against_invoice, "currency")
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 = webnotes.conn.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']:
r.append('%s %s against Bill %s dated %s' %
(cstr(bill_no[0][2]), fmt_money(flt(d.debit)), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d')) or ''))
if self.doc.user_remark:
r.append("User Remark : %s"%self.doc.user_remark)
if r:
self.doc.remark = ("\n").join(r)
else:
webnotes.msgprint("User Remarks is mandatory", raise_exception=1)
def create_remarks(self):
r = []
if self.doc.cheque_no :
if self.doc.cheque_date:
r.append('Via Reference #%s dated %s' %
(self.doc.cheque_no, formatdate(self.doc.cheque_date)))
else :
msgprint("Please enter Reference date", raise_exception=1)
for d in getlist(self.doclist, 'entries'):
if d.against_invoice and d.credit:
currency = webnotes.conn.get_value("Sales Invoice", d.against_invoice, "currency")
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 = webnotes.conn.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']:
r.append('%s %s against Bill %s dated %s' %
(cstr(bill_no[0][2]), fmt_money(flt(d.debit)), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d')) or ''))
if self.doc.user_remark:
r.append("User Remark : %s"%self.doc.user_remark)
if r:
self.doc.remark = ("\n").join(r)
else:
webnotes.msgprint("User Remarks is mandatory", raise_exception=1)
def check_credit_limit(self, account, company, tot_outstanding):
# Get credit limit
credit_limit_from = 'Customer'
cr_limit = sql("select t1.credit_limit from tabCustomer t1, `tabAccount` t2 where t2.name='%s' and t1.name = t2.master_name" % account)
credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
if not credit_limit:
credit_limit = get_value('Company', company, 'credit_limit')
credit_limit_from = 'global settings in the Company'
# If outstanding greater than credit limit and not authorized person raise exception
if credit_limit > 0 and flt(tot_outstanding) > credit_limit and not self.get_authorized_user():
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)
def check_credit_limit(self, account, company, tot_outstanding):
# Get credit limit
credit_limit_from = 'Customer'
cr_limit = sql("select t1.credit_limit from tabCustomer t1, `tabAccount` t2 where t2.name='%s' and t1.name = t2.master_name" % account)
credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
if not credit_limit:
credit_limit = get_value('Company', company, 'credit_limit')
credit_limit_from = 'global settings in the Company'
# If outstanding greater than credit limit and not authorized person raise exception
if credit_limit > 0 and flt(tot_outstanding) > credit_limit and not self.get_authorized_user():
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)
def decorate_quotation_doclist(doclist):
for d in doclist:
if d.item_code:
d.fields.update(webnotes.conn.get_value("Item", d.item_code,
["website_image", "description", "page_name"], as_dict=True))
d.formatted_rate = fmt_money(d.export_rate, currency=doclist[0].currency)
d.formatted_amount = fmt_money(d.export_amount, currency=doclist[0].currency)
elif d.charge_type:
d.formatted_tax_amount = fmt_money(d.tax_amount / doclist[0].conversion_rate,
currency=doclist[0].currency)
doclist[0].formatted_grand_total_export = fmt_money(doclist[0].grand_total_export,
currency=doclist[0].currency)
return [d.fields for d in doclist]
def decorate_quotation_doclist(doclist):
for d in doclist:
if d.item_code:
d.fields.update(webnotes.conn.get_value("Item", d.item_code,
["website_image", "description", "page_name"], as_dict=True))
d.formatted_rate = fmt_money(d.export_rate, currency=doclist[0].currency)
d.formatted_amount = fmt_money(d.export_amount, currency=doclist[0].currency)
elif d.charge_type:
d.formatted_tax_amount = fmt_money(d.tax_amount / doclist[0].conversion_rate,
currency=doclist[0].currency)
doclist[0].formatted_grand_total_export = fmt_money(doclist[0].grand_total_export,
currency=doclist[0].currency)
return [d.fields for d in doclist]
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)
if self.doc.against_voucher_type == 'Purchase Invoice':
# amount to debit
bal = -bal
# Validation : Outstanding can not be negative
if bal < 0 and self.doc.is_cancelled == 'No':
msgprint(_("Outstanding for Voucher ") + self.doc.against_voucher +
_(" will become ") + fmt_money(bal) +
_("Outstanding cannot be less than zero. \
Please match exact outstanding."),
raise_exception=1)
# Update outstanding amt on against voucher
sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
(self.doc.against_voucher_type, bal, self.doc.against_voucher))
def update_outstanding_amt(account, against_voucher_type, against_voucher, on_cancel=False):
# get final outstanding amt
bal = flt(webnotes.conn.sql("""select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where against_voucher_type=%s and against_voucher=%s and account = %s""",
(against_voucher_type, against_voucher, account),debug=1)[0][0] or 0.0)
if against_voucher_type == 'Purchase Invoice':
bal = -bal
elif against_voucher_type == "Journal Voucher":
against_voucher_amount = flt(webnotes.conn.sql("""
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
and account = %s and ifnull(against_voucher, '') = ''""",
(against_voucher, account),debug=1)[0][0])
bal = against_voucher_amount + bal
if against_voucher_amount < 0:
bal = -bal
# Validation : Outstanding can not be negative
if bal < 0 and not on_cancel:
webnotes.throw(_("Outstanding for Voucher ") + against_voucher + _(" will become ") +
fmt_money(bal) + _(". Outstanding cannot be less than zero. \
Please match exact outstanding."))
# Update outstanding amt on against voucher
if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
webnotes.conn.sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
(against_voucher_type, bal, against_voucher))
def convert_to_simple_type(self, v, formatted=0):
import datetime
from webnotes.utils import formatdate, fmt_money
# date
if type(v) == datetime.date:
v = str(v)
if formatted:
v = formatdate(v)
# time
elif type(v) == datetime.timedelta:
h = int(v.seconds / 60 / 60)
v = str(h) + ':' + str(v.seconds / 60 - h * 60)
if v[1] == ':':
v = '0' + v
# datetime
elif type(v) == datetime.datetime:
v = str(v)
# long
elif type(v) == long:
v = int(v)
# convert to strings... (if formatted)
if formatted:
if type(v) == float:
v = fmt_money(v)
if type(v) == int:
v = str(v)
return v
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
if self.doc.against_voucher_type == "Purchase Invoice":
# amount to debit
bal = -bal
# Check if tds applicable
tds = sql(
"select total_tds_on_voucher from `tabPurchase Invoice` where name = '%s'" % self.doc.against_voucher
)
tds = tds and flt(tds[0][0]) or 0
# Validation : Outstanding can not be negative
if bal < 0 and not tds and self.doc.is_cancelled == "No":
msgprint(
"Outstanding for Voucher %s will become %s. Outstanding cannot be less than zero. Please match exact outstanding."
% (self.doc.against_voucher, fmt_money(bal))
)
raise Exception
# Update outstanding amt on against voucher
sql(
"update `tab%s` set outstanding_amount=%s where name='%s'"
% (self.doc.against_voucher_type, bal, self.doc.against_voucher)
)
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)
if self.doc.against_voucher_type == 'Purchase Invoice':
bal = -bal
elif self.doc.against_voucher_type == "Journal Voucher":
against_voucher_amount = flt(webnotes.conn.sql("""select sum(debit) - sum(credit)
from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
and account = %s""", (self.doc.against_voucher, self.doc.account))[0][0])
bal = against_voucher_amount + bal
if against_voucher_amount < 0:
bal = -bal
# Validation : Outstanding can not be negative
if bal < 0 and self.doc.is_cancelled == 'No':
msgprint(_("Outstanding for Voucher ") + self.doc.against_voucher +
_(" will become ") + fmt_money(bal) + _(". Outstanding cannot be less than zero. \
Please match exact outstanding."), raise_exception=1)
# Update outstanding amt on against voucher
if self.doc.against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
sql("update `tab%s` set outstanding_amount=%s where name='%s'"%
(self.doc.against_voucher_type, bal, self.doc.against_voucher))
def convert_to_simple_type(self, v, formatted=0): import datetime from webnotes.utils import formatdate, fmt_money # date if type(v)==datetime.date: v = str(v) if formatted: v = formatdate(v) # time elif type(v)==datetime.timedelta: h = int(v.seconds/60/60) v = str(h) + ':' + str(v.seconds/60 - h*60) if v[1]==':': v='0'+v # datetime elif type(v)==datetime.datetime: v = str(v) # long elif type(v)==long: v=int(v) # convert to strings... (if formatted) if formatted: if type(v)==float: v=fmt_money(v) if type(v)==int: v=str(v) return v
def convert_to_simple_type(self, v, formatted=0): import datetime from webnotes.utils import formatdate, fmt_money # date if type(v)==datetime.date: v = unicode(v) if formatted: v = formatdate(v) # time elif type(v)==datetime.timedelta: v = unicode(v) # datetime elif type(v)==datetime.datetime: v = unicode(v) # long elif type(v)==long: v=int(v) # convert to strings... (if formatted) if formatted: if type(v)==float: v=fmt_money(v) if type(v)==int: v=str(v) return v
def create_remarks(self):
r = []
if self.doc.cheque_no :
if self.doc.cheque_date:
r.append('Via Reference #%s dated %s' % (self.doc.cheque_no, formatdate(self.doc.cheque_date)))
else :
msgprint("Please enter Reference 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.user_remark:
r.append("User Remark : %s"%self.doc.user_remark)
if r:
self.doc.remark = ("\n").join(r)
def get_children():
args = webnotes.form_dict
ctype, company = args['ctype'], args['comp']
company_field = ctype=='Account' and 'company' or 'company_name'
# root
if args['parent'] == company:
acc = webnotes.conn.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and %s = %s and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), company_field, '%s'),
args['parent'], as_dict=1)
else:
# other
acc = webnotes.conn.sql("""select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = %s
and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'),
args['parent'], as_dict=1)
if ctype == 'Account':
currency = webnotes.conn.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
for each in acc:
bal = get_balance_on(each.get("value"))
each['balance'] = currency + ' ' + fmt_money(bal)
return acc
def update_outstanding_amt(account, against_voucher_type, against_voucher, on_cancel=False):
# get final outstanding amt
bal = flt(webnotes.conn.sql("""select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where against_voucher_type=%s and against_voucher=%s and account = %s""",
(against_voucher_type, against_voucher, account))[0][0] or 0.0)
if against_voucher_type == 'Purchase Invoice':
bal = -bal
elif against_voucher_type == "Journal Voucher":
against_voucher_amount = flt(webnotes.conn.sql("""
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
and account = %s and ifnull(against_voucher, '') = ''""",
(against_voucher, account))[0][0])
bal = against_voucher_amount + bal
if against_voucher_amount < 0:
bal = -bal
# Validation : Outstanding can not be negative
if bal < 0 and not on_cancel:
webnotes.throw(_("Outstanding for Voucher ") + against_voucher + _(" will become ") +
fmt_money(bal) + _(". Outstanding cannot be less than zero. \
Please match exact outstanding."))
# Update outstanding amt on against voucher
if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
webnotes.conn.sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
(against_voucher_type, bal, against_voucher))
def convert_to_simple_type(self, v, formatted=0): from webnotes.utils import formatdate, fmt_money if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)): if isinstance(v, datetime.date): v = unicode(v) if formatted: v = formatdate(v) # time elif isinstance(v, (datetime.timedelta, datetime.datetime)): v = unicode(v) # long elif isinstance(v, long): v=int(v) # convert to strings... (if formatted) if formatted: if isinstance(v, float): v=fmt_money(v) elif isinstance(v, int): v = unicode(v) return v
def convert_to_simple_type(self, v, formatted=0): from webnotes.utils import formatdate, fmt_money if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)): if isinstance(v, datetime.date): v = unicode(v) if formatted: v = formatdate(v) # time elif isinstance(v, (datetime.timedelta, datetime.datetime)): v = unicode(v) # long elif isinstance(v, long): v=int(v) # convert to strings... (if formatted) if formatted: if isinstance(v, float): v=fmt_money(v) elif isinstance(v, int): v = unicode(v) return v
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)
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
if self.doc.against_voucher_type == 'Purchase Invoice':
# amount to debit
bal = -bal
# Check if tds applicable
tds = sql(
"select total_tds_on_voucher from `tabPurchase Invoice` where name = '%s'"
% self.doc.against_voucher)
tds = tds and flt(tds[0][0]) or 0
# Validation : Outstanding can not be negative
if bal < 0 and not tds and self.doc.is_cancelled == 'No':
msgprint(
"Outstanding for Voucher %s will become %s. Outstanding cannot be less than zero. Please match exact outstanding."
% (self.doc.against_voucher, fmt_money(bal)))
raise Exception
# Update outstanding amt on against voucher
sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
(self.doc.against_voucher_type, bal, self.doc.against_voucher))
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_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 modify_status(doc):
doc.status = ""
if flt(doc.outstanding_amount):
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-warning", "icon-exclamation-sign",
_("To Pay") + " = " + fmt_money(doc.outstanding_amount, currency=doc.currency))
else:
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-success", "icon-ok", _("Paid"))
def get_new_sum(self, doctype, label, sum_field):
count_sum = webnotes.conn.sql("""select count(*), sum(ifnull(`%s`, 0))
from `tab%s` where docstatus < 2 and company = %s and
date(creation)>=%s and date(creation)<=%s""" % (sum_field, doctype, "%s",
"%s", "%s"), (self.doc.company, self.from_date, self.to_date))
count, total = count_sum and count_sum[0] or (0, 0)
return count, self.get_html(label, self.currency,
"%s - (%s)" % (fmt_money(total), cstr(count)))
def check_credit_limit(self, total_outstanding):
# Get credit limit
credit_limit_from = 'Customer'
cr_limit = webnotes.conn.sql("""select t1.credit_limit from tabCustomer t1, `tabAccount` t2
where t2.name=%s and t1.name = t2.master_name""", self.doc.name)
credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
if not credit_limit:
credit_limit = webnotes.conn.get_value('Company', self.doc.company, 'credit_limit')
credit_limit_from = 'Company'
# If outstanding greater than credit limit and not authorized person raise exception
if credit_limit > 0 and flt(total_outstanding) > credit_limit \
and not self.get_authorized_user():
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 in the <b>%s</b> master""" % (fmt_money(total_outstanding),
self.doc.name, fmt_money(credit_limit), credit_limit_from), raise_exception=1)
def check_credit_limit(self, total_outstanding):
# Get credit limit
credit_limit_from = 'Customer'
cr_limit = webnotes.conn.sql("""select t1.credit_limit from tabCustomer t1, `tabAccount` t2
where t2.name=%s and t1.name = t2.master_name""", self.doc.name)
credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
if not credit_limit:
credit_limit = webnotes.conn.get_value('Company', self.doc.company, 'credit_limit')
credit_limit_from = 'Company'
# If outstanding greater than credit limit and not authorized person raise exception
if credit_limit > 0 and flt(total_outstanding) > credit_limit \
and not self.get_authorized_user():
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 in the <b>%s</b> master""" % (fmt_money(total_outstanding),
self.doc.name, fmt_money(credit_limit), credit_limit_from), raise_exception=1)
def get_booked_total(self, party_type, gle_field, label):
# account is of master_type Customer or Supplier
accounts = [a["name"] for a in self.get_accounts() if a["master_type"] == party_type]
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
total += gle[gle_field]
return total, self.get_html(label, self.currency, fmt_money(total))
def modify_status(doc):
doc.status = ""
if flt(doc.outstanding_amount):
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-warning", "icon-exclamation-sign",
_("To Pay") + " = " + fmt_money(doc.outstanding_amount, currency=doc.currency))
else:
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-success", "icon-ok", _("Paid"))
def get_income(self, from_date=None, label=None): # account is PL Account and Credit type account accounts = [a["name"] for a in self.get_accounts() if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Credit"] income = 0 for gle in self.get_gl_entries(from_date or self.from_date, self.to_date): if gle["account"] in accounts: income += gle["credit"] - gle["debit"] return income, self.get_html(label or "Income", self.currency, fmt_money(income))
def get_expenses_booked(self):
# account is PL Account and Debit type account
accounts = [a["name"] for a in self.get_accounts()
if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Debit"]
expense = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
expense += gle["debit"] - gle["credit"]
return expense, self.get_html("Expenses", self.currency, fmt_money(expense))
def get_booked_total(self, party_type, gle_field, label): # account is of master_type Customer or Supplier accounts = [a["name"] for a in self.get_accounts() if a["master_type"]==party_type] total = 0 for gle in self.get_gl_entries(self.from_date, self.to_date): if gle["account"] in accounts: total += gle[gle_field] return total, self.get_html(label, self.currency, fmt_money(total))
def get_income(self, from_date=None, label=None):
# account is PL Account and Credit type account
accounts = [a["name"] for a in self.get_accounts()
if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Credit"]
income = 0
for gle in self.get_gl_entries(from_date or self.from_date, self.to_date):
if gle["account"] in accounts:
income += gle["credit"] - gle["debit"]
return income, self.get_html(label or self.meta.get_label("income"), self.currency,
fmt_money(income))
def get_expenses_booked(self):
# account is PL Account and Debit type account
accounts = [a["name"] for a in self.get_accounts()
if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Debit"]
expense = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
expense += gle["debit"] - gle["credit"]
return expense, self.get_html(self.meta.get_label("expenses_booked"), self.currency,
fmt_money(expense))
def get_product_info(item_code):
"""get product price / stock info"""
if not cint(webnotes.conn.get_default("shopping_cart_enabled")):
return {}
cart_quotation = _get_cart_quotation()
price_list = webnotes.local.request.cookies.get("selling_price_list")
warehouse = webnotes.conn.get_value("Item", item_code, "website_warehouse")
if warehouse:
in_stock = webnotes.conn.sql(
"""select actual_qty from tabBin where
item_code=%s and warehouse=%s""", (item_code, warehouse))
if in_stock:
in_stock = in_stock[0][0] > 0 and 1 or 0
else:
in_stock = -1
price = price_list and webnotes.conn.sql(
"""select ip.ref_rate, ip.currency from
`tabItem Price` ip, `tabPrice List` pl where ip.price_list=pl.name and
ip.item_code=%s and ip.price_list=%s and pl.enabled=1""",
(item_code, price_list),
as_dict=1) or []
price = price and price[0] or None
qty = 0
if price:
price["formatted_price"] = fmt_money(price["ref_rate"],
currency=price["currency"])
price["currency"] = not cint(webnotes.conn.get_default("hide_currency_symbol")) \
and (webnotes.conn.get_value("Currency", price.currency, "symbol") or price.currency) \
or ""
if webnotes.session.user != "Guest":
item = cart_quotation.doclist.get({"item_code": item_code})
if item:
qty = item[0].qty
return {
"price": price,
"stock": in_stock,
"uom": webnotes.conn.get_value("Item", item_code, "stock_uom"),
"qty": qty
}
def get_bank_balance(self):
# account is of type "Bank or Cash"
accounts = dict([[a["name"], [a["account_name"], 0]] for a in self.get_accounts()
if a["account_type"]=="Bank or Cash"])
ackeys = accounts.keys()
for gle in self.get_gl_entries(None, self.to_date):
if gle["account"] in ackeys:
accounts[gle["account"]][1] += gle["debit"] - gle["credit"]
# build html
out = self.get_html("Bank/Cash Balance", "", "")
for ac in ackeys:
if accounts[ac][1]:
out += "\n" + self.get_html(accounts[ac][0], self.currency,
fmt_money(accounts[ac][1]), style="margin-left: 17px")
return sum((accounts[ac][1] for ac in ackeys)), out
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)
if self.doc.against_voucher_type=='Purchase Invoice':
# amount to debit
bal = -bal
# Validation : Outstanding can not be negative
if bal < 0 and self.doc.is_cancelled == 'No':
msgprint("""Outstanding for Voucher %s will become %s.
Outstanding cannot be less than zero. Please match exact outstanding.""" %
(self.doc.against_voucher, fmt_money(bal)))
raise Exception
# Update outstanding amt on against voucher
sql("update `tab%s` set outstanding_amount=%s where name='%s'"%
(self.doc.against_voucher_type, bal, self.doc.against_voucher))
def get_party_total(self, party_type, gle_field, label):
import re
# account is of master_type Customer or Supplier
accounts = [a["name"] for a in self.get_accounts() if a["master_type"] == party_type]
# account is "Bank or Cash"
bc_accounts = [esc(a["name"], "()|") for a in self.get_accounts() if a["account_type"] == "Bank or Cash"]
bc_regex = re.compile("""(%s)""" % "|".join(bc_accounts))
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
# check that its made against a bank or cash account
if gle["account"] in accounts and gle["against"] and bc_regex.findall(gle["against"]):
val = gle["debit"] - gle["credit"]
total += (gle_field == "debit" and 1 or -1) * val
return total, self.get_html(label, self.currency, fmt_money(total))
def convert_to_simple_type(self, v, formatted=0):
try:
import decimal # for decimal Python 2.5 onwards
except:
pass
import datetime
from webnotes.utils import formatdate, fmt_money
# date
if type(v) == datetime.date:
v = str(v)
if formatted:
v = formatdate(v)
# time
elif type(v) == datetime.timedelta:
h = int(v.seconds / 60 / 60)
v = str(h) + ":" + str(v.seconds / 60 - h * 60)
if v[1] == ":":
v = "0" + v
# datetime
elif type(v) == datetime.datetime:
v = str(v)
# long
elif type(v) == long:
v = int(v)
# decimal
try:
if type(v) == decimal.Decimal:
v = float(v)
except:
pass
# convert to strings... (if formatted)
if formatted:
if type(v) == float:
v = fmt_money(v)
if type(v) == int:
v = str(v)
return v
def get_product_info(item_code):
"""get product price / stock info"""
if not cint(webnotes.conn.get_default("shopping_cart_enabled")):
return {}
cart_quotation = _get_cart_quotation()
price_list = webnotes.cookies.get("selling_price_list").value
warehouse = webnotes.conn.get_value("Item", item_code, "website_warehouse")
if warehouse:
in_stock = webnotes.conn.sql("""select actual_qty from tabBin where
item_code=%s and warehouse=%s""", (item_code, warehouse))
if in_stock:
in_stock = in_stock[0][0] > 0 and 1 or 0
else:
in_stock = -1
price = price_list and webnotes.conn.sql("""select ip.ref_rate, pl.currency from
`tabItem Price` ip, `tabPrice List` pl where ip.parent = pl.name and
ip.item_code=%s and ip.parent=%s""",
(item_code, price_list), as_dict=1) or []
price = price and price[0] or None
qty = 0
if price:
price["formatted_price"] = fmt_money(price["ref_rate"], currency=price["currency"])
price["currency"] = not cint(webnotes.conn.get_default("hide_currency_symbol")) \
and (webnotes.conn.get_value("Currency", price.currency, "symbol") or price.currency) \
or ""
if webnotes.session.user != "Guest":
item = cart_quotation.doclist.get({"item_code": item_code})
if item:
qty = item[0].qty
return {
"price": price,
"stock": in_stock,
"uom": webnotes.conn.get_value("Item", item_code, "stock_uom"),
"qty": qty
}
def convert_to_simple_type(self, v, formatted=0):
try:
import decimal # for decimal Python 2.5 onwards
except:
pass
import datetime
from webnotes.utils import formatdate, fmt_money
# date
if type(v) == datetime.date:
v = str(v)
if formatted:
v = formatdate(v)
# time
elif type(v) == datetime.timedelta:
h = int(v.seconds / 60 / 60)
v = str(h) + ':' + str(v.seconds / 60 - h * 60)
if v[1] == ':':
v = '0' + v
# datetime
elif type(v) == datetime.datetime:
v = str(v)
# long
elif type(v) == long:
v = int(v)
# decimal
try:
if type(v) == decimal.Decimal:
v = float(v)
except:
pass
# convert to strings... (if formatted)
if formatted:
if type(v) == float:
v = fmt_money(v)
if type(v) == int:
v = str(v)
return v
def get_party_total(self, party_type, gle_field, label):
import re
# account is of master_type Customer or Supplier
accounts = [a["name"] for a in self.get_accounts()
if a["master_type"]==party_type]
# account is "Bank or Cash"
bc_accounts = [esc(a["name"], "()|") for a in self.get_accounts()
if a["account_type"]=="Bank or Cash"]
bc_regex = re.compile("""(%s)""" % "|".join(bc_accounts))
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
# check that its made against a bank or cash account
if gle["account"] in accounts and gle["against"] and \
bc_regex.findall(gle["against"]):
val = gle["debit"] - gle["credit"]
total += (gle_field=="debit" and 1 or -1) * val
return total, self.get_html(label, self.currency, fmt_money(total))
def get_children():
args = webnotes.form_dict
ctype, company = args['ctype'], args['comp']
company_field = ctype == 'Account' and 'company' or 'company_name'
# root
if args['parent'] == company:
acc = webnotes.conn.sql(
""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and %s = %s and docstatus<2
order by name""" %
(ctype, ctype.lower().replace(' ', '_'), company_field, '%s'),
args['parent'],
as_dict=1)
else:
# other
acc = webnotes.conn.sql("""select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = %s
and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ', '_'), '%s'),
args['parent'],
as_dict=1)
if ctype == 'Account':
currency = webnotes.conn.sql(
"select default_currency from `tabCompany` where name = %s",
company)[0][0]
for each in acc:
bal = get_balance_on(each.get("value"))
each['balance'] = currency + ' ' + fmt_money(bal)
return acc
def get_standard_body(self, result):
"""
Generate email body depending on the result
"""
from webnotes.utils import fmt_money
from webnotes.model.doc import Document
company = Document('Company', self.doc.company)
currency = company.default_currency
def table(args):
table_body = ""
if isinstance(args['body'], basestring):
table_body = """\
<tbody><tr>
<td style='padding: 5px; font-size: 24px; \
font-weight: bold; background: #F7F7F5'>""" + \
args['body'] + \
"""\
</td>
</tr></tbody>"""
elif isinstance(args['body'], list):
body_rows = []
for rows in args['body']:
for r in rows:
body_rows.append("""\
<tr>
<td style='padding: 5px; font-size: 24px; \
font-weight: bold; background: #F7F7F5'>""" \
+ r + """\
</td>
</tr>""")
body_rows.append("<tr><td style='background: #F7F7F5'><br></td></tr>")
table_body = "<tbody>" + "".join(body_rows) + "</tbody>"
table_head = """\
<thead><tr>
<td style='padding: 5px; background: #D8D8D4; font-size: 16px; font-weight: bold'>""" \
+ args['head'] + """\
</td>
</tr></thead>"""
return "<table style='border-collapse: collapse; width: 100%;'>" \
+ table_head \
+ table_body \
+ "</table>"
currency_amount_str = "<span style='color: grey; font-size: 12px'>%s</span> %s"
body_dict = {
'invoiced_amount': {
'table': 'invoiced_amount' in result and table({
'head': 'Invoiced Amount',
'body': currency_amount_str \
% (currency, fmt_money(result['invoiced_amount']['debit']))
}),
'idx': 300
},
'payables': {
'table': 'payables' in result and table({
'head': 'Payables',
'body': currency_amount_str \
% (currency, fmt_money(result['payables']['credit']))
}),
'idx': 200
},
'collections': {
'table': 'collections' in result and table({
'head': 'Collections',
'body': currency_amount_str \
% (currency, fmt_money(result['collections']['credit']))
}),
'idx': 301
},
'payments': {
'table': 'payments' in result and table({
'head': 'Payments',
'body': currency_amount_str \
% (currency, fmt_money(result['payments']['debit']))
}),
'idx': 201
},
'income': {
'table': 'income' in result and table({
'head': 'Income',
'body': currency_amount_str \
% (currency, fmt_money(result['income']['value']))
}),
'idx': 302
},
'income_year_to_date': {
'table': 'income_year_to_date' in result and table({
'head': 'Income Year To Date',
'body': currency_amount_str \
% (currency, fmt_money(result['income_year_to_date']['value']))
}),
'idx': 303
},
'expenses_booked': {
'table': 'expenses_booked' in result and table({
'head': 'Expenses Booked',
'body': currency_amount_str \
% (currency, fmt_money(result['expenses_booked']['value']))
}),
'idx': 202
},
'bank_balance': {
'table': 'bank_balance' in result and result['bank_balance'] and table({
'head': 'Bank Balance',
'body': [
[
"<span style='font-size: 16px; font-weight: normal'>%s</span>" % bank['name'],
currency_amount_str % (currency, fmt_money(bank['value']))
] for bank in result.get('bank_balance', [])
]
}),
'idx': 400
},
'new_leads': {
'table': 'new_leads' in result and table({
'head': 'New Leads',
'body': '%s' % result['new_leads']['count']
}),
'idx': 100
},
'new_enquiries': {
'table': 'new_enquiries' in result and table({
'head': 'New Enquiries',
'body': '%s' % result['new_enquiries']['count']
}),
'idx': 101
},
'new_quotations': {
'table': 'new_quotations' in result and table({
'head': 'New Quotations',
'body': '%s' % result['new_quotations']['count']
}),
'idx': 102
},
'new_sales_orders': {
'table': 'new_sales_orders' in result and table({
'head': 'New Sales Orders',
'body': '%s' % result['new_sales_orders']['count']
}),
'idx': 103
},
'new_purchase_orders': {
'table': 'new_purchase_orders' in result and table({
'head': 'New Purchase Orders',
'body': '%s' % result['new_purchase_orders']['count']
}),
'idx': 104
},
'new_transactions': {
'table': 'new_transactions' in result and table({
'head': 'New Transactions',
'body': '%s' % result['new_transactions']['count']
}),
'idx': 105
}
#'stock_below_rl':
}
table_list = []
# Sort these keys depending on idx value
bd_keys = sorted(body_dict, key=lambda x: body_dict[x]['idx'])
for k in bd_keys:
if self.doc.fields[k]:
if k in result:
table_list.append(body_dict[k]['table'])
elif k in ['collections', 'payments']:
table_list.append(\
"<div style='font-size: 16px; color: grey'>[" + \
k.capitalize() + \
"]<br />Missing: Account of type 'Bank or Cash'\
</div>")
elif k=='bank_balance':
table_list.append(\
"<div style='font-size: 16px; color: grey'>[" + \
"Bank Balance" + \
"]<br />Alert: GL Entry not found for Account of type 'Bank or Cash'\
</div>")
i = 0
result = []
op_len = len(table_list)
while(True):
if i>=op_len:
break
elif (op_len - i) == 1:
result.append("""\
<tr>
<td style='width: 50%%; vertical-align: top;'>%s</td>
<td></td>
</tr>""" % (table_list[i]))
else:
result.append("""\
<tr>
<td style='width: 50%%; vertical-align: top;'>%s</td>
<td>%s</td>
</tr>""" % (table_list[i], table_list[i+1]))
i = i + 2
from webnotes.utils import formatdate
start_date, end_date = self.get_start_end_dates()
digest_daterange = self.doc.frequency=='Daily' \
and formatdate(str(start_date)) \
or (formatdate(str(start_date)) + " to " + (formatdate(str(end_date))))
email_body = """
<div style='width: 100%%'>
<div style='padding: 10px; margin: auto; text-align: center; line-height: 80%%'>
<p style='font-weight: bold; font-size: 24px'>%s</p>
<p style='font-size: 16px; color: grey'>%s</p>
<p style='font-size: 20px; font-weight: bold'>%s</p>
</div>
<table cellspacing=15 style='width: 100%%'>""" \
% ((self.doc.frequency + " Digest"), \
digest_daterange, self.doc.company) \
+ "".join(result) + """\
</table><br><p></p>
</div>"""
return email_body
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_standard_body(self, result):
"""
Generate email body depending on the result
"""
from webnotes.utils import fmt_money
from webnotes.model.doc import Document
company = Document('Company', self.doc.company)
currency = company.default_currency
def table(args):
table_body = ""
if isinstance(args['body'], basestring):
return """<p>%(head)s: <span style='font-size: 110%%; font-weight: bold;'>%(body)s</span></p>""" % args
else:
return ("""<p>%(head)s:</p> """ % args) +\
"".join(map(lambda b: "<p style='margin-left: 17px;'>%s</p>" % b, args['body']))
currency_amount_str = "<span style='color: grey;'>%s</span> %s"
body_dict = {
'invoiced_amount': {
'table': result.get('invoiced_amount') and \
table({
'head': 'Invoiced Amount',
'body': currency_amount_str \
% (currency, fmt_money(result['invoiced_amount'].get('debit')))
}),
'idx': 300,
'value': result.get('invoiced_amount') and result['invoiced_amount'].get('debit')
},
'payables': {
'table': result.get('payables') and \
table({
'head': 'Payables',
'body': currency_amount_str \
% (currency, fmt_money(result['payables'].get('credit')))
}),
'idx': 200,
'value': result.get('payables') and result['payables'].get('credit')
},
'collections': {
'table': result.get('collections') and \
table({
'head': 'Collections',
'body': currency_amount_str \
% (currency, fmt_money(result['collections'].get('credit')))
}),
'idx': 301,
'value': result.get('collections') and result['collections'].get('credit')
},
'payments': {
'table': result.get('payments') and \
table({
'head': 'Payments',
'body': currency_amount_str \
% (currency, fmt_money(result['payments'].get('debit')))
}),
'idx': 201,
'value': result.get('payments') and result['payments'].get('debit')
},
'income': {
'table': result.get('income') and \
table({
'head': 'Income',
'body': currency_amount_str \
% (currency, fmt_money(result['income'].get('value')))
}),
'idx': 302,
'value': result.get('income') and result['income'].get('value')
},
'income_year_to_date': {
'table': result.get('income_year_to_date') and \
table({
'head': 'Income Year To Date',
'body': currency_amount_str \
% (currency, fmt_money(result['income_year_to_date'].get('value')))
}),
'idx': 303,
'value': result.get('income_year_to_date') and \
result['income_year_to_date'].get('value')
},
'expenses_booked': {
'table': result.get('expenses_booked') and \
table({
'head': 'Expenses Booked',
'body': currency_amount_str \
% (currency, fmt_money(result['expenses_booked'].get('value')))
}),
'idx': 202,
'value': result.get('expenses_booked') and result['expenses_booked'].get('value')
},
'bank_balance': {
'table': result.get('bank_balance') and \
table({
'head': 'Bank / Cash Balance',
'body': [(bank['name'] + ": <span style='font-size: 110%%; font-weight: bold;'>" \
+ currency_amount_str % \
(currency, fmt_money(bank.get('value'))) + '</span>')
for bank in (isinstance(result['bank_balance'], list) and \
result['bank_balance'] or \
[result['bank_balance']])
]
}),
'idx': 0,
'value': 0.1
},
'new_leads': {
'table': result.get('new_leads') and \
table({
'head': 'New Leads',
'body': '%s' % result['new_leads'].get('count')
}),
'idx': 100,
'value': result.get('new_leads') and result['new_leads'].get('count')
},
'new_enquiries': {
'table': result.get('new_enquiries') and \
table({
'head': 'New Enquiries',
'body': '%s' % result['new_enquiries'].get('count')
}),
'idx': 101,
'value': result.get('new_enquiries') and result['new_enquiries'].get('count')
},
'new_quotations': {
'table': result.get('new_quotations') and \
table({
'head': 'New Quotations',
'body': '%s' % result['new_quotations'].get('count')
}),
'idx': 102,
'value': result.get('new_quotations') and result['new_quotations'].get('count')
},
'new_sales_orders': {
'table': result.get('new_sales_orders') and \
table({
'head': 'New Sales Orders',
'body': '%s' % result['new_sales_orders'].get('count')
}),
'idx': 103,
'value': result.get('new_sales_orders') and result['new_sales_orders'].get('count')
},
'new_purchase_orders': {
'table': result.get('new_purchase_orders') and \
table({
'head': 'New Purchase Orders',
'body': '%s' % result['new_purchase_orders'].get('count')
}),
'idx': 104,
'value': result.get('new_purchase_orders') and \
result['new_purchase_orders'].get('count')
},
'new_transactions': {
'table': result.get('new_transactions') and \
table({
'head': 'New Transactions',
'body': '%s' % result['new_transactions'].get('count')
}),
'idx': 105,
'value': result.get('new_transactions') and result['new_transactions'].get('count')
}
#'stock_below_rl':
}
table_list = []
# Sort these keys depending on idx value
bd_keys = sorted(body_dict, key=lambda x: \
(-webnotes.utils.flt(body_dict[x]['value']), body_dict[x]['idx']))
new_section = False
def set_new_section(new_section):
if not new_section:
table_list.append("<hr /><h4>No Updates For:</h4><br>")
new_section = True
return new_section
for k in bd_keys:
if self.doc.fields[k]:
if k in result:
if not body_dict[k].get('value') and not new_section:
new_section = set_new_section(new_section)
table_list.append(body_dict[k]['table'])
elif k in ['collections', 'payments']:
new_section = set_new_section(new_section)
table_list.append(\
"<p>[" + \
k.capitalize() + \
"]<br />Missing: Account of type 'Bank or Cash'\
</p>")
elif k=='bank_balance':
new_section = set_new_section(new_section)
table_list.append(\
"<p>[" + \
"Bank Balance" + \
"]<br />Alert: GL Entry not found for Account of type 'Bank or Cash'\
</p>")
from webnotes.utils import formatdate
start_date, end_date = self.get_start_end_dates()
digest_daterange = self.doc.frequency=='Daily' \
and formatdate(str(start_date)) \
or (formatdate(str(start_date)) + " to " + (formatdate(str(end_date))))
email_body = """
<h2>%s</h2>
<p style='color: grey'>%s</p>
<h4>%s</h4>
<hr>
""" \
% ((self.doc.frequency + " Digest"), \
digest_daterange, self.doc.company) \
+ "".join(table_list) + """\
<br><p></p>
"""
return email_body
def get_standard_body(self, result):
"""
Generate email body depending on the result
"""
from webnotes.utils import fmt_money
from webnotes.model.doc import Document
company = Document('Company', self.doc.company)
currency = company.default_currency
def table(args):
table_body = ""
if isinstance(args['body'], basestring):
table_body = """\
<tbody><tr>
<td style='padding: 5px; font-size: 24px; \
font-weight: bold; background: #F7F7F5'>""" + \
args['body'] + \
"""\
</td>
</tr></tbody>"""
elif isinstance(args['body'], list):
body_rows = []
for rows in args['body']:
for r in rows:
body_rows.append("""\
<tr>
<td style='padding: 5px; font-size: 24px; \
font-weight: bold; background: #F7F7F5'>""" \
+ r + """\
</td>
</tr>""")
body_rows.append(
"<tr><td style='background: #F7F7F5'><br></td></tr>")
table_body = "<tbody>" + "".join(body_rows) + "</tbody>"
table_head = """\
<thead><tr>
<td style='padding: 5px; background: #D8D8D4; font-size: 16px; font-weight: bold'>""" \
+ args['head'] + """\
</td>
</tr></thead>"""
return "<table style='border-collapse: collapse; width: 100%;'>" \
+ table_head \
+ table_body \
+ "</table>"
currency_amount_str = "<span style='color: grey; font-size: 12px'>%s</span> %s"
body_dict = {
'invoiced_amount': {
'table': result.get('invoiced_amount') and \
table({
'head': 'Invoiced Amount',
'body': currency_amount_str \
% (currency, fmt_money(result['invoiced_amount'].get('debit')))
}),
'idx': 300,
'value': result.get('invoiced_amount') and result['invoiced_amount'].get('debit')
},
'payables': {
'table': result.get('payables') and \
table({
'head': 'Payables',
'body': currency_amount_str \
% (currency, fmt_money(result['payables'].get('credit')))
}),
'idx': 200,
'value': result.get('payables') and result['payables'].get('credit')
},
'collections': {
'table': result.get('collections') and \
table({
'head': 'Collections',
'body': currency_amount_str \
% (currency, fmt_money(result['collections'].get('credit')))
}),
'idx': 301,
'value': result.get('collections') and result['collections'].get('credit')
},
'payments': {
'table': result.get('payments') and \
table({
'head': 'Payments',
'body': currency_amount_str \
% (currency, fmt_money(result['payments'].get('debit')))
}),
'idx': 201,
'value': result.get('payments') and result['payments'].get('debit')
},
'income': {
'table': result.get('income') and \
table({
'head': 'Income',
'body': currency_amount_str \
% (currency, fmt_money(result['income'].get('value')))
}),
'idx': 302,
'value': result.get('income') and result['income'].get('value')
},
'income_year_to_date': {
'table': result.get('income_year_to_date') and \
table({
'head': 'Income Year To Date',
'body': currency_amount_str \
% (currency, fmt_money(result['income_year_to_date'].get('value')))
}),
'idx': 303,
'value': result.get('income_year_to_date') and \
result['income_year_to_date'].get('value')
},
'expenses_booked': {
'table': result.get('expenses_booked') and \
table({
'head': 'Expenses Booked',
'body': currency_amount_str \
% (currency, fmt_money(result['expenses_booked'].get('value')))
}),
'idx': 202,
'value': result.get('expenses_booked') and result['expenses_booked'].get('value')
},
'bank_balance': {
'table': result.get('bank_balance') and \
table({
'head': 'Bank Balance',
'body': [
[
"<span style='font-size: 16px; font-weight: normal'>%s</span>" \
% bank['name'],
currency_amount_str % (currency, fmt_money(bank.get('value')))
] for bank in (isinstance(result['bank_balance'], list) and \
result['bank_balance'] or \
[result['bank_balance']])
]
}),
'idx': 0,
'value': 0.1
},
'new_leads': {
'table': result.get('new_leads') and \
table({
'head': 'New Leads',
'body': '%s' % result['new_leads'].get('count')
}),
'idx': 100,
'value': result.get('new_leads') and result['new_leads'].get('count')
},
'new_enquiries': {
'table': result.get('new_enquiries') and \
table({
'head': 'New Enquiries',
'body': '%s' % result['new_enquiries'].get('count')
}),
'idx': 101,
'value': result.get('new_enquiries') and result['new_enquiries'].get('count')
},
'new_quotations': {
'table': result.get('new_quotations') and \
table({
'head': 'New Quotations',
'body': '%s' % result['new_quotations'].get('count')
}),
'idx': 102,
'value': result.get('new_quotations') and result['new_quotations'].get('count')
},
'new_sales_orders': {
'table': result.get('new_sales_orders') and \
table({
'head': 'New Sales Orders',
'body': '%s' % result['new_sales_orders'].get('count')
}),
'idx': 103,
'value': result.get('new_sales_orders') and result['new_sales_orders'].get('count')
},
'new_purchase_orders': {
'table': result.get('new_purchase_orders') and \
table({
'head': 'New Purchase Orders',
'body': '%s' % result['new_purchase_orders'].get('count')
}),
'idx': 104,
'value': result.get('new_purchase_orders') and \
result['new_purchase_orders'].get('count')
},
'new_transactions': {
'table': result.get('new_transactions') and \
table({
'head': 'New Transactions',
'body': '%s' % result['new_transactions'].get('count')
}),
'idx': 105,
'value': result.get('new_transactions') and result['new_transactions'].get('count')
}
#'stock_below_rl':
}
table_list = []
# Sort these keys depending on idx value
bd_keys = sorted(body_dict, key=lambda x: \
(-webnotes.utils.flt(body_dict[x]['value']), body_dict[x]['idx']))
new_section = False
for k in bd_keys:
if self.doc.fields[k]:
if k in result:
if not body_dict[k].get('value') and not new_section:
if len(table_list) % 2 != 0:
table_list.append("")
table_list.append("<hr />")
table_list.append("<hr />")
new_section = True
table_list.append(body_dict[k]['table'])
elif k in ['collections', 'payments']:
table_list.append(\
"<div style='font-size: 16px; color: grey'>[" + \
k.capitalize() + \
"]<br />Missing: Account of type 'Bank or Cash'\
</div>" )
elif k == 'bank_balance':
table_list.append(\
"<div style='font-size: 16px; color: grey'>[" + \
"Bank Balance" + \
"]<br />Alert: GL Entry not found for Account of type 'Bank or Cash'\
</div>" )
i = 0
result = []
op_len = len(table_list)
while (True):
if i >= op_len:
break
elif (op_len - i) == 1:
result.append("""\
<tr>
<td style='width: 50%%; vertical-align: top;'>%s</td>
<td></td>
</tr>""" % (table_list[i]))
else:
result.append("""\
<tr>
<td style='width: 50%%; vertical-align: top;'>%s</td>
<td>%s</td>
</tr>""" % (table_list[i], table_list[i + 1]))
i = i + 2
from webnotes.utils import formatdate
start_date, end_date = self.get_start_end_dates()
digest_daterange = self.doc.frequency=='Daily' \
and formatdate(str(start_date)) \
or (formatdate(str(start_date)) + " to " + (formatdate(str(end_date))))
email_body = """
<div style='width: 100%%'>
<div style='padding: 10px; margin: auto; text-align: center; line-height: 80%%'>
<p style='font-weight: bold; font-size: 24px'>%s</p>
<p style='font-size: 16px; color: grey'>%s</p>
<p style='font-size: 20px; font-weight: bold'>%s</p>
</div>
<table cellspacing=15 style='width: 100%%'>""" \
% ((self.doc.frequency + " Digest"), \
digest_daterange, self.doc.company) \
+ "".join(result) + """\
</table><br><p></p>
</div>"""
return email_body
class DocType(DocListController):
def __init__(self, d, dl):
self.doc, self.doclist = d, dl
def validate(self):
self.validate_value("calculate_based_on", "in",
["Net Total", "Net Weight"])
self.shipping_rule_conditions = self.doclist.get(
{"parentfield": "shipping_rule_conditions"})
self.validate_from_to_values()
self.sort_shipping_rule_conditions()
self.validate_overlapping_shipping_rule_conditions()
def validate_from_to_values(self):
zero_to_values = []
for d in self.shipping_rule_conditions:
self.round_floats_in(d)
# values cannot be negative
self.validate_value("from_value", ">=", 0.0, d)
self.validate_value("to_value", ">=", 0.0, d)
if d.to_value == 0:
zero_to_values.append(d)
elif d.from_value >= d.to_value:
msgprint(_("Error") + ": " + _("Row") + " # %d: " % d.idx +
_("From Value should be less than To Value"),
raise_exception=FromGreaterThanToError)
# check if more than two or more rows has To Value = 0
if len(zero_to_values) >= 2:
msgprint(_(
'''There can only be one Shipping Rule Condition with 0 or blank value for "To Value"'''
),
raise_exception=ManyBlankToValuesError)
def sort_shipping_rule_conditions(self):
"""Sort Shipping Rule Conditions based on increasing From Value"""
self.shipping_rules_conditions = sorted(
self.shipping_rule_conditions, key=lambda d: flt(d.from_value))
for i, d in enumerate(self.shipping_rule_conditions):
d.idx = i + 1
def validate_overlapping_shipping_rule_conditions(self):
def overlap_exists_between((x1, x2), (y1, y2)):
"""
(x1, x2) and (y1, y2) are two ranges
if condition x = 100 to 300
then condition y can only be like 50 to 99 or 301 to 400
hence, non-overlapping condition = (x1 <= x2 < y1 <= y2) or (y1 <= y2 < x1 <= x2)
"""
separate = (x1 <= x2 <= y1 <= y2) or (y1 <= y2 <= x1 <= x2)
return (not separate)
overlaps = []
for i in xrange(0, len(self.shipping_rule_conditions)):
for j in xrange(i + 1, len(self.shipping_rule_conditions)):
d1, d2 = self.shipping_rule_conditions[
i], self.shipping_rule_conditions[j]
if d1.fields != d2.fields:
# in our case, to_value can be zero, hence pass the from_value if so
range_a = (d1.from_value, d1.to_value or d1.from_value)
range_b = (d2.from_value, d2.to_value or d2.from_value)
if overlap_exists_between(range_a, range_b):
overlaps.append([d1, d2])
if overlaps:
company_currency = get_company_currency(self.doc.company)
msgprint(
_("Error") + ": " + _("Overlapping Conditions found between") +
":")
messages = []
for d1, d2 in overlaps:
messages.append(
"%s-%s = %s " %
(d1.from_value, d1.to_value,
fmt_money(d1.shipping_amount, currency=company_currency))
+ _("and") + " %s-%s = %s" %
(d2.from_value, d2.to_value,
fmt_money(d2.shipping_amount, currency=company_currency)))
msgprint("\n".join(messages),
raise_exception=OverlappingConditionError)
def get_bal(self,arg):
msgprint(arg)
bal = sql("select balance,debit_or_credit from tabAccount where name=%s", arg)
msgprint(bal)
return fmt_money(flt(bal[0][0])) + ' ' + bal[0][1]
def get_standard_body(self, result):
"""
Generate email body depending on the result
"""
from webnotes.utils import fmt_money
from webnotes.model.doc import Document
company = Document('Company', self.doc.company)
currency = company.default_currency
def table(args):
table_body = ""
if isinstance(args['body'], basestring):
return """<p>%(head)s: <span style='font-size: 110%%; font-weight: bold;'>%(body)s</span></p>""" % args
else:
return ("""<p>%(head)s:</p> """ % args) +\
"".join(map(lambda b: "<p style='margin-left: 17px;'>%s</p>" % b, args['body']))
currency_amount_str = "<span style='color: grey;'>%s</span> %s"
body_dict = {
'invoiced_amount': {
'table': result.get('invoiced_amount') and \
table({
'head': 'Invoiced Amount',
'body': currency_amount_str \
% (currency, fmt_money(result['invoiced_amount'].get('debit')))
}),
'idx': 300,
'value': result.get('invoiced_amount') and result['invoiced_amount'].get('debit')
},
'payables': {
'table': result.get('payables') and \
table({
'head': 'Payables',
'body': currency_amount_str \
% (currency, fmt_money(result['payables'].get('credit')))
}),
'idx': 200,
'value': result.get('payables') and result['payables'].get('credit')
},
'collections': {
'table': result.get('collections') and \
table({
'head': 'Collections',
'body': currency_amount_str \
% (currency, fmt_money(result['collections'].get('credit')))
}),
'idx': 301,
'value': result.get('collections') and result['collections'].get('credit')
},
'payments': {
'table': result.get('payments') and \
table({
'head': 'Payments',
'body': currency_amount_str \
% (currency, fmt_money(result['payments'].get('debit')))
}),
'idx': 201,
'value': result.get('payments') and result['payments'].get('debit')
},
'income': {
'table': result.get('income') and \
table({
'head': 'Income',
'body': currency_amount_str \
% (currency, fmt_money(result['income'].get('value')))
}),
'idx': 302,
'value': result.get('income') and result['income'].get('value')
},
'income_year_to_date': {
'table': result.get('income_year_to_date') and \
table({
'head': 'Income Year To Date',
'body': currency_amount_str \
% (currency, fmt_money(result['income_year_to_date'].get('value')))
}),
'idx': 303,
'value': result.get('income_year_to_date') and \
result['income_year_to_date'].get('value')
},
'expenses_booked': {
'table': result.get('expenses_booked') and \
table({
'head': 'Expenses Booked',
'body': currency_amount_str \
% (currency, fmt_money(result['expenses_booked'].get('value')))
}),
'idx': 202,
'value': result.get('expenses_booked') and result['expenses_booked'].get('value')
},
'bank_balance': {
'table': result.get('bank_balance') and \
table({
'head': 'Bank / Cash Balance',
'body': [(bank['name'] + ": <span style='font-size: 110%%; font-weight: bold;'>" \
+ currency_amount_str % \
(currency, fmt_money(bank.get('value'))) + '</span>')
for bank in (isinstance(result['bank_balance'], list) and \
result['bank_balance'] or \
[result['bank_balance']])
]
}),
'idx': 0,
'value': 0.1
},
'new_leads': {
'table': result.get('new_leads') and \
table({
'head': 'New Leads',
'body': '%s' % result['new_leads'].get('count')
}),
'idx': 100,
'value': result.get('new_leads') and result['new_leads'].get('count')
},
'new_enquiries': {
'table': result.get('new_enquiries') and \
table({
'head': 'New Enquiries',
'body': '%s' % result['new_enquiries'].get('count')
}),
'idx': 101,
'value': result.get('new_enquiries') and result['new_enquiries'].get('count')
},
'new_quotations': {
'table': result.get('new_quotations') and \
table({
'head': 'New Quotations',
'body': '%s' % result['new_quotations'].get('count')
}),
'idx': 102,
'value': result.get('new_quotations') and result['new_quotations'].get('count')
},
'new_sales_orders': {
'table': result.get('new_sales_orders') and \
table({
'head': 'New Sales Orders',
'body': '%s' % result['new_sales_orders'].get('count')
}),
'idx': 103,
'value': result.get('new_sales_orders') and result['new_sales_orders'].get('count')
},
'new_purchase_orders': {
'table': result.get('new_purchase_orders') and \
table({
'head': 'New Purchase Orders',
'body': '%s' % result['new_purchase_orders'].get('count')
}),
'idx': 104,
'value': result.get('new_purchase_orders') and \
result['new_purchase_orders'].get('count')
},
'new_transactions': {
'table': result.get('new_transactions') and \
table({
'head': 'New Transactions',
'body': '%s' % result['new_transactions'].get('count')
}),
'idx': 105,
'value': result.get('new_transactions') and result['new_transactions'].get('count')
}
#'stock_below_rl':
}
table_list = []
# Sort these keys depending on idx value
bd_keys = sorted(body_dict, key=lambda x: \
(-webnotes.utils.flt(body_dict[x]['value']), body_dict[x]['idx']))
new_section = False
def set_new_section(new_section):
if not new_section:
table_list.append("<hr /><h4>No Updates For:</h4><br>")
new_section = True
return new_section
for k in bd_keys:
if self.doc.fields[k]:
if k in result:
if not body_dict[k].get('value') and not new_section:
new_section = set_new_section(new_section)
table_list.append(body_dict[k]['table'])
elif k in ['collections', 'payments']:
new_section = set_new_section(new_section)
table_list.append(\
"<p>[" + \
k.capitalize() + \
"]<br />Missing: Account of type 'Bank or Cash'\
</p>" )
elif k == 'bank_balance':
new_section = set_new_section(new_section)
table_list.append(\
"<p>[" + \
"Bank Balance" + \
"]<br />Alert: GL Entry not found for Account of type 'Bank or Cash'\
</p>" )
from webnotes.utils import formatdate
start_date, end_date = self.get_start_end_dates()
digest_daterange = self.doc.frequency=='Daily' \
and formatdate(str(start_date)) \
or (formatdate(str(start_date)) + " to " + (formatdate(str(end_date))))
email_body = """
<h2>%s</h2>
<p style='color: grey'>%s</p>
<h4>%s</h4>
<hr>
""" \
% ((self.doc.frequency + " Digest"), \
digest_daterange, self.doc.company) \
+ "".join(table_list) + """\
<br><p></p>
"""
return email_body
def get_bal(self,arg):
bal = 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 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]
