· 9 years ago · Jan 31, 2017, 06:44 PM
1drop procedure klient_rejestracja
2drop procedure klient_usuwanie
3drop procedure klient_przywracanie
4drop procedure klient_zmienImie
5drop procedure klient_zmienNazwisko
6drop procedure klient_zmienAdres
7drop procedure klient_zmienTelefon
8drop procedure klient_zmienMail
9drop procedure klient_awansuj
10drop procedure klient_degraduj
11drop procedure administrator_dodawanie
12drop procedure moderator_dodawanie
13drop function klient_lista
14GO
15
16drop table Zwrot
17drop table Wymiana
18drop table Reklamacja
19drop table Zamowienie_Towar
20drop table Towar
21drop table Zamowienie
22drop table Status
23drop table Klient
24drop table Uzytkownik
25drop table Rola
26GO
27
28create table Rola (
29 id smallint identity (1,1) primary key,
30 nazwa varchar(50) not null
31)
32create table Uzytkownik(
33 id int identity (1,1) primary key,
34 login varchar (50) unique not null,
35 haslo varchar (50) unique not null,
36 czyAktywny bit default 'true',
37 rola smallint references Rola(id)
38)
39create table Klient(
40 id int identity (1,1) primary key,
41 imie varchar (50) not null,
42 nazwisko varchar (50) not null,
43 adres text not null,
44 telefon varchar (11) not null,
45 email varchar (100),
46 idUzytkownika int references Uzytkownik(id)
47)
48create table Status (
49 id smallint identity (1,1) primary key,
50 nazwa varchar(50) not null
51)
52create table Zamowienie(
53 id int identity (1,1) primary key,
54 idKlienta int references Klient(id),
55 dataZamowiena date not null,
56 dataRealizacji date,
57 wartosc int not null,
58 status smallint references Status(id)
59)
60create table Towar (
61 id int identity (1,1) primary key,
62 producent varchar (100) not null,
63 nazwa varchar (100) not null unique,
64 ilosc int not null,
65 cenaNetto money not null,
66 podatek smallint not null default '23',
67 cenaBrutto as round( (cenaNetto + cenaNetto * podatek * 0.01), 2)
68)
69create table Zamowienie_Towar(
70 idZamowienia int references Zamowienie(id),
71 idTowaru int references Towar(id),
72 ilosc int not null,
73 cena money not null default '0',
74 imei varchar (15) not null,
75 primary key (idZamowienia, idTowaru)
76)
77create table Reklamacja(
78 id_rekamacja int identity (1,1) primary key,
79 idZamowienia int references Zamowienie(id),
80 idTowaru int references Towar(id),
81 opis text not null,
82 status smallint references Status(id)
83)
84create table Wymiana(
85 id_wymiana int identity (1,1) primary key,
86 idZamowienia int references Zamowienie(id),
87 idTowaru int references Towar(id),
88 opis text not null,
89 status smallint references Status(id)
90)
91create table Zwrot(
92 id_zwrot int identity (1,1) primary key,
93 idZamowienia int references Zamowienie(id),
94 idTowaru int references Towar(id),
95 opis text not null,
96 status smallint references Status(id)
97)
98GO
99
100--CONSTRAINTS
101
102alter table Towar
103add constraint towar_ilosc_check check (ilosc >= 0)
104GO
105alter table Towar
106add constraint towar_cenaNetto_check check (cenaNetto >= 0)
107GO
108alter table Towar
109add constraint towar_podatek_check check (podatek >= 0)
110GO
111alter table Zamowienie_Towar
112add constraint zamowienie_ilosc_check check (ilosc >= 0)
113GO
114alter table Zamowienie_Towar
115add constraint zamowienie_cena_check check (cena >= 0)
116GO
117-- INSERT
118
119insert into Rola (nazwa)
120values
121 ('administrator'),
122 ('klient'),
123 ('stalyKlient'),
124 ('moderator')
125GO
126
127insert into Uzytkownik(login, haslo, rola)
128values
129 ('JanNowak', 'poziomki', '4'),
130 ('EwaKowalska', 'kobylamamalybok', '1'),
131 ('AdamBajsztok', 'qwerty', '2'),
132 ('DanutaKos', 'ptak', '2'),
133 ('KatarzynaMaczek', 'general', '4')
134GO
135
136insert into Klient (imie, nazwisko, adres, telefon, email, idUzytkownika)
137values
138 ('Adam', 'Bajsztok', 'Poznań, os. Różany Potok 17', '841548143', 'AdamBajsztok@gmail.com', '3'),
139 ('Danuta', 'Kos', 'Wrocław, ul. Grunwaldzka 14/5', '741852963', 'DanKos@wp.pl', '4')
140GO
141
142insert into Status (nazwa)
143values
144 ('w trakcie'),
145 ('zakończono'),
146 ('przerwano')
147GO
148
149insert into Towar (producent, nazwa, ilosc, cenaNetto)
150values
151 ('Samsung', 'Galaxy A3 gray', 50, 600),
152 ('Smasung', 'Galaxy S7 white', 50, 2100),
153 ('Sony', 'Xperia Z5 black', 37, 1200),
154 ('LG', 'G5', 24, 1100 )
155GO
156
157insert into Zamowienie (idKlienta, dataZamowiena, dataRealizacji, wartosc, status)
158values
159 (1, '2016-12-01', null, 738, 1)
160GO
161
162select * from Rola
163select * from Klient
164select * from Uzytkownik
165select * from Status
166select * from Towar
167select * from Zamowienie
168GO
169
170-- TRIGGER
171
172CREATE TRIGGER trigger_usuwanieUzytkownika ON uzytkownik
173INSTEAD OF DELETE
174AS
175BEGIN
176 DECLARE @id int
177 SELECT @id = deleted.id FROM deleted
178 UPDATE uzytkownik SET czyAktywny = 'false' WHERE id=@id
179END
180GO
181
182CREATE TRIGGER trigger_usuwanieKlienta ON klient
183INSTEAD OF DELETE
184AS
185BEGIN
186 DECLARE @id int
187 SELECT @id = deleted.idUzytkownika FROM deleted
188 UPDATE uzytkownik SET czyAktywny = 'false' WHERE id=@id
189END
190GO
191
192-- PRCEDUER
193
194CREATE PROCEDURE klient_rejestracja
195 @login varchar(45),
196 @haslo varchar(45),
197 @imie varchar(50),
198 @nazwisko varchar(50),
199 @adres varchar(300),
200 @telefon varchar(11),
201 @email varchar(100)
202AS
203 DECLARE @id int
204
205 IF EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
206 RAISERROR('Taki login jest już zajęty!', 11, 1)
207 ELSE IF EXISTS (SELECT * FROM klient WHERE email = @email)
208 RAISERROR('Taki mail jest już w bazie!', 11, 2)
209 ELSE
210 BEGIN
211 INSERT INTO uzytkownik
212 VALUES(@login, @haslo, 'true', 2)
213
214 SELECT @id = Uzytkownik.id FROM Uzytkownik
215
216 INSERT INTO klient
217 VALUES(@imie, @nazwisko, @adres, @telefon, @email, @id)
218 END
219GO
220
221CREATE PROCEDURE klient_usuwanie
222 @id int = NULL,
223 @login varchar(50) = NULL
224AS
225 IF @id IS NOT NULL
226 IF @login IS NOT NULL
227 RAISERROR('Podaj jeden parametr!', 11, 3)
228
229 IF @id IS NOT NULL BEGIN
230 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE id = @id)
231 RAISERROR('Taki użytkownik nie istnieje!', 11, 3)
232 ELSE IF EXISTS (SELECT * FROM uzytkownik WHERE id = @id AND czyAktywny = 'false')
233 RAISERROR('Ten uzytkownik jest już nieaktywny!', 11, 1)
234 ELSE BEGIN
235 UPDATE uzytkownik
236 SET czyAktywny = 'false'
237 WHERE id = @id
238 END
239 END
240
241 IF @login IS NOT NULL BEGIN
242 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
243 RAISERROR('Taki użytkownik nie istnieje!', 11, 3)
244 ELSE IF EXISTS (SELECT * FROM uzytkownik WHERE login = @login AND czyAktywny = 'false')
245 RAISERROR('Ten uzytkownik jest już nieaktywny!', 11, 1)
246 ELSE BEGIN
247 UPDATE uzytkownik
248 SET czyAktywny = 'false'
249 WHERE login = @login
250 END
251 END
252
253 IF @id IS NULL
254 IF @login IS NULL
255 RAISERROR('Podaj jeden parametr!', 11, 3)
256GO
257
258CREATE PROCEDURE klient_przywracanie
259 @id int = NULL,
260 @login varchar(50) = NULL
261AS
262 IF @id IS NOT NULL
263 IF @login IS NOT NULL
264 RAISERROR('Podaj jeden parametr!', 11, 3)
265
266 IF @id IS NOT NULL BEGIN
267 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE id = @id)
268 RAISERROR('Taki użytkownik nie istnieje!', 11, 3)
269 ELSE IF EXISTS (SELECT * FROM uzytkownik WHERE id = @id AND czyAktywny = 'true')
270 RAISERROR('Ten uzytkownik jest już aktywny!', 11, 1)
271 ELSE BEGIN
272 UPDATE uzytkownik
273 SET czyAktywny = 'true'
274 WHERE id = @id
275 END
276 END
277
278 IF @login IS NOT NULL BEGIN
279 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
280 RAISERROR('Taki użytkownik nie istnieje!', 11, 3)
281 ELSE IF EXISTS (SELECT * FROM uzytkownik WHERE login = @login AND czyAktywny = 'true')
282 RAISERROR('Ten uzytkownik jest już aktywny!', 11, 1)
283 ELSE BEGIN
284 UPDATE uzytkownik
285 SET czyAktywny = 'true'
286 WHERE login = @login
287 END
288 END
289
290 IF @id IS NULL
291 IF @login IS NULL
292 RAISERROR('Podaj jeden parametr!', 11, 3)
293GO
294
295CREATE PROCEDURE klient_zmienImie
296 @id int = NULL,
297 @login varchar(50) = NULL,
298 @imie varchar(50)
299AS
300 IF @id IS NOT NULL
301 IF @login IS NOT NULL
302 RAISERROR('Podaj jeden parametr!', 11, 3)
303
304 IF @id IS NOT NULL BEGIN
305 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
306 RAISERROR('Taki klient nie istnieje!', 11, 3)
307 ELSE BEGIN
308 UPDATE klient
309 SET imie = @imie
310 WHERE id = @id
311 END
312 END
313
314 IF @login IS NOT NULL BEGIN
315 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
316 RAISERROR('Taki klient nie istnieje!', 11, 3)
317 ELSE BEGIN
318 DECLARE @idU int
319 SELECT @idU = Uzytkownik.id FROM Uzytkownik WHERE login = @login
320
321 UPDATE klient
322 SET imie = @imie
323 WHERE id = @idU
324 END
325 END
326
327 IF @id IS NULL
328 IF @login IS NULL
329 RAISERROR('Podaj jeden parametr!', 11, 3)
330GO
331
332CREATE PROCEDURE klient_zmienNazwisko
333 @id int = NULL,
334 @login varchar(50) = NULL,
335 @nazwisko varchar(50)
336AS
337 IF @id IS NOT NULL
338 IF @login IS NOT NULL
339 RAISERROR('Podaj jeden parametr!', 11, 3)
340
341 IF @id IS NOT NULL BEGIN
342 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
343 RAISERROR('Taki klient nie istnieje!', 11, 3)
344 ELSE BEGIN
345 UPDATE klient
346 SET nazwisko = @nazwisko
347 WHERE id = @id
348 END
349 END
350
351 IF @login IS NOT NULL BEGIN
352 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
353 RAISERROR('Taki klient nie istnieje!', 11, 3)
354 ELSE BEGIN
355 DECLARE @idU int
356 SELECT @idU = Uzytkownik.id FROM Uzytkownik WHERE login = @login
357
358 UPDATE klient
359 SET nazwisko = @nazwisko
360 WHERE id = @idU
361 END
362 END
363
364 IF @id IS NULL
365 IF @login IS NULL
366 RAISERROR('Podaj jeden parametr!', 11, 3)
367GO
368
369CREATE PROCEDURE klient_zmienAdres
370 @id int = NULL,
371 @login varchar(50) = NULL,
372 @adres varchar(300)
373AS
374 IF @id IS NOT NULL
375 IF @login IS NOT NULL
376 RAISERROR('Podaj jeden parametr!', 11, 3)
377
378 IF @id IS NOT NULL BEGIN
379 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
380 RAISERROR('Taki klient nie istnieje!', 11, 3)
381 ELSE BEGIN
382 UPDATE klient
383 SET adres = @adres
384 WHERE id = @id
385 END
386 END
387
388 IF @login IS NOT NULL BEGIN
389 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
390 RAISERROR('Taki klient nie istnieje!', 11, 3)
391 ELSE BEGIN
392 DECLARE @idU int
393 SELECT @idU = Uzytkownik.id FROM Uzytkownik WHERE login = @login
394
395 UPDATE klient
396 SET adres = @adres
397 WHERE id = @idU
398 END
399 END
400
401 IF @id IS NULL
402 IF @login IS NULL
403 RAISERROR('Podaj jeden parametr!', 11, 3)
404GO
405
406CREATE PROCEDURE klient_zmienTelefon
407 @id int = NULL,
408 @login varchar(50) = NULL,
409 @telefon varchar(11)
410AS
411 IF @id IS NOT NULL
412 IF @login IS NOT NULL
413 RAISERROR('Podaj jeden parametr!', 11, 3)
414
415 IF @id IS NOT NULL BEGIN
416 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
417 RAISERROR('Taki klient nie istnieje!', 11, 3)
418 ELSE BEGIN
419 UPDATE klient
420 SET telefon = @telefon
421 WHERE id = @id
422 END
423 END
424
425 IF @login IS NOT NULL BEGIN
426 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
427 RAISERROR('Taki klient nie istnieje!', 11, 3)
428 ELSE BEGIN
429 DECLARE @idU int
430 SELECT @idU = Uzytkownik.id FROM Uzytkownik WHERE login = @login
431
432 UPDATE klient
433 SET telefon = @telefon
434 WHERE id = @idU
435 END
436 END
437
438 IF @id IS NULL
439 IF @login IS NULL
440 RAISERROR('Podaj jeden parametr!', 11, 3)
441GO
442
443CREATE PROCEDURE klient_zmienMail
444 @id int = NULL,
445 @login varchar(50) = NULL,
446 @email varchar(100)
447AS
448 IF @id IS NOT NULL
449 IF @login IS NOT NULL
450 RAISERROR('Podaj jeden parametr!', 11, 3)
451
452 IF @id IS NOT NULL BEGIN
453 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
454 RAISERROR('Taki klient nie istnieje!', 11, 3)
455 ELSE BEGIN
456 UPDATE klient
457 SET email = @email
458 WHERE id = @id
459 END
460 END
461
462 IF @login IS NOT NULL BEGIN
463 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
464 RAISERROR('Taki klient nie istnieje!', 11, 3)
465 ELSE BEGIN
466 DECLARE @idU int
467 SELECT @idU = Uzytkownik.id FROM Uzytkownik WHERE login = @login
468
469 UPDATE klient
470 SET email = @email
471 WHERE id = @idU
472 END
473 END
474
475 IF @id IS NULL
476 IF @login IS NULL
477 RAISERROR('Podaj jeden parametr!', 11, 3)
478GO
479
480CREATE PROCEDURE klient_awansuj
481 @id int = NULL,
482 @login varchar(50) = NULL
483AS
484 IF @id IS NOT NULL
485 IF @login IS NOT NULL
486 RAISERROR('Podaj jeden parametr!', 11, 3)
487
488 IF @id IS NOT NULL BEGIN
489 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
490 RAISERROR('Taki klient nie istnieje!', 11, 3)
491 ELSE BEGIN
492 DECLARE @idU int
493 SELECT @idU = Klient.idUzytkownika FROM Klient WHERE id = @id
494
495 UPDATE uzytkownik
496 SET rola = 3
497 WHERE id = @idU
498 END
499 END
500
501 IF @login IS NOT NULL BEGIN
502 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
503 RAISERROR('Taki klient nie istnieje!', 11, 3)
504 ELSE BEGIN
505 UPDATE uzytkownik
506 SET rola = 3
507 WHERE login = @login
508 END
509 END
510
511 IF @id IS NULL
512 IF @login IS NULL
513 RAISERROR('Podaj jeden parametr!', 11, 3)
514GO
515
516CREATE PROCEDURE klient_degraduj
517 @id int = NULL,
518 @login varchar(50) = NULL
519AS
520 IF @id IS NOT NULL
521 IF @login IS NOT NULL
522 RAISERROR('Podaj jeden parametr!', 11, 3)
523
524 IF @id IS NOT NULL BEGIN
525 IF NOT EXISTS (SELECT * FROM klient WHERE id = @id)
526 RAISERROR('Taki klient nie istnieje!', 11, 3)
527 ELSE BEGIN
528 DECLARE @idU int
529 SELECT @idU = Klient.idUzytkownika FROM Klient WHERE id = @id
530
531 UPDATE uzytkownik
532 SET rola = 2
533 WHERE id = @idU
534 END
535 END
536
537 IF @login IS NOT NULL BEGIN
538 IF NOT EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
539 RAISERROR('Taki klient nie istnieje!', 11, 3)
540 ELSE BEGIN
541 UPDATE uzytkownik
542 SET rola = 2
543 WHERE login = @login
544 END
545 END
546
547 IF @id IS NULL
548 IF @login IS NULL
549 RAISERROR('Podaj jeden parametr!', 11, 3)
550GO
551
552----
553
554CREATE PROCEDURE administrator_dodawanie
555 @login varchar(45),
556 @haslo varchar(45)
557AS
558 IF EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
559 RAISERROR('Taki login jest już zajęty!', 11, 1)
560 ELSE
561 BEGIN
562 INSERT INTO uzytkownik
563 VALUES(@login, @haslo, 'true', 1)
564 END
565GO
566
567----
568
569CREATE PROCEDURE moderator_dodawanie
570 @login varchar(45),
571 @haslo varchar(45)
572AS
573 IF EXISTS (SELECT * FROM uzytkownik WHERE login = @login)
574 RAISERROR('Taki login jest już zajęty!', 11, 1)
575 ELSE
576 BEGIN
577 INSERT INTO uzytkownik
578 VALUES(@login, @haslo, 'true', 4)
579 END
580GO
581
582----
583
584CREATE FUNCTION klient_lista(@czyAktywny bit)
585RETURNS TABLE
586AS
587RETURN (SELECT u.login, k.imie, k.nazwisko, k.adres, k.telefon, k.email FROM Klient k
588 JOIN Uzytkownik u on u.id = k.idUzytkownika
589 WHERE u.czyAktywny = @czyAktywny)
590GO
591
592----
593
594CREATE PROCEDURE towar_dodawanieNowego
595 @producent varchar(100),
596 @nazwa varchar(100),
597 @ilosc int,
598 @cenaNetto money
599AS
600 IF EXISTS (SELECT * FROM towar WHERE nazwa = @nazwa)
601 RAISERROR('Taki towar jest już w bazie!', 11, 1)
602 ELSE IF @ilosc <0
603 RAISERROR('Ilosc nie moze byc mniejsza od 0!', 11, 1)
604 ELSE IF @cenaNetto <0
605 RAISERROR('Cena nie moze byc ujemna!', 11, 1)
606 ELSE
607 BEGIN
608 INSERT INTO towar(producent, nazwa, ilosc, cenaNetto)
609 VALUES(@producent, @nazwa, @ilosc, @cenaNetto)
610 END
611GO
612
613----
614
615CREATE FUNCTION towar_producent(@producent varchar(100))
616RETURNS TABLE
617AS
618RETURN (SELECT t.id, t.producent, t.nazwa, t.ilosc, t.cenaNetto, t.cenaBrutto FROM Towar t
619 WHERE t.producent = @producent)
620GO
621
622CREATE FUNCTION towar_mniejNiz(@ilosc int)
623RETURNS TABLE
624AS
625RETURN (SELECT t.id, t.producent, t.nazwa, t.ilosc, t.cenaNetto, t.cenaBrutto FROM Towar t
626 WHERE t.ilosc < @ilosc)
627GO
628
629CREATE FUNCTION towar_wiecejNiz(@ilosc int)
630RETURNS TABLE
631AS
632RETURN (SELECT t.id, t.producent, t.nazwa, t.ilosc, t.cenaNetto, t.cenaBrutto FROM Towar t
633 WHERE t.ilosc > @ilosc)
634GO
635
636CREATE FUNCTION towar_tanszyNiz(@cenaBrutto money)
637RETURNS TABLE
638AS
639RETURN (SELECT t.id, t.producent, t.nazwa, t.ilosc, t.cenaNetto, t.cenaBrutto FROM Towar t
640 WHERE t.cenaBrutto < @cenaBrutto)
641GO
642
643CREATE FUNCTION towar_drozszyNiz(@cenaBrutto money)
644RETURNS TABLE
645AS
646RETURN (SELECT t.id, t.producent, t.nazwa, t.ilosc, t.cenaNetto, t.cenaBrutto FROM Towar t
647 WHERE t.cenaBrutto > @cenaBrutto)
648GO