protected void ddl_ChangeGroup_SelectedIndexChanged(object sender, EventArgs e)
{
int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString());
if (subjID > 0)
{
int newgroup1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString());
int newgroup2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString());
SQL_utils sql = new SQL_utils("backend");
int oldgroup = sql.IntScalar_from_SQLstring(String.Format("select groupID from tblsubject where subjID={0}", subjID));
if (newgroup1 == newgroup2 && newgroup1 > 0 && newgroup1 != oldgroup)
{
string sqlcode = String.Format("select groupID from tblsubject where subjID = {0}", subjID);
int oldgroupID = sql.IntScalar_from_SQLstring(sqlcode);
if (newgroup1 != oldgroupID)
{
btn_ChangeGroup.Visible = true;
}
}
else
{
btn_ChangeGroup.Visible = false;
}
sql.Close();
}
}
public void SaveToDB()
{
SQL_utils sql = new SQL_utils("data");
int current_maxpk = sql.IntScalar_from_SQLstring("select max(datauploadPK) maxPK from def.DataUpload");
string sqlcode = String.Format("insert into def.DataUpload (filename, studymeasID, ID, isMultSubj, uploaded, uploadedby) values ({0},{1},{2},{3},getdate(), sec.systemuser())"
, filename, studymeasID, ID, isMultSubj);
sql.NonQuery_from_SQLstring(sqlcode);
string sqlcode2 = String.Format("select datauploadPK from def.DataUpload where filename='{0}' and uploadedby=sec.systemuser() and datauploadPK > {1} ", filename, current_maxpk);
datauploadPK = sql.IntScalar_from_SQLstring(sqlcode2);
sql.Close();
if (datauploadPK > 0)
{
results.Add(String.Format("Added datauploadPK {0}", datauploadPK));
}
else
{
results.Add(String.Format("DataUpload Not Logged for '{0}'!", filename));
}
}
protected void btnInsertToken_Click(object sender, EventArgs e)
{
string url = "https://redcap.iths.org/api/";
SQL_utils sql = new SQL_utils("data");
//Make sure token is unique
int numtokens = sql.IntScalar_from_SQLstring(String.Format("select count(*) from def.redcaptoken where token='{0}'", txtToken.Text));
if (numtokens > 0)
{
lblRESULTS.ForeColor = Color.Red;
lblRESULTS.Text = "This token has already been entered into the DB. Try again.";
}
else
{
int newtokenid = sql.IntScalar_from_SQLstring(String.Format("select max(tokenid)+1 from def.REDCapToken "));
string sql_newtoken = String.Format("insert into def.REDCapToken(token, project, url, idfld, tokenid) Values('{0}','{1}','{2}','{3}',{4})", txtToken.Text, txtProjectname.Text, url, txtIDfld.Text, newtokenid);
sql.NonQuery_from_SQLstring(sql_newtoken);
sql.NonQuery_from_SQLstring(String.Format("insert into def.REDCapToken_study(tokenid, studyid) values({0},{1})", newtokenid, Master.Master_studyID));
lblRESULTS.ForeColor = Color.ForestGreen;
lblRESULTS.Text = "Token added.";
}
sql.Close();
gv_tokens.DataBind();
}
protected void btnSubmitMissing_Click(object sender, EventArgs e)
{
bool isInvalid = string.IsNullOrEmpty(cboDataUploadType.Value.ToString());
if (!isInvalid)
{
int doctypeID = Convert.ToInt32(cboDataUploadType.Value);
int entitytypeid = Convert.ToInt32(Request.QueryString["entitytypeid"]);
int entityid = Convert.ToInt32(Request.QueryString["entityid"]);
SQL_utils sql = new SQL_utils();
int docid = sql.IntScalar_from_SQLstring("select max(docid)+1 from tbldoc");
int docversid = sql.IntScalar_from_SQLstring("select max(docversid)+1 from tbldocvers");
if (docid > 0 & docversid > 0)
{
string code1 = String.Format("insert into tbldoc(docid, doctitle, doctypeid, docstatusid) values({0}, 'MISSING', {1}, 5)", docid, doctypeID);
sql.NonQuery_from_SQLstring(code1);
string code2 = String.Format("insert into tbldocvers(docid, docversid, docexists) values({0}, {1}, 0)", docid, docversid);
sql.NonQuery_from_SQLstring(code2);
string code3 = String.Format("insert into tbldocfk(docid, entitytypeid, entityid) values({0}, {1}, {2})", docid, entitytypeid, entityid);
sql.NonQuery_from_SQLstring(code3);
btnSubmitMissing.Visible = false;
lblMissingLogged.Visible = true;
}
}
}
public string SaveVarsToDB(SQL_utils sql, int extractionmodeID)
{
string info = "";
int num_vars = 0;
if (vars.Count > 0)
{
try
{
foreach (uwac.Variable v in vars)
{
//Check that var is not already defined
int var_in_flds = sql.IntScalar_from_SQLstring(String.Format("select count(*) from def.fld where tblpk={0} and fldname='{1}'", tblpk, v.varname));
if (var_in_flds == 0)
{
string sqlcode;
if (extractionmodeID >= 0)
{
sqlcode = String.Format("insert into def.fld(tblpk, ord_pos, fldname, FieldDataType, FieldLabel, FieldValueSetID, fldextractionmode) " +
" values({0},{1},'{2}','{3}','{4}',{5},{6})"
, tblpk, num_vars, v.varname, v.datatype, v.varlabel, v.fieldvaluesetid, extractionmodeID);
}
else
{
sqlcode = String.Format("insert into def.fld(tblpk, ord_pos, fldname, FieldDataType, FieldLabel, FieldValueSetID) " +
" values({0},{1},'{2}','{3}','{4}',{5})"
, tblpk, num_vars, v.varname, v.datatype, v.varlabel, v.fieldvaluesetid);
}
sql.NonQuery_from_SQLstring(sqlcode);
int newfldpk = sql.IntScalar_from_SQLstring(
String.Format("select fldpk from def.fld where tblpk={0} and fldname='{1}'", tblpk, v.varname));
if (newfldpk > 0)
{
num_vars++;
}
}
}
if (num_vars > 0)
{
info += String.Format(" {0} variables added.", num_vars);
}
return(info);
}
catch (Exception ex)
{
return(String.Format("ERROR. {0}", ex.Message));
}
}
else
{
return("No variables defined.");
}
}
public int LogUpload_Doc()
{
OrderedDictionary newvalues = new OrderedDictionary();
//newvalues.Add("docID", docID);
//newvalues.Add("docversID", docversID);
newvalues.Add("docfilename", docfilename);
newvalues.Add("doctitle", doctitle);
newvalues.Add("docdesc", docdesc);
newvalues.Add("doctypeID", doctypeID);
newvalues.Add("docstatusID", docstatusID);
newvalues.Add("fileext", fileext);
newvalues.Add("origfilename", origfilename);
//Insert Doc
docID = DxDbOps.InsertSqlCode_ReturnPK(newvalues, "tblDoc", "backend", "dbo", "DocID");
if (docID > 0)
{
newvalues.Add("DocID", docID);
//Insert DocVers
docversID = DxDbOps.InsertSqlCode_ReturnPK(newvalues, "tblDocVers", "backend", "dbo", "DocVersID");
//Insert entity link - we link this DocID to this MeasureID
SQL_utils sql = new SQL_utils("backend");
string sql_doc = String.Format("select docID from tbldoc where doctitle='{0}' and created > dateadd(minute, -3, getdate())", doctitle);
string sql_docvers = String.Format("select docversID from tbldocvers where origfilename='{0}' and docID={1} and created > dateadd(minute, -3, getdate())"
, origfilename, docID);
int thisDocID = sql.IntScalar_from_SQLstring(sql_doc);
int thisDocVersID = sql.IntScalar_from_SQLstring(sql_docvers);
string fk_code = String.Format("insert into tblDocFK(DocID, EntitytypeID, EntityID) values({0}, {1}, {2})"
, thisDocID, (int)linked_entitytype, linked_entityID);
if (thisDocID == docID & thisDocVersID == docversID)
{
docfilename = String.Format("DocVersID_{0}{1}", docversID, fileext);
sql.NonQuery_from_SQLstring(fk_code);
sql.Close();
return(docversID);
}
else
{
sql.Close();
return(-1);
}
}
return(-1);
}
protected void ValidateNewID(object sender, EventArgs e)
{
string id1 = newID1.Text;
string id2 = newID2.Text;
string oldsubjid = ddl_newID.SelectedValue;
SQL_utils sql = new SQL_utils("data");
string oldid = sql.StringScalar_from_SQLstring(String.Format("select id from uwautism_research_backend..tblsubject where subjID={0}", oldsubjid));
if (oldsubjid != "-1")
{
if (id1 == id2 && !String.IsNullOrEmpty(id1))
{
int n_newid = sql.IntScalar_from_SQLstring(String.Format("select coalesce(count(*),0) n from uwautism_research_backend..vwMasterStatus_S where studyID={0} and ID='{1}'"
, Master.Master_studyID, id1));
if (n_newid > 0)
{
error_label.Text = String.Format("The ID '{0}' is already taken.", id1);
}
else
{
//new ID is unique
string sql_ndata = String.Format("select count(*) n from uwautism_research_data..datData where ID='{0}' and studymeasID in (select studymeasID from uwautism_research_backend..tblstudymeas where studyID={1})"
, oldid, Master.Master_studyID);
int ndata = sql.IntScalar_from_SQLstring(sql_ndata);
List <string> dataupdateresult = new List <string>();
if (ndata > 0)
{
error_label.Text = String.Format("'{0}' is available. However, there are {1} entered data records in which ID='{2}'.<br/>Check the box if you want to update the entered data to this new ID '{0}'.<br/>Do not check the box if you want the {1} entered data records to remain as ID '{2}'.", id1, ndata, oldid);
chkDataToo.Visible = true;
}
else
{
error_label.Text = String.Format("'{0}' is available. There is no entered data with the original ID '{1}'.", id1, oldid);
//txtOVERRIDE.Text = "Enter 'fix' here to update entered data with the new ID.";
}
btnNewID.Visible = true;
btnCancelNewID.Visible = true;
btnValidateNewID.Visible = false;
}
}
else
{
error_label.Text = "New ID values must match.";
}
sql.Close();
}
}
protected void btnNew_OnCLick(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
try
{
//string code = String.Format(String.Format("exec spSEC_StudyDesign__BeginNewStudy '{0}', '{1}' ", txtName.Value, txtNameFull.Value));
int newstudyID = sql.IntScalar_from_SQLstring("select max(studyID) + 1 from tblStudy");
int newgroupID = sql.IntScalar_from_SQLstring("select max(groupID) + 1 from tblGroup");
//int labID = sql.IntScalar_from_SQLstring("select coalesce(defaultlabid, 1) from tblstaff where staffID = sec.systemuser_staffID()");
int labID = Convert.ToInt32(cboLab.SelectedItem.Value.ToString());
string studyname = txtName.Value.ToString();
string studyfullname = txtNameFull.Value.ToString();
if (newstudyID > 0 & labID > 0)
{
string code0 = String.Format("insert into dbo.tblstudy(studyID, studyname, studyfullname, active, labID) values({0}, '{1}', '{2}', 1, {3});", newstudyID, studyname, studyfullname, labID);
string code1 = String.Format("insert into dbo.tblgroup(groupID, studyID, groupname, groupabbr, goaln, siteID, sortorder) values({0}, {1}, 'Group 1', 'Grp1', 0, 3, 1);", newgroupID, newstudyID);
string code2 = String.Format("insert into dbo.tblLabGroup(groupid, labid, labgroup_enabled) values({0}, {1}, 1 )", newgroupID, labID);
string code3 = String.Format("insert into dbo.tblLabGroup_staff(staffid, dbroleid, labgroupid) select sec.systemuser_staffID(), 1, max(labgroupID) from tbllabgroup where groupID={0};", newgroupID);
string code4 = String.Format("insert into dbo.tblTimepoint(studyID, timepoint, timepoint_text) values ({0}, 1, 'T1');", newstudyID);
string code = String.Format("{0} {1} {2} {3} {4}", code0, code1, code2, code3, code4);
var result1 = sql.NonQuery_from_SQLstring_withRollback(code);
if (result1 == "success")
{
try
{
sql.NonQuery_from_SQLstring(String.Format("update tblStaff set defaultstudyID={0}", newstudyID));
Response.Redirect("StudyDesign.aspx");
}
catch (Exception ex) { }
}
}
}
catch (Exception ex)
{
}
sql.Close();
}
public static string SaveToDB(string shortname)
{
NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortname);
DataTable dse = NDAR.NDARDataStructureElements_to_DataTable(ds);
string results = "";
//lblInfo.Text = "nrow = {" + dse.Rows.Count.ToString() + "}";
SQL_utils sql = new SQL_utils();
//Check is NDAR_DS exists
int n_ds = sql.IntScalar_from_SQLstring(
String.Format("select count(*) from NDAR_DS where shortname='{0}'", shortname));
if (n_ds == 0)
{
// then insert DSE
string code = String.Format("insert into ndar_ds (shortname, title, datatype, status ) values ('{0}', '{1}','{2}','{3}')"
, ds.shortName, ds.title, ds.dataType, ds.status);
sql.NonQuery_from_SQLstring(code);
}
//Check is NDAR_DS exists
int n_dse = sql.IntScalar_from_SQLstring(
String.Format("select count(*) from NDAR_DSE where shortname='{0}'", shortname));
if (n_dse > 0)
{
sql.NonQuery_from_SQLstring(String.Format("delete from NDAR_DSE where shortname='{0}'", shortname));
}
SqlParameter p = sql.CreateParam("NDAR_DSE", dse);
try
{
sql.NonQuery_from_ProcName("spNDAR_Insert_DSE", p);
results = "Successfully imported " + shortname;
}
catch (Exception ex)
{
results = "Error: " + ex.Message;
}
sql.Close();
return(results);
}
protected void btnHousehold_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
if (Request.QueryString["ID"] != null)
{
int hhid = sql.IntScalar_from_SQLstring("select householdID from vwMasterStatus_S where ID='" + Request.QueryString["ID"] + "' and studyID=" + Master.Master_studyID.ToString());
Response.Redirect("~/Tracking/Household2.aspx?hhID=" + hhid.ToString());
}
else if (Request.QueryString["subjid"] != null)
{
int hhid = sql.IntScalar_from_SQLstring("select householdID from vwMasterStatus_S where subjID='" + Request.QueryString["subjid"] + "' and studyID=" + Master.Master_studyID.ToString());
Response.Redirect("~/Tracking/Household2.aspx?hhID=" + hhid.ToString());
}
}
protected void btnCreateNew_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("kawdata");
int docid = sql.IntScalar_from_SQLstring(String.Format("select docid from tblDocVers where docversid={0}", Request.QueryString["dvID"]));
int entitytypeid = sql.IntScalar_from_SQLstring(String.Format("select entitytypeid from vwDocVers where docversid={0}", Request.QueryString["dvID"]));
int entityid = Convert.ToInt32(cbo_NewEntity.Value.ToString());
string sqlcode = String.Format("insert into tbldocfk (docid, entitytypeid, entityid) values({0},{1},{2})"
, docid, entitytypeid, entityid);
sql.NonQuery_from_SQLstring(sqlcode);
sql.Close();
}
protected void SetDataChk(string subjID)
{
SQL_utils sql = new SQL_utils("backend");
int datachkID = sql.IntScalar_from_SQLstring("select datachkID from uwautism_research_backend..tblSubject where subjID= " + subjID);
string str_datachkID = datachkID.ToString();
//foreach(ListItem itm in ddlDataChk.Items)
//{
// if(datachkID == itm.Value) itm.Selected=true;
//}
// ddlDataChk.SelectedValue = ddlDataChk.Items.FindByValue(datachkID.ToString()).ToString();
ddlDataChk.DataBind();
//ddlDataChk.ClearSelection();
ddlDataChk.Items.FindByValue(str_datachkID).Selected = true;
btnUpdateDataChk.Visible = true;
ddlDataChk.Visible = true;
panelGrid.Visible = true;
sql.Close();
}
public Datadictionary(DataTable dt_meta, List <string> formnames, int studyID)
{
Initialize();
CreateDatadictionaryFromREDCapMeta(dt_meta, formnames);
if (formnames.Count == 1)
{
SQL_utils sql = new SQL_utils("data");
measureid = sql.IntScalar_from_SQLstring(String.Format("select measureid from def.REDCap_form where form_name='{0}' and tokenid in (select tokenid from def.REDCapToken_study where studyid={1})", formnames[0], studyID));
measname = sql.StringScalar_from_SQLstring(String.Format("select measname from uwautism_research_backend..tblmeasure where measureid = {0}", measureid));
tblname = sql.StringScalar_from_SQLstring(String.Format("select tblname from def.tbl where measureid={0}", measureid));
tblpk = sql.IntScalar_from_SQLstring(String.Format("select tblpk from def.tbl where measureid={0}", measureid));
sql.Close();
}
}
public static int CreateValueSet(SQL_utils sql, int mID, uwac.Valueset vset)
{
int max = sql.IntScalar_from_SQLstring("select max(fieldvaluesetID) from datfieldvalueset");
string measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblmeasure where measureID=" + mID.ToString());
int newmax = (max > 0) ? max + 1 : 0;
if (newmax > 0)
{
try
{
string setname = String.Format("{0} set {1}", measname, vset.localvaluesetid.ToString());
string code = String.Format("insert into datfieldvalueset (fieldvaluesetID, fieldvaluesetdesc) values({0},'{1}')"
, newmax, setname);
sql.NonQuery_from_SQLstring(code);
foreach (Valuesetitem itm in vset.valitems)
{
AddValueSetItem(sql, newmax, Convert.ToInt32(itm.value), itm.label);
}
return(newmax);
}
catch (Exception ex)
{
return(-1);
}
}
else
{
return(-1);
}
}
//protected void ValidateChangeGroup(object sender, EventArgs e)
//{
// int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString());
// int newgroupID1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString());
// int newgroupID2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString());
// if(newgroupID1 == newgroupID2 && newgroupID1 > 0 && subjID > 0)
// {
// btn_ChangeGroup.Visible = true;
// }
//}
protected void ChangeGroup(object sender, EventArgs e)
{
int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString());
int newgroupID1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString());
int newgroupID2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString());
SQL_utils sql = new SQL_utils("backend");
int oldgroup = sql.IntScalar_from_SQLstring(String.Format("select groupID from tblsubject where subjID={0}", subjID));
if (newgroupID1 == newgroupID2 && newgroupID1 > 0 && subjID > 0 && newgroupID1 != oldgroup)
{
try
{
string sqlcode = String.Format(" update tblsubject set groupID = {0} where subjID = {1};", newgroupID1, subjID);
string sqlAddActions = String.Format("exec spUpdate_SA_SM_for_Subj_with_new_group {0}", subjID);
string sqllogchange = String.Format("insert into uwautism_research_backend..AuditChanges(pk, pkfldname, tblname, fldname, oldvalue, newvalue, updated, updatedBy) values({0},'subjID','tblSubject', 'groupID','{1}','{2}',getdate(),sec.systemuser());"
, subjID, oldgroup, newgroupID1);
string sql_all = String.Format("{0} {1} {2}", sqlcode, sqlAddActions, sqllogchange);
sql.NonQuery_from_SQLstring_withRollback(sql_all);
error_label.Text = String.Format("Group changed and actions and measures updated.");
btn_ChangeGroup.Visible = false;
}
catch (Exception ex)
{ }
}
sql.Close();
}
//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);
}
protected string ProcessDictionary(string filename)
{
string insertresults = "";
SQL_utils sql = new SQL_utils("data");
//Check for tblpk
int tblpk = sql.IntScalar_from_SQLstring("select coalesce(tblpk,-1) from def.tbl where measureID=" + Request.QueryString["mID"]);
if (tblpk > 0)
{
DataSet ds = SpreadsheetGearUtils.GetDataSet(filename, false);
DataTable dt = ds.Tables[0];
// Add tblpk
DataColumn col = new DataColumn("tblpk", typeof(int));
col.DefaultValue = tblpk;
// Add fldextractionmode - and thus allow this measure to be imported
DataColumn col2 = new DataColumn("fldextractionmode", typeof(int));
int mode = (int)FieldExtractionMode.matchFldname;
col2.DefaultValue = mode;
dt.Columns.AddRange(new DataColumn[2] {
col, col2
});
insertresults = sql.BulkInsert(dt, "fld", "def");
}
sql.Close();
return(insertresults);
}
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 gridSets_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
{
var keys = e.Keys;
ICollection valueCollection = e.Keys.Values;
int[] vals = new int[e.Keys.Count];
valueCollection.CopyTo(vals, 0);
int subjset_pk = Convert.ToInt32(vals[0]);
int i = gridSets.FindVisibleIndexByKeyValue(e.Keys[gridSets.KeyFieldName]);
Control c = gridSets.FindDetailRowTemplateControl(i, "gridSets");
e.Cancel = true;
SQL_utils sql = new SQL_utils("data");
int n = sql.IntScalar_from_SQLstring("select count(*) from dp.dataproject where subjset_pk = " + subjset_pk.ToString());
if (n == 0)
{
string sql1 = "delete from dp.Subj where subjset_pk = " + subjset_pk;
sql.NonQuery_from_SQLstring(sql1);
string sql2 = "delete from dp.SubjSet where subjset_pk = " + subjset_pk;
sql.NonQuery_from_SQLstring(sql2);
}
DataTable dt = GetSubjSets();
gridSets.DataSource = dt;
gridSets.DataBind();
sql.Close();
}
public string SaveToDB()
{
string result = "";
if (rptnum > 0 & dataproj_pk > 0)
{
SQL_utils sql = new SQL_utils("data");
string sqlinsert = String.Format("insert into dp.Report (dataproj_pk, rptnum, rpttitle, rptdesc, rptfilename, created, createdBy) " +
" values({0},{1},'{2}','{3}', '{4}',getdate(), sec.systemuser())"
, dataproj_pk, rptnum, rpttitle, rptdesc, RptFilename());
sql.NonQuery_from_SQLstring(sqlinsert);
int rptpk = sql.IntScalar_from_SQLstring(String.Format("select rptpk from dp.Report where dataproj_pk={0} and rptnum={1}", dataproj_pk, rptnum));
string saveorders_msg = SaveOrdersToDB(sql, rptpk);
result = String.Format("Report saved (with {0}).", saveorders_msg);
sql.Close();
}
return(result);
}
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 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();
}
}
}
//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 string CreateValueSet (List<string> vals, string valuesetname)
//{
// SQL_utils sql = new SQL_utils("data");
// string code = String.Format("insert into datFieldValueSet {0}")
//}
//public int ConvertToFieldValue()
public static int ProcessValueSet(string fldname, string valuesetname, bool createNewSetIfNeeded, List <string> valuelabels)
{
SQL_utils sql = new SQL_utils("data");
string code = String.Format("select fieldvaluesetID from def.fld where fldname = '{0}'", fldname);
int fvsID = sql.IntScalar_from_SQLstring(code, true);
//No set yet, so create one
if (fvsID == -98765)
{
bool addDummyItem = false;
fvsID = CreateValueSet(sql, valuesetname, addDummyItem, valuelabels);
}
if (fvsID > 0)
{
if (valuelabels.Count > 0)
{
for (int i = 0; i < valuelabels.Count; i++)
{
AddValueSetItem(sql, fvsID, null, valuelabels[i]); //Add the label
}
}
}
return(fvsID);
}
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 static int Get_Count_NDAR_DSE_from_DB(string shortName)
{
SQL_utils sql = new SQL_utils("data");
int n = sql.IntScalar_from_SQLstring("select coalesce(count(*),0) from NDAR_DSE where shortname = '" + shortName + "'");
return(n);
}
private int NextDocID()
{
SQL_utils sql = new SQL_utils("backend");
int id = sql.IntScalar_from_SQLstring("select max(docID) + 1 from tbldoc");
sql.Close();
return(id);
}
protected void Page_Load(object sender, EventArgs e)
{
//pivotM.FieldValueTemplate = new FieldValueTemplate();
//pivotM.CellTemplate = new CellTemplate();
string filter = "";
bool isCallback = IsCallback;
bool isPostback = IsPostBack;
if (Request.QueryString["studymeasID"] != null)
{
var smID = Request.QueryString["studymeasID"];
SQL_utils sql = new SQL_utils("backend");
int measureID = sql.IntScalar_from_SQLstring(String.Format("select measureID from tblstudymeas where studymeasID={0}", smID));
int timept = sql.IntScalar_from_SQLstring(String.Format("select timepointID from tblstudymeas where studymeasID={0}", smID));
sql.Close();
//gridLkupMeas.GridView.Selection.SelectRowByKey(measureID);
filter = String.Format("[StudyMeasID] = {0}", smID);
}
if (!IsCallback && !IsPostBack)
{
gvM.Visible = true;
SQL_utils sql = new SQL_utils("backend");
string sqlcode = String.Format("select count(*) n from uwautism_research_backend..tblstudymeas where studyID = {0} and reliabilityStudymeasID > 0", Master.Master_studyID);
int numrel = sql.IntScalar_from_SQLstring(sqlcode);
rblREL.Visible = (numrel > 0) ? true : false;
sql.Close();
// LoadEntities();
}
if (IsPostBack && Session["M_data"] != null)
{
}
sliderValue.Value = Master.Colorlevel.ToString();
txtColorLevel.Text = Master.Colorlevel.ToString();
//Still to fix
//if(filter != "") gvM.FilterExpression = filter;
}
private int GetRptnum()
{
SQL_utils sql = new SQL_utils("data");
int nextrptnum = sql.IntScalar_from_SQLstring("select coalesce(max(rptnum),0)+1 from dp.Report where dataproj_pk=" + dataproj_pk.ToString());
sql.Close();
return(nextrptnum);
}
protected void btnDuplicate_click(Object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("data");
DataEntryController dec = (DataEntryController)this.DataEntryController1;
string tblname = dec.DatabaseTable;
var smID = dec.StudyMeasID;
var pkfld = dec.PrimaryKeyField;
int pkval = dec.PrimaryKeyVal;
string id = dec.StudySubjectIDString;
int indexnum = dec.StudyMeasureIndexnum;
DataTable dtflds = sql.DataTable_from_SQLstring("select fldname from def.fld where tblpk = " + Environment.NewLine +
" (select tblpk from def.tbl where tblname='" + tblname + "') and fldname not in ('" + pkfld + "')");
int maxindexnum = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine +
" where id = '" + id + "' and studymeasID=" + smID.ToString());
int nextindexnum = maxindexnum + 1;
List <string> flds = dtflds.AsEnumerable().Select(f => f.Field <string>("fldname")).ToList();
string flds_csv = String.Join(",", flds).ToLower();
string newflds_csv = flds_csv.Replace(",studymeasid,", ",4984 as studymeasid,");
newflds_csv = newflds_csv.Replace(",indexnum,", "," + nextindexnum.ToString() + " as indexnum,");
string sqlcode = String.Format("insert into all_currchecklist({0}) " + Environment.NewLine +
" select {1} " + Environment.NewLine +
" from all_currchecklist where ccl_pk={2}"
, flds_csv, newflds_csv, pkval);
sql.NonQuery_from_SQLstring(sqlcode);
int maxindexnum2 = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine +
" where id = '" + id + "' and studymeasID=" + smID.ToString());
if (maxindexnum2 > maxindexnum)
{
lblNewrec.Text = String.Format("indexnum={0} inserted", maxindexnum2);
}
sql.Close();
}