public SmartCollection<ClientDocument> GetClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId)
{
var result = new SmartCollection<ClientDocument>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientDocuments";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientId", clientId);
using (var reader = dbConnection.ExecuteReader(dbCommand)) {
while (reader.Read()) {
result.Add(new ClientDocument {
ClientDocumentId = (int)reader["ClientDocumentID"],
Filename = reader["Filename"].ToString(),
CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : -1,
CreatedUser = reader["CreatedUser"].ToString(),
CreatedDate = reader["CreatedDate"] != DBNull.Value ? (DateTime)reader["CreatedDate"] : (DateTime)SqlDateTime.Null,
ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : -1,
ModifiedUser = reader["ModifiedUser"].ToString(),
ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? (DateTime)reader["ModifiedDate"] : (DateTime)SqlDateTime.Null
});
}
}
return result;
}
public SmartCollection<Client> GetClientsList()
{
try {
SmartCollection<Client> results = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsList";
DbCommand.Parameters.Clear();
var reader = DbConnection.ExecuteReader(DbCommand);
results.AddRange(AutoMap.MapReaderToList<Client>(reader));
}
}else {
throw new Exception("Unable to Connect");
}
}
return results;
}catch {
throw;
}
}
public SmartCollection<Order> SearchModuleOrders(int? customerId, DateTime? orderReceivedStartDate, DateTime? orderReceivedEndDate, int? testId, int? analyteId)
{
try
{
var searchCustomers = customerId != null && customerId > 0;
var searchDates = orderReceivedStartDate != null || orderReceivedEndDate != null;
var searchTests = testId != null && testId > 0;
var serachAnalytes = analyteId != null && analyteId > 0;
var result = new SmartCollection<Order>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql = @"
select distinct orders.id, orders.parentid, customers.customer_name, orders.received_date, orders.status
from orders
LEFT JOIN customers ON customers.id = orders.parentid
";
if (searchTests)
{
sql += @"
LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
";
}
if (serachAnalytes)
{
// NOTE: joining to seperate order_samples / order_samples_tests table aliases
// is so searching tests and analytes can be be done independently or concurrently
sql += @"
LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id
LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id
LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id
";
}
sql += @"
WHERE orders.delete_date IS NULL
";
if (searchCustomers)
{
sql += @"
AND customers.id = @customerId
";
}
if (searchDates)
{
sql += @"
AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate)
";
}
if (searchTests)
{
sql += @"
AND tests.testid = @testId
";
}
if (serachAnalytes)
{
sql += @"
AND analytes.analyteid = @analyteId
";
}
sql += @"
ORDER BY orders.received_date DESC
";
DbCommand.CommandText = sql;
if (searchCustomers)
{
DbCommand.Parameters.AddWithValue("@customerId", customerId);
}
if (searchDates)
{
DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value));
DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value));
}
if (searchTests)
{
DbCommand.Parameters.AddWithValue("@testId", testId);
}
if (serachAnalytes)
{
DbCommand.Parameters.AddWithValue("@analyteId", analyteId);
}
var reader = DbConnection.ExecuteReader(DbCommand);
result.AddRange(AutoMap.MapReaderToList<Order>(reader));
foreach (var order in result)
{
using (var dao = new ClientDAO())
order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
private SmartCollection<SampleTestContainer> GetSampleTestContainers(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? sampleTestId)
{
SmartCollection<SampleTestContainer> returnList = new SmartCollection<SampleTestContainer>();
try
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleTestContainers";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
returnList.AddRange(AutoMap.MapReaderToList<SampleTestContainer>(dbConnection.ExecuteReader(dbCommand)));
}
catch
{
throw;
}
return returnList;
}
public SmartCollection<SampleDocument> GetSampleDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int arlNumber)
{
var result = new SmartCollection<SampleDocument>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSampleDocuments";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ARLNumber", arlNumber);
using (var reader = dbConnection.ExecuteReader(dbCommand))
{
while (reader.Read())
{
result.Add(new SampleDocument
{
SampleDocumentId = Convert.ToInt32(reader["SampleDocumentID"]),
ARLNumber = Convert.ToInt32(reader["ARLNumber"]),
Filename = reader["Filename"].ToString(),
CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : new Int32(),
CreatedUser = reader["CreatedUser"].ToString(),
CreatedDate = reader["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(reader["CreatedDate"]) : new DateTime(),
ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : new Int32(),
ModifiedUser = reader["ModifiedUser"].ToString(),
ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? Convert.ToDateTime(reader["ModifiedDate"]) : new DateTime()
});
}
}
return result;
}
public UserSignature GetSignature(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int userId)
{
try
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetSignature";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
return AutoMap.MapReaderToObject<UserSignature>(dbConnection.ExecuteReader(dbCommand));
}
catch
{
throw;
}
}
public Role GetRole(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Guid roleId)
{
try {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetRole";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@RoleId", System.Data.SqlDbType.UniqueIdentifier).Value = roleId;
return AutoMap.MapReaderToObject<Role>(dbConnection.ExecuteReader(dbCommand));
}catch {
throw;
}
}
public Department GetDepartment(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? departmentid)
{
try {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetDepartment";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = departmentid;
return AutoMap.MapReaderToObject<Department>(dbConnection.ExecuteReader(dbCommand));
}catch {
throw;
}
}
public List<ReportNotification> GetReportNotifications()
{
List<ReportNotification> returnList = new List<ReportNotification>();
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandText = @"
SELECT id, reportid, notificationid, departmentid, customerid, subjectline, created_date
FROM report_notifications
WHERE DATEDIFF(MINUTE,created_date,@CutoffDateTime) >= 5
";
DbCommand.Parameters.Clear();
DbCommand.Parameters.AddWithValue("@CutoffDateTime", DateTime.Now);
returnList.AddRange(AutoMap.MapReaderToList<ReportNotification>(DbConnection.ExecuteReader(DbCommand)));
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
return returnList;
}