# -*- coding: utf-8 -*-
"""
Created on Tue Mar  2 00:24:28 2021

@author: stalatam
"""
import pandas as pd
import numpy as np
import time

start_time = time.time()

''' ----------------- Loading MetLife Financial Data sets ----------------- '''

# Persistency
Persistency = pd.read_csv(data_path + 'Finance Data/McKinsey Update - Persistency - All.csv', engine = 'python')
Persistency=Persistency.rename(columns={'ï»¿REC_YR':'REC_YR'})

# Inforce
Inforce = pd.read_csv(data_path+'Finance Data/McKinsey Update - Inforce - All.csv',engine = 'python',
                          dtype={'CUST_NUM': str,
                                'CUST_NM': str,
                                'SRC_SYS_ST_CD': str,
                                'SRC_SYS_ZIP_DLV_OFC_CD': str})
Inforce=Inforce.rename(columns={'ï»¿REC_YR':'REC_YR'})

# Metlife Broker mapping file
MetLife_mapping = pd.read_csv(data_path+'Finance Data/McKinsey Update - Broker Listing.csv', engine='python')
MetLife_mapping=MetLife_mapping.rename(columns={'ï»¿BRKR_FIRM_ID':'BRKR_FIRM_ID'})

# Customer Logistics
Customer_logistics = pd.read_csv(data_path+"Finance Data/McKinsey Update - Customer Logistics - All.csv",sep = ",", engine='python')
Customer_logistics = Customer_logistics.rename(columns={"ï»¿CUST_NUM":"CUST_NUM"})
Customer_logistics["CUST_NUM"] = Customer_logistics["CUST_NUM"].str.strip() 
Customer_numtostate = Customer_logistics[["CUST_NUM","CUST_ST_CD"]].drop_duplicates().copy()
Customer_numtostate =  Customer_numtostate[Customer_numtostate["CUST_ST_CD"].notnull()]
Customer_numtostate = Customer_numtostate.rename(columns={"CUST_ST_CD":"Customer_State"})

# Sales and Quotes
Sales_Quote = pd.read_csv(data_path + 'Finance Data/McKinsey Update - Sales & Quotes - All.csv', engine = 'python', names=['ETL_CYC_DT',	'COMP_CD',	'LKUP_BRKR_FIRM_NM',	'LKUP_BRKR_ID',	'BRKR_FIRM_PRNT_NM',	'BRKR_FIRM_ZIP_CD',	'ELGBL_LIF_CNT',	'MKT_CD_WITH_SORT_TXT',	'SEG_3_DFN_TXT',	'BRKR_FIRM_CITY_NM',	'BRKR_FIRM_ST_CD',	'SL_REP_NM',	'SL_OFC_NM',	'TRAN_TYP_CD',	'QT_PCT',	'SL_PCT',	'CVR_CNT',	'LKUP_PROD_GRP_SCOR_CARD_NM',	'CVR_GRP_SCOR_CARD_CD',	'CVR_EFF_DT',	'EFF_YR',	'PRSP_NUM',	'CUST_NUM',	'CUST_NM',	'CUST_CITY_NM',	'CUST_ST_CD',	'CUST_ZIP_CD',	'DGT_4_SIC_CD',	'DGT_4_SIC_CD_DSCR','PREMIUM'],encoding="utf-8")
Sales_Quote["LKUP_BRKR_ID"] = Sales_Quote["LKUP_BRKR_ID"].str.strip()



''' ----------------- Loading MetLife Assumptions and Reconcilation Data sets ----------------- '''

# Finance_assumptions = pd.read_csv(data_path + "Assumptions and Mapping Data/Finance_Assumptions.csv")
Finance_assumptions = pd.read_excel(data_path + "Assumptions Data/Finance_Assumptions.xlsx") #dated running 3/15/2022
Finance_assumptions = Finance_assumptions.dropna()

