protected List <string> UpdateDataID(int studyID, string oldid, string newid) { List <string> result = new List <string>(); SQL_utils sql = new SQL_utils("data"); string sql_changestomake = String.Format("select * from DatData where ID='{0}' and studymeasID in (select studymeasID from uwautism_research_backend..tblstudymeas where studyID={1})", oldid, studyID); DataTable dt = sql.DataTable_from_SQLstring(sql_changestomake); string tblname = ""; string error = ""; if (dt != null) { foreach (DataRow row in dt.Rows) { int tblpk = Convert.ToInt32(row["tblpk"].ToString()); int pkval = Convert.ToInt32(row["tablePK"].ToString()); string pkfld = ""; pkfld = sql.StringScalar_from_SQLstring(String.Format("select fldname from def.fld where tblpk={0} and fieldcodeID=10", tblpk)); tblname = sql.StringScalar_from_SQLstring("select tblname from def.tbl where tblpk=" + tblpk.ToString()); if (tblname != "" && pkfld != "" && pkval > 0) { string update_datatable = String.Format(" Update {0} set ID='{1}' where ID='{2}' and {3}={4}", tblname, newid, oldid, pkfld, pkval); string update_datdata = String.Format(" Update datData set ID='{0}' where tblpk={1} and ID = '{2}' and tablePK={3}", newid, tblpk, oldid, pkval); result.Add(update_datatable); result.Add(update_datdata); Debug.WriteLine(update_datatable); Debug.WriteLine(update_datdata); try{ //sql.NonQuery_from_SQLstring(update_datatable); //sql.NonQuery_from_SQLstring(update_datdata); } catch (Exception ex) { error = String.Format("Error when updating {0}.", tblname); } } } } if (error != "") { return new List <string> { error } } ; else { return(result); } }
protected void Page_Init(object sender, EventArgs e) { if (Request.Browser.Crawler) { Response.End(); } //Sept 2017 - Now always handled in Web.config and the various publish profiles oConn.ConnectionString = ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"].ToString(); try { oConn.Open(); } catch (Exception) { } bool isAuthenticated = Request.IsAuthenticated; SQL_utils sql1 = new SQL_utils("backend"); string sql1_state = sql1.SqlConnection.State.ToString(); string oSQL_state = oSQL.SqlConnection.State.ToString(); var ident = HttpContext.Current.User.Identity; ////Here remove the constant for the specific location of the file on the websever ////so that we only deal with a relative path from the root of the site. //aspxfull = Path.GetFullPath(Request.Path).Replace(@"c:\research2", "").Replace(@"\", @"/"); //aspx = Path.GetFileName(Request.Path); GetCurrentDefaultStudyID(); PopulateStudyDDL(); SetTitles(); colorlevel = uwac.trk.color.GetColorLevel(); NReco.PivotData.License.SetLicenseKey( "PivotDataToolkit_Bin_Pack_000426121678", "Galv8kCqIiZYLGq5Qoc+ymn8Jo57iNuWgMqZ9vrpjfdSLkiqGpTC0AtpV7VHn1GyhoEorRfIEi27UOgJf28o+2KlKuOnrPlO/FxCV4+qUTTWL7LyP/HmhYZdeI4kox5pyThzUZb36ijxsfXzAMlFzpv3uDtPtu6ktwtiROwKG/c="); string theme = oSQL.StringScalar_from_SQLstring("exec spSEC_Get_Default_theme_for_User"); cboTheme.Text = theme; }
//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); }
//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 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 static string RecoverDeletedRecord(string tbl, string db, string fldname, int fldvalue) { string result = "The following record has been deleted:<br/><br/>"; SQL_utils sql = new SQL_utils(db); try { string myXml = sql.StringScalar_from_SQLstring(String.Format("select top 1 contents from auditdeletes where tblname='{0}' and {1}={2}", tbl, fldname, fldvalue)); XmlDocument xdoc = new XmlDocument(); xdoc.LoadXml(myXml); var rownodes = xdoc.ChildNodes; foreach (XmlNode child in rownodes[0].ChildNodes) { if (child.InnerText != "") { result += String.Format("{0} = {1}{2}", child.Name, child.InnerText, "<br/>"); } } result += "<br/><br/><i>This record can be recovered if needed. Contact Jeff.</i>"; return(result); } catch (Exception ex) { return(String.Format("{0} <br/>ERROR retrieving deleted data for tbl: {1} <br/>{2}: {3}", result, tbl, fldname, fldvalue)); } }
public DataTable GetNDARDatadictionaryFromDB() { DataTable dt = new DataTable(); if (measureid > 0) { SQL_utils sql = new SQL_utils("data"); string code = String.Format("select * from (select * from vwNDAR_Required_Vars_Definition union select Row_number() over( order by ord_pos) + 5 as Pos, fldname as ElementName " + Environment.NewLine + ", (case when fielddatatype in ('decimal', 'float') then 'Float' when fielddatatype like '%char%' then 'String' when fielddatatype like '%int%' then 'Integer' when fielddatatype like '%date%' then 'Date' else 'String' end) DataType" + ", fielddatatypelength as Size, 'Recommended' as Required " + Environment.NewLine + ", fieldlabel as ElementDescription " + Environment.NewLine + " , def.fnValueLabels_for_NDARdatadict(fieldvaluesetID) ValueRange " + Environment.NewLine + " , def.fnValueLabels_for_HtmlDisplay(fieldvaluesetID,'; ') Notes, '' Aliases " + Environment.NewLine + " from def.Fld a " + Environment.NewLine + " JOIN def.Tbl b ON a.tblpk = b.tblpk" + Environment.NewLine + " LEFT JOIN (select table_name, column_name from INFORMATION_SCHEMA.COLUMNS ) c ON b.tblname = c.table_name and a.fldname = c.column_name " + Environment.NewLine + " where a.tblpk = (select tblpk from def.tbl where measureID = {0}) " + Environment.NewLine + " and fldname not in ('id','studymeasID','indexnum','verified','created','updated','scored','createdby','updatedby','scoredby') " + Environment.NewLine + " and ord_pos >= 1 " + Environment.NewLine + " and ExcludeFromNDARdict is null " + Environment.NewLine + " and (a.fieldcodeID != 10 or a.fieldcodeID is null) " + Environment.NewLine + " ) x order by Pos", measureid); measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); //measfullname = sql.StringScalar_from_SQLstring("select measfullname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); dt = sql.DataTable_from_SQLstring(code); } return(dt); }
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(); } }
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 DataTable GetDatadictionaryFromDB_forStudy(int studyID) { DataTable dt = new DataTable(); if (studyID > 0) { SQL_utils sql = new SQL_utils("data"); string code = String.Format("select measname, rank() over(partition by measname order by a.ord_pos) position, fldname, fieldlabel " + Environment.NewLine + " , FieldDataType + coalesce( '('+cast( FieldDataTypeLength as varchar)+')', '') as datatype " + Environment.NewLine + " , def.fnValueLabels_for_HtmlDisplay(fieldvaluesetID,'; ') valuelabels " + Environment.NewLine + " from def.Fld a " + Environment.NewLine + " JOIN def.Tbl b ON a.tblpk = b.tblpk" + Environment.NewLine + " JOIN uwautism_research_backend..tblMeasure m ON b.measureID = m.measureID " + Environment.NewLine + " LEFT JOIN (select table_name, column_name from INFORMATION_SCHEMA.COLUMNS ) c ON b.tblname = c.table_name and a.fldname = c.column_name " + Environment.NewLine + " where a.tblpk in (select tblpk from def.tbl where measureID in (select distinct(measureID) from uwautism_research_backend..tblstudymeas where studyID={3})) " + Environment.NewLine + " and fldname not in ('id','studymeasID','indexnum','verified','created','updated','scored','createdby','updatedby','scoredby') " + Environment.NewLine + " and ord_pos >= 0 " + Environment.NewLine + " and (a.fieldcodeID != 10 or a.fieldcodeID is null) " + Environment.NewLine + " order by measname, a.ord_pos" , (int)ImportFiletype.REDCap, (int)FieldExtractionMode.useOtherFld, (int)FieldExtractionMode.matchFldname , studyID); measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); //measfullname = sql.StringScalar_from_SQLstring("select measfullname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); dt = sql.DataTable_from_SQLstring(code); } return(dt); }
public string NameOfRelatedEntity2(DbEntityType relatedEntType1, DbEntityType relatedEntType2) { string where = WhereIn(relatedEntType1); DbEntity relatedEnt1 = new DbEntity(relatedEntType1); DbEntity relatedEnt2 = new DbEntity(relatedEntType2); string x = String.Format("select {0} from {1}.{2}.{3} where {4} in (select {5} from {6}.{7}.{8} {9})" , relatedEnt2.namefld , relatedEnt2.fulldb , relatedEnt2.schema , relatedEnt2.table_name , relatedEnt1.pkfld , relatedEnt1.pkfld , relatedEnt1.fulldb , relatedEnt1.schema , relatedEnt1.table_name , where); SQL_utils sql = new SQL_utils(relatedEnt2.db); string name = sql.StringScalar_from_SQLstring(x); sql.Close(); return(name); }
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 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); }
protected void BindVallabels(int fvsID) { SQL_utils sql = new SQL_utils("data"); string code = String.Format("select * from datfieldvaluesetitem where fieldvaluesetID = {0}", fvsID); DataTable dt = sql.DataTable_from_SQLstring(code); if (dt.Rows.Count == 0) { //gridVallabels.ClientVisible = false; //btnCreateNewValueSet.ClientVisible = true; } else { //gridVallabels.ClientVisible = true; //btnCreateNewValueSet.ClientVisible = false; string name = sql.StringScalar_from_SQLstring(String.Format("select fieldvaluesetdesc from datfieldvalueset where fieldvaluesetID = {0}", fvsID)); gridVallabels.Caption = String.Format("#{0}<br/>{1}", fvsID, name); gridVallabels.DataSource = dt; gridVallabels.DataBind(); } 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 callbackPanel_Callback(object source, DevExpress.Web.CallbackEventArgsBase e) { SQL_utils sql = new SQL_utils("data"); string html = sql.StringScalar_from_SQLstring("select projSummary from dp.DataProject where dataproj_pk=" + e.Parameter.ToString()); litText.Text = html; sql.Close(); }
public static string GetEntityTablename(Entity ent) { SQL_utils sql = new SQL_utils("backend"); int id = (int)ent; string t = sql.StringScalar_from_SQLstring("select entitytbl from ac.enumEntity where entityID = " + id.ToString()); sql.Close(); return(t); }
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 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); } } }
private void Caclulate(DateTime date, int numdays, string id, int studyID, string act_timezone_property) { Days = new List <Daylight>(); targetdate = date; TimeZoneActigraph = LookupTimeZone(act_timezone_property); var zipCodes = ZipCodeSource.FromMemory().GetRepository(); SQL_utils sql = new SQL_utils("backend"); string code = String.Format("select left(zip,5) from trk.vwMasterStatus_S a where id='{0}' and studyID={1}" , id, studyID); string myDBzip = sql.StringScalar_from_SQLstring(code); sql.Close(); if (myDBzip.Contains("Error")) { Zip = "98195"; ZipDerivedFrom = "Default (address in DB is null)"; } else { Zip = myDBzip; ZipDerivedFrom = "address in DB"; } var myzipinfo = zipCodes.Get(Zip); Place = String.Format("{0}, {1}", myzipinfo.PlaceName, myzipinfo.StateAbbreviation); Lat = myzipinfo.Latitude; Lng = myzipinfo.Longitude; TimeZoneApiResultZip = LookupApiTimeZone(Lat, Lng); //TimeZoneZip = LookupTimeZone(myzipinfo.Latitude, myzipinfo.Longitude); for (int i = 0; i < numdays; i++) { DateTime tempdate = date.AddDays(i); SolarTimes solarTimes = new SolarTimes(tempdate, myzipinfo.Latitude, myzipinfo.Longitude); Daylight day = new Daylight() { Targetdate = tempdate, Sunrise = TimeZoneInfo.ConvertTimeFromUtc(solarTimes.Sunrise.ToUniversalTime(), TimeZoneActigraph), Sunset = TimeZoneInfo.ConvertTimeFromUtc(solarTimes.Sunset.ToUniversalTime(), TimeZoneActigraph) }; Days.Add(day); } }
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 GetCounts(string db, Panel panel, List <string> tbls) { string identityname = (String.IsNullOrEmpty(HttpContext.Current.User.Identity.Name)) ? "NULL" : HttpContext.Current.User.Identity.Name; LabelUser.Text = "HttpContext.Identity = " + HttpContext.Current.User.Identity.ToString() + " Name = " + identityname; try { SQL_utils sql = new SQL_utils(db); string sqluser = sql.StringScalar_from_SQLstring("select system_user"); Label connstring = new Label() { Text = db + "<br/>" }; connstring.Font.Bold = true; Label user = new Label() { Text = "SQL system_user = "******"NULL" : sqluser) + "<br/>" }; panel.Controls.Add(connstring); panel.Controls.Add(user); //List<string> tbls = new List<string>() { "tblPerson", "tblStudy", "tblSubject" }; foreach (string s in tbls) { N n = GetCount(sql, "select count(*) from " + s); Label lbl = new Label(); if (n.error == 1) { lbl.Text = n.errortext; } else { lbl.Text = s + " = " + n.count.ToString() + "<br/>"; } panel.Controls.Add(lbl); } sql.Close(); } catch (Exception ex) { LabelError1.Text = "#1 " + ex.Message + " " + ex.InnerException; } }
public static string GetLinkingTablename(Entity ent1, Entity ent2) { SQL_utils sql = new SQL_utils("backend"); int id1 = (int)ent1; int id2 = (int)ent2; string sqlcode = "select entitytbl from ac.enumEntity where entity1ID = " + id1.ToString() + " and entity2ID = " + id2.ToString(); string t = sql.StringScalar_from_SQLstring(sqlcode); sql.Close(); return(t); }
protected void LoadValueset(int fieldvaluesetID) { SQL_utils sql = new SQL_utils("data"); DataTable dt = sql.DataTable_from_SQLstring("select * from datfieldvaluesetitem where fieldvaluesetID = " + fieldvaluesetID.ToString()); string name = sql.StringScalar_from_SQLstring("select fieldvaluesetdesc from datfieldvalueset where fieldvaluesetID = " + fieldvaluesetID.ToString()); gridVallabels.DataSource = dt; gridVallabels.DataBind(); lblName.Text = name; }
public string REDCap_id_fldname_for_DB(SQL_utils sql, string formname, int studyid) { //int mID = MeasureID_from_REDCap_form(sql, formname, studyid); //string code = String.Format("select (case when fldextractionmode=6 then conststring else fldname end) as id_fld from def.fld where fldname='id' and tblpk = (select tblpk from def.tbl where measureid = {0})", mID); //string idfld = sql.StringScalar_from_SQLstring(code); //return idfld; string code = String.Format("select idfld from vwREDCap_idfld_by_token_study where form='{0}' and studyid={1} ", formname, studyid); string idfld = sql.StringScalar_from_SQLstring(code); return(idfld); }
public DataTable GetDatadictionaryFromDB() { DataTable dt = new DataTable(); if (measureid > 0) { SQL_utils sql = new SQL_utils("data"); string code = String.Format("select a.ord_pos, fldname, fieldlabel " + Environment.NewLine + " , (case " + Environment.NewLine + " when importfiletype={0} and fldextractionmode={1} then constString " + Environment.NewLine + " when importfiletype={0} and fldextractionmode={2} then fldname " + Environment.NewLine + " else '' end) fldname_in_redcap " + Environment.NewLine + " , inanalysis " + Environment.NewLine + " , varorigin " + Environment.NewLine + " from def.Fld a " + Environment.NewLine + " JOIN def.Tbl b ON a.tblpk = b.tblpk" + Environment.NewLine + " LEFT JOIN (select table_name, column_name from INFORMATION_SCHEMA.COLUMNS ) c ON b.tblname = c.table_name and a.fldname = c.column_name " + Environment.NewLine + " where a.tblpk = (select tblpk from def.tbl where measureID = {3}) " + Environment.NewLine + " and fldname not in ('id','studymeasID','indexnum','verified','created','updated','scored','createdby','updatedby','scoredby') " + Environment.NewLine + " and ord_pos >= 0 " + Environment.NewLine + " and (a.fieldcodeID != 10 or a.fieldcodeID is null) " + Environment.NewLine + " order by a.ord_pos" , (int)ImportFiletype.REDCap, (int)FieldExtractionMode.useOtherFld, (int)FieldExtractionMode.matchFldname , measureid); measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); //measfullname = sql.StringScalar_from_SQLstring("select measfullname from uwautism_research_backend..tblMeasure where measureID=" + measureid.ToString()); tblname = sql.StringScalar_from_SQLstring("select tblname from uwautism_research_data.def.tbl where measureID=" + measureid.ToString()); dt = sql.DataTable_from_SQLstring(code); } return(dt); }
public static void RescoreIntHx(SQL_utils sql, int studyID) { int studyIDfull = sql.IntScalar_from_SQLstring(String.Format("select dbo.fnstudyIDfull({0})", studyID)); string sqlcode = String.Format("exec sec.setusercontext 'jeffmun'; exec spScore_ALL_MIND_IntHx_vers2_2018_ScoreStatus {0}", studyIDfull); string status = sql.StringScalar_from_SQLstring(sqlcode); //if the raw data has been updated more recently than the aggregation then rescore if (status == "rescore") { string code1 = String.Format("exec spCREATE_ALL_MIND_IntHx_vers2_STACKED_TIMEPT_TXCAT_2018_WholeStudy {0}, 'exec'", studyIDfull); sql.NonQuery_from_SQLstring(code1); } }
protected void cboDataUploadType_SelectedIndexChanged(object sender, EventArgs e) { var filetype = cboDataUploadType.Value.ToString(); SQL_utils sql = new SQL_utils("backend"); string exts_csv = sql.StringScalar_from_SQLstring(String.Format("select allowedfileext from tbldoctype_lkup where doctypeid={0}", filetype)); sql.Close(); string[] exts = exts_csv.Split(',').ToArray(); DocumentsUploadControl.ValidationSettings.AllowedFileExtensions = exts; AllowedFileExtensionsLabel.Text = String.Format("Allowed file extensions: {0}", String.Join(",", exts)); }
public static string GetHtml() { string layout_section = "1"; string measID = "807"; SQL_utils sql = new SQL_utils("data"); string txt = sql.StringScalar_from_SQLstring("select coalesce(sectionheadertext,'') sectionheadertext from def.Tbl_Section where tblpk= " + " (select tblpk from def.Tbl where measureID=" + measID + ") and layout_section=" + layout_section); sql.Close(); return(txt); }
protected void LoadHouseholdInfo(int householdID) { SQL_utils sql = new SQL_utils("backend"); DataSet ds = sql.DataSet_from_ProcName("hh.spHousehold", sql.CreateParam("householdID", householdID.ToString(), "int")); hidHouseholdID.Value = householdID.ToString(); string hhname = sql.StringScalar_from_SQLstring("select hhname from tblHousehold where householdID = " + householdID.ToString()); lblHHname.Text = hhname; sql.Close(); panelMAIN.Visible = true; }