def get_due_date(posting_date, customer):
"""
referenced from erpnext.accounts.party
"""
if not customer or not posting_date:
return
customer_group = frappe.get_value("Customer", filters={"name": customer}, fieldname="customer_group")
credit_days_based_on, credit_months, credit_days = frappe.db.get_value("Customer Group", customer_group, ["credit_days_based_on", "consoleerp_credit_months", "consoleerp_credit_days"])
if not credit_months:
credit_months = 0
if not credit_days:
credit_days = 0
due_date = None
if credit_days_based_on == "Custom Day of Custom Month":
"""
frappe.util.data.get_first_day(dt_string, d_years=0, d_months=0):
Returns the first day of the month for the date specified by date object
Also adds `d_years` and `d_months` if specified
"""
# referenced from get_due_date in party.py
due_date = (get_first_day(posting_date, 0, credit_months) +
relativedelta.relativedelta(days=credit_days - 1)).strftime("%Y-%m-%d")
return due_date
def execute(filters=None):
if not filters: filters = {}
from_date = get_first_day(filters["month"] + '-' + filters["year"])
to_date = get_last_day(filters["month"] + '-' + filters["year"])
total_days_in_month = date_diff(to_date, from_date) +1
columns = get_columns(total_days_in_month)
students = get_student_group_students(filters.get("student_group"),1)
students_list = get_students_list(students)
att_map = get_attendance_list(from_date, to_date, filters.get("student_group"), students_list)
data = []
for stud in students:
row = [stud.student, stud.student_name]
student_status = frappe.db.get_value("Student", stud.student, "enabled")
date = from_date
total_p = total_a = 0.0
for day in range(total_days_in_month):
status="None"
if att_map.get(stud.student):
status = att_map.get(stud.student).get(date, "None")
elif not student_status:
status = "Inactive"
else:
status = "None"
status_map = {"Present": "P", "Absent": "A", "None": "", "Inactive":"-"}
row.append(status_map[status])
if status == "Present":
total_p += 1
elif status == "Absent":
total_a += 1
date = add_days(date, 1)
row += [total_p, total_a]
data.append(row)
return columns, data
def make_new_invoice(ref_wrapper, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_invoice = frappe.copy_doc(ref_wrapper)
mcount = month_map[ref_wrapper.recurring_type]
invoice_period_from_date = get_next_date(ref_wrapper.invoice_period_from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.invoice_period_from_date)) == \
cstr(ref_wrapper.invoice_period_from_date)) and \
(cstr(get_last_day(ref_wrapper.invoice_period_to_date)) == \
cstr(ref_wrapper.invoice_period_to_date)):
invoice_period_to_date = get_last_day(get_next_date(ref_wrapper.invoice_period_to_date,
mcount))
else:
invoice_period_to_date = get_next_date(ref_wrapper.invoice_period_to_date, mcount)
new_invoice.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.due_date,
ref_wrapper.posting_date))),
"invoice_period_from_date": invoice_period_from_date,
"invoice_period_to_date": invoice_period_to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.owner,
})
new_invoice.submit()
return new_invoice
def make_new_document(ref_wrapper, date_field, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_document = frappe.copy_doc(ref_wrapper)
mcount = month_map[ref_wrapper.recurring_type]
from_date = get_next_date(ref_wrapper.from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.from_date)) == cstr(ref_wrapper.from_date)) and \
(cstr(get_last_day(ref_wrapper.to_date)) == cstr(ref_wrapper.to_date)):
to_date = get_last_day(get_next_date(ref_wrapper.to_date, mcount))
else:
to_date = get_next_date(ref_wrapper.to_date, mcount)
new_document.update({
date_field: posting_date,
"from_date": from_date,
"to_date": to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.owner,
})
if ref_wrapper.doctype == "Sales Order":
new_document.update({
"delivery_date": get_next_date(ref_wrapper.delivery_date, mcount,
cint(ref_wrapper.repeat_on_day_of_month))
})
new_document.submit()
return new_document
def get_employee_holidays(self):
first_day = get_first_day(self.att_date)
last_day = get_last_day(self.att_date)
holidays = frappe.db.sql("""select t1.holiday_date from `tabHoliday` t1, tabEmployee t2 where
t1.parent = t2.holiday_list and t2.name = %s and t1.holiday_date between %s and %s""",(self.employee, first_day, last_day))
holidays = [cstr(i[0]) for i in holidays]
if self.status in ('Weekly Off', 'Public Holiday') and self.att_date not in holidays:
frappe.throw(_("This date not present in Holiday list.Please select correct date.."))
def get_due_date(posting_date, party_type, party, company):
"""Set Due Date = Posting Date + Credit Days"""
due_date = None
if posting_date and party:
due_date = posting_date
credit_days_based_on, credit_days = get_credit_days(party_type, party, company)
if credit_days_based_on == "Fixed Days" and credit_days:
due_date = add_days(posting_date, credit_days)
elif credit_days_based_on == "Last Day of the Next Month":
due_date = (get_first_day(posting_date, 0, 2) + datetime.timedelta(-1)).strftime("%Y-%m-%d")
return due_date
def make_new_document(ref_wrapper, date_field, posting_date): from erpnext.accounts.utils import get_fiscal_year new_document = frappe.copy_doc(ref_wrapper) mcount = month_map[ref_wrapper.recurring_type] from_date = get_next_date(ref_wrapper.from_date, mcount) # get last day of the month to maintain period if the from date is first day of its own month # and to date is the last day of its own month if (cstr(get_first_day(ref_wrapper.from_date)) == cstr(ref_wrapper.from_date)) and \ (cstr(get_last_day(ref_wrapper.to_date)) == cstr(ref_wrapper.to_date)): to_date = get_last_day(get_next_date(ref_wrapper.to_date, mcount)) else: to_date = get_next_date(ref_wrapper.to_date, mcount)
def make_new_document(reference_doc, date_field, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_document = frappe.copy_doc(reference_doc, ignore_no_copy=True)
mcount = month_map[reference_doc.recurring_type]
from_date = get_next_date(reference_doc.from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(reference_doc.from_date)) == cstr(reference_doc.from_date)) and \
(cstr(get_last_day(reference_doc.to_date)) == cstr(reference_doc.to_date)):
to_date = get_last_day(get_next_date(reference_doc.to_date, mcount))
else:
to_date = get_next_date(reference_doc.to_date, mcount)
new_document.update({
date_field: posting_date,
"from_date": from_date,
"to_date": to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"next_date": get_next_date(from_date, mcount,cint(reference_doc.repeat_on_day_of_month))
})
# copy document fields
for fieldname in ("owner", "recurring_type", "repeat_on_day_of_month",
"recurring_id", "notification_email_address", "is_recurring", "end_date",
"title", "naming_series", "select_print_heading", "ignore_pricing_rule",
"posting_time", "remarks", 'submit_on_creation'):
if new_document.meta.get_field(fieldname):
new_document.set(fieldname, reference_doc.get(fieldname))
# copy item fields
for i, item in enumerate(new_document.items):
for fieldname in ("page_break",):
item.set(fieldname, reference_doc.items[i].get(fieldname))
new_document.run_method("on_recurring", reference_doc=reference_doc)
if reference_doc.submit_on_creation:
new_document.submit()
else:
new_document.docstatus=0
new_document.insert()
return new_document
def create_salary_slips_for_payroll_period(employee, salary_structure, payroll_period, deduct_random=True):
deducted_dates = []
i = 0
while i < 12:
slip = frappe.get_doc({"doctype": "Salary Slip", "employee": employee,
"salary_structure": salary_structure, "frequency": "Monthly"})
if i == 0:
posting_date = add_days(payroll_period.start_date, 25)
else:
posting_date = add_months(posting_date, 1)
if i == 11:
slip.deduct_tax_for_unsubmitted_tax_exemption_proof = 1
slip.deduct_tax_for_unclaimed_employee_benefits = 1
if deduct_random and not random.randint(0, 2):
slip.deduct_tax_for_unsubmitted_tax_exemption_proof = 1
deducted_dates.append(posting_date)
slip.posting_date = posting_date
slip.start_date = get_first_day(posting_date)
slip.end_date = get_last_day(posting_date)
doc = make_salary_slip(salary_structure, slip, employee)
doc.submit()
i += 1
return deducted_dates
def set_subscription_period(args, mcount, new_document):
if mcount and new_document.meta.get_field('from_date') and new_document.meta.get_field('to_date'):
last_ref_doc = frappe.db.sql("""
select name, from_date, to_date
from `tab{0}`
where subscription=%s and docstatus < 2
order by creation desc
limit 1
""".format(args.reference_doctype), args.name, as_dict=1)
if not last_ref_doc:
return
from_date = get_next_date(last_ref_doc[0].from_date, mcount)
if (cstr(get_first_day(last_ref_doc[0].from_date)) == cstr(last_ref_doc[0].from_date)) and \
(cstr(get_last_day(last_ref_doc[0].to_date)) == cstr(last_ref_doc[0].to_date)):
to_date = get_last_day(get_next_date(last_ref_doc[0].to_date, mcount))
else:
to_date = get_next_date(last_ref_doc[0].to_date, mcount)
new_document.set('from_date', from_date)
new_document.set('to_date', to_date)
def get_total_pi(filters):
filters = json.loads(filters)
if(filters["period_type"]=="Month"):
#convert from_date
from_date = get_first_day(filters["period_num"] + '-' + filters["year"])
filters["to_date"] = get_last_day(filters["period_num"] + '-' + filters["year"])
#frappe.msgprint(filters["from_date)
if(filters["period_type"]=="Quarter"):
#convert from_date
period_num = "1"
if(filters["period_num"]=="2"):
period_num = "4"
if(filters["period_num"]=="3"):
period_num = "7"
if(filters["period_num"]=="4"):
period_num = "10"
filters["from_date"] = get_first_day(period_num + '-' + filters["year"])
filters["to_date"] = add_months(filters["from_date"], 3)
filters["to_date"] = add_days(filters["to_date"], -1)
#Purchase Invoice
query = """ SELECT name, tax_rate FROM `tabAccount`
WHERE `account_type`='Tax'
AND `freeze_account`='No'
AND `is_group` = 0
AND `name` LIKE '133%'
ORDER BY name """
listAcount = frappe.db.sql(query, as_list=1)
arrAcount = []
arrTaxRate = []
for i in range(0, len(listAcount)):
arrAcount.append(listAcount[i][0])
arrTaxRate.append(listAcount[i][1])
conditions = get_conditions(filters)
data = []
total_pi = 0
for i in range(0, len(arrAcount)):
rate_name = arrAcount[i]
tax_rate = arrTaxRate[i]
query = """SELECT si_tax.account_head,
sum(si.base_net_total), %d, sum(si_tax.base_tax_amount)
FROM `tabPurchase Invoice` si, `tabPurchase Taxes and Charges` si_tax
WHERE si.docstatus = 1 AND si_tax.parent = si.name AND si_tax.account_head = '%s'
%s
GROUP BY si_tax.account_head
""" %(tax_rate, rate_name, conditions)
row = frappe.db.sql(query, as_list=1)
if (row):
data.append(row[0])
total_pi = total_pi + row[0][1]
else:
data.append([rate_name, 0, tax_rate, 0])
#Sales Invoice
query = """ SELECT name, tax_rate FROM `tabAccount`
WHERE `account_type`='Tax'
AND `freeze_account`='No'
AND `is_group` = 0
AND `name` LIKE '3331%'
ORDER BY name """
listAcount = frappe.db.sql(query, as_list=1)
arrAcount = []
arrTaxRate = []
for i in range(0, len(listAcount)):
arrAcount.append(listAcount[i][0])
arrTaxRate.append(listAcount[i][1])
for i in range(0, len(arrAcount)):
rate_name = arrAcount[i]
tax_rate = arrTaxRate[i]
query = """SELECT si_tax.account_head,
sum(si.base_net_total), %d, sum(si_tax.base_tax_amount)
FROM `tabSales Invoice` si, `tabSales Taxes and Charges` si_tax
WHERE si.docstatus = 1 AND si_tax.parent = si.name AND si_tax.account_head = '%s'
%s
GROUP BY si_tax.account_head
""" %(tax_rate, rate_name, conditions)
row = frappe.db.sql(query, as_list=1)
if (row):
data.append(row[0])
else:
data.append([rate_name, 0, tax_rate, 0])
result = {
'total_pi': total_pi,
'total_tax_pi': data[2][3]+data[3][3],
'data': data
}
return result
def get_period_list(from_fiscal_year, to_fiscal_year, periodicity):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
year_start_date = getdate(fiscal_year.year_start_date)
year_end_date = getdate(fiscal_year.year_end_date)
months_to_add = {
"Yearly": 12,
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
months = get_months(year_start_date, year_end_date)
for i in xrange(months / months_to_add):
period = frappe._dict({"from_date": start_date})
to_date = add_months(start_date, months_to_add)
start_date = to_date
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
else:
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
period.to_date_fiscal_year = get_date_fiscal_year(period.to_date)
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly":
label = formatdate(opts["to_date"], "MMM YYYY")
else:
label = get_label(periodicity, opts["from_date"], opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
def get_period_list(fiscal_year, periodicity, from_beginning=False):
"""Get a list of dict {"to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fy_start_end_date = frappe.db.get_value("Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
if not fy_start_end_date:
frappe.throw(_("Fiscal Year {0} not found.").format(fiscal_year))
start_date = getdate(fy_start_end_date[0])
end_date = getdate(fy_start_end_date[1])
if periodicity == "Yearly":
period_list = [_dict({"to_date": end_date, "key": fiscal_year, "label": fiscal_year})]
else:
months_to_add = {
"Half-yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
# start with first day, so as to avoid year to_dates like 2-April if ever they occur
to_date = get_first_day(start_date)
for i in xrange(12 / months_to_add):
to_date = add_months(to_date, months_to_add)
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
else:
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
if to_date <= end_date:
# the normal case
period_list.append(_dict({ "to_date": to_date }))
# if it ends before a full year
if to_date == end_date:
break
else:
# if a fiscal year ends before a 12 month period
period_list.append(_dict({ "to_date": end_date }))
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
label = formatdate(opts["to_date"], "MMM YYYY")
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": start_date,
"year_end_date": end_date
})
if from_beginning:
# set start date as None for all fiscal periods, used in case of Balance Sheet
opts["from_date"] = None
else:
opts["from_date"] = start_date
return period_list
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_period_list(from_fiscal_year,
to_fiscal_year,
period_start_date,
period_end_date,
filter_based_on,
periodicity,
accumulated_values=False,
company=None,
reset_period_on_fy_change=True,
ignore_fiscal_year=False):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
if filter_based_on == 'Fiscal Year':
fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
year_start_date = getdate(fiscal_year.year_start_date)
year_end_date = getdate(fiscal_year.year_end_date)
else:
validate_dates(period_start_date, period_end_date)
year_start_date = getdate(period_start_date)
year_end_date = getdate(period_end_date)
months_to_add = {
"Yearly": 12,
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
months = get_months(year_start_date, year_end_date)
for i in range(cint(math.ceil(months / months_to_add))):
period = frappe._dict({"from_date": start_date})
if i == 0 and filter_based_on == 'Date Range':
to_date = add_months(get_first_day(start_date), months_to_add)
else:
to_date = add_months(start_date, months_to_add)
start_date = to_date
# Subtract one day from to_date, as it may be first day in next fiscal year or month
to_date = add_days(to_date, -1)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
if not ignore_fiscal_year:
period.to_date_fiscal_year = get_fiscal_year(period.to_date,
company=company)[0]
period.from_date_fiscal_year_start_date = get_fiscal_year(
period.from_date, company=company)[1]
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly" and not accumulated_values:
label = formatdate(opts["to_date"], "MMM YYYY")
else:
if not accumulated_values:
label = get_label(periodicity, opts["from_date"],
opts["to_date"])
else:
if reset_period_on_fy_change:
label = get_label(periodicity,
opts.from_date_fiscal_year_start_date,
opts["to_date"])
else:
label = get_label(periodicity, period_list[0].from_date,
opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
def get_period_list(fiscal_year, periodicity, from_beginning=False):
"""Get a list of dict {"to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fy_start_end_date = frappe.db.get_value(
"Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
if not fy_start_end_date:
frappe.throw(_("Fiscal Year {0} not found.").format(fiscal_year))
start_date = getdate(fy_start_end_date[0])
end_date = getdate(fy_start_end_date[1])
if periodicity == "Yearly":
period_list = [
_dict({
"to_date": end_date,
"key": fiscal_year,
"label": fiscal_year
})
]
else:
months_to_add = {
"Half-yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
# start with first day, so as to avoid year to_dates like 2-April if ever they occur
to_date = get_first_day(start_date)
for i in xrange(12 / months_to_add):
to_date = add_months(to_date, months_to_add)
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
else:
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
if to_date <= end_date:
# the normal case
period_list.append(_dict({"to_date": to_date}))
# if it ends before a full year
if to_date == end_date:
break
else:
# if a fiscal year ends before a 12 month period
period_list.append(_dict({"to_date": end_date}))
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
label = formatdate(opts["to_date"], "MMM YYYY")
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": start_date,
"year_end_date": end_date
})
if from_beginning:
# set start date as None for all fiscal periods, used in case of Balance Sheet
opts["from_date"] = None
else:
opts["from_date"] = start_date
return period_list
def test_payment_days_based_on_attendance(self):
from erpnext.hr.doctype.attendance.attendance import mark_attendance
no_of_days = self.get_no_of_days()
# Payroll based on attendance
frappe.db.set_value("Payroll Settings", None, "payroll_based_on",
"Attendance")
frappe.db.set_value("Payroll Settings", None,
"daily_wages_fraction_for_half_day", 0.75)
emp_id = make_employee(
"*****@*****.**")
frappe.db.set_value("Employee", emp_id, {
"relieving_date": None,
"status": "Active"
})
frappe.db.set_value("Leave Type", "Leave Without Pay",
"include_holiday", 0)
month_start_date = get_first_day(nowdate())
month_end_date = get_last_day(nowdate())
first_sunday = frappe.db.sql(
"""
select holiday_date from `tabHoliday`
where parent = 'Salary Slip Test Holiday List'
and holiday_date between %s and %s
order by holiday_date
""", (month_start_date, month_end_date))[0][0]
mark_attendance(emp_id, first_sunday, 'Absent',
ignore_validate=True) # invalid lwp
mark_attendance(emp_id,
add_days(first_sunday, 1),
'Absent',
ignore_validate=True) # counted as absent
mark_attendance(emp_id,
add_days(first_sunday, 2),
'Half Day',
leave_type='Leave Without Pay',
ignore_validate=True) # valid 0.75 lwp
mark_attendance(emp_id,
add_days(first_sunday, 3),
'On Leave',
leave_type='Leave Without Pay',
ignore_validate=True) # valid lwp
mark_attendance(emp_id,
add_days(first_sunday, 4),
'On Leave',
leave_type='Casual Leave',
ignore_validate=True) # invalid lwp
mark_attendance(emp_id,
add_days(first_sunday, 7),
'On Leave',
leave_type='Leave Without Pay',
ignore_validate=True) # invalid lwp
ss = make_employee_salary_slip(
"*****@*****.**", "Monthly",
"Test Payment Based On Attendence")
self.assertEqual(ss.leave_without_pay, 1.25)
self.assertEqual(ss.absent_days, 1)
days_in_month = no_of_days[0]
no_of_holidays = no_of_days[1]
self.assertEqual(ss.payment_days,
days_in_month - no_of_holidays - 2.25)
#Gross pay calculation based on attendances
gross_pay = 78000 - ((78000 / (days_in_month - no_of_holidays)) *
flt(ss.leave_without_pay + ss.absent_days))
self.assertEqual(ss.gross_pay, gross_pay)
frappe.db.set_value("Payroll Settings", None, "payroll_based_on",
"Leave")
def test_payment_days_based_on_leave_application(self):
no_of_days = self.get_no_of_days()
# Payroll based on attendance
frappe.db.set_value("Payroll Settings", None, "payroll_based_on",
"Leave")
emp_id = make_employee(
"*****@*****.**")
frappe.db.set_value("Employee", emp_id, {
"relieving_date": None,
"status": "Active"
})
frappe.db.set_value("Leave Type", "Leave Without Pay",
"include_holiday", 0)
month_start_date = get_first_day(nowdate())
month_end_date = get_last_day(nowdate())
first_sunday = frappe.db.sql(
"""
select holiday_date from `tabHoliday`
where parent = 'Salary Slip Test Holiday List'
and holiday_date between %s and %s
order by holiday_date
""", (month_start_date, month_end_date))[0][0]
make_leave_application(emp_id, first_sunday, add_days(first_sunday, 3),
"Leave Without Pay")
leave_type_ppl = create_leave_type(
leave_type_name="Test Partially Paid Leave", is_ppl=1)
leave_type_ppl.save()
alloc = create_leave_allocation(employee=emp_id,
from_date=add_days(first_sunday, 4),
to_date=add_days(first_sunday, 10),
new_leaves_allocated=3,
leave_type="Test Partially Paid Leave")
alloc.save()
alloc.submit()
#two day leave ppl with fraction_of_daily_salary_per_leave = 0.5 equivalent to single day lwp
make_leave_application(emp_id, add_days(first_sunday, 4),
add_days(first_sunday, 5),
"Test Partially Paid Leave")
ss = make_employee_salary_slip(
"*****@*****.**",
"Monthly", "Test Payment Based On Leave Application")
self.assertEqual(ss.leave_without_pay, 4)
days_in_month = no_of_days[0]
no_of_holidays = no_of_days[1]
self.assertEqual(ss.payment_days, days_in_month - no_of_holidays - 4)
frappe.db.set_value("Payroll Settings", None, "payroll_based_on",
"Leave")
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.bean(copy=test_records[0])
base_si.doc.fields.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.bean(copy=base_si.doclist)
si1.insert()
si1.submit()
self._test_recurring_invoice(si1, True)
# monthly without a first and last day period
si2 = frappe.bean(copy=base_si.doclist)
si2.doc.fields.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.bean(copy=base_si.doclist)
si3.doc.fields.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.bean(copy=base_si.doclist)
si4.doc.fields.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.bean(copy=base_si.doclist)
si5.doc.fields.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.bean(copy=base_si.doclist)
si6.doc.fields.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.bean(copy=base_si.doclist)
si7.doc.fields.update({"posting_date": add_to_date(today, days=-1)})
si7.insert()
si7.submit()
# setting so that _test function works
si7.doc.posting_date = today
self._test_recurring_invoice(si7, True)
def get_period_list(fiscal_year, periodicity):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fy_start_end_date = frappe.db.get_value("Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
if not fy_start_end_date:
frappe.throw(_("Fiscal Year {0} not found.").format(fiscal_year))
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
year_start_date = get_first_day(getdate(fy_start_end_date[0]))
year_end_date = getdate(fy_start_end_date[1])
if periodicity == "Yearly":
period_list = [frappe._dict({"from_date": year_start_date, "to_date": year_end_date,
"key": fiscal_year, "label": fiscal_year})]
else:
months_to_add = {
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
for i in xrange(12 / months_to_add):
period = frappe._dict({
"from_date": start_date
})
to_date = add_months(start_date, months_to_add)
start_date = to_date
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
else:
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly":
label = formatdate(opts["to_date"], "MMM YYYY")
else:
label = get_label(periodicity, opts["from_date"], opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
def get_period_list(fiscal_year, periodicity):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fy_start_end_date = frappe.db.get_value("Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
if not fy_start_end_date:
frappe.throw(_("Fiscal Year {0} not found.").format(fiscal_year))
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
year_start_date = get_first_day(getdate(fy_start_end_date[0]))
year_end_date = getdate(fy_start_end_date[1])
if periodicity == "Yearly":
period_list = [frappe._dict({"from_date": year_start_date, "to_date": year_end_date,
"key": fiscal_year, "label": fiscal_year})]
else:
months_to_add = {
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
for i in xrange(12 / months_to_add):
period = frappe._dict({
"from_date": start_date
})
to_date = add_months(start_date, months_to_add)
start_date = to_date
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
else:
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly":
label = formatdate(opts["to_date"], "MMM YYYY")
else:
label = get_label(periodicity, opts["from_date"], opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
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 add_late_entry_deduction(debug=False):
from hr_policies.custom_validate import preview_salary_slip_for_late_entry
end_date = add_days(today(), -1)
start_date = get_first_day(end_date)
late_entry_doc = frappe.db.sql(
"""select employee,sum(hours) as 'hours' from
`tabAttendance Extra Entry` where calculated = 0 and ignore_penalty = 0 and
YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
group by employee;""",
as_dict=1)
extra_entry = frappe.db.sql("""select name from
`tabAttendance Extra Entry` where calculated = 0 and
YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH);""",
as_dict=1)
for row in late_entry_doc:
if row.employee == emp:
frappe.errprint("Iterating")
try:
hours = frappe.db.sql(
"""select office_hours from `tabAttendance` where docstatus = 1 and
employee = %s order by creation desc limit 1;""", (row.employee))
salary_slip = preview_salary_slip_for_late_entry(row.employee)
if row.employee == emp:
frappe.errprint("Employee " + row.employee)
frappe.errprint("Salary Slip Gropay")
frappe.errprint(salary_slip.gross_pay)
day_rate = salary_slip.gross_pay / 30 #salary_slip.total_working_days
hourly_rate = 0
if row.employee == emp:
print(row.employee)
print(salary_slip.gross_pay)
print(salary_slip.total_working_days)
print(abs(hours[0][0]))
if not abs(hours[0][0]) == False and abs(hours[0][0]) > 0:
hourly_rate = flt(day_rate) / flt(
abs(hours[0][0])
) # office hours 10, hourly rate should be 57.66, so day rate 576.6
amount = hourly_rate * row.hours # 865 row hours 15
add_deduction_for_late_entry(row.employee, end_date, amount)
if row.employee == emp:
frappe.errprint("Creating Late Entry")
frappe.errprint(row.employee)
frappe.errprint(end_date)
frappe.errprint(amount)
frappe.errprint(hourly_rate)
frappe.errprint(row.hours)
else:
frappe.throw(
_("Employee {0} Shift Not Define").format(row.employee))
except Exception as e:
frappe.errprint(str(e))
frappe.log_error(frappe.get_traceback())
frappe.errprint("Iteration Complete")
frappe.db.commit()
for id in extra_entry:
if id.name:
frappe.db.set_value("Attendance Extra Entry", id.name,
"calculated", True)
frappe.db.commit()
def calcute_latein_earlyout():
cur_date = today()
pre_mnth = add_months(cur_date, -1)
first_day = get_first_day(pre_mnth)
last_day = get_last_day(pre_mnth)
employees = frappe.get_list("Employee", {'status': "Active"},
['name', 'holiday_list'])
for emp in employees:
disciplinary_measure = ""
late_in_count = 0
early_out_count = 0
monthly_occ_count = 0
late_in_count_for_3month = 0
early_out_count_for_3month = 0
occ_count_for_3month = 0
total_late_in_deduction_hours = 0
total_early_out_deduction_hours = 0.0
total_deduction_hours = 0.0
total_deduction_amount = 0
cum_late_in = "00:00:0"
cum_late_in = cum_late_in.split(":")
cum_late_in = timedelta(hours=cint(cum_late_in[0]),
minutes=cint(cum_late_in[1])).total_seconds()
cum_early_out = "00:00:0"
cum_early_out = cum_early_out.split(":")
cum_early_out = timedelta(hours=cint(cum_early_out[0]),
minutes=cint(
cum_early_out[1])).total_seconds()
late_in_list = frappe.db.sql(
"""select late_in,deduction_hours from `tabLate IN Register`
where employee = %s and is_approved != 1 and attendance_date between %s and %s """,
(emp.name, first_day, last_day),
as_dict=True)
if late_in_list:
for l in late_in_list:
late_in = l.late_in
late_in = late_in.split(":")
late_in = timedelta(hours=cint(late_in[0]),
minutes=cint(late_in[1])).total_seconds()
cum_late_in += late_in
late_in_count += 1
# print l.deduction_hours
total_late_in_deduction_hours += l.deduction_hours
early_out_list = frappe.db.sql(
"""select early_out,deduction_hours from `tabEarly OUT Register`
where employee = %s and is_approved != 1 and attendance_date between %s and %s """,
(emp.name, first_day, last_day),
as_dict=True)
if early_out_list:
for e in early_out_list:
early_out = e.early_out
early_out = early_out.split(":")
early_out = timedelta(hours=cint(early_out[0]),
minutes=cint(
early_out[1])).total_seconds()
cum_early_out += early_out
early_out_count += 1
total_early_out_deduction_hours += e.deduction_hours
monthly_occ_count = late_in_count + early_out_count
bs = frappe.get_single("Biometry Settings")
if monthly_occ_count >= int(bs.lateness_count_for_a_month):
disciplinary_measure = "Probation for 60 Days"
before_3month_first_day = add_days(first_day, -90)
late_in_list_for_3month = frappe.db.sql(
"""select late_in from `tabLate IN Register`
where employee = %s and attendance_date between %s and %s """,
(emp.name, before_3month_first_day, last_day),
as_dict=True)
if late_in_list_for_3month:
for l in late_in_list_for_3month:
late_in_count_for_3month += 1
early_out_list_for_3month = frappe.db.sql(
"""select early_out from `tabEarly OUT Register`
where employee = %s and attendance_date between %s and %s """,
(emp.name, before_3month_first_day, last_day),
as_dict=True)
if early_out_list_for_3month:
for l in early_out_list_for_3month:
early_out_count_for_3month += 1
occ_count_for_3month = late_in_count_for_3month + early_out_count_for_3month
if occ_count_for_3month >= int(bs.lateness_count_for_90_days):
disciplinary_measure = "Termination"
ssa = frappe.db.get_value("Salary Structure Assignment",
{"employee": emp.name}, "salary_structure")
if ssa:
holiday_count = 0
pay_per_hour = ""
if emp.holiday_list:
holiday_list = frappe.get_doc("Holiday List",
{"name": emp.holiday_list})
if holiday_list:
for h in holiday_list.holidays:
if h.holiday_date >= first_day and h.holiday_date <= last_day:
holiday_count += 1
actual_days_count = date_diff(last_day, first_day) + 1
pay_days = actual_days_count - holiday_count
salary_structure = frappe.get_doc("Salary Structure", ssa)
pay_per_hour = round(salary_structure.net_pay / pay_days / 9.5, 2)
if cum_late_in or cum_early_out:
total_deduction_hours = total_late_in_deduction_hours + total_early_out_deduction_hours
total_deduction_amount = total_deduction_hours * pay_per_hour
emp_details = frappe.get_doc("Employee", emp.name)
emp_details.update({
"current_month_late_in_count":
late_in_count,
"late_in_hours":
round(cum_late_in / 3600, 1),
"current_month_early_out_count":
early_out_count,
"early_out_hours":
round(cum_early_out / 3600, 1),
"disciplinary_measure":
disciplinary_measure,
"net_pay_per_hour":
pay_per_hour,
"total_deduction_amount":
total_deduction_amount,
"late_in_deduction_hours":
total_late_in_deduction_hours,
"early_out_deduction_hours":
total_early_out_deduction_hours
})
emp_details.save(ignore_permissions=True)
frappe.db.commit()
def get_period_list(from_fiscal_year,
to_fiscal_year,
periodicity,
accumulated_values=False,
company=None,
reset_period_on_fy_change=True):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
# fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
# validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
# year_start_date = from_fiscal_year.date()
fmt = "%Y-%m-%d"
year_start_date = datetime.strptime(from_fiscal_year, fmt).date()
year_end_date = datetime.strptime(to_fiscal_year, fmt).date()
# year_end_date = to_fiscal_year.date()
months_to_add = {
"Yearly": 12,
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
months = get_months(year_start_date, year_end_date)
for i in range(months // months_to_add):
period = frappe._dict({"from_date": start_date})
to_date = add_months(start_date, months_to_add)
start_date = to_date
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
period.to_date_fiscal_year = get_fiscal_year(period.to_date,
company=company)[0]
period.from_date_fiscal_year_start_date = get_fiscal_year(
period.from_date, company=company)[1]
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly" and not accumulated_values:
label = formatdate(opts["to_date"], "MMM YYYY")
else:
if not accumulated_values:
label = get_label(periodicity, opts["from_date"],
opts["to_date"])
else:
if reset_period_on_fy_change:
label = get_label(periodicity,
opts.from_date_fiscal_year_start_date,
opts["to_date"])
else:
label = get_label(periodicity, period_list[0].from_date,
opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
def get_report_content(company, customer_name, from_date=None, to_date=None):
'''Returns html for the report in PDF format'''
settings_doc = frappe.get_single('Customer Statements Sender')
if not from_date:
from_date = get_first_day(today()).strftime("%Y-%m-%d")
if not to_date:
to_date = today()
# Get General Ledger report content
report_gl = frappe.get_doc('Report', 'General Ledger')
report_gl_filters = {
'company': company,
'party_type': 'Customer',
'party': [customer_name],
'from_date': from_date,
'to_date': to_date,
'group_by': 'Group by Voucher (Consolidated)'
}
columns_gl, data_gl = report_gl.get_data(limit=500,
user="******",
filters=report_gl_filters,
as_dict=True)
# Add serial numbers
columns_gl.insert(0, frappe._dict(fieldname='idx', label='', width='30px'))
for i in range(len(data_gl)):
data_gl[i]['idx'] = i + 1
# Get ageing summary report content
data_ageing = []
labels_ageing = []
if settings_doc.no_ageing != 1:
report_ageing = frappe.get_doc('Report', 'Accounts Receivable Summary')
report_ageing_filters = {
'company': company,
'ageing_based_on': 'Posting Date',
'report_date': datetime.datetime.today(),
'range1': 30,
'range2': 60,
'range3': 90,
'range4': 120,
'customer': customer_name
}
columns_ageing, data_ageing = report_ageing.get_data(
limit=50,
user="******",
filters=report_ageing_filters,
as_dict=True)
labels_ageing = {}
for col in columns_ageing:
if 'range' in col['fieldname']:
labels_ageing[col['fieldname']] = col['label']
# Get Letter Head
no_letterhead = bool(
frappe.db.get_single_value('Customer Statements Sender',
'no_letter_head'))
letter_head = frappe._dict(
printview.get_letter_head(settings_doc, no_letterhead) or {})
if letter_head.content:
letter_head.content = frappe.utils.jinja.render_template(
letter_head.content, {"doc": settings_doc.as_dict()})
# Render Template
date_time = global_date_format(now()) + ' ' + format_time(now())
currency = frappe.db.get_value('Company', company, 'default_currency')
report_html_data = frappe.render_template(
'erpnext_customer_statements_sender/templates/report/customer_statement_jinja.html',
{
'title': 'Customer Statement for {0}'.format(customer_name),
'description': 'Customer Statement for {0}'.format(customer_name),
'date_time': date_time,
'columns': columns_gl,
'data': data_gl,
'report_name': 'Customer Statement for {0}'.format(customer_name),
'filters': report_gl_filters,
'currency': currency,
'letter_head': letter_head.content,
'billing_address': get_billing_address(customer_name),
'labels_ageing': labels_ageing,
'data_ageing': data_ageing
})
return report_html_data
def validate_cross_sale_limit(self):
indent_amount = {}
errors = []
default_aggr_dict = {'amt': 0, 'qty_in_kg': 0}
for indent_item in self.indent:
if indent_item.cross_sold:
indent_amount.setdefault(indent_item.customer, default_aggr_dict.copy())
indent_amount[indent_item.customer]['amt'] += indent_item.amount
indent_amount[indent_item.customer]['qty_in_kg'] += float(indent_item.item.replace('FC', '').replace('L', '')) * indent_item.qty
month_end = get_last_day(self.posting_date)
month_start = get_first_day(self.posting_date)
for customer in indent_amount.keys():
invoice_sum_value = frappe.db.sql("""
select ifnull(sum(inv.actual_amount), 0) + ifnull(sum(sal.grand_total_export), 0)
from `tabIndent Invoice` inv
LEFT JOIN `tabSales Invoice` sal
on inv.transportation_invoice = sal.name
where inv.customer = "{customer}"
and inv.docstatus = 1
and inv.cross_sold = 1
and inv.transaction_date between "{month_start}" and "{month_end}"
""".format(customer=customer, month_end=month_end, month_start=month_start))[0][0]
indent_sum = frappe.db.sql("""
select ifnull(sum(replace(replace(itm.item, 'FC' ,''), 'L', '')*itm.qty), 0) as qty,
ifnull(sum(itm.amount), 0) as amount
from `tabIndent Item` itm left join `tabIndent` ind
on itm.parent = ind.name
where itm.name not in (
select ifnull(indent_item, '')
from `tabIndent Invoice`
where docstatus = 1
)
and itm.parent != "{self_indent}"
and itm.docstatus != 2
and itm.customer = "{customer}"
and itm.cross_sold = 1
and ind.posting_date between "{month_start}" and "{month_end}"
""".format(customer=customer, month_end=month_end, month_start=month_start, self_indent=self.name), as_dict=True)[0]
sales_rate = frappe.db.sql(
"""
SELECT applicable_transport_rate
FROM `tabCustomer Sale`
WHERE customer="{customer}"
AND with_effect_from <= "{invoice_date}"
AND ifnull(valid_up_to, "{invoice_date}") <= "{invoice_date}"
AND docstatus = 1
ORDER BY with_effect_from DESC LIMIT 1
""".format(invoice_date=today(), customer=customer)
)
sales_rate = sales_rate[0][0] if sales_rate else 0
limit = frappe.db.get_value("Customer", {'name': customer}, 'cross_sale_limit')
limit = limit if limit else 0
available_limit = limit - invoice_sum_value - indent_sum.amount - (float(indent_sum.qty) * float(sales_rate))
cur_dict = indent_amount.get(customer, default_aggr_dict)
diff = round(available_limit - cur_dict['amt'] - cur_dict['qty_in_kg'] * sales_rate, 2)
if diff < 0:
errors.append(
"Cross sold limit exceeded for customer `{}` by {}. Get it increased or place indent for other customer"
.format(customer, abs(diff))
)
if errors:
errors.insert(0, 'Did not save')
frappe.throw('\n'.join(errors))
def get_period_list(from_fiscal_year, to_fiscal_year, periodicity, accumulated_values=False,
company=None, reset_period_on_fy_change=True):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
year_start_date = getdate(fiscal_year.year_start_date)
year_end_date = getdate(fiscal_year.year_end_date)
months_to_add = {
"Yearly": 12,
"Half-Yearly": 6,
"Quarterly": 3,
"Monthly": 1
}[periodicity]
period_list = []
start_date = year_start_date
months = get_months(year_start_date, year_end_date)
for i in xrange(months / months_to_add):
period = frappe._dict({
"from_date": start_date
})
to_date = add_months(start_date, months_to_add)
start_date = to_date
if to_date == get_first_day(to_date):
# if to_date is the first day, get the last day of previous month
to_date = add_days(to_date, -1)
if to_date <= year_end_date:
# the normal case
period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
period.to_date_fiscal_year = get_fiscal_year(period.to_date, company=company)[0]
period.from_date_fiscal_year_start_date = get_fiscal_year(period.from_date, company=company)[1]
period_list.append(period)
if period.to_date == year_end_date:
break
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
if periodicity == "Monthly" and not accumulated_values:
label = formatdate(opts["to_date"], "MMM YYYY")
else:
if not accumulated_values:
label = get_label(periodicity, opts["from_date"], opts["to_date"])
else:
if reset_period_on_fy_change:
label = get_label(periodicity, opts.from_date_fiscal_year_start_date, opts["to_date"])
else:
label = get_label(periodicity, period_list[0].from_date, opts["to_date"])
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
"year_start_date": year_start_date,
"year_end_date": year_end_date
})
return period_list
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,
"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)