Procesamiento de puntos y sanciones

Se concluye el proceso de desarrollo e implementación del Stored Procedure encargado de procesar debidamente los acumulados de saldo de puntos por corredor por campeonato, saldo de puntos de castigo y el acumulado total de tiempo corrido.

Para los saldos de puntos finales y puntos de castigo se implementó además un saldo autorizado o saldo comprometido, especialmente con fines ilustrativos, dicho atributo extra se tomo en cuenta al codificar la respectiva transacción responsable de las inserciones de movimientos, tanto pendientes como procesados (Aunque en realidad esto se ejecuta en una sola fase de manera inmediata a la inserción de movimientos pendientes).

 La siguiente metodología fue la utilizada para aplicar dichos movimientos:

1. Se insertan los movimientos dentro de la tabla de movimientos pendientes, de manera que tal que anulen, aritméticamente hablando, el movimiento pendiente original ( o sea el inverso del monto especificado en el movimiento pendiente original. Ej: Crédito Pendiente de 1 punto, entonces se insertará en la tabla de Movimientos Pendientes un "Crédito Pendiente" de  -1 puntos  ) .
2. Después se modifica el saldo No Aplicante (Autorizado o Comprometido).
3. Después se añade  el movimiento pero a la tabla de Movimientos Procesados.
4. Y se aplica el saldo final o aplicante.

En el siguiente código puede observarse la implementación del stored procedure correspondiente:

-- =============================================

-- Author: <Author: Vinicio Flores>

-- Create date: <Create Date: August 29th, 2015>

-- It inserts and process all the movements generated inside the database system

-- =============================================

ALTER PROCEDURE [dbo].[SP_ResultProcessing]

@Challenge INT,

@PostIn VARCHAR(50),

@PostDate DATE,

@PostBy VARCHAR(50)

-- Add the parameters for the stored procedure here

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY

DECLARE @PositionsProc TABLE(ID INT IDENTITY(1,1),PosID INT PRIMARY KEY,FK_Races INT,FK_Runners INT,Position INT)

DECLARE @PunishProc TABLE(ID INT IDENTITY(1,1),PunID INT PRIMARY KEY,Amount INT,FK_Races INT,FK_PunishmentType INT,FK_Runners INT)

INSERT INTO @PositionsProc(PosID,FK_Races,FK_Runners,Position)

SELECT P.ID,P.FK_Races,P.FK_Runners,P.Position

FROM Positions P

WHERE (P.Position >= 1) AND (P.Position <= 20)

INSERT INTO @PunishProc(PunID,Amount,FK_Races,FK_PunishmentType,FK_Runners)

SELECT P.ID, P.Points,P.FK_Races,P.FK_PunishmentType,P.FK_Runners

FROM Punishments P

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

INSERT INTO dbo.PointsMovementPending(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT 20-Position, 1, dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),@PostIn,@PostDate,@PostBy

FROM @PositionsProc P

ORDER BY P.PosID

UPDATE dbo.RunnersXChallenge SET AcumTime = dbo.FN_SumRunnerTimeByRace(RC.ID)

-- update dbo.RunnersXChallenge SET AcumTime = '00:00:00'

FROM dbo.RunnersXChallenge RC

INNER JOIN @PositionsProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

WHERE RC.FK_Challenge = @Challenge

UPDATE dbo.RunnersXChallenge SET AcumPointsAuthorized = AcumPointsAuthorized + (20-PP.Position)

FROM dbo.RunnersXChallenge RC

INNER JOIN @PositionsProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PointsMovementPending(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT -(20-Position),2,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),@PostIn,@PostDate,@PostBy

FROM @PositionsProc P

ORDER BY P.PosID

UPDATE dbo.RunnersXChallenge SET AcumPointsAuthorized = AcumPointsAuthorized + -(20-PP.Position)

FROM dbo.RunnersXChallenge RC

INNER JOIN @PositionsProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PointsMovementProcessed(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT 20-Position, 1, dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),@PostIn,@PostDate,@PostBy

FROM @PositionsProc P

ORDER BY P.PosID

UPDATE dbo.RunnersXChallenge SET AcumPoints = AcumPoints + (20-PP.Position)

FROM dbo.RunnersXChallenge RC

INNER JOIN @PositionsProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

　

-- Process the punish points movements tables

INSERT INTO dbo.PunishPtsMovementPending(Amount,FK_Punishments,FK_RunnersXChallenge,FK_TypePunishPtsMovement,PostIn,PostDate,PostBy)

SELECT Amount,P.FK_PunishmentType,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners) ,1 ,@PostIn,@PostDate,@PostBy

FROM @PunishProc P

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPunishPtsAuthorized = AcumPunishPtsAuthorized + Amount

FROM dbo.RunnersXChallenge RC

INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PunishPtsMovementPending(Amount,FK_Punishments,FK_RunnersXChallenge,FK_TypePunishPtsMovement,PostIn,PostDate,PostBy)

SELECT -Amount,P.FK_PunishmentType,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),2,@PostIn,@PostDate,@PostBy

FROM @PunishProc P

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPunishPtsAuthorized = AcumPunishPtsAuthorized + -Amount

FROM dbo.RunnersXChallenge RC

INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PunishPtsMovementProcessed(Amount,FK_Punishments,FK_RunnersXChallenge,FK_TypePunishPtsMovement,PostIn,PostDate,PostBy)

SELECT Amount,P.FK_PunishmentType,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),1,@PostIn,@PostDate,@PostBy

FROM @PunishProc P

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPunishPts = AcumPunishPts + Amount

FROM dbo.RunnersXChallenge RC

INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

--- Executes the substraction of the punishment points over the total points balance (acumpoints)

INSERT INTO dbo.PointsMovementPending(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT RC.AcumPunishPts,2,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners) ,@PostIn,@PostDate,@PostBy

FROM @PunishProc P INNER JOIN dbo.RunnersXChallenge RC ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners)

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPointsAuthorized = AcumPointsAuthorized + -RC.AcumPunishPts

FROM dbo.RunnersXChallenge RC INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PointsMovementPending(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT -RC.AcumPunishPts,1,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners),@PostIn,@PostDate,@PostBy

FROM @PunishProc P INNER JOIN dbo.RunnersXChallenge RC ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners)

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPointsAuthorized = AcumPointsAuthorized + RC.AcumPunishPts

FROM dbo.RunnersXChallenge RC INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

INSERT INTO dbo.PointsMovementProcessed(Amount,FK_TypeMovement,FK_RunnersXChallenge,PostIn,PostDate,PostBy)

SELECT RC.AcumPunishPts,2,dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners) ,@PostIn,@PostDate,@PostBy

FROM @PunishProc P INNER JOIN dbo.RunnersXChallenge RC ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,P.FK_Runners)

ORDER BY P.PunID

UPDATE dbo.RunnersXChallenge SET AcumPoints = AcumPoints + -RC.AcumPunishPts

FROM dbo.RunnersXChallenge RC INNER JOIN @PunishProc PP

ON RC.ID = dbo.FN_GetRunnerXChallengeByChallenge(@Challenge,PP.FK_Runners)

COMMIT

RETURN 1

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

RETURN @@ERROR * -1

END CATCH

END