protected void btnGetCounts_Click(object sender, EventArgs e)
{
UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelChk.FindControl("study1");
string studies = p.GetChecked_as_CSV();
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("datebeg", txtD1.Text, "date"));
ps.Add(sql.CreateParam("dateend", txtD2.Text, "date"));
ps.Add(sql.CreateParam("studies", studies, "text"));
DataTable dt = sql.DataTable_from_ProcName("spTracking_Sessions_Counts", ps);
gv.DataSource = dt;
gv.DataBind();
if (studies.Contains("1076") | studies.Contains("1077"))
{
List <SqlParameter> psTadpole = new List <SqlParameter>();
psTadpole.Add(sql.CreateParam("datebeg", txtD1.Text, "date"));
psTadpole.Add(sql.CreateParam("dateend", txtD2.Text, "date"));
DataTable dtTadpole = sql.DataTable_from_ProcName("spTracking_Sessions_Counts_TadpoleTX_hrs", psTadpole);
gvTadpole.DataSource = dtTadpole;
gvTadpole.DataBind();
}
}
/// <summary>
/// New from Jeff - incorporating the Default StudyID
/// </summary>
public void GetCurrentDefaultStudyID()
{
//DataTable dt = oDB.GetProc("spSEC_Get_Default_StudyID_for_User");
DataTable dt = oSQL.DataTable_from_ProcName("spSEC_Get_Default_StudyID_for_User");
//2del
//SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
//sqlAdapter.Fill(dt);
if (dt.HasRows())
{
foreach (DataRow row in dt.Rows)
{
master_studyID = Convert.ToInt16(row["defaultstudyID"]);
master_studyname = Convert.ToString(row["studyname"]);
master_studyIDfull = Convert.ToInt32(row["studyIDfull"]);
}
lblDefaultStudy.Text = master_studyname;
lblMasterStudyID.Text = master_studyID.ToString();
lblMasterStudyIDfull.Text = master_studyIDfull.ToString();
Session["master_studyID"] = master_studyID.ToString();
string theme = oSQL.StringScalar_from_SQLstring("exec spSEC_Get_Default_theme_for_User");
Session["master_theme"] = theme.ToString();
}
else
{
Session["master_studyID"] = "0";
Session["master_theme"] = "Glass";
}
}
protected void btnGetAddresses_Click(object sender, EventArgs e)
{
UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelChk.FindControl("study1");
string studies = p.GetChecked_as_CSV();
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studies", studies, "text"));
DataTable dt = sql.DataTable_from_ProcName("spTracking_Subj_AddressList", ps);
dt.TableName = "Subject Addresses";
string datenow = DateTime.Now.Year.ToString() + ((DateTime.Now.Month < 10) ? "0" : "") + DateTime.Now.Month.ToString() + ((DateTime.Now.Day < 10) ? "0" : "") + DateTime.Now.Day.ToString();
string hrminnow = ((DateTime.Now.Hour < 10) ? "0" : "") + DateTime.Now.Hour.ToString() + ((DateTime.Now.Minute < 10) ? "0" : "") + DateTime.Now.Minute.ToString();
string filename = "UWAC_Subject_Addresses_" + datenow + "_" + hrminnow; //TODO
SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, "xlsx");
}
protected void Refresh_Gridview_EditStaff(int staffID)
{
LoadUWAC_ADinfo(staffID);
//populate the staff details
if (oConn.State == ConnectionState.Closed)
{
oConn.ConnectionString = ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"].ToString();
oConn.Open();
}
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", int.Parse(DDL_SelectLabID.SelectedItem.Value).ToString(), "int"));
ps.Add(sql.CreateParam("staffID", staffID.ToString(), "int"));
DataTable dtStaffDetails = sql.DataTable_from_ProcName("spSEC_LabGroup_Staff_details_for_EDIT", ps);
Gridview_EditStaff.DataSource = dtStaffDetails;
Gridview_EditStaff.DataBind();
Panel_EditStaff.Visible = true;
Gridview_EditStaff.Visible = true;
}
protected void getTimepointDates_by_Subject(string subjID)
{
SQL_utils sql = new SQL_utils();
string IDtxt = ddlNewID.SelectedItem.Text;
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studyIDfull", Master.Master_studyIDfull.ToString(), "int"));
ps.Add(sql.CreateParam("subjID", subjID, "int"));
DataTable dt = sql.DataTable_from_ProcName("def.spTimepointDates_by_Subject", ps);
if (dt.Rows.Count > 0)
{
ogrid.DataSource = dt;
ogrid.DataBind();
panelGrid.Visible = true;
}
//if (ds.Tables[1].Rows.Count > 0) {
// ogrid2.DataSource = ds.Tables[1];
// ogrid2.DataBind();
// ogrid2.Visible = true;
//}
sql.Close();
}
protected void GetData(string mo, string mode)
{
SQL_utils sql = new SQL_utils("FinMart");
string txtimportDate = sql.StringScalar_from_SQLstring("select convert(varchar,min(imported_from_EDW),0) from fin.tblEDWData ");
importDate.Text = String.Format("Imported on {0}", txtimportDate);
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("yrmo", mo, "text"));
ps.Add(sql.CreateParam("mode", mode, "text"));
DataTable dt = sql.DataTable_from_ProcName("fin.spGetTabularMonth", ps);
string tblinfo = (dt.HasRows()) ? "" : " - No data returned.";
Label l = new Label();
l.Text = String.Format("<br/><br/>{0} {1}", mo, tblinfo);
l.Font.Size = 12;
if (dt == null)
{
if (mode == "core")
{
panel1.Controls.Add(l);
}
else if (mode == "gift")
{
panel2.Controls.Add(l);
}
}
else
{
GridView gv = new GridView();
gv.RowCreated += new GridViewRowEventHandler(grid_RowCreated);
gv.RowDataBound += new GridViewRowEventHandler(grid_RowDataBound);
gv.DataSource = dt;
gv.DataBind();
if (mode == "core")
{
panel1.Controls.Add(l);
panel1.Controls.Add(gv);
}
else if (mode == "gift")
{
panel2.Controls.Add(l);
panel2.Controls.Add(gv);
}
}
sql.Close();
}
protected void RefreshGrid(Grid grid, string procname, string hhID)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_ProcName(procname, sql.CreateParam("householdID", hhID, "int"));
LoadGrid(grid, dt);
sql.Close();
}
public static DataTable NDAR_DSE_with_Matching_UWFlds(string shortName)
{
SQL_utils sql = new SQL_utils("data");
DataTable dt = sql.DataTable_from_ProcName("def.spNDAR_MatchedFlds", sql.CreateParam("shortname", shortName, "text"));
return(dt);
}
private void Page_Load(object sender, System.EventArgs e)
{
sql = new SQL_utils("backend");
int nPersonID = Convert.ToInt32(Request.QueryString["personID"].ToString());
DataTable dt = sql.DataTable_from_ProcName("spGetPersonInfoByPersonID", sql.CreateParam("personID", nPersonID.ToString(), "int"));
string sJSOutput = "";
if (IsPostBack)
{
// MovePersonIntoStudy();
}
if (dt != null)
{
DataRow row = dt.Rows[0];
string fullname = row["FullName"].ToString();
//string fullname = dt.AsEnumerable().Select(f => f.Field<string>("FullName")).ToString();
lblName.Text = fullname;
tdTitle.InnerHtml = "Create Subject: " + fullname;
//txtPersonName.Value = fullname;
hidPersonID.Value = nPersonID.ToString();
}
else
{
//oReader.Close();
tblMaster.Rows.Clear();
HtmlTableRow oTr = new HtmlTableRow();
HtmlTableCell oTd = new HtmlTableCell();
tblMaster.Attributes["class"] = "";
oTr.Cells.Add(oTd);
tblMaster.Rows.Add(oTr);
oTd.InnerHtml = "The Person ID you supplied is not valid.";
}
LoadMaxIDs();
// LoadOtherIDs(nPersonID);
//if (!IsPostBack)
//{
// LoadGroups();
//}
//DropDownList ddlSite = gridOtherID.Templates[1].Container.FindControl("ddlSite") as DropDownList;
//ddlSite.DataSource = sql_Site;
//ddlSite.DataBind();
sql.Close();
}
protected void getDATADates_by_Subject(string subjID, bool showIntHx, string title)
{
SQL_utils sql = new SQL_utils();
string IDtxt = ddlNewID.SelectedItem.Text;
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studyIDfull", Master.Master_studyIDfull.ToString(), "int"));
ps.Add(sql.CreateParam("subjID", subjID, "int"));
DataTable dt = sql.DataTable_from_ProcName("def.spDATADates_by_Subject", ps);
//see this for why it wasn't working in production:
//http://stackoverflow.com/questions/7933830/asp-net-charting-control-not-working-on-production-server
//I set the ChartImageHandler value in ISS to:
//storage=memory;timeout=20;deleteAfterServicing=true;
//System.Web.UI.DataVisualization.Charting.Chart chart =
//utilMSCharts.MSGeom("point", dt, "datedone", "rnk", "measname",
//"date", "numeric",
// "chartname", "Subject DATA Dates: " + IDtxt, "Date", "Measure", -999, -999, -999, -999, 1100, 800, "mytooltip");
//if (chart != null)
//{
// panelCharts.Controls.Add(chart);
//}
//else
//{
// //there was an error
//}
string chartname = "chart" + subjID.ToString();
string lchart = "";
lchart = utilCharts.Highcharts_Line(chartname, dt, "datedone", "rnk", "measname", "date", "Subject DATA Dates: " + title,
"Admin Date", "Measure", 900, 500, "delink");
Literal lit = new Literal();
lit.Text = lchart;
panelCharts.Controls.Add(lit);
string ID = sql.StringScalar_from_SQLstring("select ID from uwautism_research_backend..tblSubject where subjID=" + subjID);
if (showIntHx)
{
DisplayIntHxCharts(Master.Master_studyID, ID);
}
sql.Close();
}
protected void LoadStudies()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_ProcName("spSEC_GetStudies_CanView_OR_Acess_by_User__Short__with_SelectStudy");
lstStudies.DataSource = dt;
lstStudies.DataTextField = "studyname";
lstStudies.DataValueField = "studyID";
lstStudies.DataBind();
}
protected void LoadStudyIDs()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_ProcName("spSEC_AllowedStudies_By_User");
UpdatePanel_O_CheckBoxes p = new UpdatePanel_O_CheckBoxes(dt, "study1", "Select studies", "studyname", "studyID", "int", "0");
panelChk.Controls.Add(p);
sql.Close();
}
//protected override void Render(HtmlTextWriter writer) {
// Button btn = (Button)panelSubjectInfo.ContentTemplateContainer.FindControlRecursive("btn1");
// Page.ClientScript.RegisterForEventValidation(btn.UniqueID);
// base.Render(writer);
//}
protected void LoadIDs()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_ProcName("spSEC_AllowedIDs_By_Study_User", sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int"));
ddlID.DataSource = dt;
ddlID.DataTextField = "IDlabel";
ddlID.DataValueField = "subjID";
ddlID.DataBind();
sql.Close();
}
protected DataTable GetAddressData()
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int"));
ps.Add(sql.CreateParam("type", "2", "int")); //1=all subjects, 2=main contacts
ps.Add(sql.CreateParam("include", "1", "int"));
DataTable dt = sql.DataTable_from_ProcName("spSubject_Addresses", ps);
return(dt);
}
protected void FindStorageUnits(object sender, System.EventArgs e)
{
//SqlCommand oCmd = new SqlCommand();
//SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
//oTable = new DataTable();
//oCmd.Connection = Master.SqlConn;
//oCmd.CommandTimeout = 90;
//oCmd.CommandType = CommandType.StoredProcedure;
//oCmd.CommandText = "spSearchStorageUnits";
//oCmd.Parameters.AddWithValue("@StudyID", selStudyID.SelectedValue);
//oCmd.Parameters.AddWithValue("@ID", txtSubjID.Text.Trim());
//oCmd.Parameters.AddWithValue("@StorageLocationID", selStorageLocationID.SelectedValue);
//oCmd.Parameters.AddWithValue("@StorageContentID", selStorageContent.SelectedValue);
//if (oCmd.Parameters["@ID"].Value.ToString() == "") {oCmd.Parameters["@ID"].Value = DBNull.Value;}
//if (oCmd.Parameters["@StorageLocationID"].Value.ToString() == "-1") {oCmd.Parameters["@StorageLocationID"].Value = DBNull.Value;}
//if (oCmd.Parameters["@StorageContentID"].Value.ToString() == "-1") {oCmd.Parameters["@StorageContentID"].Value = DBNull.Value;}
//oAdapter.Fill(oTable);
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("StudyID", selStudyID.SelectedValue, "int"));
ps.Add(sql.CreateParam("ID", txtSubjID.Text.Trim(), "text"));
ps.Add(sql.CreateParam("StorageLocationID", selStorageLocationID.SelectedValue, "int"));
ps.Add(sql.CreateParam("StorageContentID", selStorageContent.SelectedValue, "int"));
DataTable oTable = sql.DataTable_from_ProcName("spSearchStorageUnits", ps);
if (oTable.Rows.Count > 0)
{
tblSearchResults.DataSource = oTable;
tblSearchResults.DataBind();
tblSearchResults.Style["display"] = "inline";
tdErrMsg.Style["display"] = "none";
}
else
{
tblSearchResults.Style["display"] = "none";
tdErrMsg.Style["display"] = "inline";
tdErrMsg.Style["color"] = "#ff0000";
tdErrMsg.InnerHtml = "There were no results that matched your search.";
}
}
//private SqlDataReader GetPersonInfo(int nPersonID)
//{
// SqlCommand oCmd = new SqlCommand();
// SqlDataReader oReader;
// oCmd.Connection = Master.SqlConn;
// oCmd.CommandText = "spGetPersonInfoByPersonID";
// oCmd.CommandTimeout = 90;
// oCmd.CommandType = CommandType.StoredProcedure;
// oCmd.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0 , "", DataRowVersion.Current, nPersonID));
// oReader = oCmd.ExecuteReader();
// return oReader;
//}
#endregion
protected void LoadGroups(object sender, EventArgs e)
{
if (cboStudyID.Value.ToString() != "-1")
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_ProcName("spSEC_GetGroups_by_StudyID", sql.CreateParam("studyID", cboStudyID.Value.ToString(), "int"));
cboGroupID.DataSource = dt;
cboGroupID.DataBind();
cboGroupID.ValueField = "groupID";
cboGroupID.TextField = "GroupName";
cboGroupID.SelectedIndex = 0;
}
}
protected DataTable GetCompvars(DataTable data, int pk)
{
//Need to calculate Z-scores if needed
SQL_utils sql = new SQL_utils("data");
DataTable dtcompvars = sql.DataTable_from_ProcName("dp.spCompvars", sql.CreateParam("dataproj_pk", pk.ToString(), "int"));
if (dtcompvars != null)
{
dtcompvars.Columns.Add("ref_M", Nullable.GetUnderlyingType(typeof(double)) ?? typeof(double));
dtcompvars.Columns.Add("ref_SD", Nullable.GetUnderlyingType(typeof(double)) ?? typeof(double));
dtcompvars.Columns.Add("ref_N", Nullable.GetUnderlyingType(typeof(int)) ?? typeof(int));
#region Z values
//Get new values
foreach (DataRow row in dtcompvars.Rows)
{
//Calculate Z
string curr_Variable = row.Field <string>("varname").ToString();
string ref_timept = row.Field <string>("ref_timept").ToString();
if (data.Columns.Contains(curr_Variable))
{
//remove NULL values
var currvar_data = data.AsEnumerable()
.Where(r => !r.IsNull(curr_Variable))
.Where(r => r["timept"].ToString() == ref_timept)
.Select(x => Convert.ToDouble(x[curr_Variable]));
if (currvar_data.Count() > 0)
{
double?ref_M = currvar_data.Average();
double?ref_SD = currvar_data.StandardDeviation();
int ref_N = currvar_data.Count();
row["ref_M"] = ref_M;
row["ref_SD"] = ref_SD;
row["ref_N"] = ref_N;
}
}
}
#endregion
}
sql.Close();
return(dtcompvars);
}
protected void loadList()
{
SQL_utils sql = new SQL_utils();
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studyID", _content_studyID.ToString(), "int"));
//Fill the data table
DataTable dt = sql.DataTable_from_ProcName("spSCORING_ERRORS_GetViews_by_studyID", ps);
sql.Close();
HideAllPanels();
load_dupl_studymeasID();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string tbl = row["tbl"].ToString();
string vw = row["vw"].ToString();
string gv_name = "gv_" + tbl;
string up_name = "UpdatePanel_" + tbl;
//lblInfo.Text += up_name + " " + gv_name + " " + tbl + "<br/>";
UpdatePanel up = (UpdatePanel)FindControlRecursive(this.Page, up_name);
if (up != null)
{
// lblInfo.Text += "..." + up.ID + "<br/>";
loadScoringErrors(up, gv_name, vw);
up.Visible = true;
}
else
{
// lblInfo.Text += "...NULL<br/>" ;
}
//
}
}
protected void GetInfo(string infotype, string sqlproc)
{
SQL_utils sql = new SQL_utils("backend");
// Create a DataTable of the selected studies
DataTable dt = new DataTable();
dt.Columns.Add("fld", typeof(string));
dt.Columns.Add("value", typeof(int));
int counter = 0;
foreach (ListItem li in lstStudies.Items)
{
counter++;
lblInfo.Text += ", " + counter.ToString();
if (li.Selected)
{
DataRow r = dt.NewRow();
r["fld"] = "studyID";
r["value"] = Convert.ToInt16(li.Value);
dt.Rows.Add(r);
lblInfo.Text += "SELECTED";
}
}
ViewState["SelectedStudies"] = dt;
if (dt.Rows.Count > 0)
{
SqlParameter p = sql.CreateParam("studies", dt);
DataTable info = sql.DataTable_from_ProcName(infotype, p);
//ShowSelectedStudies(mailing);
SpreadsheetGearUtils.SaveDataTableToExcel(info, "mailing", true, "xlsx");
}
else
{
lblInfo.Text = "0 records in data table.";
}
}
protected DataTable GetTrackingData(string mode)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
//Get Info
DataSet ds = LoadSelectedItems_into_DataSet();
foreach (DataTable dt in ds.Tables)
{
ps.Add(sql.CreateParam(dt.TableName, dt));
}
DataTable dtX = sql.DataTable_from_ProcName("spTracking_GetInfo_" + mode, ps);
sql.Close();
return(dtX);
}
protected void btnGetIDList_Click(object sender, EventArgs e)
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
ps.Add(sql.CreateParam("study_csv", "all", "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps);
string filename = "code6_IDs_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, true, "csv");
sql.Close();
}
}
protected void Populate_IDdelete_DDL(int studymeasID)
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studymeasID", ddl_studymeasID.SelectedValue, "int"));
ps.Add(sql.CreateParam("clin", ddl_Clin.SelectedValue, "text"));
ps.Add(sql.CreateParam("mode", "0,1,4", "text"));
DataTable dtSM = sql.DataTable_from_ProcName("spID_and_Indexnum_by_smID", ps);
sql.Close();
ddl_IDdelete.DataSource = dtSM;
ddl_IDdelete.DataTextField = "IDindex";
ddl_IDdelete.DataValueField = "pk";
ddl_IDdelete.DataBind();
UpdatePanel01.Update();
}
protected void Populate_StudyMeas_New_DDL(int studymeasID)
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studymeasID", studymeasID.ToString(), "int"));
DataTable dtSM = sql.DataTable_from_ProcName("spDEF__Potential_Movable_Studymeas", ps);
sql.Close();
ddl_Studymeas_New.DataSource = dtSM;
ddl_Studymeas_New.DataTextField = "studymeasname";
ddl_Studymeas_New.DataValueField = "studymeasID";
ddl_Studymeas_New.DataBind();
UpdatePanel01.Update();
}
protected void ShowCode6_Single()
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
ps.Add(sql.CreateParam("code6", txtCode6.Text, "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_all_IDs_for_single_code6", ps);
gvSingle.DataSource = dt;
gvSingle.DataBind();
gvSingle.Visible = true;
sql.Close();
}
}
public static string GetSQL_for_NDAR_studymeas_to_include(string shortName, int studyID, int tp)
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> p = new List <SqlParameter>();
p.Add(sql.CreateParam("shortName", shortName, "string"));
p.Add(sql.CreateParam("studyID", studyID.ToString(), "int"));
p.Add(sql.CreateParam("tp", tp.ToString(), "int"));
DataTable dt = sql.DataTable_from_ProcName("spNDAR_get_studymeasID_to_include", p);
string sql_for_studymeasIDs = "";
if (dt != null)
{
List <string> myIds = new List <string>();
foreach (DataRow dr in dt.Rows)
{
myIds.Add(Convert.ToString(dr["studymeasID"]));
}
sql_for_studymeasIDs = string.Join(",", myIds);
}
//var idlist = dt.AsEnumerable().Select(r => r.Field<string>("studymeasID")).ToArray();
//string sql_for_studymeasIDs = string.Join(",", idlist);
//string sql_for_studymeasIDs = dt.AsEnumerable()
// .Select(row => row["studymeasID"].ToString())
// .Aggregate((s1, s2) => String.Concat(s1, "," + s2));
// List<string> list_sql = dt.AsEnumerable().Select(r => r.Field<string>("studymeasID")).ToList<string>();
//string sql_for_studymeasIDs = String.Join(" ", (from row In dt.AsEnumerable() select row("studymeasID")).ToList<string>);
sql.Close();
return(sql_for_studymeasIDs);
}
protected void Load_gv_ScoreProc(int tblpk)
{
try
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("tblpk", tblpk.ToString(), "int"));
DataTable dt = sql.DataTable_from_ProcName("spDEF__spScore_by_tblpk", ps);
sql.Close();
int i = 0;
foreach (DataRow row in dt.Rows)
{
if (i == 0)
{
lblTableName.Text = Convert.ToString(row["tblname"]);
lblspName.Text = Convert.ToString(row["spName"]);
lblLength.Text = Convert.ToString(row["spTextHTML_Length"]);
foo.Text = "<pre class=\"prettyprint\">" + Convert.ToString(row["spTextHTML"]) + "</pre>";
}
i++;
}
//gv_ScoreProc.DataSource = dt;
//gv_ScoreProc.DataBind();
}
catch (SqlException)
{
}
}
protected void Load_LabStaff()
{
int labID = int.Parse(DDL_SelectLabID.SelectedValue.ToString());
int studyactive = int.Parse(DDL_StudyActive.SelectedValue.ToString());
if (labID > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> p = new List <SqlParameter>();
p.Add(sql.CreateParam("labID", labID.ToString(), "int"));
p.Add(sql.CreateParam("studyactive", studyactive.ToString(), "int"));
p.Add(sql.CreateParam("single_staffID", "0", "int"));
DataTable dt = sql.DataTable_from_ProcName("spSEC_Lab_and_AllStaffInLab_by_Studies", p);
string[] keepCols = new string[] { "staffID", "StaffName", "LastLogon", "LabName", "N_Labs" };
DataTable dt_pivot = utilsPivot.Pivot(dt, keepCols, "StudyName", "color_allowed_groups", "StaffName", "StudyName DESC");
sql.Close();
Gridview_LabGroup_Staff2.DataSource = dt_pivot;
Gridview_LabGroup_Staff2.DataBind();
Gridview_LabGroup_Staff2.Visible = true;
Panel_AddNewStaff.Visible = false;
btnShow_AddNew.Visible = true;
Panel_MultLabs.Visible = true;
Panel_EditStaff.Visible = false;
Panel_UWAC_ADinfo.Visible = false;
}
}
public static string GetActigraphProperty(string id, int studymeasID, string propname)
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("id", id, "text"));
ps.Add(sql.CreateParam("smID_epoch", studymeasID.ToString(), "int"));
DataTable dt_actprops = sql.DataTable_from_ProcName("spActigraphProps", ps);
sql.Close();
string act_propvalue;
try
{
act_propvalue = dt_actprops.AsEnumerable().Where(f => f.Field <string>("ap_param") == propname)
.Select(f => f.Field <string>("ap_value")).First().ToString();
}
catch (Exception ex)
{
act_propvalue = "Not Found!";
}
return(act_propvalue);
}
protected void Export_IBIS_Vineland()
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
string ID = ddlID2.SelectedValue.ToString();
string studymeasID = ddlStudymeasID.SelectedValue.ToString();
string dccID_1 = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_ID('" + ID + "')");
string subjecttype = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_subjecttype('" + ID + "')");
int tp = -1;
if (studymeasID == "2671")
{
tp = 3; // 2671 IBIS: 03_Vineland-II (surv intvw)
}
if (studymeasID == "1811")
{
tp = 6; // 1811 IBIS: 06_Vineland-II (intvw)
}
if (studymeasID == "1837")
{
tp = 12; // 1837 IBIS: 12_Vineland-II (intvw)
}
if (studymeasID == "2037")
{
tp = 18; // 2037 IBIS: 18_Vineland-II (surv intvw)
}
if (studymeasID == "1863")
{
tp = 24; // 1863 IBIS: 24_Vineland-II (intvw)
}
if (studymeasID == "2293")
{
tp = 36; // 2293 IBIS: 36_Vineland-II (surv intvw)
}
if (studymeasID == "2750")
{
tp = 3; // 2750 IBIS-2: 03_Vineland-II (surv intvw)
}
if (studymeasID == "2740")
{
tp = 3; // 2740 IBIS-2: 03_Vineland-II (surv intvw)_Proband
}
if (studymeasID == "2760")
{
tp = 6; // 2760 IBIS-2: 06_Vineland-II (surv intvw)
}
if (studymeasID == "2755")
{
tp = 6; // 2755 IBIS-2: 06_Vineland-II (surv intvw)_Proband
}
if (studymeasID == "2773")
{
tp = 9; // 2773 IBIS-2: 09_Vineland-II (surv intvw)
}
if (studymeasID == "2784")
{
tp = 12; // 2784 IBIS-2: 12_Vineland-II (surv intvw)
}
if (studymeasID == "2798")
{
tp = 15; // 2798 IBIS-2: 15_Vineland-II (surv intvw)
}
if (studymeasID == "2810")
{
tp = 24; // 2810 IBIS-2: 24_Vineland-II (surv intvw)
}
if (studymeasID == "2827")
{
tp = 36; // 2827 IBIS-2: 36_Vineland-II (surv intvw)
}
if (studymeasID == "3678")
{
tp = 0; // 3678 IBIS-2: PROBAND_Vineland-II (surv intvw)
}
string tp_text = "";
if (tp == 0)
{
tp_text = "Screening";
}
if (tp == 3)
{
tp_text = "v03";
}
if (tp == 6)
{
tp_text = "v06";
}
if (tp == 9)
{
tp_text = "v09";
}
if (tp == 12)
{
tp_text = "v12";
}
if (tp == 15)
{
tp_text = "v15";
}
if (tp == 18)
{
tp_text = "v18";
}
if (tp == 24)
{
tp_text = "v24";
}
if (tp == 36)
{
tp_text = "v36";
}
if (tp_text != "")
{
ps.Add(sql.CreateParam("ID", ID, "text"));
ps.Add(sql.CreateParam("studymeasID", studymeasID, "int"));
DataTable dt = sql.DataTable_from_ProcName("spIBIS2_VinelandIISurvey_Recode_for_IBIS_DCC_upload", ps);
string id_nopr = ID.Replace("pr", "").Replace("PR", "");
if (dt.HasRows())
{
string filename = "vineland_" + subjecttype + "_" + dccID_1 + "_" + id_nopr + "_" + tp_text + ".txt";
SpreadsheetGearUtils.SaveDataTableToFile(dt, filename, "IBISVine");
lblNoData.Text = "Data saved to ??? R:\\IBIS-2\\Vineland_Uploads<br/>" + filename;
lblNoData.ForeColor = Color.Blue;
}
else
{
lblNoData.Text = "No data returned";
lblNoData.ForeColor = Color.Red;
}
}
sql.Close();
}
protected void Load_gv_ScoreProc(int tblpk)
{
try
{
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("tblpk", tblpk.ToString(), "int"));
DataTable dt = sql.DataTable_from_ProcName("spDEF__spScore_by_tblpk", ps);
int n1 = dt.Rows.Count;
string proccode = "";
string objcode = "";
int i = 0;
foreach (DataRow row in dt.Rows)
{
if (i == 0)
{
//get referenced objects
//string refobj_code = String.Format("exec spDEF_ReferencedObj_in_ScoringProc '{0}'", row["spName"].ToString());
string refobj_code = "SELECT o.name AS ReferencingObject, o.type_desc ReferencingObject_type, sd.referenced_entity_name AS ReferencedObject, o2.type_desc ReferencedObject_type " + Environment.NewLine +
" FROM sys.sql_expression_dependencies AS sd " + Environment.NewLine +
" INNER JOIN sys.objects AS o ON o.object_id = sd.referencing_id " + Environment.NewLine +
" INNER JOIN sys.objects AS o2 ON o2.object_id = sd.referenced_id " + Environment.NewLine +
" WHERE o.name = '" + row["spName"].ToString() + "'" + Environment.NewLine +
" and(o2.type_desc like '%function%' or o2.type_desc like '%procedure%') " + Environment.NewLine +
" and sd.referenced_entity_name != 'systemuser' ";
Debug.WriteLine(refobj_code);
Debug.WriteLine("[" + row["spName"].ToString() + "]");
DataTable dt_refobj = sql.DataTable_from_SQLstring(refobj_code);
lblTableName.Text = Convert.ToString(row["tblname"]);
lblspName.Text = Convert.ToString(row["spName"]);
lblLength.Text = Convert.ToString(row["spTextHTML_Length"]);
int n = -1;
if (dt_refobj != null)
{
n = dt_refobj.Rows.Count;
}
lblRefObj.Text = n.ToString(); // (dt_refobj != null) ? dt_refobj.Rows.Count.ToString() : "0";
proccode = Convert.ToString(row["spTextHTML"]);
Literal litproc = new Literal();
litproc = FormatCodeOfObject(sql, row["spName"].ToString());
placehold_proc.Controls.Add(litproc);
//display referenced objects
foreach (DataRow obj in dt_refobj.Rows)
{
string refobjname = obj["ReferencedObject"].ToString();
Literal lit = new Literal();
lit = FormatCodeOfObject(sql, refobjname);
placehold_obj.Controls.Add(lit);
//string myobjcode = sql.StringScalar_from_SQLstring("exec spRefObj_FullSQLtext " + refobjname);
//DataTable dt_objcode = sql.DataTable_from_SQLstring("exec sp_helptext '" + refobjname + "'");
//objcode = String.Format("<br/><br/><b><u>{0}</u></b><br/>", refobjname);
//for(int c=0; c < dt_objcode.Rows.Count; c++)
//{
// DataRow r = dt_objcode.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 = objcode;
}
}
i++;
}
//lit_ProcCode.Text = "<pre class=\"prettyprint\">" + proccode + "</pre>";
//lit_ObjCode.Text = "<pre class=\"prettyprint\">" + objcode + "</pre>";
//lit_ProcCode.Text = proccode ;
//lit_ObjCode.Text = objcode ;
//gv_ScoreProc.DataSource = dt;
//gv_ScoreProc.DataBind();
sql.Close();
}
catch (SqlException)
{
}
}