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