College Match - Merge Duplicate High School Records in Preapplication Data

Background
=========
During the Preapplication Cycle, we did not attempt to dedupe the High School records that students entered. Hence, the final DB from that cycle contains many duplicate High School (civicrm_contact/civicrm_organization) records. We now have a master reference DB of schools which we can use to dedupe and match school based on a CEEB (school) code. However, some schools are not in the CEEB reference database.

Quest has updated the final CiviCRM DB from the Preapp cycle - populating the correct CEEB codes for each listed school. The task for this issue is to dedupe and merge any school records which have a CEEB code into a single contact organization, AND correct the school data - school name, address, school type (custom_2) to match the info in the CEEB database.. We will also migrate the school type (custom_2) data values for schools w/o CEEB codes to match the new mappings.

This operation should be coded as a PHP script which can be run from the command line. It should be commited to the quest repository. http://svn.quest.civicrm.org/quest/trunk/bin

Implementation
============
1. Merge and correct data for schools with CEEB codes

1.1 Source the updated Preapp DB from cividata/matchapp/quest_db.sql.bz2 (u need to source quest_data_cm.sql and civicrm_data.mysql before doing this)

1.2 Get a list of all unique CEEB code values (select UNIQUE(char_data) from civicrm_custom_value WHERE custom_field_id=1)
.
1.3 For each unique CEEB code value:

• Find the civicrm_contact record with the lowest ID - this contact organization (school) will be preserved
• Retrieve the corresponding quest_ceeb record (quest_ceeb.code MATCHES the contact's custom_1 value)
• Update the following data values to match the CEEB data:
  -+ set civicrm_custom_value for custom_field 2 (school type) = quest_ceeb.school_type
  -+ set organization_name = quest_ceeb.school_name
  -+ set location 1 street_address, city, state_province to match corresponding quest_ceeb table values
• For each of the other contact (organization) records which have this CEEB code
  -+ update their civicrm_relationship records - replacing their contact ID with the ID of the school instance we're keeping
  -+ use the delete contact API to delete this duplication school and it's child records

2. Fix the school type field value (custom_2) for schools which don't have CEEB codes

• Find all civicrm_organization records which do NOT have a CEEB code (e.g. no corresponding civicrm_custom_value record) AND which do have a school type (custom_2)
• For these schools, update (migrate) the civicrm_custom_value.char_data field per these mappings (old value -> new value):

1. 'Public' (310) maps to 'public' (A)
2. 'Private' (311) maps to 'independent, not religious' (B)
3. 'Parochial' (312) maps to 'other independent, religious' (D)