CM Processing - Tasks

Database Migration

Steps for migrating current (1.5) production DB to for 1.6 (trunk) use:
1. Create new DB - civicrm_quest - and load latest full dump from quest production backup.
2. Make sure civicrm trunk and quest trunk code-bases are update to date from svn.
3. From $ svn/trunk/bin >> ./setup.sh schema/QuestSchema.xml
4. From $ svn/quest/sql >> ./quest_dump.sh civicrm_quest civicrm password /home/not-backed-up/civicrm_tmp.sql
5. Create new copy of civicrm_quest and load with structure and fixed data:
mysqladmin -u civicrm -p<password> -f drop civicrm_quest;
mysqladmin -u civicrm -p<password> -f create civicrm_quest;
mysql -u civicrm -p<password> -f civicrm_quest < ~/svn/trunk/sql/civicrm_41.mysql;
mysql -u civicrm -p<password> -f civicrm_quest < ~/svn/trunk/sql/civicrm_data.mysql;
mysql -u civicrm -p<password> -f civicrm_quest < ~/svn/quest/sql/quest_data_cm.mysql;
mysql -u civicrm -p<password> -f civicrm_quest < ~/svn/quest/sql/quest_data_acl.mysql;

6. Load dump from production to the new civicrm_quest DB:
mysql -u civicrm -pMt!Everest -f civicrm_quest < civicrm_tmp.sql
7. Run quest/bin/computeIndex.php <path to civicrm.settings.php>
8. Run quest/bin/fixDupeRecords.php <path to civicrm.settings.php>

Partner Module

Spec Note: Partners will not have CiviCRM access. Links to downloads will be handled in Locker.

Quest Tasks

Create PDF templates for:

• *- college match
  • each partners supplement
  • counselor rec
  • teacher rec
  • transcripts and test results

CiviCRM Tasks

Extend the script for creating XML and PDF's:

XML Dumps for Oct 2 partner excel files....

• CRM-1212 (completed) Full dump script needs to handle the new schema and multiple applications (CollegeMatch, Recommendation, Partner) and Application Segments.
• CRM-1212 (completed) Implement Summary mode for the dump (export):
  • CiviCRM contact fields
    *-quest_student and quest_student_summary fields
    *-"Interest in College" - list of partner colleges which are either Ranked or selected for Forwarding (quest_partner_ranking.partner_id lookup to quest_partner.id WHERE ranking NOT NULL OR is_forward = 1)
    *-TOEFL score (from quest_test, test_id = 351)
  • Parent/Guardian's highest level of eduction - export two columns: relationship (e.g. Mother, Father...) and education level. Get highest_school_level_id, relationship_id FROM quest_person WHERE is_parent_guardian = 1 . Add joins to get relationship_type names and option_value labels.

For October 20 Partner PDF packages:

• PDF Outputs are in two phases
  • Create pdf's for each student (CM, separate files for each completed Partner Supplement, Teacher Rec, Counselor Rec)
  • Merge a copy of the applicable "pieces" for each student to Partner College directory tree.

Reader Module

Spec Notes:

• Assignment of Apps to Reader will probably be done by exporting, setting a reader ID value, re-importing (or via PHP script).
• Feedback input will probably use a new Batch Update screen (grid) driven by search results + profile.
• We'll store disposition (values=Finalist, Leaning Finalist, Leaning Rejected, Rejected, Not Rated) + keyword + comments in student_summary records.
  • Comments will include the following options
    • First Generation Student (radio button options)
      • Yes
      • No
      • Yes, but not in US
    • Likelyhood of parental income increasing (radio button options)
      • Very Likely
      • Somewhat
      • Unlikely
    • Summary of Need (radio button options)
      • Excellent
      • Good
      • Borderline
      • Disqualifying
    • Transcript (radio button options)
      • All A's
      • Few B's, but not during Jr year
      • Few B's
      • Low
    • Level of classes (radio button options)
      • Honors, AP, IB
      • Some Honors, AP, IB
      • Few or no Honors, AP, IB
      • Honors, AP, IB may not be offered
    • Test Scores (radio button options)
      • 1440+
      • 1300-1440
      • 1150-1300
      • Below 1150
    • Summary of Academics (radio button options)
      • Excellent
      • Good
      • Borderline
      • Disqualifying
• Tims feedback, as well as Round 1 and Round 2 rReader feedback will all be stored in same place.
• We'll log updates to student_summary record so we have a record of who did what/when.

Quest Tasks

Define search criteria needed for automated accept/reject decision by Tim

• Search will include the 'basic' contact information and the following fields from the quest_student and quest_student_summary tables:
  • ethnicity 1
  • parent grad college
  • gpa weighted
  • gpa unweighted
  • class rank
  • class rank %
  • fed lunch
  • SAT composite alt
  • ACT composite
  • income total
  • financial need index
  • academic performance index
• the following search fields are 'nice to have':
  • SAT critical reading
  • SAT math
  • household member count
  • class num of students

Define Feedback Keywords values

• The values are defined in the 'Reader Module' section

CiviCRM Tasks

CRM-1195 (completed) - Get search and export working with required fields in student and student_summary records.
CRM-1220 (completed) - Implement advanced search fields as listed above.

Design and implement permissioning scheme for Readers' Access to Assigned Applicants
Decide whether to implement Tokenized Smart Group access control in some form vs. simple ACL vs. sticking with current permissioning model and having 20 groups. Finalize Reader to Student Assignment process.

Extend student_summary with 3 feedback fields

CRM-1213 Design and implement Batch Update process for reader feedback input.

Applicant Indexes

CiviCRM Tasks

CRM-1218 (completed) - Create 2 new fields in student_summary and write command-line PHP script to calculate and populate the index columns.

Set Task Status for Uploaded/Faxed Docs

We've added a new API which should be called when Transcripts and SAT Results are received to set the appropriate task status. The unit test below shows how to use the API:

Composite Task Statuses

We have two CM tasks which are composites of other tasks, and need to be set via cron-job executed script.

• All Recommendations (task 9): Student has THREE complete recommendations submitted.
• CM Total Package (task 7): the following tasks are all completed...
  • CM Application (task 8)
  • Partner Supplement (task 19)
  • All Recommendations (task 9)
  • Transcript document submitted (task 11)
  • SAT results submitted (task 13)

The following script should be run periodically (once a day?). It will update the two composite task statuses for ALL students if they have completed the main CM App (task 8):

$ cd quest/bin/
$ php compositeTaskStatus.php <path to civicrm.settings.php file>