def get_query(self, req: CamcopsRequest) -> SelectBase:
which_idnum = req.get_int_param(ViewParam.WHICH_IDNUM, 1)
if which_idnum is None:
raise exc.HTTPBadRequest("{} not specified".format(
ViewParam.WHICH_IDNUM))
group_ids = req.user.ids_of_groups_user_may_report_on
# Step 1: link referral and patient
# noinspection PyUnresolvedReferences
p1 = Patient.__table__.alias("p1")
# noinspection PyUnresolvedReferences
i1 = PatientIdNum.__table__.alias("i1")
desc = req.get_id_shortdesc(which_idnum)
select_fields = [
CPFTLPSReferral.lps_division,
CPFTLPSReferral.referral_date_time,
CPFTLPSReferral.referral_priority,
p1.c.surname,
p1.c.forename,
p1.c.dob,
i1.c.idnum_value.label(desc),
CPFTLPSReferral.patient_location,
]
# noinspection PyUnresolvedReferences
select_from = p1.join(
CPFTLPSReferral.__table__,
and_(
p1.c._current == True,
CPFTLPSReferral.patient_id == p1.c.id,
CPFTLPSReferral._device_id == p1.c._device_id,
CPFTLPSReferral._era == p1.c._era,
CPFTLPSReferral._current == True,
)) # nopep8
select_from = select_from.join(i1,
and_(
i1.c.patient_id == p1.c.id,
i1.c._device_id == p1.c._device_id,
i1.c._era == p1.c._era,
i1.c._current == True,
)) # nopep8
wheres = [
i1.c.which_idnum == which_idnum,
]
if not req.user.superuser:
# Restrict to accessible groups
wheres.append(CPFTLPSReferral._group_id.in_(group_ids))
# Step 2: not yet discharged
# noinspection PyUnresolvedReferences
p2 = Patient.__table__.alias("p2")
# noinspection PyUnresolvedReferences
i2 = PatientIdNum.__table__.alias("i2")
# noinspection PyUnresolvedReferences
discharge = (
select(['*']).select_from(
p2.join(
CPFTLPSDischarge.__table__,
and_(
p2.c._current == True,
CPFTLPSDischarge.patient_id == p2.c.id,
CPFTLPSDischarge._device_id == p2.c._device_id,
CPFTLPSDischarge._era == p2.c._era,
CPFTLPSDischarge._current == True,
)).join(
i2,
and_(
i2.c.patient_id == p2.c.id,
i2.c._device_id == p2.c._device_id,
i2.c._era == p2.c._era,
i2.c._current == True,
))).where(
and_(
# Link on ID to main query: same patient
i2.c.which_idnum == which_idnum,
i2.c.idnum_value == i1.c.idnum_value,
# Discharge later than referral
(CPFTLPSDischarge.discharge_date >=
CPFTLPSReferral.referral_date_time),
))) # nopep8
if not req.user.superuser:
# Restrict to accessible groups
discharge = discharge.where(
CPFTLPSDischarge._group_id.in_(group_ids))
wheres.append(~exists(discharge))
# Step 3: not yet clerked
# noinspection PyUnresolvedReferences
p3 = Patient.__table__.alias("p3")
# noinspection PyUnresolvedReferences
i3 = PatientIdNum.__table__.alias("i3")
# noinspection PyUnresolvedReferences
clerking = (
select(['*']).select_from(
p3.join(
PsychiatricClerking.__table__,
and_(
p3.c._current == True,
PsychiatricClerking.patient_id == p3.c.id,
PsychiatricClerking._device_id == p3.c._device_id,
PsychiatricClerking._era == p3.c._era,
PsychiatricClerking._current == True,
)).join(
i3,
and_(
i3.c.patient_id == p3.c.id,
i3.c._device_id == p3.c._device_id,
i3.c._era == p3.c._era,
i3.c._current == True,
))).where(
and_(
# Link on ID to main query: same patient
i3.c.which_idnum == which_idnum,
i3.c.idnum_value == i1.c.idnum_value,
# Discharge later than referral
(PsychiatricClerking.when_created >=
CPFTLPSReferral.referral_date_time),
))) # nopep8
if not req.user.superuser:
# Restrict to accessible groups
clerking = clerking.where(
PsychiatricClerking._group_id.in_(group_ids))
wheres.append(~exists(clerking))
# Finish up
order_by = [
CPFTLPSReferral.lps_division,
CPFTLPSReferral.referral_date_time,
CPFTLPSReferral.referral_priority,
]
query = (select(select_fields).select_from(select_from).where(
and_(*wheres)).order_by(*order_by))
return query
def get_query(self, req: CamcopsRequest) -> SelectBase:
which_idnum = req.get_int_param(ViewParam.WHICH_IDNUM, 1)
if which_idnum is None:
raise exc.HTTPBadRequest(f"{ViewParam.WHICH_IDNUM} not specified")
group_ids = req.user.ids_of_groups_user_may_report_on
# Step 1: link referral and patient
p1 = Patient.__table__.alias("p1")
i1 = PatientIdNum.__table__.alias("i1")
desc = req.get_id_shortdesc(which_idnum)
select_fields = [
CPFTLPSReferral.lps_division,
CPFTLPSReferral.referral_date_time,
CPFTLPSReferral.referral_priority,
p1.c.surname,
p1.c.forename,
p1.c.dob,
i1.c.idnum_value.label(desc),
CPFTLPSReferral.patient_location,
]
select_from = p1.join(
CPFTLPSReferral.__table__,
and_(
p1.c._current == True, # noqa: E712
CPFTLPSReferral.patient_id == p1.c.id,
CPFTLPSReferral._device_id == p1.c._device_id,
CPFTLPSReferral._era == p1.c._era,
CPFTLPSReferral._current == True,
),
)
select_from = select_from.join(
i1,
and_(
i1.c.patient_id == p1.c.id,
i1.c._device_id == p1.c._device_id,
i1.c._era == p1.c._era,
i1.c._current == True, # noqa: E712
),
)
wheres = [i1.c.which_idnum == which_idnum]
if not req.user.superuser:
# Restrict to accessible groups
wheres.append(CPFTLPSReferral._group_id.in_(group_ids))
# Step 2: not yet discharged
p2 = Patient.__table__.alias("p2")
i2 = PatientIdNum.__table__.alias("i2")
discharge = (
select(["*"]).select_from(
p2.join(
CPFTLPSDischarge.__table__,
and_(
p2.c._current == True, # noqa: E712
CPFTLPSDischarge.patient_id == p2.c.id,
CPFTLPSDischarge._device_id == p2.c._device_id,
CPFTLPSDischarge._era == p2.c._era,
CPFTLPSDischarge._current == True,
),
).join(
i2,
and_(
i2.c.patient_id == p2.c.id,
i2.c._device_id == p2.c._device_id,
i2.c._era == p2.c._era,
i2.c._current == True,
),
)).where(
and_(
# Link on ID to main query: same patient
i2.c.which_idnum == which_idnum,
i2.c.idnum_value == i1.c.idnum_value,
# Discharge later than referral
(CPFTLPSDischarge.discharge_date >=
CPFTLPSReferral.referral_date_time),
))) # nopep8
if not req.user.superuser:
# Restrict to accessible groups
discharge = discharge.where(
CPFTLPSDischarge._group_id.in_(group_ids))
wheres.append(~exists(discharge))
# Finish up
order_by = [
CPFTLPSReferral.lps_division,
CPFTLPSReferral.referral_date_time,
CPFTLPSReferral.referral_priority,
]
query = (select(select_fields).select_from(select_from).where(
and_(*wheres)).order_by(*order_by))
return query