-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SIU-GUARANI 3
-- Version 3.9.0, 3.13.0 , 3.15.0 Marzo 2018, 3.19.0 Febrero 2021, Julio 2021
-- Function: f_int_alumnos_plan_progresar
--
-- Tabla donde se carga la info de los alumnos: int_progresar
-- La cantidad de materias en el año a evaluar se cuenta desde el 01/04/Año al 31/03/Año+1 (Idem Araucano)
--
-- El codigo de institucion araucano sale de la tabla de Responsables Academicas
-- 
-- * Retorna alumnos en las propuestas donde tenga inscripcion en estado aceptada o pendiente
-- * Informa actividad academica del titulo araucano que corresponde con la propuesta del alumno
--   Si el titulo araucano se encuentra en otra propuesta donde el alumno no se encuentra entonces informa actividad academica del alumno 
--   de todas las propuestas en la que se encuentra que tengan un titulo araucano asociado.
-- * Respecto de la fecha de finalización del curso de ingreso, en aquellas propuestas que tengan
--   una propuesta vinculada de tipo "curso de ingreso", devolverá la fecha máxima de aprobación de materia del curso de ingreso,
--   solo en el caso que haya cumplido con todas las materias del curso de ingreso.
--   En caso contrario se devuelve vacio el dato de fecha de cumplimiento de curso de ingreso.
-- 
--
-- Recibe:
--    1. pAnio = Año a evaluar  
--    2. pResponsableAcademica = ID de Responsable Academica a procesar. Si existe el dato, solo alumnos de propuestas de esta responsable Academica
--    3. pActividadesPlanActual = Indica si solo se consideran actividades aprobadas/cursadas que son del plan actual del alumno o todas de la propuesta sin considerar plan y versión.
--                                Valores: TRUE (valor por defecto) / FALSE 
--    4. pTituloNivelFinal = Indica si solo se informan actividades relacionadas con el titulo de nivel final o tambien se informan titulos de nivel "Intermedio"
--                           Valores: TRUE (Solo titulo de Nivel Final) / FALSE (Titulos de cualquier nivel: Final/Intermedio. Valor por defecto)
--    5. pNrodocumento = Si se quiere recuperar inforación de un alumno en particular. Valor default NULL.

-- Retorna: 
--   1. Cada registro con info de los alumnos... y los planes de estudio...
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DROP FUNCTION IF EXISTS f_int_alumnos_plan_progresar(integer); -- Version 2022

DROP FUNCTION IF EXISTS f_int_alumnos_plan_progresar(integer, integer, boolean, boolean, varchar);
CREATE OR REPLACE FUNCTION f_int_alumnos_plan_progresar(
  pAnio integer, 
  pResponsableAcademica Integer DEFAULT NULL,
  pActividadesPlanActual BOOLEAN DEFAULT TRUE,
  pTituloNivelFinal BOOLEAN DEFAULT FALSE,
  pNroDocumento varchar(20) DEFAULT NULL)

  RETURNS SETOF text AS 
$BODY$
DECLARE 
  _datos text;
  _alu  record;
  _ci record;
  _planes record;
  _arch1 record;
  _arch2 record;
  _errores record;
  _cant_aprobadas integer;
  _cant_aprobadas_anio_a_evaluar integer;
  _fecha_curso_ingreso varchar(10);
  _inst_codigo integer;
  _inst_nombre varchar(100);
  _TipoCursoIngreso integer;
  _AlumnoCI integer;
  _PropuestaCI integer;
  _PlanCI integer;
  _PlanVersionCI integer;        
  _cant_no_aprobadas_ci integer;
  _dfecha_ci date;
  _aux_plan varchar(10);
  _aux_titulo integer;
  _FechaDesde date;
  _FechaHasta date;
  _cant integer;
  _id integer;
  _error_alumno text;
  _existe_titulo_en_su_propuesta boolean;
  
BEGIN
  
  -- El rango de fechas a evaluar las actividades aprobadas son desde el 01/04/Año a Evaluar al 31/03/Año + 1
  _FechaDesde := to_date('0104' || cast(pAnio as varchar), 'DDMMYYYY');
  _FechaHasta := to_date('3103' || cast((pAnio + 1) as varchar), 'DDMMYYYY');

  
  _TipoCursoIngreso := 204; -- Curso de Ingreso
  _id := 0;
  
  CREATE TEMP TABLE _Terrores (id integer, error text);
   
  CREATE TEMP TABLE _Tplanes (
     plan integer, 
     cnt_materias integer, 
     titulo_araucano integer,
     institucion_araucano integer
    );
  
 -- Limpio datos por si viene el string vacio
 UPDATE int_progresar SET codigo_titulo_araucano = NULL WHERE trim(codigo_titulo_araucano) = '';
 UPDATE int_progresar SET codigo_facultad  = NULL WHERE trim(codigo_facultad) = '';
 UPDATE int_progresar SET codigo_institucion  = NULL  WHERE trim(codigo_institucion) = '';
 
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 -- Devuelvo el archivo de alumnos
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 _datos := '**************** Inicio archivo alumnos.txt *****************';  
 RETURN NEXT _datos;

 -- Recupero una vez cada persona (por si esta informado mas de una vez en el archivo)  
 -- Lo informo por cada titulo araucano que viene en el archivo a informar.
 FOR _alu IN 
     SELECT DISTINCT
           CASE tipo_documento
             WHEN 'DNI' THEN 0
             WHEN 'DNT' THEN 1
             WHEN 'CI'  THEN 2
             WHEN 'LE'  THEN 18
             WHEN 'LC'  THEN 19             
             WHEN 'PAS' THEN 90             
          END as tipo_documento, 
          numero_documento,
		  tipo_documento as tipo_documento_archivo,
          cast(trim(codigo_titulo_araucano) as integer) as codigo_titulo_araucano,
          cast(trim(codigo_facultad) as integer) as codigo_facultad,
		COALESCE(apellido,'') as apellido, 
		COALESCE(nombre,'') as nombre
      FROM int_progresar
      WHERE (pNroDocumento IS NULL OR trim(numero_documento) = pNroDocumento)
    ORDER BY numero_documento
  
  LOOP
    -- ******************************* INICIO VALIDACIONES ****************************************************
	-- Formato de salida del archivo de errores: DOC|12345678|Apellido|Nombre|Descripción del Error
	_error_alumno := _alu.tipo_documento_archivo || '|' ||  _alu.numero_documento || '|' ||  _alu.apellido|| '|' ||  _alu.nombre || '|'; 
	
    -- Si no encuentra el alumno por su numero de documento.
    SELECT count(1) INTO _cant
     FROM mdp_personas_documentos as d
    WHERE d.tipo_documento = _alu.tipo_documento
      AND d.nro_documento  = _alu.numero_documento;   
	IF _cant = 0 THEN 
	   _id := _id + 1;
	   INSERT INTO _Terrores (id, error) 
	      	VALUES (_id, _error_alumno || 'No se encontró el numero de documento');
	   CONTINUE;		
	END IF;  

    -- No existe el codigo de Título Araucano en la propuesta del alumno
	_existe_titulo_en_su_propuesta := true;
    SELECT count(1) INTO _cant
     FROM mdp_personas_documentos as d
     -- JOIN mdp_personas as p ON p.documento_principal = d.documento
     JOIN mdp_personas as p ON p.persona = d.persona
     JOIN sga_alumnos as alu ON alu.persona = p.persona
     JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
     JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
     JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
     JOIN sga_certificados as c ON c.certificado = pc.certificado
     JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
     JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
     WHERE d.tipo_documento = _alu.tipo_documento
        AND d.nro_documento  = _alu.numero_documento
        AND sa.resultado_asp IN ('P','A')
        AND c.titulo_araucano = _alu.codigo_titulo_araucano;
	IF _cant = 0 THEN 
	   _existe_titulo_en_su_propuesta := false;
	   -- NO LO INFORMO COMO ERROR DEBIDO A QUE AL NO ENCONTRAR EL TITULO EN ALGUNA DE LAS PROPUESTAS DEL ALUMNO INFORMO POR TODAS.
	   -- _id := _id + 1;
	   -- INSERT INTO _Terrores (id, error) 
	   --   VALUES (_id, _error_alumno || 'No existe el código de titulo araucano (' || _alu.codigo_titulo_araucano || ') definido en algún título de las propuestas donde se encuentra el alumno o el alumno no tiene la inscripción a propuesta en estado aceptada o pendiente.');

       -- Verifico si el titulo araucano esta definido para otra propuesta en la que no esta el alumno.
		SELECT count(1) INTO _cant
		 FROM sga_certificados as c
		 JOIN sga_planes_certificados as pc ON c.certificado = pc.certificado
		WHERE c.titulo_araucano = _alu.codigo_titulo_araucano
		 AND pc.plan_version NOT IN (SELECT alu.plan_version 
							FROM mdp_personas_documentos as d
							JOIN mdp_personas as p ON p.persona = d.persona
							JOIN sga_alumnos as alu ON alu.persona = p.persona
							JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
							JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
							JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
							WHERE d.tipo_documento = _alu.tipo_documento
							  AND d.nro_documento  = _alu.numero_documento
							  AND sa.resultado_asp IN ('P','A')
							 );
		IF _cant > 0 THEN 		
	     _id := _id + 1;
	     INSERT INTO _Terrores (id, error) 
	         VALUES (_id, _error_alumno || 'El título araucano (' || _alu.codigo_titulo_araucano || ') se oferta por otra propuesta en la que no se encuentra el alumno.');
		END IF;

       -- Verifico si puedo informarlo por algun titulo
		SELECT count(1) INTO _cant
			FROM mdp_personas_documentos as d
			JOIN mdp_personas as p ON p.persona = d.persona
			JOIN sga_alumnos as alu ON alu.persona = p.persona
			JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
			JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
			JOIN sga_certificados as c ON c.certificado = pc.certificado
			JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
			JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
			WHERE d.tipo_documento = _alu.tipo_documento
			  AND d.nro_documento  = _alu.numero_documento
			  AND sa.resultado_asp IN ('P','A')
			  AND c.titulo_araucano IS NOT NULL
			  AND prop.propuesta_tipo NOT IN (207, 208); -- No informo propuestas de tipo VOCACIONAL ni CURSO
			
		IF _cant = 0 THEN 		
	     _id := _id + 1;
	     INSERT INTO _Terrores (id, error) 
	         VALUES (_id, _error_alumno || 'El alumno se encuentra en propuesta/s que NO tiene/n definido algún código de título araucano en su oferta. No se puede informar.');
		END IF;

   END IF;  
		

    -- ******************************* FIN VALIDACIONES ****************************************************
	
   -- Datos del alumno a informar
   FOR _arch2 IN
   -- Titulos Araucano por los que va a informar...
   -- Por el que viene informado desde el ministerio y si no viene titulo se informa por todos en los que se encuentre el alumno.
   SELECT 
           CASE d.tipo_documento
             WHEN 0 THEN 'DNI'
             WHEN 1 THEN 'DNT' 
             WHEN 2 THEN 'CI'  
             WHEN 18 THEN 'LE' 
             WHEN 19 THEN 'LC'              
             WHEN 90 THEN 'PAS'             
          END as tipo_documento_progresar, 
          alu.persona as persona, 
          alu.alumno as alumno,
		  alu.regular as regular,
		  alu.propuesta as propuesta,
          d.tipo_documento,
          d.nro_documento,
          p.apellido,
          p.nombres,
          CASE p.sexo
            WHEN 'M' THEN '1'
            WHEN 'F' THEN '2'
          END as genero,
          to_char(p.fecha_nacimiento, 'YYYYMMDD') as fecha_nacimiento,
          c.titulo_araucano as titulo_araucano,
          pa.anio_academico as anio_ingreso,
          pv.plan as plan,
          sga_planes.codigo as plan_codigo,
          pv.cnt_materias as cnt_materias,
          prop.nombre as propuesta_nombre,
          c.titulo_nivel as titulo_nivel, -- INTERMEDIO / FINAL 
	  c.nombre as titulo_nombre,
	  alu.plan_version
     FROM mdp_personas_documentos as d
     -- JOIN mdp_personas as p ON p.documento_principal = d.documento
     JOIN mdp_personas as p ON p.persona = d.persona
     JOIN sga_alumnos as alu ON alu.persona = p.persona
     JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
     JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
     JOIN sga_planes ON sga_planes.plan = pv.plan
     JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
     JOIN sga_certificados as c ON c.certificado = pc.certificado
     JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
     JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
          
      WHERE d.tipo_documento = _alu.tipo_documento
        AND d.nro_documento  = _alu.numero_documento
        AND sa.resultado_asp IN ('P','A')  -- Inscripcion a propuesta aceptada/pendiente
        AND c.titulo_araucano IS NOT NULL		-- Que el plan tenga un titulo con titulo araucano definido.
        AND (NOT _existe_titulo_en_su_propuesta OR (_existe_titulo_en_su_propuesta AND c.titulo_araucano = _alu.codigo_titulo_araucano))
	AND prop.propuesta_tipo NOT IN (207, 208) -- No informo propuestas de tipo VOCACIONAL ni CURSO
        AND ((pTituloNivelFinal AND c.titulo_nivel = 'FINAL') OR NOT pTituloNivelFinal)
        -- Filtro alumnos de propuesta de la responsable academica
        AND (pResponsableAcademica IS NULL OR alu.propuesta IN (SELECT propuesta FROM sga_propuestas_ra WHERE responsable_academica = pResponsableAcademica))
        
      ORDER BY p.apellido, p.nombres, prop.nombre, c.nombre
  LOOP
  
  
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	-- Recupero codigo de institucion araucano y nombre en base a la responsable academica
	-- Tomo la primera que encuentre si existe mas de una RA para la propuesta
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	_inst_codigo := null;
	_inst_nombre := null;
	SELECT ra.institucion_araucano, ra.nombre
	  INTO _inst_codigo, _inst_nombre
	  FROM sga_propuestas_ra as pra
	  JOIN sga_responsables_academicas as ra ON pra.responsable_academica = ra.responsable_academica
     WHERE pra.propuesta = _arch2.propuesta
	   AND ra.institucion_araucano IS NOT NULL
	 ORDER BY ra.responsable_academica
	 LIMIT 1;

	IF _inst_codigo IS NULL THEN 
	   _id := _id + 1;
	   INSERT INTO _Terrores (id, error) 
	     VALUES (_id, _error_alumno || 'La responsable académica de la propuesta (' || _arch2.propuesta_nombre || ') no tiene definido el código de institución araucano');
    END IF;  

     -- Inserto los planes
     INSERT INTO _Tplanes (plan, cnt_materias, titulo_araucano, institucion_araucano) 
	      VALUES (_arch2.plan, _arch2.cnt_materias, _arch2.titulo_araucano, _inst_codigo);

	 
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Cuento materias aprobadas
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _cant_aprobadas := 0;
    _cant_aprobadas_anio_a_evaluar := 0;
    SELECT COUNT(*), 
	    SUM(CASE WHEN (fecha >= _FechaDesde AND fecha <= _FechaHasta) THEN 1 ELSE 0 END)
      INTO _cant_aprobadas, _cant_aprobadas_anio_a_evaluar          
      FROM vw_hist_academica_basica
     WHERE alumno = _arch2.alumno
       AND resultado = 'A'
       AND ((pActividadesPlanActual AND elemento IN (SELECT elemento FROM vw_elementos_plan WHERE plan_version = _arch2.plan_version)) OR NOT pActividadesPlanActual);
       
       
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Fecha cumplimiento Curso de Ingreso
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _fecha_curso_ingreso := '';
    _AlumnoCI    := NULL;
    _PropuestaCI := NULL;
    _PlanCI      := NULL;
    _PlanVersionCI := NULL;
    _dfecha_ci     := NULL;
    
    -- Verifico si la propuesta tiene un curso de ingreso vinculado y la persona esta como alumno en esa propuesta
    SELECT sga_alumnos.alumno, sga_planes.propuesta, sga_planes.plan, sga_alumnos.plan_version
         INTO _AlumnoCI, _PropuestaCI, _PlanCI, _PlanVersionCI 
         FROM sga_propuestas_relacion_grupo
         JOIN sga_propuestas_relacion_plan ON sga_propuestas_relacion_plan.relacion_grupo = sga_propuestas_relacion_grupo.relacion_grupo
         JOIN sga_planes ON sga_planes.plan = sga_propuestas_relacion_plan.plan 
         JOIN sga_propuestas ON sga_propuestas.propuesta = sga_planes.propuesta
         JOIN sga_alumnos ON sga_alumnos.propuesta = sga_planes.propuesta 
        WHERE sga_propuestas_relacion_grupo.relacion IN (SELECT relacion FROM get_relacion_propuesta(_arch2.alumno))
          AND sga_propuestas.propuesta_tipo = _TipoCursoIngreso
          AND sga_alumnos.persona   = _arch2.persona
          LIMIT 1;
    
    IF FOUND THEN
       -- Existe Curso de ingreso. 
       -- Verifico si cumplio todas las actividades y si es asi recupero la fecha maxima de aprobación.
       
       _cant_no_aprobadas_ci := 0;
       
       SELECT COUNT(*) INTO _cant_no_aprobadas_ci
         FROM vw_actividades_plan
         WHERE plan_version = _PlanVersionCI
           AND elemento NOT IN (SELECT elemento FROM vw_hist_academica_basica
                                 WHERE alumno = _AlumnoCI
                                   AND resultado = 'A'
                                );
       
       IF _cant_no_aprobadas_ci = 0 THEN
          -- Recupero fecha maxima de aprobacion de las materias del curso de ingreso.  
          SELECT MAX(fecha) INTO _dfecha_ci
            FROM vw_hist_academica_basica
           WHERE alumno    = _AlumnoCI
             AND resultado = 'A'
             AND elemento IN (SELECT elemento FROM vw_actividades_plan WHERE plan_version = _PlanVersionCI);
          
          _fecha_curso_ingreso := to_char(_dfecha_ci, 'DD-MM-YYYY');
       END IF;
     
    END IF; -- Existe Curso de Ingreso


    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Devuelvo los datos
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _datos := _arch2.tipo_documento_progresar;
    _datos := _datos || '|' || _arch2.nro_documento;
    _datos := _datos || '|' || LEFT(_arch2.apellido,30);
    _datos := _datos || '|' || LEFT(_arch2.nombres,30);
    _datos := _datos || '|' || COALESCE(_inst_codigo::text,'');
    _datos := _datos || '|' || COALESCE(LEFT(_inst_nombre,60),'');
    _datos := _datos || '|' || _arch2.titulo_araucano; 
    _datos := _datos || '|' || _arch2.propuesta_nombre;
    _datos := _datos || '|' || _arch2.plan_codigo;
    _datos := _datos || '|' || _arch2.anio_ingreso::text;
    _datos := _datos || '|' || COALESCE(_fecha_curso_ingreso,'');
    _datos := _datos || '|' || COALESCE(_cant_aprobadas::text, '0');
    _datos := _datos || '|' || COALESCE(_cant_aprobadas_anio_a_evaluar::text, '0');
    _datos := _datos || '|' || _arch2.regular;
    
    RETURN NEXT _datos;
  
  END LOOP; -- Datos de cada alumno
      
