root/trunk/SQL/StoredProcedures/sp_GetReklamy.txt @ 336

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:              marek
-- Create date: 2009-02-08
-- Description: Wyszukuje reklamy odpowiedniodo podanych parametrow
-- =============================================
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)
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

IF NOT (@zd IS NULL)
SET @query = @query + ' AND R.[Zatwierdzono do druku]=' + CAST(@zd as nvarchar) + ' '

IF NOT (@kz IS NULL)
SET @query = @query + ' AND R.[Kier_zatwierdzil]=' + CAST(@kz as nvarchar) + ' '

IF NOT (@wy IS NULL)
SET @query = @query + ' AND R.[wyroznienie]=' + CAST(@wy as nvarchar) + ' '

IF NOT (@bz IS NULL)
BEGIN
IF @bz=1
SET @query = @query + ' AND R.idZamowienia IS NULL '
ELSE
SET @query = @query + ' AND R.idZamowienia IS NOT NULL '
END


--print @query


EXECUTE sp_executesql @query
RETURN

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO