Preapplication Launch Specs

Phase 1 Milestones

4/3 - Beta
4/6 - Final Usability tests
4/14 - Site is feature complete and ready for release
4/15 - Site is live and accessed by end users

Quest

3/10 - App Spec / Schema Spec Frozen
3/10 - Paras completes all Registration involved tasks and moves to CiviCRM team
3/15 - Machines set up at Hosting Provider
3/17 - Drupal up and running with valid https Certificate at Hosting Provider

CiviCRM team

3/10 - First version of Student Forms
3/17 - First version of Application
3/17 - Final changes of Application and Schema from Quest
3/21 - Quest does internal testing of application flow
3/24 - Application complete and QA site up
3/24 - Quest Search integrated into CiviCRM
3/31 - Beta Site up and running
4/3 - External Security Audit of Code and Hosting Provider / Setup
4/3 - Export includes Quest specific data
4/15 - View screen implemented for Quest students

Phase 1 Scope

Quest will probably maintain most content on existing Questbridge site
Quest will handle creation of a Drupal theme that has Questbridge 'look and feel'.
Some content may be syndicated to Drupal site (TBD)
Applicants will be linked to Drupal site to register and complete pre-application.

Phase 1 - Key Features

• Applicant (user) registration (using existing Drupal functionality)

• • password sent via email
  • forgot password link (sends new email)
• Configured to core applicant demographics and pre-application response data
• Pre-application is a "Program" entity linked to a data-driven "Survey" entity (details below)
• Phase 1 Roles:
  • Applicant - access to Locker (personal dashboard) and open application(s)
  • Administrator - read/write access to all contact and application data (see below for details)

Applicant Interace

• Locker = Drupal content + user task status + links to begin/continue application(s)
• Pre-application wizard - tracks progress and retains last saved step (page)
• Submit task with application inspector

Admin Interface

• Find a student.
  • Students Searchable by / and display uses Default List View fields from spec (configurable selector columns)
