import pyodbc
import os
import pandas
import re
import io
import math

string_connection="Driver={SQL Server};SERVER=*; DATABASE=*"
connect=pyodbc.connect(string_connection)

name_folder=csv_files=[x for x in os.listdir("//S*/d$/Reports/DeviceDirectory") if x.endswith("ALPHA") or x.endswith("SIGMA")]

csv_files=[x for x in os.listdir("//*u/d$/Reports/DeviceDirectory/") if x.endswith(".csv")] #все CSV
cursor=connect.cursor()
file=open("logs.txt","w")

def validate_name_table(name_table):
    cursor.execute("select COUNT(*) from sys.tables where name='"+name_table+"'")
    row=cursor.fetchone()
    if row[0]==0:
        validate=False
    else:
        validate=True
    return validate

def query_column(columns_list):
    result=''
    for column_name in columns_list:
        result+=column_name.strip()+','
    return  result[0:-1]

def insert_data():
    for namefolders in name_folder:
        for name_table_full in csv_files:
            name_table = name_table_full.split('.')[0].replace("-", "")+"_"+namefolders
            # ОЧИСТКА ТАБЛИЦЫ
            try:
                cursor.execute("truncate table [*].[*].["+name_table+"]")
                connect.commit()
                print("Очистка выполнена, таблица: " + name_table)
            except Exception as e:
                print("Очистка таблицы "+name_table+" не выполнена, код ошибки: ",e)
            #
            excel_csv = pandas.read_csv("//*/d$/temp/devdir_src/"+name_table_full, dtype=str)
            query_column_str = query_column(excel_csv.columns)
            count=excel_csv.count()[0]
            for i in range(count):
                query_values =''
                for columns_n in excel_csv.columns:
                    if (re.search('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', str(excel_csv[columns_n][i]))):
                        stroka=str(excel_csv[columns_n][i][0:-3])
                        query_values += "'" + stroka.replace("'", "*") + "',"
                    else:
                        stroka = str(excel_csv[columns_n][i])
                        if stroka=='nan':
                            query_values+='null,'
                        else:
                            if(re.search('/[0-9][0-9]', stroka)):
                                stroka=stroka[0:-3]
                            query_values += "'"+stroka.replace("'","*") + "',"
                try:
                    query='INSERT INTO '+name_table+' ('+query_column_str+') VALUES ('+query_values[0:-1]+')'
                    cursor.execute(query)
                    connect.commit() #ВЫПОЛНЕНИЕ ЗАПРОСА В БД
                except Exception as e:
                    print('======')
                    print('Ошибка добавления:\n'+query+'\n',e)
                    print('======')
                    file.write('Ошибка добавления:\n'+query+'\n',e)
        print ('======Добавление в таблицу '+name_table+' выполнено!======')
        file.write('======Добавление в таблицу '+name_table+' выполнено!======')

def create_table():
    for namefolders in name_folder:
        for name_table_full in csv_files:
            try:
                string_column_table = ''
                name_table=name_table_full.split('.')[0].replace("-","")+"_"+namefolders
                query = ''
                validate_name_ = validate_name_table(name_table)
                if validate_name_==True:
                    continue
                excel_csv=pandas.read_csv("//*/d$/temp/devdir_src/"+name_table_full,dtype=str)
                columns=excel_csv.columns
                for name_column in columns:
                   #if(re.search('_at',name_column)):
                    if (re.search('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', str(excel_csv[name_column][0]))):
                        string_column_table+=name_column + " " + "datetime"+","
                    else:
                        string_column_table+= name_column+ " "+ "nvarchar(MAX)"+","
                        #name_table.split('.')[0]
                query='CREATE TABLE '+name_table+" ("+string_column_table[0:-1]+")"
                cursor.execute(query)
                connect.commit()
                print("======Таблица "+name_table+" создана======")
            except Exception as e:
                print("Таблица "+name_table+" не создана:",e)

#connect.close()