public string SaveFieldsToDB()
{
string result = "";
string result2 = "";
if (dt_metadata.HasRows())
{
SQL_utils sql = new SQL_utils("data");
string first_tokenid = dt_metadata.AsEnumerable().Select(f => f.Field <int>("tokenid")).First().ToString();
if (first_tokenid != "")
{
string sql_n = String.Format("select count(*) from def.REDCap_fields where tokenid = {0}", first_tokenid);
int nflds = sql.IntScalar_from_SQLstring(sql_n);
if (nflds > 0)
{
string sql_del = String.Format("delete from def.REDCap_fields where tokenid = {0}", first_tokenid);
sql.NonQuery_from_SQLstring(sql_del);
result = String.Format("{0} records deleted from [redcap_fields]. ", nflds);
}
result2 = sql.BulkInsert(dt_metadata, "redcap_fields", "def");
}
}
return(String.Format("{0} {1}", result, result2));
}
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);
}
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();
}
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 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);
}
public static void SqlCommand_NonQuery(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;
sql.NonQuery_from_SQLstring(sqlproc, ps);
sql.Close();
}
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 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();
}
}
}
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);
}
}
protected void btnUpdateSMS_OnClick(object sender, EventArgs e)
{
int numselected = ogridSMS.SelectedRecords.Count;
ArrayList sel = ogridSMS.SelectedRecords;
List <string> pks = new List <string>();
if (numselected > 0)
{
for (int i = 0; i < numselected; i++)
{
Hashtable vals = (Hashtable)sel[i];
pks.Add(vals["studymeassubjID"].ToString());
}
}
string sqlcode = String.Format("update tblstudymeassubj set measstatusID={0}, measstatusdetailID={1}, date='{2}' where studymeassubjID in ({3})",
ddlMS.SelectedValue.ToString(), ddlMSD.SelectedValue.ToString(), txtSMSdate.Text, String.Join(",", pks.AsEnumerable()));
SQL_utils sql = new SQL_utils("backend");
sql.NonQuery_from_SQLstring(sqlcode);
LoadSMS(sql, Request.QueryString["id"]);
sql.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();
}
//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();
}
public void SaveToDB(int dataproj_pk)
{
byte[] result;
using (var stream = new MemoryStream())
{
var ser = new BinaryFormatter();
ser.Serialize(stream, this);
stream.Flush();
result = stream.ToArray();
SQL_utils sql = new SQL_utils("data");
//sql.SaveChartOrder(dataproj_pk, result);
sql.Close();
}
Debug.WriteLine(String.Format("This order is {0} bytes long", result.Length));
DxChartOrder neworderC = new DxChartOrder();
using (MemoryStream ms = new MemoryStream(result))
{
var ser2 = new BinaryFormatter();
neworderC = (DxChartOrder)(ser2.Deserialize(ms));
}
}
//private DataRow PopulateRow(DataTable dt, DataRow source_row, List<string> source_colnames)
//{
// DataRow row = dt.NewRow();
// try
// {
// for(int i=0; i < row.ItemArray.Length; i++)
// //foreach (Importfield fld in settings.fields)
// {
// fld.type = dt.Columns[fld.field].DataType.ToString();
// fld.basedate = basedate;
// if (fld.ConvertFromLabelToValue)
// {
// fld.valueset = settings.valuesets.Where(v => v.fieldvaluesetid == fld.fieldvaluesetID).First();
// }
// fld.AssignImportValueToProcess(source_row);
// string rowfield = fld.field;
// row[rowfield] = fld.ProcessedValue();
// }
// return row;
// }
// catch (Exception ex)
// {
// //some error
// return null;
// }
//}
//public void AddRow(bool has_id, DataTable dt, DataRow row)
//{
// if (row != null)
// {
// if (has_id)
// {
// string row_id = row["id"].ToString();
// if (String.IsNullOrEmpty(row_id))
// {
// row["id"] = settings.ID;
// }
// }
// Debug.WriteLine(row["id"].ToString());
// dt.Rows.Add(row);
// }
//}
public int SaveRowToDB(SQL_utils sql, DataRow row)
{
int studymeasid = Convert.ToInt32(row["studymeasid"]);
string tblname = sql.StringScalar_from_SQLstring(String.Format("select coalesce(tblname,'none') from def.tbl where measureid = (select measureid from uwautism_research_backend..tblstudymeas where studymeasid={0})", studymeasid));
int x = 0;
int n = CountValuesInRow(row);
if (studymeasid > 0 & tblname != "none")
{
string code = String.Format("select count(*) from {0} where studymeasid={1} and id='{2}'", tblname, studymeasid, row["record_id"]);
int nrows = sql.IntScalar_from_SQLstring(code);
string msg;
if (nrows > 0)
{
msg = String.Format(" YES ROW nrows:{0} studymeasid={1} and id='{2}' n_values: {3}", nrows, studymeasid, row["record_id"], n);
}
else
{
msg = String.Format(" NO ROW nrows:{0} studymeasid={1} and id='{2}' n_values: {3}", nrows, studymeasid, row["record_id"], n);
}
Debug.WriteLine(msg);
}
return(x);
}
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 SetTitles()
{
string title = ConfigurationManager.AppSettings["MasterTitle"];
string subtitle = ConfigurationManager.AppSettings["MasterSubtitle"];
//title += " [backend=" + ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"] + "]";
SQL_utils sql = new SQL_utils("backend");
title = (Master_studyID > 0) ?
sql.StringScalar_from_SQLstring("select coalesce(mastertitle, 'UWAC DB') mastertitle from vwStudyMastertitle where studyID=" + Master_studyID.ToString()) :
"UWAC DB (no default study selected)";
placeholderMasterTitle.Controls.Clear();
Literal litTitle = new Literal {
Text = title
};
Label lblSubtitle = new Label {
Text = String.Format(" {0}", subtitle), ForeColor = System.Drawing.Color.Gray
};
placeholderMasterTitle.Controls.Add(litTitle);
placeholderMasterTitle.Controls.Add(lblSubtitle);
sql.Close();
}
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 dxMenu_OnItemDataBound(object sender, MenuItemEventArgs e)
{
var itm = e.Item;
SQL_utils sql = new SQL_utils("backend");
int mastertitleID = sql.IntScalar_from_SQLstring("select mastertitleID from vwStudyMastertitle where studyID = " + Master_studyID.ToString());
sql.Close();
List <string> nodes_to_hide = new List <string>();
if (mastertitleID != 1)
{
nodes_to_hide.AddRange(new List <string> {
"Clinic", "Calendar", "Other ID's", "Bernier Lab", "Tadpole", "PATH", "IBIS", "ESS"
, "Sleep Pilot", "(old) Data Projects", "IntHx Data Summary", "IntHx Tx Types & Categories", "Reliability Tracking"
});
}
if (mastertitleID != 2)
{
nodes_to_hide.Add("AVH");
}
itm.Visible = (nodes_to_hide.Contains(itm.Text)) ? false : true;
}
protected void callbackPanel_Callback(object source, DevExpress.Web.CallbackEventArgsBase e)
{
SQL_utils sql = new SQL_utils();
string x = sql.StringScalar_from_SQLstring(String.Format("select processing_results from tblDocVers_ProcessingResults where docversid={0}", e.Parameter.ToString()));
litText.Text = x;
}
protected void CreateHousehold()
{
//Continue with your logic
SQL_utils sql = new SQL_utils("backend");
int hhID = sql.IntScalar_from_SQLstring("exec hh.spHousehold_INSERT '" + NameTextBox.Text + "'");
sql.Close();
if (hhID > 0)
{
Response.Redirect("Household2.aspx?hhID=" + hhID.ToString());
}
//lblEnterName.Visible = false;
//if (txtNewHHname.Text == "New Household Name")
//{
// lblEnterName.Visible = true;
// //ExecBeforeLoad("alert('some value')"); ShowAlert("Enter a name for
// //the new household.");
//}
//else if (txtNewHHname.Text != "" )
//{
//}
}
protected int UpdateSubjset()
{
var subj_csv = uwac.trk.dataops.GetCSV(gridLkupSubj.GridView.GetSelectedFieldValues(gridLkupSubj.KeyFieldName));
if (subj_csv != null)
{
string subjset_pk = (Request.QueryString["subjset_pk"] != null) ? Request.QueryString["subjset_pk"] : "0";
string subjset = txtSubjset.Text;
string subjsetdetails = txtSubjsetdetails.Text;
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("subjset_pk", subjset_pk, "int"));
ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int"));
ps.Add(sql.CreateParam("subjset", subjset, "text"));
ps.Add(sql.CreateParam("subjsetdetails", subjsetdetails, "text"));
ps.Add(sql.CreateParam("sel_subj", subj_csv, "text"));
ps.Add(sql.CreateParam("out", "", "int", "output"));
int newsubjset_pk = sql.NonQuery_from_ProcName("dp.spSubjSet_UPDATE", ps, "out");
sql.Close();
return(newsubjset_pk);
}
else
{
return(0);
}
}
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 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() + ")");
}
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();
}
//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);
}
protected DataTable getDataforCombobox(string sqlcode)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
return(dt);
}
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);
}
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 logConfirmation(string email, string usergroup)
//{
// string textToShow = "Confirmation successful, thank you!";
// email = email ?? "";
// usergroup = usergroup ?? "";
// if (email.Length < 5 || usergroup.Length < 5) textToShow = "Confirmation failed, please email [email protected].";
// SqlCommand sqlCmd = new SqlCommand
// {
// Connection = oConn,
// CommandType = System.Data.CommandType.Text,
// CommandText = "INSERT into confirmations ([email], [usergroup], [datetime]) VALUES ( '" + email + "', '" + usergroup + "', '" + DateTime.Now + "')"
// };
// try
// {
// sqlCmd.ExecuteNonQuery();
// }
// catch(SqlException exc)
// {
// textToShow = "Confirmation failed, please email [email protected]." + exc.Message;
// }
// lblInfo.Text = textToShow;
//}
protected void logConfirmation(string email, string usergroup)
{
string textToShow = "Confirmation successful, thank you!";
email = email ?? "";
usergroup = usergroup ?? "";
if (email.Length < 5 || usergroup.Length < 5)
{
textToShow = "Confirmation failed, please email [email protected].";
}
else
{
SQL_utils sql = new SQL_utils("backend");
string sqlcode = String.Format("INSERT into confirmations ([email], [usergroup], [datetime]) VALUES ( '{0}', '{1}', '{2}')", email, usergroup, DateTime.Now.ToString());
try
{
sql.NonQuery_from_SQLstring(sqlcode);
}
catch (SqlException exc)
{
textToShow = "Confirmation failed, please email [email protected]." + exc.Message;
}
}
lblInfo.Text = textToShow;
}
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
}
}
