<?php
/**
 * logbook_kpi_export.php — /modules/logbook_kpi_export.php
 * Dipanggil via index.php?module=logbook_kpi&tab=export_laporan
 * Context index.php sudah menyediakan: $conn, session, $workloadMap, $monthlyWorkloadMap
 *
 * 6 Sheet:
 *   1. Ringkasan        – summary & domain KPI
 *   2. Seluruh Kegiatan – flat semua staf
 *   3. Rekap Per Staf   – ringkasan + detail kegiatan
 *   4. Rekap Per Unit   – ringkasan + detail kegiatan
 *   5. KPI Per Unit     – skor KPI, adjusted, kategori per unit
 *   6. KPI Per Staf     – skor KPI per staf dikelompokkan per unit
 */

if (!isset($conn) || !($conn instanceof mysqli)) { exit('Koneksi DB tidak tersedia.'); }

$xUserId  = (int)($_SESSION['user_id'] ?? 0);
$xRole    = strtolower((string)($_SESSION['user_role'] ?? 'user'));
$xIsAdmin = in_array($xRole, ['superadmin','admin'], true);
if ($xUserId === 0) { exit('Sesi tidak valid.'); }

/* ── DB helpers ── */
function xp_all(mysqli $c, string $sql, array $p=[], string $t=''): array {
    $s=$c->prepare($sql); if(!$s) return [];
    if($p) $s->bind_param($t,...$p);
    $s->execute(); $r=$s->get_result(); $rows=[];
    if($r) while($row=$r->fetch_assoc()) $rows[]=$row;
    $s->close(); return $rows;
}
function xp_one(mysqli $c, string $sql, array $p=[], string $t=''): ?array {
    $s=$c->prepare($sql); if(!$s) return null;
    if($p) $s->bind_param($t,...$p);
    $s->execute(); $r=$s->get_result(); $row=$r?$r->fetch_assoc():null; $s->close(); return $row?:null;
}

/* ── XML cell helpers ── */
function xe(string $v): string { return htmlspecialchars($v,ENT_XML1,'UTF-8'); }
function cs(string $v, string $st=''): string {
    $a=$st?" ss:StyleID=\"$st\"":'';
    return '<Cell'.$a.'><Data ss:Type="String">'.xe($v).'</Data></Cell>';
}
function cn(float $v, string $st=''): string {
    $a=$st?" ss:StyleID=\"$st\"":'';
    $val=floor($v)==$v?(string)(int)$v:number_format($v,2,'.','');
    return '<Cell'.$a.'><Data ss:Type="Number">'.$val.'</Data></Cell>';
}
function xrow(string ...$c): string { return '<Row>'.implode('',$c).'</Row>'."\n"; }
function hrow(string ...$ls): string {
    return '<Row>'.implode('',array_map(fn($l)=>cs($l,'H'),$ls)).'</Row>'."\n";
}
function blank(int $n=1): string { return str_repeat('<Cell><Data ss:Type="String"></Data></Cell>',$n); }
function mnt2str(int $m): string {
    $h=intdiv($m,60);$mn=$m%60;
    return $h>0?($mn>0?"$h jam $mn mnt":"$h jam"):"$mn mnt";
}
function titrow(string $sh, string $per, string $un): string {
    return '<Row><Cell ss:StyleID="Title"><Data ss:Type="String">'.xe($sh).'</Data></Cell></Row>'."\n"
          .'<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">Periode: '.xe($per).' | Unit: '.xe($un).'</Data></Cell></Row>'."\n"
          .'<Row><Cell><Data ss:Type="String"></Data></Cell></Row>'."\n";
}
function kpi_label(float $ks): string {
    if($ks>=90) return 'Sangat Baik';
    if($ks>=80) return 'Baik';
    if($ks>=70) return 'Cukup';
    if($ks>=60) return 'Perlu Pembinaan';
    return 'Kurang';
}
function kpi_style(float $ks): string {
    if($ks>=90) return 'KA';   // hijau
    if($ks>=80) return 'KB';   // biru
    if($ks>=70) return 'KC';   // kuning
    if($ks>=60) return 'KD';   // oranye
    return 'KE';                // merah
}

/* ── Filter ── */
$xMonth  = max(1,min(12,(int)($_GET['month'] ?? date('n'))));
$xYear   = max(2024,min(2099,(int)($_GET['year'] ?? date('Y'))));
$xFUnit  = (int)($_GET['unit_id']     ?? 0);
$xFUser  = (int)($_GET['user_id']     ?? 0);
$xFEmp   = (int)($_GET['employee_id'] ?? 0);
$xStart  = sprintf('%04d-%02d-01',$xYear,$xMonth);
$xEnd    = date('Y-m-t',strtotime($xStart));
$xPeriod = date('F Y',strtotime($xStart));

/* ── WHERE clause ── */
$xP = [$xStart,$xEnd]; $xT = 'ss';
$xW = "WHERE le.log_date BETWEEN ? AND ?";
if (!$xIsAdmin) { $xW .= " AND le.user_id=?"; $xP[]=$xUserId; $xT.='i'; }
if ($xFUnit>0)  { $xW .= " AND le.unit_id=?"; $xP[]=$xFUnit; $xT.='i'; }
if ($xFEmp>0)   { $xW .= " AND le.employee_id=?"; $xP[]=$xFEmp; $xT.='i'; }
elseif ($xFUser>0) {
    $xW .= " AND (le.user_id=? OR le.employee_id=(SELECT usf.employee_id FROM users usf WHERE usf.id=? LIMIT 1))";
    $xP[]=$xFUser; $xP[]=$xFUser; $xT.='ii';
}

