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_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."))