CASO CADENA DE CINES - SQL Server 2014

Una cadena de cines ha optado por adquirir una aplicación informática que permita controlar la programación de películas, así como los ingresos en soles en un determinado período. Es así que el dueño de dicha cadena se ha enterado que usted es un alumno de Ingeniería de Sistemas e Informática y le solicita que prepare el soporte en base de datos para tal fin. El trabajo consistirá en desarrollar la base de datos y secuencias de comandos, teniendo como soporte el manejador de base de datos SQL Server (en cualquiera de sus versiones) que permita controlar la información necesaria en el momento oportuno acerca del proceso integral: 

1. Archivo con el script para la creacion de la base de datos, tablas, relaciones y llaves foraneas. Se debe tomar en cuenta la verificacion de la existencia de dichos objetos con sentencias condicionales, asi como la presentacion del diccionario de datos correspondiente.
2. Archivo con el script de soporte para la funcionalidad del modulo: programacion de peliculas. Aqui debera crear diferentes rutinas que permita registrar las peliculas que seran programas durante la(s) semana(s) que el usuario considere necesario. Ademas, debe registrarse datos basicos que permita al espectador ver de que trata la pelicula.
3. Archivo con el script de soporte para la funcionalidad del modulo: venta de entradas. Es aqui donde se debe crear las rutinas para el registro del ticket indicando los datos basicos necesarios que figuran en un boleto de cualquier cine tradicional.
4. Archivo con el script de soporte para la funcionalidad del modulo: promociones. Es un complemento al modulo de programacion de peliculas ya que aqui se permitira registrar los dias en que habra promociones como el dia del cine, 3x2, vales de inkafarma, etc.
5. Archivo con el script de las consultas y estadisticas en general, por rangos de fechas, aplicacion de filtros, etc. Se evaluara la aplicacion de cursores, consultas de union, uso de vistas y subconsultas.
6. Archivo script con todos los disparadores utilizados en la aplicacion. Puede incluir cursores y codigo en Transact SQL.

DESARROLLO DEL CASO

     1. Presentando el Diagrama de Base de Datos BASECINE

Ejecutamos el siguiente script para la creacion de la base de datos y las tablas correspondientes al diseño.

-Creamos la base de datos y la usamos.

use master

go

create database BASECINE

go

use BASECINE

go

-Creamos la tabla Estado

CREATE TABLE ESTADO

(

IDESTADO INT NOT NULL IDENTITY(1,1),

DESCRIPCION VARCHAR(50) NOT NULL,

CONSTRAINT PK_ESTADO PRIMARY KEY (IDESTADO)

);

GO

-Creamos la tabla Sala

CREATE TABLE SALA

