
-- 1.

create or alter FUNCTION dbo.Razdvoji
(
	@Vrijednosti nvarchar(MAX)
)
RETURNS @Tablica TABLE
(
	Vrijednost int
)
AS
BEGIN
	DECLARE @pos int
	DECLARE @nextpos int
	DECLARE @duljina int
	DECLARE @temp nvarchar(500)

	SET @pos = 1
	SET @nextpos = 1

	WHILE @nextpos > 0 BEGIN
		SET @nextpos = CHARINDEX(',', @Vrijednosti, @pos)
		SET @duljina = 
			CASE
				WHEN @nextpos > 0 THEN @nextpos - @pos
				ELSE LEN(@Vrijednosti) + 1 - @pos
			END
		SET @temp = SUBSTRING(@Vrijednosti, @pos, @duljina)
		
		INSERT INTO @Tablica (Vrijednost) VALUES (CONVERT(int, @temp))

		SET @pos = @nextpos + 1
	END	

	RETURN
END
GO

SELECT * FROM dbo.Razdvoji('75,2')
SELECT * FROM dbo.Razdvoji('75,1245,134,1,2')
GO


-- 2.

CREATE PROC DohvatiProizvode
	@Proizvodi nvarchar(max)
AS
SELECT IDProizvod, Naziv, Boja
FROM Proizvod
WHERE IDProizvod IN (SELECT Vrijednost FROM dbo.Razdvoji(@Proizvodi))
GO

EXEC DohvatiProizvode '316,317,318'
GO


-- 3.

ALTER FUNCTION dbo.Razdvoji
(
	@Vrijednosti nvarchar(MAX)
)
RETURNS @Tablica TABLE
(
	Vrijednost nvarchar(100)
)
AS
BEGIN
	DECLARE @pos int
	DECLARE @nextpos int
	DECLARE @duljina int
	DECLARE @temp nvarchar(500)

	SET @pos = 1
	SET @nextpos = 1

	WHILE @nextpos > 0 BEGIN
		SET @nextpos = CHARINDEX(',', @Vrijednosti, @pos)
		SET @duljina = 
			CASE
				WHEN @nextpos > 0 THEN @nextpos - @pos
				ELSE LEN(@Vrijednosti) + 1 - @pos
			END
		SET @temp = SUBSTRING(@Vrijednosti, @pos, @duljina)
		
		INSERT INTO @Tablica (Vrijednost) VALUES (@temp)

		SET @pos = @nextpos + 1
	END	

	RETURN
END
GO

SELECT * FROM dbo.Razdvoji('Jedan,Dva,Pet')
GO


-- 4.

alter PROC DohvatiProizvode
	@Proizvodi nvarchar(max)
AS
SELECT IDProizvod, Naziv, Boja
FROM Proizvod
WHERE Naziv IN (SELECT Vrijednost FROM dbo.Razdvoji(@Proizvodi))
GO

EXEC DohvatiProizvode 'Bearing Ball,BB Ball Bearing,Chainring Bolts' -- OK
EXEC DohvatiProizvode 'Bearing Ball,Sport-100 Helmet, Blue' -- PROBLEM!
GO


-- 5. 

ALTER FUNCTION dbo.Razdvoji
(
	@Vrijednosti nvarchar(MAX),
	@Separator char(1)
)
RETURNS @Tablica TABLE
(
	Vrijednost nvarchar(100)
)
AS
BEGIN
	DECLARE @pos int
	DECLARE @nextpos int
	DECLARE @duljina int
	DECLARE @temp nvarchar(500)

	SET @pos = 1
	SET @nextpos = 1

	WHILE @nextpos > 0 BEGIN
		SET @nextpos = CHARINDEX(@Separator, @Vrijednosti, @pos)
		SET @duljina = 
			CASE
				WHEN @nextpos > 0 THEN @nextpos - @pos
				ELSE LEN(@Vrijednosti) + 1 - @pos
			END
		SET @temp = SUBSTRING(@Vrijednosti, @pos, @duljina)
		
		INSERT INTO @Tablica (Vrijednost) VALUES (@temp)

		SET @pos = @nextpos + 1
	END	

	RETURN
END
GO

ALTER PROC DohvatiProizvode
	@Proizvodi nvarchar(max)
AS
SELECT IDProizvod, Naziv, Boja
FROM Proizvod
WHERE Naziv IN (SELECT Vrijednost FROM Razdvoji(@Proizvodi, '¤'))
GO

EXEC DohvatiProizvode 'Bearing BallB¤B Ball Bearing¤Chainring Bolts' -- OK
EXEC DohvatiProizvode 'Bearing Ball¤Sport-100 Helmet, Blue' -- OK
-- ¤: Alt+0164
GO


-- 6.

DECLARE @Adrese xml
SET @Adrese = N'
    <Adrese>
        <Adresa Grad="Zagreb" Pbr="10000">Sunčana ulica 17</Adresa>
        <Adresa Grad="Varaždin" Pbr="42000">Široka ulica 25</Adresa>
        <Adresa Grad="Zabok" Pbr="49210">Trg slobode 4</Adresa>
    </Adrese>'

