/** Create and stream an Excel workbook (.xlsx). Summary + one sheet per chapter (fallback to CSV if library missing). */ private function output_xlsx( array $grouped, array $grand, $from, $to ) { // If PhpSpreadsheet is present, build a real XLSX; otherwise emit a safe CSV fallback. if ( class_exists( '\PhpOffice\PhpSpreadsheet\Spreadsheet' ) && class_exists( '\PhpOffice\PhpSpreadsheet\IOFactory' ) ) { $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $spreadsheet->getProperties()->setCreator('NCGR Report')->setTitle('Member Payments'); // Autosize helper $autosize = function($sheet, $lastColLetter){ for ($c = 'A'; $c <= $lastColLetter; $c++) { $sheet->getColumnDimension($c)->setAutoSize(true); if ($c === $lastColLetter) break; } }; /** Summary sheet **/ $summary = $spreadsheet->getActiveSheet(); $summary->setTitle('Summary'); $row = 1; $summary->setCellValue('A'.$row, 'Window:'); $summary->setCellValue('B'.$row, $from.' to '.$to); $row += 2; $summary->fromArray(['Total Paid','Chapter Rebate','NCGR Revenue','Members'], NULL, 'A'.$row); $row++; $summary->fromArray([ number_format((float)$grand['total_paid'],2,'.',''), number_format((float)$grand['chapter_rebate'],2,'.',''), number_format((float)$grand['ncgr_revenue'],2,'.',''), (int)$grand['members'] ], NULL, 'A'.$row); $row += 2; $summary->setCellValue('A'.$row, 'Chapter Rollup'); $row++; $summary->fromArray(['Chapter','Members','Total Paid','Chapter Rebate','NCGR Revenue'], NULL, 'A'.$row); $row++; foreach ($grouped as $chapter => $data) { $summary->fromArray([ $chapter, count($data['members']), number_format((float)$data['totals']['total_paid'],2,'.',''), number_format((float)$data['totals']['chapter_rebate'],2,'.',''), number_format((float)$data['totals']['ncgr_revenue'],2,'.',''), ], NULL, 'A'.$row); $row++; } $autosize($summary,'E'); /** Per-chapter sheets **/ foreach ($grouped as $chapter => $data) { $sheet = $spreadsheet->createSheet(); $sheet->setTitle($this->sanitize_sheet_title($chapter)); $sheet->fromArray( ['Login','Email','Recent Date','Recent Pay Type','Total Paid','Chapter Rebate','NCGR Revenue','Gateway (raw)','Pay Type (raw)'], NULL, 'A1' ); $r = 2; foreach ($data['members'] as $rowData) { $sheet->fromArray([ (string)$rowData['user_login'], (string)$rowData['user_email'], (string)$rowData['recent_transaction_date'], (string)$rowData['recent_payment_type'], number_format((float)$rowData['total_paid'],2,'.',''), number_format((float)$rowData['chapter_rebate'],2,'.',''), number_format((float)$rowData['ncgr_revenue'],2,'.',''), (string)$rowData['recent_gateway_raw'], (string)$rowData['recent_payment_type_raw'], ], NULL, 'A'.$r); $r++; } // Totals row $sheet->fromArray([ 'Totals','','','', number_format((float)$data['totals']['total_paid'],2,'.',''), number_format((float)$data['totals']['chapter_rebate'],2,'.',''), number_format((float)$data['totals']['ncgr_revenue'],2,'.',''), '', '' ], NULL, 'A'.$r); $autosize($sheet,'I'); } // Stream the XLSX nocache_headers(); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename=ncgr-member-payments-'.preg_replace('/[^0-9]/','',$from).'-to-'.preg_replace('/[^0-9]/','',$to).'.xlsx'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); exit; } // Fallback: single CSV that includes Summary + per-chapter sections $this->output_csv_fallback_note($grouped, $grand, $from, $to); } /** Excel sheet-name sanitizer (31 chars; no \ / * ? : [ ]). */ private function sanitize_sheet_title($title) { $title = wp_strip_all_tags((string)$title); $title = preg_replace('/[\\\\\\/*?:\\[\\]]+/', '-', $title); if ($title === '') $title = 'Chapter'; if (function_exists('mb_substr')) { $title = mb_substr($title, 0, 31); } else { $title = substr($title, 0, 31); } return $title; } /** CSV fallback used when PhpSpreadsheet isn’t installed. */ private function output_csv_fallback_note(array $grouped, array $grand, $from, $to) { nocache_headers(); header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=ncgr-member-payments-'.preg_replace('/[^0-9]/','',$from).'-to-'.preg_replace('/[^0-9]/','',$to).'-fallback.csv'); $out = fopen('php://output','w'); fputcsv($out, ['NOTE','PhpSpreadsheet not found — install phpoffice/phpspreadsheet for true XLSX.']); fputcsv($out, []); fputcsv($out, ['Window', $from.' to '.$to]); fputcsv($out, []); fputcsv($out, ['Grand Totals']); fputcsv($out, ['Total Paid','Chapter Rebate','NCGR Revenue','Members']); fputcsv($out, [ number_format((float)$grand['total_paid'],2,'.',''), number_format((float)$grand['chapter_rebate'],2,'.',''), number_format((float)$grand['ncgr_revenue'],2,'.',''), (int)$grand['members'] ]); fputcsv($out, []); fputcsv($out, ['Chapter Rollup']); fputcsv($out, ['Chapter','Members','Total Paid','Chapter Rebate','NCGR Revenue']); foreach ($grouped as $chapter => $data) { fputcsv($out, [ $chapter, count($data['members']), number_format((float)$data['totals']['total_paid'],2,'.',''), number_format((float)$data['totals']['chapter_rebate'],2,'.',''), number_format((float)$data['totals']['ncgr_revenue'],2,'.','') ]); } foreach ($grouped as $chapter => $data) { fputcsv($out, []); fputcsv($out, ['Chapter: '.$chapter]); fputcsv($out, ['Login','Email','Recent Date','Recent Pay Type','Total Paid','Chapter Rebate','NCGR Revenue','Gateway (raw)','Pay Type (raw)']); foreach ($data['members'] as $r) { fputcsv($out, [ (string)$r['user_login'], (string)$r['user_email'], (string)$r['recent_transaction_date'], (string)$r['recent_payment_type'], number_format((float)$r['total_paid'],2,'.',''), number_format((float)$r['chapter_rebate'],2,'.',''), number_format((float)$r['ncgr_revenue'],2,'.',''), (string)$r['recent_gateway_raw'], (string)$r['recent_payment_type_raw'], ]); } // Totals row fputcsv($out, [ 'Totals','','','', number_format((float)$data['totals']['total_paid'],2,'.',''), number_format((float)$data['totals']['chapter_rebate'],2,'.',''), number_format((float)$data['totals']['ncgr_revenue'],2,'.',''), '', '' ]); } fclose($out); exit; }