Compare commits
| Author | SHA1 | Date | |
|---|---|---|---|
 TudorandClaude Fable 5
|
a00cbe9161 | ||
 tudor
|
5c39131b50 | ||
|
TudorandClaude Fable 5
|
95081d38bd | ||
|
tudor
|
694b6013b3 | ||
|
TudorandClaude Fable 5
|
9f8dba227c | ||
|
tudor
|
18cd805c6c |
+1
-2
@@ -572,7 +572,7 @@ async def get_school_details(request: Request, urn: int):
|
||||
# Get latest info for the school
|
||||
latest = school_data.iloc[-1]
|
||||
|
||||
# Fetch supplementary data (Ofsted, Parent View, admissions, etc.)
|
||||
# Fetch supplementary data (Ofsted, admissions, etc.)
|
||||
from .database import SessionLocal
|
||||
supplementary = {}
|
||||
try:
|
||||
@@ -605,7 +605,6 @@ async def get_school_details(request: Request, urn: int):
|
||||
"yearly_data": clean_for_json(school_data),
|
||||
# Supplementary data (null if not yet populated by Kestra)
|
||||
"ofsted": supplementary.get("ofsted"),
|
||||
"parent_view": supplementary.get("parent_view"),
|
||||
"census": supplementary.get("census"),
|
||||
"admissions": supplementary.get("admissions"),
|
||||
"admissions_history": supplementary.get("admissions_history") or [],
|
||||
|
||||
+1
-25
@@ -14,7 +14,7 @@ from .config import settings
|
||||
from .database import SessionLocal, engine
|
||||
from .models import (
|
||||
DimSchool, DimLocation, KS2Performance,
|
||||
FactOfstedInspection, FactParentView, FactAdmissions,
|
||||
FactOfstedInspection, FactAdmissions,
|
||||
FactDeprivation, FactFinance, FactPupilCharacteristics,
|
||||
)
|
||||
from .schemas import SCHOOL_TYPE_MAP
|
||||
@@ -446,30 +446,6 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
|
||||
else None
|
||||
)
|
||||
|
||||
# Parent View
|
||||
pv = safe_query(FactParentView, "urn")
|
||||
result["parent_view"] = (
|
||||
{
|
||||
"survey_date": pv.survey_date.isoformat() if pv.survey_date else None,
|
||||
"total_responses": pv.total_responses,
|
||||
"q_happy_pct": pv.q_happy_pct,
|
||||
"q_safe_pct": pv.q_safe_pct,
|
||||
"q_behaviour_pct": pv.q_behaviour_pct,
|
||||
"q_bullying_pct": pv.q_bullying_pct,
|
||||
"q_communication_pct": pv.q_communication_pct,
|
||||
"q_progress_pct": pv.q_progress_pct,
|
||||
"q_teaching_pct": pv.q_teaching_pct,
|
||||
"q_information_pct": pv.q_information_pct,
|
||||
"q_curriculum_pct": pv.q_curriculum_pct,
|
||||
"q_future_pct": pv.q_future_pct,
|
||||
"q_leadership_pct": pv.q_leadership_pct,
|
||||
"q_wellbeing_pct": pv.q_wellbeing_pct,
|
||||
"q_recommend_pct": pv.q_recommend_pct,
|
||||
}
|
||||
if pv
|
||||
else None
|
||||
)
|
||||
|
||||
# Census (latest year of fact_pupil_characteristics)
|
||||
pc = safe_query(FactPupilCharacteristics, "urn", "year")
|
||||
result["census"] = (
|
||||
|
||||
@@ -433,6 +433,25 @@ def _apply_schema_alterations():
|
||||
conn.commit()
|
||||
|
||||
|
||||
def _apply_schema_drops():
|
||||
"""
|
||||
Drop tables retired from the schema. Idempotent (DROP … IF EXISTS), so it's
|
||||
safe to run on every migration. Add entries here when a model is removed.
|
||||
"""
|
||||
drops = [
|
||||
# v6: Ofsted Parent View feature removed
|
||||
"DROP TABLE IF EXISTS marts.fact_parent_view CASCADE",
|
||||
]
|
||||
from sqlalchemy import text as sa_text
|
||||
with engine.connect() as conn:
|
||||
for stmt in drops:
|
||||
try:
|
||||
conn.execute(sa_text(stmt))
|
||||
except Exception as e:
|
||||
print(f" Warning: drop skipped ({e})")
|
||||
conn.commit()
|
||||
|
||||
|
||||
def run_full_migration(geocode: bool = False) -> bool:
|
||||
"""
|
||||
Run a complete migration: drop all tables and reimport from CSV.
|
||||
@@ -479,6 +498,9 @@ def run_full_migration(geocode: bool = False) -> bool:
|
||||
print("Applying column additions to supplementary tables...")
|
||||
_apply_schema_alterations()
|
||||
|
||||
print("Dropping retired tables...")
|
||||
_apply_schema_drops()
|
||||
|
||||
print("\nLoading CSV data...")
|
||||
df = load_csv_data(settings.data_dir)
|
||||
|
||||
|
||||
@@ -149,29 +149,6 @@ class FactOfstedInspection(Base):
|
||||
report_url = Column(Text)
|
||||
|
||||
|
||||
class FactParentView(Base):
|
||||
"""Ofsted Parent View survey — latest per school."""
|
||||
__tablename__ = "fact_parent_view"
|
||||
__table_args__ = MARTS
|
||||
|
||||
urn = Column(Integer, primary_key=True)
|
||||
survey_date = Column(Date)
|
||||
total_responses = Column(Integer)
|
||||
q_happy_pct = Column(Float)
|
||||
q_safe_pct = Column(Float)
|
||||
q_behaviour_pct = Column(Float)
|
||||
q_bullying_pct = Column(Float)
|
||||
q_communication_pct = Column(Float)
|
||||
q_progress_pct = Column(Float)
|
||||
q_teaching_pct = Column(Float)
|
||||
q_information_pct = Column(Float)
|
||||
q_curriculum_pct = Column(Float)
|
||||
q_future_pct = Column(Float)
|
||||
q_leadership_pct = Column(Float)
|
||||
q_wellbeing_pct = Column(Float)
|
||||
q_recommend_pct = Column(Float)
|
||||
|
||||
|
||||
class FactAdmissions(Base):
|
||||
"""School admissions — one row per URN per year."""
|
||||
__tablename__ = "fact_admissions"
|
||||
|
||||
+2
-1
@@ -13,7 +13,7 @@ WHEN TO BUMP:
|
||||
"""
|
||||
|
||||
# Current schema version - increment when models change
|
||||
SCHEMA_VERSION = 5
|
||||
SCHEMA_VERSION = 6
|
||||
|
||||
# Changelog for documentation
|
||||
SCHEMA_CHANGELOG = {
|
||||
@@ -22,4 +22,5 @@ SCHEMA_CHANGELOG = {
|
||||
3: "Added supplementary data tables: ofsted, parent_view, census, admissions, sen_detail, phonics, deprivation, finance; GIAS columns on schools",
|
||||
4: "Added Ofsted Report Card columns to ofsted_inspections (new framework from Nov 2025)",
|
||||
5: "Apply ALTER TABLE additions for RC columns missed by create_all on existing tables",
|
||||
6: "Removed the Ofsted Parent View feature: dropped fact_parent_view table and model",
|
||||
}
|
||||
|
||||
+1
-2
@@ -79,8 +79,7 @@ fail the E2E gate. That's the point: staging absorbs the risk.
|
||||
5. **Bootstrap staging data via Airflow** (no prod dump — staging populates
|
||||
itself from source, exercising the pipeline image end-to-end):
|
||||
- Open the staging Airflow UI (`http://<host>:8081`) and trigger, in order:
|
||||
`school_data_daily`, `school_data_monthly_ofsted`,
|
||||
`school_data_monthly_parent_view`, then the manual-schedule
|
||||
`school_data_daily`, `school_data_monthly_ofsted`, then the manual-schedule
|
||||
`school_data_annual_ees` and `school_data_annual_idaci`.
|
||||
- First runs download from government sources (GIAS, Ofsted, EES, IDACI),
|
||||
run dbt, and sync Typesense — expect the initial backfill to take a while.
|
||||
|
||||
@@ -133,7 +133,7 @@ export default async function SchoolPage({ params }: SchoolPageProps) {
|
||||
notFound();
|
||||
}
|
||||
|
||||
const { school_info, yearly_data, absence_data, ofsted, parent_view, census, admissions, admissions_history, sen_detail, phonics, deprivation, finance } = data;
|
||||
const { school_info, yearly_data, absence_data, ofsted, census, admissions, admissions_history, sen_detail, phonics, deprivation, finance } = data;
|
||||
|
||||
// Redirect bare URN to canonical slug URL
|
||||
const canonicalSlug = schoolUrl(urn, school_info.school_name).replace('/school/', '');
|
||||
@@ -189,7 +189,6 @@ export default async function SchoolPage({ params }: SchoolPageProps) {
|
||||
yearlyData={yearly_data}
|
||||
absenceData={absence_data}
|
||||
ofsted={ofsted ?? null}
|
||||
parentView={parent_view ?? null}
|
||||
census={census ?? null}
|
||||
admissions={admissions ?? null}
|
||||
senDetail={sen_detail ?? null}
|
||||
@@ -203,7 +202,6 @@ export default async function SchoolPage({ params }: SchoolPageProps) {
|
||||
yearlyData={yearly_data}
|
||||
absenceData={absence_data}
|
||||
ofsted={ofsted ?? null}
|
||||
parentView={parent_view ?? null}
|
||||
census={census ?? null}
|
||||
admissions={admissions ?? null}
|
||||
admissionsHistory={admissions_history ?? []}
|
||||
|
||||
@@ -111,8 +111,10 @@ export function ComparisonView({
|
||||
setComparisonData(data.comparison);
|
||||
})
|
||||
.catch((err) => {
|
||||
// Keep whatever we already have (SSR data or a previous fetch) rather
|
||||
// than blanking the chart — a transient refetch failure shouldn't
|
||||
// destroy a working comparison the user is looking at.
|
||||
console.error('Failed to fetch comparison:', err);
|
||||
setComparisonData(null);
|
||||
});
|
||||
} else {
|
||||
setComparisonData(null);
|
||||
|
||||
@@ -369,6 +369,16 @@
|
||||
|
||||
.viewToggle {
|
||||
justify-content: center;
|
||||
flex-shrink: 0;
|
||||
}
|
||||
|
||||
/* The sort <select> sizes to its widest option ("Highest Reading, Writing
|
||||
& Maths %"), which overflows a phone viewport — beside the view toggle it
|
||||
ran off the right edge. Let it flex into the remaining space and shrink;
|
||||
the selected label truncates instead of pushing past the screen. */
|
||||
.sortSelect {
|
||||
flex: 1;
|
||||
min-width: 0;
|
||||
}
|
||||
|
||||
.mapViewContainer {
|
||||
|
||||
@@ -713,18 +713,6 @@
|
||||
margin: -0.5rem 0 1rem;
|
||||
}
|
||||
|
||||
/* Response count badge */
|
||||
.responseBadge {
|
||||
font-size: 0.75rem;
|
||||
font-weight: 500;
|
||||
font-family: var(--font-dm-sans), sans-serif;
|
||||
color: var(--text-muted, #8a847a);
|
||||
background: var(--bg-secondary, #f3ede4);
|
||||
padding: 0.1rem 0.5rem;
|
||||
border-radius: 999px;
|
||||
margin-left: auto;
|
||||
}
|
||||
|
||||
.subSectionTitle {
|
||||
font-size: 0.875rem;
|
||||
font-weight: 600;
|
||||
@@ -732,18 +720,6 @@
|
||||
margin: 1.25rem 0 0.75rem;
|
||||
}
|
||||
|
||||
/* Parent recommendation line in Ofsted section */
|
||||
.parentRecommendLine {
|
||||
font-size: 0.85rem;
|
||||
color: var(--text-secondary, #5c564d);
|
||||
margin: 0.5rem 0 0;
|
||||
}
|
||||
|
||||
.parentRecommendLine strong {
|
||||
color: var(--accent-teal, #2d7d7d);
|
||||
font-weight: 700;
|
||||
}
|
||||
|
||||
/* Metrics Grid & Cards */
|
||||
.metricsGrid {
|
||||
display: grid;
|
||||
@@ -1094,49 +1070,6 @@
|
||||
text-decoration: underline;
|
||||
}
|
||||
|
||||
/* Parent View */
|
||||
.parentViewGrid {
|
||||
display: flex;
|
||||
flex-direction: column;
|
||||
gap: 0.5rem;
|
||||
}
|
||||
|
||||
.parentViewRow {
|
||||
display: flex;
|
||||
align-items: center;
|
||||
gap: 0.75rem;
|
||||
font-size: 0.875rem;
|
||||
}
|
||||
|
||||
.parentViewLabel {
|
||||
flex: 0 0 18rem;
|
||||
color: var(--text-secondary, #5c564d);
|
||||
font-size: 0.8125rem;
|
||||
}
|
||||
|
||||
.parentViewBar {
|
||||
flex: 1;
|
||||
height: 0.5rem;
|
||||
background: var(--bg-secondary, #f3ede4);
|
||||
border-radius: 4px;
|
||||
overflow: hidden;
|
||||
}
|
||||
|
||||
.parentViewFill {
|
||||
height: 100%;
|
||||
background: var(--accent-teal, #2d7d7d);
|
||||
border-radius: 4px;
|
||||
transition: width 0.4s ease;
|
||||
}
|
||||
|
||||
.parentViewPct {
|
||||
flex: 0 0 2.75rem;
|
||||
text-align: right;
|
||||
font-size: 0.8125rem;
|
||||
font-weight: 600;
|
||||
color: var(--text-primary, #1a1612);
|
||||
}
|
||||
|
||||
/* Admissions badge — uses unified status colours */
|
||||
.admissionsBadge {
|
||||
display: inline-flex;
|
||||
@@ -1269,25 +1202,6 @@
|
||||
}
|
||||
|
||||
@media (max-width: 480px) {
|
||||
.parentViewRow {
|
||||
flex-direction: column;
|
||||
align-items: flex-start;
|
||||
gap: 0.25rem;
|
||||
}
|
||||
|
||||
.parentViewLabel {
|
||||
flex: none;
|
||||
max-width: 100%;
|
||||
}
|
||||
|
||||
.parentViewBar {
|
||||
width: 100%;
|
||||
}
|
||||
|
||||
.parentViewPct {
|
||||
flex: none;
|
||||
}
|
||||
|
||||
.card {
|
||||
padding: 1rem;
|
||||
}
|
||||
|
||||
@@ -13,7 +13,7 @@ import { SchoolHeroMap, type SchoolHeroMapHandle } from './SchoolHeroMap';
|
||||
import { MetricTooltip } from './MetricTooltip';
|
||||
import type {
|
||||
School, SchoolResult, AbsenceData,
|
||||
OfstedInspection, OfstedParentView, SchoolCensus,
|
||||
OfstedInspection, SchoolCensus,
|
||||
SchoolAdmissions, SenDetail, Phonics,
|
||||
SchoolDeprivation, SchoolFinance, NationalAverages,
|
||||
} from '@/lib/types';
|
||||
@@ -63,7 +63,6 @@ interface SchoolDetailViewProps {
|
||||
yearlyData: SchoolResult[];
|
||||
absenceData: AbsenceData | null;
|
||||
ofsted: OfstedInspection | null;
|
||||
parentView: OfstedParentView | null;
|
||||
census: SchoolCensus | null;
|
||||
admissions: SchoolAdmissions | null;
|
||||
admissionsHistory: SchoolAdmissions[];
|
||||
@@ -75,7 +74,7 @@ interface SchoolDetailViewProps {
|
||||
|
||||
export function SchoolDetailView({
|
||||
schoolInfo, yearlyData, absenceData,
|
||||
ofsted, parentView, census, admissions, admissionsHistory, senDetail, phonics, deprivation, finance,
|
||||
ofsted, census, admissions, admissionsHistory, senDetail, phonics, deprivation, finance,
|
||||
}: SchoolDetailViewProps) {
|
||||
const router = useRouter();
|
||||
const { addSchool, removeSchool, isSelected } = useComparison();
|
||||
@@ -234,8 +233,6 @@ export function SchoolDetailView({
|
||||
if (hasInclusionData) navItems.push({ id: 'inclusion', label: 'Pupils' });
|
||||
if (yearlyData.length > 0) navItems.push({ id: 'history', label: 'History' });
|
||||
if (hasPhonics && isPrimary) navItems.push({ id: 'phonics', label: 'Phonics' });
|
||||
if (parentView && parentView.total_responses != null && parentView.total_responses > 0)
|
||||
navItems.push({ id: 'parents', label: 'Parents' });
|
||||
if (hasSchoolLife) navItems.push({ id: 'school-life', label: 'School Life' });
|
||||
if (hasDeprivation) navItems.push({ id: 'local-area', label: 'Local Area' });
|
||||
if (hasFinance) navItems.push({ id: 'finances', label: 'Finances' });
|
||||
@@ -549,11 +546,6 @@ export function SchoolDetailView({
|
||||
) : null;
|
||||
})}
|
||||
</div>
|
||||
{parentView?.q_recommend_pct != null && parentView.total_responses != null && parentView.total_responses > 0 && (
|
||||
<p className={styles.parentRecommendLine}>
|
||||
<strong>{Math.round(parentView.q_recommend_pct)}%</strong> of parents would recommend this school ({parentView.total_responses.toLocaleString()} responses)
|
||||
</p>
|
||||
)}
|
||||
</>
|
||||
) : (
|
||||
/* ── Old OEIF layout ── */
|
||||
@@ -572,11 +564,6 @@ export function SchoolDetailView({
|
||||
<p className={styles.ofstedDisclaimer}>
|
||||
From September 2024, Ofsted no longer makes an overall effectiveness judgement in inspections of state-funded schools.
|
||||
</p>
|
||||
{parentView?.q_recommend_pct != null && parentView.total_responses != null && parentView.total_responses > 0 && (
|
||||
<p className={styles.parentRecommendLine}>
|
||||
<strong>{Math.round(parentView.q_recommend_pct)}%</strong> of parents would recommend this school ({parentView.total_responses.toLocaleString()} responses)
|
||||
</p>
|
||||
)}
|
||||
{oeifAllSameGrade ? (
|
||||
<p className={styles.ofstedAllSame}>
|
||||
Rated <strong>{OFSTED_LABELS[ofsted.overall_effectiveness!]}</strong> across all inspected areas — Quality of Teaching, Behaviour, Pupils' Development and Leadership.
|
||||
@@ -1129,42 +1116,6 @@ export function SchoolDetailView({
|
||||
</section>
|
||||
)}
|
||||
|
||||
{/* What Parents Say */}
|
||||
{parentView && parentView.total_responses != null && parentView.total_responses > 0 && (
|
||||
<section id="parents" className={styles.card}>
|
||||
<h2 className={styles.sectionTitle}>
|
||||
What Parents Say
|
||||
<span className={styles.responseBadge}>
|
||||
{parentView.total_responses.toLocaleString()} responses
|
||||
</span>
|
||||
</h2>
|
||||
<p className={styles.sectionSubtitle}>
|
||||
From the Ofsted Parent View survey — parents share their experience of this school.
|
||||
</p>
|
||||
<div className={styles.parentViewGrid}>
|
||||
{[
|
||||
{ label: 'Would recommend this school', pct: parentView.q_recommend_pct },
|
||||
{ label: 'My child is happy here', pct: parentView.q_happy_pct },
|
||||
{ label: 'My child feels safe here', pct: parentView.q_safe_pct },
|
||||
{ label: 'Teaching is good', pct: parentView.q_teaching_pct },
|
||||
{ label: 'My child makes good progress', pct: parentView.q_progress_pct },
|
||||
{ label: 'School looks after pupils\' wellbeing', pct: parentView.q_wellbeing_pct },
|
||||
{ label: 'Behaviour is well managed', pct: parentView.q_behaviour_pct },
|
||||
{ label: 'School deals well with bullying', pct: parentView.q_bullying_pct },
|
||||
{ label: 'Communicates well with parents', pct: parentView.q_communication_pct },
|
||||
].filter(q => q.pct != null).map(({ label, pct }) => (
|
||||
<div key={label} className={styles.parentViewRow}>
|
||||
<span className={styles.parentViewLabel}>{label}</span>
|
||||
<div className={styles.parentViewBar}>
|
||||
<div className={styles.parentViewFill} style={{ width: `${pct}%` }} />
|
||||
</div>
|
||||
<span className={styles.parentViewPct}>{Math.round(pct!)}%</span>
|
||||
</div>
|
||||
))}
|
||||
</div>
|
||||
</section>
|
||||
)}
|
||||
|
||||
{/* School Life */}
|
||||
{hasSchoolLife && (
|
||||
<section id="school-life" className={styles.card}>
|
||||
|
||||
@@ -383,17 +383,6 @@
|
||||
margin: 1.25rem 0 0.75rem;
|
||||
}
|
||||
|
||||
.responseBadge {
|
||||
font-size: 0.75rem;
|
||||
font-weight: 500;
|
||||
font-family: var(--font-dm-sans), sans-serif;
|
||||
color: var(--text-muted, #8a847a);
|
||||
background: var(--bg-secondary, #f3ede4);
|
||||
padding: 0.1rem 0.5rem;
|
||||
border-radius: 999px;
|
||||
margin-left: auto;
|
||||
}
|
||||
|
||||
/* ── Progress 8 suspension banner ───────────────────── */
|
||||
.p8Banner {
|
||||
background: rgba(180, 120, 0, 0.1);
|
||||
@@ -664,60 +653,6 @@
|
||||
text-decoration: underline;
|
||||
}
|
||||
|
||||
/* ── Parent View ─────────────────────────────────────── */
|
||||
.parentRecommendLine {
|
||||
font-size: 0.85rem;
|
||||
color: var(--text-secondary, #5c564d);
|
||||
margin: 0.5rem 0 0;
|
||||
}
|
||||
|
||||
.parentRecommendLine strong {
|
||||
color: var(--accent-teal, #2d7d7d);
|
||||
font-weight: 700;
|
||||
}
|
||||
|
||||
.parentViewGrid {
|
||||
display: flex;
|
||||
flex-direction: column;
|
||||
gap: 0.5rem;
|
||||
}
|
||||
|
||||
.parentViewRow {
|
||||
display: flex;
|
||||
align-items: center;
|
||||
gap: 0.75rem;
|
||||
font-size: 0.875rem;
|
||||
}
|
||||
|
||||
.parentViewLabel {
|
||||
flex: 0 0 18rem;
|
||||
color: var(--text-secondary, #5c564d);
|
||||
font-size: 0.8125rem;
|
||||
}
|
||||
|
||||
.parentViewBar {
|
||||
flex: 1;
|
||||
height: 0.5rem;
|
||||
background: var(--bg-secondary, #f3ede4);
|
||||
border-radius: 4px;
|
||||
overflow: hidden;
|
||||
}
|
||||
|
||||
.parentViewFill {
|
||||
height: 100%;
|
||||
background: var(--accent-teal, #2d7d7d);
|
||||
border-radius: 4px;
|
||||
transition: width 0.4s ease;
|
||||
}
|
||||
|
||||
.parentViewPct {
|
||||
flex: 0 0 2.75rem;
|
||||
text-align: right;
|
||||
font-size: 0.8125rem;
|
||||
font-weight: 600;
|
||||
color: var(--text-primary, #1a1612);
|
||||
}
|
||||
|
||||
/* ── Admissions ──────────────────────────────────────── */
|
||||
.admissionsTypeBadge {
|
||||
border-radius: 6px;
|
||||
@@ -1135,10 +1070,6 @@
|
||||
font-size: 1rem;
|
||||
}
|
||||
|
||||
.parentViewLabel {
|
||||
flex-basis: 10rem;
|
||||
}
|
||||
|
||||
.ofstedReportLink {
|
||||
margin-left: 0;
|
||||
display: block;
|
||||
@@ -1151,25 +1082,6 @@
|
||||
}
|
||||
|
||||
@media (max-width: 480px) {
|
||||
.parentViewRow {
|
||||
flex-direction: column;
|
||||
align-items: flex-start;
|
||||
gap: 0.25rem;
|
||||
}
|
||||
|
||||
.parentViewLabel {
|
||||
flex: none;
|
||||
max-width: 100%;
|
||||
}
|
||||
|
||||
.parentViewBar {
|
||||
width: 100%;
|
||||
}
|
||||
|
||||
.parentViewPct {
|
||||
flex: none;
|
||||
}
|
||||
|
||||
.metricsGrid {
|
||||
grid-template-columns: 1fr 1fr;
|
||||
gap: 0.5rem;
|
||||
|
||||
@@ -19,7 +19,7 @@ const PerformanceChart = dynamic(
|
||||
);
|
||||
import type {
|
||||
School, SchoolResult, AbsenceData,
|
||||
OfstedInspection, OfstedParentView, SchoolCensus,
|
||||
OfstedInspection, SchoolCensus,
|
||||
SchoolAdmissions, SenDetail, Phonics,
|
||||
SchoolDeprivation, SchoolFinance, NationalAverages,
|
||||
} from '@/lib/types';
|
||||
@@ -65,7 +65,6 @@ interface SecondarySchoolDetailViewProps {
|
||||
yearlyData: SchoolResult[];
|
||||
absenceData: AbsenceData | null;
|
||||
ofsted: OfstedInspection | null;
|
||||
parentView: OfstedParentView | null;
|
||||
census: SchoolCensus | null;
|
||||
admissions: SchoolAdmissions | null;
|
||||
senDetail: SenDetail | null;
|
||||
@@ -76,7 +75,7 @@ interface SecondarySchoolDetailViewProps {
|
||||
|
||||
export function SecondarySchoolDetailView({
|
||||
schoolInfo, yearlyData,
|
||||
ofsted, parentView, census, admissions, senDetail, deprivation, finance, absenceData,
|
||||
ofsted, census, admissions, senDetail, deprivation, finance, absenceData,
|
||||
}: SecondarySchoolDetailViewProps) {
|
||||
const router = useRouter();
|
||||
// Hero map — the "View on map" link opens its fullscreen view.
|
||||
@@ -101,7 +100,6 @@ export function SecondarySchoolDetailView({
|
||||
|
||||
const hasSixthForm = schoolInfo.age_range?.includes('18') ?? false;
|
||||
const hasFinance = finance != null && finance.per_pupil_spend != null;
|
||||
const hasParents = parentView != null && parentView.total_responses != null && parentView.total_responses > 0;
|
||||
const hasDeprivation = deprivation != null && deprivation.idaci_decile != null;
|
||||
const hasLocation = schoolInfo.latitude != null && schoolInfo.longitude != null;
|
||||
const hasWellbeing = (latestResults?.sen_support_pct != null || latestResults?.sen_ehcp_pct != null) || hasDeprivation;
|
||||
@@ -159,7 +157,6 @@ export function SecondarySchoolDetailView({
|
||||
if (hasResults) navItems.push({ id: 'gcse', label: 'GCSEs' });
|
||||
if (admissions) navItems.push({ id: 'admissions', label: 'Admissions' });
|
||||
if (yearlyData.length > 1) navItems.push({ id: 'history', label: 'History' });
|
||||
if (hasParents) navItems.push({ id: 'parents', label: 'Parents' });
|
||||
if (hasWellbeing) navItems.push({ id: 'wellbeing', label: 'Wellbeing' });
|
||||
if (hasFinance) navItems.push({ id: 'finances', label: 'Finances' });
|
||||
|
||||
@@ -435,11 +432,6 @@ export function SecondarySchoolDetailView({
|
||||
</div>
|
||||
</>
|
||||
)}
|
||||
{hasParents && (
|
||||
<p className={styles.parentRecommendLine}>
|
||||
<strong>{Math.round(parentView!.q_recommend_pct!)}%</strong> of parents would recommend this school ({parentView!.total_responses!.toLocaleString()} responses)
|
||||
</p>
|
||||
)}
|
||||
</section>
|
||||
)}
|
||||
|
||||
@@ -775,42 +767,6 @@ export function SecondarySchoolDetailView({
|
||||
</details>
|
||||
</section>
|
||||
)}
|
||||
{/* ── Parent View ────────────────────────────────── */}
|
||||
{hasParents && parentView && (
|
||||
<section id="parents" className={styles.card}>
|
||||
<h2 className={styles.sectionTitle}>
|
||||
What Parents Say
|
||||
<span className={styles.responseBadge}>
|
||||
{parentView.total_responses!.toLocaleString()} responses
|
||||
</span>
|
||||
</h2>
|
||||
<p className={styles.sectionSubtitle}>
|
||||
From the Ofsted Parent View survey — parents share their experience of this school.
|
||||
</p>
|
||||
<div className={styles.parentViewGrid}>
|
||||
{[
|
||||
{ label: 'Would recommend this school', pct: parentView.q_recommend_pct },
|
||||
{ label: 'My child is happy here', pct: parentView.q_happy_pct },
|
||||
{ label: 'My child feels safe here', pct: parentView.q_safe_pct },
|
||||
{ label: 'Teaching is good', pct: parentView.q_teaching_pct },
|
||||
{ label: 'My child makes good progress', pct: parentView.q_progress_pct },
|
||||
{ label: 'School looks after pupils\' wellbeing', pct: parentView.q_wellbeing_pct },
|
||||
{ label: 'Behaviour is well managed', pct: parentView.q_behaviour_pct },
|
||||
{ label: 'School deals well with bullying', pct: parentView.q_bullying_pct },
|
||||
{ label: 'Communicates well with parents', pct: parentView.q_communication_pct },
|
||||
].filter(q => q.pct != null).map(({ label, pct }) => (
|
||||
<div key={label} className={styles.parentViewRow}>
|
||||
<span className={styles.parentViewLabel}>{label}</span>
|
||||
<div className={styles.parentViewBar}>
|
||||
<div className={styles.parentViewFill} style={{ width: `${pct}%` }} />
|
||||
</div>
|
||||
<span className={styles.parentViewPct}>{Math.round(pct!)}%</span>
|
||||
</div>
|
||||
))}
|
||||
</div>
|
||||
</section>
|
||||
)}
|
||||
|
||||
{/* ── Wellbeing ──────────────────────────────────── */}
|
||||
{hasWellbeing && (
|
||||
<section id="wellbeing" className={styles.card}>
|
||||
|
||||
@@ -99,25 +99,6 @@ export interface OfstedInspection {
|
||||
rc_sixth_form: number | null;
|
||||
}
|
||||
|
||||
export interface OfstedParentView {
|
||||
survey_date: string | null;
|
||||
total_responses: number | null;
|
||||
q_happy_pct: number | null;
|
||||
q_safe_pct: number | null;
|
||||
q_behaviour_pct: number | null;
|
||||
q_bullying_pct: number | null;
|
||||
q_communication_pct: number | null;
|
||||
q_progress_pct: number | null;
|
||||
q_teaching_pct: number | null;
|
||||
q_information_pct: number | null;
|
||||
q_curriculum_pct: number | null;
|
||||
q_future_pct: number | null;
|
||||
q_leadership_pct: number | null;
|
||||
q_wellbeing_pct: number | null;
|
||||
q_recommend_pct: number | null;
|
||||
q_sen_pct: number | null;
|
||||
}
|
||||
|
||||
export interface SchoolCensus {
|
||||
year: number;
|
||||
total_pupils: number | null;
|
||||
@@ -312,7 +293,6 @@ export interface SchoolDetailsResponse {
|
||||
absence_data: AbsenceData | null;
|
||||
// Supplementary data (null until Kestra populates)
|
||||
ofsted: OfstedInspection | null;
|
||||
parent_view: OfstedParentView | null;
|
||||
census: SchoolCensus | null;
|
||||
admissions: SchoolAdmissions | null;
|
||||
/** All available admissions years, oldest first. Drives the multi-year trend view. */
|
||||
|
||||
@@ -19,7 +19,6 @@ RUN pip install --no-cache-dir \
|
||||
./plugins/extractors/tap-uk-gias \
|
||||
./plugins/extractors/tap-uk-ees \
|
||||
./plugins/extractors/tap-uk-ofsted \
|
||||
./plugins/extractors/tap-uk-parent-view \
|
||||
./plugins/extractors/tap-uk-fbit \
|
||||
./plugins/extractors/tap-uk-idaci
|
||||
|
||||
|
||||
@@ -156,31 +156,6 @@ with DAG(
|
||||
extract_ees_group >> dbt_build_ees >> sync_typesense_ees
|
||||
|
||||
|
||||
# ── Monthly DAG (Parent View) ──────────────────────────────────────────
|
||||
|
||||
with DAG(
|
||||
dag_id="school_data_monthly_parent_view",
|
||||
default_args=default_args,
|
||||
description="Monthly Ofsted Parent View extraction and transform",
|
||||
schedule="0 3 1 * *",
|
||||
start_date=datetime(2025, 1, 1),
|
||||
catchup=False,
|
||||
tags=["school-compare", "monthly"],
|
||||
) as monthly_parent_view_dag:
|
||||
|
||||
extract_parent_view = BashOperator(
|
||||
task_id="extract_parent_view",
|
||||
bash_command=f"cd {PIPELINE_DIR} && {MELTANO_BIN} run tap-uk-parent-view target-postgres",
|
||||
)
|
||||
|
||||
dbt_build_parent_view = BashOperator(
|
||||
task_id="dbt_build",
|
||||
bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_parent_view+ fact_parent_view+",
|
||||
)
|
||||
|
||||
extract_parent_view >> dbt_build_parent_view
|
||||
|
||||
|
||||
# ── Annual DAG (IDACI Deprivation) ────────────────────────────────────
|
||||
|
||||
with DAG(
|
||||
|
||||
@@ -50,11 +50,6 @@ plugins:
|
||||
kind: string
|
||||
description: Ofsted Management Information download URL
|
||||
|
||||
- name: tap-uk-parent-view
|
||||
namespace: uk_parent_view
|
||||
pip_url: ./plugins/extractors/tap-uk-parent-view
|
||||
executable: tap-uk-parent-view
|
||||
|
||||
- name: tap-uk-fbit
|
||||
namespace: uk_fbit
|
||||
pip_url: ./plugins/extractors/tap-uk-fbit
|
||||
|
||||
@@ -1,18 +0,0 @@
|
||||
[build-system]
|
||||
requires = ["setuptools>=68", "wheel"]
|
||||
build-backend = "setuptools.build_meta"
|
||||
|
||||
[project]
|
||||
name = "tap-uk-parent-view"
|
||||
version = "0.1.0"
|
||||
description = "Singer tap for UK Ofsted Parent View survey data"
|
||||
requires-python = ">=3.10"
|
||||
dependencies = [
|
||||
"singer-sdk~=0.53",
|
||||
"requests>=2.31",
|
||||
"pandas>=2.0",
|
||||
"openpyxl>=3.1",
|
||||
]
|
||||
|
||||
[project.scripts]
|
||||
tap-uk-parent-view = "tap_uk_parent_view.tap:TapUKParentView.cli"
|
||||
@@ -1 +0,0 @@
|
||||
"""tap-uk-parent-view: Singer tap for Ofsted Parent View survey data."""
|
||||
@@ -1,151 +0,0 @@
|
||||
"""Parent View Singer tap — extracts survey data from Ofsted Parent View open data portal."""
|
||||
|
||||
from __future__ import annotations
|
||||
|
||||
import io
|
||||
import re
|
||||
from datetime import date
|
||||
|
||||
import pandas as pd
|
||||
import requests
|
||||
from singer_sdk import Stream, Tap
|
||||
from singer_sdk import typing as th
|
||||
|
||||
OPEN_DATA_PAGE = "https://parentview.ofsted.gov.uk/open-data"
|
||||
|
||||
|
||||
def _positive_pct(row: pd.Series, q_col_base: str) -> float | None:
|
||||
"""Sum 'Strongly agree' + 'Agree' percentages for a question."""
|
||||
strongly = row.get(f"{q_col_base} - Strongly agree %") or row.get(f"{q_col_base} - Strongly Agree %")
|
||||
agree = row.get(f"{q_col_base} - Agree %")
|
||||
try:
|
||||
total = 0.0
|
||||
if pd.notna(strongly):
|
||||
total += float(strongly)
|
||||
if pd.notna(agree):
|
||||
total += float(agree)
|
||||
return round(total, 1) if total > 0 else None
|
||||
except (TypeError, ValueError):
|
||||
return None
|
||||
|
||||
|
||||
class ParentViewStream(Stream):
|
||||
"""Stream: Parent View survey responses per school."""
|
||||
|
||||
name = "parent_view"
|
||||
primary_keys = ["urn"]
|
||||
replication_key = None
|
||||
|
||||
schema = th.PropertiesList(
|
||||
th.Property("urn", th.IntegerType, required=True),
|
||||
th.Property("survey_date", th.StringType),
|
||||
th.Property("total_responses", th.IntegerType),
|
||||
th.Property("q_happy_pct", th.NumberType),
|
||||
th.Property("q_safe_pct", th.NumberType),
|
||||
th.Property("q_behaviour_pct", th.NumberType),
|
||||
th.Property("q_bullying_pct", th.NumberType),
|
||||
th.Property("q_communication_pct", th.NumberType),
|
||||
th.Property("q_progress_pct", th.NumberType),
|
||||
th.Property("q_teaching_pct", th.NumberType),
|
||||
th.Property("q_information_pct", th.NumberType),
|
||||
th.Property("q_curriculum_pct", th.NumberType),
|
||||
th.Property("q_future_pct", th.NumberType),
|
||||
th.Property("q_leadership_pct", th.NumberType),
|
||||
th.Property("q_wellbeing_pct", th.NumberType),
|
||||
th.Property("q_recommend_pct", th.NumberType),
|
||||
).to_dict()
|
||||
|
||||
def _discover_download_url(self) -> str:
|
||||
"""Scrape the open data page for the download link."""
|
||||
resp = requests.get(OPEN_DATA_PAGE, timeout=30)
|
||||
resp.raise_for_status()
|
||||
urls = re.findall(r'href="([^"]+\.(?:xlsx|csv|zip))"', resp.text, re.IGNORECASE)
|
||||
if not urls:
|
||||
msg = "No download link found on Parent View open data page"
|
||||
raise RuntimeError(msg)
|
||||
url = urls[0]
|
||||
if not url.startswith("http"):
|
||||
url = "https://parentview.ofsted.gov.uk" + url
|
||||
return url
|
||||
|
||||
def get_records(self, context):
|
||||
url = self._discover_download_url()
|
||||
self.logger.info("Downloading Parent View data: %s", url)
|
||||
|
||||
resp = requests.get(url, timeout=120)
|
||||
resp.raise_for_status()
|
||||
|
||||
if url.endswith(".xlsx"):
|
||||
df = pd.read_excel(io.BytesIO(resp.content))
|
||||
else:
|
||||
df = pd.read_csv(
|
||||
io.BytesIO(resp.content),
|
||||
encoding="latin-1",
|
||||
low_memory=False,
|
||||
)
|
||||
|
||||
# Normalise URN column
|
||||
urn_col = next((c for c in df.columns if c.strip().upper() == "URN"), None)
|
||||
if not urn_col:
|
||||
self.logger.error("URN column not found. Columns: %s", list(df.columns)[:20])
|
||||
return
|
||||
|
||||
df.rename(columns={urn_col: "urn"}, inplace=True)
|
||||
df["urn"] = pd.to_numeric(df["urn"], errors="coerce")
|
||||
df = df.dropna(subset=["urn"])
|
||||
|
||||
# Find total responses column
|
||||
resp_col = next(
|
||||
(c for c in df.columns if "total" in c.lower() and "respon" in c.lower()),
|
||||
None,
|
||||
)
|
||||
|
||||
today = date.today().isoformat()
|
||||
|
||||
for _, row in df.iterrows():
|
||||
try:
|
||||
urn = int(row["urn"])
|
||||
except (ValueError, TypeError):
|
||||
continue
|
||||
|
||||
total = None
|
||||
if resp_col and pd.notna(row.get(resp_col)):
|
||||
try:
|
||||
total = int(row[resp_col])
|
||||
except (ValueError, TypeError):
|
||||
pass
|
||||
|
||||
yield {
|
||||
"urn": urn,
|
||||
"survey_date": today,
|
||||
"total_responses": total,
|
||||
"q_happy_pct": _positive_pct(row, "Q1"),
|
||||
"q_safe_pct": _positive_pct(row, "Q2"),
|
||||
"q_behaviour_pct": _positive_pct(row, "Q3"),
|
||||
"q_bullying_pct": _positive_pct(row, "Q4"),
|
||||
"q_communication_pct": _positive_pct(row, "Q5"),
|
||||
"q_progress_pct": _positive_pct(row, "Q7"),
|
||||
"q_teaching_pct": _positive_pct(row, "Q8"),
|
||||
"q_information_pct": _positive_pct(row, "Q9"),
|
||||
"q_curriculum_pct": _positive_pct(row, "Q10"),
|
||||
"q_future_pct": _positive_pct(row, "Q11"),
|
||||
"q_leadership_pct": _positive_pct(row, "Q12"),
|
||||
"q_wellbeing_pct": _positive_pct(row, "Q13"),
|
||||
"q_recommend_pct": _positive_pct(row, "Q14"),
|
||||
}
|
||||
|
||||
|
||||
class TapUKParentView(Tap):
|
||||
"""Singer tap for UK Ofsted Parent View."""
|
||||
|
||||
name = "tap-uk-parent-view"
|
||||
config_jsonschema = th.PropertiesList(
|
||||
th.Property("download_url", th.StringType, description="Direct URL to Parent View data file"),
|
||||
).to_dict()
|
||||
|
||||
def discover_streams(self):
|
||||
return [ParentViewStream(self)]
|
||||
|
||||
|
||||
if __name__ == "__main__":
|
||||
TapUKParentView.cli()
|
||||
@@ -105,12 +105,6 @@ models:
|
||||
- name: year
|
||||
tests: [not_null]
|
||||
|
||||
- name: fact_parent_view
|
||||
description: Parent View survey responses
|
||||
columns:
|
||||
- name: urn
|
||||
tests: [not_null]
|
||||
|
||||
- name: fact_ks2_national_averages
|
||||
description: Official DfE KS2 national headline averages — one row per academic year
|
||||
columns:
|
||||
|
||||
@@ -1,20 +0,0 @@
|
||||
-- Mart: Parent View survey responses — one row per URN (latest survey)
|
||||
|
||||
select
|
||||
urn,
|
||||
survey_date,
|
||||
total_responses,
|
||||
q_happy_pct,
|
||||
q_safe_pct,
|
||||
q_behaviour_pct,
|
||||
q_bullying_pct,
|
||||
q_communication_pct,
|
||||
q_progress_pct,
|
||||
q_teaching_pct,
|
||||
q_information_pct,
|
||||
q_curriculum_pct,
|
||||
q_future_pct,
|
||||
q_leadership_pct,
|
||||
q_wellbeing_pct,
|
||||
q_recommend_pct
|
||||
from {{ ref('stg_parent_view') }}
|
||||
@@ -53,9 +53,6 @@ sources:
|
||||
|
||||
# Phonics: no school-level data on EES (only national/LA level)
|
||||
|
||||
- name: parent_view
|
||||
description: Ofsted Parent View survey responses
|
||||
|
||||
- name: fbit_finance
|
||||
description: Financial benchmarking data from FBIT API
|
||||
|
||||
|
||||
@@ -1,30 +0,0 @@
|
||||
-- Staging model: Ofsted Parent View survey responses
|
||||
-- The tap computes positive percentages (Strongly agree + Agree) per question.
|
||||
|
||||
with source as (
|
||||
select * from {{ source('raw', 'parent_view') }}
|
||||
),
|
||||
|
||||
renamed as (
|
||||
select
|
||||
cast(urn as integer) as urn,
|
||||
cast(survey_date as date) as survey_date,
|
||||
cast(total_responses as integer) as total_responses,
|
||||
cast(q_happy_pct as numeric) as q_happy_pct,
|
||||
cast(q_safe_pct as numeric) as q_safe_pct,
|
||||
cast(q_behaviour_pct as numeric) as q_behaviour_pct,
|
||||
cast(q_bullying_pct as numeric) as q_bullying_pct,
|
||||
cast(q_communication_pct as numeric) as q_communication_pct,
|
||||
cast(q_progress_pct as numeric) as q_progress_pct,
|
||||
cast(q_teaching_pct as numeric) as q_teaching_pct,
|
||||
cast(q_information_pct as numeric) as q_information_pct,
|
||||
cast(q_curriculum_pct as numeric) as q_curriculum_pct,
|
||||
cast(q_future_pct as numeric) as q_future_pct,
|
||||
cast(q_leadership_pct as numeric) as q_leadership_pct,
|
||||
cast(q_wellbeing_pct as numeric) as q_wellbeing_pct,
|
||||
cast(q_recommend_pct as numeric) as q_recommend_pct
|
||||
from source
|
||||
where urn is not null
|
||||
)
|
||||
|
||||
select * from renamed
|
||||
@@ -0,0 +1,5 @@
|
||||
-- Retire the Ofsted Parent View feature (schema v6).
|
||||
-- The marts schema is dbt-owned; deleting the dbt model stops the table being
|
||||
-- rebuilt but does not drop the existing relation, so apply this directly
|
||||
-- against the staging and production marts databases.
|
||||
DROP TABLE IF EXISTS marts.fact_parent_view CASCADE;
|
||||
Reference in New Issue
Block a user