-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SIU-GUARANI 3
-- Version 3.0.0
-- Function: f_actividades_con_mesas_vigentes
--
-- Recupera actividades que tienen mesas de examen con:
--  - Fecha mayor o igual a la fecha actual.
--  - Períodos de inscripcion vigentes y habilitados
--  - Periodos de inscripcion definidos para la version del plan de estudios pasado por parametro
--    y para la ubicación y modalidad de la mesa de examen.
--
-- Recibe:  
--    1. Propuesta del alumno
--    2. Plan del alumno
--    3. Version de Plan del alumno
--    4. Dentro de un período de inscripción activo: true - Si / false - No
--    5. Si parámetro "exa_rinde_en_cualquier_ubicacion" = 'N' entonces recibe la ubicacion del alumno (sga_alumnos.ubicacion)
-- Retorna: 
--    1. Integer: ID de las actividades que tienen mesas de examen. (sga_elementos.elemento)
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Recibe plan_version.
-- DROP FUNCTION f_actividades_con_mesas_vigentes(integer, integer, integer, boolean, integer);

CREATE OR REPLACE FUNCTION f_actividades_con_mesas_vigentes(_propuesta integer, _plan integer,_id_plan_version integer, _en_periodo_insc boolean, _ubicacion integer)
  RETURNS SETOF INTEGER AS
$BODY$
DECLARE 

 cur_actividad RECORD;
