private void CreateLinksForPatientTreatmentReport(
LinkedResourceBaseDto wrapper,
int pageNumber, int pageSize, PatientOnStudyCriteria patientOnStudyCriteria,
bool hasNext, bool hasPrevious)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreatePatientTreatmentReportResourceUri(ResourceUriType.Current, pageNumber, pageSize, patientOnStudyCriteria),
"self", "GET"));
if (hasNext)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreatePatientTreatmentReportResourceUri(ResourceUriType.NextPage, pageNumber, pageSize, patientOnStudyCriteria),
"nextPage", "GET"));
}
if (hasPrevious)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreatePatientTreatmentReportResourceUri(ResourceUriType.PreviousPage, pageNumber, pageSize, patientOnStudyCriteria),
"previousPage", "GET"));
}
}
public PatientTreatmentReportQuery(int pageNumber, int pageSize, DateTime searchFrom, DateTime searchTo, PatientOnStudyCriteria patientOnStudyCriteria) : this()
{
PageNumber = pageNumber;
PageSize = pageSize;
SearchFrom = searchFrom;
SearchTo = searchTo;
PatientOnStudyCriteria = patientOnStudyCriteria;
}
public string CreatePatientTreatmentReportResourceUri(ResourceUriType type,
int pageNumber,
int pageSize,
PatientOnStudyCriteria patientOnStudyCriteria)
{
switch (type)
{
case ResourceUriType.PreviousPage:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetPatientTreatmentReport",
new
{
pageNumber = pageNumber - 1,
pageSize = pageSize,
PatientOnStudyCriteria = patientOnStudyCriteria
}));
case ResourceUriType.NextPage:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetPatientTreatmentReport",
new
{
pageNumber = pageNumber + 1,
pageSize = pageSize,
PatientOnStudyCriteria = patientOnStudyCriteria
}));
case ResourceUriType.Current:
default:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetPatientTreatmentReport",
new
{
pageNumber = pageNumber,
pageSize = pageSize,
PatientOnStudyCriteria = patientOnStudyCriteria
}));
}
}
public ICollection <PatientList> GetPatientListOnStudyItems(DateTime searchFrom, DateTime searchTo, PatientOnStudyCriteria patientOnStudyCriteria, int facilityId)
{
string sql = "";
switch (patientOnStudyCriteria)
{
case PatientOnStudyCriteria.HasEncounterinDateRange:
sql = string.Format(@"
SELECT s.Id AS PatientId, s.FirstName + ' ' + s.Surname AS PatientName, s.FacilityName FROM
(
select ip.Id, ip.FirstName, ip.Surname, ifa.FacilityName
from Encounter ie
inner join Patient ip on ie.Patient_Id = ip.Id
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
where ie.Archived = 0 and ip.Archived = 0
and ie.EncounterDate between '{0}' and '{1}'
and ifa.Id = {2}
group by ip.Id, ip.FirstName, ip.Surname, ifa.FacilityName
) AS s
", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"), facilityId);
break;
case PatientOnStudyCriteria.PatientRegisteredinFacilityinDateRange:
sql = string.Format(@"
SELECT s.Id AS PatientId, s.FirstName + ' ' + s.Surname AS PatientName, s.FacilityName FROM
(
select ip.Id, ip.FirstName, ip.Surname, ifa.FacilityName
from Patient ip
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
where ip.Archived = 0
and ip.Created between '{0}' and '{1}'
and ifa.Id = {2}
) AS s
", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"), facilityId);
break;
default:
break;
}
SqlParameter[] parameters = new SqlParameter[0];
return(_unitOfWork.Repository <PatientList>().ExecuteSql(sql, parameters));
}
public ICollection <PatientOnStudyList> GetPatientOnStudyItems(DateTime searchFrom, DateTime searchTo, PatientOnStudyCriteria patientOnStudyCriteria)
{
string sql = "";
switch (patientOnStudyCriteria)
{
case PatientOnStudyCriteria.HasEncounterinDateRange:
sql = string.Format(@"
SELECT f.FacilityName, f.Id as FacilityId
, (
select count(distinct(ip.Id))
from Encounter ie
inner join Patient ip on ie.Patient_Id = ip.Id
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
where ie.Archived = 0 and ip.Archived = 0 and ie.Archived = 0
and ie.EncounterDate between '{0}' and '{1}'
and ifa.Id = f.Id
) AS PatientCount
, (
select count(distinct(ip.Id))
from Encounter ie
inner join Patient ip on ie.Patient_Id = ip.Id
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
inner join MetaPatientClinicalEvent impce on ipce.PatientClinicalEventGuid = impce.PatientClinicalEventGuid
where ie.Archived = 0 and ip.Archived = 0 and ipce.Archived = 0
and ie.EncounterDate between '{0}' and '{1}'
and ifa.Id = f.Id
and (impce.[Istheadverseeventserious?] <> 'Yes' or impce.[Istheadverseeventserious?] is null)
) AS PatientWithNonSeriousEventCount
, (
select count(distinct(ip.Id))
from Encounter ie
inner join Patient ip on ie.Patient_Id = ip.Id
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
inner join MetaPatientClinicalEvent impce on ipce.PatientClinicalEventGuid = impce.PatientClinicalEventGuid
where ie.Archived = 0 and ip.Archived = 0 and ipce.Archived = 0
and ie.EncounterDate between '{0}' and '{1}'
and ifa.Id = f.Id
and impce.[Istheadverseeventserious?] = 'Yes'
) AS PatientWithSeriousEventCount
, (
select count(distinct(ip.Id))
from Encounter ie
inner join Patient ip on ie.Patient_Id = ip.Id
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
where ie.Archived = 0 and ip.Archived = 0 and ipce.Archived = 0
and ie.EncounterDate between '{0}' and '{1}'
and ifa.Id = f.Id
) AS PatientWithEventCount
FROM Facility f
ORDER BY f.FacilityName
", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case PatientOnStudyCriteria.PatientRegisteredinFacilityinDateRange:
sql = string.Format(@"
SELECT f.FacilityName, f.Id as FacilityId
, (
select count(distinct(ip.Id))
from Patient ip
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
where ip.Archived = 0 and ipf.Archived = 0
and ipf.EnrolledDate between '{0}' and '{1}'
and ifa.Id = f.Id
) AS PatientCount
, (
select count(distinct(ip.Id))
from Patient ip
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
inner join MetaPatientClinicalEvent impce on ipce.PatientClinicalEventGuid = impce.PatientClinicalEventGuid
where ip.Archived = 0 and ipf.Archived = 0 and ipce.Archived = 0
and ipf.EnrolledDate between '{0}' and '{1}'
and ifa.Id = f.Id
and (impce.[Istheadverseeventserious?] <> 'Yes' or impce.[Istheadverseeventserious?] is null)
) AS PatientWithNonSeriousEventCount
, (
select count(distinct(ip.Id))
from Patient ip
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
inner join MetaPatientClinicalEvent impce on ipce.PatientClinicalEventGuid = impce.PatientClinicalEventGuid
where ip.Archived = 0 and ipf.Archived = 0 and ipce.Archived = 0
and ipf.EnrolledDate between '{0}' and '{1}'
and ifa.Id = f.Id
and impce.[Istheadverseeventserious?] = 'Yes'
) AS PatientWithSeriousEventCount
, (
select count(distinct(ip.Id))
from Patient ip
inner join PatientFacility ipf on ip.Id = ipf.Patient_Id AND ipf.EnrolledDate = (select MAX(EnrolledDate) FROM PatientFacility iipf WHERE iipf.Patient_Id = ip.Id)
inner join Facility ifa on ipf.Facility_Id = ifa.Id
inner join PatientClinicalEvent ipce on ip.Id = ipce.Patient_Id
where ip.Archived = 0 and ipf.Archived = 0 and ipce.Archived = 0
and ipf.EnrolledDate between '{0}' and '{1}'
and ifa.Id = f.Id
) AS PatientWithEventCount
FROM Facility f
ORDER BY f.FacilityName
", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
default:
break;
}
SqlParameter[] parameters = new SqlParameter[0];
return(_unitOfWork.Repository <PatientOnStudyList>().ExecuteSql(sql, parameters));
}
