· 8 years ago · Jul 03, 2018, 03:28 PM
1-------- USUŃ TABELE --------
2
3IF OBJECT_ID('Sprzedaz', 'U') IS NOT NULL
4 DROP TABLE Sprzedaz;
5IF OBJECT_ID('Egzemplarze', 'U') IS NOT NULL
6 DROP TABLE Egzemplarze;
7IF OBJECT_ID('Faktury', 'U') IS NOT NULL
8 DROP TABLE Faktury;
9IF OBJECT_ID('Produkty', 'U') IS NOT NULL
10 DROP TABLE Produkty;
11IF OBJECT_ID('Klienci', 'U') IS NOT NULL
12 DROP TABLE Klienci;
13IF OBJECT_ID('Pracownicy', 'U') IS NOT NULL
14 DROP TABLE Pracownicy;
15IF OBJECT_ID('Sklepy', 'U') IS NOT NULL
16 DROP TABLE Sklepy;
17GO
18
19--------- CREATE - UTWÓRZ TABELE I POWIĄZANIA --------
20
21CREATE TABLE Klienci
22(
23imie VARCHAR(50) NOT NULL CHECK (imie LIKE '[A-Z]%'),
24nazwisko VARCHAR(50) NOT NULL CHECK (nazwisko LIKE '[A-Z]%'),
25adres VARCHAR(100),
26telefon VARCHAR(9) CHECK (len(telefon) = 9 and ISNUMERIC(telefon) = 1),
27pesel VARCHAR(11) NOT NULL PRIMARY KEY,
28nr_dowodu VARCHAR(10) NOT NULL UNIQUE,
29e_mail VARCHAR(50) CHECK (e_mail LIKE '%@%')
30);
31
32CREATE TABLE Sklepy
33(
34id_sklep INT IDENTITY(1000, 1) PRIMARY KEY,
35nazwa VARCHAR(50) NOT NULL UNIQUE,
36wlasciciel VARCHAR(50) NOT NULL CHECK(wlasciciel LIKE '[A-Z]%'),
37adres VARCHAR(100),
38nr_telefonu VARCHAR(9) CHECK (len(nr_telefonu) = 9 AND ISNUMERIC(nr_telefonu) = 1),
39);
40CREATE TABLE Produkty
41(
42id_prod INT IDENTITY(1, 1) PRIMARY KEY,
43brand VARCHAR(30) NOT NULL,
44rodzaj VARCHAR(30),
45jakosc VARCHAR (30) CHECK (jakosc IN ('Basic', 'Premium')),
46kolekcja VARCHAR(30),
47);
48
49CREATE TABLE Pracownicy
50(
51id_prac INT IDENTITY(100, 1) PRIMARY KEY,
52imie VARCHAR(30) NOT NULL CHECK (imie LIKE '[A-Z]%'),
53nazwisko VARCHAR(30) NOT NULL CHECK (nazwisko LIKE '[A-Z]%'),
54sklep INT REFERENCES Sklepy(id_sklep),
55stanowisko VARCHAR(30) CHECK (stanowisko LIKE '[A-Z]%'),
56);
57
58
59CREATE TABLE Egzemplarze
60(
61produkt INT REFERENCES Produkty(id_prod),
62id_egz INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
63rozmiar VARCHAR(10),
64cena MONEY,
65sklep INT REFERENCES Sklepy(id_sklep),
66statusEgz VARCHAR(20) CHECK (StatusEgz IN ('Brak','Sprzedana', 'Na_Sprzedaż')),
67);
68
69CREATE TABLE Faktury
70(
71id_faktury INT IDENTITY(1000, 1) PRIMARY KEY,
72klient VARCHAR(11) REFERENCES Klienci(pesel),
73sklep INT REFERENCES Sklepy(id_sklep),
74statusFakt VARCHAR(30) check (statusFakt IN ('Zaplacona', 'Oczekujaca')) DEFAULT 'Oczekujaca',
75rodzaj_platnosci VARCHAR(30) CHECK (rodzaj_platnosci IN ('Gotowka','Karta','Przelew')),
76kwota MONEY,
77);
78
79CREATE TABLE Sprzedaz
80(
81id_zak INT IDENTITY(1, 1) PRIMARY KEY,
82klient VARCHAR(11) REFERENCES Klienci(pesel),
83pracownik INT REFERENCES Pracownicy(id_prac),
84dataZam DATE NOT NULL,
85egzemplarz INT UNIQUE REFERENCES Egzemplarze(id_egz),
86faktura INT REFERENCES Faktury(id_faktury),
87zwrot VARCHAR(10) DEFAULT NULL
88);
89
90GO
91---------- TRIGGERS -----------
92
93DROP TRIGGER IF EXISTS dbo.Zmiana_nazwy_sklepu
94GO
95--WYZWALACZ OBSÅUGUJÄ„CY BRAK MOÅ»LIWOÅšCI ZMIANY NAZWY SKLEPU
96
97CREATE OR ALTER TRIGGER Zmiana_nazwy_sklepu on Sklepy
98INSTEAD OF UPDATE
99AS
100BEGIN
101 PRINT 'Nie wolno zmieniać nazwy sklepu'
102END
103
104--EXEC Modyfikuj_Sklep '1000', 'Sklep', null, 'ul. ÅšlÄ…ska 3 Warszawa', '123456789'
105--SELECT * FROM Sklepy
106
107
108DROP TRIGGER IF EXISTS dbo.Zmiana_Ceny
109GO
110--WYZWALACZ OBSÅUGUJÄ„CY ZMIANĘ CENY WSZYSTKICH EGEMPLARZY DANEGO PRODUKTU
111
112CREATE OR ALTER TRIGGER Zmiana_Ceny on Egzemplarze
113AFTER update
114AS
115BEGIN
116 UPDATE Egzemplarze
117 SET cena = inserted.cena
118 FROM Egzemplarze
119 JOIN inserted on Egzemplarze.produkt = inserted.produkt
120 WHERE Egzemplarze.produkt = inserted.produkt
121END
122GO
123
124--UPDATE Egzemplarze SET cena = cena+1 WHERE id_egz='1'
125--SELECT * FROM Egzemplarze
126
127
128DROP TRIGGER IF EXISTS dbo.Zly_Rozmiar
129GO
130--WYZWALACZ OBSÅUGUJÄ„CY BÅĘDNIE WPROWADZONY ROZMIAR
131
132CREATE OR ALTER TRIGGER zly_rozmiar ON Egzemplarze
133AFTER INSERT
134AS
135BEGIN
136 DECLARE @x VARCHAR(50);
137 SET @x = (SELECT inserted.rozmiar FROM inserted)
138 IF @x NOT IN ('36','37','38','39','40','41','42','43','44','45','46','47', 'XXS','XS','S','M','L','XL','XXL','XXXL')
139 BEGIN
140 PRINT 'Błędny rozmiar'
141 ROLLBACK
142 END
143END
144GO
145
146
147/*BEGIN TRY
148 exec Nowy_Egzemplarz '5', 'XL', 299, '1000', 'Na_Sprzedaż'
149END TRY
150BEGIN CATCH
151 SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;
152END CATCH
153
154
155SELECT * FROM Egzemplarze*/
156
157GO
158
159---------- INSERT - WSTAW DANE --------
160
161INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Zara', 'Sukienka','Premium', '2017/18');
162INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Louis Vuitton','Bluza','Premium','2016/17');
163INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('New Look','Spodnie','Basic','2016/17');
164INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Zara', 'Koszulka', 'Basic', '2017/18');
165INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('New Look', 'Buty', 'Basic', '2016/17');
166INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Adidas', 'Buty', 'Premium', '2016/17');
167INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Louis Vuitton', 'Buty', 'Premium', '2017/18');
168INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Louis Vuitton', 'Sukienka', 'Premium', '2016/17');
169INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Zara', 'Spódnica', 'Basic', '2016/17');
170INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Nike', 'Buty', 'Premium', '2017/18');
171INSERT INTO Produkty(brand, rodzaj,jakosc, kolekcja) VALUES('Nike', 'Spodnie', 'Premium', '2017/18');
172
173
174INSERT INTO Klienci VALUES('Adam','Kowalski','ul. Skwer Armii Krajowej 15 Białystok','606605403','80092918799','AXX884453','akow@wp.pl');
175INSERT INTO Klienci VALUES('Stanisław','Włodarczyk','ul. Poznańska 119 Białystok','722043300','01302418195','ARE765348','stanwlo@wp.pl');
176INSERT INTO Klienci VALUES('Jan','Małek','ul. Kobylińskiego Stanisława 4 Zabrze','534841441','46051406075','AJW234491','janmalek@wp.pl');
177INSERT INTO Klienci VALUES('Antonina', 'Czajkowska', 'ul. Generała Połbina Iwana 19 Wrocław', '699660585', '59121505033','ANJ701699','antczaj@op.pl');
178INSERT INTO Klienci VALUES('Aleksandra', 'Polak', 'ul. Cygańska 54 Białystok', '783330106', '41052312958','AVX908782','alepol@op.pl');
179INSERT INTO Klienci VALUES('Kacper', 'Jabłoński', 'ul. Malinowa 33 Warszawa/Wesoła', '793238982', '59112915865','AYP838910','kacjab@op.pl');
180INSERT INTO Klienci VALUES('Karolina', 'Makowska', 'ul. Kołłątaja Hugona 3 Zabrze', '690932534', '16271705908','AGE240314','karmak@op.pl');
181INSERT INTO Klienci VALUES('Julia', 'Zielińska', 'ul. Zdrowa 106 Kraków', '783369973', '11291911342','AAZ753305','julziel@op.pl');
182
183INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Modna szafa','Maja Kacprzak','ul. Różana 115 Siemianowice Śląskie','662425008');
184INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Ciuszki','Mikołaj Chojnacki','ul. Pozdawilska 56 Szczecin','884049565');
185INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('U Moniki','Pola Jakubowska','ul. Lindego Samuela Bogumiła 127 Warszawa','780695582');
186INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Ubrania','MikoÅ‚aj MaÅ‚ecki','ul. Kolumny 102 Åódź','666352480');
187INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Åšwiat mody','Patryk Wojciechowski','ul. Kwiatowa 149 Åódź','795209928');
188INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Modnie','Natalia Grabowska','ul. Swarożyca 87 Kraków','726954971');
189INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Na rogu','Martyna Brzozowska','ul. Kawaleryjska 107 Lublin','739337168');
190INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Ubranko','Magdalena Kowalczyk','ul. Kielecka 81 Bydgoszcz','780190932');
191INSERT INTO Sklepy(nazwa, wlasciciel, adres, nr_telefonu) VALUES('Pod schodami','Monika Kędzierska','ul. Kochanowskiego Jana 40 Jastrzębie-Zdrój','782283071');
192
193INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Jakub','Wolski',1001,'Sprzedawca');
194INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Adam','Cieślak',1000,'Sprzedawca');
195INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Piotr','Małek',1000,'Kierownik');
196INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Antoni', 'Krawczyk',1003, 'Kierownik');
197INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Adrian', 'Kowalski',1000, 'Sprzedawca');
198INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Barbara', 'Leszczyńska',1001, 'Kierownik');
199INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Oliwia', 'Nowak',1002, 'Kierownik');
200INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko) VALUES('Patrycja', 'Mazurkiewicz',1003, 'Sprzedawca');
201
202INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('80092918799','1001','Zaplacona','Gotowka',233);
203INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('80092918799','1002','Zaplacona','Karta',350);
204INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('11291911342','1003','Oczekujaca','Przelew',499);
205INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('41052312958','1000', 'Oczekujaca','Gotowka',500);
206INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('11291911342','1003', 'Zaplacona','Karta',170);
207INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('41052312958','1000', 'Oczekujaca','Gotowka',358);
208INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('59121505033','1002', 'Zaplacona','Przelew',654);
209INSERT INTO Faktury(klient, sklep, statusFakt, rodzaj_platnosci, kwota) VALUES('46051406075','1002', 'Oczekujaca','Karta',255);
210
211INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('1','XL',233,'1000', 'Na_Sprzedaż');
212INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('2','L',150,'1002','Sprzedana');
213INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('3','38',350,'1003','Sprzedana');
214INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('4','M',499,'1001','Na_Sprzedaż');
215INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('4','S',499,'1003','Sprzedana');
216INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('6','40',299,'1005','Na_Sprzedaż');
217INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('2','L',150,'1006','Sprzedana');
218INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('7','38',155,'1002','Sprzedana');
219INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('9','39',244,'1002','Sprzedana');
220INSERT INTO Egzemplarze(produkt, rozmiar, cena, sklep, statusEgz) VALUES('1','M',233,'1001','Sprzedana');
221
222INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('80092918799','100','2012-02-03', '2', '1000');
223INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('41052312958','102','2002-07-04', '3', '1003');
224INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('46051406075','101','2010-12-09', '5', '1002');
225INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('11291911342','103','2012-08-17', '7', '1004');
226INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('46051406075','105','2012-08-03', '8', '1000');
227INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('01302418195','103','2012-08-03', '9', '1000');
228INSERT INTO Sprzedaz(klient, pracownik, dataZam, egzemplarz, faktura) VALUES('11291911342','104','2012-08-03', '10', '1000');
229
230------------ SELECT --------
231
232SELECT * FROM Produkty
233SELECT * FROM Egzemplarze
234SELECT * FROM Sprzedaz
235SELECT * FROM Klienci
236SELECT * FROM Pracownicy
237SELECT * FROM Sklepy
238SELECT * FROM Faktury
239
240
241---------------- PROCEDURY ---------------------
242
243DROP PROCEDURE IF EXISTS dbo.Nowy_Produkt
244GO
245--DODANIE NOWEGO PRODUKTU
246CREATE OR ALTER PROCEDURE Nowy_Produkt
247 @brand VARCHAR(30),
248 @rodzaj VARCHAR(30),
249 @jakosc VARCHAR(30),
250 @kolekcja VARCHAR(30)
251
252AS
253BEGIN
254 IF NOT EXISTS (SELECT * FROM Produkty WHERE brand = @brand AND rodzaj = @rodzaj AND jakosc = @jakosc AND kolekcja = @kolekcja)
255 BEGIN
256 DECLARE @jak VARCHAR(30)
257 IF @jakosc IN ('Basic', 'Premium')
258 SET @jak = @jakosc
259 ELSE RAISERROR('Błędnie wprowadzona jakość produktu', 16, 1)
260
261 DECLARE @bran VARCHAR(30)
262 IF @brand IS NOT NULL
263 SET @bran = @brand
264 ELSE RAISERROR('Nazwa brandu nie może być pusta',16, 1)
265
266 INSERT INTO Produkty(brand,rodzaj,jakosc,kolekcja)
267 VALUES(@bran,@rodzaj,@jak,@kolekcja);
268 END
269 ELSE
270 PRINT 'Produkt już istnieje'
271END
272
273--EXEC Nowy_Produkt 'Hugo Boss' , 'Koszulka' ,'Premium', '2017/18'
274--EXEC Nowy_Produkt 'Reserved' , 'Koszuka', 'Basic', '2017/18'
275--SELECT * FROM Produkty
276
277DROP PROCEDURE IF EXISTS bdo.Modyfikuj_Produkt
278GO
279--MODYFIKACJA PRODUKTU
280
281CREATE OR ALTER PROCEDURE dbo.Modyfikuj_Produkt
282 @id INT,
283 @brand VARCHAR(30),
284 @rodzaj VARCHAR(30),
285 @jakosc VARCHAR(30),
286 @kolekcja VARCHAR(30)
287AS
288BEGIN
289 IF EXISTS (SELECT * FROM Produkty WHERE id_prod = @id)
290 BEGIN
291 UPDATE Produkty
292 SET brand = isnull(@brand, brand),
293 rodzaj = isnull(@rodzaj, rodzaj),
294 jakosc = isnull(@jakosc, jakosc),
295 kolekcja = isnull(@kolekcja, kolekcja)
296 WHERE id_prod = @id
297 END
298 ELSE
299 PRINT 'Nie ma takiego produktu'
300END
301
302--EXEC Modyfikuj_Produkt 1, 'Hugo Boss' , 'Koszulka' ,'Premium', '2017/18'
303--EXEC Modyfikuj_Produkt 20, 'Reserved' , 'Koszuka', 'Basic', '2017/18'
304--SELECT * FROM Produkty
305
306DROP PROCEDURE IF EXISTS dbo.Nowy_Egzemplarz
307GO
308--DODANIE EGZEMPLARZA
309
310CREATE OR ALTER PROCEDURE Nowy_Egzemplarz
311 @produkt INT,
312 @rozmiar VARCHAR(5),
313 @cena MONEY,
314 @sklep INT,
315 @status VARCHAR(20)
316AS
317BEGIN
318 DECLARE @prod INT
319 IF EXISTS (SELECT * FROM Produkty WHERE id_prod = @produkt )
320 SET @prod =(SELECT id_prod FROM Produkty WHERE id_prod = @produkt)
321 ELSE RAISERROR('Nie ma takiego produktu',16,1)
322
323 DECLARE @skl INT
324 IF EXISTS (SELECT * FROM Sklepy WHERE id_sklep = @Sklep)
325 SET @skl =(SELECT id_sklep FROM Sklepy WHERE id_sklep = @Sklep)
326 ELSE RAISERROR('Nie ma takiego Sklepu',16,1)
327
328 DECLARE @Sta VARCHAR(20)
329 IF @Status in ('Brak','Sprzedana', 'Na_Sprzedaż')
330 SET @sta= @Status
331 ELSE RAISERROR('ZÅ‚y status',16,1)
332
333 INSERT INTO Egzemplarze(produkt, rozmiar,cena, sklep, statusEgz)
334 VALUES(@prod,@rozmiar,@cena,@skl,@sta)
335END
336
337/*BEGIN TRY
338 EXEC Nowy_Egzemplarz '5', 'w', 299, '1000', 'Na_Sprzedaż'
339END TRY
340BEGIN CATCH
341 SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;
342END CATCH
343
344SELECT * FROM Egzemplarze*/
345
346DROP PROCEDURE IF EXISTS dbo.Nowy_Klient
347GO
348--DODANIE KLIENTA
349
350CREATE OR ALTER PROCEDURE Nowy_Klient
351 @imie varchar(50),
352 @nazwisko varchar(50),
353 @adres varchar(100),
354 @tel VARCHAR(9),
355 @pes VARCHAR(11),
356 @nr_dowodu varchar(10),
357 @mail varchar(50)
358AS
359BEGIN
360
361 DECLARE @pesel VARCHAR(11)
362 IF LEN(@pes) = 11 and ISNUMERIC(@pes) = 1
363 SET @pesel = @pes
364 ELSE RAISERROR('Błędny nr pesel', 16, 1)
365
366 DECLARE @email VARCHAR(50)
367 IF @mail LIKE '%@%'
368 SET @email = @mail
369 ELSE RAISERROR('Błędny adres e-mail',16,1)
370
371 DECLARE @dow VARCHAR(10)
372 IF NOT EXISTS (SELECT * FROM Klienci WHERE nr_dowodu = @nr_dowodu)
373 SET @dow = @nr_dowodu
374 ELSE RAISERROR('Podany nr dowodu jest już w bazie',16,1)
375
376 INSERT INTO Klienci(imie,nazwisko,adres,telefon,pesel,nr_dowodu,e_mail)
377 values(@imie,@nazwisko,@adres,@tel,@pesel,@dow,@email)
378END
379-----
380--EXEC Nowy_Klient 'Sebastian', 'Górski', 'ul. Świętego Tomasza 98 Kraków', '789566378', '46002814280', 'AZR429580', 'sebgor@wp.pl'
381--SELECT * FROM Klienci
382
383DROP PROCEDURE IF EXISTS dbo.Modyfikuj_Klient
384GO
385--MODYFIKACJA KLIENTA
386
387CREATE OR ALTER PROCEDURE Modyfikuj_Klient
388 @id varchar(11),
389 @imie varchar(50),
390 @nazwisko varchar(50),
391 @adres varchar(100),
392 @tel VARCHAR(9),
393 @nr_dowodu varchar(10),
394 @mail varchar(50)
395AS
396BEGIN
397 IF EXISTS (SELECT * FROM Klienci WHERE pesel = @id)
398 BEGIN
399
400 DECLARE @email VARCHAR(50)
401 IF @mail LIKE '%@%'
402 SET @email = @mail
403 ELSE RAISERROR('Błędny adres e-mail',16,1)
404
405 DECLARE @dow VARCHAR(10)
406 IF NOT EXISTS (SELECT * FROM Klienci WHERE nr_dowodu = @nr_dowodu)
407 SET @dow = @nr_dowodu
408 ELSE RAISERROR('Podany nr dowodu jest już w bazie',16,1)
409
410 UPDATE Klienci
411 SET pesel = isnull(@id, pesel),
412 imie = isnull(@imie, imie),
413 nazwisko = isnull(@nazwisko,nazwisko),
414 adres = isnull(@adres,adres),
415 telefon = isnull(@tel, telefon),
416 nr_dowodu = isnull(@nr_dowodu,nr_dowodu),
417 e_mail = isnull(@mail, e_mail)
418 WHERE pesel = @id
419 END
420 ELSE
421 PRINT 'Brak takiego klienta'
422END
423-----
424--EXEC Modyfikuj_Klient '01302418195', 'Mariusz', 'Górski', 'ul. Świętego Tomasza 98 Kraków', '789566378', 'AZR429580', 'sebgor@wp.pl'
425--SELECT * FROM Klienci
426
427DROP PROCEDURE IF EXISTS dbo.Nowy_Pracownik
428GO
429--DODANIE NOWEGO PRACOWNIKA
430
431CREATE OR ALTER PROCEDURE Nowy_Pracownik
432
433 @imie VARCHAR(30),
434 @nazwisko VARCHAR(30),
435 @sklep INT,
436 @stanowisko VARCHAR(30)
437AS
438BEGIN
439
440 DECLARE @skl INT
441 IF EXISTS (SELECT * FROM Sklepy WHERE id_sklep = @sklep )
442 SET @skl = (SELECT id_sklep FROM Sklepy WHERE id_sklep = @sklep)
443 ELSE RAISERROR('Taki sklep nie istnieje',16,1)
444
445 INSERT INTO Pracownicy(imie, nazwisko, sklep, stanowisko)
446 VALUES(@imie,@nazwisko,@skl,@stanowisko)
447END
448
449/*BEGIN TRY
450 EXEC Nowy_Pracownik 'Jan','Kurek',1000,'Szef'
451END TRY
452BEGIN CATCH
453 SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;
454END CATCH
455
456SELECT * FROM Pracownicy*/
457
458DROP PROCEDURE IF EXISTS dbo.Modyfikuj_Pracownik
459GO
460--MODYFIKACJA PRACOWNIKA
461
462CREATE OR ALTER PROCEDURE Modyfikuj_Pracownik
463 @id INT,
464 @imie VARCHAR(30),
465 @nazwisko VARCHAR(30),
466 @sklep INT,
467 @stanowisko VARCHAR(30)
468AS
469BEGIN
470 IF EXISTS(SELECT * FROM Pracownicy WHERE id_prac = @id)
471 BEGIN
472 DECLARE @skl INT
473 IF EXISTS (SELECT * FROM Sklepy WHERE id_sklep = @sklep )
474 SET @skl = (SELECT id_sklep FROM Sklepy WHERE id_sklep = @sklep)
475 ELSE RAISERROR('Taki sklep nie istnieje',16,1)
476
477 UPDATE Pracownicy
478 SET imie = isnull(@imie, imie),
479 nazwisko = isnull(@nazwisko, nazwisko),
480 sklep = isnull(@sklep, sklep),
481 stanowisko = isnull(@stanowisko, stanowisko)
482 WHERE id_prac = @id
483 END
484 ELSE
485 PRINT 'Brak takiego pracownika'
486END
487
488/*BEGIN TRY
489 EXEC Modyfikuj_Pracownik 103, 'Jan','Kurek',1000,'Szef'
490END TRY
491BEGIN CATCH
492 SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;
493END CATCH
494
495SELECT * FROM Pracownicy*/
496
497DROP PROCEDURE IF EXISTS dbo.Nowy_Sklep
498GO
499--DODANIE NOWEGO SKLEPU
500
501CREATE OR ALTER PROCEDURE Nowy_Sklep
502
503 @nazwa VARCHAR(50),
504 @wlasciciel VARCHAR(50),
505 @adres VARCHAR(100),
506 @nr_tel VARCHAR(20)
507AS
508BEGIN
509 DECLARE @naz VARCHAR(50)
510 IF NOT EXISTS (SELECT * FROM Sklepy WHERE nazwa = @nazwa)
511 SET @naz = @nazwa
512 ELSE RAISERROR('Podana nazwa istnieje już w bazie',16,1)
513
514 DECLARE @tel VARCHAR(20)
515 IF LEN(@nr_tel) = 9 AND ISNUMERIC(@nr_tel) = 1
516 SET @tel = @nr_tel
517 ELSE RAISERROR('Błędny nr telefonu',16,1)
518
519 INSERT INTO Sklepy(Nazwa, Wlasciciel,Adres, Nr_telefonu)
520 VALUES(@Nazwa, @Wlasciciel, @Adres, @Nr_tel)
521END
522----
523--EXEC Nowy_Sklep 'Twój KÄ…cik', 'Mateusz DÄ…browski', 'ul. Sierakowskiego Zygmunta 118 Åódź', '696082349'
524--SELECT * FROM Sklepy
525
526DROP PROCEDURE IF EXISTS dbo.Modyfikuj_Sklep
527GO
528--MODYFIKACJA SKLEPU
529
530CREATE OR ALTER PROCEDURE Modyfikuj_Sklep
531 @id INT,
532 @nazwa VARCHAR(20),
533 @wlasciciel VARCHAR(30),
534 @adres VARCHAR(30),
535 @nr_tel VARCHAR(11)
536AS
537BEGIN
538 UPDATE Sklepy
539 SET nazwa = isnull(@nazwa, nazwa),
540 wlasciciel = isnull(@wlasciciel, wlasciciel),
541 adres = isnull(@adres, adres),
542 nr_telefonu = isnull(@nr_tel, nr_telefonu)
543 WHERE id_Sklep = @id;
544END
545----
546--EXEC Modyfikuj_Sklep '1000', null, 'Maja Sosnowska', 'ul. ÅšlÄ…ska 3 Warszawa', '605456789'
547--SELECT * FROM Sklepy
548
549
550DROP PROCEDURE IF EXISTS dbo.Sprawdz_Egzemplarze
551GO
552--SPRAWDZENIE NIESPRZEDANYCH EGZEMPLARZY PRODUKTU
553
554CREATE OR ALTER PROCEDURE Sprawdz_Egzemplarze
555 @X INT
556AS
557BEGIN
558
559 IF EXISTS(
560 SELECT * FROM Egzemplarze WHERE Produkt=@x and StatusEgz != 'Sprzedana')
561 SELECT * FROM Egzemplarze WHERE Produkt=@x and StatusEgz != 'Sprzedana'
562 ELSE
563 PRINT 'Produkt niedostępny'
564END
565
566--EXEC Sprawdz_Egzemplarze '4'
567
568--SELECT * FROM Egzemplarze
569
570
571DROP PROCEDURE IF EXISTS dbo.Zwroc_Towar
572GO
573--ZWRACANIE TOWARU
574
575CREATE OR ALTER PROCEDURE Zwroc_Towar
576 @trans INT,
577 @sklep INT
578AS
579BEGIN
580 IF EXISTS (SELECT * FROM Sprzedaz WHERE id_zak = @trans)
581 BEGIN
582 DECLARE @skl INT = (
583 SELECT p.sklep
584 FROM Sprzedaz s
585 JOIN Pracownicy p on p.id_prac = s.pracownik
586 WHERE s.id_zak = @trans)
587
588 DECLARE @fakt INT = (
589 SELECT faktura
590 FROM Sprzedaz s
591 WHERE s.id_zak = @trans)
592
593 UPDATE Sprzedaz
594 SET zwrot = 'Zwrot'
595 WHERE id_zak = @trans
596
597 DECLARE @egz INT = (SELECT s.egzemplarz
598 FROM Sprzedaz s
599 WHERE s.faktura = @fakt)
600 UPDATE Egzemplarze
601 SET statusEgz = 'Na_Sprzedaż'
602 WHERE id_egz = @egz
603
604 IF @sklep IS NOT NULL
605 BEGIN
606 UPDATE Egzemplarze
607 SET sklep = @sklep
608 WHERE id_egz = @egz
609 END
610 END
611 ELSE PRINT 'Nie zarejestrowano takiej sprzedaży'
612END
613
614--EXEC Zwroc_Towar 4, 1000;
615
616--SELECT * FROM Sprzedaz
617--SELECT * FROM Sklepy
618--SELECT * FROM Faktury
619--SELECT * FROM Pracownicy
620--SELECT * FROM Egzemplarze
621
622
623DROP PROCEDURE IF EXISTS dbo.Dokonanie_sprzedazy
624GO
625--DOKONANIE SPRZEDAŻY PRODUKTU
626
627CREATE OR ALTER PROCEDURE Dokonanie_sprzedazy
628 @id_fakt INT,
629 @klient VARCHAR(11),
630 @sklep INT,
631 @platnosc VARCHAR(30),
632 @id_prac INT,
633 @id_egz INT,
634 @data DATE,
635 @rabat INT
636AS
637BEGIN
638
639 IF NOT EXISTS (SELECT * FROM Egzemplarze WHERE id_egz = @id_egz and statusEgz != 'Sprzedana')
640 BEGIN
641 RAISERROR ('BÅÄ„D',16,1)
642 END
643 ELSE
644 BEGIN
645 IF @sklep != (SELECT sklep FROM Egzemplarze WHERE id_egz = @id_egz)
646 BEGIN
647 RAISERROR('Danego egzemplarza nie ma w tym sklepie',16,1)
648 END
649 ELSE IF @platnosc != (SELECT rodzaj_platnosci FROM Faktury WHERE id_faktury = @id_fakt)
650 BEGIN
651 RAISERROR('Nie można łączyć różnych rodzajów płatności na jednej fakturze',16,1)
652 END
653 ELSE
654 BEGIN
655 DECLARE @kwota MONEY = (
656 SELECT e.cena
657 FROM Egzemplarze e
658 WHERE e.id_egz = @id_egz)
659 SET @kwota = @kwota - (@kwota * @rabat * 0.01)
660 END
661 BEGIN
662 IF EXISTS (SELECT * FROM Faktury WHERE id_faktury = @id_fakt)
663 BEGIN
664 IF @sklep = (SELECT sklep FROM Faktury WHERE id_faktury = @id_fakt)
665 BEGIN
666 UPDATE Faktury
667 SET kwota = kwota + @kwota
668 WHERE id_faktury = @id_fakt
669 END
670 ELSE
671 BEGIN
672 INSERT INTO Faktury(klient,sklep,rodzaj_platnosci,kwota) VALUES(@klient, @sklep, @platnosc, @kwota)
673 END
674
675 SET @kwota = (SELECT kwota FROM Faktury WHERE id_faktury = @id_fakt)
676 INSERT INTO Sprzedaz(klient,pracownik,dataZam,egzemplarz,faktura) VALUES (@klient,@id_prac,@data,@id_egz,(select id_faktury FROM Faktury WHERE klient = @klient and sklep = @sklep and rodzaj_platnosci = @platnosc and kwota = @kwota))
677 END
678 ELSE
679 BEGIN
680 INSERT INTO Faktury(klient,sklep,rodzaj_platnosci,kwota) VALUES(@klient, @sklep, @platnosc, @kwota)
681 INSERT INTO Sprzedaz(klient,pracownik,dataZam,egzemplarz,faktura) VALUES (@klient,@id_prac,@data,@id_egz,(select id_faktury FROM Faktury WHERE klient = @klient and sklep = @sklep and rodzaj_platnosci = @platnosc and kwota = @kwota))
682
683 UPDATE Egzemplarze SET statusEgz = 'Sprzedana' WHERE id_egz = @id_egz
684 PRINT 'Produkt sprzedany pomyślnie'
685 END
686 END
687 END
688END
689
690
691/*BEGIN TRY
692 EXEC Dokonanie_sprzedazy 1003, '80092918799', 1001,'Gotowka', 102, 4, '2018-01-20', '10'
693END TRY
694BEGIN CATCH
695 SELECT ERROR_NUMBER() AS ErrorNumber ,'Nie można dokonać sprzedaży, sprawdź dane' AS ErrorMessage;
696END CATCH
697
698select * from Faktury
699select * from Egzemplarze
700select * from Sprzedaz*/
701
702
703DROP FUNCTION IF EXISTS dbo.Ilosc_sprzedazy
704GO
705--FUNKCJA ZWRACAJĄCA ILOŚĆ SPRZEDAŻY DANEGO PRACOWNIKA W DANYM ROKU I MIESIĄCU
706
707CREATE OR ALTER FUNCTION Ilosc_sprzedazy
708(
709 @id INT,
710 @year INT,
711 @month INT
712)
713 RETURNS INT
714AS
715BEGIN
716 RETURN (SELECT COUNT(*) FROM Sprzedaz WHERE pracownik = @id AND YEAR(dataZam) = @year AND MONTH(dataZam) = @month)
717END
718GO
719
720--SELECT imie, nazwisko, dbo.Ilosc_sprzedazy(id_prac, 2012, 8) as ilosc_sprzedazy FROM Pracownicy;
721
722
723
724DROP FUNCTION IF EXISTS dbo.Klient_Zakupy
725GO
726--FUNKCJA SPRAWDZAJĄCA ILE ZAKUPÓW WYKONAŠDANY KLIENT
727
728CREATE OR ALTER FUNCTION Klient_Zakupy
729(
730 @s VARCHAR(11)
731)
732 RETURNS @nazwisko TABLE
733 (
734 imie VARCHAR(30),
735 nazwisko VARCHAR(30),
736 ilosc_zakupow INT
737 )
738AS
739BEGIN
740 IF EXISTS (SELECT * FROM Klienci WHERE pesel = @s)
741 BEGIN
742 INSERT INTO @nazwisko
743 SELECT imie, nazwisko, count(*) ilosc_zakupow FROM Klienci k join Sprzedaz s on s.klient = k.pesel WHERE Pesel = @s GROUP BY imie, nazwisko
744 END
745 ELSE
746 BEGIN
747 INSERT INTO @nazwisko VALUES('', '', '')
748 END
749 RETURN
750END
751GO
752--SELECT * FROM Klient_Zakupy('11291911342')
753
754
755
756DROP VIEW IF EXISTS dbo.Najlepsi_Sprzedawcy
757GO
758--WIDOK NAJLEPSZYCH SPRZEDAWCÓW
759
760CREATE OR ALTER VIEW Najlepsi_Sprzedawcy
761AS
762(
763 SELECT DISTINCT TOP 3 nazwisko, COUNT(pracownik) as Ilość_transakcji from Pracownicy join Sprzedaz ON Pracownik = id_prac
764 GROUP BY nazwisko
765 ORDER BY COUNT(pracownik) DESC
766);
767GO
768
769--SELECT * FROM Najlepsi_Sprzedawcy
770--SELECT * FROM Sprzedaz
771
772DROP VIEW IF EXISTS dbo.Najpopularniejsze_Produkty
773GO
774--WIDOK NAJPOPULARNIEJSZYCH PRODUKTÓW
775
776CREATE OR ALTER VIEW Najpopularniejsze_Produkty
777AS
778(
779 SELECT DISTINCT TOP 3 id_prod, brand, rodzaj, COUNT(*) as Ilość_transakcji from Produkty p
780 join Egzemplarze e ON e.produkt = p.id_prod
781 join Sprzedaz s on s.egzemplarz = e.id_egz
782 GROUP BY id_prod, brand, rodzaj
783 ORDER BY COUNT(*) DESC
784);
785GO
786
787--SELECT * FROM Najpopularniejsze_Produkty
788--SELECT * FROM Sprzedaz
789--SELECT * FROM Egzemplarze
790--SELECT * FROM Produkty