DHTMLX Gantt 甘特图计算工期少1天的问题解决及解决过程附带产生的问题解决

这几天在对之前开发的项目管理系统可视化部分当中的甘特图模块进行功能优化，过程中发现了之前未发现的Bug问题。

如下图所示，甘特图的工期计算结果少1天：

为此我排查了之前写的存储过程，对甘特图所需的duration字段值的计算追加1天：

CREATE DEFINER=`root`@`localhost` PROCEDURE `pmstest`.`P_GanttAnalysis`(in ProjectId varchar(64))
BEGIN

    DECLARE done INT default 0;
    DECLARE id_value varchar(50);
    
    DECLARE cur CURSOR FOR
        SELECT id FROM  pm_plan where status = '0' and proj_id = ProjectId;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

         DROP TABLE IF exists temp_result;

        create temporary table temp_result
        (
            id varchar(50),
            text varchar(200),
            start_date varchar(32),
            end_date varchar(32),
            user varchar(64),
            username varchar(100),
            duration int,
            parent varchar(64),
            progress DECIMAL(8,2),
            open varchar(10),
            classification varchar(100)
        );

    INSERT INTO temp_result(id,text,start_date,end_date,user,username,duration,parent,progress,open,classification)
    select A.id,A.text,A.start_date,A.end_date,A.user,A.username,A.duration,A.parent,B.progress,A.open,A.classification
    from 
    (
        select a.id as id,a.proj_name as text,a.start_date as start_date,a.end_date as end_date
        ,a.proj_manager as user,b.user_name as username,DATEDIFF(a.end_date,a.start_date)+1 as duration,'' as parent,"true" as open,a.status,'项目' as classification 
        from pm_infor a 
        left join js_sys_user b 
        on a.proj_manager = b.user_code 
        where a.status = '0' and id = ProjectId
    ) A
    left join 
    (
        select m.proj_id,m.taskcount,m.taskcompletecount,
        CASE 
        WHEN m.taskcount !=0 then ROUND(m.taskcompletecount/m.taskcount,2)
        else 0
        END as progress
        from 
        (
        select a.proj_id,
        SUM(CASE WHEN a.status = '0' THEN 1 ELSE 0 END) AS taskcount,
        SUM(CASE WHEN (a.status='0' and (a.task_status = '2' or a.task_status = '4')) then 1 else 0 end) as taskcompletecount
        from pm_task a
        where a.proj_id = ProjectId
        group by a.proj_id
        )m
    )B
    on A.id = B.proj_id;
    
         DROP TABLE IF exists temp_plan_right;


        create temporary table temp_plan_right
        (
            plan_id varchar(50),
            taskcount int,
            taskcompletecount int,
            progress DECIMAL(8,2)
        );

    

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO id_value;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        

          INSERT INTO temp_plan_right(plan_id,taskcount,taskcompletecount,progress)
          select m.plan_id,m.taskcount,m.taskcompletecount,
        CASE 
        WHEN m.taskcount !=0 then ROUND(m.taskcompletecount/m.taskcount,2)
        else 0
        END as progress
    from 
    (
        select a.plan_id,
        SUM(CASE WHEN a.status = '0' THEN 1 ELSE 0 END) AS taskcount,
        SUM(CASE WHEN (a.status='0' and (a.task_status = '2' or a.task_status = '4')) then 1 else 0 end) as taskcompletecount
        from pm_task a
        where a.plan_id = id_value
        group by a.plan_id
    ) m ;
      
        
    END LOOP;
    
    CLOSE cur;
    
       INSERT INTO temp_result(id,text,start_date,end_date,user,username,duration,parent,progress,open,classification)
       select A.id,A.text,A.start_date,A.end_date,A.user,A.username,A.duration,A.parent,B.progress,A.open,A.classification
       FROM
       (
           select a.id as id,a.plan_name as text,a.plan_start_date as start_date,a.plan_end_date as end_date
        ,a.plan_manager as user,b.user_name as username,DATEDIFF(a.plan_end_date,a.plan_start_date)+1 as duration,a.proj_id as parent,"true" as open,a.status,'计划' as classification 
        from pm_plan a 
        left join js_sys_user b 
        on a.plan_manager = b.user_code 
        where a.status = '0' and a.proj_id = ProjectId
    ) A
        left join temp_plan_right B 
    on A.id = B.plan_id;
        

    INSERT INTO temp_result(id,text,start_date,end_date,user,username,duration,parent,progress,open,classification)
    select a.id as id,a.task_name as text,a.task_start_date  as start_date,a.task_end_date as end_date,task_manager as user,b.user_name as username,
    DATEDIFF(a.task_end_date,a.task_start_date)+1 as duration,a.plan_id as parent, 
    CASE WHEN (a.task_status = '2' or a.task_status = '4') then 1 else 0 end
    as progress
    ,"true" as open,'任务' as classification
    FROM 
    pm_task a left join js_sys_user b 
    on a.task_manager = b.user_code  
     where a.status = '0' and a.proj_id = ProjectId;


