//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();
}
public static string BuildDeleteSqlCode(string tbl, string db, string fldname, int fldvalue, string subquery, string schema)
{
SQL_utils sql = new SQL_utils(db);
string result = "";
string where_clause = "";
if (subquery != "" && fldname == "" && fldvalue == -1)
{
where_clause = String.Format(" {0} ", subquery);
}
else
{
where_clause = String.Format(" {0} = {1} ", fldname, fldvalue);
}
try
{
string sql_selectcode = String.Format("select * from {0}.{1} where {2} for xml raw, elements", schema, tbl, where_clause);
//string contents = sql.StringScalar_from_SQLstring(log_code1);
//string log_code2 = String.Format("insert into AuditDeletes(tblname,pkfld,pkval,contents,deleted,deletedby) select '{0}','{1}',{2},({3}),getdate(),cast(SESSION_CONTEXT(N'netid') as varchar)"
//, tbl, fldname, fldvalue, sql_selectcode);
//string del_code = String.Format("Delete from {0} where {1}={2}", tbl, pkfld, pkval);
string log_code2 = (fldvalue > 0) ?
String.Format("insert into AuditDeletes(schemaname,tblname,pkfld,pkval,contents,deleted,deletedby) select '{0}','{1}','{2}',{3},coalesce(({4}),'no records'),getdate(), sec.systemuser()"
, schema, tbl, fldname, fldvalue, sql_selectcode) :
String.Format("insert into AuditDeletes(schemaname,tblname,pkfld,pkval,where_clause,contents,deleted,deletedby) select '{0}','{1}',null,-1,'{2}',coalesce(({3}),'no records'),getdate(), sec.systemuser()"
, schema, tbl, where_clause, sql_selectcode);
string del_code = String.Format("Delete from {0}.{1} where {2}", schema, tbl, where_clause);
//Temp turn it off
var sql_output = sql.NonQuery_from_SQLstring_withRollback(String.Format("{0}; {1};", log_code2, del_code));
result += "/* sql_selectcode */ " + Environment.NewLine + sql_selectcode +
Environment.NewLine + "/* log_code2 */" + Environment.NewLine + log_code2 +
Environment.NewLine + "/* del_code */" + Environment.NewLine + del_code +
Environment.NewLine + "/* sql_output */" + Environment.NewLine + sql_output;
}
catch (Exception ex)
{
}
sql.Close();
////Temp turn it off
//string result = (fldvalue > 0) ? RecoverDeletedRecord(tbl, db, fldname, fldvalue) : RecoverDeletedRecord(tbl, db, where_clause);
return(result);
}
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();
}
protected void ChangeIDSubject(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>();
string sqllogchange = String.Format("insert into uwautism_research_backend..AuditChanges(pk, pkfldname, tblname, fldname, oldvalue, newvalue, updated, updatedBy) values({0},'subjID','tblSubject', 'ID','{1}','{2}',getdate(),sec.systemuser())"
, oldsubjid, oldid, id1);
string sqlnewid = String.Format("update uwautism_research_backend..tblsubject set id='{0}' where subjID={1} and ID='{2}'"
, id1, oldsubjid, oldid);
dataupdateresult.Add(sqllogchange);
dataupdateresult.Add(sqlnewid);
if (ndata > 0)
{
if (chkDataToo.Checked)
{
List <string> datachanges = UpdateDataID(Master.Master_studyID, oldid, id1);
dataupdateresult.AddRange(datachanges);
}
}
try
{
string dataupdateresults = String.Join(";", dataupdateresult);
string dataupdateresults_for_diplay = String.Join("<br/>", dataupdateresult);
string executeresults = sql.NonQuery_from_SQLstring_withRollback(dataupdateresults);
string datamsg = (ndata == 0) ? String.Format("No entered data with ID='{0}' were found.<br/>", oldid) : "";
error_label.Text = String.Format("ID updated to '{0}'.<br/><br/>/* SQL code executed: */<br/>{1}", id1, dataupdateresults_for_diplay);
}
catch (Exception ex)
{
}
}
}
else
{
error_label.Text = "New ID values must match.";
}
sql.Close();
}
}