feat: add voucher-wise balance report logic
diff --git a/erpnext/accounts/report/voucher_wise_balance/__init__.py b/erpnext/accounts/report/voucher_wise_balance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/voucher_wise_balance/__init__.py
diff --git a/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.js b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.js
new file mode 100644
index 0000000..0c148f8
--- /dev/null
+++ b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.js
@@ -0,0 +1,28 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Voucher-wise Balance"] = {
+ "filters": [
+ {
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company"
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ "width": "60px"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "width": "60px"
+ },
+ ]
+};
diff --git a/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.json b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.json
new file mode 100644
index 0000000..434e5a3
--- /dev/null
+++ b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.json
@@ -0,0 +1,33 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2023-06-27 16:40:15.109554",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "json": "{}",
+ "letter_head": "LetterHead",
+ "modified": "2023-06-27 16:40:32.493725",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Voucher-wise Balance",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "GL Entry",
+ "report_name": "Voucher-wise Balance",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.py b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.py
new file mode 100644
index 0000000..cbe2549
--- /dev/null
+++ b/erpnext/accounts/report/voucher_wise_balance/voucher_wise_balance.py
@@ -0,0 +1,66 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe import _
+from frappe.query_builder.functions import Sum
+
+
+def execute(filters=None):
+ columns = get_columns()
+ data = get_data(filters)
+ return columns, data
+
+
+def get_columns():
+ return [
+ {"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 300},
+ {
+ "label": _("Voucher No"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 300,
+ },
+ {
+ "fieldname": "debit",
+ "label": _("Debit"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 300,
+ },
+ {
+ "fieldname": "credit",
+ "label": _("Credit"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 300,
+ },
+ ]
+
+
+def get_data(filters):
+ gle = frappe.qb.DocType("GL Entry")
+ query = (
+ frappe.qb.from_(gle)
+ .select(
+ gle.voucher_type, gle.voucher_no, Sum(gle.debit).as_("debit"), Sum(gle.credit).as_("credit")
+ )
+ .groupby(gle.voucher_no)
+ )
+ query = apply_filters(query, filters, gle)
+ gl_entries = query.run(as_dict=True, debug=True)
+ unmatched = [entry for entry in gl_entries if entry.debit != entry.credit]
+ return unmatched
+
+
+def apply_filters(query, filters, gle):
+ if filters.get("company"):
+ query = query.where(gle.company == filters.company)
+ if filters.get("voucher_type"):
+ query = query.where(gle.voucher_type == filters.voucher_type)
+ if filters.get("from_date"):
+ query = query.where(gle.posting_date >= filters.from_date)
+ if filters.get("to_date"):
+ query = query.where(gle.posting_date <= filters.to_date)
+ return query