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: +-- Create date: +-- 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 + Index: trunk/BazaReklam/OrdersForm.cs =================================================================== --- trunk/BazaReklam/OrdersForm.cs (revision 306) +++ trunk/BazaReklam/OrdersForm.cs (revision 312) @@ -576,9 +576,10 @@ private void szukajToolStripButton_Click(object sender, EventArgs e) { - command.CommandText = query; + command.CommandText = "sp_GetReklamy"; + command.CommandType = CommandType.StoredProcedure; rEKLAMADataSet.ReklamyZestawienie.Clear(); - command.CommandText += " where 1=1 AND (r.SZER * r.WYS)<>0 "; + //command.CommandText += " where 1=1 AND (r.SZER * r.WYS)<>0 "; command.Parameters.Clear(); @@ -586,5 +587,5 @@ if (!string.IsNullOrEmpty(agencjaToolStripComboBox.Text.Trim())) { - command.CommandText += " AND A.Symbol=@agencja "; + //command.CommandText += " AND A.Symbol=@agencja "; command.Parameters.AddWithValue("@agencja", agencjaToolStripComboBox.Text.Trim()); } @@ -592,5 +593,5 @@ if (!string.IsNullOrEmpty(agentToolStripComboBox.Text.Trim())) { - command.CommandText += "AND ([symbol akwizytora] like '%' + @agent + '%' )"; + //command.CommandText += "AND ([symbol akwizytora] like '%' + @agent + '%' )"; command.Parameters.AddWithValue("@agent", agentToolStripComboBox.Text.Trim()); @@ -599,6 +600,6 @@ if (!string.IsNullOrEmpty(symbolToolStripTextBox.Text.Trim())) { - command.CommandText += "AND r.[id reklamy] like '%' + @symbol + '%' "; - command.Parameters.AddWithValue("@symbol", symbolToolStripTextBox.Text.Trim()); + //command.CommandText += "AND r.[id reklamy] like '%' + @symbol + '%' "; + command.Parameters.AddWithValue("@reklama", symbolToolStripTextBox.Text.Trim()); } @@ -613,5 +614,5 @@ } - command.CommandText += "AND DATEPART(yyyy,NR.DATA_W)=@rok "; + //command.CommandText += "AND DATEPART(yyyy,NR.DATA_W)=@rok "; command.Parameters.AddWithValue("@rok", rokToolStripTextBox.Text.Trim()); } @@ -627,5 +628,5 @@ } - command.CommandText += "AND DATEPART(month,NR.DATA_W)=@ms "; + //command.CommandText += "AND DATEPART(month,NR.DATA_W)=@ms "; command.Parameters.AddWithValue("@ms", msToolStripTextBox.Text.Trim()); } @@ -634,5 +635,5 @@ if (tytToolStripComboBox.SelectedIndex != -1) { - command.CommandText += "AND R.[tytu³]=@tytul "; + //command.CommandText += "AND R.[tytu³]=@tytul "; command.Parameters.AddWithValue("@tytul",tytToolStripComboBox.Text.Trim()); } @@ -640,53 +641,53 @@ if (!string.IsNullOrEmpty(typToolStripComboBox.Text.Trim())) { - command.CommandText += "AND R.[TYP]=@typ "; + //command.CommandText += "AND R.[TYP]=@typ "; command.Parameters.AddWithValue("@typ", typToolStripComboBox.Text.Trim()); } if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["DRCheckBox"]).Control).CheckState == CheckState.Checked) { - command.CommandText += " AND R.[ZATWIERDZONO DO DRUKU]=1 "; + //command.CommandText += " AND R.[ZATWIERDZONO DO DRUKU]=1 "; } else if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["DRCheckBox"]).Control).CheckState == CheckState.Unchecked) { - command.CommandText += " AND R.[ZATWIERDZONO DO DRUKU]=0 "; + //command.CommandText += " AND R.[ZATWIERDZONO DO DRUKU]=0 "; } if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["FWCheckBox"]).Control).CheckState == CheckState.Checked) { - command.CommandText += "AND R.[FAKTURA WYSTAWIONO]=1 "; + //command.CommandText += "AND R.[FAKTURA WYSTAWIONO]=1 "; } else if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["FWCheckBox"]).Control).CheckState == CheckState.Unchecked) { - command.CommandText += "AND R.[FAKTURA WYSTAWIONO]=0 "; + //command.CommandText += "AND R.[FAKTURA WYSTAWIONO]=0 "; } if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["KZCheckBox"]).Control).CheckState == CheckState.Checked) { - command.CommandText += " AND R.[kier_zatwierdzil]=1 "; + //command.CommandText += " AND R.[kier_zatwierdzil]=1 "; } else if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["KZCheckBox"]).Control).CheckState == CheckState.Unchecked) { - command.CommandText += " AND R.[kier_zatwierdzil]=0 "; + //command.CommandText += " AND R.[kier_zatwierdzil]=0 "; } if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["WyrCheckBox"]).Control).CheckState == CheckState.Checked) { - command.CommandText += " AND R.[wyroznienie]=1 "; + //command.CommandText += " AND R.[wyroznienie]=1 "; } else if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["WyrCheckBox"]).Control).CheckState == CheckState.Unchecked) { - command.CommandText += " AND R.[wyroznienie]=0 "; + //command.CommandText += " AND R.[wyroznienie]=0 "; } if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["BZCheckBox"]).Control).CheckState == CheckState.Checked) { - command.CommandText += " AND Z.idZamowienia IS NULL "; + //ommand.CommandText += " AND Z.idZamowienia IS NULL "; } else if (((CheckBox)((ToolStripControlHost)szukajToolStrip.Items["BZCheckBox"]).Control).CheckState == CheckState.Unchecked) { - command.CommandText += " AND Z.idZamowienia IS NOT NULL "; - } - - - command.CommandText += " order by R.[DATA ZAMÓWIENIA] desc"; + //command.CommandText += " AND Z.idZamowienia IS NOT NULL "; + } + + + //command.CommandText += " order by R.[DATA ZAMÓWIENIA] desc"; sqlDataAdapter.SelectCommand = command; @@ -1221,5 +1222,5 @@ if (File.Exists(sciezka)) { - System.Diagnostics.Process.Start(sciezka); + Process.Start(sciezka); } else @@ -1253,5 +1254,5 @@ if (File.Exists(sciezka)) { - System.Diagnostics.Process.Start(sciezka); + Process.Start(sciezka); } else