fj-dc/.svn/pristine/f7/f7df1e7dcd7f06953e16e283396f4b5eedb2ab26.svn-base

<?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.goldenwater.dcproj.dao.TacInspYearBatchGroupPersDao">

	<resultMap type="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers" id="tacInspYearBatchGroupPersResultMap">
		<result property="inTm" column="IN_TM"/>
		<result property="upTm" column="UP_TM"/>
		<result property="id" column="ID"/>
		<result property="yearBatchId" column="YEAR_BATCH_ID"/>
		<result property="year" column="YEAR"/>
		<result property="batch" column="BATCH"/>
		<result property="persId" column="PERS_ID"/>
		<result property="groupId" column="GROUP_ID"/>
		<result property="groupNm" column="GROUP_NM"/>
		<result property="roleType" column="ROLE_TYPE"/>
		<result property="isNotice" column="IS_NOTICE"/>
		<result property="noticeInfo" column="NOTICE_INFO"/>
		<result property="isSms" column="IS_SMS"/>
		<result property="dataStat" column="DATA_STAT"/>
		<result property="ctPersId" column="CT_PERS_ID"/>
		<result property="isJoin" column="IS_JOIN"/>
		<result property="sendsmsTm" column="SENDSMS_TM" jdbcType="TIMESTAMP"/>
		<result property="phone" column="PHONE"/>
	</resultMap>

	<sql id="table_columns">
		IN_TM,
		UP_TM,
		ID,
		YEAR_BATCH_ID,
		"YEAR",
		BATCH,
		PERS_ID,
		GROUP_ID,
		GROUP_NM,
		ROLE_TYPE,
		IS_NOTICE,
		NOTICE_INFO,
		IS_SMS,
		DATA_STAT,
		CT_PERS_ID,
		IS_JOIN,
        SENDSMS_TM,
        PHONE
	</sql>
	<sql id="entity_properties">
		#{inTm},
		#{upTm},
		#{id},
		#{yearBatchId},
		#{year},
		#{batch},
		#{persId},
		#{groupId},
		#{groupNm},
		#{roleType},
		#{isNotice},
		#{noticeInfo},
		#{isSms},
		#{dataStat},
		#{ctPersId},
		#{isJoin},
		#{sendsmsTm},
		#{phone}
	</sql>

	<!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
	<sql id="page_where">
		<trim prefix="where" prefixOverrides="and | or ">
			<if test="upTm != null">and UP_TM = #{upTm}</if>
			<if test="id != null and id != ''">and ID = #{id}</if>
			<if test="yearBatchId != null and yearBatchId != ''">and YEAR_BATCH_ID = #{yearBatchId}</if>
			<if test="year != null and year != ''">and YEAR = #{year}</if>
			<if test="batch != null and batch != ''">and BATCH = #{batch}</if>
			<if test="persId != null and persId != ''">and PERS_ID = #{persId}</if>
			<if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
			<if test="groupNm != null and groupNm != ''">and GROUP_NM = #{groupNm}</if>
			<if test="roleType != null and roleType != ''">and ROLE_TYPE = #{roleType}</if>
			<if test="isNotice != null and isNotice != ''">and IS_NOTICE = #{isNotice}</if>
			<if test="noticeInfo != null and noticeInfo != ''">and NOTICE_INFO = #{noticeInfo}</if>
			<if test="isSms != null and isSms != ''">and IS_SMS = #{isSms}</if>
			<if test="dataStat != null and dataStat != ''">and DATA_STAT = #{dataStat}</if>
			<if test="ctPersId != null and ctPersId != ''">and CT_PERS_ID = #{ctPersId}</if>
			<if test="isJoin != null and isJoin != ''">and IS_JOIN = #{isJoin}</if>
			<if test="phone != null and phone != ''">and PHONE = #{phone}</if>
			<if test="sttm != null and sttm != ''">and SENDSMS_TM &gt;= TO_DATE(#{sttm},'yyyy-mm-dd hh24:mi:ss')</if>
			<if test="entm != null and entm != ''">and SENDSMS_TM &lt;= To_DATE(#{entm},'yyyy-mm-dd hh24:mi:ss')</if>
		</trim>
	</sql>


	<select id="get" resultMap="tacInspYearBatchGroupPersResultMap" parameterType="String">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS where ID = #{id}
	</select>

	<select id="getBy" resultMap="tacInspYearBatchGroupPersResultMap">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<include refid="page_where"/>
	</select>

	<select id="findAll" resultMap="tacInspYearBatchGroupPersResultMap">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS
	</select>

	<select id="findList" resultMap="tacInspYearBatchGroupPersResultMap">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<include refid="page_where"/>
	</select>

	<select id="selectCount" resultType="int">
		select count(ID) from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<include refid="page_where"/>
	</select>


	<insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers">
		insert into TAC_INSP_YEAR_BATCH_GROUP_PERS(
		<include refid="table_columns"/>
		)
		values (
		<include refid="entity_properties"/>
		)
	</insert>


	<delete id="delete" parameterType="java.lang.String">
		delete from TAC_INSP_YEAR_BATCH_GROUP_PERS where ID = #{id}
	</delete>

	<delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers">
		delete from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<include refid="page_where"/>
	</delete>

	<update id="deleteInFlag" parameterType="java.lang.String">
		update TAC_INSP_YEAR_BATCH_GROUP_PERS set flag_valid = 0 where ID = #{id}
	</update>

	<update id="update" parameterType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers">
		update TAC_INSP_YEAR_BATCH_GROUP_PERS
		<trim prefix="set" suffixOverrides=",">
			<if test="upTm != null">UP_TM = #{upTm},</if>
			<if test="id != null and id != ''">ID = #{id},</if>
			<if test="yearBatchId != null and yearBatchId != ''">YEAR_BATCH_ID = #{yearBatchId},</if>
			<if test="year != null and year != ''">"YEAR" = #{year},</if>
			<if test="batch != null and batch != ''">BATCH = #{batch},</if>
			<if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
			GROUP_ID = #{groupId},
			GROUP_NM = #{groupNm},
			<if test="roleType != null and roleType != ''">ROLE_TYPE = #{roleType},</if>
			<if test="isNotice != null and isNotice != ''">IS_NOTICE = #{isNotice},</if>
			<if test="noticeInfo != null and noticeInfo != ''">NOTICE_INFO = #{noticeInfo},</if>
			<if test="isSms != null and isSms != ''">IS_SMS = #{isSms},</if>
			<if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
			<if test="ctPersId != null and ctPersId != ''">CT_PERS_ID = #{ctPersId},</if>
			<if test="isJoin != null and isJoin != ''">IS_JOIN = #{isJoin},</if>
			SENDSMS_TM = #{sendsmsTm},
			<if test="phone != null and phone != ''">PHONE = #{phone},</if>
		</trim>
		<where>ID = #{id}</where>
	</update>

	<update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers">
		update TAC_INSP_YEAR_BATCH_GROUP_PERS
		<trim prefix="set" suffixOverrides=",">
			<if test="upTm != null">UP_TM = #{upTm},</if>
			<if test="id != null and id != ''">ID = #{id},</if>
			<if test="yearBatchId != null and yearBatchId != ''">YEAR_BATCH_ID = #{yearBatchId},</if>
			<if test="year != null and year != ''">"YEAR" = #{year},</if>
			<if test="batch != null and batch != ''">BATCH = #{batch},</if>
			<if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
			GROUP_ID = #{groupId},
			GROUP_NM = #{groupNm},
			<if test="roleType != null and roleType != ''">ROLE_TYPE = #{roleType},</if>
			<if test="isNotice != null and isNotice != ''">IS_NOTICE = #{isNotice},</if>
			<if test="noticeInfo != null and noticeInfo != ''">NOTICE_INFO = #{noticeInfo},</if>
			<if test="isSms != null and isSms != ''">IS_SMS = #{isSms},</if>
			<if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
			<if test="ctPersId != null and ctPersId != ''">CT_PERS_ID = #{ctPersId},</if>
			<if test="isJoin != null and isJoin != ''">IS_JOIN = #{isJoin},</if>
			<if test="sendsmsTm != null">SENDSMS_TM = #{sendsmsTm},</if>
			<if test="phone != null and phone != ''">PHONE = #{phone},</if>
		</trim>
		<include refid="page_where"/>
	</update>
	<sql id="choseSql">
		<choose>
			<when test="province !=null and province !=''">
				and t.province like concat(#{province},'%')
			</when>
			<otherwise>
				and t.province is null
			</otherwise>
		</choose>
	</sql>

	<!-- 其他自定义SQL -->
	<update id="deleteGroupPersByGroupId"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		update TAC_INSP_YEAR_BATCH_GROUP_PERS
		set GROUP_NM = '',GROUP_ID = '', IS_NOTICE= '0' ,IS_SMS='0',IS_JOIN='0',SENDSMS_TM = null,NOTICE_INFO='',PHONE='',UP_TM=sysdate where GROUP_ID = #{groupId}
		<if test="roleType != null and roleType != ''">
			and role_type = #{roleType}
		</if>
	</update>

	<select id="findPersList" parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam"
			resultType="cn.com.goldenwater.dcproj.model.BisInspAllRlation">
		select #{persId} persid,t.group_id as id,t.ROLE_TYPE as pertype from Tac_Insp_Year_Batch_Group_Pers t
		where 1=1
		<if test="groupId != null and groupId != ''">
			and t.group_id = #{groupId}
		</if>
		and t.PERS_ID in (select id from TAC_WORKER_B t where t.login_id = #{persId}
		<include refid="choseSql"/>
		)
	</select>

	<select id="getYearList" resultType="cn.com.goldenwater.dcproj.dto.BisInspPlanDto"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		select DISTINCT YEAR,'008'||YEAR as plnaId,year||'年' as prsnTitle from Tac_Insp_Year_Batch_Group_Pers
		where 1=1
		<if test="persId != null and persId != ''">
			and group_id in (
			select t.group_id from TAC_INSP_YEAR_BATCH_GROUP_PERS t WHERE PERS_ID IN (select t.id from TAC_WORKER_B t
			where t.login_id = #{persId}
			<include refid="choseSql"/>
			)
			)
		</if>
		order by year
	</select>

	<select id="getBatchList" resultType="cn.com.goldenwater.dcproj.dto.BisInspPlanDto"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		select DISTINCT batch,'008'||year || batch as plnaId,'第'|| batch ||'批次' as prsnTitle from
		Tac_Insp_Year_Batch_Group_Pers
		where 1=1
		<if test="persId != null and persId != ''">
			and group_id in (
			select t.group_id from TAC_INSP_YEAR_BATCH_GROUP_PERS t WHERE PERS_ID in (select t.id from TAC_WORKER_B t
			where t.login_id = #{persId}
			<include refid="choseSql"/>
			)
			)
		</if>
		<if test="year != null and year != ''">
			and year = #{year}
		</if>
		order by batch desc
	</select>

	<select id="getGroupPersList" resultType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		select b.*,t.name as persName from Tac_Insp_Year_Batch_Group_Pers b left join tac_worker_b t on b.pers_id = t.id
		where t.id is not null
		<include refid="choseSql"/>
		<if test="yearBatchId != null and yearBatchId != ''">
			and b.year_batch_id = #{yearBatchId}
		</if>
		<if test="roleType != null and roleType != ''">
			and b.roleType = #{roleType}
		</if>
	</select>
	<select id="getSmsWorkListByGroupId" resultType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupParam">
		SELECT p.id,p.YEAR, p.BATCH, p.PERS_ID, GROUP_ID, GROUP_NM
		, p.ROLE_TYPE, IS_NOTICE, NOTICE_INFO, IS_SMS, NAME as persName
		, MOBILENUMB as phone from TAC_INSP_YEAR_BATCH_GROUP_PERS p left join TAC_WORKER_B t on t.id = p.pers_id
		left join tac_insp_year_batch h on p.year_batch_id=h.id
		where h.data_stat='0'
		<include refid="choseSql"/>
		<if test="id != null and id != ''">
			and p.GROUP_ID = #{id}
		</if>
		and IS_NOTICE = '0' and IS_SMS = '0' and IS_JOIN = '0'
	</select>

	<select id="confirmerGroupPers" resultType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<trim prefix="where" prefixOverrides="and | or ">
			<if test="isNotice != null and isNotice != ''">and IS_NOTICE = #{isNotice}</if>
			<if test="isSms != null and isSms != ''">and IS_SMS = #{isSms}</if>
			<if test="isJoin != null and isJoin != ''">and IS_JOIN = #{isJoin}</if>
			<if test="entm != null and entm != ''">and (sysdate-SENDSMS_TM)*24 &lt;= #{entm}</if>
		</trim>
	</select>

	<select id="getNoReply" resultType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers"
			parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchGroupPersParam">
		select
		<include refid="table_columns"/>
		from TAC_INSP_YEAR_BATCH_GROUP_PERS
		<trim prefix="where" prefixOverrides="and | or ">
			<if test="isNotice != null and isNotice != ''">and IS_NOTICE = #{isNotice}</if>
			<if test="isSms != null and isSms != ''">and IS_SMS = #{isSms}</if>
			<if test="isJoin != null and isJoin != ''">and IS_JOIN = #{isJoin}</if>
			<if test="entm != null and entm != ''">and (sysdate-SENDSMS_TM)*24 &gt;= #{entm}</if>
		</trim>
	</select>

	<!-- 其他自定义SQL -->
	<update id="deleteGroupPersInfo"
			parameterType="cn.com.goldenwater.dcproj.model.TacInspYearBatchGroupPers">
		update TAC_INSP_YEAR_BATCH_GROUP_PERS
		set GROUP_NM = '',GROUP_ID = '', IS_NOTICE= '0' ,IS_SMS='0',IS_JOIN='0',SENDSMS_TM = null,NOTICE_INFO='',UPTM=sysdate where GROUP_ID = #{groupId}
		<if test="persId != null and persId != ''">
			and PERS_ID = #{persId}
		</if>
	</update>
</mapper>