/// <summary>
/// Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
/// </summary>
/// <param name="rowField">The column name of the Source Table which you want to spread into rows</param>
/// <param name="dataField">The column name of the Source Table which you want to spread into Data Part</param>
/// <param name="aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
/// <param name="columnFields">The List of column names which you want to spread as columns</param>
/// <returns>A DataTable containing the Pivoted Data</returns>
public static DataTable PivotData(this DataTable SourceTable, string rowField, string dataField, AggregateFunctionE aggregate, params string[] columnFields)
{
IEnumerable <DataRow> _Source = new List <DataRow>();
_Source = SourceTable.Rows.Cast <DataRow>();
DataTable dt = new DataTable();
string Separator = ".";
List <string> rowList = _Source.Select(x => x[rowField].ToString()).Distinct().ToList();
// Gets the list of columns .(dot) separated.
var colList = _Source.Select(x => (columnFields.Select(n => x[n]).Aggregate((a, b) => a += Separator + b.ToString())).ToString()).Distinct().OrderBy(m => m);
dt.Columns.Add(rowField);
foreach (var colName in colList)
{
dt.Columns.Add(colName); // Cretes the result columns.//
}
foreach (string rowName in rowList)
{
DataRow row = dt.NewRow();
row[rowField] = rowName;
foreach (string colName in colList)
{
string strFilter = rowField + " = '" + rowName + "'";
string[] strColValues = colName.Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < columnFields.Length; i++)
{
strFilter += " and " + columnFields[i] + " = '" + strColValues[i] + "'";
}
row[colName] = SourceTable.Computing(strFilter, dataField, aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
public static DataTable PivotData(this DataTable SourceTable, string rowField, string dataField, AggregateFunctionE aggregate, bool showSubTotal, params string[] columnFields)
{
IEnumerable <DataRow> _Source = new List <DataRow>();
_Source = SourceTable.Rows.Cast <DataRow>();
DataTable dt = new DataTable();
string Separator = ".";
List <string> rowList = _Source.Select(x => x[rowField].ToString()).Distinct().ToList();
// Gets the list of columns .(dot) separated.
List <string> colList = _Source.Select(x => columnFields.Aggregate((a, b) => x[a].ToString() + Separator + x[b].ToString())).Distinct().OrderBy(m => m).ToList();
if (showSubTotal && columnFields.Length > 1)
{
string totalField = string.Empty;
for (int i = 0; i < columnFields.Length - 1; i++)
{
totalField += columnFields[i] + "(Total)" + Separator;
}
List <string> totalList = _Source.Select(x => totalField + x[columnFields.Last()].ToString()).Distinct().OrderBy(m => m).ToList();
colList.InsertRange(0, totalList);
}
dt.Columns.Add(rowField);
colList.ForEach(x => dt.Columns.Add(x));
foreach (string rowName in rowList)
{
DataRow row = dt.NewRow();
row[rowField] = rowName;
foreach (string colName in colList)
{
string filter = rowField + " = '" + rowName + "'";
string[] colValues = colName.Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < columnFields.Length; i++)
{
if (!colValues[i].Contains("(Total)"))
{
filter += " and " + columnFields[i] + " = '" + colValues[i] + "'";
}
}
row[colName] = SourceTable.Computing(filter, dataField, colName.Contains("(Total)") ? AggregateFunctionE.Sum : aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="element"></param>
/// <param name="evp"></param>
public override void Visit(Fun element, EvaluationParam evp)
{
if (element.Type == FunctionType.Avg)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Average);
}
}
else if (element.Type == FunctionType.Count)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Rows.Count;
}
}
else if (element.Type == FunctionType.UCount)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.DefaultView.ToTable(true, element.ActionField).Rows.Count;
}
}
else if (element.Type == FunctionType.Min)
{
if (element.GetSource() != null)
{
DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Min);
}
}
else if (element.Type == FunctionType.Max)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Max);
}
}
else if (element.Type == FunctionType.Sum)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Sum);
}
}
else if (element.Type == FunctionType.Case)
{
var c = (Case)element;
System.Data.DataTable dt = getRecord(element);
result = new Result();
StringBuilder sb = new StringBuilder();
sb.Append("{0}");
foreach (CaseParam cp in c.Cases)
{
StringBuilder s = new StringBuilder();
s.AppendFormat("IIF([{0}] {1} '{2}','{3}','{4}')", c.FieldName, cp.Condition, cp.Value, cp.AliasValue, "{0}");
string sst = sb.ToString();
sb.Clear();
sb.AppendFormat(sst, s.ToString());
}
if (sb.ToString() != "")
{
string st = sb.ToString();
sb.Clear();
sb.AppendFormat(st, c.elseValue);
if (dt.Columns.Contains(c.NewFieldName) == false)
{
dt.Columns.Add(c.NewFieldName, typeof(string));
}
dt.Columns[c.NewFieldName].Expression = sb.ToString();
dt.AcceptChanges();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Order)
{
var c = (Order)element;
System.Data.DataTable dt = getRecord(element);
result = new Result();
string s = "";
foreach (ParamFields p in c.Fields)
{
s = s + "," + p.FieldName + " " + p.Order;
}
if (s.StartsWith(","))
{
s = s.Substring(1);
}
DataView viewFI = new DataView(dt);
viewFI.Sort = s;
dt = viewFI.ToTable();
result.Value = dt;
}
else if (element.Type == FunctionType.Pivot)
{
var p = (Pivot)element;
System.Data.DataTable dt = (DataTable)element.GetSource();
List <string> row = new List <string>();
List <string> datafield = new List <string>();
List <AggregateFunctionE> dataagg = new List <AggregateFunctionE>();
List <string> colfield = new List <string>();
foreach (ParamFields d in p.RowArguments)
{
row.Add(d.FieldName);
}
foreach (ParamFields d in p.AggrArguments)
{
datafield.Add(d.FieldName);
dataagg.Add((AggregateFunctionE)d.Aggregate);
}
foreach (ParamFields d in p.ColumnArguments)
{
colfield.Add(d.FieldName);
}
dt = dt.PivotData(datafield, dataagg.ToList(), row.ToArray(), colfield.ToArray(), false, 0, null, null, false);
result = new Result();
result.Value = dt;
}
else if (element.Type == FunctionType.Mean)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Median);
}
}
else if (element.Type == FunctionType.Mode)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Mode);
}
}
else if (element.Type == FunctionType.Range)
{
}
else if (element.Type == FunctionType.Medium)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = getRecord(element);
result = new Result();
result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Median);
}
}
else if (element.Type == FunctionType.Merge)
{
}
else if (element.Type == FunctionType.Join)
{
}
else if (element.Type == FunctionType.Filter)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
var flt = "1=1";
foreach (ParamFields pf in element.Arguments)
{
flt = flt + " AND " + pf.FieldName + " = " + pf.FieldValue;
}
dt.DefaultView.RowFilter = flt;
dt = dt.DefaultView.ToTable(true);
dt.DefaultView.RowFilter = "";
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Calculate)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt.Calculate(((Calculate)element).ColumnName, ((Calculate)element).formula);
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Columnduplicate)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt = dt.DuplicateColumn(((Duplicate)element).ColumnName, ((Duplicate)element).AliasColumnName);
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Dateparse)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt = dt.DateParse(((DateParse)element).ColumnName, ((DateParse)element).GetParse());
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Columnspit)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt = dt.Split(((Split)element).SplitColumn, ((Split)element).Spliter, ((Split)element).ColumnPrefix);
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Replace)
{
if (element.GetSource() != null)
{
List <KeyValuePair <string, string> > kp = new List <KeyValuePair <string, string> >();
kp.Add(new KeyValuePair <string, string>(((Replace)element).FindString, ((Replace)element).ReplaceString));
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt = dt.FindReplace(((Replace)element).ColumnName, kp, ((Replace)element).NewColumnName);
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.ChangeCase)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
if ((((TextCase)element).CaseType == TextCaseType.UPPER))
{
dt = dt.UpperCase(((TextCase)element).ColumnName);
}
else if ((((TextCase)element).CaseType == TextCaseType.CAPITAL))
{
dt = dt.CapitalCase(((TextCase)element).ColumnName);
}
else if ((((TextCase)element).CaseType == TextCaseType.LOWER))
{
dt = dt.LowerCase(((TextCase)element).ColumnName);
}
else if ((((TextCase)element).CaseType == TextCaseType.TITLE))
{
dt = dt.TitleCase(((TextCase)element).ColumnName);
}
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.Trancate)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
dt.Trancate(((Trancate)element).ColumnName, ((Trancate)element).TrancateIndex);
result = new Result();
result.Value = dt;
}
}
else if (element.Type == FunctionType.GetValue)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
if (dt.Rows.Count > 0)
{
var name = ((Fun)element).Arguments[0].FieldName;
var s = dt.Rows[0][name.Trim()];
result = new Result();
result.Value = s;
}
else
{
result = new Result();
result.Value = "";
}
}
}
else if (element.Type == FunctionType.GetValues)
{
if (element.GetSource() != null)
{
System.Data.DataTable dt = (System.Data.DataTable)element.GetSource();
if (dt.Rows.Count > 0)
{
var name = ((Fun)element).Arguments[0].FieldName.Trim();
Type type = dt.Columns[name].DataType;
var selectedColumn = string.Join(",", dt.AsEnumerable().Select(s => s.Field <string>(name)).ToArray());
result = new Result();
result.Value = selectedColumn;
}
else
{
result = new Result();
result.Value = "";
}
}
else
{
result = new Result();
result.Value = "";
}
}
else if (element.Type == FunctionType.toString)
{
if (element.GetSource() != null)
{
}
}
}
public static DataTable PivotData(this DataTable _SourceTable, string DataField, AggregateFunctionE Aggregate, string[] RowFields, string[] ColumnFields, Boolean isFindSummary = false)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = _SourceTable.DefaultView.ToTable(true, RowFields).AsEnumerable().ToList();
for (int index = RowFields.Count() - 1; index >= 0; index--)
{
RowList = RowList.OrderBy(x => x.Field <object>(RowFields[index])).ToList();
}
// Gets the list of columns .(dot) separated.
//dt.Columns.Add(RowFields);
foreach (string s in RowFields)
{
//Get dataType of the column
if (_SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("datetime") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("date") >= 0)
{
dt.Columns.Add(s, typeof(System.DateTime));
}
else if (_SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("int") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("double") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("float") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("decimal") >= 0)
{
dt.Columns.Add(s, typeof(double));
}
else
{
dt.Columns.Add(s);
}
}
if (ColumnFields != null)
{
var ColList = (from x in _SourceTable.AsEnumerable()
select new
{
Name = ColumnFields.Select(n => x.Field <object>(n))
.Aggregate((a, b) => a += Separator + b.ToString())
})
.Distinct();
//.OrderBy(m => m.Name);
foreach (var col in ColList)
{
if (col.Name != null)
{
dt.Columns.Add(col.Name.ToString(), typeof(double)); // Cretes the result columns.//
}
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
string strFilter = string.Empty;
foreach (string Field in RowFields)
{
row[Field] = RowName[Field];
strFilter += " and " + Field + " = '" + RowName[Field].ToString() + "'";
}
strFilter = strFilter.Substring(5);
foreach (var col in ColList)
{
string filter = strFilter;
if (col.Name != null)
{
string[] strColValues = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
{
if (strColValues[i] != null)
{
filter += " and " + ColumnFields[i] + " = '" + strColValues[i] + "'";
}
}
object val = _SourceTable.Computing(filter, DataField, Aggregate);
if (val == null)
{
val = 0;
}
row[col.Name.ToString()] = val;
}
}
dt.Rows.Add(row);
}
}
else
{
dt.Columns.Add(DataField, typeof(double));
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
string strFilter = string.Empty;
foreach (string Field in RowFields)
{
row[Field] = RowName[Field];
strFilter += " and " + Field + " = '" + RowName[Field].ToString() + "'";
}
strFilter = strFilter.Substring(5);
row[DataField] = _SourceTable.Computing(strFilter, DataField, Aggregate);
dt.Rows.Add(row);
}
if (isFindSummary)
{
for (int index = 0; index < RowFields.Count() - 1; index++)
{
DataTable _temp = new DataTable();
_temp = dt.Clone();
List <string> rFields = new List <string>();
DataTable _dt = new DataTable();
List <string> _disRow = new List <string>();
for (int rix = 0; rix < RowFields.Count() - 1; rix++)
{
if (index >= rix)
{
{
rFields.Add(RowFields[rix]);
}
}
}
foreach (string i in rFields)
{
_disRow.Add(i);
}
_dt = _SourceTable.DefaultView.ToTable(true, _disRow.ToArray());
foreach (DataRow _dr in _dt.Rows)
{
DataRow dr = _temp.NewRow();
string strFilter = "";
foreach (string fn in _disRow)
{
dr[fn] = _dr[fn];
strFilter += " and " + fn + " = '" + _dr[fn].ToString() + "'";
}
strFilter = strFilter.Substring(5);
dr[DataField] = dt.Computing(strFilter, DataField, Aggregate);
_temp.Rows.Add(dr);
}
dt.Merge(_temp);
}
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Replace(" ", "_");
}
return(dt);
}
