· 9 years ago · Jan 30, 2017, 08:50 PM
1/*
2use master
3go
4
5set language polski
6go
7
8if db_id('Hotel') is not null
9drop database Hotel
10go
11
12if db_id('Hotel') is null
13create database Hotel
14go
15*/
16
17use Hotel
18go
19
20if object_id('dbo.Rezerwacja','U') is not null
21drop table dbo.Rezerwacja
22
23if object_id('dbo.Pracownik','U') is not null
24drop table dbo.Pracownik
25
26if object_id('dbo.Pokoj','U') is not null
27drop table dbo.Pokoj
28
29if object_id('dbo.Pies','U') is not null
30drop table dbo.Pies
31
32if object_id('dbo.Klient','U') is not null
33drop table dbo.Klient
34
35if object_id('dbo.RodzajPokoju','U') is not null
36drop table dbo.RodzajPokoju
37
38if object_id('dbo.Placowka','U') is not null
39drop table dbo.Placowka
40GO
41
42/* Drop all non-system stored procs */
43DECLARE @name VARCHAR(128)
44DECLARE @SQL VARCHAR(254)
45
46SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
47
48WHILE @name is not null
49BEGIN
50 SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
51 EXEC (@SQL)
52 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
53END
54GO
55
56/* Drop all functions */
57DECLARE @name VARCHAR(128)
58DECLARE @SQL VARCHAR(254)
59
60SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
61
62WHILE @name IS NOT NULL
63BEGIN
64 SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
65 EXEC (@SQL)
66 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
67END
68GO
69
70/* Drop all views */
71DECLARE @name VARCHAR(128)
72DECLARE @SQL VARCHAR(254)
73
74SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
75
76WHILE @name IS NOT NULL
77BEGIN
78 SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
79 EXEC (@SQL)
80 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
81END
82GO
83
84
85create table Klient(
86PESEL char(11) primary key,
87imie varchar(40) not null,
88nazwisko varchar(40) not null,
89adres varchar(300),
90telefon varchar(11)
91);
92
93create table Placowka(
94idPlacowki int primary key identity(1,1),
95nazwa varchar(50) not null,
96adres varchar(300) not null,
97telefon varchar(11)
98);
99
100create table Pracownik(
101idPracownika int primary key identity (1,1),
102imie varchar(40) not null,
103nazwisko varchar(40) not null,
104adres varchar(300),
105telefon varchar(11),
106stanowisko varchar(15) not null,
107czyAktywny bit default 'true',
108idPlacowki int references Placowka(idPlacowki)
109);
110
111create table Pies(
112idPsa int primary key identity(1,1),
113imie varchar(40) not null,
114rok_urodzenia int,
115rasa varchar(100) not null,
116wielkosc varchar(10),
117PESELWlasciciela char(11) references Klient(PESEL),
118constraint data_urodzenia check (rok_urodzenia > 1990)
119);
120
121create table RodzajPokoju(
122idRodzaju tinyint primary key identity(1,1),
123typ varchar(30) not null,
124cena smallint not null
125);
126
127create table Pokoj(
128idPokoju int primary key identity(1,1),
129opis text,
130idPlacowki int references Placowka(idPlacowki),
131idRodzaju tinyint references RodzajPokoju(idRodzaju),
132);
133
134create table Rezerwacja(
135idRezerwacji int primary key identity(1,1),
136dataRezerwacji datetime,
137dataZameldowania date,
138dataWymeldowania date,
139czyAktywna bit default 'true',
140PESEL char(11) references Klient(PESEL),
141idPsa int references Pies(idPsa),
142idPokoju int references Pokoj(idPokoju),
143idPracownika int references Pracownik(idPracownika),
144constraint checkdate1 check (dataWymeldowania > dataZameldowania),
145constraint checkdate2 check (dataZameldowania > dataRezerwacji),
146constraint checkdate3 check (dataWymeldowania > dataRezerwacji),
147);
148
149GO
150
151CREATE TRIGGER czyAktywny ON Rezerwacja
152INSTEAD OF INSERT
153AS
154
155 IF NOT EXISTS(select distinct p.idPsa from inserted i join Pies p on i.idPsa = p.idPsa and i.PESEL=p.PESELWlasciciela)
156 BEGIN
157 ;THROW 51000, 'Taka osoba nie posiada podanego psa!',1
158 END
159 ELSE IF (select dataWymeldowania from inserted) < GETDATE()
160 INSERT INTO Rezerwacja values ((select dataRezerwacji from inserted),(select dataZameldowania from inserted),(select dataWymeldowania from inserted), 'false' ,(select PESEL from inserted), (select idPsa from inserted), (select idPokoju from inserted), (select idPracownika from inserted))
161 ELSE IF (select dataWymeldowania from inserted) is NULL or (select dataWymeldowania from inserted) > GETDATE()
162 INSERT INTO Rezerwacja values ((select dataRezerwacji from inserted),(select dataZameldowania from inserted),(select dataWymeldowania from inserted), 'true' ,(select PESEL from inserted), (select idPsa from inserted), (select idPokoju from inserted), (select idPracownika from inserted))
163GO
164
165CREATE TRIGGER sprawdzPsaUpdate on Rezerwacja
166AFTER UPDATE
167as
168 IF NOT EXISTS(select distinct p.idPsa from inserted i join Pies p on i.idPsa = p.idPsa and i.PESEL=p.PESELWlasciciela)
169 BEGIN
170 RAISERROR('Taka osoba nie posiada podanego psa!', 11, 1)
171 rollback
172 END
173go
174
175create trigger pobyt on rezerwacja
176after insert,update
177as
178 declare @idPokoju int,
179 @dataRezerwacji datetime,
180 @dataZameldowania date,
181 @dataWymeldowania date,
182 @zameldowany date,
183 @wymeldowany date,
184 @maxliczbarez int
185
186 set @idPokoju = (select idPokoju from inserted)
187 set @dataRezerwacji = (select dataRezerwacji from inserted)
188 set @dataZameldowania = (select dataZameldowania from inserted)
189 set @dataWymeldowania = (select dataWymeldowania from inserted)
190 set @maxliczbarez = (select COUNT(*) from Rezerwacja where PESEL = (select PESEL from inserted) and czyAktywna ='true' group by PESEL)
191
192 if (@maxliczbarez > 2)
193 begin
194 RAISERROR('Ta osoba ma już maksymalną liczbę aktywnych rezerwacji!', 11, 1)
195 rollback
196 end
197 else if exists (select * from Rezerwacja where idPokoju = @idPokoju and czyAktywna = 'true' and dataRezerwacji != @dataRezerwacji)
198 begin
199 select @zameldowany = dataZameldowania, @wymeldowany = dataWymeldowania from Rezerwacja where idPokoju = @idPokoju and czyAktywna = 'true' and dataRezerwacji != @dataRezerwacji
200 if not ((@dataZameldowania < @zameldowany and @dataWymeldowania <= @zameldowany) OR (@dataZameldowania >=@wymeldowany))
201 begin
202 RAISERROR('Ten pokój jest zajęty w tym terminie!', 11, 1)
203 rollback
204 end
205 end
206go
207
208
209insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('95264785129','Jan','Nowak','Jeziorańskiego 16','541245784')
210insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('94251368452','Kamila','Kuznowicz','Aleje Niepodległości 34c','514751423')
211insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('94523654782','Albert','Paderewski','Mickiewicza 14','625412547')
212insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('85654125412','Marcjanna','Borowiecka','Ul. Kaszatanowa 77','654124785')
213insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('78121245789','Antonina','Wójcik','Wielkopolańska 84','512645877')
214insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('72102548754','Piotr','Zięba','Szeligowa 8','542154789')
215insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('86060245125','Ewelina','Majchrzak','Åšwierkowa 22a','665214578')
216insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('79041842512','Daria','Makowska','Sarnecka 16d/74','541246985')
217insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('78030214586','Radosław','Pamiętny','Jaglana 13','652140112')
218
219insert into Placowka(nazwa,adres,telefon) values ('Uszko','SÅ‚owackiego 15','222011071')
220insert into Placowka(nazwa,adres,telefon) values ('Åapka','Grunwaldzka 23c','226457784')
221insert into Placowka(nazwa,adres,telefon) values ('Nosek','Sokoła 16','221045587')
222insert into Placowka(nazwa,adres,telefon) values ('Pyszczek','Kasztanowa 70','222044571')
223
224insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Alicja','Jakubczyk','532645785','Kierownik',1,1)
225insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Jakub','Kowalewski','512458745','Opiekun',0,1)
226insert into Pracownik(imie,nazwisko,adres,telefon,stanowisko,czyAktywny,idPlacowki) values ('Adam','Bartczak','Kraszewskiego 145f','541258745','Opiekun',1,1)
227insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Agnieszka','Szwedek','652145789','Kierownik',1,2)
228insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Åucja','PaÅ„szczyk','652145852','Opiekun',1,2)
229insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Filip','Marciniak','563210114','Kierownik',0,3)
230insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Monika','Kamieniek','645641625','Opiekun',1,3)
231insert into Pracownik(imie,nazwisko,adres,telefon,stanowisko,czyAktywny,idPlacowki) values ('Mateusz','Bernatowicz','Karmlekicka 2a','653214785','Opiekun',1,3)
232insert into Pracownik(imie,nazwisko,adres,telefon,stanowisko,czyAktywny,idPlacowki) values ('Kamil','Serafin','Kasztanowa 82c','578451212','Kierownik',1,4)
233insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Patryk','Kozerski','652314579','Opiekun',0,4)
234insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Andrzej','Szymczak','542100369','Opiekun',1,4)
235
236insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Brzęczyk','1999','Cocker Spaniel','średni','95264785129')
237insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Ludka','2005','Pekińczyk','mały','94251368452')
238insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Bińczyk','2010','Dog niemiecki','duży','94523654782')
239insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Berto','2008','Dog niemiecki','duży','94523654782')
240insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Makak','2012','Pinczer','mały','78121245789')
241insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Dusia','2012','Pinczer','mały','78121245789')
242insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Lola','2012','Pinczer','mały','78121245789')
243insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Reksio','2004','York','mały','85654125412')
244insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Azor','2009','West Highland White Terrier','mały','72102548754')
245insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Maksio','2011','Owczarek niemiecki','duży','86060245125')
246insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Gildzia','2016','Cavalier king charles spaniel','średni','79041842512')
247insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Tuńczyk','2017','Amerykański eskimo','średni','79041842512')
248insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Pershing','2017','Golden retriever','duży','78030214586')
249
250insert into RodzajPokoju(typ,cena) values ('mały standard',60)
251insert into RodzajPokoju(typ,cena) values ('średni standard',80)
252insert into RodzajPokoju(typ,cena) values ('duży standard',100)
253insert into RodzajPokoju(typ,cena) values ('mały apartament',200)
254insert into RodzajPokoju(typ,cena) values ('duży apartament',300)
255
256insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, duża ilość zabawek, małe legowisko.',1,1)
257insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych i średnich psów, duża ilość zabawek, małe i średnie legowisko.',1,2)
258insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla średnich i dużych psów, duża ilość zabawek, średnie i duże legowisko.',1,3)
259insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, duża ilość zabawek, małe legowisko.',2,1)
260insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych i średnich psów, duża ilość zabawek, małe i średnie legowisko.',2,2)
261insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla średnich i dużych psów, duża ilość zabawek, średnie i duże legowisko.',2,3)
262insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, z małym legowiskiem i ogrodem do biegania.',2,4)
263insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla dużych psów, z dużym legowiskiem i ogrodem do biegania.',2,5)
264insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, duża ilość zabawek, małe legowisko.',3,1)
265insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, duża ilość zabawek, małe legowisko.',3,1)
266insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych i średnich psów, duża ilość zabawek, małe i średnie legowisko.',3,2)
267insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla średnich i dużych psów, duża ilość zabawek, średnie i duże legowisko.',3,3)
268insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych i średnich psów, duża ilość zabawek, małe i średnie legowisko.',4,2)
269insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla średnich i dużych psów, duża ilość zabawek, średnie i duże legowisko.',4,3)
270insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, z małym legowiskiem i ogrodem do biegania.',4,4)
271insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla dużych psów, z dużym legowiskiem i ogrodem do biegania.',4,5)
272
273insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2010-10-20T10:00:00','2010-10-21','2010-10-24','95264785129',1,3,1)
274insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2011-11-21T09:00:00','2011-11-23','2011-11-26','94251368452',2,1,3)
275insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2013-03-11T11:00:00','2013-04-14','2013-04-26','94523654782',3,12,8)
276insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2015-05-11T12:42:00','2015-05-14','2015-05-26','94523654782',4,16,9)
277insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-09-01T10:42:00','2016-12-07','2016-12-10','78121245789',5,2,3)
278insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-10-20T10:00:00','2016-10-21','2016-10-24','94251368452',2,4,4)
279insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-11-21T09:00:00','2016-11-23','2016-11-26','94251368452',2,4,5)
280insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-11-11T11:00:00','2016-12-14','2016-12-26','85654125412',8,13,11)
281insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-11-11T12:42:00','2017-01-01','2017-01-05','72102548754',9,11,7)
282insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-12-01T10:42:00','2017-02-07','2017-02-10','86060245125',10,16,11)
283insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-12-20T10:00:00','2016-12-30','2016-12-31','86060245125',10,12,7)
284insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2017-01-01T09:00:00','2017-11-23','2017-11-26','78121245789',7,7,4)
285insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2017-01-02T11:00:00','2017-04-14','2017-04-26','79041842512',11,5,5)
286insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2017-01-03T12:42:00','2017-05-14','2017-05-26','79041842512',11,6,4)
287insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2017-01-14T10:42:00','2017-12-07','2017-12-10','95264785129',1,3,3)
288
289GO
290
291CREATE PROCEDURE dodajKlienta
292 @PESEL char(11),
293 @imie varchar(40),
294 @nazwisko varchar(40),
295 @adres varchar(300),
296 @telefon varchar(11)
297AS
298 IF(LEN(@PESEL) != 11) OR (ISNUMERIC(@PESEL) <> 1)
299 RAISERROR('ZÅ‚a forma PESELU!', 12, 2)
300 ELSE
301 BEGIN
302 IF EXISTS (SELECT * FROM Klient WHERE PESEL = @PESEL)
303 RAISERROR('Taki klient jest już w bazie!', 12, 2)
304 ELSE
305 INSERT INTO Klient VALUES(@PESEL,@imie,@nazwisko,@adres,@telefon)
306 END
307GO
308
309
310CREATE PROCEDURE zmienDaneKlienta
311 @PESEL char(11),
312 @noweImie varchar(40),
313 @noweNazwisko varchar(40),
314 @nowyAdres varchar(200),
315 @nowyTelefon char(9)
316AS
317 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESEL)
318 RAISERROR('Takiego klienta nie ma w bazie!', 12, 2)
319 ELSE
320 UPDATE Klient
321 SET imie = @noweImie,
322 nazwisko = @noweNazwisko,
323 adres = @nowyAdres,
324 telefon = @nowyTelefon
325 WHERE PESEL = @PESEL
326GO
327
328
329CREATE PROCEDURE dodajPsa
330 @imie varchar(40),
331 @rok_urodzenia varchar(4),
332 @rasa varchar(100),
333 @wielkosc varchar(10),
334 @PESELWlasciciela char(11)
335AS
336 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESELWlasciciela)
337 RAISERROR('Nie ma takiego właściciela w bazie!', 11, 1)
338 ELSE IF EXISTS (SELECT * FROM Pies WHERE imie = @imie AND PESELWlasciciela = @PESELWlasciciela)
339 RAISERROR('Taki pies jest już w bazie!', 11, 1)
340 ELSE
341 INSERT INTO Pies VALUES(@imie,@rok_urodzenia,@rasa,@wielkosc,@PESELWlasciciela)
342GO
343
344
345CREATE PROCEDURE zmienDanePsa
346 @idPsa int,
347 @PESELWlasciciela char(11),
348 @noweImie varchar(40),
349 @nowyRok_urodzenia varchar(4),
350 @nowaRasa varchar(100),
351 @nowaWielkosc varchar(10)
352AS
353 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESELWlasciciela)
354 RAISERROR('Takiego właściciela nie ma w bazie!', 12, 2)
355 ELSE IF NOT EXISTS (SELECT * FROM Pies WHERE idPsa=@idPsa AND PESELWlasciciela = @PESELWlasciciela)
356 RAISERROR('Takiego psa nie ma w bazie!', 11, 1)
357 ELSE
358 UPDATE Pies
359 SET imie = @noweImie,
360 rok_urodzenia = @nowyRok_urodzenia,
361 rasa = @nowaRasa,
362 wielkosc = @nowaWielkosc
363 WHERE idPsa = @idPsa
364GO
365
366CREATE PROCEDURE dodajPracownika
367 @imie varchar(40),
368 @nazwisko varchar(40),
369 @adres varchar(300),
370 @telefon varchar(11),
371 @stanowisko varchar(15),
372 @czyAktywny bit = 'true',
373 @idPlacowki int
374AS
375 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @idPlacowki)
376 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
377 ELSE IF EXISTS (SELECT * FROM Pracownik WHERE imie = @imie AND nazwisko = @nazwisko AND telefon = @telefon)
378 RAISERROR('Taki pracownik jest już w bazie!', 11, 1)
379 ELSE
380 INSERT INTO Pracownik VALUES(@imie,@nazwisko,@adres,@telefon,@stanowisko,@czyAktywny,@idPlacowki)
381GO
382
383CREATE PROCEDURE zmienDanePracownika
384 @idPracownika int,
385 @noweImie varchar(40),
386 @nowenazwisko varchar(40),
387 @nowyadres varchar(300),
388 @nowytelefon varchar(11),
389 @nowestanowisko varchar(15),
390 @nowyczyAktywny bit,
391 @noweidPlacowki int
392AS
393 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @noweidPlacowki)
394 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
395 ELSE IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika=@idPracownika)
396 RAISERROR('Takiego pracownika nie ma w bazie!', 11, 1)
397 ELSE
398 UPDATE Pracownik
399 SET imie = @noweImie,
400 nazwisko = @noweNazwisko,
401 adres = @nowyAdres,
402 telefon = @nowyTelefon,
403 stanowisko=@nowestanowisko,
404 czyAktywny =@nowyczyAktywny,
405 idPlacowki=@noweidPlacowki
406 WHERE idPracownika=@idPracownika
407GO
408
409
410CREATE PROCEDURE dodajPlacowke
411 @nazwa varchar(50),
412 @adres varchar(300),
413 @telefon varchar(11)
414AS
415 IF EXISTS (SELECT * FROM Placowka WHERE nazwa = @nazwa)
416 RAISERROR('Taka placówka jest już w bazie!', 11, 1)
417 ELSE
418 INSERT INTO Placowka VALUES(@nazwa,@adres,@telefon)
419GO
420
421
422CREATE PROCEDURE zmienDanePlacowki
423 @idplacowki int,
424 @nowanazwa varchar(50),
425 @nowyadres varchar(300),
426 @nowytelefon varchar(11)
427AS
428 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @idplacowki)
429 RAISERROR('Takiej placówki nie ma w bazie!', 11, 1)
430 ELSE
431 UPDATE Placowka
432 SET nazwa=@nowanazwa,
433 adres=@nowyadres,
434 telefon=@nowytelefon
435 WHERE idPlacowki=@idplacowki
436GO
437
438CREATE PROCEDURE dodajPokoj
439 @opis text,
440 @idPlacowki int,
441 @idRodzaju tinyint
442AS
443 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @idPlacowki)
444 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
445 ELSE IF NOT EXISTS (SELECT * FROM RodzajPokoju WHERE idRodzaju = @idRodzaju)
446 RAISERROR('Nie ma takiego rodzaju w bazie!', 11, 1)
447 ELSE
448 INSERT INTO Pokoj VALUES(@opis,@idPlacowki,@idRodzaju)
449GO
450
451CREATE PROCEDURE zmienDanePokoju
452 @idpokoju int,
453 @nowyopis text,
454 @noweidPlacowki int,
455 @noweidRodzaju tinyint
456AS
457 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @noweidPlacowki)
458 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
459 ELSE IF NOT EXISTS (SELECT * FROM RodzajPokoju WHERE idRodzaju = @noweidRodzaju)
460 RAISERROR('Nie ma takiego rodzaju w bazie!', 11, 1)
461 ELSE IF NOT EXISTS(SELECT * FROM Pokoj WHERE idPokoju = @idpokoju)
462 RAISERROR('Nie ma takiego pokoju w bazie!', 11, 1)
463 ELSE
464 UPDATE Pokoj
465 SET opis=@nowyopis,
466 idPlacowki=@noweidPlacowki,
467 idRodzaju=@noweidRodzaju
468 WHERE idPokoju=@idpokoju
469GO
470
471CREATE PROCEDURE utworzRezerwacje
472 @dataZameldowania date,
473 @dataWymeldowania date,
474 @PESEL char(11),
475 @idPsa int,
476 @idPokoju int,
477 @idPracownika int
478AS
479 declare @dataRezerwacji datetime
480
481 set @dataRezerwacji = GETDATE()
482
483 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESEL)
484 RAISERROR('Nie ma takiego klienta w bazie!', 11, 1)
485 ELSE IF NOT EXISTS (SELECT * FROM Pies WHERE idPsa = @idPsa)
486 RAISERROR('Nie ma takiego psa w bazie!', 11, 1)
487 ELSE IF NOT EXISTS (SELECT * FROM Pokoj WHERE idPokoju = @idPokoju)
488 RAISERROR('Nie ma takiego pokoju w bazie!', 11, 1)
489 ELSE IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika)
490 RAISERROR('Nie ma takiego pracownika w bazie!', 11, 1)
491 ELSE IF EXISTS (SELECT * FROM Rezerwacja WHERE dataRezerwacji = @dataRezerwacji AND idPokoju = @idPokoju)
492 RAISERROR('Taka rezerwacja już istnieje!', 11, 1)
493 ELSE
494 INSERT INTO Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) VALUES(@dataRezerwacji,@dataZameldowania,@dataWymeldowania,@PESEL,@idPsa,@idPokoju,@idPracownika)
495GO
496
497CREATE PROCEDURE zmienDaneRezerwacji
498 @idRezerwacji int,
499 @nowadataZameldowania date,
500 @nowadataWymeldowania date,
501 @nowaczyAktywna bit,
502 @nowyPESEL char(11),
503 @noweidPsa int,
504 @noweidPokoju int,
505 @noweidPracownika int
506
507AS
508 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @nowyPESEL)
509 RAISERROR('Nie ma takiego klienta w bazie!', 11, 1)
510 ELSE IF NOT EXISTS (SELECT * FROM Pies WHERE idPsa =@noweidPsa)
511 RAISERROR('Nie ma takiego psa w bazie!', 11, 1)
512 ELSE IF NOT EXISTS (SELECT * FROM Pokoj WHERE idPokoju = @noweidPokoju)
513 RAISERROR('Nie ma takiego pokoju w bazie!', 11, 1)
514 ELSE IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @noweidPracownika)
515 RAISERROR('Nie ma takiego pracownika w bazie!', 11, 1)
516 ELSE IF NOT EXISTS (SELECT * FROM Rezerwacja WHERE idRezerwacji=@idRezerwacji)
517 RAISERROR('Taka rezerwacja nie istnieje!', 11, 1)
518 ELSE
519 UPDATE Rezerwacja
520 SET dataZameldowania=@nowadataZameldowania,
521 dataWymeldowania=@nowadataWymeldowania,
522 czyAktywna=@nowaczyAktywna,
523 PESEL=@nowyPESEL,
524 idPsa=@noweidPsa,
525 idPokoju=@noweidPokoju,
526 idPracownika=@noweidPracownika
527 WHERE idRezerwacji=@idRezerwacji
528GO
529
530CREATE PROCEDURE aktywujPracownika
531 @idPracownika int
532AS
533 IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika)
534 RAISERROR('Takiego pracownika nie ma w bazie!', 11, 2)
535 ELSE IF EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika and czyAktywny='true')
536 RAISERROR('Ten pracownik jest już aktywny!', 11, 3)
537 ELSE
538 UPDATE Pracownik
539 SET czyAktywny = 'true'
540 WHERE idPracownika=@idPracownika
541GO
542
543CREATE PROCEDURE dezaktywujPracownika
544 @idPracownika int
545AS
546 IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika)
547 RAISERROR('Takiego pracownika nie ma w bazie!', 11, 2)
548 ELSE IF EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika and czyAktywny='false')
549 RAISERROR('Ten pracownik jest już nieaktywny!', 11, 3)
550 ELSE
551 UPDATE Pracownik
552 SET czyAktywny = 'false'
553 WHERE idPracownika = @idPracownika
554GO
555
556CREATE PROCEDURE aktywujRezerwacje
557 @idRezerwacji int
558AS
559 IF NOT EXISTS (SELECT * FROM Rezerwacja WHERE idRezerwacji=@idRezerwacji)
560 RAISERROR('Takiej rezerwacji nie ma w bazie!', 11, 2)
561 ELSE IF EXISTS (SELECT * FROM Rezerwacja WHERE idRezerwacji = @idRezerwacji and czyAktywna='true')
562 RAISERROR('Ta rezerwacja jest już aktywna!', 11, 3)
563 ELSE
564 UPDATE Rezerwacja
565 SET czyAktywna = 'true'
566 WHERE idRezerwacji = @idRezerwacji
567GO
568
569CREATE PROCEDURE dezaktywujRezerwacje
570 @idRezerwacji int
571AS
572 IF NOT EXISTS (SELECT * FROM Rezerwacja WHERE idRezerwacji=@idRezerwacji)
573 RAISERROR('Takiej rezerwacji nie ma w bazie!', 11, 2)
574 ELSE IF EXISTS (SELECT * FROM Rezerwacja WHERE idRezerwacji = @idRezerwacji and czyAktywna='false')
575 RAISERROR('Ta rezerwacja jest już nieaktywna!', 11, 3)
576 ELSE
577 UPDATE Rezerwacja
578 SET czyAktywna = 'false'
579 WHERE idRezerwacji = @idRezerwacji
580GO
581
582CREATE PROCEDURE przeniesPracownika
583 @idPracownika int,
584 @idPlacowki int
585AS
586 IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika)
587 RAISERROR('Takiego pracownika nie ma w bazie!', 11, 2)
588 ELSE IF NOT EXISTS(SELECT * FROM Placowka WHERE idPlacowki = @idPlacowki)
589 RAISERROR('Takiej placówki nie ma w bazie!', 11, 2)
590 ELSE IF EXISTS(SELECT * FROM Pracownik where idPracownika = @idPracownika and idPlacowki = @idPlacowki)
591 RAISERROR('Taki pracownik jest już przypisany do takiej placówki!', 11, 2)
592 ELSE
593 UPDATE Pracownik
594 SET idPlacowki=@idPlacowki
595 WHERE idPracownika=@idPracownika
596GO
597
598
599create function wolnePokoje(@dataZameldowania date, @dataWymeldowania date)
600returns table
601as
602 return(select * from Pokoj
603where idPokoju not in (select idPokoju from Rezerwacja where
604 (czyAktywna = 'true'
605 and not ((@dataZameldowania < dataZameldowania and @dataWymeldowania <= dataZameldowania) OR (@dataZameldowania >= dataWymeldowania)))
606 or ((@dataZameldowania >= dataZameldowania) and dataWymeldowania is null)
607 ))
608go
609
610create function historiaKlienta(@PESEL char(11))
611returns table
612as
613return (select * from Rezerwacja where PESEL = @PESEL)
614go
615
616create function pokazPokojePoCenie()
617returns table
618as
619 return (select top (select convert (int,IDENT_CURRENT('Pokoj'))) p.idPokoju,p.idPlacowki,p.opis,rp.typ,rp.cena from Pokoj p join RodzajPokoju rp on p.idRodzaju=rp.idRodzaju order by rp.cena desc)
620go
621
622create function pokazPokojePoWielkosci()
623returns table
624as
625 return(select top (select convert (int,IDENT_CURRENT('Pokoj'))) p.idRodzaju,rp.typ,p.idPokoju,p.opis from Pokoj p join RodzajPokoju rp on p.idRodzaju = rp.idRodzaju order by p.idRodzaju )
626go
627
628create function cenaPokoju(@idRezerwacji int)
629returns @cena table (cena float)
630as
631begin
632declare @dlugosc int
633set @dlugosc = (select DATEDIFF(DAY,dataZameldowania,dataWymeldowania) from Rezerwacja where idRezerwacji = @idRezerwacji)
634if exists (select top 1 PESEL from Rezerwacja where
635 PESEL = (select PESEL from Rezerwacja where idRezerwacji = @idRezerwacji)
636 and DATEDIFF(YEAR,dataRezerwacji,GETDATE()) >= 5)
637 and (select COUNT(*) from Rezerwacja where
638 PESEL = (select PESEL from Rezerwacja where idRezerwacji = @idRezerwacji)
639 and idRezerwacji < @idRezerwacji) >= 5
640 begin
641 insert into @cena select cena * 0.8 * @dlugosc from RodzajPokoju
642 where idRodzaju = (select idRodzaju from Pokoj
643 where idPokoju = (select idPokoju from Rezerwacja where idRezerwacji = @idRezerwacji))
644 end
645else if exists (select PESEL from Rezerwacja where
646 PESEL = (select PESEL from Rezerwacja where idRezerwacji = @idRezerwacji)
647 and DATEDIFF(MONTH,dataRezerwacji,GETDATE()) <= 3
648 and idRezerwacji < @idRezerwacji)
649 begin
650 insert into @cena select cena * 0.9 * @dlugosc from RodzajPokoju
651 where idRodzaju = (select idRodzaju from Pokoj
652 where idPokoju = (select idPokoju from Rezerwacja where idRezerwacji = @idRezerwacji))
653 end
654else if exists (select PESEL from Rezerwacja where
655 PESEL = (select PESEL from Rezerwacja where idRezerwacji = @idRezerwacji))
656 begin
657 insert into @cena select cena * @dlugosc from RodzajPokoju
658 where idRodzaju = (select idRodzaju from Pokoj
659 where idPokoju = (select idPokoju from Rezerwacja where idRezerwacji = @idRezerwacji))
660 end
661else
662 insert into @cena select distinct (cena * 0 - 1) from RodzajPokoju
663return
664end
665go
666
667create view Ksiegowosc
668as
669select idRezerwacji,dataRezerwacji,dataZameldowania,dataWymeldowania,czyAktywna,PESEL,idPsa,idPokoju,idPracownika,(select * from cenaPokoju(idRezerwacji)) 'cena' from Rezerwacja
670go
671
672
673create function pokazPsyWlasciciela(@PESEL char(11))
674returns table
675as
676return (SELECT p.idPsa,p.imie,p.rok_urodzenia,p.rasa,p.wielkosc from Pies p join Klient k on p.PESELWlasciciela=k.PESEL where p.PESELWlasciciela = @PESEL)
677go
678
679--najczesciej wynajmowany pokój
680create view najczesciejWynajmowanyPokoj
681as
682select p.idPokoju,rp.typ,rp.cena,COUNT(*) 'liczba rezerwacji'
683from Rezerwacja r join Pokoj p on r.idPokoju=p.idPokoju join RodzajPokoju rp on p.idRodzaju=rp.idRodzaju
684group by p.idPokoju,rp.typ,rp.cena
685having COUNT(*) =
686 (select MAX(liczba)
687 from
688 (select idPokoju, COUNT(idPokoju) liczba
689 from Rezerwacja
690 group by idPokoju) as tab)
691go
692
693--klient o najwiekszej liczbie psów
694
695create view klientZNajwiekszaIlosciaPsow
696as
697select k.PESEL,k.imie,k.nazwisko,k.adres,k.telefon,COUNT(*) 'liczba psow'
698from Klient k join Pies p on k.PESEL=p.PESELWlasciciela
699group by k.PESEL,k.imie,k.nazwisko,k.adres,k.telefon
700having COUNT(*) =
701 (select MAX(liczba)
702 from
703 (select PESELWlasciciela, COUNT(PESELWlasciciela) liczba
704 from Pies
705 group by PESELWlasciciela) as tab)
706
707go
708
709
710--najczęściej wybierany pracownik pomiędzy datami
711create function najlepszyPracownik(@date1 date, @date2 date)
712returns table
713as
714return (select p.idPracownika,p.imie,p.nazwisko,p.stanowisko, COUNT(*) 'ile razy wybrany'
715from Rezerwacja r join Pracownik p on r.idPracownika=p.idPracownika
716where r.dataZameldowania >= @date1 and r.dataWymeldowania <= @date2
717group by p.idPracownika,p.imie,p.nazwisko,p.stanowisko
718having COUNT(*) = (select MAX(liczba)
719 from (select r.idPracownika,COUNT(r.idPracownika)liczba from Rezerwacja r join Pracownik p
720 on r.idPracownika=p.idPracownika where r.dataZameldowania >= @date1 and r.dataWymeldowania <= @date2
721 group by r.idPracownika) tab))
722go
723
724--3 najlepszych klientow
725select PESEL, COUNT(PESEL) AS 'liczba rezerwacji'
726from Rezerwacja
727GROUP BY PESEL
728having COUNT(PESEL) in
729(
730 SELECT top 3 COUNT(PESEL) as 'liczba rezerwacji'
731 FROM Rezerwacja
732 GROUP BY PESEL
733 ORDER BY [liczba rezerwacji] DESC
734)
735ORDER BY [liczba rezerwacji] DESC
736go
737
738select * from Placowka
739select * from Pokoj
740select * from Pracownik
741select * from Rezerwacja
742select * from RodzajPokoju
743select * from Klient