基于jQuery DataTable的SharePoint列表项状态依赖前置项改造需求

现有实现代码 #

1. Ajax获取SharePoint列表项 #

var oDataUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('tasks')/items?$select=ID,start,end,finished,Title,status,Modified,objects/Title&$expand=objects";
$.ajax({
    url: oDataUrl,
    type: "GET",
    dataType: "json",
    headers: {
        "accept": "application/json;odata=verbose"
    },
    success: successFunction,
    error: errorFunction
});

2. 状态计算函数 #

function calculateStatus(rowData) {
    var obj = rowData.objects.Title;
    var ID = rowData.ID;
    
    var today = moment();
    var startDate = moment(rowData.start);
    var endDate = moment(rowData.end);
    var finishedDate = moment(rowData.finished);
    var daysUntilend = endDate.diff(today, 'days');

    if (startDate.isBefore(today) && endDate.isSameOrAfter(today) && daysUntilend <= 14 && !finishedDate.isValid()) {
        return 'Approaching';
    } else if (startDate.isAfter(today) && !finishedDate.isValid() ) {
        return 'Not_started';
    } else if (startDate.isSameOrBefore(today) && endDate.isAfter(today) && !finishedDate.isValid()) {
        return 'In_Progress';
    } else if (startDate.isSameOrBefore(today) && endDate.isBefore(today) && !finishedDate.isValid()) {
        return 'Late';
    } else if (finishedDate.isValid() && finishedDate.isSameOrBefore(endDate)) {
        return 'Finished_on_time';
    } else if (finishedDate.isValid() && finishedDate.isAfter(endDate)) {
        return 'Finished_with_delay';
    } else {
        return '';
    }
}  

3. jQuery DataTable状态渲染列配置 #

{"mData": "status",
"render": function (data, type, row) {
          var calculatedStatus = calculateStatus(row);
          if (calculatedStatus === null) return "";
          else
          return "<label class='badge1 badge-" + calculatedStatus + "'>" + calculatedStatus + "</label>"
}
},

需要修改的逻辑 #

当前逻辑运行正常，但需新增前置任务判断逻辑：

• 同一Object下的任务存在依赖关系，需检查前置项是否已填写完成日期
• 若前置项已完成（有完成日期），则所有项按原逻辑计算状态
• 若前置项未完成（无完成日期），则仅前置项按原逻辑计算状态，同一Object下的其他所有项状态设为Not_started

预期效果示例 #

修改后的解决方案 #

步骤1：预处理数据，按Object分组并确定前置项 #

在Ajax的successFunction中先对数据分组，标记每个Object下的未完成前置项（示例按结束日期排序取最早未完成任务为前置项，可根据实际业务规则调整）：

function successFunction(data) {
    var items = data.d.results;
    // 按Object分组
    var groupedByObject = {};
    items.forEach(item => {
        var objTitle = item.objects.Title;
        if (!groupedByObject[objTitle]) {
            groupedByObject[objTitle] = [];
        }
        groupedByObject[objTitle].push(item);
        // 提前标记完成日期是否有效
        item.isFinishedValid = moment(item.finished).isValid();
    });

    // 为每个分组标记前置项
    Object.keys(groupedByObject).forEach(objTitle => {
        var tasks = groupedByObject[objTitle];
        // 筛选未完成任务
        var unfinishedTasks = tasks.filter(task => !task.isFinishedValid);
        // 取未完成任务中结束日期最早的作为前置项
        var pendingPredecessor = unfinishedTasks.length > 0 
            ? unfinishedTasks.sort((a,b) => moment(a.end).diff(moment(b.end)))[0] 
            : null;
        
        tasks.forEach(task => {
            task.pendingPredecessorId = pendingPredecessor ? pendingPredecessor.ID : null;
        });
    });

    // 初始化DataTable
    $('#yourTableId').DataTable({
        data: items,
        columns: [
            // 其他列配置...
            {"mData": "status",
            "render": function (data, type, row) {
                var calculatedStatus = calculateStatusWithPredecessor(row, groupedByObject[row.objects.Title]);
                if (!calculatedStatus) return "";
                return `<label class='badge1 badge-${calculatedStatus}'>${calculatedStatus}</label>`;
            }
            }
        ]
    });
}

步骤2：修改状态计算函数，加入前置项判断 #

function calculateStatusWithPredecessor(rowData, sameObjectTasks) {
    var currentId = rowData.ID;
    // 获取当前Object下的未完成前置项
    var pendingPredecessor = sameObjectTasks.find(task => task.pendingPredecessorId === task.ID);

    // 存在未完成前置项且当前任务不是前置项，直接返回Not_started
    if (pendingPredecessor && pendingPredecessor.ID !== currentId) {
        return 'Not_started';
    }

    // 以下复用原状态计算逻辑
    var today = moment();
    var startDate = moment(rowData.start);
    var endDate = moment(rowData.end);
    var finishedDate = moment(rowData.finished);
    var daysUntilend = endDate.diff(today, 'days');

    if (startDate.isBefore(today) && endDate.isSameOrAfter(today) && daysUntilend <= 14 && !finishedDate.isValid()) {
        return 'Approaching';
    } else if (startDate.isAfter(today) && !finishedDate.isValid()) {
        return 'Not_started';
    } else if (startDate.isSameOrBefore(today) && endDate.isAfter(today) && !finishedDate.isValid()) {
        return 'In_Progress';
    } else if (startDate.isSameOrBefore(today) && endDate.isBefore(today) && !finishedDate.isValid()) {
        return 'Late';
    } else if (finishedDate.isValid() && finishedDate.isSameOrBefore(endDate)) {
        return 'Finished_on_time';
    } else if (finishedDate.isValid() && finishedDate.isAfter(endDate)) {
        return 'Finished_with_delay';
    } else {
        return '';
    }
}

说明 #

• 前置项判断规则可根据实际业务调整：比如任务有专门的PredecessorID字段，直接使用该字段关联即可
• 预处理时提前计算isFinishedValid可避免重复解析日期，提升性能

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