-----------------------------------------------------------------------------------
-- Zadatak 1.
-----------------------------------------------------------------------------------
create FUNCTION UkupnaKolicina
(
	@ID int
)
RETURNS int
AS
BEGIN
	DECLARE @Ukupno int
	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
	RETURN @Ukupno
END
GO

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

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

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

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

	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
-----------------------------------------------------------------------------------
-- Zadatak 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

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

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

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

CREATE NONCLUSTERED INDEX i1 ON dbo.Racun(KupacID) INCLUDE (DatumIzdavanja)
GO
-----------------------------------------------------------------------------------
-- Zadatak 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('Zhu')

SELECT * 
FROM DohvatiOsobe('Zhu') AS os
INNER JOIN Racun AS r ON os.IDKupac = r.KupacID
GO
-----------------------------------------------------------------------------------
-- Zadatak 5.
-----------------------------------------------------------------------------------
CREATE FUNCTION DohvatiRacune
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
	SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
	FROM Racun AS r
	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacune('20040601', '20040603')
GO

ALTER FUNCTION DohvatiRacune
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
	SELECT r.BrojRacuna, CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, k.Ime, k.Prezime
	FROM Racun AS r
	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacune('20040601', '20040603')
GO
-----------------------------------------------------------------------------------
-- Zadatak 6.
-----------------------------------------------------------------------------------
CREATE FUNCTION DohvatiRacuneSloz
(
    @D1 datetime, 
    @D2 datetime
)
RETURNS @RetVal TABLE ( BrojRacuna nvarchar(25), DatumIzdavanja datetime, Ime nvarchar(50), Prezime nvarchar(50) )
AS
BEGIN
	INSERT INTO @RetVal (BrojRacuna, DatumIzdavanja, Ime, Prezime)
	SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
	FROM Racun AS r
	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
	
	RETURN
END
GO

SELECT * FROM DohvatiRacuneSloz('20040601', '20040603')
GO
-----------------------------------------------------------------------------------
-- Zadatak 7.
-----------------------------------------------------------------------------------
CREATE FUNCTION F4
(
	@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 F4(NULL)
SELECT * FROM F4(3000)
GO
-----------------------------------------------------------------------------------
-- Zadatak 8.
-----------------------------------------------------------------------------------
CREATE FUNCTION GetDatume
(
	@Datum datetime
)
RETURNS @RetVal TABLE ( Datum datetime )
AS
BEGIN
	DECLARE @i int = 1
	WHILE @i <= 5 BEGIN
		INSERT INTO @RetVal (Datum)
		VALUES (Dateadd(day, @i, @Datum))
		
		SET @i += 1
	END
	
	RETURN
END
GO

SELECT * FROM GetDatume(GETDATE())
SELECT * FROM GetDatume('20111229')
GO
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------