private static void LogMessage(MatchingDB db, EmailQueueMessage qMessage,string status)
{
EmailLog eLog = new EmailLog(qMessage,status);
Project pr; Student st;
try
{
switch (qMessage.ContactType)
{
case "Project":
pr = db.Projects.Where(p => p.Id == qMessage.ContactId).First();
pr.Emailed = (status == EmailStatus.Success.ToString());
if (pr.EmailLogs == null)
pr.EmailLogs = new List<EmailLog>();
pr.EmailLogs.Add(eLog);
break;
case "Student":
st = db.Students.Where(s => s.Id == qMessage.ContactId).First();
st.Emailed = (status == EmailStatus.Success.ToString());
if (st.EmailLogs == null)
st.EmailLogs = new List<EmailLog>();
st.EmailLogs.Add(eLog);
break;
default:
break;
}
db.SaveChanges();
}
catch (Exception ex)
{
log.Error("An unexpected exception occured while creating a log record for the notification e-mail sent from the queue. The id for the " + qMessage.ContactType + " is: " + qMessage.ContactId + ". You can disregard this message if the " + qMessage.ContactType + " is deleted.", ex.InnerException ?? ex);
}
}
public ActionResult Clear(string id)
{
string redirect = "";
string sql = "";
switch (id)
{
case "systemLogs":
redirect = "SystemLogs";
sql = "DELETE FROM Log";
break;
case "emailLogs":
redirect = "EmailLogs";
sql = "DELETE FROM EmailLogs";
break;
case "emailQueue":
redirect = "EmailQueue";
sql = "DELETE FROM EmailQueueMessages";
break;
default:
return RedirectToAction("SystemLogs");
}
using (MatchingDB db = new MatchingDB())
{
db.Database.ExecuteSqlCommand(sql, new object[0]);
}
return RedirectToAction(redirect);
}
/// <summary>
/// Retrieves e-mail notification related configuration parameters
/// </summary>
/// <returns>EmailConfiguration object that encapsulates all config parameters related to emails</returns>
public static EmailConfiguration GetEmailConfigParameters()
{
EmailConfiguration emailConfig;
using (MatchingDB db = new MatchingDB())
emailConfig = new EmailConfiguration(db.ConfigParameters.Where(c => c.Id >= 100));
return emailConfig;
}
static EmailService()
{
EmailConfiguration emailConfig = ConfigurationService.GetEmailConfigParameters();
MAIL_SERVER = emailConfig.MailServer;//System.Configuration.ConfigurationManager.AppSettings["MailServer"];
MAIL_ACCOUNT = emailConfig.MailAccount;//System.Configuration.ConfigurationManager.AppSettings["MailAccount"];
MAIL_PWD = emailConfig.MailPassword;//System.Configuration.ConfigurationManager.AppSettings["MailPassword"];
MAIL_PORT = emailConfig.MailServerPort;//Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["MailServerPort"]);
ENABLE_SSL = emailConfig.IsSSLEnabled;//Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["SSLEnabled"]);
IS_MAIL_HTML = emailConfig.IsMailBodyHtml;//Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["IsMailBodyHtml"]);
IS_TESTING = emailConfig.IsTesting;//Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["IsTesting"]);
using (MatchingDB db = new MatchingDB())
{
MAIL_FROM = db.ConfigParameters.First(c => c.Id == ((int)ConfigEnum.SiteMasterEmail)).Value;
MAIL_FROM_INSTITUTE = "Tauber Institute Matching Application";
MAIL_FROM_ADMIN = db.ConfigParameters.First(c => c.Name == "SiteMasterFirstName").Value + " " + db.ConfigParameters.First(c => c.Name == "SiteMasterLastName").Value;
}
if(System.Web.HttpContext.Current==null)
PICKUP_DIR = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName);
else
PICKUP_DIR = System.Web.HttpContext.Current.Server.MapPath("~/");
//PICKUP_DIR = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); //Returns in URI form (starst with file:// which can not be used by smtpClient as pickup directory)
PICKUP_DIR = System.IO.Path.Combine(PICKUP_DIR,"App_Data","emails");
if (!System.IO.Directory.Exists(PICKUP_DIR))
System.IO.Directory.CreateDirectory(PICKUP_DIR);
}
/// <summary>
/// Retrieves all application settings in AppConfiguration object
/// </summary>
/// <returns>AppConfiguration object that encapsulates all named application configuration parameters which are not relted to notification e-mail configuration.</returns>
public static AppConfiguration GetConfigParameters()
{
AppConfiguration appConfig;
using (MatchingDB db = new MatchingDB())
appConfig = new AppConfiguration(db.ConfigParameters.Where(c=>c.Id<100));
return appConfig;
}
/// <summary>
/// Gets the list of config parameters that are related to business rules governing ui interaction for ranking
/// </summary>
/// <returns>List of ConfigParameter objects</returns>
public static IList<ConfigParameter> GetBusinessRulesConfigParametersFor(ContactType cType)
{
string[] projectConfigParams=null;
switch (cType)
{
case ContactType.Project:
projectConfigParams= new string[]{
ConfigEnum.EnforceContinuousStudentRanking.ToString(),ConfigEnum.MaxRejectedBusStudents.ToString(),ConfigEnum.MaxRejectedEngStudents.ToString(),ConfigEnum.MaxRejectedStudents.ToString(),ConfigEnum.MinABusStudents.ToString(),ConfigEnum.MinAEngStudents.ToString(),ConfigEnum.MinAStudents.ToString(),ConfigEnum.MinBBusStudents.ToString(),ConfigEnum.MinBEngStudents.ToString(),ConfigEnum.MinBStudents.ToString(),ConfigEnum.RejectedStudentThreshold.ToString()
};
break;
case ContactType.Student:
projectConfigParams= new string[]{
ConfigEnum.EnforceContinuousProjectRanking.ToString(),ConfigEnum.MaxRejectedProjects.ToString(),ConfigEnum.RejectedProjectThreshold.ToString(), ConfigEnum.MinFirstProjects.ToString()
};
break;
default:
break;
}
IList<ConfigParameter> uiParams;
using (MatchingDB db = new MatchingDB())
{
if (projectConfigParams != null)
uiParams = db.ConfigParameters.Where(c => projectConfigParams.Contains(c.Name)).ToList();
else
uiParams = db.ConfigParameters.ToList();
}
return uiParams;
}
public static void SendMailsInTheQueue()
{
int i = 0;
using (var db = new MatchingDB())
{
foreach (var m in GetMessages(db))
{
EmailService emailService = new EmailService(m.To, m.Subject, m.Body);
String status = emailService.SendMessage();
LogMessage(db, m, status);
if (status == EmailStatus.Success.ToString())
db.EmailQueueMessages.Remove(m);
i++;
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
log.Info("An error has happened while trying to persist the log info after e-mail has been sent and emaillog has been generated but before queue message has been deleted. Exception is:",ex);
}
}
if(i>0)
log.Info("The number of emails successfully sent: "+i.ToString()+" at "+DateTime.Now.ToString());
}
/// <summary>
/// Drops the message in the email queueing table for Quartz job to pick it up. This is the main method to send templated -emails to project contacts and students.
/// </summary>
/// <param name="to">Recipient</param>
/// <param name="subject">Subject of the email</param>
/// <param name="body">Body of the email</param>
public static void QueueMessage(EmailQueueMessage message)
{
using (var db = new MatchingDB())
{
db.EmailQueueMessages.Add(message);
db.SaveChanges();
}
}
public ActionResult EmailQueue()
{
IList<EmailQueueMessage> emailQueueLogs;
using (MatchingDB db = new MatchingDB())
{
emailQueueLogs = db.EmailQueueMessages.ToList();
}
return View(emailQueueLogs);
}
public void CreateMatchings()
{
m.CreateProjects(m.ExtractProjects());
m.CreateStudents(m.ExtractStudents());
m.CreateMatchings(m.ExtractMatchings());
Assert.AreEqual(189,new MatchingDB().Matchings.Where(mat=>mat.Id!=0).Count());
var db = new MatchingDB();
Assert.AreEqual(0, db.Projects.Where(p => p.Matchings.Count() == 0).Count());
Assert.AreEqual(0, db.Students.Where(s => s.Matchings.Count() == 0).Count());
}
public static void DeleteProjectRejectsReferencingStudent(int studentId)
{
using(MatchingDB db = new MatchingDB())
{
var projectRejectsToDelete = db.ProjectRejects.Where(pr => pr.Student.Id == studentId).ToList();
foreach (ProjectReject pr in projectRejectsToDelete)
db.ProjectRejects.Remove(pr);
db.SaveChanges();
}
}
public ActionResult SystemLogs()
{
IList<SystemLogDto> systemLogs;
using (MatchingDB db = new MatchingDB())
{
var systemLogSql=@"select * from log order by date desc";
systemLogs = db.Database.SqlQuery<SystemLogDto>(systemLogSql,new object[0]).ToList();
}
return View(systemLogs);
}
public static void DeleteStudentFeedbacksReferencingProject(int projectId)
{
using (MatchingDB db = new MatchingDB())
{
var studentFeedbacksToDelete = db.StudentFeedbacks.Where(sf => sf.Project.Id == projectId).ToList();
foreach (StudentFeedback sf in studentFeedbacksToDelete)
db.StudentFeedbacks.Remove(sf);
db.SaveChanges();
}
}
public static void DeleteStudentFeedbacksReferencingProjectForStudents(int projectId, params int[] studentsRemovedFromProject)
{
using (MatchingDB db = new MatchingDB())
{
var studentFeedbacksToDelete = db.StudentFeedbacks.Where(sf => sf.Project.Id == projectId && studentsRemovedFromProject.Contains(sf.Student.Id)).ToList();
foreach (StudentFeedback sf in studentFeedbacksToDelete)
{
if (sf != null)// The projection above would insert null into projectRejectsToDelete list for the studentId when the project has not rejected that student.
db.StudentFeedbacks.Remove(sf);
}
db.SaveChanges();
}
}
public static void DeleteProjectRejectsReferencingStudentForProjects(int studentId, params int[] projectIds)
{
using (MatchingDB db = new MatchingDB())
{
var projectRejectsToDelete = db.Projects.Where(p => projectIds.Contains(p.Id)).Select(p => p.ProjectRejects.Where(pr=>pr.Student.Id==studentId).FirstOrDefault());
foreach (ProjectReject pr in projectRejectsToDelete)
{
if (pr != null) // The projection above would insert null into projectRejectsToDelete list for the studentId when the project has not rejected that student.
db.ProjectRejects.Remove(pr);
}
db.SaveChanges();
}
}
public ActionResult EmailLogs()
{
IList<EmailLogDto> emailLogs;
using (MatchingDB db = new MatchingDB())
{
var emailLogSql = @"SELECT EmailLogs.Date, EmailLogs.Guid, EmailLogs.Status, EmailLogs.Subject, EmailLogs.Message, Projects.Name as Name, Projects.ContactFirst as FirstName, Projects.ContactLast as LastName, Projects.ContactEmail as Email
FROM EmailLogs INNER JOIN Projects ON EmailLogs.Project_Id = Projects.Id
UNION
SELECT EmailLogs.Date, EmailLogs.Guid, EmailLogs.Status, EmailLogs.Subject, EmailLogs.Message, Students.UniqueName as Name, Students.FirstName as FirstName, Students.LastName as LastName, Students.Email as Email
FROM EmailLogs INNER JOIN Students ON EmailLogs.Student_Id = Students.Id
ORDER BY EmailLogs.Date DESC";
emailLogs = db.Database.SqlQuery<EmailLogDto>(emailLogSql, new object[0]).ToList();
}
return View(emailLogs);
}
public ActionResult Create(Student st)
{
if (!ValidateStudent(st))
this.ModelState.AddModelError("UniqueName", uniqueNameErrorMsg);
if (ModelState.IsValid)
{
MatchingDB db = new MatchingDB();
st.Guid = Guid.NewGuid();
db.Students.Add(st);
db.SaveChanges();
TempData["message"] = "Student \"" + st.FirstName+" "+st.LastName + "\" is added!";
db.Dispose();
return RedirectToAction("Index");
}
setViewDataForListOfDegrees();
return View(st);
}
static EmailQueueMessage()
{
EmailConfiguration emailConfig = ConfigurationService.GetEmailConfigParameters();
PROJECT_URL_EMAIL_SUBJECT=emailConfig.ProjectAccessUrlEmailSubject;
STUDENT_URL_EMAIL_SUBJECT=emailConfig.StudentAccessUrlEmailSubject;
PROJECT_URL_EMAIL_BODY=emailConfig.ProjectAccessUrlEmailBody;
STUDENT_URL_EMAIL_BODY=emailConfig.StudentAccessUrlEmailBody;
_emailHeaderTemplate=emailConfig.EmailHeader;
_emailFooterTemplate = emailConfig.EmailFooter;
using (var db = new MatchingDB())
{
_siteAdminInfoDict = db.ConfigParameters
.Where(c => new string[] { "SiteMasterFirstName", "SiteMasterLastName", "SiteMasterEmail", "SiteMasterPhone" }
.Contains(c.Name))
.ToDictionary(c => (ConfigEnum)Enum.Parse(typeof(ConfigEnum),c.Name), c => c.Value);
}
}
public ActionResult GenerateProjectCommentsExcelReport()
{
string[] headers = new string[2] { "Project", "Comments"};
string[] rowKeys = new string[2] { "p", "c"};
IList rows;
using (MatchingDB db = new MatchingDB())
{
rows = (from pr in db.Projects.OrderBy(p=>p.Name)
select new
{
p = pr.Name,
c = pr.Feedback
}).ToList();
}
var result = this.Excel("ProjectComments.xlsx", "Project Comments", rows, headers, rowKeys);
return result;
}
public ActionResult GenerateStudentCommentsExcelReport()
{
string[] headers = new string[2] { "Student", "Comments" };
string[] rowKeys = new string[2] { "s", "c" };
IList rows;
using (MatchingDB db = new MatchingDB())
{
rows = (from st in db.Students.OrderBy(s => s.FirstName).ThenBy(s=>s.LastName)
select new
{
s = st.FirstName+" "+st.LastName,
c = st.OtherComments
}).ToList();
}
var result = this.Excel("StudentComments.xlsx", "Student Comments", rows, headers, rowKeys);
return result;
}
public JsonResult SubmitPreferences(ProjectPreferencesDto preferencesDto)
{
MatchingDB db = new MatchingDB();
Project project = db.Projects.Include("Matchings.Student").Include("ProjectRejects.Student").Where(p => p.Id == preferencesDto.ProjectId && p.Guid == new Guid(preferencesDto.ProjectGuid)).FirstOrDefault();
string message = project == null ? "Authentication failure: Project can not be identified." : "Success";
var jsonResult = new JsonResult();
jsonResult.Data = message;
if (project == null)
return jsonResult;
// Update the project scores with the scores coming from UI
foreach (ProjectScoreDto pSDto in preferencesDto.ProjectPreferences)
{
project.Matchings.Where(m => m.Student.Id == pSDto.StudentId).FirstOrDefault().ProjectScore = pSDto.Score;
}
// Remove all rejects
ICollection<ProjectReject> projectRejects = project.ProjectRejects.ToList();
project.ProjectRejects.Clear();
foreach (ProjectReject reject in projectRejects)
{
db.ProjectRejects.Remove(reject);
}
// Add rejects that came from UI.
IList<ProjectReject> userRejects = new List<ProjectReject>();
if (preferencesDto.ProjectRejects != null)
{
foreach (ProjectRejectDto pRDto in preferencesDto.ProjectRejects)
{
ProjectReject pr = new ProjectReject();
pr.Student = db.Students.Where(s => s.Id == pRDto.StudentId).FirstOrDefault();
pr.Reason = pRDto.Reason;
userRejects.Add(pr);
}
}
project.ProjectRejects = userRejects;
project.Feedback = preferencesDto.Feedback;
project.ScoreDate = DateTime.Now;
db.SaveChanges();
db.Dispose();
SendConfirmationMessage(project);
return jsonResult;
}
public ActionResult GenerateProjectRejectsExcelReport()
{
string[] headers = new string[4] { "Project", "Student", "Degree","Reason" };
string[] rowKeys = new string[4] { "p", "s", "d", "r" };
IList rows;
using (MatchingDB db = new MatchingDB())
{
rows = (from p in db.Projects from pr in p.ProjectRejects
select new
{
p = p.Name,
s = pr.Student.FirstName+" "+pr.Student.LastName,
d = pr.Student.Degree,
r = pr.Reason
}).ToList();
}
var result = this.Excel("ProjectReject.xlsx", "ProjectRejects", rows, headers, rowKeys);
return result;
}
public ActionResult GenerateRankingExcelReport()
{
string[] headers = new string[4] { "Project","Student","Project Score","Student Score"};
string[] rowKeys = new string[4] { "p", "s", "ps", "ss"};
IList rows;
using(MatchingDB db = new MatchingDB())
{
rows = (from m in db.Matchings.OrderBy(ma=>ma.Project.Name)
select new
{
p = m.Project.Name,
s = m.Student.UniqueName,
ps = m.ProjectScore,
ss = m.StudentScore
}).ToList();
}
var result= this.Excel("Rankings.xlsx", "Rankings", rows, headers, rowKeys);
return result;
}
/// <summary>
/// Deletes all matching objects for a given student from the database.
/// </summary>
/// <param name="studentId">Student identifier</param>
public static void DeleteMatchingsForStudent(int studentId)
{
using (MatchingDB db = new MatchingDB())
{
Student student = db.Students.Include("Matchings.Project").Include("StudentFeedbacks").Where(s => s.Id == studentId).FirstOrDefault();
var existingMatchings = student.Matchings.ToList();
var studentFeedbacksToBeDeleted = student.StudentFeedbacks.ToList();
student.Matchings.Clear();
foreach (Matching m in existingMatchings)
db.Matchings.Remove(m);
#region Delete the student feedbacks of the student for the projects that appeared in the matchings deleted.
foreach (StudentFeedback sf in studentFeedbacksToBeDeleted)
db.StudentFeedbacks.Remove(sf);
#endregion
db.SaveChanges();
ProjectService.DeleteProjectRejectsReferencingStudent(studentId);
}
}
public static Student DeleteStudent(int studentId)
{
Student s = null;
DeleteMatchingsForStudent(studentId);
using (MatchingDB db = new MatchingDB())
{
s = db.Students.Include("EmailLogs").SingleOrDefault(st => st.Id == studentId);
#region Remove EmailLogs for the project
IList<EmailLog> emailLogsToBeDeleted = s.EmailLogs.ToList();
foreach (EmailLog log in emailLogsToBeDeleted)
db.EmailLogs.Remove(log);
db.SaveChanges();
#endregion
db.Students.Remove(s);
db.SaveChanges();
}
return s;
}
public static Project DeleteProject(int projectId)
{
Project p = null;
DeleteMatchingsForProject(projectId);
StudentService.DeleteStudentFeedbacksReferencingProject(projectId);
using (MatchingDB db = new MatchingDB())
{
p = db.Projects.Include("EmailLogs").SingleOrDefault(pr => pr.Id == projectId);
#region Remove EmailLogs for the project
IList<EmailLog> emailLogsToBeDeleted = p.EmailLogs.ToList();
foreach (EmailLog log in emailLogsToBeDeleted)
db.EmailLogs.Remove(log);
db.SaveChanges();
#endregion
db.Projects.Remove(p);
db.SaveChanges();
}
return p;
}
/// <summary>
/// Deletes all matching objects for a given project from the database.
/// </summary>
/// <param name="projectId">Project identifier</param>
public static void DeleteMatchingsForProject(int projectId)
{
using (MatchingDB db = new MatchingDB())
{
Project project = db.Projects.Include("Matchings.Student").Include("ProjectRejects").Where(p => p.Id == projectId).FirstOrDefault();
var existingMatchings = project.Matchings.ToList();
// Find the children collection of students that used be matching the project but will not be matching following the deletion.
var studentsRemovedFromProject = existingMatchings.Select(m => m.Student.Id).ToArray();
var projectRejectsToRemove =project.ProjectRejects.Where(pr=>studentsRemovedFromProject.Contains(pr.Student.Id)).ToList();
project.Matchings.Clear();
foreach (Matching m in existingMatchings)
db.Matchings.Remove(m);
#region Clear the collection for the students deleted off the db.
foreach (ProjectReject pr in projectRejectsToRemove)
db.ProjectRejects.Remove(pr);
#endregion
db.SaveChanges();
// Not needed because only non-matching students should be providing Positive|Constructive Feedback StudentService.DeleteStudentFeedbacksReferencingProjectForStudents(projectId, studentsRemovedFromProject);
}
}
static UIParamsAndMessages()
{
using(MatchingDB db = new MatchingDB())
{
TAUBER_ADMIN_NAME=db.ConfigParameters.Where(cp=>cp.Id==(int)ConfigEnum.SiteMasterFirstName).FirstOrDefault().Value+" "+db.ConfigParameters.Where(cp=>cp.Id==(int)ConfigEnum.SiteMasterLastName).FirstOrDefault().Value;
TAUBER_EMAIL=db.ConfigParameters.Where(cp=>cp.Id==(int)ConfigEnum.SiteMasterEmail).FirstOrDefault().Value;
TAUBER_PHONE=db.ConfigParameters.Where(cp=>cp.Id==(int)ConfigEnum.SiteMasterPhone).FirstOrDefault().Value;
INVALID_URL_ERROR_MESSAGE = String.Format(ConfigurationService.GetEmailConfigParameters().InvalidAccessUrlMessage/*System.Configuration.ConfigurationManager.AppSettings["ProjectAccessUrlSubject"]*/, TAUBER_EMAIL, TAUBER_PHONE);
ProjectScoreDetails = ScoreService.GetScoreDetailsFor(ContactType.Project);
StudentScoreDetails = ScoreService.GetScoreDetailsFor(ContactType.Student);
STUDENT_DEGREE_JAVASCRIPT_ARRAY = StudentDegreeService.GetStudentDegreeJavaScritArray();
PROJECT_SCORES_JAVASCRIPT_ARRAY = ScoreService.GetScoreJavascriptArrayFromScoreDetails(ProjectScoreDetails);
STUDENT_SCORES_JAVASCRIPT_ARRAY = ScoreService.GetScoreJavascriptArrayFromScoreDetails(StudentScoreDetails);
PROJECT_CONFIG_PARAM_JS_ARRAY = ConfigurationService.GetBusinessRulesConfigParametersAsJSVariableStatementFor(ContactType.Project);
STUDENT_CONFIG_PARAM_JS_ARRAY = ConfigurationService.GetBusinessRulesConfigParametersAsJSVariableStatementFor(ContactType.Student);
RANK_STUDENTS_INDEX_HEAD = new StringBuilder().Append(STUDENT_DEGREE_JAVASCRIPT_ARRAY).Append(PROJECT_SCORES_JAVASCRIPT_ARRAY).Append(PROJECT_CONFIG_PARAM_JS_ARRAY).ToString();
RANK_PROJECTS_INDEX_HEAD = new StringBuilder().Append(STUDENT_SCORES_JAVASCRIPT_ARRAY).Append(STUDENT_CONFIG_PARAM_JS_ARRAY).ToString();
}
}
private static IList<EmailQueueMessage> GetMessages(MatchingDB db)
{
IList<EmailQueueMessage> messages;
messages = db.EmailQueueMessages.ToList();
return messages;
}
public static IList<ProjectDto> GetProjecDtoNotMatchingStudent(int studentId)
{
IList<ProjectDto> projects = null;
using (MatchingDB db = new MatchingDB())
{
projects = db.Projects.Where(s => !s.Matchings.Select(m => m.Student.Id).Contains(studentId)).OrderBy(p => p.Name).Select(p => new ProjectDto() { Id = p.Id, Name = p.Name }).ToList();
}
return projects;
}
