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;
}