hello,
i’m making a script report with date input conditions from user (From and To date), but when now after finishing the query… when i open the report the data is fetched, i mean the rows appears but with empty values check picture below. What could be the problem?
and this is my Python file:
from __future__ import unicode_literals
import frappe
from frappe import _
def execute(filters=None):
columns, data = [], []
columns=get_columns()
data=get_data(filters,columns)
return columns, data
def get_columns():
return [
{
"label": _("Name"),
"fieldname": "name",
"fieldtype": "Link",
"options": "Sales Invoice",
"width": 80
},
{
"label": _("Status"),
"fieldname": "status",
"fieldtype": "Data",
"width": 100
},
{
"label": _("Customer Name"),
"fieldname": "customer_name",
"fieldtype": "Link",
"options": "Customer",
"width": 120
},
{
"label": _("Territory"),
"fieldname": "territory",
"fieldtype": "Data",
"width": 100
},
{
"label": _("Union Europeen"),
"fieldname": "union_europeen",
"fieldtype": "Check",
"width": 80
},
{
"label": _("Tax ID"),
"fieldname": "tax_id",
"fieldtype": "Float",
"width": 80
},
{
"label": _("Shipping Rule"),
"fieldname": "shipping_rule",
"fieldtype": "Float",
"width": 80
},
{
"label": _("Total Taxes and Charges"),
"fieldname": "total_taxes_and_charges",
"fieldtype": "Currency",
"width": 80
},
{
"label": _("Total HT"),
"fieldname": "total_ht",
"fieldtype": "Currency",
"width": 80
},
{
"label": _("Grand Total"),
"fieldname": "grand_total",
"fieldtype": "Currency",
"width": 80
}
]
def get_data(filters, columns):
sales_invoice_data = []
sales_invoice_data = get_sales_invoice_data(filters)
return sales_invoice_data
def get_sales_invoice_data(filters):
conditions = ""
if filters.get("from_date" and "to_date"):
conditions += "where a.posting_date>=%(from_date)s and a.posting_date<=%(to_date)s"
item_results = frappe.db.sql("""select a.name as Name, a.status as Status,
a.customer_name as `Customer Name`, a.territory as Territory, a.union_europeen as `Union Europeen`, a.tax_id as` Tax ID`, a.shipping_rule as `Shipping Rule`, a.total_taxes_and_charges as `Total T&C`,
a.total_ht as `Total HT`, a.grand_total as `Grand Total`
from `tabSales Invoice` a
{conditions}"""
.format(conditions=conditions), filters, as_dict=1)
return item_results
1 Like
remove as_dict=1 from sql query.
2 Likes
what if you use as_list=1 instead? At least this will help when trying to get content to Excel…
You are welcome 