디지털 혁신과
FinTech 솔루션으로
비즈니스를 연결합니다

<?php
// submit.php  -- 폼 데이터 수신 후 DB 저장
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: https://hpsystem.net');

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
  http_response_code(405); exit;
}
$in = json_decode(file_get_contents('php://input'), true);

$name    = trim($in['name']    ?? '');
$email   = trim($in['email']   ?? '');
$service = trim($in['service'] ?? '');
if (!$name || !$email || !$service) {
  http_response_code(400);
  echo json_encode(['success'=>false,'msg'=>'Required fields missing']); exit;
}

try {
  $pdo = new PDO(
    'mysql:host=localhost;dbname=hpsystem_db;charset=utf8mb4',
    'hpsystem_user', 'YOUR_DB_PASSWORD',
    [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]
  );
  $st = $pdo->prepare(
    'INSERT INTO inquiries
     (contact_name,company,email,phone,country,service_type,message,lang,ip_address)
     VALUES (?,?,?,?,?,?,?,?,?)'
  );
  $st->execute([
    $name, trim($in['company']??''),(string)$email,
    trim($in['phone']??''),(string)($in['country']??''),(string)$service,
    trim($in['message']??''),(string)($in['lang']??'ko'),
    $_SERVER['REMOTE_ADDR']??null
  ]);
  echo json_encode(['success'=>true,'id'=>$pdo->lastInsertId()]);
} catch(PDOException $e){
  http_response_code(500);
  echo json_encode(['success'=>false,'msg'=>'DB error']);
  error_log($e->getMessage());
}
?>

// server.js  (npm install express mysql2 cors dotenv)
require('dotenv').config();
const express = require('express');
const mysql   = require('mysql2/promise');
const cors    = require('cors');
const app     = express();
app.use(cors({ origin: 'https://hpsystem.net' }));
app.use(express.json());

const pool = mysql.createPool({
  host: process.env.DB_HOST||'localhost',
  user: process.env.DB_USER||'hpsystem_user',
  password: process.env.DB_PASS||'YOUR_PASSWORD',
  database: process.env.DB_NAME||'hpsystem_db',
  connectionLimit: 10, charset: 'utf8mb4'
});

// POST /api/inquiry  -- 폼 저장
app.post('/api/inquiry', async (req, res) => {
  const {name,company,email,phone,country,service,message,lang} = req.body;
  if (!name||!email||!service)
    return res.status(400).json({success:false,msg:'Required fields'});
  try {
    const [r] = await pool.execute(
      `INSERT INTO inquiries
       (contact_name,company,email,phone,country,service_type,message,lang,ip_address)
       VALUES (?,?,?,?,?,?,?,?,?)`,
      [name,company||null,email,phone||null,country||null,
       service,message||null,lang||'ko',req.ip||null]
    );
    res.json({success:true, id:r.insertId});
  } catch(e){
    console.error(e);
    res.status(500).json({success:false,msg:'DB error'});
  }
});
app.listen(3000, ()=>console.log('HP System API :3000'));

<?php
// admin/index.php  -- 관리자 접수 목록 & 상태 관리
// 접근 보호 (Basic Auth)
if(!isset($_SERVER['PHP_AUTH_USER'])||
   $_SERVER['PHP_AUTH_USER']!=='admin'||
   $_SERVER['PHP_AUTH_PW']!=='ADMIN_PW_HERE'){
  header('WWW-Authenticate: Basic realm="HP System Admin"');
  header('HTTP/1.0 401 Unauthorized'); exit;
}

