public ActionResult <IEnumerable <string> > PostEvaluateRank([FromBody] TestAnswerDTO answer)
{
RankPoint rp = SystemDAO.EvaluateRank(context, answer);
if (rp == null)
{
return(new JsonResult(rm.Error("Evaluate Failed")));
}
return(new JsonResult(rm.Success("Evaluate successful", rp)));
}
public ActionResult <IEnumerable <string> > GenTest([FromBody] ConfigurationDTO configuration)
{
string message = SystemDAO.GenerateTest(context, configuration);
if (message != null)
{
return(new JsonResult(rm.Error(message)));
}
return(new JsonResult(rm.Success("Created succeed")));
}
public bool TestDatabaseConnection()
{
try
{
Database db = AppEnvironment.CreateDatabase();
return(SystemDAO.TestConnection(db));
}
catch
{
return(false);
}
}
/// <summary>
/// Called by the <see cref="IScheduler" /> when a
/// <see cref="ITrigger" /> fires that is associated with
/// the <see cref="IJob" />.
/// </summary>
public void Execute(IJobExecutionContext context)
{
if (SysVars.isConsole)
Console.WriteLine("Executing Job ExpireTokens");
Dictionary<Guid, DateTime> tokens;
// Get All locks
using (SystemDAO dao = new SystemDAO()) {
using (dao.DbConnection) {
if (dao.DbConnection.IsConnected()) {
using (dao.DbCommand) {
tokens = dao.GetTokens();
foreach (KeyValuePair<Guid, DateTime> item in tokens) {
if (item.Value.AddMinutes(AppVars.hostOptions.SessionTimeout) < DateTime.Now) {
dao.RemoveToken(item.Key);
}
}
}
}
}
}
}
/// <summary>
/// Called by the <see cref="IScheduler" /> when a
/// <see cref="ITrigger" /> fires that is associated with
/// the <see cref="IJob" />.
/// </summary>
public void Execute(IJobExecutionContext context)
{
if (SysVars.isConsole)
Console.WriteLine("Executing Job ExpireLocks");
//BroadcastMessage message = new BroadcastMessage();
//Identification identity = new Identification();
//identity.UserName = "******";
//message.MessageType = MessageType.SystemMessage;
//message.Text = "Executed Scheduled Task";
//HubClient.Instance.MessageSystem.Invoke("SendAll", message, identity);
// Exipre Locks
using (SystemDAO dao = new SystemDAO()) {
using (dao.DbConnection) {
if (dao.DbConnection.IsConnected()) {
using (dao.DbCommand) {
dao.RemoveExpiredLocks(AppVars.hostOptions.LockTimeout);
}
}
}
}
}
public List<ReportDueFungalResultItem> GetDueFungalResultsData(string token, DateTime reportDate)
{
var results = new List<ReportDueFungalResultItem>();
if (!AppLib.DesignMode)
{
var userId = 0;
using (var systemDao = new SystemDAO())
userId = systemDao.GetToken(new Guid(token));
if (userId > 0)
{
using (var reportDao = new ReportDAO())
{
results = reportDao.GetDueFungalResults(reportDate);
}
}
}
return results;
}
public Oos GetOosData(string token, object recordId)
{
Oos result = new Oos();
User user = new User();
int userId = 0;
this.Token = token;
if (AppLib.DesignMode)
{
return new Oos();
}
else
{
try
{
using (SystemDAO systemDao = new SystemDAO())
{
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0)
{
using (UserDAO userDao = new UserDAO())
{
user = userDao.GetUser(userId);
}
using (SampleDAO ordersDao = new SampleDAO())
{
result = ordersDao.GetOOS(Convert.ToInt32(recordId), new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId });
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
public SmartCollection<User> GetAdministrators()
{
SmartCollection<User> resultList = new SmartCollection<User>();
try
{
AppVars.dbSettings = hostSettings;
if (HostSettings.ValidateModel()) {
using (SystemDAO systemDao = new SystemDAO()) {
using (systemDao.DbConnection = new MsSqlPersistence(systemDao.DbConnectionSettings)) {
if (systemDao.DbConnection.IsConnected()) {
using (systemDao.DbCommand) {
systemDao.DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
systemDao.DbCommand.CommandText = "uspGetAdministrators";
resultList.AddRange(AutoMap.MapReaderToList<User>(systemDao.DbConnection.ExecuteReader(systemDao.DbCommand)));
}
}else {
throw new Exception("Unable to Connect");
}
}
}
}
}catch {
throw;
}
return resultList;
}
public SmartCollection<AppointmentItem> DoLoadAppointments(Identification identification, DateTime startDate, DateTime endDate)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.LoadAppointmentRecords(identification, startDate, endDate);
}
}
public int DoRemoveRole(Guid roleId, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.RemoveRole(roleId);
}
}
public SmartCollection<BusinessClosedDay> DoLoadBusinessClosedDays(Identification identification, DateTime startDate, DateTime endDate)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.LoadBusinessClosedDays(identification, startDate, endDate);
}
}
public SmartCollection<SampleTest> SearchResultSampleTests(string searchString, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
string containerSearch = string.Empty;
int? labNumber = null;
int? sampleTestId = null;
try
{
if (searchString.Contains("-"))
{
containerSearch = searchString;
}
else if (searchString.Contains(","))
{
char[] charSeparators = new char[] { ',' };
string[] sepString = searchString.Split(charSeparators, StringSplitOptions.None);
labNumber = Convert.ToInt32(sepString[0].Trim());
sampleTestId = Convert.ToInt32(sepString[1].Trim());
}
else
{
labNumber = Convert.ToInt32(searchString.Trim());
}
}
catch (Exception)
{
throw new Exception("Search value is an invalid format");
}
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql =
@"
SELECT DISTINCT
sampleTests.id,timepoints.id as timePointId, sampleTests.parentid,sampleTests.status,sampleTests.sampleid,sampleTests.lab_number,
sampleTests.priorityid,sampleTests.typeid,sampleTests.analyteid,sampleTests.testid,
sampleTests.departmentid,sampleTests.analystid,sampleTests.method_name,sampleTests.method_number_name,
sampleTests.low,sampleTests.high,sampleTests.test_minutes, sampleTests.equipment_minutes,
sampleTests.accounting_code,dbo.ReturnNextBusinessDay(sampleTests.begin_date,timepoints.begindate_days) AS begin_date,
sampleTests.due_date,sampleTests.has_requirement_code,
sampleTests.requirement_code,sampleTests.item_price,sampleTests.rush_charge,sampleTests.bill_groupid,
sampleTests.catalogid, sampleTests.methodid, sampleTests.methodnumberid,
sampleTests.is_per_analyte, sampleTests.is_price_table,
priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template,
test.testname,test.active as testactive,analyte.analytename,analyte.controlled,
analyte.active as analyteactive,analyst.firstname, analyst.lastname,
sampleTests.endotoxin_limit,sampleTests.endotoxin_limit_uom, sampleTests.avg_weight, sampleTests.avg_weight_uom,
sampleTests.dose_per_hour, sampleTests.dose_per_hour_uom,sampleTests.route_of_administration,
sampleTests.articles,sampleTests.is_signed, timepoints.begindate_days,
(users.firstname + ' ' + users.lastname) as modifieduser,
(users2.firstname + ' ' + users2.lastname) as createduser,
sampleTests.modified_by, sampleTests.modified_date, sampleTests.created_by, sampleTests.created_date,
timepoints.timepoint_type
FROM orders_samples_tests AS sampleTests
RIGHT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.id = (
SELECT TOP 1
timepoints.id
FROM orders_samples_tests_timepoints AS timepoints
LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid
WHERE
timepointsResults.result_date IS NULL AND timepoints.parentid = sampleTests.id
ORDER BY timepoints.begindate_days
)
LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON oos.id = timepoints.oosid AND oos.is_testing_complete = 'true' -- Continue Testing
LEFT JOIN [User] as users ON sampleTests.modified_by = users.UserID
LEFT JOIN [User] as users2 ON sampleTests.created_by = users2.UserID
LEFT JOIN [User] as analyst ON sampleTests.analystid = analyst.UserID
LEFT JOIN list.departments as dpart ON sampleTests.[departmentid] = dpart.[departmentid]
LEFT JOIN list.tests as test ON sampleTests.[testid] = test.[testid]
LEFT JOIN list.analyte as analyte ON sampleTests.[analyteid] = analyte.[analyteid]
LEFT JOIN list.priorities as priorities ON sampleTests.[priorityid] = priorities.[priorityid]
LEFT JOIN orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id
WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL)
";
// Maybe Limit Results by Analyst and/or Department
DbCommand.Parameters.Clear();
if (!String.IsNullOrWhiteSpace(containerSearch))
{
sql += "AND containers.containerid = @ContainerId ";
DbCommand.Parameters.Add("@ContainerId", System.Data.SqlDbType.VarChar, 100).Value = containerSearch;
}
else if (labNumber != null && sampleTestId != null)
{
sql += "AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId ";
DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
}
else
{
sql += "AND sampleTests.lab_number = @LabNumber ";
DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber;
}
sql += " ORDER BY sampleTests.id";
DbCommand.CommandText = sql;
//DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.Approved;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleTest sampleTest = new SampleTest();
using (CatalogDAO catalogDao = new CatalogDAO())
{
//sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification);
}
using (SystemDAO systemDao = new SystemDAO())
{
//sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem);
}
resultList.Add(sampleTest);
}
returnDT = null;
}
return resultList;
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
}
public List<WorksheetForEndotoxinReportItem> GetWorksheetForEndotoxin(string token)
{
var results = new List<WorksheetForEndotoxinReportItem>();
if (!AppLib.DesignMode)
{
var userId = 0;
using (var systemDao = new SystemDAO())
userId = systemDao.GetToken(new Guid(token));
if (userId > 0)
{
using (var reportDao = new ReportDAO())
{
results = reportDao.GetWorksheetForEndotoxin();
}
}
}
return results;
}
/// <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 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;
}
}
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 SmartCollection<SampleTest> GetWorkOrders(List<string> recordList, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sqlSampleTests = string.Format("AND sampleTest.id IN ({0}) ", String.Join(",", recordList));
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,sampleTest.notes,sampleTest.is_discount_allowed,
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,
orders_samples.status as sample_status,
(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 orders_samples ON sampleTest.sampleid = orders_samples.id
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.delete_date IS NULL ";
sql += sqlSampleTests;
sql += "ORDER BY sampleTest.id DESC";
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);
}
// Do not load predefined notes for canceled tests
if (sampleTest.Status != SampleTestStatus.Cancelled)
{
using (SystemDAO systemDao = new SystemDAO())
{
//sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem);
}
}
resultList.Add(sampleTest);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
/// <summary>
/// Save User record.
/// </summary>
/// <returns></returns>
public int SaveUser(User user, Guid userToken, int userId)
{
int returnValue = -1;
string sql = string.Empty;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
//Check New Record
if (user.Signature.IsDirty) if (user.Signature.SignatureId == Guid.Empty)
{
user.SignatureId = Guid.NewGuid();
}
if (user.IsDirty)
{
SystemDAO.SaveChangeAudit<User>(ref dbConnection, ref dbCommand,
GetUser(ref dbConnection, ref dbCommand, user.Pk ?? 0),
user,
ModuleNames.Users,
user.Pk,
userId
);
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.Parameters.Clear();
if (user.UserId == null)
{
DbCommand.CommandText = "uspInsertUser";
DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
DbCommand.CommandText = "uspUpdateUser";
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = user.UserId;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
DbCommand.Parameters.Add("@RoleId", System.Data.SqlDbType.UniqueIdentifier).Value = user.Role.RoleId;
DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = user.DepartmentId;
DbCommand.Parameters.Add("@AdministratorYN", System.Data.SqlDbType.Bit).Value = user.AdministratorYN;
DbCommand.Parameters.Add("@AdministratorKey", System.Data.SqlDbType.VarChar, 200).Value = user.AdministratorKey ?? string.Empty;
DbCommand.Parameters.Add("@Username", System.Data.SqlDbType.VarChar, 50).Value = user.Username;
if (user.Password.IsNotNull())
DbCommand.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 64).Value = user.Password;
DbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.VarChar, 50).Value = user.FirstName ?? string.Empty;
DbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.VarChar, 50).Value = user.LastName ?? string.Empty;
DbCommand.Parameters.Add("@Email", System.Data.SqlDbType.VarChar, 255).Value = user.Email ?? string.Empty;
DbCommand.Parameters.Add("@Title", System.Data.SqlDbType.VarChar, 255).Value = user.Title ?? string.Empty;
DbCommand.Parameters.Add("@SignatureId", System.Data.SqlDbType.UniqueIdentifier).Value = user.SignatureId;
if (user.UserId != null) returnValue = DbConnection.ExecuteCommand(DbCommand);
else
{
// returnValue = Primary Key Id
returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
user.UserId = returnValue;
}
}
// Save Signature
if (user.Signature.IsDirty)
this.SaveSignature(ref dbConnection, ref dbCommand, user, (int)user.UserId);
// Release Lock
using (SystemDAO systemDao = new SystemDAO())
{
systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.User, user.UserId.ToString(), userToken);
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return returnValue;
}
public List<ReportSterilityFungalBeginItem> GetSterilityFungalBeginData(string token, DateTime workListDate)
{
var results = new List<ReportSterilityFungalBeginItem>();
if (!AppLib.DesignMode)
{
var userId = 0;
using (var systemDao = new SystemDAO())
userId = systemDao.GetToken(new Guid(token));
if (userId > 0)
{
using (var reportDao = new ReportDAO())
{
results = reportDao.GetSterilityFungalBeginData(workListDate);
}
}
}
return results;
}
public ActionResult <IEnumerable <string> > GetAllConfigTestInfoToday(int acccountId)
{
List <ConfigurationDTO> com = SystemDAO.GetAllConfigTestTodayByUsername(context, acccountId);
return(new JsonResult(rm.Success(com)));
}
public Order GetSubmission(string token, object orderId)
{
Order result = new Order();
User user = new User();
int userId = 0;
this.Token = token;
Identification ident = null;
if (AppLib.DesignMode)
{
return result;
}
else
{
try
{
using (SystemDAO systemDao = new SystemDAO())
{
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0)
{
using (UserDAO userDao = new UserDAO())
{
user = userDao.GetUser(userId);
}
ident = new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId };
using (SampleDAO ordersDao = new SampleDAO())
{
//result = ordersDao.GetOrder(Convert.ToInt32(orderId), true, false, ident);
foreach (Sample sample in result.Samples)
{
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 users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
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() + @"' ";
}
foreach (SampleTest test in result.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 users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
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() + @"' ";
//test.ReportNotes = ordersDao.GetOrderNotes(test.ARLNumber.Value, ident, getTestNotesSql);
}
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 users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = 0
AND orders_notes.linked_type = '" + EnumNoteLink.None.ToString() + @"' ";
//result.Notes = ordersDao.GetOrderNotes(result.Id.Value, ident, getOrderNotesSql);
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
public int DoRemoveAppointment(Identification identification, string id)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.RemoveAppointmentRecord(id, identification.Token, identification.UserId);
}
}
public SmartCollection<SampleTest> GetData(string token, object recordIds)
{
SmartCollection<SampleTest> results = new SmartCollection<SampleTest>();
this.RecordList = new List<string>();
User user = new User();
int userId = 0;
this.Token = token;
this.RecordList.AddRange(((object[])recordIds).Select(x => x.ToString()).ToList());
if (AppLib.DesignMode) {
LoadSampleTestMockData(ref results);
}else {
try {
using (SystemDAO systemDao = new SystemDAO()) {
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0) {
using (UserDAO userDao = new UserDAO()) {
user = userDao.GetUser(userId);
}
using (SampleDAO ordersDao = new SampleDAO()) {
results = ordersDao.GetWorkOrders(RecordList, new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId });
}
}
}catch (Exception) {
throw;
}
}
return results;
}
public int DoRemoveNote(int noteId, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO())
{
return dao.RemoveNote(noteId, identification);
}
}
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 Boolean DoReleaseLock(int source, string sourceId, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.ReleaseLock(source, sourceId, identification.Token);
}
}
public OrdersModuleObject DoGetOrderNotifiers(int id, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.GetOrderNotifiers(id, identification);
}
}
private SmartCollection<SampleTest> GetSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, bool loadCanceled, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleTests";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
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);
}
// Do not load predefined notes for canceled tests
if (sampleTest.Status != SampleTestStatus.Cancelled)
{
using (SystemDAO systemDao = new SystemDAO())
{
//sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem);
}
}*/
sampleTest.SampleTestId = row["SampleTestID"] != DBNull.Value ? (int)row["SampleTestID"] : new Int32();
sampleTest.ARLNumber = row["ARLNumber"] != DBNull.Value ? (int)row["ARLNumber"] : new Int32();
sampleTest.Status = row["Status"] != DBNull.Value ? (SampleTestStatus)row["Status"] : new Int32();
sampleTest.Stage = row["Stage"] != DBNull.Value ? (SampleTestStage)row["Stage"] : new Int32();
sampleTest.Type = row["Type"] != DBNull.Value ? (SampleTestType)row["Type"] : new Int32();
sampleTest.OriginalSampleTestId = row["OriginalSampleTestID"] != DBNull.Value ? (int?)row["OriginalSampleTestID"] : new Int32();
sampleTest.CatalogId = row["CatalogID"] != DBNull.Value ? (int)row["CatalogID"] : new Int32();
sampleTest.TestId = row["TestID"] != DBNull.Value ? (int)row["TestID"] : new Int32();
sampleTest.TestName = row["TestName"] != DBNull.Value ? row["TestName"].ToString() : null;
sampleTest.AnalyteId = row["AnalyteID"] != DBNull.Value ? (int)row["AnalyteID"] : new Int32();
sampleTest.AnalyteName = row["AnalyteName"] != DBNull.Value ? row["AnalyteName"].ToString() : null;
sampleTest.ControlledYN = row["ControlledYN"] != DBNull.Value ? (bool)row["ControlledYN"] : false;
sampleTest.AntibioticYN = row["AntibioticYN"] != DBNull.Value ? (bool)row["AntibioticYN"] : false;
if (sampleTest.TestId != null && sampleTest.TestId != 0)
sampleTest.Test = new Test() { TestId = sampleTest.TestId, TestName = row["TestName"].ToString()};
sampleTest.DepartmentId = row["DepartmentID"] != DBNull.Value ? (int)row["DepartmentId"] : new Int32();
sampleTest.DepartmentName = row["DepartmentName"] != DBNull.Value ? row["DepartmentName"].ToString() : null;
if (sampleTest.DepartmentId != null && sampleTest.DepartmentId != 0)
sampleTest.Department = new Department() { DepartmentId = sampleTest.DepartmentId, DepartmentName = sampleTest.DepartmentName };
sampleTest.AnalystId = row["AnalystID"] != DBNull.Value ? (int)row["AnalystID"] : new Int32();
sampleTest.AnalystName = row["AnalystName"] != DBNull.Value ? row["AnalystName"].ToString() : null;
if (sampleTest.AnalystId != null && sampleTest.AnalystId != 0)
sampleTest.Analyst = new User() { UserId = sampleTest.AnalystId, FirstName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(0, sampleTest.AnalystName.IndexOf(' ')) : sampleTest.AnalystName, LastName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(sampleTest.AnalystName.IndexOf(' ') + 1, sampleTest.AnalystName.Length - sampleTest.AnalystName.IndexOf(' ') - 1) : "" };
sampleTest.Containers = row["Containers"] != DBNull.Value ? row["Containers"].ToString() : null;
sampleTest.MethodId = row["MethodID"] != DBNull.Value ? (int)row["MethodID"] : new Int32();
sampleTest.MethodName = row["MethodName"] != DBNull.Value ? row["MethodName"].ToString() : null;
sampleTest.MethodNumberId = row["MethodNumberID"] != DBNull.Value ? (int)row["MethodNumberID"] : new Int32();
sampleTest.MethodNumberName = row["MethodNumberName"] != DBNull.Value ? row["MethodNumberName"].ToString() : null;
sampleTest.OutsourcedYN = row["OutsourcedYN"] != DBNull.Value ? (bool)row["OutsourcedYN"] : false;
sampleTest.BasePrice = row["BasePrice"] != DBNull.Value ? (decimal)row["BasePrice"] : new Decimal();
if (row["LowSpec"] != DBNull.Value)
sampleTest.LowSpec = Convert.ToDecimal(row["LowSpec"]);
if (row["HighSpec"] != DBNull.Value)
sampleTest.HighSpec = Convert.ToDecimal(row["HighSpec"]);
sampleTest.CalculatePercentageExpectedYN = row["CalculatePercentageExpectedYN"] != DBNull.Value ? (bool)row["CalculatePercentageExpectedYN"] : false;
sampleTest.OtherSpec = row["OtherSpec"] != DBNull.Value ? row["OtherSpec"].ToString() : null;
sampleTest.TestMinutes = row["TestMinutes"] != DBNull.Value ? (short)row["TestMinutes"] : new Int16();
sampleTest.EquipmentMinutes = row["EquipmentMinutes"] != DBNull.Value ? (short)row["EquipmentMinutes"] : new Int16();
sampleTest.AccountingCode = row["AccountingCode"] != DBNull.Value ? row["AccountingCode"].ToString() : null;
sampleTest.Instructions = row["Instructions"] != DBNull.Value ? row["Instructions"].ToString() : null;
sampleTest.RequirementYN = row["RequirementYN"] != DBNull.Value ? (bool)row["RequirementYN"] : false;
sampleTest.RequirementDescription = row["RequirementDescription"] != DBNull.Value ? row["RequirementDescription"].ToString() : null;
sampleTest.EndotoxinLimit = row["EndotoxinLimit"] != DBNull.Value ? (decimal)row["EndotoxinLimit"] : new Decimal();
sampleTest.EndotoxinLimitUOM = row["EndotoxinLimitUOM"] != DBNull.Value ? row["EndotoxinLimitUOM"].ToString() : null;
sampleTest.AverageWeight = row["AverageWeight"] != DBNull.Value ? (int)row["AverageWeight"] : new Int32();
sampleTest.AverageWeightUOM = row["AverageWeightUOM"] != DBNull.Value ? row["AverageWeightUOM"].ToString() : null;
sampleTest.DosePerHour = row["DosePerHour"] != DBNull.Value ? (decimal)row["DosePerHour"] : new Decimal();
sampleTest.DosePerHourUOM = row["DosePerHourUOM"] != DBNull.Value ? row["DosePerHourUOM"].ToString() : null;
sampleTest.RouteOfAdministration = row["RouteOfAdministration"] != DBNull.Value ? row["RouteOfAdministration"].ToString() : null;
sampleTest.SignedYN = row["SignedYN"] != DBNull.Value ? (bool)row["SignedYN"] : false;
sampleTest.Articles = row["Articles"] != DBNull.Value ? (int)row["Articles"] : new Int32();
sampleTest.SignedName = row["SignedName"] != DBNull.Value ? row["SignedName"].ToString() : null;
sampleTest.PriorityId = row["PriorityID"] != DBNull.Value ? (int)row["PriorityID"] : new Int32();
if (sampleTest.PriorityId != null && sampleTest.PriorityId != 0)
using (SystemDAO systemDao = new SystemDAO())
{
sampleTest.Priority = systemDao.GetPriority(sampleTest.PriorityId);
}
sampleTest.AdditionalDays = row["AdditionalDays"] != DBNull.Value ? (int)row["AdditionalDays"] : new Int32();
sampleTest.PriceAdjustment = row["PriceAdjustment"] != DBNull.Value ? (decimal)row["PriceAdjustment"] : new Decimal();
sampleTest.DiscountAllowedYN = row["DiscountAllowedYN"] != DBNull.Value ? (bool)row["DiscountAllowedYN"] : false;
sampleTest.BillGroup = row["BillGroup"] != DBNull.Value ? (int)row["BillGroup"] : new Int32();
sampleTest.BeginDate = row["BeginDate"] != DBNull.Value ? (DateTime)row["BeginDate"] : new DateTime();
sampleTest.DueDate = row["DueDate"] != DBNull.Value ? (DateTime)row["DueDate"] : new DateTime();
sampleTest.ApprovedBy = row["ApprovedBy"] != DBNull.Value ? (int)row["ApprovedBy"] : new Int32();
sampleTest.ApprovedDate = row["ApprovedDate"] != DBNull.Value ? (DateTime)row["ApprovedDate"] : new DateTime();
sampleTest.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
sampleTest.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
sampleTest.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
sampleTest.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
sampleTest.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
sampleTest.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32();
sampleTest.Results = GetResultsForSampleTest((int)sampleTest.SampleTestId, identification);
resultList.Add(sampleTest);
}
returnDT = null;
return resultList;
}
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;
}
}
private SmartCollection<TimePoint> GetSampleTestTimePoints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, SampleTest sampleTest, string[] useSql = null)
{
string sql = string.Empty;
SmartCollection<TimePoint> result = new SmartCollection<TimePoint>();
try
{
SmartCollection<BusinessClosedDay> closedDays = new SmartCollection<BusinessClosedDay>();
using (SystemDAO systeDao = new SystemDAO())
{
closedDays.AddRange(systeDao.GetBusinessClosedDays((DateTime)sampleTest.BeginDate, (DateTime)sampleTest.DueDate));
}
sql = useSql != null && useSql[1].Length > 0 ? useSql[1] : @"
SELECT
timepoints.id, timepoints.parentid,
DATEADD(day,timepoints.begindate_days, tests.begin_date) as begin_date,
timepoints.timepoint_type,timepoints.status, timepoints.is_on_report,
timepoints.report_by,timepoints.report_date, timepoints.report_type, timepoints.begindate_days, timepoints.oosid,
timepointDetails.parentid as timepointDetailsParentID,
timepointDetails.id as timepointDetailsId, timepointDetails.is_override_record, timepointDetails.limit,
timepointDetails.result, timepointDetails.expected_result, timepointDetails.result_date,
timepointDetails.is_oos,timepointDetails.created_by, timepointDetails.created_date,
timepointDetails.modified_by, timepointDetails.modified_date,
(users.firstname + ' ' + users.lastname) as modifieduser,
(users1.firstname + ' ' + users1.lastname) as ReportedByUser,
(users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_samples_tests_timepoints AS timepoints
LEFT JOIN orders_samples_tests AS tests ON timepoints.parentid = tests.id
LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointDetails ON timepoints.id = timepointDetails.parentid
LEFT JOIN [User] AS users ON timepointDetails.modified_by = users.UserID
LEFT JOIN [User] as users1 ON timepoints.report_by = users1.UserID
LEFT JOIN [User] as users2 ON timepointDetails.created_by = users2.UserID
WHERE timepoints.parentid = @ParentId
ORDER BY timepoints.begindate_days
;";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId;
dbCommand.CommandText = sql;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
TimePoint timePoint = new TimePoint();
timePoint.Id = Convert.ToInt32(row["Id"]);
timePoint.ParentId = Convert.ToInt32(row["ParentId"]);
timePoint.BeginDate = AppLib.GetNextOpenBuisnessDay(Convert.ToDateTime(row["begin_date"]), closedDays);
timePoint.TimepointType = row["timepoint_type"] != DBNull.Value ? (EnumTimePointType)row["timepoint_type"] : EnumTimePointType.Result;
timePoint.IsOnReport = row["is_on_report"] != DBNull.Value ? Convert.ToBoolean(row["is_on_report"]) : false;
timePoint.ReportBy = row["report_by"] != DBNull.Value ? (int)row["report_by"] : 0;
timePoint.ReportedByUser = row["ReportedByUser"].ToString();
timePoint.ReportDate = row["report_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["report_date"]) : null;
timePoint.BeginDateDays = Convert.ToInt32(row["begindate_days"]);
timePoint.OosId = row["oosid"] != DBNull.Value ? Convert.ToInt32(row["oosid"]) : 0;
timePoint.ResultDetail.Id = row["timepointDetailsId"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsId"]) : null;
timePoint.ResultDetail.ParentId = row["timepointDetailsParentID"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsParentID"]) : null;
timePoint.ResultDetail.Limit = row["limit"].ToString();
timePoint.ResultDetail.Result = row["result"].ToString();
timePoint.ResultDetail.IsOverrideRecord = row["is_override_record"] != DBNull.Value ? Convert.ToBoolean(row["is_override_record"]) : false;
timePoint.ResultDetail.IsOos = row["is_oos"] != DBNull.Value ? Convert.ToBoolean(row["is_oos"]) : false;
timePoint.ResultDetail.ExpectedResult = row["expected_result"] != DBNull.Value ? (decimal?)Convert.ToDecimal(row["expected_result"]) : null;
timePoint.ResultDetail.ResultDate = row["result_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["result_date"]) : (DateTime?)null;
timePoint.ResultDetail.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0;
timePoint.ResultDetail.CreatedUser = row["createduser"].ToString();
timePoint.ResultDetail.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
timePoint.ResultDetail.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0;
timePoint.ResultDetail.ModifiedUser = row["modifieduser"].ToString();
timePoint.ResultDetail.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
result.Add(timePoint);
}
}
catch
{
throw;
}
return result;
}
private SmartCollection<SampleTest> GetWorkLoad(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, CatalogItem filterItem, SmartCollection<Analyte> filterAnalytes, DateTime beginStartDate, DateTime beginEndDate, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
string sqlOrderBy = string.Empty;
string sqlTest = string.Empty;
string sqlAnalyst = string.Empty;
string sqlDepartment = string.Empty;
string sqlBeginDate = string.Empty;
string sqlAnalytes = string.Empty;
if (filterItem.TestId > 0)
{
sqlTest = "AND sampleTest.testId = @TestId ";
}
else
{
sqlTest = "AND sampleTest.testId >= @TestId ";
}
if (filterItem.AnalystId > 0)
{
sqlAnalyst = "AND sampleTest.analystid = @AnalystId ";
}
else
{
sqlAnalyst = "AND sampleTest.analystid >= @AnalystId ";
}
if (filterItem.DepartmentId > 0)
{
sqlDepartment = "AND sampleTest.departmentid = @DepartmentId ";
}
else
{
sqlDepartment = "AND sampleTest.departmentid >= @DepartmentId ";
}
if (filterAnalytes != null || filterAnalytes.Count() > 0)
{
var analyteIds = filterAnalytes.Select(x => x.AnalyteId).ToArray();
if (analyteIds.Count() > 0)
{
sqlAnalytes = string.Format("AND sampleTest.analyteid IN ({0})", String.Join(",", analyteIds));
}
}
beginStartDate = DateEx.GetStartOfDay(beginStartDate);
beginEndDate = DateEx.GetEndOfDay(beginEndDate);
sqlBeginDate = " AND dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) BETWEEN @BeginStartDate AND @BeginEndDate "; //;DATEADD(day,timepoints.begindate_days, sampleTest.begin_date) BETWEEN @BeginStartDate AND @BeginEndDate ";
sqlOrderBy = "ORDER BY begin_date";
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,dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) AS 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, timepoints.begindate_days, timepoints.timepoint_type,
(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]
LEFT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.parentid = sampleTest.id
LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid
WHERE ((sampleTest.status = 2 or sampleTest.status = 3) AND sampleTest.delete_date IS NULL AND timepointsResults.result_date IS NULL)
";
sql += sqlTest;
sql += sqlAnalyst;
sql += sqlDepartment;
sql += sqlBeginDate;
sql += sqlAnalytes;
sql += sqlOrderBy;
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress;
dbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = filterItem.TestId ?? 0;
dbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = filterItem.AnalystId ?? 0;
dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = filterItem.DepartmentId ?? 0;
dbCommand.Parameters.Add("@BeginStartDate", System.Data.SqlDbType.DateTime).Value = beginStartDate;
dbCommand.Parameters.Add("@BeginEndDate", System.Data.SqlDbType.DateTime).Value = beginEndDate;
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);
}
resultList.Add(sampleTest);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public ClientInterfaceObject DoGetClientNotifiers(int id)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.GetClientNotifiers(id);
}
}
public void SaveAdministrators(SmartCollection<User> admins)
{
if (admins == null) return;
if (this.hostSettings != null) {
if (hostSettings.ValidateModel()) {
using (HostDAO dao = new HostDAO()) {
dao.SaveHostSettings(this.hostSettings);
AppVars.dbSettings = hostSettings;
}
}
}
try {
int result = -1;
string sql;
using (HostDAO dao = new HostDAO()) {
this.HostSettings = new SMHostSettings(dao.GetHostSettings());
AppVars.dbSettings = hostSettings;
}
using (SystemDAO systemDao = new SystemDAO()) {
using (systemDao.DbConnection = new MsSqlPersistence(systemDao.DbConnectionSettings)) {
if (systemDao.DbConnection.IsConnected()) {
using (systemDao.DbCommand) {
foreach (User user in admins) {
systemDao.DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
systemDao.DbCommand.Parameters.Clear();
if (user.UserId <= 0 || user.UserId == null) {
systemDao.DbCommand.CommandText = "uspInsertAdministrator";
string hashedPassword = Security.GetSha2Hash(user.Password.Trim(), user.Username.Trim());
systemDao.DbCommand.Parameters.AddWithValue("@Password", hashedPassword);
}else {
systemDao.DbCommand.CommandText = "uspUpdateAdministrator";
systemDao.DbCommand.Parameters.AddWithValue("@UserId", user.UserId);
}
systemDao.DbCommand.Parameters.AddWithValue("@Username", user.Username);
systemDao.DbCommand.Parameters.AddWithValue("@FirstName", user.FirstName);
systemDao.DbCommand.Parameters.AddWithValue("@LastName", user.LastName);
systemDao.DbCommand.Parameters.AddWithValue("@AdministratorYN", true);
string administratorKey = Security.GetSha2Hash(user.Username, hostSettings.BaseKey);
systemDao.DbCommand.Parameters.AddWithValue("@AdministratorKey", administratorKey);
if (user.UserId <= 0 || user.UserId == null) {
result = (int)systemDao.DbConnection.ExecuteCommand(systemDao.DbCommand);
}else result = systemDao.DbConnection.ExecuteCommand(systemDao.DbCommand);
}
}
}else {
throw new Exception("Unable to Connect");
}
}
}
}catch {
throw ;
}
}
public SmartCollection<UnitOfMeasure> DoGetUnitsOfMeasure(Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
using (SystemDAO dao = new SystemDAO()) {
return dao.GetUnitsOfMeasure();
}
}
