<?php
require_once __DIR__ . '/../../bbs/db_conn.php';

$search_comp = $_GET['comp'] ?? '';
$search_year = $_GET['year'] ?? date('Y');
$search_dept = $_GET['dept'] ?? '';
$search_name = $_GET['name'] ?? '';
$search_comp_status = $_GET['comp_status'] ?? '';

try {
  $pdo = db_conn();

  // G1 메인 쿼리와 동일한 쿼리 사용
  // $sql_inner = "SELECT a.sys_comp_code, a.belong_comp
  //   , (SELECT code_name FROM edu_codes c WHERE c.group_code = 'CO100' AND c.code = a.belong_comp LIMIT 1) AS comp_name
  //   , a.name, a.member_id
  //   , a.dept_name
  //   , IFNULL(b.formatted_tm, '00시간 00분') AS all_tm
  //   , CASE WHEN a.member_id IN (
  //     SELECT u2.member_id FROM edu_users u2 WHERE NOT EXISTS (
  //       SELECT 1 FROM edu_contents c2 WHERE c2.category_code = 'CA10003' AND c2.base_year = ? AND c2.is_active = '1'
  //       AND NOT EXISTS (SELECT 1 FROM edu_learning_histories h2 WHERE h2.content_id = c2.content_id AND h2.member_id = u2.member_id AND h2.sys_comp_code = u2.sys_comp_code AND h2.completed_at IS NOT NULL AND h2.completed_at != '')
  //     )
  //   ) THEN '수료' ELSE '미수료' END AS completion_status
  //   , fn_get_progress_rate(a.sys_comp_code, ?, a.member_id, 'CA10003', '') AS progress_rate
  //   , fn_get_completion_date(a.sys_comp_code, ?, a.member_id, 'CA10003', '') AS completion_date
  // FROM edu_users a
  // LEFT JOIN (
  //   SELECT x.sys_comp_code, x.member_id,
  //     CONCAT(LPAD(FLOOR(SUM(CASE WHEN x.completed_at IS NOT NULL AND x.completed_at <> '' THEN x.content_tm ELSE x.watch_tm END)/3600),2,'0'),'시간 ',
  //            LPAD(FLOOR((SUM(CASE WHEN x.completed_at IS NOT NULL AND x.completed_at <> '' THEN x.content_tm ELSE x.watch_tm END)%3600)/60),2,'0'),'분') AS formatted_tm
  //   FROM edu_learning_histories x JOIN edu_contents y ON x.content_id = y.content_id
  //   WHERE y.category_code = 'CA10003' AND YEAR(x.first_viewed_at) = ?
  //   GROUP BY x.sys_comp_code, x.member_id
  // ) b ON a.member_id = b.member_id AND a.sys_comp_code = b.sys_comp_code
  // WHERE (a.end_date IS NULL OR a.end_date = '' OR (a.end_date > '1000-01-01' AND YEAR(a.end_date) >= ?))
  //   AND (? = '' OR a.belong_comp = ?)
  //   AND a.dept_name LIKE CONCAT('%', ?, '%')
  //   AND a.name LIKE CONCAT('%', ?, '%')";

  $sql_inner = "SELECT a.sys_comp_code, a.belong_comp
    , (SELECT code_name FROM edu_codes c WHERE c.group_code = 'CO100' AND c.code = a.belong_comp LIMIT 1) AS comp_name
    , a.name, a.member_id
    , a.dept_name
    , IFNULL(b.formatted_tm, '00시간 00분') AS all_tm
    , CASE WHEN a.member_id IN (
      SELECT u2.member_id FROM edu_users u2 WHERE NOT EXISTS (
        SELECT 1 FROM edu_contents c2 WHERE c2.category_code = 'CA10003' AND c2.base_year = ? AND c2.is_active = '1'
        AND NOT EXISTS (SELECT 1 FROM edu_learning_histories h2 WHERE h2.content_id = c2.content_id AND h2.member_id = u2.member_id AND h2.sys_comp_code = u2.sys_comp_code AND h2.completed_at IS NOT NULL AND h2.completed_at != '')
      )
    ) THEN '수료' ELSE '미수료' END AS completion_status
    , fn_get_progress_rate(a.sys_comp_code, ?, a.member_id, 'CA10003', '') AS progress_rate  -- 진행율
    , fn_get_completion_date(a.sys_comp_code, ?, a.member_id, 'CA10003', '') AS completion_date  -- 학습완료일
  FROM edu_users a
  LEFT JOIN (
    SELECT z.sys_comp_code, x.member_id,
      CONCAT(LPAD(FLOOR(SUM(CASE WHEN x.completed_at IS NOT NULL AND x.completed_at <> '' THEN x.content_tm ELSE x.watch_tm END)/3600),2,'0'),'시간 ',
             LPAD(FLOOR((SUM(CASE WHEN x.completed_at IS NOT NULL AND x.completed_at <> '' THEN x.content_tm ELSE x.watch_tm END)%3600)/60),2,'0'),'분') AS formatted_tm
    FROM edu_learning_histories x JOIN edu_contents y ON x.content_id = y.content_id
    JOIN edu_users z ON x.sys_comp_code = z.working_comp AND  x.member_id = z.member_id
    WHERE y.category_code = 'CA10003' AND YEAR(x.first_viewed_at) = ?
    GROUP BY x.sys_comp_code, x.member_id
  ) b ON a.member_id = b.member_id AND a.sys_comp_code = b.sys_comp_code
  WHERE (a.end_date IS NULL OR a.end_date = '' OR (a.end_date > '1000-01-01' AND YEAR(a.end_date) >= ?))
    and a.sys_comp_code = a.belong_comp
    AND (? = '' OR a.belong_comp = ?)
    AND a.dept_name LIKE CONCAT('%', ?, '%')
    AND a.name LIKE CONCAT('%', ?, '%')";


  if ($search_comp_status === 'Y') {
    $sql = "SELECT * FROM ($sql_inner) t WHERE completion_status = '수료'";
  } elseif ($search_comp_status === 'N') {
    $sql = "SELECT * FROM ($sql_inner) t WHERE completion_status = '미수료'";
  } else {
    $sql = "SELECT * FROM ($sql_inner) t";
  }

  $stmt = $pdo->prepare($sql);
  $stmt->execute([$search_year, $search_year, $search_year, $search_year, $search_year, $search_comp, $search_comp, $search_dept, $search_name]);
  $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);


} catch (Exception $e) {
  die("DB Error");
}

