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