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