sql injection fixes
diff --git a/erpnext/accounts/doctype/account/account.py b/erpnext/accounts/doctype/account/account.py
index 2b7a0b1..0ed44c9 100644
--- a/erpnext/accounts/doctype/account/account.py
+++ b/erpnext/accounts/doctype/account/account.py
@@ -241,7 +241,7 @@
rebuild_tree("Account", "parent_account")
def get_master_name(doctype, txt, searchfield, start, page_len, filters):
- conditions = (" and company='%s'"% filters["company"]) if doctype == "Warehouse" else ""
+ conditions = (" and company='%s'"% filters["company"].replace("'", "\'")) if doctype == "Warehouse" else ""
return frappe.db.sql("""select name from `tab%s` where %s like %s %s
order by name limit %s, %s""" %
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index cbe94fd..c5a7028 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -143,7 +143,7 @@
# Update outstanding amt on against voucher
if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
- frappe.db.sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
+ frappe.db.sql("update `tab%s` set outstanding_amount=%s where name=%s",
(against_voucher_type, bal, against_voucher))
def validate_frozen_account(account, adv_adj=None):
diff --git a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
index c1635bd..f4e886b 100644
--- a/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
+++ b/erpnext/accounts/doctype/journal_voucher/journal_voucher.py
@@ -88,7 +88,7 @@
msgprint("You can not enter current voucher in 'Against JV' column",
raise_exception=1)
elif not frappe.db.sql("""select name from `tabJournal Voucher Detail`
- where account = '%s' and docstatus = 1 and parent = '%s'""" %
+ where account = %s and docstatus = 1 and parent = %s""",
(d.account, d.against_jv)):
msgprint("Against JV: %s is not valid." % d.against_jv, raise_exception=1)
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index f0a9ecb..1d7af8a 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -137,7 +137,7 @@
for d in getlist(self.doclist,'entries'):
if d.purchase_order and not d.purchase_order in check_list and not d.purchase_receipt:
check_list.append(d.purhcase_order)
- stopped = frappe.db.sql("select name from `tabPurchase Order` where status = 'Stopped' and name = '%s'" % d.purchase_order)
+ stopped = frappe.db.sql("select name from `tabPurchase Order` where status = 'Stopped' and name = %s", d.purchase_order)
if stopped:
msgprint("One cannot do any transaction against 'Purchase Order' : %s, it's status is 'Stopped'" % (d.purhcase_order))
raise Exception
@@ -238,11 +238,11 @@
def check_prev_docstatus(self):
for d in getlist(self.doclist,'entries'):
if d.purchase_order:
- submitted = frappe.db.sql("select name from `tabPurchase Order` where docstatus = 1 and name = '%s'" % d.purchase_order)
+ submitted = frappe.db.sql("select name from `tabPurchase Order` where docstatus = 1 and name = %s", d.purchase_order)
if not submitted:
frappe.throw("Purchase Order : "+ cstr(d.purchase_order) +" is not submitted")
if d.purchase_receipt:
- submitted = frappe.db.sql("select name from `tabPurchase Receipt` where docstatus = 1 and name = '%s'" % d.purchase_receipt)
+ submitted = frappe.db.sql("select name from `tabPurchase Receipt` where docstatus = 1 and name = %s", d.purchase_receipt)
if not submitted:
frappe.throw("Purchase Receipt : "+ cstr(d.purchase_receipt) +" is not submitted")
diff --git a/erpnext/accounts/doctype/sales_invoice/pos.py b/erpnext/accounts/doctype/sales_invoice/pos.py
index c2d7c4b..74ff03f 100644
--- a/erpnext/accounts/doctype/sales_invoice/pos.py
+++ b/erpnext/accounts/doctype/sales_invoice/pos.py
@@ -15,7 +15,7 @@
condition = "i.is_purchase_item='Yes'"
if item_group and item_group != "All Item Groups":
- condition += " and i.item_group='%s'" % item_group
+ condition += " and i.item_group='%s'" % item_group.replace("'", "\'")
if item:
condition += " and CONCAT(i.name, i.item_name) like %(name)s"
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index 769a01e..a261334 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -257,7 +257,7 @@
def validate_debit_acc(self):
- acc = frappe.db.sql("select debit_or_credit, is_pl_account from tabAccount where name = '%s' and docstatus != 2" % self.doc.debit_to)
+ acc = frappe.db.sql("select debit_or_credit, is_pl_account from tabAccount where name = %s and docstatus != 2", self.doc.debit_to)
if not acc:
msgprint("Account: "+ self.doc.debit_to + " does not exist")
raise Exception
@@ -272,8 +272,11 @@
def validate_fixed_asset_account(self):
"""Validate Fixed Asset Account and whether Income Account Entered Exists"""
for d in getlist(self.doclist,'entries'):
- item = frappe.db.sql("select name,is_asset_item,is_sales_item from `tabItem` where name = '%s' and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now())"% d.item_code)
- acc = frappe.db.sql("select account_type from `tabAccount` where name = '%s' and docstatus != 2" % d.income_account)
+ item = frappe.db.sql("""select name,is_asset_item,is_sales_item from `tabItem`
+ where name = %s and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00'
+ or end_of_life > now())""", d.item_code)
+ acc = frappe.db.sql("""select account_type from `tabAccount`
+ where name = %s and docstatus != 2""", d.income_account)
if not acc:
msgprint("Account: "+d.income_account+" does not exist in the system", raise_exception=True)
elif item and item[0][1] == 'Yes' and not acc[0][0] == 'Fixed Asset Account':
@@ -344,7 +347,9 @@
def validate_proj_cust(self):
"""check for does customer belong to same project as entered.."""
if self.doc.project_name and self.doc.customer:
- res = frappe.db.sql("select name from `tabProject` where name = '%s' and (customer = '%s' or ifnull(customer,'')='')"%(self.doc.project_name, self.doc.customer))
+ res = frappe.db.sql("""select name from `tabProject`
+ where name = %s and (customer = %s or
+ ifnull(customer,'')='')""", (self.doc.project_name, self.doc.customer))
if not res:
msgprint("Customer - %s does not belong to project - %s. \n\nIf you want to use project for multiple customers then please make customer details blank in that project."%(self.doc.customer,self.doc.project_name))
raise Exception
@@ -397,10 +402,13 @@
def get_warehouse(self):
- w = frappe.db.sql("select warehouse from `tabPOS Setting` where ifnull(user,'') = '%s' and company = '%s'" % (frappe.session['user'], self.doc.company))
+ w = frappe.db.sql("""select warehouse from `tabPOS Setting`
+ where ifnull(user,'') = %s and company = %s""",
+ (frappe.session['user'], self.doc.company))
w = w and w[0][0] or ''
if not w:
- ps = frappe.db.sql("select name, warehouse from `tabPOS Setting` where ifnull(user,'') = '' and company = '%s'" % self.doc.company)
+ ps = frappe.db.sql("""select name, warehouse from `tabPOS Setting`
+ where ifnull(user,'') = '' and company = %s""", self.doc.company)
if not ps:
msgprint("To make POS entry, please create POS Setting from Accounts --> POS Setting page and refresh the system.", raise_exception=True)
elif not ps[0][1]:
@@ -439,13 +447,15 @@
def check_prev_docstatus(self):
for d in getlist(self.doclist,'entries'):
if d.sales_order:
- submitted = frappe.db.sql("select name from `tabSales Order` where docstatus = 1 and name = '%s'" % d.sales_order)
+ submitted = frappe.db.sql("""select name from `tabSales Order`
+ where docstatus = 1 and name = %s""", d.sales_order)
if not submitted:
msgprint("Sales Order : "+ cstr(d.sales_order) +" is not submitted")
raise Exception , "Validation Error."
if d.delivery_note:
- submitted = frappe.db.sql("select name from `tabDelivery Note` where docstatus = 1 and name = '%s'" % d.delivery_note)
+ submitted = frappe.db.sql("""select name from `tabDelivery Note`
+ where docstatus = 1 and name = %s""", d.delivery_note)
if not submitted:
msgprint("Delivery Note : "+ cstr(d.delivery_note) +" is not submitted")
raise Exception , "Validation Error."
diff --git a/erpnext/accounts/report/accounts_payable/accounts_payable.py b/erpnext/accounts/report/accounts_payable/accounts_payable.py
index fffc948..b65c1e8 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.py
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.py
@@ -93,7 +93,7 @@
def get_conditions(filters, before_report_date=True):
conditions = ""
if filters.get("company"):
- conditions += " and company='%s'" % filters["company"]
+ conditions += " and company='%s'" % filters["company"].replace("'", "\'")
supplier_accounts = []
if filters.get("account"):
diff --git a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
index 36d02e0..33e0495 100644
--- a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
+++ b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
@@ -51,7 +51,8 @@
if filters.get("account"):
party_accounts = [filters["account"]]
else:
- cond = filters.get("company") and (" and company = '%s'" % filters["company"]) or ""
+ cond = filters.get("company") and (" and company = '%s'" %
+ filters["company"].replace("'", "\'")) or ""
if filters.get("payment_type") == "Incoming":
cond += " and master_type = 'Customer'"
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 4edf774..0964478 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -22,7 +22,7 @@
# if year start date is 2012-04-01, year end date should be 2013-03-31 (hence subdate)
cond = ""
if fiscal_year:
- cond = "name = '%s'" % fiscal_year
+ cond = "name = '%s'" % fiscal_year.replace("'", "\'")
else:
cond = "'%s' >= year_start_date and '%s' <= year_end_date" % \
(date, date)
@@ -88,7 +88,7 @@
and ac.lft >= %s and ac.rgt <= %s
)""" % (acc.lft, acc.rgt))
else:
- cond.append("""gle.account = "%s" """ % (account, ))
+ cond.append("""gle.account = "%s" """ % (account.replace('"', '\"'), ))
bal = frappe.db.sql("""
SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
diff --git a/erpnext/buying/doctype/purchase_order/purchase_order.py b/erpnext/buying/doctype/purchase_order/purchase_order.py
index 1210ee9..fca61e3 100644
--- a/erpnext/buying/doctype/purchase_order/purchase_order.py
+++ b/erpnext/buying/doctype/purchase_order/purchase_order.py
@@ -127,7 +127,8 @@
update_bin(args)
def check_modified_date(self):
- mod_db = frappe.db.sql("select modified from `tabPurchase Order` where name = '%s'" % self.doc.name)
+ mod_db = frappe.db.sql("select modified from `tabPurchase Order` where name = %s",
+ self.doc.name)
date_diff = frappe.db.sql("select TIMEDIFF('%s', '%s')" % ( mod_db[0][0],cstr(self.doc.modified)))
if date_diff and date_diff[0][0]:
@@ -166,7 +167,10 @@
pc_obj.check_docstatus(check = 'Next', doctype = 'Purchase Receipt', docname = self.doc.name, detail_doctype = 'Purchase Receipt Item')
# Check if Purchase Invoice has been submitted against current Purchase Order
- submitted = frappe.db.sql("select t1.name from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2 where t1.name = t2.parent and t2.purchase_order = '%s' and t1.docstatus = 1" % self.doc.name)
+ submitted = frappe.db.sql("""select t1.name
+ from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2
+ where t1.name = t2.parent and t2.purchase_order = %s and t1.docstatus = 1""",
+ self.doc.name)
if submitted:
msgprint("Purchase Invoice : " + cstr(submitted[0][0]) + " has already been submitted !")
raise Exception
diff --git a/erpnext/buying/doctype/quality_inspection/quality_inspection.py b/erpnext/buying/doctype/quality_inspection/quality_inspection.py
index 972757f..4222d68 100644
--- a/erpnext/buying/doctype/quality_inspection/quality_inspection.py
+++ b/erpnext/buying/doctype/quality_inspection/quality_inspection.py
@@ -23,16 +23,19 @@
def on_submit(self):
if self.doc.purchase_receipt_no:
- frappe.db.sql("update `tabPurchase Receipt Item` t1, `tabPurchase Receipt` t2 set t1.qa_no = '%s', t2.modified = '%s' \
- where t1.parent = '%s' and t1.item_code = '%s' and t1.parent = t2.name" \
- % (self.doc.name, self.doc.modified, self.doc.purchase_receipt_no, self.doc.item_code))
+ frappe.db.sql("""update `tabPurchase Receipt Item` t1, `tabPurchase Receipt` t2
+ set t1.qa_no = %s, t2.modified = %s
+ where t1.parent = %s and t1.item_code = %s and t1.parent = t2.name""",
+ (self.doc.name, self.doc.modified, self.doc.purchase_receipt_no,
+ self.doc.item_code))
def on_cancel(self):
if self.doc.purchase_receipt_no:
- frappe.db.sql("update `tabPurchase Receipt Item` t1, `tabPurchase Receipt` t2 set t1.qa_no = '', t2.modified = '%s' \
- where t1.parent = '%s' and t1.item_code = '%s' and t1.parent = t2.name" \
- % (self.doc.modified, self.doc.purchase_receipt_no, self.doc.item_code))
+ frappe.db.sql("""update `tabPurchase Receipt Item` t1, `tabPurchase Receipt` t2
+ set t1.qa_no = '', t2.modified = %s
+ where t1.parent = %s and t1.item_code = %s and t1.parent = t2.name""",
+ (self.doc.modified, self.doc.purchase_receipt_no, self.doc.item_code))
def item_query(doctype, txt, searchfield, start, page_len, filters):
diff --git a/erpnext/buying/doctype/supplier/supplier.py b/erpnext/buying/doctype/supplier/supplier.py
index 878dda5..f937958 100644
--- a/erpnext/buying/doctype/supplier/supplier.py
+++ b/erpnext/buying/doctype/supplier/supplier.py
@@ -63,7 +63,7 @@
def get_contacts(self,nm):
if nm:
- contact_details =frappe.db.convert_to_lists(frappe.db.sql("select name, CONCAT(IFNULL(first_name,''),' ',IFNULL(last_name,'')),contact_no,email_id from `tabContact` where supplier = '%s'"%nm))
+ contact_details =frappe.db.convert_to_lists(frappe.db.sql("select name, CONCAT(IFNULL(first_name,''),' ',IFNULL(last_name,'')),contact_no,email_id from `tabContact` where supplier = %s", nm))
return contact_details
else:
diff --git a/erpnext/controllers/status_updater.py b/erpnext/controllers/status_updater.py
index 40be699..f389b99 100644
--- a/erpnext/controllers/status_updater.py
+++ b/erpnext/controllers/status_updater.py
@@ -118,10 +118,11 @@
args['name'] = d.fields[args['join_field']]
# get all qty where qty > target_field
- item = frappe.db.sql("""select item_code, `%(target_ref_field)s`,
- `%(target_field)s`, parenttype, parent from `tab%(target_dt)s`
- where `%(target_ref_field)s` < `%(target_field)s`
- and name="%(name)s" and docstatus=1""" % args, as_dict=1)
+ item = frappe.db.sql("""select item_code, `{target_ref_field}`,
+ `{target_field}`, parenttype, parent from `{target_dt}`
+ where `{target_ref_field}` < `{target_field}`
+ and name=%s and docstatus=1""".format(**args),
+ args['name'], as_dict=1)
if item:
item = item[0]
item['idx'] = d.idx
@@ -181,9 +182,9 @@
for args in self.status_updater:
# condition to include current record (if submit or no if cancel)
if self.doc.docstatus == 1:
- args['cond'] = ' or parent="%s"' % self.doc.name
+ args['cond'] = ' or parent="%s"' % self.doc.name.replace('"', '\"')
else:
- args['cond'] = ' and parent!="%s"' % self.doc.name
+ args['cond'] = ' and parent!="%s"' % self.doc.name.replace('"', '\"')
args['modified_cond'] = ''
if change_modified:
diff --git a/erpnext/home/page/activity/activity.py b/erpnext/home/page/activity/activity.py
index 0d088bb..8fab54a 100644
--- a/erpnext/home/page/activity/activity.py
+++ b/erpnext/home/page/activity/activity.py
@@ -7,15 +7,16 @@
@frappe.whitelist()
def get_feed(arg=None):
"""get feed"""
+ roles = frappe.get_roles()
return frappe.db.sql("""select
distinct t1.name, t1.feed_type, t1.doc_type, t1.doc_name, t1.subject, t1.owner,
t1.modified
from tabFeed t1, tabDocPerm t2
where t1.doc_type = t2.parent
- and t2.role in ('%s')
+ and t2.role in (%s)
and t2.permlevel = 0
and ifnull(t2.`read`,0) = 1
order by t1.modified desc
- limit %s, %s""" % ("','".join(frappe.get_roles()),
- frappe.form_dict['limit_start'], frappe.form_dict['limit_page_length']),
- as_dict=1)
\ No newline at end of file
+ limit %s, %s""" % (','.join(['%s']*len(roles)), '%s', '%s'),
+ tuple(roles + [frappe.form_dict['limit_start'], frappe.form_dict['limit_page_length']]),
+ as_dict=1)
\ No newline at end of file
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index cdc5979..1630e14 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -141,11 +141,10 @@
msgprint("Employee : %s has already applied for %s between %s and %s on %s. Please refer Leave Application : <a href=\"#Form/Leave Application/%s\">%s</a>" % (self.doc.employee, cstr(d['leave_type']), formatdate(d['from_date']), formatdate(d['to_date']), formatdate(d['posting_date']), d['name'], d['name']), raise_exception = OverlapError)
def validate_max_days(self):
- max_days = frappe.db.sql("select max_days_allowed from `tabLeave Type` where name = '%s'" %(self.doc.leave_type))
- max_days = max_days and flt(max_days[0][0]) or 0
+ max_days = frappe.db.get_value("Leave Type", self.doc.leave_type, "max_days_allowed")
if max_days and self.doc.total_leave_days > max_days:
- msgprint("Sorry ! You cannot apply for %s for more than %s days" % (self.doc.leave_type, max_days))
- raise Exception
+ frappe.throw("Sorry ! You cannot apply for %s for more than %s days" %
+ (self.doc.leave_type, max_days))
def validate_leave_approver(self):
employee = frappe.bean("Employee", self.doc.employee)
@@ -328,11 +327,12 @@
txt = "%" + cstr(txt) + "%"
if "Leave Approver" in frappe.user.get_roles():
+ user = frappe.session.user.replace('"', '\"')
condition = """and (exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name and ela.leave_approver= "%s") or
not exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name)
- or user_id = "%s")""" % (frappe.session.user, frappe.session.user)
+ or user_id = "%s")""" % (user, user)
else:
from frappe.widgets.reportview import build_match_conditions
condition = build_match_conditions("Employee")
diff --git a/erpnext/hr/doctype/salary_manager/salary_manager.py b/erpnext/hr/doctype/salary_manager/salary_manager.py
index 622b89b..0420f77 100644
--- a/erpnext/hr/doctype/salary_manager/salary_manager.py
+++ b/erpnext/hr/doctype/salary_manager/salary_manager.py
@@ -37,7 +37,7 @@
cond = ''
for f in ['company', 'branch', 'department', 'designation', 'grade']:
if self.doc.fields.get(f):
- cond += " and t1." + f + " = '" + self.doc.fields.get(f) + "'"
+ cond += " and t1." + f + " = '" + self.doc.fields.get(f).replace("'", "\'") + "'"
return cond
@@ -58,7 +58,7 @@
def get_month_details(self, year, month):
- ysd = frappe.db.sql("select year_start_date from `tabFiscal Year` where name ='%s'"%year)[0][0]
+ ysd = frappe.db.get_value("Fiscal Year", year, "year_start_date")
if ysd:
from dateutil.relativedelta import relativedelta
import calendar, datetime
@@ -117,8 +117,8 @@
cond = self.get_filter_condition()
ss_list = frappe.db.sql("""
select t1.name from `tabSalary Slip` t1
- where t1.docstatus = 0 and month = '%s' and fiscal_year = '%s' %s
- """ % (self.doc.month, self.doc.fiscal_year, cond))
+ where t1.docstatus = 0 and month = %s and fiscal_year = %s %s
+ """ % ('%s', '%s', cond), (self.doc.month, self.doc.fiscal_year))
return ss_list
@@ -179,8 +179,8 @@
cond = self.get_filter_condition()
tot = frappe.db.sql("""
select sum(rounded_total) from `tabSalary Slip` t1
- where t1.docstatus = 1 and month = '%s' and fiscal_year = '%s' %s
- """ % (self.doc.month, self.doc.fiscal_year, cond))
+ where t1.docstatus = 1 and month = %s and fiscal_year = %s %s
+ """ % ('%s', '%s', cond), (self.doc.month, self.doc.fiscal_year))
return flt(tot[0][0])
diff --git a/erpnext/hr/doctype/salary_manager/test_salary_manager.py b/erpnext/hr/doctype/salary_manager/test_salary_manager.py
deleted file mode 100644
index 071d9d4..0000000
--- a/erpnext/hr/doctype/salary_manager/test_salary_manager.py
+++ /dev/null
@@ -1,205 +0,0 @@
-# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
-# License: GNU General Public License v3. See license.txt
-
-from __future__ import unicode_literals
-import unittest
-import frappe
-
-test_records = []
-
-# from frappe.model.doc import Document
-# from frappe.model.code import get_obj
-# frappe.db.sql = frappe.db.sql
-#
-# class TestSalaryManager(unittest.TestCase):
-# def setUp(self):
-# frappe.db.begin()
-# for rec in [des1, dep1, branch1, grade1, comp1, emp1, emp2]:
-# rec.save(1)
-#
-# ss1[0].employee = emp1.name
-# for s in ss1: s.save(1)
-# for s in ss1[1:]:
-# frappe.db.sql("update `tabSalary Structure Earning` set parent = '%s' where name = '%s'" % (ss1[0].name, s.name))
-# frappe.db.sql("update `tabSalary Structure Deduction` set parent = '%s' where name = '%s'" % (ss1[0].name, s.name))
-#
-#
-# ss2[0].employee = emp2.name
-# for s in ss2: s.save(1)
-# for s in ss2[1:]:
-# frappe.db.sql("update `tabSalary Structure Earning` set parent = '%s' where name = '%s'" % (ss2[0].name, s.name))
-# frappe.db.sql("update `tabSalary Structure Deduction` set parent = '%s' where name = '%s'" % (ss2[0].name, s.name))
-#
-# sman.save()
-# self.sm = get_obj('Salary Manager')
-# leave.save(1)
-# self.sm.create_sal_slip()
-#
-# def test_creation(self):
-# ssid = frappe.db.sql("""
-# select name, department
-# from `tabSalary Slip`
-# where month = '08' and fiscal_year='2011-2012'""")
-#
-# self.assertTrue(len(ssid)==1)
-# self.assertTrue(ssid[0][1] == 'dep1')
-#
-#
-# def test_lwp_calc(self):
-# ss = frappe.db.sql("""
-# select payment_days
-# from `tabSalary Slip`
-# where month = '08' and fiscal_year='2011-2012' and employee = '%s'
-# """ % emp1.name)
-#
-# self.assertTrue(ss[0][0]==27)
-#
-# def test_net_pay(self):
-# ss = frappe.db.sql("""
-# select rounded_total
-# from `tabSalary Slip`
-# where month = '08'
-# and fiscal_year='2011-2012' and employee = '%s'""" % emp1.name)
-# self.assertTrue(ss[0][0]==67)
-#
-# def test_submit(self):
-# self.sm.submit_salary_slip()
-# ss = frappe.db.sql("""
-# select docstatus
-# from `tabSalary Slip`
-# where month = '08'
-# and fiscal_year='2011-2012' and employee = '%s'""" % emp1.name)
-# self.assertTrue(ss[0][0]==1)
-#
-# def tearDown(self):
-# frappe.db.rollback()
-#
-# #--------------------------------------------
-# # test data
-# #--------------------------------------------
-# des1 = Document(fielddata={
-# 'name':'des1',
-# 'doctype':'Designation',
-# 'designation_name':'des1'
-# })
-#
-# dep1 = Document(fielddata={
-# 'name':'dep1',
-# 'doctype':'Department',
-# 'department_name' : 'dep1'
-# })
-#
-# branch1 = Document(fielddata={
-# 'name':'branch1',
-# 'doctype':'Branch',
-# 'branch' : 'branch1'
-# })
-#
-# comp1 = Document(fielddata={
-# 'name':'comp1',
-# 'doctype':'Company',
-# 'abbr':'c1',
-# 'company_name' : 'comp1'
-# })
-#
-# grade1 = Document(fielddata={
-# 'name':'grade1',
-# 'doctype':'Grade',
-# 'grade_name' : 'grade1'
-# })
-#
-# emp1 = Document(fielddata={
-# 'doctype':'Employee',
-# 'employee_number':'emp1',
-# 'department':'dep1',
-# 'designation':'des1',
-# 'branch' : 'branch1',
-# 'company':'comp1',
-# 'grade':'grade1',
-# 'naming_series':'EMP/',
-# 'status':'Active',
-# 'docstatus':0,
-# 'employee_name':'emp1'
-# })
-#
-# emp2 = Document(fielddata={
-# 'doctype':'Employee',
-# 'employee_number':'emp2',
-# 'department':'dep1',
-# 'designation':'des2',
-# 'branch' : 'branch1',
-# 'company':'comp1',
-# 'naming_series':'EMP/',
-# 'grade':'grade1',
-# 'status':'Active',
-#
-# })
-#
-# ss1 = [
-# Document(fielddata={
-# 'doctype':'Salary Structure',
-# 'docstatus':0,
-# 'employee':'emp1',
-# 'is_active':'Yes',
-# 'department': 'dep1',
-# 'designation' : 'des1',
-# 'employee_name': 'emp1'
-# }),
-# Document(fielddata={
-# 'parenttype':'Salary Structure',
-# 'parentfield':'earning_details',
-# 'doctype':'Salary Structure Earning',
-# 'e_type' : 'Basic',
-# 'depend_on_lwp':1,
-# 'modified_value':100
-# }),
-# Document(fielddata={
-# 'parenttype':'Salary Structure',
-# 'parentfield':'earning_details',
-# 'doctype':'Salary Structure Deduction',
-# 'd_type':'TDS',
-# 'd_modified_amt':20
-# })
-# ]
-#
-# ss2 = [
-# Document(fielddata={
-# 'doctype':'Salary Structure',
-# 'is_active':'Yes',
-# 'docstatus':0,
-# }),
-# Document(fielddata={
-# 'parenttype':'Salary Structure',
-# 'parentfield':'deduction_details',
-# 'doctype':'Salary Structure Earning',
-# 'e_type' : 'Basic',
-# 'modified_value':100
-# }),
-# Document(fielddata={
-# 'parenttype':'Salary Structure',
-# 'parentfield':'deduction_details',
-# 'doctype':'Salary Structure Deduction',
-# 'd_type':'TDS',
-# 'd_modified_amt':20
-# })
-# ]
-#
-# sman = Document(fielddata={
-# 'name':'Salary Manager',
-# 'doctype':'Salary Manager',
-# 'company': 'comp1',
-# 'department':'dep1',
-# 'designation':'des1',
-# 'month': '08',
-# 'fiscal_year':'2011-2012'
-# })
-#
-# leave = Document(fielddata = {
-# 'doctype':'Leave Application',
-# 'employee':'emp1',
-# 'from_date':'2011-08-12',
-# 'to_date':'2011-08-15',
-# 'total_leave_days':'4',
-# 'leave_type':'Leave Without Pay',
-# 'docstatus':1
-# })
diff --git a/erpnext/hr/report/employee_birthday/employee_birthday.py b/erpnext/hr/report/employee_birthday/employee_birthday.py
index f1f4be9..51a5051 100644
--- a/erpnext/hr/report/employee_birthday/employee_birthday.py
+++ b/erpnext/hr/report/employee_birthday/employee_birthday.py
@@ -32,6 +32,7 @@
"Dec"].index(filters["month"]) + 1
conditions += " and month(date_of_birth) = '%s'" % month
- if filters.get("company"): conditions += " and company = '%s'" % filters["company"]
+ if filters.get("company"): conditions += " and company = '%s'" % \
+ filters["company"].repalce("'", "\'")
return conditions
\ No newline at end of file
diff --git a/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py b/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
index 24ab7e7..8098db4 100644
--- a/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
+++ b/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
@@ -18,16 +18,18 @@
fiscal_years = [filters["fiscal_year"]]
else:
fiscal_years = frappe.db.sql_list("select name from `tabFiscal Year` order by name desc")
-
- employee_in = '", "'.join([e.name for e in employees])
-
+
allocations = frappe.db.sql("""select employee, fiscal_year, leave_type, total_leaves_allocated
from `tabLeave Allocation`
- where docstatus=1 and employee in ("%s")""" % employee_in, as_dict=True)
- applications = frappe.db.sql("""select employee, fiscal_year, leave_type, SUM(total_leave_days) as leaves
- from `tabLeave Application`
- where status="Approved" and docstatus = 1 and employee in ("%s")
- group by employee, fiscal_year, leave_type""" % employee_in, as_dict=True)
+ where docstatus=1 and employee in (%s)""" %
+ ','.join(['%s']*len(employees)), employees, as_dict=True)
+
+ applications = frappe.db.sql("""select employee, fiscal_year, leave_type,
+ SUM(total_leave_days) as leaves
+ from `tabLeave Application`
+ where status="Approved" and docstatus = 1 and employee in (%s)
+ group by employee, fiscal_year, leave_type""" %
+ ','.join(['%s']*len(employees)), employees, as_dict=True)
columns = [
"Fiscal Year", "Employee:Link/Employee:150", "Employee Name::200", "Department::150"
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index 5d1a5d8..2c6d642 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -271,8 +271,8 @@
for d in check_list:
bom_list, count = [self.doc.name], 0
while (len(bom_list) > count ):
- boms = frappe.db.sql(" select %s from `tabBOM Item` where %s = '%s' " %
- (d[0], d[1], cstr(bom_list[count])))
+ boms = frappe.db.sql(" select %s from `tabBOM Item` where %s = %s " %
+ (d[0], d[1], '%s'), cstr(bom_list[count]))
count = count + 1
for b in boms:
if b[0] == self.doc.name:
@@ -389,10 +389,6 @@
ch.docstatus = self.doc.docstatus
ch.save(1)
- def get_parent_bom_list(self, bom_no):
- p_bom = frappe.db.sql("select parent from `tabBOM Item` where bom_no = '%s'" % bom_no)
- return p_bom and [i[0] for i in p_bom] or []
-
def validate_bom_links(self):
if not self.doc.is_active:
act_pbom = frappe.db.sql("""select distinct bom_item.parent from `tabBOM Item` bom_item
diff --git a/erpnext/manufacturing/doctype/workstation/workstation.py b/erpnext/manufacturing/doctype/workstation/workstation.py
index 3dd2fc9..2829f41 100644
--- a/erpnext/manufacturing/doctype/workstation/workstation.py
+++ b/erpnext/manufacturing/doctype/workstation/workstation.py
@@ -3,13 +3,7 @@
from __future__ import unicode_literals
import frappe
-
from frappe.utils import flt
-from frappe.model import db_exists
-from frappe.model.bean import copy_doclist
-
-
-
class DocType:
def __init__(self, doc, doclist=[]):
@@ -17,11 +11,15 @@
self.doclist = doclist
def update_bom_operation(self):
- bom_list = frappe.db.sql(" select DISTINCT parent from `tabBOM Operation` where workstation = '%s'" % self.doc.name)
+ bom_list = frappe.db.sql("""select DISTINCT parent from `tabBOM Operation`
+ where workstation = %s""", self.doc.name)
for bom_no in bom_list:
- frappe.db.sql("update `tabBOM Operation` set hour_rate = '%s' where parent = '%s' and workstation = '%s'"%( self.doc.hour_rate, bom_no[0], self.doc.name))
+ frappe.db.sql("""update `tabBOM Operation` set hour_rate = %s
+ where parent = %s and workstation = %s""",
+ (self.doc.hour_rate, bom_no[0], self.doc.name))
def on_update(self):
- frappe.db.set(self.doc, 'overhead', flt(self.doc.hour_rate_electricity) + flt(self.doc.hour_rate_consumable) + flt(self.doc.hour_rate_rent))
+ frappe.db.set(self.doc, 'overhead', flt(self.doc.hour_rate_electricity) +
+ flt(self.doc.hour_rate_consumable) + flt(self.doc.hour_rate_rent))
frappe.db.set(self.doc, 'hour_rate', flt(self.doc.hour_rate_labour) + flt(self.doc.overhead))
self.update_bom_operation()
\ No newline at end of file
diff --git a/erpnext/selling/doctype/opportunity/opportunity.py b/erpnext/selling/doctype/opportunity/opportunity.py
index 9edc261..d2f7a2e 100644
--- a/erpnext/selling/doctype/opportunity/opportunity.py
+++ b/erpnext/selling/doctype/opportunity/opportunity.py
@@ -38,7 +38,8 @@
return ret
def get_cust_address(self,name):
- details = frappe.db.sql("select customer_name, address, territory, customer_group from `tabCustomer` where name = '%s' and docstatus != 2" %(name), as_dict = 1)
+ details = frappe.db.sql("""select customer_name, address, territory, customer_group
+ from `tabCustomer` where name = %s and docstatus != 2""", (name), as_dict = 1)
if details:
ret = {
'customer_name': details and details[0]['customer_name'] or '',
@@ -48,7 +49,9 @@
}
# ********** get primary contact details (this is done separately coz. , in case there is no primary contact thn it would not be able to fetch customer details in case of join query)
- contact_det = frappe.db.sql("select contact_name, contact_no, email_id from `tabContact` where customer = '%s' and is_customer = 1 and is_primary_contact = 'Yes' and docstatus != 2" %(name), as_dict = 1)
+ contact_det = frappe.db.sql("""select contact_name, contact_no, email_id
+ from `tabContact` where customer = %s and is_customer = 1
+ and is_primary_contact = 'Yes' and docstatus != 2""", name, as_dict = 1)
ret['contact_person'] = contact_det and contact_det[0]['contact_name'] or ''
ret['contact_no'] = contact_det and contact_det[0]['contact_no'] or ''
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index 165660d..fd915de 100644
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -71,7 +71,7 @@
d.transaction_date = self.doc.transaction_date
tot_avail_qty = frappe.db.sql("select projected_qty from `tabBin` \
- where item_code = '%s' and warehouse = '%s'" % (d.item_code,d.warehouse))
+ where item_code = %s and warehouse = %s", (d.item_code,d.warehouse))
d.projected_qty = tot_avail_qty and flt(tot_avail_qty[0][0]) or 0
def validate_sales_mntc_quotation(self):
@@ -94,7 +94,9 @@
def validate_proj_cust(self):
if self.doc.project_name and self.doc.customer_name:
- res = frappe.db.sql("select name from `tabProject` where name = '%s' and (customer = '%s' or ifnull(customer,'')='')"%(self.doc.project_name, self.doc.customer))
+ res = frappe.db.sql("""select name from `tabProject` where name = %s
+ and (customer = %s or ifnull(customer,'')='')""",
+ (self.doc.project_name, self.doc.customer))
if not res:
msgprint("Customer - %s does not belong to project - %s. \n\nIf you want to use project for multiple customers then please make customer details blank in project - %s."%(self.doc.customer,self.doc.project_name,self.doc.project_name))
raise Exception
@@ -187,7 +189,10 @@
msgprint("Delivery Note : " + cstr(submit_dn[0][0]) + " has been submitted against " + cstr(self.doc.doctype) + ". Please cancel Delivery Note : " + cstr(submit_dn[0][0]) + " first and then cancel "+ cstr(self.doc.doctype), raise_exception = 1)
# Checks Sales Invoice
- submit_rv = frappe.db.sql("select t1.name from `tabSales Invoice` t1,`tabSales Invoice Item` t2 where t1.name = t2.parent and t2.sales_order = '%s' and t1.docstatus = 1" % (self.doc.name))
+ submit_rv = frappe.db.sql("""select t1.name
+ from `tabSales Invoice` t1,`tabSales Invoice Item` t2
+ where t1.name = t2.parent and t2.sales_order = %s and t1.docstatus = 1""",
+ self.doc.name)
if submit_rv:
msgprint("Sales Invoice : " + cstr(submit_rv[0][0]) + " has already been submitted against " +cstr(self.doc.doctype)+ ". Please cancel Sales Invoice : "+ cstr(submit_rv[0][0]) + " first and then cancel "+ cstr(self.doc.doctype), raise_exception = 1)
@@ -209,8 +214,9 @@
pro_order[0][0], raise_exception=1)
def check_modified_date(self):
- mod_db = frappe.db.sql("select modified from `tabSales Order` where name = '%s'" % self.doc.name)
- date_diff = frappe.db.sql("select TIMEDIFF('%s', '%s')" % ( mod_db[0][0],cstr(self.doc.modified)))
+ mod_db = frappe.db.get_value("Sales Order", self.doc.name, "modified")
+ date_diff = frappe.db.sql("select TIMEDIFF('%s', '%s')" %
+ ( mod_db, cstr(self.doc.modified)))
if date_diff and date_diff[0][0]:
msgprint("%s: %s has been modified after you have opened. Please Refresh"
% (self.doc.doctype, self.doc.name), raise_exception=1)
diff --git a/erpnext/selling/doctype/sms_center/sms_center.py b/erpnext/selling/doctype/sms_center/sms_center.py
index e1284e2..7da6f32 100644
--- a/erpnext/selling/doctype/sms_center/sms_center.py
+++ b/erpnext/selling/doctype/sms_center/sms_center.py
@@ -5,8 +5,6 @@
import frappe
from frappe.utils import cstr
-from frappe.model import db_exists
-from frappe.model.bean import copy_doclist
from frappe.model.code import get_obj
from frappe import msgprint, _
@@ -18,11 +16,16 @@
def create_receiver_list(self):
rec, where_clause = '', ''
if self.doc.send_to == 'All Customer Contact':
- where_clause = self.doc.customer and " and customer = '%s'" % self.doc.customer or " and ifnull(customer, '') != ''"
+ where_clause = self.doc.customer and " and customer = '%s'" % \
+ self.doc.customer.replace("'", "\'") or " and ifnull(customer, '') != ''"
if self.doc.send_to == 'All Supplier Contact':
- where_clause = self.doc.supplier and " and ifnull(is_supplier, 0) = 1 and supplier = '%s'" % self.doc.supplier or " and ifnull(supplier, '') != ''"
+ where_clause = self.doc.supplier and \
+ " and ifnull(is_supplier, 0) = 1 and supplier = '%s'" % \
+ self.doc.supplier.replace("'", "\'") or " and ifnull(supplier, '') != ''"
if self.doc.send_to == 'All Sales Partner Contact':
- where_clause = self.doc.sales_partner and " and ifnull(is_sales_partner, 0) = 1 and sales_partner = '%s'" % self.doc.sales_partner or " and ifnull(sales_partner, '') != ''"
+ where_clause = self.doc.sales_partner and \
+ " and ifnull(is_sales_partner, 0) = 1 and sales_partner = '%s'" % \
+ self.doc.sales_partner.replace("'", "\'") or " and ifnull(sales_partner, '') != ''"
if self.doc.send_to in ['All Contact', 'All Customer Contact', 'All Supplier Contact', 'All Sales Partner Contact']:
rec = frappe.db.sql("""select CONCAT(ifnull(first_name,''), '', ifnull(last_name,'')),
@@ -34,8 +37,11 @@
ifnull(mobile_no,'')!='' and docstatus != 2 and status='Open'""")
elif self.doc.send_to == 'All Employee (Active)':
- where_clause = self.doc.department and " and department = '%s'" % self.doc.department or ""
- where_clause += self.doc.branch and " and branch = '%s'" % self.doc.branch or ""
+ where_clause = self.doc.department and " and department = '%s'" % \
+ self.doc.department.replace("'", "\'") or ""
+ where_clause += self.doc.branch and " and branch = '%s'" % \
+ self.doc.branch.replace("'", "\'") or ""
+
rec = frappe.db.sql("""select employee_name, cell_number from
`tabEmployee` where status = 'Active' and docstatus < 2 and
ifnull(cell_number,'')!='' %s""", where_clause)
diff --git a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
index ad409f0..c7ee35f 100644
--- a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
+++ b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
@@ -30,25 +30,28 @@
dt_item.item_code, dt_item.qty, dt_item.base_amount, st.sales_person,
st.allocated_percentage, dt_item.base_amount*st.allocated_percentage/100
from `tab%s` dt, `tab%s Item` dt_item, `tabSales Team` st
- where st.parent = dt.name and dt.name = dt_item.parent and st.parenttype = '%s'
+ where st.parent = dt.name and dt.name = dt_item.parent and st.parenttype = %s
and dt.docstatus = 1 %s order by st.sales_person, dt.name desc""" %
- (date_field, filters["doc_type"], filters["doc_type"], filters["doc_type"], conditions),
- tuple(items), as_list=1)
+ (date_field, filters["doc_type"], filters["doc_type"], '%s', conditions),
+ tuple([filters["doc_type"]] + items), as_list=1)
return entries
def get_conditions(filters, date_field):
conditions = ""
- if filters.get("company"): conditions += " and dt.company = '%s'" % filters["company"]
- if filters.get("customer"): conditions += " and dt.customer = '%s'" % filters["customer"]
- if filters.get("territory"): conditions += " and dt.territory = '%s'" % filters["territory"]
+ if filters.get("company"): conditions += " and dt.company = '%s'" % \
+ filters["company"].replace("'", "\'")
+ if filters.get("customer"): conditions += " and dt.customer = '%s'" % \
+ filters["customer"].replace("'", "\'")
+ if filters.get("territory"): conditions += " and dt.territory = '%s'" % \
+ filters["territory"].replace("'", "\'")
if filters.get("from_date"): conditions += " and dt.%s >= '%s'" % \
(date_field, filters["from_date"])
if filters.get("to_date"): conditions += " and dt.%s <= '%s'" % (date_field, filters["to_date"])
if filters.get("sales_person"): conditions += " and st.sales_person = '%s'" % \
- filters["sales_person"]
+ filters["sales_person"].replace("'", "\'")
items = get_items(filters)
if items:
diff --git a/erpnext/setup/doctype/authorization_rule/authorization_rule.py b/erpnext/setup/doctype/authorization_rule/authorization_rule.py
index d7c8cb2..71f5caf 100644
--- a/erpnext/setup/doctype/authorization_rule/authorization_rule.py
+++ b/erpnext/setup/doctype/authorization_rule/authorization_rule.py
@@ -5,13 +5,8 @@
import frappe
from frappe.utils import cint, cstr, flt, has_common
-from frappe.model import db_exists
-from frappe.model.bean import copy_doclist
from frappe import msgprint
-
-
-
class DocType:
def __init__(self, d, dl):
self.doc, self.doclist = d, dl
@@ -38,12 +33,12 @@
def validate_master_name(self):
if self.doc.based_on == 'Customerwise Discount' and \
- not frappe.db.sql("select name from tabCustomer where name = '%s' and docstatus != 2" % \
- (self.doc.master_name)):
+ not frappe.db.sql("""select name from tabCustomer
+ where name = %s and docstatus != 2""", (self.doc.master_name)):
msgprint("Please select valid Customer Name for Customerwise Discount",
raise_exception=1)
elif self.doc.based_on == 'Itemwise Discount' and \
- not frappe.db.sql("select name from tabItem where name = '%s' and docstatus != 2" % \
+ not frappe.db.sql("select name from tabItem where name = %s and docstatus != 2",
(self.doc.master_name)):
msgprint("Please select valid Item Name for Itemwise Discount", raise_exception=1)
elif (self.doc.based_on == 'Grand Total' or \
@@ -64,7 +59,7 @@
Applicable To (Role).", raise_exception=1)
elif self.doc.system_user and self.doc.approving_role and \
has_common([self.doc.approving_role], [x[0] for x in \
- frappe.db.sql("select role from `tabUserRole` where parent = '%s'" % \
+ frappe.db.sql("select role from `tabUserRole` where parent = %s", \
(self.doc.system_user))]):
msgprint("System User : %s is assigned role : %s. So rule does not make sense" %
(self.doc.system_user,self.doc.approving_role), raise_exception=1)
diff --git a/erpnext/setup/doctype/email_digest/email_digest.py b/erpnext/setup/doctype/email_digest/email_digest.py
index b4d5b7d..aeabf49 100644
--- a/erpnext/setup/doctype/email_digest/email_digest.py
+++ b/erpnext/setup/doctype/email_digest/email_digest.py
@@ -339,7 +339,7 @@
def get_new_count(self, doctype, label, docstatus=0, filter_by_company=True):
if filter_by_company:
- company = """and company="%s" """ % self.doc.company
+ company = """and company="%s" """ % self.doc.company.replace('"', '\"')
else:
company = ""
count = frappe.db.sql("""select count(*) from `tab%s`
diff --git a/erpnext/setup/doctype/sales_partner/sales_partner.py b/erpnext/setup/doctype/sales_partner/sales_partner.py
index 288cadf..eefabf6 100644
--- a/erpnext/setup/doctype/sales_partner/sales_partner.py
+++ b/erpnext/setup/doctype/sales_partner/sales_partner.py
@@ -14,10 +14,12 @@
if self.doc.partner_website and not self.doc.partner_website.startswith("http"):
self.doc.partner_website = "http://" + self.doc.partner_website
- def get_contacts(self,nm):
+ def get_contacts(self, nm):
if nm:
- contact_details =frappe.db.convert_to_lists(frappe.db.sql("select name, CONCAT(IFNULL(first_name,''),' ',IFNULL(last_name,'')),contact_no,email_id from `tabContact` where sales_partner = '%s'"%nm))
- return contact_details
+ return frappe.db.convert_to_lists(frappe.db.sql("""
+ select name, CONCAT(IFNULL(first_name,''),
+ ' ',IFNULL(last_name,'')),contact_no,email_id
+ from `tabContact` where sales_partner = %s""", nm))
else:
return ''
diff --git a/erpnext/stock/doctype/delivery_note/delivery_note.py b/erpnext/stock/doctype/delivery_note/delivery_note.py
index 42d1eec..a7c4923 100644
--- a/erpnext/stock/doctype/delivery_note/delivery_note.py
+++ b/erpnext/stock/doctype/delivery_note/delivery_note.py
@@ -46,7 +46,8 @@
def set_actual_qty(self):
for d in getlist(self.doclist, 'delivery_note_details'):
if d.item_code and d.warehouse:
- actual_qty = frappe.db.sql("select actual_qty from `tabBin` where item_code = '%s' and warehouse = '%s'" % (d.item_code, d.warehouse))
+ actual_qty = frappe.db.sql("""select actual_qty from `tabBin`
+ where item_code = %s and warehouse = %s""", (d.item_code, d.warehouse))
d.actual_qty = actual_qty and flt(actual_qty[0][0]) or 0
def so_required(self):
@@ -104,7 +105,9 @@
def validate_proj_cust(self):
"""check for does customer belong to same project as entered.."""
if self.doc.project_name and self.doc.customer:
- res = frappe.db.sql("select name from `tabProject` where name = '%s' and (customer = '%s' or ifnull(customer,'')='')"%(self.doc.project_name, self.doc.customer))
+ res = frappe.db.sql("""select name from `tabProject`
+ where name = %s and (customer = %s or
+ ifnull(customer,'')='')""", (self.doc.project_name, self.doc.customer))
if not res:
msgprint("Customer - %s does not belong to project - %s. \n\nIf you want to use project for multiple customers then please make customer details blank in project - %s."%(self.doc.customer,self.doc.project_name,self.doc.project_name))
raise Exception
@@ -201,12 +204,18 @@
frappe.msgprint("Packing Error:\n" + err_msg, raise_exception=1)
def check_next_docstatus(self):
- submit_rv = frappe.db.sql("select t1.name from `tabSales Invoice` t1,`tabSales Invoice Item` t2 where t1.name = t2.parent and t2.delivery_note = '%s' and t1.docstatus = 1" % (self.doc.name))
+ submit_rv = frappe.db.sql("""select t1.name
+ from `tabSales Invoice` t1,`tabSales Invoice Item` t2
+ where t1.name = t2.parent and t2.delivery_note = %s and t1.docstatus = 1""",
+ (self.doc.name))
if submit_rv:
msgprint("Sales Invoice : " + cstr(submit_rv[0][0]) + " has already been submitted !")
raise Exception , "Validation Error."
- submit_in = frappe.db.sql("select t1.name from `tabInstallation Note` t1, `tabInstallation Note Item` t2 where t1.name = t2.parent and t2.prevdoc_docname = '%s' and t1.docstatus = 1" % (self.doc.name))
+ submit_in = frappe.db.sql("""select t1.name
+ from `tabInstallation Note` t1, `tabInstallation Note Item` t2
+ where t1.name = t2.parent and t2.prevdoc_docname = %s and t1.docstatus = 1""",
+ (self.doc.name))
if submit_in:
msgprint("Installation Note : "+cstr(submit_in[0][0]) +" has already been submitted !")
raise Exception , "Validation Error."
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index b797e43..d1e86a8 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -248,7 +248,10 @@
self.make_gl_entries()
def check_next_docstatus(self):
- submit_rv = frappe.db.sql("select t1.name from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2 where t1.name = t2.parent and t2.purchase_receipt = '%s' and t1.docstatus = 1" % (self.doc.name))
+ submit_rv = frappe.db.sql("""select t1.name
+ from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2
+ where t1.name = t2.parent and t2.purchase_receipt = %s and t1.docstatus = 1""",
+ (self.doc.name))
if submit_rv:
msgprint("Purchase Invoice : " + cstr(self.submit_rv[0][0]) + " has already been submitted !")
raise Exception , "Validation Error."
@@ -259,13 +262,13 @@
self.check_for_stopped_status(pc_obj)
# Check if Purchase Invoice has been submitted against current Purchase Order
- # pc_obj.check_docstatus(check = 'Next', doctype = 'Purchase Invoice', docname = self.doc.name, detail_doctype = 'Purchase Invoice Item')
-
- submitted = frappe.db.sql("select t1.name from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2 where t1.name = t2.parent and t2.purchase_receipt = '%s' and t1.docstatus = 1" % self.doc.name)
+ submitted = frappe.db.sql("""select t1.name
+ from `tabPurchase Invoice` t1,`tabPurchase Invoice Item` t2
+ where t1.name = t2.parent and t2.purchase_receipt = %s and t1.docstatus = 1""",
+ self.doc.name)
if submitted:
- msgprint("Purchase Invoice : " + cstr(submitted[0][0]) + " has already been submitted !")
- raise Exception
-
+ frappe.throw("Purchase Invoice : " + cstr(submitted[0][0]) +
+ " has already been submitted !")
frappe.db.set(self.doc,'status','Cancelled')
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index 276b369..9bb9911 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -60,8 +60,9 @@
if not self.doc.fields.get(k):
msgprint("Stock Ledger Entry: '%s' is mandatory" % k, raise_exception = 1)
elif k == 'warehouse':
- if not frappe.db.sql("select name from tabWarehouse where name = '%s'" % self.doc.fields.get(k)):
- msgprint("Warehouse: '%s' does not exist in the system. Please check." % self.doc.fields.get(k), raise_exception = 1)
+ if not frappe.db.exists("Warehouse", self.doc.fields.get(k)):
+ msgprint("Warehouse: '%s' does not exist in the system. Please check." %
+ self.doc.fields.get(k), raise_exception = 1)
def validate_item(self):
item_det = frappe.db.sql("""select name, has_batch_no, docstatus,
@@ -78,9 +79,10 @@
frappe.throw("Batch number is mandatory for Item '%s'" % self.doc.item_code)
# check if batch belongs to item
- if not frappe.db.sql("""select name from `tabBatch`
- where item='%s' and name ='%s' and docstatus != 2""" % (self.doc.item_code, self.doc.batch_no)):
- frappe.throw("'%s' is not a valid Batch Number for Item '%s'" % (self.doc.batch_no, self.doc.item_code))
+ if not frappe.db.get_value("Batch",
+ {"item": self.doc.item_code, "name": self.doc.batch_no}):
+ frappe.throw("'%s' is not a valid Batch Number for Item '%s'" %
+ (self.doc.batch_no, self.doc.item_code))
if not self.doc.stock_uom:
self.doc.stock_uom = item_det.stock_uom
diff --git a/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py b/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
index 5160e83..8ff8938 100644
--- a/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
+++ b/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
@@ -3,11 +3,7 @@
from __future__ import unicode_literals
import frappe
-
-from frappe.utils import cstr, flt, now, cint
-from frappe.model import db_exists
-from frappe.model.bean import copy_doclist
-from frappe.model.code import get_obj
+from frappe.utils import cstr, flt, cint
from frappe import msgprint, _
@@ -33,8 +29,7 @@
msgprint("Please Enter Conversion Factor.")
raise Exception
- stock_uom = frappe.db.sql("select stock_uom from `tabItem` where name = '%s'" % self.doc.item_code)
- stock_uom = stock_uom and stock_uom[0][0]
+ stock_uom = frappe.db.get_value("Item", self.doc.item_code, "stock_uom")
if cstr(self.doc.new_stock_uom) == cstr(stock_uom):
msgprint("Item Master is already updated with New Stock UOM " + cstr(self.doc.new_stock_uom))
raise Exception
@@ -49,9 +44,20 @@
def update_bin(self):
# update bin
if flt(self.doc.conversion_factor) != flt(1):
- frappe.db.sql("update `tabBin` set stock_uom = '%s' , indented_qty = ifnull(indented_qty,0) * %s, ordered_qty = ifnull(ordered_qty,0) * %s, reserved_qty = ifnull(reserved_qty,0) * %s, planned_qty = ifnull(planned_qty,0) * %s, projected_qty = actual_qty + ordered_qty + indented_qty + planned_qty - reserved_qty where item_code = '%s'" % (self.doc.new_stock_uom, self.doc.conversion_factor, self.doc.conversion_factor, self.doc.conversion_factor, self.doc.conversion_factor, self.doc.item_code) )
+ frappe.db.sql("""update `tabBin`
+ set stock_uom = %s,
+ indented_qty = ifnull(indented_qty,0) * %s,
+ ordered_qty = ifnull(ordered_qty,0) * %s,
+ reserved_qty = ifnull(reserved_qty,0) * %s,
+ planned_qty = ifnull(planned_qty,0) * %s,
+ projected_qty = actual_qty + ordered_qty + indented_qty +
+ planned_qty - reserved_qty
+ where item_code = %s""", (self.doc.new_stock_uom, self.doc.conversion_factor,
+ self.doc.conversion_factor, self.doc.conversion_factor,
+ self.doc.conversion_factor, self.doc.item_code))
else:
- frappe.db.sql("update `tabBin` set stock_uom = '%s' where item_code = '%s'" % (self.doc.new_stock_uom, self.doc.item_code) )
+ frappe.db.sql("update `tabBin` set stock_uom = %s where item_code = %s",
+ (self.doc.new_stock_uom, self.doc.item_code) )
# acknowledge user
msgprint(" All Bins Updated Successfully.")
@@ -61,9 +67,13 @@
from erpnext.stock.stock_ledger import update_entries_after
if flt(self.doc.conversion_factor) != flt(1):
- frappe.db.sql("update `tabStock Ledger Entry` set stock_uom = '%s', actual_qty = ifnull(actual_qty,0) * '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.conversion_factor, self.doc.item_code))
+ frappe.db.sql("""update `tabStock Ledger Entry`
+ set stock_uom = %s, actual_qty = ifnull(actual_qty,0) * %s
+ where item_code = %s""",
+ (self.doc.new_stock_uom, self.doc.conversion_factor, self.doc.item_code))
else:
- frappe.db.sql("update `tabStock Ledger Entry` set stock_uom = '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.item_code))
+ frappe.db.sql("""update `tabStock Ledger Entry` set stock_uom=%s
+ where item_code=%s""", (self.doc.new_stock_uom, self.doc.item_code))
# acknowledge user
msgprint("Stock Ledger Entries Updated Successfully.")
diff --git a/erpnext/support/doctype/customer_issue/customer_issue.py b/erpnext/support/doctype/customer_issue/customer_issue.py
index 9f4b3db..128accd 100644
--- a/erpnext/support/doctype/customer_issue/customer_issue.py
+++ b/erpnext/support/doctype/customer_issue/customer_issue.py
@@ -27,7 +27,10 @@
self.doc.resolved_by = frappe.session.user
def on_cancel(self):
- lst = frappe.db.sql("select t1.name from `tabMaintenance Visit` t1, `tabMaintenance Visit Purpose` t2 where t2.parent = t1.name and t2.prevdoc_docname = '%s' and t1.docstatus!=2"%(self.doc.name))
+ lst = frappe.db.sql("""select t1.name
+ from `tabMaintenance Visit` t1, `tabMaintenance Visit Purpose` t2
+ where t2.parent = t1.name and t2.prevdoc_docname = %s and t1.docstatus!=2""",
+ (self.doc.name))
if lst:
lst1 = ','.join([x[0] for x in lst])
msgprint("Maintenance Visit No. "+lst1+" already created against this customer issue. So can not be Cancelled")
diff --git a/erpnext/support/doctype/maintenance_visit/maintenance_visit.py b/erpnext/support/doctype/maintenance_visit/maintenance_visit.py
index 78cd428..1ddf67b 100644
--- a/erpnext/support/doctype/maintenance_visit/maintenance_visit.py
+++ b/erpnext/support/doctype/maintenance_visit/maintenance_visit.py
@@ -18,18 +18,12 @@
self.doclist = doclist
def get_item_details(self, item_code):
- item = frappe.db.sql("select item_name,description from `tabItem` where name = '%s'" %(item_code), as_dict=1)
- ret = {
- 'item_name' : item and item[0]['item_name'] or '',
- 'description' : item and item[0]['description'] or ''
- }
- return ret
+ return frappe.db.get_value("Item", item_code, ["item_name", "description"], as_dict=1)
def validate_serial_no(self):
for d in getlist(self.doclist, 'maintenance_visit_details'):
- if d.serial_no and not frappe.db.sql("select name from `tabSerial No` where name = '%s' and docstatus != 2" % d.serial_no):
- msgprint("Serial No: "+ d.serial_no + " not exists in the system")
- raise Exception
+ if d.serial_no and not frappe.db.exists("Serial No", d.serial_no):
+ frappe.throw("Serial No: "+ d.serial_no + " not exists in the system")
def validate(self):
diff --git a/erpnext/support/doctype/newsletter/newsletter.py b/erpnext/support/doctype/newsletter/newsletter.py
index ad5c0a7..72d05af 100644
--- a/erpnext/support/doctype/newsletter/newsletter.py
+++ b/erpnext/support/doctype/newsletter/newsletter.py
@@ -59,9 +59,9 @@
self.send_to_doctype = "Lead"
conditions = []
if self.doc.lead_source and self.doc.lead_source != "All":
- conditions.append(" and source='%s'" % self.doc.lead_source)
+ conditions.append(" and source='%s'" % self.doc.lead_source.replace("'", "\'"))
if self.doc.lead_status and self.doc.lead_status != "All":
- conditions.append(" and status='%s'" % self.doc.lead_status)
+ conditions.append(" and status='%s'" % self.doc.lead_status.replace("'", "\'"))
if conditions:
conditions = "".join(conditions)
diff --git a/erpnext/utilities/cleanup_data.py b/erpnext/utilities/cleanup_data.py
index d8c342d..c6a78c3 100644
--- a/erpnext/utilities/cleanup_data.py
+++ b/erpnext/utilities/cleanup_data.py
@@ -34,7 +34,8 @@
"Job Applicant", "Web Page", "Website Slideshow", "Blog Post", "Blog Category", "Blogger",
"Time Log", "Time Log Batch", "Workflow"]
for d in trans:
- for t in frappe.db.sql("select options from tabDocField where parent='%s' and fieldtype='Table'" % d):
+ for t in frappe.db.sql("select options from tabDocField \
+ where parent=%s and fieldtype='Table'", d):
frappe.db.sql("delete from `tab%s`" % (t))
frappe.db.sql("delete from `tab%s`" % (d))
print "Deleted " + d
@@ -95,11 +96,11 @@
'BOM': ''
}
for d in masters.keys():
- for t in frappe.db.sql("select options from tabDocField where parent='%s' \
- and fieldtype='Table'" % d):
- frappe.db.sql("delete from `tab%s`" % (t))
- lst = '"'+'","'.join(masters[d])+ '"'
- frappe.db.sql("delete from `tab%s` where name not in (%s)" % (d, lst))
+ for t in frappe.db.sql("select options from tabDocField where parent=%s \
+ and fieldtype='Table'", d):
+ frappe.db.sql("delete from `tab%s`" % (t))
+ frappe.db.sql("delete from `tab%s` where name not in (%s)" %
+ (d, ', '.join(['%s']*len(masters[d]), masters[d])))
print "Deleted " + d
@@ -125,7 +126,8 @@
def delete_main_masters():
main_masters = ['Fiscal Year', 'Company', 'DefaultValue']
for d in main_masters:
- for t in frappe.db.sql("select options from tabDocField where parent='%s' and fieldtype='Table'" % d):
+ for t in frappe.db.sql("select options from tabDocField \
+ where parent=%s and fieldtype='Table'", d):
frappe.db.sql("delete from `tab%s`" % (t))
frappe.db.sql("delete from `tab%s`" % (d))
print "Deleted " + d
diff --git a/erpnext/utilities/doctype/contact/contact.py b/erpnext/utilities/doctype/contact/contact.py
index cc7ffc0..948b753 100644
--- a/erpnext/utilities/doctype/contact/contact.py
+++ b/erpnext/utilities/doctype/contact/contact.py
@@ -30,20 +30,27 @@
def validate_primary_contact(self):
if self.doc.is_primary_contact == 1:
if self.doc.customer:
- frappe.db.sql("update tabContact set is_primary_contact=0 where customer = '%s'" % (self.doc.customer))
+ frappe.db.sql("update tabContact set is_primary_contact=0 where customer = %s",
+ (self.doc.customer))
elif self.doc.supplier:
- frappe.db.sql("update tabContact set is_primary_contact=0 where supplier = '%s'" % (self.doc.supplier))
+ frappe.db.sql("update tabContact set is_primary_contact=0 where supplier = %s",
+ (self.doc.supplier))
elif self.doc.sales_partner:
- frappe.db.sql("update tabContact set is_primary_contact=0 where sales_partner = '%s'" % (self.doc.sales_partner))
+ frappe.db.sql("""update tabContact set is_primary_contact=0
+ where sales_partner = %s""", (self.doc.sales_partner))
else:
if self.doc.customer:
- if not frappe.db.sql("select name from tabContact where is_primary_contact=1 and customer = '%s'" % (self.doc.customer)):
+ if not frappe.db.sql("select name from tabContact \
+ where is_primary_contact=1 and customer = %s", (self.doc.customer)):
self.doc.is_primary_contact = 1
elif self.doc.supplier:
- if not frappe.db.sql("select name from tabContact where is_primary_contact=1 and supplier = '%s'" % (self.doc.supplier)):
+ if not frappe.db.sql("select name from tabContact \
+ where is_primary_contact=1 and supplier = %s", (self.doc.supplier)):
self.doc.is_primary_contact = 1
elif self.doc.sales_partner:
- if not frappe.db.sql("select name from tabContact where is_primary_contact=1 and sales_partner = '%s'" % (self.doc.sales_partner)):
+ if not frappe.db.sql("select name from tabContact \
+ where is_primary_contact=1 and sales_partner = %s",
+ self.doc.sales_partner):
self.doc.is_primary_contact = 1
def on_trash(self):