C# MsSqlPersistence.ExecuteQuery方法代码示例

        public SmartCollection<SampleTest> GetSampleTestsResultsList(SmartCollection<SampleTest> list, Identification identification)
        {
            try
            {
                string sql = string.Empty;
                SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            foreach (SampleTest sampleTestItem in list)
                            {
                                DbCommand.Parameters.Clear();
                                sql =
                                    @"
                                    SELECT
                                    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 orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id
                                    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]
                                    WHERE ((sampleTests.status = 2 OR sampleTests.status = 3)  AND sampleTests.delete_date IS NULL AND oos.id IS NULL)
                                    AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId
                                    ORDER BY sampleTests.id
                                    ";

                                // Maybe Limit Results by Analyst and/or Department

                                DbCommand.CommandText = sql;
                                DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = sampleTestItem.ARLNumber;
                                DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestItem.SampleTestId;
                                DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                                foreach (DataRow row in returnDT.Rows)
                                {
                                    SampleTest sampleTest = new SampleTest();
                                    //sampleTest.Sample = this.GetSample(ref dbConnection, ref dbCommand, sampleTest.SampleId);

                                    //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<ClientPricing> GetClientPricings(int ClientId)
        {
            try {
                SmartCollection<ClientPricing> resultList = new SmartCollection<ClientPricing>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspGetClientPricings";
                            dbCommand.Parameters.Clear();
                            dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;

                            DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in returnDT.Rows) {
                                ClientPricing price = new ClientPricing();
                                price.ClientPricingId = Convert.ToInt32(row["ClientPricingID"]);
                                price.ClientId = Convert.ToInt32(row["ClientID"]);
                                if (row["MethodID"] != DBNull.Value)
                                    price.MethodId = Convert.ToInt32(row["MethodID"]);
                                if (row["MethodNumberID"] != DBNull.Value)
                                    price.MethodNumberId = Convert.ToInt32(row["MethodNumberID"]);
                                if (row["AnalyteID"] != DBNull.Value)
                                    price.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
                                price.Description = row["Description"].ToString();
                                price.Discount = row["Discount"] != DBNull.Value ? Convert.ToDouble(row["Discount"]) : 0;
                                price.FlatRate = row["FlatRate"] != DBNull.Value ? Convert.ToDouble(row["FlatRate"]) : 0;
                                price.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
                                price.CreatedUser = row["CreatedUser"].ToString();
                                price.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                                price.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
                                price.ModifiedUser = row["ModifiedUser"].ToString();
                                price.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;

                                if (price.MethodId.HasValue)
                                    price.Method = new Method { MethodId = price.MethodId, MethodName = row["MethodName"].ToString() };
                                else
                                    price.Method = null;

                                if (price.MethodNumberId.HasValue)
                                    price.MethodNumber = new MethodNumber { MethodNumberId = price.MethodNumberId, MethodNumberName = row["MethodNumberName"].ToString() };
                                else
                                    price.MethodNumber = null;

                                if (price.AnalyteId.HasValue)
                                    price.AnalyteItem = new Analyte { AnalyteId = price.AnalyteId, AnalyteName = row["AnalyteName"].ToString() };
                                else
                                    price.AnalyteItem = null;

                                resultList.Add(price);
                            }
                            returnDT = null;
                        }
                    }else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }catch {
                throw;
            }
        }

        public Contact GetContact(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? contactId)
        {
            try
            {
                Contact contact = new Contact();
                if (dbConnection.IsConnected())
                {
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.CommandText = "uspGetContact";
                    dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;

                    DataTable contactDT = dbConnection.ExecuteQuery(dbCommand);
                    if (contactDT.Rows.Count == 1)
                    {
                        DataRow row = contactDT.Rows[0];
                        contact.ContactId = Convert.ToInt32(row["ContactID"]);
                        contact.ClientId = Convert.ToInt32(row["ClientID"]);
                        contact.FirstName = row["FirstName"].ToString();
                        contact.LastName = row["LastName"].ToString();
                        contact.PrimaryEmail = row["PrimaryEmail"].ToString();
                        contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
                        contact.SecondaryEmail = row["SecondaryEmail"].ToString();
                        contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
                        contact.PrimaryPhone = row["PrimaryPhone"].ToString();
                        contact.SecondaryPhone = row["SecondaryPhone"].ToString();
                        contact.Fax = row["Fax"].ToString();
                        contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
                        contact.Comments = row["Comments"].ToString();
                        contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
                        contact.CreatedUser = row["CreatedUser"].ToString();
                        contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                        contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
                        contact.ModifiedUser = row["ModifiedUser"].ToString();
                        contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
                        contactDT = null;
                    }
                    else
                    {
                        contactDT = null;
                        return null;
                    }
                }
                else
                {
                    throw new Exception("Unable to Connect");
                }
                return contact;
            }
            catch
            {
                throw;
            }
        }

        public ClientComplaint GetClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? clientComplaintId)
        {
            try {
                ClientComplaint complaint = new ClientComplaint();
                if (dbConnection.IsConnected()) {
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.CommandText = "uspGetClientComplaint";
                    dbCommand.Parameters.Clear();
                    dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = clientComplaintId;

                    DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
                    if (returnDT.Rows.Count == 1) {
                        DataRow row = returnDT.Rows[0];
                        complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
                        complaint.ClientId = Convert.ToInt32(row["ClientID"]);
                        complaint.ClientName = row["ClientName"].ToString();
                        complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
                        if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
                            complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
                        complaint.Description = row["Description"].ToString();
                        complaint.ARLNumber = row["ARLNumber"].ToString();
                        complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
                        complaint.RootCause = row["RootCause"].ToString();
                        complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
                        complaint.CorrectiveActionDate = row["CorrectiveActionDate"] != DBNull.Value ? (DateTime)row["CorrectiveActionDate"] : (DateTime)SqlDateTime.Null;
                        complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
                        complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
                        complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
                        complaint.NotifyUser = row["NotifyUser"].ToString();
                        complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
                        complaint.CreatedUser = row["CreatedUser"].ToString();
                        complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                        complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
                        complaint.ModifiedUser = row["ModifiedUser"].ToString();
                        complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;

                        returnDT = null;
                    }else {
                        returnDT = null;
                        return null;
                    }
                }else {
                    throw new Exception("Unable to Connect");
                }
                return complaint;
            }catch {
                throw;
            }
        }

        public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
        {
            try {
                ClientNote note = new ClientNote();
                if (dbConnection.IsConnected()) {
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.CommandText = "uspGetClientNote";
                    dbCommand.Parameters.Clear();
                    dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;

                    DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
                    if (notesDT.Rows.Count == 1) {
                        DataRow row = notesDT.Rows[0];
                        note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
                        note.ClientId = Convert.ToInt32(row["ClientID"]);
                        note.Note = row["Note"].ToString();
                        note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
                        note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
                        note.CreatedUser = row["CreatedUser"].ToString();
                        note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                        note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
                        note.ModifiedUser = row["ModifiedUser"].ToString();
                        note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
                        notesDT = null;
                    }else {
                        notesDT = null;
                        return null;
                    }
                }else {
                    throw new Exception("Unable to Connect");
                }
                return note;
            }catch {
                throw;
            }
        }

        private SmartCollection<TimePoint> GetSampleTestTimePoints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, SampleTest sampleTest, string[] useSql = null)
        {
            string sql = string.Empty;
            SmartCollection<TimePoint> result = new SmartCollection<TimePoint>();
            try
            {
                SmartCollection<BusinessClosedDay> closedDays = new SmartCollection<BusinessClosedDay>();
                using (SystemDAO systeDao = new SystemDAO())
                {
                    closedDays.AddRange(systeDao.GetBusinessClosedDays((DateTime)sampleTest.BeginDate, (DateTime)sampleTest.DueDate));
                }
                sql = useSql != null && useSql[1].Length > 0 ? useSql[1] : @"
                            SELECT
                            timepoints.id, timepoints.parentid,
                            DATEADD(day,timepoints.begindate_days, tests.begin_date) as begin_date,
                            timepoints.timepoint_type,timepoints.status, timepoints.is_on_report,
                            timepoints.report_by,timepoints.report_date, timepoints.report_type, timepoints.begindate_days, timepoints.oosid,
                            timepointDetails.parentid as timepointDetailsParentID,
                            timepointDetails.id as timepointDetailsId, timepointDetails.is_override_record, timepointDetails.limit,
                            timepointDetails.result, timepointDetails.expected_result, timepointDetails.result_date,
                            timepointDetails.is_oos,timepointDetails.created_by, timepointDetails.created_date,
                            timepointDetails.modified_by, timepointDetails.modified_date,
                            (users.firstname + ' ' + users.lastname) as modifieduser,
                            (users1.firstname + ' ' + users1.lastname) as ReportedByUser,
                            (users2.firstname + ' ' + users2.lastname) as createduser
                            FROM orders_samples_tests_timepoints AS timepoints
                            LEFT JOIN orders_samples_tests AS tests ON timepoints.parentid = tests.id
                            LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointDetails ON timepoints.id = timepointDetails.parentid
                            LEFT JOIN  [User] AS users ON timepointDetails.modified_by = users.UserID
                            LEFT JOIN  [User] as users1 ON timepoints.report_by = users1.UserID
                            LEFT JOIN  [User] as users2 ON timepointDetails.created_by = users2.UserID
                            WHERE timepoints.parentid = @ParentId
                            ORDER BY timepoints.begindate_days
                            ;";

                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
                dbCommand.CommandText = sql;
                DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
                foreach (DataRow row in returnDT.Rows)
                {
                    TimePoint timePoint = new TimePoint();
                    timePoint.Id = Convert.ToInt32(row["Id"]);
                    timePoint.ParentId = Convert.ToInt32(row["ParentId"]);
                    timePoint.BeginDate = AppLib.GetNextOpenBuisnessDay(Convert.ToDateTime(row["begin_date"]), closedDays);
                    timePoint.TimepointType = row["timepoint_type"] != DBNull.Value ? (EnumTimePointType)row["timepoint_type"] : EnumTimePointType.Result;
                    timePoint.IsOnReport = row["is_on_report"] != DBNull.Value ? Convert.ToBoolean(row["is_on_report"]) : false;
                    timePoint.ReportBy = row["report_by"] != DBNull.Value ? (int)row["report_by"] : 0;
                    timePoint.ReportedByUser = row["ReportedByUser"].ToString();
                    timePoint.ReportDate = row["report_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["report_date"]) : null;
                    timePoint.BeginDateDays = Convert.ToInt32(row["begindate_days"]);
                    timePoint.OosId = row["oosid"] != DBNull.Value ? Convert.ToInt32(row["oosid"]) : 0;
                    timePoint.ResultDetail.Id = row["timepointDetailsId"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsId"]) : null;
                    timePoint.ResultDetail.ParentId = row["timepointDetailsParentID"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsParentID"]) : null;
                    timePoint.ResultDetail.Limit = row["limit"].ToString();
                    timePoint.ResultDetail.Result = row["result"].ToString();
                    timePoint.ResultDetail.IsOverrideRecord = row["is_override_record"] != DBNull.Value ? Convert.ToBoolean(row["is_override_record"]) : false;
                    timePoint.ResultDetail.IsOos = row["is_oos"] != DBNull.Value ? Convert.ToBoolean(row["is_oos"]) : false;
                    timePoint.ResultDetail.ExpectedResult = row["expected_result"] != DBNull.Value ? (decimal?)Convert.ToDecimal(row["expected_result"]) : null;
                    timePoint.ResultDetail.ResultDate = row["result_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["result_date"]) : (DateTime?)null;
                    timePoint.ResultDetail.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0;
                    timePoint.ResultDetail.CreatedUser = row["createduser"].ToString();
                    timePoint.ResultDetail.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
                    timePoint.ResultDetail.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0;
                    timePoint.ResultDetail.ModifiedUser = row["modifieduser"].ToString();
                    timePoint.ResultDetail.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
                    result.Add(timePoint);
                }
            }
            catch
            {
                throw;
            }
            return result;
        }

        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)
//.........这里部分代码省略.........

        private SmartCollection<SampleTest> GetPendingFinalizations(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
        {
            try
            {
                SmartCollection<SampleTest> resultList = 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.FinalizeAll)))
                {
                    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();

                    resultList.Add(sampleTest);
                }
                returnDT = null;
                return resultList;
            }
            catch
            {
                throw;
            }
        }

        private SmartCollection<Invoice> GetPendingInvoices(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
        {
            try
            {
                SmartCollection<Invoice> resultList = new SmartCollection<Invoice>();

                User currentUser = new User();
                using (UserDAO userDao = new UserDAO())
                {
                    currentUser = userDao.GetUser(identification.UserId);
                }
                if (currentUser.IsNull() || currentUser.UserId.IsNull())
                    return resultList;

                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "uspGetInvoicableSamples";
                dbCommand.Parameters.Clear();

                DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
                foreach (DataRow row in returnDT.Rows)
                {
                    Invoice invoice = new Invoice();
                    invoice.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
                    invoice.ClientName = row["ClientName"].ToString();
                    invoice.AccountingId = row["AccountingId"].ToString();
                    invoice.PONumber = row["PONumber"].ToString();
                    invoice.TermName = row["TermName"].ToString();
                    invoice.IncludeYN = (bool)false;

                    invoice.InvoiceItems = this.GetInvoicableSampleItems((int)invoice.ARLNumber, showAll, identification);

                    resultList.Add(invoice);
                }
                returnDT = null;
                return resultList;

            }
            catch
            {
                throw;
            }
        }

        private SmartCollection<InvoiceItem> GetInvoicableSampleItems(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, Identification identification)
        {
            try
            {
                SmartCollection<InvoiceItem> resultList = new SmartCollection<InvoiceItem>();
                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "uspGetInvoicableSampleItems";
                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
                dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
                DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);

                foreach (DataRow row in returnDT.Rows)
                {
                    InvoiceItem invoiceItem = new InvoiceItem();

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

                    invoiceItem.AccountingCode = row["AccountingCode"].ToString();
                    invoiceItem.Class = row["DepartmentName"].ToString();
                    invoiceItem.Other1 = row["TestName"].ToString();
                    invoiceItem.Other2 = row["AnalystName"].ToString();
                    invoiceItem.Description = row["Description"].ToString();
                    if (row["BasePrice"] != DBNull.Value)
                        invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]);
                    if (row["SampleTestID"] != DBNull.Value)
                        invoiceItem.SampleTestId = (int)row["SampleTestID"];
                    if (row["Ordering"] != DBNull.Value)
                        invoiceItem.Ordering = (int)row["Ordering"];
                    if (row["QBInvoiceNumber"] != DBNull.Value)
                        invoiceItem.InvoiceId = (int)row["QBInvoiceNumber"];

                    resultList.Add(invoiceItem);
                }
                returnDT = null;
                return resultList;
            }
            catch
            {
                throw;
            }
        }

        private SmartCollection<Oos> GetOOSs(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
        {
            try
            {
                SmartCollection<Oos> resultList = new SmartCollection<Oos>();

                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetOOSs";
                            DbCommand.Parameters.Clear();
                            dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
                            DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in returnDT.Rows)
                            {
                                Oos result = new Oos();
                                result.OosId = Convert.ToInt32(row["OOSID"]);
                                if (row["ARLNumber"] != DBNull.Value)
                                    result.ARLNumber = (int)row["ARLNumber"];
                                if (row["SampleTestId"] != DBNull.Value)
                                result.SampleTestId = (int)row["SampleTestId"];
                                result.ClientName = row["ClientName"].ToString();
                                result.Status = row["status"] != DBNull.Value ? (SampleTestStatus)row["status"] : SampleTestStatus.UnderInvestigation;
                                result.TestName = row["TestName"].ToString();
                                result.AnalyteName = row["AnalyteName"].ToString();
                                result.PriorityName = row["PriorityName"].ToString();
                                if (row["DueDate"] != DBNull.Value)
                                    result.DueDate = (DateTime)row["DueDate"];
                                result.DepartmentName = row["DepartmentName"].ToString();
                                if (row["TimepointStudyYN"] != DBNull.Value)
                                    result.TimepointStudyYN = (bool)row["TimepointStudyYN"];
                                if (row["NextTimepoint"] != DBNull.Value)
                                    result.NextTimepoint = (DateTime)row["NextTimepoint"];

                                result.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
                                result.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
                                result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
                                result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
                                result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
                                result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32();

                                // result.Results =

                                resultList.Add(result);
                            }

                            returnDT = null;
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }
            catch
            {
                throw;
            }
        }

