def get_data(filters, mode_of_payments):
data = []
conditions = get_conditions(filters)
entry = frappe.db.sql("""
select branch, mode_of_payment, sum(net_pay) as net_pay, sum(gross_pay) as gross_pay
from `tabSalary Slip` sal
where docstatus = 1 %s
group by branch, mode_of_payment
""" % (conditions),
as_dict=1)
branch_wise_entries, gross_pay = prepare_data(entry)
branches = frappe.db.sql_list("""
select distinct branch from `tabSalary Slip` sal
where docstatus = 1 %s
""" % (conditions))
total_row = {"total": 0, "branch": "Total"}
for branch in branches:
total = 0
row = {"branch": branch}
for mode in mode_of_payments:
if branch_wise_entries.get(branch).get(mode):
row[mode] = branch_wise_entries.get(branch).get(mode)
total += branch_wise_entries.get(branch).get(mode)
row["total"] = total
data.append(row)
total_row = get_total_based_on_mode_of_payment(data, mode_of_payments)
total_deductions = gross_pay - total_row.get("total")
report_summary = []
if data:
data.append(total_row)
data.append({})
data.append({
"branch": "<b>Total Gross Pay</b>",
mode_of_payments[0]: gross_pay
})
data.append({
"branch": "<b>Total Deductions</b>",
mode_of_payments[0]: total_deductions
})
data.append({
"branch": "<b>Total Net Pay</b>",
mode_of_payments[0]: total_row.get("total")
})
currency = erpnext.get_company_currency(filters.company)
report_summary = get_report_summary(gross_pay, total_deductions,
total_row.get("total"), currency)
return data, total_row, report_summary
def get_data(filters):
data = []
component_type_dict = frappe._dict(
frappe.db.sql(
""" select name, component_type from `tabSalary Component`
where component_type = 'Professional Tax' """))
if not len(component_type_dict):
return []
conditions = get_conditions(filters)
entry = frappe.db.sql(
""" select sal.employee, sal.employee_name, ded.salary_component, ded.amount
from `tabSalary Slip` sal, `tabSalary Detail` ded
where sal.name = ded.parent
and ded.parentfield = 'deductions'
and ded.parenttype = 'Salary Slip'
and sal.docstatus = 1 %s
and ded.salary_component in (%s)
""" % (conditions, ", ".join(["%s"] * len(component_type_dict))),
tuple(component_type_dict.keys()),
as_dict=1,
)
for d in entry:
employee = {
"employee": d.employee,
"employee_name": d.employee_name,
"amount": d.amount
}
data.append(employee)
return data