/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="systemType"></param>
/// <param name="styleIndex"></param>
/// <returns></returns>
public static uint GetExcelStyleIndex(BExIS.Dlm.Entities.DataStructure.DataType dataType, List <StyleIndexStruct> styleIndex)
{
if (dataType.SystemType == DataTypeCode.Double.ToString() || dataType.SystemType == DataTypeCode.Decimal.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Decimal")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.Int16.ToString() || dataType.SystemType == DataTypeCode.Int32.ToString() || dataType.SystemType == DataTypeCode.Int64.ToString() || dataType.SystemType == DataTypeCode.UInt16.ToString() || dataType.SystemType == DataTypeCode.Int32.ToString() || dataType.SystemType == DataTypeCode.Int64.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Number")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.String.ToString() || dataType.SystemType == DataTypeCode.Char.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.DateTime.ToString())
{
if (dataType.Extra != null)
{
DataTypeDisplayPattern pattern = DataTypeDisplayPattern.Materialize(dataType.Extra);
if (pattern != null)
{
StyleIndexStruct tmp = styleIndex.Where(p => p.Name.Equals(pattern.Name)).FirstOrDefault();
return(tmp.Index);
}
}
return(styleIndex.Where(p => p.Name.Equals("DateTime")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.Boolean.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
protected string GetStringFormat(Dlm.Entities.DataStructure.DataType datatype)
{
DataTypeDisplayPattern ddp = DataTypeDisplayPattern.Materialize(datatype.Extra);
if (ddp != null)
{
return(ddp.StringPattern);
}
return("");
}
public JsonResult DisplayPattern(long id, string variable)
{
//id less then 1 is not possible so return empty string
if (id <= 0)
{
return(Json("", JsonRequestBehavior.AllowGet));
}
using (var datasetManager = new DatasetManager())
using (var dataStructureManager = new DataStructureManager())
using (var dataContainerManager = new DataContainerManager())
using (var dataTypeManager = new DataTypeManager())
{
var dataset = datasetManager.GetDataset(id);
if (dataset == null)
{
return(Json("", JsonRequestBehavior.AllowGet));
}
var datastructureId = dataset.DataStructure.Id;
var datastructure = dataStructureManager.StructuredDataStructureRepo.Get(datastructureId);
var v = datastructure.Variables.Where(var => var.Label.ToLower().Equals(variable.ToLower())).FirstOrDefault();
//if variable not exit return false
if (v == null)
{
return(Json("", JsonRequestBehavior.AllowGet));
}
if (v.DataAttribute != null)
{
var attr = dataContainerManager.DataAttributeRepo.Get(v.DataAttribute.Id);
if (attr.DataType.SystemType.Equals("DateTime"))
{
var dataType = dataTypeManager.Repo.Get(attr.DataType.Id);
if (dataType != null && dataType.Extra != null)
{
DataTypeDisplayPattern dp = DataTypeDisplayPattern.Materialize(dataType.Extra);
if (dp != null)
{
return(Json(dp.StringPattern, JsonRequestBehavior.AllowGet));
}
}
}
}
}
return(Json("", JsonRequestBehavior.AllowGet));
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="styleIndex"></param>
/// <param name="systemType"></param>
/// <returns></returns>
private uint getExcelStyleIndex(BExIS.Dlm.Entities.DataStructure.DataType dataType, uint[] styleIndex)
{
string systemType = dataType.SystemType;
if (systemType == "Double" || systemType == "Decimal")
{
return(styleIndex[0]);
}
if (systemType == "Int16" || systemType == "Int32" || systemType == "Int64" || systemType == "UInt16" || systemType == "UInt32" || systemType == "UInt64")
{
return(styleIndex[1]);
}
if (systemType == "Char" || systemType == "String")
{
return(styleIndex[2]);
}
if (systemType == "Boolean")
{
return(styleIndex[2]);
}
//for time and date only
if (dataType.Extra != null)
{
DataTypeDisplayPattern displayPattern = DataTypeDisplayPattern.Materialize(dataType.Extra);
//date
if (systemType == "DateTime" && displayPattern.Name.ToLower().Contains("date"))
{
return(styleIndex[4]);
}
//time
if (systemType == "DateTime" &&
(displayPattern.Name.ToLower().Equals("time") ||
displayPattern.Name.ToLower().Equals("time 12h")))
{
return(styleIndex[5]);
}
}
if (systemType == "DateTime")
{
return(styleIndex[3]);
}
return(styleIndex[2]);
}
public DataTypeModel(long Id)
{
DataTypeManager dtm = null;
try
{
dtm = new DataTypeManager();
dataType = dtm.Repo.Get(Id);
pattern = DataTypeDisplayPattern.Materialize(dataType.Extra);
}
finally
{
dtm.Dispose();
}
}
/// <summary>
/// Create ValueValidationManager of a Variable
/// </summary>
/// <remarks></remarks>
/// <param name="varName"></param>
/// <param name="dataType"></param>
/// <param name="optional"></param>
/// <param name="variable"></param>
/// <returns></returns>
private ValueValidationManager createValueValidationManager(string varName, string dataType, bool optional, DataAttribute variable)
{
string pattern = "";
if (variable != null && variable.DataType != null && variable.DataType.Extra != null)
{
DataTypeDisplayPattern displayPattern = DataTypeDisplayPattern.Materialize(variable.DataType.Extra);
if (displayPattern != null)
{
pattern = displayPattern.StringPattern;
}
}
ValueValidationManager vvm = new ValueValidationManager(varName, dataType, optional, Info.Decimal, pattern);
return(vvm);
}
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 AttributePreviewStruct fill(DataAttribute dataAttribute, bool getConstraints)
{
this.Id = dataAttribute.Id;
this.Name = dataAttribute.Name;
this.Description = dataAttribute.Description;
this.Unit.Id = dataAttribute.Unit.Id;
this.Unit.Name = dataAttribute.Unit.Name;
this.Unit.Description = dataAttribute.Unit.Abbreviation;
this.DataType = dataAttribute.DataType.Name;
this.Dimension = dataAttribute.Unit.Dimension.Name;
DataTypeDisplayPattern displayPattern = DataTypeDisplayPattern.Materialize(dataAttribute.DataType.Extra);
if (displayPattern != null)
{
this.DisplayPattern = displayPattern.StringPattern;
}
if (getConstraints)
{
if (dataAttribute.Constraints != null)
{
foreach (Constraint c in dataAttribute.Constraints)
{
c.Materialize();
this.Constraints.Add(c.Id, c.FormalDescription);
}
}
}
if (dataAttribute.UsagesAsVariable.Any())
{
this.inUse = true;
}
else
{
this.inUse = false;
}
return(this);
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="systemType"></param>
/// <param name="styleIndex"></param>
/// <returns></returns>
private uint getExcelStyleIndex(DataType dataType, List <StyleIndexStruct> styleIndex)
{
if (dataType.SystemType == DataTypeCode.Double.ToString() || dataType.SystemType == DataTypeCode.Decimal.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Decimal")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.Int16.ToString() || dataType.SystemType == DataTypeCode.Int32.ToString() || dataType.SystemType == DataTypeCode.Int64.ToString() || dataType.SystemType == DataTypeCode.UInt16.ToString() || dataType.SystemType == DataTypeCode.Int32.ToString() || dataType.SystemType == DataTypeCode.Int64.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Number")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.String.ToString() || dataType.SystemType == DataTypeCode.Char.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.DateTime.ToString())
{
if (dataType.Extra != null)
{
if (DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "DateTimeIso" && DataTypeDisplayPattern.Materialize(dataType.Extra).Systemtype == DataTypeCode.DateTime)
{
return(styleIndex.Where(p => p.Name.Equals("DateTime")).FirstOrDefault().Index);
}
if ((DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "DateIso" || DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "DateUs" || DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "DateUk" || DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "DateEu") && DataTypeDisplayPattern.Materialize(dataType.Extra).Systemtype == DataTypeCode.DateTime)
{
return(styleIndex.Where(p => p.Name.Equals("Date")).FirstOrDefault().Index);
}
if (DataTypeDisplayPattern.Materialize(dataType.Extra).Name == "Time" && DataTypeDisplayPattern.Materialize(dataType.Extra).Systemtype == DataTypeCode.DateTime)
{
return(styleIndex.Where(p => p.Name.Equals("Time")).FirstOrDefault().Index);
}
}
return(styleIndex.Where(p => p.Name.Equals("DateTime")).FirstOrDefault().Index);
}
if (dataType.SystemType == DataTypeCode.Boolean.ToString())
{
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
return(styleIndex.Where(p => p.Name.Equals("Text")).FirstOrDefault().Index);
}
/// <summary>
/// Create ValueValidationManager of a Variable
/// </summary>
/// <remarks></remarks>
/// <param name="varName"></param>
/// <param name="dataType"></param>
/// <param name="optional"></param>
/// <param name="variable"></param>
/// <returns></returns>
private ValueValidationManager createValueValidationManager(string varName, string dataType, bool optional, Variable variable)
{
string pattern = "";
DataAttribute dataAttribute = variable.DataAttribute;
if (string.IsNullOrEmpty(varName))
{
varName = variable.Label;
}
if (dataAttribute != null && dataAttribute.DataType != null && dataAttribute.DataType.Extra != null)
{
DataTypeDisplayPattern displayPattern = DataTypeDisplayPattern.Materialize(dataAttribute.DataType.Extra);
if (displayPattern != null)
{
pattern = displayPattern.StringPattern;
}
}
ValueValidationManager vvm = new ValueValidationManager(varName, dataType, optional, Info.Decimal, pattern, variable.MissingValues, CultureInfo.CurrentCulture, variable.DataAttribute.Constraints);
return(vvm);
}
/// <summary>
/// List of used datatypes and
/// the maxvalue of the datatypes
/// </summary>
/// <returns></returns>
private static List <string> CompareValues(StructuredDataStructure dataStructure)
{
List <string> cv = new List <string>();
if (dataStructure != null)
{
foreach (var variable in dataStructure.Variables)
{
StringBuilder sb = new StringBuilder();
foreach (var missingValue in variable.MissingValues)
{
if (DataTypeUtility.GetTypeCode(variable.DataAttribute.DataType.SystemType) == DataTypeCode.DateTime && DataTypeDisplayPattern.Materialize(variable.DataAttribute.DataType.Extra) != null)
{
DataTypeDisplayPattern ddp = DataTypeDisplayPattern.Materialize(variable.DataAttribute.DataType.Extra);
DateTime dateTime;
if (DateTime.TryParse(missingValue.Placeholder, new CultureInfo("en-US", false), DateTimeStyles.NoCurrentDateDefault, out dateTime))
{
sb.Append(missingValue.DisplayName + "|" + dateTime.ToString(ddp.StringPattern) + "#%#");;
}
}
else
{
sb.Append(missingValue.DisplayName + "|" + missingValue.Placeholder + "#%#");
}
}
//add also the case of the optional field
//replace the value with EMPTY String
sb.Append(" |" + getMaxvalueOfType(variable.DataAttribute.DataType.SystemType));
cv.Add(sb.ToString());
}
}
return(cv);
}
/// <summary>
/// Read Row and convert each value into a variableValue
/// and each row to a Datatuple
/// </summary>
/// <param name="row">List of values in one row</param>
/// <param name="indexOfRow">Currently row index</param>
/// <returns>DataTuple</returns>
public DataTuple ReadRow(List <string> row, int indexOfRow)
{
DataTuple dt = new DataTuple();
string value = "";
// convert row to List<VariableValue>
for (int i = 0; i < row.Count(); i++)
{
VariableIdentifier variableIdentifier = this.SubmitedVariableIdentifiers.ElementAt(i);
long variableId = 0;
if (variableIdentifier.id > 0)
{
variableId = this.SubmitedVariableIdentifiers.ElementAt(i).id;
}
else
{
variableId = getVariableUsage(variableIdentifier).Id;
}
// if variable from systemtype datatime
// maybee needs to convert into the default datetime culture format
if (this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("DateTime"))
{
Dlm.Entities.DataStructure.DataType dataType = this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType;
if (dataType != null && dataType.Extra != null)
{
DataTypeDisplayPattern dp = DataTypeDisplayPattern.Materialize(dataType.Extra);
if (dp != null && !string.IsNullOrEmpty(dp.StringPattern))
{
value = IOUtility.ConvertToDateUS(row[i], dp.StringPattern);
}
else
{
value = IOUtility.ConvertDateToCulture(row[i]);
}
}
else
{
value = IOUtility.ConvertDateToCulture(row[i]);
}
}
else
{
if (this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Double") ||
this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Decimal") ||
this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Float"))
{
value = row[i];
if (Info.Decimal.Equals(DecimalCharacter.comma))
{
if (value.Contains("."))
{
value = value.Replace(".", "");
}
if (value.Contains(","))
{
value = value.Replace(',', '.');
}
}
if (Info.Decimal.Equals(DecimalCharacter.point))
{
if (value.Contains(","))
{
value = value.Remove(',');
}
}
}
else
{
value = row[i];
}
}
dt.VariableValues.Add(DatasetManager.CreateVariableValue(value, "", DateTime.Now, DateTime.Now, new ObtainingMethod(), variableId, new List <ParameterValue>()));
}
return(dt);
}
/// <summary>
/// Convert a VariableValue to Cell
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="variableValue"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
protected Cell VariableValueToCell(VariableValue variableValue, int rowIndex, int columnIndex)
{
using (var uow = this.GetUnitOfWork())
{
DataAttribute dataAttribute = uow.GetReadOnlyRepository <Variable>().Query(p => p.Id == variableValue.VariableId).Select(p => p.DataAttribute).FirstOrDefault();
string message = "row :" + rowIndex + "column:" + columnIndex;
Debug.WriteLine(message);
string cellRef = getColumnIndex(columnIndex);
Cell cell = new Cell();
cell.CellReference = cellRef;
cell.StyleIndex = ExcelHelper.GetExcelStyleIndex(dataAttribute.DataType, styleIndex);
//cell.DataType = new EnumValue<CellValues>(getExcelType(dataAttribute.DataType.SystemType));
//cell.CellValue = new CellValue(variableValue.Value.ToString());
CellValues cellValueType = getExcelType(dataAttribute.DataType.SystemType);
object value = variableValue.Value;
if (value != null && !(value is DBNull) && cellValueType == CellValues.Number)
{
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
try
{
if (value.ToString() != "")
{
double d = Convert.ToDouble(value, System.Globalization.CultureInfo.InvariantCulture);
cell.CellValue = new CellValue(d.ToString(System.Globalization.CultureInfo.InvariantCulture));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\n|" + message);
}
return(cell);
}
else
{
if (value != null && !(value is DBNull) && cellValueType == CellValues.Date)
{
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
//CultureInfo provider = CultureInfo.InvariantCulture;
try
{
if (value.ToString() != "")
{
DateTime dt;
if (dataAttribute.DataType != null && dataAttribute.DataType.Extra != null)
{
DataTypeDisplayPattern pattern = DataTypeDisplayPattern.Materialize(dataAttribute.DataType.Extra);
if (!string.IsNullOrEmpty(pattern.StringPattern))
{
IOUtility.ExportDateTimeString(value.ToString(), pattern.StringPattern, out dt);
cell.CellValue = new CellValue(dt.ToOADate().ToString());
}
else
{
if (IOUtility.IsDate(value.ToString(), out dt))
{
cell.CellValue = new CellValue(dt.ToOADate().ToString());
}
}
}
else
{
if (IOUtility.IsDate(value.ToString(), out dt))
{
cell.CellValue = new CellValue(dt.ToOADate().ToString());
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + "|" + message);
}
}
else
{
cell.DataType = new EnumValue <CellValues>(CellValues.String);
if (value == null)
{
cell.CellValue = new CellValue("");
}
else
{
cell.CellValue = new CellValue(value.ToString());
}
}
}
return(cell);
}
}
public static MetadataAttributeModel CreateMetadataAttributeModel(BaseUsage current, BaseUsage parent, long metadataStructureId, int packageModelNumber, long parentStepId)
{
MetadataAttribute metadataAttribute;
List <object> domainConstraintList = new List <object>();
string constraintsDescription = "";
if (current is MetadataNestedAttributeUsage)
{
MetadataNestedAttributeUsage mnau = (MetadataNestedAttributeUsage)current;
metadataAttribute = mnau.Member;
}
else
{
MetadataAttributeUsage mau = (MetadataAttributeUsage)current;
metadataAttribute = mau.MetadataAttribute;
}
if (metadataAttribute.Constraints.Where(c => (c is DomainConstraint)).Count() > 0)
{
domainConstraintList = createDomainContraintList(metadataAttribute);
}
if (metadataAttribute.Constraints.Count > 0)
{
foreach (Constraint c in metadataAttribute.Constraints)
{
if (string.IsNullOrEmpty(constraintsDescription))
{
constraintsDescription = c.FormalDescription;
}
else
{
constraintsDescription = String.Format("{0}\n{1}", constraintsDescription, c.FormalDescription);
}
}
}
//load displayPattern
DataTypeDisplayPattern dtdp = DataTypeDisplayPattern.Materialize(metadataAttribute.DataType.Extra);
string displayPattern = "";
if (dtdp != null)
{
displayPattern = dtdp.StringPattern;
}
return(new MetadataAttributeModel
{
Id = current.Id,
Number = 1,
ParentModelNumber = packageModelNumber,
MetadataStructureId = metadataStructureId,
Parent = parent,
Source = current,
DisplayName = current.Label,
Discription = current.Description,
ConstraintDescription = constraintsDescription,
DataType = metadataAttribute.DataType.Name,
SystemType = metadataAttribute.DataType.SystemType,
DisplayPattern = displayPattern,
MinCardinality = current.MinCardinality,
MaxCardinality = current.MaxCardinality,
NumberOfSourceInPackage = 1,
first = true,
DomainList = domainConstraintList,
last = true,
MetadataAttributeId = metadataAttribute.Id,
ParentStepId = parentStepId,
Errors = null
});
}
public static Stylesheet UpdateStylesheet(Stylesheet styleSheet, out List <StyleIndexStruct> styleIndex)
{
//start add numberingFormats after 164
uint iExcelIndex = 164;
var numberFormats = new NumberingFormats();
styleIndex = new List <StyleIndexStruct>();
if (styleSheet.CellFormats == null)
{
styleSheet.CellFormats = new CellFormats();
styleSheet.CellFormats.Count = 0;
}
CellFormats cellFormats = styleSheet.Elements <CellFormats>().First();
//number 0,00
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Decimal", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//number 0
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)1U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
//styleIndex.Add(new StyleIndexStruct() { Name = "Number", Index = (uint)(uint)cellFormats.ChildElements.Count + 1, DisplayPattern = null });
styleIndex.Add(new StyleIndexStruct()
{
Name = "Number", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//text
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "Text", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//default Date
cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
styleIndex.Add(new StyleIndexStruct()
{
Name = "DateTime", Index = (uint)cellFormats.Count++, DisplayPattern = null
});
//add cellformats from displaypattern
foreach (var pattern in DataTypeDisplayPattern.Pattern)
{
//Excel special cases
//add numberFormats from displaypattern
var newNumberFortmat = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString(pattern.ExcelPattern)
};
numberFormats.Append(newNumberFortmat);
//if (pattern.Name.Equals("DateTime")) uInt32Value = 22U;
//if (pattern.Name.Equals("Date")) uInt32Value = 14U;
//if (pattern.Name.Equals("Time")) uInt32Value = 21U;
///UInt32Value uInt32Value = 0U;
cellFormat = new CellFormat()
{
NumberFormatId = newNumberFortmat.NumberFormatId, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
};
cellFormat.Protection = new Protection();
cellFormat.Protection.Locked = false;
cellFormats.Append(cellFormat);
DataTypeDisplayPattern tmp = new DataTypeDisplayPattern()
{
Name = pattern.Name,
Systemtype = pattern.Systemtype,
StringPattern = pattern.ExcelPattern,
RegexPattern = pattern.RegexPattern
};
styleIndex.Add(new StyleIndexStruct()
{
Name = pattern.Name, Index = (uint)cellFormats.Count++, DisplayPattern = tmp
});
}
styleSheet.NumberingFormats = numberFormats;
return(styleSheet);
}
public VariablePreviewStruct fill(Variable variable, bool getConstraints)
{
MissingValueManager missingValueManager = null;
try
{
missingValueManager = new MissingValueManager();
List <MissingValue> temp = missingValueManager.Repo.Query(mv => mv.Variable.Id.Equals(variable.Id)).ToList();
variable.Unit = variable.Unit ?? new Unit();
variable.Unit.Dimension = variable.Unit.Dimension ?? new Dimension();
variable.DataAttribute = variable.DataAttribute ?? new DataAttribute();
variable.DataAttribute.DataType = variable.DataAttribute.DataType ?? new DataType();
this.Id = variable.Id;
this.Name = variable.Label;
this.Description = variable.Description;
this.isOptional = variable.IsValueOptional;
this.Unit.Id = variable.Unit.Id;
this.Unit.Name = variable.Unit.Name;
this.Unit.Description = variable.Unit.Abbreviation;
this.convertibleUnits = getUnitListByDimenstionAndDataType(variable.Unit.Dimension.Id, variable.DataAttribute.DataType.Id);
this.DataType = variable.DataAttribute.DataType.Name;
DataTypeDisplayPattern displayPattern = DataTypeDisplayPattern.Materialize(variable.DataAttribute.DataType.Extra);
if (displayPattern != null)
{
this.DisplayPattern = displayPattern.StringPattern;
}
TypeCode typeCode = TypeCode.String;
foreach (TypeCode tc in Enum.GetValues(typeof(DataTypeCode)))
{
if (tc.ToString() == variable.DataAttribute.DataType.SystemType)
{
typeCode = tc;
break;
}
}
if (missingValueManager.getPlaceholder(typeCode, this.Id) != null && temp.Any())
{
foreach (MissingValue mv in temp)
{
this.MissingValues.Add(new MissingValueStruct()
{
Id = mv.Id,
DisplayName = mv.DisplayName,
Description = mv.Description,
Placeholder = mv.Placeholder
});
}
}
else if (missingValueManager.getPlaceholder(typeCode, this.Id) == null)
{
this.MissingValues = null;
}
if (getConstraints)
{
if (variable.DataAttribute.Constraints != null)
{
foreach (Constraint c in variable.DataAttribute.Constraints)
{
c.Materialize();
this.Constraints.Add(c.Id, c.FormalDescription);
}
}
}
this.Attribute = Attribute.fill(variable.DataAttribute, false);
return(this);
}
finally
{
missingValueManager.Dispose();
}
}
public static MetadataAttributeModel CreateMetadataAttributeModel(BaseUsage current, BaseUsage parent, long metadataStructureId, int packageModelNumber, long parentStepId)
{
MetadataAttribute metadataAttribute;
List <object> domainConstraintList = new List <object>();
string constraintsDescription = "";
double lowerBoundary = 0;
double upperBoundary = 0;
LinkElementType type = LinkElementType.MetadataNestedAttributeUsage;
bool locked = false;
bool entityMappingExist = false;
bool partyMappingExist = false;
string metadataAttributeName = "";
//simple
bool partySimpleMappingExist = false;
//complex
bool partyComplexMappingExist = false;
if (current is MetadataNestedAttributeUsage)
{
MetadataNestedAttributeUsage mnau = (MetadataNestedAttributeUsage)current;
metadataAttribute = mnau.Member;
type = LinkElementType.MetadataNestedAttributeUsage;
}
else
{
MetadataAttributeUsage mau = (MetadataAttributeUsage)current;
metadataAttribute = mau.MetadataAttribute;
type = LinkElementType.MetadataAttributeUsage;
}
if (metadataAttribute.Constraints.Where(c => (c is DomainConstraint)).Count() > 0)
{
domainConstraintList = createDomainContraintList(metadataAttribute);
}
if (metadataAttribute.Constraints.Count > 0)
{
foreach (Constraint c in metadataAttribute.Constraints)
{
if (string.IsNullOrEmpty(constraintsDescription))
{
constraintsDescription = c.FormalDescription;
}
else
{
constraintsDescription = String.Format("{0}\n{1}", constraintsDescription, c.FormalDescription);
}
}
if (metadataAttribute.DataType.Name == "string" && metadataAttribute.Constraints.Where(c => (c is RangeConstraint)).Count() > 0)
{
foreach (RangeConstraint r in metadataAttribute.Constraints.Where(c => (c is RangeConstraint)))
{
lowerBoundary = r.Lowerbound;
upperBoundary = r.Upperbound;
}
}
}
//set metadata attr name
metadataAttributeName = metadataAttribute.Name;
//load displayPattern
DataTypeDisplayPattern dtdp = DataTypeDisplayPattern.Materialize(metadataAttribute.DataType.Extra);
string displayPattern = "";
if (dtdp != null)
{
displayPattern = dtdp.StringPattern;
}
//ToDO/Check if dim is active
//check if its linked with a system field
//
locked = MappingUtils.ExistSystemFieldMappings(current.Id, type);
// check if a mapping for parties exits
partyMappingExist = MappingUtils.ExistMappingWithParty(current.Id, type);
// check if mapping to this metadata attribute is simple or complex.
// complex means, that the attribute is defined in the context of the parent
// e.g. name of User
// simple means, that the attribute is not defined in the context of the
// e.g. DataCreator Name in Contacts as list of contacts
partySimpleMappingExist = hasSimpleMapping(current.Id, type);
partyComplexMappingExist = hasComplexMapping(current.Id, type);
// check if a mapping for entites exits
entityMappingExist = MappingUtils.ExistMappingWithEntity(current.Id, type);
return(new MetadataAttributeModel
{
Id = current.Id,
Number = 1,
ParentModelNumber = packageModelNumber,
MetadataStructureId = metadataStructureId,
MetadataAttributeName = metadataAttributeName,
Parent = parent,
Source = current,
DisplayName = current.Label,
Discription = current.Description,
ConstraintDescription = constraintsDescription,
DataType = metadataAttribute.DataType.Name,
SystemType = metadataAttribute.DataType.SystemType,
DisplayPattern = displayPattern,
MinCardinality = current.MinCardinality,
MaxCardinality = current.MaxCardinality,
NumberOfSourceInPackage = 1,
first = true,
DomainList = domainConstraintList,
last = true,
MetadataAttributeId = metadataAttribute.Id,
ParentStepId = parentStepId,
Errors = null,
Locked = locked,
EntityMappingExist = entityMappingExist,
PartyMappingExist = partyMappingExist,
PartySimpleMappingExist = partySimpleMappingExist,
PartyComplexMappingExist = partyComplexMappingExist,
LowerBoundary = lowerBoundary,
UpperBoundary = upperBoundary,
});
}
public DataTypeModel()
{
dataType = new Dlm.Entities.DataStructure.DataType();
pattern = null;
}
/// <summary>
/// Read Row and convert each value into a variableValue
/// and each row to a Datatuple
/// </summary>
/// <param name="row">List of values in one row</param>
/// <param name="indexOfRow">Currently row index</param>
/// <returns>DataTuple</returns>
public DataTuple ReadRow(List <string> row, int indexOfRow)
{
if (row == null)
{
return(null);
}
if (row.Count == 1 && string.IsNullOrEmpty(row.ElementAt(0)))
{
return(null);
}
if (row.Count > this.StructuredDataStructure.Variables.Count || row.Count < this.StructuredDataStructure.Variables.Count)
{
throw new Exception("Number of values different then the number of values.");
}
DataTuple dt = new DataTuple();
string value = "";
// convert row to List<VariableValue>
for (int i = 0; i < row.Count(); i++)
{
VariableIdentifier variableIdentifier = this.SubmitedVariableIdentifiers.ElementAt(i);
long variableId = 0;
if (variableIdentifier.id > 0)
{
variableId = this.SubmitedVariableIdentifiers.ElementAt(i).id;
}
else
{
variableId = getVariableUsage(variableIdentifier).Id;
}
//if the value is a missing value get the placeholder
ValueValidationManager validationManager = ValueValidationManagerDic[variableId];
if (!validationManager.ValueIsMissingValueGetPlaceHolder(row[i], i, out value)) // jump over this code if its a missing value
{
// if variable from systemtype datatime
// maybee needs to convert into the default datetime culture format
if (this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("DateTime"))
{
Dlm.Entities.DataStructure.DataType dataType = this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType;
if (dataType != null && dataType.Extra != null)
{
DataTypeDisplayPattern dp = DataTypeDisplayPattern.Materialize(dataType.Extra);
if (dp != null && !string.IsNullOrEmpty(dp.StringPattern))
{
value = IOUtility.ConvertToDateUS(row[i], dp.StringPattern);
}
else
{
value = IOUtility.ConvertDateToCulture(row[i]);
}
}
else
{
value = IOUtility.ConvertDateToCulture(row[i]);
}
}
else
{
if (this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Double") ||
this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Decimal") ||
this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType.Equals("Float"))
{
var datatype = this.StructuredDataStructure.Variables.Where(p => p.Id.Equals(variableId)).FirstOrDefault().DataAttribute.DataType.SystemType;
value = row[i];
if (Info.Decimal.Equals(DecimalCharacter.comma))
{
if (value.Contains("."))
{
value = value.Replace(".", "");
}
if (value.Contains(","))
{
value = value.Replace(',', '.');
}
}
if (Info.Decimal.Equals(DecimalCharacter.point))
{
if (value.Contains(","))
{
value = value.Remove(',');
}
}
switch (datatype)
{
case "Double": {
double tmp = 0;
if (double.TryParse(value, NumberStyles.Any, new CultureInfo("en-US"), out tmp))
{
value = tmp.ToString("G16", new CultureInfo("en-US"));
}
break;
}
case "Decimal":
{
decimal tmp = 0;
if (decimal.TryParse(value, NumberStyles.Any, new CultureInfo("en-US"), out tmp))
{
value = "" + tmp.ToString("G29", new CultureInfo("en-US"));
}
break;
}
case "Float":
{
float tmp = 0;
if (float.TryParse(value, NumberStyles.Any, new CultureInfo("en-US"), out tmp))
{
value = "" + tmp.ToString("G7", new CultureInfo("en-US"));
}
break;
}
}
}
else
{
value = row[i].Trim();
}
}
}
dt.VariableValues.Add(DatasetManager.CreateVariableValue(value, "", DateTime.Now, DateTime.Now, new ObtainingMethod(), variableId, new List <ParameterValue>()));
}
return(dt);
}
public string GenerateJsonTable(SheetFormat sheetFormat, String worksheetUri)
{
// open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.fileStream, false))
{
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
WorksheetPart worksheetPart = null;
foreach (Sheet worksheet in workbookPart.Workbook.Descendants <Sheet>())
{
//Get the current worksheetpart and see if it is the correct one
WorksheetPart tmp = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);
if (tmp.Uri.ToString() == worksheetUri)
{
//Found the correct WorksheetPart
worksheetPart = tmp;
}
}
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
{
int expectedRowIndex = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row))
{
do
{
DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement();
List <String> rowAsStringList = new List <string>();
//Since this library will ignore empty rows, check if we skipped some and add empty rows if necessary
//This will still ignore empty rows at the end of the file but those wouldn't have any influence on the indices of data & header anyway
while (row.RowIndex > expectedRowIndex)
{
List <String> dummyRow = new List <string>();
dummyRow.Add("");
table.Add(dummyRow);
expectedRowIndex++;
}
// create a new cell
Cell c = new Cell();
int expectedIndex = 0; //To check whether we skipped cells because they were empty
for (int i = 0; i < row.ChildElements.Count(); i++)
{
// get current cell at i
c = row.Elements <Cell>().ElementAt(i);
string value = "";
if (c != null)
{
//See if cells have been skipped (empty cells are not contained in the xml and therefore not contained in row.ChildElements)
//See: https://stackoverflow.com/a/3981249
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference));
if (expectedIndex < cellColumnIndex)
{
//We skipped one or more cells so add some blank data
do
{
rowAsStringList.Add(""); //Insert blank data
expectedIndex++;
}while (expectedIndex < cellColumnIndex);
}
//We now have the correct index and can grab the value of the cell
if (c.CellValue != null && !string.IsNullOrEmpty(c.CellValue.Text))
{
// if Value a text
if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString)
{
int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture);
SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex];
value = sharedStringItem.InnerText;
}
//If cell contains boolean (doesn't always work for files saved with libre office)
else if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.Boolean)
{
if (c.InnerText == "1")
{
value = "true";
}
else
{
value = "false";
}
}
// not a text
else if (c.StyleIndex != null && c.StyleIndex.HasValue)
{
uint styleIndex = c.StyleIndex.Value;
CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat;
if (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
{
uint numberFormatId = cellFormat.NumberFormatId.Value;
NumberingFormat numberFormat = _stylesheet.NumberingFormats?.FirstOrDefault(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat;
//
if (numberFormat != null)
{
if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue)
{
string formatCode = numberFormat.FormatCode.Value;
if ((formatCode.ToLower().Contains("d") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("y")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("d")) ||
(formatCode.ToLower().Contains("h") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("s"))
)
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
//value = dateTime.ToString(new CultureInfo("en-us"));
//get c# display pattern
DataTypeDisplayPattern dataTypeDisplayPattern = DataTypeDisplayPattern.GetByExcelPattern(formatCode);
value = dataTypeDisplayPattern != null?dateTime.ToString(dataTypeDisplayPattern.StringPattern) : dateTime.ToString(new CultureInfo("en-us"));
//Debug.WriteLine("----");
//Debug.WriteLine(formatCode);
}
}
} // numberformat not null end
} // (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
//It may happen that values are in a cell, but the associated information such as numberformat or style are missing.
// In this case, we decide to display the values, even if they are incorrect.
if (string.IsNullOrEmpty(value) && (!string.IsNullOrEmpty(c?.CellValue?.Text)))
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
rowAsStringList.Add(value);
}//end if cell value null
else
{
rowAsStringList.Add("");
}
}//end if cell null
expectedIndex++;
}//for children of row
//Check if there's a new max length for the length of a row
maxCellCount = Math.Max(maxCellCount, rowAsStringList.Count);
//Just read a row, so increase the expected index for the next one
expectedRowIndex++;
table.Add(rowAsStringList);
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
//Make sure each row has the same number of values in it
foreach (List <String> row in table)
{
while (row.Count < maxCellCount)
{
row.Add("");
}
}
//Convert the Lists to Arrays
List <String>[] rowArray = table.ToArray(); //The elements of the Array are the rows in form of String-lists
String[][] tableArray = new String[rowArray.Length][];
for (int i = 0; i < rowArray.Length; i++)
{
tableArray[i] = rowArray[i].ToArray();
}
return(JsonConvert.SerializeObject(tableArray));
}
}
// create read data types in bpp
public void CreateDataTypes(ref DataTable mappedDataTypes)
{
DataTypeManager dataTypeManager = null;
try
{
dataTypeManager = new DataTypeManager();
foreach (DataRow mappedDataType in mappedDataTypes.Rows)
{
string dtName = mappedDataType["Name"].ToString();
string dtDescription = mappedDataType["Description"].ToString();
DataTypeDisplayPattern dtDisplayPettern = new DataTypeDisplayPattern();
TypeCode dtSystemType = new TypeCode();
foreach (TypeCode type in Enum.GetValues(typeof(TypeCode)))
{
if (type.ToString().Equals(mappedDataType["SystemType"].ToString()))
{
dtSystemType = type;
}
}
if (dtSystemType == TypeCode.DateTime)
{
if (mappedDataType["DisplayPattern"] != null && mappedDataType["DisplayPattern"].ToString() != "")
{
dtDisplayPettern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals(mappedDataType["DisplayPattern"].ToString())).FirstOrDefault();
}
else
{
dtDisplayPettern = DataTypeDisplayPattern.Pattern.Where(p => p.Name.Equals("DateTimeIso")).FirstOrDefault();
}
}
DataType dataType = new DataType();
// get existing dataTypes
DataType existDT = dataTypeManager.Repo.Get().Where(d =>
d.Name.Equals(dtName) &&
d.SystemType.ToString().Equals(mappedDataType["SystemType"].ToString())
).FirstOrDefault();
// return ID of existing dataType or create dataType
if (existDT == null && dtSystemType != null)
{
dataType = dataTypeManager.Create(dtName, dtDescription, dtSystemType);
XmlDocument xmlDoc = new XmlDocument();
XmlNode xmlNode;
xmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "Extra", null);
xmlDoc.AppendChild(xmlNode);
xmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "DisplayPattern", null);
xmlNode.InnerXml = DataTypeDisplayPattern.Dematerialize(dtDisplayPettern).InnerXml;
xmlDoc.DocumentElement.AppendChild(xmlNode);
dataType.Extra = xmlDoc;
dataTypeManager.Update(dataType);
}
else
{
dataType = existDT;
}
mappedDataType["DataTypesId"] = dataType.Id;
}
}
finally
{
dataTypeManager.Dispose();
}
}
