openspecimen_demo Database

Parameters

Name	Type	Mode

Definition

begin
        
        insert into tmp_specimen_stats (
          specimen_id, 
          collection_time, collection_procedure,
          collection_container, collector_id,
          received_time,
          received_quality, receiver_id, 
          frozen_time, processing_time, ischemia_time,
          aliquot_seq
        )
      
        select
          c.identifier as specimen_id, 
          ce.event_timestamp as coll_time, ce.collection_procedure as coll_procedure,
          ce.container as collection_container, ce.user_id as collector_id,
          re.event_timestamp as received_time, 
          re.received_quality as received_quality, re.user_id as receiver_id, 
          fe.event_timestamp as frozen_time,
          TIMESTAMPDIFF(MINUTE, re.event_timestamp,fe.event_timestamp) as processing_time,
          TIMESTAMPDIFF(MINUTE, ce.event_timestamp, fe.event_timestamp) as ischemia_time,
          0
        from
          catissue_specimen s
          inner join catissue_coll_event_param ce on ce.specimen_id = s.identifier
          inner join catissue_received_event_param re on re.specimen_id = s.identifier
          left join (
            select
              fre.object_id as specimen_id, max(fep.event_timestamp) as event_timestamp
            from
              catissue_frozen_event_param fep
              inner join catissue_form_record_entry fre on fre.record_id = fep.identifier
              inner join catissue_form_context ffc on ffc.identifier = fre.form_ctxt_id
              inner join dyextn_containers ff on ff.identifier = ffc.container_id and ff.name = 'SpecimenFrozenEvent'
            group by
              fre.object_id  
          ) fe on fe.specimen_id = s.identifier
          inner join catissue_specimen_hierarchy h on h.ancestor_id = s.identifier
          inner join catissue_specimen c on c.identifier = h.descendent_id
        where
          s.parent_specimen_id is null and s.collection_status != 'Pending' and
          s.activity_status != 'Disabled' and c.activity_status != 'Disabled' and
          c.collection_status = 'Collected';

set @num := 0, @parent := 0;

update 
          tmp_specimen_stats stats
          join (
            select
              t.identifier, @num as counter
            from (
              select 
                identifier,parent_specimen_id
              from
                catissue_specimen 
              where 
                lineage = 'Aliquot' and activity_status = 'Active' and collection_status = 'Collected'
              order by 
                parent_specimen_id
              ) t
            where
              (@num := if(t.parent_specimen_id = @parent, @num + 1, 1)) is not null and 
              (@parent := t.parent_specimen_id) is not null
           ) ctr
        set
          stats.aliquot_seq = ctr.counter
        where
          stats.specimen_id = ctr.identifier;
        
      end

populate_specimen_stats

Parameters

Definition