portalrenovaveis_final

import pymysql.cursors

## ta configurado pra classe providers.
connection = pymysql.connect(host='localhost',
                             user='root',
                             port=3306,
                             password='admin',
                             db='testeDB',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)


class Provider:
    id = 0
    idaux = []
    nome = ""
    situacao = ""
    link = ""
    email = ""
    portfolio = ""
    busca = ""
    enderecos = []
    energias = []
    createdAt = ""
    updatedAt = ""

    def __init__(self, id, nome, situacao, link, email, portifolio
                 , busca, createdAt, updateAt, idaux=None, enderecos=None, energias=None):
        self.id = id
        self.nome = nome
        self.situacao = situacao
        self.link = link
        self.email = email
        self.portifolio = portifolio
        self.busca = busca
        self.createdAt = createdAt
        self.updatedAt = updateAt
        if (idaux == None):
            self.idaux = []
        if (enderecos == None):
            self.enderecos = []
        if (energias == None):
            self.energias = []


def presente(atual, lista):
    for membro in lista:
        if (atual == membro.nome):
            return True
    return False


## funcao percorre lista e retorna data.

def returndata(id):
    with connection.cursor() as cursor:
        sql = "select created_at from provider where id = %s"
        cursor.execute(sql, (id))
        timestamp = cursor.fetchone()
        #print("ID: ", id, timestamp)
        return timestamp['created_at']

def returnEmail(id):
    with connection.cursor() as cursor:
        #print("id dentro da funcao:",id)
        sql = "select email from provider where id = %s"
        cursor.execute(sql,(id))
        email = cursor.fetchone()

        #print (email['email'])
        return email['email']
