USE AdventureWorksOBP

--Ishod 4--

--Zad 1--
/*
Osmislite XML podatak u kojem je mogu�e proceduri odjednom poslati nazive vi�e gradova iz Hrvatske.
Kreirajte proceduru koja prima takav XML podatak, upisuje poslane gradove i ve�e ih stranim klju�em uz
Hrvatsku. Napravite poziv procedure kojim prikazujete njezinu funkcionalnost.
*/

DECLARE @gradoviHr xml;

SET @gradoviHr = '
	<Gradovi Drzava="Hrvatska">
		<Grad>Zabok</Grad>
		<Grad>Sisak</Grad>
		<Grad>Zagreb</Grad>
	</Gradovi>
'

EXEC upisiGradove @gradoviHr

CREATE OR ALTER PROC upisiGradove
	@xml xml
AS
BEGIN
	DECLARE @hrId INT
	SELECT @hrId=IDDrzava
	FROM Drzava
	WHERE Naziv IN (
		SELECT tbl.stupac.value('@Drzava', 'NVARCHAR(50)')
		FROM @xml.nodes('/Gradovi') as tbl(stupac)
	)
	INSERT INTO Grad(Naziv, DrzavaID)
	SELECT tbl.stupac.value('.', 'NVARCHAR(50)'), @hrId
	FROM @xml.nodes('/Gradovi/Grad') as tbl(stupac)

END

SELECT * FROM Grad

DROP PROC upisiGradove


--Zad 2--
/*
Napi�ite proceduru koja u jednom pozivu omogu�uje upis vi�e komercijalista u tablicu Komercijalist.
Koristite tabli�ne varijable. Napravite poziv procedure kojim prikazujete njezinu funkcionalnost. Za provjeru
napisati upit kojim prikazujete komercijaliste upisane kreiranom procedurom.
*/

CREATE TYPE kType AS TABLE (
	Ime NVARCHAR(50),
	Prezime NVARCHAR(50),
	StalniZaposlenik bit
)

DECLARE @komercijalisti kType
INSERT INTO @komercijalisti VALUES 
('Ivek', 'Ivica', 1),
('Marija', 'Maric', 0),
('Ivo', 'Simic', 1)

EXEC prUpisiKom @komercijalisti

CREATE OR ALTER PROC prUpisiKom
	@kom kType READONLY
AS
BEGIN
	INSERT INTO Komercijalist
	SELECT * 
	FROM @kom
END

SELECT * FROM Komercijalist WHERE IDKomercijalist BETWEEN 292 AND 294

DROP PROC prUpisiKom
DROP TYPE kType

--Zad 3--

/*
Napi�ite proceduru koja prima XML dokument oblika:
<Kupac Ime="Luka" Prezime="Modri�" Grad="Madrid" Drzava="�panjolska">
</Kupac>
Procedura treba provjeriti postoji li poslana dr�ava, grad i kupac u bazi (gledano po nazivima odnosno
imenu i prezimenu). Ukoliko neki od navedenih podataka nedostaje u bazi, procedura ih treba upisati u
pripadaju�e tablice. Za provjeru izvr�iti proceduru dva puta: kad poslana dr�ava, grad i kupac postoje u
bazi i kad neki od tih podataka ne postoji u bazi.
*/

DECLARE @kupac xml
SET @kupac = '
	<Kupac Ime="Luka" Prezime="Modri�" Grad="Madrid" Drzava="�panjolska">
	</Kupac>
'

EXEC prUpisiKupac @kupac

SET @kupac = '
	<Kupac Ime="John" Prezime="Doe" Grad="Madrid" Drzava="�panjolska">
	</Kupac>
'

EXEC prUpisiKupac @kupac


CREATE OR ALTER PROC prUpisiKupac
	@xml xml
