def validate_block_days(self):
from_date = getdate(self.doc.from_date)
to_date = getdate(self.doc.to_date)
department = webnotes.conn.get_value("Employee", self.doc.employee, "department")
if department:
block_list = webnotes.conn.get_value("Department", department, "holiday_block_list")
if block_list:
if self.is_user_in_allow_list(block_list):
return
for d in webnotes.conn.sql(
"""select block_date, reason from
`tabHoliday Block List Date` where parent=%s""",
block_list,
as_dict=1,
):
block_date = getdate(d.block_date)
if block_date > from_date and block_date < to_date:
webnotes.msgprint(
_("You cannot apply for a leave on the following date because it is blocked")
+ ": "
+ formatdate(d.block_date)
+ _(" Reason: ")
+ d.reason
)
raise LeaveDayBlockedError
def get_last_purchase_details(item_code, doc_name=None, conversion_rate=1.0):
"""returns last purchase details in stock uom"""
# get last purchase order item details
last_purchase_order = webnotes.conn.sql("""\
select po.name, po.transaction_date, po.conversion_rate,
po_item.conversion_factor, po_item.purchase_ref_rate,
po_item.discount_rate, po_item.purchase_rate
from `tabPurchase Order` po, `tabPurchase Order Item` po_item
where po.docstatus = 1 and po_item.item_code = %s and po.name != %s and
po.name = po_item.parent
order by po.transaction_date desc, po.name desc
limit 1""", (item_code, cstr(doc_name)), as_dict=1)
# get last purchase receipt item details
last_purchase_receipt = webnotes.conn.sql("""\
select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
pr_item.conversion_factor, pr_item.purchase_ref_rate, pr_item.discount_rate,
pr_item.purchase_rate
from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
where pr.docstatus = 1 and pr_item.item_code = %s and pr.name != %s and
pr.name = pr_item.parent
order by pr.posting_date desc, pr.posting_time desc, pr.name desc
limit 1""", (item_code, cstr(doc_name)), as_dict=1)
purchase_order_date = getdate(last_purchase_order and last_purchase_order[0].transaction_date \
or "1900-01-01")
purchase_receipt_date = getdate(last_purchase_receipt and \
last_purchase_receipt[0].posting_date or "1900-01-01")
if (purchase_order_date > purchase_receipt_date) or \
(last_purchase_order and not last_purchase_receipt):
# use purchase order
last_purchase = last_purchase_order[0]
purchase_date = purchase_order_date
elif (purchase_receipt_date > purchase_order_date) or \
(last_purchase_receipt and not last_purchase_order):
# use purchase receipt
last_purchase = last_purchase_receipt[0]
purchase_date = purchase_receipt_date
else:
return webnotes._dict()
conversion_factor = flt(last_purchase.conversion_factor)
out = webnotes._dict({
"purchase_ref_rate": flt(last_purchase.purchase_ref_rate) / conversion_factor,
"purchase_rate": flt(last_purchase.purchase_rate) / conversion_factor,
"discount_rate": flt(last_purchase.discount_rate),
"purchase_date": purchase_date
})
conversion_rate = flt(conversion_rate) or 1.0
out.update({
"import_ref_rate": out.purchase_ref_rate / conversion_rate,
"import_rate": out.purchase_rate / conversion_rate,
"rate": out.purchase_rate
})
return out
def send_notification(new_rv):
"""Notify concerned persons about recurring invoice generation"""
subject = "Invoice : " + new_rv.doc.name
com = new_rv.doc.company # webnotes.conn.get_value('Control Panel', '', 'letter_head')
hd = '''<div><h2>%s</h2></div>
<div><h3>Invoice: %s</h3></div>
<table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"><b>Customer</b><br>%s<br>%s</td>
<td width = "50%%">Invoice Date : %s<br>Invoice Period : %s to %s <br>Due Date : %s</td>
</tr>
</table>
''' % (com, new_rv.doc.name, new_rv.doc.customer_name, new_rv.doc.address_display, getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"), \
getdate(new_rv.doc.invoice_period_from_date).strftime("%d-%m-%Y"), getdate(new_rv.doc.invoice_period_to_date).strftime("%d-%m-%Y"),\
getdate(new_rv.doc.due_date).strftime("%d-%m-%Y"))
tbl = '''<table border="1px solid #CCC" width="100%%" cellpadding="0px" cellspacing="0px">
<tr>
<td width = "15%%" bgcolor="#CCC" align="left"><b>Item</b></td>
<td width = "40%%" bgcolor="#CCC" align="left"><b>Description</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Qty</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Rate</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Amount</b></td>
</tr>
'''
for d in getlist(new_rv.doclist, 'entries'):
tbl += '<tr><td>' + d.item_code +'</td><td>' + d.description+'</td><td>' + cstr(d.qty) +'</td><td>' + cstr(d.basic_rate) +'</td><td>' + cstr(d.amount) +'</td></tr>'
tbl += '</table>'
totals ='''<table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"></td>
<td width = "50%%">
<table width = "100%%">
<tr>
<td width = "50%%">Net Total: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Total Tax: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Grand Total: </td><td>%s</td>
</tr><tr>
<td width = "50%%">In Words: </td><td>%s</td>
</tr>
</table>
</td>
</tr>
<tr><td>Terms and Conditions:</td></tr>
<tr><td>%s</td></tr>
</table>
''' % (new_rv.doc.net_total,
new_rv.doc.other_charges_total,new_rv.doc.grand_total,
new_rv.doc.in_words,new_rv.doc.terms)
msg = hd + tbl + totals
sendmail(new_rv.doc.notification_email_address, subject=subject, msg = msg)
def check_freezing_date(self, adv_adj):
if not adv_adj:
acc_frozen_upto = get_value('Global Defaults', None, 'acc_frozen_upto')
if acc_frozen_upto:
bde_auth_role = get_value( 'Global Defaults', None,'bde_auth_role')
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)
def set_next_date(self):
""" Set next date on which auto invoice will be created"""
if not self.doc.repeat_on_day_of_month:
msgprint(
"""Please enter 'Repeat on Day of Month' field value. \nThe day of the month on which auto invoice
will be generated e.g. 05, 28 etc.""",
raise_exception=1,
)
import datetime
mcount = {"Monthly": 1, "Quarterly": 3, "Half-yearly": 6, "Yearly": 12}
m = getdate(self.doc.posting_date).month + mcount[self.doc.recurring_type]
y = getdate(self.doc.posting_date).year
if m > 12:
m, y = m - 12, y + 1
try:
next_date = datetime.date(y, m, cint(self.doc.repeat_on_day_of_month))
except:
import calendar
last_day = calendar.monthrange(y, m)[1]
next_date = datetime.date(y, m, last_day)
next_date = next_date.strftime("%Y-%m-%d")
webnotes.conn.set(self.doc, "next_date", next_date)
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)
else:
# get balance of all entries that exist
date = nowdate()
try:
year_start_date = get_fiscal_year(date, verbose=0)[1]
except FiscalYearError, e:
from webnotes.utils import getdate
if getdate(date) > getdate(nowdate()):
# if fiscal year not found and the date is greater than today
# get fiscal year for today's date and its corresponding year start date
year_start_date = get_fiscal_year(nowdate(), verbose=1)[1]
else:
# this indicates that it is a date older than any existing fiscal year.
# hence, assuming balance as 0.0
return 0.0
def get_fy_details(fy_start_date, fy_end_date): start_year = getdate(fy_start_date).year if start_year == getdate(fy_end_date).year: fy = cstr(start_year) else: fy = cstr(start_year) + '-' + cstr(start_year + 1) return fy
def execute(filters=None):
if not filters: filters = {}
float_preceision = webnotes.conn.get_default("float_preceision")
condition =get_condition(filters)
avg_daily_outgoing = 0
diff = ((getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days)+1
if diff <= 0:
webnotes.msgprint("To Date should not be less than eual to From Date",raise_exception=1)
columns = get_columns()
items = get_item_info()
consumed_item_map = get_consumed_items(condition)
delivered_item_map = get_delivered_items(condition)
data = []
for item in items:
total_outgoing = consumed_item_map.get(item.name, 0)+delivered_item_map.get(item.name,0)
avg_daily_outgoing = flt(total_outgoing/diff, float_preceision)
reorder_level = (avg_daily_outgoing * flt(item.lead_time_days)) + flt(item.min_order_qty)
data.append([item.name, item.item_name, item.description, item.min_order_qty, item.lead_time_days,
consumed_item_map.get(item.name, 0), delivered_item_map.get(item.name,0), total_outgoing,
avg_daily_outgoing, reorder_level])
return columns , data
def get_period_date_ranges(period, fiscal_year=None, year_start_date=None):
from dateutil.relativedelta import relativedelta
if not year_start_date:
year_start_date, year_end_date = webnotes.conn.get_value("Fiscal Year",
fiscal_year, ["year_start_date", "year_end_date"])
increment = {
"Monthly": 1,
"Quarterly": 3,
"Half-Yearly": 6,
"Yearly": 12
}.get(period)
period_date_ranges = []
for i in xrange(1, 13, increment):
period_end_date = getdate(year_start_date) + relativedelta(months=increment, days=-1)
if period_end_date > getdate(year_end_date):
period_end_date = year_end_date
period_date_ranges.append([year_start_date, period_end_date])
year_start_date = period_end_date + relativedelta(days=1)
if period_end_date == year_end_date:
break
return period_date_ranges
def send_daily_summary(for_date=None, event_date=None):
if not for_date:
for_date = add_days(today(), days=-1)
if not event_date:
event_date = today()
formatted_date = getdate(for_date).strftime("%a, %d %B, %Y")
formatted_event_date = getdate(event_date).strftime("%a, %d %B, %Y")
subject = "[AAP Ka Manch] Updates for {formatted_date}".format(formatted_date=formatted_date)
unit_post_map = get_unit_post_map(for_date, event_date)
if not unit_post_map:
# no updates!
return
for user in webnotes.conn.sql_list("""select name from `tabProfile`
where user_type='Website User' and enabled=1 and name not in ('Administrator', 'Guest')"""):
summary = prepare_daily_summary(user, unit_post_map, {"subject": subject, "formatted_date": formatted_date,
"formatted_event_date": formatted_event_date})
if not summary:
# no access!
continue
send(recipients=[user],
subject=subject,
message=summary,
# to allow unsubscribe
doctype='Profile',
email_field='name',
# for tracking sent status
ref_doctype="Profile", ref_docname=user)
def get_weekly_off_date_list(self, year_start_date, year_end_date):
from webnotes.utils import getdate
year_start_date, year_end_date = getdate(year_start_date), getdate(year_end_date)
from dateutil import relativedelta
from datetime import timedelta
import calendar
date_list = []
date_list1 = []
if self.doc.weekly_off=='3rd Saturday':
webnotes.errprint(self.doc.weekly_off)
weekday = getattr(calendar, ('Saturday').upper())
reference_date = year_start_date + relativedelta.relativedelta(weekday=weekday)
while reference_date <= year_end_date:
date_list1.append(reference_date)
reference_date += timedelta(days=7)
for dt in date_list1:
if dt.day>14 :
if dt.day <22:
#webnotes.errprint(dt)
ch = addchild(self.doc, 'holiday_list_details', 'Holiday', self.doclist)
ch.description = self.doc.weekly_off
ch.holiday_date = dt
return date_list
else:
weekday = getattr(calendar, (self.doc.weekly_off).upper())
reference_date = year_start_date + relativedelta.relativedelta(weekday=weekday)
while reference_date <= year_end_date:
date_list.append(reference_date)
reference_date += timedelta(days=7)
return date_list
def update_production_order(self, is_submit):
if self.doc.production_order:
pro_obj = get_obj("Production Order", self.doc.production_order)
if flt(pro_obj.doc.docstatus) != 1:
msgprint("""You cannot do any transaction against
Production Order : %s, as it's not submitted"""
% (pro_obj.doc.name), raise_exception=1)
if pro_obj.doc.status == 'Stopped':
msgprint("""You cannot do any transaction against Production Order : %s,
as it's status is 'Stopped'"""% (pro_obj.doc.name), raise_exception=1)
if getdate(pro_obj.doc.posting_date) > getdate(self.doc.posting_date):
msgprint("""Posting Date of Stock Entry cannot be before Posting Date of
Production Order: %s"""% cstr(self.doc.production_order), raise_exception=1)
if self.doc.process == 'Backflush':
pro_obj.doc.produced_qty = flt(pro_obj.doc.produced_qty) + \
(is_submit and 1 or -1 ) * flt(self.doc.fg_completed_qty)
args = {
"item_code": pro_obj.doc.production_item,
"posting_date": self.doc.posting_date,
"planned_qty": (is_submit and -1 or 1 ) * flt(self.doc.fg_completed_qty)
}
get_obj('Warehouse', pro_obj.doc.fg_warehouse).update_bin(args)
pro_obj.doc.status = (flt(pro_obj.doc.qty)==flt(pro_obj.doc.produced_qty)) \
and 'Completed' or 'In Process'
pro_obj.doc.save()
def set_last_contact_date(self):
if self.doc.contact_date_ref and self.doc.contact_date_ref != self.doc.contact_date:
if getdate(self.doc.contact_date_ref) < getdate(self.doc.contact_date):
self.doc.last_contact_date=self.doc.contact_date_ref
else:
msgprint("Contact Date Cannot be before Last Contact Date")
raise Exception
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
def validate_warranty_status(self):
if self.doc.warranty_expiry_date and getdate(self.doc.warranty_expiry_date) >= datetime.date.today() and self.doc.maintenance_status == 'Out of Warranty':
msgprint("Warranty expiry date and maintenance status mismatch. Please verify")
raise Exception
elif (not self.doc.warranty_expiry_date or getdate(self.doc.warranty_expiry_date) < datetime.date.today()) and self.doc.maintenance_status == 'Under Warranty':
msgprint("Warranty expiry date and maintenance status mismatch. Please verify")
raise Exception
def __init__(self, company, start, end, interval):
from webnotes.utils import getdate
from webnotes.model.code import get_obj
import webnotes
self.company = company
self.abbr = webnotes.conn.get_value("Company", company, "abbr")
self.start = getdate(start)
self.end = getdate(end)
self.interval = interval
self.glc = get_obj("GL Control")
self.cash_accounts = [
d[0]
for d in webnotes.conn.sql(
"""
select name from tabAccount
where account_type='Bank or Cash'
and company = %s and docstatus = 0
""",
company,
)
]
self.receivables_group = webnotes.conn.get_value("Company", company, "receivables_group")
self.payables_group = webnotes.conn.get_value("Company", company, "payables_group")
# list of bank and cash accounts
self.bc_list = [
s[0] for s in webnotes.conn.sql("select name from tabAccount where account_type='Bank or Cash'")
]
def validate_amc_status(self):
if self.doc.amc_expiry_date and getdate(self.doc.amc_expiry_date) >= datetime.date.today() and self.doc.maintenance_status == 'Out of AMC':
msgprint("AMC expiry date and maintenance status mismatch. Please verify")
raise Exception
elif (not self.doc.amc_expiry_date or getdate(self.doc.amc_expiry_date) < datetime.date.today()) and self.doc.maintenance_status == 'Under AMC':
msgprint("AMC expiry date and maintenance status mismatch. Please verify")
raise Exception
def get_loan_details(self):
end= self.doc.doi_closing
list1 = []
#list1.append(self.doc.doi_start)
#webnotes.errprint(start)
#webnotes.errprint(number )
#webnotes.errprint((cstr(self.doc.doi_start) + datetime.timedelta(12*365/12)).isoformat())
#self.doc.doi_closing=(self.doc.doi_start + datetime.timedelta(12*365/12)).isformat()
#webnotes.errprint(self.doc.doi_closing)
#j=0
j=self.doc.number_of_installments
dt=self.doc.doi_start
for j in range(0,j):
date=add_months(getdate(dt),1)
#ebnotes.errprint(["j",date])
#ebnotes.errprint(["hii",end])
if date<=getdate(end):
list1.append(date)
dt=date
#webnotes.errprint(date)
#ebnotes.errprint(list1)
self.doclist=self.doc.clear_table(self.doclist,'installment')
for i in list1:
#ebnotes.errprint("in for loop")
#self.doclist=self.doc.clear_table(self.doclist,'installment')
ch = addchild(self.doc, 'installment',
'Loan Installment Details', self.doclist)
ch.date_of_installment = i
ch.amount_to_be_paid =self.doc.amount_per_month
ch.status='Unpaid'
def validate_with_timesheet_dates(self):
chk = sql(
"select t1.name, t1.timesheet_date from `tabTimesheet` t1, `tabTimesheet Detail` t2 where t2.parent = t1.name and t2.task_id = %s and t1.status = 'Submitted' order by t1.timesheet_date asc",
self.doc.name,
as_dict=1,
)
if chk:
if self.doc.act_start_date:
if chk[0]["timesheet_date"] > getdate(self.doc.act_start_date) or chk[0]["timesheet_date"] < getdate(
self.doc.act_start_date
):
msgprint(
"Actual start date of this task is "
+ cstr(chk[0]["timesheet_date"])
+ " as per timesheet "
+ cstr(chk[0]["name"])
)
raise Exception
else:
self.doc.act_start_date = chk[0]["timesheet_date"]
if self.doc.act_end_date:
if chk[len(chk) - 1]["timesheet_date"] < getdate(self.doc.act_end_date) or chk[len(chk) - 1][
"timesheet_date"
] > getdate(self.doc.act_end_date):
msgprint(
"Actual end date of this task is "
+ cstr(chk[len(chk) - 1]["timesheet_date"])
+ " as per timesheet "
+ cstr(chk[len(chk) - 1]["name"])
)
raise Exception
else:
self.doc.act_end_date = chk[len(chk) - 1]["timesheet_date"]
def validate_posting_date(self):
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 ""
# Posting Date should be within closing year
if getdate(self.doc.posting_date) < getdate(self.year_start_date) or getdate(self.doc.posting_date) > getdate(
self.year_end_date
):
msgprint("Posting Date should be within Closing Fiscal Year")
raise Exception
# Period Closing Entry
pce = sql(
"select name from `tabPeriod Closing Voucher` \
where posting_date > '%s' and fiscal_year = '%s' and docstatus = 1"
% (self.doc.posting_date, self.doc.fiscal_year)
)
if pce and pce[0][0]:
msgprint(
"Another Period Closing Entry: %s has been made after posting date: %s"
% (cstr(pce[0][0]), self.doc.posting_date)
)
raise Exception
def validate(self):
if getdate(self.doc.timesheet_date) > getdate(nowdate()):
msgprint("You can not prepare timesheet for future date")
raise Exception
chk = sql(
"select name from `tabTimesheet` where timesheet_date=%s and owner=%s and status!='Cancelled' and name!=%s",
(self.doc.timesheet_date, self.doc.owner, self.doc.name),
)
if chk:
msgprint("You have already created timesheet " + cstr(chk and chk[0][0] or "") + " for this date.")
raise Exception
import time
for d in getlist(self.doclist, "timesheet_details"):
if d.act_start_time and d.act_end_time:
d1 = time.strptime(d.act_start_time, "%H:%M")
d2 = time.strptime(d.act_end_time, "%H:%M")
if d1 > d2:
msgprint("Start time can not be greater than end time. Check for Task Id : " + cstr(d.task_id))
raise Exception
elif d1 == d2:
msgprint("Start time and end time can not be same. Check for Task Id : " + cstr(d.task_id))
raise Exception
def execute(filters=None):
priority_map = {"High": 3, "Medium": 2, "Low": 1}
todo_list = runreport(doctype="ToDo", fields=["name", "date", "description",
"priority", "reference_type", "reference_name", "assigned_by", "owner"],
filters=[["ToDo", "checked", "!=", 1]])
todo_list.sort(key=lambda todo: (priority_map.get(todo.priority, 0),
todo.date and getdate(todo.date) or getdate("1900-01-01")), reverse=True)
columns = [_("ID")+":Link/ToDo:90", _("Priority")+"::60", _("Date")+ ":Date",
_("Description")+"::150", _("Assigned To/Owner") + ":Link/Profile:120",
_("Assigned By")+":Link/Profile:120", _("Reference")+"::200"]
result = []
for todo in todo_list:
if todo.reference_type:
todo.reference = """<a href="#Form/%s/%s">%s: %s</a>""" % \
(todo.reference_type, todo.reference_name, todo.reference_type, todo.reference_name)
else:
todo.reference = None
result.append([todo.name, todo.priority, todo.date, todo.description,
todo.owner, todo.assigned_by, todo.reference])
return columns, result
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)
def notify(self):
import datetime
from webnotes.utils import getdate
serial_nos = webnotes.conn.sql(\
"""SELECT name, warranty_expiry_date,\
amc_expiry_date, warranty_amc_status,\
brand, product_code,\
customer, territory\
FROM `tabSerial No`\
WHERE (((DATEDIFF(NOW(),warranty_expiry_date) between -30 and 365) AND ifnull(amc_expiry_date, '') = '')\
OR (DATEDIFF(NOW(), amc_expiry_date) between -30 and 365 AND warranty_amc_status = 'Under AMC'))\
AND warranty_amc_status in ('Under Warranty', 'Under AMC')\
""", as_dict=1)
for s in serial_nos:
status = s['warranty_amc_status']
try:
amc_ex_date = getdate(s['amc_expiry_date'])
except:
amc_ex_date = s['amc_expiry_date']
try:
warranty_ex_date = getdate(s['warranty_expiry_date'])
except:
warranty_ex_date = s['warranty_expiry_date']
days = 1 # intializing to 1 coz it wont be true even if we dont have warranty or expiry date
if status == 'Under Warranty' and warranty_ex_date:
days = (datetime.datetime.now().date() - warranty_ex_date).days
elif status == 'Under AMC' and amc_ex_date:
days = (datetime.datetime.now().date() - amc_ex_date).days
if days % 30 == 0 and s['territory']:
self.send_mail(s, days)
def get_weekly_off_date_list(self, yr_start_date, yr_end_date):
days_dict, dt_list, lst_st = (
{"Monday": 0, "Tuesday": 1, "Wednesday": 2, "Thursday": 3, "Friday": 4, "Saturday": 5, "Sunday": 6},
[],
"",
)
w = cint(days_dict[self.doc.weekly_off]) # Weekly Off Day No.
st_dt_weekday = getdate(yr_start_date).weekday() # Year Start Date weekday()
if w == st_dt_weekday: # Get Start Date
lst_st = yr_start_date
dt_list.append(lst_st)
elif w > st_dt_weekday:
lst_st = add_days(yr_start_date, w - st_dt_weekday)
dt_list.append(lst_st)
else:
lst_st = add_days(yr_start_date, 6 - st_dt_weekday + 1)
dt_list.append(lst_st)
while getdate(lst_st) < getdate(yr_end_date): # Get list of dates
lst_st = add_days(lst_st, 7)
if getdate(lst_st) > getdate(yr_end_date):
break
dt_list.append(lst_st)
return dt_list
def validate(self):
if self.doc.exp_start_date and self.doc.exp_end_date and getdate(self.doc.exp_start_date) > getdate(self.doc.exp_end_date):
msgprint("'Expected Start Date' can not be greater than 'Expected End Date'")
raise Exception
if self.doc.act_start_date and self.doc.act_end_date and getdate(self.doc.act_start_date) > getdate(self.doc.act_end_date):
msgprint("'Actual Start Date' can not be greater than 'Actual End Date'")
raise Exception
def validate_po_date(self):
# validate p.o date v/s delivery date
if self.doc.po_date and self.doc.delivery_date and getdate(self.doc.po_date) > getdate(self.doc.delivery_date):
msgprint("Expected Delivery Date cannot be before Purchase Order Date")
raise Exception
# amendment date is necessary if document is amended
if self.doc.amended_from and not self.doc.amendment_date:
msgprint("Please Enter Amendment Date")
raise Exception
def validate_po_date(self):
# validate p.o date v/s delivery date
if self.doc.po_date and self.doc.delivery_date and getdate(self.doc.po_date) > getdate(self.doc.delivery_date):
msgprint("Expected Delivery Date cannot be before Purchase Order Date")
raise Exception
# amendment date is necessary if document is amended
if self.doc.amended_from and not self.doc.amendment_date:
msgprint("Please Enter Amendment Date")
raise Exception
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
total_invoiced_amount = total_paid = total_outstanding = 0
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
paid_amount = get_paid_amount(gle, filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - paid_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no,
gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no,
bill_date, invoiced_amount, paid_amount, outstanding_amount]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount)
# Add to total
total_invoiced_amount += flt(invoiced_amount)
total_paid += flt(paid_amount)
total_outstanding += flt(outstanding_amount)
data.append(row)
if data:
data.append(["", "", "", "", "", "", "", "Total", "", total_invoiced_amount, total_paid,
total_outstanding, "", "", "", ""])
return columns, data
def validate_to_date(self):
#ebnotes.errprint("in validate")
if getdate(self.doc.loan_sanction_date) < getdate(self.doc.doi_start):
pass
else:
#tdate(self.doc.loan_sanction_date) < getdate(self.doc.doi_start)):
webnotes.msgprint("Date of installament cannot be less than loan sanction date")
raise Exception
def validate(self):
"""validate start date before end date"""
if self.doc.project_start_date and self.doc.completion_date:
if getdate(self.doc.completion_date) < getdate(
self.doc.project_start_date):
msgprint(
"Expected Completion Date can not be less than Project Start Date"
)
raise Exception
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
paid_amount = get_paid_amount(
gle,
filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - paid_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [
gle.posting_date, gle.account, gle.voucher_type,
gle.voucher_no, gle.remarks,
account_supplier_type_map.get(gle.account), due_date,
bill_no, bill_date, invoiced_amount, paid_amount,
outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
def validate_amc_date(self):
for d in getlist(self.doclist,'service_order_details'):
if d.start_date and d.end_date: # in AMC we don't require start_date and end_date
if getdate(d.start_date) >= getdate(d.end_date):
msgprint("End Date must be after Start Date in Item .")
raise Exception
if flt(d.no_of_visit) <= 0:
msgprint("No of visit must be a positive value")
raise Exception
def validate_for_items(self):
check_list, flag = [], 0
chk_dupl_itm = []
# Sales Order Details Validations
for d in getlist(self.doclist, 'sales_order_details'):
if cstr(self.doc.quotation_no) == cstr(d.prevdoc_docname):
flag = 1
if d.prevdoc_docname:
if self.doc.quotation_date and getdate(
self.doc.quotation_date) > getdate(
self.doc.transaction_date):
msgprint(
"Sales Order Date cannot be before Quotation Date")
raise Exception
# validates whether quotation no in doctype and in table is same
if not cstr(d.prevdoc_docname) == cstr(self.doc.quotation_no):
msgprint(
"Items in table does not belong to the Quotation No mentioned."
)
raise Exception
# validates whether item is not entered twice
e = [
d.item_code, d.description, d.reserved_warehouse,
d.prevdoc_docname or ''
]
f = [d.item_code, d.description]
#check item is stock item
st_itm = sql(
"select is_stock_item from `tabItem` where name = '%s'" %
d.item_code)
if st_itm and st_itm[0][0] == 'Yes':
if e in check_list:
msgprint("Item %s has been entered twice." % d.item_code)
else:
check_list.append(e)
elif st_itm and st_itm[0][0] == 'No':
if f in chk_dupl_itm:
msgprint("Item %s has been entered twice." % d.item_code)
else:
chk_dupl_itm.append(f)
# used for production plan
d.transaction_date = self.doc.transaction_date
d.delivery_date = self.doc.delivery_date
# gets total projected qty of item in warehouse selected (this case arises when warehouse is selected b4 item)
tot_avail_qty = sql(
"select projected_qty from `tabBin` where item_code = '%s' and warehouse = '%s'"
% (d.item_code, d.reserved_warehouse))
d.projected_qty = tot_avail_qty and flt(tot_avail_qty[0][0]) or 0
if flag == 0:
msgprint("There are no items of the quotation selected.")
raise Exception
def validate_mandatory(self):
# validate transaction date v/s delivery date
if self.doc.delivery_date:
if getdate(self.doc.transaction_date) > getdate(self.doc.delivery_date):
msgprint("Expected Delivery Date cannot be before Sales Order Date")
raise Exception
# amendment date is necessary if document is amended
if self.doc.amended_from and not self.doc.amendment_date:
msgprint("Please Enter Amendment Date")
raise Exception
def set_last_contact_date(self):
#if not self.doc.contact_date_ref:
#self.doc.contact_date_ref=self.doc.contact_date
#self.doc.last_contact_date=self.doc.contact_date_ref
if self.doc.contact_date_ref and self.doc.contact_date_ref != self.doc.contact_date:
if getdate(self.doc.contact_date_ref) < getdate(self.doc.contact_date):
self.doc.last_contact_date=self.doc.contact_date_ref
else:
msgprint("Contact Date Cannot be before Last Contact Date")
raise Exception
def validate_warranty_status(self):
if (self.doc.maintenance_status == 'Out of Warranty'
and self.doc.warranty_expiry_date and getdate(
self.doc.warranty_expiry_date) >= datetime.date.today()
) or (self.doc.maintenance_status == 'Under Warranty' and
(not self.doc.warranty_expiry_date or getdate(
self.doc.warranty_expiry_date) < datetime.date.today())):
webnotes.throw(
self.doc.name + ": " +
_("Warranty expiry date and maintenance status mismatched"))
def validate_amc_status(self):
if (self.doc.maintenance_status == 'Out of AMC'
and self.doc.amc_expiry_date
and getdate(self.doc.amc_expiry_date) >= datetime.date.today()
) or (self.doc.maintenance_status == 'Under AMC' and
(not self.doc.amc_expiry_date or
getdate(self.doc.amc_expiry_date) < datetime.date.today())):
webnotes.throw(
self.doc.name + ": " +
_("AMC expiry date and maintenance status mismatched"))
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
account_customer = dict(
webnotes.conn.sql("""select account.name, customer.customer_name
from `tabAccount` account, `tabCustomer` customer
where account.master_type="Customer" and customer.name=account.master_name"""
))
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, upto_report_date=False)]
account_territory_map = get_account_territory_map()
si_due_date_map = get_si_due_date_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
due_date = (gle.voucher_type == "Sales Invoice") \
and si_due_date_map.get(gle.voucher_no) or ""
invoiced_amount = gle.debit > 0 and gle.debit or 0
outstanding_amount = get_outstanding_amount(
gle,
filters.get("report_date") or nowdate())
if abs(flt(outstanding_amount)) > 0.01:
payment_amount = invoiced_amount - outstanding_amount
row = [
gle.posting_date, gle.account,
account_customer.get(gle.account, ""), gle.voucher_type,
gle.voucher_no, gle.remarks, due_date,
account_territory_map.get(gle.account), invoiced_amount,
payment_amount, outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
def create_schedule_list(self, start_date, end_date, no_of_visit):
schedule_list = []
start_date1 = start_date
date_diff = (getdate(end_date) - getdate(start_date)).days
add_by = date_diff / no_of_visit
#schedule_list.append(start_date1)
while (getdate(start_date1) < getdate(end_date)):
start_date1 = add_days(start_date1, add_by)
if len(schedule_list) < no_of_visit:
schedule_list.append(getdate(start_date1))
return schedule_list
def check_stock_frozen_date(self):
stock_frozen_upto = webnotes.conn.get_value('Stock Settings', None, 'stock_frozen_upto') or ''
if stock_frozen_upto:
stock_auth_role = webnotes.conn.get_value('Stock Settings', None,'stock_auth_role')
if getdate(self.doc.posting_date) <= getdate(stock_frozen_upto) and not stock_auth_role in webnotes.user.get_roles():
msgprint("You are not authorized to do / modify back dated stock entries before %s" % getdate(stock_frozen_upto).strftime('%d-%m-%Y'), raise_exception=StockFreezeError)
stock_frozen_upto_days = int(webnotes.conn.get_value('Stock Settings', None, 'stock_frozen_upto_days') or 0)
if stock_frozen_upto_days:
stock_auth_role = webnotes.conn.get_value('Stock Settings', None,'stock_auth_role')
older_than_x_days_ago = (add_days(getdate(self.doc.posting_date), stock_frozen_upto_days) <= date.today())
if older_than_x_days_ago and not stock_auth_role in webnotes.user.get_roles():
msgprint("You are not authorized to do / modify back dated stock entries older than %d days ago" %stock_frozen_upto_days, raise_exception=StockFreezeError)
def validate_po(self):
# validate p.o date v/s delivery date
if self.doc.po_date and self.doc.delivery_date and getdate(self.doc.po_date) > getdate(self.doc.delivery_date):
msgprint("Expected Delivery Date cannot be before Purchase Order Date")
raise Exception
if self.doc.po_no and self.doc.customer:
so = webnotes.conn.sql("select name from `tabSales Order` \
where ifnull(po_no, '') = %s and name != %s and docstatus < 2\
and customer = %s", (self.doc.po_no, self.doc.name, self.doc.customer))
if so and so[0][0]:
msgprint("""Another Sales Order (%s) exists against same PO No and Customer.
Please be sure, you are not making duplicate entry.""" % so[0][0])
def validate_warranty_status(self):
"""
validate warranty status
"""
if (self.doc.maintenance_status == 'Out of Warranty'
and self.doc.warranty_expiry_date and getdate(
self.doc.warranty_expiry_date) >= datetime.date.today()
) or (self.doc.maintenance_status == 'Under Warranty' and
(not self.doc.warranty_expiry_date or getdate(
self.doc.warranty_expiry_date) < datetime.date.today())):
msgprint(
"Warranty expiry date and maintenance status mismatch. Please verify",
raise_exception=1)
def check_freezing_date(self, adv_adj):
"""
Nobody can do GL Entries where posting date is before freezing date
except authorized person
"""
if not adv_adj:
acc_frozen_upto = webnotes.conn.get_value('Global Defaults', None, 'acc_frozen_upto')
if acc_frozen_upto:
bde_auth_role = webnotes.conn.get_value( 'Global Defaults', None,'bde_auth_role')
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 entries before ") +
getdate(acc_frozen_upto).strftime('%d-%m-%Y'), raise_exception=1)
def check_stock_frozen_date(self):
stock_frozen_upto = webnotes.conn.get_value('Stock Settings', None,
'stock_frozen_upto') or ''
if stock_frozen_upto:
stock_auth_role = webnotes.conn.get_value('Stock Settings', None,
'stock_auth_role')
if getdate(self.doc.posting_date) <= getdate(
stock_frozen_upto
) and not stock_auth_role in webnotes.user.get_roles():
msgprint(
"You are not authorized to do / modify back dated stock entries before %s"
% getdate(stock_frozen_upto).strftime('%d-%m-%Y'),
raise_exception=1)
def validate_serial_no_warranty(self):
for d in getlist(self.doclist, 'item_maintenance_detail'):
if d.serial_no:
dt = sql("select warranty_expiry_date, amc_expiry_date from `tabSerial No` where name = %s", d.serial_no, as_dict=1)
if dt[0]['warranty_expiry_date']:
if dt[0]['warranty_expiry_date'] >= getdate(d.start_date):
msgprint("Serial no "+d.serial_no+" for item "+d.item_code+" is already under warranty till "+(dt[0]['warranty_expiry_date']).strftime('%Y-%m-%d')+". You can schedule AMC start date after "+(dt[0]['warranty_expiry_date']).strftime('%Y-%m-%d'))
raise Exception
if dt[0]['amc_expiry_date']:
if dt[0]['amc_expiry_date'] >= getdate(d.start_date):
msgprint("Serial no "+d.serial_no+" for item "+d.item_code+" is already under AMC till "+(dt[0]['amc_expiry_date']).strftime('%Y-%m-%d')+". You can schedule new AMC start date after "+(dt[0]['amc_expiry_date']).strftime('%Y-%m-%d'))
raise Exception
def check_freezing_date(posting_date, adv_adj=False):
"""
Nobody can do GL Entries where posting date is before freezing date
except authorized person
"""
if not adv_adj:
acc_frozen_upto = webnotes.conn.get_value('Accounts Settings', None, 'acc_frozen_upto')
if acc_frozen_upto:
bde_auth_role = webnotes.conn.get_value( 'Accounts Settings', None,'bde_auth_role')
if getdate(posting_date) <= getdate(acc_frozen_upto) \
and not bde_auth_role in webnotes.user.get_roles():
webnotes.throw(_("You are not authorized to do/modify back dated entries before ")
+ getdate(acc_frozen_upto).strftime('%d-%m-%Y'))
def validate(self):
year_start_end_dates = webnotes.conn.sql(
"""select year_start_date, year_end_date
from `tabFiscal Year` where name=%s""", (self.doc.name))
if year_start_end_dates:
if getdate(
self.doc.year_start_date
) != year_start_end_dates[0][0] or getdate(
self.doc.year_end_date) != year_start_end_dates[0][1]:
webnotes.throw(
_("Cannot change Year Start Date and Year End Date \
once the Fiscal Year is saved."))
def validate_amc_status(self):
"""
validate amc status
"""
if (self.doc.maintenance_status == 'Out of AMC'
and self.doc.amc_expiry_date
and getdate(self.doc.amc_expiry_date) >= datetime.date.today()
) or (self.doc.maintenance_status == 'Under AMC' and
(not self.doc.amc_expiry_date or
getdate(self.doc.amc_expiry_date) < datetime.date.today())):
msgprint(
"AMC expiry date and maintenance status mismatch. Please verify",
raise_exception=1)
def get_fy_details(self, fy_start):
st = {'1st Jan':'01-01','1st Apr':'04-01','1st Jul':'07-01', '1st Oct': '10-01'}
curr_year = getdate(nowdate()).year
if cint(getdate(nowdate()).month) < cint((st[fy_start].split('-'))[0]):
curr_year = getdate(nowdate()).year - 1
stdt = cstr(curr_year)+'-'+cstr(st[fy_start])
#eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0]))
if(fy_start == '1st Jan'):
fy = cstr(getdate(nowdate()).year)
abbr = cstr(fy)[-2:]
else:
fy = cstr(curr_year) + '-' + cstr(curr_year+1)
abbr = cstr(curr_year)[-2:] + '-' + cstr(curr_year+1)[-2:]
return fy, stdt, abbr
def validate(self):
if self.doc.exp_start_date and self.doc.exp_end_date and getdate(
self.doc.exp_start_date) > getdate(self.doc.exp_end_date):
msgprint(
"'Expected Start Date' can not be greater than 'Expected End Date'"
)
raise Exception
if self.doc.act_start_date and self.doc.act_end_date and getdate(
self.doc.act_start_date) > getdate(self.doc.act_end_date):
msgprint(
"'Actual Start Date' can not be greater than 'Actual End Date'"
)
raise Exception
def check_freezing_date(self, adv_adj):
if not adv_adj:
acc_frozen_upto = get_value('Global Defaults', None,
'acc_frozen_upto')
if acc_frozen_upto:
bde_auth_role = get_value('Global Defaults', None,
'bde_auth_role')
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)
def get_payment_days(self, m):
payment_days = m['month_days']
emp = webnotes.conn.sql(
"select date_of_joining, relieving_date from `tabEmployee` \
where name = %s",
self.doc.employee,
as_dict=1)[0]
if emp['relieving_date']:
if getdate(
emp['relieving_date']) > m['month_start_date'] and getdate(
emp['relieving_date']) < m['month_end_date']:
payment_days = getdate(emp['relieving_date']).day
elif getdate(emp['relieving_date']) < m['month_start_date']:
payment_days = 0
if emp['date_of_joining']:
if getdate(emp['date_of_joining']
) > m['month_start_date'] and getdate(
emp['date_of_joining']) < m['month_end_date']:
payment_days = payment_days - getdate(
emp['date_of_joining']).day + 1
elif getdate(emp['date_of_joining']) > m['month_end_date']:
payment_days = 0
return payment_days
def get_next_date(dt, mcount):
import datetime
m = getdate(dt).month + mcount
y = getdate(dt).year
d = getdate(dt).day
if m > 12:
m, y = m - 12, y + 1
try:
next_month_date = datetime.date(y, m, d)
except:
import calendar
last_day = calendar.monthrange(y, m)[1]
next_month_date = datetime.date(y, m, last_day)
return next_month_date.strftime("%Y-%m-%d")
def get_payment_days(self, m):
payment_days = m['month_days']
emp = webnotes.conn.sql(
"select date_of_joining, relieving_date from `tabEmployee` \
where name = %s",
self.doc.employee,
as_dict=1)[0]
if emp['relieving_date']:
if getdate(emp['relieving_date']) > m['month_start_date'] and \
getdate(emp['relieving_date']) < m['month_end_date']:
payment_days = getdate(emp['relieving_date']).day
elif getdate(emp['relieving_date']) < m['month_start_date']:
webnotes.msgprint(
_("Relieving Date of employee is ") +
cstr(emp['relieving_date'] +
_(". Please set status of the employee as 'Left'")),
raise_exception=1)
if emp['date_of_joining']:
if getdate(emp['date_of_joining']) > m['month_start_date'] and \
getdate(emp['date_of_joining']) < m['month_end_date']:
payment_days = payment_days - getdate(
emp['date_of_joining']).day + 1
elif getdate(emp['date_of_joining']) > m['month_end_date']:
payment_days = 0
return payment_days
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, upto_report_date=False)]
account_territory_map = get_account_territory_map()
si_due_date_map = get_si_due_date_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
due_date = (gle.voucher_type == "Sales Invoice") \
and si_due_date_map.get(gle.voucher_no) or ""
invoiced_amount = gle.debit > 0 and gle.debit or 0
payment_amount = get_payment_amount(
gle,
filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - payment_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [
gle.posting_date, gle.account, gle.voucher_type,
gle.voucher_no, gle.remarks, due_date,
account_territory_map.get(gle.account), invoiced_amount,
payment_amount, outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
def get_next_date(dt, mcount, day=None):
import datetime
month = getdate(dt).month + mcount
year = getdate(dt).year
if not day:
day = getdate(dt).day
if month > 12:
month, year = m-12, y+1
try:
next_month_date = datetime.date(year, month, day)
except:
import calendar
last_day = calendar.monthrange(year, month)[1]
next_month_date = datetime.date(year, month, last_day)
return next_month_date.strftime("%Y-%m-%d")
def get_ageing_data(ageing_based_on_date, age_on, outstanding_amount):
val1 = val2 = val3 = val4 = diff = 0
diff = age_on and ageing_based_on_date \
and (getdate(age_on) - getdate(ageing_based_on_date)).days or 0
if diff <= 30:
val1 = outstanding_amount
elif 30 < diff <= 60:
val2 = outstanding_amount
elif 60 < diff <= 90:
val3 = outstanding_amount
elif diff > 90:
val4 = outstanding_amount
return [diff, val1, val2, val3, val4]
def validate_first_entry(self,obj):
if obj.doc.doctype == 'Purchase Invoice':
supp_acc = obj.doc.credit_to
elif obj.doc.doctype == 'Journal Voucher':
supp_acc = obj.doc.supplier_account
if obj.doc.ded_amount:
# first pv
first_pv = sql("select posting_date from `tabPurchase Invoice` where credit_to = '%s' and docstatus = 1 and tds_category = '%s' and fiscal_year = '%s' and tds_applicable = 'Yes' and (ded_amount != 0 or ded_amount is not null) order by posting_date asc limit 1"%(supp_acc, obj.doc.tds_category, obj.doc.fiscal_year))
first_pv_date = first_pv and first_pv[0][0] or ''
# first jv
first_jv = sql("select posting_date from `tabJournal Voucher` where supplier_account = '%s'and docstatus = 1 and tds_category = '%s' and fiscal_year = '%s' and tds_applicable = 'Yes' and (ded_amount != 0 or ded_amount is not null) order by posting_date asc limit 1"%(supp_acc, obj.doc.tds_category, obj.doc.fiscal_year))
first_jv_date = first_jv and first_jv[0][0] or ''
#first tds voucher date
first_tds_date = ''
if first_pv_date and first_jv_date:
first_tds_date = first_pv_date < first_jv_date and first_pv_date or first_jv_date
elif first_pv_date:
first_tds_date = first_pv_date
elif first_jv_date:
first_tds_date = first_jv_date
if first_tds_date and getdate(obj.doc.posting_date) < first_tds_date:
msgprint("First tds voucher for this category has been made already. Hence payable voucher cannot be made before posting date of first tds voucher ")
raise Exception
def get_next_date(dt, mcount, day=None):
dt = getdate(dt)
from dateutil.relativedelta import relativedelta
dt += relativedelta(months=mcount, day=day)
return dt
