C# MsSqlPersistence.IsConnected方法代码示例

 public int SaveOrderInvoiceItems(Invoice invoice, Identification identification)
 {
     try
     {
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
         {
             if (DbConnection.IsConnected())
             {
                 using (DbCommand)
                 {
                     return this.SaveOrderInvoiceItems(ref dbConnection, ref dbCommand, invoice, identification);
                 }
             }
             else
             {
                 throw new Exception("Unable to Connect");
             }
         }
     }
     catch
     {
         throw;
     }
 }

        public SmartCollection<Client> GetClientsRecent(int userId)
        {
            try {
                SmartCollection<Client> resultList = new SmartCollection<Client>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientsRecent";
                            DbCommand.Parameters.Clear();
                            DataTable clientDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in clientDT.Rows)
                            {
                                Client client = new Client();
                                client.ClientId = Convert.ToInt32(row["ClientID"]);
                                client.AccountingId = row["AccountingID"].ToString();
                                client.ClientName = row["ClientName"].ToString();
                                client.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
                                if (client.TermId != null && client.TermId != -1)
                                    client.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
                                client.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
                                client.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
                                client.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
                                client.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
                                client.BillingAddress = row["BillingAddress"].ToString();
                                client.BillingCity = row["BillingCity"].ToString();
                                client.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
                                if (client.BillingStateId != null && client.BillingStateId != -1)
                                    client.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
                                client.BillingZip = row["BillingZip"].ToString();
                                client.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
                                if (client.BillingCountryId != null && client.BillingCountryId != -1)
                                    client.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
                                client.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
                                client.ShippingAddress = row["ShippingAddress"].ToString();
                                client.ShippingCity = row["ShippingCity"].ToString();
                                client.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
                                if (client.ShippingStateId != null && client.ShippingStateId != -1)
                                    client.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
                                client.ShippingZip = row["ShippingZip"].ToString();
                                client.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
                                if (client.ShippingCountryId != null && client.ShippingCountryId != -1)
                                    client.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
                                client.CreatedUser = row["CreatedUser"].ToString();
                                client.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
                                client.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                                client.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
                                client.ModifiedUser = row["ModifiedUser"].ToString();
                                client.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;

                                // Other client objects
                                client.Contacts = GetContacts(client.ClientId);
                                client.Prices = GetClientPricings(client.ClientId);
                                client.Documents = GetClientDocuments(client.ClientId);
                                client.Notes = GetClientNotes(client.ClientId);
                                client.Complaints = GetClientComplaints(client.ClientId);

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

 public Client GetClient(int id)
 {
     try {
         Client result = new Client();
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
             if (DbConnection.IsConnected()) {
                 using (DbCommand) {
                     result = this.GetClient(ref dbConnection, ref dbCommand, id);
                 }
             }else {
                 throw new Exception("Unable to Connect");
             }
         }
         return result;
     }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;
            }
        }

        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 SmartCollection<ClientComplaint> GetClientComplaintsOpen(Identification identification)
        {
            try
            {
                SmartCollection<ClientComplaint> resultList = new SmartCollection<ClientComplaint>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientComplaintsOpen";
                            DbCommand.Parameters.Clear();

                            DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in returnDT.Rows)
                            {
                                ClientComplaint complaint = new ClientComplaint();
                                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();
                                if (row["CorrectiveActionDate"] != DBNull.Value)
                                    complaint.CorrectiveActionDate = (DateTime)row["CorrectiveActionDate"];
                                else
                                    complaint.CorrectiveActionDate = 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;
                                resultList.Add(complaint);
                            }
                            returnDT = null;
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }
            catch
            {
                throw;
            }
        }

        public byte[] GetClientDocumentData(int clientDocumentId)
        {
            try {
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientDocumentData";
                            DbCommand.Parameters.Clear();
                            DbCommand.Parameters.AddWithValue("@ClientDocumentId", clientDocumentId);
                            var result = DbConnection.ExecuteScalar(DbCommand);
                            if (result != null)
                                return result as byte[];
                            else
                                return new byte[0];
                        }
                    }
                }

                return null;
            }catch {
                throw;
            }
        }

        public SmartCollection<SampleTest> SearchResultSampleTests(string searchString, Identification identification)
        {
            try
            {
                SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
                string containerSearch = string.Empty;
                int? labNumber = null;
                int? sampleTestId = null;
                try
                {
                    if (searchString.Contains("-"))
                    {
                        containerSearch = searchString;
                    }
                    else if (searchString.Contains(","))
                    {
                        char[] charSeparators = new char[] { ',' };
                        string[] sepString = searchString.Split(charSeparators, StringSplitOptions.None);
                        labNumber = Convert.ToInt32(sepString[0].Trim());
                        sampleTestId = Convert.ToInt32(sepString[1].Trim());
                    }
                    else
                    {
                        labNumber = Convert.ToInt32(searchString.Trim());
                    }
                }
                catch (Exception)
                {
                    throw new Exception("Search value is an invalid format");
                }

                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            string sql =
                                @"
                                    SELECT DISTINCT
                                    sampleTests.id,timepoints.id as timePointId, sampleTests.parentid,sampleTests.status,sampleTests.sampleid,sampleTests.lab_number,
                                    sampleTests.priorityid,sampleTests.typeid,sampleTests.analyteid,sampleTests.testid,
                                    sampleTests.departmentid,sampleTests.analystid,sampleTests.method_name,sampleTests.method_number_name,
                                    sampleTests.low,sampleTests.high,sampleTests.test_minutes, sampleTests.equipment_minutes,
                                    sampleTests.accounting_code,dbo.ReturnNextBusinessDay(sampleTests.begin_date,timepoints.begindate_days) AS begin_date,
                                    sampleTests.due_date,sampleTests.has_requirement_code,
                                    sampleTests.requirement_code,sampleTests.item_price,sampleTests.rush_charge,sampleTests.bill_groupid,
                                    sampleTests.catalogid, sampleTests.methodid, sampleTests.methodnumberid,
                                    sampleTests.is_per_analyte, sampleTests.is_price_table,
                                    priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template,
                                    test.testname,test.active as testactive,analyte.analytename,analyte.controlled,
                                    analyte.active as analyteactive,analyst.firstname, analyst.lastname,
                                    sampleTests.endotoxin_limit,sampleTests.endotoxin_limit_uom, sampleTests.avg_weight, sampleTests.avg_weight_uom,
                                    sampleTests.dose_per_hour, sampleTests.dose_per_hour_uom,sampleTests.route_of_administration,
                                    sampleTests.articles,sampleTests.is_signed, timepoints.begindate_days,
                                    (users.firstname + ' ' + users.lastname) as modifieduser,
                                    (users2.firstname + ' ' + users2.lastname) as createduser,
                                    sampleTests.modified_by, sampleTests.modified_date, sampleTests.created_by, sampleTests.created_date,
                                    timepoints.timepoint_type
                                    FROM orders_samples_tests AS sampleTests
                                    RIGHT JOIN  orders_samples_tests_timepoints AS timepoints ON timepoints.id = (
                                        SELECT TOP 1
                                        timepoints.id
                                        FROM orders_samples_tests_timepoints AS timepoints
                                        LEFT OUTER JOIN  orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid
                                        WHERE
                                        timepointsResults.result_date IS NULL AND timepoints.parentid = sampleTests.id
                                        ORDER BY timepoints.begindate_days
                                    )
                                    LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON oos.id = timepoints.oosid AND oos.is_testing_complete = 'true'  -- Continue Testing
                                    LEFT JOIN  [User] as users ON sampleTests.modified_by = users.UserID
                                    LEFT JOIN  [User] as users2 ON sampleTests.created_by = users2.UserID
                                    LEFT JOIN  [User] as analyst ON sampleTests.analystid = analyst.UserID
                                    LEFT JOIN  list.departments as dpart ON sampleTests.[departmentid] = dpart.[departmentid]
                                    LEFT JOIN  list.tests as test ON sampleTests.[testid] = test.[testid]
                                    LEFT JOIN  list.analyte as analyte ON sampleTests.[analyteid] = analyte.[analyteid]
                                    LEFT JOIN  list.priorities as priorities ON sampleTests.[priorityid] = priorities.[priorityid]
                                    LEFT JOIN orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id
                                    WHERE ((sampleTests.status = 2 OR sampleTests.status = 3)  AND sampleTests.delete_date IS NULL AND oos.id IS NULL)
                                    ";

                            // Maybe Limit Results by Analyst and/or Department

                            DbCommand.Parameters.Clear();

                            if (!String.IsNullOrWhiteSpace(containerSearch))
                            {
                                sql += "AND containers.containerid = @ContainerId ";
                                DbCommand.Parameters.Add("@ContainerId", System.Data.SqlDbType.VarChar, 100).Value = containerSearch;
                            }
                            else if (labNumber != null && sampleTestId != null)
                            {
                                sql += "AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId ";
                                DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
                                DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
                            }
                            else
                            {
                                sql += "AND sampleTests.lab_number = @LabNumber ";
                                DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
//.........这里部分代码省略.........

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

        public SmartCollection<Order> SearchModuleOrders(int? customerId, DateTime? orderReceivedStartDate, DateTime? orderReceivedEndDate, int? testId, int? analyteId)
        {
            try
            {
                var searchCustomers = customerId != null && customerId > 0;
                var searchDates = orderReceivedStartDate != null || orderReceivedEndDate != null;
                var searchTests = testId != null && testId > 0;
                var serachAnalytes = analyteId != null && analyteId > 0;

                var result = new SmartCollection<Order>();

                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            string sql = @"
                                select distinct orders.id, orders.parentid, customers.customer_name, orders.received_date, orders.status
                                from orders
                                LEFT JOIN  customers ON customers.id = orders.parentid
                                ";

                            if (searchTests)
                            {
                                sql += @"
                                    LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
                                    LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
                                    ";
                            }

                            if (serachAnalytes)
                            {
                                // NOTE: joining to seperate order_samples / order_samples_tests table aliases
                                //       is so searching tests and analytes can be be done independently or concurrently
                                sql += @"
                                    LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id
                                    LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id
                                    LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id
                                    ";
                            }

                            sql += @"
                                WHERE orders.delete_date IS NULL
                                ";

                            if (searchCustomers)
                            {
                                sql += @"
                                    AND customers.id = @customerId
                                    ";
                            }

                            if (searchDates)
                            {
                                sql += @"
                                    AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate)
                                    ";
                            }

                            if (searchTests)
                            {
                                sql += @"
                                    AND tests.testid = @testId
                                    ";
                            }

                            if (serachAnalytes)
                            {
                                sql += @"
                                    AND analytes.analyteid = @analyteId
                                    ";
                            }

                            sql += @"
                                ORDER BY orders.received_date DESC
                                ";

                            DbCommand.CommandText = sql;

                            if (searchCustomers)
                            {
                                DbCommand.Parameters.AddWithValue("@customerId", customerId);
                            }

                            if (searchDates)
                            {
                                DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value));
                                DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value));
                            }

                            if (searchTests)
                            {
                                DbCommand.Parameters.AddWithValue("@testId", testId);
                            }

                            if (serachAnalytes)
                            {
                                DbCommand.Parameters.AddWithValue("@analyteId", analyteId);
                            }
