--
===================================
-- Fecha: 2016/11/11
--
===================================
USE CSDaniel;
GO
-- Información Tarjetas
SELECT *
FROM Tarjeta;
-- Información de los
clientes y tarjetas
SELECT *
FROM vClienteTarjeta;
-- Información de las
Tarjetas y sus Saldos
SELECT *
FROM vTarjetaSaldo;
--
===================================
-- paActualizarSaldo
--
===================================
USE [CSDaniel]
GO
/****** Object:
StoredProcedure [dbo].[paActualizarSaldo] Script Date: 11/11/2016 12:54:26 p. m.
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Daniel
Santizo Saldaña
-- Create date: 2016-11-11
-- Description: Actualizar el saldo de una tarjeta y la fecha
-- =============================================
CREATE PROCEDURE [dbo].[paActualizarSaldo]
--
Add the parameters for the stored procedure here
@CodTarjeta int = 0
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT ON;
-- ACTUALIZAR EL SALDO de
la Tarjeta @CodTarjeta
DECLARE @Saldo Decimal;
-- RECUPERA EL SALDO
SELECT @Saldo = Saldo
FROM vTarjetaSaldo
WHERE CodTarjeta =
@CodTarjeta;
-- ACTUALIZA EN TARJETA
UPDATE Tarjeta
SET SaldoActual =
@Saldo, FechaSaldo =
GETDATE()
WHERE CodTarjeta=@CodTarjeta;
END
GO
-- Actualizar el Saldo de
la Tarjeta CodTarjeta = 1589
EXEC paActualizarSaldo 1589;
-- Consultar información
SELECT *
FROM Tarjeta
WHERE CodTarjeta=1589;
-- Información de Tarjetas
y sus Saldos
SELECT T.CodCliente,
v.CodTarjeta, v.CargoTotal, v.AbonoTotal,
v.Saldo
FROM vTarjetaSaldo v
INNER JOIN Tarjeta T
ON T.CodTarjeta = v.CodTarjeta
ORDER BY T.CodCliente, v.CodTarjeta;
-- VISTA vClienteSaldo
SELECT *
FROM vClienteSaldo
ORDER BY CodCliente, CodTarjeta;
-- UPDATE / SELECT
SELECT T.CodCliente, T.CodTarjeta,
T.FechaSaldo, T.SaldoActual,
vCS.CodCliente, vCS.CodTarjeta,
vCS.Saldo
FROM Tarjeta T
INNER
JOIN vClienteSaldo vCS
ON T.CodTarjeta = vCS.CodTarjeta
WHERE T.CodCliente = 2;
--
===================================
-- vClienteSaldo
--
===================================
USE [CSDaniel]
GO
/****** Object:
View [dbo].[vClienteSaldo]
Script Date: 11/11/2016 12:52:57 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vClienteSaldo]
AS
SELECT dbo.Tarjeta.CodCliente, dbo.vTarjetaSaldo.CodTarjeta,
dbo.vTarjetaSaldo.CargoTotal, dbo.vTarjetaSaldo.AbonoTotal,
dbo.vTarjetaSaldo.Saldo
FROM dbo.Tarjeta
INNER JOIN dbo.vTarjetaSaldo
ON dbo.Tarjeta.CodTarjeta = dbo.vTarjetaSaldo.CodTarjeta
GO
GRANT SELECT ON [dbo].[vClienteSaldo] TO
[ServicioCliente] AS [dbo]
GO
--
===================================
-- paActualizarCliente
--
===================================
USE [CSDaniel]
GO
/****** Object:
StoredProcedure [dbo].[paActualizarCliente] Script Date: 11/11/2016 12:54:32 p. m.
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Daniel
Santizo Saldaña
-- Create date: 2016-11-11
-- Description: Actualizar los Saldos de las Tarjetas de un
Cliente
-- =============================================
CREATE PROCEDURE [dbo].[paActualizarCliente]
--
Add the parameters for the stored procedure here
@CodCliente int = 0
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT ON;
--
Insert statements for procedure here
UPDATE
Tarjeta
SET FechaSaldo = GETDATE(),
SaldoActual
= vClienteSaldo.Saldo
FROM vClienteSaldo
WHERE Tarjeta.CodCliente
= @CodCliente
AND Tarjeta.CodTarjeta
= vClienteSaldo.CodTarjeta;
END
GO
-- Clientes y Tarjetas
SELECT C.CodCliente, C.Apellido, C.Nombre,
T.CodTarjeta, T.FechaSaldo, T.SaldoActual
FROM Cliente C
INNER
JOIN Tarjeta T
ON C.CodCliente = T.CodCliente;
-- Cliente = 1
EXEC paActualizarCliente 1;
-- Genera un script para
ejecutar la actualización de saldos de todos
-- los clientes en la
tabla.
SELECT 'EXEC paActualizarCliente ',
CodCliente,
';'
FROM Cliente
-- =============================================
-- SEGURIDAD
-- =============================================
USE [master]
GO
CREATE LOGIN [Servicio2]
WITH
PASSWORD=N'12345',
DEFAULT_DATABASE=[CSDaniel],
DEFAULT_LANGUAGE=[Español],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [CSDaniel]
GO
CREATE USER [Servicio2]
FOR
LOGIN [Servicio2]
GO
USE [CSDaniel]
GO
ALTER ROLE [ServicioCliente]
ADD MEMBER [Servicio2]
GO
