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