protected Literal PlotStackedCol_TOTAL_byBudgetNbr(DataTable dt) { List <string> rows = new List <string> { "ObjDesc", "caldate" }; //List<string> cols = new List<string> { "budgetNbr", "budgetName", "BegDate", "EndDate", "PastProj" }; List <string> cols = new List <string> { "const" }; List <string> stats = new List <string> { "Sum" }; List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); SumAggregatorFactory sumAggr = new SumAggregatorFactory("amt"); var cube = new PivotData(keepCols.ToArray(), sumAggr); cube.ProcessData(new DataTableReader(dt)); PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); PivotTableDataTableWriter foo = new PivotTableDataTableWriter("foo"); DataTable dt_totals = foo.Write(pivotTable); string[] series_vals = new string[1] { "ObjDesc" }; PivotTable pivotTable_series = new PivotTable(series_vals, null, cube); PivotTableDataTableWriter foo2 = new PivotTableDataTableWriter("foo2"); DataTable dt_series = foo2.Write(pivotTable_series); //DataView dv = //DataTable dt_series2 = sql.DataTable_from_SQLstring("select Name from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' group by Name"); //DataTable dtraw2 = sql.DataTable_from_SQLstring("select * from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' and calcFTE>0 order by Name"); //string x1 = utilCharts.Highcharts_StackedColumn() Literal lit2 = new Literal(); string x2 = utilCharts.Highcharts_StackedColumn(dt_series, dt_totals, "caldate", "1[trial]", "ObjDesc", "ObjDesc", "chart1", "charttitle", "Month", "Totals", -999, -999, -999, -999, 700, 400); lit2.Text += x2; ////panel2.Controls.Add(lit2); //sql.Close(); return(lit2); }
protected void ShowCode6Wide() { int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString()); if (labID > 0) { //var x = lstStudy.SelectedValue; var x2 = gridStudy.GetSelectedFieldValues("studyID"); if (x2.Count == 0) //if (lstStudy.SelectedValue == "") { lblSelectOne.Text = "Select some studies."; lblSelectOne.Visible = true; } else if (chkDisplayOnPage.Checked == false && chkXLSX.Checked == false) { lblSelectOne.Text = "Select the output method."; lblSelectOne.Visible = true; } else { SQL_utils sql = new SQL_utils("backend"); string study_csv = String.Join(",", x2); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("labID", labID.ToString(), "int")); //ps.Add(sql.CreateParam("study_csv", lstStudy.SelectedValue, "text")); ps.Add(sql.CreateParam("study_csv", study_csv, "text")); DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps); PivotTable pivotTable = utilPivotTable.DataTable_to_PivotTable(dt, new List <string> { "code6" }, new List <string> { "source_of_ID" }, "ID", new List <utilPivotTable.AggrFx> { utilPivotTable.AggrFx.Concat2 }); lblSelectOne.Visible = false; if (chkDisplayOnPage.Checked == true) { //Write back to page var htmlResult = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); pvtHtmlWr.TotalsColumn = false; pvtHtmlWr.TotalsRow = false; pvtHtmlWr.GrandTotal = false; pvtHtmlWr.Write(pivotTable); Literal xtab = new Literal(); xtab.Text = htmlResult.ToString(); panel_wide.Controls.Add(xtab); } if (chkXLSX.Checked == true) { var dataTblWr = new PivotTableDataTableWriter("Test"); //.Write(pivotTable); var tbl = dataTblWr.Write(pivotTable); string filename = "code6_IDs_wide_" + SpreadsheetGearUtils.DateTime_for_filename(); SpreadsheetGearUtils.SaveDataTableToExcel(tbl, filename, true, "xlsx"); } sql.Close(); } } }
private DataTable GetWeeklyHoursByEmployee() { DataRow dr; BuildEmpTable(); //dtTeam = objADM.LoadUsers(ddlLocation.SelectedItem.Text); //if (dtTeam.Rows.Count > 0) //{ // foreach (DataRow drTeam in dtTeam.Rows) // { // foreach (var item in lstDate.Chunks) // { // dr = dtResult.NewRow(); // dr["Team"] = drTeam["T_TeamName"]; // dr["User"] = drTeam["UL_User_Name"].ToString().Trim(); // dr["Date"] = item.ToString().Split('-')[0]; // dt = objADM.GetTotalWorkHours("user", item.ToString().Split('-')[0], item.ToString().Split('-')[1], drTeam["UL_User_Name"].ToString()); // if (dt.Rows.Count > 0) // { // if (dt.Rows[0]["Total"].ToString() == "0") // { // continue; // } // dr["Avg"] = Math.Round(Convert.ToDouble(dt.Rows[0]["Total"].ToString()), 2, MidpointRounding.AwayFromZero); // } // dtResult.Rows.Add(dr); // } // } //} foreach (var item in lstDate.Chunks) { dt = objADM.GetUserTotal(item.ToString().Split('-')[0], item.ToString().Split('-')[1], ddlLocation.SelectedItem.Text); if (dt.Rows.Count > 0) { foreach (DataRow drRow in dt.Rows) { dr = dtResult.NewRow(); if (string.IsNullOrEmpty(drRow["Team1"].ToString())) { dr["Team"] = drRow["Team2"]; } else { dr["Team"] = drRow["Team1"]; } if (string.IsNullOrEmpty(drRow["user1"].ToString())) { dr["User"] = drRow["user2"].ToString().Trim(); } else { dr["User"] = drRow["user1"].ToString().Trim(); } dr["Date"] = item.ToString().Split('-')[0]; dr["Avg"] = Math.Round(Convert.ToDecimal(drRow["Total"]), 2, MidpointRounding.AwayFromZero); dtResult.Rows.Add(dr); } } } //if (dtResult.Rows.Count > 0) //{ // DataView dv = dtResult.DefaultView; // dv.Sort = "[Date] asc, [Team] asc"; // dtResult = dv.ToTable(); //} var ordersPvtData = new PivotData(new[] { "Team", "User", "Date" }, new SumAggregatorFactory("Avg")); ordersPvtData.ProcessData(new DataTableReader(dtResult)); var pvtTbl = new PivotTable( new[] { "Team", "User" }, //rows new[] { "Date" }, // columns ordersPvtData, new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }), new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }) ); var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable"); DataTable res = pvtDataTableWr.Write(pvtTbl); res.Columns[0].ColumnName = "Team"; res.Columns[1].ColumnName = "User"; return(res); }
private void ExportReporttoPDF() { if (DateTime.TryParse(txtFrom.Text, out temp)) { } else { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "from date", "alert('Please select valid from date')", true); return; } if (DateTime.TryParse(txtTo.Text, out temp)) { } else { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "to date", "alert('Please select valid to date')", true); return; } if (Convert.ToDateTime(txtFrom.Text) > Convert.ToDateTime(txtTo.Text)) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "to date", "alert('To date should be greated than from date')", true); return; } int weeks = Convert.ToInt32((Convert.ToDateTime(txtTo.Text) - Convert.ToDateTime(txtFrom.Text)).TotalDays / 7); string from = Convert.ToDateTime(txtFrom.Text).ToString("MM/dd/yyyy"); for (int i = 0; i <= weeks - 1; i++) { string to = Convert.ToDateTime(from).AddDays(6).ToString("MM/dd/yyyy"); if (Convert.ToDateTime(to) > Convert.ToDateTime(txtTo.Text)) { break; } lstDate.Add(from + "-" + to); from = Convert.ToDateTime(to).AddDays(1).ToString("MM/dd/yyyy"); } DataRow dr; BuildTeamTable(); //double count = 0; double average = 0; //dtTeam = objADM.LoadTeams(ddlLocation.SelectedItem.Text); //if (dtTeam.Rows.Count > 0) //{ // foreach (DataRow drTeam in dtTeam.Rows) // { // dt = objADM.GetUserCount(drTeam["T_ID"].ToString()); // if (dt.Rows.Count > 0) // { // if (!string.IsNullOrEmpty(dt.Rows[0]["Count"].ToString())) // { // count = Convert.ToDouble(dt.Rows[0]["Count"]); // } // } // foreach (var item in lstDate.Chunks) // { // dr = dtResult.NewRow(); // dr["Team"] = drTeam["T_TeamName"]; // dr["Date"] = item.ToString().Split('-')[0]; // dt = objADM.GetTotalWorkHours("team", item.ToString().Split('-')[0], item.ToString().Split('-')[1], drTeam["T_ID"].ToString()); // if (dt.Rows.Count > 0) // { // if (dt.Rows[0]["Total"].ToString() == "0") // { // continue; // } // if (count > 0) // { // average = (Convert.ToDouble(dt.Rows[0]["Total"])) / count; // } // } // dr["Avg"] = Math.Round(average, 2, MidpointRounding.AwayFromZero); // if (average > 0) // { // dtResult.Rows.Add(dr); // } // average = 0; // } // count = 0; // } //} foreach (var item in lstDate.Chunks) { dt = objADM.GetTeamTotal(item.ToString().Split('-')[0], item.ToString().Split('-')[1], ddlLocation.SelectedItem.Text); if (dt.Rows.Count > 0) { foreach (DataRow drRow in dt.Rows) { dr = dtResult.NewRow(); if (string.IsNullOrEmpty(drRow["Team1"].ToString())) { dr["Team"] = drRow["Team2"]; } else { dr["Team"] = drRow["Team1"]; } dr["Date"] = item.ToString().Split('-')[0]; dtUsers = objADM.GetUserCount(dr["Team"].ToString()); if (dtUsers.Rows.Count > 0) { average = Convert.ToDouble(drRow["Total"]) / Convert.ToDouble(dtUsers.Rows[0]["Count"].ToString()); dr["Avg"] = Math.Round(average, 2, MidpointRounding.AwayFromZero); } average = 0; dtResult.Rows.Add(dr); } } } //if (dtResult.Rows.Count > 0) //{ // DataView dv = dtResult.DefaultView; // dv.Sort = "[Date] asc, [Team] asc"; // dtResult = dv.ToTable(); //} var ordersPvtData = new PivotData(new[] { "Team", "Date" }, new SumAggregatorFactory("Avg")); ordersPvtData.ProcessData(new DataTableReader(dtResult)); var pvtTbl = new PivotTable( new[] { "Team" }, //rows new[] { "Date" }, // columns ordersPvtData, new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }), new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }) ); var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable"); DataTable res = pvtDataTableWr.Write(pvtTbl); res.Columns[0].ColumnName = "Team"; ExportToPDF(res); }
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); } }
protected DataTable GetYear(DataTable dt, List <string> rows, List <string> cols, bool showCount) { // These are all the fields that are going to be used in the cube List <string> keepCols = new List <string>(); keepCols.AddRange(rows); keepCols.AddRange(cols); // Create the cube PivotData cube; // = new PivotData(keepCols.ToArray(), compositeFactory); //Create the aggregators you want (Sum, Avg, Count, Min, Max, etc.) SumAggregatorFactory sumAggr = new SumAggregatorFactory("sumTotal"); LastAggregatorFactory balAggr = new LastAggregatorFactory("cumTot"); if (false) { // Put them into a composite IAggregatorFactory[] aggs = new IAggregatorFactory[2]; aggs[0] = sumAggr; aggs[1] = balAggr; CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs); // Create the cube cube = new PivotData(keepCols.ToArray(), compositeFactory); } else { SumAggregatorFactory sumFactory = new SumAggregatorFactory("sumTotal"); // Create the cube cube = new PivotData(keepCols.ToArray(), sumFactory); } cube.ProcessData(new DataTableReader(dt)); //Create pivot table PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube); //Define how we are going to render the pivot table - here as HTML (could be Excel, CSV, etc.) var htmlResult = new StringWriter(); //var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); var pvtHtmlWr = new MyHtmlWriter(htmlResult); // Define the labels for the dimensions //string[] aggr_labels = new string[] { "Sum", "Count" }; string[] aggr_labels = new string[] { "Sum", "Cumulative" }; // Various properties pvtHtmlWr.SubtotalDimensions = new string[] { "Sum", "Cumulative" }; pvtHtmlWr.SubtotalRows = true; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.SubtotalColumns = true; pvtHtmlWr.TotalsColumn = true; pvtHtmlWr.TotalsRow = true; pvtHtmlWr.GrandTotal = false; //create the actual HTML code pvtHtmlWr.Write(pivotTable); //Place the HTML code into literal control and add it to a Panel so you can place it where you wish on the page. Literal lit = new Literal(); lit.Text = htmlResult.ToString(); //panel1.Controls.Add(lit); //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); return(FormatYear(dtpivot)); }