GGC Project - Home - CRM

Questions and information for us to get a better idea of the project scope and depth. Also summarizing all we know about the project so far:

Roles

There are 3 roles in the system. CSR, Auditor, Retrofit Manager.
Everyone is a contact with one or more of the above roles. We'll have a group to for each of the above roles (to create a PicList etc). We need information to send them email as various processes are triggered

CSR

The CSR is responsible for the following tasks:

Create and enter a new application.
Since we are collecting 40-50 fields, we might want to consider creating a more stremalined multi page application form for this.
- I think this is the preferred option and we'll need mockups for this set of screens
After an application is created, trigger a process that
- Creates an empty excel spreadsheet for the auditor. We can create the empty tables. We'll allow Paul to implement the Excel import/export code.
- Inform the auditor that he has a new application. Will this be done via email?

View and edit entered applications. (What are the possible states for an application?)
- New Application
- Auditor has logged on and "picked" up application
- Auditor has uploaded new file and updated the application

Auditor

Picks up a new application when an email is recieved from a CSR
Updates excel file and uploads them back into the system
- Who should know / care about this? (retrofit manager? any iterations between auditor and retrofit manager?)
The auditor interacts with the system ONLY via excel files. He also has a list of all the applications that he is responsible for and has not yet downloaded and/or uploaded the information
We will need a mockup for the auditor screens (Listing, import and export screen)

Retrofit Manager

Pick up a completed audit application
Periodically enters a set of dates for each phase of a retrofit (how many phases, and names of phases? is all the data for each phase the same? do we need to expose the fields the auditor has completed for each phase as view only?)
When all phases are complete, marks application as complete. The auditor is informed at this stages to "adjust" the application if needed
Does he need to know what stages his applications are in?
Will need mockups for how retrofit manager deals with applications and enters data for each phase

Database schema

Create a new table for "Applicant" for group 1 fields that are not name / address related. Name and Address are stored in the normal CiviCRM place. This will scale better and easier to manage than custom fields. From the preview site, Applicant has approx: 22 columns
We'll also add the 2 Group 4 fields to the Applicant table
There are 10 PM groups (w/out retrofit info). Each group has the exact same 12 fields. Two of the fields dont have a type (L_Oil, L_Propane). Would be good to know of any other constraints with regard to those fields (we encode them in xml in the schema def)
There are 12 XM groups with the exact same 12 fields as the PM group.
Each of the XM group has a retrofit group with 4 fields
whats the diff between PM and XM?
We could potentially have one table for all PM's / XM's, linked by Applicant ID (i.e. contact ID), along with a group ID and group Type (XM or PM). Other than initial creation, this table is written only by the IMPORT code
We could also have one table for all retrofit information linked to Applicant ID and above table ID
When we create a new applicant, we populate the above tables with the appropriate records. The Excel import/export tool needs to recognize and understand this format. We should have agree on the schema with regard to this at an early stage
There are 9 other Group 3 fields, which can potentially go in a misc table (to seperate all the view only fields for data integrity sake)
There is 1 additional Group 4 field (retrofit completed) which can potentially go in Applicant table

Misc Questions?

CustomOnlineDBForms.pdf. I have no idea what the fields in Page 2 are and how they correspond to the Database Fields. I suspect they are linked to the PM/XM fields, in which case the numbers actually have some names associated with it
Whats the mode of communication between CSR <~~> Auditor <~~> Retrofit manager. Should the system be sending emails? Do we need to record these emails?
How closely should we track status? We have a "task status" system in place (which we've used for quest/tmf) that we can reuse for fine grained task status (i.e. each phase of retrofit manager etc).