public int OrderSampleTestsSetStatus(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleTestId, Identification identification, int? forceStatus = null)
{
try
{
int returnValue = 0;
int remainingTimepoints = 0;
int remainingOosRecords = 0;
SampleTestStatus setStatus = SampleTestStatus.InProgress;
string sql = string.Empty;
if (forceStatus.IsNotNull())
{
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders_samples_tests
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = forceStatus;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
return returnValue;
}
dbCommand.Parameters.Clear();
sql = @"
SELECT Count(timepoints.Id) as CountOfId
from orders_samples_tests_timepoints timepoints
LEFT JOIN orders_samples_tests_timepoints_results AS result ON timepoints.id = result.parentid
LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON timepoints.oosid = oos.id
WHERE timepoints.parentid = @ID AND result.created_date IS NULL AND (oos.is_testing_complete = 'false' OR oos.is_testing_complete IS NULL) ;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
dbCommand.CommandText = sql;
remainingTimepoints = (int)dbConnection.ExecuteScalar(dbCommand);
sql = @"
SELECT Count(DISTINCT oos.Id) as CountOfId
from orders_samples_tests_timepoints_oos oos
LEFT JOIN orders_samples_tests_timepoints AS timepoint ON timepoint.oosid = oos.id
WHERE timepoint.parentid = @ID and oos.status < 2 ;
";
dbCommand.CommandText = sql;
remainingOosRecords = (int)dbConnection.ExecuteScalar(dbCommand);
if (remainingTimepoints == 0 && remainingOosRecords == 0)
setStatus = SampleTestStatus.InProgress;
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders_samples_tests
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = sampleTestId;
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)setStatus;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
//Return Total Number of Updated Records or Last Primary ID
dbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public void SaveClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Client client, int userId)
{
try {
foreach (var doc in client.Documents) {
if (doc.ClientDocumentId < 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientId", client.ClientId);
dbCommand.Parameters.AddWithValue("@Filename", doc.Filename);
dbCommand.Parameters.AddWithValue("@DocumentData", Convert.FromBase64CharArray(doc.DocumentData.ToCharArray(), 0, doc.DocumentData.Length));
dbCommand.Parameters.AddWithValue("@CreatedBy", userId);
dbCommand.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
doc.ClientDocumentId = dbConnection.ExecuteCommand(dbCommand);
}else if (doc.IsDirty) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientDocumentId", doc.ClientDocumentId);
dbCommand.Parameters.AddWithValue("@ClientId", client.ClientId);
dbCommand.Parameters.AddWithValue("@ModifiedBy", userId);
dbCommand.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
dbCommand.Parameters.AddWithValue("@DeleteDate", doc.DeleteDate);
dbConnection.ExecuteCommand(dbCommand);
}
}
}catch {
throw;
}
}
public int SaveClientPricing(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try {
int returnValue = 0;
foreach (ClientPricing price in client.Prices) {
if (price.IsDirty) {
SystemDAO.SaveChangeAudit<ClientPricing>(ref dbConnection, ref dbCommand,
GetClientPricing(price.ClientPricingId != -1 ? price.ClientPricingId : -1),
price,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.Parameters.Clear();
if (price.ClientPricingId <= 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientPricing";
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
}else {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientPricing";
dbCommand.Parameters.Add("@ClientPricingId", System.Data.SqlDbType.Int).Value = price.ClientPricingId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = price.DeleteDate;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@MethodId", System.Data.SqlDbType.Int).Value = price.MethodId;
dbCommand.Parameters.Add("@MethodNumberId", System.Data.SqlDbType.Int).Value = price.MethodNumberId;
dbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = price.AnalyteId;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.Text, 100).Value = price.Description;
dbCommand.Parameters.Add("@Discount", System.Data.SqlDbType.Decimal).Value = price.Discount ?? 0;
dbCommand.Parameters.Add("@FlatRate", System.Data.SqlDbType.Decimal).Value = price.FlatRate ?? 0;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public int RemoveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId, int userId)
{
try
{
int returnValue = 0;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspRemoveContacts";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
returnValue = dbConnection.ExecuteCommand(dbCommand);
return returnValue;
}
catch
{
throw;
}
}
public int SaveClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ClientComplaint complaint, Identification identification)
{
try
{
int returnValue = 0;
if (complaint.IsDirty)
{
SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk),
complaint,
ModuleNames.Clients,
complaint.ClientId,
identification.UserId);
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientComplaint";
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = complaint.ClientId;
dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Int).Value = (bool)complaint.StatusYN;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber;
dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
return returnValue;
}
catch
{
throw;
}
}
public void UpdateOrderSampleTestTimePointReportData(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int timePointId, bool isOnReport, DateTime? reportDate, int reportBy)
{
string sql = string.Empty;
try
{
sql = @"
UPDATE orders_samples_tests_timepoints
SET report_by = @ReportBy, report_date = @ReportDate, is_on_report = @IsOnReport
WHERE id = @ID";
dbCommand.Parameters.Clear();
dbCommand.CommandText = sql;
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = timePointId;
dbCommand.Parameters.Add("@ReportBy", System.Data.SqlDbType.Int).Value = reportBy;
dbCommand.Parameters.Add("@ReportDate", System.Data.SqlDbType.DateTime).Value = reportDate;
dbCommand.Parameters.Add("@IsOnReport", System.Data.SqlDbType.Bit).Value = isOnReport;
dbConnection.ExecuteCommand(dbCommand);
}
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 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;
}
}
}
public int SaveOrderInvoiceItems(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Invoice invoice, Identification identification)
{
try
{
int returnValue = 0;
dbCommand.CommandText = @"
INSERT INTO orders_invoices_items
(invoiceid,item_type,txn_lineid,item_recordid,accounting_code,line_description,quantity,class,amount,labnumber)
VALUES
(@Invoiceid,@ItemType,@TxnLineid,@ItemRecordId,@AccountingCode,@LineDescription,@Quantity,@Class,@Amount,@Labnumber)
";
foreach (InvoiceItem item in invoice.InvoiceItems)
{
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Invoiceid", System.Data.SqlDbType.Int).Value = invoice.ARLNumber;
dbCommand.Parameters.Add("@ItemType", System.Data.SqlDbType.Int).Value = (int)item.ItemType;
//dbCommand.Parameters.Add("@ItemRecordId", System.Data.SqlDbType.Int).Value = item.ItemRecordId.Value;
dbCommand.Parameters.Add("@TxnLineid", System.Data.SqlDbType.VarChar, 50).Value = item.TxnLineID ?? string.Empty;
dbCommand.Parameters.Add("@AccountingCode", System.Data.SqlDbType.VarChar, 16).Value = item.AccountingCode ?? string.Empty;
dbCommand.Parameters.Add("@LineDescription", System.Data.SqlDbType.VarChar, 4096).Value = item.Description ?? string.Empty;
dbCommand.Parameters.Add("@Quantity", System.Data.SqlDbType.Int).Value = item.Quantity ?? 0;
dbCommand.Parameters.Add("@Class", System.Data.SqlDbType.VarChar, 25).Value = item.Class ?? string.Empty;
dbCommand.Parameters.Add("@Amount", System.Data.SqlDbType.Decimal).Value = item.Amount ?? 0;
dbCommand.Parameters.Add("@Labnumber", System.Data.SqlDbType.Int).Value = item.ARLNumber;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
return returnValue;
}
catch
{
throw;
}
}
public int RemoveOrderSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, dynamic parentId, int userId)
{
try
{
int returnValue = 0;
string sql = string.Empty;
if (parentId is int)
{
sql = "UPDATE orders_samples_tests " +
"SET delete_date = @DeleteDate, modified_by = @UserID " +
"WHERE Parentid = @ParentId";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = parentId;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
}
else
{
sql = string.Format("UPDATE orders_samples_tests " +
"SET delete_date = @DeleteDate, modified_by = @UserID " +
"WHERE parentid in ({0})", String.Join(",", parentId.Count() > 0 ? parentId : new List<int>() { -1 }));
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
}
return returnValue;
}
catch
{
throw;
}
}
public int RemoveSample(int arlNumber, Identification identification)
{
try
{
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspRemoveSample";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
this.RemoveSampleAnalytes(ref dbConnection, ref dbCommand, arlNumber, identification.UserId);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return rowsAffected;
}
catch
{
throw;
}
}
public int RemoveOrderSampleTest(int id, int userId)
{
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
try
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = string.Empty;
sql = "UPDATE orders_samples_tests " +
"SET delete_date = @DeleteDate, modified_by = @ModifiedBy " +
"WHERE id = @ID";
DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.CommandText = sql;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
}
}
else
{
throw new Exception("Unable to Connect");
}
return rowsAffected;
}
catch
{
throw;
}
}
}
public int ReinstateOrderSampleTest(int id, SampleNote note, Identification identificaion)
{
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
try
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
this.SaveSampleNote(ref dbConnection, ref dbCommand, ref note, id, identificaion);
string sql = string.Empty;
sql = "UPDATE orders_samples_tests " +
"SET status = @Status, modified_by = @ModifiedBy " +
"WHERE id = @ID";
DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id;
DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identificaion.UserId;
DbCommand.CommandText = sql;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
}
}
else
{
throw new Exception("Unable to Connect");
}
return rowsAffected;
}
catch
{
throw;
}
}
}
public int OrderSetStatus(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int id, Identification identification, int? forceStatus = null)
{
try
{
int returnValue = 0;
int remainingInvoicesPending = 0;
EnumOrderStatus setStatus = EnumOrderStatus.Open;
string sql = string.Empty;
if (forceStatus.IsNotNull())
{
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = forceStatus;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
return returnValue;
}
dbCommand.Parameters.Clear();
sql = @"
SELECT Count(DISTINCT tests.Id) as CountOfId
from orders
LEFT JOIN orders_samples_tests AS tests ON tests.parentid = orders.id
WHERE
orders.id = @Id AND
(orders.delete_date IS NULL AND orders.status < @OrderInvoiced AND
tests.delete_date IS NULL AND tests.status != @TestInvoiced)
;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
dbCommand.Parameters.Add("@OrderInvoiced", System.Data.SqlDbType.Int).Value = EnumOrderStatus.Invoiced;
dbCommand.Parameters.Add("@TestInvoiced", System.Data.SqlDbType.Int).Value = SampleTestStatus.Completed;
dbCommand.CommandText = sql;
remainingInvoicesPending = (int)dbConnection.ExecuteScalar(dbCommand);
if (remainingInvoicesPending == 0)
setStatus = EnumOrderStatus.Invoiced;
else return 0;
dbCommand.Parameters.Clear();
sql = @"
UPDATE orders
SET status = @Status, modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)setStatus;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.CommandText = sql;
returnValue = dbConnection.ExecuteCommand(dbCommand);
dbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public int SaveSampleNotes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Sample sample, Identification identification)
{
try
{
int returnValue = 0;
foreach (SampleNote note in sample.SampleNotes)
{
if (note.IsDirty)
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
if (note.SampleNoteId <= 0)
{
dbCommand.CommandText = "uspInsertSampleNote";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
/*SystemDAO.SaveChangeAudit<SampleNote>(ref dbConnection, ref dbCommand,
GetSampleNote(ref dbConnection, ref dbCommand, note.Pk, identification),
note,
ModuleNames.Samples,
sample.Pk,
identification.UserId); */
dbCommand.Parameters.Clear();
dbCommand.CommandText = "uspUpdateSampleNote";
dbCommand.Parameters.Add("@SampleNoteId", System.Data.SqlDbType.Int).Value = note.SampleNoteId;
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 = identification.UserId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = note.SampleTestId ?? note.SampleTestId;
dbCommand.Parameters.Add("@Note", System.Data.SqlDbType.NVarChar, 4000).Value = note.Note ?? SqlString.Null;
dbCommand.Parameters.Add("@IncludeOnWOYN", System.Data.SqlDbType.Bit).Value = note.IncludeOnWOYN;
dbCommand.Parameters.Add("@IncludeOnCOAYN", System.Data.SqlDbType.Bit).Value = note.IncludeOnCOAYN;
dbCommand.Parameters.Add("@BillGroup", System.Data.SqlDbType.Int).Value = note.BillGroup;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public int SaveOrderSampleTestContainers(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, SampleTest sampleTest, Sample sample, int userId, List<int> containerList)
{
try
{
int returnValue = 0;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspRemoveSampleTestContainers";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
dbConnection.ExecuteCommand(dbCommand);
foreach (int container in containerList)
{
dbCommand.CommandText = "uspRemoveSampleTestContainers";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
dbCommand.Parameters.Add("@ContainerNumber", System.Data.SqlDbType.Text, 100).Value = container;
dbCommand.Parameters.Add("@Label", System.Data.SqlDbType.NVarChar, 50).Value = sample.ARLNumber + "-" + container;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
return returnValue;
}
catch
{
throw;
}
}
public int SaveSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Sample sample, Identification identification)
{
try
{
int returnValue = 0;
//string sql = string.Empty;
//bool reCalculatePricing = sample.Status != EnumSampleStatus.Canceled;
foreach (SampleTest sampleTest in sample.SampleTests)
{
if (sampleTest.IsDirty)
{
// Load Required Objects (do not trust what is passed in to be propulated or correct for pricing)
// May not need to reprice due to facts like already invoiced
//SmartCollection<ClientPricing> pricing = new SmartCollection<ClientPricing>();
//Priority priority = new Priority();
//SmartCollection<PricingRule> matrixItems = new SmartCollection<PricingRule>();
CatalogItem catalogItem = new CatalogItem();
/*if (reCalculatePricing)
{
using (ClientDAO customerDao = new ClientDAO())
{
//priority = customerDao.GetCustomerPriorityOverrideOrDefault((int)order.Customer.Id, (int)sampleTest.PriorityId);
priority = null;
pricing.AddRange(customerDao.GetClientPricingOverrides((int)sample.Client.ClientId, (int)sampleTest.MethodId, (int)sampleTest.MethodNumberId, (int)sampleTest.AnalyteId));
}
using (CatalogDAO catalogDao = new CatalogDAO())
{
catalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification);
}
using (SystemDAO systemDao = new SystemDAO())
{
//matrixItems.AddRange(systemDao.ReturnMatrixItems(order.Samples.Where(x => x.Id == sampleTest.SampleId).Select(x => x).FirstOrDefault().Clone(), sampleTest, reCalculatePricing));
}
}
SystemDAO.SaveChangeAudit<SampleTest>(ref dbConnection, ref dbCommand,
GetSampleTest(ref dbConnection, ref dbCommand, sampleTest.Pk ?? 0, identification),
sampleTest,
ModuleNames.Samples,
sample.Pk,
identification.UserId); */
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
if (sampleTest.SampleTestId <= 0 || sampleTest.SampleTestId == null)
{
dbCommand.CommandText = "uspInsertSampleTest";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
dbCommand.CommandText = "uspUpdateSampleTest";
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = sampleTest.DeleteDate ?? sampleTest.DeleteDate;
}
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
if (sampleTest.BeginDate > DateTime.Today)
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = SampleTestStatus.Scheduled;
else
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = SampleTestStatus.InProgress;
dbCommand.Parameters.Add("@Stage", System.Data.SqlDbType.Int).Value = SampleTestStage.AccInReview;
dbCommand.Parameters.Add("@Type", System.Data.SqlDbType.Int).Value = sampleTest.Type;
dbCommand.Parameters.Add("@CatalogId", System.Data.SqlDbType.Int).Value = sampleTest.CatalogId ?? sampleTest.CatalogId;
dbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = sampleTest.TestId ?? sampleTest.TestId;
dbCommand.Parameters.Add("@TestName", System.Data.SqlDbType.NVarChar, 250).Value = sampleTest.TestName ?? sampleTest.TestName;
dbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = sampleTest.Analyte.AnalyteId != null ? sampleTest.Analyte.AnalyteId : sampleTest.AnalyteId != null ? sampleTest.AnalyteId : null;
dbCommand.Parameters.Add("@AnalyteName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.AnalyteName ?? sampleTest.AnalyteName;
dbCommand.Parameters.Add("@ControlledYN", System.Data.SqlDbType.Bit).Value = sampleTest.ControlledYN != null ? (bool)sampleTest.ControlledYN : false;
dbCommand.Parameters.Add("@AntibioticYN", System.Data.SqlDbType.Bit).Value = sampleTest.AntibioticYN != null ? (bool)sampleTest.AntibioticYN : false;
dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = sampleTest.Department.DepartmentId ?? sampleTest.Department.DepartmentId;
dbCommand.Parameters.Add("@DepartmentName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.Department.DepartmentName ?? sampleTest.Department.DepartmentName;
dbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = sampleTest.Analyst.UserId != null ? (int)sampleTest.Analyst.UserId : SqlInt32.Null;
dbCommand.Parameters.Add("@AnalystName", System.Data.SqlDbType.NVarChar, 100).Value = sampleTest.Analyst.FullName != null ? sampleTest.Analyst.FullName : SqlString.Null;
dbCommand.Parameters.Add("@Containers", System.Data.SqlDbType.NVarChar, 4000).Value = sampleTest.Containers != null ? sampleTest.Containers : SqlString.Null;
dbCommand.Parameters.Add("@MethodId", System.Data.SqlDbType.Int).Value = sampleTest.MethodId ?? sampleTest.MethodId;
dbCommand.Parameters.Add("@MethodName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.MethodName ?? sampleTest.MethodName;
dbCommand.Parameters.Add("@MethodNumberId", System.Data.SqlDbType.Int).Value = sampleTest.MethodNumberId ?? sampleTest.MethodNumberId;
dbCommand.Parameters.Add("@MethodNumberName", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.MethodNumberName ?? sampleTest.MethodNumberName;
dbCommand.Parameters.Add("@OutsourcedYN", System.Data.SqlDbType.Bit).Value = sampleTest.OutsourcedYN != null ? (bool)sampleTest.OutsourcedYN : false;
dbCommand.Parameters.Add("@BasePrice", System.Data.SqlDbType.Money).Value = sampleTest.BasePrice != null ? sampleTest.BasePrice : SqlMoney.Null;
dbCommand.Parameters.Add("@LowSpec", System.Data.SqlDbType.Decimal).Value = sampleTest.LowSpec != null ? Convert.ToDecimal(sampleTest.LowSpec) : SqlDecimal.Null;
dbCommand.Parameters.Add("@HighSpec", System.Data.SqlDbType.Decimal).Value = sampleTest.HighSpec != null ? Convert.ToDecimal(sampleTest.HighSpec) : SqlDecimal.Null;
dbCommand.Parameters.Add("@CalculatePercentageExpectedYN", System.Data.SqlDbType.Bit).Value = sampleTest.CalculatePercentageExpectedYN != null ? (bool)sampleTest.CalculatePercentageExpectedYN : false;
dbCommand.Parameters.Add("@OtherSpec", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.OtherSpec ?? sampleTest.OtherSpec;
dbCommand.Parameters.Add("@TestMinutes", System.Data.SqlDbType.SmallInt).Value = sampleTest.TestMinutes ?? sampleTest.TestMinutes;
dbCommand.Parameters.Add("@EquipmentMinutes", System.Data.SqlDbType.SmallInt).Value = sampleTest.EquipmentMinutes ?? sampleTest.EquipmentMinutes;
dbCommand.Parameters.Add("@AccountingCode", System.Data.SqlDbType.NVarChar, 50).Value = sampleTest.AccountingCode ?? sampleTest.AccountingCode;
dbCommand.Parameters.Add("@Instructions", System.Data.SqlDbType.NVarChar, 1024).Value = sampleTest.Instructions ?? sampleTest.Instructions;
dbCommand.Parameters.Add("@RequirementYN", System.Data.SqlDbType.Bit).Value = sampleTest.RequirementYN != null ? (bool)sampleTest.RequirementYN : false;
dbCommand.Parameters.Add("@RequirementDescription", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.RequirementDescription ?? sampleTest.RequirementDescription;
dbCommand.Parameters.Add("@EndotoxinLimit", System.Data.SqlDbType.Decimal).Value = sampleTest.EndotoxinLimit != null ? (decimal)sampleTest.EndotoxinLimit : SqlDecimal.Null;
dbCommand.Parameters.Add("@EndotoxinLimitUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.EndotoxinLimitUOM ?? sampleTest.EndotoxinLimitUOM;
dbCommand.Parameters.Add("@AverageWeight", System.Data.SqlDbType.Int).Value = sampleTest.AverageWeight ?? sampleTest.AverageWeight;
dbCommand.Parameters.Add("@AverageWeightUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.AverageWeightUOM ?? sampleTest.AverageWeightUOM;
dbCommand.Parameters.Add("@DosePerHour", System.Data.SqlDbType.Decimal).Value = sampleTest.DosePerHour != null ? (decimal)sampleTest.DosePerHour : SqlDecimal.Null;
dbCommand.Parameters.Add("@DosePerHourUOM", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.DosePerHourUOM ?? sampleTest.DosePerHourUOM;
dbCommand.Parameters.Add("@RouteOfAdministration", System.Data.SqlDbType.NVarChar, 20).Value = sampleTest.RouteOfAdministration ?? sampleTest.RouteOfAdministration;
dbCommand.Parameters.Add("@SignedYN", System.Data.SqlDbType.Bit).Value = sampleTest.SignedYN != null ? (bool)sampleTest.SignedYN : false;
dbCommand.Parameters.Add("@Articles", System.Data.SqlDbType.Int).Value = sampleTest.Articles;
dbCommand.Parameters.Add("@SignedName", System.Data.SqlDbType.NVarChar, 100).Value = sampleTest.SignedName ?? sampleTest.SignedName;
dbCommand.Parameters.Add("@PriorityId", System.Data.SqlDbType.Int).Value = sampleTest.Priority.PriorityId ?? (int)sampleTest.Priority.PriorityId;
dbCommand.Parameters.Add("@AdditionalDays", System.Data.SqlDbType.Int).Value = sampleTest.Priority.PlusDays != null ? (int)sampleTest.Priority.PlusDays : SqlInt32.Null;
dbCommand.Parameters.Add("@PriceAdjustment", System.Data.SqlDbType.Decimal).Value = sampleTest.Priority.PriceAdjustment != null ? (decimal)sampleTest.Priority.PriceAdjustment : SqlDecimal.Null;
dbCommand.Parameters.Add("@DiscountAllowedYN", System.Data.SqlDbType.Bit).Value = sampleTest.DiscountAllowedYN != null ? (bool)sampleTest.DiscountAllowedYN : false;
dbCommand.Parameters.Add("@BillGroup", System.Data.SqlDbType.Int).Value = sampleTest.BillGroup ?? sampleTest.BillGroup;
dbCommand.Parameters.Add("@BeginDate", System.Data.SqlDbType.DateTime).Value = sampleTest.BeginDate.Value > DateTime.MinValue ? (DateTime)sampleTest.BeginDate : SqlDateTime.Null;
dbCommand.Parameters.Add("@DueDate", System.Data.SqlDbType.DateTime).Value = sampleTest.DueDate.Value > DateTime.MinValue ? (DateTime)sampleTest.DueDate : SqlDateTime.Null;
//dbCommand.Parameters.Add("@ApprovedBy", System.Data.SqlDbType.Int).Value = sampleTest.ApprovedBy ?? sampleTest.ApprovedBy;
//dbCommand.Parameters.Add("@ApprovedDate", System.Data.SqlDbType.DateTime).Value = sampleTest.ApprovedDate != null && sampleTest.ApprovedDate.Value > DateTime.MinValue ? (SqlDateTime)sampleTest.ApprovedDate : SqlDateTime.Null;
/*if (reCalculatePricing)
{
decimal[] testPrice = Routines.Pricing.CalculateTestPrice(sampleTest, pricing, priority,
matrixItems,
sample.Samples.Where(x => x.ARLNumber == sampleTest.SampleId).SelectMany(x => x.SampleAnalytes).Where(a => a.DeleteDate == null).Count(),
catalogItem.BasePrice);
dbCommand.Parameters.Add("@ItemPrice", System.Data.SqlDbType.Decimal).Value = testPrice[0];
dbCommand.Parameters.Add("@RushCharge", System.Data.SqlDbType.Decimal).Value = testPrice[1];
}
else
{
dbCommand.Parameters.Add("@ItemPrice", System.Data.SqlDbType.Decimal).Value = sampleTest.BasePrice;
dbCommand.Parameters.Add("@RushCharge", System.Data.SqlDbType.Decimal).Value = sampleTest.PriceAdjustment;
} */
//int? oldSampleTestId = sampleTest.SampleTestId;
if (sampleTest.SampleTestId > 0)
returnValue += dbConnection.ExecuteCommand(dbCommand);
else
{
// returnValue = Primary Key Id
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
sampleTest.SampleTestId = returnValue;
// Update New Note Records
//var sampleNotes = order.Notes.Where(x => x.LinkedType == EnumNoteLink.Test && x.LinkedId == oldSampleTestId);
//foreach (ClientNote sampleNote in sampleNotes) {
// if (sampleNote != null)
// sampleNote.LinkedId = sampleTest.Id;
//}
}
// Save Order Sample Test Containers
//this.SaveOrderSampleTestContainers(ref dbConnection, ref dbCommand, sampleTest, sample, identification.UserId);
// Save Order Sample Test Timepoints
//this.SaveOrderSampleTestTimePoints(ref dbConnection, ref dbCommand, sampleTest, sample, identification.UserId, true);
}
}
//Return Total Number of Updated Records or Last Primary ID
return returnValue;
}
catch
{
throw;
}
}
public int SaveOrderSampleTestOos(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Oos oos, Identification identification)
{
try
{
int returnValue = 0;
string sql = string.Empty;
dbCommand.Parameters.Clear();
if (oos.IsDirty)
{
sql = string.Empty;
if (oos.OosId.IsNull() || oos.OosId <= 0)
{
sql = @"
INSERT INTO orders_samples_tests_timepoints_oos
([parentid]
,[status]
,[is_sample_prep_acceptable]
,[is_raw_data_acceptable]
,[is_standard_acceptable]
,[is_method_acceptable]
,[is_parameters_acceptable]
,[is_trends_acceptable]
,[is_analyst_acceptable]
,[is_retest]
,[primary_cause]
,[client_contact_id]
,[client_contact_phone_id]
,[is_client_notified]
,[is_testing_complete]
,[review_comment]
,[conclusion_comment]
,[investigator_id]
,[investigation_date]
,[approved_by]
,[approved_date]
,[created_by]
)
VALUES
(@ParentId
,@Status
,@IsSamplePrepAcceptable
,@IsRawDataAcceptable
,@IsStandardAcceptable
,@IsMethodAcceptable
,@IsParametersAcceptable
,@IsTrendsAcceptable
,@IsAnalystAcceptable
,@IsRetest
,@PrimaryCause
,@ClientContactId
,@ClientContactPhoneId
,@IsClientNotified
,@IsTestingComplete
,@ReviewComment
,@ConclusionComment
,@InvestigatorId
,@InvestigationDate
,@ApprovedBy
,@ApprovedDate
,@CreatedBy
);
SELECT id FROM orders_samples_tests_timepoints_oos WHERE (id = SCOPE_IDENTITY());
";
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = oos.SampleTestId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}
else
{
sql = @"
UPDATE orders_samples_tests_timepoints_oos
SET status = @Status
,is_sample_prep_acceptable = @IsSamplePrepAcceptable
,is_raw_data_acceptable = @IsRawDataAcceptable
,is_standard_acceptable = @IsStandardAcceptable
,is_method_acceptable = @IsMethodAcceptable
,is_parameters_acceptable = @IsParametersAcceptable
,is_trends_acceptable = @IsTrendsAcceptable
,is_analyst_acceptable = @IsAnalystAcceptable
,is_retest = @IsRetest
,primary_cause = @PrimaryCause
,client_contact_id = @ClientContactId
,client_contact_phone_id = @ClientContactPhoneId
,is_client_notified = @IsClientNotified
,is_testing_complete = @IsTestingComplete
,review_comment = @ReviewComment
,conclusion_comment = @ConclusionComment
,investigator_id = @InvestigatorId
,investigation_date = @InvestigationDate
,approved_by = @ApprovedBy
,approved_date = @ApprovedDate
,modified_by = @ModifiedBy
WHERE id = @ID;
";
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = oos.OosId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
}
dbCommand.CommandText = sql;
/*dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)oos.Status;
dbCommand.Parameters.Add("@IsSamplePrepAcceptable", System.Data.SqlDbType.Bit).Value = oos.PreparationYN;
dbCommand.Parameters.Add("@IsRawDataAcceptable", System.Data.SqlDbType.Bit).Value = oos.RawDataYN;
dbCommand.Parameters.Add("@IsStandardAcceptable", System.Data.SqlDbType.Bit).Value = oos.StandardsReagentsYN;
dbCommand.Parameters.Add("@IsMethodAcceptable", System.Data.SqlDbType.Bit).Value = oos.MethodologyYN;
dbCommand.Parameters.Add("@IsParametersAcceptable", System.Data.SqlDbType.Bit).Value = oos.InstrumentParametersYN;
dbCommand.Parameters.Add("@IsTrendsAcceptable", System.Data.SqlDbType.Bit).Value = oos.TrendsYN;
dbCommand.Parameters.Add("@IsAnalystAcceptable", System.Data.SqlDbType.Bit).Value = oos.AnalystYN;
dbCommand.Parameters.Add("@IsRetest", System.Data.SqlDbType.Bit).Value = oos.IsRetest;
dbCommand.Parameters.Add("@PrimaryCause", System.Data.SqlDbType.VarChar, 50).Value = oos.PrimaryCause ?? string.Empty;
dbCommand.Parameters.Add("@ClientContactId", System.Data.SqlDbType.Int).Value = oos.ClientContactEmailYN ?? 0;
dbCommand.Parameters.Add("@ClientContactPhoneId", System.Data.SqlDbType.Int).Value = oos.ClientContactPhoneYN ?? 0;
dbCommand.Parameters.Add("@IsClientNotified", System.Data.SqlDbType.Bit).Value = oos.IsClientNotified;
dbCommand.Parameters.Add("@IsTestingComplete", System.Data.SqlDbType.Bit).Value = oos.IsTestingComplete;
dbCommand.Parameters.Add("@ReviewComment", System.Data.SqlDbType.NVarChar, -1).Value = oos.AdditionalObservations ?? string.Empty;
dbCommand.Parameters.Add("@ConclusionComment", System.Data.SqlDbType.NVarChar, -1).Value = oos.Conclusion ?? string.Empty;
dbCommand.Parameters.Add("@InvestigatorId", System.Data.SqlDbType.Int).Value = oos.InvestigatorId ?? 0;
dbCommand.Parameters.Add("@InvestigationDate", System.Data.SqlDbType.DateTime).Value = oos.InvestigationDate.HasValue ? oos.InvestigationDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ApprovedBy", System.Data.SqlDbType.Int).Value = oos.ApprovedBy ?? 0;
dbCommand.Parameters.Add("@ApprovedDate", System.Data.SqlDbType.DateTime).Value = oos.ApprovedDate.HasValue ? oos.ApprovedDate.Value : SqlDateTime.Null;
*/
if (oos.OosId.IsNotNull() && oos.OosId > 0)
returnValue += dbConnection.ExecuteCommand(dbCommand);
else
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
// Create Timepoint Entry if IsRetest
// Calculate SampleTest Status
if (oos.SampleTestId.IsNotNull())
this.OrderSampleTestsSetStatus(ref dbConnection, ref dbCommand, (int)oos.SampleTestId, identification);
// Release Lock
using (SystemDAO systemDao = new SystemDAO())
{
//systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Order, oos.OrderId.ToString(), identification.Token);
}
}
return returnValue;
}
catch
{
throw;
}
}
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;
}
}
/// <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 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 int SaveSampleAnalytes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Sample sample, int userId)
{
try
{
int returnValue = 0;
foreach (SampleAnalyte sampleAnalyte in sample.SampleAnalytes)
{
if (sampleAnalyte.IsDirty)
{
/*SystemDAO.SaveChangeAudit<SampleAnalyte>(ref dbConnection, ref dbCommand,
GetSampleAnalyte(ref dbConnection, ref dbCommand, sampleAnalyte.Pk),
sampleAnalyte,
ModuleNames.Samples,
sample.ARLNumber,
userId); */
SystemDAO systemDao = new SystemDAO();
Analyte analyte = new Analyte();
if (sampleAnalyte.AnalyteId != null)
analyte = systemDao.GetAnalyteItem(ref dbConnection, ref dbCommand, (int)sampleAnalyte.AnalyteId);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
if (sampleAnalyte.SampleAnalyteId <= 0)
{
dbCommand.CommandText = "uspInsertSampleAnalyte";
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
dbCommand.CommandText = "uspUpdateSampleAnalyte";
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber;
dbCommand.Parameters.Add("@SampleAnalyteId", System.Data.SqlDbType.Int).Value = sampleAnalyte.SampleAnalyteId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = sampleAnalyte.DeleteDate.HasValue ? sampleAnalyte.DeleteDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
dbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = sampleAnalyte.AnalyteId ?? SqlInt32.Null;
dbCommand.Parameters.Add("@AnalyteName", System.Data.SqlDbType.NVarChar, 100).Value = analyte != null ? analyte.AnalyteName : SqlString.Null;
dbCommand.Parameters.Add("@Amount", System.Data.SqlDbType.Decimal).Value = sampleAnalyte.Amount != null ? Convert.ToDecimal(sampleAnalyte.Amount) : SqlDecimal.Null;
dbCommand.Parameters.Add("@AmountUOM", System.Data.SqlDbType.NVarChar, 100).Value = sampleAnalyte.AmountUOM ?? SqlString.Null;
dbCommand.Parameters.Add("@Concentration", System.Data.SqlDbType.Decimal).Value = sampleAnalyte.Concentration != null ? Convert.ToDecimal(sampleAnalyte.Concentration) : SqlDecimal.Null;
dbCommand.Parameters.Add("@ConcentrationUOM", System.Data.SqlDbType.NVarChar, 100).Value = sampleAnalyte.ConcentrationUOM ?? SqlString.Null;
dbCommand.Parameters.Add("@ControlledYN", System.Data.SqlDbType.Bit).Value = analyte.ControlledYN;
dbCommand.Parameters.Add("@AntibioticYN", System.Data.SqlDbType.Bit).Value = analyte.AntibioticYN;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
/// <summary>
/// Save Client
/// </summary>
/// <returns></returns>
public int SaveClient(Client client, Guid userToken, int userId)
{
try {
int returnValue = -1;
bool insertNewRecord = false;
string sql = string.Empty;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
try { // Try Catch here allows other exceptions to rollback transactions
if (client.IsDirty) {
SystemDAO.SaveChangeAudit<Client>(ref dbConnection, ref dbCommand,
GetClient(ref dbConnection, ref dbCommand, client.Pk ?? 0),
client,
ModuleNames.Clients,
client.Pk,
userId);
DbCommand.Parameters.Clear();
if (client.ClientId == null || client.ClientId <= 0) {
insertNewRecord = true;
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspInsertClient";
DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}else {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspUpdateClient";
DbCommand.Parameters.Add("@ClientID", System.Data.SqlDbType.Int).Value = client.ClientId;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
DbCommand.Parameters.Add("@ClientName", System.Data.SqlDbType.Text, 100).Value = client.ClientName != null ? client.ClientName : SqlString.Null;
DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId != null ? client.AccountingId : SqlString.Null;
DbCommand.Parameters.Add("@TermId", System.Data.SqlDbType.Int).Value = client.Term.TermId != null ? client.Term.TermId : (int?)SqlInt32.Null;
DbCommand.Parameters.Add("@CreditCheckYN", System.Data.SqlDbType.Bit).Value = client.CreditCheckYN != null ? client.CreditCheckYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@CreditHoldYN", System.Data.SqlDbType.Bit).Value = client.CreditHoldYN != null ? client.CreditHoldYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@WebClientYN", System.Data.SqlDbType.Bit).Value = client.WebClientYN != null ? client.WebClientYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = client.GMPYN != null ? client.GMPYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@BillingAddress", System.Data.SqlDbType.Text, 100).Value = client.BillingAddress != null ? client.BillingAddress : SqlString.Null;
DbCommand.Parameters.Add("@BillingCity", System.Data.SqlDbType.Text, 100).Value = client.BillingCity != null ? client.BillingCity : SqlString.Null;
if (client.BillingState != null && client.BillingState.StateId != null)
DbCommand.Parameters.Add("@BillingStateId", System.Data.SqlDbType.Int).Value = client.BillingState.StateId;
DbCommand.Parameters.Add("@BillingZip", System.Data.SqlDbType.Text, 10).Value = client.BillingZip != null ? client.BillingZip : SqlString.Null;
if (client.BillingCountry != null && client.BillingCountry.CountryId != null)
DbCommand.Parameters.Add("@BillingCountryId", System.Data.SqlDbType.Int).Value = client.BillingCountry.CountryId;
DbCommand.Parameters.Add("@SameAsBillingYN", System.Data.SqlDbType.Bit).Value = client.SameAsBillingYN != null ? client.SameAsBillingYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@ShippingAddress", System.Data.SqlDbType.Text, 100).Value = client.ShippingAddress != null ? client.ShippingAddress : SqlString.Null;
DbCommand.Parameters.Add("@ShippingCity", System.Data.SqlDbType.Text, 100).Value = client.ShippingCity != null ? client.ShippingCity : SqlString.Null;
if (client.ShippingState != null && client.ShippingState.StateId != null)
DbCommand.Parameters.Add("@ShippingStateId", System.Data.SqlDbType.Int).Value = client.ShippingState.StateId;
DbCommand.Parameters.Add("@ShippingZip", System.Data.SqlDbType.Text, 10).Value = client.ShippingZip != null ? client.ShippingZip : SqlString.Null;
if (client.ShippingCountry != null && client.ShippingCountry.CountryId != null)
DbCommand.Parameters.Add("@ShippingCountryId", System.Data.SqlDbType.Int).Value = client.ShippingCountry.CountryId;
if (client.ClientId > 0)
returnValue = DbConnection.ExecuteCommand(DbCommand);
else {
// returnValue = Primary Key Id
returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
client.ClientId = returnValue;
}
}
// Save Contacts
using (ClientDAO contactDao = new ClientDAO())
contactDao.SaveContacts(ref dbConnection, ref dbCommand, ref client, userId);
// Save Pricing
this.SaveClientPricing(ref dbConnection, ref dbCommand, ref client, userId);
// Save Complaints
this.SaveClientComplaints(ref dbConnection, ref dbCommand, ref client, userId);
// Save Notes
this.SaveClientNotes(ref dbConnection, ref dbCommand, client, userId);
// Save Documents
this.SaveClientDocuments(ref dbConnection, ref dbCommand, client, userId);
// Accounting Interface
//if (insertNewRecord && Vars.AccountingSettings.InterfaceId > 0) {
// client = new ClientInterface().ClientAdd(client);
// if (!string.IsNullOrWhiteSpace(client.AccountingId)) {
// sql = @"
// UPDATE customers
// SET accountingid = @AccountingId
// WHERE id = @ID
// ";
// DbCommand.Parameters.Clear();
// DbCommand.CommandText = sql;
// DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = client.ClientId;
// DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId ?? string.Empty;
// DbConnection.ExecuteCommand(DbCommand);
// }
//}
// Release Lock
using (SystemDAO systemDao = new SystemDAO()) {
systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Client, client.ClientId.ToString(), userToken);
}
}catch {
if (DbConnection.IsConnected() && DbConnection.IsTransaction())
DbConnection.Rollback();
throw;
}
}
}else {
throw new Exception("Unable to Connect");
}
}
return returnValue;
}catch {
throw;
}
}
public int SaveSampleCharges(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Sample sample, int userId)
{
try
{
int returnValue = 0;
string sql = string.Empty;
foreach (SampleCharge charge in sample.SampleCharges)
{
if (charge.IsDirty)
{
SystemDAO.SaveChangeAudit<SampleCharge>(ref dbConnection, ref dbCommand,
GetSampleCharge(ref dbConnection, ref dbCommand, charge.Pk),
charge,
ModuleNames.Samples,
sample.Pk,
userId);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
if (charge.SampleChargeId <= 0)
{
dbCommand.CommandText = "uspInsertSampleCharge";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
dbCommand.CommandText = "uspUpdateSampleCharge";
dbCommand.Parameters.Add("@SampleChargeId", System.Data.SqlDbType.Int).Value = charge.SampleChargeId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = charge.DeleteDate.HasValue ? charge.DeleteDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = charge.ARLNumber ?? SqlInt32.Null;
dbCommand.Parameters.Add("@ChargeID", System.Data.SqlDbType.Int).Value = charge.ChargeId ?? SqlInt32.Null;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = charge.Description ?? SqlString.Null;
dbCommand.Parameters.Add("@Amount", System.Data.SqlDbType.Decimal).Value = charge.Amount != null ? charge.Amount : SqlDecimal.Null;
dbCommand.Parameters.Add("@AccountingCode", System.Data.SqlDbType.NVarChar, 50).Value = charge.AccountingCode ?? SqlString.Null;
dbCommand.Parameters.Add("@BillGroup", System.Data.SqlDbType.Int).Value = charge.BillGroup ?? SqlInt32.Null;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public int SaveClientComplaints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try {
int returnValue = 0;
string sql = string.Empty;
foreach (ClientComplaint complaint in client.Complaints)
{
if (complaint.IsDirty) {
SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk != null ? complaint.Pk : -1),
complaint,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.Parameters.Clear();
sql = string.Empty;
if (complaint.ClientComplaintId == null || complaint.ClientComplaintId <= 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientComplaint";
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 = "uspUpdateClientComplaint";
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = complaint.DeleteDate ?? SqlDateTime.Null;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Bit).Value = (bool)complaint.StatusYN;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber != "" ? complaint.ARLNumber : SqlString.Null;
dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public void SaveSampleDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Sample sample, int userId)
{
try
{
foreach (var doc in sample.SampleDocuments)
{
if (doc.SampleDocumentId < 0) // insert new
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertSampleDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ARLNumber", sample.ARLNumber);
dbCommand.Parameters.AddWithValue("@Filename", doc.Filename);
dbCommand.Parameters.AddWithValue("@DocumentData", Convert.FromBase64CharArray(doc.DocumentData.ToCharArray(), 0, doc.DocumentData.Length));
dbCommand.Parameters.AddWithValue("@CreatedBy", userId);
dbCommand.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
doc.SampleDocumentId = dbConnection.ExecuteCommand(dbCommand);
}
else if (doc.IsDirty) // update / mark deleted dirty
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateSampleDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@SampleDocumentId", doc.SampleDocumentId);
dbCommand.Parameters.AddWithValue("@ARLNumber", doc.ARLNumber);
dbCommand.Parameters.AddWithValue("@ModifiedBy", userId);
dbCommand.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
dbCommand.Parameters.AddWithValue("@DeleteDate", doc.DeleteDate ?? (object)DBNull.Value);
dbConnection.ExecuteCommand(dbCommand);
}
}
}
catch
{
throw;
}
}
public int SaveClientNotes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Client client, int userId)
{
try {
int returnValue = 0;
string sql = string.Empty;
foreach (ClientNote note in client.Notes) {
if (note.IsDirty) {
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetClientNote(ref dbConnection, ref dbCommand, note.Pk),
note,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
sql = string.Empty;
if (note.ClientNoteId <= 0) {
dbCommand.CommandText = "uspInsertClientNote";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}else {
dbCommand.CommandText = "uspUpdateClientNote";
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = note.ClientNoteId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = note.DeleteDate ?? SqlDateTime.Null;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@Note", System.Data.SqlDbType.NVarChar, 4000).Value = note.Note;
dbCommand.Parameters.Add("@CopyToSampleYN", System.Data.SqlDbType.Bit).Value = (bool)note.CopyToSampleYN;
dbCommand.Parameters.Add("@IncludeOnCOAYN", System.Data.SqlDbType.Bit).Value = (bool)note.IncludeOnCOAYN;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public int SaveSampleNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref SampleNote note, int? arlNumber, Identification identification)
{
try
{
int returnValue = 0;
if (note.IsDirty)
{
dbCommand.Parameters.Clear();
if (note.SampleNoteId <= 0 || note.SampleNoteId.IsNull())
{
dbCommand.CommandText = "uspInsertSampleNote";
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetSampleNote(ref dbConnection, ref dbCommand, note.Pk, identification),
note,
ModuleNames.Samples,
arlNumber,
identification.UserId);
dbCommand.CommandText = "uspUpdateSampleNote";
dbCommand.Parameters.Add("@SampleNoteId", System.Data.SqlDbType.Int).Value = note.SampleNoteId;
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 = identification.UserId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
dbCommand.Parameters.Add("@Note", System.Data.SqlDbType.NVarChar, 4000).Value = note.Note ?? SqlString.Null;
dbCommand.Parameters.Add("@IncludeOnCOAYN", System.Data.SqlDbType.Bit).Value = note.IncludeOnCOAYN;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public int SaveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try
{
int returnValue = 0;
foreach (Contact contact in client.Contacts)
{
if (contact.IsDirty)
{
/*SystemDAO.SaveChangeAudit<Contact>(ref dbConnection, ref dbCommand,
GetContact(ref dbConnection, ref dbCommand, contact.Pk),
contact,
ModuleNames.Clients,
client.Pk,
userId); */
dbCommand.Parameters.Clear();
if (contact.ContactId <= 0)
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertContact";
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateContact";
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contact.ContactId;
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = contact.DeleteDate;
}
dbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 100).Value = contact.FirstName;
dbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.Text, 100).Value = contact.LastName;
dbCommand.Parameters.Add("@PrimaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.PrimaryEmail;
dbCommand.Parameters.Add("@PrimaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.PrimaryEmailSendYN;
dbCommand.Parameters.Add("@SecondaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.SecondaryEmail;
dbCommand.Parameters.Add("@SecondaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.SecondaryEmailSendYN;
dbCommand.Parameters.Add("@PrimaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.PrimaryPhone;
dbCommand.Parameters.Add("@SecondaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.SecondaryPhone;
dbCommand.Parameters.Add("@Fax", System.Data.SqlDbType.Text, 20).Value = contact.Fax;
dbCommand.Parameters.Add("@FaxSendYN", System.Data.SqlDbType.Bit).Value = contact.FaxSendYN;
dbCommand.Parameters.Add("@Comments", System.Data.SqlDbType.Text, 4000).Value = contact.Comments;
if (contact.ContactId > 0)
{
//returnValue = Number of Rows Affected
returnValue = dbConnection.ExecuteCommand(dbCommand);
}
else
{
// returnValue = Primary Key Id
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
contact.ContactId = returnValue;
}
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public int CancelSampleTest(int sampleTestId, SampleNote note, Identification identification)
{
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
try
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspCancelSampleTest";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.Cancelled;
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = identification.UserId;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
this.SaveSampleNote(ref dbConnection, ref dbCommand, ref note, note.ARLNumber, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
return rowsAffected;
}
catch
{
throw;
}
}
}