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