public UserProfileView GetUserProfile(int userID)
{
UserProfileView UPV = new UserProfileView();
using (mwdEntities db = new mwdEntities())
{
var user = db.SYSUsers.Find(userID);
if (user != null)
{
UPV.SYSUserID = user.SYSUserID;
UPV.LoginName = user.LoginName;
UPV.Password = user.PasswordEncryptedText;
var SUP = db.SYSUserProfiles.Find(userID);
if (SUP != null)
{
UPV.FirstName = SUP.FirstName;
UPV.LastName = SUP.LastName;
UPV.Gender = SUP.Gender;
}
var SUR = db.SYSUserRoles.Find(userID);
if (SUR != null)
{
UPV.LOOKUPRoleID = SUR.LOOKUPRoleID;
UPV.RoleName = SUR.LOOKUPRole.RoleName;
UPV.IsRoleActive = SUR.IsActive;
}
}
}
return(UPV);
}
public bool IsLoginNameExist(string loginName)
{
using (mwdEntities db = new mwdEntities())
{
return(db.SYSUsers.Where(o => o.LoginName.Equals(loginName)).Any());
}
}
public List <TicketProfileView> GetAllTicketProfiles(bool resolved)
{
List <TicketProfileView> profiles = new List <TicketProfileView>();
using (mwdEntities db = new mwdEntities())
{
TicketProfileView TPV;
IQueryable <helpdesk> tickets;
//var tickets = db.helpdesks.Where(o => o.StatusID != 3);
if (resolved)
{
tickets = db.helpdesks.Where(o => o.StatusID == 3);
}
else
{
tickets = db.helpdesks.Where(o => o.StatusID != 3);
}
foreach (helpdesk h in tickets)
{
TPV = new TicketProfileView();
TPV.ID = h.ID;
TPV.UserID = h.UserID;
TPV.Comments = h.Comments;
TPV.SeverityID = h.SeverityID;
TPV.StatusID = h.StatusID;
TPV.DepartmentID = h.DepartmentID;
TPV.RowCreatedDateTime = h.RowCreatedDateTime;
var SUP = db.SYSUsers.Find(h.UserID);
if (SUP != null)
{
TPV.LoginName = SUP.LoginName;
}
var SEV = db.Severities.Find(h.SeverityID);
if (SEV != null)
{
TPV.Severity = SEV.Severity1;
}
var STA = db.Status.Find(h.StatusID);
if (STA != null)
{
TPV.Status = STA.Status1;
}
var DEP = db.Departments.Find(h.DepartmentID);
if (DEP != null)
{
TPV.Department = DEP.Department1;
}
profiles.Add(TPV);
}
}
return(profiles);
}
public void UpdateTicket(TicketProfileView ticket)
{
using (mwdEntities db = new mwdEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
helpdesk HD = db.helpdesks.Find(ticket.ID);
HD.UserID = ticket.UserID;
HD.SeverityID = ticket.SeverityID;
HD.StatusID = ticket.StatusID;
HD.DepartmentID = ticket.DepartmentID;
HD.Comments = ticket.Comments;
HD.RowModifiedSYSUserID = ticket.SYSUserID;
HD.RowModifiedDateTime = DateTime.Now;
db.SaveChanges();
dbContextTransaction.Commit();
}
catch
{
dbContextTransaction.Rollback();
}
}
}
}
public int GetUserID(string loginName)
{
using (mwdEntities db = new mwdEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().SYSUserID);
}
}
return(0);
}
public List <LookupSeverity> GetAllSeverities()
{
using (mwdEntities db = new mwdEntities())
{
var severities = db.Severities.Select(o => new LookupSeverity
{
ID = o.ID,
Severity = o.Severity1
}).ToList();
return(severities);
}
}
public List <LookupStatus> GetAllStatus()
{
using (mwdEntities db = new mwdEntities())
{
var status = db.Status.Select(o => new LookupStatus
{
ID = o.ID,
Status = o.Status1
}).ToList();
return(status);
}
}
public List <LookupDepartment> GetAllDepartments()
{
using (mwdEntities db = new mwdEntities())
{
var departments = db.Departments.Select(o => new LookupDepartment
{
ID = o.ID,
Department = o.Department1
}).ToList();
return(departments);
}
}
public List <LOOKUPAvailableRole> GetAllRoles()
{
using (mwdEntities db = new mwdEntities())
{
var roles = db.LOOKUPRoles.Select(o => new LOOKUPAvailableRole
{
LOOKUPRoleID = o.LOOKUPRoleID,
RoleName = o.RoleName,
RoleDescription = o.RoleDescription
}).ToList();
return(roles);
}
}
public string GetUserPassword(string loginName)
{
using (mwdEntities db = new mwdEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.ToLower().Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().PasswordEncryptedText);
}
else
{
return(string.Empty);
}
}
}
public TicketProfileView GetTicketProfile(int ticketID)
{
TicketProfileView TPV = new TicketProfileView();
using (mwdEntities db = new mwdEntities())
{
var ticket = db.helpdesks.Find(ticketID);
if (ticket != null)
{
TPV.ID = ticket.ID;
TPV.UserID = ticket.UserID;
TPV.Comments = ticket.Comments;
TPV.SeverityID = ticket.SeverityID;
TPV.StatusID = ticket.StatusID;
TPV.DepartmentID = ticket.DepartmentID;
TPV.RowCreatedDateTime = ticket.RowCreatedDateTime;
var SUP = db.SYSUsers.Find(ticket.UserID);
if (SUP != null)
{
TPV.LoginName = SUP.LoginName;
}
var SEV = db.Severities.Find(ticket.SeverityID);
if (SEV != null)
{
TPV.Severity = SEV.Severity1;
}
var STA = db.Status.Find(ticket.StatusID);
if (STA != null)
{
TPV.Status = STA.Status1;
}
var DEP = db.Departments.Find(ticket.DepartmentID);
if (DEP != null)
{
TPV.Department = DEP.Department1;
}
}
}
return(TPV);
}
public void AddUserAccount(UserSignUpView user)
{
using (mwdEntities db = new mwdEntities())
{
SYSUser SU = new SYSUser();
SU.LoginName = user.LoginName;
SU.PasswordEncryptedText = user.Password;
SU.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SU.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SU.RowCreatedDateTime = DateTime.Now;
SU.RowModifiedDateTime = DateTime.Now;
db.SYSUsers.Add(SU);
db.SaveChanges();
SYSUserProfile SUP = new SYSUserProfile();
SUP.SYSUserID = SU.SYSUserID;
SUP.FirstName = user.FirstName;
SUP.LastName = user.LastName;
SUP.Gender = user.Gender;
SUP.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUP.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUP.RowCreatedDateTime = DateTime.Now;
SUP.RowModifiedDateTime = DateTime.Now;
db.SYSUserProfiles.Add(SUP);
db.SaveChanges();
if (user.LOOKUPRoleID > 0)
{
SYSUserRole SUR = new SYSUserRole();
SUR.LOOKUPRoleID = user.LOOKUPRoleID;
SUR.SYSUserID = user.SYSUserID;
SUR.IsActive = true;
SUR.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUR.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUR.RowCreatedDateTime = DateTime.Now;
SUR.RowModifiedDateTime = DateTime.Now;
db.SYSUserRoles.Add(SUR);
db.SaveChanges();
}
}
}
protected override bool AuthorizeCore(HttpContextBase httpContext)
{
bool authorize = false;
using (mwdEntities db = new mwdEntities())
{
UserManager UM = new UserManager();
foreach (var roles in userAssignedRoles)
{
authorize = UM.IsUserInRole(httpContext.User.Identity.Name, roles);
if (authorize)
{
return(authorize);
}
}
}
return(authorize);
}
public UserDataView GetUserDataView(string loginName)
{
UserDataView UDV = new UserDataView();
List <UserProfileView> profiles = GetAllUserProfiles();
List <LOOKUPAvailableRole> roles = GetAllRoles();
int? userAssignedRoleID = 0, userID = 0;
string userGender = string.Empty;
userID = GetUserID(loginName);
using (mwdEntities db = new mwdEntities())
{
userAssignedRoleID = db.SYSUserRoles.Where(o => o.SYSUserID == userID)?.FirstOrDefault().LOOKUPRoleID;
userGender = db.SYSUserProfiles.Where(o => o.SYSUserID == userID)?.FirstOrDefault().Gender;
}
List <Gender> genders = new List <Gender>();
genders.Add(new Gender
{
Text = "Male",
Value = "M"
});
genders.Add(new Gender
{
Text = "Female",
Value = "F"
});
UDV.UserProfile = profiles;
UDV.UserRoles = new UserRoles
{
SelectedRoleID = userAssignedRoleID,
UserRoleList = roles
};
UDV.UserGender = new UserGender
{
SelectedGender = userGender,
Gender = genders
};
return(UDV);
}
public void AddTicket(TicketCreateView ticket)
{
using (mwdEntities db = new mwdEntities())
{
helpdesk HD = new helpdesk();
HD.UserID = ticket.UserID;
HD.StatusID = ticket.StatusID;
HD.SeverityID = ticket.SeverityID;
HD.DepartmentID = ticket.DepartmentID;
HD.Comments = ticket.Comments;
HD.RowCreatedSYSUserID = ticket.UserID > 0 ? ticket.UserID : 1;
HD.RowModifiedSYSUserID = ticket.UserID > 0 ? ticket.UserID : 1;
HD.RowCreatedDateTime = DateTime.Now;
HD.RowModifiedDateTime = DateTime.Now;
db.helpdesks.Add(HD);
db.SaveChanges();
}
}
public bool IsUserInRole(string loginName, string roleName)
{
using (mwdEntities db = new mwdEntities())
{
SYSUser SU = db.SYSUsers.Where(o => o.LoginName.ToLower().Equals(loginName))?.FirstOrDefault();
if (SU != null)
{
var roles = from q in db.SYSUserRoles
join r in db.LOOKUPRoles on q.LOOKUPRoleID equals r.LOOKUPRoleID
where r.RoleName.Equals(roleName) && q.SYSUserID.Equals(SU.SYSUserID)
select r.RoleName;
if (roles != null)
{
return(roles.Any());
}
}
return(false);
}
}
public List <UserProfileView> GetAllUserProfiles()
{
List <UserProfileView> profiles = new List <UserProfileView>();
using (mwdEntities db = new mwdEntities())
{
UserProfileView UPV;
var users = db.SYSUsers.ToList();
foreach (SYSUser u in db.SYSUsers)
{
UPV = new UserProfileView();
UPV.SYSUserID = u.SYSUserID;
UPV.LoginName = u.LoginName;
UPV.Password = u.PasswordEncryptedText;
var SUP = db.SYSUserProfiles.Find(u.SYSUserID);
if (SUP != null)
{
UPV.FirstName = SUP.FirstName;
UPV.LastName = SUP.LastName;
UPV.Gender = SUP.Gender;
}
var SUR = db.SYSUserRoles.Where(o => o.SYSUserID.Equals(u.SYSUserID));
if (SUR.Any())
{
var userRole = SUR.FirstOrDefault();
UPV.LOOKUPRoleID = userRole.LOOKUPRoleID;
UPV.RoleName = userRole.LOOKUPRole.RoleName;
UPV.IsRoleActive = userRole.IsActive;
}
profiles.Add(UPV);
}
}
return(profiles);
}
public void DeleteUser(int userID)
{
using (mwdEntities db = new mwdEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
var SUR = db.SYSUserRoles.Where(o => o.SYSUserID == userID);
if (SUR.Any())
{
db.SYSUserRoles.Remove(SUR.FirstOrDefault());
db.SaveChanges();
}
var SUP = db.SYSUserProfiles.Where(o => o.SYSUserID == userID);
if (SUP.Any())
{
db.SYSUserProfiles.Remove(SUP.FirstOrDefault());
db.SaveChanges();
}
var SU = db.SYSUsers.Where(o => o.SYSUserID == userID);
if (SU.Any())
{
db.SYSUsers.Remove(SU.FirstOrDefault());
db.SaveChanges();
}
dbContextTransaction.Commit();
}
catch
{
dbContextTransaction.Rollback();
}
}
}
}
public void UpdateUserAccount(UserProfileView user)
{
using (mwdEntities db = new mwdEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
SYSUser SU = db.SYSUsers.Find(user.SYSUserID);
SU.LoginName = user.LoginName;
SU.PasswordEncryptedText = user.Password;
SU.RowCreatedSYSUserID = user.SYSUserID;
SU.RowModifiedSYSUserID = user.SYSUserID;
SU.RowCreatedDateTime = DateTime.Now;
SU.RowModifiedDateTime = DateTime.Now;
db.SaveChanges();
var userProfile = db.SYSUserProfiles.Where(o => o.SYSUserID == user.SYSUserID);
if (userProfile.Any())
{
SYSUserProfile SUP = userProfile.FirstOrDefault();
SUP.SYSUserID = SU.SYSUserID;
SUP.FirstName = user.FirstName;
SUP.LastName = user.LastName;
SUP.Gender = user.Gender;
SUP.RowCreatedSYSUserID = user.SYSUserID;
SUP.RowModifiedSYSUserID = user.SYSUserID;
SUP.RowCreatedDateTime = DateTime.Now;
SUP.RowModifiedDateTime = DateTime.Now;
db.SaveChanges();
}
if (user.LOOKUPRoleID > 0)
{
var userRole = db.SYSUserRoles.Where(o => o.SYSUserID == user.SYSUserID);
SYSUserRole SUR = null;
if (userRole.Any())
{
SUR = userRole.FirstOrDefault();
SUR.LOOKUPRoleID = user.LOOKUPRoleID;
SUR.SYSUserID = user.SYSUserID;
SUR.IsActive = true;
SUR.RowCreatedSYSUserID = user.SYSUserID;
SUR.RowModifiedSYSUserID = user.SYSUserID;
SUR.RowCreatedDateTime = DateTime.Now;
SUR.RowModifiedDateTime = DateTime.Now;
}
else
{
SUR = new SYSUserRole();
SUR.LOOKUPRoleID = user.LOOKUPRoleID;
SUR.SYSUserID = user.SYSUserID;
SUR.IsActive = true;
SUR.RowCreatedSYSUserID = user.SYSUserID;
SUR.RowModifiedSYSUserID = user.SYSUserID;
SUR.RowCreatedDateTime = DateTime.Now;
SUR.RowModifiedDateTime = DateTime.Now;
db.SYSUserRoles.Add(SUR);
}
db.SaveChanges();
}
dbContextTransaction.Commit();
}
catch
{
dbContextTransaction.Rollback();
}
}
}
}
