/// <summary>
/// Handles the locus changing so we display the correct alleles for it
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ddlLocus_SelectedIndexChanged(object sender, EventArgs e)
{
FST.Common.Business_Interface bi = new FST.Common.Business_Interface();
// if the locus is blank, don't show an alleles filter
if (String.IsNullOrEmpty(this.ddlLocus.SelectedValue))
{
this.ddlAlleleNo.Items.Clear();
this.ddlAlleleNo.Items.Add("");
this.ddlAlleleNo.SelectedIndex = 0;
}
else
{ // otherwise, if a locus is selected
// get the alleles for that locus
DataTable dt = bi.GetAlleles(this.ddlLocus.SelectedValue);
this.ddlAlleleNo.DataSource = dt;
// add a blank row
DataRow row = dt.NewRow();
row["FieldName"] = DBNull.Value;
row["FieldValue"] = DBNull.Value;
dt.Rows.Add(row);
// and bind it to the allele drop down
this.ddlAlleleNo.DataTextField = "FieldName";
this.ddlAlleleNo.DataValueField = "FieldValue";
this.ddlAlleleNo.DataBind();
this.ddlAlleleNo.SelectedIndex = 0;
}
}
/// <summary>
/// This is the page Load event of the Home page with the default values
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//gets all the Case Types
FST.Common.Business_Interface bi = new FST.Common.Business_Interface();
DataTable dt = bi.GetCaseTypes();
this.ddlCase_Type.DataSource = dt;
this.ddlCase_Type.DataTextField = "FieldName";
this.ddlCase_Type.DataValueField = "FieldValue";
this.ddlCase_Type.DataBind();
this.ddlCase_Type.SelectedIndex = 0;
this.ddlCase_Type_SelectedIndexChanged(ddlCase_Type, new EventArgs());
//populates the Theta
dt.Clear();
dt = bi.GetTheta();
this.ddlTheta.DataSource = dt;
this.ddlTheta.DataTextField = "FieldName";
this.ddlTheta.DataValueField = "FieldValue";
this.ddlTheta.DataBind();
this.ddlTheta.SelectedIndex = 2;
// get the lab kits
dt.Clear();
dt = bi.GetLabKits();
this.ddlLabKit.DataSource = dt;
this.ddlLabKit.DataTextField = "FieldName";
this.ddlLabKit.DataValueField = "FieldValue";
this.ddlLabKit.DataBind();
int cnt = 0;
if (Session["LabKitID"] != null)
{
this.ddlLabKit.SelectedValue = Session["LabKitID"].ToString();
}
else
{
this.ddlLabKit.SelectedIndex = 0;
}
Session["LabKitID"] = this.ddlLabKit.SelectedValue;
Session["LabKitName"] = this.ddlLabKit.SelectedItem.Text;
this.ddlDegradedType.SelectedIndex = 0;
// show the appropriate panels for the first comparison in the list
this.PnlCaseTypes.Visible = true;
this.pnlSuspPrfl1.Visible = true;
this.pnlSuspPrfl2.Visible = false;
this.pnlKnownPrfl1.Visible = false;
this.pnlKnownPrfl2.Visible = false;
this.pnlKnownPrfl3.Visible = false;
this.pnlKnownPrfl4.Visible = false;
this.btnGo.Visible = true;
this.btnBulk.Visible = true;
this.lblDegradedType.Visible = true;
this.ddlDegradedType.Visible = true;
this.pnlUserWarning.Visible = false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FST.Common.Business_Interface bi = new FST.Common.Business_Interface();
// load the races (or ethnicities) into the page
DataTable dt = bi.GetRaces();
this.ddlRace.DataSource = dt;
DataRow row = dt.NewRow();
row["FieldName"] = DBNull.Value;
row["FieldValue"] = DBNull.Value;
dt.Rows.Add(row);
this.ddlRace.DataTextField = "FieldName";
this.ddlRace.DataValueField = "FieldValue";
this.ddlRace.DataBind();
this.ddlRace.Text = "";
dt.Clear();
// get the loci (for all lab kits)
dt = bi.GetLocus(Guid.Empty);
this.ddlLocus.DataSource = dt;
row = dt.NewRow();
row["FieldName"] = DBNull.Value;
row["FieldValue"] = DBNull.Value;
dt.Rows.Add(row);
this.ddlLocus.DataTextField = "FieldName";
this.ddlLocus.DataValueField = "FieldValue";
this.ddlLocus.DataBind();
this.ddlLocus.Text = "";
dt.Clear();
// get the alleles for the selected locus
dt = bi.GetAlleles(this.ddlLocus.SelectedValue);
this.ddlAlleleNo.DataSource = dt;
row = dt.NewRow();
row["FieldName"] = DBNull.Value;
row["FieldValue"] = DBNull.Value;
dt.Rows.Add(row);
this.ddlAlleleNo.DataTextField = "FieldName";
this.ddlAlleleNo.DataValueField = "FieldValue";
this.ddlAlleleNo.DataBind();
this.ddlAlleleNo.Text = "";
dt.Clear();
// get the frequencies
dt = bi.GetFrequencyData();
this.gvFreqView.DataSource = dt;
this.gvFreqView.DataBind();
}
}
/// <summary>
/// Applies sort and search to the grid
/// </summary>
public void PopulateGrid()
{
FST.Common.Business_Interface bi = new FST.Common.Business_Interface();
// get all the frequencies
DataTable dt = bi.GetFrequencyData();
using (DataView dv = new DataView(dt))
{
// apply race filter
string strRowFilter = "";
if (!String.IsNullOrEmpty(this.ddlRace.SelectedValue))
{
strRowFilter = "EthnicID = '" + this.ddlRace.SelectedValue + "'";
}
// apply locus filter
if (!String.IsNullOrEmpty(this.ddlLocus.SelectedValue))
{
if (!String.IsNullOrEmpty(strRowFilter))
{
strRowFilter += " and ";
}
strRowFilter += "LocusID = '" + this.ddlLocus.SelectedValue + "'";
}
// apply allele filter
if (!String.IsNullOrEmpty(this.ddlAlleleNo.SelectedValue))
{
if (!String.IsNullOrEmpty(strRowFilter))
{
strRowFilter += " and ";
}
strRowFilter += "AlleleID = '" + this.ddlAlleleNo.SelectedValue + "'";
}
// apply all filtes to data view
if (!String.IsNullOrEmpty(strRowFilter))
{
dv.RowFilter = strRowFilter;
}
// set data view source for gridview and databind
this.gvFreqView.DataSource = dv;
this.gvFreqView.DataBind();
}
}
/// <summary>
/// Saves the updated frequency to the database
/// </summary>
public void SaveData()
{
// go through each of the rows
for (int i = 0; i < gvFreqView.Rows.Count; i++)
{
// get the ID, current value, and old value
string strFreq_Id = ((Label)gvFreqView.Rows[i].FindControl("lblFrequency_ID")).Text;
string strFreqNew = ((TextBox)gvFreqView.Rows[i].FindControl("txtFrequency")).Text;
string strFreqOld = ((Label)gvFreqView.Rows[i].FindControl("lblOldFrequency")).Text;
// if we have a difference between the old value and the current value
if (strFreqNew != strFreqOld)
{
// update the frequency in the DB
FST.Common.Business_Interface bi = new FST.Common.Business_Interface();
string message = bi.UpdateFrequency(strFreq_Id, strFreqNew);
// log the change
Log.Info(Context.User.Identity.Name, Request.FilePath, Session, "Edited Frequency Rate", "ID:" + strFreq_Id + ",OldValue:" + strFreqOld + ",NewValue:" + strFreqNew);
}
}
}
/// <summary>
/// Constructor takes the DataRow returned from the database and creates a case.
/// </summary>
/// <param name="dr">DataRow with case data</param>
public ComparisonCase(DataRow dr)
{
bi = FSTService.Instance.bi;
if (dr == null)
{
return;
}
// get our record ID, the Comparison Data, and set up some variables that we use to process the report
comparisonData = ComparisonData.Deserialize(dr["ComparisonXML"].ToString());
recordID = dr["RecordID"].ToString();
strUserId = comparisonData.UserName;
strHp_Head = comparisonData.HpHead;
strHd_Head = comparisonData.HdHead;
strLab_Popultn_Type = comparisonData.BulkType == ComparisonData.enBulkType.FromFile ? "From File" : comparisonData.BulkType == ComparisonData.enBulkType.LabTypes ? "Lab Types" : "Population";
strUserName = comparisonData.UserName.Substring(comparisonData.UserName.LastIndexOf('\\') + 1);
// spawn thread
ThreadPool.QueueUserWorkItem(new WaitCallback(ComparisonWorkerThreadMain));
}
/// <summary>
/// This class prepares a DataSet that is passed into the ReportPrinter classes to print out a copy of the report.
/// </summary>
/// <returns>A DataSet with DataTables populated with the data for printing.</returns>
public DataSet Print()
{
// we dynamically generate the locus columns based on this lab kit
DataTable dtLocus = new Business_Interface().GetLocus(this.LabKitID);
List <string> loci = new List <string>();
foreach (DataRow dr in dtLocus.Rows)
{
loci.Add(dr["FieldName"].ToString().ToUpper());
}
DataSet val = new DataSet("PrintDataSet");
// table with report parameters
DataTable dtParams = new DataTable("tblParameters");
dtParams.Columns.Add("FBNo", typeof(string));
dtParams.Columns.Add("RefNo", typeof(string));
dtParams.Columns.Add("ItemNo", typeof(string));
dtParams.Columns.Add("SuspectNo", typeof(string));
dtParams.Columns.Add("DropOut", typeof(string));
dtParams.Columns.Add("CreatedBy", typeof(string));
dtParams.Columns.Add("Hp", typeof(string));
dtParams.Columns.Add("Hd", typeof(string));
dtParams.Columns.Add("Deducible", typeof(string));
dtParams.Columns.Add("Degradation", typeof(string));
DataRow drParams = dtParams.NewRow();
drParams["FBNo"] = this.FB1;
drParams["RefNo"] = this.FB2;
drParams["ItemNo"] = this.Item;
drParams["SuspectNo"] = this.Comparison;
drParams["DropOut"] = this.DNAAmount;
drParams["CreatedBy"] = this.UserName;
drParams["Hp"] = this.HpHead;
drParams["Hd"] = this.HdHead;
drParams["Deducible"] = this.Deducible ? "Yes" : "No";
drParams["Degradation"] = this.Degradation == enDegradation.None ? "ND" : this.Degradation == enDegradation.Mild ? "MD" : "SD";
dtParams.Rows.Add(drParams);
// table with the comaprison profiles/evidence
DataTable dtAlleles = new DataTable("tblAlleles");
dtAlleles.Columns.Add("Type", typeof(string));
dtAlleles.Columns.Add("Profile", typeof(string));
// we dynamically generate the locus columns based on this lab kit
foreach (string locus in loci)
{
dtAlleles.Columns.Add(locus);
}
// copy the comparisons alleles from the dictionaries
if (this.ComparisonAlleles.Count >= 1)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Comparison1Name != string.Empty ? string.Format("{0} (Comparison)", this.Comparison1Name) : string.Format("Comparison{0}", this.NumeratorProfiles.ComparisonCount > 1 ? " 1" : string.Empty);
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (ComparisonAlleles[1].ContainsKey(locus))
{
dr[locus] = ComparisonAlleles[1][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.ComparisonAlleles.Count >= 2)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Comparison2Name != string.Empty ? string.Format("{0} (Comparison)", this.Comparison2Name) : "Comparison 2";
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (ComparisonAlleles[2].ContainsKey(locus))
{
dr[locus] = ComparisonAlleles[2][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.ComparisonAlleles.Count >= 3)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Comparison3Name != string.Empty ? string.Format("{0} (Comparison)", this.Comparison2Name) : "Comparison 3";
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (ComparisonAlleles[3].ContainsKey(locus))
{
dr[locus] = ComparisonAlleles[3][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.ComparisonAlleles.Count >= 4)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Comparison4Name != string.Empty ? string.Format("{0} (Comparison)", this.Comparison2Name) : "Comparison 4";
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (ComparisonAlleles[4].ContainsKey(locus))
{
dr[locus] = ComparisonAlleles[4][locus];
}
}
dtAlleles.Rows.Add(dr);
}
// copy the knowns alleles from the dictionaries
if (this.KnownsAlleles.Count >= 1)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Known1Name != string.Empty ? string.Format("{0} (Known)", this.Known1Name) : string.Format("Known{0}", this.NumeratorProfiles.KnownCount > 1 ? " 1" : string.Empty);
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (KnownsAlleles[1].ContainsKey(locus))
{
dr[locus] = KnownsAlleles[1][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.KnownsAlleles.Count >= 2)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Known2Name != string.Empty ? string.Format("{0} (Known)", this.Known2Name) : string.Format("Known 2");
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (KnownsAlleles[2].ContainsKey(locus))
{
dr[locus] = KnownsAlleles[2][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.KnownsAlleles.Count >= 3)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Known3Name != string.Empty ? string.Format("{0} (Known)", this.Known3Name) : string.Format("Known 3");
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (KnownsAlleles[3].ContainsKey(locus))
{
dr[locus] = KnownsAlleles[3][locus];
}
}
dtAlleles.Rows.Add(dr);
}
if (this.KnownsAlleles.Count >= 4)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = this.Known4Name != string.Empty ? string.Format("{0} (Known)", this.Known4Name) : string.Format("Known 4");
dr["Profile"] = string.Empty;
foreach (string locus in loci)
{
if (KnownsAlleles[4].ContainsKey(locus))
{
dr[locus] = KnownsAlleles[4][locus];
}
}
dtAlleles.Rows.Add(dr);
}
// copy the evidence from the dictionaries
List <DataRow> drEvidenceRows = new List <DataRow>();
for (int replicate = 1; replicate <= 3; replicate++)
{
DataRow dr = dtAlleles.NewRow();
dr["Type"] = "Evidence";
dr["Profile"] = replicate.ToString();
drEvidenceRows.Add(dr);
}
foreach (string locus in loci)
{
foreach (int replicate in EvidenceAlleles[locus].Keys)
{
if (EvidenceAlleles.ContainsKey(locus))
{
drEvidenceRows[replicate - 1][locus] = EvidenceAlleles[locus][replicate].Replace(",", ", "); // here we add a space after the comma so that alleles are properly separated
}
}
}
foreach (DataRow dr in drEvidenceRows)
{
dtAlleles.Rows.Add(dr);
}
// copy the result data and format it accordingly (scientific notation for values less than 0.01 or greater than 1000000)
DataTable dtResult = new DataTable("tblResults");
dtResult.Columns.Add("Asian");
dtResult.Columns.Add("Black");
dtResult.Columns.Add("Caucasian");
dtResult.Columns.Add("Hispanic");
dtResult.Rows.Add(new object[]
{
(this.AsianLR < 0.1 || this.AsianLR >= 1000 ? String.Format("{0:#.##e+00}", this.AsianLR) : Convert.ToString(Math.Round(this.AsianLR, 2))),
(this.BlackLR < 0.1 || this.BlackLR >= 1000 ? String.Format("{0:#.##e+00}", this.BlackLR) : Convert.ToString(Math.Round(this.BlackLR, 2))),
(this.CaucasianLR < 0.1 || this.CaucasianLR >= 1000 ? String.Format("{0:#.##e+00}", this.CaucasianLR) : Convert.ToString(Math.Round(this.CaucasianLR, 2))),
(this.HispanicLR < 0.1 || this.HispanicLR >= 1000 ? String.Format("{0:#.##e+00}", this.HispanicLR) : Convert.ToString(Math.Round(this.HispanicLR, 2)))
});
val.Tables.Add(dtParams);
val.Tables.Add(dtAlleles);
val.Tables.Add(dtResult);
return(val);
}
/// <summary>
/// This method uses the Results dictionary, Known Profiles table, and Comparison Data structure to fill out the template passed as this class' constructor.
/// </summary>
/// <param name="Results">Dictionary holding results from the bulk comparison. See FST_WindowsService.</param>
/// <param name="dtKnownProfile">Data Table holding profiles used in the bulk comparison. See FST_WindowsService and GetKnown_Profile in Business_Interface in FST.Common.</param>
/// <param name="comparisonData">Comparison Data structure holding comparison information. See ComparisonData in FST.Common</param>
/// <returns>A string path to the saved report.</returns>
public string Print(Dictionary <string, Dictionary <string, float> > Results, DataTable dtKnownProfile, ComparisonData comparisonData)
{
// READ THIS: there are quite a few assumptions made in this code. you might want to read through the comments to understand
// what is going on. specifically, you will want to look at how the column names from Excel are mapped to the first row as column names (see dtSchema and colMap)
// also, you might want to see how we generate the locus columns dynamically in the third sheet, and then read them back.
// you may also want to look at how we grab the last write time for the output file, then we check to see if it has been written to, and whether it's locked.
// also, there is an assumption about the columns which are present in the template, and their order (see the DELETE statements)
// this returns the printed dataset from Comparison Data. check method for details on how we prepare data for printing.
DataSet dsPrint = comparisonData.Print();
DateTime bulkLastWriteTime;
int retries = 0;
#region Generate File Path
// here we generate a filename for the final report based on the username and the current time.
// there may be a false assumption here that no user will finish two comparisons in the same second.
string strUserName = comparisonData.UserName.Substring(comparisonData.UserName.LastIndexOf('\\') + 1);
string filename = outputPath + "FSTReport_" + strUserName + "_" + DateTime.Now.Year.ToString().PadLeft(2, '0');
filename += DateTime.Now.Month.ToString().PadLeft(2, '0');
filename += DateTime.Now.Day.ToString().PadLeft(2, '0');
filename += DateTime.Now.Hour.ToString().PadLeft(2, '0');
filename += DateTime.Now.Minute.ToString().PadLeft(2, '0');
filename += DateTime.Now.Second.ToString().PadLeft(2, '0');
filename += ".xlsx";
#endregion
lock (lockWriting)
{
#region Prepare File
// get a copy of the template and store the last time it was written to. the last time it was written to is used to see if the ACE OLEDB buffer has flushed.
File.Copy(templatePath, filename);
bulkLastWriteTime = File.GetLastWriteTime(filename);
#endregion
#region Connection String
string connString = string.Empty;
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=No';");
#endregion
// open the file
using (OleDbConnection cn = new OleDbConnection(connString))
{
#region Prepare Some Variables
OleDbCommand cmd; // recycled command refernce, used quite a lot below
cn.Open();
string query = string.Empty; // recycled query string for the command above
string sheetName = string.Empty; // this is the table name in the query and the sheet name in Excel (with $ appended)
// this table is used to retrieve the schema of the current table.
// we do not bring the data in with headers because we need to insert new columns for the dynamically generated locus columns
DataTable dtSchema;
Dictionary <string, string> colMap; // this is used to map the friendly column names to Excel column names F1,F2,F3,...,Fn
#endregion
sheetName = "Results$"; // we switch to the Results table here
#region DELETE from Results
// delete everything on the sheet except the first column (hence checking where F1 <> 'ID')
// our assumption is that this is the first column. if this changes, this code must change
query = "DELETE FROM [" + sheetName + "] WHERE F1 <> 'ID'";
cmd = new OleDbCommand(query, cn);
cmd.ExecuteNonQuery();
#endregion
#region Get Column Mapping
// we get the schema and column map for the current sheet/table
dtSchema = getTable(cn, sheetName);
colMap = getColumnMap(dtSchema);
#endregion
#region INSERT Results
// iterate over the results and insert them into the table.
// it is assumed that our columns in the template and our races in the database will match.
// if there is ever a need for dynamic race column generation we could create those too like the loci below
query = "INSERT INTO [" + sheetName + "] (" + getColumns(colMap) + ") VALUES (" + getColumnNames(colMap) + ")";
foreach (string key in Results.Keys)
{
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@ID", key);
foreach (string race in Results[key].Keys) // we assume the races match those in the spreadsheet here
{
cmd.Parameters.AddWithValue("@" + race, (Results[key][race] < 0.01 || Results[key][race] > 1000000 ? String.Format("{0:#.##e+00}", Results[key][race]) : Convert.ToString(Math.Round(Results[key][race], 2))));
}
cmd.ExecuteNonQuery();
}
#endregion
sheetName = "Parameters$"; // we switch to the Parameters table here
#region DELETE from Parameters
// delete everything from the sheet except the first column (hence checking where F1 <> 'FB1')
// our assumption is that this is the first column. if this changes, this code must change
query = "DELETE FROM [" + sheetName + "] WHERE F1 <> 'FB1'";
cmd = new OleDbCommand(query, cn);
cmd.ExecuteNonQuery();
#endregion
#region Get Column Mapping
// we get the schema and column map for the current sheet/table
dtSchema = getTable(cn, sheetName);
colMap = getColumnMap(dtSchema);
#endregion
#region INSERT Parameters
// use the parameters from the Comparison Data structure to fill out the report parameters sheet
// it is assumed that at least these columns exist. if they do not, this code will error.
query = "INSERT INTO [" + sheetName + "] (" + getColumns(colMap) + ") VALUES (" + getColumnNames(colMap) + ")";
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@FB1", comparisonData.FB1);
cmd.Parameters.AddWithValue("@FB2", comparisonData.FB2);
cmd.Parameters.AddWithValue("@Item", comparisonData.Item);
cmd.Parameters.AddWithValue("@Comparison", comparisonData.Comparison);
cmd.Parameters.AddWithValue("@DNA_Template_Amount", comparisonData.DNAAmount);
cmd.Parameters.AddWithValue("@Created_By", comparisonData.UserName);
cmd.Parameters.AddWithValue("@Prosecutorial_Hypothesis", comparisonData.HpHead);
cmd.Parameters.AddWithValue("@Defense_Hypothesis", comparisonData.HdHead);
cmd.Parameters.AddWithValue("@Deducible", comparisonData.Deducible ? "Yes" : "No");
cmd.Parameters.AddWithValue("@Lab_Kit", comparisonData.LabKitName);
cmd.ExecuteNonQuery();
#endregion
sheetName = "Profiles$"; // we switch to the Parameters table here
#region DELETE from Profiles
// delete everything from the sheet except the first column (hence checking where F1 <> 'Type')
// our assumption is that this is the first column. if this changes, this code must change
query = "DELETE FROM [" + sheetName + "] WHERE F1 <> 'Type'";
cmd = new OleDbCommand(query, cn);
cmd.ExecuteNonQuery();
#endregion
#region ADD COLUMNS from Lab Kit Loci
// get the loci based on the lab kit from the database based on the lab kit used in the lab kit ID
DataTable dtLoci = new Business_Interface().GetLocus(comparisonData.LabKitID);
// get the schema for the table so we can write the locus columns from the database (above)
DataTable dtSchemaTemp = getTable(cn, sheetName);
int firstRow = 0;
for (int i = 0; i < dtSchemaTemp.Columns.Count; i++)
{
if (dtSchemaTemp.Rows[0][i].ToString().Trim() == string.Empty) // if we find the first column with no header we can start inserting loci here
{
firstRow = i;
break;
}
}
// starting at the row we found above we go through and add the first row with the locus names
for (int i = firstRow; i < dtLoci.Rows.Count + firstRow; i++)
{
string locusName = dtLoci.Rows[i - firstRow]["FieldName"].ToString();
query = "UPDATE [" + sheetName + "] SET F" + (i + 1) + "='" + locusName + "' WHERE F1 = 'Type'";
cmd = new OleDbCommand(query, cn);
cmd.ExecuteNonQuery();
}
#endregion
#region Get Column Mapping (after adding new ones)
// we get the schema and column map for the current sheet/table again. this is after we wrote our locus columns from the DB
dtSchema = getTable(cn, sheetName);
colMap = getColumnMap(dtSchema);
#endregion
#region INSERT Evidence
// we use the Evidence dictionary from the ComparisonData structure to fill out the first 3 rows of the profiles table, one per replicate
query = "INSERT INTO [" + sheetName + "] (" + getColumns(colMap) + ") VALUES (" + getColumnNames(colMap) + ")";
int cnt = 1;
// here we start 3 rows from the bottom of the tblAlleles table. this is where the evidence gets printed. 3 is for 3 replicates.
for (int i = dsPrint.Tables["tblAlleles"].Rows.Count - 3; i < dsPrint.Tables["tblAlleles"].Rows.Count; i++)
{
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@Type", "Evidence");
cmd.Parameters.AddWithValue("@FileName", comparisonData.EvidenceFileName ?? string.Empty);
cmd.Parameters.AddWithValue("@ID", cnt++);
foreach (DataRow dr in dtLoci.Rows)
{
string strLocus = dr["FieldName"].ToString();
string strColumnLocus = strLocus.Replace(" ", "_"); // here we replace spaces in column names with underscores so they fit as parameter names to our command (see getColumnMap)
cmd.Parameters.AddWithValue("@" + strColumnLocus, dsPrint.Tables["tblAlleles"].Rows[i][strLocus.ToUpper()].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
#region INSERT Blank Row
// we insert a blank row to separate the Evidence from the Known profiles
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@Type", ' ');
cmd.Parameters.AddWithValue("@FileName", ' ');
cmd.Parameters.AddWithValue("@ID", ' ');
foreach (DataRow dr in dtLoci.Rows)
{
string strLocus = dr["FieldName"].ToString();
string strColumnLocus = strLocus.Replace(" ", "_"); // replace spaces in column names with underscores so they work as parameters (see getColumnMap)
cmd.Parameters.AddWithValue("@" + strColumnLocus, ' ');
}
cmd.ExecuteNonQuery();
#endregion
#region INSERT Known Alleles
// here we iterate through all the Known profiles in the KnownAlleles dictionary of the ComparisonData structure. index starts at 1, and we may have up to 4.
for (int i = 1; i <= comparisonData.KnownsAlleles.Count; i++)
{
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@Type", "Known");
// here we get the known profile uploaded file name (if any) and the custom name entered for this profile (if any)
switch (i)
{
case 1:
cmd.Parameters.AddWithValue("@FileName", comparisonData.Known1FileName ?? string.Empty);
cmd.Parameters.AddWithValue("@ID", comparisonData.Known1Name ?? string.Empty);
break;
case 2:
cmd.Parameters.AddWithValue("@FileName", comparisonData.Known2FileName ?? string.Empty);
cmd.Parameters.AddWithValue("@ID", comparisonData.Known2Name ?? string.Empty);
break;
case 3:
cmd.Parameters.AddWithValue("@FileName", comparisonData.Known3FileName ?? string.Empty);
cmd.Parameters.AddWithValue("@ID", comparisonData.Known3Name ?? string.Empty);
break;
case 4:
cmd.Parameters.AddWithValue("@FileName", comparisonData.Known4FileName ?? string.Empty);
cmd.Parameters.AddWithValue("@ID", comparisonData.Known4Name ?? string.Empty);
break;
}
// we go through each one of the loci in the lab kit, and we take that value from the known profile and add its value to the parameters for this command
foreach (DataRow dr in dtLoci.Rows)
{
string strLocus = dr["FieldName"].ToString();
string strColumnLocus = strLocus.Replace(" ", "_");
cmd.Parameters.AddWithValue("@" + strColumnLocus, comparisonData.KnownsAlleles[i][strLocus.ToUpper()].ToString());
}
cmd.ExecuteNonQuery();
}
#endregion
#region INSERT Blank Row
// we insert a blank row to separate the Known profiles from the Comparison profiles
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@Type", ' ');
cmd.Parameters.AddWithValue("@FileName", ' ');
cmd.Parameters.AddWithValue("@ID", ' ');
foreach (DataRow dr in dtLoci.Rows)
{
string strLocus = dr["FieldName"].ToString();
string strColumnLocus = strLocus.Replace(" ", "_");
cmd.Parameters.AddWithValue("@" + strColumnLocus, ' ');
}
cmd.ExecuteNonQuery();
#endregion
#region INSERT Bulk Comparison Profiles
// here we iterate through the dtKnownProfile table (which actually holds our comparisons) and we add them to the parameters for the insert command
foreach (DataRow dr in dtKnownProfile.Rows)
{
cmd = new OleDbCommand(query, cn);
cmd.Parameters.AddWithValue("@Type", "Comparison");
cmd.Parameters.AddWithValue("@FileName", comparisonData.BulkType == ComparisonData.enBulkType.FromFile ? (comparisonData.FromFileName != string.Empty ? comparisonData.FromFileName : "From File") : comparisonData.BulkType == ComparisonData.enBulkType.LabTypes ? "Lab Types" : "Population");
cmd.Parameters.AddWithValue("@ID", dr["ID"].ToString());
foreach (DataRow drLocus in dtLoci.Rows)
{
string strLocus = drLocus["FieldName"].ToString();
string strColumnLocus = strLocus.Replace(" ", "_"); // here we replace a space with an underscore so the locus name makes a valid column name
cmd.Parameters.AddWithValue("@" + strColumnLocus, dr[strLocus]);
}
cmd.ExecuteNonQuery();
}
#endregion
cn.Close();
cn.Dispose();
GC.Collect();
}
}
// we check for the writing ot have started by waiting for the last write time to be different from the template last write time
while (bulkLastWriteTime >= File.GetLastWriteTime(filename) && ++retries < 300)
{
GC.Collect(); // do not remove this, the ACE OLEDB has GC issues and invoking the collector causes it to flush its buffer to disk
Thread.Sleep(1000);
}
// give ACE OLEDB some time to write
bulkLastWriteTime = File.GetLastWriteTime(filename);
retries = 0;
do
{
Thread.Sleep(15000);
}while (File.GetLastWriteTime(filename) > bulkLastWriteTime && ++retries < 4);
// we check for whether we can send out the file by seeing if we can write to the file
retries = 0;
while (IsFileLocked(new FileInfo(filename)) && ++retries < 10)
{
GC.Collect(); // do not remove this, the ACE OLEDB has GC issues and invoking the collector causes it to flush its buffer to disk
Thread.Sleep(1000);
}
return(filename);
}
