<?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.qianhe.zqcz.pftb.mapper.ZqczPftbMapper">
    <!--  条件查询  -->
    <select id="list" parameterType="com.qianhe.zqcz.pftb.domain.ZqczPftbQuery" resultType="map">
        SELECT
            lsjl.xmryid,
            lsjl.zsrwid,
            lsjl.jcrwid,
            lsjl.xmmc,
            lsjl.xmlb,
            lsjl.rymc,
            lsjl.dwmc,
            lsjl.jcdwid,
            jcdw.jcdwmc,
            lsjl.CREATE_TIME AS update_time,
            IFNULL(lsjl.remark, '') AS remark,
            IFNULL(ROUND(pftb.pjdf, 5), '') AS pjdf,
            <foreach item="item" collection="pwxxList" separator=",">
                IFNULL(pftb.`${item.pwmc}`, '') AS `${item.pwmc}`
            </foreach>
        FROM
            zqcz_xm_ry_lsjl lsjl
            LEFT JOIN zqcz_jcdw jcdw ON lsjl.jcdwid = jcdw.jcdwid
            LEFT JOIN (
            SELECT
            pftb.zsrwid,
            pftb.jcrwid,
            pftb.xmryid,
            AVG(pftb.khdf) AS pjdf,
            <foreach item="item" collection="pwxxList" separator=",">
                MAX( CASE WHEN pwxx.pwid = #{item.pwid} THEN pftb.khdf END ) AS `${item.pwmc}`
            </foreach>
            FROM
                zqcz_pftb pftb LEFT JOIN zqcz_pwxx pwxx ON pftb.pwid = pwxx.pwid
            GROUP BY
                pftb.zsrwid,
                pftb.jcrwid,
                pftb.xmryid
            ) pftb ON lsjl.XMRYID = pftb.xmryid
        <where>
            <if test="zsrwid != null ">AND lsjl.zsrwid = #{zsrwid}</if>
            <if test="jcrwid != null ">AND lsjl.jcrwid = #{jcrwid}</if>
        </where>
        ORDER BY lsjl.xmlb
    </select>
    <!--  添加|修改  -->
    <insert id="add" parameterType="com.qianhe.zqcz.pftb.domain.ZqczPftb">
        REPLACE INTO zqcz_pftb (XMRYID, PWID, ZSRWID, JCRWID, KHDF, CREATE_BY, CREATE_TIME, UPDATE_BY, REMARK, YL1, YL2, YL3, YL4, YL5, UPDATE_TIME)
        VALUES (#{xmryid}, #{pwid}, #{zsrwid}, #{jcrwid}, #{khdf}, #{create_by}, #{create_time}, #{update_by}, #{remark}, #{yl1}, #{yl2}, #{yl3}, #{yl4}, #{yl5}, now())
    </insert>
    <!--  删除  -->
    <delete id="deleteByJcrwid" parameterType="long">
        DELETE FROM zqcz_pftb WHERE JCRWID = #{jcrwid}
    </delete>
    <!--  批量新增修改  -->
    <insert id="batchAdd" parameterType="java.util.List">
        REPLACE INTO zqcz_pftb (
            XMRYID, PWID, ZSRWID, JCRWID, KHDF,
            CREATE_BY, CREATE_TIME, UPDATE_BY, REMARK,
            YL1, YL2, YL3, YL4, YL5, UPDATE_TIME
        )
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.xmryid}, #{item.pwid}, #{item.zsrwid}, #{item.jcrwid}, #{item.khdf},
            #{item.create_by}, #{item.create_time}, #{item.update_by}, #{item.remark},
            #{item.yl1}, #{item.yl2}, #{item.yl3}, #{item.yl4}, #{item.yl5}, now())
        </foreach>
    </insert>
</mapper>