<?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.tofly.xrtygis.custom.mapper.FixedMapper">

    <select id="selectWaterInfo" resultType="com.tofly.xrtygis.entity.Costrlwater">
        select *
        from TF_YWPN_COSTRLWATER_W
        where ROWNUM &lt;= 1
        <if test="mark == 1">
            and to_char(UP_DATE, 'yyyy-mm') = substr(#{date},1,7)
            and mark = #{mark}
        </if>
        <if test="mark == 0">
            and to_char(UP_DATE, 'yyyy-mm-dd') = #{date}
            and mark = #{mark}
        </if>
    </select>

    <select id="viewFixedPlan" resultType="com.tofly.xrtygis.entity.Costbgfixed">
        select paczfy_one,
        paczfy_two,
        syzfy_one,
        ylzfy_two,
        pamzszfy,
        ftzfy,
        cnzfy,
        gmsjfhyzfy,
        pamwnzfy,
        bcszfy,
        wnzyf,
        jbdf_one,
        jbdf_two,
        jbdf_total,
        dldf_one,
        dldf_two,
        dldf_total,
        ysf,
        szysf_one,
        szysf_two,
        szysf_total,
        dfcb_one,
        dfcb_two,
        dfcb_total,
        paccb_one,
        sycb_one,
        cncb_one,
        bcscb_one,
        zsclcb_one,
        paccb_two,
        ylcb_two,
        pamzscb_two,
        ftcb_two,
        gmsjfhycb_two,
        pamwncb_two,
        wnzycb_two,
        zsclcb_two,
        zsclcb_total,
        ysfcb,
        szysfcb_one,
        szysfcb_two,
        szysfcb_total,
        zfy_one,
        zfy_two,
        dwcb_one,
        dwcb_two,
        dwcb_total,
        create_time,
        zzsclfy_one,
        zzsclfy_two,
        zfy,
        otherzfy,
        othercb
        from TF_YWPN_COSTBGFIXED_W
        where
        <if test="month != null">
            MOUTH = #{month}
        </if>
        <if test="month == null">
            MOUTH = to_char(sysdate,'yyyy-mm')
        </if>
    </select>

    <select id="viewFinance" resultType="com.tofly.xrtygis.entity.Costrlfixed">
        select id,
        to_char(UP_DATE,'yyyy-mm-dd'),
        YSF,
        FTCB_TWO,
        SZYSF_ONE,
        SZYSF_TWO,
        SZYSF_TOTAL,
        DFCB_ONE,
        DFCB_TWO,
        DFCB_TOTAL,
        PACCB_ONE,
        SYCB_ONE,
        BCSCB_ONE,
        CNCB_ONE,
        ZSCLCB_ONE,
        PACCB_TWO,
        YLCB_TWO,
        PAMZSCB_TWO,
        GMSJFHYCB_TWO,
        PAMWNCB_TWO,
        WNZYCB_TWO,
        ZSCLCB_TWO,
        ZSCLCB_TOTAL,
        YSFCB,
        SZYSFCB_ONE,
        SZYSFCB_TWO,
        SZYSFCB_TOTAL,
        DWCB_ONE,
        DWCB_TWO,
        DWCB_TOTAL,
        ZFY_ONE,
        ZFY_TWO,
        ZFY,
        PACZFY_ONE,
        SYZFY_ONE,
        PACZFY_TWO,
        YLZFY_TWO,
        PAMZSZFY,
        FTZFY,
        CNZFY,
        GMSJFHYZFY,
        PAMWNZFY,
        BCSZFY,
        WNZYF,
        JBDF_ONE,
        JBDF_TWO,
        JBDF_TOTAL,
        DLDF_ONE,
        DLDF_TWO,
        DLDF_TOTAL,
        SZYSF_TOTAL,
        OTHERZFY,
        OTHERCB,
        DF_ONE_TOTAL,
        DF_TWO_TOTAL,
        DL_ONE_TOTAL,
        DL_TWO_TOTAL
        from TF_YWPN_COSTRLFIXED_W
        where MARK = '1'
        <if test="month != null">
            and to_char(UP_DATE,'yyyy-mm') = #{month}
        </if>
        <if test="month == null">
            and to_char(UP_DATE,'yyyy-mm') = to_char(sysdate,'yyyy-mm')
        </if>
    </select>

    <select id="viewSumFixed" resultType="map">
        select
        sum(YSF) YSF,
        sum(SZYSF_ONE) SZYSF_ONE,
        sum(SZYSF_TWO) SZYSF_TWO,
        sum(SZYSF_TOTAL) SZYSF_TOTAL,
        sum(ZFY_ONE) ZFY_ONE,
        sum(ZFY_TWO) ZFY_TWO,
        sum(ZFY) ZFY,
        sum(PACZFY_ONE) PACZFY_ONE ,
        sum(SYZFY_ONE) SYZFY_ONE,
        sum(PACZFY_TWO) PACZFY_TWO,
        sum(YLZFY_TWO) YLZFY_TWO,
        sum(PAMZSZFY) PAMZSZFY,
        sum(FTZFY) FTZFY,
        sum(CNZFY) CNZFY,
        sum(GMSJFHYZFY) GMSJFHYZFY,
        sum(PAMWNZFY) PAMWNZFY,
        sum(BCSZFY) BCSZFY,
        sum(WNZYF) WNZYF,
        sum(JBDF_ONE) JBDF_ONE,
        sum(JBDF_TWO) JBDF_TWO,
        sum(JBDF_TOTAL) JBDF_TOTAL,
        sum(DLDF_ONE) DLDF_ONE,
        sum(DLDF_TWO) DLDF_TWO,
        sum(DLDF_TOTAL) DLDF_TOTAL,
        sum(SZYSF_TOTAL) SZYSF_TOTAL,
        sum(OTHERZFY) OTHERZFY,

        -- ---------成本计算-----------
        round((sum(JBDF_ONE) + sum(DLDF_ONE) ) / nullif(sum(GSL_ONE),0) * 1000,3) DFCB_ONE,
        round((sum(JBDF_TWO) + sum(DLDF_TWO) ) / nullif(sum(GSL_TWO),0) * 1000,3) DFCB_TWO,
        round((sum(JBDF_TOTAL) + sum(DLDF_TOTAL) ) / nullif(sum(GSL_TOTAL),0) * 1000,3) DFCB_TOTAL,
        round(sum(PACZFY_ONE) / nullif(sum(GSL_ONE),0) * 1000,3) PACCB_ONE,
        round(sum(SYZFY_ONE) / nullif(sum(GSL_ONE),0) * 1000,3) SYCB_ONE,
        round(sum(BCSZFY) / nullif(sum(GSL_ONE),0) * 1000,3) BCSCB_ONE,
        round(sum(CNZFY) / nullif(sum(GSL_ONE),0) * 1000,3) CNCB_ONE,
        round(sum(ZZSCLFY_ONE) / nullif(sum(GSL_ONE),0) * 1000,3) ZSCLCB_ONE,
        round(sum(PACZFY_TWO) / nullif(sum(GSL_TWO),0) * 1000,3) PACCB_TWO,
        round(sum(YLZFY_TWO) / nullif(sum(GSL_TWO),0) * 1000,3) YLCB_TWO,
        round(sum(PAMZSZFY) / nullif(sum(GSL_TWO),0) * 1000,3) PAMZSCB_TWO,
        round(sum(GMSJFHYZFY) / nullif(sum(GSL_TWO),0) * 1000,3) GMSJFHYCB_TWO,
        round(sum(PAMWNZFY) / nullif(sum(GSL_TWO),0) * 1000,3) PAMWNCB_TWO,
        round(sum(FTZFY) / nullif(sum(GSL_TWO),0) * 1000,3) FTCB_TWO,
        round(sum(WNZYF) / nullif(sum(GSL_TWO),0) * 1000,3) WNZYCB_TWO,
        round(sum(ZZSCLFY_TWO) / decode(sum(GSL_TWO),0,null,sum(GSL_TWO)) * 1000,3) ZSCLCB_TWO,
        round((sum(ZZSCLFY_ONE) + sum(ZZSCLFY_TWO)) / nullif(sum(GSL_TOTAL),0) * 1000,3) ZSCLCB_TOTAL,
        round(sum(YSF) / nullif(sum(GSL_TOTAL),0) * 1000,3) YSFCB,
        round(sum(SZYSF_ONE) / nullif(sum(GSL_ONE),0) * 1000,3) SZYSFCB_ONE,
        round(sum(SZYSF_TWO) / nullif(sum(GSL_TWO),0) * 1000,3) SZYSFCB_TWO,
        round(sum(SZYSF_TOTAL) / nullif(sum(GSL_TOTAL),0) * 1000,3) SZYSFCB_TOTAL,
        round(sum(ZFY_ONE) / nullif(sum(GSL_TOTAL),0) * 1000,3) DWCB_ONE,
        round(sum(ZFY_TWO) / nullif(sum(GSL_TOTAL),0) * 1000,3)DWCB_TWO,
        round(sum(ZFY) / nullif(sum(GSL_TOTAL),0) * 1000,3) DWCB_TOTAL,
        round(sum(OTHERZFY) / nullif(sum(GSL_TOTAL),0) * 1000,3) OTHERCB
        from TF_YWPN_COSTRLFIXED_W f full join TF_YWPN_COSTRLWATER_W w on to_char(f.UP_DATE,'yyyy-mm-dd') =
        to_char(w.UP_DATE,'yyyy-mm-dd') and f.MARK = w.MARK
        where f.MARK = '0'
        <if test="month != null">
            and to_char(f.UP_DATE,'yyyy-mm') = #{month}
        </if>
        <if test="month == null">
            and to_char(f.UP_DATE,'yyyy-mm') = to_char(sysdate,'yyyy-mm')
        </if>
        group by to_char(f.UP_DATE,'yyyy-mm')
    </select>

    <select id="viewFixed" resultType="map">
        select
        id,
        to_char(UP_DATE,'yyyy-mm-dd') up_date,
        YSF,
        SZYSF_ONE,
        SZYSF_TWO,
        SZYSF_TOTAL,
        DFCB_ONE,
        DFCB_TWO,
        DFCB_TOTAL,
        PACCB_ONE,
        SYCB_ONE,
        BCSCB_ONE,
        CNCB_ONE,
        ZSCLCB_ONE,
        PACCB_TWO,
        YLCB_TWO,
        PAMZSCB_TWO,
        GMSJFHYCB_TWO,
        PAMWNCB_TWO,
        WNZYCB_TWO,
        ZSCLCB_TWO,
        ZSCLCB_TOTAL,
        YSFCB,
        SZYSFCB_ONE,
        SZYSFCB_TWO,
        SZYSFCB_TOTAL,
        DWCB_ONE,
        DWCB_TWO,
        DWCB_TOTAL,
        ZFY_ONE,
        ZFY_TWO,
        ZFY,
        FTCB_TWO,
        PACZFY_ONE,
        SYZFY_ONE,
        PACZFY_TWO,
        YLZFY_TWO,
        PAMZSZFY,
        FTZFY,
        CNZFY,
        GMSJFHYZFY,
        PAMWNZFY,
        BCSZFY,
        WNZYF,
        JBDF_ONE,
        JBDF_TWO,
        JBDF_TOTAL,
        DLDF_ONE,
        DLDF_TWO,
        DLDF_TOTAL,
        SZYSF_TOTAL
        from TF_YWPN_COSTRLFIXED_W
        where MARK = '0'
        <if test="month != null">
            and to_char(UP_DATE,'yyyy-mm') = #{month}
        </if>
        <if test="month == null">
            and to_char(UP_DATE,'yyyy-mm') = to_char(sysdate,'yyyy-mm')
        </if>
        order by up_date
    </select>

    <select id="viewDayFixed" resultType="com.tofly.xrtygis.entity.Costrlfixed">
        select *
        from TF_YWPN_COSTRLFIXED_W
        where ROWNUM = 1
        <if test="mark == 1">
            and to_char(UP_DATE,'yyyy-mm') = to_char(#{date},'yyyy-mm')
            and mark = #{mark}
        </if>
        <if test="mark == 0">
            and to_char(UP_DATE,'yyyy-mm-dd') = #{date}
            and mark = #{mark}
        </if>
    </select>

    <select id="getPriceHistory" resultType="map">
        select id, year,UP_DATE, pacdj, yldj, pam_zsdj, pam_wndj, ftdj, cndj, gmsjfhydj, sydj, dlddj_one, dlddj_two, wnzydj,
        ysfdj, szysdj, bcsdj, isnew, create_time, create_user, pac2dj, qbjzgdj, otherdj, jbdldj_one, jbdldj_two,
        (select REAL_NAME from TF_SMPN_USER_X where id = p.CREATE_USER) user_name
        from TF_YWPN_COSTRLPRICE_W p
        where YEAR = #{year}
        <if test="start != null and end != null">
            and to_char(UP_DATE,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        order by UP_DATE desc
    </select>

</mapper>