public List <OrderModel> ReadOrdersListForNurse(string lang)
{
//Status: Ordered and InProgress where study not in progress in lab (only sample collected)
string query = $@"
select o.IdOrder, o.IdPatient, o.IdEmployee, e.FirstName as DoctorFirstName, e.Surname as DoctorSurname, o.Comment, o.DateOfOrder, 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 st.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 (s.IdStatus = 1 or s.IdStatus = 3)
and o.IdOrder in (
select stu.IdOrder
from Studies stu
where stu.IdStatus in (1, 7)
)
order by o.IdPriority desc, o.IdOrder
";
return(BaseDAO.Select(query, ReadSimpleOrderModel));
}
public List <StudyModel> ReadStudiesListForLab(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, sam.Code as Sample
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
left join Samples sam on s.IdStudy = sam.IdStudy
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.TakenSample}, {(long)StatusTypeEnum.InProgress}, {(long)StatusTypeEnum.ToVerify})
order by o.IdPriority desc, s.IdOrder, s.IdStudy
";
return(BaseDAO.Select(query, ReadStudyModelForLabList));
}
public List <StudyModel> ReadStudiesListForDoctors(long idEmployee, 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 ({idEmployee} = o.IdEmployee or {idEmployee} in (select con.IdEmployee from Consultants con where con.IdOrder = o.IdOrder))
and 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 != {(long)StatusTypeEnum.ReOrdered}
order by o.IdPriority desc, s.IdOrder, s.IdStudy
";
return(BaseDAO.Select(query, ReadStudyModelForList));
}
public StudyModel ReadStudyById(long?id, string lang)
{
string query = $@"
select s.IdStudy, s.IdProfile, s.IdEmployee, s.IdOrder, s.IdStatus, stt.Name as Status, s.DateOfStudy, o.IdPriority, prt.Name as Priority,
e.FirstName as DoctorName, e.Surname as DoctorSurname, p.IdPatient, p.FirstName as PatientName, p.Surname as PatientSurname, o.DateOfOrder,
pft.Name as Profile, o.Comment, sam.Code as Sample, p.Sex as PatientSex, s.ReasonForRepeat, s.Actual, s.PreviousId, s.DateOfEnd,
e2.FirstName as RepeatFirstName, e2.Surname as RepeatSurname, n.IdStudy as NextId
from Studies s
join Orders o on s.IdOrder = o.IdOrder
join Patients p on o.IdPatient = p.IdPatient
left join Employees e on o.IdEmployee = e.IdEmployee
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
left join Samples sam on s.IdStudy = sam.IdStudy
left join Employees e2 on s.IdRepeatEmployee = e2.IdEmployee
left join (select * from Studies s2 ) n on s.IdStudy = n.PreviousId
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.IdStudy = {id}
";
return(BaseDAO.SelectFirst(query, ReadStudyModel));
}
public void DeleteOrderedTestByStudy(long idStudy)
{
string query = $@"
delete OrderedTests where IdStudy = {idStudy}
";
BaseDAO.InsertOrUpdate(query, false);
}
public void DeleteStudiesByOrder(long idOrder)
{
string query = $@"
delete Studies where IdOrder = {idOrder}
";
BaseDAO.InsertOrUpdate(query, false);
}
public List <DictionaryModel> ReadDictionaryListByType(DictionaryTypesEnum type, string lang)
{
string query = "";
switch (type)
{
case DictionaryTypesEnum.Positions:
query = $@"
select p.IdPosition as Id, t.Name as Name
from Positions p
join PositionTranslations t on p.IdPosition = t.IdPosition
join Languages l on t.IdLanguage = l.IdLanguage
where l.code = '{lang}'
order by t.Name
";
break;
case DictionaryTypesEnum.Ward:
query = $@"
select p.IdWard as Id, t.Name as Name
from Wards p
join WardTranslations t on p.IdWard = t.IdWard
join Languages l on t.IdLanguage = l.IdLanguage
where l.code = '{lang}'
order by t.Name
";
break;
case DictionaryTypesEnum.Priorities:
query = $@"
select p.IdPriority as Id, t.Name as Name
from Priorities p
join PriorityTranslations t on p.IdPriority = t.IdPriority
join Languages l on t.IdLanguage = l.IdLanguage
where l.code = '{lang}'
order by t.Name
";
break;
case DictionaryTypesEnum.Status:
query = $@"
select p.IdStatus as Id, t.Name as Name
from Status p
join StatusTranslations t on p.IdStatus = t.IdStatus
join Languages l on t.IdLanguage = l.IdLanguage
where l.code = '{lang}'
order by t.Name
";
break;
}
List <DictionaryModel> list = BaseDAO.Select(query, ReadDictionaryModel);
foreach (DictionaryModel model in list)
{
model.Type = type;
}
return(list);
}
public void ChangeOrderStatus(long IdOrder, long IdStatus)
{
string query = $@"
update Orders set IdStatus={IdStatus}
where IdOrder={IdOrder}
";
BaseDAO.InsertOrUpdate(query, false);
}
public void SetNotUseSession(long idUser)
{
string query = $@"
update Sessions set InUse = 0
where IdUser = {idUser}
";
BaseDAO.InsertOrUpdate(query, false);
}
public void UpdateUser(UserModel u)
{
string query = $@"
update Users set Password = {BaseDAO.SetString(u.Password)}, DateOfChange = {BaseDAO.SetDate(DateTime.Now)}, InUse = {BaseDAO.SetBool(u.InUse)}
where IdUser = {u.IdUser};
";
BaseDAO.InsertOrUpdate(query, false);
}
public void InsertUser(UserModel u)
{
string query = $@"
insert into Users(IdEmployee, Login, Password, DateOfChange, InUse, IdPatient)
values({BaseDAO.SetNullableLong(u.IdEmployee)},{BaseDAO.SetString(u.Login)},{BaseDAO.SetString(u.Password)},{BaseDAO.SetDate(DateTime.Now)},1,{BaseDAO.SetNullableLong(u.IdPatient)});
";
BaseDAO.InsertOrUpdate(query, false);
}
public void UpdateSample(SampleModel s)
{
string query = $@"
update Samples set Code={BaseDAO.SetString(s.Code)}
where IdSample={s.IdSample}
";
BaseDAO.InsertOrUpdate(query, false);
}
public List <PatientModel> ReadPatientsSelect()
{
string query = @"
select IdPatient, FirstName, Surname, Pesel
from Patients
";
return(BaseDAO.Select(query, ReadPatientSelect));
}
public void InsertVerify(VerificationModel v)
{
string query = $@"
insert into Verifications(IdResult, IdEmployee, DateOfVerification, Description, Positive)
values({v.IdResult},{v.IdEmployee},{BaseDAO.SetDate(DateTime.Now)},{BaseDAO.SetString(v.Description)}, {BaseDAO.SetBool(v.Positive)});
";
BaseDAO.InsertOrUpdate(query, false);
}
public List <ResultModel> ReadResultsList()
{
string query = @"
select IdResult, IdEmployee, IdStudy, DateOfResult, Description
from Results
";
return(BaseDAO.Select(query, ReadResultModel));
}
public void InsertResultUnit(ResultUnitModel r)
{
string query = $@"
insert into ResultUnits(IdOrderedTests, IdResult, Value)
values({r.IdOrderedTest},{r.IdResult},{BaseDAO.SetNullableDouble(r.Value)});
";
BaseDAO.InsertOrUpdate(query, false);
}
public void ChangeStudyStatus(long IdStudy, long IdStatus)
{
string query = $@"
update Studies set IdStatus={IdStatus}
where IdStudy={IdStudy}
";
BaseDAO.InsertOrUpdate(query, false);
}
public List <ResultUnitModel> ReadResultUnitsList()
{
string query = @"
select IdResultUnit, IdOrderedTests, IdResult, Value
from ResultUnits
";
return(BaseDAO.Select(query, ReadResultUnitModel));
}
public List <UserModel> ReadUsersList()
{
string query = @"
select IdUser, IdEmployee, Login, Password, DateOfChange, InUse, IdPatient
from Users
";
return(BaseDAO.Select(query, ReadUserModel));
}
public void InsertSession(SessionModel s)
{
string query = $@"
insert into Sessions(Token, ExpirationDate, IdUser, InUse)
values({BaseDAO.SetString(s.Token)},{BaseDAO.SetDate(s.ExpirationDate)},{s.IdUser}, 1);
";
BaseDAO.InsertOrUpdate(query, false);
}
public DictionaryModel ReadDictionaryById(DictionaryTypesEnum type, long?id, string lang)
{
if (id != null)
{
string query = "";
switch (type)
{
case DictionaryTypesEnum.Positions:
query = $@"
select p.IdPosition as Id, t.Name as Name
from Positions p
join PositionTranslations t on p.IdPosition = t.IdPosition
join Languages l on t.IdLanguage = l.IdLanguage
where p.IdPosition = {id} and l.code = '{lang}'
";
break;
case DictionaryTypesEnum.Ward:
query = $@"
select p.IdWard as Id, t.Name as Name
from Wards p
join WardTranslations t on p.IdWard = t.IdWard
join Languages l on t.IdLanguage = l.IdLanguage
where p.IdWard = {id} and l.code = '{lang}'
";
break;
case DictionaryTypesEnum.Priorities:
query = $@"
select p.IdPriority as Id, t.Name as Name
from Priorities p
join PriorityTranslations t on p.IdPriority = t.IdPriority
join Languages l on t.IdLanguage = l.IdLanguage
where p.IdPriority = {id} and l.code = '{lang}'
";
break;
case DictionaryTypesEnum.Status:
query = $@"
select p.IdStatus as Id, t.Name as Name
from Status p
join StatusTranslations t on p.IdStatus = t.IdStatus
join Languages l on t.IdLanguage = l.IdLanguage
where p.IdStatus = {id} and l.code = '{lang}'
";
break;
}
DictionaryModel model = BaseDAO.SelectFirst(query, ReadDictionaryModel);
model.Type = type;
return(model);
}
else
{
return(null);
}
}
public UserModel ReadUserByEmployeeId(long employeeId)
{
string query = $@"
select IdUser, IdEmployee, Login, Password, DateOfChange, InUse, IdPatient
from Users
where IdEmployee = {employeeId}
";
return(BaseDAO.SelectFirst(query, ReadUserModel));
}
public ResultUnitModel ReadResultUnitModelById(long id)
{
string query = $@"
select IdResultUnit, IdOrderedTests, IdResult, Value
from ResultUnits
where IdResultUnit = {id}
";
return(BaseDAO.SelectFirst(query, ReadResultUnitModel));
}
public ResultModel ReadResultById(long id)
{
string query = $@"
select IdResult, IdEmployee, IdStudy, DateOfResult, Description
from Results
where IdResult = {id}
";
return(BaseDAO.SelectFirst(query, ReadResultModel));
}
public void FullUpdateOrder(OrderModel o)
{
string query = $@"
update Orders set IdPatient={o.IdPatient}, IdWard={BaseDAO.SetNullableLong(o.IdWard)}, Institution={BaseDAO.SetString(o.Institution)},
Comment={BaseDAO.SetString(o.Comment)}, IdPriority={o.IdPriority}
where IdOrder={o.IdOrder}
";
BaseDAO.InsertOrUpdate(query, false);
}
public long?InsertSample(SampleModel s)
{
string query = $@"
insert into Samples(IdStudy, IdEmployee, DateOfCollection)
output INSERTED.IdSample
values({s.IdStudy}, {s.IdEmployee},{BaseDAO.SetDate(s.DateOfCollection)} ) ;
";
return(BaseDAO.InsertOrUpdate(query, true));
}
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 SessionModel ReadSessionByToken(string token)
{
string query = $@"
select IdSession, Token, ExpirationDate, IdUser, InUse
from Sessions
where Token = {BaseDAO.SetString(token)} and ExpirationDate > {BaseDAO.SetDate(DateTime.Now)} and InUse = 1
";
return(BaseDAO.SelectFirst(query, ReadSessionModel));
}
public long?InsertOrderedTest(long idStudy, long idTest)
{
string query = $@"
insert into OrderedTests(IdStudy, IdTest)
output INSERTED.IdOrderedTest
values({idStudy}, {idTest}) ;
";
return(BaseDAO.InsertOrUpdate(query, true));
}
public UserModel checkLogin(string login)
{
string query = $@"
select IdUser, IdEmployee, Login, Password, DateOfChange, InUse, IdPatient
from Users
where Login = {BaseDAO.SetString(login)}
";
return(BaseDAO.SelectFirst(query, ReadUserModel));
}
