import data_read_write as drw
import pandas as pd
import numpy as np

def calc_cols(df):
    df['96440'] = (df['96433']+df['15744'])/2

    # if 35699 is empty, replace it with a zero (to prevent null values in columns that use 35699)
    df['15745'] = ((df['96433']/df['15734'])*1000) + df['35699'].combine_first(pd.Series(0, index=df.index, name='zeroes'))

    # 2017+ uses a different calculation than 2016
    df['35702'] = ((df['15780']- df['15805'])/ df['15805'])
    # so 2016 has to be changed
    df.loc[df['Peiljaar'] == '1-1-2016', '35702'] = ((df['15744'] - df['15750'])/ df['15750'])

    df['96441'] = (df['15771']+df['15772'])/2 
    df['96488'] = ((df['96441']/df['15734'])*1000)+df['35699'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['35701'] = df['15772']/df['15744']
    df['96444'] = (df['15779']+df['15780'])/2 
    df['15781'] = df['15780']/df['15772']
    df['96445'] = df['15772']/df['15780']
    df['15763'] = (df['15750'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15752'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15753'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15754'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15755'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15756'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15757'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15758'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15759'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15760'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15762'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['24293'].combine_first(pd.Series(0, index=df.index, name='zeroes')))-df['15751'].combine_first(pd.Series(0, index=df.index, name='zeroes'))-df['17196'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['15764'] = (df['15763']/df['15744'])
    df['15767'] = df['15752']+df['15753']+df['15754']+df['15755']+df['15756']+df['15758']+df['15759']+df['15762']+df['24293']
    df['15768'] = (df['15767']/df['15744'])+(df['35700'].combine_first(pd.Series(0, index=df.index, name='zeroes'))/100)
    df['17239'] = df['15773']+df['15775']
    df['15776'] = ((df['15775'])/df['15772'])*100
    df['99861'] = (df['15775'])/(df['15773']+df['15775']+df['15777'])
    df['99860'] = ((df['15777'])/(df['15773']+df['15775']+df['15777']))*100
    df['15778'] = ((df['15775'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15777'].combine_first(pd.Series(0, index=df.index, name='zeroes')))/(df['15773'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15775'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15777'].combine_first(pd.Series(0, index=df.index, name='zeroes'))))
    df['35806'] = df['35799']+df['35800']+df['35801']+df['35802']+df['35804']+df['35805']
    df['15802'] = df['15800']/((df['15779']+df['15780'])/2)
    df['15803'] = df['15801']/((df['15779']+df['15780'])/2)
    df['24294'] = (df['15801']/df['15800'])*100
    df['96448'] = df['15801']/df['15859']
    df['15807'] = (df['15806']/df['15805'])*100
    df['15810'] = (df['15809']/df['15780'])*100
    df['15812'] = df['15812'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['15813'] = df['15813'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['15814'] = df['15814'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['15815'] = df['15815'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['15816'] = df['15816'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['17241'] = df['15812']+df['15813']+df['15814']+df['15815']+df['15816']
    df['15817'] = (df['15812']+df['15813'])/df['15780']
    df['15818'] = df['15816']/df['15780']*100
    df['98137'] = df['15820']-df['15822']


    # 2020- uses a different calculation than 2020
    df['15829'] = (df['15820']/df['15779'])
    df['15830'] = (df['15821']/df['15779'])
    df['15831'] = (df['15822']/df['15779'])
    
    # so 2020 has to be changed
    df.loc[df['Peiljaar'] == '1-1-2020', '15829'] = (df['15820']/df['15780'])
    df.loc[df['Peiljaar'] == '1-1-2020', '15830'] = (df['15821']/df['15780'])
    df.loc[df['Peiljaar'] == '1-1-2020', '15831'] = (df['15822']/df['15780'])



    df['15832'] = (df['15823']/df['15779'])*100
    df['15834'] = (df['15825']/df['15780'])*100
    df['15835'] = (df['15826']/df['15780'])*100
    df['15837'] = (df['15828']/df['15779'])*100
    df['15848'] = df['15847']/((df['96433']+df['15744'])/2)
    df['15849'] = df['15847']/df['15734']
    df['15861'] = df['15859']/df['15772']
    df['15866'] = df['15864']/((df['96433']+df['15744'])/2)
    df['59153'] = df['15864']/df['15734']
    df['15878'] = (df['15877']/df['24412'])*100
    df['56065'] = ((df['24441']+(df['56056']+df['56057']+ df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063']))/(df['15859']+(df['56056']+df['56057']+ df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063'])))*100
    df['15882'] = df['15880']/(df['15859']+df['15880'])
    df['15883'] = (df['15881']/(df['24441']+df['15881']))*100
    df['15886'] = df['15885']/100
    df['36766'] = df['36762']+df['36763']+df['36764']+df['36765']
    df['36769'] = (df['36762']*1+df['36763']*2+df['36764']*3+df['36765']*4)/100
    df['15892'] = df['96461']+df['96460'] 
    df['15893'] = df['15892']/((df['96433']+df['15744'])/2)
    df['24299'] = df['17228']/df['15734']
    df['96467'] = df['96465']/df['15734']
    df['35697'] = ((df['24441']+(df['39453']+df['39454']+ df['39455']+df['39456']+df['39457']+df['39458']+df['39459']+df['39460']+df['39461']))/(df['15859']+(df['39453']+df['39454']+ df['39455']+df['39456']+df['39457']+df['39458']+df['39459']+df['39460']+df['39461'])))*100
    df['97948'] = ((df['15752']+df['15753']+df['15754']+df['15755']+df['15756']+df['15758']+df['15759'])* df['97941'])+((df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39458']+df['39460'])/ df['96440']) + df['15866']
    df['96711'] = df['56071']/df['15744']
    df['96712'] = df['56072']/df['15744']
    df['96713'] = df['56076']/df['15744']
    df['15912'] = df['15908']+df['15910']
    df['15913'] = (df['15908'])/df['15744']
    df['15914'] = (df['15908']/df['15912'])*100
    df['15915'] = (df['15910']/df['15912'])*100
    df['24317'] = df['24316']/df['15908']
    df['15923'] = (df['15921']/df['15909'])*1000
    df['15927'] = df['15925']/df['15909']
    df['15924'] = df['15922']/df['15909']
    df['15928'] = df['15926']/df['15909']
    df['24301'] = df['24300']/df['15909']
    df['24302'] = df['15924']+df['15928']+df['24301']
    df['39349'] = (df['15922']/df['15921'])/1000
    df['39350'] = df['15926']/df['15925']
    df['15870'] = df['15869']/df['15744']
    df['24296'] = df['24295']/df['15744']
    df['15938'] = (df['15937']/df['15936'])
    df['15941'] = (df['15940']/df['15936'])*100
    df['15946'] = (df['15944']/df['15943'])*100
    df['35797'] = (df['35796']/df['15943'])*100
    df['17263'] = (df['24346']+df['24347']+df['24348']+df['24349']+df['24350']+df['24351']+df['24352']+df['24353']+df['24354']+df['24355']+df['24356']+df['24357']+df['24358']+df['24359']+df['24360']+df['24361']+df['24362']+df['24363']+df['24364']+df['24365']+df['24366']+df['24367']+df['24368']+df['24369']+df['24370']+df['24371']+df['24372'])/27
    df['15965'] = df['15949']/((df['15779']+df['15780'])/2)
    df['96491'] = df['15750']-df['15751']
    df['96492'] = df['15760']-df['17196']
    df['96493'] = (df['96491']/df['15744'])
    df['96494'] = (df['96492']/df['15744'])
    df['96495'] = (df['15752']/df['15744'])
    df['96496'] = (df['15753']/df['15744'])
    df['96497'] = (df['15754']/df['15744'])
    df['96498'] = (df['15755']/df['15744'])
    df['96499'] = (df['15756']/df['15744'])
    df['96500'] = (df['15758']/df['15744'])
    df['96501'] = (df['24293']/df['15744'])
    df['96502'] = (df['15759']/df['15744'])
    df['96503'] = (df['15762']/df['15744'])
    df['104008'] = df['15813']+df['15814']
    df['103115'] = df['103109']+df['103110']+df['103111']+df['103112']+df['103113']+df['103114']
    df['104861'] = df['15864']-(df['103109']+df['103110']+df['103111']+df['103112']+df['103114']+df['103113'])

    # if 103108 is empty, replace it with a zero (to prevent null values in columns that use 103108)
    df['103116'] = df['15864']/(df['15780']+df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes')))

    df['103117'] = df['15864']/(df['15859']+df['15880'])*100
    df['103105'] = df['103101']+df['103102']+df['103103']+df['103104']
    df['103106'] = df['103105']/df['15734']
    df['104016'] = df['103911']/df['15734']
    df['103913'] = df['103911']/((df['96433']+df['15744'])/2)
    df['104015'] = (df['103911']/(df['15880']+df['15859']))*100
    df['104790'] = (df['35696']/df['35694'])*100
    df['104789'] = (df['104788']/df['104787'])*100
    df['104014'] = (df['15757']/df['15744'])*100
    df['104007'] = df['15813']+df['15814']
    df['104075'] = (df['104074']/df['15744'])*100
    df['104010'] = df['103101']/df['15734']
    df['104011'] = df['103102']/df['15734']
    df['104012'] = df['103103']/df['15734']
    df['104013'] = df['103104']/df['15734']
    df['104020'] = df['15780']+df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes'))
    df['104021'] = df['103109']/df['104020']
    df['104022'] = df['103110']/df['104020']
    df['104023'] = df['103111']/df['104020']
    df['104024'] = df['103112']/df['104020']
    df['104025'] = df['103114']/df['104020']
    df['104026'] = df['103113']/df['104020']
    df['104028'] = df['103109']/df['15734']
    df['104029'] = df['103110']/df['15734']
    df['104030'] = df['103111']/df['15734']
    df['104031'] = df['103112']/df['15734']
    df['104032'] = df['103114']/df['15734']
    df['104033'] = df['103113']/df['15734']
    df['104036'] = (df['96449']/df['15772'])*100
    df['104039'] = (df['96450']/df['15772'])*100
    df['104040'] = (df['96451']/df['15772'])*100
    df['104041'] = (df['96452']/df['15772'])*100
    df['104042'] = (df['96453']/df['15772'])*100
    df['104045'] = (df['96454']/df['15772'])*100
    df['104049'] = (df['15825']/df['15780'])*100
    df['104050'] = (df['15826']/df['15780'])*100
    df['104051'] = ((df['15780']-df['15825']-df['15826'])/df['15780'])*100
    df['104053'] = df['56056']+df['56057']+df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063']
    df['104058'] = (df['24441']/(df['15859']+df['104053']))*100
    df['104059'] = (df['104053']/(df['15859']+df['104053']))*100
    df['104054'] = df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39459']+df['39458']+df['39460']+df['39461']
    df['104078'] = (df['15812']/df['17241'])*100
    df['104079'] = (df['15813']/df['17241'])*100
    df['104080'] = (df['15814']/df['17241'])*100
    df['104081'] = (df['15815']/df['17241'])*100
    df['104082'] = (df['15816']/df['17241'])*100
    df['104084'] = (df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes'))/df['104020'])*100
    df['104085'] = (df['15780']/df['104020'])*100
    df['104086'] = df['15869']/df['104020']
    df['104862'] = df['104861']/df['104020']
    df['104863'] = df['104861']/df['15734']
    df['157213'] = (df['157210']/df['15780'])*100
    df['157524'] = ((df['15864']+df['15880']+df['15859']+df['24316']+df['15801'])-(df['103109']+df['103110']))
    df['157339'] = df['157329']/df['96441']
    df['157367'] = df['15829']
    df['157369'] = df['15831']
    df['157370'] = df['15776']
    df['157371'] = df['15882']
    df['157372'] = df['15795']
    df['158226'] = df['96495']+df['96496']+df['96497']+df['96498']+df['96499']+df['96500']+df['96501']+df['96502']+df['96503']
    df['157642'] = df['104861']/df['104020']
    df['157643'] = df['104861']/df['15734']
    df['157717'] = df['103095']+df['103096']+df['103097']+df['103098']+df['35952']+df['35950']+df['35951']+df['35953']+df['35955']+df['35956']+df['35957']+df['35954']+df['35958']+df['35962']+df['35964']+df['35965']+df['35966']
    df['157713'] = df['103095']+df['103096']+df['103097']+df['103098']+df['35952']+df['35950']+df['35951']+df['35953']+df['35955']+df['35956']+df['35957']+df['35954']+df['35958']
    df['157715'] = 13-df['157713']
    df['157714'] = df['35962']+df['35964']+df['35965']+df['35966']
    df['157716'] = 4-df['157714']
    df['157676'] = (df['157534']+df['157535']+df['157537']+df['157536'])*25
    df['157681'] = (df['157677']+df['157678']+df['157679']+df['157680'])*25
    df['157686'] = (df['157682']+df['157683']+df['157684']+df['157685']+df['157687'])*20
    df['157718'] = (((df['157700']+df['157701'])*50)+((df['157709']+df['157703'])/2))/2
    df['160651'] = (df['96449']+df['96450']+df['96451']+df['96452']+df['96453']+df['96454'])
    df['161594'] = (df['15861']*df['15767'])
    df['161592'] = df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39459']+df['39458']+df['39460']
    df['161593'] = ((df['161594']+df['161592'])/(df['15859']+df['161592']))*100
    df['160593'] = df['15949']/((df['15779']+df['15780'])/2)
    df['103124'] = df['103124']/100
    df['163202'] = df['163202']/100
    df['163204'] = df['163204']/100
    df['163205'] = df['163205']/100
    df['163206'] = df['163206']/100
    df['163207'] = df['163207']/100
    return df

def main():
    config_path = 'config.ini'
    excel_path = 'Data_VvB_def.xlsx'
    excel_sheets = ['2015', '2016', '2017', '2018', '2019', '2020']
    
    engine_vvb = drw.create_db_connection(config_path, 'gemeenten')
    df_vvb_1520 = drw.read_excel_file(excel_path, excel_sheets)

    # 2021 uses commas as decimal separator
    # when writing this directly to the db, columns turn out as strings instead of double
    # therefore, write/read to csv has been added as workaround
    df_vvb_21 = drw.read_from_db('data_pbi', engine_vvb)

    # remove dashes and other NAs
    df_vvb_21 = df_vvb_21.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'10-20':15}, regex=True)

    df_vvb_21.to_csv('Data_VvB_21.csv', index=False, sep=';', decimal=',')
    df_vvb_21 = pd.read_csv('Data_VvB_21.csv', sep=';', decimal=',')

    df_vvb_21['Jaar'] = '2021-01-01'
    df_vvb_21['Jaar'] = pd.to_datetime(df_vvb_21['Jaar'])
    df_vvb_21['Organisatienaam'] = df_vvb_21['Organisatienaam'].str.replace('Gemeente ', '')

    df_vvb_21 = drw.remove_icode(df_vvb_21, 2021)

    columns_to_divide = [
        '35701',
        '15882',
        '15764',
        '15768',
        '163202',
        '163204',
        '163205',
        '163206',
        '163207',
        '15886',
        '15795',
        '15778',
        '15829',
        '15831',
        '96448',
        '103124',
        '15938'
    ]
    df_vvb_21[columns_to_divide] = df_vvb_21[columns_to_divide]/100


    df_vvb_1521 = pd.concat([df_vvb_1520, df_vvb_21])

    drw.write_to_db(engine_vvb, 'vvb_validatie', df_vvb_1521, append=False)
    drw.write_to_db(engine_vvb, 'vvb', df_vvb_1520, append=False)

if __name__ == "__main__":
    main()