BEGIN
/* Parametros:
  1. Propuesta del Alumno
  2. Plan del Alumno
  3. Version de Plan del Alumno
  4. Si debe estar dentro de un período de inscripción activo de mesas futuras
  5. Si solo rinde en la ubicacion del alumno, recibe la ubicacion.
*/
  -- Creo tabla temporal
  CREATE TEMP TABLE tmp_llamados (
     llamado_mesa integer, 
     mesa_examen integer, 
     periodo_insc integer, 
     periodo_inscripcion integer, 
     fecha_inicio timestamp, 
     fecha_fin timestamp, 
     hs_previas_fin smallint, 
     elemento integer); 
  
  -- lleno tabla temporal con actividades que tienen mesas de examen con fecha igual o mayor a la actual
  -- y segun parametro que define que la mesa sea de la ubicación del alumno.
  INSERT INTO tmp_llamados (mesa_examen, elemento, llamado_mesa, periodo_insc, fecha_inicio, fecha_fin, hs_previas_fin, periodo_inscripcion)
    SELECT DISTINCT 
           sga_mesas_examen.mesa_examen,
           sga_mesas_examen.elemento,
           sga_llamados_mesa.llamado_mesa, 
           sga_periodos_inscripcion_fechas.periodo_insc,
           sga_periodos_inscripcion_fechas.fecha_inicio,
           sga_periodos_inscripcion_fechas.fecha_fin,
           sga_periodos_inscripcion_fechas.hs_previas_fin,
           sga_periodos_inscripcion_fechas.periodo_inscripcion
      FROM sga_llamados_mesa,
           sga_llamados_turno,
           sga_mesas_examen,
           sga_mesas_examen_propuestas,
           sga_periodos_inscripcion_fechas,
           sga_periodos_inscripcion,
           sga_periodos_inscripcion_aplanado,
           sga_per_insc_ubicacion
    WHERE sga_llamados_mesa.fecha >= CURRENT_DATE
       AND sga_llamados_mesa.mesa_examen = sga_mesas_examen.mesa_examen
       AND sga_mesas_examen_propuestas.mesa_examen = sga_mesas_examen.mesa_examen
       AND sga_mesas_examen_propuestas.propuesta = _propuesta
       AND sga_mesas_examen_propuestas.plan      = _plan
       AND sga_llamados_turno.llamado = sga_llamados_mesa.llamado
       AND sga_periodos_inscripcion.periodo = sga_llamados_turno.periodo
       AND sga_periodos_inscripcion.periodo_inscripcion = sga_periodos_inscripcion_fechas.periodo_inscripcion
       AND sga_periodos_inscripcion_fechas.habilitado = 'S'
       AND sga_periodos_inscripcion_aplanado.periodo_insc = sga_periodos_inscripcion_fechas.periodo_insc
       AND sga_periodos_inscripcion_aplanado.plan_version = _id_plan_version
       AND sga_per_insc_ubicacion.periodo_inscripcion = sga_periodos_inscripcion.periodo_inscripcion
       AND sga_per_insc_ubicacion.ubicacion = sga_mesas_examen.ubicacion
       AND (_ubicacion IS NULL OR (_ubicacion IS NOT NULL AND sga_mesas_examen.ubicacion = _ubicacion));  
       
  -- Si la fecha de finalizacion es el campo fecha_fin, se  
  FOR cur_actividad IN (
    SELECT t.elemento as elemento
      FROM tmp_llamados as t
              LEFT JOIN sga_llamados_mesa_excep_perinsc as e ON e.periodo_insc = t.periodo_insc AND e.llamado_mesa = t.llamado_mesa,
           sga_llamados_mesa
     WHERE t.llamado_mesa = sga_llamados_mesa.llamado_mesa 
        -- fecha de inicio del periodo de insc.
       AND CURRENT_DATE >= COALESCE(e.fecha_inicio, t.fecha_inicio)
       AND ((-- fecha y hora actual se encuentre en un período de inscripcion de la mesa
             _en_periodo_insc = true AND 
             CURRENT_TIMESTAMP BETWEEN COALESCE(e.fecha_inicio, t.fecha_inicio) AND
                                      COALESCE(e.fecha_fin, CASE 
                                                             WHEN e.hs_previas_fin IS NOT NULL THEN CAST(sga_llamados_mesa.fecha::text || ' ' || sga_llamados_mesa.hora_inicio::text as timestamp) - cast(cast(e.hs_previas_fin as text) || ' hours' as interval)
                                                             WHEN e.hs_previas_fin IS NULL THEN COALESCE(t.fecha_fin, CAST(sga_llamados_mesa.fecha::text || ' ' || sga_llamados_mesa.hora_inicio::text as timestamp) - cast(cast(t.hs_previas_fin as text) || ' hours' as interval))
                                                            END
                                              )
                                      
             ) OR
            (-- Fuera de un periodo de inscripción
             _en_periodo_insc = false AND 
             -- CURRENT_TIMESTAMP < COALESCE(e.fecha_inicio, t.fecha_inicio) AND
             CURRENT_TIMESTAMP > COALESCE((e.fecha_fin + CAST('1 days' AS INTERVAL)), -- Porque es fecha y hora
                                           CASE 
                                             WHEN e.hs_previas_fin IS NOT NULL THEN CAST(sga_llamados_mesa.fecha::text || ' ' || sga_llamados_mesa.hora_inicio::text as timestamp) - cast(cast(e.hs_previas_fin as text) || ' hours' as interval)
                                             WHEN e.hs_previas_fin IS NULL THEN COALESCE(t.fecha_fin + CAST('1 days' AS INTERVAL), CAST(sga_llamados_mesa.fecha::text || ' ' || sga_llamados_mesa.hora_inicio::text as timestamp) - cast(cast(t.hs_previas_fin as text) || ' hours' as interval))
                                            END
                                            )
             )
            ) 
       -- MODALIDAD
       AND EXISTS (SELECT 1 FROM sga_per_insc_modalidad
                    WHERE sga_per_insc_modalidad.periodo_inscripcion = t.periodo_inscripcion
                      AND sga_per_insc_modalidad.modalidad IN (SELECT modalidad FROM sga_mesas_examen_modalidad  WHERE sga_mesas_examen_modalidad.mesa_examen = t.mesa_examen)
                   )
  ) LOOP
    
    -- Retorno la actividad que tiene mesa con periodo de insc vigente en este momento.
    RETURN NEXT cur_actividad.elemento;
  END LOOP;
         
  -- Borro la tabla temporal       
  DROP TABLE tmp_llamados;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
  
-- ++++++++++++++++++++++++++ Fin Function f_actividades_con_mesas_vigentes +++++++++++++++++++++++++++++++++++++++
  
-- REVOKE EXECUTE ON FUNCTION f_actividades_con_mesas_vigentes FROM public;
GRANT EXECUTE ON FUNCTION f_actividades_con_mesas_vigentes(integer, integer, integer, boolean, integer) TO public;