# Broker standardization file
Broker_Standardized_file = pd.read_excel(data_path + "Mapping Data/McKinsey Update - metlife_brokers_standardized_all_yrs.xlsx")

parent_to_Mckinsey = pd.merge(Broker_Standardized_file[["BRKR_FIRM_ID","BrokerProper"]].drop_duplicates(),MetLife_mapping[["BRKR_FIRM_ID","BRKR_FIRM_PRNT_NM"]].drop_duplicates(),left_on = ["BRKR_FIRM_ID"],right_on = ["BRKR_FIRM_ID"],how = "left")
parent_to_Mckinsey = parent_to_Mckinsey[["BrokerProper","BRKR_FIRM_PRNT_NM"]].drop_duplicates()
parent_to_Mckinsey = parent_to_Mckinsey.groupby('BrokerProper').filter(lambda x: len(x) == 1)

# Medical focussed health plans
medical_focused_healthplan = pd.read_csv(data_path + "Assumptions Data/medical_focused_healthplan.csv", sep='|', encoding="WINDOWS-1252")
medical_focused_healthplan.columns=['Plans']
medical_focused_healthplan = list(medical_focused_healthplan["Plans"])



''' ----------------- Loading MetLife Mapping and Adjustment Data sets ----------------- '''

# ZIP Matrix
met_office_map = pd.read_excel(data_path + "Business Data/NA Matrix consolidated.xlsx")
met_office_map["Sales Office"] = met_office_map["Sales Office"].str.strip()
met_office_map["Region"] = met_office_map["Region"].str.strip()

# RM Default State
RM_Default_State = pd.read_excel(data_path + "Business Data/RM Default State_v1.xlsx")
RM_Default_State["Sales Office"] = RM_Default_State["Sales Office"].str.strip()
RM_Default_State["Region"] = RM_Default_State["Region"].str.strip()

# North East Adjustment
Northeast_broker_adjustment = pd.read_excel(data_path + "Business Data/Northeast_broker_adjustment.xlsx")
Northeast_broker_adjustment["Sales_Office_Northeast"] = Northeast_broker_adjustment["Sales_Office_Northeast"].str.strip()

# Southern Adjustment
southern_office_adjustment = pd.read_excel(data_path + "Business Data/sales_office_adjustment_by_broker_updated.xlsx")
southern_office_adjustment["RMZip_Sales_Office"] = southern_office_adjustment["RMZip_Sales_Office"].str.strip()
southern_office_adjustment["New_Sales_Office"] = southern_office_adjustment["New_Sales_Office"].str.strip()
southern_office_adjustment["New_Region"] = southern_office_adjustment["New_Region"].str.strip()
southern_office_adjustment = southern_office_adjustment.drop_duplicates()

# Regional Feedback
Regional_adjustment = pd.read_excel(data_path + "Mapping Data/Regional Feedback.xlsx")
Regional_adjustment = Regional_adjustment[Regional_adjustment['Feedback Status'] == 'Active']
Regional_adjustment = Regional_adjustment.drop(['Feedback Status','Comments'], axis = 1)

# Group Adjustments
Group_Account_Adjustments = pd.read_excel(data_path + "Business Data/Group_Account_Adjustments.xlsx")



''' ----------------- Loading 5500 Data sets ----------------- '''

# PIT Data
PIT_Data_Raw = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_PIT_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
PIT_Data = PIT_Data_Raw[PIT_Data_Raw.Year >= 2016]

