Index: branches/Emisje/SQL/StoredProcedures/sp_GetZestawienieZamowien.txt =================================================================== --- branches/Emisje/SQL/StoredProcedures/sp_GetZestawienieZamowien.txt (revision 796) +++ branches/Emisje/SQL/StoredProcedures/sp_GetZestawienieZamowien.txt (revision 796) @@ -0,0 +1,145 @@ + +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO + +-- ============================================= +-- Author: +-- Create date: +-- Description: +-- ============================================= +ALTER PROCEDURE [dbo].[sp_GetZestawienieZamowien] + -- Add the parameters for the stored procedure here + @agencja NVARCHAR(50)=NULL, + @nrZamowienia INT=NULL, + @rokZamowienia INT=NULL, + @miesiacZamowienia INT=NULL, + @kodAgenta NVARCHAR(3)=NULL, + @kodKlienta NVARCHAR(10)=NULL + +AS +BEGIN + +SET NOCOUNT ON; + +DECLARE @query nvarchar(4000) + +SET @query = ' +DECLARE @ZestawienieZamowien TABLE +( + idZamowienia INT, + agencja NVARCHAR(50), + agent NVARCHAR(50), + idKlienta INT, + nrZamowienia INT, + rokZamowienia INT, + kodAgenta NVARCHAR(3), + nrZamowieniaExt NVARCHAR(76), + iloscReklam INT, + wartoscNetto FLOAT, + kwotaWalutaBrutto float, + data_od SMALLDATETIME, + data_od_bez_FK SMALLDATETIME, + nettoZafakturowane MONEY, + DoZafakturowania FLOAT, + kodKlienta NVARCHAR(10), + Brutto_Euro_Miano NVARCHAR(4) +) + +DECLARE @SumaNaNastepnejFakturze TABLE +( + idZamowienia INT, + SumaNaNastepnejFakturze DECIMAL(18,2) +) + +INSERT INTO @ZestawienieZamowien +SELECT TOP 1000 idZamowienia, agencja, agent, idKlienta, nrZamowienia, rokZamowienia, + kodAgenta, nrZamowieniaExt, iloscReklam, wartoscNetto, kwotaWalutaBrutto, data_od, data_od_bez_FK, + nettoZafakturowane, DoZafakturowania, kodKlienta, Brutto_Euro_Miano + FROM VIEW_ZESTAWIENIE_ZAMOWIEN_NOWE WHERE 1=1 ' + +IF NOT (@agencja IS NULL) +SET @query = @query + ' AND agencja=''' + @agencja + ''' ' + +IF NOT (@nrZamowienia IS NULL) +SET @query = @query + ' AND nrZamowienia=' + CAST(@nrZamowienia as nvarchar) + ' ' + +IF NOT (@rokZamowienia IS NULL) +SET @query = @query + ' AND rokZamowienia=' + CAST(@rokZamowienia as nvarchar) + ' ' + +IF NOT (@miesiacZamowienia IS NULL) +SET @query = @query + ' AND (DATEPART(MONTH, data_od)=' + CAST(@miesiacZamowienia as nvarchar) + ' OR DATEPART(MONTH, data_od_bez_FK)=' + CAST(@miesiacZamowienia as nvarchar) + ') ' + +IF NOT (@kodAgenta IS NULL) +SET @query = @query + ' AND kodAgenta=''' + @kodAgenta + ''' ' + +IF NOT (@kodKlienta IS NULL) +SET @query = @query + ' AND kodKlienta=''' + @kodKlienta + ''' ' + +IF (NOT (@rokZamowienia IS NULL)) AND (NOT (@miesiacZamowienia IS NULL)) +BEGIN + +SET @query = @query + ' + DECLARE @startDate DATETIME + DECLARE @endDate DATETIME + + SELECT @startDate=dateadd(yy,(' + CAST(@rokZamowienia as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@miesiacZamowienia as nvarchar) + '-1,0) + 0 + SELECT @endDate=dateadd(yy,(' + CAST(@rokZamowienia as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@miesiacZamowienia as nvarchar) + '-1+3,0) + 0 + + INSERT INTO @SumaNaNastepnejFakturze + SELECT Z.idZamowienia, SUM(ROUND((R.[Cena Jedn] * (1-R.Rabat)), 2)) AS SumaFaktura FROM Reklama R + INNER JOIN Zamowienia Z ON R.idZamowienia=Z.idZamowienia + INNER JOIN [Uka¿e siê w nr] U ON U.ReklamaId=R.ReklamaId + INNER JOIN Nr N ON N.Tyt=R.[Tytu³] AND N.NRW=U.[Nr Wydania] + WHERE + Z.rodzajFakturowania=0 + AND U.Status=0 + AND U.Zafakturowana=0 + AND YEAR(N.Data_W)=' + CAST(@rokZamowienia as nvarchar) + ' AND MONTH(N.Data_W)=' + CAST(@miesiacZamowienia as nvarchar) + ' + GROUP BY Z.idZamowienia + UNION + SELECT Z.idZamowienia, SUM(ROUND((R.[Cena Jedn] * (1-R.Rabat)), 2)) AS SumaFaktura FROM Reklama R + INNER JOIN Zamowienia Z ON R.idZamowienia=Z.idZamowienia + INNER JOIN [Uka¿e siê w nr] U ON U.ReklamaId=R.ReklamaId + INNER JOIN Nr N ON N.Tyt=R.[Tytu³] AND N.NRW=U.[Nr Wydania] + WHERE + Z.rodzajFakturowania=1 + AND U.Status=0 + AND U.Zafakturowana=0 + AND N.Data_W>=@startDate AND N.Data_W<@endDate + GROUP BY Z.idZamowienia + UNION + SELECT Z.idZamowienia, SUM(ROUND((R.[Cena Jedn] * (1-R.Rabat)), 2)) AS SumaFaktura FROM Reklama R + INNER JOIN Zamowienia Z ON R.idZamowienia=Z.idZamowienia + INNER JOIN [Uka¿e siê w nr] U ON U.ReklamaId=R.ReklamaId + INNER JOIN Nr N ON N.Tyt=R.[Tytu³] AND N.NRW=U.[Nr Wydania] + WHERE + Z.rodzajFakturowania=2 + AND U.Status=0 + AND U.Zafakturowana=0 + GROUP BY Z.idZamowienia + ' +END + + + + +SET @query = @query + ' SELECT ZZ.*, SNF.SumaNaNastepnejFakturze FROM @ZestawienieZamowien ZZ ' +SET @query = @query + ' LEFT OUTER JOIN @SumaNaNastepnejFakturze SNF ON SNF.idZamowienia=ZZ.idZamowienia ' +SET @query = @query + ' ORDER BY ZZ.data_od_bez_FK, ZZ.nrZamowienia, ZZ.kodKlienta ' + +--PRINT @query + + +EXECUTE sp_executesql @query +RETURN + +END +GO + +SET ANSI_NULLS OFF +GO +SET QUOTED_IDENTIFIER OFF +GO + Index: branches/Emisje/SQL/StoredProcedures/sp_GetReklamy.txt =================================================================== --- branches/Emisje/SQL/StoredProcedures/sp_GetReklamy.txt (revision 732) +++ branches/Emisje/SQL/StoredProcedures/sp_GetReklamy.txt (revision 796) @@ -30,15 +30,30 @@ 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 = ' +SET @query = @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'', +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], @@ -51,5 +66,5 @@ 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 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 @@ -57,32 +72,17 @@ where 1=1 and (r.SZER * r.WYS)<>0 ' -if NOT (@agencja IS NULL) +IF NOT (@agencja IS NULL) SET @query = @query + ' AND A.Symbol=''' + @agencja + ''' ' -if NOT (@agent IS NULL) +IF NOT (@agent IS NULL) SET @query = @query + ' AND [symbol akwizytora] like ''%' + @agent + '%'' ' -if NOT (@reklama IS NULL) +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 (@rok IS NULL) +SET @query = @query + ' AND DATEPART(year, N.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 (@promocja IS NULL) -SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' ' - -if (NOT (@fw IS NULL) AND NOT(@ms IS NULL) AND NOT(@rok IS NULL)) +IF (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL)) BEGIN SET @query = @query + ' AND R.ReklamaId IN ' @@ -90,9 +90,40 @@ 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) + ') ' +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) @@ -114,5 +145,5 @@ -print @query +--PRINT @query