--BAZA DANYCH ZAMÓWIEŃ CERTYFIKATÓW--
CREATE DATABASE Obsluga_Zamowien_Certyfikatow;

GO

USE Obsluga_Zamowien_Certyfikatow;

GO

CREATE TABLE Dzialy
(id_dzialu INT IDENTITY(1,1) PRIMARY KEY,
nazwa_dzialu VARCHAR(20));

GO

CREATE TABLE Pracownicy
(id_pracownik INT IDENTITY(1,1) PRIMARY KEY,
imie_pracownika VARCHAR(20) NOT NULL,
nazwisko_pracownika VARCHAR(30) NOT NULL,
PESEL BIGINT UNIQUE NOT NULL,
id_dzialu INT REFERENCES Dzialy(id_dzialu));

GO

CREATE TABLE Klienci
(id_klient INT IDENTITY(1,1) PRIMARY KEY,
imie_klienta VARCHAR(20) NOT NULL,
nazwisko_klienta VARCHAR(30) NOT NULL,
ulica VARCHAR(100) NOT NULL,
kod_pocztowy VARCHAR(6) NOT NULL,
miasto VARCHAR(50) NOT NULL,
telefon INT UNIQUE NOT NULL,
e_mail VARCHAR(100) UNIQUE NOT NULL);

GO

CREATE TABLE Zamowienia
(id_zamowienia INT IDENTITY(1,1) PRIMARY KEY,
id_klient INT REFERENCES Klienci(id_klient),
status_zamowienia VARCHAR(50) DEFAULT 'W realizacji',
data_zamowienia DATE DEFAULT (GETDATE()),
planowana_data_realizacji DATE DEFAULT (DATEADD(DAY,+14,GETDATE())),
data_wyslania DATE,
kto_wyslal INT REFERENCES Pracownicy(id_pracownik));

GO

CREATE TABLE Certyfikaty
(id_certyfikat INT IDENTITY(1,1) PRIMARY KEY,
id_zamowienia INT REFERENCES Zamowienia(id_zamowienia),
typ VARCHAR(50));

GO


CREATE TABLE Certyfikaty_log
(id_certyfikaty_log INT IDENTITY(1,1) PRIMARY KEY,
id_certyfikat INT REFERENCES Certyfikaty(id_certyfikat),
id_pracownik INT REFERENCES Pracownicy(id_pracownik),
status_certyfikatu VARCHAR(50) DEFAULT 'PRZEKAZANY DO PRODUKCJI' CHECK(status_certyfikatu='PRZEKAZANY DO PRODUKCJI' OR status_certyfikatu='WYDRUKOWANY' OR status_certyfikatu ='OHOLOGRAMOWANY' OR status_certyfikatu ='PODPISANY' OR status_certyfikatu ='PRZYGOTOWANY DO WYSYŁKI' OR status_certyfikatu ='WYSŁANE' OR status_certyfikatu ='ANULOWANY'),
DATA DATETIME DEFAULT GETDATE());

GO



--PRZYKŁADOWE DANE--

USE Obsluga_Zamowien_Certyfikatow;

GO

INSERT INTO Dzialy (nazwa_dzialu)
VALUES
('DRUK'),
('HOLOGRAMOWANIE'),
('PODPISY'),
('PAKOWANIE'),
('SPEDYCJA');
GO

INSERT INTO Pracownicy (imie_pracownika, nazwisko_pracownika, PESEL, id_dzialu)
VALUES
('Damian', 'Krzesik', 94012502365, 1),
('Danuta', 'Polaczek', 86020306369, 4),
('Krzysztof', 'Turet', 89111405667, 2),
('Marian', 'Stoker', 780117078960, 3),
('Barbara', 'Mańczuk', 85061570789, 2),
('Aleksander', 'Szewc', 89020326532, 1),
('Daria', 'Mróz', 91020406326, 3),
('Piotr', 'Szczęk', 91031202698, 4),
('Daria', 'Mocna', 92111503652, 5);

GO

