def read_respreads_input(full_file_path, company, scenario):
respreads_data_df = pd.read_excel(full_file_path, header=0)
print(len(respreads_data_df))
input_date = date_utils.get_dates_info_from_amr_scenario(scenario)[0][-1]
respreads_upload_list = []
"""
Plant Account No Account Title Model group Project ID Cost Component ID Work Order Number Outage Code ID Invoice ID Contract Number ID Reference number Cost Category Cost Sub Category Project Name
"""
print(input_date)
for item in respreads_data_df:
if isinstance(item, datetime.datetime):
if str(item).split(" ")[0] > input_date:
# print (item);
# value_list.append(list(respreads_data_df[item]));
for i in range(0, len(respreads_data_df)):
respreads_upload_list.append(list(respreads_data_df.iloc[i][['Plant','Account No','Account Title',\
'Model group','Project ID','Cost Component ID','Work Order Number','Outage Code ID',\
'Invoice ID', 'Contract Number ID','Reference number','Cost Category',\
'Cost Sub Category','Project Name']]) + [str(item).split(" ")[0],str(respreads_data_df[item].iloc[i])])
# for item in respreads_upload_list:
# print (item);
#
return respreads_upload_list
def read_labor_input(full_file_path, company, scenario):
labor_data_df = pd.read_excel(full_file_path, header=0)
input_date = date_utils.get_dates_info_from_amr_scenario(scenario)[0][-1]
# print (input_date);
labor_upload_list = []
for item in labor_data_df:
# print (len(labor_data_df[item]));
if item == 'Plant Seniority Date':
temp_list = list(labor_data_df[item])
temp_list = [
datetime.datetime.strptime(str(item),
"%m/%d/%Y").strftime("%Y-%m-%d")
for item in temp_list
]
# print (temp_list);
labor_upload_list.append(temp_list)
continue
labor_upload_list.append(list(labor_data_df[item]))
labor_upload_list = list(zip(*labor_upload_list))
labor_upload_list = [
list((input_date, scenario, company) + item)
for item in labor_upload_list
]
# print (len(labor_upload_list));
# print (len(labor_upload_list[0]));
return labor_upload_list
def read_headcount_input(full_file_path, company, scenario):
headcount_data_df = pd.read_excel(full_file_path, header=0)
input_date = date_utils.get_dates_info_from_amr_scenario(scenario)[0][-1]
temp_list = []
date_list = []
for item in headcount_data_df:
if isinstance(item, datetime.datetime):
# print (item);
temp_list.append(list(headcount_data_df[item]))
date_list.append(item)
entity_list = []
for item in headcount_data_df:
if item == 'Entity':
entity_list = list(headcount_data_df[item])
print(entity_list)
# for item in temp_list:
# print (item);
headcount_upload_list = []
for i in range(0, len(temp_list)):
for j in range(0, len(temp_list[i]) - 1):
headcount_upload_list.append([
input_date, scenario, company, entity_list[j],
str(date_list[i]).split(" ")[0], temp_list[i][j],
temp_list[i][-1]
])
# for item in headcount_upload_list:
# print (item);
return headcount_upload_list
def read_manual_input(full_file_path, company, scenario):
respreads_data_df = pd.read_excel(full_file_path, header=0)
print(len(respreads_data_df))
input_date = date_utils.get_dates_info_from_amr_scenario(scenario)[0][-1]
respreads_upload_list = []
"""
account
"""
print(input_date)
for item in respreads_data_df:
if isinstance(item, datetime.datetime):
if str(item).split(" ")[0] > input_date:
# print (item);
# value_list.append(list(respreads_data_df[item]));
for i in range(0, len(respreads_data_df)):
respreads_upload_list.append([company] + [
list(respreads_data_df.iloc[i][['Entity', 'FSLI']])[0]
] + [scenario] + [
list(respreads_data_df.iloc[i][['Entity', 'FSLI']])[1]
] + [
str(item).split(" ")[0],
str(respreads_data_df[item].iloc[i])
])
for item in respreads_upload_list:
print(item)
print(len(respreads_upload_list))
return respreads_upload_list
def read_respreads_input(full_file_path, company, scenario):
respreads_data_df = pd.read_excel(full_file_path, header = 0);
input_date = date_utils.get_dates_info_from_amr_scenario(scenario)[0][-1];
respreads_upload_list = [];
for item in respreads_data_df:
if isinstance(item, datetime.datetime):
if str(item).split(" ")[0] > input_date:
for i in range(0, len(respreads_data_df)):
temp_info_list = list(respreads_data_df.iloc[i][['Plant','Account No','Account Title',\
'Model group','Project ID','Cost Component ID','Work Order Number','Outage Code ID',\
'Invoice ID', 'Contract Number ID','Reference number','Cost Category',\
'Cost Sub Category','Project Name']])
for j in range(0, len(temp_info_list)):
if str(temp_info_list[j]) == 'nan':
temp_info_list[j] = '';
respreads_upload_list.append([company, scenario] + temp_info_list + [str(item).split(" ")[0],str(respreads_data_df[item].iloc[i]) if str(respreads_data_df[item].iloc[i]) != 'nan' else 0.0]);
# for item in respreads_upload_list:
# print (item);
return respreads_upload_list;
def draw_var_holdco(month_header, actest_result_df, budget_result_df,
selected_worksheet):
selected_worksheet.sheet_view.showGridLines = False
holdco_fsli_item_dict = {
'Lightstone': [
'Hedge P&L', 'Arctos', 'Kindle Energy (AMA fees)',
'Nextera (EMA fees)', 'CAMS (OMA fees)', 'Transition Costs',
'General & Administrative'
]
}
current_company = actest_result_df.iloc[0]['company']
current_scenario = actest_result_df.iloc[0]['scenario']
current_month_date = date_utils.get_dates_info_from_amr_scenario(
current_scenario)[0][-1]
print(current_month_date)
current_month_date_obj = datetime.date(
int(current_month_date.split("-")[0]),
int(current_month_date.split("-")[1]),
int(current_month_date.split("-")[2]))
fsli_item_list = holdco_fsli_item_dict[current_company]
holdco_actual_result_df = actest_result_df.loc[
(actest_result_df['account'].isin(fsli_item_list))
& (actest_result_df['entity'] == 'HoldCo')]
holdco_budget_result_df = budget_result_df.loc[
(budget_result_df['account'].isin(fsli_item_list))
& (budget_result_df['entity'] == 'HoldCo')]
# for i in range(0, len(holdco_actual_result_df)):
# print (holdco_actual_result_df.iloc[i]);
#
# for i in range(0, len(holdco_budget_result_df)):
# print (holdco_budget_result_df.iloc[i]);
# print (month_header);
for row_number in range(2, 10):
if row_number == 2:
selected_worksheet.cell(
row=row_number,
column=3).value = "HoldCo " + month_header + " MTD"
selected_worksheet.cell(
row=row_number,
column=6).value = "HoldCo " + month_header + " YTD"
continue
if row_number == 3:
selected_worksheet.cell(row=row_number, column=3).value = "Actual"
selected_worksheet.cell(row=row_number, column=4).value = 'Budget'
selected_worksheet.cell(row=row_number,
column=5).value = 'Variance'
continue
account_title = selected_worksheet.cell(row=row_number, column=1).value
# print ("-----------------------", account_title);
if account_title != 'Hedge P&L':
selected_worksheet.cell(
row=row_number, column=3).value = holdco_actual_result_df.loc[
(holdco_actual_result_df['entity'] == 'HoldCo')
& (holdco_actual_result_df['account'] == account_title) &
(holdco_actual_result_df['period']
== current_month_date_obj)].iloc[0]['value'] / -1000.0
else:
selected_worksheet.cell(
row=row_number, column=3).value = holdco_actual_result_df.loc[
(holdco_actual_result_df['entity'] == 'HoldCo')
& (holdco_actual_result_df['account'] == account_title) &
(holdco_actual_result_df['period']
== current_month_date_obj)].iloc[0]['value'] / 1000.0
selected_worksheet.cell(
row=row_number, column=4).value = holdco_budget_result_df.loc[
(holdco_budget_result_df['entity'] == 'HoldCo')
& (holdco_budget_result_df['account'] == account_title) &
(holdco_budget_result_df['period']
== current_month_date_obj)].iloc[0]['value'] / 1000.0
if account_title != 'Hedge P&L':
selected_worksheet.cell(row=row_number, column=6).value = sum(
list(holdco_actual_result_df.loc[
(holdco_actual_result_df['entity'] == 'HoldCo')
& (holdco_actual_result_df['account'] == account_title) &
(holdco_actual_result_df['period'] <=
current_month_date_obj)]['value'])) / -1000.0
else:
selected_worksheet.cell(row=row_number, column=6).value = sum(
list(holdco_actual_result_df.loc[
(holdco_actual_result_df['entity'] == 'HoldCo')
& (holdco_actual_result_df['account'] == account_title) &
(holdco_actual_result_df['period'] <=
current_month_date_obj)]['value'])) / 1000.0
selected_worksheet.cell(row=row_number, column=7).value = sum(
list(holdco_budget_result_df.loc[
(holdco_budget_result_df['entity'] == 'HoldCo')
& (holdco_budget_result_df['account'] == account_title) &
(holdco_budget_result_df['period'] <= current_month_date_obj)]
['value'])) / 1000.0
def process_labor(conn_ins, scenario, company):
census_df = db_controller_labor.get_census(conn_ins, scenario, company)
headcount_df = db_controller_labor.get_headcount(conn_ins, scenario,
company)
print(len(census_df))
print(len(headcount_df))
entity_name_list = COMPANY_ENTITY_MAPPING[company]
estimate_month_dates = date_utils.get_dates_info_from_amr_scenario(
scenario)[2]
current_year = int(scenario.split(" ")[0])
year_range = list(range(current_year + 1, current_year + 5))
forecast_month_dates_list = []
for temp_year in year_range:
forecast_month_dates_list += date_utils.get_month_dates_list(
temp_year, 1, 12)
# print (estimate_month_dates);
estimate_month_dates = estimate_month_dates + forecast_month_dates_list
# for item in estimate_month_dates:
# print (item);
upload_to_financials_labor_expense_list = []
support_document_labor_expense_list = []
for entity in entity_name_list:
entity_census_df = census_df.loc[
census_df['default_department'].str.contains(entity.upper())]
entity_headcount_df = headcount_df.loc[headcount_df['entity'] ==
entity]
print(len(entity_census_df))
print(len(entity_headcount_df))
for period in estimate_month_dates:
# print (int(datetime.datetime.strptime(period,"%Y-%m-%d").date()));
period_entity_headcount_df = entity_headcount_df.loc[
entity_headcount_df['period'] == datetime.datetime.strptime(
period, "%Y-%m-%d").date()]
# print (len(period_entity_headcount_df));
headcount = period_entity_headcount_df.iloc[0]['headcount']
payroll_cycles = period_entity_headcount_df.iloc[0][
'payroll_cycles']
basesalary = (sum(list(entity_census_df['salary'])) /
(len(entity_census_df))) * headcount * (
payroll_cycles / PAY_CYCLES_ANNUAL)
if entity == 'Gavin' and int(period.split("-")[0]) > 2018:
basesalary = basesalary * 1.025**(int(period.split("-")[0]) -
2018)
if entity != 'Gavin' and int(period.split("-")[0]) > 2017:
basesalary = basesalary * 1.025**(int(period.split("-")[0]) -
2017)
incentive = basesalary * INCENTIVE_PERCENTAGE
fringe = basesalary * FRINGE_PERCENTAGE
overtime = basesalary * OVERTIME_PERCENTAGE
ldw_credit = 0.0
severence = 0.0
retention = 0.0
retiree_medical = 0.0
if entity in ASSUMPTIONS_VALUE_DICT:
if 'ldw_credit' in ASSUMPTIONS_VALUE_DICT[entity]:
ldw_credit = [
item[1] for item in ASSUMPTIONS_VALUE_DICT[entity]
['ldw_credit'] if item[0] == period
][0] if [
item[1] for item in ASSUMPTIONS_VALUE_DICT[entity]
['ldw_credit'] if item[0] == period
] != [] else ldw_credit
if 'severence' in ASSUMPTIONS_VALUE_DICT[entity]:
severence = [
item[1]
for item in ASSUMPTIONS_VALUE_DICT[entity]['severence']
if item[0] == period
][0] if [
item[1]
for item in ASSUMPTIONS_VALUE_DICT[entity]['severence']
if item[0] == period
] != [] else severence
if 'retention' in ASSUMPTIONS_VALUE_DICT[entity]:
retention = [
item[1]
for item in ASSUMPTIONS_VALUE_DICT[entity]['retention']
if item[0] == period
][0] if [
item[1]
for item in ASSUMPTIONS_VALUE_DICT[entity]['retention']
if item[0] == period
] != [] else retention
if 'retiree_medical' in ASSUMPTIONS_VALUE_DICT[entity]:
retiree_medical = [
item[1] for item in ASSUMPTIONS_VALUE_DICT[entity]
['retiree_medical'] if item[0] == period
][0] if [
item[1] for item in ASSUMPTIONS_VALUE_DICT[entity]
['retiree_medical'] if item[0] == period
] != [] else retiree_medical
# print (entity, ldw_credit, severence, retention, retiree_medical);
# print (entity, period, basesalary, incentive, fringe, overtime, ldw_credit, severence, retention, retiree_medical);
support_document_labor_expense_list.append([
entity, period, basesalary, incentive, fringe, overtime,
ldw_credit, severence, retention, retiree_medical, headcount,
payroll_cycles
])
"""
(company, entity, scenario, account, period, value, version)
"""
labor_expenses = sum([
basesalary, incentive, fringe, overtime, ldw_credit, severence,
retention, retiree_medical
])
upload_to_financials_labor_expense_list.append([
company, entity, scenario, 'Labor Expenses', period,
labor_expenses
])
# for item in support_document_labor_expense_list:
# print (item);
# print ("=======================================================");
#
# for item in upload_to_financials_labor_expense_list:
# print (item);
return upload_to_financials_labor_expense_list, support_document_labor_expense_list
def fill_in_cells_labor(new_file_path, support_document_labor_expense_list,
scenario, company):
actuals_month_dates_list, allyear_month_dates_list, estimate_month_dates_list = date_utils.get_dates_info_from_amr_scenario(
scenario)
entity_name_list = COMPANY_ENTITY_MAP[company]
wb = load_workbook(new_file_path)
for entity in entity_name_list:
selected_worksheet = wb.copy_worksheet(wb.active)
selected_worksheet.title = entity
selected_worksheet['K1'] = scenario
entity_support_list = [
item for item in support_document_labor_expense_list
if item[0] == entity
]
print(len(entity_support_list))
for i in range(0, len(allyear_month_dates_list)):
period = allyear_month_dates_list[i]
selected_worksheet.cell(
row=3,
column=i + 2).value = date_utils.calc_forecast_monthly_headers(
int(period.split("-")[0]), int(period.split("-")[1]))
img = openpyxl.drawing.image.Image(LOGO_PATH)
img.anchor(selected_worksheet['A1'])
selected_worksheet.add_image(img)
# print (period);
if period in actuals_month_dates_list:
continue
else:
period_entity_support_list = [
item for item in entity_support_list if item[1] == period
][0]
"""
[entity, period, basesalary, incentive, fringe, overtime, ldw_credit, severence, retention, retiree_medical, headcount, payroll_cycles]
"""
selected_worksheet.cell(
row=5, column=i + 2).value = period_entity_support_list[-2]
selected_worksheet.cell(
row=6, column=i + 2).value = period_entity_support_list[-1]
selected_worksheet.cell(
row=9, column=i + 2).value = period_entity_support_list[2]
selected_worksheet.cell(
row=10, column=i + 2).value = period_entity_support_list[3]
selected_worksheet.cell(
row=11, column=i + 2).value = period_entity_support_list[4]
selected_worksheet.cell(
row=12, column=i + 2).value = period_entity_support_list[5]
selected_worksheet.cell(
row=15, column=i + 2).value = period_entity_support_list[6]
selected_worksheet.cell(
row=16, column=i + 2).value = period_entity_support_list[7]
selected_worksheet.cell(
row=17, column=i + 2).value = period_entity_support_list[8]
selected_worksheet.cell(
row=18, column=i + 2).value = period_entity_support_list[9]
wb.remove_sheet(wb.active)
wb.save(new_file_path)
def respreads_main_details(conn_ins, company, amr_scenario, budget_scenario,
respreads_period_range_list):
""" step 1: prepare data """
budget_df = db_controller_budget.get_budget_data(
conn_ins, company, budget_scenario, respreads_period_range_list)
respreads_df = db_controller_respreads.get_respreads(
conn_ins, company, amr_scenario)
actuals_df = db_controller_actuals.get_actuals(conn_ins, company,
amr_scenario)
actuals_month_dates_list, budget_month_dates_list, estimate_month_dates_list = date_utils.get_dates_info_from_amr_scenario(
amr_scenario)
actuals_start_date = datetime.datetime.strptime(
actuals_month_dates_list[0], '%Y-%m-%d').date()
actuals_end_date = datetime.datetime.strptime(actuals_month_dates_list[-1],
'%Y-%m-%d').date()
estimate_start_date = datetime.datetime.strptime(
estimate_month_dates_list[0], '%Y-%m-%d').date()
estimate_end_date = datetime.datetime.strptime(
estimate_month_dates_list[-1], '%Y-%m-%d').date()
""" step 2: get ytd variance view """
actual_plant_id_list = list(set(list(actuals_df['plant_id'])))
# print (actual_plant_id_list);
actuals_plant_fsli_account_list = []
for plant_id in actual_plant_id_list:
fsli_mapping_df = db_controller_budget.get_tb_mapping_company(
conn_ins, company, estimate_month_dates_list[0], plant_id)
for fsli in fsli_mapping_df.groups:
fsli_account_mapping_df = fsli_mapping_df.get_group(fsli)
for i in range(0, len(fsli_account_mapping_df)):
actuals_plant_fsli_account_list.append(
list(fsli_account_mapping_df.iloc[i][[
'plant_id', 'fsli', 'account'
]]))
actuals_ytd_list = []
for item in actuals_plant_fsli_account_list:
# print (item);
actuals_plant_fsli_account_project_df = actuals_df.loc[
(actuals_df['plant_id'] == item[0])
& (actuals_df['account'] == item[2])]
grouped_actuals_plant_fsli_account_project_df = actuals_plant_fsli_account_project_df.groupby(
['account', 'project_id'])
for account_project_group in grouped_actuals_plant_fsli_account_project_df.groups:
account_project_df = grouped_actuals_plant_fsli_account_project_df.get_group(
account_project_group)
if 'C' != account_project_group[0][0]:
ytd_actuals_value = sum(
list(account_project_df['total_credit'])) - sum(
list(account_project_df['total_debit']))
else:
ytd_actuals_value = -sum(
list(account_project_df['period_balance']))
actuals_ytd_list.append([
'Actual', item[0], item[0], item[1], account_project_group[0],
account_project_group[1], ytd_actuals_value
])
print("Actuals plant fsli account list length: ",
len(actuals_plant_fsli_account_list))
# for item in actuals_ytd_list:
# print (item);
print("Actuals YTD list length: ", len(actuals_ytd_list))
# sys.exit();
budget_entity_fsli_account_list = []
budget_entity_list = list(set(list(budget_df['entity'])))
budget_plant_id_list = [[
db_controller_budget.get_entity_plant_id(conn_ins, item), item
] for item in budget_entity_list]
# print (budget_plant_id_list);
budget_plant_fsli_account_list = []
for plant_id in budget_plant_id_list:
# print (plant_id);
fsli_mapping_df = db_controller_budget.get_tb_mapping_company(
conn_ins, company, estimate_month_dates_list[0], plant_id[0])
for fsli in fsli_mapping_df.groups:
fsli_account_mapping_df = fsli_mapping_df.get_group(fsli)
for i in range(0, len(fsli_account_mapping_df)):
budget_plant_fsli_account_list.append(
[plant_id[1]] + list(fsli_account_mapping_df.iloc[i]
[['plant_id', 'fsli', 'account']]))
print("Budget plant fsli account list length: ",
len(budget_plant_fsli_account_list))
# for item in budget_plant_fsli_account_list:
# print (item);
budget_ytd_list = []
for item in budget_plant_fsli_account_list:
# print (item);
budget_plant_fsli_account_project_df = budget_df.loc[
(budget_df['entity'] == item[0])
& (budget_df['account'] == item[3])]
grouped_budget_plant_fsli_account_project_df = budget_plant_fsli_account_project_df.groupby(
['account', 'project_id'])
for account_project_group in grouped_budget_plant_fsli_account_project_df.groups:
account_project_df = grouped_budget_plant_fsli_account_project_df.get_group(
account_project_group)
ytd_budget_value = sum(
list(account_project_df.loc[account_project_df['period'] <=
actuals_end_date]['value']))
budget_ytd_list.append([
'Budget', item[0], item[1], item[2], account_project_group[0],
account_project_group[1], ytd_budget_value
])
print("Budget plant fsli account list length: ",
len(budget_plant_fsli_account_list))
# for item in budget_ytd_list:
# print (item);
print("Budget YTD list length: ", len(budget_ytd_list))
ytd_result_list = actuals_ytd_list + budget_ytd_list
ytd_df = pd.DataFrame(data=ytd_result_list,
columns=[
'source', 'entity', 'plant_id', 'fsli',
'account', 'project_id', 'ytd_value'
])
# ytd_df.to_csv('ytd_df.csv');
ytd_variance_list = []
for actual_item in actuals_ytd_list:
flag = 0
for budget_item in budget_ytd_list:
# print (actual_item[2:6]);
if actual_item[2:6] == budget_item[2:6]:
ytd_variance_list.append(actual_item[2:6] + [
actual_item[6], budget_item[6], actual_item[6] -
budget_item[6]
] + ['acc-proj combo exists in both Actual and Budget'])
flag = 1
if flag == 0:
""" we have actuals but no budget """
ytd_variance_list.append(actual_item[2:6] + [
actual_item[6], 0, actual_item[6] -
0, 'acc-proj combo only exists in actual'
])
for budget_item in budget_ytd_list:
flag = 0
for actual_item in actuals_ytd_list:
if actual_item[2:6] == budget_item[2:6]:
flag = 1
if flag == 0:
""" we have budget but no actuals """
ytd_variance_list.append(budget_item[2:6] + [
0, budget_item[6], 0 -
budget_item[6], 'acc-proj combo only exists in budget'
])
print(len(ytd_variance_list))
ytd_variance_list = sorted(ytd_variance_list,
key=lambda x: (x[0], x[1], x[2], x[3]))
ytd_variance_df = pd.DataFrame(data=ytd_variance_list,
columns=[
'entity', 'fsli', 'account', 'project',
'ytd actual', 'ytd budget',
'ytd variance', 'note'
])
ytd_variance_df.to_csv('ytd_variance_df.csv')
""" step 3: get targeted respreads """
respreads_entity_list = list(set(list(respreads_df['entity'])))
# print ("=================================");
# print (respreads_entity_list);
# print ("=================================");
respreads_plant_id_list = [[
db_controller_budget.get_entity_plant_id(conn_ins, item), item
] for item in respreads_entity_list]
print(respreads_plant_id_list)
respreads_plant_fsli_account_list = []
for plant_id in respreads_plant_id_list:
# print (plant_id);
fsli_mapping_df = db_controller_budget.get_tb_mapping_company(
conn_ins, company, estimate_month_dates_list[0], plant_id[0])
for fsli in fsli_mapping_df.groups:
fsli_account_mapping_df = fsli_mapping_df.get_group(fsli)
for i in range(0, len(fsli_account_mapping_df)):
respreads_plant_fsli_account_list.append(
[plant_id[1]] + list(fsli_account_mapping_df.iloc[i]
[['plant_id', 'fsli', 'account']]))
print("Respreads plant fsli account list length: ",
len(respreads_plant_fsli_account_list))
# for item in respreads_plant_fsli_account_list:
# print (item);
# for item in budget_ytd_list:
# print (item);
targeted_respreads_list = []
for i in range(0, len(respreads_df)):
current_entity = respreads_df.iloc[i]['entity']
current_account = respreads_df.iloc[i]['account']
current_project_id = respreads_df.iloc[i]['project_id']
current_period = respreads_df.iloc[i]['period']
current_value = respreads_df.iloc[i]['value']
current_plant_fsli = [
item[1:3] for item in respreads_plant_fsli_account_list
if item[0] == current_entity and item[3] == current_account
][0]
current_budget = 0.0
current_actuals = 0.0
current_budget_df = budget_df.loc[
(budget_df['period'] == current_period)
& (budget_df['entity'] == current_entity) &
(budget_df['account'] == current_account) &
(budget_df['project_id'] == current_project_id)]
if not current_budget_df.empty:
current_budget = current_budget_df.iloc[0]['value']
targeted_respreads_list.append([
current_entity, current_plant_fsli[0], current_plant_fsli[1],
current_account, current_project_id, current_period, current_value,
current_budget, current_budget + current_value
])
print("Targeted respreades length: ", len(targeted_respreads_list))
targeted_respreads_list = sorted(targeted_respreads_list,
key=lambda x:
(x[0], x[2], x[3], x[4], x[5]))
targeted_respreads_df = pd.DataFrame(data=targeted_respreads_list,
columns=[
'entity', 'plant_id', 'fsli',
'account', 'project', 'period',
'targeted respreads value',
'budget', 'budget + target resp'
])
targeted_respreads_df.to_csv("targeted_respreads_df.csv")
""" step 4: get peanut butter respreads view """
level_respreads_list = []
plant_id_entity_dict = {
'DBY001': 'Darby',
'LMC001': 'HoldCo',
'LSHLD001': 'HoldCo',
'WAT001': 'Waterford',
'LWS001': 'Lawrenceburg',
'GPW001': 'Gavin'
}
for ytd_var_item in ytd_variance_list:
flag = 0
for targeted_item in targeted_respreads_list:
""" if the acc-proj combo already exists in targeted respreads list, we don't do any peanut butter respreads on that """
target_project_id = targeted_item[
4] if targeted_item[4] != 'nan' else ''
if ytd_var_item[0] == targeted_item[1] and ytd_var_item[
1] == targeted_item[2] and ytd_var_item[2] == targeted_item[
3] and ytd_var_item[3] == target_project_id:
flag = 1
if flag == 0:
pb_respreads_value = ytd_var_item[6] / len(
estimate_month_dates_list)
for estimate_date in estimate_month_dates_list:
estimate_date_obj = datetime.datetime.strptime(
estimate_date, "%Y-%m-%d").date()
# print (plant_id_entity_dict[ytd_var_item[0]], ytd_var_item[2], ytd_var_item[3],estimate_date_obj);
current_budget_df = budget_df.loc[
(budget_df['entity'] == plant_id_entity_dict[
ytd_var_item[0]])
& (budget_df['account'] == ytd_var_item[2]) &
(budget_df['project_id'] == ytd_var_item[3]) &
(budget_df['period'] == estimate_date_obj)]
# print (len(current_budget_df));
# sys.exit();
current_value = 0.0
note = 'acc-proj combo doenst exist in budget, need addition'
if not current_budget_df.empty:
current_value = current_budget_df.iloc[0]['value']
note = 'acc-proj combo exists in budget'
level_respreads_list.append(
[plant_id_entity_dict[ytd_var_item[0]]] +
ytd_var_item[0:4] + [
str(estimate_date_obj), current_value,
pb_respreads_value, current_value +
pb_respreads_value, note
])
# for item in level_respreads_list:
# print (item);
level_respreads_list = sorted(level_respreads_list,
key=lambda x: (x[0], x[1], x[2], x[3], x[4]))
level_respreads_df = pd.DataFrame(data=level_respreads_list,
columns=[
'entity', 'plant_id', 'fsli',
'account', 'project', 'period',
'budget', 'pb respreads',
'budget + pb respreads', 'note'
])
level_respreads_df.to_csv("level_respreads_df.csv")
""" step 5: get a summurized projects detail view of peanut butter respreads and targeted respreads """
respreads_summary_list = []
for item in targeted_respreads_list:
current_project_id = item[4] if item[4] != 'nan' else ''
respreads_summary_list.append(item[0:4] + [current_project_id] +
[item[5]] + [item[7]] + [item[6]] +
[item[8]] + ['targeted respreads'])
for item in level_respreads_list:
respreads_summary_list.append(item[0:6] + [item[6]] + [item[7]] +
[item[8]] + ['peanut butter respreads'])
respreads_summary_df = pd.DataFrame(data=respreads_summary_list,
columns=[
'entity', 'plant_id', 'fsli',
'account', 'project', 'period',
'budget', 'adj', 'forecast', 'type'
])
print("Respreads summary list length: ", len(respreads_summary_list))
respreads_summary_df.to_csv("respreads_summary_df.csv")
add_budget_list = []
id_start = budget_df['id_budget'].max() + 1
for respreads_item in respreads_summary_list:
current_period_obj = datetime.datetime.strptime(
respreads_item[5], '%Y-%m-%d').date() if isinstance(
respreads_item[5], str) else respreads_item[5]
# print (current_period_obj, type(current_period_obj));
adj_budget_df = budget_df.loc[
(budget_df['entity'] == respreads_item[0])
& (budget_df['account'] == respreads_item[3]) &
(budget_df['project_id'] == respreads_item[4]) &
(budget_df['period'] == current_period_obj)]
if not adj_budget_df.empty:
current_id_in_budget = adj_budget_df.iloc[0]['id_budget']
current_value = adj_budget_df.iloc[0]['value']
if 'targeted' in respreads_item[-1]:
budget_df.loc[budget_df.id_budget == current_id_in_budget,
'value'] = current_value + respreads_item[7]
else:
budget_df.loc[budget_df.id_budget == current_id_in_budget,
'value'] = current_value - respreads_item[7]
else:
if 'targeted' in respreads_item[-1]:
add_budget_list.append([
id_start, company, budget_scenario, respreads_item[0],
respreads_item[3], '', current_period_obj,
respreads_item[7], '', respreads_item[4], '', '', '', '',
'', '', '', '', ''
])
else:
add_budget_list.append([
id_start, company, budget_scenario, respreads_item[0],
respreads_item[3], '', current_period_obj,
-respreads_item[7], '', respreads_item[4], '', '', '', '',
'', '', '', '', ''
])
id_start += 1
# budget_df.loc[budget_df.id_budget == current_id_in_budget,'value'] = new_value;
add_budget_df = pd.DataFrame(data=add_budget_list,
columns=budget_df.columns)
print('before:', len(budget_df))
budget_df = budget_df.append(add_budget_df)
print('after:', len(budget_df))
budget_df.to_csv('temp_budget.csv')
# sys.exit();
respreads_financial_upload_list = get_budget_financials_respreads(
conn_ins, budget_df, estimate_month_dates_list, company, amr_scenario)
for item in respreads_financial_upload_list:
print(item)
db_controller.upload_cal_results_to_financials(
conn_ins, respreads_financial_upload_list, company, amr_scenario)
def read_direct_input(full_file_path, company, scenario):
upload_to_financials_list = []
direct_data_df = pd.read_excel(full_file_path, header=0)
# print (len(direct_data_df));
actuals_month_dates_list, fullyear_month_dates_list, estimate_month_dates_list = date_utils.get_dates_info_from_amr_scenario(
scenario)
# for item in direct_data_df:
# print (item);
# print (direct_data_df.iloc[0][0])
# print (direct_data_df.iloc[0][2])
# print (direct_data_df.iloc[2][0])
row_number = 0
col_number = 0
while True:
if estimate_month_dates_list[0] in str(
direct_data_df.iloc[row_number][col_number]):
# print (row_number, col_number, direct_data_df.iloc[row_number][col_number]);
break
col_number += 1
col_number_list = range(col_number,
col_number + len(estimate_month_dates_list))
# for item in col_number_list:
# print (item);
col_number = 0
row_number = 0
while True:
if 'LOAD TO FORECAST' in str(
direct_data_df.iloc[row_number][col_number]):
# print (row_number, col_number, direct_data_df.iloc[row_number][col_number]);
break
row_number += 1
row_number = row_number + 1
insurance_row = []
entity_list = []
for row in range(row_number, row_number + 4):
# print (str(direct_data_df.iloc[row][3]));
entity = str(direct_data_df.iloc[row][3]).strip()
entity_list.append(entity)
insurance_row.append(row)
# print (col_number_list);
# print (entity_list);
# print (insurance_row);
for i in range(0, len(entity_list)):
entity = entity_list[i]
for col in col_number_list:
# print (entity, str(direct_data_df.iloc[0][col]).split(" ")[0],str(direct_data_df.iloc[insurance_row[i]][col]));
"""
(company, entity, scenario, account, period, value, version)
"""
upload_to_financials_list.append([
company, entity, scenario, 'Insurance',
str(direct_data_df.iloc[0][col]).split(" ")[0],
str(direct_data_df.iloc[insurance_row[i]][col])
])
###################################################################################
###################################################################################
###################################################################################
direct_data_df = pd.read_excel(full_file_path, sheetname=1, header=0)
row_number = 0
while True:
print(row_number)
if 'Property Taxes' in str(direct_data_df.iloc[row_number][2]):
# print (row_number, 0, direct_data_df.iloc[row_number][2]);
break
row_number += 1
row_number = row_number - 1
print(row_number)
col_number = 0
while True:
# print (col_number);
if estimate_month_dates_list[0] in str(
direct_data_df.iloc[row_number][col_number]):
# print (row_number, col_number, direct_data_df.iloc[row_number][col_number]);
break
col_number += 1
col_number_list = range(col_number,
col_number + len(estimate_month_dates_list))
# print (col_number_list);
for row in range(row_number + 1, row_number + 5):
entity = str(direct_data_df.iloc[row][3])
# print (entity);
for i in range(0, len(col_number_list)):
col = col_number_list[i]
"""
(company, entity, scenario, account, period, value, version)
"""
value = str(abs(float(str(direct_data_df.iloc[row][col]))))
upload_to_financials_list.append([
company, entity, scenario, 'Property Tax',
estimate_month_dates_list[i], value
])
# print ([company, entity, scenario, 'Property Tax', estimate_month_dates_list[i],value]);
###################################################################################
###################################################################################
###################################################################################
direct_data_df = pd.read_excel(full_file_path, sheetname=2, header=0)
row_number = 0
while True:
print(row_number)
if 'Other Income' in str(direct_data_df.iloc[row_number][2]):
# print (row_number, 0, direct_data_df.iloc[row_number][2]);
break
row_number += 1
row_number = row_number - 1
print(row_number)
col_number = 0
while True:
# print (col_number);
if estimate_month_dates_list[0] in str(
direct_data_df.iloc[row_number][col_number]):
# print (row_number, col_number, direct_data_df.iloc[row_number][col_number]);
break
col_number += 1
col_number_list = range(col_number,
col_number + len(estimate_month_dates_list))
print(col_number_list)
for row in range(row_number + 1, row_number + 2):
entity = str(direct_data_df.iloc[row][3])
# print (entity);
for i in range(0, len(col_number_list)):
col = col_number_list[i]
"""
(company, entity, scenario, account, period, value, version)
"""
value = str(abs(float(str(direct_data_df.iloc[row][col]))))
upload_to_financials_list.append([
company, entity, scenario, 'Misc Income',
estimate_month_dates_list[i], value
])
# print ([company, entity, scenario, 'Misc Income', estimate_month_dates_list[i],value]);
upload_to_financials_list += hardcode_fixed_fuel()
return upload_to_financials_list