CREATE PROCEDURE [dbo].[pos25_PazarDan]
(
	@Datum datetime,
	@Objekat varchar(6)
)
AS
	SET NOCOUNT ON
if exists (select * from sysobjects where id = object_id('dbo.RekDanKase') and sysstat & 0xf = 3)
	drop table dbo.RekDanKase
CREATE TABLE dbo.RekDanKase (
	IDPaz int IDENTITY(1,1) NOT NULL, 
	Datum datetime NULL ,
	Objekat nvarchar (6) NULL ,
	Kasa nvarchar (3) NOT NULL ,
	Partner nvarchar (8) NULL ,
	Dokument nvarchar (50) NOT NULL , 
	Placanje smallint NULL ,
	VrstaPlacanja nvarchar (20) NULL ,
	Kartica nvarchar (30) NULL ,
	IznosA money NULL CONSTRAINT DF_RekDanKaseIznosA default(0) ,
	PorezA money NULL CONSTRAINT DF_RekDanKasePorezA default(0) ,
	PopustA money NULL CONSTRAINT DF_RekDanKasePopustA default(0) ,
	IznosB money NULL CONSTRAINT DF_RekDanKaseIznosB default(0) ,
	PorezB money NULL CONSTRAINT DF_RekDanKasePorezB default(0) ,
	PopustB money NULL CONSTRAINT DF_RekDanKasePopustB default(0) ,
	IznosC money NULL CONSTRAINT DF_RekDanKaseIznosC default(0) ,
	PorezC money NULL CONSTRAINT DF_RekDanKasePorezC default(0) ,
	PopustC money NULL CONSTRAINT DF_RekDanKasePopustC default(0) ,
	IznosD money NULL CONSTRAINT DF_RekDanKaseIznosD default(0) ,
	PorezD money NULL CONSTRAINT DF_RekDanKasePorezD default(0) ,
	PopustD money NULL CONSTRAINT DF_RekDanKasePopustD default(0) ,
	IznosE money NULL CONSTRAINT DF_RekDanKaseIznosE default(0) ,
	PorezE money NULL CONSTRAINT DF_RekDanKasePorezE default(0) ,
	PopustE money NULL CONSTRAINT DF_RekDanKasePopustE default(0) ,
	Svega money NULL CONSTRAINT DF_RekDanKaseSvega default(0) , 
	Iznos2 money null CONSTRAINT DF_RekDanKaseIznos2 default(0) , 
	IznosTr money null CONSTRAINT DF_RekDanKaseIznosTr default(0) , 
	Avans money null CONSTRAINT DF_RekDanKaseAvans default(0),
	OtpremnicaRacun bit null CONSTRAINT DF_RekDanKaseOtpremnicaRacun default(0),
    InvoiceFiskType int null CONSTRAINT DF_RekDanKaseInvoiceFiskType default(0),
	Broj int null, 
CONSTRAINT PK_RekDanKase primary key (IDPaz)
) 
CREATE TABLE #RekDanTmp (
	IDPaz0 int IDENTITY(1,1) NOT NULL, 
	Datum datetime NULL ,
	Objekat nvarchar (6) NULL ,
	Kasa nvarchar (3) NOT NULL ,
	Partner nvarchar (8) NULL ,
	Dokument nvarchar (50) NOT NULL , 
	Placanje smallint NULL ,
	VrstaPlacanja nvarchar (20) NULL ,
	Kartica nvarchar (30) NULL ,
	IznosA money NULL default(0) ,
	PorezA money NULL default(0) ,
	PopustA money NULL default(0) ,
	IznosB money NULL default(0) ,
	PorezB money NULL default(0) ,
	PopustB money NULL default(0) ,
	IznosC money NULL default(0) ,
	PorezC money NULL default(0) ,
	PopustC money NULL default(0) ,
	IznosD money NULL default(0) ,
	PorezD money NULL default(0) ,
	PopustD money NULL default(0) ,
	IznosE money NULL default(0) ,
	PorezE money NULL default(0) ,
	PopustE money NULL default(0) ,
	Svega money NULL default(0) , 
	Iznos2 money null default(0) , 
	IznosTr money null default(0) , 
	Avans money null default(0),
	OtpremnicaRacun bit null default(0),
    InvoiceFiskType int null default(0),
	Broj int null, 
