/// <summary>
/// Handles the upload button event. We save the file to disk, attempt to read a Lab Kit from it, and attempt to save it to the DB
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnLabKitUpload_Click(object sender, EventArgs e)
{
DataTable dt = null;
List <int> labKitLoci = new List <int>();
// make sure we have a lab kit name
if (string.IsNullOrEmpty(txtLabKitName.Text))
{
MessageBox.Show("Please enter a name for the Lab Kit.");
return;
}
// make sure a file has been uploaded with this post
if (string.IsNullOrEmpty(FileUpload1.FileName))
{
MessageBox.Show("Please select a Lab Kit file.");
return;
}
try
{
string connString = string.Empty;
string extension = string.Empty;
// determine file type
if (FileUpload1.FileName.EndsWith(".xlsx"))
{
extension = ".xlsx";
}
else if (FileUpload1.FileName.EndsWith(".xls"))
{
extension = ".xls";
}
// save file
string filename = DateTime.Now.Ticks.ToString() + extension;
FileUpload1.SaveAs(Server.MapPath("~/Admin/Upload/" + filename));
// create connection string
switch (extension)
{
case ".xlsx":
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1';");
break;
case ".xls":
connString = string.Format("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';");
break;
}
// create a dropout rate table
DataTable dtDropouts = new DataTable();
dtDropouts.Columns.Add(new DataColumn("LabKitID", typeof(Guid)));
dtDropouts.Columns.Add(new DataColumn("LocusID", typeof(int)));
dtDropouts.Columns.Add(new DataColumn("TypeID", typeof(int)));
dtDropouts.Columns.Add(new DataColumn("DropOptionID", typeof(int)));
dtDropouts.Columns.Add(new DataColumn("DropoutRate", typeof(double)));
dtDropouts.Columns.Add(new DataColumn("NoOfPersonsInvolvd", typeof(int)));
dtDropouts.Columns.Add(new DataColumn("Deducible", typeof(string)));
DataTable dtLocus = bi.GetLocus(Guid.Empty);
// get drop out rates
// for 1 through 4 people, obviously
for (int i = 1; i <= 4; i++)
{
// open the file and read the data from the sheet which we are working (named "1 person" and so on)
using (OleDbConnection cn = new OleDbConnection(connString))
{
cn.Open();
string query = string.Empty;
switch (extension)
{
case ".xlsx": query = "SELECT * FROM [" + i + " person$]"; break;
case ".xls": query = "SELECT * FROM [" + i + " person$]"; break;
}
// read file
OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn);
dt = new DataTable();
adapter.Fill(dt);
}
string deducible = string.Empty;
List <DropOutRow> vals = new List <DropOutRow>();
// get loci
int rowIdx = 2, colIdx = 2;
List <string> locusNames = new List <string>();
while (colIdx < dt.Columns.Count && dt.Rows[rowIdx][colIdx].ToString().Trim() != string.Empty)
{
locusNames.Add(dt.Rows[rowIdx][colIdx++].ToString().Trim());
}
Dictionary <string, int> locusIDMap = new Dictionary <string, int>();
foreach (DataRow dr in dtLocus.Rows)
{
if (locusNames.Contains(dr["FieldName"].ToString(), StringComparer.Create(CultureInfo.CurrentCulture, true)))
{
locusIDMap.Add(dr["FieldName"].ToString().ToUpper(), Convert.ToInt32(dr["FieldValue"].ToString()));
}
}
locusNames.Clear();
foreach (string locus in locusIDMap.Keys)
{
locusNames.Add(locus);
}
// set up the lab kit loci. we will be sending these to the database later
if (labKitLoci.Count == 0)
{
foreach (string locus in locusIDMap.Keys)
{
labKitLoci.Add(locusIDMap[locus]);
}
}
// get the deducible, PHET1, for persons i (which will be 1 through 4, see above)
int firstRow = 3, firstCol = 2, type = 1, persons = i;
deducible = "Yes";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
// get the deducible, PHET2, for persons i (which will be 1 through 4, see above)
firstRow = 14;
firstCol = 2;
type = 2;
persons = i;
deducible = "Yes";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
// get the deducible, PHOM1, for persons i (which will be 1 through 4, see above)
firstRow = 25;
firstCol = 2;
type = 3;
persons = i;
deducible = "Yes";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
// if we're doing more than one person, then get the non-deducible too
if (i > 1)
{
// get the non-deducible, PHET1, for persons i (which will be 1 through 4, see above)
firstRow = 38;
firstCol = 2;
type = 1;
persons = i;
deducible = "No";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
// get the non-deducible, PHET2, for persons i (which will be 1 through 4, see above)
firstRow = 49;
firstCol = 2;
type = 2;
persons = i;
deducible = "No";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
// get the non-deducible, PHOM1, for persons i (which will be 1 through 4, see above)
firstRow = 60;
firstCol = 2;
type = 3;
persons = i;
deducible = "No";
vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons));
}
// go through our DropOutRows we created from the Excel file and add them to our table
foreach (DropOutRow drow in vals)
{
DataRow dr = dtDropouts.NewRow();
dr["LocusID"] = drow.LocusID;
dr["TypeID"] = drow.TypeID;
dr["DropOptionID"] = drow.DropOptionID;
dr["DropoutRate"] = drow.DropoutRate;
dr["NoOfPersonsInvolvd"] = drow.NoOfPersonsInvolvd;
dr["Deducible"] = drow.Deducible;
dtDropouts.Rows.Add(dr);
}
vals.Clear();
}
// create a dropins table
DataTable dtDropins = new DataTable();
dtDropins.Columns.Add(new DataColumn("LabKitID", typeof(Guid)));
dtDropins.Columns.Add(new DataColumn("DropInRateID", typeof(string)));
dtDropins.Columns.Add(new DataColumn("Type", typeof(string)));
dtDropins.Columns.Add(new DataColumn("DropInRate", typeof(double)));
// get drop in rates
// open the file and get the data from the dropins table
using (OleDbConnection cn = new OleDbConnection(connString))
{
cn.Open();
string query = string.Empty;
switch (extension)
{
case ".xlsx": query = "SELECT * FROM [Drop-in$]"; break;
case ".xls": query = "SELECT * FROM [Drop-in$]"; break;
}
// read file
OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn);
dt = new DataTable();
adapter.Fill(dt);
}
// go through the specific areas in the file
for (int i = 2; i <= 7; i++)
{
DataRow dr = dtDropins.NewRow();
dr["DropinRateID"] = dt.Rows[i][0];
dr["Type"] = dt.Rows[i][1];
dr["DropInRate"] = dt.Rows[i][2];
dtDropins.Rows.Add(dr);
}
// save the lab kit name and get back a GUID for it
string guid = bi.SaveLabKit(txtLabKitName.Text, "");
Log.Info(Context.User.Identity.Name, Request.FilePath, Session, "Uploaded Lab Kit", guid);
// add that GUID to the tables of drop-ins and drop-outs
foreach (DataRow dr in dtDropouts.Rows)
{
dr["LabKitID"] = guid;
}
foreach (DataRow dr in dtDropins.Rows)
{
dr["LabKitID"] = guid;
}
// save the drop-ins and drop-outs to the database
bi.SaveLabKitData(guid, dtDropouts, dtDropins, labKitLoci);
// refresh the list of lab kits (should now include the new one)
DataTable dt2 = bi.GetLabKits();
this.gvLabKits.DataSource = dt2;
this.gvLabKits.DataBind();
txtLabKitName.Text = string.Empty;
}
catch (Exception ex)
{
// handle errors
Log.Error(Context.User.Identity.Name, Request.FilePath, Session, "Error Adding Lab Kit", "File Parsing Error", ex);
MessageBox.Show("There was an error reading the uploaded file. Please try uploading an Excel file in the correct format.");
return;
}
}