refactor: DB independent quoting and truthy/falsy values (#31358)
* refactor: DB independent quoting and truthy/falsy values
* style: reformat to black spec
* fix: ifnull -> coalesce
* fix: coalesce -> Coalesce
* fix: revert pypika comparison
* refactor: convert queries to QB
* fix: incorrect value types for query
`=` query makes no sense with list of values
* fix: remove warehouse docstatus condition
* fix: keep using base rate as rate
Co-authored-by: Ankush Menat <ankush@frappe.io>
diff --git a/erpnext/hr/doctype/exit_interview/exit_interview.py b/erpnext/hr/doctype/exit_interview/exit_interview.py
index 8317310..ce4355b 100644
--- a/erpnext/hr/doctype/exit_interview/exit_interview.py
+++ b/erpnext/hr/doctype/exit_interview/exit_interview.py
@@ -88,7 +88,7 @@
reference_doctype=interview.doctype,
reference_name=interview.name,
)
- interview.db_set("questionnaire_email_sent", True)
+ interview.db_set("questionnaire_email_sent", 1)
interview.notify_update()
email_success.append(email)
else:
diff --git a/erpnext/hr/doctype/job_offer/test_job_offer.py b/erpnext/hr/doctype/job_offer/test_job_offer.py
index 7d8ef11..9c4cb36 100644
--- a/erpnext/hr/doctype/job_offer/test_job_offer.py
+++ b/erpnext/hr/doctype/job_offer/test_job_offer.py
@@ -49,7 +49,7 @@
frappe.db.set_value("HR Settings", None, "check_vacancies", 1)
def tearDown(self):
- frappe.db.sql("DELETE FROM `tabJob Offer` WHERE 1")
+ frappe.db.sql("DELETE FROM `tabJob Offer`")
def create_job_offer(**args):
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index 43c2bb3..d49d1bd 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -399,7 +399,7 @@
select
name, leave_type, posting_date, from_date, to_date, total_leave_days, half_day_date
from `tabLeave Application`
- where employee = %(employee)s and docstatus < 2 and status in ("Open", "Approved")
+ where employee = %(employee)s and docstatus < 2 and status in ('Open', 'Approved')
and to_date >= %(from_date)s and from_date <= %(to_date)s
and name != %(name)s""",
{
@@ -439,7 +439,7 @@
"""select count(name) from `tabLeave Application`
where employee = %(employee)s
and docstatus < 2
- and status in ("Open", "Approved")
+ and status in ('Open', 'Approved')
and half_day = 1
and half_day_date = %(half_day_date)s
and name != %(name)s""",
@@ -456,7 +456,7 @@
def validate_attendance(self):
attendance = frappe.db.sql(
"""select name from `tabAttendance` where employee = %s and (attendance_date between %s and %s)
- and status = "Present" and docstatus = 1""",
+ and status = 'Present' and docstatus = 1""",
(self.employee, self.from_date, self.to_date),
)
if attendance:
diff --git a/erpnext/hr/doctype/leave_application/test_leave_application.py b/erpnext/hr/doctype/leave_application/test_leave_application.py
index 27c5410..1b9505e 100644
--- a/erpnext/hr/doctype/leave_application/test_leave_application.py
+++ b/erpnext/hr/doctype/leave_application/test_leave_application.py
@@ -108,7 +108,7 @@
def _clear_roles(self):
frappe.db.sql(
"""delete from `tabHas Role` where parent in
- ("test@example.com", "test1@example.com", "test2@example.com")"""
+ ('test@example.com', 'test1@example.com', 'test2@example.com')"""
)
def _clear_applications(self):
diff --git a/erpnext/hr/report/employee_exits/employee_exits.py b/erpnext/hr/report/employee_exits/employee_exits.py
index 9cd9ff0..80b9ec1 100644
--- a/erpnext/hr/report/employee_exits/employee_exits.py
+++ b/erpnext/hr/report/employee_exits/employee_exits.py
@@ -5,6 +5,7 @@
from frappe import _
from frappe.query_builder import Order
from frappe.utils import getdate
+from pypika import functions as fn
def execute(filters=None):
@@ -110,7 +111,7 @@
)
.distinct()
.where(
- ((employee.relieving_date.isnotnull()) | (employee.relieving_date != ""))
+ (fn.Coalesce(fn.Cast(employee.relieving_date, "char"), "") != "")
& ((interview.name.isnull()) | ((interview.name.isnotnull()) & (interview.docstatus != 2)))
& ((fnf.name.isnull()) | ((fnf.name.isnotnull()) & (fnf.docstatus != 2)))
)
diff --git a/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py b/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
index da6dace..e546810 100644
--- a/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
+++ b/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
@@ -20,7 +20,7 @@
frappe.db.sql("delete from `tabVehicle Log`")
employee_id = frappe.db.sql(
- '''select name from `tabEmployee` where name="testdriver@example.com"'''
+ """select name from `tabEmployee` where name='testdriver@example.com' """
)
self.employee_id = employee_id[0][0] if employee_id else None
if not self.employee_id:
diff --git a/erpnext/hr/utils.py b/erpnext/hr/utils.py
index 3f4e31b..db69147 100644
--- a/erpnext/hr/utils.py
+++ b/erpnext/hr/utils.py
@@ -458,7 +458,7 @@
def get_sal_slip_total_benefit_given(employee, payroll_period, component=False):
total_given_benefit_amount = 0
query = """
- select sum(sd.amount) as 'total_amount'
+ select sum(sd.amount) as total_amount
from `tabSalary Slip` ss, `tabSalary Detail` sd
where ss.employee=%(employee)s
and ss.docstatus = 1 and ss.name = sd.parent