Specifications for training-related data feeds

Jim Repa. Last modified 10/15/2003

I. Information to be posted via HTML form variables:

The following data fields are transferred from ehsweb to traincasters in "real-time" after a user registers on ehsweb and clicks a button to proceed to Traincasters.

• Information available in the certificate:
  • Kerberos username
  • full name (mixed case)

• HTML form variables

  Variable name	Description
  last_name	user's last name (all uppercase)
  first_name	user's first name (all uppercase)
  middle_name	user's middle name (all uppercase)
  email	user's email address
  mit_id	user's 9-digit MIT ID number (999999999 means "none") Maybe we don't need this since username is unique.
  dlc	code for dept. in whose facility the user works (Could be blank. If there is more than one DLC, only one will be shown.)
  supervisor_pi	Kerberos username of the supervisor (Principal Investigator) in charge of the facility where the user works. (Could be blank. If there is more than one, only one will be shown.)
  facility	code for the lab_suite or other facility where the user works. (Could be blank. If there is more than one, only one will be shown.)
  course_code01	a course code the user must take (uncompleted or completed courses)
  course_code02	a course code the user must take (uncompleted or completed courses)
  ...
  course_code20	a course code the user must take (uncompleted or completed courses)
  checksum	In the future, we might want to add an algorithmically generated checksum on the above fields to make sure the user is not messing with certain fields (e.g., required course_codes). This is a possible future enhancement; let's not do anything with it now.

II. Files to be sent from MIT to Traincasters

Files will be tab-delimited and will be encrypted via PGP and sent via FTP.

1. dlc.text

   Field name	Description	Maximum length
   d_code	DLC code such as 'D_CHEM'	15
   description	Name of DLC	50

2. pi_supervisor.text

   Field name	Description	Maximum length
   username	Kerberos username of the PI/supervisor	8
   fullname	full name of supervisor (lastname followed by first name)	60

3. reg_person.text

   Field name	Description	Maximum length
   kerberos_username	user's Kerberos username (all uppercase)	8
   last_name	user's last name (all uppercase)	30
   first_name	user's first name (all uppercase)	30
   middle_name	user's middle name (all uppercase)	30
   email	user's email address	60
   mit_id	user's MIT ID number (999999999 means "none")	9
   dlc	D_xxxxx code for main department reported by the user	15
   supervisor_pi	Kerberos username of the main PI or supervisor reported by the user	8
   facility	code for the lab_suite or other facility where the user works. (We decided not to collect person to room-set information, so this field will be blank.)	15
   course_code01	a course code the user must take (uncompleted only)	15
   course_code02	a course code the user must take (uncompleted only)	15
   ...
   course_code20	a course code the user must take (uncompleted only)	15

   Programming notes for the feed program on EHSWEB:

   • A nightly feed program should be written, similar to the other feed programs that transfer data to Netcasters. The program will produce a text file, encrypt it with PGP, and transfer it with FTP to the traincasters server.

   • In the text file, include a record for each person who has completed the training needs assessment (i.e., the person has a record in train_registration) and who has either completed a web-based course or has an uncompleted course requirement that can be satisfied by a web-based course. These are people found in the following select statement
     

                select p.kerberos_name, p.first_name, p.last_name, etc.
                from train_registration t, person2 p
                where p.kerberos_name = t.kerberos_name
                and p.status_code = 'A'
                and p.kerberos_name in
                (select distinct pcc.kerberos_name
                 from tr_person_completed_course pcc, tr_course_option co1
                 where co1.course_option_id = pcc.course_option_id
                 and co1.course_option_type = 'W'
                 and pcc.kerberos_name = p.kerberos_name
                 union select distinct puc.kerberos_name
                 from tr_person_uncompleted_course puc, tr_course_option co2
                 where co2.course_option_id = puc.web_course_option_id
                 and co2.course_option_type = 'W'
                 and puc.kerberos_name = p.kerberos_name);
               

   • The DLC code for a person should come from the following select statement, which gives us the primary self-reported DLC for the person, the alphabetic first self-reported DLC if no primary DLC was reported, or the DLC from the person's directory entry (derived from tr_person_all_trigger) if there are no self-reported DLCs.

                select tsr.kerberos_name, d.dept_code
                 from train_self_report_dept tsr, dept d
                 where tsr.main_dept = 'Y'
                 and d.dept_id = tsr.dept_id
                union select tsr.kerberos_name, min(d.dept_code)    
                 from train_self_report_dept tsr, dept d
                 where d.dept_id = tsr.dept_id
                 and not exists 
                 (select tsr2.kerberos_name from train_self_report_dept tsr2
                  where tsr2.kerberos_name = tsr.kerberos_name
                  and tsr2.main_dept = 'Y')
                 group by tsr.kerberos_name
                union select pt.kerberos_name, min(pt.trigger_object_code)    
                 from train_registration t, tr_person_all_trigger pt
                 where pt.kerberos_name = t.kerberos_name
                 and pt.trigger_type_code = 'D'
                 and not exists 
                 (select tsr.kerberos_name from train_self_report_dept tsr
                  where tsr.kerberos_name = pt.kerberos_name);
               

   • The supervisor_pi field for a person should come from the following select statement, which gives us the primary self-reported PI for the person or the alphabetic first self-reported PI if no primary PI was reported.

                select tsr.kerberos_name, tsr.pi_kerberos_name
                 from train_self_report_pi tsr
                 where tsr.main_pi = 'Y'
                union select tsr.kerberos_name, min(tsr.pi_kerberos_name)    
                 from train_self_report_pi tsr
                 where not exists 
                 (select tsr2.kerberos_name from train_self_report_pi tsr2
                  where tsr2.kerberos_name = tsr.kerberos_name
                  and tsr2.main_pi = 'Y')
                 group by tsr.kerberos_name;
               

   • The course codes in the course_code__ fields should be taken from the external_course_code column in the tr_course_option table. Only fill in these fields for uncompleted courses for the person where there is a web course_option that has a non-null external_course_code field. The following select statement may be helpful.

                select puc.kerberos_name, co.external_course_code
                 from tr_person_uncompleted_course puc, tr_course_option co
                 where co.course_option_id = puc.web_course_option_id
                 and co.external_course_code is not null;
               

