public IAsyncResult BeginApproveSampleSubmission(Sample sample, Identification identification, AsyncCallback callback, object state)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
identification.UserId = AppLib.VerifyToken(identification.Token);
if (identification.UserId <= 0) {
throw new FaultException<ServiceFault>(new ServiceFault("Invalid Authentication", "Authorization"), new FaultReason("Unauthorized"));
}
var task = Task<int>.Factory.StartNew(process => DoApproveSampleSubmission(sample, identification), state);
return task.ContinueWith(res => callback(task));
}
private void GetRequestResponse(IAsyncResult result)
{
try {
Result = ((ISampleService)result.AsyncState).EndGetSample(result);
} catch (Exception ex) {
Error = ex;
} finally {
service.Close();
service = null;
}
// Execute Last
Caliburn.Micro.Execute.OnUIThread(() => Completed(this, new ResultCompletionEventArgs()));
}
private Sample GetSampleCoaData(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleId, int? billGroupId, int departmentId, Identification identification)
{
try
{
Sample sample = new Sample();
//User currentUser = new User();
//using (UserDAO userDao = new UserDAO()) {
// currentUser = userDao.GetUser(identification.UserId);
//}
//if (currentUser.IsNull() || currentUser.Id.IsNull())
// return sample;
sample = this.GetSample(sampleId, identification);
return sample;
}
catch
{
throw;
}
}
private SmartCollection<Sample> GetReportableCOAs(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification, string departments)
{
try
{
SmartCollection<Sample> resultList = new SmartCollection<Sample>();
User currentUser = new User();
using (UserDAO userDao = new UserDAO())
{
currentUser = userDao.GetUser(identification.UserId);
}
if (currentUser.IsNull() || currentUser.UserId.IsNull())
return resultList;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetReportableCOAs";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Departments", System.Data.SqlDbType.NVarChar, 40).Value = departments;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Sample sample = new Sample();
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ClientName = row["ClientName"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.SampleTests = this.GetReportableCOATests((int)sample.ARLNumber, false, identification, departments);
resultList.Add(sample);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<Sample> GetPendingSubmissions(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<Sample> resultList = new SmartCollection<Sample>();
SmartCollection<SampleTest> testList = new SmartCollection<SampleTest>();
User currentUser = new User();
using (UserDAO userDao = new UserDAO())
{
currentUser = userDao.GetUser(identification.UserId);
}
if (currentUser.IsNull() || currentUser.UserId.IsNull())
return resultList;
string sqlDepartment = "AND sampleTest.DepartmentId = @DepartmentId";
// Check ApproveAll or Administrator rights
if (AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.SampleTest, ModuleAction.ApproveAll)))
{
sqlDepartment = "AND sampleTest.DepartmentId >= 0";
}
string sql = @"
SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number,
sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid,
sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name,
sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes,
sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code,
sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid,
sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid,
sampleTest.is_per_analyte, sampleTest.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,
sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom,
sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed,
(users.firstname + ' ' + users.lastname) as modifieduser,
(users2.firstname + ' ' + users2.lastname) as createduser,
sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date
FROM orders_samples_tests as sampleTest
LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID
LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID
LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID
LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid]
LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid]
LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid]
LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid]
WHERE (sampleTest.status = @Status " + sqlDepartment + ") ";
sql += "AND sampleTest.delete_date IS NULL";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress;
dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = currentUser.DepartmentId;
dbCommand.CommandText = sql;
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);
}
testList.Add(sampleTest);
}
returnDT = null;
if (testList.Count > 0)
{
var sampleIds = testList.Select(x => x.ARLNumber).ToArray();
sql = string.Format(@"
SELECT sample.id, sample.parentid,sample.sample_type_id,sample.name,sample.lab_number,
sample.formulation_id,sample.quote_number,sample.project_number,sample.lot_number,
sample.storage_id,sample.dosage_id,dosage.value as dosagevalue,dosage.active as dosageactive, sample.ndc_id, sample.articles,sample.container_type,sample.volume_amount,
sample.volume_uom,sample.is_study,sample.is_gmp,sample.compounded_by,sample.compound_date,storage.description as storagedescription, storage.active as storageactive, storage.conditions as storageconditions,
(SELECT COUNT(analytes.controlled) FROM orders_samples_analytes AS analytes WHERE analytes.controlled = 'true' and analytes.parentid = sample.id AND analytes.delete_date IS NULL) AS is_cds,
(users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser,
sample.modified_by, sample.modified_date, sample.created_by, sample.created_date
FROM orders_samples as sample
LEFT JOIN [User] as users ON sample.modified_by = users.UserID
LEFT JOIN [User] as users2 ON sample.created_by = users2.UserID
LEFT JOIN list.dosagetype as dosage ON sample.dosage_id = dosage.dosageid
LEFT JOIN list.storage as storage ON sample.storage_id = storage.storageid
WHERE sample.id IN ({0}) AND sample.delete_date IS NULL
;", String.Join(",", sampleIds));
dbCommand.Parameters.Clear();
dbCommand.CommandText = sql;
returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Sample sample = new Sample();
resultList.Add(sample);
}
foreach (Sample sampleItem in resultList)
{
sampleItem.SampleTests.AddRange(testList.Where(x => x.ARLNumber == sampleItem.ARLNumber).Select(s => s));
}
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public ApproveSampleSubmissionRequest(Sample sample)
{
this.sample = sample;
}
public int DoCreateCoaReport(Sample sample, Identification identification)
{
ReportRecord reportRecord = null;
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SampleDAO dao = new SampleDAO()) {
reportRecord = dao.CreateCoaReport(sample, identification);
}
if (reportRecord.Id.IsNotNull() && reportRecord.Id > 0) {
using (ClientDAO customerDao = new ClientDAO()) {
reportRecord.ParentId = customerDao.GetClientId(reportRecord.ParentId.Value);
}
using (ReportDAO reportDao = new ReportDAO()) {
ReportNotification reportNotification = new ReportNotification();
reportNotification.ReportId = reportRecord.Id.Value;
reportNotification.CustomerId = reportRecord.ParentId.Value;
reportNotification.DepartmentId = reportRecord.DepartmentId.Value;
reportNotification.SubjectLine = reportRecord.SubjectLine;
reportDao.SaveNotificationReports(reportDao.GetNotificationRecords(reportNotification));
}
}
return reportRecord.Id.Value;
}
public IAsyncResult BeginCreateCoaReport(Sample sample, Identification identification, AsyncCallback callback, object state)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
if (AppLib.VerifyToken(identification.Token) <= 0)
throw new FaultException<ServiceFault>(new ServiceFault("Invalid Authentication", "Authorization"), new FaultReason("Unauthorized"));
if (!AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.Sample, ModuleAction.Create, ModuleAction.Report)))
throw new FaultException<ServiceFault>(new ServiceFault("User account is not Authorized.", "Authorization"), new FaultReason("Restricted"));
var task = Task<int>.Factory.StartNew(process => DoCreateCoaReport(sample, identification), state);
return task.ContinueWith(res => callback(task));
}
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 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 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 ReportRecord CreateCoaReport(Sample sample, Identification identification)
{
ReportRecord rc = new ReportRecord();
DateTime reportDate = DateTime.Now;
// Update Timepoint Data
List<SampleTest> testsNotOnReport = new List<SampleTest>();
CoaReportModel reportData = new CoaReportModel();
try
{
/*
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
// GET User
using (UserDAO userDao = new UserDAO())
reportData.User = userDao.GetUser(ref dbConnection, ref dbCommand, identification.UserId);
foreach (SampleTest test in sample.SampleTests)
{
List<TimePoint> timpointsNotOnReport = new List<TimePoint>();
//var isOnReport = Convert.ToBoolean(test.TimePoints.Count(x => x.IsOnReport == true));
//if (!isOnReport)
//testsNotOnReport.Add(test);
/*foreach (TimePoint timePoint in test.TimePoints)
{
if (!timePoint.IsOnReport)
timpointsNotOnReport.Add(timePoint);
if (timePoint.ReportDate.IsNull())
timePoint.ReportDate = reportDate;
if ((timePoint.ReportBy == 0 || timePoint.ReportBy.IsNull()) && timePoint.IsOnReport)
{
timePoint.ReportBy = identification.UserId;
timePoint.ReportedByUser = reportData.User.FullName;
}
UpdateOrderSampleTestTimePointReportData(ref dbConnection, ref dbCommand, (int)timePoint.Id, timePoint.IsOnReport, timePoint.ReportDate, timePoint.ReportBy);
if (test.CatalogItem.AnalyteId.IsNotNull() && test.CatalogItem.AnalyteId > 0)
{
timePoint.ResultDetail.ExpectedAmount = sample.SampleAnalytes.FirstOrDefault(a => a.AnalyteId == test.AnalyteId).Amount;
timePoint.ResultDetail.ExpectedUnits = sample.SampleAnalytes.FirstOrDefault(a => a.AnalyteId == test.AnalyteId).AmountUOM;
}
}
foreach (TimePoint removeTimePoint in timpointsNotOnReport)
{
//test.TimePoints.Remove(removeTimePoint);
}
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
foreach (SampleTest onTest in testsNotOnReport)
sample.SampleTests.Remove(onTest);
// Create Report
try
{
string customerSql = @"
select customers.id, customers.customer_name, customers.accountingid, customers.termid,
terms.value as terms, customers.credit_check , customers.web_client, customers.sales_rep_id,
(users.firstname + ' ' + users.lastname) as modifieduser,
(users2.firstname + ' ' + users2.lastname) as createduser,
customers.modified_by, customers.modified_date, customers.created_by,
customers.created_date, salesrep.firstname, salesrep.lastname
from customers
LEFT JOIN [User] as users ON customers.modified_by = users.UserID
LEFT JOIN [User] as users2 ON customers.created_by = users2.UserID
LEFT JOIN list.terms as terms ON customers.termid = terms.termid
LEFT JOIN [User] as salesrep ON customers.sales_rep_id = salesrep.UserID
LEFT JOIN orders ON orders.parentid = customers.id
where orders.id = ";
// Get Customer for Sample
using (ClientDAO dao = new ClientDAO())
reportData.Customer = dao.GetClient(0);
string getOrderNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN [User] as users ON orders_notes.modified_by = users.UserID
LEFT JOIN [User] as users2 ON orders_notes.created_by = users2.UserID
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = 0
AND orders_notes.linked_type = '" + EnumNoteLink.None.ToString() + @"'
AND orders_notes.is_show_on_report = 1 ";
//reportData.ReportNotes = GetSampleNotes(sample.ARLNumber.Value, identification, getOrderNotesSql);
string getSampleNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN [User] as users ON orders_notes.modified_by = users.UserID
LEFT JOIN [User] as users2 ON orders_notes.created_by = users2.UserID
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = " + sample.ARLNumber.Value +
@" AND orders_notes.linked_type = '" + EnumNoteLink.Sample.ToString() + @"'
AND orders_notes.is_show_on_report = 1 ";
foreach (SampleTest test in sample.SampleTests)
{
string getTestNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN [User] as users ON orders_notes.modified_by = users.UserID
LEFT JOIN [User] as users2 ON orders_notes.created_by = users2.UserID
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = " + test.SampleTestId.Value +
@" AND orders_notes.linked_type = '" + EnumNoteLink.Test.ToString() + @"'
AND orders_notes.is_show_on_report = 1 ";
//test.ReportNotes = GetSampleNotes(test.ARLNumber.Value, identification, getTestNotesSql);
}
*/
reportData.Sample = sample;
if (!string.IsNullOrWhiteSpace(reportData.User.Signature.Signature))
reportData.Signature = Image.FromStream(new MemoryStream(Convert.FromBase64String(Security.AesDecrypt(reportData.User.Signature.Signature, AppVars.dbSettings.BaseKey))));
Reports.Coa report = new Reports.Coa();
//report.ReportData = reportData;
ReportProcessor reportProcessor = new ReportProcessor();
InstanceReportSource instanceReportSource = new InstanceReportSource();
instanceReportSource.ReportDocument = report;
RenderingResult result = reportProcessor.RenderReport("PDF", instanceReportSource, null);
Byte[] pdfReport = result.DocumentBytes;
rc.ReferenceId = sample.ARLNumber;
rc.DepartmentId = sample.SampleTests[0].DepartmentId;
rc.CreatedBy = identification.UserId;
rc.CreatedDate = DateTime.Now;
rc.ReportData = pdfReport;
rc.ReportType = "PDF";
rc.ReportName = "Certificate of Analysis";
rc.SubjectLine = "Result ARL #" + sample.ARLNumber.ToString() + " Lot: " + sample.LotNumber.ToString() + " Drug: " + sample.Description.ToString();
rc.Id = SaveReport(rc);
}
catch
{
throw;
}
return rc;
}
public Sample GetSample(int? arlNumber, Identification identification)
{
try
{
Sample sample = new Sample();
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetSample";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DataTable returnDT = DbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count > 0)
{
DataRow row = returnDT.Rows[0];
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?();
sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32();
sample.ClientName = row["ClientName"].ToString();
if (sample.ClientId > -1)
{
//sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName };
using (ClientDAO clientDao = new ClientDAO())
{
sample.Client = clientDao.GetClient((int)sample.ClientId);
}
}
sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress;
sample.PONumber = row["PONumber"].ToString();
sample.FormulationId = row["FormulationID"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.ProjectNumber = row["ProjectNumber"].ToString();
sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32();
sample.StorageLocationName = row["StorageLocationName"].ToString();
sample.StorageLocationConditions = row["StorageLocationConditions"].ToString();
sample.StorageLocationCode = row["StorageLocationCode"].ToString();
if (sample.StorageLocationId > -1)
sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() };
sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32();
sample.RequestedStorageName = row["RequestedStorageName"].ToString();
if (sample.RequestedStorageId > -1)
sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" };
sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32();
sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null;
if (sample.DosageId > -1)
sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() };
sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32();
sample.ContainerDescription = row["ContainerDescription"].ToString();
if (sample.ContainerDescription != null)
sample.Container = new Container() { Description = sample.ContainerDescription };
sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal();
sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32();
sample.VolumeUOM = row["VolumeUOM"].ToString();
if (sample.VolumeUOMId != null)
sample.VolumeUnitOfMeasure = new UnitOfMeasure() { UomId = sample.VolumeUOMId, Uom = sample.VolumeUOM };
sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false;
sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
sample.CompoundedBy = row["CompoundedBy"].ToString();
sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?();
sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
sample.CreatedUser = row["CreatedUser"].ToString();
sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
sample.ModifiedUser = row["ModifiedUser"].ToString();
sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber);
sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber);
sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber);
sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification);
sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification);
}
returnDT = null;
return sample;
}
catch
{
throw;
}
}
public int ApproveSampleSubmission(Sample sample, Identification identification)
{
int rowsAffected = 0;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
try
{
foreach (SampleTest sampleTestItem in sample.SampleTests)
{
rowsAffected += ApproveSampleTestSubmission(sampleTestItem.SampleTestId.Value, identification);
}
return rowsAffected;
}
catch
{
throw;
}
}
}
private SmartCollection<Sample> GetSamples(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, string searchString, Identification identification)
{
try
{
SmartCollection<Sample> resultList = new SmartCollection<Sample>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSamples";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SearchString", System.Data.SqlDbType.NVarChar, 100).Value = searchString;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Sample sample = new Sample();
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?();
sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32();
sample.ClientName = row["ClientName"].ToString();
if (sample.ClientId > -1)
{
//sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName };
using (ClientDAO clientDao = new ClientDAO())
{
sample.Client = clientDao.GetClient((int)sample.ClientId);
}
}
sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress;
sample.PONumber = row["PONumber"].ToString();
sample.FormulationId = row["FormulationID"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.ProjectNumber = row["ProjectNumber"].ToString();
sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32();
sample.StorageLocationName = row["StorageLocationName"].ToString();
sample.StorageLocationConditions = row["StorageLocationConditions"].ToString();
sample.StorageLocationCode = row["StorageLocationCode"].ToString();
if (sample.StorageLocationId > -1)
sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() };
sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32();
sample.RequestedStorageName = row["RequestedStorageName"].ToString();
if (sample.RequestedStorageId > -1)
sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" };
sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32();
sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null;
if (sample.DosageId > -1)
sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() };
sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32();
sample.ContainerDescription = row["ContainerDescription"].ToString();
sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal();
sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32();
sample.VolumeUOM = row["VolumeUOM"].ToString();
sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false;
sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
sample.CompoundedBy = row["CompoundedBy"].ToString();
sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?();
sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
sample.CreatedUser = row["CreatedUser"].ToString();
sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
sample.ModifiedUser = row["ModifiedUser"].ToString();
sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber);
sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber);
sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber);
sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification);
sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification);
resultList.Add(sample);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public 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 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 DoSaveSample(Sample sample, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
//Validate Order Object
if (!sample.ValidateModel())
throw new FaultException<ServiceFault>(new ServiceFault(sample.CurrentErrors), new FaultReason(SysVars.InvalidFormat));
// Validate All SampleTests
Dictionary<string, List<string>> sampleTestErrors = new Dictionary<string, List<string>>();
foreach (SampleTest sampleTest in sample.SampleTests)
{
if (!sampleTest.ValidateModel())
sampleTestErrors = sampleTest.CurrentErrors;
}
if (sampleTestErrors.Count > 0)
throw new FaultException<ServiceFault>(new ServiceFault(sampleTestErrors), new FaultReason(SysVars.InvalidFormat));
// Validate All SampleCharges
Dictionary<string, List<string>> chargesErrors = new Dictionary<string, List<string>>();
foreach (SampleCharge charge in sample.SampleCharges)
{
if (!charge.ValidateModel())
chargesErrors = charge.CurrentErrors;
}
if (chargesErrors.Count > 0)
throw new FaultException<ServiceFault>(new ServiceFault(chargesErrors), new FaultReason(SysVars.InvalidFormat));
// Validate All SampleNotes
Dictionary<string, List<string>> notesErrors = new Dictionary<string, List<string>>();
foreach (SampleNote note in sample.SampleNotes)
{
if (!note.ValidateModel())
notesErrors = note.CurrentErrors;
}
if (notesErrors.Count > 0)
throw new FaultException<ServiceFault>(new ServiceFault(notesErrors), new FaultReason(SysVars.InvalidFormat));
// Save Order
using (SampleDAO dao = new SampleDAO())
{
return dao.SaveSample(sample, identification);
}
}
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 DoApproveSampleSubmission(Sample sample, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SampleDAO dao = new SampleDAO()) {
return dao.ApproveSampleSubmission(sample, identification);
}
}
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 CreateCoaReportRequest(Sample sample)
{
this.sample = sample;
}
public SaveSampleRequest(Sample sample)
{
this.sample = sample;
}