public List <ResultModel> ReadResultsList()
{
string query = @"
select IdResult, IdEmployee, IdStudy, DateOfResult, Description
from Results
";
return(BaseDAO.Select(query, ReadResultModel));
}
public List <UserModel> ReadUsersList()
{
string query = @"
select IdUser, IdEmployee, Login, Password, DateOfChange, InUse, IdPatient
from Users
";
return(BaseDAO.Select(query, ReadUserModel));
}
public List <ResultUnitModel> ReadResultUnitsList()
{
string query = @"
select IdResultUnit, IdOrderedTests, IdResult, Value
from ResultUnits
";
return(BaseDAO.Select(query, ReadResultUnitModel));
}
public List <PatientModel> ReadPatientsSelect()
{
string query = @"
select IdPatient, FirstName, Surname, Pesel
from Patients
";
return(BaseDAO.Select(query, ReadPatientSelect));
}
public List <ResultUnitModel> ReadResultUnitModelByResultId(long id)
{
string query = $@"
select IdResultUnit, IdOrderedTests, IdResult, Value
from ResultUnits
where IdResult = {id}
";
return(BaseDAO.Select(query, ReadResultUnitModel));
}
public List <SampleModel> ReadSamplesList()
{
string query = @"
select s.IdSample, s.IdEmployee, s.IdStudy, s.DateOfCollection, s.Code, e.FirstName, e.Surname
from Samples s
join Employees e on s.IdEmployee = e.IdEmployee
";
return(BaseDAO.Select(query, ReadSampleModel));
}
public List <StudyModel> ReadNotVerifiedStudiesListByOrderId(long id)
{
string query = $@"
select s.IdStudy, s.IdProfile, s.IdEmployee, s.IdOrder, s.IdStatus, s.DateOfStudy, sam.Code as Sample, s.ReasonForRepeat, s.Actual, s.PreviousId, s.DateOfEnd
from Studies s
left join Samples sam on s.IdStudy = sam.IdStudy
where s.IdOrder = {id} and s.Actual = 1 and IdStatus in ({(long)StatusTypeEnum.Ordered}, {(long)StatusTypeEnum.InProgress}, {(long)StatusTypeEnum.TakenSample}, {(long)StatusTypeEnum.ToVerify})
";
return(BaseDAO.Select(query, ReadSimpleStudyModel));
}
public List <StudyModel> ReadStudiesListByOrderId(long?id)
{
string query = $@"
select s.IdStudy, s.IdProfile, s.IdEmployee, s.IdOrder, s.IdStatus, s.DateOfStudy, sam.Code as Sample, s.ReasonForRepeat, s.Actual, s.PreviousId, s.DateOfEnd
from Studies s
left join Samples sam on s.IdStudy = sam.IdStudy
where s.IdOrder = {id} and s.Actual = 1
";
return(BaseDAO.Select(query, ReadSimpleStudyModel));
}
public List <SelectOption> ReadConsultantsSelect()
{
string query = @"
select e.IdEmployee, e.FirstName, e.Surname
from Employees e
join Positions p on e.IdPosition = p.IdPosition
where p.IdPosition = 1;
";
return(BaseDAO.Select(query, ReadPatientSelect));
}
public List <long> ReadOrderedTestByStudyId(long idStudy)
{
string query = $@"
select t.IdTest
from Tests t
join OrderedTests o on t.IdTest = o.IdTest
where o.IdStudy= {idStudy}
";
return(BaseDAO.Select(query, ReadOrderedTestId));
}
public List <PatientModel> ReadPatientsList()
{
string query = @"
select p.IdPatient, p.FirstName, p.Surname, p.Pesel, p.Sex, p.Street, p.HouseNumber, p.City, p.PostalCode, p.Country, p.Phone, p.IdCardNumber,
p.ContactPersonFirstName, p.ContactPersonSurname, p.ContactPersonPesel, p.ContactPersonPhone, u.Login
from Patients p
join Users u on p.IdPatient = u.IdPatient
";
return(BaseDAO.Select(query, ReadPatientModel));
}
public List <TestModel> ReadOrderedTestsList(long idStudy, string lang)
{
string query = $@"
select t.IdTest, t.IdProfile, t.Code, t.NormMinM, t.NormMaxM, t.NormMinF, t.NormMaxF, tr.Name as Name, t.Unit
from Tests t
join OrderedTests o on t.IdTest = o.IdTest
join TestTranslations tr on t.IdTest = tr.IdTest
join Languages l on tr.IdLanguage = l.IdLanguage
where o.IdStudy = {idStudy} and l.code = '{lang}'
";
return(BaseDAO.Select(query, ReadSimpleTestModel));
}
public List <EmployeeModel> ReadConsultantsList(long idOrder, string lang)
{
string query = $@"
select e.IdEmployee, e.IdPosition, e.FirstName, e.Surname, e.Phone, e.Email, e.IdWard, wt.Name as Ward
from Employees e join Consultants c on e.IdEmployee = c.IdEmployee
left join Wards w on e.IdWard = w.IdWard
full join WardTranslations wt on w.IdWard = wt.Idward
where c.IdOrder = {idOrder}
and (w.IdWard is null or wt.IdLanguage = (select l1.IdLanguage from Languages l1 where l1.Code = '{lang}'))
";
return(BaseDAO.Select(query, ReadSimpleEmployeeModel));
}
public List <ProfileModel> ReadProfilesList(string lang)
{
string query = $@"
select p.IdProfile, p.Code, p.Permament, t.Name as Name
from Profiles p
join ProfileTranslations t on p.IdProfile = t.IdProfile
join Languages l on t.IdLanguage = l.IdLanguage
where l.code = '{lang}'
order by t.Name
";
return(BaseDAO.Select(query, ReadProfileModel));
}
public List <TestModel> ReadFullOrderedTestByStudyId(long idStudy, string lang)
{
string query = $@"
select t.IdTest, t.IdProfile, t.Code, t.NormMinM, t.NormMaxM, t.NormMinF, t.NormMaxF, tr.Name as Name, t.Unit, ru.Value as Result, o.IdOrderedTest
from Tests t
join OrderedTests o on t.IdTest = o.IdTest
join TestTranslations tr on t.IdTest = tr.IdTest
join Languages l on tr.IdLanguage = l.IdLanguage
left join ResultUnits ru on o.IdOrderedTest = ru.IdOrderedTests
where o.IdStudy= {idStudy} and l.code = '{lang}'
";
return(BaseDAO.Select(query, ReadOrderedTestModel));
}
//public List<EmployeeModel> ReadEmployeesList(string lang)
//{
// string query = $@"
// select e.IdEmployee, e.IdPosition, e.FirstName, e.Surname, e.Pesel, e.Sex, e.Street, e.HouseNumber, e.City, e.PostalCode, e.Country,
// e.Phone, e.Email, e.DateOfEmployment, e.DateOfLaying, e.LicenseNumber, e.IdWard, wt.Name as Ward
// from Employees
// join Wards w on e.IdWard = w.IdWard
// join WardTranslations wt on w.IdWard = wt.Idward
// where wt.IdLanguage = (select l1.IdLanguage from Languages l1 where l1.Code = '{lang}')
// ";
// return BaseDAO.Select(query, ReadEmployeeModel);
//}
public List <EmployeeModel> ReadEmployeesListForAdmin(string lang)
{
string query = $@"
select e.IdEmployee, e.IdPosition, pt.Name as Position, e.FirstName, e.Surname, e.Pesel, e.Sex, e.Street, e.HouseNumber, e.City, e.PostalCode, e.Country,
e.Phone, e.Email, e.DateOfEmployment, e.DateOfLaying, e.LicenseNumber, e.IdWard, wt.Name as Ward, u.Login
from Employees e
join Users u
on e.IdEmployee = u.IdEmployee
left join Wards w on e.IdWard = w.IdWard
full join WardTranslations wt on w.IdWard = wt.Idward
join Positions p on e.IdPosition = p.IdPosition
join PositionTranslations pt on p.IdPosition = pt.IdPosition
where (w.IdWard is null or wt.IdLanguage = (select l1.IdLanguage from Languages l1 where l1.Code = '{lang}'))
and pt.IdLanguage = (select l2.IdLanguage from Languages l2 where l2.Code = '{lang}')
and u.InUse = 1;
";
return(BaseDAO.Select(query, ReadEmployeeModelForAdmin));
}
public List <OrderModel> ReadOrdersList(long idEmployee, string lang)
{
//Status: all
string query = $@"
select o.IdOrder, o.IdPatient, o.IdEmployee, e.FirstName as DoctorFirstName, e.Surname as DoctorSurname, o.Comment, o.DateOfOrder, o.DateOfReceived, o.IdStatus,
st.Name as Status, o.IdPriority, prt.Name as Priority, p.FirstName, p.Surname
from Orders o
join Patients p on o.IdPatient = p.IdPatient
join Employees e on o.IdEmployee = e.IdEmployee
join Priorities pr on o.IdPriority = pr.IdPriority
join PriorityTranslations prt on pr.IdPriority = prt.IdPriority
join Status s on o.IdStatus = s.IdStatus
join StatusTranslations st on s.IdStatus = st.IdStatus
where ({idEmployee} = o.IdEmployee or {idEmployee} in (select con.IdEmployee from Consultants con where con.IdOrder = o.IdOrder))
and st.IdLanguage = (select l2.IdLanguage from Languages l2 where l2.Code = '{lang}')
and prt.IdLanguage = (select l3.IdLanguage from Languages l3 where l3.Code = '{lang}')
order by o.IdPriority desc, o.IdOrder
";
return(BaseDAO.Select(query, ReadSimpleOrderModel));
}
public List <StudyModel> ReadStudiesListForNurse(string lang)
{
string query = $@"
select s.IdStudy, s.IdOrder, s.IdStatus, stt.Name as Status, prt.Name as Priority,
p.FirstName as PatientName, p.Surname as PatientSurname, o.DateOfOrder,
pft.Name as Profile
from Studies s
join Orders o on s.IdOrder = o.IdOrder
join Patients p on o.IdPatient = p.IdPatient
join Status st on s.IdStatus = st.IdStatus
join StatusTranslations stt on st.IdStatus = stt.IdStatus
join Priorities pr on o.IdPriority = pr.IdPriority
join PriorityTranslations prt on pr.IdPriority = prt.IdPriority
join Profiles pf on s.IdProfile = pf.IdProfile
join ProfileTranslations pft on pf.IdProfile = pft.IdProfile
where stt.IdLanguage = (select l1.IdLanguage from Languages l1 where l1.Code = '{lang}')
and prt.IdLanguage = (select l2.IdLanguage from Languages l2 where l2.Code = '{lang}')
and pft.IdLanguage = (select l3.IdLanguage from Languages l3 where l3.Code = '{lang}')
and s.IdStatus in ({(long)StatusTypeEnum.Ordered}, {(long)StatusTypeEnum.TakenSample})
order by o.IdPriority desc, s.IdOrder, s.IdStudy
";
return(BaseDAO.Select(query, ReadStudyModelForList));
}
