root/branches/Emisje/SQL/StoredProcedures/sp_GetReklamy.txt @ 796

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,
@promocja int = NULL,
@zd bit = NULL,
@fw bit = NULL,
@kz bit = NULL,
@wy bit = NULL,
@bz bit = NULL

AS
BEGIN

DECLARE @query nvarchar(4000)

SET @query = ''

IF (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL))
BEGIN

SET @query = @query + 
'
DECLARE @startDate DATETIME 
DECLARE @endDate DATETIME 

SELECT @startDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1,0) + 0 
SELECT @endDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1+1,0) + 0 

'
END


SET @query = @query + '

SELECT TOP 1000 
R.*, 
O.wyd_od AS OD, 
O.wyd_do AS DO, 
DATEPART(year, N.DATA_W), 
DATEPART(month, N.DATA_W), 
N.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 dbo.NR N ON R.TYTU£ = N.TYT AND O.wyd_od = N.NRW 
LEFT OUTER JOIN [NAZWY MODU£ÓW] NM ON r.MOD_TYP = NM.MOD_TYP AND r.[Tytu³] = NM.Tytul
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, N.DATA_W)=' + CAST(@rok as nvarchar) + ' '

IF (NOT (@rok IS NULL)) AND (NOT (@ms 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 dbo.Nr N on U.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] '
SET @query = @query + ' WHERE N.Data_W>=@startDate AND N.Data_W<@endDate '
IF (NOT @fw IS NULL)
SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar)
IF NOT (@agent IS NULL)
SET @query = @query + ' AND R.[symbol akwizytora] like ''%' + @agent + '%'' '

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 (@promocja IS NULL)
SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' '

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) + ' '

SET @query = @query + ' ) '
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 (@promocja IS NULL)
SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' '

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