import pandas as pd
import numpy as np
import datetime as dt

path = "C:/Users/1000256942/Documents/Scripts/OTP Test/"
EC = pd.read_excel(path + 'Static Earnings Codes.xlsx',sheet_name=0)
OTP = pd.read_excel(path + 'International_Payroll_Payment_Report.xlsx',sheet_name=0,
                                                                       skiprows=1)
FN = pd.read_excel(path + 'Export for OTPs.xlsx',sheet_name=0)
SF = pd.read_excel(path +'Static Fields.xlsx',sheet_name=0)

df_one_time_payments = pd.DataFrame(OTP[['Employee ID',
                                         'One Time Payment Amount',
                                         'Plan']])
df_one_time_payments = df_one_time_payments[df_one_time_payments['One Time Payment Amount'].notnull()]

df_file_numbers = pd.DataFrame(FN[["Employee ID",
                                   'File Number']])
df_file_numbers = df_file_numbers.replace(r'\s+',np.nan,regex=True).replace('',np.nan).dropna()
df_file_numbers['Employee ID'] = df_file_numbers['Employee ID'].astype('int64')

df_earnings_codes = pd.DataFrame(EC[['Plan',
                                     'Earnings Code']])

df_merged_tables = df_one_time_payments.merge(df_file_numbers, on='Employee ID', how='left').merge(df_earnings_codes, on='Plan', how='left')
df_merged_tables["CO Code"] = "WDD"
df_merged_tables["PAY #"] = "9"
df_merged_tables = df_merged_tables.rename(columns={'Earnings Code' : 'Earnings Code 3',
                                                    'One Time Payment Amount' : 'Earnings Code 3 Amount'})

Final = pd.DataFrame(df_merged_tables[['CO Code',
                                       'PAY #',
                                       'File Number',
                                       'Earnings Code 3',
                                       'Earnings Code 3 Amount']])

today = dt.datetime.today().strftime('%m%d%Y%H%M')
Final.to_csv(path + 'OTP_File_{}.csv'.format(today), index=False)