public SmartCollection<Contact> DoGetContacts(int parentId)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO())
{
return dao.GetContacts(parentId);
}
}
public string DoGetOpenComplaintsCount(Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO())
{
return dao.GetClientComplaintsOpenCount(identification);
}
}
public SmartCollection<Client> DoGetClients(string clientString)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO()) {
return dao.GetClients(clientString);
}
}
public SmartCollection<Client> DoGetClientsRecent(int userId)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO()) {
return dao.GetClientsRecent(userId);
}
}
/// <summary>
/// Remove Client
/// </summary>
/// <returns></returns>
public int RemoveClient(int ClientId, int userId)
{
try {
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspRemoveClient";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
// ToDo Mark all Related Data Deleted
using (ClientDAO contactDao = new ClientDAO()) {
contactDao.RemoveContacts(ref dbConnection, ref dbCommand, ClientId, userId);
}
this.RemoveClientPricings(ref dbConnection, ref dbCommand, ClientId, userId);
this.RemoveClientNotes(ref dbConnection, ref dbCommand, ClientId, userId);
}
}else {
throw new Exception("Unable to Connect");
}
}
return rowsAffected;
}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 Client DoGetClient(int id, Guid userToken)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO()) {
return dao.GetClient(id);
}
}
public int DoSaveClientComplaint(ClientComplaint complaint, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
//Validate Complaint Object
if (!complaint.ValidateModel())
throw new FaultException<ServiceFault>(new ServiceFault(complaint.CurrentErrors), new FaultReason(SysVars.InvalidFormat));
using (ClientDAO dao = new ClientDAO())
{
return dao.SaveClientComplaint(complaint, identification);
}
}
public SmartCollection<Order> SearchOrders(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification)
{
try
{
SmartCollection<Order> resultList = new SmartCollection<Order>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = @"
select distinct orders.id,orders.parentid,orders.ponumber, orders.received_date,orders.start_date,
orders.due_date,orders.report_date, orders.status,
(users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser,
orders.modified_by, orders.modified_date, orders.created_by, orders.created_date,
(select sum(amount) from orders_charges where parentid = orders.id and delete_date IS NULL) as ChargesTotal,
(select sum(orders_samples_tests.item_price) from orders_samples_tests where orders_samples_tests.parentid = orders.id and orders_samples_tests.delete_date IS NULL and orders_samples_tests.status < 7 ) as TestsTotal
from orders
LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
LEFT JOIN orders_samples_containers as containers ON containers.parentid = samples.id
LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
LEFT JOIN list.departments as dept ON dept.departmentid = tests.departmentid
LEFT JOIN customers ON customers.id = orders.parentid
LEFT JOIN [User] AS users ON orders.modified_by = users.UserID
LEFT JOIN [User] as users2 ON orders.created_by = users2.UserID " +
SysLib.BuildSearchAllWhereClause(searchString, searchItems);
if (identification.ClientYN)
{
sql += " and customers.id = @companyId ";
DbCommand.Parameters.Add("@companyId", System.Data.SqlDbType.Int).Value = identification.ClientId;
}
sql += " AND orders.delete_date IS NULL ";
sql += "ORDER BY orders.modified_date DESC ";
DbCommand.CommandText = sql;
DataTable customerDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in customerDT.Rows)
{
Order order = new Order();
order.Id = Convert.ToInt32(row["Id"]);
order.ParentId = row["parentid"] != DBNull.Value ? (int)row["parentid"] : 0;
order.Status = row["status"] != DBNull.Value ? (EnumOrderStatus)row["status"] : EnumOrderStatus.Open;
order.PoNumber = row["ponumber"].ToString();
order.OrderTotal = Convert.ToDecimal(row["ChargesTotal"] != DBNull.Value ? (decimal)row["ChargesTotal"] : 0) + Convert.ToDecimal(row["TestsTotal"] != DBNull.Value ? (decimal)row["TestsTotal"] : 0);
if (row["received_date"] != DBNull.Value)
order.ReceivedDate = (DateTime)row["received_date"];
if (row["start_date"] != DBNull.Value)
order.StartDate = (DateTime)row["start_date"];
if (row["due_date"] != DBNull.Value)
order.DueDate = (DateTime)row["due_date"];
if (row["report_date"] != DBNull.Value)
order.ReportDate = (DateTime)row["report_date"];
order.CreatedUser = row["createduser"].ToString();
order.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0;
order.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
order.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0;
order.ModifiedUser = row["modifieduser"].ToString();
order.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
order.Samples = this.GetSamples(ref dbConnection, ref dbCommand, "", identification);
order.SampleTests = this.GetSampleTests(ref dbConnection, ref dbCommand, order.Id, true, identification);
using (ClientDAO dao = new ClientDAO())
{
order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
}
resultList.Add(order);
}
customerDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<Sample> GetSamples(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, string searchString, Identification identification)
{
try
{
SmartCollection<Sample> resultList = new SmartCollection<Sample>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSamples";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SearchString", System.Data.SqlDbType.NVarChar, 100).Value = searchString;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Sample sample = new Sample();
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?();
sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32();
sample.ClientName = row["ClientName"].ToString();
if (sample.ClientId > -1)
{
//sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName };
using (ClientDAO clientDao = new ClientDAO())
{
sample.Client = clientDao.GetClient((int)sample.ClientId);
}
}
sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress;
sample.PONumber = row["PONumber"].ToString();
sample.FormulationId = row["FormulationID"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.ProjectNumber = row["ProjectNumber"].ToString();
sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32();
sample.StorageLocationName = row["StorageLocationName"].ToString();
sample.StorageLocationConditions = row["StorageLocationConditions"].ToString();
sample.StorageLocationCode = row["StorageLocationCode"].ToString();
if (sample.StorageLocationId > -1)
sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() };
sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32();
sample.RequestedStorageName = row["RequestedStorageName"].ToString();
if (sample.RequestedStorageId > -1)
sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" };
sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32();
sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null;
if (sample.DosageId > -1)
sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() };
sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32();
sample.ContainerDescription = row["ContainerDescription"].ToString();
sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal();
sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32();
sample.VolumeUOM = row["VolumeUOM"].ToString();
sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false;
sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
sample.CompoundedBy = row["CompoundedBy"].ToString();
sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?();
sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
sample.CreatedUser = row["CreatedUser"].ToString();
sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
sample.ModifiedUser = row["ModifiedUser"].ToString();
sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber);
sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber);
sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber);
sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification);
sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification);
resultList.Add(sample);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<Order> SearchModuleOrders(int? customerId, DateTime? orderReceivedStartDate, DateTime? orderReceivedEndDate, int? testId, int? analyteId)
{
try
{
var searchCustomers = customerId != null && customerId > 0;
var searchDates = orderReceivedStartDate != null || orderReceivedEndDate != null;
var searchTests = testId != null && testId > 0;
var serachAnalytes = analyteId != null && analyteId > 0;
var result = new SmartCollection<Order>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = @"
select distinct orders.id, orders.parentid, customers.customer_name, orders.received_date, orders.status
from orders
LEFT JOIN customers ON customers.id = orders.parentid
";
if (searchTests)
{
sql += @"
LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
";
}
if (serachAnalytes)
{
// NOTE: joining to seperate order_samples / order_samples_tests table aliases
// is so searching tests and analytes can be be done independently or concurrently
sql += @"
LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id
LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id
LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id
";
}
sql += @"
WHERE orders.delete_date IS NULL
";
if (searchCustomers)
{
sql += @"
AND customers.id = @customerId
";
}
if (searchDates)
{
sql += @"
AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate)
";
}
if (searchTests)
{
sql += @"
AND tests.testid = @testId
";
}
if (serachAnalytes)
{
sql += @"
AND analytes.analyteid = @analyteId
";
}
sql += @"
ORDER BY orders.received_date DESC
";
DbCommand.CommandText = sql;
if (searchCustomers)
{
DbCommand.Parameters.AddWithValue("@customerId", customerId);
}
if (searchDates)
{
DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value));
DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value));
}
if (searchTests)
{
DbCommand.Parameters.AddWithValue("@testId", testId);
}
if (serachAnalytes)
{
DbCommand.Parameters.AddWithValue("@analyteId", analyteId);
}
var reader = DbConnection.ExecuteReader(DbCommand);
result.AddRange(AutoMap.MapReaderToList<Order>(reader));
foreach (var order in result)
{
using (var dao = new ClientDAO())
order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public Sample GetSample(int? arlNumber, Identification identification)
{
try
{
Sample sample = new Sample();
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetSample";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DataTable returnDT = DbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count > 0)
{
DataRow row = returnDT.Rows[0];
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?();
sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32();
sample.ClientName = row["ClientName"].ToString();
if (sample.ClientId > -1)
{
//sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName };
using (ClientDAO clientDao = new ClientDAO())
{
sample.Client = clientDao.GetClient((int)sample.ClientId);
}
}
sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress;
sample.PONumber = row["PONumber"].ToString();
sample.FormulationId = row["FormulationID"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.ProjectNumber = row["ProjectNumber"].ToString();
sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32();
sample.StorageLocationName = row["StorageLocationName"].ToString();
sample.StorageLocationConditions = row["StorageLocationConditions"].ToString();
sample.StorageLocationCode = row["StorageLocationCode"].ToString();
if (sample.StorageLocationId > -1)
sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() };
sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32();
sample.RequestedStorageName = row["RequestedStorageName"].ToString();
if (sample.RequestedStorageId > -1)
sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" };
sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32();
sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null;
if (sample.DosageId > -1)
sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() };
sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32();
sample.ContainerDescription = row["ContainerDescription"].ToString();
if (sample.ContainerDescription != null)
sample.Container = new Container() { Description = sample.ContainerDescription };
sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal();
sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32();
sample.VolumeUOM = row["VolumeUOM"].ToString();
if (sample.VolumeUOMId != null)
sample.VolumeUnitOfMeasure = new UnitOfMeasure() { UomId = sample.VolumeUOMId, Uom = sample.VolumeUOM };
sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false;
sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
sample.CompoundedBy = row["CompoundedBy"].ToString();
sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?();
sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
sample.CreatedUser = row["CreatedUser"].ToString();
sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
sample.ModifiedUser = row["ModifiedUser"].ToString();
sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber);
sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber);
sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber);
sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification);
sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification);
}
returnDT = null;
return sample;
}
catch
{
throw;
}
}
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 SmartCollection<ClientComplaint> DoGetPendingComplaints(Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO())
{
return dao.GetClientComplaintsOpen(identification);
}
}
public byte[] DoGetClientDocumentData(int documentId)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (var dao = new ClientDAO())
{
return dao.GetClientDocumentData(documentId);
}
}
public int DoRemoveCustomerNote(int id, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO()) {
return dao.RemoveClientNote(id, identification.UserId);
}
}
public ClientPricing DoGetClientPricing(int clientPricingId)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO())
{
return dao.GetClientPricing(clientPricingId);
}
}
public int DoCreateCoaReport(Sample sample, Identification identification)
{
ReportRecord reportRecord = null;
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SampleDAO dao = new SampleDAO()) {
reportRecord = dao.CreateCoaReport(sample, identification);
}
if (reportRecord.Id.IsNotNull() && reportRecord.Id > 0) {
using (ClientDAO customerDao = new ClientDAO()) {
reportRecord.ParentId = customerDao.GetClientId(reportRecord.ParentId.Value);
}
using (ReportDAO reportDao = new ReportDAO()) {
ReportNotification reportNotification = new ReportNotification();
reportNotification.ReportId = reportRecord.Id.Value;
reportNotification.CustomerId = reportRecord.ParentId.Value;
reportNotification.DepartmentId = reportRecord.DepartmentId.Value;
reportNotification.SubjectLine = reportRecord.SubjectLine;
reportDao.SaveNotificationReports(reportDao.GetNotificationRecords(reportNotification));
}
}
return reportRecord.Id.Value;
}
public SmartCollection<ClientPricing> DoGetClientPricings(int clientId)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (ClientDAO dao = new ClientDAO()) {
return dao.GetClientPricings(clientId);
}
}
/// <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 void ProcessNotifications()
{
if (!AppVars.ReportNotificationsEnabled) return;
if (SysVars.isConsole) Console.WriteLine("Processing Notifications");
ReportNotificationsActive = true;
List<ReportNotification> reportNotifications = this.GetReportNotifications();
if (reportNotifications.Count > 0)
{
foreach (ReportNotification item in reportNotifications)
{
ContactNotify notify = null;
using (ClientDAO cDao = new ClientDAO()) { notify = null; } //cDao.GetContact(item.NotificationId);}
item.NotifyContact = notify;
ReportRecord report = new ReportRecord();
using (SampleDAO oDao = new SampleDAO()) { report = oDao.GetOrdersReport(item.ReportId, null); }
// Email
if (notify.IsFax == false) {
this.SendReportEmail(notify, item, report);
}
//Fax
if (notify.IsFax == true) {
this.ReportSendFaxAsync(notify, item, report);
}
}
}
ReportNotificationsActive = false;
}
