private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetEventListCompany(string month, string yearV) { var listCompany = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); var conn = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString; var reminderList = new CalendarEventDAL(conn).GetAllCAllBackEvents(); var monthtype = month; var year = yearV; reminderList = GetAllCurrentMonthValid(reminderList, monthtype, year); if (reminderList.Count() > 0) { foreach (var item in reminderList) { if (item.companyId != "0") { var comView = new DeltoneCRM_DAL.CompanyDAL.CompanyView(); comView.CompanyID = Convert.ToInt32(item.companyId); listCompany.Add(comView); } } } return(listCompany); }
private List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetReALlocationCompany() { var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); // var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName, // CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode // ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP // join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7"; var query = @"SELECT Distinct CP.CompanyName, CP.CompanyID FROM dbo.Companies CP inner join LeadCompany lo on CP.CompanyID=lo.CompanyId inner join CalendarEvent ce on lo.CompanyId=ce.CompanyId and ce.IsLeadEvent=1 and convert(varchar(10),ce.EventStart, 120) >= CAST(getdate() as date) and convert(varchar(10),lo.ExpiryDate, 120) < CAST(getdate() as date) "; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = CONNSTRING; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = query; cmd.Connection = conn; conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr.HasRows) { while (sdr.Read()) { var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView(); sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString()); sbj.CompanyName = sdr["CompanyName"].ToString(); comList.Add(sbj); } } } } } return(comList); }
private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> DoFilterForCompany(List <ComReAssign> coms, string mode, string month, string year) { var listCompanyReminders = GetEventListCompany(month, year); var listObj = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); if (mode == "2") { foreach (var item in coms) { var obj = new DeltoneCRM_DAL.CompanyDAL.CompanyView(); obj.CompanyID = Convert.ToInt32(item.ComId); listObj.Add(obj); } } else { return(listCompanyReminders); } return(listObj); }
private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetCompaniesBYRep(string repId) { var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); // var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName, // CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode // ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP // join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7"; string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString; var query = @"SELECT CP.CompanyName, CP.CompanyID ,Hold FROM dbo.Companies CP where CP.OwnershipAdminID=@ownerID and CP.Active='Y' and (Hold IS NULL OR Hold<>'Y') and (IsSupperAcount IS NULL OR IsSupperAcount<>'1')"; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = CONNSTRING; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = query; cmd.Parameters.AddWithValue("@ownerID", repId); cmd.Connection = conn; conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr.HasRows) { while (sdr.Read()) { var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView(); sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString()); sbj.CompanyName = sdr["CompanyName"].ToString(); comList.Add(sbj); } } } } } return(comList); }
private List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetCompanyBYHouseAccount() { var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); // var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName, // CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode // ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP // join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7"; var query = @"SELECT CP.CompanyName, CP.CompanyID ,Hold FROM dbo.Companies CP where CP.OwnershipAdminID=7 and CP.Active='Y' and (Hold IS NULL OR Hold<>'Y') and (LeadLocked IS NULL OR LeadLocked<>'Y')"; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = CONNSTRING; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = query; cmd.Connection = conn; conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr.HasRows) { while (sdr.Read()) { var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView(); sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString()); sbj.CompanyName = sdr["CompanyName"].ToString(); comList.Add(sbj); } } } } } return(comList); }
protected List <DeltoneCRM_DAL.CompanyDAL.CompanyView> LoadCompanyForAllocation(string rep, string startDate, string endDate) { List <DeltoneCRM_DAL.CompanyDAL.CompanyView> sampleComList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>(); var count = 0; //var searchQuer=searche+'%'; var orderByString = "Order By OrderedDateTime desc"; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = @"SELECT Distinct CP.CompanyID,ROW_NUMBER() OVER(ORDER BY OrderedDateTime ASC) AS Row# , CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName, CP.Active,CP.Hold,CP.IsSupperAcount,CP.CreatedDateTime,CP.Notes ,MBG.OrderedDateTime , lc.FirstName + ' ' + lc.LastName AS createdUser,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM [dbo].Companies CP inner join [dbo].Contacts CT on CP.CompanyID = CT.CompanyID inner join [dbo].Logins lc on lc.LoginID=CP.OwnershipAdminID OUTER APPLY (SELECT Top 1 ORC.OrderedDateTime,ORC.OrderID From [dbo].Orders ORC Where ORC.CompanyID=CP.CompanyID Order By OrderID Desc ) AS MBG WHERE CP.OwnershipAdminID = " + rep + " and (CP.IsSupperAcount IS NULL OR CP.IsSupperAcount<>'1') " + " and (CP.Hold IS NULL OR CP.Hold<>'Y') " + orderByString; if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { cmd.CommandText = @"SELECT Distinct CP.CompanyID,ROW_NUMBER() OVER(ORDER BY OrderedDateTime ASC) AS Row# , CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName, CP.Active,CP.Hold,CP.IsSupperAcount,CP.CreatedDateTime,CP.Notes ,MBG.OrderedDateTime , lc.FirstName + ' ' + lc.LastName AS createdUser,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM [dbo].Companies CP inner join [dbo].Contacts CT on CP.CompanyID = CT.CompanyID inner join [dbo].Logins lc on lc.LoginID=CP.OwnershipAdminID OUTER APPLY (SELECT Top 1 ORC.OrderedDateTime,ORC.OrderID From [dbo].Orders ORC Where ORC.CompanyID=CP.CompanyID Order By OrderID Desc ) AS MBG WHERE CP.OwnershipAdminID = " + rep + " and MBG.OrderedDateTime between @startDate and @endDate and (CP.IsSupperAcount IS NULL OR CP.IsSupperAcount<>'1') " + " and (CP.Hold IS NULL OR CP.Hold<>'Y') " + orderByString;; var starDateTimeContver = Convert.ToDateTime(startDate).ToString("yyyy-MM-dd"); var endDateTimeContver = Convert.ToDateTime(endDate).ToString("yyyy-MM-dd"); cmd.Parameters.AddWithValue("@startDate", starDateTimeContver); cmd.Parameters.AddWithValue("@endDate", endDateTimeContver); } cmd.Connection = conn; conn.Open(); DataTable comTabldata = new DataTable(); using (SqlDataReader sdr = cmd.ExecuteReader()) { comTabldata.Load(sdr); var query = comTabldata.AsEnumerable().ToList(); count = query.Count(); foreach (var row in query) { DateTime?renewalDate = row.Field <DateTime?>("OrderedDateTime"); var obj = new DeltoneCRM_DAL.CompanyDAL.CompanyView() { CompanyID = row.Field <int>("CompanyID"), CompanyName = row.Field <string>("CompanyName"), }; sampleComList.Add(obj); } } conn.Close(); } } return(sampleComList); }