我用的是mybatis，<?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.smics.apps.iw.gv.mapper.ohsapp.IwAppGvVisitInfoMapper">
<sql id="allField">
SELECT v.ID as "id",
v.CASE_NO as "caseNo",
v.VISIT_TYPE as "visitType",
v.VISIT_TOPIC as "visitTopic",
v.VISIT_COMPANY as "visitCompany",
v.VISIT_TIME as "visitTime",
v.CONTACT as "contact",
v.GREETER as "greeter",
v.CREATE_DATE as "createDate",
v.UPDATE_TIME as "updateTime",
v.UPDATE_BY as "updateBy",
v.STATUS as "status",
v.CREATE_BY as "createBy",
v.SUBMIT_BY as "submitBy",
v.SUBMIT_TIME as "submitTime",
v.CHANGE_STEP_TIME as "changeStepTime",
v.GATHER_LOC as "gatherLoc",
v.INSPECTION_LOC as "inspectionLoc",
v.DETAIL_DESC as "detailDesc",
v.INLINE_INSPECTION as "inlineInspection",
v.OFFLINE_INSPECTION as "offlineInspection",
v.ATTENDEE as "attendee",
v.INVOLVED_GROUP as "involvedGroup",
v.PHONE as "phone"
</sql>
<select id="getVisitInfoList" resultType="com.smics.apps.iw.gv.entity.IwAppGvVisitInfo">
<include refid="allField"></include>
FROM IW_APP_GV_VISIT_INFO v
<where>
<if test="cm.caseNo != null and cm.caseNo != ''">
and v.CASE_NO like '%${cm.caseNo}%'
</if>
<if test="cm.loginEmpNo != null and cm.loginEmpNo != ''">
and (select count(*)
from IW_APP_GV_SIGN_STATUS s
where s.FORM_ID = v.ID
and s.EMP_NO = #{cm.loginEmpNo})> 0
</if>
<if test="cm.statusList != null">
and v.STATUS in (
<foreach collection="cm.statusList" item="item" separator=",">
#{item}
</foreach>
)
</if>
</where>
order by v.CREATE_DATE desc
</select>
<select id="getVisitInfoById" resultType="com.smics.apps.iw.gv.entity.IwAppGvVisitInfo">
<include refid="allField"></include>
FROM IW_APP_GV_VISIT_INFO v
where v.ID = #{id}
order by v.CREATE_DATE desc
</select>
<select id="getDistinctVisitCompanyList" resultType="java.lang.String">
select distinct VISIT_COMPANY
FROM IW_APP_GV_VISIT_INFO
</select>
<select id="getCaseNoList" resultType="java.lang.String">
select CASE_NO
FROM IW_APP_GV_VISIT_INFO
where CASE_NO like '${caseNoPre}%'
order by CASE_NO desc
</select>
<insert id="insert" parameterType="com.smics.apps.iw.gv.entity.IwAppGvVisitInfo">
<selectKey keyProperty="cm.id" resultType="long" order="BEFORE">
SELECT hibernate_sequence.nextval FROM dual
</selectKey>
INSERT INTO IW_APP_GV_VISIT_INFO
(ID, CASE_NO, VISIT_TYPE, VISIT_TOPIC, VISIT_COMPANY, VISIT_TIME, CONTACT,
GREETER, CREATE_DATE, UPDATE_TIME,
UPDATE_BY,STATUS,CREATE_BY,SUBMIT_BY,SUBMIT_TIME,CHANGE_STEP_TIME,GATHER_LOC,INSPECTION_LOC,
DETAIL_DESC,
INLINE_INSPECTION,
OFFLINE_INSPECTION,
ATTENDEE,INVOLVED_GROUP,PHONE)
VALUES (#{cm.id,jdbcType=NUMERIC}, #{cm.caseNo,jdbcType=VARCHAR}, #{cm.visitType,jdbcType=VARCHAR},
#{cm.visitTopic,jdbcType=VARCHAR},#{cm.visitCompany,jdbcType=VARCHAR},#{cm.visitTime,jdbcType=VARCHAR},
#{cm.contact,jdbcType=VARCHAR}, #{cm.greeter,jdbcType=VARCHAR}, #{cm.createDate,jdbcType=DATE},
#{cm.updateTime,jdbcType=DATE}, #{cm.updateBy,jdbcType=VARCHAR},#{cm.status,jdbcType=VARCHAR},
#{cm.createBy,jdbcType=VARCHAR},#{cm.submitBy,jdbcType=VARCHAR},#{cm.submitTime,jdbcType=DATE},
#{cm.changeStepTime,jdbcType=DATE},#{cm.gatherLoc,jdbcType=VARCHAR},#{cm.inspectionLoc,jdbcType=VARCHAR},#{cm.detailDesc,jdbcType=VARCHAR},
#{cm.inlineInspection,jdbcType=VARCHAR},#{cm.offlineInspection,jdbcType=VARCHAR},#{cm.attendee,jdbcType=VARCHAR},
#{cm.involvedGroup,jdbcType=VARCHAR},#{cm.phone,jdbcType=VARCHAR})
</insert>
<update id="updateById">
UPDATE IW_APP_GV_VISIT_INFO
SET CASE_NO = #{cm.caseNo,jdbcType=VARCHAR}
, VISIT_TYPE= #{cm.visitType,jdbcType=VARCHAR}
, VISIT_TOPIC= #{cm.visitTopic,jdbcType=VARCHAR}
, VISIT_COMPANY= #{cm.visitCompany,jdbcType=VARCHAR}
, CONTACT=#{cm.contact,jdbcType=VARCHAR}
, UPDATE_TIME=#{cm.updateTime,jdbcType=DATE}
, UPDATE_BY= #{cm.updateBy,jdbcType=VARCHAR}
, STATUS= #{cm.status,jdbcType=VARCHAR}
, SUBMIT_BY = #{cm.submitBy,jdbcType=VARCHAR}
, SUBMIT_TIME = #{cm.submitTime,jdbcType=DATE}
, CHANGE_STEP_TIME = #{cm.changeStepTime,jdbcType=DATE}
, GATHER_LOC = #{cm.gatherLoc,jdbcType=VARCHAR}
, VISIT_TIME = #{cm.visitTime,jdbcType=VARCHAR}
, INSPECTION_LOC = #{cm.inspectionLoc,jdbcType=VARCHAR}
, DETAIL_DESC = #{cm.detailDesc,jdbcType=VARCHAR}
, INLINE_INSPECTION = #{cm.inlineInspection,jdbcType=VARCHAR}
, OFFLINE_INSPECTION = #{cm.offlineInspection,jdbcType=VARCHAR}
, ATTENDEE = #{cm.attendee,jdbcType=VARCHAR}
, INVOLVED_GROUP = #{cm.involvedGroup,jdbcType=VARCHAR}
, PHONE = #{cm.phone,jdbcType=VARCHAR}
where ID = #{cm.id,jdbcType=NUMERIC}
</update>
<update id="ignoreUpdateById">
UPDATE IW_APP_GV_VISIT_INFO
SET VISIT_TYPE= COALESCE(#{cm.visitType,jdbcType=VARCHAR}, VISIT_TYPE),
VISIT_TOPIC= COALESCE(#{cm.visitTopic,jdbcType=VARCHAR}, VISIT_TOPIC),
VISIT_COMPANY= COALESCE(#{cm.visitCompany,jdbcType=VARCHAR}, VISIT_COMPANY),
VISIT_TIME=COALESCE(#{cm.visitTime,jdbcType=VARCHAR}, VISIT_TIME),
CONTACT=COALESCE(#{cm.contact,jdbcType=VARCHAR}, CONTACT),
GREETER=#{cm.greeter,jdbcType=VARCHAR},
CREATE_DATE=COALESCE(#{cm.createDate,jdbcType=DATE}, CREATE_DATE),
UPDATE_TIME=COALESCE(#{cm.updateTime,jdbcType=DATE}, UPDATE_TIME),
UPDATE_BY= COALESCE(#{cm.updateBy,jdbcType=VARCHAR}, UPDATE_BY),
STATUS= COALESCE(#{cm.status,jdbcType=VARCHAR}, STATUS),
SUBMIT_BY = COALESCE(#{cm.submitBy,jdbcType=VARCHAR}, SUBMIT_BY),
SUBMIT_TIME =COALESCE(#{cm.submitTime,jdbcType=DATE}, SUBMIT_TIME),
CHANGE_STEP_TIME = COALESCE(#{cm.changeStepTime,jdbcType=DATE}, CHANGE_STEP_TIME),
GATHER_LOC = COALESCE(#{cm.gatherLoc,jdbcType=VARCHAR}, GATHER_LOC),
INSPECTION_LOC= COALESCE(#{cm.inspectionLoc,jdbcType=VARCHAR}, INSPECTION_LOC),
DETAIL_DESC= COALESCE(#{cm.detailDesc,jdbcType=VARCHAR}, DETAIL_DESC),
INLINE_INSPECTION= COALESCE(#{cm.inlineInspection,jdbcType=VARCHAR}, INLINE_INSPECTION),
OFFLINE_INSPECTION= COALESCE(#{cm.offlineInspection,jdbcType=VARCHAR}, OFFLINE_INSPECTION),
ATTENDEE= COALESCE(#{cm.attendee,jdbcType=VARCHAR}, ATTENDEE),
INVOLVED_GROUP = COALESCE(#{cm.involvedGroup,jdbcType=VARCHAR}, INVOLVED_GROUP),
PHONE = COALESCE(#{cm.phone,jdbcType=VARCHAR}, PHONE)
where ID = #{cm.id,jdbcType=NUMERIC}
</update>
<delete id="deleteById">
delete
from IW_APP_GV_VISIT_INFO
where ID = #{id}
</delete>
</mapper>
最新发布
扫一扫
5209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
