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 CreateXLS(DataSet mydset)
{
if (mydset.Tables.Count > 0)
{
SpreadsheetGearUtils.SaveDataSetToExcel(mydset, "gev", true, "xls");
}
else
{
lblInfo.Text = "No tables in dataset.";
}
}
protected void btnSave_Click1(object sender, EventArgs e)
{
DataSet dset = new DataSet();
dt_rows.TableName = "rows_and_data";
dt_cols.TableName = "cols";
dset.Tables.Add(dt_rows);
dset.Tables.Add(dt_cols);
SpreadsheetGearUtils.SaveDataSetToExcel(dset, "DB_diffs" + txtSchema.Text, true, "xlsx");
}
protected void ExportDictionaryForStudy()
{
Datadictionary dict = new Datadictionary(Master.Master_studyID, true);
if (dict.dt_dict.HasRows())
{
string dictfilename = String.Format("{0}_DataDictionary", Master.Master_studyname);
dict.dt_dict.TableName = "DataDictionary";
DataSet ds = new DataSet();
ds.Tables.Add(dict.dt_dict);
if (dict.dt_ndardict.HasRows())
{
dict.dt_ndardict.TableName = "NDA_DataDictionary";
ds.Tables.Add(dict.dt_ndardict);
}
SpreadsheetGearUtils.SaveDataSetToExcel(ds, dictfilename);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
LoadDset();
SpreadsheetGearUtils.SaveDataSetToExcel(dset, "norms_tables");
}
protected void btnDownload_Click(object sender, EventArgs e)
{
DataSet ds = GetNormsTables();
SpreadsheetGearUtils.SaveDataSetToExcel(ds, "norms_data", true, "xlsx");
}
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
SpreadsheetGearUtils.SaveDataSetToExcel(dset_results, "gev_search", true, "xlsx");
}
protected void LoadDescstats(string filename)
{
DataSet dset_descstats = new DataSet();
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
//convert to .xls here
try
{
dset_descstats = SpreadsheetGearUtils.GetDataSet(filename);
int g = 0;
}
catch (Exception)
{
string result = "Sorry, the file [" + filename + "] was unable to load. Create a new Excel file to generate the latest descriptive stats.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else
{
string data_filename = filename.Replace("DescStats", "Data");
DataSet dset = SpreadsheetGearUtils.GetDataSet(data_filename);
int dataproj_pk = Convert.ToInt32(Request.QueryString["pk"]);
if (dataproj_pk > 0 & dset != null & dset.Tables.Count > 0)
{
dset_descstats = utilDataProject.DataProject_Descstats(dataproj_pk, dset);
SpreadsheetGearUtils.SaveDataSetToExcel(dset_descstats, filename, false, "xlsx");
int g = 0;
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
if (dset_descstats != null & dset_descstats.Tables.Count > 0)
{
foreach (DataTable dt in dset_descstats.Tables)
{
if (dt.TableName == "fileinfo")
{
GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
panel0.Controls.Add(gv);
}
else
{
var dtSorted = dt.AsEnumerable()
.OrderBy(r => r.Field <string>("Measure"))
.ThenBy(r => r.Field <string>("Varname"))
.ThenBy(r => r.Field <string>("TimePoint"))
.CopyToDataTable();
GridView gv = new GridView();
gv.RowDataBound += gv_RowDataBound;
gv.AllowSorting = true;
gv.DataSource = dtSorted;
gv.DataBind();
gv.CssClass = "GridViewClass";
Label lbl = new Label();
lbl.Text = " By " + dt.TableName;
lbl.Font.Bold = true;
lbl.Font.Size = 12;
Literal lit = new Literal();
lit.Text = "<br/><br/>";
panel1.Controls.Add(lbl);
panel1.Controls.Add(gv);
panel1.Controls.Add(lit);
}
}
}
}