<?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.QstcMapper">

    <select id="getMaxYear" resultType="java.lang.String">
        SELECT MAX(nd) FROM RS_ZJ_QSTC
    </select>
    <select id="getList" parameterType="com.zjrs.zwnw.zj.dto.QsDTO"
            resultType="com.zjrs.zwnw.zj.dto.QstcDTO">
        SELECT t.rs_zj_qstc_lsh rsZjQstcLsh,nd,
        (SELECT max(ttt.GJZJ)+max(ttt.SHENGJZJ)+max(ttt.SHIJZJ)+max(ttt.XSGWYTSJT) FROM rs_zj_init ttt)+
        (SELECT nvl(SUM(GJZJXZ),0)+nvl(SUM(SHENGJZJXZ),0)+nvl(SUM(SHIJZJXZ),0)+nvl(SUM(XSGWYTSJTXZ),0)
        -NVL(SUM(GJZJlc),0)-NVL(SUM(SHENGJZJlc),0)-NVL(SUM(SHIJZJLC),0)-NVL(SUM(XSGWYTSJTLC),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.GJZJXZ-t.GJZJlc+t.SHENGJZJXZ-t.SHENGJZJlc+t.SHIJZJXZ-t.SHIJZJLC+t.XSGWYTSJTXZ-t.XSGWYTSJTLC ljzl,
        (t.GJZJXZ+t.SHENGJZJXZ+t.SHIJZJXZ+t.XSGWYTSJTXZ) ndxz,
        (t.GJZJlc+t.SHENGJZJlc+t.SHIJZJLC+t.XSGWYTSJTLC) ndlc,
        (SELECT nvl(SUM(GJZJXZ),0)-NVL(SUM(GJZJlc),0) FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.GJZJXZ-t.GJZJlc gjzjlj,
        (SELECT max(ttt.SHENGJZJ) FROM rs_zj_init ttt)+(SELECT nvl(SUM(SHENGJZJXZ),0)-NVL(SUM(SHENGJZJlc),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.SHENGJZJXZ-t.SHENGJZJlc shengjzjlj,
        (SELECT nvl(SUM(SHIJZJXZ),0)-NVL(SUM(SHIJZJLC),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.SHIJZJXZ-t.SHIJZJLC shijzjlj,
        (SELECT max(ttt.XSGWYTSJT) FROM rs_zj_init ttt)+(SELECT nvl(SUM(XSGWYTSJTXZ),0)-NVL(SUM(XSGWYTSJTLC),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.XSGWYTSJTXZ-t.XSGWYTSJTLC xsgwytsjtlj,
        t.GJZJXZ,t.SHENGJZJXZ,t.SHIJZJXZ,t.XSGWYTSJTXZ,t.GJZJlc,t.SHENGJZJlc,t.SHIJZJLC,t.XSGWYTSJTLC
        FROM rs_zj_qstc 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.QstcDTO">
        SELECT t.rs_zj_qstc_lsh rsZjQstcLsh,nd,
        (SELECT max(ttt.GJZJ)+max(ttt.SHENGJZJ)+max(ttt.SHIJZJ)+max(ttt.XSGWYTSJT) FROM rs_zj_init ttt)+
        t.GJZJXZ-t.GJZJlc+t.SHENGJZJXZ-t.SHENGJZJlc+t.SHIJZJXZ-t.SHIJZJLC+t.XSGWYTSJTXZ-t.XSGWYTSJTLC ljzl,
        (t.GJZJXZ+t.SHENGJZJXZ+t.SHIJZJXZ+t.XSGWYTSJTXZ) ndxz,
        (t.GJZJlc+t.SHENGJZJlc+t.SHIJZJLC+t.XSGWYTSJTLC) ndlc,
        (SELECT max(ttt.GJZJ) FROM rs_zj_init ttt)+(SELECT nvl(SUM(GJZJXZ),0)-NVL(SUM(GJZJlc),0) FROM rs_zj_qstc tt
        WHERE tt.nd &lt; t.nd)+t.GJZJXZ-t.GJZJlc gjzjlj,
        (SELECT max(ttt.SHENGJZJ) FROM rs_zj_init ttt)+(SELECT nvl(SUM(SHENGJZJXZ),0)-NVL(SUM(SHENGJZJlc),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.SHENGJZJXZ-t.SHENGJZJlc shengjzjlj,
        (SELECT nvl(SUM(SHIJZJXZ),0)-NVL(SUM(SHIJZJLC),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.SHIJZJXZ-t.SHIJZJLC shijzjlj,
        (SELECT max(ttt.XSGWYTSJT) FROM rs_zj_init ttt)+(SELECT nvl(SUM(XSGWYTSJTXZ),0)-NVL(SUM(XSGWYTSJTLC),0)
        FROM rs_zj_qstc tt WHERE tt.nd &lt; t.nd)+t.XSGWYTSJTXZ-t.XSGWYTSJTLC xsgwytsjtlj
        FROM rs_zj_qstc 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.QstcDTO">
        SELECT max(t.nd)+1 nd,
        max(ttt.GJZJ)+MAX(ttt.shengjzj)+MAX(shijzj)+MAX(xsgwytsjt)+
        nvl(SUM(t.gjzjxz),0)+SUM(t.shengjzjxz)+SUM(t.shijzjxz)+SUM(t.xsgwytsjtxz)
        -NVL(SUM(t.gjzjlc),0)-NVL(SUM(t.shengjzjlc),0)-SUM(t.shijzjlc)-SUM(t.xsgwytsjtlc) ljzl,
        0 ndxz,0 ndlc,
        max(ttt.GJZJ)+nvl(SUM(t.gjzjxz),0)-NVL(SUM(t.gjzjlc),0) gjzjlj,
        MAX(ttt.shengjzj)+nvl(SUM(t.shengjzjxz),0)-NVL(SUM(t.shengjzjlc),0) shengjzjlj,
        MAX(ttt.shijzj)+nvl(SUM(t.shijzjxz),0)-NVL(SUM(t.shijzjlc),0) shijzjlj,
        MAX(ttt.xsgwytsjt)+nvl(SUM(t.xsgwytsjtxz),0)-NVL(SUM(t.xsgwytsjtlc),0) xsgwytsjtlj
        FROM rs_zj_qstc t,rs_zj_init ttt
        <if test="dto.nd != null and dto.nd != ''">
            WHERE t.nd &lt; #{ dto.nd }
        </if>
    </select>
</mapper>
