# Workbook-to-Application Mapping

This mapping records the uploaded workbook structures as application contracts. The PHP application uses relational records rather than spreadsheet cell coordinates, while keeping the same fields, headings, calculated outputs, and review sequence. The supplied workbooks remain source templates; they are not bundled with the deployment package because they contain school and student data.

| Workflow step | Template reference | Application records | Dynamic form/table fields | Calculated result |
|---|---|---|---|---|
| 1. Kalender Pendidikan | No Kaldik workbook was supplied | `calendar_events` | Date range, title, category, effective/non-effective flag, notes | Effective dates available to weekly planning and teaching journals |
| 2. Minggu Efektif | `MINGGUEFEKTIF.xls`, sheet `GASAL 25` | `effective_week_plans`, `effective_week_months`, `non_effective_events` | Subject, class, semester, academic year, monthly week count, non-effective activity, lesson hours/week | Total weeks, non-effective weeks, effective teaching weeks, and effective lesson hours |
| 3. Jurnal Mengajar | `JurnalMengajar.xlsx`, sheet `Jurnal` | `teaching_schedules`, `teaching_journals` | Date, schedule/class, TP, material, activity, allocation, present/absent count, absent students, implementation status | Journal completion per effective week and schedule |
| 4. Nilai Pengetahuan | `NILAIPENGETAHUAN2627.xls`, sheet `XII-1` | `assessment_sets`, `knowledge_scores` | Student, TP, Ulangan, Remidi, Pengayaan, Observasi, Projek, Tugas, PSTS, PSAS | TP average, selected assessment-set average, and accumulated `Nilai Pengetahuan 1` |
| 5. Nilai Diagnostik | `NILAIDIAGNOSTIK2627.xls`, sheet `XII-1B` | `diagnostic_profiles`, `diagnostic_cognitive_scores` | Non-cognitive profile fields, learning style, interest, TP 1–5 prerequisite and initial-understanding values | Learner diagnostic profile and cognitive-readiness view |
| 6. Analisis Butir Soal UH | `AnalisisButirSoalTahun2627.xlsx`, sheet `XII` | `daily_assessments`, `daily_assessment_items`, `daily_assessment_scores` | Assessment identity, score per item, total score, achievement, pass/fail | Student result, item accuracy rate, and class achievement summary |
| 7. Raport | `X-1-KEMUH-JUDIN.xlsx`, sheets `IDENTITAS`, `PROG NILAI`, `Nilai raport`, `Rekap`, and `Rekap BULAT` | `report_settings`, `learning_objectives`, `report_scores` | Teacher/course/class identity, KKTP, TP descriptions, Sumatif 1–4, PSTS, PSAS, final score | Report score, rounded score, predicate, completion status, strongest and improvement objectives |

## Extracted Template Contracts

The effective-week template separates the total calendar weeks, non-effective activities, and final effective learning hours. Its fields are retained as editable line items so the total is computed rather than typed. The journal template repeats its table headers over printable pages; in the application this becomes one journal table that can be filtered by week, schedule, and implementation status.

The knowledge-score template has four TP blocks. Each block contains `Ulangan`, `Remidi`, `Pengayaan`, `Observasi`, `Projek`, `Tugas`, `PSTS`, `PSAS`, and a row average. The application stores the score-type labels in `assessment_sets` so the score-entry grid renders from configuration instead of fixed spreadsheet columns. This allows a teacher to retain the uploaded structure while changing the number of TPs or assessments without source-code changes.

The diagnostic template has two groups. The non-cognitive group includes interaction pattern, initiative, learning motivation, learning assessment, parent profile, learning preferences, and learner interest. The cognitive group records both prerequisite material and initial understanding for TP 1 through TP 5. The application renders these two groups as collapsible form sections.

The daily-assessment template contains a student row with item columns, total score, achievement, and completion. Item rows are generated from the number of assessment items configured by the teacher. The class analysis adds an accuracy percentage for every item, calculated from the number of correct responses divided by scored responses, to make the item review useful beyond the original spreadsheet grid.

The report template identifies the teacher, course, class, semester, academic year, KKTP, and TP descriptions. It produces subject-level values, predicates, completion results, and narratives based on high- and low-performing objectives. The application preserves those outputs in one reviewable report card recap.

## Mandatory Score Link

> **Rule:** the accumulated value for `Nilai Pengetahuan 1` is the source of `Sumatif 1` in the report card.

The PHP service calculates the arithmetic mean of valid components in the active `Nilai Pengetahuan 1` assessment set for each student. When report recaps are requested, that calculated average is exposed as `sumatif_1` and cannot be manually overwritten in the report form. A later edit to a Nilai Pengetahuan 1 score updates the linked report-card recap after recalculation.