import pandas as pd
from sqlalchemy import create_engine
from database import conn_string

cfg = {
    'clients': ['deop01', 'deop02', 'deop03', 'deop04', 'deop05', 'deop06', 'deop07', 'deop08', 'deop09', 'deop10',
                'deop11', 'deop12', 'deop13', 'deop14', 'deop15', 'deop16', 'deop17', 'deop18', 'deop19', 'deop20',
                'deop21', 'deop22', 'deop23', 'deop24', 'deop25', 'deop26', 'deop27', 'deop28', 'deop29', 'deho02', 'deni02'],
    'date_filter': "'2019-01-01'",
    'source_dsn': {'user': 'sa', 'pass': 'Mffu3011#', 'server': 'GC-SERVER1\\GLOBALCUBE', 'database': 'desk01', 'driver': 'mssql'},
    'source_schema': 'dbo',
    'source_query': 'Belege_Planung_Ist_FC.sql',
    'source_header': ['Datenbank', 'Betrieb_Nr', 'Marke', 'Konto_Nr', 'Bookkeep_Period', 'Betrag', 'Menge'],
    'target_csv': 'Belege_Planung_Ist_FC.csv'
}


def db_import(select_query, source_db, current_table, target_db, target_schema):
    pd.read_sql(select_query, source_db).to_sql(current_table['target'], target_db, schema=target_schema, index=False, if_exists='append')


source_db = create_engine(conn_string(cfg['source_dsn']))
with open(cfg['source_query'], 'r') as f:
    select_query = f.read()


with open(cfg['target_csv'], 'w') as f:
    f.write(';'.join(cfg['source_header']) + '\n')

for current_client in cfg['clients']:
    current_query = select_query.replace('desk01', current_client)
    pd.read_sql(current_query, source_db).to_csv(cfg['target_csv'], sep=';', decimal=',', encoding='latin-1',
                                                 index=False, header=False, mode='a')
    print(current_client)