<?php
// File: wp-content/plugins/stella-quotes/includes/class-stq-export-compare.php
if (!defined(‘ABSPATH’)) exit;
/**
* Stella Quotes — Export (Compare) — SPEED+CACHE PATCH
* – Direct-write into template (preserve styles)
* – XLSX writer tuning (ZipArchive, compression 0, no precalc)
* – File cache for repeat exports (date + template aware)
* – dry_save timing mode (skips edits)
* – nocache=1 bypass (skip cache read/write)
*/
class STQ_Export_Compare {
/* ————————— bootstrap ————————— */
public static function init(){
add_action(‘admin_post_stq_export_compare_xlsx’, [__CLASS__,’handle_xlsx’]);
add_action(‘admin_post_nopriv_stq_export_compare_xlsx’, [__CLASS__,’handle_xlsx’]);
add_action(‘admin_post_stq_export_compare_pdf’, [__CLASS__,’handle_pdf’]);
add_action(‘admin_post_nopriv_stq_export_compare_pdf’, [__CLASS__,’handle_pdf’]);
add_action(‘admin_post_stq_export_compare_html’, [__CLASS__,’handle_html’]);
add_action(‘admin_post_nopriv_stq_export_compare_html’, [__CLASS__,’handle_html’]);
add_action(‘admin_post_stq_export_compare_ping’, [__CLASS__,’handle_ping’]);
add_action(‘admin_post_nopriv_stq_export_compare_ping’, [__CLASS__,’handle_ping’]);
}
/* —————- safety / streaming helpers ——————– */
private static function clear_output_buffers(){
@ini_set(‘zlib.output_compression’,’0′);
if (function_exists(‘apache_setenv’)) @apache_setenv(‘no-gzip’,’1′);
while (ob_get_level() > 0) { @ob_end_clean(); }
}
private static function send_download_headers($filename, $contentType){
header(‘Content-Type: ‘.$contentType);
header(‘Content-Disposition: attachment; filename=”‘. $filename .'”‘);
header(‘Content-Transfer-Encoding: binary’);
header(‘Cache-Control: private, max-age=0, must-revalidate’);
header(‘Pragma: public’);
header(‘Expires: 0’);
}
/* ——————– template path helper ———————- */
private static function resolve_template_path(){
$p1 = WP_CONTENT_DIR . ‘/uploads/stq-templates/Sample Output Pages.xlsx’;
$upload = function_exists(‘wp_get_upload_dir’) ? wp_get_upload_dir() : [‘basedir’ => WP_CONTENT_DIR . ‘/uploads’];
$p2 = rtrim($upload[‘basedir’], ‘/\\’) . ‘/stq-templates/Sample Output Pages.xlsx’;
if (file_exists($p1)) return $p1;
if (file_exists($p2)) return $p2;
return ”;
}
/* —————- matrix for compare table ———————- */
private static function build_compare_matrix($plans, $rate_set, $area){
$rate_set_id = class_exists(‘STQ_Calc’) ? STQ_Calc::rate_set_id($rate_set) : 0;
$area = intval($area);
global $wpdb;
$cols = [];
foreach ((array)$plans as $pc){
$p = $wpdb->get_row($wpdb->prepare(
“SELECT * FROM {$wpdb->prefix}stq_plans WHERE year=%d AND plan_code=%s”, 2025, $pc
), ARRAY_A);
if (!$p) continue;
$b = class_exists(‘STQ_Calc’) ? STQ_Calc::benefits($p[‘carrier’], $pc) : [];
$cols[$pc] = [‘plan’=>$p, ‘benefits’=>$b];
}
$rows = [
‘Plan #’ => function($c,$pc){ return $pc; },
‘Plan Name’ => function($c){ return $c[‘plan’][‘marketing_name’]; },
‘Network Name’ => function($c){ return $c[‘plan’][‘network_name’]; },
‘Network Type’ => function($c){ return $c[‘plan’][‘network_type’]; },
‘Plan Type’ => function($c){ return $c[‘plan’][‘plan_type’]; },
‘Tier’ => function($c){
$raw = isset($c[‘plan’][‘metal’]) ? (string)$c[‘plan’][‘metal’] : ”;
$m = strtoupper(trim($raw));
switch ($m) {
case ‘P’: case ‘PLATINUM’: return ‘Platinum’;
case ‘G’: case ‘GOLD’: return ‘Gold’;
case ‘S’: case ‘SILVER’: return ‘Silver’;
case ‘B’: case ‘BRONZE’: return ‘Bronze’;
default: return $raw;
}
},
‘Metal’ => function($c){
$raw = isset($c[‘plan’][‘metal’]) ? (string)$c[‘plan’][‘metal’] : ”;
$t = strtoupper(trim($raw));
if (in_array($t, [‘P’,’G’,’S’,’B’], true)) return $t;
return [‘PLATINUM’=>’P’,’GOLD’=>’G’,’SILVER’=>’S’,’BRONZE’=>’B’][$t] ?? $raw;
},
‘Deductible – Individual’ => function($c){ return STQ_Format::money($c[‘benefits’][‘deductible_individual’]); },
‘Deductible – Family’ => function($c){ return STQ_Format::money($c[‘benefits’][‘deductible_family’]); },
‘Coinsurance – Member’ => function($c){ return STQ_Format::coins($c[‘benefits’][‘coinsurance_member’]); },
‘Coinsurance – Plan’ => function($c){ return STQ_Format::coins($c[‘benefits’][‘coinsurance_plan’]); },
‘Out-of-Pocket – Individual’ => function($c){ return STQ_Format::money($c[‘benefits’][‘oop_individual’]); },
‘Out-of-Pocket – Family’ => function($c){ return STQ_Format::money($c[‘benefits’][‘oop_family’]); },
‘Virtual Visits’ => function($c){ return STQ_Format::money($c[‘benefits’][‘virtual_visits’]); },
‘PCP <19’ => function($c){ return STQ_Format::money($c[‘benefits’][‘pcp_under19’]); },
‘Primary’ => function($c){ return STQ_Format::money($c[‘benefits’][‘primary_adult’]); },
‘Specialist’ => function($c){ return STQ_Format::money($c[‘benefits’][‘specialist’]); },
‘Urgent Care’ => function($c){ return STQ_Format::money($c[‘benefits’][‘urgent_care’]); },
‘Emergency’ => function($c){ return STQ_Format::money($c[‘benefits’][’emergency’]); },
‘Lab/X-Ray’ => function($c){ return STQ_Format::money($c[‘benefits’][‘lab_xray’]); },
‘Imaging’ => function($c){ return STQ_Format::money($c[‘benefits’][‘imaging’]); },
‘Inpatient Services’ => function($c){ return STQ_Format::money($c[‘benefits’][‘inpatient’]); },
‘Outpatient Surgery’ => function($c){ return STQ_Format::money($c[‘benefits’][‘outpatient_surgery’]); },
‘Prescriptions’ => function($c){ return $c[‘benefits’][‘rx_string’]; },
‘OON Deductible (Individual/Family)’ => function($c){
return STQ_Format::na_if_oonsuppressed_pair(
$c[‘benefits’][‘oon_ded_individual’] ?? ”,
$c[‘benefits’][‘oon_ded_family’] ?? ”,
$c[‘plan’][‘network_type’]
);
},
‘OON Coinsurance (Member)’ => function($c){
return STQ_Format::na_if_oonsuppressed_coins(
$c[‘benefits’][‘oon_coins_member’] ?? ”,
$c[‘plan’][‘network_type’]
);
},
‘OON Out-of-Pocket (Individual/Family)’ => function($c){
return STQ_Format::na_if_oonsuppressed_pair(
$c[‘benefits’][‘oon_oop_individual’] ?? ”,
$c[‘benefits’][‘oon_oop_family’] ?? ”,
$c[‘plan’][‘network_type’]
);
},
];
$matrix = [];
$header = [”];
foreach (array_keys($cols) as $pc){
$header[] = $pc.’ — ‘.$cols[$pc][‘plan’][‘marketing_name’];
}
$matrix[] = $header;
foreach ($rows as $label => $fn){
$line = [$label];
foreach ($cols as $pc => $col){
$val = call_user_func($fn, $col, $pc);
$line[] = (string)$val;
}
$matrix[] = $line;
}
return $matrix;
}
/* ———————— small utilities ———————– */
private static function norm(string $s): string {
return strtolower(preg_replace(‘/[^a-z0-9]+/i’, ”, $s));
}
private static function find_marker(
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet,
string $token,
string $maxCol = ‘ZZ’,
int $maxRow = 1000
): ?array {
$want = trim($token);
$maxC = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($maxCol);
for ($r = 1; $r <= $maxRow; $r++) {
for ($c = 1; $c <= $maxC; $c++) {
$addr = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($c).$r;
$val = trim((string)$sheet->getCell($addr)->getValue());
if ($val === $want) {
$sheet->setCellValue($addr, ”); // clear the marker
return [$c, $r];
}
}
}
return null;
}
private static function build_label_row_map(
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet,
int $labelCol,
int $rowMin,
int $rowMax
): array {
$map = [];
$rowMin = max(1, $rowMin); $rowMax = max($rowMin, $rowMax);
for ($r = $rowMin; $r <= $rowMax; $r++) {
$v = (string)$sheet->getCellByColumnAndRow($labelCol, $r)->getValue();
if ($v === ”) continue;
$map[self::norm($v)] = $r;
}
return $map;
}
private static function row_alias_reverse(): array {
static $rev = null;
if ($rev !== null) return $rev;
$aliases = [
‘plannumber’ => [‘plan#’,’planno’,’plan id’,’planid’],
‘planname’ => [‘name’],
‘tier’ => [‘metal’],
‘networkname’ => [‘network’],
‘networktype’ => [‘ntwk type’,’type’],
‘plantype’ => [‘plan type’,’type’],
‘deductibleindividual’ => [‘ded-ind’,’dedind’,’ded_i’,’individual’],
‘deductiblefamily’ => [‘ded-fam’,’dedfam’,’ded_f’,’family’],
‘outofpocketindividual’ => [‘oop-ind’,’oopind’,’oop_i’],
‘outofpocketfamily’ => [‘oop-fam’,’oopfam’,’oop_f’],
‘coinsurancemember’ => [‘coins-mem’,’coinsmember’,’coins% member’],
‘coinsuranceplan’ => [‘coins-plan’,’coinsplan’,’coins% plan’],
‘virtualvisits’ => [‘telemed’,’virtual’],
‘pcp<19’ => [‘pcp<19′,’pcp< 19′,’pcp under19′,’pcp u19’],
‘primary’ => [‘pcp’,’pcp adult’,’primary care’],
‘specialist’ => [‘spec’],
‘urgentcare’ => [‘urgent’,’uc’],
’emergency’ => [‘er’,’emergencyroom’,’emerg’],
‘labxray’ => [‘lab/xray’,’lab & xray’,’lab’],
‘imaging’ => [‘mri/ct’,’radiology’],
‘inpatientservices’ => [‘inpatient’,’ip’],
‘outpatientsurgery’ => [‘outpatient’,’op surg’,’ambulatory surg’],
‘prescriptions’ => [‘rx’,’pharmacy’],
‘oondeductibleindividualfamily’ => [‘oon ded (ind/fam)’,’oon ded’],
‘ooncoinsurancemember’ => [‘oon coins’,’oon coins (mem)’],
‘oonoutofpocketindividualfamily’=> [‘oon oop (ind/fam)’,’oon oop’],
];
$rev = [];
foreach ($aliases as $canonical => $list) {
$key = self::norm($canonical);
$rev[$key] = $key; // self
foreach ($list as $aka) $rev[self::norm($aka)] = $key;
}
return $rev;
}
private static function build_header_col_map(
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet,
int $headerRow,
int $colMin = 1,
int $colMax = 100
): array {
$rev = self::row_alias_reverse();
$map = [];
for ($c = $colMin; $c <= $colMax; $c++) {
$raw = (string)$sheet->getCellByColumnAndRow($c, $headerRow)->getValue();
if ($raw === ”) continue;
$h = self::norm($raw);
$canonical = $rev[$h] ?? $h;
$map[$canonical] = $c;
}
return $map;
}
/* ——————– request + placeholders ——————– */
private static function read_request(){
$plans = isset($_REQUEST[‘plans’]) ? explode(‘,’, sanitize_text_field($_REQUEST[‘plans’])) : [];
$plans = array_values(array_filter(array_map(‘trim’, $plans)));
$rate_set = isset($_REQUEST[‘rate_set’]) ? sanitize_text_field($_REQUEST[‘rate_set’]) : ”;
$area = isset($_REQUEST[‘area’]) ? intval($_REQUEST[‘area’]) : 1;
if (!wp_verify_nonce($_REQUEST[‘_stq_export_nonce’] ?? ”, ‘stq_export_compare’)) {
wp_die(‘Invalid export request (nonce).’);
}
return [$plans, $rate_set, $area];
}
private static function replace_placeholders(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, array $ctx): void {
$maxCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString(‘BF’);
$maxRow = 120;
for ($r = 1; $r <= $maxRow; $r++) {
for ($c = 1; $c <= $maxCol; $c++) {
$cell = $sheet->getCellByColumnAndRow($c, $r);
$val = $cell ? (string)$cell->getValue() : ”;
if ($val === ” || strpos($val, ‘[[‘) === false) continue;
$replaced = strtr($val, [
‘[[STQ_EMPLOYER]]’ => (string)($ctx[’employer’] ?? ”),
‘[[STQ_COMPANY]]’ => (string)($ctx[‘company’] ?? ”),
‘[[STQ_BROKER_NAME]]’ => (string)($ctx[‘broker_name’]?? ”),
‘[[STQ_BROKER_AGENCY]]’ => (string)($ctx[‘broker_agency’] ?? ”),
‘[[STQ_BROKER_EMAIL]]’ => (string)($ctx[‘broker_email’] ?? ”),
‘[[STQ_BROKER_PHONE]]’ => (string)($ctx[‘broker_phone’] ?? ”),
‘[[STQ_BROKER_WEBSITE]]’ => (string)($ctx[‘broker_website’]?? ”),
‘[[STQ_BROKER_LINE]]’ => (string)($ctx[‘broker_line’] ?? ”),
‘[[STQ_BROKER_CONTACT]]’ => (string)($ctx[‘broker_contact’]?? ”),
‘[[STQ_EFFECTIVE]]’ => (string)($ctx[‘effective’] ?? ”),
‘[[STQ_RATE_STYLE]]’ => (string)($ctx[‘rate_style’] ?? ”), // ✅ NEW LINE
‘[[STQ_RATE_SET]]’ => (string)($ctx[‘rate_set’] ?? ”),
‘[[STQ_AREA]]’ => (string)($ctx[‘area’] ?? ”),
‘[[STQ_COUNTY]]’ => (string)($ctx[‘county’] ?? ”),
‘[[STQ_DATE_TODAY]]’ => (string)($ctx[‘today’] ?? ”),
‘[[STQ_PLANS]]’ => (string)($ctx[‘plans’] ?? ”),
‘[[STQ_DISCLAIMER]]’ => (string)($ctx[‘disclaimer’] ?? ”),
]);
if ($replaced !== $val) $cell->setValue($replaced);
}
}
}
/* ===== START: STQ handle_xlsx (paste-over) ===== */
/* —————————– XLSX —————————– */
public static function handle_xlsx(){
$t=[]; $mark=function($k)use(&$t){$t[$k]=microtime(true);};
$span=function($a,$b)use(&$t){return (isset($t[$a],$t[$b])?round($t[$b]-$t[$a],3):0);};
$loadPath=’SCAN’;
try{
$mark(‘start’);
list($plans,$rate_set,$area)=self::read_request();
$ctx = []; // ensure defined for cache key even on dry_save=1
$just_resave = !empty($_REQUEST[‘dry_save’]);
$noCache = !empty($_REQUEST[‘nocache’]);
// Only build matrix when we intend to write cells (skip in dry_save timing runs)
$matrix = [];
if (!$just_resave) {
$matrix=self::build_compare_matrix($plans,$rate_set,$area);
}
$mark(‘after_matrix’);
if(!class_exists(‘\PhpOffice\PhpSpreadsheet\Spreadsheet’)){
$csv=’Stella-Compare-‘.date(‘Ymd-His’).’.csv’;
self::clear_output_buffers();
self::send_download_headers($csv,’text/csv; charset=utf-8′);
$out=fopen(‘php://output’,’w’); foreach($matrix as $row) fputcsv($out,(array)$row); fclose($out); exit;
}
@set_time_limit(180);
$template=self::resolve_template_path();
if(!$template || !file_exists($template)){
wp_die(‘STQ export: template workbook not found at /uploads/stq-templates/Sample Output Pages.xlsx’);
}
$reader=\PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($template);
if(method_exists($reader,’setReadDataOnly’)) $reader->setReadDataOnly(false);
if(method_exists($reader,’setIncludeCharts’)) $reader->setIncludeCharts(false);
$preferred = isset($_REQUEST[‘sheet’])?sanitize_text_field($_REQUEST[‘sheet’]):”;
$srcBook=null; $wsSrc=null; $mode=null;
$startCol=$startRow=$startColR=$startRowR=null;
// FAST: load only preferred
if($preferred && method_exists($reader,’setLoadSheetsOnly’)){
try{
$reader->setLoadSheetsOnly([$preferred]);
$mark(‘before_load_fast’);
$srcBook=$reader->load($template);
$mark(‘after_load_fast’);
$wsSrc=$srcBook->getSheetByName($preferred) ?: $srcBook->getActiveSheet();
$posR=self::find_marker($wsSrc,'[[STQ_TABLE_ROWS]]’);
if($posR){ $mode=’rows’; list($startColR,$startRowR)=$posR; $loadPath=’FAST-rows’; }
else{
$posC=self::find_marker($wsSrc,'[[STQ_TABLE]]’);
if(!$posC) $posC=self::find_marker($wsSrc,'[STQ_TABLE]’);
if($posC){ $mode=’cols’; list($startCol,$startRow)=$posC; $loadPath=’FAST-cols’; }
}
if(!$mode){ $srcBook=null; $wsSrc=null; $loadPath=’SCAN’; }
}catch(\Throwable $e){
$srcBook=null; $wsSrc=null; $loadPath=’SCAN’;
}
}
// SAFE SCAN fallback
if(!$srcBook){
$reader2=\PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($template);
if(method_exists($reader2,’setReadDataOnly’)) $reader2->setReadDataOnly(false);
if(method_exists($reader2,’setIncludeCharts’)) $reader2->setIncludeCharts(false);
$mark(‘before_load_scan’);
$srcBook=$reader2->load($template);
$mark(‘after_load_scan’);
for($i=0;$i<$srcBook->getSheetCount();$i++){
$probe=$srcBook->getSheet($i);
$posR=self::find_marker($probe,'[[STQ_TABLE_ROWS]]’);
if($posR){ $wsSrc=$probe; $mode=’rows’; list($startColR,$startRowR)=$posR; break; }
$posC=self::find_marker($probe,'[[STQ_TABLE]]’);
if(!$posC) $posC=self::find_marker($probe,'[STQ_TABLE]’);
if($posC){ $wsSrc=$probe; $mode=’cols’; list($startCol,$startRow)=$posC; break; }
}
}
if(!$wsSrc){
$names=[]; if($srcBook){ for($i=0;$i<$srcBook->getSheetCount();$i++) $names[]=$srcBook->getSheet($i)->getTitle(); }
wp_die(‘STQ export: could not find [[STQ_TABLE]] or [[STQ_TABLE_ROWS]] in template. Sheets seen: ‘.esc_html(implode(‘, ‘,$names?:[‘(none)’])));
}
if (!$just_resave) {
// placeholders
$defDisclaimer=”These plan summaries are for illustration only and are not a contract. Final rates and benefits are subject to carrier approval.”;
$bk_name = isset($_REQUEST[‘broker_name’]) ? sanitize_text_field($_REQUEST[‘broker_name’]) : ”;
$bk_agency = isset($_REQUEST[‘broker_agency’]) ? sanitize_text_field($_REQUEST[‘broker_agency’]) : ”;
$bk_email = isset($_REQUEST[‘broker_email’]) ? sanitize_text_field($_REQUEST[‘broker_email’]) : ”;
$bk_phone = isset($_REQUEST[‘broker_phone’]) ? sanitize_text_field($_REQUEST[‘broker_phone’]) : ”;
$bk_web = isset($_REQUEST[‘broker_website’])? sanitize_text_field($_REQUEST[‘broker_website’]): ”;
$join=function(array $parts,string $sep=’ | ‘){
$parts=array_values(array_filter(array_map(‘trim’,$parts),fn($v)=>$v!==”));
return implode($sep,$parts);
};
// Prefer explicit effective date (from intake) when provided
$effective_req = isset($_REQUEST[‘effective’]) ? sanitize_text_field($_REQUEST[‘effective’]) : ”;
$ctx=[
’employer’ => isset($_REQUEST[’employer’])?sanitize_text_field($_REQUEST[’employer’]):”,
‘company’ => get_bloginfo(‘name’),
‘broker_name’ => $bk_name,
‘broker_agency’ => $bk_agency,
‘broker_email’ => $bk_email,
‘broker_phone’ => $bk_phone,
‘broker_website’ => $bk_web,
‘broker_line’ => $join([$bk_name,$bk_agency]),
‘broker_contact’ => $join([$bk_email,$bk_phone,$bk_web]),
// Prefer explicit effective date (from intake) when provided
‘effective’ => ($effective_req !== ” ? $effective_req : $rate_set),
‘rate_set’ => $rate_set,
‘area’ => (string)intval($area), // bare number
‘county’ => isset($_REQUEST[‘county’])?sanitize_text_field($_REQUEST[‘county’]):”,
‘today’ => date(‘Y-m-d’),
‘plans’ => implode(‘, ‘,$plans),
‘disclaimer’ => (isset($_REQUEST[‘disclaimer’]) && $_REQUEST[‘disclaimer’]!==”)? wp_kses_post($_REQUEST[‘disclaimer’]) : $defDisclaimer,
// NEW: include rate_style in context so it can be templated and cached distinctly
‘rate_style’ => isset($_REQUEST[‘rate_style’]) ? sanitize_text_field($_REQUEST[‘rate_style’]) : ”,
];
$mark(‘before_placeholders’);
self::replace_placeholders($wsSrc,$ctx);
$mark(‘after_placeholders’);
// write grid
$mark(‘before_write’);
if($mode===’rows’){
$headerRow=$startRowR-1;
$colMap=self::build_header_col_map($wsSrc,$headerRow,1,200);
$rev=self::row_alias_reverse();
$labelVals=[];
for($r=1;$r<count($matrix);$r++){
$label=(string)($matrix[$r][0]??”); if($label===”) continue;
$norm=self::norm($label);
$canonical=$rev[$norm]??$norm;
$labelVals[$canonical]=array_values(array_slice($matrix[$r],1));
}
$planCount=max(0,count($matrix[0])-1);
for($pi=0;$pi<$planCount;$pi++){
$targetRow=$startRowR+$pi;
foreach($colMap as $canon=>$colIndex){
$val=$labelVals[$canon][$pi]??”;
$wsSrc->setCellValueByColumnAndRow($colIndex,$targetRow,(string)$val);
}
}
}else{
if($startCol===null || $startRow===null){
wp_die(‘STQ export: could not find [[STQ_TABLE]] on sheet “‘.$wsSrc->getTitle().'”.’);
}
if(!empty($matrix)){
$hdr=$matrix[0];
for($i=1,$c=$startCol;$i<count($hdr);$i++,$c++){
$wsSrc->setCellValueByColumnAndRow($c,$startRow,(string)$hdr[$i]);
}
}
$labelCol=max(1,$startCol-1);
$rowMap=self::build_label_row_map($wsSrc,$labelCol,max(1,$startRow-200),$startRow+400);
$matched=0;
for($r=1;$r<count($matrix);$r++){
$label=(string)($matrix[$r][0]??”); if($label===”) continue;
$norm=self::norm($label); if(!isset($rowMap[$norm])) continue;
$targetRow=$rowMap[$norm];
for($i=1,$c=$startCol;$i<count($matrix[$r]);$i++,$c++){
$wsSrc->setCellValueByColumnAndRow($c,$targetRow,(string)$matrix[$r][$i]);
}
$matched++;
}
if($matched===0){
wp_die(‘STQ export: none of the labels matched next to [[STQ_TABLE]] on sheet “‘.$wsSrc->getTitle().'”.’);
}
}
$mark(‘after_write’);
} else {
// Dry save timings (no edits)
$t[‘before_placeholders’] = $t[‘before_write’] = microtime(true);
$t[‘after_placeholders’] = $t[‘after_write’] = microtime(true);
}
// ———- writer/save (speed-tuned + cache) ———-
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($srcBook, ‘Xlsx’);
if (method_exists($writer,’setPreCalculateFormulas’)) $writer->setPreCalculateFormulas(false);
if (method_exists($writer,’setIncludeCharts’)) $writer->setIncludeCharts(false);
if (method_exists($writer,’setOffice2003Compatibility’)) $writer->setOffice2003Compatibility(false);
if (method_exists($writer,’setZipClass’) && class_exists(‘\ZipArchive’)) {
$writer->setZipClass(\PhpOffice\PhpSpreadsheet\Writer\Xlsx::ZIPARCHIVE);
}
if (method_exists($writer,’setCompressionLevel’)) $writer->setCompressionLevel(0);
if (method_exists($writer,’setUseDiskCaching’)) {
$upload = function_exists(‘wp_get_upload_dir’) ? wp_get_upload_dir() : [‘basedir’=>sys_get_temp_dir()];
$base = rtrim($upload[‘basedir’],’/\\’);
$tmpDir = $base.’/stq-tmp’;
if (!is_dir($tmpDir)) @wp_mkdir_p($tmpDir);
$writer->setUseDiskCaching(true, (is_dir($tmpDir)&&is_writable($tmpDir)) ? $tmpDir : sys_get_temp_dir());
}
$upload = function_exists(‘wp_get_upload_dir’) ? wp_get_upload_dir() : [‘basedir’=>sys_get_temp_dir()];
$base = rtrim($upload[‘basedir’],’/\\’);
$cacheDir= $base.’/stq-cache’;
$tmpDir = $base.’/stq-tmp’;
if (!is_dir($cacheDir)) @wp_mkdir_p($cacheDir);
if (!is_dir($tmpDir)) @wp_mkdir_p($tmpDir);
// Build cache key
$templateMtime = @filemtime($template) ?: 0;
$templateHash = @md5_file($template) ?: ”;
$metaForKey = [
’employer’ => (string)($ctx[’employer’] ?? ”),
‘county’ => (string)($ctx[‘county’] ?? ”),
‘broker_name’ => (string)($ctx[‘broker_name’] ?? ”),
‘broker_agency’ => (string)($ctx[‘broker_agency’] ?? ”),
‘broker_email’ => (string)($ctx[‘broker_email’] ?? ”),
‘broker_phone’ => (string)($ctx[‘broker_phone’] ?? ”),
‘broker_website’ => (string)($ctx[‘broker_website’] ?? ”),
‘disclaimer’ => (string)($ctx[‘disclaimer’] ?? ”),
‘effective’ => isset($_REQUEST[‘effective’]) ? (string)sanitize_text_field($_REQUEST[‘effective’]) : ”, // NEW
‘today’ => date(‘Y-m-d’),
];
$cacheKey = sha1(json_encode([
‘sheet’ => $preferred ?: ”,
‘plans’ => $plans,
‘rate_set’ => $rate_set,
‘area’ => $area,
‘meta’ => $metaForKey,
‘template_mtime’ => $templateMtime,
‘template_md5’ => $templateHash,
]));
$cacheFile = $cacheDir.’/stq-‘.$cacheKey.’.xlsx’;
$fname = ‘Stella-Compare-‘.$loadPath.’-‘.date(‘Ymd-His’).’.xlsx’;
self::clear_output_buffers();
// ———- CACHE HIT: stream immediately ———-
if (!$noCache && is_file($cacheFile)) {
$mark(‘before_save’); $mark(‘after_save’); // treat as instant
$line = sprintf(“[%s] path=%s sheet=%s matrix=%0.3fs load_fast=%0.3fs load_scan=%0.3fs placeholders=%0.3fs write=%0.3fs save=%0.3fs total=%0.3fs (cache hit)\n”,
date(‘Y-m-d H:i:s’), $loadPath, ($preferred?:'(none)’),
$span(‘start’,’after_matrix’),
$span(‘before_load_fast’,’after_load_fast’),
$span(‘before_load_scan’,’after_load_scan’),
$span(‘before_placeholders’,’after_placeholders’),
$span(‘before_write’,’after_write’),
$span(‘before_save’,’after_save’),
$span(‘start’,’after_save’)
);
@file_put_contents($tmpDir.’/stq-export.log’, $line, FILE_APPEND);
self::send_download_headers($fname, ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
$size=@filesize($cacheFile); if($size!==false) header(‘Content-Length: ‘.$size);
readfile($cacheFile);
if (method_exists($srcBook,’disconnectWorksheets’)) $srcBook->disconnectWorksheets();
unset($srcBook);
exit;
}
// ———- CACHE MISS: save, (maybe) cache, stream ———-
$mark(‘before_save’);
$tmp = ((is_dir($tmpDir)&&is_writable($tmpDir)) ? $tmpDir : sys_get_temp_dir()).’/’.$fname;
$writer->save($tmp);
$mark(‘after_save’);
if (!$noCache) { @copy($tmp, $cacheFile); }
$line = sprintf(“[%s] path=%s sheet=%s matrix=%0.3fs load_fast=%0.3fs load_scan=%0.3fs placeholders=%0.3fs write=%0.3fs save=%0.3fs total=%0.3fs%s\n”,
date(‘Y-m-d H:i:s’), $loadPath, ($preferred?:'(none)’),
$span(‘start’,’after_matrix’),
$span(‘before_load_fast’,’after_load_fast’),
$span(‘before_load_scan’,’after_load_scan’),
$span(‘before_placeholders’,’after_placeholders’),
$span(‘before_write’,’after_write’),
$span(‘before_save’,’after_save’),
$span(‘start’,’after_save’),
$noCache ? ‘ (nocache)’ : ”
);
@file_put_contents($tmpDir.’/stq-export.log’, $line, FILE_APPEND);
self::send_download_headers($fname, ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
$size=@filesize($tmp); if($size!==false) header(‘Content-Length: ‘.$size);
$fp=@fopen($tmp,’rb’); if($fp){ fpassthru($fp); fclose($fp);} else { readfile($tmp); }
@unlink($tmp);
if (method_exists($srcBook,’disconnectWorksheets’)) $srcBook->disconnectWorksheets();
unset($srcBook);
exit;
}catch(\Throwable $e){
wp_die(‘Export error (XLSX): ‘.esc_html($e->getMessage()));
}
}
/* ===== END: STQ handle_xlsx (paste-over) ===== */
/* —————————- PDF ——————————- */
public static function handle_pdf(){
try{
list($plans, $rate_set, $area) = self::read_request();
if (empty($plans)) wp_die(‘No plans to export.’);
$matrix = self::build_compare_matrix($plans, $rate_set, $area);
$style = ‘
<style>
@page { size: letter landscape; margin: 12mm; }
body { font-family: Arial, Helvetica, sans-serif; font-size: 11px; color:#111; }
table { border-collapse: collapse; width: 100%; table-layout: fixed; }
th, td { border: 1px solid #444; padding: 4px 6px; vertical-align: top; word-wrap: break-word; }
th { background: #f1f1f1; font-weight: bold; }
.firstcol { background:#fafafa; font-weight:bold; width:22%; }
h2 { margin:0 0 10px 0; font-size:16px; }
</style>’;
$html = ‘<!DOCTYPE html><html><head><meta charset=”UTF-8″>’.$style.'</head><body>’;
$html .= ‘<h2>Stella Quotes – Side-by-Side</h2><table><thead><tr>’;
if (!empty($matrix)) {
foreach ((array)$matrix[0] as $cell) $html .= ‘<th>’.htmlspecialchars((string)$cell).'</th>’;
$html .= ‘</tr></thead><tbody>’;
for ($r=1; $r<count($matrix); $r++){
$html .= ‘<tr>’;
$cIdx=0; foreach ((array)$matrix[$r] as $cell){
$tag = ($cIdx===0)?’th’:’td’; $class = ($cIdx===0)?’ class=”firstcol”‘:”;
$html .= ‘<‘.$tag.$class.’>’.htmlspecialchars((string)$cell).'</’.$tag.’>’; $cIdx++;
}
$html .= ‘</tr>’;
}
$html .= ‘</tbody>’;
}
$html .= ‘</table></body></html>’;
if (class_exists(‘\Dompdf\Dompdf’)) {
$dompdf = new \Dompdf\Dompdf([‘isRemoteEnabled’=>true]);
$dompdf->loadHtml($html);
$dompdf->setPaper(‘letter’,’landscape’);
$dompdf->render();
$fname = ‘Stella-Compare-‘.date(‘Ymd-His’).’.pdf’;
self::clear_output_buffers();
self::send_download_headers($fname, ‘application/pdf’);
echo $dompdf->output();
exit;
}
// If Dompdf missing, stream the HTML
$fname = ‘Stella-Compare-‘.date(‘Ymd-His’).’.html’;
self::clear_output_buffers();
self::send_download_headers($fname, ‘text/html; charset=utf-8’);
echo $html; exit;
} catch (\Throwable $e){
error_log(‘[STQ Export PDF] ‘.$e->getMessage());
wp_die(‘Export error (PDF): ‘.esc_html($e->getMessage()));
}
}
/* —————————- HTML —————————— */
public static function handle_html(){
try{
list($plans, $rate_set, $area) = self::read_request();
if (empty($plans)) wp_die(‘No plans to export.’);
$matrix = self::build_compare_matrix($plans, $rate_set, $area);
$style = ‘
<style>
@page { size: letter landscape; margin: 10mm; }
body { font-family: Arial, Helvetica, sans-serif; font-size: 11px; color:#111; }
h2 { margin: 0 0 8px 0; font-size: 16px; }
.meta { margin: 0 0 8px 0; font-size: 11px; color:#444; }
table { border-collapse: collapse; width: 100%; table-layout: fixed; }
th, td { border: 1px solid #444; padding: 4px 6px; vertical-align: top; word-wrap: break-word; }
th { background: #f1f1f1; font-weight: bold; }
.sticky-header th { position: sticky; top: 0; z-index: 2; }
.firstcol { font-weight: bold; background: #fafafa; width: 22%; }
</style>’;
$title = ‘Stella Quotes – Side-by-Side’;
$meta = ‘Rate set: ‘ . esc_html($rate_set) . ‘ · Area: ‘ . esc_html($area) .
‘ · Plans: ‘ . esc_html(implode(‘, ‘, $plans));
$html = ‘<!DOCTYPE html><html><head><meta charset=”UTF-8″>’.$style.'</head><body>’;
$html .= ‘<h2>’.$title.'</h2><div class=”meta”>’.$meta.'</div>’;
$html .= ‘<table class=”sxs”><thead class=”sticky-header”>’;
if (!empty($matrix)) {
$html .= ‘<tr>’;
foreach ((array)$matrix[0] as $cell) $html .= ‘<th>’.htmlspecialchars((string)$cell).'</th>’;
$html .= ‘</tr></thead><tbody>’;
for ($r=1; $r<count($matrix); $r++){
$html .= ‘<tr>’;
$cIdx=0; foreach ((array)$matrix[$r] as $cell){
$tag = ($cIdx===0)?’th’:’td’; $class = ($cIdx===0)?’ class=”firstcol”‘:”;
$html .= ‘<‘.$tag.$class.’>’.htmlspecialchars((string)$cell).'</’.$tag.’>’; $cIdx++;
}
$html .= ‘</tr>’;
}
$html .= ‘</tbody>’;
}
$html .= ‘</table></body></html>’;
$fname = ‘Stella-Compare-‘.date(‘Ymd-His’).’.html’;
self::clear_output_buffers();
self::send_download_headers($fname, ‘text/html; charset=utf-8’);
echo $html; exit;
} catch (\Throwable $e){
error_log(‘[STQ Export HTML] ‘.$e->getMessage());
wp_die(‘Export error (HTML): ‘.esc_html($e->getMessage()));
}
}
/* ————————— tiny ping ————————– */
public static function handle_ping(){
self::clear_output_buffers();
self::send_download_headers(‘stq-ping.csv’, ‘text/csv; charset=utf-8’);
$out = fopen(‘php://output’, ‘w’);
fputcsv($out, [‘hello’,’world’,’🧪’,’php ‘.PHP_VERSION]);
fclose($out);
exit;
}
}
STQ_Export_Compare::init();