public string GenerateRequestNo() { string maxRequestNo = ""; string requestNo = ""; using (var context = new TheThinkerz_WTPSharePointEntities()) { try { var sql = "SELECT COUNT(1) FROM dbo.tblUserRequest"; var count = context.Database.SqlQuery<int>(sql).Single(); if (count > 0) { maxRequestNo = context.tblUserRequests.OrderByDescending(p => p.RequestId).Select(r => r.RequestNo).FirstOrDefault(); } if (string.IsNullOrEmpty(maxRequestNo)) { requestNo = "WTP-" + (1).ToString().PadLeft(6, '0'); } else { long maxNo = Convert.ToInt64(maxRequestNo.Substring(4)); requestNo = "WTP-" + (maxNo + 1).ToString().PadLeft(6, '0'); } } catch (Exception ex) { throw ex; } } return requestNo; }
public bool CheckPhoneNoExit(string phoneNo) { var count = 0; using (var context = new TheThinkerz_WTPSharePointEntities()) { try { var users = context.tblUsers.Where(r => r.PrimaryPhoneNo == phoneNo).Select(p => p.UserID).ToList(); count = users.Count; } catch (Exception ex) { throw ex; } } return count > 0; }
public List<StateMaster> GetStateData() { List<StateMaster> lstStates = new List<StateMaster>(); using (var context = new TheThinkerz_WTPSharePointEntities()) { try { context.tblStateMasters.Where(r => r.Active == true).ToList().ForEach(p => { lstStates.Add(new StateMaster() { StateId = p.StateId, StateName = p.StateName }); }); } catch (Exception ex) { throw ex; } } return lstStates; }
public string GetUploadedFiles(int requestId) { string strFileList = string.Empty; using (var context = new TheThinkerz_WTPSharePointEntities()) { try { var lstDocs = context.tblDocRepositories.Where(r => r.RequestId == requestId).ToList<tblDocRepository>(); foreach (var item in lstDocs) { strFileList += string.IsNullOrEmpty(strFileList) ? item.FileName : "," + item.FileName; } } catch (Exception ex) { throw ex; } } return strFileList; }
public bool UserIsAuthenticated(string userName, string password, out long userId) { var count = 0; userId = 0; using (var context = new TheThinkerz_WTPSharePointEntities()) { try { var objUser = context.tblUsers.Where<tblUser>(r => r.PrimaryPhoneNo == userName && r.PinNo == password); if (objUser != null) { userId = objUser.Select(r => r.UserID).FirstOrDefault(); count = objUser.Count(); } } catch (Exception ex) { throw ex; } } return count > 0 && userId > 0; }
public List<UserRequest> SearchRequest(SearchParam objParam) { List<UserRequest> lstRequests = new List<UserRequest>(); using (var context = new TheThinkerz_WTPSharePointEntities()) { try { var result = context.tblUserRequests.Where(r => string.IsNullOrEmpty(objParam.RequestNo) ? (objParam.RequestNo.Equals(string.Empty)) : (r.RequestNo.Equals(objParam.RequestNo)) && (r.PrimaryPhoneNo.Equals(objParam.UserId)) && (string.IsNullOrEmpty(objParam.RequestStatus) ? (objParam.RequestStatus.Equals(string.Empty)) : (r.Status.Equals(objParam.RequestStatus))) ).ToList<tblUserRequest>(); switch (objParam.CompareOperator) { case "=": result = result.Where(p => (objParam.RequestDate == DateTime.MinValue ? (objParam.RequestDate.Equals(DateTime.MinValue)) : (DateTime.Compare(p.CreateDate.Value.Date, objParam.RequestDate.Date) == 0))).ToList<tblUserRequest>(); break; case ">": result = result.Where(p => (objParam.RequestDate == DateTime.MinValue ? (objParam.RequestDate.Equals(DateTime.MinValue)) : (DateTime.Compare(p.CreateDate.Value.Date, objParam.RequestDate.Date) > 0))).ToList<tblUserRequest>(); break; case "<": result = result.Where(p => (objParam.RequestDate == DateTime.MinValue ? (objParam.RequestDate.Equals(DateTime.MinValue)) : (DateTime.Compare(p.CreateDate.Value.Date, objParam.RequestDate.Date) < 0))).ToList<tblUserRequest>(); break; } //result = result.Take(objParam.NoOfData).ToList<tblUserRequest>(); result = result.ToList<tblUserRequest>(); foreach (tblUserRequest objRequest in result) { lstRequests.Add(new UserRequest() { RequestId = objRequest.RequestId, RequestNo = objRequest.RequestNo, CreateDate = objRequest.CreateDate, UpdateDate = objRequest.UpdateDate, Status = objRequest.Status, ProblemLocation = objRequest.ProblemLocation, ShortDescription = objRequest.ShortDescription, LongDescription = objRequest.LongDescription }); } } catch (Exception) { throw; } } return lstRequests; }
public bool SaveUserRequest(UserRequest requestData, RequestStatus statusData, List<DocRepository> documentData) { long requestId = 0; long statusId = 0; long docId = 0; using (var context = new TheThinkerz_WTPSharePointEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { tblUserRequest objUserRequest = new tblUserRequest(); var sql = "SELECT COUNT(1) FROM dbo.tblUserRequest where RequestId=" + requestData.RequestId; var count = context.Database.SqlQuery<int>(sql).Single(); if (count == 0) { requestData.RequestNo = GenerateRequestNo(); objUserRequest.RequestNo = requestData.RequestNo; objUserRequest.FirstName = requestData.FirstName; objUserRequest.LastName = requestData.LastName; objUserRequest.Address1 = requestData.Address1; objUserRequest.Address2 = requestData.Address2; objUserRequest.ZipCode = requestData.ZipCode; objUserRequest.CityName = requestData.CityName; objUserRequest.StateId = requestData.StateId; objUserRequest.PrimaryPhoneNo = requestData.PrimaryPhoneNo; objUserRequest.PrimaryPhoneType = requestData.PrimaryPhoneType; objUserRequest.PrimaryMobileProvider = requestData.PrimaryMobileProvider; objUserRequest.SecondaryPhoneNo = requestData.SecondaryPhoneNo; objUserRequest.SecondaryPhoneType = requestData.SecondaryPhoneType; objUserRequest.SecondaryMobileProvider = requestData.SecondaryMobileProvider; objUserRequest.EmailId = requestData.EmailId; objUserRequest.PrefContactMethod = requestData.PrefContactMethod; objUserRequest.ProblemLocation = requestData.ProblemLocation; objUserRequest.ShortDescription = requestData.ShortDescription; objUserRequest.LongDescription = requestData.LongDescription; objUserRequest.AttachedDocName = requestData.AttachedDocName; objUserRequest.EmailConfirmation = requestData.EmailConfirmation; objUserRequest.CreateDate = requestData.CreateDate; objUserRequest.UpdateDate = requestData.UpdateDate; objUserRequest.Status = requestData.Status; context.tblUserRequests.Add(objUserRequest); } else { objUserRequest = context.tblUserRequests.Where(r => r.RequestId == requestData.RequestId).Single<tblUserRequest>(); ; objUserRequest.ProblemLocation = requestData.ProblemLocation; objUserRequest.ShortDescription = requestData.ShortDescription; objUserRequest.LongDescription = requestData.LongDescription; objUserRequest.EmailConfirmation = requestData.EmailConfirmation; objUserRequest.Status = requestData.Status; objUserRequest.UpdateDate = requestData.UpdateDate; } context.SaveChanges(); context.Entry(objUserRequest).GetDatabaseValues(); requestId = objUserRequest.RequestId; tblDocRepository objDocRepository = new tblDocRepository(); foreach (DocRepository objItem in documentData) { objItem.RequestId = requestId; var sqlDoc = "SELECT COUNT(1) FROM dbo.tblDocRepository where RecId=" + objItem.RecId; var countDoc = context.Database.SqlQuery<int>(sqlDoc).Single(); if (countDoc == 0) { objDocRepository.RequestId = objItem.RequestId; objDocRepository.FileName = objItem.FileName; objDocRepository.FilePath = objItem.FilePath; objDocRepository.UploadDate = objItem.UploadDate; context.tblDocRepositories.Add(objDocRepository); context.SaveChanges(); } docId++; } tblRequestStatu objRequestStatus = new tblRequestStatu(); statusData.RequestId = requestId; var sqlStatus = "SELECT COUNT(1) FROM dbo.tblRequestStatus where RecordId=" + statusData.RecordId; var countStatus = context.Database.SqlQuery<int>(sqlStatus).Single(); if (countStatus == 0) { objRequestStatus.RequestId = statusData.RequestId; objRequestStatus.Status = statusData.Status; objRequestStatus.UpdateDate = statusData.UpdateDate; objRequestStatus.UpdateBy = statusData.UpdateBy; context.tblRequestStatus.Add(objRequestStatus); } context.SaveChanges(); if (statusData != null) { context.Entry(objRequestStatus).GetDatabaseValues(); statusId = objRequestStatus.RecordId; } dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); } } } return requestId > 0 && statusId > 0 && documentData.Count == docId; }
public long SaveUserMaster(List<UserMaster> userData) { long userId = 0; using (var context = new TheThinkerz_WTPSharePointEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { tblUser objUser = new tblUser(); foreach (UserMaster objItem in userData) { objUser.EmailId = objItem.EmailId; objUser.FirstName = objItem.FirstName; objUser.LastName = objItem.LastName; objUser.Address1 = objItem.Address1; objUser.Address2 = objItem.Address2; objUser.ZipCode = objItem.ZipCode; objUser.CityName = objItem.CityName; objUser.StateId = objItem.StateId; objUser.PrimaryPhoneNo = objItem.PrimaryPhoneNo; objUser.PrimaryPhoneType = objItem.PrimaryPhoneType; objUser.PrimaryMobileProvider = objItem.PrimaryMobileProvider; objUser.SecondaryPhoneNo = objItem.SecondaryPhoneNo; objUser.SecondaryPhoneType = objItem.SecondaryPhoneType; objUser.SecondaryMobileProvider = objItem.SecondaryMobileProvider; objUser.PrefferedContactMethod = objItem.PrefferedContactMethod; objUser.CreateDate = objItem.CreateDate; objUser.UpdateDate = objItem.UpdateDate; objUser.PinNo = objItem.PinNo; objUser.Active = objItem.Active; context.tblUsers.Add(objUser); } context.SaveChanges(); context.Entry(objUser).GetDatabaseValues(); userId = objUser.UserID; dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); throw ex; } } } return userId; }
public int SaveLoginHistory(List<LoginHistory> data) { int returnValue = 0; using (var context = new TheThinkerz_WTPSharePointEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { foreach (LoginHistory objItem in data) { var count = context.tblLoginHistories.Count(r => r.RecordId == objItem.RecordId); if (count == 0) { tblLoginHistory objLoginHistory = new tblLoginHistory(); PropertyInfo[] userProperties = objLoginHistory.GetType().GetProperties().Where(p => p.GetMethod.IsVirtual == false).ToArray(); ; foreach (PropertyInfo item in userProperties) { PropertyInfo dataProperty = objItem.GetType().GetProperty(item.Name, BindingFlags.Public | BindingFlags.Instance); item.SetValue(objLoginHistory, dataProperty.GetValue(objItem), null); } context.tblLoginHistories.Add(objLoginHistory); } } returnValue = context.SaveChanges(); dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); throw ex; } } } return returnValue; }