/// <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(); } }