Creación de inserciones básicas.

Se han empezado a crear los Inserts básicos de datos para el mantenimiento en la base de datos y pronto se empezaran para hacer inserts desde la aplicación, algunos ejemplos son:

Insertar datos de deudores:

CREATE PROCEDURE [dbo].[ARSP_DebtorInsert]
-- Add the parameters for the stored procedure here
@id int,
@Name varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO [dbo].[Debtor]
           ([ID]
           ,[Name]
           ,[Deleted])
     VALUES
           (@id
           ,@Name
           ,0)
END

GO

Insertar datos de Loan:
CREATE PROCEDURE [dbo].[ARSP_LoanInsert]
-- Add the parameters for the stored procedure here
@id int,@originalamount money,@fee money,@loantype int,@deadline int,@debtor int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @remterm INT
SELECT @remterm = LT.TermMonths
FROM dbo.LoanType LT INNER JOIN dbo.Loan L on L.FK_LoanType = LT.ID
WHERE LT.ID = @loantype

    -- Insert statements for procedure here
INSERT INTO [dbo].[Loan]
           ([ID]
           ,[OriginalAmount]
           ,[Fee]
           ,[RemainingTerm]
           ,[NonAppliedBalance]
           ,[AppliedBalance]
           ,[MonthAcumInterest]
           ,[Deadline]
           ,[FK_LoanType]
           ,[FK_Debtor]
           ,[Deleted])
     VALUES
           (@id
           ,@originalamount
           ,@fee
           ,@remterm
           ,0
           ,@originalamount
           ,0
           ,@deadline
           ,@loantype
           ,@debtor
           ,0)
END

GO

El tiempo que se duro fue 15 minutos mas las modificaciones de los deleted.

Se han tenido problemas con los movimientos que se tienen que hacer día a día ya que a veces se ejecutan pero no se terminan al  100% pero se solucionara cuando agreguemos las transacciones a los SP.

Avanzando en el proyecto!

Avances

Se ha avanzado mucho en la parte de migración de datos del XML a la base de datos

se han trabajado 3 horas en la migración de datos, todas las inserciones se han hecho de forma masiva y estas a su vez se han optimizado

Ejemplo del Stored Procedure:

CREATE PROCEDURE [dbo].[ARSP_MigrateData]

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY

EXEC dbo.ARSP_ClearMovementDebug

IF(EXISTS(SELECT 1 FROM dbo.Loan))

DELETE FROM dbo.Loan;

IF(EXISTS(SELECT 1 FROM dbo.Debtor))

DELETE FROM dbo.Debtor;

IF(EXISTS(SELECT 1 FROM dbo.LoanType))

DELETE FROM dbo.LoanType;

IF(EXISTS(SELECT 1 FROM dbo.BillStatus))

DELETE FROM dbo.BillStatus;

DECLARE @Loan TABLE(ID INT IDENTITY(1,1),OriginalAmount MONEY,Fee MONEY,RemainingTerm INT,NABalance MONEY,ABalance MONEY,MonthAcumInterest FLOAT,Deadline INT,fkLoanType INT,fkDebtor int)

DECLARE @LoanType TABLE(ID INT,Name VARCHAR(50),InterestRate FLOAT,TermMonths INT)

DECLARE @Debtor TABLE(ID INT NOT NULL,Name VARCHAR(50),Code VARCHAR(50))

DECLARE @BillStatus TABLE(ID INT,Name VARCHAR(50))

DECLARE @XML XML

SELECT @XML = BulkColumn

FROM OPENROWSET(BULK 'D:\XMLBasesAR.xml', SINGLE_CLOB) AS x;

INSERT INTO @BillStatus(ID,Name)

SELECT    estado.value('@ID', 'VARCHAR(50)'),

estado.value('@Name', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./BillPaymentStatus') AS x2(estados)

cross apply x2.estados.nodes('./BillStatus') AS x3(estado)

 

INSERT INTO @LoanType(ID,Name,InterestRate,TermMonths)

SELECT tipo.value('@ID','VARCHAR(50)'),

tipo.value('@Name','VARCHAR(50)'),

tipo.value('@InterestRate','VARCHAR(50)'),

tipo.value('@TermMonths','VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./LoanTypes') AS x2(tipos)

cross apply x2.tipos.nodes('./LoanType') AS x3(tipo)

INSERT INTO @Debtor(ID,Name,Code)

SELECT      

deudor.value('@ID', 'VARCHAR(50)'),

deudor.value('@Name','VARCHAR(50)'),

deudor.value('@Cedula', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./Debtors') AS x2(deudores)

cross apply x2.deudores.nodes('./Debtor') AS x3(deudor)

select * from @Debtor

INSERT INTO @Loan(OriginalAmount,Fee,RemainingTerm,NABalance,ABalance,MonthAcumInterest,Deadline,fkLoanType,fkDebtor)

SELECT       

prestamo.value('@OriginalAmount', 'VARCHAR(50)'),

prestamo.value('@Fee', 'VARCHAR(50)'),

(SELECT  LT.TermMonths

 FROM @LoanType LT 

 WHERE LT.ID = prestamo.value('@IDTipPrestamo', 'VARCHAR(50)')),

0,prestamo.value('@OriginalAmount', 'VARCHAR(50)'),

0,prestamo.value('@Deadline', 'VARCHAR(50)'),

prestamo.value('@IDTipPrestamo', 'VARCHAR(50)'),

prestamo.value('@IDPersona', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./Loans') AS x2(prestamos)

cross apply x2.prestamos.nodes('./Loan') AS x3(prestamo)

SELECT * FROM @Loan

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

INSERT INTO dbo.BillStatus(ID,Name) 

SELECT BS.ID, BS.Name 

FROM @BillStatus BS

INSERT INTO dbo.LoanType(ID,Name,InterestRate,TermMonths)

SELECT LT.ID, LT.Name, LT.InterestRate, LT.TermMonths

FROM @LoanType LT

INSERT INTO dbo.Debtor(ID,Name,Deleted,Code)

SELECT D.ID,D.Name,0,D.Code

FROM @Debtor D

INSERT INTO dbo.Loan(ID,OriginalAmount,Fee,RemainingTerm,NonAppliedBalance,AppliedBalance,MonthAcumInterest,Deadline,FK_LoanType,FK_Debtor,Deleted)

SELECT L.ID,L.OriginalAmount,L.Fee,L.RemainingTerm,L.NABalance,L.ABalance,L.MonthAcumInterest,L.Deadline,L.fkLoanType,L.fkDebtor,0

FROM @Loan L

COMMIT

RETURN 1

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 

ROLLBACK

PRINT 'Something went wrong!'

RETURN -1 * @@ERROR

END CATCH

END

GO

A su vez se ha creado un Stored Procedure para poder estar limpiando la base de datos ya que los compañeros que hacen el archivo XML por lo general lo hacen con errores y cambios constantes y se tiene que estar borrando los datos en algunas tablas, en esta parte de la base de datos se ha durado 30 minutos

CREATE PROCEDURE [dbo].[ARSP_ClearMovementDebug] 

-- 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;

DELETE FROM dbo.AppliedBalanceMovement

DELETE FROM dbo.NonAppliedBalanceMovement

DELETE FROM dbo.DailyInterestMovement

END

GO

Primeras versiones de los SP

El equipo ha empezado hacer los Stored Procedure de migración de datos, el equipo empezó a crear el xml para adelantar.
Documento XML version 1: Ver archivo xml

Stored Procedure de migración datos:

CREATE PROCEDURE [dbo].[ARSP_MigrateData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
EXEC dbo.ARSP_ClearMovementDebug

IF(EXISTS(SELECT 1 FROM dbo.Debtor))
DELETE FROM dbo.Debtor;
IF(EXISTS(SELECT 1 FROM dbo.Loan))
DELETE FROM dbo.Loan;
IF(EXISTS(SELECT 1 FROM dbo.LoanType))
DELETE FROM dbo.LoanType;
IF(EXISTS(SELECT 1 FROM dbo.BillStatus))
DELETE FROM dbo.BillStatus;

DECLARE @Loan TABLE(ID INT,OriginalAmount MONEY,Fee MONEY,RemainingTerm INT,NABalance MONEY,ABalance MONEY,MonthAcumInterest FLOAT,Deadline INT,fkLoanType INT,fkDebtor int)
DECLARE @LoanType TABLE(ID INT,Name VARCHAR(50),InterestRate FLOAT,TermMonths INT)
DECLARE @Debtor TABLE(ID INT,Name VARCHAR(50))
DECLARE @BillStatus TABLE(ID INT,Name VARCHAR(50))

DECLARE @XML XML
SELECT @XML = BulkColumn
FROM OPENROWSET(BULK 'D:\XMLBasesAR.xml', SINGLE_CLOB) AS x;

INSERT INTO @BillStatus(ID,Name)
SELECT    estado.value('@ID', 'VARCHAR(50)'),
estado.value('@Name', 'VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./BillStatus') AS x2(estado)

INSERT INTO @LoanType(ID,Name,InterestRate,TermMonths)
SELECT tipo.value('@ID','VARCHAR(50)'),
tipo.value('@Name','VARCHAR(50)'),
tipo.value('@InterestRate','VARCHAR(50)'),
tipo.value('@TermMonths','VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./LoanType') AS x2(tipo)

INSERT INTO @Loan(ID,OriginalAmount,Fee,RemainingTerm,NABalance,ABalance,MonthAcumInterest,Deadline,fkLoanType,fkDebtor)
SELECT       prestamo.value('@ID', 'VARCHAR(50)'),
prestamo.value('@OriginalAmount', 'VARCHAR(50)'),
prestamo.value('@Fee', 'VARCHAR(50)'),
prestamo.value('@RemainingTerm', 'VARCHAR(50)'),
prestamo.value('@NABalance', 'VARCHAR(50)'),
prestamo.value('@ABalance', 'VARCHAR(50)'),
prestamo.value('@MonthAcumInterest', 'VARCHAR(50)'),
prestamo.value('@Deadline', 'VARCHAR(50)'),
prestamo.value('@LoanType', 'VARCHAR(50)'),
prestamo.value('@Debtor', 'VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./Loan') AS x2(prestamo)

INSERT INTO @Debtor(ID,Name)
SELECT    
deudor.value('@ID', 'VARCHAR(50)'),
deudor.value('@Name','VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./Debtor') AS x2(deudor)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
INSERT INTO dbo.BillStatus(ID,Name)
SELECT BS.ID, BS.Name
FROM @BillStatus BS

INSERT INTO dbo.LoanType(ID,Name,InterestRate,TermMonths)
SELECT LT.ID, LT.Name, LT.InterestRate, LT.TermMonths
FROM @LoanType LT

INSERT INTO dbo.Loan(ID,OriginalAmount,Fee,RemainingTerm,NonAppliedBalance,AppliedBalance,MonthAcumInterest,Deadline,FK_LoanType,FK_Debtor)
SELECT L.ID,L.OriginalAmount,L.Fee,L.RemainingTerm,L.NABalance,L.ABalance,L.MonthAcumInterest,L.Deadline,L.fkLoanType,L.fkDebtor
FROM @Loan L

INSERT INTO dbo.Debtor(ID,Name)
SELECT D.ID,D.Name
FROM @Debtor D
COMMIT

RETURN 1
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
PRINT 'Something went wrong!'
RETURN -1 * @@ERROR
END CATCH
END

GO

Stored Procedure para borrar todos los datos de las tablas:

CREATE PROCEDURE [dbo].[ARSP_ClearMovementDebug]
-- 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;

DELETE FROM dbo.AppliedBalanceMovement
DELETE FROM dbo.NonAppliedBalanceMovement
DELETE FROM dbo.DailyInterestMovement

END
GO

Empezamos apuntando a lo mejor.

Esta es la especificación del proyecto:

Objetivos:

A partir de la descripción de un problema y un modelo conceptual, construir la  base de datos física y el código en SQL para el mantenimiento de entidades y para el procesamiento masivo y no masivo, que requiere el problema de marras.

Definición del proyecto:

Un deudor puede tener varios préstamos, un préstamos se asocia con un tipo de préstamo el cual determina la tasa de interés anual, y el plazo en meses, ejemplos de tipo de préstamo: vivienda, personal, prendario, etc.

El préstamo tiene atributos como Monto Origina, Cuota, Saldo Real, Saldo según recibos (no aplicado), Interés Acumulado Mensual, Plazo que falta, día de corte (o día en que se generan recibos), y día de pago.

Características que deben pasar diariamente en el sistema: 

+ Calcula intereses: toma el saldo según recibos (no aplicado) * tasa de interés / 360. Genera un movimiento de intereses y lo acumula en Interés acumulado mensual.

+ Si la fecha corresponde al día de corte de un préstamo, genera un movimiento no aplicado al saldo por el monto de Cuota menos interés acumulado del mes (que es el monto que se amortiza) y se genera un movimiento de intereses que lleva el monto de interés acumulado del mes a cero. Y se genera un recibo asociado a los movimientos de interés y movimiento no aplicado al saldo, que contiene fecha para pago, el monto a pagar y el texto descriptivo del pago.

Un cliente visita el banco o visita la página del banco para pagar un recibo, se realiza una consulta de lista los recibos pendientes de pago, el cual indica, la fecha del recibo, la fecha para el pago, cuota, intereses del mes y amortización. Se lista en orden ascendente de la fecha del recibo, el cliente o el cajero del banco, selecciona un recibo para pagar, que debe ser el recibo más viejo, y lo paga usando algún medio de pago: efectivo, cheque o débito a cuenta corriente o de ahorro.

Al pagarse un recibo, se crea un movimiento al saldo real, con lo datos del movimiento no aplicado, se actualiza el saldo real, y el recibo cambia de estatus, de pendiente a pagado.

Que se pide en el proyecto programado:

+ Scripts de llenado de datos básicos
+ Scripts de llenado de datos para pruebas
+ Scripts para la creación de los store procedure, de todos los mantenimientos, del proceso diario que debe ser masivo,  del proceso de pagar recibos y el de consulta de recibos.
+ La misma documentación de la primera tarea.

Modelo conceptual el cual se definió en clases: 

Hoy se empezó con el proyecto, esta vez nos dimos a la tarea de diseñando nuestro xml por primera vez para no atrasar el proyecto y terminarlo lo mas antes posible ya que para el proyecto anterior se nos complico avanzar por dicho archivo que nuestros compañeros no hicieron rápidamente.

Primer modelo de bases de datos: