public int SaveOrderInvoiceItems(Invoice invoice, Identification identification)
{
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
return this.SaveOrderInvoiceItems(ref dbConnection, ref dbCommand, invoice, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
}
public SmartCollection<Client> GetClientsRecent(int userId)
{
try {
SmartCollection<Client> resultList = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsRecent";
DbCommand.Parameters.Clear();
DataTable clientDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in clientDT.Rows)
{
Client client = new Client();
client.ClientId = Convert.ToInt32(row["ClientID"]);
client.AccountingId = row["AccountingID"].ToString();
client.ClientName = row["ClientName"].ToString();
client.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
if (client.TermId != null && client.TermId != -1)
client.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
client.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
client.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
client.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
client.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
client.BillingAddress = row["BillingAddress"].ToString();
client.BillingCity = row["BillingCity"].ToString();
client.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
if (client.BillingStateId != null && client.BillingStateId != -1)
client.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
client.BillingZip = row["BillingZip"].ToString();
client.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
if (client.BillingCountryId != null && client.BillingCountryId != -1)
client.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
client.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
client.ShippingAddress = row["ShippingAddress"].ToString();
client.ShippingCity = row["ShippingCity"].ToString();
client.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
if (client.ShippingStateId != null && client.ShippingStateId != -1)
client.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
client.ShippingZip = row["ShippingZip"].ToString();
client.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
if (client.ShippingCountryId != null && client.ShippingCountryId != -1)
client.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
client.CreatedUser = row["CreatedUser"].ToString();
client.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
client.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
client.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
client.ModifiedUser = row["ModifiedUser"].ToString();
client.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
// Other client objects
client.Contacts = GetContacts(client.ClientId);
client.Prices = GetClientPricings(client.ClientId);
client.Documents = GetClientDocuments(client.ClientId);
client.Notes = GetClientNotes(client.ClientId);
client.Complaints = GetClientComplaints(client.ClientId);
resultList.Add(client);
}
clientDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public Client GetClient(int id)
{
try {
Client result = new Client();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
result = this.GetClient(ref dbConnection, ref dbCommand, id);
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
{
try {
ClientNote note = new ClientNote();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNote";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;
DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
if (notesDT.Rows.Count == 1) {
DataRow row = notesDT.Rows[0];
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
notesDT = null;
}else {
notesDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return note;
}catch {
throw;
}
}
public SmartCollection<ClientPricing> GetClientPricings(int ClientId)
{
try {
SmartCollection<ClientPricing> resultList = new SmartCollection<ClientPricing>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientPricings";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows) {
ClientPricing price = new ClientPricing();
price.ClientPricingId = Convert.ToInt32(row["ClientPricingID"]);
price.ClientId = Convert.ToInt32(row["ClientID"]);
if (row["MethodID"] != DBNull.Value)
price.MethodId = Convert.ToInt32(row["MethodID"]);
if (row["MethodNumberID"] != DBNull.Value)
price.MethodNumberId = Convert.ToInt32(row["MethodNumberID"]);
if (row["AnalyteID"] != DBNull.Value)
price.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
price.Description = row["Description"].ToString();
price.Discount = row["Discount"] != DBNull.Value ? Convert.ToDouble(row["Discount"]) : 0;
price.FlatRate = row["FlatRate"] != DBNull.Value ? Convert.ToDouble(row["FlatRate"]) : 0;
price.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
price.CreatedUser = row["CreatedUser"].ToString();
price.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
price.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
price.ModifiedUser = row["ModifiedUser"].ToString();
price.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (price.MethodId.HasValue)
price.Method = new Method { MethodId = price.MethodId, MethodName = row["MethodName"].ToString() };
else
price.Method = null;
if (price.MethodNumberId.HasValue)
price.MethodNumber = new MethodNumber { MethodNumberId = price.MethodNumberId, MethodNumberName = row["MethodNumberName"].ToString() };
else
price.MethodNumber = null;
if (price.AnalyteId.HasValue)
price.AnalyteItem = new Analyte { AnalyteId = price.AnalyteId, AnalyteName = row["AnalyteName"].ToString() };
else
price.AnalyteItem = null;
resultList.Add(price);
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public SmartCollection<ClientComplaint> GetClientComplaintsOpen(Identification identification)
{
try
{
SmartCollection<ClientComplaint> resultList = new SmartCollection<ClientComplaint>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientComplaintsOpen";
DbCommand.Parameters.Clear();
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
ClientComplaint complaint = new ClientComplaint();
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
if (row["CorrectiveActionDate"] != DBNull.Value)
complaint.CorrectiveActionDate = (DateTime)row["CorrectiveActionDate"];
else
complaint.CorrectiveActionDate = null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(complaint);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
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 SmartCollection<SampleTest> SearchResultSampleTests(string searchString, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
string containerSearch = string.Empty;
int? labNumber = null;
int? sampleTestId = null;
try
{
if (searchString.Contains("-"))
{
containerSearch = searchString;
}
else if (searchString.Contains(","))
{
char[] charSeparators = new char[] { ',' };
string[] sepString = searchString.Split(charSeparators, StringSplitOptions.None);
labNumber = Convert.ToInt32(sepString[0].Trim());
sampleTestId = Convert.ToInt32(sepString[1].Trim());
}
else
{
labNumber = Convert.ToInt32(searchString.Trim());
}
}
catch (Exception)
{
throw new Exception("Search value is an invalid format");
}
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql =
@"
SELECT DISTINCT
sampleTests.id,timepoints.id as timePointId, sampleTests.parentid,sampleTests.status,sampleTests.sampleid,sampleTests.lab_number,
sampleTests.priorityid,sampleTests.typeid,sampleTests.analyteid,sampleTests.testid,
sampleTests.departmentid,sampleTests.analystid,sampleTests.method_name,sampleTests.method_number_name,
sampleTests.low,sampleTests.high,sampleTests.test_minutes, sampleTests.equipment_minutes,
sampleTests.accounting_code,dbo.ReturnNextBusinessDay(sampleTests.begin_date,timepoints.begindate_days) AS begin_date,
sampleTests.due_date,sampleTests.has_requirement_code,
sampleTests.requirement_code,sampleTests.item_price,sampleTests.rush_charge,sampleTests.bill_groupid,
sampleTests.catalogid, sampleTests.methodid, sampleTests.methodnumberid,
sampleTests.is_per_analyte, sampleTests.is_price_table,
priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template,
test.testname,test.active as testactive,analyte.analytename,analyte.controlled,
analyte.active as analyteactive,analyst.firstname, analyst.lastname,
sampleTests.endotoxin_limit,sampleTests.endotoxin_limit_uom, sampleTests.avg_weight, sampleTests.avg_weight_uom,
sampleTests.dose_per_hour, sampleTests.dose_per_hour_uom,sampleTests.route_of_administration,
sampleTests.articles,sampleTests.is_signed, timepoints.begindate_days,
(users.firstname + ' ' + users.lastname) as modifieduser,
(users2.firstname + ' ' + users2.lastname) as createduser,
sampleTests.modified_by, sampleTests.modified_date, sampleTests.created_by, sampleTests.created_date,
timepoints.timepoint_type
FROM orders_samples_tests AS sampleTests
RIGHT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.id = (
SELECT TOP 1
timepoints.id
FROM orders_samples_tests_timepoints AS timepoints
LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid
WHERE
timepointsResults.result_date IS NULL AND timepoints.parentid = sampleTests.id
ORDER BY timepoints.begindate_days
)
LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON oos.id = timepoints.oosid AND oos.is_testing_complete = 'true' -- Continue Testing
LEFT JOIN [User] as users ON sampleTests.modified_by = users.UserID
LEFT JOIN [User] as users2 ON sampleTests.created_by = users2.UserID
LEFT JOIN [User] as analyst ON sampleTests.analystid = analyst.UserID
LEFT JOIN list.departments as dpart ON sampleTests.[departmentid] = dpart.[departmentid]
LEFT JOIN list.tests as test ON sampleTests.[testid] = test.[testid]
LEFT JOIN list.analyte as analyte ON sampleTests.[analyteid] = analyte.[analyteid]
LEFT JOIN list.priorities as priorities ON sampleTests.[priorityid] = priorities.[priorityid]
LEFT JOIN orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id
WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL)
";
// Maybe Limit Results by Analyst and/or Department
DbCommand.Parameters.Clear();
if (!String.IsNullOrWhiteSpace(containerSearch))
{
sql += "AND containers.containerid = @ContainerId ";
DbCommand.Parameters.Add("@ContainerId", System.Data.SqlDbType.VarChar, 100).Value = containerSearch;
}
else if (labNumber != null && sampleTestId != null)
{
sql += "AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId ";
DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
}
else
{
sql += "AND sampleTests.lab_number = @LabNumber ";
DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
}
sql += " ORDER BY sampleTests.id";
DbCommand.CommandText = sql;
//DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.Approved;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleTest sampleTest = new SampleTest();
using (CatalogDAO catalogDao = new CatalogDAO())
{
//sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification);
}
using (SystemDAO systemDao = new SystemDAO())
{
//sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem);
}
resultList.Add(sampleTest);
}
returnDT = null;
}
return resultList;
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
}
private SmartCollection<Oos> GetOOSs(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<Oos> resultList = new SmartCollection<Oos>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetOOSs";
DbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
Oos result = new Oos();
result.OosId = Convert.ToInt32(row["OOSID"]);
if (row["ARLNumber"] != DBNull.Value)
result.ARLNumber = (int)row["ARLNumber"];
if (row["SampleTestId"] != DBNull.Value)
result.SampleTestId = (int)row["SampleTestId"];
result.ClientName = row["ClientName"].ToString();
result.Status = row["status"] != DBNull.Value ? (SampleTestStatus)row["status"] : SampleTestStatus.UnderInvestigation;
result.TestName = row["TestName"].ToString();
result.AnalyteName = row["AnalyteName"].ToString();
result.PriorityName = row["PriorityName"].ToString();
if (row["DueDate"] != DBNull.Value)
result.DueDate = (DateTime)row["DueDate"];
result.DepartmentName = row["DepartmentName"].ToString();
if (row["TimepointStudyYN"] != DBNull.Value)
result.TimepointStudyYN = (bool)row["TimepointStudyYN"];
if (row["NextTimepoint"] != DBNull.Value)
result.NextTimepoint = (DateTime)row["NextTimepoint"];
result.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
result.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32();
// result.Results =
resultList.Add(result);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
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 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;
}
}
/// <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 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 SaveOrderSampleTestOos(Oos oos, Identification identification)
{
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
return this.SaveOrderSampleTestOos(ref dbConnection, ref dbCommand, oos, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
}
public int SaveClientComplaint(ClientComplaint complaint, Identification identification)
{
int result = 0;
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
result = this.SaveClientComplaint(ref dbConnection, ref dbCommand, complaint, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public Boolean RemoveAllTokens()
{
Boolean result = false;
try {
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
string sql = "DELETE FROM tokens;";
dbCommand.CommandText = sql;
dbConnection.ExecuteCommand(dbCommand);
result = true;
}
} else {
throw new Exception("Unable to Connect");
}
}
return result;
}
catch {
throw;
}
}
public ClientComplaint GetClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? clientComplaintId)
{
try {
ClientComplaint complaint = new ClientComplaint();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientComplaint";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = clientComplaintId;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
complaint.CorrectiveActionDate = row["CorrectiveActionDate"] != DBNull.Value ? (DateTime)row["CorrectiveActionDate"] : (DateTime)SqlDateTime.Null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
returnDT = null;
}else {
returnDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return complaint;
}catch {
throw;
}
}
public int SetDatabaseVersion(int currentVersion)
{
try {
int result = 0;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
string sql = "UPDATE dbversion " +
"SET dbversion = @Version ";
DbCommand.CommandText = sql;
DbCommand.Parameters.Add("@Version", System.Data.SqlDbType.Int).Value = currentVersion;
result = DbConnection.ExecuteCommand(DbCommand);
}
} else {
throw new Exception("Unable to Connect");
}
}
return result;
} catch {
throw;
}
}
public string GetClientComplaintsOpenCount(Identification identification)
{
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
return this.GetClientComplaintsOpenCount(ref dbConnection, ref dbCommand, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
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 SmartCollection<ClientDocument> GetClientDocuments(int clientId)
{
try {
var result = new SmartCollection<ClientDocument>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
if (DbConnection.IsConnected())
using (DbCommand)
result.AddRange(GetClientDocuments(ref dbConnection, ref dbCommand, clientId));
return result;
}catch {
throw;
}
}
/// <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 SmartCollection<ClientNote> GetClientNotes(int clientId)
{
try {
SmartCollection<ClientNote> resultList = new SmartCollection<ClientNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNotes";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows) {
ClientNote note = new ClientNote();
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public int RemoveClientNote(int clientNoteId, int userId)
{
try {
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspRemoveClientNote";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = clientNoteId;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
}
}else {
throw new Exception("Unable to Connect");
}
}
return rowsAffected;
}catch {
throw;
}
}
public SmartCollection<Client> GetClientsList()
{
try {
SmartCollection<Client> results = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsList";
DbCommand.Parameters.Clear();
var reader = DbConnection.ExecuteReader(DbCommand);
results.AddRange(AutoMap.MapReaderToList<Client>(reader));
}
}else {
throw new Exception("Unable to Connect");
}
}
return results;
}catch {
throw;
}
}
public int RemoveContact(int contactId, int userId)
{
try
{
DateTime deleteDate = System.DateTime.Now;
int result = -1;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspRemoveContact";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
result = dbConnection.ExecuteCommand(dbCommand);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public Contact GetContact(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? contactId)
{
try
{
Contact contact = new Contact();
if (dbConnection.IsConnected())
{
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetContact";
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
DataTable contactDT = dbConnection.ExecuteQuery(dbCommand);
if (contactDT.Rows.Count == 1)
{
DataRow row = contactDT.Rows[0];
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
contactDT = null;
}
else
{
contactDT = null;
return null;
}
}
else
{
throw new Exception("Unable to Connect");
}
return contact;
}
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 SmartCollection<Contact> GetContacts(int clientId)
{
try
{
SmartCollection<Contact> resultList = new SmartCollection<Contact>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetContacts";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable contactsDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in contactsDT.Rows)
{
Contact contact = new Contact();
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(contact);
}
contactsDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public int ReturnToOos(TimePoint timePoint, SampleNote note, Identification identification)
{
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
try
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
SystemDAO.SaveChangeAudit<TimePoint>(ref dbConnection, ref dbCommand,
"Return to OOS #:" + timePoint.OosId.ToString(),
"Status",
"Approved",
"Pending Investigation",
ModuleNames.Samples,
timePoint.ParentId,
timePoint.Pk,
identification.UserId);
this.SaveSampleNote(ref dbConnection, ref dbCommand, ref note, (int)timePoint.ParentId, identification);
string sql = string.Empty;
sql = "UPDATE orders_samples_tests_timepoints_oos " +
"SET status = @Status " +
"WHERE id = @ID";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = timePoint.OosId;
DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)EnumOosStatus.Open;
DbCommand.CommandText = sql;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
OrderSampleTestsSetStatus(ref dbConnection, ref dbCommand, (int)timePoint.ParentId, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
return rowsAffected;
}
catch
{
throw;
}
}
}