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