/// <summary>
/// Validate the row columns against the attributes set for each column
/// </summary>
public void ValidateFields(ICOBieSheetValidationTemplate SheetValidator)
{
int r = 0;
foreach (T row in Rows)
{
r++;
for (int col = 0; col < row.RowCount; col++)
{
var cell = row[col];
COBieAttributeState state = cell.COBieColumn.AttributeState;
var errorLevel = COBieError.ErrorLevels.Warning;
if (SheetValidator != null && SheetValidator.IsRequired.ContainsKey(col) && SheetValidator.IsRequired[col])
{
// Override required state based on validation template
if ((SheetValidator.IsRequired.ContainsKey(col) && SheetValidator.IsRequired[col]))
{
errorLevel = COBieError.ErrorLevels.Error;
switch (state)
{
case COBieAttributeState.Required_IfSpecified:
state = COBieAttributeState.Required_Information;
break;
case COBieAttributeState.Required_System_IfSpecified:
state = COBieAttributeState.Required_System;
break;
case COBieAttributeState.Required_PrimaryKey_IfSpecified:
state = COBieAttributeState.Required_PrimaryKey;
break;
default:
break;
}
}
}
COBieError errNull = GetCobieFieldNullError(row[col], state, errorLevel, SheetName, r, col, row.InitialRowHashValue);
if (errNull != null)
{
_errors.Add(errNull);
}
else //passed null check so check format
{
errNull = GetCOBieFieldOutOfBoundsError(row[col], state, errorLevel, SheetName, r, col, row.InitialRowHashValue);
if (errNull != null)
{
_errors.Add(errNull);
}
errNull = GetCOBieFieldFormatError(row[col], state, errorLevel, SheetName, r, col, row.InitialRowHashValue);
if (errNull != null)
{
_errors.Add(errNull);
}
}
}
}
}
/// <summary>
/// Validate the existence of the Foreign Key value on the referencing sheet, if not add error
/// </summary>
/// <param name="context">COBieContext object holding global values for this model</param>
private void ValidateForeignKeys(COBieWorkbook workbook, ICOBieSheetValidationTemplate SheetValidator)
{
int rowIndex = 1;
foreach (COBieRow row in Rows)
{
foreach (COBieColumn foreignKeyColumn in ForeignKeyColumns)
{
// TODO: COBieColumn should own the relationship rather than creating a new one each time.
COBieColumnRelationship cobieReference = new COBieColumnRelationship(workbook, foreignKeyColumn);
if ((SheetValidator == null) ||
(SheetValidator.IsRequired.ContainsKey(foreignKeyColumn.ColumnOrder) && SheetValidator.IsRequired[foreignKeyColumn.ColumnOrder])
)
{
if (!string.IsNullOrEmpty(foreignKeyColumn.ReferenceColumnName))
{
COBieCell cell = row[foreignKeyColumn.ColumnOrder];
string foreignKeyValue = cell.CellValue;
// Don't validate nulls. Will be reported by the Foreign Key null value check, so just skip here
if (!string.IsNullOrEmpty(foreignKeyValue))
{
bool isValid = false;
bool isPickList = (cobieReference.SheetName == Constants.WORKSHEET_PICKLISTS);
if (isPickList)
{
isValid = PickListMatch(cobieReference, cell);
}
else
{
isValid = ForeignKeyMatch(cobieReference, cell);
}
//report no match
if (!isValid)
{
string errorDescription = BuildErrorMessage(cobieReference, isPickList);
COBieError.ErrorTypes errorType = isPickList == true
? COBieError.ErrorTypes.PickList_Violation
: COBieError.ErrorTypes.ForeignKey_Violation;
COBieError error = new COBieError(SheetName, foreignKeyColumn.ColumnName,
errorDescription, errorType,
COBieError.ErrorLevels.Error, row.InitialRowHashValue,
foreignKeyColumn.ColumnOrder, rowIndex);
_errors.Add(error);
}
}
}
}
}
rowIndex++;
}
}
/// <summary>
/// Validate the sheet
/// </summary>
/// <param name="workbook"></param>
public void Validate(COBieWorkbook workbook, ErrorRowIndexBase errorRowIdx, ICOBieSheetValidationTemplate SheetValidator)
{
_errorRowIdx = errorRowIdx; //set the index for error reporting on rows
_errors.Clear();
ValidatePrimaryKeysUnique(SheetValidator);
ValidateFields(SheetValidator);
ValidateForeignKeys(workbook, SheetValidator);
}
/// <summary>
/// Validate the Primary Keys only exist once in the sheet
/// </summary>
private void ValidatePrimaryKeysUnique(ICOBieSheetValidationTemplate SheetValidator)
{
var dupes = Rows
.Select((v, i) => new { row = v, index = i }) //get COBieRow and its index in the list
.GroupBy(r => r.row.GetPrimaryKeyValue.ToLower().Trim(), (key, group) => new { rowkey = key, rows = group }) //group by the primary key value(s) joint as a delimited string
.Where(grp => grp.rows.Count() > 1);
List <string> keyColList = new List <string>();
foreach (COBieColumn col in KeyColumns)
{
keyColList.Add(col.ColumnName);
}
string keyCols = string.Join(",", keyColList);
//set the index for the reported error row numbers
int errorRowInc = 2; //default for rows starting at row two - ErrorRowIndexBase.Two
if (_errorRowIdx == ErrorRowIndexBase.RowOne) //if error row starting sow set the the row numbered one
{
errorRowInc = 1;
}
foreach (var dupe in dupes)
{
List <string> indexList = new List <string>();
foreach (var row in dupe.rows)
{
if ((SheetValidator == null) ||
(SheetValidator.IsRequired.ContainsKey(row.index) && SheetValidator.IsRequired[row.index])
)
{
indexList.Add((row.index + errorRowInc).ToString());
}
}
string rowIndexList = string.Join(",", indexList);
foreach (var row in dupe.rows)
{
if ((SheetValidator == null) ||
(SheetValidator.IsRequired.ContainsKey(row.index) && SheetValidator.IsRequired[row.index])
)
{
string errorDescription = String.Format(ErrorDescription.PrimaryKey_Violation, keyCols,
rowIndexList);
COBieError error = new COBieError(SheetName, keyCols, errorDescription,
COBieError.ErrorTypes.PrimaryKey_Violation,
COBieError.ErrorLevels.Error, row.row.InitialRowHashValue, KeyColumns.First().ColumnOrder,
(row.index + 1));
_errors.Add(error);
}
}
}
}
private void ReportErrors(COBieWorkbook workbook, ICOBieValidationTemplate validationTemplate = null)
{
var errorsSheet = XlsWorkbook.GetSheet(ErrorsSheet) ?? XlsWorkbook.CreateSheet(ErrorsSheet);
ICOBieSheetValidationTemplate sheetValidator = null;
foreach (var sheet in workbook.OrderBy(w => w.SheetName))
{
if (sheet.SheetName != Constants.WORKSHEET_PICKLISTS)
{
if (validationTemplate != null && validationTemplate.Sheet.ContainsKey(sheet.SheetName))
{
sheetValidator = validationTemplate.Sheet[sheet.SheetName];
}
// Ensure the validation is up to date
sheet.Validate(workbook, ErrorRowIndexBase.RowTwo, sheetValidator);
}
WriteErrors(errorsSheet, sheet.Errors);
}
}
private void ReportErrors(COBieWorkbook workbook, ICOBieValidationTemplate ValidationTemplate = null)
{
ISheet errorsSheet = ExcelWorkbook.GetSheet(ErrorsSheet) ?? ExcelWorkbook.CreateSheet(ErrorsSheet);
//if we are validating here then ensure we have Indices on each sheet
//workbook.CreateIndices();
ICOBieSheetValidationTemplate SheetValidator = null;
foreach (var sheet in workbook.OrderBy(w => w.SheetName))
{
if (sheet.SheetName != Constants.WORKSHEET_PICKLISTS)
{
if (ValidationTemplate != null && ValidationTemplate.Sheet.ContainsKey(sheet.SheetName))
{
SheetValidator = ValidationTemplate.Sheet[sheet.SheetName];
}
// Ensure the validation is up to date
sheet.Validate(workbook, ErrorRowIndexBase.RowTwo, SheetValidator);
}
WriteErrors(errorsSheet, sheet.Errors);
}
}