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())
{
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;
}
}
public int SaveSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Sample sample, Identification identification)
{
try
{
int returnValue = 0;
//string sql = string.Empty;
//bool reCalculatePricing = sample.Status != EnumSampleStatus.Canceled;
foreach (SampleTest sampleTest in sample.SampleTests)
{
if (sampleTest.IsDirty)
{
// Load Required Objects (do not trust what is passed in to be propulated or correct for pricing)
// May not need to reprice due to facts like already invoiced
//SmartCollection<ClientPricing> pricing = new SmartCollection<ClientPricing>();
//Priority priority = new Priority();
//SmartCollection<PricingRule> matrixItems = new SmartCollection<PricingRule>();
CatalogItem catalogItem = new CatalogItem();
/*if (reCalculatePricing)
{
using (ClientDAO customerDao = new ClientDAO())
{
//priority = customerDao.GetCustomerPriorityOverrideOrDefault((int)order.Customer.Id, (int)sampleTest.PriorityId);
priority = null;
pricing.AddRange(customerDao.GetClientPricingOverrides((int)sample.Client.ClientId, (int)sampleTest.MethodId, (int)sampleTest.MethodNumberId, (int)sampleTest.AnalyteId));
}
using (CatalogDAO catalogDao = new CatalogDAO())
{
catalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification);
}
using (SystemDAO systemDao = new SystemDAO())
{
//matrixItems.AddRange(systemDao.ReturnMatrixItems(order.Samples.Where(x => x.Id == sampleTest.SampleId).Select(x => x).FirstOrDefault().Clone(), sampleTest, reCalculatePricing));
}
}
SystemDAO.SaveChangeAudit<SampleTest>(ref dbConnection, ref dbCommand,
GetSampleTest(ref dbConnection, ref dbCommand, sampleTest.Pk ?? 0, identification),
sampleTest,
ModuleNames.Samples,
sample.Pk,
identification.UserId); */
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
if (sampleTest.SampleTestId <= 0 || sampleTest.SampleTestId == null)
{
dbCommand.CommandText = "uspInsertSampleTest";
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 = "uspUpdateSampleTest";
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = sampleTest.DeleteDate ?? sampleTest.DeleteDate;
}
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
if (sampleTest.BeginDate > DateTime.Today)
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = SampleTestStatus.Scheduled;
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,
modified_by=@ModifiedBy, 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");
}
}
return returnValue;
}
catch
{
throw;
}
}
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;
}
}
public int OrderSetStatus(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int id, Identification identification, int? forceStatus = null)
{
try
{
int returnValue = 0;
int remainingInvoicesPending = 0;
EnumOrderStatus setStatus = EnumOrderStatus.Open;
string sql = string.Empty;
if (forceStatus.IsNotNull())
{
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
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(DISTINCT tests.Id) as CountOfId
from orders
LEFT JOIN orders_samples_tests AS tests ON tests.parentid = orders.id
WHERE
orders.id = @Id AND
(orders.delete_date IS NULL AND orders.status < @OrderInvoiced AND
tests.delete_date IS NULL AND tests.status != @TestInvoiced)
;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
dbCommand.Parameters.Add("@OrderInvoiced", System.Data.SqlDbType.Int).Value = EnumOrderStatus.Invoiced;
dbCommand.Parameters.Add("@TestInvoiced", System.Data.SqlDbType.Int).Value = SampleTestStatus.Completed;
dbCommand.CommandText = sql;
remainingInvoicesPending = (int)dbConnection.ExecuteScalar(dbCommand);
if (remainingInvoicesPending == 0)
setStatus = EnumOrderStatus.Invoiced;
else return 0;
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
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);
dbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
private string GetPendingSubmissionsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
int returnValue = 0;
User currentUser = new User();
using (UserDAO userDao = new UserDAO())
{
currentUser = userDao.GetUser(identification.UserId);
}
if (currentUser.IsNull() || currentUser.UserId.IsNull())
return string.Empty;
string sqlDepartment = "AND sampleTest.DepartmentId = @DepartmentId";
// Check ApproveAll or Administrator rights
if (AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.SampleTest, ModuleAction.ApproveAll)))
{
sqlDepartment = "AND sampleTest.DepartmentId >= 0";
}
string sql = @"
SELECT Count(sampleTest.id)
FROM orders_samples_tests as sampleTest
WHERE (sampleTest.status = @Status " + sqlDepartment + ") ";
sql += "AND sampleTest.delete_date IS NULL";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress;
dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = currentUser.DepartmentId;
dbCommand.CommandText = sql;
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
return returnValue <= 0 ? string.Empty : returnValue.ToString();
}
catch
{
throw;
}
}
public int SaveOrderInvoice(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Invoice invoice, Identification identification)
{
try
{
if (invoice.ARLNumber <= 0)
{
dbCommand.CommandText = @"
INSERT INTO orders_invoices
([orderid],[customer_id],[txnid],[edit_sequence],[txn_number],[invoice_number],
[po_number],[txn_date],[due_date],[total_invoice],[memo],[bill_groupid],[created_by],[created_date])
VALUES
(@OrderId,@CustomerId,@TxnId,@EditSequence,@TxnNumber,@InvoiceNumber,
@PoNumber,@TxnDate,@DueDate,@TotalInvoice,@Memo,@BillGroupId,@CreatedBy,@CreatedDate);
SELECT id FROM orders_invoices WHERE (id = SCOPE_IDENTITY());
";
dbCommand.Parameters.Clear();
//dbCommand.Parameters.Add("@orderid", System.Data.SqlDbType.Int).Value = invoice.OrderId;
dbCommand.Parameters.Add("@CustomerId", System.Data.SqlDbType.Int).Value = invoice.ClientId;
dbCommand.Parameters.Add("@TxnId", System.Data.SqlDbType.VarChar, 100).Value = invoice.TxnID ?? string.Empty;
dbCommand.Parameters.Add("@EditSequence", System.Data.SqlDbType.VarChar, 16).Value = invoice.EditSequence ?? string.Empty;
dbCommand.Parameters.Add("@TxnNumber", System.Data.SqlDbType.Int).Value = invoice.TxnNumber ?? 0;
dbCommand.Parameters.Add("@InvoiceNumber", System.Data.SqlDbType.VarChar, 20).Value = invoice.InvoiceNumber;
dbCommand.Parameters.Add("@PoNumber", System.Data.SqlDbType.VarChar, 25).Value = invoice.PONumber ?? string.Empty;
dbCommand.Parameters.Add("@TxnDate", System.Data.SqlDbType.DateTime2).Value = Convert.ToDateTime(invoice.TxnDate.Value);//?? DateTime.Now;
dbCommand.Parameters.Add("@DueDate", System.Data.SqlDbType.DateTime2).Value = Convert.ToDateTime(invoice.DueDate.Value); // ?? DateTime.Now;
//dbCommand.Parameters.Add("@TotalInvoice", System.Data.SqlDbType.Decimal).Value = Convert.ToDecimal(invoice.TotalInvoice);
dbCommand.Parameters.Add("@Memo", System.Data.SqlDbType.VarChar, 4096).Value = invoice.Memo ?? string.Empty;
dbCommand.Parameters.Add("@BillGroupId", System.Data.SqlDbType.Int).Value = invoice.BillGroupId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = Convert.ToDateTime(DateTime.Now);
invoice.ARLNumber = (int)dbConnection.ExecuteScalar(dbCommand);
}
return invoice.ARLNumber.Value;
}
catch
{
throw;
}
}
/// <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;
}
}
public int SaveOrderSampleTestOos(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Oos oos, Identification identification)
{
try
{
int returnValue = 0;
string sql = string.Empty;
dbCommand.Parameters.Clear();
if (oos.IsDirty)
{
sql = string.Empty;
if (oos.OosId.IsNull() || oos.OosId <= 0)
{
sql = @"
INSERT INTO orders_samples_tests_timepoints_oos
([parentid]
,[status]
,[is_sample_prep_acceptable]
,[is_raw_data_acceptable]
,[is_standard_acceptable]
,[is_method_acceptable]
,[is_parameters_acceptable]
,[is_trends_acceptable]
,[is_analyst_acceptable]
,[is_retest]
,[primary_cause]
,[client_contact_id]
,[client_contact_phone_id]
,[is_client_notified]
,[is_testing_complete]
,[review_comment]
,[conclusion_comment]
,[investigator_id]
,[investigation_date]
,[approved_by]
,[approved_date]
,[created_by]
)
VALUES
(@ParentId
,@Status
,@IsSamplePrepAcceptable
,@IsRawDataAcceptable
,@IsStandardAcceptable
,@IsMethodAcceptable
,@IsParametersAcceptable
,@IsTrendsAcceptable
,@IsAnalystAcceptable
,@IsRetest
,@PrimaryCause
,@ClientContactId
,@ClientContactPhoneId
,@IsClientNotified
,@IsTestingComplete
,@ReviewComment
,@ConclusionComment
,@InvestigatorId
,@InvestigationDate
,@ApprovedBy
,@ApprovedDate
,@CreatedBy
);
SELECT id FROM orders_samples_tests_timepoints_oos WHERE (id = SCOPE_IDENTITY());
";
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = oos.SampleTestId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}
else
{
sql = @"
UPDATE orders_samples_tests_timepoints_oos
SET status = @Status
,is_sample_prep_acceptable = @IsSamplePrepAcceptable
,is_raw_data_acceptable = @IsRawDataAcceptable
,is_standard_acceptable = @IsStandardAcceptable
,is_method_acceptable = @IsMethodAcceptable
,is_parameters_acceptable = @IsParametersAcceptable
,is_trends_acceptable = @IsTrendsAcceptable
,is_analyst_acceptable = @IsAnalystAcceptable
,is_retest = @IsRetest
,primary_cause = @PrimaryCause
,client_contact_id = @ClientContactId
,client_contact_phone_id = @ClientContactPhoneId
,is_client_notified = @IsClientNotified
,is_testing_complete = @IsTestingComplete
,review_comment = @ReviewComment
,conclusion_comment = @ConclusionComment
,investigator_id = @InvestigatorId
,investigation_date = @InvestigationDate
,approved_by = @ApprovedBy
,approved_date = @ApprovedDate
,modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = oos.OosId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}
dbCommand.CommandText = sql;
/*dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)oos.Status;
dbCommand.Parameters.Add("@IsSamplePrepAcceptable", System.Data.SqlDbType.Bit).Value = oos.PreparationYN;
dbCommand.Parameters.Add("@IsRawDataAcceptable", System.Data.SqlDbType.Bit).Value = oos.RawDataYN;
dbCommand.Parameters.Add("@IsStandardAcceptable", System.Data.SqlDbType.Bit).Value = oos.StandardsReagentsYN;
dbCommand.Parameters.Add("@IsMethodAcceptable", System.Data.SqlDbType.Bit).Value = oos.MethodologyYN;
dbCommand.Parameters.Add("@IsParametersAcceptable", System.Data.SqlDbType.Bit).Value = oos.InstrumentParametersYN;
dbCommand.Parameters.Add("@IsTrendsAcceptable", System.Data.SqlDbType.Bit).Value = oos.TrendsYN;
dbCommand.Parameters.Add("@IsAnalystAcceptable", System.Data.SqlDbType.Bit).Value = oos.AnalystYN;
dbCommand.Parameters.Add("@IsRetest", System.Data.SqlDbType.Bit).Value = oos.IsRetest;
dbCommand.Parameters.Add("@PrimaryCause", System.Data.SqlDbType.VarChar, 50).Value = oos.PrimaryCause ?? string.Empty;
dbCommand.Parameters.Add("@ClientContactId", System.Data.SqlDbType.Int).Value = oos.ClientContactEmailYN ?? 0;
dbCommand.Parameters.Add("@ClientContactPhoneId", System.Data.SqlDbType.Int).Value = oos.ClientContactPhoneYN ?? 0;
dbCommand.Parameters.Add("@IsClientNotified", System.Data.SqlDbType.Bit).Value = oos.IsClientNotified;
dbCommand.Parameters.Add("@IsTestingComplete", System.Data.SqlDbType.Bit).Value = oos.IsTestingComplete;
dbCommand.Parameters.Add("@ReviewComment", System.Data.SqlDbType.NVarChar, -1).Value = oos.AdditionalObservations ?? string.Empty;
dbCommand.Parameters.Add("@ConclusionComment", System.Data.SqlDbType.NVarChar, -1).Value = oos.Conclusion ?? string.Empty;
dbCommand.Parameters.Add("@InvestigatorId", System.Data.SqlDbType.Int).Value = oos.InvestigatorId ?? 0;
dbCommand.Parameters.Add("@InvestigationDate", System.Data.SqlDbType.DateTime).Value = oos.InvestigationDate.HasValue ? oos.InvestigationDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ApprovedBy", System.Data.SqlDbType.Int).Value = oos.ApprovedBy ?? 0;
dbCommand.Parameters.Add("@ApprovedDate", System.Data.SqlDbType.DateTime).Value = oos.ApprovedDate.HasValue ? oos.ApprovedDate.Value : SqlDateTime.Null;
*/
if (oos.OosId.IsNotNull() && oos.OosId > 0)
returnValue += dbConnection.ExecuteCommand(dbCommand);
else
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
// Create Timepoint Entry if IsRetest
// Calculate SampleTest Status
if (oos.SampleTestId.IsNotNull())
this.OrderSampleTestsSetStatus(ref dbConnection, ref dbCommand, (int)oos.SampleTestId, identification);
// Release Lock
using (SystemDAO systemDao = new SystemDAO())
{
//systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Order, oos.OrderId.ToString(), identification.Token);
}
}
return returnValue;
}
catch
{
throw;
}
}
private string GetClientComplaintsOpenCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
bool canApproveComplaints = AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Clients, ModelNamesEnum.Complaint, ModuleAction.Approve));
int returnValue = 0;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientComplaintsOpenCount";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = identification.UserId;
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
return returnValue <= 0 ? string.Empty : returnValue.ToString();
}
catch
{
throw;
}
}
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 GetClientId(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleId)
{
try {
int result = -1;
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientId";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleId", System.Data.SqlDbType.Int).Value = sampleId;
result = (int)dbConnection.ExecuteScalar(dbCommand);
return result;
}catch {
throw;
}
}
public int SaveCatalogItem(CatalogItem catalogItem, Identification identification)
{
try {
int returnValue = -1;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
if (catalogItem.IsDirty) {
SystemDAO.SaveChangeAudit<CatalogItem>(ref dbConnection, ref dbCommand,
GetCatalogItem(ref dbConnection, ref dbCommand, catalogItem.Pk ?? 0, identification),
catalogItem,
ModuleNames.Catalog,
catalogItem.Pk,
identification.UserId
);
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.Parameters.Clear();
if (catalogItem.CatalogId == null) {
DbCommand.CommandText = "uspInsertCatalogItem";
DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}else {
DbCommand.CommandText = "uspUpdateCatalogItem";
DbCommand.Parameters.Add("@CatalogId", System.Data.SqlDbType.Int).Value = catalogItem.CatalogId != null && catalogItem.CatalogId != -1 ? catalogItem.CatalogId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = catalogItem.DeleteDate.HasValue ? catalogItem.DeleteDate.Value : SqlDateTime.Null;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}
DbCommand.Parameters.Add("@Active", System.Data.SqlDbType.Bit).Value = catalogItem.Active != null ? catalogItem.Active : true;
DbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = catalogItem.Test.TestId != null && catalogItem.Test.TestId != -1 ? catalogItem.Test.TestId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = catalogItem.Analyte != null && catalogItem.Analyte.AnalyteId != null && catalogItem.Analyte.AnalyteId != -1 ? catalogItem.Analyte.AnalyteId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@MethodId", System.Data.SqlDbType.Int).Value = catalogItem.Method != null && catalogItem.Method.MethodId != null && catalogItem.Method.MethodId != -1 ? catalogItem.MethodId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@MethodNumberId", System.Data.SqlDbType.Int).Value = catalogItem.MethodNumber != null && catalogItem.MethodNumber.MethodNumberId != null && catalogItem.MethodNumber.MethodNumberId != -1 ? catalogItem.MethodNumberId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = catalogItem.Analyst != null && catalogItem.AnalystId != 1 ? catalogItem.AnalystId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = catalogItem.Department != null && catalogItem.Department.DepartmentId != null && catalogItem.Department.DepartmentId != -1 ? catalogItem.Department.DepartmentId.Value : SqlInt32.Null;
DbCommand.Parameters.Add("@OutsourcedYN", System.Data.SqlDbType.Bit).Value = catalogItem.OutsourcedYN != null ? (bool)catalogItem.OutsourcedYN : false;
DbCommand.Parameters.Add("@BasePrice", System.Data.SqlDbType.Decimal).Value = catalogItem.BasePrice != null ? catalogItem.BasePrice : 0;
DbCommand.Parameters.Add("@LowSpec", System.Data.SqlDbType.Decimal).Value = catalogItem.LowSpec != null ? Convert.ToDecimal(catalogItem.LowSpec) : SqlDecimal.Null;
DbCommand.Parameters.Add("@HighSpec", System.Data.SqlDbType.Decimal).Value = catalogItem.HighSpec != null ? Convert.ToDecimal(catalogItem.HighSpec) : SqlDecimal.Null;
DbCommand.Parameters.Add("@CalculatePercentageExpectedYN", System.Data.SqlDbType.Bit).Value = catalogItem.CalculatePercentageExpectedYN ? (bool)catalogItem.CalculatePercentageExpectedYN : false;
DbCommand.Parameters.Add("@OtherSpec", System.Data.SqlDbType.Text).Value = catalogItem.OtherSpec != null && catalogItem.OtherSpec != String.Empty ? catalogItem.OtherSpec : null;
DbCommand.Parameters.Add("@TestMinutes", System.Data.SqlDbType.SmallInt).Value = catalogItem.TestMinutes ?? 0 ;
DbCommand.Parameters.Add("@EquipmentMinutes", System.Data.SqlDbType.SmallInt).Value = catalogItem.EquipmentMinutes ?? 0;
DbCommand.Parameters.Add("@AccountingCode", System.Data.SqlDbType.Text).Value = catalogItem.AccountingCode != null && catalogItem.AccountingCode != String.Empty ? catalogItem.AccountingCode : null;
DbCommand.Parameters.Add("@Instructions", System.Data.SqlDbType.Text).Value = catalogItem.Instructions != null && catalogItem.Instructions != String.Empty ? catalogItem.Instructions : null;
DbCommand.Parameters.Add("@RequirementYN", System.Data.SqlDbType.Bit).Value = catalogItem.RequirementYN != null ? (bool)catalogItem.RequirementYN : false;
DbCommand.Parameters.Add("@RequirementDescription", System.Data.SqlDbType.Text).Value = catalogItem.RequirementDescription != null && catalogItem.RequirementDescription != String.Empty && catalogItem.RequirementYN == true ? catalogItem.RequirementDescription : null;
DbCommand.Parameters.Add("@DiscountAllowedYN", System.Data.SqlDbType.Bit).Value = catalogItem.DiscountAllowedYN != null ? (bool)catalogItem.DiscountAllowedYN : false;
DbCommand.Parameters.Add("@ClientSelectableYN", System.Data.SqlDbType.Bit).Value = catalogItem.ClientSelectableYN != null ? (bool)catalogItem.ClientSelectableYN : false;
DbCommand.Parameters.Add("@SelectionOrder", System.Data.SqlDbType.Int).Value = catalogItem.SelectionOrder != null ? catalogItem.SelectionOrder : 20;
if (catalogItem.CatalogId != null) returnValue = DbConnection.ExecuteCommand(DbCommand);
else {
returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
catalogItem.CatalogId = returnValue;
}
}
// Save Priorities
this.SaveCatalogItemPriorities(ref dbConnection, ref dbCommand, ref catalogItem, identification.UserId);
// Release Lock
using (SystemDAO systemDao = new SystemDAO()) {
systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Catalog, catalogItem.CatalogId.ToString(), identification.Token);
}
}
}else {
throw new Exception("Unable to Connect");
}
}
return returnValue;
}catch {
throw;
}
}