def get_conditions(filters):
conditions = ""
si_cond = ""
so_cond = ""
if filters.get("customer"):
conditions += " and dn.customer = '%s'" % filters["customer"]
if filters.get("from_date"):
if filters.get("to_date"):
if getdate(filters.get("from_date"))>getdate(filters.get("to_date")):
frappe.msgprint("From Date cannot be greater than To Date", raise_exception=1)
conditions += " and dn.posting_date >= '%s'" % filters["from_date"]
if filters.get("to_date"):
conditions += " and dn.posting_date <= '%s'" % filters["to_date"]
if filters.get("draft")=="Yes":
si_cond = " and si.docstatus != 2"
else:
si_cond = " and si.docstatus = 1"
if filters.get("trial") == "Yes":
so_cond = "and so.track_trial = 1"
else:
so_cond = "and (so.track_trial <> 1 or so.track_trial is null)"
return conditions, si_cond, so_cond
def validate_filters(filters):
if not filters.fiscal_year:
frappe.throw(_("Fiscal Year {0} is required").format(filters.fiscal_year))
fiscal_year = frappe.db.get_value("Fiscal Year", filters.fiscal_year, ["year_start_date", "year_end_date"], as_dict=True)
if not fiscal_year:
frappe.throw(_("Fiscal Year {0} does not exist").format(filters.fiscal_year))
else:
filters.year_start_date = getdate(fiscal_year.year_start_date)
filters.year_end_date = getdate(fiscal_year.year_end_date)
if not filters.from_date:
filters.from_date = filters.year_start_date
if not filters.to_date:
filters.to_date = filters.year_end_date
filters.from_date = getdate(filters.from_date)
filters.to_date = getdate(filters.to_date)
if filters.from_date > filters.to_date:
frappe.throw(_("From Date cannot be greater than To Date"))
if (filters.from_date < filters.year_start_date) or (filters.from_date > filters.year_end_date):
frappe.msgprint(_("From Date should be within the Fiscal Year. Assuming From Date = {0}")\
.format(formatdate(filters.year_start_date)))
filters.from_date = filters.year_start_date
if (filters.to_date < filters.year_start_date) or (filters.to_date > filters.year_end_date):
frappe.msgprint(_("To Date should be within the Fiscal Year. Assuming To Date = {0}")\
.format(formatdate(filters.year_end_date)))
filters.to_date = filters.year_end_date
def get_leaves_for_period(employee, leave_type, from_date, to_date, status, docname=None):
leave_applications = frappe.db.sql("""
select name, employee, leave_type, from_date, to_date, total_leave_days
from `tabLeave Application`
where employee=%(employee)s and leave_type=%(leave_type)s
and status = %(status)s and docstatus != 2
and (from_date between %(from_date)s and %(to_date)s
or to_date between %(from_date)s and %(to_date)s
or (from_date < %(from_date)s and to_date > %(to_date)s))
""", {
"from_date": from_date,
"to_date": to_date,
"employee": employee,
"status": status,
"leave_type": leave_type
}, as_dict=1)
leave_days = 0
for leave_app in leave_applications:
if docname and leave_app.name == docname:
continue
if leave_app.from_date >= getdate(from_date) and leave_app.to_date <= getdate(to_date):
leave_days += leave_app.total_leave_days
else:
if leave_app.from_date < getdate(from_date):
leave_app.from_date = from_date
if leave_app.to_date > getdate(to_date):
leave_app.to_date = to_date
leave_days += get_number_of_leave_days(employee, leave_type,
leave_app.from_date, leave_app.to_date)
return leave_days
def validate_asset_values(self):
if not flt(self.gross_purchase_amount):
frappe.throw(_("Gross Purchase Amount is mandatory"), frappe.MandatoryError)
if not is_cwip_accounting_disabled():
if not self.is_existing_asset and not (self.purchase_receipt or self.purchase_invoice):
frappe.throw(_("Please create purchase receipt or purchase invoice for the item {0}").
format(self.item_code))
if (not self.purchase_receipt and self.purchase_invoice
and not frappe.db.get_value('Purchase Invoice', self.purchase_invoice, 'update_stock')):
frappe.throw(_("Update stock must be enable for the purchase invoice {0}").
format(self.purchase_invoice))
if not self.calculate_depreciation:
return
elif not self.finance_books:
frappe.throw(_("Enter depreciation details"))
if self.is_existing_asset:
return
docname = self.purchase_receipt or self.purchase_invoice
if docname:
doctype = 'Purchase Receipt' if self.purchase_receipt else 'Purchase Invoice'
date = frappe.db.get_value(doctype, docname, 'posting_date')
if self.available_for_use_date and getdate(self.available_for_use_date) < getdate(self.purchase_date):
frappe.throw(_("Available-for-use Date should be after purchase date"))
def get_approved_leaves_for_period(employee, leave_type, from_date, to_date):
query = """
select employee, leave_type, from_date, to_date, total_leave_days
from `tabLeave Application`
where employee=%(employee)s
and docstatus=1
and (from_date between %(from_date)s and %(to_date)s
or to_date between %(from_date)s and %(to_date)s
or (from_date < %(from_date)s and to_date > %(to_date)s))
"""
if leave_type:
query += "and leave_type=%(leave_type)s"
leave_applications = frappe.db.sql(query,{
"from_date": from_date,
"to_date": to_date,
"employee": employee,
"leave_type": leave_type
}, as_dict=1)
leave_days = 0
for leave_app in leave_applications:
if leave_app.from_date >= getdate(from_date) and leave_app.to_date <= getdate(to_date):
leave_days += leave_app.total_leave_days
else:
if leave_app.from_date < getdate(from_date):
leave_app.from_date = from_date
if leave_app.to_date > getdate(to_date):
leave_app.to_date = to_date
leave_days += get_number_of_leave_days(employee, leave_type,
leave_app.from_date, leave_app.to_date)
return leave_days
def test_overlapping_allocation(self):
employee = frappe.get_doc("Employee", frappe.db.sql_list("select name from tabEmployee limit 1")[0])
leaves = [
{
"doctype": "Leave Allocation",
"__islocal": 1,
"employee": employee.name,
"employee_name": employee.employee_name,
"leave_type": "_Test Leave Type",
"from_date": getdate("2015-10-1"),
"to_date": getdate("2015-10-31"),
"new_leaves_allocated": 5,
"docstatus": 1
},
{
"doctype": "Leave Allocation",
"__islocal": 1,
"employee": employee.name,
"employee_name": employee.employee_name,
"leave_type": "_Test Leave Type",
"from_date": getdate("2015-09-1"),
"to_date": getdate("2015-11-30"),
"new_leaves_allocated": 5
}
]
frappe.get_doc(leaves[0]).save()
self.assertRaises(frappe.ValidationError, frappe.get_doc(leaves[1]).save)
def update_attendance(self):
if self.status == "Approved":
attendance = frappe.db.sql("""select name from `tabAttendance` where employee = %s\
and (attendance_date between %s and %s) and docstatus < 2""",(self.employee, self.from_date, self.to_date), as_dict=1)
if attendance:
for d in attendance:
doc = frappe.get_doc("Attendance", d.name)
if getdate(self.half_day_date) == doc.attendance_date:
status = "Half Day"
else:
status = "On Leave"
frappe.db.sql("""update `tabAttendance` set status = %s, leave_type = %s\
where name = %s""",(status, self.leave_type, d.name))
elif getdate(self.to_date) <= getdate(nowdate()):
for dt in daterange(getdate(self.from_date), getdate(self.to_date)):
date = dt.strftime("%Y-%m-%d")
doc = frappe.new_doc("Attendance")
doc.employee = self.employee
doc.attendance_date = date
doc.company = self.company
doc.leave_type = self.leave_type
doc.status = "Half Day" if date == self.half_day_date else "On Leave"
doc.flags.ignore_validate = True
doc.insert(ignore_permissions=True)
doc.submit()
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 = frappe.db.sql("""\
select po.name, po.transaction_date, po.conversion_rate,
po_item.conversion_factor, po_item.base_price_list_rate,
po_item.discount_percentage, po_item.base_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 = frappe.db.sql("""\
select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
pr_item.conversion_factor, pr_item.base_price_list_rate, pr_item.discount_percentage,
pr_item.base_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 frappe._dict()
conversion_factor = flt(last_purchase.conversion_factor)
out = frappe._dict({
"base_price_list_rate": flt(last_purchase.base_price_list_rate) / conversion_factor,
"base_rate": flt(last_purchase.base_rate) / conversion_factor,
"discount_percentage": flt(last_purchase.discount_percentage),
"purchase_date": purchase_date
})
conversion_rate = flt(conversion_rate) or 1.0
out.update({
"price_list_rate": out.base_price_list_rate / conversion_rate,
"rate": out.base_rate / conversion_rate,
"base_rate": out.base_rate
})
return out
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 get_usage_info():
"""Get data to show for Usage Info"""
# imported here to prevent circular import
from frappe.email.queue import get_emails_sent_this_month
limits = get_limits()
if not (limits and any([limits.users, limits.space, limits.emails, limits.expiry])):
# no limits!
return
limits.space = (limits.space or 0) * 1024.0 # to MB
if not limits.space_usage:
# hack! to show some progress
limits.space_usage = {"database_size": 26, "files_size": 1, "backup_size": 1, "total": 28}
usage_info = frappe._dict(
{
"limits": limits,
"enabled_users": len(get_enabled_system_users()),
"emails_sent": get_emails_sent_this_month(),
"space_usage": limits.space_usage["total"],
}
)
if limits.expiry:
usage_info["expires_on"] = formatdate(limits.expiry)
usage_info["days_to_expiry"] = (getdate(limits.expiry) - getdate()).days
if limits.upgrade_url:
usage_info["upgrade_url"] = get_upgrade_url(limits.upgrade_url)
return usage_info
def get_period_date_ranges(filters):
from dateutil.relativedelta import relativedelta
from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
increment = {
"Monthly": 1,
"Quarterly": 3,
"Half-Yearly": 6,
"Yearly": 12
}.get(filters.range,1)
periodic_daterange = []
for dummy in range(1, 53, increment):
if filters.range == "Weekly":
period_end_date = from_date + relativedelta(days=6)
else:
period_end_date = from_date + relativedelta(months=increment, days=-1)
if period_end_date > to_date:
period_end_date = to_date
periodic_daterange.append([from_date, period_end_date])
from_date = period_end_date + relativedelta(days=1)
if period_end_date == to_date:
break
return periodic_daterange
def validate_dates(self):
if self.from_date and self.to_date and (getdate(self.to_date) < getdate(self.from_date)):
frappe.throw(_("To date cannot be before from date"))
if not is_lwp(self.leave_type):
self.validate_dates_acorss_allocation()
self.validate_back_dated_application()
def get_item_warehouse_batch_map(filters, float_precision):
sle = get_stock_ledger_entries(filters)
iwb_map = {}
from_date = getdate(filters["from_date"])
to_date = getdate(filters["to_date"])
for d in sle:
iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, {})\
.setdefault(d.batch_no, frappe._dict({
"opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
}))
qty_dict = iwb_map[d.item_code][d.warehouse][d.batch_no]
if d.posting_date < from_date:
qty_dict.opening_qty = flt(qty_dict.opening_qty, float_precision) \
+ flt(d.actual_qty, float_precision)
elif d.posting_date >= from_date and d.posting_date <= to_date:
if flt(d.actual_qty) > 0:
qty_dict.in_qty = flt(qty_dict.in_qty, float_precision) + flt(d.actual_qty, float_precision)
else:
qty_dict.out_qty = flt(qty_dict.out_qty, float_precision) \
+ abs(flt(d.actual_qty, float_precision))
qty_dict.bal_qty = flt(qty_dict.bal_qty, float_precision) + flt(d.actual_qty, float_precision)
return iwb_map
def get_usage_info():
'''Get data to show for Usage Info'''
# imported here to prevent circular import
from frappe.email.queue import get_emails_sent_this_month
limits = get_limits()
if not (limits and any([limits.users, limits.space, limits.emails, limits.expiry])):
# no limits!
return
limits.space = (limits.space or 0) * 1024.0 # to MB
if not limits.space_usage:
# hack! to show some progress
limits.space_usage = {
'database_size': 26,
'files_size': 1,
'backup_size': 1,
'total': 28
}
usage_info = frappe._dict({
'limits': limits,
'enabled_users': len(get_enabled_system_users()),
'emails_sent': get_emails_sent_this_month(),
'space_usage': limits.space_usage['total'],
})
if limits.expiry:
usage_info['expires_on'] = formatdate(limits.expiry)
usage_info['days_to_expiry'] = (getdate(limits.expiry) - getdate()).days
if limits.upgrade_url:
usage_info['upgrade_url'] = get_upgrade_url(limits.upgrade_url)
return usage_info
def test_reschedule_dependent_task(self):
task1 = create_task("_Test Task 1", nowdate(), add_days(nowdate(), 10))
task2 = create_task("_Test Task 2", add_days(nowdate(), 11), add_days(nowdate(), 15), task1.name)
task2.get("depends_on")[0].project = "_Test Project"
task2.save()
task3 = create_task("_Test Task 3", add_days(nowdate(), 11), add_days(nowdate(), 15), task2.name)
task3.get("depends_on")[0].project = "_Test Project"
task3.save()
task1.update({
"exp_end_date": add_days(nowdate(), 20)
})
task1.save()
self.assertEqual(frappe.db.get_value("Task", task2.name, "exp_start_date"),
getdate(add_days(nowdate(), 21)))
self.assertEqual(frappe.db.get_value("Task", task2.name, "exp_end_date"),
getdate(add_days(nowdate(), 25)))
self.assertEqual(frappe.db.get_value("Task", task3.name, "exp_start_date"),
getdate(add_days(nowdate(), 26)))
self.assertEqual(frappe.db.get_value("Task", task3.name, "exp_end_date"),
getdate(add_days(nowdate(), 30)))
def validate_supplier_invoice(self):
if self.bill_date:
if getdate(self.bill_date) > getdate(self.posting_date):
frappe.throw(_("Supplier Invoice Date cannot be greater than Posting Date"))
if self.bill_no:
if cint(frappe.db.get_single_value("Accounts Settings", "check_supplier_invoice_uniqueness")):
fiscal_year = get_fiscal_year(self.posting_date, company=self.company, as_dict=True)
pi = frappe.db.sql('''select name from `tabPurchase Invoice`
where
bill_no = %(bill_no)s
and supplier = %(supplier)s
and name != %(name)s
and docstatus < 2
and posting_date between %(year_start_date)s and %(year_end_date)s''', {
"bill_no": self.bill_no,
"supplier": self.supplier,
"name": self.name,
"year_start_date": fiscal_year.year_start_date,
"year_end_date": fiscal_year.year_end_date
})
if pi:
pi = pi[0][0]
frappe.throw(_("Supplier Invoice No exists in Purchase Invoice {0}".format(pi)))
def get_events(start, end, filters=None):
"""Returns events for Gantt / Calendar view rendering.
:param start: Start date-time.
:param end: End date-time.
:param filters: Filters (JSON).
"""
condition = ""
values = {"start_date": getdate(start), "end_date": getdate(end)}
if filters:
if isinstance(filters, basestring):
filters = json.loads(filters)
if filters.get("holiday_list"):
condition = "and hlist.name=%(holiday_list)s"
values["holiday_list"] = filters["holiday_list"]
data = frappe.db.sql(
"""select hlist.name, h.holiday_date, h.description
from `tabHoliday List` hlist, tabHoliday h
where h.parent = hlist.name
and h.holiday_date is not null
and h.holiday_date >= %(start_date)s
and h.holiday_date <= %(end_date)s
{condition}""".format(
condition=condition
),
values,
as_dict=True,
update={"allDay": 1},
)
return data
def validate_dates(self):
if getdate(self.year_start_date) > getdate(self.year_end_date):
frappe.throw(_("Fiscal Year Start Date should not be greater than Fiscal Year End Date"))
if (getdate(self.year_end_date) - getdate(self.year_start_date)).days > 366:
date = getdate(self.year_start_date) + relativedelta(years=1) - relativedelta(days=1)
self.year_end_date = date.strftime("%Y-%m-%d")
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 = frappe.db.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 get_no_of_days(self): no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month) no_of_holidays_in_month = len([1 for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month) if i[6] != 0]) return [no_of_days_in_month[1], no_of_holidays_in_month]
def validate(self):
year_start_end_dates = frappe.db.sql("""select year_start_date, year_end_date
from `tabFiscal Year` where name=%s""", (self.name))
if year_start_end_dates:
if getdate(self.year_start_date) != year_start_end_dates[0][0] or getdate(self.year_end_date) != year_start_end_dates[0][1]:
frappe.throw(_("Cannot change Fiscal Year Start Date and Fiscal Year End Date once the Fiscal Year is saved."))
def validate_dates(self):
date_of_joining, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
if date_of_joining and getdate(self.payroll_date) < getdate(date_of_joining):
frappe.throw(_("Payroll date can not be less than employee's joining date"))
elif relieving_date and getdate(self.payroll_date) > getdate(relieving_date):
frappe.throw(_("To date can not greater than employee's relieving date"))
def execute(filters=None):
if not filters: filters = {}
float_preceision = frappe.db.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:
frappe.throw(_("'From Date' must be after 'To Date'"))
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 validate_recurring_next_date(doc):
posting_date = doc.get("posting_date") or doc.get("transaction_date")
if getdate(posting_date) > getdate(doc.next_date):
frappe.throw(_("Next Date must be greater than Posting Date"))
if getdate(doc.next_date).day != doc.repeat_on_day_of_month:
frappe.throw(_("Next Date's day and Repeat on Day of Month must be equal"))
def validate(self):
"""validate start date before end date"""
if self.project_start_date and self.completion_date:
if getdate(self.completion_date) < getdate(self.project_start_date):
frappe.throw(_("Expected Completion Date can not be less than Project Start Date"))
self.update_milestones_completed()
def execute(filters=None):
priority_map = {"High": 3, "Medium": 2, "Low": 1}
todo_list = frappe.get_list('ToDo', fields=["name", "date", "description",
"priority", "reference_type", "reference_name", "assigned_by", "owner"],
filters={'status': 'Open'})
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") + ":Data:120",
_("Assigned By")+":Data:120", _("Reference")+"::200"]
result = []
for todo in todo_list:
if todo.owner==frappe.session.user or todo.assigned_by==frappe.session.user:
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 set_indicator(self): if self.docstatus==1: self.indicator_color = 'blue' self.indicator_title = 'Submitted' if self.valid_till and getdate(self.valid_till) < getdate(nowdate()): self.indicator_color = 'darkgrey' self.indicator_title = 'Expired'
def update_clearance_date(self):
clearance_date_updated = False
for d in self.get('payment_entries'):
if d.clearance_date:
if not d.payment_document:
frappe.throw(_("Row #{0}: Payment document is required to complete the trasaction"))
if d.cheque_date and getdate(d.clearance_date) < getdate(d.cheque_date):
frappe.throw(_("Row #{0}: Clearance date {1} cannot be before Cheque Date {2}")
.format(d.idx, d.clearance_date, d.cheque_date))
if d.clearance_date or self.include_reconciled_entries:
if not d.clearance_date:
d.clearance_date = None
frappe.db.set_value(d.payment_document, d.payment_entry, "clearance_date", d.clearance_date)
frappe.db.sql("""update `tab{0}` set clearance_date = %s, modified = %s
where name=%s""".format(d.payment_document),
(d.clearance_date, nowdate(), d.payment_entry))
clearance_date_updated = True
if clearance_date_updated:
self.get_payment_entries()
msgprint(_("Clearance Date updated"))
else:
msgprint(_("Clearance Date not mentioned"))
def validate_filters(filters):
filters.from_date = getdate(filters.from_date)
filters.to_date = getdate(filters.to_date)
if filters.from_date > filters.to_date:
frappe.throw(_("From Date cannot be greater than To Date"))
def validate_filters(filters):
filters.year_start_date, filters.year_end_date = frappe.db.get_value("Fiscal Year", filters.fiscal_year,
["year_start_date", "year_end_date"])
filters.year_start_date = getdate(filters.year_start_date)
filters.year_end_date = getdate(filters.year_end_date)
if not filters.from_date:
filters.from_date = filters.year_start_date
if not filters.to_date:
filters.to_date = filters.year_end_date
filters.from_date = getdate(filters.from_date)
filters.to_date = getdate(filters.to_date)
if filters.from_date > filters.to_date:
frappe.throw(_("From Date cannot be greater than To Date"))
if (filters.from_date < filters.year_start_date) or (filters.from_date > filters.year_end_date):
frappe.msgprint(_("From Date should be within the Fiscal Year. Assuming From Date = {0}")\
.format(formatdate(filters.year_start_date)))
filters.from_date = filters.year_start_date
if (filters.to_date < filters.year_start_date) or (filters.to_date > filters.year_end_date):
frappe.msgprint(_("To Date should be within the Fiscal Year. Assuming To Date = {0}")\
.format(formatdate(filters.year_end_date)))
filters.to_date = filters.year_end_date
def validate_dates(self):
if self.from_date and self.to_date and (getdate(self.to_date) < getdate(self.from_date)):
frappe.throw(_("To date cannot be before from date"))
def prepare_filter_condition(self, f):
"""Returns a filter condition in the format:
ifnull(`tabDocType`.`fieldname`, fallback) operator "value"
"""
f = get_filter(self.doctype, f)
tname = ('`tab' + f.doctype + '`')
if not tname in self.tables:
self.append_table(tname)
if 'ifnull(' in f.fieldname:
column_name = f.fieldname
else:
column_name = '{tname}.{fname}'.format(tname=tname,
fname=f.fieldname)
can_be_null = True
# prepare in condition
if f.operator.lower() in ('in', 'not in'):
values = f.value or ''
if not isinstance(values, (list, tuple)):
values = values.split(",")
fallback = "''"
value = (frappe.db.escape((v or '').strip(), percent=False)
for v in values)
value = '("{0}")'.format('", "'.join(value))
else:
df = frappe.get_meta(f.doctype).get("fields",
{"fieldname": f.fieldname})
df = df[0] if df else None
if df and df.fieldtype in ("Check", "Float", "Int", "Currency",
"Percent"):
can_be_null = False
if f.operator.lower() == 'between' and \
(f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))):
from_date = None
to_date = None
if f.value and isinstance(f.value, (list, tuple)):
if len(f.value) >= 1: from_date = f.value[0]
if len(f.value) >= 2: to_date = f.value[1]
value = "'%s' AND '%s'" % (
add_to_date(get_datetime(from_date),
days=-1).strftime("%Y-%m-%d %H:%M:%S.%f"),
get_datetime(to_date).strftime("%Y-%m-%d %H:%M:%S.%f"))
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype == "Date":
value = getdate(f.value).strftime("%Y-%m-%d")
fallback = "'0000-00-00'"
elif (df and df.fieldtype == "Datetime") or isinstance(
f.value, datetime):
value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f")
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype == "Time":
value = get_time(f.value).strftime("%H:%M:%S.%f")
fallback = "'00:00:00'"
elif f.operator.lower() in ("like", "not like") or (
isinstance(f.value, basestring) and
(not df or df.fieldtype
not in ["Float", "Int", "Currency", "Percent", "Check"])):
value = "" if f.value == None else f.value
fallback = '""'
if f.operator.lower() in ("like", "not like") and isinstance(
value, basestring):
# because "like" uses backslash (\) for escaping
value = value.replace("\\", "\\\\").replace("%", "%%")
else:
value = flt(f.value)
fallback = 0
# put it inside double quotes
if isinstance(value,
basestring) and not f.operator.lower() == 'between':
value = '"{0}"'.format(frappe.db.escape(value, percent=False))
if (self.ignore_ifnull or not can_be_null
or (f.value and f.operator.lower() in ('=', 'like'))
or 'ifnull(' in column_name.lower()):
condition = '{column_name} {operator} {value}'.format(
column_name=column_name, operator=f.operator, value=value)
else:
condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format(
column_name=column_name,
fallback=fallback,
operator=f.operator,
value=value)
return condition
def is_member():
'''Returns true if the user is still a member'''
last_membership = get_last_membership()
if last_membership and getdate(last_membership.to_date) > getdate():
return True
return False
def get_events(start, end, user=None, for_reminder=False, filters=None):
if not user:
user = frappe.session.user
if isinstance(filters, string_types):
filters = json.loads(filters)
filter_condition = get_filters_cond("Event", filters, [])
tables = ["`tabEvent`"]
if "`tabEvent Participants`" in filter_condition:
tables.append("`tabEvent Participants`")
events = frappe.db.sql(
"""
SELECT `tabEvent`.name,
`tabEvent`.subject,
`tabEvent`.description,
`tabEvent`.color,
`tabEvent`.starts_on,
`tabEvent`.ends_on,
`tabEvent`.owner,
`tabEvent`.all_day,
`tabEvent`.event_type,
`tabEvent`.repeat_this_event,
`tabEvent`.repeat_on,
`tabEvent`.repeat_till,
`tabEvent`.monday,
`tabEvent`.tuesday,
`tabEvent`.wednesday,
`tabEvent`.thursday,
`tabEvent`.friday,
`tabEvent`.saturday,
`tabEvent`.sunday
FROM {tables}
WHERE (
(
(date(`tabEvent`.starts_on) BETWEEN date(%(start)s) AND date(%(end)s))
OR (date(`tabEvent`.ends_on) BETWEEN date(%(start)s) AND date(%(end)s))
OR (
date(`tabEvent`.starts_on) <= date(%(start)s)
AND date(`tabEvent`.ends_on) >= date(%(end)s)
)
)
OR (
date(`tabEvent`.starts_on) <= date(%(start)s)
AND `tabEvent`.repeat_this_event=1
AND coalesce(`tabEvent`.repeat_till, '3000-01-01') > date(%(start)s)
)
)
{reminder_condition}
{filter_condition}
AND (
`tabEvent`.event_type='Public'
OR `tabEvent`.owner=%(user)s
OR EXISTS(
SELECT `tabDocShare`.name
FROM `tabDocShare`
WHERE `tabDocShare`.share_doctype='Event'
AND `tabDocShare`.share_name=`tabEvent`.name
AND `tabDocShare`.user=%(user)s
)
)
AND `tabEvent`.status='Open'
ORDER BY `tabEvent`.starts_on""".format(
tables=", ".join(tables),
filter_condition=filter_condition,
reminder_condition="AND coalesce(`tabEvent`.send_reminder, 0)=1"
if for_reminder else "",
),
{
"start": start,
"end": end,
"user": user,
},
as_dict=1,
)
# process recurring events
start = start.split(" ")[0]
end = end.split(" ")[0]
add_events = []
remove_events = []
def add_event(e, date):
new_event = e.copy()
enddate = (add_days(
date,
int(date_diff(e.ends_on.split(" ")[0],
e.starts_on.split(" ")[0]))) if
(e.starts_on and e.ends_on) else date)
new_event.starts_on = date + " " + e.starts_on.split(" ")[1]
new_event.ends_on = new_event.ends_on = (enddate + " " +
e.ends_on.split(" ")[1]
if e.ends_on else None)
add_events.append(new_event)
for e in events:
if e.repeat_this_event:
e.starts_on = get_datetime_str(e.starts_on)
e.ends_on = get_datetime_str(e.ends_on) if e.ends_on else None
event_start, time_str = get_datetime_str(e.starts_on).split(" ")
repeat = "3000-01-01" if cstr(
e.repeat_till) == "" else e.repeat_till
if e.repeat_on == "Yearly":
start_year = cint(start.split("-")[0])
end_year = cint(end.split("-")[0])
# creates a string with date (27) and month (07) eg: 07-27
event_start = "-".join(event_start.split("-")[1:])
# repeat for all years in period
for year in range(start_year, end_year + 1):
date = str(year) + "-" + event_start
if (getdate(date) >= getdate(start)
and getdate(date) <= getdate(end)
and getdate(date) <= getdate(repeat)):
add_event(e, date)
remove_events.append(e)
if e.repeat_on == "Monthly":
# creates a string with date (27) and month (07) and year (2019) eg: 2019-07-27
date = start.split("-")[0] + "-" + start.split(
"-")[1] + "-" + event_start.split("-")[2]
# last day of month issue, start from prev month!
try:
getdate(date)
except ValueError:
date = date.split("-")
date = date[0] + "-" + str(cint(date[1]) -
1) + "-" + date[2]
start_from = date
for i in range(int(date_diff(end, start) / 30) + 3):
if (getdate(date) >= getdate(start)
and getdate(date) <= getdate(end)
and getdate(date) <= getdate(repeat)
and getdate(date) >= getdate(event_start)):
add_event(e, date)
date = add_months(start_from, i + 1)
remove_events.append(e)
if e.repeat_on == "Weekly":
for cnt in range(date_diff(end, start) + 1):
date = add_days(start, cnt)
if (getdate(date) >= getdate(start)
and getdate(date) <= getdate(end)
and getdate(date) <= getdate(repeat)
and getdate(date) >= getdate(event_start)
and e[weekdays[getdate(date).weekday()]]):
add_event(e, date)
remove_events.append(e)
if e.repeat_on == "Daily":
for cnt in range(date_diff(end, start) + 1):
date = add_days(start, cnt)
if (getdate(date) >= getdate(event_start)
and getdate(date) <= getdate(end)
and getdate(date) <= getdate(repeat)):
add_event(e, date)
remove_events.append(e)
for e in remove_events:
events.remove(e)
events = events + add_events
for e in events:
# remove weekday properties (to reduce message size)
for w in weekdays:
del e[w]
return events
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 = frappe.db.sql("""\
select po.name, po.transaction_date, po.conversion_rate,
po_item.conversion_factor, po_item.base_price_list_rate,
po_item.discount_percentage, po_item.base_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 = frappe.db.sql("""\
select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
pr_item.conversion_factor, pr_item.base_price_list_rate, pr_item.discount_percentage,
pr_item.base_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 frappe._dict()
conversion_factor = flt(last_purchase.conversion_factor)
out = frappe._dict({
"base_price_list_rate":
flt(last_purchase.base_price_list_rate) / conversion_factor,
"base_rate":
flt(last_purchase.base_rate) / conversion_factor,
"discount_percentage":
flt(last_purchase.discount_percentage),
"purchase_date":
purchase_date
})
conversion_rate = flt(conversion_rate) or 1.0
out.update({
"price_list_rate": out.base_price_list_rate / conversion_rate,
"rate": out.base_rate / conversion_rate,
"base_rate": out.base_rate
})
return out
def get_payment_entries(self):
frappe.msgprint(_("Getting entries..."), alert=True)
if not (self.from_date and self.to_date):
frappe.throw(_("From Date and To Date are Mandatory"))
if not self.account:
frappe.throw(_("Account is mandatory to get payment entries"))
condition = ""
if not self.include_reconciled_entries:
condition = "and (clearance_date IS NULL or clearance_date='0000-00-00')"
journal_entries = frappe.db.sql("""
select
"Journal Entry" as payment_document, t1.name as payment_entry,
t1.cheque_no as cheque_number, t1.cheque_date,
sum(t2.debit_in_account_currency) as debit, sum(t2.credit_in_account_currency) as credit,
t1.posting_date, t2.against_account, t1.clearance_date, t2.account_currency
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t2.parent = t1.name and t2.account = %(account)s and t1.docstatus=1
and t1.posting_date >= %(from)s and t1.posting_date <= %(to)s
and ifnull(t1.is_opening, 'No') = 'No' {condition}
group by t2.account, t1.name
order by t1.posting_date ASC, t1.name DESC
""".format(condition=condition), {
"account": self.account,
"from": self.from_date,
"to": self.to_date
},
as_dict=1)
if self.bank_account:
condition += 'and bank_account = %(bank_account)s'
payment_entries = frappe.db.sql("""
select
"Payment Entry" as payment_document, name as payment_entry,
reference_no as cheque_number, reference_date as cheque_date,
if(paid_from=%(account)s, paid_amount, 0) as credit,
if(paid_from=%(account)s, 0, received_amount) as debit,
posting_date, ifnull(party_name,if(paid_from=%(account)s,paid_to,paid_from)) as against_account, clearance_date,
if(paid_to=%(account)s, paid_to_account_currency, paid_from_account_currency) as account_currency
from `tabPayment Entry`
where
(paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
and posting_date >= %(from)s and posting_date <= %(to)s
{condition}
order by
posting_date ASC, name DESC
""".format(condition=condition), {
"account": self.account,
"from": self.from_date,
"to": self.to_date,
"bank_account": self.bank_account
},
as_dict=1)
pos_sales_invoices, pos_purchase_invoices = [], []
if self.include_pos_transactions:
pos_sales_invoices = frappe.db.sql("""
select
"Sales Invoice Payment" as payment_document, sip.name as payment_entry, sip.amount as debit,
si.posting_date, si.customer as against_account, sip.clearance_date,
account.account_currency, 0 as credit
from `tabSales Invoice Payment` sip, `tabSales Invoice` si, `tabAccount` account
where
sip.account=%(account)s and si.docstatus=1 and sip.parent = si.name
and account.name = sip.account and si.posting_date >= %(from)s and si.posting_date <= %(to)s
order by
si.posting_date ASC, si.name DESC
""", {
"account": self.account,
"from": self.from_date,
"to": self.to_date
},
as_dict=1)
pos_purchase_invoices = frappe.db.sql("""
select
"Purchase Invoice" as payment_document, pi.name as payment_entry, pi.paid_amount as credit,
pi.posting_date, pi.supplier as against_account, pi.clearance_date,
account.account_currency, 0 as debit
from `tabPurchase Invoice` pi, `tabAccount` account
where
pi.cash_bank_account=%(account)s and pi.docstatus=1 and account.name = pi.cash_bank_account
and pi.posting_date >= %(from)s and pi.posting_date <= %(to)s
order by
pi.posting_date ASC, pi.name DESC
""", {
"account": self.account,
"from": self.from_date,
"to": self.to_date
},
as_dict=1)
entries = sorted(list(payment_entries) +
list(journal_entries + list(pos_sales_invoices) +
list(pos_purchase_invoices)),
key=lambda k: k['posting_date'] or getdate(nowdate()))
frappe.msgprint(_("Got " + str(len(entries)) + " entries."),
alert=True)
self.set('payment_entries', [])
self.total_amount = 0.0
for d in entries:
row = self.append('payment_entries', {})
amount = flt(d.get('debit', 0)) - flt(d.get('credit', 0))
d.flt_amount = amount
formatted_amount = fmt_money(abs(amount), 2, d.account_currency)
d.amount = formatted_amount + " " + \
(_("Dr") if amount > 0 else _("Cr"))
d.pop("credit")
d.pop("debit")
d.pop("account_currency")
row.update(d)
self.total_amount += flt(amount)
def format_date(date):
return getdate(date).strftime("%Y-%m-%d")
def get_data(self, party_naming_by, args):
from erpnext.accounts.utils import get_currency_precision
currency_precision = get_currency_precision() or 2
dr_or_cr = "debit" if args.get("party_type") == "Customer" else "credit"
voucher_details = self.get_voucher_details(args.get("party_type"))
future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
if not self.filters.get("company"):
self.filters["company"] = frappe.db.get_single_value('Global Defaults', 'default_company')
company_currency = frappe.db.get_value("Company", self.filters.get("company"), "default_currency")
return_entries = self.get_return_entries(args.get("party_type"))
data = []
for gle in self.get_entries_till(self.filters.report_date, args.get("party_type")):
if self.is_receivable_or_payable(gle, dr_or_cr, future_vouchers):
outstanding_amount, credit_note_amount = self.get_outstanding_amount(gle,
self.filters.report_date, dr_or_cr, return_entries, currency_precision)
if abs(outstanding_amount) > 0.1/10**currency_precision:
row = [gle.posting_date, gle.party]
# customer / supplier name
if party_naming_by == "Naming Series":
row += [self.get_party_name(gle.party_type, gle.party)]
# get due date
due_date = voucher_details.get(gle.voucher_no, {}).get("due_date", "")
# get tax no
# tax_no = voucher_details.get(gle.voucher_no, {}).get("tax_no", "")
# get resi number
# resi_number = voucher_details.get(gle.voucher_no, {}).get("resi_number", "")
# get date of receipt
receipt_date = voucher_details.get(gle.voucher_no, {}).get("date_of_receipt", "")
# row += [gle.voucher_type, gle.voucher_no, tax_no, resi_number, receipt_date, due_date]
row += [gle.voucher_type, gle.voucher_no, receipt_date, due_date]
# get supplier bill details
if args.get("party_type") == "Supplier":
row += [
voucher_details.get(gle.voucher_no, {}).get("bill_no", ""),
voucher_details.get(gle.voucher_no, {}).get("bill_date", "")
]
# invoiced and paid amounts
invoiced_amount = gle.get(dr_or_cr) if (gle.get(dr_or_cr) > 0) else 0
paid_amt = invoiced_amount - outstanding_amount - credit_note_amount
row += [invoiced_amount, paid_amt, credit_note_amount, outstanding_amount]
# ageing data
entry_date = due_date if self.filters.ageing_based_on == "Due Date" else gle.posting_date
row += get_ageing_data(cint(self.filters.range1), cint(self.filters.range2),
cint(self.filters.range3), self.age_as_on, entry_date, outstanding_amount)
# issue 6371-Ageing buckets should not have amounts if due date is not reached
if self.filters.ageing_based_on == "Due Date" \
and getdate(due_date) > getdate(self.filters.report_date):
row[-1]=row[-2]=row[-3]=row[-4]=0
if self.filters.get(scrub(args.get("party_type"))):
row.append(gle.account_currency)
else:
row.append(company_currency)
# customer territory / supplier type
if args.get("party_type") == "Customer":
row += [self.get_territory(gle.party), self.get_customer_group(gle.party)]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_type(gle.party)]
row.append(gle.remarks)
data.append(row)
return data
def get_entries_after(self, report_date, party_type): # returns a distinct list return list(set([(e.voucher_type, e.voucher_no) for e in self.get_gl_entries(party_type) if getdate(e.posting_date) > report_date]))
def __init__(self, filters=None):
self.filters = frappe._dict(filters or {})
self.filters.report_date = getdate(self.filters.report_date or nowdate())
self.age_as_on = getdate(nowdate()) \
if self.filters.report_date > getdate(nowdate()) \
else self.filters.report_date
def execute(filters=None):
if not filters: filters = {}
conditions, filters = get_conditions(filters)
columns = get_columns(filters)
delta = datetime.strptime(filters.get("to_date"),
'%Y-%m-%d') - datetime.strptime(
filters.get("from_date"), '%Y-%m-%d')
sdate = datetime.strptime(filters.get("from_date"), '%Y-%m-%d')
data = []
total_p = total_d = total_l = 0.0
total_all = 0.0
total_earl = 0.0
total_ext = 0.0
total_lat = 0.0
total_over = 0.0
ovr_rate = frappe.db.get_value("HR Settings", None, "overtime_hour_price")
if not ovr_rate:
frappe.throw(_("Add a value for Overtime Hour Rate in HR Settings"))
overtime_hour_price_in_holidays = frappe.db.get_value(
"HR Settings", None, "overtime_hour_price_in_holidays")
if not overtime_hour_price_in_holidays:
frappe.throw(
_("Add a value for Holiday and Leaves Overtime Hour Rate in HR Settings"
))
emp = filters.get("employee")
for i in range(delta.days + 1):
day = sdate + timedelta(days=i)
total_work_hrs, start_work, end_work, next_day, next_total_work_hrs = get_wsh_history(
filters.get("employee"), getdate(day))
if next_day and next_day == 1:
emp_map = frappe.db.sql(
"""select att.attendance_date, dept.departure_date,att.attendance_time, dept.departure_time,GREATEST(round(TIMESTAMPDIFF(MINUTE,att.attendance_time,dept.departure_time)/60,2),0) as total_hours ,GREATEST(24-round((TIMESTAMPDIFF(MINUTE,dept.departure_time,att.attendance_time))/60,3),0) as next_total_hrs ,att.status
from tabAttendance as att left join tabDeparture as dept on att.employee=dept.employee and date_add(att.attendance_date,interval 1 day) =dept.departure_date and dept.docstatus = 1 and att.docstatus=1 where att.docstatus=1 and dept.docstatus = 1 and att.employee= '{0}' and att.attendance_date = '{1}' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
else:
emp_map = frappe.db.sql(
"""select att.attendance_date, dept.departure_date,att.attendance_time, dept.departure_time,GREATEST(round(TIMESTAMPDIFF(MINUTE,att.attendance_time,dept.departure_time)/60,2),0) as total_hours ,GREATEST(24-round((TIMESTAMPDIFF(MINUTE,dept.departure_time,att.attendance_time))/60,3),0) as next_total_hrs ,att.status
from tabAttendance as att join tabDeparture as dept on att.employee=dept.employee and att.attendance_date=dept.departure_date and dept.docstatus = 1 and att.docstatus=1 where att.docstatus=1 and dept.docstatus = 1 and att.employee= '{0}' and att.attendance_date = '{1}' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
attend = ""
dep = ""
total_ho = 0
next_total_hrs = 0
status = ""
total = 0.0
attend = ""
dep = ""
total_ho = 0
next_total_hrs = 0
status = ""
if emp_map:
attend = emp_map[0].attendance_time
dep = emp_map[0].departure_time
total_ho = emp_map[0].total_hours
next_total_hrs = emp_map[0].next_total_hrs
status = emp_map[0].status
emp = emp_map[0]
if not start_work:
row = [
day.strftime("%m-%d-%Y"),
_(day.strftime("%A")), "", "", 0, 0,
_("On Holiday")
]
total = 0.0
else:
if next_day and next_day == 1:
row = [
day.strftime("%m-%d-%Y"),
_(day.strftime("%A")), attend, dep, next_total_hrs,
next_total_work_hrs, status
]
total = next_total_hrs
total_work_hrs = next_total_work_hrs
else:
row = [
day.strftime("%m-%d-%Y"),
_(day.strftime("%A")), attend, dep, total_ho,
total_work_hrs, status
]
total = total_ho
total_work_hrs = total_work_hrs
total_l += total_work_hrs
total_row = ['', '', '', '', '', round(total_l, 2), '']
if filters.get("morning_late"):
diff_klk = frappe.db.sql(
"""select ifnull(ex.late_diff,0) as late_diff from `tabExit permission` as ex where ex.employee ='{0}' and ex.permission_date = '{1}' and docstatus= 1 and permission_type = 'Morning Late' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
late_hrs = "0:00:00"
if diff_klk:
latt = datetime.strptime(diff_klk[0].late_diff, "%H:%M:%S")
late_hrs = str(diff_klk[0].late_diff)
total_lat += (latt.hour) + (latt.minute * 60) + latt.second
row += [str(late_hrs)]
total_row += [convert_hms_format(round(total_lat, 2))]
if filters.get("overtime"):
over_tt = frappe.db.sql(
"""select t.docstatus,employee,from_time,ifnull(sum(CASE WHEN type='compensatory' THEN hours END),0) as compensatory, ifnull(sum(CASE WHEN type='Normal' THEN hours END),0) as overtime_hours, ifnull(sum(CASE WHEN type='With Leave' THEN hours END),0) as holiday_overtime_hours,type from tabTimesheet as t join `tabTimesheet Detail` as td on t.name=td.parent and t.docstatus=1 where employee = '{0}' and date(from_time) ='{1}' group by date(from_time),employee """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
comp_over, over, sh_over = 0.0, 0.0, 0.0
if over_tt:
over_tt = over_tt[0]
if over_tt.compensatory and over_tt.compensatory != 0.0:
if total > total_work_hrs:
total = total - (total - (total_work_hrs or 0.0))
comp_over = float(over_tt.compensatory)
total += comp_over
#total_comp_over += comp_over
#frappe.msgprint(str(comp_over))
if over_tt.overtime_hours:
over = over_tt.overtime_hours * float(ovr_rate)
#total += 0.0
total_over += over
if over_tt.holiday_overtime_hours:
over = over_tt.holiday_overtime_hours * float(
overtime_hour_price_in_holidays)
#total += 0.0
total_over += over
sh_over = comp_over
if not comp_over or comp_over == 0.0: sh_over = over
row += [convert_hms_format(round(sh_over * 3600, 2))]
total_row += [convert_hms_format(round(total_over * 3600, 2))]
if filters.get("early_dep"):
if next_day and next_day == 1:
earl = frappe.db.sql(
"""select ifnull(ex.early_diff,0) as early_diff from `tabExit permission` as ex where ex.employee ='{0}' and DATE_SUB(ex.permission_date,interval 1 day)= '{1}' and docstatus= 1 and permission_type = 'Early Departure' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
else:
earl = frappe.db.sql(
"""select ifnull(ex.early_diff,0) as early_diff from `tabExit permission` as ex where ex.employee ='{0}' and ex.permission_date = '{1}' and docstatus= 1 and permission_type = 'Early Departure' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
early_departure = "0:00:00"
if earl and earl[0].early_diff:
early_departure = convert_hms_format(
round(float(earl[0].early_diff), 2))
total_earl += float(earl[0].early_diff)
row += [str(early_departure)]
total_row += [str(convert_hms_format(round(total_earl, 2)))]
if filters.get("ex_per"):
ex = frappe.db.sql(
"""select ifnull(ex.early_diff,0) as early_diff from `tabExit permission` as ex where ex.employee ='{0}' and ex.permission_date = '{1}' and docstatus= 1 and permission_type = 'Exit with return' """
.format(filters.get("employee"), getdate(day)),
as_dict=1)
ext_diff = 0.0
if ex:
ext_diff = float(ex[0].early_diff)
row += [convert_hms_format(round(ext_diff * 60, 2))]
discount_permissions_from_attendance_hours = frappe.db.get_value(
"HR Settings", None,
"discount_permissions_from_attendance_hours")
#if total > total_work_hrs and not comp_over:
# total= total - (total -total_work_hrs)
if ext_diff and discount_permissions_from_attendance_hours:
total -= ext_diff
total_ext += ext_diff
total_row += [str(convert_hms_format(round(total_ext, 2)))]
if total > total_work_hrs:
total = total_work_hrs
disc = 0
if total < total_work_hrs:
disc = total_work_hrs * 3600 - total * 3600
total_d += disc
if total <= 0:
total = 0.0
if filters.get("disc_hrs"):
row += [convert_hms_format(round(disc, 2))]
total_row += [str(convert_hms_format(round(total_d, 2)))]
row += [convert_hms_format(round(total * 3600, 2))]
total_all += total
total_row += [str(convert_hms_format(round(total_all * 3600, 2)))]
data.append(row)
data.append(total_row)
return columns, data
def get_entries_till(self, report_date, party_type): # returns a generator return (e for e in self.get_gl_entries(party_type) if getdate(e.posting_date) <= report_date)
def before_insert(self):
if not frappe.flags.in_test:
start_date = getdate(self.start_date)
today_date = getdate(today())
if start_date <= today_date:
self.start_date = today_date
def check_workstation_time(self, row):
workstation_doc = frappe.get_cached_doc("Workstation",
self.workstation)
if (not workstation_doc.working_hours or cint(
frappe.db.get_single_value("Manufacturing Settings",
"allow_overtime"))):
row.remaining_time_in_mins -= time_diff_in_minutes(
row.planned_end_time, row.planned_start_time)
self.update_time_logs(row)
return
start_date = getdate(row.planned_start_time)
start_time = get_time(row.planned_start_time)
new_start_date = workstation_doc.validate_workstation_holiday(
start_date)
if new_start_date != start_date:
row.planned_start_time = datetime.datetime.combine(
new_start_date, start_time)
start_date = new_start_date
total_idx = len(workstation_doc.working_hours)
for i, time_slot in enumerate(workstation_doc.working_hours):
workstation_start_time = datetime.datetime.combine(
start_date, get_time(time_slot.start_time))
workstation_end_time = datetime.datetime.combine(
start_date, get_time(time_slot.end_time))
if (get_datetime(row.planned_start_time) >= workstation_start_time
and get_datetime(
row.planned_start_time) <= workstation_end_time):
time_in_mins = time_diff_in_minutes(workstation_end_time,
row.planned_start_time)
# If remaining time fit in workstation time logs else split hours as per workstation time
if time_in_mins > row.remaining_time_in_mins:
row.planned_end_time = add_to_date(
row.planned_start_time,
minutes=row.remaining_time_in_mins)
row.remaining_time_in_mins = 0
else:
row.planned_end_time = add_to_date(row.planned_start_time,
minutes=time_in_mins)
row.remaining_time_in_mins -= time_in_mins
self.update_time_logs(row)
if total_idx != (i + 1) and row.remaining_time_in_mins > 0:
row.planned_start_time = datetime.datetime.combine(
start_date,
get_time(workstation_doc.working_hours[i +
1].start_time))
if row.remaining_time_in_mins > 0:
start_date = add_days(start_date, 1)
row.planned_start_time = datetime.datetime.combine(
start_date,
get_time(workstation_doc.working_hours[0].start_time))
def validate_date(doc):
if doc.repeat_this_event == 1 and doc.repeat_till and getdate(
doc.from_date) > getdate(doc.repeat_till):
frappe.throw(
_("Practitioner Event Repeat Till must be after From Date"))
def get_next_date(dt, mcount, day=None):
dt = getdate(dt)
dt += relativedelta(months=mcount, day=day)
return dt
def get_data(filters):
data = []
row = []
zero_invest = filters.get("zero")
cond_it, cond_itd, cond_ptrack = get_conditions(filters)
query = """SELECT it.name, it.portfolio, itd.date, itd.type_of_transaction, itd.investment_name,
itd.quantity, itd.price, itd.commission, itd.total
FROM `tabInvestment Transaction` it, `tabInvestment Transaction Detail` itd
WHERE it.docstatus = 1 AND itd.parent = it.name %s %s
ORDER BY itd.date""" % (cond_it, cond_itd)
invst_tran = frappe.db.sql(query, as_dict=1)
if not filters.get("Portfolio"):
portfolio = frappe.db.sql(
"""SELECT name FROM `tabPortfolio` WHERE docstatus=0""", as_list=1)
else:
portfolio = [[filters.get("Portfolio")]]
item_list = frappe.db.sql(
"""SELECT name, item_group, symbol FROM `tabItem` WHERE docstatus = 0""",
as_dict=1)
for portf in portfolio:
for item in item_list:
last_price = frappe.db.sql(
"""SELECT name, price, price_date FROM `tabPrice Tracker`
WHERE docstatus=0 AND investment = '%s' %s
ORDER BY price_date DESC LIMIT 1""" % (item.name, cond_ptrack),
as_dict=1)
transactions = 0
qty = 0
total_cost = 0
total_sell = 0
total_qty = 0
div = 0
dop = getdate("2199-12-31")
dos = getdate("1900-01-01")
tot_days = 0
for trans in invst_tran:
if item.name == trans.investment_name and portf[
0] == trans.portfolio:
transactions += 1
if trans.get("type_of_transaction") == "Buy":
qty += trans.get("quantity")
total_cost += trans.get("total")
total_qty += trans.get("quantity")
if trans.get("date") < dop:
dop = trans.get("date")
elif trans.get("type_of_transaction") == "Sell":
qty -= trans.get("quantity")
total_sell += trans.get("total")
if trans.get("date") > dos:
dos = trans.get("date")
elif trans.get("type_of_transaction") == "Dividend":
div += trans.get("total")
if transactions > 0:
if dos < dop:
dos = getdate(nowdate())
tot_days = (dos - dop).days
av_price = total_cost / total_qty
if qty > 0:
if total_sell == 0:
profit = 0
unrel_profit = (qty * last_price[0].price) - total_cost
else:
unrel_profit = qty * (last_price[0].price - av_price)
profit = total_sell - total_cost
else:
profit = total_sell - total_cost
unrel_profit = 0
roi = (unrel_profit + profit + div) / total_cost
ann_roi = pow((1 + roi), (365 / tot_days)) - 1
if zero_invest == 1:
row = [
portf[0], item.name, item.item_group, item.symbol, qty,
av_price, total_cost, profit, unrel_profit, div,
unrel_profit + profit + div, roi * 100, ann_roi * 100,
last_price[0].price_date, last_price[0].price,
qty * last_price[0].price
]
data.append(row)
else:
if qty > 0:
row = [
portf[0], item.name, item.item_group, item.symbol,
qty, av_price, total_cost, profit, unrel_profit,
div, unrel_profit + profit + div, roi * 100,
ann_roi * 100, last_price[0].price_date,
last_price[0].price, qty * last_price[0].price
]
data.append(row)
return data
def is_completed(self):
return self.end_date and getdate(self.end_date) < getdate(today())
def validate_mandatory(self):
# validate transaction date v/s delivery date
if self.c_insurance_expiry_date:
if getdate(self.c_insurance_expiry_date) < getdate(utils.today()):
frappe.throw(_("Insurance Expiry Date cannot be a past date"))
def set_expiry(row):
expiry_in_days = (row.expiry_date - getdate()).days if row.expiry_date else None
return merge(row, {"expiry_in_days": expiry_in_days})
def on_submit(self):
frappe.db.set_value(self.doctype, self.name, "submitted_date",
getdate())
insert_lab_test_to_medical_record(self)
frappe.db.set_value("Lab Test", self.name, "status", "Completed")
def validate(self):
if getdate(self.from_date) > getdate(self.to_date):
frappe.throw(_("To date needs to be before from date"))
def get_working_total_hours(employee=None, start_date=None, end_date=None):
from erpnext.hr import get_compensatory, get_permissions
from datetime import date, timedelta
from datetime import datetime
cur_date = getdate(start_date)
if start_date and ( isinstance(start_date, str) or isinstance(start_date, unicode)) :
start_date = datetime.strptime(start_date , '%Y-%m-%d')
if end_date and ( isinstance(end_date, str) or isinstance(end_date, unicode)):
end_date= datetime.strptime(end_date , '%Y-%m-%d')
month = cur_date.month
year = cur_date.year
total_hrs, real_hrs, total_real_hrs= 0.0, 0.0, 0.0
delta = cint(monthrange( cint(year), cint(month) )[1])
if end_date and start_date:
delta = end_date- start_date
delta=delta.days
sdate= getdate(start_date)
total_ho=0
next_total_hrs=0
status=""
total_work_hrss=0
total_d=0
disc=0
total_all=0
total_l=0
from erpnext.hr.report.monthly_attendance_sheet.monthly_attendance_sheet import get_wsh_history
for i in range(delta + 1):
total=0
day = sdate + timedelta(days=i)
total_work_hrs, start_work, end_work,next_day,next_total_work_hrs = get_wsh_history(employee, getdate(day))
if next_day and next_day==1:
emp_map = frappe.db.sql("""select att.attendance_date, dept.departure_date,att.attendance_time, dept.departure_time,GREATEST(round(TIMESTAMPDIFF(MINUTE,att.attendance_time,dept.departure_time)/60,2),0) as total_hours ,GREATEST(24-round((TIMESTAMPDIFF(MINUTE,dept.departure_time,att.attendance_time))/60,3),0) as next_total_hrs ,att.status
from tabAttendance as att left join tabDeparture as dept on att.employee=dept.employee and date_add(att.attendance_date,interval 1 day) =dept.departure_date and dept.docstatus = 1 and att.docstatus=1 where att.docstatus=1 and dept.docstatus = 1 and att.employee= '{0}' and att.attendance_date = '{1}' """.format(employee,getdate(day)), as_dict=1)
else:
emp_map = frappe.db.sql("""select att.attendance_date, dept.departure_date,att.attendance_time, dept.departure_time,GREATEST(round(TIMESTAMPDIFF(MINUTE,att.attendance_time,dept.departure_time)/60,2),0) as total_hours ,GREATEST(24-round((TIMESTAMPDIFF(MINUTE,dept.departure_time,att.attendance_time))/60,3),0) as next_total_hrs ,att.status
from tabAttendance as att join tabDeparture as dept on att.employee=dept.employee and att.attendance_date=dept.departure_date and dept.docstatus = 1 and att.docstatus=1 where att.docstatus=1 and dept.docstatus = 1 and att.employee= '{0}' and att.attendance_date = '{1}' """.format(employee,getdate(day)), as_dict=1)
attend=""
attend=""
dep=""
total_ho=0
next_total_hrs=0
status=""
total=0.0
attend=""
dep=""
total_ho=0
next_total_hrs=0
status=""
if emp_map:
attend=emp_map[0].attendance_time
dep=emp_map[0].departure_time
total_ho=emp_map[0].total_hours
next_total_hrs=emp_map[0].next_total_hrs
status=emp_map[0].status
emp=emp_map[0]
else:
total_work_hrs=0
next_total_work_hrs =0
if not start_work:
total= 0.0
else:
if next_day and next_day==1:
total= next_total_hrs
total_work_hrs=next_total_work_hrs
else:
total= total_ho
total_work_hrs=total_work_hrs
total_all +=total
total_l+=total_work_hrs
compensatory_total_hours= get_compensatory(employee, start_date, end_date)
permission_total_hours= get_permissions(employee, start_date, end_date)
#if total_hrs > total_real_hrs : total_hrs= total_real_hrs
total_work_hrss = total_work_hrss
disc=0
total_all = total_all + compensatory_total_hours - permission_total_hours
if float(total_all) < float(total_l):
disc = float(total_l)- float(total_all)
if total_all <= 0:
total_all = 0.0
if disc < 0.0 : disc = 0.0
return total_l , disc ,total_all, compensatory_total_hours, permission_total_hours
def update_status(status, name):
frappe.db.sql(
"""update `tabLab Test` set status=%s, approved_date=%s where name = %s""",
(status, getdate(), name))
def make_gl_entries(self, cancel=0, adv_adj=0):
gle_map = []
loan_details = frappe.get_doc("Loan", self.against_loan)
if self.penalty_amount:
gle_map.append(
self.get_gl_dict({
"account": loan_details.loan_account,
"against": loan_details.payment_account,
"debit": self.penalty_amount,
"debit_in_account_currency": self.penalty_amount,
"against_voucher_type": "Loan",
"against_voucher": self.against_loan,
"remarks": _("Penalty against loan:") + self.against_loan,
"cost_center": self.cost_center,
"party_type": self.applicant_type,
"party": self.applicant,
"posting_date": getdate(self.posting_date)
}))
gle_map.append(
self.get_gl_dict({
"account": loan_details.penalty_income_account,
"against": loan_details.payment_account,
"credit": self.penalty_amount,
"credit_in_account_currency": self.penalty_amount,
"against_voucher_type": "Loan",
"against_voucher": self.against_loan,
"remarks": _("Penalty against loan:") + self.against_loan,
"cost_center": self.cost_center,
"posting_date": getdate(self.posting_date)
}))
gle_map.append(
self.get_gl_dict({
"account":
loan_details.payment_account,
"against":
loan_details.loan_account + ", " +
loan_details.interest_income_account + ", " +
loan_details.penalty_income_account,
"debit":
self.amount_paid,
"debit_in_account_currency":
self.amount_paid,
"against_voucher_type":
"Loan",
"against_voucher":
self.against_loan,
"remarks":
_("Repayment against Loan: ") + self.against_loan,
"cost_center":
self.cost_center,
"posting_date":
getdate(self.posting_date)
}))
gle_map.append(
self.get_gl_dict({
"account": loan_details.loan_account,
"party_type": loan_details.applicant_type,
"party": loan_details.applicant,
"against": loan_details.payment_account,
"credit": self.amount_paid,
"credit_in_account_currency": self.amount_paid,
"against_voucher_type": "Loan",
"against_voucher": self.against_loan,
"remarks": _("Repayment against Loan: ") + self.against_loan,
"cost_center": self.cost_center,
"posting_date": getdate(self.posting_date)
}))
if gle_map:
make_gl_entries(gle_map,
cancel=cancel,
adv_adj=adv_adj,
merge_entries=False)
def test_inpatient_medication_orders_report(self):
filters = {
'company': '_Test Company',
'from_date': getdate(),
'to_date': getdate(),
'patient': '_Test IPD Patient',
'service_unit': 'Test Service Unit Ip Occupancy - _TC'
}
report = execute(filters)
expected_data = [{
'patient':
'_Test IPD Patient',
'inpatient_record':
self.ip_record.name,
'practitioner':
None,
'drug':
'Dextromethorphan',
'drug_name':
'Dextromethorphan',
'dosage':
1.0,
'dosage_form':
'Tablet',
'date':
getdate(),
'time':
datetime.timedelta(seconds=32400),
'is_completed':
0,
'healthcare_service_unit':
'Test Service Unit Ip Occupancy - _TC'
}, {
'patient':
'_Test IPD Patient',
'inpatient_record':
self.ip_record.name,
'practitioner':
None,
'drug':
'Dextromethorphan',
'drug_name':
'Dextromethorphan',
'dosage':
1.0,
'dosage_form':
'Tablet',
'date':
getdate(),
'time':
datetime.timedelta(seconds=50400),
'is_completed':
0,
'healthcare_service_unit':
'Test Service Unit Ip Occupancy - _TC'
}, {
'patient':
'_Test IPD Patient',
'inpatient_record':
self.ip_record.name,
'practitioner':
None,
'drug':
'Dextromethorphan',
'drug_name':
'Dextromethorphan',
'dosage':
1.0,
'dosage_form':
'Tablet',
'date':
getdate(),
'time':
datetime.timedelta(seconds=75600),
'is_completed':
0,
'healthcare_service_unit':
'Test Service Unit Ip Occupancy - _TC'
}]
self.assertEqual(expected_data, report[1])
filters = frappe._dict(from_date=getdate(),
to_date=getdate(),
from_time='',
to_time='')
ipme = create_ipme(filters)
ipme.submit()
filters = {
'company': '_Test Company',
'from_date': getdate(),
'to_date': getdate(),
'patient': '_Test IPD Patient',
'service_unit': 'Test Service Unit Ip Occupancy - _TC',
'show_completed_orders': 0
}
report = execute(filters)
self.assertEqual(len(report[1]), 0)
def validate_batch(self):
if self.batch_no and self.voucher_type != "Stock Entry":
expiry_date = frappe.db.get_value("Batch", self.batch_no, "expiry_date")
if expiry_date:
if getdate(self.posting_date) > getdate(expiry_date):
frappe.throw(_("Batch {0} of Item {1} has expired.").format(self.batch_no, self.item_code))
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
def get_leave_allocation_records(date, employee=None):
from datetime import date as mod_date
conditions = (" and employee='%s'" % employee) if employee else ""
from_date = date
from_date = getdate(mod_date(getdate(from_date).year, 1, 1))
leave_allocation_records = frappe.db.sql("""
select employee, leave_type, total_leaves_allocated, from_date, to_date
from `tabLeave Allocation`
where %s between from_date and to_date and docstatus=1 {0}""".format(
conditions), (date),
as_dict=1)
allocated_leaves = frappe._dict()
for d in leave_allocation_records:
# Ver 1.0 Begins added by SSK on 20/08/2016, Latest allocation record for Earned Leave is handled later
if d.leave_type != 'Earned Leave':
allocated_leaves.setdefault(d.employee, frappe._dict()).setdefault(
d.leave_type,
frappe._dict({
"from_date": d.from_date,
"to_date": d.to_date,
"total_leaves_allocated": d.total_leaves_allocated
}))
# Ver 1.0 Begins added by SSK on 20/08/2016, Earned Leave
if employee:
leave_allocation_records = frappe.db.sql("""
select employee, leave_type, total_leaves_allocated, from_date, to_date
from `tabLeave Allocation`
where from_date between %s and %s
and docstatus=1 and leave_type = 'Earned Leave'
{0}
order by to_date desc limit 1""".format(conditions),
(from_date, date),
as_dict=1)
for d in leave_allocation_records:
if d.leave_type == 'Earned Leave':
allocated_leaves.setdefault(d.employee,
frappe._dict()).setdefault(
d.leave_type,
frappe._dict({
"from_date":
d.from_date,
"to_date":
d.to_date,
"total_leaves_allocated":
d.total_leaves_allocated
}))
else:
employee_list = frappe.db.sql("""
select distinct employee
from `tabLeave Allocation`
where from_date between %s and %s
and docstatus=1 and leave_type = 'Earned Leave'
""", (from_date, date),
as_dict=1)
for e in employee_list:
leave_allocation_records = frappe.db.sql(
"""
select employee, leave_type, total_leaves_allocated, from_date, to_date
from `tabLeave Allocation`
where employee = %s
and from_date between %s and %s
and docstatus=1 and leave_type = 'Earned Leave'
order by to_date desc limit 1""",
(e.employee, from_date, date),
as_dict=1)
for d in leave_allocation_records:
if d.leave_type == 'Earned Leave':
allocated_leaves.setdefault(
d.employee, frappe._dict()).setdefault(
d.leave_type,
frappe._dict({
"from_date":
d.from_date,
"to_date":
d.to_date,
"total_leaves_allocated":
d.total_leaves_allocated
}))
# Ver 1.0 Ends
return allocated_leaves
def validate_joining_date(self):
for e in self.get('employees'):
joining_date = getdate(frappe.db.get_value("Employee", e.employee, "date_of_joining"))
if getdate(self.from_date) < joining_date:
frappe.throw(_("From Date in Salary Structure cannot be lesser than Employee Joining Date."))
