Pokaż
Ignoruj:
Data:
2009-07-31 10:46:09 (17 years ago)
Autor:
marek
Opis:

re #195 - merged with latest trunk

Lokalizacja:
branches/Emisje
Pliki:
2 zmodyfikowane
1 skopiowane

Legenda:

Bez zmian
Dodane
Usunięte

  • branches/Emisje

    • Property svn:mergeinfo

      •  

        old new  
        11/branches/ReklamaReorganizacja:568-731 
         2/trunk:774-795 

  • branches/Emisje/SQL/StoredProcedures/sp_GetReklamy.txt

    r732 r796  
    3030DECLARE @query nvarchar(4000) 
    3131 
     32SET @query = '' 
     33 
     34IF (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL)) 
     35BEGIN 
     36 
     37SET @query = @query +  
     38' 
     39DECLARE @startDate DATETIME  
     40DECLARE @endDate DATETIME  
     41 
     42SELECT @startDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1,0) + 0  
     43SELECT @endDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1+1,0) + 0  
     44 
     45' 
     46END 
    3247 
    3348 
    34 set @query = ' 
     49SET @query = @query + ' 
     50 
    3551SELECT TOP 1000  
    36 --R.ReklamaID 
    3752R.*,  
    3853O.wyd_od AS OD,  
    3954O.wyd_do AS DO,  
    40 DATEPART(year, NR.DATA_W),  
    41 DATEPART(month, NR.DATA_W),  
    42 NR.DATA_W AS ''1emisja'',  
     55DATEPART(year, N.DATA_W),  
     56DATEPART(month, N.DATA_W),  
     57N.DATA_W AS ''1emisja'',  
    4358A.Symbol AS agencja,  
    4459(r.[CENA JEDN] - r.[RABAT WARTOŒÆ]) / r.SZER * r.WYS AS [CENA MODU£U],  
    … …  
    5166RIGHT JOIN REKLAMA R ON A2.Symbol = R.[SYMBOL AKWIZYTORA]  
    5267LEFT JOIN dbo.VIEW_REKLAMA_OD_DO_DATY O ON R.ReklamaID = O.ReklamaId  
    53 LEFT JOIN NR ON R.TYTU£ = NR.TYT AND O.wyd_od = NR.NRW -- OR O.wyd_do = NR.NRW)  
     68LEFT JOIN dbo.NR N ON R.TYTU£ = N.TYT AND O.wyd_od = N.NRW  
    5469LEFT OUTER JOIN [NAZWY MODU£ÓW] NM ON r.MOD_TYP = NM.MOD_TYP AND r.[Tytu³] = NM.Tytul 
    5570LEFT OUTER JOIN dbo.KLIENCI AS K ON R.customerId=K.CustomerId  
    … …  
    5772where 1=1 and (r.SZER * r.WYS)<>0 ' 
    5873 
    59 if NOT (@agencja IS NULL) 
     74IF NOT (@agencja IS NULL) 
    6075SET @query = @query + ' AND A.Symbol=''' + @agencja + ''' ' 
    6176 
    62 if NOT (@agent IS NULL) 
     77IF NOT (@agent IS NULL) 
    6378SET @query = @query + ' AND [symbol akwizytora] like ''%' + @agent + '%'' ' 
    6479 
    65 if NOT (@reklama IS NULL) 
     80IF NOT (@reklama IS NULL) 
    6681SET @query = @query + ' AND r.[id reklamy] like ''%' + @reklama + '%'' ' 
    6782 
    68 if NOT (@rok IS NULL) 
    69 SET @query = @query + ' AND DATEPART(year,NR.DATA_W)=' + CAST(@rok as nvarchar) + ' ' 
     83IF NOT (@rok IS NULL) 
     84SET @query = @query + ' AND DATEPART(year, N.DATA_W)=' + CAST(@rok as nvarchar) + ' ' 
    7085 
    71 if NOT (@ms IS NULL) 
    72 BEGIN 
    73 SET @query = @query + ' AND DATEPART(month, O.data_od)<=' + CAST(@ms as nvarchar) + ' ' 
    74 SET @query = @query + ' AND DATEPART(month, O.data_do)>=' + CAST(@ms as nvarchar) + ' ' 
    75 END 
    76  
    77 if NOT (@tytul IS NULL) 
    78 SET @query = @query + ' AND R.[tytu³]=''' + @tytul + ''' ' 
    79  
    80 if NOT (@typ IS NULL) 
    81 SET @query = @query + ' AND R.[TYP]=''' + @typ + ''' ' 
    82  
    83 if NOT (@promocja IS NULL) 
    84 SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' ' 
    85  
    86 if (NOT (@fw IS NULL) AND NOT(@ms IS NULL) AND NOT(@rok IS NULL)) 
     86IF (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL)) 
    8787BEGIN 
    8888SET @query = @query + ' AND R.ReklamaId IN ' 
    … …  
    9090SET @query = @query + ' FROM [Uka¿e siê w Nr] U ' 
    9191SET @query = @query + ' INNER JOIN Reklama R on R.ReklamaID=U.ReklamaID ' 
    92 SET @query = @query + ' INNER JOIN nr N on u.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] ' 
    93 SET @query = @query + ' WHERE DATEPART(MONTH, N.Data_W)=' + CAST(@ms as nvarchar) 
    94 SET @query = @query + ' AND DATEPART(YEAR, N.Data_W)=' + CAST(@rok as nvarchar) 
    95 SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) + ') ' 
     92SET @query = @query + ' INNER JOIN dbo.Nr N on U.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] ' 
     93SET @query = @query + ' WHERE N.Data_W>=@startDate AND N.Data_W<@endDate ' 
     94IF (NOT @fw IS NULL) 
     95SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) 
     96IF NOT (@agent IS NULL) 
     97SET @query = @query + ' AND R.[symbol akwizytora] like ''%' + @agent + '%'' ' 
     98 
     99IF NOT (@tytul IS NULL) 
     100SET @query = @query + ' AND R.[tytu³]=''' + @tytul + ''' ' 
     101 
     102IF NOT (@typ IS NULL) 
     103SET @query = @query + ' AND R.[TYP]=''' + @typ + ''' ' 
     104 
     105IF NOT (@promocja IS NULL) 
     106SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' ' 
     107 
     108IF NOT (@zd IS NULL) 
     109SET @query = @query + ' AND R.[Zatwierdzono do druku]=' + CAST(@zd as nvarchar) + ' ' 
     110 
     111IF NOT (@kz IS NULL) 
     112SET @query = @query + ' AND R.[Kier_zatwierdzil]=' + CAST(@kz as nvarchar) + ' ' 
     113 
     114IF NOT (@wy IS NULL) 
     115SET @query = @query + ' AND R.[wyroznienie]=' + CAST(@wy as nvarchar) + ' ' 
     116 
     117SET @query = @query + ' ) ' 
    96118END 
     119 
     120IF NOT (@tytul IS NULL) 
     121SET @query = @query + ' AND R.[tytu³]=''' + @tytul + ''' ' 
     122 
     123IF NOT (@typ IS NULL) 
     124SET @query = @query + ' AND R.[TYP]=''' + @typ + ''' ' 
     125 
     126IF NOT (@promocja IS NULL) 
     127SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' ' 
    97128 
    98129IF NOT (@zd IS NULL) 
    … …  
    114145 
    115146 
    116 print @query 
     147--PRINT @query 
    117148 
    118149