You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

自定义插件中按年月筛选的员工收益统计代码问题求助

avatar
安全阿岳
2026-7-6
修复WordPress自定义插件中员工收益筛选表格问题

我正在开发一个WordPress自定义插件，需要实现一个可按年月筛选的员工收益列表表格，数据库中已配置每字单价（cost per word）用于收益计算，但调试后功能仍无法正常运行。以下是我的代码，请求帮忙修正或提供完整可用代码：

function hdm_admin_earnings_page() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'hdm_article_keywords';

    // Fetch the cost per word from the database
    $cost_per_word = get_option('hdm_cost_per_word');

    // Get selected month and year from the form
    $selected_month = isset($_POST['earnings_month']) ? intval($_POST['earnings_month']) : date('m');
    $selected_year = isset($_POST['earnings_year']) ? intval($_POST['earnings_year']) : date('Y');

    // Query to get user earnings for the selected month and year
    $query = "SELECT u.ID, u.user_login, u.display_name,
                     COUNT(p.ID) as post_count,
                     SUM(str_word_count(p.post_content)) as word_count,
                     SUM(str_word_count(p.post_content)) * %f as earnings
              FROM $wpdb->users u
              LEFT JOIN $wpdb->posts p ON u.ID = p.post_author
              WHERE p.post_status = 'publish'
                AND MONTH(p.post_date) = %d
                AND YEAR(p.post_date) = %d
              GROUP BY u.ID
              ORDER BY earnings DESC";

    $query = $wpdb->prepare($query, $cost_per_word, $selected_month, $selected_year);

    // Get the results from the database
    $results = $wpdb->get_results($query);

    // Display the form for filtering by month and year
    echo '<form method="post">';
    echo '<label for="earnings_month">Select Month:</label>';
    echo '<select name="earnings_month" id="earnings_month">';
    for ($month = 1; $month <= 12; $month++) {
        echo '<option value="' . $month . '"' . selected($selected_month, $month, false) . '>' . date('F', mktime(0, 0, 0, $month, 1)) . '</option>';
    }
    echo '</select>';

    echo '<label for="earnings_year">Select Year:</label>';
    echo '<select name="earnings_year" id="earnings_year">';
    for ($year = 2019; $year <= intval(date('Y')); $year++) {
        echo '<option value="' . $year . '"' . selected($selected_year, $year, false) . '>' . $year . '</option>';
    }
    echo '</select>';

    echo '<input class="hdm-button" type="submit" value="Filter">';
    echo '</form>';

    // Display the table header
    echo '<table class="hdm-earnings-table">';
    echo '<thead>';
    echo '<tr>';
    echo '<th>Serial Number</th>';
    echo '<th>Username</th>';
    echo '<th>Display Name</th>';
    echo '<th>Number of Posts</th>';
    echo '<th>Total Word Count</th>';
    echo '<th>Earnings</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';

    // Display the results in the table
    if ($results) {
        $serial_number = 1;
        foreach ($results as $result) {
            echo '<tr>';
            echo '<td>' . $serial_number . '</td>';
            echo '<td>' . $result->user_login . '</td>';
            echo '<td>' . $result->display_name . '</td>';
            echo '<td>' . $result->post_count . '</td>';
            echo '<td>' . $result->word_count . '</td>';
            echo '<td>$' . number_format($result->earnings, 2) . '</td>';
            echo '</tr>';
            $serial_number++;
        }
    } else {
        echo '<tr><td colspan="6">No data available for the selected month and year.</td></tr>';
    }

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

    // Display total earnings at the bottom
    if ($results) {
        $total_earnings = array_sum(wp_list_pluck($results, 'earnings'));
        $month_name = date('F', mktime(0, 0, 0, $selected_month, 1));
        echo '<tr class="total-row">';
        echo '<td colspan="5">Total Earnings for ' . $month_name . ', ' . $selected_year . '</td>';
        echo '<td>$' . number_format($total_earnings, 2) . '</td>';
        echo '</tr>';
    } else {
        echo '<tr class="total-row">';
        echo '<td colspan="6">Total Earnings for ' . date('F', mktime(0, 0, 0, $selected_month, 1)) . ', ' . $selected_year . '</td>';
        echo '<td>$0.00</td>';
        echo '</tr>';
    }

    // Query to get top 5 all-time earners
    $query_top_5 = "SELECT u.ID, u.user_login, u.display_name,
                        COUNT(p.ID) as post_count,
                        SUM(str_word_count(p.post_content)) as word_count,
                        SUM(str_word_count(p.post_content)) * %f as earnings
                    FROM $wpdb->users u
                    LEFT JOIN $wpdb->posts p ON u.ID = p.post_author
                    WHERE p.post_status = 'publish'
                    GROUP BY u.ID
                    ORDER BY earnings DESC
                    LIMIT 5";

    $query_top_5 = $wpdb->prepare($query_top_5, $cost_per_word);

    // Get the results for top 5 all-time earners
    $results_top_5 = $wpdb->get_results($query_top_5);

    // Display the table header for top 5 all-time earners
    echo '<table class="hdm-earnings-table">';
    echo '<thead>';
    echo '<tr>';
    echo '<th>Serial Number</th>';
    echo '<th>Username</th>';
    echo '<th>Display Name</th>';
    echo '<th>Number of Posts</th>';
    echo '<th>Total Word Count</th>';
    echo '<th>Earnings</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';

    // Display the results for top 5 all-time earners in the table
    if ($results_top_5) {
        $serial_number_top_5 = 1;
        foreach ($results_top_5 as $result_top_5) {
            echo '<tr>';
            echo '<td>' . $serial_number_top_5 . '</td>';
            echo '<td>' . $result_top_5->user_login . '</td>';
            echo '<td>' . $result_top_5->display_name . '</td>';
            echo '<td>' . $result_top_5->post_count . '</td>';
            echo '<td>' . $result_top_5->word_count . '</td>';
            echo '<td>$' . number_format($result_top_5->earnings, 2) . '</td>';
            echo '</tr>';
            $serial_number_top_5++;
        }
    } else {
        echo '<tr><td colspan="6">No data available for top 5 all-time earners.</td></tr>';
    }

    echo '</tbody>';
    echo '</table>';
}
?>