CONSTRAINT PK_RekTmp primary key (IDPaz0)
)
declare @strDat0 as nvarchar(10)
declare @strDat1 as nvarchar(10)
declare @Dat0 as datetime
declare @Dat1 as datetime

set @strDat0 = convert(nvarchar(10), @Datum, 126)
set @strDat1 = convert(nvarchar(10), DateAdd(d, 1, @Datum), 126)
set @Dat0 = convert(datetime, @strDat0 + ' 00:00:01', 102)
set @Dat1 = convert(datetime, @strDat0 + ' 23:59:59', 102)

-- Saberi totale po svakom bloku
INSERT INTO #RekDanTmp (Datum, Broj, Objekat, Kasa, Partner, Placanje, VrstaPlacanja, Kartica, OtpremnicaRacun, InvoiceFiskType, IznosA, IznosB, IznosC, IznosD, IznosE, PorezA, PorezB, PorezC, PorezD, PorezE, PopustA, PopustB, PopustC, PopustD, PopustE, Svega, Dokument)
SELECT     @Datum AS Datum, k.Broj, k.Objekat, k.Kasa, case when k.Placanje = 1 then k.Partner else '0000' end as [Partner], k.Placanje, 
			pl.VrstaPlacanja as VrstaPlacanja, Null AS Kartica, k.OtpremnicaRacun, k.InvoiceFiskType,
                      CASE WHEN st.TarifaA = N'A' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS IznosA, 
                      CASE WHEN st.TarifaA = N'B' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS IznosB,
                      CASE WHEN st.TarifaA = N'C' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS IznosC,
                      CASE WHEN st.TarifaA = N'D' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS IznosD,
                      CASE WHEN not st.TarifaA in('A','B','C','D') THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS IznosE,
                      cast(0 as money) AS PorezA,
                      cast(0 as money) AS PorezB,
                      cast(0 as money) AS PorezC,
                      cast(0 as money) AS PorezD,
                      cast(0 as money) AS PorezE,
                      CASE WHEN st.TarifaA = N'A' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena) - SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS PopustA, 
                      CASE WHEN st.TarifaA = N'B' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena) - SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS PopustB,
                      CASE WHEN st.TarifaA = N'C' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena) - SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS PopustC, 
                      CASE WHEN st.TarifaA = N'D' THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena) - SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS PopustD,
                      CASE WHEN not st.TarifaA in('A','B','C','D') THEN dbo.BankersRound(SUM(st.Kolicina * st.Cena) - SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) ELSE cast(0 as money) END AS PopustE,
		dbo.BankersRound(SUM(st.Kolicina * st.Cena * (1 - (cast(st.Rabat as Money) / 100))), 2) as Svega, case when k.Placanje = 1 then k.Faktura else N'0000' end as Dokumet
FROM         dbo.KasaFPSt st INNER JOIN
                      dbo.KasaFP k ON st.Broj = k.Broj
					  LEFT OUTER JOIN dbo.Placanje pl ON k.Placanje = pl.Placanje 
WHERE k.Objekat = @Objekat and k.ZakljucenDan <> 0  and (k.Datum between @Dat0 and @Dat1)
GROUP BY k.Broj, Objekat, k.Kasa, k.Placanje, pl.VrstaPlacanja, k.Faktura, case when k.Placanje = 1 then k.Partner else '0000' end,  k.ZakljucenDan, st.TarifaA, k.OtpremnicaRacun, k.InvoiceFiskType

-- dopis gotovinske doplate u posebnu kolonu
INSERT INTO #RekDanTmp (Datum, Objekat, Kasa, Partner, Placanje, VrstaPlacanja, Kartica, Iznos2, IznosTr, Avans, Dokument)
SELECT     @Datum as Datum, k.Objekat, k.Kasa, '0000' as Partner, k.Placanje, 
			pl.VrstaPlacanja, null AS Kartica, 
		SUM(k.Gotovina) as Iznos2, SUM(k.Iznos) as IznosTr, SUM(k.Avans) as Avans, N'0000' as Dokumet
