<?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.zjrs.zwnw.zj.entity.QsbsMapper">

    <select id="getMaxYear" resultType="java.lang.String">
        SELECT MAX(nd) FROM RS_ZJ_QSBS
    </select>
    <select id="getList" parameterType="com.zjrs.zwnw.zj.dto.QsDTO"
            resultType="com.zjrs.zwnw.zj.dto.QsbsDTO">
        SELECT rs_zj_qsbs_lsh rsZjQsbsLsh,nd,(SELECT max(ttt.nl40ubs)+MAX(ttt.NL40DBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(NL40UXZ),0)+nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40ULC),0)-NVL(SUM(NL40DLC),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt; t.nd)+t.Nl40uxz+t.Nl40dxz-t.nl40ulc-t.nl40dlc ljzl,t.Nl40uxz+t.Nl40dxz ndxz,
        t.nl40ulc+t.nl40dlc ndlc,
        (SELECT max(ttt.nl40ubs) FROM rs_zj_init ttt)+
        t.nl40uxz-t.nl40ulc nl40ulj,
        (SELECT MAX(ttt.NL40DBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40DLC),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt;  t.nd)+t.nl40dxz-t.nl40dlc nl40dlj,
        (SELECT MAX(ttt.ZYBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(ZYSZZDWXZ),0)-NVL(SUM(ZYSZZDWlc),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt;  t.nd)+t.zyszzdwxz-t.zyszzdwlc zyszzdwlj,
        (SELECT MAX(ttt.SSBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(SSDWXZ),0)-NVL(SUM(SSDWlc),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt;  t.nd)+t.ssdwxz-t.ssdwlc ssdwlj,
        t.nl40uxz,t.nl40dxz,t.zyszzdwxz,t.ssdwxz,t.nl40ulc,t.nl40dlc,t.zyszzdwlc,t.ssdwlc
        FROM rs_zj_qsbs t
        <if test="dto.nd != null and dto.nd != ''">
            where nd = #{ dto.nd }
        </if>
        ORDER BY nd desc
    </select>
    <select id="getListPage" parameterType="com.zjrs.zwnw.zj.dto.QsDTO"
            resultType="com.zjrs.zwnw.zj.dto.QsbsDTO">
        SELECT rs_zj_qsbs_lsh rsZjQsbsLsh,nd,(SELECT max(ttt.nl40ubs)+MAX(ttt.NL40DBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(NL40UXZ),0)+nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40ULC),0)-NVL(SUM(NL40DLC),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt; t.nd)+t.Nl40uxz+t.Nl40dxz-t.nl40ulc-t.nl40dlc ljzl,t.Nl40uxz+t.Nl40dxz ndxz,
        t.nl40ulc+t.nl40dlc ndlc,
        (SELECT max(ttt.nl40ubs) FROM rs_zj_init ttt)+
        t.nl40uxz-t.nl40ulc nl40ulj,
        (SELECT MAX(ttt.NL40DBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40DLC),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt; t.nd)+t.nl40dxz-t.nl40dlc nl40dlj,
        (SELECT MAX(ttt.ZYBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(ZYSZZDWXZ),0)-NVL(SUM(ZYSZZDWlc),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt; t.nd)+t.zyszzdwxz-t.zyszzdwlc zyszzdwlj,
        (SELECT MAX(ttt.SSBS) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(SSDWXZ),0)-NVL(SUM(SSDWlc),0)
        FROM rs_zj_qsbs tt WHERE tt.nd &lt; t.nd)+t.ssdwxz-t.ssdwlc ssdwlj
        FROM rs_zj_qsbs t
        <if test="dto.nd != null and dto.nd != ''">
            where nd = #{ dto.nd }
        </if>
        ORDER BY nd desc
    </select>
    <select id="getTjData" resultType="com.zjrs.zwnw.zj.dto.QsbsDTO">
        SELECT max(t.nd)+1 nd,
               max(ttt.nl40ubs)+MAX(ttt.NL40DBS)+nvl(SUM(NL40UXZ),0)+nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40ULC),0)-NVL(SUM(NL40DLC),0) ljzl,
               0 ndxz,0 ndlc,
               max(ttt.nl40ubs)+nvl(SUM(NL40UXZ),0)-NVL(SUM(NL40ULC),0) nl40ulj,
               MAX(ttt.NL40DBS)+nvl(SUM(NL40DXZ),0)-NVL(SUM(NL40DLC),0) NL40Dlj,
               MAX(ttt.ZYBS)+nvl(SUM(ZYSZZDWXZ),0)-NVL(SUM(ZYSZZDWlc),0) ZYSZZDWlj,
               MAX(ttt.SSBS)+nvl(SUM(SSDWXZ),0)-NVL(SUM(SSDWlc),0) SSDWlj
        FROM rs_zj_qsbs t,rs_zj_init ttt
        <if test="dto.nd != null and dto.nd != ''">
            WHERE t.nd &lt; #{ dto.nd }
        </if>
    </select>
</mapper>
