Make annual census workbook fully generic [2022:HPR]

[Ghislain Hachey]

There should be a single workbook that can be used in any country through configuration.

Some of the remaining tasks:

• Push all remaining dropdown to the database
• Make all default terminologies of the workbook columns based on the EMIS internal defaults. Currently the defaults are based on FSM where the workbook originated. So the default is State when it should be District.
• Adjust all census workbook mappings accordingly
• An empty fresh copy of the workbook should be downloadable (much like we can download one with the rollover)

‌

[Ghislain Hachey]

Some relevant comments from @Brian Lewis on the workings of the mapping.

It controls how the data is moved into the database, the mapping is the field name expected in the SQL.

So the internal name expected in the XML is e.g. FSM_SSN. this ends up in TPayroll on TeacherIdentity.

So if the column header is actually RMI_SSN, the the mapping translates that to FSM_SSN, So when it gets to the upload, it knows what to do with that column.

Its a slightly confusing example becuase the internal name is FSM_SSN, not just SSN or indeed PAYROLL which would be cleaner - we mentioned this yesterday.

So the mappings are critical in the Load from the workbook as described, but also going the other way in the Rollover.

ie to populate the colmn RMI SSN; it has to know that the data actually comes from tPayroll, and goes into a column called FSM_SSN in the recordset that is merged onto the sheet. The column names on the sheet are translated and matched against the column names in the recrdsset.

‌

[Brian Lewis]

task(census): New loookup table for Organizations; to use in place of hardcoded list in Census workbook

Resolves #1137

→ <<cset 1f29639fdeef>>

[Brian Lewis]

work in progress by successive commits logged to this issue

[Brian Lewis]

feat(censusworkbook): Validate Job Title using RoleGrades, use mappings to set Column titles

Resolves #1137

→ <<cset 31b6e415f1b7>>

[Brian Lewis]

work in progress

[Brian Lewis]

Job Titles list in Generic Workbook

Job titles are currently validated from hardcoded lists in the workbook, and this list in FSM is dependent on the State.

In generic workbook:
-- these state level lists will be aggregated into a single list;
-- this list will be provided from the table RoleGrades
-- existing data will be cleaned up and matched to RoleGrades, so that the existing Job Title can be mapped to a valid entry going forward
-- the teacher role (held in tchRole) will be derived from the RoleGrade.

Note that there should be a one-to-many relation between lkpTeacherRole and RoleGrades. e.g. Classroom Teacher is a Role, ClassroomTeacher III is a RoleGrade.
To clean this up:
-- RoleGrades will be assigned to the correct role;
-- roles that should be RoleGrades will be removed from TeacherRole.

‌

[Brian Lewis]

task(census): Move field of study list to table, allow this table to be edited

Resolves #1137

→ <<cset a504554cb16c>>

[Brian Lewis]

For editing local data, use this script to populate lkpTeacherFieldOfStudy:

‌

