Functions Specific logic used by PEMS for automation 1. Calendar Functions Calendar Functions Not started 1.0 Calculate field Values for Calendar Tables Purpose After the user enters vacation days, and configures start dated, PEMS calculates the number of days in a term. Trigger Recalculate anytime there is a change to any records used to make these calculations upon save Conditions Initial configuration was completed. Sequence These functions may be run anytime there is a new school term record. need some way to limit how many times this is recalculated. Step 1 For each new School Term record, calculate the number of calendar days between the start and end dates, excluding the days specified in the associated Term Label record. Enter result in School Term calendar days. Step 2 For each School Term, count the number of associated Vacation Days records with status = activated. Step 3 Calculate School Term school days by subtracting the number of vacation days found above from the school days, enter as School Term calendar days Step 4 Academic Year school days is the sum of the associated School Term school days. Step 5 Academic Year calendar days is the sum of the associated School Term calendar days. END Calendar Functions Not started 1.1 Automatically Create Academic Years Past and Future Purpose Automate creating new academic future academic records when the user-defined promotion date has been reached.  This reduces the need for users to add a year every time they complete one. Trigger Promotion Date (school configuration)   =  actual date Conditions Initial configuration was completed. Sequence This function must run before more school terms can be created. Step 1 Find the  academic year record the farthest in the future, the "last record" Step 2 Create one more academic year record Step 3 Set field values: Sequence = last record sequence + 1 Start year = last record start year + 1 End year = last record end year + 1 Academic year name - concatenate start year - end year start date = last record start date + 1 year end date = last record end date + 1 year. set status = future END Calendar Functions Not started 1.2 Automatically Update Status of Academic Years. Purpose Academic Year records have a status of current, past, or future, update the status of the records automatically when the user-defined promotion date has been reached. Trigger Managed by the Promotion WorkFlow Conditions There can be only one current year. Sequence This step is best triggered after the next future record has been made and in accordance with the PROMOTION workflow. Step 1 Set previous current academic year status to 'past' Step 2 Set next year, based on ordinate to current. END Calendar Functions Not started 1.3 Automatically Create School Term Records Purpose Automate student term records creation as new academic years are added. Trigger When a new academic year is added. Conditions Sequence This step is best triggered after the creation of a new academic year record.  Term records must be made before students can have their plans updated. Step 1 Find the first term label record with Flag = official start term. Step 2 Create a new school term record Step 3 Set Term Start Year and Start Date Academic year: start date => term: start date Academic year start year => term: start year calculate estimated dates, terms may run over an calendar year Step 4 Calculate Term End Year, Term End Date If Term Label "End in same year" = true then: Term End Year = term: start year Term End Date = Term: start year; Term label:end month: Term Label End day If Term label "End in same year" = False then: Term End Year = term: start year + 1 Term End Date = Term: end year; Term label:end month: Term Label End day Step 4 Set other term field values: Name - concatenate term label name and term: start year status = future term label => related term label record academic year => related academic year record term label flag => term flag Step 5 If term label: flag not equal to "official end term" then go to the next term label record, increase term label: sequence by one. Ste 6 Set Term Start Year and Start Date If previous term label record (sequence -1) end month Grade Span end grade Plan start grade < Grade Span start grade Show  the message "Error in plan grade levels" END Programs of Study Not started 4.2 Auto-enter total terms Purpose The program plan stores the total terms of the plan, this may be calculated from the start and end grade. However, the user may change the values. Trigger Any change  to the Plan start or end grade fields Conditions Sequence Step 1 Set Plan total terms equal to (Plan end grade - Plan start grade + 1) x School Configuration terms_per_year Step 2 Set Plan end grade equal to Grade Span end grade Step 3 The user can then update the grade levels, so do not allow save if Plan total terms >  Grade Span total terms Show  the message "Error in total terms" END Subject Requirements The subject requirement records specify how many units of the subject a student must take under that specific plan.  They relate the subjects to the programs in a many to many relationship.  There will be multiple subject requirement records for each plan, but each subject requirement only applies to one plan.  Multiple subject requirement records will be related to a single subject record. For example, under the honors program, the requirements for English are 40 units ( four years, two terms).  But under the basic diploma, the subject requirements for English is only three years, or 30 units. The user creates subject requirements records when making a plan of study, then selects the classes that fulfill that requirement by creating template records. Programs of Study Completed 4.3 Concatenate Subject Requirement Name Purpose Ensure a unique record name that reflects all relevant information and avoid confusing subject requirements with subjects. Trigger Any change  fields used to create the name Conditions Sequence Step 1 Concatenate: Subject Requirement Subject Name “.” Plan  Name “.U” Units Required : Example: “English.Standard 23.U20” END 5. Create Set of Enrollment Records - TBD Launching this process The student plan links the student and the selected program plan.  The program plan has a set of child records that specify which classes the student should take in sequential terms, the template records.  When a student plan is changed from future to active, then the template records associated with the linked program plan are used to create a set of enrollment records.  Enrollment records link the student, the student term, and the class. The key trigger for the creation of enrollment records is the change of a student plan record from future to active. What we Know From the active student plan record we know the corresponding program plan record, and the corresponding template records. From the template records, we know: the class linked to the class list table in which the student needs to be enrolled the term the class is supposed to be take in,  from the term label the grade level the class should be take in, "at_grade" From the student plan record we know the Program start term From the program start term, We can find the corresponding student  start term From the student start term: We can find the grade level the student will be at in the term, the 'at grade' We can find the term label for that student term Match the templates to the student terms to create enrollment records For each template record associated to the activated student plan (though the linked program plan) Match the student term to template: find a student term record where student term at_grade = template at_grade  AND student term label = template term label.  There will be multiple templates that match these conditions (one template for each class to take in that term). Note: The student term records can be limited to those withing the grade span of the associated program plan, to reduce time to find a match. For each template that matched the conditions above, create an enrollment record, and associate that enrollment record with the student term. Set enrollment class, enrollment at_grade,  enrollment term label,  enrollment curriculum, enrollment curriculum option, enrollment set, enrollment subject to the values in the corresponding template record. If there is no match between template and student term record based on at_grade and term label, proceed to the next template record.  Note: If no student term records found, they will also not be found for multiple templates. Exit the matching when every single template record has been considered. New Students For new students, PEMS anticipates that the student may be joining the school in the middle of a term or program plan, and so no enrollments records will be created for terms that have passed. If there were no student terms created, then there will be no match and no enrollments created. In this way we do not create enrollments for student terms where the student was not enrolled. Suspend a Plan A student may need to change the plan they are working towards. This may occur after some enrollment records created by the plan are completed and others are not completed.  The first step is to suspend the current plan. To suspend the plan, the  active student plan status field is set to 'suspended' by the user.  When a student plan is suspended, all the enrollment records that are active or completed will remain unchanged. By default, the student will remain in the enrollments until the end of the term.  All incomplete enrollments will be permanently deleted from the system when the plan is suspended. Key Trigger: Student plan is changed from Active to Suspended Find all incomplete enrollment records associated to the suspended student plan and delete them New Plan Assigned to Recurring Students Whether a student is being assigned a new plan because they completed a plan or because the user suspended their plan,  the new plan may require the student to take classes they may have already completed, or are in the process of completing.  Therefore a check needs to be run to see if  enrollment records exist with the same classes as the template records exist. Since plans never cross a grade span, this check only needs to be performed on enrollment records within grade span in question.  If enrollment records exist with the same classes, then the corresponding template records will be excluded and no new classes will be made. Complete enrollment records in the grade span When making enrollment records, PEMS first checks if there are any enrollment records in the student's current grade span.  Each enrollment record has an at_grade, the grade level the enrollment is to be completed or has been completed. If there are enrollment records created with the grade span of the plan to be assigned, and these enrollment record are complete, no new enrollment record will be created from the the templates, and that corresponding template will be excluded from the matching algorithm specified above. Active enrollment records in the grade span If there is an active enrollment record with the same class as the template record, PEMS checks the curriculum.  A different plan may have a different curriculum selected for each class, even though the class is the same.  In this case, the user may want to change the student's curriculum. This could happens early in the school term, if a student found that college preparation work was to difficult and wanted to pursue a basic diploma.  The user may want to choose to change the curriculum but keep the same class.  Note: a change to curriculum would cause the corresponding Moodle class to be suspended, and the student assigned to a different course. However, late in the term, the student will have no chance to complete a new curriculum, and so the better choice will be to complete the chosen classes. In order to manage this use case, a new field needs to be created called "Curriculum Update"  The default field value will be "Retain Current Curricula." In this case, we assume there is no change to curricula for active enrollment records.  The user must change this field value to "Change Curricula"  in order to the curricula to be update in the active enrollment records. For recurring students with new plans: Identify if there is a subset of enrollment records with at_grade field within the grade span of the newly assigned plan. If there are any enrollment records that meet this criteria, continue. If no records found, skip this check Find enrollment records that have status = complete in the student's current grade span. Find the matching template record in the new plan by matching the class fields Exclude any template records from the enrollment algorithm that have been matched by the class field to any completed enrollment record Find enrollment records that have status = active in the student's current grade span. Exclude any template records from the enrollment algorithm that have been matched by the class field to any completed enrollment record. Only if the new plan has the field "Curriculum update" = "change curricula" then update the curriclum field in the enrollment record to match the curriculum field in the template template record (that has same class). Proceed to create enrollment records as described in the enrollment algorithm, but exclude the records that have already been matched to active/completed enrollments.6. Transcript GPA Calculation Reference Information Transcript UI View  GoogleSheet Example Calculations of GPA The 6. Transcript GPA Calculation  page describes detail on how to make GPA calculations. The  Enrollments Table describes the rules to update all the various Grade point fields in the enrollment record.  This must be completed in order to run the transcript. The Student Terms Table describes the rules to calculate GPA fields for the student term record. This must be completed in order to run the transcript. The Transcript UI describes how to display Student Terms table GPA calculations as well as the student table GPA calculations on the Transcript. The List of transcript tags to be displayed on the transcript is described in Programs Table. The Marks System table describes how this table is used to allow users to customize the GPA calculations. Enrollments Table All the grade points fields are entered based on the fields corresponding to the letter grade field in the Marking System table. The transcript tag on the curriculum related to the enrollment record indicates whether the GPA can be increased (weighted). If it can be increased, then the weighted GPAs are from the Marks System Table are entered into the weighted points fields. If the curriculum does not specify to weight the grade points, then the unweighted grade points from the Marking System table are entered into the enrollment weighted fields. The class record controls whether the GPA should be excluded from the standard calculation or the alternate calculation. If the class is to be excluded, then the grade points are zeroed.  There are two types of exclusions, one for the standard and one for the alternate GPA. Both exclusions must be selected to exclude from both types of calculations. To complete the logic - a student term level exclusion will need to be applied, where the term is excluded base on at_grade. This has not been specified yet, but will be added after above logic is implemented and vetted. Student Terms There are four GPA fields to calculate in the student term record. Calculate them all the same, recalculate when ever there is a change in the fields. If enrollment status is complete and if there is a change to any enrollment grade point field, then: For each enrollment, multiply the Grade Points field times the Units Earned field Sum all the values above Sum all the units earned field from associated enrollments. Divide:  (Grade Points x Units Earned ) / Units Earned Enter into the respective GPA field in the student term record. GPA Functions GPA Calculations Not started 6.1 Calculate Grade Point Fields in Enrollment record Purpose Based on the letter grade, look up the grade points from the Marking System table. The Marking System  table allows schools to customize the points they give based on a letter grade. Trigger Update whenever enrollment  status = completed, and then whenever there is a change to the enrollment letter grade. Conditions Marking System table has been configured. Sequence This function can only run after students have been enrolled in courses. all fields are first set to unweighted values Step 1 Based on enrollment  letter grade, look up grade points in Marking System table Set enrollment  grade points to Marking System grade points. Set enrollment  weighted grade points to Marking System grade points. Set enrollment  alternate grade points to Marking System alternate grade points. Set enrollment  alternate weighted grade points to Marking System alternate grade points. weighted fields are increased only when appropriate Step 2 Look up enrollment | curriculum | Compliance Tags. If associated curriculum has ANY compliance tag with "Weight GPA" = yes Then: Set enrollment weighted points to Marking System weighted points. Set enrollment alternate weighted points to Marking System alternate weighted points. fields are zeroed if excluded from gpa calculations Step 3 Look up enrollment | Class | Exclude GPA If Exclude GPA = standard GPA then Set enrollment Grade Points to zero Set enrollment Weighted Grade Points to zero If Exclude GPA = alternate GPA then Set enrollment alternate grade points to zero Set enrollment alternate weighted Grade Points to zero END 6.1 Test Case Enrollment Grades Class is Excluded Standard Class is Excluded Alternate Curriculum tag is Weighted = yes Marking System Grade Points Marking System Weighted Grade Points Marking System alternate Grade Points Marking System Alternate Weighted Grade Points A+ yes 4.0 5.0 4.0 5.0 A+ no 4.0 4.0 4.0 4.0 B- Excluded no 0 0 3.0 4.0 C no 2.0 2.0 2.0 2.0 D- yes 0.9 1.9 1.0 2.0 B Excluded no 3.0 3.0 0 0 Marking System Table Example GPA Calculations Not started 6.2 Calculate Student Term GPAs Purpose The GPAs are calculated based on the points awarded for each enrollment and the units earned for each enrollment record. Trigger When ever there is a change to the any of the points fields. Conditions Points fields are not empty. Sequence This function runs after the points have been updated. Step 1 Calculate Student Term Units Earned.  Sum all  associated enrollment units earned. Step 2 Calculate standard adjusted Student Term Units Earned. Sum all units earned from all enrollment records where | Class | Exclude GPA = standard Subtract excluded units (above) from Student Term Units Earned Step 3 Calculate alternate adjusted Student Term Units Earned. Sum all units earned from all enrollment records where | Class | Exclude GPA = alternate Subtract excluded units (above) from Student Term Units Earned Step 4 Calculate Student Term GPA: Calculate total points: For each enrollment record sum (units earned x grade points) GPA = total points / standard adjusted units earned (step 2) Step 5 Calculate Student Term Weighted GPA: Calculate total points: For each enrollment record sum (units earned x weighted points) GPA = total points / standard adjusted units earned (step 2) Step 6 Calculate Student Term Alternate GPA: Calculate total points: For each enrollment record, sum (units earned x alternate grade points) GPA = total points / alternate adjusted units earned (step 3) Step 7 Calculate Student Term Alternate Weighted GPA: Calculate total points: For each enrollment record, sum (units earned x alternate weighted grade points) GPA = total points / alternate adjusted units earned (step 3) END 6.2 Test Case Units Earned Standard adjusted Units earned Alternate Adjusted Units Earned Grade Points Weighted Grade Points Alternate Grade Points Alternate Weighted Grade Points enrollment 1 5 5 5 4.0 5.0 4.0 5.0 enrollment 2 - excluded standard 5 0 5 0 0 2.0 3.0 enrollment 3 5 5 5 3.0 3.0 3.0 3.0 enrollment 4 - excluded alternate 5 5 0 2.0 2.0 0 0 Student Term Values 20 15 15 (4.0x5) + (0x5) +(3.0x5) +(2.0x5) = 45 45/15 = 3.0 (5.0x5) + (0x5) +(3.0x5) +(2.0x5) = 50 50/15 = 3.33 (4.0x5) + (2.0x5) +(3.0x5) +(0x5) = 45 45/15 = 3.0 (5.0x5) + (3.0x5) +(3.0x5) +(0x5) = 55 55/15 = 3.67 Student Term Fields units earned - - GPA WGPA AGPA AWGPA 7. Activate Next Student Plan - TBD The student plan table shows the status of the various plans the student has been assigned over time, it is like a log file for the student plans. Multiple student plan records may be associated to a student record Only one plan may be active at any point in time.  Program_status = active. There may be other student plan records associated with the student, but their status may not be active. The program start term field contains information on the school term, not the student term.  This is an important value and so the name is stored in the student plan term reocrd field called program start term. Only one student plan record may be active at a time Develop a combination of UI and programming to ensure that only one student plan is active at ay point in time - (I have no idea how to do this) Find Program start term from start date When the user assigns the student a plan, they may enter the start date of the plan. For new students is will be important to enter the start date of the plan, as they may be starting in middle of a term.  It is important to know if they had less time to complete courses due to a mid-term start date. Based on the start_date of the student plan, PEMS identifies the starting school term and creates the relationship between school term and the student's plans. Entering State date from Program Start Term Once a new student has become recurring student, it is easier to enter the program start term, rather than the start date. The start date of the student plan should be the corresponding start date ot the Program start term. However, if the user selects a Program start term after a student plan start date has been entered, this function will over-write the start date. We do not want to overwrite a start date as that may contain userful information. If the Program start term is input by the user, then enter the start date from the Program start term's start date, but only if the start date field is empty. Determining the school term when a student plan is complete Student Plans are used to create enrollments, but eventually each student will complete their plan and need to be assigned another plan.  In the course of 13 years of school, the student will likely complete 6 to 8 plans. The student plan is associated to only one school plan in the prgrams of study table. The school plan includes the total number of terms required to complete the plan. The student's plan record contains the program start term. To find the last term of the student plan, add the total number of terms to the sequence student plan record: program start term: related sequence + related school plan: total number of terms = sequence of term the school plan will be completed Completing the Active Student Plan We can find the school term when a student plan should completed (see above).  When the last school term in the plan has passed, PEMS can mark the student plan record complete.  The student plan record is marked complete based only on the calendar. A student's marks for classes during the plan are not relevent. So when the current date is passed any student's plan's final term current date, then the student plan record will be marked complete. When the current date is >/= end date of the student plans' final term, then change the status of the student plan to 'completed. Activating the next student plan If there is another student plan record with status = future, then we can automatically activate the next plan record, after we set the satus of the student plan record above to completed.  Each student plan is associated to a program start term, which has a sequence.  The next student plan will have a program start term sequence greater than the last student plan record program start term's sequence.  The last student plan record will be the record we most recently marked complete in the step above. However, there could be multiple student plans with program start term sequence greater than the current school term. So we need to find the lowest sequence number that is also greater than the last program start term's sequence. FInd the student plan record where the program start term's squence is greater than the last student plan's  program start term's sequence, but also the lowest value in all the avaiable student plan records. Set this student plan status to active. No future plan exists If there are no student plan records with status = future, then the student can not automically be assigned the next plan.  For students who have had their plan marked completed, but they have no future plan, we will create a filtered report for the user.  The user will then assign a plan for these students.  Ideally, the user will complete this task before the end of term. Create a filtered list of students who do not have any active student plans for the user to review and address All new student plan records will default to status = future. When a Student Plan is activated When a new student plan is activated, status was changed from future to active. This change in status will trigger the next process, creating the set of enrollment records. When a Student plan is Changed before its completion A student may need to change the plan they are working towards. This may occur after some enrollment records were already created, and some records may be completed while others are not completed. For a change of plan, the active plan program value field is set to 'suspended' by the user.  A new plan will be created as a future plan, then the field status changed from future to active.  This set of actions will trigger the creating of new enrollment records. 8. Groups Function Groups Not started 1.0 Create Group records for new Homeroom record Purpose When a new homeroom is added, the group records in PEMS are created. Trigger Runs only one time, when the new homeroom record is added. Conditions homeroom status field is active. Sequence This function runs after a new homeroom record is created Step 1 Find Set of all Active Curriculum records. Step 2 Create a Group record for each Curriculum record. Step 3 Set Homeroom = to the new homeroom record created. END Groups Not started 2.0 Create Group records when a curriculum record has been activated note: do not allow new curriculum records to be created with status = active, default must be draft. Purpose When a new curriculum record has been added, group records need to be made for all active homerooms. Trigger Runs whenever the status field in the curriculum record has been changed to 'active' Conditions curriculum status field has been changed from any value to active. Sequence This function runs after the curriculum status has been changed. Step 1 Find Set of all Active Homeroom records Step 2 Create a Group record for each homeroom record Step 3 Set Curriculum in the group record = to the newly activated curriculum record. END Groups Not started 3.0 Deactivate Group when home room is deactivated or Curriculum is deactivated note: add active/inactive status field to Group and Homeroom Purpose #When a curriculum is not longer uses or a homeroom no longer operates, deactivate the associated group. Trigger Runs whenever the the status field in the curriculum or homeroom record has been changed to 'inactive' Conditions Curriculum status field has been changed from any value to inactive. OR Homeroom status field has been changed to inactive Sequence This function runs after the curriculum or homeroom status has been changed. Step 1 Find related Group record Step 2 Set Group record status to inactive. END 0.Add New Fields - Completed System Level Fields - Configuration Page This table page should replace the "parameters" page and table with system level field values below: Field Name Description Reference Terms Per Year This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters Days Per Year This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters School Beginning Date This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters Back Years This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters Attrition Rate This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters Student Teacher Ratio This is already column in the parameters table, convert to a field on the configuration page https://cs.studianova.org/admin/parameters Promotion Date Date field when the students are promoted by grade level 1.Calendar Functions Lowest Grade Level Menu select from K-12 list Highest Grade Level Menu select from K-12 list Table Field Updates Field Name Description Reference Term Starts Term Label Table - menu select menu options: "in academic start year" and "in academic end year" Default Term Years Term Ends Term Label Table - menu select menu options: "in academic start year" and "in academic end year" Default Term Years Assessment Window Term Label table - integer value Assessment Module Type - change menu options Assessment Code  table - change menu options to "Level Equivalent", "Percentile Rank", "Competency" Assessment Module Subject field - make mandatory Assessment Code  table Assessment Module Percentile Rank Marks Matrix  table - add a decimal field, two places Assessment Module Relationship - relate assessment results table to student term table multiple assessment results per student term Assessment Module Percentile Rank Assessment Result table - integer value Assessment Module Moodle Learning Plan program of study table - text field Assessment Module Learning Plan Status program of study table - text field Assessment Module Plan GPA Student Plan Table - decimal value, two places Student Plan Table Expiry Date authorizations table - date field Authorizations Relationship and Table Changes Tables Description Reference Relationship - relate assessment results table to student term table multiple assessment results per student term Assessment Module Delete CredAuthorizations table replace with multi-select field in authorizations table (see below) Authorizations For Authorizations, add a multi-select pick list that allows users to select from the credential table. Authorizations Authorizations templates are child records to subject requirements many template to one subject requirement record 3.Programs of Study and Subject Requirements