import xlrd
from formulario.models import Sitio

def run():
    MALAS_COORD = []
    try:
        doc = xlrd.open_workbook("scripts/GLPI.xlsx")
    except:
        print("No se encontró la planilla GLPI.xlsx")
        exit()
    hoja = doc.sheet_by_index(0)
    filas = hoja.nrows
    cols = hoja.ncols

    id_i = -1
    org_i = -1
    rep_i = -1
    dep_i = -1
    prov_i = -1
    tec_i = -1
    bw_dw_i = -1
    bw_up_i = -1
    coord_i = -1
    est_i = -1
    for i in range(cols):
        celda = str(hoja.cell_value(0,i))
        if celda == "Nombre":
            id_i = i
        if "Organismo" in celda:
            org_i = i
        if "Repartici" in celda:
            rep_i = i
        if "Dependencia" in celda:
            dep_i = i
        if "Proveedor" in celda:
            prov_i = i
        if "Tecnolog" in celda:
            tec_i = i
        if "BW " in celda.upper() and "UP" in celda.upper():
            bw_dw_i = i
        if "BW " in celda.upper() and "UP" in celda.upper():
            bw_up_i = i
        if "Coordenadas" in celda:
            coord_i = i
        if "Estado" in celda:
            est_i = i
    if id_i == -1 or org_i == -1 or rep_i == -1 or dep_i == -1 or prov_i == -1 or tec_i == -1 or bw_dw_i == -1 or bw_up_i == -1 or coord_i == -1 or est_i == -1:
        print("No se encontró alguno de los campos necesarios")
        exit()

    print("Eliminando base de datos actual")
    for s in Sitio.objects.all():
        s.delete()

    for i in range(filas):
        if i == 0:
            continue
        ID = str(hoja.cell_value(i,id_i)).split(".")[0]
        ORG = str(hoja.cell_value(i,org_i))[:30]
        REP = str(hoja.cell_value(i,rep_i))[:30]
        DEP = str(hoja.cell_value(i,dep_i))[:30]
        PROV = str(hoja.cell_value(i,prov_i))[:30]
        TEC = str(hoja.cell_value(i,tec_i))[:30]
        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))

        if COORD == "":
            continue

        COORDS = reparar_coords(COORD).split(" ")
        try:
            COORD_X = float(COORDS[0])
            COORD_Y = float(COORDS[1])
        except:
            MALAS_COORD.append(COORD)
            continue
        EST = str(hoja.cell_value(i,est_i))
        if "Alta" not in EST:
            continue

        sitio = Sitio(id=i,id_enlace=ID,organismo=ORG,reparticion=REP,dependencia=DEP,proveedor=PROV,tecnologia=TEC,bw_dw=BW_DW,bw_up=BW_UP,coord_x=COORD_X,coord_y=COORD_Y,estado=EST)
        sitio.save()
        #print(ID + " guardado")
    for m in MALAS_COORD:
        print("MALA COORD: " + m)
    print("Fin. Cantidad de sitios: " + str(len(Sitio.objects.all())))

def reparar_coords(C):
    COORD = C
    if "Itinerante" in COORD:
        return ""
    if "W" in COORD:
        print("MAL FORMATO:" + COORD)
        return ""
    while len(COORD) > 0 and COORD[0] != "-":
        print("EMPIEZA MAL:" + COORD)
        COORD = COORD[1:]
        print("     |" + COORD)
    if ", " in COORD:
        COORD = COORD.replace(", "," ")
    if " " not in COORD:
        print("NO TIENE 2 COORDENADAS:" + COORD)
        if ",-" in COORD:
            COORD = COORD.replace(",-",", -")
            print("ARREGLADAS: " + COORD)
        else:
            return ""
    if COORD == "" or COORD == "0":
        print("ES NULA: [" + COORD + "]")
        return ""
    while "- " in COORD:
        print("TIENE UN ESPACIO ENTRE EL MENOS Y EL NÚMERO: " + COORD)
        COORD = COORD.replace("- ","-")
        print("     |" + COORD)

    COORD_AUX = []
    for i in COORD.split(" "):
        if i != "":
            COORD_AUX.append(i)
    #print("     " + str(COORD_AUX))
    for i in range(2):
        if COORD_AUX[i][0] != "-":
            COORD_AUX[i] = "-" + COORD_AUX[i]
        if COORD_AUX[i][3] != ".":
            print("REPARANDO:" + COORD + " - " + COORD_AUX[i])
            if COORD_AUX[i][3] == ",":
                COORD_AUX[i] = COORD_AUX[i].replace(",",".").replace(". ",", ")
            else:
                COORD_AUX[i] = COORD_AUX[i][:3] + "." + COORD_AUX[i][3:]
            print(COORD_AUX[i])
    COORD = COORD_AUX[0] + " " + COORD_AUX[1]
    return COORD