<?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="com.sunny.dao.duty.DutyMapper" >
<resultMap id="BaseResultMap" type="com.sunny.model.duty.Duty" >
<id column="DUTY_ID" property="dutyId" jdbcType="INTEGER" />
<result column="DSCD" property="dscd" jdbcType="CHAR" />
<result column="UNIT_ID" property="unitId" jdbcType="INTEGER" />
<result column="YEAR" property="year" jdbcType="CHAR" />
<result column="MONTH" property="month" jdbcType="CHAR" />
<result column="DAY" property="day" jdbcType="CHAR" />
<result column="DAY0" property="day0" jdbcType="CHAR" />
<result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" />
<result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" />
<result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" />
<result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" />
<result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" />
<result column="FLAG" property="flag" jdbcType="INTEGER" />
<result column="REMARK" property="remark" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="onHashMap_1" type="java.util.HashMap">
<result column="DSCD" property="dscd" jdbcType="CHAR" />
<result column="UNIT_ID" property="unitId" jdbcType="INTEGER" />
<result column="YEAR" property="year" jdbcType="CHAR" />
<result column="MONTH" property="month" jdbcType="CHAR" />
<result column="FLAG" property="flag" jdbcType="INTEGER" />
<result column="FLAGNAME" property="flagName" jdbcType="VARCHAR" />
<result column="DSNM" property="dsnm" jdbcType="CHAR" />
<result column="UNIT_NAME" property="unitName" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="onHashMap_2" type="java.util.HashMap">
<result column="mName" property="mName" jdbcType="VARCHAR" />
<result column="mContact" property="mContact" jdbcType="VARCHAR" />
<result column="mEmail" property="mEmail" jdbcType="VARCHAR" />
<result column="m2Name" property="m2Name" jdbcType="VARCHAR" />
<result column="m2Contact" property="m2Contact" jdbcType="VARCHAR" />
<result column="m2Email" property="m2Email" jdbcType="VARCHAR" />
<result column="aName" property="aName" jdbcType="VARCHAR" />
<result column="aContact" property="aContact" jdbcType="VARCHAR" />
<result column="aEmail" property="aEmail" jdbcType="VARCHAR" />
<result column="a2Name" property="a2Name" jdbcType="VARCHAR" />
<result column="a2Contact" property="a2Contact" jdbcType="VARCHAR" />
<result column="a2Email" property="a2Email" jdbcType="VARCHAR" />
<result column="eName" property="eName" jdbcType="VARCHAR" />
<result column="eContact" property="eContact" jdbcType="VARCHAR" />
<result column="eEmail" property="eEmail" jdbcType="VARCHAR" />
<result column="e2Name" property="e2Name" jdbcType="VARCHAR" />
<result column="e2Contact" property="e2Contact" jdbcType="VARCHAR" />
<result column="e2Email" property="e2Email" jdbcType="VARCHAR" />
<result column="lName" property="lName" jdbcType="VARCHAR" />
<result column="lContact" property="lContact" jdbcType="VARCHAR" />
<result column="lEmail" property="lEmail" jdbcType="VARCHAR" />
<result column="l2Name" property="l2Name" jdbcType="VARCHAR" />
<result column="l2Contact" property="l2Contact" jdbcType="VARCHAR" />
<result column="l2Email" property="l2Email" jdbcType="VARCHAR" />
<result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" />
<result column="FLAG" property="flag" jdbcType="INTEGER" />
<result column="DAY0" property="day0" jdbcType="CHAR" />
<result column="DAY" property="day" jdbcType="CHAR" />
<result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" />
<result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="onHashMap_3" type="java.util.HashMap">
<result column="RN" property="rn" jdbcType="INTEGER" />
<result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" />
<result column="FLAG" property="flag" jdbcType="INTEGER" />
<result column="FLAG" property="flagName" jdbcType="VARCHAR" />
<result column="DAY0" property="day0" jdbcType="CHAR" />
<result column="DAY" property="day" jdbcType="CHAR" />
<result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" />
<result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="onHashMap_4" type="java.util.HashMap">
<result column="PERSON_NAME" property="personName" jdbcType="VARCHAR" />
<result column="DAY0" property="day0" jdbcType="CHAR" />
<result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" />
<result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" />
<result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" />
<result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" />
<result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" />
<result column="flagName" property="flagName" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="onHashMap_5" type="java.util.HashMap">
<result column="mName" property="mName" jdbcType="VARCHAR" />
<result column="mContact" property="mContact" jdbcType="VARCHAR" />
<result column="mEmail" property="mEmail" jdbcType="VARCHAR" />
<result column="m2Name" property="m2Name" jdbcType="VARCHAR" />
<result column="m2Contact" property="m2Contact" jdbcType="VARCHAR" />
<result column="m2Email" property="m2Email" jdbcType="VARCHAR" />
<result column="aName" property="aName" jdbcType="VARCHAR" />
<result column="aContact" property="aContact" jdbcType="VARCHAR" />
<result column="aEmail" property="aEmail" jdbcType="VARCHAR" />
<result column="a2Name" property="a2Name" jdbcType="VARCHAR" />
<result column="a2Contact" property="a2Contact" jdbcType="VARCHAR" />
<result column="a2Email" property="a2Email" jdbcType="VARCHAR" />
<result column="eName" property="eName" jdbcType="VARCHAR" />
<result column="eContact" property="eContact" jdbcType="VARCHAR" />
<result column="eEmail" property="eEmail" jdbcType="VARCHAR" />
<result column="e2Name" property="e2Name" jdbcType="VARCHAR" />
<result column="e2Contact" property="e2Contact" jdbcType="VARCHAR" />
<result column="e2Email" property="e2Email" jdbcType="VARCHAR" />
<result column="lName" property="lName" jdbcType="VARCHAR" />
<result column="lContact" property="lContact" jdbcType="VARCHAR" />
<result column="lEmail" property="lEmail" jdbcType="VARCHAR" />
<result column="l2Name" property="l2Name" jdbcType="VARCHAR" />
<result column="l2Contact" property="l2Contact" jdbcType="VARCHAR" />
<result column="l2Email" property="l2Email" jdbcType="VARCHAR" />
<result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" />
<result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" />
<result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" />
<result column="FLAG" property="flag" jdbcType="INTEGER" />
<result column="DAY0" property="day0" jdbcType="CHAR" />
<result column="DAY" property="day" jdbcType="CHAR" />
<result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" />
<result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" />
<result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" />
<result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
DUTY_ID,DSCD,UNIT_ID,YEAR,MONTH,DAY,DAY0,WEEK_DAY,MORNING_PEOPLE_IDS,MORNING_PEOPLE_NAMES,AFTERNOON_PEOPLE_IDS,AFTERNOON_PEOPLE_NAMES,EVENING_PEOPLE_IDS,EVENING_PEOPLE_NAMES,LEADER_IDS,LEADER_NAMES,FLAG,REMARK
</sql>
<!-- 按条件查找某天的值班信息 -->
<select id="getTodayDutyInfo" resultMap="onHashMap_5">
select
m1.person_name as mName,m1.contact as mContact,m1.email as mEmail,m2.person_name as m2Name,m2.contact as m2Contact,m2.email as m2Email,a1.person_name as aName,a1.contact as aContact,a1.email as aEmail,a2.person_name as a2Name,a2.contact as a2Contact,a2.email as a2Email,e1.person_name as eName,e1.contact as eContact,e1.email as eEmail,e2.person_name as e2Name,e2.contact as e2Contact,e2.email as e2Email,l1.person_name as lName,l1.contact as lContact,l1.email as lEmail,l2.person_name as l2Name,l2.contact as l2Contact,l2.email as l2Email,t.morning_people_ids,t.afternoon_people_ids,t.evening_people_ids,t.leader_ids,t.week_day,t.flag,case when t.flag=1 then '普通班' when t.flag=2 then '加强班' else '' end flagName,t.day0,t.day,t.morning_people_names,t.afternoon_people_names,t.evening_people_names,t.leader_names
from(
select
substr(a.morning_people_ids,instr(a.morning_people_ids,',1,1)-1) as mId,substr(a.morning_people_ids,1)+1) as m2Id,substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,1)-1) as aId,1)+1) as a2Id,substr(a.evening_people_ids,instr(a.evening_people_ids,1)-1) as eId,1)+1) as e2Id,substr(a.leader_ids,instr(a.leader_ids,1)-1) as lId,1)+1) as l2Id,a.*
from tb_duty a
) t,tb_duty_person_info m1,tb_duty_person_info m2,tb_duty_person_info a1,tb_duty_person_info a2,tb_duty_person_info e1,tb_duty_person_info e2,tb_duty_person_info l1,tb_duty_person_info l2
where 1=1
and
nvl2(t.mId,t.mId,t.m2Id)=m1.person_id
and t.m2Id=m2.person_id
and
nvl2(t.aId,t.aId,t.a2Id)=a1.person_id
and t.a2Id=a2.person_id
and
nvl2(t.eId,t.eId,t.e2Id)=e1.person_id
and t.e2Id=e2.person_id
and
nvl2(t.lId,t.lId,t.l2Id)=l1.person_id
and t.l2Id=l2.person_id
<if test="dscdStr!='' and dscdStr!=null" >
and t.DSCD=#{dscdStr,jdbcType=CHAR}
</if>
<if test="unitIdInt!=null" >
and t.UNIT_ID=#{unitIdInt,jdbcType=INTEGER}
</if>
<if test="day0Str!='' and day0Str!=null" >
and t.DAY0=#{day0Str,jdbcType=CHAR}
</if>
<if test="flagInt != null " >
and t.FLAG=#{flagInt,jdbcType=INTEGER}
</if>
order by t.day0
</select>
<!-- 值班个人查询 -->
<select id="selectPersonalDutyList" resultMap="onHashMap_4">
select b.person_name,a.day0,a.week_day,a.morning_people_ids,a.morning_people_names,a.afternoon_people_ids,a.afternoon_people_names,a.evening_people_ids,a.evening_people_names,a.leader_ids,a.leader_names,case when a.flag=1 then '普通班' when a.flag=2 then '加强班' else '' end flagName
from tb_duty a,tb_duty_person_info b
where 1=1
<if test="dscd !='' and dscd != null " >
and a.dscd=#{dscd,jdbcType=CHAR}
</if>
<if test="unitId != null " >
and a.unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="year !='' and year != null " >
and a.year=#{year,jdbcType=CHAR}
</if>
<if test="month !='' and month != null " >
and a.month=#{month,jdbcType=CHAR}
</if>
and (
substr(a.morning_people_ids,1)-1)=#{personId,jdbcType=CHAR} or
substr(a.morning_people_ids,1)+1)=#{personId,jdbcType=CHAR} or
substr(a.afternoon_people_ids,jdbcType=CHAR} or
substr(a.afternoon_people_ids,jdbcType=CHAR} or
substr(a.evening_people_ids,jdbcType=CHAR} or
substr(a.evening_people_ids,jdbcType=CHAR} or
substr(a.leader_ids,jdbcType=CHAR} or
substr(a.leader_ids,jdbcType=CHAR}
) and b.person_id=#{personId,jdbcType=CHAR}
order by day0 asc
</select>
<!-- 前一个月的后10条值班记录 -->
<select id="selectPreDutyList" resultMap="onHashMap_3">
select s.* from(
select
rownum rn,t.leader_names
from tb_duty t
where 1=1
<if test="dscd !='' and dscd != null " >
and t.dscd=#{dscd,jdbcType=CHAR}
</if>
<if test="unitId != null " >
and t.unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="day0 !='' and day0 != null " >
and to_char(to_date(t.day0,'yyyy-mm-dd'),'yyyy-mm')=#{day0,jdbcType=CHAR}
</if>
<if test="flag != null " >
and t.flag=#{flag,jdbcType=INTEGER}
</if>
order by t.duty_id
) s
<![CDATA[
where s.rn>((
]]>
select count(*) from tb_duty p
where 1=1
<if test="dscd !='' and dscd != null " >
and p.dscd=#{dscd,jdbcType=CHAR}
</if>
<if test="unitId != null " >
and p.unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="day0 !='' and day0 != null " >
and to_char(to_date(p.day0,jdbcType=CHAR}
</if>
<if test="flag != null " >
and p.flag=#{flag,jdbcType=INTEGER}
</if>
)-10)
</select>
<!-- 后一个月的前10条值班记录 -->
<select id="selectNextDutyList" resultMap="onHashMap_3">
select s.* from(
select
rownum rn,jdbcType=INTEGER}
</if>
<![CDATA[
and rownum<11
order by t.duty_id
) s
where s.rn>0
]]>
</select>
<!-- 按条件查找今日值班 -->
<select id="selectTodayDutyList" resultMap="onHashMap_2">
select
m1.person_name as mName,t.leader_names
from(
select
substr(a.morning_people_ids,a.*
from tb_duty a
) t,tb_duty_person_info l2
where 1=1
and
nvl2(t.mId,t.m2Id)=m1.person_id
and t.m2Id=m2.person_id
and
nvl2(t.aId,t.a2Id)=a1.person_id
and t.a2Id=a2.person_id
and
nvl2(t.eId,t.e2Id)=e1.person_id
and t.e2Id=e2.person_id
and
nvl2(t.lId,t.l2Id)=l1.person_id
and t.l2Id=l2.person_id
<if test="dscd !='' and dscd != null " >
and t.dscd=#{dscd,jdbcType=INTEGER}
</if>
order by t.day0
</select>
<select id="selectByUnitIdAndYear" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from TB_DUTY
where 1=1
<if test="unitId != null " >
and unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="year != null " >
and year=#{year,jdbcType=INTEGER}
</if>
</select>
<!-- 根据unitId和year查找记录集 -->
<select id="selectListByConditions" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from TB_DUTY
where 1=1
<if test="dscd!='' and dscd!=null" >
and dscd=${dscd}
</if>
<if test="unitId!=null" >
and unit_id=${unitId}
</if>
<if test="year!='' and year!=null" >
and year=${year}
</if>
<if test="month!='' and month!=null" >
and month=${month}
</if>
<if test="flag!=null" >
and flag=${flag}
</if>
</select>
<!-- 批量新增 -->
<insert id="addMonthDutyIntoDB" parameterType="java.util.List">
insert into TB_DUTY select SEQ_TB_DUTY.nextval,A.* from(
<foreach collection="list" item="item" index="index" separator="union">
SELECT #{item.dscd},#{item.unitId},#{item.year},#{item.month},#{item.day},trim(#{item.weekDay}),#{item.morningPeopleIds},#{item.morningPeopleNames},#{item.afternoonPeopleIds},#{item.afternoonPeopleNames},#{item.eveningPeopleIds},#{item.eveningPeopleNames},#{item.leaderIds},#{item.leaderNames},#{item.flag},#{item.remark},#{item.day0} FROM DUAL
</foreach>
) A
</insert>
<!-- 按条件统计月度排班 -->
<select id="getDutyByCondition" resultMap="onHashMap_1">
select a.dscd,a.unit_id,a.year,a.month,a.flag,case when a.flag=1 then '普通班' when a.flag=2 then '加强班' else '' end flagName,ltrim(rtrim(b.dsnm)) as dsnm,c.unit_name
from tb_duty a
left join tb_code_area_info b
on a.dscd=b.dscd
left join tb_unit_info c
on a.unit_id=c.unit_id
where 1=1
<if test="dscd !='' and dscd != null " >
and a.dscd=#{dscd,jdbcType=CHAR}
</if>
<if test="unitId != null " >
and a.unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="year != null " >
and a.year=#{year,jdbcType=INTEGER}
</if>
<if test="month != null " >
and a.month=#{month,jdbcType=INTEGER}
</if>
<if test="flag != null " >
and a.flag=#{flag,jdbcType=INTEGER}
</if>
group by a.dscd,b.dsnm,c.unit_name,a.flag
order by a.year desc,to_number(a.month) desc
</select>
<!-- 重复保存月度排班检查 -->
<select id="selectRecordExists" resultType="java.lang.Integer" >
select count(*) from tb_duty where 1=1
<if test="dscd!='' and dscd!=null" >
and dscd=${dscd}
</if>
<if test="unitId!=null" >
and unit_id=${unitId}
</if>
<if test="year!='' and year!=null" >
and year=${year}
</if>
<if test="month!='' and month!=null" >
and month=${month}
</if>
<if test="flag!=null" >
and flag=${flag}
</if>
</select>
<!-- 单个删除值班表 -->
<delete id="delById">
delete from tb_duty where 1=1
<if test="dscd !='' and dscd != null " >
and dscd=#{dscd,jdbcType=CHAR}
</if>
<if test="unitId != null " >
and unit_id=#{unitId,jdbcType=INTEGER}
</if>
<if test="year != null " >
and year=#{year,jdbcType=INTEGER}
</if>
<if test="month != null " >
and month=#{month,jdbcType=INTEGER}
</if>
<if test="flag != null " >
and flag=#{flag,jdbcType=INTEGER}
</if>
</delete>
<!-- 批量删除值班表(多条件) -->
<delete id="delMultiByIds2" parameterType="java.util.List">
delete from tb_duty A
where exists
(
select 1 from(
<foreach collection="list" item="item" index="index" separator="union all">
select B.* from tb_duty B where 1=1 and B.dscd=${item.dscd} and B.unit_id=${item.unitId} and
B.year=${item.year} and B.month=${item.month} and B.flag=${item.flag}
</foreach>
)S where A.duty_id=S.duty_id
)
</delete>
<!--批量修改值班表(多条件) -->
<update id="updateByMultiConditions" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close="; end;" separator=";">
update TB_DUTY
<set>
<if test="item.morningPeopleIds != null and item.morningPeopleIds != '' " >
MORNING_PEOPLE_IDS=#{item.morningPeopleIds,jdbcType=VARCHAR},</if>
<if test="item.morningPeopleNames != null and item.morningPeopleNames != '' " >
MORNING_PEOPLE_NAMES=#{item.morningPeopleNames,</if>
<if test="item.afternoonPeopleIds != null and item.afternoonPeopleIds != '' " >
AFTERNOON_PEOPLE_IDS=#{item.afternoonPeopleIds,</if>
<if test="item.afternoonPeopleNames != null and item.afternoonPeopleNames != '' " >
AFTERNOON_PEOPLE_NAMES=#{item.afternoonPeopleNames,</if>
<if test="item.eveningPeopleIds != null and item.eveningPeopleIds != '' " >
EVENING_PEOPLE_IDS=#{item.eveningPeopleIds,</if>
<if test="item.eveningPeopleNames != null and item.eveningPeopleNames != '' " >
EVENING_PEOPLE_NAMES=#{item.eveningPeopleNames,</if>
<if test="item.leaderIds != null and item.leaderIds != '' " >
LEADER_IDS=#{item.leaderIds,</if>
<if test="item.leaderNames != null and item.leaderNames != '' " >
LEADER_NAMES=#{item.leaderNames,</if>
</set>
where DUTY_ID=#{item.dutyId,jdbcType=INTEGER}
</foreach>
</update>
<!-- 以下是自动生成的sql -->
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from TB_DUTY
where DUTY_ID = #{dutyId,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from TB_DUTY
where DUTY_ID = #{dutyId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.sunny.model.duty.Duty" >
insert into TB_DUTY (DUTY_ID,REMARK)
values (SEQ_TB_DUTY.nextval,#{dscd,jdbcType=CHAR},#{unitId,jdbcType=INTEGER},#{year,#{month,#{day,#{day0,#{weekDay,#{morningPeopleIds,#{morningPeopleNames,#{afternoonPeopleIds,#{afternoonPeopleNames,#{eveningPeopleIds,#{eveningPeopleNames,#{leaderIds,#{leaderNames,#{flag,#{remark,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.sunny.model.duty.Duty" >
insert into TB_DUTY
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="dutyId != null" >
DUTY_ID,</if>
<if test="dscd != null" >
DSCD,</if>
<if test="unitId != null" >
UNIT_ID,</if>
<if test="year != null" >
YEAR,</if>
<if test="month != null" >
MONTH,</if>
<if test="day != null" >
DAY,</if>
<if test="day0 != null" >
DAY0,</if>
<if test="weekDay != null" >
WEEK_DAY,</if>
<if test="morningPeopleIds != null" >
MORNING_PEOPLE_IDS,</if>
<if test="morningPeopleNames != null" >
MORNING_PEOPLE_NAMES,</if>
<if test="afternoonPeopleIds != null" >
AFTERNOON_PEOPLE_IDS,</if>
<if test="afternoonPeopleNames != null" >
AFTERNOON_PEOPLE_NAMES,</if>
<if test="eveningPeopleIds != null" >
EVENING_PEOPLE_IDS,</if>
<if test="eveningPeopleNames != null" >
EVENING_PEOPLE_NAMES,</if>
<if test="leaderIds != null" >
LEADER_IDS,</if>
<if test="leaderNames != null" >
LEADER_NAMES,</if>
<if test="flag != null" >
FLAG,</if>
<if test="remark != null" >
REMARK,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="dutyId != null" >
SEQ_TB_DUTY.nextval,</if>
<if test="dscd != null" >
#{dscd,</if>
<if test="unitId != null" >
#{unitId,</if>
<if test="year != null" >
#{year,</if>
<if test="month != null" >
#{month,</if>
<if test="day != null" >
#{day,</if>
<if test="day0 != null" >
#{day0,</if>
<if test="weekDay != null" >
#{weekDay,</if>
<if test="morningPeopleIds != null" >
#{morningPeopleIds,</if>
<if test="morningPeopleNames != null" >
#{morningPeopleNames,</if>
<if test="afternoonPeopleIds != null" >
#{afternoonPeopleIds,</if>
<if test="afternoonPeopleNames != null" >
#{afternoonPeopleNames,</if>
<if test="eveningPeopleIds != null" >
#{eveningPeopleIds,</if>
<if test="eveningPeopleNames != null" >
#{eveningPeopleNames,</if>
<if test="leaderIds != null" >
#{leaderIds,</if>
<if test="leaderNames != null" >
#{leaderNames,</if>
<if test="flag != null" >
#{flag,</if>
<if test="remark != null" >
#{remark,</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.sunny.model.duty.Duty" >
update TB_DUTY
<set >
<if test="dscd != null" >
DSCD = #{dscd,</if>
<if test="unitId != null" >
UNIT_ID = #{unitId,</if>
<if test="year != null" >
YEAR = #{year,</if>
<if test="month != null" >
MONTH = #{month,</if>
<if test="day != null" >
DAY = #{day,</if>
<if test="day0 != null" >
DAY0 = #{day0,</if>
<if test="weekDay != null" >
WEEK_DAY = #{weekDay,</if>
<if test="morningPeopleIds != null" >
MORNING_PEOPLE_IDS = #{morningPeopleIds,</if>
<if test="morningPeopleNames != null" >
MORNING_PEOPLE_NAMES = #{morningPeopleNames,</if>
<if test="afternoonPeopleIds != null" >
AFTERNOON_PEOPLE_IDS = #{afternoonPeopleIds,</if>
<if test="afternoonPeopleNames != null" >
AFTERNOON_PEOPLE_NAMES = #{afternoonPeopleNames,</if>
<if test="eveningPeopleIds != null" >
EVENING_PEOPLE_IDS = #{eveningPeopleIds,</if>
<if test="eveningPeopleNames != null" >
EVENING_PEOPLE_NAMES = #{eveningPeopleNames,</if>
<if test="leaderIds != null" >
LEADER_IDS = #{leaderIds,</if>
<if test="leaderNames != null" >
LEADER_NAMES = #{leaderNames,</if>
<if test="flag != null" >
FLAG = #{flag,</if>
<if test="remark != null" >
REMARK = #{remark,</if>
</set>
where DUTY_ID = #{dutyId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.sunny.model.duty.Duty" >
update TB_DUTY
set DSCD = #{dscd,UNIT_ID = #{unitId,YEAR = #{year,MONTH = #{month,DAY = #{day,DAY0 = #{day0,WEEK_DAY = #{weekDay,MORNING_PEOPLE_IDS = #{morningPeopleIds,MORNING_PEOPLE_NAMES = #{morningPeopleNames,AFTERNOON_PEOPLE_IDS = #{afternoonPeopleIds,AFTERNOON_PEOPLE_NAMES = #{afternoonPeopleNames,EVENING_PEOPLE_IDS = #{eveningPeopleIds,EVENING_PEOPLE_NAMES = #{eveningPeopleNames,LEADER_IDS = #{leaderIds,LEADER_NAMES = #{leaderNames,FLAG = #{flag,REMARK = #{remark,jdbcType=VARCHAR}
where DUTY_ID = #{dutyId,jdbcType=INTEGER}
</update>
</mapper>
Mapper:
package com.sunny.dao.duty;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.sunny.model.duty.Duty;
public interface DutyMapper {
int deleteByPrimaryKey(@Param(value="dutyId")Integer dutyId);
int insert(Duty record);
int insertSelective(Duty record);
Duty selectByPrimaryKey(@Param(value="dutyId")Integer dutyId);
int updateByPrimaryKeySelective(Duty record);
int updateByPrimaryKey(Duty record);
//以下是自定义的
//
List<Duty> selectByUnitIdAndYear(@Param(value="unitId")Integer unitId,@Param(value="year")Integer year);
//
List<Duty> selectListByConditions(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId,@Param(value="year")String year,@Param(value="month")String month,@Param(value="flag")Integer flag);
//
int addMonthDutyIntoDB(@Param(value="list")List<Duty> list);
//
List<Map<String,Object>> getDutyByCondition(@Param(value="dscd")String dscd,@Param(value="year")Integer year,@Param(value="month")Integer month,@Param(value="flag")Integer flag);
//
int delById(@Param(value="dscd")String dscd,@Param(value="flag")Integer flag);
//
int delMultiByIds2(List<Duty> list);
//
int selectRecordExists(@Param(value="dscd")String dscd,@Param(value="flag")Integer flag);
//
int updateByMultiConditions(@Param(value="list")List<Duty> list);
//
List<Map<String,Object>> selectTodayDutyList(@Param(value="dscd")String dscd,@Param(value="day0")String day0,@Param(value="flag")Integer flag);
//
List<Map<String,Object>> getTodayDutyInfo(@Param(value="dscdStr")String dscd,@Param(value="unitIdInt")Integer unitId,@Param(value="day0Str")String day0,@Param(value="flagInt")Integer flag);
//
List<Map<String,Object>> selectPreDutyList(@Param(value="dscd")String dscd,@Param(value="flag")Integer flag);
//
List<Map<String,Object>> selectNextDutyList(@Param(value="dscd")String dscd,Object>> selectPersonalDutyList(@Param(value="dscd")String dscd,@Param(value="personId")String personId);
}