/// <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 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); } } } }
/// <summary> /// Check for Field format length /// </summary> /// <param name="cell">COBieCell</param> /// <param name="sheetName">Sheet name</param> /// <param name="row">Row index</param> /// <param name="col">Column index</param> /// <param name="initialRowHash">Initial row hash value</param> /// <returns>COBieError or null</returns> public COBieError GetCOBieFieldOutOfBoundsError(COBieCell cell, COBieAttributeState state, COBieError.ErrorLevels errorLevel, string sheetName, int row, int col, string initialRowHash) { COBieError err = new COBieError(sheetName, cell.COBieColumn.ColumnName, "", COBieError.ErrorTypes.None, COBieError.ErrorLevels.None, initialRowHash, col, row); int maxLength = cell.COBieColumn.ColumnLength; if (cell.CellValue.Length > maxLength) { err.ErrorDescription = String.Format(ErrorDescription.Value_Out_of_Bounds, maxLength); err.ErrorType = COBieError.ErrorTypes.Value_Out_of_Bounds; err.ErrorLevel = errorLevel; // GetErroLevel(cell.COBieColumn.AttributeState); err.FieldValue = cell.CellValue; return(err); } return(null); }
/// <summary> /// check for Field format Error in passed cell /// </summary> /// <param name="cell">COBieCell</param> /// <param name="sheetName">Sheet name</param> /// <param name="row">Row index</param> /// <param name="col">Column index</param> /// <param name="initialRowHash">Initial row hash value</param> /// <returns>COBieError or null</returns> public COBieError GetCOBieFieldFormatError(COBieCell cell, COBieAttributeState state, COBieError.ErrorLevels errorLevel, string sheetName, int row, int col, string initialRowHash) { COBieError err = new COBieError(sheetName, cell.COBieColumn.ColumnName, "", COBieError.ErrorTypes.None, COBieError.ErrorLevels.None, initialRowHash, col, row); int maxLength = cell.COBieColumn.ColumnLength; COBieAllowedType allowedType = cell.COBieColumn.AllowedType; if ((state == COBieAttributeState.Required_IfSpecified) || (state == COBieAttributeState.Required_System) || (state == COBieAttributeState.Required_System_IfSpecified) ) //if a required value but marked as n/a then do not class as error { if (cell.CellValue == Constants.DEFAULT_STRING) { return(null); } } //check cell.COBieColumn.AllowedType for format errors switch (allowedType) { case COBieAllowedType.AlphaNumeric: if (!cell.IsAlphaNumeric()) { err.ErrorDescription = ErrorDescription.AlphaNumeric_Value_Expected; err.ErrorType = COBieError.ErrorTypes.AlphaNumeric_Value_Expected; } break; case COBieAllowedType.Email: if (!cell.IsEmailAddress()) { err.ErrorDescription = ErrorDescription.ISODate_Value_Expected; err.ErrorType = COBieError.ErrorTypes.ISODate_Value_Expected; } break; case COBieAllowedType.ISODate: case COBieAllowedType.ISODateTime: if (!cell.IsDateTime()) { err.ErrorDescription = ErrorDescription.ISODate_Value_Expected; err.ErrorType = COBieError.ErrorTypes.ISODate_Value_Expected; } break; case COBieAllowedType.Numeric: if (!cell.IsNumeric()) { err.ErrorDescription = ErrorDescription.Numeric_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Numeric_Value_Expected; } break; case COBieAllowedType.AnyType: case COBieAllowedType.Text: if (!cell.IsText()) { err.ErrorDescription = ErrorDescription.Text_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Text_Value_Expected; } break; default: break; } if (err.ErrorType != COBieError.ErrorTypes.None) { err.FieldValue = cell.CellValue; if (err.ErrorLevel == COBieError.ErrorLevels.None) //if set above, just in case we do set above { err.ErrorLevel = errorLevel; } return(err); } return(null); }
/// <summary> /// Validating of the column COBieAttributeState attributes for null or n/a values /// </summary> /// <param name="cell">COBieCell</param> /// <param name="sheetName">Sheet name</param> /// <param name="row">Row index</param> /// <param name="col">Column index</param> /// <param name="initialRowHash">Initial row hash value</param> /// <returns>COBieError or null</returns> private COBieError GetCobieFieldNullError(COBieCell cell, COBieAttributeState state, COBieError.ErrorLevels errorLevel, string sheetName, int row, int col, string initialRowHash) { COBieError err = new COBieError(sheetName, cell.COBieColumn.ColumnName, "", COBieError.ErrorTypes.None, COBieError.ErrorLevels.None, initialRowHash, col, row); if ((string.IsNullOrEmpty(cell.CellValue)) || (cell.CellValue == Constants.DEFAULT_STRING) ) { switch (state) { case COBieAttributeState.Required_PrimaryKey: case COBieAttributeState.Required_CompoundKeyPart: err.ErrorDescription = ErrorDescription.PrimaryKey_Violation; err.ErrorType = COBieError.ErrorTypes.PrimaryKey_Violation; err.ErrorLevel = errorLevel; break; case COBieAttributeState.Required_Information: case COBieAttributeState.Required_System: switch (cell.COBieColumn.AllowedType) { case COBieAllowedType.AlphaNumeric: err.ErrorDescription = ErrorDescription.AlphaNumeric_Value_Expected; err.ErrorType = COBieError.ErrorTypes.AlphaNumeric_Value_Expected; break; case COBieAllowedType.Email: err.ErrorDescription = ErrorDescription.Email_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Email_Value_Expected; break; case COBieAllowedType.ISODateTime: case COBieAllowedType.ISODate: err.ErrorDescription = ErrorDescription.ISODate_Value_Expected; err.ErrorType = COBieError.ErrorTypes.ISODate_Value_Expected; break; case COBieAllowedType.Numeric: err.ErrorDescription = ErrorDescription.Numeric_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Numeric_Value_Expected; break; case COBieAllowedType.AnyType: case COBieAllowedType.Text: err.ErrorDescription = ErrorDescription.Text_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Text_Value_Expected; break; default: err.ErrorDescription = ErrorDescription.Text_Value_Expected; err.ErrorType = COBieError.ErrorTypes.Text_Value_Expected; break; } err.ErrorLevel = errorLevel; //err.ErrorLevel = COBieError.ErrorLevels.Warning; //set as a warning break; case COBieAttributeState.Required_Reference_PrimaryKey: case COBieAttributeState.Required_Reference_PickList: case COBieAttributeState.Required_Reference_ForeignKey: err.ErrorDescription = ErrorDescription.Null_ForeignKey_Value; err.ErrorType = COBieError.ErrorTypes.Null_ForeignKey_Value; err.ErrorLevel = errorLevel; break; case COBieAttributeState.Required_IfSpecified: case COBieAttributeState.Required_System_IfSpecified: case COBieAttributeState.Required_PrimaryKey_IfSpecified: if (cell.CellValue == Constants.DEFAULT_STRING) { return(null); //if a required value but not required in validation template then do not class as error } break; default: return(null); } if (err.ErrorType != COBieError.ErrorTypes.None) { err.FieldValue = cell.CellValue; return(err); } } return(null); }