SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_adMotoRaportProwizja]
	@rok int,
	@miesiac int,
	@tytul nvarchar(100),
	@prowizjaPoz money,
    @prowizjaKat money
AS
DECLARE @Wplywy TABLE
(
	IdFaktury INT,
	NumerFaktury INT,
	Agent nvarchar(100),
	SymbolFaktury nvarchar(100),
	DataWplaty DATETIME,
	Netto MONEY,
	DataFaktury DATETIME,
	Prowizja MONEY,
	Agencja nvarchar(100)
)
DECLARE @Sprzedaz TABLE
(
	IdFaktury INT,
	NumerFaktury INT,
	Agent nvarchar(100),
	SymbolFaktury nvarchar(100),
	DataWplaty DATETIME,
	Netto MONEY,
	DataFaktury DATETIME,
	Prowizja MONEY,
	Agencja nvarchar(100)
)

INSERT INTO @Wplywy
SELECT 
ID_FAKTURY AS IdFaktury, 
NumerFaktury, 
LOWER(Symbol) AS Agent, 
NR_FK AS SymbolFaktury, 
dataWplaty AS DataWplaty, 
NETTO AS Netto, 
DATA_WYSTAWIENIA AS DataFaktury, 
ROUND(NETTO * procentProwizji, 2) AS Prowizja, 
Agencja
FROM WplywyByTytul
WHERE (YEAR(dataWplaty) = @rok) 
AND (MONTH(dataWplaty) = @miesiac) 
AND (Tytul IN (@tytul))

IF @prowizjaPoz IS NOT NULL
BEGIN
UPDATE @Wplywy
SET Prowizja=ROUND(NETTO * @prowizjaPoz, 2), Netto = NULL
WHERE Agencja='GS O/POZNAŃ'
END

IF @prowizjaKat IS NOT NULL
BEGIN
UPDATE @Wplywy
SET Prowizja=ROUND(NETTO * @prowizjaKat, 2), Netto = NULL
WHERE Agencja='GS O/KATOWICE'
END


UPDATE @Wplywy
SET Netto = NULL
WHERE MONTH(DataFaktury)<@miesiac

INSERT INTO @Sprzedaz
SELECT 
Id AS IdFaktury, 
Numer AS NumerFaktury,
Agent,
NumerFaktury AS SymbolFaktury,
NULL AS DataWplaty,
Netto,
Data AS DataFaktury,
NULL AS Prowizja,
Agencja
FROM FakturyNettoByTytul
WHERE
(YEAR(Data) = @rok) 
AND (MONTH(Data) in (@miesiac))
AND (Tytul in (@tytul))

SELECT * FROM @Wplywy 
UNION
SELECT * FROM @Sprzedaz
WHERE IdFaktury NOT IN (SELECT IdFaktury FROM @Wplywy)
ORDER BY DataWplaty, Agent, NumerFaktury
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO