private PivotValue CalculateSummary(PivotValue pv, double val)
{
pv.Count++;
pv.Sum += val;
pv.Average = pv.Sum / pv.Count;
pv.Max = pv.Max > val ? pv.Max : val;
pv.Min = pv.Min < val ? pv.Min : val;
return(pv);
}
public double GetCellData(PivotValue pv, PivotGroupFunction grpFn)
{
switch (grpFn)
{
case PivotGroupFunction.Count:
return(pv.Count);
case PivotGroupFunction.Sum:
return(pv.Sum);
case PivotGroupFunction.Max:
return(pv.Max);
case PivotGroupFunction.Min:
return(pv.Min);
case PivotGroupFunction.Average:
case PivotGroupFunction.None:
default:
return(pv.Average);
}
}
public List <DynamicDictionary> PivotTheData(List <DynamicDictionary> sourceData)
{
DynamicDictionary disc = sourceData[0];
string mov = disc.GetValueAsString("mov").ToUpper().Trim();
try
{
SummaryOfData.Clear();
//manage or prepare distict of the values
double val = 0;
string valx = string.Empty, valy = string.Empty;
for (int i = 0, len = sourceData.Count; i < len; i++)
{
valx = sourceData[i].GetValueAsString(ColumnX.Name1);
valy = sourceData[i].GetValueAsString(ColumnY.Name2) + sourceData[i].GetValueAsString(ColumnY.Name1);
val = sourceData[i].GetValue <Double>(ColumnValue);
PivotData pd = GetPivotData(valx, valy);
pd.Value = CalculateSummary(pd.Value, val);
}
List <DynamicDictionary> dt = FinalPivotDictionary;
double tot = 0;
//now put the data in DataTable
foreach (string y in yKeys)
{
if (y.Trim().Length == 0)
{
continue;
}
IEnumerable <PivotData> yRows = SummaryOfData.Where(d => d.ValueY == y);
DynamicDictionary dr = new DynamicDictionary();
//prepare the row/record
dr[ColumnY.Name1] = y;
PivotValue pv_y = SummaryOfY[y];
foreach (PivotData pd in yRows)
{
val = GetCellData(pd.Value, ColumnY.SummaryGroupFunction);
dr[pd.ValueX] = val;
//calculate summary of Y
pv_y = CalculateSummary(pv_y, val);
}
foreach (string x in xKeys)
{
if (!dr.ContainsKey(x))
{
dr[x] = string.Empty;
}
}
tot = GetCellData(pv_y, ColumnY.SummaryGroupFunction);
dr[ColumnY.SummaryLabel] = tot;
//
dt.Add(dr);
//also calculating the grand total
GrandTotal += tot;
//Calculate total for arr and dep
if (mov == "DEP")
{
DepTotal = GrandTotal;
}
else
{
ArrTotal = GrandTotal - DepTotal;
}
}
DepTotal = GrandTotal;
//calculate the summary of X
if (ColumnY.CalculateSummary)
{
DynamicDictionary dr = new DynamicDictionary();
dr[ColumnY.Name1] = ColumnX.SummaryLabel;
foreach (string x in xKeys)
{
IEnumerable <PivotData> xRows = SummaryOfData.Where(d => d.ValueX == x);
PivotValue pv_x = SummaryOfX[x];
foreach (PivotData pd in xRows)
{
pv_x = CalculateSummary(pv_x, GetCellData(pd.Value, ColumnX.SummaryGroupFunction));
}
dr[x] = GetCellData(pv_x, ColumnX.SummaryGroupFunction);
}
if (ColumnX.CalculateSummary)
{
dr[ColumnX.SummaryLabel] = mov == "ARR" ? ArrTotal : DepTotal;
}
dt.Add(dr);
}
return(dt);
}
catch (Exception ex)
{
}
return(null);
}
public DataTable PivotTheData(DataTable sourceData)
{
try
{
//manage or prepare distict of the values
double val = 0;
string valx = string.Empty, valy = string.Empty;
SummaryOfData.Clear();
for (int i = 0, len = sourceData.Rows.Count; i < len; i++)
{
valx = GetStringValue(sourceData.Rows[i], ColumnX.Name1, ColumnY.Name2);
valy = GetStringValue(sourceData.Rows[i], ColumnY.Name1, ColumnY.Name2);
val = GetDoubleValue(sourceData.Rows[i], ColumnValue);
PivotData pd = GetPivotData(valx, valy);
pd.Value = CalculateSummary(pd.Value, val);
}
DataTable dt = FinalPivotDataTable;
double tot = 0;
//now put the data in DataTable
foreach (string y in yKeys)
{
if (y.Trim().Length == 0)
{
continue;
}
IEnumerable <PivotData> yRows = SummaryOfData.Where(d => d.ValueY == y);
DataRow dr = dt.NewRow();
dr[ColumnY.Name1] = y;
PivotValue pv_y = SummaryOfY[y];
foreach (PivotData pd in yRows)
{
val = GetCellData(pd.Value, ValueGroupFunction);
dr[pd.ValueX] = val;
//calculate summary of Y
pv_y = CalculateSummary(pv_y, val);
}
tot = GetCellData(pv_y, ColumnY.SummaryGroupFunction);
dr[ColumnY.SummaryLabel] = tot;
dt.Rows.Add(dr);
//also calculating the grand total
GrandTotal += tot;
}
//calculate the summary of X
if (ColumnY.CalculateSummary)
{
DataRow dr = dt.NewRow();
dr[ColumnY.Name1] = ColumnX.SummaryLabel;
foreach (string x in xKeys)
{
IEnumerable <PivotData> xRows = SummaryOfData.Where(d => d.ValueX == x);
PivotValue pv_x = SummaryOfX[x];
foreach (PivotData pd in xRows)
{
pv_x = CalculateSummary(pv_x, GetCellData(pd.Value, ValueGroupFunction));
}
dr[x] = GetCellData(pv_x, ColumnX.SummaryGroupFunction);
}
if (dt.Columns.Contains(ColumnX.SummaryLabel))
{
dr[ColumnX.SummaryLabel] = GrandTotal;
}
dt.Rows.Add(dr);
}
return(dt);
}
catch (Exception ex)
{
}
return(null);
}