SELECT
    Tablica.Stupac.value('@Grad', 'nvarchar(50)') AS Grad,
    Tablica.Stupac.value('@Pbr', 'int') AS PostanskiBroj,
    Tablica.Stupac.value('.', 'nvarchar(50)') As Ulica
FROM @Adrese.nodes('/Adrese/Adresa') AS Tablica(Stupac)
GO


-- 7.

ALTER PROC DohvatiProizvode
	@Proizvodi xml
AS
SELECT IDProizvod, Naziv, Boja
FROM Proizvod
WHERE IDProizvod IN (
	SELECT Tablica.Stupac.value('.', 'int') As IDProizvoda
	FROM @Proizvodi.nodes('/Proizvodi/IDPro') AS Tablica(Stupac)
)
GO

DECLARE @Proizvodi xml
SET @Proizvodi = '
    <Proizvodi>
		<IDPro>1</IDPro>
		<IDPro>315</IDPro>
		<IDPro>316</IDPro>
    </Proizvodi>'
    
EXEC DohvatiProizvode @Proizvodi
GO


-- 8. 

declare @xml xml
set @xml='
<Drzava Naziv="Slovenija">
	<Grad>Ljubljana</Grad>
	<Grad>Maribor</Grad>
	<Grad>Ptuj</Grad>
</Drzava>
'
exec p8 @xml

select * from drzava
select * from grad

create proc p8
	@xml xml
as
insert into Drzava(naziv)
select distinct tablica.stupac.value('../@Naziv', 'nvarchar(100)') as Drzava
from @xml.nodes('/Drzava/Grad') as tablica(stupac)
insert into Grad(Naziv, drzavaid)
select tablica.stupac.value('.', 'nvarchar(100)') as Grad, SCOPE_IDENTITY()
from @xml.nodes('/Drzava/Grad') as tablica(stupac)
go


-- 9. 

SELECT 
	p.idproizvod as [@IDProizvod], 
	p.Naziv, 
	p.BrojProizvoda, 
	p.Boja as [Boje/Boja]
FROM proizvod as p
FOR XML PATH('Proizvod'), ROOT('Proizvodi')


-- 10. 

CREATE TYPE ZadaniProizvod AS TABLE
(
	IDPro int
)
GO

ALTER PROC DohvatiProizvode
	@Proizvodi ZadaniProizvod READONLY
AS
SELECT IDProizvod, Naziv, Boja
FROM Proizvod
WHERE IDProizvod IN (SELECT IDPro FROM @Proizvodi)
GO

DECLARE @t ZadaniProizvod
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO @t VALUES (316)

EXEC DohvatiProizvode @t
GO


-- 11.

CREATE TABLE Osoba
(
	IDOsoba int PRIMARY KEY IDENTITY,
	Ime nvarchar(50),
	Prezime nvarchar(50)
)
CREATE TYPE Osoba AS TABLE
(
	Ime nvarchar(50),
	Prezime nvarchar(50)
)
GO

CREATE PROC UmetniOsobe
	@Osobe Osoba READONLY
AS
INSERT INTO Osoba
SELECT * FROM @Osobe
GO

DECLARE @t Osoba
INSERT INTO @t VALUES ('Miro', 'Mirić')
INSERT INTO @t VALUES ('Ana', 'Anič')

EXEC UmetniOsobe @t

SELECT * FROM Osoba
GO


-- 12.

create or alter proc p12
	@json nvarchar(max)
as
select ime, prezime
from openjson(@json, '$.OSOBE')		
with		
(
	Ime nvarchar(50) '$.OSOBA.ime',
	Prezime nvarchar(50) '$.OSOBA.prezime'
)


declare @json nvarchar(max)
set @json= 
'{"OSOBE":	
		[
			{"OSOBA":
					{
						"ime": "Pero",	
						"prezime": "Perić"
					}
			},
			{"OSOBA":
					{
						"ime": "Iva",	
						"prezime": "Ivić"
					}
			}
		]}'

exec p12 @json


-- 13.

create or alter proc p13
	@json nvarchar(max)
as
select ime, prezime, telefon
from openjson(@json, '$.OSOBE')		
with		
(
	Ime nvarchar(50) '$.OSOBA.ime',
	Prezime nvarchar(50) '$.OSOBA.prezime',
	Telefon nvarchar(50) '$.OSOBA.ostalo.telefon'
)
go

declare @json nvarchar(max)
set @json= 
N'{"OSOBE":	
		[
			{"OSOBA":
					{"idosoba": 2, "ime": "Iva", "prezime": "Ivić",	
					 "ostalo": 
						{"email": "iva.ivic@algebra.hr", 
						 "telefon": "091 222 3333"}}}]}'
exec p13 @json


-- 14.

select 
	k.IDKupac	as [OSOBA.idkupac], 
	k.Ime		as [OSOBA.ime], 
	k.prezime	as [OSOBA.prezime],
	k.email		as [OSOBA.ostalo.email],
	k.telefon	as [OSOBA.ostalo.telefon]
from Kupac as k
where k.IDKupac<11
for json path, root('OSOBE')