321 lines
11 KiB
PHP
321 lines
11 KiB
PHP
<?php
|
|
//=========================
|
|
// 콘텐츠리스트 AJAX
|
|
//=========================
|
|
require_once __DIR__ . '/../bbs/db_conn.php';
|
|
|
|
header('Content-Type: application/json; charset=utf-8');
|
|
|
|
// TODO: 실제 로그인 세션 연동 후 교체
|
|
$memberId = 'U001';
|
|
$sysCompCode = 'COMP01';
|
|
|
|
function mypage_json(array $data): void
|
|
{
|
|
echo json_encode($data, JSON_UNESCAPED_UNICODE);
|
|
exit;
|
|
}
|
|
|
|
function mypage_h(?string $str): string
|
|
{
|
|
return htmlspecialchars((string)$str, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
|
|
}
|
|
|
|
function map_content_row(array $row, array $catMap = []): array
|
|
{
|
|
$kwStr = (string)($row['keywords'] ?? '');
|
|
$keywords = ($kwStr !== '') ? explode(',', $kwStr) : [];
|
|
|
|
$watchTm = (float)($row['watch_tm'] ?? 0);
|
|
$contentTm = (float)($row['content_tm'] ?? 0);
|
|
$gauge = ($contentTm > 0) ? (int)round(($watchTm / $contentTm) * 100) : 0;
|
|
$gauge = max(0, min(100, $gauge));
|
|
|
|
$raw = trim((string)($row['content_url'] ?? ''));
|
|
if (preg_match('/(?:v=|youtu\.be\/)([A-Za-z0-9_-]{11})/', $raw, $m)) {
|
|
$videoId = $m[1];
|
|
} elseif (preg_match('/^[A-Za-z0-9_-]{11}$/', $raw)) {
|
|
$videoId = $raw;
|
|
} else {
|
|
$videoId = '';
|
|
}
|
|
|
|
$thumbFromDb = trim((string)($row['thumbnail_url'] ?? ''));
|
|
$url = $videoId !== '' ? "https://www.youtube.com/watch?v={$videoId}" : $raw;
|
|
$thumbnail = $thumbFromDb !== ''
|
|
? $thumbFromDb
|
|
: ($videoId !== '' ? "https://img.youtube.com/vi/{$videoId}/sddefault.jpg" : '/edu/img/video/img_thumb_01.png');
|
|
|
|
return [
|
|
'content_id' => $row['content_id'] ?? '',
|
|
'category_code' => $row['category_code'] ?? '',
|
|
'category_name' => $catMap[$row['category_code'] ?? ''] ?? ($row['category_code'] ?? ''),
|
|
'title' => $row['title'] ?? '',
|
|
'url' => $url,
|
|
'thumbnail' => $thumbnail,
|
|
'watch_tm' => (int)($row['watch_tm'] ?? 0),
|
|
'content_tm' => (int)($row['content_tm'] ?? 0),
|
|
'gauge' => $gauge,
|
|
'is_bookmarked' => !empty($row['is_bookmarked']),
|
|
'keywords' => $keywords,
|
|
];
|
|
}
|
|
|
|
function category_tag_class(string $categoryCode): string
|
|
{
|
|
$map = [
|
|
'CA10001' => 'myclass',
|
|
'CA10002' => 'onboarding',
|
|
'CA10003' => 'legal',
|
|
'CA10004' => 'leader',
|
|
'CA10005' => 'insight',
|
|
'CA10006' => 'biz',
|
|
];
|
|
|
|
return $map[$categoryCode] ?? 'etc';
|
|
}
|
|
|
|
try {
|
|
$pdo = db_conn();
|
|
$pdo->exec("SET NAMES 'utf8mb4'");
|
|
|
|
$tab = trim((string)($_GET['tab'] ?? 'watching'));
|
|
$page = max(1, (int)($_GET['page'] ?? 1));
|
|
$limit = max(1, (int)($_GET['limit'] ?? 12));
|
|
$offset = ($page - 1) * $limit;
|
|
|
|
$CATEGORY_MAP = [
|
|
'CA10001' => '마이클래스',
|
|
'CA10002' => '온보딩',
|
|
'CA10003' => '법정교육',
|
|
'CA10004' => '리더십',
|
|
'CA10005' => '인사이트',
|
|
'CA10006' => '비즈트렌드',
|
|
];
|
|
|
|
$stmtCode = $pdo->prepare("
|
|
SELECT base_code, code_name
|
|
FROM edu_codes
|
|
WHERE group_code = 'CA100'
|
|
AND is_active = '1'
|
|
ORDER BY code
|
|
");
|
|
$stmtCode->execute();
|
|
foreach ($stmtCode->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$CATEGORY_MAP[$row['base_code']] = $row['code_name'];
|
|
}
|
|
|
|
// 탭 카운트
|
|
$stmtCountWatching = $pdo->prepare("
|
|
SELECT COUNT(*)
|
|
FROM edu_learning_histories
|
|
WHERE member_id = :member_id
|
|
AND sys_comp_code = :sys_comp_code
|
|
AND watch_tm < content_tm
|
|
");
|
|
$stmtCountWatching->execute([
|
|
':member_id' => $memberId,
|
|
':sys_comp_code' => $sysCompCode,
|
|
]);
|
|
$countWatching = (int)$stmtCountWatching->fetchColumn();
|
|
|
|
$stmtCountCompleted = $pdo->prepare("
|
|
SELECT COUNT(*)
|
|
FROM edu_learning_histories
|
|
WHERE member_id = :member_id
|
|
AND sys_comp_code = :sys_comp_code
|
|
AND watch_tm >= content_tm
|
|
");
|
|
$stmtCountCompleted->execute([
|
|
':member_id' => $memberId,
|
|
':sys_comp_code' => $sysCompCode,
|
|
]);
|
|
$countCompleted = (int)$stmtCountCompleted->fetchColumn();
|
|
|
|
$stmtCountSaved = $pdo->prepare("
|
|
SELECT COUNT(*)
|
|
FROM edu_content_wishlist
|
|
WHERE member_id = :member_id
|
|
AND sys_comp_code = :sys_comp_code
|
|
AND is_active = '1'
|
|
");
|
|
$stmtCountSaved->execute([
|
|
':member_id' => $memberId,
|
|
':sys_comp_code' => $sysCompCode,
|
|
]);
|
|
$countSaved = (int)$stmtCountSaved->fetchColumn();
|
|
|
|
if ($tab === 'saved') {
|
|
$countTotal = $countSaved;
|
|
|
|
$sql = "
|
|
SELECT
|
|
c.content_id,
|
|
c.category_code,
|
|
c.title,
|
|
c.content_url,
|
|
c.thumbnail_url,
|
|
lh.watch_tm,
|
|
lh.content_tm,
|
|
1 AS is_bookmarked,
|
|
GROUP_CONCAT(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
|
|
FROM edu_content_wishlist cw
|
|
INNER JOIN edu_contents c
|
|
ON c.content_id = cw.content_id
|
|
LEFT JOIN edu_learning_histories lh
|
|
ON lh.content_id = c.content_id
|
|
AND lh.member_id = cw.member_id
|
|
AND lh.sys_comp_code = cw.sys_comp_code
|
|
LEFT JOIN edu_content_keywords ck
|
|
ON ck.content_id = c.content_id
|
|
WHERE cw.member_id = :member_id
|
|
AND cw.sys_comp_code = :sys_comp_code
|
|
AND cw.is_active = '1'
|
|
GROUP BY c.content_id
|
|
ORDER BY COALESCE(cw.favorited_at, cw.updated_at) DESC
|
|
LIMIT :limit OFFSET :offset
|
|
";
|
|
} elseif ($tab === 'completed') {
|
|
$countTotal = $countCompleted;
|
|
|
|
$sql = "
|
|
SELECT
|
|
c.content_id,
|
|
c.category_code,
|
|
c.title,
|
|
c.content_url,
|
|
c.thumbnail_url,
|
|
lh.watch_tm,
|
|
lh.content_tm,
|
|
CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
|
|
GROUP_CONCAT(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
|
|
FROM edu_learning_histories lh
|
|
INNER JOIN edu_contents c
|
|
ON c.content_id = lh.content_id
|
|
LEFT JOIN edu_content_wishlist cw
|
|
ON cw.content_id = c.content_id
|
|
AND cw.member_id = lh.member_id
|
|
AND cw.sys_comp_code = lh.sys_comp_code
|
|
AND cw.is_active = '1'
|
|
LEFT JOIN edu_content_keywords ck
|
|
ON ck.content_id = c.content_id
|
|
WHERE lh.member_id = :member_id
|
|
AND lh.sys_comp_code = :sys_comp_code
|
|
AND lh.watch_tm >= lh.content_tm
|
|
GROUP BY c.content_id
|
|
ORDER BY COALESCE(lh.completed_at, lh.last_viewed_at) DESC
|
|
LIMIT :limit OFFSET :offset
|
|
";
|
|
} else {
|
|
$tab = 'watching';
|
|
$countTotal = $countWatching;
|
|
|
|
$sql = "
|
|
SELECT
|
|
c.content_id,
|
|
c.category_code,
|
|
c.title,
|
|
c.content_url,
|
|
c.thumbnail_url,
|
|
lh.watch_tm,
|
|
lh.content_tm,
|
|
CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
|
|
GROUP_CONCAT(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
|
|
FROM edu_learning_histories lh
|
|
INNER JOIN edu_contents c
|
|
ON c.content_id = lh.content_id
|
|
LEFT JOIN edu_content_wishlist cw
|
|
ON cw.content_id = c.content_id
|
|
AND cw.member_id = lh.member_id
|
|
AND cw.sys_comp_code = lh.sys_comp_code
|
|
AND cw.is_active = '1'
|
|
LEFT JOIN edu_content_keywords ck
|
|
ON ck.content_id = c.content_id
|
|
WHERE lh.member_id = :member_id
|
|
AND lh.sys_comp_code = :sys_comp_code
|
|
AND lh.watch_tm < lh.content_tm
|
|
GROUP BY c.content_id
|
|
ORDER BY lh.last_viewed_at DESC
|
|
LIMIT :limit OFFSET :offset
|
|
";
|
|
}
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->bindValue(':member_id', $memberId, PDO::PARAM_STR);
|
|
$stmt->bindValue(':sys_comp_code', $sysCompCode, PDO::PARAM_STR);
|
|
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
|
|
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
ob_start();
|
|
|
|
foreach ($rows as $row) {
|
|
$item = map_content_row($row, $CATEGORY_MAP);
|
|
$tagClass = category_tag_class($item['category_code']);
|
|
?>
|
|
<li
|
|
class="content-card <?php if ($tab === 'completed') { ?>content-card--completed<?php } ?>"
|
|
data-content-id="<?= mypage_h($item['content_id']) ?>"
|
|
>
|
|
<a href="<?= mypage_h($item['url']) ?>" class="card-link">
|
|
<label class="bookmark" for="wish_<?= mypage_h($tab . '_' . $item['content_id']) ?>" onclick="event.stopPropagation();">
|
|
<input
|
|
type="checkbox"
|
|
id="wish_<?= mypage_h($tab . '_' . $item['content_id']) ?>"
|
|
title="저장"
|
|
<?php if ($item['is_bookmarked']) { ?>checked<?php } ?>
|
|
>
|
|
</label>
|
|
|
|
<div class="item-thumb">
|
|
<img src="<?= mypage_h($item['thumbnail']) ?>" alt="<?= mypage_h($item['title']) ?>">
|
|
<div class="gauge-bar rect">
|
|
<div class="gauge-fill" style="width: <?= (int)$item['gauge'] ?>%"></div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="item-info">
|
|
<span class="category-tag <?= mypage_h($tagClass) ?>">
|
|
<?= mypage_h($item['category_name']) ?>
|
|
</span>
|
|
<strong class="item-title"><?= mypage_h($item['title']) ?></strong>
|
|
|
|
<div class="tag-list">
|
|
<?php foreach ($item['keywords'] as $kw): ?>
|
|
<?php if (trim((string)$kw) === '') { continue; } ?>
|
|
<span class="tag"><?= mypage_h($kw) ?></span>
|
|
<?php endforeach; ?>
|
|
</div>
|
|
|
|
<button type="button" class="btn-remove-card" title="삭제"></button>
|
|
</div>
|
|
</a>
|
|
</li>
|
|
<?php
|
|
}
|
|
|
|
$html = ob_get_clean();
|
|
|
|
mypage_json([
|
|
'success' => true,
|
|
'tab' => $tab,
|
|
'page' => $page,
|
|
'limit' => $limit,
|
|
'total_count' => $countTotal,
|
|
'counts' => [
|
|
'watching' => $countWatching,
|
|
'completed' => $countCompleted,
|
|
'saved' => $countSaved,
|
|
],
|
|
'html' => $html,
|
|
'is_last' => (($offset + $limit) >= $countTotal),
|
|
]);
|
|
} catch (Throwable $e) {
|
|
mypage_json([
|
|
'success' => false,
|
|
'message' => '콘텐츠 리스트를 불러오지 못했습니다.',
|
|
'error' => $e->getMessage(),
|
|
]);
|
|
}
|