import logging
import configparser
import pyodbc

config = configparser.ConfigParser()
try:
    config.read('D:\\data_loading\\ini.txt')
except Exception as e:
    logging.info(e)

server = config['Default']['server']
database = config['Default']['database']
username = config['Default']['username']
password = config['Default']['password']

conn_str = ('DRIVER={SQL Server}' +
            ';SERVER=' + server +
            ';DATABASE=' + database +
            ';UID=' + username +
            ';PWD=' + password)

cnxn = pyodbc.connect(conn_str)
cursor = cnxn.cursor()

clients_file = 'D:\\data_loading\\27062019_clients_blank.csv'
orders_file = 'D:\\data_loading\\27062019_orders_blank.csv'

if __name__ == '__main__':
    with open(orders_file, encoding='utf-8', newline='') as reader_1:
        orders = [line.split(',"') for line in reader_1.readlines()]
        orders[0][0] = orders[0][0].replace('\ufeff', '')
        for i in orders:
            i[-1] = i[-1].strip()
        orders = [[c.strip('"') for c in i] for i in orders]
        #print(orders)

    with open(clients_file, encoding='utf-8', newline='') as reader_2:
        clients = [line.split('","') for line in reader_2.readlines()]
        clients[0][0] = clients[0][0].replace('\ufeff', '')
        for i in clients:
            i[-1] = i[-1].strip()
        clients = [[c.strip('"') for c in i] for i in clients]
        #print(clients)



    r = cursor.execute("SELECT SAASCLIENTID FROM CLIENTS WHERE ENABLED = 1").fetchall()
    cl = [] #преобразование из кортежа в список id
    for c in r:
        cl += [l for l in c]
    #print(cl)

    for client in clients:
        client_id = client[0]
        saas_clientid = client[20]
        #print(saas_clientid)

        if client_id == 0: # поиск айди клиента из файла в базе
            #print(client_id)
            cursor.execute("INSERT INTO CLIENTS(FULLNAME,NAME,NAME1,NAME2,BIRTHDATE,DOCS,DOCNUM,DOCBEGINDATE,DOCCONTENT,ADDRESS_REG,ADDRESS_FACT,PHONE,MOBILEPHONE,BIRTHPLACE,DOCCODE,ENABLED,OLDNAME,CREATIONDATETIME,USERID,SAASCLIENTID)"
                           "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", client[1:])
            #print(client[1:])
            new_id = cursor.execute("SELECT TOP 1 ID FROM CLIENTS ORDER BY ID DESC").fetchone()
            #print(new_id)

        deals = [o for o in orders if o[17] == saas_clientid]
        #print(deals)

        if cl.count(client_id) == 0: # клиент есть в базе
            for deal in deals:
                deal[8] = new_id # обновляем значение clientid для договора
                print(deal)

        # записываем договоры в базу
        try:
            cursor.executemany('''INSERT INTO ORDERS(DAYSQUANT,LOANCOSTALL,ENABLED,WORKNAME,WORKADDRESS,WORKPROF,NUMBER,CREATIONDATETIME,CLIENTID,USERID,ORDERSTATUS,PUTDATETIME,PERCENTCOSTALL,
                               LOANRESTCOSTALL,INFO,MAINPERCENT,FULLNAME)
                               VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', deals)
            logging.info('Orders by client {0} had been loaded! Downloaded {1} orders!'.format(client_id,len(deals)))
        except pyodbc.ProgrammingError as e: # Если нет договоров у клиента в файле
            logging.info('Client id:{} has not orders!'.format(client_id))
    logging.info('Logging completed successfully.')
    logging.info('Downloaded {} orders!'.format(len(deals)))
    logging.info('Downloaded {} clients!'.format(len(new_id)))
cnxn.commit()
cursor.close()