try:
    with connection.cursor() as cursor:

        sql = "select * from provider  order by name "  # salva tabela provider
        cursor.execute(sql)
        select = list(cursor.fetchall())
        sql2 = "select * from provider_energy_type "  # salva tabela energy_type
        cursor.execute(sql2)
        provider_energy_type = list(cursor.fetchall())
        enderecos = "select * from provider_address"  # le os enderecos nXn
        cursor.execute(enderecos)
        provider_address = list(cursor.fetchall())
        select_tabela_enderecos = "select * from address "  # le a tabela completa de enderecos
        cursor.execute(select_tabela_enderecos)
        tabela_enderecos = list(cursor.fetchall())

        # for n in select:
        #     print(n['name'],n['id'])
        ## agora tenho que percorrer o select, salvar o primeiro sem ser repetido e salvar o id dos próximos.
        finalInsert = []
        for atual in select:

            if (presente(atual['name'], finalInsert) == False):
                aux = Provider(atual['id'],
                               atual['name'],
                               atual['status'],
                               atual['link'],
                               atual['email'],
                               atual['portfolio'],
                               atual['search'],
                               atual['created_at'],
                               atual['updated_at'])
                finalInsert.append(aux)
        # regista os ids que vao ser desconsiderados
        for registro in finalInsert:
            for auxiliar in select:
                if (registro.nome == auxiliar['name']):
                    if (registro.id != auxiliar['id']):
                        registro.idaux.append(auxiliar['id'])

        print(len(finalInsert))  # debug

        # debug tabela energia.
        # for n in provider_energy_type:
        #    print (n)

        ##insere as energias.
        for entry in finalInsert:
            for aux_energy in provider_energy_type:
                if entry.id == aux_energy['provider_id']:
                    entry.energias.append(aux_energy['energy_type_id'])
                elif aux_energy['provider_id'] in entry.idaux:
                    if (aux_energy['energy_type_id'] not in entry.energias):
                        entry.energias.append(aux_energy['energy_type_id'])
            auxiliardenovo = set(entry.energias)
            entry.energias = list((auxiliardenovo))

        # enderecos insere eles na lista de enderecos
        for entry in finalInsert:
            for aux_address in provider_address:
                if (entry.id == aux_address['provider_id']):
                    entry.enderecos.append(aux_address['address_id'])
                elif aux_address[
                    'provider_id'] in entry.idaux:  # SE a posicao atual da lista de enderecos for igual a algum dos ids da lista auxiliar de ids.
                    if (aux_address['address_id'] not in entry.enderecos):
                        entry.enderecos.append(aux_address['address_id'])
            auxiliardenovo = set(entry.enderecos)
            entry.enderecos = list(auxiliardenovo)

        ##definir ID certo.
        for entry in finalInsert:
            auxiliares = []
            for auxiliar in entry.idaux:
                #print(returndata(entry.id), ">", returndata(auxiliar))
                if (returndata(entry.id) > returndata(auxiliar)):
                    # print("sim")
                    auxiliares.append(auxiliar)
                else:
                    # print("nao")
                    auxiliares.append(entry.id)
                    entry.id = auxiliar
                    # print(entry.id)
            auxiliardenovo = set(auxiliares)
            entry.idaux = list(auxiliardenovo)

        for entry in finalInsert:
           if (returnEmail(entry.id) == ""):
               if(entry.email != ""):
                   sql = "update provider set email = %s where id = %s"
                   #print ("update provider set email = ", entry.email,"where id = ",entry.id)
                   cursor.execute(sql,(entry.email,entry.id))
                   connection.commit()




        #copiar email dos que não possuem para a classe, mas ainda não dá o altertable.
        for entry in finalInsert:
            for aux in entry.idaux:
                #print (entry.nome,entry.id,entry.email, returnEmail(aux) , aux)
                if (entry.email == ""  and returnEmail(aux) != ""):
                    sql = "update provider set email = %s where id = %s"
                    print("caso 2 =update provider set email = ",returnEmail(aux),"where id = ",entry.id)
                    cursor.execute(sql,(returnEmail(aux), entry.id) )
                    connection.commit()




        #nos casos em que há mais de um endereço, remove os enderecos genericos , definir os enderecos ali embaixo
        countdoisenderecos = 0
        countumendereco = 0
        for entry in finalInsert:
            for aux in  finalInsert:
                if (len(entry.enderecos) > 1):
                    if (865 in entry.enderecos):
                        entry.enderecos.remove(865)
                        countdoisenderecos+=1
                if (len(entry.enderecos) == 1):
                    #enderecoBrasil
                    if (865 in entry.enderecos):
                        sql = "select max(id) from address"
                        cursor.execute(sql)
                        var = cursor.fetchone()
                        maxAddress = var['max(id)'] + 1 # coloca o nome
                        countumendereco+=1
                        city = ""
                        neighborhood = ""
                        postal_code = ""
                        number = ""
                        phone = ""
                        carga = "carga" + str(maxAddress)
                        sql = "INSERT INTO address (id,country_id,address,city,neighborhood, postal_code,number,phone) VALUES (%s ,%s ,%s ,%s,%s,%s,%s,%s)"
                        cursor.execute(sql, (maxAddress, 1, carga, city, neighborhood, postal_code, number, number))
                        connection.commit()
                        entry.enderecos.append(maxAddress)
                        entry.enderecos.remove(865)
                    #enderecoArgentina
                    if (834 in entry.enderecos):
                        sql = "select max(id) from address"
                        cursor.execute(sql)
                        var = cursor.fetchone()
                        maxAddress = var['max(id)'] + 1 # coloca o nome
                        countumendereco+=1
                        city = ""
                        neighborhood = ""
                        postal_code = ""
                        number = ""
                        phone = ""
                        carga = "carga" + str(maxAddress)
                        sql = "INSERT INTO address (id,country_id,address,city,neighborhood, postal_code,number,phone) VALUES (%s ,%s ,%s ,%s,%s,%s,%s,%s)"
                        cursor.execute(sql, (maxAddress, 10, carga, city, neighborhood, postal_code, number, number))
                        connection.commit()
                        entry.enderecos.append(maxAddress)
                        entry.enderecos.remove(834)
                    #enderecolombia
                    if (860 in entry.enderecos):
                        sql = "select max(id) from address"
                        cursor.execute(sql)
                        var = cursor.fetchone()
                        maxAddress = var['max(id)'] + 1 # coloca o nome
                        countumendereco+=1
                        city = ""
                        neighborhood = ""
                        postal_code = ""
                        number = ""
                        phone = ""
                        carga = "carga" + str(maxAddress)
                        sql = "INSERT INTO address (id,country_id,address,city,neighborhood, postal_code,number,phone) VALUES (%s ,%s ,%s ,%s,%s,%s,%s,%s)"
                        cursor.execute(sql, (maxAddress, 9, carga, city, neighborhood, postal_code, number, number))
                        connection.commit()
                        entry.enderecos.append(maxAddress)
                        entry.enderecos.remove(860)
                    #enderecobolivia
                    if (858 in entry.enderecos):
                        sql = "select max(id) from address"
                        cursor.execute(sql)
                        var = cursor.fetchone()
                        maxAddress = var['max(id)'] + 1 # coloca o nome
                        countumendereco+=1
                        city = ""
                        neighborhood = ""
                        postal_code = ""
                        number = ""
                        phone = ""
                        carga = "carga" + str(maxAddress)
                        sql = "INSERT INTO address (id,country_id,address,city,neighborhood, postal_code,number,phone) VALUES (%s ,%s ,%s ,%s,%s,%s,%s,%s)"
                        cursor.execute(sql, (maxAddress, 11, carga, city, neighborhood, postal_code, number, number))
                        connection.commit()
                        entry.enderecos.append(maxAddress)
                        entry.enderecos.remove(858)
                    #enderecoChile
                    if (864 in entry.enderecos):
                        sql = "select max(id) from address"
                        cursor.execute(sql)
                        var = cursor.fetchone()
                        maxAddress = var['max(id)'] + 1 # coloca o nome
                        countumendereco+=1
                        city = ""
                        neighborhood = ""
                        postal_code = ""
                        number = ""
                        phone = ""
                        carga = "carga" + str(maxAddress)
                        sql = "INSERT INTO address (id,country_id,address,city,neighborhood, postal_code,number,phone) VALUES (%s ,%s ,%s ,%s,%s,%s,%s,%s)"
                        cursor.execute(sql, (maxAddress, 12, carga, city, neighborhood, postal_code, number, number))
                        connection.commit()
                        entry.enderecos.append(maxAddress)
                        entry.enderecos.remove(864)


        ## debug

        for n in finalInsert:
            print(n.nome, n.id, end=" ")
            for aux in n.idaux:
                print(aux, end=" ")
            print(n.energias)
            print(n.enderecos, end="")
            print("\n")

        print("um endereco", countumendereco)
        print("dois enderecos", countdoisenderecos)

        # switch pra rodar os sql que deletam os dados repetidos na tabela provider e criam novas conexoes
        # na tabela provider_type_energy.

        if (True):
            # deleta os ids repetidos.
            for entry in finalInsert:
                for membro in entry.idaux:
                    sql3 = "update provider set status='I' where id = %s"
                    cursor.execute(sql3, (membro))
            # cria os ids em provider_type.

            prepare = "delete from provider_energy_type"
            cursor.execute(prepare)
            connection.commit()

            for entry in finalInsert:
                for teste in entry.energias:
                    sql4 = "insert into provider_energy_type (provider_id,energy_type_id) values(%s,%s)"
                    cursor.execute(sql4, (entry.id, teste))
                    connection.commit()

            prepare = "delete from provider_address"
            cursor.execute(prepare)
            connection.commit()

            sql = "select * from address"
            cursor.execute(sql)
            list = cursor.fetchall()
            listaIDEndereco = []
            for entry in list:
                listaIDEndereco.append(entry['id'])

            print(listaIDEndereco)


            # insere na tabela provider_address
            for entry in finalInsert:
                for teste in entry.enderecos:
                    if(teste in listaIDEndereco):
                        insere_endereco = "insert into provider_address(provider_id,address_id) values(%s,%s)"
                        cursor.execute(insere_endereco, (entry.id, teste))
                        connection.commit()

            for entry in finalInsert:
                atribuiA = "update provider set status='A' where id = %s"
                cursor.execute(atribuiA, entry.id)
                connection.commit()



finally:
    connection.close()