public void Setup()
{
int length = 50000;
_dataFrame = new DataFrame();
_column = new PrimitiveColumn <int>("Int0", Enumerable.Range(0, length));
_string = new StringColumn("String", Enumerable.Range(0, length).Select(x => x.ToString()));
_bool = new PrimitiveColumn <bool>("Bool", Enumerable.Range(0, length).Select(x => x % 2 == 0 ? true : false));
_dataFrame.InsertColumn(0, _column);
_dataFrame.InsertColumn(1, _string);
_dataFrame.InsertColumn(2, _bool);
_otherDataFrame = new DataFrame();
_otherColumn = new PrimitiveColumn <int>("Int0", Enumerable.Range(0, length / 2));
_otherString = new StringColumn("String", Enumerable.Range(0, length / 2).Select(x => x.ToString()));
_otherBool = new PrimitiveColumn <bool>("Bool", Enumerable.Range(0, length / 2).Select(x => x % 2 == 0 ? true : false));
_otherDataFrame.InsertColumn(0, _otherColumn);
_otherDataFrame.InsertColumn(1, _otherString);
_otherDataFrame.InsertColumn(2, _otherBool);
}
private void RenderHeaderText(HtmlTextWriter writer)
{
if (string.IsNullOrEmpty(Header))
{
writer.Write("Empty header");
}
else
{
writer.Write(HttpUtility.HtmlEncode(Header));
}
if (BaseColumn != null && BaseColumn.CustomRenderInHeader != null)
{
BaseColumn.CustomRenderInHeader(this, writer);
}
else if (Childs.Count == 1 && Childs[0].HideInHeader && Childs[0].BaseColumn != null && Childs[0].BaseColumn.CustomRenderInHeader != null)
{
Childs[0].BaseColumn.CustomRenderInHeader(this, writer);
}
}
public RenderContext GetRenderContextFor(BaseColumn column)
{
string columnName;
if (string.IsNullOrEmpty(CrossColumnId))
{
columnName = column.ColumnName;
}
else
{
var length = ColumnHierarchy.ColumnKey.Length - Column.ColumnName.Length - CrossColumnId.Length - 1;
columnName = ColumnHierarchy.ColumnKey.Substring(0, length) + column.ColumnName + "_" + CrossColumnId;
}
if (!OtherColumns.ContainsKey(columnName))
{
throw new ArgumentException("RenderContext does not contain column with name " + columnName);
}
return(OtherColumns[columnName]);
}
private static decimal GetSumOf(RenderContext context, BaseColumn c, Func <RenderContext, BaseColumn, bool> where)
{
if (!c.IsCrossColumn)
{
if (where == null || where (context, c))
{
return(Convert.ToDecimal(c.GetValueByContext(context)));
}
return(0);
}
var contexts = c.BaseCrossColumnDataSource.GetCrossColumnNames().Select(columnName => context.OtherColumns[columnName]);
var crossColumnNames = where == null
? contexts.ToList()
: contexts.Where(renderContext => where (renderContext, renderContext.Column)).ToList();
if (crossColumnNames.Count == 0)
{
return(0);
}
return(crossColumnNames.Sum(renderContext => Convert.ToDecimal(renderContext.Column.GetValue(renderContext))));
}
public void processColumn(Column col, int i, BaseColumn[] ChartColumns)
{
ChartColumns[i] = new BaseColumn();
switch (col.Context)
{
case "Nominal": { Column <string> column = new Column <string> {
data = processNominalColumn(col), dataType = new Nominal {
dataType = "Nominal", type = "string"
}, columnHeader = col.Heading
}; ChartColumns[i] = column; break; }
case "Numeric": { Column <double> column = new Column <double> {
data = processNumericColumn(col), dataType = new Numeric {
dataType = "Numeric", type = "double"
}, columnHeader = col.Heading
}; ChartColumns[i] = column; break; }
case "Percentage": { Column <double> column = new Column <double> {
data = processNumericColumn(col), dataType = new Numeric {
dataType = "Percentage", type = "double"
}, columnHeader = col.Heading
}; ChartColumns[i] = column; break; }
case "Time series": { Column <string> column = new Column <string> {
data = processDateColumn(col), dataType = new FYP_MVC.Models.CoreObjects.DateTime {
dataType = "TimeSeries", type = "string"
}, columnHeader = col.Heading
}; ChartColumns[i] = column; break; }
case "Location": { Column <string> column = new Column <string> {
data = processLocationColumn(col), dataType = new FYP_MVC.Models.CoreObjects.Location {
dataType = "Location", type = "string", region = col.Region, resolution = col.Resolution
}, columnHeader = col.Heading
}; ChartColumns[i] = column; break; }
}
}
/// <summary>
/// This method takes a record and a Column and returns an evaluated value of DFKA formula.
/// </summary>
public static string GetDFKA(BaseRecord rec, BaseColumn col)
{
ForeignKey fkColumn = AreasTable.Instance.TableDefinition.GetExpandableNonCompositeForeignKey(col);
if (fkColumn == null)
{
return(null);
}
String _DFKA = fkColumn.PrimaryKeyDisplayColumns;
if (_DFKA.Trim().StartsWith("="))
{
// if the formula is in the format of "= <Primary table>.<Field name>, then pull out the data from the rec object instead of doing formula evaluation
string tableCodeName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
string column = _DFKA.Trim('=').Trim();
if (column.StartsWith(tableCodeName + ".", StringComparison.InvariantCultureIgnoreCase))
{
column = column.Substring(tableCodeName.Length + 1);
}
foreach (BaseColumn c in fkColumn.PrimaryKeyTableDefinition.Columns)
{
if (column == c.CodeName)
{
return(rec.Format(c));
}
}
String tableName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
return(EvaluateFormula(_DFKA, rec, null, tableName));
}
else
{
return(null);
}
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="name">name of the column to fetch.</param>
public static BaseColumn GetColumnByName(string name)
{
BaseColumn column = View_FieldTripStatsView.Instance.TableDefinition.ColumnList.GetByInternalName(name);
return(column);
}
/// <summary>
/// This method takes a keyValue and a Column and returns an evaluated value of DFKA formula.
/// </summary>
public static string GetDFKA(String keyValue, BaseColumn col, String formatPattern)
{
if (keyValue == null)
return null;
ForeignKey fkColumn = UsersTable.Instance.TableDefinition.GetExpandableNonCompositeForeignKey(col);
if (fkColumn == null)
return null;
String _DFKA = fkColumn.PrimaryKeyDisplayColumns;
if (_DFKA.Trim().StartsWith("="))
{
String tableName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
PrimaryKeyTable t = (PrimaryKeyTable)DatabaseObjects.GetTableObject(tableName);
BaseRecord rec = null;
if (t != null)
{
try
{
rec = (BaseRecord)t.GetRecordData(keyValue, false);
}
catch
{
rec = null;
}
}
if( rec == null)
return "";
// if the formula is in the format of "= <Primary table>.<Field name>, then pull out the data from the rec object instead of doing formula evaluation
string tableCodeName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
string column = _DFKA.Trim('=').Trim();
if (column.StartsWith(tableCodeName + ".", StringComparison.InvariantCultureIgnoreCase))
{
column = column.Substring(tableCodeName.Length + 1);
}
foreach (BaseColumn c in fkColumn.PrimaryKeyTableDefinition.Columns)
{
if (column == c.CodeName)
{
return rec.Format(c);
}
}
return EvaluateFormula(_DFKA, rec, null, tableName);
}
else
return null;
}
public static String[] GetValues(
BaseColumn col,
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int maxItems)
{
// Create the filter list.
SqlBuilderColumnSelection retCol = new SqlBuilderColumnSelection(false, true);
retCol.AddColumn(col);
return UsersTable.Instance.GetColumnValues(retCol, join, where.GetFilter(), null, orderBy, BaseTable.MIN_PAGE_NUMBER, maxItems);
}
/// <summary>
/// Sets a database record values with values retrieved from data file.
/// </summary>
/// <param name="rowValues"></param>
/// <param name="record"></param>
private bool UpdateColumnValuesInRecord(string[] rowValues, IRecord record, bool isResolvedForeignKeysChecked)
{
int j = 0;
bool isRecordUpdated = false;
ColumnCount = 1;
foreach (string data in rowValues)
{
ColumnCount++;
if (j > this.ImportList.Count - 1)
{
return(isRecordUpdated);
}
try
{
if (this.ColumnNameList[j].ToString() != "" && ((CheckBox)this.ImportList[j]).Checked)
{
ForeignKey fkColumn = null;
BaseColumn currentColumn = this.DBTable.TableDefinition.ColumnList.GetByAnyName((string)this.ColumnNameList[j]);
if (isResolvedForeignKeysChecked)
{
fkColumn = this.DBTable.TableDefinition.GetForeignKeyByColumnName(currentColumn.InternalName);
}
String colValue = "";
// Check if the foreign key has DFKA. If so, then check the calue from csv file agains the DFKA column in the parent/foreign key table.
// If a match is found retrieve its ID and set that as value to be insterted in the current table where you are adding records.
if (fkColumn != null)
{
TableDefinition originalTableDef = fkColumn.PrimaryKeyTableDefinition;
BaseTable originalBaseTable = originalTableDef.CreateBaseTable();
WhereClause wc = null;
ArrayList records = new ArrayList();
BaseColumn pkColumn = (BaseColumn)originalTableDef.PrimaryKey.Columns[0];//Index is zero because we handle only those tables which has single PK column not composite keys.
if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (!fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
wc = new WhereClause(originalTableDef.ColumnList.GetByAnyName(fkColumn.PrimaryKeyDisplayColumns), BaseFilter.ComparisonOperator.EqualsTo, data);
}
else if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
string primaryKeyDisplay = GetDFKA(fkColumn);
if (primaryKeyDisplay != null)
{
wc = new WhereClause(originalTableDef.ColumnList.GetByAnyName(primaryKeyDisplay), BaseFilter.ComparisonOperator.EqualsTo, data);
}
else
{
wc = new WhereClause(pkColumn, BaseFilter.ComparisonOperator.EqualsTo, data);
}
}
else
{
// if the foreign key does not have DFKA then just check in the foreign key table if the id exists. If not create a record with the specified ID
// before adding to current table
wc = new WhereClause(pkColumn, BaseFilter.ComparisonOperator.EqualsTo, data);
}
BaseClasses.Data.BaseFilter join = null;
records = originalBaseTable.GetRecordList(join, wc.GetFilter(), null, null, 0, 100);
if (records.Count > 0)
{
// take the first record and retrieve its ID.
BaseRecord rec = (BaseRecord)records[0];
colValue = (rec.GetValue(pkColumn)).ToString();
}
else
{
// IF there is not match found then you have to create a record in the foreign key table with DFKA value and then retreive its ID
if (data != null & data != "")
{
IRecord tempRec;
if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (!fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
tempRec = originalBaseTable.CreateRecord();
TableDefinition tableDef = originalBaseTable.TableDefinition;
foreach (BaseColumn newCol in tableDef.Columns)
{
if (fkColumn.PrimaryKeyDisplayColumns == newCol.InternalName)
{
tempRec.SetValue(data, newCol.UniqueName);
}
}
}
else
{
tempRec = originalBaseTable.CreateRecord(data);
}
tempRec.Save();
colValue = (tempRec.GetValue(pkColumn)).ToString();
}
// colValue = data;
}
}
else
{
colValue = data;
}
// set the table row's column for value
record.SetValue(colValue, currentColumn.UniqueName);
isRecordUpdated = true;
}
j++;
}
catch (Exception ex)
{
throw new Exception(ex.InnerException.Message);
}
}
return(isRecordUpdated);
}
public SplitDataToRowsInitializer(BaseColumn column, params Func <RenderContext, object>[] getValues)
{
this.column = column;
this.getValues = getValues;
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="name">name of the column to fetch.</param>
public static BaseColumn GetColumnByName(string name)
{
BaseColumn column = AreasTable.Instance.TableDefinition.ColumnList.GetByInternalName(name);
return(column);
}
private void VerifyJoin(DataFrame join, DataFrame left, DataFrame right, JoinAlgorithm joinAlgorithm)
{
PrimitiveColumn <long> mapIndices = new PrimitiveColumn <long>("map", join.RowCount);
for (long i = 0; i < join.RowCount; i++)
{
mapIndices[i] = i;
}
for (int i = 0; i < join.ColumnCount; i++)
{
BaseColumn joinColumn = join.Column(i);
BaseColumn isEqual;
if (joinAlgorithm == JoinAlgorithm.Left)
{
if (i < left.ColumnCount)
{
BaseColumn leftColumn = left.Column(i);
isEqual = joinColumn == leftColumn;
}
else
{
int columnIndex = i - left.ColumnCount;
BaseColumn rightColumn = right.Column(columnIndex);
BaseColumn compareColumn = rightColumn.Length <= join.RowCount ? rightColumn.Clone(numberOfNullsToAppend: join.RowCount - rightColumn.Length) : rightColumn.Clone(mapIndices);
isEqual = joinColumn == compareColumn;
}
}
else if (joinAlgorithm == JoinAlgorithm.Right)
{
if (i < left.ColumnCount)
{
BaseColumn leftColumn = left.Column(i);
BaseColumn compareColumn = leftColumn.Length <= join.RowCount ? leftColumn.Clone(numberOfNullsToAppend: join.RowCount - leftColumn.Length) : leftColumn.Clone(mapIndices);
isEqual = joinColumn == compareColumn;
}
else
{
int columnIndex = i - left.ColumnCount;
BaseColumn rightColumn = right.Column(columnIndex);
isEqual = joinColumn == rightColumn;
}
}
else if (joinAlgorithm == JoinAlgorithm.Inner)
{
if (i < left.ColumnCount)
{
BaseColumn leftColumn = left.Column(i);
isEqual = joinColumn == leftColumn.Clone(mapIndices);
}
else
{
int columnIndex = i - left.ColumnCount;
BaseColumn rightColumn = right.Column(columnIndex);
isEqual = joinColumn == rightColumn.Clone(mapIndices);
}
}
else
{
if (i < left.ColumnCount)
{
BaseColumn leftColumn = left.Column(i);
isEqual = joinColumn == leftColumn.Clone(numberOfNullsToAppend: join.RowCount - leftColumn.Length);
}
else
{
int columnIndex = i - left.ColumnCount;
BaseColumn rightColumn = right.Column(columnIndex);
isEqual = joinColumn == rightColumn.Clone(numberOfNullsToAppend: join.RowCount - rightColumn.Length);
}
}
for (int j = 0; j < join.RowCount; j++)
{
Assert.Equal(true, isEqual[j]);
}
}
}
public string GetDataForExport(BaseColumn col, BaseRecord rec)
{
String val = "";
if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col))
{
// Foreign Key column, so we will use DFKA and String type.
val = rec.Format(col);
}
else
{
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
break;
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
val = rec.Format(col);
break;
default:
val = rec.Format(col);
break;
}
}
return val;
}
/// <summary>
/// Cretes new ExcelColumn
/// </summary>
/// <param name="col">BaseColumn</param>
/// <param name="format">a format string from Excel's Format Cell menu. For example "dddd, mmmm dd, yyyy h:mm AM/PM;@", "#,##0.00"</param>
public ExcelColumn(BaseColumn col, string format)
{
DisplayColumn = col;
DisplayFormat = format;
}
public bool IsString(BaseColumn col)
{
if (col == null)
return false;
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
return false;
}
return true;
}
public ExportDataToCSV(BaseTable tbl, WhereClause wc, OrderBy orderBy, BaseColumn[] columns, String header)
: base(header)
{
data = new DataForExport(tbl, wc, orderBy, columns);
}
public bool IncludeInExport(BaseColumn col)
{
if (col == null)
return false;
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
// Skip - do nothing for these columns
return false;
}
return true;
}
public DataForExport(BaseTable tbl, WhereClause wc, OrderBy orderBy, BaseColumn[] columns, BaseFilter join)
{
this.DBTable = tbl;
this.SelectWhereClause = wc;
this.SelectOrderBy = orderBy;
this.SelectJoin = join;
if (columns != null)
ColumnList.AddRange(columns);
}
public BoundColumnBuilder(BaseColumn <T> column) : base(column)
{
}
public void TestComputations()
{
DataFrame df = MakeDataFrameWithAllColumnTypes(10);
df["Int"][0] = -10;
Assert.Equal(-10, df["Int"][0]);
df["Int"].Abs();
Assert.Equal(10, df["Int"][0]);
Assert.Throws <NotSupportedException>(() => df["Byte"].All());
Assert.Throws <NotSupportedException>(() => df["Byte"].Any());
Assert.Throws <NotSupportedException>(() => df["Char"].All());
Assert.Throws <NotSupportedException>(() => df["Char"].Any());
Assert.Throws <NotSupportedException>(() => df["Decimal"].All());
Assert.Throws <NotSupportedException>(() => df["Decimal"].Any());
Assert.Throws <NotSupportedException>(() => df["Double"].All());
Assert.Throws <NotSupportedException>(() => df["Double"].Any());
Assert.Throws <NotSupportedException>(() => df["Float"].All());
Assert.Throws <NotSupportedException>(() => df["Float"].Any());
Assert.Throws <NotSupportedException>(() => df["Int"].All());
Assert.Throws <NotSupportedException>(() => df["Int"].Any());
Assert.Throws <NotSupportedException>(() => df["Long"].All());
Assert.Throws <NotSupportedException>(() => df["Long"].Any());
Assert.Throws <NotSupportedException>(() => df["Sbyte"].All());
Assert.Throws <NotSupportedException>(() => df["Sbyte"].Any());
Assert.Throws <NotSupportedException>(() => df["Short"].All());
Assert.Throws <NotSupportedException>(() => df["Short"].Any());
Assert.Throws <NotSupportedException>(() => df["Uint"].All());
Assert.Throws <NotSupportedException>(() => df["Uint"].Any());
Assert.Throws <NotSupportedException>(() => df["Ulong"].All());
Assert.Throws <NotSupportedException>(() => df["Ulong"].Any());
Assert.Throws <NotSupportedException>(() => df["Ushort"].All());
Assert.Throws <NotSupportedException>(() => df["Ushort"].Any());
bool any = df["Bool"].Any();
bool all = df["Bool"].All();
Assert.True(any);
Assert.False(all);
// Test the computation results
df["Double"][0] = 100.0;
df["Double"].CumulativeMax();
Assert.Equal(100.0, df["Double"][9]);
df["Float"][0] = -10.0f;
df["Float"].CumulativeMin();
Assert.Equal(-10.0f, df["Float"][9]);
df["Uint"].CumulativeProduct();
Assert.Equal((uint)0, df["Uint"][9]);
df["Ushort"].CumulativeSum();
Assert.Equal((ushort)45, df["Ushort"][9]);
Assert.Equal(100.0, df["Double"].Max());
Assert.Equal(-10.0f, df["Float"].Min());
Assert.Equal((uint)0, df["Uint"].Product());
Assert.Equal((ushort)165, df["Ushort"].Sum());
df["Double"][0] = 100.1;
Assert.Equal(100.1, df["Double"][0]);
df["Double"].Round();
Assert.Equal(100.0, df["Double"][0]);
// Test that none of the numeric column types throw
for (int i = 0; i < df.ColumnCount; i++)
{
BaseColumn column = df.Column(i);
if (column.DataType == typeof(bool))
{
Assert.Throws <NotSupportedException>(() => column.CumulativeMax());
Assert.Throws <NotSupportedException>(() => column.CumulativeMin());
Assert.Throws <NotSupportedException>(() => column.CumulativeProduct());
Assert.Throws <NotSupportedException>(() => column.CumulativeSum());
Assert.Throws <NotSupportedException>(() => column.Max());
Assert.Throws <NotSupportedException>(() => column.Min());
Assert.Throws <NotSupportedException>(() => column.Product());
Assert.Throws <NotSupportedException>(() => column.Sum());
continue;
}
else if (column.DataType == typeof(string))
{
Assert.Throws <NotImplementedException>(() => column.CumulativeMax());
Assert.Throws <NotImplementedException>(() => column.CumulativeMin());
Assert.Throws <NotImplementedException>(() => column.CumulativeProduct());
Assert.Throws <NotImplementedException>(() => column.CumulativeSum());
Assert.Throws <NotImplementedException>(() => column.Max());
Assert.Throws <NotImplementedException>(() => column.Min());
Assert.Throws <NotImplementedException>(() => column.Product());
Assert.Throws <NotImplementedException>(() => column.Sum());
continue;
}
column.CumulativeMax();
column.CumulativeMin();
column.CumulativeProduct();
column.CumulativeSum();
column.Max();
column.Min();
column.Product();
column.Sum();
}
}
public ExportData(BaseTable tbl, WhereClause wc, OrderBy orderBy, BaseColumn[] columns)
{
_exportDataToCSV = new ExportDataToCSV(tbl, wc, orderBy, columns);
}
/// <summary>
/// Cretes new ExcelColumn
/// </summary>
/// <param name="col">BaseColumn</param>
/// <param name="format">a format string from Excel's Format Cell menu. For example "dddd, mmmm dd, yyyy h:mm AM/PM;@", "#,##0.00"</param>
public ExcelColumn(BaseColumn col, string format)
{
DisplayColumn = col;
DisplayFormat = format;
}
public JoinColumnInitializer(BaseColumn resultColumn, params BaseColumn[] columns)
{
this.resultColumn = resultColumn;
this.columns = columns;
JoinSeparator = ",\r\n";
}
public static string GetSum(
BaseColumn col,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false);
colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Sum);
return VwPropSBondBudgetView.Instance.GetColumnStatistics(colSel, null, where.GetFilter(), null, orderBy, pageIndex, pageSize);
}
private static IEnumerable <string> GetJoinOf(RenderContext context, int rowIndex, BaseColumn c)
{
if (!c.IsCrossColumn)
{
return new[] { GetName(c, context.OtherColumns[c.ColumnName], rowIndex) }
}
;
var crossColumnNames = c.BaseCrossColumnDataSource.GetCrossColumnNames().ToList();
if (crossColumnNames.Count == 0)
{
return(new string[0]);
}
return(crossColumnNames.Select(n => GetName(c, context.OtherColumns[n], rowIndex)));
}
public void TestGroupBy()
{
DataFrame df = MakeDataFrameWithNumericAndBoolColumns(10);
DataFrame count = df.GroupBy("Bool").Count();
Assert.Equal(2, count.RowCount);
Assert.Equal((long)5, count["Int"][0]);
Assert.Equal((long)4, count["Decimal"][1]);
for (int r = 0; r < count.RowCount; r++)
{
for (int c = 1; c < count.ColumnCount; c++)
{
Assert.Equal((long)(r == 0 ? 5 : 4), count.Column(c)[r]);
}
}
DataFrame first = df.GroupBy("Bool").First();
Assert.Equal(2, first.RowCount);
for (int r = 0; r < 2; r++)
{
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
BaseColumn firstColumn = first[originalColumn.Name];
Assert.Equal(originalColumn[r], firstColumn[r]);
}
}
DataFrame head = df.GroupBy("Bool").Head(3);
List <int> verify = new List <int>()
{
0, 3, 1, 4, 2, 5
};
for (int r = 0; r < 5; r++)
{
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
BaseColumn headColumn = head[originalColumn.Name];
Assert.Equal(originalColumn[r].ToString(), headColumn[verify[r]].ToString());
}
}
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
if (originalColumn.Name == "Bool")
{
continue;
}
BaseColumn headColumn = head[originalColumn.Name];
Assert.Equal(originalColumn[5], headColumn[verify[5]]);
}
Assert.Equal(6, head.RowCount);
DataFrame tail = df.GroupBy("Bool").Tail(3);
Assert.Equal(6, tail.RowCount);
List <int> originalColumnVerify = new List <int>()
{
6, 8, 7, 9
};
List <int> tailColumnVerity = new List <int>()
{
1, 2, 4, 5
};
for (int r = 0; r < 4; r++)
{
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
BaseColumn tailColumn = tail[originalColumn.Name];
Assert.Equal(originalColumn[originalColumnVerify[r]].ToString(), tailColumn[tailColumnVerity[r]].ToString());
}
}
DataFrame max = df.GroupBy("Bool").Max();
Assert.Equal(2, max.RowCount);
for (int r = 0; r < 2; r++)
{
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
if (originalColumn.Name == "Bool" || originalColumn.Name == "Char")
{
continue;
}
BaseColumn maxColumn = max[originalColumn.Name];
Assert.Equal(((long)(r == 0 ? 8 : 9)).ToString(), maxColumn[r].ToString());
}
}
DataFrame min = df.GroupBy("Bool").Min();
Assert.Equal(2, min.RowCount);
DataFrame product = df.GroupBy("Bool").Product();
Assert.Equal(2, product.RowCount);
DataFrame sum = df.GroupBy("Bool").Sum();
Assert.Equal(2, sum.RowCount);
for (int r = 0; r < 2; r++)
{
for (int c = 0; c < count.ColumnCount; c++)
{
BaseColumn originalColumn = df.Column(c);
if (originalColumn.Name == "Bool" || originalColumn.Name == "Char")
{
continue;
}
BaseColumn minColumn = min[originalColumn.Name];
Assert.Equal("0", minColumn[r].ToString());
BaseColumn productColumn = product[originalColumn.Name];
Assert.Equal("0", productColumn[r].ToString());
BaseColumn sumColumn = sum[originalColumn.Name];
Assert.Equal("20", sumColumn[r].ToString());
}
}
}
public ControlsColumnBuilder(BaseColumn <T> column) : base(column)
{
}
public static string GetCount(
BaseColumn col,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false);
colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Count);
return EstimateTable.Instance.GetColumnStatistics(colSel, null, where.GetFilter(), null, orderBy, pageIndex, pageSize);
}
public BaseColumnBuilder(BaseColumn <T> column)
{
Column = column;
}
/// <summary>
/// This method takes a record and a Column and returns an evaluated value of DFKA formula.
/// </summary>
public static string GetDFKA(BaseRecord rec, BaseColumn col)
{
ForeignKey fkColumn = UsersTable.Instance.TableDefinition.GetExpandableNonCompositeForeignKey(col);
if (fkColumn == null)
return null;
String _DFKA = fkColumn.PrimaryKeyDisplayColumns;
if (_DFKA.Trim().StartsWith("="))
{
// if the formula is in the format of "= <Primary table>.<Field name>, then pull out the data from the rec object instead of doing formula evaluation
string tableCodeName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
string column = _DFKA.Trim('=').Trim();
if (column.StartsWith(tableCodeName + ".", StringComparison.InvariantCultureIgnoreCase))
{
column = column.Substring(tableCodeName.Length + 1);
}
foreach (BaseColumn c in fkColumn.PrimaryKeyTableDefinition.Columns)
{
if (column == c.CodeName)
{
return rec.Format(c);
}
}
String tableName = fkColumn.PrimaryKeyTableDefinition.TableCodeName;
return EvaluateFormula(_DFKA, rec, null, tableName);
}
else
return null;
}
public CloneDataColumnInitializer(BaseColumn cloneColumn, BaseColumn baseColumn)
{
this.baseColumn = baseColumn;
this.cloneColumn = cloneColumn;
}
public static string GetSum(
BaseColumn col,
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false);
colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Sum);
return UsersTable.Instance.GetColumnStatistics(colSel, join, where.GetFilter(), null, orderBy, pageIndex, pageSize);
}
/// <summary>
/// Return the type of the given variable if it exists in the data source.
/// If the Return Type is Nothing, the evaluator assumes this is an invalid
/// variable and tries other methods to get its value.
/// </summary>
/// <param name="sender">The sender that sent this event.</param>
/// <param name="e">The event argument. Set e.VariableType.</param>
protected void variables_ResolveVariableType(object sender, ResolveVariableTypeEventArgs e)
{
BaseColumn col = null;
// Returning Nothing indicates that we do not recognize this variable.
e.VariableType = null;
// If no DataSource was set, we do not have variables that we can use
// directly.
if ((DataSource == null))
{
return;
}
try
{
// Find a column in the datasource using a variable name.
col = DataSource.TableAccess.TableDefinition.ColumnList.GetByCodeName(e.VariableName);
if (col == null)
{
// if the variable name ended with "DefaultValue", remmove it and then try to get the column name again.
if (e.VariableName.ToLower().EndsWith("defaultvalue"))
{
col = DataSource.TableAccess.TableDefinition.ColumnList.GetByCodeName(e.VariableName.Substring(0, e.VariableName.Length - 12));
}
}
if (col == null)
{
return;
}
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
case BaseColumn.ColumnTypes.Star:
// By default, all our internal data types use Decimal.
// This may result in problems when using the Math library
// but it reduces the problem by allowing us to loosely-type
// all data source properties.
e.VariableType = typeof(decimal);
break;
case BaseColumn.ColumnTypes.Currency:
// Convert currency into decimal to allow easier use in formulas
e.VariableType = typeof(decimal);
break;
case BaseColumn.ColumnTypes.Boolean:
// Boolean data types are maintained as Boolean and not
// converted to Integer as the Binary data type is.
e.VariableType = typeof(bool);
break;
case BaseColumn.ColumnTypes.Credit_Card_Date:
case BaseColumn.ColumnTypes.Date:
// Use DateTme even for Credit Card Date.
e.VariableType = typeof(DateTime);
break;
case BaseColumn.ColumnTypes.Country:
case BaseColumn.ColumnTypes.Credit_Card_Number:
case BaseColumn.ColumnTypes.Email:
case BaseColumn.ColumnTypes.Password:
case BaseColumn.ColumnTypes.String:
case BaseColumn.ColumnTypes.Unique_Identifier:
case BaseColumn.ColumnTypes.USA_Phone_Number:
case BaseColumn.ColumnTypes.USA_State:
case BaseColumn.ColumnTypes.USA_Zip_Code:
case BaseColumn.ColumnTypes.Very_Large_String:
case BaseColumn.ColumnTypes.Web_Url:
// For the purpose of formula's, all of the above field types
// are treated as strings.
e.VariableType = typeof(string);
break;
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.File:
case BaseColumn.ColumnTypes.Image:
// For the purpose of formula's we ignore BLOB fields since they
// cannot be used in any calculations or string functions.
e.VariableType = null;
break;
default:
// Unknown data type.
e.VariableType = null;
break;
}
}
catch (Exception)
{
// Ignore the error in case we cannot find the variable or its type - simply say that
// the Variable Type is Nothing - implying that we do not recognize this variable.
}
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="uniqueColumnName">Unique name of the column to fetch.</param>
public static BaseColumn GetColumn(string uniqueColumnName)
{
BaseColumn column = View_FieldTripStatsView.Instance.TableDefinition.ColumnList.GetByUniqueName(uniqueColumnName);
return(column);
}
public void Setup()
{
_dataFrame = new DataFrame();
_column0 = new PrimitiveColumn <int>("Int0", Enumerable.Range(0, 50000));
_dataFrame.InsertColumn(0, _column0);
}
public TemplateColumnBuilder(BaseColumn <T> column) : base(column)
{
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="uniqueColumnName">Unique name of the column to fetch.</param>
public static BaseColumn GetColumn(string uniqueColumnName)
{
BaseColumn column = PhotoClubContactLinksTable.Instance.TableDefinition.ColumnList.GetByUniqueName(uniqueColumnName);
return(column);
}
// Event handler for ImageButton .
public virtual void UOMExportExcelButton_Click(object sender, ImageClickEventArgs args)
{
try {
// Enclose all database retrieval/update code within a Transaction boundary
DbUtils.StartTransaction();
// To customize the columns or the format, override this function in Section 1 of the page
// and modify it to your liking.
// Build the where clause based on the current filter and search criteria
// Create the Order By clause based on the user's current sorting preference.
WhereClause wc = null;
wc = CreateWhereClause();
OrderBy orderBy = null;
orderBy = CreateOrderBy();
bool done = false;
string val = "";
CompoundFilter join = CreateCompoundJoinFilter();
// Read pageSize records at a time and write out the Excel file.
int totalRowsReturned = 0;
this.TotalRecords = UOMTable.GetRecordCount(join, wc);
if (this.TotalRecords > 10000)
{
// Add each of the columns in order of export.
BaseColumn[] columns = new BaseColumn[] {
UOMTable.UOMName,
UOMTable.UOMDescription,
UOMTable.Status,
null};
ExportDataToCSV exportData = new ExportDataToCSV(UOMTable.Instance,wc,orderBy,columns);
exportData.StartExport(this.Page.Response, true);
DataForExport dataForCSV = new DataForExport(UOMTable.Instance, wc, orderBy, columns,join);
// Read pageSize records at a time and write out the CSV file.
while (!done)
{
ArrayList recList = dataForCSV.GetRows(exportData.pageSize);
if (recList == null)
break; //we are done
totalRowsReturned = recList.Count;
foreach (BaseRecord rec in recList)
{
foreach (BaseColumn col in dataForCSV.ColumnList)
{
if (col == null)
continue;
if (!dataForCSV.IncludeInExport(col))
continue;
val = rec.GetValue(col).ToString();
exportData.WriteColumnData(val, dataForCSV.IsString(col));
}
exportData.WriteNewRow();
}
// If we already are below the pageSize, then we are done.
if (totalRowsReturned < exportData.pageSize)
{
done = true;
}
}
exportData.FinishExport(this.Page.Response);
}
else
{
// Create an instance of the ExportDataToExcel class with the table class, where clause and order by.
ExportDataToExcel excelReport = new ExportDataToExcel(UOMTable.Instance, wc, orderBy);
// Add each of the columns in order of export.
// To customize the data type, change the second parameter of the new ExcelColumn to be
// a format string from Excel's Format Cell menu. For example "dddd, mmmm dd, yyyy h:mm AM/PM;@", "#,##0.00"
if (this.Page.Response == null)
return;
excelReport.CreateExcelBook();
int width = 0;
int columnCounter = 0;
DataForExport data = new DataForExport(UOMTable.Instance, wc, orderBy, null,join);
data.ColumnList.Add(new ExcelColumn(UOMTable.UOMName, "Default"));
data.ColumnList.Add(new ExcelColumn(UOMTable.UOMDescription, "Default"));
data.ColumnList.Add(new ExcelColumn(UOMTable.Status, "Default"));
// First write out the Column Headers
foreach (ExcelColumn col in data.ColumnList)
{
width = excelReport.GetExcelCellWidth(col);
if (data.IncludeInExport(col))
{
excelReport.AddColumnToExcelBook(columnCounter, col.ToString(), excelReport.GetExcelDataType(col), width, excelReport.GetDisplayFormat(col));
columnCounter++;
}
}
while (!done)
{
ArrayList recList = data.GetRows(excelReport.pageSize);
if (recList == null)
{
break;
}
totalRowsReturned = recList.Count;
foreach (BaseRecord rec in recList)
{
excelReport.AddRowToExcelBook();
columnCounter = 0;
foreach (ExcelColumn col in data.ColumnList)
{
if (!data.IncludeInExport(col))
continue;
Boolean _isExpandableNonCompositeForeignKey = col.DisplayColumn.TableDefinition.IsExpandableNonCompositeForeignKey(col.DisplayColumn);
if (_isExpandableNonCompositeForeignKey && col.DisplayColumn.IsApplyDisplayAs)
{
val = UOMTable.GetDFKA(rec.GetValue(col.DisplayColumn).ToString(), col.DisplayColumn, null);
if (String.IsNullOrEmpty(val))
{
val = rec.Format(col.DisplayColumn);
}
}
else
val = excelReport.GetValueForExcelExport(col, rec);
excelReport.AddCellToExcelRow(columnCounter, excelReport.GetExcelDataType(col), val);
columnCounter++;
}
}
// If we already are below the pageSize, then we are done.
if (totalRowsReturned < excelReport.pageSize)
{
done = true;
}
}
excelReport.SaveExcelBook(this.Page.Response);
}
} catch (Exception ex) {
// Upon error, rollback the transaction
this.Page.RollBackTransaction(sender);
this.Page.ErrorOnPage = true;
// Report the error message to the end user
BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(this, "BUTTON_CLICK_MESSAGE", ex.Message);
} finally {
DbUtils.EndTransaction();
}
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="name">name of the column to fetch.</param>
public static BaseColumn GetColumnByName(string name)
{
BaseColumn column = PhotoClubContactLinksTable.Instance.TableDefinition.ColumnList.GetByInternalName(name);
return(column);
}
/// <summary>
/// This method gets the specified column.
/// </summary>
/// <param name="uniqueColumnName">Unique name of the column to fetch.</param>
public static BaseColumn GetColumn(string uniqueColumnName)
{
BaseColumn column = AreasTable.Instance.TableDefinition.ColumnList.GetByUniqueName(uniqueColumnName);
return(column);
}
public void TestIEnumerable()
{
DataFrame df = MakeDataFrameWithAllColumnTypes(10);
int totalValueCount = 0;
for (int i = 0; i < df.ColumnCount; i++)
{
BaseColumn baseColumn = df.Column(i);
foreach (object value in baseColumn)
{
totalValueCount++;
}
}
Assert.Equal(10 * df.ColumnCount, totalValueCount);
// spot check a few column types:
StringColumn stringColumn = (StringColumn)df["String"];
StringBuilder actualStrings = new StringBuilder();
foreach (string value in stringColumn)
{
if (value == null)
{
actualStrings.Append("<null>");
}
else
{
actualStrings.Append(value);
}
}
Assert.Equal("01234<null>6789", actualStrings.ToString());
ArrowStringColumn arrowStringColumn = (ArrowStringColumn)df["ArrowString"];
actualStrings.Clear();
foreach (string value in arrowStringColumn)
{
if (value == null)
{
actualStrings.Append("<null>");
}
else
{
actualStrings.Append(value);
}
}
Assert.Equal("foofoofoofoofoofoofoofoofoofoo", actualStrings.ToString());
PrimitiveColumn <float> floatColumn = (PrimitiveColumn <float>)df["Float"];
actualStrings.Clear();
foreach (float?value in floatColumn)
{
if (value == null)
{
actualStrings.Append("<null>");
}
else
{
actualStrings.Append(value);
}
}
Assert.Equal("01234<null>6789", actualStrings.ToString());
PrimitiveColumn <int> intColumn = (PrimitiveColumn <int>)df["Int"];
actualStrings.Clear();
foreach (int?value in intColumn)
{
if (value == null)
{
actualStrings.Append("<null>");
}
else
{
actualStrings.Append(value);
}
}
Assert.Equal("01234<null>6789", actualStrings.ToString());
}
/// <summary>
/// Return the value of the given variable if it exists in the data source
/// </summary>
/// <param name="sender">The input whose absolute value is to be found.</param>
/// <param name="e">The input whose absolute value is to be found.</param>
protected void variables_ResolveVariableValue(object sender, ResolveVariableValueEventArgs e)
{
BaseColumn col = default(BaseColumn);
// Default value is Nothing
e.VariableValue = null;
// If no DataSource was set, we do not have variables that we can use
// directly. We should not get here since the request for Type should have
// caught this.
if (DataSource == null)
{
return;
}
try
{
// Find a column in the datasource using a variable name.
col = DataSource.TableAccess.TableDefinition.ColumnList.GetByCodeName(e.VariableName);
if (col == null)
{
// if the variable name ended with "DefaultValue", remmove it and then try to get the column name again.
if (e.VariableName.ToLower().EndsWith("defaultvalue"))
{
col = DataSource.TableAccess.TableDefinition.ColumnList.GetByCodeName(e.VariableName.Substring(0, e.VariableName.Length - 12));
}
if (col != null)
{
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
case BaseColumn.ColumnTypes.Star:
// The Number and Percentage values are saved as Single. So we first
// retrieve the Single value and then convert to Decimal. Our policy is
// always to return Decimal (never to return Single or Double) to be constent
// and avoid type conversion in the evaluator.
e.VariableValue = BaseFormulaUtils.ParseDecimal(col.DefaultValue);
break;
case BaseColumn.ColumnTypes.Currency:
e.VariableValue = BaseFormulaUtils.ParseDecimal(col.DefaultValue);
break;
case BaseColumn.ColumnTypes.Boolean:
e.VariableValue = col.DefaultValue;
break;
case BaseColumn.ColumnTypes.Credit_Card_Date:
case BaseColumn.ColumnTypes.Date:
e.VariableValue = BaseFormulaUtils.ParseDate(col.DefaultValue);
break;
case BaseColumn.ColumnTypes.Country:
case BaseColumn.ColumnTypes.Credit_Card_Number:
case BaseColumn.ColumnTypes.Email:
case BaseColumn.ColumnTypes.Password:
case BaseColumn.ColumnTypes.String:
case BaseColumn.ColumnTypes.Unique_Identifier:
case BaseColumn.ColumnTypes.USA_Phone_Number:
case BaseColumn.ColumnTypes.USA_State:
case BaseColumn.ColumnTypes.USA_Zip_Code:
case BaseColumn.ColumnTypes.Very_Large_String:
case BaseColumn.ColumnTypes.Web_Url:
e.VariableValue = col.DefaultValue;
break;
case BaseColumn.ColumnTypes.File:
case BaseColumn.ColumnTypes.Image:
// Can't do anything here.
e.VariableValue = null;
break;
default:
e.VariableValue = null;
break;
}
}
}
else
{
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
case BaseColumn.ColumnTypes.Star:
// The Number and Percentage values are saved as Single. So we first
// retrieve the Single value and then convert to Decimal. Our policy is
// always to return Decimal (never to return Single or Double) to be constent
// and avoid type conversion in the evaluator.
e.VariableValue = Decimal.Parse(this.DataSource.GetValue(col).ToDouble().ToString());
break;
case BaseColumn.ColumnTypes.Currency:
e.VariableValue = this.DataSource.GetValue(col).ToDecimal();
break;
case BaseColumn.ColumnTypes.Boolean:
e.VariableValue = this.DataSource.GetValue(col).ToBoolean();
break;
case BaseColumn.ColumnTypes.Credit_Card_Date:
case BaseColumn.ColumnTypes.Date:
e.VariableValue = this.DataSource.GetValue(col).ToDateTime();
break;
case BaseColumn.ColumnTypes.Country:
case BaseColumn.ColumnTypes.Credit_Card_Number:
case BaseColumn.ColumnTypes.Email:
case BaseColumn.ColumnTypes.Password:
case BaseColumn.ColumnTypes.String:
case BaseColumn.ColumnTypes.Unique_Identifier:
case BaseColumn.ColumnTypes.USA_Phone_Number:
case BaseColumn.ColumnTypes.USA_State:
case BaseColumn.ColumnTypes.USA_Zip_Code:
case BaseColumn.ColumnTypes.Very_Large_String:
case BaseColumn.ColumnTypes.Web_Url:
e.VariableValue = this.DataSource.GetValue(col).ToString();
break;
case BaseColumn.ColumnTypes.File:
case BaseColumn.ColumnTypes.Image:
// Can't do anything here.
e.VariableValue = null;
break;
default:
e.VariableValue = null;
break;
}
}
}
catch (Exception)
{
// Ignore the error in case we cannot find the variable or its type - simply say that
// the Variable Type is Nothing - implying that we do not recognize this variable.
}
}
public JsonResult Save(BaseColumn model, FormCollection form)
{
try
{
if (model.id != 0)
{
BoolString validation = model.BeforeEdit(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
db.Entry(model).State = System.Data.Entity.EntityState.Modified;
validation = model.BeforeSave(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
validation = model.BeforeEdit(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
db.SaveChanges();
validation = model.AfterSave(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
validation = model.AfterEdit(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
}
else
{
BoolString validation = model.BeforeSave(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
validation = model.BeforeCreate(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
db.BaseColumns.Add(model);
db.SaveChanges();
validation = model.AfterSave(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
validation = model.AfterCreate(db);
if (validation.BoolValue)
{
return(Json(new { Message = validation.StringValue }));
}
}
return(Json(new { id = model.id, MessageSucess = "That Column saved successfully." }));
}
catch (Exception ex)
{
return(Json(new { Message = Helper.ModeralException(ex).Replace("@table", "Column") }));
}
}