/*Iz baze podataka AdventureWorks2014 prikazati ukupnu
ostvarenu zaradu po zaposleniku na području Evrope u januaru mjesecu 2014. godine.
Lista treba da sadrži ime i prezime zaposlenika (spojeno), datum zaposlenja u formatu dd.mm.yyyy,
 mail adresu, te ukupnu ostvarenu zaradu
zaokruženu na dvije decimale. Podatke sortirati po zaradi opadajućim redoslijedom.*/

USE AdventureWorks2014

SELECT P.FirstName + ' ' + P.LastName, CONVERT(NVARCHAR,HireDate,104), PE.EmailAddress, ROUND(SUM(SOH.TotalDue),2) AS 'ZARADA'
FROM Person.Person AS P INNER JOIN HumanResources.Employee AS E
    ON P.BusinessEntityID = E.BusinessEntityID INNER JOIN Person.EmailAddress AS PE
    ON P.BusinessEntityID = PE.BusinessEntityID INNER JOIN Sales.SalesPerson AS SP
    ON E.BusinessEntityID = SP.BusinessEntityID INNER JOIN Sales.SalesOrderHeader AS SOH
    ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN Sales.SalesTerritory AS ST
    ON SP.TerritoryID = ST.TerritoryID INNER JOIN Sales.SalesOrderDetail AS SOD
    ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE ST.[GROUP] = 'Europe' AND DATEPART(MONTH,SOH.OrderDate) = 1 AND DATEPART(YEAR,SOH.OrderDate) = 2014
GROUP BY P.FirstName, P.LastName, E.HireDate, PE.EmailAddress
ORDER BY ZARADA DESC


/*Iz baze podataka AdventureWorks2014 prikazati kreditne kartice kojima je plaćeno
više od 20 narudžbi. U listu uključiti ime i prezime vlasnika kartice,
tip kartice, broj kartice, ukupan iznos plaćen karticom.*/


SELECT P.FirstName, P.LastName,CC.CardType AS 'Tip kartice', CC.CardNumber AS 'Broj Kartice',
        SUM(SOH.TotalDue) AS 'Ukupan Iznos', COUNT(SOH.SalesOrderID) AS 'Broj Narudzbi'
FROM Person.Person AS P INNER JOIN Sales.Customer AS C
        ON P.BusinessEntityID = C.CustomerID INNER JOIN SALES.PersonCreditCard AS PC
        ON P.BusinessEntityID = PC.BusinessEntityID INNER JOIN Sales.CreditCard AS CC
        ON PC.CreditCardID = CC.CreditCardID INNER JOIN SALES.SalesOrderHeader AS SOH
        ON CC.CreditCardID = SOH.CreditCardID
GROUP BY P.FirstName, P.LastName,CC.CardType,CC.CardNumber
HAVING COUNT(SOH.SalesOrderID) >20

--1)

CREATE DATABASE IB130085_ispit06

USE IB130085_ispit06

CREATE TABLE Kandidati
(
    Ime NVARCHAR(30) NOT NULL,
    Prezime NVARCHAR(30) NOT NULL,
    JMBG NVARCHAR(13) UNIQUE NONCLUSTERED NOT NULL,
    DatumRodjenja DATE NOT NULL,
    MjestoRodjenja NVARCHAR(30) NULL,
    Telefon NVARCHAR(20) NULL,
    Email NVARCHAR(50) UNIQUE NONCLUSTERED NULL

)

CREATE TABLE Testovi
(
    Datum DATETIME NOT NULL,
    Naziv NVARCHAR(50) NOT NULL,
    Oznaka NVARCHAR(10) UNIQUE NONCLUSTERED NOT NULL,
    Oblast NVARCHAR(50) NOT NULL,
    MaxBrojBodova INT NOT NULL,
    Opis NVARCHAR(250) NULL

)

CREATE TABLE RezultatiTesta
(
    Polozio BIT NOT NULL,
    OsvojeniBodovi DECIMAL NOT NULL,
    Napomena NVARCHAR(MAX) NOT NULL
)

ALTER TABLE Kandidati
ADD KandidatID INT IDENTITY(1,1) PRIMARY KEY

ALTER TABLE Testovi
ADD TestID INT IDENTITY(1,1) PRIMARY KEY

