actually for salary variance we created the report by using the script report method and report is coming fine. Now we want to print that report by using the html here we face the challenges can anyone help me on this.
Here is my .py code
from future import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt, getdate, formatdate, cint
def execute(filters=None):
columns = get_columns()
data = get_data(filters)
html_context = {
"month": filters.get('month'),
"year": filters.get('year'),
"data": data
}
return columns, data, None, html_context # Ensure print view uses the HTML template
def get_columns():
return [
{
“label”: _(“Salary Component”),
“fieldname”: “salary_component”,
“fieldtype”: “Data”,
“width”: 200
},
{
“label”: _(“Type”),
“fieldname”: “parentfield”,
“fieldtype”: “Data”,
“width”: 120
},
{
“label”: _(“Previous Month Amount”),
“fieldname”: “previous_amount”,
“fieldtype”: “Currency”,
“width”: 150
},
{
“label”: _(“Current Month Amount”),
“fieldname”: “current_amount”,
“fieldtype”: “Currency”,
“width”: 150
},
{
“label”: _(“Variance Amount”),
“fieldname”: “difference”,
“fieldtype”: “Currency”,
“width”: 150
},
{
“label”: _(“Variance Percentage”),
“fieldname”: “percentage_change”,
“fieldtype”: “Percent”,
“width”: 150
},
{
“label”: _(“YTD Amount”),
“fieldname”: “ytd_amount”,
“fieldtype”: “Currency”,
“width”: 150
}
]
def get_data(filters):
data = get_component_data(filters)
earnings = [d for d in data if d.get('parentfield') == 'earnings']
deductions = [d for d in data if d.get('parentfield') == 'deductions']
total_earnings = {
'salary_component': 'Total Earnings',
'parentfield': 'earnings',
'previous_amount': sum(d.get('previous_amount', 0) for d in earnings),
'current_amount': sum(d.get('current_amount', 0) for d in earnings),
'difference': sum(d.get('current_amount', 0) for d in earnings) - sum(d.get('previous_amount', 0) for d in earnings),
'percentage_change': calculate_percentage_change(
sum(d.get('previous_amount', 0) for d in earnings),
sum(d.get('current_amount', 0) for d in earnings)
),
'bold': 1,
'is_total': 1
}
total_deductions = {
'salary_component': 'Total Deductions',
'parentfield': 'deductions',
'previous_amount': sum(d.get('previous_amount', 0) for d in deductions),
'current_amount': sum(d.get('current_amount', 0) for d in deductions),
'difference': sum(d.get('current_amount', 0) for d in deductions) - sum(d.get('previous_amount', 0) for d in deductions),
'percentage_change': calculate_percentage_change(
sum(d.get('previous_amount', 0) for d in deductions),
sum(d.get('current_amount', 0) for d in deductions)
),
'bold': 1,
'is_total': 1
}
net_pay = {
'salary_component': 'Net Pay',
'parentfield': '',
'previous_amount': total_earnings['previous_amount'] - total_deductions['previous_amount'],
'current_amount': total_earnings['current_amount'] - total_deductions['current_amount'],
'difference': (total_earnings['current_amount'] - total_deductions['current_amount']) -
(total_earnings['previous_amount'] - total_deductions['previous_amount']),
'percentage_change': calculate_percentage_change(
total_earnings['previous_amount'] - total_deductions['previous_amount'],
total_earnings['current_amount'] - total_deductions['current_amount']
),
'ytd_amount': '',
'bold': 1,
'is_total': 1,
'is_net_pay': 1
}
if earnings:
data.insert(len(earnings), total_earnings)
if deductions:
data.insert(len(data), total_deductions)
data.append(net_pay)
return data
def get_component_data(filters):
month = filters.get(‘month’)
year = filters.get(‘year’)
month_number = {
'January': 1, 'February': 2, 'March': 3, 'April': 4,
'May': 5, 'June': 6, 'July': 7, 'August': 8,
'September': 9, 'October': 10, 'November': 11, 'December': 12
}.get(month, 1)
query = """
SELECT
current.salary_component,
current.parentfield,
COALESCE(prev.total_amount, 0) AS previous_amount,
current.total_amount AS current_amount,
(current.total_amount - COALESCE(prev.total_amount, 0)) AS difference,
ROUND(
CASE
WHEN COALESCE(prev.total_amount, 0) = 0 THEN 100
ELSE ((current.total_amount - prev.total_amount) / prev.total_amount) * 100
END, 2
) AS percentage_change,
SUM(ytd.total_amount) AS ytd_amount
FROM
(
SELECT
b.salary_component,
b.parentfield,
SUM(b.amount) AS total_amount
FROM
`tabSalary Slip` a
JOIN `tabSalary Detail` b ON a.name = b.parent
WHERE
MONTHNAME(a.posting_date) = %(month)s
AND YEAR(a.posting_date) = %(year)s
AND a.docstatus = 1
GROUP BY
b.salary_component, b.parentfield
) current
LEFT JOIN
(
SELECT
b.salary_component,
b.parentfield,
SUM(b.amount) AS total_amount
FROM
`tabSalary Slip` a
JOIN `tabSalary Detail` b ON a.name = b.parent
WHERE
MONTHNAME(a.posting_date) =
CASE
WHEN %(month)s = 'January' THEN 'December'
WHEN %(month)s = 'February' THEN 'January'
WHEN %(month)s = 'March' THEN 'February'
WHEN %(month)s = 'April' THEN 'March'
WHEN %(month)s = 'May' THEN 'April'
WHEN %(month)s = 'June' THEN 'May'
WHEN %(month)s = 'July' THEN 'June'
WHEN %(month)s = 'August' THEN 'July'
WHEN %(month)s = 'September' THEN 'August'
WHEN %(month)s = 'October' THEN 'September'
WHEN %(month)s = 'November' THEN 'October'
WHEN %(month)s = 'December' THEN 'November'
END
AND YEAR(a.posting_date) =
CASE
WHEN %(month)s = 'January' THEN %(year)s - 1
ELSE %(year)s
END
AND a.docstatus = 1
GROUP BY
b.salary_component, b.parentfield
) prev
ON current.salary_component = prev.salary_component
AND current.parentfield = prev.parentfield
LEFT JOIN
(
SELECT
b.salary_component,
b.parentfield,
SUM(b.amount) AS total_amount
FROM
`tabSalary Slip` a
JOIN `tabSalary Detail` b ON a.name = b.parent
WHERE
YEAR(a.posting_date) = %(year)s
AND MONTH(a.posting_date) <= %(month_num)s
AND a.docstatus = 1
GROUP BY
b.salary_component, b.parentfield
) ytd
ON current.salary_component = ytd.salary_component
AND current.parentfield = ytd.parentfield
GROUP BY current.salary_component, current.parentfield
ORDER BY current.parentfield DESC, current.salary_component
"""
return frappe.db.sql(query, {
'month': month,
'year': year,
'month_num': month_number
}, as_dict=1)
return columns, data, None, html_context
def calculate_percentage_change(previous, current):
if not previous:
return 100.0 if current else 0.0
return ((current - previous) / previous) * 100
