protected void btnSaveCSV_Click(object sender, EventArgs e) { string shortName = Request.QueryString["shortName"]; NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortName); DataTable dt = NDAR.NDARDataStructureElements_to_DataTable(ds); dt.TableName = "csv"; SpreadsheetGearUtils.SaveDataTableToFile(dt, "testit", "csv"); }
protected void GUID_fileinfo(string stage) { SQL_utils sql = new SQL_utils(); string proc = "NDAR..spNDAR_WEB_GUID_to_create_MULTIPLE_by_study"; string mode = "_new_guid"; if (rblMode.SelectedValue == "convert") { proc = "NDAR..spNDAR_WEB_GUID_to_convert_INVGUID_MULTIPLE_by_study"; mode = "_convert_INVGUID"; } string consent = "skip_consent"; if (chkConsent.Checked) { consent = "use_consent"; } List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); ps.Add(sql.CreateParam("useconsent", consent, "text")); DataSet ds = sql.DataSet_from_ProcName(proc, ps); if (stage == "displayinfo") { GridView gv = new GridView(); gv.BorderStyle = BorderStyle.None; gv.BorderColor = Color.White; gv.DataSource = ds.Tables[0]; gv.DataBind(); panelGuidFileInfo.ContentTemplateContainer.Controls.Add(gv); GridView gv2 = new GridView(); gv2.BorderStyle = BorderStyle.None; gv2.BorderColor = Color.White; gv2.DataSource = ds.Tables[1]; gv2.DataBind(); panelGuidFileInfo.ContentTemplateContainer.Controls.Add(gv2); panelGuidFileInfo.Update(); } else if (stage == "savefile") { string datetime = SpreadsheetGearUtils.DateTime_for_filename(); SpreadsheetGearUtils.SaveDataTableToFile(ds.Tables[0], Master.Master_studyname + mode + datetime, "csv"); } sql.Close(); }
protected void btnSaveNDARCSV_Click(object sender, EventArgs e) { UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelStudymeas.FindControlRecursive("studymeasID"); string studymeasIDs = p.GetChecked_as_CSV(); string shortName = Request.QueryString["shortName"]; int studyID = sql.GetUserStudyID(); string studyname = sql.GetUserStudyName(); string IDexclude = txtIDexclude.Text; DataTable dt = NDAR.GetNDAR_view(shortName, studyID, 0, false, IDexclude, studymeasIDs); dt.TableName = shortName; string now = DateTime.Now.ToString("yyyyMMdd__HH_mm"); string filename = studyname + "_" + shortName + "_" + now; SpreadsheetGearUtils.SaveDataTableToFile(dt, filename, "csv", true); }
protected void Export_IBIS_Vineland() { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); string ID = ddlID2.SelectedValue.ToString(); string studymeasID = ddlStudymeasID.SelectedValue.ToString(); string dccID_1 = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_ID('" + ID + "')"); string subjecttype = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_subjecttype('" + ID + "')"); int tp = -1; if (studymeasID == "2671") { tp = 3; // 2671 IBIS: 03_Vineland-II (surv intvw) } if (studymeasID == "1811") { tp = 6; // 1811 IBIS: 06_Vineland-II (intvw) } if (studymeasID == "1837") { tp = 12; // 1837 IBIS: 12_Vineland-II (intvw) } if (studymeasID == "2037") { tp = 18; // 2037 IBIS: 18_Vineland-II (surv intvw) } if (studymeasID == "1863") { tp = 24; // 1863 IBIS: 24_Vineland-II (intvw) } if (studymeasID == "2293") { tp = 36; // 2293 IBIS: 36_Vineland-II (surv intvw) } if (studymeasID == "2750") { tp = 3; // 2750 IBIS-2: 03_Vineland-II (surv intvw) } if (studymeasID == "2740") { tp = 3; // 2740 IBIS-2: 03_Vineland-II (surv intvw)_Proband } if (studymeasID == "2760") { tp = 6; // 2760 IBIS-2: 06_Vineland-II (surv intvw) } if (studymeasID == "2755") { tp = 6; // 2755 IBIS-2: 06_Vineland-II (surv intvw)_Proband } if (studymeasID == "2773") { tp = 9; // 2773 IBIS-2: 09_Vineland-II (surv intvw) } if (studymeasID == "2784") { tp = 12; // 2784 IBIS-2: 12_Vineland-II (surv intvw) } if (studymeasID == "2798") { tp = 15; // 2798 IBIS-2: 15_Vineland-II (surv intvw) } if (studymeasID == "2810") { tp = 24; // 2810 IBIS-2: 24_Vineland-II (surv intvw) } if (studymeasID == "2827") { tp = 36; // 2827 IBIS-2: 36_Vineland-II (surv intvw) } if (studymeasID == "3678") { tp = 0; // 3678 IBIS-2: PROBAND_Vineland-II (surv intvw) } string tp_text = ""; if (tp == 0) { tp_text = "Screening"; } if (tp == 3) { tp_text = "v03"; } if (tp == 6) { tp_text = "v06"; } if (tp == 9) { tp_text = "v09"; } if (tp == 12) { tp_text = "v12"; } if (tp == 15) { tp_text = "v15"; } if (tp == 18) { tp_text = "v18"; } if (tp == 24) { tp_text = "v24"; } if (tp == 36) { tp_text = "v36"; } if (tp_text != "") { ps.Add(sql.CreateParam("ID", ID, "text")); ps.Add(sql.CreateParam("studymeasID", studymeasID, "int")); DataTable dt = sql.DataTable_from_ProcName("spIBIS2_VinelandIISurvey_Recode_for_IBIS_DCC_upload", ps); string id_nopr = ID.Replace("pr", "").Replace("PR", ""); if (dt.HasRows()) { string filename = "vineland_" + subjecttype + "_" + dccID_1 + "_" + id_nopr + "_" + tp_text + ".txt"; SpreadsheetGearUtils.SaveDataTableToFile(dt, filename, "IBISVine"); lblNoData.Text = "Data saved to ??? R:\\IBIS-2\\Vineland_Uploads<br/>" + filename; lblNoData.ForeColor = Color.Blue; } else { lblNoData.Text = "No data returned"; lblNoData.ForeColor = Color.Red; } } sql.Close(); }
protected void SaveTextFileForIBIS(string ID, int studymeasID) { string tp_text = ""; int tp = 0; string filepath = @"\\autism-fs03\shared files\R drive\IBIS-2\Vineland_Uploads\"; SQL_utils sql = new SQL_utils(); DataTable dt = sql.DataTable_from_SQLstring("select * from uwautism_research_backend..tblStaff"); //SqlParameter p = sql.CreateParam("name", "8", "int"); //DataSet ds = sql.DataSet_from_ProcName("spMyProc", p); int studyID = sql.IntScalar_from_SQLstring("select max( studyid) from uwautism_research_backend.dbo.vwmasterstatus_s where id = '" + ID + "'"); string dccID_1 = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_ID('" + ID + "') "); string subjecttype = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_subjecttype('" + ID + "') "); if (studymeasID == 2671) { tp = 3; // 2671 IBIS: 03_Vineland-II (surv intvw) } if (studymeasID == 1811) { tp = 6; // 1811 IBIS: 06_Vineland-II (intvw) } if (studymeasID == 1837) { tp = 12; // 1837 IBIS: 12_Vineland-II (intvw) } if (studymeasID == 2037) { tp = 18; // 2037 IBIS: 18_Vineland-II (surv intvw) } if (studymeasID == 1863) { tp = 24; // 1863 IBIS: 24_Vineland-II (intvw) } if (studymeasID == 2293) { tp = 36; // 2293 IBIS: 36_Vineland-II (surv intvw) } if (studymeasID == 2750) { tp = 3; // 2750 IBIS-2: 03_Vineland-II (surv intvw) } if (studymeasID == 2740) { tp = 3; // 2740 IBIS-2: 03_Vineland-II (surv intvw)_Proband } if (studymeasID == 2760) { tp = 6; // 2760 IBIS-2: 06_Vineland-II (surv intvw) } if (studymeasID == 2755) { tp = 6; // 2755 IBIS-2: 06_Vineland-II (surv intvw)_Proband } if (studymeasID == 2773) { tp = 9; // 2773 IBIS-2: 09_Vineland-II (surv intvw) } if (studymeasID == 2784) { tp = 12; // 2784 IBIS-2: 12_Vineland-II (surv intvw) } if (studymeasID == 2798) { tp = 15; // 2798 IBIS-2: 15_Vineland-II (surv intvw) } if (studymeasID == 2810) { tp = 24; // 2810 IBIS-2: 24_Vineland-II (surv intvw) } if (studymeasID == 2827) { tp = 36; // 2827 IBIS-2: 36_Vineland-II (surv intvw) } if (tp == 3) { tp_text = "v03"; } if (tp == 6) { tp_text = "v06"; } if (tp == 9) { tp_text = "v09"; } if (tp == 12) { tp_text = "v12"; } if (tp == 15) { tp_text = "v15"; } if (tp == 18) { tp_text = "v18"; } if (tp == 24) { tp_text = "v24"; } if (tp == 36) { tp_text = "v36"; } string leadingzero = (tp == 3 | tp == 6 | tp == 9) ? "0" : ""; string dccIDqry = dccID_1 + "-" + ID.Replace("SEA", "") + "-" + leadingzero + tp.ToString(); if (subjecttype == "PROBAND") { tp_text = "screening"; } if (subjecttype == "CANDIDATE") { subjecttype = "SUBJECT"; } string filename = "vineland_" + subjecttype + "_" + dccID_1 + "_" + ID.Replace("PR", "") + "_" + tp_text; DataTable vin = sql.DataTable_from_SQLstring("exec spIBIS2_VineII_Recode_for_IBIS_DCC_upload '" + ID + "'," + studymeasID.ToString()); SpreadsheetGearUtils.SaveDataTableToFile(vin, filename, "IBISVine"); //string @makefile = 'BCP "exec uwautism_research_data.dbo.spIBIS2_VineII_Recode_for_IBIS_DCC_upload ''' + @ID + ''', ' + // cast(@studymeasID as varchar) + '" queryout "' + @filepath + 'DATAvineland_' + // @subjecttype + '_' + @dccID_1 + '_' + replace(@ID, 'PR','') + '_' + @tp_text + '.txt" -c -T -S' + @@servername //exec uwautism_research_data.dbo.spAUTO_SQLAgentJob_addstep @sqlagentjobname, 'query' , @makefile , 'cmdexec' , 3 /*3=goto next step, 1=quit with success */ , 1 /* run as proxy */ ///****************************************/ ///****** Merge the two *******/ ///****************************************/ //print '-- .... prepare to add step 3 .... --' //declare @joinvarsdata nvarchar(1000) //set @joinvarsdata = 'copy "' + @filepath + 'IBIS_Vineland_columns.txt" + ' + // ' "' + @filepath + 'DATAvineland_' + @subjecttype + '_' + @dccID_1 + '_' + @ID + '_' + @tp_text + '.txt"' + // ' "' + @filepath + 'vineland_' + @subjecttype + '_' + @dccID_1 + '_' + @ID + '_' + @tp_text + '.txt" /Y' //exec uwautism_research_data.dbo.spAUTO_SQLAgentJob_addstep @sqlagentjobname, 'add varnames' , @joinvarsdata , 'cmdexec' , 3 /*3=goto next step, 1=quit with success */ , 1 /* run as proxy */ //print @joinvarsdata ///****************************************/ ///****** Delete the data *******/ ///****************************************/ //print '-- .... prepare to add step 4 .... --' //declare @deldata nvarchar(1000) //set @deldata = 'del "' + @filepath + 'DATAvineland_' + @subjecttype + '_' + @dccID_1 + '_' + @ID + '_' + @tp_text + '.txt"' //exec uwautism_research_data.dbo.spAUTO_SQLAgentJob_addstep @sqlagentjobname, 'del data file' , @deldata , 'cmdexec' , 3 /*3=goto next step, 1=quit with success */ , 1 /* run as proxy */ //print @deldata //print '-- ... prepare to start job ... --' //exec uwautism_research_data.dbo.spAUTO_SQLAgentJob_addstep @sqlagentjobname, 'finish', '/* finish job. */', 'tsql', 1 //exec uwautism_research_data.dbo.spAUTO_SQLAgentJob_start @sqlagentjobname //--exec sp_executesql @select_sql_varnames sql.Close(); }