$pdo = new PDO('mysql:host=localhost;dbname=hpsystem_db;charset=utf8mb4',
  'hpsystem_user','DB_PW_HERE',[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);

// 상태 변경 처리
if($_SERVER['REQUEST_METHOD']='POST'&&isset($_POST['id',$_POST['status']])){
  $pdo->prepare('UPDATE inquiries SET status=? WHERE id=?')
      ->execute([$_POST['status'],$_POST['id']]);
}

// CSV 내보내기
if(isset($_GET['export'])){
  $rows = $pdo->query('SELECT * FROM inquiries ORDER BY created_at DESC')->fetchAll(PDO::FETCH_ASSOC);
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="inquiries_'.date('Ymd').'.csv"');
  echo "";
  $out = fopen('php://output','w');
  fputcsv($out,['ID','이름','회사','이메일','연락처','국가','서비스','언어','내용','상태','접수일']);
  foreach($rows as $r) fputcsv($out,array_values($r));
  fclose($out); exit;
}

// 필터 조건
$w=[];$p=[];
$st=$_GET['st']??'';;$lg=$_GET['lg']??'';;$q=$_GET['q']??'';;
if($st){$w[]='status=?';$p[]=$st;}
if($lg){$w[]='lang=?';$p[]=$lg;}
if($q){$w[]= '(contact_name LIKE ? OR email LIKE ? OR company LIKE ?)';
       $p=array_merge($p,["%$q%","%$q%","%$q%"]);}
$sql='SELECT * FROM inquiries'.($w?' WHERE '.implode(' AND ',$w):''). ' ORDER BY created_at DESC';
$rows=$pdo->prepare($sql); $rows->execute($p); $rows=$rows->fetchAll(PDO::FETCH_ASSOC);

$SL=['new'=>'🆕 신규','contacted'=>'📞 연락완료',
     'in_progress'=>'🔧 진행중','completed'=>'✅ 완료','closed'=>'🔒 종료'];
$SC=['new'=>'#00C2A8','contacted'=>'#F5A623',
     'in_progress'=>'#4A90E2','completed'=>'#27AE60','closed'=>'#8A9BB5'];
$stats=$pdo->query('SELECT status,COUNT(*) c FROM inquiries GROUP BY status')->fetchAll(PDO::FETCH_KEY_PAIR);
?>
<!DOCTYPE html><html lang="ko"><head>
<meta charset="UTF-8"><title>HP System 관리자</title>
<style>
body{font-family:'Segoe UI',sans-serif;background:#0B1E3D;color:#F8FBFF;margin:0;padding:20px}
h1{color:#00C2A8;margin-bottom:16px} .stats{display:flex;gap:12px;flex-wrap:wrap;margin-bottom:18px}
.sb{background:#112244;border:1px solid rgba(0,194,168,.2);border-radius:10px;padding:12px 18px;text-align:center}
.sb .n{font-size:22px;font-weight:700} .sb .l{font-size:11px;color:#8A9BB5;margin-top:3px}
.filters{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:14px}
.filters input,.filters select{background:#112244;border:1px solid rgba(0,194,168,.3);
  color:#F8FBFF;padding:7px 12px;border-radius:8px;font-size:13px}
.filters button,.btn{background:#00C2A8;color:#0B1E3D;border:none;padding:7px 16px;
  border-radius:8px;cursor:pointer;font-weight:600;font-size:13px;text-decoration:none}
.btn-o{background:transparent;border:1px solid #00C2A8;color:#00C2A8}
table{width:100%;border-collapse:collapse;font-size:13px}
th{background:#132952;padding:10px;text-align:left;color:#8A9BB5;border-bottom:1px solid rgba(0,194,168,.15)}
td{padding:9px 10px;border-bottom:1px solid rgba(0,194,168,.07);vertical-align:top}
tr:hover td{background:rgba(0,194,168,.04)}
.msg{max-width:160px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;color:#8A9BB5}
select.ss{background:#0B1E3D;border:1px solid rgba(0,194,168,.3);color:#F8FBFF;
  padding:3px 7px;border-radius:6px;font-size:12px;cursor:pointer}
</style></head><body>
<h1>📋 HP System — 업무 접수 관리</h1>
<div class="stats">
  <div class="sb"><div class="n" style="color:#00C2A8"><?=array_sum($stats)?></div><div class="l">전체</div></div>
  <?php foreach($SL as $k=>$v): ?>
  <div class="sb"><div class="n" style="color:<?=$SC[$k]?>"><?=$stats[$k]??0?></div><div class="l"><?=$v?></div></div>
  <?php endforeach; ?>
</div>
<form class="filters" method="GET">
  <input name="q" placeholder="이름/이메일/회사 검색" value="<?=htmlspecialchars($q)?>">
  <select name="st"><option value="">전체 상태</option>
    <?php foreach($SL as $k=>$v) echo "<option value='$k'".($st==$k?' selected':''). ">$v</option>"; ?>
  </select>
  <select name="lg"><option value="">전체 언어</option>
    <option value="ko"<?=$lg=='ko'?' selected':''?>>🇰🇷 KO</option>
    <option value="en"<?=$lg=='en'?' selected':''?>>🇺🇸 EN</option>
    <option value="vi"<?=$lg=='vi'?' selected':''?>>🇻🇳 VI</option>
  </select>
  <button type="submit">🔍 검색</button>
  <a href="?export=1" class="btn btn-o">⬇ CSV</a>
</form>
<table><thead><tr>
  <th>#</th><th>이름</th><th>회사</th><th>이메일</th><th>연락처</th>
  <th>국가</th><th>서비스</th><th>언어</th><th>내용</th><th>상태</th><th>접수일</th>
</tr></thead><tbody>
<?php foreach($rows as $r): ?>
<tr>
  <td><?=$r['id']?></td>
  <td><strong><?=htmlspecialchars($r['contact_name'])?></strong></td>
  <td><?=htmlspecialchars($r['company']??'')?></td>
  <td><a href="mailto:<?=$r['email']?>" style="color:#00C2A8"><?=htmlspecialchars($r['email'])?></a></td>
  <td><?=htmlspecialchars($r['phone']??'')?></td>
  <td><?=$r['country']??''?></td>
  <td><?=$r['service_type']?></td>
  <td><?=$r['lang']=='ko'?'🇰🇷':($r['lang']=='en'?'🇺🇸':'🇻🇳')?></td>
  <td><div class="msg" title="<?=htmlspecialchars($r['message']??'')?>"><?=htmlspecialchars($r['message']??'')?></div></td>
  <td>
    <form method="POST" style="margin:0">
      <input type="hidden" name="id" value="<?=$r['id']?>">
      <select name="status" class="ss" onchange="this.form.submit()">
        <?php foreach($SL as $k=>$v) echo "<option value='$k'".($r['status']==$k?' selected':''). ">$v</option>"; ?>
      </select>
    </form>
  </td>
  <td><?=date('Y-m-d H:i',strtotime($r['created_at']))?></td>
</tr>
<?php endforeach; ?>
</tbody></table>
</body></html>

// admin 라우터  (npm install express-basic-auth)
// server.js 의 pool, app 선언 이후에 추가

const basicAuth = require('express-basic-auth');
const adminAuth = basicAuth({
  users:{ [process.env.ADMIN_USER||'admin']: process.env.ADMIN_PASS||'hpsystem2025' },
  challenge: true, realm: 'HP System Admin'
});

// GET /admin  -- 관리자 대시보드 HTML
app.get('/admin', adminAuth, async (req, res) => {
  const {st,lg,q} = req.query;
  let sql = 'SELECT * FROM inquiries WHERE 1=1'; const p = [];
  if(st){sql+=' AND status=?';p.push(st);}
  if(lg){sql+=' AND lang=?';p.push(lg);}
  if(q){sql+=' AND (contact_name LIKE ? OR email LIKE ? OR company LIKE ?)';
        p.push(`%${q}%`,`%${q}%`,`%${q}%`);}
  sql += ' ORDER BY created_at DESC';
  const [rows]  = await pool.execute(sql, p);
  const [stats] = await pool.execute(
    'SELECT status, COUNT(*) c FROM inquiries GROUP BY status'
  );
  const SL={new:'🆕 신규',contacted:'📞 연락완료',
             in_progress:'🔧 진행중',completed:'✅ 완료',closed:'🔒 종료'};
  const SC={new:'#00C2A8',contacted:'#F5A623',
             in_progress:'#4A90E2',completed:'#27AE60',closed:'#8A9BB5'};

  const statMap = Object.fromEntries(stats.map(s=>[s.status,s.c]));
  const total   = stats.reduce((a,s)=>a+Number(s.c),0);

  const statHtml = Object.entries(SL).map(([k,v])=>
    `<div class="sb"><div class="n" style="color:${SC[k]}">${statMap[k]||0}</div>
     <div class="l">${v}</div></div>`).join('');

  const rowHtml = rows.map(r=>{
    const opts = Object.entries(SL).map(([k,v])=>
      `<option value="${k}"${r.status===k?' selected':''}>${v}</option>`).join('');
    const flag = r.lang==='ko'?'🇰🇷':r.lang==='en'?'🇺🇸':'🇻🇳';
    const msg  = (r.message||''). replace(/</g,'&lt;').replace(/>/g,'&gt;').slice(0,60);
    return `<tr>
      <td>${r.id}</td>
      <td><strong>${r.contact_name}</strong></td>
      <td>${r.company||''}</td>
      <td><a href="mailto:${r.email}" style="color:#00C2A8">${r.email}</a></td>
      <td>${r.phone||''}</td>
      <td>${r.country||''}</td>
      <td>${r.service_type}</td>
      <td>${flag}</td>
      <td style="max-width:150px;overflow:hidden;text-overflow:ellipsis;
                 white-space:nowrap;color:#8A9BB5" title="${r.message||''}">${msg}</td>
      <td><select onchange="chgStatus(${r.id},this.value)"
        style="background:#0B1E3D;border:1px solid rgba(0,194,168,.3);
               color:#F8FBFF;padding:3px 7px;border-radius:6px;font-size:12px">
        ${opts}</select></td>
      <td>${new Date(r.created_at).toLocaleString('ko-KR')}</td>
    </tr>`;
  }).join('');

  res.send(`<!DOCTYPE html><html lang="ko"><head>
  <meta charset="UTF-8"><title>HP System 관리자</title>
  <style>
    body{font-family:'Segoe UI',sans-serif;background:#0B1E3D;color:#F8FBFF;margin:0;padding:20px}
    h1{color:#00C2A8} .stats{display:flex;gap:12px;flex-wrap:wrap;margin:14px 0}
    .sb{background:#112244;border:1px solid rgba(0,194,168,.2);border-radius:10px;padding:12px 18px;text-align:center}
    .sb .n{font-size:22px;font-weight:700} .sb .l{font-size:11px;color:#8A9BB5;margin-top:3px}
    .f{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:14px}
    .f input,.f select{background:#112244;border:1px solid rgba(0,194,168,.3);
      color:#F8FBFF;padding:7px 12px;border-radius:8px;font-size:13px}
    .f button,.btn{background:#00C2A8;color:#0B1E3D;border:none;padding:7px 16px;
      border-radius:8px;cursor:pointer;font-weight:600;font-size:13px;text-decoration:none;display:inline-block}
    .btn-o{background:transparent;border:1px solid #00C2A8;color:#00C2A8}
    table{width:100%;border-collapse:collapse;font-size:13px}
    th{background:#132952;padding:10px;text-align:left;color:#8A9BB5;border-bottom:1px solid rgba(0,194,168,.15)}
    td{padding:9px 10px;border-bottom:1px solid rgba(0,194,168,.07);vertical-align:top}
    tr:hover td{background:rgba(0,194,168,.04)}
  </style></head><body>
  <h1>📋 HP System 업무 접수 관리</h1>
  <div class="stats">
    <div class="sb"><div class="n" style="color:#00C2A8">${total}</div><div class="l">전체</div></div>
    ${statHtml}
  </div>
  <form class="f" method="GET">
    <input name="q" placeholder="이름/이메일/회사" value="${q||''}">
    <select name="st"><option value="">전체 상태</option>
      ${Object.entries(SL).map(([k,v])=>`<option value="${k}"${st===k?' selected':''}>${v}</option>`).join('')}</select>
    <select name="lg"><option value="">전체 언어</option>
      <option value="ko"${lg==='ko'?' selected':''}>🇰🇷 KO</option>
      <option value="en"${lg==='en'?' selected':''}>🇺🇸 EN</option>
      <option value="vi"${lg==='vi'?' selected':''}>🇻🇳 VI</option>
    </select>
    <button type="submit">🔍 검색</button>
    <a href="/admin/export" class="btn btn-o">⬇ CSV</a>
  </form>
  <table><thead><tr>
    <th>#</th><th>이름</th><th>회사</th><th>이메일</th><th>연락처</th>
    <th>국가</th><th>서비스</th><th>언어</th><th>내용</th><th>상태</th><th>접수일</th>
  </tr></thead><tbody>${rowHtml}</tbody></table>
  <script>
    async function chgStatus(id,status){
      await fetch('/admin/status',{method:'POST',
        headers:{'Content-Type':'application/json'},
        body:JSON.stringify({id,status})});
    }
  </script></body></html>`);
});

// POST /admin/status  -- 상태 변경
app.post('/admin/status', adminAuth, async (req,res)=> {
  const {id,status} = req.body;
  await pool.execute('UPDATE inquiries SET status=? WHERE id=?',[status,id]);
  res.json({success:true});
});

// GET /admin/export  -- CSV 다운로드
app.get('/admin/export', adminAuth, async (req,res)=> {
  const [rows] = await pool.execute('SELECT * FROM inquiries ORDER BY created_at DESC');
  const header = 'ID,이름,회사,이메일,연락처,국가,서비스,언어,내용,상태,접수일\n';
  const csv = rows.map(r=>
    [r.id,r.contact_name,r.company,r.email,r.phone,r.country,
     r.service_type,r.lang,(r.message||''). replace(/\n/g,' '),r.status,r.created_at]
    .map(v=>`"${String(v||''). replace(/"/g,'""')}"`).join(',')
  ).join('\n');
  res.setHeader('Content-Type','text/csv; charset=utf-8');
  res.setHeader('Content-Disposition',`attachment; filename="inquiries_${Date.now()}.csv"`);
  res.send('\uFEFF'+header+csv);
});