Hi All
I Want to get Total salary before create salary slip ,During a period
To calculate salary in a period, (leave application, from date - to date / including date of additional salary )
The reason why I need that is, I defined a leave type and a duration of 120 days, as the first thirty days are not deducted from the salary, the second thirty days are deducted from the salary by 50%, and the other sixty days are all deducted from the salary.
I take advice .
Thank you.
@kid1194
anyone ?
@Yousuf Sorry bro for the late reply…
Can you post a screenshot of the fields of all related doctypes so I can better understand the data flow?
And do you want to do that using JavaScript or Python?
Bro Ameen
Thanks for the response
I Want to get total salary depend on (salary structure , salary structure assignment to get (base amount), additional salary ( from salary component )
salary structure :
Additional salary:
ex. amount = 1500
total salary = (base + earning (housing allowance = 1500 )) - deduction (5000+1500)*0.975
total salary = (5000 +1500) -633.75
I want to call up the total salary, based on the leave application date
As you know my brother, the total salary is variable during a certain period.
I create the formula that I needed, according to the above, I only need to retrieve total salary
conclusion:
The available method is to create a salary slip to collect the total salary(net pay) from it, which is illogical
@Yousuf Ok bro, let’s plan the data flow…
Here is what I thought of…
Now let me ask you some questions…
Are the steps listed above correct?
Both the earnings and deductions have amount & default amount fields. Which one will be holding the exact amount for calculation if it’s a fixed amount?
For the Additional Salary, can you tell me how does it fit in the salary calculation? Because in your example it wasn’t mentioned at all.
Best regards
Bro @kid1194
2- If it is Recurring the period is considered to be calculated in the salary period, when creating a Salary Slip.
Thanks
@Yousuf Ok bro…
Do you want the code to be executed on a specific doctype or when a specific column is clicked or you want it to be part of a report?
For now, I have created a JavaScript code that will do the salary calculation but if you want it to be part of a report then the calculation must be done using Python…
@Yousuf I have created the Python code for you…
I hope that it works without any problem…
import datetime
import frappe
from frappe import _, _dict
from frappe.utils import cint, flt, getdate
from frappe.model.meta import get_field_precision
from pypika.terms import Criterion
def get_employee_salary(employee, company, from_date, to_date):
try:
data = frappe.get_all(
"Salary Structure Assignment",
fields=["salary_structure", "base"],
filters={
"employee": employee,
"company": company,
"from_date": ["in", [from_date, to_date]]
},
limit_page_length=1,
)
data = data.pop(0) if data and isinstance(data, list) else None
if not data:
frappe.throw(_("Unable to find the employee in Salary Structure Assignment doctype."))
details = frappe.get_all(
"Salary Detail",
fields=[
"parentfield", "salary_component", "default_amount", "abbr", "amount",
"additional_salary", "condition", "formula", "amount_based_on_formula"
],
filters={
"parent": data["salary_structure"],
"parenttype": "Salary Structure",
"parentfield": ["in", ["earnings", "deductions"]]
}
)
if not details or not isinstance(details, list):
frappe.throw(_("Unable to get the salary details of the employee's Salary Structure entry."))
additional_salary = [row["additional_salary"] for row in details if row["additional_salary"]]
additionals = get_additional_salary(additional_salary, employee, company, from_date, to_date)
whitelisted_globals = {
"int": int,
"float": float,
"long": int,
"round": round,
"date": datetime.date,
"getdate": getdate,
}
local_data = {"base": data["base"]}
earnings = 0
deductions = 0
for row in details:
total = None
if row["additional_salary"]:
if row["additional_salary"] in additionals:
additional = additionals.get(row["additional_salary"])
if row["salary_component"] == additional["salary_component"]:
if additional["amount"] and cint(additional["overwrite_salary_structure_amount"]):
total = additional["amount"]
if not total:
total = eval_condition_and_formula(_dict(row), local_data, whitelisted_globals)
if not total:
continue
total = flt(total)
if row["abbr"]:
local_data[row["abbr"]] = total
if row["parentfield"] == "earnings":
earnings += total
else:
deductions += total
return flt(earnings - deductions)
except Exception as exc:
frappe.throw(_("Unable to calculate the employee's total salary."))
def get_additional_salary(names, employee, company, from_date, to_date):
doc = frappe.qb.DocType("Additional Salary")
data = (
frappe.qb.from_(doc)
.select(
doc.name,
doc.salary_component,
doc.amount,
doc.overwrite_salary_structure_amount
)
.where(doc.name.isin(names))
.where(doc.employee == employee)
.where(doc.company == company)
.where(
Criterion.any(
Criterion.all(
doc.is_recurring == 0,
doc.payroll_date.between(from_date, to_date)
),
Criterion.all(
doc.is_recurring == 1,
doc.from_date.gte(from_date),
doc.to_date.lte(to_date)
)
)
)
).run(as_dict=True)
if not data or not isinstance(data, list):
return {}
return {row["name"]:row for row in data}
def eval_condition_and_formula(row, data, whitelisted_globals):
try:
condition = row.condition.strip().replace("\n", " ") if row.condition else None
if condition:
if not frappe.safe_eval(condition, whitelisted_globals, data):
return None
amount = row.amount
if row.amount_based_on_formula:
formula = row.formula.strip().replace("\n", " ") if row.formula else None
if formula:
amount = flt(frappe.safe_eval(formula, whitelisted_globals, data), get_precision("amount"))
return amount
except NameError as err:
frappe.throw(
_("{0} <br> This error can be due to missing or deleted field.").format(err),
title=_("Name error"),
)
except SyntaxError as err:
frappe.throw(_("Syntax error in formula or condition: {0}").format(err))
except Exception as e:
frappe.throw(_("Error in formula or condition: {0}").format(e))
raise
def get_precision(fieldname):
df = frappe.get_meta("Salary Detail").get_field(fieldname)
if df.fieldtype in ("Currency", "Float", "Percent"):
return get_field_precision(df)
return None