4. facility.text

   Field name	Description	Maximum length
   facility_code	unique code number for the lab or other facility	15
   facility_type	Description of type of facility (lab-suite, machine-shop, etc.)	50
   facility_name	Name or description of facility	50
   d_code	DLC code	15
   pi_username	Kerberos username of PI/supervisor	8
   room_list	Comma-delimited list of building-room numbers (e.g., '16-301,16-302,16-305')	200

5. course_req.text

   We might want to think about some of the fields in the next two files before finalizing them. What do we do about live courses in which a person is registered, if anything? How do we handle courses due to expire? Does MIT or does Traincasters determine when it is time to start reporting courses that are due to expire in a given number of days?

   Field name	Description	Maximum length
   username	Kerberos username of trainee	8
   course_code	course code for required course	10(?)
   needs_repeat	Will course need to be repeated? 'Y' or 'N'	1
   expiration_date	Date course will need to be repeated (mm/dd/yyyy)	10

6. course_complete.text

   Field name	Description	Maximum length
   username	Kerberos username of trainee	8
   course_code	course code for required course	10(?)
   completion_date	Date course was completed (mm/dd/yyyy)	10
   completion_notes	How was course completed? web, live, signature-of-supervisor, etc.	40

III. Files to be pulled by MIT from Traincasters

Files will be tab-delimited and will be encrypted via PGP and pulled via FTP.

1. course_complete.text

   Field name	Description	Maximum length
   username	Kerberos username of trainee	8
   course_code	course code for required course	10(?)
   completion_date	Date course was completed (mm/dd/yyyy)	10
   completion_notes	How was course completed? web, live, signature-of-supervisor, etc.	40

IV. Files to be transfered from SAP or K.C.'s training DB to ehsweb

Files be tab-delimited and will be encrypted via PGP and pulled via FTP. Fields should be variable-length.

1. live_course.text

   Field name	Description	Maximum length
   username	Kerberos username of trainee	8
   mitid	MIT ID of trainee (required if there is no Kerberos username)	9
   course_option_code	course option code, e.g., 100c, 111s, 260c, etc. (required field)	15
   course_section_code	course section code (not needed)	15
   course_option_type	how course is taken: L=live, W=web, O=other (required field)	1
   registered_date	Date user registered for course (mm/dd/yyyy)	10
   status	Status of trainee in course. R=registered, C=completed (required field)	1
   completion_date	Date course was completed (mm/dd/yyyy) - required for completed courses	10
   instructor_kerbname	Kerberos username of instructor (if known)	8
   instructor_name	Full name of instructor (first middle last) - fill this in if instructor name is known but there is no instructor Kerberos username	60
   completion_notes	Optional notes about completion of course or other option	40

   Notes:
   

   • It is essential to report completed courses, and for completed courses, the completion_date is a required field.
   • It is optional to report registered but still uncompleted courses.