INSERT INTO Klienci (imie_klienta,nazwisko_klienta,ulica,kod_pocztowy,miasto,telefon,e_mail) VALUES
/*('Konrad','Tkacz','Połomskiego 4/16', '80-326','Gdańsk', 503698236, 'korad.tkacz@gmail.com'),*/
('Dorota','Zwada','Mściwoja 6', '52-200','Komorowice', 698642856, 'dorota.zwada@gmail.com'),
('Wojciech','Czart','Poznańska 56/3', '76-200','Słupsk', 768523695, 'wojciech.czart@gmail.com'),
('Damian','Ósmy','Wajdeloty 3', '80-437','Gdańsk', 659523655, 'damian.osmy@gmail.com'),
('Marzena','Mościcka','Jana Pawła II 43', '00-175','Warszawa', 563268958, 'marzena.moscicka@gmail.com'),
('Wiesław','Wszywka','Kołobrzeska 42', '80-394','Gdańsk', 503624857, 'wieslaw.wszywka@gmail.com');

GO

INSERT INTO Zamowienia (id_klient, status_zamowienia, data_zamowienia, planowana_data_realizacji, data_wyslania,kto_wyslal) VALUES
(1, 'WYSŁANE', '2017-05-20', '2017-06-03', '2017-06-02', 9),
(2, DEFAULT, '2017-06-01', '2017-06-15', NULL,NULL),
(3, DEFAULT, '2017-06-05', '2017-06-19', NULL,NULL),
(4, DEFAULT, '2017-06-10', '2017-06-24', NULL,NULL),
(5, DEFAULT, DEFAULT, DEFAULT, NULL,NULL);

GO

INSERT INTO Certyfikaty (id_zamowienia, typ) VALUES
(1, 'Szkoleniowy'),
(1, 'Szkoleniowy'),
(2, 'Jakościowy'),
(2, 'Jakościowy'),
(3, 'Szkoleniowy'),
(3, 'Jakościowy'),
(3, 'Szkoleniowy'),
(4, 'Szkoleniowy'),
(5, 'Jakościowy'),
(5, 'Jakościowy');

GO

INSERT INTO Certyfikaty_log (id_certyfikat, id_pracownik, status_certyfikatu, DATA) VALUES
(1, NULL, DEFAULT, '2017-05-20'),/*Zamowienie nr1*/
(1, 1, 'WYDRUKOWANY', '2017-05-21'),
(1, 3, 'OHOLOGRAMOWANY', '2017-05-22'),
(1, 4, 'PODPISANY', '2017-05-23'),
(1, 2, 'PRZYGOTOWANY DO WYSYŁKI', '2017-05-23'),
(1, 9, 'WYSŁANE', '2017-06-02'),
(2, NULL, DEFAULT, '2017-05-20'),
(2, 6, 'WYDRUKOWANY', '2017-05-21'),
(2, 5, 'OHOLOGRAMOWANY', '2017-05-22'),
(2, 7, 'PODPISANY', '2017-05-23'),
(2, 1, 'PRZYGOTOWANY DO WYSYŁKI', '2017-05-23'),
(2, 9, 'WYSŁANE', '2017-06-02'),
(3, NULL, DEFAULT, '2017-06-01'),/*Zamowienie nr2*/
(3, 1, 'WYDRUKOWANY', '2017-06-02'),
(3, 1, 'OHOLOGRAMOWANY', '2017-06-03'),
(3, 1, 'PODPISANY', '2017-06-04'),
(3, 1, 'PRZYGOTOWANY DO WYSYŁKI', '2017-06-05'),
(4, NULL, DEFAULT, '2017-06-01'),
(4, 1, 'WYDRUKOWANY', '2017-06-06'),
(4, 1, 'OHOLOGRAMOWANY', '2017-06-07'),
(4, 1, 'PODPISANY', '2017-06-08'),
(5, NULL, DEFAULT, '2017-06-05'), /*Zamowienie nr3*/
(5, 1, 'WYDRUKOWANY', '2017-06-06'),
(5, 3, 'OHOLOGRAMOWANY', '2017-06-07'),
(5, 4, 'PODPISANY', '2017-06-08'),
(5, 2, 'PRZYGOTOWANY DO WYSYŁKI', '2017-06-09'),
(6, NULL, DEFAULT, '2017-06-06'),
(6, 1, 'WYDRUKOWANY', '2017-06-07'),
(6, 5, 'OHOLOGRAMOWANY', '2017-06-09'),
(7, 6, 'WYDRUKOWANY', '2017-06-09'),
(8, NULL, DEFAULT, '2017-06-10'),/*Zamowienie nr4*/
(8, 1, 'WYDRUKOWANY', '2017-06-12'),
(9, NULL, DEFAULT, GETDATE()),/*Zamowienie nr5*/
(10, NULL, DEFAULT, GETDATE());

