lakshmi
1
I was trying to add a total row to my report.I have gone through those related posts here and is quite confused.
from __future__ import unicode_literals
import frappe
from frappe import _, msgprint
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
last_col = len(columns)
data = get_entries(filters)
total=get_total()
paid_amount=get_paid_amount()
item_list = get_entries(filters)
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"]
# conditions += " and payment.mode_of_payment=%(Mode of Payment)s"
# if filters.get("Date"): conditions += " and sale.posting_date=%(Date)s"
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)
return 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():
total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale""" )
return total_amount
def get_paid_amount():
paid_amount=frappe.db.sql("""SELECT SUM(payment.paid_amount) FROM `tabPayment` payment""" )
return paid_amount
Obviously I want the total of sale.grand_total and payment.paid_amount.I still a beginner.Can someone help me?
have you tried to thick total rows option when create a report ?
lakshmi
3
Yes,but it does not gave the totals.
rmehta
4
You have to append your totals row to data
lakshmi
5
Now it shows :
OperationalError: (1054, "Unknown column 'payment.mode_of_payment' in 'where clause'")
Code after modification:
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 Invoice Amount','','',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
rmehta
6
Not sure what is tabPayment
- could be related your custom design.