/// <summary>
/// Check for valid low or high value
/// </summary>
/// <param name="val"></param>
/// <param name="colType"></param>
/// <param name="r"></param>
/// <param name="c"></param>
/// <returns></returns>
private bool IsValidValue(
string val,
MetaColumnType colType,
int r,
int c)
{
double d1;
if (MetaColumn.IsNumericMetaColumnType(colType))
{
if (!double.TryParse(val, out d1))
{
XtraMessageBox.Show("Invalid numeric value", UmlautMobius.String);
//RulesGrid.EditCell(r, c);
return(false);
}
}
else if (colType == MetaColumnType.Date)
{
if (DateTimeMx.Normalize(val) == null)
{
XtraMessageBox.Show("Invalid date", UmlautMobius.String);
//RulesGrid.EditCell(r, c);
return(false);
}
}
return(true);
}
/// <summary>
/// Set derived values for a single calc function field
/// </summary>
/// <param name="sourceColumnType"></param>
/// <param name="cfc"></param>
void SetDerivedColumnValues(
MetaColumnType sourceColumnType,
CalcFieldColumn cfc)
{
cfc.FunctionEnum = CalcFuncEnum.Unknown;
cfc.ConstantDouble = 0;
cfc.ResultColumnType = MetaColumnType.Unknown;
if (sourceColumnType == MetaColumnType.Unknown)
{
return;
}
if (!String.IsNullOrEmpty(cfc.Function))
{
cfc.FunctionEnum = ConvertCalcFuncStringToEnum(cfc.Function);
}
else
{
cfc.FunctionEnum = CalcFuncEnum.None;
}
double.TryParse(cfc.Constant, out cfc.ConstantDouble);
// Get datatype after applying function
cfc.ResultColumnType = sourceColumnType;
if (sourceColumnType == MetaColumnType.Date)
{
if (cfc.FunctionEnum == CalcFuncEnum.DaysSince)
{
cfc.ResultColumnType = MetaColumnType.Integer;
}
else if (cfc.FunctionEnum == CalcFuncEnum.None)
{
}
else
{
throw new Exception("Invalid function for field");
}
}
else if (MetaColumn.IsNumericMetaColumnType(sourceColumnType))
{
if (cfc.FunctionEnum == CalcFuncEnum.DaysSince)
{
throw new Exception("Invalid function for field");
}
}
else
{
if (cfc.FunctionEnum != CalcFuncEnum.None)
{
throw new Exception("Invalid function for field");
}
}
}
/// <summary>
/// Set role and default grouping/summary type based on the metacolumn type
/// </summary>
/// <param name="role"></param>
/// <param name="mct"></param>
public void SetDefaultTypeIfUndefined(
MetaColumn mc,
bool setIfAlreadyDefined = false)
{
MetaColumnType mct = mc.DataType;
if (IsGroupingType)
{
if (mct == MetaColumnType.Date && GroupingType == GroupingTypeEnum.EqualValues)
{
GroupingType = GroupingTypeEnum.Date; // fixup for date types
}
if (GroupingType == GroupingTypeEnum.Undefined || setIfAlreadyDefined)
{
if (mct == MetaColumnType.Date)
{
GroupingType = GroupingTypeEnum.Date;
}
else
{
GroupingType = GroupingTypeEnum.EqualValues;
}
}
}
else if (Role == AggregationRole.DataSummary)
{
if (SummaryType == SummaryTypeEnum.Undefined || setIfAlreadyDefined)
{
if (MetaColumn.IsNumericMetaColumnType(mct))
{
if (mc.SinglePoint && mc.MultiPoint)
{
SummaryType = SummaryTypeEnum.ResultMean;
}
else if (mc.MultiPoint)
{
SummaryType = SummaryTypeEnum.GeometricMean;
}
else
{
SummaryType = SummaryTypeEnum.ArithmeticMean;
}
}
else
{
SummaryType = SummaryTypeEnum.Count;
}
}
}
return;
}
/// <summary>
/// Allow numeric types only
/// </summary>
public void AllowNumericTypesOnly()
{
_excludedDataTypes = new HashSet <MetaColumnType>();
foreach (MetaColumnType mcType in (MetaColumnType[])Enum.GetValues(typeof(MetaColumnType)))
{
if (!MetaColumn.IsNumericMetaColumnType(mcType))
{
_excludedDataTypes.Add(mcType);
}
}
return;
}
/// <summary>
/// Set the column type for the source data
/// </summary>
public void SetupFormForColumnType(
MetaColumnType mcType)
{
if (MetaColumn.AreCompatibleMetaColumnTypes(mcType, SourceColumnType))
{
return;
}
SourceColumnType = mcType;
string[] funcs = null;
string[] ops = null;
if (MetaColumn.IsNumericMetaColumnType(mcType))
{
funcs = CalcField.NumericFuncs;
ops = CalcField.NumericOps;
}
else if (mcType == MetaColumnType.Date)
{
funcs = CalcField.DateFuncs;
ops = CalcField.DateOps;
}
else if (mcType == MetaColumnType.Image) // allow overlay of NGR CRC curves
{
funcs = CalcField.CrcOverlayFuncs;
ops = CalcField.CrcOverlayOps;
}
else
{
funcs = CalcField.NoFuncs;
ops = CalcField.NoOps;
}
foreach (CalcFieldColumnControl cfcc in CfColCtls)
{
cfcc.Function.Properties.Items.Clear();
cfcc.Function.Properties.Items.AddRange(funcs);
cfcc.Function.SelectedIndex = 0;
}
Operation.Properties.Items.Clear();
Operation.Properties.Items.AddRange(ops);
Operation.SelectedIndex = 0;
return;
}
/// <summary>
/// Initialize internal match values for a single rule
/// </summary>
/// <param name="columnType"></param>
public void InitializeInternalMatchValues(MetaColumnType columnType)
{
OpCode = ConvertOpNameToCode(Op);
bool calculateEpsilonFromCfValue = false; // if true use cf value (note: may not be same number of decimals as output format)
Epsilon = 0;
if (MetaColumn.IsNumericMetaColumnType(columnType) && !String.IsNullOrEmpty(Value))
{
double.TryParse(Value, out ValueNumber);
if (calculateEpsilonFromCfValue)
{
Epsilon = MobiusDataType.GetEpsilon(Value);
}
else
{
int decimals = 10; // use default epsilon value
Epsilon = MobiusDataType.GetEpsilon(decimals);
}
}
else if (columnType == MetaColumnType.Date && !String.IsNullOrEmpty(Value))
{
ValueNormalized = DateTimeMx.Normalize(Value);
}
if (MetaColumn.IsNumericMetaColumnType(columnType) && !String.IsNullOrEmpty(Value2))
{
double.TryParse(Value2, out Value2Number);
double e2 = MobiusDataType.GetEpsilon(Value2);
if (e2 < Epsilon)
{
Epsilon = e2;
}
}
else if (columnType == MetaColumnType.Date && !String.IsNullOrEmpty(Value2))
{
Value2Normalized = DateTimeMx.Normalize(Value2);
}
}
public static CondFormat CreateAndInitializeCf(
MetaColumnType mcType)
{
CondFormat cf = null;
if (MetaColumn.IsNumericMetaColumnType(mcType) ||
mcType == MetaColumnType.String ||
mcType == MetaColumnType.Date)
{
cf = CondFormat.BuildDefaultConditionalFormatting();
}
else
{
cf = new CondFormat();
cf.Rules.Add(new CondFormatRule()); // initial rule
}
cf.ColumnType = mcType; // be sure type is set
return(cf);
}
/// <summary>
/// Set values derived from primary values
/// </summary>
public void SetDerivedValuesWithException()
{
MetaColumnType firstColumnType = MetaColumnType.Unknown;
MetaColumnType column2Type = MetaColumnType.Unknown;
List <MetaColumn> inputMetaColumns = GetInputMetaColumnList();
if (SourceColumnType == MetaColumnType.Unknown)
{
SourceColumnType = MetaColumnType.Number; // default type
}
if (CalcType == CalcTypeEnum.Basic)
{
if (MetaColumn1 != null)
{
SourceColumnType = MetaColumn1.DataType;
}
if (!String.IsNullOrEmpty(Operation))
{
OpEnum = ConvertCalcOpStringToEnum(ref Operation);
}
else
{
OpEnum = CalcOpEnum.None;
}
for (int ci = 0; ci < CfCols.Count; ci++)
{
CalcFieldColumn cfc = CfCols[ci];
SetDerivedColumnValues(SourceColumnType, cfc); // set the result type for the col
if (ci == 0)
{
firstColumnType = cfc.ResultColumnType;
}
else if (ci == 1)
{
column2Type = cfc.ResultColumnType;
}
if (ci > 0 && cfc.MetaColumn != null &&
MetaColumn1 != null && OpEnum != CalcOpEnum.None)
{ // be sure column types are compatible if binary op
if (!MetaColumn.AreCompatibleMetaColumnTypes(Column1.ResultColumnType, cfc.ResultColumnType))
{
throw new Exception("Incompatible column types");
}
if (MetaColumn.IsNumericMetaColumnType(MetaColumn1.DataType))
{
} // all operations allowed
else if (MetaColumn1.DataType == MetaColumnType.Date)
{
if (OpEnum != CalcOpEnum.Sub)
{
throw new Exception("Invalid date operation");
}
}
else if (MetaColumn1.DataType == MetaColumnType.Image)
{
if (OpEnum != CalcOpEnum.Overlay)
{
throw new Exception("Invalid Curve/Image operation");
}
}
else
{
throw new Exception("Invalid operation");
}
}
}
// Determine the result type from the source column types, functions, operations and mapping
PreclassificationlResultType = MetaColumnType.Unknown;
// Set result type before any classification is applied
if (firstColumnType == MetaColumnType.Integer && // see if integer type output
(column2Type == MetaColumnType.Integer || OpEnum == CalcOpEnum.None) &&
(OpEnum == CalcOpEnum.None || OpEnum == CalcOpEnum.Add || OpEnum == CalcOpEnum.Sub))
{
PreclassificationlResultType = MetaColumnType.Integer;
}
else if (MetaColumn.IsNumericMetaColumnType(firstColumnType))
{
PreclassificationlResultType = MetaColumnType.Number;
}
else if (firstColumnType == MetaColumnType.Date)
{
if (Column1.FunctionEnum == CalcFuncEnum.None && OpEnum == CalcOpEnum.None)
{
PreclassificationlResultType = MetaColumnType.Date;
}
else
{
PreclassificationlResultType = MetaColumnType.Integer; // either days elapsed or difference in date
}
}
else
{
PreclassificationlResultType = firstColumnType;
}
if (Classification != null) // set the type info for the classification
{
Classification.ColumnType = PreclassificationlResultType;
}
}
else if (CalcType == CalcTypeEnum.Advanced) // user defines result type via input
{
if (PreclassificationlResultType == MetaColumnType.Unknown)
{
PreclassificationlResultType = MetaColumnType.Number; // default to number
}
}
// Set final result type
FinalResultType = PreclassificationlResultType; // default type without classification
if (IsClassificationDefined)
{ // if mapping to class then class names determine the type
FinalResultType = MetaColumnType.Integer; // start assuming integer type
foreach (CondFormatRule rule in Classification.Rules)
{
if (Lex.IsInteger(rule.Name))
{
continue;
}
else if (Lex.IsDouble(rule.Name))
{
FinalResultType = MetaColumnType.Number;
}
else
{
FinalResultType = MetaColumnType.String;
break;
}
}
}
return;
}
/// <summary>
/// Sort a dataset
/// </summary>
/// <param name="results">Data to sort</param>
/// <param name="sortColumns">Columns to sort on</param>
/// <param name="query">Query information with vo positions of sort cols</param>
/// <param name="keyValueVoPos">Index of added key value</param>
/// <param name="ResultKeys">Ordered keys are returned here</param>
/// <returns></returns>
///
public List <object[]> Sort(
List <object[]> results,
List <SortColumn> sortColumns,
Query query,
int keyValueVoPos,
out List <string> ResultKeys)
{
object [] vo, vo2;
int qti, ri, ti, sci, i1, i2, i3;
SortItem currentVal = null; // current top composite value (high or low) for current key
object value;
object [] sourceVo, destVo;
string key;
SortItem sItem = null, sItem2;
int keyOffset = 0; // pk is stored in first element of vo and must be offset
ValidateSortColumns(sortColumns);
// Get array of sort columns associated with each metatable. This will be
// used in the sort routine to do the local sorts in the context of
// each table
bool[][] mtSortCols = new bool[query.Tables.Count][];
MetaTableSortItems mtsi;
int mtsiIdx;
int firstQtWithSorting = -1;
for (sci = 0; sci < sortColumns.Count; sci++)
{
QueryColumn qc = sortColumns[sci].QueryColumn;
for (qti = 0; qti < query.Tables.Count; qti++)
{
// if (Query.Tables[qti] == qc.QueryTable) break; // (fails for preview)
if (Lex.Eq((query.Tables[qti]).MetaTable.Name, qc.QueryTable.MetaTable.Name))
{
break; // todo: properly handle when same metatable in query multiple times
}
}
if (qti >= query.Tables.Count)
{
throw new Exception("Sort column not found in table");
}
if (mtSortCols[qti] == null) // allocate array first time
{
mtSortCols[qti] = new bool[sortColumns.Count];
}
mtSortCols[qti][sci] = true;
if (firstQtWithSorting < 0)
{
firstQtWithSorting = qti;
}
}
MultiColumnSortComparer sortComp = // sort including context of 1st table with sorting
new MultiColumnSortComparer(sortColumns, mtSortCols[firstQtWithSorting]);
// Build the array of values to be sorted. Each element of the array consists of
// one or more primary values (i.e. the max or min value for each compound id
// depending on the SortDirection for the column), the compound id and
// one or more secondary values (i.e. the actual value for the sort key for that row)
string currentKey = "";
int firstRowForKey = -1, rowsForKey;
List <object> sArray = new List <object>();
object[] voResults = null;
for (ri = 0; ri < results.Count; ri++)
{
vo = (object [])results[ri];
key = (string)vo[keyValueVoPos];
sItem = new SortItem();
sItem.SortValue = new IComparable[sortColumns.Count];
sItem.SortValueB = new IComparable[sortColumns.Count];
sArray.Add(sItem);
sItem.Key = key;
sItem.TupleIndex = ri;
for (sci = 0; sci < sortColumns.Count; sci++)
{ // copy the primary & secondary values from result buffer into sort value array
QueryColumn qc = sortColumns[sci].QueryColumn;
SortOrder direction = sortColumns[sci].Direction;
if (qc.IsKey) // if key be sure to get non-null value
{
sItem.SortValue[sci] = sItem.SortValueB[sci] = key;
}
else // copy non-key values
{
object o = null;
if (qc.VoPosition >= 0 && qc.VoPosition < vo.Length) // be sure in range
{
o = vo[qc.VoPosition]; // get primitive or Mobius data type
}
else if (NullValue.IsNull(o))
{
o = null;
}
else if (o is MobiusDataType) // convert Mobius types to a comparable primitive type
{
if (o is NumberMx)
{
o = (o as NumberMx).Value;
}
else if (o is QualifiedNumber)
{
QualifiedNumber qn = o as QualifiedNumber;
if (MetaColumn.IsNumericMetaColumnType(qc.MetaColumn.DataType))
{
o = qn.NumberValue;
}
else if (qc.MetaColumn.DataType == MetaColumnType.String)
{
o = qn.TextValue;
}
else
{
o = qn.NumberValue; // shouldn't happen
}
}
else if (o is StringMx)
{
o = (o as StringMx).Value;
}
else if (o is DateTimeMx)
{
o = (o as DateTimeMx).Value;
}
else if (o is CompoundId)
{
o = (o as CompoundId).Value;
}
}
else if (o is byte || o is sbyte || // convert all numbers to doubles so they compare properly (e.g. int that has cond formatting NumberMx values for some rows)
o is Int16 || o is Int32 || o is Int64 ||
o is float || o is decimal)
{
o = Convert.ToDouble(o);
}
if (!(o is IComparable))
{
o = null; // be sure it's a IComparable
}
sItem.SortValue[sci] = sItem.SortValueB[sci] = (IComparable)o;
}
}
if (key != null && key != currentKey) // new key value
{
firstRowForKey = ri;
currentKey = key;
currentVal = sItem;
}
else // another tuple for same key
{
if (sortComp.CompareSortItems(currentVal, sItem) > 0) // new primary value?
{
currentVal = sItem;
for (i1 = firstRowForKey; i1 < ri; i1++)
{ // reset primary value for preceeding rows for this key to the value of this row
sItem2 = (SortItem)sArray[i1];
for (sci = 0; sci < sortColumns.Count; sci++)
{
sItem2.SortValue[sci] = currentVal.SortValue[sci];
}
}
}
else // copy current primary values to this sort item
{
for (sci = 0; sci < sortColumns.Count; sci++)
{
sItem.SortValue[sci] = currentVal.SortValue[sci];
}
}
}
}
// Do the first sort which is overall for key order & relative for
// first table with sort columns
sArray.Sort(sortComp);
// Reorder arraylist of raw tuples according to initial search results
List <object[]> newResults = new List <object[]>(results.Count);
for (ri = 0; ri < sArray.Count; ri++)
{
sItem = (SortItem)sArray[ri];
newResults.Add(results[sItem.TupleIndex]);
}
// Setup range in Vo for each query table and its key
List <SortTableData> std = new List <SortTableData>();
foreach (QueryTable qt0 in query.Tables)
{
SortTableData st = new SortTableData();
std.Add(st);
foreach (QueryColumn qc0 in qt0.QueryColumns)
{
MetaColumn mc = qc0.MetaColumn;
if (mc.IsKey)
{
st.KeyColPos = qc0.VoPosition;
}
if (qc0.VoPosition >= 0 && st.FirstColumn < 0)
{
st.FirstColumn = qc0.VoPosition;
}
if (qc0.Selected)
{
st.SelectCount++;
}
}
}
// Do table-relative sort for other tables with sort fields
ArrayList CopyBuf = new ArrayList(); // used for copying
int voLen = 0; // length of vo
if (results != null && results.Count > 0 && results[0] != null)
{
voLen = ((object [])results[0]).Length;
}
for (ti = 0; ti < std.Count; ti++) // check all tables
{
int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table
int voTableLen = std[ti].SelectCount;
if (ti == firstQtWithSorting)
{
continue; // did this table first
}
else if (mtSortCols[ti] == null)
{
continue; // no sorting on this one
}
sortComp = // proper sort comparer for this table
new MultiColumnSortComparer(sortColumns, mtSortCols[ti]);
sArray.Sort(sortComp); // re-sort original results for this table
currentKey = "";
firstRowForKey = -1;
rowsForKey = 0;
for (ri = 0; ri <= sArray.Count; ri++) // reorder table vo entries for new results
{
if (ri < sArray.Count)
{
sItem = (SortItem)sArray[ri];
vo = (object [])results[sItem.TupleIndex]; // address the vo
key = (string)vo[keyValueVoPos];
}
else
{
key = "<end>"; // past end of data, force copy back of last chunk
}
if (key != null && key != currentKey) // new key value
{
if (firstRowForKey >= 0) // anything to copy back
{
for (i1 = 0; i1 < rowsForKey; i1++)
{ // copy back to results buffer in proper order
sourceVo = (Object[])CopyBuf[i1];
destVo = (Object[])newResults[firstRowForKey + i1];
Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen);
}
}
if (ri >= sArray.Count)
{
break; // really done?
}
currentKey = key;
firstRowForKey = ri; // first row in newResults for the key
rowsForKey = 0;
}
if (CopyBuf.Count < rowsForKey + 1) // be sure copy buffer big enough
{
CopyBuf.Add(new object[voLen]);
}
sourceVo = (Object[])results[sItem.TupleIndex]; // copy from original results row to buffer
destVo = (Object[])CopyBuf[rowsForKey];
Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen);
rowsForKey++;
} // end of loop on results entries
} // end of loop on query tables
// For each key shift non-null metatable data to top of section for key
ResultKeys = new List <string>(); // reorder result keys also
currentKey = "";
for (ri = 0; ri < newResults.Count; ri++)
{
vo = (object [])newResults[ri];
key = (string)vo[keyValueVoPos];
if (key != null && key != currentKey) // new key value
{
firstRowForKey = ri;
currentKey = key;
ResultKeys.Add(key);
}
//else // another row for key, shift up data as needed to fill gaps
{
if (query.Tables.Count <= 1)
{
continue; // only need to do if more than one table
}
for (ti = 0; ti < std.Count; ti++) // check all tables (sorted tables should be ok except for root table is sorted on key
{
int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table
int voTableLen = std[ti].SelectCount;
if (NullValue.IsNull(vo[voTablePos]))
{
continue; // skip if null data for this table (i.e. key is null)
}
for (i2 = firstRowForKey; i2 < ri; i2++) // look for empty slot
{
vo2 = (object [])newResults[i2];
object o = vo2[voTablePos];
if (!NullValue.IsNull(o))
{
continue; // already full
}
Array.Copy(vo, voTablePos, vo2, voTablePos, voTableLen);
Array.Clear(vo, voTablePos, voTableLen);
break;
}
}
}
}
return(newResults); // substitute new ordered set of results
}