$xUnitLabel = 'Semua Unit';
if ($xFUnit>0) {
    $xu = xp_one($conn,"SELECT name FROM units WHERE id=? LIMIT 1",[$xFUnit],'i');
    if ($xu) $xUnitLabel=(string)$xu['name'];
}

/* ── Query data ── */
$qSum = xp_one($conn,"SELECT COUNT(*) total_entries,
    COUNT(DISTINCT le.user_id) total_staff, COUNT(DISTINCT le.unit_id) total_units,
    COUNT(DISTINCT le.activity_id) total_activities,
    SUM(le.real_count) total_real, SUM(le.patients_count) total_patients,
    SUM(le.duration_minutes) total_minutes,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) verified_count,
    SUM(CASE WHEN le.status='submitted' THEN 1 ELSE 0 END) submitted_count
    FROM logbook_kpi_entries le $xW",$xP,$xT) ?? [];

$qDomain = xp_all($conn,"SELECT le.kpi_domain domain, COUNT(*) entri,
    SUM(le.real_count) total_real, SUM(le.patients_count) total_patients,
    SUM(le.duration_minutes) total_minutes,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) verified_count
    FROM logbook_kpi_entries le $xW
    GROUP BY le.kpi_domain ORDER BY total_real DESC",$xP,$xT);