//.........这里部分代码省略.........
                                @"
                                    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<Order> SearchOrders(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification)
        {
            try
            {
                SmartCollection<Order> resultList = new SmartCollection<Order>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            string sql = @"
                                        select distinct orders.id,orders.parentid,orders.ponumber, orders.received_date,orders.start_date,
                                        orders.due_date,orders.report_date, orders.status,
                                        (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser,
                                        orders.modified_by, orders.modified_date, orders.created_by, orders.created_date,
                                        (select sum(amount) from orders_charges where parentid = orders.id and delete_date IS NULL) as ChargesTotal,
                                        (select sum(orders_samples_tests.item_price) from orders_samples_tests where orders_samples_tests.parentid = orders.id and orders_samples_tests.delete_date IS NULL and orders_samples_tests.status < 7 ) as TestsTotal
                                        from orders
                                        LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
                                        LEFT JOIN orders_samples_containers as containers ON containers.parentid = samples.id
                                        LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
                                        LEFT JOIN list.departments as dept ON dept.departmentid = tests.departmentid
                                        LEFT JOIN customers ON customers.id = orders.parentid
                                        LEFT JOIN [User] AS users ON orders.modified_by = users.UserID
                                        LEFT JOIN [User] as users2 ON orders.created_by = users2.UserID " +
                                         SysLib.BuildSearchAllWhereClause(searchString, searchItems);

                            if (identification.ClientYN)
                            {
                                sql += " and customers.id = @companyId ";
                                DbCommand.Parameters.Add("@companyId", System.Data.SqlDbType.Int).Value = identification.ClientId;
                            }

                            sql += " AND orders.delete_date IS NULL ";
                            sql += "ORDER BY orders.modified_date DESC ";

                            DbCommand.CommandText = sql;
                            DataTable customerDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in customerDT.Rows)
                            {
                                Order order = new Order();
                                order.Id = Convert.ToInt32(row["Id"]);
                                order.ParentId = row["parentid"] != DBNull.Value ? (int)row["parentid"] : 0;
                                order.Status = row["status"] != DBNull.Value ? (EnumOrderStatus)row["status"] : EnumOrderStatus.Open;
                                order.PoNumber = row["ponumber"].ToString();
                                order.OrderTotal = Convert.ToDecimal(row["ChargesTotal"] != DBNull.Value ? (decimal)row["ChargesTotal"] : 0) + Convert.ToDecimal(row["TestsTotal"] != DBNull.Value ? (decimal)row["TestsTotal"] : 0);
                                if (row["received_date"] != DBNull.Value)
                                    order.ReceivedDate = (DateTime)row["received_date"];
                                if (row["start_date"] != DBNull.Value)
                                    order.StartDate = (DateTime)row["start_date"];
                                if (row["due_date"] != DBNull.Value)
                                    order.DueDate = (DateTime)row["due_date"];
                                if (row["report_date"] != DBNull.Value)
                                    order.ReportDate = (DateTime)row["report_date"];
                                order.CreatedUser = row["createduser"].ToString();
                                order.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0;
                                order.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
                                order.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0;
                                order.ModifiedUser = row["modifieduser"].ToString();
                                order.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
                                order.Samples = this.GetSamples(ref dbConnection, ref dbCommand, "", identification);
                                order.SampleTests = this.GetSampleTests(ref dbConnection, ref dbCommand, order.Id, true, identification);
                                using (ClientDAO dao = new ClientDAO())
                                {
                                    order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
                                }

                                resultList.Add(order);
                            }
                            customerDT = null;
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }
            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;
            }
        }

        private SmartCollection<SampleTest> GetSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, bool loadCanceled, Identification identification)
        {
            try
            {
                SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "uspGetSampleTests";
                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
                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);
                        }
                    }*/

                    sampleTest.SampleTestId = row["SampleTestID"] != DBNull.Value ? (int)row["SampleTestID"] : new Int32();
                    sampleTest.ARLNumber = row["ARLNumber"] != DBNull.Value ? (int)row["ARLNumber"] : new Int32();
                    sampleTest.Status = row["Status"] != DBNull.Value ? (SampleTestStatus)row["Status"] : new Int32();
                    sampleTest.Stage = row["Stage"] != DBNull.Value ? (SampleTestStage)row["Stage"] : new Int32();
                    sampleTest.Type = row["Type"] != DBNull.Value ? (SampleTestType)row["Type"] : new Int32();
                    sampleTest.OriginalSampleTestId = row["OriginalSampleTestID"] != DBNull.Value ? (int?)row["OriginalSampleTestID"] : new Int32();
                    sampleTest.CatalogId = row["CatalogID"] != DBNull.Value ? (int)row["CatalogID"] : new Int32();
                    sampleTest.TestId = row["TestID"] != DBNull.Value ? (int)row["TestID"] : new Int32();
                    sampleTest.TestName = row["TestName"] != DBNull.Value ? row["TestName"].ToString() : null;
                    sampleTest.AnalyteId = row["AnalyteID"] != DBNull.Value ? (int)row["AnalyteID"] : new Int32();
                    sampleTest.AnalyteName = row["AnalyteName"] != DBNull.Value ? row["AnalyteName"].ToString() : null;
                    sampleTest.ControlledYN = row["ControlledYN"] != DBNull.Value ? (bool)row["ControlledYN"] : false;
                    sampleTest.AntibioticYN = row["AntibioticYN"] != DBNull.Value ? (bool)row["AntibioticYN"] : false;
                    if (sampleTest.TestId != null && sampleTest.TestId != 0)
                        sampleTest.Test = new Test() { TestId = sampleTest.TestId, TestName = row["TestName"].ToString()};
                    sampleTest.DepartmentId = row["DepartmentID"] != DBNull.Value ? (int)row["DepartmentId"] : new Int32();
                    sampleTest.DepartmentName = row["DepartmentName"] != DBNull.Value ? row["DepartmentName"].ToString() : null;
                    if (sampleTest.DepartmentId != null && sampleTest.DepartmentId != 0)
                        sampleTest.Department = new Department() { DepartmentId = sampleTest.DepartmentId, DepartmentName = sampleTest.DepartmentName };
                    sampleTest.AnalystId = row["AnalystID"] != DBNull.Value ? (int)row["AnalystID"] : new Int32();
                    sampleTest.AnalystName = row["AnalystName"] != DBNull.Value ? row["AnalystName"].ToString() : null;
                    if (sampleTest.AnalystId != null && sampleTest.AnalystId != 0)
                        sampleTest.Analyst = new User() { UserId = sampleTest.AnalystId, FirstName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(0, sampleTest.AnalystName.IndexOf(' ')) : sampleTest.AnalystName, LastName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(sampleTest.AnalystName.IndexOf(' ') + 1, sampleTest.AnalystName.Length - sampleTest.AnalystName.IndexOf(' ') - 1) : "" };
                    sampleTest.Containers = row["Containers"] != DBNull.Value ? row["Containers"].ToString() : null;
                    sampleTest.MethodId = row["MethodID"] != DBNull.Value ? (int)row["MethodID"] : new Int32();
                    sampleTest.MethodName = row["MethodName"] != DBNull.Value ? row["MethodName"].ToString() : null;
                    sampleTest.MethodNumberId = row["MethodNumberID"] != DBNull.Value ? (int)row["MethodNumberID"] : new Int32();
                    sampleTest.MethodNumberName = row["MethodNumberName"] != DBNull.Value ? row["MethodNumberName"].ToString() : null;
                    sampleTest.OutsourcedYN = row["OutsourcedYN"] != DBNull.Value ? (bool)row["OutsourcedYN"] : false;
                    sampleTest.BasePrice = row["BasePrice"] != DBNull.Value ? (decimal)row["BasePrice"] : new Decimal();
                    if (row["LowSpec"] != DBNull.Value)
                        sampleTest.LowSpec = Convert.ToDecimal(row["LowSpec"]);
                    if (row["HighSpec"] != DBNull.Value)
                        sampleTest.HighSpec = Convert.ToDecimal(row["HighSpec"]);
                    sampleTest.CalculatePercentageExpectedYN = row["CalculatePercentageExpectedYN"] != DBNull.Value ? (bool)row["CalculatePercentageExpectedYN"] : false;
                    sampleTest.OtherSpec = row["OtherSpec"] != DBNull.Value ? row["OtherSpec"].ToString() : null;
                    sampleTest.TestMinutes = row["TestMinutes"] != DBNull.Value ? (short)row["TestMinutes"] : new Int16();
                    sampleTest.EquipmentMinutes = row["EquipmentMinutes"] != DBNull.Value ? (short)row["EquipmentMinutes"] : new Int16();
                    sampleTest.AccountingCode = row["AccountingCode"] != DBNull.Value ? row["AccountingCode"].ToString() : null;
                    sampleTest.Instructions = row["Instructions"] != DBNull.Value ? row["Instructions"].ToString() : null;
                    sampleTest.RequirementYN = row["RequirementYN"] != DBNull.Value ? (bool)row["RequirementYN"] : false;
                    sampleTest.RequirementDescription = row["RequirementDescription"] != DBNull.Value ? row["RequirementDescription"].ToString() : null;
                    sampleTest.EndotoxinLimit = row["EndotoxinLimit"] != DBNull.Value ? (decimal)row["EndotoxinLimit"] : new Decimal();
                    sampleTest.EndotoxinLimitUOM = row["EndotoxinLimitUOM"] != DBNull.Value ? row["EndotoxinLimitUOM"].ToString() : null;
                    sampleTest.AverageWeight = row["AverageWeight"] != DBNull.Value ? (int)row["AverageWeight"] : new Int32();
                    sampleTest.AverageWeightUOM = row["AverageWeightUOM"] != DBNull.Value ? row["AverageWeightUOM"].ToString() : null;
                    sampleTest.DosePerHour = row["DosePerHour"] != DBNull.Value ? (decimal)row["DosePerHour"] : new Decimal();
                    sampleTest.DosePerHourUOM = row["DosePerHourUOM"] != DBNull.Value ? row["DosePerHourUOM"].ToString() : null;
                    sampleTest.RouteOfAdministration = row["RouteOfAdministration"] != DBNull.Value ? row["RouteOfAdministration"].ToString() : null;
                    sampleTest.SignedYN = row["SignedYN"] != DBNull.Value ? (bool)row["SignedYN"] : false;
                    sampleTest.Articles = row["Articles"] != DBNull.Value ? (int)row["Articles"] : new Int32();
                    sampleTest.SignedName = row["SignedName"] != DBNull.Value ? row["SignedName"].ToString() : null;
                    sampleTest.PriorityId = row["PriorityID"] != DBNull.Value ? (int)row["PriorityID"] : new Int32();
                    if (sampleTest.PriorityId != null && sampleTest.PriorityId != 0)
                        using (SystemDAO systemDao = new SystemDAO())
                        {
                            sampleTest.Priority = systemDao.GetPriority(sampleTest.PriorityId);
                        }
                    sampleTest.AdditionalDays = row["AdditionalDays"] != DBNull.Value ? (int)row["AdditionalDays"] : new Int32();
                    sampleTest.PriceAdjustment = row["PriceAdjustment"] != DBNull.Value ? (decimal)row["PriceAdjustment"] : new Decimal();
                    sampleTest.DiscountAllowedYN = row["DiscountAllowedYN"] != DBNull.Value ? (bool)row["DiscountAllowedYN"] : false;
                    sampleTest.BillGroup = row["BillGroup"] != DBNull.Value ? (int)row["BillGroup"] : new Int32();
                    sampleTest.BeginDate = row["BeginDate"] != DBNull.Value ? (DateTime)row["BeginDate"] : new DateTime();
                    sampleTest.DueDate = row["DueDate"] != DBNull.Value ? (DateTime)row["DueDate"] : new DateTime();
                    sampleTest.ApprovedBy = row["ApprovedBy"] != DBNull.Value ? (int)row["ApprovedBy"] : new Int32();
                    sampleTest.ApprovedDate = row["ApprovedDate"] != DBNull.Value ? (DateTime)row["ApprovedDate"] : new DateTime();
                    sampleTest.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
                    sampleTest.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
                    sampleTest.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
                    sampleTest.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
                    sampleTest.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
//.........这里部分代码省略.........