header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=legal_edu_result_" . date('Ymd') . ".xls");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");

echo '<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body>';
echo '<table border="1">';
echo '<thead><tr>';
echo '<th>NO</th>';
echo '<th>소속법인</th>';
echo '<th>성명</th>';
echo '<th>사번</th>';
echo '<th>부서</th>';
echo '<th>학습시간</th>';
echo '<th>진도율</th>';
echo '<th>교육이수일</th>';
echo '<th>수료구분</th>';
echo '</tr></thead>';
echo '<tbody>';

$idx = 1;
foreach ($rows as $row) {
  echo '<tr>';
  echo '<td>' . $idx++ . '</td>';
  echo '<td>' . htmlspecialchars($row['comp_name'] ?? '') . '</td>';
  echo '<td>' . htmlspecialchars($row['name'] ?? '') . '</td>';
  echo '<td>' . htmlspecialchars($row['member_id'] ?? '') . '</td>';
  echo '<td>' . htmlspecialchars($row['dept_name'] ?? '') . '</td>';
  echo '<td>' . htmlspecialchars($row['all_tm'] ?? '') . '</td>';

  // 진행율 처리
  $progress = $row['progress_rate'] ?? '0%';
  $progress_value = is_numeric($progress) ? (int) $progress : (int) preg_replace('/[^0-9]/', '', $progress);
  echo '<td>' . $progress_value . '%</td>';

  echo '<td>' . htmlspecialchars($row['completion_date'] ?? '-') . '</td>';
  echo '<td>' . htmlspecialchars($row['completion_status']) . '</td>';
  echo '</tr>';
}

echo '</tbody></table></body></html>';