Changes to support refactor in frappe pg-poc branch (#15287)
* Remove quotes from sql to make it compatible with postgres as well
* Fix queries
- Replace mysql specifc queries with standard ones
* Make repo URL chages to test pg-poc
* Add root passowrd to test site config
* Fix quotes issue
* Remove debug flag from a pricing rule query
* Remove python 3.6 version from travis.yml
* Fix improper query issue
* Fix incorrect query
* Fix a query
- This fix need to be changed when we will start supporting postgres
since date_format is not supported by postgres
* Get price list map as dict
* Convert price_list_currency_map to dict
diff --git a/erpnext/accounts/doctype/budget/budget.py b/erpnext/accounts/doctype/budget/budget.py
index c83249b..b76cdf3 100644
--- a/erpnext/accounts/doctype/budget/budget.py
+++ b/erpnext/accounts/doctype/budget/budget.py
@@ -15,7 +15,7 @@
class Budget(Document):
def autoname(self):
- self.name = make_autoname(self.get(frappe.scrub(self.budget_against))
+ self.name = make_autoname(self.get(frappe.scrub(self.budget_against))
+ "/" + self.fiscal_year + "/.###")
def validate(self):
@@ -89,7 +89,7 @@
if args.get('company') and not args.fiscal_year:
args.fiscal_year = get_fiscal_year(args.get('posting_date'), company=args.get('company'))[0]
- frappe.flags.exception_approver_role = frappe.get_cached_value('Company',
+ frappe.flags.exception_approver_role = frappe.get_cached_value('Company',
args.get('company'), 'exception_budget_approver_role')
if not args.account:
@@ -106,12 +106,12 @@
and frappe.db.get_value("Account", {"name": args.account, "root_type": "Expense"})):
if args.project and budget_against == 'project':
- condition = "and b.project='%s'" % frappe.db.escape(args.project)
+ condition = "and b.project=%s" % frappe.db.escape(args.project)
args.budget_against_field = "Project"
-
+
elif args.cost_center and budget_against == 'cost_center':
cc_lft, cc_rgt = frappe.db.get_value("Cost Center", args.cost_center, ["lft", "rgt"])
- condition = """and exists(select name from `tabCost Center`
+ condition = """and exists(select name from `tabCost Center`
where lft<=%s and rgt>=%s and name=b.cost_center)""" % (cc_lft, cc_rgt)
args.budget_against_field = "Cost Center"
@@ -126,13 +126,13 @@
b.action_if_annual_budget_exceeded, b.action_if_accumulated_monthly_budget_exceeded,
b.action_if_annual_budget_exceeded_on_mr, b.action_if_accumulated_monthly_budget_exceeded_on_mr,
b.action_if_annual_budget_exceeded_on_po, b.action_if_accumulated_monthly_budget_exceeded_on_po
- from
+ from
`tabBudget` b, `tabBudget Account` ba
where
- b.name=ba.parent and b.fiscal_year=%s
+ b.name=ba.parent and b.fiscal_year=%s
and ba.account=%s and b.docstatus=1
{condition}
- """.format(condition=condition,
+ """.format(condition=condition,
budget_against_field=frappe.scrub(args.get("budget_against_field"))),
(args.fiscal_year, args.account), as_dict=True)
@@ -151,12 +151,12 @@
args["month_end_date"] = get_last_day(args.posting_date)
- compare_expense_with_budget(args, budget_amount,
+ compare_expense_with_budget(args, budget_amount,
_("Accumulated Monthly"), monthly_action, budget.budget_against, amount)
if yearly_action in ("Stop", "Warn") and monthly_action != "Stop" \
and yearly_action != monthly_action:
- compare_expense_with_budget(args, flt(budget.budget_amount),
+ compare_expense_with_budget(args, flt(budget.budget_amount),
_("Annual"), yearly_action, budget.budget_against, amount)
def compare_expense_with_budget(args, budget_amount, action_for, action, budget_against, amount=0):
@@ -166,9 +166,9 @@
currency = frappe.get_cached_value('Company', args.company, 'default_currency')
msg = _("{0} Budget for Account {1} against {2} {3} is {4}. It will exceed by {5}").format(
- _(action_for), frappe.bold(args.account), args.budget_against_field,
+ _(action_for), frappe.bold(args.account), args.budget_against_field,
frappe.bold(budget_against),
- frappe.bold(fmt_money(budget_amount, currency=currency)),
+ frappe.bold(fmt_money(budget_amount, currency=currency)),
frappe.bold(fmt_money(diff, currency=currency)))
if (frappe.flags.exception_approver_role
@@ -250,12 +250,12 @@
condition1 = " and gle.posting_date <= %(month_end_date)s" \
if args.get("month_end_date") else ""
if args.budget_against_field == "Cost Center":
- lft_rgt = frappe.db.get_value(args.budget_against_field,
+ lft_rgt = frappe.db.get_value(args.budget_against_field,
args.budget_against, ["lft", "rgt"], as_dict=1)
args.update(lft_rgt)
- condition2 = """and exists(select name from `tabCost Center`
+ condition2 = """and exists(select name from `tabCost Center`
where lft>=%(lft)s and rgt<=%(rgt)s and name=gle.cost_center)"""
-
+
elif args.budget_against_field == "Project":
condition2 = "and exists(select name from `tabProject` where name=gle.project and gle.project = %(budget_against)s)"
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index e6fe6ca..db93cf9 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -162,7 +162,7 @@
def update_outstanding_amt(account, party_type, party, against_voucher_type, against_voucher, on_cancel=False):
if party_type and party:
- party_condition = " and party_type='{0}' and party='{1}'"\
+ party_condition = " and party_type={0} and party={1}"\
.format(frappe.db.escape(party_type), frappe.db.escape(party))
else:
party_condition = ""
diff --git a/erpnext/accounts/doctype/loyalty_program/loyalty_program.py b/erpnext/accounts/doctype/loyalty_program/loyalty_program.py
index d840304..563165b 100644
--- a/erpnext/accounts/doctype/loyalty_program/loyalty_program.py
+++ b/erpnext/accounts/doctype/loyalty_program/loyalty_program.py
@@ -19,7 +19,7 @@
condition = ''
if company:
- condition = " and company='%s' " % frappe.db.escape(company)
+ condition = " and company=%s " % frappe.db.escape(company)
if not include_expired_entry:
condition += " and expiry_date>='%s' " % expiry_date
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 6a361a4..7a37e0b 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -299,12 +299,12 @@
if self.payment_type == "Receive" \
and self.base_total_allocated_amount < self.base_received_amount + total_deductions \
and self.total_allocated_amount < self.paid_amount + (total_deductions / self.source_exchange_rate):
- self.unallocated_amount = (self.base_received_amount + total_deductions -
+ self.unallocated_amount = (self.base_received_amount + total_deductions -
self.base_total_allocated_amount) / self.source_exchange_rate
elif self.payment_type == "Pay" \
and self.base_total_allocated_amount < (self.base_paid_amount - total_deductions) \
and self.total_allocated_amount < self.received_amount + (total_deductions / self.target_exchange_rate):
- self.unallocated_amount = (self.base_paid_amount - (total_deductions +
+ self.unallocated_amount = (self.base_paid_amount - (total_deductions +
self.base_total_allocated_amount)) / self.target_exchange_rate
def set_difference_amount(self):
@@ -549,7 +549,7 @@
# Get positive outstanding sales /purchase invoices/ Fees
condition = ""
if args.get("voucher_type") and args.get("voucher_no"):
- condition = " and voucher_type='{0}' and voucher_no='{1}'"\
+ condition = " and voucher_type={0} and voucher_no={1}"\
.format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"]))
# Add cost center condition
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index 7cd951a..ec48e71 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -13,20 +13,20 @@
def get_unreconciled_entries(self):
self.get_nonreconciled_payment_entries()
self.get_invoice_entries()
-
+
def get_nonreconciled_payment_entries(self):
self.check_mandatory_to_fetch()
-
+
payment_entries = self.get_payment_entries()
journal_entries = self.get_jv_entries()
-
+
self.add_payment_entries(payment_entries + journal_entries)
-
+
def get_payment_entries(self):
order_doctype = "Sales Order" if self.party_type=="Customer" else "Purchase Order"
- payment_entries = get_advance_payment_entries(self.party_type, self.party,
+ payment_entries = get_advance_payment_entries(self.party_type, self.party,
self.receivable_payable_account, order_doctype, against_all_orders=True)
-
+
return payment_entries
def get_jv_entries(self):
@@ -38,8 +38,8 @@
journal_entries = frappe.db.sql("""
select
- "Journal Entry" as reference_type, t1.name as reference_name,
- t1.posting_date, t1.remark as remarks, t2.name as reference_row,
+ "Journal Entry" as reference_type, t1.name as reference_name,
+ t1.posting_date, t1.remark as remarks, t2.name as reference_row,
{dr_or_cr} as amount, t2.is_advance
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
@@ -47,8 +47,8 @@
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
and t2.party_type = %(party_type)s and t2.party = %(party)s
and t2.account = %(account)s and {dr_or_cr} > 0
- and (t2.reference_type is null or t2.reference_type = '' or
- (t2.reference_type in ('Sales Order', 'Purchase Order')
+ and (t2.reference_type is null or t2.reference_type = '' or
+ (t2.reference_type in ('Sales Order', 'Purchase Order')
and t2.reference_name is not null and t2.reference_name != ''))
and (CASE
WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
@@ -106,7 +106,7 @@
self.validate_invoice()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency")
-
+
lst = []
for e in self.get('payments'):
if e.invoice_number and e.allocated_amount:
@@ -124,11 +124,11 @@
'unadjusted_amount' : flt(e.amount),
'allocated_amount' : flt(e.allocated_amount)
}))
-
+
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
-
+
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
@@ -171,8 +171,8 @@
frappe.throw(_("Please select Allocated Amount, Invoice Type and Invoice Number in atleast one row"))
def check_condition(self):
- cond = " and posting_date >= '{0}'".format(frappe.db.escape(self.from_date)) if self.from_date else ""
- cond += " and posting_date <= '{0}'".format(frappe.db.escape(self.to_date)) if self.to_date else ""
+ cond = " and posting_date >= {0}".format(frappe.db.escape(self.from_date)) if self.from_date else ""
+ cond += " and posting_date <= {0}".format(frappe.db.escape(self.to_date)) if self.to_date else ""
dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable'
else "credit_in_account_currency")
diff --git a/erpnext/accounts/doctype/pos_profile/pos_profile.py b/erpnext/accounts/doctype/pos_profile/pos_profile.py
index bf2e20c..9e4006c 100644
--- a/erpnext/accounts/doctype/pos_profile/pos_profile.py
+++ b/erpnext/accounts/doctype/pos_profile/pos_profile.py
@@ -107,7 +107,7 @@
if pos_profile.get('item_groups'):
# Get items based on the item groups defined in the POS profile
for data in pos_profile.get('item_groups'):
- item_groups.extend(["'%s'" % frappe.db.escape(d.name) for d in get_child_nodes('Item Group', data.item_group)])
+ item_groups.extend(["%s" % frappe.db.escape(d.name) for d in get_child_nodes('Item Group', data.item_group)])
return list(set(item_groups))
diff --git a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
index a44ac00..780759c 100644
--- a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
+++ b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
@@ -255,10 +255,12 @@
if parent_groups:
if allow_blank: parent_groups.append('')
- condition = " ifnull("+field+", '') in ('" + \
- "', '".join([frappe.db.escape(d) for d in parent_groups])+"')"
- frappe.flags.tree_conditions[key] = condition
+ condition = "ifnull({field}, '') in ({parent_groups})".format(
+ field=field,
+ parent_groups=", ".join([frappe.db.escape(d) for d in parent_groups])
+ )
+ frappe.flags.tree_conditions[key] = condition
return condition
diff --git a/erpnext/accounts/doctype/tax_rule/tax_rule.py b/erpnext/accounts/doctype/tax_rule/tax_rule.py
index 5b8bcce..57b5ddb 100644
--- a/erpnext/accounts/doctype/tax_rule/tax_rule.py
+++ b/erpnext/accounts/doctype/tax_rule/tax_rule.py
@@ -75,7 +75,7 @@
for d in filters:
if conds:
conds += " and "
- conds += """ifnull({0}, '') = '{1}'""".format(d, frappe.db.escape(cstr(filters[d])))
+ conds += """ifnull({0}, '') = {1}""".format(d, frappe.db.escape(cstr(filters[d])))
if self.from_date and self.to_date:
conds += """ and ((from_date > '{from_date}' and from_date < '{to_date}') or
@@ -152,7 +152,7 @@
customer_group_condition = get_customer_group_condition(value)
conditions.append("ifnull({0}, '') in ('', {1})".format(key, customer_group_condition))
else:
- conditions.append("ifnull({0}, '') in ('', '{1}')".format(key, frappe.db.escape(cstr(value))))
+ conditions.append("ifnull({0}, '') in ('', {1})".format(key, frappe.db.escape(cstr(value))))
tax_rule = frappe.db.sql("""select * from `tabTax Rule`
where {0}""".format(" and ".join(conditions)), as_dict = True)
@@ -180,7 +180,7 @@
def get_customer_group_condition(customer_group):
condition = ""
- customer_groups = ["'%s'"%(frappe.db.escape(d.name)) for d in get_parent_customer_groups(customer_group)]
+ customer_groups = ["%s"%(frappe.db.escape(d.name)) for d in get_parent_customer_groups(customer_group)]
if customer_groups:
condition = ",".join(['%s'] * len(customer_groups))%(tuple(customer_groups))
return condition
\ No newline at end of file
diff --git a/erpnext/accounts/party.py b/erpnext/accounts/party.py
index 5f8d52f..056cd79 100644
--- a/erpnext/accounts/party.py
+++ b/erpnext/accounts/party.py
@@ -443,7 +443,7 @@
# fetch and append data from Activity Log
data += frappe.db.sql("""select {fields}
from `tabActivity Log`
- where reference_doctype="{doctype}" and reference_name="{name}"
+ where reference_doctype={doctype} and reference_name={name}
and status!='Success' and creation > {after}
{group_by} order by creation desc
""".format(doctype=frappe.db.escape(doctype), name=frappe.db.escape(name), fields=fields,
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
index d517a56..b48856e 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
@@ -100,7 +100,7 @@
net_profit_loss = get_net_profit_loss(income, expense, companies, filters.company, company_currency, True)
return income, expense, net_profit_loss
-
+
def get_cash_flow_data(fiscal_year, companies, filters):
cash_flow_accounts = get_cash_flow_accounts()
@@ -122,7 +122,7 @@
# add first net income in operations section
if net_profit_loss:
net_profit_loss.update({
- "indent": 1,
+ "indent": 1,
"parent_account": cash_flow_accounts[0]['section_header']
})
data.append(net_profit_loss)
@@ -268,7 +268,7 @@
return all_companies, companies
def get_subsidiary_companies(company):
- lft, rgt = frappe.get_cached_value('Company',
+ lft, rgt = frappe.get_cached_value('Company',
company, ["lft", "rgt"])
return frappe.db.sql_list("""select name from `tabCompany`
@@ -322,7 +322,7 @@
accounts_by_name, ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
- company_lft, company_rgt = frappe.get_cached_value('Company',
+ company_lft, company_rgt = frappe.get_cached_value('Company',
filters.get('company'), ["lft", "rgt"])
additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
@@ -330,7 +330,7 @@
gl_entries = frappe.db.sql("""select gl.posting_date, gl.account, gl.debit, gl.credit, gl.is_opening, gl.company,
gl.fiscal_year, gl.debit_in_account_currency, gl.credit_in_account_currency, gl.account_currency,
acc.account_name, acc.account_number
- from `tabGL Entry` gl, `tabAccount` acc where acc.name = gl.account and gl.company in
+ from `tabGL Entry` gl, `tabAccount` acc where acc.name = gl.account and gl.company in
(select name from `tabCompany` where lft >= %(company_lft)s and rgt <= %(company_rgt)s)
{additional_conditions} and gl.posting_date <= %(to_date)s and acc.lft >= %(lft)s and acc.rgt <= %(rgt)s
order by gl.account, gl.posting_date""".format(additional_conditions=additional_conditions),
@@ -368,10 +368,10 @@
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
if not filters.get('finance_book') or (filters.get('finance_book') == company_finance_book):
- additional_conditions.append("ifnull(finance_book, '') in ('%s', '')" %
+ additional_conditions.append("ifnull(finance_book, '') in (%s, '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
- additional_conditions.append("ifnull(finance_book, '') = '%s' " %
+ additional_conditions.append("ifnull(finance_book, '') = %s " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 74ca258..e7eff63 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -389,10 +389,10 @@
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
if not filters.get('finance_book') or (filters.get('finance_book') == company_finance_book):
- additional_conditions.append("ifnull(finance_book, '') in ('%s', '')" %
+ additional_conditions.append("ifnull(finance_book, '') in (%s, '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
- additional_conditions.append("ifnull(finance_book, '') = '%s' " %
+ additional_conditions.append("ifnull(finance_book, '') = %s " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 6fbe97d..92ffd12 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -99,7 +99,7 @@
cond = []
if date:
- cond.append("posting_date <= '%s'" % frappe.db.escape(cstr(date)))
+ cond.append("posting_date <= %s" % frappe.db.escape(cstr(date)))
else:
# get balance of all entries that exist
date = nowdate()
@@ -158,14 +158,14 @@
if acc.account_currency == frappe.get_cached_value('Company', acc.company, "default_currency"):
in_account_currency = False
else:
- cond.append("""gle.account = "%s" """ % (frappe.db.escape(account, percent=False), ))
+ cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False), ))
if party_type and party:
- cond.append("""gle.party_type = "%s" and gle.party = "%s" """ %
+ cond.append("""gle.party_type = %s and gle.party = %s """ %
(frappe.db.escape(party_type), frappe.db.escape(party, percent=False)))
if company:
- cond.append("""gle.company = "%s" """ % (frappe.db.escape(company, percent=False)))
+ cond.append("""gle.company = %s """ % (frappe.db.escape(company, percent=False)))
if account or (party_type and party):
if in_account_currency:
@@ -183,7 +183,7 @@
def get_count_on(account, fieldname, date):
cond = []
if date:
- cond.append("posting_date <= '%s'" % frappe.db.escape(cstr(date)))
+ cond.append("posting_date <= %s" % frappe.db.escape(cstr(date)))
else:
# get balance of all entries that exist
date = nowdate()
@@ -218,7 +218,7 @@
and ac.lft >= %s and ac.rgt <= %s
)""" % (acc.lft, acc.rgt))
else:
- cond.append("""gle.account = "%s" """ % (frappe.db.escape(account, percent=False), ))
+ cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False), ))
entries = frappe.db.sql("""
SELECT name, posting_date, account, party_type, party,debit,credit,
diff --git a/erpnext/assets/doctype/location/location.py b/erpnext/assets/doctype/location/location.py
index 4d73f11..317894c 100644
--- a/erpnext/assets/doctype/location/location.py
+++ b/erpnext/assets/doctype/location/location.py
@@ -203,12 +203,11 @@
from
`tab{doctype}` comp
where
- ifnull(parent_location, "")="{parent}"
+ ifnull(parent_location, "")={parent}
""".format(
- doctype=frappe.db.escape(doctype),
- parent=frappe.db.escape(parent)
- ), as_dict=1)
-
+ doctype=doctype,
+ parent=frappe.db.escape(parent)
+ ), as_dict=1)
@frappe.whitelist()
def add_node():
diff --git a/erpnext/buying/doctype/purchase_order/purchase_order.py b/erpnext/buying/doctype/purchase_order/purchase_order.py
index ed761ce..33f70cb 100644
--- a/erpnext/buying/doctype/purchase_order/purchase_order.py
+++ b/erpnext/buying/doctype/purchase_order/purchase_order.py
@@ -182,7 +182,7 @@
def check_modified_date(self):
mod_db = frappe.db.sql("select modified from `tabPurchase Order` where name = %s",
self.name)
- date_diff = frappe.db.sql("select TIMEDIFF('%s', '%s')" % ( mod_db[0][0],cstr(self.modified)))
+ date_diff = frappe.db.sql("select '%s' - '%s' " % (mod_db[0][0], cstr(self.modified)))
if date_diff and date_diff[0][0]:
msgprint(_("{0} {1} has been modified. Please refresh.").format(self.doctype, self.name),
diff --git a/erpnext/controllers/buying_controller.py b/erpnext/controllers/buying_controller.py
index 3671481..18293ea 100644
--- a/erpnext/controllers/buying_controller.py
+++ b/erpnext/controllers/buying_controller.py
@@ -746,7 +746,7 @@
if not items:
return
- item_list = ", ".join(["'%s'" % frappe.db.escape(d) for d in items])
+ item_list = ", ".join(["%s" % frappe.db.escape(d) for d in items])
invalid_items = [d[0] for d in frappe.db.sql("""
select item_code from tabItem where name in ({0}) and {1}=0
diff --git a/erpnext/controllers/item_variant.py b/erpnext/controllers/item_variant.py
index 24726ad..85fbe0a 100644
--- a/erpnext/controllers/item_variant.py
+++ b/erpnext/controllers/item_variant.py
@@ -119,7 +119,7 @@
return frappe.flags.attribute_values, frappe.flags.numeric_values
def find_variant(template, args, variant_item_code=None):
- conditions = ["""(iv_attribute.attribute="{0}" and iv_attribute.attribute_value="{1}")"""\
+ conditions = ["""(iv_attribute.attribute={0} and iv_attribute.attribute_value={1})"""\
.format(frappe.db.escape(key), frappe.db.escape(cstr(value))) for key, value in args.items()]
conditions = " or ".join(conditions)
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 4c16323..4c4bf78 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -208,9 +208,9 @@
limit %(start)s, %(page_len)s """.format(
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype),
- key=frappe.db.escape(searchfield)),
+ key=searchfield),
{
- 'txt': "%%%s%%" % frappe.db.escape(txt),
+ 'txt': frappe.db.escape('%' + txt + '%'),
'_txt': txt.replace("%", ""),
'start': start or 0,
'page_len': page_len or 20
@@ -353,7 +353,7 @@
{condition} {match_condition}
order by idx desc, name"""
.format(condition=condition, match_condition=get_match_cond(doctype), key=searchfield), {
- 'txt': "%%%s%%" % frappe.db.escape(txt),
+ 'txt': frappe.db.escape('%' + txt + '%'),
'company': filters.get("company", "")
})
@@ -375,10 +375,10 @@
and tabAccount.docstatus!=2
and tabAccount.{key} LIKE %(txt)s
{condition} {match_condition}"""
- .format(condition=condition, key=frappe.db.escape(searchfield),
+ .format(condition=condition, key=searchfield,
match_condition=get_match_cond(doctype)), {
'company': filters.get("company", ""),
- 'txt': "%%%s%%" % frappe.db.escape(txt)
+ 'txt': frappe.db.escape('%' + txt + '%')
})
@@ -406,7 +406,7 @@
{start}, {page_len}
""".format(
sub_query=sub_query,
- key=frappe.db.escape(searchfield),
+ key=searchfield,
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions),
mcond=get_match_cond(doctype),
start=start,
@@ -430,9 +430,9 @@
query = """select batch_id from `tabBatch`
where disabled = 0
and (expiry_date >= CURDATE() or expiry_date IS NULL)
- and name like '{txt}'""".format(txt = frappe.db.escape('%{0}%'.format(txt)))
+ and name like {txt}""".format(txt = frappe.db.escape('%{0}%'.format(txt)))
if filters and filters.get('item'):
- query += " and item = '{item}'".format(item = frappe.db.escape(filters.get('item')))
+ query += " and item = {item}".format(item = frappe.db.escape(filters.get('item')))
return frappe.db.sql(query, filters)
diff --git a/erpnext/controllers/status_updater.py b/erpnext/controllers/status_updater.py
index d94564e..d239255 100644
--- a/erpnext/controllers/status_updater.py
+++ b/erpnext/controllers/status_updater.py
@@ -308,7 +308,7 @@
def _update_modified(self, args, update_modified):
args['update_modified'] = ''
if update_modified:
- args['update_modified'] = ', modified = now(), modified_by = "{0}"'\
+ args['update_modified'] = ', modified = now(), modified_by = {0}'\
.format(frappe.db.escape(frappe.session.user))
def update_billing_status_for_zero_amount_refdoc(self, ref_dt):
diff --git a/erpnext/education/doctype/fee_schedule/fee_schedule.py b/erpnext/education/doctype/fee_schedule/fee_schedule.py
index b6df8c5..a42800a 100644
--- a/erpnext/education/doctype/fee_schedule/fee_schedule.py
+++ b/erpnext/education/doctype/fee_schedule/fee_schedule.py
@@ -117,14 +117,14 @@
def get_students(student_group, academic_year, academic_term=None, student_category=None):
conditions = ""
if student_category:
- conditions = " and pe.student_category='{}'".format(frappe.db.escape(student_category))
+ conditions = " and pe.student_category={}".format(frappe.db.escape(student_category))
if academic_term:
- conditions = " and pe.academic_term='{}'".format(frappe.db.escape(academic_term))
+ conditions = " and pe.academic_term={}".format(frappe.db.escape(academic_term))
students = frappe.db.sql("""
select pe.student, pe.student_name, pe.program, pe.student_batch_name
from `tabStudent Group Student` sgs, `tabProgram Enrollment` pe
- where
+ where
pe.student = sgs.student and pe.academic_year = %s
and sgs.parent = %s and sgs.active = 1
{conditions}
diff --git a/erpnext/hotels/doctype/hotel_room_reservation/hotel_room_reservation.py b/erpnext/hotels/doctype/hotel_room_reservation/hotel_room_reservation.py
index 9a81405..6f9e4a9 100644
--- a/erpnext/hotels/doctype/hotel_room_reservation/hotel_room_reservation.py
+++ b/erpnext/hotels/doctype/hotel_room_reservation/hotel_room_reservation.py
@@ -59,15 +59,15 @@
if not d.item:
continue
day_rate = frappe.db.sql("""
- select
- item.rate
- from
+ select
+ item.rate
+ from
`tabHotel Room Pricing Item` item,
`tabHotel Room Pricing` pricing
where
item.parent = pricing.name
and item.item = %s
- and %s between pricing.from_date
+ and %s between pricing.from_date
and pricing.to_date""", (d.item, day))
if day_rate:
@@ -90,7 +90,7 @@
def get_rooms_booked(room_type, day, exclude_reservation=None):
exclude_condition = ''
if exclude_reservation:
- exclude_condition = 'and reservation.name != "{0}"'.format(frappe.db.escape(exclude_reservation))
+ exclude_condition = 'and reservation.name != {0}'.format(frappe.db.escape(exclude_reservation))
return frappe.db.sql("""
select sum(item.qty)
@@ -105,5 +105,5 @@
and reservation.docstatus = 1
{exclude_condition}
and %s between reservation.from_date
- and reservation.to_date""".format(exclude_condition=exclude_condition),
+ and reservation.to_date""".format(exclude_condition=exclude_condition),
(room_type, day))[0][0] or 0
diff --git a/erpnext/hr/doctype/expense_claim/expense_claim.py b/erpnext/hr/doctype/expense_claim/expense_claim.py
index 5cf9570..ca092ad 100644
--- a/erpnext/hr/doctype/expense_claim/expense_claim.py
+++ b/erpnext/hr/doctype/expense_claim/expense_claim.py
@@ -18,7 +18,7 @@
class ExpenseClaim(AccountsController):
def onload(self):
- self.get("__onload").make_payment_via_journal_entry = frappe.db.get_single_value('Accounts Settings',
+ self.get("__onload").make_payment_via_journal_entry = frappe.db.get_single_value('Accounts Settings',
'make_payment_via_journal_entry')
def validate(self):
@@ -103,7 +103,7 @@
self.validate_account_details()
payable_amount = flt(self.total_sanctioned_amount) - flt(self.total_advance_amount)
-
+
# payable entry
if payable_amount:
gl_entry.append(
@@ -232,7 +232,7 @@
expense.default_account = get_expense_claim_account(expense.expense_type, self.company)["account"]
def update_reimbursed_amount(doc):
- amt = frappe.db.sql("""select ifnull(sum(debit_in_account_currency), 0) as amt
+ amt = frappe.db.sql("""select ifnull(sum(debit_in_account_currency), 0) as amt
from `tabGL Entry` where against_voucher_type = 'Expense Claim' and against_voucher = %s
and party = %s """, (doc.name, doc.employee) ,as_dict=1)[0].amt
@@ -287,7 +287,7 @@
if not account:
frappe.throw(_("Please set default account in Expense Claim Type {0}")
.format(expense_claim_type))
-
+
return {
"account": account
}
@@ -295,14 +295,14 @@
@frappe.whitelist()
def get_advances(employee, advance_id=None):
if not advance_id:
- condition = 'docstatus=1 and employee="{0}" and paid_amount > 0 and paid_amount > claimed_amount'.format(frappe.db.escape(employee))
+ condition = 'docstatus=1 and employee={0} and paid_amount > 0 and paid_amount > claimed_amount'.format(frappe.db.escape(employee))
else:
- condition = 'name="{0}"'.format(frappe.db.escape(advance_id))
+ condition = 'name={0}'.format(frappe.db.escape(advance_id))
return frappe.db.sql("""
- select
+ select
name, posting_date, paid_amount, claimed_amount, advance_account
- from
+ from
`tabEmployee Advance`
where {0}
""".format(condition), as_dict=1)
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 12f2f04..502541e 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -103,7 +103,7 @@
item_condition = ""
if self.item_code:
- item_condition = ' and so_item.item_code = "{0}"'.format(frappe.db.escape(self.item_code))
+ item_condition = ' and so_item.item_code = {0}'.format(frappe.db.escape(self.item_code))
items = frappe.db.sql("""select distinct parent, item_code, warehouse,
(qty - work_order_qty) * conversion_factor as pending_qty, name
@@ -114,7 +114,7 @@
(", ".join(["%s"] * len(so_list)), item_condition), tuple(so_list), as_dict=1)
if self.item_code:
- item_condition = ' and so_item.item_code = "{0}"'.format(frappe.db.escape(self.item_code))
+ item_condition = ' and so_item.item_code = {0}'.format(frappe.db.escape(self.item_code))
packed_items = frappe.db.sql("""select distinct pi.parent, pi.item_code, pi.warehouse as warehouse,
(((so_item.qty - so_item.work_order_qty) * pi.qty) / so_item.qty)
@@ -138,7 +138,7 @@
item_condition = ""
if self.item_code:
- item_condition = " and mr_item.item_code ='{0}'".format(frappe.db.escape(self.item_code))
+ item_condition = " and mr_item.item_code ={0}".format(frappe.db.escape(self.item_code))
items = frappe.db.sql("""select distinct parent, name, item_code, warehouse,
(qty - ordered_qty) as pending_qty
@@ -512,7 +512,7 @@
conditions = ""
warehouse = row.source_warehouse or row.default_warehouse or row.warehouse
if warehouse:
- conditions = " and warehouse='{0}'".format(frappe.db.escape(warehouse))
+ conditions = " and warehouse={0}".format(frappe.db.escape(warehouse))
item_projected_qty = frappe.db.sql(""" select ifnull(sum(projected_qty),0) as projected_qty,
ifnull(sum(actual_qty),0) as actual_qty from `tabBin`
diff --git a/erpnext/manufacturing/doctype/production_planning_tool/production_planning_tool.py b/erpnext/manufacturing/doctype/production_planning_tool/production_planning_tool.py
index 323aaf9..dbc6552 100644
--- a/erpnext/manufacturing/doctype/production_planning_tool/production_planning_tool.py
+++ b/erpnext/manufacturing/doctype/production_planning_tool/production_planning_tool.py
@@ -127,7 +127,7 @@
item_condition = ""
if self.fg_item:
- item_condition = ' and so_item.item_code = "{0}"'.format(frappe.db.escape(self.fg_item))
+ item_condition = ' and so_item.item_code = {0}'.format(frappe.db.escape(self.fg_item))
items = frappe.db.sql("""select distinct parent, item_code, warehouse,
(qty - delivered_qty)*conversion_factor as pending_qty
@@ -138,7 +138,7 @@
(", ".join(["%s"] * len(so_list)), item_condition), tuple(so_list), as_dict=1)
if self.fg_item:
- item_condition = ' and pi.item_code = "{0}"'.format(frappe.db.escape(self.fg_item))
+ item_condition = ' and pi.item_code = {0}'.format(frappe.db.escape(self.fg_item))
packed_items = frappe.db.sql("""select distinct pi.parent, pi.item_code, pi.warehouse as warehouse,
(((so_item.qty - so_item.delivered_qty) * pi.qty) / so_item.qty)
@@ -161,7 +161,7 @@
item_condition = ""
if self.fg_item:
- item_condition = ' and mr_item.item_code = "' + frappe.db.escape(self.fg_item, percent=False) + '"'
+ item_condition = ' and mr_item.item_code =' + frappe.db.escape(self.fg_item, percent=False)
items = frappe.db.sql("""select distinct parent, name, item_code, warehouse,
(qty - ordered_qty) as pending_qty
@@ -487,7 +487,7 @@
def get_item_projected_qty(self,item):
conditions = ""
if self.purchase_request_for_warehouse:
- conditions = " and warehouse='{0}'".format(frappe.db.escape(self.purchase_request_for_warehouse))
+ conditions = " and warehouse={0}".format(frappe.db.escape(self.purchase_request_for_warehouse))
item_projected_qty = frappe.db.sql("""
select ifnull(sum(projected_qty),0) as qty
diff --git a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
index 2d3d078..b6f7d01 100644
--- a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
+++ b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
@@ -62,7 +62,7 @@
where wh.lft >= %s and wh.rgt <= %s and ledger.warehouse = wh.name)" % (warehouse_details.lft,
warehouse_details.rgt)
else:
- conditions += " and ledger.warehouse = '%s'" % frappe.db.escape(filters.get("warehouse"))
+ conditions += " and ledger.warehouse = %s" % frappe.db.escape(filters.get("warehouse"))
else:
conditions += ""
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
index 3236839..44db1b5 100644
--- a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
@@ -43,7 +43,7 @@
where wh.lft >= %s and wh.rgt <= %s and ledger.warehouse = wh.name)" % (warehouse_details.lft,
warehouse_details.rgt)
else:
- conditions += " and ledger.warehouse = '%s'" % frappe.db.escape(filters.get("warehouse"))
+ conditions += " and ledger.warehouse = %s" % frappe.db.escape(filters.get("warehouse"))
else:
conditions += ""
diff --git a/erpnext/patches/v10_0/update_territory_and_customer_group.py b/erpnext/patches/v10_0/update_territory_and_customer_group.py
index c02d327..dc99e8c 100644
--- a/erpnext/patches/v10_0/update_territory_and_customer_group.py
+++ b/erpnext/patches/v10_0/update_territory_and_customer_group.py
@@ -15,8 +15,8 @@
value = frappe.db.escape(frappe.as_unicode(customer.get("customer_group")))
when_then.append('''
- WHEN `%s` = "%s" and %s != "%s"
- THEN "%s"
+ WHEN `%s` = %s and %s != %s
+ THEN %s
'''%(d["master_fieldname"], frappe.db.escape(frappe.as_unicode(customer.name)),
d["linked_to_fieldname"], value, value))
diff --git a/erpnext/patches/v11_0/update_total_qty_field.py b/erpnext/patches/v11_0/update_total_qty_field.py
index 5c7663d..749e24f 100644
--- a/erpnext/patches/v11_0/update_total_qty_field.py
+++ b/erpnext/patches/v11_0/update_total_qty_field.py
@@ -9,7 +9,7 @@
frappe.reload_doc('stock', 'doctype', 'purchase_receipt')
frappe.reload_doc('accounts', 'doctype', 'sales_invoice')
frappe.reload_doc('accounts', 'doctype', 'purchase_invoice')
-
+
doctypes = ["Sales Order", "Sales Invoice", "Delivery Note",\
"Purchase Order", "Purchase Invoice", "Purchase Receipt", "Quotation", "Supplier Quotation"]
@@ -25,7 +25,7 @@
when_then = []
for d in total_qty:
when_then.append("""
- when dt.name = '{0}' then {1}
+ when dt.name = {0} then {1}
""".format(frappe.db.escape(d.get("parent")), d.get("qty")))
if when_then:
diff --git a/erpnext/patches/v8_0/create_domain_docs.py b/erpnext/patches/v8_0/create_domain_docs.py
index 4710287..3ef4f3c 100644
--- a/erpnext/patches/v8_0/create_domain_docs.py
+++ b/erpnext/patches/v8_0/create_domain_docs.py
@@ -22,7 +22,7 @@
condition = ""
company = erpnext.get_default_company()
if company:
- condition = " and name='{0}'".format(frappe.db.escape(company))
+ condition = " and name={0}".format(frappe.db.escape(company))
domains = frappe.db.sql_list("select distinct domain from `tabCompany` where domain != 'Other' {0}".format(condition))
diff --git a/erpnext/patches/v8_0/set_sales_invoice_serial_number_from_delivery_note.py b/erpnext/patches/v8_0/set_sales_invoice_serial_number_from_delivery_note.py
index 5dedc81..8a4ef40 100644
--- a/erpnext/patches/v8_0/set_sales_invoice_serial_number_from_delivery_note.py
+++ b/erpnext/patches/v8_0/set_sales_invoice_serial_number_from_delivery_note.py
@@ -10,9 +10,9 @@
frappe.reload_doc("stock", "doctype", "serial_no")
- frappe.db.sql(""" update `tabSales Invoice Item` sii inner join
+ frappe.db.sql(""" update `tabSales Invoice Item` sii inner join
`tabDelivery Note Item` dni on sii.dn_detail=dni.name and sii.qty=dni.qty
- set sii.serial_no=dni.serial_no where sii.parent IN (select si.name
+ set sii.serial_no=dni.serial_no where sii.parent IN (select si.name
from `tabSales Invoice` si where si.update_stock=0 and si.docstatus=1)""")
items = frappe.db.sql(""" select sii.parent, sii.serial_no from `tabSales Invoice Item` sii
@@ -26,13 +26,13 @@
if not sales_invoice or not serial_nos:
continue
- serial_nos = ["'%s'"%frappe.db.escape(no) for no in serial_nos.split("\n")]
+ serial_nos = ["{}".format(frappe.db.escape(no)) for no in serial_nos.split("\n")]
frappe.db.sql("""
- UPDATE
+ UPDATE
`tabSerial No`
- SET
- sales_invoice='{sales_invoice}'
+ SET
+ sales_invoice={sales_invoice}
WHERE
name in ({serial_nos})
""".format(
diff --git a/erpnext/patches/v8_10/change_default_customer_credit_days.py b/erpnext/patches/v8_10/change_default_customer_credit_days.py
index eddafb5..992be17 100644
--- a/erpnext/patches/v8_10/change_default_customer_credit_days.py
+++ b/erpnext/patches/v8_10/change_default_customer_credit_days.py
@@ -35,7 +35,7 @@
else:
template = frappe.get_doc("Payment Terms Template", pyt_template_name)
- payment_terms.append('WHEN `name`="%s" THEN "%s"' % (frappe.db.escape(party_name), template.template_name))
+ payment_terms.append('WHEN `name`={0} THEN {1}'.format(frappe.db.escape(party_name), template.template_name))
records.append(frappe.db.escape(party_name))
begin_query_str = "UPDATE `tab{0}` SET `payment_terms` = CASE ".format(doctype)
diff --git a/erpnext/projects/doctype/task/task.py b/erpnext/projects/doctype/task/task.py
index 3dc52d4..ebe38af 100755
--- a/erpnext/projects/doctype/task/task.py
+++ b/erpnext/projects/doctype/task/task.py
@@ -167,7 +167,8 @@
%(mcond)s
order by name
limit %(start)s, %(page_len)s """ % {'key': searchfield,
- 'txt': "%%%s%%" % frappe.db.escape(txt), 'mcond':get_match_cond(doctype),
+ 'txt': frappe.db.escape('%' + txt + '%'),
+ 'mcond':get_match_cond(doctype),
'start': start, 'page_len': page_len})
diff --git a/erpnext/projects/doctype/timesheet/timesheet.py b/erpnext/projects/doctype/timesheet/timesheet.py
index f48c0c6..c6f405d 100644
--- a/erpnext/projects/doctype/timesheet/timesheet.py
+++ b/erpnext/projects/doctype/timesheet/timesheet.py
@@ -223,7 +223,7 @@
and tsd.parent LIKE %(txt)s {condition}
order by tsd.parent limit %(start)s, %(page_len)s"""
.format(condition=condition), {
- "txt": "%%%s%%" % frappe.db.escape(txt),
+ 'txt': frappe.db.escape('%' + txt + '%'),
"start": start, "page_len": page_len, 'project': filters.get("project")
})
diff --git a/erpnext/projects/utils.py b/erpnext/projects/utils.py
index d663ad0..d0d88eb 100644
--- a/erpnext/projects/utils.py
+++ b/erpnext/projects/utils.py
@@ -23,6 +23,6 @@
`%s`,
subject
limit %s, %s""" %
- (frappe.db.escape(searchfield), "%s", "%s", match_conditions, "%s",
- frappe.db.escape(searchfield), "%s", frappe.db.escape(searchfield), "%s", "%s"),
+ (searchfield, "%s", "%s", match_conditions, "%s",
+ searchfield, "%s", searchfield, "%s", "%s"),
(search_string, search_string, order_by_string, order_by_string, start, page_len))
diff --git a/erpnext/regional/report/gstr_1/gstr_1.py b/erpnext/regional/report/gstr_1/gstr_1.py
index fa2d2af..c6f203b 100644
--- a/erpnext/regional/report/gstr_1/gstr_1.py
+++ b/erpnext/regional/report/gstr_1/gstr_1.py
@@ -117,7 +117,7 @@
if self.filters.get("type_of_business") == "B2B":
conditions += """ and ifnull(invoice_type, '') != 'Export' and is_return != 1
- and customer in ('{0}')""".format("', '".join([frappe.db.escape(c.name) for c in customers]))
+ and customer in ({0})""".format(", ".join([frappe.db.escape(c.name) for c in customers]))
if self.filters.get("type_of_business") in ("B2C Large", "B2C Small"):
b2c_limit = frappe.db.get_single_value('GSt Settings', 'b2c_limit')
@@ -126,13 +126,13 @@
if self.filters.get("type_of_business") == "B2C Large":
conditions += """ and SUBSTR(place_of_supply, 1, 2) != SUBSTR(company_gstin, 1, 2)
- and grand_total > {0} and is_return != 1 and customer in ('{1}')""".\
- format(flt(b2c_limit), "', '".join([frappe.db.escape(c.name) for c in customers]))
+ and grand_total > {0} and is_return != 1 and customer in ({1})""".\
+ format(flt(b2c_limit), ", ".join([frappe.db.escape(c.name) for c in customers]))
elif self.filters.get("type_of_business") == "B2C Small":
conditions += """ and (
SUBSTR(place_of_supply, 1, 2) = SUBSTR(company_gstin, 1, 2)
- or grand_total <= {0}) and is_return != 1 and customer in ('{1}')""".\
- format(flt(b2c_limit), "', '".join([frappe.db.escape(c.name) for c in customers]))
+ or grand_total <= {0}) and is_return != 1 and customer in ({1})""".\
+ format(flt(b2c_limit), ", ".join([frappe.db.escape(c.name) for c in customers]))
elif self.filters.get("type_of_business") == "CDNR":
conditions += """ and is_return = 1 """
diff --git a/erpnext/selling/page/point_of_sale/point_of_sale.py b/erpnext/selling/page/point_of_sale/point_of_sale.py
index ed28204..00e4de1 100644
--- a/erpnext/selling/page/point_of_sale/point_of_sale.py
+++ b/erpnext/selling/page/point_of_sale/point_of_sale.py
@@ -126,7 +126,7 @@
condition = """(i.name like %(item_code)s
or i.item_name like %(item_code)s)"""
- return '%%%s%%'%(frappe.db.escape(item_code)), condition
+ return frappe.db.escape('%' + item_code + '%'), condition
def get_item_group_condition(pos_profile):
cond = "and 1=1"
diff --git a/erpnext/setup/doctype/authorization_control/authorization_control.py b/erpnext/setup/doctype/authorization_control/authorization_control.py
index b419850..7db703f 100644
--- a/erpnext/setup/doctype/authorization_control/authorization_control.py
+++ b/erpnext/setup/doctype/authorization_control/authorization_control.py
@@ -40,7 +40,7 @@
chk = 1
add_cond1,add_cond2 = '',''
if based_on == 'Itemwise Discount':
- add_cond1 += " and master_name = '"+frappe.db.escape(cstr(item))+"'"
+ add_cond1 += " and master_name = " + frappe.db.escape(cstr(item))
itemwise_exists = frappe.db.sql("""select value from `tabAuthorization Rule`
where transaction = %s and value <= %s
and based_on = %s and company = %s and docstatus != 2 %s %s""" %
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index 0c58fb2..762f729 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -388,17 +388,19 @@
current_month_year = formatdate(today(), "MM-yyyy")
results = frappe.db.sql('''
- select
- sum(base_grand_total) as total, date_format(posting_date, '%m-%Y') as month_year
- from
+ SELECT
+ SUM(base_grand_total) AS total,
+ DATE_FORMAT(`posting_date`, '%m-%Y') AS month_year
+ FROM
`tabSales Invoice`
- where
- date_format(posting_date, '%m-%Y')="{0}"
- and docstatus = 1
- and company = "{1}"
- group by
+ WHERE
+ DATE_FORMAT(`posting_date`, '%m-%Y') = '{current_month_year}'
+ AND docstatus = 1
+ AND company = {company}
+ GROUP BY
month_year
- '''.format(current_month_year, frappe.db.escape(company)), as_dict = True)
+ '''.format(current_month_year=current_month_year, company=frappe.db.escape(company)),
+ as_dict = True)
monthly_total = results[0]['total'] if len(results) > 0 else 0
@@ -408,7 +410,7 @@
'''Cache past year monthly sales of every company based on sales invoices'''
from frappe.utils.goal import get_monthly_results
import json
- filter_str = "company = '{0}' and status != 'Draft' and docstatus=1".format(frappe.db.escape(company))
+ filter_str = "company = {0} and status != 'Draft' and docstatus=1".format(frappe.db.escape(company))
month_to_value_dict = get_monthly_results("Sales Invoice", "base_grand_total",
"posting_date", filter_str, "sum")
@@ -440,9 +442,9 @@
from
`tab{doctype}` comp
where
- ifnull(parent_company, "")="{parent}"
+ ifnull(parent_company, "")={parent}
""".format(
- doctype = frappe.db.escape(doctype),
+ doctype = doctype,
parent=frappe.db.escape(parent)
), as_dict=1)
diff --git a/erpnext/setup/doctype/company/delete_company_transactions.py b/erpnext/setup/doctype/company/delete_company_transactions.py
index 0ffe2c7..637e655 100644
--- a/erpnext/setup/doctype/company/delete_company_transactions.py
+++ b/erpnext/setup/doctype/company/delete_company_transactions.py
@@ -89,18 +89,18 @@
leads = [ "'%s'"%row.get("name") for row in leads ]
addresses = []
if leads:
- addresses = frappe.db.sql_list("""select parent from `tabDynamic Link` where link_name
+ addresses = frappe.db.sql_list("""select parent from `tabDynamic Link` where link_name
in ({leads})""".format(leads=",".join(leads)))
if addresses:
- addresses = ["'%s'"%frappe.db.escape(addr) for addr in addresses]
+ addresses = ["%s" % frappe.db.escape(addr) for addr in addresses]
- frappe.db.sql("""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
+ frappe.db.sql("""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(addresses=",".join(addresses)))
- frappe.db.sql("""delete from `tabDynamic Link` where link_doctype='Lead'
+ frappe.db.sql("""delete from `tabDynamic Link` where link_doctype='Lead'
and parenttype='Address' and link_name in ({leads})""".format(leads=",".join(leads)))
frappe.db.sql("""update tabCustomer set lead_name=NULL where lead_name in ({leads})""".format(leads=",".join(leads)))
diff --git a/erpnext/setup/doctype/item_group/item_group.py b/erpnext/setup/doctype/item_group/item_group.py
index 6bc9036..48963c2 100644
--- a/erpnext/setup/doctype/item_group/item_group.py
+++ b/erpnext/setup/doctype/item_group/item_group.py
@@ -88,7 +88,7 @@
# return child item groups if the type is of "Is Group"
return get_child_groups_for_list_in_html(item_group, start, limit, search)
- child_groups = ", ".join(['"' + frappe.db.escape(i[0]) + '"' for i in get_child_groups(product_group)])
+ child_groups = ", ".join([frappe.db.escape(i[0]) for i in get_child_groups(product_group)])
# base query
query = """select I.name, I.item_name, I.item_code, I.route, I.image, I.website_image, I.thumbnail, I.item_group,
diff --git a/erpnext/setup/doctype/party_type/party_type.py b/erpnext/setup/doctype/party_type/party_type.py
index 8baddf4..fd044bc 100644
--- a/erpnext/setup/doctype/party_type/party_type.py
+++ b/erpnext/setup/doctype/party_type/party_type.py
@@ -20,6 +20,6 @@
where `{key}` LIKE %(txt)s {cond}
order by name limit %(start)s, %(page_len)s"""
.format(key=searchfield, cond=cond), {
- 'txt': "%%%s%%" % frappe.db.escape(txt),
+ 'txt': frappe.db.escape('%' + txt + '%'),
'start': start, 'page_len': page_len
})
diff --git a/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py b/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
index be4670e..3098190 100644
--- a/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
+++ b/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
@@ -28,9 +28,10 @@
raise_exception=ShoppingCartSetupError)
price_list_currency_map = frappe.db.get_values("Price List",
- [self.price_list],
- "currency")
+ [self.price_list], "currency")
+ price_list_currency_map = dict(price_list_currency_map)
+
# check if all price lists have a currency
for price_list, currency in price_list_currency_map.items():
if not currency:
@@ -39,8 +40,8 @@
expected_to_exist = [currency + "-" + company_currency
for currency in price_list_currency_map.values()
if currency != company_currency]
-
- # manqala 20/09/2016: set up selection parameters for query from tabCurrency Exchange
+
+ # manqala 20/09/2016: set up selection parameters for query from tabCurrency Exchange
from_currency = [currency for currency in price_list_currency_map.values() if currency != company_currency]
to_currency = company_currency
# manqala end
diff --git a/erpnext/startup/boot.py b/erpnext/startup/boot.py
index 62c9e7b..473fba5 100644
--- a/erpnext/startup/boot.py
+++ b/erpnext/startup/boot.py
@@ -26,11 +26,12 @@
'default_valid_till'))
# if no company, show a dialog box to create a new company
- bootinfo.customer_count = frappe.db.sql("""select count(*) from tabCustomer""")[0][0]
+ bootinfo.customer_count = frappe.db.sql("""SELECT count(*) FROM `tabCustomer`""")[0][0]
if not bootinfo.customer_count:
- bootinfo.setup_complete = frappe.db.sql("""select name from
- tabCompany limit 1""") and 'Yes' or 'No'
+ bootinfo.setup_complete = frappe.db.sql("""SELECT `name`
+ FROM `tabCompany`
+ LIMIT 1""") and 'Yes' or 'No'
bootinfo.docs += frappe.db.sql("""select name, default_currency, cost_center, default_terms,
default_letter_head, default_bank_account, enable_perpetual_inventory from `tabCompany`""",
diff --git a/erpnext/stock/doctype/item/item.py b/erpnext/stock/doctype/item/item.py
index 64a66cf..24e8f5a 100644
--- a/erpnext/stock/doctype/item/item.py
+++ b/erpnext/stock/doctype/item/item.py
@@ -961,7 +961,7 @@
value = ""
uom_details = frappe.db.sql("""select to_uom, from_uom, value from `tabUOM Conversion Factor`\
where to_uom in ({0})
- """.format(', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in uoms])), as_dict=True)
+ """.format(', '.join([frappe.db.escape(i, percent=False) for i in uoms])), as_dict=True)
for d in uom_details:
if d.from_uom == stock_uom and d.to_uom == uom:
diff --git a/erpnext/stock/doctype/item_alternative/item_alternative.py b/erpnext/stock/doctype/item_alternative/item_alternative.py
index 6f9c5de..93ec5fd 100644
--- a/erpnext/stock/doctype/item_alternative/item_alternative.py
+++ b/erpnext/stock/doctype/item_alternative/item_alternative.py
@@ -36,5 +36,5 @@
and two_way = 1) limit {0}, {1}
""".format(start, page_len), {
"item_code": frappe.db.escape(filters.get('item_code')),
- "txt": "%%%s%%" % frappe.db.escape(txt)
+ "txt": frappe.db.escape('%' + txt + '%')
})
\ No newline at end of file
diff --git a/erpnext/stock/doctype/serial_no/serial_no.py b/erpnext/stock/doctype/serial_no/serial_no.py
index 872daba..2559b7a 100644
--- a/erpnext/stock/doctype/serial_no/serial_no.py
+++ b/erpnext/stock/doctype/serial_no/serial_no.py
@@ -171,7 +171,7 @@
where fieldname='serial_no' and fieldtype in ('Text', 'Small Text')"""):
for item in frappe.db.sql("""select name, serial_no from `tab%s`
- where serial_no like '%%%s%%'""" % (dt[0], frappe.db.escape(old))):
+ where serial_no like %s""" % (dt[0], frappe.db.escape('%' + old + '%'))):
serial_nos = map(lambda i: new if i.upper()==old.upper() else i, item[1].split('\n'))
frappe.db.sql("""update `tab%s` set serial_no = %s
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 3c60896..66a294d 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -124,11 +124,11 @@
is_group_warehouse(self.warehouse)
def on_doctype_update():
- if not frappe.db.sql("""show index from `tabStock Ledger Entry`
- where Key_name="posting_sort_index" """):
+ if not frappe.db.has_index('tabStock Ledger Entry', 'posting_sort_index'):
frappe.db.commit()
- frappe.db.sql("""alter table `tabStock Ledger Entry`
- add index posting_sort_index(posting_date, posting_time, name)""")
+ frappe.db.add_index("Stock Ledger Entry",
+ fields=["posting_date", "posting_time", "name"],
+ index_name="posting_sort_index")
frappe.db.add_index("Stock Ledger Entry", ["voucher_no", "voucher_type"])
frappe.db.add_index("Stock Ledger Entry", ["batch_no", "item_code", "warehouse"])
diff --git a/erpnext/stock/report/item_variant_details/item_variant_details.py b/erpnext/stock/report/item_variant_details/item_variant_details.py
index c7ca388..e8449cc 100644
--- a/erpnext/stock/report/item_variant_details/item_variant_details.py
+++ b/erpnext/stock/report/item_variant_details/item_variant_details.py
@@ -22,7 +22,7 @@
frappe.msgprint(_("There isn't any item variant for the selected item"))
return []
else:
- variants = ",".join(['"' + frappe.db.escape(variant['name']) + '"' for variant in variant_results])
+ variants = ", ".join([frappe.db.escape(variant['name']) for variant in variant_results])
order_count_map = get_open_sales_orders_map(variants)
stock_details_map = get_stock_details_map(variants)
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index e6ca5c2..b25a42a 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -94,7 +94,7 @@
frappe.throw(_("'From Date' is required"))
if filters.get("to_date"):
- conditions += " and sle.posting_date <= '%s'" % frappe.db.escape(filters.get("to_date"))
+ conditions += " and sle.posting_date <= %s" % frappe.db.escape(filters.get("to_date"))
else:
frappe.throw(_("'To Date' is required"))
@@ -112,7 +112,7 @@
item_conditions_sql = ''
if items:
item_conditions_sql = ' and sle.item_code in ({})'\
- .format(', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items]))
+ .format(', '.join([frappe.db.escape(i, percent=False) for i in items]))
conditions = get_conditions(filters)
@@ -166,15 +166,15 @@
qty_dict.val_rate = d.valuation_rate
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
-
+
iwb_map = filter_items_with_no_transactions(iwb_map)
return iwb_map
-
+
def filter_items_with_no_transactions(iwb_map):
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
-
+
no_transactions = True
float_precision = cint(frappe.db.get_default("float_precision")) or 3
for key, val in iteritems(qty_dict):
@@ -182,7 +182,7 @@
qty_dict[key] = val
if key != "val_rate" and val:
no_transactions = False
-
+
if no_transactions:
iwb_map.pop((company, item, warehouse))
@@ -211,10 +211,10 @@
if items:
for item in frappe.db.sql("""
- select name, item_name, description, item_group, brand, stock_uom
- from `tabItem`
- where name in ({0}) and ifnull(disabled, 0) = 0
- """.format(', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])), as_dict=1):
+ SELECT `name`, `item_name`, `description`, `item_group`, `brand`, `stock_uom`
+ FROM `tabItem`
+ WHERE `name` IN ({0}) AND ifnull(`disabled`, 0) = 0
+ """.format(', '.join([frappe.db.escape(i, percent=False) for i in items])), as_dict=1):
item_details.setdefault(item.name, item)
if filters.get('show_variant_attributes', 0) == 1:
@@ -231,7 +231,7 @@
select parent, warehouse, warehouse_reorder_qty, warehouse_reorder_level
from `tabItem Reorder`
where parent in ({0})
- """.format(', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])), as_dict=1)
+ """.format(', '.join([frappe.db.escape(i, percent=False) for i in items])), as_dict=1)
return dict((d.parent + d.warehouse, d) for d in item_reorder_details)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 9237cfd..dc3cb28 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -56,7 +56,7 @@
item_conditions_sql = ''
if items:
item_conditions_sql = 'and sle.item_code in ({})'\
- .format(', '.join(['"' + frappe.db.escape(i) + '"' for i in items]))
+ .format(', '.join([frappe.db.escape(i) for i in items]))
return frappe.db.sql("""select concat_ws(" ", posting_date, posting_time) as date,
item_code, warehouse, actual_qty, qty_after_transaction, incoming_rate, valuation_rate,
@@ -100,7 +100,7 @@
select name, item_name, description, item_group, brand, stock_uom
from `tabItem`
where name in ({0})
- """.format(', '.join(['"' + frappe.db.escape(i,percent=False) + '"' for i in items])), as_dict=1):
+ """.format(', '.join([frappe.db.escape(i,percent=False) for i in items])), as_dict=1):
item_details.setdefault(item.name, item)
return item_details
diff --git a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
index 3e6e5a5..885ab78 100644
--- a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
+++ b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
@@ -64,10 +64,10 @@
def get_bin_list(filters):
conditions = []
-
+
if filters.item_code:
conditions.append("item_code = '%s' "%filters.item_code)
-
+
if filters.warehouse:
warehouse_details = frappe.db.get_value("Warehouse", filters.warehouse, ["lft", "rgt"], as_dict=1)
@@ -88,7 +88,7 @@
condition = ""
if item_code:
- condition = 'and item_code = "{0}"'.format(frappe.db.escape(item_code, percent=False))
+ condition = 'and item_code = {0}'.format(frappe.db.escape(item_code, percent=False))
items = frappe.db.sql("""select * from `tabItem` item
where is_stock_item = 1
@@ -100,7 +100,7 @@
condition = ""
if item_code:
- condition = 'where parent="{0}"'.format(frappe.db.escape(item_code, percent=False))
+ condition = 'where parent={0}'.format(frappe.db.escape(item_code, percent=False))
reorder_levels = frappe._dict()
for ir in frappe.db.sql("""select * from `tabItem Reorder` {condition}""".format(condition=condition), as_dict=1):
diff --git a/erpnext/stock/report/total_stock_summary/total_stock_summary.py b/erpnext/stock/report/total_stock_summary/total_stock_summary.py
index fafc169..b25e096 100644
--- a/erpnext/stock/report/total_stock_summary/total_stock_summary.py
+++ b/erpnext/stock/report/total_stock_summary/total_stock_summary.py
@@ -30,8 +30,8 @@
if filters.get("group_by") == "Warehouse":
if filters.get("company"):
- conditions += " AND warehouse.company = '%s'" % frappe.db.escape(filters.get("company"), percent=False)
-
+ conditions += " AND warehouse.company = %s" % frappe.db.escape(filters.get("company"), percent=False)
+
conditions += " GROUP BY ledger.warehouse, item.item_code"
columns += "'' as company, ledger.warehouse"
else:
diff --git a/test_sites/test_site/site_config.json b/test_sites/test_site/site_config.json
index 48b330b..7a4d106 100644
--- a/test_sites/test_site/site_config.json
+++ b/test_sites/test_site/site_config.json
@@ -7,6 +7,7 @@
"mail_password": "test",
"admin_password": "admin",
"run_selenium_tests": 1,
+ "root_password": "travis",
"host_name": "http://localhost:8000",
"install_apps": ["erpnext"]
}
\ No newline at end of file
diff --git a/travis/bench_init.sh b/travis/bench_init.sh
index f96269b..0fb7f3d 100755
--- a/travis/bench_init.sh
+++ b/travis/bench_init.sh
@@ -1,8 +1,8 @@
#!/bin/bash
cd ~/
-curl -I https://github.com/frappe/frappe/tree/$TRAVIS_BRANCH | head -n 1 | cut -d $' ' -f2 | (
+curl -I https://github.com/surajshetty3416/frappe/tree/pg-poc | head -n 1 | cut -d $' ' -f2 | (
read response;
- [ $response == '200' ] && branch=$TRAVIS_BRANCH || branch='develop';
- bench init frappe-bench --frappe-path https://github.com/frappe/frappe.git --frappe-branch $branch --python $(which python)
+ [ $response == '200' ] && branch='pg-poc' || branch='develop';
+ bench init frappe-bench --frappe-path https://github.com/surajshetty3416/frappe.git --frappe-branch $branch --python $(which python)
)