act funcion tutorias

CREATE OR REPLACE FUNCTION esq_ceac_planclase.f_historia_tutoria_periodo_docente(
    p_idperiodo integer,
    p_idpersonal bigint)
    RETURNS TABLE(r_docente character varying, r_departamento character varying, r_categoria character varying, r_dedicacion character varying, r_tutorados integer, r_tutorados_activos bigint, r_tutorados_pasivo bigint, r_tutorados_egresado bigint, r_tutorados_otros bigint, r_agendas_programadas integer, r_agendas_programadas_e bigint, r_agendas_programadas_d bigint, r_entrevistas_efectiva bigint, r_entrevistas_n_efectiva bigint, r_entrevistas_n_efectiva_d bigint, r_entrevistas_n_efectiva_e bigint, r_calificacion numeric, r_tutorias_evaluadas bigint, r_idfichero_hoja_vida_foto bigint, r_ta_agendas_programadas bigint, r_ta_agendas_programadas_e bigint, r_ta_agendas_programadas_d bigint, r_ta_entrevistas_efectiva bigint, r_ta_entrevistas_n_efectiva bigint, r_ta_entrevistas_n_efectiva_e bigint, r_ta_entrevistas_n_efectiva_d bigint, r_ta_calificacion numeric, r_ta_tutorias_evaluadas bigint)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
    ROWS 1000
AS $BODY$
DECLARE
v_docente VARCHAR := '';
v_departamento VARCHAR := '';
v_categoria VARCHAR := '';
v_dedicacion VARCHAR := '';
v_tutorados integer:=0;
v_tutorados_activos bigint;
v_tutorados_pasivo bigint;
v_tutorados_egresado bigint;
v_tutorados_otros bigint;
v_agendas_programadas integer:=0;
v_agendas_programadas_e bigint;
v_agendas_programadas_d bigint;
v_entrevistas_efectiva bigint;
v_entrevistas_n_efectiva bigint;
v_entrevistas_n_efectiva_d bigint;
v_entrevistas_n_efectiva_e bigint;
v_calificacion numeric;
v_tutorias_evaluadas bigint;
v_idfichero_hoja_vida_foto bigint;
v_ta_agendas_programadas bigint;
v_ta_agendas_programadas_e bigint;
v_ta_agendas_programadas_d bigint;
v_ta_entrevistas_efectiva bigint;
v_ta_entrevistas_n_efectiva bigint;
v_ta_entrevistas_n_efectiva_e bigint;
v_ta_entrevistas_n_efectiva_d bigint;
v_ta_calificacion numeric;
v_ta_tutorias_evaluadas bigint;
v_fecha_inicio date;
v_fecha_final date;
r RECORD;
BEGIN
select p.fecha_inicio, case EXTRACT(MONTH FROM p.fecha_final) when 2 then p.fecha_final + 31 when 8 then p.fecha_final + 31 else p.fecha_final end as fecha_final into v_fecha_inicio, v_fecha_final from esq_periodos_academicos.periodo_academico p where p.idperiodo = p_idperiodo;
select r_ndepartamento from esq_distributivos.f_historia_laboral_obtener(p_idperiodo, 0, p_idpersonal) into v_departamento;
select (select nombre from esq_distributivos.categoria_tipo ct where ct.idcategoria = t.r_idcategoria) into v_categoria from esq_distributivos.f_historia_laboral_obtener(p_idperiodo, 0, p_idpersonal) t;
select (select nombre from esq_distributivos.dedicacion_tipo ct where ct.iddedicacion = t.r_iddedicacion) into v_dedicacion from esq_distributivos.f_historia_laboral_obtener(p_idperiodo, 0, p_idpersonal) t;
/*tutorados*/
select 0 as t_tutorados into v_tutorados;
select count(*) into v_tutorados_activos from esq_tutorias.tutorado t where t.fecha BETWEEN v_fecha_inicio and v_fecha_final and t.idpersonal_docente = p_idpersonal and t.habilitado = 'S' and t.es_pasivo = 'N' and t.es_egresado = 'N';
select count(*) into v_tutorados_pasivo FROM esq_tutorias.ft_listar_tutorados_pasivos(p_idpersonal) t where t.r_fecha BETWEEN v_fecha_inicio and v_fecha_final;
select count(*) into v_tutorados_egresado from esq_tutorias.tutorado t where t.fecha BETWEEN v_fecha_inicio and v_fecha_final and t.idpersonal_docente = p_idpersonal and t.es_egresado = 'S';
select count(*) into v_tutorados_otros from esq_tutorias.tutorado t where t.fecha BETWEEN v_fecha_inicio and v_fecha_final and t.idpersonal_docente = p_idpersonal and t.habilitado = 'N' and t.es_pasivo = 'N' and t.es_egresado = 'N';

/*agenda*/
select 0 as agendas_programadas into v_agendas_programadas;
select count(*) into v_agendas_programadas_e from esq_tutorias.agenda a where a.fecha_sol_docente is null and coalesce(a.fecha_sol_estudiante, CURRENT_TIMESTAMP) BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_agendas_programadas_d from esq_tutorias.agenda a where a.fecha_sol_docente BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;

/*entrevistas*/
select count(e.idpersonal_estudiante) into v_entrevistas_efectiva from esq_tutorias.entrevista e  where e.idpersonal_docente = p_idpersonal and e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final;
select count(*) into v_entrevistas_n_efectiva from esq_tutorias.agenda a where a.id_tipo_estado in (2597, 2636) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_entrevistas_n_efectiva_d from esq_tutorias.agenda a where a.id_tipo_estado in (2597) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_entrevistas_n_efectiva_e from esq_tutorias.agenda a where a.id_tipo_estado in (2636) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;

