How to Subtract two dates in salary Component Formula Column

Savez_Shabkhez · April 5, 2022, 2:24am

Hi,
I am trying to subtract the two dates: End Date and Start date on the salary slip to get the number of days in the month (not working days) in a salary component formula.

But I can’t seem to be able to subtract the two dates here.
The following don’t work:

end_date - start_date - Error: Expected int got str
getDate(end_date) - getDate(start_date) - Error: getDate is not defined.
frappe.datetime.get_day_diff(begin, end) - this works in other places but not on the salary component formula space. Error: frappe is not defined.

Can’t seem to be able to use generic JS or Python functions.

Any help is welcome!

Thanks!

Savez_Shabkhez · April 5, 2022, 2:30am

I managed to achieve a very situational workaround using the string function in python.
int(end_date[8:10]) - int(start_date[8:10]) + 1

But any help on the original is very welcome.

clayrisser · November 14, 2022, 10:31am

I’m trying to figure out how dates work in a salary component formula as well. I’m going to did through the code to figure out how it works. What I did find so far, is where the formula is evaluated. Maybe this will help someone in the future.

github.com

frappe/hrms/blob/e4e130406178fbe0e23b6751d0c6d28ee4bdc1e8/hrms/payroll/doctype/salary_slip/salary_slip.py#L705


      
          		# shallow copy of data to store default amounts (without payment days) for tax calculation
          		default_data = data.copy()
          
          
		for key in ("earnings", "deductions"):
          			for d in self.get(key):
          				default_data[d.abbr] = d.default_amount or 0
          				data[d.abbr] = d.amount or 0
          
          
		return data, default_data
          
          
	def eval_condition_and_formula(self, d, data):
          		try:
          			condition = d.condition.strip().replace("\n", " ") if d.condition else None
          			if condition:
          				if not frappe.safe_eval(condition, self.whitelisted_globals, data):
          					return None
          			amount = d.amount
          			if d.amount_based_on_formula:
          				formula = d.formula.strip().replace("\n", " ") if d.formula else None
          				if formula:
          					amount = flt(frappe.safe_eval(formula, self.whitelisted_globals, data), d.precision("amount"))

clayrisser · November 14, 2022, 10:36am

Here’s some more details. Looks like the safe_eval function is being used, which whitelists specific global variables. This will at least help us know what functions are available to be called.

https://github.com/frappe/frappe/blob/5e96e92a550bf23581f49a78a1ca2b2b139e4da8/frappe/init.py#L2239

clayrisser · November 14, 2022, 10:40am

I wish I knew how the date was formatted. Seems to be a string, but I’m not sure how it’s formatted. If I know, I could probably convert it to a unix timestamp. Then it would be easy to work with in this very restricted safe_eval.

I think I figured it out though. The following two whitelists are what is available in the formula.

https://github.com/frappe/frappe/blob/5e96e92a550bf23581f49a78a1ca2b2b139e4da8/frappe/init.py#L2241

github.com

frappe/hrms/blob/e4e130406178fbe0e23b6751d0c6d28ee4bdc1e8/hrms/payroll/doctype/salary_slip/salary_slip.py#L56-L63


      
          		self.whitelisted_globals = {
          			"int": int,
          			"float": float,
          			"long": int,
          			"round": round,
          			"date": datetime.date,
          			"getdate": getdate,
          		}

Notice, date and getdate are both available. This probably is flexible enough to work with the date object or convert it to a unix timestamp and do raw math.

I have confirmed that this works. If you’re trying to use dates with a salary component formula, use the getdate() function to convert the date into a python date object. Then you can go from there.

For example, if I want to get the month when an employee was hired, I would use the following.

getdate(date_of_joining).month

You can see all the available attributes on a python date object at the following docs.