D:\host\scoreman.in\tldt2025\certificatelist.php

<?php
// download_certificate_list.php
// CSV with ALL event columns (not just "highest").
// GET: repdistrict (required), appnos (optional comma-separated), prefix (optional)
// Sort: Team -> Discipline -> agorder -> Gender (Girls first) -> AppNo

date_default_timezone_set('Asia/Kolkata');
header('Content-Type: text/csv; charset=UTF-8');

require_once __DIR__ . '/dbconnect.php'; // must define $con (mysqli)

if (!($con instanceof mysqli)) {
    http_response_code(500);
    echo "DB not connected";
    exit;
}

$repdistrict = isset($_GET['repdistrict']) ? trim($_GET['repdistrict']) : '';
if ($repdistrict === '') {
    http_response_code(400);
    echo "repdistrict is required";
    exit;
}
$certPrefix = isset($_GET['prefix']) && $_GET['prefix'] !== '' ? $_GET['prefix'] : 'TNDT2025/';
$appnosCsv  = isset($_GET['appnos']) ? trim($_GET['appnos']) : '';

$filename = 'certificate_list_full_' . preg_replace('/[^A-Za-z0-9_-]/', '_', $repdistrict) . '_' . date('Ymd_His') . '.csv';
header('Content-Disposition: attachment; filename="'.$filename.'"');

// BOM for Excel UTF-8
echo "\xEF\xBB\xBF";

// ---- Championships date (optional reference for age calc) ----
$champDate = '';
if ($st = mysqli_prepare($con, "SELECT dates FROM championships WHERE district=? ORDER BY id DESC LIMIT 1")) {
    mysqli_stmt_bind_param($st, "s", $repdistrict);
    mysqli_stmt_execute($st);
    mysqli_stmt_store_result($st);
    mysqli_stmt_bind_result($st, $champDate);
    mysqli_stmt_fetch($st);
    mysqli_stmt_close($st);
}

// ---- Event columns & pretty headers ----
$quadKeys   = array('rink1','rink2','rink3','road1','road2');
$inlineKeys = array('rink4','rink5','rink6','rink7','p2p','elim','road3','road4','road5','p2prd','elimrd','relay','mixedrelay','marathon');
$artKeys    = array('figure','free','pair','solodance','pairdance','inlinefree','quartet','precisionskating','showgroup');
$hokKeys    = array('quadhockey','mixedrollerhockey'); // Roller-Hockey
$ihkKeys    = array('inlinehockey');                   // Inline-Hockey
$frKeys     = array('speedslalom','classicslalom','pairslalom'); // Inline-Freestyle
$othKeys    = array('alpine','downhill','rollerderby','rollerfreestyle','skateboarding','scooter'); // Other-Games

// For a consistent CSV (single header), include ALL event columns:
$allEventCols = array_merge($quadKeys,$inlineKeys,$artKeys,$hokKeys,$ihkKeys,$frKeys,$othKeys);

// Pretty names (same style you’ve used in certificates)
$pretty = array(
  // Speed-Quad
  'rink1'=>'RINK - I', 'rink2'=>'RINK - II', 'rink3'=>'RINK - III', 'road1'=>'ROAD - I', 'road2'=>'ROAD - II',
  // Speed-Inline
  'rink4'=>'RINK - IV', 'rink5'=>'RINK - V', 'rink6'=>'RINK - VI', 'rink7'=>'RINK - VII (Dual TT)',
  'p2p'=>'POINTS (TRACK)', 'elim'=>'ELIMINATION (RINK)',
  'road3'=>'ROAD - III', 'road4'=>'ROAD - IV', 'road5'=>'ROAD - V',
  'p2prd'=>'POINT TO POINT (ROAD)', 'elimrd'=>'ELIMINATION (ROAD)',
  'relay'=>'RELAY', 'mixedrelay'=>'MIXED RELAY', 'marathon'=>'MARATHON',
  // Artistic
  'figure'=>'FIGURE', 'free'=>'FREE', 'pair'=>'PAIR', 'solodance'=>'SOLO DANCE', 'pairdance'=>'PAIR DANCE', 'inlinefree'=>'INLINE FREE',
  'quartet'=>'QUARTET', 'precisionskating'=>'PRECISION', 'showgroup'=>'SHOW GROUP',
  // Hockey
  'quadhockey'=>'ROLLER HOCKEY', 'mixedrollerhockey'=>'MIXED ROLLER HOCKEY',
  // Inline-Hockey
  'inlinehockey'=>'INLINE HOCKEY',
  // Inline-Freestyle
  'speedslalom'=>'SPEED SLALOM', 'classicslalom'=>'CLASSIC SLALOM', 'pairslalom'=>'PAIR SLALOM',
  // Other-Games
  'alpine'=>'ALPINE', 'downhill'=>'DOWNHILL', 'rollerderby'=>'ROLLER DERBY', 'rollerfreestyle'=>'ROLLER FREESTYLE',
  'skateboarding'=>'SKATEBOARDING', 'scooter'=>'SCOOTER'
);

