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 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);
        }