CiviCRM GP/Practice Update Process

A proposed process to automatically import and update GP and GP Practice details.

Options

1. Import data from HSCIC web site.
2. Import the data from the UHL data warehouse.

1. HSCIC

This is the preferred method.

The data files are downloadable from the following page: ​http://systems.hscic.gov.uk/data/ods/datadownloads/gppractice.

New files are release quarterly, but update files are released monthly (​http://systems.hscic.gov.uk/data/ods/datadownloads/monthamend/index_html)

There is a 27 column 'standard' format for the data, as follows:

• epraccur.zip - GP practice current data *epraccur.csv
  • Fields:
    • Organisation code
    • Practice Name
    • National Grouping
    • High Level Health Authority
    • Address line 1
    • Address line 2
    • Address line 3
    • Address line 4
    • Address line 5
    • Postcode
    • Open date
    • Close date
    • Status (A = Active, C = Closed, D = Dormant, P = Proposed)
    • Sub-type code (B = Allocated to a parent organisation, Z = Not allocated to a parent organisation)
    • Parent Organisation code (CCG/PCT etc code)
    • Join parent date
    • Left parent date
    • Telephone number
    • Null
    • Null
    • Null
    • Amended record indicator
    • Null
    • Null
    • Null
    • Practice Type (0 = Other, 1 = WIC Practice, 2 = OOH Practice, 3 = WIC + OOH Practice, 4 = GP Practice, 5 = Prison prescribing cost centre)
    • Null

• ebranchs.zip - Branch surgery data *ebranchs.csv
  • Fields:
    • Organisation code (made up of the surgery code plus three digits - 001, 002, etc - to denote a branch surgery)
    • Branch surgery Name
    • National Grouping
    • High Level Health Authority
    • Address line 1
    • Address line 2
    • Address line 3
    • Address line 4
    • Address line 5
    • Postcode
    • Open date
    • Close date
    • Null
    • Null
    • Parent Organisation code (GP surgery code)
    • Join parent date
    • Left parent date
    • Telephone number
    • Null
    • Null
    • Null
    • Amended record indicator
    • Null
    • Government Office Region Code
    • Null
    • Null
    • Null

• egpcur.zip - GP current data *egpcur.csv
  • Fields:
    • G code
    • Name (surname space initials)
    • National Grouping
    • High Level Health Authority
    • Address line 1
    • Address line 2
    • Address line 3
    • Address line 4
    • Address line 5
    • Postcode
    • Open date
    • Close date
    • Status (A = Active, C = Closed, P = Proposed)
    • Sub-type code (P = Principal GP / Senior partner, O = Other GP)
    • Parent Organisation code (GP surgery code)
    • Join parent date
    • Left parent date
    • Telephone number
    • Null
    • Null
    • Null
    • Amended record indicator
    • Null
    • Current care organisation
    • Null
    • Null
    • Null

The monthly update file (egpam.zip -> egpam.csv) is an amalgamation of entries in both the above two formats into a single file for GP and GP practice data. Updated branch data is in ebranchsam.csv which is contained in eamendam.zip each month.

Process

Match each primary practice with a record in CiviCRM - update details, ensure 'main' address matches or is updated Match each branch surgery with an address in CiviCRM of type 'other' Match each GP to a health worker record in CiviCRM, ensure relationship links to correct GP Practice. Include senior partner / principal GP relationship.

How to deal with archive data? Do we care?

See ​https://api.drupal.org/api/drupal/modules%21system%21system.api.php/function/hook_cron/7 for details on using cron to schedule the work

Psuedocode

wget monthly GP + practice amendment file compare to last version held if the same, delete if different, unpack zip file process csv file

wget monthly branch amendment file compare to last version held if the same, delete if different, unpack zip file process csv file

2. UHL Data Warehouse

The data is stored in the DWREPO_BASE database. The tables are:

• MF_GP_OCS (GPs)
• MF_GP_PRACTICE_OCS (Practices)

There are other tables with similar names, but I don't know how they differ from the ones above.

It may be possible to use the SHA codes of the GPs to filter the details for the Leicestershire area.

These tables are recreated from scratch on a weekly basis, but the source from which they are created is the quarterly file above.