ALTER TABLE RezultatiTesta
ADD TestID INT FOREIGN KEY REFERENCES Testovi(TestID) NOT NULL


ALTER TABLE RezultatiTesta
ADD KandidatID INT FOREIGN KEY REFERENCES Kandidati(KandidatID) NOT NULL

ALTER TABLE RezultatiTesta
ADD PRIMARY KEY(KandidatID,TestID)



/*Koristeći AdventureWorks2014 bazu podataka, importovati 10 kupaca u tabelu Kandidati i to sljedeće kolone:
a. FirstName (Person) -> Ime,
b. LastName (Person) -> Prezime,
c. Zadnjih 13 karaktera kolone rowguid iz tabele Customer (Crticu zamijeniti brojem 0) -> JMBG,
d. ModifiedDate (Customer) -> DatumRodjenja,
e. City (Address) -> MjestoRodjenja,
f. PhoneNumber (PersonPhone) -> Telefon,
g. EmailAddress (EmailAddress) -> Email.
Također, u tabelu Testovi unijeti minimalno tri testa sa proizvoljnim podacima.*/

INSERT INTO IB130085_ispit06.dbo.Kandidati(Ime,Prezime,JMBG,DatumRodjenja,MjestoRodjenja,Telefon,Email)
SELECT top 10 P.FirstName AS 'Ime', P.LastName AS 'Prezime', RIGHT(REPLACE(C.rowguid,'-','0'),13) AS 'JMBG',
        C.ModifiedDate AS 'DatumRodjenja', PA.City AS 'MjestoRodjenja',PP.PhoneNumber AS 'Telefon',PE.EmailAddress AS 'Email'

FROM AdventureWorks2014.Person.Person AS P INNER JOIN AdventureWorks2014.Sales.Customer AS C
        ON P.BusinessEntityID = C.CustomerID INNER JOIN AdventureWorks2014.Person.PersonPhone AS PP
        ON P.BusinessEntityID = PP.BusinessEntityID INNER JOIN AdventureWorks2014.Person.EmailAddress AS PE
        ON P.BusinessEntityID = PE.BusinessEntityID INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS SOH
        ON SOH.CustomerID = C.CustomerID INNER JOIN AdventureWorks2014.Person.[Address] AS PA
        ON SOH.ShipToAddressID = PA.AddressID
ORDER BY C.CustomerID DESC

INSERT INTO dbo.Testovi VALUES('1-2-2001 14:00','UPR','KJKSZJP','Prog',10,NULL),
                                ('1-2-2001 15:00','PR1','AJFGHJK','Prog',10,NULL),
                                ('1-2-2001 16:00','REP','AKDSGJJ','reporting',10,NULL)


/*4. Kreirati stored proceduru koja će na osnovu proslijeđenih parametara
služiti za unos podataka u tabelu RezultatiTesta. Proceduru pohraniti pod nazivom
usp_RezultatiTesta_Insert. Obavezno testirati ispravnost kreirane
procedure (unijeti proizvoljno minimalno 10 rezultata za različite testove).*/

ALTER PROCEDURE usp_RezultatiTesta_Insert
(
    @Polozio BIT,
    @OsvojeniBodovi DECIMAL,
    @Napomena NVARCHAR(MAX),
    @TestID INT,
    @KandidatID INT

)
AS
BEGIN
    INSERT INTO dbo.REzultatiTesta VALUES(@Polozio,@OsvojeniBodovi,@Napomena,@TestID,@KandidatID)

END


SELECT * FROM Testovi
SELECT * FROM Kandidati
SELECT * FROM RezultatiTesta



EXEC usp_RezultatiTesta_Insert @Polozio = 1,@OsvojeniBodovi = 7, @Napomena = 'sedam' , @TestID = 7,
        @KandidatID = 1
EXEC usp_RezultatiTesta_Insert @Polozio = 1,@OsvojeniBodovi = 8, @Napomena = 'osam' , @TestID = 8,
        @KandidatID = 2

EXEC usp_RezultatiTesta_Insert @Polozio = 0,@OsvojeniBodovi = 3, @Napomena = 'pet' , @TestID = 9,
        @KandidatID = 3