AS
BEGIN
	DECLARE @drzava_ident INT
	DECLARE @grad_ident INT

	IF NOT EXISTS(
		SELECT 1
		FROM Drzava
		WHERE Naziv IN (
			SELECT tbl.stupac.value('@Drzava', 'NVARCHAR(50)')
			FROM @xml.nodes('/Kupac') as tbl(stupac)
		)
	)
		BEGIN
			INSERT INTO Drzava(Naziv)
			SELECT tbl.stupac.value('@Drzava', 'NVARCHAR(50)')
			FROM @xml.nodes('/Kupac') as tbl(stupac)
			SET @drzava_ident = SCOPE_IDENTITY()
		END

	IF NOT EXISTS(
		SELECT 1
		FROM Grad
		WHERE Naziv IN (
			SELECT tbl.stupac.value('@Grad', 'NVARCHAR(50)')
			FROM @xml.nodes('/Kupac') as tbl(stupac)
		)
	)
		BEGIN
			INSERT INTO Grad(Naziv, DrzavaID)
			SELECT tbl.stupac.value('@Grad', 'NVARCHAR(50)'), @drzava_ident
			FROM @xml.nodes('/Kupac') as tbl(stupac)
			SET @grad_ident = SCOPE_IDENTITY()
		END

	IF NOT EXISTS(
		SELECT 1
		FROM Kupac
		WHERE Ime + Prezime IN (
			SELECT tbl.stupac.value('@Ime', 'NVARCHAR(50)') + tbl.stupac.value('@Prezime', 'NVARCHAR(50)')
			FROM @xml.nodes('/Kupac') as tbl(stupac)
		)
	)
		BEGIN
			INSERT INTO Kupac(Ime, Prezime, GradID)
			SELECT tbl.stupac.value('@Ime', 'NVARCHAR(50)'), tbl.stupac.value('@Prezime', 'NVARCHAR(50)'), @grad_ident
			FROM @xml.nodes('/Kupac') as tbl(stupac)
		END
END

DROP PROC prUpisiKupac

--Ishod 5--

--Zad 1--

/*
Napi�ite proceduru s jednim parametrom IDKupac koja bri�e zadanog kupca, sve ra�une koje je on
napravio i stavke tih ra�una. U transakciji izvan procedure pozovite proceduru 3 puta s 3 razli�ita podatka
IDKupac. Ispi�ite uspjeh ili neuspjeh cijele operacije. Napravite poziv procedure kojim prikazujete primjer
uspje�nog brisanja podataka.
*/

CREATE OR ALTER PROC brisiKupca
	@IDKupac int
AS
BEGIN
	DELETE FROM Stavka WHERE RacunID IN (
		SELECT IDRacun
		FROM Racun
		WHERE KupacID = @IDKupac
	)

	DELETE FROM Racun WHERE KupacID = @IDKupac

	DELETE FROM Kupac WHERE IDKupac = @IDKupac

END


BEGIN TRAN

BEGIN TRY
	EXEC brisiKupca @IDKupac = 2
	EXEC brisiKupca @IDKupac = 4
	EXEC brisiKupca @IDKupac = 5
	COMMIT TRAN
	PRINT('Uspjeh')
END TRY

BEGIN CATCH
	ROLLBACK TRAN
	PRINT('Gre�ka')
END CATCH

--Zad 2--

/*
	Opi�ite i na tablici Proizvod demonstrirajte kako nastaje problem fantoma. Nazna�ite u kojem se to�no
	dijelu problem pojavljuje i na koji na�in i gdje ste ga rije�ili.
*/

/*
	Problem fantoma se pojavljuje kada prva transakcija �ita tablicu dva puta, no u me�uvremenu druga transakcija unese novi podatak
	Tada prva transakcija na drugom �itanju dobiva druga�ije podatke od prvog �itanja, to jest dobiva n + 1 redaka
*/

BEGIN TRAN
SELECT * FROM Proizvod
INSERT INTO Proizvod(Naziv, BrojProizvoda, MinimalnaKolicinaNaSkladistu, CijenaBezPDV)
VALUES ('Red Sunglasses', 'PR-123', 23, 9.99)
SELECT * FROM Proizvod
COMMIT TRAN

/*

	Problem se rje�ava dodavanjem: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	Koji spre�ava dodavanje podataka druge transakcije dok ne zavr�i prva transakcija
*/

--Zad 3--

/*
	Napi�ite proceduru s jednim ulaznim parametrom IDRacun koja u transakciji a�urira stupac Komentar
	poslanog ra�una i u njega upisuje �Provjeriti�. Pozovite proceduru dva puta s dva razli�ita podatka
	IDRacun: u jednom pozivu neka bude stvarni ra�un, a u drugom pozivu po�aljite nepostoje�i IDRacun).
	Ispi�ite uspjeh ili neuspjeh cijele operacije.
*/