$qAll = xp_all($conn,"SELECT le.log_date, le.shift,
    le.employee_name staf, COALESCE(le.unit_name,'') unit_name, le.role_level,
    le.activity_name kegiatan, le.category_code kat, le.kpi_domain domain,
    le.real_count dikerjakan, le.ideal_count target, le.patients_count pasien,
    le.duration_minutes durasi, le.quality_score mutu, le.status,
    le.notes catatan, le.patient_room kamar
    FROM logbook_kpi_entries le $xW
    ORDER BY le.employee_name ASC, le.log_date ASC",$xP,$xT);

$qStaf = xp_all($conn,"SELECT MAX(le.employee_name) staf,
    MAX(COALESCE(le.unit_name,'')) unit_name, MAX(le.role_level) role_level,
    COUNT(*) total_entri, COUNT(DISTINCT le.activity_id) jenis_kegiatan,
    SUM(le.real_count) total_dikerjakan, SUM(le.patients_count) total_pasien,
    SUM(le.duration_minutes) total_menit,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) terverifikasi,
    SUM(CASE WHEN le.status='submitted' THEN 1 ELSE 0 END) menunggu,
    MIN(le.log_date) tgl_pertama, MAX(le.log_date) tgl_terakhir,
    COUNT(DISTINCT le.log_date) hari_aktif
    FROM logbook_kpi_entries le $xW
    GROUP BY le.user_id ORDER BY staf ASC",$xP,$xT);

$qStafAkt = xp_all($conn,"SELECT MAX(le.employee_name) staf,
    MAX(COALESCE(le.unit_name,'')) unit_name, le.activity_name kegiatan,
    MAX(le.category_code) kat, MAX(le.kpi_domain) domain, COUNT(*) entri,
    SUM(le.real_count) total_dikerjakan, SUM(le.patients_count) total_pasien,
    SUM(le.duration_minutes) total_menit,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) terverifikasi,
    MIN(le.log_date) tgl_pertama, MAX(le.log_date) tgl_terakhir
    FROM logbook_kpi_entries le $xW
    GROUP BY le.user_id, le.activity_id
    ORDER BY staf ASC, total_dikerjakan DESC",$xP,$xT);

$qUnit = xp_all($conn,"SELECT MAX(COALESCE(le.unit_name,'Tanpa Unit')) unit_name,
    COUNT(DISTINCT le.user_id) jumlah_staf, COUNT(*) total_entri,
    COUNT(DISTINCT le.activity_id) jenis_kegiatan,
    SUM(le.real_count) total_dikerjakan, SUM(le.patients_count) total_pasien,
    SUM(le.duration_minutes) total_menit,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) terverifikasi
    FROM logbook_kpi_entries le $xW
    GROUP BY le.unit_id ORDER BY total_dikerjakan DESC",$xP,$xT);

$qUnitAkt = xp_all($conn,"SELECT MAX(COALESCE(le.unit_name,'Tanpa Unit')) unit_name,
    le.activity_name kegiatan, MAX(le.category_code) kat, MAX(le.kpi_domain) domain,
    COUNT(DISTINCT le.user_id) jumlah_staf, COUNT(*) entri,
    SUM(le.real_count) total_dikerjakan, SUM(le.patients_count) total_pasien,
    SUM(le.duration_minutes) total_menit,
    SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) terverifikasi
    FROM logbook_kpi_entries le $xW
    GROUP BY le.unit_id, le.activity_id
    ORDER BY unit_name ASC, total_dikerjakan DESC",$xP,$xT);

/* ── KPI: Unit & Staf (query mengikuti personRows di logbook_kpi.php) ── */
$qKpiStaf = xp_all($conn,"SELECT x.user_id, MAX(x.employee_id) employee_id,
    MAX(x.employee_name) employee_name, MAX(x.role_level) role_level,
    MAX(x.unit_name) unit_name, SUM(x.total_entries) total_entries,
    SUM(LEAST(x.actual_count,x.target_count)*x.weight) real_score,
    SUM(x.actual_count*x.weight) workload_score,
    SUM(GREATEST(x.actual_count-x.target_count,0)*x.weight) overload_score,
    SUM(x.target_count*x.weight) ideal_score,
    SUM(x.minutes) minutes, SUM(x.verified_count) verified_count,
    COALESCE(MAX(sr.salary_amount),0) salary_amount
    FROM (
        SELECT le.user_id, MAX(le.employee_id) employee_id,
               MAX(le.employee_name) employee_name, MAX(le.role_level) role_level,
               MAX(COALESCE(le.unit_name,'')) unit_name, le.activity_id,
               MAX(le.weight) weight, SUM(le.real_count) actual_count,
               MAX(le.ideal_count) target_count, COUNT(*) total_entries,
               SUM(le.duration_minutes) minutes,
               SUM(CASE WHEN le.status='verified' THEN 1 ELSE 0 END) verified_count
        FROM logbook_kpi_entries le $xW
        GROUP BY le.user_id, le.activity_id
    ) x
    LEFT JOIN logbook_kpi_salary_refs sr
        ON sr.user_id=x.user_id AND sr.month=$xMonth AND sr.year=$xYear
    GROUP BY x.user_id
    ORDER BY unit_name ASC, employee_name ASC",$xP,$xT);

$qKpiUnit = xp_all($conn,"SELECT x.unit_key, MAX(x.unit_name) unit_name,
    COUNT(DISTINCT x.user_id) staff_count, SUM(x.total_entries) total_entries,
    SUM(LEAST(x.actual_count,x.target_count)*x.weight) real_score,
    SUM(x.actual_count*x.weight) workload_score,
    SUM(GREATEST(x.actual_count-x.target_count,0)*x.weight) overload_score,
    SUM(x.target_count*x.weight) ideal_score, SUM(x.minutes) minutes
    FROM (
        SELECT LOWER(TRIM(COALESCE(le.unit_name,''))) unit_key,
               MAX(COALESCE(le.unit_name,'Tanpa Unit')) unit_name,
               le.user_id, le.activity_id, MAX(le.weight) weight,
               SUM(le.real_count) actual_count, MAX(le.ideal_count) target_count,
               COUNT(*) total_entries, SUM(le.duration_minutes) minutes
        FROM logbook_kpi_entries le $xW
        GROUP BY unit_key, le.user_id, le.activity_id
    ) x
    GROUP BY x.unit_key
    ORDER BY real_score DESC",$xP,$xT);

/* ── Output ── */
$xFnDate = sprintf('%04d%02d',$xYear,$xMonth);
$xFnUnit = $xFUnit>0?'_'.preg_replace('/[^A-Za-z0-9]+/','_',strtolower($xUnitLabel)):'';
$xFile   = "laporan_logbook_{$xFnDate}{$xFnUnit}.xls";

while(ob_get_level()>0) ob_end_clean();
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="'.$xFile.'"');
header('Cache-Control: max-age=0');
header('Pragma: public');

echo '<?xml version="1.0" encoding="UTF-8"?>'."\n".'<?mso-application progid="Excel.Sheet"?>'."\n";
?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Title>Laporan Logbook KPI <?= xe($xPeriod) ?></Title>
  <Author>RS Balimed – ITC Program</Author>
</DocumentProperties>
<Styles>
  <Style ss:ID="Default"/>
  <Style ss:ID="Title"><Font ss:Bold="1" ss:Size="13" ss:Color="#1E3A5F"/></Style>
  <Style ss:ID="Sub"><Font ss:Italic="1" ss:Size="10" ss:Color="#555555"/></Style>
  <Style ss:ID="H">
    <Font ss:Bold="1" ss:Color="#FFFFFF"/>
    <Interior ss:Color="#1D4ED8" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
  </Style>
  <Style ss:ID="H2">
    <Font ss:Bold="1" ss:Color="#FFFFFF"/>
    <Interior ss:Color="#0F766E" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
  </Style>
  <Style ss:ID="SH">
    <Font ss:Bold="1" ss:Color="#1E3A5F"/>
    <Interior ss:Color="#DBEAFE" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="UH">
    <Font ss:Bold="1" ss:Color="#FFFFFF" ss:Size="11"/>
    <Interior ss:Color="#0F4C75" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="Num"><Alignment ss:Horizontal="Right"/></Style>
  <Style ss:ID="Total">
    <Font ss:Bold="1" ss:Color="#1E3A5F"/>
    <Interior ss:Color="#EFF6FF" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Right"/>
  </Style>
  <Style ss:ID="TotalL">
    <Font ss:Bold="1" ss:Color="#1E3A5F"/>
    <Interior ss:Color="#EFF6FF" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="Alt"><Interior ss:Color="#F8FAFC" ss:Pattern="Solid"/></Style>
  <Style ss:ID="OK"><Font ss:Bold="1" ss:Color="#065F46"/><Interior ss:Color="#D1FAE5" ss:Pattern="Solid"/><Alignment ss:Horizontal="Center"/></Style>
  <Style ss:ID="Pend"><Font ss:Bold="1" ss:Color="#92400E"/><Interior ss:Color="#FEF3C7" ss:Pattern="Solid"/><Alignment ss:Horizontal="Center"/></Style>
  <!-- KPI Category styles -->
  <Style ss:ID="KA">
    <Font ss:Bold="1" ss:Color="#065F46" ss:Size="11"/>
    <Interior ss:Color="#D1FAE5" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center"/>
  </Style>
  <Style ss:ID="KB">
    <Font ss:Bold="1" ss:Color="#1E40AF" ss:Size="11"/>
    <Interior ss:Color="#DBEAFE" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center"/>
  </Style>
  <Style ss:ID="KC">
    <Font ss:Bold="1" ss:Color="#92400E" ss:Size="11"/>
    <Interior ss:Color="#FEF3C7" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center"/>
  </Style>
  <Style ss:ID="KD">
    <Font ss:Bold="1" ss:Color="#9A3412" ss:Size="11"/>
    <Interior ss:Color="#FFEDD5" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center"/>
  </Style>
  <Style ss:ID="KE">
    <Font ss:Bold="1" ss:Color="#9F1239" ss:Size="11"/>
    <Interior ss:Color="#FFE4E6" ss:Pattern="Solid"/>
    <Alignment ss:Horizontal="Center"/>
  </Style>
  <Style ss:ID="KNum">
    <Font ss:Bold="1" ss:Size="11"/>
    <Alignment ss:Horizontal="Right"/>
  </Style>
  <Style ss:ID="KNumAlt">
    <Font ss:Size="11"/>
    <Alignment ss:Horizontal="Right"/>
    <Interior ss:Color="#F8FAFC" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="KStr">
    <Font ss:Bold="1" ss:Size="11"/>
  </Style>
  <Style ss:ID="KStrAlt">
    <Font ss:Size="11"/>
    <Interior ss:Color="#F8FAFC" ss:Pattern="Solid"/>
  </Style>
</Styles>

<?php
/* ── Summary shortcuts ── */
$tE=(int)($qSum['total_entries']??0);$tS=(int)($qSum['total_staff']??0);
$tU=(int)($qSum['total_units']??0);$tA=(int)($qSum['total_activities']??0);
$tR=(int)($qSum['total_real']??0);$tP=(int)($qSum['total_patients']??0);
$tM=(int)($qSum['total_minutes']??0);$tV=(int)($qSum['verified_count']??0);
$tB=(int)($qSum['submitted_count']??0);$pV=$tE>0?round($tV/$tE*100,1):0;
?>

<!-- ═══════════════════════════════════════════════
     SHEET 1: RINGKASAN
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="Ringkasan">
<Table ss:DefaultColumnWidth="120">
<Column ss:Width="230"/><Column ss:Width="160"/><Column ss:Width="200"/>
<?= titrow('Ringkasan KPI Keperawatan',$xPeriod,$xUnitLabel) ?>
<?= hrow('Indikator','Nilai','Keterangan') ?>
<?= xrow(cs('Periode'),cs($xPeriod)) ?>
<?= xrow(cs('Unit'),cs($xUnitLabel)) ?>
<?= xrow(cs('Total Entri Logbook'),cn((float)$tE,'Num'),cs('jumlah baris diisi')) ?>
<?= xrow(cs('Staf Aktif Mengisi'),cn((float)$tS,'Num'),cs('staf unik')) ?>
<?= xrow(cs('Jumlah Unit'),cn((float)$tU,'Num')) ?>
<?= xrow(cs('Jenis Kegiatan'),cn((float)$tA,'Num'),cs('aktivitas unik')) ?>
<?= xrow(cs('Total Tindakan (Real Count)'),cn((float)$tR,'Num')) ?>
<?= xrow(cs('Total Pasien'),cn((float)$tP,'Num')) ?>
<?= xrow(cs('Total Durasi'),cs(mnt2str($tM)),cs($tM.' menit')) ?>
<?= xrow(cs('Terverifikasi'),cn((float)$tV,'Num'),cs($pV.'% dari total entri')) ?>
<?= xrow(cs('Menunggu Verifikasi'),cn((float)$tB,'Num')) ?>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<?= xrow(cs('REKAP PER DOMAIN KPI','SH')) ?>
<?= hrow('Domain KPI','Entri','Total Tindakan','Total Pasien','Durasi (mnt)','Terverifikasi','% Verifikasi') ?>
<?php
$gE=0;$gR=0;$gM=0;
foreach($qDomain as $i=>$d):
    $st=$i%2===1?'Alt':'';
    $pv=(int)$d['entri']>0?round((int)$d['verified_count']/(int)$d['entri']*100,1):0;
    $gE+=(int)$d['entri'];$gR+=(int)$d['total_real'];$gM+=(int)$d['total_minutes'];
    echo xrow(cs(ucwords(str_replace('_',' ',(string)$d['domain'])),$st),cn((float)$d['entri'],'Num'),cn((float)$d['total_real'],'Num'),cn((float)$d['total_patients'],'Num'),cn((float)$d['total_minutes'],'Num'),cn((float)$d['verified_count'],'Num'),cs($pv.'%',$st));
endforeach;
echo xrow(cs('TOTAL','TotalL'),cn((float)$gE,'Total'),cn((float)$gR,'Total'),blank(),cn((float)$gM,'Total'));
?>
</Table></Worksheet>

<!-- ═══════════════════════════════════════════════
     SHEET 2: SELURUH KEGIATAN
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="Seluruh Kegiatan">
<Table ss:DefaultColumnWidth="100">
<Column ss:Width="40"/><Column ss:Width="95"/><Column ss:Width="60"/>
<Column ss:Width="190"/><Column ss:Width="110"/><Column ss:Width="70"/>
<Column ss:Width="40"/><Column ss:Width="210"/><Column ss:Width="110"/>
<Column ss:Width="80"/><Column ss:Width="60"/><Column ss:Width="60"/>
<Column ss:Width="65"/><Column ss:Width="70"/><Column ss:Width="75"/>
<Column ss:Width="90"/><Column ss:Width="210"/>
<?= titrow('Seluruh Kegiatan Semua Staf',$xPeriod,$xUnitLabel) ?>
<?= hrow('No','Tanggal','Shift','Nama Staf','Unit','Role','Kat','Nama Kegiatan','Domain KPI','Dikerjakan','Target','Pasien','Durasi (mnt)','Mutu','Status','Kamar/Bed','Catatan') ?>
<?php
$xTotDur=0;
foreach($qAll as $i=>$r):
    $st=$i%2===1?'Alt':'';
    $stSt=match((string)$r['status']){'verified'=>'OK','submitted'=>'Pend',default=>''};
    $xTotDur+=(int)$r['durasi'];
    echo '<Row>'.cn($i+1,'Num').cs((string)$r['log_date'],$st).cs((string)$r['shift'],$st).cs((string)$r['staf'],$st).cs((string)$r['unit_name'],$st).cs((string)$r['role_level'],$st).cs((string)$r['kat'],$st).cs((string)$r['kegiatan'],$st).cs(ucwords(str_replace('_',' ',(string)$r['domain'])),$st).cn((float)$r['dikerjakan'],'Num').cn((float)$r['target'],'Num').cn((float)$r['pasien'],'Num').cn((float)$r['durasi'],'Num').($r['mutu']!==null?cn((float)$r['mutu'],'Num'):cs('-')).cs(ucfirst((string)$r['status']),$stSt).cs((string)$r['kamar'],$st).cs((string)$r['catatan'],$st).'</Row>'."\n";
endforeach;
echo xrow(cs('TOTAL','TotalL'),blank(11),cn((float)$xTotDur,'Total'),blank(4));
?>
</Table></Worksheet>

<!-- ═══════════════════════════════════════════════
     SHEET 3: REKAP PER STAF
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="Rekap Per Staf">
<Table ss:DefaultColumnWidth="100">
<Column ss:Width="35"/><Column ss:Width="195"/><Column ss:Width="120"/>
<Column ss:Width="85"/><Column ss:Width="70"/><Column ss:Width="80"/>
<Column ss:Width="75"/><Column ss:Width="90"/><Column ss:Width="80"/>
<Column ss:Width="80"/><Column ss:Width="75"/><Column ss:Width="80"/>
<Column ss:Width="80"/><Column ss:Width="75"/>
<?= titrow('Rekap Per Staf',$xPeriod,$xUnitLabel) ?>
<?= hrow('No','Nama Staf','Unit','Role','Hari Aktif','Jenis Kegiatan','Total Entri','Total Dikerjakan','Total Pasien','Total Menit','Total Jam','Terverifikasi','Menunggu','% Verifikasi') ?>
<?php
$gSE=0;$gSR=0;$gSP=0;$gSM=0;$gSV=0;
foreach($qStaf as $i=>$r):
    $st=$i%2===1?'Alt':'';
    $ent=(int)$r['total_entri'];$ver=(int)$r['terverifikasi'];$mnt2=(int)$r['total_menit'];
    $pv=$ent>0?round($ver/$ent*100,1):0;
    $gSE+=$ent;$gSR+=(int)$r['total_dikerjakan'];$gSP+=(int)$r['total_pasien'];$gSM+=$mnt2;$gSV+=$ver;
    echo '<Row>'.cn($i+1,'Num').cs((string)$r['staf'],$st).cs((string)$r['unit_name'],$st).cs((string)$r['role_level'],$st).cn((float)$r['hari_aktif'],'Num').cn((float)$r['jenis_kegiatan'],'Num').cn((float)$ent,'Num').cn((float)$r['total_dikerjakan'],'Num').cn((float)$r['total_pasien'],'Num').cn((float)$mnt2,'Num').cs(number_format($mnt2/60,1).' jam',$st).cn((float)$ver,'Num').cn((float)$r['menunggu'],'Num').cs($pv.'%',$st).'</Row>'."\n";
endforeach;
$gpv=$gSE>0?round($gSV/$gSE*100,1):0;
echo xrow(cs('TOTAL','TotalL'),blank(5),cn((float)$gSE,'Total'),cn((float)$gSR,'Total'),cn((float)$gSP,'Total'),cn((float)$gSM,'Total'),cs(number_format($gSM/60,1).' jam','TotalL'),cn((float)$gSV,'Total'),blank(),cs($gpv.'%','TotalL'));
?>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<?= xrow(cs('DETAIL KEGIATAN PER STAF','SH')) ?>
<?= hrow('No','Nama Staf','Unit','Nama Kegiatan','Kat','Domain KPI','Entri','Total Dikerjakan','Total Pasien','Total Menit','Terverifikasi','Tgl Pertama','Tgl Terakhir') ?>
<?php
$prevS='';$noS=0;
foreach($qStafAkt as $i=>$r):
    $st=$i%2===1?'Alt':'';
    if((string)$r['staf']!==$prevS){$noS++;$prevS=(string)$r['staf'];}
    echo '<Row>'.cn((float)$noS,'Num').cs((string)$r['staf'],$st).cs((string)$r['unit_name'],$st).cs((string)$r['kegiatan'],$st).cs((string)$r['kat'],$st).cs(ucwords(str_replace('_',' ',(string)$r['domain'])),$st).cn((float)$r['entri'],'Num').cn((float)$r['total_dikerjakan'],'Num').cn((float)$r['total_pasien'],'Num').cn((float)$r['total_menit'],'Num').cn((float)$r['terverifikasi'],'Num').cs((string)$r['tgl_pertama'],$st).cs((string)$r['tgl_terakhir'],$st).'</Row>'."\n";
endforeach;
?>
</Table></Worksheet>

<!-- ═══════════════════════════════════════════════
     SHEET 4: REKAP PER UNIT
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="Rekap Per Unit">
<Table ss:DefaultColumnWidth="100">
<Column ss:Width="35"/><Column ss:Width="150"/><Column ss:Width="75"/>
<Column ss:Width="85"/><Column ss:Width="80"/><Column ss:Width="100"/>
<Column ss:Width="80"/><Column ss:Width="85"/><Column ss:Width="85"/><Column ss:Width="90"/>
<?= titrow('Rekap Per Unit',$xPeriod,$xUnitLabel) ?>
<?= hrow('No','Unit','Jml Staf','Jenis Kegiatan','Total Entri','Total Dikerjakan','Total Pasien','Total Menit','Terverifikasi','% Verifikasi') ?>
<?php
$gUE=0;$gUA=0;$gUn=0;$gUR=0;$gUP=0;$gUM=0;$gUV=0;
foreach($qUnit as $i=>$r):
    $st=$i%2===1?'Alt':'';
    $ent=(int)$r['total_entri'];$ver=(int)$r['terverifikasi'];
    $pv=$ent>0?round($ver/$ent*100,1):0;
    $gUE+=(int)$r['jumlah_staf'];$gUA+=(int)$r['jenis_kegiatan'];$gUn+=$ent;
    $gUR+=(int)$r['total_dikerjakan'];$gUP+=(int)$r['total_pasien'];$gUM+=(int)$r['total_menit'];$gUV+=$ver;
    echo '<Row>'.cn($i+1,'Num').cs((string)$r['unit_name'],$st).cn((float)$r['jumlah_staf'],'Num').cn((float)$r['jenis_kegiatan'],'Num').cn((float)$ent,'Num').cn((float)$r['total_dikerjakan'],'Num').cn((float)$r['total_pasien'],'Num').cn((float)$r['total_menit'],'Num').cn((float)$ver,'Num').cs($pv.'%',$st).'</Row>'."\n";
endforeach;
$gpvu=$gUn>0?round($gUV/$gUn*100,1):0;
echo xrow(cs('TOTAL','TotalL'),blank(),cn((float)$gUE,'Total'),cn((float)$gUA,'Total'),cn((float)$gUn,'Total'),cn((float)$gUR,'Total'),cn((float)$gUP,'Total'),cn((float)$gUM,'Total'),cn((float)$gUV,'Total'),cs($gpvu.'%','TotalL'));
?>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<?= xrow(cs('DETAIL KEGIATAN PER UNIT','SH')) ?>
<?= hrow('No','Unit','Nama Kegiatan','Kat','Domain KPI','Jml Staf','Entri','Total Dikerjakan','Total Pasien','Total Menit','Terverifikasi') ?>
<?php
$prevU='';$noU=0;
foreach($qUnitAkt as $i=>$r):
    $st=$i%2===1?'Alt':'';
    if((string)$r['unit_name']!==$prevU){$noU++;$prevU=(string)$r['unit_name'];}
    echo '<Row>'.cn((float)$noU,'Num').cs((string)$r['unit_name'],$st).cs((string)$r['kegiatan'],$st).cs((string)$r['kat'],$st).cs(ucwords(str_replace('_',' ',(string)$r['domain'])),$st).cn((float)$r['jumlah_staf'],'Num').cn((float)$r['entri'],'Num').cn((float)$r['total_dikerjakan'],'Num').cn((float)$r['total_pasien'],'Num').cn((float)$r['total_menit'],'Num').cn((float)$r['terverifikasi'],'Num').'</Row>'."\n";
endforeach;
?>
</Table></Worksheet>

<!-- ═══════════════════════════════════════════════
     SHEET 5: KPI PER UNIT
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="KPI Per Unit">
<Table ss:DefaultColumnWidth="110">
<Column ss:Width="40"/>   <!-- No -->
<Column ss:Width="150"/>  <!-- Unit -->
<Column ss:Width="70"/>   <!-- Staf -->
<Column ss:Width="80"/>   <!-- Aktivitas -->
<Column ss:Width="90"/>   <!-- Skor Real -->
<Column ss:Width="90"/>   <!-- Skor Aktual -->
<Column ss:Width="90"/>   <!-- Overload -->
<Column ss:Width="90"/>   <!-- Target KPI -->
<Column ss:Width="80"/>   <!-- Capaian % -->
<Column ss:Width="90"/>   <!-- Beban Harian -->
<Column ss:Width="90"/>   <!-- Multiplier -->
<Column ss:Width="100"/>  <!-- KPI Adjusted -->
<Column ss:Width="90"/>   <!-- Durasi -->
<Column ss:Width="120"/>  <!-- Kategori KPI -->
<?= titrow('KPI Per Unit',$xPeriod,$xUnitLabel) ?>
<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">KPI Adjusted = Efektivitas × Multiplier Beban. Capaian = Skor Real / Target KPI. Rumus KPI: min(150%, Capaian × Multiplier).</Data></Cell></Row>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<?= hrow('No','Unit','Jml Staf','Aktivitas','Skor Real','Skor Aktual','Overload','Target KPI','Capaian %','Beban Harian','Multiplier','KPI Adjusted %','Durasi (jam)','Kategori KPI') ?>
<?php
$noKU=0;
$gKUReal=0;$gKUIdeal=0;$gKUWork=0;$gKUOver=0;$gKUMnt=0;
/* Gunakan $workloadMap & $monthlyWorkloadMap dari context logbook_kpi.php */
$wMap  = $workloadMap       ?? [];
$mwMap = $monthlyWorkloadMap ?? [];
foreach($qKpiUnit as $i=>$r):
    $st=$i%2===1?'Alt':'';
    $noKU++;
    $uk  = strtolower(trim((string)($r['unit_key']??'')));
    if(empty($uk)) $uk = function_exists('lbk_workload_unit_key')
        ? lbk_workload_unit_key((string)$r['unit_name']) : $uk;
    $mw  = $mwMap[$uk] ?? null;
    $wm  = $wMap[$uk]  ?? null;
    $ach = (float)$r['ideal_score']>0 ? round((float)$r['real_score']/(float)$r['ideal_score']*100,1) : 0;
    $monthlyIdealStaff = (float)($mw['ideal_staff_activities'] ?? 0);
    $staffCount = max(1,(int)$r['staff_count']);
    $unitEff = $monthlyIdealStaff>0 ? round((float)$r['total_entries']/$staffCount/$monthlyIdealStaff*100,1) : 0;
    $mul = $wm ? round((float)($wm['multiplier_calc']??1),2) : 1.00;
    $baseAdj = $monthlyIdealStaff>0 ? $unitEff : $ach;
    $adj = round(min(150,$baseAdj*$mul),1);
    $beban = $wm ? (string)($wm['level_calc']??'Belum Diisi') : 'Belum Diisi';
    $katKpi = kpi_label($adj); $stKpi = kpi_style($adj);
    $durJam = round((int)$r['minutes']/60,1);
    $gKUReal+=(float)$r['real_score'];$gKUIdeal+=(float)$r['ideal_score'];
    $gKUWork+=(float)$r['workload_score'];$gKUOver+=(float)$r['overload_score'];
    $gKUMnt+=(int)$r['minutes'];
    echo '<Row>'.cn((float)$noKU,'Num').cs((string)$r['unit_name'],'KStr').cn((float)$r['staff_count'],'KNum').cn((float)$r['total_entries'],'KNum').cn((float)$r['real_score'],'KNum').cn((float)$r['workload_score'],'KNum').cn((float)$r['overload_score'],'KNum').cn((float)$r['ideal_score'],'KNum').cs($ach.'%','KNum').cs($beban,'KStr').cs('x'.$mul,'KStr').cs($adj.'%',$stKpi).cs(number_format($durJam,1).' jam','KStr').cs($katKpi.' ('.$adj.'%)',$stKpi).'</Row>'."\n";
endforeach;
$gAch=$gKUIdeal>0?round($gKUReal/$gKUIdeal*100,1):0;
echo xrow(cs('TOTAL SELURUH UNIT','TotalL'),blank(),blank(),blank(),cn((float)$gKUReal,'Total'),cn((float)$gKUWork,'Total'),cn((float)$gKUOver,'Total'),cn((float)$gKUIdeal,'Total'),cs($gAch.'%','Total'),blank(),blank(),blank(),cs(number_format($gKUMnt/60,1).' jam','TotalL'));
?>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">Keterangan kategori KPI: Sangat Baik ≥90% | Baik ≥80% | Cukup ≥70% | Perlu Pembinaan ≥60% | Kurang &lt;60%</Data></Cell></Row>
</Table></Worksheet>

<!-- ═══════════════════════════════════════════════
     SHEET 6: KPI PER STAF DALAM UNIT
     ═══════════════════════════════════════════════ -->
<Worksheet ss:Name="KPI Per Staf">
<Table ss:DefaultColumnWidth="110">
<Column ss:Width="40"/>   <!-- No -->
<Column ss:Width="195"/>  <!-- Nama Staf -->
<Column ss:Width="90"/>   <!-- Role -->
<Column ss:Width="70"/>   <!-- Aktivitas -->
<Column ss:Width="90"/>   <!-- Skor Real -->
<Column ss:Width="90"/>   <!-- Capaian % -->
<Column ss:Width="90"/>   <!-- KPI Adjusted % -->
<Column ss:Width="85"/>   <!-- % Verified -->
<Column ss:Width="120"/>  <!-- Skor KPI Final -->
<Column ss:Width="120"/>  <!-- Kategori -->
<Column ss:Width="80"/>   <!-- Durasi -->
<?= titrow('KPI Per Staf dalam Unit',$xPeriod,$xUnitLabel) ?>
<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">Rumus Skor KPI Final = min(100, KPI_Adjusted×0.55 + Verified_Rate×0.25 + 20). Staf dikelompokkan per unit.</Data></Cell></Row>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<?php
$prevUnit=''; $noInUnit=0; $noGlobal=0;
foreach($qKpiStaf as $i=>$r):
    $curUnit = (string)$r['unit_name'];

    /* Header unit baru */
    if($curUnit !== $prevUnit):
        $prevUnit=$curUnit; $noInUnit=0;
        echo '<Row ss:Height="22">'.
            '<Cell ss:StyleID="UH" ss:MergeAcross="10"><Data ss:Type="String">'.xe('▌ '.$curUnit).'</Data></Cell>'.
        '</Row>'."\n";
        echo hrow('#','Nama Staf','Role','Aktivitas','Skor Real','Capaian %','KPI Adjusted %','% Terverifikasi','Skor KPI Final','Kategori KPI','Durasi (jam)');
    endif;

    $noGlobal++; $noInUnit++;
    $st = $noInUnit%2===1?'KStr':'KStrAlt';
    $stN= $noInUnit%2===1?'KNum':'KNumAlt';

    /* Hitung KPI sama persis dengan tab Staf di logbook_kpi.php */
    $ach    = (float)$r['ideal_score']>0 ? round((float)$r['real_score']/(float)$r['ideal_score']*100,1) : 0;
    $usesAdj= function_exists('lbk_role_uses_workload_adjustment')
              ? lbk_role_uses_workload_adjustment((string)$r['role_level'])
              : in_array((string)$r['role_level'],['pelaksana'],true);
    $uk     = function_exists('lbk_workload_unit_key')
              ? lbk_workload_unit_key((string)$r['unit_name'])
              : strtolower(trim((string)$r['unit_name']));
    $mw     = $usesAdj ? ($mwMap[$uk] ?? null) : null;
    $wm     = $usesAdj ? ($wMap[$uk]  ?? null) : null;
    $monthlyIdealStaff = (float)($mw['ideal_staff_activities'] ?? 0);
    $monthlyEff = $monthlyIdealStaff>0
        ? round((int)$r['total_entries']/$monthlyIdealStaff*100,1) : 0;
    $mul    = $wm ? (float)($wm['multiplier_calc']??1) : 1.0;
    $baseAch= ($usesAdj && $monthlyIdealStaff>0) ? $monthlyEff : $ach;
    $adjAch = $usesAdj ? round(min(150,$baseAch*$mul),1) : round(min(100,$ach),1);
    $vr     = (int)$r['total_entries']>0
              ? round((int)$r['verified_count']/(int)$r['total_entries']*100,1) : 0;
    $ks     = round(min(100,($adjAch*.55)+($vr*.25)+20),1);
    $katKpi = kpi_label($ks); $stKpi = kpi_style($ks);
    $salary = (float)$r['salary_amount'];
    $durJam = round((int)$r['minutes']/60,1);

    echo '<Row>'.
        cn((float)$noInUnit,'Num').
        cs((string)$r['employee_name'],$st).
        cs((string)$r['role_level'],$st).
        cn((float)$r['total_entries'],$stN).
        cn((float)$r['real_score'],$stN).
        cs($ach.'%',$stN).
        cs($adjAch.'%',$stN).
        cs($vr.'%',$stN).
        cn((float)$ks,$stKpi).
        cs($katKpi.' ('.$ks.'%)',$stKpi).
        cs(number_format($durJam,1).' jam',$stN).
    '</Row>'."\n";
endforeach;
?>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">Keterangan: KPI Adjusted = Efektivitas × Multiplier Beban Unit (untuk pelaksana). Manajemen (Katim/Kaunit/Kasubdiv/Kadiv) menggunakan Capaian Langsung tanpa multiplier.</Data></Cell></Row>
<Row><Cell ss:StyleID="Sub"><Data ss:Type="String">Kategori: Sangat Baik ≥90 | Baik ≥80 | Cukup ≥70 | Perlu Pembinaan ≥60 | Kurang &lt;60</Data></Cell></Row>
</Table></Worksheet>

</Workbook>
