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;
}
