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