private void CreateLinksForAdverseEventReport(
LinkedResourceBaseDto wrapper,
int pageNumber, int pageSize,
AdverseEventStratifyCriteria adverseEventStratifyCriteria,
bool hasNext, bool hasPrevious)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreateAdverseEventReportResourceUri(ResourceUriType.Current, pageNumber, pageSize, adverseEventStratifyCriteria),
"self", "GET"));
if (hasNext)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreateAdverseEventReportResourceUri(ResourceUriType.NextPage, pageNumber, pageSize, adverseEventStratifyCriteria),
"nextPage", "GET"));
}
if (hasPrevious)
{
wrapper.Links.Add(
new LinkDto(
_linkGeneratorService.CreateAdverseEventReportResourceUri(ResourceUriType.PreviousPage, pageNumber, pageSize, adverseEventStratifyCriteria),
"previousPage", "GET"));
}
}
public AdverseEventReportQuery(int pageNumber, int pageSize, DateTime searchFrom, DateTime searchTo,
AdverseEventStratifyCriteria adverseEventStratifyCriteria,
AgeGroupCriteria ageGroupCriteria,
string genderId,
string regimenId,
int organisationUnitId,
string outcomeId,
string isSeriousId,
string seriousnessId,
string classificationId) : this()
{
PageNumber = pageNumber;
PageSize = pageSize;
SearchFrom = searchFrom;
SearchTo = searchTo;
AdverseEventStratifyCriteria = adverseEventStratifyCriteria;
AgeGroupCriteria = ageGroupCriteria;
GenderId = genderId;
RegimenId = regimenId;
OrganisationUnitId = organisationUnitId;
OutcomeId = outcomeId;
IsSeriousId = isSeriousId;
SeriousnessId = seriousnessId;
ClassificationId = classificationId;
}
public string CreateAdverseEventReportResourceUri(ResourceUriType type,
int pageNumber,
int pageSize,
AdverseEventStratifyCriteria adverseEventStratifyCriteria)
{
switch (type)
{
case ResourceUriType.PreviousPage:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetAdverseEventReport",
new
{
pageNumber = pageNumber - 1,
pageSize = pageSize,
AdverseEventStratifyCriteria = adverseEventStratifyCriteria
}));
case ResourceUriType.NextPage:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetAdverseEventReport",
new
{
pageNumber = pageNumber + 1,
pageSize = pageSize,
AdverseEventStratifyCriteria = adverseEventStratifyCriteria
}));
case ResourceUriType.Current:
default:
return(_linkGenerator.GetPathByName(_accessor.HttpContext, "GetAdverseEventReport",
new
{
pageNumber = pageNumber,
pageSize = pageSize,
AdverseEventStratifyCriteria = adverseEventStratifyCriteria
}));
}
}
public ICollection <AdverseEventList> GetAdverseEventItems(DateTime searchFrom, DateTime searchTo, AdverseEventCriteria adverseEventCriteria, AdverseEventStratifyCriteria adverseEventStratifyCriteria)
{
string sql = "";
if (adverseEventCriteria == AdverseEventCriteria.ReportSource)
{
switch (adverseEventStratifyCriteria)
{
case AdverseEventStratifyCriteria.AgeGroup:
sql = string.Format(@"
SELECT mpce.SourceTerminologyMedDra AS 'Description',
CASE WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) < 5844 THEN '<16'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 5844 AND 9131 THEN 'Between 16 and 25'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 9132 AND 12784 THEN 'Between 26 and 35'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 12785 AND 16436 THEN 'Between 36 and 45'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 16437 AND 20089 THEN 'Between 46 and 55'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) > 20089 THEN '>55' END AS 'Criteria',
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY mpce.SourceTerminologyMedDra,
CASE WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) < 5844 THEN '<16'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 5844 AND 9131 THEN 'Between 16 and 25'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 9132 AND 12784 THEN 'Between 26 and 35'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 12785 AND 16436 THEN 'Between 36 and 45'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 16437 AND 20089 THEN 'Between 46 and 55'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) > 20089 THEN '>55' END
, [Istheadverseeventserious?]
ORDER BY mpce.SourceTerminologyMedDra asc, Criteria asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Facility:
sql = string.Format(@"
SELECT mpce.SourceTerminologyMedDra AS 'Description',
mpf.Facility AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN MetaPatientFacility mpf ON mp.Id = mpf.Patient_Id AND mpf.EnrolledDate = (SELECT MAX(EnrolledDate) FROM MetaPatientFacility impf WHERE impf.Patient_Id = mp.Id)
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY mpce.SourceTerminologyMedDra, mpf.Facility, [Istheadverseeventserious?]
ORDER BY mpce.SourceTerminologyMedDra asc, mpf.Facility asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Drug:
sql = string.Format(@"
SELECT mpce.SourceTerminologyMedDra AS 'Description',
mpm.Medication AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN ReportInstance ri ON ri.ContextGuid = mpce.PatientClinicalEventGuid
INNER JOIN ReportInstanceMedication rim ON ri.Id = rim.ReportInstance_Id
INNER JOIN MetaPatientMedication mpm ON rim.ReportInstanceMedicationGuid = mpm.PatientMedicationGuid
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY mpce.SourceTerminologyMedDra, mpm.Medication, [Istheadverseeventserious?]
ORDER BY mpce.SourceTerminologyMedDra asc, mpm.Medication asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Cohort:
sql = string.Format(@"
SELECT mpce.SourceTerminologyMedDra AS 'Description',
cg.CohortName AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN CohortGroupEnrolment cge ON mp.Id = cge.Patient_Id
INNER JOIN CohortGroup cg ON cge.CohortGroup_Id = cg.Id
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY mpce.SourceTerminologyMedDra, cg.CohortName, [Istheadverseeventserious?]
ORDER BY mpce.SourceTerminologyMedDra asc, cg.CohortName asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
default:
break;
}
}
else // MedDRA
{
switch (adverseEventStratifyCriteria)
{
case AdverseEventStratifyCriteria.AgeGroup:
sql = string.Format(@"
SELECT t.MedDraTerm AS 'Description',
CASE WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) < 5844 THEN '<16'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 5844 AND 9131 THEN 'Between 16 and 25'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 9132 AND 12784 THEN 'Between 26 and 35'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 12785 AND 16436 THEN 'Between 36 and 45'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 16437 AND 20089 THEN 'Between 46 and 55'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) > 20089 THEN '>55' END AS 'Criteria',
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN ReportInstance ri ON ri.ContextGuid = mpce.PatientClinicalEventGuid
INNER JOIN TerminologyMedDra t ON ri.TerminologyMedDra_Id = t.Id
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY t.MedDraTerm,
CASE WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) < 5844 THEN '<16'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 5844 AND 9131 THEN 'Between 16 and 25'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 9132 AND 12784 THEN 'Between 26 and 35'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 12785 AND 16436 THEN 'Between 36 and 45'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) BETWEEN 16437 AND 20089 THEN 'Between 46 and 55'
WHEN DATEDIFF(dd, mp.DateOfBirth, GETDATE()) > 20089 THEN '>55' END
, [Istheadverseeventserious?]
ORDER BY t.MedDraTerm asc, Criteria asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Facility:
sql = string.Format(@"
SELECT t.MedDraTerm AS 'Description',
mpf.Facility AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN ReportInstance ri ON ri.ContextGuid = mpce.PatientClinicalEventGuid
INNER JOIN TerminologyMedDra t ON ri.TerminologyMedDra_Id = t.Id
INNER JOIN MetaPatientFacility mpf ON mp.Id = mpf.Patient_Id AND mpf.EnrolledDate = (SELECT MAX(EnrolledDate) FROM MetaPatientFacility impf WHERE impf.Patient_Id = mp.Id)
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY t.MedDraTerm, mpf.Facility, [Istheadverseeventserious?]
ORDER BY t.MedDraTerm asc, mpf.Facility asc, [Istheadverseeventserious?] asc", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Drug:
sql = string.Format(@"
SELECT t.MedDraTerm AS 'Description',
mpm.Medication AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN ReportInstance ri ON ri.ContextGuid = mpce.PatientClinicalEventGuid
INNER JOIN TerminologyMedDra t ON ri.TerminologyMedDra_Id = t.Id
INNER JOIN ReportInstanceMedication rim ON ri.Id = rim.ReportInstance_Id
INNER JOIN MetaPatientMedication mpm ON rim.ReportInstanceMedicationGuid = mpm.PatientMedicationGuid
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY t.MedDraTerm, mpm.Medication, [Istheadverseeventserious?]
ORDER BY t.MedDraTerm asc, mpm.Medication asc, [Istheadverseeventserious?] asc", searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
case AdverseEventStratifyCriteria.Cohort:
sql = string.Format(@"
SELECT t.MedDraTerm AS 'Description',
cg.CohortName AS Criteria,
[Istheadverseeventserious?] AS 'Serious',
COUNT(*) AS PatientCount
FROM MetaPatientClinicalEvent mpce
INNER JOIN MetaPatient mp ON mpce.Patient_Id = mp.Id
INNER JOIN ReportInstance ri ON ri.ContextGuid = mpce.PatientClinicalEventGuid
INNER JOIN TerminologyMedDra t ON ri.TerminologyMedDra_Id = t.Id
INNER JOIN CohortGroupEnrolment cge ON mp.Id = cge.Patient_Id
INNER JOIN CohortGroup cg ON cge.CohortGroup_Id = cg.Id
WHERE mpce.OnsetDate BETWEEN '{0}' AND '{1}'
GROUP BY t.MedDraTerm, cg.CohortName, [Istheadverseeventserious?]
ORDER BY t.MedDraTerm asc, cg.CohortName asc, [Istheadverseeventserious?] asc"
, searchFrom.ToString("yyyy-MM-dd"), searchTo.ToString("yyyy-MM-dd"));
break;
default:
break;
}
}
SqlParameter[] parameters = new SqlParameter[0];
return(_unitOfWork.Repository <AdverseEventList>().ExecuteSql(sql, parameters));
}