# Mckinsey and Margie adjustments as discussed with Srinivas
PIT_Data['Broker'] = np.where(((PIT_Data['Broker_Raw'].str.contains('CONNOR & GALLAGHER')) & (PIT_Data['Broker'] == 'Benefit Services')),'Connor and Gallagher Benefit Services',PIT_Data['Broker']) 
PIT_Data['BrokerCrossProduct'] = np.where(((PIT_Data['Broker_Raw'].str.contains('CONNOR & GALLAGHER')) & (PIT_Data['BrokerCrossProduct'] == 'Benefit Services')),'Connor and Gallagher Benefit Services',PIT_Data['BrokerCrossProduct']) 
PIT_Data['Broker'] = np.where((PIT_Data['Broker'] == 'Summit')& (PIT_Data['Broker_State'].isin(['WV', 'VA'])),'The Hilb Group',PIT_Data['Broker']) 
PIT_Data['BrokerCrossProduct'] = np.where((PIT_Data['BrokerCrossProduct'] == 'Summit')& (PIT_Data['BrokerCrossProductSTATE'].isin(['WV', 'VA'])),'The Hilb Group',PIT_Data['BrokerCrossProduct']) 
PIT_Data['Broker'] = np.where((PIT_Data['Broker'] == 'Tfa Benefits'),'Towne Bank',PIT_Data['Broker']) 
PIT_Data['BrokerCrossProduct'] = np.where((PIT_Data['BrokerCrossProduct'] == 'Tfa Benefits'),'Towne Bank',PIT_Data['BrokerCrossProduct']) 
PIT_Data['Broker'] = np.where((PIT_Data['Broker'] == 'Capital Grpbenefitsfin '),'Capital Group Benefits',PIT_Data['Broker']) 
PIT_Data['BrokerCrossProduct'] = np.where((PIT_Data['BrokerCrossProduct'] == 'Capital Grpbenefitsfin'),'Capital Group Benefits',PIT_Data['BrokerCrossProduct']) 
PIT_Data['Broker'] = np.where(((PIT_Data['Broker_Raw'].str.upper().str.contains('ADVANCED BENEFIT SOLUTION'))& (PIT_Data['Broker_State'] == 'MI')),'Advanced Benefit Solutions',PIT_Data['Broker']) 
PIT_Data['BrokerCrossProduct'] = np.where(((PIT_Data['Broker_Raw'].str.upper().str.contains('ADVANCED BENEFIT SOLUTION')) & (PIT_Data['BrokerCrossProduct'] == 'Gallagher') & (PIT_Data['BrokerCrossProductSTATE'] == 'MI')),'Advanced Benefit Solutions',PIT_Data['BrokerCrossProduct']) 



# Member Adjustment basing on feedback

x = PIT_Data[(PIT_Data.EIN == 521846907)]

PIT_Data['Members']=np.where(((PIT_Data['EIN']==10211783) & 
                             (PIT_Data['Product'].isin(['CI','Accident'])) &
                             (PIT_Data['Year'].isin([2021, 2020]))),149 ,PIT_Data['Members'])
         
PIT_Data['Members']=np.where((PIT_Data['EIN']==530115260) & (PIT_Data['Members'] > 99999),3785,PIT_Data['Members'])

PIT_Data['Members']=np.where(((PIT_Data['EIN']==541230110) & 
                             (PIT_Data['Carrier']=='Unum') &
                             (PIT_Data['Product']=='STD') &
                             (PIT_Data['Year'].isin([2021, 2020]))),2215,PIT_Data['Members'])
         
PIT_Data['Members']=np.where(PIT_Data['EIN']==900424876  
                            ,307,PIT_Data['Members'])

PIT_Data['Members'] = np.where(PIT_Data['EIN']==521846907  
                            ,PIT_Data['EIN_FTE'],PIT_Data['Members'])

PIT_Data['Members'] = np.where(PIT_Data['EIN']==462283648  
                            ,2540,PIT_Data['Members'])

PIT_Data['Members'] = np.where(PIT_Data['EIN']==132928869  
                            ,5237,PIT_Data['Members'])                            

PIT_Data['Members']=np.where(((PIT_Data['EIN']==941632821) & 
                             (PIT_Data['Carrier']=='Delta Dental') &
                             (PIT_Data['Product']=='Dental')  &
                             (PIT_Data['Year'].isin([2016,2017,2018,2019,2020, 2021]))) ,PIT_Data['EIN_FTE'],PIT_Data['Members'])

