-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SIU-GUARANI 3
-- Version 3.0.0
-- Function: f_mesas_vigentes_una_actividad
--
-- Recupera mesas de examenes de una actividad
--  - 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. ID de la Version de Plan (del alumno)
--    2. Dentro de un período de inscripción activo: true - Si / false - No
--    3. Si parámetro "exa_rinde_en_cualquier_ubicacion" = 'N' entonces recibe la ubicacion del alumno (sga_alumnos.ubicacion)
-- Retorna: 
--    1. Integer: ID de las mesas de examen por llamado (sga_llamados_mesa.llamado_mesa)
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

CREATE OR REPLACE FUNCTION f_mesas_vigentes_una_actividad(_actividad integer, _propuesta integer, _plan integer, _id_plan_version integer, _en_periodo_insc boolean, _ubicacion integer)
  RETURNS SETOF INTEGER AS
$BODY$
DECLARE 
 cur_llamados_mesa RECORD;
BEGIN
/* Parametros:
  1. Actividad
  2. Propuesta del Alumno
  3. Plan del Alumno
  4. Version de Plan del Alumno
  5. Si debe estar dentro de un período de inscripción activo de mesas futuras
  6. 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
     ); 
  
  -- lleno tabla temporal con mesas de examen de la actividad, con fecha de examen igual o mayor a la actual
  -- y segun parametro que define que la mesa sea de la ubicación del alumno y si debe estar dentro de un periodo de inscripcion vigente o fuera de un período de insc vigente.
  INSERT INTO tmp_llamados (mesa_examen, llamado_mesa, periodo_insc, fecha_inicio, fecha_fin, hs_previas_fin, periodo_inscripcion)
    SELECT sga_mesas_examen.mesa_examen,
           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_llamados_turno.llamado = sga_llamados_mesa.llamado
       AND sga_mesas_examen.elemento  = _actividad
       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_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));  
       
  -- Verifico periodo de inscripcion del llamado o alguna excepcion de la mesa en el llamado.
  FOR cur_llamados_mesa IN (
    SELECT t.llamado_mesa as llamado_mesa
      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, -- 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(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 mesa de examen instanciada en un llamado. 
    RETURN NEXT cur_llamados_mesa.llamado_mesa;
  END LOOP;
         
  -- Borro la tabla temporal       
  DROP TABLE tmp_llamados;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
  
-- ++++++++++++++++++++++++++ Fin Function f_mesas_vigentes_una_actividad +++++++++++++++++++++++++++++++++++++++
  
-- REVOKE EXECUTE ON FUNCTION f_mesas_vigentes_una_actividad FROM public;
GRANT EXECUTE ON FUNCTION f_mesas_vigentes_una_actividad(integer, integer, integer, integer, boolean, integer) TO public;


/*
select * from f_mesas_vigentes_una_actividad (, , , 1000, true, NULL);
*/