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]