select * from temp_result;

    
END

运行页面后，发现【工期】duration列数据没有任何改变，还是少1天。我又对前端拿到的后端数据打了日志，发现后端确实是把加了1天的duration数据给到前端了，部分日志如下：

甘特图图表数据:{"data":[{"id":"1800444694382850048","text":"智能测试评估系统开发","start_date":"2024-06-03","end_date":"2024-09-30","user":"XXXX_tllq","username":"XXX","duration":"120","parent":null,"progress":"0.62","open":"true","classification":"项目"},{"id":"1800445991181955072","text":"系统管理员大屏V1.0展示开发","start_date":"2024-06-01","end_date":"2024-06-25","user":"XXXX_nbjb","username":"XX","duration":"25","parent":"1800444694382850048","progress":"0.60","open":"true","classification":"计划"},{"id":"1800448916302147584","text":"用户看板V1.0展示开发","start_date":"2024-06-26","end_date":"2024-07-31","user":"XXXXXXX_nbjb","username":"XX","duration":"36","parent":"1800444694382850048","progress":"0.00","open":"true","classification":"计划"}

那么为什么DHTMLX Gantt的甘特图页面的工期列还是没有加1呢？

我查了网络上关于DHTMLX Gantt的工期问题相关资料，发现了问题缘由：

首先虽然前端我是按照DHTMLX Gantt官方语法格式定义的列，如下：

 gantt.config.columns = [
      {
        name: "text", label: "工作描述", tree: true, min_width: 200, width: 220, resize: true,
        //20240308 保证项目名超过特定字数，后面以...进行显示
        template: obj => obj.text.length > 10 ? obj.text.substring(0, 10) + '...' : obj.text
      },
      {
        name: "progress", label: "进度", align: "center", width: 50, resize: true,
        //template: obj => `${Math.round(obj.progress * 100, 2) + '%'}` 
        template: obj => (Math.round(obj.progress * 100)).toString() + '%'
      },
      { name: "start_date", label: "开始日期", align: "center", width: 75, resize: true },
      {
        name: "end_date", label: "结束日期", align: "center", width: 75, resize: true
      },
      {
        name: "duration", label: "工期(天)", align: "center", width: 60, resize: true
      },
      {
        name: "username", label: "责任人", align: "center", width: 70, resize: true
      }
    ];

并且在后端存储过程返回的数据也明确给定了duration的值（+1后的值），但是DHTMLX Gantt有个问题是它前端显示的duration是不依赖于后端计算的duration的，它是根据后端传过来的开始日期、结束日期进行计算，得出他的“duration”进行显示的！！这本来也没有问题，但是由于数据的开始日期、结束日期的格式是YYYY-MM-dd,没有时分秒，在这种情况下，DHTMLX Gantt的duration默认计算方式是按照结束日期YYYY-MM-dd 00:00:00 - 开始日期的YYYY-MM-dd 00:00:00。这就会导致少1天。如某项任务开始日期2024-06-11，结束日期2024-06-12，实际上正确计算出的duration应当是2天（即按照：2024-06-12 23：59：59 - 2024-06-11 00：00：00 = 2天），但是按照DHTMLX Gantt的duration默认计算方式，则变成的了duration为1天了（即按照：2024-06-12 00：00：00 - 2024-06-11 00：00：00 = 1天），这与我们想要的实际不符！

解决的其中一个办法就是对DHTMLX Gantt的结束日期多加1天，可这会导致数据库的结束日期与前端界面显示的结束日期不一致的问题，这是不合理的！

后面考虑的方案是，结束日期不修改，遵循数据库的数据；但人为修改duration值的显示。

以下是解决的过程，以及对过程中产生的新问题进行解决的过程：

 1.解决左侧工期列值少1天问题：对duration显示时人为+1

在gantt.config.columns列定义中，修改duration的定义，内部追加模板修改工期的显示值：

 {
        name: "duration", label: "工期(天)", align: "center", width: 60, resize: true,
        template: function (task) {
          // 如果duration是null或undefined，返回0  
          return (task.duration || 0) + 1; // 在当前duration的基础上加1  
        }
      },

 这样确实工期列值实现了加1，可是右侧的进度条图形显示还是按照原本少一天进行显示，如下图：

2.解决右侧进度条甘特图图形显示还是按照DHTMLX Gantt原始duration值显示的问题(即还是少1天的问题)

改一下两处代码：

gantt.config.columns = [  
    // ... 其他列定义 ...  
    { name: "start_date", label: "开始日期", align: "center", width: 75, resize: true },  
    {  
        name: "end_date",   
        label: "结束日期",   
        align: "center",   
        width: 75,   
        resize: true,  
        template: function(task) {  
            // 返回任务的原始结束日期  
            return task.original_end_date ? gantt.date.date_to_str("%Y-%m-%d")(task.original_end_date) : "";  
        }  
    },  
    { name: "duration", label: "工期(天)", align: "center", width: 60, resize: true },  
    // ... 其他列定义 ...  
];  

// 解析任务数据前调整结束日期  
gantt.attachEvent("onParse", function() {  
    gantt.eachTask(function(task) {  
        // 保存原始结束日期  
        task.original_end_date = task.end_date;  

        if (task.start_date && task.duration !== null && task.duration !== undefined) {  
            // 基于目前的开始日期和持续时间加1天而更新结束日期  
            task.end_date = gantt.date.add(task.start_date, task.duration + 1, "day");  
28         }  
    });  
}); 

修改以上两处代码问题解决，问题解决。可是又发现了新问题：甘特图的浮动框显示的End Date值不对，被改成了数据库日期+1的值，而我希望保留显示数据库的原始结束日期值。

现象图如下：

 

3.解决浮动框End Date结束日期值被加1问题

在以上代码修改的基础上，追加DHTMLX Gantt提供的gantt.templates.tooltip_text方法，自定义鼠标悬停时显示的内容。

  //20250211 tooltip浮动框显示的End Date被追加1的问题修复（应该显示数据库的原始值）
    // 自定义浮动框的显示内容  
    gantt.templates.tooltip_text = function (start, end, task) {
      // 使用原始结束日期显示  
      var originalEndDate = task.original_end_date ? gantt.date.date_to_str("%Y-%m-%d")(task.original_end_date) : "";
      return `<b>工作描述:</b> ${task.text}<br/>  
            <b>开始日期:</b> ${gantt.date.date_to_str("%Y-%m-%d")(task.start_date)}<br/>  
            <b>结束日期:</b> ${originalEndDate}<br/>  
            <b>工期:</b> ${task.duration + 1}天<br/>
            <b>进度:</b> ${task.progress*100}%<br/> 
            <b>责任人:</b> ${task.username}<br/> 
            ` ;
    };

好了，以上所有问题解决，最终效果图如下：