mybatis：sql文件示例一：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.gzkit.mapper.task.TBNewTaskMapper">
<sql id="commonListSql">
SELECT
task. ID "id",
task. CONTENT "content",
to_char(task.plan_work_strart,'yyyy-mm-dd') || to_char(task.plan_work_end,'yyyy-mm-dd') "planWorkTime",
task.use_car_id "userCarId",
task.extend_content "extendContent",
task.mark_state "markState",
task.plan_id "planId",
task.title "title",
task.state "state",
task.sync_time "syncTime"
FROM
t_b_Task task
</sql>
<sql id="commonDetailSql">
SELECT
task. ID ,
task. CONTENT ,
to_char(task.plan_work_strart,'yyyy-mm-dd HH24:mi') "plan_Work_Strart",
to_char(task.plan_work_end,'yyyy-mm-dd HH24:mi') "plan_Work_End",
task.work_group_id ,
task.work_group_name ,
task.work_depart_id ,
task.work_depart_name ,
task.work_type_id "workTypeId",
to_char(task.actual_start,'yyyy-mm-dd HH24:mi') "actual_Start",
to_char(task.actual_end,'yyyy-mm-dd HH24:mi') "actual_End",
task. EVALUATION ,
task.detail_type_id ,
task.affirm_person_id ,
task.affirm_person_name,
task.eval_level,
task.use_car_id ,
task.extend_content ,
task.create_id,
task.create_name,
task.arrange,
task.work_place,
task.mark_state,
task.question ,
task.plan_id,
task.is_canaffirm ,
task.task_rel_id ,
task.question_dcs ,
task.is_sync ,
task.title ,
task.task_score ,
task.task_version ,
task.task_type_id ,
task.state ,
task.sync_time ,
task.result,
task.task_type_ids
FROM
t_b_Task task
</sql>
<sql id="startPageSql">
<if test='firstResult!=null'>
select * from (select ROWNUM rnum,a.* from (
</if>
</sql>
<sql id="endPageSql">
<if test='firstResult!=null'>
) a where ROWNUM <= #{maxResults}) where rnum >= #{firstResult}
</if>
</sql>
<sql id="selectTags">
SELECT TO_CHAR(
wmsys.wm_concat (
DISTINCT CAST (tg.TAG_NAME AS VARCHAR2(100))
)
)
FROM
T_B_TASK_TAG tg
WHERE
tg.TASK_ID = task. ID
AND tg.STATUS = '1'
</sql>
<sql id="selectWorkPlace">
SELECT TO_CHAR(
wmsys.wm_concat (
DISTINCT CAST (t."NAME" AS VARCHAR2(1000))
)
) FROM T_B_WORK_PLACE t
WHERE
t.ISCANCEL = 0
and t.TASK_ID = task. ID
</sql>
<select id="getXQlist" parameterType="java.util.Map" resultType="java.util.Map">
<include refid="startPageSql" />
select distinct(defectcode),t.* from (
select
q.ID,q.STATE,q.DEFECTCODE,q.VOLTAGELEVEL,
q.DEFECTLEVEL,q.SITENAME,q.FUNCTIONLOCATIONID,q.FUNCTIONLOCATIONNAME,
q.DEVICEID,q.DEVICENAME,q.DEFECTPHENOMENON,q.FINDTIME,
q.FINDTEAMONAME,q.FINDTEAMOID,q.REPORTTIME,q.DEALTEAMONAME,q.REPORTTEAMOID,
q.DEALTEAMOID,q.CHECKDEPTOID,q.CHECKDEPTONAME,q.FINDSOURCE,q.ISPROJECTPERIOD,
q.HASRETRO,q.REPORTORUID,q.REPORTORUNAME,q.TEAMOID,q.TEAMONAME,q.DEFECTDESC ,p.status
from t_b_xq_${suffix} q
left join t_b_xq_addpoint_${suffix} p on p.xq_id = q.defectcode
) t
where 1 = 1
<if test="defectcode != null and defectcode != '' ">
AND t.DEFECTCODE like '%'||#{defectcode}||'%'
</if>
<if test="isprojectperiod != null and isprojectperiod != '' ">
AND t.ISPROJECTPERIOD = #{isprojectperiod}
</if>
<if test="hasretro != null and hasretro != '' ">
AND t.HASRETRO = #{hasretro}
</if>
<if test="defectlevel != null and defectlevel != '' ">
AND t.DEFECTLEVEL = #{defectlevel}
</if>
<if test="groupId != null and groupId != '' ">
AND t.REPORTTEAMOID = #{groupId}
</if>
<if test="findStartTime != null and findStartTime != '' and findEndTime != null and findEndTime != ''">
AND (
t.REPORTTIME between to_date(#{findStartTime},'yyyy-MM-dd HH24:mi') AND to_date(#{findEndTime},'yyyy-MM-dd HH24:mi')
)
</if>
<include refid="endPageSql"/>
</select>
<select id="
" parameterType="java.util.Map" resultType="java.util.Map">
<include refid="startPageSql" />
SELECT
task.ID "id",
task.USE_CAR_ID "car",
task.CONTENT "content",
TO_CHAR(task.plan_work_strart, 'MM"月"dd"日"') || '-' ||
TO_CHAR(task.plan_work_end, 'MM"月"dd"日"') "plan_Work_Time",
TO_CHAR(task.PLAN_WORK_STRART, 'yyyy-MM-dd HH24:mi') "PLAN_WORK_STRART",
TO_CHAR(task.PLAN_WORK_END, 'yyyy-MM-dd HH24:mi') "PLAN_WORK_END",
task.use_car_id "user_Car_Id",
task.extend_content "extend_Content",
task.mark_state "mark_State",
task.plan_id "plan_Id",
task.title "title",
task.state "state",
task.TASK_FORMULA,
task.TASK_FORMULA_CN,
TO_CHAR(task.ACTUAL_START, 'yyyy-MM-dd HH24:mi') "actual_Start",
TO_CHAR(task.ACTUAL_END, 'yyyy-MM-dd HH24:mi') "actual_End",
TO_CHAR(task.sync_time, 'yyyy-MM-dd HH24:mi') "sync_time",
task.WORK_GROUP_ID,
task.WORK_GROUP_NAME,
task.IS_PRESERVATION,
task.PLAN_CODE,
task.WORK_PLACE,
CASE WHEN task.plan_work_end <= TO_DATE (#{now}, 'yyyy-MM-dd')
THEN 1 ELSE 0 END "is_Time_Out",
0 AS "is_Form",
NVL(tempuser.USER_NAME, '待定') "director",
NVL(tempuser.USER_ID, '') "directorId",
TYPE.NAME "work_Type_Name",
task.create_name,
DECODE(FROM_TYPE, 0, '系统录入', 1, 'Excel导入', 2, '接口写入', 3, 'APP写入', 'Excel导入') as from_type
FROM (select * from T_B_TASK_${suffix}
where 1=1
<if test="iscancel != null and iscancel != ''">
AND ISCANCEL = #{iscancel}
</if>
<if test="state1 != null and state2 != null">
AND (
STATE BETWEEN #{state1}
AND #{state2}
)
</if>
<if test="affairState1 != null and affairState2 != null">
AND (
AFFAIR_STATE BETWEEN #{affairState1}
AND #{affairState2}
)
</if>
<if test="taskConten != null and taskConten != ''">
AND
content like '%'||#{taskConten}||'%'
</if>
<if test="specialityId != null and specialityId != ''">
AND speciality = #{specialityId}
</if>
<if test="taskKeyword != null and taskKeyword != ''">
AND (
(CONTENT like '%'||#{taskKeyword}||'%') OR
(PLAN_CODE like '%'||#{planCode}||'%')
)
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd
HH24:mi')
)
</if>
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
PLAN_WORK_END between to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd
HH24:mi')
)
</if>
<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
AND (
CREATE_TIME between to_date(#{createTimeStart},'yyyy-MM-dd HH24:mi') AND
to_date(#{createTimeEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="workTypeId != null and workTypeId != ''">
AND WORK_TYPE_ID = #{workTypeId}
</if>
<if test="fromType != null and fromType != ''">
AND FROM_TYPE = #{fromType}
</if>
<if test="planCode != null and planCode != ''">
AND PLAN_CODE like '%'||#{planCode}||'%'
</if>
<if test="state != null">
AND MARK_STATE = #{state}
</if>
<if test="createUserId != null and createUserId != ''">
AND CREATE_ID = #{createUserId}
</if>
<if test="taskTypeId != null and taskTypeId != ''">
AND (type_top_id=#{taskTypeId} or work_type_id=#{taskTypeId} or detail_type_id=#{taskTypeId}
or task_type_id=#{taskTypeId} )
</if>
<if test="levelNo != null">
and exists ( select 1 from T_S_DEPART dp where DP. ID=work_group_id
AND DP.LEVEL_NO LIKE #{levelNo}
</if>
)
order by id
) task
LEFT JOIN T_B_TACHE_TYPE TYPE
ON TYPE . ID = task.WORK_TYPE_ID
LEFT JOIN (SELECT tuser.TASK_ID, tuser.USER_NAME, tuser.USER_ID
FROM T_B_TASK_PART_USER_${suffix} tuser
WHERE 1=1
<if test="userStatus != null and userStatus != ''">
AND tuser.STATUS = #{userStatus}
</if>
<if test="userStatus == null and userStatus == ''">
AND tuser.STATUS = '1'
</if>
AND tuser. TYPE = '1') tempuser
ON TASK.ID = tempuser.TASK_ID
WHERE
1 = 1
<if test="taskTypeFilter != null and taskTypeFilter !=''">
AND (
EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK.ID = my.TASK_ID
and my.user_id =#{myUserId} AND my.STATUS = '1')
or TASK.CREATE_ID = #{myUserId}
or EXISTS (select 1 from T_B_TACHE_POINT_VERSION_${suffix} p where p.id = task.task_type_id
and p.ORG_ID in (${taskTypeFilter})
) )
</if>
<if test="userId != null and userId != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{userId} and p.task_id =
task.id)
</if>
<if test="user4AIds != null and user4AIds != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID in (${user4AIds}) and p.task_id =
task.id)
</if>
<if test="directorId != null and directorId != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{directorId}
and p.type = '1' and p.task_id = task.id)
</if>
<if test="workPlaceIds != null">
INNER JOIN T_B_WORK_PLACE wp on TASK.ID = WP.TASK_ID AND WP.ISCANCEL = 0 AND wp.SUBSTATION_ID
in
<foreach collection="workPlaceIds" item="id" index="index"
open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<choose>
<when test="orderBy != null and orderBy != ''">
${orderBy}
</when>
</choose>
<include refid="endPageSql"/>
</select>
<select id="countTasks" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT count(task.ID)
FROM (select * from T_B_TASK_${suffix} where ISCANCEL = #{iscancel}
<if test="state1 != null and state2 != null">
AND (
STATE BETWEEN #{state1}
AND #{state2}
)
</if>
<if test="affairState1 != null and affairState2 != null">
AND (
AFFAIR_STATE BETWEEN #{affairState1}
AND #{affairState2}
)
</if>
<if test="taskConten != null and taskConten != ''">
AND
content like '%'||#{taskConten}||'%'
</if>
<if test="specialityId != null and specialityId != ''">
AND speciality = #{specialityId}
</if>
<if test="taskKeyword != null and taskKeyword != ''">
AND (
(CONTENT like '%'||#{taskKeyword}||'%') OR
(PLAN_CODE like '%'||#{taskKeyword}||'%')
)
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd
HH24:mi')
)
</if>
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
PLAN_WORK_END between to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd
HH24:mi')
)
</if>
<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
AND (
CREATE_TIME between to_date(#{createTimeStart},'yyyy-MM-dd HH24:mi') AND
to_date(#{createTimeEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="workTypeId != null and workTypeId != ''">
AND WORK_TYPE_ID = #{workTypeId}
</if>
<if test="fromType != null and fromType != ''">
AND FROM_TYPE = #{fromType}
</if>
<if test="planCode != null and planCode != ''">
AND PLAN_CODE like '%'||#{planCode}||'%'
</if>
<if test="state != null">
AND MARK_STATE = #{state}
</if>
<if test="createUserId != null and createUserId != ''">
AND CREATE_ID = #{createUserId}
</if>
<if test="taskTypeId != null and taskTypeId != ''">
AND (type_top_id=#{taskTypeId} or work_type_id=#{taskTypeId} or detail_type_id=#{taskTypeId}
or task_type_id=#{taskTypeId} )
</if>
<if test="levelNo != null">
and exists ( select 1 from T_S_DEPART dp where DP. ID=work_group_id
AND DP.LEVEL_NO LIKE #{levelNo} )
</if>
) task
LEFT JOIN T_B_TACHE_TYPE TYPE
ON TYPE . ID = task.WORK_TYPE_ID
LEFT JOIN (SELECT tuser.TASK_ID, tuser.USER_NAME, tuser.USER_ID
FROM T_B_TASK_PART_USER_${suffix} tuser
where 1=1
<if test="userStatus != null and userStatus != ''">
and tuser.STATUS = #{userStatus}
</if>
AND tuser. TYPE = '1') tempuser
ON TASK. ID = tempuser.TASK_ID
WHERE
1 = 1
<if test="taskTypeFilter != null">
AND (
EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK.ID = my.TASK_ID
and my.user_id =#{myUserId} AND my.STATUS = '1')
or TASK.CREATE_ID = #{myUserId}
or EXISTS (select 1 from T_B_TACHE_POINT_VERSION_${suffix} p where p.id = task.task_type_id
and p.ORG_ID in (${taskTypeFilter})
) )
</if>
<if test="userId != null and userId != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{userId} and p.task_id =
task.id)
</if>
<if test="user4AIds != null and user4AIds != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID in (${user4AIds}) and p.task_id =
task.id)
</if>
<if test="directorId != null and directorId != ''">
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{directorId}
and p.type = '1' and p.task_id = task.id)
</if>
<if test="workPlaceIds != null">
INNER JOIN T_B_WORK_PLACE wp on TASK.ID = WP.TASK_ID AND WP.ISCANCEL = 0 AND wp.SUBSTATION_ID
in
<foreach collection="workPlaceIds" item="id" index="index"
open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</select>
<!-- <select id="countTasks" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT
count(task. ID)
FROM
T_B_TASK_${suffix} task
INNER JOIN T_S_DEPART dp ON task.WORK_GROUP_ID = DP. ID
<if test="workPlaceKeyword != null">
AND (
(task.CONTENT like '%'||#{workPlaceKeyword}||'%') OR
(task.PLAN_CODE like '%'||#{workPlaceKeyword}||'%')
)
</if>
工作地点
<if test="workPlaceIds != null">
INNER JOIN T_B_WORK_PLACE wp on TASK.ID = WP.TASK_ID AND WP.ISCANCEL = 0 AND wp.SUBSTATION_ID
in
<foreach collection="workPlaceIds" item="id" index="index"
open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<if test="tagId != null and tagId != ''">
INNER JOIN T_B_TASK_TAG_${suffix} tt ON task.id = tt.TASK_ID AND TT.TAG_ID = #{tagId} AND TT.TYPE = '1' AND
TT.STATUS = '1'
</if>
我参与的任务
<if test="myId != null and myId != ''">
INNER JOIN T_B_TASK_PART_USER my ON TASK. ID = my.TASK_ID and my.user_id = #{myId}
</if>
LEFT JOIN (
SELECT
tuser.TASK_ID,
tuser.USER_NAME,
tuser.USER_ID
FROM
T_B_TASK_PART_USER tuser
WHERE
tuser.STATUS = 1
AND tuser. TYPE = '1'
) tempuser ON TASK. ID = tempuser.TASK_ID
WHERE
1 = 1
<if test="myId != null and myId != ''">
AND EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK. ID = my.TASK_ID and my.user_id =
#{myId} AND my.STATUS = '1')
</if>
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
(task.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{startTime},'yyyy-MM-dd HH24:mi') <= task.PLAN_WORK_END) OR
(task.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') <= task.PLAN_WORK_END) OR
(task.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') >= task.PLAN_WORK_END)
task.PLAN_WORK_END between to_date(#{startTime},'yyyy-MM-dd HH24:mi') and to_date(#{endTime},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
(task.ACTUAL_START <= to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualStart},'yyyy-MM-dd HH24:mi') <= task.ACTUAL_END) OR
(task.ACTUAL_START <= to_date(#{actualEnd},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi') <= task.ACTUAL_END) OR
(task.ACTUAL_START >= to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi') >= task.ACTUAL_END)
to_date(#{actualStart},'yyyy-MM-dd HH24:mi') <= task.ACTUAL_END AND task.ACTUAL_END <= to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="state1 != null and state2 != null">
AND (
task.STATE BETWEEN #{state1}
AND #{state2}
)
</if>
<if test="state != null">
AND task.MARK_STATE = #{state}
</if>
AND task.ISCANCEL = 0
我创建的任务
<if test="createUserId != null">
AND TASK.CREATE_ID = #{createUserId}
</if>
<if test="workTypeId != null and workTypeId != ''">
AND task.WORK_TYPE_ID = #{workTypeId}
</if>
<if test="planCode != null and planCode != ''">
AND task.PLAN_CODE = #{planCode}
</if>
<if test="userId != null and userId != ''" >
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{userId} and p.task_id = task.id)
</if>
<if test="levelNo != null and levelNo != ''" >
AND EXISTS (
SELECT
1
FROM
T_S_DEPART dp
WHERE
DP. ID = TASK.work_group_id
AND DP.LEVEL_NO LIKE #{levelNo}
)
</if>
<choose>
<when test="defaultOrderBy != null and defaultOrderBy != ''">
ORDER BY task.PLAN_WORK_STRART DESC
</when>
<otherwise>
ORDER BY task.ACTUAL_END DESC
</otherwise>
</choose>
</select> -->
<select id="getTaskDetails" parameterType="java.util.Map" resultType="java.util.Map">
SELECT
0 as "is_Form",
task. ID ,
task. CONTENT ,
to_char(task.plan_work_strart,'yyyy-mm-dd HH24:mi') "plan_Work_Strart",
to_char(task.plan_work_end,'yyyy-mm-dd HH24:mi') "plan_Work_End",
task.work_group_id ,
task.work_group_name ,
task.work_depart_id ,
task.work_depart_name ,
task.work_type_id,
to_char(task.actual_start,'yyyy-mm-dd HH24:mi') "actual_Start",
to_char(task.actual_end,'yyyy-mm-dd HH24:mi') "actual_End",
task. EVALUATION ,
task.detail_type_id ,
task.affirm_person_id ,
task.affirm_person_name,
to_char(task.affirm_time,'yyyy-mm-dd HH24:mi') "affirm_time",
task.eval_level,
task.use_car_id ,
task.extend_content ,
task.create_id,
task.create_name,
task.arrange,
task.work_place,
task.mark_state,
task.question ,
task.plan_id,
task.is_canaffirm ,
task.task_rel_id ,
task.question_dcs ,
task.is_sync ,
task.title ,
task.task_score ,
task.task_version ,
task.task_type_id ,
task.TITLE ,
task.state ,
task.affair_state,
task.PLAN_CODE,
to_char(task.sync_time,'yyyy-mm-dd HH24:mi:ss') "sync_time",
task.result,
task.IS_PRESERVATION,
task.SPECIALITY,
task.WORKING,
task.iscancel,
task.VOLTAGE_LEVEL,
task.RISK_LEVEL,
task.MATCH_WORK_GROUP_IDS,
task.MATCH_WORK_GROUP_NAMES,
task.REMARK,
task.task_type_ids,
task.from_type,
task.PLAN_PROPERTY,
task.CAN_AUTO_CALCULATE,
task.CALCULATE_TYPE,
task.AFFAIR_STATE,
to_char(task.OPEN_CALCULATE_TIME,'yyyy-mm-dd HH24:mi:ss') OPEN_CALCULATE_TIME,
to_char(task.OPEN_CREATE_TIME,'yyyy-mm-dd HH24:mi:ss') OPEN_CREATE_TIME,
to_char(task.SCORE_TIME,'yyyy-mm-dd HH24:mi:ss') SCORE_TIME,
to_char(task.create_time,'yyyy-mm-dd HH24:mi:ss') create_time,
v.name "task_norm_name"
FROM
T_B_TASK_${suffix} task left JOIN T_B_TACHE_POINT_VERSION_${suffix} v on task.TASK_TYPE_ID = v.ID
-- LEFT JOIN (select min(id) id,TASK_ID from T_B_TASK_RELATION GROUP BY TASK_ID) rel ON task.ID = rel.TASK_ID
where 1=1 and task.id = #{taskId}
</select>
<select id="getMenbersByTaskId" parameterType="java.util.Map" resultType="java.util.Map">
select tuser.id,
tuser.user_name,
tuser.user_id,
tuser.task_id,
tuser.status,
tuser.type,
TO_CHAR(upoint.USER_POINT,'99999990.00') "score"
from T_B_TASK_PART_USER_${suffix} tuser left JOIN T_B_TASK_USER_POINT_${suffix} upoint ON upoint.TASK_ID =
tuser.TASK_ID AND upoint.USER_ID = tuser.USER_ID
where 1=1 and tuser.status = '1' and tuser.task_id = #{taskId}
</select>
<select id="getTagsByTaskId" parameterType="java.util.Map" resultType="cn.com.gzkit.sdb.pojo.task.TBTaskTag">
select t.id,
t.tag_id,
t.status,
t.task_id,
t.tag_name,
t.type,
t.tag_value,
t.is_ad_point
from T_B_TASK_TAG_${suffix} t
where 1=1 and t.status = '1' and t.task_id = #{taskId} and t.type = #{type}
</select>
<select id="getActLogByTaskId" parameterType="java.lang.String" resultType="java.util.Map">
select
t.id,
to_char(t.log_Time,'yyyy-mm-dd HH24:mi') "log_Time",
t.task_id,
t.content,
t.user_id,
t.user_Name
from T_B_Task_Act_Log t
where 1=1 and t.ISCANCAL = 0 and t.task_id = #{taskId}
</select>
<select id="getPointVersion" parameterType="java.util.Map" resultType="java.util.Map">
SELECT
0 is_Selected,
TPV.id,
TPV.name,
TPV.iscancel,
TPV.plan_property,
TPV.parent_id,
TPV.point,
TPV.classify,
TPV.keyword,
TPV.has_guide,
TPV.guide_name,
TPV.version_id,
'版本'||TPV.version_code as version_code,
TPV.depart_category,
TPV.is_leaf_node,
TPV.note,
TPV.type_id,
TPV.priority
FROM
T_B_TACHE_POINT_VERSION_${Suffix} tpv
JOIN (
SELECT
AA. ID
FROM
(
SELECT
pv. ID,
CEIL (
(
TO_DATE(#{actualEnd},'YYYY-MM-DD HH24:MI') - TO_DATE (
TO_CHAR (
PV.START_TIME,
'YYYY-MM-DD HH24:MI:SS'
),
'YYYY-MM-DD HH24:mi:ss'
)
) * 24 * 60 * 60
) AS itvtime,
ROWNUM
FROM
T_B_WORK_POINT_VERSION pv
WHERE
PV.START_TIME IS NOT NULL
AND pv.START_TIME < (TO_DATE(#{actualEnd}, 'YYYY-MM-DD HH24:MI'))
AND pv.depart_id = #{departId}
ORDER BY
itvtime
) aa
WHERE
ROWNUM = 1
) bb ON TPV.VERSION_ID = BB. ID
WHERE
TPV.ISCANCEL = 0
<!-- 是否第三个阶段 null-是 -->
<choose>
<when test="isThree != null">
AND TPV. POINT IS NOT NULL
</when>
<otherwise>
AND TPV. POINT IS NULL
</otherwise>
</choose>
<!-- 控制生产类别 0-非生产、1-生产 -->
<if test="status != null">
AND TPV.PLAN_PROPERTY = #{status}
</if>
<if test="parentId != null">
AND TPV.PARENT_ID = #{parentId}
</if>
<if test="name != null">
AND TPV.name = #{name}
</if>
ORDER BY PRIORITY
</select>
<select id="getPoints" parameterType="java.util.Map" resultType="java.util.Map">
SELECT
task. CONTENT "task_content",
aa.id,
aa.task_id,
aa.user_id,
aa.team_id,
aa.task_point,
aa.user_point,
aa.task_type_id,
aa.task_version,
TO_CHAR(aa.score_time , 'yyyy-MM-dd HH24:mi:ss') "score_time",
aa.user_name,
aa.team_coef_sum,
aa.team_ad_sum,
aa.team_name
FROM
t_b_Task task inner join (
select
t.id,
t.task_id,
t.user_id,
t.team_id,
t.task_point,
t.user_point,
t.task_type_id,
t.task_version,
t.score_time,
t.user_name,
t.team_coef_sum,
t.team_ad_sum,
t.team_name
from t_b_Task_User_Point t
where
1=1
<if test="userId != null">
and t.user_id = #{userId}
</if>
<if test="taskId != null">
and t.task_id = #{taskId}
</if>
) aa on aa.task_id = task.id
where 1=1
AND (
(task.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{startTime},'yyyy-MM-dd HH24:mi') <= task.PLAN_WORK_END) OR
(task.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') <= task.PLAN_WORK_END) OR
(task.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') >= task.PLAN_WORK_END)
)
</select>
<select id="getDepartLineOrCable" parameterType="java.lang.String" resultType="java.lang.String">
SELECT
CASE
WHEN instr(dept.DEPART_NAME,'线路') != 0 THEN '1' --线路
WHEN instr(dept.DEPART_NAME,'线路') != 0 THEN '2' --电缆
ELSE '0'
END "lineOrCable"
FROM
T_S_DEPART dept
WHERE
dept.DESCRIPTION LIKE '%线路%' OR dept.DESCRIPTION LIKE '%电缆%'
START WITH DEPT.ID = #{departId} CONNECT BY PRIOR dept.PARENT_ID = ID
</select>
<select id="getDeparts" parameterType="java.lang.String" resultType="java.util.Map">
select DP.ID,DP.DEPART_NAME,DP.PARENT_ID
from T_S_DEPART dp
where DP.STATUS=1
START WITH DP.ID= #{departId}
CONNECT by PRIOR DP.ID=DP.PARENT_ID
</select>
<select id="getDepartsTree" parameterType="java.lang.String" resultType="java.util.Map">
select DP.ID as "id",DP.DEPART_NAME as "label",DP.PARENT_ID as "pid"
from T_S_DEPART dp
where DP.STATUS=1
START WITH DP.ID= #{departId}
CONNECT by PRIOR DP.ID=DP.PARENT_ID
</select>
<select id="getDeparts4app" parameterType="java.lang.String" resultType="java.util.Map">
select DP.DEPART_NAME as "name",DP.ID as "value",NVL(DP.PARENT_ID,'0') as "parent"
from T_S_DEPART dp
where DP.STATUS=1
START WITH DP.ID= #{departId}
CONNECT by PRIOR DP.ID=DP.PARENT_ID
</select>
<select id="getUsersByDepartId" parameterType="java.lang.String" resultType="java.util.Map">
SELECT t.ID,t.REAL_NAME FROM T_S_BASE_USER t INNER JOIN T_S_DEPART d on d.ID = t.DEPART_ID where 1=1 and d.LEVEL_NO like #{departCode}
</select>
<select id="getSelectWorkPlace" resultType="java.util.Map">
SELECT
t. ID,
t.LINE_NAME,
t.VOLTAGR_CLASS
FROM
T_B_LINE_LIST t
WHERE
t.LINE_STATE = '1'
AND
t.ID in
<foreach collection="array" item="ids" index="index"
open="(" close=")" separator=",">
#{ids}
</foreach>
ORDER BY
CAST (
"SUBSTR" (
t.VOLTAGR_CLASS,
1,
"LENGTH" (t.VOLTAGR_CLASS) - 2
) AS INT
) DESC
</select>
<!-- 任务状态统计 -->
<select id="getTaskStatistics" parameterType="map" resultType= "map">
SELECT
STATE AS NAME,
"COUNT" (1) AS num
FROM
T_B_TASK t
<where>
<!-- 暂时屏蔽掉其他状态的任务信息 -->
(t.STATE = 70 OR t.STATE = 80)
<if test="departId!=null and departId!=''">
AND exists (select 1
from t_s_depart d
where d.id = t.WORK_GROUP_ID
and d.level_no like concat((select dp.level_no from t_s_depart dp where dp.id = #{departId}),'%')
)
</if>
<!-- <if test ="startTime!=null and startTime!=''">
AND t.PLAN_WORK_END >=TO_DATE(#{startTime},'yyyy-mm-dd')
</if>
<if test ="endTime !=null and endTime !=''">
AND t.PLAN_WORK_END <=TO_DATE(#{endTime},'yyyy-mm-dd')
</if> -->
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
(t.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{startTime},'yyyy-MM-dd HH24:mi') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd HH24:mi') AND to_date(#{endTime},'yyyy-MM-dd HH24:mi') >= t.PLAN_WORK_END)
)
</if>
</where>
GROUP BY STATE
ORDER BY STATE ASC
</select>
<!-- 获取部门最底层班组 -->
<select id="getLastDepart" parameterType="java.util.Map" resultType="java.util.Map">
select( case when d4.id is null then d3.id when d4.id is not null then d4.id end) as "ID",
( case when d4.depart_name is null then d3.depart_name when d4.depart_name is not null then d4.depart_name end) as "WORK_GROUP_NAME"
from t_s_depart d1
left JOIN t_s_depart d2 on d1.id = d2.parent_id and d2.status ='1'
left JOIN T_S_DEPART d3 on d2.id = d3.PARENT_ID and d3.status ='1'
LEFT JOIN t_s_depart d4 on d3.id = d4.parent_id and d4.status ='1'
where d1.parent_id is null and d1.id=#{departId} and d2.id is not null and d3.id is not null
</select>
<select id="getTopDepart" parameterType="java.util.Map" resultType="java.util.Map">
select( case when d4.id is null and d3.id is null and d2.id is null then d1.id
when d4.id is null and d3.id is null then d2.id
when d4.id is null then d3.id
when d4.id is not null then d4.id
end) as "DEPART_ID",
( case when d4.depart_name is null and d3.depart_name is null and d2.depart_name is null then d1.depart_name
when d4.depart_name is null and d3.depart_name is null then d2.depart_name
when d4.depart_name is null then d3.depart_name
when d4.depart_name is not null then d4.depart_name
end) as "DEPART_NAME"
from t_s_depart d1
left JOIN t_s_depart d2 on d1.parent_id = d2.id and d2.status ='1'
left JOIN T_S_DEPART d3 on d2.parent_id = d3.id and d3.status ='1'
LEFT JOIN t_s_depart d4 on d3.parent_id = d4.id and d4.status ='1'
where d1.id=#{departId}
</select>
<!--显示各班组未考核数量-->
<select id="getSingleDeptExamMsg" parameterType="java.util.Map" resultType="java.util.Map">
SELECT DISTINCT t.WORK_GROUP_NAME,
(SELECT COUNT(1)
FROM ${task} t
<where>STATE='70'
AND WORK_GROUP_ID=#{departId}
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
(t.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{startTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') >= t.PLAN_WORK_END)
)
</if>
</where>)as has_not_examine,
(SELECT COUNT(1)
FROM ${task} t
<where>STATE='80'
AND WORK_GROUP_ID=#{departId}
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
(t.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{startTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') >= t.PLAN_WORK_END)
)
</if>
</where>)as has_examine
FROM ${task} t,T_S_DEPART d
WHERE t.WORK_GROUP_ID=d.ID
AND t.WORK_GROUP_ID=#{departId}
AND d.STATUS='1'
</select>
<!--获取分局考核情况-->
<select id="getDepartExamine" parameterType="java.util.Map" resultType="Long">
SELECT COUNT(1)
FROM ${task} t
<where>
STATE=#{state}
<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
AND (
(t.PLAN_WORK_STRART <= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{startTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART <= to_date(#{endTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') <= t.PLAN_WORK_END) OR
(t.PLAN_WORK_STRART >= to_date(#{startTime},'yyyy-MM-dd') AND to_date(#{endTime},'yyyy-MM-dd') >= t.PLAN_WORK_END)
)
</if>
</where>
</select>
<update id="updateTaskPartUser" parameterType="java.util.Map">
update T_B_TASK_PART_USER_${suffix}
set STATUS=#{status,jdbcType=VARCHAR}
where TASK_ID=#{TASK_ID}
</update>
<update id="updateTaskPartUserByState" parameterType="java.util.Map">
update T_B_TASK_PART_USER_${suffix}
set formula_id=null,weight_version_id=null,score=null
where TASK_ID=#{TASK_ID}
</update>
<update id="updateTaskByState" parameterType="java.util.Map">
update T_B_TASK_${suffix}
set task_formula_cn=null,task_formula=null,task_score=null,state='70'
where ID=#{TASK_ID}
</update>
<select id="getTasksByPageOther" parameterType="java.util.Map" resultType="java.util.Map">
<include refid="startPageSql" />
SELECT task.ID,task.TYPE,task.SUB_TYPE,task.USER_ID,task.USER_NAME,task.ORG_ID,task.ORG_NAME,task.ROLE_NAME,
TO_CHAR(task.FINISH_TIME, 'yyyy-MM-dd HH24:mi') FINISH_TIME,task.WORK_TYPE,
task.WORK_CONTENT,task.MARK_REASON,task.POINT,task.DEPART_ID from T_B_OMS_TASK_DETAILS task
<where>
<if test="taskConten != null and taskConten != ''">
AND task.work_content like '%'||#{taskConten}||'%'
</if>
<if test="taskKeyword != null and taskKeyword != ''">
AND (task.work_content like '%'||#{taskKeyword}||'%')
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
task.FINISH_TIME between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="userId != null and userId != ''" >
AND task.USER_ID=#{userId}
</if>
<if test="departId != null and departId != ''">
AND EXISTS (SELECT 1 FROM t_s_depart D
WHERE D.ID = task.depart_id AND D.level_no LIKE
CONCAT((SELECT dp.level_no FROM t_s_depart dp
WHERE dp. ID = #{departId}),'%'))
</if>
<if test="myId != null and myId != ''">
AND EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK. ID = my.TASK_ID and my.user_id =
#{myId} AND my.STATUS = '1')
</if>
<choose>
<when test="orderBy != null and orderBy != ''">
${orderBy}
</when>
</choose>
</where>
<include refid="endPageSql" />
</select>
<select id="countTasksOther" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(task.ID) from T_B_OMS_TASK_DETAILS task
<where>
<if test="taskConten != null and taskConten != ''">
AND task.work_content like '%'||#{taskConten}||'%'
</if>
<if test="taskKeyword != null and taskKeyword != ''">
AND (task.work_content like '%'||#{taskKeyword}||'%')
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
task.FINISH_TIME between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="userId != null and userId != ''" >
AND task.USER_ID=#{userId}
</if>
<if test="departId != null and departId != ''">
AND EXISTS (SELECT 1 FROM t_s_depart D
WHERE D.ID = task.depart_id AND D.level_no LIKE
CONCAT((SELECT dp.level_no FROM t_s_depart dp
WHERE dp. ID = #{departId}),'%'))
</if>
<if test="myId != null and myId != ''">
AND EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK. ID = my.TASK_ID and my.user_id =
#{myId} AND my.STATUS = '1')
</if>
</where>
</select>
<select id="getTaskPartUserSum" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(num) nums from (SELECT COUNT(part.USER_ID) num from T_B_TASK_${suffix} task,T_B_TASK_PART_USER_${suffix} part
<where>
task.id=part.task_id and task.iscancel='0' and part.status='1'
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
</where>
group by part.user_id )
</select>
<select id="getTaskGroupSum" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(num) nums from (SELECT COUNT(task.work_group_id) num from T_B_TASK_${suffix} task
<where>
task.iscancel='0' and task.work_group_id is not null
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
task.ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
</where>
group by task.work_group_id )
</select>
<select id="getOmsTaskDetatilsByCloum" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(num) nums from (SELECT COUNT(task.${cloum}) num from T_B_OMS_TASK_DETAILS task
<where>
task.status='1'
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
task.FINISH_TIME between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
</where>
group by task.${cloum} )
</select>
<select id="qryTaskGroupByDate" parameterType="java.util.Map" resultType="java.util.Map">
SELECT to_char(task.ACTUAL_END, 'yyyy-MM') month, count(task.id) num
FROM (select * from T_B_TASK_${suffix}
<where>
ISCANCEL = 0
<if test="state1 != null and state1 != ''">
AND state =#{state1}
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND ACTUAL_END >= to_date(#{actualStart},'yyyy-MM-dd HH24:mi')
AND ACTUAL_END <= to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
</if>
<if test="levelNo != null">
and exists ( select 1 from T_S_DEPART dp where DP. ID=work_group_id
AND DP.LEVEL_NO LIKE #{levelNo} )
</if>
</where>
) task
LEFT JOIN T_B_TACHE_TYPE TYPE
ON TYPE . ID = task.WORK_TYPE_ID
LEFT JOIN (SELECT tuser.TASK_ID, tuser.USER_NAME, tuser.USER_ID
FROM T_B_TASK_PART_USER_${suffix} tuser
WHERE tuser.STATUS = 1
AND tuser. TYPE = '1') tempuser
ON TASK. ID = tempuser.TASK_ID
group by to_char(task.ACTUAL_END, 'yyyy-MM')
order by to_char(task.ACTUAL_END, 'yyyy-MM')
</select>
<select id="qryDlddTaskGroupByDate" parameterType="java.util.Map" resultType="java.util.Map">
SELECT to_char(task.FINISH_TIME, 'yyyy-MM') month, count(task.id) num from T_B_OMS_TASK_DETAILS task
<where>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND task.FINISH_TIME >= to_date(#{actualStart},'yyyy-MM-dd HH24:mi')
AND task.FINISH_TIME <= to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
</if>
<if test="departId != null and departId != ''">
AND EXISTS (SELECT 1 FROM t_s_depart D
WHERE D.ID = task.depart_id AND D.level_no LIKE
CONCAT((SELECT dp.level_no FROM t_s_depart dp
WHERE dp. ID = #{departId}),'%'))
</if>
</where>
group by to_char(task.FINISH_TIME, 'yyyy-MM')
order by to_char(task.FINISH_TIME, 'yyyy-MM')
</select>
<select id="getTaskWorkGroupSum" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(num) nums from (SELECT COUNT(task.work_group_id) num from T_B_TASK_WORK_${suffix} task
<where>
task.iscancel='0' and task.work_group_id is not null
<if test="state1 != null and state1 != ''">
AND task.state =#{state1}
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
task.ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
</where>
group by task.work_group_id )
</select>
<select id="getTaskWorkPartUserSum" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT COUNT(num) nums from (SELECT COUNT(part.USER_ID) num from T_B_TASK_${suffix} task,T_B_TASK_PART_USER_${suffix} part,
T_B_TASK_WORK_${suffix} work
<where>
work.id=task.parent_task_id and task.id=part.task_id and task.iscancel='0'
and part.status='1' and work.iscancel='0' and part.user_id is not null
<if test="state1 != null and state1 != ''">
AND task.state =#{state1}
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND task.ACTUAL_END >= to_date(#{actualStart},'yyyy-MM-dd HH24:mi')
AND task.ACTUAL_END < to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
</if>
</where>
group by part.user_id )
</select>
<update id="updateTaskStatsTime" parameterType="java.util.Map">
update t_b_task_stats_${suffix}
set time=to_date(#{startDate},'yyyy-mm-dd')
where time=to_date(#{endDate},'yyyy-mm-dd')
</update>
<update id="updateTaskStatsTimeByOther" parameterType="java.util.Map">
update t_b_task_stats_${suffix} set time=add_months(time,-1)
</update>
<select id="qryEveryDayPointByDate" parameterType="java.util.Map" resultType="java.util.Map">
SELECT to_char(point.task_end_time,'yyyy-mm-dd') day,sum(point.user_point) point
FROM t_b_task_${suffix} task, t_b_task_user_point_${suffix} point
<where>
task.id = point.task_id and iscancel='0'
<if test="startDate != null and startDate != '' and endDate != null and endDate != ''">
and point.task_end_time >= to_date(#{startDate}, 'yyyy-mm-dd')
and point.task_end_time < to_date(#{endDate}, 'yyyy-mm-dd')
</if>
<if test="departId != null and departId != ''">
AND EXISTS (SELECT 1 FROM t_s_depart D
WHERE D.ID = point.team_id AND D.level_no LIKE
CONCAT((SELECT dp.level_no FROM t_s_depart dp
WHERE dp. ID = #{departId}),'%'))
</if>
<if test="sysDate !=null and sysDate !=''">
AND NOT EXISTS (SELECT 1 FROM T_S_MONTH_DATE_ROLE x, T_S_USER_PERFORMANCE_ROLE y where x.id =
y.month_date_role_id
and y.user_id=point.user_id and x.exp_date>=to_date(#{sysDate},'yyyy-mm') UNION ALL
SELECT 1 FROM T_S_MONTH_DATE_ROLE m, T_S_USER_PERFORMANCE_ROLE n,T_S_ROLE_USER k where m.id =
n.month_date_role_id
and n.role_id=k.role_id and m.exp_date>=to_date(#{sysDate},'yyyy-mm') and k.user_id=point.user_id)
<!-- AND NOT EXISTS (select 1 from t_s_role x,t_s_role_user y where x.id=y.role_id and x.role_code='noParticipateJXTJ' and y.user_id=t.user_id) -->
</if>
<if test="remarkRole!=null and remarkRole!=''">
AND NOT EXISTS (select 1 from ${user_remark_role} remark where remark.user_id =point.user_id
and remark.time>=to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss') and remark.time <to_date(#{endDate},'yyyy-mm-dd
hh24:mi:ss'))
</if>
AND NOT EXISTS (SELECT 1 FROM t_b_month_staff_vacation ti where ti.group_id=#{departId}
and ti.stat_time >= to_date(#{startDate}, 'yyyy-MM-dd')
and ti.stat_time < to_date(#{endDate}, 'yyyy-MM-dd') and ti.user_id=point.user_id )
</where>
group by to_char(point.task_end_time,'yyyy-mm-dd')
</select>
<select id="getExportTaskDetilData" parameterType="java.util.Map" resultType="java.util.Map">
SELECT task.ID "id",
task.USE_CAR_ID "car",
task.CONTENT "content",
TO_CHAR(task.plan_work_strart, 'MM"月"dd"日"') || '-' ||
TO_CHAR(task.plan_work_end, 'MM"月"dd"日"') "plan_Work_Time",
TO_CHAR(task.PLAN_WORK_STRART, 'yyyy-MM-dd HH24:mi') "PLAN_WORK_STRART",
TO_CHAR(task.PLAN_WORK_END, 'yyyy-MM-dd HH24:mi') "PLAN_WORK_END",
task.use_car_id "user_Car_Id",
task.extend_content "extend_Content",
task.mark_state "mark_State",
task.plan_id "plan_Id",
task.title "title",
task.state "state",
task.TASK_FORMULA,
task.TASK_FORMULA_CN,
TO_CHAR(task.ACTUAL_START, 'yyyy-MM-dd HH24:mi') "actual_Start",
TO_CHAR(task.ACTUAL_END, 'yyyy-MM-dd HH24:mi') "actual_End",
TO_CHAR(task.sync_time, 'yyyy-MM-dd HH24:mi') "sync_time",
task.WORK_GROUP_ID,
task.WORK_GROUP_NAME,
task.IS_PRESERVATION,
task.PLAN_CODE,
task.WORK_PLACE,
p.name task_type_name,
CASE
WHEN task.plan_work_end <= TO_DATE (#{now}, 'yyyy-MM-dd') THEN
1
ELSE
0
END "is_Time_Out",
0 AS "is_Form",
(SELECT tuser.USER_NAME
FROM T_B_TASK_PART_USER_${suffix} tuser
WHERE TASK. ID = tuser.TASK_ID
AND tuser. TYPE = '1') director,
(SELECT listagg(to_char(tuser.USER_NAME),',') within group(order by tuser.USER_NAME) name
FROM T_B_TASK_PART_USER_${suffix} tuser
WHERE TASK. ID = tuser.TASK_ID
AND tuser. TYPE = '2') part_user,
TYPE . NAME "work_Type_Name"
FROM (select * from T_B_TASK_${suffix}
where 1=1
<if test="iscancel != null">
AND ISCANCEL = #{iscancel}
</if>
<if test="state1 != null and state2 != null">
AND (
STATE BETWEEN #{state1}
AND #{state2}
)
</if>
<if test="taskConten != null and taskConten != ''">
AND
content like '%'||#{taskConten}||'%'
</if>
<if test="specialityId != null and specialityId != ''">
AND speciality = #{specialityId}
</if>
<if test="taskKeyword != null and taskKeyword != ''">
AND (
(CONTENT like '%'||#{taskKeyword}||'%') OR
(PLAN_CODE like '%'||#{planCode}||'%')
)
</if>
<if test="actualStart != null and actualStart != '' and actualEnd != null and actualEnd != ''">
AND (
ACTUAL_END between to_date(#{actualStart},'yyyy-MM-dd HH24:mi') AND to_date(#{actualEnd},'yyyy-MM-dd HH24:mi')
)
</if>
<if test="workTypeId != null and workTypeId != ''">
AND WORK_TYPE_ID = #{workTypeId}
</if>
<if test="planCode != null and planCode != ''">
AND <!-- PLAN_CODE = #{planCode} -->
PLAN_CODE like '%'||#{planCode}||'%'
</if>
<if test="state != null">
AND MARK_STATE = #{state}
</if>
<if test="createUserId != null and createUserId != ''">
AND CREATE_ID = #{createUserId}
</if>
<if test="taskTypeId != null and taskTypeId != ''">
AND (type_top_id=#{taskTypeId} or work_type_id=#{taskTypeId} or detail_type_id=#{taskTypeId}
or task_type_id=#{taskTypeId} )
</if>
<if test="levelNo != null">
and exists ( select 1 from T_S_DEPART dp where DP. ID=work_group_id
AND DP.LEVEL_NO LIKE #{levelNo}
</if>
)
order by id
) task
LEFT JOIN T_B_TACHE_TYPE TYPE ON TYPE . ID = task.WORK_TYPE_ID
LEFT JOIN T_B_TACHE_POINT_VERSION_${suffix} p on p.id = task.task_type_id
WHERE
1 = 1
<if test="myId != null and myId != ''">
AND EXISTS (SELECT 1 FROM T_B_TASK_PART_USER_${suffix} my WHERE TASK. ID = my.TASK_ID and my.user_id =
#{myId} AND my.STATUS = '1')
</if>
<if test="userId != null and userId != ''" >
AND EXISTS (select 1 from T_B_TASK_PART_USER_${suffix} p where p.USER_ID = #{userId} and p.task_id = task.id)
</if>
<if test="workPlaceIds != null">
INNER JOIN T_B_WORK_PLACE wp on TASK.ID = WP.TASK_ID AND WP.ISCANCEL = 0 AND wp.SUBSTATION_ID
in
<foreach collection="workPlaceIds" item="id" index="index"
open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<choose>
<when test="orderBy != null and orderBy != ''">
${orderBy}
</when>
</choose>
</select>
</mapper>
2174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
