private IEnumerable <ActivityLogModel> ProcessRows(IEnumerable <dynamic> rows)
{
ICollection <ActivityLogModel> logs = new List <ActivityLogModel>();
foreach (IDictionary <string, object> row in rows)
{
logs.Add(new ActivityLogModel()
{
Id = (int)row["Id"],
Created = DateTimeFilter.UtcToLocal((DateTime)row["Created"]),
CreatedBy = (int)row["CreatedBy"],
Title = row["Title"] as string,
Title2 = row["Title2"] as string,
Title3 = row["Title3"] as string,
Location = row["Location"] as string,
StartDate = DateTimeFilter.UtcToLocal((DateTime)row["StartDate"]),
EndDate = DateTimeFilter.UtcToLocal((DateTime)row["EndDate"]),
Organizers = row["Organizers"] as string,
OnCampus = row["OnCampus"] as bool?,
WebSite = row["WebSite"] as string,
Notes = row["Notes"] as string
});
}
return(logs);
}
public async Task <IEnumerable <NoteModel> > GetNotes(int studentId)
{
const string sql = @"
SELECT n.Id AS StudentId, u.Id AS Id, u.FirstName AS CreatedByFirstName,
u.LastName AS CreatedByLastName, [EntryDate], [Note],
u.Id AS CreatedById
FROM [dbo].[StudentNotes] n
INNER JOIN [dbo].[Users] u ON
[CreatedBy] = u.[Id]
WHERE n.StudentId = @StudentId
ORDER BY [EntryDate] DESC";
List <NoteModel> notes = new List <NoteModel>();
try
{
notes = (await UnitOfWork.Context().QueryAsync <NoteModel>(sql, new { StudentId = studentId })).ToList();
notes.ForEach(x => x.EntryDate = DateTimeFilter.UtcToLocal(x.EntryDate));
}
catch (Exception e)
{
e.Data["SQL"] = e;
ErrorStore.LogException(e, HttpContext.Current);
}
return(notes);
}
public async Task <IEnumerable <PromoViewModel> > GetPromos()
{
List <PromoViewModel> promos = new List <PromoViewModel>();
const string sql = @"
SELECT p.Id AS Id,
[Description],
p.Created,
[PublicToken],
p.Active,
u.FirstName AS CreatedByFirstName,
u.LastName AS CreatedByLastName,
(SELECT COUNT(*) FROM [StudentPromoLog] WHERE [PromoId] = p.Id) AS TotalStudents
FROM [dbo].[UserPromo] p
INNER JOIN [dbo].[Users] u ON
[CreatedBy] = u.id
ORDER BY [Description]";
try
{
promos = (await UnitOfWork.Context().QueryAsync <PromoViewModel>(sql)).ToList();
promos.ForEach(x => x.Created = DateTimeFilter.UtcToLocal(x.Created));
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
return(promos);
}
private IEnumerable <StudyAbroadViewModel> ProcessRecords(IEnumerable <dynamic> rows)
{
ICollection <StudyAbroadViewModel> studyAbroad = new List <StudyAbroadViewModel>();
foreach (IDictionary <string, object> row in rows)
{
StudyAbroadViewModel study = new StudyAbroadViewModel()
{
Id = (int)row["Id"],
StudentId = (int)row["StudentId"],
Semester = (int)row["Semester"],
Year = (int)row["Year"],
CreditBearing = (bool)row["CreditBearing"],
Internship = (bool)row["Internship"],
CountryId = (int)row["CountryId"],
ProgramId = (int)row["ProgramId"],
City = row["City"] as string
};
if (row.ContainsKey("StartDate") && row["StartDate"] != null)
{
study.StartDate = DateTimeFilter.UtcToLocal((DateTime)row["StartDate"]);
}
if (row.ContainsKey("EndDate") && row["EndDate"] != null)
{
study.EndDate = DateTimeFilter.UtcToLocal((DateTime)row["EndDate"]);
}
if (row.ContainsKey("ProgramTypeIds") && row["ProgramTypeIds"] != null)
{
study.ProgramTypes = Array.ConvertAll(((string)row["ProgramTypeIds"]).Split(','), int.Parse);
}
study.Student = new StudentModel()
{
FirstName = (string)row["FirstName"],
MiddleName = row["MiddleName"] as string,
LastName = (string)row["LastName"]
};
studyAbroad.Add(study);
}
return(studyAbroad);
}
public async Task <IEnumerable <UserModel> > GetUsers(string username = null)
{
List <UserModel> users = new List <UserModel>();
string sql = @"
SELECT [Id], [FirstName], [LastName],
[Created], [LastLogin], [Email],
[Admin] AS IsAdmin, [Active] AS IsActive, [Login],
[Password]
FROM [dbo].[Users] ";
if (username != null)
{
sql += "WHERE [Login] = @Username";
}
try
{
if (username != null)
{
users = (await UnitOfWork.Context().QueryAsync <UserModel>(sql, new { Username = username })).ToList();
}
else
{
users = (await UnitOfWork.Context().QueryAsync <UserModel>(sql)).ToList();
}
users.ForEach(x =>
{
x.Created = DateTimeFilter.UtcToLocal(x.Created);
x.LastLogin = DateTimeFilter.UtcToLocal(x.LastLogin);
});
// TODO: LastLogin may need to be changed to a nullable type.
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
return(users);
}
public async Task <IEnumerable <DocumentViewModel> > GetActivityLogDocumentsList(int id)
{
const string sql = @"
SELECT [Id], [Title], [Size], [MimeType], [Created]
FROM [dbo].[Documents]
WHERE [ActivityLogId] = @Id AND
[Deleted] IS NULL";
try
{
List <DocumentViewModel> documents = (await UnitOfWork.Context().QueryAsync <DocumentViewModel>(sql,
new { Id = id })).ToList();
documents.ForEach(x => x.Created = DateTimeFilter.UtcToLocal(x.Created));
return(documents);
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
return(Enumerable.Empty <DocumentViewModel>());
}
public async Task <IEnumerable <EventLogViewModel> > GetEvents(int numEvents)
{
ICollection <EventLogViewModel> events = new List <EventLogViewModel>();
const string sql = @"
SELECT TOP(@NumEvents) e.Id, e.Date, e.ModifiedBy,
e.StudentId, e.UserId, e.Type,
e.Action, u.FirstName, u.LastName,
s.FirstName AS StudentFirstName,
s.LastName AS StudentLastName,
us.FirstName AS UserFirstName,
us.LastName AS UserLastName,
IPAddress
FROM [dbo].[EventLog] e
INNER JOIN [dbo].[Users] u ON
[ModifiedBy] = u.id
LEFT OUTER JOIN [dbo].[Students] s ON
e.StudentId = s.id
LEFT OUTER JOIN [dbo].[Users] us ON
e.UserId = us.id
ORDER BY [Date] DESC";
try
{
IEnumerable <dynamic> rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql,
new { NumEvents = numEvents });
foreach (IDictionary <string, object> row in rows)
{
StudentModel student = null;
// TODO: Check for key or does key always exist and value is null?
if (row.ContainsKey("StudentId") && row["StudentId"] != null)
{
student = new StudentModel()
{
Id = (int)row["StudentId"],
FirstName = (string)row["StudentFirstName"],
LastName = (string)row["StudentLastName"]
};
}
EventLogViewModel eventLog = new EventLogViewModel()
{
Id = (int)row["Id"],
EventDate = DateTimeFilter.UtcToLocal((DateTime)row["Date"]),
ModifiedById = (int)row["ModifiedBy"],
ModifiedByFirstName = (string)row["FirstName"],
ModifiedByLastName = (string)row["LastName"],
Student = student,
Type = (int)row["Type"],
Action = (string)row["Action"]
};
if (row.ContainsKey("UserId") && row["UserId"] != null)
{
eventLog.UserId = (int)row["UserId"];
eventLog.UserFirstName = (string)row["UserFirstName"];
eventLog.UserLastName = (string)row["UserLastName"];
}
eventLog.RelativeDate = DateTimeFilter.CalculateRelativeDate(eventLog.EventDate.ToUniversalTime());
events.Add(eventLog);
}
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
return(events);
}
public async Task <IEnumerable <StudentModel> > GetStudents(int?id = null)
{
ICollection <StudentModel> students = new List <StudentModel>();
string sql = @"
SELECT s.Id, s.Created, s.InitialMeeting,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([MajorId] AS VARCHAR(3)) FROM [dbo].[Matriculation] WHERE [StudentId] = s.Id AND IsMajor = 1 FOR XML PATH('')),1,1,'')) AS MajorIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([MajorId] AS VARCHAR(3)) FROM [dbo].[Matriculation] WHERE [StudentId] = s.Id AND IsMajor = 0 FOR XML PATH('')),1,1,'')) AS MinorIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([LanguageId] AS VARCHAR(3)) FROM [dbo].[StudentStudiedLanguages] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS StudiedLanguageIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([LanguageId] AS VARCHAR(3)) FROM [dbo].[StudentDesiredLanguages] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS DesiredLanguageIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([LanguageId] AS VARCHAR(3)) FROM [dbo].[StudentFluentLanguages] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS FluentLanguageIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([CountryId] AS VARCHAR(3)) FROM [dbo].[StudentStudyAbroadWishlist] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS StudyAbroadCountryIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([Year] AS VARCHAR(4)) FROM [dbo].[StudentStudyAbroadWishlist] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS StudyAbroadYearIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([Period] AS VARCHAR(3)) FROM [dbo].[StudentStudyAbroadWishlist] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS StudyAbroadPeriodIds,
(SELECT DISTINCT STUFF((SELECT ',' + CAST([PromoId] AS VARCHAR(3)) FROM [dbo].[StudentPromoLog] WHERE [StudentId] = s.Id FOR XML PATH('')),1,1,'')) AS StudentPromoLogIds,
s.FirstName, s.MiddleName, s.LastName,
s.LivingOnCampus, s.PhoneNumber, s.StudentId,
s.Dob, s.EnrolledFullTime, s.Citizenship,
s.PellGrantRecipient, s.PassportHolder, s.Gpa,
s.CampusEmail, s.AlternateEmail, s.GraduatingYear,
s.Classification, s.StreetAddress, s.StreetAddress2,
s.City, s.State, s.PostalCode,
s.EnteringYear,
COUNT(n.Id) AS NumNotes
FROM [dbo].[Students] s
LEFT OUTER JOIN [dbo].[StudentNotes] n ON
s.Id = n.StudentId ";
if (id.HasValue)
{
sql += "WHERE s.Id = @StudentId ";
}
sql += @"
GROUP BY s.Id, s.Created, s.InitialMeeting,
s.FirstName, s.MiddleName, s.LastName,
s.LivingOnCampus, s.PhoneNumber, s.StudentId,
s.Dob, s.EnrolledFullTime, s.Citizenship,
s.PellGrantRecipient, s.PassportHolder, s.Gpa,
s.CampusEmail, s.AlternateEmail, s.GraduatingYear,
s.Classification, s.StreetAddress, s.StreetAddress2,
s.City, s.State, s.PostalCode,
s.EnteringYear
ORDER BY s.LastName, s.FirstName";
try
{
IEnumerable <dynamic> rows;
if (id.HasValue)
{
rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql, new { StudentId = id.Value });
}
else
{
rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql);
}
foreach (IDictionary <string, object> row in rows)
{
StudentModel student = new StudentModel()
{
Id = (int)row["Id"],
FirstName = (string)row["FirstName"],
LastName = (string)row["LastName"],
MiddleName = row["MiddleName"] as string,
LivingOnCampus = row["LivingOnCampus"] as bool?,
StreetAddress = row["StreetAddress"] as string,
StreetAddress2 = row["StreetAddress2"] as string,
City = row["City"] as string,
State = row["State"] as string,
PostalCode = row["PostalCode"] as string,
PhoneNumber = row["PhoneNumber"] as string,
EnteringYear = row["EnteringYear"] as int?,
GraduatingYear = row["GraduatingYear"] as int?,
StudentId = row["StudentId"] as string,
EnrolledFullTime = row["EnrolledFullTime"] as bool?,
Citizenship = row["Citizenship"] as int?,
PellGrantRecipient = row["PellGrantRecipient"] as bool?,
HasPassport = row["PassportHolder"] as bool?,
CampusEmail = row["CampusEmail"] as string,
AlternateEmail = row["AlternateEmail"] as string,
Created = DateTimeFilter.UtcToLocal((DateTime)row["Created"]),
NumberOfNotes = (int)row["NumNotes"],
Gpa = row["Gpa"] as decimal?,
};
if (row.ContainsKey("Dob") && row["Dob"] != null)
{
student.DateOfBirth = DateTimeFilter.UtcToLocal((DateTime)row["Dob"]);
}
if (row.ContainsKey("InitialMeeting") && row["InitialMeeting"] != null)
{
student.InitialMeeting = DateTimeFilter.UtcToLocal((DateTime)row["InitialMeeting"]);
}
if (row.ContainsKey("MajorIds") && row["MajorIds"] != null)
{
student.SelectedMajors = Array.ConvertAll(((string)row["MajorIds"]).Split(','), int.Parse);
}
if (row.ContainsKey("MinorIds") && row["MinorIds"] != null)
{
student.SelectedMinors = Array.ConvertAll(((string)row["MinorIds"]).Split(','), int.Parse);
}
if (row.ContainsKey("StudiedLanguageIds") && row["StudiedLanguageIds"] != null)
{
student.StudiedLanguages = Array.ConvertAll(((string)row["StudiedLanguageIds"]).Split(','), int.Parse);
}
if (row.ContainsKey("DesiredLanguageIds") && row["DesiredLanguageIds"] != null)
{
student.SelectedDesiredLanguages = Array.ConvertAll(((string)row["DesiredLanguageIds"]).Split(','), int.Parse);
}
if (row.ContainsKey("FluentLanguageIds") && row["FluentLanguageIds"] != null)
{
student.SelectedLanguages = Array.ConvertAll(((string)row["FluentLanguageIds"]).Split(','), int.Parse);
}
if (row.ContainsKey("StudentPromoLogIds") && row["StudentPromoLogIds"] != null)
{
student.PromoIds = Array.ConvertAll(((string)row["StudentPromoLogIds"]).Split(','), int.Parse);
}
// View and JavaScript expects these collections
// to never be null.
if (row.ContainsKey("StudyAbroadCountryIds") && row["StudyAbroadCountryIds"] != null)
{
student.StudyAbroadCountry = Array.ConvertAll(((string)row["StudyAbroadCountryIds"]).Split(','), int.Parse);
student.StudyAbroadYear = Array.ConvertAll(((string)row["StudyAbroadYearIds"]).Split(','), int.Parse);
student.StudyAbroadPeriod = Array.ConvertAll(((string)row["StudyAbroadPeriodIds"]).Split(','), int.Parse);
}
else
{
student.StudyAbroadCountry = new List <int>();
student.StudyAbroadYear = new List <int>();
student.StudyAbroadPeriod = new List <int>();
}
students.Add(student);
}
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
return(students);
}