GO

--WYZWALACZE--

/*Wyzwalacz zmieniający status zlecenia gdy wyszystkie certyfikaty z zamówienia są gotowe do wysyłki*/

USE Obsluga_Zamowien_Certyfikatow;

GO
CREATE TRIGGER zmiana_statusu_zamowienia ON Certyfikaty_log
AFTER INSERT AS
BEGIN
IF (SELECT COUNT(c.id_zamowienia)
FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE c.id_zamowienia IN (SELECT TOP 1 c.id_zamowienia FROM Certyfikaty_log cl INNER JOIN Certyfikaty c
ON c.id_certyfikat=cl.id_certyfikat ORDER BY cl.id_certyfikaty_log DESC)
AND (cl.status_certyfikatu LIKE 'PRZYGOTOWANY DO WYSYŁKI'))>=
(SELECT COUNT(id_zamowienia) FROM Certyfikaty c
WHERE c.id_zamowienia IN (SELECT TOP 1 c.id_zamowienia FROM Certyfikaty_log cl INNER JOIN Certyfikaty c
ON c.id_certyfikat=cl.id_certyfikat ORDER BY cl.id_certyfikaty_log DESC))
BEGIN
UPDATE Zamowienia SET status_zamowienia=cl.status_certyfikatu
FROM Zamowienia z INNER JOIN Certyfikaty c ON z.id_zamowienia=c.id_zamowienia INNER JOIN Certyfikaty_log cl ON c.id_certyfikat=cl.id_certyfikat
WHERE cl.id_certyfikaty_log IN (SELECT MAX(id_certyfikaty_log) FROM Certyfikaty_log)
END
END;
GO
--PROCEDURY--

USE Obsluga_Zamowien_Certyfikatow;

GO

/*Procedura pokazująca opóźnione zamówienia*/
CREATE PROC opoznione_zamowienia
AS
SELECT
z.id_zamowienia, k.id_klient,k.imie_klienta,k.nazwisko_klienta,z.status_zamowienia, z.data_zamowienia, z.planowana_data_realizacji, DATEDIFF(dd,z.planowana_data_realizacji,GETDATE()) AS DNI_OPOZNIENIA
FROM Zamowienia z INNER JOIN Klienci k ON z.id_klient=k.id_klient
WHERE z.planowana_data_realizacji<= GETDATE() AND z.data_wyslania IS NULL;
GO


/*Procedura pokazująca wysłane zamowienia*/
CREATE PROC wyslane_zamowienia
AS
SELECT z.id_zamowienia,z.status_zamowienia,z.data_zamowienia,z.data_wyslania,(p.imie_pracownika+' '+p.nazwisko_pracownika) AS Kto_wyslal
FROM Zamowienia z INNER JOIN Pracownicy p ON z.kto_wyslal=p.id_pracownik
WHERE z.status_zamowienia='WYSŁANE'
ORDER BY z.id_zamowienia;
GO

/*Procedura pokazująca kto przygotowywał certyfikaty dla danego zamówienia*/
CREATE PROC kto_przygotowal @nr_zamowienia INT
AS
SELECT c.id_zamowienia, cl.id_certyfikat, c.typ, cl.status_certyfikatu, (p.imie_pracownika+' '+p.nazwisko_pracownika) AS 'Osoba odpowiedzialna',d.nazwa_dzialu AS Dział, cl.DATA
FROM Certyfikaty c INNER JOIN Certyfikaty_log cl ON c.id_certyfikat=cl.id_certyfikat INNER JOIN Pracownicy p ON cl.id_pracownik=p.id_pracownik INNER JOIN Dzialy d ON  p.id_pracownik=d.id_dzialu
WHERE c.id_zamowienia=@nr_zamowienia
ORDER BY c.id_certyfikat,cl.DATA;
GO

