Esempio n. 1
0
        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;
            }
        }
Esempio n. 2
0
        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;
            }
        }