def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["fiscal_year"])
sim_map = get_salesperson_item_month_map(filters)
data = []
for salesperson, salesperson_items in sim_map.items():
for item_group, monthwise_data in salesperson_items.items():
row = [salesperson, 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)
return columns, sorted(data, key=lambda x: (x[0], x[1]))
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
cost_centers = get_cost_centers(filters.company)
period_month_ranges = get_period_month_ranges(filters["period"], filters["fiscal_year"])
cam_map = get_cost_center_account_month_map(filters)
data = []
for cost_center in cost_centers:
cost_center_items = cam_map.get(cost_center)
if cost_center_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, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["fiscal_year"])
cam_map = get_costcenter_account_month_map(filters)
data = []
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]))
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
columns = get_columns(filters)
if filters.get("cost_center"):
cost_centers = [filters.get("cost_center")]
else:
cost_centers = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"], filters["fiscal_year"])
cam_map = get_cost_center_account_month_map(filters)
data = []
for cost_center in cost_centers:
cost_center_items = cam_map.get(cost_center)
if cost_center_items:
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
totals[2] = totals[0] - totals[1]
row += totals
data.append(row)
return columns, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
period_month_ranges = get_period_month_ranges(filters["period"], filters["fiscal_year"])
sim_map = get_salesperson_item_month_map(filters)
data = []
for salesperson, salesperson_items in sim_map.items():
for item_group, monthwise_data in salesperson_items.items():
row = [salesperson, 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)
return columns, sorted(data, key=lambda x: (x[0], x[1]))
def execute(filters=None):
columns, data = [], []
columns = get_columns(filters)
cost_centers = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["fiscal_year"])
begin_month = []
end_month = []
if filters.get("period") == "Monthly":
begin_month = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
end_month = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
elif filters.get("period") == "Quarterly":
begin_month = [1, 4, 7, 10]
end_month = [3, 6, 9, 12]
elif filters.get("period") == "Half-Yearly":
begin_month = [1, 7]
end_month = [6, 12]
elif filters.get("period") == "Yearly":
begin_month = [1]
end_month = [12]
cc_columns = ""
gle_columns = ""
skpjb_columns = ""
for i in range(0, len(begin_month)):
begin = begin_month[i]
end = end_month[i]
cc_columns = """{0}, IFNULL(gle.`biaya_{1}`,0), IFNULL(skpjb.`omzet_{1}`,0) """.format(
cc_columns, begin)
gle_columns = """{0}, SUM(IF(MONTH(gle.`posting_date`)>={1} AND MONTH(gle.`posting_date`)<={2},gle.`debit`,0)) AS `biaya_{1}` """.format(
gle_columns, begin, end)
skpjb_columns = """{0}, SUM(IF(MONTH(skpjb.`posting_date`)>={1} AND MONTH(skpjb.`posting_date`)<={2},skpjb.`harga_jual`,0)) AS `omzet_{1}`
""".format(skpjb_columns, begin, end)
data = frappe.db.sql("""
SELECT cc.`name` {0} FROM `tabCost Center`cc
LEFT JOIN
(
SELECT gle.`cost_center` {1} FROM `tabGL Entry`gle
#WHERE gle.`fiscal_year`="{3}" AND gle.`company`="{4}"
GROUP BY gle.`cost_center`
)gle ON gle.`cost_center` = cc.`name`
LEFT JOIN
(
SELECT cc.`name` {2} FROM `tabSKPJB`skpjb JOIN `tabKavling`k ON k.`name`=skpjb.`kavling`
JOIN `tabCost Center`cc
ON cc.`company` = k.`company`
AND cc.`lead_source`=skpjb.`lead_source`
WHERE k.`company`="{4}" AND YEAR(skpjb.`posting_date`)={3} AND skpjb.`docstatus`=1
)skpjb ON skpjb.`name`=cc.`name`
WHERE cc.`company` ="{4}"
""".format(cc_columns, gle_columns, skpjb_columns,
filters.get("fiscal_year"), filters.get("company")))
return columns, data
def execute(filters=None):
if not filters:
filters = {}
columns = get_columns(filters)
if filters.get("budget_against_filter"):
dimensions = filters.get("budget_against_filter")
else:
dimensions = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["from_fiscal_year"])
cam_map = get_dimension_account_month_map(filters)
data = []
for dimension in dimensions:
dimension_items = cam_map.get(dimension)
if dimension_items:
for account, monthwise_data in iteritems(dimension_items):
row = [
dimension,
frappe.get_doc("Project Activities",
dimension).project_activity, account
]
totals = [0, 0, 0]
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]
if filters["period"] != "Yearly":
row += totals
data.append(row)
return columns, data
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
columns = get_columns(filters)
if filters.get("cost_center"):
cost_centers = [filters.get("cost_center")]
else:
cost_centers = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"], filters["from_fiscal_year"])
cam_map = get_cost_center_account_month_map(filters)
data = []
for cost_center in cost_centers:
cost_center_items = cam_map.get(cost_center)
if cost_center_items:
for account, monthwise_data in iteritems(cost_center_items):
row = [cost_center, account]
totals = [0, 0, 0]
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]
if filters["period"] != "Yearly" :
row += totals
data.append(row)
return columns, data
def execute(filters=None):
if not filters:
filters = {}
columns = get_columns(filters)
if filters.get("budget_against_filter"):
dimensions = filters.get("budget_against_filter")
else:
dimensions = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["from_fiscal_year"])
cam_map = get_dimension_account_month_map(filters)
data = []
for dimension in dimensions:
dimension_items = cam_map.get(dimension)
if dimension_items:
data = get_final_data(dimension, dimension_items, filters,
period_month_ranges, data, 0)
else:
DCC_allocation = frappe.db.sql(
"""SELECT parent, sum(percentage_allocation) as percentage_allocation
FROM `tabDistributed Cost Center`
WHERE cost_center IN %(dimension)s
AND parent NOT IN %(dimension)s
GROUP BY parent""",
{"dimension": [dimension]},
)
if DCC_allocation:
filters["budget_against_filter"] = [DCC_allocation[0][0]]
ddc_cam_map = get_dimension_account_month_map(filters)
dimension_items = ddc_cam_map.get(DCC_allocation[0][0])
if dimension_items:
data = get_final_data(dimension, dimension_items, filters,
period_month_ranges, data,
DCC_allocation[0][1])
chart = get_chart_data(filters, columns, data)
return columns, data, None, chart
def execute(filters=None):
if not filters:
filters = {}
columns = get_columns(filters)
if filters.get("budget_against_filter"):
dimensions = filters.get("budget_against_filter")
else:
dimensions = get_cost_centers(filters)
period_month_ranges = get_period_month_ranges(filters["period"],
filters["from_fiscal_year"])
cam_map = get_dimension_account_month_map(filters)
data = []
for dimension in dimensions:
dimension_items = cam_map.get(dimension)
if dimension_items:
data = get_final_data(dimension, dimension_items, filters,
period_month_ranges, data, 0)
chart = get_chart_data(filters, columns, data)
return columns, data, None, chart