// Discipline groupings
$discMaps = array(
    'Speed-Quad'       => $quadKeys,
    'Speed-Inline'     => $inlineKeys,
    'Artistic'         => $artKeys,
    'Roller-Hockey'    => $hokKeys,
    'Inline-Hockey'    => $ihkKeys,
    'Inline-Freestyle' => $frKeys,
    'Other-Games'      => $othKeys
);
$discOrder = array(
    'Speed-Quad' => 1,
    'Speed-Inline' => 2,
    'Artistic' => 3,
    'Roller-Hockey' => 4,
    'Inline-Hockey' => 5,
    'Inline-Freestyle' => 6,
    'Other-Games' => 7
);

// ---- Helpers (PHP 5.6) ----
function label_code($v) {
    $v = strtoupper(trim((string)$v));
    if ($v==='1' || $v==='G') return 'FIRST';
    if ($v==='2')              return 'SECOND';
    if ($v==='3' || $v==='B') return 'THIRD';
    if ($v==='S')             return 'SELECTED';
    if ($v==='F')             return 'FORWARD';
    if ($v==='P')             return 'PARTICIPATED';
    if ($v==='A' || $v==='NR')return 'ABSENT';
    if ($v==='')              return 'NIL';
    return 'NIL';
}
function gender_word($g) {
    $g = strtolower(trim((string)$g));
    if (in_array($g, array('f','female','girl','girls','women','woman'), true)) return 'Girls';
    if (in_array($g, array('m','male','boy','boys','men','man'), true))         return 'Boys';
    return ucfirst($g);
}
function status_rank_for_cert($lbl) {
    // For Cert Type per discipline
    if ($lbl==='FIRST' || $lbl==='SECOND' || $lbl==='THIRD') return 70; // Merit
    if ($lbl==='SELECTED')  return 50;
    if ($lbl==='FORWARD')   return 40;
    if ($lbl==='PARTICIPATED') return 30;
    if ($lbl==='ABSENT')    return 10;
    return 0; // NIL
}
function compute_age_years($ageStr, $dobStr, $eventDateStr) {
    $age = (int)$ageStr;
    if ($age > 0) return $age;
    $dobTs = strtotime($dobStr);
    if (!$dobTs) return 0;
    $refTs = strtotime($eventDateStr);
    if (!$refTs) $refTs = time();
    $y1 = (int)date('Y',$refTs); $m1=(int)date('n',$refTs); $d1=(int)date('j',$refTs);
    $y0 = (int)date('Y',$dobTs); $m0=(int)date('n',$dobTs); $d0=(int)date('j',$dobTs);
    $age = $y1 - $y0;
    if ($m1 < $m0 || ($m1==$m0 && $d1 < $d0)) $age--;
    return ($age<0?0:$age);
}
function special_agegroup_for_row($discipline, $eventKey, $ageYears) {
    $age = (int)$ageYears;
    if ($discipline === 'Roller-Hockey' || $discipline === 'Inline-Hockey') {
        if ($age >= 6 && $age < 9)  return '6 to 9 Cadet Tiny Tots';
        if ($age >= 9 && $age < 12) return '9 to 12 Cadet';
        return null;
    }
    if ($eventKey === 'rollerderby') {
        if ($age >= 12 && $age < 18) return '12 to 18 Junior';
        return null;
    }
    if ($eventKey === 'scooter') {
        if ($age >= 12) return 'Above 12';
        return null;
    }
    return null;
}
function certificate_type_for($labelsInDiscipline) {
    // Decide from best label in that discipline
    $best = 'NIL'; $bestR = 0;
    foreach ($labelsInDiscipline as $lbl) {
        $r = status_rank_for_cert($lbl);
        if ($r > $bestR) { $bestR = $r; $best = $lbl; }
    }
    if ($best==='FIRST' || $best==='SECOND' || $best==='THIRD') return 'Merit';
    if ($best==='SELECTED')  return 'Selected';
    if ($best==='FORWARD')   return 'Forward';
    if ($best==='PARTICIPATED') return 'Participation';
    if ($best==='ABSENT')    return 'Absent';
    return 'Nil';
}

