// this class will be frequently referenced
// it should be stateless, don't put any fields here
// All method in this class is self contained.
// Add as many as methods as you wish here.
// Don't delete any method here!
//
//
public Boolean addAdminAccount(string email, string password)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var duplicatedAccount = from user in db.ProjectLaucherSets
where user.emailAddr == email
select user;
if (!duplicatedAccount.Any())
{
ProjectLaucherSet_AdminSet admin = new ProjectLaucherSet_AdminSet();
admin.ProjectLaucherSet.emailAddr = email;
admin.ProjectLaucherSet.role = "Admin";
admin.password = password;
db.ProjectLaucherSet_AdminSet.Add(admin);
db.SaveChanges();
return true;
}
else
return false;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
return false;
}
}
}
//
//
public int addProjectView(int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var projects = from p in db.ProjectSets where p.Id == projectId select p;
if (projects.Any())
{
projects.First().viewTimes += 1;
db.SaveChanges();
return projects.First().viewTimes;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public Boolean deleteJob(int jobId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<JobSet> jobList = (from j in db.JobSets where j.Id == jobId select j).ToList();
if (jobList.Any())
{
JobSet job = jobList.First();
db.JobSets.Remove(job);
db.SaveChanges();
return true;
}
else
return false;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
return false;
}
}
}
//
//
public int resetPassword(string email, string oldPassword, string newPassword)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var users = from u in db.ProjectLaucherSets where u.emailAddr == email select u;
if (users.Any())
{
ProjectLaucherSet user = users.First();
if (user.role == "External_User")
{
if (user.ProjectLaucherSet_ExtenalUser.password == oldPassword)
{
user.ProjectLaucherSet_ExtenalUser.password = newPassword;
db.SaveChanges();
// account password has been resetted
return 1;
}
else
// account password donest match
return 0;
}
else if (user.role == "???")
{
}
}
else
// account doest exist
return -1;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public Boolean deleteActivity(int activityId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var activityList = from ac in db.ActivitySets where ac.Id == activityId select ac;
if (activityList.Any())
{
ActivitySet activity = activityList.First();
List<JobSet> jobList = activity.JobSets.ToList();
foreach (JobSet j in jobList)
{
db.JobSets.Remove(j);
}
db.ActivitySets.Remove(activity);
db.SaveChanges();
return true;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public List<ActivitySet> getActivityList(int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<ActivitySet> activityList = (from ac in db.ActivitySets
where ac.ProjectId == projectId
select ac).ToList();
return activityList;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return new List<ActivitySet>();
}
}
// for advanced search only
//
public List<DashboardViewModel> getDashboardViewModelList_advanced(DateTime startDate_from, DateTime startDate_to, DateTime endDate_from, DateTime endDate_to, int jobScopeId, string target, string organization, Boolean regular)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
Debug.WriteLine(startDate_from + " | " + startDate_to + " | " + endDate_from + " | " + endDate_to + " | " + jobScopeId + " | " + target + " | " + organization);
List<ProjectSet> projectList = new List<ProjectSet>();
if (jobScopeId != -1)
{
Debug.WriteLine("jobscope != null");
projectList = (from j in db.JobSets
where j.JobScopeId == jobScopeId
select j.ActivitySet.ProjectSet).ToList();
projectList = (from p in projectList
where p.status == "Approved" && p.endDate >= DateTime.Now && p.postDate != null && p.Contact != null && p.isRegular == regular
//orderby p.postDate
select p).ToList();
}
projectList = (from p in db.ProjectSets
where p.status == "Approved" && p.endDate >= DateTime.Now && p.postDate != null && p.Contact != null && p.isRegular == regular
//orderby p.postDate
select p).ToList();
projectList = (from p in projectList
where startDate_from <= p.startDate && p.startDate <= startDate_to && endDate_from <= p.endDate && p.endDate <= endDate_to
select p).ToList();
if (organization != null)
{
projectList = (from p in projectList
where p.Contact.organization.ToUpper().Equals(organization.ToUpper())
select p).ToList();
}
if (target != null)
{
TargetGroupSet targetGroup = (from t in db.TargetGroupSets
where t.targetGroup == target
select t).First();
projectList = (from p in projectList
where p.TargetGroupSets.Contains(targetGroup)
select p).ToList();
}
List<DashboardViewModel> modelList = new List<DashboardViewModel>();
foreach (ProjectSet p in projectList)
{
List<string> strList = new List<string>();
foreach (TargetGroupSet t in p.TargetGroupSets)
{
strList.Add(t.targetGroup);
}
DashboardViewModel model = new DashboardViewModel();
model.project = p;
model.contact = p.Contact;
model.laucher = p.ProjectLaucherSet;
model.targetGroupList = strList;
modelList.Add(model);
}
return modelList;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return new List<DashboardViewModel>();
}
}
//
//
public Boolean editJob(int jobId, string scopeName, int larbor, string detail)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<JobSet> jobList = (from j in db.JobSets where j.Id == jobId select j).ToList();
JobScopeSet jobScope = (from js in db.JobScopeSets
where js.scopeName == scopeName
select js).First();
if (jobList.Any())
{
JobSet job = jobList.First();
job.JobScopeId = jobScope.Id;
job.JobScopeSet = jobScope;
job.jobDetail = detail;
job.labors = larbor;
db.SaveChanges();
return true;
}
else
return false;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
return false;
}
}
}
//
//
public Boolean eidtContact(int projectId, string name, string email, string phone, string organization, string description)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var contactList = from c in db.Contacts where c.ProjectSet.Id == projectId select c;
if (contactList.Any())
{
Contact contact = contactList.First();
contact.name = name;
contact.emailAddr = email;
contact.phoneNumber = phone;
contact.organization = organization;
contact.organizationDescription = description;
db.SaveChanges();
return true;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public int createActivity(int projectId, DateTime startTime, DateTime endTime, string location, string description)
{
using(Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var projects = from p in db.ProjectSets where p.Id == projectId select p;
if (projects.Any())
{
ActivitySet activity = new ActivitySet();
activity.startTime = startTime;
activity.endTime = endTime;
activity.location = location;
activity.description = description;
activity.ProjectSet = projects.First();
activity = db.ActivitySets.Add(activity);
db.SaveChanges();
return activity.Id;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public int createContact(int projectId, string name, string email, string phone, string organization, string description)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var projectList = from p in db.ProjectSets where p.Id == projectId select p;
if (projectList.Any())
{
Contact contact = new Contact();
contact.name = name;
contact.emailAddr = email;
contact.phoneNumber = phone;
contact.organization = organization;
contact.organizationDescription = description;
contact.ProjectSet = projectList.First();
contact = db.Contacts.Add(contact);
db.SaveChanges();
return contact.Id;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public int ValidateAdmin(string emailAddr, string password)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var adminList = from ad in db.ProjectLaucherSet_AdminSet
where ad.ProjectLaucherSet.emailAddr == emailAddr
select ad;
if (adminList.Any())
{
Debug.WriteLine("AdminList " + adminList.First().ProjectLaucherSet.emailAddr + " " + adminList.First().password);
}
var adminSet = from ad in db.ProjectLaucherSet_AdminSet
where ad.password == password
select ad;
if (adminSet.Any())
Debug.WriteLine("AdminSet " + adminSet.First().ProjectLaucherSet.emailAddr + " " + adminSet.First().password);
var admins = from admin in db.ProjectLaucherSet_AdminSet
where admin.ProjectLaucherSet.emailAddr == emailAddr && admin.password == password
select admin;
if (admins.Any())
return admins.First().ProjectLaucherSet.Id;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public int ValidateExtenalUser(string emailAddr, string password)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var ex_users = from eu in db.ProjectLaucherSet_ExtenalUser
where eu.ProjectLaucherSet.emailAddr == emailAddr && eu.password == password && eu.isDisabled == false
select eu;
if (ex_users.Any())
return ex_users.First().ProjectLaucherSet.Id;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
public Boolean submitProject(int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
ProjectSet project = (from p in db.ProjectSets where p.Id == projectId select p).First();
project.status = "beforeApproved";
project.createDate = DateTime.Now;
db.SaveChanges();
return true;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public Boolean StudentPersonalInfo(int userId, string faculty, List<string> jobScopeList, List<string> targets)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var students = from user in db.ProjectLaucherSets
where user.Id == userId
select user.ProjectLaucherSet_Student;
if (students.Any())
{
ProjectLaucherSet_Student student = students.First();
student.department = faculty;
List<JobScopeSet> jobScopes = (from js in db.JobScopeSets
where jobScopeList.Contains(js.scopeName)
select js).ToList();
student.JobScopeSets.Clear();
student.JobScopeSets = jobScopes;
List<TargetGroupSet> targetGroups = (from t in db.TargetGroupSets
where targets.Contains(t.targetGroup)
select t).ToList();
student.TargetGroupSets.Clear();
student.TargetGroupSets = targetGroups;
student.hasPersonalInfo = true;
db.SaveChanges();
return true;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public string resetPassword(string email)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var users = from u in db.ProjectLaucherSets where u.emailAddr == email select u;
if (users.Any())
{
ProjectLaucherSet user = users.First();
if (user.role == "External_User")
{
int hashcode1 = DateTime.Now.GetHashCode();
int hashcode2 = email.GetHashCode();
string newPassword = Convert.ToString(hashcode1 + hashcode2);
user.ProjectLaucherSet_ExtenalUser.password = newPassword;
db.SaveChanges();
return newPassword;
}
else if (user.role == "???")
{
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return null;
}
}
//
//
public Boolean deleteProject(int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var projects = from p in db.ProjectSets where p.Id == projectId select p;
if (projects.Any())
{
ProjectSet project = projects.First();
List<ActivitySet> activityList = project.ActivitySets.ToList();
foreach (ActivitySet ac in activityList)
{
this.deleteActivity(ac.Id);
}
db.Contacts.Remove(project.Contact);
db.SaveChanges();
project.TargetGroupSets.Clear();
project.ActivitySets.Clear();
project.Volunteers.Clear();
db.ProjectSets.Remove(project);
db.SaveChanges();
return true;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public int createExternalAccount(string email, string password, string name, string phone, string organization, string description)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var duplicatedEmail = from p in db.ProjectLaucherSets
where p.emailAddr.ToUpper().Equals(email.ToUpper())
select p;
if (!duplicatedEmail.Any())
{
ProjectLaucherSet laucher = new ProjectLaucherSet();
laucher.emailAddr = email;
laucher.role = "External_User";
ProjectLaucherSet_ExtenalUser externalUser = new ProjectLaucherSet_ExtenalUser();
externalUser.name = name;
externalUser.password = password;
externalUser.phoneNumber = phone;
externalUser.organization = organization;
externalUser.organizationDescription = description;
externalUser.ProjectLaucherSet = laucher;
externalUser.isDisabled = true;
db.ProjectLaucherSets.Add(laucher);
externalUser = db.ProjectLaucherSet_ExtenalUser.Add(externalUser);
db.SaveChanges();
return externalUser.Id;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public Boolean editActivity(int activityId, DateTime startTime, DateTime endTime, string location, string description)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var activityList = from ac in db.ActivitySets where ac.Id == activityId select ac;
if (activityList.Any())
{
ActivitySet activity = activityList.First();
activity.startTime = startTime;
activity.endTime = endTime;
activity.location = location;
activity.description = description;
db.SaveChanges();
return true;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public Boolean createJob(string scopeName, string detail, int number, int activityId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<ActivitySet> activityList = (from a in db.ActivitySets
where a.Id == activityId
select a).ToList();
if (activityList.Any())
{
var duplicatedJobScope = from j in db.JobSets
where j.ActivitySetId == activityId && j.JobScopeSet.scopeName == scopeName
select j;
if (!duplicatedJobScope.Any())
{
ActivitySet activity = activityList.First();
JobScopeSet jobScope = (from js in db.JobScopeSets
where js.scopeName == scopeName
select js).ToList().First();
JobSet job = new JobSet();
job.JobScopeSet = jobScope;
job.ActivitySet = activity;
job.jobDetail = detail;
job.labors = number;
db.JobSets.Add(job);
db.SaveChanges();
return true;
}
else
{
Debug.WriteLine("current activity already has the job scope");
return false;
}
}
else
{
Debug.WriteLine("cannot find current project");
return false;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
return false;
}
}
}
//
//
public int editProjectBase(int currentProjectId, string name, string description, DateTime startDate, DateTime endDate, string frequence, List<string> targ_strs)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<TargetGroupSet> targ = (from t in db.TargetGroupSets
where targ_strs.Contains(t.targetGroup)
select t).ToList();
var projectList = from p in db.ProjectSets where p.Id == currentProjectId select p;
if (projectList.Any())
{
ProjectSet project = projectList.First();
project.projectName = name;
project.projectDescription = description;
project.TargetGroupSets.Clear();
project.TargetGroupSets = targ;
if (frequence != "regular")
project.isRegular = false;
else
project.isRegular = true;
project.startDate = startDate;
project.endDate = endDate;
project.status = "beforeSubmitted";
db.SaveChanges();
return project.Id;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public Int32 createProjectBase(int userId, string name, string description, DateTime startDate, DateTime endDate, string frequence, List<string> targ_strs)
{
using(Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<TargetGroupSet> targ = (from t in db.TargetGroupSets
where targ_strs.Contains(t.targetGroup)
select t).ToList();
ProjectSet project = new ProjectSet();
project.projectName = name;
project.projectDescription = description;
project.TargetGroupSets = targ;
project.startDate = startDate;
project.endDate = endDate;
if (frequence != "regular")
project.isRegular = false;
else
project.isRegular = true;
project.status = "beforeSubmitted"; //"beforeApproved";
var lauchers = from pl in db.ProjectLaucherSets where pl.Id == userId select pl;
if(lauchers.Any())
{
project.ProjectLaucherSet = lauchers.First();
project = db.ProjectSets.Add(project);
db.SaveChanges();
return project.Id;
}
}
catch(Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
//
//
public ActivitySet getActivity(int activityId)
{
using(Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var list = (from a in db.ActivitySets
where a.Id == activityId
select a).ToList();
if (list.Any())
return list.First();
}
catch(Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return null;
}
}
// for dashboard only
//
public List<DashboardViewModel> getDashboardViewModels(Boolean regular)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<ProjectSet> projectList = (from p in db.ProjectSets
where p.status == "Approved" && p.endDate > DateTime.Now && p.postDate != null && p.Contact != null && p.isRegular == regular
//orderby p.postDate
select p).ToList();
List<DashboardViewModel> modelList = new List<DashboardViewModel>();
foreach(ProjectSet p in projectList)
{
List<string> strList = new List<string>();
foreach (TargetGroupSet t in p.TargetGroupSets)
{
strList.Add(t.targetGroup);
}
DashboardViewModel model = new DashboardViewModel();
model.project = p;
model.contact = p.Contact;
model.laucher = p.ProjectLaucherSet;
model.targetGroupList = strList;
modelList.Add(model);
}
return modelList;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return new List<DashboardViewModel>();
}
}
//
//
public Contact getContact(int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var contactList = from c in db.Contacts where c.ProjectSet.Id == projectId select c;
if (contactList.Any())
return contactList.First();
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return new Contact();
}
}
//
//
public Boolean hasPersonalInfo(int userId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var users = from user in db.ProjectLaucherSets
where user.Id == userId
select user;
if (users.Any())
{
if (users.First().role == "Student")
{
if ((Boolean)users.First().ProjectLaucherSet_Student.hasPersonalInfo)
return true;
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
// not local student account, create one and return userId
// local student account exist, return userId
public int createStudentAccount(String email)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var dupicatedAccount = from user in db.ProjectLaucherSets
where user.emailAddr.ToUpper().Equals(email.ToUpper())
select user;
if (!dupicatedAccount.Any())
{
ProjectLaucherSet user = new ProjectLaucherSet();
user.emailAddr = email;
user.role = "Student";
ProjectLaucherSet_Student student = new ProjectLaucherSet_Student();
student.hasPersonalInfo = false;
student.canPost = true;
student.ProjectLaucherSet = user;
db.ProjectLaucherSet_Student.Add(student);
db.SaveChanges();
return user.Id;
}
else
{
return dupicatedAccount.First().Id;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
// when user join project
//
public Boolean joinUserToPreject(int userId, int projectId)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var projects = from p in db.ProjectSets
where p.Id == projectId
select p;
if (projects.Any())
{
ProjectSet project = projects.First();
var users = from user in db.ProjectLaucherSets
where user.Id == userId
select user;
if (users.Any())
{
ProjectLaucherSet user = users.First();
Boolean duplicate = false;
foreach (Volunteer v in project.Volunteers)
{
if (v.LaucherId == userId)
duplicate = true;
}
if (!duplicate)
{
Volunteer volunteer = new Volunteer();
volunteer.LaucherId = userId;
volunteer.role = user.role;
project.Volunteers.Add(volunteer);
return true;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return false;
}
}
//
//
public List<DashboardViewModel> getDashboardViewModelsByEmail(string email)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
List<ProjectSet> projectList = (from p in db.ProjectSets
where p.Contact != null && p.ProjectLaucherSet.emailAddr.ToUpper().Equals(email.ToUpper())
//orderby p.postDate
select p).ToList();
List<DashboardViewModel> modelList = new List<DashboardViewModel>();
foreach (ProjectSet p in projectList)
{
List<string> strList = new List<string>();
foreach (TargetGroupSet t in p.TargetGroupSets)
{
strList.Add(t.targetGroup);
}
DashboardViewModel model = new DashboardViewModel();
model.project = p;
model.contact = p.Contact;
model.laucher = p.ProjectLaucherSet;
model.targetGroupList = strList;
modelList.Add(model);
}
return modelList;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return new List<DashboardViewModel>();
}
}
//
//
public int getUserIdByEmail(string email)
{
using (Youth_Center_DB_Conn db = new Youth_Center_DB_Conn())
{
try
{
var users = from u in db.ProjectLaucherSets where u.emailAddr == email select u;
if (users.Any())
return users.First().Id;
}
catch (Exception ex)
{
Debug.WriteLine(ex.StackTrace);
}
return -1;
}
}
