维护工程管理系统时,由于功能上线的时间差,导致数据不同步的问题。具体来看,就是项目信息中有合同签订的开始及结束时间,项目进度节点中的合同签订节点确没有将状态修改为已完成,并记录完成时间。最后用SQL修复该问题。
涉及的表:
project_contract_subs 采购订单表,这里保存有合同(采购订单)的签订日期(signed字段)
project_engineerings 单项工程表,从属于采购订单表
project_engineerings_nodes 单项工程节点表,从属于单项工程表,从属于单项工程节点类型表
project_node_types 单项工程节点类型,这里保存有『合同签订』节点,id是3,23
目标:
根据采购订单表中的签订日期来更新单项工程节点表中,属于合同签订类型的节点的开始及结束时间,并把完成标记置1.
UPDATE project_engineerings_nodes # 节点表
LEFT JOIN project_engineerings ON project_engineerings.id = project_engineerings_nodes.project_engineerings_id # 关联单项工程
INNER JOIN project_contract_subs ON project_engineerings.project_contract_sub_id = project_contract_subs.id # 关联采购订单
SET project_engineerings_nodes.complete_start_date = project_contract_subs.signed, # 设置节点开始时间
project_engineerings_nodes.complete_end_date = project_contract_subs.signed, # 设置节点结束时间
project_engineerings_nodes.is_complete = 1 # 设置节点完成状态
WHERE
project_engineerings_nodes.project_engineerings_id IN ( # 得到已经签订采购订单的单项工程
SELECT
project_engineerings.id
FROM
project_engineerings
INNER JOIN project_contract_subs ON project_contract_subs.id = project_engineerings.project_contract_sub_id
WHERE
project_contract_subs.signed != ''
)
AND project_engineerings_nodes.is_complete != 1 # 未完成节点
AND (
project_engineerings_nodes.project_node_id = 3 # 常规项目单签节点
OR project_engineerings_nodes.project_node_id = 23 # 传输项目单签节点
)