/// <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);
}
/// <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>
/// <returns></returns>
private BuildValueClustersResult BuildValueClustersDate(DataTable dataTable, int columnIndex, int maxNumber)
{
// Get the distinct values and their counts
var clusterYear = new HashSet <int>();
var clusterMonth = new HashSet <DateTime>();
var clusterDay = new HashSet <DateTime>();
var clusterHour = new HashSet <long>();
var columnName = dataTable.Columns[columnIndex].ColumnName;
foreach (DataRow dataRow in dataTable.Rows)
{
int keyYear;
DateTime keyMonth;
DateTime keyDate;
long keyHour;
if (dataRow[columnIndex] == DBNull.Value)
{
keyYear = 0;
keyHour = 0;
keyMonth = DateTime.MinValue;
keyDate = DateTime.MinValue;
}
else
{
var value = (DateTime)dataRow[columnIndex];
keyHour = GetHourKey(value);
keyDate = value.Date;
keyYear = GetYearKey(value);
keyMonth = GetMonthKey(value);
}
clusterHour.Add(keyHour);
clusterDay.Add(keyDate);
clusterMonth.Add(keyMonth);
clusterYear.Add(keyYear);
// if we have more than the maximum entries stop, no value filter will be used
if (clusterYear.Count <= maxNumber)
{
continue;
}
m_ValueClusters.Clear();
return(BuildValueClustersResult.TooManyValues);
}
if (clusterYear.Count == 0)
{
m_ValueClusters.Clear();
return(BuildValueClustersResult.NoValues);
}
if (clusterDay.Count == 1)
{
m_Type = ValueClustersGroupType.DateHours;
foreach (var dic in clusterHour)
{
if (dic != 0)
{
var from = StringConversion.GetTimeFromTicks(dic * c_TicksPerGroup);
var to = StringConversion.GetTimeFromTicks((dic + 1) * c_TicksPerGroup);
m_ValueClusters.Add(new ValueCluster
{
Display = $"{from:t} - {to:t}",
SQLCondition = string.Format(CultureInfo.InvariantCulture,
@"({0} >= #{1:MM\/dd\/yyyy HH:mm}# AND {0} < #{2:MM\/dd\/yyyy HH:mm}#)",
StringUtilsSQL.SqlNameSafe(columnName), from, to),
Sort = GetHourSort(dic)
});
}
else
{
m_ValueClusters.Add(new ValueCluster
{
Display = ColumnFilterLogic.cOPisNull,
SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)",
StringUtilsSQL.SqlNameSafe(columnName)),
Sort = string.Empty
});
}
}
}
else if (clusterDay.Count < maxNumber)
{
m_Type = ValueClustersGroupType.DateDay;
foreach (var dic in clusterDay)
{
if (dic != DateTime.MinValue)
{
m_ValueClusters.Add(new ValueCluster
{
Display = dic.ToString("d", CultureInfo.CurrentCulture),
SQLCondition = string.Format(CultureInfo.InvariantCulture,
@"({0} >= #{1:MM\/dd\/yyyy}# AND {0} < #{2:MM\/dd\/yyyy}#)", StringUtilsSQL.SqlNameSafe(columnName),
dic, dic.AddDays(1)),
Sort = GetDaySort(dic)
});
}
else
{
m_ValueClusters.Add(new ValueCluster
{
Display = ColumnFilterLogic.cOPisNull,
SQLCondition =
string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)),
Sort = string.Empty
});
}
}
}
else if (clusterMonth.Count < maxNumber)
{
m_Type = ValueClustersGroupType.DateMonth;
foreach (var dic in clusterMonth)
{
if (dic != DateTime.MinValue)
{
m_ValueClusters.Add(new ValueCluster
{
Display = dic.ToString("Y", CultureInfo.CurrentCulture), // Year month pattern
SQLCondition = string.Format(CultureInfo.InvariantCulture,
@"({0} >= #{1:MM\/dd\/yyyy}# AND {0} < #{2:MM\/dd\/yyyy}#)", StringUtilsSQL.SqlNameSafe(columnName),
dic, dic.AddMonths(1)),
Sort = GetMonthSort(dic)
});
}
else
{
m_ValueClusters.Add(new ValueCluster
{
Display = ColumnFilterLogic.cOPisNull,
SQLCondition =
string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)),
Sort = string.Empty
});
}
}
}
else
{
m_Type = ValueClustersGroupType.DateYear;
foreach (var dic in clusterYear)
{
if (dic != 0)
{
m_ValueClusters.Add(new ValueCluster
{
Display = dic.ToString("D", CultureInfo.CurrentCulture), // Decimal
SQLCondition = string.Format(CultureInfo.InvariantCulture,
"({0} >= #01/01/{1:d4}# AND {0} < #01/01/{2:d4}#)", StringUtilsSQL.SqlNameSafe(columnName), dic,
dic + 1),
Sort = GetYearSort(dic)
});
}
else
{
m_ValueClusters.Add(new ValueCluster
{
Display = ColumnFilterLogic.cOPisNull,
SQLCondition =
string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)),
Sort = string.Empty
});
}
}
}
return(BuildValueClustersResult.ListFilled);
}
