Looking for instructions for Script Reports standard=no

2

Hi,

Please find below working code:

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

    from_date = filters.get("from_date")
    to_date = filters.get("to_date")
    branch = (filters.get("branch") or "") + "%"

    query = """
        SELECT gle.posting_date, gle.account, gle.against, gle.branch, gle.cost_center,  gle.voucher_no, mo.name AS expense_no, d.group_1 AS marque, d.group_2 AS type_action, gle.remarks,
			CASE WHEN mo.purchase_invoice IS NULL THEN gle.debit - gle.credit ELSE d.amount END AS amount,
			mo.mois, gle.name AS gl_entry, s.supplier_name AS against_name, mo.description
		FROM `tabGL Entry` gle LEFT JOIN `tabBPM Marketing Operations` mo ON mo.purchase_invoice = gle.voucher_no
			LEFT JOIN `tabBPM Expense Details` d ON d.parent = mo.name 
			LEFT JOIN tabSupplier s ON gle.against = s.name
		WHERE gle.account = '62720700 - Marketing Expenses - MCO'  AND gle.posting_date BETWEEN %(from_date)s AND %(to_date)s AND gle.branch LIKE %(branch)s
    """

    result = frappe.db.sql(query, {
        "from_date": from_date,
        "to_date": to_date,
        "branch": branch,
    }, as_dict=True)

    return result
result = get_data(filters)

Please set up the filters and column in GUI. Also replace the SQL script with yours.

Thanks,

Divyesh