private void Insert(JToken newValue)
{
var item = new DBItem(db, table);
SetDBItemValues(ref item, table.Columns, newValue);
table.Add(item);
}
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;
string tableName = (string)vars["TableName"];
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
var inputData = (List <DBItem>)vars["InputData"];
string keyMappingString = (string)vars["KeyMapping"];
var keyMappingDictionary = new Dictionary <string, string>();
foreach (var segment in keyMappingString.Split(','))
{
var pair = segment.Split(':');
if (pair.Length == 2)
{
keyMappingDictionary.Add(pair[0], pair[1]);
}
}
DBTable table = db.Table(tableName, searchInShared);
DateTime timestamp = DateTime.Now;
foreach (var inputRow in inputData)
{
var newRowItem = new DBItem(db, table);
foreach (DBColumn column in table.Columns)
{
if (keyMappingDictionary.ContainsKey(column.Name))
{
string dictionaryValue = keyMappingDictionary[column.Name];
if (dictionaryValue.Substring(0, 2) == "$$")
{
newRowItem[column.Name] = inputRow[dictionaryValue.Substring(2)];
}
else
{
newRowItem[column.Name] = KeyValueString.ParseValue(dictionaryValue, vars);
}
}
}
if (table.Columns.Any(c => c.Name == "id_user_insert"))
{
int userId = core.User.Id;
newRowItem["id_user_insert"] = userId;
newRowItem["id_user_change"] = userId;
newRowItem["datetime_insert"] = timestamp;
newRowItem["datetime_change"] = timestamp;
}
table.Add(newRowItem);
}
db.SaveChanges();
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string tableName = vars.ContainsKey("TableName")
? (string)vars["TableName"]
:(string)vars["__TableName__"];
DBTable table = db.Table(tableName, searchInShared);
if (table == null)
{
throw new Exception($"Queried table not found! (Table: {tableName}, Action: {Name} ({Id}))");
}
if (!vars.ContainsKey("JArray"))
{
throw new Exception("JArray parameter not passed!");
}
JArray jarray = (JArray)vars["JArray"];
foreach (JObject jo in jarray)
{
Dictionary <string, object> parsedColumns = new Dictionary <string, object>();
TapestryUtils.ParseJObjectRecursively(jo, parsedColumns);
DBItem parsedRow = new DBItem(db, null);
foreach (var parsedCol in parsedColumns)
{
parsedRow[parsedCol.Key] = parsedCol.Value;
}
DBItem item = new DBItem(db, table);
foreach (DBColumn col in table.Columns)
{
if (col.Name == DBCommandSet.PrimaryKey)
{
continue;
}
string parsedColName = (col.Name == "ext_id") ? DBCommandSet.PrimaryKey : col.Name;
item[col.Name] = parsedRow[parsedColName];
}
table.Add(item);
}
db.SaveChanges();
outputVars["Result"] = "Successful";
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
JArray jarray = (JArray)vars["JArray"];
if (jarray.HasValues)
{
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string tableName = vars.ContainsKey("TableName")
? (string)vars["TableName"]
: (string)vars["__TableName__"];
DBTable table = db.Table(tableName, searchInShared);
if (table == null)
{
throw new Exception($"Queried table not found (Tabulka: {tableName}, Akce: {Name} ({Id}))");
}
var listDbItem = table.Select().ToList();
//check if table has column (IsDeleted),if no , the result is null
var columnIsDeletedExist = table.Columns.SingleOrDefault(c => c.Name == "IsDeleted");
string uniqueCol;
string uniqueExtCol; //basicly foreign key
if (vars.ContainsKey("UniqueCol"))
{
uniqueCol = (string)vars["UniqueCol"];
uniqueExtCol = uniqueCol;
}
else
{
uniqueCol = "ext_id";
uniqueExtCol = DBCommandSet.PrimaryKey;
}
if (!table.Columns.Any(c => c.Name == uniqueCol))
{
throw new Exception($"Table column named '{uniqueCol}' not found!");
}
foreach (JObject jo in jarray)
{
if (columnIsDeletedExist != null)
{
//if theres column IsDeleted, check if the entity is in rising, if not, set isDeleted to true.
for (int i = 0; i < listDbItem.Count; i++)
{
if (!jarray.Any(j => j["id"].ToString() == listDbItem[i]["ext_id"].ToString()))
{
DBItem foundItem = listDbItem[i];
foundItem["IsDeleted"] = true;
table.Update(foundItem, (int)foundItem["id"]);
}
}
}
Dictionary <string, object> parsedColumns = new Dictionary <string, object>();
TapestryUtils.ParseJObjectRecursively(jo, parsedColumns);
DBItem parsedRow = new DBItem(db, table, parsedColumns);
DBItem updatedRow = table.Select().Where(c => c.Column(uniqueCol).Equal(parsedRow[uniqueExtCol])).FirstOrDefault();
if (updatedRow != null) //update
{
foreach (var col in parsedRow.getColumnNames())
{
if (updatedRow.getColumnNames().Contains(col) && col != DBCommandSet.PrimaryKey && col != uniqueCol)
{
updatedRow[col] = parsedRow[col];
}
}
table.Update(updatedRow, (int)updatedRow[DBCommandSet.PrimaryKey]);
}
else // insert row if it does not exist
{
DBItem item = new DBItem(db, table);
foreach (DBColumn col in table.Columns)
{
if (col.Name == DBCommandSet.PrimaryKey)
{
continue;
}
string parsedColName = (col.Name == "ext_id") ? DBCommandSet.PrimaryKey : col.Name;
item[col.Name] = parsedRow[parsedColName];
}
table.Add(item);
}
}
db.SaveChanges();
outputVars["Result"] = true;
}
else
{
Watchtower.OmniusLog.Log($"{Name}: Input JArray has no values! Action aborted", Watchtower.OmniusLogLevel.Warning);
outputVars["Result"] = false;
}
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string tableName = vars.ContainsKey("TableName")
? (string)vars["TableName"]
: (string)vars["__TableName__"];
DBTable table = db.Table(tableName);
if (table == null)
{
throw new Exception($"Požadovaná tabulka nebyla nalezena (Tabulka: {tableName}, Akce: {Name} ({Id}))");
}
bool addParentRelation = false;
string parentRelationColumn = "";
int parentId = -1;
if (vars.ContainsKey("ParentProperty") && vars.ContainsKey("ParentId") &&
table.Columns.Any(c => c.Name == (string)vars["ParentProperty"]))
{
addParentRelation = true;
parentRelationColumn = (string)vars["ParentProperty"];
parentId = (int)vars["ParentId"];
}
DBItem item = new DBItem(db, table);
foreach (DBColumn column in table.Columns)
{
if (column.Type == DbType.Boolean)
{
item[column.Name] = vars.ContainsKey($"__Model.{table.Name}.{column.Name}");
}
else if (vars.ContainsKey($"__Model.{table.Name}.{column.Name}"))
{
if (column.Type == DbType.DateTime)
{
try
{
item[column.Name] = DateTime.ParseExact((string)vars[$"__Model.{table.Name}.{column.Name}"], "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);
}
catch (FormatException)
{
// skip empty date instead of crashing
}
}
else
{
item[column.Name] = vars[$"__Model.{table.Name}.{column.Name}"];
}
}
}
if (addParentRelation)
{
item[parentRelationColumn] = parentId;
}
table.Add(item);
for (int panelIndex = 1; vars.ContainsKey($"panelCopy{panelIndex}Marker"); panelIndex++)
{
item = new DBItem(db, table);
foreach (DBColumn column in table.Columns)
{
if (column.Type == DbType.Boolean)
{
item[column.Name] = vars.ContainsKey($"__Model.panelCopy{panelIndex}.{table.Name}.{column.Name}");
}
else if (vars.ContainsKey($"__Model.panelCopy{panelIndex}.{table.Name}.{column.Name}"))
{
if (column.Type == DbType.DateTime)
{
try
{
item[column.Name] = DateTime.ParseExact((string)vars[$"__Model.{table.Name}.{column.Name}"], "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);
}
catch (FormatException)
{
// skip empty date instead of crashing
}
}
else
{
item[column.Name] = vars[$"__Model.panelCopy{panelIndex}.{table.Name}.{column.Name}"];
}
}
}
if (addParentRelation)
{
item[parentRelationColumn] = parentId;
}
table.Add(item);
}
db.SaveChanges();
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
// init
COREobject core = COREobject.i;
DBConnection db = core.Entitron;
bool isServerFile = vars.ContainsKey("isServerFile") ? (bool)vars["isServerFile"] : false;
int countAdded = 0;
//jméno form inputu, nebo cesta k souboru
string csvSource = (string)vars["CsvSource"];
string tableName = (string)vars["TableName"];
string delimiter = vars.ContainsKey("Delimiter") ? (string)vars["Delimiter"] : ";";
string dateFormat = vars.ContainsKey("DateTimeFormat") ? (string)vars["DateTimeFormat"] : "yyyy-MM-dd";
bool enclosed = vars.ContainsKey("b$HasFieldsInQuotes") ? (bool)vars["b$HasFieldsInQuotes"] : false;
List <string> uniqueColumns = vars.ContainsKey("UniqueColumns") ? ((string)vars["UniqueColumns"]).Split(',').ToList() : new List <string>();
CultureInfo czechCulture = new CultureInfo("cs-CZ");
var columnMetadataList = db.Application.ColumnMetadata.Where(c => c.TableName == tableName).ToList();
DBTable table = db.Table(tableName, false);
Dictionary <int, DBColumn> columnsMap = new Dictionary <int, DBColumn>();
dynamic files;
if (!isServerFile)
{
files = HttpContext.Current.Request.Files;
if (files == null)
{
return;
}
}
else
{
files = new string[] { csvSource };
}
List <string> messages = new List <string>();
bool isHeader = true;
foreach (string fileName in files)
{
HttpPostedFile file = null;
if (!isServerFile)
{
file = HttpContext.Current.Request.Files[fileName];
if (file.ContentLength == 0 || fileName != csvSource)
{
continue;
}
}
Encoding cp1250 = Encoding.GetEncoding(1250);
using (StreamReader sr = new StreamReader((isServerFile) ? File.OpenRead(csvSource) : file.InputStream, cp1250))
{
sr.Peek();
Encoding enc = sr.CurrentEncoding;
using (CsvReader reader = new CsvReader(sr))
{
reader.Configuration.Delimiter = delimiter;
reader.Configuration.TrimFields = true;
reader.Configuration.TrimHeaders = true;
reader.Configuration.Encoding = cp1250;
if (reader.ReadHeader())
{
string[] fields = reader.FieldHeaders;
int i = 0;
foreach (string field in fields)
{
var colMetadata = columnMetadataList.SingleOrDefault(c => c.ColumnDisplayName == field);
string colName = colMetadata == null ? field : colMetadata.ColumnName;
if (table.Columns.Where(c => c.Name == colName).Count() > 0)
{
columnsMap.Add(i, table.Columns.Where(c => c.Name == colName).First());
}
i++;
}
}
else
{
messages.Add("Nepodařilo se načíst názvy sloupců. Nelze pokračovat.");
isHeader = false;
}
if (isHeader)
{
while (reader.Read())
{
long line = reader.Row;
string[] fields = reader.CurrentRecord;
int i = 0;
Dictionary <string, object> data = new Dictionary <string, object>();
bool isValid = true;
foreach (string value in fields)
{
// Neznámé sloupce ignorujeme
if (!columnsMap.ContainsKey(i))
{
i++;
continue;
}
// Prázdné hodnoty vynecháme
if (string.IsNullOrEmpty(value))
{
i++;
continue;
}
DBColumn col = columnsMap[i];
switch (col.Type)
{
case DbType.String:
data.Add(col.Name, value);
break;
case DbType.Boolean:
bool parsedBool;
if (bool.TryParse(value, out parsedBool))
{
data.Add(col.Name, parsedBool);
}
else
{
if (value == "0")
{
data.Add(col.Name, false);
}
else if (value == "1")
{
data.Add(col.Name, true);
}
else
{
isValid = false;
messages.Add(string.Format(typeError, line, col.Name, "logická hodnota"));
}
}
break;
case DbType.Int32:
int parsedInt;
if (int.TryParse(value, NumberStyles.Any, CultureInfo.InvariantCulture, out parsedInt))
{
data.Add(col.Name, parsedInt);
}
else
{
isValid = false;
messages.Add(string.Format(typeError, line, col.Name, "celé číslo"));
}
break;
case DbType.Double:
double parsedDouble;
if (double.TryParse(value, NumberStyles.Any, CultureInfo.InvariantCulture, out parsedDouble))
{
data.Add(col.Name, parsedDouble);
}
else
{
isValid = false;
messages.Add(string.Format(typeError, line, col.Name, "celé nebo desetinní číslo"));
}
break;
case DbType.DateTime:
try
{
DateTime parsedDateTime = DateTime.Parse(value, czechCulture);
data.Add(col.Name, parsedDateTime);
}
catch (FormatException)
{
isValid = false;
messages.Add(string.Format(typeError, line, col.Name, "platné datum"));
}
break;
}
i++;
}
if (!isValid)
{
continue;
}
if (uniqueColumns.Count > 0)
{
try
{
var select = table.Select();
// setConditions
foreach (string colName in uniqueColumns)
{
string colName2 = columnMetadataList.SingleOrDefault(c => c.ColumnDisplayName == colName).ColumnName;
object condValue = data[colName2].ToString();
if (colName == "IČO")
{
condValue = data[colName2].ToString().PadLeft(8, '0');
}
DBColumn column = table.Columns.Single(c => c.Name == colName2);
select.Where(c => c.Column(colName2).Equal(condValue));
}
// check if row already exists
if (select.ToList().Count > 0)
{
isValid = false;
messages.Add(string.Format(uniqueError, line));
}
}
catch (KeyNotFoundException)
{
isValid = false;
messages.Add(string.Format(uniqueColumnMissingError, line));
}
}
if (isValid)
{
data.Add("Datum_vlozeni", DateTime.Now);
data.Add("Cas_editace", DateTime.Now);
data.Add("Editoval", core.User.Id);
DBItem item = new DBItem(db, table);
foreach (KeyValuePair <string, object> kv in data)
{
item[kv.Key] = kv.Value;
}
table.Add(item);
countAdded++;
}
}
}
}
}
}
db.SaveChanges();
outputVars["Success"] = messages.Count() == 0;
outputVars["Message"] = string.Join("<br>", messages);
outputVars["CountAdded"] = countAdded;
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message)
{
// init
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
if (!vars.ContainsKey("TableName"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: TableName is required");
}
if (!vars.ContainsKey("BaseName"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: BaseName is required");
}
if (!vars.ContainsKey("Data"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: Data is required");
}
JToken data = (JToken)vars["Data"];
string tableName = (string)vars["TableName"];
string baseName = (string)vars["BaseName"];
string itemName = vars.ContainsKey("ItemName") ? (string)vars["ItemName"] : "item";
/****************************************************************************************
** MOCKUP DATA **
*****************************************************************************************
* string jsonText;
* try {
* XmlDocument xml = new XmlDocument();
* xml.Load("c:/users/mnvk8/Downloads/response.xml");
* jsonText = JsonConvert.SerializeXmlNode(xml);
* }
* catch (Exception e) {
* if (e is ArgumentNullException || e is XmlException) {
* jsonText = "";// JsonConvert.SerializeObject(response);
* }
* else {
* throw e;
* }
* }
* JToken data = JToken.Parse(jsonText);
****************************************************************************************/
DBTable table = db.Table(tableName, searchInShared);
Dictionary <string, DBColumn> columnExists = new Dictionary <string, DBColumn>();
Dictionary <string, DbType> columnType = new Dictionary <string, DbType>();
var items = data.SelectToken($"$..{baseName}.{itemName}");
foreach (JToken item in items)
{
DBItem entity = new DBItem(db, table);
foreach (JProperty pair in item)
{
// Zjistíme, jestli ten slupec v tabulce vůbec existuje
string columnName = pair.Name.ToLowerInvariant();
if (!columnExists.ContainsKey(columnName))
{
DBColumn column = table.Columns.Where(c => c.Name.ToLowerInvariant() == columnName).FirstOrDefault();
columnExists.Add(columnName, column);
if (column != null)
{
columnType.Add(columnName, column.Type);
}
}
if (columnExists[columnName] != null)
{
var columnInfo = columnExists[columnName];
entity[columnInfo.Name] = DataType.ConvertTo(columnType[columnName], pair);
}
}
table.Add(entity);
}
db.SaveChanges();
// return
outputVars["Result"] = true;
}
public void AddBuildingLocation(LatLong loc) //Adds LatLong to database cache, then builds it in the gameworld
{
database.Add(database.GetUniqueKey(), SerializableData.SerializeLatLon(loc));
AddBuildingObject(loc);
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
JArray jarray = (JArray)vars["JArray"];
if (jarray.HasValues)
{
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string tableName = vars.ContainsKey("TableName")
? (string)vars["TableName"]
: (string)vars["__TableName__"];
DBTable table = db.Table(tableName, searchInShared);
if (table == null)
{
throw new Exception($"Queried table not found! (Table: {tableName}, Action: {Name} ({Id}))");
}
string uniqueCol;
if (vars.ContainsKey("UniqueCol"))
{
uniqueCol = (string)vars["UniqueCol"];
}
else
{
uniqueCol = "ext_id";
}
if (!table.Columns.Any(c => c.Name == uniqueCol))
{
throw new Exception($"Table column named '{uniqueCol}' not found!");
}
List <DBItem> currentDBItems = table.Select(uniqueCol).ToList();
List <DBItem> newItemsFromJArray = new List <DBItem>();
//iterate jarray and insert data to Omnius table,if theres unique column defined and a record with duplicate data,ignore it
foreach (JObject j in jarray)
{
var duplicatedItem = currentDBItems.SingleOrDefault(c => c[uniqueCol].ToString() == j["id"].ToString());
if (duplicatedItem != null)
{
continue;
}
DBItem item = new DBItem(db);
//get all properties of jObject entity
foreach (var prop in j.Properties())
{
if (prop.Name == "id")
{
item[uniqueCol] = ((JValue)j.GetValue(prop.Name)).Value;
}
else
{
item[prop.Name] = ((JValue)j.GetValue(prop.Name)).Value;
}
}
newItemsFromJArray.Add(item);
}
foreach (var item in newItemsFromJArray)
{
table.Add(item);
}
db.SaveChanges();
outputVars["Result"] = true;
}
else
{
Watchtower.OmniusLog.Log($"{Name}: Input JArray has no values! Action aborted", Watchtower.OmniusLogLevel.Warning);
outputVars["Result"] = false;
}
}
