public DataTable CreatePivotTable(DataTable SourceTable, Field RowFields, Field ColumnFields, Field TotalFields, PlaceOfTotalType PlaceOfTotal)
{
DataTable dt = CreateGroupByCubeTable(SourceTable, RowFields, ColumnFields, TotalFields, PlaceOfTotal);
return(CreatePivotTableByCol(dt, RowFields, ColumnFields, TotalFields));
}
public DataTable CreateGroupByCubeTable(DataTable SourceTable, Field RowFields, Field ColumnFields, Field TotalFields, PlaceOfTotalType PlaceOfTotal)
{
int FColIndex = RowFields.Fields.Count - 1;
FieldInfo[] Fields = new FieldInfo[RowFields.Fields.Count + ColumnFields.Fields.Count + TotalFields.Fields.Count];
RowFields.Fields.CopyTo(Fields, 0);
ColumnFields.Fields.CopyTo(Fields, RowFields.Fields.Count);
TotalFields.Fields.CopyTo(Fields, RowFields.Fields.Count + ColumnFields.Fields.Count);
if (SourceTable == null || Fields == null)
{
return(null);
}
DataTable dt = new DataTable(SourceTable.TableName);
string groupby = "";
int GFieldCnt = 0;
FieldInfo[] GFields = new FieldInfo[Fields.Length];
// Шинээр үүсгэж байгаа тэйбэлдээ багана үүсгэхдээ шинэ нэртэйгээр үүсгэнэ
// Учир нь Aggregate баганын алиас нэрийг давхардуулж өгч болох учир алдаа
// гарах магадлалтай байгаа юм.
int fieldcount = 0;
foreach (FieldInfo field in Fields)
{
// баганын шинэ нэрийг энд хадгална
fieldcount++;
field.NewFieldName = fieldcount.ToString();
DataColumn dc = SourceTable.Columns[field.FieldName];
dt.Columns.Add(field.NewFieldName, dc.DataType, dc.Expression);
if (field.Aggregate == AggregateType.None)
{
if (groupby.Length > 0)
{
groupby += ",";
}
groupby += field.FieldName;
GFields[GFieldCnt] = field;
GFieldCnt++;
}
}
DataView dv = new DataView(SourceTable, "", groupby, DataViewRowState.OriginalRows);
if (dv.Count <= 0)
{
return(dt);
}
DataRow lastrow = null;
bool samerow;
int rowcount = 0;
DataRow[] temprow;
temprow = new DataRow[GFieldCnt];
dv.AddNew();
foreach (DataRowView rowview in dv)
{
DataRow row = rowview.Row;
samerow = false;
rowcount++;
for (int i = GFieldCnt - 1; i >= FColIndex; i--)
{
lastrow = temprow[i];
if (lastrow != null)
{
FieldInfo[] F = new FieldInfo[i + 1];
Array.Copy(GFields, F, F.Length);
samerow = EqualRow(row, lastrow, F);
if (!samerow && temprow[i][GFields[0].NewFieldName].ToString().Length != 0)
{
//for (int j = i + 1; j < GFieldCnt; j++)
// temprow[i][GFields[j].NewFieldName] = (PlaceOfTotal == PlaceOfTotalType.Left ? DBNull.Value : "\xff");
dt.Rows.Add(temprow[i]);
rowcount = 1;
temprow[i] = dt.NewRow();
}
}
else
{
temprow[i] = dt.NewRow();
}
foreach (FieldInfo field in Fields)
{
switch (field.Aggregate)
{
case AggregateType.None:
case AggregateType.Last:
temprow[i][field.NewFieldName] = row[field.FieldName];
break;
case AggregateType.First:
if (rowcount == 1)
{
temprow[i][field.NewFieldName] = row[field.FieldName];
}
break;
case AggregateType.Count:
if (temprow[i][field.NewFieldName].ToString().Length == 0)
{
temprow[i][field.NewFieldName] = rowcount;
}
else
{
temprow[i][field.NewFieldName] = Convert.ToInt16(temprow[i][field.NewFieldName].ToString()) + 1;
}
break;
case AggregateType.Sum:
temprow[i][field.NewFieldName] = Add(temprow[i][field.NewFieldName], row[field.FieldName]);
break;
case AggregateType.Min:
temprow[i][field.NewFieldName] = Min(temprow[i][field.NewFieldName], row[field.FieldName]);
break;
case AggregateType.Max:
temprow[i][field.NewFieldName] = Max(temprow[i][field.NewFieldName], row[field.FieldName]);
break;
case AggregateType.Avg:
temprow[i][field.NewFieldName] = Avg(temprow[i][field.NewFieldName], row[field.FieldName], rowcount);
break;
}
}
}
}
return(dt);
}
