Changes between Initial Version and Version 1 of BRICCS CRLN Recruitment Report

Timestamp:

06/02/15 14:42:23 (9 years ago)

Author:

Richard Bramley

Comment:

--

BRICCS CRLN Recruitment Report

@@ +1 @@
+= BRICCS CRLN Recruitment Report
+
+Tags: [[BRICCS Study]] [[Reporting]] [[CRLN]]
+
+== Summary
+
+Report run at the start of each month summarizing all participants recruited into the [[BRICCS Study]] in the previous month.  Report does not include participants with a stus of {{{CANCELLED}}}.
+
+== Format
+
+Tab delimited files for Northampton, Leicester and External REDCap sites (i.e., 3 files), with the columns:
+
+- Study ID (BPt Number)
+- Site name
+- Date Recruited
+
+Ordered by date recruited.
+
+== Details
+
+ Recipient:: angel.christian@uhl-tr.nhs.uk
+ Server:: {{{uhlbriccsapp01.xuhl-tr.nhs.uk}}}
+ Working Directory:: {{{/home/nick/briccs_enrollment_reports/}}}
+ Archive Directory:: {{{V:\BRICCS data requests\BRICCS recruitment data for CLRN}}}
+ SQL Account:: Auditor
+
+== Procedure
+
+=== Create Participant Extracts
+
+1. Log onto the server and create the required extracts using the following MySql commands:
+
+==== Northampton
+
+{{{
+#!sh
+mysql -h briccsdb -u auditor -p briccs_northampton -e "SELECT barcode, site_no, start_date, status FROM participant ptone, interview WHERE NOT EXISTS (SELECT 1 FROM participant pttwo WHERE pttwo.enrollment_id = ptone.enrollment_id LIMIT 1, 1 ) AND barcode is not null AND status <> 'CANCELLED' AND start_date > '2014-01-01 00:00:00' AND start_date < '2014-02-01 00:00:00' AND ptone.id = interview.participant_id order by start_date;" > briccs_enrollment_northampton_2014_01.txt
+}}}
+
+**Change dates in query and file name for the current month.**
+
+==== Leicester
+
+{{{
+#!sh
+mysql -h briccsdb -u auditor -p redcap -e "SELECT pk,project_id,ts from redcap_log_event where event = 'INSERT' and object_type = 'redcap_data' and project_id = '24' and ts > '20141101000000' and ts < '20141201000000' order by ts;" > briccs_enrolment_leicester_2014_11.txt
+}}}
+
+**Change dates in query and file name for the current month.**
+
+==== External REDCap sites
+
+{{{
+#!sh
+mysql -h briccsdb -u auditor -p redcap_briccsext -e "SELECT pk,project_id,ts from redcap_log_event where event = 'INSERT' and object_type = 'redcap_data' and project_id <> '12' and ts > '20141101000000' and ts < '20141201000000' order by ts;" > briccs_enrolment_external_sites_2014_11.txt
+}}}
+
+**Change dates in query and file name for the current month.**
+
+[[ToDo]]: Reference the required projects, as opposed to excluding the not required ones.
+
+== Post Process Files
+
+1. Copy the files from the working directory to the archive directory.
+2. Open the file {{{datetime conversion file.ods}}} from the archive directory in Libre Office.
+3. Open the Leicester and REDCap external extract files from the archive directory in Libre Office.
+4. Replace the existing data in the {{{datetime conversion file.ods}}} spreadsheet with the data from each file in turn.  
+5. Copy the list of data spreadsheet creates containing the site name and formatted date back into the extract file and save.
+6. Email the file to the recipient 
+
+[[BackLinks]]