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/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 8f0fe51..2c16ca3 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -416,7 +416,7 @@
against_entries = frappe.db.sql(
"""select * from `tabJournal Entry Account`
where account = %s and docstatus = 1 and parent = %s
- and (reference_type is null or reference_type in ("", "Sales Order", "Purchase Order"))
+ and (reference_type is null or reference_type in ('', 'Sales Order', 'Purchase Order'))
""",
(d.account, d.reference_name),
as_dict=True,
diff --git a/erpnext/accounts/doctype/subscription/subscription.py b/erpnext/accounts/doctype/subscription/subscription.py
index 2243b19..9dab4e9 100644
--- a/erpnext/accounts/doctype/subscription/subscription.py
+++ b/erpnext/accounts/doctype/subscription/subscription.py
@@ -145,13 +145,14 @@
You shouldn't need to call this directly. Use `get_billing_cycle` instead.
"""
plan_names = [plan.plan for plan in self.plans]
- billing_info = frappe.db.sql(
- "select distinct `billing_interval`, `billing_interval_count` "
- "from `tabSubscription Plan` "
- "where name in %s",
- (plan_names,),
- as_dict=1,
- )
+
+ subscription_plan = frappe.qb.DocType("Subscription Plan")
+ billing_info = (
+ frappe.qb.from_(subscription_plan)
+ .select(subscription_plan.billing_interval, subscription_plan.billing_interval_count)
+ .distinct()
+ .where(subscription_plan.name.isin(plan_names))
+ ).run(as_dict=1)
return billing_info
diff --git a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
index 4eef307..0577214 100644
--- a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
+++ b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
@@ -179,7 +179,7 @@
def get_mode_of_payments(filters):
mode_of_payments = {}
invoice_list = get_invoices(filters)
- invoice_list_names = ",".join('"' + invoice["name"] + '"' for invoice in invoice_list)
+ invoice_list_names = ",".join("'" + invoice["name"] + "'" for invoice in invoice_list)
if invoice_list:
inv_mop = frappe.db.sql(
"""select a.owner,a.posting_date, ifnull(b.mode_of_payment, '') as mode_of_payment
@@ -200,7 +200,7 @@
from `tabJournal Entry` a, `tabJournal Entry Account` b
where a.name = b.parent
and a.docstatus = 1
- and b.reference_type = "Sales Invoice"
+ and b.reference_type = 'Sales Invoice'
and b.reference_name in ({invoice_list_names})
""".format(
invoice_list_names=invoice_list_names
@@ -228,7 +228,7 @@
def get_mode_of_payment_details(filters):
mode_of_payment_details = {}
invoice_list = get_invoices(filters)
- invoice_list_names = ",".join('"' + invoice["name"] + '"' for invoice in invoice_list)
+ invoice_list_names = ",".join("'" + invoice["name"] + "'" for invoice in invoice_list)
if invoice_list:
inv_mop_detail = frappe.db.sql(
"""
@@ -259,7 +259,7 @@
from `tabJournal Entry` a, `tabJournal Entry Account` b
where a.name = b.parent
and a.docstatus = 1
- and b.reference_type = "Sales Invoice"
+ and b.reference_type = 'Sales Invoice'
and b.reference_name in ({invoice_list_names})
group by a.owner, a.posting_date, mode_of_payment
) t
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index ccf4b40..41f3223 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -475,7 +475,7 @@
select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2
where t1.name = t2.parent and t2.account = %(account)s
and t2.party_type = %(party_type)s and t2.party = %(party)s
- and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order"))
+ and (t2.reference_type is null or t2.reference_type in ('', 'Sales Order', 'Purchase Order'))
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.docstatus=1 """.format(
dr_or_cr=args.get("dr_or_cr")
@@ -495,7 +495,7 @@
t1.name = t2.parent and t1.docstatus = 1
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s
- and t2.reference_doctype in ("", "Sales Order", "Purchase Order")
+ and t2.reference_doctype in ('', 'Sales Order', 'Purchase Order')
and t2.allocated_amount = %(unreconciled_amount)s
""".format(
party_account_field
diff --git a/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py b/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
index e603d34..0028d84 100644
--- a/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
+++ b/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
@@ -47,17 +47,19 @@
team_member = frappe.db.get_value("User", assign_to_member, "email")
args = {
"doctype": "Asset Maintenance",
- "assign_to": [team_member],
+ "assign_to": team_member,
"name": asset_maintenance_name,
"description": maintenance_task,
"date": next_due_date,
}
if not frappe.db.sql(
"""select owner from `tabToDo`
- where reference_type=%(doctype)s and reference_name=%(name)s and status="Open"
+ where reference_type=%(doctype)s and reference_name=%(name)s and status='Open'
and owner=%(assign_to)s""",
args,
):
+ # assign_to function expects a list
+ args["assign_to"] = [args["assign_to"]]
assign_to.add(args)
diff --git a/erpnext/buying/report/procurement_tracker/procurement_tracker.py b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
index e0b02ee..d70ac46 100644
--- a/erpnext/buying/report/procurement_tracker/procurement_tracker.py
+++ b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
@@ -252,7 +252,7 @@
ON pi_item.`purchase_order` = po.`name`
WHERE
pi_item.docstatus = 1
- AND po.status not in ("Closed","Completed","Cancelled")
+ AND po.status not in ('Closed','Completed','Cancelled')
AND pi_item.po_detail IS NOT NULL
"""
)
@@ -271,7 +271,7 @@
pr.docstatus=1
AND pr.name=pr_item.parent
AND pr_item.purchase_order_item IS NOT NULL
- AND pr.status not in ("Closed","Completed","Cancelled")
+ AND pr.status not in ('Closed','Completed','Cancelled')
"""
)
)
@@ -302,7 +302,7 @@
WHERE
parent.docstatus = 1
AND parent.name = child.parent
- AND parent.status not in ("Closed","Completed","Cancelled")
+ AND parent.status not in ('Closed','Completed','Cancelled')
{conditions}
GROUP BY
parent.name, child.item_code
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index f49366a..ded9a30 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -2049,7 +2049,7 @@
journal_entries = frappe.db.sql(
"""
select
- "Journal Entry" as reference_type, t1.name as reference_name,
+ 'Journal Entry' as reference_type, t1.name as reference_name,
t1.remark as remarks, t2.{0} as amount, t2.name as reference_row,
t2.reference_name as against_order, t2.exchange_rate
from
@@ -2104,7 +2104,7 @@
payment_entries_against_order = frappe.db.sql(
"""
select
- "Payment Entry" as reference_type, t1.name as reference_name,
+ 'Payment Entry' as reference_type, t1.name as reference_name,
t1.remarks, t2.allocated_amount as amount, t2.name as reference_row,
t2.reference_name as against_order, t1.posting_date,
t1.{0} as currency, t1.{4} as exchange_rate
@@ -2124,7 +2124,7 @@
if include_unallocated:
unallocated_payment_entries = frappe.db.sql(
"""
- select "Payment Entry" as reference_type, name as reference_name, posting_date,
+ select 'Payment Entry' as reference_type, name as reference_name, posting_date,
remarks, unallocated_amount as amount, {2} as exchange_rate, {3} as currency
from `tabPayment Entry`
where
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index a725f67..5ba314e 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -340,12 +340,12 @@
fields = get_fields("Project", ["name", "project_name"])
searchfields = frappe.get_meta("Project").get_search_fields()
- searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
+ searchfields = " or ".join(["`tabProject`." + field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
"""select {fields} from `tabProject`
where
- `tabProject`.status not in ("Completed", "Cancelled")
+ `tabProject`.status not in ('Completed', 'Cancelled')
and {cond} {scond} {match_cond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
@@ -374,7 +374,7 @@
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1
- and status not in ("Stopped", "Closed") %(fcond)s
+ and status not in ('Stopped', 'Closed') %(fcond)s
and (
(`tabDelivery Note`.is_return = 0 and `tabDelivery Note`.per_billed < 100)
or (`tabDelivery Note`.grand_total = 0 and `tabDelivery Note`.per_billed < 100)
@@ -654,7 +654,7 @@
filter_dict = get_doctype_wise_filters(filters)
query = """select `tabWarehouse`.name,
- CONCAT_WS(" : ", "Actual Qty", ifnull(round(`tabBin`.actual_qty, 2), 0 )) actual_qty
+ CONCAT_WS(' : ', 'Actual Qty', ifnull(round(`tabBin`.actual_qty, 2), 0 )) actual_qty
from `tabWarehouse` left join `tabBin`
on `tabBin`.warehouse = `tabWarehouse`.name {bin_conditions}
where
diff --git a/erpnext/controllers/status_updater.py b/erpnext/controllers/status_updater.py
index 517e080..76a25a0 100644
--- a/erpnext/controllers/status_updater.py
+++ b/erpnext/controllers/status_updater.py
@@ -352,9 +352,9 @@
for args in self.status_updater:
# condition to include current record (if submit or no if cancel)
if self.docstatus == 1:
- args["cond"] = ' or parent="%s"' % self.name.replace('"', '"')
+ args["cond"] = " or parent='%s'" % self.name.replace('"', '"')
else:
- args["cond"] = ' and parent!="%s"' % self.name.replace('"', '"')
+ args["cond"] = " and parent!='%s'" % self.name.replace('"', '"')
self._update_children(args, update_modified)
@@ -384,7 +384,7 @@
args["second_source_condition"] = frappe.db.sql(
""" select ifnull((select sum(%(second_source_field)s)
from `tab%(second_source_dt)s`
- where `%(second_join_field)s`="%(detail_id)s"
+ where `%(second_join_field)s`='%(detail_id)s'
and (`tab%(second_source_dt)s`.docstatus=1)
%(second_source_extra_cond)s), 0) """
% args
@@ -398,7 +398,7 @@
frappe.db.sql(
"""
(select ifnull(sum(%(source_field)s), 0)
- from `tab%(source_dt)s` where `%(join_field)s`="%(detail_id)s"
+ from `tab%(source_dt)s` where `%(join_field)s`='%(detail_id)s'
and (docstatus=1 %(cond)s) %(extra_cond)s)
"""
% args
@@ -445,7 +445,7 @@
ifnull((select
ifnull(sum(if(abs(%(target_ref_field)s) > abs(%(target_field)s), abs(%(target_field)s), abs(%(target_ref_field)s))), 0)
/ sum(abs(%(target_ref_field)s)) * 100
- from `tab%(target_dt)s` where parent="%(name)s" having sum(abs(%(target_ref_field)s)) > 0), 0), 6)
+ from `tab%(target_dt)s` where parent='%(name)s' having sum(abs(%(target_ref_field)s)) > 0), 0), 6)
%(update_modified)s
where name='%(name)s'"""
% args
diff --git a/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py b/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
index 17e332c..b526624 100644
--- a/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
+++ b/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
@@ -23,7 +23,7 @@
def tearDown(self):
frappe.db.sql("delete from `tabMpesa Settings`")
- frappe.db.sql('delete from `tabIntegration Request` where integration_request_service = "Mpesa"')
+ frappe.db.sql("delete from `tabIntegration Request` where integration_request_service = 'Mpesa'")
def test_creation_of_payment_gateway(self):
mode_of_payment = create_mode_of_payment("Mpesa-_Test", payment_type="Phone")
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
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index 631548b..4c88eca 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -1305,7 +1305,7 @@
if not field in searchfields
]
- query_filters = {"disabled": 0, "ifnull(end_of_life, '5050-50-50')": (">", today())}
+ query_filters = {"disabled": 0, "end_of_life": (">", today())}
or_cond_filters = {}
if txt:
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 9ca05b9..8a28454 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -849,7 +849,7 @@
FROM
`tabBOM Item` bom_item
JOIN `tabBOM` bom ON bom.name = bom_item.parent
- JOIN tabItem item ON bom_item.item_code = item.name
+ JOIN `tabItem` item ON bom_item.item_code = item.name
LEFT JOIN `tabItem Default` item_default
ON item.name = item_default.parent and item_default.company = %(company)s
LEFT JOIN `tabUOM Conversion Detail` item_uom
@@ -979,7 +979,7 @@
select distinct so.name, so.transaction_date, so.customer, so.base_grand_total
from `tabSales Order` so, `tabSales Order Item` so_item
where so_item.parent = so.name
- and so.docstatus = 1 and so.status not in ("Stopped", "Closed")
+ and so.docstatus = 1 and so.status not in ('Stopped', 'Closed')
and so.company = %(company)s
and so_item.qty > so_item.work_order_qty {so_filter} {item_filter}
and (exists (select name from `tabBOM` bom where {bom_item}
diff --git a/erpnext/manufacturing/doctype/work_order/work_order.py b/erpnext/manufacturing/doctype/work_order/work_order.py
index 2802310..7b86253 100644
--- a/erpnext/manufacturing/doctype/work_order/work_order.py
+++ b/erpnext/manufacturing/doctype/work_order/work_order.py
@@ -939,7 +939,7 @@
from `tabStock Entry` entry, `tabStock Entry Detail` detail
where
entry.work_order = %(name)s
- and entry.purpose = "Material Transfer for Manufacture"
+ and entry.purpose = 'Material Transfer for Manufacture'
and entry.docstatus = 1
and detail.parent = entry.name
and (detail.item_code = %(item)s or detail.original_item = %(item)s)""",
diff --git a/erpnext/payroll/doctype/payroll_entry/payroll_entry.py b/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
index 1524fb7..a0cef70 100644
--- a/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
+++ b/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
@@ -674,7 +674,7 @@
def get_joining_relieving_condition(start_date, end_date):
cond = """
- and ifnull(t1.date_of_joining, '0000-00-00') <= '%(end_date)s'
+ and ifnull(t1.date_of_joining, '1900-01-01') <= '%(end_date)s'
and ifnull(t1.relieving_date, '2199-12-31') >= '%(start_date)s'
""" % {
"start_date": start_date,
diff --git a/erpnext/payroll/doctype/salary_slip/salary_slip.py b/erpnext/payroll/doctype/salary_slip/salary_slip.py
index 6a35985..e1ccc11 100644
--- a/erpnext/payroll/doctype/salary_slip/salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/salary_slip.py
@@ -508,7 +508,7 @@
SELECT attendance_date, status, leave_type
FROM `tabAttendance`
WHERE
- status in ("Absent", "Half Day", "On leave")
+ status in ('Absent', 'Half Day', 'On leave')
AND employee = %s
AND docstatus = 1
AND attendance_date between %s and %s
diff --git a/erpnext/projects/report/project_profitability/project_profitability.py b/erpnext/projects/report/project_profitability/project_profitability.py
index abbbaf5..aa955bc 100644
--- a/erpnext/projects/report/project_profitability/project_profitability.py
+++ b/erpnext/projects/report/project_profitability/project_profitability.py
@@ -39,17 +39,17 @@
FROM
(SELECT
si.customer_name,si.base_grand_total,
- si.name as voucher_no,tabTimesheet.employee,
- tabTimesheet.title as employee_name,tabTimesheet.parent_project as project,
- tabTimesheet.start_date,tabTimesheet.end_date,
- tabTimesheet.total_billed_hours,tabTimesheet.name as timesheet,
+ si.name as voucher_no,`tabTimesheet`.employee,
+ `tabTimesheet`.title as employee_name,`tabTimesheet`.parent_project as project,
+ `tabTimesheet`.start_date,`tabTimesheet`.end_date,
+ `tabTimesheet`.total_billed_hours,`tabTimesheet`.name as timesheet,
ss.base_gross_pay,ss.total_working_days,
- tabTimesheet.total_billed_hours/(ss.total_working_days * {0}) as utilization
+ `tabTimesheet`.total_billed_hours/(ss.total_working_days * {0}) as utilization
FROM
- `tabSalary Slip Timesheet` as sst join `tabTimesheet` on tabTimesheet.name = sst.time_sheet
- join `tabSales Invoice Timesheet` as sit on sit.time_sheet = tabTimesheet.name
- join `tabSales Invoice` as si on si.name = sit.parent and si.status != "Cancelled"
- join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != "Cancelled" """.format(
+ `tabSalary Slip Timesheet` as sst join `tabTimesheet` on `tabTimesheet`.name = sst.time_sheet
+ join `tabSales Invoice Timesheet` as sit on sit.time_sheet = `tabTimesheet`.name
+ join `tabSales Invoice` as si on si.name = sit.parent and si.status != 'Cancelled'
+ join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != 'Cancelled' """.format(
standard_working_hours
)
if conditions:
@@ -72,23 +72,25 @@
conditions = []
if filters.get("company"):
- conditions.append("tabTimesheet.company={0}".format(frappe.db.escape(filters.get("company"))))
+ conditions.append("`tabTimesheet`.company={0}".format(frappe.db.escape(filters.get("company"))))
if filters.get("start_date"):
- conditions.append("tabTimesheet.start_date>='{0}'".format(filters.get("start_date")))
+ conditions.append("`tabTimesheet`.start_date>='{0}'".format(filters.get("start_date")))
if filters.get("end_date"):
- conditions.append("tabTimesheet.end_date<='{0}'".format(filters.get("end_date")))
+ conditions.append("`tabTimesheet`.end_date<='{0}'".format(filters.get("end_date")))
if filters.get("customer_name"):
conditions.append("si.customer_name={0}".format(frappe.db.escape(filters.get("customer_name"))))
if filters.get("employee"):
- conditions.append("tabTimesheet.employee={0}".format(frappe.db.escape(filters.get("employee"))))
+ conditions.append(
+ "`tabTimesheet`.employee={0}".format(frappe.db.escape(filters.get("employee")))
+ )
if filters.get("project"):
conditions.append(
- "tabTimesheet.parent_project={0}".format(frappe.db.escape(filters.get("project")))
+ "`tabTimesheet`.parent_project={0}".format(frappe.db.escape(filters.get("project")))
)
conditions = " and ".join(conditions)
diff --git a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
index 5ceb2c0..1d4f96b 100644
--- a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
+++ b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
@@ -83,7 +83,7 @@
("gst_hsn_code", " and gst_hsn_code=%(gst_hsn_code)s"),
("company_gstin", " and company_gstin=%(company_gstin)s"),
("from_date", " and posting_date >= %(from_date)s"),
- ("to_date", "and posting_date <= %(to_date)s"),
+ ("to_date", " and posting_date <= %(to_date)s"),
):
if filters.get(opts[0]):
conditions += opts[1]
diff --git a/erpnext/regional/report/irs_1099/irs_1099.py b/erpnext/regional/report/irs_1099/irs_1099.py
index 0f578be..66ade1f 100644
--- a/erpnext/regional/report/irs_1099/irs_1099.py
+++ b/erpnext/regional/report/irs_1099/irs_1099.py
@@ -47,7 +47,7 @@
s.name = gl.party
AND s.irs_1099 = 1
AND gl.fiscal_year = %(fiscal_year)s
- AND gl.party_type = "Supplier"
+ AND gl.party_type = 'Supplier'
AND gl.company = %(company)s
{conditions}
diff --git a/erpnext/regional/report/vat_audit_report/vat_audit_report.py b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
index 70f2c0a..3d486ce 100644
--- a/erpnext/regional/report/vat_audit_report/vat_audit_report.py
+++ b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
@@ -65,7 +65,7 @@
`tab{doctype}`
WHERE
docstatus = 1 {where_conditions}
- and is_opening = "No"
+ and is_opening = 'No'
ORDER BY
posting_date DESC
""".format(
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index d5fd946..f6877e9 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -267,7 +267,7 @@
def set_expired_status():
# filter out submitted non expired quotations whose validity has been ended
- cond = "qo.docstatus = 1 and qo.status != 'Expired' and qo.valid_till < %s"
+ cond = "`tabQuotation`.docstatus = 1 and `tabQuotation`.status != 'Expired' and `tabQuotation`.valid_till < %s"
# check if those QUO have SO against it
so_against_quo = """
SELECT
@@ -275,13 +275,18 @@
WHERE
so_item.docstatus = 1 and so.docstatus = 1
and so_item.parent = so.name
- and so_item.prevdoc_docname = qo.name"""
+ and so_item.prevdoc_docname = `tabQuotation`.name"""
# if not exists any SO, set status as Expired
- frappe.db.sql(
- """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and not exists({so_against_quo})""".format(
- cond=cond, so_against_quo=so_against_quo
- ),
+ frappe.db.multisql(
+ {
+ "mariadb": """UPDATE `tabQuotation` SET `tabQuotation`.status = 'Expired' WHERE {cond} and not exists({so_against_quo})""".format(
+ cond=cond, so_against_quo=so_against_quo
+ ),
+ "postgres": """UPDATE `tabQuotation` SET status = 'Expired' FROM `tabSales Order`, `tabSales Order Item` WHERE {cond} and not exists({so_against_quo})""".format(
+ cond=cond, so_against_quo=so_against_quo
+ ),
+ },
(nowdate()),
)
diff --git a/erpnext/selling/doctype/sales_order/test_sales_order.py b/erpnext/selling/doctype/sales_order/test_sales_order.py
index 45868fb..e5e317c 100644
--- a/erpnext/selling/doctype/sales_order/test_sales_order.py
+++ b/erpnext/selling/doctype/sales_order/test_sales_order.py
@@ -329,7 +329,7 @@
def test_sales_order_on_hold(self):
so = make_sales_order(item_code="_Test Product Bundle Item")
- so.db_set("Status", "On Hold")
+ so.db_set("status", "On Hold")
si = make_sales_invoice(so.name)
self.assertRaises(frappe.ValidationError, create_dn_against_so, so.name)
self.assertRaises(frappe.ValidationError, si.submit)
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
index cc1055c..928ed80 100644
--- a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
@@ -65,7 +65,7 @@
WHERE
so.docstatus = 1
and so.name = so_item.parent
- and so.status not in ("Closed","Completed","Cancelled")
+ and so.status not in ('Closed','Completed','Cancelled')
GROUP BY
so.name,so_item.item_code
""",
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index 9bde6e2..9ffd6df 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -464,7 +464,7 @@
# reset default company
frappe.db.sql(
- """update `tabSingles` set value=""
+ """update `tabSingles` set value=''
where doctype='Global Defaults' and field='default_company'
and value=%s""",
self.name,
@@ -472,7 +472,7 @@
# reset default company
frappe.db.sql(
- """update `tabSingles` set value=""
+ """update `tabSingles` set value=''
where doctype='Chart of Accounts Importer' and field='company'
and value=%s""",
self.name,
diff --git a/erpnext/setup/doctype/email_digest/email_digest.py b/erpnext/setup/doctype/email_digest/email_digest.py
index 42ba6ce..4fc20e6 100644
--- a/erpnext/setup/doctype/email_digest/email_digest.py
+++ b/erpnext/setup/doctype/email_digest/email_digest.py
@@ -198,7 +198,7 @@
todo_list = frappe.db.sql(
"""select *
- from `tabToDo` where (owner=%s or assigned_by=%s) and status="Open"
+ from `tabToDo` where (owner=%s or assigned_by=%s) and status='Open'
order by field(priority, 'High', 'Medium', 'Low') asc, date asc limit 20""",
(user_id, user_id),
as_dict=True,
diff --git a/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py b/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
index 78b3939..7c478bb 100644
--- a/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
+++ b/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
@@ -42,7 +42,7 @@
def delete_bins(self):
frappe.db.sql(
- """delete from tabBin where warehouse in
+ """delete from `tabBin` where warehouse in
(select name from tabWarehouse where company=%s)""",
self.company,
)
@@ -64,7 +64,7 @@
addresses = ["%s" % frappe.db.escape(addr) for addr in addresses]
frappe.db.sql(
- """delete from tabAddress where name in ({addresses}) and
+ """delete from `tabAddress` where name in ({addresses}) and
name not in (select distinct dl1.parent from `tabDynamic Link` dl1
inner join `tabDynamic Link` dl2 on dl1.parent=dl2.parent
and dl1.link_doctype<>dl2.link_doctype)""".format(
@@ -80,7 +80,7 @@
)
frappe.db.sql(
- """update tabCustomer set lead_name=NULL where lead_name in ({leads})""".format(
+ """update `tabCustomer` set lead_name=NULL where lead_name in ({leads})""".format(
leads=",".join(leads)
)
)
@@ -178,7 +178,7 @@
else:
last = 0
- frappe.db.sql("""update tabSeries set current = %s where name=%s""", (last, prefix))
+ frappe.db.sql("""update `tabSeries` set current = %s where name=%s""", (last, prefix))
def delete_version_log(self, doctype, company_fieldname):
frappe.db.sql(
diff --git a/erpnext/stock/doctype/delivery_trip/delivery_trip.py b/erpnext/stock/doctype/delivery_trip/delivery_trip.py
index 73b250d..ff95c50 100644
--- a/erpnext/stock/doctype/delivery_trip/delivery_trip.py
+++ b/erpnext/stock/doctype/delivery_trip/delivery_trip.py
@@ -263,9 +263,9 @@
FROM
`tabDynamic Link` dl
WHERE
- dl.link_doctype="Customer"
+ dl.link_doctype='Customer'
AND dl.link_name=%s
- AND dl.parenttype = "Contact"
+ AND dl.parenttype = 'Contact'
""",
(name),
as_dict=1,
@@ -289,9 +289,9 @@
FROM
`tabDynamic Link` dl
WHERE
- dl.link_doctype="Customer"
+ dl.link_doctype='Customer'
AND dl.link_name=%s
- AND dl.parenttype = "Address"
+ AND dl.parenttype = 'Address'
""",
(name),
as_dict=1,
@@ -388,7 +388,7 @@
if email_recipients:
frappe.msgprint(_("Email sent to {0}").format(", ".join(email_recipients)))
- delivery_trip.db_set("email_notification_sent", True)
+ delivery_trip.db_set("email_notification_sent", 1)
else:
frappe.msgprint(_("No contacts with email IDs found."))
diff --git a/erpnext/stock/doctype/item/item.py b/erpnext/stock/doctype/item/item.py
index b2f5fb7..87fa72d 100644
--- a/erpnext/stock/doctype/item/item.py
+++ b/erpnext/stock/doctype/item/item.py
@@ -1155,7 +1155,7 @@
bin_list = frappe.db.sql(
"""
- select * from tabBin where item_code = %s
+ select * from `tabBin` where item_code = %s
and (reserved_qty > 0 or ordered_qty > 0 or indented_qty > 0 or planned_qty > 0)
and stock_uom != %s
""",
@@ -1171,7 +1171,7 @@
)
# No SLE or documents against item. Bin UOM can be changed safely.
- frappe.db.sql("""update tabBin set stock_uom=%s where item_code=%s""", (stock_uom, item))
+ frappe.db.sql("""update `tabBin` set stock_uom=%s where item_code=%s""", (stock_uom, item))
def get_item_defaults(item_code, company):
diff --git a/erpnext/stock/doctype/item/test_item.py b/erpnext/stock/doctype/item/test_item.py
index d5074e7..3366c73 100644
--- a/erpnext/stock/doctype/item/test_item.py
+++ b/erpnext/stock/doctype/item/test_item.py
@@ -381,8 +381,8 @@
frappe.delete_doc_if_exists("Item Attribute", "Test Item Length")
frappe.db.sql(
- '''delete from `tabItem Variant Attribute`
- where attribute="Test Item Length"'''
+ """delete from `tabItem Variant Attribute`
+ where attribute='Test Item Length' """
)
frappe.flags.attribute_values = None
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index bd60cf0..23e0f1e 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -611,7 +611,7 @@
select
i.name as item_code, i.item_name, bin.warehouse as warehouse, i.has_serial_no, i.has_batch_no
from
- tabBin bin, tabItem i
+ `tabBin` bin, `tabItem` i
where
i.name = bin.item_code
and IFNULL(i.disabled, 0) = 0
@@ -629,7 +629,7 @@
select
i.name as item_code, i.item_name, id.default_warehouse as warehouse, i.has_serial_no, i.has_batch_no
from
- tabItem i, `tabItem Default` id
+ `tabItem` i, `tabItem Default` id
where
i.name = id.parent
and exists(
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index df16643..ab784ca 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -161,8 +161,7 @@
fields = ["name as value", "is_group as expandable"]
filters = [
- ["docstatus", "<", "2"],
- ['ifnull(`parent_warehouse`, "")', "=", parent],
+ ["ifnull(`parent_warehouse`, '')", "=", parent],
["company", "in", (company, None, "")],
]
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 7cff85f..38ad662 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -890,7 +890,7 @@
return frappe.db.sql(
""" select name, price_list_rate, uom
from `tabItem Price` {conditions}
- order by valid_from desc, batch_no desc, uom desc """.format(
+ order by valid_from desc, ifnull(batch_no, '') desc, uom desc """.format(
conditions=conditions
),
args,
diff --git a/erpnext/stock/reorder_item.py b/erpnext/stock/reorder_item.py
index f19c75f..136c78f 100644
--- a/erpnext/stock/reorder_item.py
+++ b/erpnext/stock/reorder_item.py
@@ -105,7 +105,7 @@
for item_code, warehouse, projected_qty in frappe.db.sql(
"""select item_code, warehouse, projected_qty
from tabBin where item_code in ({0})
- and (warehouse != "" and warehouse is not null)""".format(
+ and (warehouse != '' and warehouse is not null)""".format(
", ".join(["%s"] * len(items_to_consider))
),
items_to_consider,
diff --git a/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py b/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
index bcc2139..b68db35 100644
--- a/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
+++ b/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
@@ -73,7 +73,7 @@
"Stock Ledger Entry",
fields=fields,
filters=filters,
- order_by="timestamp(posting_date, posting_time) asc, creation asc",
+ order_by="posting_date asc, posting_time asc, creation asc",
)
diff --git a/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py b/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
index 78c6961..39d84a7 100644
--- a/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
+++ b/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
@@ -106,7 +106,7 @@
"Stock Ledger Entry",
fields=fields,
filters=filters,
- order_by="timestamp(posting_date, posting_time) asc, creation asc",
+ order_by="posting_date asc, posting_time asc, creation asc",
)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 409e238..ef1642e 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -238,7 +238,7 @@
sl_entries = frappe.db.sql(
"""
SELECT
- concat_ws(" ", posting_date, posting_time) AS date,
+ concat_ws(' ', posting_date, posting_time) AS date,
item_code,
warehouse,
actual_qty,
diff --git a/erpnext/stock/stock_balance.py b/erpnext/stock/stock_balance.py
index e05d1c3..14cedd2 100644
--- a/erpnext/stock/stock_balance.py
+++ b/erpnext/stock/stock_balance.py
@@ -118,7 +118,7 @@
select qty, parent_detail_docname, parent, name
from `tabPacked Item` dnpi_in
where item_code = %s and warehouse = %s
- and parenttype="Sales Order"
+ and parenttype='Sales Order'
and item_code != parent_item
and exists (select * from `tabSales Order` so
where name = dnpi_in.parent and docstatus = 1 and status != 'Closed')
@@ -194,7 +194,7 @@
planned_qty = frappe.db.sql(
"""
select sum(qty - produced_qty) from `tabWork Order`
- where production_item = %s and fg_warehouse = %s and status not in ("Stopped", "Completed", "Closed")
+ where production_item = %s and fg_warehouse = %s and status not in ('Stopped', 'Completed', 'Closed')
and docstatus=1 and qty > produced_qty""",
(item_code, warehouse),
)