/*evaluacion tutoria acompañamiento*/
select coalesce(round(avg(t.calificacion), 1), 0) as calificacion into v_calificacion
      from (
          select round(avg(ee.calificacion), 1) as calificacion, ee.id_entrevista
          from esq_tutorias.entrevista e inner join esq_tutorias.entrevista_evaluacion ee on ee.id_entrevista = e.id_entrevista
          where ee.calificacion > 0 and e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final and e.idpersonal_docente = p_idpersonal
          group by ee.id_entrevista
      ) t;

select count(t.calificacion) into v_tutorias_evaluadas
      from (
          select round(avg(ee.calificacion), 1) as calificacion, ee.id_entrevista
          from esq_tutorias.entrevista e inner join esq_tutorias.entrevista_evaluacion ee on ee.id_entrevista = e.id_entrevista
          where e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final and e.idpersonal_docente = p_idpersonal
          group by ee.id_entrevista
      ) t;

/*t. academica agenda*/
select count(*) into v_ta_agendas_programadas from esq_tutorias.ta_agenda a where a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente =p_idpersonal;
select count(*) into v_ta_agendas_programadas_e from esq_tutorias.ta_agenda a where a.fecha_sol_docente is null and coalesce(a.fecha_sol_estudiante, CURRENT_TIMESTAMP) BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_ta_agendas_programadas_d from esq_tutorias.ta_agenda a where a.fecha_sol_docente BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;

/*t. academica entrevistas*/
select count(e.idpersonal_estudiante) into v_ta_entrevistas_efectiva from esq_tutorias.ta_agenda e where e.id_tipo_estado = 2554 and e.idpersonal_docente = p_idpersonal and e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final;
select count(*) into v_ta_entrevistas_n_efectiva from esq_tutorias.ta_agenda a where a.id_tipo_estado in (2597, 2636) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_ta_entrevistas_n_efectiva_d from esq_tutorias.ta_agenda a where a.id_tipo_estado in (2597) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;
select count(*) into v_ta_entrevistas_n_efectiva_e from esq_tutorias.ta_agenda a where a.id_tipo_estado in (2636) and a.fecha BETWEEN v_fecha_inicio and v_fecha_final and a.idpersonal_docente = p_idpersonal;

/*evaluacion tutoria academica*/
select coalesce(round(avg(t.calificacion), 1), 0) into v_ta_calificacion
      from (
          select round(avg(ee.calificacion), 1) as calificacion, ee.id_entrevista
          from esq_tutorias.ta_agenda e inner join esq_tutorias.ta_entrevista_evaluacion ee on ee.id_entrevista = e.id_agenda
          where ee.calificacion > 0 and e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final and e.id_tipo_estado = 2554 and e.idpersonal_docente = p_idpersonal
          group by ee.id_entrevista
      ) t;

select count(t.calificacion) into v_ta_tutorias_evaluadas
      from (
          select round(avg(ee.calificacion), 1) as calificacion, ee.id_entrevista
          from esq_tutorias.ta_agenda e inner join esq_tutorias.ta_entrevista_evaluacion ee on ee.id_entrevista = e.id_agenda
          where e.fecha_registro BETWEEN v_fecha_inicio and v_fecha_final and e.id_tipo_estado = 2554 and e.idpersonal_docente = p_idpersonal
          group by ee.id_entrevista
      ) t;



select concat_ws(' ', coalesce(p.apellido1, ''), coalesce(p.apellido2, ''), p.nombres) || ' (' || p.cedula || ')' as docente ,
    p.idfichero_hoja_vida_foto into v_docente,v_idfichero_hoja_vida_foto
from esq_datos_personales.personal p
where p.idpersonal = p_idpersonal; --40885;
r_docente := v_docente;
r_departamento := v_departamento;
r_categoria := v_categoria;
r_dedicacion := v_dedicacion;
r_tutorados := v_tutorados;
r_tutorados_activos := v_tutorados_activos;
r_tutorados_pasivo := v_tutorados_pasivo;
r_tutorados_egresado := v_tutorados_egresado;
r_tutorados_otros := v_tutorados_otros;
r_agendas_programadas := v_agendas_programadas;
r_agendas_programadas_e := v_agendas_programadas_e;
r_agendas_programadas_d := v_agendas_programadas_d;
r_entrevistas_efectiva := v_entrevistas_efectiva;
r_entrevistas_n_efectiva := v_entrevistas_n_efectiva;
r_entrevistas_n_efectiva_d := v_entrevistas_n_efectiva_d;
r_entrevistas_n_efectiva_e := v_entrevistas_n_efectiva_e;
r_calificacion := v_calificacion;
r_tutorias_evaluadas := v_tutorias_evaluadas;
r_idfichero_hoja_vida_foto := v_idfichero_hoja_vida_foto;
r_ta_agendas_programadas := v_ta_agendas_programadas;
r_ta_agendas_programadas_e := v_ta_agendas_programadas_e;
r_ta_agendas_programadas_d := v_ta_agendas_programadas_d;
r_ta_entrevistas_efectiva := v_ta_entrevistas_efectiva;
r_ta_entrevistas_n_efectiva := v_ta_entrevistas_n_efectiva;
r_ta_entrevistas_n_efectiva_e := v_ta_entrevistas_n_efectiva_e;
r_ta_entrevistas_n_efectiva_d := v_ta_entrevistas_n_efectiva_d;
r_ta_calificacion := v_ta_calificacion;
r_ta_tutorias_evaluadas := v_ta_tutorias_evaluadas;
return next;
END;
$BODY$;