public ViewModel()
{
TheModel = new MainModel();
TheModel.ReadConfiguration(@"F:\Oplandsmodel\NitrateModel\config_clgw122_Rerun11.xml");
SubModels = new SmartCollection <BaseModel>();
SubModels.AddRange(TheModel.SourceModels.Cast <BaseModel>());
SubModels.AddRange(TheModel.InternalReductionModels.Cast <BaseModel>());
SubModels.AddRange(TheModel.MainStreamRecutionModels.Cast <BaseModel>());
TheModel.LoadCatchments();
}
public ViewModel()
{
TheModel = new MainModel();
TheModel.ReadConfiguration(@"F:\Oplandsmodel\NitrateModel\config_clgw122_Rerun11.xml");
SubModels = new SmartCollection<BaseModel>();
SubModels.AddRange(TheModel.SourceModels.Cast<BaseModel>());
SubModels.AddRange(TheModel.InternalReductionModels.Cast<BaseModel>());
SubModels.AddRange(TheModel.MainStreamRecutionModels.Cast<BaseModel>());
TheModel.LoadCatchments();
}
public SmartCollection<User> GetAdministrators()
{
SmartCollection<User> resultList = new SmartCollection<User>();
try
{
AppVars.dbSettings = hostSettings;
if (HostSettings.ValidateModel()) {
using (SystemDAO systemDao = new SystemDAO()) {
using (systemDao.DbConnection = new MsSqlPersistence(systemDao.DbConnectionSettings)) {
if (systemDao.DbConnection.IsConnected()) {
using (systemDao.DbCommand) {
systemDao.DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
systemDao.DbCommand.CommandText = "uspGetAdministrators";
resultList.AddRange(AutoMap.MapReaderToList<User>(systemDao.DbConnection.ExecuteReader(systemDao.DbCommand)));
}
}else {
throw new Exception("Unable to Connect");
}
}
}
}
}catch {
throw;
}
return resultList;
}
public SmartCollection<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<ClientDocument> GetClientDocuments(int clientId)
{
try {
var result = new SmartCollection<ClientDocument>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
if (DbConnection.IsConnected())
using (DbCommand)
result.AddRange(GetClientDocuments(ref dbConnection, ref dbCommand, clientId));
return result;
}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<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<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;
}
}
public SmartCollection<SampleDocument> GetSampleDocuments(int arlNumber)
{
try
{
var result = new SmartCollection<SampleDocument>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
if (DbConnection.IsConnected())
using (DbCommand)
result.AddRange(GetSampleDocuments(ref dbConnection, ref dbCommand, arlNumber));
return result;
}
catch
{
throw;
}
}
public void Load(IEnumerable <SmileItem> smiles)
{
_items.Clear();
_items.AddRange(smiles);
}