FITOMN of AM v3
5/16/2017 - 3:55 PM
BIT/IAM-1626 - spPanelAlumnoValidaInscAsignatura - Adecuación de validaciones para checar empalmes
BIT/IAM-1626 - spPanelAlumnoValidaInscAsignatura - Adecuación de validaciones para checar empalmes
----------------------------INICIO PROCEDIMIENTO ------------------------
IF OBJECT_ID('spPanelAlumnoValidaInscAsignatura', 'P') IS NOT NULL
DROP PROC spPanelAlumnoValidaInscAsignatura
GO
CREATE PROCEDURE spPanelAlumnoValidaInscAsignatura
/* --------------------------------------------------------------------------------------------------------------
FECHA | AUTOR | DESCRIPCION
00/00/2015 | Leonardo Dominguez |
22/02/2017 | Luis Bernal |
16/05/2017 | Luis Bernal | BIT/IAM-1626 - INSCRIPCIONES EN LÍNEA EMPALMES
--------------------------------------------------------------------------------------------------------------*/
--@Variables INT
@idAlumno INT ,
@iFolioPeriodo BIGINT ,
@idOfEduc SMALLINT ,
@idPlanEstudios SMALLINT ,
@idGrupo SMALLINT ,
@idAula SMALLINT ,
@idProfesor SMALLINT ,
@idAsignatura SMALLINT
AS
BEGIN
SET NOCOUNT ON;
SET LANGUAGE spanish;
SET DATEFORMAT MDY;
--CONTENIDO PROCEDIMIENTO
---horarios del alumno en el periodo escolar
/*------------------------------------------------------------------------------------------------------------------------------------*/
DECLARE @dHoraInicio INT ,
@dHoraFin INT ,
@iDia TINYINT ,
@dHoraInicio2 INT ,
@dHoraFin2 INT ,
@iDia2 TINYINT;
DECLARE cCursor CURSOR LOCAL
FOR
SELECT CAST(SUBSTRING(CAST(CAST(chc.dHoraInicio AS TIME) AS VARCHAR(25)), 1, 2) AS INT) AS dHoraInicio ,
CAST(SUBSTRING(CAST(CAST(chc.dHoraFin AS TIME) AS VARCHAR(25)), 1, 2) AS INT) AS dHoraFin ,
cgcd.iDia
FROM dbo.Cat_HorarioClase AS chc
LEFT JOIN dbo.Ctrl_GrupoClasesDet AS cgcd ON cgcd.idHorarioClase = chc.idHorarioClase
INNER JOIN dbo.Ctrl_GrupoClases AS cgc ON cgc.iFolio = cgcd.iFolioGrupoClases
AND cgc.iFolioPeriodo = @iFolioPeriodo
INNER JOIN dbo.Ctrl_Grupos AS cg ON cg.idGrupo = cgc.idGrupo
INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cgc.idAsignatura
INNER JOIN dbo.Cat_Profesores AS cp ON cp.idProfesor = cgc.idProfesor
INNER JOIN dbo.Cat_Aulas AS ca2 ON ca2.idAula = cgcd.idAula
INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.idAsignatura = cgc.idAsignatura
AND cta.idGrupo = cg.idGrupo
AND cta.idPlanEstudios = cg.idPlanEstudios
INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
AND ci.iFolioPeriodo = cgc.iFolioPeriodo
WHERE ci.idAlumno = @idAlumno
AND ci.idOfEduc = @idOfEduc
AND (cg.idPlanEstudios = @idPlanEstudios OR @idPlanEstudios = 0);
---horario de la materia que quiere inscribirse
SELECT TOP 1
@dHoraInicio2 = CAST(SUBSTRING(CAST(CAST(chc.dHoraInicio AS TIME) AS VARCHAR(25)), 1, 2) AS INT) ,
@dHoraFin2 = CAST(SUBSTRING(CAST(CAST(chc.dHoraFin AS TIME) AS VARCHAR(25)), 1, 2) AS INT) ,
@iDia2 = cgcd.iDia
FROM dbo.Cat_HorarioClase AS chc
LEFT JOIN dbo.Ctrl_GrupoClasesDet AS cgcd ON cgcd.idHorarioClase = chc.idHorarioClase
INNER JOIN dbo.Ctrl_GrupoClases AS cgc ON cgc.iFolio = cgcd.iFolioGrupoClases
AND cgc.idGrupo = @idGrupo
AND cgc.iFolioPeriodo = @iFolioPeriodo
INNER JOIN dbo.Ctrl_Grupos AS cg ON cg.idGrupo = cgc.idGrupo
INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cgc.idAsignatura
INNER JOIN dbo.Cat_Profesores AS cp ON cp.idProfesor = cgc.idProfesor
INNER JOIN dbo.Cat_Aulas AS ca2 ON ca2.idAula = cgcd.idAula
--INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.idAsignatura = cgc.idAsignatura >>>>>>> 16/May/2017 - Se comenta para obtener el horario y día de la materia a inscribir <<<<<<<
-- AND cta.idGrupo = cg.idGrupo
-- AND cta.idPlanEstudios = cg.idPlanEstudios
--INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
-- AND ci.iFolioPeriodo = cgc.iFolioPeriodo
WHERE ca2.idAula = @idAula
AND cgc.idProfesor = @idProfesor
AND ca.idAsignatura = @idAsignatura
--AND ci.idOfEduc = @idOfEduc
AND (cg.idPlanEstudios = @idPlanEstudios OR @idPlanEstudios = 0)
ORDER BY chc.dHoraInicio ,
chc.dHoraFin ,
iDia;
DECLARE @bSeEmpalmaConOtraAsignatura BIT= 0;
---si coincide en alguna materia del alumno regresa un true en el empalme
OPEN cCursor;
FETCH cCursor INTO @dHoraInicio, @dHoraFin, @iDia;
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @dHoraInicio2 >= @dHoraInicio AND @dHoraInicio2 < @dHoraFin )
AND ( @iDia = @iDia2 )
BEGIN
SET @bSeEmpalmaConOtraAsignatura = 1;
END;
ELSE
BEGIN
IF ( @dHoraFin2 BETWEEN @dHoraInicio AND @dHoraFin )
AND ( @iDia = @iDia2 )
BEGIN
SET @bSeEmpalmaConOtraAsignatura = 1;
END;
END;
FETCH cCursor INTO @dHoraInicio, @dHoraFin, @iDia;
END;
CLOSE cCursor;
DEALLOCATE cCursor;
/*------------------------------------------------------------------------------------------------------------------------------------*/
--DECLARE @idAlumno INT = 106
-- , @iFolioPeriodo BIGINT = 7
-- , @idOfEduc SMALLINT = 1
-- , @idPlanEstudios SMALLINT = 0
-- , @idGrupo SMALLINT = 0
-- , @idAula SMALLINT = 0
-- , @idProfesor SMALLINT = 0
-- , @idAsignatura SMALLINT = 0
--SET NOCOUNT ON;
--SET LANGUAGE spanish
--CONTENIDO PROCEDIMIENTO
DECLARE @iResultado TINYINT = 0 ,
@sResultado VARCHAR(150) = '';
DECLARE @iFolioInscUlt BIGINT ,
@idPlanEst SMALLINT ,
@idPlantel SMALLINT;
SELECT TOP 1
@iFolioInscUlt = ci.iFolio
FROM dbo.Ctrl_Inscripciones AS ci
INNER JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
WHERE ci.idAlumno = @idAlumno
AND ci.iFolioPeriodo = @iFolioPeriodo
AND ci.idOfEduc = @idOfEduc
ORDER BY cp.iAnioInicial DESC ,
cp.iMesInicial DESC ,
cp.iCicloEscolar ASC;
--IF 0 < ( SELECT COUNT(*)
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
-- LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
-- WHERE ci.iFolio = @iFolioInscUlt
-- AND ci.idOfEduc = @idOfEduc
-- )
-- BEGIN
-- SELECT TOP 1
-- @idPlanEst = cta.idPlanEstudios ,
-- @idPlantel = ci.idPlantel
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
-- LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
-- WHERE ci.iFolio = @iFolioInscUlt
-- AND ci.idOfEduc = @idOfEduc
-- ORDER BY cp.iAnioInicial DESC ,
-- cp.iMesInicial DESC ,
-- cp.iCicloEscolar ASC
-- END
-- ELSE
-- BEGIN
-- SELECT TOP 1
-- @idPlanEst = cta.idPlanEstudios ,
-- @idPlantel = ci.idPlantel
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
-- LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
-- WHERE ci.iFolio <> @iFolioInscUlt
-- AND ci.idOfEduc = @idOfEduc
-- ORDER BY cp.iAnioInicial DESC ,
-- cp.iMesInicial DESC ,
-- cp.iCicloEscolar ASC
-- END
-- IF @idPlanEst IS NULL
-- BEGIN
----REVISAR SI EXISTE PLAN DE ESTUDIOS
-- IF 0 < ( SELECT COUNT(cta.idPlanEstudios)
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.iFolioInscripcion = ci.iFolio
-- WHERE ci.iFolioPeriodo = @iFolioPeriodo
-- AND ci.idAlumno = @idAlumno
-- AND ci.idOfEduc = @idOfEduc
-- )
-- BEGIN
-- SELECT @idPlanEst = cta.idPlanEstudios
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.iFolioInscripcion = ci.iFolio
-- WHERE ci.iFolioPeriodo = @iFolioPeriodo
-- AND ci.idAlumno = @idAlumno
-- AND ci.idOfEduc = @idOfEduc
-- END
-- ELSE
-- BEGIN
-- --REVISAR SI EXISTE PLAN DE ESTUDIOS
-- SELECT TOP 1
-- cpe.idPlanEstudios
-- FROM dbo.Ctrl_Inscripciones AS ci
-- INNER JOIN dbo.Cat_PlanEstudios AS cpe ON cpe.idOfEduc = ci.idOfEduc
-- WHERE ci.iFolioPeriodo = @iFolioPeriodo
-- AND ci.idAlumno = @idAlumno
-- AND ci.idOfEduc = @idOfEduc
-- ORDER BY idPlanEstudios DESC
-- END
-- END
IF @bSeEmpalmaConOtraAsignatura = 0
BEGIN
SELECT @idPlanEst = foupep.idPlanEstudios,
@idPlantel = foupep.idPlantel
FROM dbo.fnObtenerUltimoPlanEstPlantel(@idAlumno, @idOfEduc, @iFolioPeriodo) AS foupep;
----Checa tipo de cupo a validar (Por Grupo o por Aula)
DECLARE @bTipoCupo TINYINT, @idTipoHorario TINYINT, @cupoActual INT
SELECT @bTipoCupo = CASE WHEN csp.sValor = '0' THEN 0 ELSE 1 END
FROM dbo.Cat_SistemaParametros AS csp
WHERE csp.idParametro = 48
IF @bTipoCupo = 0 --Por Aula
BEGIN
SELECT @idTipoHorario = cg.idTipoHorario FROM dbo.Ctrl_Grupos AS cg WHERE cg.idGrupo = @idGrupo
SELECT ca.iCapacidad - dbo.fnObtenerInscritosAulaConHorarios(@iFolioPeriodo, @idPlanEst, @idPlantel, @idAsignatura, @idTipoHorario, @idAula) AS iCupoActualAula
FROM dbo.Cat_Aulas AS ca
WHERE CA.idAula = @idAula
END
ELSE --Por Grupo
BEGIN
SELECT @cupoActual = (cg.iCapacidad - dbo.fnObtenerInscritosGrupo(@iFolioPeriodo, @idPlanEst, @idPlantel, @idAsignatura, @idGrupo))
FROM dbo.Ctrl_Grupos cg
WHERE cg.idGrupo = @idGrupo
END
----VALIDANDO CUPO EN GRUPO
IF @cupoActual > 0
BEGIN
----VALIDANDO SI YA EXISTE UNA INSCRIPCION EN LA MISMA MATERIA
IF 0 = ( SELECT COUNT(*)
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
WHERE ci.iFolioPeriodo = @iFolioPeriodo
AND cta.idAsignatura = @idAsignatura
AND ci.idOfEduc = @idOfEduc
AND ci.idAlumno = @idAlumno
)
-------------revisa que no tenga una inscripcion en la misma optativa
AND 0 = (SELECT COUNT(*)
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
INNER JOIN dbo.Ctrl_PlanEstudios AS cpe ON cpe.idAsignatura = cta.idAsignatura AND cpe.idPlanEstudios = cta.idPlanEstudios
WHERE ci.iFolioPeriodo = @iFolioPeriodo
--AND cta.idAsignatura = @idAsignatura
AND cpe.idOptativa IN ( SELECT DISTINCT cpe.idOptativa FROM dbo.Ctrl_PlanEstudios AS cpe
WHERE cpe.idAsignatura = @idAsignatura
AND cpe.idPlanEstudios = @idPlanEstudios)
AND ci.idOfEduc = @idOfEduc
AND ci.idAlumno = @idAlumno
)
BEGIN
--------Valida creditos maximos y minimos
DECLARE @iCreditosTotales DECIMAL(7, 3) ,
@iCreditosAsig DECIMAL(7, 3);
SELECT @iCreditosTotales = SUM(ca.iCreditos)
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cta.idAsignatura
WHERE cta.iFolioInscripcion = @iFolioInscUlt;
IF ( @iCreditosTotales + @iCreditosAsig ) > ( SELECT ISNULL(cpe.iCreMaxXGrado, 99999999)
FROM dbo.Cat_PlanEstudios AS cpe
WHERE cpe.idPlanEstudios = @idPlanEstudios
)
BEGIN
SET @iResultado = 1;
SET @sResultado = 'Superaste los creditos permitidos por grado.';
END;
DECLARE @asigActuales INT;
SELECT @asigActuales = COUNT(*)
FROM ( SELECT DISTINCT
ca.idAsignatura
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cta.idAsignatura
INNER JOIN dbo.Ctrl_PlanEstudios AS cpe ON cpe.idAsignatura = cta.idAsignatura
AND cpe.idPlanEstudios = cta.idPlanEstudios
LEFT JOIN dbo.Ctrl_NucleosCantidadAsignaturas AS cnca ON cnca.idNucleo = cpe.idNucleo
WHERE ci.idAlumno = @idAlumno
AND cnca.idNucleo = ( SELECT DISTINCT
cpe33.idNucleo
FROM dbo.Ctrl_PlanEstudios AS cpe33
WHERE cpe33.idPlanEstudios = @idPlanEst
AND cpe33.idAsignatura = @idAsignatura
AND ci.idPlantel = @idPlantel
)
) TR;
DECLARE @sNucleo VARCHAR(50) = '' ,
@bSuperaAsig BIT = 0;
SELECT DISTINCT
@bSuperaAsig = ( CASE WHEN cnca.iMaxAsig >= @asigActuales THEN 0
ELSE 1
END ) ,
@sNucleo = cn.sNucleo
FROM dbo.Ctrl_NucleosCantidadAsignaturas AS cnca
INNER JOIN dbo.Cat_Nucleos AS cn ON cn.idNucleo = cnca.idNucleo
WHERE cnca.idNucleo = ( SELECT DISTINCT
cpe33.idNucleo
FROM dbo.Ctrl_PlanEstudios AS cpe33
WHERE cpe33.idPlanEstudios = @idPlanEst
AND cpe33.idAsignatura = @idAsignatura
AND cnca.idPlantel = @idPlantel
);
IF 1 = @bSuperaAsig
BEGIN
SET @iResultado = 2;
SET @sResultado = 'Superaste la cantidad de materias para el núcleo ' + UPPER(@sNucleo) + '.';
END;
END;
ELSE
BEGIN
SET @iResultado = 3;
SET @sResultado = 'Ya cuentas con una inscripción a esta materia.';
END;
END
ELSE
BEGIN
SET @iResultado = 5;
SET @sResultado = 'Se ha llegado al cupo máximo permitido del grupo.';
END
END;
ELSE
BEGIN
SET @iResultado = 4;
SET @sResultado = 'La materia se empalma con otra asignatura.';
END;
IF @iResultado = 0 --VALIDA QUE NO HAYA APROBADO LA MATERIA EN OTRO GRADO
AND 0 < (
SELECT COUNT(*) FROM (
SELECT CASE WHEN ctad.dCalificacion >= cfe3.dMinimaAprobatoria THEN 1 ELSE 0 END AS bAprobada, cta.idAsignatura FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_TrayectoriaAcademicaDet AS ctad
ON ctad.iFolioTrayectoria = cta.iFolio
INNER JOIN dbo.Ctrl_Inscripciones AS ci
ON ci.iFolio = cta.iFolioInscripcion
INNER JOIN dbo.Ctrl_PlanEstudios AS cpe
ON cpe.idAsignatura = cta.idAsignatura
AND cpe.idPlanEstudios = cta.idPlanEstudios
INNER JOIN dbo.Cat_PlanEstudios AS cpe2
ON cpe2.idPlanEstudios = cpe.idPlanEstudios
INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe
ON cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,cpe2.idFormEvaluacion)
AND cfe.idEvaluacion = ctad.idEvaluacion
INNER JOIN dbo.Cat_FormEvaluacion AS cfe3 ON cfe3.idFormEvaluacion = cfe.idFormEvaluacion
WHERE ci.idAlumno = @idAlumno
AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
AND cta.idAsignatura = @idAsignatura
--AND ci.iFolioPeriodo = @iFolioPeriodo
AND cfe.bPromedioGral = 1
) TR
WHERE TR.bAprobada = 1
)
BEGIN
SET @iResultado = 6
SET @sResultado = 'No te puedes inscribir anteriormente has aprobado la materia.'
END
IF @iResultado = 0 ---VALIDA LA SERIACION DE LA MATERIA
AND 0 < (SELECT COUNT(cpe.idAsignatura)
FROM dbo.Ctrl_PlanEstudios AS cpe
INNER JOIN dbo.Ctrl_SeriacionAsignaturas AS csa
ON csa.idAsignatura = cpe.idAsignatura
AND csa.idPlanEstudios = cpe.idPlanEstudios
INNER JOIN dbo.Cat_PlanEstudios AS cpe2
ON cpe2.idPlanEstudios = cpe.idPlanEstudios
INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe
ON cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,
cpe2.idFormEvaluacion)
WHERE cpe.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
AND csa.idAsignatura = @idAsignatura
AND cfe.bPromedioGral = 1
AND csa.bSimultanea = 0
)
AND (
(0 = (SELECT COUNT(*)
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_Inscripciones AS ci
ON ci.iFolio = cta.iFolioInscripcion
INNER JOIN dbo.Ctrl_SeriacionAsignaturas AS csa
ON csa.idAsignatura = cta.idAsignatura
AND csa.idPlanEstudios = cta.idPlanEstudios
WHERE ci.idAlumno = @idAlumno
AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
AND csa.idAsignatura = @idAsignatura
))
OR 0 < (SELECT COUNT(*)
FROM (SELECT CASE WHEN ctad.dCalificacion >= cfe2.dMinimaAprobatoria
THEN 1
ELSE 0
END AS bAprobada
, cta.idAsignatura
FROM dbo.Ctrl_TrayectoriaAcademica AS cta
INNER JOIN dbo.Ctrl_TrayectoriaAcademicaDet
AS ctad
ON ctad.iFolioTrayectoria = cta.iFolio
INNER JOIN dbo.Ctrl_Inscripciones AS ci
ON ci.iFolio = cta.iFolioInscripcion
INNER JOIN dbo.Ctrl_PlanEstudios AS cpe
ON cpe.idAsignatura = cta.idAsignatura
AND cpe.idPlanEstudios = cta.idPlanEstudios
INNER JOIN dbo.Cat_PlanEstudios AS cpe2
ON cpe2.idPlanEstudios = cpe.idPlanEstudios
INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe
ON cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,
cpe2.idFormEvaluacion)
AND cfe.idEvaluacion = ctad.idEvaluacion
INNER JOIN dbo.Cat_FormEvaluacion AS cfe2 ON cfe2.idFormEvaluacion = cfe.idFormEvaluacion
INNER JOIN dbo.Ctrl_SeriacionAsignaturas
AS csa
ON csa.idAsignatura = cta.idAsignatura
AND csa.idPlanEstudios = cta.idPlanEstudios
WHERE ci.idAlumno = @idAlumno
AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
AND csa.idAsignatura = @idAsignatura
--AND ci.iFolioPeriodo = @iFolioPeriodo
AND cfe.bPromedioGral = 1
AND csa.bSimultanea = 0
) TR
WHERE TR.bAprobada = 0
)
)
BEGIN
SET @iResultado = 7;
SET @sResultado = 'No puedes agregar esta materia ya que te falta aprobar alguna de las que van searidas.';
END;
SELECT @iResultado AS iResultado ,
@sResultado AS sResultado;
END;