// ---- Build SQL ----
$cols = array_merge(
    array('appno','sname','gender','agegroup','age','dob','club','clubname','agorder','skatertype'),
    $allEventCols
);
$select = '`'.implode('`,`',$cols).'`';

$where = "order_status='Success' AND repdistrict='".mysqli_real_escape_string($con, $repdistrict)."'";
if ($appnosCsv !== '') {
    $parts = explode(',', $appnosCsv);
    $ins = array();
    foreach ($parts as $p) {
        $p = trim($p);
        if ($p!=='') $ins[] = "'".mysqli_real_escape_string($con,$p)."'";
    }
    if (!empty($ins)) $where .= " AND appno IN (".implode(',', $ins).")";
}

$sql = "
  SELECT $select
  FROM applications
  WHERE $where
  ORDER BY
    IFNULL(NULLIF(clubname,''), club),
    FIELD(skatertype,
      'Speed-Quad','Speed-Inline','Artistic','Roller-Hockey','Inline-Hockey','Inline-Freestyle','Other','Other-Games'
    ),
    IFNULL(agorder,999999)+0,
    gender DESC,
    CAST(appno AS UNSIGNED), appno
";

$res = mysqli_query($con, $sql);
if (!$res) {
    echo "Query failed: ".mysqli_error($con);
    exit;
}

// ---- Build rows ----
$rowsOut = array();