/*Procedura pokazująca certyfikaty gotowe do wysyłki*/
CREATE PROC gotowe_do_wysylki
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu = 'PRZYGOTOWANY DO WYSYŁKI' AND cl.status_certyfikatu NOT LIKE 'WYSŁANE';
GO

/*Procedura pokazująca które certyfikaty nie zostały przygotowane*/
CREATE PROC ktore_nieprzygotwane
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu NOT LIKE 'PRZYGOTOWANY DO WYSYŁKI' AND cl.status_certyfikatu NOT LIKE 'WYSŁANE';
GO

/*Procedura pokazująca certyfikaty do druku*/
CREATE PROC do_druku
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu LIKE 'PRZEKAZANY DO PRODUKCJI';
GO

/*Procedura pokazująca certyfikaty do hologramowania*/
CREATE PROC do_hologramowania
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu LIKE 'WYDRUKOWANY';
GO

/*Procedura pokazująca certyfikaty do podpisania*/
CREATE PROC do_podpisania
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu LIKE 'OHOLOGRAMOWANY';
GO

/*Procedura pokazująca certyfikaty do przygotowania do wysyłki*/
CREATE PROC na_przygotowanie_do_wysylki
AS
SELECT c.id_zamowienia AS Numer_zamówienia,
c.id_certyfikat AS Numer_Certyfikatu,
cl.status_certyfikatu AS Status_certyfikatu, cl.DATA

FROM (SELECT DISTINCT cl.id_certyfikat, MAX(DATA) DATA
FROM Certyfikaty_log cl GROUP BY cl.id_certyfikat) sort
INNER JOIN Certyfikaty_log cl ON sort.DATA=cl.DATA AND sort.id_certyfikat=cl.id_certyfikat
INNER JOIN Certyfikaty c ON cl.id_certyfikat=c.id_certyfikat
WHERE cl.status_certyfikatu LIKE 'PODPISANY';
GO

/*Wysłanie zamówienia do klienta*/
CREATE PROC wyslanie_zamowienia @id_zamówienia INT, @id_pracownik INT,  @data_wyslania DATE
AS

UPDATE Zamowienia SET status_zamowienia = 'WYSŁANE', kto_wyslal = @id_pracownik, data_wyslania=@data_wyslania
WHERE id_zamowienia=@id_zamówienia;

INSERT INTO Certyfikaty_log (id_certyfikat,id_pracownik,status_certyfikatu)
SELECT DISTINCT c.id_certyfikat,z.kto_wyslal,z.status_zamowienia
FROM Zamowienia z INNER JOIN Certyfikaty c ON z.id_zamowienia=c.id_zamowienia
INNER JOIN Certyfikaty_log cl ON c.id_certyfikat=cl.id_certyfikat
WHERE z.id_zamowienia=@id_zamówienia;
GO

/*Dodanie zapisu pracy*/
CREATE proc dodanie_pracy @id_certyfikatu INT,@id_pracownika INT, @STATUS VARCHAR(50)
AS
INSERT INTO Certyfikaty_log(id_certyfikat,id_pracownik,status_certyfikatu) VALUES
(@id_certyfikatu,@id_pracownika,@STATUS);
GO

/*anulowanie zamówienia*/
CREATE proc anuluj_zamowienie @id_zamowienia INT
AS
UPDATE Zamowienia SET status_zamowienia = 'ANULOWANE' WHERE id_zamowienia = @id_zamowienia

DECLARE @id_certyfikat INT
DECLARE cur CURSOR LOCAL FOR SELECT id_certyfikat FROM Certyfikaty WHERE id_zamowienia = @id_zamowienia
OPEN cur
fetch NEXT FROM cur INTO @id_certyfikat
while @@FETCH_STATUS = 0 BEGIN
    INSERT INTO Certyfikaty_log (id_certyfikat, id_pracownik, DATA, status_certyfikatu) VALUES (@id_certyfikat, NULL, GETDATE(), 'ANULOWANY')

    fetch NEXT FROM cur INTO @id_certyfikat
END
close cur
deallocate cur
GO