(

IDSALA INT NOT NULL IDENTITY(1,1),

DESCRIPCION VARCHAR(50) NOT NULL,

ASIENTOS INT NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_SALA PRIMARY KEY (IDSALA),

CONSTRAINT FK_SALA_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

-Creamos la tabla Pelicula

CREATE TABLE PELICULA

(

IDPELICULA INT NOT NULL IDENTITY(1,1),

TITULO VARCHAR(50) NOT NULL,

DURACION TIME NOT NULL,

GENERO VARCHAR(50) NOT NULL,

CLASIFICACION VARCHAR(50)NOT NULL,

FORMATO VARCHAR(40) NOT NULL,

PORTADA IMAGE NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_PELICULA PRIMARY KEY (IDPELICULA),

CONSTRAINT FK_PELICULA_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

-Creamos la tabla Asiento

CREATE TABLE ASIENTO

(

IDASIENTO INT NOT NULL IDENTITY (1,1),

NROASIENTO INT NOT NULL,

IDSALA INT NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_ASIENTO PRIMARY KEY (IDASIENTO),

CONSTRAINT FK_ASIENTO_SALA FOREIGN KEY (IDSALA) REFERENCES SALA(IDSALA),

CONSTRAINT FK_ASIENTO_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO),

);

GO

-Creamos la tabla Cliente

CREATE TABLE CLIENTE

(

IDCLIENTE INT NOT NULL IDENTITY (1,1),

NOMBRE VARCHAR(50) NOT NULL,

APELLIDO VARCHAR(50) NOT NULL,

DNI CHAR(8) NULL,

CONSTRAINT PK_CLIENTE PRIMARY KEY (IDCLIENTE)

);

GO

-Creamos la tabla Cartelera

CREATE TABLE CARTELERA

(

IDCARTELERA INT NOT NULL IDENTITY (1,1),

IDPELICULA INT NOT NULL,

IDSALA INT NOT NULL,

HORAINICIO TIME NOT NULL,

HORAFIN TIME NOT NULL,

FECHA DATE NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_CARTELERA PRIMARY KEY (IDCARTELERA),

CONSTRAINT FK_CARTELERA_PELICULA FOREIGN KEY (IDPELICULA) REFERENCES PELICULA(IDPELICULA),

CONSTRAINT FK_CARTELERA_SALA FOREIGN KEY (IDSALA) REFERENCES SALA(IDSALA),

CONSTRAINT FK_CARTELERA_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

-Creamos la tabla Registro de Asiento

CREATE TABLE REGISTROASIENTO

(

IDRASIENTO INT NOT NULL IDENTITY(1,1),

IDCARTELERA INT NOT NULL,

NROASIENTO INT NOT NULL,

CONSTRAINT PK_RASIENTO PRIMARY KEY (IDRASIENTO),

CONSTRAINT FK_RASIENTO_CARTELERA FOREIGN KEY (IDCARTELERA) REFERENCES CARTELERA(IDCARTELERA),

);

GO

-Creamos la tabla Asiento Cliente

CREATE TABLE ASIENTOCLIENTE

(

IDACLIENTE INT NOT NULL IDENTITY (1,1),

IDRASIENTO INT NOT NULL,

IDCLIENTE INT NOT NULL,

CONSTRAINT PK_ACLIENTE PRIMARY KEY (IDACLIENTE),

CONSTRAINT FK_ACLIENTE_RCLIENTE FOREIGN KEY (IDRASIENTO) REFERENCES REGISTROASIENTO(IDRASIENTO),

CONSTRAINT FK_ACLIENTE_CLIENTE FOREIGN KEY (IDCLIENTE) REFERENCES CLIENTE(IDCLIENTE)

);

GO

-Creamos la tabla Empleado

CREATE TABLE EMPLEADO

(

IDEMPLEADO INT NOT NULL IDENTITY (1,1),

NOMBRE VARCHAR(50) NOT NULL,

APELLIDO VARCHAR(50) NOT NULL,

FECHACONTRAT DATE NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_EMPLEADO PRIMARY KEY (IDEMPLEADO),

CONSTRAINT FK_EMPLEADO_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

-Creamos la tabla Ticket

CREATE TABLE TICKET

(

IDTICKET INT NOT NULL IDENTITY (1,1),

TIPO VARCHAR(80)NOT NULL,

DESCRIPCION VARCHAR(200) NOT NULL,

PRECIO DECIMAL(10,2) NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_TICKET PRIMARY KEY (IDTICKET),

CONSTRAINT FK_TICKET_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

-Creamos la tabla Venta

CREATE TABLE VENTA

(

IDVENTA INT NOT NULL IDENTITY (1,1),

IDCLIENTE INT NOT NULL,

IDCARTELERA INT NOT NULL,

IDEMPLEADO INT NOT NULL,

FECHA DATE NOT NULL,

ASIENTOS VARCHAR(20) NOT NULL,

TPAGO VARCHAR(50) NOT NULL,

PROMOCION VARCHAR(80) NOT NULL,

DESCUENTO DECIMAL(10,2) NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_VENTA PRIMARY KEY (IDVENTA),

CONSTRAINT FK_VENTA_CLIENTE FOREIGN KEY (IDCLIENTE) REFERENCES CLIENTE(IDCLIENTE),

CONSTRAINT FK_VENTA_CARTELERA FOREIGN KEY (IDCARTELERA) REFERENCES CARTELERA(IDCARTELERA),

CONSTRAINT FK_VENTA_EMPLEADO FOREIGN KEY (IDEMPLEADO) REFERENCES EMPLEADO(IDEMPLEADO),

CONSTRAINT FK_VENTA_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

-Creamos la tabla DetalleVenta

CREATE TABLE DETALLEVENTA

(

IDDVENTA INT NOT NULL IDENTITY(1,1),

IDVENTA INT NOT NULL,

DESCRIPCION VARCHAR(100) NOT NULL,

CANTIDAD INT NOT NULL,

PRECIO DECIMAL(10,2) NOT NULL,

CONSTRAINT PK_DVENTA PRIMARY KEY (IDDVENTA),

CONSTRAINT FK_DVENTA_VENTA FOREIGN KEY (IDVENTA) REFERENCES VENTA(IDVENTA)

);

GO

-Creamos la tabla Promocion

CREATE TABLE PROMOCION

(

IDPROMOCION INT NOT NULL IDENTITY(1,1),

NOMBRE VARCHAR(80) NOT NULL,

DESCUENTO DECIMAL (10,2) NOT NULL,

BASETICKET BIT NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_PROMOCION PRIMARY KEY (IDPROMOCION),

CONSTRAINT FK_PROMOCION_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO),

);

GO

-Creamos la tabla ProgPromocion

CREATE TABLE PROGRPROMOCION

(

IDPPROMOCION INT NOT NULL IDENTITY(1,1),

FECHA DATE NOT NULL,

PROMOCION VARCHAR(80) NOT NULL,

IDESTADO INT NOT NULL,

CONSTRAINT PK_PPROMOCION PRIMARY KEY (IDPPROMOCION),

CONSTRAINT FK_PPROMOCION_ESTADO FOREIGN KEY (IDESTADO) REFERENCES ESTADO(IDESTADO)

);

GO

INSERCIONES EN LAS TABLAS

-Insert en la tabla Estado

INSERT INTO ESTADO VALUES ('ACTIVO');
INSERT INTO ESTADO VALUES ('INACTIVO');
INSERT INTO ESTADO VALUES ('OCUPADO');
INSERT INTO ESTADO VALUES ('LIBRE');
INSERT INTO ESTADO VALUES ('REALIZADA');

INSERT INTO ESTADO VALUES ('CANCELADA');

-Insert en la tabla Sala

INSERT INTO SALA VALUES ('SALA 1',14,4);

INSERT INTO SALA VALUES ('SALA 2',14,4);

-Insert en la tabla Pelicula

INSERT INTO PELICULA VALUES ('THE MOVIE 1','01:30:00','ACCION','APTO PARA TODOS','3D',null,1);

-Insert en la tabla Asientos

  SALA1

INSERT INTO ASIENTO VALUES (1,1,1);
INSERT INTO ASIENTO VALUES (2,1,1);
INSERT INTO ASIENTO VALUES (3,1,1);
INSERT INTO ASIENTO VALUES (4,1,1);
INSERT INTO ASIENTO VALUES (5,1,1);
INSERT INTO ASIENTO VALUES (6,1,1);
INSERT INTO ASIENTO VALUES (7,1,1);
INSERT INTO ASIENTO VALUES (8,1,1);
INSERT INTO ASIENTO VALUES (9,1,1);
INSERT INTO ASIENTO VALUES (10,1,1);
INSERT INTO ASIENTO VALUES (11,1,1);
INSERT INTO ASIENTO VALUES (12,1,1);
INSERT INTO ASIENTO VALUES (13,1,1);

INSERT INTO ASIENTO VALUES (14,1,1);

  SALA 2

INSERT INTO ASIENTO VALUES (1,2,1);
INSERT INTO ASIENTO VALUES (2,2,1);
INSERT INTO ASIENTO VALUES (3,2,1);
INSERT INTO ASIENTO VALUES (4,2,1);
INSERT INTO ASIENTO VALUES (5,2,1);
INSERT INTO ASIENTO VALUES (6,2,1);
INSERT INTO ASIENTO VALUES (7,2,1);
INSERT INTO ASIENTO VALUES (8,2,1);
INSERT INTO ASIENTO VALUES (9,2,1);
INSERT INTO ASIENTO VALUES (10,2,1);
INSERT INTO ASIENTO VALUES (11,2,1);
INSERT INTO ASIENTO VALUES (12,2,1);
INSERT INTO ASIENTO VALUES (13,2,1);
INSERT INTO ASIENTO VALUES (14,2,1);

-Insert en la tabla Cliente

INSERT INTO CLIENTE VALUES ('JUAN','PEREZ','88888888');

-Insert en la tabla Cartelera

INSERT INTO CARTELERA VALUES (1,1,'18:00:00','19:30:00',CURRENT_TIMESTAMP,1);

-Insert en la tabla Empleado

INSERT INTO EMPLEADO VALUES ('JOSE','HERNANDEZ',CURRENT_TIMESTAMP,1);

-Insert en la tabla Tickets

INSERT INTO TICKET VALUES ('ENTRADA GENERAL','ENTRADA PARA LAS PERSONAS MAYORES DE 18 ANIOS',10,1);

INSERT INTO TICKET VALUES ('ENTRADA NINOS','ENTRADA PARA LOS NINOS',5,1);

-Insert en la tabla Promocion

INSERT INTO PROMOCION VALUES ('PROMOCION 3X2',1.0,1,1);

INSERT INTO PROMOCION VALUES ('DESCUENTO 30%',0.3,0,1);

-Insert en la tabla ProgPromocion

INSERT INTO PROGRPROMOCION VALUES (CURRENT_TIMESTAMP,'DESCUENTO 30%',1,2);

INSERT INTO PROGRPROMOCION VALUES (CURRENT_TIMESTAMP,'PROMOCION 3x2',1,1);

2. PROGRAMACION DE PELICULAS

CREATE PROCEDURE SP_PROGRAMAR_PELICULA
(
@IDPELICULA INT,
@DURACION INT,
@IDSALA INT,
@HORAINICIO TIME,
@HORAFIN TIME
)

AS
DECLARE @ULTIMODIA INT,
@UDIA DATE,
@DIAPROGRAMA DATE,
@CONTADOR INT=1
BEGIN

SET @ULTIMODIA=(SELECT 7-( DATEDIFF(day,0,GETDATE())%7+1));
SET @UDIA=(SELECT DATEADD(day,@ULTIMODIA,GETDATE()))
SET @DIAPROGRAMA=GETDATE();

WHILE (@CONTADOR<=@DURACION)
BEGIN  
  WHILE (@DIAPROGRAMA<=@UDIA)
BEGIN  
INSERT INTO CARTELERA VALUES (@IDPELICULA,@IDSALA,@HORAINICIO,@HORAFIN,@DIAPROGRAMA,1);
SET @DIAPROGRAMA=(SELECT DATEADD(day,1,@DIAPROGRAMA));
    END
SET @UDIA=(SELECT DATEADD(day,7,@UDIA));
  SET @CONTADOR=@CONTADOR+1; 
END  

END
GO

EXPLICANDO EL PROCEDIMIENTO ALMACENADO
Para programar las peliculas durante la semana hemos creado este procedimiento almacenado que tendra como parametros el id de la pelicula a programar, la duracion que seria la cantidad de semanas que queremos programar, el id de la sala donde queremos programar la funcion, la hora de inicio y la hora final de la funcion.

Luego declaramos algunas variables para obtener la diferencia de dias entre el dia actual y el fin de semana, luego declaramos una variable que obtendra el dia actual, y un contador de dias.

Se crea un primer bucle while que va recorrer de acuerdo a las semanas que hemos ingresado. el segundo bucle while evalua que las fecha de hoy llegue hasta el fin de semana para ir realizando las programaciones en caso de que sea una sola semana, si fueran mas de dos semanas al terminar el segundo bucle se establecen valores para las variables UDIA con una suma de 7 dias que representaran la segunda semana, y el contador de semanas que va de uno en uno. 

De esta forma dentro del segundo bucle while se van ingresando las programaciones.

Finalmente para probar el procedimiento almacenado lo Ejecutamos indicando los valores que indica en sus parametros.

EXEC SP_PROGRAMAR_PELICULA 1,2,1,'20:00:00','22:00:00';

Programamos la pelicula con id =1, duracion 2 semanas, id de la sala =1, Hora de inicio : '20:00:00', Hora final : '22:00:00'

3. VENTA DE ENTRADAS

CREATE PROCEDURE [dbo].[SP_VENTA]
(
@IDCLIENTE INT,
@IDCARTELERA INT,
@IDEMPLEADO INT,
@TPAGO VARCHAR(50),
@XMLDOC TEXT
)

AS
DECLARE @DOC INT,
@IDVENTA INT,
@IDRASIENTO INT,
@DESCT DECIMAL(19,2)
BEGIN

BEGIN TRY
    BEGIN TRANSACTION 

EXEC sp_xml_preparedocument @DOC OUTPUT, @XMLDOC;

DECLARE @promoTEMP TABLE
(
id int identity(1,1),
descuento decimal(10,2) DEFAULT 0,
baseticket bit DEFAULT 0
);

IF (SELECT COUNT(IDPPROMOCION) from PROGRPROMOCION where CONVERT(VARCHAR,FECHA,111)=CONVERT(VARCHAR,GETDATE(),111))>0
INSERT INTO @promoTEMP SELECT p.DESCUENTO,p.BASETICKET 
from PROGRPROMOCION pg inner join PROMOCION p 
on p.IDPROMOCION=pg.IDPROMOCION WHERE pg.FECHA=CONVERT(VARCHAR,GETDATE(),111);

SET @DESCT =(SELECT  descuento from @promoTEMP);
INSERT INTO VENTA 
SELECT @IDCLIENTE,@IDCARTELERA,@IDEMPLEADO,GETDATE(),ASIENTOS,@TPAGO,PROMOCION,@DESCT,1 FROM OPENXML(@DOC,N'/ROOT/VENTA') 
WITH (ASIENTOS VARCHAR(20),TPAGO VARCHAR(50),PROMOCION VARCHAR(80)) 
SELECT @IDVENTA=@@IDENTITY;

IF @@ERROR<>0
ROLLBACK
ELSE

INSERT INTO DETALLEVENTA 
SELECT @IDVENTA,DESCRIPCION,CANTIDAD,PRECIO FROM OPENXML(@DOC,N'/ROOT/VENTA/ITEM')
WITH (DESCRIPCION VARCHAR(100),CANTIDAD INT,PRECIO DECIMAL(10,2));

IF @@ERROR<>0
ROLLBACK
ELSE

INSERT INTO REGISTROASIENTO 
SELECT @IDVENTA,@IDCARTELERA,NROASIENTO FROM OPENXML(@DOC,N'/ROOT/ASIENTO')
WITH ( NROASIENTO INT);

    COMMIT
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK
END CATCH

END

EXPLICANDO EL PROCEDIMIENTO
Para realizar las ventas de los tickets hemos creado un procedimiento almacenado que recibira como parametros el id del cliente, el id de la cartelera, el id del empleado, el tipo de pago en cadena, y una entrada de texto donde ingresaremos codigo xml que generara la aplicacion para poder ingresar la venta y detalle de la venta.

Creamos un conjunto de operaciones dentro de una transaccion: ejecutamos un procedimiento almacenado llamado sp_xml_preparedocument que segun la documentacion oficial de sql server nos comenta que este nos devuelve un indicador que se puede usar para acceder a la representación interna recién creada del documento XML. Este identificador es válido para la duración de la sesión o hasta que se invalide el identificador al ejecutar sp_xml_removedocument.
REFERENCIA:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql?view=sql-server-2017

El indicador se almacenara en @DOC, despues de ello creamos una tabla temporal llamada @promoTEMP que tendra los campos id autoincrementable, descuento y baseticket.
Creamos un condicional y verificamos si existe alguna oferta en el dia, entonces se almacenan algunos datos en la tabla temporal que tengan que ver con la promocion del dia.
Se asigna el descuento agregado de la tabla temporal hacia una variable.

Luego hacemos un insert a la tabla venta usando la instruccion OPENXML para crear una vista de conjunto de filas de un documento XML (segun la documentacion oficial de sql server). Dentro de la instruccion OPENXML colocamos como parametros el indicador que obtuvimos del procedimiento almacenado y los nodos que queremos identificar. Indicamos que tipo de dato tendras los valores que se obtendran y se insertaran en la tabla venta.

Una vez que se realice la operacion obtenemos el id ingresado con @@IDENTITY y lo asignamos a una variable @IDVENTA.
Si todo se ha realizado correctamente continuamos con las siguientes instrucciones, caso contrario se realizara un rolback de los cambios hechos para que no se haga transacciones incompletas.


Ahora insertaremos el detalle de venta es el mismo procedimiento solo que otra tabla y otros campos. En el detalle ingresamos la descripcion, la cantidad y el precio. De la misma forma si ocurre algun error se ejecuta el rollback.
Ahora ingresamos en una tabla de registro de asientos que almacena el id de la cartelera, el id del asiento, el nro de asiento, de tal forma que si se tienen varios asientos se ingresara los registros a esta tabla. En conjunto tambien se tendra disponible un disparador que se encargara de asignar los asientos y el cliente que lo ocupara.

Si hasta ese punto se ha realizado todo correctamente se aplican los cambios realizados mediante el COMMIT.

Para ejecutar el procedimiento almacenado indicamos todos los valores pero en el campo TEXT donde se insertara el texto XML tendra la siguiente estructura:


<ROOT><ASIENTO NROASIENTO="9"></ASIENTO><ASIENTO NROASIENTO="10"></ASIENTO><VENTA ASIENTOS="N 9, N 10" PROMOCION="NINGUNA"><ITEM DESCRIPCION="ENTRADA GENERAL" CANTIDAD="2" PRECIO="10" /></VENTA></ROOT>

4. MODULO PROMOCIONES

CREATE PROCEDURE SP_PROGRAMAR_PROMO

(

@DURACION INT,

@NOMPROMOCION VARCHAR(80),

@IDPROMOCION INT

)

AS

DECLARE @ULTIMODIA INT,

@UDIA DATE,

@DIAPROGRAMA DATE,

@CONTADOR INT=1

BEGIN


SET @ULTIMODIA=(SELECT 7-( DATEDIFF(day,0,GETDATE())%7+1));

SET @UDIA=(SELECT DATEADD(day,@ULTIMODIA,GETDATE()))

SET @DIAPROGRAMA=GETDATE();


WHILE (@CONTADOR<=@DURACION)

BEGIN

WHILE (@DIAPROGRAMA<=@UDIA)

BEGIN

INSERT INTO PROGRPROMOCION VALUES (GETDATE(),@NOMPROMOCION,@IDPROMOCION,1);

SET @DIAPROGRAMA=(SELECT DATEADD(day,1,@DIAPROGRAMA));

END

SET @UDIA=(SELECT DATEADD(day,7,@UDIA));

SET @CONTADOR=@CONTADOR+1;

END
END;

EXPLICANDO EL PROCEDIMIENTO
Hemos creado un procedimiento almacenado para crear las programaciones de las promociones y tendra como parametros la duracion que se dara en semanas, el nombre de la promocion y un id de promocion.
Igual como el procedimiento de programacion de peliculas funciona de la misma forma solo que esta vez los datos se insertaran en la tabla PROGPROMOCION.

5. REPORTES Y ESTADISTICAS

PROCEDIMIENTO PARA VER LA CARTELERA ENTRE DOS FECHAS ESPECIFICAS

CREATE PROCEDURE SP_PROGRAMACION_XFECHA

(@FECHAINI DATE,@FECHAFIN DATE)

AS

BEGIN

SELECT c.IDCARTELERA,p.TITULO,s.DESCRIPCION,c.HORAINICIO,c.HORAFIN,c.FECHA,e.DESCRIPCION as 'estado' from CARTELERA c inner join PELICULA p on p.IDPELICULA=c.IDPELICULA

inner join sala s on s.IDSALA=c.IDSALA inner join ESTADO e on e.IDESTADO=c.IDESTADO

where c.FECHA Between @FECHAINI and @FECHAFIN;

END;

EXPLICANDO EL PROCEDIMIENTO
Creamos este procedimiento para mostrar la cartelera entre dos fechas especificas, teniendo como parametros la fecha inicial y la fecha final.
Luego se realiza una seleccion a la tabla cartelera y uniendolas mediante un inner join con pelicula y salas para acceder a mas datos y usar la instruccion between para filtrar por las dos fechas.


EXEC SP_PROGRAMACION_XFECHA '2018-06-23','2018-06-29';


PROCEDIMIENTO ALMACENADO VENTAS POR MESES

CREATE PROCEDURE SP_REPORTE_MES

AS

BEGIN

SELECT COUNT (*) AS contador,

YEAR(fecha) AS anio,

MONTH(fecha) AS mes

FROM VENTA

GROUP BY YEAR(fecha), MONTH(fecha)

ORDER BY YEAR(fecha), MONTH(fecha) ASC;

END;

EXPLICANDO EL PROCEDIMIENTO
Creamos este procedimiento para poder mostrar las ventas por meses y años seleccionando los datos de la tabla venta y agrupandola por el campo fecha usando las funciones year y month.

EXEC SP_REPORTE_MES;


PROCEDIMIENTO ALMACENADO REPORTE DE VENTA POR CLIENTE

CREATE PROCEDURE SP_REPORTE_VCLIENTE

(@CODCLIENTE INT)

AS

BEGIN

SELECT V.IDVENTA,V.FECHA,C.NOMBRE+' '+C.APELLIDO AS CLIENTE,R.HORAINICIO,R.HORAFIN,P.TITULO AS PELICULA,D.NOMBRE+' '+D.APELLIDO as 'empleado',v.FECHA,v.ASIENTOS,v.TPAGO,v.PROMOCION,v.DESCUENTO,E.DESCRIPCION FROM VENTA V

INNER JOIN CARTELERA R

ON R.IDCARTELERA = V.IDCARTELERA

INNER JOIN ESTADO E

ON E.IDESTADO=R.IDESTADO

INNER JOIN CLIENTE C

ON C.IDCLIENTE=V.IDCLIENTE

INNER JOIN PELICULA P ON

P.IDPELICULA = R.IDPELICULA

INNER JOIN EMPLEADO D ON

D.IDEMPLEADO=V.IDEMPLEADO

WHERE V.IDCLIENTE=@CODCLIENTE;

END;

EXPLICANDO EL PROCEDIMIENTO
Creamos este procedimiento para mostrar los boletos que dicho cliente compro, para ello ingresado el codigo del cliente y mediante la instruccion select seleccionamos la tabla venta y usamos el inner join con la tabla cartelera , pelicula, empleado, estado y cliente.

EXEC SP_REPORTE_VCLIENTE 1

PROCEDIMIENTO ALMACENADO REPORTE DE CARTELERA

CREATE PROCEDURE SP_REPORTE_CARTELERA

(@DURACION INT)

AS

DECLARE @SEMANAS INT=@DURACION*7;

DECLARE @SPELICULA DATE=GETDATE()+@SEMANAS;

BEGIN

SELECT F.IDCARTELERA,F.FECHA,F.HORAINICIO,F.HORAFIN,P.TITULO,S.DESCRIPCION

FROM CARTELERA F

INNER JOIN PELICULA P ON

P.IDPELICULA=F.IDPELICULA

INNER JOIN SALA S ON

S.IDSALA=F.IDSALA

WHERE F.FECHA BETWEEN GETDATE() AND @SPELICULA;

END;

EXPLICANDO EL PROCEDIMIENTO
Creamos este procedimiento para mostrar la programacion de la cartelera por semanas por ello debemos ingresar la duracion en semanas.

EXEC SP_REPORTE_CARTELERA 1;

6. TRIGGERS

TRIGGER PARA ACTUALIZAR EL ESTADO DEL ASIENTO E INSERTAR EL REGISTRO EN LA TABLA ASIENTO CLIENTE

CREATE TRIGGER T_VENTAASIENTO 
  ON REGISTROASIENTO
  AFTER INSERT 
  as
BEGIN
INSERT INTO ASIENTOCLIENTE select i.IDRASIENTO,v.IDCLIENTE from inserted i inner 
join VENTA v on v.IDVENTA=i.IDVENTA;

UPDATE ASIENTO SET IDESTADO=3  FROM inserted i where ASIENTO.IDASIENTO =i.NROASIENTO;


END;

EXPLICANDO EL TRIGGER
El trigger se ejecuta cuando se inserte un registro en la tabla registroasiento entonces se inserta datos en la tabla asientocliente como el id del asiento y el id del cliente. Asi mismo se actualiza el estado del asiento.