public SmartCollection<SampleTest> GetSampleTestsResultsList(SmartCollection<SampleTest> list, Identification identification)
{
try
{
string sql = string.Empty;
SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
foreach (SampleTest sampleTestItem in list)
{
DbCommand.Parameters.Clear();
sql =
@"
SELECT
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 orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id
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]
WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL)
AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId
ORDER BY sampleTests.id
";
// Maybe Limit Results by Analyst and/or Department
DbCommand.CommandText = sql;
DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = sampleTestItem.ARLNumber;
DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestItem.SampleTestId;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleTest sampleTest = new SampleTest();
//sampleTest.Sample = this.GetSample(ref dbConnection, ref dbCommand, sampleTest.SampleId);
//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 SmartCollection<ClientPricing> GetClientPricings(int ClientId)
{
try {
SmartCollection<ClientPricing> resultList = new SmartCollection<ClientPricing>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientPricings";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows) {
ClientPricing price = new ClientPricing();
price.ClientPricingId = Convert.ToInt32(row["ClientPricingID"]);
price.ClientId = Convert.ToInt32(row["ClientID"]);
if (row["MethodID"] != DBNull.Value)
price.MethodId = Convert.ToInt32(row["MethodID"]);
if (row["MethodNumberID"] != DBNull.Value)
price.MethodNumberId = Convert.ToInt32(row["MethodNumberID"]);
if (row["AnalyteID"] != DBNull.Value)
price.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
price.Description = row["Description"].ToString();
price.Discount = row["Discount"] != DBNull.Value ? Convert.ToDouble(row["Discount"]) : 0;
price.FlatRate = row["FlatRate"] != DBNull.Value ? Convert.ToDouble(row["FlatRate"]) : 0;
price.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
price.CreatedUser = row["CreatedUser"].ToString();
price.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
price.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
price.ModifiedUser = row["ModifiedUser"].ToString();
price.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (price.MethodId.HasValue)
price.Method = new Method { MethodId = price.MethodId, MethodName = row["MethodName"].ToString() };
else
price.Method = null;
if (price.MethodNumberId.HasValue)
price.MethodNumber = new MethodNumber { MethodNumberId = price.MethodNumberId, MethodNumberName = row["MethodNumberName"].ToString() };
else
price.MethodNumber = null;
if (price.AnalyteId.HasValue)
price.AnalyteItem = new Analyte { AnalyteId = price.AnalyteId, AnalyteName = row["AnalyteName"].ToString() };
else
price.AnalyteItem = null;
resultList.Add(price);
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public Contact GetContact(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? contactId)
{
try
{
Contact contact = new Contact();
if (dbConnection.IsConnected())
{
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetContact";
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
DataTable contactDT = dbConnection.ExecuteQuery(dbCommand);
if (contactDT.Rows.Count == 1)
{
DataRow row = contactDT.Rows[0];
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
contactDT = null;
}
else
{
contactDT = null;
return null;
}
}
else
{
throw new Exception("Unable to Connect");
}
return contact;
}
catch
{
throw;
}
}
public ClientComplaint GetClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? clientComplaintId)
{
try {
ClientComplaint complaint = new ClientComplaint();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientComplaint";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = clientComplaintId;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
complaint.CorrectiveActionDate = row["CorrectiveActionDate"] != DBNull.Value ? (DateTime)row["CorrectiveActionDate"] : (DateTime)SqlDateTime.Null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
returnDT = null;
}else {
returnDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return complaint;
}catch {
throw;
}
}
public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
{
try {
ClientNote note = new ClientNote();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNote";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;
DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
if (notesDT.Rows.Count == 1) {
DataRow row = notesDT.Rows[0];
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
notesDT = null;
}else {
notesDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return note;
}catch {
throw;
}
}
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;
}
}
private SmartCollection<SampleTest> GetPendingFinalizations(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<SampleTest> resultList = 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.FinalizeAll)))
{
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();
resultList.Add(sampleTest);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<Invoice> GetPendingInvoices(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<Invoice> resultList = new SmartCollection<Invoice>();
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 = "uspGetInvoicableSamples";
dbCommand.Parameters.Clear();
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Invoice invoice = new Invoice();
invoice.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
invoice.ClientName = row["ClientName"].ToString();
invoice.AccountingId = row["AccountingId"].ToString();
invoice.PONumber = row["PONumber"].ToString();
invoice.TermName = row["TermName"].ToString();
invoice.IncludeYN = (bool)false;
invoice.InvoiceItems = this.GetInvoicableSampleItems((int)invoice.ARLNumber, showAll, identification);
resultList.Add(invoice);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<InvoiceItem> GetInvoicableSampleItems(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, Identification identification)
{
try
{
SmartCollection<InvoiceItem> resultList = new SmartCollection<InvoiceItem>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetInvoicableSampleItems";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
InvoiceItem invoiceItem = new InvoiceItem();
/*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);
}
}*/
invoiceItem.AccountingCode = row["AccountingCode"].ToString();
invoiceItem.Class = row["DepartmentName"].ToString();
invoiceItem.Other1 = row["TestName"].ToString();
invoiceItem.Other2 = row["AnalystName"].ToString();
invoiceItem.Description = row["Description"].ToString();
if (row["BasePrice"] != DBNull.Value)
invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]);
if (row["SampleTestID"] != DBNull.Value)
invoiceItem.SampleTestId = (int)row["SampleTestID"];
if (row["Ordering"] != DBNull.Value)
invoiceItem.Ordering = (int)row["Ordering"];
if (row["QBInvoiceNumber"] != DBNull.Value)
invoiceItem.InvoiceId = (int)row["QBInvoiceNumber"];
resultList.Add(invoiceItem);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<Oos> GetOOSs(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<Oos> resultList = new SmartCollection<Oos>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetOOSs";
DbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
Oos result = new Oos();
result.OosId = Convert.ToInt32(row["OOSID"]);
if (row["ARLNumber"] != DBNull.Value)
result.ARLNumber = (int)row["ARLNumber"];
if (row["SampleTestId"] != DBNull.Value)
result.SampleTestId = (int)row["SampleTestId"];
result.ClientName = row["ClientName"].ToString();
result.Status = row["status"] != DBNull.Value ? (SampleTestStatus)row["status"] : SampleTestStatus.UnderInvestigation;
result.TestName = row["TestName"].ToString();
result.AnalyteName = row["AnalyteName"].ToString();
result.PriorityName = row["PriorityName"].ToString();
if (row["DueDate"] != DBNull.Value)
result.DueDate = (DateTime)row["DueDate"];
result.DepartmentName = row["DepartmentName"].ToString();
if (row["TimepointStudyYN"] != DBNull.Value)
result.TimepointStudyYN = (bool)row["TimepointStudyYN"];
if (row["NextTimepoint"] != DBNull.Value)
result.NextTimepoint = (DateTime)row["NextTimepoint"];
result.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
result.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32();
// result.Results =
resultList.Add(result);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<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 SmartCollection<Order> SearchOrders(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification)
{
try
{
SmartCollection<Order> resultList = new SmartCollection<Order>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = @"
select distinct orders.id,orders.parentid,orders.ponumber, orders.received_date,orders.start_date,
orders.due_date,orders.report_date, orders.status,
(users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser,
orders.modified_by, orders.modified_date, orders.created_by, orders.created_date,
(select sum(amount) from orders_charges where parentid = orders.id and delete_date IS NULL) as ChargesTotal,
(select sum(orders_samples_tests.item_price) from orders_samples_tests where orders_samples_tests.parentid = orders.id and orders_samples_tests.delete_date IS NULL and orders_samples_tests.status < 7 ) as TestsTotal
from orders
LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
LEFT JOIN orders_samples_containers as containers ON containers.parentid = samples.id
LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
LEFT JOIN list.departments as dept ON dept.departmentid = tests.departmentid
LEFT JOIN customers ON customers.id = orders.parentid
LEFT JOIN [User] AS users ON orders.modified_by = users.UserID
LEFT JOIN [User] as users2 ON orders.created_by = users2.UserID " +
SysLib.BuildSearchAllWhereClause(searchString, searchItems);
if (identification.ClientYN)
{
sql += " and customers.id = @companyId ";
DbCommand.Parameters.Add("@companyId", System.Data.SqlDbType.Int).Value = identification.ClientId;
}
sql += " AND orders.delete_date IS NULL ";
sql += "ORDER BY orders.modified_date DESC ";
DbCommand.CommandText = sql;
DataTable customerDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in customerDT.Rows)
{
Order order = new Order();
order.Id = Convert.ToInt32(row["Id"]);
order.ParentId = row["parentid"] != DBNull.Value ? (int)row["parentid"] : 0;
order.Status = row["status"] != DBNull.Value ? (EnumOrderStatus)row["status"] : EnumOrderStatus.Open;
order.PoNumber = row["ponumber"].ToString();
order.OrderTotal = Convert.ToDecimal(row["ChargesTotal"] != DBNull.Value ? (decimal)row["ChargesTotal"] : 0) + Convert.ToDecimal(row["TestsTotal"] != DBNull.Value ? (decimal)row["TestsTotal"] : 0);
if (row["received_date"] != DBNull.Value)
order.ReceivedDate = (DateTime)row["received_date"];
if (row["start_date"] != DBNull.Value)
order.StartDate = (DateTime)row["start_date"];
if (row["due_date"] != DBNull.Value)
order.DueDate = (DateTime)row["due_date"];
if (row["report_date"] != DBNull.Value)
order.ReportDate = (DateTime)row["report_date"];
order.CreatedUser = row["createduser"].ToString();
order.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0;
order.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
order.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0;
order.ModifiedUser = row["modifieduser"].ToString();
order.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
order.Samples = this.GetSamples(ref dbConnection, ref dbCommand, "", identification);
order.SampleTests = this.GetSampleTests(ref dbConnection, ref dbCommand, order.Id, true, identification);
using (ClientDAO dao = new ClientDAO())
{
order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
}
resultList.Add(order);
}
customerDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
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;
}
}
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;
}
}
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;
}
}
private TimePoint GetSampleTestTimePoint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int id, DateTime beginDate)
{
TimePoint result = new TimePoint();
try
{
string sql = @"
SELECT
timepoints.id, timepoints.parentid,
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,
(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 users2 ON timepointDetails.created_by = users2.UserID
WHERE timepoints.id = @Id
ORDER BY timepoints.begindate_days
;";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
dbCommand.CommandText = sql;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1)
{
DataRow row = returnDT.Rows[0];
result.Id = Convert.ToInt32(row["Id"]);
result.ParentId = Convert.ToInt32(row["ParentId"]);
result.BeginDate = beginDate;
result.TimepointType = row["timepoint_type"] != DBNull.Value ? (EnumTimePointType)row["timepoint_type"] : EnumTimePointType.Result;
result.IsOnReport = row["is_on_report"] != DBNull.Value ? Convert.ToBoolean(row["is_on_report"]) : false;
result.ReportBy = row["report_by"] != DBNull.Value ? (int)row["report_by"] : 0;
result.ReportDate = row["report_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["report_date"]) : (DateTime?)null;
result.BeginDateDays = Convert.ToInt32(row["begindate_days"]);
result.OosId = row["oosid"] != DBNull.Value ? Convert.ToInt32(row["oosid"]) : 0;
result.ResultDetail.Id = row["timepointDetailsId"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsId"]) : null;
result.ResultDetail.ParentId = row["timepointDetailsParentID"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsParentID"]) : null;
result.ResultDetail.Limit = row["limit"].ToString();
result.ResultDetail.Result = row["result"].ToString();
result.ResultDetail.IsOos = row["is_oos"] != DBNull.Value ? Convert.ToBoolean(row["is_oos"]) : false;
result.ResultDetail.IsOverrideRecord = row["is_override_record"] != DBNull.Value ? Convert.ToBoolean(row["is_override_record"]) : false;
result.ResultDetail.ExpectedResult = row["expected_result"] != DBNull.Value ? (decimal?)Convert.ToDecimal(row["expected_result"]) : null;
result.ResultDetail.ResultDate = row["result_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["result_date"]) : (DateTime?)null;
result.ResultDetail.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0;
result.ResultDetail.CreatedUser = row["createduser"].ToString();
result.ResultDetail.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
result.ResultDetail.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0;
result.ResultDetail.ModifiedUser = row["modifieduser"].ToString();
result.ResultDetail.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
}
return result;
}
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<Submission> GetWebSubmissions(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
SmartCollection<Submission> resultList = new SmartCollection<Submission>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
dbCommand.CommandText = "uspGetWebSubmissions";
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Submission submission = new Submission();
submission.SubmissionID = Convert.ToInt32(row["RequestID"]);
submission.ReceivedDate = Convert.ToDateTime(row["SubmissionDate"]);
submission.ConfirmationString = row["ConfirmationString"] != null ? row["ConfirmationString"].ToString() : null;
submission.Description = row["DrugDescription"] != null ? row["DrugDescription"].ToString() : null;
submission.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : 0;
submission.ClientName = row["Customer"].ToString();
submission.Client = row["Customer"].ToString() + " (" + submission.ClientId.ToString() + ")";
submission.RequestedStorageName = row["StorageName"] != null ? row["StorageName"].ToString() : null;
submission.PONumber = row["PONumber"] != null ? row["PONumber"].ToString() : null;
submission.LotNumber = row["LotNumber"] != null ? row["LotNumber"].ToString() : null;
submission.CreatedUser = row["FirstName"].ToString() + " " + row["LastName"].ToString();
submission.CreatedDate = DateTime.Now;
submission.ModifiedUser = row["FirstName"].ToString() + " " + row["LastName"].ToString();
submission.ModifiedDate = DateTime.Now;
submission.Comments = row["Comments"] != null ? row["Comments"].ToString() : null;
submission.ImportedYN = row["ImportedYN"] != DBNull.Value ? Convert.ToBoolean(row["ImportedYN"]) : false;
submission.ARLNumber = row["ARLNumber"] != null ? row["ARLNumber"].ToString() : null;
submission.Tests = row["Tests"] != null ? row["Tests"].ToString() : null;
resultList.Add(submission);
}
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<Result> GetResultsForSampleTest(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleTestId, Identification identification)
{
try
{
SmartCollection<Result> resultList = new SmartCollection<Result>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetResultsForSampleTest";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Result result = new Result();
if (row["ResultID"] != DBNull.Value)
result.ResultId = (int)row["ResultID"];
result.SampleTestId = row["SampleTestID"] != DBNull.Value ? (int)row["SampleTestID"] : new Int32();
result.ARLNumber = row["ARLNumber"] != DBNull.Value ? (int)row["ARLNumber"] : new Int32();
result.ResultName = row["ResultName"] != DBNull.Value ? row["ResultName"].ToString() : null;
result.ResultValue = row["ResultValue"] != DBNull.Value ? row["ResultValue"].ToString() : null;
result.TestId = row["TestID"] != DBNull.Value ? (int)row["TestID"] : new Int32();
result.TestName = row["TestName"] != DBNull.Value ? row["TestName"].ToString() : null;
result.AnalyteId = row["AnalyteID"] != DBNull.Value ? (int)row["AnalyteID"] : new Int32();
result.AnalyteName = row["AnalyteName"] != DBNull.Value ? row["AnalyteName"].ToString() : null;
if (result.AnalyteId != null && result.AnalyteId != 0)
result.Analyte = new Analyte() { AnalyteId = result.AnalyteId, AnalyteName = result.AnalyteName };
if (result.TestId != null && result.TestId != 0)
result.Test = new Test() { TestId = result.TestId, TestName = row["TestName"].ToString() };
result.DepartmentId = row["DepartmentID"] != DBNull.Value ? (int)row["DepartmentId"] : new Int32();
result.DepartmentName = row["DepartmentName"] != DBNull.Value ? row["DepartmentName"].ToString() : null;
if (result.DepartmentId != null && result.DepartmentId != 0)
result.Department = new Department() { DepartmentId = result.DepartmentId, DepartmentName = result.DepartmentName };
result.AnalystName = row["AnalystName"] != DBNull.Value ? row["AnalystName"].ToString() : null;
result.MethodId = row["MethodID"] != DBNull.Value ? (int)row["MethodID"] : new Int32();
result.MethodName = row["MethodName"] != DBNull.Value ? row["MethodName"].ToString() : null;
result.MethodNumberId = row["MethodNumberID"] != DBNull.Value ? (int)row["MethodNumberID"] : new Int32();
result.MethodNumberName = row["MethodNumberName"] != DBNull.Value ? row["MethodNumberName"].ToString() : null;
if (result.MethodNumberName != null)
result.MethodNumber = new MethodNumber() { MethodNumberId = result.MethodNumberId, MethodNumberName = result.MethodNumberName, Active = true };
if (row["Amount"] != DBNull.Value)
result.Amount = (decimal)row["Amount"];
result.AmountUOM = row["AmountUOM"] != DBNull.Value ? row["AmountUOM"].ToString() : null;
result.Concentration = row["Concentration"] != DBNull.Value ? row["Concentration"].ToString() : null;
result.ConcentrationUOM = row["ConcentrationUOM"] != DBNull.Value ? row["ConcentrationUOM"].ToString() : null;
if (row["LowSpec"] != DBNull.Value)
result.LowSpec = Convert.ToDecimal(row["LowSpec"]);
if (row["HighSpec"] != DBNull.Value)
result.HighSpec = Convert.ToDecimal(row["HighSpec"]);
result.CalculatePercentageExpectedYN = row["CalculatePercentageExpectedYN"] != DBNull.Value ? (bool)row["CalculatePercentageExpectedYN"] : false;
result.OtherSpec = row["OtherSpec"] != DBNull.Value ? row["OtherSpec"].ToString() : null;
result.OOS = row["OOSYN"] != DBNull.Value ? (bool)row["OOSYN"] : false;
if (row["BeginDate"] != DBNull.Value)
result.BeginDate = (DateTime)row["BeginDate"];
if (row["BeginDate"] != DBNull.Value)
result.ScheduledReadDate = (DateTime)row["BeginDate"];
if (row["TestedDate"] != DBNull.Value)
result.TestedDate = (DateTime)row["TestedDate"];
result.SecondAnalyst = row["SecondAnalyst"] != DBNull.Value ? row["SecondAnalyst"].ToString() : null;
if (row["CalcAvgYN"] != DBNull.Value)
result.CalcAvgYN = (bool)row["CalcAvgYN"];
else
result.CalcAvgYN = false;
if (row["ReportOnCOAYN"] != DBNull.Value)
result.ReportYN = (bool)row["ReportOnCOAYN"];
else
result.ReportYN = false;
result.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null;
result.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null;
result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime();
result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime();
result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32();
resultList.Add(result);
}
returnDT = null;
return resultList;
}
catch
{
throw new Exception("Error in GetResults");
}
}
public Client GetClient(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? id)
{
try {
Client result = new Client();
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClient";
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = id;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
result.ClientId = Convert.ToInt32(row["ClientID"]);
result.AccountingId = row["AccountingID"].ToString();
result.ClientName = row["ClientName"].ToString();
result.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
if (result.TermId != null && result.TermId != -1)
result.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
result.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
result.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
result.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
result.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
result.BillingAddress = row["BillingAddress"].ToString();
result.BillingCity = row["BillingCity"].ToString();
result.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
if (result.BillingStateId != null && result.BillingStateId != -1)
result.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
result.BillingZip = row["BillingZip"].ToString();
result.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
if (result.BillingCountryId != null && result.BillingCountryId != -1)
result.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
result.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
result.ShippingAddress = row["ShippingAddress"].ToString();
result.ShippingCity = row["ShippingCity"].ToString();
result.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
if (result.ShippingStateId != null && result.ShippingStateId != -1)
result.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
result.ShippingZip = row["ShippingZip"].ToString();
result.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
if (result.ShippingCountryId != null && result.ShippingCountryId != -1)
result.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
result.CreatedUser = row["CreatedUser"].ToString();
result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
result.ModifiedUser = row["ModifiedUser"].ToString();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
row = null;
}
returnDT = null;
return result;
}catch {
throw;
}
}
private SmartCollection<SampleAnalyte> GetSampleAnalytes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber)
{
try
{
SmartCollection<SampleAnalyte> resultList = new SmartCollection<SampleAnalyte>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleAnalytes";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleAnalyte analyte = new SampleAnalyte();
analyte.SampleAnalyteId = Convert.ToInt32(row["SampleAnalyteID"]);
analyte.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
analyte.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
analyte.AnalyteName = row["AnalyteName"].ToString();
if (analyte.AnalyteId > -1)
analyte.Analyte = new Analyte() { AnalyteId = analyte.AnalyteId, AnalyteName = row["AnalyteName"].ToString(), Active = true, AntibioticYN = (bool)row["AntibioticYN"], ControlledYN = (bool)row["ControlledYN"] };
if (row["Amount"] != DBNull.Value)
analyte.Amount = Convert.ToDecimal(row["Amount"]);
analyte.AmountUOM = row["AmountUOM"].ToString();
if (analyte.AmountUOM != null)
analyte.AmountUnitOfMeasure = new UnitOfMeasure { UomId = 0, Uom = analyte.AmountUOM };
if (row["Concentration"] != DBNull.Value)
analyte.Concentration = Convert.ToDecimal(row["Concentration"]);
analyte.ConcentrationUOM = row["ConcentrationUOM"].ToString();
if (analyte.ConcentrationUOM != null)
analyte.ConcentrationUnitOfMeasure = new UnitOfMeasure { UomId = 0, Uom = analyte.ConcentrationUOM };
analyte.ControlledYN = row["ControlledYN"] != DBNull.Value ? Convert.ToBoolean(row["ControlledYN"]) : false;
analyte.AntibioticYN = row["AntibioticYN"] != DBNull.Value ? Convert.ToBoolean(row["AntibioticYN"]) : false;
analyte.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
analyte.CreatedUser = row["CreatedUser"].ToString();
analyte.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
analyte.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
analyte.ModifiedUser = row["ModifiedUser"].ToString();
analyte.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(analyte);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<ClientComplaint> GetClientComplaintsOpen(Identification identification)
{
try
{
SmartCollection<ClientComplaint> resultList = new SmartCollection<ClientComplaint>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientComplaintsOpen";
DbCommand.Parameters.Clear();
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
ClientComplaint complaint = new ClientComplaint();
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
if (row["CorrectiveActionDate"] != DBNull.Value)
complaint.CorrectiveActionDate = (DateTime)row["CorrectiveActionDate"];
else
complaint.CorrectiveActionDate = null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(complaint);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
private SmartCollection<SampleCharge> GetSampleCharges(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber)
{
try
{
SmartCollection<SampleCharge> resultList = new SmartCollection<SampleCharge>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleCharges";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleCharge charge = new SampleCharge();
charge.SampleChargeId = Convert.ToInt32(row["SampleChargeID"]);
charge.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
charge.ChargeId = Convert.ToInt32(row["ChargeID"]);
if (charge.ChargeId > 0)
charge.Charge = new Charge() { ChargeId = charge.ChargeId, ChargeDescription = row["Description"].ToString(), AccountingCode = row["AccountingCode"].ToString(), Amount = (decimal)row["Amount"] };
charge.Description = row["Description"].ToString();
charge.AccountingCode = row["AccountingCode"].ToString();
charge.BillGroup = row["BillGroup"] != DBNull.Value ? Convert.ToInt32(row["BillGroup"]) : new Int32();
charge.Amount = row["Amount"] != DBNull.Value ? (decimal)row["Amount"] : new Decimal();
charge.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
charge.CreatedUser = row["CreatedUser"].ToString();
charge.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
charge.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
charge.ModifiedUser = row["ModifiedUser"].ToString();
charge.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(charge);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<ClientNote> GetClientNotes(int clientId)
{
try {
SmartCollection<ClientNote> resultList = new SmartCollection<ClientNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNotes";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows) {
ClientNote note = new ClientNote();
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
private SmartCollection<SampleContainer> GetSampleLabel(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int arlNumber)
{
SmartCollection<SampleContainer> returnList = new SmartCollection<SampleContainer>();
try
{
returnList = new SmartCollection<SampleContainer>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleLabel";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
SampleContainer container = new SampleContainer();
container.SampleContainerId = Convert.ToInt32(row["SampleContainerID"]);
container.Label = row["Label"].ToString();
container.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
container.ReceivedDate = row["ReceivedDate"].ToString();
container.StorageLocationCode = row["StorageLocationCode"].ToString();
returnList.Add(container);
}
}
catch
{
throw;
}
return returnList;
}
public SmartCollection<Client> GetClientsRecent(int userId)
{
try {
SmartCollection<Client> resultList = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsRecent";
DbCommand.Parameters.Clear();
DataTable clientDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in clientDT.Rows)
{
Client client = new Client();
client.ClientId = Convert.ToInt32(row["ClientID"]);
client.AccountingId = row["AccountingID"].ToString();
client.ClientName = row["ClientName"].ToString();
client.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
if (client.TermId != null && client.TermId != -1)
client.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
client.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
client.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
client.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
client.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
client.BillingAddress = row["BillingAddress"].ToString();
client.BillingCity = row["BillingCity"].ToString();
client.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
if (client.BillingStateId != null && client.BillingStateId != -1)
client.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
client.BillingZip = row["BillingZip"].ToString();
client.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
if (client.BillingCountryId != null && client.BillingCountryId != -1)
client.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
client.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
client.ShippingAddress = row["ShippingAddress"].ToString();
client.ShippingCity = row["ShippingCity"].ToString();
client.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
if (client.ShippingStateId != null && client.ShippingStateId != -1)
client.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
client.ShippingZip = row["ShippingZip"].ToString();
client.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
if (client.ShippingCountryId != null && client.ShippingCountryId != -1)
client.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
client.CreatedUser = row["CreatedUser"].ToString();
client.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
client.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
client.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
client.ModifiedUser = row["ModifiedUser"].ToString();
client.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
// Other client objects
client.Contacts = GetContacts(client.ClientId);
client.Prices = GetClientPricings(client.ClientId);
client.Documents = GetClientDocuments(client.ClientId);
client.Notes = GetClientNotes(client.ClientId);
client.Complaints = GetClientComplaints(client.ClientId);
resultList.Add(client);
}
clientDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
private SmartCollection<Sample> GetSamples(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, string searchString, Identification identification)
{
try
{
SmartCollection<Sample> resultList = new SmartCollection<Sample>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSamples";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SearchString", System.Data.SqlDbType.NVarChar, 100).Value = searchString;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
Sample sample = new Sample();
sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
sample.Description = row["Description"].ToString();
sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?();
sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32();
sample.ClientName = row["ClientName"].ToString();
if (sample.ClientId > -1)
{
//sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName };
using (ClientDAO clientDao = new ClientDAO())
{
sample.Client = clientDao.GetClient((int)sample.ClientId);
}
}
sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress;
sample.PONumber = row["PONumber"].ToString();
sample.FormulationId = row["FormulationID"].ToString();
sample.LotNumber = row["LotNumber"].ToString();
sample.ProjectNumber = row["ProjectNumber"].ToString();
sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32();
sample.StorageLocationName = row["StorageLocationName"].ToString();
sample.StorageLocationConditions = row["StorageLocationConditions"].ToString();
sample.StorageLocationCode = row["StorageLocationCode"].ToString();
if (sample.StorageLocationId > -1)
sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() };
sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32();
sample.RequestedStorageName = row["RequestedStorageName"].ToString();
if (sample.RequestedStorageId > -1)
sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" };
sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32();
sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null;
if (sample.DosageId > -1)
sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() };
sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32();
sample.ContainerDescription = row["ContainerDescription"].ToString();
sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal();
sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32();
sample.VolumeUOM = row["VolumeUOM"].ToString();
sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false;
sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
sample.CompoundedBy = row["CompoundedBy"].ToString();
sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?();
sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
sample.CreatedUser = row["CreatedUser"].ToString();
sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
sample.ModifiedUser = row["ModifiedUser"].ToString();
sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber);
sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber);
sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber);
sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification);
sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification);
resultList.Add(sample);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<Contact> GetContacts(int clientId)
{
try
{
SmartCollection<Contact> resultList = new SmartCollection<Contact>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetContacts";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable contactsDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in contactsDT.Rows)
{
Contact contact = new Contact();
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(contact);
}
contactsDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public SmartCollection<SampleNote> GetSampleTestNotes(int arlNumber, int sampleTestId)
{
try
{
SmartCollection<SampleNote> resultList = new SmartCollection<SampleNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetSampleTestNotes";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows)
{
SampleNote note = new SampleNote();
note.SampleNoteId = Convert.ToInt32(row["SampleNoteID"]);
note.ARLNumber = row["ARLNumber"] != DBNull.Value ? Convert.ToInt32(row["ARLNumber"]) : new Int32();
note.SampleTestId = row["SampleTestID"] != DBNull.Value ? Convert.ToInt32(row["SampleTestID"]) : new Int32();
note.Note = row["Note"].ToString();
note.IncludeOnWOYN = row["IncludeOnWOYN"] != DBNull.Value ? Convert.ToBoolean(row["IncludeOnWOYN"]) : false;
note.IncludeOnCOAYN = row["IncludeOnCOAYN"] != DBNull.Value ? Convert.ToBoolean(row["IncludeOnCOAYN"]) : false;
note.BillGroup = row["BillGroup"] != DBNull.Value ? Convert.ToInt32(row["BillGroup"]) : new Int32();
note.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}