def exportar_listados( fase='CPV2017'): conn=conx.Conexion() cursor = conn.cursor() sql = """ select ccdd from dbo.marco_departamento b where fase='{fase}' """.format(fase=fase) cursor.execute(sql) for row in cursor: idsubdep = row[0] list_subprovincias = listar_subprovincias(idsubdep) informacion=obtener_informacion_reporte(ccdd=idsubdep, ccpp='', fase='CPV2017') numsubdep=informacion[0][0] if len(informacion[1]) > 0: out_final = os.path.join(path_urbano_listados_dep, u'{}-{}.pdf'.format(idsubdep,numsubdep)) listado.listado_depart_prov(informacion=informacion,nivel=1,output=out_final) for idsubprov in list_subprovincias: informacion_2=obtener_informacion_reporte(ccdd=idsubdep,ccpp=idsubprov, fase='CPV2017') if informacion_2[1]>0: numsubprov = informacion_2[0][1] out_final = os.path.join(path_urbano_listados_prov, u'{}{}-{}.pdf'.format(idsubdep,idsubprov,numsubprov)) listado.listado_depart_prov(informacion=informacion_2, nivel=2, output=out_final)
def actualizar_zona(ubigeo, zona, flag, fase='CPV2017'): c = conx.Conexion() cursor = c.cursor() sql = """update b set b.flag_imp_total={flag} from dbo.MARCO_ZONA b where b.ubigeo='{ubigeo}' and b.zona='{zona}' and b.fase='{fase}' """.format(ubigeo=ubigeo, zona=zona, flag=flag, fase=fase) cursor.execute(sql) c.commit() c.close
def crear_merge(ubigeo,zona,fase='CPV2017'): conn = conx.Conexion() cursor = conn.cursor() sql_subzona = """ select a.ruta_croq from subzona a where ubigeo='{}' and zona='{}' and fase='{}' and cant_pea=1 """.format(ubigeo,zona,fase) cursor.execute(sql_subzona) list_subzona=[x[0] for x in cursor] sql_seccion = """ select a.ruta_croq from segm_u_seccion a where ubigeo='{}' and zona='{}' and fase='{}' """.format(ubigeo, zona, fase) cursor.execute(sql_seccion) list_seccion = [x[0] for x in cursor] sql_aeu = """ select a.ruta_croq from segm_u_aeu a where ubigeo='{}' and zona='{}' and fase='{}' """.format(ubigeo, zona, fase) cursor.execute(sql_aeu) list_aeu = [x[0] for x in cursor] legajo_aeu=list_aeu legajo_aeu.sort() legajo_seccion=[] legajo_seccion.extend(list_seccion) legajo_seccion.extend(list_aeu) legajo_seccion.sort() legajo_zona = [] legajo_zona.extend(list_subzona) legajo_zona.extend(list_seccion) legajo_zona.sort() out_aeu="{path_ini}\\{ubigeo}\\{zona}\\{ubigeo}_{zona}_aeu.pdf".format(path_ini=path_inicial,ubigeo=ubigeo,zona=zona) out_secc = "{path_ini}\\{ubigeo}\\{zona}\\{ubigeo}_{zona}_seccion.pdf".format(path_ini=path_inicial, ubigeo=ubigeo, zona=zona) out_zona = "{path_ini}\\{ubigeo}\\{zona}\\{ubigeo}_{zona}_zona.pdf".format(path_ini=path_inicial, ubigeo=ubigeo, zona=zona) list_legajos=[[legajo_aeu,out_aeu ],[legajo_seccion,out_secc],[legajo_zona,out_zona]] print out_aeu print out_secc print out_zona for list in list_legajos: mergePDF(list[0],list[1]) conn.close()
def procesar_listado_estudiantes(): c = conx.Conexion() cursor = c.cursor() sql = """ begin update subzona set flag_selec_estudiantes=0 update a set a.flag_selec_estudiantes=1 from subzona a inner join ( SELECT distinct c.ubigeo,c.zona,c.subzona FROM CPV_SEGMENTACION_GDB.sde.TB_CPV0301_VIVIENDA_U A inner join CPV_SEGMENTACION.DBO.SEGM_U_VIV B ON A.UBIGEO=B.UBIGEO COLLATE DATABASE_DEFAULT AND A.ZONA =B.ZONA COLLATE DATABASE_DEFAULT AND A.MANZANA=B.MANZANA COLLATE DATABASE_DEFAULT AND A.ID_REG_OR=B.ID_REG_OR AND B.FASE='CPV2017' inner join CPV_SEGMENTACION.DBO.SEGM_U_AEU C ON B.UBIGEO=C.UBIGEO COLLATE DATABASE_DEFAULT AND B.ZONA=C.ZONA COLLATE DATABASE_DEFAULT AND B.AEU=C.AEU COLLATE DATABASE_DEFAULT AND C.FASE='CPV2017' WHERE A.P29 IN (1,3) AND ((isnull(A.P33_1A_N,0)<>0 ) or ( isnull(A.P33_1B_N,0)<> 0 ) or ( isnull(A.P33_1C_N,0)<> 0 ) ) ) b on a.ubigeo=b.ubigeo and a.zona=b.zona and a.subzona=b.subzona update a set flag_selec_estudiantes=1 from (select * from subzona ) a inner join (select distinct ubigeo,zona from subzona where subzona<>0 and flag_selec_estudiantes=1 ) b on a.ubigeo=b.ubigeo and a.zona=b.zona where a.subzona=0 select ubigeo,zona,subzona from CPV_SEGMENTACION.DBO.SUBZONA where flag_selec_estudiantes=1 AND fase='{}' order by 1,2 end """.format(fase) print sql cursor.execute(sql) for row in cursor: ubigeo = row[0] zona = row[1] subzona = row[2] print ubigeo, zona, subzona exportar_listado_estudiantes(ubigeo, zona, subzona) c.close()
def listar_distrito_operativos(ubigeo, fase): conn = conx.Conexion() cursor = conn.cursor() sql = """select b.ubigeo,b.id from dbo.DISTRITO_OPE b where b.ubigeo='{}' and b.FASE='{}' """.format( ubigeo, fase) cursor.execute(sql) list_dist_ope = [] for row in cursor: list_dist_ope.append(row[1]) return list_dist_ope conn.close()
def ObtenerReporteDistrital(ubigeo, distope, fase): conn = conx.Conexion() cursor = conn.cursor() if distope != '00': sql_query_cabecera = """ select a.ubigeo,a.ccdd,a.nombdep ,a.ccpp,a.nombprov,a.ccdi,a.nombdist,b.nombdistope from marco_distrito a inner join dbo.DISTRITO_OPE b on a.ubigeo=b.ubigeo where b.id='{}{}' and b.FASE='{}' """.format( ubigeo, distope, fase) else: sql_query_cabecera = """ select a.ubigeo,a.ccdd,a.nombdep ,a.ccpp,a.nombprov,a.ccdi,a.nombdist,a.nombdist nombdistope from marco_distrito a where a.ubigeo='{}' and a.FASE='{}' """.format(ubigeo, fase) cursor.execute(sql_query_cabecera) cabecera = [] for row in cursor: cabecera = row sql_query_data = """ EXEC USP_REPORTE_DISTRITAL_TEMP '{}','{}','{}' """.format(ubigeo, distope, fase) cursor.execute(sql_query_data) data = [] for row in cursor: data.append(row) sql_query_resumen = """exec USP_RESUMEN_DISTRITAL_TEMP '{}','{}','{}'""".format( ubigeo, distope, fase) cursor.execute(sql_query_resumen) resumen = [] for row in cursor: resumen = row conn.commit() conn.close() return [cabecera, data, resumen]
def listar_subprovincias(ccdd,fase='CPV2017'): conn=conx.Conexion() cursor = conn.cursor() #sql="""select idsubprov_censal from dbo.SUBPROVINCIA_CENSAL b # where b.idsubdep_censal='{}' """.format(idsubdep,fase) sql = """select ccpp from dbo.marco_provincia b where ccdd='{ccdd}' and fase='{fase}' """.format(ccdd=ccdd, fase=fase) cursor.execute(sql) list_sub_prov = [] for row in cursor: list_sub_prov.append(row[0]) return list_sub_prov conn.close()
def actualizar_flag_insercion(list_zonas_sql): conn = conx.Conexion() cursor = conn.cursor() sql_query = """ update marco_zona set flag_data_insert=1 where flag_data_insert=0 and flag_proc_segm=1 and fase='CPV2017' and ubigeo+zona in ({ccdd}) """.format(ccdd=list_zonas_sql) print sql_query cursor.execute(sql_query) data = [] for row in cursor: data.append(["{}".format(row[0]), "{}".format(row[1])]) conn.commit() conn.close() return data
def actualizar_monitoreo(): distrito = "" conn = conx.Conexion() cursor = conn.cursor() sql_query = """ select distinct a.ubigeo from ( select distinct ubigeo from dbo.TB_VIVIENDA_U union all select distinct ubigeo from dbo.TB_VIVIENDA_r) a where ubigeo>'050506' order by a.ubigeo """ cursor.execute(sql_query) for row in cursor.fetchall(): distrito = "{}".format(row[0]) where = expresion.Expresion_2([[distrito]], [["UBIGEO", "TEXT"]]) print where importar_tablas(where) insertar_registros([distrito]) continue #sql_query2="""UPDATE A # SET A.AREA=2 # FROM sde.CARTO_ccpp A # # UPDATE A # SET A.AREA=1 # FROM sde.CARTO_ccpp A # INNER JOIN # (SELECT DISTINCT UBIGEO ,CODCCPP FROM [LKN_93_SEGM].[CPV_SEGMENTACION].DBO.MARCO_ZONA) B ON A.UBIGEO=B.UBIGEO AND A.CODCCPP=B.CODCCPP""" # #cursor.execute(sql_query2) conn.commit() conn.close()
def obtener_lista_zonas_insercion(): conn = conx.Conexion() cursor = conn.cursor() sql_query = """ select ubigeo,zona from marco_zona where flag_data_insert=0 and flag_proc_segm=1 and fase='CPV2017' --and flag_nuevo=1 --and ubigeo +zona in ('15010800301') """ print sql_query cursor.execute(sql_query) data=[] for row in cursor: data.append(["{}".format(row[0]),"{}".format(row[1])]) conn.commit() conn.close() return data
def exportar_emp_especial_dist(ubigeo,fase='CPV2017'): c = conx.Conexion() cursor = c.cursor() list_paths_dist=[] sql_zona = """ begin select ubigeo,zona,subzona from CPV_SEGMENTACION.dbo.SUBZONA where fase='{fase}' and ubigeo='{ubigeo}' order by 1,2,3 end """.format(fase=fase, ubigeo=ubigeo) cursor.execute(sql_zona) list_paths_zonas=[] for row in cursor: ubigeo = row[0] zona = row[1] subzona = row[2] list_paths_zonas=exportar_emp_especial_zona(ubigeo=ubigeo,zona=zona,subzona=subzona)[:] list_paths_dist.extend(list_paths_zonas) path_out_final=os.path.join(path_emp_esp,'{}_emp_especial.pdf'.format(ubigeo)) if len(list_paths_dist)>0: utilidades.mergePDF(list_paths_dist,path_out_final) print 'listado de pdfs', list_paths_dist for path in list_paths_dist: os.remove(path) c.close()
for row in cursor: list_dist_ope.append(row[1]) return list_dist_ope conn.close() def exportar_listado_urbano_distrito(ubigeo, fase): list_dist_ope = listar_distrito_operativos(ubigeo, fase) print list_dist_ope for iddistope in list_dist_ope: path_pdf = "{}\\{}.pdf".format(path_urbano_croquis_listado, iddistope) distope = iddistope[6:] informacion = ObtenerReporteDistrital(ubigeo, distope, fase)[:] listado.ListadoDistrito(informacion, path_pdf) c = conx.Conexion() cursor = c.cursor() sql = """select distinct b.ubigeo from GEODB_CPV_SEGM.sde.SEGM_RUTA_PRUEBA b where b.ubigeo in ('150108') order by b.ubigeo """ cursor.execute(sql) fase = 'CPV2017' for row in cursor: ubigeo = row[0] print ubigeo exportar_listado_urbano_distrito(ubigeo, fase) c.close()
def obtener_informacion_reporte(ccdd,ccpp,fase='CPV2017'): conn = conx.Conexion() cursor = conn.cursor() data=[] cabecera=[] #if idsubprov == '': # sql_query_cab = """ select nombdep_censal,nombsubdep_censal from dbo.SUBDEPARTAMENTO_CENSAL where idsubdep_censal='{idsubdep}' """.format(idsubdep=idsubdep) #else: # sql_query_cab = """ select nombdep_censal,nombprov_censal,nombsubprov_censal from dbo.SUBPROVINCIA_CENSAL where idsubdep_censal='{idsubdep}' and idsubprov_censal='{idsubprov}' """.format( # idsubdep=idsubdep,idsubprov=idsubprov) if ccpp == '': sql_query_cab = """ select nombdep,'' nombsubdep_censal from dbo.MARCO_DEPARTAMENTO where ccdd='{ccdd}' and fase='{fase}' """.format(ccdd=ccdd,fase=fase) where = """ where a.fase='{fase}' and a.ccdd='{ccdd}' """.format(ccdd=ccdd, fase=fase) else: sql_query_cab = """ select nombdep,nombprov,'' nombsubprov_censal from dbo.MARCO_PROVINCIA where ccdd='{ccdd}' and ccpp='{ccpp}' and fase='{fase}' """.format(ccdd=ccdd ,ccpp=ccpp,fase=fase) where = """ where a.fase='{fase}' and a.ccdd='{ccdd}' and a.ccpp='{ccpp}' """.format(ccdd=ccdd, ccpp=ccpp, fase=fase) cursor.execute(sql_query_cab) for row in cursor: cabecera=row #if idsubprov == '': # where=""" where a.fase='CPV2017' and a.idsubdep_censal='{idsubdep}' """.format(idsubdep=idsubdep) #else: # where = """ where a.fase='CPV2017' and a.idsubdep_censal='{idsubdep}' and a.idsubprov_censal='{idsubprov}' """.format(idsubdep=idsubdep,idsubprov=idsubprov) sql_query_data = """ SELECT '','','','','','',SUM(a.n_zonas) n_zonas,SUM(a.n_subzonas) n_subzonas,SUM(a.n_secc)n_secc,SUM(a.n_aeu)n_aeu ,SUM(a.n_mzs)n_mzs,SUM(a.n_viv)n_viv,'000000' ubigeo from ( select A.CCDD,A.CCPP,A.NOMBPROV provincia,'' subprovincia,a.nombdist distrito,b.nombdistope subdistrito,count(distinct c.idzona) n_zonas, sum(c.cant_subzonas)n_subzonas,sum(c.cant_secc_u) n_secc,sum(c.cant_ae_u)n_aeu, sum(c.cant_mzs_marco) n_mzs,sum(c.cant_viv_marco) n_viv,A.UBIGEO from marco_distrito a inner join distrito_ope b on a.ubigeo=b.ubigeo and a.fase=b.fase inner join marco_zona c on c.ubigeo=b.ubigeo and c.distope=b.distope and b.fase=c.fase {where} group by A.CCDD,A.CCPP,A.NOMBPROV, a.nombdist ,b.nombdistope,a.ubigeo ) a union all select A.CCDD,A.CCPP,A.NOMBPROV provincia,'' subprovincia,a.nombdist distrito,b.nombdistope subdistrito,count(distinct c.idzona) n_zonas, sum(c.cant_subzonas)n_subzonas,sum(c.cant_secc_u) n_secc,sum(c.cant_ae_u)n_aeu, sum(c.cant_mzs_marco) n_mzs,sum(c.cant_viv_marco) n_viv,A.UBIGEO from marco_distrito a inner join distrito_ope b on a.ubigeo=b.ubigeo and a.fase=b.fase inner join marco_zona c on c.ubigeo=b.ubigeo and c.distope=b.distope and b.fase=c.fase {where} group by A.CCDD,A.CCPP,A.NOMBPROV, a.nombdist ,b.nombdistope,a.ubigeo order by 13 """.format(where=where) cursor.execute(sql_query_data) for row in cursor: data.append(row) conn.commit() conn.close() return [cabecera,data]