public List <Users> FetchUsers()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
return(db.Users.ToList());
}
}
public void AddProject(ProjectModel p, string s, string c)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
Projects proj = new Projects();
proj.Title = p.Title;
proj.Date_Created = DateTime.Now;
proj.Date_From = p.Date_From;
proj.Date_To = p.Date_To;
proj.Cap = Convert.ToDecimal(p.Cap);
proj.IsActive = 1;
proj.IsDeleted = 0;
proj.Client_Id = Convert.ToInt32(s);
proj.CreatedBy = Convert.ToInt32(c);
proj.TotalStaff = p.TotalStaff;
proj.ITsOnProject = p.ITsOnProject;
proj.ManualWorkersOnProject = p.ManualWorkersOnProject;
proj.EngineersOnProject = p.EngineersOnProject;
proj.ArchitectsOnProject = p.ArchitectsOnProject;
proj.EstimatedEarnings = p.EstimatedEarnings;
proj.RiskFactor = p.RiskFactor;
db.Projects.Add(proj);
db.SaveChanges();
}
}
public bool DoesNameExist(string newtitle)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
return(db.Clients.Where(o => o.Name.Equals(newtitle)).Any());
}
}
public void UpdateProject(ProjectModel pm, string selectClient, string sid, string active)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
db.Entry(convertProjectForDB(pm, selectClient, sid, active)).State = EntityState.Modified;
db.SaveChanges();
}
}
public Clients GetClientById(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
Clients cli = db.Clients.Find(id);
return(cli);
}
}
public string GetUserPassword(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Find(id);
return(user.Password);
}
}
public Projects GetProjectById(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
Projects p = db.Projects.Find(id);
return(p);
}
}
public void DeleteUser(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
db.Database.ExecuteSqlCommand("Update Users Set IsDeleted = 1 where id =" + id);
db.SaveChanges();
}
}
public int GetNumberOfActiveProjects()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
int count = Convert.ToInt32(db.Projects.SqlQuery("select count(Id) from Projects where IsActive=1 and IsDeleted <> 1 "));
return(count);
}
}
public string GetEmail(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Find(id);
return(user.Email);
}
}
public string GetClient(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var client = db.Clients.Find(id);
return(client.Name);
}
}
public int GetUserId(string username)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
int id = db.Database.ExecuteSqlCommand("select Id from Users where Username = " + username);
return(id);
}
}
public int GetCreatedByFromName(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Projects.Where(o => o.Id == id);
return(user.FirstOrDefault().CreatedBy);
}
}
public void UpdateClient(ClientModel c, string sid)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
db.Entry(convertClientForDB(c, sid)).State = EntityState.Modified;
db.SaveChanges();
}
}
public List <Projects> FetchProjects()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
List <Projects> dels = db.Projects.Where(p => p.IsDeleted != 1).ToList();
return(dels);
}
}
public int GetActiveProjectsForUser(int sid)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
//var count = db.Database.ExecuteSqlCommand("SELECT count(CreatedBy) FROM Projects where IsActive = 1 AND CreatedBy =" + sid);
var count2 = db.Projects.Count(t => (t.CreatedBy == sid) && (t.IsActive == 1));
return(count2);
}
}
public string GetUserScreenName(string username)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Where(o => o.Username.ToLower().Equals(username.ToLower()));
return(user.FirstOrDefault().Name);
}
}
public string GetMainContact(int a)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Find(a);
string maincontact = user.Name;
return(maincontact);
}
}
public List <Clients> FetchClients()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
List <Clients> dels = db.Clients.SqlQuery("Select * from Clients where IsDeleted = 1").ToList(); ////////////dodati jos jednu listu i vracati ProjectModel listu
List <Clients> clis = db.Clients.ToList().Except(dels).ToList();
return(clis);
}
}
public List <Projects> GetPforPM(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
List <Projects> dels = db.Projects.SqlQuery("Select * from Projects where CreatedBy = " + id).ToList(); ////////////dodati jos jednu listu i vracati ProjectModel listu
var projs = db.Projects.ToList().Except(dels).ToList();
return(projs);
}
}
public bool DoesTitleExist(string newtitle)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
string currenttitle = db.Projects.Where(o => o.Title.Equals(newtitle)).ToString();
if (newtitle == currenttitle)
{
return(true);
}
return(false);
}
}
public string GetUserRole(string username)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Where(o => o.Username.ToLower().Equals(username.ToLower()));
byte role = user.FirstOrDefault().Role_Id;
if (role == 1)
{
return("Administrator");
}
return("ProjectManager");
}
}
public string GetCreator(int?id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
if (id == null)
{
return("");
}
var user = db.Users.Find(id);
string creator = user.Name;
return(creator);
}
}
public bool ChangePassword(string old_pass, string new_pass, int id)
{
if (GetUserPassword(id).Equals(old_pass))
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
db.Users.Find(id).Password = new_pass;
db.SaveChanges();
return(true);
}
}
return(false);
}
public bool UpdateUser(string name, string address, string phone, string email, int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Find(id);
user.Address = address;
user.Name = name;
user.PhoneNumber = phone;
user.Email = email;
db.SaveChanges();
return(true);
}
}
public string GetUserPassword(string username)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Where(o => o.Username.ToLower().Equals(username.ToLower()));
if (user.Any())
{
return(user.FirstOrDefault().Password);
}
else
{
return(string.Empty);
}
}
}
public UserModel getUserById(int id)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Find(id);
UserModel um = new UserModel();
um.Id = user.Id;
um.Name = user.Name;
um.E_mail = user.Email;
um.Address = user.Address.ToString();
um.PhoneNumber = user.PhoneNumber;
return(um);
}
}
public List <string> GetPmOnProjects()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
List <string> a = new List <string>();
var author = db.Database.SqlQuery <int>("SELECT CreatedBy FROM Projects where IsActive = 1 GROUP by CreatedBy order by count(CreatedBy) desc").ToList();
foreach (var blj in author)
{
a.Add(GetCreator(blj));
}
return(a);
}
}
public int CheckEmail(string email)
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
var user = db.Users.Where(o => o.Email.ToLower().Equals(email.ToLower()));
if (user.Any())
{
return(user.FirstOrDefault().Id);
}
else
{
return(0);
}
}
}
public List <int> GetPmProjectCount()
{
using (ProjectsDBEntities db = new ProjectsDBEntities())
{
List <int> a = new List <int>();
var count = db.Database.SqlQuery <int>("SELECT count(CreatedBy) FROM Projects where IsActive = 1 GROUP by CreatedBy order by count(CreatedBy) desc").ToList();
foreach (var blj in count)
{
a.Add(Convert.ToInt32(blj));
}
return(a);
}
}
