fj-dc/.svn/pristine/d7/d71effe631a3e844b10ec5a3dfe50c2dca901e6d.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.BisInspWateruserInfoDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspWateruserInfo" id="bisInspWateruserInfoResultMap">
        <result property="villageCode" column="VILLAGE_CODE"/>
        <result property="prsnWaterId" column="PRSN_WATER_ID"/>
        <result property="engId" column="ENG_ID"/>
        <result property="villageNm" column="VILLAGE_NM"/>
        <result property="userNm" column="USER_NM"/>
        <result property="telphone" column="TELPHONE"/>
        <result property="cwsCode" column="CWS_CODE"/>
        <result property="cwsName" column="CWS_NAME"/>
        <result property="isQualityGood" column="IS_QUALITY_GOOD"/>
        <result property="isQualityProblem" column="IS_QUALITY_PROBLEM"/>
        <result property="qualityProblem" column="QUALITY_PROBLEM"/>
        <result property="isSplyHome" column="IS_SPLY_HOME"/>
        <result property="is24hSupply" column="IS_24H_SUPPLY"/>
        <result property="isWaterProblem" column="IS_WATER_PROBLEM"/>
        <result property="waterProblem" column="WATER_PROBLEM"/>
        <result property="isCalFee" column="IS_CAL_FEE"/>
        <result property="isFeeProblem" column="IS_FEE_PROBLEM"/>
        <result property="feeProblem" column="FEE_PROBLEM"/>
        <result property="isSplyProblem" column="IS_SPLY_PROBLEM"/>
        <result property="splyProblem" column="SPLY_PROBLEM"/>
        <result property="evaluate" column="EVALUATE"/>
        <result property="visitDate" column="VISIT_DATE"/>
        <result property="recPersId" column="REC_PERS_ID"/>
        <result property="recPers2" column="REC_PERS2"/>
        <result property="recPersTel" column="REC_PERS_TEL"/>
        <result property="createTime" column="CREATE_TIME"/>
        <result property="updateTime" column="UPDATE_TIME"/>
        <result property="status" column="STATUS"/>
        <result property="isHrSupply" column="IS_HR_SUPPLY"/>
        <result property="hrNote" column="HR_NOTE"/>
        <result property="waterFee" column="WATER_FEE"/>
        <result property="otherProblem" column="OTHER_PROBLEM"/>
    </resultMap>

    <sql id="table_columns">
        VILLAGE_CODE ,
        PRSN_WATER_ID ,
        ENG_ID ,
        VILLAGE_NM ,
        USER_NM ,
        TELPHONE ,
        CWS_CODE ,
        CWS_NAME,
        IS_QUALITY_GOOD ,
        IS_QUALITY_PROBLEM ,
        QUALITY_PROBLEM ,
        IS_SPLY_HOME ,
        IS_24H_SUPPLY ,
        IS_WATER_PROBLEM ,
        WATER_PROBLEM ,
        IS_CAL_FEE ,
        IS_FEE_PROBLEM ,
        FEE_PROBLEM ,
        IS_SPLY_PROBLEM ,
        SPLY_PROBLEM ,
        EVALUATE ,
        TO_CHAR(VISIT_DATE,'YYYY-MM-DD') VISIT_DATE,
        REC_PERS_ID ,
        REC_PERS2 ,
        REC_PERS_TEL ,
        TO_CHAR(CREATE_TIME,'YYYY-MM-DD HH24:MI:SS') CREATE_TIME,
        TO_CHAR(UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_TIME,
        STATUS,
        IS_HR_SUPPLY,
        HR_NOTE,
        WATER_FEE,
        OTHER_PROBLEM
    </sql>
    <sql id="table_columns2">
        VILLAGE_CODE ,
        PRSN_WATER_ID ,
        ENG_ID ,
        VILLAGE_NM ,
        USER_NM ,
        TELPHONE ,
        CWS_CODE ,
        CWS_NAME ,
        IS_QUALITY_GOOD ,
        IS_QUALITY_PROBLEM ,
        QUALITY_PROBLEM ,
        IS_SPLY_HOME ,
        IS_24H_SUPPLY ,
        IS_WATER_PROBLEM ,
        WATER_PROBLEM ,
        IS_CAL_FEE ,
        IS_FEE_PROBLEM ,
        FEE_PROBLEM ,
        IS_SPLY_PROBLEM ,
        SPLY_PROBLEM ,
        EVALUATE ,
        VISIT_DATE ,
        REC_PERS_ID ,
        REC_PERS2 ,
        REC_PERS_TEL ,
        CREATE_TIME ,
        UPDATE_TIME ,
        STATUS,
        IS_HR_SUPPLY,
        HR_NOTE,
        WATER_FEE,
        OTHER_PROBLEM
    </sql>
    <sql id="entity_properties">
        #{villageCode},
        #{prsnWaterId},
        #{engId},
        #{villageNm},
        #{userNm},
        #{telphone},
        #{cwsCode},
        #{cwsName},
        #{isQualityGood},
        #{isQualityProblem},
        #{qualityProblem},
        #{isSplyHome},
        #{is24hSupply},
        #{isWaterProblem},
        #{waterProblem},
        #{isCalFee},
        #{isFeeProblem},
        #{feeProblem},
        #{isSplyProblem},
        #{splyProblem},
        #{evaluate},
        TO_DATE(#{visitDate},'YYYY-MM-DD'),
        #{recPersId},
        #{recPers2},
        #{recPersTel},
        TO_DATE(#{createTime},'YYYY-MM-DD HH24:MI:SS') ,
        TO_DATE(#{updateTime},'YYYY-MM-DD HH24:MI:SS') ,
        #{status},
        #{isHrSupply},
        #{hrNote},
        #{waterFee},
        #{otherProblem}
    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="villageCode != null and villageCode != ''">and VILLAGE_CODE = #{villageCode}</if>
            <if test="prsnWaterId != null and prsnWaterId != ''">and PRSN_WATER_ID = #{prsnWaterId}</if>
            <if test="engId != null and engId != ''">and ENG_ID = #{engId}</if>
            <if test="villageNm != null and villageNm != ''">and VILLAGE_NM = #{villageNm}</if>
            <if test="userNm != null and userNm != ''">and USER_NM = #{userNm}</if>
            <if test="telphone != null and telphone != ''">and TELPHONE = #{telphone}</if>
            <if test="cwsCode != null and cwsCode != ''">and CWS_CODE = #{cwsCode}</if>
            <if test="cwsName != null and cwsName != ''">and CWS_NAME LIKE '%'||#{cwsName}||'%'</if>
            <if test="isQualityGood != null and isQualityGood != ''">and IS_QUALITY_GOOD = #{isQualityGood}</if>
            <if test="isQualityProblem != null and isQualityProblem != ''">and IS_QUALITY_PROBLEM =
                #{isQualityProblem}
            </if>
            <if test="qualityProblem != null and qualityProblem != ''">and QUALITY_PROBLEM = #{qualityProblem}</if>
            <if test="isSplyHome != null and isSplyHome != ''">and IS_SPLY_HOME = #{isSplyHome}</if>
            <if test="is24hSupply != null and is24hSupply != ''">and IS_24H_SUPPLY = #{is24hSupply}</if>
            <if test="isWaterProblem != null and isWaterProblem != ''">and IS_WATER_PROBLEM = #{isWaterProblem}</if>
            <if test="waterProblem != null and waterProblem != ''">and WATER_PROBLEM = #{waterProblem}</if>
            <if test="isCalFee != null and isCalFee != ''">and IS_CAL_FEE = #{isCalFee}</if>
            <if test="isFeeProblem != null and isFeeProblem != ''">and IS_FEE_PROBLEM = #{isFeeProblem}</if>
            <if test="feeProblem != null and feeProblem != ''">and FEE_PROBLEM = #{feeProblem}</if>
            <if test="isSplyProblem != null and isSplyProblem != ''">and IS_SPLY_PROBLEM = #{isSplyProblem}</if>
            <if test="splyProblem != null and splyProblem != ''">and SPLY_PROBLEM = #{splyProblem}</if>
            <if test="evaluate != null and evaluate != ''">and EVALUATE = #{evaluate}</if>
            <if test="visitDate != null and visitDate != ''">and VISIT_DATE = TO_DATE(#{visitDate},'YYYY-MM-DD')</if>
            <if test="recPersId != null and recPersId != ''">and REC_PERS_ID = #{recPersId}</if>
            <if test="recPers2 != null and recPers2 != ''">and REC_PERS2 = #{recPers2}</if>
            <if test="recPersTel != null and recPersTel != ''">and REC_PERS_TEL = #{recPersTel}</if>
            <if test="createTime != null and createTime != ''">and CREATE_TIME = TO_DATE(#{createTime},'YYYY-MM-DD
                HH24:MI:SS')
            </if>
            <if test="updateTime != null and updateTime != ''">and UPDATE_TIME = TO_DATE(#{updateTime},'YYYY-MM-DD
                HH24:MI:SS')
            </if>
            <if test="status != null and status != ''">and STATUS = #{status}</if>
            <if test="isHrSupply != null and isHrSupply != ''">and IS_HR_SUPPLY = #{isHrSupply}</if>
            <if test="hrNote != null and hrNote != ''">and HR_NOTE = #{hrNote}</if>
            <if test="waterFee != null">and WATER_FEE = #{waterFee}</if>
        </trim>
    </sql>


    <select id="get" resultMap="bisInspWateruserInfoResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WATERUSER_INFO where PRSN_WATER_ID = #{id}
    </select>

    <select id="getBy" resultMap="bisInspWateruserInfoResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WATERUSER_INFO
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="bisInspWateruserInfoResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WATERUSER_INFO
    </select>

    <select id="findList" resultMap="bisInspWateruserInfoResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WATERUSER_INFO
        <include refid="page_where"/>
        ORDER BY UPDATE_TIME DESC
    </select>

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


    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.BisInspWateruserInfo">
        insert into BIS_INSP_WATERUSER_INFO(
        <include refid="table_columns2"/>
        )
        values (
        <include refid="entity_properties"/>
        )
    </insert>

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

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

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

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspWateruserInfo">
        update BIS_INSP_WATERUSER_INFO
        <trim prefix="set" suffixOverrides=",">
            <if test="villageCode != null and villageCode != ''">VILLAGE_CODE = #{villageCode},</if>
            <if test="engId != null and engId != ''">ENG_ID = #{engId},</if>
            <if test="villageNm != null and villageNm != ''">VILLAGE_NM = #{villageNm},</if>
            <if test="userNm != null and userNm != ''">USER_NM = #{userNm},</if>
            <if test="telphone != null and telphone != ''">TELPHONE = #{telphone},</if>
            <if test="cwsCode != null and cwsCode != ''">CWS_CODE = #{cwsCode},</if>
            <if test="cwsName != null and cwsName != ''">CWS_NAME = #{cwsName},</if>
            <if test="isQualityGood != null and isQualityGood != ''">IS_QUALITY_GOOD = #{isQualityGood},</if>
            <if test="isQualityProblem != null and isQualityProblem != ''">IS_QUALITY_PROBLEM = #{isQualityProblem},
            </if>
            <if test="qualityProblem != null and qualityProblem != ''">QUALITY_PROBLEM = #{qualityProblem},</if>
            <if test="isSplyHome != null and isSplyHome != ''">IS_SPLY_HOME = #{isSplyHome},</if>
            <if test="is24hSupply != null and is24hSupply != ''">IS_24H_SUPPLY = #{is24hSupply},</if>
            <if test="isWaterProblem != null and isWaterProblem != ''">IS_WATER_PROBLEM = #{isWaterProblem},</if>
            <if test="waterProblem != null and waterProblem != ''">WATER_PROBLEM = #{waterProblem},</if>
            <if test="isCalFee != null and isCalFee != ''">IS_CAL_FEE = #{isCalFee},</if>
            <if test="isFeeProblem != null and isFeeProblem != ''">IS_FEE_PROBLEM = #{isFeeProblem},</if>
            <if test="feeProblem != null and feeProblem != ''">FEE_PROBLEM = #{feeProblem},</if>
            <if test="isSplyProblem != null and isSplyProblem != ''">IS_SPLY_PROBLEM = #{isSplyProblem},</if>
            <if test="splyProblem != null and splyProblem != ''">SPLY_PROBLEM = #{splyProblem},</if>
            <if test="evaluate != null and evaluate != ''">EVALUATE = #{evaluate},</if>
            <if test="visitDate != null and visitDate != ''">VISIT_DATE = TO_DATE(#{visitDate},'YYYY-MM-DD'),</if>
            <if test="recPersId != null and recPersId != ''">REC_PERS_ID = #{recPersId},</if>
            <if test="recPers2 != null and recPers2 != ''">REC_PERS2 = #{recPers2},</if>
            <if test="recPersTel != null and recPersTel != ''">REC_PERS_TEL = #{recPersTel},</if>
            <if test="createTime != null and createTime != ''">CREATE_TIME = TO_DATE(#{createTime},'YYYY-MM-DD
                HH24:MI:SS'),
            </if>
            <if test="updateTime != null and updateTime != ''">UPDATE_TIME = TO_DATE(#{updateTime},'YYYY-MM-DD
                HH24:MI:SS'),
            </if>
            <if test="status != null and status != ''">STATUS = #{status},</if>
            <if test="isHrSupply != null and isHrSupply != ''">IS_HR_SUPPLY = #{isHrSupply},</if>
            <if test="hrNote != null and hrNote != ''">HR_NOTE = #{hrNote},</if>
            <if test="waterFee != null">WATER_FEE = #{waterFee},</if>
            <if test="otherProblem != null">OTHER_PROBLEM = #{otherProblem},</if>
        </trim>
        <where>PRSN_WATER_ID = #{prsnWaterId}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspWateruserInfo">
        update BIS_INSP_WATERUSER_INFO
        <trim prefix="set" suffixOverrides=",">
            <if test="villageCode != null and villageCode != ''">VILLAGE_CODE = #{villageCode},</if>
            <if test="engId != null and engId != ''">ENG_ID = #{engId},</if>
            <if test="villageNm != null and villageNm != ''">VILLAGE_NM = #{villageNm},</if>
            <if test="userNm != null and userNm != ''">USER_NM = #{userNm},</if>
            <if test="telphone != null and telphone != ''">TELPHONE = #{telphone},</if>
            <if test="cwsCode != null and cwsCode != ''">CWS_CODE = #{cwsCode},</if>
            <if test="cwsName != null and cwsName != ''">CWS_NAME = #{cwsName},</if>
            <if test="isQualityGood != null and isQualityGood != ''">IS_QUALITY_GOOD = #{isQualityGood},</if>
            <if test="isQualityProblem != null and isQualityProblem != ''">IS_QUALITY_PROBLEM = #{isQualityProblem},
            </if>
            <if test="qualityProblem != null and qualityProblem != ''">QUALITY_PROBLEM = #{qualityProblem},</if>
            <if test="isSplyHome != null and isSplyHome != ''">IS_SPLY_HOME = #{isSplyHome},</if>
            <if test="is24hSupply != null and is24hSupply != ''">IS_24H_SUPPLY = #{is24hSupply},</if>
            <if test="isWaterProblem != null and isWaterProblem != ''">IS_WATER_PROBLEM = #{isWaterProblem},</if>
            <if test="waterProblem != null and waterProblem != ''">WATER_PROBLEM = #{waterProblem},</if>
            <if test="isCalFee != null and isCalFee != ''">IS_CAL_FEE = #{isCalFee},</if>
            <if test="isFeeProblem != null and isFeeProblem != ''">IS_FEE_PROBLEM = #{isFeeProblem},</if>
            <if test="feeProblem != null and feeProblem != ''">FEE_PROBLEM = #{feeProblem},</if>
            <if test="isSplyProblem != null and isSplyProblem != ''">IS_SPLY_PROBLEM = #{isSplyProblem},</if>
            <if test="splyProblem != null and splyProblem != ''">SPLY_PROBLEM = #{splyProblem},</if>
            <if test="evaluate != null and evaluate != ''">EVALUATE = #{evaluate},</if>
            <if test="visitDate != null and visitDate != ''">VISIT_DATE = TO_DATE(#{visitDate},'YYYY-MM-DD'),</if>
            <if test="recPersId != null and recPersId != ''">REC_PERS_ID = #{recPersId},</if>
            <if test="recPers2 != null and recPers2 != ''">REC_PERS2 = #{recPers2},</if>
            <if test="recPersTel != null and recPersTel != ''">REC_PERS_TEL = #{recPersTel},</if>
            <if test="createTime != null and createTime != ''">CREATE_TIME = TO_DATE(#{createTime},'YYYY-MM-DD
                HH24:MI:SS'),
            </if>
            <if test="updateTime != null and updateTime != ''">UPDATE_TIME = TO_DATE(#{updateTime},'YYYY-MM-DD
                HH24:MI:SS'),
            </if>
            <if test="status != null and status != ''">STATUS = #{status},</if>
            <if test="isHrSupply != null and isHrSupply != ''">IS_HR_SUPPLY = #{isHrSupply},</if>
            <if test="hrNote != null and hrNote != ''">HR_NOTE = #{hrNote},</if>
            <if test="waterFee != null">WATER_FEE = #{waterFee},</if>
            <if test="otherProblem != null">OTHER_PROBLEM = #{otherProblem},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->
    <select id="getListByCodeAndPerId" parameterType="cn.com.goldenwater.dcproj.dto.VillRgstrDto"
            resultType="cn.com.goldenwater.dcproj.model.BisInspWateruserInfo">
        SELECT * FROM BIS_INSP_WATERUSER_INFO A LEFT JOIN BIS_INSP_VILL_RGSTR B ON A.ENG_ID = B.ENG_ID
        LEFT JOIN BIS_INSP_ALL_OBJ C ON B.OBJ_ID = C.OBJ_ID
        WHERE
        <!--
        REGEXP_LIKE(
        C.Id,(SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persId})
        )
        -->
        REGEXP_LIKE(
        C.Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId})>0 then
        (SELECT LISTAGG(id, '|')  as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId} group by persid)
        else (select 'non' from dual) end)
        ||')')
        AND C.CODE LIKE '${adCode}%' AND C.PTYPE = '2'
        <if test="adNm != null and adNm != ''">and A.VILLAGE_NM LIKE '%${adNm}%'</if>
    </select>

    <select id="getPageByNodeId" parameterType="cn.com.goldenwater.dcproj.param.GetVillPageByNodeIdParam"
            resultType="cn.com.goldenwater.dcproj.dto.BisInspWateruserInfoDcdxDto">
        select A.VILLAGE_CODE ,
        A.PRSN_WATER_ID ,
        A.ENG_ID ,
        A.VILLAGE_NM ,
        A.USER_NM ,
        A.TELPHONE ,
        A.CWS_CODE ,
        A.CWS_NAME,
        A.IS_QUALITY_GOOD ,
        A.IS_QUALITY_PROBLEM ,
        A.QUALITY_PROBLEM ,
        A.IS_SPLY_HOME ,
        A.IS_24H_SUPPLY ,
        A.IS_WATER_PROBLEM ,
        A.WATER_PROBLEM ,
        A.IS_CAL_FEE ,
        A.IS_FEE_PROBLEM ,
        A.FEE_PROBLEM ,
        A.IS_SPLY_PROBLEM ,
        A.SPLY_PROBLEM ,
        A.EVALUATE ,
        TO_CHAR(A.VISIT_DATE,'YYYY-MM-DD') VISIT_DATE,
        A.REC_PERS_ID ,
        A.REC_PERS2 ,
        A.REC_PERS_TEL ,
        TO_CHAR(A.CREATE_TIME,'YYYY-MM-DD HH24:MI:SS') CREATE_TIME,
        TO_CHAR(A.UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_TIME,
        A.STATUS,
        A.IS_HR_SUPPLY,
        A.HR_NOTE,
        A.WATER_FEE,D.PNM GROUP_NM,F.PNM CHKCOM,H.AD_NAME TOWN,I.AD_NAME COUNTY,J.AD_NAME CITY,K.AD_NAME PROVINCE from
        BIS_INSP_WATERUSER_INFO A
        LEFT JOIN BIS_INSP_VILL_RGSTR B ON A.ENG_ID=B.ENG_ID LEFT JOIN bis_insp_all_obj C ON B.OBJ_ID=C.OBJ_ID
        LEFT JOIN BIS_INSP_ALL D ON C.ID=D.ID LEFT JOIN BIS_INSP_ALL E ON D.PID=E.ID LEFT JOIN BIS_INSP_ALL F ON
        E.PID=F.ID
        LEFT JOIN ATT_AD_X_BASE G ON A.VILLAGE_CODE=G.AD_CODE LEFT JOIN ATT_AD_X_BASE H ON G.AD_FCODE=H.AD_CODE
        LEFT JOIN ATT_AD_X_BASE I ON H.AD_FCODE=I.AD_CODE LEFT JOIN ATT_AD_X_BASE J ON I.AD_FCODE=J.AD_CODE
        LEFT JOIN ATT_AD_X_BASE K ON J.AD_FCODE=K.AD_CODE WHERE 1=1
        <if test="nodeId != null and nodeId != ''">
            and C.ID like #{nodeId}||'%'
        </if>
        <if test="startDate != null and startDate != ''">
            AND A.VISIT_DATE &gt;=TO_DATE(#{startDate},'YYYY-MM-DD')
        </if>
        <if test="endDate != null and endDate != ''">
            AND A.VISIT_DATE &lt;TO_DATE(#{endDate},'YYYY-MM-DD')+1
        </if>
        <if test="chkComId != null and chkComId != ''">
            AND F.ID =#{chkComId}
        </if>
        <if test="groupId != null and groupId != ''">
            AND D.ID =#{groupId}
        </if>
        <if test="adNm != null and adNm != ''">
            AND( A.VILLAGE_NM LIKE '%'||#{adNm}||'%' OR H.AD_NAME LIKE '%'||#{adNm}||'%' OR I.AD_NAME LIKE
            '%'||#{adNm}||'%' OR J.AD_NAME LIKE '%'||#{adNm}||'%' OR K.AD_NAME LIKE '%'||#{adNm}||'%')
        </if>
        ORDER BY A.UPDATE_TIME DESC,A.VISIT_DATE DESC
    </select>
</mapper>