select
'1009' as codigo_iess,
dense_rank() over (order by tp.idregistro) as num_publicacion,
tipo_p.nombre as tipo_publicacion,
tp.idregistro as codigo_unico_publicacion,
(case tp.tipo_documento
    when 'ABS' then
    'Abstract/Resumen'
    when 'CMP' then
    'Artículo completo'
    else
    tp.tipo_documento
end) as tipo_documento,
tipo_estado.nombre as estado,
tp.titulo as titulo_publicacion,
coalesce(tp.link,'') as link_publicacion,
coalesce(tp.doi,'') as doi,
coalesce((select string_agg(coalesce(concat(t.nombre, (case tibi.origen when 'PROYECCION' THEN ' <-Proyección' else '' end)),''), ',') from esq_publicacion.tbl_instrumento_bases_indexadas tibi
        inner join esq_catalogos.tipo t on t.idtipo = tibi.id_tipo_bd_indexada
                and tibi.id_publicacion_instrumento = tp.id_publicacion_instrumento
                and (tibi.fecha_desde is null or (tibi.fecha_desde is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) >= tibi.fecha_desde))
                and (tibi.fecha_hasta is null or (tibi.fecha_hasta is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) <= tibi.fecha_hasta))
                ),'') as base_datos_indexada,
coalesce(tp.rango_paginas,'') as rango_paginas,
tp.revista_numero as numero_revista,
tpi.idregistro as codigo_unico_instrumento,
tpi.nombre as nombre_instrumento,
tpi.codigo_isbn,
tpi.codigo_isbn_online,
tpi.codigo_issn,
tpi.codigo_issn_online,
tpi.link as link_instrumento,
tpi.revista_arbitrada as revision_pares,
tpi.editor_compilador,
(case tpi.estado_revision
        when 'AP' then 'APROBADO POR VICEDECANO'
        when 'ER' then 'EN REVISION'
        when 'AC' then 'A CORRECCION'
        when 'ED' then 'EN DESARROLLO'
        when 'VDD' then 'VALIDADO POR EVALUACIÓN'
        else tpi.estado_revision
end) as estado_revision_instrumento_detalle,
coalesce(tpi.total_capitulos_libro::text,'') as total_capitulos_libro,
coalesce((select string_agg(coalesce(concat(tifdi.factor_impacto,(case tifdi.origen when 'PROYECCION' THEN ' <-Proyección' else '' end)), ''), '--') from esq_publicacion.tbl_instrumento_factor_de_impacto tifdi
                where tifdi.id_publicacion_instrumento = tp.id_publicacion_instrumento
                and (tifdi.fecha_desde is null or (tifdi.fecha_desde is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) >= tifdi.fecha_desde))
                and (tifdi.fecha_hasta is null or (tifdi.fecha_hasta is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) <= tifdi.fecha_hasta))
                ),'') as factor_impacto,
coalesce((select string_agg(coalesce(concat(tifdi.escala, ' (', tifdi.tipo_escala ,') ', (case tifdi.origen when 'PROYECCION' THEN ' <-Proyección' else '' end)), ''), '--') from esq_publicacion.tbl_instrumento_factor_de_impacto tifdi
                where tifdi.id_publicacion_instrumento = tp.id_publicacion_instrumento
                and (tifdi.fecha_desde is null or (tifdi.fecha_desde is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) >= tifdi.fecha_desde))
                and (tifdi.fecha_hasta is null or (tifdi.fecha_hasta is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) <= tifdi.fecha_hasta))
                ),'') as quartil,
