TMF - Selection Phase Schema Changes, Search and Profile Features

This issue covers modifications need to allow TMF to proceed w/ filtering applicants for scholarship eligibility. Code changes and DB schema modifications will need to be integrated with the current production site (which is running CiviCRM 1.6). All (or most) code changes should affect only the TMF component code.

• • • All work hours for this project MUST be tracked and recorded on the wiki at:
      http://wiki.civicrm.org/confluence/display/CRM/Time+Tracking+for+TMF

• • • All work in the tmf repository should be done in http://svn.quest.civicrm.org/tmf/branches/v1.6

• • • Any changes to CiviCRM Core should be done on a separate branch called '1.6tmf'

Tasks
=====
1. Get a sandbox running with the current TMF + CiviCRM code, and loaded with a dump of the production site (CiviCRM and Drupal DB's). [dumps now posted to cividata repos in tmf directory]

2. DB modifications
2.1 Add fields and tables needed for applicant tracking and selection to the TMF schema:

• Additional tmf_student fields:
  applicant_status_id - INT - implied FK to civicrm_option_value where civicrm_option_group = 'applicant_status'
  interview_rank - INT
  reader_score_avg - NUMBER default NULL

• ReaderScore (new table)
  [you can clone StudentRanking.xml and modify as this is a similar table]

• id (PK)
• target_contact_id
• source_contact_id
• total_score INT default NULL
• academic_ability INT default NULL
• attitude INT default NULL
• integrity INT default NULL
• work_ethic INT default NULL
• accomplishment INT default NULL
• commitment INT default NULL
• community INT default NULL
• benefit INT default NULL
• leadership INT default NULL
• essence INT default NULL

2.2 Add option_group and option_value records to tmf_data.mysql for new fields which are lookups (.._id):

• Values for applicant_status :
  In Progress
  Submitted
  Ineligible
  Deleted / Rejected
  Eligible
  Reviewed
  Awarded
  Recipient

2.3 Create a DB upgrade script to add new tables, new fields and all fixed data records (2.2 and 2.3 above) to the existing production DB: tmf_upgrade_selection.mysql

• Upgrade script should also set applicant_status_id value to either "In Progress" or "Submitted" depending on whether the student has successfully completed the application or not. Check civicrm_task_status for task_id = 3. If status_id = 3 (Completed) - then set applicant_status_id field value to "Submitted".

3. TMF Component Search functionality
Follow the model used for Quest (I think most of the code is in Quest/CRM/BAO/Query.php) to add a "McConnell Foundation" search block to Advanced Search. This block allows searching on one or more of the following criteria (fields) from the TMF Student record:

• Scholarship Type : scholarship_type_id (2 checkboxes, one for each type)
• GPA : highschool_gpa_id (range search with From/To dropdown fields)
• Felony Conviction : is_convicted (radio yes/no + unselect jscript link)
• EFC, household_income_efc (range search with From/To text fields)
• High School Completion Status : highschool_years_id (drop-down)
• Applicant Status : applicant_status_id (drop-down) [new field]
• Average Reader Score : reader_score_avg (range search w/ From/To text fields)
• Interview Rank: interview_rank (range search w/ From/To dropdown fields) [new field]

We also need to include two "core" CiviCRM fields in Advanced Search:

• County : civicrm_address.county_id (drop-down). This could be added to the existing "Address Fields" block, or the McConnell block (whichever is easier).
• Date of Birth : civicrm_individual.birth_date (range search From / To w/ date pickers)

3a. Enable "Task" searching block in Advanced Search when TMF component is enabled. (This is the same as what we do for Quest).

4. Support selected TMF Fields in Profiles for Search Results and Batch Update
TMF will need to be able to include the following Student fields in profiles for use in Advanced Search "search results" (e.g. the profile-driven selector customization), as well as for profile-driven "Batch Update" via profile operations:

• Scholarship Type : scholarship_type_id
• GPA : highschool_gpa_id
• EFC, household_income_efc
• Applicant Status : applicant_status_id [new field]
• Average Reader Score : reader_score_avg [new field]
• Interview Rank: interview_rank [new field]

• County : civicrm_address.county_id (this may already be supported for profiles ??)
• Date of Birth : civicrm_individual.birth_date (already supported for profiles ??)

5. Include 2 additional action links in advanced search selector (when TMF component is enabled):

• "View App" - this links to the current contact/student's application in "preview" mode
• "Edit App" - links to current contact/student's application in "edit" mode

NOTE: Drupal permissions already have been defined in Core/Component for TMF to allow role(s) to be assigned access to edit and view students' applications. So you should be able to just assign these to "your" role. Other than making sure the permissioning is working to allow the TMF admins to access the apps - the apps should load from these links w/o errors. If there are problems, we'll need to address them

6. Update value for tmf_student.applicant_status_id to "Submitted" whenever the student successfully submits (or re-submits) a complete application. (We do this regardless of current value for applicant_status_id). You should be able to hook this into the existing function which is already setting the civicrm_task_status for task_id = 3 to Completed (status_id = 3).

7. Handle deletion of individual contact records
Currently, we aren't handling the deletion of related tmf_student and other tmf specific records when an admin tries to delete an individual contact who is a "student". Check for how this is handled for Quest and implement for TMF.