USE AdventureWorksOBP

/* 
------------------------------------------------------------------------
MEĐUISPIT #2
ISHOD 4 
------------------------------------------------------------------------
*/

/*
ZADATAK 8 [JSON]:
Osmislite JSON podatak u kojem je moguće proceduri odjednom poslati naziv jedne kategorije i nazive pripadajućih potkategorija. 
Kreirajte proceduru koja prima takav JSON podatak i upisuje poslanu kategoriju i poslane pripadajuće potkategorije. 
Prije upisa kategorije procedura treba provjeriti je li poslana kategorija već upisana. 
Ako je, ne treba upisivati ponovo niti kategoriju niti potkategorije. Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure.
*/

DECLARE @json NVARCHAR(MAX)
SET @json = '
	{
		"Kategorija": "Alati",
		"Potkategorije": [
			{
				"Potkategorija": "Alatka"
			},
			{
				"Potkategorija": "Fraunhofer"
			}
		]
	}

'

EXEC prWriteCategory @json

CREATE OR ALTER PROC prWriteCategory
	@json NVARCHAR(MAX)
AS
	BEGIN
		DECLARE @NazivKategorije NVARCHAR(50)
		DECLARE @Ident INT
		SELECT @NazivKategorije=Kategorija
		FROM OPENJSON(@json)
		WITH (
			Kategorija NVARCHAR(50) '$.Kategorija'
		)
		IF NOT EXISTS (SELECT 1 FROM Kategorija WHERE Naziv = @NazivKategorije) 
			BEGIN	
				INSERT INTO Kategorija(Naziv) VALUES (@NazivKategorije)
				SET @Ident = SCOPE_IDENTITY()

				INSERT INTO Potkategorija(Naziv, KategorijaID)
				SELECT Potkategorija, @Ident
				FROM OPENJSON(@json, '$.Potkategorije')
				WITH (
					Potkategorija NVARCHAR(100) '$.Potkategorija'
				)
			END
	END

/*
ZADTAK 9 [XML]:
Napišite proceduru koja prima XML dokument oblika:
<Gradovi Drzava="Hrvatska">
		<Grad>Sinj</Grad>
		<Grad>Gospić</Grad>
		<Grad>Vinkovci</Grad>
</Gradovi>
Neka procedura upiše državu i gradove iz XML-a u pripadne tablice Drzava i Grad. Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure. 
Za provjeru napisati upit kojim prikazujete državu i gradove upisane kreiranom procedurom.
*/

DECLARE @Gradovi xml

SET @Gradovi = '
	<Gradovi Drzava="Hrvatska">
		<Grad>Sinj</Grad>
		<Grad>Gospić</Grad>
		<Grad>Vinkovci</Grad>
	</Gradovi>
'

EXEC gradoviXML @Gradovi

CREATE OR ALTER PROC gradoviXML
	@Gradovi xml
AS
	BEGIN
		DECLARE @ident INT
		INSERT INTO Drzava
		SELECT tbl.stupac.value('@Drzava', 'nvarchar(50)')
		FROM @Gradovi.nodes('/Gradovi') as tbl(stupac)
		set @ident = SCOPE_IDENTITY()

		INSERT INTO Grad(Naziv, DrzavaID)
		SELECT tbl.stupac.value('.', 'nvarchar(50)'), @ident
		FROM @Gradovi.nodes('/Gradovi/Grad') as tbl(stupac)

	END

SELECT * FROM Drzava

SELECT * FROM Grad

DELETE FROM Drzava WHERE IDDrzava = 4

/* 
------------------------------------------------------------------------
ISHOD 5 
------------------------------------------------------------------------
*/

/*
ZADATAK 10 [TRANSAKCIJE]:
Napišite proceduru s jednim parametrom IDKategorija koja unutar transakcije ažurira sve cijene proizvoda poslane kategorije na nulu (CijenaBezPDV=0). 
Napravite poziv procedure kojim prikazujete zadanu funkcionalnost procedure.
*/

