<?php
|
/**
|
* WPCode 片段 1/4 — 核心数据层
|
* 名称建议：IM - 核心数据层
|
* 类型：PHP Snippet
|
* 位置：Run everywhere
|
*
|
* 包含：全局常量 · 自动建表 · IM_Candidate · IM_Token · IM_Mailer
|
*/
|
|
defined('ABSPATH') || exit;
|
|
/* ============================================================
|
!! 配置区 —— 根据实际情况修改以下常量 !!
|
============================================================ */
|
|
// 开启调试模式：在列表页后台显示所有相关申请表、面试链接
|
if (!defined('IM_DEBUG_LINKS'))
|
define('IM_DEBUG_LINKS', true);
|
|
// 视频最大尺寸（字节），默认 500 MB
|
if (!defined('IM_MAX_VIDEO_SIZE'))
|
define('IM_MAX_VIDEO_SIZE', 500 * 1024 * 1024);
|
|
// 详细申请表单页面 URL（用于 Join Us 确认邮件中的链接）
|
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_apply_form] shortcode
|
if (!defined('IM_APPLY_PAGE_URL'))
|
define('IM_APPLY_PAGE_URL', home_url('/apply/'));
|
|
// 面试页面 URL（用于面试邀请邮件中的链接）
|
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_interview] shortcode
|
if (!defined('IM_INTERVIEW_PAGE_URL'))
|
define('IM_INTERVIEW_PAGE_URL', home_url('/interview/'));
|
|
// 培训页面 URL（用于录取邮件中的培训链接）
|
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_training] shortcode
|
if (!defined('IM_TRAINING_PAGE_URL'))
|
define('IM_TRAINING_PAGE_URL', home_url('/training/'));
|
|
// 培训完成后发送的临时账号信息（用于培训完成邮件）
|
// 请将此处改为实际的临时账号信息
|
if (!defined('IM_TRAINING_ACCOUNT'))
|
define('IM_TRAINING_ACCOUNT', 'Teaching@YStarEdu.onmicrosoft.com');
|
if (!defined('IM_TRAINING_PASSWORD'))
|
define('IM_TRAINING_PASSWORD', 'YStarEdu1021');
|
|
/* ============================================================
|
自动建表（用 wp_options 版本号做幂等控制）
|
============================================================ */
|
function im_maybe_create_tables()
|
{
|
$current_version = get_option('im_db_version');
|
if ($current_version === '1.7')
|
return;
|
|
global $wpdb;
|
$charset = $wpdb->get_charset_collate();
|
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
|
|
// 候选人主表
|
dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_candidates (
|
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
first_name VARCHAR(100) NOT NULL DEFAULT '',
|
last_name VARCHAR(100) NOT NULL DEFAULT '',
|
preferred_name VARCHAR(100) NOT NULL DEFAULT '',
|
email VARCHAR(200) NOT NULL DEFAULT '',
|
phone VARCHAR(50) NOT NULL DEFAULT '',
|
location VARCHAR(200) NOT NULL DEFAULT '',
|
degree_level VARCHAR(50) NOT NULL DEFAULT '',
|
university VARCHAR(200) NOT NULL DEFAULT '',
|
major VARCHAR(200) NOT NULL DEFAULT '',
|
grad_year VARCHAR(10) NOT NULL DEFAULT '',
|
gpa VARCHAR(20) NOT NULL DEFAULT '',
|
deans_list TINYINT(1) NOT NULL DEFAULT 0,
|
ug_university VARCHAR(200) NOT NULL DEFAULT '',
|
ug_major VARCHAR(200) NOT NULL DEFAULT '',
|
ug_grad_year VARCHAR(10) NOT NULL DEFAULT '',
|
ms_university VARCHAR(200) NOT NULL DEFAULT '',
|
ms_major VARCHAR(200) NOT NULL DEFAULT '',
|
ms_grad_year VARCHAR(10) NOT NULL DEFAULT '',
|
languages TEXT,
|
teaching_exp TEXT,
|
has_achievement TINYINT(1) NOT NULL DEFAULT 0,
|
achievement_type VARCHAR(200) NOT NULL DEFAULT '',
|
achievement_desc TEXT,
|
subjects TEXT,
|
extra_notes TEXT,
|
status VARCHAR(20) NOT NULL DEFAULT 'applied',
|
reject_reason TEXT,
|
apply_token VARCHAR(64) NOT NULL DEFAULT '' COMMENT '详细表单专属 token，防止他人访问',
|
apply_token_used TINYINT(1) NOT NULL DEFAULT 0,
|
apply_opened_at DATETIME DEFAULT NULL,
|
ca_highschool TINYINT(1) NOT NULL DEFAULT 0,
|
ca_highschool_name VARCHAR(200) NOT NULL DEFAULT '',
|
country VARCHAR(100) NOT NULL DEFAULT '',
|
city VARCHAR(200) NOT NULL DEFAULT '',
|
training_token VARCHAR(64) NOT NULL DEFAULT '',
|
training_opened_at DATETIME DEFAULT NULL,
|
training_completed_at DATETIME DEFAULT NULL,
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY email (email),
|
KEY status (status),
|
KEY apply_token (apply_token),
|
KEY training_token (training_token)
|
) $charset;");
|
|
// 面试 Token 表
|
dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_interview_tokens (
|
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
candidate_id BIGINT(20) UNSIGNED NOT NULL,
|
token VARCHAR(64) NOT NULL DEFAULT '',
|
expires_at DATETIME NOT NULL,
|
is_used TINYINT(1) NOT NULL DEFAULT 0,
|
video_path TEXT,
|
video_filename VARCHAR(255) NOT NULL DEFAULT '',
|
sent_at DATETIME DEFAULT NULL,
|
opened_at DATETIME DEFAULT NULL,
|
submitted_at DATETIME DEFAULT NULL,
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY token (token),
|
KEY candidate_id (candidate_id)
|
) $charset;");
|
|
// 附件上传记录表
|
dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_attachments (
|
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
candidate_id BIGINT(20) UNSIGNED NOT NULL,
|
file_path TEXT NOT NULL,
|
file_name VARCHAR(255) NOT NULL DEFAULT '',
|
file_type VARCHAR(50) NOT NULL DEFAULT '',
|
uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
KEY candidate_id (candidate_id)
|
) $charset;");
|
|
if ($current_version === '1.1') {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'applied'");
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD COLUMN IF NOT EXISTS reject_reason TEXT");
|
$wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='applied' WHERE status='pending'");
|
$wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='screening' WHERE status='submitted' AND apply_token_used=1");
|
$wpdb->query("UPDATE {$wpdb->prefix}im_candidates c JOIN {$wpdb->prefix}im_interview_tokens t ON c.id=t.candidate_id AND t.is_used=1 SET c.status='completed' ");
|
}
|
if (version_compare($current_version, '1.4', '<')) {
|
// Bulletproof method to add columns for older MySQL versions that fail on "ADD COLUMN IF NOT EXISTS"
|
$cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
|
if (!in_array('apply_opened_at', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD apply_opened_at DATETIME DEFAULT NULL");
|
}
|
if (!in_array('country', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD country VARCHAR(100) NOT NULL DEFAULT ''");
|
}
|
if (!in_array('city', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD city VARCHAR(200) NOT NULL DEFAULT ''");
|
}
|
|
$tok_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_interview_tokens");
|
if (!in_array('opened_at', $tok_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_interview_tokens ADD opened_at DATETIME DEFAULT NULL");
|
}
|
|
update_option('im_db_version', '1.4');
|
}
|
if (version_compare(get_option('im_db_version'), '1.5', '<')) {
|
$cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
|
if (!in_array('ca_highschool', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ca_highschool TINYINT(1) NOT NULL DEFAULT 0");
|
}
|
if (!in_array('ca_highschool_name', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ca_highschool_name VARCHAR(200) NOT NULL DEFAULT ''");
|
}
|
update_option('im_db_version', '1.5');
|
}
|
if (version_compare(get_option('im_db_version'), '1.6', '<')) {
|
$cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
|
if (!in_array('ms_university', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_university VARCHAR(200) NOT NULL DEFAULT ''");
|
}
|
if (!in_array('ms_major', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_major VARCHAR(200) NOT NULL DEFAULT ''");
|
}
|
if (!in_array('ms_grad_year', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_grad_year VARCHAR(10) NOT NULL DEFAULT ''");
|
}
|
update_option('im_db_version', '1.6');
|
}
|
if (version_compare(get_option('im_db_version'), '1.7', '<')) {
|
$cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
|
if (!in_array('training_token', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_token VARCHAR(64) NOT NULL DEFAULT ''");
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD INDEX training_token (training_token)");
|
}
|
if (!in_array('training_opened_at', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_opened_at DATETIME DEFAULT NULL");
|
}
|
if (!in_array('training_completed_at', $cand_cols)) {
|
$wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_completed_at DATETIME DEFAULT NULL");
|
}
|
// Migrate existing 'hired' candidates to 'training' status
|
$wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='training' WHERE status='hired'");
|
update_option('im_db_version', '1.7');
|
}
|
}
|
add_action('init', 'im_maybe_create_tables', 1);
|
|
/* ============================================================
|
IM_Candidate
|
============================================================ */
|
if (!class_exists('IM_Candidate')) {
|
class IM_Candidate
|
{
|
|
public static function table()
|
{
|
global $wpdb;
|
return $wpdb->prefix . 'im_candidates';
|
}
|
|
/** 第一步：仅凭 Join Us 简表单创建候选人（只有姓名、邮箱） */
|
public static function create_from_joinus(string $first_name, string $last_name, string $email)
|
{
|
global $wpdb;
|
$email = sanitize_email($email);
|
$existing = self::get_by_email($email);
|
if ($existing)
|
return (int) $existing->id;
|
|
$apply_token = bin2hex(random_bytes(24)); // 48 位申请表 token
|
$wpdb->insert(self::table(), [
|
'first_name' => sanitize_text_field($first_name),
|
'last_name' => sanitize_text_field($last_name),
|
'email' => $email,
|
'apply_token' => $apply_token,
|
'status' => 'applied',
|
]);
|
return $wpdb->insert_id ?: false;
|
}
|
|
/** 第二步：详细表单提交后，更新候选人全部信息 */
|
public static function update_full(int $id, array $data)
|
{
|
global $wpdb;
|
return $wpdb->update(self::table(), [
|
'preferred_name' => sanitize_text_field($data['preferred_name'] ?? ''),
|
'phone' => sanitize_text_field($data['phone'] ?? ''),
|
'country' => sanitize_text_field($data['country'] ?? ''),
|
'city' => sanitize_text_field($data['city'] ?? ''),
|
'degree_level' => sanitize_text_field($data['degree_level'] ?? ''),
|
'university' => sanitize_text_field($data['university'] ?? ''),
|
'major' => sanitize_text_field($data['major'] ?? ''),
|
'grad_year' => sanitize_text_field($data['grad_year'] ?? ''),
|
'gpa' => sanitize_text_field($data['gpa'] ?? ''),
|
'deans_list' => (int) ($data['deans_list'] ?? 0),
|
'ug_university' => sanitize_text_field($data['ug_university'] ?? ''),
|
'ug_major' => sanitize_text_field($data['ug_major'] ?? ''),
|
'ug_grad_year' => sanitize_text_field($data['ug_grad_year'] ?? ''),
|
'ms_university' => sanitize_text_field($data['ms_university'] ?? ''),
|
'ms_major' => sanitize_text_field($data['ms_major'] ?? ''),
|
'ms_grad_year' => sanitize_text_field($data['ms_grad_year'] ?? ''),
|
'ca_highschool' => (int) ($data['ca_highschool'] ?? 0),
|
'ca_highschool_name' => sanitize_text_field($data['ca_highschool_name'] ?? ''),
|
'languages' => sanitize_textarea_field($data['languages'] ?? ''),
|
'teaching_exp' => sanitize_textarea_field($data['teaching_exp'] ?? ''),
|
'has_achievement' => (int) ($data['has_achievement'] ?? 0),
|
'achievement_type' => sanitize_text_field($data['achievement_type'] ?? ''),
|
'achievement_desc' => sanitize_textarea_field($data['achievement_desc'] ?? ''),
|
'subjects' => is_array($data['subjects'] ?? null)
|
? json_encode(array_values($data['subjects']), JSON_UNESCAPED_UNICODE)
|
: '[]',
|
'extra_notes' => sanitize_textarea_field($data['extra_notes'] ?? ''),
|
'status' => 'screening',
|
'apply_token_used' => 1,
|
], ['id' => $id]);
|
}
|
|
public static function get(int $id)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE id=%d', $id));
|
}
|
|
public static function mark_apply_opened(int $id)
|
{
|
global $wpdb;
|
return $wpdb->query($wpdb->prepare('UPDATE ' . self::table() . ' SET apply_opened_at = CURRENT_TIMESTAMP WHERE id = %d AND apply_opened_at IS NULL', $id));
|
}
|
|
public static function get_by_email(string $email)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE email=%s',
|
sanitize_email($email)
|
));
|
}
|
|
public static function get_by_apply_token(string $token)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE apply_token=%s AND apply_token_used=0',
|
$token
|
));
|
}
|
|
public static function get_by_apply_token_any(string $token)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE apply_token=%s',
|
$token
|
));
|
}
|
|
public static function get_list(array $args = [])
|
{
|
global $wpdb;
|
$limit = (int) ($args['per_page'] ?? 20);
|
$offset = ((int) ($args['page'] ?? 1) - 1) * $limit;
|
[$where, $params] = self::_where($args);
|
$sql = 'SELECT * FROM ' . self::table() . " WHERE $where ORDER BY created_at DESC LIMIT %d OFFSET %d";
|
return $wpdb->get_results($wpdb->prepare($sql, ...array_merge($params, [$limit, $offset])));
|
}
|
|
public static function count(array $args = [])
|
{
|
global $wpdb;
|
[$where, $params] = self::_where($args);
|
$sql = 'SELECT COUNT(*) FROM ' . self::table() . " WHERE $where";
|
return (int) ($params
|
? $wpdb->get_var($wpdb->prepare($sql, ...$params))
|
: $wpdb->get_var($sql));
|
}
|
|
public static function update_status(int $id, string $status)
|
{
|
global $wpdb;
|
return $wpdb->update(self::table(), ['status' => $status], ['id' => $id]);
|
}
|
|
public static function reject(int $id, string $reason)
|
{
|
global $wpdb;
|
return $wpdb->update(self::table(), ['status' => 'rejected', 'reject_reason' => sanitize_textarea_field($reason)], ['id' => $id]);
|
}
|
|
/** 删除候选人及其所有关联数据和文件 */
|
public static function delete_with_files(int $id): bool
|
{
|
global $wpdb;
|
$c = self::get($id);
|
if (!$c)
|
return false;
|
|
$upload_dir = wp_upload_dir();
|
|
// 删除申请附件目录
|
$app_dir = $upload_dir['basedir'] . '/im-applications/' . $id;
|
if (is_dir($app_dir)) {
|
self::_rmdir($app_dir);
|
}
|
|
// 删除面试视频目录
|
$iv_dir = $upload_dir['basedir'] . '/interviews/' . $id;
|
if (is_dir($iv_dir)) {
|
self::_rmdir($iv_dir);
|
}
|
|
// 删除数据库记录
|
$wpdb->delete(IM_Attachment::table(), ['candidate_id' => $id]);
|
$wpdb->delete(IM_Token::table(), ['candidate_id' => $id]);
|
$wpdb->delete(self::table(), ['id' => $id]);
|
|
return true;
|
}
|
|
private static function _rmdir(string $dir): void
|
{
|
$files = new \RecursiveIteratorIterator(
|
new \RecursiveDirectoryIterator($dir, \FilesystemIterator::SKIP_DOTS),
|
\RecursiveIteratorIterator::CHILD_FIRST
|
);
|
foreach ($files as $f) {
|
$f->isDir() ? rmdir($f->getRealPath()) : unlink($f->getRealPath());
|
}
|
rmdir($dir);
|
}
|
|
public static function get_by_training_token(string $token)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE training_token=%s AND status=%s',
|
$token,
|
'training'
|
));
|
}
|
|
public static function get_by_training_token_any(string $token)
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE training_token=%s',
|
$token
|
));
|
}
|
|
public static function generate_training_token(int $id): string|false
|
{
|
global $wpdb;
|
$c = self::get($id);
|
if (!$c)
|
return false;
|
if ($c->training_token) {
|
return add_query_arg('im_training_token', $c->training_token, IM_TRAINING_PAGE_URL);
|
}
|
$token = bin2hex(random_bytes(24));
|
$ok = $wpdb->update(self::table(), [
|
'training_token' => $token,
|
'status' => 'training',
|
], ['id' => $id]);
|
if ($ok === false)
|
return false;
|
return add_query_arg('im_training_token', $token, IM_TRAINING_PAGE_URL);
|
}
|
|
public static function mark_training_opened(int $id)
|
{
|
global $wpdb;
|
return $wpdb->query($wpdb->prepare(
|
'UPDATE ' . self::table() . ' SET training_opened_at = CURRENT_TIMESTAMP WHERE id = %d AND training_opened_at IS NULL',
|
$id
|
));
|
}
|
|
public static function mark_training_completed(int $id)
|
{
|
global $wpdb;
|
return $wpdb->update(self::table(), [
|
'status' => 'trained',
|
'training_completed_at' => current_time('mysql'),
|
], ['id' => $id]);
|
}
|
|
public static function get_subjects($candidate): array
|
{
|
if (is_numeric($candidate))
|
$candidate = self::get((int) $candidate);
|
if (!$candidate)
|
return [];
|
$s = json_decode($candidate->subjects ?? '[]', true);
|
if (!is_array($s))
|
return [];
|
$titles = [];
|
foreach ($s as $id) {
|
if (is_numeric($id)) {
|
$post = get_post((int) $id);
|
$titles[] = $post ? $post->post_title : (string) $id;
|
} else {
|
$titles[] = (string) $id;
|
}
|
}
|
return $titles;
|
}
|
|
private static function _where(array $args): array
|
{
|
global $wpdb;
|
$where = ['1=1'];
|
$params = [];
|
if (!empty($args['status']) && in_array($args['status'], ['applied', 'screening', 'invited', 'completed', 'rejected', 'hired', 'training', 'trained'], true)) {
|
$where[] = 'status=%s';
|
$params[] = $args['status'];
|
}
|
if (!empty($args['search'])) {
|
$like = '%' . $wpdb->esc_like($args['search']) . '%';
|
$where[] = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s)';
|
array_push($params, $like, $like, $like);
|
}
|
return [implode(' AND ', $where), $params];
|
}
|
}
|
} // end IM_Candidate
|
|
/* ============================================================
|
IM_Attachment
|
============================================================ */
|
if (!class_exists('IM_Attachment')) {
|
class IM_Attachment
|
{
|
|
public static function table()
|
{
|
global $wpdb;
|
return $wpdb->prefix . 'im_attachments';
|
}
|
|
public static function add(int $candidate_id, string $path, string $name, string $type)
|
{
|
global $wpdb;
|
return $wpdb->insert(self::table(), [
|
'candidate_id' => $candidate_id,
|
'file_path' => $path,
|
'file_name' => $name,
|
'file_type' => $type,
|
]);
|
}
|
|
public static function get_by_candidate(int $candidate_id)
|
{
|
global $wpdb;
|
return $wpdb->get_results($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY uploaded_at ASC',
|
$candidate_id
|
));
|
}
|
}
|
} // end IM_Attachment
|
|
/* ============================================================
|
IM_Token
|
============================================================ */
|
if (!class_exists('IM_Token')) {
|
class IM_Token
|
{
|
|
public static function table()
|
{
|
global $wpdb;
|
return $wpdb->prefix . 'im_interview_tokens';
|
}
|
|
public static function generate(int $candidate_id): string|false
|
{
|
global $wpdb;
|
$expires_at = date('Y-m-d H:i:s', current_time('timestamp') + 86400 * 3);
|
$sent_at = current_time('mysql');
|
|
// Check if token already exists for candidate
|
$tk = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY id DESC LIMIT 1', $candidate_id));
|
|
if ($tk) {
|
$token = $tk->token;
|
// Update existing token to reset state and extend validity
|
$ok = $wpdb->update(self::table(), [
|
'expires_at' => $expires_at,
|
'sent_at' => $sent_at,
|
'is_used' => 0,
|
'opened_at' => null,
|
'video_path' => null,
|
'video_filename' => null,
|
'submitted_at' => null
|
], ['id' => $tk->id]);
|
if ($ok === false)
|
return false;
|
} else {
|
$token = bin2hex(random_bytes(32));
|
$ok = $wpdb->insert(self::table(), [
|
'candidate_id' => $candidate_id,
|
'token' => $token,
|
'expires_at' => $expires_at,
|
'is_used' => 0,
|
'sent_at' => $sent_at,
|
]);
|
if (!$ok)
|
return false;
|
}
|
|
IM_Candidate::update_status($candidate_id, 'invited');
|
return add_query_arg('im_token', $token, IM_INTERVIEW_PAGE_URL);
|
}
|
|
public static function validate(string $token): ?object
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE token=%s AND is_used=0 AND expires_at>%s',
|
$token,
|
current_time('mysql')
|
)) ?: null;
|
}
|
|
public static function mark_opened(int $id): bool
|
{
|
global $wpdb;
|
$now = current_time('mysql');
|
$expires = date('Y-m-d H:i:s', current_time('timestamp') + 86400); // 24 hours
|
return (bool) $wpdb->query($wpdb->prepare(
|
'UPDATE ' . self::table() . ' SET opened_at = %s, expires_at = %s WHERE id = %d AND opened_at IS NULL',
|
$now,
|
$expires,
|
$id
|
));
|
}
|
|
public static function get_by_token(string $token): ?object
|
{
|
global $wpdb;
|
return $wpdb->get_row($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE token=%s',
|
$token
|
)) ?: null;
|
}
|
|
public static function get_by_candidate(int $candidate_id): array
|
{
|
global $wpdb;
|
return $wpdb->get_results($wpdb->prepare(
|
'SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY created_at DESC',
|
$candidate_id
|
)) ?: [];
|
}
|
|
public static function mark_used(int $id, string $path, string $filename): bool
|
{
|
global $wpdb;
|
return (bool) $wpdb->update(self::table(), [
|
'is_used' => 1,
|
'video_path' => $path,
|
'video_filename' => $filename,
|
'submitted_at' => current_time('mysql'),
|
], ['id' => $id]);
|
}
|
}
|
} // end IM_Token
|
|
/* ============================================================
|
IM_Mailer
|
============================================================ */
|
if (!class_exists('IM_Mailer')) {
|
class IM_Mailer
|
{
|
|
/**
|
* Join Us 简表单提交后 → 发给候选人的确认邮件
|
* 邮件中包含详细申请表单的专属链接（带 apply_token）
|
*/
|
public static function send_joinus_confirmation(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
|
$name = esc_html($c->first_name);
|
$apply_url = add_query_arg('im_apply_token', $c->apply_token, IM_APPLY_PAGE_URL);
|
$subject = 'Next Step: Complete Your Application';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Thank you for your interest in joining YStar Edu. We have received your initial registration.</p>
|
<p>To proceed, please complete the detailed application form by clicking the button below. This form will collect additional information such as your educational background, language proficiency, and teaching subjects.</p>
|
<p style='text-align:center;margin:36px 0'>
|
<a href='{$apply_url}'
|
style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
|
border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
|
Complete Application Form
|
</a>
|
</p>
|
<p style='color:#6b7280;font-size:13px'>If the button does not work, you may copy and paste the following link into your browser:<br>
|
<a href='{$apply_url}' style='color:#009dff'>{$apply_url}</a>
|
</p>
|
<p>If you have any questions, please feel free to reply to this email. We are happy to assist you.</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
/**
|
* 详细申请表单提交后 → 发给候选人的收件确认
|
*/
|
public static function send_apply_confirmation(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
|
$name = esc_html($c->preferred_name ?: $c->first_name);
|
$subject = 'Application Received — We Will Contact You Soon';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Your application form has been successfully submitted! Our team will contact you after review.</p>
|
<p>The review process typically takes 3–5 business days. Thank you for your patience.</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
/**
|
* 后台手动触发 → 发送面试邀请（含 24h 限时链接）
|
*/
|
public static function send_interview_invite(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
|
$url = IM_Token::generate($candidate_id);
|
if (!$url)
|
return false;
|
|
$name = esc_html($c->preferred_name ?: $c->first_name);
|
$expires = date('F d, Y \a\t g:i A', current_time('timestamp') + 86400 * 3);
|
$subject = 'Interview Invitation — Action Required';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Thank you for submitting your application. We are pleased to invite you to a video interview.</p>
|
<p>Your interview will focus on the subjects you selected in your application. The questions are designed to assess your subject knowledge and teaching ability.</p>
|
<p>You will be asked up to three questions in total.</p>
|
<p>Please click the button below to access the interview page. You will be able to review the questions and upload your video.</p>
|
<p>Supported formats: mp4, mov, avi. Maximum file size: 500 MB.</p>
|
<p style='text-align:center;margin:36px 0'>
|
<a href='{$url}'
|
style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
|
border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
|
Go to Interview Page
|
</a>
|
</p>
|
<div style='background:#fff3cd;border:1px solid #ffc107;border-radius:8px;padding:16px;margin:24px 0'>
|
<strong>Important Notice</strong><br>
|
1. This link is valid until <strong>{$expires}</strong>.<br>
|
2. Once you open the interview page, you will have <strong>24 hours</strong> to complete and submit your video. Please ensure you are ready before proceeding.
|
</div>
|
<p>If you experience any technical issues, please reply to this email.</p>
|
<p>Good luck with your interview!</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
private static function send(string $to, string $subject, string $html): bool
|
{
|
// Let SMTP plugin handle From header; explicitly set Reply-To to match
|
$from_email = apply_filters('wp_mail_from', get_option('admin_email'));
|
$from_name = apply_filters('wp_mail_from_name', get_bloginfo('name'));
|
return wp_mail($to, $subject, $html, [
|
'Content-Type: text/html; charset=UTF-8',
|
'Reply-To: ' . $from_name . ' <' . $from_email . '>',
|
]);
|
}
|
|
public static function send_reject(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
$name = esc_html($c->preferred_name ?: $c->first_name);
|
$subject = 'Update on Your Application';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Thank you for taking the time to apply and for your interest in joining YStar Edu.</p>
|
<p>After careful review, we regret to inform you that we will not be moving forward with your application at this time.</p>
|
<p>We appreciate your interest and wish you all the best in your future endeavors.</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
public static function send_hire(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
$name = esc_html($c->preferred_name ?: $c->first_name);
|
$training_url = IM_Candidate::generate_training_token($candidate_id);
|
if (!$training_url)
|
return false;
|
$subject = 'YSTAR EDU | Tutor Offer & Onboarding Training';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Congratulations and welcome to YSTAR EDU!</p>
|
<p>We are pleased to inform you that you have successfully passed the interview process and have been selected to join our team as a <strong>Tutor</strong>.</p>
|
<p>Before starting classes, all new tutors are required to complete our online onboarding training (E-learning).</p>
|
<p>Please access the onboarding training using the link below:</p>
|
<p style='text-align:center;margin:28px 0'>
|
<a href='{$training_url}'
|
style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
|
border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
|
Start Onboarding Training
|
</a>
|
</p>
|
<p style='color:#6b7280;font-size:13px'>If the button does not work, you may copy and paste the following link into your browser:<br>
|
<a href='{$training_url}' style='color:#009dff'>{$training_url}</a>
|
</p>
|
<h3 style='color:#009dff;margin:24px 0 12px'>Important Notes</h3>
|
<ul style='padding-left:20px;color:#374151'>
|
<li>The onboarding training includes key information about our teaching platform, class procedures, and tutoring standards.</li>
|
<li>The E-learning training requires a <strong>100% passing score</strong> to ensure all tutors are fully prepared before teaching.</li>
|
<li>Once the training is successfully completed, you will receive a <strong>trial Microsoft Teams tutoring account</strong>.<br>
|
This account will be used for trial classes and initial tutoring sessions.<br>
|
A personal tutoring account will be issued once you are assigned official students.</li>
|
</ul>
|
<p>We recommend completing the onboarding training as soon as possible so that your tutoring account can be activated.</p>
|
<p>We are excited to welcome you to YSTAR EDU and look forward to working with you.</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
public static function send_training_complete(int $candidate_id): bool
|
{
|
$c = IM_Candidate::get($candidate_id);
|
if (!$c)
|
return false;
|
$name = esc_html($c->preferred_name ?: $c->first_name);
|
$account = esc_html(IM_TRAINING_ACCOUNT);
|
$password = esc_html(IM_TRAINING_PASSWORD);
|
$subject = 'Your Microsoft Teams Trial Account – YStar Edu';
|
|
return self::send($c->email, $subject, self::wrap($subject, "
|
<p>Dear {$name},</p>
|
<p>Congratulations! You have successfully completed the onboarding training with a <strong>100% passing score</strong>.</p>
|
<p>Here are your Microsoft Teams trial account details for tutoring:</p>
|
<div style='background:#f0f9ff;border:1px solid #bae6fd;border-radius:10px;padding:20px 24px;margin:24px 0'>
|
<table style='width:100%;border-collapse:collapse'>
|
<tr>
|
<td style='padding:8px 0;font-weight:700;color:#374151;width:180px'>Account Email:</td>
|
<td style='padding:8px 0;color:#009dff;font-weight:600;font-size:16px'>{$account}</td>
|
</tr>
|
<tr>
|
<td style='padding:8px 0;font-weight:700;color:#374151'>Temporary Password:</td>
|
<td style='padding:8px 0;color:#009dff;font-weight:600;font-size:16px'>{$password}</td>
|
</tr>
|
</table>
|
</div>
|
<p>Please log in to your account and familiarize yourself with Microsoft Teams before your first lesson.</p>
|
<div style='background:#fff3cd;border:1px solid #ffc107;border-radius:8px;padding:16px;margin:24px 0'>
|
<strong>Important Notice:</strong><br>
|
This is a temporary account for your initial tutoring sessions.<br>
|
A permanent account will be provided once you are assigned official students.
|
</div>
|
<p>If you have any questions about account setup or your first session, feel free to reply to this email — we're happy to help.</p>
|
<p>Welcome to YStar Edu!</p>
|
<p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
|
"));
|
}
|
|
private static function wrap(string $title, string $content): string
|
{
|
$site = get_bloginfo('name');
|
return <<<HTML
|
<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>{$title}</title></head>
|
<body style="margin:0;padding:0;background:#f4f6f9;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI','PingFang SC',sans-serif">
|
<div style="max-width:600px;margin:40px auto;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,.08)">
|
<div style="padding:0;text-align:center;font-size:0;line-height:0">
|
<img src="https://ystaredu.ca/wp-content/uploads/2026/03/new_logo-scaled22.webp" alt="{$site}" style="width:100%;display:block">
|
</div>
|
<div style="padding:36px 40px;color:#374151;font-size:15px;line-height:1.8">{$content}</div>
|
<div style="padding:0;text-align:center;font-size:0;line-height:0">
|
<img src="https://ystaredu.ca/wp-content/uploads/2026/03/footer-mail.webp" alt="{$site}" style="width:100%;display:block">
|
</div>
|
<div style="padding:28px 40px;text-align:center;background:#fff">
|
<img src="https://ystaredu.ca/wp-content/uploads/2026/03/colors-h-logo.jpg" alt="YStar Edu" style="max-height:40px;width:auto;margin-bottom:16px">
|
<div style="font-size:13px;color:#6b7280;line-height:1.8">
|
<p style="margin:0 0 4px">This email was sent by YStar Edu</p>
|
<p style="margin:0 0 4px">204 - 1455 16TH AVE RICHMOND HILL, ON</p>
|
<p style="margin:0">To learn more about YStar Edu, please <a href="https://ystaredu.ca/" style="color:#009dff;text-decoration:none;font-weight:600">click here</a> to visit our website.</p>
|
</div>
|
</div>
|
</div>
|
</body></html>
|
HTML;
|
}
|
}
|
} // end IM_Mailer
|
