protected void btnSaveCSV_Click(object sender, EventArgs e)
{
string shortName = Request.QueryString["shortName"];
NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortName);
DataTable dt = NDAR.NDARDataStructureElements_to_DataTable(ds);
dt.TableName = "csv";
SpreadsheetGearUtils.SaveDataTableToFile(dt, "testit", "csv");
}
protected void LoadNDARview(string shortName, string studymeasIDs)
{
int studyID = sql.GetUserStudyID();
string IDexclude = txtIDexclude.Text;
try
{
gvNDARview.DataSource = null;
DataTable dt = NDAR.GetNDAR_view(shortName, studyID, IDexclude, studymeasIDs);
if (dt.Rows.Count > 0)
{
Session["NDARview"] = dt;
gridNDARview.DataSource = dt;
gridNDARview.DataBind();
gridNDARview.Visible = true;
gridNDARview.Columns[0].FixedStyle = DevExpress.Web.GridViewColumnFixedStyle.Left;
gridNDARview.Columns[1].FixedStyle = DevExpress.Web.GridViewColumnFixedStyle.Left;
gridNDARview.Columns[2].FixedStyle = DevExpress.Web.GridViewColumnFixedStyle.Left;
gridNDARview.Columns[3].FixedStyle = DevExpress.Web.GridViewColumnFixedStyle.Left;
gridNDARview.Columns[4].FixedStyle = DevExpress.Web.GridViewColumnFixedStyle.Left;
//count rows where reason_exclude is not null
int n_reason_exclude_NotNull = dt.AsEnumerable().Where(f => f.Field <string>("reason_exclude") != "").Count();
int n_reason_exclude_IsNull = dt.Rows.Count - n_reason_exclude_NotNull;
v5.Text = dt.Rows.Count.ToString();
v5.ForeColor = Color.Navy;
v6.Text = n_reason_exclude_IsNull.ToString();
v6.ForeColor = Color.Navy;
v7.Text = n_reason_exclude_NotNull.ToString();
v7.ForeColor = Color.DarkRed;
}
else
{
gvNDARview.Visible = false;
v5.Text = "This query returned 0 records.";
v5.ForeColor = Color.Red;
}
}
catch (Exception ex)
{
//To show the query
string sqlout = NDAR.GetSQL_for_NDAR_view(shortName, studyID, IDexclude, studymeasIDs);
lblInfo.Text = ex.Message + "<br/><br/>" + sqlout;
//string sqlout = NDAR.GetSQL_for_NDAR_studymeas_to_include(shortName, studyID, 0);
//lblInfo.Text = "<br/><br/>" + sqlout;
}
}
protected void btnDisplayFromNDAR_OnClick(object sender, EventArgs e)
{
string shortname = txtNDAR_ShortName.Value.ToString();
DataTable dt = NDAR.LoadDataStructure(shortname);
int n = dt.Rows.Count;
Session["shortname"] = shortname;
Session["StructureFromNDAR"] = dt;
gridDS.DataBind();
btnImportFromNDAR.Visible = true;
btnImportFromNDAR.ClientVisible = true;
}
protected void LoadMatchedFlds(string shortName)
{
SQL_utils sql = new SQL_utils("data");
DataTable dt = NDAR.NDAR_DSE_with_Matching_UWFlds(shortName);
if (dt.HasRows())
{
Session["ndar_matchedflds"] = dt;
gridMatched.DataSource = dt;
gridMatched.DataBind();
}
sql.Close();
}
protected DataTable LoadStructureFromNDAR(string shortname)
{
DataTable dt_ds;
object o = Session["StructureFromNDAR"];
if (o != null)
{
dt_ds = (DataTable)o;
}
else
{
dt_ds = NDAR.LoadDataStructure(shortname);
}
return(dt_ds);
}
protected DataTable LoadStructuresFromNDAR()
{
DataTable dt_fulllist;
object o = Session["StructuresFromNDAR"];
if (o != null)
{
dt_fulllist = (DataTable)o;
}
else
{
dt_fulllist = NDAR.LoadFullDataStructureList("NDAR");
}
return(dt_fulllist);
}
protected int LoadDataStructureInfo(string shortName)
{
int return_value = 0;
//Don't need to retrieve all the DSE yet.
//DataTable dt = NDAR.NDAR_DSE_from_DB(shortName);
//instead, just get the count of DSE
int n_dse = NDAR.Get_Count_NDAR_DSE_from_DB(shortName);
if (shortName != "")
{
string source = "UWAC DB";
NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortName);
if (ds == null)
{
return_value++;
lblInfo.Text = "**ERROR** '" + shortName + "' is inaccessible from the NDAR API.<br/>";
}
//Get the associated table
string uwtable = sql.StringScalar_from_SQLstring("select coalesce(uwtable, 'NONE') uwtable from NDAR_DS where shortname = '" + shortName + "'");
if (uwtable == null)
{
return_value++;
lblInfo.Text += "**ERROR** No linked UW table found for '" + shortName + "'.<br/>";
}
v0.Text = sql.GetUserStudyName();
v1.Text = shortName;
v2.Text = (ds != null) ? ds.title : "";
v3.Text = n_dse.ToString();
v4.Text = source;
return(return_value);
}
else
{
return(0);
}
}
protected void LoadDataStructures(string source)
{
string where = txtWhere.Text;
DataTable dt = new System.Data.DataTable();
SQL_utils sql = new SQL_utils("data");
if (chkInStudy.Checked)
{
//UWAutism uwac = new UWAutism();
//int studyID = uwac.Master_studyID;
int studyID = sql.GetUserStudyID();
dt = NDAR.LoadFullDataStructureList(source, where, chkUWview.Checked, studyID);
}
else
{
dt = NDAR.LoadFullDataStructureList(source, where, chkUWview.Checked);
}
string code_no_ndar = String.Format("select * from uwautism_research_backend..tblMeasure where " +
" measureID in (select measureID from uwautism_research_backend..tblStudyMeas where studyID={0}) and measureID not in " +
" ( select measureID from def.Tbl a " +
" join NDAR_DS b ON a.tblname = b.uwtable where measureID>0) "
, Master.Master_studyID);
DataTable dt_no_ndar = sql.DataTable_from_SQLstring(code_no_ndar);
sql.Close();
gridNoNDAR.DataSource = dt_no_ndar;
gridNoNDAR.DataBind();
//gvScroll.DataSource = dt;
//gvScroll.DataBind();
Session["datastructures"] = dt;
grid.DataSource = dt;
grid.DataBind();
lblN.Text = "No. of Data Structures: " + dt.Rows.Count.ToString();
}
protected void Process_Button_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "LoadData")
{
int DatStructureInfo_isOK = LoadDataStructureInfo(shortName);
if (DatStructureInfo_isOK == 0)
{
// UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelStudymeas.FindControlRecursive("smID");
try
{
UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelStudymeas.FindControlRecursive("studymeasID");
string studymeasIDs = p.GetChecked_as_CSV();
if (studymeasIDs == "")
{
//string message = "alert('Please choose which study measures to include.');";
//ScriptManager.RegisterClientScriptBlock(sender as Control, this.GetType(), "alert", message, true);
lblPleaseSelect.Visible = true;
}
else
{
lblPleaseSelect.Visible = false;
LoadNDARview(shortName, studymeasIDs);
btnSaveNDARCSV.Visible = true;
if (chkShowSQLsyntax.Checked)
{
string sqlout = NDAR.GetSQL_for_NDAR_view(shortName, Master.Master_studyID, studymeasIDs);
lblSQLsyntax.Text = "<b><u>SQL syntax for creating NDAR data for '" + shortName + "':</u></b><br/><br/>" + sqlout;
}
}
}
catch (Exception)
{ }
}
}
}
protected string SaveNDARCSV(string shortName, string IDexclude, string studymeasIDs, string filename, int studyID)
{
bool savedCSV = false;
DataTable dt = NDAR.GetNDAR_view(shortName, studyID, 0, false, IDexclude, studymeasIDs);
dt.TableName = shortName;
if (dt.HasRows())
{
string filename_result = SpreadsheetGearUtils.WriteDataTableAsCSV(dt, filename, "csv", true);
filename_result += String.Format("|{0}", dt.Rows.Count);
return(filename_result);
}
else
{
return(String.Format("ERROR: No rows for {0}|0", shortName));
}
}
protected void btnSaveNDARCSV_Click(object sender, EventArgs e)
{
UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelStudymeas.FindControlRecursive("studymeasID");
string studymeasIDs = p.GetChecked_as_CSV();
string shortName = Request.QueryString["shortName"];
int studyID = sql.GetUserStudyID();
string studyname = sql.GetUserStudyName();
string IDexclude = txtIDexclude.Text;
DataTable dt = NDAR.GetNDAR_view(shortName, studyID, 0, false, IDexclude, studymeasIDs);
dt.TableName = shortName;
string now = DateTime.Now.ToString("yyyyMMdd__HH_mm");
string filename = studyname + "_" + shortName + "_" + now;
SpreadsheetGearUtils.SaveDataTableToFile(dt, filename, "csv", true);
}
protected void OpenAllViewdataTabs(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("data");
string where = txtWhere.Text;
int studyID = sql.GetUserStudyID();
DataTable dt = NDAR.LoadFullDataStructureList("localDB", where, chkUWview.Checked, studyID);
int counter = 0;
foreach (DataRow row in dt.Rows)
{
string shortName = row["shortName"].ToString();
string url = "NDARview.aspx?shortName=";
string url2 = String.Format("window.open('{0}{1}','_blank');", url, shortName);
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), String.Format("key{0}", counter), url2, true);
counter++;
}
}
//protected void btnSave_Click(object sender, EventArgs e)
//{
// DataTable dt = NDAR.LoadFullDataStructureList("NDAR");
// SaveAllDataStructures(dt);
//}
//protected void SaveAllDataStructures(DataTable dt)
//{
// SQL_utils sql = new SQL_utils();
// SqlParameter p = sql.CreateParam("NDAR_DS", dt);
// sql.NonQuery_from_ProcName("spNDAR_Insert_DS", p);
//}
protected void gvScroll_RowCommand(object sender, GridViewCommandEventArgs e)
{
//save DSE here
if (e.CommandName == "Import flds")
{
NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(e.CommandArgument.ToString());
DataTable dse = NDAR.NDARDataStructureElements_to_DataTable(ds);
//lblInfo.Text = "nrow = {" + dse.Rows.Count.ToString() + "}";
SQL_utils sql = new SQL_utils();
SqlParameter p = sql.CreateParam("NDAR_DSE", dse);
sql.NonQuery_from_ProcName("spNDAR_Insert_DSE", p);
//GridView gv_Fields = new GridView();
//gv_Fields.DataSource = dse;
//gv_Fields.DataBind();
//Panel_dataElements.Controls.Add(gv_Fields);
//Panel_dataElements.Visible = true;
}
}
protected void SaveDSToDB(string shortname)
{
string results = NDAR.SaveToDB(shortname);
lblManualImport_results.Text = results;
}
protected void btnSave_Click(object sender, EventArgs e)
{
DataTable dt = NDAR.LoadFullDataStructureList("NDAR");
SaveAllDataStructures(dt);
}
protected void LoadDataStructure(string shortName)
{
SQL_utils sql = new SQL_utils("data");
string source = "UWAC DB";
NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortName);
DataTable dt = NDAR.NDAR_DSE_from_DB(shortName);
//Get the associated table
string uwtable = sql.StringScalar_from_SQLstring("select coalesce(uwtable, 'NONE') uwtable from NDAR_DS where shortname = '" + shortName + "'");
if (dt.Rows.Count == 0)
{
dt = NDAR.NDARDataStructureElements_to_DataTable(ds);
source = "NDAR";
}
v1.Text = ds.shortName;
v2.Text = ds.title;
v3.Text = ds.dataElements.Count.ToString();
v4.Text = source;
string ndar_datadict_string = @"https://ndar.nih.gov/ndar_data_dictionary.html?short_name=" + shortName;
//linkNDAR_DataDict.PostBackUrl = ndar_datadict_string;
//linkNDAR_DataDict.Text = shortName;
dynLink.NavigateUrl = ndar_datadict_string;
dynLink.Text = shortName;
Session["ndar_dataElements"] = dt;
griddataElements.DataSource = dt;
griddataElements.DataBind();
if (uwtable != "NONE")
{
LoadUWfields(uwtable);
ddlUWtables.Visible = false;
btnShowUWtable.Visible = false;
btnLinkUWtable.Visible = false;
LoadMatchedFlds(shortName);
}
else
{
Panel_UWtable.Visible = false;
}
//List<SqlParameter> ps = new List<SqlParameter>();
//ps.Add(sql.CreateParam("shortname", shortName, "text"));
//ps.Add(sql.CreateParam("mode", "count", "text"));
//DataTable dt_uwflds = sql.DataTable_from_ProcName("def.spNDAR_matching_UWflds", ps);
//GridView gv = new GridView();
//gv.DataSource = dt_uwflds;
//gv.DataBind();
//panel_UWfldsinfo.Controls.Add(gv);
sql.Close();
}
