USE AdventureWorksOBP
--Ishod 4--

/*
Osmislite JSON podatak u kojem je moguće proceduri odjednom poslati više brojeva računa.
Kreirajte proceduru koja prima takav JSON podatak briše račune
i stavke koje imaju navedeni broj računa.
Napravite poziv procedure kojim prikazujete njezinu funkcionalnost.
*/

DECLARE @racuniJson NVARCHAR(MAX)

SET @racuniJson = '
	{
		"racuni": [
			{
				"racun": "SO43659"
			},
			{
				"racun": "SO43669"
			},
			{
				"racun": "SO43234"
			}
		]
	}
'

EXEC obradiJsonRacune @racuniJson



CREATE OR ALTER PROC obradiJsonRacune
	@json NVARCHAR(MAX)
AS
	BEGIN
		DECLARE @ident INT
		DELETE FROM Stavka WHERE IDStavka IN (
			SELECT IDStavka
			FROM Stavka as s
			INNER JOIN Racun as r ON s.RacunID = r.IDRacun
			WHERE r.BrojRacuna IN (
				SELECT BrojRacuna
				FROM OPENJSON(@json, '$.racuni')
				WITH (
					BrojRacuna NVARCHAR(50) '$.racun'
				)
			)
		)

		DELETE FROM Racun WHERE BrojRacuna IN (
			SELECT BrojRacuna
			FROM OPENJSON(@json, '$.racuni')
			WITH (
				BrojRacuna NVARCHAR(50) '$.racun'
			)
		
		)
	END	


/*
Napišite proceduru koja u jednom pozivu omogućuje upis vise računa u tablicu Račun. Koristite tablične varijable.
Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure.
Za provjeru napisati upit kojim prikazujete račune upisane kreiranom procedurom.
*/

CREATE TYPE Racun AS TABLE (
	DatumIzdavanja datetime not null,
	BrojRacuna nvarchar(25) not null,
	KupacID int not null,
	KomercijalistID int not null,
	KreditnaKarticaID int not null,
	Komentar nvarchar(128) not null
)

DECLARE @racuni Racun

INSERT INTO @racuni VALUES
('1995-01-01', 'CO122334', 12, 289, 1970, ''),
('1995-02-01', 'CO1234334', 12, 289, 1970, ''),
('1995-04-01', 'CO1234334', 12, 289, 1970, '')

EXEC upisRacuna @racuni

CREATE OR ALTER PROC upisRacuna
	@racuni Racun READONLY
AS
	BEGIN
		INSERT INTO Racun 
		SELECT *
		FROM @racuni
	END

SELECT * FROM Racun 
WHERE KupacID = 12

/*
Napišite proceduru koja prima XML dokument oblika:
<Potkategorija Naziv="Glasses" KategorijaID="4">
<Proizvod BrojProizvoda="SG-21" MinimalnaKolicinaNaSkladistu="15" CijenaBezPDV="120">Sunglasses Orange</Proizvod>
<Proizvod BrojProizvoda="SG-22" MinimalnaKolicinaNaSkladistu="15" CijenaBezPDV="130">Sunglasses Red</Proizvod>
</Potkategorija>
Procedura treba provjeriti postoji li poslana potkategorija i proizvod u bazi (gledano po nazivima).
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 potkategorija i proizvod postoje u bazi i kad neki od tih podataka ne postoji u bazi.
*/

DECLARE @xmlPodaci xml
SET @xmlPodaci = '
	<Potkategorija Naziv="Glasses" KategorijaID="4">
		<Proizvod BrojProizvoda="SG-21" MinimalnaKolicinaNaSkladistu="15" CijenaBezPDV="120">Sunglasses Orange</Proizvod>
		<Proizvod BrojProizvoda="SG-22" MinimalnaKolicinaNaSkladistu="15" CijenaBezPDV="130">Sunglasses Red</Proizvod>
	</Potkategorija>
'

EXEC xmlProKat @xmlPodaci



SELECT tbl.stupac.value('@Naziv','NVARCHAR(50)')
FROM @xmlPodaci.nodes('/Potkategorija') as tbl(stupac)


CREATE OR ALTER PROC xmlProKat
	@xml xml
