private static XmlDocument createOderNode(StructuredDataStructure structuredDataStructure) { DataStructureManager dsm = new DataStructureManager(); XmlDocument doc = (XmlDocument)structuredDataStructure.Extra; XmlNode order; if (doc == null) { doc = new XmlDocument(); XmlNode root = doc.CreateNode(XmlNodeType.Element, "extra", null); doc.AppendChild(root); } if (doc.GetElementsByTagName("order").Count == 0) { if (structuredDataStructure.Variables.Count > 0) { order = doc.CreateNode(XmlNodeType.Element, "order", null); foreach (Variable v in structuredDataStructure.Variables) { XmlNode variable = doc.CreateNode(XmlNodeType.Element, "variable", null); variable.InnerText = v.Id.ToString(); order.AppendChild(variable); } doc.FirstChild.AppendChild(order); structuredDataStructure.Extra = doc; dsm.UpdateStructuredDataStructure(structuredDataStructure); } } return doc; }
/// <summary> /// Removes the relationship between the structured data structure and the view, neither the data structure nor the view. /// </summary> /// <param name="dataStructure">The data structure to be release from the relationship.</param> /// <param name="view">The view to be release from the relationship.</param> public void RemoveDataView(BExIS.Dlm.Entities.DataStructure.StructuredDataStructure dataStructure, DatasetView view) { Contract.Requires(dataStructure != null && dataStructure.Id >= 0); Contract.Requires(view != null && view.Id >= 0); Contract.Requires(view.Dataset == null); //Contract.Ensures(Contract.Result<StructuredDataStructure>() != null && Contract.Result<StructuredDataStructure>().Id >= 0); using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository <StructuredDataStructure> repo = uow.GetRepository <StructuredDataStructure>(); repo.Reload(dataStructure); repo.LoadIfNot(dataStructure.Views); int count = (from v in dataStructure.Views where v.Id.Equals(view.Id) select v ) .Count(); if (count <= 0) { throw new Exception(string.Format("There is no connection between data structure {0} and view {1}", dataStructure.Id, view.Id)); } dataStructure.Views.Remove(view); view.DataStructures.Remove(dataStructure); repo.Put(dataStructure); uow.Commit(); } //throw new NotImplementedException(); }
/// <summary> /// Adds a spanning view to the passed structured data structure. Spanning views are available and applicable to all datasets associated with the data structure. /// </summary> /// <param name="dataStructure">The structured data structure to add the data view to.</param> /// <param name="view">The data view to be linked to the data structure as a spanning view.</param> public void AddDataView(BExIS.Dlm.Entities.DataStructure.StructuredDataStructure dataStructure, DatasetView view) { // view should not be connected to a Dataset. if so throw an exception and the caller must remove the relationship to that dataset and then add to a data structure Contract.Requires(dataStructure != null && dataStructure.Id >= 0); Contract.Requires(view != null && view.Id >= 0); Contract.Requires(view.Dataset == null); //Contract.Ensures(Contract.Result<StructuredDataStructure>() != null && Contract.Result<StructuredDataStructure>().Id >= 0); using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository <StructuredDataStructure> repo = uow.GetRepository <StructuredDataStructure>(); repo.Reload(dataStructure); repo.LoadIfNot(dataStructure.Views); int count = (from v in dataStructure.Views where v.Id.Equals(view.Id) select v ) .Count(); if (count > 0) { throw new Exception(string.Format("There is a connection between data structure {0} and view {1}", dataStructure.Id, view.Id)); } dataStructure.Views.Add(view); view.DataStructures.Add(dataStructure); repo.Put(dataStructure); uow.Commit(); } }
public static List<Variable> getOrderedVariables(StructuredDataStructure structuredDataStructure) { XmlDocument doc = createOderNode(structuredDataStructure); XmlNode order; order = doc.GetElementsByTagName("order")[0]; List<Variable> orderedVariables = new List<Variable>(); if (structuredDataStructure.Variables.Count != 0) { foreach (XmlNode x in order) { foreach (Variable v in structuredDataStructure.Variables) { if (v.Id == Convert.ToInt64(x.InnerText)) orderedVariables.Add(v); } } } return orderedVariables; }
public static XmlDocument setVariableOrder(StructuredDataStructure structuredDataStructure, List<long> orderList) { DataStructureManager dsm = new DataStructureManager(); XmlDocument doc = createOderNode(structuredDataStructure); XmlNode order = doc.GetElementsByTagName("order")[0]; doc.FirstChild.RemoveChild(order); order = doc.CreateNode(XmlNodeType.Element, "order", null); foreach (long l in orderList) { XmlNode variable = doc.CreateNode(XmlNodeType.Element, "variable", null); variable.InnerText = l.ToString(); order.AppendChild(variable); } doc.FirstChild.AppendChild(order); structuredDataStructure.Extra = doc; dsm.UpdateStructuredDataStructure(structuredDataStructure); return doc; }
public FileResult downloadTemplate(long id) { if (id != 0) { DataStructureManager dataStructureManager = new DataStructureManager(); StructuredDataStructure dataStructure = new StructuredDataStructure(); dataStructure = dataStructureManager.StructuredDataStructureRepo.Get(id); if (dataStructure != null) { ExcelTemplateProvider provider = new ExcelTemplateProvider("BExISppTemplate_Clean.xlsm"); provider.CreateTemplate(dataStructure); string path = ""; XmlNode resources = dataStructure.TemplatePaths.FirstChild; XmlNodeList resource = resources.ChildNodes; foreach (XmlNode x in resource) { if (x.Attributes.GetNamedItem("Type").Value == "Excel") path = x.Attributes.GetNamedItem("Path").Value; } string rgxPattern = "[<>?\":|\\\\/*]"; string rgxReplace = "-"; Regex rgx = new Regex(rgxPattern); string filename = rgx.Replace(dataStructure.Name, rgxReplace); if (filename.Length > 50) filename = filename.Substring(0, 50); return File(Path.Combine(AppConfiguration.DataPath, path), "application/xlsm", "Template_" + dataStructure.Id + "_" + filename + ".xlsm"); } } return File(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", "BExISppTemplate_Clean.xlsm"), "application/xlsm", "Template_" + id + "_No_Data_Structure.xlsm"); }
public static DataTable ConvertStructuredDataStructureToDataTable(StructuredDataStructure sds) { DataTable dt = new DataTable(); dt.TableName = "DataStruture"; dt.Columns.Add("VariableName"); dt.Columns.Add("Optional"); dt.Columns.Add("VariableId"); dt.Columns.Add("ShortName"); //dt.Columns.Add("Parameters"); dt.Columns.Add("Description"); dt.Columns.Add("Unit"); dt.Columns.Add("DataType"); DataStructureManager dsm = new DataStructureManager(); StructuredDataStructure datastructure = dsm.StructuredDataStructureRepo.Get(sds.Id); if (datastructure != null) { List<Variable> variables = SortVariablesOnDatastructure(datastructure.Variables.ToList(), datastructure); foreach (Variable var in variables) { Variable sdvu = dsm.VariableRepo.Get(var.Id); DataRow dr = dt.NewRow(); if (sdvu.Label != null) dr["VariableName"] = sdvu.Label; else dr["VariableName"] = "n/a"; dr["Optional"] = sdvu.IsValueOptional.ToString(); if (sdvu.Label != null) dr["VariableId"] = sdvu.Id; else dr["VariableId"] = "n/a"; if (sdvu.DataAttribute.DataType != null) dr["ShortName"] = sdvu.DataAttribute.ShortName; else dr["ShortName"] = "n/a"; //if (sdvu.Parameters.Count > 0) dr["Parameters"] = "current not shown"; //else dr["Parameters"] = "n/a"; if (sdvu.Description != null || sdvu.Description != "") dr["Description"] = sdvu.Description; else dr["Description"] = "n/a"; if (sdvu.Unit != null) dr["Unit"] = sdvu.Unit.Name; else dr["Unit"] = "n/a"; if (sdvu.DataAttribute.DataType != null) dr["DataType"] = sdvu.DataAttribute.DataType.Name; else dr["DataType"] = "n/a"; dt.Rows.Add(dr); } } return dt; }
/// <summary> /// Read line by line based on a packageSize. /// Convert the lines into a datatuple based on the datastructure. /// Return value is a list of datatuples /// </summary> /// <remarks></remarks> /// <seealso cref="AsciiFileReaderInfo"/> /// <seealso cref="DataTuple"/> /// <seealso cref="StructuredDataStructure"/> /// <param name="FileStream">Stream of the FileStream</param> /// <param name="fileName">name of the FileStream</param> /// <param name="fri">AsciiFileReaderInfo needed</param> /// <param name="sds">StructuredDataStructure</param> /// <param name="datasetId">Id of the dataset</param> /// <param name="packageSize"></param> /// <returns>List of datatuples</returns> public List<DataTuple> ReadFile(Stream file, string fileName, AsciiFileReaderInfo fri, StructuredDataStructure sds, long datasetId, int packageSize) { // clear list of datatuples this.DataTuples = new List<DataTuple>(); this.VariableIdentifierRows = new List<List<string>>(); this.SubmitedVariableIdentifiers = new List<VariableIdentifier>(); this.FileStream = file; this.FileName = fileName; this.Info = fri; this.StructuredDataStructure = sds; this.DatasetId = datasetId; // Check params if (this.FileStream == null) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File not exist")); } if (!this.FileStream.CanRead) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File is not readable")); } if (this.Info.Variables <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Variable can´t be 0")); } if (this.Info.Data <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Data can´t be 0")); } if (this.ErrorMessages.Count == 0) { using (StreamReader streamReader = new StreamReader(file)) { string line; int index = 1; int items = 0; char seperator = AsciiFileReaderInfo.GetSeperator(fri.Seperator); //int end = packageSize; //int start = 1; // read to position if (Position == 1) { Position = this.Info.Data; } Stopwatch _timer = Stopwatch.StartNew(); /// <summary> /// go to current position is reached at the line /// </summary> /// <remarks></remarks> for (int i = 1; i < Position; i++) { string l = streamReader.ReadLine(); if (i == this.Info.Variables) { VariableIdentifierRows.Add(rowToList(l, seperator)); convertAndAddToSubmitedVariableIdentifier(); } } _timer.Stop(); Debug.WriteLine(" "); Debug.WriteLine("*****************************************************************"); Debug.WriteLine(" position : " + Position+" --> Timer: "+ _timer.Elapsed.TotalSeconds.ToString() ); _timer = Stopwatch.StartNew(); /// <summary> /// read each line as long as the packet size is not reached /// generating a datatuple from the line /// </summary> /// <remarks></remarks> while ((line = streamReader.ReadLine()) != null && items <= packageSize-1) { if (Position >= this.Info.Data) { // return List of VariablesValues, and error messages this.DataTuples.Add(ReadRow(rowToList(line, seperator), index)); } Position++; index++; items++; } _timer.Stop(); Debug.WriteLine(" created datatuples : " + _timer.Elapsed.TotalSeconds.ToString()); } } return this.DataTuples; }
private static List<DisplayFormatObject> getDisplayFormatObjects(StructuredDataStructure dataStructure) { List<DisplayFormatObject> tmp = new List<DisplayFormatObject>(); foreach (var variable in dataStructure.Variables) { string format = ""; string unit = ""; string column = variable.Label; DataType dt = variable.DataAttribute.DataType; // add display pattern to DisplayFormatObject; DataTypeDisplayPattern ddp = DataTypeDisplayPattern.Materialize(dt.Extra); if (ddp != null) { format = ddp.StringPattern; } // add unit abbr if exist do DisplayFormatObject // first variable, second dataattribute if (variable.Unit != null && !string.IsNullOrEmpty(variable.Unit.Abbreviation)) { unit = variable.Unit.Abbreviation; } else { if (variable.DataAttribute.Unit != null && !string.IsNullOrEmpty(variable.DataAttribute.Unit.Abbreviation) && !variable.DataAttribute.Unit.Name.Equals("None")) { unit = variable.DataAttribute.Unit.Abbreviation; } } if(!string.IsNullOrEmpty(column) && (!string.IsNullOrEmpty(format) || !string.IsNullOrEmpty(unit)) ) tmp.Add(new DisplayFormatObject(column,format,unit)); } return tmp; }
public List<Variable> getOrderedVariables(StructuredDataStructure structuredDataStructure) { return getOrderedVariables(structuredDataStructure.Id, structuredDataStructure.Variables.ToList()); }
/// <summary> /// Creates a structured data structure <seealso cref="StructuredDataStructure"/> and persists the entity in the database. /// </summary> /// <param name="name">The name of the data structure</param> /// <param name="description">A free text describing the purpose, usage, and/or the domain of the data structure usage.</param> /// <param name="xsdFileName">Not in use.</param> /// <param name="xslFileName">Not in use.</param> /// <param name="indexerType">If the data structure is used as a matrix, The indexer type show what kind of column would be represented by the indexer variable. <see cref="DataStructureCategory"/></param> /// <param name="indexer">The variable indicating the first indexing column of the matrix, if the data structure is representing a matrix.</param> /// <returns>The persisted structured data structure instance.</returns> public StructuredDataStructure CreateStructuredDataStructure(string name, string description, string xsdFileName, string xslFileName, DataStructureCategory indexerType, Variable indexer = null) { Contract.Requires(!string.IsNullOrWhiteSpace(name)); Contract.Requires(indexerType != DataStructureCategory.Generic ? (indexer != null) : true); Contract.Ensures(Contract.Result<StructuredDataStructure>() != null && Contract.Result<StructuredDataStructure>().Id >= 0); StructuredDataStructure e = new StructuredDataStructure() { Name = name, Description = description, XsdFileName = xsdFileName, XslFileName = xslFileName, IndexerType = indexerType, // Indexer = indexer, // how its possible to have the indexer before assigning variable to the structure }; using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository<StructuredDataStructure> repo = uow.GetRepository<StructuredDataStructure>(); repo.Put(e); uow.Commit(); } return (e); }
/// <summary> /// Applies changes to the data structure and persists them in the database. /// </summary> /// <param name="entity">The entity containing the changes.</param> /// <returns>The data structure entity with the changes applied.</returns> public StructuredDataStructure UpdateStructuredDataStructure(StructuredDataStructure entity) { Contract.Requires(entity != null, "provided entity can not be null"); Contract.Requires(entity.Id >= 0, "provided entity must have a permanent ID"); Contract.Ensures(Contract.Result<StructuredDataStructure>() != null && Contract.Result<StructuredDataStructure>().Id >= 0, "No entity is persisted!"); using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository<StructuredDataStructure> repo = uow.GetRepository<StructuredDataStructure>(); repo.Put(entity); // Merge is required here!!!! uow.Commit(); } return (entity); }
public DataStructure CreateDataStructure(string dataSetID, DataTable mapVariables, List<string> variableNames) { DataStructureManager dataStructureManager = new DataStructureManager(); DataContainerManager attributeManager = new DataContainerManager(); StructuredDataStructure dataStructure = new StructuredDataStructure(); UnitManager unitManager = new UnitManager(); // values of DataStructure ExcelTemplateProvider provider = new ExcelTemplateProvider(); string name = "oldBExIS" + dataSetID; string description = "old BExIS datastructure, created for " + dataSetID + ", possibly used by other"; //metadata.Title; string xsdFileName = "";//"xsdFileName"; string xslFileName = "";//"xslFileName"; DataStructureCategory indexerType = DataStructureCategory.Generic; // if dataStructure not exists StructuredDataStructure existDS = existingDataStructures(mapVariables, dataSetID); if (existDS.Name == null) { // create dataStructure dataStructure = dataStructureManager.CreateStructuredDataStructure(name, description, xsdFileName, xslFileName, indexerType, null); foreach (string varName in variableNames) { // values of variables string attName = ""; string convFactor = ""; string varDescription = ""; int Block = -999; long AttributeId = -999, UnitId = -999, VarUnitId = -999; foreach (DataRow mapRow in mapVariables.Select("DatasetId = '" + dataSetID + "'")) { if (mapRow["Name"].ToString() == varName) { attName = mapRow["Attribute"].ToString(); convFactor = mapRow["ConvFactor"].ToString(); varDescription = mapRow["Description"].ToString(); Block = int.Parse(mapRow["Block"].ToString()); if (attName.Length > 1) // if not mapped yet { AttributeId = Convert.ToInt64(mapRow["AttributeId"].ToString()); UnitId = Convert.ToInt64(mapRow["UnitId"].ToString()); if (mapRow["Unit"].ToString().Length > 0) VarUnitId = Convert.ToInt64(mapRow["VarUnitId"].ToString()); } } } if (AttributeId > 0 && Block == 0) // if not mapped yet AND variable is in block 0 { // find existing attribute for each variable DataAttribute attribute = attributeManager.DataAttributeRepo.Get(AttributeId); Unit varUnit = null; if (VarUnitId > 0) { varUnit = unitManager.Repo.Get(VarUnitId); } // add variables to dataStructure Variable variable = dataStructureManager.AddVariableUsage(dataStructure, attribute, true, varName, null, null, varDescription, varUnit); dataStructure.Variables.Add(variable); } } provider.CreateTemplate(dataStructure); return dataStructure; } else { return existDS; } }
/// <summary> /// Validate a Excel Template file /// </summary> /// <remarks>Only when excel template is in use</remarks> /// <seealso cref=""/> /// <param name="file">File as stream</param> /// <param name="fileName">Name of the file</param> /// <param name="sds">StructuredDataStructure of a dataset</param> /// <param name="datasetId">Datasetid of a dataset</param> public void ValidateFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId) { this.FileStream = file; this.FileName = fileName; this.StructuredDataStructure = sds; //this.Info = efri; this.DatasetId = datasetId; // open excel file spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // get all the defined area List<DefinedNameVal> namesTable = BuildDefinedNamesTable(workbookPart); // select data area this._areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault(); if(this._areaOfData == null) this.ErrorMessages.Add(new Error(ErrorType.Other,"Data area is not defined in the excel template.")); // Select variable area this._areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault(); if(this._areaOfVariables == null) this.ErrorMessages.Add(new Error(ErrorType.Other,"VariableIdentifiers area is not defined in the excel template.")); // Get intergers for reading data startColumn = GetColumnNumber(this._areaOfData.StartColumn); endColumn = GetColumnNumber(this._areaOfData.EndColumn); numOfColumns = (endColumn - startColumn) + 1; offset = GetColumnNumber(GetColumnName(this._areaOfData.StartColumn)) - 1; // select worksheetpart by selected defined name area like data in sheet // sheet where data area is inside WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData); //worksheet = worksheetPart.Worksheet; // get styleSheet _stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; // Get shared strings _sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); if (this.ErrorMessages.Count == 0) { if (ValidateDatastructure(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow)) { ValidateRows(worksheetPart, this._areaOfData.StartRow, this._areaOfData.EndRow); } } // close fehlt }
/// <summary> /// Read a Excel row by row /// Convert the rows into a datatuple based on the datastructure. /// Return a list of datatuples /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="file">File as stream</param> /// <param name="fileName">Name of the file</param> /// <param name="fri">FileReaderInfo (ExcelFileReaderInfo) for additional Informations to read the file</param> /// <param name="sds">StructuredDataStructure of a dataset</param> /// <param name="datasetId">Datasetid of a dataset</param> /// <returns>List of DataTuples</returns> public List<DataTuple> ReadFile(Stream file, string fileName, FileReaderInfo fri, StructuredDataStructure sds, long datasetId) { this.FileStream = file; this.FileName = fileName; this.StructuredDataStructure = sds; this.Info = fri; this.DatasetId = datasetId; // Check params if (this.FileStream == null) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File not exist")); } if (!this.FileStream.CanRead) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File is not readable")); } if (this.Info.Variables <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Variable can´t be 0")); } if (this.Info.Data <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Data can´t be 0")); } if (this.ErrorMessages.Count == 0) { // open excel file spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; SheetDimension dimension = workbookPart.WorksheetParts.First().Worksheet.GetFirstChild<SheetDimension>(); string s = dimension.Reference.Value; string[] references = s.Split(':'); // get all the defined area //List<DefinedNameVal> namesTable = BuildDefinedNamesTable(workbookPart); // Get intergers for reading data startColumn = GetColumnNumber(GetColumnName(references[0])); endColumn = GetColumnNumber(GetColumnName(references[1])); numOfColumns = (endColumn - startColumn) + 1; offset = this.Info.Offset; int endRowData = GetRowNumber(references[1]); // select worksheetpart by selected defined name area like data in sheet // sheet where data area is inside WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //GetWorkSheetPart(workbookPart, this._areaOfData); // get styleSheet _stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; // Get shared strings _sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); if (GetSubmitedVariableIdentifier(worksheetPart, this.Info.Variables, this.Info.Variables) != null) { ReadRows(worksheetPart, this.Info.Data, endRowData); } return this.DataTuples; } return this.DataTuples; }
/// <summary> /// /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="dsVersionTuples"></param> /// <param name="sds"></param> /// <returns></returns> private List<string> generateStringFromTuples(List<AbstractTuple> dsVersionTuples, StructuredDataStructure sds) { if (dsVersionTuples.Count > 0) { List<string> generatedStrings = new List<string>(); foreach (var tuple in dsVersionTuples) { foreach (var vv in tuple.VariableValues) { if (vv.VariableId >0) { Variable varr = sds.Variables.Where(p => p.Id == vv.VariableId).SingleOrDefault(); switch (varr.DataAttribute.DataType.SystemType) { case "String": { if (vv.Value != null) { generatedStrings.Add(vv.Value.ToString()); } break; } default: { break; } } } } } foreach (var variable in sds.Variables) { generatedStrings.Add(variable.DataAttribute.Name); generatedStrings.Add(variable.Label); if (!string.IsNullOrEmpty(variable.DataAttribute.Description)) generatedStrings.Add(variable.DataAttribute.Description); } return generatedStrings; } return null; }
/// <summary> /// This function convert a datatuple into datarow for a datatable to show on the client side /// the grid in the client side (in client mode) has unknow problem with value 0 and null /// So every empty cell get the max value of the specific Systemtype. /// On the client side this values replaced with "" /// </summary> /// <param name="dt"></param> /// <param name="t"></param> /// <returns></returns> private static DataRow ConvertTupleIntoDataRow(DataTable dt, AbstractTuple t, StructuredDataStructure sts) { DataRow dr = dt.NewRow(); foreach(var vv in t.VariableValues) { if (vv.VariableId > 0) { string valueAsString=""; if (vv.Value == null) { dr["ID" + vv.VariableId.ToString()] = DBNull.Value; } else { valueAsString = vv.Value.ToString(); Variable varr = sts.Variables.Where(p => p.Id == vv.VariableId).SingleOrDefault(); switch (varr.DataAttribute.DataType.SystemType) { case "String": { dr["ID" +vv.VariableId.ToString()] = valueAsString; break; } case "Double": { double value; if (double.TryParse(valueAsString, out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToDouble(valueAsString); else dr["ID" + vv.VariableId.ToString()] = -99999;//double.MaxValue; break; } case "Int16": { Int16 value; if(Int16.TryParse(valueAsString,out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToInt16(valueAsString); else dr["ID" + vv.VariableId.ToString()] = Int16.MaxValue; break; } case "Int32": { Int32 value; if(Int32.TryParse(valueAsString,out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToInt32(valueAsString); else dr["ID" + vv.VariableId.ToString()] = Int32.MaxValue; break; } case "Int64": { Int64 value; if(Int64.TryParse(valueAsString,out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToInt64(valueAsString); else dr["ID" + vv.VariableId.ToString()] = Int64.MaxValue; break; } case "Decimal": { decimal value; if (decimal.TryParse(valueAsString, out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToDecimal(valueAsString); else dr["ID" + vv.VariableId.ToString()] = -99999;//decimal.MaxValue; break; } case "Float": { decimal value; if (decimal.TryParse(valueAsString, out value)) dr["ID" + vv.VariableId.ToString()] = Convert.ToDecimal(valueAsString); else dr["ID" + vv.VariableId.ToString()] = -99999; break; } case "DateTime": { if (!String.IsNullOrEmpty(valueAsString)) dr["ID"+vv.VariableId.ToString()] = Convert.ToDateTime(valueAsString, CultureInfo.InvariantCulture); else dr["ID" + vv.VariableId.ToString()] = DateTime.MaxValue; break; } default: { if (!String.IsNullOrEmpty(vv.Value.ToString())) dr["ID"+vv.VariableId.ToString()] = valueAsString; else dr["ID" + vv.VariableId.ToString()] = DBNull.Value; break; } } } /*if (vv.ParameterValues.Count > 0) { foreach (var pu in vv.ParameterValues) { dr[pu.Parameter.Label.Replace(" ", "")] = pu.Value; } }*/ } } return dr; }
/// <summary> /// /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="file"></param> /// <param name="fileName"></param> /// <param name="sds"></param> /// <param name="datasetId"></param> /// <param name="variableList"></param> /// <param name="packageSize"></param> /// <returns></returns> public List<List<string>> ReadValuesFromFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId, List<long> variableList, int packageSize) { List<List<string>> listOfSelectedvalues = new List<List<string>>(); this.FileStream = file; this.FileName = fileName; this.StructuredDataStructure = sds; //this.Info = efri; this.DatasetId = datasetId; // open excel file spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // get all the defined area List<DefinedNameVal> namesTable = BuildDefinedNamesTable(workbookPart); // select data area this._areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault(); // Select variable area this._areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault(); // Get intergers for reading data startColumn = GetColumnNumber(this._areaOfData.StartColumn); endColumn = GetColumnNumber(this._areaOfData.EndColumn); numOfColumns = (endColumn - startColumn) + 1; offset = GetColumnNumber(GetColumnName(this._areaOfData.StartColumn)) - 1; // select worksheetpart by selected defined name area like data in sheet // sheet where data area is inside WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData); //worksheet = worksheetPart.Worksheet; // get styleSheet _stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; // Get shared strings _sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); if (Position == 1) { Position = this._areaOfData.StartRow; } if (GetSubmitedVariableIdentifier(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow) != null) { listOfSelectedvalues= GetValuesFromRows(worksheetPart,variableList, Position, Position + packageSize); Position += packageSize; } return listOfSelectedvalues; }
/// <summary> /// Get all values from the FileStream of each variable in variable list /// </summary> /// <remarks></remarks> /// <seealso cref="AsciiFileReaderInfo"/> /// <seealso cref="DataTuple"/> /// <seealso cref="StructuredDataStructure"/> /// <param name="FileStream">Stream of the FileStream</param> /// <param name="fileName">name of the FileStream</param> /// <param name="fri">AsciiFileReaderInfo needed</param> /// <param name="sds">StructuredDataStructure</param> /// <param name="datasetId">Id of the dataset</param> /// <param name="variableList">List of variables</param> /// <param name="packageSize">size of a package</param> /// <returns></returns> public List<List<string>> ReadValuesFromFile(Stream file, string fileName, AsciiFileReaderInfo fri, StructuredDataStructure sds, long datasetId, List<long> variableList, int packageSize) { this.FileStream = file; this.FileName = fileName; this.Info = fri; this.StructuredDataStructure = sds; this.DatasetId = datasetId; List<List<string>> listOfSelectedvalues = new List<List<string>>(); // Check params if (this.FileStream == null) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File not exist")); } if (!this.FileStream.CanRead) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File is not readable")); } if (this.Info.Variables <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Variable can´t be 0")); } if (this.Info.Data <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Data can´t be 0")); } if (this.ErrorMessages.Count == 0) { Stopwatch totalTime = Stopwatch.StartNew(); using (StreamReader streamReader = new StreamReader(file)) { string line; //int index = fri.Variables; int index = 1; int items = 0; char seperator = AsciiFileReaderInfo.GetSeperator(fri.Seperator); //int end = packageSize; //int start = 1; // read to position if (Position == 1) { Position = this.Info.Data; } Stopwatch _timer = Stopwatch.StartNew(); /// <summary> /// go to current position is reached at the line /// </summary> /// <remarks></remarks> for (int i = 1; i < Position; i++) { string l = streamReader.ReadLine(); if (i == this.Info.Variables) { VariableIdentifierRows.Add(rowToList(l, seperator)); convertAndAddToSubmitedVariableIdentifier(); } } // go to every line while ((line = streamReader.ReadLine()) != null && items <= packageSize - 1) { //// is position of datastructure? //if (index == this.info.Variables) //{ // variableIdentifierRows.Add(RowToList(line, seperator)); // ConvertAndAddToSubmitedVariableIdentifier(); //} // is position = or over startposition of data? if (Position >= this.Info.Data) { Stopwatch rowTime = Stopwatch.StartNew(); // return List of VariablesValues, and error messages listOfSelectedvalues.Add(GetValuesFromRow(rowToList(line, seperator), index, variableList)); rowTime.Stop(); //Debug.WriteLine("index : "+index+" ---- Total Time of primary key check " + rowTime.Elapsed.TotalSeconds.ToString()); } Position++; index++; items++; } _timer.Stop(); Debug.WriteLine(" get values for primary key check datatuples : " + _timer.Elapsed.TotalSeconds.ToString()); } totalTime.Stop(); Debug.WriteLine(" Total Time of primary key check " + totalTime.Elapsed.TotalSeconds.ToString()); } return listOfSelectedvalues; }
private StructuredDataStructure existingDataStructures(DataTable mapVariables, string dataSetID) { DataStructureManager dataStructureManager = new DataStructureManager(); StructuredDataStructure dataStructure = new StructuredDataStructure(); // get alldatasetId respective variables-rows from mapped-variables-table DataRow[] mappedRows = mapVariables.Select("DatasetId = '" + dataSetID + "' AND Block = 0"); // get all datastrcutures from repo which has the same number of variables List<StructuredDataStructure> existDatastructures = dataStructureManager.StructuredDataStructureRepo.Get(s => mappedRows.Count().Equals(s.Variables.Count)).ToList(); foreach (StructuredDataStructure existDs in existDatastructures) { bool isEqualStructure = true; //foreach (DataRow mapRow in mapVariables.Rows) foreach (DataRow mapRow in mappedRows) { int exvarno = existDs.Variables.Where(v => Convert.ToInt64(mapRow["UnitId"]).Equals(v.Unit.Id) && mapRow["Name"].ToString().Equals(v.Label) && Convert.ToInt64(mapRow["AttributeId"]).Equals(v.DataAttribute.Id) ).Count(); if (exvarno == 0) { isEqualStructure &= false; break; } } if (isEqualStructure) { dataStructure = existDs; break; } } return dataStructure; }
/// <summary> /// Validate the whole FileStream line by line until no more come. /// Convert the lines into a datatuple based on the datastructure. /// Return value is a list of datatuples /// </summary> /// <remarks>A list of errorMessages is filled when the fil is not valid </remarks> /// <seealso cref="AsciiFileReaderInfo"/> /// <seealso cref="DataTuple"/> /// <seealso cref="StructuredDataStructure"/> /// <param name="FileStream">Stream of the FileStream</param> /// <param name="fileName">name of the FileStream</param> /// <param name="fri">AsciiFileReaderInfo needed</param> /// <param name="sds">StructuredDataStructure</param> /// <param name="datasetId">Id of the dataset</param> public void ValidateFile(Stream file, string fileName, AsciiFileReaderInfo fri, StructuredDataStructure sds, long datasetId) { this.FileStream = file; this.FileName = fileName; this.Info = fri; this.StructuredDataStructure = sds; this.DatasetId = datasetId; // Check params if (this.FileStream == null) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File not exist")); } if (!this.FileStream.CanRead) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File is not readable")); } if (this.Info.Variables <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Variable can´t be 0")); } if (this.Info.Data <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Data can´t be 0")); } if (this.ErrorMessages.Count == 0) { using (StreamReader streamReader = new StreamReader(file)) { string line; int index = 1; char seperator = AsciiFileReaderInfo.GetSeperator(fri.Seperator); bool dsdIsOk = false; while ((line = streamReader.ReadLine()) != null) { if (index == this.Info.Variables) { dsdIsOk = ValidateDatastructure(line, seperator); } if (dsdIsOk && index >= this.Info.Data) { this.ErrorMessages = this.ErrorMessages.Union(ValidateRow(rowToList(line, seperator), index)).ToList(); } index++; } } } }
/// <summary> /// Creates a link between a <see cref="StructuredDataStructure"/> and <see cref="DataAttribute"/>. This link is known as <see cref="Variable"/>. /// In addition to what a variable inherits from the associated data attribute, it can have its own label, default and missing values, and optionality of its value. /// </summary> /// <param name="dataStructure">The structured data structure to be linked to the data attribute</param> /// <param name="dataAttribute">The data attribute to be used in a data structure as a variable</param> /// <param name="isValueOptional">Indicates whether the <see cref="VariableValue"/> associated to the variable is optional or not. This allows dataset to not provide data values for optional variables.</param> /// <param name="label">The display name of the variable. It may differ from the associated data attribute name. The variable label usually indicates the role of the data attribute in the structure. /// Its possible for a data structure to use a data attribute more than once by creating more than one variables, hence having different labels.</param> /// <param name="defaultValue">The default value of the associated variable values. Mainly considered for user interface purposes.</param> /// <param name="missingValue">A specific sentinel value that when is put into the variable values, means those values are missing and should not be considered data.</param> /// <param name="variableUnit">A specific unit for the variable. If not provided the unit of the <paramref name="dataAttibute"/> is used. /// If provided, its dimension must be equal to the dimension of the <paramref name="dataAttribute"/>'s unit.</param> /// <returns>A created and persisted variable object.</returns> public Variable AddVariableUsage(StructuredDataStructure dataStructure, DataAttribute dataAttribute, bool isValueOptional, string label, string defaultValue, string missingValue, string description, Unit variableUnit = null) { Contract.Requires(dataStructure != null && dataStructure.Id >= 0); Contract.Requires(dataAttribute != null && dataAttribute.Id >= 0); Contract.Requires((variableUnit == null && dataAttribute.Unit == null) || (variableUnit == null) || (variableUnit.Dimension == dataAttribute.Unit.Dimension)); Contract.Ensures(Contract.Result<Variable>() != null && Contract.Result<Variable>().Id >= 0); //StructuredDataStructureRepo.Reload(dataStructure); StructuredDataStructureRepo.LoadIfNot(dataStructure.Variables); int count = (from v in dataStructure.Variables where v.DataAttribute.Id.Equals(dataAttribute.Id) select v ) .Count(); //if (count > 0) // throw new Exception(string.Format("Data attribute {0} is already used as a variable in data structure {0}", dataAttribute.Id, dataStructure.Id)); Variable usage = new Variable() { DataStructure = dataStructure, DataAttribute = dataAttribute, MinCardinality = isValueOptional ? 0 : 1, // if there is no label provided, use the data attribute name and a sequence number calculated by the number of occurrences of that data attribute in the current structure Label = !string.IsNullOrWhiteSpace(label) ? label : (count <= 0 ? dataAttribute.Name : string.Format("{0} ({1})", dataAttribute.Name, count)), DefaultValue = defaultValue, MissingValue = missingValue, Description = description, Unit = (variableUnit != null ? variableUnit : dataAttribute.Unit), }; dataAttribute.UsagesAsVariable.Add(usage); dataStructure.Variables.Add(usage); using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository<Variable> repo = uow.GetRepository<Variable>(); repo.Put(usage); uow.Commit(); } return (usage); }
/// <summary> /// Read the whole FileStream line by line until no more come. /// Convert the lines into a datatuple based on the datastructure. /// Return value is a list of datatuples /// </summary> /// <remarks></remarks> /// <seealso cref="AsciiFileReaderInfo"/> /// <seealso cref="DataTuple"/> /// <seealso cref="StructuredDataStructure"/> /// <param name="FileStream">Stream of the FileStream</param> /// <param name="fileName">name of the FileStream</param> /// <param name="fri">AsciiFileReaderInfo needed</param> /// <param name="sds">StructuredDataStructure</param> /// <param name="datasetId">Id of the dataset</param> /// <returns>List of datatuples</returns> public List<DataTuple> ReadFile(Stream file, string fileName, AsciiFileReaderInfo fri, StructuredDataStructure sds, long datasetId) { this.FileStream = file; this.FileName = fileName; this.Info = fri; this.StructuredDataStructure = sds; this.DatasetId = datasetId; // Check params if (this.FileStream == null) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File not exist")); } if (!this.FileStream.CanRead) { this.ErrorMessages.Add(new Error(ErrorType.Other, "File is not readable")); } if (this.Info.Variables <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Variable can´t be 0")); } if (this.Info.Data <= 0) { this.ErrorMessages.Add(new Error(ErrorType.Other, "Startrow of Data can´t be 0")); } if (this.ErrorMessages.Count == 0) { using (StreamReader streamReader = new StreamReader(file)) { string line; int index = fri.Variables; char seperator = AsciiFileReaderInfo.GetSeperator(fri.Seperator); while ((line = streamReader.ReadLine()) != null) { if (index == this.Info.Variables) { VariableIdentifierRows.Add(rowToList(line, seperator)); convertAndAddToSubmitedVariableIdentifier(); } if (index >= this.Info.Data) { // return List of VariablesValues, and error messages this.DataTuples.Add(ReadRow(rowToList(line, seperator), index)); } index++; } } } return this.DataTuples; }
/// <summary> /// If the <paramref name="entity"/> is not associated to any <see cref="Dateset"/>, the method deletes it from the database. /// </summary> /// <param name="entity">The data structure object to be deleted.</param> /// <returns>True if the data structure is deleted, False otherwise.</returns> /// <remarks>Database exceptions are not handled intentionally, so that if the data structure is related to some datasets, a proper exception will be thrown.</remarks> public bool DeleteStructuredDataStructure(StructuredDataStructure entity) { Contract.Requires(entity != null); Contract.Requires(entity.Id >= 0); using (IUnitOfWork uow = this.GetUnitOfWork()) { IRepository<StructuredDataStructure> repo = uow.GetRepository<StructuredDataStructure>(); entity = repo.Reload(entity); repo.Delete(entity); uow.Commit(); } return (true); }
public List<Variable> getOrderedVariables(StructuredDataStructure structuredDataStructure) { DataStructureManager dsm = new DataStructureManager(); XmlDocument doc = (XmlDocument)structuredDataStructure.Extra; XmlNode order; if (doc == null) { doc = new XmlDocument(); XmlNode root = doc.CreateNode(XmlNodeType.Element, "extra", null); doc.AppendChild(root); } if (doc.GetElementsByTagName("order").Count == 0) { if (structuredDataStructure.Variables.Count > 0) { order = doc.CreateNode(XmlNodeType.Element, "order", null); foreach (Variable v in structuredDataStructure.Variables) { XmlNode variable = doc.CreateNode(XmlNodeType.Element, "variable", null); variable.InnerText = v.Id.ToString(); order.AppendChild(variable); } doc.FirstChild.AppendChild(order); structuredDataStructure.Extra = doc; dsm.UpdateStructuredDataStructure(structuredDataStructure); } } order = doc.GetElementsByTagName("order")[0]; List<Variable> orderedVariables = new List<Variable>(); if (structuredDataStructure.Variables.Count != 0) { foreach (XmlNode x in order) { foreach (Variable v in structuredDataStructure.Variables) { if (v.Id == Convert.ToInt64(x.InnerText)) orderedVariables.Add(v); } } } return orderedVariables; }
/// <summary> /// /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="id"></param> /// <returns></returns> protected StructuredDataStructure GetDataStructure(long id) { if (dataStructure == null) { DataStructureManager dataStructureManager = new DataStructureManager(); dataStructure = dataStructureManager.StructuredDataStructureRepo.Get(id); } return dataStructure; }
public List<VariableStruct> getOrderedVariableStructs(StructuredDataStructure structuredDataStructure) { List<VariableStruct> variableStructs = new List<VariableStruct>(); List<Variable> variables = getOrderedVariables(structuredDataStructure); DataContainerManager dataAttributeManager = new DataContainerManager(); VariableStruct temp = new VariableStruct(); List<BExIS.Dlm.Entities.DataStructure.Constraint> tempconstraints; UnitDimenstionModel unitDimenstionModel = new UnitDimenstionModel(); foreach (Variable v in variables) { unitDimenstionModel = new UnitDimenstionModel(); temp.variable = v; temp.unitStructs = unitDimenstionModel.getUnitListByDimenstionAndDataType(v.DataAttribute.Unit.Dimension.Id, v.DataAttribute.DataType.Id); tempconstraints = dataAttributeManager.DataAttributeRepo.Get(v.DataAttribute.Id).Constraints.ToList(); temp.rangeConstraints = new List<RangeConstraint>(); temp.domainConstraints = new List<DomainConstraint>(); temp.patternConstraints = new List<PatternConstraint>(); foreach(BExIS.Dlm.Entities.DataStructure.Constraint c in tempconstraints) { if (c is DomainConstraint) { DomainConstraint tempDomainConstraint = (DomainConstraint)c; tempDomainConstraint.Materialize(); temp.domainConstraints.Add(tempDomainConstraint); } if (c is PatternConstraint) temp.patternConstraints.Add((PatternConstraint)c); if (c is RangeConstraint) temp.rangeConstraints.Add((RangeConstraint)c); } variableStructs.Add(temp); } return variableStructs; }
/// <summary> /// Add Datatuples to a Excel Template file /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="dataTuples"> Datatuples to add</param> /// <param name="filePath">Path of the excel template file</param> /// <param name="dataStructureId">Id of datastructure</param> /// <returns>List of Errors or null</returns> public List<Error> AddDataTuplesToTemplate(DatasetManager datasetManager, List<long> dataTuplesIds, string filePath, long dataStructureId ) { if (File.Exists(filePath)) { //Stream file = Open(filePath); //_dataTuples = dataTuples; // loading datastructure dataStructure = GetDataStructure(dataStructureId); // open excel file spreadsheetDocument = SpreadsheetDocument.Open(filePath, true); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // get all the defined area List<DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart); // select data area this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault(); // Select variable area this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault(); // Get intergers for reading data startColumn = getColumnNumber(this.areaOfData.StartColumn); endColumn = getColumnNumber(this.areaOfData.EndColumn); numOfColumns = (endColumn - startColumn) + 1; offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1; // gerneat Style for cell types generateStyle(spreadsheetDocument); // get styleSheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; // Get shared strings sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); // select worksheetpart by selected defined name area like data in sheet // sheet where data area is inside WorksheetPart worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData); // Get VarioableIndentifiers this.VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow); AddRows(worksheetPart, this.areaOfData.StartRow, this.areaOfData.EndRow, dataTuplesIds, datasetManager); // set data area foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>()) { if (name.Name == "Data") { string[] tempArr = name.InnerText.Split('$'); string temp = ""; //$A$10:$C$15 tempArr[tempArr.Count() - 1] = numOfDataRows.ToString(); foreach (string t in tempArr) { if (t == tempArr.First()) { temp = temp + t; } else { temp = temp + "$" + t; } } name.Text = temp; } } spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } return ErrorMessages; }
public string CreateTemplate(StructuredDataStructure dataStructure) { DataStructureManager dataStructureManager = new DataStructureManager(); List<Variable> variables = getOrderedVariables(dataStructure); string rgxPattern = "[<>?\":|\\\\/*]"; string rgxReplace = "-"; Regex rgx = new Regex(rgxPattern); string filename = filename = dataStructure.Id + "_" + rgx.Replace(dataStructure.Name, rgxReplace) +".xlsm"; string path = Path.Combine("DataStructures", dataStructure.Id.ToString()); CreateTemplate(variables, path, filename); XmlDocument resources = new XmlDocument(); resources.LoadXml("<Resources><Resource Type=\"Excel\" Edition=\"2010\" Path=\"" + Path.Combine(path, filename) + "\"></Resource></Resources>"); dataStructure.TemplatePaths = resources; dataStructureManager.UpdateStructuredDataStructure(dataStructure); return Path.Combine(path, filename); }