protected void GetRptData(string d1, string d2, Panel panel) { panel.Controls.Clear(); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("d1", d1, "date")); ps.Add(sql.CreateParam("d2", d2, "date")); DataSet ds = sql.DataSet_from_ProcName("ac.spValant_CHDD_annual_report", ps); foreach (DataTable dt in ds.Tables) { GridView gv = new GridView(); //Grid gv = new Grid(); gv.RowDataBound += new GridViewRowEventHandler(gv_RowDataBound); gv.DataSource = dt; gv.DataBind(); panel.Controls.Add(gv); Literal lit = new Literal(); lit.Text = "<br/><br/>"; panel.Controls.Add(lit); } }
public static DataSet GetIntHxData(SQL_utils sql, int dataproj_pk) { List <DataTable> tbls = new List <DataTable>(); if (sql.SqlConnection.State != ConnectionState.Open) { sql.SqlConnection.Open(); } List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("dataproj_pk", dataproj_pk.ToString(), "int")); DataSet ds = sql.DataSet_from_ProcName("[dp].[IntHx_SUMMARIZE]", ps); if (ds != null) { int counter = 0; foreach (DataTable dt in ds.Tables) { if (counter == 0) { dt.TableName = "IntHx Settings"; } else { dt.TableName = "IntHx_" + counter.ToString(); } counter++; } } return(ds); }
protected void LoadCoderPairDetails(string sql_params) { string[] str_ps = sql_params.Split(','); SQL_utils sql = new SQL_utils(); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("orig_studymeasID", str_ps[0], "int")); ps.Add(sql.CreateParam("rel_studymeasID", str_ps[1], "int")); ps.Add(sql.CreateParam("rel_pkval", str_ps[2], "int")); DataSet ds = sql.DataSet_from_ProcName("spRELv2_GET_items__for_coderpair", ps); for (int i = 0; i < 3; i++) //3 datatables to process { GridView gv_coderpair_details = (GridView)UpdatePanel_coderpair_single.ContentTemplateContainer.FindControl("gv_coderpair_details" + i.ToString()); gv_coderpair_details.DataSource = ds.Tables[i]; //dt; gv_coderpair_details.DataBind(); } sql.Close(); UpdatePanel_coderpair_single.Visible = true; }
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 LoadHouseholdInfo(int householdID) { SQL_utils sql = new SQL_utils("backend"); DataSet ds = sql.DataSet_from_ProcName("hh.spHousehold", sql.CreateParam("householdID", householdID.ToString(), "int")); hidHouseholdID.Value = householdID.ToString(); string hhname = sql.StringScalar_from_SQLstring("select hhname from tblHousehold where householdID = " + householdID.ToString()); lblHHname.Text = hhname; sql.Close(); panelMAIN.Visible = true; }
protected void getPersonData() { //string ID = txtID.Text; SQL_utils sql = new SQL_utils("backend"); DataSet ds = sql.DataSet_from_ProcName("ac.getPersonData", sql.CreateParam("personID", Request.QueryString["personID"], "int")); //grid0.DataSource = ds.Tables[0]; //grid1.DataSource = ds.Tables[1]; //grid2.DataSource = ds.Tables[2]; //grid0.DataBind(); //grid1.DataBind(); //grid2.DataBind(); sql.Close(); }
public static DataSet Get_MIND_IntHx_v2_Data_by_ID(SQL_utils sql, int studyID, string id, string periodtype) { //SQL_utils sql = new SQL_utils(); if (studyID >= 10000) { RescoreIntHx(sql, studyID); } List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyID", studyID.ToString(), "int")); ps.Add(sql.CreateParam("ID", id, "text")); ps.Add(sql.CreateParam("periodtype", periodtype, "text")); DataSet ds = sql.DataSet_from_ProcName("spSCORE_ALL_MIND_IntHx_vers2_STACKED__getdata_for_CHARTS_2018", ps); List <string> tblnames = new List <string> { "Weekly by TYPE", "Weekly by CAT", "Weekly by ALL", //"Period by TYPE", //"Period By CAT", //"Period by ALL" , "dt_admindates", "dt_colors0", "dt_colors1", "dt_colors2" }; int counter = 0; if (ds != null) { foreach (DataTable dt in ds.Tables) { dt.TableName = tblnames[counter]; counter++; } } return(ds); }
protected void LoadCheckBoxes() { int studyID = Master.Master_studyID; SQL_utils sql = new SQL_utils("backend"); DataSet ds = sql.DataSet_from_ProcName("spTracking_Populate_CheckBox_Selectors", sql.CreateParam("studyID", studyID.ToString(), "int")); p_group.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[0], "groupID", "Group", "groupname", "groupID", "int", "1")); p_subjstatus.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[1], "subjstatusID", "Subject Status", "subjstatus", "subjstatusID", "int", "1")); p_id.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[2], "subjID", "ID", "id", "subjID", "int", "1")); p_timepoint.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[3], "timepointID", "TimePoint", "timepoint_text", "timepointID", "int", "1")); p_actiontype.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[4], "actiontypeID", "Action Type", "actiontype", "actiontypeID", "int", "1")); p_studyaction.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[5], "studyactionID", "Action", "actiontext", "studyactionID", "int", "1")); p_measure.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[6], "measureID", "Measure", "measname", "measureID", "int", "1")); p_consent.Controls.Add(new UpdatePanel_O_CheckBoxes(ds.Tables[7], "consentformversionID", "Consent", "cfvname", "consentformversionID", "int", "1")); }
public static DataSet DataProject_Populate_Metadata(int dataproj_pk) { SQL_utils sql = new SQL_utils(); DataSet dset = new DataSet(); if (dataproj_pk > 0) { dset = sql.DataSet_from_ProcName("dp.spDataProject_MetaData", sql.CreateParam("dataproj_pk", dataproj_pk.ToString(), "int")); dset.Tables[0].TableName = "Subjects"; dset.Tables[1].TableName = "Subjects_excluded"; dset.Tables[2].TableName = "DataDict"; dset.Tables[3].TableName = "Measures"; } return(dset); }
protected void LoadHouseholdInfo(int householdID) { gridP.ClearPreviousDataSource(); gridS.ClearPreviousDataSource(); SQL_utils sql = new SQL_utils("backend"); DataSet ds = sql.DataSet_from_ProcName("hh.spHousehold", sql.CreateParam("householdID", householdID.ToString(), "int")); hidHouseholdID.Value = householdID.ToString(); //Populate the Grids LoadGrid(gridP, ds.Tables[0]); LoadGrid(gridPhone, ds.Tables[1]); LoadGrid(gridEmail, ds.Tables[2]); LoadGrid(gridAddress, ds.Tables[3]); LoadGrid(gridLogContact, ds.Tables[4]); LoadGrid(gridLogInfo, ds.Tables[5]); LoadGrid(gridEmerContact, ds.Tables[6]); //DataView vwdups = ds.Tables[0].AsDataView(); vwdups.RowFilter = "ndups //> 0"; //DataTable dtdups = vwdups.ToTable(); //if(dtdups.Rows.Count > 0) //{ // LoadGrid(gridDups, dtdups); //} string hhname = sql.StringScalar_from_SQLstring("select hhname from tblHousehold where householdID = " + householdID.ToString()); lblHHname.Text = hhname; sql.Close(); //sql_Persons_by_Household.SelectParameters["householdID"].DefaultValue = householdID.ToString(); //gridP.DataBind(); panelMAIN.Visible = true; }
protected void DisplaySummaryStats() { SQL_utils sql = new SQL_utils(); DataSet ds = sql.DataSet_from_ProcName("spSCORE_ALL_MIND_IntHx_vers2_STACKED__getdata_for_DESCSTATS", sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); sql.Close(); int dt_to_use = 0; if (rblLevel.SelectedValue == "txall") { dt_to_use = 1; } List <string> vars = new List <string> { rblLevel.SelectedValue, "period", "txgrp" }; DataTable dtout = utilStats.DataTable_DescStats_ByGroup(ds.Tables[dt_to_use], "ALL_MIND_IntHx_vers2_STACKED_by_PERIOD_CAT", vars, "stacked", true); GridView gv = new GridView(); gv.DataSource = dtout; gv.DataBind(); panelStats.Controls.Add(gv); //GVHistogram h = utilCharts.Histogram(ds.Tables[1], "avgPER_ihhrsperwk", 250, 250); //h.ID = "foo"; //panelMS2.Controls.Add(h); panelMS2.Visible = true; Table t2 = utilCharts.Histograms_byRowLevel_byColLevel_Facet(ds.Tables[dt_to_use], "avgPER_ihhrsperwk", "txgrp", "period", rblLevel.SelectedValue, 150, 150, "color", ""); panelMS2.Controls.Add(t2); }
protected void LoadSubjectInfo(string str_subjID, int smsID) { int subjID = 0; bool success = int.TryParse(str_subjID, out subjID); if (success & subjID > 0) { //populate the Subject Superform by updating the select parameter //sqlSUBJ.SelectParameters[0].DefaultValue = subjID.ToString(); //// markup SQL ////populate the Measure grid by updating the select parameter //sqlA.SelectParameters[0].DefaultValue = subjID.ToString(); //gridA.DataBind(); ////populate the Measure grid by updating the select parameter //sqlM.SelectParameters[0].DefaultValue = subjID.ToString(); //gridM.DataBind(); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("subjID", subjID.ToString(), "int")); ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); ps.Add(sql.CreateParam("ID", ID, "text")); //Not currently used //UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)p_timepoint.FindControl("timepointID"); //if (p != null) //{ // DataTable dt_tp = p.GetChecked_as_DataTable(); // ps.Add(sql.CreateParam("tp", dt_tp)); // ds = sql.DataSet_from_ProcName("spTracking_SubjInfo", ps); //} //else //{ ds = sql.DataSet_from_ProcName("spTracking_SubjInfo_all_tp", ps); //} //LoadForm(ds.Tables[0]); //LoadGrid(superS, ds.Tables[0]); string strID = ds.Tables[0].AsEnumerable().Select(f => f.Field <string>("ID")).FirstOrDefault(); string groupname = ds.Tables[0].AsEnumerable().Select(f => f.Field <string>("GroupName")).FirstOrDefault(); int subjstatusID = ds.Tables[0].AsEnumerable().Select(f => f.Field <int>("subjstatusID")).FirstOrDefault(); int subjstatusdetailID = ds.Tables[0].AsEnumerable().Select(f => f.Field <int>("subjstatusdetailID")).FirstOrDefault(); string notes = ds.Tables[0].AsEnumerable().Select(f => f.Field <string>("notes")).FirstOrDefault(); int householdID = ds.Tables[0].AsEnumerable().Select(f => f.Field <int>("householdID")).FirstOrDefault(); int studyID = ds.Tables[0].AsEnumerable().Select(f => f.Field <int>("studyID")).FirstOrDefault(); string subjstatus = ds.Tables[0].AsEnumerable().Select(f => f.Field <string>("subjstatus")).FirstOrDefault(); string subjstatusdetail = ds.Tables[0].AsEnumerable().Select(f => f.Field <string>("subjstatusdetail")).FirstOrDefault(); if (studyID != Master.Master_studyID) { sql.NonQuery_from_SQLstring("exec spSEC_Update_Default_StudyID_for_User " + studyID.ToString()); Response.Redirect("subject.aspx?subjID=" + subjID.ToString()); } hidHouseholdID.Value = householdID.ToString(); lblID.Text = strID; lblGroupName.Text = groupname; txtNotes.Text = notes; lblNotes.Text = notes; lblSS.Text = subjstatus; lblSSD.Text = subjstatusdetail; lblNotes.Text = notes; // ddlSubjStatus2.SelectedValue = subjstatusID.ToString(); //sqlSSD.SelectParameters["subjstatusID"].DefaultValue = subjstatusID.ToString(); // ddlSubjStatusDetail2.DataBind(); //try //{ // ddlSubjStatusDetail2.SelectedValue = subjstatusdetailID.ToString(); //} //catch(Exception ex) //{ //} LoadGrid(gridA, ds.Tables[1]); LoadGrid(gridM, ds.Tables[2]); LoadGrid(gridC, ds.Tables[3]); DataTable dtDocs = sql.DataTable_from_SQLstring("select a.*, dbo.fnElapTime_text(created, getdate()) TimeSinceUpload " + " from vwDocVers a join tblDocFK b ON a.docID = b.docID " + " where entitytypeID=4 and entityID=" + Request.QueryString["subjID"]); if (dtDocs.Rows.Count > 0) { gvDocs.DataSource = dtDocs; gvDocs.DataBind(); } sql.Close(); panelSubjectInfo.Visible = true; btnReturnToHousehold.Visible = true; btnViewDataValues.Visible = false; } }
//This parallels the DataProject that is defined on the website and formally saved to the DB. //This method is a one-off used by MeasureStats.aspx that creates the same structure. public static DataSet DataProject_Populate(int studyID, string measureID_csv, string timepointID_csv, string groupID_csv, string subjstatusID_csv) { SQL_utils sql = new SQL_utils(); DataSet dset = new DataSet(); List <int> vartypes = new List <int>(); vartypes.AddRange(new List <int> { 2, 3, 4 }); //int dataproj_pk = Convert.ToInt32(Request.QueryString["pk"]); if (1 == 1) { List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyID", studyID.ToString(), "int")); ps.Add(sql.CreateParam("measureID_csv", measureID_csv, "text")); ps.Add(sql.CreateParam("timepointID_csv", timepointID_csv, "text")); ps.Add(sql.CreateParam("groupID_csv", groupID_csv, "text")); ps.Add(sql.CreateParam("subjstatusID_csv", subjstatusID_csv, "text")); dset = sql.DataSet_from_ProcName("dp.spDataProject_MetaData2", ps); if (dset != null) { dset.Tables[0].TableName = "Subjects"; dset.Tables[1].TableName = "Subjects_excluded"; dset.Tables[2].TableName = "DataDict"; dset.Tables[3].TableName = "Measures"; #region MEASURES foreach (DataRow row in dset.Tables["Measures"].Rows) { //1 int measureID = (int)row["measureID"]; string measname = row["measname"].ToString(); string tblname = row["tblname"].ToString(); string studymeas_csv = row["studymeas_csv"].ToString(); //2 pass 0 for dataproj_pk as we are not using a dataproject List <DataTable> tbls = GetMeasure(sql, 0, measureID, measname, tblname, studymeas_csv, studyID, groupID_csv, subjstatusID_csv); foreach (DataTable t in tbls) { dset.Tables.Add(t); } } } #endregion } sql.Close(); dset = utilStats.RemoveMissingValues_from_DataSet(dset); return(dset); }
protected void ActigraphPlots() { panel.Controls.Clear(); SQL_utils sql = new SQL_utils("data"); int iswake_negvalue = -15; int cap_activity = 2000; int cap_whitelight = 300; int scaleto_activity = 100; int scaleto_whitelight = 100; lblSetupInfo.Text = String.Format("Activity capped at {0}, White light capped at {1}<br/>Activity scaled 0:{2}, White light scaled 0:{3}" , cap_activity, cap_whitelight, scaleto_activity, scaleto_whitelight); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("id", cboSubject.Value.ToString(), "text")); ps.Add(sql.CreateParam("smID_epoch", cboStudymeas.Value.ToString(), "int")); ps.Add(sql.CreateParam("cap_activity", cap_activity.ToString(), "int")); ps.Add(sql.CreateParam("cap_whitelight", cap_whitelight.ToString(), "int")); ps.Add(sql.CreateParam("iswake_neg_value", iswake_negvalue.ToString(), "int")); DataSet ds = sql.DataSet_from_ProcName("spActigraphData", ps); ds.Tables[0].TableName = "epoch"; ds.Tables[1].TableName = "stats"; DxActogramSettings s = new DxActogramSettings(); s.W = 1200; s.H = 300; s.seriesby = "id"; s.xaxisvar = "time_of_day"; s.xaxis_is_date = true; s.AddVarsFromList(new List <string>() { "activity", "is_wake", "white_light" }); //Changed Feb 2019 //s.numvars = new List<string>() { "activity", "is_wake", "white_light"}; s.colorvar = "variable"; s.panelvar = "report_date"; s.colors = Actigraph.colors; s.legend_pos_h = "RightOutside"; s.legend_pos_v = "Top"; s.maxy = scaleto_whitelight; s.miny = iswake_negvalue; s.maxCol = 1; s.geom = LineplotGeom.Bar; s.altgeom = LineplotGeom.Star; s.vars_for_altgeom = new List <string> { "white_light" }; s.markersize = 8; DxHistogramSettings hist = new DxHistogramSettings((DxChartSettings)s); hist.AddVarsFromList(new List <string>() { "activity", "white_light" }); //Changed Feb 2019 //hist.numvars = new List<string>() { "activity", "white_light" }; hist.W = 400; hist.H = 200; hist.colors = Actigraph.colors; DxChartOrder order = new DxChartOrder(); order.list_settings.Add(s); order.list_settings.Add(hist); DataTable stats1 = utilStats.DataTable_DescStats(ds.Tables["epoch"], true); ds.Tables["epoch"].RescaleColumn("activity", 0, scaleto_activity); ds.Tables["epoch"].RescaleColumn("white_light", 0, scaleto_whitelight); DataTable stats2 = utilStats.DataTable_DescStats(ds.Tables["epoch"], true); DxChartFactory factory = new DxChartFactory(ds.Tables["epoch"], new List <DxChartOrder> { order }); int counter = 0; foreach (DxChartOrder myorder in factory.orders) { foreach (DxChartBatch batch in myorder.batches) { if (batch.batchsettings.outputtype == DxOutputtype.Actogram) { foreach (DxActogram ch in batch.charts) { XYDiagram xy = ch.xydiagram; xy.AxisX.DateTimeScaleOptions.MeasureUnit = DateTimeMeasureUnit.Hour; xy.AxisX.DateTimeScaleOptions.GridAlignment = DateTimeGridAlignment.Hour; xy.AxisX.Label.TextPattern = "{A:t}"; DataTable dt = ds.Tables[1]; string date_txt = String.Format("{0}, {1}", ch.date_txt, Convert.ToDateTime(ch.date_txt).DayOfWeek.ToString()); ch.SetMainTitle(date_txt); //Place date in the title foreach (DataRow row in dt.Rows) { DateTime rpt_date = Convert.ToDateTime(row["report_date"].ToString()); if (rpt_date == Convert.ToDateTime(date_txt)) { Actigraph.ActogramStats stats = new Actigraph.ActogramStats(row); ch.AnnotateActogram(stats); counter++; } } } batch.SetYAxisRange(); batch.SetXAxisRange_1day(); } else { foreach (DxChart ch in batch.charts) { XYDiagram xy = ch.xydiagram; xy.AxisX.Label.TextPattern = "{A:n}"; } } System.Web.UI.WebControls.Table t = LayoutOutput.LayoutBatch(batch); panel.Controls.Add(t); } } gridDaily.DataSource = ds.Tables[1]; gridDaily.DataBind(); gridDaily.Visible = true; //DxChartOrder order2 = LinePlot_BedTimes(); //DxChartFactory fatory2 = new DxChartFactory(ds.Tables["stats"], order2); }
protected void GetRandom() { panel3.Controls.Clear(); SQL_utils sql = new SQL_utils("backend"); DataTable dt1 = sql.DataTable_from_SQLstring("select * from vw_tmpTadpole_Randomization_Strata_withTOTS"); DataTable dt2 = sql.DataTable_from_SQLstring("select * from vwTadpole_Randomization_CurrentImbalance"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("age", txtAge.Text, "int")); ps.Add(sql.CreateParam("dq", txtDQ.Text, "int")); ps.Add(sql.CreateParam("gender", txtGender.Text, "int")); ps.Add(sql.CreateParam("uw", txtUW.Text, "int")); ps.Add(sql.CreateParam("ucd", txtUCD.Text, "int")); ps.Add(sql.CreateParam("tot", txtTOT.Text, "int")); DataSet ds = sql.DataSet_from_ProcName("spTadpole_Randomization_DecisionFx", ps); int counter = 0; foreach (DataTable dt in ds.Tables) { counter++; GridView gv = new GridView(); gv.ID = "gvout" + counter.ToString(); Label lbl = new Label(); lbl.Text = "<br/>If subject assigned to..."; lbl.Font.Italic = true; lbl.Font.Size = 11; lbl.ForeColor = Color.Navy; lbl.Font.Bold = true; gv.RowDataBound += new GridViewRowEventHandler(gv_RowDataBound); gv.DataSource = dt; gv.DataBind(); if (counter < 4) { panel3.Controls.Add(gv); Literal lit = new Literal(); lit.Text = "<br/><br/>"; panel3.Controls.Add(lit); } else if (counter == 4) { lbl.Text += "A"; panel4a.Controls.Add(lbl); panel4a.Controls.Add(gv); } else if (counter == 5) { lbl.Text += "B"; panel4b.Controls.Add(lbl); panel4b.Controls.Add(gv); } else if (counter == 6) { lbl.Text += "C"; panel4c.Controls.Add(lbl); panel4c.Controls.Add(gv); } else if (counter == 7) { lbl.Text += "D"; panel4d.Controls.Add(lbl); panel4d.Controls.Add(gv); } } sql.Close(); }