/// <summary> /// Initializes a new instance of the <see cref="ColumnFilterLogic" /> class. /// </summary> /// <param name="columnDataType">Type of the column data.</param> /// <param name="dataPropertyName">Name of the data property.</param> public ColumnFilterLogic(Type columnDataType, string dataPropertyName) { Contract.Requires(columnDataType != null); Contract.Requires(dataPropertyName != null); var dataPropertyName1 = dataPropertyName; // Un-escape the name again if (dataPropertyName.StartsWith("[", StringComparison.Ordinal) && dataPropertyName.EndsWith("]", StringComparison.Ordinal)) { dataPropertyName1 = dataPropertyName.Substring(1, dataPropertyName.Length - 2).Replace(@"\]", "]") .Replace(@"\\", @"\"); } m_DataPropertyNameEscape = $"[{StringUtilsSQL.SqlName(dataPropertyName1)}]"; m_ColumnDataType = columnDataType; // m_ValueClusterCollection.CollectionChanged += delegate(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e) { FilterChanged(); }; }
/// <summary> /// Builds the value clusters date. /// </summary> /// <param name="dataTable">The data view.</param> /// <param name="columnIndex">Index of the column.</param> /// <param name="maxNumber">The maximum number.</param> /// <param name="columnType">Type of the column.</param> /// <returns></returns> private BuildValueClustersResult BuildValueClustersNumeric(DataTable dataTable, int columnIndex, int maxNumber, Type columnType) { // Get the distinct values and their counts var clusterFractions = new HashSet <double>(); var clusterOne = new HashSet <long>(); var clusterTen = new HashSet <long>(); var clusterHundered = new HashSet <long>(); var clusterThousand = new HashSet <long>(); var columnName = dataTable.Columns[columnIndex].ColumnName; foreach (DataRow dataRow in dataTable.Rows) { if (dataRow[columnIndex] == DBNull.Value) { clusterFractions.Add(int.MinValue); clusterOne.Add(int.MinValue); clusterTen.Add(int.MinValue); clusterHundered.Add(int.MinValue); } else { if (columnType == typeof(decimal) || columnType == typeof(float) || columnType == typeof(double)) { var rounded = Math.Floor(Convert.ToDouble(dataRow[columnIndex], CultureInfo.CurrentCulture) * 10d) / 10d; clusterFractions.Add(rounded); } var key = Convert.ToInt64(dataRow[columnIndex], CultureInfo.CurrentCulture); clusterOne.Add(key); clusterTen.Add(key / 10); clusterHundered.Add(key / 100); clusterThousand.Add(key / 1000); } // if we have more than the maximum entries stop, no value filter will be used if (clusterThousand.Count <= maxNumber) { continue; } m_ValueClusters.Clear(); return(BuildValueClustersResult.TooManyValues); } if (clusterOne.Count == 0 && clusterFractions.Count == 0) { m_ValueClusters.Clear(); return(BuildValueClustersResult.NoValues); } var colNameEsc = $"[{StringUtilsSQL.SqlName(columnName)}]"; if (clusterFractions.Count < maxNumber && clusterFractions.Count > 0) { m_Type = ValueClustersGroupType.NumericFraction; foreach (var dic in clusterFractions) { if (Math.Abs(dic - int.MinValue) > .1) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString(CultureInfo.CurrentCulture), // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic, dic + .1), Sort = (dic * 10d).ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterOne.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericOnes; foreach (var dic in clusterOne) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString("D", CultureInfo.CurrentCulture), // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic, dic + 1), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterTen.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericTens; foreach (var dic in clusterTen) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 10} - {dic * 10 + 9}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 10, (dic + 1) * 10), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterHundered.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericHundreds; foreach (var dic in clusterHundered) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 100} - {dic * 100 + 99}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 100, (dic + 1) * 100), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else { m_Type = ValueClustersGroupType.NumericThousands; foreach (var dic in clusterThousand) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 1000} - {dic * 1000 + 999}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 1000, (dic + 1) * 1000), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } return(BuildValueClustersResult.ListFilled); }