Index: branches/ReklamaReorganizacja/SQL/StoredProcedures/sp_GetClientsWithNoContact.txt =================================================================== --- branches/ReklamaReorganizacja/SQL/StoredProcedures/sp_GetClientsWithNoContact.txt (revision 671) +++ branches/ReklamaReorganizacja/SQL/StoredProcedures/sp_GetClientsWithNoContact.txt (revision 671) @@ -0,0 +1,43 @@ +-- ================================================ +-- Template generated from Template Explorer using: +-- Create Procedure (New Menu).SQL +-- +-- Use the Specify Values for Template Parameters +-- command (Ctrl-Shift-M) to fill in the parameter +-- values below. +-- +-- This block of comments will not be included in +-- the definition of the procedure. +-- ================================================ +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +-- ============================================= +-- Author: +-- Create date: +-- Description: +-- ============================================= +CREATE PROCEDURE sp_GetClientsWithNoContact + -- Add the parameters for the stored procedure here + @agencyId int, + @numberOfDaysStart int, + @numberOfDaysEnd int +AS +BEGIN + -- SET NOCOUNT ON added to prevent extra result sets from + -- interfering with SELECT statements. + SET NOCOUNT ON; + + select top 2000 K.* from KLIENCI K + left join dbo.KONTAKTY K2 on K.CustomerId = K2.CustomerID + left join dbo.AGENCI A on K.UserName = A.Symbol + group by K.NameInMagazine, K.AgentExpo, K.kodKlienta, K.CustomerID, K.FirstName, K.LastName, K.OrganizationName, + K.Address, K.City, K.State, K.PostalCode, K.Country, K.Nip, K.ContactName, K.PhoneNumber, K.FaxNumber, + K.Note, K.data, K.Adres_Fkatura, K.Adres_Kor, K.Platnik_VAT, K.Aktywny, + K.Email, K.UserName, K.Last_Modify, K.Modify_User, K.http, K.VIES, K.regon, K.krs, K.osw_nr,K.osw_wazne_do,K.Old_ID, K.NipKraj, A.Id_agencji + HAVING datediff(day,max(K2.data),getdate()) > @numberOfDaysStart AND datediff(day,max(K2.data),getdate()) < @numberOfDaysEnd + AND A.Id_agencji = @agencyId order by datediff(day,max(K2.data),getdate()) desc + +END +GO Index: branches/ReklamaReorganizacja/BazaReklam/ClientsForm.cs =================================================================== --- branches/ReklamaReorganizacja/BazaReklam/ClientsForm.cs (revision 646) +++ branches/ReklamaReorganizacja/BazaReklam/ClientsForm.cs (revision 671) @@ -8,5 +8,4 @@ using System.IO; using System.Text.RegularExpressions; -using Baza_Reklam.Classes; using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces; @@ -494,4 +493,5 @@ command.CommandText += " where K.UserName=@agent order by data desc"; command.Parameters.AddWithValue("@agent", User.Instance().Symbol_agenta); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -516,4 +516,5 @@ command.CommandText += " where K.UserName=@agent AND K.Aktywny=1 order by K.data desc"; command.Parameters.AddWithValue("@agent", User.Instance().Symbol_agenta); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -528,4 +529,5 @@ command.CommandText += " where K.AgentExpo=@agent AND K.Aktywny=1 order by K.data desc"; command.Parameters.AddWithValue("@agent", User.Instance().Symbol_agenta); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -540,4 +542,5 @@ command.CommandText += " where K.UserName=@agent AND K.Aktywny=0 order by K.data desc"; command.Parameters.AddWithValue("@agent", User.Instance().Symbol_agenta); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -552,4 +555,5 @@ command.CommandText += " where K.AgentExpo=@agent AND K.Aktywny=0 order by K.data desc"; command.Parameters.AddWithValue("@agent", User.Instance().Symbol_agenta); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -567,4 +571,5 @@ command.CommandText += " left join dbo.AGENCI A on K.UserName = A.Symbol where A.Id_agencji = @idAgencji"; command.Parameters.AddWithValue("@idAgencji", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -579,4 +584,5 @@ command.CommandText += " on K.UserName = A.Symbol where K2.ID_KL_KLIENCI is null AND A.Id_agencji = @idAgencji"; command.Parameters.AddWithValue("@idAgencji", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -591,4 +597,5 @@ command.CommandText += " on K.UserName = A.Symbol where K.aktywny=1 AND KT.id_klienci_tytul is null AND A.Id_agencji = @idAgencji"; command.Parameters.AddWithValue("@idAgencji", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -619,4 +626,5 @@ command.CommandText += " on K.UserName = A.Symbol where K2.ID_KL_KLIENCI is null AND A.Symbol = @symAgenta"; command.Parameters.AddWithValue("@symAgenta", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -631,4 +639,5 @@ command.CommandText += " on K.UserName = A.Symbol where K.aktywny=1 AND KT.id_klienci_tytul is null AND A.Symbol = @symAgenta"; command.Parameters.AddWithValue("@symAgenta", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -654,4 +663,5 @@ command.CommandText += " where UserName=@agent"; command.Parameters.AddWithValue("@agent", e.Node.Name); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); clientsDataGridView.Refresh(); @@ -663,23 +673,14 @@ rEKLAMADataSet.KLIENCI.Clear(); - command.CommandText += "left join dbo.KONTAKTY K2 on K.CustomerId = K2.CustomerID " + - "left join dbo.AGENCI A on K.UserName = A.Symbol " + - "group by K.AgentExpo, K.kodKlienta, K.CustomerID, K.FirstName, K.LastName, K.OrganizationName, " + - "K.Address, K.City, K.State, K.PostalCode, K.Country, K.Nip, K.ContactName, K.PhoneNumber, K.FaxNumber," + - "K.Note, K.data, K.Adres_Fkatura, K.Adres_Kor, K.Platnik_VAT, K.Aktywny, " + - "K.Email, K.UserName, K.Last_Modify, K.Modify_User, K.http, K.VIES, K.regon, K.krs, K.osw_nr,K.osw_wazne_do,K.Old_ID, K.NipKraj, A.Id_agencji " + - "HAVING datediff(day,max(K2.data),getdate()) > @param1 AND datediff(day,max(K2.data),getdate()) <@param2 " + - "AND A.Id_agencji = @idAgencji order by datediff(day,max(K2.data),getdate()) desc"; - command.Parameters.AddWithValue("@idAgencji", e.Node.Parent.Parent.Name); - command.Parameters.AddWithValue("@param1", e.Node.Name); - + + command.CommandText = "sp_GetClientsWithNoContact"; + command.CommandType = CommandType.StoredProcedure; + + command.Parameters.AddWithValue("@agencyId", e.Node.Parent.Parent.Name); + command.Parameters.AddWithValue("@numberOfDaysStart", e.Node.Name); if (e.Node.NextNode != null) - { - command.Parameters.AddWithValue("@param2", e.Node.NextNode.Name); - } + command.Parameters.AddWithValue("@numberOfDaysEnd", e.Node.NextNode.Name); else - { - command.Parameters.AddWithValue("@param2", "9999"); - } + command.Parameters.AddWithValue("@numberOfDaysEnd", 9999); sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI); @@ -787,4 +788,5 @@ rEKLAMADataSet.KLIENCI.Clear(); + command.CommandType = CommandType.Text; sqlDataAdapter.Fill(rEKLAMADataSet.KLIENCI);