База на податоци: VARK Skripta.sql

USE [dbVARK]
GO
/****** Object:  UserDefinedFunction [dbo].[ZemiOstanatiSedista]    Script Date: 30.12.2015 03:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ZemiOstanatiSedista](@TerID INT, @KinoID INT)
RETURNS INT
AS

BEGIN

        DECLARE @Result INT
        DECLARE @p1 AS INT, @p2 AS INT

        SET @p1 = (SELECT Sedista FROM dbo.KinoSali     WHERE Id_Kino = @KinoID) --momentalno sediste
        SET @p2 = (SELECT SUM(Kolicina) FROM dbo.Rezervacii WHERE Rezervacii.ID_TerminFK = @TerID)
        IF (@p2 IS NULL)
                SET @p2 = 0;
        SET @Result = @p1 - @p2
        RETURN @Result
END
GO
/****** Object:  Table [dbo].[Administratori]    Script Date: 30.12.2015 03:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Administratori](
        [Verifikaciski_Kod] [nvarchar](20) NOT NULL,
        [fName] [varchar](50) NOT NULL,
        [lName] [varchar](max) NOT NULL,
        [email_KI] [varchar](50) NOT NULL,
        [telefon] [varchar](50) NULL,
        [Lozinka] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Administratori] PRIMARY KEY CLUSTERED 
(
        [email_KI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AdminKod]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AdminKod](
        [AKID] [int] IDENTITY(1,1) NOT NULL,
        [Kod] [nvarchar](20) NOT NULL,
        [Aktiviran] [bit] NOT NULL CONSTRAINT [DF_AdminKod_Aktiviran]  DEFAULT ((0)),
 CONSTRAINT [PK_AdminKod] PRIMARY KEY CLUSTERED 
(
        [AKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Filmovi]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Filmovi](
        [Id_Film] [int] IDENTITY(1,1) NOT NULL,
        [Naziv] [varchar](max) NOT NULL,
        [Zanr] [varchar](max) NOT NULL,
        [Reziser] [varchar](max) NOT NULL,
        [Akter1] [varchar](max) NOT NULL,
        [Akter2] [varchar](max) NULL,
        [Akter3] [varchar](max) NULL,
 CONSTRAINT [PK__Filmovi__3214EC07F309A015] PRIMARY KEY CLUSTERED 
(
        [Id_Film] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[KinoSali]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KinoSali](
        [Id_Kino] [int] IDENTITY(1,1) NOT NULL,
        [Naziv] [varchar](max) NOT NULL,
        [Adresa] [varchar](max) NOT NULL,
        [email] [varchar](max) NOT NULL,
        [telefon] [varchar](50) NOT NULL,
        [Sedista] [int] NOT NULL,
 CONSTRAINT [PK_KinoSali_1] PRIMARY KEY CLUSTERED 
(
        [Id_Kino] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Kupci]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Kupci](
        [fName] [varchar](50) NOT NULL,
        [lName] [varchar](60) NOT NULL,
        [email_KI] [varchar](50) NOT NULL,
        [telefon] [varchar](50) NULL,
        [Lozinka] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Kupci] PRIMARY KEY CLUSTERED 
(
        [email_KI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Rezervacii]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Rezervacii](
        [ID_Rezervacija] [bigint] IDENTITY(1,1) NOT NULL,
        [kupecEmailFK] [varchar](50) NOT NULL,
        [ID_TerminFK] [int] NOT NULL,
        [Kolicina] [int] NOT NULL CONSTRAINT [DF_dbo.Rezervacii_Kolicina]  DEFAULT ((0)),
        [Platena] [bit] NOT NULL CONSTRAINT [DF_dbo.Rezervacii_Platena]  DEFAULT ((0)),
        [PlatenaDatum] [datetime] NULL,
        [NositelNaKarticka] [nvarchar](100) NULL,
        [TipNaKarticka] [nvarchar](20) NULL,
        [BrojNaKarticka] [nvarchar](16) NULL,
 CONSTRAINT [PK_dbo.Rezervacii] PRIMARY KEY CLUSTERED 
(
        [ID_Rezervacija] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Termini]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Termini](
        [Id_Termin] [int] IDENTITY(1,1) NOT NULL,
        [Id_Film] [int] NOT NULL,
        [Id_Kino] [int] NOT NULL,
        [Datum] [date] NOT NULL,
        [Cas] [time](0) NOT NULL,
        [Cena] [money] NOT NULL CONSTRAINT [DF_Termini_Cena]  DEFAULT ((200)),
        [OstanatiMesta]  AS ([dbo].[ZemiOstanatiSedista]([Id_Termin],[Id_Kino])),
 CONSTRAINT [PK__Termini__790FDCFD6641073C] PRIMARY KEY CLUSTERED 
(
        [Id_Termin] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Rezervacii]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Rezervacii_Kupci] FOREIGN KEY([kupecEmailFK])
REFERENCES [dbo].[Kupci] ([email_KI])
GO
ALTER TABLE [dbo].[Rezervacii] CHECK CONSTRAINT [FK_dbo.Rezervacii_Kupci]
GO
ALTER TABLE [dbo].[Rezervacii]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Rezervacii_Termini] FOREIGN KEY([ID_TerminFK])
REFERENCES [dbo].[Termini] ([Id_Termin])
GO
ALTER TABLE [dbo].[Rezervacii] CHECK CONSTRAINT [FK_dbo.Rezervacii_Termini]
GO
ALTER TABLE [dbo].[Termini]  WITH CHECK ADD  CONSTRAINT [FK_Termini_To_Filmovi] FOREIGN KEY([Id_Film])
REFERENCES [dbo].[Filmovi] ([Id_Film])
GO
ALTER TABLE [dbo].[Termini] CHECK CONSTRAINT [FK_Termini_To_Filmovi]
GO
ALTER TABLE [dbo].[Termini]  WITH CHECK ADD  CONSTRAINT [FK_Termini_To_KinoSali] FOREIGN KEY([Id_Kino])
REFERENCES [dbo].[KinoSali] ([Id_Kino])
GO
ALTER TABLE [dbo].[Termini] CHECK CONSTRAINT [FK_Termini_To_KinoSali]
GO
/****** Object:  StoredProcedure [dbo].[GetMyCart]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMyCart] 
    @email VARCHAR(50)  = ''
AS
SELECT TOP 50 ID_Rezervacija AS RID
          ,t3.Naziv AS FNaziv
          ,t4.Naziv AS KNaziv
          ,t2.Cena AS CenaBilet
          ,Kolicina
          , (Cena * Kolicina) AS CenaVkupno
  FROM dbo.Rezervacii AS t1
  LEFT JOIN dbo.Termini AS t2 ON t1.ID_TerminFK = t2.Id_Termin
  LEFT JOIN dbo.Filmovi AS t3 ON t2.Id_Film = t3.Id_Film
  LEFT JOIN dbo.KinoSali AS t4 ON t2.Id_Kino = t4.Id_Kino
  WHERE kupecEmailFK = @email AND Platena = 0;
RETURN 0 
GO
/****** Object:  StoredProcedure [dbo].[GetTermin]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetTermin]

AS
SELECT t1.Id_Termin, t2.Naziv AS FNaziv, t2.Zanr, t3.Naziv AS KNaziv, t1.Datum, t1.Cas, t1.Cena FROM dbo.Termini AS t1
LEFT JOIN dbo.Filmovi AS t2 ON t1.Id_Film = t2.Id_Film
LEFT JOIN dbo.KinoSali AS t3 ON t1.Id_Kino = t1.Id_Kino
WHERE t1.Datum >= {fn NOW()}; 
RETURN 0 
GO