INSERT INTO lkpTeacherFieldOfStudy
(codeCode, codeDescription)
VALUES
('Accounting','Accounting'),
('Accounting Technology','Accounting Technology'),
('Accounting/Merchandising/Liberal Arts','Accounting/Merchandising/Liberal Arts'),
('Administration of Justice','Administration of Justice'),
('Agricultural Science','Agricultural Science'),
('Agriculture','Agriculture'),
('Agriculture and Food Technology','Agriculture and Food Technology'),
('Agriculture and Natural Resource Management','Agriculture and Natural Resource Management'),
('Anthropology','Anthropology'),
('Applied Science','Applied Science'),
('Applied Zoology','Applied Zoology'),
('Art','Art'),
('Arts','Arts'),
('Arts and Sciences','Arts and Sciences'),
('ASL','ASL'),
('Automotive Mechanics Technology','Automotive Mechanics Technology'),
('Behavioral Science','Behavioral Science'),
('Biblical Studies','Biblical Studies'),
('Biology','Biology'),
('Bookkeeper','Bookkeeper'),
('Building Technology','Building Technology'),
('Building Technology (Construction Electricity)','Building Technology (Construction Electricity)'),
('Business Accounting Clerk','Business Accounting Clerk'),
('Business Administration','Business Administration'),
('Business Education-Secretarial Science','Business Education-Secretarial Science'),
('Business Management','Business Management'),
('Business Studies','Business Studies'),
('Business/Mechanical Engineering','Business/Mechanical Engineering'),
('Busness Administration','Busness Administration'),
('Certificate in Education','Certificate in Education'),
('Chemistry','Chemistry'),
('Christian School Education','Christian School Education'),
('Church Ministries','Church Ministries'),
('Church Ministry','Church Ministry'),
('Commerce','Commerce'),
('Communication','Communication'),
('Communication Disorders','Communication Disorders'),
('Community Service','Community Service'),
('Computer Engineering','Computer Engineering'),
('Computer Information Systems','Computer Information Systems'),
('Computer Science','Computer Science'),
('Construction Carpentry Technology','Construction Carpentry Technology'),
('Construction Tecnology','Construction Tecnology'),
('Criminal Justice','Criminal Justice'),
('Deaf Studies','Deaf Studies'),
('Designer in Art & Culture','Designer in Art & Culture'),
('Divinity','Divinity'),
('Early Childhood Education','Early Childhood Education'),
('Economics','Economics'),
('Ecology','Ecology'),
('Ecotourism and Adventure Travel','Ecotourism and Adventure Travel'),
('Education','Education'),
('Education Administration','Education Administration'),
('Education Elementary','Education Elementary'),
('Education Secondary','Education Secondary'),
('Education in Reading','Education in Reading'),
('Education-Curriculum Studies','Education-Curriculum Studies'),
('Education-Early Childhood','Education-Early Childhood'),
('Education Vocational','Education Vocational'),
('Electrical Technology','Electrical Technology'),
('Electronic Technology','Electronic Technology'),
('Elementary Education/3rd Year Certification','Elementary Education/3rd Year Certification'),
('Elementary Teacher Education','Elementary Teacher Education'),
('Engineering','Engineering'),
('English','English'),
('English & Philosophy','English & Philosophy'),
('English Writing & Literature','English Writing & Literature'),
('Entomology','Entomology'),
('Environmental Studies','Environmental Studies'),
('Farm Tractor Mechanic','Farm Tractor Mechanic'),
('Foreign Service','Foreign Service'),
('General Agriculture','General Agriculture'),
('General Business','General Business'),
('General Education','General Education'),
('General Studies','General Studies'),
('Global Health','Global Health'),
('Graduate Studies','Graduate Studies'),
('Health Career Opportunities Program','Health Career Opportunities Program'),
('Health Careers','Health Careers'),
('Heavy Equipment Maintenance & Repair','Heavy Equipment Maintenance & Repair'),
('History','History'),
('Hotel and Restaurant Management','Hotel and Restaurant Management'),
('HR Management','HR Management'),
('Humanities','Humanities'),
('Information Technology','Information Technology'),
('In-Service Elementary Education','In-Service Elementary Education'),
('In-Service Special Education','In-Service Special Education'),
('International Business','International Business'),
('International Business Management','International Business Management'),
('International Relations','International Relations'),
('International Relations/International Business','International Relations/International Business'),
('Interpreter for the Deaf','Interpreter for the Deaf'),
('Island Health','Island Health'),
('Justice Administration','Justice Administration'),
('Language Arts','Language Arts'),
('LA & Sciences (Interdisciplinary Studies)','LA & Sciences (Interdisciplinary Studies)'),
('Law','Law'),
('Liberal Arts','Liberal Arts'),
('Liberal Arts and Science','Liberal Arts and Science'),
('Liberal Arts and Sciences','Liberal Arts and Sciences'),
('Liberal Arts and Sciences Interdisciplinary Studies','Liberal Arts and Sciences Interdisciplinary Studies'),
('Liberal Arts in Education','Liberal Arts in Education'),
('Liberal Arts/Education','Liberal Arts/Education'),
('Liberal Arts/HCOP','Liberal Arts/HCOP'),
('Liberal Arts/Health Careers Opportunity Program','Liberal Arts/Health Careers Opportunity Program'),
('Liberal Arts/Media Studies','Liberal Arts/Media Studies'),
('Liberal ArtsPre-Nursing','Liberal ArtsPre-Nursing'),
('Liberal Studies','Liberal Studies'),
('Liberal Studies(Pre-Law)','Liberal Studies(Pre-Law)'),
('Library and Information Services','Library and Information Services'),
('Management','Management'),
('Marine Science','Marine Science'),
('Marketing','Marketing'),
('Marketting & Management','Marketting & Management'),
('Mathematics','Mathematics'),
('Micronesian Studies','Micronesian Studies'),
('Micronesian Studies/Teacher Preparation','Micronesian Studies/Teacher Preparation'),
('Nursing','Nursing'),
('Nursing/Secondary Education','Nursing/Secondary Education'),
('Office Administration','Office Administration'),
('Office Technology','Office Technology'),
('Organizaitonal Leadership','Organizaitonal Leadership'),
('Other','Other'),
('Pastoral Ministries','Pastoral Ministries'),
('Pedagogy','Pedagogy'),
('Philosophy','Philosophy'),
('Physical Education','Physical Education'),
('Political Science','Political Science'),
('Pre-Teacher Preparation','Pre-Teacher Preparation'),
('Professional and Technical Writing','Professional and Technical Writing'),
('Psychology','Psychology'),
('Psychology/Fisheries & Education','Psychology/Fisheries & Education'),
('Public Administration','Public Administration'),
('Reading','Reading'),
('Reading Mastery','Reading Mastery'),
('Recreational Leadership','Recreational Leadership'),
('Related Services Assistant','Related Services Assistant'),
('Religion','Religion'),
('Religious Education','Religious Education'),
('Religious Studies','Religious Studies'),
('Religiuos Instruction','Religiuos Instruction'),
('School of Education','School of Education'),
('School of International and Intercultural Studies','School of International and Intercultural Studies'),
('Science','Science'),
('Secondary Education','Secondary Education'),
('Secondary Vocational Teacher Education','Secondary Vocational Teacher Education'),
('SecondaryEducation(Home Economics)','SecondaryEducation(Home Economics)'),
('Secretarial','Secretarial'),
('Service Elementary Education','Service Elementary Education'),
('Small Engine & Outboard Marine Technology','Small Engine & Outboard Marine Technology'),
('Social Science','Social Science'),
('Social Sciences','Social Sciences'),
('Social Work','Social Work'),
('Social Work/Religion','Social Work/Religion'),
('Sociology','Sociology'),
('Special Education','Special Education'),
('Teacher Education','Teacher Education'),
('Teacher Education - Elementary','Teacher Education - Elementary'),
('Teaching English to Speakers of Other Languages (TESOL)','Teaching English to Speakers of Other Languages (TESOL)'),
('Teacher Prep','Teacher Prep'),
('Teacher Preparation','Teacher Preparation'),
('Teacher Preparation - Elementary','Teacher Preparation - Elementary'),
('Telecommunication Technology','Telecommunication Technology'),
('Telecommunications','Telecommunications'),
('Theology','Theology'),
('Theology/Divinity','Theology/Divinity'),
('Third Year Teacher Preparation Elementary','Third Year Teacher Preparation Elementary'),
('Traditional Skill,
 Craft and Art','Traditional Skill,
 Craft and Art'),
('Wildlife and Wildlands Conservation','Wildlife and Wildlands Conservation'),
('Zoology','Zoology')

This has already been done in FSM RMI production

[Ghislain Hachey]

@Brian Lewis was there a reason not to use lkpSubjects here? Since lkpSubjects already has a mapping to ISCED standard fields of study this is really useful for better reporting both locally and internationally. If there is a reason to go with yet another lkpTeacherFieldOfStudy then it would also need the same mapping as the lkpSubjects to ISCED data.

[Brian Lewis]

You’re right better to stick with lkpSubjects. Of the 170 records in lkpTaacherFieldOfStudy (ie imported from the hardcoded list in the workbook) 156 of those are already in lkpSubjects.

I’ll get rid of lkpTEacherFieldOfStudy

‌

[Brian Lewis]

task(census): validate Staff Field of Study from lkpSubjects tabloe (subjects lookup)

Resolves #1137

→ <<cset 037351daf0d0>>