def _get_account_type_based_data(filters, account_names, period_list, accumulated_values, opening_balances=0): from erpnext.accounts.report.cash_flow.cash_flow import get_start_date company = filters.company data = {} total = 0 for period in period_list: start_date = get_start_date(period, accumulated_values, company) accounts = ', '.join(['"%s"' % d for d in account_names]) if opening_balances: date_info = dict(date=start_date) months_map = {'Monthly': -1, 'Quarterly': -3, 'Half-Yearly': -6} years_map = {'Yearly': -1} if months_map.get(filters.periodicity): date_info.update(months=months_map[filters.periodicity]) else: date_info.update(years=years_map[filters.periodicity]) if accumulated_values: start, end = add_to_date(start_date, years=-1), add_to_date(period['to_date'], years=-1) else: start, end = add_to_date(**date_info), add_to_date(**date_info) gl_sum = frappe.db.sql_list(""" select sum(credit) - sum(debit) from `tabGL Entry` where company=%s and posting_date >= %s and posting_date <= %s and voucher_type != 'Period Closing Voucher' and account in ( SELECT name FROM tabAccount WHERE name IN (%s) OR parent_account IN (%s)) """, (company, start, end, accounts, accounts)) else: gl_sum = frappe.db.sql_list(""" select sum(credit) - sum(debit) from `tabGL Entry` where company=%s and posting_date >= %s and posting_date <= %s and voucher_type != 'Period Closing Voucher' and account in ( SELECT name FROM tabAccount WHERE name IN (%s) OR parent_account IN (%s)) """, (company, start_date if accumulated_values else period['from_date'], period['to_date'], accounts, accounts)) if gl_sum and gl_sum[0]: amount = gl_sum[0] else: amount = 0 total += amount data.setdefault(period["key"], amount) data["total"] = total return data
def get_period_date_ranges(self): from dateutil.relativedelta import relativedelta, MO from_date, to_date = getdate(self.filters.from_date), getdate(self.filters.to_date) increment = { "Monthly": 1, "Quarterly": 3, "Half-Yearly": 6, "Yearly": 12 }.get(self.filters.range, 1) if self.filters.range in ['Monthly', 'Quarterly']: from_date = from_date.replace(day = 1) elif self.filters.range == "Yearly": from_date = get_fiscal_year(from_date)[1] else: from_date = from_date + relativedelta(from_date, weekday=MO(-1)) self.periodic_daterange = [] for dummy in range(1, 53): if self.filters.range == "Weekly": period_end_date = add_days(from_date, 6) else: period_end_date = add_to_date(from_date, months=increment, days=-1) if period_end_date > to_date: period_end_date = to_date self.periodic_daterange.append(period_end_date) from_date = add_days(period_end_date, 1) if period_end_date == to_date: break
def get_end_date(start_date, frequency): start_date = getdate(start_date) frequency = frequency.lower() if frequency else 'monthly' kwargs = get_frequency_kwargs(frequency) if frequency != 'bimonthly' else get_frequency_kwargs('monthly') # weekly, fortnightly and daily intervals have fixed days so no problems end_date = add_to_date(start_date, **kwargs) - relativedelta(days=1) if frequency != 'bimonthly': return dict(end_date=end_date.strftime(DATE_FORMAT)) else: return dict(end_date='')
def get_date_from_string(seleted_timespan): """return string for ex:this week as date:string""" days = months = years = 0 if "month" == seleted_timespan.lower(): months = -1 elif "quarter" == seleted_timespan.lower(): months = -3 elif "year" == seleted_timespan.lower(): years = -1 else: days = -7 return add_to_date(None, years=years, months=months, days=days, as_string=True, as_datetime=True)
def get_between_date_filter(value): ''' return the formattted date as per the given example [u'2017-11-01', u'2017-11-03'] => '2017-11-01 00:00:00.000000' AND '2017-11-04 00:00:00.000000' ''' from_date = None to_date = None if value and isinstance(value, (list, tuple)): if len(value) >= 1: from_date = value[0] if len(value) >= 2: to_date = value[1] data = "'%s' AND '%s'" % ( get_datetime(from_date).strftime("%Y-%m-%d %H:%M:%S.%f"), add_to_date(get_datetime(to_date),days=1).strftime("%Y-%m-%d %H:%M:%S.%f")) return data
def get_data(filters): start_date = getdate(filters.from_date) data = [] time_slot_wise_total_count = {} while(start_date <= getdate(filters.to_date)): hours_count = {'date': start_date} for key, value in time_slots.items(): start_time, end_time = value.split('-') start_time = get_datetime("{0} {1}".format(start_date.strftime("%Y-%m-%d"), start_time)) end_time = get_datetime("{0} {1}".format(start_date.strftime("%Y-%m-%d"), end_time)) hours_count[key] = get_hours_count(start_time, end_time) time_slot_wise_total_count[key] = time_slot_wise_total_count.get(key, 0) + hours_count[key] if hours_count: data.append(hours_count) start_date = add_to_date(start_date, days=1) return data, time_slot_wise_total_count
def test_disable_scheduler_on_expiry(self): update_limits({'expiry': add_to_date(today(), days=-1)}) frappe.local.conf = _dict(frappe.get_site_config()) if not frappe.db.exists('User', '*****@*****.**'): user = frappe.new_doc('User') user.email = '*****@*****.**' user.first_name = 'Test_scheduler' user.save() user.add_roles('System Manager') frappe.db.commit() frappe.set_user("*****@*****.**") disable_scheduler_on_expiry() ss = frappe.get_doc("System Settings") self.assertFalse(ss.enable_scheduler) clear_limit("expiry") frappe.local.conf = _dict(frappe.get_site_config())
def get_download_url(file_url, expire_on=None): try: _file = frappe.get_doc("File", {"file_url": file_url}) if not is_downloadable_file(_file): raise frappe.PermissionError url = None if _file.is_private: token = make_jwt(frappe.session.user, expire_on=expire_on or add_to_date(date=now_datetime(), hours=3)) url = "{}?token={}".format(file_url, token) else: url = file_url return {"status": "success", "url": url} except frappe.PermissionError: frappe.msgprint( "You don't have enough permissions to download the file") return { "status": "forbidden", "msg": "You dont have enough permission to download the file" }
def get_period_date_ranges(self): from dateutil.relativedelta import relativedelta, MO from_date, to_date = getdate(self.filters.from_date), getdate( self.filters.to_date) increment = { 'Monthly': 1, 'Quarterly': 3, 'Half-Yearly': 6, 'Yearly': 12 }.get(self.filters.range, 1) if self.filters.range in ['Monthly', 'Quarterly']: from_date = from_date.replace(day=1) elif self.filters.range == 'Yearly': from_date = get_fiscal_year(from_date)[1] else: from_date = from_date + relativedelta(from_date, weekday=MO(-1)) self.periodic_daterange = [] for dummy in range(1, 53): if self.filters.range == 'Weekly': period_end_date = add_days(from_date, 6) else: period_end_date = add_to_date(from_date, months=increment, days=-1) if period_end_date > to_date: period_end_date = to_date self.periodic_daterange.append(period_end_date) from_date = add_days(period_end_date, 1) if period_end_date == to_date: break
def get_between_date_filter(value, df=None): ''' return the formattted date as per the given example [u'2017-11-01', u'2017-11-03'] => '2017-11-01 00:00:00.000000' AND '2017-11-04 00:00:00.000000' ''' from_date = None to_date = None date_format = "%Y-%m-%d %H:%M:%S.%f" if df: date_format = "%Y-%m-%d %H:%M:%S.%f" if df.fieldtype == 'Datetime' else "%Y-%m-%d" if value and isinstance(value, (list, tuple)): if len(value) >= 1: from_date = value[0] if len(value) >= 2: to_date = value[1] if not df or (df and df.fieldtype == 'Datetime'): to_date = add_to_date(to_date,days=1) data = "'%s' AND '%s'" % ( get_datetime(from_date).strftime(date_format), get_datetime(to_date).strftime(date_format)) return data
def test_partial_restore(self): _now = now() for num in range(10): frappe.get_doc({ "doctype": "ToDo", "date": add_to_date(_now, days=num), "description": frappe.mock("paragraph") }).insert() frappe.db.commit() todo_count = frappe.db.count("ToDo") # check if todos exist, create a partial backup and see if the state is the same after restore self.assertIsNot(todo_count, 0) self.execute("bench --site {site} backup --only 'ToDo'") db_path = fetch_latest_backups(partial=True)["database"] self.assertTrue("partial" in db_path) frappe.db.sql_ddl("DROP TABLE IF EXISTS `tabToDo`") frappe.db.commit() self.execute("bench --site {site} partial-restore {path}", {"path": db_path}) self.assertEqual(self.returncode, 0) self.assertEqual(frappe.db.count("ToDo"), todo_count)
def _get_account_type_based_data(filters, account_names, period_list, accumulated_values, opening_balances=0): from erpnext.accounts.report.cash_flow.cash_flow import get_start_date company = filters.company data = {} total = 0 for period in period_list: start_date = get_start_date(period, accumulated_values, company) accounts = ', '.join(['"%s"' % d for d in account_names]) if opening_balances: date_info = dict(date=start_date) months_map = {'Monthly': -1, 'Quarterly': -3, 'Half-Yearly': -6} years_map = {'Yearly': -1} if months_map.get(filters.periodicity): date_info.update(months=months_map[filters.periodicity]) else: date_info.update(years=years_map[filters.periodicity]) if accumulated_values: start, end = add_to_date(start_date, years=-1), add_to_date( period['to_date'], years=-1) else: start, end = add_to_date(**date_info), add_to_date(**date_info) gl_sum = frappe.db.sql_list( """ select sum(credit) - sum(debit) from `tabGL Entry` where company=%s and posting_date >= %s and posting_date <= %s and voucher_type != 'Period Closing Voucher' and account in ( SELECT name FROM tabAccount WHERE name IN (%s) OR parent_account IN (%s)) """, (company, start, end, accounts, accounts)) else: gl_sum = frappe.db.sql_list( """ select sum(credit) - sum(debit) from `tabGL Entry` where company=%s and posting_date >= %s and posting_date <= %s and voucher_type != 'Period Closing Voucher' and account in ( SELECT name FROM tabAccount WHERE name IN (%s) OR parent_account IN (%s)) """, (company, start_date if accumulated_values else period['from_date'], period['to_date'], accounts, accounts)) if gl_sum and gl_sum[0]: amount = gl_sum[0] else: amount = 0 total += amount data.setdefault(period["key"], amount) data["total"] = total return data
def quartely(date, idx): return add_to_date(date, months=cint(idx) * 3)
def _get_account_type_based_data( filters, account_names, period_list, accumulated_values, opening_balances=0 ): if not account_names or not account_names[0] or not type(account_names[0]) == str: # only proceed if account_names is a list of account names return {} from erpnext.accounts.report.cash_flow.cash_flow import get_start_date company = filters.company data = {} total = 0 GLEntry = frappe.qb.DocType("GL Entry") Account = frappe.qb.DocType("Account") for period in period_list: start_date = get_start_date(period, accumulated_values, company) account_subquery = ( frappe.qb.from_(Account) .where((Account.name.isin(account_names)) | (Account.parent_account.isin(account_names))) .select(Account.name) .as_("account_subquery") ) if opening_balances: date_info = dict(date=start_date) months_map = {"Monthly": -1, "Quarterly": -3, "Half-Yearly": -6} years_map = {"Yearly": -1} if months_map.get(filters.periodicity): date_info.update(months=months_map[filters.periodicity]) else: date_info.update(years=years_map[filters.periodicity]) if accumulated_values: start, end = add_to_date(start_date, years=-1), add_to_date(period["to_date"], years=-1) else: start, end = add_to_date(**date_info), add_to_date(**date_info) start, end = get_date_str(start), get_date_str(end) else: start, end = start_date if accumulated_values else period["from_date"], period["to_date"] start, end = get_date_str(start), get_date_str(end) result = ( frappe.qb.from_(GLEntry) .select(Sum(GLEntry.credit) - Sum(GLEntry.debit)) .where( (GLEntry.company == company) & (GLEntry.posting_date >= start) & (GLEntry.posting_date <= end) & (GLEntry.voucher_type != "Period Closing Voucher") & (GLEntry.account.isin(account_subquery)) ) ).run() if result and result[0]: gl_sum = result[0][0] else: gl_sum = 0 total += flt(gl_sum) data.setdefault(period["key"], flt(gl_sum)) data["total"] = total return data
def yearly(date, idx): return add_to_date(date, years=cint(idx))
def test_recurring_invoice(self): from frappe.utils import get_first_day, get_last_day, add_to_date, nowdate, getdate from erpnext.accounts.utils import get_fiscal_year today = nowdate() base_si = frappe.copy_doc(test_records[0]) base_si.update({ "convert_into_recurring_invoice": 1, "recurring_type": "Monthly", "notification_email_address": "[email protected], [email protected], [email protected]", "repeat_on_day_of_month": getdate(today).day, "posting_date": today, "fiscal_year": get_fiscal_year(today)[0], "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(today) }) # monthly si1 = frappe.copy_doc(base_si) si1.insert() si1.submit() self._test_recurring_invoice(si1, True) # monthly without a first and last day period si2 = frappe.copy_doc(base_si) si2.update({ "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, days=30) }) si2.insert() si2.submit() self._test_recurring_invoice(si2, False) # quarterly si3 = frappe.copy_doc(base_si) si3.update({ "recurring_type": "Quarterly", "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(add_to_date(today, months=3)) }) si3.insert() si3.submit() self._test_recurring_invoice(si3, True) # quarterly without a first and last day period si4 = frappe.copy_doc(base_si) si4.update({ "recurring_type": "Quarterly", "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, months=3) }) si4.insert() si4.submit() self._test_recurring_invoice(si4, False) # yearly si5 = frappe.copy_doc(base_si) si5.update({ "recurring_type": "Yearly", "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(add_to_date(today, years=1)) }) si5.insert() si5.submit() self._test_recurring_invoice(si5, True) # yearly without a first and last day period si6 = frappe.copy_doc(base_si) si6.update({ "recurring_type": "Yearly", "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, years=1) }) si6.insert() si6.submit() self._test_recurring_invoice(si6, False) # change posting date but keep recuring day to be today si7 = frappe.copy_doc(base_si) si7.update({ "posting_date": add_to_date(today, days=-1) }) si7.insert() si7.submit() # setting so that _test function works si7.posting_date = today self._test_recurring_invoice(si7, True)
def get_expected_time_for(parameter, service_level, start_date_time): current_date_time = start_date_time expected_time = current_date_time start_time = None end_time = None # lets assume response time is in days by default if parameter == 'response': allotted_days = service_level.response_time time_period = service_level.response_time_period elif parameter == 'resolution': allotted_days = service_level.resolution_time time_period = service_level.resolution_time_period else: frappe.throw(_("{0} parameter is invalid".format(parameter))) allotted_hours = 0 if time_period == 'Hour': allotted_hours = allotted_days allotted_days = 0 elif time_period == 'Week': allotted_days *= 7 expected_time_is_set = 1 if allotted_days == 0 and time_period in [ 'Day', 'Week' ] else 0 support_days = {} for service in service_level.support_and_resolution: support_days[service.workday] = frappe._dict({ 'start_time': service.start_time, 'end_time': service.end_time, }) holidays = get_holidays(service_level.holiday_list) weekdays = get_weekdays() while not expected_time_is_set: current_weekday = weekdays[current_date_time.weekday()] if not is_holiday(current_date_time, holidays) and current_weekday in support_days: start_time = current_date_time - datetime( current_date_time.year, current_date_time.month, current_date_time.day ) if getdate(current_date_time) == getdate( start_date_time) else support_days[current_weekday].start_time end_time = support_days[current_weekday].end_time time_left_today = time_diff_in_hours(end_time, start_time) # no time left for support today if time_left_today < 0: pass elif time_period == 'Hour': if time_left_today >= allotted_hours: expected_time = datetime.combine( getdate(current_date_time), get_time(start_time)) expected_time = add_to_date(expected_time, hours=allotted_hours) expected_time_is_set = 1 else: allotted_hours = allotted_hours - time_left_today else: allotted_days -= 1 expected_time_is_set = allotted_days <= 0 current_date_time = add_to_date(current_date_time, days=1) if end_time and time_period != 'Hour': current_date_time = datetime.combine(getdate(current_date_time), get_time(end_time)) else: current_date_time = expected_time return current_date_time
def setUp(self): create_loan_accounts() create_loan_type("Demand Loan", 2000000, 13.5, 25, 0, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_security_type() create_loan_security() create_loan_security_price("Test Security 1", 500, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) create_loan_security_price("Test Security 2", 250, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) if not frappe.db.exists("Customer", "_Test Loan Customer"): frappe.get_doc(get_customer_dict('_Test Loan Customer')).insert(ignore_permissions=True) self.applicant = frappe.db.get_value("Customer", {'name': '_Test Loan Customer'}, 'name')
def setUp(self): create_loan_accounts() create_loan_type("Personal Loan", 500000, 8.4, is_term_loan=1, mode_of_payment='Cash', payment_account='Payment Account - _TC', loan_account='Loan Account - _TC', interest_income_account='Interest Income Account - _TC', penalty_income_account='Penalty Income Account - _TC') create_loan_type("Stock Loan", 2000000, 13.5, 25, 1, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_type("Demand Loan", 2000000, 13.5, 25, 0, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_security_type() create_loan_security() create_loan_security_price("Test Security 1", 500, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) create_loan_security_price("Test Security 2", 250, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) self.applicant1 = make_employee("*****@*****.**") if not frappe.db.exists("Customer", "_Test Loan Customer"): frappe.get_doc(get_customer_dict('_Test Loan Customer')).insert(ignore_permissions=True) self.applicant2 = frappe.db.get_value("Customer", {'name': '_Test Loan Customer'}, 'name') create_loan(self.applicant1, "Personal Loan", 280000, "Repay Over Number of Periods", 20)
def add_comment(comment, comment_email, comment_by, reference_doctype, reference_name, route): doc = frappe.get_doc(reference_doctype, reference_name) if not comment.strip(): frappe.msgprint(_('The comment cannot be empty')) return False url_regex = re.compile( r"http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\(\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+", re.IGNORECASE) email_regex = re.compile( r"(^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$)", re.IGNORECASE) if url_regex.search(comment) or email_regex.search(comment): frappe.msgprint(_('Comments cannot have links or email addresses')) return False if not comment_email == frappe.session.user: comment_email = frappe.session.user comments_count = frappe.db.count( "Comment", { "comment_type": "Comment", "comment_email": frappe.session.user, "creation": (">", add_to_date(now(), hours=-1)) }) if comments_count > 20: frappe.msgprint( _('Hourly comment limit reached for: {0}').format( frappe.bold(frappe.session.user))) return False comment = doc.add_comment(text=comment, comment_email=comment_email, comment_by=comment_by) comment.db_set('published', 1) # since comments are embedded in the page, clear the web cache if route: clear_cache(route) content = ( comment.content + "<p><a href='{0}/app/Form/Comment/{1}' style='font-size: 80%'>{2}</a></p>" .format(frappe.utils.get_request_site_address(), comment.name, _("View Comment"))) # notify creator frappe.sendmail(recipients=frappe.db.get_value('User', doc.owner, 'email') or doc.owner, subject=_('New Comment on {0}: {1}').format( doc.doctype, doc.name), message=content, reference_doctype=doc.doctype, reference_name=doc.name) # revert with template if all clear (no backlinks) template = frappe.get_template("templates/includes/comments/comment.html") return template.render({"comment": comment.as_dict()})
def get_next_expected_date(date, timegrain): next_date = None # given date is always assumed to be the period ending date next_date = get_period_ending(add_to_date(date, days=1), timegrain) return getdate(next_date)
def biweekly(date, idx): return add_to_date(date, days=cint(idx) * 14)
def test_recurring_invoice(self): from frappe.utils import get_first_day, get_last_day, add_to_date, nowdate, getdate from erpnext.accounts.utils import get_fiscal_year today = nowdate() base_si = frappe.copy_doc(test_records[0]) base_si.update({ "convert_into_recurring_invoice": 1, "recurring_type": "Monthly", "notification_email_address": "[email protected], [email protected], [email protected]", "repeat_on_day_of_month": getdate(today).day, "posting_date": today, "fiscal_year": get_fiscal_year(today)[0], "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(today) }) # monthly si1 = frappe.copy_doc(base_si) si1.insert() si1.submit() self._test_recurring_invoice(si1, True) # monthly without a first and last day period si2 = frappe.copy_doc(base_si) si2.update({ "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, days=30) }) si2.insert() si2.submit() self._test_recurring_invoice(si2, False) # quarterly si3 = frappe.copy_doc(base_si) si3.update({ "recurring_type": "Quarterly", "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(add_to_date(today, months=3)) }) si3.insert() si3.submit() self._test_recurring_invoice(si3, True) # quarterly without a first and last day period si4 = frappe.copy_doc(base_si) si4.update({ "recurring_type": "Quarterly", "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, months=3) }) si4.insert() si4.submit() self._test_recurring_invoice(si4, False) # yearly si5 = frappe.copy_doc(base_si) si5.update({ "recurring_type": "Yearly", "invoice_period_from_date": get_first_day(today), "invoice_period_to_date": get_last_day(add_to_date(today, years=1)) }) si5.insert() si5.submit() self._test_recurring_invoice(si5, True) # yearly without a first and last day period si6 = frappe.copy_doc(base_si) si6.update({ "recurring_type": "Yearly", "invoice_period_from_date": today, "invoice_period_to_date": add_to_date(today, years=1) }) si6.insert() si6.submit() self._test_recurring_invoice(si6, False) # change posting date but keep recuring day to be today si7 = frappe.copy_doc(base_si) si7.update({"posting_date": add_to_date(today, days=-1)}) si7.insert() si7.submit() # setting so that _test function works si7.posting_date = today self._test_recurring_invoice(si7, True)
def setUp(self): create_loan_accounts() create_loan_type( "Personal Loan", 500000, 8.4, is_term_loan=1, mode_of_payment="Cash", disbursement_account="Disbursement Account - _TC", payment_account="Payment Account - _TC", loan_account="Loan Account - _TC", interest_income_account="Interest Income Account - _TC", penalty_income_account="Penalty Income Account - _TC", ) create_loan_type( "Stock Loan", 2000000, 13.5, 25, 1, 5, "Cash", "Disbursement Account - _TC", "Payment Account - _TC", "Loan Account - _TC", "Interest Income Account - _TC", "Penalty Income Account - _TC", ) create_loan_type( "Demand Loan", 2000000, 13.5, 25, 0, 5, "Cash", "Disbursement Account - _TC", "Payment Account - _TC", "Loan Account - _TC", "Interest Income Account - _TC", "Penalty Income Account - _TC", ) create_loan_security_type() create_loan_security() create_loan_security_price( "Test Security 1", 500, "Nos", get_datetime(), get_datetime(add_to_date(nowdate(), hours=24))) create_loan_security_price( "Test Security 2", 250, "Nos", get_datetime(), get_datetime(add_to_date(nowdate(), hours=24))) self.applicant1 = make_employee("*****@*****.**") make_salary_structure( "Test Salary Structure Loan", "Monthly", employee=self.applicant1, currency="INR", company="_Test Company", ) if not frappe.db.exists("Customer", "_Test Loan Customer"): frappe.get_doc(get_customer_dict("_Test Loan Customer")).insert( ignore_permissions=True) if not frappe.db.exists("Customer", "_Test Loan Customer 1"): frappe.get_doc(get_customer_dict("_Test Loan Customer 1")).insert( ignore_permissions=True) self.applicant2 = frappe.db.get_value("Customer", {"name": "_Test Loan Customer"}, "name") self.applicant3 = frappe.db.get_value( "Customer", {"name": "_Test Loan Customer 1"}, "name") create_loan(self.applicant1, "Personal Loan", 280000, "Repay Over Number of Periods", 20)
def test_recurring_document(obj, test_records): from frappe.utils import get_first_day, get_last_day, add_to_date, nowdate, getdate, add_days from erpnext.accounts.utils import get_fiscal_year frappe.db.set_value("Print Settings", "Print Settings", "send_print_as_pdf", 1) today = nowdate() base_doc = frappe.copy_doc(test_records[0]) base_doc.update({ "is_recurring": 1, "recurring_type": "Monthly", "notification_email_address": "[email protected], [email protected], [email protected]", "repeat_on_day_of_month": getdate(today).day, "due_date": None, "fiscal_year": get_fiscal_year(today)[0], "from_date": get_first_day(today), "to_date": get_last_day(today) }) if base_doc.doctype == "Sales Order": base_doc.update({ "transaction_date": today, "delivery_date": add_days(today, 15) }) elif base_doc.doctype == "Sales Invoice": base_doc.update({ "posting_date": today }) if base_doc.doctype == "Sales Order": date_field = "transaction_date" elif base_doc.doctype == "Sales Invoice": date_field = "posting_date" # monthly doc1 = frappe.copy_doc(base_doc) doc1.insert() doc1.submit() _test_recurring_document(obj, doc1, date_field, True) # monthly without a first and last day period doc2 = frappe.copy_doc(base_doc) doc2.update({ "from_date": today, "to_date": add_to_date(today, days=30) }) doc2.insert() doc2.submit() _test_recurring_document(obj, doc2, date_field, False) # quarterly doc3 = frappe.copy_doc(base_doc) doc3.update({ "recurring_type": "Quarterly", "from_date": get_first_day(today), "to_date": get_last_day(add_to_date(today, months=3)) }) doc3.insert() doc3.submit() _test_recurring_document(obj, doc3, date_field, True) # quarterly without a first and last day period doc4 = frappe.copy_doc(base_doc) doc4.update({ "recurring_type": "Quarterly", "from_date": today, "to_date": add_to_date(today, months=3) }) doc4.insert() doc4.submit() _test_recurring_document(obj, doc4, date_field, False) # yearly doc5 = frappe.copy_doc(base_doc) doc5.update({ "recurring_type": "Yearly", "from_date": get_first_day(today), "to_date": get_last_day(add_to_date(today, years=1)) }) doc5.insert() doc5.submit() _test_recurring_document(obj, doc5, date_field, True) # yearly without a first and last day period doc6 = frappe.copy_doc(base_doc) doc6.update({ "recurring_type": "Yearly", "from_date": today, "to_date": add_to_date(today, years=1) }) doc6.insert() doc6.submit() _test_recurring_document(obj, doc6, date_field, False) # change date field but keep recurring day to be today doc7 = frappe.copy_doc(base_doc) doc7.update({ date_field: today, }) doc7.insert() doc7.submit() # setting so that _test function works # doc7.set(date_field, today) _test_recurring_document(obj, doc7, date_field, True)
def daily(date, idx): return add_to_date(date, days=cint(idx))
def get_expected_time_for(parameter, service_level, start_date_time): current_date_time = start_date_time expected_time = current_date_time start_time = None end_time = None if parameter == "response": allotted_seconds = service_level.get("response_time") elif parameter == "resolution": allotted_seconds = service_level.get("resolution_time") else: frappe.throw(_("{0} parameter is invalid").format(parameter)) expected_time_is_set = 0 support_days = {} for service in service_level.get("support_and_resolution"): support_days[service.workday] = frappe._dict( { "start_time": service.start_time, "end_time": service.end_time, } ) holidays = get_holidays(service_level.get("holiday_list")) weekdays = get_weekdays() while not expected_time_is_set: current_weekday = weekdays[current_date_time.weekday()] if not is_holiday(current_date_time, holidays) and current_weekday in support_days: start_time = ( current_date_time - datetime(current_date_time.year, current_date_time.month, current_date_time.day) if getdate(current_date_time) == getdate(start_date_time) and get_time_in_timedelta(current_date_time.time()) > support_days[current_weekday].start_time else support_days[current_weekday].start_time ) end_time = support_days[current_weekday].end_time time_left_today = time_diff_in_seconds(end_time, start_time) # no time left for support today if time_left_today <= 0: pass elif allotted_seconds: if time_left_today >= allotted_seconds: expected_time = datetime.combine(getdate(current_date_time), get_time(start_time)) expected_time = add_to_date(expected_time, seconds=allotted_seconds) expected_time_is_set = 1 else: allotted_seconds = allotted_seconds - time_left_today if not expected_time_is_set: current_date_time = add_to_date(current_date_time, days=1) if end_time and allotted_seconds >= 86400: current_date_time = datetime.combine(getdate(current_date_time), get_time(end_time)) else: current_date_time = expected_time return current_date_time
def test_recurring_document(obj, test_records): frappe.db.set_value("Print Settings", "Print Settings", "send_print_as_pdf", 1) today = nowdate() base_doc = frappe.copy_doc(test_records[0]) base_doc.update({ "is_recurring": 1, "submit_on_create": 1, "recurring_type": "Monthly", "notification_email_address": "[email protected], [email protected], [email protected]", "repeat_on_day_of_month": getdate(today).day, "due_date": None, "fiscal_year": get_fiscal_year(today)[0], "from_date": get_first_day(today), "to_date": get_last_day(today) }) date_field = date_field_map[base_doc.doctype] base_doc.set(date_field, today) if base_doc.doctype == "Sales Order": base_doc.set("delivery_date", add_days(today, 15)) # monthly doc1 = frappe.copy_doc(base_doc) doc1.insert() doc1.submit() _test_recurring_document(obj, doc1, date_field, True) # monthly without a first and last day period if getdate(today).day != 1: doc2 = frappe.copy_doc(base_doc) doc2.update({ "from_date": today, "to_date": add_to_date(today, days=30) }) doc2.insert() doc2.submit() _test_recurring_document(obj, doc2, date_field, False) # quarterly doc3 = frappe.copy_doc(base_doc) doc3.update({ "recurring_type": "Quarterly", "from_date": get_first_day(today), "to_date": get_last_day(add_to_date(today, months=3)) }) doc3.insert() doc3.submit() _test_recurring_document(obj, doc3, date_field, True) # quarterly without a first and last day period doc4 = frappe.copy_doc(base_doc) doc4.update({ "recurring_type": "Quarterly", "from_date": today, "to_date": add_to_date(today, months=3) }) doc4.insert() doc4.submit() _test_recurring_document(obj, doc4, date_field, False) # yearly doc5 = frappe.copy_doc(base_doc) doc5.update({ "recurring_type": "Yearly", "from_date": get_first_day(today), "to_date": get_last_day(add_to_date(today, years=1)) }) doc5.insert() doc5.submit() _test_recurring_document(obj, doc5, date_field, True) # yearly without a first and last day period doc6 = frappe.copy_doc(base_doc) doc6.update({ "recurring_type": "Yearly", "from_date": today, "to_date": add_to_date(today, years=1) }) doc6.insert() doc6.submit() _test_recurring_document(obj, doc6, date_field, False) # change date field but keep recurring day to be today doc7 = frappe.copy_doc(base_doc) doc7.update({ date_field: today, }) doc7.insert() doc7.submit() # setting so that _test function works # doc7.set(date_field, today) _test_recurring_document(obj, doc7, date_field, True)
def handle_hold_time(self, status): if self.service_level_agreement: # set response and resolution variance as None as the issue is on Hold pause_sla_on = frappe.db.get_all( "Pause SLA On Status", fields=["status"], filters={"parent": self.service_level_agreement}) hold_statuses = [entry.status for entry in pause_sla_on] update_values = {} if hold_statuses: if self.status in hold_statuses and status not in hold_statuses: update_values[ 'on_hold_since'] = frappe.flags.current_time or now_datetime( ) if not self.first_responded_on: update_values['response_by'] = None update_values['response_by_variance'] = 0 update_values['resolution_by'] = None update_values['resolution_by_variance'] = 0 # calculate hold time when status is changed from any hold status to any non-hold status if self.status not in hold_statuses and status in hold_statuses: hold_time = self.total_hold_time if self.total_hold_time else 0 now_time = frappe.flags.current_time or now_datetime() last_hold_time = 0 if self.on_hold_since: # last_hold_time will be added to the sla variables last_hold_time = time_diff_in_seconds( now_time, self.on_hold_since) update_values[ 'total_hold_time'] = hold_time + last_hold_time # re-calculate SLA variables after issue changes from any hold status to any non-hold status # add hold time to SLA variables start_date_time = get_datetime( self.service_level_agreement_creation) priority = get_priority(self) now_time = frappe.flags.current_time or now_datetime() if not self.first_responded_on: response_by = get_expected_time_for( parameter="response", service_level=priority, start_date_time=start_date_time) response_by = add_to_date( response_by, seconds=round(last_hold_time)) response_by_variance = round( time_diff_in_seconds(response_by, now_time)) update_values['response_by'] = response_by update_values[ 'response_by_variance'] = response_by_variance + last_hold_time resolution_by = get_expected_time_for( parameter="resolution", service_level=priority, start_date_time=start_date_time) resolution_by = add_to_date(resolution_by, seconds=round(last_hold_time)) resolution_by_variance = round( time_diff_in_seconds(resolution_by, now_time)) update_values['resolution_by'] = resolution_by update_values[ 'resolution_by_variance'] = resolution_by_variance + last_hold_time update_values['on_hold_since'] = None self.db_set(update_values)
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")): if get_datetime(row.planned_end_time) < get_datetime( row.planned_start_time): row.planned_end_time = add_to_date(row.planned_start_time, minutes=row.time_in_mins) row.remaining_time_in_mins = 0.0 else: 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 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 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 half_yearly(date, idx): return add_to_date(date, months=cint(idx) * 6)
def add_months(date, months): return add_to_date(date, months=months, as_datetime=True)
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 in ('in', 'not in'): values = f.value 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=='Between' and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = "'%s' AND '%s'" % ( get_datetime(f.value[0]).strftime("%Y-%m-%d %H:%M:%S.%f"), add_to_date(get_datetime(f.value[1]),days=1).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": 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 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 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=='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 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 test_landed_cost_voucher(self): frappe.db.set_value("Buying Settings", None, "allow_multiple_items", 1) pr = make_purchase_receipt( company="_Test Company with perpetual inventory", warehouse="Stores - TCP1", supplier_warehouse="Work in Progress - TCP1", get_multiple_items=True, get_taxes_and_charges=True, ) last_sle = frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": pr.doctype, "voucher_no": pr.name, "item_code": "_Test Item", "warehouse": "Stores - TCP1", "is_cancelled": 0, }, fieldname=["qty_after_transaction", "stock_value"], as_dict=1, ) create_landed_cost_voucher("Purchase Receipt", pr.name, pr.company) pr_lc_value = frappe.db.get_value("Purchase Receipt Item", {"parent": pr.name}, "landed_cost_voucher_amount") self.assertEqual(pr_lc_value, 25.0) last_sle_after_landed_cost = frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": pr.doctype, "voucher_no": pr.name, "item_code": "_Test Item", "warehouse": "Stores - TCP1", "is_cancelled": 0, }, fieldname=["qty_after_transaction", "stock_value"], as_dict=1, ) self.assertEqual(last_sle.qty_after_transaction, last_sle_after_landed_cost.qty_after_transaction) self.assertEqual( last_sle_after_landed_cost.stock_value - last_sle.stock_value, 25.0) # assert after submit self.assertPurchaseReceiptLCVGLEntries(pr) # Mess up cancelled SLE modified timestamp to check # if they aren't effective in any business logic. frappe.db.set_value( "Stock Ledger Entry", { "is_cancelled": 1, "voucher_type": pr.doctype, "voucher_no": pr.name }, "is_cancelled", 1, modified=add_to_date(now(), hours=1, as_datetime=True, as_string=True), ) items, warehouses = pr.get_items_and_warehouses() update_gl_entries_after(pr.posting_date, pr.posting_time, warehouses, items, company=pr.company) # reassert after reposting self.assertPurchaseReceiptLCVGLEntries(pr)
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 ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'): values = f.value or '' # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None: ref_doctype = frappe.get_meta(f.doctype).get_field( f.fieldname).options result = [] lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ('descendants of', 'not descendants of'): result = frappe.get_all(ref_doctype, filters={ 'lft': ['>', lft], 'rgt': ['<', rgt] }, order_by='`lft` ASC') else: # Get ancestor elements of a DocType with a tree structure result = frappe.get_all(ref_doctype, filters={ 'lft': ['<', lft], 'rgt': ['>', rgt] }, order_by='`lft` DESC') fallback = "''" value = [ frappe.db.escape((v.name or '').strip(), percent=False) for v in result ] if len(value): value = "({0})".format(", ".join(value)) else: value = "('')" # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = 'not in' if f.operator.lower() in ( 'not ancestors of', 'not descendants of') else 'in' elif f.operator.lower() in ('in', 'not in'): values = f.value or '' if isinstance(values, frappe.string_types): values = values.split(",") fallback = "''" value = [ frappe.db.escape((v or '').strip(), percent=False) for v in values ] if len(value): value = "({0})".format(", ".join(value)) else: 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() in ('previous', 'next'): if f.operator.lower() == "previous": if f.value == "1 week": date_range = [ add_to_date(nowdate(), days=-7), nowdate() ] elif f.value == "1 month": date_range = [ add_to_date(nowdate(), months=-1), nowdate() ] elif f.value == "3 months": date_range = [ add_to_date(nowdate(), months=-3), nowdate() ] elif f.value == "6 months": date_range = [ add_to_date(nowdate(), months=-6), nowdate() ] elif f.value == "1 year": date_range = [ add_to_date(nowdate(), years=-1), nowdate() ] elif f.operator.lower() == "next": if f.value == "1 week": date_range = [ nowdate(), add_to_date(nowdate(), days=7) ] elif f.value == "1 month": date_range = [ nowdate(), add_to_date(nowdate(), months=1) ] elif f.value == "3 months": date_range = [ nowdate(), add_to_date(nowdate(), months=3) ] elif f.value == "6 months": date_range = [ nowdate(), add_to_date(nowdate(), months=6) ] elif f.value == "1 year": date_range = [ nowdate(), add_to_date(nowdate(), years=1) ] f.operator = "Between" f.value = date_range fallback = "'0001-01-01 00:00:00'" if f.operator in ('>', '<') and (f.fieldname in ('creation', 'modified')): value = cstr(f.value) fallback = "NULL" elif f.operator.lower() in ('between') and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0001-01-01 00:00:00'" elif f.operator.lower() == "is": if f.value == 'set': f.operator = '!=' elif f.value == 'not set': f.operator = '=' value = "" fallback = "''" can_be_null = True if 'ifnull' not in column_name: column_name = 'ifnull({}, {})'.format( column_name, fallback) elif df and df.fieldtype == "Date": value = frappe.db.format_date(f.value) fallback = "'0001-01-01'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = frappe.db.format_datetime(f.value) fallback = "'0001-01-01 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, string_types) 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, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") elif f.operator == '=' and df and df.fieldtype in [ 'Link', 'Data' ]: # TODO: Refactor if possible value = f.value or "''" fallback = "''" elif f.fieldname == 'name': value = f.value or "''" fallback = "''" else: value = flt(f.value) fallback = 0 # escape value if isinstance( value, string_types) 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()): if f.operator.lower() == 'like' and frappe.conf.get( 'db_type') == 'postgres': f.operator = 'ilike' 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 google_calendar_to_repeat_on(start, end, recurrence=None): """ recurrence is in the form ['RRULE:FREQ=WEEKLY;BYDAY=MO,TU,TH'] has the frequency and then the days on which the event recurs Both have been mapped in a dict for easier mapping. """ repeat_on = { "starts_on": get_datetime(start.get("date")) if start.get("date") else parser.parse( start.get("dateTime")).utcnow(), "ends_on": get_datetime(end.get("date")) if end.get("date") else parser.parse( end.get("dateTime")).utcnow(), "all_day": 1 if start.get("date") else 0, "repeat_this_event": 1 if recurrence else 0, "repeat_on": None, "repeat_till": None, "sunday": 0, "monday": 0, "tuesday": 0, "wednesday": 0, "thursday": 0, "friday": 0, "saturday": 0, } # recurrence rule "RRULE:FREQ=WEEKLY;BYDAY=MO,TU,TH" if recurrence: # google_calendar_frequency = RRULE:FREQ=WEEKLY, byday = BYDAY=MO,TU,TH, until = 20191028 google_calendar_frequency, until, byday = get_recurrence_parameters( recurrence) repeat_on["repeat_on"] = google_calendar_frequencies.get( google_calendar_frequency) if repeat_on["repeat_on"] == "Daily": repeat_on["ends_on"] = None repeat_on["repeat_till"] = datetime.strptime( until, "%Y%m%d") if until else None if byday and repeat_on["repeat_on"] == "Weekly": repeat_on["repeat_till"] = datetime.strptime( until, "%Y%m%d") if until else None byday = byday.split("=")[1].split(",") for repeat_day in byday: repeat_on[google_calendar_days[repeat_day]] = 1 if byday and repeat_on["repeat_on"] == "Monthly": byday = byday.split("=")[1] repeat_day_week_number, repeat_day_name = None, None for num in ["-2", "-1", "1", "2", "3", "4", "5"]: if num in byday: repeat_day_week_number = num break for day in ["MO", "TU", "WE", "TH", "FR", "SA", "SU"]: if day in byday: repeat_day_name = google_calendar_days.get(day) break # Only Set starts_on for the event to repeat monthly start_date = parse_google_calendar_recurrence_rule( int(repeat_day_week_number), repeat_day_name) repeat_on["starts_on"] = start_date repeat_on["ends_on"] = add_to_date(start_date, minutes=5) repeat_on["repeat_till"] = datetime.strptime( until, "%Y%m%d") if until else None if repeat_on["repeat_till"] == "Yearly": repeat_on["ends_on"] = None repeat_on["repeat_till"] = datetime.strptime( until, "%Y%m%d") if until else None return repeat_on
def monthly(date, idx): return add_to_date(date, months=cint(idx))
def get_checkout_data(conditions, filters, chift_type_details): checkout_data = [] checkout_details = get_checkout_details(conditions, filters) for checkout_d in checkout_details: if checkout_d.default_shift and checkout_d.shift_type: checkout_d["shift"] = checkout_d.shift_type if checkout_d.default_shift or checkout_d.shift_type: checkout_d["shift"] = checkout_d.shift_type or checkout_d.default_shift if checkout_d.shift: for shift_type in chift_type_details: if checkout_d.shift == shift_type.name: checkout_time_diff = get_time(checkout_d.checkout_time) early_exit_grace_time = str("00:" + cstr(shift_type.early_exit_grace_period) + ":00") end_time = get_time(str(shift_type.end_time)) end_time_diff = get_time(add_to_date(str(shift_type.end_time), minutes=(- + (shift_type.early_exit_grace_period)), as_string=False, as_datetime=True)) if checkout_time_diff <= end_time_diff: checkout_status = "Early Checkout" else: if checkout_time_diff <= end_time: checkout_status = "On Time" else: checkout_status = "Late Checkout" complete_row = { "employee": checkout_d.employee, "employee_name": checkout_d.employee_name, "department": checkout_d.department, "shift": checkout_d.shift, "date": checkout_d.date, "actual_checkout_time": str(shift_type.end_time), "checkout_time": checkout_d.checkout_time, "early_exit_grace_time": early_exit_grace_time, "checkout_status": checkout_status } checkout_data.append(complete_row) else: continue else: half_row = { "employee": checkout_d.employee, "employee_name": checkout_d.employee_name, "department": checkout_d.department, "shift": '', "date": checkout_d.date, "actual_checkout_time": '', "checkout_time": checkout_d.checkout_time, "early_exit_grace_time": '', "checkout_status": '' } checkout_data.append(half_row) return checkout_data
def get_sick_leave_allocation_records(vacation_dates, employee=None,leave_type=None): allocated_leaves = frappe._dict() first_sick_vacation_date = frappe.db.get_value('Leave Application', {"leave_type": ["=", leave_type]}, 'from_date',order_by='from_date', cache=False) or getdate(vacation_dates["from_date"]) end_sick_vacation_date = add_to_date(getdate(first_sick_vacation_date),months=12,days=-1) try: total_leaves_allocated = get_leave_allocation_records(first_sick_vacation_date,employee=employee,leave_type=leave_type)[employee][leave_type]["total_leaves_allocated"] except : frappe.throw(_("please....allocate this vacation ({0}) for employee ({1}) .".format(leave_type,employee))) allocated_leaves.setdefault(employee, frappe._dict()).setdefault(leave_type, frappe._dict({ "from_date": first_sick_vacation_date, "to_date": end_sick_vacation_date, "total_leaves_allocated": total_leaves_allocated })) conditions = ((" and employee='%s'" % employee) if employee else "") + ((" and leave_type ='%s' " % leave_type) if leave_type!=None else "") vacation_dict = frappe.db.sql( """ select employee ,leave_type, from_date, to_date, leave_balance, total_leave_days,(leave_balance - total_leave_days) total_leaves_allocated from `tabLeave Application` where employee=%(employee)s and leave_type=%(leave_type)s and status="Approved" and docstatus=1 order by from_date """ ,{ "employee": employee, "leave_type": leave_type } , as_dict=1) for item in vacation_dict: if getdate(vacation_dates["from_date"]) < getdate(first_sick_vacation_date): first_sick_vacation_date = getdate(vacation_dates["from_date"]) end_sick_vacation_date = add_to_date(getdate(first_sick_vacation_date),months=12,days=-1) break elif getdate(vacation_dates["from_date"]) > getdate(first_sick_vacation_date) and getdate(vacation_dates["to_date"]) > getdate(end_sick_vacation_date): daydiff = (date_diff(cstr(vacation_dates["to_date"]), cstr(end_sick_vacation_date))) - 1 first_sick_vacation_date = add_to_date(vacation_dates["to_date"],days=int(daydiff) * -1) end_sick_vacation_date = add_to_date(getdate(first_sick_vacation_date),months=12,days=-1) elif getdate(vacation_dates["from_date"]) > getdate(end_sick_vacation_date): if getdate(vacation_dates["from_date"]) > getdate(item["from_date"]) and getdate(vacation_dates["to_date"]) > getdate(item["to_date"]): daydiff = (date_diff(cstr(vacation_dates["to_date"]), cstr(getdate(item["to_date"])))) - 1 first_sick_vacation_date = add_to_date(vacation_dates["to_date"], days=int(daydiff) * -1) end_sick_vacation_date = add_to_date(getdate(first_sick_vacation_date), months=12, days=-1) elif getdate(vacation_dates["from_date"]) > getdate(item["to_date"]): first_sick_vacation_date = getdate(vacation_dates["from_date"]) end_sick_vacation_date = add_to_date(getdate(first_sick_vacation_date), months=12, days=-1) allocated_leaves.setdefault(employee, frappe._dict()).update( frappe._dict( { leave_type: frappe._dict( { "from_date": first_sick_vacation_date, "to_date": end_sick_vacation_date, "total_leaves_allocated": total_leaves_allocated } ) } )) # frappe.throw("===========>"+str(allocated_leaves)) return allocated_leaves