END LOOP; -- Archivo de alumnos

 _datos := '**************** Fin archivo alumnos.txt *****************';  
 RETURN NEXT _datos;
_datos := '**************** Inicio archivo planes_estudio.txt *****************';  
 RETURN NEXT _datos;
 
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 -- Devuelvo el archivo de planes de estudios.
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 _aux_plan   := '';
 _aux_titulo := 0;
 FOR _arch1 IN SELECT DISTINCT t.institucion_araucano, p.codigo, t.cnt_materias, t.titulo_araucano, p.duracion_en_anios
                 FROM _Tplanes as t, sga_planes as p
                WHERE t.plan = p.plan
		 ORDER BY t.institucion_araucano, p.codigo, t.cnt_materias DESC
 LOOP
    -- Puede haber versiones de plan para informar el mismo titulo con cantidad de materias diferentes.
	-- Informo por la version del plan que mas materias tiene.
    IF _aux_titulo = _arch1.titulo_araucano AND _aux_plan = _arch1.codigo THEN 
	   CONTINUE;
	END IF;   
	
    _datos := COALESCE(_arch1.institucion_araucano::text,''); 
    _datos := _datos || '|' || COALESCE(_arch1.titulo_araucano::text, '') ;
    _datos := _datos || '|' || COALESCE(_arch1.codigo, ''); 
    _datos := _datos || '|' || COALESCE(_arch1.cnt_materias::text, '0');  
    _datos := _datos || '|' || COALESCE(_arch1.duracion_en_anios::text, '0') ;
    RETURN NEXT _datos;
	
	_aux_titulo := _arch1.titulo_araucano;
	_aux_plan   := _arch1.codigo;
 END LOOP;

 _datos := '**************** Fin archivo planes_estudio.txt ************';  
 RETURN NEXT _datos;
 
 
 _datos := '**************** Inicio archivo de Errores alumnos_con_error.txt ************';  
 RETURN NEXT _datos;
 FOR _errores IN (SELECT error FROM _TErrores ORDER BY id)
 LOOP
    RETURN NEXT _errores.error;
 END LOOP;
 _datos := '**************** Fin archivo de Errores alumnos_con_error.txt ************';  
 RETURN NEXT _datos;



 -- Borro la tabla temporal   
 DROP TABLE IF EXISTS _Tplanes;
 DROP TABLE IF EXISTS _TErrores;
    
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- ++++++++++++++++++++++++++++++ Fin Function f_int_alumnos_plan_progresar ++++++++++++++++

-- REVOKE EXECUTE ON FUNCTION f_int_alumnos_plan_progresar (integer, integer, boolean, boolean, varchar) FROM public;
GRANT EXECUTE ON FUNCTION f_int_alumnos_plan_progresar (integer, integer, boolean, boolean, varchar) to public;

