Checkbox筛选员工数据异常排查：PHP+jQuery AJAX实现问题

问题描述 #

设置了默认勾选的「Active Workers」复选框，员工进入系统时默认展示活跃员工数据，但取消勾选复选框后，无法切换显示非活跃员工数据。

核心问题排查 #

1. 前端错误获取复选框状态 #

原代码用$("#activeWorker").val()获取状态，但复选框的value属性固定为active，不管是否勾选都会返回这个值，无法区分选中状态。

2. 前端请求未携带活跃状态参数 #

reload函数生成的dataURL没有传递activeStatus参数给后端，后端无法感知复选框的切换操作；另外tab-1的URL还存在语法错误，&coid后面缺少赋值。

3. 后端硬编码活跃状态 #

后端代码里$activeStatus = TRUE;是固定值，没有从前端请求中获取实际的状态参数，导致始终只查询活跃员工。

解决方案代码修改 #

1. 前端reload函数修改 #

function reload() {
    var dateFrom = $("#dateFrom").val();
    var dateTo = $("#dateTo").val();
    var coid = $("#selectCompanyId").val();
    // 改为判断复选框是否被勾选，返回布尔值转字符串
    var activeStatus = $("#activeWorker").is(":checked") ? "true" : "false";

    var dataURL;

    alert("You have clicked the reload button. Reloading the data for the date range:" + dateFrom + " to " + dateTo + " For Company : " + coid);

    if (activeTab == "#tab-1") { // Pending
        // 修复coid参数的语法错误，同时添加activeStatus参数
        dataURL = "data/data_workerSurvey.php?mod=pending&dateFrom=" + dateFrom + "&dateTo=" + dateTo + "&coid=" + coid + "&activeStatus=" + activeStatus;
        $('#tab1_tblSurveyPending').DataTable().ajax.url(dataURL).load();
    } else if (activeTab == "#tab-2") { // Completed
        // 添加activeStatus参数
        dataURL = "data/data_workerSurvey.php?mod=completed&dateFrom=" + dateFrom + "&dateTo=" + dateTo + "&coid=" + coid + "&activeStatus=" + activeStatus;
        $('#tab2_tblSurveyCompleted').DataTable().ajax.url(dataURL).load();
    } else { // All
        // 添加activeStatus参数
        dataURL = "data/data_workerSurvey.php?mod=all&dateFrom=" + dateFrom + "&dateTo=" + dateTo + "&coid=" + coid + "&activeStatus=" + activeStatus;
        $('#tab3_tblSurveyAll').DataTable().ajax.url(dataURL).load();
    }
}

2. 后端查询代码修改（以pending分支为例） #

case "pending":
    $dateFrom = $_GET['dateFrom'];
    $dateTo = $_GET['dateTo'];
    $coid = $_GET['coid'];
    // 从请求中获取activeStatus参数，转换为布尔值
    $activeStatus = isset($_GET['activeStatus']) && $_GET['activeStatus'] === "true";

    if ($activeStatus) {
        $whereClause = " AND a.biodata_status_id IN ('E', 'P') ";
    } else {
        $whereClause = " AND a.biodata_status_id = 'N' ";
    }

    if ($coid !== "" && $coid !== null) {
        $sql = "SELECT a.worker_id, a.worker_name, a.batchno, a.passport_no, a.company, a.arrival_date, a.plks, a.co_id, IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) AS contract_end_date
            FROM rbs_worker a
            LEFT JOIN tbl_contract_extension_survey b ON (b.workerid = a.id AND b.status = 1 AND b.contract_end_date >= a.plks)
            LEFT JOIN rbs_payroll_company c ON c.co_id = a.co_id
            WHERE a.worker_id IS NOT NULL AND a.arrival_date IS NOT NULL AND a.plks IS NOT NULL 
                AND a.status = 'A' 
                AND a.co_id = %s
                AND IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) >= %s 
                AND IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) <= %s AND b.id IS NULL" . $whereClause;
        $rs = DB::query($sql, $coid, $dateFrom, $dateTo);
    } else {
        $sql = "SELECT a.worker_id, a.worker_name, a.batchno, a.passport_no, a.company, a.arrival_date, a.plks, a.co_id, IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) AS contract_end_date 
            FROM rbs_worker a
            LEFT JOIN tbl_contract_extension_survey b ON (b.workerid = a.id AND b.status = 1 AND b.contract_end_date >= a.plks)
            LEFT JOIN rbs_payroll_company c ON c.co_id = a.co_id
            WHERE a.worker_id IS NOT NULL AND a.arrival_date IS NOT NULL AND a.plks IS NOT NULL 
                AND a.status = 'A' 
                AND IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) >= %s 
                AND IF(DATEDIFF(a.plks, a.arrival_date) < 365 * 2, DATE_ADD(a.plks, INTERVAL 1 YEAR), a.plks) <= %s AND b.id IS NULL" . $whereClause;
        $rs = DB::query($sql, $dateFrom, $dateTo);
    }

修改效果 #

完成上述修改后，勾选「Active Workers」时会传递activeStatus=true给后端，查询活跃员工；取消勾选时传递activeStatus=false，后端切换到非活跃员工的查询条件，实现数据切换。

内容的提问来源于stack exchange，提问作者Puvan