그누보드 메일 발송기록 페이지 정보
그누보드 메일 발송기록 페이지본문
회원메일 발송기록 확인페이지입니다.
---------------------------------------------------------------------------------------------------
1. 로그 테이블
만들기
CREATE TABLE IF NOT EXISTS g5_mail_member_log (
ml_id INT(11) NOT NULL AUTO_INCREMENT,
ma_id INT(11) NOT NULL DEFAULT 0, -- g5_mail.ma_id (캠페인 ID)
ml_mb_id VARCHAR(20) NOT NULL DEFAULT '', -- 회원 아이디
ml_mb_email VARCHAR(255) NOT NULL DEFAULT '', -- 회원 이메일
ml_mb_name VARCHAR(50) NOT NULL DEFAULT '', -- 이름
ml_mb_nick VARCHAR(50) NOT NULL DEFAULT '', -- 닉네임
ml_result ENUM('Y','N') NOT NULL DEFAULT 'Y', -- 발송 성공/실패
ml_datetime DATETIME NOT NULL, -- 발송 시각
ml_ip VARCHAR(45) NOT NULL DEFAULT '', -- 발송한 관리자 IP
PRIMARY KEY (ml_id),
KEY idx_ma_id (ma_id),
KEY idx_mb_id (ml_mb_id),
KEY idx_datetime (ml_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---------------------------------------------------------------------------------------------------
2. /adm/mail_select_update.php 수정
$cnt = 0; 이부분 찾아서 전체수정.
$cnt = 0;
for ($i = 0; $i < count($member_list); $i++) {
list($to_email, $mb_id, $name, $nick, $datetime) = explode("||", trim($member_list[$i]));
$sw = preg_match("/[0-9a-zA-Z_]+(\.[0-9a-zA-Z_]+)*@[0-9a-zA-Z_]+(\.[0-9a-zA-Z_]+)*/", $to_email);
// 올바른 메일 주소만
if ($sw == true) {
$cnt++;
$mb_md5 = md5($mb_id . $to_email . $datetime);
$content = $ma['ma_content'];
$content = preg_replace("/{이름}/", $name, (string)$content);
$content = preg_replace("/{닉네임}/", $nick, (string)$content);
$content = preg_replace("/{회원아이디}/", $mb_id, (string)$content);
$content = preg_replace("/{이메일}/", $to_email, (string)$content);
$content = $content . "<hr size=0><p><span style='font-size:9pt; font-family:굴림'>▶ 더 이상 정보 수신을 원치 않으시면 [<a href='" . G5_BBS_URL . "/email_stop.php?mb_id={$mb_id}&mb_md5={$mb_md5}' target='_blank'>수신거부</a>] 해 주십시오.</span></p>";
// ─────────────────────────────
// ① 메일 발송
// ─────────────────────────────
$is_mail_sent = mailer(
$config['cf_admin_email_name'],
$config['cf_admin_email'],
$to_email,
$subject,
$content,
1
);
// ─────────────────────────────
// ② 발송 로그 기록
// ─────────────────────────────
$log_sql = "
INSERT INTO g5_mail_member_log
SET ma_id = '{$ma_id}',
ml_mb_id = '" . sql_escape_string($mb_id) . "',
ml_mb_email = '" . sql_escape_string($to_email) . "',
ml_mb_name = '" . sql_escape_string($name) . "',
ml_mb_nick = '" . sql_escape_string($nick) . "',
ml_result = '" . ($is_mail_sent ? 'Y' : 'N') . "',
ml_datetime = '" . G5_TIME_YMDHIS . "',
ml_ip = '" . $_SERVER['REMOTE_ADDR'] . "'
";
sql_query($log_sql);
// ─────────────────────────────
// ③ 화면 출력 부분 (원래 코드 그대로)
// ─────────────────────────────
echo "<script> document.all.cont.innerHTML += '$cnt. $to_email ($mb_id : $name)<br>'; </script>\n";
//echo "+";
flush();
ob_flush();
ob_end_flush();
usleep($sleepsec);
if ($cnt % $countgap == 0) {
echo "<script> document.all.cont.innerHTML += '<br>'; document.body.scrollTop += 1000; </script>\n";
}
// 화면을 지운다... 부하를 줄임
if ($cnt % $maxscreen == 0) {
echo "<script> document.all.cont.innerHTML = ''; document.body.scrollTop += 1000; </script>\n";
}
}
}
---------------------------------------------------------------------------------------------------
3. 기록확인 화면
생성
/adm/mail_member_log.php
<?php
$sub_menu = "200400"; // 회원메일발송과 같은 권한 사용
include_once('./_common.php');
auth_check_menu($auth, $sub_menu, 'r');
$g5['title'] = '회원메일 발송기록';
// 검색 파라미터
$fr_date = isset($_GET['fr_date']) ? preg_replace('/[^0-9\-]/', '', $_GET['fr_date']) : '';
$to_date = isset($_GET['to_date']) ? preg_replace('/[^0-9\-]/', '', $_GET['to_date']) : '';
$ml_result = isset($_GET['ml_result']) ? preg_replace('/[^YN]/', '', $_GET['ml_result']) : '';
$sfl = isset($_GET['sfl']) ? trim($_GET['sfl']) : 'l.ml_mb_id';
$stx = isset($_GET['stx']) ? trim($_GET['stx']) : '';
// sfl 화이트리스트
$allowed_sfl = array(
'l.ml_mb_id' => 'l.ml_mb_id',
'l.ml_mb_email' => 'l.ml_mb_email',
'l.ml_mb_name' => 'l.ml_mb_name',
'l.ml_mb_nick' => 'l.ml_mb_nick',
'm.ma_subject' => 'm.ma_subject',
);
if (!array_key_exists($sfl, $allowed_sfl)) {
$sfl = 'l.ml_mb_id';
}
$sql_common = "
FROM g5_mail_member_log AS l
LEFT JOIN {$g5['mail_table']} AS m ON l.ma_id = m.ma_id
";
$sql_search = " WHERE 1 ";
// 날짜 검색
if ($fr_date) {
$sql_search .= " AND l.ml_datetime >= '{$fr_date} 00:00:00' ";
}
if ($to_date) {
$sql_search .= " AND l.ml_datetime <= '{$to_date} 23:59:59' ";
}
// 발송 결과(Y/N) 검색
if ($ml_result !== '') {
$sql_search .= " AND l.ml_result = '{$ml_result}' ";
}
// 텍스트 검색
if ($stx && $sfl) {
$stx = sql_real_escape_string($stx);
$sql_search .= " AND {$sfl} LIKE '%{$stx}%' ";
}
// 전체 건수
$sql = " SELECT COUNT(*) AS cnt {$sql_common} {$sql_search} ";
$row = sql_fetch($sql);
$total_count = isset($row['cnt']) ? (int)$row['cnt'] : 0;
// 페이징 설정
$rows = 50;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($page < 1) $page = 1;
$total_page = $total_count > 0 ? (int)ceil($total_count / $rows) : 1;
$from_record = ($page - 1) * $rows;
// 실제 데이터 조회
$sql = "
SELECT l.*, m.ma_subject
{$sql_common}
{$sql_search}
ORDER BY l.ml_id DESC
LIMIT {$from_record}, {$rows}
";
$result = sql_query($sql);
// 쿼리스트링
$qstr = "fr_date={$fr_date}&to_date={$to_date}&ml_result={$ml_result}&sfl={$sfl}&stx=" . urlencode($stx);
include_once('./admin.head.php');
?>
<div class="local_ov01 local_ov">
총 <?php echo number_format($total_count); ?>건의 발송기록이 있습니다.
</div>
<div class="local_sch local_sch01">
<form id="fsearch" name="fsearch" method="get">
<input type="hidden" name="page" value="1">
<label for="fr_date" class="sound_only">시작일</label>
<input type="date" name="fr_date" id="fr_date" value="<?php echo $fr_date; ?>" class="frm_input" style="width:130px;">
~
<label for="to_date" class="sound_only">종료일</label>
<input type="date" name="to_date" id="to_date" value="<?php echo $to_date; ?>" class="frm_input" style="width:130px;">
<label for="ml_result">결과</label>
<select name="ml_result" id="ml_result">
<option value="" <?php echo $ml_result === '' ? 'selected' : ''; ?>>전체</option>
<option value="Y" <?php echo $ml_result === 'Y' ? 'selected' : ''; ?>>성공</option>
<option value="N" <?php echo $ml_result === 'N' ? 'selected' : ''; ?>>실패</option>
</select>
<select name="sfl" id="sfl">
<option value="l.ml_mb_id" <?php echo $sfl === 'l.ml_mb_id' ? 'selected' : ''; ?>>회원아이디</option>
<option value="l.ml_mb_email" <?php echo $sfl === 'l.ml_mb_email' ? 'selected' : ''; ?>>이메일</option>
<option value="l.ml_mb_name" <?php echo $sfl === 'l.ml_mb_name' ? 'selected' : ''; ?>>이름</option>
<option value="l.ml_mb_nick" <?php echo $sfl === 'l.ml_mb_nick' ? 'selected' : ''; ?>>닉네임</option>
<option value="m.ma_subject" <?php echo $sfl === 'm.ma_subject' ? 'selected' : ''; ?>>캠페인제목</option>
</select>
<label for="stx" class="sound_only">검색어<strong class="sound_only"> 필수</strong></label>
<input type="text" name="stx" id="stx" value="<?php echo get_text($stx); ?>" class="frm_input" size="30">
<input type="submit" class="btn_submit" value="검색">
</form>
</div>
<div class="tbl_head01 tbl_wrap">
<table>
<caption><?php echo $g5['title']; ?> 목록</caption>
<thead>
<tr>
<th scope="col">번호</th>
<th scope="col">발송일시</th>
<th scope="col">캠페인(ID / 제목)</th>
<th scope="col">회원아이디</th>
<th scope="col">이름 / 닉네임</th>
<th scope="col">E-mail</th>
<th scope="col">결과</th>
<th scope="col">IP</th>
</tr>
</thead>
<tbody>
<?php
if ($total_count == 0) {
?>
<tr>
<td colspan="8" class="empty_table">발송기록이 없습니다.</td>
</tr>
<?php
} else {
for ($i = 0; $row = sql_fetch_array($result); $i++) {
$num = $total_count - ($page - 1) * $rows - $i;
$bg = 'bg' . ($i % 2);
$datetime = substr($row['ml_datetime'], 0, 16); // yyyy-mm-dd hh:ii
$ma_subject = get_text($row['ma_subject']);
if ($ma_subject === '') {
$ma_subject = '-';
}
$result_txt = ($row['ml_result'] === 'Y') ? '성공' : '실패';
$result_color = ($row['ml_result'] === 'Y') ? '#15803d' : '#b91c1c';
?>
<tr class="<?php echo $bg; ?>">
<td class="td_num"><?php echo $num; ?></td>
<td class="td_datetime"><?php echo $datetime; ?></td>
<td class="td_left">
<span class="sv_sbj">
[<?php echo $row['ma_id']; ?>]
<?php echo $ma_subject; ?>
</span>
</td>
<td class="td_mbid"><?php echo get_text($row['ml_mb_id']); ?></td>
<td class="td_mbname">
<?php echo get_text($row['ml_mb_name']); ?>
<?php if ($row['ml_mb_nick']) { ?>
<span class="sv_nick">(<?php echo get_text($row['ml_mb_nick']); ?>)</span>
<?php } ?>
</td>
<td class="td_email"><?php echo get_text($row['ml_mb_email']); ?></td>
<td class="td_numsmall">
<span style="color:<?php echo $result_color; ?>; font-weight:bold;">
<?php echo $result_txt; ?>
</span>
</td>
<td class="td_ip"><?php echo get_text($row['ml_ip']); ?></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
<div class="local_frm01 local_frm">
<?php
echo get_paging($config['cf_write_pages'], $page, $total_page, "./mail_member_log.php?{$qstr}&page=");
?>
</div>
<?php
include_once('./admin.tail.php');
---------------------------------------------------------------------------------------------------
4. 관리자 메뉴추가
/adm/admin.menu200.php
array("200400", "회원메일 발송기록", G5_ADMIN_URL."/mail_member_log.php", "mb_mail"),
3
댓글 3개
감사합니다^^
감사합니다.
감사합니다
