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;
}
}
}
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 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();
}
protected void ClearUWinfo(string shortName)
{
SQL_utils sql = new SQL_utils("data");
string fromtopos = txtFromToPos.Text;
int from = 0; int to = 0;
if (fromtopos.Contains('-'))
{
string[] pos = fromtopos.Split('-');
from = Convert.ToInt16(pos[0]);
to = Convert.ToInt16(pos[1]);
}
else
{
from = Convert.ToInt16(fromtopos);
}
if (from > 0 & to > 0)
{
sql.NonQuery_from_SQLstring("update NDAR_DSE set uwfld=null, fx=null, param1=null, param2=null where shortname='" + shortName + "' " +
" and position >= " + from.ToString() + " and position <= " + to.ToString());
}
else if (from > 0)
{
sql.NonQuery_from_SQLstring("update NDAR_DSE set uwfld=null, fx=null, param1=null, param2=null where shortname='" + shortName + "' " +
" and position = " + from.ToString());
}
else
{
lblInfo.Text = "Enter valid From-To positions.";
}
}
protected void Button_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "DisplayAll")
{
DisplayIntHxCharts_for_study(Master.Master_studyID);
panelChart.Visible = true;
panelProgressBar.Visible = false;
btnRecreate.Visible = true;
}
else if (e.CommandName == "SaveAllPNG")
{
int studyID = Master.Master_studyID;
//SaveIntHxCharts_for_study(studyID);
}
else if (e.CommandName == "RescoreAll")
{
SQL_utils sql = new SQL_utils();
//For reference, when IntHx data is edited for individual cases, the
//following is run:
//
// spSCORE_All_Mind_ IntHx_vers2_STACKED ...
// spSCORE_All_Mind_ IntHx_vers2_STACKED__Process ...
// spSCORE_All_Mind_ IntHx_vers2_STACKED__Process_step0_by_wk ...
// spSCORE_ALL_MIND_ IntHx_vers2_STACKED__Process_step1_AGGREGATE/_TxStart
//Here. the last step is called directly to redo the aggregation.
// For PATH this is done also based on the TxStart periods.
string periodtype = rblPeriodType.SelectedValue;
sql.NonQuery_from_SQLstring("exec spSCORE_ALL_MIND_IntHx_vers2_STACKED__Process_step1_AGGREGATE " + Master.Master_studyIDfull.ToString() + ", 0, '', 0, 'WHOLESTUDY'");
if (Master.Master_studyIDfull == 90000)
{
sql.NonQuery_from_SQLstring("exec spSCORE_ALL_MIND_IntHx_vers2_STACKED__Process_step1_AGGREGATE_TxStart " + Master.Master_studyIDfull.ToString() + ", 0, '', 0, 'WHOLESTUDY'");
}
sql.Close();
}
else if (e.CommandName == "Stats")
{
DisplaySummaryStats();
panelChart.Visible = false;
panelStats.Visible = true;
}
}
protected void dxgrid_OnRowDeleting(object sender, ASPxDataDeletingEventArgs e)
{
bool proceed_as_normal = true;
ASPxGridView gv = (ASPxGridView)sender;
string result = "Error (uninitialized.)";
#region Conditions for specific grids
//For people, first check if there are any subjects
if (gv.ClientInstanceName == "grid_tblgroup")
{
var pk = e.Keys[0];
SQL_utils sql = new SQL_utils("backend");
int n = sql.IntScalar_from_SQLstring("select count(*) from tblSubject where groupID=" + pk.ToString());
if (n > 0)
{
string study = (n == 1) ? " study" : " studies";
string subject = (n == 1) ? " this subject" : " these subjects";
string msg = "This group is assigned to " + n.ToString() + " subjects. Please delete these first.";
result = msg;
proceed_as_normal = false;
}
else
{
try
{
sql.NonQuery_from_SQLstring("delete from tblStudyActionGroup where groupID=" + pk.ToString());
sql.NonQuery_from_SQLstring("delete from tblStudyMeasGroup where groupID=" + pk.ToString());
sql.NonQuery_from_SQLstring("delete from tblLabGroup_Staff where labgroupID in (select labgroupID from tblLabGroup where groupID=" + pk.ToString() + ")");
sql.NonQuery_from_SQLstring("delete from tblLabGroup where groupID =" + pk.ToString());
}
catch (Exception ex)
{ }
}
sql.Close();
}
#endregion
if (proceed_as_normal)
{
result = DxDbOps.BuildDeleteSqlCode(e, GridnameToTable(gv.ClientInstanceName), "backend");
}
//
((ASPxGridView)sender).JSProperties["cpIsUpdated"] = String.Format("DELETED.{0}", result);
gv.CancelEdit();
e.Cancel = true;
RefreshGrids();
}
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);
}
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);
}
}
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));
}
}
public static void SetColorLevel(int x)
{
SQL_utils sql = new SQL_utils("backend");
sql.NonQuery_from_SQLstring("exec trk.spUpdate_ColorLevel_for_User " + x.ToString());
sql.Close();
}
protected void SaveButton_Click(object sender, EventArgs e)
{
//(fv.FindControl("form_docvers") as ASPxFormLayout).ForEach(SaveItem);
//try
//{
// sql_docvers.Update();
//}
//catch (Exception ex)
//{
// var x = ex.Message;
//}
ASPxTextBox txt_doctitle = (ASPxTextBox)this.FindControlRecursive("txt_doctitle");
ASPxComboBox cbo_docstatusid = (ASPxComboBox)this.FindControlRecursive("cbo_docstatusid");
ASPxHtmlEditor htmledit_docdesc = (ASPxHtmlEditor)this.FindControlRecursive("htmledit_docdesc");
string docdesc = htmledit_docdesc.Html.ToString();
string doctitle = txt_doctitle.Value.ToString();
string docstatusid = cbo_docstatusid.Value.ToString();
//if (docdesc != "")
// {
SQL_utils sql = new SQL_utils("backend");
string code = String.Format("update tbldoc set docdesc = '{0}', doctitle='{1}', docstatusid={2} where docid=(select docid from tbldocvers where docversid={3})",
docdesc, doctitle, docstatusid, Request.QueryString["dvID"]);
sql.NonQuery_from_SQLstring(code);
sql.Close();
//}
}
protected void btnConfirmDelete_Click(object sender, EventArgs e)
{
string docversID = Request.QueryString["dvID"];
//TODO!! need to add deletion to the DocVers class
SQL_utils sql = new SQL_utils("backend");
string fileext = sql.StringScalar_from_SQLstring("Select fileext from tbldocvers where docversID=" + docversID.ToString());
string file = Server.MapPath(String.Format("~/webdocs/DocVersID_{0}{1}", docversID, fileext));
string relative_file = String.Format("~/webdocs/DocVersID_{0}{1}", docversID, fileext);
sql.NonQuery_from_SQLstring(String.Format("exec spDeleteDocVers_and_Data {0}", docversID));
bool fileexists = File.Exists(file);
//Delete the unzipped file
if (fileexists)
{
//File.Delete(String.Format("{0}{1}", path, filename_to_use));
File.Delete(file);
//processingresults.Add(String.Format("Deleting '{0}'", filename_to_use));
}
sql.Close();
Response.Redirect(Request.RawUrl);
}
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 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 btnCancelBulkAssign_OnClick(object sender, EventArgs e)
//{
//}
protected void btnREL_OnClick(object sender, EventArgs e)
{
var x = grid_tblstudymeas.GetSelectedFieldValues("studymeasID");
SQL_utils sql = new SQL_utils("backend");
for (int i = 0; i < x.Count; i++)
{
int studymeasID = Convert.ToInt32(x[i].ToString());
if (studymeasID > 0)
{
string sqlcode = "exec spStudyDesign_add_reliability_StudyMeas " + studymeasID.ToString();
try
{
sql.NonQuery_from_SQLstring(sqlcode);
}
catch (Exception ex) { }
}
}
sql.Close();
Response.Redirect("StudyDesign.aspx");
}
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 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 gridProjects_OnCustomButtonCallback(object sender, DevExpress.Web.ASPxGridViewCustomButtonCallbackEventArgs e)
{
Debug.WriteLine("**** gridProjects_OnCustomButtonCallback");
ASPxGridView grid = (ASPxGridView)sender;
string keyValue = grid.GetRowValues(e.VisibleIndex, "dataproj_pk").ToString();
//gv.JSProperties["cpKeyValue"] = keyValue;
if (e.ButtonID == "btnDelete")
{
SQL_utils sql = new SQL_utils("data");
sql.NonQuery_from_SQLstring("update dp.DataProject set IsDeleted=1, deleted=getdate(), deletedBy=sec.systemuser() where dataproj_pk = " + keyValue);
sql.Close();
DataTable dt = GetProjects();
gridProjects.DataSource = dt;
gridProjects.DataBind();
}
else if (e.ButtonID == "btnUndelete")
{
SQL_utils sql = new SQL_utils("data");
sql.NonQuery_from_SQLstring("update dp.DataProject set IsDeleted=0, deleted=null, deletedBy=null where dataproj_pk = " + keyValue);
sql.Close();
DataTable dt = GetProjects();
gridProjects.DataSource = dt;
gridProjects.DataBind();
}
}
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 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.");
}
}
protected void btnCreateNewComputer_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
string sqlcode = String.Format("INSERT into [IT].[ComputerReservations] ([Computer]) VALUES ( '" + TextBoxNewComputerName.Text + "')");
sql.NonQuery_from_SQLstring(sqlcode);
}
protected void logConfirmation(string email, string usergroup)
{
string textToShow = "Confirmation successful, thank you!";
email = email ?? "";
usergroup = usergroup ?? "";
if (email.Length < 1 || usergroup.Length < 1)
{
textToShow = "Confirmation failed, please email [email protected].";
}
SQL_utils sql = new SQL_utils("backend");
string sqlcode = String.Format("INSERT into mob.confirmations ([email], [usergroup], [datetime]) VALUES ( '{0}', '{1}', getdate())", email, usergroup);
sql.NonQuery_from_SQLstring(sqlcode);
try
{
sql.NonQuery_from_SQLstring(sqlcode);
}
catch (Exception exc)
{
textToShow = "Confirmation failed, please email [email protected]." + exc.Message;
}
//SqlCommand sqlCmd = new SqlCommand
//{
// Connection = oConn,
// CommandType = System.Data.CommandType.Text,
// CommandText = "INSERT into mob.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 UpdateData(string db, string tbl, string schema)
{
string cols = sql.GetAllColumnsInTable(db, tbl, "dbo");
string pk = sql.GetPKForTable(db, tbl, "dbo");
string insSQL = "INSERT INTO uwacdb." + schema + "." + tbl + " (" + cols + ") " +
" select " + cols + " from " + db + ".." + tbl + " where " + pk + " not in " +
" (select " + pk + " from uwacdb." + schema + "." + tbl + " )";
string delSQL = "DELETE FROM uwacdb." + schema + "." + tbl +
" where " + pk + " not in " +
" (select " + pk + " from " + db + ".dbo." + tbl + " )";
string updateCols = sql.GetUpdateSQLForAllColumnsInTable(db, tbl, "dbo", pk);
string updatePKs = sql.GetPKsOfUnequalRecords(db, tbl, schema);
string updSQL = "UPDATE uwacdb." + schema + "." + tbl + " set " +
updateCols + " from " + db + ".dbo." + tbl + " b" +
" where uwacdb." + schema + "." + tbl + "." + pk + " = b." + pk +
" AND b." + pk + " IN (" + updatePKs + ")";
sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " ON");
sql.NonQuery_from_SQLstring(insSQL);
sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " OFF");
sql.NonQuery_from_SQLstring(delSQL);
sql.NonQuery_from_SQLstring("DISABLE TRIGGER ALL ON " + schema + "." + tbl);
sql.NonQuery_from_SQLstring(updSQL);
sql.NonQuery_from_SQLstring("ENABLE TRIGGER ALL ON " + schema + "." + tbl);
// lblInfo.Text = updateCols;
}
protected void gridCPT_InsertCommand(object sender, Obout.Grid.GridRecordEventArgs e)
{
SQL_utils sql = new SQL_utils();
sql.NonQuery_from_SQLstring("insert into ac.enumCPT(CPTcode, servicetypeID, isVisit)" +
" values('" + e.Record["CPTcode"].ToString() + "'," + e.Record["ServiceTypeID"].ToString() + "," + e.Record["isVisit"].ToString() + ")");
sql.Close();
}
public void SaveToDB()
{
SQL_utils sql = new SQL_utils("data");
string sqlcode = String.Format("INSERT INTO[def].[Calculatedvar] ([fldname],[fieldlabel],[fldnames],[code],[calctype], created, createdby) VALUES('{0}','{1}','{2}','{3}','{4}', getdate(), sec.systemuser())"
, fldname, fieldlabel, fldnames, code, calctype);
sql.NonQuery_from_SQLstring(sqlcode);
}
protected void btnLinkUWtable_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils();
sql.NonQuery_from_SQLstring("update NDAR_DS set uwtable='" + ddlUWtables.SelectedValue + "' where shortname='" + Request.QueryString["shortName"] + "'");
sql.Close();
LoadUWfields(ddlUWtables.SelectedValue);
}
protected void Page_Load(object sender, EventArgs e)
{
//ApplyValidationSummarySettings();
//ApplyEditorsSettings();
bool isCallback = IsCallback;
bool isPostback = IsPostBack;
if (Request.QueryString["subjid"] != null)
{
SQL_utils sql = new SQL_utils("backend");
string theID = sql.StringScalar_from_SQLstring("select id from tblsubject where subjID=" + Request.QueryString["subjid"]);
Session["ID"] = theID;
//cboID.Value = theID;
ID = theID;
lblID.Text = theID;
int newstudyID = sql.IntScalar_from_SQLstring("select studyID from vwMasterStatus_S where subjID=" + Request.QueryString["subjid"]);
if (newstudyID != Master.Master_studyID)
{
sql.NonQuery_from_SQLstring("exec spSEC_Update_Default_StudyID_for_User " + newstudyID);
}
Master.Master_studyID = newstudyID;
sql.Close();
}
else if (Request.QueryString["ID"] != null)
{
ID = Request.QueryString["ID"];
Session["ID"] = ID;
lblID.Text = ID;
//cboID.Value = ID;
}
else
{
Session["ID"] = "";
}
if (!IsCallback && !IsPostBack)
{
//sliderValue.Value = color.GetColorLevel().ToString();
if (!String.IsNullOrEmpty(Session["ID"].ToString()))
{
GetSubjectInfo(Session["ID"].ToString());
//GetMeasureInfo(ID);
ASPxEdit.ValidateEditorsInContainer(this);
}
}
}
protected void btnNew_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils();
sql.NonQuery_from_SQLstring("insert into all_lo_tadpole (studymeasID, indexnum, id, clipname) values (1,1, '" + Request.QueryString["id"] + "', 'ENTER CLIP NAME')");
sql.Close();
gv2.DataBind();
}
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 ButtonCommand_Click(object sender, CommandEventArgs e)
{
if (e.CommandName == "DeletePerson")
{
SQL_utils sql = new SQL_utils("backend");
string personid = hidPersonID.Value.ToString();
int nsubj = sql.IntScalar_from_SQLstring("select count(*) from tblSubject where personID=" + personid);
if (nsubj == 0)
{
//sql.NonQuery_from_SQLstring("delete from tblPerson where personID = " + personid);
//LoadHouseholdInfo(Convert.ToInt32(hidHouseholdID.Value));
}
else
{
string study = (nsubj == 1) ? " study" : " studies";
string subject = (nsubj == 1) ? " this subject" : " these subjects";
string msg = "This person is assigned to " + nsubj.ToString() + study + ". Please delete" + subject + " first.";
//ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Notify", "alert('" + msg + "');", true);
//ShowAlert(msg); // only with with obout AJAXpage
//ExecOnLoad("ShowPersonMessage('" + msg + "')");
//sql.NonQuery_from_SQLstring("delete from tblPerson where personID = " + personid);
}
sql.Close();
}
else if (e.CommandName == "NewSubject")
{
int rowIndex = int.Parse(e.CommandArgument.ToString());
Hashtable dataItem = gridP.Rows[rowIndex].ToHashtable() as Hashtable;
string personID = dataItem["PersonID"].ToString();
Response.Redirect("~/Household/CreateSubject.aspx?PersonID=" + personID);
}
else if (e.CommandName == "MergeHousehold")
{
SQL_utils sql = new SQL_utils("backend");
string hhID = GetHouseholdID().ToString();
string sqlcmd = "exec hh.spHousehold_MERGE " + e.CommandArgument + ", " + hhID;
sql.NonQuery_from_SQLstring(sqlcmd);
sql.Close();
Response.Redirect("Household.aspx?hhID=" + hhID);
}
}