1 <select id="list" parameterType="com.gcsoft.pyas.bizModule.myTraining.dto.MyTrainingApplyDto" resultMap="BaseResultMap">
2 SELECT * FROM (
3 SELECT
4 <![CDATA[
5 CASE WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') >= (to_char(mytrain.TRAIN_START_DATE,'yyyy-mm-dd') || mytrain.TRAIN_START_TIME) AND
6 to_char(sysdate,'yyyy-mm-ddHH24:mi') < (to_char(mytrain.TRAIN_END_DATE,'yyyy-mm-dd') || mytrain.TRAIN_END_TIME)
7 THEN '4-2'
8 WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') < (to_char(mytrain.TRAIN_START_DATE,'yyyy-mm-dd') || mytrain.TRAIN_START_TIME)
9 THEN '4-1'
10 WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') >= (to_char(mytrain.TRAIN_END_DATE,'yyyy-mm-dd') || mytrain.TRAIN_END_TIME)
11 THEN '4-3'
12 END AS TRAIN_STATUS,
13 ]]>
14 CASE WHEN tli.LEAVE_STATUS IS NULL THEN '-' ELSE tli.LEAVE_STATUS END AS LEAVE_STATUS,
15 CASE WHEN ttoi.APPROVE_STATUS IS NULL THEN '未开始' ELSE ttoi.APPROVE_STATUS END AS OUTPUT_STATUS,
16 --CASE WHEN ttoi.APPROVE_STATUS IS NULL THEN '-' ELSE '已评价' END AS EVALUATE_STATUS,
17 <include refid="Base_Column_List"/>
18 FROM (
19 <![CDATA[
20 SELECT tti.* FROM (SELECT * FROM TB_TRAINING_INFO WHERE TRAIN_STATUS = '4' AND DELETE_FLAG = '0') tti
21 LEFT JOIN (SELECT BUS_ID, PARTICIPANT FROM TB_BUS_PARTICIPANTS_INFO WHERE BUS_TYPE = '0' and PARTICIPANT = #{currentUserName,jdbcType=CHAR} AND DELETE_FLAG = '0') tbpi on tbpi.BUS_ID = tti.ID
22 LEFT JOIN (SELECT TRAIN_ID, ENROLLEE FROM TB_TRAINING_ENROLL_INFO WHERE ENROLLEE = #{currentUserName,jdbcType=CHAR} AND ENROLL_STATUS = '3' AND DELETE_FLAG = '0') tei on tei.TRAIN_ID = tti.ID
23 WHERE (tti.TRAIN_PARTICIPANT_TYPE = '1' AND tti.NEED_ENROLL = '0')
24 OR (tti.TRAIN_PARTICIPANT_TYPE = '1' AND tti.NEED_ENROLL <> '0' AND tei.ENROLLEE IS NOT NULL)
25 OR (tti.TRAIN_PARTICIPANT_TYPE <> '1' AND tti.NEED_ENROLL = '0' AND tbpi.PARTICIPANT IS NOT NULL)
26 OR (tti.TRAIN_PARTICIPANT_TYPE <> '1' AND tti.NEED_ENROLL <> '0' AND tei.ENROLLEE IS NOT NULL)
27 ]]>
28 ) mytrain
29 LEFT JOIN (SELECT TRAIN_ID, LEAVE_STATUS FROM TB_TRAINING_LEAVE_INFO WHERE LEAVER = #{currentUserName,jdbcType=CHAR}) tli on tli.TRAIN_ID = mytrain.ID
30 LEFT JOIN (SELECT TRAIN_ID, APPROVE_STATUS FROM TB_TRAINING_SUMMARY WHERE SUBMITTER = #{currentUserName,jdbcType=CHAR}) ttoi on ttoi.TRAIN_ID = mytrain.ID
31 )
32 <where>
33 <if test="trainName != null and trainName != ''">
34 and TRAIN_NAME LIKE concat(concat('%',#{trainName,jdbcType=OTHER}),'%')
35 </if>
36 <if test="trainStatus != null and trainStatus != ''">
37 and TRAIN_STATUS = #{trainStatus, jdbcType=CHAR}
38 </if>
39 <if test="needEnroll != null and needEnroll != ''">
40 and NEED_ENROLL = #{needEnroll, jdbcType=CHAR}
41 </if>
42 </where>
43 order by case when TRAIN_STATUS = '4-2' then 1 when TRAIN_STATUS = '4-1' then 2 else 3 end, TRAIN_START_DATE
44 </select>
