python - 操作错误 : (1054, "Unknown column ' ' 在 'where clause' 中”)

Question

我试图使用 sale.posting_date 和 payment.mode_of_ payment 从数据库中过滤一些数据。 当我尝试使用 payment.mode_of_ payment=Cash 进行过滤时:我收到以下错误:“‘where 子句’中的未知列‘ payment.mode_of_ payment ’”

from __future__ import unicode_literals
import frappe
from frappe import _, msgprint
 from frappe.utils import flt


def execute(filters=None):
 if not filters: filters = {}

 columns = get_columns()
 data = get_entries(filters)
 if data:
    total=get_total(filters)
    paid_amount=get_paid_amount(filters)
    data.append(['Total ','','',total,paid_amount])

return columns, data

def get_columns():

return [_("Sales Invoice") + ":Link/Sales Invoice:140", _("Customer") + ":Link/Customer:140", _(" Mode of Payment") + ":200",
     _("Total Amount") + ":200",
    _("Paid Amount") + ":120"
]

def get_conditions(filters):
 conditions = ""
 if not filters.get("Date"):
    msgprint(_("Please select Date"), raise_exception=1
    )
else:
    conditions += " and sale.posting_date = '%s'" % filters["Date"]
if filters.get("Mode of Payment"): conditions += " and payment.mode_of_payment='%s'" % filters["Mode of Payment"]
# if filters.get("Mode of Payment"): conditions += " and payment.mode_of_payment=%(Mode of Payment)s"

return conditions

def get_entries(filters):
 conditions = get_conditions(filters)
 entries = frappe.db.sql("""select payment.parent,  sale.customer,payment.mode_of_payment,
    sale.grand_total, payment.paid_amount
    from `tabSales Invoice` sale, `tabPayment` payment
    where payment.parent=sale.name  %s
    order by payment.parent DESC""" %
    conditions, filters, as_list=1)

return entries

def get_total(filters):
 conditions = get_conditions(filters)
 date1= filters.get("Date")
 mode= filters.get("Mode of Payment")
 total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale where sale.grand_total=sale.rounded_total %s """ % conditions, filters )
# total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale , `tabPayment` payment  where sale.posting_date= %s """ % date1 )
print total_amount
return total_amount


def get_paid_amount(filters):
 conditions = get_conditions(filters)
 date1= filters.get("Date")
 paid_amount=frappe.db.sql("""SELECT SUM(payment.paid_amount) FROM `tabSales Invoice` sale, `tabPayment` payment where payment.parent=sale.name %s """ % conditions, filters  )
return paid_amount

回溯:

Traceback (innermost last):
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/app.py",  line 49, in application
response = frappe.handler.handle()
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/handler.py", line 66, in handle
execute_cmd(cmd)
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/handler.py", line 89, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/__init__.py", line 531, in call
return fn(*args, **newargs)
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/widgets/query_report.py", line 87, in run
columns, result = frappe.get_attr(method_name)(frappe._dict(filters))
File "/home/adminuser/frappe-bench-hitech/apps/erpnext/erpnext/accounts/report/daily_sales__report/daily_sales__report.py", line 16, in execute
total=get_total(filters)
File "/home/adminuser/frappe-bench-hitech/apps/erpnext/erpnext/accounts/report/daily_sales__report/daily_sales__report.py", line 54, in get_total
total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale where sale.grand_total=sale.rounded_total %s """ % conditions, filters )
File "/home/adminuser/frappe-bench-hitech/apps/frappe/frappe/database.py", line 110, in sql
self._cursor.execute(query, values)
File "/home/adminuser/frappe-bench-hitech/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/home/adminuser/frappe-bench-hitech/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'payment.mode_of_payment' in 'where clause'")

有人可以帮助我吗？

Answer 1

感谢您对我投反对票，我已经找到答案了。

def get_total(filters):
conditions = get_conditions(filters)
mode= filters.get("Mode of Payment")
if mode:
    total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale, `tabPayment` payment where payment.parent=sale.name %s """ % conditions, filters  )
else:
    total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale where sale.grand_total=sale.rounded_total %s """ % conditions, filters  )

return total_amount

def get_paid_amount(filters):
conditions = get_conditions(filters)
paid_amount=frappe.db.sql("""SELECT SUM(payment.paid_amount) FROM `tabSales Invoice` sale, `tabPayment` payment where payment.parent=sale.name %s """ % conditions, filters  )
return paid_amount