protected void LoadData()
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
DataTable dt_elig = sql.DataTable_from_SQLstring("select * from avh.eligibility");
DataTable dt_survey = sql.DataTable_from_SQLstring("select * from avh.survey");
DataTable dt_surveyflds = sql.DataTable_from_SQLstring("select q_id, b.tblpk, b.fldpk, fldname, stem, fieldlabel from avh.questions_def_flds a join def.fld b ON a.tblpk=b.tblpk and a.fldpk=b.fldpk");
DataTable dt730 = sql.DataTable_from_SQLstring("select * from all_sds");
sql.Close();
int n_elig = dt_elig.Rows.Count;
int n_survey = dt_survey.Rows.Count;
int n_flds = dt_surveyflds.Rows.Count;
grid.DataSource = dt_surveyflds;
grid.DataBind();
List <int> tblpks = dt_surveyflds.AsEnumerable().Select(f => f.Field <int>("tblpk")).Distinct().ToList();
foreach (int tblpk in tblpks)
{
Debug.WriteLine("/***************************************************************************/");
Debug.WriteLine("/***************************************************************************/");
//CreateViewSyntax(tblpk);
//if(tblpk==731)
BulkInsert(tblpk);
}
}
protected void LoadAdvEvents()
{
if (Master.Master_studyID == 1076)
{
lblStudyname.Text = Master.Master_studyname + " (select Tadpole_TX above to view these)";
}
else
{
lblStudyname.Text = Master.Master_studyname;
}
SQL_utils sql = new SQL_utils();
DataTable dt = sql.DataTable_from_SQLstring("select * from vwALL_AdverseEvents where studyID = " + Master.Master_studyID.ToString() + " and isSerious='serious' order by event_date desc");
gv.DataSource = dt;
gv.DataBind();
DataTable dt2 = sql.DataTable_from_SQLstring("select * from vwALL_AdverseEvents where studyID = " + Master.Master_studyID.ToString() + " and isSerious<>'serious' order by event_date desc");
gv2.DataSource = dt2;
gv2.DataBind();
sql.Close();
}
protected void LoadNormsTables()
{
DataTable dt = sql.DataTable_from_SQLstring("select table_name from uwautism_research_data.information_schema.tables where table_name like 'norm%' order by table_name");
gvTables.DataSource = dt;
gvTables.DataBind();
}
private void LoadFromDB(int rptpk)
{
SQL_utils sql = new SQL_utils("data");
DataTable dt = sql.DataTable_from_SQLstring("select dataproj_pk, rpttitle, rptdesc, rptnum, rptfilename from dp.Report where rptpk=" + rptpk.ToString());
rptnum = Convert.ToInt32(dt.Rows[0]["rptnum"].ToString());
rpttitle = dt.Rows[0]["rpttitle"].ToString();
rptdesc = dt.Rows[0]["rptdesc"].ToString();
//Load the orders
DataTable dt_orders = sql.DataTable_from_SQLstring("select orderpk, ordernum, ordertype, worksheet, filter, vars, objects, orderbinary from dp.ReportOrder where rptpk=" + rptpk.ToString());
if (dt_orders.HasRows())
{
orders = new SessionOrders();
foreach (DataRow row in dt_orders.Rows)
{
byte[] orderbytes = (byte[])(row["orderbinary"]);
DxChartOrder neworderC = new DxChartOrder();
using (MemoryStream ms = new MemoryStream(orderbytes))
{
var ser2 = new BinaryFormatter();
neworderC = (DxChartOrder)(ser2.Deserialize(ms));
orders.chartorders.Add(neworderC);
orders.orders.Add(neworderC);
}
}
}
sql.Close();
}
protected DataTable GetIDs(int studyID)
{
DataTable dt = new DataTable();
SQL_utils sql = new SQL_utils();
if (studyID == 1011 | studyID == 1015 | studyID == 1022 | studyID == 1030)
{
dt = sql.DataTable_from_SQLstring("select ID, txgrp + '...' + ID + '...' + subjstatus as details " +
" from uwautism_research_backend..vwmasterstatus_S_txgrp where txgrp<>'' and ID in " +
" (select tap_id from uwautism_research_backend..tbl_TxRandomAssign where tap_id not like '%x') and studyID=" +
studyID.ToString() + " order by txgrp, ID");
}
else
{
string sortexpr = "ID";
dt = sql.DataTable_from_SQLstring(String.Format("select ID, txgrp + '...' + ID + '...' + subjstatus as details " +
" from uwautism_research_backend..vwmasterstatus_S_txgrp where txgrp<>'' and studyID=" +
studyID.ToString() + " order by {0} ", sortexpr));
}
sql.Close();
return(dt);
}
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 Populate_Ns()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_Strata_Ns_BY_STRATA order by 1,2");
gvNs_Strata.DataSource = dt;
gvNs_Strata.DataBind();
DataTable dt1 = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_Strata_Ns_BY_SexSiteGroup order by 1,2");
gvSexSiteGroup.DataSource = dt1;
gvSexSiteGroup.DataBind();
DataTable dt2 = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_Strata_Ns_BY_SiteGroup order by 1");
gvSiteGroup.DataSource = dt2;
gvSiteGroup.DataBind();
DataTable dt3 = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_Strata_MeanAge_BY_SiteGroup order by 1");
gvMeanAge.DataSource = dt3;
gvMeanAge.DataBind();
DataTable dt4 = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_Strata_MeanDQ_BY_SiteGroup order by 1");
gvMeanDQ.DataSource = dt4;
gvMeanDQ.DataBind();
sql.Close();
}
// protected void Load_ddlSSD()
//{
// SQL_utils sql = new SQL_utils("backend");
// string sqlcmd = "select -1 as subjstatusdetailID, '-- update status detail--' subjstatusdetail union select a.subjstatusdetailID, subjstatusdetail from uwautism_research_backend..tblsubjstatusdetail_lkup a " +
// "join tblsubjstatusanddetail b ON a.subjstatusdetailID = b.subjstatusdetailID " +
// "where b.subjstatusID = " + ddlSS.SelectedValue.ToString();
// DataTable dt = sql.DataTable_from_SQLstring(sqlcmd);
// List<string> ssdvals = dt.AsEnumerable().Select(f => f.Field<int>("subjstatusdetailID").ToString()).ToList();
// Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallMyFunction", "ClearOptionSSD();", true);
// foreach(string s in ssdvals)
// {
// Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallMyFunction", "AddOptionSSD(" + s + "," + s + ");", true);
// //foreach(ListItem itm in ddlSSD.Items)
// //{
// // if (ssdvals.Contains(itm.Value))
// // {
// // itm.Enabled = true;
// // }
// // else{
// // ddlSSD.options.
// // itm.Enabled = false;
// // }
// //}
// }
// //ddlSSD.DataValueField = "subjstatusdetailID";
// //ddlSSD.DataTextField = "subjstatusdetail";
// //ddlSSD.DataSource = dt;
// //ddlSSD.DataBind();
// // updatepanelSSD.Update();
// sql.Close();
//}
public string loadMeasstatusdetail(string measstatusID, string measstatusdetailID)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select '--select detail--,-1' as ms_msd union select measstatusdetail +','+ cast(measstatusdetailID as varchar) ms_msd from tblMeasstatusdetail_lkup" +
" where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")");
DataTable dt2 = sql.DataTable_from_SQLstring("select '--select detail--' measstatusdetail,'-1' as measstatusdetailID union select measstatusdetail , cast(measstatusdetailID as varchar) measstatusdetailID from tblMeasstatusdetail_lkup" +
" where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")");
string msd = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("ms_msd")).ToArray());
if (measstatusdetailID == null | measstatusdetailID == "")
{
measstatusdetailID = "-1";
}
Control ctrl = gridM.Templates[0].Container.FindControl("ddl_measstatusdetailID");
string ctlID = ctrl.ID;
string ctl_clientID = ctrl.ClientID;
msd += "|" + measstatusdetailID + "|" + ctlID; //try to just lookup the control name without the full clientID
sql.Close();
return(msd);
}
protected void LoadIntHxCategories()
{
SQL_utils sql = new SQL_utils("data");
DataTable dtType = sql.DataTable_from_SQLstring("select txtypeID, left(txcat1,3) txcat1, left(txcat2,3) txcat2, left(txcat3,3) txcat3, left(txcat4,3) txcat4, txtype from vwconst_MIND_IntHXv2_TxTypeCat order by txcat4, txtype");
DataTable dtCat = sql.DataTable_from_SQLstring("select txcat1, txcat2, txcat3, txcat4 from vwconst_MIND_IntHXv2_TxTypeCat group by txcat1, txcat2, txcat3, txcat4 order by txcat1, txcat2, txcat3 ");
gridTxType.DataSource = dtType;
gridTxCat.DataSource = dtCat;
gridTxCat.DataBind();
gridTxType.DataBind();
}
protected void DisplaySummaryTable()
{
////TODO
string sqlcode;
if (Master.Master_studyID == 1076)
{
sqlcode = "select a1.ID, Groupname, subjstatus, '~/dataentryforms/common/MIND_IntHx_v2stack/MIND_IntHx_v2stack.aspx?id=' + a1.ID as link, " +
" sms1, sms2, sms3, sms3_5, sms4, sms4_5, sms5, " +
"b.* " +
"from vwTadpole_IntHx_Tracking_Summary a1 " +
" left join " +
"(select * from uwautism_research_backend..vwmasterstatus_S where studyID = " + Master.Master_studyID.ToString() + ") a ON a1.ID = a.ID " +
"left join vwIntHx_weeks_by_year b ON a.personID = b.personID order by Groupname, subjstatus, ID";
}
else
{
sqlcode = "select ID, Groupname, subjstatus, '~/dataentryforms/common/MIND_IntHx_v2stack/MIND_IntHx_v2stack.aspx?id=' + ID as link, " +
"b.* " +
"from " +
"(select * from uwautism_research_backend..vwmasterstatus_S where studyID = " + Master.Master_studyID.ToString() + ") a " +
"left join vwIntHx_weeks_by_year b ON a.personID = b.personID order by Groupname, subjstatus, ID";
}
SQL_utils sql = new SQL_utils();
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
//Get the years to show or hide
DataTable dtTOTALS = sql.DataTable_from_SQLstring("exec spIntHx_weeks_by_year_TOTALS " + Master.Master_studyID.ToString());
yrs = dtTOTALS.AsEnumerable().Select(f => f.Field <int>("yr")).ToList();
if (Master.Master_studyID == 1076)
{
gvByYearTadpole.DataSource = dt;
gvByYearTadpole.DataBind();
gvByYearTadpole.Visible = true;
gvByYear.Visible = false;
}
else
{
gvByYear.DataSource = dt;
gvByYear.DataBind();
gvByYearTadpole.Visible = false;
gvByYear.Visible = true;
}
sql.Close();
}
public static DataTable LoadFullDataStructureList(string source, string whereclause, bool uwonly, int studyID)
{
DataTable dt = new System.Data.DataTable();
if (source == "localDB")
{
SQL_utils sql = new SQL_utils("data");
if (uwonly & studyID > 0)
{
// See below where the view 'vwALL_VinelandIISurvey_withMBI' is swapped in.
// NDAR now requires the MBI variables, this view pulls these in from the 'ALL_VinelandIISurvey_MBI' table.
//dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where uwtable is not null and " +
// " (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')" +
// " and uwtable in (select (case when name = 'ALL_VinelandIISurvey' then 'vwALL_VinelandIISurvey_withMBI' else name end) name from datTable a " +
// " join datTable_Measure b ON a.tableID = b.tableID " +
// " where measureID in " +
// " (select measureID from uwautism_research_backend..tblstudymeas where studyID = " + studyID.ToString() + "))");
string code = String.Format("select * from vwNDAR_DS where uwtable is not null and " +
" (shortname like '%{0}%' or title like '%{0}%')" +
" and uwtable in (select tblname from def.Tbl " +
" where measureID in " +
" (select measureID from uwautism_research_backend..tblstudymeas where studyID = {1}))"
, whereclause, studyID);
dt = sql.DataTable_from_SQLstring(code);
}
else if (uwonly & studyID == 0)
{
dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where uwtable is not null and " +
" (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')");
}
else if (whereclause != "")
{
dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where " +
" (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')");
}
else
{
dt = sql.DataTable_from_ViewName("vwNDAR_DS");
}
sql.Close();
}
else if (source == "NDAR")
{
dt = LoadFullDataStructureList(source);
}
return(dt);
}
protected void LoadSubjects()
{
var selstudies = dataops.GetCSV(gridLkupStudy.GridView.GetSelectedFieldValues(gridLkupStudy.KeyFieldName));
SQL_utils sql = new SQL_utils("backend");
string sqlcode;
bool includeAll = (chkIncludeAll.Checked) ? true : false;
string addLEFTJOIN = (includeAll) ? "LEFT " : "";
sqlcode = String.Format(" select otherID, sitename, a.personID, code6, a.lastname, a.firstname, a.sex, a.ethnicitydesc as race, a.hispanicdesc as ethnicity, a.personRole " +
" from vwPerson a " +
" {1} join vwOTherID2 b ON a.personID = b.personID " +
" left join code6_person c ON a.PersonID = c.rownum" +
" where a.personID in (select personID from vwMasterStatus_S where studyID in ({0})) order by a.lastname, a.firstname ", selstudies, addLEFTJOIN);
//sqlcode = String.Format("select otherID, sitename, a.personID, code6, a.lastname, a.firstname, sex, ethnicitydesc as race, hispanicdesc as ethnicity, personRole " +
// " from vwOTherID2 a" +
// " {1} join vwPerson b ON a.personID = b.personID" +
// " left join code6_person c ON a.PersonID = c.rownum " +
// " where a.personID in (select personID from vwMasterStatus_S where studyID in ({0}))", selstudies);
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
otherID_by_person.DataSource = dt;
otherID_by_person.DataBind();
Session["otherID_by_person"] = dt;
string sqlcode2 = String.Format("select studyname, ID, groupname, otherID, sitename, code6, a.personID, a.lastname, a.firstname, a2.sex, a2.ethnicitydesc as race, a2.hispanicdesc as ethnicity, personRole " +
" from vwOTherID2 a" +
" join vwPerson a2 ON a.personID = a2.personID" +
" join vwMasterStatus_S b ON a.personID = b.personID" +
" left join code6_person c ON a.PersonID = c.rownum " +
" where a.personID in (select personID from vwMasterStatus_S where studyID in ({0})) order by a.lastname, a.firstname", selstudies);
DataTable dt2 = sql.DataTable_from_SQLstring(sqlcode2);
otherID_by_subject.DataSource = dt2;
otherID_by_subject.DataBind();
Session["otherID_by_subject"] = dt2;
sql.Close();
}
public string GetDDLcontents_A(string ctlname, string actiontypeID, string actionstatusID)
{
SQL_utils sql = new SQL_utils("backend");
string data = "";
DataTable dt = new DataTable();
if (ctlname == "actionstatusID")
{
dt = sql.DataTable_from_SQLstring("select '--select status--^-1' as textvalue " +
" union select * from (select top 100 percent actionstatus +'^'+ cast(a.actionstatusID as varchar) textvalue " +
" from tblactionstatus_lkup a" +
" join tblActionTypeStatus b ON a.actionstatusID = b.actionstatusID " +
" where actiontypeID = " + actiontypeID + " order by b.sortorder ) x ");
}
data = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("textvalue")).ToArray());
if (actionstatusID == "")
{
actionstatusID = "-1";
}
Control ctl = (Control)gridM.Templates[0].Container.FindControl("ddl_" + ctlname);
data += "|" + actionstatusID.ToString() + "|ddl_" + ctlname;
return(data);
}
//Pass in the ctlname and filtervalue to get the data to populate a DDL
//with a subset of relevant options. This string is then passed back to a
//Javascript function that loads the ddl.
public string GetDDLcontents(string ctlname, string measstatusID, string measstatusdetailID)
{
SQL_utils sql = new SQL_utils("backend");
string data = "";
DataTable dt = new DataTable();
if (ctlname == "measstatusdetailID")
{
dt = sql.DataTable_from_SQLstring("select '--select detail--^-1' as textvalue union select measstatusdetail +'^'+ cast(measstatusdetailID as varchar) textvalue from tblMeasstatusdetail_lkup" +
" where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID.ToString() + ")");
}
data = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("textvalue")).ToArray());
if (measstatusdetailID == "")
{
measstatusdetailID = "-1";
}
//Control ctl = (Control)gridM.Templates[0].Container.FindControl("ddl_" + ctlname);
data += "|" + measstatusdetailID.ToString() + "|ddl_" + ctlname;
sql.Close();
return(data);
}
public void LoadTokens()
{
if (_studyID > 0)
{
SQL_utils sql = new SQL_utils("data");
//string code = "select coalesce(token,'') token, coalesce(url,'') url, coalesce(idfld, '') idfld from uwautism_research_backend..tblstudy s LEFT JOIN def.REDCapToken_Study a ON s.studyID=a.studyID LEFT JOIN def.REDCapToken b ON a.tokenID=b.tokenID where s.studyID=" + studyID.ToString();
string code = "select b.tokenid, coalesce(token,'') token, coalesce(url,'') url, coalesce(idfld, '') idfld from uwautism_research_backend..tblstudy s JOIN def.REDCapToken_Study a ON s.studyID=a.studyID JOIN def.REDCapToken b ON a.tokenID=b.tokenID where s.studyID=" + _studyID.ToString();
dt_tokens = sql.DataTable_from_SQLstring(code);
string form_event_code = String.Format("select unique_event_name + form form_and_event, tokenid, studymeasid from def.redcap_formevent where studymeasid in (select studymeasid from uwautism_research_backend..tblstudymeas where studyid={0})", _studyID);
dt_db_formevent_studymeasid = sql.DataTable_from_SQLstring(form_event_code);
sql.Close();
}
}
protected void LoadSubjStatus_options()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select * from tblSubjectStatus_Lkup where subjstatusID in " +
" (select subjstatusID from tblStudy_SubjStatus where studyID = "+ Master.Master_studyID.ToString() + ")");
}
private void LoadDoc(string filepath)
{
if (docversid > 0)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt_docversinfo = sql.DataTable_from_SQLstring(String.Format("select * from vwdocvers where docversid={0}", docversid));
if (dt_docversinfo.HasRows())
{
if (dt_docversinfo.Rows.Count == 1)
{
DataRow row = dt_docversinfo.Rows[0];
_origfilename = row["origfilename"].ToString();
_fileext = row["fileext"].ToString();
_doctypeid = Convert.ToInt32(row["doctypeid"].ToString());
_docfilename = row["docfilename"].ToString();
}
if (_fileext.ToLower() == ".xlsx" | _fileext.ToLower() == ".xls")
{
ReadExcelFile(filepath, _docfilename);
}
else if (_fileext.ToLower() == ".csv")
{
ReadExcelFile(filepath, _docfilename);
}
}
else
{
processing_results.Log(String.Format("ERROR: docversID {0} not in DB.", docversid));
}
}
}
protected Literal FormatCodeOfObject(SQL_utils sql, string refobjname)
{
Literal lit = new Literal();
//string refobjname = obj["ReferencedObject"].ToString();
////string myobjcode = sql.StringScalar_from_SQLstring("exec spRefObj_FullSQLtext " + refobjname);
DataTable dt = sql.DataTable_from_SQLstring("exec sp_helptext '" + refobjname + "'");
string objcode = String.Format("<br/><br/><b><u>{0}</u></b><br/>", refobjname);
for (int c = 0; c < dt.Rows.Count; c++)
{
DataRow r = dt.Rows[c];
string newline = String.Format("<span style=\"color:silver\">{0}.</span> {1}<br/>", c + 1, r["Text"]);
objcode += newline;
}
//objcode = String.Format("<b><u>{0}</u></b><br/>{1}<br/><br/>", refobjname, myobjcode);
//lit.Text = "<pre class=\"prettyprint\">" + objcode + "</pre>";
lit.Text = objcode;
return(lit);
}
protected void gridMeasNoVarsCallback_OnCallback(object sender, EventArgs e)
{
Debug.Print(" $$ gridMeasCallback_OnCallback " + System.DateTime.Now.ToString());
List <int> selmeasID = dataops.GetListInt(gridSelMeas.GetSelectedFieldValues("measureID")).Distinct().ToList();
List <int> measID_of_selvars = dataops.GetListInt(gridSelVars.GetSelectedFieldValues("measureID")).Distinct().ToList();
List <int> MeasNoVars = selmeasID.Except(measID_of_selvars).ToList();
if (MeasNoVars.Count > 0)
{
SQL_utils sql = new SQL_utils("backend");
string code = String.Format("select measname from tblMeasure where measureID in ({0})", String.Join(",", MeasNoVars));
DataTable dt_MeasNoVar = sql.DataTable_from_SQLstring(code);
sql.Close();
gridMeasWithNoVars.DataSource = dt_MeasNoVar;
gridMeasWithNoVars.DataBind();
}
//RefreshSession_selinthx();
//PivotSelIntHx.DataSource = (DataTable)Session["proj_inthx"];
//PivotSelIntHx.DataBind();
//var selinthx = gridSelIntHx.GetSelectedFieldValues(gridSelIntHx.KeyFieldName);
//selected_inthxlist = selinthx.ConvertAll(x => Convert.ToString(x));
}
protected void btnFindID_Click(object sender, EventArgs e)
{
if (txtIDToFind.Text.Length > 0)
{
SQL_utils sql = new SQL_utils("backend");
string s = "select a.* from gev.vwSubjects_in_UW_studies_with_Names a " +
"where personID in " +
"(select personID from gev.vwSubjects_in_UW_studies where ID like '%" + txtIDToFind.Text + "%' )";
DataTable dt = sql.DataTable_from_SQLstring(s);
gv.DataSource = dt;
gv.DataBind();
lblInfo.Text = "";
}
else
{
lblInfo.Text = "";
//Nothing to search
}
}
//0
#region 1) Load data for display
protected void PopulateGV(string id)
{
int getall = 1; //adjust this if needed.
SQL_utils sql = new SQL_utils();
DataTable dt = sql.DataTable_from_SQLstring("exec spALL_MIND_IntHx_vers2_stacked_2018_for_display " + Master.Master_studyID.ToString() + ",'" + id + "'," + getall.ToString());
sql.Close();
ViewState["dt"] = dt;
gvRawData.DataSource = dt;
gvRawData.DataBind();
lblRawData_nrecs.Text = dt.Rows.Count.ToString() + " records.";
SQL_utils sql1 = new SQL_utils("backend");
string groupname = sql1.StringScalar_from_SQLstring("select groupname from vwMasterStatus_S where studyID=" + Master.Master_studyID.ToString() + " and ID='" + id + "'");
lblGroup.Text = groupname;
int hasscan = sql1.IntScalar_from_SQLstring("exec uwautism_research_data.dbo.spHaveUWmethodRawScan '" + id + "'");
if (hasscan == 1)
{
linkRawData.NavigateUrl = "~/dataentryforms/common/MIND_IntHx_v2stack/scans/UW_method_" + id + "_reduced.pdf";
linkRawData.Visible = true;
}
LoadSMS(sql1, id);
sql1.Close();
}
protected void LoadOverview_by_study()
{
if (print_jminfo)
{
jminfo.Text += "LoadOverview_by_study..<br/>";
}
//lbl_StudyName.Text = _content_studyname;
lbl_StudyName.Text = Master.Master_studyname;
SQL_utils sql = new SQL_utils("data");
string sqlcode = "exec spRELv2_GET_RelTracking_Overview_by_study " + Master.Master_studyID.ToString();
DataTable dt2 = sql.DataTable_from_SQLstring(sqlcode);
sql.Close();
GridView gv_measures = (GridView)UpdatePanel_Overview.FindControl("gv_measures");
gv_measures.DataSource = dt2;
gv_measures.DataBind();
UpdatePanel_Overview.Update();
}
protected DataTable getDataforCombobox(string sqlcode)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
return(dt);
}
protected void LoadDatatypes()
{
SQL_utils sql = new SQL_utils("backend");
string sqlcode = "select studymeasID, studymeasname from tblmeasure a join tblstudymeas b ON a.measureID=b.measureID " + Environment.NewLine +
" where a.measureID in (select measureID from uwautism_research_data.def.tbl where measureID>0 and importfiletype is not null and importfiletype != 7) " + Environment.NewLine +
" and studyID = " + Master.Master_studyID.ToString() + Environment.NewLine +
" order by b.timepointID, studymeasname ";
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
cboStudymeas.DataSource = dt;
cboStudymeas.ValueField = "studymeasID";
cboStudymeas.TextField = "studymeasname";
cboStudymeas.DataBind();
if (dt.Rows.Count > 0)
{
cboStudymeas.NullText = "select measure";
}
else
{
cboStudymeas.NullText = "none configured for import";
}
sql.Close();
}
protected DataTable GetStaffToEdit(int staffID)
{
SQL_utils sql = new SQL_utils("backend");
//int staffID = Convert.ToInt32(cboStaffInLab.Value.ToString());
int labID = Convert.ToInt32(cboLab.Value.ToString());
string studyIDs_csv = uwac.trk.dataops.GetCSV(gridStudies.GridView.GetSelectedFieldValues("studyID"));
string studyID_whereclause = (studyIDs_csv == null) ? " > 0 " : String.Format(" in ({0}) ", studyIDs_csv);
string code1 = String.Format("select coalesce(labgroup_staffID, -1 * a.labgroupID) labgroup_staffID, studyname, groupname, a.labgroupID, a.groupID, staffID, coalesce(b.dbroleID, 0) dbroleID, coalesce(dbrole, '*NONE*') dbrole " +
" from (select * from tbllabgroup where labID = {2}) a " +
" join tblgroup a1 ON a.groupID=a1.groupID " +
" join tblstudy a2 ON a1.studyID = a2.studyID " +
" left join (select * from tblLabgroup_staff where staffID = {1}) b ON a.labgroupID = b.labgroupID " +
" left join tblDBrole_lkup c ON b.dbroleID = c.dbroleID " +
" where a.labID = {2} order by studyname, groupname ", studyID_whereclause, staffID, labID);
DataTable dt1 = sql.DataTable_from_SQLstring(code1);
sql.Close();
int n1 = dt1.Rows.Count;
return(dt1);
}
public static DataTable SqlCommand_returns_DataTable(SqlCommand sqlcmd)
{
//New Feb 2019
SQL_utils sql = new SQL_utils("data");
SqlParameter[] newps = new SqlParameter[sqlcmd.Parameters.Count];
List <SqlParameter> ps = new List <SqlParameter>();
foreach (SqlParameter p in sqlcmd.Parameters)
{
SqlParameter newp = new SqlParameter();
newp.ParameterName = p.ParameterName;
newp.Value = p.Value;
newp.SqlValue = p.SqlValue;
newp.SqlDbType = p.SqlDbType;
ps.Add(newp);
}
string sqlproc = sqlcmd.CommandText;
DataTable dt = sql.DataTable_from_SQLstring(sqlproc, ps);
sql.Close();
return(dt);
}
protected void btnLoadStaff_OnClick(object sender, EventArgs e)
{
int labID = Convert.ToInt32(cboLab.Value);
SQL_utils sql = new SQL_utils("backend");
string studyIDs_csv = uwac.trk.dataops.GetCSV(gridStudies.GridView.GetSelectedFieldValues("studyID"));
//string sqlcode = String.Format("select * from vwSEC_AllowedGroups_By_StaffID where studyID in ({0}) and StaffName not in ('{1}','{2}')", studyIDs_csv, "aut user", "autsys TestAcct");
string sqlcode = String.Format("select * from vwSEC_AllowedGroups_By_StaffID where labID in ({0}) and StaffName not in ('{1}','{2}')", labID, "aut user", "autsys TestAcct");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
Session["labstaff"] = dt;
pivotStaff.DataSource = dt;
pivotStaff.DataBind();
pivotStaff.Visible = true;
sql.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
panelDups.Visible = false;
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select -1 labID, '--select lab--' labname union " +
" select a.labID, labname from fn_AllowedLabs_by_User() a join tblLab b ON a.labID = b.labID");
ddlLab.DataSource = dt;
ddlLab.DataBind();
int numfail = sql.IntScalar_from_SQLstring("select coalesce(count(*),0) from tblOtherID_failed");
lblNumFailed.Text = String.Format("# of failed otherID records: {0}", numfail);
sql.Close();
}
else
{
if (Session["labstudies"] != null)
{
gridStudy.DataSource = (DataTable)Session["labstudies"];
gridStudy.DataBind();
}
}
}
private void InsertREDCapData()
{
DataTable dtready = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "ready").CopyToDataTable();
DataColumn col = new DataColumn("verified", typeof(int));
col.DefaultValue = 0;
dtready.Columns.Add(col);
SQL_utils sql = new SQL_utils("data");
resultslog.Log("INFO: Attempting to match data types");
DataTable dt_dest = sql.DataTable_from_SQLstring(String.Format("select * from {0} where 1=2", tblname));
dtready.MatchColumnDataTypes(dt_dest);
try
{
string insertresults = sql.BulkInsert(dtready, tblname);
resultslog.Log(insertresults);
//resultslog.Log(String.Format("SUCCESS: inserted {0} rows of data into {1}", dtready.Rows.Count, tblname));
if (!insertresults.Contains("Error"))
{
UpdateTrackingDB(dtready);
}
}
catch (Exception ex)
{
resultslog.Log(String.Format("FAILED to insert data into {0} (# rows = {1})", tblname, dtready.Rows.Count));
}
sql.Close();
}
//protected void GetCurrentDefaultStudyID()
//{
// SQL_utils sql = new SQL_utils("data");
// string sqlcode = "exec spSEC_Get_Default_StudyID_for_User ";
// DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
// sql.Close();
// SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("exec spSEC_Get_Default_StudyID_for_User", oConn);
// DataTable dt = new DataTable();
// SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
// sqlAdapter.Fill(dt);
// foreach (DataRow row in dt.Rows)
// {
// _content_studyID = Convert.ToInt16(row["defaultstudyID"]);
// _content_studyname = Convert.ToString(row["studyname"]);
// }
//}
//raise events on content page for the buttons on master page
protected void Page_Init(object sender, EventArgs e)
{
oConn.ConnectionString = ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"].ToString();
oConn.Open();
oConnData.ConnectionString = ConfigurationManager.ConnectionStrings["DATA_CONN_STRING"].ToString();
oConnData.Open();
// version = "1";
Master.DDL_Master_SelectStudyID.SelectedIndexChanged += new EventHandler(Master_Study_Changed);
if (print_jminfo)
{
jminfo.Text += "##Init##<br/>";
}
// Create a new table.
DataTable dt_coder_pairs_list = new DataTable("dt_coder_pairs_list");
SQL_utils sql = new SQL_utils("backend");
DataTable dt_grps = sql.DataTable_from_SQLstring("select groupID , groupname from tblGroup where studyID = " + Master.Master_studyID.ToString());
sql.Close();
utilSelect2.loaditems(selGroups, dt_grps, "groupID", "groupname", true);
}