//protected void LoadYr2(Panel p, List<string> rows, List<string> cols, List<string> functions, string title, bool useGTE0, bool useFromTo)
//{
// DataTable dt = new DataTable();
// if (useFromTo)
// {
// dt = GetValantData_From_To2(rows, cols, functions, useGTE0);
// }
// else
// {
// //this branch no longer used
// //dt = GetValantData(rows, cols, functions, useGTE0);
// }
// LoadYr2(dt, p, rows, cols, functions, title, useGTE0, useFromTo);
//}
protected void LoadYr2(DataTable dt, Panel p, List <string> rows, List <string> cols, List <string> functions, string title, bool useGTE0, bool useFromTo)
{
//DataTable dt = new DataTable();
//if (useFromTo)
//{
// dt = GetValantData_From_To2(rows, cols, functions, useGTE0);
//}
//else
//{
// //this branch no longer used
// //dt = GetValantData(rows, cols, functions, useGTE0);
//}
List <string> keepCols = new List <string>();
keepCols.AddRange(rows);
keepCols.AddRange(cols);
PivotData cube;
PivotTable pivotTable;
string[] aggr_labels = new string[functions.Count];
if (functions.Count > 1)
{
IAggregatorFactory[] aggs = new IAggregatorFactory[functions.Count];
for (int f = 0; f < functions.Count; f++)
{
if (functions[f] == "Sum")
{
aggs[f] = new SumAggregatorFactory("n");
aggr_labels[f] = "sum n";
}
else if (functions[f] == "Monthly Avg")
{
aggs[f] = new AverageAggregatorFactory("amt");
aggr_labels[f] = "Monthly Avg";
}
else if (functions[f] == "N Patients")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID");
aggr_labels[f] = "N Patients";
}
else if (functions[f] == "N Patients Dx")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID_DX");
aggr_labels[f] = "N Patients Dx";
}
else if (functions[f] == "N Patients Tx")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID_TX");
aggr_labels[f] = "N Patients Tx";
}
else if (functions[f] == "N Visits")
{
aggs[f] = new SumAggregatorFactory("n_appt");
aggr_labels[f] = "N Visits";
}
else if (functions[f] == "N Visits Dx")
{
aggs[f] = new SumAggregatorFactory("n_appt_dx");
aggr_labels[f] = "N Visits Dx";
}
else if (functions[f] == "N Visits Tx")
{
aggs[f] = new SumAggregatorFactory("n_appt_Tx");
aggr_labels[f] = "N Visits Tx";
}
}
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
else
{
switch (functions[0])
{
case "Sum":
cube = new PivotData(keepCols.ToArray(), new SumAggregatorFactory("n"));
break;
case "Monthly Avg":
cube = new PivotData(keepCols.ToArray(), new AverageAggregatorFactory("amt"));
break;
case "N Patients":
cube = new PivotData(keepCols.ToArray(), new CountUniqueAggregatorFactory("patientID"));
break;
default:
cube = new PivotData(keepCols.ToArray(), new CountAggregatorFactory());
break;
}
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
var htmlResult = new StringWriter();
var pvtHtmlWr = new MyHtmlWriter2(htmlResult);
if (functions.Count >= 1)
{
pvtHtmlWr.FormatValue = (aggr, idx) =>
{
string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
return(newval);
};
//pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0}", aggr.Value); };
pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); };
}
else
{
//pvtHtmlWr.FormatValue(aggr, idx) =>
//{
// string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
// return newval;
//};
}
pvtHtmlWr.RowHeaderClass = "row1";
pvtHtmlWr.ColumnHeaderClass = "col1";
pvtHtmlWr.MeasureHeaderClass = "meashead";
pvtHtmlWr.SubtotalDimensions = new string[] { "Direct" };
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsColumn = false;
pvtHtmlWr.TotalsRow = true;
pvtHtmlWr.GrandTotal = false;
pvtHtmlWr.Write(pivotTable);
Label header = new Label();
header.Text = "<br/>" + title;
header.Font.Size = 12;
header.Font.Bold = true;
Literal lit = new Literal();
lit.Text = htmlResult.ToString();
p.Controls.Add(header);
p.Controls.Add(lit);
}
//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_TOTALS(List <string> list_budgetNbrs, List <string> list_fy, List <string> rows, List <string> cols, List <string> functions)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = new DataTable();
string sql_code;
if (1 == 1)
{
sql_code = "select 1 as const, * from fin.vwProjections " +
" where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and FY in ( '" + String.Join("','", list_fy) + "')" +
" and amttype not in ('Endowment','Revenue','BalancePrior','NonEndowment')";
}
//else if (ddlFY.SelectedValue.ToString() == "Custom")
//{
// sql_code = "select 1 as const, * from fin.vwOBJ_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 1 as const, * from fin.vwProjections " +
// " where budgetNbr in ('" + String.Join("','", list_budgetNbrs) + "') and BY='Current'";
//}
dt = sql.DataTable_from_SQLstring(sql_code);
sql.Close();
List <string> stats = new List <string> {
"Sum", "N"
};
List <string> keepCols = new List <string>();
keepCols.AddRange(rows);
keepCols.AddRange(cols);
//IAggregatorFactory[] aggs = new IAggregatorFactory[2];
//SumAggregatorFactory sumAggr = new SumAggregatorFactory("amt");
//CountAggregatorFactory countAggr = new CountAggregatorFactory();
//aggs[0] = sumAggr;
//aggs[1] = countAggr;
PivotData cube;
PivotTable pivotTable;
string[] aggr_labels = new string[functions.Count];
if (functions.Count > 1)
{
IAggregatorFactory[] aggs = new IAggregatorFactory[functions.Count];
for (int f = 0; f < functions.Count; f++)
{
if (functions[f] == "Sum")
{
aggs[f] = new SumAggregatorFactory("amt");
aggr_labels[f] = "Total";
}
else if (functions[f] == "Monthly Avg")
{
aggs[f] = new AverageAggregatorFactory("amt");
aggr_labels[f] = "Monthly Avg";
}
else if (functions[f] == "N Months")
{
aggs[f] = new CountUniqueAggregatorFactory("caldate");
aggr_labels[f] = "N Months";
}
else if (functions[f] == "Count")
{
aggs[f] = new CountAggregatorFactory();
aggr_labels[f] = "Count";
}
}
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
else
{
switch (functions[0])
{
case "Sum":
cube = new PivotData(keepCols.ToArray(), new SumAggregatorFactory("amt"));
break;
case "Monthly Avg":
cube = new PivotData(keepCols.ToArray(), new AverageAggregatorFactory("amt"));
break;
case "N Months":
cube = new PivotData(keepCols.ToArray(), new CountUniqueAggregatorFactory("caldate"));
break;
default:
cube = new PivotData(keepCols.ToArray(), new CountAggregatorFactory());
break;
}
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
//The following assumes that there is a PivotTable object named "pivotTable" that has been created
var pvtDataTableWr = new PivotTableDataTableWriter("out");
DataTable dtpivot = pvtDataTableWr.Write(pivotTable);
DataColumn colcumsum = new DataColumn("cumsum", typeof(decimal));
dtpivot.Columns.Add(colcumsum);
List <decimal> cumsum = new List <decimal>();
decimal runningtotal = 0;
for (int i = 0; i < dtpivot.Rows.Count; i++)
{
string val = dtpivot.Rows[i][2].ToString();
decimal d;
if (decimal.TryParse(val, out d))
{
d = (d == 0) ? 0 : d;
}
runningtotal += d;
cumsum.Add(runningtotal);
dtpivot.Rows[i]["cumsum"] = runningtotal;
}
var htmlResult = new StringWriter();
var pvtHtmlWr = new MyHtmlWriter2(htmlResult);
if (functions.Count >= 1)
{
pvtHtmlWr.FormatValue = (aggr, idx) =>
{
string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
return(newval);
};
//pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0}", aggr.Value); };
pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); };
}
else
{
//pvtHtmlWr.FormatValue(aggr, idx) =>
//{
// string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
// return newval;
//};
}
pvtHtmlWr.RowHeaderClass = "row1";
pvtHtmlWr.ColumnHeaderClass = "col1";
pvtHtmlWr.MeasureHeaderClass = "meashead";
pvtHtmlWr.SubtotalDimensions = new string[] { "Direct" };
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsColumn = true;
pvtHtmlWr.TotalsRow = true;
pvtHtmlWr.GrandTotal = true;
pvtHtmlWr.Write(pivotTable);
Label header = new Label();
header.Text = "<br/>Budget Totals";
header.Font.Size = 12;
header.Font.Bold = true;
Literal lit = new Literal();
lit.Text = htmlResult.ToString();
panel_TOTAL.Controls.Add(header);
panel_TOTAL.Controls.Add(lit);
ToggleVisibility("Totals");
//plot
if (chkPlot.Checked == true)
{
Literal totplot = PlotStackedCol_TOTAL_byBudgetNbr(dt);
panelPlots_TOTAL.Controls.Add(totplot);
}
}
