from dataclasses import replace
from typing import final
from ams.db import select_data, insert_df, sql_execute
import pandas as pd
import numpy as np
import datetime
import os
from sqlalchemy import false
connection_string = os.environ['DB_A']
# Read from database and save file
# def my_ceil(a, precision=0):
# return np.true_divide(np.ceil(a * 10**precision), 10**precision)
# def my_floor(a, precision=0):
# return np.true_divide(np.floor(a * 10**precision), 10**precision)
saveme_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
sizecheck_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
# checkme_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '31-MAY-22' AND ACCT_TYPE = 'Constrained'""")
saveme_df.to_csv('C:/Users/cvanwert/Documents/ams_axioma_weights_rebal.csv', index=False)
# saveme_df = pd.read_csv('C:/Users/cvanwert/Documents/ams_axioma_weights_rebal.csv')
saveme_df['PERCENTILE'] = saveme_df.PERCENTILE.astype(float)
saveme_df['PERCENTILE_PRE'] = saveme_df['PERCENTILE'].apply(lambda x: np.true_divide(np.floor(x * 10**4), 10**4))
saveme_df['PERCENTILE_FIX'] = saveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.ceil(x * 10**2), 10**2))
# saveme_df[saveme_df['PERCENTILE'] == 1.1].PERCENTILE_FIX.unique()
saveme_df.loc[saveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
saveme_df.loc[saveme_df.PERCENTILE == 1.09, 'PERCENTILE_FIX'] = 1.09
saveme_df.loc[saveme_df.PERCENTILE == 1.08, 'PERCENTILE_FIX'] = 1.08
saveme_df.loc[saveme_df.PERCENTILE == 1.07, 'PERCENTILE_FIX'] = 1.07
saveme_df.loc[saveme_df.PERCENTILE == 1.06, 'PERCENTILE_FIX'] = 1.06
saveme_df.loc[saveme_df.PERCENTILE == 1.05, 'PERCENTILE_FIX'] = 1.05
saveme_df.loc[saveme_df.PERCENTILE == 1.04, 'PERCENTILE_FIX'] = 1.04
saveme_df.loc[saveme_df.PERCENTILE == 1.03, 'PERCENTILE_FIX'] = 1.03
saveme_df.loc[saveme_df.PERCENTILE == 1.02, 'PERCENTILE_FIX'] = 1.02
saveme_df.loc[saveme_df.PERCENTILE == 1.01, 'PERCENTILE_FIX'] = 1.01
saveme_df.loc[saveme_df.PERCENTILE == 1., 'PERCENTILE_FIX'] = 1.
saveme_df.loc[saveme_df.PERCENTILE == 0.99, 'PERCENTILE_FIX'] = 0.99
saveme_df.loc[saveme_df.PERCENTILE == 0.98, 'PERCENTILE_FIX'] = 0.98
saveme_df.loc[saveme_df.PERCENTILE == 0.97, 'PERCENTILE_FIX'] = 0.97
saveme_df.loc[saveme_df.PERCENTILE == 0.96, 'PERCENTILE_FIX'] = 0.96
saveme_df.loc[saveme_df.PERCENTILE == 0.95, 'PERCENTILE_FIX'] = 0.95
saveme_df.loc[saveme_df.PERCENTILE == 0.94, 'PERCENTILE_FIX'] = 0.94
saveme_df.loc[saveme_df.PERCENTILE == 0.93, 'PERCENTILE_FIX'] = 0.93
saveme_df.loc[saveme_df.PERCENTILE == 0.92, 'PERCENTILE_FIX'] = 0.92
saveme_df.loc[saveme_df.PERCENTILE == 0.91, 'PERCENTILE_FIX'] = 0.91
saveme_df.loc[saveme_df.PERCENTILE == 0.9, 'PERCENTILE_FIX'] = 0.9
# saveme_df.loc[(saveme_df.PERCENTILE == 0.96015) & (saveme_df.MODEL_CODE == 'E2GR'), 'PERCENTILE_FIX'] = 0.96
saveme_df['PERCENTILE_TEMP'] = saveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
saveme_df['ACCT_ID_FIX'] = 'CON' + saveme_df['PERCENTILE_TEMP'].astype(str)
dupe_check_df = pd.concat(g for _, g in saveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","ASSET_CLASS"]) if len(g) > 1)
Index_array = dupe_check_df.index
saveme_df.loc[Index_array, 'PERCENTILE_FIX'] = saveme_df.loc[Index_array, 'PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
saveme_df['PERCENTILE_TEMP'] = saveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
saveme_df['ACCT_ID_FIX'] = 'CON' + saveme_df['PERCENTILE_TEMP'].astype(str)
dupe_check_df = pd.concat(g for _, g in saveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","ASSET_CLASS"]) if len(g) > 1)
# saveme_df.loc[(saveme_df.PERCENTILE == dupe_check_df.PERCENTILE) & (saveme_df.MODEL_CODE == dupe_check_df.MODEL_CODE), 'PERCENTILE_FIX'] = saveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
# dupe_check_df.to_csv('C:/Users/cvanwert/Documents/dupes_ams_axioma_weights_rebal.csv')
saveme_df = saveme_df.drop(columns=['PERCENTILE', 'ACCT_ID'])
saveme_df= saveme_df.rename(columns={"PERCENTILE_FIX":"PERCENTILE", "ACCT_ID_FIX":"ACCT_ID"})
saveme_df = saveme_df.drop(columns=['PERCENTILE_TEMP'])
saveme_df = saveme_df.drop(columns=['PERCENTILE_PRE'])
saveme_df = saveme_df[['RUN_DATE', 'MODEL_CODE', 'ACCT_TYPE', 'ACCT_ID', 'PERCENTILE', 'ASSET_CLASS', 'WEIGHT']]
saveme_df['ACCT_ID'] = saveme_df.ACCT_ID.astype(str)
#Insert into database
saveme_df['PERCENTILE'] = saveme_df.PERCENTILE.astype(float)
saveme_df['WEIGHT'] = saveme_df.WEIGHT.astype(float)
saveme_df['RUN_DATE'] = pd.to_datetime(saveme_df.RUN_DATE)
# Delete Raw Returns Run
sql_execute(connection_string, f"""DELETE FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
insert_df(connection_string,saveme_df,'ams_axioma_weights_rebal')
# Stats to match
statssaveme_df = select_data(connection_string, """SELECT * FROM AMS_AXIOMA_STATS_REBAL WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
# statssaveme_df = pd.read_csv('C:/Users/cvanwert/Documents/ams_axioma_stats_rebal.csv')
statssaveme_df.to_csv('C:/Users/cvanwert/Documents/ams_axioma_stats_rebal.csv', index=False)
statssaveme_df['PERCENTILE'] = statssaveme_df.PERCENTILE.astype(float)
statssaveme_df['PERCENTILE_PRE'] = statssaveme_df['PERCENTILE'].apply(lambda x: np.true_divide(np.floor(x * 10**4), 10**4))
statssaveme_df['PERCENTILE_FIX'] = statssaveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.ceil(x * 10**2), 10**2))
statssaveme_df[statssaveme_df['PERCENTILE'] == 1.1].PERCENTILE_FIX.unique()
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.09, 'PERCENTILE_FIX'] = 1.09
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.08, 'PERCENTILE_FIX'] = 1.08
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.07, 'PERCENTILE_FIX'] = 1.07
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.06, 'PERCENTILE_FIX'] = 1.06
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.05, 'PERCENTILE_FIX'] = 1.05
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.04, 'PERCENTILE_FIX'] = 1.04
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.03, 'PERCENTILE_FIX'] = 1.03
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.02, 'PERCENTILE_FIX'] = 1.02
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.01, 'PERCENTILE_FIX'] = 1.01
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1., 'PERCENTILE_FIX'] = 1.
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.99, 'PERCENTILE_FIX'] = 0.99
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.98, 'PERCENTILE_FIX'] = 0.98
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.97, 'PERCENTILE_FIX'] = 0.97
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.96, 'PERCENTILE_FIX'] = 0.96
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.95, 'PERCENTILE_FIX'] = 0.95
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.94, 'PERCENTILE_FIX'] = 0.94
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.93, 'PERCENTILE_FIX'] = 0.93
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.92, 'PERCENTILE_FIX'] = 0.92
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.91, 'PERCENTILE_FIX'] = 0.91
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.9, 'PERCENTILE_FIX'] = 0.9
# statssaveme_df.loc[(statssaveme_df.PERCENTILE == 0.96015) & (statssaveme_df.MODEL_CODE == 'E2GR'), 'PERCENTILE_FIX'] = 0.96
statssaveme_df['PERCENTILE_TEMP'] = statssaveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
statssaveme_df['ACCT_ID_FIX'] = 'CON' + statssaveme_df['PERCENTILE_TEMP'].astype(str)
# statssaveme_df.loc[(statssaveme_df.PERCENTILE == 0.92164) & (statssaveme_df.MODEL_CODE == 'UKBAL'), 'ACCT_ID_FIX'] = 'CON0.92164'
dupe_check_df = pd.concat(g for _, g in statssaveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","STAT_NAME"]) if len(g) > 1)
Index_array = dupe_check_df.index
statssaveme_df.loc[Index_array, 'PERCENTILE_FIX'] = statssaveme_df.loc[Index_array, 'PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
statssaveme_df['PERCENTILE_TEMP'] = statssaveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
statssaveme_df['ACCT_ID_FIX'] = 'CON' + statssaveme_df['PERCENTILE_TEMP'].astype(str)
dupe_check_df = pd.concat(g for _, g in statssaveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","STAT_NAME"]) if len(g) > 1)
statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE', 'ACCT_ID'])
statssaveme_df= statssaveme_df.rename(columns={"PERCENTILE_FIX":"PERCENTILE", "ACCT_ID_FIX":"ACCT_ID"})
statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE_TEMP'])
statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE_PRE'])
# statssaveme_df = statssaveme_df[['RUN_DATE', 'MODEL_CODE', 'ACCT_TYPE', 'ACCT_ID', 'PERCENTILE', 'ASSET_CLASS', 'WEIGHT']]
statssaveme_df['ACCT_ID'] = statssaveme_df.ACCT_ID.astype(str)
statssaveme_df['PERCENTILE'] = statssaveme_df.PERCENTILE.astype(float)
statssaveme_df['VALUE'] = statssaveme_df.VALUE.astype(float)
statssaveme_df['RUN_DATE'] = pd.to_datetime(statssaveme_df.RUN_DATE)
# statssaveme_df = statssaveme_df.drop(columns=['Unnamed: 0'])
sql_execute(connection_string, f"""DELETE FROM AMS_AXIOMA_STATS_REBAL WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
insert_df(connection_string,statssaveme_df,'ams_axioma_stats_rebal')
Comments