public Select DropStep(int finalCount, ESqlFunction function = ESqlFunction.LAST, AscDesc ascDesc = AscDesc.Asc, params string[] orderColumnNames) { if (!orderColumnNames.Any()) { throw new ArgumentException("orderedColumnNames needs at least one column"); } return(DropStep(finalCount, new Order { AscDesc = ascDesc, Columns = orderColumnNames.Select(c => c.Contains('.') ? c : $"{_tabloidName}.{c}") }, function)); }
public Select DropStep(int finalCount, Order order, ESqlFunction function = ESqlFunction.LAST) { if (function == ESqlFunction.none) { throw new ArgumentException("DropStep with no function return no data!"); } _dropStep = new DropStep { FinalCount = finalCount, Function = function, Order = order }; return(this); }
public Select Group(ESqlFunction function = ESqlFunction.none, Func <ConditionColumn, ConditionConcat> having = null, params string[] columns) { _groupColumns = new GroupBy(_db) { Columns = columns.Select(c => c.Contains('.') ? c : $"{_tabloidName}.{c}"), Function = function, Having = new Manager <Condition>(_db) }; if (having != null) { _groupColumns.Having.i.tabloidName = _tabloidName; _groupColumns.Having.Start(); having(new ConditionColumn(_groupColumns.Having)); } return(this); }
public static bool NeedsInnerQuery(this ESqlFunction function) { switch (function) { case ESqlFunction.none: case ESqlFunction.MIN: case ESqlFunction.MAX: case ESqlFunction.COUNT: case ESqlFunction.AVG: case ESqlFunction.SUM: return(false); case ESqlFunction.FIRST: case ESqlFunction.LAST: return(true); default: throw new InvalidOperationException(); } }
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message) { // init DBConnection db = COREobject.i.Entitron; // get view string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null; bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false; AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc; Tabloid view = db.Tabloid((string)vars["ViewName"], searchInShared); var select = view.Select(); int conditionIndex = 1; /// each condition while (true) { string postfix = (conditionIndex == 1 ? "" : conditionIndex.ToString()); string columnNameVars = $"ColumnName{postfix}"; string valueVars = $"Value{postfix}"; // select all - no condition if (!vars.ContainsKey(columnNameVars) || !vars.ContainsKey(valueVars)) { break; } string columnName = (string)vars[columnNameVars]; object value = vars[valueVars]; if (value is Newtonsoft.Json.Linq.JValue) { select.Where(c => c.Column(columnName).Equal(value.ToString())); } // condition is list else if (!(value is string) && value is IEnumerable) { // condition list is empty -> return empty list if (((IEnumerable <object>)value).Count() == 0) { outputVars["Result"] = new List <DBItem>(); return; } select.Where(c => c.Column(columnName).In((IEnumerable <object>)value)); } // condition is list of strings else if ((value is string) && ((string)value).Contains(",")) { string[] list = vars["Value"].ToString().Split(','); select.Where(c => c.Column(columnName).In(list)); } // condition is object else { select.Where(c => c.Column(columnName).Equal(value)); } conditionIndex++; } // MaxRows if (vars.ContainsKey("MaxRows") && orderBy != null) { select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy); } // order select.Order(isDescending, orderBy); // group if (vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"])) { ESqlFunction function = ESqlFunction.SUM; if (vars.ContainsKey("GroupByFunction")) { switch ((string)vars["GroupByFunction"]) { case "none": function = ESqlFunction.none; break; case "MAX": function = ESqlFunction.MAX; break; case "MIN": function = ESqlFunction.MIN; break; case "AVG": function = ESqlFunction.AVG; break; case "COUNT": function = ESqlFunction.COUNT; break; case "SUM": function = ESqlFunction.SUM; break; case "FIRST": function = ESqlFunction.FIRST; break; case "LAST": function = ESqlFunction.LAST; break; } } select.Group(function, columns: (string)vars["GroupBy"]); } outputVars["Result"] = select.ToList(); }
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message) { // init COREobject core = COREobject.i; DBConnection db = core.Entitron; bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false; string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null; AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc; int? maxRows = vars.ContainsKey("MaxRows") ? (int?)vars["MaxRows"] : null; string groupBy = vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"]) ? (string)vars["GroupBy"] : null; // Select select = db.Select((string)vars["ViewName"], searchInShared); int CondCount = vars.Keys.Where(k => k.StartsWith("CondColumn[") && k.EndsWith("]")).Count(); // setConditions for (int i = 0; i < CondCount; i++) { string condOperator = vars.ContainsKey($"CondOperator[{i}]") ? (string)vars[$"CondOperator[{i}]"] : "Equal"; string condColumn = (string)vars[$"CondColumn[{i}]"]; object condValue = vars[$"CondValue[{i}]"]; var value = condValue; switch (condOperator) { case "Less": select.Where(c => c.Column(condColumn).Less(value)); break; case "LessOrEqual": select.Where(c => c.Column(condColumn).LessOrEqual(value)); break; case "Greater": select.Where(c => c.Column(condColumn).Greater(value)); break; case "GreaterOrEqual": select.Where(c => c.Column(condColumn).GreaterOrEqual(value)); break; case "Equal": select.Where(c => c.Column(condColumn).Equal(value)); break; case "IsIn": select.Where(c => c.Column(condColumn).In((IEnumerable <object>)value)); break; default: // == select.Where(c => c.Column(condColumn).Equal(value)); break; } } // MaxRows if (maxRows != null && orderBy != null) { select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy); } // order select.Order(isDescending, orderBy); // group if (groupBy != null) { ESqlFunction function = ESqlFunction.SUM; if (vars.ContainsKey("GroupByFunction")) { switch ((string)vars["GroupByFunction"]) { case "none": function = ESqlFunction.none; break; case "MAX": function = ESqlFunction.MAX; break; case "MIN": function = ESqlFunction.MIN; break; case "AVG": function = ESqlFunction.AVG; break; case "COUNT": function = ESqlFunction.COUNT; break; case "SUM": function = ESqlFunction.SUM; break; case "FIRST": function = ESqlFunction.FIRST; break; case "LAST": function = ESqlFunction.LAST; break; } } select.Group(function, columns: (string)vars["GroupBy"]); } // return outputVars["Data"] = select.ToList(); }
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message) { // init DBConnection db = COREobject.i.Entitron; string tableName = vars.ContainsKey("TableName") ? (string)vars["TableName"] : (string)vars["__TableName__"]; bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false; string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null; AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc; // Tabloid tabloid = db.Tabloid(tableName, searchInShared); Select select = tabloid.Select(); int CondCount = vars.Keys.Where(k => k.StartsWith("CondColumn[") && k.EndsWith("]")).Count(); // setConditions for (int i = 0; i < CondCount; i++) { string condOperator = vars.ContainsKey($"CondOperator[{i}]") ? (string)vars[$"CondOperator[{i}]"] : "Equal"; string condColumn = (string)vars[$"CondColumn[{i}]"]; object condValue = vars[$"CondValue[{i}]"]; DBColumn column = tabloid.Columns.Single(c => c.Name == condColumn); object value = condOperator != "IsIn" ? DataType.ConvertTo(column.Type, condValue) : condValue; switch (condOperator) { case "Less": select.Where(c => c.Column(condColumn).Less(value)); break; case "LessOrEqual": select.Where(c => c.Column(condColumn).LessOrEqual(value)); break; case "Greater": select.Where(c => c.Column(condColumn).Greater(value)); break; case "GreaterOrEqual": select.Where(c => c.Column(condColumn).GreaterOrEqual(value)); break; case "Equal": select.Where(c => c.Column(condColumn).Equal(value)); break; case "IsIn": // string, multiple values if ((condValue is string) && ((string)condValue).Contains(",")) { select.Where(c => c.Column(condColumn).In((condValue as string).Split(','))); } // Enumerable else { select.Where(c => c.Column(condColumn).In((IEnumerable <object>)condValue)); } break; default: // == select.Where(c => c.Column(condColumn).Equal(value)); break; } } // top if (vars.ContainsKey("Top")) { select = select.Limit((int)vars["Top"]); } // order if (!string.IsNullOrWhiteSpace(orderBy)) { select = select.Order(isDescending, orderBy); if (vars.ContainsKey("MaxRows")) { select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy); } } // Group if (vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"])) { ESqlFunction function = ESqlFunction.SUM; if (vars.ContainsKey("GroupByFunction")) { switch ((string)vars["GroupByFunction"]) { case "none": function = ESqlFunction.none; break; case "MAX": function = ESqlFunction.MAX; break; case "MIN": function = ESqlFunction.MIN; break; case "AVG": function = ESqlFunction.AVG; break; case "COUNT": function = ESqlFunction.COUNT; break; case "SUM": function = ESqlFunction.SUM; break; case "FIRST": function = ESqlFunction.FIRST; break; case "LAST": function = ESqlFunction.LAST; break; } } select.Group(function, columns: (string)vars["GroupBy"]); } // return outputVars["Data"] = select.ToList(); }