public DBItem SingleOrDefault()
{
Tabloid newTabloid = new Tabloid(_db)
{
Name = _tabloidName
};
return(_db.ExecuteRead(_db.CommandSet.SELECT(_db, newTabloid, _columns, _condition, _join, _order, _groupColumns, 2, _page, _dropStep), newTabloid).SingleOrDefault());
}
public ListJson <DBItem> ToList()
{
Tabloid newTabloid = new Tabloid(_db)
{
Name = _tabloidName
};
return(_db.ExecuteRead(_db.CommandSet.SELECT(_db, newTabloid, _columns, _condition, _join, _order, _groupColumns, _limit, _page, _dropStep), newTabloid));
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
COREobject core = COREobject.i;
DBConnection db = core.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
var tableUsers = db.Table("Users", searchInShared);
DBItem epkUser = tableUsers.Select().Where(c => c.Column("ad_email").Equal(core.User.Email)).FirstOrDefault();
int userPernr;
if (vars.ContainsKey("Pernr"))
{
userPernr = (int)vars["Pernr"];
}
else if (epkUser != null)
{
userPernr = (int)epkUser["pernr"];
}
else
{
throw new Exception("Váš účet není propojen s tabulkou Users");
}
Tabloid superiorView = db.Tabloid("SuperiorMapping", searchInShared);
DBItem superiorMapping = superiorView.Select().Where(c => c.Column("pernr").Equal(userPernr)).FirstOrDefault();
DBItem result;
Tabloid userView = db.Tabloid("UserView", searchInShared);
if (superiorMapping != null)
{
outputVars["NoSuperior"] = false;
var superiorPernr = superiorMapping["superior_pernr"];
var assistantView = db.Tabloid("AssistantMapping", searchInShared);
var assistantMapping = assistantView.Select().Where(c => c.Column("pernr").Equal(superiorPernr)).FirstOrDefault();
if (assistantMapping != null)
{
var assistentPernr = assistantMapping["assistant_pernr"];
result = userView.Select().Where(c => c.Column("pernr").Equal(assistentPernr)).FirstOrDefault();
}
else
{
result = userView.Select().Where(c => c.Column("pernr").Equal(superiorPernr)).FirstOrDefault();
}
}
else
{
result = userView.Select().Where(c => c.Column("pernr").Equal(userPernr)).FirstOrDefault();
outputVars["NoSuperior"] = true;
}
result["DisplayName"] = result["vorna"] + " " + result["nachn"];
outputVars["SuperiorData"] = result;
}
public TabloidPageViewModel(INavigationService navigationService, IPageDialogService pageDialogService) : base(navigationService)
{
Tabloids = new ObservableCollection <Tabloid>
{
new Tabloid()
};
Tabloid = new Tabloid();
Task.Run(async() =>
{
await GetTabloidAsync();
}).GetAwaiter();
}
public int Count()
{
Tabloid newTabloid = new Tabloid(_db)
{
Name = _tabloidName
};
using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.SELECT_count(_db, newTabloid, _condition, _join, _groupColumns)))
{
reader.Read();
return(Convert.ToInt32(reader["count"]));
}
}
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
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();
}