while ($r = mysqli_fetch_assoc($res)) {
    $team = ($r['clubname'] !== '') ? $r['clubname'] : $r['club'];
    $ageYears = compute_age_years(isset($r['age'])?$r['age']:'', isset($r['dob'])?$r['dob']:'', $champDate);
    $genderLbl = gender_word($r['gender']);
    $agorder = isset($r['agorder']) ? (int)$r['agorder'] : 999999;
    $appno = (string)$r['appno'];
    $name  = (string)$r['sname'];
    $skType= (string)$r['skatertype'];
    $agRaw = (string)$r['agegroup'];

    // Compute labels for all event columns once
    $labelsAll = array();
    $sawAny=false; $sawPositive=false; $sawAbsent=false;
    foreach ($allEventCols as $ek) {
        $lbl = label_code(isset($r[$ek]) ? $r[$ek] : '');
        $labelsAll[$ek] = $lbl;
        if ($lbl !== 'NIL') { $sawAny = true; }
        if ($lbl === 'ABSENT') $sawAbsent = true;
        if (in_array($lbl, array('FIRST','SECOND','THIRD','PARTICIPATED','SELECTED','FORWARD'), true)) $sawPositive = true;
    }

    if (!$sawAny) {
        // no event info at all; skip (clean CSV)
        continue;
    }

    if (!$sawPositive && $sawAbsent) {
        // Fully absent overall: include ONE row (discipline from skatertype) and show all events
        $discName = array_key_exists($skType, $discOrder) ? $skType : 'Speed-Quad';
        $discWeight = isset($discOrder[$discName]) ? $discOrder[$discName] : 99;

        // special age group (use derby/scooter keys if that discipline)
        $someKey = '';
        if ($discName==='Other-Games') {
            // pick a representative for special rule
            if ($labelsAll['rollerderby']!=='NIL') $someKey='rollerderby';
            elseif ($labelsAll['scooter']!=='NIL') $someKey='scooter';
        }
        $agOverride = special_agegroup_for_row($discName, $someKey, $ageYears);
        $agDisplay  = ($agOverride !== null && $agOverride !== '') ? $agOverride : $agRaw;

        $row = array(
            'team' => $team, 'disc'=>$discName, 'discW'=>$discWeight,
            'agorder'=>$agorder, 'gender'=>$r['gender'], 'genderLbl'=>$genderLbl,
            'certNo'=>$certPrefix.$appno, 'appno'=>$appno, 'name'=>$name,
            'ageGroupDisplay'=>$agDisplay, 'certType'=>'Absent'
        );
        // populate all event columns with their labels
        foreach ($allEventCols as $ek) $row[$ek] = $labelsAll[$ek];
        $rowsOut[] = $row;
        continue;
    }

    // Otherwise: emit one row PER discipline with ALL event columns, filling only its own
    foreach ($discMaps as $discName => $keys) {
        // check if participant has any non-NIL value in this discipline
        $hasAnyInDisc = false;
        $labelsInDisc = array();
        foreach ($keys as $ek) {
            $lbl = $labelsAll[$ek];
            $labelsInDisc[$ek] = $lbl;
            if ($lbl !== 'NIL') $hasAnyInDisc = true;
        }
        if (!$hasAnyInDisc) continue;

        $discWeight = isset($discOrder[$discName]) ? $discOrder[$discName] : 99;

        // Decide certificate type for this discipline
        $certType = certificate_type_for($labelsInDisc);

        // Special age label (hockey / derby / scooter)
        $reprKey = '';
        if ($discName==='Other-Games') {
            if ($labelsAll['rollerderby']!=='NIL') $reprKey='rollerderby';
            elseif ($labelsAll['scooter']!=='NIL') $reprKey='scooter';
        }
        $agOverride = special_agegroup_for_row($discName, $reprKey, $ageYears);
        $agDisplay  = ($agOverride !== null && $agOverride !== '') ? $agOverride : $agRaw;

        $row = array(
            'team'=>$team, 'disc'=>$discName, 'discW'=>$discWeight,
            'agorder'=>$agorder, 'gender'=>$r['gender'], 'genderLbl'=>$genderLbl,
            'certNo'=>$certPrefix.$appno, 'appno'=>$appno, 'name'=>$name,
            'ageGroupDisplay'=>$agDisplay, 'certType'=>$certType
        );
        // Fill this discipline's event labels; leave others empty for clarity
        foreach ($allEventCols as $ek) {
            $row[$ek] = in_array($ek,$keys,true) ? $labelsAll[$ek] : '';
        }
        $rowsOut[] = $row;
    }
}
mysqli_free_result($res);

// ---- Final sort ----
usort($rowsOut, function($a,$b){
    if ($a['team'] !== $b['team']) return strcasecmp($a['team'], $b['team']);
    if ($a['discW'] !== $b['discW']) return ($a['discW'] < $b['discW']) ? -1 : 1;
    if ($a['agorder'] !== $b['agorder']) return ($a['agorder'] < $b['agorder']) ? -1 : 1;
    // Girls before Boys
    $ga = strtolower(substr($a['gender'],0,1)); $gb = strtolower(substr($b['gender'],0,1));
    if ($ga !== $gb) return ($ga === 'f') ? -1 : 1;
    $na = (int)$a['appno']; $nb = (int)$b['appno'];
    if ($na !== $nb) return ($na < $nb) ? -1 : 1;
    return strcmp($a['appno'],$b['appno']);
});

// ---- CSV header ----
$out = fopen('php://output','w');
$header = array(
  'Sl.No','Cert No','App No','Name','Team','Discipline','Age Group (Printed)','Gender','Certificate Type'
);
// append ALL event columns (pretty names)
foreach ($allEventCols as $ek) {
    $header[] = isset($pretty[$ek]) ? $pretty[$ek] : strtoupper($ek);
}
fputcsv($out, $header);

// ---- CSV rows ----
$sl = 1;
foreach ($rowsOut as $row) {
    $line = array(
        $sl++,
        $row['certNo'],
        $row['appno'],
        $row['name'],
        $row['team'],
        $row['disc'],
        $row['ageGroupDisplay'].' - '.$row['genderLbl'].' Age Group',
        $row['genderLbl'],
        $row['certType']
    );
    foreach ($allEventCols as $ek) {
        $line[] = isset($row[$ek]) ? $row[$ek] : '';
    }
    fputcsv($out, $line);
}
fclose($out);
exit;
?>