public static void PopulateQuantityUnits(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_quantity_units", CommandType.StoredProcedure);
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
}
public static void PopulateSampleParameterNames(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "select * from sample_parameter_name", CommandType.Text);
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["type"].HeaderText = "Type";
}
public static void PopulateActivityUnits(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_activity_units_flat", CommandType.StoredProcedure);
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Unit name";
grid.Columns["convert_factor"].HeaderText = "Conv.fact.";
grid.Columns["uniform_activity_unit_name"].HeaderText = "Uniform unit";
}
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 static void PopulateAccreditationTermSampleTypes(SqlConnection conn, SqlTransaction trans, Guid accredId, DataGridView grid)
{
string query = @"
select st.id, st.name, st.path from sample_type st
inner join accreditation_term_x_sample_type atxst on st.id = atxst.sample_type_id and atxst.accreditation_term_id = @accreditation_term_id
order by st.name";
grid.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId));
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["path"].HeaderText = "Path";
}
public static void PopulatePersons(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_persons", CommandType.StoredProcedure);
grid.Columns["id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["email"].HeaderText = "Email";
grid.Columns["phone"].HeaderText = "Phone";
grid.Columns["address"].HeaderText = "Address";
}
public static void PopulateAttachments(SqlConnection conn, SqlTransaction trans, string sourceTable, Guid id, DataGridView grid)
{
string query = "select id, name, file_extension from attachment where source_table = @source_table and source_id = @source_id order by create_date desc";
grid.DataSource = DB.GetDataTable(conn, trans, query, CommandType.Text, new[] {
new SqlParameter("@source_table", sourceTable),
new SqlParameter("@source_id", id)
});
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["file_extension"].HeaderText = "Type";
}
public static void PopulateSamplingMethods(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_sampling_methods_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", InstanceStatus.Deleted));
grid.Columns["id"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
public static void PopulateOrdersConstruction(SqlConnection conn, Guid laboratoryId, DataGridView grid)
{
string query = @"
select id, name, customer_contact_name, customer_company_name
from assignment a
where laboratory_id = @laboratory_id and instance_status_id = 1 and workflow_status_id = 1
order by create_date desc";
grid.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text, new SqlParameter("@laboratory_id", laboratoryId));
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["customer_contact_name"].HeaderText = "Customer";
grid.Columns["customer_company_name"].HeaderText = "Company";
}
public static void PopulateLabAnalMeths(SqlConnection conn, Guid labId, DataGridView grid)
{
string query = @"
select am.id, am.name_short, am.name
from analysis_method am
inner join laboratory_x_analysis_method lxam on lxam.analysis_method_id = am.id and lxam.laboratory_id = @lab_id
order by am.name_short
";
grid.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text, new SqlParameter("@lab_id", labId));
grid.Columns["id"].Visible = false;
grid.Columns["name_short"].HeaderText = "Abbr.";
grid.Columns["name"].HeaderText = "Name";
}
public static void PopulateUsersForProjectSub(SqlConnection conn, SqlTransaction trans, Guid projectSubId, DataGridView grid)
{
string query = @"
select a.id, a.name, a.email
from cv_account a
inner join project_sub_x_account psa on psa.account_id = a.id and psa.project_sub_id = @psid
where a.email is not NULL
";
grid.DataSource = DB.GetDataTable(conn, trans, query, CommandType.Text, new SqlParameter("@psid", projectSubId));
grid.Columns["id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["email"].HeaderText = "Email";
}
private void FormLabXPrepMeth_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
var pmArr = from item in mExistingPrepMeths select "'" + item + "'";
string exceptIds = string.Join(",", pmArr);
conn = DB.OpenConnection();
string query;
if (String.IsNullOrEmpty(exceptIds))
{
query = "select * from preparation_method where instance_status_id < 2";
}
else
{
query = "select * from preparation_method where id not in(" + exceptIds + ") and instance_status_id < 2";
}
gridPrepMeth.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text, new SqlParameter("@lid", mLabId));
gridPrepMeth.Columns["id"].Visible = false;
gridPrepMeth.Columns["comment"].Visible = false;
gridPrepMeth.Columns["instance_status_id"].Visible = false;
gridPrepMeth.Columns["create_id"].Visible = false;
gridPrepMeth.Columns["create_date"].Visible = false;
gridPrepMeth.Columns["update_id"].Visible = false;
gridPrepMeth.Columns["update_date"].Visible = false;
gridPrepMeth.Columns["name"].HeaderText = "Name";
gridPrepMeth.Columns["name_short"].HeaderText = "Abbr.";
gridPrepMeth.Columns["description_link"].HeaderText = "Desc.link";
gridPrepMeth.Columns["destructive"].HeaderText = "Destructive";
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
public static void PopulateUsers(SqlConnection conn, int instanceStatusLevel, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_accounts_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", instanceStatusLevel));
grid.Columns["id"].Visible = false;
grid.Columns["address"].Visible = false;
grid.Columns["language_code"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["email"].HeaderText = "Email";
grid.Columns["phone"].HeaderText = "Phone";
grid.Columns["laboratory_name"].HeaderText = "Lab";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
public static void PopulateCustomers(SqlConnection conn, int statusLevel, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_customers_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", statusLevel));
grid.Columns["id"].Visible = false;
grid.Columns["person_name"].HeaderText = "Cont.Name";
grid.Columns["person_email"].HeaderText = "Cont.Email";
grid.Columns["person_phone"].HeaderText = "Cont.Phone";
grid.Columns["person_address"].HeaderText = "Cont.Address";
grid.Columns["company_name"].HeaderText = "Comp.Name";
grid.Columns["company_email"].HeaderText = "Comp.Email";
grid.Columns["company_phone"].HeaderText = "Comp.Phone";
grid.Columns["company_address"].HeaderText = "Comp.Address";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
public static void PopulateMunicipalities(SqlConnection conn, Guid cid, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_municipalities_for_county_flat", CommandType.StoredProcedure,
new SqlParameter("@county_id", cid),
new SqlParameter("@instance_status_level", InstanceStatus.Deleted));
grid.Columns["id"].Visible = false;
grid.Columns["county_name"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["municipality_number"].HeaderText = "Number";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
public static void PopulateGeometries(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_preparation_geometries_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", InstanceStatus.Deleted));
grid.Columns["id"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["min_fill_height_mm"].HeaderText = "Min Fillh. (mm)";
grid.Columns["max_fill_height_mm"].HeaderText = "Max Fillh. (mm)";
grid.Columns["instance_status_name"].HeaderText = "Status";
// FIXME
}
public static void PopulateProjectsSub(SqlConnection conn, Guid project_main_id, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_projects_sub_flat", CommandType.StoredProcedure,
new SqlParameter("@project_main_id", project_main_id),
new SqlParameter("@instance_status_level", InstanceStatus.Deleted)
);
grid.Columns["id"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["project_main_name"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
public static void PopulateAnalysisMethods(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_analysis_methods_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", InstanceStatus.Deleted));
grid.Columns["id"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["name_short"].HeaderText = "Abbr.";
grid.Columns["description_link"].HeaderText = "Desc.link";
grid.Columns["specter_reference_regexp"].HeaderText = "Spec.Ref RE";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
private void FormSelectExistingPreps_Load(object sender, EventArgs e)
{
lblSampleInfo.Text = "Sample: " + mSample.Number + ", External Id: " + mSample.ExternalId;
SqlConnection conn = null;
try
{
conn = DB.OpenConnection();
string query = @"
select
p.id,
p.number as 'Name',
pm.name as 'Prep.meth',
pg.name as 'Geom.',
l.name as 'Laboratory'
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
left outer join preparation_geometry pg on pg.id = p.preparation_geometry_id
where p.sample_id = @sample_id and p.laboratory_id = @laboratory_id
order by p.number";
gridPreparations.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text,
new SqlParameter("@sample_id", mSample.Id),
new SqlParameter("@laboratory_id", LaboratoryId));
gridPreparations.Columns["id"].Visible = false;
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
public static void PopulateLaboratories(SqlConnection conn, int instanceStatusLevel, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_laboratories_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", instanceStatusLevel));
grid.Columns["id"].Visible = false;
grid.Columns["assignment_counter"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["name_prefix"].HeaderText = "Prefix";
grid.Columns["address"].HeaderText = "Address";
grid.Columns["email"].HeaderText = "Email";
grid.Columns["phone"].HeaderText = "Phone";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
private void FormProjectSubXUsers_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
var uArr = from item in mExistingUsers select "'" + item + "'";
string exceptIds = string.Join(",", uArr);
conn = DB.OpenConnection();
string query;
if (String.IsNullOrEmpty(exceptIds))
{
query = "select id, name, username from cv_account where instance_status_id < 2 and email is not NULL";
}
else
{
query = "select id, name, username from cv_account where id not in(" + exceptIds + ") and instance_status_id < 2 and email is not NULL";
}
gridUsers.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text);
gridUsers.Columns["id"].Visible = false;
gridUsers.Columns["name"].HeaderText = "Name";
gridUsers.Columns["username"].HeaderText = "Username";
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
public static void PopulateNuclides(SqlConnection conn, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, null, "csp_select_nuclides_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", InstanceStatus.Deleted));
grid.Columns["id"].Visible = false;
grid.Columns["comment"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["zas"].HeaderText = "Id (ZAS)";
grid.Columns["name"].HeaderText = "Name";
grid.Columns["protons"].HeaderText = "Protons";
grid.Columns["neutrons"].HeaderText = "Neutrons";
grid.Columns["meta_stable"].HeaderText = "Meta stable";
grid.Columns["half_life_year"].HeaderText = "T 1/2 (Years)";
grid.Columns["instance_status_name"].HeaderText = "Status";
grid.Columns["half_life_year"].DefaultCellStyle.Format = Utils.ScientificFormat;
}
public static void PopulateAccreditationTerms(SqlConnection conn, SqlTransaction trans, int instance_status_level, DataGridView grid)
{
grid.DataSource = DB.GetDataTable(conn, trans, "csp_select_accreditation_terms_flat", CommandType.StoredProcedure,
new SqlParameter("@instance_status_level", instance_status_level));
grid.Columns["id"].Visible = false;
grid.Columns["create_date"].Visible = false;
grid.Columns["create_id"].Visible = false;
grid.Columns["update_date"].Visible = false;
grid.Columns["update_id"].Visible = false;
grid.Columns["name"].HeaderText = "Name";
grid.Columns["fill_height_min"].HeaderText = "F.H. min";
grid.Columns["fill_height_max"].HeaderText = "F.H. max";
grid.Columns["weight_min"].HeaderText = "Weight min";
grid.Columns["weight_max"].HeaderText = "Weight max";
grid.Columns["volume_min"].HeaderText = "Volume min";
grid.Columns["volume_max"].HeaderText = "Volume max";
grid.Columns["density_min"].HeaderText = "Density min";
grid.Columns["density_max"].HeaderText = "Density max";
grid.Columns["instance_status_name"].HeaderText = "Status";
}
private void FormSampleMerge_Load(object sender, EventArgs e)
{
HashSet <string> samplePathSet = new HashSet <string>();
SqlConnection conn = null;
try
{
UI.PopulateSampleTypes(mSampleTypeTree, cboxSampleType);
conn = DB.OpenConnection();
foreach (Guid sid in mSampleIds)
{
Sample s = new Sample();
s.LoadFromDB(conn, null, sid);
s.Parameters.Clear();
foreach (Preparation p in s.Preparations)
{
p.Analyses.Clear();
}
s.Preparations.Clear();
s.SamplingDateFrom = null;
s.SamplingDateTo = null;
s.ReferenceDate = null;
mSamples.Add(s);
samplePathSet.Add(s.GetSampleTypePath(conn, null));
}
if (samplePathSet.Count == 0)
{
cboxSampleType.SelectedValue = Guid.Empty;
cboxSampleType.Enabled = true;
}
else if (samplePathSet.Count == 1)
{
SqlCommand cmd = new SqlCommand("select id from sample_type where path = @path", conn);
cmd.Parameters.AddWithValue("@path", samplePathSet.ElementAt(0));
Guid stId = (Guid)cmd.ExecuteScalar();
cboxSampleType.SelectedValue = stId;
cboxSampleType.Enabled = false;
}
else
{
string[] firstItems = samplePathSet.ElementAt(0).Split(new char[] { '/' });
int finalLevel = firstItems.Length;
for (int i = 1; i < samplePathSet.Count; i++)
{
int level = 0;
string[] items = samplePathSet.ElementAt(i).Split(new char[] { '/' });
int max = Math.Min(firstItems.Length, items.Length);
for (int j = 0; j < items.Length && j < max; j++)
{
if (items[j] == firstItems[j])
{
level++;
}
else
{
break;
}
}
if (level < finalLevel)
{
finalLevel = level;
}
}
string finalPath = String.Join("/", firstItems, 0, finalLevel);
if (finalPath == String.Empty)
{
cboxSampleType.SelectedValue = Guid.Empty;
cboxSampleType.Enabled = true;
}
else
{
SqlCommand cmd = new SqlCommand("select id from sample_type where path = @path", conn);
cmd.Parameters.AddWithValue("@path", finalPath);
Guid stId = (Guid)cmd.ExecuteScalar();
cboxSampleType.SelectedValue = stId;
cboxSampleType.Enabled = false;
}
}
List <Sample> mSamplesSortedBySamplingDate = mSamples.OrderBy(x => x.SamplingDateFrom).ToList();
mSamplesSortedBySamplingDate.RemoveAll(x => x.SamplingDateFrom == null);
if (mSamplesSortedBySamplingDate.Count == 0)
{
DateTime now = DateTime.Now;
dtSamplingDate.Value = now;
dtSamplingTime.Value = now;
}
else
{
int mid = mSamplesSortedBySamplingDate.Count / 2;
DateTime dtstime = mSamplesSortedBySamplingDate[mid].SamplingDateFrom.Value;
dtSamplingDate.Value = dtstime;
dtSamplingTime.Value = dtstime;
}
List <Sample> mSamplesSortedByRefDate = mSamples.OrderBy(x => x.ReferenceDate).ToList();
mSamplesSortedByRefDate.RemoveAll(x => x.ReferenceDate == null);
if (mSamplesSortedByRefDate.Count == 0)
{
DateTime now = DateTime.Now;
dtReferenceDate.Value = now;
dtReferenceTime.Value = now;
}
else
{
int mid = mSamplesSortedByRefDate.Count / 2;
DateTime dtrtime = mSamplesSortedByRefDate[mid].ReferenceDate.Value;
dtReferenceDate.Value = dtrtime;
dtReferenceTime.Value = dtrtime;
}
string query = String.Format(@"
select
s.id,
s.number as 'sample_number',
s.external_id,
st.name as 'sample_type_name',
sc.name as 'sample_component_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
where s.id in({0})
order by s.number desc", mSampleIdsCsv);
gridSamples.DataSource = DB.GetDataTable(conn, null, query, CommandType.Text);
gridSamples.Columns["id"].Visible = false;
gridSamples.Columns["sample_number"].HeaderText = "Sample number";
gridSamples.Columns["external_id"].HeaderText = "External Id";
gridSamples.Columns["sample_type_name"].HeaderText = "Sample type";
gridSamples.Columns["sample_component_name"].HeaderText = "Sample component";
}
catch (Exception ex)
{
Common.Log.Error(ex);
MessageBox.Show(ex.Message);
DialogResult = DialogResult.Abort;
Close();
}
finally
{
conn?.Close();
}
}
