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);
}
}
