Se inicia la programación de SP diarios

(13 horas)

Se empezó a analizar cuales eran los procedimientos que tendrían que ser diseñados para lograr hacer funcionar un proceso diario que se encargara de procesar los movimientos individualmente. Se indentificó que para este proceso que corre la fecha de cobro, era necesario crear una función que se encargue de generar el texto que será usado para la tabla TextoEstadoCuenta. Para ello programamos el siguiente procedimiento:

USE [II Proyecto - Banco]
GO

CREATE FUNCTION BAFN_Texto (@inTextID int, @inFecha date)

    RETURNS varchar(max)

AS
BEGIN
   
    DECLARE @outputTexto varchar(max)
    DECLARE @ECID int
    DECLARE @NombreCliente varchar(200)
    DECLARE @EmailCliente varchar(50)
    DECLARE @Identificacion varchar(50)
    DECLARE @SaldoInicial money
    DECLARE @SaldoActual money
    DECLARE @SaldoAcumulado money
    DECLARE @InteresAcumulado money
   
    SELECT @ECID = ID FROM dbo.Estado_Cuenta WHERE Fk_Texto_Estado_Cuenta = @inTextID

    SELECT @NombreCliente = A.Nombre, @EmailCliente = A.Email, @Identificacion = A.Identificacion, @SaldoInicial = E.Saldo_Inicial, @SaldoAcumulado = E.Saldo_Acumulado, @InteresAcumulado = E.Interes_Acumulado
    FROM dbo.Cuenta_Ahorrista A, dbo.Cuenta C, dbo.Estado_Cuenta E
    WHERE A.ID = C.FK_Cuenta_Ahorrista
    AND C.ID = E.Fk_Cuenta
    AND E.ID = @ECID

    SET @SaldoActual = @SaldoInicial + @SaldoAcumulado + @InteresAcumulado

    SET @outputTexto = 'Para ' + @NombreCliente + ' (' + @EmailCliente + ')' + CHAR(13)+CHAR(10) +
                       'Identificacion: ' + @Identificacion + CHAR(13)+CHAR(10) +
                       'Fecha de estado de cuenta ' + CONVERT(varchar(8), @inFecha, 101) + CHAR(13)+CHAR(10) +
                       'a las ' + CONVERT(VARCHAR(8), GETDATE(), 108) + CHAR(13)+CHAR(10) +
                       CHAR(13)+CHAR(10) +
                       'Saldo inicial: ' + CONVERT(varchar, @SaldoInicial) + CHAR(13)+CHAR(10) +
                       'Saldo acumulado: ' + CONVERT(varchar, @SaldoInicial) + CHAR(13)+CHAR(10) +
                       'Interes acumulado: ' + CONVERT(varchar, @SaldoInicial) + CHAR(13)+CHAR(10) +
                       'Saldo actual: ' + CONVERT(varchar, @SaldoActual) + CHAR(13)+CHAR(10) +
                       CHAR(13)+CHAR(10) +
                       'Movimientos:' + CHAR(13)+CHAR(10)

    SELECT @outputTexto = @outputTexto + T.Nombre + char(9),
           @outputTexto = @outputTexto + CONVERT(varchar, M.Monto) + char(9),
           @outputTexto = @outputTexto + CONVERT(varchar(8), M.Fecha, 101) + CHAR(13)+CHAR(10)
    FROM dbo.Movimientos_Estado_Cuenta M, dbo.Tipo_Movimiento_Estado_Cuenta T
    WHERE M.Fk_Estado_Cuenta = @ECID
    ORDER BY M.Fecha

    RETURN @outputTexto
   
END
GO

Como podemos ver, este procedimiento busca de todos los Clientes los datos que ocupa para generar y les genera un texto que está pensado para enviar por correo electronico al cliente.

De esta manera continuaremos con los siguientes codigos:

USE [II Proyecto - Banco]
GO

CREATE PROCEDURE BASP_Corte
   
    @inFecha date

AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        BEGIN TRANSACTION Transaccion

        --Primero: Actualizamos la fecha del dia de corte de la tabla Estado_Cuenta
        UPDATE [dbo].[Estado_Cuenta]
           SET [Fecha_Corte] = @inFecha
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         WHERE ID in (SELECT MAX(ID)
                      FROM dbo.Estado_Cuenta
                      WHERE Fk_Cuenta in (SELECT ID
                                          FROM dbo.Cuenta
                                          WHERE Fecha_Corte = DATEPART(DAY, @inFecha))
                      GROUP BY Fk_Cuenta)
       
        --Segundo: Sumamos todos los movimientos a la Cuenta
        UPDATE [dbo].[Cuenta]
           SET [Saldo_Parcial] = Saldo_Parcial + E.Dinero
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         FROM (SELECT Fk_Cuenta, Saldo_Acumulado + Interes_Acumulado AS Dinero
               FROM dbo.Estado_Cuenta
               WHERE Fecha_Corte = @inFecha) E
         WHERE ID = E.Fk_Cuenta

        --Cuarto: Preparamos nuevos TextoEstadoCuenta, para los nuevos Estados de Cuenta
        INSERT INTO dbo.TextoEstadoCuenta(Texto)
            SELECT ' ' FROM dbo.Cuenta WHERE Fecha_Corte = DATEPART(DAY, @inFecha)

        UPDATE [dbo].[Estado_Cuenta]
           SET [Fk_Texto_Estado_Cuenta] = T.ID
          FROM (SELECT ID FROM dbo.TextoEstadoCuenta WHERE Texto = ' ') T
         WHERE Fecha_Corte = @inFecha

        --Tercero: Acutalizamos el texto del Estado de Cuenta
        UPDATE [dbo].[TextoEstadoCuenta]
           SET [Texto] = dbo.BAFN_Texto(ID, @inFecha)
         WHERE Texto = ' '

        --Quinto: Creamos los nuevos estados de cuenta para el siguiente mes
        INSERT INTO [dbo].[Estado_Cuenta]
                   ([Fk_Cuenta]
                   ,[Fecha_Corte]
                   ,[Interes_Acumulado]
                   ,[Saldo_Inicial]
                   ,[Saldo_Acumulado]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT C.ID, DATEADD(MONTH, 1, @inFecha), 0, C.Saldo_Parcial, 0, @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
                FROM dbo.Cuenta C
                WHERE C.Fecha_Corte = DATEPART(DAY, @inFecha)
                GROUP BY C.ID, C.Saldo_Parcial
       
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

 Este otro SP se encargaría de cobrar una multa a aquellas personas que han retirado más de X cantidad de veces duarante el mes antes de fecha de corte.

USE [II Proyecto - Banco]
GO

CREATE PROCEDURE BASP_MultaMaxRetiros
   
    @inFecha date

AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_TipoMovCargoXServicio int
        DECLARE @TipoMov int
        SELECT @ID_TipoMovCargoXServicio = ID, @TipoMov = Tipo FROM dbo.Tipo_Movimiento_Estado_Cuenta WHERE Nombre = 'Cargos por Servicio'

        BEGIN TRANSACTION Transaccion

        --Creamos una entrada de multa
        INSERT INTO [dbo].[Movimientos_Estado_Cuenta]
                   ([Fk_Tipo_Movimiento_Estado_Cuenta]
                   ,[Fk_Estado_Cuenta]
                   ,[Fecha]
                   ,[Monto]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT @ID_TipoMovCargoXServicio, E.ID, @inFecha, CONVERT(int, P.Valor) * @TipoMov, @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
             FROM dbo.Tipo_Cuenta T, dbo.Cuenta C, dbo.Estado_Cuenta E, dbo.Parametros P
             WHERE C.FK_Tipo_Cuenta = T.ID
             AND E.Fk_Cuenta = C.ID
             AND C.Fecha_Corte = DATEPART(DAY, @inFecha)
             AND P.Nombre = 'Multa Maximo Retiros'
             AND T.MaxRetirosXmes < (SELECT SUM(1)
                                     FROM dbo.Movimientos_Estado_Cuenta M
                                     WHERE M.Fk_Tipo_Movimiento_Estado_Cuenta IN (SELECT ID
                                                                                  FROM dbo.Tipo_Movimiento_Estado_Cuenta
                                                                                  WHERE Nombre = 'Retiro por Ventanilla'
                                                                                  AND Nombre = 'Retiro por ATM'))

       
        --Le sumamos la multa
        UPDATE [dbo].[Estado_Cuenta]
           SET [Saldo_Acumulado] = [Saldo_Acumulado] + S.Suma
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         FROM (SELECT SUM(M.Monto) Suma, M.Fk_Estado_Cuenta
               FROM dbo.Movimientos_Estado_Cuenta M
               WHERE M.Fk_Tipo_Movimiento_Estado_Cuenta IN (SELECT ID
                                                            FROM dbo.Tipo_Movimiento_Estado_Cuenta
                                                            WHERE Nombre = 'Cargos por Servicio')
               GROUP BY M.Fk_Estado_Cuenta) S

         WHERE ID = S.Fk_Estado_Cuenta
           
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

 Durante este tiempo también se estuvo programando otro procedimiento que cobra si el saldo está por debajo del minimo.

USE [II Proyecto - Banco]
GO

CREATE PROCEDURE BASP_MultaSaldoMinimo
   
    @inFecha date

AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_TipoMultaSaldoMinimo int
        DECLARE @TipoMov int
        SELECT @ID_TipoMultaSaldoMinimo = ID, @TipoMov = Tipo FROM dbo.Tipo_Movimiento_Estado_Cuenta WHERE Nombre = 'Multa Saldo Minimo'

        BEGIN TRANSACTION Transaccion

        --Creamos una entrada de multa
        INSERT INTO [dbo].[Movimientos_Estado_Cuenta]
                   ([Fk_Tipo_Movimiento_Estado_Cuenta]
                   ,[Fk_Estado_Cuenta]
                   ,[Fecha]
                   ,[Monto]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT @ID_TipoMultaSaldoMinimo, E.ID, @inFecha, T.Multa_Saldo_Minimo * @TipoMov, @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
             FROM dbo.Tipo_Cuenta T, dbo.Cuenta C, dbo.Estado_Cuenta E
             WHERE C.FK_Tipo_Cuenta = T.ID
             AND E.Fk_Cuenta = C.ID
             AND C.Fecha_Corte = DATEPART(DAY, @inFecha)
             AND T.Saldo_Minimo < E.Saldo_Inicial + E.Saldo_Acumulado + E.Interes_Acumulado

       
        --Le sumamos la multa
        UPDATE [dbo].[Estado_Cuenta]
           SET [Saldo_Acumulado] = [Saldo_Acumulado] + S.Suma
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         FROM (SELECT SUM(M.Monto) Suma, M.Fk_Estado_Cuenta
               FROM dbo.Movimientos_Estado_Cuenta M
               WHERE M.Fk_Tipo_Movimiento_Estado_Cuenta IN (SELECT ID
                                                            FROM dbo.Tipo_Movimiento_Estado_Cuenta
                                                            WHERE Nombre = 'Multa Saldo Minimo')
               GROUP BY M.Fk_Estado_Cuenta) S

         WHERE ID = S.Fk_Estado_Cuenta
           
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

El siguiente procedimiento se encarga de sumar el interé acumulado diario:

USE [II Proyecto - Banco]
GO

CREATE PROCEDURE BASP_InteresAcumulado
   
    @inFecha date

AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_TipoCredito int
        SELECT @ID_TipoCredito = ID FROM dbo.Tipo_Movimiento_Interes WHERE Nombre = 'Credito'

        BEGIN TRANSACTION Transaccion

        --Creamos una entrada de multa
        INSERT INTO [dbo].[Movimientos_De_Interes]
                   ([Fk_Tipo_Movimiento_Interes]
                   ,[Fk_Estado_Cuenta]
                   ,[Fecha]
                   ,[Monto]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT @ID_TipoCredito, E.ID, @inFecha, (T.Tasa_Interes / 100) * (E.Saldo_Inicial + E.Interes_Acumulado), @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
             FROM dbo.Tipo_Cuenta T, dbo.Cuenta C, dbo.Estado_Cuenta E
             WHERE C.FK_Tipo_Cuenta = T.ID
             AND E.Fk_Cuenta = C.ID
             AND @inFecha <= E.Fecha_Corte

        --Le sumamos la multa
        UPDATE [dbo].[Estado_Cuenta]
           SET [Interes_Acumulado] = [Interes_Acumulado] + S.Monto
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         FROM (SELECT M.Monto, M.Fk_Estado_Cuenta
               FROM dbo.Movimientos_De_Interes M
               WHERE M.Fecha = @inFecha
               AND M.Fk_Tipo_Movimiento_Interes = @ID_TipoCredito) S

         WHERE ID = S.Fk_Estado_Cuenta
         AND @inFecha < Fecha_Corte
           
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Y por último se programó este procedimiento que crea cobros a los cuentas por el servicio si bajan de un cierto monto:

USE [II Proyecto - Banco]
GO

CREATE PROCEDURE BASP_CargosXServicio
   
    @inFecha date

AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_TipoCargosXServicio int
        DECLARE @TipoMov int
        SELECT @ID_TipoCargosXServicio = ID, @TipoMov = Tipo FROM dbo.Tipo_Movimiento_Estado_Cuenta WHERE Nombre = 'Cargos por Servicio'

        BEGIN TRANSACTION Transaccion

        --Creamos una entrada de multa
        INSERT INTO [dbo].[Movimientos_Estado_Cuenta]
                   ([Fk_Tipo_Movimiento_Estado_Cuenta]
                   ,[Fk_Estado_Cuenta]
                   ,[Fecha]
                   ,[Monto]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT @ID_TipoCargosXServicio, E.ID, @inFecha, T.Cargo_Servicio * @TipoMov, @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
             FROM dbo.Tipo_Cuenta T, dbo.Cuenta C, dbo.Estado_Cuenta E
             WHERE C.FK_Tipo_Cuenta = T.ID
             AND E.Fk_Cuenta = C.ID
             AND C.Fecha_Corte = DATEPART(DAY, @inFecha)
             AND T.Limite_Minimo_NoCobro < E.Saldo_Inicial + E.Saldo_Acumulado + E.Interes_Acumulado

       
        --Le sumamos la multa
        UPDATE [dbo].[Estado_Cuenta]
           SET [Saldo_Acumulado] = [Saldo_Acumulado] + S.Suma
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         FROM (SELECT SUM(M.Monto) Suma, M.Fk_Estado_Cuenta
               FROM dbo.Movimientos_Estado_Cuenta M
               WHERE M.Fk_Tipo_Movimiento_Estado_Cuenta IN (SELECT ID
                                                            FROM dbo.Tipo_Movimiento_Estado_Cuenta
                                                            WHERE Nombre = 'Cargos por Servicio')
               GROUP BY M.Fk_Estado_Cuenta) S

         WHERE ID = S.Fk_Estado_Cuenta
           
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO