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"); } }
//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); }
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); } }