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,