public static List <Lemma <int, string> > GetIntLemmata(SqlConnection conn, SqlTransaction trans, string proc, bool addEmpty)
{
List <Lemma <int, string> > list = new List <Lemma <int, string> >();
if (addEmpty)
{
list.Add(new Lemma <int, string>(0, ""));
}
try
{
using (SqlDataReader reader = DB.GetDataReader(conn, trans, proc, CommandType.StoredProcedure))
{
while (reader.Read())
{
list.Add(new Lemma <int, string>(reader.GetInt32("id"), reader.GetString("name")));
}
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
}
return(list);
}
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid analResId)
{
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis_result", CommandType.StoredProcedure,
new SqlParameter("@id", analResId)))
{
if (!reader.HasRows)
{
throw new Exception("Error: Analysis result with id " + analResId.ToString() + " was not found");
}
reader.Read();
Id = reader.GetGuid("id");
AnalysisId = reader.GetGuid("analysis_id");
NuclideId = reader.GetGuid("nuclide_id");
Activity = reader.GetDoubleNullable("activity");
ActivityUncertaintyABS = reader.GetDoubleNullable("activity_uncertainty_abs");
ActivityApproved = reader.GetBoolean("activity_approved");
UniformActivity = reader.GetDoubleNullable("uniform_activity");
UniformActivityUnitId = reader.GetInt32("uniform_activity_unit_id");
DetectionLimit = reader.GetDoubleNullable("detection_limit");
DetectionLimitApproved = reader.GetBoolean("detection_limit_approved");
Accredited = reader.GetBoolean("accredited");
Reportable = reader.GetBoolean("reportable");
InstanceStatusId = reader.GetInt32("instance_status_id");
CreateDate = reader.GetDateTime("create_date");
CreateId = reader.GetGuid("create_id");
UpdateDate = reader.GetDateTime("update_date");
UpdateId = reader.GetGuid("update_id");
}
NuclideName = GetNuclideName(conn, trans);
Dirty = false;
}
public static void PopulateComboBoxes(SqlConnection conn, string procedure, SqlParameter[] sqlParams, params ComboBox[] cbn)
{
List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >();
list.Add(new Lemma <Guid, string>(Guid.Empty, ""));
using (SqlDataReader reader = DB.GetDataReader(conn, null, procedure, CommandType.StoredProcedure, sqlParams))
{
while (reader.Read())
{
list.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
foreach (ComboBox cb in cbn)
{
object o = cb.SelectedValue;
cb.DataSource = new List <Lemma <Guid, string> >(list);
cb.DisplayMember = "Name";
cb.ValueMember = "Id";
if (o != null)
{
cb.SelectedValue = o;
}
}
}
public void Populate(SqlConnection conn, SqlTransaction trans)
{
if (Id == Guid.Empty)
{
return;
}
using (SqlDataReader reader = DB.GetDataReader(conn, trans, @"
select a.number, am.name as 'analysis_method_name', l.name as 'laboratory_name', ws.id as 'workflow_status_id', ws.name as 'workflow_status_name'
from analysis a
inner join analysis_method am on am.id = a.analysis_method_id
inner join laboratory l on l.id = a.laboratory_id
inner join workflow_status ws on ws.id = a.workflow_status_id
where a.id = @aid
", CommandType.Text, new SqlParameter("@aid", Id)))
{
reader.Read();
Number = reader.GetInt32("number");
AnalysisMethodName = reader.GetString("analysis_method_name");
LaboratoryName = reader.GetString("laboratory_name");
WorkflowStatusId = reader.GetInt32("workflow_status_id");
WorkflowStatusName = reader.GetString("workflow_status_name");
}
}
public static bool HasRequiredFields(SqlConnection conn, SqlTransaction trans, Guid sampleId)
{
string query = "select number, sample_type_id, project_sub_id, laboratory_id, reference_date from sample where id = @id";
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@id", sampleId)))
{
if (!reader.HasRows)
{
return(false);
}
reader.Read();
if (!DB.IsValidField(reader["number"]) ||
!DB.IsValidField(reader["sample_type_id"]) ||
!DB.IsValidField(reader["project_sub_id"]) ||
!DB.IsValidField(reader["laboratory_id"]) ||
!DB.IsValidField(reader["reference_date"]))
{
return(false);
}
if (!Utils.IsValidGuid(reader["sample_type_id"]) ||
!Utils.IsValidGuid(reader["project_sub_id"]) ||
!Utils.IsValidGuid(reader["laboratory_id"]))
{
return(false);
}
}
return(true);
}
public void Populate(SqlConnection conn, SqlTransaction trans)
{
if (Id == Guid.Empty)
{
return;
}
using (SqlDataReader reader = DB.GetDataReader(conn, trans, @"
select s.number, st.name as 'sample_type_name', sc.name as 'sample_component_name', l.name as 'laboratory_name'
from sample s
inner join sample_type st on st.id = s.sample_type_id
left outer join sample_component sc on sc.id = s.sample_component_id
inner join laboratory l on l.id = s.laboratory_id
where s.id = @sid
", CommandType.Text,
new SqlParameter("@sid", Id)))
{
reader.Read();
Number = reader.GetInt32("number");
SampleTypeName = reader.GetString("sample_type_name");
SampleComponentName = reader.GetString("sample_component_name");
LaboratoryName = reader.GetString("laboratory_name");
}
}
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid sampParamId)
{
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_sample_parameter", CommandType.StoredProcedure,
new SqlParameter("@id", sampParamId)))
{
if (!reader.HasRows)
{
throw new Exception("Error: Sample parameter with id " + sampParamId.ToString() + " was not found");
}
reader.Read();
Id = reader.GetGuid("id");
SampleId = reader.GetGuid("sample_id");
SampleParameterNameId = reader.GetGuid("sample_parameter_name_id");
Value = reader.GetString("value");
CreateDate = reader.GetDateTime("create_date");
CreateId = reader.GetGuid("create_id");
UpdateDate = reader.GetDateTime("update_date");
UpdateId = reader.GetGuid("update_id");
}
Name = GetParameterName(conn, trans);
Type = GetParameterType(conn, trans);
Dirty = false;
}
private void UpdateCurrentPreparations(TreeNode tnode)
{
if (tnode.Level != 1 || tnode.Tag == null)
{
return;
}
tnode.ToolTipText = "";
try
{
List <Guid> prepList = tnode.Tag as List <Guid>;
string query = "select number from preparation where id in ("
+ String.Join(",", prepList.Select(x => "'" + x.ToString() + "'").ToArray()) + ")";
List <object> prepNums = new List <object>();
using (SqlConnection conn = DB.OpenConnection())
{
using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text))
while (reader.Read())
{
prepNums.Add(mSample.Number + "/" + reader.GetString("number"));
}
}
tnode.ToolTipText = "Connected preparations: " + String.Join(", ", prepNums);
}
catch {}
}
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);
}
}
}
}
public void Populate(SqlConnection conn, SqlTransaction trans)
{
if (Id == Guid.Empty)
{
return;
}
using (SqlDataReader reader = DB.GetDataReader(conn, trans, @"
select p.number, pm.name as 'preparation_method_name', l.name as 'laboratory_name', ws.id as 'workflow_status_id', ws.name as 'workflow_status_name'
from preparation p
inner join preparation_method pm on pm.id = p.preparation_method_id
inner join laboratory l on l.id = p.laboratory_id
inner join workflow_status ws on ws.id = p.workflow_status_id
where p.id = @pid
", CommandType.Text, new SqlParameter("@pid", Id)))
{
reader.Read();
Number = reader.GetInt32("number");
PreparationMethodName = reader.GetString("preparation_method_name");
LaboratoryName = reader.GetString("laboratory_name");
WorkflowStatusId = reader.GetInt32("workflow_status_id");
WorkflowStatusName = reader.GetString("workflow_status_name");
}
}
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid analId)
{
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis", CommandType.StoredProcedure,
new SqlParameter("@id", analId)))
{
if (!reader.HasRows)
{
throw new Exception("Error: Analysis with id " + analId.ToString() + " was not found");
}
reader.Read();
Id = reader.GetGuid("id");
Number = reader.GetInt32("number");
AssignmentId = reader.GetGuid("assignment_id");
LaboratoryId = reader.GetGuid("laboratory_id");
PreparationId = reader.GetGuid("preparation_id");
AnalysisMethodId = reader.GetGuid("analysis_method_id");
WorkflowStatusId = reader.GetInt32("workflow_status_id");
SpecterReference = reader.GetString("specter_reference");
ActivityUnitId = reader.GetGuid("activity_unit_id");
ActivityUnitTypeId = reader.GetGuid("activity_unit_type_id");
SigmaActivity = reader.GetDouble("sigma_act");
SigmaMDA = reader.GetDouble("sigma_mda");
NuclideLibrary = reader.GetString("nuclide_library");
MDALibrary = reader.GetString("mda_library");
InstanceStatusId = reader.GetInt32("instance_status_id");
Comment = reader.GetString("comment");
CreateDate = reader.GetDateTime("create_date");
CreateId = reader.GetGuid("create_id");
UpdateDate = reader.GetDateTime("update_date");
UpdateId = reader.GetGuid("update_id");
}
Results.Clear();
List <Guid> analResIds = new List <Guid>();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from analysis_result where analysis_id = @id and instance_status_id <= @instance_status_level", CommandType.Text,
new SqlParameter("@id", analId),
new SqlParameter("@instance_status_level", InstanceStatus.Active)))
{
while (reader.Read())
{
analResIds.Add(reader.GetGuid("id"));
}
}
foreach (Guid analResId in analResIds)
{
AnalysisResult ar = new AnalysisResult();
ar.LoadFromDB(conn, trans, analResId);
Results.Add(ar);
}
ImportFile = String.Empty;
Dirty = false;
}
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid prepId)
{
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_preparation", CommandType.StoredProcedure,
new SqlParameter("@id", prepId)))
{
if (!reader.HasRows)
{
throw new Exception("Error: Preparation with id " + prepId.ToString() + " was not found");
}
reader.Read();
Id = reader.GetGuid("id");
SampleId = reader.GetGuid("sample_id");
Number = reader.GetInt32("number");
AssignmentId = reader.GetGuid("assignment_id");
LaboratoryId = reader.GetGuid("laboratory_id");
PreparationGeometryId = reader.GetGuid("preparation_geometry_id");
PreparationMethodId = reader.GetGuid("preparation_method_id");
WorkflowStatusId = reader.GetInt32("workflow_status_id");
Amount = reader.GetDoubleNullable("amount");
PrepUnitId = reader.GetInt32("prep_unit_id");
Quantity = reader.GetDoubleNullable("quantity");
QuantityUnitId = reader.GetInt32("quantity_unit_id");
FillHeightMM = reader.GetDoubleNullable("fill_height_mm");
Volume_l = reader.GetDoubleNullable("volume_l");
PreprocessingVolume_l = reader.GetDoubleNullable("preprocessing_volume_l");
InstanceStatusId = reader.GetInt32("instance_status_id");
Comment = reader.GetString("comment");
CreateDate = reader.GetDateTime("create_date");
CreateId = reader.GetGuid("create_id");
UpdateDate = reader.GetDateTime("update_date");
UpdateId = reader.GetGuid("update_id");
}
// Load analyses
Analyses.Clear();
List <Guid> analysisIds = new List <Guid>();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from analysis where preparation_id = @id", CommandType.Text,
new SqlParameter("@id", Id)))
{
while (reader.Read())
{
analysisIds.Add(reader.GetGuid("id"));
}
}
foreach (Guid aId in analysisIds)
{
Analysis a = new Analysis();
a.LoadFromDB(conn, trans, aId);
Analyses.Add(a);
}
}
public static bool HasAccreditationForOrderLine(SqlConnection conn, SqlTransaction trans, Guid labId, Guid sampleTypeId, Guid sampleComponentId, Guid prepMethId, Guid analMethId)
{
object o = GetScalar(conn, trans, "select path from sample_type where id = @id", CommandType.Text, new SqlParameter("@id", sampleTypeId));
if (o == null || o == DBNull.Value)
{
return(false);
}
string findSampleTypePath = o.ToString().ToLower();
bool accredFound = false;
string query = @"
select a.*, st.id as 'sample_type_id', st.path as 'sample_type_path', sc.id as 'sample_component_id' from accreditation_term a
inner join accreditation_term_x_laboratory al on a.id = al.accreditation_term_id and al.laboratory_id = @laboratory_id
inner join accreditation_term_x_sample_type ast on a.id = ast.accreditation_term_id
left outer join sample_type st on st.id = ast.sample_type_id
inner join accreditation_term_x_preparation_method apm on a.id = apm.accreditation_term_id and apm.preparation_method_id = @preparation_method_id
inner join accreditation_term_x_analysis_method aam on a.id = aam.accreditation_term_id and aam.analysis_method_id = @analysis_method_id
left outer join accreditation_term_x_sample_type_x_sample_component ass on a.id = ass.accreditation_term_id and st.id = ass.sample_type_id
left outer join sample_component sc on sc.id = ass.sample_component_id
where a.instance_status_id = 1";
SqlCommand cmd = new SqlCommand(query, conn, trans);
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new[] {
new SqlParameter("@laboratory_id", labId),
new SqlParameter("@preparation_method_id", prepMethId),
new SqlParameter("@analysis_method_id", analMethId)
}))
{
while (reader.Read())
{
Guid accredId = reader.GetGuid("id");
Guid sampTypeId = reader.GetGuid("sample_type_id");
Guid sampCompId = reader.GetGuid("sample_component_id");
string sampleTypePath = reader.GetString("sample_type_path").ToLower();
if (findSampleTypePath.StartsWith(sampleTypePath))
{
if (sampCompId != Guid.Empty)
{
if (sampCompId == sampleComponentId)
{
accredFound = true;
}
}
else
{
accredFound = true;
}
}
}
}
return(accredFound);
}
private void FormOrdersAssignUsers_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
DataTable dt = DB.GetDataTable(conn, null, "select id, name from cv_account where email is not NULL order by name", CommandType.Text);
DataColumn assignedColumn = new DataColumn("Assigned", typeof(bool));
assignedColumn.DefaultValue = false;
dt.Columns.Add(assignedColumn);
gridUsers.DataSource = dt;
gridUsers.ReadOnly = false;
gridUsers.Columns["name"].ReadOnly = true;
gridUsers.Columns["Assigned"].ReadOnly = false;
gridUsers.Columns["id"].Visible = false;
gridUsers.Columns["name"].HeaderText = "User";
List <Guid> assignedUserIds = new List <Guid>();
using (SqlDataReader reader = DB.GetDataReader(conn, null, "select account_id from assignment_x_account where assignment_id = @aid", CommandType.Text,
new SqlParameter("@aid", mAssignmentId)))
{
while (reader.Read())
{
assignedUserIds.Add(reader.GetGuid("account_id"));
}
}
foreach (DataGridViewRow row in gridUsers.Rows)
{
Guid uid = Utils.MakeGuid(row.Cells["id"].Value);
if (assignedUserIds.Contains(uid))
{
row.Cells["Assigned"].Value = true;
}
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid astId)
{
if (!AssignmentSampleType.IdExists(conn, trans, astId))
{
throw new Exception("Assignment sample type with id " + astId.ToString() + " was not found");
}
PreparationMethods.Clear();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_assignment_sample_type", CommandType.StoredProcedure,
new SqlParameter("@id", astId)))
{
if (!reader.HasRows)
{
throw new Exception("Assignment sample type with id " + astId.ToString() + " was not found");
}
reader.Read();
Id = reader.GetGuid("id");
AssignmentId = reader.GetGuid("assignment_id");
SampleTypeId = reader.GetGuid("sample_type_id");
SampleComponentId = reader.GetGuid("sample_component_id");
SampleCount = reader.GetInt32("sample_count");
RequestedActivityUnitId = reader.GetGuid("requested_activity_unit_id");
RequestedActivityUnitTypeId = reader.GetGuid("requested_activity_unit_type_id");
ReturnToSender = reader.GetBoolean("return_to_sender");
Comment = reader.GetString("comment");
CreateDate = reader.GetDateTime("create_date");
CreateId = reader.GetGuid("create_id");
UpdateDate = reader.GetDateTime("update_date");
UpdateId = reader.GetGuid("update_id");
Dirty = false;
}
List <Guid> prepMethIds = new List <Guid>();
using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from assignment_preparation_method where assignment_sample_type_id = @astId", CommandType.Text,
new SqlParameter("@astId", astId)))
{
while (reader.Read())
{
prepMethIds.Add(reader.GetGuid("id"));
}
}
foreach (Guid apmId in prepMethIds)
{
AssignmentPreparationMethod apm = new AssignmentPreparationMethod();
apm.LoadFromDB(conn, trans, apmId);
PreparationMethods.Add(apm);
}
}
public static void GetOrderRequiredInventory(SqlConnection conn, SqlTransaction trans, Guid assignmentId, out int nSamples, out int nPreparations, out int nAnalyses)
{
nSamples = nPreparations = nAnalyses = 0;
string query = @"
select
(
select sum(ast.sample_count)
from assignment a
inner join assignment_sample_type ast on ast.assignment_id = a.id
where a.id = @aid
) as 'nsamples',
(
select
sum(apm.preparation_method_count * ast.sample_count) as 'npreparations'
from assignment a
inner join assignment_sample_type ast on ast.assignment_id = a.id
inner join assignment_preparation_method apm on apm.assignment_sample_type_id = ast.id and apm.preparation_laboratory_id = a.laboratory_id
where a.id = @aid
) as 'npreparations',
(
select
sum(aam.analysis_method_count * apm.preparation_method_count * ast.sample_count) as 'nanalyses'
from assignment a
inner join assignment_sample_type ast on ast.assignment_id = a.id
inner join assignment_preparation_method apm on apm.assignment_sample_type_id = ast.id
left outer join assignment_analysis_method aam on aam.assignment_preparation_method_id = apm.id
where a.id = @aid
) as 'nanalyses'
";
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@aid", assignmentId)))
{
if (reader.HasRows)
{
reader.Read();
if (IsValidField(reader["nsamples"]))
{
nSamples = reader.GetInt32("nsamples");
}
if (IsValidField(reader["npreparations"]))
{
nPreparations = reader.GetInt32("npreparations");
}
if (IsValidField(reader["nanalyses"]))
{
nAnalyses = reader.GetInt32("nanalyses");
}
}
}
}
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);
}
}
}
private void FormSampleParameter_Load(object sender, EventArgs e)
{
lblTypeInfo.Text = "";
SampleParameterName spn = new SampleParameterName();
spn.Id = Guid.Empty;
spn.Name = "";
spn.Type = "";
cboxSampleParameterNames.Items.Add(spn);
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
SqlDataReader reader = DB.GetDataReader(conn, null, "select id, name, type from sample_parameter_name order by name", CommandType.Text);
while (reader.Read())
{
spn = new SampleParameterName();
spn.Id = reader.GetGuid("id");
spn.Name = reader.GetString("name");
spn.Type = reader.GetString("type");
cboxSampleParameterNames.Items.Add(spn);
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
cboxSampleParameterNames.DisplayMember = "Name";
cboxSampleParameterNames.ValueMember = "Id";
if (mSPId != Guid.Empty)
{
SampleParameter p = mSample.Parameters.Find(x => x.Id == mSPId);
cboxSampleParameterNames.Text = p.Name;
cboxSampleParameterNames.Enabled = false;
tbSampleParameterValue.Text = p.Value;
lblTypeInfo.Text = "Parameter type: " + p.Type;
}
}
public static void PopulateOrderYears(SqlConnection conn, ComboBox cbox)
{
List <string> years = new List <string>();
years.Add("");
using (SqlDataReader reader = DB.GetDataReader(conn, null, "select distinct year(create_date) as 'year' from assignment order by year desc", CommandType.Text))
{
while (reader.Read())
{
years.Add(reader.GetString("year"));
}
}
cbox.DataSource = years;
}
public static void PopulateOrderWorkflowStatus(SqlConnection conn, ComboBox cbox)
{
List <Lemma <int, string> > stats = new List <Lemma <int, string> >();
stats.Add(new Lemma <int, string>(0, ""));
using (SqlDataReader reader = DB.GetDataReader(conn, null, "csp_select_workflow_status", CommandType.StoredProcedure))
{
while (reader.Read())
{
stats.Add(new Lemma <int, string>(reader.GetInt32("id"), reader.GetString("name")));
}
}
cbox.DataSource = stats;
}
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;
}
public static void PopulateAccreditationTermNuclides(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb)
{
lb.Items.Clear();
string query = @"
select n.id, n.name from nuclide n
inner join accreditation_term_x_nuclide atxn on n.id = atxn.nuclide_id and atxn.accreditation_term_id = @accreditation_term_id
order by n.name";
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId)))
{
while (reader.Read())
{
lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
}
public static List <Lemma <double, string> > GetSigmaValues(SqlConnection conn, SqlTransaction trans, bool MDA)
{
List <Lemma <double, string> > lst = new List <Lemma <double, string> >();
lst.Add(new Lemma <double, string>(0d, ""));
using (SqlDataReader reader = DB.GetDataReader(conn, trans, MDA ? "csp_select_sigma_mda_values" : "csp_select_sigma_values", CommandType.StoredProcedure))
{
while (reader.Read())
{
lst.Add(new Lemma <double, string>(reader.GetDouble(0), reader.GetString(1)));
}
}
return(lst);
}
public static void PopulateAccreditationTermAnalysisMethods(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb)
{
lb.Items.Clear();
string query = @"
select am.id, am.name from analysis_method am
inner join accreditation_term_x_analysis_method atxam on am.id = atxam.analysis_method_id and atxam.accreditation_term_id = @accreditation_term_id
order by am.name";
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId)))
{
while (reader.Read())
{
lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
}
public static void PopulateAccreditationTermLaboratories(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb)
{
lb.Items.Clear();
string query = @"
select l.id, l.name from laboratory l
inner join accreditation_term_x_laboratory atxl on l.id = atxl.laboratory_id and atxl.accreditation_term_id = @accreditation_term_id
order by l.name";
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId)))
{
while (reader.Read())
{
lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
}
public static void PopulateProjectMain(SqlConnection conn, SqlTransaction trans, Guid accountId, int instanceStatusLevel, params ComboBox[] cbn)
{
List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >();
list.Add(new Lemma <Guid, string>(Guid.Empty, ""));
string query;
if (!Utils.IsValidGuid(Common.LabId))
{
query = @"
select distinct pm.id, pm.name
from project_main pm
inner join project_sub ps on ps.project_main_id = pm.id
inner join project_sub_x_account psxa on psxa.project_sub_id = ps.id and psxa.account_id = @aid
where pm.instance_status_id <= @isl
order by pm.name
";
}
else
{
query = "select id, name from project_main where instance_status_id <= @isl order by name";
}
using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text,
new SqlParameter("@aid", accountId),
new SqlParameter("@isl", instanceStatusLevel)
))
{
while (reader.Read())
{
list.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
foreach (ComboBox cb in cbn)
{
object o = cb.SelectedValue;
cb.DataSource = new List <Lemma <Guid, string> >(list);
cb.DisplayMember = "Name";
cb.ValueMember = "Id";
if (o != null)
{
cb.SelectedValue = o;
}
}
}
private void FormSampParamName_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
cboxSampParamType.Items.Clear();
using (SqlDataReader reader = DB.GetDataReader(conn, null, "select * from sample_parameter_type order by name", CommandType.Text))
{
while (reader.Read())
{
cboxSampParamType.Items.Add(reader.GetString("name"));
}
}
if (mSampParamNameId != Guid.Empty)
{
using (SqlDataReader reader = DB.GetDataReader(conn, null, "select * from sample_parameter_name where id = @id", CommandType.Text,
new SqlParameter("@id", mSampParamNameId)))
{
if (!reader.HasRows)
{
throw new Exception("FormSampParamName_Load: No sample parameter name found with id: " + mSampParamNameId.ToString());
}
reader.Read();
cboxSampParamType.Text = reader.GetString("type");
tbParamName.Text = reader.GetString("name");
}
cboxSampParamType.Enabled = false;
}
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
public List <Guid> GetSampleTypeIdsForSampleType(SqlConnection conn, SqlTransaction trans, string sampleTypeRoot)
{
List <Guid> ids = new List <Guid>();
using (SqlDataReader astReader = DB.GetDataReader(conn, null, "csp_select_assignment_sample_types_for_sample_type_name", CommandType.StoredProcedure,
new SqlParameter("@assignment_id", Id),
new SqlParameter("@sample_type_name", sampleTypeRoot)))
{
while (astReader.Read())
{
Guid stId = astReader.GetGuid("sample_type_id");
ids.Add(stId);
}
}
return(ids);
}
private static void AddSampleTypeComponentsAscending(SqlConnection conn, Guid sampleTypeId, TreeNode tnode, List <Lemma <Guid, string> > comps)
{
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())
{
comps.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")));
}
}
if (tnode.Parent != null)
{
Guid parentId = Guid.Parse(tnode.Parent.Name);
AddSampleTypeComponentsAscending(conn, parentId, tnode.Parent, comps);
}
}
