public List <EmailRequest> GetEmailRequestsByUniqueID(PRMDataContext ctx, String pUniqueID)
{
try
{
var data = new List <EmailRequest>();
string query = "execute dbo.GetEmailRequestsByUniqueID @0";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = pUniqueID
}
};
if (ctx != null)
{
data = ctx.Database.SqlQuery <EmailRequest>(query, args).ToList();
}
else
{
using (ctx = new PRMDataContext())
{
data = ctx.Database.SqlQuery <EmailRequest>(query, args).ToList();
}
}
return(data);
}
catch (Exception)
{
return(new List <EmailRequest>());
}
}
public SampleUser getSampleUserByMail(String Email)
{
try {
using (var db = new PRMDataContext())
{
var result = (from elem in db.SampleUsers
where elem.Email == Email
select elem).SingleOrDefault();
return(result);
// string query = "execute getSampleUserByMail @0";
// var args = new DbParameter[]
// {
// new SqlParameter { ParameterName="@0", Value = Email }
// };
// var data = ctx.Database.SqlQuery<int>(query, args).FirstOrDefault();
//return data;
}
}
catch (Exception e)
{
return(null);
}
}
public int SaveSampleUsers(String name, String mail)
{
try
{
using (var ctx = new PRMDataContext())
{
string query = "execute AddSampleUser @0, @1";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = name
},
new SqlParameter {
ParameterName = "@1", Value = mail
},
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
catch (Exception e)
{
return(-1);
}
}
public List <User> GetAllUsers()
{
using (var db = new PRMDataContext())
{
return(db.Users.ToList());
}
}
public bool EnableDisableRole(int pRoleID, Boolean pIsActiv, DateTime pActivityTime, int pActivityBy)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.EnableDisableRole @0, @1, @2, @3";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = pRoleID
},
new SqlParameter {
ParameterName = "@1", Value = pIsActiv
},
new SqlParameter {
ParameterName = "@2", Value = pActivityTime.YYYYMMDD()
},
new SqlParameter {
ParameterName = "@3", Value = pActivityBy
}
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(true);
}
}
public int SaveUsers(User u, DateTime pActivityTime, int pActivityBy)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.SaveUsers @0, @1, @2, @3,@4, @5,@6";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = u.UserId
},
new SqlParameter {
ParameterName = "@1", Value = u.Login
},
new SqlParameter {
ParameterName = "@2", Value = "123"
},
new SqlParameter {
ParameterName = "@3", Value = u.Name
},
new SqlParameter {
ParameterName = "@4", Value = u.Email
},
new SqlParameter {
ParameterName = "@5", Value = pActivityTime.YYYYMMDD()
},
new SqlParameter {
ParameterName = "@6", Value = pActivityBy
}
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
public int SaveRolePermissionMapping(int pRoleID, List <int> pPermissionsList)
{
using (var db = new PRMDataContext())
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
foreach (var p in pPermissionsList)
{
DataRow row = dt.NewRow();
dt.Rows.Add(p);
}
string query = "execute sec.SaveRolePermissionMapping @0, @1";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = pRoleID
},
new SqlParameter {
ParameterName = "@1", Value = dt, SqlDbType = SqlDbType.Structured, TypeName = "dbo.ArrayInt"
},
};
var data = db.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
public int SaveRole(Roles role, DateTime pActivityTime, int pActivityBy)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.SaveRoles @0, @1, @2,@3,@4";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = role.Id
},
new SqlParameter {
ParameterName = "@1", Value = role.Name
},
new SqlParameter {
ParameterName = "@2", Value = role.Description
},
new SqlParameter {
ParameterName = "@3", Value = pActivityTime.YYYYMMDD()
},
new SqlParameter {
ParameterName = "@4", Value = pActivityBy
}
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
public List <Roles> GetAllRoles()
{
using (var db = new PRMDataContext())
{
return(db.Roles.ToList());
}
}
public List <int> GetPermissionsByRoleID(int pRoleID)
{
using (var db = new PRMDataContext())
{
var result = db.PermissionsMapping.Where(p => p.RoleId == pRoleID).Select(p => p.PermissionId).ToList();
return(result);
}
}
public List <int> GetRolesByUserID(int pUserID)
{
using (var db = new PRMDataContext())
{
var result = db.UserRoles.Where(p => p.UserId == pUserID).Select(p => p.RoleId).ToList();
return(result);
}
}
public List <Permissions> GetAllPermissions()
{
using (var db = new PRMDataContext())
{
string query = "execute sec.GetAllPermissions ";
var list = db.Database.SqlQuery <Permissions>(query).ToList();
return(list);
}
}
public User GetUserByEmail(string emailAddress)
{
using (var db = new PRMDataContext())
{
var result = (from data in db.Users
where data.Email == emailAddress && data.IsActive == true
select data).FirstOrDefault();
return(result);
}
}
public int resetPassword(String emailAddress, String password)
{
using (var db = new PRMDataContext())
{
var query = (from data in db.Users
where data.Email == emailAddress
select data).SingleOrDefault();
query.Password = password;
db.SaveChanges();
return(1);
}
}
public List <UserSmallDTO> SearchUser(string key)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.SearchUserForAutoComplete @0";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = key
}
};
var list = ctx.Database.SqlQuery <UserSmallDTO>(query, args).ToList();
return(list);
}
}
public List <LoginHistory> GetLoginHistory()
{
using (var db = new PRMDataContext())
{
string query = "select * from sec.LoginHistory Order by LoginTime Desc";
List <LoginHistory> log = db.Database.SqlQuery <LoginHistory>(query).ToList();
foreach (var l in log)
{
l.LoginTime = l.LoginTime.ToTimeZoneTime(tzi);
}
return(log);
}
}
public List <EmailRequest> GetEmailRequestsForProcessing()
{
try
{
using (var ctx = new PRMDataContext())
{
var list = ctx.EmailRequests.Where(p => p.EmailRequestStatus == (int)EmailRequestStatus.Pending).OrderBy(p => p.EmailRequestID).ToList();
return(list);
}
}
catch (Exception ex)
{
return(new List <EmailRequest>());
}
}
public List <String> GetRolePermissionById(int pUserID, out List <String> pRoles)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.GetRolePermissionById @0";
var cmd = ctx.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@0", Value = pUserID
});
ctx.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var roles = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <Roles>(reader).ToList();
reader.NextResult();
var permissions = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <PermissionsWithRoleID>(reader).ToList();
reader.Close();
var rolesList = new List <String>();
var permList = new List <String>();
foreach (var r in roles)
{
rolesList.Add(r.Name);
}
foreach (var p in permissions)
{
permList.Add(p.Name.ToUpper());
}
pRoles = rolesList;
return(permList);
}
}
public void ProcessEmailRequests(List <long> list)
{
using (var ctx = new PRMDataContext())
{
foreach (var id in list)
{
var dto = new EmailRequest()
{
EmailRequestID = id, EmailRequestStatus = (int)EmailRequestStatus.Processed
};
ctx.EmailRequests.Attach(dto);
var entry = ctx.Entry(dto);
entry.State = EntityState.Unchanged;
entry.Property(e => e.EmailRequestStatus).IsModified = true;
}
ctx.SaveChanges();
}
}
public int changePassword(PasswordEntity pass)
{
var username = SessionManager.GetUserLogin();
using (var db = new PRMDataContext())
{
var query = db.Users.Where(x => (x.Login == username) && (x.Password == pass.CurrentPassword)).FirstOrDefault();
if (query != null)
{
query.Password = pass.NewPassword;
db.SaveChanges();
return(1);
}
else
{
return(0);
}
}
}
public UserSearchResult SearchUsers(UserSearchParam entity)
{
using (var ctx = new PRMDataContext())
{
UserSearchResult result = new Entities.DBEntities.UserSearchResult();
string query = "execute sec.SearchUsers @0, @1, @2,@3";
var cmd = ctx.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@0", Value = entity.TextToSearch
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@1", Value = entity.IsActive
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@2", Value = entity.PageSize
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@3", Value = entity.PageIndex
});
ctx.Database.Connection.Open();
var reader = cmd.ExecuteReader();
result.ResultCount = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <int>(reader).FirstOrDefault();
reader.NextResult();
result.Result = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <UserSearchResultObj>(reader).ToList();
return(result);
}
}
public SecUserDTO ValidateUserSP(String pLogin, String pPassword, DateTime pCurrTime, String pMachineIP, Boolean pIgnorePassword, String pLoggerLoginID)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.ValidateUser @0, @1, @2, @3,@4,@5";
var cmd = ctx.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@0", Value = pLogin
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@1", Value = pPassword
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@2", Value = pCurrTime.YYYYMMDD()
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@3", Value = pMachineIP
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@4", Value = pIgnorePassword
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@5", Value = pLoggerLoginID
});
ctx.Database.Connection.Open();
var reader = cmd.ExecuteReader();
// Read User from the first result set
var user = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <User>(reader).FirstOrDefault();
if (user != null)
{
var secUserForSession = new SecUserDTO();
if (user.IsActive == false)
{
secUserForSession.IsActive = user.IsActive;
}
else
{
reader.NextResult();
var roles = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <Roles>(reader).ToList();
reader.NextResult();
var permissions = ((IObjectContextAdapter)ctx)
.ObjectContext
.Translate <PermissionsWithRoleID>(reader).ToList();
reader.Close();
secUserForSession.Login = user.Login;
secUserForSession.UserFullName = user.Name;
secUserForSession.UserId = user.UserId;
secUserForSession.Email = user.Email;
secUserForSession.IsActive = user.IsActive;
secUserForSession.Permissions = new List <string>();
secUserForSession.Roles = new List <string>();
foreach (var r in roles)
{
secUserForSession.Roles.Add(r.Name);
}
foreach (var p in permissions)
{
secUserForSession.Permissions.Add(p.Name.ToUpper());
}
}
return(secUserForSession);
}
reader.Close();
return(null);
}
}
