= 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@nihr.ac.uk
 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@nihr.ac.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

== 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

{{{
#!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]]