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