[Report] Completed Budget variance Report and corrections in Territory Target Varinace & Sales Person Target Variance Report
diff --git a/accounts/report/budget_variance_report/budget_variance_report.py b/accounts/report/budget_variance_report/budget_variance_report.py
index ac38d9f..99e303b 100644
--- a/accounts/report/budget_variance_report/budget_variance_report.py
+++ b/accounts/report/budget_variance_report/budget_variance_report.py
@@ -26,27 +26,27 @@
columns = get_columns(filters)
period_month_ranges = get_period_month_ranges(filters)
- # tim_map = get_territory_item_month_map(filters)
+ cam_map = get_costcenter_account_month_map(filters)
data = []
- # for territory, territory_items in tim_map.items():
- # for item_group, monthwise_data in territory_items.items():
- # row = [territory, item_group]
- # 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", "achieved", "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
- # totals[2] = totals[0] - totals[1]
- # row += totals
- # data.append(row)
+ for cost_center, cost_center_items in cam_map.items():
+ for account, monthwise_data in cost_center_items.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
+ totals[2] = totals[0] - totals[1]
+ row += totals
+ data.append(row)
return columns, sorted(data, key=lambda x: (x[0], x[1]))
@@ -57,7 +57,7 @@
msgprint(_("Please specify") + ": " + label,
raise_exception=True)
- columns = ["Cost Center:Link/Cost Center:80"]
+ columns = ["Cost Center:Link/Cost Center:100", "Account:Link/Account:100"]
group_months = False if filters["period"] == "Monthly" else True
@@ -105,80 +105,64 @@
return period_month_ranges
-#Get cost center details
-def get_costcenter_details(filters):
- return webnotes.conn.sql("""select t.name, td.item_group, td.target_qty,
- td.target_amount, t.distribution_id
- from `tabTerritory` t, `tabTarget Detail` td
- where td.parent=t.name and td.fiscal_year=%s and
- ifnull(t.distribution_id, '')!='' order by t.name""" %
- ('%s'), (filters.get("fiscal_year")), as_dict=1)
+#Get cost center & target details
+def get_costcenter_target_details(filters):
+ return webnotes.conn.sql("""select cc.name, cc.distribution_id,
+ cc.parent_cost_center, bd.account, bd.budget_allocated
+ from `tabCost Center` cc, `tabBudget Detail` bd
+ where bd.parent=cc.name and bd.fiscal_year=%s and
+ cc.company_name=%s and ifnull(cc.distribution_id, '')!=''
+ order by cc.name""" % ('%s', '%s'),
+ (filters.get("fiscal_year"), filters.get("company")), as_dict=1)
-#Get target distribution details of item group
+#Get target distribution details of accounts of cost center
def get_target_distribution_details(filters):
target_details = {}
- abc = []
+
for d in webnotes.conn.sql("""select bdd.month, bdd.percentage_allocation \
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd, \
- `tabTerritory` t where bdd.parent=bd.name and t.distribution_id=bd.name and \
- bd.fiscal_year=%s """ % ('%s'), (filters.get("fiscal_year")), as_dict=1):
+ `tabCost Center` cc where bdd.parent=bd.name and cc.distribution_id=bd.name and \
+ bd.fiscal_year=%s""" % ('%s'), (filters.get("fiscal_year")), as_dict=1):
target_details.setdefault(d.month, d)
return target_details
-#Get achieved details from sales order
-def get_achieved_details(filters):
- start_date, end_date = get_year_start_end_date(filters)
- achieved_details = {}
+#Get actual details from gl entry
+def get_actual_details(filters):
+ return webnotes.conn.sql("""select gl.account, gl.debit, gl.credit,
+ gl.cost_center, MONTHNAME(gl.posting_date) as month_name
+ from `tabGL Entry` gl, `tabBudget Detail` bd
+ where gl.fiscal_year=%s and company=%s and is_cancelled='No'
+ and bd.account=gl.account""" % ('%s', '%s'),
+ (filters.get("fiscal_year"), filters.get("company")), as_dict=1)
- for d in webnotes.conn.sql("""select soi.item_code, soi.qty, soi.amount, so.transaction_date, \
- MONTHNAME(so.transaction_date) as month_name \
- from `tabSales Order Item` soi, `tabSales Order` so \
- where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and \
- so.transaction_date<=%s""" % ('%s', '%s'), \
- (start_date, end_date), as_dict=1):
- achieved_details.setdefault(d.month_name, d)
-
- return achieved_details
-
-def get_territory_item_month_map(filters):
- territory_details = get_territory_details(filters)
+def get_costcenter_account_month_map(filters):
+ costcenter_target_details = get_costcenter_target_details(filters)
tdd = get_target_distribution_details(filters)
- achieved_details = get_achieved_details(filters)
+ actual_details = get_actual_details(filters)
- tim_map = {}
+ cam_map = {}
- for td in territory_details:
+ for ccd in costcenter_target_details:
for month in tdd:
- tim_map.setdefault(td.name, {}).setdefault(td.item_group, {})\
+ cam_map.setdefault(ccd.name, {}).setdefault(ccd.account, {})\
.setdefault(month, webnotes._dict({
- "target": 0.0, "achieved": 0.0, "variance": 0.0
+ "target": 0.0, "actual": 0.0, "variance": 0.0
}))
- tav_dict = tim_map[td.name][td.item_group][month]
+ tav_dict = cam_map[ccd.name][ccd.account][month]
+ tav_dict.target = ccd.budget_allocated*(tdd[month]["percentage_allocation"]/100)
- for ad in achieved_details:
- if (filters["target_on"] == "Quantity"):
- tav_dict.target = td.target_qty*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == td.item_group:
- tav_dict.achieved += achieved_details[month]["qty"]
-
- if (filters["target_on"] == "Amount"):
- tav_dict.target = td.target_amount*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == td.item_group:
- tav_dict.achieved += achieved_details[month]["amount"]
-
- return tim_map
+ for ad in actual_details:
+ if ad.month_name == month and ad.account == ccd.account \
+ and ad.cost_center == ccd.name:
+ tav_dict.actual += ad.debit - ad.credit
+
+ return cam_map
def get_year_start_end_date(filters):
return webnotes.conn.sql("""select year_start_date,
subdate(adddate(year_start_date, interval 1 year), interval 1 day)
as year_end_date
from `tabFiscal Year`
- where name=%s""", filters["fiscal_year"])[0]
-
-def get_item_group(item_name):
- """Get Item Group of an item"""
-
- return webnotes.conn.sql_list("select item_group from `tabItem` where name=%s""" %
- ('%s'), (item_name))
\ No newline at end of file
+ where name=%s""", filters["fiscal_year"])[0]
\ No newline at end of file
diff --git a/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
index ad9d795..f7afb3d 100644
--- a/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
+++ b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
@@ -23,7 +23,6 @@
columns = get_columns()
item_list = get_items(filters)
aii_account_map = get_aii_accounts()
- webnotes.errprint(aii_account_map)
data = []
for d in item_list:
expense_head = d.expense_head or aii_account_map.get(d.company)
diff --git "a/selling/report/sales_person_target_variance_\050item_group_wise\051/sales_person_target_variance_\050item_group_wise\051.py" "b/selling/report/sales_person_target_variance_\050item_group_wise\051/sales_person_target_variance_\050item_group_wise\051.py"
index 3163829..8f5931d 100644
--- "a/selling/report/sales_person_target_variance_\050item_group_wise\051/sales_person_target_variance_\050item_group_wise\051.py"
+++ "b/selling/report/sales_person_target_variance_\050item_group_wise\051/sales_person_target_variance_\050item_group_wise\051.py"
@@ -117,7 +117,7 @@
#Get target distribution details of item group
def get_target_distribution_details(filters):
target_details = {}
- abc = []
+
for d in webnotes.conn.sql("""select bdd.month, bdd.percentage_allocation \
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd, \
`tabTerritory` t where bdd.parent=bd.name and t.distribution_id=bd.name and \
@@ -129,17 +129,14 @@
#Get achieved details from sales order
def get_achieved_details(filters):
start_date, end_date = get_year_start_end_date(filters)
- achieved_details = {}
-
- for d in webnotes.conn.sql("""select soi.item_code, soi.qty, soi.amount, so.transaction_date, \
- MONTHNAME(so.transaction_date) as month_name \
- from `tabSales Order Item` soi, `tabSales Order` so \
- where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and \
- so.transaction_date<=%s""" % ('%s', '%s'), \
- (start_date, end_date), as_dict=1):
- achieved_details.setdefault(d.month_name, d)
-
- return achieved_details
+
+ return webnotes.conn.sql("""select soi.item_code, soi.qty, soi.amount, so.transaction_date,
+ st.sales_person, MONTHNAME(so.transaction_date) as month_name
+ from `tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
+ where soi.parent=so.name and so.docstatus=1 and
+ st.parent=so.name and so.transaction_date>=%s and
+ so.transaction_date<=%s""" % ('%s', '%s'),
+ (start_date, end_date), as_dict=1)
def get_salesperson_item_month_map(filters):
salesperson_details = get_salesperson_details(filters)
@@ -160,13 +157,15 @@
for ad in achieved_details:
if (filters["target_on"] == "Quantity"):
tav_dict.target = sd.target_qty*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == sd.item_group:
- tav_dict.achieved += achieved_details[month]["qty"]
+ if ad.month_name == month and ''.join(get_item_group(ad.item_code)) == sd.item_group \
+ and ad.sales_person == sd.name:
+ tav_dict.achieved += ad.qty
if (filters["target_on"] == "Amount"):
tav_dict.target = sd.target_amount*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == sd.item_group:
- tav_dict.achieved += achieved_details[month]["amount"]
+ if ad.month_name == month and ''.join(get_item_group(ad.item_code)) == sd.item_group \
+ and ad.sales_person == sd.name:
+ tav_dict.achieved += ad.amount
return sim_map
diff --git "a/selling/report/territory_target_variance_\050item_group_wise\051/territory_target_variance_\050item_group_wise\051.py" "b/selling/report/territory_target_variance_\050item_group_wise\051/territory_target_variance_\050item_group_wise\051.py"
index fe5e628..079f8e8 100644
--- "a/selling/report/territory_target_variance_\050item_group_wise\051/territory_target_variance_\050item_group_wise\051.py"
+++ "b/selling/report/territory_target_variance_\050item_group_wise\051/territory_target_variance_\050item_group_wise\051.py"
@@ -117,11 +117,11 @@
#Get target distribution details of item group
def get_target_distribution_details(filters):
target_details = {}
- abc = []
+
for d in webnotes.conn.sql("""select bdd.month, bdd.percentage_allocation \
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd, \
`tabTerritory` t where bdd.parent=bd.name and t.distribution_id=bd.name and \
- bd.fiscal_year=%s """ % ('%s'), (filters.get("fiscal_year")), as_dict=1):
+ bd.fiscal_year=%s""" % ('%s'), (filters.get("fiscal_year")), as_dict=1):
target_details.setdefault(d.month, d)
return target_details
@@ -129,17 +129,13 @@
#Get achieved details from sales order
def get_achieved_details(filters):
start_date, end_date = get_year_start_end_date(filters)
- achieved_details = {}
- for d in webnotes.conn.sql("""select soi.item_code, soi.qty, soi.amount, so.transaction_date, \
- MONTHNAME(so.transaction_date) as month_name \
- from `tabSales Order Item` soi, `tabSales Order` so \
- where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and \
- so.transaction_date<=%s""" % ('%s', '%s'), \
- (start_date, end_date), as_dict=1):
- achieved_details.setdefault(d.month_name, d)
-
- return achieved_details
+ return webnotes.conn.sql("""select soi.item_code, soi.qty, soi.amount, so.transaction_date,
+ so.territory, MONTHNAME(so.transaction_date) as month_name
+ from `tabSales Order Item` soi, `tabSales Order` so
+ where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and
+ so.transaction_date<=%s""" % ('%s', '%s'),
+ (start_date, end_date), as_dict=1)
def get_territory_item_month_map(filters):
territory_details = get_territory_details(filters)
@@ -160,13 +156,15 @@
for ad in achieved_details:
if (filters["target_on"] == "Quantity"):
tav_dict.target = td.target_qty*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == td.item_group:
- tav_dict.achieved += achieved_details[month]["qty"]
+ if ad.month_name == month and ''.join(get_item_group(ad.item_code)) == td.item_group \
+ and ad.territory == td.name:
+ tav_dict.achieved += ad.qty
if (filters["target_on"] == "Amount"):
tav_dict.target = td.target_amount*(tdd[month]["percentage_allocation"]/100)
- if ad == month and ''.join(get_item_group(achieved_details[month]["item_code"])) == td.item_group:
- tav_dict.achieved += achieved_details[month]["amount"]
+ if ad.month_name == month and ''.join(get_item_group(ad.item_code)) == td.item_group \
+ and ad.territory == td.name:
+ tav_dict.achieved += ad.amount
return tim_map