Salary Structure Assignment - remove to_date (#14642)
* Salary Structure Assignment - remove to_date
* fix Salary Assignment - remove to_date
* Salary Slip - fix codacy remove sting based query, add end_date to condition
diff --git a/erpnext/hr/doctype/payroll_entry/payroll_entry.py b/erpnext/hr/doctype/payroll_entry/payroll_entry.py
index 9373d1a..10cbd13 100644
--- a/erpnext/hr/doctype/payroll_entry/payroll_entry.py
+++ b/erpnext/hr/doctype/payroll_entry/payroll_entry.py
@@ -40,7 +40,7 @@
{"company": self.company, "salary_slip_based_on_timesheet":self.salary_slip_based_on_timesheet})
if sal_struct:
cond += "and t2.salary_structure IN %(sal_struct)s "
- cond += "and ((%(from_date)s between t2.from_date and ifnull(t2.to_date, '2199-12-31')) or (%(to_date)s between t2.from_date and ifnull(t2.to_date, '2199-12-31')) or (t2.from_date between %(from_date)s and %(to_date)s))"
+ cond += "and %(from_date)s >= t2.from_date"
emp_list = frappe.db.sql("""
select
t1.name as employee, t1.employee_name, t1.department, t1.designation, t2.name
@@ -49,7 +49,7 @@
where
t1.name = t2.employee
and t2.docstatus = 1
- %s """% cond, {"sal_struct": sal_struct, "from_date": self.start_date, "to_date": self.end_date}, as_dict=True)
+ %s order by t2.from_date desc limit 1"""% cond, {"sal_struct": sal_struct, "from_date": self.start_date}, as_dict=True)
return emp_list
def fill_employee_details(self):
diff --git a/erpnext/hr/doctype/salary_slip/salary_slip.py b/erpnext/hr/doctype/salary_slip/salary_slip.py
index 42b63b6..944ba7c 100644
--- a/erpnext/hr/doctype/salary_slip/salary_slip.py
+++ b/erpnext/hr/doctype/salary_slip/salary_slip.py
@@ -230,12 +230,11 @@
cond = """and payroll_frequency = '%(payroll_frequency)s'""" % {"payroll_frequency": self.payroll_frequency}
st_name = frappe.db.sql("""select salary_structure from `tabSalary Structure Assignment`
- where employee=%s and (from_date <= %s or from_date <= %s)
- and (to_date is null or to_date >= %s or to_date >= %s)
+ where employee=%s and (from_date <= %s or from_date <= %s or from_date <= %s)
and docstatus = 1
and salary_structure in (select name from `tabSalary Structure`
- where is_active = 'Yes'%s)
- """% ('%s', '%s', '%s','%s','%s', cond),(self.employee, self.start_date, joining_date, self.end_date, relieving_date))
+ where is_active = 'Yes' %s) order by from_date desc limit 1
+ """, (self.employee, self.start_date, self.end_date, joining_date, cond))
if st_name:
if len(st_name) > 1:
diff --git a/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.json b/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.json
index 221550f..95308dc 100644
--- a/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.json
+++ b/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.json
@@ -213,38 +213,6 @@
{
"allow_bulk_edit": 0,
"allow_in_quick_entry": 0,
- "allow_on_submit": 1,
- "bold": 0,
- "collapsible": 0,
- "columns": 0,
- "fieldname": "to_date",
- "fieldtype": "Date",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "ignore_xss_filter": 0,
- "in_filter": 0,
- "in_global_search": 0,
- "in_list_view": 0,
- "in_standard_filter": 0,
- "label": "To Date",
- "length": 0,
- "no_copy": 0,
- "permlevel": 0,
- "precision": "",
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "read_only": 0,
- "remember_last_selected_value": 0,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "translatable": 0,
- "unique": 0
- },
- {
- "allow_bulk_edit": 0,
- "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -444,7 +412,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2018-06-13 16:18:19.784377",
+ "modified": "2018-06-22 14:54:05.494234",
"modified_by": "Administrator",
"module": "HR",
"name": "Salary Structure Assignment",
diff --git a/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.py b/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.py
index 2168a36..210f75d 100644
--- a/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.py
+++ b/erpnext/hr/doctype/salary_structure_assignment/salary_structure_assignment.py
@@ -13,13 +13,15 @@
class SalaryStructureAssignment(Document):
def validate(self):
self.validate_dates()
- self.validate_duplicate_assignments()
def validate_dates(self):
joining_date, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
if self.from_date:
+ if frappe.db.exists("Salary Structure Assignment", {"employee": self.employee, "from_date": self.from_date, "docstatus": 1}):
+ frappe.throw("Salary Structure Assignment for Employee already exists")
+
if joining_date and getdate(self.from_date) < joining_date:
frappe.throw(_("From Date {0} cannot be before employee's joining Date {1}")
.format(self.from_date, joining_date))
@@ -29,51 +31,15 @@
frappe.throw(_("From Date {0} cannot be after employee's relieving Date {1}")
.format(self.from_date, relieving_date))
- if self.to_date:
- if self.from_date and getdate(self.from_date) > getdate(self.to_date):
- frappe.throw(_("From Date {0} cannot be after To Date {1}")
- .format(self.from_date, self.to_date))
- if relieving_date and getdate(self.to_date) > getdate(relieving_date) and not self.flags.old_employee:
- frappe.throw(_("To Date {0} cannot be after employee's relieving Date {1}")
- .format(self.to_date, relieving_date))
-
- def validate_duplicate_assignments(self):
- if not self.name:
- # hack! if name is null, it could cause problems with !=
- self.name = "New "+self.doctype
- assignment = frappe.db.sql("""
- select name from `tabSalary Structure Assignment`
- where employee=%(employee)s
- and name != %(name)s
- and docstatus != 2
- and (
- (%(from_date)s between from_date and ifnull(to_date, '2199-12-31'))
- or (%(to_date)s between from_date and ifnull(to_date, '2199-12-31'))
- or (from_date between %(from_date)s and %(to_date)s)
- )""", {
- 'employee': self.employee,
- 'from_date': self.from_date,
- 'to_date': (self.to_date or '2199-12-31'),
- 'name': self.name
- })
-
- if assignment:
- frappe.throw(_("Active Salary Structure Assignment {0} found for employee {1} for the given dates").
- format(assignment[0][0], self.employee), DuplicateAssignment)
-
def get_assigned_salary_structure(employee, on_date):
if not employee or not on_date:
return None
-
salary_structure = frappe.db.sql("""
select salary_structure from `tabSalary Structure Assignment`
where employee=%(employee)s
and docstatus = 1
- and (
- (%(on_date)s between from_date and ifnull(to_date, '2199-12-31'))
- )""", {
+ and %(on_date)s > from_date order by from_date desc limit 1""", {
'employee': employee,
'on_date': on_date,
})
-
return salary_structure[0][0] if salary_structure else None
diff --git a/erpnext/hr/utils.py b/erpnext/hr/utils.py
index 7a861ac..6c31195 100644
--- a/erpnext/hr/utils.py
+++ b/erpnext/hr/utils.py
@@ -303,9 +303,7 @@
select * from `tabSalary Structure Assignment`
where employee=%(employee)s
and docstatus = 1
- and (
- (%(on_date)s between from_date and ifnull(to_date, '2199-12-31'))
- )""", {
+ and %(on_date)s >= from_date order by from_date desc limit 1""", {
'employee': employee,
'on_date': date,
}, as_dict=1)