//.........这里部分代码省略.........

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

        /// <summary>
        /// Save Sample
        /// </summary>
        /// <returns></returns>
        public int SaveSample(Sample sample, Identification identification)
        {
            try
            {
                int returnValue = -1;
                string sql = string.Empty;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            try
                            { // Try Catch here allows other exceptions to rollback transactions
                                if (sample.IsDirty)
                                {
                                    if (sample.ARLNumber != 0 && sample.ARLNumber != null)
                                    /*SystemDAO.SaveChangeAudit<Order>(ref dbConnection, ref dbCommand,
                                        GetSample(sample.ARLNumber, identification),
                                        sample,
                                        ModuleNames.Samples,
                                        sample.Pk,
                                        identification.UserId); */

                                    DbCommand.CommandType = CommandType.StoredProcedure;
                                    DbCommand.Parameters.Clear();
                                    if (sample.ARLNumber == 0 || sample.ARLNumber == null)
                                    {
                                        DbCommand.CommandText = "uspInsertSample";

                                        DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
                                        DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                                    }
                                    else
                                    {
                                        DbCommand.CommandText = "uspUpdateSample";

                                        DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
                                        //DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = sample.DeleteDate.HasValue ? sample.DeleteDate.Value : SqlDateTime.Null;
                                        DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
                                        DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                                    }

                                    DbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = (sample.Description != null ? sample.Description.Trim() : string.Empty).Trim();
                                    DbCommand.Parameters.Add("@ReceivedDate", System.Data.SqlDbType.DateTime).Value = sample.ReceivedDate.HasValue ? sample.ReceivedDate.Value : SqlDateTime.Null;
                                    DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = sample.ClientId.HasValue ? sample.ClientId.Value : SqlInt32.Null;
                                    DbCommand.Parameters.Add("@ClientName", System.Data.SqlDbType.NVarChar, 100).Value = sample.ClientId.HasValue ? sample.Client.ClientName.Trim() : SqlString.Null;
                                    DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)sample.Status;
                                    DbCommand.Parameters.Add("@PONumber", System.Data.SqlDbType.NVarChar, 25).Value = sample.PONumber ?? string.Empty;
                                    DbCommand.Parameters.Add("@FormulationId", System.Data.SqlDbType.NVarChar, 50).Value = sample.FormulationId != null ? sample.FormulationId.Trim() : SqlString.Null;
                                    DbCommand.Parameters.Add("@LotNumber", System.Data.SqlDbType.NVarChar, 50).Value = sample.LotNumber != null ? sample.LotNumber.Trim() : SqlString.Null;
                                    DbCommand.Parameters.Add("@ProjectNumber", System.Data.SqlDbType.NVarChar, 50).Value = sample.ProjectNumber != null ? sample.ProjectNumber.Trim() : SqlString.Null;
                                    DbCommand.Parameters.Add("@StorageLocationId", System.Data.SqlDbType.Int).Value = sample.StorageLocation.StorageLocationId != null ? (SqlInt32)sample.StorageLocation.StorageLocationId : SqlInt32.Null;
                                    DbCommand.Parameters.Add("@StorageLocationName", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.Description :  SqlString.Null;
                                    DbCommand.Parameters.Add("@StorageLocationConditions", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.Conditions : SqlString.Null;
                                    DbCommand.Parameters.Add("@StorageLocationCode", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.LocationCode : SqlString.Null;
                                    DbCommand.Parameters.Add("@RequestedStorageId", System.Data.SqlDbType.Int).Value = sample.RequestedStorageLocation.StorageLocationId != null ? (SqlInt32)sample.RequestedStorageLocation.StorageLocationId : SqlInt32.Null;
                                    DbCommand.Parameters.Add("@RequestedStorageName", System.Data.SqlDbType.NVarChar, 50).Value = sample.RequestedStorageLocation.StorageLocationId != null ? sample.RequestedStorageLocation.Description : SqlString.Null;
                                    DbCommand.Parameters.Add("@DosageId", System.Data.SqlDbType.Int).Value = sample.DosageId ?? SqlInt32.Null;
                                    DbCommand.Parameters.Add("@DosageName", System.Data.SqlDbType.NVarChar, 50).Value = sample.DosageId.HasValue ? sample.Dosage.DosageName : SqlString.Null;
                                    DbCommand.Parameters.Add("@Containers", System.Data.SqlDbType.Int).Value = sample.Containers ?? SqlInt32.Null;
                                    DbCommand.Parameters.Add("@ContainerDescription", System.Data.SqlDbType.NVarChar, 255).Value = sample.ContainerDescription != null ? sample.ContainerDescription : SqlString.Null;
                                    DbCommand.Parameters.Add("@VolumeAmount", System.Data.SqlDbType.Decimal).Value = sample.VolumeAmount ?? SqlDecimal.Null;
                                    DbCommand.Parameters.Add("@VolumeUOMID", System.Data.SqlDbType.Int).Value = sample.VolumeUnitOfMeasure != null ? (SqlInt32)sample.VolumeUnitOfMeasure.UomId : SqlInt32.Null;
                                    DbCommand.Parameters.Add("@VolumeUOM", System.Data.SqlDbType.NVarChar, 50).Value = sample.VolumeUnitOfMeasure != null ? sample.VolumeUnitOfMeasure.Uom : SqlString.Null;
                                    DbCommand.Parameters.Add("@TimepointStudyYN", System.Data.SqlDbType.Bit).Value = sample.TimepointStudyYN;
                                    DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = sample.GMPYN;
                                    DbCommand.Parameters.Add("@CompoundedBy", System.Data.SqlDbType.NVarChar, 50).Value = sample.CompoundedBy != null ? sample.CompoundedBy : SqlString.Null;
                                    DbCommand.Parameters.Add("@CompoundedDate", System.Data.SqlDbType.DateTime).Value = sample.CompoundedDate.HasValue ? sample.CompoundedDate.Value : SqlDateTime.Null;

                                    if (sample.ARLNumber > 0)
                                        returnValue = DbConnection.ExecuteCommand(DbCommand);
                                    else
                                    {
                                        // returnValue = Primary Key Id
                                        returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
                                        sample.ARLNumber = returnValue;
                                    }
                                }
                                // Save Order Sample Analytes
                                this.SaveSampleAnalytes(ref dbConnection, ref dbCommand, ref sample, identification.UserId);

                                // Save Order Charges
                                this.SaveSampleCharges(ref dbConnection, ref dbCommand, ref sample, identification.UserId);

                                // Save Documents
                                this.SaveSampleDocuments(ref dbConnection, ref dbCommand, sample, identification.UserId);

                                // Save Notes
                                this.SaveSampleNotes(ref dbConnection, ref dbCommand, ref sample, identification);

                                // Save Sample Tests
                                this.SaveSampleTests(ref dbConnection, ref dbCommand, ref sample, identification);

                                // Release Lock
                                using (SystemDAO systemDao = new SystemDAO())
//.........这里部分代码省略.........

        public int SaveReport(ReportRecord report)
        {
            int returnValue = -1;
            try
            {
                string sql = string.Empty;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            sql = @"
                                INSERT INTO orders_reports ( parentid, referenceid,departmentid,report_type, report_name, report_data, created_by,created_date)
                                VALUES ( @ParentId,@ReferenceId,@DepartmentId, @ReportType,@ReportName, @ReportData, @CreatedBy, @CreatedDate);
                                SELECT id FROM orders_reports WHERE (id = SCOPE_IDENTITY())
                                ";

                            DbCommand.Parameters.Clear();
                            DbCommand.CommandText = sql;
                            DbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = report.ParentId;
                            DbCommand.Parameters.Add("@ReferenceId", System.Data.SqlDbType.Int).Value = report.ReferenceId;
                            DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = report.DepartmentId ?? 0;
                            DbCommand.Parameters.Add("@ReportType", System.Data.SqlDbType.VarChar, 3).Value = report.ReportType;
                            DbCommand.Parameters.Add("@ReportName", System.Data.SqlDbType.VarChar, 30).Value = report.ReportName;
                            DbCommand.Parameters.Add("@ReportData", System.Data.SqlDbType.VarBinary, -1).Value = report.ReportData;
                            DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = report.CreatedBy;
                            DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = report.CreatedDate;

                            returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
            }
            catch
            {
                throw;
            }
            return returnValue;
        }

 public int SaveOrderSampleTestOos(Oos oos, Identification identification)
 {
     try
     {
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
         {
             if (DbConnection.IsConnected())
             {
                 using (DbCommand)
                 {
                     return this.SaveOrderSampleTestOos(ref dbConnection, ref dbCommand, oos, identification);
                 }
             }
             else
             {
                 throw new Exception("Unable to Connect");
             }
         }
     }
     catch
     {
         throw;
     }
 }

 public int SaveClientComplaint(ClientComplaint complaint, Identification identification)
 {
     int result = 0;
     try
     {
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
         {
             if (DbConnection.IsConnected())
             {
                 using (DbCommand)
                 {
                     result = this.SaveClientComplaint(ref dbConnection, ref dbCommand, complaint, identification);
                 }
             }
             else
             {
                 throw new Exception("Unable to Connect");
             }
         }
         return result;
     }
     catch
     {
         throw;
     }
 }