/// <summary>
/// Resets the color scheme of the parameter rows in the parameter sheet
/// </summary>
/// <param name="application">
/// The Excel application.
/// </param>
/// <param name="workbook">
/// The current <see cref="Workbook"/>.
/// </param>
public void ResetRows(Application application, Workbook workbook)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "CDP4: Reset row color scheme in Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
try
{
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
parameterRange.Interior.Color = XlRgbColor.rgbWhite;
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
application.StatusBar = string.Format("CDP4: Reset row color scheme in {0} [ms]", sw.ElapsedMilliseconds);
}
}
/// <summary>
/// Highlight the rows of the <see cref="Thing"/>s in the <paramref name="things"/>
/// </summary>
/// <param name="application">
/// The Excel application.
/// </param>
/// <param name="workbook">
/// The current <see cref="Workbook"/>.
/// </param>
/// <param name="things">
/// The <see cref="Thing"/>s that need to be highlighted in the <see cref="Workbook"/>
/// </param>
public void HighlightRows(Application application, Workbook workbook, IReadOnlyDictionary <Guid, ProcessedValueSet> processedValueSets)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "Highlighting rows in Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
try
{
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, false);
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
this.ResetDefaultColorScheme(parameterRange);
this.parameterContent = (object[, ])parameterRange.Value;
var currentRow = parameterRange.Row;
application.StatusBar = string.Format("Processing Parameter sheet - row: {0}", currentRow);
for (var i = 1; i < this.parameterContent.GetLength(0); i++)
{
var rowType = (string)this.parameterContent[i, ParameterSheetConstants.TypeColumn];
if (rowType != null)
{
var rowIid = Convert.ToString(this.parameterContent[i, ParameterSheetConstants.IdColumn]);
var rowIidChar = rowIid.Split(':');
var thingIid = rowIidChar[0];
Guid iid;
var isIid = Guid.TryParse(thingIid, out iid);
//TODO: fix for compites
if (isIid && processedValueSets.ContainsKey(iid))
{
var row = parameterRange.Rows[i];
row.Interior.Color = XlRgbColor.rgbYellow;
}
}
currentRow++;
}
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
application.StatusBar = string.Format("Highlighted rows in {0} [ms]", sw.ElapsedMilliseconds);
}
}
/// <summary>
/// Process the <see cref="ParameterSubscriptionValueSet"/> .
/// </summary>
/// <param name="parameterSubscriptionValueSet">
/// The <see cref="ParameterSubscriptionValueSet"/> to be processed.
/// </param>
/// <param name="componentIndex">
/// The index of the <see cref="ParameterTypeComponent"/>.
/// </param>
/// <param name="currentRow">
/// The row in the Parameter sheet that contains the <see cref="ParameterValueSet"/>.
/// </param>
/// <param name="manualValue">
/// The manual value of the <see cref="ParameterValueSet"/>.
/// </param>
/// <param name="switchValue">
/// The string value of the <see cref="ParameterSwitchKind"/> of the <see cref="ParameterSubscriptionValueSet"/>
/// </param>
/// <param name="valuesets">
/// A <see cref="Dictionary{Guid,ProcessedValueSet}"/> of <see cref="ProcessedValueSet"/>s that capture the updated <see cref="Thing"/>s with its value validation result
/// </param>
/// <param name="provider">
/// The <see cref="IFormatProvider"/> used to validate.
/// </param>
/// <returns>
/// The <see cref="ValidationResultKind"/> for the <see cref="ParameterSubscriptionValueSet"/>
/// </returns>
private void ProcessValueSet(ParameterSubscriptionValueSet parameterSubscriptionValueSet, int componentIndex, int currentRow, object manualValue, string switchValue, ref Dictionary <Guid, ProcessedValueSet> valuesets, IFormatProvider provider)
{
var validationResult = ValidationResultKind.InConclusive;
var switchKind = ParameterSwitchKind.MANUAL;
ParameterType parameterType = null;
MeasurementScale measurementScale = null;
ParameterSheetUtilities.QueryParameterTypeAndScale(parameterSubscriptionValueSet, componentIndex, out parameterType, out measurementScale);
ValidationResult validSwitch;
var isValidSwitchKind = Enum.IsDefined(typeof(ParameterSwitchKind), switchValue);
if (isValidSwitchKind)
{
switchKind = (ParameterSwitchKind)Enum.Parse(typeof(ParameterSwitchKind), switchValue);
validSwitch = new ValidationResult {
ResultKind = ValidationResultKind.Valid, Message = string.Empty
};
}
else
{
switchKind = ParameterSwitchKind.MANUAL;
validSwitch = new ValidationResult {
ResultKind = ValidationResultKind.Invalid, Message = string.Format("{0} is not a valid Parameter Switch Kind", switchValue)
};
}
if (validSwitch.ResultKind > validationResult)
{
validationResult = validSwitch.ResultKind;
}
var validManualValue = new ValidationResult
{
ResultKind = ValidationResultKind.InConclusive,
Message = string.Empty
};
if (parameterType != null)
{
if (parameterType is TimeOfDayParameterType)
{
ParameterSheetUtilities.ConvertDoubleToDateTimeObject(ref manualValue, parameterType);
}
validManualValue = parameterType.Validate(manualValue, measurementScale, provider);
if (validManualValue.ResultKind > validationResult)
{
validationResult = validManualValue.ResultKind;
}
}
ParameterSheetUtilities.Decorate(validManualValue, this.parameterSheet, currentRow, ParameterSheetConstants.ManualColumn);
ProcessedValueSet processedValueSet;
var valueSetExists = valuesets.TryGetValue(parameterSubscriptionValueSet.Iid, out processedValueSet);
if (!valueSetExists)
{
processedValueSet = new ProcessedValueSet(parameterSubscriptionValueSet, validationResult);
}
ValueSetValues valueSetValues;
if (processedValueSet.IsDirty(componentIndex, parameterType, switchKind, manualValue, null, null, null, out valueSetValues))
{
processedValueSet.UpdateClone(valueSetValues);
if (!valueSetExists)
{
valuesets.Add(parameterSubscriptionValueSet.Iid, processedValueSet);
}
}
}
/// <summary>
/// Validate the values on the Parameter sheet and check for any changes that have been made by the user
/// </summary>
/// <param name="application">
/// The excel application object that contains the <see cref="Workbook"/> in which the parameter sheet is to be processed.
/// </param>
/// <param name="workbook">
/// The <see cref="Workbook"/> that contains the Parameter sheet that is being processed
/// </param>
/// <param name="processedValueSets">
/// A <see cref="List{ProcessedValueSet}"/> of clones that capture the updated <see cref="Thing"/>s with its value validation result
/// </param>
/// <remarks>
/// Changed cells will be highlighted, cells containing invalid or out-of-bounds data will be marked.
/// </remarks>
public void ValidateValuesAndCheckForChanges(Application application, Workbook workbook, out IReadOnlyDictionary <Guid, ProcessedValueSet> processedValueSets)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "Processing Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
var temporaryProcessedValueSets = new Dictionary <Guid, ProcessedValueSet>();
var numberFormatInfo = this.QuerayNumberFormatInfo(application);
var rowType = string.Empty;
var rowIid = string.Empty;
try
{
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, false);
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
this.parameterContent = (object[, ])parameterRange.Value;
this.parameterFormula = (object[, ])parameterRange.Formula;
var currentRow = parameterRange.Row;
application.StatusBar = string.Format("Processing Parameter sheet - row: {0}", currentRow);
var parameterContentRows = this.parameterContent.GetLength(0) + 1;
for (var i = 1; i < parameterContentRows; i++)
{
try
{
rowType = (string)this.parameterContent[i, ParameterSheetConstants.TypeColumn];
}
catch (Exception ex)
{
logger.Error(ex);
}
if (rowType != string.Empty &&
(rowType == ParameterSheetConstants.PVS || rowType == ParameterSheetConstants.PVSCT ||
rowType == ParameterSheetConstants.POVS || rowType == ParameterSheetConstants.POVSCT ||
rowType == ParameterSheetConstants.PSVS || rowType == ParameterSheetConstants.PSVSCT))
{
rowIid = Convert.ToString(this.parameterContent[i, ParameterSheetConstants.IdColumn]);
var computedValue = this.QueryComputedValue(i);
var formulaValue = this.QueryFormulaValue(i);
var manualValue = this.QueryManualValue(i);
var referenceValue = this.QueryReferenceValue(i);
var switchValue = this.QuerySwitchValue(i);
var actualValue = this.QueryActualValue(i);
var rowIidChar = rowIid.Split(':');
var thingIid = rowIidChar[0];
var componentIndex = 0;
if (rowIidChar.Length > 1)
{
componentIndex = int.Parse(rowIidChar[1]);
}
var lazyThing = this.workbookSession.Assembler.Cache.Select(item => item.Value).SingleOrDefault(item => item.Value.Iid == Guid.Parse(thingIid));
if (lazyThing != null)
{
var thing = lazyThing.Value;
if (rowType == ParameterSheetConstants.PVS || rowType == ParameterSheetConstants.PVSCT)
{
var parameterValueSet = (ParameterValueSet)thing;
this.ProcessValueSet(parameterValueSet, componentIndex, currentRow, manualValue, computedValue, referenceValue, actualValue, switchValue, formulaValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
if (rowType == ParameterSheetConstants.POVS || rowType == ParameterSheetConstants.POVSCT)
{
var parameterOverrideValueSet = (ParameterOverrideValueSet)thing;
this.ProcessValueSet(parameterOverrideValueSet, componentIndex, currentRow, manualValue, computedValue, referenceValue, actualValue, switchValue, formulaValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
if (rowType == ParameterSheetConstants.PSVS || rowType == ParameterSheetConstants.PSVSCT)
{
var parameterSubscriptionValueSet = (ParameterSubscriptionValueSet)thing;
this.ProcessValueSet(parameterSubscriptionValueSet, componentIndex, currentRow, manualValue, switchValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
}
else
{
logger.Warn("The Thing of RowType {0} with unique id {1} could not be found in the workbook cache", rowType, thingIid);
}
}
currentRow++;
}
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
if (rowType != string.Empty && rowIid != string.Empty)
{
this.errorMessage = $"{rowType}:{rowIid} - {ex.Message}";
}
else
{
this.errorMessage = ex.Message;
}
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
if (string.IsNullOrEmpty(this.errorMessage))
{
application.StatusBar = string.Format("CDP4: Parameter sheet processed in {0} [ms]", sw.ElapsedMilliseconds);
}
else
{
application.StatusBar = string.Format("CDP4: The following error occured while processing the sheet: {0}", this.errorMessage);
}
processedValueSets = new Dictionary <Guid, ProcessedValueSet>(temporaryProcessedValueSets);
}
}