<?php
if (!defined(‘ABSPATH’)) exit;
class STQ_Calc {
/* ——————– basic lookups (existing) ——————– */
public static function rate_set_id($name){
global $wpdb; if (is_numeric($name)) return intval($name);
$id = $wpdb->get_var($wpdb->prepare(“SELECT id FROM {$wpdb->prefix}stq_rate_sets WHERE name=%s”,$name));
if (!$id){ $id = $wpdb->get_var(“SELECT id FROM {$wpdb->prefix}stq_rate_sets ORDER BY year DESC, quarter DESC LIMIT 1”); }
return intval($id);
}
public static function get_plans($rate_set_id,$area,$carrier=null){
global $wpdb;
$sql = $wpdb->prepare(“SELECT r.carrier, r.plan_code, p.marketing_name, p.metal, p.funding, p.network_name, p.network_type, p.plan_type
FROM {$wpdb->prefix}stq_plan_rates r
JOIN {$wpdb->prefix}stq_plans p ON p.year=%d AND p.carrier=r.carrier AND p.plan_code=r.plan_code
WHERE r.rate_set_id=%d AND r.rating_area_id=%d”, 2025, $rate_set_id, $area);
if ($carrier){ $sql .= $wpdb->prepare(” AND r.carrier=%s”, $carrier); }
$sql .= ” ORDER BY r.carrier, CAST(SUBSTRING(r.plan_code,2) AS UNSIGNED) ASC”;
return $wpdb->get_results($sql, ARRAY_A);
}
/* Map friendly headers → canonical keys the UI expects */
private static function canonicalize_benefits(array $b){
$map = [
‘oon_ded_individual’ => ‘OON Deductible – Individual’,
‘oon_ded_family’ => ‘OON Deductible – Family’,
‘oon_coins_member’ => ‘OON Coinsurance – Member’,
‘oon_oop_individual’ => ‘OON Out-of-Pocket – Individual’,
‘oon_oop_family’ => ‘OON Out-of-Pocket – Family’,
];
foreach ($map as $canon => $friendly){
if ((!isset($b[$canon]) || $b[$canon]===”) && isset($b[$friendly]) && $b[$friendly]!==”){
$b[$canon] = $b[$friendly];
}
}
return $b;
}
/* Prefer the row that actually has any OON values (handles duplicates) */
public static function benefits($carrier,$plan_code){
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
“SELECT * FROM {$wpdb->prefix}stq_plan_benefits
WHERE year=%d AND carrier=%s AND plan_code=%s”,
2025, $carrier, $plan_code
), ARRAY_A);
if (!$rows) return [];
$pick = null;
foreach ($rows as $r){
$r = self::canonicalize_benefits($r);
$has_oon = (
trim((string)($r[‘oon_ded_individual’] ?? ”)) !== ” ||
trim((string)($r[‘oon_ded_family’] ?? ”)) !== ” ||
trim((string)($r[‘oon_coins_member’] ?? ”)) !== ” ||
trim((string)($r[‘oon_oop_individual’] ?? ”)) !== ” ||
trim((string)($r[‘oon_oop_family’] ?? ”)) !== ”
);
if ($has_oon) { $pick = $r; break; }
if ($pick === null) $pick = $r; // fall back to first
}
return $pick;
}
public static function rate21($rate_set_id,$area){
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(“SELECT plan_code, rate21_eo FROM {$wpdb->prefix}stq_plan_rates WHERE rate_set_id=%d AND rating_area_id=%d”,$rate_set_id,$area), ARRAY_A);
$m=[]; foreach($rows as $r){ $m[$r[‘plan_code’]]=floatval($r[‘rate21_eo’]); } return $m;
}
public static function derive_amounts($known_plan,$known_eo,$rate_map){
$out=[]; if(!isset($rate_map[$known_plan])) return $out;
foreach($rate_map as $pc=>$r21){
$eo = ($r21/$rate_map[$known_plan]) * $known_eo;
$out[$pc]=[‘EO’=>$eo,’ES’=>2*$eo,’EC’=>2*$eo,’EF’=>3*$eo];
}
return $out;
}
public static function rate_set_exists($rate_set){
$rate_set_id = self::rate_set_id($rate_set);
if (!$rate_set_id) return false;
global $wpdb;
$sql = $wpdb->prepare(“SELECT 1 FROM {$wpdb->prefix}stq_plan_rates WHERE rate_set_id=%d LIMIT 1″, $rate_set_id);
$exists = (int)$wpdb->get_var($sql);
return $exists === 1;
}
/* ———————— NEW: file loaders ———————– */
private static function uploads_base(): string {
$upload = function_exists(‘wp_get_upload_dir’) ? wp_get_upload_dir() : [‘basedir’=>WP_CONTENT_DIR.’/uploads’];
return rtrim($upload[‘basedir’],’/\\’);
}
private static function try_paths(string $basename, string $rate_set = ”): array {
$base = self::uploads_base().’/stq-data’;
$paths = [];
if ($rate_set !== ”) $paths[] = $base.’/’.preg_replace(‘/[^A-Za-z0-9_\-]/’,”,$basename.’_’.$rate_set.’.csv’);
$paths[] = $base.’/’.$basename.’.csv’;
return $paths;
}
/** Load age curve (age,factor) — prefers age_curve_{rate_set}.csv then age_curve.csv */
private static function load_age_curve(string $rate_set = ”): array {
foreach (self::try_paths(‘age_curve’, $rate_set) as $p){
if (!is_readable($p)) continue;
$fh = @fopen($p,’r’); if(!$fh) continue;
$hdr = fgetcsv($fh); if(!$hdr){ fclose($fh); continue; }
$idx = array_change_key_case(array_flip($hdr), CASE_LOWER);
$ageCol = $idx[‘age’] ?? null; $factorCol = $idx[‘factor’] ?? null;
if ($ageCol===null || $factorCol===null){ fclose($fh); continue; }
$map=[];
while(($row=fgetcsv($fh))!==false){
$a = (int)trim((string)$row[$ageCol]);
$f = (float)trim((string)$row[$factorCol]);
if ($a>=0 && $a<=120 && $f>0) $map[$a]=$f;
}
fclose($fh);
if ($map) return $map;
}
return []; // caller decides what to do if empty
}
/** Load tier weights (tier,weight) — prefers tier_weights_{rate_set}.csv then tier_weights.csv */
private static function load_tier_weights(string $rate_set = ”): array {
foreach (self::try_paths(‘tier_weights’, $rate_set) as $p){
if (!is_readable($p)) continue;
$fh=@fopen($p,’r’); if(!$fh) continue;
$hdr=fgetcsv($fh); if(!$hdr){ fclose($fh); continue; }
$idx = array_change_key_case(array_flip($hdr), CASE_LOWER);
$tCol=$idx[‘tier’] ?? null; $wCol=$idx[‘weight’] ?? null;
if ($tCol===null || $wCol===null){ fclose($fh); continue; }
$out=[];
while(($row=fgetcsv($fh))!==false){
$tier=strtoupper(trim((string)$row[$tCol]));
$w =(float)trim((string)$row[$wCol]);
if ($tier!==” && $w>0) $out[$tier]=$w;
}
fclose($fh);
if ($out) return $out;
}
// fallback defaults
return [‘EO’=>1.0,’ES’=>2.0,’EC’=>2.0,’EF’=>3.0];
}
/* ——————- NEW: census file reader ——————— */
/**
* Read a census CSV into normalized rows.
* Accepts flexible headers:
* – “Employee ID” or “Subscriber ID” or “Employee” (for family grouping)
* – “Relationship” (Employee/Spouse/Child/Other)
* – “Age” OR “DOB”
* – “Waive” (Y/N/Yes/No)
*/
private static function read_census_csv(string $path): array {
if (!is_readable($path)) return [];
$fh=@fopen($path,’r’); if(!$fh) return [];
$hdr=fgetcsv($fh); if(!$hdr){ fclose($fh); return []; }
$idx = array_change_key_case(array_flip($hdr), CASE_LOWER);
$idCol = $idx[’employee id’] ?? $idx[‘subscriber id’] ?? $idx[’employee’] ?? $idx[‘subscriber’] ?? null;
$relCol= $idx[‘relationship’] ?? null;
$ageCol= $idx[‘age’] ?? null;
$dobCol= $idx[‘dob’] ?? $idx[‘date of birth’] ?? $idx[‘birthdate’] ?? null;
$wavCol= $idx[‘waive’] ?? $idx[‘waived’] ?? $idx[‘decline’] ?? null;
$rows=[];
while(($row=fgetcsv($fh))!==false){
$emp = $idCol!==null ? trim((string)$row[$idCol]) : ”;
$rel = $relCol!==null ? trim((string)$row[$relCol]) : ”;
$wav = $wavCol!==null ? trim((string)$row[$wavCol]) : ”;
$age = null; $dob = null;
if ($ageCol!==null) {
$age = (int)preg_replace(‘/[^0-9]/’,”,(string)$row[$ageCol]);
} elseif ($dobCol!==null) {
$dob = trim((string)$row[$dobCol]);
}
$rows[] = [’emp’=>$emp, ‘rel’=>$rel, ‘age’=>$age, ‘dob’=>$dob, ‘waive’=>$wav];
}
fclose($fh);
return $rows;
}
/** compute age as of $effective (YYYY-MM-DD) */
private static function age_as_of(?int $age, ?string $dob, string $effective): ?int {
if (is_int($age) && $age>0) return min(120,$age);
if (!$dob) return null;
$t = strtotime($dob); $e = strtotime($effective ?: ‘today’);
if (!$t || !$e) return null;
$y = (int)date(‘Y’,$e) – (int)date(‘Y’,$t);
// adjust if birthday not yet reached
$bd = (int)date(‘md’,$t); $ed=(int)date(‘md’,$e);
if ($ed < $bd) $y–;
return max(0,min(120,$y));
}
/* —————- NEW: true census → composite anchor ————- */
/**
* Build a TRUE composite anchor (EO/ES/EC/EF) for a chosen plan from a member census.
* Steps:
* 1) Load age curve & tier weights (with per-rate-set override support).
* 2) Choose anchor plan:
* – if $anchor_plan provided & present in rate21 map, use it
* – else pick the first plan_code from $plan_codes that exists in rate21 map.
* 3) Age-rate every non-waived member using rate21(anchor) * factor(age)
* (child-4+ rule enforced per family: only first 3 <21 children count)
* 4) Back-solve EO from group total using tier weights: EO = total / (EO*wEO + ES*wES + EC*wEC + EF*wEF)
* 5) Use derive_amounts(anchor_plan, EO, rate21_map) to get tiers for all plans.
*
* @return array [‘anchor_plan’=>…, ‘eo’=>float, ‘amounts’=>[plan=>[‘EO’=>..,’ES’=>..,’EC’=>..,’EF’=>..], …]]
*/
public static function anchor_from_census_file(
int $rate_set_id,
int $area,
array $plan_codes,
string $census_path,
string $effective = ”,
string $anchor_plan = ”
): array {
$ageCurve = self::load_age_curve(self::rate_set_name($rate_set_id));
$weights = self::load_tier_weights(self::rate_set_name($rate_set_id));
$rate21 = self::rate21($rate_set_id, $area);
if (empty($rate21)) return [];
if (empty($ageCurve)) return []; // require upload
// choose anchor
$anchor = ”;
if ($anchor_plan && isset($rate21[$anchor_plan])) $anchor = $anchor_plan;
if ($anchor === ”) {
foreach ($plan_codes as $pc) { if (isset($rate21[$pc])) { $anchor = $pc; break; } }
}
if ($anchor === ”) return [];
$rows = self::read_census_csv($census_path);
if (empty($rows)) return [];
// normalize families for child-4+ rule
$families = []; // emp_id => [ members[] ]
foreach ($rows as $r){
// skip waived
$wav = strtolower(trim((string)$r[‘waive’]));
if (in_array($wav, [‘y’,’yes’,’waive’,’waived’,’true’,’1′], true)) continue;
$emp = (string)$r[’emp’];
if ($emp === ”) $emp = ‘_grp_’.md5(json_encode($r)); // best-effort grouping if no id
$families[$emp] = $families[$emp] ?? [];
$families[$emp][] = $r;
}
// Build age-rated total for anchor plan (rate21(anchor) * factor(age))
$r21_anchor = (float)$rate21[$anchor];
$total = 0.0;
$tierCounts = [‘EO’=>0,’ES’=>0,’EC’=>0,’EF’=>0];
foreach ($families as $members){
// count dependents and enforce child-4+ (<21) cap
$dependents = [];
$employee = null;
foreach ($members as $m){
$age = self::age_as_of($m[‘age’], $m[‘dob’], $effective);
$rel = strtolower(trim((string)$m[‘rel’]));
$rec = [‘age’=>$age, ‘rel’=>$rel];
if ($rel===’employee’ || $rel===’emp’ || $rel===’subscriber’ || $rel===’self’) $employee = $rec;
else $dependents[] = $rec;
}
// apply child-4+ cap to dependents
usort($dependents, function($a,$b){ return ($a[‘age’]??0) <=> ($b[‘age’]??0); }); // youngest first
$children_u21 = array_filter($dependents, fn($d)=> isset($d[‘age’]) && $d[‘age’] < 21);
if (count($children_u21) > 3) {
// mark extras as free (age factor = 0)
$excess = array_slice($children_u21, 3); // beyond the first 3
foreach ($excess as &$e) { $e[‘age’] = -1; } // sentinel to zero out
}
// determine tier for this family for the composite equation
$hasSpouse = !!array_filter($dependents, fn($d)=> in_array($d[‘rel’], [‘spouse’,’partner’,’domestic partner’,’dp’,’sp’], true));
$childCount = count(array_filter($dependents, fn($d)=> isset($d[‘age’]) && $d[‘age’] >= 0 && $d[‘rel’]!==’spouse’ && $d[‘rel’]!==’partner’ && $d[‘rel’]!==’domestic partner’ && $d[‘rel’]!==’dp’ && $d[‘rel’]!==’sp’));
if ($employee) {
if ($hasSpouse && $childCount>0) $tierCounts[‘EF’]++;
elseif ($hasSpouse) $tierCounts[‘ES’]++;
elseif ($childCount>0) $tierCounts[‘EC’]++;
else $tierCounts[‘EO’]++;
}
// age-rate all counted members in this family
$familyTotal = 0.0;
$to_rate = [];
if ($employee && isset($employee[‘age’])) $to_rate[] = $employee[‘age’];
foreach ($dependents as $d){
if (!isset($d[‘age’])) continue;
$to_rate[] = $d[‘age’];
}
foreach ($to_rate as $age){
if ($age < 0) continue; // free child (4+)
$factor = $ageCurve[$age] ?? null;
if ($factor === null) {
// try nearest available age in curve (graceful degradation)
$nearest = null; $bestDelta = 999;
foreach ($ageCurve as $a=>$f){ $delta = abs($a – $age); if ($delta < $bestDelta){ $bestDelta=$delta; $nearest=$f; } }
$factor = $nearest ?? 0.0;
}
$familyTotal += $r21_anchor * (float)$factor;
}
$total += $familyTotal;
}
// Back-solve EO using composite weights
$wEO = (float)($weights[‘EO’] ?? 1.0);
$wES = (float)($weights[‘ES’] ?? 2.0);
$wEC = (float)($weights[‘EC’] ?? 2.0);
$wEF = (float)($weights[‘EF’] ?? 3.0);
$den = $tierCounts[‘EO’]*$wEO + $tierCounts[‘ES’]*$wES + $tierCounts[‘EC’]*$wEC + $tierCounts[‘EF’]*$wEF;
if ($den <= 0) return [];
$eo = $total / $den;
// derive all plans from this anchor EO
$amounts = self::derive_amounts($anchor, $eo, $rate21);
return [
‘anchor_plan’ => $anchor,
‘eo’ => $eo,
‘amounts’ => $amounts,
‘tiers’ => $tierCounts,
];
}
/** Reverse-lookup: rate set “name” string for per-RS file overrides */
private static function rate_set_name(int $rate_set_id): string {
global $wpdb;
$name = (string)$wpdb->get_var($wpdb->prepare(“SELECT name FROM {$wpdb->prefix}stq_rate_sets WHERE id=%d”, $rate_set_id));
return trim($name);
}
}