//This takes a DataTable and returns a PivotTable on a given field (var_to_process) by a list of Rows and Cols
public static PivotTable DataTable_to_PivotTable(DataTable dtStacked, List <string> pivotRows, List <string> pivotCols, string fldname, List <AggrFx> requested_stats)
{
if (dtStacked.Rows.Count > 0)
{
List <string> keepCols = new List <string>();
keepCols.AddRange(pivotRows);
keepCols.AddRange(pivotCols);
keepCols.Add(fldname);
IAggregatorFactory[] aggs = new IAggregatorFactory[requested_stats.Count];
CountAggregatorFactory aggrN = new CountAggregatorFactory();
AverageAggregatorFactory aggrM = new AverageAggregatorFactory(fldname);
VarianceAggregatorFactory aggrSD = new VarianceAggregatorFactory(fldname, VarianceAggregatorValueType.StandardDeviation);
MinAggregatorFactory aggrMin = new MinAggregatorFactory(fldname);
MaxAggregatorFactory aggrMax = new MaxAggregatorFactory(fldname);
SumAggregatorFactory aggrSum = new SumAggregatorFactory(fldname);
ConcatAggregatorFactory aggrConcat = new ConcatAggregatorFactory(fldname);
CountUniqueAggregatorFactory aggrNunq = new CountUniqueAggregatorFactory(fldname);
Concat2AggregatorFactory aggrConcat2 = new Concat2AggregatorFactory(fldname);
int counter = 0;
foreach (AggrFx s in requested_stats)
{
if (s == AggrFx.M)
{
aggs[counter] = aggrM;
}
else if (s == AggrFx.SD)
{
aggs[counter] = aggrSD;
}
else if (s == AggrFx.N)
{
aggs[counter] = aggrN;
}
else if (s == AggrFx.Nunq)
{
aggs[counter] = aggrNunq;
}
else if (s == AggrFx.Min)
{
aggs[counter] = aggrMin;
}
else if (s == AggrFx.Max)
{
aggs[counter] = aggrMax;
}
else if (s == AggrFx.Sum)
{
aggs[counter] = aggrSum;
}
else if (s == AggrFx.Concat)
{
aggs[counter] = aggrConcat;
}
else if (s == AggrFx.Concat2)
{
aggs[counter] = aggrConcat2;
}
counter++;
}
if (aggs.Length > 1)
{
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
var cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dtStacked));
var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension
var pivotTable = new PivotTable(
pivotRows.ToArray(), // row dimension(s)
pivotCols.ToArray(), // column dimension(s)
cube);
return(pivotTable);
}
else
{
var cube = new PivotData(keepCols.ToArray(), aggs[0]);
cube.ProcessData(new DataTableReader(dtStacked));
var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension
var pivotTable = new PivotTable(
pivotRows.ToArray(), // row dimension(s)
pivotCols.ToArray(), // column dimension(s)
cube);
return(pivotTable);
}
}
else
{
return(null);
}
}
protected void LoadProjections_FTE(List <string> list_budgetNbrs, List <string> list_fy)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = new DataTable();
string sql_code;
if (1 == 1)
{
sql_code = "select * from fin.vwFTE_Previous_and_Projections " +
" where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and FY in ( '" + String.Join("','", list_fy) + "')";
}
//else if (ddlFY.SelectedValue.ToString() == "Custom")
//{
// sql_code = "select * from fin.vwFTE_Previous_and_Projections " +
// " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and mo >= '" + txtFrom.Text + "' and mo <= '" + txtTo.Text + "'";
//}
//else if (list_fy.Contains("Current Budget Yr"))
//{
// sql_code = "select * from fin.vwFTE_Previous_and_Projections " +
// " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and mo >= begdate and mo <= enddate ";
//}
dt = sql.DataTable_from_SQLstring(sql_code);
sql.Close();
if (dt.Rows.Count == 0)
{
}
else
{
//Name, budget, a.budgetNbr, futuremo, Salary, FringeRate
List <string> rows = new List <string> {
"Name"
};
//List<string> cols = new List<string> { "budgetNbr", "budgetName", "BegDate", "EndDate", "PastProj" };
List <string> cols = new List <string> {
"budgetNbr", "budgetName", "FY", "PastProj"
};
List <string> stats = new List <string> {
"Sum", "Concat"
};
#region by hand
List <string> keepCols = new List <string>();
keepCols.AddRange(rows);
keepCols.AddRange(cols);
SumAggregatorFactory sumAggr = new SumAggregatorFactory("Total_prev_mo");
ConcatAggregatorFactory concatAggr = new ConcatAggregatorFactory("FTEcode");
CountAggregatorFactory countAggr = new CountAggregatorFactory();
IAggregatorFactory[] aggs = new IAggregatorFactory[3];
aggs[0] = sumAggr;
aggs[1] = concatAggr;
aggs[2] = countAggr;
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
var cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dt));
PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
#endregion
var htmlResult = new StringWriter();
var pvtHtmlWr = new MyHtmlWriter2(htmlResult);
string[] aggr_labels = new string[] { "Sal + Bene", "FTE", "N mos" };
//pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0.##}", aggr.Value); };
pvtHtmlWr.FormatValue = (aggr, idx) =>
{
if (aggr.GetType() == typeof(ConcatAggregator))
{
int nchars = aggr.Value.ToString().Distinct().Count();
if (nchars > 1)
{
string newvalue = String.Format("<div class=\"FTEchange\">{0}</div>", aggr.Value);
return(newvalue);
}
else
{
return(String.Format("{0}", aggr.Value));
}
}
else
{
return(String.Format("{0:0.##}", aggr.Value));
}
};
pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); };
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsColumn = true;
pvtHtmlWr.TotalsRow = true;
pvtHtmlWr.GrandTotal = true;
pvtHtmlWr.Write(pivotTable);
Label header = new Label();
header.Text = "<br/><br/>Staff Expenses";
header.Font.Size = 12;
header.Font.Bold = true;
Literal lit = new Literal();
string processedHTML = htmlResult.ToString().Replace("<", "<");
processedHTML = processedHTML.Replace(">", ">");
processedHTML = processedHTML.Replace("<td class=\" pvtMeasure1\">", "<td class=\"foo2\">");
lit.Text = processedHTML;
panel_FTE.Controls.Add(header);
panel_FTE.Controls.Add(lit);
ToggleVisibility("Totals_FTE");
}
}
