public static void ProcessClientChangeFile(Stream fileStream, List<BusinessDataColumnBuilder> extraColumns, BusinessDataTableMetaData clientMetaData, string uploadCode, string sheetName, string fileName)
{
try
{
var excelDataSet = ExcelProcessor.GetDataSetFromExcelStream(fileStream);
if (excelDataSet == null)
{
return;
}
var excelDataTable = excelDataSet.Tables[sheetName];
if (excelDataTable == null)
{
return;
}
var clientChangeTable = (excelDataTable.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is DBNull)))).CopyToDataTable();
ExcelProcessor.DropEmptyColumns(clientChangeTable);
ExcelProcessor.AddExtraColumns(clientChangeTable, extraColumns);
ExcelProcessor.DropUnmatchedColumns(clientChangeTable, clientMetaData);
var validationErrors = new List<BusinessExcelValidationRecord>();
validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(clientChangeTable, clientMetaData));
validationErrors.AddRange(ExcelProcessor.ValidateDataTypes(clientChangeTable, clientMetaData));
validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(clientChangeTable, clientMetaData));
if (validationErrors.Count > 0)
{
ExcelProcessor.WriteExcelValidationErrors(validationErrors, uploadCode, fileName);
}
else
{
DynamicEntity.ClearTable(clientMetaData.Name, DALISGDatabaseType.ISGInput);
ExcelProcessor.BulkInsert(clientChangeTable, clientMetaData, clientMetaData.Name, BusinessISGDatabaseType.ISGInput);
if (clientChangeTable.Columns.Contains("YearQuater") && clientChangeTable.Rows != null && clientChangeTable.Rows.Count > 0)
{
var yearQuarter = clientChangeTable.Rows[0]["YearQuater"];
DALClient.ClientMaintenance(clientMetaData.Name, (yearQuarter == null) ? string.Empty : yearQuarter.ToString());
}
ExcelProcessor.CheckForExcelValidationSuccess(uploadCode);
}
excelDataSet.Dispose();
}
catch (Exception ex)
{
ExcelProcessor.WriteExcelValidationSystemException(ex, uploadCode);
}
}
public static List<BusinessExcelValidationRecord> ValidateRequiredColumns(DataTable changeTable, BusinessDataTableMetaData metaData)
{
var rtrn = new List<BusinessExcelValidationRecord>();
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (var metaColumn in metaData.Columns.Where(x => x.IsNullable == false && x.IsReadonlyIdentity == false))
{
if (changeTable.Columns[metaColumn.ExcelColumn] == null && changeTable.Columns[metaColumn.Name] == null)
{
rtrn.Add(new BusinessExcelValidationRecord(changeTable.TableName, metaColumn.FriendlyName, BusinessExcelValidationResultType.RequiredColumnMissing));
}
}
return rtrn;
}
public static List<BusinessExcelValidationRecord> ValidateDataTypes(DataTable changeTable, BusinessDataTableMetaData metaData)
{
var rtrn = new List<BusinessExcelValidationRecord>();
for (var i = 0; i < changeTable.Rows.Count; i++)
{
var row = changeTable.Rows[i];
var excelRowIndex = i + 2;
foreach (DataColumn column in changeTable.Columns)
{
var metaDataColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) && x.StrongType != typeof(string));
if ((metaDataColumn == null) || ((string.IsNullOrEmpty(row[metaDataColumn.ExcelColumn].ToString()) && metaDataColumn.IsNullable)))
{
continue;
}
var valid = true;
if (metaDataColumn.StrongType == typeof(DateTime))
{
DateTime testValue;
valid = DateTime.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue);
}
else if (metaDataColumn.StrongType == typeof(long))
{
long testValue;
row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString());
valid = long.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue);
if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%"))
{
row[metaDataColumn.ExcelColumn] = testValue / 100;
}
}
else if (metaDataColumn.StrongType == typeof(bool))
{
bool testValue;
valid = bool.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue);
}
else if (metaDataColumn.StrongType == typeof(DateTimeOffset))
{
DateTimeOffset testValue;
valid = DateTimeOffset.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue);
}
else if (metaDataColumn.StrongType == typeof(decimal))
{
decimal testValue;
row[metaDataColumn.ExcelColumn] = UpdateDecimalTemplates(row[metaDataColumn.ExcelColumn].ToString());
valid = decimal.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue);
if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%"))
{
row[metaDataColumn.ExcelColumn] = testValue / 100;
}
}
else if (metaDataColumn.StrongType == typeof(double))
{
double testValue;
row[metaDataColumn.ExcelColumn] = UpdateDecimalTemplates(row[metaDataColumn.ExcelColumn].ToString());
valid = double.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue);
if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%"))
{
row[metaDataColumn.ExcelColumn] = testValue / 100;
}
}
else if (metaDataColumn.StrongType == typeof(int))
{
int testValue;
row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString());
valid = int.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue);
if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%"))
{
row[metaDataColumn.ExcelColumn] = testValue / 100;
}
}
else if (metaDataColumn.StrongType == typeof(short))
{
short testValue;
row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString());
valid = short.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue);
if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%"))
{
row[metaDataColumn.ExcelColumn] = testValue / 100;
}
}
else if (metaDataColumn.StrongType == typeof(TimeSpan))
{
TimeSpan testValue;
valid = TimeSpan.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue);
}
else if (metaDataColumn.StrongType == typeof(Guid))
{
Guid testValue;
valid = Guid.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue);
}
if (!valid)
{
rtrn.Add(
new BusinessExcelValidationRecord(changeTable.TableName, column.ColumnName, excelRowIndex,metaDataColumn.StrongType,BusinessExcelValidationResultType.DataTypeValidation)
{
BadValue = row[metaDataColumn.ExcelColumn].ToString()
});
}
}
}
return rtrn;
}
public static void BulkInsert(DataTable changeTable, BusinessDataTableMetaData metaData, string tableName, BusinessISGDatabaseType databaseType)
{
var columns = new List<BusinessDataTableColumn>();
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (DataColumn column in changeTable.Columns)
{
var selectedColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
var isNullable = (selectedColumn == null || selectedColumn.IsNullable);
var typeName = (selectedColumn != null) ? selectedColumn.StrongType.ToString() : column.DataType.ToString();
var length = (selectedColumn == null || selectedColumn.Length == 0) ? Convert.ToInt16(-1) : selectedColumn.Length;
var columnName = (selectedColumn != null) ? selectedColumn.Name : column.ColumnName;
columns.Add(
new BusinessDataTableColumn
{
Name = columnName,
IsNullable = isNullable,
TypeName = typeName,
Length = length
});
}
var rows = new List<BusinessDataTableRow>();
foreach (DataRow row in changeTable.Rows)
{
var cells = new List<BusinessDataTableCell>();
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (var column in columns)
{
var selectedColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.Name, column.Name, StringComparison.CurrentCultureIgnoreCase));
var columnName = (selectedColumn != null && !string.IsNullOrEmpty(selectedColumn.ExcelColumn)) ? selectedColumn.ExcelColumn : column.Name;
cells.Add(
new BusinessDataTableCell
{
ColumnName = column.Name,
Value = row[columnName].ToString()
});
}
rows.Add(
new BusinessDataTableRow
{
Cells = cells
});
}
var businessDataTable =
new BusinessDataTable
{
Name = tableName,
Columns = columns,
Rows = rows
};
var database = Mapper.Map<BusinessISGDatabaseType, DALISGDatabaseType>(databaseType);
var dalDataTable = Mapper.Map<BusinessDataTable, DALDataTable>(businessDataTable);
DynamicEntity.BulkInsert(dalDataTable, database);
}
public static void DropUnmatchedColumns(DataTable changeTable, BusinessDataTableMetaData metaData)
{
// ReSharper disable once LoopCanBePartlyConvertedToQuery
foreach (var column in changeTable.Columns.Cast<DataColumn>().ToArray())
{
var metaDataColumnExists = metaData.Columns.Any(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) || string.Equals(x.Name, column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
if (!metaDataColumnExists)
{
changeTable.Columns.Remove(column);
}
}
}
public static List<BusinessExcelValidationRecord> ValidateMaxLength(DataTable changeTable, BusinessDataTableMetaData metaData)
{
var rtrn = new List<BusinessExcelValidationRecord>();
for (var i = 0; i < changeTable.Rows.Count; i++)
{
var row = changeTable.Rows[i];
var excelRowIndex = i + 2;
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (DataColumn column in changeTable.Columns)
{
var metaDataColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) && x.IsNullable == false && x.IsReadonlyIdentity == false && x.StrongType == typeof(string));
if ((metaDataColumn != null) && (!string.IsNullOrEmpty(row[metaDataColumn.ExcelColumn].ToString())) && (row[metaDataColumn.ExcelColumn].ToString().Length > metaDataColumn.Length))
{
rtrn.Add(
new BusinessExcelValidationRecord(changeTable.TableName, column.ColumnName, excelRowIndex,BusinessExcelValidationResultType.MaximumStringLengthViolation)
{
BadValue = row[metaDataColumn.ExcelColumn].ToString()
});
}
}
}
return rtrn;
}
private static void AddDropColumns(DataTable universeTable, BusinessDataTableMetaData metaData, List<BusinessDataColumnBuilder> extraColumns)
{
ExcelProcessor.DropEmptyColumns(universeTable);
ExcelProcessor.AddExtraColumns(universeTable, extraColumns);
ExcelProcessor.DropUnmatchedColumns(universeTable, metaData);
}
private static void ValidateAndProcessColumns(DataTable universeTable, BusinessDataTableMetaData metaData, BusinessUniverseMap universeMap, string uploadCode, string fileName)
{
var validationErrors = new List<BusinessExcelValidationRecord>();
validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(universeTable, metaData));
validationErrors.AddRange(ExcelProcessor.ValidateDataTypes(universeTable, metaData));
validationErrors.AddRange(ExcelProcessor.ValidateMaxLength(universeTable, metaData));
validationErrors.AddRange(ExcelProcessor.ValidateRequiredFields(universeTable, metaData));
if (validationErrors.Count > 0)
{
ExcelProcessor.WriteExcelValidationErrors(validationErrors, uploadCode, fileName);
}
else
{
ExcelProcessor.BulkInsert(universeTable, metaData, universeMap.InputTable, universeMap.LoadISGDatabase);
}
}