public static void PopulateSampleTypePrepMeth(SqlConnection conn, SqlTransaction trans, TreeNode tnode, ListBox lb, ListBox lbInherited)
{
Guid sampleTypeId = Guid.Parse(tnode.Name);
string query = @"
select pm.id, pm.name from preparation_method pm
inner join sample_type_x_preparation_method stpm on stpm.preparation_method_id = pm.id
inner join sample_type st on stpm.sample_type_id = st.id and st.id = @sample_type_id
order by name";
lb.Items.Clear();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId)))
{
while (reader.Read())
{
Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lb.Items.Add(st);
}
}
lbInherited.Items.Clear();
while (tnode.Parent != null)
{
tnode = tnode.Parent;
sampleTypeId = Guid.Parse(tnode.Name);
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId)))
{
while (reader.Read())
{
Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lbInherited.Items.Add(st);
}
}
}
}
private static void AddSampleTypeChildren(TreeNodeCollection tnc, List <Lemma <Guid, string> > list)
{
foreach (TreeNode tn in tnc)
{
Lemma <Guid, string> st = new Lemma <Guid, string>(Guid.Parse(tn.Name), tn.Text + " -> " + tn.Tag.ToString());
list.Add(st);
AddSampleTypeChildren(tn.Nodes, list);
}
}
public static void PopulatePrepMethAnalMeths(SqlConnection conn, SqlTransaction trans, Guid preparationMethodId, ListBox lb)
{
lb.Items.Clear();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis_methods_for_preparation_method", CommandType.StoredProcedure,
new SqlParameter("@preparation_method_id", preparationMethodId)))
{
while (reader.Read())
{
Lemma <Guid, string> n = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lb.Items.Add(n);
}
}
}
public static void PopulateSampleComponents(SqlConnection conn, Guid sampleTypeId, ListBox lb)
{
lb.Items.Clear();
using (SqlDataReader reader = DB.GetDataReader(conn, null, "csp_select_sample_components_for_sample_type", CommandType.StoredProcedure,
new SqlParameter("@sample_type_id", sampleTypeId)))
{
while (reader.Read())
{
Lemma <Guid, string> sampleComponent = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lb.Items.Add(sampleComponent);
}
}
}
public static void PopulateSampleTypePrepMeth(SqlConnection conn, Guid sampleTypeId, Guid labId, ComboBox cbox)
{
List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >();
string query = @"
select pm.id, pm.name_short as 'name' from preparation_method pm
inner join sample_type_x_preparation_method stpm on stpm.preparation_method_id = pm.id
inner join laboratory_x_preparation_method lxpm on lxpm.preparation_method_id = pm.id and lxpm.laboratory_id = @laboratory_id
inner join sample_type st on stpm.sample_type_id = st.id and st.id = @sample_type_id
where pm.instance_status_id <= 1
order by name";
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text,
new SqlParameter("@sample_type_id", sampleTypeId),
new SqlParameter("@laboratory_id", labId)))
{
while (reader.Read())
{
Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
list.Add(st);
}
}
while (true)
{
sampleTypeId = DB.GetSampleTypeParentId(conn, null, sampleTypeId);
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text,
new SqlParameter("@sample_type_id", sampleTypeId),
new SqlParameter("@laboratory_id", labId)))
{
while (reader.Read())
{
Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
list.Add(st);
}
}
if (sampleTypeId == Guid.Empty)
{
break;
}
}
cbox.DisplayMember = "name";
cbox.ValueMember = "id";
cbox.DataSource = list;
}
private void FormSampTypeXPrepMeth_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
var methArr = from item in MethodsAbove select "'" + item + "'";
string smeth = string.Join(",", methArr);
string query;
if (String.IsNullOrEmpty(smeth))
{
query = "select id, name from preparation_method where instance_status_id < 2 order by name";
}
else
{
query = "select id, name from preparation_method where instance_status_id < 2 and id not in(" + smeth + ") order by name";
}
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text))
{
lbPrepMeth.Items.Clear();
while (reader.Read())
{
var pm = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lbPrepMeth.Items.Add(pm);
}
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
private void FormPrepMethXAnalMeth_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
var analMethArr = from item in ExistingAnalysisMethods select "'" + item + "'";
string sanalmeth = string.Join(",", analMethArr);
string query;
if (String.IsNullOrEmpty(sanalmeth))
{
query = "select id, name from analysis_method order by name";
}
else
{
query = "select id, name from analysis_method where id not in(" + sanalmeth + ") order by name";
}
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text))
{
lbAnalysisMethods.Items.Clear();
while (reader.Read())
{
var am = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lbAnalysisMethods.Items.Add(am);
}
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
private void FormAccountXRoles_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
var roleArr = from item in mExistingRoleIds select "'" + item + "'";
string sroles = string.Join(",", roleArr);
string query;
if (String.IsNullOrEmpty(sroles))
{
query = "select id, name from role order by name";
}
else
{
query = "select id, name from role where id not in(" + sroles + ") order by name";
}
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text))
{
lbRoles.Items.Clear();
while (reader.Read())
{
var r = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"));
lbRoles.Items.Add(r);
}
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
