protected DataSet GetNormsTables()
{
SQL_utils sql = new SQL_utils();
DataSet ds = new DataSet();
// GetSelectedIndices
foreach (int i in lstTables.GetSelectedIndices())
{
string tbl = lstTables.Items[i].Value.ToString();
DataTable dt = sql.DataTable_from_SQLstring("select * from " + tbl);
dt.TableName = tbl;
ds.Tables.Add(dt);
}
sql.Close();
return(ds);
}
protected DataTable GetSubjSets()
{
string sqlcode = "select ab.*, coalesce(n_dataproj, 0) n_dataproj from " +
"( select a.subjset_pk, a.studyID, a.subjset, a.subjsetdetails, count(*) as nsubj, a.created, a.createdBy " +
" from dp.SubjSet a " +
" join dp.Subj b ON a.subjset_pk = b.subjset_pk " +
" where studyID = " + Master.Master_studyID.ToString() +
" group by a.subjset_pk, a.studyID, a.subjset, a.subjsetdetails, a.created, a.createdBy) ab " +
" left join (select subjset_pk, count(*) n_dataproj from dp.DataProject group by subjset_pk) c ON ab.subjset_pk = c.subjset_pk";
SQL_utils sql = new SQL_utils("data");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
Session["subjsets"] = dt;
sql.Close();
return(dt);
}
//We never need to get all the subjects, only those in the selected set
public static DataTable GetSelectedSubjects(int pk)
{
Debug.Print(" ***** GetSelectedSubjects " + System.DateTime.Now.ToString());
string sqlcode = "select subjID, id, groupname as [Group], coalesce(txgrp, '**NA**') txgrp, subjstatus, subjstatusdetail, sex, " +
" coalesce(ethnicitydesc, 'UNKNOWN') as race, coalesce(hispanicdesc, 'UNKNOWN') as ethnicity " +
", subjNotes, convert(VARCHAR, txstart, 101) txstart, txstartnote " +
"from uwautism_research_backend..vwMasterStatus_S_txgrp a " +
" where subjID in (select subjID from dp.Subj where subjset_pk = " +
" (select subjset_pk from dp.DataProject where dataproj_pk = " + pk.ToString() + ")) ";
SQL_utils sql = new SQL_utils("data");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
//Session["proj_subj"] = dt;
sql.Close();
return(dt);
}
protected void LoadUWfields(string uwtable)
{
SQL_utils sql = new SQL_utils("data");
string sql_code = "exec def.spUWflds '" + uwtable + "'";
DataTable dt = sql.DataTable_from_SQLstring(sql_code);
Session["ndar_UWflds"] = dt;
lblUWtable.Text = uwtable;
gridUWtable.DataSource = dt;
gridUWtable.DataBind();
Panel_UWtable.Visible = true;
sql.Close();
}
protected void updateData()
{
SQL_utils sql = new SQL_utils("FinMart");
DataTable dt =
sql.DataTable_from_SQLstring("select * from fin.tbl_EDWData where BudgetNbr = "
+ BudgetDDL.SelectedValue.ToString()
+ " and calyr = " + YearDDL.Text
//+ " and calyr > 2014 "
+ ";");
sql.Close();
ShowYear(GetYear(dt, new List <string> {
"calyr", "calmo"
}, new List <string> {
"BudgetNbr", "Category"
}, chkCount.Checked));
}
public void LoadSA()
{
string sqlcode = String.Format("select 'studyaction' objtype, studyactionID objpk " +
", studyactionID, studyID, actiontypeID, labID, assignonentry, daysuntildue, actiontext, StaffPosID, sortorder, allowduplicate, numtoaddStart, numtoaddEnd " +
", dateunitsID, datebaseID, timepointID, notes, baseDateStudyActionID, actiontype, studyname, studyfullname, labname, staffshortname " +
", timepoint, timepoint_text " +
", uwautism_research_backend.dbo.fnCSV_GetLinkedIDs('studyaction', 'group', studyactionID, ',') groupIDs " +
", uwautism_research_backend.dbo.fnCSV_GetLinkedIDs_text('studyaction', 'group', studyactionID, ',') groupabbrs " +
"from uwautism_research_backend..vwstudyaction where studyID = {0} order by timepoint, sortorder ", Master.Master_studyID.ToString());
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
sql.Close();
Session["studydesign_studyaction"] = dt;
grid_tblstudyaction.DataSource = dt;
grid_tblstudyaction.DataBind();
}
protected void LoadFields(string str_measureID)
{
SQL_utils sql = new SQL_utils();
string orderby_clause = "";
switch (rblSort.SelectedValue.ToString())
{
case "SectionRowCol":
orderby_clause = "order by layout_section, layout_row, layout_col, ord_pos";
break;
case "SectionColRow":
orderby_clause = "order by layout_section, layout_col, layout_row, ord_pos";
break;
case "Position":
orderby_clause = "order by ord_pos, databasefield";
break;
case "FieldName":
orderby_clause = "order by databasefield ";
break;
}
sql.NonQuery_from_SQLstring("exec spDEF_Delete_Unneeded_Layout_Sections_Columns " + ddlMeas.SelectedValue.ToString());
//TOFIX
//Fields
DataTable dt_fields = sql.DataTable_from_SQLstring("select pk, ord_pos, databasefield, layout_section, layout_row, layout_col " +
" , width_label, width_box, fieldlabel, aspxfieldlabeltext, isReadOnly, DoubleEntryRequired , fieldvaluesetID " +
" from datfieldinfo where databasetable = " +
"(select name from datTable where tableID = (select tableID from datTable_measure where measureID = " + str_measureID + ")) " +
orderby_clause);
Grid1.DataSource = dt_fields;
Grid1.DataBind();
Grid1.Visible = true;
sql.Close();
}
protected void LoadDataStructures(string source)
{
string where = txtWhere.Text;
DataTable dt = new System.Data.DataTable();
SQL_utils sql = new SQL_utils("data");
if (chkInStudy.Checked)
{
//UWAutism uwac = new UWAutism();
//int studyID = uwac.Master_studyID;
int studyID = sql.GetUserStudyID();
dt = NDAR.LoadFullDataStructureList(source, where, chkUWview.Checked, studyID);
}
else
{
dt = NDAR.LoadFullDataStructureList(source, where, chkUWview.Checked);
}
string code_no_ndar = String.Format("select * from uwautism_research_backend..tblMeasure where " +
" measureID in (select measureID from uwautism_research_backend..tblStudyMeas where studyID={0}) and measureID not in " +
" ( select measureID from def.Tbl a " +
" join NDAR_DS b ON a.tblname = b.uwtable where measureID>0) "
, Master.Master_studyID);
DataTable dt_no_ndar = sql.DataTable_from_SQLstring(code_no_ndar);
sql.Close();
gridNoNDAR.DataSource = dt_no_ndar;
gridNoNDAR.DataBind();
//gvScroll.DataSource = dt;
//gvScroll.DataBind();
Session["datastructures"] = dt;
grid.DataSource = dt;
grid.DataBind();
lblN.Text = "No. of Data Structures: " + dt.Rows.Count.ToString();
}
protected void CreateViewSyntax(int tblpk)
{
SQL_utils sql = new SQL_utils("data");
string tblname = sql.StringScalar_from_SQLstring("select tblname from def.tbl where tblpk=" + tblpk.ToString());
int studymeasID = sql.IntScalar_from_SQLstring("select studymeasID from uwautism_research_backend..tblstudymeas where studyID=1110 and " +
"measureID = (select measureID from def.tbl where tblpk=" + tblpk.ToString() + ")");
DataTable dt_surveyflds = sql.DataTable_from_SQLstring("select q_id, b.tblpk, b.fldpk, fldname, stem, fieldlabel from avh.questions_def_flds a join def.fld b ON a.tblpk=b.tblpk and a.fldpk=b.fldpk where a.tblpk=" + tblpk.ToString());
DataTable dt = sql.DataTable_from_SQLstring("select * from " + tblname);
List <string> flds = new List <string>();
//Loop through rows of the surveyflds
foreach (DataRow row in dt_surveyflds.Rows)
{
string fldname = row["fldname"].ToString();
string surveyname = row["q_id"].ToString().Replace("-", "_");
string fld = String.Format(", {0} as {1}", surveyname, fldname);
flds.Add(fld);
}
List <string> syntax = new List <string>();
syntax.Add(String.Format("create view avh.vw{0} as ", tblpk));
syntax.Add(String.Format("select {0} studymeasID, 1 indexnum, account_id id ", studymeasID));
syntax.AddRange(flds);
syntax.Add(", getdate() created, sec.systemuser() createdBy");
syntax.Add("from avh.survey");
syntax.Add("GO");
syntax.Add(" ");
//sql.NonQuery_from_SQLstring(String.Join(Environment.NewLine, syntax));
sql.Close();
foreach (string s in syntax)
{
Debug.WriteLine(s);
}
}
protected void btnCreateNewValueSet_Click(object sender, EventArgs e)
{
string valuesetname = txtNewValueSet.Text;
int x = 0;
//Create new ValueSet and populate with 1 dummy value;
bool addDummyItem = true;
//string valuelabel = "New Value Labels for " + lblMeasName.Text;
SQL_utils sql = new SQL_utils("data");
int newfvsID = DxDbOps.CreateValueSet(sql, valuesetname, addDummyItem, new List <string>());
var fldpk = hidField["fldpk"];
string code = String.Format("update def.fld set fieldvaluesetID={0} where fldpk={1}", newfvsID, fldpk);
sql.NonQuery_from_SQLstring(code);
sql.Close();
}
protected Literal PlotStackedCol_FTE_byBudgetNbr(string budgetNbr)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt_series2 = sql.DataTable_from_SQLstring("select Name from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' group by Name");
DataTable dtraw2 = sql.DataTable_from_SQLstring("select * from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' and calcFTE>0 order by Name");
Literal lit2 = new Literal();
string x2 = utilCharts.Highcharts_StackedColumn(dt_series2, dtraw2, "Mo", "calcFTE", "Name", "Name", "chart1" + budgetNbr.Replace(" ", ""), budgetNbr,
"Month", "FTE", -999, -999, -999, -999, 700, 400);
lit2.Text += x2;
//panel2.Controls.Add(lit2);
sql.Close();
return(lit2);
}
protected void LoadHouseholdInfo(int householdID)
{
gridP.ClearPreviousDataSource();
gridS.ClearPreviousDataSource();
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();
//Populate the Grids
LoadGrid(gridP, ds.Tables[0]);
LoadGrid(gridPhone, ds.Tables[1]);
LoadGrid(gridEmail, ds.Tables[2]);
LoadGrid(gridAddress, ds.Tables[3]);
LoadGrid(gridLogContact, ds.Tables[4]);
LoadGrid(gridLogInfo, ds.Tables[5]);
LoadGrid(gridEmerContact, ds.Tables[6]);
//DataView vwdups = ds.Tables[0].AsDataView(); vwdups.RowFilter = "ndups
//> 0";
//DataTable dtdups = vwdups.ToTable();
//if(dtdups.Rows.Count > 0)
//{
// LoadGrid(gridDups, dtdups);
//}
string hhname = sql.StringScalar_from_SQLstring("select hhname from tblHousehold where householdID = " + householdID.ToString());
lblHHname.Text = hhname;
sql.Close();
//sql_Persons_by_Household.SelectParameters["householdID"].DefaultValue = householdID.ToString();
//gridP.DataBind();
panelMAIN.Visible = true;
}
protected void showCPTBreakdown()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select a.*, CPTgroup from vtj.vwValantTJ a join vtj.CPTgroups b ON a.CPTcode = b.cpt" +
" where a.Yr=" + YearDDL.Text + "");
sql.Close();
GetCPT(dt, new List <string> {
"PaymentOther", "PatientType"
}, new List <string> {
"YrMo"
}, chkCount.Checked);
GetCPT(dt, new List <string> {
"providerID"
}, new List <string> {
"YrMo"
}, chkCount.Checked);
}
protected void btnDeleteDatafile_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "DeleteDatafile")
{
ASPxSpreadsheet.Visible = false;
SQL_utils sql = new SQL_utils();
string filename = e.CommandArgument.ToString();
sql.NonQuery_from_SQLstring("update dp.Datafile set IsDeleted=1, deleted=getdate(), deletedBy=system_user where filename = '" + filename + "'");
int dataproj_pk = int.Parse(Request.QueryString["pk"]);
LoadDatafiles(dataproj_pk);
sql.Close();
}
}
protected void ddlID2_SelectedIndexChanged(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("data");
string ID = ddlID2.SelectedValue.ToString();
DataTable dt = sql.DataTable_from_SQLstring("Select a.studymeasID, studymeasname from all_vineii a " +
" join uwautism_research_backend..tblstudymeas b ON a.studymeasID = b.studymeasID " +
" where ID = '" + ID + "' and studyID in (1042,1071)");
ddlStudymeasID.DataTextField = "studymeasname";
ddlStudymeasID.DataValueField = "studymeasID";
ddlStudymeasID.DataSource = dt;
ddlStudymeasID.DataBind();
sql.Close();
btnExport.Visible = true;
}
//protected void ddlSubjStatus2_SelectedIndexChanged(object sender, EventArgs e)
//{
// //UpdateSSD();
//}
// protected void UpdateSSD()
//{
// //string x = ddlSubjStatus2.SelectedValue.ToString();
// //SQL_utils sql = new SQL_utils("backend");
// //DataTable dt = sql.DataTable_from_SQLstring("select 0 subjstatusdetailID, '--select status detail--' subjstatusdetail union " +
// // " select subjstatusdetailID, subjstatusdetail from tblSubjStatusDetail_Lkup " +
// // " where subjstatusdetailID in " +
// // " (select subjstatusdetailID from tblSubjStatusAndDetail where subjstatusID = " + x + ")");
// //ddlSubjStatusDetail2.DataSource = dt;
// //ddlSubjStatusDetail2.DataTextField = "subjstatusdetail";
// //ddlSubjStatusDetail2.DataValueField = "subjstatusdetailID";
// //ddlSubjStatusDetail2.DataBind();
// //panelSubjectInfo.Update();
// //sql.Close();
//}
// protected void ddlSubjStatus2_TextChanged(object sender, EventArgs e)
// {
// UpdateSSD();
// }
protected void btnUpdateSS_Click(object sender, EventArgs e)
{
UpdatePanel("panelSubjectInfo");
SQL_utils sql = new SQL_utils("backend");
HiddenField hidSSD = (HiddenField)Page.FindControlRecursive("hidSSD");
string ssd = hidSSD.Value;
if (ssd != "" | txtNotes.Text != "")
//if (ddlSSD.SelectedValue.ToString() != "")
{
if (ssd == "")
{
ssd = "-1"; //if ssd is blank then set to -1 so that the orig value is retained in the stored proc
}
//note that it is blank because the SSD dropdown is not populated until the SS is selected
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("subjID", Request.QueryString["subjID"], "int"));
ps.Add(sql.CreateParam("subjstatusID", ddlSS.SelectedValue.ToString(), "int"));
ps.Add(sql.CreateParam("subjstatusdetailID", ssd, "int"));
ps.Add(sql.CreateParam("notes", txtNotes.Text, "text"));
sql.NonQuery_from_ProcName("hh.spSubject_MODIFY", ps);
//panelEditSS.Visible = false;
//btnUpdateSS.Visible = false;
//btnEditSS.Visible = true;
//reset selected index to -1
ddlSS.SelectedIndex = -1;
ddlSSD.SelectedIndex = -1;
Response.Redirect("subject.aspx?subjID=" + Request.QueryString["subjID"]);
}
//LoadSubjectInfo(Request.QueryString["subjID"]);
sql.Close();
}
protected DataTable GetMasterDataTable(string schema, string master_tbl, string master_pk, bool usestudy)
{
SQL_utils sql = new SQL_utils("backend");
string cols = sql.GetAllColumnsInTable("uwautism_research_backend", master_tbl, schema, true);
cols = cols.Replace(master_pk + ",", "");
string where_clause = " where 1=1 ";
string study_clause = "";
if (usestudy)
{
study_clause = " and studyID = " + Master.Master_studyID.ToString();
}
string sqlcode = "select " + master_pk + " as master_pk, " + cols + " from " + schema + "." + master_tbl + where_clause + study_clause;
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
sql.Close();
return(dt);
}
protected void LoadStudymeas()
{
SQL_utils sql = new SQL_utils("backend");
string sqltext = "select studymeasID, studymeasname from uwautism_research_backend..tblStudymeas where studyID= " + Master.Master_studyID.ToString() +
" and measureID = (select measureID from uwautism_research_data.def.Tbl where tblname = " +
" (select uwtable from uwautism_research_data..NDAR_ds where shortname='"+ shortName + "')) order by studymeasname";
DataTable dt = sql.DataTable_from_SQLstring(sqltext);
if (dt.Rows.Count >= 1)
{
UpdatePanel_O_CheckBoxes p = new UpdatePanel_O_CheckBoxes(dt, "studymeasID", "Measure", "studymeasname", "studymeasID", "int", "0");
p.ID = "studymeasID";
panelStudymeas.Controls.Add(p);
}
sql.Close();
}
protected void load_DDLStudymeasID()
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select studymeasID, studymeasname +' ('+ cast(studymeasID as varchar)+')' as studymeasname from tblStudymeas where studyID=1071 and measureID=427");
DDLstudymeasID.DataSource = dt;
DDLstudymeasID.DataBind();
DataTable dt2 = sql.DataTable_from_SQLstring("select distinct(ID) ID from uwautism_research_data..ALL_VineII where studymeasID in " +
" (select studymeasID from tblStudymeas where studyID=1071 and measureID=427) order by 1");
DDL_ID.DataSource = dt2;
DDL_ID.DataBind();
sql.Close();
}
protected void LoadListBox_Group(string grps)
{
string[] grp = grps.Split(',');
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select groupID, '(' + studyname + ') ' + groupname as groupname from tblgroup a join tblStudy b ON a.studyID = b.studyID " +
" where a.studyID in (" + grps + ") ");
//lstGroup.DataValueField = "groupID";
//lstGroup.DataTextField = "groupname";
//lstGroup.DataSource = dt;
//lstGroup.DataBind();
sql.Close();
}
public Valueset(int myvaluesetid)
{
valitems = new List <Valuesetitem>();
fieldvaluesetid = myvaluesetid;
SQL_utils sql = new SQL_utils("data");
DataTable dt = sql.DataTable_from_SQLstring(String.Format("select * from datFieldValueSetItem where fieldvaluesetid = {0}", fieldvaluesetid));
foreach (DataRow row in dt.Rows)
{
int val = Convert.ToInt32(row["FieldValue"]);
string label = row["FieldValueLabel"].ToString();
Valuesetitem itm = new Valuesetitem(val, label);
valitems.Add(itm);
}
sql.Close();
}
protected void btnLoad_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select a.*, CPTgroup from vtj.vwValantTJ a join vtj.CPTgroups b ON a.CPTcode = b.cpt" +
" where a.Yr=" + txtYr.Text + "");
sql.Close();
GetCPT(dt, new List <string> {
"PaymentOther", "PatientType"
}, new List <string> {
"YrMo"
}, chkCount.Checked);
GetCPT(dt, new List <string> {
"providerID"
}, new List <string> {
"YrMo"
}, chkCount.Checked);
}
protected void ProcessSubmit(string description, List <UploadedFileInfo> fileInfos)
{
DescriptionLabel.Value = Server.HtmlEncode(description);
SQL_utils sql = new SQL_utils("backend");
foreach (UploadedFileInfo fileInfo in fileInfos)
{
// process uploaded files here
DateTime?filename_date = GetFirstDateFromString(fileInfo.OriginalFileName);
//Check to see if the filename has already been uploaded
int exists = sql.IntScalar_from_SQLstring(
String.Format("select coalesce(count(*),0) result from vwDocVers where doctype != 'SleepSensorBox' and origfilename = '{0}'", fileInfo.OriginalFileName));
int doctypeID = Convert.ToInt32(cboDataUploadType.Value);
if (exists == 0)
{
UploadSettings uploadSettings;
//int max_datauploadpk = sql.IntScalar_from_SQLstring("select coalesce(max(datauploadpk),0) from def.DataUpload");
//Save to DB
uploadSettings = LogTheUpload(fileInfo, fileInfo.OriginalFileName, doctypeID);
fileInfo.SummaryInfo = uploadSettings.results;
}
else
{
fileInfo.SummaryInfo = "NOT PROCESSED: The file already exists.";
}
}
sql.Close();
gvResults.DataSource = fileInfos;
gvResults.DataBind();
SubmittedFilesListBox.DataSource = fileInfos;
SubmittedFilesListBox.DataBind();
FormLayout.FindItemOrGroupByName("ResultGroup").Visible = true;
}
public string loadMeasstatusdetail(string measstatusID, string measstatusdetailID)
{
SQL_utils sql = new SQL_utils("backend");
DataTable dt = sql.DataTable_from_SQLstring("select '--select detail--,-1' as ms_msd union select measstatusdetail +','+ cast(measstatusdetailID as varchar) ms_msd from tblMeasstatusdetail_lkup" +
" where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")");
DataTable dt2 = sql.DataTable_from_SQLstring("select '--select detail--' measstatusdetail,'-1' as measstatusdetailID union select measstatusdetail , cast(measstatusdetailID as varchar) measstatusdetailID from tblMeasstatusdetail_lkup" +
" where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")");
string msd = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("ms_msd")).ToArray());
if (measstatusdetailID == null | measstatusdetailID == "")
{
measstatusdetailID = "-1";
}
//Control ctrl = gridM.Templates[0].Container.FindControl("ddl_measstatusdetailID");
//string ctlID = ctrl.ID;
//string ctl_clientID = ctrl.ClientID;
//OboutDropDownList ddl = (OboutDropDownList )gridM.Templates[0].Container.FindControl("ddlMeasstatusdetailID2");
//ddl.DataSource = dt2;
//ddl.DataBind();
//UpdatePanel up = (UpdatePanel)gridM.Templates[0].Container.FindControl("updatepanel_M");
//up.Update();
//msd += "|" + measstatusdetailID + "|" +
//ddlMeasstatusdetailID_clientID; //although
//ddlMeasstatusdetailID_clientID was correct didn't work
//msd += "|" + measstatusdetailID + "|" + ctlID ; //try to just lookup the control name without the full clientID
sql.Close();
return(msd);
}
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 == "dxgridPeople")
{
var personID = e.Keys[0];
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.ToString());
sql.Close();
if (nsubj > 0)
{
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.";
result = msg;
proceed_as_normal = false;
}
}
#endregion
if (proceed_as_normal)
{
result = DxDbOps.BuildDeleteSqlCode(e, GridnameToTable(gv.ClientInstanceName), "backend");
}
//
((ASPxGridView)sender).JSProperties["cpIsUpdated"] = result;
gv.CancelEdit();
e.Cancel = true;
}
protected void submit_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils();
string layout_section = hid_layout_section.Value;
string layout_dim = hid_layout_dim.Value;
string dim_index = hid_dim_index.Value;
string sqlcode = "";
string ctlname = "";
if (layout_dim == "section")
{
sqlcode = "update def.Tbl_Section set sectionheadertext = '" + Editor.Content + "' where tblpk= (select tblpk from def.Tbl where measureID = " +
ddlMeas.SelectedValue.ToString() + ") and layout_section=" + layout_section;
ctlname = "section" + layout_section;
}
else if (layout_dim == "row")
{
sqlcode = "update def.Tbl_SectionRow set rowheadertext = '" + Editor.Content + "' where tblpk= (select tblpk from def.Tbl where measureID = " +
ddlMeas.SelectedValue.ToString() + ") and layout_section=" + layout_section + " and layout_row=" + dim_index;
ctlname = "section" + layout_section + "_" + dim_index;
}
else if (layout_dim == "col")
{
sqlcode = "update def.Tbl_SectionCol set colheadertext = '" + Editor.Content + "' where tblpk= (select tblpk from def.Tbl where measureID = " +
ddlMeas.SelectedValue.ToString() + ") and layout_section=" + layout_section + " and layout_col=" + dim_index;
ctlname = "section" + layout_section + "_" + dim_index;
}
sql.NonQuery_from_SQLstring(sqlcode);
sql.Close();
ModalPopupExtender.Hide();
LoadLayout(ddlMeas.SelectedValue);
panelDEMainBody.Update();
}
//protected void LoadStudyAppr()
//{
// SQL_utils sql = new SQL_utils();
// DataTable dt = new DataTable();
// dt = sql.DataTable_from_SQLstring("select studyappr_pk, a.staffID, allowProxy, firstname + ' ' + lastname staffname, studyname " +
// " from dp.StudyApprover a join uwautism_research_backend..tblstaff b ON a.staffID = b.staffID" +
// " join uwautism_research_backend..tblstudy c ON a.studyID = c.studyID order by studyname, firstname, lastname " );
// gridStudyAppr.DataSource = dt;
// gridStudyAppr.DataBind();
// sql.Close();
//}
//protected void LoadPotentAppr()
//{
// SQL_utils sql = new SQL_utils();
// DataTable dt = new DataTable();
// dt = sql.DataTable_from_SQLstring("select a.staffID, firstname + ' ' + lastname staffname " +
// " from dp.PotentialApprover a join uwautism_research_backend..tblstaff b ON a.staffID = b.staffID" );
// gridPotentAppr.DataSource = dt;
// gridPotentAppr.DataBind();
// sql.Close();
//}
//protected void gridStudyAppr_InsertCommand(object sender, Obout.Grid.GridRecordEventArgs e)
//{
// oboutGrid_utils o = new oboutGrid_utils();
// string x = o.oGrid_InsertData(e.Record, "StudyApprover", "data", "dp", "studyappr_pk", 0);
//}
//protected void gridPotentAppr_InsertCommand(object sender, Obout.Grid.GridRecordEventArgs e)
//{
// oboutGrid_utils o = new oboutGrid_utils();
// string x = o.oGrid_InsertData(e.Record, "PotentialApprover", "data", "dp", "staffID", 0);
//}
//protected void gridStudyAppr_DeleteCommand(object sender, Obout.Grid.GridRecordEventArgs e)
//{
// int pkval = Convert.ToInt16(e.Record["studyappr_pk"]);
// oboutGrid_utils o = new oboutGrid_utils();
// string x = o.oGrid_DeleteData("StudyApprover", "data", "dp", "studyappr_pk", pkval);
//}
//protected void gridPotentAppr_DeleteCommand(object sender, Obout.Grid.GridRecordEventArgs e)
//{
// int pkval = Convert.ToInt16(e.Record["staffID"]);
// oboutGrid_utils o = new oboutGrid_utils();
// string x = o.oGrid_DeleteData("PotentialApprover", "data", "dp", "staffID", pkval);
//}
//#region Rebind
//protected void gridStudyAppr_Rebind(object sender, EventArgs e)
//{
// LoadStudyAppr();
//}
//protected void gridPotentAppr_Rebind(object sender, EventArgs e)
//{
// LoadPotentAppr();
//}
//#endregion
protected void GetApproverList_for_all_studies()
{
SQL_utils sql = new SQL_utils("data");
string[] keep = new string[] { "FullStudyName", "StudyName", "studyID" };
DataTable dtSTATS = sql.DataTable_from_SQLstring("select * from dp.vwDBstatistics");
gvSTATS.DataSource = dtSTATS;
gvSTATS.DataBind();
DataTable dtY0 = sql.DataTable_from_SQLstring("exec dp.spStudyApprover 'yes', 0");
DataTable dtY0_pivot = utilsPivot.Pivot(dtY0, keep, "ApproverNum", "ApproverName", "FullStudyName, ApproverName", "ApproverNum");
gvY0.DataSource = dtY0_pivot;
gvY0.DataBind();
DataTable dtN0 = sql.DataTable_from_SQLstring("exec dp.spStudyApprover 'no', 0");
DataTable dtN0_pivot = utilsPivot.Pivot(dtN0, keep, "ApproverNum", "ApproverName", "FullStudyName, ApproverName", "ApproverNum");
gvN0.DataSource = dtN0_pivot;
gvN0.DataBind();
DataTable dtY1 = sql.DataTable_from_SQLstring("exec dp.spStudyApprover 'yes', 1");
DataTable dtY1_pivot = utilsPivot.Pivot(dtY1, keep, "ApproverNum", "ApproverName", "FullStudyName, ApproverName", "ApproverNum");
gvY1.DataSource = dtY1_pivot;
gvY1.DataBind();
DataTable dtN1 = sql.DataTable_from_SQLstring("exec dp.spStudyApprover 'no', 1");
DataTable dtN1_pivot = utilsPivot.Pivot(dtN1, keep, "ApproverNum", "ApproverName", "FullStudyName, ApproverName", "ApproverNum");
gvN1.DataSource = dtN1_pivot;
gvN1.DataBind();
sql.Close();
}
protected DataTable GetTrackingData(string mode)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
//Get Info
DataSet ds = LoadSelectedItems_into_DataSet();
foreach (DataTable dt in ds.Tables)
{
ps.Add(sql.CreateParam(dt.TableName, dt));
}
DataTable dtX = sql.DataTable_from_ProcName("spTracking_GetInfo_" + mode, ps);
sql.Close();
return(dtX);
}
protected void LoadData()
{
SQL_utils sql = new SQL_utils("backend");
string sqlcode = "select a.staffID, lastname, firstname, actdirID NETID, numHits, unqHits, hitTime, dbo.fnElapTime_text(hittime, getdate()) as lastHit " +
" , datediff(minute, hittime, getdate()) mins_since_lastHit " +
"from uwautism_research_backend..tblStaff a " +
"join tblSessionCounter b ON a.staffID = b.staffID order by hitTime desc";
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
grid.DataSource = dt;
grid.DataBind();
sql.Close();
}
protected void btnClone_OnClick(object sender, EventArgs e)
{
Debug.Print("btnClone_OnClick " + System.DateTime.Now.ToString());
SQL_utils sql = new SQL_utils("data");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("dataproj_pk", Request.QueryString["pk"], "int"));
ps.Add(sql.CreateParam("newdataproj_pk", "0", "int", "output"));
int newdataproj_pk = sql.NonQuery_from_ProcName("dp.CloneDataProject", ps, "newdataproj_pk");
sql.Close();
if (newdataproj_pk > 0)
{
Response.Redirect("Edit.aspx?pk=" + newdataproj_pk.ToString());
}
}
