private void GetRequestResponse(IAsyncResult result)
{
try
{
Result = ((IInventoryService)result.AsyncState).EndGetInventoryItem(result);
}
catch (Exception ex)
{
Error = ex;
}
finally
{
service.Close();
service = null;
}
// Execute Last
Caliburn.Micro.Execute.OnUIThread(() => Completed(this, new ResultCompletionEventArgs()));
}
public int DoSaveInventoryItem(InventoryItem inventoryItem, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
//Validate Inventory Object
if (!inventoryItem.ValidateModel())
throw new FaultException<ServiceFault>(new ServiceFault(inventoryItem.CurrentErrors), new FaultReason(SysVars.InvalidFormat));
using (InventoryDAO dao = new InventoryDAO())
{
return dao.SaveInventoryRecord(inventoryItem, identification.Token, identification.UserId);
}
}
public IAsyncResult BeginSaveInventoryItem(InventoryItem inventoryItem, Identification identification, AsyncCallback callback, object state)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
identification.UserId = AppLib.VerifyToken(identification.Token);
if (identification.UserId <= 0)
{
throw new FaultException<ServiceFault>(new ServiceFault("Invalid Authentication", "Authorization"), new FaultReason("Unauthorized"));
}
var task = Task<int>.Factory.StartNew(process => DoSaveInventoryItem(inventoryItem, identification), state);
return task.ContinueWith(res => callback(task));
}
public SmartCollection<InventoryItem> SearchInventory(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification)
{
try
{
SmartCollection<InventoryItem> resultList = new SmartCollection<InventoryItem>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = @"
SELECT DISTINCT " + baseInventorySql + SysLib.BuildSearchAllWhereClause(searchString, searchItems);
sql += " AND inventory.delete_date IS NULL ";
sql += "ORDER BY inventory.modified_date DESC ";
DbCommand.CommandText = sql;
var table = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in table.Rows)
{
InventoryItem item = new InventoryItem();
item.Id = Convert.ToInt32(row["Id"]);
item.Active = Convert.ToBoolean(row["analyte_active"]);
item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard;
item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0;
item.ClientId = Convert.ToInt32(row["customerid"]);
item.AnalyteId = Convert.ToInt32(row["analyteid"]);
if (item.AnalyteId > 0)
item.AnalyteItem = new Analyte() {
AnalyteId = item.AnalyteId,
AnalyteName = row["analytename"].ToString(),
Active = Convert.ToBoolean(row["analyte_active"]),
ControlledYN = Convert.ToBoolean(row["analyte_controlled"]),
AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"])
};
item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0;
if (item.StorageId > 0)
item.StorageLocation = new StorageLocation() {
StorageLocationId = item.StorageId,
Active = Convert.ToBoolean(row["storage_active"]),
Description = row["storage_description"].ToString(),
Conditions = row["storage_conditions"].ToString()
};
item.PoNumber = row["po_number"].ToString();
item.Manufacturer = row["manufacturer"].ToString();
item.Lot = row["lot"].ToString();
item.Cas = row["cas"].ToString();
item.Catalog = row["catalog"].ToString();
item.Weight = row["weight"].ToString();
item.VolumeAmount = row["volume_amount"].ToString();
item.OrderedFor = row["ordered_for"].ToString();
item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0);
item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0);
item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0);
item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null;
item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null;
item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null;
item.CreatedUser = row["createduser"].ToString();
item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0;
item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null;
item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0;
item.ModifiedUser = row["modifieduser"].ToString();
item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null;
using (ClientDAO dao = new ClientDAO())
{
item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId);
}
resultList.Add(item);
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
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 SaveInventoryNotes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, InventoryItem inventoryItem, int userId)
{
try
{
int returnValue = 0;
string sql = string.Empty;
foreach (ClientNote note in inventoryItem.Notes)
{
if (note.IsDirty)
{
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetInventoryNote(ref dbConnection, ref dbCommand, note.Pk),
note,
ModuleNames.Inventory,
inventoryItem.Pk,
userId);
dbCommand.Parameters.Clear();
sql = string.Empty;
if (note.ClientNoteId <= 0)
{
sql = "INSERT into inventory_notes (parentid, txt, html,created_by) " +
"Values(@ParentId,@Txt, @Html, @CreatedBy) ";
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = inventoryItem.Id;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
}
else
{
sql = "UPDATE inventory_notes " +
"SET txt = @Txt, html = @Html, delete_date = @DeleteDate, modified_by = @ModifiedBy " +
"WHERE id = @ID";
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = note.ClientNoteId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = note.DeleteDate.HasValue ? note.DeleteDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
}
dbCommand.CommandText = sql;
dbCommand.Parameters.Add("@Txt", System.Data.SqlDbType.NText).Value = note.Note ?? string.Empty;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public InventoryItem GetInventoryItemRecord(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? id, string useSql = null)
{
try
{
InventoryItem item = new InventoryItem();
string sql = useSql != null ? useSql : "SELECT " + baseInventorySql + @"
WHERE inventory.id = @ID
";
dbCommand.Parameters.Clear();
dbCommand.CommandText = sql;
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id;
var table = dbConnection.ExecuteQuery(dbCommand);
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
item.Id = Convert.ToInt32(row["Id"]);
item.Active = Convert.ToBoolean(row["analyte_active"]);
item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard;
item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0;
item.ClientId = Convert.ToInt32(row["customerid"]);
item.AnalyteId = Convert.ToInt32(row["analyteid"]);
if (item.AnalyteId > 0)
item.AnalyteItem = new Analyte()
{
AnalyteId = item.AnalyteId,
AnalyteName = row["analytename"].ToString(),
Active = Convert.ToBoolean(row["analyte_active"]),
ControlledYN = Convert.ToBoolean(row["analyte_controlled"]),
AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"])
};
item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0;
if (item.StorageId > 0)
item.StorageLocation = new StorageLocation()
{
StorageLocationId = item.StorageId,
Active = Convert.ToBoolean(row["storage_active"]),
Description = row["storage_description"].ToString(),
Conditions = row["storage_conditions"].ToString()
};
item.PoNumber = row["po_number"].ToString();
item.Manufacturer = row["manufacturer"].ToString();
item.Lot = row["lot"].ToString();
item.Cas = row["cas"].ToString();
item.Catalog = row["catalog"].ToString();
item.Weight = row["weight"].ToString();
item.VolumeAmount = row["volume_amount"].ToString();
item.OrderedFor = row["ordered_for"].ToString();
item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0);
item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0);
item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0);
item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null;
item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null;
item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null;
item.CreatedUser = row["createduser"].ToString();
item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0;
item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null;
item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0;
item.ModifiedUser = row["modifieduser"].ToString();
item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null;
using (ClientDAO dao = new ClientDAO())
{
item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId);
}
}
return item;
}
catch
{
throw;
}
}
public InventoryItem GetInventoryItemRecord(int id, string useSql = null)
{
try
{
InventoryItem result = new InventoryItem();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
result = this.GetInventoryItemRecord(ref dbConnection, ref dbCommand, id, useSql);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public SaveInventoryItemRequest(InventoryItem inventoryItem)
{
this.inventoryItem = inventoryItem;
}
