private string GetReportableCOAsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { int returnValue = 0; User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return string.Empty; return returnValue <= 0 ? string.Empty : returnValue.ToString(); } 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 string GetPendingSubmissionsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { int returnValue = 0; User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return string.Empty; 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 Count(sampleTest.id) FROM orders_samples_tests as sampleTest 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; returnValue = (int)dbConnection.ExecuteScalar(dbCommand); return returnValue <= 0 ? string.Empty : returnValue.ToString(); } 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 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<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; } }