CREATE OR ALTER PROC spUpadteCijena
	@IDKategorija int
AS
	BEGIN
		BEGIN TRAN

		BEGIN TRY
			UPDATE Proizvod SET CijenaBezPDV = 0
			WHERE PotkategorijaID IN(
				SELECT PotkategorijaID FROM Potkategorija WHERE KategorijaID = @IDKategorija
			)

			COMMIT TRAN

		END TRY

		BEGIN CATCH
			ROLLBACK TRAN
		END CATCH

	END

/*
ZADATAK 11 [TRANZAKCIJE]:
Opišite i na tablici Kupac demonstrirajte kako nastaje problem neponovljivog čitanja. Naznačite gdje se problem pojavljuje i na koji način i gdje ste ga riješili.
*/

/*	Problem neponovljivog čitanja nastaje kada jedna transakcija dva puta pročita iste podatke,
a druga transakcija izmjeni te podatke između čitanja
Kao rezultat, prva transakcija dobiva različite vrijednosti za isti skup podataka.
*/
	SET TRANSACTION ISOLATION LEVEL READ COMMITED
	BEGIN TRAN
	SELECT * FROM Kupac WHERE IDKupac = 1
	SELECT * FROM Kupac WHERE IDKupac = 1
	END TRAN

/* 
------------------------------------------------------------------------
ISHOD 6 
------------------------------------------------------------------------
*/

/*
ZADATAK 12 [GROUP BY]:
Napišite jedan upit za prikaz naziva potkategorija i broja koliko ima proizvoda iz pojedine potkategorije. 
Potkategorije treba rangirati s nekom od funkcija (po izboru) kako bi na vrhu bile potkategorije u kojima ima najviše proizvoda.
*/

	SELECT pk.Naziv as 'Naziv Proizvoda',
	COUNT(p.IDProizvod) AS 'Broj Proizvoda',
	RANK() OVER (ORDER BY COUNT(p.IDProizvod)) AS 'Rang' FROM Potkategorija as pk
	LEFT JOIN Proizvod as p ON pk.IDPotkategorija = p.PotkategorijaID
	GROUP BY pk.Naziv

/*
ZADATAK 13 [GROUP BY]:
Napišite jedan upit za izračunavanje ukupne plaćene cijene (SUM(Stavka.UkupnaCijena)) po kupcima i godinama računa. Imena i prezimena kupaca neka budu ispisana u prvom (jednom) stupcu, 
godine izdavanja računa u drugom stupcu, a ukupna plaćena cijena u trećem stupcu. Treba biti vidljivo i koliko je ukupna zarada od pojedinog kupca te grand total.
*/
	SELECT k.Ime + ' ' + k.Prezime as 'Kupac', YEAR(r.DatumIzdavanja) as Godina, SUM(s.UkupnaCijena) as 'Zarada'
	FROM Stavka as s
	INNER JOIN Racun as r ON s.RacunID = r.IDRacun
	INNER JOIN Kupac as k ON r.KupacID = k.IDKupac
	GROUP BY ROLLUP(k.Ime + ' ' + k.Prezime, YEAR(r.DatumIzdavanja))
/* 
------------------------------------------------------------------------
ISHOD 7 
------------------------------------------------------------------------
*/

/*
ZADATAK 14 [NOSQL]:
- Kreirajte novu bazu podataka proizvoljnog naziva i u njoj novu kolekciju naziva Student.
- Upišite sebe u novokreiranu kolekciju: upišite svoje ime, prezime, email adresu, JMBAG te naziv svoje grupe.
- Napišite upit kojim ćete ažurirati svoj dokument iz kolekcije Student na način da si promijenite email adresu u ime.prezime@algebra.hr.
- Napišite upit kojim ćete izbrisati ranije kreiranu bazu podataka.
*/

/* --------------------------------------------------------------------- */
