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