/// <summary>
/// Function for retrieving a single lead from the list
/// </summary>
/// <param name="CompanyId">Id of the company</param>
/// <param name="Id">Id of that particular lead you want to retrieve</param>
/// <returns>single lead</returns>
public static Lead GetDetailsForLeads(int CompanyId, int Id)
{
DBManager db = new DBManager();
try
{
db.Open();
string query = @"select top 1 le.Lead_Id,le.Name,le.Address1,le.Address2,isnull(le.Country_Id,0)[Country_Id],
isnull(le.State_Id,0)[State_Id],le.Phone1,le.Phone2,le.Email,le.Taxno1,le.Taxno2,
isnull( le.Currency_Id,0)[Currency_Id],le.Primary_Status,le.Assign,le.[Source],
isnull(le.Created_By,0)[Created_By], le.Created_Date,isnull(le.Modified_By,0)[Modified_By],
le.Modified_Date,isnull(le.Company_Id,0)[Company_Id],co.Name[Country],s.Name[State],cu.Code[Currency],
f.Name[Company],le.Salutation,le.City,le.Contact_Name,le.Zip_Code,le.Details,le.Profile_Image_Path
from TBL_LEADS le
left join TBL_COUNTRY_MST co on le.Country_Id = co.Country_Id
left join TBL_STATE_MST s on s.State_Id = le.State_Id
left join TBL_CURRENCY_MST cu on cu.Currency_Id = le.Currency_Id
left join TBL_COMPANY_MST f on f.Company_Id = le.Company_Id
where le.Company_Id=@Company_Id and le.Lead_Id=@id;";
db.CreateParameters(2);
db.AddParameters(0, "@Company_Id", CompanyId);
db.AddParameters(1, "@id", Id);
DataSet ds = db.ExecuteDataSet(CommandType.Text, query);
Lead lead = new Lead();
if (ds.Tables[0] != null)
{
DataRow item = ds.Tables[0].Rows[0];
lead.ID = (item["Lead_Id"] != DBNull.Value) ? Convert.ToInt32(item["Lead_Id"]) : 0;
lead.Name = Convert.ToString(item["Name"]);
lead.Address1 = Convert.ToString(item["Address1"]);
lead.Address2 = Convert.ToString(item["Address2"]);
lead.Details = Convert.ToString(item["Details"]);
lead.CountryId = (item["Country_Id"] != DBNull.Value) ? Convert.ToInt32(item["Country_Id"]) : 0;
lead.StateId = (item["State_Id"] != DBNull.Value) ? Convert.ToInt32(item["State_Id"]) : 0;
lead.Phone1 = Convert.ToString(item["Phone1"]);
lead.Phone2 = Convert.ToString(item["Phone2"]);
lead.Email = Convert.ToString(item["Email"]);
lead.Taxno1 = Convert.ToString(item["Taxno1"]);
lead.Taxno2 = Convert.ToString(item["Taxno2"]);
lead.City = Convert.ToString(item["City"]);
lead.Salutation = Convert.ToString(item["Salutation"]);
lead.ContactName = Convert.ToString(item["Contact_Name"]);
lead.ZipCode = Convert.ToString(item["Zip_Code"]);
lead.CreatedBy = item["Created_By"] != DBNull.Value ? Convert.ToInt32(item["Created_By"]) : 0;
lead.Country = Convert.ToString(item["Country"]);
lead.State = Convert.ToString(item["State"]);
lead.Status = item["Primary_Status"] != DBNull.Value ? Convert.ToInt32(item["Primary_Status"]) : 0;
lead.AssignId = item["Assign"] != DBNull.Value ? Convert.ToInt32(item["Assign"]) : 0;
lead.Source = item["Source"] != DBNull.Value ? Convert.ToInt32(item["Source"]) : 0;
lead.ProfileImagePath = Convert.ToString(item["profile_image_path"]);
}
return(lead);
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "Lead |GetDetailsForLeads(int CompanyId, int Id)");
return(null);
}
finally
{
db.Close();
}
}
/// <summary>
/// Function for retrieving a list of leads
/// </summary>
/// <param name="CompanyId">Id of the company</param>
/// <returns>list of leads</returns>
public static List <Lead> GetDetailsForLeads(int?CompanyId, int?Status, int?EmployeeId, DateTime?From, DateTime?To)
{
DBManager db = new DBManager();
try
{
db.Open();
string query = @"select le.Lead_Id,le.Name,le.Address1,le.Address2,isnull(le.Country_Id,0)[Country_Id],
isnull(le.State_Id,0)[State_Id],le.Phone1,le.Phone2,le.Email,le.Taxno1,le.Taxno2,
isnull(le.Currency_Id,0)[Currency_Id],le.Primary_Status,le.Assign,le.[Source],
isnull(le.Created_By,0)[Created_By],le.Created_Date,isnull(le.Modified_By,0)[Modified_By],
le.Modified_Date, le.Company_Id,co.Name[Country],le.Salutation,le.City,le.Contact_Name,le.Zip_Code,
s.Name[State],cu.Code[Currency],f.Name[Company],em.First_Name[Employee],le.Details
from TBL_LEADS le
left join TBL_COUNTRY_MST co on le.Country_Id = co.Country_Id
left join TBL_EMPLOYEE_MST em on em.employee_Id=le.Assign
left join TBL_STATE_MST s on s.State_Id = le.State_Id
left join TBL_CURRENCY_MST cu on cu.Currency_Id = le.Currency_Id
left join TBL_COMPANY_MST f on f.Company_Id = le.Company_Id
where le.Company_Id=@Company_Id {#employeefilter#} {#statusfilter#} {#daterangefilter#} order by le.Created_Date desc";
if (Status > 0 && Status != null)
{
query = query.Replace("{#statusfilter#}", " and le.Primary_Status=@Primary_Status");
}
else
{
query = query.Replace("{#statusfilter#}", string.Empty);
}
if (EmployeeId > 0 && EmployeeId != null)
{
query = query.Replace("{#employeefilter#}", "and le.Assign=@Assign ");
}
else
{
query = query.Replace("{#employeefilter#}", string.Empty);
}
if (From != null && To != null)
{
query = query.Replace("{#daterangefilter#}", " and convert(date,le.Created_Date)>=@fromdate and convert(date,le.Created_Date)<= @todate ");
}
else
{
To = DateTime.UtcNow;
From = new DateTime(To.Value.Year, To.Value.Month, 01);
query = query.Replace("{#daterangefilter#}", " and le.Created_Date>=@fromdate and le.Created_Date<=@todate ");
}
db.CreateParameters(5);
db.AddParameters(0, "@Assign", EmployeeId);
db.AddParameters(1, "@Primary_Status", Status);
db.AddParameters(2, "@Company_Id", CompanyId);
db.AddParameters(3, "@fromdate", From.Value);
db.AddParameters(4, "@todate", To.Value);
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
List <Lead> result = new List <Lead>();
if (dt != null)
{
foreach (DataRow item in dt.Rows)
{
Lead lead = new Lead();
lead.ID = (item["Lead_Id"] != DBNull.Value) ? Convert.ToInt32(item["Lead_Id"]) : 0;
lead.Name = Convert.ToString(item["Name"]);
lead.Address1 = Convert.ToString(item["Address1"]);
lead.Address2 = Convert.ToString(item["Address2"]);
lead.Details = Convert.ToString(item["Details"]);
lead.City = Convert.ToString(item["City"]);
lead.Salutation = Convert.ToString(item["Salutation"]);
lead.ContactName = Convert.ToString(item["Contact_Name"]);
lead.ZipCode = Convert.ToString(item["Zip_Code"]);
lead.CountryId = (item["Country_Id"] != DBNull.Value) ? Convert.ToInt32(item["Country_Id"]) : 0;
lead.StateId = (item["State_Id"] != DBNull.Value) ? Convert.ToInt32(item["State_Id"]) : 0;
lead.Phone1 = Convert.ToString(item["Phone1"]);
lead.Phone2 = Convert.ToString(item["Phone2"]);
lead.Email = Convert.ToString(item["Email"]);
lead.Taxno1 = Convert.ToString(item["Taxno1"]);
lead.Taxno2 = Convert.ToString(item["Taxno2"]);
lead.CreatedBy = item["Created_By"] != DBNull.Value ? Convert.ToInt32(item["Created_By"]) : 0;
lead.Country = Convert.ToString(item["Country"]);
lead.State = Convert.ToString(item["State"]);
lead.Status = item["Primary_Status"] != DBNull.Value ? Convert.ToInt32(item["Primary_Status"]) : 0;
lead.AssignId = item["Assign"] != DBNull.Value ? Convert.ToInt32(item["Assign"]) : 0;
lead.Assign = Convert.ToString(item["Employee"]);
lead.Source = item["Source"] != DBNull.Value ? Convert.ToInt32(item["Source"]) : 0;
result.Add(lead);
}
return(result);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "Lead | GetDetailsForLeads(int? CompanyId,int? Status, int? EmployeeId,DateTime? From,DateTime? To)");
return(null);
}
finally
{
db.Close();
}
}