• Contact View as specified in Default Detail View in spec
• Access application (read/write) - replace Submit 'action' with Application Inspector 'action' (so Admin can see what's not yet completed for a given application)
• Export all applicant and application data as csv - EXCEPT the Essay field (the export is for quantitative analysis)

NOTE: We might look at integrating the Group = Role code from Rich Orris

Pre-application requirements

• app has N sections
• each section can have 1 to n pages, save and continue 1 page at a time
• must be able to save screen w/o completing all required fields
• we need to handle (encode) conditional fld requirement rules (e.g. if SAT_1 Score entered, then SAT_1 Date is required)
• need to decide how to deal w/ iterative data sets (e.g. 1 to N guardians, 1 to N siblings). Henry will determine how big N can be for these.
• need javascript to warn if leave a form when any field has been modified and not saved (build into framework)
• no branching needed for now but will be later
• remembers last screen i changed - give them start links to beginning OR to 'continue' on next page after last save if in progress
• application inspector triggered by student SUBMIT PRE-APP
  • any missing required fields displayed in context of section and page titles - so they can progress through the app seeing only pages w/ missing required fields (highlighted)
  • then SUBMIT sets student->program->task->status to completed

NOTE: all program app elements should point to a shared pool of data fields, so when the next program app is built, an already populated field has it's value brought forward

Schema for Pre-application Phase (draft)

This section is divided into 2 parts. Part 1 is schema for base student forms/info (3/10 deliverable). Part 2 is schema for household / related persons info (TBD based on Quest mock-up modifications) as well as new core tables for Project and Task info.

Questions for Quest staff are in red.
Required CiviCRM core code enhancements, and CiviCRM questions are in blue.

Configurable Field Options (civicrm_select_value, civicrm_select_value_option)

Two new CiviCRM core tables will be created to store configurable field (enum) label / value pairs. These will be used for all the new Quest-specific DB enum fields (e.g. quest_student.ethnicity_id). We will also migrate existing core ENUM tables (e.g. civicrm_gender) to this data-store.

Option Groups (civicrm_option_group)

civicrm_option_group is a meta table of field option "sets" which is used to retrieve valid options for one or more fields. By convention, civicrm_option_group.name will be the "owner" column name with the "_id" (and anything after) removed. For migrated enums (like gender, prefix, suffix...) we will use the full name of the deprecated table.

Examples:
For civicrm_individual.gender_id : civicrm_option_group.name = civicrm_gender
For quest_student.home_area_id : civicrm_option_group.name = home_area
For quest_student.ethnicity_id_1 : civicrm_option_group.name = ethnicity

• id
• domain_id
• name (VARCHAR, NOT NULL. This is the handle the class properties will use to retrieve the joined select_value_option rows.)
• description (VARCHAR (255))
• is_active

Option Values (civicrm_option_value)

The actual option strings. Classes using configurable "ENUMS" will store Foreign Keys to rows in this table.

Example:
quest_student.home_area_id is an FK to civicrm_option_value.id

• id
• option_group_id (FK to civicrm_option_group)
• title (VARCHAR, NOT NULL. This is the string displayed to users - e.g. the "label" in an HTML option tag.)
• name (VARCHAR, NULL. Gives us the ability to store an option value that is different from the "title". One use case is when a non-tranlsated value needs to be set / sent to another application.)
• is_default
• description (VARCHAR (255), NULL)
• weight
• is_reserved
• is_active
• group (VARCHAR. To highlight and/or group options within a set. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners)
• is_optgroup (BOOLEAN, Is this option just a display header? Expected usage is to render these as <OPTGROUP> tags within a <SELECT> field list of options).

Part 1 Quest Schema (Student, High School, Honors, Testing, Essay)

Individual (civicrm_contact and civicrm_individual)

Use existing core class/data fields.

• First, Middle and Last Name
• Suffix
• Nickname
• Gender
• DOB
• Preferred (primary) Email

Address (civicrm_address)

Two location types - Permanent and Mailing. Use existing core class/data fields.

• Street address (this will include apartment # per our data model)
• Supplemental address
• City, etc.

Phone (civicrm_phone)

Add phone type - Alternate. Use existing core class/data fields.
?? Does Quest care about Mobile vs. Landline.

Student (quest_student)

New Quest schema class/table which extends Individual. All fields in this table should be exportable/importable. Encompasses Personal Information, Educational Interests, Academic Information, Test Information (summary). Values for columns marked DB Enum will be configurable (stored as rows in select_values table, see below).

• contact_id - foreign key to civicrm_contact table
• citizenship_status_id (DB enum)
• citizenship_country_id (uses civicrm_country selector)
• ethnicity_1_id (DB enum)
• ethnicity_2 _id (DB enum)
• home_area_id (DB enum - Rural, Suburban, etc.)
• growup_country_id (uses civicrm_country selector)
• nationality_country_id (uses civicrm_country selector)
• years_live_us (INT)
• first_language (VARCHAR)
• primary_language (VARCHAR)
• is_partner_share (BOOLEAN, ok to share info with partner schools)
• high_school_grad_year
• parent_grad_college_id (DB enum - Yes, No, Don't Know)
• internet_access_id (DB enum)
• internet_acces_other (VARCHAR)
• is_home_computer (BOOLEAN)
• is_home_internet (BOOLEAN)
• is_take_SAT_ACT (boolean)
• study_method_id (DB enum )
• educational_interest (VARCHAR. Which educational areas are they interested in. Multi-select checkbox array populates a list of ID values from civicrm_option_value where civicrm_option_group.name = educational_interest.)
• educational_interest_other
• college_type (DB Enum)
• college_interest (VARCHAR. Which colleges are they interested in. Multi-select checkbox array populates a list of ID values from civicrm_option_value where civicrm_option_group.name = college_interest.)
• college_interest_other (MEMO)
• GPA_id (DB Enum)
• class_rank
• class_rank_percent_id (DB Enum)
• class_num_students
• GPA_explanation (MEMO)
• score_SAT (INT. Combined highest reported score.)
• score_PSAT (INT. Combined highest reported score.)
• score_ACT (INT. Combined highest reported score.)
• test_tutoring (VARCHAR. Which tests have they received tutoring for. Multi-select checkbox array populates a list of titles from civicrm_select_value_option where civicrm_select_value.name = test.)
• household_income_total (MONEY)
• fed_lunch_id (DB Enum)

High School Info (civicrm_organization, civicrm_address, civicrm_phone, civicrm_relationship)

Each High School entered in an application will be inserted as a civicrm_organization record. Address and phone info will be stored in standard CiviCRM records/fields.

Organization will be extended with these custom fields (using available CiviCRM custom field configuration):

• CEEB Code
• Institution Type (alphanumeric radio, values = Secondary School, Other Program, College, ...)
• School Type (alphanumeric radio, values = Public, Private, Parochial)
• Number of Students (int)

We will duplicate-match school input based on School (organization) Name + State + CEEB Code. If all three match, link applicant to existing organization record, else create a new one.
How should we reconcile / handle property differences for matched schools? EXAMPLE: Applicant submits High School info page and we match on our 3 criteria. However, values for # or students and phone number differ from existing entry.

Students are linked to one or more secondary schools (or other educational institutions/programs) with Relationship records (civicrm_relationship).

• relationship_type (configure these types: Current Secondary School, Other School Program)
• start date
• end date

Extend CiviCRM core to support extending relationship class with custom fields . This might not be a Phase 1 requirement as the only candidate for this is Program Description (see below).

• Program Description (MEMO)
  NOTE: Program Description sounds like organization info, but really will be specific to the student's relationship w/ particular programs.

Academic Honors (quest_student_honor)

• contact_id - foreign key to civicrm_contact table
• honor_title
• honor_date

Test Results (quest_student_test)

• contact_id - foreign key to civicrm_contact table
• test_id (DB Enum - e.g. SAT, PSAT, ....)
• test_date
• score_composite
• score_english
• score_reading
• score_writing
• score_math
• score_science
• subject (VARCHAR - for SAT II and AP only)

Essay (quest_student_essay)

• contact_id - foreign key to civicrm_contact table
• essay (MEMO)
  We will add a foreign key property to this table in the next phase so we can differentiate essay responses to different questions.

Part 2 Schema (Household, Related Persons, Income Details...Project and Task)

Household (UI will support up to 2 household records per student)

• contact_id (FK to Student via Contact)
• household_type (ENUM, "Current, Previous")
• member_count
• person_1_id (FK to Quest Person table)
• person_2_id
• years_lived_id
• description (MEMO. Additional info about household situation.)

Person (related individuals)

• contact_id (FK to Student via Contact)
• relationship_id (DB Enum, e.g. Mother, Father, Sister... Use option_value.grouping field values (parent, sibling) to differentiate which options are included in which page/drop-down.)
• is_parent_guardian (Boolean)
• first_name
• last_name
• marital_status_id (DB Enum)
• separated_year (Date)
• deceased_id (DB Enum)
• deceased_year (Date)
• age (Int)
• lived_with_period_id (DB Enum)
• lived_with_from_age (INT)
• lived_with_to_age (INT)
• industry_id
• job_organization
• job_occupation
• job_current_years
• highest_school_level_id (DB Enum - used on Parent pages)
• current_school_level_id (DB Enum - used on Sibling pages)
• college_name
• college_country_id
• college_grad_year
• college_major
• prof_school_name
• prof_school_degree_id
• prof_grad_year
• description (MEMO. Additional circumstances regarding person (parent / guardian or sibling))

Income

• person_id (FK to Quest Person)
• source_1_id (DB Enum)
• job_1
• amount_1 (MONEY. Total income for prior complete year)
• source_2_id
• job_2
• amount_2

Project

Defines a Quest program (e.g. Pre-Application, College Match, Scholarship...). Projects have tasks.
NOTE: This object may be generalizable to Programs, Campaigns, etc. for other use cases.

• title
• logo
• intro content
• start date
• end date

Task Type

Configurable enumeration of types of tasks - e.g. Survey, Document Submission, Reference/Recommendation etc.

Project Tasks

What 'tasks' must be completed for a program under what condition and when.

• title (e.g. Submit pre-application, Fax high-school transcript, etc.)
• project id
• task type id (FKey to task type. Do we need this meta-classification of tasks?)
• task entity type and id (use this to link task to a survey instance or other entity+entity_id)
• condition (prerequisite task id + status ? vs. date-driven vs. ?)
• start and end date
• program status (enum)

Applicant Task Status

• contact id
• task id
• status (enum)
• status details (integer to store sub-task statuses for programmatic evaluation of things like percent complete)
• status message (for display)
• closed: flag to indicate artifacts for this task are now read-only conditional on permissions (This might just be another status enum value.)

Implementation Notes / Civicrm Task Notes

Data-store for Quest Applicant Data (contact data extension)

• Extended data stored in set of Quest-specific tables (not in civicrm_custom_data)
• Tables/columns defined using our XML format. Stored in xml/schema/quest folder.
  • Leverage existing automated SQL and DAO generation

Application Wizard

• For Phase 1, the application wizard will be coded in a set of PHP file(s) - using our existing framework/Quickform logic to define fields and forms (sections). Form elements will reference core or Quest schema data elements. Field, form and wizard-level validation rules will be encoded in these file(s).

• We will add wizard-level field validation (in addition to field-level and form-level validation currently available in our framework). This will be used to define any requirement/validation logic that is enforced on "application submit" event.

Configurable Search Result Listings (List View of Applicants)

• The Profile "selector" configuration model will be extended so it can be applied to our built-in search results listings. This will allow Quest to include any (or most) core and Quest applicant fields as listing columns.
• Must be able to inject section w/ Quest data fields into Advanced Search, Search Query, and Export (so these fields are "searchable" and "exportable").

Configurable Contact (Detail) View Screen

• We will create a mechanism that allows "swapping in" of customized templates (and hence layouts) for the Contact (Detail) View screen. A Quest Student template will be developed which matches the specified "Detail View" elements.

Projects and Tasks - Minimal Implementation

• Quest Pre-application Program will be instantiated as Project in a new core table. Administrators will be able to edit start date, end date, logo... and we will probably provide API calls allowing these project properties to be exposed in CMS (Drupal) blocks as needed by Quest.
• Submit Pre-application will be instantiated as a Task for the pre-app program. A Task Status object will be created for each applicant who begins this task. Task status values will be updated on the following events:
  • first app wizard form saved: status=In Progress
  • application submitted: status=Submitted
  • application

PDF Generation