coalesce(tp.fecha_publicacion::date, coalesce(tp.fecha_presentacion::date, coalesce(tp.fecha_aceptacion::date, null))) as fecha,
tipo_sub.nombre as campo_detallado,
(case tp.estado_revision
        when 'AP' then 'APROBADO POR VICEDECANO'
        when 'ER' then 'EN REVISION'
        when 'AC' then 'A CORRECCION'
        when 'ED' then 'EN DESARROLLO'
        when 'VDD' then 'VALIDADO POR EVALUACIÓN'
        else tp.estado_revision
end) as estado_revision_detalle,
coalesce(tili.linea_investigacion,'') as linea_investigacion,
(case tp.origen_publicacion
         when 'PINV' then
            'Proyecto de Investigación'
         when 'PVINC' then
            'Proyecto de Vinculación'
         when 'TIT' then
            'Proyecto de Titulación'
         when 'PP' then
              'Pasantía'
         when 'GC' then
                 'Gestión Curricular'
         when 'NG' then
                 'Ninguno'
         else
                 tp.origen_publicacion
         end
) as tipo_fuente,
(case tp.origen_publicacion
         when 'PINV' then
        (select tip.titulo_proyecto from esq_investigacion.tbl_inv_proyectos tip where tip.idregistro = tp.id_origen_publicacion)
         when 'PVINC' then
         (SELECT datos.nombre
                           FROM dblink(f_conexion_db_link('ppp_sistema')::text, '
                                select pro.id_proyecto,
                                pro.nombre
                                from esq_vinculacion.tbl_proyecto pro
                                                '::text) datos(id_proyecto bigint, nombre character varying(500)) where datos.id_proyecto = tp.id_origen_publicacion)
         when 'TIT' then
          (select ttt.tema from esq_titulacion.tbl_trab_titulacion ttt where ttt.idregistro = tp.id_origen_publicacion)
         when 'PP' then
                 (SELECT datos.nombre_empresa
                           FROM dblink(f_conexion_db_link('ppp_sistema')::text, '
                                select ppp.id_pasantia,
                                ep.nombre_empresa as nombre_empresa
                                from tbl_pasantia ppp
                                        left join
                                        tbl_empresa ep on (ppp.id_empresa = ep.id_empresa)
                                                '::text) datos(idpasantia_ppp bigint, nombre_empresa character varying(500)) where datos.idpasantia_ppp = tp.id_origen_publicacion)
         when 'GC' then
                 tp.origen_descripcion
         when 'NG' then
                 ''
         else
                 tp.origen_publicacion
         end
) as nombre_fuente,
tpp.idregistro as codigo_unico_persona_publicacion,
(case when p.idpersonal <> -1 then
    p.cedula
    when ta.idregistro <> -1 then
    ta.numero_documento
    else ''
end ) as identificacion,
(case when p.idpersonal <> -1 then
    concat(p.apellido1,' ', p.apellido2, ' ', p.nombres)
when ta.idregistro <> -1 then
    concat(ta.apellido1,' ', ta.apellido2, ' ', ta.nombres)
else ''
end ) as participacion,
(case when p.idpersonal <> -1 then
    'UNIVERSIDAD TÉCNICA DE MANABÍ'
    when ta.idregistro <> -1 then
        coalesce(pu.nombre,'')
    else ''
end ) as universidad,
tpp.orden,
tpp.tiene_filiacion  as filiacion,
(case when p.idpersonal <> -1 then
    'Personal UTM'
when ta.idregistro <> -1 then
    'Externo'
else ''
end ) as tipo_persona,
/*
(case
    when tp.idregistro >= 7092 and tp.idregistro <= 7791 then
    'Marlene'
    when tp.idregistro >= 7792 and tp.idregistro <= 8065 then
    'Diego 1'
    when tp.idregistro >= 8078 and tp.idregistro <= 8242 then
    'Diego 2'
    else
    'Desconocido'
end
) as origen,
*/
(case when (select count(t.idtipo) from esq_publicacion.tbl_instrumento_bases_indexadas tibi
        inner join esq_catalogos.tipo t on t.idtipo = tibi.id_tipo_bd_indexada
                and tibi.id_publicacion_instrumento = tp.id_publicacion_instrumento
                and (tibi.fecha_desde is null or (tibi.fecha_desde is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) >= tibi.fecha_desde))
                and (tibi.fecha_hasta is null or (tibi.fecha_hasta is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) <= tibi.fecha_hasta))
                and (t.idtipo) in (2771, 2777)
            ) > 0  then
            'ALTO IMPACTO'
    when (select count(t.idtipo) from esq_publicacion.tbl_instrumento_bases_indexadas tibi
        inner join esq_catalogos.tipo t on t.idtipo = tibi.id_tipo_bd_indexada
                and tibi.id_publicacion_instrumento = tp.id_publicacion_instrumento
                and (tibi.fecha_desde is null or (tibi.fecha_desde is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) >= tibi.fecha_desde))
                and (tibi.fecha_hasta is null or (tibi.fecha_hasta is not null and coalesce(tp.fecha_publicacion, coalesce(tp.fecha_presentacion, coalesce(tp.fecha_aceptacion, null))) <= tibi.fecha_hasta))
                and (t.idtipo) not in (2771, 2777)
            ) > 0  then
            'REGIONAL'
                 else
                 'SIN INDEXACIÓN'
end) as clasificacion_publicacion,
tp.fecha_registro,
f.nombre  as nombre_facultad,
d.nombre as nombre_departamento,
pa.nombre as nombre_periodo
from esq_publicacion.tbl_publicacion tp
inner join esq_publicacion.tbl_publicacion_persona tpp on (tp.idregistro = tpp.id_publicacion)
inner join esq_publicacion.tbl_publicacion_instrumento tpi  on tpi.idregistro  = tp.id_publicacion_instrumento
left join esq_datos_personales.personal p on (p.idpersonal = tpp.id_personal and p.idpersonal != -1)
left join esq_publicacion.tbl_publicacion_autor_externo ta on (ta.idregistro = tpp.id_autor_externo and ta.idregistro != -1)
left join esq_datos_personales.p_universidad pu on ( pu.iduniversidad = ta.id_institucion_proviene and ta.idregistro != -1)
left join esq_catalogos.tipo tipo_p on tipo_p.idtipo = tp.id_tipo_publicacion
left join esq_catalogos.tipo tipo_estado on tipo_estado.idtipo = tp.id_tipo_estado_publicacion
left join esq_catalogos.tipo tipo_sub on tipo_sub.idtipo = tp.id_tipo_subarea_conocimiento_especifica
left join esq_catalogos.tipo tipo_part on tipo_part.idtipo = tp.id_tipo_participacion
left join esq_investigacion.tbl_inv_lineas_investigacion tili on tili.idregistro = tp.id_linea_investigacion
left join esq_distributivos.distribucion_administrativa_resumen dar on dar.id_personal = p.idpersonal and dar.habilitado = 'S' and dar.id_personal >= 1 and dar.iddepartamento >= 1 and dar.idperiodo_academico = $P!{ID_PERIODO_ACADEMICO}  and (dar.iddepartamento = $P!{ID_DEPARTAMENTO} or $P!{ID_DEPARTAMENTO} = 0)
left join esq_periodos_academicos.periodo_academico pa on (pa.idperiodo = dar.idperiodo_academico)
left join esq_distributivos.departamento d on (d.iddepartamento = dar.iddepartamento)
left join esq_inscripciones.facultad f on f.idfacultad = d.idfacultad and (d.idfacultad = $P!{ID_FACULTAD}  or $P!{ID_FACULTAD} = 0)
where f.idfacultad = 2 --tp.idregistro in ($P!{ID_PUBLICACIONES_LIST})
order by tp.idregistro