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 InsertNote(int userId, AddStudentNoteViewModel model)
{
const string sql = @"
INSERT INTO [dbo].[StudentNotes]
([StudentId], [CreatedBy], [EntryDate], [Note])
VALUES
(@StudentId, @CreatedBy, @EntryDate, @Note)";
try
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
StudentId = model.StudentId,
CreatedBy = userId,
EntryDate = DateTime.Now.ToUniversalTime(),
Note = model.Note.Trim()
});
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
public async Task <bool> Delete(int id)
{
int rowsDeleted;
const string sql = @"
DELETE FROM [dbo].[StudyAbroad]
WHERE [Id] = @Id";
try
{
rowsDeleted = await UnitOfWork.Context().ExecuteAsync(sql, new { Id = id });
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
return(false);
}
try
{
await ReplaceProgramTypes(id, null);
}
catch (Exception)
{
return(false);
}
return(rowsDeleted == 1);
}
public async Task Save(int studentId, Guid promoToken)
{
const string sql = @"
INSERT INTO [dbo].[StudentPromoLog]
([PromoId], [StudentId], [Created])
VALUES
(
(SELECT [Id] FROM [dbo].[UserPromo] WHERE [PublicToken] = @PromoToken), @StudentId, @Created
)";
try
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
PromoToken = promoToken,
StudentId = studentId,
Created = DateTime.Now.ToUniversalTime()
});
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
}
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 async Task SaveStudentLanguages(int studentId, string tableName, IEnumerable <int> languages)
{
await DeleteLanguages(studentId, tableName);
if (languages != null && languages.Any())
{
string insertSql = string.Format(@"
INSERT INTO [dbo].[{0}]
([StudentId], [LanguageId])
VALUES
(@StudentId, @LanguageId)",
tableName);
try
{
foreach (int languageId in languages)
{
await UnitOfWork.Context().ExecuteAsync(insertSql,
new
{
StudentId = studentId,
LanguageId = languageId
});
}
}
catch (Exception e)
{
e.Data["SQL"] = insertSql.ToString();
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
}
public async Task Update(UserModel model)
{
const string sql = @"
UPDATE [dbo].[Users]
SET [FirstName] = @FirstName,
[LastName] = @LastName,
[Email] = @Email,
[Password] = @Password,
[Admin] = @Admin,
[Active] = @Active
WHERE [Id] = @Id";
try
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
FirstName = model.FirstName.Trim(),
LastName = model.LastName.Trim(),
Email = model.Email.Trim(),
Password = model.Password,
Admin = model.IsAdmin,
Active = model.IsActive,
Id = model.Id
});
}
catch (Exception e)
{
e.Data["SQL"] = sql.ToString();
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
public async Task Save(int studentId, IEnumerable <int> promoIds)
{
await Delete(studentId);
if (promoIds != null && promoIds.Any())
{
const string sql = @"
INSERT INTO [dbo].[StudentPromoLog]
([PromoId], [StudentId], [Created])
VALUES
(@PromoId, @StudentId, @Created)";
try
{
DateTime created = DateTime.Now.ToUniversalTime();
foreach (int promoId in promoIds)
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
PromoId = promoId,
StudentId = studentId,
Created = created
});
}
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
}
}
public async Task Save(AddStudyAbroadViewModel model)
{
const string sql = @"
INSERT INTO [dbo].[StudyAbroad]
(
StudentId, Year, Semester, CreditBearing, Internship,
CountryId, ProgramId, StartDate, EndDate, City
)
OUTPUT INSERTED.Id
VALUES
(
@StudentId, @Year, @Semester, @CreditBearing, @Internship,
@CountryId, @ProgramId, @StartDate, @EndDate, @City
)";
if (model.StartDate.HasValue)
{
model.StartDate = model.StartDate.Value.ToUniversalTime();
}
if (model.EndDate.HasValue)
{
model.EndDate = model.EndDate.Value.ToUniversalTime();
}
int studyAbroadId;
try
{
studyAbroadId = (await UnitOfWork.Context().QueryAsync <int>(sql,
new
{
StudentId = model.StudentId,
Year = model.Year,
Semester = model.Semester,
CreditBearing = model.CreditBearing,
Internship = model.Internship,
CountryId = model.CountryId,
ProgramId = model.ProgramId,
StartDate = model.StartDate,
EndDate = model.EndDate,
City = model.City
})).Single();
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
try
{
await ReplaceProgramTypes(studyAbroadId, model.ProgramTypes);
}
catch (Exception)
{
// Caught and logged already.
}
}
public async Task <ActivityLogModel> GetActivityByTitle(string title)
{
const string sql = @"
SELECT [Id], [Title], [Title2], [Title3],
[Organizers], [Location], [StartDate],
[EndDate], [OnCampus], [WebSite], [Notes],
[Created], [CreatedBy]
FROM [dbo].[ActivityLog]
WHERE [Title] = @Title";
ActivityLogModel activity = null;
try
{
IEnumerable <dynamic> rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql,
new { Title = title });
activity = ProcessRows(rows).SingleOrDefault();
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
activity = null;
}
return(activity);
}
public async Task AssociatePeopleWithActivity(int activityId, IEnumerable <ActivityLogParticipantModel> people)
{
const string sql = @"
INSERT INTO [dbo].[ActivityLogParticipant]
([EventId], [PersonId], [ParticipantType])
VALUES
(@EventId, @PersonId, @Type)";
try
{
foreach (ActivityLogParticipantModel person in people)
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
EventId = activityId,
PersonId = person.Person.Id,
Type = person.Type
});
}
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
public async Task <int> InsertActivity(ActivityLogModel model, int userId)
{
const string sql = @"
INSERT INTO [dbo].[ActivityLog]
(
[Title], [Title2], [Title3], [Organizers],
[Location], [StartDate], [EndDate], [OnCampus],
[WebSite], [Notes], [Created], [CreatedBy]
)
OUTPUT INSERTED.Id
VALUES
(
@Title, @Title2, @Title3, @Organizers,
@Location, @StartDate, @EndDate, @OnCampus,
@WebSite, @Notes, @Created, @CreatedBy
)";
try
{
model.StartDate = model.StartDate.ToUniversalTime();
model.EndDate = model.EndDate.ToUniversalTime();
model.Created = DateTime.Now.ToUniversalTime();
model.CreatedBy = userId;
return((await UnitOfWork.Context().QueryAsync <int>(sql, model)).Single());
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
public async Task UpdateActivity(ActivityLogModel model)
{
const string sql = @"
UPDATE [dbo].[ActivityLog]
SET [Title] = @Title,
[Title2] = @Title2,
[Title3] = @Title3,
[Organizers] = @Organizers,
[Location] = @Location,
[StartDate] = @StartDate,
[EndDate] = @EndDate,
[OnCampus] = @OnCampus,
[WebSite] = @WebSite,
[Notes] = @Notes
WHERE [Id] = @Id";
try
{
model.StartDate = model.StartDate.ToUniversalTime();
model.EndDate = model.EndDate.ToUniversalTime();
await UnitOfWork.Context().ExecuteAsync(sql, model);
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
/// <summary>
/// Get all types for a given activity log.
/// </summary>
/// <param name="activityId">Activity log ID.</param>
/// <returns>Types for the activity.</returns>
public async Task <ActivityLogTypes[]> GetActivityTypes(int activityId)
{
const string sql = @"
SELECT [TypeId]
FROM [dbo].[ActivityLogTypes]
WHERE [EventId] = @EventId";
ICollection <ActivityLogTypes> types = new List <ActivityLogTypes>();
try
{
IEnumerable <dynamic> rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql,
new { EventId = activityId });
foreach (IDictionary <string, object> row in rows)
{
types.Add((ActivityLogTypes)(int)row["TypeId"]);
}
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
// TODO: Can we refactor this to return an ICollection or something rather than an array?
return(types.ToArray());
}
public async Task <IEnumerable <ActivityLogModel> > GetAllActivities()
{
const string sql = @"
SELECT [Id], [Title], [Title2], [Title3],
[Organizers], [Location], [StartDate],
[EndDate], [OnCampus], [WebSite], [Notes],
[Created], [CreatedBy]
FROM [dbo].[ActivityLog]
ORDER BY [CreatedBy] DESC";
List <ActivityLogModel> activities = new List <ActivityLogModel>();
try
{
IEnumerable <dynamic> rows = await UnitOfWork.Context().QueryAsync <dynamic>(sql);
activities = ProcessRows(rows).ToList();
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
foreach (ActivityLogModel activity in activities)
{
activity.Types = await GetActivityTypes(activity.Id);
}
return(activities);
}
/// <summary>
/// Fires on Application Error
/// </summary>
protected void Application_Error(object sender, EventArgs e)
{
var environment = ConfigurationManager.AppSettings["Environment"];
if (environment != "dev")
{
var ex = Server.GetLastError().GetBaseException();
Context.Response.Clear();
Server.ClearError();
var routeData = new RouteData();
routeData.Values.Add("controller", "Error");
routeData.Values.Add("action", "500");
if (ex.GetType() == typeof(HttpException))
{
var httpException = (HttpException)ex;
var code = httpException.GetHttpCode();
// Is it a 4xx Error
if (code % 400 < 100)
{
routeData.Values["action"] = "404";
}
}
ErrorStore.LogException(ex, this.Context);
routeData.Values.Add("error", ex);
IController errorController = new ErrorController();
errorController.Execute(new RequestContext(new HttpContextWrapper(Context), routeData));
}
}
/// <summary>
/// manually write an exception to our standard exception log
/// </summary>
public static void LogException(Exception ex, bool rollupPerServer = false)
{
try
{
ErrorStore.LogException(ex, Context, appendFullStackTrace: true, rollupPerServer: rollupPerServer);
}
catch { /* Do nothing */ }
}
/// <summary>
/// manually write an exception to our standard exception log
/// </summary>
public static void LogException(Exception ex, string key = null, int?reLogDelaySeconds = null)
{
if (!ShouldLog(key))
{
return;
}
ErrorStore.LogException(ex, Context, appendFullStackTrace: true);
RecordLogged(key);
}
public async Task <bool> InsertActivityLogDocument(int activityLogId, int userId,
HttpPostedFileBase document)
{
// ContentLength hardcoded into SQL statements because as a
// parameter it's always zero. Unsure if it's Dapper or something
// else.
string sql = string.Format(@"
UPDATE [dbo].[Documents]
SET [Content] = @Content,
[Size] = {0},
[MimeType] = @MimeType,
[LastModified] = @LastModified,
[LastModifiedBy] = @LastModifiedBy,
[Deleted] = NULL,
[DeletedBy] = NULL
WHERE [ActivityLogId] = @ActivityLogId AND
LOWER([Title]) = LOWER(@Title)
IF @@ROWCOUNT = 0
INSERT INTO [dbo].[Documents]
(
[Id], [Created], [CreatedBy], [ActivityLogId],
[Title], [Size], [MimeType], [Content]
)
VALUES
(
@Id, @Created, @CreatedBy, @ActivityLogId,
@Title, {0}, @MimeType, @Content
);",
document.ContentLength);
try
{
await UnitOfWork.Context().ExecuteAsync(sql, new
{
Content = GetDocumentFromPostedFileBase(document.InputStream),
ActivityLogId = activityLogId,
Title = document.FileName,
Id = Guid.NewGuid(),
Created = DateTime.Now.ToUniversalTime(),
CreatedBy = userId,
MimeType = document.ContentType,
LastModified = DateTime.Now.ToUniversalTime(),
LastModifiedBy = userId
});
return(true);
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
}
return(false);
}
void Application_Error(object sender, EventArgs e)
{
// Code that runs when an unhandled error occurs
// Get the exception object.
Exception exc = Server.GetLastError();
ErrorStore.LogException(exc, null);
// Server.ClearError();
}
public void LogToStore()
{
var customErrorMessage = new Dictionary <string, string>();
customErrorMessage.Add("RequestMessage", _requestMessage.ToString());
customErrorMessage.Add("StatusCode", _statusCode.ToString());
customErrorMessage.Add("ErrorMessage", _errorMessage);
ErrorStore.LogException(new Exception(_errorMessage),
HttpContext.Current, true, false,
customErrorMessage, "HTN.Api");
}
public bool ContainsKey(string key)
{
try
{
return(_cache.Contains(key));
}
catch (Exception e)
{
ErrorStore.LogException(e, HttpContext.Current, applicationName: "memory");
}
return(false);
}
public ActionResult DeleteComment(Guid reportGuid, DiscussionTargetType targetType, Guid targetGuid, Guid commentGuid, string authorHash)
{
// company admins can delete any comment for their company.
// non admins can only delete their own comments
var deletionAuthorised = false;
var discussion = _discussionManager.Get(reportGuid, targetType, targetGuid);
var comment = discussion.Comments.SingleOrDefault(c => c.UniqueId == commentGuid);
if (Request.IsAuthenticated)
{
InitializeContext();
if (discussion.CompanyId == CurrentUser.CompanyId && CurrentUser.IsCompanyAdmin)
{
deletionAuthorised = true;
}
}
if (!deletionAuthorised)
{
// let's see if the comment is made by the person trying to delete it
if (authorHash == ConversionUtil.CommentToHashString(comment))
{
deletionAuthorised = true;
}
}
if (deletionAuthorised)
{
discussion.Comments.Remove(comment);
_discussionManager.DeleteComment(comment);
try
{
// update others
GlobalHost.ConnectionManager.GetHubContext <DiscussionHub>()
.Clients.Group(discussion.DiscussionName)
.removeComment(discussion.DiscussionName, commentGuid, discussion.CommentCount);
}
catch (Exception exception)
{
ErrorStore.LogException(exception, System.Web.HttpContext.Current);
}
}
else
{
throw new HttpException(401, "Unauthorized");
}
return(Json(new { success = true, commentGuid, commentCount = discussion.CommentCount }));
}
private async Task SaveStudentMajors(int studentId, IEnumerable <int> majors, bool isMajor)
{
const string sql = @"
DELETE FROM [dbo].[Matriculation]
WHERE [StudentId] = @StudentId AND
[IsMajor] = @IsMajor";
try
{
await UnitOfWork.Context().ExecuteAsync(sql,
new
{
StudentId = studentId,
IsMajor = isMajor
});
}
catch (Exception e)
{
e.Data["SQL"] = sql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
if (majors != null && majors.Any())
{
const string insertSql = @"
INSERT INTO [dbo].[Matriculation]
([StudentId], [MajorId], [IsMajor])
VALUES
(@StudentId, @MajorId, @IsMajor)";
try
{
foreach (int majorId in majors)
{
await UnitOfWork.Context().ExecuteAsync(insertSql,
new
{
StudentId = studentId,
MajorId = majorId,
IsMajor = isMajor
});
}
}
catch (Exception e)
{
e.Data["SQL"] = insertSql.ToString();
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
}
private async Task SaveStudyAbroadDestinations(int studentId, IEnumerable <int> countries,
IEnumerable <int> years, IEnumerable <int> periods)
{
if (countries == null || years == null || periods == null)
{
return;
}
int countriesCount = countries.Count();
// Each collection should have the same number of elements.
if (countriesCount == 0 || countriesCount != years.Count() || countriesCount != periods.Count())
{
return;
}
// The default if the user doesn't selecting anything at all is
// that all three enumerables will have a single element of value
// zero.
if (countries.ElementAt(0) == 0 && years.ElementAt(0) == 0 && periods.ElementAt(0) == 0)
{
return;
}
const string insertSql = @"
INSERT INTO [dbo].[StudentStudyAbroadWishlist]
([StudentId], [CountryId], [Year], [Period])
VALUES
(@StudentId, @CountryId, @Year, @Period)";
try
{
for (int i = 0; i < countries.Count(); i++)
{
await UnitOfWork.Context().ExecuteAsync(insertSql,
new
{
StudentId = studentId,
CountryId = countries.ElementAt(i),
Year = years.ElementAt(i),
Period = periods.ElementAt(i)
});
}
}
catch (Exception e)
{
e.Data["SQL"] = insertSql;
ErrorStore.LogException(e, HttpContext.Current);
throw e;
}
}
public HttpResponseMessage GetComments(int id)
{
IEnumerable <CommentVM> comments = new List <CommentVM>();
try
{
comments = commentManager.GetComments(id);
}
catch (Exception ex)
{
ErrorStore.LogException(ex, System.Web.HttpContext.Current);
}
return(Request.CreateResponse(HttpStatusCode.OK, comments));
}
public HttpResponseMessage Get(int count = 50)
{
IEnumerable <IssuesPerStatusGroup> groupedIssues = new List <IssuesPerStatusGroup>();
try
{
groupedIssues = issueManager.GetIssuesGroupedByStatusGroup(count);
}
catch (Exception ex)
{
ErrorStore.LogException(ex, System.Web.HttpContext.Current);
}
return(Request.CreateResponse(HttpStatusCode.OK, groupedIssues));
}
public override bool ContainsKey(string key)
{
try
{
using (IRedisClient redis = GetRedisClient())
{
return(redis.ContainsKey(key));
}
}
catch (Exception e)
{
ErrorStore.LogException(e, HttpContext.Current, applicationName: "redis");
}
return(false);
}
public bool Set <T>(string key, T t, int seconds)
{
try
{
_cache.Set(key, t, new CacheItemPolicy()
{
AbsoluteExpiration = DateTime.Now.AddSeconds(seconds)
});
}
catch (Exception e)
{
ErrorStore.LogException(e, HttpContext.Current, applicationName: "memory");
}
return(false);
}
public bool Set <T>(string key, T t)
{
try
{
_cache.Set(key, t, new CacheItemPolicy()
{
Priority = CacheItemPriority.NotRemovable
});
}
catch (Exception e)
{
ErrorStore.LogException(e, HttpContext.Current, applicationName: "memory");
}
return(false);
}