/// <summary>
/// Produces a table where each row is the number of unique values in a source column, followed by the top N occurences in that column.
/// </summary>
/// <param name="table">source table</param>
/// <param name="N">number of top N occurences to include in the summary table </param>
/// <returns>a summary table</returns>
public static MutableDataTable GetColumnValueCounts(DataTable table, int N)
{
if (N < 0)
{
throw new ArgumentOutOfRangeException("N");
}
string[] names = table.ColumnNames.ToArray();
int count = names.Length;
MutableDataTable dSummary = new MutableDataTable();
Column c1 = new Column("column name", count);
Column c2 = new Column("count", count);
int kFixed = 2;
Column[] cAll = new Column[kFixed + N * 2];
cAll[0] = c1;
cAll[1] = c2;
for (int i = 0; i < N; i++)
{
cAll[i * 2 + kFixed] = new Column("Top Value " + i, count);
cAll[i * 2 + 1 + kFixed] = new Column("Top Occurrence " + i, count);
}
dSummary.Columns = cAll;
int columnId = 0;
foreach (string name in names)
{
Tuple<string, int>[] hist = AsHistogram(table, columnId);
c1.Values[columnId] = name;
c2.Values[columnId] = hist.Length.ToString();
for (int i = 0; i < N; i++)
{
if (i >= hist.Length)
{
break;
}
cAll[i * 2 + kFixed].Values[columnId] = hist[i].Item1;
cAll[i * 2 + 1 + kFixed].Values[columnId] = hist[i].Item2.ToString();
}
columnId++;
}
return dSummary;
}
static Dictionary<string, int> GetRowIndex(Column c)
{
Dictionary<string, int> d = new Dictionary<string, int>();
for (int row = 0; row < c.Values.Length; row++)
{
string x = c.Values[row].ToUpperInvariant();
// If this add fails, it means the column we're doing a join on has duplicate entries.
d.Add(x, row); // verifies uniqueness
}
return d;
}
public ColumnDTO ToColumnDTO(DataAccess.Column item, bool simple)
{
var cachedItem = CacheManager.GetCacheManager().GetCachedItem(CacheItemType.Column, item.ID.ToString(), simple.ToString());
if (cachedItem != null)
{
return(cachedItem as ColumnDTO);
}
ColumnDTO result = new ColumnDTO();
result.Name = item.Name;
result.DataType = item.DataType;
result.ID = item.ID;
result.TableID = item.TableID;
result.IsNull = item.IsNull;
result.PrimaryKey = item.PrimaryKey;
result.ColumnType = (Enum_ColumnType)item.TypeEnum;
result.OriginalColumnType = (Enum_ColumnType)item.OriginalTypeEnum;
if (!string.IsNullOrEmpty(item.Alias))
{
result.Alias = item.Alias;
}
else
{
result.Alias = item.Name;
}
if (item.RelationshipColumns.Any())
{
result.ForeignKey = item.RelationshipColumns.Any(x => x.Relationship.Removed != true && x.Relationship.MasterTypeEnum == (int)Enum_MasterRelationshipType.FromForeignToPrimary);
}
result.DataEntryEnabled = item.DataEntryEnabled;
result.DefaultValue = item.DefaultValue;
result.IsMandatory = item.IsMandatory;
result.IsIdentity = item.IsIdentity;
result.Position = (item.Position == null ? 0 : item.Position.Value);
result.IsDisabled = item.IsDisabled;
result.IsNotTransferable = item.IsNotTransferable;
result.DBFormula = item.DBCalculateFormula;
// result.IsDBCalculatedColumn = !string.IsNullOrEmpty(result.DBFormula);
result.IsReadonly = item.IsReadonly;
result.DotNetType = GetColumnDotNetType(item.DataType, item.IsNull);
if (item.ColumnCustomFormula != null)
{
result.CustomFormulaName = item.ColumnCustomFormula.Formula.Name;
}
if (!simple)
{
if (item.StringColumnType != null)
{
result.StringColumnType = ToStringColumTypeDTO(item.StringColumnType);
}
if (item.NumericColumnType != null)
{
result.NumericColumnType = ToNumericColumTypeDTO(item.NumericColumnType);
}
if (item.DateColumnType != null)
{
result.DateColumnType = ToDateColumTypeDTO(item.DateColumnType);
}
if (item.TimeColumnType != null)
{
result.TimeColumnType = ToTimeColumTypeDTO(item.TimeColumnType);
}
if (item.DateTimeColumnType != null)
{
result.DateTimeColumnType = ToDateTimeColumTypeDTO(item.DateTimeColumnType);
}
BizColumnValueRange bizColumnValueRange = new MyModelManager.BizColumnValueRange();
if (item.ColumnValueRange != null)
{
result.ColumnValueRange = bizColumnValueRange.ToColumnValueRangeDTO(item.ColumnValueRange, true);
}
if (item.ColumnCustomFormula != null)
{
result.ColumnCustomFormula = ToColumnCustomFormulaDTO(item.ColumnCustomFormula);
}
}
CacheManager.GetCacheManager().AddCacheItem(result, CacheItemType.Column, item.ID.ToString(), simple.ToString());
return(result);
}
private static MutableDataTable ReadArray(IList<string> lines, char separator, bool fAllowMismatch = false, string[] defaultColumns = null)
{
if (separator == '\0')
{
separator = GuessSeparateFromHeaderRow(lines[0]);
}
int numRows = lines.Count - (defaultColumns != null ? 0 : 1);
// First row is a header only if we dont pass defaultColumns
// if defaultColumns is not null then we use them as columns
string[] names = defaultColumns ?? split(lines[0], separator);
int numColumns = names.Length;
var columns = new Column[numColumns];
for (int i = 0; i < numColumns; i++) {
columns[i] = new Column(names[i], numRows);
}
// Parse each row into data set
using (var lineEnumerator = lines.GetEnumerator())
{
if (defaultColumns == null)
{
lineEnumerator.MoveNext(); // in this case we have columns at first index
}
var row = -1;
while(lineEnumerator.MoveNext())
{
string line = lineEnumerator.Current;
row++;
string[] parts = split(line, separator);
if (parts.Length < numColumns) {
// Deal with possible extra commas at the end.
// Excel handles this.
for (int c = 0; c < parts.Length; c++) {
columns[c].Values[row] = parts[c];
}
if (fAllowMismatch)
{
for (int c = parts.Length; c < numColumns; c++)
{
columns[c].Values[row] = String.Empty;
}
continue;
}
}
if (!fAllowMismatch) {
// If mismatch allowed, then treat this row as garbage rather
// than throw an exception
Utility.Assert(
parts.Length == names.Length,
String.Format(
"Allow Mismatch is False. Line has incorrect number of parts. Line Number:{0}; Expected:{1}; Actual:{2}",
row + 1,
names.Length,
parts.Length));
}
for (int c = 0; c < numColumns; c++) {
columns[c].Values[row] = parts[c];
}
}
}
MutableDataTable data = new MutableDataTable();
data.Columns = columns;
return data;
}
private static MutableDataTable ReadArray(IList<string> lines, char separator, bool fAllowMismatch = false)
{
if (separator == '\0')
{
separator = GuessSeparateFromHeaderRow(lines[0]);
}
int numRows = lines.Count - 1;
// First row is a header
string[] names = split(lines[0], separator);
int numColumns = names.Length;
var columns = new Column[numColumns];
for (int i = 0; i < numColumns; i++) {
columns[i] = new Column(names[i], numRows);
}
// Parse each row into data set
for (int i = 1; i < lines.Count; i++) {
string line = lines[i];
int row = i - 1;
string[] parts = split(line, separator);
if (parts.Length < numColumns) {
// Deal with possible extra commas at the end.
// Excel handles this.
for (int c = 0; c < parts.Length; c++) {
columns[c].Values[row] = parts[c];
}
for (int c = parts.Length; c < numColumns; c++) {
columns[c].Values[row] = String.Empty;
}
continue;
}
if (!fAllowMismatch) {
// If mismatch allowed, then treat this row as garbage rather
// than throw an exception
Utility.Assert(parts.Length == names.Length);
}
for (int c = 0; c < numColumns; c++) {
columns[c].Values[row] = parts[c];
}
}
MutableDataTable data = new MutableDataTable();
data.Columns = columns;
return data;
}
// Create discrete value for the given column
public DiscreteValueKind(Column c)
{
m_label = c.Name;
AddValues(c.Values);
}