openspecimen Database

Parameters

Name	Type	Mode
cp_list	text	IN

Definition

begin
        drop table if exists tmp_os_cpr_anticipated_spmns;

set @create_tmp_table = concat('create table
          tmp_os_cpr_anticipated_spmns (identifier bigint auto_increment primary key)
        as
          select
            r.identifier cpr_id,
            r.registration_date reg_date,
            e.code event_code,
            e.collection_point_label event_label,
            case
              when v.collection_status = 'Pending' and v.collection_timestamp is not null then v.collection_timestamp
              else date_add(r.registration_date, interval (e.offset - me.min_offset) day)
            end event_date,
            e.clinical_status_id,
            e.clinical_diagnosis_id,
            spmn.identifier req_id,
            spmn.spec_req_label req_name,
            spmn.code req_code,
            spmn.lineage,
            spmn.specimen_class_id,
            spmn.specimen_type_id,
            spmn.anatomic_site_id anatomic_site_id,
            spmn.laterality_id laterality_id,
            spmn.initial_quantity quantity,
            spmn.concentration,
            spmn.pathological_status_id path_status_id,
            spmn.collection_container_id,
            spmn.collection_procedure_id
          from
            catissue_coll_prot_reg r
            inner join (
              select
                ae.identifier, ae.code, ae.collection_point_label, ae.collection_protocol_id,
                ae.study_calendar_event_point, ae.event_point_unit, ae.activity_status,
                ae.clinical_status_id, ae.clinical_diagnosis_id,
                case
                  when ae.event_point_unit = 'YEARS' then ae.study_calendar_event_point * 365
                  when ae.event_point_unit = 'MONTHS' then ae.study_calendar_event_point * 30
                  when ae.event_point_unit = 'WEEKS' then ae.study_calendar_event_point * 7
                  else ae.study_calendar_event_point
                end as offset
              from
                catissue_coll_prot_event ae
              where
                ae.activity_status != 'Disabled'
            ) e on e.collection_protocol_id = r.collection_protocol_id
            inner join catissue_cp_req_specimen spmn on spmn.collection_protocol_event_id = e.identifier
            left join catissue_specimen_coll_group v
              on v.collection_protocol_event_id = e.identifier and
                v.collection_protocol_reg_id = r.identifier and
                v.activity_status != 'Disabled'
            left join (
              select
                reg.identifier as reg_id,
                min(
                  case
                    when cpe.event_point_unit = 'YEARS'  then cpe.study_calendar_event_point * 365
                    when cpe.event_point_unit = 'MONTHS' then cpe.study_calendar_event_point * 30
                    when cpe.event_point_unit = 'WEEKS'  then cpe.study_calendar_event_point * 7
                    else cpe.study_calendar_event_point
                  end
                ) as min_offset
              from
                catissue_coll_prot_reg reg
                inner join catissue_coll_prot_event cpe on cpe.collection_protocol_id = reg.collection_protocol_id
                left join catissue_specimen_coll_group v
                  on v.collection_protocol_reg_id = reg.identifier and
                    v.collection_protocol_event_id = cpe.identifier and
                    v.activity_status != 'Disabled'
              where
                ((cpe.activity_status = 'Active' and v.identifier is null) or
                 (v.identifier is not null)) and
                reg.activity_status = 'Active' and
                reg.collection_protocol_id in (', cp_list, ')
              group by
                reg.identifier
            ) me on me.reg_id = r.identifier
          where
            ((e.activity_status = 'Active' and v.identifier is null) or
             (v.collection_status = 'Pending')) and
             r.activity_status = 'Active' and
             r.collection_protocol_id in (', cp_list, ') and
             spmn.activity_status = 'Active'');

prepare create_sql from @create_tmp_table;
      execute create_sql;
      deallocate prepare create_sql;
      end

stage_anticipated_specimens

Parameters

Definition