PIT_Data['Members']=np.where(((PIT_Data['EIN']==135156640) & 
                             (PIT_Data['Product']=='Dental')),2120,PIT_Data['Members'])
PIT_Data['Members']=np.where(((PIT_Data['EIN']==135156640) & 
                             (PIT_Data['Product']=='Life')),1413,PIT_Data['Members'])
PIT_Data['Members']=np.where((PIT_Data['EIN']==135156640) & 
                             (PIT_Data['Product'].isin(['LTD','STD','Vision'])),4240,PIT_Data['Members'])

PIT_Data['Members']=np.where(PIT_Data['EIN']==753172698,PIT_Data['EIN_FTE'],PIT_Data['Members'])

PIT_Data=PIT_Data[~((PIT_Data['EIN']==592693969) &
                             (PIT_Data['Product']=='Dental') & (PIT_Data['Carrier']=='Not Provided'))]

PIT_Data[(PIT_Data['EIN']==900424876)][['Product','Members','Carrier']]
PIT_Data[(PIT_Data['EIN']==941632821)][['Product','Members','Carrier']]

# Group Standardization file
PIT_Data_grp_std = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_PIT_extract_standardizedGroupNames_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")

PIT_Data = pd.merge(PIT_Data,PIT_Data_grp_std,left_on = ['ACK_ID', 'EIN', 'Year', 'State_HQ', 'Carrier', 'Carrier_EIN', 'Broker','Product', 'Members', 'Funding', 'EIN_FTE'],right_on = ['ACK_ID', 'EIN', 'Year', 'State_HQ', 'Carrier', 'Carrier_EIN', 'Broker','Product', 'Members', 'Funding', 'EIN_FTE'],how = "left")
PIT_Data["Group"] = PIT_Data["Group_y"]
PIT_Data = PIT_Data.drop(["Group_x","Group_y"],axis = 1)

# Broker Longitudinal Data
Broker_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_brokerLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
Broker_Long["EIN_FTE"] = Broker_Long["EIN_FTE"].fillna(100)
Broker_Long = Broker_Long.fillna("")
   
# Product Longitudinal Datasets
Dental_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_DentalLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
Life_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_LifeLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
LTD_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_LTDLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
STD_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_STDLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
Vision_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_VisionLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
Accident_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_AccidentLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
Hospital_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_HospitalLongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")
CI_Long = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_CILongitudinal_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")

# Emperical Switching Account Data
EmpiricalSwitchingAccount = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_EmpiricalSwitchingAccount_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")

# Shares Broker X Carrier
shares_brokerXcarrier = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_shares_brokerXcarrier_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")

# EIN Product Bundling
EIN_product_bundling = pd.read_csv(data_path + "5500 Data/GAIN_McKinsey_EIN_product_bundling_230117.txt", sep='|', engine='python', encoding="WINDOWS-1252")

# Intermediate
Match = pd.read_csv(GAIN_path + "Outputs/Intermediate/SF Mapping/Match.csv", keep_default_na=False,engine='python')
Match_internal = pd.read_csv(GAIN_path + "Outputs/Intermediate/SF Mapping/Match_internal.csv",keep_default_na=False,engine='python')
Opportunity_product = pd.read_csv(GAIN_path + "Outputs/Intermediate/SF Mapping/Opportunity_product.csv",engine='python')
Match["SF_Group_State"] = Match["Site"].str.split(',').str[1]
Match_internal["SF_Group_State"] = Match_internal["Site"].str.split(',').str[1]
brk_map_raw = pd.read_excel(GAIN_path + 'Outputs/Intermediate/Broker to AE Mapping/Accounts mapped on Broker.xlsx')

# SF gain broker file
sf_broker_mapping=pd.read_excel(data_path+'Mapping Data/SF to GAIN Broker v1.xlsx')

end_time = time.time()
print('Total time to run the code is ' + str(round((end_time - start_time)/60, 0)) + ' mins')

'''  ------  The End  ------  '''