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