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.

Old Details

Recipient

angel.christian@…

Server

UHLSQLBRICCSDB\UHLBRICCSDB

Database

i2b2_app03_b1_data

Procedure

LastMonthCrlnBriccsRecruitmentReport

Archive Directory

V:\BRICCS data requests\BRICCS recruitment data for CLRN

Procedure (if output format approved by Angel)

1. Log on to the database and run the stored procedure: EXEC LastMonthCrlnBriccsRecruitmentReport
2. The stored procedure produces the recruitment report for the previous month. So it must be run in the following month. I.e., for June 2015 recruits it must be run in July 2015.
3. Copy the output into a spreadsheet and save the spreadsheet in the archive directory with the name BRICCS_Enrollment_{year}{month}.xls. E.g., BRICCS_Enrollment_201506.xls
4. Send the report to Angel.

Old Details - Delete when next process approved

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

Old Procedure - Delete when next process approved

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'