protected void btnGetAddresses_Click(object sender, EventArgs e)
{
UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelChk.FindControl("study1");
string studies = p.GetChecked_as_CSV();
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("studies", studies, "text"));
DataTable dt = sql.DataTable_from_ProcName("spTracking_Subj_AddressList", ps);
dt.TableName = "Subject Addresses";
string datenow = DateTime.Now.Year.ToString() + ((DateTime.Now.Month < 10) ? "0" : "") + DateTime.Now.Month.ToString() + ((DateTime.Now.Day < 10) ? "0" : "") + DateTime.Now.Day.ToString();
string hrminnow = ((DateTime.Now.Hour < 10) ? "0" : "") + DateTime.Now.Hour.ToString() + ((DateTime.Now.Minute < 10) ? "0" : "") + DateTime.Now.Minute.ToString();
string filename = "UWAC_Subject_Addresses_" + datenow + "_" + hrminnow; //TODO
SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, "xlsx");
}
protected string ProcessDictionary(string filename)
{
string insertresults = "";
SQL_utils sql = new SQL_utils("data");
//Check for tblpk
int tblpk = sql.IntScalar_from_SQLstring("select coalesce(tblpk,-1) from def.tbl where measureID=" + Request.QueryString["mID"]);
if (tblpk > 0)
{
DataSet ds = SpreadsheetGearUtils.GetDataSet(filename, false);
DataTable dt = ds.Tables[0];
// Add tblpk
DataColumn col = new DataColumn("tblpk", typeof(int));
col.DefaultValue = tblpk;
// Add fldextractionmode - and thus allow this measure to be imported
DataColumn col2 = new DataColumn("fldextractionmode", typeof(int));
int mode = (int)FieldExtractionMode.matchFldname;
col2.DefaultValue = mode;
dt.Columns.AddRange(new DataColumn[2] {
col, col2
});
insertresults = sql.BulkInsert(dt, "fld", "def");
}
sql.Close();
return(insertresults);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
DataTable fte = sql.DataTable_from_SQLstring("select * from tempFTE_with_NETID order by PayCycleEndDate, budget, budgetNbr, FirstMiddleLastName");
DataTable budgets = sql.DataTable_from_SQLstring("select budget, budgetNbr, budgetName, min(PayCycleEndDate) minDate, max(PayCycleEndDate) maxDate " +
", count(*) nrecs from tempFTE_with_NETID group by budget, budgetNbr, budgetName order by 1,2");
DataTable staff = sql.DataTable_from_SQLstring("select FirstMiddleLastName, UWnetid, min(PayCycleEndDate) minDate, max(PayCycleEndDate) maxDate " +
", count(distinct(budget)) n_budgets, count(*) as n_recs from tempFTE_with_NETID group by FirstMiddleLastName, UWnetid order by 1,2");
DataTable fte_proj = sql.DataTable_from_SQLstring("select vwFTE_FutureMo order by FirstMiddleLastName, budget, budgetName, budgetNbr");
DataSet ds = new DataSet();
ds.Tables.Add(fte);
ds.Tables.Add(fte_proj);
ds.Tables.Add(budgets);
ds.Tables.Add(staff);
ds.Tables[0].TableName = "FTE";
ds.Tables[1].TableName = "FTE_proj";
ds.Tables[2].TableName = "Budgets";
ds.Tables[3].TableName = "Staff";
string filename = "uwac_fte_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataSetToExcel(ds, filename, true, "xlsx");
}
protected void btnCreateExcel_Click(object sender, EventArgs e)
{
DataTable dt = GetAddressData();
string datetxt = SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, Master.Master_studyname + "_Addresses_" + datetxt, true, "xlsx");
}
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 CreateXLS(DataSet mydset)
{
if (mydset.Tables.Count > 0)
{
SpreadsheetGearUtils.SaveDataSetToExcel(mydset, "gev", true, "xls");
}
else
{
lblInfo.Text = "No tables in dataset.";
}
}
protected void ExportDictionaryForNDAR(int measureID)
{
Datadictionary dict = new Datadictionary(measureID);
if (dict.dt_ndardict.HasRows())
{
string dictfilename = String.Format("{0}_definitions", dict.measname);
dict.dt_ndardict.TableName = dictfilename;
SpreadsheetGearUtils.SaveDataTableToExcel(dict.dt_ndardict, dictfilename);
}
}
protected void btnSave_Click1(object sender, EventArgs e)
{
DataSet dset = new DataSet();
dt_rows.TableName = "rows_and_data";
dt_cols.TableName = "cols";
dset.Tables.Add(dt_rows);
dset.Tables.Add(dt_cols);
SpreadsheetGearUtils.SaveDataSetToExcel(dset, "DB_diffs" + txtSchema.Text, true, "xlsx");
}
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();
}
private void ReadExcelFile(string file_path, string filename)
{
Regex regex = new Regex(@"\d{4}"); //ensure that the filename has at least 4 digits
if (regex.IsMatch(filename))
{
string str_docversid = regex.Match(filename).Value.ToString();
int filename_docversid = Convert.ToInt32(str_docversid);
string xlfile_path = String.Format("{0}{1}", file_path, filename);
if (docversid == filename_docversid)
{
if (noColumnHeaders)
{
dset = SpreadsheetGearUtils.GetDataSet(xlfile_path, false, false, SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
}
else
{
dset = SpreadsheetGearUtils.GetDataSet(xlfile_path, false);
}
processing_results.Log(String.Format("Loaded file {0} (docversID {1})", filename, docversid));
if (dset != null)
{
if (_fileext.ToLower() == ".xlsx" | _fileext.ToLower() == ".xls")
{
processing_results.Log(String.Format("Excel file contains {0} sheets.", dset.Tables.Count));
}
else if (_fileext.ToLower() == ".csv")
{
if (dset.Tables.Count > 0)
{
processing_results.Log(String.Format("CSV file contains {0} rows.", dset.Tables[0].Rows.Count));
}
else
{
processing_results.Log(String.Format("CSV file not found."));
}
}
}
}
else
{
processing_results.Log("No docversID provided.");
}
}
}
public static bool FixXLColumnTypes(DataSet ds)
{
try
{
DataTable dtdict = ds.Tables["DataDictionary"];
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType NOT IN ( 'text','varchar','char','string')";
DataTable dtdict2 = dv.ToTable();
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString().ToLower();
if (measname.StartsWith("IntHx_by_TYPE"))
{
measname = "IntHx_by_TYPE";
}
if (measname != "IntHx_by_TYPE")
{
if (DataType == "date")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(DateTime));
}
else //if (DataType == "numeric")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(Double));
if (measname == "IntHx_by_TYPE") //also do the other 2 IntHx tables
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables["IntHx_by_CAT"], varname, typeof(Double));
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables["IntHx_by_ALL"], varname, typeof(Double));
}
}
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
protected void GetInfo(string infotype, string sqlproc)
{
SQL_utils sql = new SQL_utils("backend");
// Create a DataTable of the selected studies
DataTable dt = new DataTable();
dt.Columns.Add("fld", typeof(string));
dt.Columns.Add("value", typeof(int));
int counter = 0;
foreach (ListItem li in lstStudies.Items)
{
counter++;
lblInfo.Text += ", " + counter.ToString();
if (li.Selected)
{
DataRow r = dt.NewRow();
r["fld"] = "studyID";
r["value"] = Convert.ToInt16(li.Value);
dt.Rows.Add(r);
lblInfo.Text += "SELECTED";
}
}
ViewState["SelectedStudies"] = dt;
if (dt.Rows.Count > 0)
{
SqlParameter p = sql.CreateParam("studies", dt);
DataTable info = sql.DataTable_from_ProcName(infotype, p);
//ShowSelectedStudies(mailing);
SpreadsheetGearUtils.SaveDataTableToExcel(info, "mailing", true, "xlsx");
}
else
{
lblInfo.Text = "0 records in data table.";
}
}
public static bool ChangeDatesToString(DataTable dt)
{
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
DataColumn col = dt.Columns[i];
if (col.DataType == typeof(DateTime))
{
SpreadsheetGearUtils.ChangeColumnDataType(dt, col.ColumnName, typeof(string));
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
protected string SaveNDARCSV(string shortName, string IDexclude, string studymeasIDs, string filename, int studyID)
{
bool savedCSV = false;
DataTable dt = NDAR.GetNDAR_view(shortName, studyID, 0, false, IDexclude, studymeasIDs);
dt.TableName = shortName;
if (dt.HasRows())
{
string filename_result = SpreadsheetGearUtils.WriteDataTableAsCSV(dt, filename, "csv", true);
filename_result += String.Format("|{0}", dt.Rows.Count);
return(filename_result);
}
else
{
return(String.Format("ERROR: No rows for {0}|0", shortName));
}
}
protected void btnGetIDList_Click(object sender, EventArgs e)
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
ps.Add(sql.CreateParam("study_csv", "all", "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps);
string filename = "code6_IDs_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, true, "csv");
sql.Close();
}
}
protected void ExportDictionaryForStudy()
{
Datadictionary dict = new Datadictionary(Master.Master_studyID, true);
if (dict.dt_dict.HasRows())
{
string dictfilename = String.Format("{0}_DataDictionary", Master.Master_studyname);
dict.dt_dict.TableName = "DataDictionary";
DataSet ds = new DataSet();
ds.Tables.Add(dict.dt_dict);
if (dict.dt_ndardict.HasRows())
{
dict.dt_ndardict.TableName = "NDA_DataDictionary";
ds.Tables.Add(dict.dt_ndardict);
}
SpreadsheetGearUtils.SaveDataSetToExcel(ds, dictfilename);
}
}
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);
}
public static bool FixXLDates(DataSet ds)
{
try
{
DataTable dtdict = ds.Tables["DataDict"];
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType = 'date'";
DataTable dtdict2 = dv.ToTable();
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString();
if (DataType == "date")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(DateTime));
}
else if (DataType == "numeric")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(Double));
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
public static bool FixXLColumnTypes(DataTable dt, DataTable dtdict)
{
try
{
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType NOT IN ( 'text','varchar','char','string')";
DataTable dtdict2 = dv.ToTable();
SpreadsheetGearUtils.FixXLColumnDatatype(dt, "id", typeof(String));
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString().ToLower();
List <string> skipvars = new List <string> {
"id", "txgrp", "txstyle", "txintensity", "timept", "group"
};
if (skipvars.Contains(varname))
{
//do nothing
}
else if (DataType == "char" || DataType == "varchar" || DataType == "nvarchar")
{
//do nothing
}
else if (DataType == "date" || DataType == "smalldatetime" || DataType == "datetime")
{
DataColumn col = dt.Columns[varname];
if (col != null)
{
var coltype = dt.Columns[varname].DataType;
if (coltype.Name == "String")
{
SpreadsheetGearUtils.FixXLColumnDatatype(dt, varname, typeof(DateTime));
}
}
}
else //if (DataType == "numeric")
{
DataColumn col = dt.Columns[varname];
if (col != null)
{
var coltype = dt.Columns[varname].DataType;
if (coltype.Name == "String")
{
SpreadsheetGearUtils.FixXLColumnDatatype(dt, varname, typeof(Double?));
}
}
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
protected void btnDownloadExistingFile_Command(object sender, CommandEventArgs e)
{
ASPxSpreadsheet.Visible = false;
if (e.CommandName == "DownloadExistingFile")
{
string filename = e.CommandArgument.ToString();
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
LogDownload(filename);
DownloadFile(filename);
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else if (e.CommandName == "LoadXLSX")
{
string filename = e.CommandArgument.ToString();
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
ASPxSpreadsheet.Open(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename));
ASPxSpreadsheet.Visible = true;
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else if (e.CommandName == "DownloadExistingFile_xls")
{
string filename = e.CommandArgument.ToString().Replace(".xlsx", ".xls");
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
LogDownload(filename);
DownloadFile(filename);
}
else
{
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + e.CommandArgument.ToString())))
{
//convert to .xls here
try
{
SpreadsheetGearUtils.Save_xls_from_xlsx(e.CommandArgument.ToString());
LogDownload(filename);
DownloadFile(filename);
}
catch (Exception ex)
{
string x = ex.Message;
string result = "Sorry, the file [" + filename + "] was not found. We have tried to create it but an error occurred.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
}
else if (e.CommandName == "ViewSummary")
{
string filename = e.CommandArgument.ToString();
string pageurl = "DataProjectStats.aspx";
pageurl += "?filename=" + e.CommandArgument.ToString() + "&pk=" + Request.QueryString["pk"];
Response.Redirect(pageurl);
}
}
protected void btnDownload_Click(object sender, EventArgs e)
{
DataSet ds = GetNormsTables();
SpreadsheetGearUtils.SaveDataSetToExcel(ds, "norms_data", true, "xlsx");
}
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 btnSave_Click(object sender, EventArgs e)
{
LoadDset();
SpreadsheetGearUtils.SaveDataSetToExcel(dset, "norms_tables");
}
protected void btnDownloadExistingFile_Command(object sender, CommandEventArgs e)
{
ASPxSpreadsheet.Visible = false;
if (e.CommandName == "DownloadExistingFile")
{
string filename = e.CommandArgument.ToString();
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
LogDownload(filename);
DownloadFile(filename);
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else if (e.CommandName == "LoadRPT")
{
string filename = e.CommandArgument.ToString();
string file = Server.MapPath(String.Format("~/App_Data/DataReports/{0}", filename));
if (File.Exists(file))
//if(true)
{
richeditDoc.Open(file, DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
richeditDoc.Open(file);
richeditDoc.Visible = true;
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else if (e.CommandName == "DownloadExistingFile_xls")
{
string filename = e.CommandArgument.ToString().Replace(".xlsx", ".xls");
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
LogDownload(filename);
DownloadFile(filename);
}
else
{
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + e.CommandArgument.ToString())))
{
//convert to .xls here
try
{
SpreadsheetGearUtils.Save_xls_from_xlsx(e.CommandArgument.ToString());
LogDownload(filename);
DownloadFile(filename);
}
catch (Exception ex)
{
string x = ex.Message;
string result = "Sorry, the file [" + filename + "] was not found. We have tried to create it but an error occurred.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
}
else if (e.CommandName == "ViewSummary")
{
string filename = e.CommandArgument.ToString();
string pageurl = "DataProjectStats.aspx";
pageurl += "?filename=" + e.CommandArgument.ToString() + "&pk=" + Request.QueryString["pk"];
Response.Redirect(pageurl);
//if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
//{
// //convert to .xls here
// try
// {
// DataSet dset_descstats = SpreadsheetGearUtils.GetDataSet(filename);
// int g = 0;
// }
// catch (Exception ex)
// {
// string result = "Sorry, the file [" + filename + "] was unable to load. Create a new Excel file to generate the latest descriptive stats.";
// ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
// }
//}
//else
//{
// string data_filename = filename.Replace("DescStats","Data");
// DataSet dset = SpreadsheetGearUtils.GetDataSet(data_filename);
// int dataproj_pk = Convert.ToInt32(Request.QueryString["pk"]);
// if(dataproj_pk > 0 & dset != null & dset.Tables.Count > 0 )
// {
// string result = "Sorry, the file [" + filename + "] was not found. Attempting to create file now...";
// ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
// DataSet dset_descstats = utilDataProject.DataProject_Descstats(dataproj_pk, dset);
// SpreadsheetGearUtils.SaveDataSetToExcel(dset_descstats, filename);
// int g = 0;
// }
// else
// {
// string result = "Sorry, the file [" + filename + "] was not found.";
// ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
// }
//}
}
}
protected void btnDownload_Click(object sender, EventArgs e)
{
DataTable dt = GetActionsTables();
SpreadsheetGearUtils.SaveDataTableToExcel(dt, "Actions_list_" + Master.Master_studyname);
}
// Geom: this can be either Point or Line
public static System.Web.UI.DataVisualization.Charting.Chart MSGeom(string geom,
DataTable dt_rawdata,
string x_var,
string y_var,
string series_var,
string x_vartype,
string y_vartype,
string chartname,
string charttitle,
string xaxistitle,
string yaxistitle,
double xaxismin,
double xaxismax,
double yaxismin,
double yaxismax,
int wd,
int ht,
string customProps)
{
if (dt_rawdata != null)
{
//SQL_utils sql = new SQL_utils();
if (x_vartype == "numeric")
{
SpreadsheetGearUtils.ChangeColumnDataType(dt_rawdata, x_var, typeof(double));
}
//CREATE THE CHART
System.Web.UI.DataVisualization.Charting.Chart chart = new System.Web.UI.DataVisualization.Charting.Chart();
//Define axis data types
foreach (DataColumn col in dt_rawdata.Columns)
{
if (col.ColumnName == x_var)
{
if (x_vartype == "date")
{
col.DataType = typeof(DateTime);
}
else if (x_vartype == "numeric")
{
col.DataType = typeof(Double);
}
else if (x_vartype == "string")
{
col.DataType = typeof(string);
}
}
}
try
{
//Pass in the custom properties in the ToolTip and then
//parse later. THis is a hack that could/should be better.
//Though I wasn't able to get this "otherFields" parameter
//to work for anything but the ToolTip.
if (customProps != "")
{
//chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "ToolTip=" + customProps, PointSortOrder.Descending);
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "ToolTip=" + customProps, PointSortOrder.Descending);
}
else
{
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "", PointSortOrder.Descending);
}
List <string> series_names = new List <string>();
foreach (Series s in chart.Series)
{
series_names.Add(s.Name);
}
List <string> random_colors = new List <string> {
"123245", "76AD23", "E40011", "7D2138", "ABCD33", "4561AA", "9C3872", "ED4421", "8921DD"
, "341951", "ADED21", "5599DB"
};
int series_counter = 0;
//Assign the correct colors
foreach (Series s in chart.Series)
{
switch (geom)
{
case "point":
s.ChartType = SeriesChartType.Point;
//s.CustomProperties = "MarkerSize=markerSize";
break;
case "line":
s.ChartType = SeriesChartType.Line;
s.BorderColor = Color.Red;
break;
}
switch (x_vartype)
{
case "numeric":
s.XValueType = ChartValueType.Double;
break;
case "date":
s.XValueType = ChartValueType.Date;
break;
case "string":
s.XValueType = ChartValueType.String;
break;
}
switch (y_vartype)
{
case "numeric":
s.YValueType = ChartValueType.Double;
break;
case "date":
s.YValueType = ChartValueType.Date;
break;
case "string":
s.YValueType = ChartValueType.String;
break;
}
s.IsValueShownAsLabel = false;
//s.LabelForeColor = System.Drawing.ColorTranslator.FromHtml("#" + random_colors[series_counter]);
//s.MarkerColor = System.Drawing.ColorTranslator.FromHtml("#" + random_colors[series_counter]);
//s.LabelForeColor = ColorTranslator.FromHtml( random_colors[series_counter]);
//s.LabelBackColor = s.MarkerColor;
int counter = 2;
foreach (var point in s.Points)
{
if (geom == "line")
{
//point.MarkerStyle = MarkerStyle.Diamond;
//point.MarkerColor = Color.Lime;
}
else
{
//point.ToolTip = s.Name + ": " + counter.ToString();
//point.MarkerSize = counter;
if (point.ToolTip.Contains("|"))
{
string[] props = point.ToolTip.Split('|');
point.MarkerSize = Convert.ToInt16(props[0]);
if (props[0] == "12")
{
point.MarkerBorderColor = Color.Black;
}
else if (props[0] == "20")
{
point.MarkerBorderColor = Color.Black;
if (props.Length >= 3)
{
point.Label = props[3].ToString();
}
}
if (props.Length >= 3)
{
point.Url = props[2];
}
point.ToolTip = props[1];
counter++;
}
else
{
//
}
}
}
s.MapAreaAttributes = "target=\"_blank\"";
s.ToolTip = s.Name;
series_counter++;
}
DataSet ds_series1 = chart.DataManipulator.ExportSeriesValues();
//Insert empty points as need so stacking is correct
if (series_names.Count > 0)
{
if (x_vartype == "numeric")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
else if (x_vartype == "date")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Months, string.Join(",", series_names));
}
else if (x_vartype == "string")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
//DataSet ds_series2 = chart.DataManipulator.ExportSeriesValues();
}
chart.ImageType = System.Web.UI.DataVisualization.Charting.ChartImageType.Png;
chart.Width = wd;
chart.Height = ht;
//ADD A PLACE HOLDER CHART AREA TO THE CHART
chart.ChartAreas.Add(new ChartArea());
if (x_vartype == "numeric")
{
if (xaxismin != -999)
{
chart.ChartAreas[0].AxisX.Minimum = xaxismin;
}
if (xaxismax != -999)
{
chart.ChartAreas[0].AxisX.Maximum = xaxismax;
}
}
if (yaxismin != -999)
{
chart.ChartAreas[0].AxisY.Minimum = yaxismin;
}
if (yaxismax != -999)
{
chart.ChartAreas[0].AxisY.Maximum = yaxismax;
}
chart.ChartAreas[0].RecalculateAxesScale();
//here not helpgful --> chart.AlignDataPointsByAxisLabel();
chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisX.Title = xaxistitle;
chart.ChartAreas["ChartArea1"].AxisY.Title = yaxistitle;
chart.ChartAreas[0].AxisX.LabelStyle.Angle = -90; // Can vary from -90 to 90.
chart.ChartAreas[0].AxisX.LabelStyle.Interval = 1;
//if (YAxisType == "free")
//{
//ADD A PLACE HOLDER LEGEND TO THE CHART
chart.Legends.Add("Default");
chart.Legends["Default"].LegendStyle = LegendStyle.Column; //.Table;
chart.Legends["Default"].TableStyle = LegendTableStyle.Auto;
chart.Legends["Default"].Docking = Docking.Right;
//chart.Legends["Default"].MarkerSize = new Size(15, 15);
chart.CustomizeLegend += new EventHandler <CustomizeLegendEventArgs>(utilMSCharts.chart_CustomizeLegend);
//LegendCellColumnCollection legendcells = chart.Legends["Default"].CellColumns;
//foreach(LegendCellColumn lcc in legendcells)
//{
// lcc.SeriesSymbolSize = new System.Drawing.Size(20,20);
//}
//}
//else if (YAxisType == "fixed")
//{
// //chart.Legends.Clear();
//}
Title t = new Title();
t.Text = charttitle + "\n(created " + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString() + ")";
if (dt_rawdata.Rows.Count == 0)
{
t.Text += "\n** 0 records entered **";
}
t.Alignment = ContentAlignment.TopLeft;
t.Font = new System.Drawing.Font("Arial", 12, GraphicsUnit.Point);
chart.Titles.Add(t);
return(chart);
}
catch (Exception ex)
{
string x = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
// Main MSStackedColumn
public static System.Web.UI.DataVisualization.Charting.Chart MSStackedColumn(
DataTable dt_rawdata,
string x_var,
string y_var,
string series_var,
string x_vartype,
DataTable colors,
string chartname,
string charttitle,
string xaxistitle,
string yaxistitle,
double xaxismin,
double xaxismax,
double yaxismin,
double yaxismax,
int wd,
int ht,
DateTime?xaxismin_date,
DateTime?xaxismax_date)
{
if (dt_rawdata != null)
{
//SQL_utils sql = new SQL_utils();
if (x_vartype == "numeric")
{
SpreadsheetGearUtils.ChangeColumnDataType(dt_rawdata, x_var, typeof(double));
}
//CREATE THE CHART
System.Web.UI.DataVisualization.Charting.Chart chart = new System.Web.UI.DataVisualization.Charting.Chart();
foreach (DataColumn col in dt_rawdata.Columns)
{
if (col.ColumnName == x_var)
{
if (x_vartype == "date")
{
col.DataType = typeof(DateTime);
}
else if (x_vartype == "numeric")
{
col.DataType = typeof(Double);
}
else if (x_vartype == "string")
{
col.DataType = typeof(string);
}
}
}
try
{
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "", PointSortOrder.Descending);
List <string> series_names = new List <string>();
foreach (Series s in chart.Series)
{
series_names.Add(s.Name);
}
List <string> random_colors = new List <string> {
"123245", "76AD23", "E40011", "7D2138", "ABCD33", "4561AA", "9C3872", "ED4421", "8921DD"
, "341951", "ADED21", "5599DB"
};
int series_counter = 0;
//Assign the correct colors
foreach (Series s in chart.Series)
{
s.ChartType = SeriesChartType.StackedColumn;
switch (x_vartype)
{
case "numeric":
s.XValueType = ChartValueType.Double;
break;
case "date":
s.XValueType = ChartValueType.Date;
break;
case "string":
s.XValueType = ChartValueType.String;
break;
}
//not helpful --> s.IsXValueIndexed = true;
//s.Sort(PointSortOrder.Ascending, sortBy: ("Y"));
string str_color;
try
{
str_color = colors.AsEnumerable().Where(r => r.Field <string>("group_var") == s.Name).Select(r => r.Field <string>("group_color")).First();
}
catch (Exception ex)
{
string x = ex.Message;
str_color = random_colors[series_counter];
}
s.Color = System.Drawing.ColorTranslator.FromHtml("#" + str_color);
s.ToolTip = s.Name;
series_counter++;
}
DataSet ds_series1 = chart.DataManipulator.ExportSeriesValues();
//Insert empty points as need so stacking is correct
if (series_names.Count > 0)
{
if (x_vartype == "numeric")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
else if (x_vartype == "date")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Months, string.Join(",", series_names));
}
else if (x_vartype == "string")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
DataSet ds_series2 = chart.DataManipulator.ExportSeriesValues();
}
chart.ImageType = System.Web.UI.DataVisualization.Charting.ChartImageType.Png;
chart.Width = wd;
chart.Height = ht;
//ADD A PLACE HOLDER CHART AREA TO THE CHART
chart.ChartAreas.Add(new ChartArea());
if (x_vartype == "numeric")
{
if (xaxismin != -999)
{
chart.ChartAreas[0].AxisX.Minimum = xaxismin;
}
if (xaxismax != -999)
{
chart.ChartAreas[0].AxisX.Maximum = xaxismax;
}
}
else if (x_vartype == "date")
{
if (xaxismin_date != null)
{
DateTime mydate = Convert.ToDateTime(xaxismin_date);
chart.ChartAreas[0].AxisX.Minimum = mydate.ToOADate();
}
if (xaxismax_date != null)
{
DateTime mydate = Convert.ToDateTime(xaxismax_date);
chart.ChartAreas[0].AxisX.Maximum = mydate.ToOADate();
}
}
if (yaxismin != -999)
{
chart.ChartAreas[0].AxisY.Minimum = yaxismin;
}
if (yaxismax != -999)
{
chart.ChartAreas[0].AxisY.Maximum = yaxismax;
}
chart.ChartAreas[0].RecalculateAxesScale();
//here not helpgful --> chart.AlignDataPointsByAxisLabel();
chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisX.Title = xaxistitle;
chart.ChartAreas["ChartArea1"].AxisY.Title = yaxistitle;
//if (YAxisType == "free")
//{
//ADD A PLACE HOLDER LEGEND TO THE CHART
chart.Legends.Add("Default");
chart.Legends["Default"].LegendStyle = LegendStyle.Table;
chart.Legends["Default"].TableStyle = LegendTableStyle.Auto;
chart.Legends["Default"].Docking = Docking.Right;
//}
//else if (YAxisType == "fixed")
//{
// //chart.Legends.Clear();
//}
Title t = new Title();
t.Text = charttitle + "\n(created " + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString() + ")";
if (dt_rawdata.Rows.Count == 0)
{
t.Text += "\n** 0 records entered **";
}
t.Alignment = ContentAlignment.TopLeft;
t.Font = new System.Drawing.Font("Arial", 12, GraphicsUnit.Point);
chart.Titles.Add(t);
return(chart);
}
catch (Exception ex)
{
string x = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
protected string ProcessOtherIDfile(string filename)
{
string result = "";
string fullname = MapPath(UploadDirectory) + filename;
DataSet ds = SpreadsheetGearUtils.GetDataSet(fullname, false);
DataTable dt = ds.Tables[0];
bool hasID = false;
bool hasotherID = false;
bool hassiteID = false;
bool hasstudyID = false;
foreach (DataColumn col in dt.Columns)
{
string colname = col.ColumnName.ToLower();
col.ColumnName = colname;
if (colname == "id")
{
hasID = true;
}
if (colname == "otherid")
{
hasotherID = true;
}
if (colname == "siteid")
{
hassiteID = true;
}
if (colname == "studyid")
{
hasstudyID = true;
}
}
if (hasID && hasotherID && hassiteID && hasstudyID)
{
SQL_utils sql = new SQL_utils("data");
int success_counter = 0;
int insertfail_counter = 0;
int cantfindID_counter = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string otherID = row["otherid"].ToString();
string siteID = row["siteid"].ToString();
string studyID = row["studyid"].ToString();
string ID = row["id"].ToString();
int personID = 0;
try
{
personID = sql.IntScalar_from_SQLstring(String.Format("select personID from uwautism_research_backend..vwMasterStatus_S where ID='{0}' and studyID={1}", ID, studyID));
}
catch (Exception ex)
{
string sqlcode2 = String.Format("insert into uwautism_research_backend..tblOtherID_failed(studyID, ID, otherID, otherIDsiteID, failreason, created, createdBy) values({0},'{1}','{2}', {3}, 'No personID',getdate(), sec.systemuser())"
, studyID, ID, otherID, siteID);
sql.NonQuery_from_SQLstring(sqlcode2);
cantfindID_counter++;
}
if (personID > 0)
{
string sqlcode = String.Format("insert into uwautism_research_backend..tblOtherID(personID, otherID, otherIDsiteID, created, createdBy) values({0},'{1}',{2}, getdate(), sec.systemuser())"
, personID, otherID, siteID);
try
{
sql.NonQuery_from_SQLstring(sqlcode);
success_counter++;
}
catch (Exception ex)
{
string sqlcode2 = String.Format("insert into uwautism_research_backend..tblOtherID_failed(studyID, ID, otherID, otherIDsiteID, failreason, created, createdBy) values({0},'{1}','{2}', {3}, 'failed insert',getdate(), sec.systemuser())"
, studyID, ID, otherID, siteID);
sql.NonQuery_from_SQLstring(sqlcode2);
insertfail_counter++;
}
}
}
result = String.Format(" OtherID inserted for {0} records.", success_counter);
if (cantfindID_counter > 0)
{
result += String.Format(" Cannot find ID for {0} records", cantfindID_counter);
}
if (insertfail_counter > 0)
{
result += String.Format(" Insert failed for {0} records", insertfail_counter);
}
}
else
{
result = "File does not contain all the required fields: studyID, ID, otherID, siteID. Check the file for the correct column names.";
}
return(result);
}
protected void ShowCode6Wide()
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
//var x = lstStudy.SelectedValue;
var x2 = gridStudy.GetSelectedFieldValues("studyID");
if (x2.Count == 0)
//if (lstStudy.SelectedValue == "")
{
lblSelectOne.Text = "Select some studies.";
lblSelectOne.Visible = true;
}
else if (chkDisplayOnPage.Checked == false && chkXLSX.Checked == false)
{
lblSelectOne.Text = "Select the output method.";
lblSelectOne.Visible = true;
}
else
{
SQL_utils sql = new SQL_utils("backend");
string study_csv = String.Join(",", x2);
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
//ps.Add(sql.CreateParam("study_csv", lstStudy.SelectedValue, "text"));
ps.Add(sql.CreateParam("study_csv", study_csv, "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps);
PivotTable pivotTable = utilPivotTable.DataTable_to_PivotTable(dt, new List <string> {
"code6"
}, new List <string> {
"source_of_ID"
}, "ID",
new List <utilPivotTable.AggrFx> {
utilPivotTable.AggrFx.Concat2
});
lblSelectOne.Visible = false;
if (chkDisplayOnPage.Checked == true)
{
//Write back to page
var htmlResult = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
pvtHtmlWr.TotalsColumn = false;
pvtHtmlWr.TotalsRow = false;
pvtHtmlWr.GrandTotal = false;
pvtHtmlWr.Write(pivotTable);
Literal xtab = new Literal();
xtab.Text = htmlResult.ToString();
panel_wide.Controls.Add(xtab);
}
if (chkXLSX.Checked == true)
{
var dataTblWr = new PivotTableDataTableWriter("Test"); //.Write(pivotTable);
var tbl = dataTblWr.Write(pivotTable);
string filename = "code6_IDs_wide_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(tbl, filename, true, "xlsx");
}
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();
}
