protected void btnExcel_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
DataTable fte = sql.DataTable_from_SQLstring("select * from tempFTE_with_NETID order by PayCycleEndDate, budget, budgetNbr, FirstMiddleLastName");
DataTable budgets = sql.DataTable_from_SQLstring("select budget, budgetNbr, budgetName, min(PayCycleEndDate) minDate, max(PayCycleEndDate) maxDate " +
", count(*) nrecs from tempFTE_with_NETID group by budget, budgetNbr, budgetName order by 1,2");
DataTable staff = sql.DataTable_from_SQLstring("select FirstMiddleLastName, UWnetid, min(PayCycleEndDate) minDate, max(PayCycleEndDate) maxDate " +
", count(distinct(budget)) n_budgets, count(*) as n_recs from tempFTE_with_NETID group by FirstMiddleLastName, UWnetid order by 1,2");
DataTable fte_proj = sql.DataTable_from_SQLstring("select vwFTE_FutureMo order by FirstMiddleLastName, budget, budgetName, budgetNbr");
DataSet ds = new DataSet();
ds.Tables.Add(fte);
ds.Tables.Add(fte_proj);
ds.Tables.Add(budgets);
ds.Tables.Add(staff);
ds.Tables[0].TableName = "FTE";
ds.Tables[1].TableName = "FTE_proj";
ds.Tables[2].TableName = "Budgets";
ds.Tables[3].TableName = "Staff";
string filename = "uwac_fte_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataSetToExcel(ds, filename, true, "xlsx");
}
protected void btnCreateExcel_Click(object sender, EventArgs e)
{
DataTable dt = GetAddressData();
string datetxt = SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, Master.Master_studyname + "_Addresses_" + datetxt, true, "xlsx");
}
protected void GUID_fileinfo(string stage)
{
SQL_utils sql = new SQL_utils();
string proc = "NDAR..spNDAR_WEB_GUID_to_create_MULTIPLE_by_study";
string mode = "_new_guid";
if (rblMode.SelectedValue == "convert")
{
proc = "NDAR..spNDAR_WEB_GUID_to_convert_INVGUID_MULTIPLE_by_study";
mode = "_convert_INVGUID";
}
string consent = "skip_consent";
if (chkConsent.Checked)
{
consent = "use_consent";
}
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int"));
ps.Add(sql.CreateParam("useconsent", consent, "text"));
DataSet ds = sql.DataSet_from_ProcName(proc, ps);
if (stage == "displayinfo")
{
GridView gv = new GridView();
gv.BorderStyle = BorderStyle.None;
gv.BorderColor = Color.White;
gv.DataSource = ds.Tables[0];
gv.DataBind();
panelGuidFileInfo.ContentTemplateContainer.Controls.Add(gv);
GridView gv2 = new GridView();
gv2.BorderStyle = BorderStyle.None;
gv2.BorderColor = Color.White;
gv2.DataSource = ds.Tables[1];
gv2.DataBind();
panelGuidFileInfo.ContentTemplateContainer.Controls.Add(gv2);
panelGuidFileInfo.Update();
}
else if (stage == "savefile")
{
string datetime = SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToFile(ds.Tables[0], Master.Master_studyname + mode + datetime, "csv");
}
sql.Close();
}
protected void btnGetIDList_Click(object sender, EventArgs e)
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
ps.Add(sql.CreateParam("study_csv", "all", "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps);
string filename = "code6_IDs_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, true, "csv");
sql.Close();
}
}
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();
}
}
}
