from formulario.models import Sitio
import xlrd
import time

def run():
    print("Borrando sitios...")
    Sitio.objects.all().delete()
    print("OK")

    doc = xlrd.open_workbook("scripts/mapa_dependencias/DEPENDENCIAS.xlsx")
    hoja = doc.sheet_by_index(0)
    columnas = hoja.ncols
    filas = hoja.nrows

    id_i = -1
    org_i = -1
    dep_i = -1
    prov_i = -1
    tec_i = -1
    est_i = -1
    bw_dw_i = -1
    bw_up_i = -1
    coord_i = - 1

    for i in range(columnas):
        celda = str(hoja.cell_value(0,i))
        if celda == "Nombre":
            id_i = i
        if "ORGANISMO" in celda.upper():
            org_i = i
        if "DEPENDENCIA" in celda.upper():
            dep_i = i
        if "PROVEEDOR" in celda.upper():
            prov_i = i
        if "TECNOLO" in celda.upper():
            tec_i = i
        if "ESTADO DE V" in celda.upper():
            est_i = i
        if "BW DW" in celda.upper():
            bw_dw_i = i
        if "BW UP" in celda.upper():
            bw_up_i = i
        if "COORD" in celda.upper():
            coord_i = i

    if id_i == -1 or org_i == -1 or dep_i == -1 or prov_i == -1 or tec_i == -1 or est_i == -1 or bw_dw_i == -1 or bw_up_i == -1 or coord_i == - 1:
        print("No se encontraron todas las columnas necesarias para cargar los sitios.")

    for i in range(filas):
        ID = str(hoja.cell_value(i,id_i))
        ORG = str(hoja.cell_value(i,org_i))
        DEP = str(hoja.cell_value(i,dep_i))
        PROV = str(hoja.cell_value(i,prov_i))
        TEC = str(hoja.cell_value(i,tec_i))
        EST = str(hoja.cell_value(i,est_i))
        if "Alta" not in EST:
            continue
        BW_DW = str(hoja.cell_value(i,bw_dw_i))
        BW_UP = str(hoja.cell_value(i,bw_up_i))
        COORD = str(hoja.cell_value(i,coord_i))

        while not COORD.startswith("-"):
            if COORD[0] == "3" or COORD[0] == "4":
                COORD = "-" + COORD
            else:
                COORD = COORD[1:]
        COORD = COORD.replace(". "," ")
        if ", " not in COORD and ",-" not in COORD and " " in COORD:
            COORD = COORD.replace(" ",".")
        COORD = COORD.replace('"','').replace(" ","").replace(",",".").replace(";",".")
        COORD = COORD.split(".")
        COORD_X = COORD[0] + "." + COORD[1]
        COORD_Y = COORD[2] + "." + COORD[3]

        sitio = Sitio(id_enlace=ID,organismo=ORG,dependencia=DEP,proveedor=PROV,tecnologia=TEC,bw_dw=BW_DW,bw_up=BW_UP,coord_x=COORD_X,coord_y=COORD_Y)
        sitio.save()

        print("Creado sitio " + ID)