public DataDetail DownloadDocument(DM.File fileDetails)
{
Check.IsNotNull<DM.File>(fileDetails, "filetoDownload");
Check.IsNotNull<ICollection<FileMetadataField>>(fileDetails.FileMetadataFields, "fileMetadata");
DataDetail dataDetail = new DataDetail();
dataDetail.FileDetail = fileDetails;
// Update the metadata to stream, create one more metasheet tab in the file
dataDetail = this.UpdateMetaDataSheetForExcel(fileDetails);
dataDetail.MimeTypeToDownLoad = fileDetails.MimeType;
dataDetail.FileNameToDownLoad = fileDetails.Name;
return dataDetail;
}
/// <summary>
/// Method to get quality check validation conditions for excel object
/// </summary>
/// <param name="stream">Stream</param>
/// <param name="sheetIds">Sheet Ids</param>
/// <param name="qualityCheck">Quality Check</param>
/// <param name="qualityCheckTypes">Quality Check type</param>
/// <returns>List of QualityCheckResult</returns>
private IEnumerable<QualityCheckResult> GetQualityCheckRulesForExcel(Stream stream, string sheetIds, DM.QualityCheck qualityCheck, IEnumerable<QualityCheckColumnType> qualityCheckTypes)
{
List<QualityCheckResult> qualityCheckResults = new List<QualityCheckResult>();
using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(stream, false))
{
foreach (var sheetId in GetSheetIds(sheetIds))
{
var currentSheet = excelDocument.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Id == sheetId);
IEnumerable<SheetCell> headersAndColumns = new List<SheetCell>();
if (excelDocument.WorkbookPart != null)
{
headersAndColumns = ExcelFileHelper.GetHeadersAndColumns(excelDocument, sheetId);
}
var headers = headersAndColumns.Where(c => c.RowIndex == 1).ToList();
var columns = headersAndColumns.Where(c => c.RowIndex != 1).ToList();
QualityCheckResult qualityCheckResult = new QualityCheckResult()
{
SheetId = currentSheet.Id,
SheetName = currentSheet.Name
};
qualityCheckResults.Add(qualityCheckResult);
GetHeaderIssues(qualityCheck, headers, qualityCheckResult);
GetColumnIssues(qualityCheck, qualityCheckTypes, headers, columns, qualityCheckResult);
}
}
return qualityCheckResults;
}
/// <summary>
/// Method to get the errors available on the input excel file.
/// </summary>
/// <param name="file">File</param>
/// <returns>List of sheet with errors.</returns>
public async Task<IList<FileSheet>> GetErrors(DM.File file)
{
var fileStream = GetFileStream(file.BlobId);
using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(fileStream, true))
{
return await ExcelFileHelper.ValidateGraphics(excelDocument);
}
}
public DataDetail UpdateMetaDataSheetForExcel(DM.File fileDetails)
{
Check.IsNotNull(fileDetails, "fileDetails");
DataDetail dataDetail = new DataDetail();
dataDetail.FileDetail = fileDetails;
Stream stream = null;
try
{
stream = GetFileStream(fileDetails.BlobId);
// do not processor further as the file is not associates with repository.
if (fileDetails.Status == FileStatus.Uploaded.ToString() || fileDetails.RepositoryId == null || fileDetails.RepositoryId <= 0)
{
dataDetail.DataStream = stream.GetBytes();
return dataDetail;
}
//FOR CSV, AT THE TIME OF PUBLISH ,ONE MORE CSV SHEET WILL BE CRATED AND SENT AS ZIP
using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(stream, true))
{
Sheet metadataSheet = null;
var repositoryMetadata = base.RepositoryService.GetRepositoryById((int)fileDetails.RepositoryId).RepositoryMetadata.FirstOrDefault();
ICollection<RepositoryMetadataField> repositoryMetadataFields = null;
if (repositoryMetadata != null)
{
repositoryMetadataFields = repositoryMetadata.RepositoryMetadataFields;
}
if ((repositoryMetadata != null && repositoryMetadataFields != null) || (fileDetails.FileColumns != null && fileDetails.FileColumns.Any()))
{
//create the sheet in any case if filecolumns exists from db or it was already existing in the file
metadataSheet = excelDocument.InsertMetadataWorksheet(fileDetails.FileMetadataFields.Any(), fileDetails.FileColumns.Any());
}
if (metadataSheet != null)
{
var fileColumnUnits = base.FileDataRepository.RetrieveFileColumnUnits();
var fileColumnTypes = base.FileDataRepository.RetrieveFileColumnTypes();
char column = 'A';
int fileMetaDataIndexer = 0;
if (repositoryMetadataFields != null && repositoryMetadataFields.Any() && fileDetails.FileMetadataFields != null && fileDetails.FileMetadataFields.Any())
{
foreach (var repositoryMetaData in repositoryMetadataFields)
{
// first write the repository metadata
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "A{0}", fileMetaDataIndexer + 2), repositoryMetaData.Name);
// write teh metadata value if it exists
var fileMetaDataField = fileDetails.FileMetadataFields.Where(fm => fm.RepositoryMetadataFieldId == repositoryMetaData.RepositoryMetadataFieldId).FirstOrDefault();
if (fileMetaDataField != null)
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "B{0}", fileMetaDataIndexer + 2), fileMetaDataField.MetadataValue);
}
fileMetaDataIndexer = fileMetaDataIndexer + 1;
}
column = 'D';
}
int cellCount = 2;
if (fileDetails.FileColumns != null && fileDetails.FileColumns.Any())
{
char entityName = column, entityDescription = (char)(column + 1), name = (char)(column + 2), description = (char)(column + 3), fileType = (char)(column + 4), fileUnit = (char)(column + 5);
foreach (var val in fileDetails.FileColumns)
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", entityName, cellCount), val.EntityName);
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", entityDescription, cellCount), val.EntityDescription);
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", name, cellCount), val.Name);
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", description, cellCount), val.Description);
if (val.FileColumnTypeId != null && val.FileColumnUnitId != 0 && fileColumnTypes != null)
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", fileType, cellCount), fileColumnTypes.Where(fc => fc.FileColumnTypeId == val.FileColumnTypeId).FirstOrDefault().Name);
}
else
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", fileType, cellCount), string.Empty);
}
if (val.FileColumnUnitId != null && val.FileColumnUnitId != 0 && fileColumnUnits != null)
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", fileUnit, cellCount), fileColumnUnits.Where(fu => fu.FileColumnUnitId == val.FileColumnUnitId).FirstOrDefault().Name);
}
else
{
excelDocument.SetCellValue(metadataSheet, string.Format(CultureInfo.InvariantCulture, "{0}{1}", fileUnit, cellCount), string.Empty);
}
cellCount++;
}
}
WorksheetPart worksheetPart = excelDocument.WorkbookPart.GetPartById(metadataSheet.Id) as WorksheetPart;
if (worksheetPart != null)
{
TableDefinitionPart metadataTable = null;
TableDefinitionPart parameterTable = null;
// get the tables in Excel
var tableParts = worksheetPart.GetPartsOfType<TableDefinitionPart>();
if (tableParts != null)
{
foreach (var item in tableParts)
{
if (item.Table.Name == Constants.MetadataRangeName)
{
metadataTable = item;
if (fileDetails.FileMetadataFields != null && fileDetails.FileMetadataFields.Any())
{
metadataTable.Table.Reference = "A1:B" + (fileDetails.FileMetadataFields.Count + 1);
}
metadataTable.Table.Save();
}
if (item.Table.Name == Constants.ParaMetadataRangeName && fileDetails.FileColumns.Any())
{
parameterTable = item;
if (fileDetails.FileColumns != null && fileDetails.FileColumns.Any() && fileDetails.FileMetadataFields.Any())
{
parameterTable.Table.Reference = string.Format(CultureInfo.InvariantCulture, "{0}1:{1}{2}", column, (char)(column + 5), fileDetails.FileColumns.Count + 1);
}
parameterTable.Table.Save();
}
}
}
}
excelDocument.WorkbookPart.Workbook.Save();
}
}
dataDetail.DataStream = stream.GetBytes();
}
finally
{
if (stream != null)
{
stream.Dispose();
}
}
return dataDetail;
}
public async Task<IEnumerable<QualityCheckResult>> GetQualityCheckIssues(DM.File fileDetail, DM.QualityCheck qualityCheck, IEnumerable<QualityCheckColumnType> qualityCheckTypes, string sheetIds)
{
IEnumerable<QualityCheckResult> qualityCheckResults = new List<QualityCheckResult>();
await Task.Factory.StartNew(() =>
{
// Get the stream
var byteArray = base.GetFileContentsAsByteArray(fileDetail.BlobId);
using (var documentStream = new MemoryStream(byteArray))
{
//If it is EXCEL file ,convert the data to excel stream
if (Path.GetExtension(fileDetail.Name) == Constants.XLFileExtension)
{
// Check and set the quality check condistions
qualityCheckResults = GetQualityCheckRulesForExcel(documentStream, sheetIds, qualityCheck, qualityCheckTypes);
}
}
});
return qualityCheckResults;
}
private void WriteParamMetadata(MerritQueryData request, XmlDocument emlDocument, DM.File file, IEnumerable<DM.FileColumnType> fileColumnTypes = null, IEnumerable<DM.FileColumnUnit> fileColumnUnits = null)
{
XmlNamespaceManager emlNamespaceMgr = new XmlNamespaceManager(emlDocument.NameTable);
emlNamespaceMgr.AddNamespace("eml", MerritConstants.EmlNamespace);
XmlElement emlRoot = emlDocument.DocumentElement;
XmlNode dataTableRef = emlRoot.SelectSingleNode(MerritConstants.SheetTableXPath, emlNamespaceMgr);
XmlNode parentDataTable = dataTableRef.ParentNode;
StringBuilder attibuteListBuilder;
XElement scaleNode;
var fileColumnType = string.Empty;
var fileColumnUnit = string.Empty;
List<string> sheetIds = new List<string>();
//foreach (var param in request.ParameterMetadata)
foreach (var param in file.FileColumns)
{
var sheetTable = dataTableRef.Clone();
attibuteListBuilder = new StringBuilder();
if (!sheetIds.Contains(param.EntityName))
{
sheetIds.Add(param.EntityName);
foreach (var attributeParam in file.FileColumns.Where(fc => fc.EntityName == param.EntityName).ToList())
{
fileColumnType = string.Empty;
fileColumnUnit = string.Empty;
if (attributeParam.FileColumnTypeId > 0 && fileColumnTypes != null)
{
scaleNode = new XElement("measurementScale");
fileColumnType = fileColumnTypes.Where(fc => fc.FileColumnTypeId == attributeParam.FileColumnTypeId).FirstOrDefault().Name;
if (attributeParam.FileColumnUnitId > 0 && fileColumnUnits != null)
{
fileColumnUnit = fileColumnUnits.Where(fc => fc.FileColumnUnitId == attributeParam.FileColumnUnitId).FirstOrDefault().Name;
}
if (!string.IsNullOrWhiteSpace(fileColumnType))
{
switch (fileColumnType.ToUpperInvariant())
{
case "TEXT":
scaleNode = new XElement("measurementScale", new XElement("nominal", new XElement("nonNumericDomain", new XElement("textDomain", new XElement("definition", System.Security.SecurityElement.Escape(attributeParam.Description))))));
break;
case "DATETIME":
scaleNode = new XElement("measurementScale", new XElement("dateTime", new XElement("formatString", "The user has designated this column as some form of date and/or time. No standard is specified due to DataUp coding constraints.")));
break;
case "NUMERIC":
XElement unitsElement = new XElement("unit", new XElement("standardUnit", System.Security.SecurityElement.Escape(fileColumnUnit)));
if (!string.IsNullOrWhiteSpace(fileColumnUnit))
{
unitsElement = new XElement("unit", new XElement("customUnit", System.Security.SecurityElement.Escape(fileColumnUnit)));
}
scaleNode = new XElement("measurementScale", new XElement("interval", unitsElement, new XElement("numericDomain", new XElement("numberType", "real"))));
break;
}
}
var attibuteNode = new XElement("attribute", new XElement("attributeName", System.Security.SecurityElement.Escape(attributeParam.Name)), new XElement("attributeDefinition", System.Security.SecurityElement.Escape(attributeParam.Description)), scaleNode);
attibuteListBuilder.AppendLine(attibuteNode.ToString().Trim());
}
}
sheetTable.SelectSingleNode("entityName").InnerText = string.Format(CultureInfo.InvariantCulture, "{0} - {1}", System.Security.SecurityElement.Escape(currentFileName), System.Security.SecurityElement.Escape(param.EntityName));
sheetTable.SelectSingleNode("entityDescription").InnerText = System.Security.SecurityElement.Escape(param.EntityDescription);
sheetTable.SelectSingleNode("attributeList").InnerXml = attibuteListBuilder.ToString();
parentDataTable.InsertBefore(sheetTable, dataTableRef);
}
}
parentDataTable.RemoveChild(dataTableRef);
}
public async Task<IEnumerable<ColumnLevelMetadata>> GetColumnLevelMetadataFromFile(DM.File fileDetail)
{
Check.IsNotNull(fileDetail, "fileDetail");
IFileProcesser fileProcesser = FileFactory.GetFileTypeInstance(Path.GetExtension(fileDetail.Name), this.BlobDataRepository, this.FileDataRepository, this.RepositoryService);
return await fileProcesser.GetColumnMetadataFromFile(fileDetail);
}
private void CreateFile(MerritConstants.FileCreationType fileCreationType, string filePath, MerritQueryData request, Encoding encoding, DM.File file, DM.Repository repository, IEnumerable<DM.FileColumnType> fileColumnTypes = null, IEnumerable<DM.FileColumnUnit> fileColumnUnits = null)
{
StringBuilder arguments = new StringBuilder();
switch (fileCreationType)
{
case MerritConstants.FileCreationType.ManiFest:
arguments.AppendLine("#%dataonem_0.1");
arguments.AppendLine("#%profile | http://uc3.cdlib.org/registry/ingest/manifest/mrt-dataone- manifest");
arguments.AppendLine("#%prefix | dom: | http://uc3.cdlib.org/ontology/dataonem#");
arguments.AppendLine("#%prefix | mrt: | http://uc3.cdlib.org/ontology/mom#");
arguments.AppendLine("#%fields | dom:scienceMetadataFile | dom:scienceMetadataFormat | dom:scienceDataFile | mrt:mimeType");
ArrayList files = ZipUtilities.GenerateFileList(Path.GetDirectoryName(filePath));
foreach (string fileName in files)
{
var tempName = System.IO.Path.GetFileName(fileName);
arguments.AppendLine(MerritConstants.MrtErcFile + " | ERC | " + tempName + " | text/plain");
arguments.AppendLine(MerritConstants.MrtEmlFile + " | eml://ecoinformatics.org/eml-2.1.1 | " + tempName + " | text/xml");
}
arguments.AppendLine("#%eof");
break;
case MerritConstants.FileCreationType.ERC:
string who = string.Format(CultureInfo.InvariantCulture, "{0}: {1}", request["who"].Key, request["who"].Value);
string what = string.Format(CultureInfo.InvariantCulture, "{0}: {1}", request["what"].Key, request["what"].Value);
string when = string.Format(CultureInfo.InvariantCulture, "{0}: {1}", request["when"].Key, request["when"].Value);
string where = string.Format(CultureInfo.InvariantCulture, "{0}: {1}", request["where"].Key, request["where"].Value);
arguments.AppendLine("erc:");
arguments.AppendLine(who);
arguments.AppendLine(what);
arguments.AppendLine(when);
arguments.AppendLine(where);
break;
case MerritConstants.FileCreationType.EML:
WriteEML(request, filePath, file, repository, fileColumnTypes, fileColumnUnits);
break;
}
using (FileStream fileStream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
if (encoding != null)
{
fileStream.Write(encoding.GetBytes(arguments.ToString()), 0, encoding.GetByteCount(arguments.ToString()));
}
}
}
public DataFile GetDataFile(DM.File file)
{
Check.IsNotNull(file, "file");
IFileProcesser fileProcesser = FileFactory.GetFileTypeInstance(Path.GetExtension(file.Name), this.BlobDataRepository, this.FileDataRepository, this.RepositoryService);
DataDetail dataDetail = fileProcesser.DownloadDocument(file);
DataFile dataFile = new DataFile();
dataFile.ContentType = dataDetail.MimeTypeToDownLoad;
dataFile.CreatedBy = dataDetail.FileDetail.CreatedBy;
dataFile.FileInfo = dataDetail.FileDetail;
dataFile.FileExtentsion = System.IO.Path.GetExtension(dataDetail.FileNameToDownLoad);
dataFile.FileName = dataDetail.FileNameToDownLoad;
dataFile.FileContent = dataDetail.DataStream;
dataFile.IsCompressed = true;
if (dataFile.FileExtentsion.Equals(Constants.XLFileExtension, StringComparison.OrdinalIgnoreCase))
{
dataFile.IsCompressed = false;
}
return dataFile;
}
/// <summary>
/// Downloads the File from Repository
/// </summary>
/// <param name="file">File object.</param>
/// <param name="repository">Repository instance.</param>
/// <param name="user">User instance.</param>
/// <param name="credentials">credentials required by the repository.</param>
/// <returns>DataFile containing the file data.</returns>
public virtual DataFile DownLoadFileFromRepository(DM.File file, Repository repository, User user, RepositoryCredentials credentials)
{
throw new NotSupportedException();
}
protected static RepositoryModel GetRepositoryModel(DM.Repository repository, string authorization)
{
Check.IsNotNull(repository, "repository");
Check.IsNotNull(authorization, "authorization");
RepositoryModel repositoryModel = new RepositoryModel();
repositoryModel.Authorization = authorization;
repositoryModel.RepositoryName = MerritConstants.OneShare;
repositoryModel.SelectedRepository = repository;
repositoryModel.RepositoryLink = repository.HttpPostUriTemplate;
return repositoryModel;
}
/// <summary>
/// Helper method to get the post query data.
/// </summary>
/// <param name="identifier">Unique identifier.</param>
/// <param name="fileData">File information.</param>
/// <param name="postFileData">Post file indormation.</param>
/// <returns></returns>
protected MerritQueryData GetPostQueryData(string identifier, DM.File fileData, Citation citation, Repository repository, PublishMessage postFileData)
{
Check.IsNotNull(identifier, "identifier");
MerritQueryData queryData = new MerritQueryData();
// TODO: Currently hard coded, needs to be replaced with specific value
queryData.MetadataXML = @"<eml:eml packageId=""doi:10.5072/12345?"" system=""DCXL"" xmlns:eml=""eml://ecoinformatics.org/eml2.1.0"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""eml://ecoinformatics.org/eml-2.1.0 eml.xsd""> <dataset id=""doi:10.5072/12345""> <creator> <!-- http://knb.ecoinformatics.org/software/eml/eml-2.1.0/eml-resource.html#creator --> <!-- multiple creators allowed --> <individualName> <givenName></givenName> <surName></surName> </individualName> <address> <deliveryPoint></deliveryPoint> <city></city> <administrativeArea></administrativeArea><postalCode></postalCode><country></country></address><phone></phone><electronicMailAddress></electronicMailAddress><organizationName></organizationName></creator><title></title><pubDate></pubDate><abstract><para></para></abstract><publisher><para></para></publisher><url></url><contact><individualName><givenName></givenName><surName></surName></individualName><address><deliveryPoint></deliveryPoint><city></city><administrativeArea></administrativeArea><postalCode></postalCode><country></country></address><phone></phone><electronicMailAddress></electronicMailAddress><organizationName></organizationName></contact><keywordSet><keyword></keyword><keywordThesaurus></keywordThesaurus></keywordSet><coverage><!-- http://knb.ecoinformatics.org/software/eml/eml-2.1.0/eml-resource.html#coverage --> <geographicCoverage><geographicDescription></geographicDescription><boundingCoordinates><westBoundingCoordinate></westBoundingCoordinate><eastBoundingCoordinate></eastBoundingCoordinate><northBoundingCoordinate></northBoundingCoordinate><southBoundingCoordinate></southBoundingCoordinate></boundingCoordinates></geographicCoverage><temporalCoverage id=""tempcov""><rangeOfDates> <beginDate><calendarDate></calendarDate></beginDate><endDate><calendarDate></calendarDate></endDate></rangeOfDates></temporalCoverage></coverage><project><!-- http://knb.ecoinformatics.org/software/eml/eml-2.1.0/eml-dataset.html#project --><title></title><abstract><para></para></abstract><funding><para></para></funding></project> <intellectualRights> <para></para> </intellectualRights> <dataTable> <!-- http://knb.ecoinformatics.org/software/eml/eml-2.1.0/eml-dataTable.html#dataTable --> <!-- dataTable is equivalent to a single tab in the excel spreadsheet. One can have multiple data tables within the document. --> <entityName></entityName> <entityDescription></entityDescription> <attributeList> <!-- http://knb.ecoinformatics.org/software/eml/eml-2.1.0/eml-dataTable.html#attributeList --> <!-- attribute list is equivalent to the parameter table from the requirements document. One can have many attributes in a single table. --> <attribute> <attributeName> <!-- non-empty string --></attributeName> <attributeDefinition> <!-- non-empty string --></attributeDefinition> </attribute> </attributeList> </dataTable> </dataset> <additionalMetadata> <describes>tempcov</describes> <metadata> <description> <!-- non-empty string describing temporal coverage --> </description> </metadata> </additionalMetadata> <additionalMetadata> <metadata> <formattedCitation> <!-- non-empty string --> </formattedCitation> </metadata> </additionalMetadata></eml:eml>";
List<DKeyValuePair<string, string>> content = new List<DKeyValuePair<string, string>>();
Check.IsNotNull<DM.Repository>(repository, "selectedRepository");
var repositoryMetadata = repository.RepositoryMetadata.FirstOrDefault();
if (repositoryMetadata != null)
{
foreach (var repositoryMetaData in repositoryMetadata.RepositoryMetadataFields)
{
DKeyValuePair<string, string> metadata = new DKeyValuePair<string, string>();
metadata.Key = repositoryMetaData.Name;
var metadataField = fileData.FileMetadataFields.Where(f => f.RepositoryMetadataFieldId == repositoryMetaData.RepositoryMetadataFieldId).FirstOrDefault();
if (metadataField != null)
{
metadata.Value = fileData.FileMetadataFields.Where(f => f.RepositoryMetadataFieldId == repositoryMetaData.RepositoryMetadataFieldId).FirstOrDefault().MetadataValue;
}
content.Add(metadata);
}
}
//set the data to filemetadata fields
//postFileData.FileMetaDataFields = fileData.FileMetadataFields;
content.Add(new DKeyValuePair<string, string>() { Key = "Profile", Value = ConfigReader<string>.GetSetting("Profile_Post") });
content.Add(new DKeyValuePair<string, string>() { Key = "who", Value = citation.Publisher });
content.Add(new DKeyValuePair<string, string>() { Key = "what", Value = citation.Title });
content.Add(new DKeyValuePair<string, string>() { Key = "when", Value = DateTime.UtcNow.ToString(CultureInfo.InvariantCulture) });
content.Add(new DKeyValuePair<string, string>() { Key = "where", Value = identifier });
content.Add(new DKeyValuePair<string, string>() { Key = "ARK", Value = identifier });
queryData.KeyValuePair = content.ToArray();
return queryData;
}
public async Task<IEnumerable<FileSheet>> GetDocumentSheetDetails(DM.File fileDetail)
{
Check.IsNotNull(fileDetail, "fileDetail");
IFileProcesser fileProcesser = FileFactory.GetFileTypeInstance(Path.GetExtension(fileDetail.Name), this.BlobDataRepository, this.FileDataRepository, this.RepositoryService);
return await fileProcesser.GetDocumentSheetDetails(fileDetail);
}
private void GetHeaderIssues(DM.QualityCheck qualityCheck, IEnumerable<SheetCell> headers, QualityCheckResult qualityCheckResult)
{
var isValidFile = true;
var headerIssueExists = false;
//Check the headers are there or not
if (headers != null && headers.Any())
{
// CHECK THE ITEMS ARE STARTING FROM THE A1
if (headers.FirstOrDefault().ColumnLocation != "A1")
{
isValidFile = false;
headerIssueExists = true;
qualityCheckResult.Errors.Add(string.Concat("Invalid file, headers should start from A1 location"));
}
}
else
{
isValidFile = false;
headerIssueExists = true;
foreach (var qcName in qualityCheck.QualityCheckColumnRules)
{
qualityCheckResult.Errors.Add(string.Format(CultureInfo.CurrentCulture, "Header '{0}' is missing", qcName.HeaderName));
}
}
if (!isValidFile)//If it is valid file ,then only check the other conditions
{
return;
}
SheetCell header;
//first check all the name exists or not and if it is required show the missing message
foreach (var qcName in qualityCheck.QualityCheckColumnRules)
{
header = headers.Where(h => h.Value.Trim().Equals(qcName.HeaderName.Trim(), StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (header == null && qcName.IsRequired == true)
{
headerIssueExists = true;
qualityCheckResult.Errors.Add(string.Format(CultureInfo.CurrentCulture, "Header '{0}' is missing", qcName.HeaderName));
}
}
// If enforcecheck order is true ,then append the message
if (headerIssueExists || qualityCheck.EnforceOrder == false)
{
return;
}
var qualityCheckColumnRules = qualityCheck.QualityCheckColumnRules.OrderBy(col => col.Order);
List<int> columnIndices = new List<int>();
foreach (var qcName in qualityCheckColumnRules)
{
header = headers.Where(h => string.Compare(h.Value.Trim(), qcName.HeaderName.Trim(), StringComparison.OrdinalIgnoreCase) == 0).FirstOrDefault();
if (header != null)
{
var columnLocationIndex = GetColumnLocationIndex(header.Value, headers);
columnIndices.Add(columnLocationIndex);
}
}
// Cheking hte order of the columns
if (!Utilities.Helper.CheckIntListAscOrder(columnIndices))
{
headerIssueExists = true;
qualityCheckResult.Errors.Add(string.Format(CultureInfo.CurrentCulture, "Headers are not in order"));
}
}
private void WriteEML(MerritQueryData request, string filePath, DM.File file, DM.Repository repositoryData, IEnumerable<DM.FileColumnType> fileColumnTypes = null, IEnumerable<DM.FileColumnUnit> fileColumnUnits = null)
{
// XmlDocument metadataDocument = new XmlDocument();
XmlDocument emlDocument = new XmlDocument();
// metadataDocument.LoadXml(request.MetaDataDetail.MetadataMappingXML);
emlDocument.LoadXml(request.MetadataXML);
XmlNamespaceManager nsmgr = new XmlNamespaceManager(emlDocument.NameTable);
nsmgr.AddNamespace("eml", MerritConstants.EmlNamespace);
var repositoryMetadata = repositoryData.RepositoryMetadata.FirstOrDefault();
if (repositoryMetadata != null)
{
ReplaceMetadata(file, emlDocument, nsmgr, repositoryMetadata);
}
// Write Parameter Metadata properties.
if (file.FileColumns != null && file.FileColumns.Count > 0)
{
WriteParamMetadata(request, emlDocument, file, fileColumnTypes, fileColumnUnits);
}
////Added this following conditions outside the above for loop to avoid the following conditions not to repeat every time
////If contact element is not there, then insert the value from creator/surname
ManageContactElement(emlDocument);
////If no project title element ,then add the project title element value
ManageProjectTitleElement(emlDocument);
////If datatable has no entityname ,then add the entity name
ManageEntityNameElement(emlDocument);
////If datatable has no attributeList, then add attributeList node
ManageAttributeListElement(emlDocument);
////set the coordinate value for geographical location
ManageGeographicLocationCoordinates(emlDocument);
////check the temporal coverage conidtions
ManageTemporalCoverage(emlDocument);
////Remove the empty elements and remove the parent node when the no child node items are present
RemoveEmptyElements(emlDocument);
emlDocument.Save(filePath);
}
private void GetColumnIssues(DM.QualityCheck qualityCheck, IEnumerable<QualityCheckColumnType> qualityCheckTypes, IEnumerable<SheetCell> headers, IEnumerable<SheetCell> columns, QualityCheckResult qualityCheckResult)
{
if (columns == null || !columns.Any())
{
qualityCheckResult.Errors.Add(string.Format(CultureInfo.CurrentCulture, "No data except Headers"));
return;
}
foreach (var headerColumn in headers)
{
//Logic for aliases
QualityCheckColumnRule qualityCheckItem = null;
foreach (var item in qualityCheck.QualityCheckColumnRules)
{
// get the quality check item
if (item.HeaderName.Trim().Equals(headerColumn.Value.Trim(), StringComparison.OrdinalIgnoreCase))
{
qualityCheckItem = item;
break;
}
}
if (qualityCheckItem == null)
{
continue;
}
// get the column index of the header row
GetColumnIndex(headerColumn.Value);
// get all the child rows of this specfic header
var columnRowDataCollection = columns.Where(c => c.ColumnName.Trim().Equals(headerColumn.ColumnName.Trim(), StringComparison.OrdinalIgnoreCase));
// Checking for numerics and range on numarics
if (qualityCheckTypes.Where(q => q.QualityCheckColumnTypeId == qualityCheckItem.QualityCheckColumnTypeId).FirstOrDefault().Name == "Numeric")// Numeric
{
bool isTypevalid = true;
foreach (var columnData in columnRowDataCollection)
{
if (!columnData.Value.IsNumeric())
{
isTypevalid = false;
qualityCheckResult.Errors.Add(string.Format(CultureInfo.CurrentCulture, "Data under header '{0}' should be of Numeric type", headerColumn.Value));
break;
}
}
// Checking for range validation
if (isTypevalid && !string.IsNullOrEmpty(qualityCheckItem.Range))
{
string[] rangeValues = qualityCheckItem.Range.Split(new string[] { Utilities.Constants.RangeSeparator }, StringSplitOptions.None);
foreach (var columnData in columnRowDataCollection)
{
string message = CheckForRange(columnData.Value, rangeValues, headerColumn.Value);
if (!string.IsNullOrEmpty(message))
{
qualityCheckResult.Errors.Add(message);
break;
}
}
}
}
}
}
private static void ReplaceMetadata(DM.File file, XmlDocument emlDocument, XmlNamespaceManager nsmgr, DM.RepositoryMetadata repositoryMetadata)
{
foreach (var repositoryMetaDataField in repositoryMetadata.RepositoryMetadataFields)
{
try
{
var fieldName = repositoryMetaDataField.Name;
var fieldMapping = repositoryMetaDataField.Mapping;
//var fieldDataType = repositoryMetaData.MetadataType.ToString();
var fileMetaData = file.FileMetadataFields.Where(f => f.RepositoryMetadataFieldId == repositoryMetaDataField.RepositoryMetadataFieldId).FirstOrDefault();
var filedValue = string.Empty;
if (fileMetaData != null)
{
filedValue = fileMetaData.MetadataValue.ToString();
}
fieldMapping = fieldMapping.Replace('.', '/').Replace("eml", string.Empty);
if (!string.IsNullOrEmpty(fieldMapping))
{
fieldMapping = "./" + fieldMapping;
XmlElement root1 = emlDocument.DocumentElement;
XmlNode oldNode = root1.SelectSingleNode(fieldMapping, nsmgr);
if (oldNode != null)
{
if (string.Compare(fieldName, "keyword(s)", StringComparison.Ordinal) == 0)
{
if (!string.IsNullOrWhiteSpace(filedValue))
{
var keywords = filedValue.Split(',');
var keywordSet = oldNode.ParentNode;
keywordSet.RemoveChild(oldNode);
foreach (var keyword in keywords)
{
var keywordNode = oldNode.Clone();
keywordNode.InnerText = keyword;
keywordSet.InsertBefore(keywordNode, keywordSet.LastChild);
}
}
}
else if (string.Compare(fieldName, "keyword thesaurus used", StringComparison.Ordinal) == 0 && string.IsNullOrWhiteSpace(filedValue))
{
var keywordset = oldNode.ParentNode;
keywordset.RemoveChild(oldNode);
}
else
{
if (!string.IsNullOrWhiteSpace(filedValue))
{
oldNode.InnerText = filedValue;
}
}
}
}
}
catch
{
//suppress this errr
}
}
}
public async Task<IEnumerable<FileSheet>> GetDocumentSheetDetails(DM.File fileDetail)
{
Check.IsNotNull<DM.File>(fileDetail, "fileDetail");
List<FileSheet> fileSheets = new List<FileSheet>();
await Task.Factory.StartNew(() =>
{
var fileArray = base.GetFileContentsAsByteArray(fileDetail.BlobId);
SpreadsheetDocument excelDocument;
using (var documentStream = new MemoryStream(fileArray))
{
excelDocument = SpreadsheetDocument.Open(documentStream, false);
IEnumerable<Sheet> sheets = excelDocument.WorkbookPart.Workbook.Descendants<Sheet>();
foreach (Sheet item in sheets)
{
if (ExcelFileHelper.CheckSheetForData(excelDocument, item.Id))
{
fileSheets.Add(new FileSheet()
{
SheetName = item.Name,
SheetId = ((Path.GetExtension(fileDetail.Name) == Constants.CSVFileExtension) ? item.Name : item.Id)
});
}
}
}
});
return fileSheets;
}
public bool UpdateFile(DM.File fileDetails)
{
Check.IsNotNull<DM.File>(fileDetails, "fileToUpdate");
bool updateResult = false;
//file details and filemetadata fields will be updated
var updatedFile = this.FileDataRepository.UpdateFile(fileDetails);
//first update the file and file meta data fileds
if (updatedFile != null && updatedFile.FileId == fileDetails.FileId)
{
updateResult = true;
this.UnitOfWork.Commit();
}
return updateResult;
}
