private void timer1_Tick(object sender, EventArgs e)
{
timer1.Enabled = false;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
dataGridView1.Rows.Clear();
dataGridView2.Rows.Clear();
SQLiteCommand query = new SQLiteCommand(select_sql, cnn);
SQLiteDataReader reader = query.ExecuteReader();
DataTable dt1 = null;
DataTable dt2 = null;
dt1 = new DataTable();
dt2 = new DataTable();
dt1.Load(reader);
reader = query.ExecuteReader();
dt2.Load(reader);
query.Dispose();
cnn.Dispose();
dataGridView1.DataSource = dt1;
dataGridView2.DataSource = dt2;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
private void bwDelete_DoWork(object sender, DoWorkEventArgs e)
{
DataGridView dgv = (DataGridView)e.Argument;
SQLiteConnection conn = GGKUtilLib.getDBConnection();
List <DataGridViewRow> rows = new List <DataGridViewRow>();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
foreach (DataGridViewRow row in dgv.SelectedRows)
{
SQLiteCommand upCmd = new SQLiteCommand("DELETE FROM kit_master WHERE kit_no=@kit_no", conn);
upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString());
upCmd.ExecuteNonQuery();
rows.Add(row);
}
//
trans.Commit();
}
this.Invoke(new MethodInvoker(delegate
{
foreach (DataGridViewRow row in rows)
{
dgvEditKit.Rows.Remove(row);
}
}));
}
public void Save()
{
GGKUtilLib.setStatus("Saving ...");
SQLiteConnection conn = GGKUtilLib.getDBConnection();
string sex = "";
string location = "";
using (SQLiteTransaction trans = conn.BeginTransaction())
{
foreach (DataGridViewRow row in dgvEditKit.Rows)
{
SQLiteCommand upCmd = new SQLiteCommand("UPDATE kit_master set name=@name, sex=@sex, disabled=@disabled, x=@x, y=@y WHERE kit_no=@kit_no", conn);
upCmd.Parameters.AddWithValue("@name", row.Cells[1].Value.ToString());
sex = row.Cells[2].Value.ToString();
if (sex == "Unknown")
{
upCmd.Parameters.AddWithValue("@sex", "U");
}
else if (sex == "Male")
{
upCmd.Parameters.AddWithValue("@sex", "M");
}
else if (sex == "Female")
{
upCmd.Parameters.AddWithValue("@sex", "F");
}
if (((bool)row.Cells[3].Value))
{
upCmd.Parameters.AddWithValue("@disabled", "1");
}
else
{
upCmd.Parameters.AddWithValue("@disabled", "0");
}
location = row.Cells[4].Value.ToString();
if (location == "Unknown")
{
upCmd.Parameters.AddWithValue("@x", "0");
upCmd.Parameters.AddWithValue("@y", "0");
}
else
{
upCmd.Parameters.AddWithValue("@x", location.Split(new char[] { ':' })[0]);
upCmd.Parameters.AddWithValue("@y", location.Split(new char[] { ':' })[1]);
}
upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString());
upCmd.ExecuteNonQuery();
}
//
trans.Commit();
}
GGKUtilLib.setStatus("Saved.");
}
private void timer1_Tick(object sender, EventArgs e)
{
timer1.Enabled = false;
DataGridViewCellStyle gray = new DataGridViewCellStyle();
gray.ForeColor = Color.LightGray;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
dgvEditKit.Rows.Clear();
SQLiteCommand query = new SQLiteCommand(@"SELECT kit_no,name,sex,disabled,coalesce(x,0),coalesce(y,0),last_modified FROM kit_master WHERE reference=0 order by last_modified DESC", cnn);
SQLiteDataReader reader = query.ExecuteReader();
string sex = "U";
string xy = null;
while (reader.Read())
{
int new_idx = dgvEditKit.Rows.Add();
DataGridViewRow row = dgvEditKit.Rows[new_idx];
row.Cells[0].Value = reader.GetString(0);
row.Cells[1].Value = reader.GetString(1);
sex = reader.GetString(2);
if (sex == "U")
{
row.Cells[2].Value = "Unknown";
}
else if (sex == "M")
{
row.Cells[2].Value = "Male";
}
else if (sex == "F")
{
row.Cells[2].Value = "Female";
}
if (reader.GetInt16(3) == 1)
{
row.Cells[3].Value = true;
}
else
{
row.Cells[3].Value = false;
}
xy = reader.GetInt16(4).ToString() + ":" + reader.GetInt16(5).ToString();
if (xy == "0:0")
{
xy = "Unknown";
}
row.Cells[4].Value = xy;
}
query.Dispose();
cnn.Dispose();
}
public static void _addParameter(string key, string value, string desc, string read_only)
{
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
SQLiteCommand upCmd = new SQLiteCommand(@"INSERT into ggk_settings(key,value,description,readonly) VALUES (@key,@value,@desc,@readonly)", cnn);
upCmd.Parameters.AddWithValue("@key", key);
upCmd.Parameters.AddWithValue("@value", value);
upCmd.Parameters.AddWithValue("@desc", desc);
upCmd.Parameters.AddWithValue("@readonly", read_only);
upCmd.ExecuteNonQuery();
upCmd.Dispose();
}
public static void saveParameterValue(string key, string value)
{
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
SQLiteCommand upCmd = new SQLiteCommand(@"UPDATE ggk_settings set value=@value WHERE key=@key", cnn);
upCmd.Parameters.AddWithValue("@value", value);
upCmd.Parameters.AddWithValue("@key", key);
upCmd.ExecuteNonQuery();
upCmd.Dispose();
//
settings[key][0] = value;
}
private void timer1_Tick(object sender, EventArgs e)
{
timer1.Enabled = false;
disabled.Clear();
DataGridViewCellStyle gray = new DataGridViewCellStyle();
gray.ForeColor = Color.LightGray;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
dataGridViewOpenKit.Rows.Clear();
string hide = GGKSettings.getParameterValue("Admixture.ReferencePopulations.Hide");
SQLiteCommand query = new SQLiteCommand(select_sql, cnn);
SQLiteDataReader reader = query.ExecuteReader();
while (reader.Read())
{
int new_idx = dataGridViewOpenKit.Rows.Add();
DataGridViewRow row = dataGridViewOpenKit.Rows[new_idx];
row.Cells[0].Value = reader.GetString(0);
row.Cells[1].Value = reader.GetString(1);
row.Cells[2].Value = reader.GetString(3);
if (reader.GetInt16(2) == 1)
{
row.DefaultCellStyle = gray;
disabled.Add(reader.GetString(0));
}
}
query.Dispose();
cnn.Dispose();
if (dataGridViewOpenKit.Rows.Count > 0)
{
dataGridViewOpenKit.CurrentCell = dataGridViewOpenKit.Rows[0].Cells[0];
kitLbl.Text = dataGridViewOpenKit.SelectedRows[0].Cells[0].Value.ToString();
}
if (dataGridViewOpenKit.Rows.Count == 0)
{
MessageBox.Show("There are no kits available to open.", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.Close();
}
}
private void bwDelete_DoWork(object sender, DoWorkEventArgs e)
{
string kit_no = (string)e.Argument;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
try
{
SQLiteCommand upCmd = new SQLiteCommand(@"DELETE from kit_master where kit_no=@kit_no", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.ExecuteNonQuery();
this.Invoke(new MethodInvoker(delegate { this.Close(); }));
}
catch (Exception err)
{
this.Invoke(new MethodInvoker(delegate
{
MessageBox.Show("Unable to delete kit " + kit_no + "\r\nTechnical Error: " + err.Message);
GGKUtilLib.setStatus("Unable to delete kit " + kit_no);
}));
}
}
public void Disable()
{
GGKUtilLib.setStatus("Disabling ...");
SQLiteConnection conn = GGKUtilLib.getDBConnection();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
foreach (DataGridViewRow row in dgvEditKit.SelectedRows)
{
SQLiteCommand upCmd = new SQLiteCommand("UPDATE kit_master set disabled=@disabled WHERE kit_no=@kit_no", conn);
upCmd.Parameters.AddWithValue("@name", row.Cells[1].Value.ToString());
upCmd.Parameters.AddWithValue("@disabled", "1");
upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString());
upCmd.ExecuteNonQuery();
row.Cells[3].Value = true;
}
//
trans.Commit();
}
GGKUtilLib.setStatus("Disabled.");
}
public void Enable()
{
string kit_no = txtKit.Text;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
using (SQLiteTransaction dbTrans = cnn.BeginTransaction())
{
try
{
SQLiteCommand upCmd = new SQLiteCommand(@"UPDATE kit_master set disabled=0 where kit_no=@kit_no", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.ExecuteNonQuery();
dbTrans.Commit();
doControlActivities(false);
}
catch (Exception err)
{
dbTrans.Rollback();
MessageBox.Show("Unable to enable kit " + kit_no + "\r\nTechnical Error: " + err.Message);
GGKUtilLib.setStatus("Unable to enable kit " + kit_no);
}
}
}
private void bwPhaseVisualizer_DoWork(object sender, DoWorkEventArgs e)
{
DataTable dt_existing = GGKUtilLib.QueryDB("select segment_image,segment_xml from cmp_phased where phased_kit='" + phased_kit + "' and match_kit='" + unphased_kit + "' and chromosome='" + chromosome + "' and start_position=" + start_position + " and end_position=" + end_position);
if (dt_existing.Rows.Count > 0)
{
object[] o = dt_existing.Rows[0].ItemArray;
string xml = o[1].ToString();
dt = new DataTable();
MemoryStream ms = new MemoryStream(Encoding.ASCII.GetBytes(xml));
dt.ReadXml(ms);
this.Invoke(new MethodInvoker(delegate
{
dgvSegment.DataSource = dt;
dgvSegment.Columns[0].HeaderText = "Position";
dgvSegment.Columns[1].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(unphased_kit));
dgvSegment.Columns[2].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Paternal)";
dgvSegment.Columns[3].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Maternal)";
dgvSegment.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[0].ReadOnly = true;
dgvSegment.Columns[1].ReadOnly = true;
dgvSegment.Columns[2].ReadOnly = true;
dgvSegment.Columns[3].ReadOnly = true;
}));
byte[] image_array = (byte[])o[0];
Image img = GGKUtilLib.byteArrayToImage(image_array);
this.Invoke(new MethodInvoker(delegate
{
original = new Bitmap(img, 600, 150);
pbSegment.Image = original;
}));
}
else
{
dt = GGKUtilLib.QueryDB("select a.position,a.genotype,p.paternal_genotype,p.maternal_genotype from kit_autosomal a,kit_phased p where a.kit_no='" + unphased_kit + "' and a.position>" + start_position + " and a.position<" + end_position + " and a.chromosome='" + chromosome + "' and p.rsid=a.rsid and p.kit_no='" + phased_kit + "' order by a.position");
if (dt.Rows.Count > 0)
{
if (this.IsHandleCreated)
{
this.Invoke(new MethodInvoker(delegate
{
dgvSegment.DataSource = dt;
dgvSegment.Columns[0].HeaderText = "Position";
dgvSegment.Columns[1].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(unphased_kit));
dgvSegment.Columns[2].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Paternal)";
dgvSegment.Columns[3].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Maternal)";
dgvSegment.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvSegment.Columns[0].ReadOnly = true;
dgvSegment.Columns[1].ReadOnly = true;
dgvSegment.Columns[2].ReadOnly = true;
dgvSegment.Columns[3].ReadOnly = true;
}));
Image img = GGKUtilLib.getPhasedSegmentImage(dt, chromosome);
this.Invoke(new MethodInvoker(delegate
{
original = img;
pbSegment.Image = img;
}));
}
dt.TableName = "cmp_phased";
StringBuilder sb = new StringBuilder();
StringWriter w = new StringWriter(sb);
dt.WriteXml(w, XmlWriteMode.WriteSchema);
string segment_xml = sb.ToString();
SQLiteConnection conn = GGKUtilLib.getDBConnection();
SQLiteCommand cmd = new SQLiteCommand("INSERT INTO cmp_phased(phased_kit,match_kit,chromosome,start_position,end_position,segment_image,segment_xml) VALUES (@phased_kit,@match_kit,@chromosome,@start_position,@end_position,@segment_image,@segment_xml)", conn);
cmd.Parameters.AddWithValue("@phased_kit", phased_kit);
cmd.Parameters.AddWithValue("@match_kit", unphased_kit);
cmd.Parameters.AddWithValue("@chromosome", chromosome);
cmd.Parameters.AddWithValue("@start_position", start_position);
cmd.Parameters.AddWithValue("@end_position", end_position);
byte[] image_bytes = GGKUtilLib.imageToByteArray(original);
cmd.Parameters.Add("@segment_image", DbType.Binary, image_bytes.Length).Value = image_bytes;
cmd.Parameters.AddWithValue("@segment_xml", segment_xml);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
private void bwPhaseVisualizer_DoWork(object sender, DoWorkEventArgs e)
{
DataTable dt2 = GGKUtilLib.QueryDB("select distinct kit_no from kit_phased");
string phased_kit = null;
string unphased_kit = null;
string chromosome = null;
string start_position = null;
string end_position = null;
int percent = 0;
foreach (DataRow row in dt2.Rows)
{
if (bwPhaseVisualizer.CancellationPending)
{
break;
}
phased_kit = row.ItemArray[0].ToString();
percent = dt2.Rows.IndexOf(row) * 100 / dt2.Rows.Count;
bwPhaseVisualizer.ReportProgress(percent, "Phased Segments for kit #" + phased_kit + " (" + GGKUtilLib.getKitName(phased_kit) + ") - Processing ...");
DataTable dt3 = GGKUtilLib.QueryDB("select unphased_kit,chromosome,start_position,end_position FROM (select kit1'unphased_kit',chromosome,start_position,end_position from cmp_autosomal where kit2='" + phased_kit + "' UNION select kit2'unphased_kit',chromosome,start_position,end_position from cmp_autosomal where kit1='" + phased_kit + "') order by cast(chromosome as integer),start_position");
foreach (DataRow row3 in dt3.Rows)
{
if (bwPhaseVisualizer.CancellationPending)
{
break;
}
unphased_kit = row3.ItemArray[0].ToString();
chromosome = row3.ItemArray[1].ToString();
start_position = row3.ItemArray[2].ToString();
end_position = row3.ItemArray[3].ToString();
DataTable exists = GGKUtilLib.QueryDB("select * from cmp_phased where phased_kit='" + phased_kit + "' and match_kit='" + unphased_kit + "' and chromosome='" + chromosome + "' and start_position=" + start_position + " and end_position=" + end_position);
if (exists.Rows.Count > 0)
{
//already exists...
if (!redo_visual)
{
bwPhaseVisualizer.ReportProgress(percent, "Segment [" + GGKUtilLib.getKitName(phased_kit) + ":" + GGKUtilLib.getKitName(unphased_kit) + "] Chr " + chromosome + ": " + start_position + "-" + end_position + ", Already Processed. Skipping ...");
continue;
}
else
{
GGKUtilLib.UpdateDB("DELETE from cmp_phased where phased_kit='" + phased_kit + "'");
}
}
bwPhaseVisualizer.ReportProgress(percent, "Segment [" + GGKUtilLib.getKitName(phased_kit) + ":" + GGKUtilLib.getKitName(unphased_kit) + "] Chr " + chromosome + ": " + start_position + "-" + end_position + ", Processing ...");
DataTable dt = GGKUtilLib.QueryDB("select a.position,a.genotype,p.paternal_genotype,p.maternal_genotype from kit_autosomal a,kit_phased p where a.kit_no='" + unphased_kit + "' and a.position>" + start_position + " and a.position<" + end_position + " and a.chromosome='" + chromosome + "' and p.rsid=a.rsid and p.kit_no='" + phased_kit + "' order by a.position");
if (dt.Rows.Count > 0)
{
if (bwPhaseVisualizer.CancellationPending)
{
break;
}
Image img = GGKUtilLib.getPhasedSegmentImage(dt, chromosome);
dt.TableName = "cmp_phased";
StringBuilder sb = new StringBuilder();
StringWriter w = new StringWriter(sb);
dt.WriteXml(w, XmlWriteMode.WriteSchema);
string segment_xml = sb.ToString();
SQLiteConnection conn = GGKUtilLib.getDBConnection();
SQLiteCommand cmd = new SQLiteCommand("INSERT INTO cmp_phased(phased_kit,match_kit,chromosome,start_position,end_position,segment_image,segment_xml) VALUES (@phased_kit,@match_kit,@chromosome,@start_position,@end_position,@segment_image,@segment_xml)", conn);
cmd.Parameters.AddWithValue("@phased_kit", phased_kit);
cmd.Parameters.AddWithValue("@match_kit", unphased_kit);
cmd.Parameters.AddWithValue("@chromosome", chromosome);
cmd.Parameters.AddWithValue("@start_position", start_position);
cmd.Parameters.AddWithValue("@end_position", end_position);
byte[] image_bytes = GGKUtilLib.imageToByteArray(img);
cmd.Parameters.Add("@segment_image", DbType.Binary, image_bytes.Length).Value = image_bytes;
cmd.Parameters.AddWithValue("@segment_xml", segment_xml);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}
private void bwPhasing_DoWork(object sender, DoWorkEventArgs e)
{
if (father_kit != "Unknown" && mother_kit != "Unknown")
{
dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",COALESCE(f.[genotype],'--')\"Father\",COALESCE(m.[genotype],'--')\"Mother\",''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal f,kit_autosomal m on f.rsid=c.rsid AND m.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND f.kit_no='" + father_kit + "' AND m.[kit_no]='" + mother_kit + "' ORDER BY cast(c.chromosome as integer),c.position");
}
else if (father_kit != "Unknown" && mother_kit == "Unknown")
{
dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",COALESCE(f.[genotype],'--')\"Father\",'--'\"Mother\",''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal f on f.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND f.kit_no='" + father_kit + "' ORDER BY cast(c.chromosome as integer),c.position");
}
else if (father_kit == "Unknown" && mother_kit != "Unknown")
{
dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",'--'\"Father\",COALESCE(m.[genotype],'--')\"Mother\" ,''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal m on m.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND m.kit_no='" + mother_kit + "' ORDER BY cast(c.chromosome as integer),c.position");
}
// after phasing...
string child = null;
string father = null;
string mother = null;
string phased_paternal = null;
string phased_maternal = null;
object[] o = null;
string nc = "N";
bool amb = false;
foreach (DataRow row in dt.Rows)
{
o = row.ItemArray;
phased_paternal = "";
phased_maternal = "";
child = o[3].ToString();
father = o[4].ToString();
mother = o[5].ToString();
if (child.Length == 1)
{
child = child + child;
}
//check
if ((father.Replace(child[0].ToString(), "").Replace(child[1].ToString(), "") == father || mother.Replace(child[0].ToString(), "").Replace(child[1].ToString(), "") == mother) && o[1].ToString() != "X" && father != "--" && mother != "--" && child != "--")
{
mutatedRow.Add(dt.Rows.IndexOf(row));
}
amb = false;
if (father == child && child[0] != child[1] && mother == "--")
{
amb = true;
}
else if (mother == child && child[0] != child[1] && father == "--")
{
amb = true;
}
else if (father == child && child[0] != child[1] && mother == child)
{
amb = true;
}
if (amb)
{
ambiguousRow.Add(dt.Rows.IndexOf(row));
nc = getNucleotideCode(child[0].ToString(), child[1].ToString());
row.SetField(6, nc);
row.SetField(7, nc);
phased_paternal = nc;
phased_maternal = nc;
continue;
}
if (child == "--" || child == "??")
{
if (father[0] == father[1] && father == mother && o[1].ToString() != "X")
{
row.SetField(6, father[0].ToString());
row.SetField(7, father[0].ToString());
phased_paternal = father[0].ToString();
phased_maternal = father[0].ToString();
continue;
}
}
if (male && o[1].ToString() == "X")
{
child = child[0].ToString();
if (child == "-" && mother != "--")
{
row.SetField(6, "");
row.SetField(7, mother[0].ToString());
phased_paternal = "";
phased_maternal = mother[0].ToString();
continue;
}
}
else
{
if (child[0] == child[1] && child[0] != '-' && child[0] != '?')
{
row.SetField(6, child[0].ToString());
row.SetField(7, child[0].ToString());
phased_paternal = child[0].ToString();
phased_maternal = child[0].ToString();
continue;
}
}
if (o[1].ToString() != "X")
{
autosomalSingleSNPPhase(child, father, mother, row, ref phased_paternal, ref phased_maternal);
}
else if (o[1].ToString() == "X")
{
if (male)
{
row.SetField(3, child[0].ToString());
row.SetField(4, "");
row.SetField(6, "");
row.SetField(7, child[0].ToString());
phased_paternal = "";
phased_maternal = child[0].ToString();
}
else
{
autosomalSingleSNPPhase(child, father, mother, row, ref phased_paternal, ref phased_maternal);
}
}
if (phased_paternal == "" && phased_maternal != "")
{
phased_paternal = child.Replace(phased_maternal, "");
if (phased_paternal.Length > 0)
{
phased_paternal = phased_paternal[0].ToString();
}
row.SetField(6, phased_paternal);
}
if (phased_maternal == "" && phased_paternal != "")
{
phased_maternal = child.Replace(phased_paternal, "");
if (phased_maternal.Length > 0)
{
phased_maternal = phased_maternal[0].ToString();
}
row.SetField(7, phased_maternal);
}
}
// save to kit_phased
string rsid = null;
string chromosome = null;
string position = null;
bwPhasing.ReportProgress(-1, "Saving Phased Kit " + child_kit + " ...");
SQLiteConnection conn = GGKUtilLib.getDBConnection();
SQLiteCommand cmd = new SQLiteCommand("DELETE FROM kit_phased where kit_no=@kit_no", conn);
cmd.Parameters.AddWithValue("@kit_no", child_kit);
cmd.ExecuteNonQuery();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
foreach (DataRow row in dt.Rows)
{
o = row.ItemArray;
rsid = o[0].ToString();
chromosome = o[1].ToString();
position = o[2].ToString();
phased_paternal = o[6].ToString();
phased_maternal = o[7].ToString();
cmd = new SQLiteCommand("INSERT OR REPLACE INTO kit_phased(kit_no,rsid,chromosome,position,paternal_genotype,maternal_genotype,paternal_kit_no,maternal_kit_no) VALUES(@kit_no,@rsid,@chromosome,@position,@paternal_genotype,@maternal_genotype,@paternal_kit_no,@maternal_kit_no)", conn);
cmd.Parameters.AddWithValue("@kit_no", child_kit);
cmd.Parameters.AddWithValue("@rsid", rsid);
cmd.Parameters.AddWithValue("@chromosome", chromosome);
cmd.Parameters.AddWithValue("@position", position);
cmd.Parameters.AddWithValue("@paternal_genotype", phased_paternal);
cmd.Parameters.AddWithValue("@maternal_genotype", phased_maternal);
if (father_kit == "Unknown")
{
cmd.Parameters.AddWithValue("@paternal_kit_no", "");
}
else
{
cmd.Parameters.AddWithValue("@paternal_kit_no", father_kit);
}
if (mother_kit == "Unknown")
{
cmd.Parameters.AddWithValue("@maternal_kit_no", "");
}
else
{
cmd.Parameters.AddWithValue("@maternal_kit_no", mother_kit);
}
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
private void bwPopuate_DoWork(object sender, DoWorkEventArgs e)
{
string kit = (string)e.Argument;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
//kit master
SQLiteCommand query = new SQLiteCommand(@"SELECT name, sex from kit_master where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
SQLiteDataReader reader = query.ExecuteReader();
string sex = "U";
if (reader.Read())
{
this.Invoke(new MethodInvoker(delegate
{
txtName.Text = reader.GetString(0);
sex = reader.GetString(1);
if (sex == "U")
{
cbSex.SelectedIndex = 0;
}
else if (sex == "M")
{
cbSex.SelectedIndex = 1;
}
else if (sex == "F")
{
cbSex.SelectedIndex = 2;
}
}));
}
reader.Close();
query.Dispose();
// kit autosomal - RSID,Chromosome,Position,Genotypoe
query = new SQLiteCommand(@"SELECT rsid 'RSID',chromosome 'Chromosome',position 'Position',genotype 'Genotype' from kit_autosomal where kit_no=@kit_no order by chromosome,position", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
this.Invoke(new MethodInvoker(delegate
{
dataGridViewAutosomal.Rows.Clear();
dataGridViewAutosomal.Columns.Clear();
dataGridViewAutosomal.DataSource = dt;
dataGridViewAutosomal.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[0]).MaxInputLength = 20;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[1]).MaxInputLength = 2;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[2]).MaxInputLength = 15;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[3]).MaxInputLength = 2;
}));
reader.Close();
query.Dispose();
// kit - ysnp
query = new SQLiteCommand(@"SELECT ysnps from kit_ysnps where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
if (reader.Read())
{
ysnps = reader.GetString(0);
}
reader.Close();
query.Dispose();
this.Invoke(new MethodInvoker(delegate
{
textBoxYDNA.Text = ysnps;
}));
// kit - ystr
query = new SQLiteCommand(@"SELECT marker,value from kit_ystr where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
string marker = null;
string value = null;
Dictionary <string, string> ystr_dict = new Dictionary <string, string>();
while (reader.Read())
{
marker = reader.GetString(0);
value = reader.GetString(1);
ystr_dict.Add(marker, value);
}
reader.Close();
query.Dispose();
this.Invoke(new MethodInvoker(delegate
{
//
//YDNA12
for (int i = 0; i < ydna12.Length; i++)
{
if (ystr_dict.ContainsKey(ydna12[i]))
{
dgvy12.Rows[i].Cells[1].Value = ystr_dict[ydna12[i]];
ystr_dict.Remove(ydna12[i]);
}
}
//YDNA25
for (int i = 0; i < ydna25.Length; i++)
{
if (ystr_dict.ContainsKey(ydna25[i]))
{
dgvy25.Rows[i].Cells[1].Value = ystr_dict[ydna25[i]];
ystr_dict.Remove(ydna25[i]);
}
}
//YDNA37
for (int i = 0; i < ydna37.Length; i++)
{
if (ystr_dict.ContainsKey(ydna37[i]))
{
dgvy37.Rows[i].Cells[1].Value = ystr_dict[ydna37[i]];
ystr_dict.Remove(ydna37[i]);
}
}
//YDNA67
for (int i = 0; i < ydna67.Length; i++)
{
if (ystr_dict.ContainsKey(ydna67[i]))
{
dgvy67.Rows[i].Cells[1].Value = ystr_dict[ydna67[i]];
ystr_dict.Remove(ydna67[i]);
}
}
//YDNA111
for (int i = 0; i < ydna111.Length; i++)
{
if (ystr_dict.ContainsKey(ydna111[i]))
{
dgvy111.Rows[i].Cells[1].Value = ystr_dict[ydna111[i]];
ystr_dict.Remove(ydna111[i]);
}
}
//YDNA MISC
dgvymisc.Rows.Clear();
foreach (string str in ystr_dict.Keys)
{
dgvymisc.Rows.Add(new string[] { str, ystr_dict[str] });
}
}));
// kit - mtdna
query = new SQLiteCommand(@"SELECT mutations,fasta from kit_mtdna where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
string fasta = null;
if (reader.Read())
{
this.Invoke(new MethodInvoker(delegate
{
textBoxMtDNA.Text = reader.GetString(0);
fasta = reader.GetString(1);
if (fasta != null)
{
tbFASTA.Text = fasta;
}
else
{
tbFASTA.Text = "";
}
}));
}
reader.Close();
query.Dispose();
}
private void bwSave_DoWork(object sender, DoWorkEventArgs e)
{
save_success = false;
Object[] args = (Object[])e.Argument;
string kit_no = args[0].ToString();
string name = args[1].ToString();
DataGridViewRowCollection rows = (DataGridViewRowCollection)args[2];
string ysnps_list = args[3].ToString();
string mutations = args[4].ToString();
DataGridViewRowCollection[] dgvy_rows = (DataGridViewRowCollection[])args[5];
string sex = args[6].ToString();
string fasta = args[7].ToString();
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
try
{
SQLiteCommand upCmd = null;
//kit master
string kit_name = GGKUtilLib.getKitName(kit_no);
if (kit_name == "Unknown")
{
// new kit
upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_master(kit_no, name, sex)values(@kit_no,@name,@sex)", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.Parameters.AddWithValue("@name", name);
upCmd.Parameters.AddWithValue("@sex", sex[0].ToString());
upCmd.ExecuteNonQuery();
}
else
{
// kit exists
upCmd = new SQLiteCommand(@"UPDATE kit_master SET name=@name, sex=@sex WHERE kit_no=@kit_no", cnn);
upCmd.Parameters.AddWithValue("@name", name);
upCmd.Parameters.AddWithValue("@sex", sex[0].ToString());
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.ExecuteNonQuery();
}
upCmd.Dispose();
bwSave.ReportProgress(35, "Saving Autosomal data ...");
upCmd = new SQLiteCommand(@"DELETE from kit_autosomal where kit_no=@kit_no", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.ExecuteNonQuery();
//kit autosomal
upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_autosomal(kit_no, rsid,chromosome,position,genotype)values(@kit_no,@rsid,@chromosome,@position,@genotype)", cnn);
using (var transaction = cnn.BeginTransaction())
{
bool incomplete = false;
foreach (DataGridViewRow row in rows)
{
if (row.IsNewRow)
{
continue;
}
incomplete = false;
for (int c = 0; c < row.Cells.Count; c++)
{
if (row.Cells[c].Value == null)
{
incomplete = true;
break;
}
else if (row.Cells[c].Value.ToString().Trim() == "")
{
incomplete = true;
break;
}
}
if (incomplete)
{
continue;
}
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.Parameters.AddWithValue("@rsid", row.Cells[0].Value.ToString());
upCmd.Parameters.AddWithValue("@chromosome", row.Cells[1].Value.ToString());
upCmd.Parameters.AddWithValue("@position", row.Cells[2].Value.ToString());
upCmd.Parameters.AddWithValue("@genotype", row.Cells[3].Value.ToString());
upCmd.ExecuteNonQuery();
}
transaction.Commit();
}
upCmd.Dispose();
bwSave.ReportProgress(75, "Saving Y-SNPs ...");
if (ysnps_list.Trim() != "")
{
//kit ysnps
upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_ysnps(kit_no, ysnps) values (@kit_no,@ysnps)", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.Parameters.AddWithValue("@ysnps", ysnps_list);
upCmd.ExecuteNonQuery();
upCmd.Dispose();
}
//kit ystr
bwSave.ReportProgress(80, "Saving Y-STR Values ...");
upCmd = new SQLiteCommand(@"DELETE from kit_ystr where kit_no=@kit_no", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.ExecuteNonQuery();
upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_ystr(kit_no, marker, value)values(@kit_no,@marker,@value)", cnn);
using (var transaction = cnn.BeginTransaction())
{
foreach (DataGridViewRowCollection row_collection in dgvy_rows)
{
foreach (DataGridViewRow row in row_collection)
{
if (row.IsNewRow)
{
continue;
}
if (row.Cells[1].Value.ToString().Trim() == "")
{
continue;
}
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.Parameters.AddWithValue("@marker", row.Cells[0].Value.ToString());
upCmd.Parameters.AddWithValue("@value", row.Cells[1].Value.ToString());
upCmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
upCmd.Dispose();
//kit mtdna
if (mutations.Trim() != "" || fasta.Trim() != "")
{
bwSave.ReportProgress(90, "Saving mtDNA mutations ...");
upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_mtdna(kit_no, mutations,fasta)values(@kit_no,@mutations,@fasta)", cnn);
upCmd.Parameters.AddWithValue("@kit_no", kit_no);
upCmd.Parameters.AddWithValue("@mutations", mutations);
upCmd.Parameters.AddWithValue("@fasta", fasta);
upCmd.ExecuteNonQuery();
}
bwSave.ReportProgress(100, "Saved");
save_success = true;
}
catch (Exception err)
{
bwSave.ReportProgress(-1, "Not Saved. Tech Details: " + err.Message);
MessageBox.Show("Not Saved. Techical Details: " + err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
cnn.Dispose();
}