i have created a script report and it is not working, not generating any report. Also the filters do not appear on the report see images below.
JS:
// apps/soa/soa/report/statement_of_accounts/statement_of_accounts.js
frappe.query_reports["Statement Of Accounts"] = {
"filters": [
{
"fieldname": "customer",
"label": __("Customer"),
"fieldtype": "Link",
"options": "Customer",
"reqd": 1
},
{
"fieldname": "to_date",
"label": __("Statement Date"),
"fieldtype": "Date",
"default": frappe.datetime.get_today(),
"reqd": 1
}
]
};
JSON:
{
"report_name": "Statement Of Accounts",
"ref_doctype": "Sales Invoice",
"report_type": "Script Report",
"is_standard": "No",
"print_format": "Customer Statement Format",
"filters": [
{
"fieldname": "customer",
"label": "Customer",
"fieldtype": "Link",
"options": "Customer",
"reqd": 1
},
{
"fieldname": "to_date",
"label": "Statement Date",
"fieldtype": "Date",
"default": "Today",
"reqd": 1
}
]
}
Python:
import frappe
from frappe.utils import date_diff
def execute(filters=None):
to_date = filters.get("to_date")
customer = filters.get("customer")
data = frappe.db.sql("""
SELECT
posting_date AS invoice_date,
due_date,
name AS invoice_number,
DATEDIFF(%(to_date)s, posting_date) AS age,
po_no AS purchase_order,
delivery_note AS delivery_note,
grand_total AS invoice_amount,
outstanding_amount AS balance_amount
FROM `tabSales Invoice`
WHERE customer = %(customer)s
AND docstatus = 1
AND posting_date <= %(to_date)s
ORDER BY posting_date
""", {"to_date": to_date, "customer": customer}, as_dict=1)
columns = [
{"label":"Invoice Date","fieldname":"invoice_date","fieldtype":"Date","width":100},
{"label":"Due Date","fieldname":"due_date","fieldtype":"Date","width":100},
{"label":"Invoice Number","fieldname":"invoice_number","fieldtype":"Data","width":120},
{"label":"Age","fieldname":"age","fieldtype":"Int","width":60},
{"label":"Purchase Order","fieldname":"purchase_order","fieldtype":"Data","width":100},
{"label":"Delivery Note","fieldname":"delivery_note","fieldtype":"Data","width":100},
{"label":"Invoice Amount","fieldname":"invoice_amount","fieldtype":"Currency","width":100},
{"label":"Balance Amount","fieldname":"balance_amount","fieldtype":"Currency","width":100}
]
return columns, data
I am trying to make statement report similar to the below photos.
-
Invoice and details ( | Invoice Date | Due Date | Invoice Number | Age | Purchase Order | Delivery Note | Invoice Amount | Balance Amount | )
-
Aging Summary ( | 0–30 Days | 31–60 Days | 61–90 Days | 91–120 Days | Above 120 Days |
Report I need without grid:
SQL (which i am currently using):
SELECT
DATE_FORMAT(SI.posting_date, '%%d-%%m-%%Y') AS `Invoice Date`,
DATE_FORMAT(SI.due_date, '%%d-%%m-%%Y') AS `Due Date`,
SI.name AS `Invoice Number`,
DATEDIFF(CURDATE(), SI.posting_date) AS `Age`,
SI.po_no AS `Purchase Order`,
IFNULL(DND.delivery_note, '') AS `Delivery Note`,
FORMAT(SI.grand_total, 2) AS `Invoice Amount`,
FORMAT(SI.outstanding_amount, 2) AS `Balance Amount`
FROM `tabSales Invoice` SI
LEFT JOIN (
SELECT
parent AS sales_invoice,
GROUP_CONCAT(DISTINCT delivery_note SEPARATOR ', ')
AS delivery_note
FROM `tabSales Invoice Item`
WHERE delivery_note IS NOT NULL
GROUP BY parent
) DND ON DND.sales_invoice = SI.name
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0
UNION ALL
-- Total row
SELECT
'<b>Total</b>' AS `Invoice Date`,
'' AS `Due Date`,
'' AS `Invoice Number`,
'' AS `Age`,
'' AS `Purchase Order`,
'' AS `Delivery Note`,
CONCAT('<b>AED ', FORMAT(SUM(SI.grand_total), 2), '</b>') AS `Invoice Amount`,
CONCAT('<b>AED ', FORMAT(SUM(SI.outstanding_amount),2), '</b>') AS `Balance Amount`
FROM `tabSales Invoice` SI
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0
UNION ALL
-- Empty spacer after Total
SELECT
'' AS `Invoice Date`,
'' AS `Due Date`,
'' AS `Invoice Number`,
'' AS `Age`,
'' AS `Purchase Order`,
'' AS `Delivery Note`,
'' AS `Invoice Amount`,
'' AS `Balance Amount`
UNION ALL
-- Aging Summary header
SELECT
'<b>Aging Summary</b>' AS `Invoice Date`,
'' AS `Due Date`,
'' AS `Invoice Number`,
'' AS `Age`,
'' AS `Purchase Order`,
'' AS `Delivery Note`,
'' AS `Invoice Amount`,
'' AS `Balance Amount`
UNION ALL
-- Aging category labels
SELECT
'0–30 Days' AS `Invoice Date`,
'31–60 Days' AS `Due Date`,
'61–90 Days' AS `Invoice Number`,
'91–120 Days' AS `Age`,
'Above 120 Days' AS `Purchase Order`,
'' AS `Delivery Note`,
'' AS `Invoice Amount`,
'' AS `Balance Amount`
UNION ALL
-- Aging amounts row
SELECT
CONCAT('<b>AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 0 AND 30 THEN SI.outstanding_amount ELSE 0 END),2), '</b>'),
CONCAT('<b>AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 31 AND 60 THEN SI.outstanding_amount ELSE 0 END),2), '</b>'),
CONCAT('<b>AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 61 AND 90 THEN SI.outstanding_amount ELSE 0 END),2), '</b>'),
CONCAT('<b>AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 91 AND 120 THEN SI.outstanding_amount ELSE 0 END),2), '</b>'),
CONCAT('<b>AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) > 120 THEN SI.outstanding_amount ELSE 0 END),2), '</b>'),
'' AS `Delivery Note`,
'' AS `Invoice Amount`,
'' AS `Balance Amount`
FROM `tabSales Invoice` SI
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0
ORDER BY
CASE
WHEN `Invoice Date` REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' THEN 0 -- invoice rows
WHEN `Invoice Date` = '<b>Total</b>' THEN 1 -- total
WHEN `Invoice Date` = '' THEN 2 -- spacer
WHEN `Invoice Date` = '<b>Aging Summary</b>' THEN 3 -- header
WHEN `Invoice Date` IN (
'0–30 Days','31–60 Days','61–90 Days','91–120 Days','Above 120 Days'
) THEN 4 -- labels
ELSE 5 -- aging amounts
END,
CASE
WHEN `Invoice Date` REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$'
THEN STR_TO_DATE(`Invoice Date`, '%%d-%%m-%%Y')
ELSE NULL
END,
`Invoice Number`;