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);
}
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.");
}
}
}
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 LoadDescstats(string filename)
{
DataSet dset_descstats = new DataSet();
if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename)))
{
//convert to .xls here
try
{
dset_descstats = SpreadsheetGearUtils.GetDataSet(filename);
int g = 0;
}
catch (Exception)
{
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)
{
dset_descstats = utilDataProject.DataProject_Descstats(dataproj_pk, dset);
SpreadsheetGearUtils.SaveDataSetToExcel(dset_descstats, filename, false, "xlsx");
int g = 0;
}
else
{
string result = "Sorry, the file [" + filename + "] was not found.";
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true);
}
}
if (dset_descstats != null & dset_descstats.Tables.Count > 0)
{
foreach (DataTable dt in dset_descstats.Tables)
{
if (dt.TableName == "fileinfo")
{
GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
panel0.Controls.Add(gv);
}
else
{
var dtSorted = dt.AsEnumerable()
.OrderBy(r => r.Field <string>("Measure"))
.ThenBy(r => r.Field <string>("Varname"))
.ThenBy(r => r.Field <string>("TimePoint"))
.CopyToDataTable();
GridView gv = new GridView();
gv.RowDataBound += gv_RowDataBound;
gv.AllowSorting = true;
gv.DataSource = dtSorted;
gv.DataBind();
gv.CssClass = "GridViewClass";
Label lbl = new Label();
lbl.Text = " By " + dt.TableName;
lbl.Font.Bold = true;
lbl.Font.Size = 12;
Literal lit = new Literal();
lit.Text = "<br/><br/>";
panel1.Controls.Add(lbl);
panel1.Controls.Add(gv);
panel1.Controls.Add(lit);
}
}
}
}
