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_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 _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