FITOMN of AM v3
7/14/2017 - 4:33 PM
CERTIFICADOS ACADÉMICOS - fn2CES_ALU_ObtenerFolioCertificado
CERTIFICADOS ACADÉMICOS - fn2CES_ALU_ObtenerFolioCertificado
----------------------------INICIO PROCEDIMIENTO ------------------------
IF OBJECT_ID('fn2CES_ALU_ObtenerFolioCertificado', 'TF') IS NOT NULL
DROP FUNCTION fn2CES_ALU_ObtenerFolioCertificado
GO
CREATE FUNCTION [dbo].[fn2CES_ALU_ObtenerFolioCertificado]
(
-- >>>>>> PARÁMETROS <<<<<<
@idFormatoCer INT,
@idPlantel INT,
@idPlanEst INT,
@idAlumno BIGINT
/* --------------------------------------------------------------------------------------------------------------
FECHA | AUTOR | DESCRIPCION
27/Jun/2017 | Luis Bernal |
13/Jul/2017 | Luis Bernal | Se agrega iConteo a la tabla resultado devuelta por la función.
--------------------------------------------------------------------------------------------------------------*/
)
RETURNS @TablaResult TABLE(sFolioCer VARCHAR(100), iFolioConfigDet INT, iConteo BIGINT)
AS
BEGIN
-- >>>>>> VARIABLES <<<<<<
DECLARE @iConteoFolio BIGINT,
@iDigitosConteo TINYINT,
@idComponente1 TINYINT,
@idComponente2 TINYINT,
@idComponente3 TINYINT,
@sOtroComponente1 VARCHAR(100),
@sOtroComponente2 VARCHAR(100),
@sOtroComponente3 VARCHAR(100),
@iOrdenConteo TINYINT,
@iOrdenComponente1 TINYINT,
@iOrdenComponente2 TINYINT,
@iOrdenComponente3 TINYINT,
@sSeparador1 VARCHAR(1),
@sSeparador2 VARCHAR(1),
@sSeparador3 VARCHAR(1),
@iInicioConteo INT,
@iConteoActual BIGINT,
@sConteoFolio VARCHAR(50),
@sComponente1 VARCHAR(100),
@sComponente2 VARCHAR(100),
@sComponente3 VARCHAR(100),
@sPalabra1 VARCHAR(100),
@sPalabra2 VARCHAR(100),
@sPalabra3 VARCHAR(100),
@sPalabra4 VARCHAR(100),
@sFolioCer VARCHAR(100),
@iFolioConfigDet INT,
@idEntidadF1 TINYINT,
@idEntidadF2 TINYINT,
@idEntidadF3 TINYINT
-- >>>>>> FUNCIÓN <<<<<<
-- *** Obtener configuración ***
SELECT TOP 1
@iConteoFolio = ISNULL(cfci.iConteoFolio, 0),
@iDigitosConteo = cccf.iDigitosConteo,
@idComponente1 = cccf.idComponente1,
@idComponente2 = cccf.idComponente2,
@idComponente3 = cccf.idComponente3,
@sOtroComponente1 = cccf.sOtroComponente1,
@sOtroComponente2 = cccf.sOtroComponente2,
@sOtroComponente3 = cccf.sOtroComponente3,
@iOrdenConteo = cccf.iOrdenConteo,
@iOrdenComponente1 = cccf.iOrdenComponente1,
@iOrdenComponente2 = cccf.iOrdenComponente2,
@iOrdenComponente3 = cccf.iOrdenComponente3,
@sSeparador1 = cccf.sSeparador1,
@sSeparador2 = cccf.sSeparador2,
@sSeparador3 = cccf.sSeparador3,
@iInicioConteo = cccd.iInicioConteo,
@iFolioConfigDet = cccd.iFolio
FROM dbo.Ctrl_ConfigCertificados AS ccc
INNER JOIN dbo.Ctrl_ConfigCertificadosDet AS cccd ON cccd.idConfgTipoCer = ccc.idConfigTipoCer
AND ccc.idFormatoCer = @idFormatoCer
AND cccd.idPlantel = @idPlantel
AND cccd.idPlanEst = @idPlanEst
INNER JOIN dbo.Cat_ConfigCerFolios AS cccf ON cccf.iFolioClaveCer = cccd.iFolioClaveCer
AND cccf.bActivo = 1
LEFT JOIN dbo.Ctrl_FormatosCertificadosImpresos AS cfci ON cfci.idCertificado = ccc.idFormatoCer
AND cfci.iFolioClaveCer = cccd.iFolio
ORDER BY cfci.iFolio DESC
-- *** Obtener conteo actual y crear nuevo conteo ***
IF @iConteoFolio = 0
BEGIN
SET @iConteoActual = @iInicioConteo;
END
ELSE
BEGIN
SET @iConteoActual = @iConteoFolio + 1;
END
SET @sConteoFolio = CASE @iDigitosConteo
WHEN 1 THEN '0' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 2 THEN '00' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 3 THEN '000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 4 THEN '0000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 5 THEN '00000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 6 THEN '000000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 7 THEN '0000000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 8 THEN '00000000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 9 THEN '000000000' + CAST(@iConteoActual AS VARCHAR(10))
WHEN 10 THEN '0000000000' + CAST(@iConteoActual AS VARCHAR(10))
END
--PRINT @sConteoFolio
-- *** Verificar Orden de Cada Palabra y asignar valor correspondiente ***
SET @sPalabra1 = ''
SET @sPalabra2 = ''
SET @sPalabra3 = ''
SET @sPalabra4 = ''
IF @iOrdenConteo = 1
BEGIN
SET @sPalabra1 = @sConteoFolio
END
ELSE
BEGIN
IF @iOrdenConteo = 2
BEGIN
SET @sPalabra2 = @sConteoFolio
END
ELSE
BEGIN
IF @iOrdenConteo = 3
BEGIN
SET @sPalabra3 = @sConteoFolio
END
ELSE
BEGIN
IF @iOrdenConteo = 4
BEGIN
SET @sPalabra4 = @sConteoFolio
END
END
END
END
SET @sComponente1 = CASE @idComponente1
WHEN 1 THEN ''
WHEN 2 THEN CONVERT(VARCHAR(2), @idEntidadF1)
WHEN 3 THEN (SELECT sAbreviatura FROM dbo.Cat_Planteles WHERE idPlantel = @idPlantel)
WHEN 4 THEN (SELECT sAbreviatura FROM dbo.Cat_OfEduc WHERE idOfEduc = @idPlanEst)
WHEN 5 THEN (SELECT CONVERT(CHAR(3), DATENAME(MONTH, dFechaIngreso), 0) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 6 THEN (SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, dFechaIngreso)) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 7 THEN ''
WHEN 8 THEN (SELECT cp.sAbCorta FROM dbo.Ctrl_Inscripciones ci JOIN dbo.Ctrl_Periodos cp ON cp.iFolio = ci.iFolioPeriodo AND ci.idAlumno = @idAlumno
AND ci.iFolioPeriodo = (SELECT MAX(iFolioPeriodo) FROM dbo.Ctrl_Inscripciones WHERE idAlumno = @idAlumno))
WHEN 9 THEN RIGHT(DATENAME(YEAR, GETDATE()), 2)
WHEN 10 THEN CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))
WHEN 11 THEN @sOtroComponente1
END
SET @sComponente2 = CASE @idComponente2
WHEN 1 THEN ''
WHEN 2 THEN CONVERT(VARCHAR(2), @idEntidadF2)
WHEN 3 THEN (SELECT sAbreviatura FROM dbo.Cat_Planteles WHERE idPlantel = @idPlantel)
WHEN 4 THEN (SELECT sAbreviatura FROM dbo.Cat_OfEduc WHERE idOfEduc = @idPlanEst)
WHEN 5 THEN (SELECT CONVERT(CHAR(3), DATENAME(MONTH, dFechaIngreso), 0) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 6 THEN (SELECT CONVERT(VARCHAR(2),DATEPART(MONTH, dFechaIngreso)) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 7 THEN ''
WHEN 8 THEN (SELECT cp.sAbCorta FROM dbo.Ctrl_Inscripciones ci JOIN dbo.Ctrl_Periodos cp ON cp.iFolio = ci.iFolioPeriodo AND ci.idAlumno = @idAlumno
AND ci.iFolioPeriodo = (SELECT MAX(iFolioPeriodo) FROM dbo.Ctrl_Inscripciones WHERE idAlumno = @idAlumno))
WHEN 9 THEN RIGHT(DATENAME(YEAR, GETDATE()), 2)
WHEN 10 THEN CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))
WHEN 11 THEN @sOtroComponente2
END
SET @sComponente3 = CASE @idComponente3
WHEN 1 THEN ''
WHEN 2 THEN CONVERT(VARCHAR(2), @idEntidadF3)
WHEN 3 THEN (SELECT sAbreviatura FROM dbo.Cat_Planteles WHERE idPlantel = @idPlantel)
WHEN 4 THEN (SELECT sAbreviatura FROM dbo.Cat_OfEduc WHERE idOfEduc = @idPlanEst)
WHEN 5 THEN (SELECT CONVERT(CHAR(3), DATENAME(MONTH, dFechaIngreso), 0) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 6 THEN (SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, dFechaIngreso)) FROM dbo.Cat_Alumnos WHERE idAlumno = @idAlumno)
WHEN 7 THEN ''
WHEN 8 THEN (SELECT cp.sAbCorta FROM dbo.Ctrl_Inscripciones ci JOIN dbo.Ctrl_Periodos cp ON cp.iFolio = ci.iFolioPeriodo AND ci.idAlumno = @idAlumno
AND ci.iFolioPeriodo = (SELECT MAX(iFolioPeriodo) FROM dbo.Ctrl_Inscripciones WHERE idAlumno = @idAlumno))
WHEN 9 THEN RIGHT(DATENAME(YEAR, GETDATE()), 2)
WHEN 10 THEN CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))
WHEN 11 THEN @sOtroComponente3
END
IF @iOrdenComponente1 = 1
BEGIN
IF @sPalabra1 = ''
BEGIN
SET @sPalabra1 = @sComponente1
END
END
ELSE
BEGIN
IF @iOrdenComponente1 = 2
BEGIN
IF @sPalabra2 = ''
BEGIN
SET @sPalabra2 = @sComponente1
END
END
ELSE
BEGIN
IF @iOrdenComponente1 = 3
BEGIN
IF @sPalabra3 = ''
BEGIN
SET @sPalabra3 = @sComponente1
END
END
ELSE
BEGIN
IF @iOrdenComponente1 = 4
BEGIN
IF @sPalabra4 = ''
BEGIN
SET @sPalabra4 = @sComponente1
END
END
END
END
END
IF @iOrdenComponente2 = 1
BEGIN
IF @sPalabra1 = ''
BEGIN
SET @sPalabra1 = @sComponente2
END
END
ELSE
BEGIN
IF @iOrdenComponente2 = 2
BEGIN
IF @sPalabra2 = ''
BEGIN
SET @sPalabra2 = @sComponente2
END
END
ELSE
BEGIN
IF @iOrdenComponente2 = 3
BEGIN
IF @sPalabra3 = ''
BEGIN
SET @sPalabra3 = @sComponente2
END
END
ELSE
BEGIN
IF @iOrdenComponente2 = 4
BEGIN
IF @sPalabra4 = ''
BEGIN
SET @sPalabra4 = @sComponente2
END
END
END
END
END
IF @iOrdenComponente3 = 1
BEGIN
IF @sPalabra1 = ''
BEGIN
SET @sPalabra1 = @sComponente3
END
END
ELSE
BEGIN
IF @iOrdenComponente3 = 2
BEGIN
IF @sPalabra2 = ''
BEGIN
SET @sPalabra2 = @sComponente3
END
END
ELSE
BEGIN
IF @iOrdenComponente3 = 3
BEGIN
IF @sPalabra3 = ''
BEGIN
SET @sPalabra3 = @sComponente3
END
END
ELSE
BEGIN
IF @iOrdenComponente3 = 4
BEGIN
IF @sPalabra4 = ''
BEGIN
SET @sPalabra4 = @sComponente3
END
END
END
END
END
SET @sFolioCer = @sPalabra1 + @sSeparador1 + @sPalabra2 + @sSeparador2 + @sPalabra3 + @sSeparador3 + @sPalabra4;
INSERT INTO @TablaResult
( sFolioCer, iFolioConfigDet, iConteo )
VALUES ( @sFolioCer, -- sFolioCer - varchar(100)
@iFolioConfigDet, -- iFolioConfigDet - int
@iConteoActual -- iConteo - bigint
)
RETURN;
END