FROM         dbo.KasaFP k LEFT OUTER JOIN dbo.Placanje pl ON k.Placanje = pl.Placanje 
WHERE k.Objekat = @Objekat and k.ZakljucenDan <> 0  
	AND k.Placanje = 3 
	and (k.Datum between @Dat0 and @Dat1)
	AND k.Gotovina <> 0
GROUP BY  k.Objekat, k.Kasa, k.Placanje, pl.VrstaPlacanja, k.ZakljucenDan


Insert into  dbo.RekDanKase (Datum, Objekat, Kasa, Partner, Dokument, Placanje, VrstaPlacanja, Kartica, IznosA, IznosB, IznosC, IznosD, IznosE, PorezA, PorezB, PorezC, PorezD, PorezE, PopustA, PopustB, PopustC, PopustD, PopustE, Svega, Iznos2, IznosTr, Avans, OtpremnicaRacun, InvoiceFiskType)
SELECT Datum, Objekat, Kasa, Partner, Dokument, Placanje, VrstaPlacanja, null Kartica, Sum(IznosA) as IznosA, Sum(IznosB) as IznosB, Sum(IznosC) as IznosC, Sum(IznosD) as IznosD, Sum(IznosE) as IznosE, Sum(PorezA) as PorezA, Sum(PorezB) as PorezB, Sum(PorezC) as PorezC, Sum(PorezD) as PorezD, Sum(PorezE) as PorezE, Sum(PopustA) as PopustA, Sum(PopustB) as PopustB, Sum(PopustC) as PopustC, Sum(PopustD) as PopustD, Sum(PopustE) as PopustE, Sum(Svega) as Svega, Sum(Iznos2) as Iznos2, Sum(IznosTr) as IznosTr, Sum(Avans) as Avans, OtpremnicaRacun, InvoiceFiskType
FROM #RekDanTmp
group by Datum, Objekat, Kasa, [Partner], Dokument, Placanje, VrstaPlacanja, OtpremnicaRacun, InvoiceFiskType
ORDER BY KASA, PLACANJE, DOKUMENT

drop table #RekDanTmp
--  azuriraj sad poreze
declare @StopaA as money, @StopaB as money, @StopaC as money
--dodato
declare @StopaD as money, @StopaE as money

set @StopaA = (select Republicki from Tarife Where TarifaA = N'A')
set @StopaB = (select Republicki from Tarife Where TarifaA = N'B')
set @StopaC = (select Republicki from Tarife Where TarifaA = N'C')
--dodato
set @StopaD = (select Republicki from Tarife Where TarifaA = N'D')
set @StopaE = (select Republicki from Tarife Where TarifaA = N'E')

set @StopaA = isnull((@StopaA * 100) / (@StopaA + 100), 0)
set @StopaB = isnull((@StopaB * 100) / (@StopaB + 100), 0)
set @StopaC = isnull((@StopaC * 100) / (@StopaC + 100), 0)
--dodato
set @StopaD = isnull((@StopaD * 100) / (@StopaD + 100), 0)
set @StopaE = isnull((@StopaE * 100) / (@StopaE + 100), 0)

set @StopaA = isnull(@StopaA / 100, 0)
set @StopaB = isnull(@StopaB / 100, 0)
set @StopaC = isnull(@StopaC / 100, 0)
--dodato
set @StopaD = isnull(@StopaD / 100, 0)
set @StopaE = isnull(@StopaE / 100, 0)


--update dbo.RekDanKase set IznosA = dbo.BankersRound(IznosA,2)
--	, IznosB = dbo.BankersRound(IznosB,2)
--	, IznosC = dbo.BankersRound(IznosC,2)
--	, IznosD = dbo.BankersRound(IznosD,2)
--	, IznosE = dbo.BankersRound(IznosE,2)

update dbo.RekDanKase set PorezA = Round(IznosA * @StopaA,2)
	, PorezB = dbo.BankersRound(IznosB * @StopaB,2)
	, PorezC = dbo.BankersRound(IznosC * @StopaC,2)
	, PorezD = dbo.BankersRound(IznosD * @StopaD,2)
	, PorezE = dbo.BankersRound(IznosE * @StopaE,2)
