mom_expense.js
// Copyright (c) 2024, Nitin Jain and contributors
// For license information, please see license.txt
frappe.query_reports[“MoM Expenses”] = {
“filters”: [
{
“fieldname”: “period”,
“label”: __(“Period”),
“fieldtype”: “Select”,
“options”: [“Monthly”, “Quarterly”, “Yearly”],
“default”: “Monthly”,
“reqd”: 1
},
{
“fieldname”: “financial_year”,
“label”: __(“Financial Year”),
“fieldtype”: “Select”,
“options”: get_financial_years(),
“default”: get_default_financial_year(),
“reqd”: 1
},
{
“fieldname”: “vendor_name”,
“label”: __(“Selected Vendor”),
“fieldtype”: “Link”,
“options”: “NG 02 Vendor Master”, // Link to the correct doctype
“reqd”: 0
}
]
};
// 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;
}
// 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}`;
}
}
mom_expense.json
{
“add_total_row”: 0,
“columns”: ,
“creation”: “2024-09-24 10:08:40.686685”,
“disabled”: 0,
“docstatus”: 0,
“doctype”: “Report”,
“filters”: ,
“idx”: 5,
“is_standard”: “Yes”,
“letterhead”: null,
“modified”: “2024-10-08 12:38:50.248179”,
“modified_by”: “Administrator”,
“module”: “Virtual Munim”,
“name”: “MoM Expenses”,
“owner”: “Administrator”,
“prepared_report”: 0,
“ref_doctype”: “NG 02 Vendor Master”,
“report_name”: “MoM Expenses”,
“report_type”: “Script Report”,
“roles”: [
{
“role”: “System Manager”
}
]
}
mom_expense.py
Copyright (c) 2024, Nitin Jain and contributors
For license information, please see license.txt
import frappe
from datetime import datetime
def execute(filters=None):
period = filters.get(“period”, “Monthly”)
financial_year = filters.get(“financial_year”, get_current_financial_year())
selected_vendor = filters.get(“vendor_name”) # Get the selected vendor if any
start_year, end_year = map(int, financial_year.split('-'))
start_date = f"{start_year}-04-01"
end_date = f"{end_year}-03-31"
columns = [
{
"fieldname": "vendor_name",
"label": "Vendor Name",
"fieldtype": "Data",
"width": 300
}
]
periods = []
if period == "Monthly":
for month, month_name in enumerate(["Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"], start=1):
periods.append(f"{month_name} {start_year if month >= 4 else end_year}")
columns.append({
"fieldname": f"{month_name.lower()}_{start_year if month >= 4 else end_year}",
"label": f"{month_name} {start_year if month >= 4 else end_year}",
"fieldtype": "Float",
"width": 100
})
elif period == "Quarterly":
quarters = ["Q1", "Q2", "Q3", "Q4"]
periods.extend(quarters)
for quarter in quarters:
columns.append({
"fieldname": f"{quarter.lower()}_{financial_year}",
"label": f"{quarter} {financial_year}",
"fieldtype": "Float",
"width": 100
})
elif period == "Yearly":
periods.append(financial_year)
columns.append({
"fieldname": f"year_{financial_year}",
"label": financial_year,
"fieldtype": "Float",
"width": 100
})
# Define filters for the query
filters = {"date_of_invoice": ["between", [start_date, end_date]]}
if selected_vendor:
filters["vendor_name_database"] = selected_vendor
vendor_database = frappe.get_all(
"NG 03 Vendor Database",
filters=filters,
fields=[
'vendor_name_database',
'date_of_invoice',
'total_invoice_value',
'vendor_name_database.vendor_name as vendor_name'
]
)
vendor_expenses = {}
for record in vendor_database:
vendor_name = record['vendor_name']
invoice_date = record['date_of_invoice']
total_invoice_value = float(record['total_invoice_value'])
if period == "Monthly":
month = invoice_date.strftime('%b').lower()
key = (vendor_name, f"{month}_{invoice_date.year}")
elif period == "Quarterly":
if invoice_date.month in [4, 5, 6]:
quarter = "Q1"
elif invoice_date.month in [7, 8, 9]:
quarter = "Q2"
elif invoice_date.month in [10, 11, 12]:
quarter = "Q3"
else:
quarter = "Q4"
key = (vendor_name, f"{quarter.lower()}_{financial_year}")
else:
key = (vendor_name, f"year_{financial_year}")
if key not in vendor_expenses:
vendor_expenses[key] = 0
vendor_expenses[key] += total_invoice_value
vendor_totals = {}
for (vendor_name, period_key), total_expense in vendor_expenses.items():
if vendor_name not in vendor_totals:
vendor_totals[vendor_name] = {'vendor_name': vendor_name}
vendor_totals[vendor_name][period_key] = total_expense
data = list(vendor_totals.values())
# Data for the chart
chart_labels = periods # Set the chart labels as the defined periods
chart_values = [0] * len(periods) # Initialize values for each period
# Accumulate values based on the selected period
for row in data:
if period == "Monthly":
for i, month in enumerate(periods):
month_key = month.lower().replace(" ", "_")
chart_values[i] += row.get(month_key, 0)
elif period == "Quarterly":
for i, quarter in enumerate(periods):
quarter_key = quarter.lower()
chart_values[i] += row.get(f"{quarter_key}_{financial_year}", 0)
elif period == "Yearly":
chart_values[0] += row.get(f"year_{financial_year}", 0)
# Define color arrays for each period type
monthly_colors = ["#FF6384", "#36A2EB", "#FFCE56", "#4BC0C0", "#9966FF", "#FF9F40", "#FF6384", "#36A2EB", "#FFCE56", "#4BC0C0", "#9966FF", "#FF9F40"]
quarterly_colors = ["#FF5733", "#33FF57", "#3357FF", "#FF33A8"]
yearly_colors = ["#FF9800"] # Use a single color for yearly
# Set background colors based on the selected period
if period == "Monthly":
background_colors = monthly_colors[:len(chart_values)] # Limit to the number of months
elif period == "Quarterly":
background_colors = quarterly_colors[:len(chart_values)] # Limit to the number of quarters
else: # Yearly
background_colors = yearly_colors # Single color for yearly
chart_data = {
"data": {
"labels": chart_labels, # Set the chart labels as the defined periods
"datasets": [
{
"name": "Total Expenses", # Name of the dataset
"values": chart_values, # Corresponding values for each period
}
],
},
"type": "bar",
"colors": background_colors
}
return columns, data, None, chart_data
def get_current_financial_year():
current_date = datetime.now()
current_year = current_date.year
current_month = current_date.month
if current_month < 4:
return f"{current_year - 1}-{current_year}"
else:
return f"{current_year}-{current_year + 1}"
Tell me where I am going wrong