protected void btnExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = GetProjectTable();
dt.TableName = "Project";
DataRow dr = dt.NewRow();
dr[1] = "Total";
dr[2] = dt.Rows.Count;
decimal phdHrs = 0.0m, msHrs = 0.0m;
foreach (DataRow row in dt.Rows)
{
phdHrs += Convert.ToDecimal(row["PhdHrs"].ToString());
msHrs += Convert.ToDecimal(row["MsHrs"].ToString());
}
dr[16] = phdHrs;
dr[17] = msHrs;
dt.Rows.Add(dr);
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, "Project");
}
/// <summary>
/// Creates an excel file of the list of grants (based on the available, optional parameters of Principal Investigator,
/// QHS Faculty/Staff, Fund Status, and/or Internal/External grants) that have been displayed on the Tracking > Grant main page.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = GetGrantTable();
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, "Grant.xlsx");
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = CreateInvoiceTable(-1, true);
string fileValue = String.Format("Invoice-{0}.xlsx", txtInvoiceId.Value);
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, fileValue);
}
/// <summary>
/// Obtains the list of projects and exports it into excel format
/// for the purposes of RMATRIX (probably obsolete; refer to "Summary Reports" section instead).
/// </summary>
private void ExportTable()
{
DataTable dt;
if (ddlGrantType.SelectedItem.Text.Equals("RMATRIX"))
{
dt = GetRmatrixTable();
dt.TableName = "RMATRIX_Request";
}
else //if (ddlGrantType.SelectedItem.Text.Equals("Ola HAWAII"))
{
dt = GetOlaHawaiiTable();
dt.TableName = "Ola_HAWAII_Request";
}
dt.Columns.Remove("InvestId");
dt.Columns.Remove("Summary");
dt.Columns.Remove("IsPilot");
dt.Columns.Remove("PilotGrantName");
dt.Columns.Remove("BiostatId");
DataRow dr = dt.NewRow();
dr[1] = "Total";
dr[2] = dt.Rows.Count;
dt.Rows.Add(dr);
FileExport fileExport = new FileExport(this.Response);
if (ddlGrantType.SelectedItem.Text.Equals("RMATRIX"))
{
fileExport.ExcelExport(dt, "RMATRIXRequest");
}
else //if (ddlGrantType.SelectedItem.Text.Equals("Ola HAWAII"))
{
fileExport.ExcelExport(dt, "OlaHAWAIIRequest");
}
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = CreateAgreementDataTable(false);
int collabId = 0;
Int32.TryParse(ddlCollab.SelectedValue, out collabId);
string strCollab = collabId > 0 ? (ddlCollab.SelectedItem.Text.Split('|')[0]).Trim() : "all";
string fileValue = String.Format("Agreement-{0}.xlsx", strCollab);
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, fileValue);
}
/// <summary>
/// Obtains the list of projects and exports it into excel format
/// for the purposes of RMATRIX (probably obsolete; refer to "Summary Reports" section instead).
/// </summary>
private void ExportTable()
{
DataTable dt = GetRmatrixTable();
dt.TableName = "RMATRIX_Request";
dt.Columns.Remove("InvestId");
dt.Columns.Remove("Summary");
dt.Columns.Remove("IsPilot");
dt.Columns.Remove("PilotGrantName");
dt.Columns.Remove("BiostatId");
DataRow dr = dt.NewRow();
dr[1] = "Total";
dr[2] = dt.Rows.Count;
dt.Rows.Add(dr);
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, "RMATRIXRequest");
}
/// <summary>
/// NOT BEING USED. (Pulls separate report for RMATRIX Montly Reports; now uses independent report.)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnRmatrixMonthly_Click(object sender, EventArgs e)
{
//create csv file for download
if (Page.User.IsInRole("Admin"))
{
DateTime dtFrom, dtTo;
if (DateTime.TryParse(TextBoxInitialDate.Text, out dtFrom) && DateTime.TryParse(TextBoxCompleteDate.Text, out dtTo))
{
string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
var cmdText = ddlRmatrix.Value == "Monthly" ? "Rpt_RMATRIX_Monthly" : "Rpt_RMATRIX_Summary";
try
{
DataTable dt = new DataTable("tblRmatrix");
using (SqlConnection sqlcon = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, sqlcon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", dtFrom);
cmd.Parameters.AddWithValue("@EndDate", dtTo);
if (ddlRmatrix.Value == "Summary")
{
var affiliation = txtPIAffil.Text;
cmd.Parameters.AddWithValue("@Affiliation", affiliation);
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
FileExport fileExport = new FileExport(this.Response);
if (ddlRmatrix.Value == "Summary")
{
fileExport.ExcelExport(dt, cmdText);
}
else
{
fileExport.CsvExport(dt, dtFrom, download_token.Value);
}
}
catch (Exception ex)
{
//Response.Write(ex.ToString());
throw ex;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
GetProjectReport();
}
}
}
}
/// <summary>
/// Obtains report based on start/end date parameters and exports it into an excel file.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportExcel_Click(object sender, EventArgs e)
{
int reportId;
DateTime fromDate, toDate;
string fileName = "";
if (Int32.TryParse(ddlReport.SelectedValue, out reportId) &&
DateTime.TryParse(txtFromDate.Text, out fromDate) && DateTime.TryParse(txtToDate.Text, out toDate))
{
DataTable dt = new DataTable();
switch (reportId)
{
case 11: // (all) projects
dt = GetProjectTable(reportId, fromDate, toDate);
dt.TableName = "Project";
dt.Columns.Remove("HealthData");
dt.Columns.Remove("NonUH");
fileName = "Project" + toDate.ToString("yyyyMMdd");
break;
case 12: // Healthcare Data projects
dt = GetProjectTable(reportId, fromDate, toDate);
dt.TableName = "HealthData";
dt.Columns.Remove("HealthData");
dt.Columns.Remove("NonUH");
fileName = "HealthData" + toDate.ToString("yyyyMMdd");
break;
case 13: // NonUH projects
dt = GetProjectTable(reportId, fromDate, toDate);
dt.TableName = "NonUH";
dt.Columns.Remove("HealthData");
dt.Columns.Remove("NonUH");
fileName = "NonUH" + toDate.ToString("yyyyMMdd");
break;
case 14: // Publications
dt = GetPubTable(reportId, fromDate, toDate);
dt.TableName = "Publications";
dt.Columns.Remove("conf");
dt.Columns.Remove("ConfLoc");
dt.Columns.Remove("StartDate");
dt.Columns.Remove("EndDate");
dt.Columns.Remove("pp");
dt.Columns.Remove("GrantAffilName");
dt.Columns.Remove("FromDate");
dt.Columns.Remove("ToDate");
dt.Columns.Remove("CitationFormat");
dt.Columns.Remove("BiostatName");
fileName = "Publications" + toDate.ToString("yyyyMMdd");
break;
case 15: // AbstractsPresentations
dt = GetPubTable(reportId, fromDate, toDate);
dt.TableName = "AbstractsPresentations";
dt.Columns.Remove("JournalName");
dt.Columns.Remove("pp");
dt.Columns.Remove("pubYear");
dt.Columns.Remove("pubMonth");
dt.Columns.Remove("Volume");
dt.Columns.Remove("Issue");
dt.Columns.Remove("Page");
dt.Columns.Remove("DOI");
dt.Columns.Remove("PMID");
dt.Columns.Remove("PMCID");
dt.Columns.Remove("GrantAffilName");
dt.Columns.Remove("FromDate");
dt.Columns.Remove("ToDate");
dt.Columns.Remove("CitationFormat");
dt.Columns.Remove("BiostatName");
fileName = "AbstractsPresentations" + toDate.ToString("yyyyMMdd");
break;
case 16: // Academic
dt = GetAcademicTable(reportId, fromDate, toDate);
dt.TableName = "Academic";
dt.Columns.Remove("Column1");
dt.Columns.Remove("Column2");
fileName = "Academic" + toDate.ToString("yyyyMMdd");
break;
default:
break;
}
FileExport fileExport = new FileExport(this.Response);
fileExport.ExcelExport(dt, fileName);
}
}
