Js code
// Copyright (c) 2024, Nitin Jain and contributors
// For license information, please see license.txt
frappe.query_reports["Cash Flow Analysis"] = {
"filters": [
{
fieldname: "financial_year",
label: __("Financial Year"),
fieldtype: "Select",
options: get_financial_years(),
default: get_default_financial_year(),
reqd: 1,
depends_on: 'eval:doc.date_range=="Fiscal Year"'
},
{
fieldname: "date_range",
label: __("Select Range Type"),
fieldtype: "Select",
options: ["Fiscal Year", "Date Range"],
default: "Fiscal Year",
reqd: 1,
on_change: function () {
const dateRangeValue = frappe.query_report.get_filter_value("date_range");
// Toggle visibility of dependent filters
frappe.query_report.toggle_filter_display("financial_year", dateRangeValue === "Date Range");
frappe.query_report.toggle_filter_display("start_date", dateRangeValue === "Fiscal Year");
frappe.query_report.toggle_filter_display("end_date", dateRangeValue === "Fiscal Year");
if (dateRangeValue === "Fiscal Year") {
frappe.query_report.set_filter_value("start_date", null);
frappe.query_report.set_filter_value("end_date", null);
} else {
frappe.query_report.set_filter_value("financial_year", null);
}
frappe.query_report.refresh();
},
},
{
fieldname: "start_date",
label: __("Start Date"),
fieldtype: "Date",
reqd: 1,
depends_on: 'eval:doc.date_range=="Date Range"'
},
{
fieldname: "end_date",
label: __("End Date"),
fieldtype: "Date",
reqd: 1,
depends_on: 'eval:doc.date_range=="Date Range"'
}
],
};
// Helper function to generate financial year options
function get_financial_years() {
const currentYear = new Date().getFullYear();
const financialYears = [];
for (let i = 0; i < 5; i++) {
const startYear = currentYear - i;
const endYear = startYear + 1;
financialYears.push(`${startYear}-${endYear}`);
}
return financialYears.join('\n');
}
// Helper function to get the default financial year
function get_default_financial_year() {
const currentDate = new Date();
const currentMonth = currentDate.getMonth() + 1; // Months are 0-indexed in JS
const currentYear = currentDate.getFullYear();
// If before April, return the previous financial year
if (currentMonth < 4) {
return `${currentYear - 1}-${currentYear}`;
} else {
return `${currentYear}-${currentYear + 1}`;
}
}
Py code
import frappe
def execute(filters=None):
if not filters:
frappe.throw("Filters are missing. Please set the required filters.")
# Handle date range selection
date_range = filters.get("date_range")
financial_year = filters.get("financial_year")
start_date = filters.get("start_date")
end_date = filters.get("end_date")
if date_range == "Fiscal Year" and financial_year:
columns = get_columns(financial_year)
data = get_hierarchical_data(filters)
else: # Date Range selected
start_year = start_date.split("-")[0]
end_year = end_date.split("-")[0]
financial_year = f"{start_year}-{end_year}"
columns = get_columns(financial_year)
data = get_hierarchical_data(filters)
return columns, data
def get_columns(financial_year):
return [
{"fieldname": "account", "label": "Account", "fieldtype": "Data", "width": 300},
{"fieldname": "amount", "label": f"{financial_year}", "fieldtype": "Currency", "width": 150},
]
def get_hierarchical_data(filters):
data_range = filters.get("date_range")
data = []
if data_range == "Fiscal Year":
financial_year = filters.get("financial_year")
start_year, end_year = map(int, financial_year.split('-'))
start_date = f"{start_year}-04-01"
end_date = f"{end_year}-03-31"
filters = {"date_of_invoice": ["between", [start_date, end_date]]}
filters["status"] = "Paid"
sales_data = frappe.get_all('NG 04 Sales Booking Prod',filters={'invoice_date_sales': ["between", [start_date,end_date]]},fields=['revenue_booked'])
total_revenue = sum(record['revenue_booked'] for record in sales_data)
vendor_data = frappe.get_all('NG 03 Vendor Database Prod',filters=filters,fields=['total_invoice_value'])
total_expense = sum(float(record['total_invoice_value'].replace(',', '')) for record in vendor_data)
profit = total_revenue - total_expense
partner_1_share = profit * (33.33 / 100)
partner_2_share = profit * (33.33 / 100)
partner_3_share = profit * (33.33 / 100)
data = [
{"account": "Income", "amount": total_revenue, "indent": 0},
{"account": "Revenue Booked", "amount": total_revenue, "indent": 1},
{"account": "Total Income (Credit)", "amount": total_revenue, "indent": 0},
{"account": "Expenses", "amount": total_expense, "indent": 0},
{"account": "Vendor Expense", "amount": total_expense, "indent": 1},
{"account": "Total Expense (Debit)", "amount": total_expense, "indent": 0},
{"account": "Profit for the Year","amount": profit,"indent": 0},
{"account": "Partner 1", "amount": partner_1_share, "indent": 0},
{"account": "Partner 2", "amount": partner_2_share, "indent": 0},
{"account": "Partner 3", "amount": partner_3_share, "indent": 0},
]
else:
start_date = filters.get("start_date")
end_date = filters.get("end_date")
filters = {"date_of_invoice": ["between", [start_date, end_date]]}
filters["status"] = "Paid"
sales_data = frappe.get_all('NG 04 Sales Booking Prod',filters={'invoice_date_sales': ["between", [start_date,end_date]]},fields=['revenue_booked'])
total_revenue = sum(record['revenue_booked'] for record in sales_data)
vendor_data = frappe.get_all('NG 03 Vendor Database Prod',filters=filters,fields=['total_invoice_value'])
total_expense = sum(float(record['total_invoice_value'].replace(',', '')) for record in vendor_data)
profit = total_revenue - total_expense
partner_1_share = profit * (33.33 / 100)
partner_2_share = profit * (33.33 / 100)
partner_3_share = profit * (33.33 / 100)
data = [
{"account": "Income", "amount": total_revenue, "indent": 0},
{"account": "Revenue Booked", "amount": total_revenue, "indent": 1},
{"account": "Total Income (Credit)", "amount": total_revenue, "indent": 0},
{"account": "Expenses", "amount": total_expense, "indent": 0},
{"account": "Vendor Expense", "amount": total_expense, "indent": 1},
{"account": "Total Expense (Debit)", "amount": total_expense, "indent": 0},
{"account": "Profit for the Year","amount": profit,"indent": 0},
{"account": "Partner 1", "amount": partner_1_share, "indent": 0},
{"account": "Partner 2", "amount": partner_2_share, "indent": 0},
{"account": "Partner 3", "amount": partner_3_share, "indent": 0},
]
return data
why giving me error please set filter. Even though i am selecting filter as required