Exemple #1
0
    /// <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;
        }
    }