-- 1.
CREATE FUNCTION UkupnaKolicina
(
	@IDProizvod int
)
RETURNS int
AS
BEGIN
	DECLARE @Ukupno int

	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod

	RETURN @Ukupno
END
GO

DECLARE @Rez int
SET @Rez = dbo.UkupnaKolicina(776)
SELECT @Rez

SELECT
	Naziv,
	Boja,
	dbo.UkupnaKolicina(IDProizvod) AS Prodano
FROM Proizvod
GO

ALTER FUNCTION UkupnaKolicina
(
	@IDProizvod int
)
RETURNS int
AS
BEGIN
	DECLARE @Ukupno int

	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod

	RETURN 
		CASE
			WHEN @Ukupno IS NOT NULL THEN @Ukupno
			ELSE 0
		END
END
GO

SELECT
	Naziv,
	Boja,
	dbo.UkupnaKolicina(IDProizvod) AS Prodano
FROM Proizvod
GO

CREATE INDEX ix1 ON Stavka (ProizvodID) INCLUDE (Kolicina)

SELECT
	Naziv,
	Boja,
	dbo.UkupnaKolicina(IDProizvod) AS Prodano
FROM Proizvod
GO

-- 2.
CREATE FUNCTION Skrati
(
	@s nvarchar(max)
)
RETURNS nvarchar(10)
AS
BEGIN
	RETURN CASE
		WHEN LEN(@s) <= 10 THEN @s
		ELSE SUBSTRING(@s, 1, 7) + '...'
	END
END
GO

SELECT dbo.Skrati('Zvonko')
SELECT dbo.Skrati('Zvonko Telefonko')

SELECT 
	Naziv,
	dbo.Skrati(Naziv) AS NazivSkraceni
FROM Proizvod
GO

-- 3.
CREATE FUNCTION GetNajnoviji
(
	@IDKupac int
)
RETURNS datetime
AS
BEGIN
	DECLARE @Datum datetime
	
	SELECT TOP 1 @Datum = DatumIzdavanja FROM Racun
	WHERE KupacID = @IDKupac
	ORDER BY DatumIzdavanja DESC
	
	RETURN @Datum
END
GO

SELECT 
	IDKupac,
	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
FROM Kupac
GO

CREATE NONCLUSTERED INDEX ix2 ON Racun(KupacID) INCLUDE (DatumIzdavanja)
GO

SELECT 
	IDKupac,
	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
FROM Kupac
GO

-- 4.
CREATE FUNCTION DohvatiOsobe
(
    @PrezimeLike nvarchar(50)
)
RETURNS TABLE
AS
RETURN
    SELECT IDKupac, Ime, Prezime
    FROM Kupac
    WHERE Prezime LIKE @PrezimeLike + '%'
GO

SELECT * FROM DohvatiOsobe('Ac')
GO

SELECT * 
FROM DohvatiOsobe('Ac') AS o
INNER JOIN Racun AS r ON o.IDKupac = r.KupacID
GO

-- 5.
CREATE FUNCTION DohvatiRacuneIzmedju
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
	SELECT *
	FROM Racun AS r
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
GO

ALTER FUNCTION DohvatiRacuneIzmedju
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
	SELECT 
		IDRacun, 
		CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, 
		BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
	FROM Racun AS r
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
GO

-- 6.
CREATE FUNCTION DohvatiRacuneIzmedjuSlozena
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS @rez TABLE
(
	IDRacun int, 
	DatumIzdavanja datetime, 
	BrojRacuna nvarchar(25), 
	KupacID int, 
	KomercijalistID int, 
	KreditnaKarticaID int, 
	Komentar nvarchar(128)	
)
AS
BEGIN
	
	INSERT INTO @rez (IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar)
	SELECT IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
	FROM Racun AS r
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
	
	RETURN
END
GO

SELECT * FROM DohvatiRacuneIzmedjuSlozena('20021111', '20030315')
GO

-- 7.
CREATE FUNCTION Slozena13
(
	@Cijena money
)
RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
AS
BEGIN
	IF @Cijena IS NULL BEGIN
		INSERT INTO @rez (Naziv, Cijena)
		SELECT Naziv, CijenaBezPDV FROM Proizvod
	END
	ELSE BEGIN
		INSERT INTO @rez (Naziv, Cijena)
		SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
	END
	
	RETURN
END
GO

SELECT * FROM Slozena13(NULL)
SELECT * FROM Slozena13(2300)
GO

