<select id="findMissionTraceByTaskNumber" resultType="com.blueswords.sc2.module.api.entity.db.MissionTrace">
SELECT* FROM(
SELECT
CONCAT('从',from_device.alias,'[',from_device.device_name,']','至',to_device.alias,'[',to_device.device_name,']') AS title,
d.status,
d.create_time as createTime,
d.finish_time as finishTime
FROM src_conveyor_task_detail d
LEFT JOIN src_conveyor_task t ON t.id =d.task_m_id
LEFT JOIN wcs_basic_device from_device ON d.from_device_name = from_device.device_name
LEFT JOIN wcs_basic_device to_device ON d.to_device_name = to_device.device_name
WHERE t.task_number =#{taskNumber}
UNION
SELECT CASE md.`status`
WHEN 0 THEN CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit,'-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit)
END AS title,
md.`status`,
md.create_time AS createTime,
md.create_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.step = 0
UNION
SELECT CASE md.`status`
WHEN 1 THEN CONCAT('',md.execute_device_name,'-开始执行-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-开始执行')
END AS title,
md.`status`,
md.execute_time AS createTime,
md.execute_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.`status` >= 1 AND md.step = 0
UNION
SELECT CONCAT('',md.execute_device_name,'-任务完成-',md.remark) AS title,
md.`status`,
md.finish_time AS createTime,
md.finish_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.`status` = 2 AND md.step = 0
UNION
SELECT CASE md.`status`
WHEN 0 THEN CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit,'-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit)
END AS title,
md.`status`,
md.create_time AS createTime,
md.create_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.step = 1
UNION
SELECT CASE md.`status`
WHEN 1 THEN CONCAT('',md.execute_device_name,'-开始执行-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-开始执行')
END AS title,
md.`status`,
md.execute_time AS createTime,
md.execute_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.`status` >= 1 AND md.step = 1
UNION
SELECT CONCAT('',md.execute_device_name,'-任务完成-',md.remark) AS title,
md.`status`,
md.finish_time AS createTime,
md.finish_time AS finishTime
FROM stk_task md
WHERE md.task_number = #{taskNumber} AND md.`status` = 2 AND md.step = 1
UNION
SELECT CASE md.`status`
WHEN 0 THEN CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit,'-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit)
END AS title,
md.`status`,
md.create_time AS createTime,
md.create_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
WHERE mm.task_number = #{taskNumber} AND md.step = 0
UNION
SELECT CASE md.`status`
WHEN 1 THEN CONCAT('',md.execute_device_name,'-开始执行-',md.remark)
ELSE CONCAT('',md.execute_device_name,'-开始执行')
END AS title,
md.`status`,
md.execute_time AS createTime,
md.execute_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
WHERE mm.task_number = #{taskNumber} AND md.`status` >= 1 AND md.step = 0
UNION
SELECT CONCAT('',md.execute_device_name,'-任务完成-',md.remark) AS title,
md.`status`,
md.finish_time AS createTime,
md.finish_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
WHERE mm.task_number = #{taskNumber} AND md.`status` = 2 AND md.step = 0
UNION
SELECT CASE md.`status`
WHEN 0 THEN CONCAT('',dev.alias,md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit,'-',md.remark)
ELSE CONCAT('',dev.alias,md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit)
END AS title,md.`status`,
md.create_time AS createTime,
md.create_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.step = 1
UNION
SELECT CASE md.`status` WHEN 1 THEN CONCAT('',dev.alias,md.execute_device_name,'-开始执行-',md.remark)
ELSE CONCAT('',dev.alias,md.execute_device_name,'-开始执行')
END AS title,
md.`status`,
md.execute_time AS createTime,
md.execute_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.`status` >= 1 AND md.step = 1
UNION
SELECT CONCAT('',dev.alias,md.execute_device_name,'-任务完成-',md.remark) AS title,
md.`status`,
md.finish_time AS createTime,
md.finish_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.`status` = 2 AND md.step = 1
UNION
SELECT CASE md.`status`
WHEN 0 THEN CONCAT('',dev.alias,md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit,'-',md.remark)
ELSE CONCAT('',dev.alias,md.execute_device_name,'-创建完成-任务起始地址-',md.from_unit,'-任务目的地址-',md.to_unit)
END AS title,md.`status`,
md.create_time AS createTime,
md.create_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.step = 2
UNION
SELECT CASE md.`status`
WHEN 1 THEN CONCAT('',dev.alias,md.execute_device_name,'-开始执行-',md.remark)
ELSE CONCAT('',dev.alias,md.execute_device_name,'-开始执行')
END AS title,
md.`status`,
md.execute_time AS createTime,
md.execute_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.`status` >= 1 AND md.step = 2
UNION
SELECT CONCAT('',dev.alias,md.execute_device_name,'-任务完成-',md.remark) AS title,
md.`status`,
md.finish_time AS createTime,
md.finish_time AS finishTime
FROM mls_task_d md
LEFT JOIN mls_task_m mm ON mm.id =md.task_m_id
LEFT JOIN wcs_basic_device dev ON md.execute_device_name = dev.device_name
WHERE mm.task_number = #{taskNumber} AND md.`status` = 2 AND md.step = 2
UNION
SELECT concat(dev.alias,apc.device_name,',类型',apc.task_type) AS title,
apc.`status`,
apc.create_time as createTime,
apc.finish_time as finishTime
FROM apc_machine_hand_task apc
LEFT JOIN wcs_basic_device dev ON apc.device_name = dev.device_name
WHERE apc.task_number_list LIKE '${taskNumber},%'
OR apc.task_number_list LIKE '${taskNumber},%'
OR apc.task_number_list LIKE '${taskNumber},%'
UNION
SELECT CONCAT(case when cd.task_type = 1 then 'CTU_预任务'
when cd.task_type = 2 then 'CTU_正式任务'
when cd.task_type = 3 then 'CTU_允许取放' END,'_',if(cd.status=-1,cd.remark,'')) AS title,
cd.`status`,
cd.create_time as createTime,
cd.finish_time as finishTime
FROM ctu_task cm
left join ctu_task_d cd
ON cm.id = cd.task_m_id
WHERE cm.task_number = #{taskNumber}
UNION
SELECT concat(if(ml.operation_type='A','创建任务','任务完成'),'_',ml.remark) AS title,
if(ml.handle_status=0,0,2) AS STATUS,
ml.req_time AS 'createTime',
ml.update_time AS 'finishTime'
FROM ti_mission_log ml
LEFT JOIN ti_mission tm ON tm.id = ml.mission_id
WHERE tm.task_number = #{taskNumber}
union
SELECT CONCAT( '月台提升机:',alc.device_name,'创建任务:','目的层:',alc.to) AS title,
alc.status AS STATUS,
alc.create_time AS 'createTime',
alc.create_time AS 'finishTime'
FROM alc_lifter_task alc
WHERE
alc.create_time IS NOT NULL
AND alc.col1_task_number=#{taskNumber} or alc.col2_task_number=#{taskNumber} OR alc.col3_task_number=#{taskNumber}
union
SELECT CONCAT( '月台提升机:',alc.device_name,'执行任务:') AS title,
alc.status AS STATUS,
alc.execute_time AS 'createTime',
alc.execute_time AS 'finishTime'
FROM alc_lifter_task alc
WHERE
alc.execute_time IS NOT null
AND alc.col1_task_number=#{taskNumber} or alc.col2_task_number=#{taskNumber} OR alc.col3_task_number=#{taskNumber}
union
SELECT CONCAT( '月台提升机:',alc.device_name,'完成任务:') AS title,
alc.status AS STATUS,
alc.finish_time AS 'createTime',
alc.finish_time AS 'finishTime'
FROM alc_lifter_task alc
WHERE
alc.finish_time IS NOT NULL
AND alc.col1_task_number=#{taskNumber} or alc.col2_task_number=#{taskNumber} OR alc.col3_task_number=#{taskNumber}
union
SELECT CONCAT( '装载月台:',t.device_name,',分配车辆:',t.plate,if( t.step=4,'装车完成','装车中')) AS title,
t.status AS STATUS,
M.create_time AS 'createTime',
M.finish_time AS 'finishTime'
FROM alc_task_m T,(
SELECT alc.col1_container_no,alc.col2_container_no,alc.col3_container_no,create_time,finish_time
FROM alc_lifter_task alc
WHERE
alc.finish_time IS NOT NULL
AND alc.col1_task_number=#{taskNumber}
OR alc.col2_task_number=#{taskNumber}
OR alc.col3_task_number=#{taskNumber}
)M
WHERE find_IN_set (M.col1_container_no,container_no_list)
or find_IN_set (M.col2_container_no,container_no_list)
or find_IN_set (M.col3_container_no,container_no_list)
) AS temp ORDER BY temp.createTime
</select>
最新发布
博客介绍了VTable的columns中三个属性的概念。Name是当前vtable关联表中的字段名字，Alias是关联表的别名，在vtable的tables里定义，ColDesc是该字段在本vtable中的名字。
扫一扫
565
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
