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@…

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

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

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

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

Error: Macro BackLinks(None) failed

'Environment' object has no attribute 'get_db_cnx'