//==============================================================//
public ResponseResult GetProjectDetailsById(int pid)
{
using (var db = new PRMDataContext())
{
String query = "execute sec.GetProjectDetailsById @1";
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@1", Value = pid
});
db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
// Read User from the first Result set
var MemberList = ((IObjectContextAdapter)db)
.ObjectContext
.Translate <User>(reader).ToList();
reader.NextResult();
//Read Project From 2nd Result set
var project = ((IObjectContextAdapter)db)
.ObjectContext
.Translate <ProjectDTO>(reader).FirstOrDefault();
db.Database.Connection.Close();
var projDetails = new ProjectDetails();
projDetails.Project = project;
projDetails.Members = MemberList;
return(ResponseResult.GetSuccessObject(projDetails));
//return projDetails;
}
}
//==============================================================//
public int Vote(int ProjectId, bool UpVote, bool DownVote, int UserId)
{
using (var ctx = new PRMDataContext())
{
string query = "execute dbo.ProjectVote @0, @1, @2, @3";
var args = new DbParameter[]
{
new SqlParameter {
ParameterName = "@0", Value = ProjectId
},
new SqlParameter {
ParameterName = "@1", Value = UserId
},
new SqlParameter {
ParameterName = "@2", Value = UpVote
},
new SqlParameter {
ParameterName = "@3", Value = DownVote
}
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
//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 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 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 List <User> GetAllUsers()
{
using (var db = new PRMDataContext())
{
return(db.Users.ToList());
}
}
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 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 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 User getUserInfo(int UserId)
{
using (var ctx = new PRMDataContext())
{
var user = ctx.Users.ToList();
User info = user.Find(x => x.UserId == UserId);
return(info);
}
}
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 List <Project> GetAllFinalProjects()
{
using (var db = new PRMDataContext())
{
string query = "execute sec.GetAllFinalProjects ";
var list = db.Database.SqlQuery <Project>(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 Section getUserSection(int userId)
//{
// using (var ctx = new PRMDataContext())
// {
// var sections = ctx.UserSectionMapping.ToList();
// var UserSection =sections.Find(x => x.UserId == userId);
// return UserSection;
// }
//}
public List <ProjectDTO> GetProjectsByUserId(int userId)
{
using (var ctx = new PRMDataContext())
{
String query = "execute sec.GetProjectsByUserID @0";
var arg = new SqlParameter {
ParameterName = "@0", Value = userId
};
var projList = ctx.Database.SqlQuery <ProjectDTO>(query, arg).ToList();
return(projList);
}
}
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 <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 <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 LoginHistorySearchResult SearchLoginHistory(LoginHistorySearchParam entity)
{
using (var ctx = new PRMDataContext())
{
LoginHistorySearchResult result = new Entities.DBEntities.LoginHistorySearchResult();
string query = "execute sec.SearchLoginHistory @0, @1, @2,@3,@4,@5";
var cmd = ctx.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@0", Value = entity.Login
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@1", Value = entity.MachineIp
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@2", Value = entity.SDate
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@3", Value = entity.EDate
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@4", Value = entity.PageSize
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@5", 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 <LoginHistory>(reader).ToList();
foreach (var d in result.Result)
{
d.LoginTime = d.LoginTime.ToTimeZoneTime(tzi);
}
return(result);
}
}
public Boolean IsValidResetToken(String pReset_Token)
{
try
{
using (var db = new PRMDataContext())
{
var query = String.Format("Execute sec.IsValidResetToken '{0}'", pReset_Token);
var result = db.Database.SqlQuery <Boolean>(query).FirstOrDefault();
return(result);
}
}
catch (Exception ex)
{
return(false);
}
}
public String UpdateResetToken(String emailAddress_login, String pGuid, String pIPAddress, DateTime pCurrTime, string pUrl)
{
try
{
using (var db = new PRMDataContext())
{
var query = String.Format("Execute sec.UpdateResetPasswordToken '{0}','{1}','{2}','{3}','{4}'", emailAddress_login, pGuid, pIPAddress, pCurrTime, pUrl);
var result = db.Database.SqlQuery <String>(query).FirstOrDefault();
return(result);
}
}
catch (Exception ex)
{
return("");
}
}
//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 Boolean UpdatePassword(String emailAddress_login, String currentPassword, String Newpassword, int pModifiedBy, DateTime pModifiedOn, Boolean IsChangePassword)
{
try
{
using (var db = new PRMDataContext())
{
var query = String.Format("Execute sec.UpdatePassword '{0}','{1}','{2}','{3}','{4}','{5}'", emailAddress_login, currentPassword, Newpassword, pModifiedOn, pModifiedBy, IsChangePassword);
var result = db.Database.SqlQuery <Boolean>(query).FirstOrDefault();
return(result);
}
}
catch (Exception ex)
{
return(false);
}
}
//========================================================
public int CheckIdeaExist(int UserId)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.CheckIdeaExist @0";
var args = new DbParameter[]
{
new SqlParameter {
ParameterName = "@0", Value = UserId
},
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
//return true;
}
//========================================================
public int RequestHandling(Request res, List <string> memberlist)
{
using (var db = new PRMDataContext())
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
foreach (var p in memberlist)
{
DataRow row = dt.NewRow();
dt.Rows.Add(p);
}
string query = "execute sec.SaveRequest @0, @1,@2,@3,@4,@5,@6,@7";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = res.RequestId
},
new SqlParameter {
ParameterName = "@1", Value = res.SendBy
},
new SqlParameter {
ParameterName = "@2", Value = res.ProjectId
},
new SqlParameter {
ParameterName = "@3", Value = res.RequestBody
},
new SqlParameter {
ParameterName = "@4", Value = res.Status
},
new SqlParameter {
ParameterName = "@5", Value = res.Type
},
new SqlParameter {
ParameterName = "@6", Value = dt, SqlDbType = SqlDbType.Structured, TypeName = "sec.MembersList"
},
new SqlParameter {
ParameterName = "@7", Value = res.CreatedOn
},
};
var data = db.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
public ForgotPasswordSearchResult SearchForgotPasswordLog(ForgotPasswordSearchParam entity)
{
using (var ctx = new PRMDataContext())
{
ForgotPasswordSearchResult result = new ForgotPasswordSearchResult();
string query = "execute sec.SearchForgotPasswordLog @0, @1, @2,@3,@4";
var cmd = ctx.Database.Connection.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@0", Value = entity.Login
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@1", Value = entity.SDate
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@2", Value = entity.EDate
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@3", Value = entity.PageSize
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@4", 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 <ForgotPasswordLogDTO>(reader).ToList();
foreach (var d in result.Result)
{
d.EntyDate = d.EntyDate.ToTimeZoneTime(tzi);
}
return(result);
}
}
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 int NotificationHandling(Notification not, List <int> SectionList)
{
using (var db = new PRMDataContext())
{
DataTable dt1 = new DataTable();
dt1.Columns.Add("ID");
foreach (var p in SectionList)
{
DataRow row = dt1.NewRow();
dt1.Rows.Add(p);
}
string query = "execute sec.SaveNotification @0, @1,@2,@3,@4,@5,@6";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@4", Value = not.CreatedOn
},
new SqlParameter {
ParameterName = "@1", Value = not.NotificationBody
},
new SqlParameter {
ParameterName = "@2", Value = not.Title
},
new SqlParameter {
ParameterName = "@3", Value = not.SendBy
},
new SqlParameter {
ParameterName = "@0", Value = not.NotificationId
},
new SqlParameter {
ParameterName = "@5", Value = not.ProjectId
},
new SqlParameter {
ParameterName = "@6", Value = dt1, SqlDbType = SqlDbType.Structured, TypeName = "sec.SectionList"
},
};
var data = db.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
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 SaveProjectIdea(projectIdea proj, int UserId)
{
using (var ctx = new PRMDataContext())
{
string query = "execute sec.InitiateIdea @0, @1, @2, @3,@4, @5, @6,@7, @8,@9";
var args = new DbParameter[] {
new SqlParameter {
ParameterName = "@0", Value = UserId
},
new SqlParameter {
ParameterName = "@1", Value = proj.ProjectId
},
new SqlParameter {
ParameterName = "@2", Value = proj.ProjectTitle
},
new SqlParameter {
ParameterName = "@3", Value = proj.Description
},
new SqlParameter {
ParameterName = "@4", Value = proj.Type
},
new SqlParameter {
ParameterName = "@5", Value = ""
},
new SqlParameter {
ParameterName = "@6", Value = proj.CreatedOn
},
new SqlParameter {
ParameterName = "@7", Value = proj.ModifiedOn
},
new SqlParameter {
ParameterName = "@8", Value = proj.IsActive
},
new SqlParameter {
ParameterName = "@9", Value = proj.ProjectState
}
};
var data = ctx.Database.SqlQuery <int>(query, args).FirstOrDefault();
return(data);
}
}
