#!/usr/bin/env python3

# SCRIPT DE PARSEO DEL EXCEL DE HALLIBURTON
try:
    import sys
    import re
    import openpyxl
    import json

    #---- FUNCION QUE PARSEA LAS SECCIONES INDIVIDUALMENTE
    def parsear_seccion(documento,seccion):
        data_all_documents = {}
        #
        if(documento == 'Datos_Personales' ):
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('Nombre') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('nombre_DP') is None:
                            data_all_documents['nombre_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Apellido') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('apellido_DP') is None:
                            data_all_documents['apellido_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Dirección Residencial') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('direccion_DP') is None:
                            data_all_documents['direccion_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Barrio') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('barrio_DP') is None:
                            data_all_documents['barrio_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('House Number') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('nro_casa_DP') is None:
                            data_all_documents['nro_casa_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Country') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('pais_DP') is None:
                            data_all_documents['pais_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Country') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('pais_DP') is None:
                            data_all_documents['pais_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Provincia') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('provincia_DP') is None:
                            data_all_documents['provincia_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Ciudad') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('ciudad_DP') is None:
                            data_all_documents['ciudad_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Zip Code') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('codigo_postal_DP') is None:
                            data_all_documents['codigo_postal_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Telephone') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('telefono_DP') is None:
                            data_all_documents['telefono_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Cell Phone') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('celular_DP') is None:
                            data_all_documents['celular_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('E-mail') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('email_DP') is None:
                            data_all_documents['email_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Birth Date') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('fecha_nacimiento_DP') is None:
                            data_all_documents['fecha_nacimiento_DP'] = str(seccion[i+2]) + '/' + str(seccion[i+1]) + '/' + str(seccion[i+3])
                        continue
                    elif seccion_str.__contains__('Estado Civil') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('estado_civil_DP') is None:
                            data_all_documents['estado_civil_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Gender') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('genero_DP') is None:
                            data_all_documents['genero_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Nationality') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('nacionalidad_DP') is None:
                            data_all_documents['nacionalidad_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('City of Birth') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('ciudad_nacimiento_DP') is None:
                            data_all_documents['ciudad_nacimiento_DP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('2nd Nationality') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('segunda_nacionalidad_DP') is None:
                            data_all_documents['segunda_nacionalidad_DP'] = str(seccion[i+1])
                        continue
        elif( documento == 'Contacto_Emergencia' ):
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('Full Name') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('nombre_completo_CE') is None:
                            data_all_documents['nombre_completo_CE'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Phone') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('telefono_CE') is None:
                            data_all_documents['telefono_CE'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Zip Code') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('codigo_postal_CE') is None:
                            data_all_documents['codigo_postal_CE'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Full Address') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('direccion_completa_CE') is None:
                            data_all_documents['direccion_completa_CE'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Relationship') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('parentesco_CE') is None:
                            data_all_documents['parentesco_CE'] = str(seccion[i+1])
                        continue
        elif( documento == 'Documentos_Personales' ):
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('DNI') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('dni_DOCP') is None:
                            data_all_documents['dni_DOCP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Passport No') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('num_pasaporte_DOCP') is None:
                            data_all_documents['num_pasaporte_DOCP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Drivers License') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('licencia_conducir_DOCP') is None:
                            data_all_documents['licencia_conducir_DOCP'] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Category License') and not (str(seccion[i+1])).__contains__(':'):
                        if data_all_documents.get('categoria_licencia_DOCP') is None:
                            data_all_documents['categoria_licencia_DOCP'] = str(seccion[i+1])
                        continue
        elif( documento == 'Grupo_Familiar' ):
            cont_name = 0
            cont_fecha_nac = 0
            cont_nacionalidad = 0
            cont_parentesco = 0
            cont_dni = 0
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('Name') and not (str(seccion[i+1])).__contains__(':'):
                        cont_name += 1 
                        str_cont = str(cont_name)
                        if data_all_documents.get('nombre_GF_'+ str_cont) is None:
                            data_all_documents['nombre_GF_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Birth Date') and not (str(seccion[i+1])).__contains__(':'):
                        cont_fecha_nac += 1
                        str_cont = str(cont_fecha_nac)
                        if data_all_documents.get('fecha_nacimiento_GF_'+str_cont) is None:
                            data_all_documents['fecha_nacimiento_GF_'+str_cont] = str(seccion[i+2]) + '/' + str(seccion[i+1]) + '/' + str(seccion[i+3])
                        continue
                    elif seccion_str.__contains__('Nationality') and not (str(seccion[i+1])).__contains__(':'):
                        cont_nacionalidad += 1
                        str_cont = str(cont_nacionalidad) 
                        if data_all_documents.get('nacionalidad_GF_'+str_cont) is None:
                            data_all_documents['nacionalidad_GF_'+str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Type of Relationship') and not (str(seccion[i+1])).__contains__(':'):
                        cont_parentesco += 1
                        str_cont = str(cont_parentesco)
                        if data_all_documents.get('parentesco_GF_'+str_cont) is None:
                            data_all_documents['parentesco_GF_'+str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('DNI') and not (str(seccion[i+1])).__contains__(':'):
                        cont_dni += 1
                        str_cont = str(cont_dni)
                        if data_all_documents.get('dni_GF_'+str_cont) is None:
                            data_all_documents['dni_GF_'+str_cont] = str(seccion[i+1])
                        continue
            #
            data_all_documents['cant_reg'] = str(cont_name)
        elif( documento == 'Educacion' ):
            cont_ultimo_titulo = 0
            cont_fecha_finalizacion = 0
            cont_institucion = 0
            cont_pais = 0
            cont_city_estado = 0
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                range_list = i + 1
                seccionSTR = 0
                if(range_list == len(seccion)):
                    seccionSTR = seccion[i]
                else:
                    seccionSTR = seccion[i+1]
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('Last Degree') and not (str(seccion[i+1])).__contains__(':'):
                        cont_ultimo_titulo += 1 
                        str_cont = str(cont_ultimo_titulo)
                        if data_all_documents.get('ultimo_titulo_ED_'+ str_cont) is None:
                            data_all_documents['ultimo_titulo_ED_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('End Date') and not (str(seccion[i+1])).__contains__(':'):
                        cont_fecha_finalizacion += 1
                        str_cont = str(cont_fecha_finalizacion)
                        if data_all_documents.get('fecha_finalizacion_ED_'+ str_cont) is None:
                            data_all_documents['fecha_finalizacion_ED_'+ str_cont] = str(seccion[i+2]) + '/' + str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Institute') and not (str(seccion[i+1])).__contains__(':'):
                        cont_institucion += 1
                        str_cont = str(cont_institucion) 
                        if data_all_documents.get('institucion_ED_'+ str_cont) is None:
                            data_all_documents['institucion_ED_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Country') and not (str(seccion[i+1])).__contains__(':'):
                        cont_pais += 1
                        str_cont = str(cont_pais)
                        if data_all_documents.get('pais_ED_'+ str_cont) is None:
                            data_all_documents['pais_ED_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Ciudad y Estado') and not (str(seccionSTR)).__contains__(':'):
                        cont_city_estado += 1
                        str_cont = str(cont_city_estado)
                        if data_all_documents.get('ciudad_ED_'+ str_cont) is None:
                            data_all_documents['ciudad_ED_'+ str_cont] = str(seccion[i+1])
                        continue
        elif( documento == 'Laboral' ):
            cont_empresa = 0
            cont_cargo = 0
            cont_sector_ind = 0
            cont_contact_ref = 0
            cont_direccion_lb = 0
            cont_pais_lb = 0
            cont_ciudad_lb = 0
            cont_fecha_start_lb = 0
            cont_fecha_end_lb = 0
            
            for i in range(0,len(seccion)):
                seccion_str = str(seccion[i])
                range_list = i + 1
                seccionSTR = 0
                if(range_list == len(seccion)):
                    seccionSTR = seccion[i]
                else:
                    seccionSTR = seccion[i+1]
                if seccion_str.__contains__(':'):
                    if seccion_str.__contains__('Company') and not (str(seccion[i+1])).__contains__(':'):
                        cont_empresa += 1
                        str_cont = str(cont_empresa)
                        if data_all_documents.get('empresa_LB_'+ str_cont) is None:
                            data_all_documents['empresa_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Position') and not (str(seccion[i+1])).__contains__(':'):
                        cont_cargo += 1
                        str_cont = str(cont_cargo) 
                        if data_all_documents.get('cargo_LB_'+ str_cont) is None:
                            data_all_documents['cargo_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Industry') and not (str(seccion[i+1])).__contains__(':'):
                        cont_sector_ind += 1
                        str_cont = str(cont_sector_ind)
                        if data_all_documents.get('sector_industrial_LB_'+ str_cont) is None:
                            data_all_documents['sector_industrial_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Contact for References') and not (str(seccion[i+1])).__contains__(':'):
                        cont_contact_ref += 1
                        str_cont = str(cont_contact_ref)
                        if data_all_documents.get('contacto_referencia_LB_'+ str_cont) is None:
                            data_all_documents['contacto_referencia_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Adress') and not (str(seccion[i+1])).__contains__(':'):
                        cont_direccion_lb += 1
                        str_cont = str(cont_direccion_lb)
                        if data_all_documents.get('direccion_LB_'+ str_cont) is None:
                            data_all_documents['direccion_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Country') and not (str(seccion[i+1])).__contains__(':'):
                        cont_pais_lb += 1
                        str_cont = str(cont_pais_lb) 
                        if data_all_documents.get('pais_LB_'+ str_cont) is None:
                            data_all_documents['pais_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('City and State') and not (str(seccion[i+1])).__contains__(':'):
                        cont_ciudad_lb += 1
                        str_cont = str(cont_ciudad_lb)
                        if data_all_documents.get('ciudad_estado_LB_'+ str_cont) is None:
                            data_all_documents['ciudad_estado_LB_'+ str_cont] = str(seccion[i+1])
                        continue
                    elif seccion_str.__contains__('Start Date') and not (str(seccion[i+1])).__contains__(':'):
                        cont_fecha_start_lb += 1
                        str_cont = str(cont_fecha_start_lb)
                        if data_all_documents.get('fecha_inicio_LB_'+ str_cont) is None:
                            data_all_documents['fecha_inicio_LB_'+ str_cont] = str(seccion[i+2]) + '/' + str(seccion[i+1]) + '/' + str(seccion[i+3])
                        continue
                    elif seccion_str.__contains__('End Date') and not (str(seccionSTR)).__contains__(':'):
                        cont_fecha_end_lb += 1
                        str_cont = str(cont_fecha_end_lb)
                        if data_all_documents.get('fecha_finalizacion_LB_'+ str_cont) is None:
                            data_all_documents['fecha_finalizacion_LB_'+ str_cont] = str(seccion[i+2]) + '/' + str(seccion[i+1]) + '/' + str(seccion[i+3])
                        continue
        # return data_all_documents
        # return json.dumps(data_all_documents)
        return data_all_documents
        # print(seccion)

    #---- FUNCION QUE DEVUELVE LA SECCION DE DATOS PERSONALES
    def parsear_secciones_excel(worksheet):
        exp_datos_personales = re.compile(r"^PERSONAL\s?DATA")
        exp_contacto_emergencia = re.compile(r"^EMERGENCY\s?CONTACT\s?INFORMATION")
        exp_documentos_personales = re.compile(r"^PERSONAL\s?DOCUMENTS")
        exp_grupo_familiar = re.compile(r"^FAMILY\s?DEPENDENT\s?INFORMATION")
        exp_educacion = re.compile(r"^EDUCATION")
        exp_laboral = re.compile(r"^PREVIOUS\s?EMPLOYMENT")
        exp_final = re.compile(r"^TO\sBE\sCOMPLETED\sBY\sHALLIBURTON")
        #
        datos_personales = []
        contacto_emergencia = []
        documentos_personales = []
        grupo_familiar = []
        educacion = []
        laboral = []
        #
        ind_start_datos_personales = 0
        ind_end_datos_personales = 0
        #
        ind_start_contacto_emergencia = 0
        ind_end_contacto_emergencia = 0
        #
        ind_start_documentos_personales = 0
        ind_end_documentos_personales = 0
        #
        ind_start_grupo_familiar = 0
        ind_end_grupo_familiar = 0
        #
        ind_start_educacion = 0
        ind_end_educacion = 0
        #
        ind_start_laboral = 0
        ind_end_laboral = 0
        #
        for i in range(0,worksheet.max_row):
            #
            for col in worksheet.iter_cols(1,worksheet.max_column):
                if col[i].value is not None and exp_datos_personales.match(str(col[i].value)) and ind_start_datos_personales == 0:
                    ind_start_datos_personales = 1
                if col[i].value is not None and exp_contacto_emergencia.match(str(col[i].value)) and ind_start_contacto_emergencia == 0:
                    ind_start_contacto_emergencia = 1
                if col[i].value is not None and exp_documentos_personales.match(str(col[i].value)) and ind_start_documentos_personales == 0:
                    ind_start_documentos_personales += 1
                if col[i].value is not None and exp_grupo_familiar.match(str(col[i].value)) and ind_start_grupo_familiar == 0:
                    ind_start_grupo_familiar += 1
                if col[i].value is not None and exp_educacion.match(str(col[i].value)) and ind_start_educacion == 0:
                    ind_start_educacion += 1
                if col[i].value is not None and exp_laboral.match(str(col[i].value)) and ind_start_laboral == 0:
                    ind_start_laboral += 1
                #
                if col[i].value is not None and exp_contacto_emergencia.match(str(col[i].value)) and ind_start_datos_personales == 1:
                    ind_end_datos_personales = 1
                    ind_start_datos_personales = 2
                    continue
                if col[i].value is not None and exp_documentos_personales.match(str(col[i].value)) and ind_start_contacto_emergencia == 1:
                    ind_end_contacto_emergencia += 1
                    ind_start_contacto_emergencia += 1
                    continue
                if col[i].value is not None and exp_grupo_familiar.match(str(col[i].value)) and ind_start_documentos_personales == 1:
                    ind_end_documentos_personales += 1
                    ind_start_documentos_personales += 1
                    continue
                if col[i].value is not None and exp_educacion.match(str(col[i].value)) and ind_start_grupo_familiar == 1:
                    ind_end_grupo_familiar += 1
                    ind_start_grupo_familiar += 1
                    continue
                if col[i].value is not None and exp_laboral.match(str(col[i].value)) and ind_start_educacion == 1:
                    ind_end_educacion += 1
                    ind_start_educacion += 1
                    continue
                if col[i].value is not None and exp_final.match(str(col[i].value)) and ind_start_laboral == 1:
                    ind_end_laboral += 1
                    ind_start_laboral += 1
                    continue
                #
                if ind_start_datos_personales == 1 and ind_end_datos_personales == 0 and col[i].value is not None:
                    datos_personales.append(col[i].value)
                if ind_start_contacto_emergencia == 1 and ind_end_contacto_emergencia == 0 and col[i].value is not None:
                    contacto_emergencia.append(col[i].value)
                if ind_start_documentos_personales == 1 and ind_end_documentos_personales == 0 and col[i].value is not None:
                    documentos_personales.append(col[i].value)
                if ind_start_grupo_familiar == 1 and ind_end_grupo_familiar == 0 and col[i].value is not None:
                    grupo_familiar.append(col[i].value)
                if ind_start_educacion == 1 and ind_end_educacion == 0 and col[i].value is not None:
                    educacion.append(col[i].value)
                if ind_start_laboral == 1 and ind_end_laboral == 0 and col[i].value is not None:
                    laboral.append(col[i].value)

        #--
        result = {'Datos_Personales':datos_personales,'Contacto_Emergencia':contacto_emergencia,'Documentos_Personales':documentos_personales,'Grupo_Familiar':grupo_familiar,'Educacion':educacion,'Laboral':laboral}
        #--
        return result
    #---
    def main(argv):
        workbook = openpyxl.load_workbook(argv[1])
        worksheet = workbook.active
        #
        secciones = parsear_secciones_excel(worksheet)
        #
        respuesta = {}
        # print('\n')
        for key,value in secciones.items():
            # print(key)
            # print(value)
            # exit()
            # if(key == 'Datos_Personales' or key == 'Contacto_Emergencia' or key == 'Documentos_Personales' or key == 'Grupo_Familiar' or key == 'Educacion' or key == 'Laboral'):
            respuesta[key] = parsear_seccion(key,value)
        #
        print(json.dumps(respuesta))
    #----
    if __name__ == '__main__':
        main(sys.argv)
except Exception as e:
    print(e)