/// <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 }