Enhancement in Budget Variance and other reports (#15455)
* Enhanced Budget Variance report and added filters and columns in other reports
* Update budget_variance_report.js
* Changes in budget variance report
* Spacing in column names
diff --git a/erpnext/accounts/report/budget_variance_report/budget_variance_report.js b/erpnext/accounts/report/budget_variance_report/budget_variance_report.js
index cf8d549..cd9f9d9 100644
--- a/erpnext/accounts/report/budget_variance_report/budget_variance_report.js
+++ b/erpnext/accounts/report/budget_variance_report/budget_variance_report.js
@@ -4,8 +4,16 @@
frappe.query_reports["Budget Variance Report"] = {
"filters": [
{
- fieldname: "fiscal_year",
- label: __("Fiscal Year"),
+ fieldname: "from_fiscal_year",
+ label: __("From Fiscal Year"),
+ fieldtype: "Link",
+ options: "Fiscal Year",
+ default: frappe.sys_defaults.fiscal_year,
+ reqd: 1
+ },
+ {
+ fieldname: "to_fiscal_year",
+ label: __("To Fiscal Year"),
fieldtype: "Link",
options: "Fiscal Year",
default: frappe.sys_defaults.fiscal_year,
@@ -21,7 +29,7 @@
{ "value": "Half-Yearly", "label": __("Half-Yearly") },
{ "value": "Yearly", "label": __("Yearly") }
],
- default: "Monthly",
+ default: "Yearly",
reqd: 1
},
{
@@ -46,5 +54,11 @@
fieldtype: "Link",
options: "Cost Center"
},
+ {
+ fieldname:"show_cumulative",
+ label: __("Show Cumulative Amount"),
+ fieldtype: "Check",
+ default: 0,
+ },
]
}
diff --git a/erpnext/accounts/report/budget_variance_report/budget_variance_report.py b/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
index 75739d9..b292bd3 100644
--- a/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
+++ b/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
@@ -9,7 +9,7 @@
from erpnext.controllers.trends import get_period_date_ranges, get_period_month_ranges
from six import iteritems
-
+from pprint import pprint
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
@@ -19,7 +19,7 @@
else:
cost_centers = get_cost_centers(filters)
- period_month_ranges = get_period_month_ranges(filters["period"], filters["fiscal_year"])
+ period_month_ranges = get_period_month_ranges(filters["period"], filters["from_fiscal_year"])
cam_map = get_cost_center_account_month_map(filters)
data = []
@@ -29,18 +29,28 @@
for account, monthwise_data in iteritems(cost_center_items):
row = [cost_center, account]
totals = [0, 0, 0]
- for relevant_months in period_month_ranges:
- period_data = [0, 0, 0]
- for month in relevant_months:
- month_data = monthwise_data.get(month, {})
- for i, fieldname in enumerate(["target", "actual", "variance"]):
- value = flt(month_data.get(fieldname))
- period_data[i] += value
- totals[i] += value
- period_data[2] = period_data[0] - period_data[1]
- row += period_data
+ for year in get_fiscal_years(filters):
+ last_total = 0
+ for relevant_months in period_month_ranges:
+ period_data = [0, 0, 0]
+ for month in relevant_months:
+ if monthwise_data.get(year[0]):
+ month_data = monthwise_data.get(year[0]).get(month, {})
+ for i, fieldname in enumerate(["target", "actual", "variance"]):
+ value = flt(month_data.get(fieldname))
+ period_data[i] += value
+ totals[i] += value
+
+ period_data[0] += last_total
+
+ if(filters.get("show_cumulative")):
+ last_total = period_data[0] - period_data[1]
+
+ period_data[2] = period_data[0] - period_data[1]
+ row += period_data
totals[2] = totals[0] - totals[1]
- row += totals
+ if filters["period"] != "Yearly" :
+ row += totals
data.append(row)
return columns, data
@@ -50,21 +60,32 @@
frappe.throw(_("Filter based on Cost Center is only applicable if Budget Against is selected as Cost Center"))
def get_columns(filters):
- columns = [_(filters.get("budget_against")) + ":Link/%s:120"%(filters.get("budget_against")), _("Account") + ":Link/Account:120"]
+ columns = [_(filters.get("budget_against")) + ":Link/%s:80"%(filters.get("budget_against")), _("Account") + ":Link/Account:80"]
group_months = False if filters["period"] == "Monthly" else True
- for from_date, to_date in get_period_date_ranges(filters["period"], filters["fiscal_year"]):
- for label in [_("Target") + " (%s)", _("Actual") + " (%s)", _("Variance") + " (%s)"]:
- if group_months:
- label = label % (formatdate(from_date, format_string="MMM") + " - " + formatdate(to_date, format_string="MMM"))
+ fiscal_year = get_fiscal_years(filters)
+
+ for year in fiscal_year:
+ for from_date, to_date in get_period_date_ranges(filters["period"], year[0]):
+ if filters["period"] == "Yearly":
+ labels = [_("Budget") + " " + str(year[0]), _("Actual ") + " " + str(year[0]), _("Varaiance ") + " " + str(year[0])]
+ for label in labels:
+ columns.append(label+":Float:80")
else:
- label = label % formatdate(from_date, format_string="MMM")
+ for label in [_("Budget") + " (%s)" + " " + str(year[0]), _("Actual") + " (%s)" + " " + str(year[0]), _("Variance") + " (%s)" + " " + str(year[0])]:
+ if group_months:
+ label = label % (formatdate(from_date, format_string="MMM") + "-" + formatdate(to_date, format_string="MMM"))
+ else:
+ label = label % formatdate(from_date, format_string="MMM")
- columns.append(label+":Float:120")
+ columns.append(label+":Float:80")
- return columns + [_("Total Target") + ":Float:120", _("Total Actual") + ":Float:120",
- _("Total Variance") + ":Float:120"]
+ if filters["period"] != "Yearly" :
+ return columns + [_("Total Budget") + ":Float:80", _("Total Actual") + ":Float:80",
+ _("Total Variance") + ":Float:80"]
+ else:
+ return columns
def get_cost_centers(filters):
cond = "and 1=1"
@@ -81,21 +102,23 @@
cond += " and b.cost_center='%s'" % frappe.db.escape(filters.get("cost_center"))
return frappe.db.sql("""
- select b.{budget_against} as budget_against, b.monthly_distribution, ba.account, ba.budget_amount
+ select b.{budget_against} as budget_against, b.monthly_distribution, ba.account, ba.budget_amount,b.fiscal_year
from `tabBudget` b, `tabBudget Account` ba
- where b.name=ba.parent and b.docstatus = 1 and b.fiscal_year=%s
- and b.budget_against = %s and b.company=%s {cond}
+ where b.name=ba.parent and b.docstatus = 1 and b.fiscal_year between %s and %s
+ and b.budget_against = %s and b.company=%s {cond} order by b.fiscal_year
""".format(budget_against=filters.get("budget_against").replace(" ", "_").lower(), cond=cond),
- (filters.fiscal_year, filters.budget_against, filters.company), as_dict=True)
+ (filters.from_fiscal_year,filters.to_fiscal_year,filters.budget_against, filters.company), as_dict=True)
+
+
#Get target distribution details of accounts of cost center
def get_target_distribution_details(filters):
target_details = {}
for d in frappe.db.sql("""select md.name, mdp.month, mdp.percentage_allocation
from `tabMonthly Distribution Percentage` mdp, `tabMonthly Distribution` md
- where mdp.parent=md.name and md.fiscal_year=%s""", (filters["fiscal_year"]), as_dict=1):
+ where mdp.parent=md.name and md.fiscal_year between %s and %s order by md.fiscal_year""",(filters.from_fiscal_year, filters.to_fiscal_year), as_dict=1):
target_details.setdefault(d.name, {}).setdefault(d.month, flt(d.percentage_allocation))
-
+
return target_details
#Get actual details from gl entry
@@ -107,7 +130,7 @@
cc_lft, cc_rgt = frappe.db.get_value("Cost Center", name, ["lft", "rgt"])
cond = "lft>='{lft}' and rgt<='{rgt}'".format(lft = cc_lft, rgt=cc_rgt)
- ac_details = frappe.db.sql("""select gl.account, gl.debit, gl.credit,
+ ac_details = frappe.db.sql("""select gl.account, gl.debit, gl.credit,gl.fiscal_year,
MONTHNAME(gl.posting_date) as month_name, b.{budget_against} as budget_against
from `tabGL Entry` gl, `tabBudget Account` ba, `tabBudget` b
where
@@ -115,11 +138,11 @@
and b.docstatus = 1
and ba.account=gl.account
and b.{budget_against} = gl.{budget_against}
- and gl.fiscal_year=%s
+ and gl.fiscal_year between %s and %s
and b.{budget_against}=%s
- and exists(select name from `tab{tab}` where name=gl.{budget_against} and {cond}) group by gl.name
- """.format(tab = filters.budget_against, budget_against = budget_against, cond = cond),
- (filters.fiscal_year, name), as_dict=1)
+ and exists(select name from `tab{tab}` where name=gl.{budget_against} and {cond}) group by gl.name order by gl.fiscal_year
+ """.format(tab = filters.budget_against, budget_against = budget_against, cond = cond,from_year=filters.from_fiscal_year,to_year=filters.to_fiscal_year),
+ (filters.from_fiscal_year, filters.to_fiscal_year, name), as_dict=1)
cc_actual_details = {}
for d in ac_details:
@@ -139,13 +162,12 @@
for month_id in range(1, 13):
month = datetime.date(2013, month_id, 1).strftime('%B')
-
- cam_map.setdefault(ccd.budget_against, {}).setdefault(ccd.account, {})\
+ cam_map.setdefault(ccd.budget_against, {}).setdefault(ccd.account, {}).setdefault(ccd.fiscal_year,{})\
.setdefault(month, frappe._dict({
"target": 0.0, "actual": 0.0
}))
- tav_dict = cam_map[ccd.budget_against][ccd.account][month]
+ tav_dict = cam_map[ccd.budget_against][ccd.account][ccd.fiscal_year][month]
month_percentage = tdd.get(ccd.monthly_distribution, {}).get(month, 0) \
if ccd.monthly_distribution else 100.0/12
@@ -156,3 +178,11 @@
tav_dict.actual += flt(ad.debit) - flt(ad.credit)
return cam_map
+
+def get_fiscal_years(filters):
+
+ fiscal_year = frappe.db.sql("""select name from `tabFiscal Year` where
+ name between %(from_fiscal_year)s and %(to_fiscal_year)s""",
+ {'from_fiscal_year': filters["from_fiscal_year"], 'to_fiscal_year': filters["to_fiscal_year"]})
+
+ return fiscal_year
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.js b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.js
index b612e02..9dd9b3f 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.js
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.js
@@ -28,6 +28,24 @@
"label": __("Mode of Payment"),
"fieldtype": "Link",
"options": "Mode of Payment"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse"
+ },
+ {
+ "fieldname":"brand",
+ "label": __("Brand"),
+ "fieldtype": "Link",
+ "options": "Brand"
+ },
+ {
+ "fieldname":"item_group",
+ "label": __("Item Group"),
+ "fieldtype": "Link",
+ "options": "Item Group"
}
]
}
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
index 75f1fca..a8d8159 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -108,6 +108,23 @@
conditions += """ and exists(select name from `tabSales Invoice Payment`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
+
+ if filters.get("warehouse"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
+
+
+ if filters.get("brand"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)"""
+
+ if filters.get("item_group"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
+
return conditions
diff --git a/erpnext/accounts/report/sales_register/sales_register.js b/erpnext/accounts/report/sales_register/sales_register.js
index db74626..0b48882c 100644
--- a/erpnext/accounts/report/sales_register/sales_register.js
+++ b/erpnext/accounts/report/sales_register/sales_register.js
@@ -52,6 +52,18 @@
"label": __("Warehouse"),
"fieldtype": "Link",
"options": "Warehouse"
+ },
+ {
+ "fieldname":"brand",
+ "label": __("Brand"),
+ "fieldtype": "Link",
+ "options": "Brand"
+ },
+ {
+ "fieldname":"item_group",
+ "label": __("Item Group"),
+ "fieldtype": "Link",
+ "options": "Item Group"
}
]
}
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index dd92c92..d37caaf 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -153,6 +153,16 @@
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
+ if filters.get("brand"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)"""
+
+ if filters.get("item_group"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
+
return conditions
def get_invoices(filters, additional_query_columns):
diff --git a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
index ffa4180..7357ab2 100644
--- a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
+++ b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
@@ -29,9 +29,9 @@
if customer_naming_type == "Naming Series":
row = [d.name, d.customer_name, credit_limit, outstanding_amt, bal,
- d.bypass_credit_limit_check_at_sales_order]
+ d.bypass_credit_limit_check_at_sales_order, d.disabled]
else:
- row = [d.name, credit_limit, outstanding_amt, bal, d.bypass_credit_limit_check_at_sales_order]
+ row = [d.name, credit_limit, outstanding_amt, bal, d.bypass_credit_limit_check_at_sales_order, d.disabled]
if credit_limit:
data.append(row)
@@ -40,11 +40,13 @@
def get_columns(customer_naming_type):
columns = [
+ _("Name") + ":Link/Customer:120",
_("Customer") + ":Link/Customer:120",
_("Credit Limit") + ":Currency:120",
_("Outstanding Amt") + ":Currency:100",
_("Credit Balance") + ":Currency:120",
- _("Bypass credit check at Sales Order ") + ":Check:240"
+ _("Bypass credit check at Sales Order ") + ":Check:240",
+ _("Is Disabled ") + ":Check:240"
]
if customer_naming_type == "Naming Series":
@@ -59,5 +61,5 @@
conditions += " where name = %(customer)s"
return frappe.db.sql("""select name, customer_name,
- bypass_credit_limit_check_at_sales_order from `tabCustomer` %s
+ bypass_credit_limit_check_at_sales_order,disabled from `tabCustomer` %s
""" % conditions, filters, as_dict=1)
\ No newline at end of file
diff --git a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
index ae2cf8c..8897792 100644
--- a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
+++ b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
@@ -17,7 +17,7 @@
for d in entries:
if d.stock_qty > 0 or filters.get('show_return_entries', 0):
data.append([
- d.name, d.customer, d.territory, d.posting_date, d.item_code,
+ d.name, d.customer, d.territory, item_details.get(d.item_code, {}).get("website_warehouse"), d.posting_date, d.item_code,
item_details.get(d.item_code, {}).get("item_group"), item_details.get(d.item_code, {}).get("brand"),
d.stock_qty, d.base_net_amount, d.sales_person, d.allocated_percentage, d.contribution_amt
])
@@ -33,7 +33,8 @@
msgprint(_("Please select the document type first"), raise_exception=1)
return [filters["doc_type"] + ":Link/" + filters["doc_type"] + ":140",
- _("Customer") + ":Link/Customer:140", _("Territory") + ":Link/Territory:100", _("Posting Date") + ":Date:100",
+ _("Customer") + ":Link/Customer:140", _("Territory") + ":Link/Territory:100", _("Warehouse") + ":Link/Warehouse:100",
+ _("Posting Date") + ":Date:100",
_("Item Code") + ":Link/Item:120", _("Item Group") + ":Link/Item Group:120",
_("Brand") + ":Link/Brand:120", _("Qty") + ":Float:100", _("Amount") + ":Currency:120",
_("Sales Person") + ":Link/Sales Person:140", _("Contribution %") + "::110",
@@ -115,7 +116,7 @@
def get_item_details():
item_details = {}
- for d in frappe.db.sql("""select name, item_group, brand from `tabItem`""", as_dict=1):
+ for d in frappe.db.sql("""select name, item_group, brand, website_warehouse from `tabItem`""", as_dict=1):
item_details.setdefault(d.name, d)
return item_details
diff --git a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.json b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.json
index 8ff7d75..989d370 100644
--- a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.json
+++ b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.json
@@ -1,17 +1,17 @@
{
- "add_total_row": 0,
- "apply_user_permissions": 1,
+ "add_total_row": 1,
"creation": "2013-06-04 11:03:47",
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
"idx": 3,
"is_standard": "Yes",
- "modified": "2017-02-24 20:19:13.150769",
+ "modified": "2018-09-20 18:20:49.229153",
"modified_by": "Administrator",
"module": "Stock",
"name": "Batch-Wise Balance History",
"owner": "Administrator",
+ "prepared_report": 0,
"ref_doctype": "Stock Ledger Entry",
"report_name": "Batch-Wise Balance History",
"report_type": "Script Report",