/// <summary> /// Save Client /// </summary> /// <returns></returns> public int SaveClient(Client client, Guid userToken, int userId) { try { int returnValue = -1; bool insertNewRecord = false; 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 (client.IsDirty) { SystemDAO.SaveChangeAudit<Client>(ref dbConnection, ref dbCommand, GetClient(ref dbConnection, ref dbCommand, client.Pk ?? 0), client, ModuleNames.Clients, client.Pk, userId); DbCommand.Parameters.Clear(); if (client.ClientId == null || client.ClientId <= 0) { insertNewRecord = true; DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspInsertClient"; 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 = "uspUpdateClient"; 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("@ClientName", System.Data.SqlDbType.Text, 100).Value = client.ClientName != null ? client.ClientName : SqlString.Null; DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId != null ? client.AccountingId : SqlString.Null; DbCommand.Parameters.Add("@TermId", System.Data.SqlDbType.Int).Value = client.Term.TermId != null ? client.Term.TermId : (int?)SqlInt32.Null; DbCommand.Parameters.Add("@CreditCheckYN", System.Data.SqlDbType.Bit).Value = client.CreditCheckYN != null ? client.CreditCheckYN : SqlBoolean.Null; DbCommand.Parameters.Add("@CreditHoldYN", System.Data.SqlDbType.Bit).Value = client.CreditHoldYN != null ? client.CreditHoldYN : SqlBoolean.Null; DbCommand.Parameters.Add("@WebClientYN", System.Data.SqlDbType.Bit).Value = client.WebClientYN != null ? client.WebClientYN : SqlBoolean.Null; DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = client.GMPYN != null ? client.GMPYN : SqlBoolean.Null; DbCommand.Parameters.Add("@BillingAddress", System.Data.SqlDbType.Text, 100).Value = client.BillingAddress != null ? client.BillingAddress : SqlString.Null; DbCommand.Parameters.Add("@BillingCity", System.Data.SqlDbType.Text, 100).Value = client.BillingCity != null ? client.BillingCity : SqlString.Null; if (client.BillingState != null && client.BillingState.StateId != null) DbCommand.Parameters.Add("@BillingStateId", System.Data.SqlDbType.Int).Value = client.BillingState.StateId; DbCommand.Parameters.Add("@BillingZip", System.Data.SqlDbType.Text, 10).Value = client.BillingZip != null ? client.BillingZip : SqlString.Null; if (client.BillingCountry != null && client.BillingCountry.CountryId != null) DbCommand.Parameters.Add("@BillingCountryId", System.Data.SqlDbType.Int).Value = client.BillingCountry.CountryId; DbCommand.Parameters.Add("@SameAsBillingYN", System.Data.SqlDbType.Bit).Value = client.SameAsBillingYN != null ? client.SameAsBillingYN : SqlBoolean.Null; DbCommand.Parameters.Add("@ShippingAddress", System.Data.SqlDbType.Text, 100).Value = client.ShippingAddress != null ? client.ShippingAddress : SqlString.Null; DbCommand.Parameters.Add("@ShippingCity", System.Data.SqlDbType.Text, 100).Value = client.ShippingCity != null ? client.ShippingCity : SqlString.Null; if (client.ShippingState != null && client.ShippingState.StateId != null) DbCommand.Parameters.Add("@ShippingStateId", System.Data.SqlDbType.Int).Value = client.ShippingState.StateId; DbCommand.Parameters.Add("@ShippingZip", System.Data.SqlDbType.Text, 10).Value = client.ShippingZip != null ? client.ShippingZip : SqlString.Null; if (client.ShippingCountry != null && client.ShippingCountry.CountryId != null) DbCommand.Parameters.Add("@ShippingCountryId", System.Data.SqlDbType.Int).Value = client.ShippingCountry.CountryId; if (client.ClientId > 0) returnValue = DbConnection.ExecuteCommand(DbCommand); else { // returnValue = Primary Key Id returnValue = (int)DbConnection.ExecuteScalar(DbCommand); client.ClientId = returnValue; } } // Save Contacts using (ClientDAO contactDao = new ClientDAO()) contactDao.SaveContacts(ref dbConnection, ref dbCommand, ref client, userId); // Save Pricing this.SaveClientPricing(ref dbConnection, ref dbCommand, ref client, userId); // Save Complaints this.SaveClientComplaints(ref dbConnection, ref dbCommand, ref client, userId); // Save Notes this.SaveClientNotes(ref dbConnection, ref dbCommand, client, userId); // Save Documents this.SaveClientDocuments(ref dbConnection, ref dbCommand, client, userId); // Accounting Interface //if (insertNewRecord && Vars.AccountingSettings.InterfaceId > 0) { // client = new ClientInterface().ClientAdd(client); // if (!string.IsNullOrWhiteSpace(client.AccountingId)) { // sql = @" // UPDATE customers // SET accountingid = @AccountingId // WHERE id = @ID // "; // DbCommand.Parameters.Clear(); // DbCommand.CommandText = sql; // DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = client.ClientId; // DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId ?? string.Empty; // DbConnection.ExecuteCommand(DbCommand); // } //} // Release Lock using (SystemDAO systemDao = new SystemDAO()) { systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Client, client.ClientId.ToString(), userToken); } }catch { if (DbConnection.IsConnected() && DbConnection.IsTransaction()) DbConnection.Rollback(); throw; } } }else { throw new Exception("Unable to Connect"); } } return returnValue; }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()) { systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Sample, sample.ARLNumber.ToString(), identification.Token); } } catch { if (DbConnection.IsConnected() && DbConnection.IsTransaction()) DbConnection.Rollback(); throw; } } } else { throw new Exception("Unable to Connect"); } } return returnValue; } catch { throw; } }