public void lnkDownload_Click(object sender, EventArgs e)
{
string strErrMsg = string.Empty;
string connString = string.Empty;
string extension = ".xlsx";
// save file
string filename = DateTime.Now.Ticks.ToString() + extension;
string sheetName = "Profiles$";
File.Copy(Server.MapPath("~/Reports/ProfileDownloadBlank.xlsx"), Server.MapPath("~/Admin/Upload/" + filename));
// create connection string
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 12.0;HDR=No';");
DataTable dt = bi.GetKnown_Profile("Lab Types", Guid.Empty);
dt.Columns["ID"].SetOrdinal(0);
// open the file
using (OleDbConnection cn = new OleDbConnection(connString))
{
cn.Open();
string query = string.Empty;
DataTable dtTables = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtTables.Rows.Count == 1)
{
sheetName = (string)dtTables.Rows[0]["TABLE_NAME"];
}
query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn);
DataTable dt2 = new DataTable();
adapter.Fill(dt2);
dt2.Columns["ID"].SetOrdinal(1);
int x = 1;
foreach (DataColumn dc in dt.Columns)
{
query = "UPDATE [" + sheetName + "] SET F" + x.ToString() + " = '" + dc.ColumnName + "'";
OleDbCommand cmd = new OleDbCommand(query, cn);
cmd.ExecuteNonQuery();
x++;
}
foreach (DataRow dr in dt.Rows)
{
OleDbCommand cmd = new OleDbCommand();
string cmdText = "INSERT INTO [" + sheetName + "] (";
for (int i = 1; i <= dt.Columns.Count; i++)
{
cmdText += "F" + i.ToString() + ",";
}
cmdText = cmdText.Substring(0, cmdText.Length - 1);
cmdText += ") VALUES (";
int j = 1;
foreach (DataColumn dc in dt.Columns)
{
cmdText += "@F" + j.ToString() + ",";
j++;
cmd.Parameters.AddWithValue("@F" + j.ToString(), dr[dc.ColumnName].ToString());
}
cmdText = cmdText.Substring(0, cmdText.Length - 1);
cmdText += ")";
cmd.CommandText = cmdText;
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
// read file
query = "SELECT * FROM [" + sheetName + "]";
adapter = new OleDbDataAdapter(query, cn);
dt = new DataTable();
adapter.Fill(dt);
cn.Close();
GC.Collect();
}
// we check for whether we can send out the file by seeing if we can write to the file
while (IsFileLocked(new FileInfo(Server.MapPath("~/Admin/Upload/" + filename))))
{
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);
}
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=LabTypes.xlsx");
Response.BinaryWrite(File.ReadAllBytes(Server.MapPath("~/Admin/Upload/" + filename)));
Response.End();
File.Delete(filename);
}
private void StartBulkSearch()
{
// special string signifying a test
bool isTest = comparisonData.Comparison == "\tes\t";
string testId = isTest ? comparisonData.FB1 : string.Empty;
if (!isTest)
{
Log.Info(strUserId, comparisonData.ToString(), "Comparison Start", strHp_Head + " / " + strHd_Head);
}
// frequencies cache
DataTable dtFrequencies = new Database().GetFrequencyData();
// create denominator cache
Dictionary <string, Dictionary <string, double> > perRaceLocusDenominatorCache = new Dictionary <string, Dictionary <string, double> >();
foreach (DataRow drEth in new Database().getAllEthnics().Rows)
{
perRaceLocusDenominatorCache.Add(drEth["EthnicName"].ToString(), new Dictionary <string, double>());
}
foreach (string key in perRaceLocusDenominatorCache.Keys)
{
foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows)
{
perRaceLocusDenominatorCache[key].Add(drLoc["LocusName"].ToString().ToUpper(), double.NaN);
}
}
// end create denominator cache
// dropout cache
Dictionary <int, Dictionary <string, Dictionary <string, float> > > perReplicateDropOutCache = new Dictionary <int, Dictionary <string, Dictionary <string, float> > >();
for (int i = 0; i < 3; i++)
{
foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows)
{
if (!perReplicateDropOutCache.ContainsKey(i))
{
perReplicateDropOutCache.Add(i, new Dictionary <string, Dictionary <string, float> >());
}
perReplicateDropOutCache[i].Add(drLoc["LocusName"].ToString().ToUpper(), new Dictionary <string, float>());
}
}
// end dropout cache
// start frequency cache
Dictionary <string, Dictionary <string, Dictionary <string, float> > > perRaceLocusFrequencyCache = new Dictionary <string, Dictionary <string, Dictionary <string, float> > >();
foreach (DataRow drEth in new Database().getAllEthnics().Rows)
{
perRaceLocusFrequencyCache.Add(drEth["EthnicName"].ToString(), new Dictionary <string, Dictionary <string, float> >());
}
foreach (string key in perRaceLocusDenominatorCache.Keys)
{
foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows)
{
perRaceLocusFrequencyCache[key].Add(drLoc["LocusName"].ToString().ToUpper(), new Dictionary <string, float>());
}
}
// end frequency cache
// start bulk permutation caches
// all we need to do is send these in as not null
Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > > numeratorPermutationCache = new Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > >();
Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > > denominatorPermutationCache = new Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > >();
// end bulk permutation cache
Dictionary <string, Dictionary <string, float> > Results = new Dictionary <string, Dictionary <string, float> >();
// gets "lab types," "population," or "from file" comparison list from the database
DataTable dtKnownProfile = bi.GetKnown_Profile(strLab_Popultn_Type, comparisonData.FromFileGuid);
for (int i = 0; i < dtKnownProfile.Rows.Count; i++)
{
try
{
// reads datarow to a dictionary
comparisonData.ComparisonAlleles = ReadBulkComparison(dtKnownProfile, i);
FST.Common.Comparison comparison = new Comparison(comparisonData);
Dictionary <string, float> Result = comparison.DoCompare( // caches
dtFrequencies,
perRaceLocusDenominatorCache,
perRaceLocusFrequencyCache,
perReplicateDropOutCache,
numeratorPermutationCache,
denominatorPermutationCache
);
// if we're testing, write test results. otherwise, add results to the Results dictionary (gets passed to the bulk printer below)
if (isTest)
{
bi.WriteTestResults(testId, dtKnownProfile.Rows[i]["ID"].ToString(), Result["Asian"].ToString(), Result["Black"].ToString(), Result["Caucasian"].ToString(), Result["Hispanic"].ToString());
}
else
{
Results.Add(dtKnownProfile.Rows[i]["ID"].ToString(), Result);
}
}
catch
{
continue;
}
}
if (!isTest)
{
strReportFilePath = FSTService.Instance.BulkPrinter.Print(Results, dtKnownProfile, comparisonData);
}
if (!isTest)
{
Log.Info(strUserId, comparisonData.ToString(), "Comparison End", strHp_Head + " / " + strHd_Head);
}
}