EXEC usp_RezultatiTesta_Insert @Polozio = 0,@OsvojeniBodovi = 3, @Napomena = 'pet' , @TestID = 8,
        @KandidatID = 4



/*Kreirati view (pogled) nad podacima koji će sadržavati sljedeća polja: ime i prezime, jmbg,
telefon i email kandidata, zatim datum, naziv, oznaku, oblast i max. broj bodova na testu,
te polje položio, osvojene bodove i procentualni rezultat testa.
 View pohranite pod nazivom view_Rezultati_Testiranja.*/


 CREATE VIEW view_Rezultati_Testiranja
 AS
 SELECT K.Ime AS 'Ime', K.Prezime AS 'Prezime', K.JMBG AS 'JMBG',K.Telefon AS 'Telefon', K.Email AS 'Email',
        T.Datum AS 'Datum', T.Naziv AS 'Naziv', T.Oznaka AS 'Oznaka', RT.Polozio AS 'Polozio', RT.OsvojeniBodovi AS 'Bodovi',
        CONVERT(NVARCHAR,ROUND((RT.OsvojeniBodovi/T.MaxBrojBodova)*100,2)) + ' %' AS 'Procenti'
FROM IB130085_ispit06.dbo.Kandidati AS K INNER JOIN RezultatiTesta AS RT
        ON K.KandidatID = RT.KandidatID INNER JOIN Testovi AS T
        ON RT.TestID = T.TestID


/*6. Kreirati stored proceduru koja će na osnovu proslijeđenih parametara
@OznakaTesta i @Polozio prikazivati rezultate testiranja. Kao izvor podataka koristiti
prethodno kreirani view. Proceduru pohraniti pod nazivom usp_RezultatiTesta_SelectByOznaka.
Obavezno testirati ispravnost kreirane procedure*/

CREATE PROCEDURE usp_RezultatiTesta_SelectByOznaka
(
    @OznakaTesta NVARCHAR,
    @Polozio BIT
)
AS
BEGIN

SELECT *
FROM [dbo].[view_Rezultati_Testiranja]
WHERE [Oznaka] = @OznakaTesta AND [Polozio] = @Polozio
END


EXEC usp_RezultatiTesta_SelectByOznaka @OznakaTesta = 'KJKSZJP', @Polozio = 0

/*7. Kreirati proceduru koja će služiti za izmjenu rezultata testiranja.
 Proceduru pohraniti pod nazivom usp_RezultatiTesta_Update.
Obavezno testirati ispravnost kreirane procedure.*/

CREATE PROCEDURE usp_RezultatiTesta_Update
(
    @TestID INT,
    @Kandidat INT,
    @Polozio BIT,
    @OsvojeniBodovi DECIMAL
)
AS
BEGIN

UPDATE RezultatiTesta
SET Polozio = @Polozio,
    OsvojeniBodovi = @OsvojeniBodovi
WHERE TestID = @TestID AND KandidatID = @Kandidat

END


SELECT * FROM RezultatiTesta

EXEC usp_RezultatiTesta_Update @TestID = 1, @Kandidat = 3, @Polozio = 1, @OsvojeniBodovi = 60

/*Kreirati stored proceduru koja će služiti za brisanje testova zajedno sa
svim rezultatima testiranja. Proceduru pohranite pod nazivom usp_Testovi_Delete.
Obavezno testirati ispravnost kreirane procedure.*/

USE IB130085_ispit06

ALTER PROCEDURE usp_Testovi_Delete
AS
BEGIN

DELETE FROM RezultatiTesta
FROM Testovi AS T
        INNER JOIN RezultatiTesta AS RT
            ON T.TestID = RT.TestID

DELETE FROM Testovi

END

/*Kreirati trigger koji će spriječiti brisanje rezultata testiranja.
Obavezno testirati ispravnost kreiranog triggera.*/


EXEC usp_Testovi_Delete


CREATE TRIGGER tr_rezultati_delete
ON RezultatiTesta INSTEAD OF DELETE
AS
BEGIN
    PRINT('Zabranjeno brisanje')
END



/*Uraditi full backup Vaše baze podataka na lokaciju D:\DBMS\Backup.*/

 backup DATABASE [IB130085_ispit06] TO disk='D:\'