#!/usr/bin/env python3 """Verify an HTTP calculation directly against OFSP XLSX files, with no JS engine. Usage: python3 verify-quote.py quote.json premiums.xlsx names.xlsx areas.xlsx regions.xlsx Download the workbooks from verification.sourceFiles in the response, checking their origin against the OFSP dataset. This script never submits an offer request. Python 3 standard library only. Equal-price tie order is intentionally not certified. """ import hashlib import json import re import sys from decimal import Decimal, ROUND_HALF_UP from pathlib import Path from xml.etree import ElementTree as ET from zipfile import ZipFile NS = {'m': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'} def rows(path, sheet='sheet1'): with ZipFile(path) as archive: strings = [] if 'xl/sharedStrings.xml' in archive.namelist(): strings = [''.join(el.itertext()) for el in ET.fromstring(archive.read('xl/sharedStrings.xml')).findall('m:si', NS)] for _, el in ET.iterparse(archive.open(f'xl/worksheets/{sheet}.xml'), events=['end']): if el.tag != f"{{{NS['m']}}}row": continue values = {} for cell in el: column = re.sub(r'\d', '', cell.attrib['r']) value = cell.find('m:v', NS) value = value.text if value is not None else '' if cell.get('t') == 's': value = strings[int(value)] elif cell.get('t') == 'inlineStr': inline = cell.find('m:is', NS) value = ''.join(inline.itertext()) if inline is not None else '' values[column] = value yield int(el.attrib['r']), values el.clear() def check(condition, message): if not condition: raise ValueError(message) def verify(quote_path, premium_path, names_path, areas_path, regions_path): q = json.loads(Path(quote_path).read_text()) check(q.get('ok') is True, 'Not a successful calculation.') check(q['tariffYear'] == 2027 and q['currency'] == 'CHF' and q['amountUnit'] == 'centime', 'Unsupported year or money unit.') check(q['redistributionMonthlyCentsPerPerson'] == 475, 'Unexpected 2027 environmental credit. Check FOEN/BAFU: https://www.bafu.admin.ch/fr/taxeco22027 (CHF 57/year).') credit = 475 request = q['verification']['criteria'] check(request['year'] == 2027 and 1 <= len(request['members']) <= 12, 'Invalid request.') files = dict(zip(['Prämien_CH.xlsx', 'Tarife.xlsx', 'Einzugsgebiete.xlsx', 'praemienregionen.xlsx'], [premium_path, names_path, areas_path, regions_path])) imported = {f['file']: f['importedSha256'] for f in q['verification']['sourceFiles']} hashes = {name: {'sha256': hashlib.sha256(Path(path).read_bytes()).hexdigest()} for name, path in files.items()} for name, value in hashes.items(): value['matchesImportedEdition'] = value['sha256'] == imported[name] names = {(int(r['A']), r['E']): (r['H'].strip(), r['F']) for i, r in rows(names_path) if i > 1 and r.get('D') == 'MOD'} restrictions, area_keys = {}, set() for i, r in rows(areas_path): if i == 1: continue check(r['D'] == '2027', 'Wrong year in municipal restrictions workbook.') key = (int(r['A']), r['B'], r['F'][-1], r['G']) area_keys.add(key) if r['I'] == 'Y': restrictions[key] = set(r['J'].split(',')) places = {} for i, r in rows(regions_path, 'sheet6'): if i > 7 and r.get('B'): places.setdefault(r['B'].zfill(4), {})[r['F']] = (r['D'], r['E'], r['G']) profiles = [] for index, member in enumerate(request['members']): options = places[member['postcode']] bfs = member.get('bfs') check(not bfs or bfs in options, 'Municipality not in postcode.') selected = {bfs: options[bfs]} if bfs else options check(len({value[:2] for value in selected.values()}) == 1, 'Ambiguous premium region.') canton, region, _ = next(iter(selected.values())) if not bfs: for (_, area_canton, area_region, _), allowed in restrictions.items(): if (canton, region) == (area_canton, area_region): check(len({value in allowed for value in options}) == 1, 'Unresolved municipality affects models.') age = 2027 - member['birthYear'] check(0 <= age <= 120, 'Invalid age.') age_class = 'AKL-KIN' if age <= 18 else 'AKL-JUG' if age <= 25 else 'AKL-ERW' lookup = q['verification']['members'][index] check((lookup['canton'], lookup['premiumRegion'], lookup['ageClass'], lookup['franchiseCHF'], lookup['accident']) == (canton, region, age_class, member['franchise'], member['accident']), 'Incorrect response lookup keys.') profiles.append({'member':member, 'canton':canton, 'region':region, 'age':age_class, 'municipalities':set(selected), 'models':{}}) source_rows = 0 for i, r in rows(premium_path): if i == 1: continue source_rows += 1 check(r['D'] == '2027', 'Wrong year in premium workbook.') insurer, model = int(r['A']), r['I'] if r['B'] in ['ZE', 'ZR'] or (r['G'].endswith('KIN') and r['K'] != 'K1') or (insurer == 1542 and model == 'FV'): continue for profile in profiles: member = profile['member'] if (r['B'], r['F'][-1], r['G'][-3:], r['H'][:3], int(r['M'].split('_')[-1])) != (profile['canton'], profile['region'], profile['age'][-3:], member['accident'], member['franchise']): continue label, care_type = names[(insurer, model)] area_key = (insurer, profile['canton'], profile['region'], model) check(care_type == 'BASE' or area_key in area_keys, 'Missing official catchment area.') if area_key in restrictions and not profile['municipalities'].issubset(restrictions[area_key]): continue cents = int((Decimal(r['N']) * 100).quantize(Decimal('1'), rounding=ROUND_HALF_UP)) key = (insurer, model) check(key not in profile['models'], 'Ambiguous source tariff.') profile['models'][key] = {'gross':cents, 'net':cents-credit, 'name':label, 'type':care_type, 'row':i} for profile in profiles: member, minimums = profile['member'], {} for (insurer, _), product in profile['models'].items(): if member['careType'] == 'any' or member['careType'] == product['type']: minimums[insurer] = min(minimums.get(insurer, product['net']), product['net']) profile['minimums'] = minimums reference, kind = None, None if 'currentPremium' in member: reference, kind = int(Decimal(member['currentPremium'].replace(',', '.')) * 100), 'declared' elif 'currentProduct' in member: insurer, model = member['currentProduct'].split('|') reference, kind = profile['models'][(int(insurer), model)]['net'], 'tariff' profile['reference'], profile['referenceKind'] = reference, kind eligible = set.intersection(*(set(p['minimums']) for p in profiles)) totals = {insurer:sum(p['minimums'][insurer] for p in profiles) for insurer in eligible} check(q['eligibleInsurerCount'] == len(eligible), f"Wrong eligible insurer count: response {q['eligibleInsurerCount']}, official rows {len(eligible)}.") check(len(q['offers']) == min(10, len(eligible)), 'Wrong number of offers.') check(len({offer['insurerId'] for offer in q['offers']}) == len(q['offers']), 'Duplicate insurer.') check([offer['monthlyCents'] for offer in q['offers']] == sorted(totals.values())[:10], 'Ranking is not the ten lowest household totals.') known = sum(p['reference'] is not None for p in profiles) reference = sum(p['reference'] for p in profiles) if known == len(profiles) else None check((q['knownReferenceCount'], q['referenceMonthlyCents']) == (known, reference), 'Incorrect reference total.') verified = [] for rank, offer in enumerate(q['offers'], 1): insurer = offer['insurerId'] check(insurer in eligible and offer['rank'] == rank, 'Invalid insurer or rank.') check(offer['monthlyCents'] == totals[insurer] and offer['annualCents'] == totals[insurer]*12, 'Wrong household total.') saving = None if reference is None else reference-totals[insurer] check(offer['monthlySavingCents'] == saving and offer['annualSavingCents'] == (None if saving is None else saving*12), 'Wrong household savings.') check(len(offer['people']) == len(profiles), 'Missing person in offer.') for index, (person, profile) in enumerate(zip(offer['people'], profiles)): product_insurer, model = person['productKey'].split('|') check(int(product_insurer) == insurer and person['memberIndex'] == index, 'Wrong person or insurer.') product = profile['models'][(insurer, model)] check((person['modelName'], person['careType']) == (product['name'], product['type']), 'Wrong model name or category.') check(profile['member']['careType'] in ['any', product['type']], 'Model outside requested care category.') check(person['monthlyGrossCents'] == product['gross'] and person['monthlyNetCents'] == product['net'] == profile['minimums'][insurer], 'Premium differs from official tariff or lowest eligible model.') check((person['referenceMonthlyCents'], person['referenceKind'], person['monthlySavingCents']) == (profile['reference'], profile['referenceKind'], None if profile['reference'] is None else profile['reference']-product['net']), 'Wrong individual reference or savings.') verified.append({'insurerId':insurer, 'modelCode':model, 'memberIndex':index, 'premiumWorkbookRow':product['row'], 'monthlyGrossCents':product['gross'], 'monthlyNetCents':product['net']}) return {'ok':True, 'tariffYear':2027, 'sourceRowsRead':source_rows, 'eligibleInsurerCount':len(eligible), 'verifiedOffers':len(q['offers']), 'verifiedPremiums':verified, 'sourceFiles':hashes, 'redistributionSource':'https://www.bafu.admin.ch/fr/taxeco22027', 'limitations':['No live Priminfo UI quotation obtained.', 'Equal-price tie order not certified.', 'Binary differences are reported; the selected source rows and ranking are checked independently. Verify source year and download origin.']} if __name__ == '__main__': try: if len(sys.argv) != 6: raise ValueError(__doc__) print(json.dumps(verify(*sys.argv[1:]), indent=2, ensure_ascii=False)) except (ValueError, KeyError, IndexError, OSError) as error: print(json.dumps({'ok':False, 'error':str(error)}), file=sys.stderr) sys.exit(1)