public static void EnsureShared() { if (Shared == null) { Shared = new FancyConn(); } if (Shared.conn.State == ConnectionState.Closed) { Shared.conn.Open(); } }
/// <summary> /// Update an FAQ by ID, create if not exists /// </summary> /// <param name="id">ID of FAQ to modify, use -1 to always create.</param> public static async Task <IActionResult> CreateOrUpdate(HttpRequest req, ClaimsPrincipal principal, ILogger log, int id) { var body = await req.GetBodyParameters(); var(query, param) = FancyConn.MakeUpsertQuery("faq", "faq_id", id, new List <string>() { "question", "answer" }, body); var(err, newId) = await FancyConn.Shared.Scalar(query, param); if (err) { return(Response.Error("Unable to edit FAQ record.", FancyConn.Shared.lastError)); } return(Response.Ok($"{(newId == null ? "Updated" : "Created")} FAQ successfully.")); }
/// <summary> /// Update a sponsor by ID, create if not exists /// </summary> /// <param name="id">ID of sponsor to modify, use -1 to always create.</param> public static async Task <IActionResult> CreateOrUpdate( HttpRequest req, ClaimsPrincipal principal, ILogger log, CloudBlobDirectory blobDirectory, int id) { var body = await req.GetBodyParameters(); var cols = new List <string>() { "name", "image", "website", "priority" }; // handle image upload var pic = req.Form.Files.GetFile("pic"); if (pic != null) { var picStream = pic.OpenReadStream(); var img = Image.Load(picStream); // resize to width of 350px while maintaining aspect ratio img.Mutate(i => i.Resize(0, 350)); var blob = blobDirectory.GetBlockBlobReference(pic.FileName.WithTimestamp()); await blob.UploadFromStreamAsync(pic.OpenReadStream()); cols.Add("pic"); body.Add("pic", blob.Uri.ToString()); } // handle database var(query, param) = FancyConn.MakeUpsertQuery("sponsor", "sponsor_id", id, cols, body); var(err, newId) = await FancyConn.Shared.Scalar(query, param); if (err) { return(Response.Error("Unable to edit sponsor record.", FancyConn.Shared.lastError)); } return(Response.Ok($"{(newId == null ? "Updated" : "Created")} sponsor successfully.")); }
/// <summary> /// Get a single user. /// DOES NOT USE <see cref="Method.IsAuthenticated"/>. /// </summary> /// <param name="type">How to select the user to get</param> /// <param name="id">ID of user to get</param> public static async Task <IActionResult> Get(HttpRequest req, ClaimsPrincipal principal, ILogger log, ReqType type, int id = -1) { string email = principal?.FindFirst(ClaimTypes.Email)?.Value; if (email == null) { return(Response.BadRequest("Not logged in.")); } FancyConn.EnsureShared(); try { var role = await FancyConn.Shared.GetRole(email); if (type != ReqType.Current && role < Role.Executive) { log.LogWarning($"[user] Unauthorized attempt by {email} to access record {id}"); return(Response.Error <JToken>($"Unauthorized.", statusCode: HttpStatusCode.Unauthorized)); } bool created = false; // use role check to see if user exists already if (role == Role.None || type == ReqType.New) { var newData = new Dictionary <string, object>() { { "first", principal.FindFirst(ClaimTypes.GivenName)?.Value }, { "last", principal.FindFirst(ClaimTypes.Surname)?.Value }, { "email", email } }; var(error, _) = await FancyConn.Shared.Scalar(@"INSERT INTO [user] (first_name, last_name, email, role_id) VALUES (@first, @last, @email, 1); SELECT SCOPE_IDENTITY()", newData); if (error) { return(Response.Error("Unable to create user.", FancyConn.Shared.lastError)); } created = true; } var cols = new List <string>() { "user_id", "role_id", "first_name", "last_name", "email", "phone_1", "phone_2", "school", "title", "bio", "pic", "show_exec" }; // use email or ID based on request type var(err, reader) = (type == ReqType.ID) ? await FancyConn.Shared.Reader( $@"SELECT TOP 1 [{string.Join("], [", cols)}] FROM [user] WHERE [user_id] = @id", new Dictionary <string, object>() { { "id", id } }) : await FancyConn.Shared.Reader( $@"SELECT TOP 1 [{string.Join("], [", cols)}] FROM [user] WHERE [email] = @email", new Dictionary <string, object>() { { "email", email } }); if (err) { return(Response.Error("Unable to fetch user.", FancyConn.Shared.lastError)); } var user = new JObject(); if (reader.HasRows) { reader.Read(); foreach (var col in cols) { user.Add(col, JToken.FromObject(reader[col])); } reader.Close(); } else { return(Response.NotFound("User not found.")); } // get confirm levels var userShifts = new JArray(); (err, reader) = await FancyConn.Shared.Reader(@"SELECT [user_shift_id], [hours], [confirm_level_id], [confirm_level], [confirm_description], [letter], [shift_id], [shift_num], [start_time], [end_time], [event_id], [name] FROM [vw_user_shift] WHERE [user_id] = @id", new Dictionary <string, object>() { { "id", user["user_id"].Value <string>() } }); if (err) { return(Response.Error("Unable to fetch attendance statuses.", FancyConn.Shared.lastError)); } while (reader.Read()) { var us = new JObject( new JProperty("user_shift_id", reader[0]), new JProperty("hours", reader[1]), new JProperty("confirm_level", new JObject( new JProperty("id", reader[2]), new JProperty("name", reader[3]), new JProperty("description", reader[4]))), new JProperty("letter", reader[5]), new JProperty("shift", new JObject( new JProperty("shift_id", reader[6]), new JProperty("shift_num", reader[7]), new JProperty("start_time", reader[8]), new JProperty("end_time", reader[9]))), new JProperty("parentEvent", new JObject( new JProperty("event_id", reader[10]), new JProperty("name", reader[11])))); userShifts.Add(us); } reader.Close(); // get mail list subscriptions (err, reader) = await FancyConn.Shared.Reader( @"SELECT m.[mail_list_id] AS [mail_list_id], m.[display_name] AS [display_name], m.[description] AS [description], (CASE WHEN uml.user_mail_list_id IS NULL THEN 0 ELSE 1 END) AS subscribed FROM [user] u CROSS JOIN [mail_list] m LEFT JOIN [user_mail_list] uml ON uml.[user_id] = u.[user_id] AND uml.mail_list_id = m.mail_list_id WHERE u.[user_id] = @id", new Dictionary <string, object>() { { "id", (int)user["user_id"] } }); if (err) { return(Response.Error("Unable to get mail list subscriptions.", FancyConn.Shared.lastError)); } var mailLists = new JArray(); while (reader.Read()) { mailLists.Add(new JObject(from c in reader.GetColumnSchema() select new JProperty(c.ColumnName, reader[(int)c.ColumnOrdinal]))); } user.Add("mail_lists", mailLists); return(Response.Ok("Retrieved user successfully.", new { user, created, userShifts })); } catch (Exception e) { return(Response.Error(data: e)); } finally { FancyConn.Shared.Dispose(); } }
/// <summary> /// Create a new user or update a current or given user. /// </summary> /// <param name="type">How to select the user to update</param> /// <param name="id">ID of user to update (if in ID mode)</param> public static async Task <IActionResult> CreateOrUpdate( HttpRequest req, CloudBlobDirectory blobDirectory, ClaimsPrincipal principal, ILogger log, ReqType type = ReqType.ID, int id = -1) { var body = await req.GetBodyParameters(); var userId = id; if (type == ReqType.Current) { var(err, currentId) = await FancyConn.Shared.Scalar("SELECT [user_id] FROM [user] WHERE [email] = @email", new Dictionary <string, object>() { { "email", principal.FindFirst(ClaimTypes.Email).Value } }); if (err) { return(Response.Error("Error finding user in database.", FancyConn.Shared.lastError)); } if (currentId == null) { return(Response.Error("User does not exist. This should not happen")); } userId = (int)currentId; } var validCols = new List <string> { "first_name", "last_name", "phone_1", "phone_2", "school", "role_id", "bio", "title", "show_exec" }; // cannot update email from user profile page if (type != ReqType.Current) { validCols.Add("email"); } // handle exec picture var pic = req.Form.Files.GetFile("pic"); if (pic != null) { var picStream = pic.OpenReadStream(); var img = Image.Load(picStream); // resize to width of 350px while maintaining aspect ratio img.Mutate(i => i.Resize(0, 350)); var blob = blobDirectory.GetBlockBlobReference(pic.FileName.WithTimestamp()); await blob.UploadFromStreamAsync(pic.OpenReadStream()); validCols.Add("pic"); body.Add("pic", blob.Uri.ToString()); } // handle body using email if CurrentUser, user_id if not var(userQuery, userParams) = FancyConn.MakeUpsertQuery("user", "user_id", userId, validCols, body); // ensure at least one valid column was passed if (!string.IsNullOrEmpty(userQuery)) { var(err, newId) = await FancyConn.Shared.Scalar(userQuery, userParams); if (err) { return(Response.Error("Unable to update user.", FancyConn.Shared.lastError)); } // update ID for future queries if this is a create if (type == ReqType.New) { userId = (int)newId; } } // handle mail list signups var mailLists = body["mail_lists"]?.Value <JArray>(); if (mailLists != null && mailLists.Count > 0) { var i = 0; var queryParams = new Dictionary <string, object>() { { "id", userId } }; var toDelete = new List <string>(); var query = new StringBuilder(); foreach (var list in mailLists) { var listId = list["mail_list_id"].Value <int>(); if (list["subscribed"].Value <bool>()) { // wacky query to ensure that we don't get a bunch of duplicate key errors query.AppendLine( $@"BEGIN TRY INSERT INTO [user_mail_list]([user_id], [mail_list_id]) VALUES (@id, @p{i}) END TRY BEGIN CATCH IF ERROR_NUMBER() != 2627 THROW -- a unique key constraint violation END CATCH;"); queryParams.Add($"p{i.ToString()}", listId); i++; } else { toDelete.Add($"p{i.ToString()}"); queryParams.Add($"p{i.ToString()}", listId); i++; } } query.AppendLine($"DELETE FROM [user_mail_list] WHERE [user_id] = @id AND [mail_list_id] IN ({string.Join(", ", toDelete)});"); var(err, _) = await FancyConn.Shared.NonQuery(query.ToString(), queryParams); if (err) { return(Response.Error("Unable to update mail list subscriptions.", FancyConn.Shared.lastError)); } } return(Response.Ok("User updated successfully.")); }
/// <summary> /// Update or create an event, along with its shifts. /// </summary> /// <param name="id">ID of event to update, -1 to create</param> public static async Task <IActionResult> Update( HttpRequest req, ClaimsPrincipal principal, ILogger log, CloudBlobDirectory blobDirectory, int id) { var body = await req.GetBodyParameters(); var deleteShifts = body["deleteShifts"]?.Value <JArray>().Select(s => (int)s); var shifts = body["shifts"]?.Values <JObject>(); var eventCols = new List <string> { "name", "description", "transport", "address", "add_info" }; // handle hours letter var letter = req.Form.Files.GetFile("letter"); if (letter != null) { var blob = blobDirectory.GetBlockBlobReference(letter.FileName.WithTimestamp()); await blob.UploadFromStreamAsync(letter.OpenReadStream()); eventCols.Add("letter"); body.Add("letter", blob.Uri.ToString()); } object newId = null; var(eventQuery, eventParams) = FancyConn.MakeUpsertQuery("event", "event_id", id, eventCols, body); if (!string.IsNullOrEmpty(eventQuery)) { var err = false; (err, newId) = await FancyConn.Shared.Scalar(eventQuery, eventParams); if (err) { return(Response.Error("Unable to update event.", FancyConn.Shared.lastError)); } } // handle deletion of shifts if (deleteShifts.Count() > 0) { var delQuery = $"DELETE FROM [shifts] WHERE [shift_id] IN ({string.Join(", ", deleteShifts.Map(x => $"@{x}"))})"; var delParams = deleteShifts.ToDictionary(x => $"p{x}", x => (object)x); var(err, _) = await FancyConn.Shared.NonQuery(delQuery, delParams); if (err) { return(Response.Error("Unable to delete shifts.", FancyConn.Shared.lastError)); } } // handle shift updates if (shifts.Count() > 0) { var errors = await Task.WhenAll(shifts.Select <JObject, Task <int?> >(async s => { var shiftCols = new string[] { "event_id", "shift_num", "max_spots", "start_time", "end_time", "meals", "notes" }.ToList(); s["event_id"] = new JValue(newId ?? id); var(shiftQuery, shiftParams) = FancyConn.MakeUpsertQuery("shift", "shift_id", (int)s["shift_id"], shiftCols, s); var(error, _) = await FancyConn.Shared.NonQuery(shiftQuery, shiftParams); if (error) { return((int)s["shift_num"]); } return(null); })); var failed = string.Join(", ", errors.Where(x => x != null).ToList()); if (failed.Length > 0) { return(Response.Error($"Unable to update shift {failed}", FancyConn.Shared.lastError)); } } return(Response.Ok("Updated event successfully.")); }