<?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.goldenwater.yjjt.mapper.YjjtSlDataMapper">

    <resultMap type="com.goldenwater.yjjt.domain.YjjtSlData" id="YjjtSlDataResult">
        <id     property="id"         column="id"          />
        <result property="stcd"       column="stcd"        />
        <result property="stnm"       column="stnm"        />
        <result property="rvnm"       column="rvnm"        />
        <result property="spt"        column="spt"         />
        <result property="z"          column="z"           />
        <result property="zJ"         column="z_j"         />
        <result property="q"          column="q"           />
        <result property="liuxiang"   column="liuxiang"    />
        <result property="nt"         column="nt"          />
        <result property="addvcd"     column="addvcd"      />
        <result property="createBy"   column="create_by"   />
        <result property="createTime" column="create_time" />
        <result property="updateBy"   column="update_by"   />
        <result property="updateTime" column="update_time" />
    </resultMap>

    <sql id="selectSlDataVo">
        select id, stcd, stnm, rvnm, spt, z, z_j, q, liuxiang, nt, addvcd,
               create_by, create_time, update_by, update_time
        from gx_yjjt_sl_data
    </sql>

    <select id="selectYjjtSlDataList" parameterType="com.goldenwater.yjjt.domain.YjjtSlData" resultMap="YjjtSlDataResult">
        <include refid="selectSlDataVo"/>
        <where>
            <if test="stcd != null and stcd != ''">
                AND stcd like '%' || #{stcd} || '%'
            </if>
            <if test="stnm != null and stnm != ''">
                AND stnm like '%' || #{stnm} || '%'
            </if>
            <if test="rvnm != null and rvnm != ''">
                AND rvnm like '%' || #{rvnm} || '%'
            </if>
            <if test="addvcd != null and addvcd != ''">
                AND UPPER(addvcd) = UPPER(#{addvcd})
            </if>
            <if test="params.beginTime != null and params.beginTime != ''">
                AND spt &gt;= TO_DATE(#{params.beginTime}, 'YYYY-MM-DD')
            </if>
            <if test="params.endTime != null and params.endTime != ''">
                AND spt &lt;= TO_DATE(#{params.endTime} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
            </if>
        </where>
        order by spt desc, rvnm, stnm
    </select>

    <select id="selectYjjtSlDataById" parameterType="Long" resultMap="YjjtSlDataResult">
        <include refid="selectSlDataVo"/>
        where id = #{id}
    </select>

    <select id="selectDistinctRvnmList" resultType="String">
        select distinct rvnm from gx_yjjt_sl_data where rvnm is not null order by rvnm
    </select>

    <select id="countByStcdAndSpt" resultType="int">
        select count(1) from gx_yjjt_sl_data
        where stcd = #{stcd}
        and TO_CHAR(spt, 'YYYY-MM-DD HH24:MI:SS') = #{spt}
        and UPPER(addvcd) = UPPER(#{addvcd})
    </select>

    <select id="selectCountByDateRange" resultType="java.util.Map">
        select TO_CHAR(d.dt, 'YYYY-MM-DD') as dt,
               NVL(s.sl_count, 0) as sl_count
        from (
            select TRUNC(TO_DATE(#{beginDate}, 'YYYY-MM-DD')) + LEVEL - 1 as dt
            from dual
            CONNECT BY LEVEL &lt;= (TO_DATE(#{endDate}, 'YYYY-MM-DD') - TO_DATE(#{beginDate}, 'YYYY-MM-DD') + 1)
        ) d
        left join (
            select TRUNC(spt) as spt_date, count(1) as sl_count
            from gx_yjjt_sl_data
            where spt &gt;= TO_DATE(#{beginDate}, 'YYYY-MM-DD')
            and spt &lt;= TO_DATE(#{endDate} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
            group by TRUNC(spt)
        ) s on d.dt = s.spt_date
        order by d.dt
    </select>

    <select id="selectTodayWeekMonthCount" resultType="java.util.Map">
        select
            (select count(1) from gx_yjjt_sl_data where TRUNC(spt) = TRUNC(SYSDATE)
             <if test="addvcd != null and addvcd != ''"> and UPPER(addvcd) = UPPER(#{addvcd}) </if>) as today_count,
            (select count(1) from gx_yjjt_sl_data where spt &gt;= TRUNC(SYSDATE, 'IW')
             <if test="addvcd != null and addvcd != ''"> and UPPER(addvcd) = UPPER(#{addvcd}) </if>) as week_count,
            (select count(1) from gx_yjjt_sl_data where spt &gt;= TRUNC(SYSDATE, 'MM')
             <if test="addvcd != null and addvcd != ''"> and UPPER(addvcd) = UPPER(#{addvcd}) </if>) as month_count
        from dual
    </select>

    <insert id="insertYjjtSlData" parameterType="com.goldenwater.yjjt.domain.YjjtSlData" useGeneratedKeys="true" keyProperty="id">
        insert into gx_yjjt_sl_data
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stcd != null and stcd != ''">stcd,</if>
            <if test="stnm != null">stnm,</if>
            <if test="rvnm != null">rvnm,</if>
            <if test="spt != null">spt,</if>
            <if test="z != null">z,</if>
            <if test="zJ != null">z_j,</if>
            <if test="q != null">q,</if>
            <if test="liuxiang != null">liuxiang,</if>
            <if test="nt != null">nt,</if>
            <if test="addvcd != null">addvcd,</if>
            <if test="createBy != null">create_by,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stcd != null and stcd != ''">#{stcd},</if>
            <if test="stnm != null">#{stnm},</if>
            <if test="rvnm != null">#{rvnm},</if>
            <if test="spt != null">#{spt},</if>
            <if test="z != null">#{z},</if>
            <if test="zJ != null">#{zJ},</if>
            <if test="q != null">#{q},</if>
            <if test="liuxiang != null">#{liuxiang},</if>
            <if test="nt != null">#{nt},</if>
            <if test="addvcd != null">#{addvcd},</if>
            <if test="createBy != null">#{createBy},</if>
            SYSDATE
        </trim>
    </insert>

    <update id="updateYjjtSlData" parameterType="com.goldenwater.yjjt.domain.YjjtSlData">
        update gx_yjjt_sl_data
        <set>
            <if test="z != null">z = #{z},</if>
            <if test="zJ != null">z_j = #{zJ},</if>
            <if test="q != null">q = #{q},</if>
            <if test="liuxiang != null">liuxiang = #{liuxiang},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            update_time = SYSDATE
        </set>
        where stcd = #{stcd} and spt = #{spt} and UPPER(addvcd) = UPPER(#{addvcd})
    </update>

    <delete id="deleteYjjtSlDataById" parameterType="Long">
        delete from gx_yjjt_sl_data where id = #{id}
    </delete>

    <delete id="deleteYjjtSlDataByIds" parameterType="Long">
        delete from gx_yjjt_sl_data where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

</mapper>
