public JObject deleteLoan(JObject request) { // Get arguments request.TryGetValue("loanId", out JToken loanId); // Verify arguments if (loanId == null || loanId.Type != JTokenType.Integer) { return(Templates.InvalidArgument("loanId")); } // Build condition var condition = new MySqlConditionBuilder(); if (CurrentUser.Permission <= UserPermission.User) { condition.Column("user").Equals(CurrentUser.Username, MySqlDbType.String); } condition.And().Column("id").Equals(loanId, MySqlDbType.String); // Get and delete loan, or return error if no such loan exists var loan = Connection.Select <LoanItem>(condition, range: (0, 1)).FirstOrDefault(); if (loan == null) { return(Templates.NoSuchLoan(loanId.ToString())); } if (loan.Start < DateTime.Now) { return(Templates.LoanAlreadyStarted()); } Connection.Delete(loan); // Create response JObject response = new JObject() { { "reason", null } }; return(response); }
public static MySqlConditionBuilder CreateCondition(JObject criteria, MySqlConditionBuilder condition = null) { condition = condition ?? new MySqlConditionBuilder(); condition.NewGroup(); int i = 0; foreach (KeyValuePair <string, JToken> pair in criteria) { if (i > 0) { condition.And(); } condition.Column(pair.Key); string value = (string)pair.Value; string[] operands = value.Split("OR"); foreach (string operand in operands) { string[] split = operand.Split(" "); if (split[0] == "LIKE") { condition.Like(split[1]); } else { condition.Equals(operand, MySql.Data.MySqlClient.MySqlDbType.String); } if (operands.Last() != operand) { condition.Or(); } } i++; } return(condition.EndGroup()); }
public JObject getImages(JObject request) { //Get arguments request.TryGetValue("columns", out JToken requestColumns); request.TryGetValue("images", out JToken requestImageIds); if (requestImageIds == null) { return(Templates.MissingArguments("imageIds")); } // Verify arguments List <string> failedVerifications = new List <string>(); if (requestColumns != null && (requestColumns.Type != JTokenType.Array || ((JArray)requestColumns).Count == 0)) { failedVerifications.Add("columns"); } if (requestImageIds.Type != JTokenType.Array) { failedVerifications.Add("images"); } if (failedVerifications.Any()) { return(Templates.InvalidArguments(failedVerifications.ToArray())); } // Build condition var condition = new MySqlConditionBuilder(); bool first = true; foreach (string id in requestImageIds) { if (!first) { condition.Or(); } condition.Column(Image.indexes.First(x => x.Type == Index.IndexType.PRIMARY).Columns[0].Column); condition.Equals(id, MySqlDbType.String); first = false; } // If condition is blank, add a condition that is false if (first) { condition.Not().Null().Is().Null(); } // Prepare query values if (requestColumns == null || !requestColumns.Any()) { requestColumns = new JArray(Image.metadata.Select(x => x.Column)); } // Add primary key column name ((JArray)requestColumns).Insert(0, Image.indexes.First(x => x.Type == Index.IndexType.PRIMARY).Columns[0].Column); // Get images List <object[]> imageData = Connection.Select <Image>(requestColumns.ToObject <string[]>(), condition).ToList(); //Create base response var responseData = new JObject(); var response = new JObject() { { "reason", null }, { "responseData", responseData } }; foreach (var data in imageData) { var item = new JObject(); for (int i = 1; i < requestColumns.Count(); i++) { item.Add((string)requestColumns[i], new JValue(data[i])); } responseData.Add((string)data[0], item); } return(response); }
public JObject getUsers(JObject request) { request.TryGetValue("permission", out JToken permValue); request.TryGetValue("username", out JToken usernameValue); // Verify arguments int permission; string username; if (permValue != null && permValue.Type == JTokenType.Integer) { permission = permValue.ToObject <int>(); if (permission <= -1 || permission >= 4) { return(Templates.InvalidArguments("Permission out of range: " + permission)); } } else { permission = -1; } if (usernameValue != null && usernameValue.Type == JTokenType.String) { username = usernameValue.ToObject <string>(); } else { username = null; } username += "%"; //Construct query MySqlConditionBuilder query = new MySqlConditionBuilder(); if (username != null) { query.Column("username") .Like(username); } if (permission != -1) { query.And() .Column("permissions") .Equals(permission, MySql.Data.MySqlClient.MySqlDbType.Int32); } //Get users List <User> userdata = Connection.Select <User>(query).ToList(); //Create response list JArray users = new JArray(); foreach (User user in userdata) { users.Add(new JObject() { { "username", user.Username }, { "permission", (int)user.Permission } }); } //Create response return(new JObject() { { "reason", null }, { "responseData", users } }); }
public JObject getProducts(JObject request) { // Get request arguments request.TryGetValue("columns", out JToken requestColumns); request.TryGetValue("criteria", out JToken requestCriteria); request.TryGetValue("language", out JToken requestLanguages); request.TryGetValue("start", out JToken requestRangeStart); request.TryGetValue("amount", out JToken requestRangeAmount); MySqlConditionBuilder condition = new MySqlConditionBuilder(); // Verify the types of the arguments List <string> failedVerifications = new List <string>(); if (requestColumns != null && (requestColumns.Type != JTokenType.Array || requestColumns.Any(x => x.Type != JTokenType.String))) { failedVerifications.Add("colums"); } if (requestCriteria != null) { try { condition = Misc.CreateCondition((JObject)requestCriteria, condition); } catch (Exception) { failedVerifications.Add("criteria"); } } if (requestLanguages != null && (requestLanguages.Type != JTokenType.Array || requestLanguages.Any(x => x.Type != JTokenType.String))) { failedVerifications.Add("language"); } if (requestRangeStart != null && (requestRangeStart.Type != JTokenType.Integer)) { failedVerifications.Add("start"); } if (requestRangeAmount != null && (requestRangeAmount.Type != JTokenType.Integer)) { failedVerifications.Add("amount"); } if (failedVerifications.Any()) { return(Templates.InvalidArguments(failedVerifications.ToArray())); } //Create base response var responseData = new JArray(); JObject response = new JObject() { { "reason", null }, { "responseData", responseData } }; // Prepare values for database call var productPrimary = Product.indexes.First(x => x.Type == Index.IndexType.PRIMARY).Columns[0]; condition.And() .Column(productPrimary.Column) .NotEquals(0, productPrimary.Type); (ulong, ulong)range = (requestRangeStart?.ToObject <ulong>() ?? 0, requestRangeAmount?.ToObject <ulong>() ?? ulong.MaxValue); if (requestColumns == null || requestColumns.Count() == 0) { requestColumns = new JArray(Product.metadata.Select(x => x.Column)); } else if (requestLanguages != null && !requestColumns.Contains("name")) { ((JArray)requestColumns).Add("name"); } // Remove unknown language columns requestLanguages = new JArray(requestLanguages.Where(x => LanguageItem.metadata.Select(y => y.Column).Contains(x.ToString()))); // Request category data from database List <object[]> categoryData = Connection.Select <Product>(requestColumns.ToObject <string[]>(), condition, range).ToList(); // Add all categories as dictionaries to responseData foreach (var data in categoryData) { var item = new JObject(); for (int i = 0; i < requestColumns.Count(); i++) { item[(string)requestColumns[i]] = new JValue(data[i]); } responseData.Add(item); } // Add translations if specified in the arguments if (requestLanguages != null) { List <string> nameIds = responseData.Select(x => x["name"].ToString()).ToList(); // Build a condition to get all language items in one query var nameCondition = new MySqlConditionBuilder(); foreach (var name in nameIds) { nameCondition.Or(); nameCondition.Column("id"); nameCondition.Equals(name, MySqlDbType.String); } // Get the specified translations var languageColumns = requestLanguages.ToObject <List <string> >(); if (languageColumns.Count == 0) { languageColumns.AddRange(LanguageItem.metadata.Select(x => x.Column)); } else { languageColumns.Insert(0, "id"); } List <object[]> names = Connection.Select <LanguageItem>(languageColumns.ToArray(), nameCondition).ToList(); for (int i = 0; i < responseData.Count; i++) { var nameData = names.First(x => x[0].Equals(nameIds[i])); var translations = new JObject(); for (int j = 1; j < languageColumns.Count; j++) { if (nameData[j] != null) { translations[languageColumns[j]] = new JValue(nameData[j]); } } responseData[i]["name"] = translations; } } if (requestLanguages != null) { List <string> descIds = responseData.Select(x => x["description"].ToString()).ToList(); // Build a condition to get all language items in one query var descCondition = new MySqlConditionBuilder(); foreach (var desc in descIds) { descCondition.Or(); descCondition.Column("id"); descCondition.Equals(desc, MySqlDbType.String); } // Get the specified translations var languageColumns = requestLanguages.ToObject <List <string> >(); if (languageColumns.Count == 0) { languageColumns.AddRange(LanguageItem.metadata.Select(x => x.Column)); } else { languageColumns.Insert(0, "id"); } List <object[]> names = Connection.Select <LanguageItem>(languageColumns.ToArray(), descCondition).ToList(); for (int i = 0; i < responseData.Count; i++) { var nameData = names.First(x => x[0].Equals(descIds[i])); var translations = new JObject(); for (int j = 1; j < languageColumns.Count; j++) { if (nameData[j] != null) { translations[languageColumns[j]] = new JValue(nameData[j]); } } responseData[i]["description"] = translations; } } return(response); }