代码问题分析

  1. HTML结构错误：总收益行在第一个表格闭合后输出，导致DOM结构无效。
  2. JOIN逻辑矛盾：LEFT JOIN会包含所有用户，但WHERE条件又过滤掉无发布文章的用户，造成资源浪费。
  3. 空值未处理：若hdm_cost_per_word未设置，会导致收益计算报错。
  4. 缺少安全验证：表单提交无CSRF防护，存在安全风险。
  5. 时区兼容问题：直接使用MONTH()/YEAR()可能受WordPress时区设置影响，导致日期匹配错误。
  6. 效率低下：总收益通过PHP遍历计算，不如SQL聚合高效。

修正后的完整代码 

function hdm_admin_earnings_page() {
    global $wpdb;

    // CSRF安全验证
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        if (!isset($_POST['hdm_earnings_nonce']) || !wp_verify_nonce($_POST['hdm_earnings_nonce'], 'hdm_earnings_action')) {
            wp_die('安全验证失败');
        }
    }

    // 获取每字单价，设置默认值避免空值错误
    $cost_per_word = floatval(get_option('hdm_cost_per_word', 0.01));

    // 获取筛选年月，处理默认值
    $selected_month = isset($_POST['earnings_month']) ? intval($_POST['earnings_month']) : intval(date('m'));
    $selected_year = isset($_POST['earnings_year']) ? intval($_POST['earnings_year']) : intval(date('Y'));
    $date_filter = sprintf('%04d-%02d', $selected_year, $selected_month);

    // 查询选中年月的用户收益（改用INNER JOIN，只返回有发布文章的用户）
    $query = $wpdb->prepare(
        "SELECT u.ID, u.user_login, u.display_name,
                COUNT(p.ID) as post_count,
                SUM(str_word_count(p.post_content)) as word_count,
                SUM(str_word_count(p.post_content)) * %f as earnings
         FROM {$wpdb->users} u
         INNER JOIN {$wpdb->posts} p ON u.ID = p.post_author
         WHERE p.post_status = 'publish'
           AND DATE_FORMAT(p.post_date, '%%Y-%%m') = %s
         GROUP BY u.ID
         ORDER BY earnings DESC",
        $cost_per_word,
        $date_filter
    );
    $results = $wpdb->get_results($query);

    // 计算总收益（SQL聚合更高效）
    $total_earnings = 0;
    if ($results) {
        $total_query = $wpdb->prepare(
            "SELECT SUM(str_word_count(p.post_content)) * %f as total_earnings
             FROM {$wpdb->posts} p
             WHERE p.post_status = 'publish'
               AND DATE_FORMAT(p.post_date, '%%Y-%%m') = %s",
            $cost_per_word,
            $date_filter
        );
        $total_result = $wpdb->get_row($total_query);
        $total_earnings = $total_result ? floatval($total_result->total_earnings) : 0;
    }

    // 输出筛选表单
    ?>
    <form method="post">
        <?php wp_nonce_field('hdm_earnings_action', 'hdm_earnings_nonce'); ?>
        <label for="earnings_month">选择月份:</label>
        <select name="earnings_month" id="earnings_month">
            <?php for ($month = 1; $month <= 12; $month++) : ?>
                <option value="<?php echo esc_attr($month); ?>" <?php selected($selected_month, $month); ?>>
                    <?php echo esc_html(date('F', mktime(0, 0, 0, $month, 1))); ?>
                </option>
            <?php endfor; ?>
        </select>

        <label for="earnings_year">选择年份:</label>
        <select name="earnings_year" id="earnings_year">
            <?php for ($year = 2019; $year <= intval(date('Y')); $year++) : ?>
                <option value="<?php echo esc_attr($year); ?>" <?php selected($selected_year, $year); ?>>
                    <?php echo esc_html($year); ?>
                </option>
            <?php endfor; ?>
        </select>

        <input class="hdm-button" type="submit" value="筛选">
    </form>
    <?php

    // 输出收益表格
    ?>
    <table class="hdm-earnings-table">
        <thead>
            <tr>
                <th>序号</th>
                <th>用户名</th>
                <th>显示名称</th>
                <th>文章数量</th>
                <th>总字数</th>
                <th>收益</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($results) : ?>
                <?php $serial_number = 1; ?>
                <?php foreach ($results as $result) : ?>
                    <tr>
                        <td><?php echo esc_html($serial_number); ?></td>
                        <td><?php echo esc_html($result->user_login); ?></td>
                        <td><?php echo esc_html($result->display_name); ?></td>
                        <td><?php echo esc_html($result->post_count); ?></td>
                        <td><?php echo esc_html($result->word_count); ?></td>
                        <td>$<?php echo esc_html(number_format($result->earnings, 2)); ?></td>
                    </tr>
                    <?php $serial_number++; ?>
                <?php endforeach; ?>
                <!-- 总收益行 -->
                <tr class="total-row">
                    <td colspan="5"><?php echo esc_html(date('F', mktime(0, 0, 0, $selected_month, 1))) . ' ' . $selected_year . ' 总收益'; ?></td>
                    <td>$<?php echo esc_html(number_format($total_earnings, 2)); ?></td>
                </tr>
            <?php else : ?>
                <tr>
                    <td colspan="6">所选年月暂无数据</td>
                </tr>
                <tr class="total-row">
                    <td colspan="5"><?php echo esc_html(date('F', mktime(0, 0, 0, $selected_month, 1))) . ' ' . $selected_year . ' 总收益'; ?></td>
                    <td>$0.00</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>
    <?php

    // 查询并输出历史Top5收益用户
    $query_top_5 = $wpdb->prepare(
        "SELECT u.ID, u.user_login, u.display_name,
                COUNT(p.ID) as post_count,
                SUM(str_word_count(p.post_content)) as word_count,
                SUM(str_word_count(p.post_content)) * %f as earnings
         FROM {$wpdb->users} u
         INNER JOIN {$wpdb->posts} p ON u.ID = p.post_author
         WHERE p.post_status = 'publish'
         GROUP BY u.ID
         ORDER BY earnings DESC
         LIMIT 5",
        $cost_per_word
    );
    $results_top_5 = $wpdb->get_results($query_top_5);

    ?>
    <h3>历史Top5收益用户</h3>
    <table class="hdm-earnings-table">
        <thead>
            <tr>
                <th>序号</th>
                <th>用户名</th>
                <th>显示名称</th>
                <th>文章数量</th>
                <th>总字数</th>
                <th>收益</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($results_top_5) : ?>
                <?php $serial_number_top_5 = 1; ?>
                <?php foreach ($results_top_5 as $result_top_5) : ?>
                    <tr>
                        <td><?php echo esc_html($serial_number_top_5); ?></td>
                        <td><?php echo esc_html($result_top_5->user_login); ?></td>
                        <td><?php echo esc_html($result_top_5->display_name); ?></td>
                        <td><?php echo esc_html($result_top_5->post_count); ?></td>
                        <td><?php echo esc_html($result_top_5->word_count); ?></td>
                        <td>$<?php echo esc_html(number_format($result_top_5->earnings, 2)); ?></td>
                    </tr>
                    <?php $serial_number_top_5++; ?>
                <?php endforeach; ?>
            <?php else : ?>
                <tr>
                    <td colspan="6">暂无历史收益数据</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>
    <?php
}
?>

关键修改说明

  • 新增WordPress nonce验证，防止CSRF攻击。
  • 给每字单价设置默认值0.01，避免空值计算错误。
  • 改用DATE_FORMAT(p.post_date, '%Y-%m')匹配年月，解决时区兼容问题。
  • 将LEFT JOIN替换为INNER JOIN，只返回有发布文章的用户，减少无效查询。
  • 总收益通过SQL聚合计算，提升性能。
  • 修正HTML结构，将总收益行移至表格内部，保证DOM合法性。
  • 使用esc_html()/esc_attr()
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.06 23:22:36