public static StandardResult <SimpleTable> Execute(SimpleTable sourceTable, string sourceKeyField, string mergeField, SimpleTable lookupTable, string lookupKeyField, string lookupDataField, bool overwrite, bool caseInsensitive = true)
{
if (!sourceTable.ColumnNames.Contains(sourceKeyField))
{
return(StandardResult <SimpleTable> .ReturnError("LookupUpdateTable() error: source key field does not exists: " + sourceKeyField));
}
if (!sourceTable.ColumnNames.Contains(mergeField))
{
return(StandardResult <SimpleTable> .ReturnError("LookupUpdateTable() error: merge field does not exists: " + mergeField));
}
var newTable = new SimpleTable();
foreach (var column in sourceTable.ColumnNames)
{
newTable.AddColumnName(column);
}
foreach (var sourceRow in sourceTable)
{
var newRow = newTable.CreateRow();
for (int i = 0; i < sourceRow.ColumnCount; i++)
{
var value = sourceRow[i];
if (value == null)
{
continue;
}
newRow[i] = value;
}
}
foreach (var row in newTable)
{
string key = row[sourceKeyField];
if (!overwrite && !string.IsNullOrWhiteSpace(row[mergeField]))
{
continue;
}
if (caseInsensitive)
{
key = key.ToLower();
}
string lookup = Lookup(lookupTable, key, lookupKeyField, lookupDataField);
row[mergeField] = lookup;
}
return(StandardResult <SimpleTable> .ReturnResult(newTable));
}
public static StandardResult <SimpleTable> Execute(string data, string newRow, char delimiter = '=', IList <string> columns = null)
{
var lines = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
var table = new SimpleTable();
if (columns != null)
{
for (int i = 0; i < columns.Count; i++)
{
table.SetColumnName(i, columns[i]);
}
}
SimpleTableRow row = null;
foreach (var line in lines)
{
if (string.IsNullOrEmpty(line))
{
continue;
}
if (line.StartsWith("#"))
{
continue;
}
if (line == newRow)
{
row = table.CreateRow();
continue;
}
int pos = line.IndexOf(delimiter);
if (pos < 0)
{
continue;
}
string name = line.Substring(0, pos);
string value = line.Substring(pos + 1);
if (columns == null && !table.ContainColumn(name))
{
table.SetColumnName(table.ColumnNames.Count, name);
}
if (table.ContainColumn(name))
{
row.SetField(name, value);
}
}
return(StandardResult <SimpleTable> .ReturnResult(table));
}
public static StandardResult <SimpleTable> Execute(string data, bool containsHeader = true, bool mapHeaderNames = true, bool unespace = true)
{
var lines = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
var start = 0;
var table = new SimpleTable();
if (containsHeader)
{
start = 1;
if (mapHeaderNames)
{
var parts = lines[0].Split('\t');
for (int i = 0; i < parts.Length; i++)
{
var value = parts[i];
if (string.IsNullOrWhiteSpace(value))
{
continue;
}
table.SetColumnName(i, value);
}
}
}
for (int index = start; index < lines.Length; index++)
{
var line = lines[index];
if (index + 1 == lines.Length && string.IsNullOrWhiteSpace(line))
{
continue;
}
var parts = line.Split('\t');
var row = table.CreateRow();
for (int i = 0; i < parts.Length; i++)
{
var part = parts[i];
if (unespace)
{
part = Unescape(part);
}
row.SetField(i, part);
}
}
return(StandardResult <SimpleTable> .ReturnResult(table));
}
public static SimpleTable CreateTestData()
{
var table = new SimpleTable();
table.Name = "TestTable";
table.SetColumnName(0, "ID");
table.SetColumnName(1, "Data");
var row = table.CreateRow();
row.SetField(0, "1");
row.SetField(1, "ABC");
row = table.CreateRow();
row.SetField(0, "2");
row.SetField(1, "DEF");
row = table.CreateRow();
row.SetField(0, "3");
row.SetField(1, "GHI");
return(table);
}
private static SimpleTable GetAllUsersTable(string site)
{
var people = GetAllUsers(site);
var table = new SimpleTable();
table.AddColumnName("ID");
table.AddColumnName("Name");
table.AddColumnName("EMail");
foreach (var person in people)
{
var row = table.CreateRow();
row.SetField("ID", person.id);
row.SetField("Name", person.Title);
row.SetField("EMail", person.Email);
}
return(table);
}
public static StandardResult <SimpleTable> Execute(string data, string destination = "name", string source = "value", char delimiter = '=')
{
var lines = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
var table = new SimpleTable();
table.SetColumnName(0, source);
table.SetColumnName(1, destination);
foreach (var line in lines)
{
if (string.IsNullOrEmpty(line))
{
continue;
}
if (line.StartsWith("#"))
{
continue;
}
int pos = line.IndexOf(delimiter);
if (pos < 0)
{
continue;
}
string dst = line.Substring(0, pos);
string src = line.Substring(pos + 1);
var row = table.CreateRow();
row.SetField(0, dst);
row.SetField(1, src);
}
return(StandardResult <SimpleTable> .ReturnResult(table));
}
public static SimpleTable Copy(this SimpleTable table)
{
var copy = new SimpleTable();
// copy of column names
for (int i = 0; i < table.ColumnNames.Count; i++)
{
var name = table.GetColumnName(i);
if (name == null)
{
continue;
}
copy.SetColumnName(i, name);
}
// copy row
foreach (var row in table)
{
var newRow = copy.CreateRow();
for (int i = 0; i < row.ColumnCount; i++)
{
var value = row[i];
if (value == null)
{
continue;
}
newRow[i] = value;
}
}
return(copy);
}
public static StandardResult <SimpleTable> Execute(SimpleTable sourceTable, SimpleTable aggregateTable)
{
if (sourceTable == null)
{
return(StandardResult <SimpleTable> .ReturnError("AggregateTable() error: source table null"));
}
if (aggregateTable == null)
{
return(StandardResult <SimpleTable> .ReturnError("AggregateTable() error: aggregate table null"));
}
var newTable = new SimpleTable();
var nonaggregateFields = new Dictionary <string, Expression>();
var aggregateFields = new Dictionary <string, Expression>();
// create columns
foreach (var map in aggregateTable)
{
var source = map["source"];
var destination = map["destination"];
newTable.AddColumnName(destination);
var expression = ExpressionCache.Compile(source);
if (expression == null)
{
return(StandardResult <SimpleTable> .ReturnError("AggregateTable() error: evaluator returns null", "Source: " + source));
}
// does expression contain any aggregate methods?
bool aggregateMethods = false;
foreach (var token in expression.Parser.Tokenizer.Tokens)
{
if (token.TokenType == TokenType.Method && aggregateMethodsNames.Contains(token.Value))
{
aggregateMethods = true;
break;
}
}
if (aggregateMethods)
{
aggregateFields.Add(destination, expression);
}
else
{
nonaggregateFields.Add(destination, expression);
}
}
var associateBuckets = new Dictionary <SimpleTableRow, List <SimpleTableRow> >();
// create new rows with non-aggregate expressions & track assocations
{
var fieldSource = new FieldDataSource();
var context = new Context()
{
FieldSource = fieldSource
};
var bucketsMap = new Dictionary <List <string>, SimpleTableRow>();
foreach (var sourceRow in sourceTable)
{
fieldSource.Row = sourceRow;
var fields = new List <string>();
foreach (var field in nonaggregateFields)
{
var destination = field.Key;
var expression = field.Value;
var result = expression.Evaluate(context);
if (result.IsError)
{
return(StandardResult <SimpleTable> .ReturnError("AggregateTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, result.String));
}
fields.Add(ExpandTable.ToString(result));
}
var row = Find(fields, bucketsMap);
if (row == null)
{
row = newTable.CreateRow();
bucketsMap.Add(fields, row);
// populate non-aggregated rows
int index = 0;
foreach (var non in nonaggregateFields)
{
var destination = non.Key;
var expression = non.Value;
var result = fields[index++];
row[destination] = result;
}
}
List <SimpleTableRow> bucketRows;
if (!associateBuckets.TryGetValue(row, out bucketRows))
{
bucketRows = new List <SimpleTableRow>();
associateBuckets.Add(row, bucketRows);
}
bucketRows.Add(sourceRow);
}
}
// evaluation aggregate expressions
{
var fieldSource = new FieldDataSource();
var aggregateFieldSource = new AggregateFieldSource();
var context = new Context()
{
FieldSource = fieldSource, AggregateFieldSource = aggregateFieldSource
};
foreach (var aggregateRow in newTable)
{
var bucketRows = associateBuckets[aggregateRow];
fieldSource.Row = bucketRows[0]; // any row -- note: undefine to reference non-aggregate fields outside of the aggregate expression
aggregateFieldSource.TableRows = bucketRows;
foreach (var field in aggregateFields)
{
var destination = field.Key;
var expression = field.Value;
var result = expression.Evaluate(context);
if (result.IsError)
{
return(StandardResult <SimpleTable> .ReturnError("AggregateTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, result.String));
}
aggregateRow[destination] = ExpandTable.ToString(result);
}
}
}
return(StandardResult <SimpleTable> .ReturnResult(newTable));
}
public static StandardResult <SimpleTable> Execute(SimpleTable sourceTable, SimpleTable mapTable)
{
if (mapTable == null)
{
return(StandardResult <SimpleTable> .ReturnError("TransformTable() error: map table null"));
}
var newTable = new SimpleTable();
// create columns
foreach (var map in mapTable)
{
var expression = ExpressionCache.Compile(map["source"]);
if (expression == null)
{
return(StandardResult <SimpleTable> .ReturnError("TransformTable() error: evaluator returns null"));
}
newTable.AddColumnName(map["destination"]);
}
var fieldSource = new FieldDataSource();
foreach (var sourceRow in sourceTable)
{
fieldSource.Row = sourceRow;
var destinationRow = newTable.CreateRow();
foreach (var map in mapTable)
{
var source = map["source"];
var destination = map["destination"];
var expression = ExpressionCache.Compile(source);
if (expression == null)
{
return(StandardResult <SimpleTable> .ReturnError("ExpandTable() error: evaluator returns null"));
}
if (!expression.IsValid)
{
return(StandardResult <SimpleTable> .ReturnError("ExpandTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression));
}
var result = expression.Evaluate(new Context()
{
FieldSource = fieldSource
});
if (result.IsError)
{
return(StandardResult <SimpleTable> .ReturnError("ExpandTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, result.String));
}
destinationRow[destination] = ExpandTable.ToString(result);
}
}
return(StandardResult <SimpleTable> .ReturnResult(newTable));
}
public static StandardResult <SimpleTable> Execute(SimpleTable sourceTable, SimpleTable validationRules, bool rowPerMatch, bool overwrite)
{
var resultsTable = new SimpleTable();
foreach (var column in sourceTable.ColumnNames)
{
resultsTable.AddColumnName(column);
}
foreach (var column in validationRules.ColumnNames)
{
if (column == "Match")
{
continue;
}
resultsTable.AddColumnName(column);
}
var fieldSource = new FieldDataSource();
foreach (var sourceRow in sourceTable)
{
fieldSource.Row = sourceRow;
SimpleTableRow row = null;
foreach (var rule in validationRules)
{
var match = rule["Match"];
if (string.IsNullOrWhiteSpace(match))
{
continue;
}
var expression = ExpressionCache.Compile(match);
if (expression == null)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: evaluator returns null"));
}
if (!expression.IsValid)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression));
}
var result = expression.Evaluate(new Context()
{
FieldSource = fieldSource
});
if (result.IsError)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, result.String));
}
if (!result.Boolean)
{
continue;
}
if (row == null || rowPerMatch)
{
row = resultsTable.CreateRow();
}
foreach (var column in sourceTable.ColumnNames)
{
row[column] = sourceRow[column];
}
foreach (var column in validationRules.ColumnNames)
{
if (column == "Match")
{
continue;
}
var text = rule[column];
if (string.IsNullOrWhiteSpace(text))
{
continue;
}
var expression2 = ExpressionCache.Compile(text);
if (expression2 == null)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: evaluator returns null"));
}
if (!expression2.IsValid)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: occurred during evaluating: " + expression2.Parser.Tokenizer.Expression));
}
var result2 = expression2.Evaluate(new Context()
{
FieldSource = fieldSource
});
if (result2.IsError)
{
return(StandardResult <SimpleTable> .ReturnError("ValidateTable() error: occurred during evaluating: " + expression2.Parser.Tokenizer.Expression, result2.String));
}
if (overwrite || string.IsNullOrWhiteSpace(row[column]))
{
row[column] = result2.String;
}
}
}
}
return(StandardResult <SimpleTable> .ReturnResult(resultsTable));
}
public static StandardResult <SimpleTable> Execute(SimpleTable sourceTable, string template, string sendFromMail, string sendToFieldExpression, string sendCCFieldExpression, string groupExpression = null)
{
var templates = new List <MessageTemplate>();
var lookup = (groupExpression != null) ? new Dictionary <string, MessageTemplate>() : null;
sendCCFieldExpression = string.IsNullOrWhiteSpace(sendCCFieldExpression) ? null : sendCCFieldExpression;
groupExpression = string.IsNullOrWhiteSpace(groupExpression) ? null : groupExpression;
foreach (var row in sourceTable)
{
var sendTo = EvaulateExpression(row, sendToFieldExpression);
if (sendTo.HasError)
{
return(StandardResult <SimpleTable> .ReturnError(sendTo.ErrorMessage)); // improve
}
var sendCC = EvaulateExpression(row, sendCCFieldExpression);
if (sendCC.HasError)
{
return(StandardResult <SimpleTable> .ReturnError(sendTo.ErrorMessage)); // improve
}
var groupName = EvaulateExpression(row, groupExpression);
if (groupName.HasError)
{
return(StandardResult <SimpleTable> .ReturnError(groupName.ErrorMessage)); // improve
}
if (lookup == null || groupName.Result == null || !lookup.TryGetValue(groupName.Result, out MessageTemplate message))
{
message = new MessageTemplate(template);
message.SetBodyFields(row);
message.To = sendTo.Result;
message.CC = sendCC.Result;
templates.Add(message);
if (lookup != null || groupName != null)
{
lookup.Add(groupName.Result, message);
}
}
message.SetRowFields(row);
}
var messageTable = new SimpleTable();
messageTable.AddColumnName("Mail-To");
messageTable.AddColumnName("Mail-From");
messageTable.AddColumnName("Mail-CC");
messageTable.AddColumnName("Mail-Subject");
messageTable.AddColumnName("Mail-Body");
foreach (var message in templates)
{
var row = messageTable.CreateRow();
row["Mail-Body"] = message.Body;
row["Mail-Subject"] = message.Subject;
row["Mail-To"] = message.To;
row["Mail-CC"] = message.CC;
row["Mail-From"] = sendFromMail;
}
return(StandardResult <SimpleTable> .ReturnResult(messageTable));
}
public static StandardResult <SimpleTable> Execute(SimpleTable table, string includeExpression)
{
if (string.IsNullOrWhiteSpace(includeExpression))
{
return(StandardResult <SimpleTable> .ReturnResult(table.Copy()));
}
var newTable = new SimpleTable();
foreach (var column in table.ColumnNames)
{
newTable.AddColumnName(column);
}
var fieldSource = new FieldDataSource();
var expression = ExpressionCache.Compile(includeExpression);
foreach (var sourceRow in table)
{
fieldSource.Row = sourceRow;
if (string.IsNullOrWhiteSpace(includeExpression))
{
continue;
}
var result = expression.Evaluate(new Context()
{
FieldSource = fieldSource
});
if (!expression.IsValid)
{
return(StandardResult <SimpleTable> .ReturnError("FilterTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, "invalid result: " + result.String));
}
if (!result.IsBoolean)
{
return(StandardResult <SimpleTable> .ReturnError("FilterTable() error: occurred during evaluating: " + expression.Parser.Tokenizer.Expression, "result was not boolean: " + result.String));
}
if (!result.Boolean)
{
continue;
}
var newRow = newTable.CreateRow();
for (int i = 0; i < sourceRow.ColumnCount; i++)
{
var value = sourceRow[i];
if (value == null)
{
continue;
}
newRow[i] = value;
}
}
return(StandardResult <SimpleTable> .ReturnResult(newTable));
}