Index: trunk/SQL/StoredProcedures/sp_GetReklamy.txt
===================================================================
--- trunk/SQL/StoredProcedures/sp_GetReklamy.txt (revision 312)
+++ trunk/SQL/StoredProcedures/sp_GetReklamy.txt (revision 312)
@@ -0,0 +1,112 @@
+
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+
+-- =============================================
+-- Author:		<Author,,Name>
+-- Create date: <Create Date,,>
+-- Description:	<Description,,>
+-- =============================================
+ALTER PROCEDURE [dbo].[sp_GetReklamy] 
+@agencja nvarchar(100) = NULL,
+@agent nvarchar(100) = NULL,
+@reklama nvarchar(100) = NULL,
+@rok int = NULL,
+@ms int = NULL,
+@tytul nvarchar(100) = NULL,
+@typ nvarchar(100) = NULL,
+@zd bit = NULL,
+@fw bit = NULL,
+@kz bit = NULL,
+@wy bit = NULL,
+@bz bit = NULL
+
+AS
+BEGIN
+
+DECLARE @query nvarchar(4000)
+
+
+
+set @query = '
+SELECT TOP 1000 
+--R.ReklamaID
+R.*, 
+O.wyd_od AS OD, 
+O.wyd_do AS DO, 
+DATEPART(year, NR.DATA_W), 
+DATEPART(month, NR.DATA_W), 
+NR.DATA_W AS ''1emisja'', 
+A.Symbol AS agencja, 
+(r.[CENA JEDN] - r.[RABAT WARTOŒÆ]) / r.SZER * r.WYS AS [CENA MODU£U], 
+NM.CENA_MIN, 
+NM.CENA, 
+K.firstname, 
+Z.idZamowienia  
+FROM dbo.AGENCI A2 
+JOIN dbo.AGENCJE A ON A.Id_agencji = A2.ID_AGENCJI 
+RIGHT JOIN REKLAMA R ON A2.Symbol = R.[SYMBOL AKWIZYTORA] 
+LEFT JOIN dbo.VIEW_REKLAMA_OD_DO_DATY O ON R.ReklamaID = O.ReklamaId 
+LEFT JOIN NR ON R.TYTU£ = NR.TYT AND O.wyd_od = NR.NRW -- OR O.wyd_do = NR.NRW) 
+LEFT OUTER JOIN [NAZWY MODU£ÓW] NM ON r.MOD_TYP = NM.MOD_TYP 
+LEFT OUTER JOIN dbo.KLIENCI AS K ON R.customerId=K.CustomerId 
+LEFT OUTER JOIN dbo.Zamowienia AS Z ON R.idZamowienia=Z.idZamowienia 
+where 1=1 and (r.SZER * r.WYS)<>0 '
+
+if NOT (@agencja IS NULL)
+SET @query = @query + ' AND A.Symbol=''' + @agencja + ''' '
+
+if NOT (@agent IS NULL)
+SET @query = @query + ' AND [symbol akwizytora] like ''%' + @agent + '%'' '
+
+if NOT (@reklama IS NULL)
+SET @query = @query + ' AND r.[id reklamy] like ''%' + @reklama + '%'' '
+
+if NOT (@rok IS NULL)
+SET @query = @query + ' AND DATEPART(year,NR.DATA_W)=' + CAST(@rok as nvarchar) + ' '
+
+if NOT (@ms IS NULL)
+--SET @query = @query + ' AND DATEPART(month,NR.DATA_W)=' + CAST(@ms as nvarchar) + ' '
+BEGIN
+SET @query = @query + ' AND DATEPART(month, O.data_od)<=' + CAST(@ms as nvarchar) + ' '
+SET @query = @query + ' AND DATEPART(month, O.data_do)>=' + CAST(@ms as nvarchar) + ' '
+END
+
+if NOT (@tytul IS NULL)
+SET @query = @query + ' AND R.[tytu³]=''' + @tytul + ''' '
+
+if NOT (@typ IS NULL)
+SET @query = @query + ' AND R.[TYP]=''' + @typ + ''' '
+
+if (NOT (@fw IS NULL) AND NOT(@ms IS NULL) AND NOT(@rok IS NULL))
+BEGIN
+SET @query = @query + ' AND R.ReklamaId IN '
+SET @query = @query + ' (SELECT DISTINCT U.reklamaid '
+SET @query = @query + ' FROM [Uka¿e siê w Nr] U '
+SET @query = @query + ' INNER JOIN Reklama R on R.ReklamaID=U.ReklamaID '
+SET @query = @query + ' INNER JOIN nr N on u.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] '
+SET @query = @query + ' WHERE DATEPART(MONTH, N.Data_W)=' + CAST(@ms as nvarchar)
+SET @query = @query + ' AND DATEPART(YEAR, N.Data_W)=' + CAST(@rok as nvarchar)
+SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) + ') '
+END
+
+
+print @query
+
+
+--SET @query = 'DECLARE @result TABLE ( id INT PRIMARY KEY ) INSERT INTO @result ' + @query
+--SET @query = @query + ' SELECT * FROM @result'
+
+EXECUTE sp_executesql @query
+RETURN
+
+END
+GO
+
+SET ANSI_NULLS OFF
+GO
+SET QUOTED_IDENTIFIER OFF
+GO
+