AS
BEGIN
	DECLARE @ident INT
	IF NOT EXISTS(
			SELECT * FROM Potkategorija WHERE Naziv IN(
				SELECT tbl.stupac.value('@Naziv','NVARCHAR(50)')
				FROM @xml.nodes('/Potkategorija') as tbl(stupac)
			)
	)
		BEGIN
			INSERT INTO Potkategorija(Naziv, KategorijaID)
			SELECT tbl.stupac.value('@Naziv','NVARCHAR(50)'), tbl.stupac.value('@KategorijaID','INT')
			FROM @xml.nodes('/Potkategorija') as tbl(stupac)
			SET @ident = SCOPE_IDENTITY()
		END

	IF NOT EXISTS (
		SELECT * FROM Proizvod WHERE Naziv IN(
				SELECT tbl.stupac.value('.','NVARCHAR(50)')
				FROM @xml.nodes('/Potgategorija/Proivzod') as tbl(stupac)
			)
	)
		BEGIN
			INSERT INTO Proizvod(Naziv, BrojProizvoda, MinimalnaKolicinaNaSkladistu, CijenaBezPDV, PotkategorijaID)
			SELECT
				tbl.stupac.value('.', 'NVARCHAR(50)'),
				tbl.stupac.value('@BrojProizvoda', 'NVARCHAR(25)'),
				tbl.stupac.value('@MinimalnaKolicinaNaSkladistu', 'smallint'),
				tbl.stupac.value('@CijenaBezPDV', 'money'),
				@ident
			FROM @xml.nodes('/Potkategorija/Proizvod') as tbl(stupac)
		END
END

SELECT * FROM Proizvod WHERE Naziv LIKE 'Sunglasses%'

/*
Napišite proceduru koja prima XML dokument oblika:
<GradoviIz Drzava="Hrvatska">
	<Grad>Sinj</Grad>
	<Grad>Gospié</Grad>
	<Grad>Vinkovci</Grad>
</GradoviIz>
Neka procedura upise državu i gradove iz XML-a u pripadne tablice Država i Grad.
Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure.
Za provjeru napisati upit kojim prikazujete.

*/

DECLARE @xmlPodatak xml

set @xmlPodatak = '
<GradoviIz Drzava="Hrvatska">
	<Grad>Sinj</Grad>
	<Grad>Gospić</Grad>
	<Grad>Vinkovci</Grad>
</GradoviIz>
'

EXEC xmlGradDrzava @xmlPodatak

CREATE OR ALTER PROC xmlGradDrzava
	@xml xml
AS
BEGIN
	DECLARE @ident INT
	INSERT INTO Drzava(Naziv)
	SELECT tbl.stupac.value('@Drzava', 'NVARCHAR(50)')
	FROM @xml.nodes('/GradoviIz') as tbl(stupac)
	SET @ident = SCOPE_IDENTITY()

	INSERT INTO Grad(Naziv, DrzavaID)
	SELECT tbl.stupac.value('.', 'NVARCHAR(50)'), @ident
	FROM @xml.nodes('/GradoviIz/Grad') as tbl(stupac)
END

SELECT * FROM Grad WHERE DrzavaID = 6

/*
Napišite proceduru koja u jednom pozivu omogućuje umetanje više stavki u tablicu Stavka.
Koristite tablične varijable. Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure.
Za provjeru napisati upit kojim prikazujete proizvode upisane kreiranom procedurom.
*/

CREATE TYPE StavkaType AS TABLE (
	RacunID int not null,
	Kolicina smallint not null,
	ProizvodID int not null,
	CijenaPoKomadu money not null,
	PopustUPostocima money not null,
	UkupnaCijena numeric(38,6) not null
)

DECLARE @stavka StavkaType

INSERT INTO @stavka VALUES
(43660, 23, 2, 13, 0, 100),
(43660, 22, 2, 42, 0, 120),
(43660, 21, 2, 13, 0, 150)

EXEC umetniStavke @stavka

CREATE OR ALTER PROC umetniStavke
	@stavka StavkaType READONLY
AS
BEGIN
	INSERT INTO Stavka
	SELECT *
	FROM @stavka
END

SELECT * FROM Stavka WHERE RacunID = 43660

/*
Napišite proceduru s jednim parametrom IDPotkategorija koja svim pripadajućim proizvodima brise podatak Boja.
Pozovite proceduru u transakciji izvan procedure. Ispišite uspjeh (ako su proizvodi ažurirani) ili neuspjeh
(ako je došlo do pogreške prilikom ažuriranja). Napravite primjer poziva kreirane procedure.
*/

CREATE OR ALTER PROC izbrisiBoje
	@IDPotkategorija int
AS
BEGIN


	BEGIN TRY
		UPDATE Proizvod SET Boja = NULL WHERE PotkategorijaID IN (
			SELECT IDPotkategorija
			FROM Potkategorija
			WHERE IDPotkategorija = @IDPotkategorija
		)
		PRINT('Uspjeh')

	END TRY

	BEGIN CATCH

		PRINT('Neuspjeh')
	END CATCH
END

BEGIN TRANSACTION;

EXEC izbrisiBoje @IDPotkategorija = 5;

IF @@ERROR = 0
    PRINT 'Transakcija uspješno završena';
ELSE
    PRINT 'Došlo je do pogreške';

COMMIT TRANSACTION;

/*
Napišite proceduru s jednim parametrom IDKategorija koja briše zadanu kategoriju,
potkategorije te kategorije i proizvode tih potkategorija.
U transakciji izvan procedure pozovite proceduru 2 puta s 2 različita IDProizvoda.
Ispišite uspjeh ili neuspjeh cijele operacije.
*/

CREATE OR ALTER PROC izbrisiSveOdKategorije
	@IDKategorija INT
AS
BEGIN
	BEGIN TRY
	DELETE FROM Proizvod WHERE PotkategorijaID IN (
		SELECT IDPotkategorija
		FROM Potkategorija
		WHERE KategorijaID = @IDKategorija
	)

	DELETE FROM Potkategorija WHERE KategorijaID = @IDKategorija

	DELETE FROM Kategorija WHERE IDKategorija = @IDKategorija
	END TRY

	BEGIN CATCH
		PRINT('Greška')
	END CATCH
END

BEGIN TRY

    EXEC izbrisiSveOdKategorije 6;
    EXEC izbrisiSveOdKategorije 7;


    COMMIT TRANSACTION;
    PRINT 'Cijela operacija uspješno završena!';
END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION;
    PRINT 'Došlo je do greške. Transakcija poništena.';
END CATCH;

/*
	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 (T1) dvaput čita iste podatke,
ali između ta dva čitanja druga transakcija (T2) ažurira ili briše podatke.
To znači da kada T1 drugi put pročita podatke, dobije drugačije vrijednosti nego prvi put,
što može dovesti do nedosljednih rezultata.
*/

BEGIN TRANSACTION;
SELECT * FROM Kupac WHERE IDKupac = 1; -- Prvo čitanje
-- Ovdje PAUZIRAMO transakciju i ne radimo COMMIT
UPDATE Kupac SET Ime = 'Ivan Novi' WHERE IDKupac = 1;
COMMIT;
SELECT * FROM Kupac WHERE IDKupac = 1; -- Dobit ćemo promijenjeni rezultat
COMMIT;

--rješenje--

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Kupac WHERE IDKupac = 1; 
-- Ovdje PAUZIRAMO transakciju
UPDATE Kupac SET Ime = 'Markec' WHERE IDKupac = 1; -- OVDJE T2 ČEKA!
COMMIT;
SELECT * FROM Kupac WHERE IDKupac = 1; -- Dobit ćemo promijenjeni rezultat
COMMIT;

/*
Opišite i na tablici Grad demonstrirajte kako nastaje problem fantoma.
Naznačite gdje se problem pojavljuje i na koji način i gdje ste ga riješili.
*/

BEGIN TRANSACTION;
SELECT * FROM Grad WHERE Naziv = 'Zagreb'; -- Prvo čitanje
-- Ovdje PAUZIRAMO transakciju
INSERT INTO Grad (IDGrad, Naziv, DrzavaID) VALUES (101, 'Zabok', 1);
COMMIT;
SELECT * FROM Grad WHERE Naziv = 'Zagreb'; -- Novi red je dodan!
COMMIT;

--rješebnje--
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Grad WHERE Naziv = 'Zagreb'; 
-- Ovdje PAUZIRAMO transakciju
INSERT INTO Grad (IDGrad, Naziv, DrzavaID) VALUES (101, 'Zabok', 1); -- OVDJE T2 ČEKA!


