-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SIU-GUARANI 3
-- Version 3.0.0
-- Function: f_comisiones_vigentes_una_actividad
--
-- Recupera comisiones de la actividad:
--    - De periodos lectivos que no finalizaron
--    - Definidos para el plan y propuesta del alumno
--    - De periodos de inscripcion vigentes o no (segun parametro) correspondiente a la version de plan del alumno
--    - Con inscripcion habilitada
--    - De la ubicación del alumno (si asi lo indica el parámetro del sistema)
--
-- Recibe:  
--    1. Propuesta del alumno
--    2. Plan del alumno
--    3. ID de la Version de Plan (del alumno)
--    4. Actividad
--    5. Dentro de un período de inscripción activo: true - Si / false - No
--    6. Si parámetro "exa_rinde_en_cualquier_ubicacion" = 'N' entonces recibe la ubicacion del alumno (sga_alumnos.ubicacion)
--
-- Retorna: Lista de comisiones disponibles para la inscripción de la actividad.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Recibe plan_version
-- DROP FUNCTION f_comisiones_vigentes_una_actividad(integer, integer, integer, integer, boolean, integer);

CREATE OR REPLACE FUNCTION f_comisiones_vigentes_una_actividad(_propuesta integer, _plan integer, _plan_version integer, _actividad integer, _en_periodo_insc boolean, _ubicacion integer)
  RETURNS SETOF integer AS
$BODY$
DECLARE 
  cur_comisiones RECORD;

BEGIN
/* Parametros:
  1. Propuesta del Alumno
  2. Plan del Alumno
  3. Version de Plan del Alumno
  4. Actividad
  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_comisiones (
     comision integer, 
     periodo_insc integer, 
     periodo_inscripcion integer, 
     fecha_inicio timestamp, 
     fecha_fin timestamp); 
  
  -- lleno tabla temporal con actividades que tienen comisiones:
  -- Con inscripción habilitada
  -- Con períodos de inscripcion definidos y habilitados 
  -- Con la misma ubicacion del alumno, si el parametro indica que solo puede cursar en su ubicación.
  INSERT INTO tmp_comisiones (comision, periodo_insc, fecha_inicio, fecha_fin, periodo_inscripcion)
    SELECT DISTINCT 
           sga_comisiones.comision,
           sga_periodos_inscripcion_fechas.periodo_insc,
           sga_periodos_inscripcion_fechas.fecha_inicio,
           sga_periodos_inscripcion_fechas.fecha_fin,
           sga_periodos_inscripcion_fechas.periodo_inscripcion
    FROM sga_comisiones,
         sga_comisiones_propuestas,
         sga_periodos_lectivos,
         sga_periodos,
         sga_periodos_inscripcion,
         sga_periodos_inscripcion_fechas,
         sga_periodos_inscripcion_aplanado,
         sga_per_insc_ubicacion
    WHERE sga_comisiones.inscripcion_habilitada = 'S'
      AND sga_comisiones.elemento = _actividad
      AND sga_comisiones_propuestas.comision = sga_comisiones.comision
      AND sga_comisiones_propuestas.propuesta = _propuesta
      AND sga_comisiones_propuestas.plan      = _plan
      AND sga_periodos_lectivos.periodo_lectivo = sga_comisiones.periodo_lectivo
      AND sga_periodos.periodo = sga_periodos_lectivos.periodo
      AND sga_periodos.fecha_fin >= CURRENT_DATE
      AND sga_periodos_inscripcion.periodo = sga_periodos_lectivos.periodo
      AND sga_periodos_inscripcion_fechas.periodo_inscripcion = sga_periodos_inscripcion.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 = _plan_version
      AND sga_per_insc_ubicacion.periodo_inscripcion = sga_periodos_inscripcion.periodo_inscripcion
      AND sga_per_insc_ubicacion.ubicacion = sga_comisiones.ubicacion
      AND (_ubicacion IS NULL OR (_ubicacion IS NOT NULL AND sga_comisiones.ubicacion = _ubicacion));  


  FOR cur_comisiones IN (
    SELECT t.comision as comision
      FROM tmp_comisiones as t
              LEFT JOIN sga_comisiones_excep_perinsc as e ON e.periodo_insc = t.periodo_insc AND e.comision = t.comision
     WHERE 
       ((-- fecha y hora actual se encuentre en un período de inscripcion 
         _en_periodo_insc = true AND CURRENT_TIMESTAMP BETWEEN COALESCE(e.fecha_inicio, t.fecha_inicio) AND COALESCE(e.fecha_fin, t.fecha_fin)
        ) OR
        (-- fecha y hora actual fuera del período de inscripcion
         _en_periodo_insc = false AND CURRENT_TIMESTAMP > COALESCE(e.fecha_fin, t.fecha_fin)
        )
       )
       -- Modalidad de cursada
       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_comisiones_modalidad  WHERE sga_comisiones_modalidad.comision = t.comision)
	                )
  ) LOOP
    
    -- Retorno la actividad que tiene mesa con periodo de insc vigente en este momento.
    RETURN NEXT cur_comisiones.comision;
  END LOOP;
  
  -- Borro tabla temporal       
  DROP TABLE tmp_comisiones;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
  
-- ++++++++++++++++++++++++++ Fin Function f_comisiones_vigentes_una_actividad +++++++++++++++++++++++++++++++++++++++
  
-- REVOKE EXECUTE ON FUNCTION f_comisiones_vigentes_una_actividad FROM public;
GRANT EXECUTE ON FUNCTION f_comisiones_vigentes_una_actividad(integer, integer, integer, integer, boolean, integer) TO public;