public void AddJobType(JobTypeModelView jobtype)
{
using (SchedulerEntities db = new SchedulerEntities())
{
JOBTYPE JT = new JOBTYPE();
JT.CreatedBy = HttpContext.Current.User.Identity.Name;
JT.LastModifiedBy = HttpContext.Current.User.Identity.Name;
JT.CreationDate = DateTime.Now;
JT.ID = Guid.NewGuid();
JT.Duration = jobtype.Duration;
JT.Unit = jobtype.Unit;
JT.Rate = jobtype.Rate;
JT.SkillRequired = jobtype.SkillRequired;
JT.WorkersRequired = jobtype.WorkersRequired;
JT.Tools = jobtype.Tools;
JT.ToolCost = jobtype.ToolsCost;
JT.FacilitiesRequired = jobtype.FacilitiesRequired;
JT.FacilitiesCost = jobtype.FacilitiesCost;
JT.Description = jobtype.Description;
JT.JobPrice = jobtype.JobPrice;
db.JOBTYPEs.Add(JT);
db.SaveChanges();
}
}
public bool IsLoginNameExist(string loginName)
{
using (SchedulerEntities db = new SchedulerEntities())
{
return(db.SYSUsers.Where(o => o.LoginName.Equals(loginName)).Any());
}
}
public void AddJobBlock(JobBlock jw)
{
using (SchedulerEntities db = new SchedulerEntities())
{
var TUR = db.JOBBLOCKs.Where(t => t.JobId == jw.JobId);
if (TUR.Any())
{
db.JOBBLOCKs.Remove(TUR.FirstOrDefault());
db.SaveChanges();
}
JOBBLOCK JW = new JOBBLOCK();
JW.CreatedBy = HttpContext.Current.User.Identity.Name;
JW.LastModifiedBy = HttpContext.Current.User.Identity.Name;
JW.CreationDate = DateTime.Now;
JW.Date = jw.Date;
JW.JobId = jw.JobId;
JW.Time = jw.TimeSlot;
JW.BlockId = Guid.NewGuid().ToString();
db.JOBBLOCKs.Add(JW);
try {
db.SaveChanges();
}
catch
{
}
}
}
public UserProfileView GetUserProfile(int userID)
{
UserProfileView UPV = new UserProfileView();
using (SchedulerEntities db = new SchedulerEntities())
{
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 List <JobType> GetJobTypeInfo()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var jobtypes = from w in db.JOBs
join o in db.JOBTYPEs on w.JobTypeID equals o.ID
select new JobType
{
Text = o.Description,
Value = w.ID,
Rate = (double)o.Rate,
Duration = (int)o.Duration,
Unit = o.Unit,
SkillsRequired = o.SkillRequired,
WorkersRequired = (int)o.WorkersRequired,
Tools = o.Tools,
FacilitiesCost = (double)o.FacilitiesCost,
ToolsCost = (double)o.ToolCost,
FacilitiesRequired = o.FacilitiesRequired,
JobPrice = (double)o.JobPrice
};
return(jobtypes.ToList());
}
}
public static void PopulateTablesFromCsv(Stream csvStream)
{
var students = new Dictionary <int, string>();
var courses = new Dictionary <int, string>();
var enrollments = new HashSet <Entrollments>();
using (var reader = new CsvReader(new StreamReader(csvStream)))
{
reader.Read();
var courseIndex = 1;
for (;;)
{
try
{
courses.Add(courseIndex, reader[courseIndex]);
courseIndex++;
}
catch
{
break;
}
}
var lineCount = 1;
while (reader.Read())
{
students.Add(lineCount, reader[0]);
for (var i = 1; i < courseIndex; i++)
{
if (reader[i] == "IP")
{
enrollments.Add(new Entrollments {
Person_Fk = lineCount, Course_Fk = i
});
}
}
lineCount++;
}
using (var db = new SchedulerEntities())
{
db.Database.ExecuteSqlCommand(
"TRUNCATE TABLE [Colors]\r\nTRUNCATE TABLE [Entrollments]\r\nDELETE FROM [Courses]\r\nDBCC CHECKIDENT ('Courses',Reseed,0)\r\nDELETE FROM [Persons]\r\nDBCC CHECKIDENT ('Persons',Reseed,0)");
db.Courses.AddRange(courses.Select(c => new Courses {
Name = c.Value
}));
db.Persons.AddRange(students.Select(s => new Persons {
Name = s.Value
}));
db.Persons.Add(new Persons
{
Name = "admin", IsAdmin = true, Password = "******", Username = "******"
});
db.SaveChanges();
db.Entrollments.AddRange(enrollments);
db.SaveChanges();
}
}
}
public static bool?FetchUserAdminStatus(string username)
{
using (var db = new SchedulerEntities())
{
var dbUser = db.Persons.SingleOrDefault(usr => usr.Username == username);
return(dbUser?.IsAdmin == true);
}
}
public static void DeleteEnrollment(int id)
{
using (var db = new SchedulerEntities())
{
var dbEnrollment = new Entrollments {
EnrollmentId = id
};
db.Entry(dbEnrollment).State = EntityState.Deleted;
db.SaveChanges();
}
}
public static void CreateCourse(CourseViewModel course)
{
using (var db = new SchedulerEntities())
{
var dbCourse = new Courses {
Name = course.Name
};
db.Courses.Add(dbCourse);
db.SaveChanges();
}
}
public int GetUserID(string loginName)
{
using (SchedulerEntities db = new SchedulerEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().SYSUserID);
}
}
return(0);
}
public static IEnumerable <ParticipantsViewModel> FetchParticipantsNotEnrolled(int courseId)
{
using (var db = new SchedulerEntities())
{
var enrolledParticipantIds =
new HashSet <int>(db.Courses.Find(courseId).Entrollments.Select(w => w.Person_Fk));
return(db.Persons.Where(c => !enrolledParticipantIds.Contains(c.Id) && c.IsAdmin != true)
.Select(c => new ParticipantsViewModel {
PersonId = c.Id, Name = c.Name
}).ToList());
}
}
public static void CreateEnrollment(CreateEnrollmentVm enrollment)
{
using (var db = new SchedulerEntities())
{
var dbEnrollment = new Entrollments
{
Course_Fk = enrollment.CourseId, Person_Fk = enrollment.ParticipantId
};
db.Entrollments.Add(dbEnrollment);
db.SaveChanges();
}
}
public List <Customer> GetAllCustomers()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var customers = db.CUSTOMERs.Select(o => new Customer
{
Text = o.Firstname,
Value = o.ID
}).ToList();
return(customers);
}
}
public List <PaymentType> GetAllPaymentTypes()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var paymenttypes = db.PAYMENTTYPEs.Select(o => new PaymentType
{
Text = o.Description,
Value = o.ID.ToString()
}).ToList();
return(paymenttypes);
}
}
public List <JobType> GetAllJobTypes()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var jobtypes = db.JOBTYPEs.Select(o => new JobType
{
Text = o.Description,
Value = o.ID
}).ToList();
return(jobtypes);
}
}
public List <PaymentMethod> GetAllPaymentMethods()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var paymentmethods = db.PAYMENTMETHODs.Select(o => new PaymentMethod
{
Text = o.Description,
Value = o.id.ToString()
}).ToList();
return(paymentmethods);
}
}
public static void CreateParticipant(ParticipantsViewModel participant)
{
using (var db = new SchedulerEntities())
{
var dbPerson = new Persons
{
Name = participant.Name, Username = participant.Username, Password = participant.Password,
IsAdmin = participant.IsAdmin
};
db.Persons.Add(dbPerson);
db.SaveChanges();
}
}
public static void Reschedule()
{
Graph.PopulateGraph();
Graph.ColorGraph();
using (var db = new SchedulerEntities())
{
db.Database.ExecuteSqlCommand("TRUNCATE TABLE [Colors]");
db.Colors.AddRange(Graph.GraphInstance.Vertices.Select(cl => new Colors
{
Course_Fk = cl.Id, Day = cl.Color.Day, TimeSlot = cl.Color.TimeSlot
}).AsEnumerable());
db.SaveChanges();
}
}
public static void DeleteCourse(CourseViewModel course)
{
using (var db = new SchedulerEntities())
{
var dbCourse = db.Courses.Find(course.CourseId);
if (dbCourse == null)
{
throw new DataException(
"The table Course does not contain an entry corresponding to the provided primary key");
}
db.Courses.Remove(dbCourse);
db.SaveChanges();
}
}
public static void DeleteParticipant(ParticipantsViewModel participant)
{
using (var db = new SchedulerEntities())
{
var dbPerson = db.Persons.Find(participant.PersonId);
if (dbPerson == null)
{
throw new DataException(
"The table Course does not contain an entry corresponding to the provided primary key");
}
db.Persons.Remove(dbPerson);
db.SaveChanges();
}
}
public List <LOOKUPAvailableRole> GetAllRoles()
{
using (SchedulerEntities db = new SchedulerEntities())
{
var roles = db.LOOKUPRoles.Select(o => new LOOKUPAvailableRole
{
LOOKUPRoleID = o.LOOKUPRoleID,
RoleName = o.RoleName,
RoleDescription = o.RoleDescription
}).ToList();
return(roles);
}
}
public static ParticipantsViewModel FetchParticipantWithId(int participantId)
{
using (var db = new SchedulerEntities())
{
var dbParticipant = db.Persons.Find(participantId);
if (dbParticipant == null)
{
throw new DataException(
"The table Course does not contain an entry corresponding to the provided primary key");
}
return(new ParticipantsViewModel {
PersonId = dbParticipant.Id, Name = dbParticipant.Name
});
}
}
public void AddJobTool(JobTool jt)
{
using (SchedulerEntities db = new SchedulerEntities())
{
JOBTOOL JT = new JOBTOOL();
JT.CreatedBy = HttpContext.Current.User.Identity.Name;
JT.LastModifiedBy = HttpContext.Current.User.Identity.Name;
JT.CreationDate = DateTime.Now;
JT.ToolId = jt.ToolId;
JT.JobId = jt.JobId;
db.JOBTOOLs.Add(JT);
db.SaveChanges();
}
}
public static IEnumerable <CourseViewModel> FetchCoursesNotEnrolled(int personId)
{
using (var db = new SchedulerEntities())
{
var enrolledCourseIds = new HashSet <int>(db.Persons.Find(personId)
.Entrollments.Select(w => w.Course_Fk));
return(db.Courses.Where(c => !enrolledCourseIds.Contains(c.Id))
.Select(c => new CourseViewModel
{
CourseId = c.Id,
Name = c.Name
})
.ToList());
}
}
public static IEnumerable <CourseViewModel> FetchCourses(int page)
{
using (var db = new SchedulerEntities())
{
return(db.Courses.Select(s => new CourseViewModel
{
CourseId = s.Id,
Name = s.Name
})
.OrderBy(s => s.CourseId)
.Skip((page - 1) * PageSize)
.Take(PageSize)
.ToList());
}
}
public static CourseViewModel FetchCourseWithId(int courseId)
{
using (var db = new SchedulerEntities())
{
var dbCourse = db.Courses.Find(courseId);
if (dbCourse == null)
{
throw new DataException(
"The table Course does not contain an entry corresponding to the provided primary key");
}
return(new CourseViewModel {
CourseId = dbCourse.Id, Name = dbCourse.Name
});
}
}
public void AddJobTool(UserSignUpView user)
{
using (SchedulerEntities db = new SchedulerEntities())
{
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();
}
}
public string GetUserPassword(string loginName)
{
using (SchedulerEntities db = new SchedulerEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.ToLower().Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().PasswordEncryptedText);
}
else
{
return(string.Empty);
}
}
}
public void AddUserAccount(UserSignUpView user)
{
using (SchedulerEntities db = new SchedulerEntities())
{
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.ID = Guid.NewGuid();
SUP.Email = user.Email;
SUP.Mobile = user.Mobile;
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();
}
}
}
/// <summary>
/// Populates the graph.
/// </summary>
public static void PopulateGraph()
{
Color.ResetLimits();
using (var db = new SchedulerEntities())
{
GraphInstance = new UndirectedGraph <Course, Edge>(false);
foreach (var course in db.Courses)
{
var temp = (Course)course;
temp.ParticipantIds = new HashSet <int>(course.Entrollments.Select(e => e.Person_Fk));
GraphInstance.AddVertex(temp);
}
PopulateEdges();
}
}