CREATE OR ALTER PROC promijeniKomentar
	@IDRacun int
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		UPDATE Racun SET Komentar = 'Provjeriti' WHERE IDRacun = @IDRacun
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN
	END CATCH
END

EXEC promijeniKomentar @IDRacun = 43659
EXEC promijeniKomentar @IDRacun = 2
IF @@ERROR = 0
	BEGIN
		PRINT ('Uspjeh')
	END
ELSE
	BEGIN
		PRINT ('Neuspjeh')
	END

--Ishod 6--

--Zad 1--
/*
Napi�ite jedan upit za prikaz koliko kreiranih ra�una ima pojedini komercijalist (prika�ite ime i prezime
komercijaliste). Rezultate treba rangirati s nekom od funkcija (po izboru) kako bi na vrhu bili komercijalisti
s najvi�e izdanih ra�una.
*/

SELECT	k.Ime, k.Prezime, COUNT(r.IDRacun) as IzdaniRacuni, RANK() OVER (ORDER BY COUNT(r.IDRacun) DESC) as Rang
FROM Komercijalist as k
INNER JOIN Racun as r ON r.KomercijalistID = k.IDKomercijalist
GROUP BY k.Ime, k.Prezime

--Zad 2--

/*
Napi�ite upit za prikaz naziva proizvoda i minimalnih koli�ina na skladi�tu, sortirano padaju�e po
minimalnim koli�inama na skladi�tu. Neka u dodatnom stupcu bude prikazan prosje�ni iznos minimalne
koli�ine na skladi�tu uklju�uju�i trenutni redak, 5 prethodnih redaka i 5 sljede�ih redaka.
*/

SELECT p.Naziv, p.MinimalnaKolicinaNaSkladistu, AVG(p.MinimalnaKolicinaNaSkladistu) OVER (PARTITION BY p.Naziv) as prosjek
FROM Proizvod as p
ORDER BY MinimalnaKolicinaNaSkladistu DESC

--Zad 3--

/*
Napi�ite jedan upit za izra�unavanje ukupnog iznosa prodane robe (SUM(Stavka.UkupnaCijena)) po
gradovima (prika�ite nazive gradova) i tipovima kreditnih kartica kojima su pla�ani ra�uni. Treba biti
vidljivo i koliki je ukupan iznos prodane robe u pojedinom gradu (za sve tipove kreditnih kartica) te grand
total.
*/

SELECT g.Naziv, kk.Tip, SUM(s.UkupnaCijena)
FROM Stavka as s
INNER JOIN Racun as r on r.IDRacun = s.RacunID
INNER JOIN KreditnaKartica as kk on kk.IDKreditnaKartica = r.KreditnaKarticaID
INNER JOIN Kupac as k on k.IDKupac = r.KupacID
INNER JOIN Grad as g on g.IDGrad = k.GradID
GROUP BY ROLLUP(g.Naziv, kk.Tip)

--Ishod 7--
/*
--Zad 1
--Kreirajte novu bazu podataka proizvoljnog naziva i u njoj novu kolekciju naziva Prijatelji.

const baza = 'OBP';
use(baza);
db.createCollection('Prijatelji');

--Zad 2
--Upi�ite sebe i svog najboljeg prijatelja u novu kolekciju: upi�ite imena, prezimena i komentar.

db.getCollection('Prijatelji').insertMany(
    [
        {
            "Ime": "Luka",
            "Prezime": "Markota",
            "Komentar": "test"
        },
        {
            "Ime": "John",
            "Prezime": "Doe",
            "Komentar": "Najjaci"
        }
    ]
);

--Zad 3
--Napi�ite upit za a�uriranje Va�eg dokumenta iz kolekcije Prijatelji promjenom komentara u �Provjeriti�.

db.getCollection('Prijatelji').updateOne(
    {"Komentar": "test"},
    {
        $set:   {"Komentar": "provjeriti"}
    }
);

--Zad 4
--Napi�ite upit kojim �ete iz kolekcije Prijatelji obrisati dokument s podacima o vama.

db.getCollection('Prijatelji').deleteOne(
    {"Ime": "Luka"}
);
*/