C# MsSqlPersistence.ExecuteScalar方法代码示例

        private string GetOOSCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
        {
            try
            {
                int returnValue = 0;

                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.Parameters.Clear();
                dbCommand.CommandText = "uspGetOOSCount";
                returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
                return returnValue <= 0 ? string.Empty : returnValue.ToString();
            }
            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())
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                        else
                            dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = SampleTestStatus.InProgress;
                        dbCommand.Parameters.Add("@Stage", System.Data.SqlDbType.Int).Value = SampleTestStage.AccInReview;
                        dbCommand.Parameters.Add("@Type", System.Data.SqlDbType.Int).Value = sampleTest.Type;
                        dbCommand.Parameters.Add("@CatalogId", System.Data.SqlDbType.Int).Value = sampleTest.CatalogId ?? sampleTest.CatalogId;
                        dbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = sampleTest.TestId ?? sampleTest.TestId;
                        dbCommand.Parameters.Add("@TestName", System.Data.SqlDbType.NVarChar, 250).Value = sampleTest.TestName ?? sampleTest.TestName;
                        dbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = sampleTest.Analyte.AnalyteId != null ? sampleTest.Analyte.AnalyteId : sampleTest.AnalyteId != null ? sampleTest.AnalyteId : null;
                        dbCommand.Parameters.Add("@AnalyteName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.AnalyteName ?? sampleTest.AnalyteName;
                        dbCommand.Parameters.Add("@ControlledYN", System.Data.SqlDbType.Bit).Value = sampleTest.ControlledYN != null ? (bool)sampleTest.ControlledYN : false;
                        dbCommand.Parameters.Add("@AntibioticYN", System.Data.SqlDbType.Bit).Value = sampleTest.AntibioticYN != null ? (bool)sampleTest.AntibioticYN : false;
                        dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = sampleTest.Department.DepartmentId ?? sampleTest.Department.DepartmentId;
                        dbCommand.Parameters.Add("@DepartmentName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.Department.DepartmentName ?? sampleTest.Department.DepartmentName;
                        dbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = sampleTest.Analyst.UserId != null ? (int)sampleTest.Analyst.UserId : SqlInt32.Null;
                        dbCommand.Parameters.Add("@AnalystName", System.Data.SqlDbType.NVarChar, 100).Value = sampleTest.Analyst.FullName != null ? sampleTest.Analyst.FullName : SqlString.Null;
                        dbCommand.Parameters.Add("@Containers", System.Data.SqlDbType.NVarChar, 4000).Value = sampleTest.Containers != null ? sampleTest.Containers : SqlString.Null;
                        dbCommand.Parameters.Add("@MethodId", System.Data.SqlDbType.Int).Value = sampleTest.MethodId ?? sampleTest.MethodId;
                        dbCommand.Parameters.Add("@MethodName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.MethodName ?? sampleTest.MethodName;
                        dbCommand.Parameters.Add("@MethodNumberId", System.Data.SqlDbType.Int).Value = sampleTest.MethodNumberId ?? sampleTest.MethodNumberId;
                        dbCommand.Parameters.Add("@MethodNumberName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.MethodNumberName ?? sampleTest.MethodNumberName;
                        dbCommand.Parameters.Add("@OutsourcedYN", System.Data.SqlDbType.Bit).Value = sampleTest.OutsourcedYN != null ? (bool)sampleTest.OutsourcedYN : false;
                        dbCommand.Parameters.Add("@BasePrice", System.Data.SqlDbType.Money).Value = sampleTest.BasePrice != null ? sampleTest.BasePrice : SqlMoney.Null;
                        dbCommand.Parameters.Add("@LowSpec", System.Data.SqlDbType.Decimal).Value = sampleTest.LowSpec != null ? Convert.ToDecimal(sampleTest.LowSpec) : SqlDecimal.Null;
                        dbCommand.Parameters.Add("@HighSpec", System.Data.SqlDbType.Decimal).Value = sampleTest.HighSpec != null ? Convert.ToDecimal(sampleTest.HighSpec) : SqlDecimal.Null;
                        dbCommand.Parameters.Add("@CalculatePercentageExpectedYN", System.Data.SqlDbType.Bit).Value = sampleTest.CalculatePercentageExpectedYN != null ? (bool)sampleTest.CalculatePercentageExpectedYN : false;
                        dbCommand.Parameters.Add("@OtherSpec", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.OtherSpec ?? sampleTest.OtherSpec;
                        dbCommand.Parameters.Add("@TestMinutes", System.Data.SqlDbType.SmallInt).Value = sampleTest.TestMinutes ?? sampleTest.TestMinutes;
                        dbCommand.Parameters.Add("@EquipmentMinutes", System.Data.SqlDbType.SmallInt).Value = sampleTest.EquipmentMinutes ?? sampleTest.EquipmentMinutes;
                        dbCommand.Parameters.Add("@AccountingCode", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.AccountingCode ?? sampleTest.AccountingCode;
                        dbCommand.Parameters.Add("@Instructions", System.Data.SqlDbType.NVarChar, 1024).Value = sampleTest.Instructions ?? sampleTest.Instructions;
                        dbCommand.Parameters.Add("@RequirementYN", System.Data.SqlDbType.Bit).Value = sampleTest.RequirementYN != null ? (bool)sampleTest.RequirementYN : false;
                        dbCommand.Parameters.Add("@RequirementDescription", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.RequirementDescription ?? sampleTest.RequirementDescription;
                        dbCommand.Parameters.Add("@EndotoxinLimit", System.Data.SqlDbType.Decimal).Value = sampleTest.EndotoxinLimit != null ? (decimal)sampleTest.EndotoxinLimit : SqlDecimal.Null;
                        dbCommand.Parameters.Add("@EndotoxinLimitUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.EndotoxinLimitUOM ?? sampleTest.EndotoxinLimitUOM;
                        dbCommand.Parameters.Add("@AverageWeight", System.Data.SqlDbType.Int).Value = sampleTest.AverageWeight ?? sampleTest.AverageWeight;
                        dbCommand.Parameters.Add("@AverageWeightUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.AverageWeightUOM ?? sampleTest.AverageWeightUOM;
                        dbCommand.Parameters.Add("@DosePerHour", System.Data.SqlDbType.Decimal).Value = sampleTest.DosePerHour != null ? (decimal)sampleTest.DosePerHour : SqlDecimal.Null;
                        dbCommand.Parameters.Add("@DosePerHourUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.DosePerHourUOM ?? sampleTest.DosePerHourUOM;
                        dbCommand.Parameters.Add("@RouteOfAdministration", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.RouteOfAdministration ?? sampleTest.RouteOfAdministration;
                        dbCommand.Parameters.Add("@SignedYN", System.Data.SqlDbType.Bit).Value = sampleTest.SignedYN != null ? (bool)sampleTest.SignedYN : false;
                        dbCommand.Parameters.Add("@Articles", System.Data.SqlDbType.Int).Value = sampleTest.Articles;
                        dbCommand.Parameters.Add("@SignedName", System.Data.SqlDbType.NVarChar, 100).Value = sampleTest.SignedName ?? sampleTest.SignedName;
                        dbCommand.Parameters.Add("@PriorityId", System.Data.SqlDbType.Int).Value = sampleTest.Priority.PriorityId ?? (int)sampleTest.Priority.PriorityId;
                        dbCommand.Parameters.Add("@AdditionalDays", System.Data.SqlDbType.Int).Value = sampleTest.Priority.PlusDays != null ? (int)sampleTest.Priority.PlusDays : SqlInt32.Null;
                        dbCommand.Parameters.Add("@PriceAdjustment", System.Data.SqlDbType.Decimal).Value = sampleTest.Priority.PriceAdjustment != null ? (decimal)sampleTest.Priority.PriceAdjustment : SqlDecimal.Null;
                        dbCommand.Parameters.Add("@DiscountAllowedYN", System.Data.SqlDbType.Bit).Value = sampleTest.DiscountAllowedYN != null ? (bool)sampleTest.DiscountAllowedYN : false;
                        dbCommand.Parameters.Add("@BillGroup", System.Data.SqlDbType.Int).Value = sampleTest.BillGroup ?? sampleTest.BillGroup;
                        dbCommand.Parameters.Add("@BeginDate", System.Data.SqlDbType.DateTime).Value = sampleTest.BeginDate.Value > DateTime.MinValue ? (DateTime)sampleTest.BeginDate : SqlDateTime.Null;
                        dbCommand.Parameters.Add("@DueDate", System.Data.SqlDbType.DateTime).Value = sampleTest.DueDate.Value > DateTime.MinValue ? (DateTime)sampleTest.DueDate : SqlDateTime.Null;
                        //dbCommand.Parameters.Add("@ApprovedBy", System.Data.SqlDbType.Int).Value = sampleTest.ApprovedBy ?? sampleTest.ApprovedBy;
                        //dbCommand.Parameters.Add("@ApprovedDate", System.Data.SqlDbType.DateTime).Value = sampleTest.ApprovedDate != null && sampleTest.ApprovedDate.Value > DateTime.MinValue ? (SqlDateTime)sampleTest.ApprovedDate : SqlDateTime.Null;

                        /*if (reCalculatePricing)
                        {

                            decimal[] testPrice = Routines.Pricing.CalculateTestPrice(sampleTest, pricing, priority,
                                matrixItems,
                                sample.Samples.Where(x => x.ARLNumber == sampleTest.SampleId).SelectMany(x => x.SampleAnalytes).Where(a => a.DeleteDate == null).Count(),
                                catalogItem.BasePrice);

                            dbCommand.Parameters.Add("@ItemPrice", System.Data.SqlDbType.Decimal).Value = testPrice[0];
                            dbCommand.Parameters.Add("@RushCharge", System.Data.SqlDbType.Decimal).Value = testPrice[1];
                        }
                        else
                        {
                            dbCommand.Parameters.Add("@ItemPrice", System.Data.SqlDbType.Decimal).Value = sampleTest.BasePrice;
                            dbCommand.Parameters.Add("@RushCharge", System.Data.SqlDbType.Decimal).Value = sampleTest.PriceAdjustment;
                        } */

                        //int? oldSampleTestId = sampleTest.SampleTestId;
                        if (sampleTest.SampleTestId > 0)
                            returnValue += dbConnection.ExecuteCommand(dbCommand);
                        else
                        {
                            // returnValue = Primary Key Id
                            returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
                            sampleTest.SampleTestId = returnValue;

                            // Update New Note Records
                            //var sampleNotes = order.Notes.Where(x => x.LinkedType == EnumNoteLink.Test && x.LinkedId == oldSampleTestId);
                            //foreach (ClientNote sampleNote in sampleNotes) {
                            //    if (sampleNote != null)
                            //        sampleNote.LinkedId = sampleTest.Id;
                            //}
                        }
                        // Save Order Sample Test Containers
                        //this.SaveOrderSampleTestContainers(ref dbConnection, ref dbCommand, sampleTest, sample, identification.UserId);
                        // Save Order Sample Test Timepoints
                        //this.SaveOrderSampleTestTimePoints(ref dbConnection, ref dbCommand, sampleTest, sample, identification.UserId, true);
                    }
                }

                //Return Total Number of Updated Records or Last Primary ID
                return returnValue;
            }
            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 int SaveInventoryRecord(InventoryItem inventoryItem, Guid userToken, int userId)
        {
            try
            {
                int returnValue = -1;
                string sql = string.Empty;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            if (inventoryItem.IsDirty)
                            {
                                SystemDAO.SaveChangeAudit<InventoryItem>(ref dbConnection, ref dbCommand,
                                    GetInventoryItemRecord(ref dbConnection, ref dbCommand, inventoryItem.Pk ?? 0),
                                    inventoryItem,
                                    ModuleNames.Inventory,
                                    inventoryItem.Pk,
                                    userId);

                                DbCommand.Parameters.Clear();
                                if (inventoryItem.Id == null || inventoryItem.Id < 0)
                                {
                                    sql = @"
                                    INSERT into inventory (item_type,active, analyteid, customerid, po_number, lab_number, storageid,
                                        manufacturer, lot, cas, weight, catalog, volume_amount, ordered_for, potency,cost, customer_cost,
                                        received_date, destroyed_date, expiration_date, created_by)
                                    Values(@ItemType, @Active, @AnalyteId, @CustomerId, @PoNumber, @LabNumber, @StorageId, @Manufacturer,
                                            @Lot, @Cas, @Weight, @Catalog, @VolumeAmount, @OrderedFor, @Potency, @Cost, @CustomerCost,
                                            @ReceivedDate, @DestroyedDate, @ExpirationDate, @CreatedBy );
                                    SELECT id FROM inventory WHERE (id = SCOPE_IDENTITY())";

                                    DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
                                }
                                else
                                {
                                    sql = @"
                                    UPDATE inventory
                                    SET item_type = @ItemType, active = @Active, analyteid = @AnalyteId, customerid = @CustomerId, po_number = @PoNumber,
                                        lab_number = @LabNumber, storageid = @StorageId, manufacturer = @Manufacturer, lot = @Lot, cas = @Cas,
                                        weight = @Weight, catalog = @Catalog, volume_amount = @VolumeAmount, ordered_for = @OrderedFor,
                                        potency = @Potency, cost = @Cost, customer_cost = @CustomerCost,
                                        received_date = @ReceivedDate, destroyed_date = @DestroyedDate, expiration_date = @ExpirationDate,
                                        [email protected], delete_date = @DeleteDate
                                    WHERE id = @ID
                                    ";
                                    DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = inventoryItem.Id;
                                    DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = inventoryItem.DeleteDate.HasValue ? inventoryItem.DeleteDate.Value : SqlDateTime.Null;
                                    DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
                                }

                                DbCommand.Parameters.Add("@Active", System.Data.SqlDbType.Bit).Value = inventoryItem.Active;
                                DbCommand.Parameters.Add("@ItemType", System.Data.SqlDbType.Int).Value = (int)inventoryItem.ItemType;
                                DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = inventoryItem.LabNumber;
                                DbCommand.Parameters.Add("@CustomerId", System.Data.SqlDbType.Int).Value = inventoryItem.ClientId ?? 0;
                                DbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = inventoryItem.AnalyteId;
                                DbCommand.Parameters.Add("@StorageId", System.Data.SqlDbType.Int).Value = inventoryItem.StorageId ?? 0;
                                DbCommand.Parameters.Add("@PoNumber", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.PoNumber ?? string.Empty;
                                DbCommand.Parameters.Add("@Manufacturer", System.Data.SqlDbType.VarChar, 100).Value = inventoryItem.Manufacturer ?? string.Empty;
                                DbCommand.Parameters.Add("@Lot", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.Lot ?? string.Empty;
                                DbCommand.Parameters.Add("@Cas", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.Cas ?? string.Empty;
                                DbCommand.Parameters.Add("@Catalog", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.Catalog ?? string.Empty;
                                DbCommand.Parameters.Add("@Weight", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.Weight ?? string.Empty;
                                DbCommand.Parameters.Add("@VolumeAmount", System.Data.SqlDbType.VarChar, 50).Value = inventoryItem.VolumeAmount ?? string.Empty;
                                DbCommand.Parameters.Add("@OrderedFor", System.Data.SqlDbType.VarChar, 100).Value = inventoryItem.OrderedFor ?? string.Empty;

                                DbCommand.Parameters.Add("@Potency", System.Data.SqlDbType.Decimal).Value = inventoryItem.Potency;
                                DbCommand.Parameters.Add("@Cost", System.Data.SqlDbType.Decimal).Value = inventoryItem.Cost;
                                DbCommand.Parameters.Add("@CustomerCost", System.Data.SqlDbType.Decimal).Value = inventoryItem.ClientCost;

                                DbCommand.Parameters.Add("@ReceivedDate", System.Data.SqlDbType.DateTime).Value = inventoryItem.ReceivedDate.HasValue ? inventoryItem.ReceivedDate.Value : SqlDateTime.Null;
                                DbCommand.Parameters.Add("@DestroyedDate", System.Data.SqlDbType.DateTime).Value = inventoryItem.DestroyedDate.HasValue ? inventoryItem.DestroyedDate.Value : SqlDateTime.Null;
                                DbCommand.Parameters.Add("@ExpirationDate", System.Data.SqlDbType.DateTime).Value = inventoryItem.ExpirationDate.HasValue ? inventoryItem.ExpirationDate.Value : SqlDateTime.Null;

                                DbCommand.CommandText = sql;

                                if (inventoryItem.Id != null)
                                    returnValue = DbConnection.ExecuteCommand(DbCommand);
                                else
                                {
                                    // returnValue = Primary Key Id
                                    returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
                                    inventoryItem.Id = returnValue;
                                }
                            }

                            // Save Notes
                            this.SaveInventoryNotes(ref dbConnection, ref dbCommand, inventoryItem, userId);
                            // Release Lock
                            using (SystemDAO systemDao = new SystemDAO())
                            {
                                systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Inventory, inventoryItem.Id.ToString(), userToken);
                            }
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
//.........这里部分代码省略.........

        public int GetDatabaseVersion()
        {
            try {
                int result = 0;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {

                            string sql = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'dbversion') SELECT dbversion FROM dbversion ELSE 	SELECT 0 AS dbversion";
                            DbCommand.CommandText = sql;
                            object value = DbConnection.ExecuteScalar(DbCommand);
                            if (value != DBNull.Value)
                                result = (int)value;
                        }
                    }
                    else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return result;
            }
            catch {
                throw new Exception("The database server is offline or unreachable");
            }
        }

        public int SaveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
        {
            try
            {
                int returnValue = 0;
                foreach (Contact contact in client.Contacts)
                {
                    if (contact.IsDirty)
                    {
                        /*SystemDAO.SaveChangeAudit<Contact>(ref dbConnection, ref dbCommand,
                            GetContact(ref dbConnection, ref dbCommand, contact.Pk),
                            contact,
                            ModuleNames.Clients,
                            client.Pk,
                            userId); */

                        dbCommand.Parameters.Clear();
                        if (contact.ContactId <= 0)
                        {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspInsertContact";
                            dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
                            dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
                            dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        }
                        else
                        {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspUpdateContact";
                            dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contact.ContactId;
                            dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
                            dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
                            dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = contact.DeleteDate;
                        }

                        dbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 100).Value = contact.FirstName;
                        dbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.Text, 100).Value = contact.LastName;
                        dbCommand.Parameters.Add("@PrimaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.PrimaryEmail;
                        dbCommand.Parameters.Add("@PrimaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.PrimaryEmailSendYN;
                        dbCommand.Parameters.Add("@SecondaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.SecondaryEmail;
                        dbCommand.Parameters.Add("@SecondaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.SecondaryEmailSendYN;
                        dbCommand.Parameters.Add("@PrimaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.PrimaryPhone;
                        dbCommand.Parameters.Add("@SecondaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.SecondaryPhone;
                        dbCommand.Parameters.Add("@Fax", System.Data.SqlDbType.Text, 20).Value = contact.Fax;
                        dbCommand.Parameters.Add("@FaxSendYN", System.Data.SqlDbType.Bit).Value = contact.FaxSendYN;
                        dbCommand.Parameters.Add("@Comments", System.Data.SqlDbType.Text, 4000).Value = contact.Comments;
                        if (contact.ContactId > 0)
                        {
                            //returnValue = Number of Rows Affected
                            returnValue = dbConnection.ExecuteCommand(dbCommand);
                        }
                        else
                        {
                            // returnValue = Primary Key Id
                            returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
                            contact.ContactId = returnValue;
                        }
                    }

                }
                //Return Total Number of Inserted or Updated Records
                return returnValue;
            }
            catch
            {
                throw;
            }
        }

        public int OrderSampleTestsSetStatus(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleTestId, Identification identification, int? forceStatus = null)
        {
            try
            {
                int returnValue = 0;
                int remainingTimepoints = 0;
                int remainingOosRecords = 0;
                SampleTestStatus setStatus = SampleTestStatus.InProgress;
                string sql = string.Empty;

                if (forceStatus.IsNotNull())
                {
                    dbCommand.Parameters.Clear();
                    sql = @"
                        UPDATE orders_samples_tests
                        SET status = @Status, modified_by = @ModifiedBy
                        WHERE id = @ID;
                        ";
                    dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
                    dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = forceStatus;
                    dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
                    dbCommand.CommandText = sql;
                    returnValue = dbConnection.ExecuteCommand(dbCommand);
                    return returnValue;
                }

                dbCommand.Parameters.Clear();
                sql = @"
                        SELECT Count(timepoints.Id) as CountOfId
                        from orders_samples_tests_timepoints timepoints
                        LEFT JOIN  orders_samples_tests_timepoints_results AS result ON timepoints.id = result.parentid
                        LEFT JOIN  orders_samples_tests_timepoints_oos AS oos ON timepoints.oosid = oos.id
                        WHERE timepoints.parentid = @ID AND result.created_date IS NULL AND (oos.is_testing_complete = 'false' OR oos.is_testing_complete IS NULL)  ;
                        ";
                dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
                dbCommand.CommandText = sql;
                remainingTimepoints = (int)dbConnection.ExecuteScalar(dbCommand);

                sql = @"
                        SELECT Count(DISTINCT oos.Id) as CountOfId
                        from orders_samples_tests_timepoints_oos oos
                        LEFT JOIN  orders_samples_tests_timepoints AS timepoint ON timepoint.oosid = oos.id
                        WHERE timepoint.parentid = @ID and oos.status < 2 ;
                        ";
                dbCommand.CommandText = sql;
                remainingOosRecords = (int)dbConnection.ExecuteScalar(dbCommand);

                if (remainingTimepoints == 0 && remainingOosRecords == 0)
                    setStatus = SampleTestStatus.InProgress;

                dbCommand.Parameters.Clear();
                sql = @"
                        UPDATE orders_samples_tests
                        SET status = @Status, modified_by = @ModifiedBy
                        WHERE id = @ID;
                        ";
                dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
                dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)setStatus;
                dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
                dbCommand.CommandText = sql;
                returnValue = dbConnection.ExecuteCommand(dbCommand);
                //Return Total Number of Updated Records or Last Primary ID
                dbCommand.Parameters.Clear();
                return returnValue;
            }
            catch
            {
                throw;
            }
        }

        public bool OrderSampleTestsVerifyCanSave(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleTestId, int timePointId, Identification identification)
        {
            try
            {
                bool submissionIsValid = false;
                bool resultIsValid = false;
                string sql = string.Empty;
                // Check Submission Order must be Open Status Sample must be Available Status and Test must be either
                // Aproved or InProgress no parent records can be deleted.
                // Returns 1 is Submission is Valid
                dbCommand.Parameters.Clear();
                sql = @"
                        SELECT Count([sample].Id) AS CountOfRecords
                        FROM orders_samples AS [sample]
                        LEFT JOIN  orders AS [order] ON [order].id = [sample].parentid
                        LEFT JOIN  orders_samples_tests AS [test] ON [test].sampleid = [sample].id
                        WHERE [test].id = @SampleTestId
                        AND ([sample].[status] != 1 AND [order].[status] = 1) AND ([test].[status] = 2 OR [test].[status] = 3)
                        AND [order].delete_date IS NULL
                        AND [sample].delete_date IS NULL
                        AND [test].delete_date IS NULL
                     ;
                    ";
                dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
                dbCommand.CommandText = sql;
                submissionIsValid = Convert.ToBoolean(dbConnection.ExecuteScalar(dbCommand));

                if (submissionIsValid)
                {
                    // Return 1 record if the result for the Timepoint has NOT been entered.
                    dbCommand.Parameters.Clear();
                    sql = @"
                            SELECT Count(timepoint.id) AS CountOfRecords
                            from orders_samples_tests_timepoints AS timepoint
                            LEFT JOIN  orders_samples_tests_timepoints_results AS result ON result.parentid = timepoint.id
                            WHERE timepoint.id = @TimePointId AND result.created_date IS NULL ;
                        ";
                    dbCommand.Parameters.Add("@TimePointId", System.Data.SqlDbType.Int).Value = timePointId;
                    dbCommand.CommandText = sql;
                    resultIsValid = Convert.ToBoolean(dbConnection.ExecuteScalar(dbCommand));
                }
                return submissionIsValid && resultIsValid;
            }
            catch
            {
                throw;
            }
        }

        public int CloneSample(int arlNumber, bool descriptionYN,
                                bool storageYN, bool containersYN, bool containerDescriptionYN, bool fillVolumeYN, bool dosageYN,
                                bool analytesYN, bool testsYN, bool chargesYN, bool notesYN, Identification identification)
        {
            int newARLNumber;

            using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
            {
                try
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspCloneSample";
                            DbCommand.Parameters.Clear();

                            DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
                            DbCommand.Parameters.Add("@DescriptionYN", System.Data.SqlDbType.Bit).Value = descriptionYN;
                            DbCommand.Parameters.Add("@StorageYN", System.Data.SqlDbType.Bit).Value = storageYN;
                            DbCommand.Parameters.Add("@ContainersYN", System.Data.SqlDbType.Bit).Value = containersYN;
                            DbCommand.Parameters.Add("@ContainerDescriptionYN", System.Data.SqlDbType.Bit).Value = containerDescriptionYN;
                            DbCommand.Parameters.Add("@FillVolumeYN", System.Data.SqlDbType.Bit).Value = fillVolumeYN;
                            DbCommand.Parameters.Add("@DosageYN", System.Data.SqlDbType.Bit).Value = dosageYN;
                            DbCommand.Parameters.Add("@AnalytesYN", System.Data.SqlDbType.Bit).Value = analytesYN;
                            DbCommand.Parameters.Add("@TestsYN", System.Data.SqlDbType.Bit).Value = testsYN;
                            DbCommand.Parameters.Add("@ChargesYN", System.Data.SqlDbType.Bit).Value = chargesYN;
                            DbCommand.Parameters.Add("@NotesYN", System.Data.SqlDbType.Bit).Value = notesYN;
                            DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = identification.UserId;

                            newARLNumber = (int)DbConnection.ExecuteScalar(DbCommand);

                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }

                    return newARLNumber;
                }
                catch
                {
                    throw;
                }
            }
        }

        public int ImportSample(int requestId, Identification identification)
        {
            int newARLNumber;

            using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
            {
                try
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspImportSample";
                            DbCommand.Parameters.Clear();

                            DbCommand.Parameters.Add("@RequestId", System.Data.SqlDbType.Int).Value = requestId;
                            DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = identification.UserId;

                            newARLNumber = (int)DbConnection.ExecuteScalar(DbCommand);

                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }

                    return newARLNumber;
                }
                catch
                {
                    throw;
                }
            }
        }

        public dynamic GetOrdersReportData(int id, bool enCode = true)
        {
            try
            {
                dynamic result;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            string sql = @"
                                        select orders_reports.report_data
                                        from orders_reports
                                        where orders_reports.id = @Id
                                        ";

                            DbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
                            DbCommand.CommandText = sql;
                            if (enCode)
                                result = Convert.ToBase64String((byte[])DbConnection.ExecuteScalar(DbCommand));
                            else
                                result = (byte[])DbConnection.ExecuteScalar(DbCommand);
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return result;
            }
            catch
            {
                throw;
            }
        }

        public bool ValidatePin(string pin, int userId)
        {
            using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
            {
                if (DbConnection.IsConnected())
                {
                    using (DbCommand)
                    {
                        DbCommand.CommandType = CommandType.StoredProcedure;
                        DbCommand.CommandText = "uspValidatePIN";
                        DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
                        DbCommand.Parameters.Add("@PIN", System.Data.SqlDbType.VarChar, 64).Value = pin;

                        var result = (int)DbConnection.ExecuteScalar(DbCommand);
                        return result > 0;
                    }
                }
                else
                {
                    throw new Exception("Unable to Connect");
                }
            }
        }

        /// <summary>
        /// Save User record.
        /// </summary>
        /// <returns></returns>
        public int SaveUser(User user, Guid userToken, int userId)
        {
            int returnValue = -1;
            string sql = string.Empty;
            using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
            {
                if (DbConnection.IsConnected())
                {
                    using (DbCommand)
                    {
                        //Check New Record
                        if (user.Signature.IsDirty) if (user.Signature.SignatureId == Guid.Empty)
                            {
                                user.SignatureId = Guid.NewGuid();
                            }

                        if (user.IsDirty)
                        {
                            SystemDAO.SaveChangeAudit<User>(ref dbConnection, ref dbCommand,
                                GetUser(ref dbConnection, ref dbCommand, user.Pk ?? 0),
                                user,
                                ModuleNames.Users,
                                user.Pk,
                                userId
                            );
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.Parameters.Clear();
                            if (user.UserId == null)
                            {
                                DbCommand.CommandText = "uspInsertUser";
                                DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
                                DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            }
                            else
                            {
                                DbCommand.CommandText = "uspUpdateUser";
                                DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = user.UserId;
                                DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
                                DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            }

                            DbCommand.Parameters.Add("@RoleId", System.Data.SqlDbType.UniqueIdentifier).Value = user.Role.RoleId;
                            DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = user.DepartmentId;
                            DbCommand.Parameters.Add("@AdministratorYN", System.Data.SqlDbType.Bit).Value = user.AdministratorYN;
                            DbCommand.Parameters.Add("@AdministratorKey", System.Data.SqlDbType.VarChar, 200).Value = user.AdministratorKey ?? string.Empty;
                            DbCommand.Parameters.Add("@Username", System.Data.SqlDbType.VarChar, 50).Value = user.Username;
                            if (user.Password.IsNotNull())
                                DbCommand.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 64).Value = user.Password;
                            DbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.VarChar, 50).Value = user.FirstName ?? string.Empty;
                            DbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.VarChar, 50).Value = user.LastName ?? string.Empty;
                            DbCommand.Parameters.Add("@Email", System.Data.SqlDbType.VarChar, 255).Value = user.Email ?? string.Empty;
                            DbCommand.Parameters.Add("@Title", System.Data.SqlDbType.VarChar, 255).Value = user.Title ?? string.Empty;
                            DbCommand.Parameters.Add("@SignatureId", System.Data.SqlDbType.UniqueIdentifier).Value = user.SignatureId;

                            if (user.UserId != null) returnValue = DbConnection.ExecuteCommand(DbCommand);
                            else
                            {
                                // returnValue = Primary Key Id
                                returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
                                user.UserId = returnValue;
                            }
                        }

                        // Save Signature
                        if (user.Signature.IsDirty)
                            this.SaveSignature(ref dbConnection, ref dbCommand, user, (int)user.UserId);

                        // Release Lock
                        using (SystemDAO systemDao = new SystemDAO())
                        {
                            systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.User, user.UserId.ToString(), userToken);
                        }
                    }
                }
                else
                {
                    throw new Exception("Unable to Connect");
                }
            }
            return returnValue;
        }

        private string GetPendingClientSubmissionsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
        {
            try
            {
                int returnValue = 0;

                string sql = @"
                            SELECT Count(DISTINCT sampleTest.parentid)
                            FROM orders_samples_tests as sampleTest
                            WHERE sampleTest.status = @Status AND sampleTest.delete_date IS NULL
                            ;";

                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress;
                dbCommand.CommandText = sql;
                var returnScalar = dbConnection.ExecuteScalar(dbCommand);
                if (returnScalar != null)
                    returnValue = (int)returnScalar;
                return returnValue <= 0 ? string.Empty : returnValue.ToString();
            }
            catch
            {
                throw;
            }
        }