private SmartCollection<Sample> GetPendingSubmissions(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<Sample> resultList = new SmartCollection<Sample>(); SmartCollection<SampleTest> testList = new SmartCollection<SampleTest>(); User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return resultList; string sqlDepartment = "AND sampleTest.DepartmentId = @DepartmentId"; // Check ApproveAll or Administrator rights if (AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.SampleTest, ModuleAction.ApproveAll))) { sqlDepartment = "AND sampleTest.DepartmentId >= 0"; } string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.is_price_table, priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template, test.testname,test.active as testactive,analyte.analytename,analyte.controlled, analyte.active as analyteactive,analyst.firstname, analyst.lastname, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] WHERE (sampleTest.status = @Status " + sqlDepartment + ") "; sql += "AND sampleTest.delete_date IS NULL"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress; dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = currentUser.DepartmentId; dbCommand.CommandText = sql; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); using (CatalogDAO catalogDao = new CatalogDAO()) { sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } testList.Add(sampleTest); } returnDT = null; if (testList.Count > 0) { var sampleIds = testList.Select(x => x.ARLNumber).ToArray(); sql = string.Format(@" SELECT sample.id, sample.parentid,sample.sample_type_id,sample.name,sample.lab_number, sample.formulation_id,sample.quote_number,sample.project_number,sample.lot_number, sample.storage_id,sample.dosage_id,dosage.value as dosagevalue,dosage.active as dosageactive, sample.ndc_id, sample.articles,sample.container_type,sample.volume_amount, sample.volume_uom,sample.is_study,sample.is_gmp,sample.compounded_by,sample.compound_date,storage.description as storagedescription, storage.active as storageactive, storage.conditions as storageconditions, (SELECT COUNT(analytes.controlled) FROM orders_samples_analytes AS analytes WHERE analytes.controlled = 'true' and analytes.parentid = sample.id AND analytes.delete_date IS NULL) AS is_cds, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sample.modified_by, sample.modified_date, sample.created_by, sample.created_date FROM orders_samples as sample LEFT JOIN [User] as users ON sample.modified_by = users.UserID LEFT JOIN [User] as users2 ON sample.created_by = users2.UserID LEFT JOIN list.dosagetype as dosage ON sample.dosage_id = dosage.dosageid LEFT JOIN list.storage as storage ON sample.storage_id = storage.storageid WHERE sample.id IN ({0}) AND sample.delete_date IS NULL ;", String.Join(",", sampleIds)); dbCommand.Parameters.Clear(); dbCommand.CommandText = sql; returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Sample sample = new Sample(); resultList.Add(sample); } foreach (Sample sampleItem in resultList) { sampleItem.SampleTests.AddRange(testList.Where(x => x.ARLNumber == sampleItem.ARLNumber).Select(s => s)); } } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<SampleTest> GetWorkLoad(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, CatalogItem filterItem, SmartCollection<Analyte> filterAnalytes, DateTime beginStartDate, DateTime beginEndDate, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); string sqlOrderBy = string.Empty; string sqlTest = string.Empty; string sqlAnalyst = string.Empty; string sqlDepartment = string.Empty; string sqlBeginDate = string.Empty; string sqlAnalytes = string.Empty; if (filterItem.TestId > 0) { sqlTest = "AND sampleTest.testId = @TestId "; } else { sqlTest = "AND sampleTest.testId >= @TestId "; } if (filterItem.AnalystId > 0) { sqlAnalyst = "AND sampleTest.analystid = @AnalystId "; } else { sqlAnalyst = "AND sampleTest.analystid >= @AnalystId "; } if (filterItem.DepartmentId > 0) { sqlDepartment = "AND sampleTest.departmentid = @DepartmentId "; } else { sqlDepartment = "AND sampleTest.departmentid >= @DepartmentId "; } if (filterAnalytes != null || filterAnalytes.Count() > 0) { var analyteIds = filterAnalytes.Select(x => x.AnalyteId).ToArray(); if (analyteIds.Count() > 0) { sqlAnalytes = string.Format("AND sampleTest.analyteid IN ({0})", String.Join(",", analyteIds)); } } beginStartDate = DateEx.GetStartOfDay(beginStartDate); beginEndDate = DateEx.GetEndOfDay(beginEndDate); sqlBeginDate = " AND dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) BETWEEN @BeginStartDate AND @BeginEndDate "; //;DATEADD(day,timepoints.begindate_days, sampleTest.begin_date) BETWEEN @BeginStartDate AND @BeginEndDate "; sqlOrderBy = "ORDER BY begin_date"; string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) AS begin_date, sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.is_price_table, priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template, test.testname,test.active as testactive,analyte.analytename,analyte.controlled, analyte.active as analyteactive,analyst.firstname, analyst.lastname, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, timepoints.begindate_days, timepoints.timepoint_type, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] LEFT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.parentid = sampleTest.id LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid WHERE ((sampleTest.status = 2 or sampleTest.status = 3) AND sampleTest.delete_date IS NULL AND timepointsResults.result_date IS NULL) "; sql += sqlTest; sql += sqlAnalyst; sql += sqlDepartment; sql += sqlBeginDate; sql += sqlAnalytes; sql += sqlOrderBy; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress; dbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = filterItem.TestId ?? 0; dbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = filterItem.AnalystId ?? 0; dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = filterItem.DepartmentId ?? 0; dbCommand.Parameters.Add("@BeginStartDate", System.Data.SqlDbType.DateTime).Value = beginStartDate; dbCommand.Parameters.Add("@BeginEndDate", System.Data.SqlDbType.DateTime).Value = beginEndDate; dbCommand.CommandText = sql; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); using (CatalogDAO catalogDao = new CatalogDAO()) { //sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } resultList.Add(sampleTest); } returnDT = null; return resultList; } catch { throw; } }
public SmartCollection<SampleTest> SearchResultSampleTests(string searchString, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); string containerSearch = string.Empty; int? labNumber = null; int? sampleTestId = null; try { if (searchString.Contains("-")) { containerSearch = searchString; } else if (searchString.Contains(",")) { char[] charSeparators = new char[] { ',' }; string[] sepString = searchString.Split(charSeparators, StringSplitOptions.None); labNumber = Convert.ToInt32(sepString[0].Trim()); sampleTestId = Convert.ToInt32(sepString[1].Trim()); } else { labNumber = Convert.ToInt32(searchString.Trim()); } } catch (Exception) { throw new Exception("Search value is an invalid format"); } using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" SELECT DISTINCT sampleTests.id,timepoints.id as timePointId, sampleTests.parentid,sampleTests.status,sampleTests.sampleid,sampleTests.lab_number, sampleTests.priorityid,sampleTests.typeid,sampleTests.analyteid,sampleTests.testid, sampleTests.departmentid,sampleTests.analystid,sampleTests.method_name,sampleTests.method_number_name, sampleTests.low,sampleTests.high,sampleTests.test_minutes, sampleTests.equipment_minutes, sampleTests.accounting_code,dbo.ReturnNextBusinessDay(sampleTests.begin_date,timepoints.begindate_days) AS begin_date, sampleTests.due_date,sampleTests.has_requirement_code, sampleTests.requirement_code,sampleTests.item_price,sampleTests.rush_charge,sampleTests.bill_groupid, sampleTests.catalogid, sampleTests.methodid, sampleTests.methodnumberid, sampleTests.is_per_analyte, sampleTests.is_price_table, priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template, test.testname,test.active as testactive,analyte.analytename,analyte.controlled, analyte.active as analyteactive,analyst.firstname, analyst.lastname, sampleTests.endotoxin_limit,sampleTests.endotoxin_limit_uom, sampleTests.avg_weight, sampleTests.avg_weight_uom, sampleTests.dose_per_hour, sampleTests.dose_per_hour_uom,sampleTests.route_of_administration, sampleTests.articles,sampleTests.is_signed, timepoints.begindate_days, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTests.modified_by, sampleTests.modified_date, sampleTests.created_by, sampleTests.created_date, timepoints.timepoint_type FROM orders_samples_tests AS sampleTests RIGHT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.id = ( SELECT TOP 1 timepoints.id FROM orders_samples_tests_timepoints AS timepoints LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid WHERE timepointsResults.result_date IS NULL AND timepoints.parentid = sampleTests.id ORDER BY timepoints.begindate_days ) LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON oos.id = timepoints.oosid AND oos.is_testing_complete = 'true' -- Continue Testing LEFT JOIN [User] as users ON sampleTests.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTests.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTests.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTests.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTests.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTests.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTests.[priorityid] = priorities.[priorityid] LEFT JOIN orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL) "; // Maybe Limit Results by Analyst and/or Department DbCommand.Parameters.Clear(); if (!String.IsNullOrWhiteSpace(containerSearch)) { sql += "AND containers.containerid = @ContainerId "; DbCommand.Parameters.Add("@ContainerId", System.Data.SqlDbType.VarChar, 100).Value = containerSearch; } else if (labNumber != null && sampleTestId != null) { sql += "AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId "; DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber; DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId; } else { sql += "AND sampleTests.lab_number = @LabNumber "; DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber; } sql += " ORDER BY sampleTests.id"; DbCommand.CommandText = sql; //DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.Approved; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); using (CatalogDAO catalogDao = new CatalogDAO()) { //sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } resultList.Add(sampleTest); } returnDT = null; } return resultList; } else { throw new Exception("Unable to Connect"); } } } catch { throw; } }
public SmartCollection<SampleTest> GetWorkOrders(List<string> recordList, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sqlSampleTests = string.Format("AND sampleTest.id IN ({0}) ", String.Join(",", recordList)); string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.is_price_table,sampleTest.notes,sampleTest.is_discount_allowed, priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template, test.testname,test.active as testactive,analyte.analytename,analyte.controlled, analyte.active as analyteactive,analyst.firstname, analyst.lastname, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, orders_samples.status as sample_status, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN orders_samples ON sampleTest.sampleid = orders_samples.id LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] WHERE sampleTest.delete_date IS NULL "; sql += sqlSampleTests; sql += "ORDER BY sampleTest.id DESC"; DbCommand.CommandText = sql; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); using (CatalogDAO catalogDao = new CatalogDAO()) { sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } // Do not load predefined notes for canceled tests if (sampleTest.Status != SampleTestStatus.Cancelled) { using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } } resultList.Add(sampleTest); } returnDT = null; } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public SmartCollection<CatalogItem> DoGetCatalog(CatalogItemFilter filter, Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (CatalogDAO dao = new CatalogDAO()) { return dao.GetCatalog(filter, identification); } }
public int DoSaveCatalogItem(CatalogItem catalogItem, Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); //Validate Object if (!catalogItem.ValidateModel()) throw new FaultException<ServiceFault>(new ServiceFault(catalogItem.CurrentErrors), new FaultReason(SysVars.InvalidFormat)); using (CatalogDAO dao = new CatalogDAO()) { return dao.SaveCatalogItem(catalogItem, identification); } }
public int DoRemoveCatalogItem(int catalogId, Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (CatalogDAO dao = new CatalogDAO()) { return dao.RemoveCatalogItem(catalogId, identification.UserId); } }