<?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.trs.ggzyexchange.mapper.CqSwBargainResultMapper">

    <select id="queryCqSwBargainResultAllList" resultType="com.trs.ggzyexchange.entity.TenderDetailsVo">
        select
            csb.SOURCE_DATA_KEY sourceDataKey,
            csi.PROJECT_CODE projectCode,
            csi.PROJECT_NAME projectName,
            csb.PROJECT_NAME noticeName,
            csi.CONTENT_DATA noticeContent,
            csi.PUBLISH_DATE noticeSendTime,
            csi.REGION_CODE regionCode,
            csi.PLATFORM_CODE platformCode,
            csi.TRA_AGENCY_NAME bulletinDuty,
            csb.SOURCE_DATA_CODE sourceDataCode,
            csi.SIGNUP_OPEN_TIME docGetStartTime,
            csi.SIGNUP_END_TIME docGetEndTime,
            csi.SIGNUP_END_TIME bidDocReferEndTime,
            csi.SIGNUP_OPEN_TIME bidOpenTime
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
    </select>
    <select id="queryCqSwBargainResultAllListByTime" resultType="com.trs.ggzyexchange.entity.TenderDetailsVo">

        select
            csb.SOURCE_DATA_KEY sourceDataKey,
            csi.PROJECT_CODE projectCode,
            csi.PROJECT_NAME projectName,
            csb.PROJECT_NAME noticeName,
            csi.CONTENT_DATA noticeContent,
            csi.PUBLISH_DATE noticeSendTime,
            csi.REGION_CODE regionCode,
            csi.PLATFORM_CODE platformCode,
            csi.TRA_AGENCY_NAME bulletinDuty,
            csb.SOURCE_DATA_CODE sourceDataCode,
            csi.SIGNUP_OPEN_TIME docGetStartTime,
            csi.SIGNUP_END_TIME docGetEndTime,
            csi.SIGNUP_END_TIME bidDocReferEndTime,
            csi.SIGNUP_OPEN_TIME bidOpenTime
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
        where csb.DATA_TIMESTAMP &gt; #{time}
    </select>


    <select id="maxDataTimeStamp" resultType="java.lang.Long">
        select max(DATA_TIMESTAMP) from "ggzyexchange"."CQ_SW_ISSUE_BULLETIN";
    </select>
    <select id="countByRegionCodeAndTime" resultType="java.lang.Integer">
        select
            count(1)
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
        where
            csb.TRADE_DATE &gt;= #{resultStartTimeYyyyMMdd} and csb.TRADE_DATE &lt;= #{resultEndTimeYyyyMMdd}
            and csi.REGION_CODE like CONCAT(#{region_code}, '%')
    </select>
    <select id="countByRegionCode" resultType="java.lang.Integer">
        select
            count(1)
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
        where  csi.REGION_CODE like CONCAT(#{region_code}, '%')
    </select>
    <select id="getSumMoneyByRegionCode" resultType="com.trs.ggzyexchange.entity.CqSwBargainResult">

        select
            sum( case when csb.PRICE_UNIT = 0 then csb.TRADE_VALUE when csb.PRICE_UNIT = 1 then csb.TRADE_VALUE*10000 else csb.TRADE_VALUE end) as sumAll
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
        where  csi.REGION_CODE like CONCAT(#{region_code}, '%')


    </select>
    <select id="getSumMoneyByRegionCodeAndTime" resultType="com.trs.ggzyexchange.entity.CqSwBargainResult">
        select
            sum( case when csb.PRICE_UNIT = 0 then csb.TRADE_VALUE when csb.PRICE_UNIT = 1 then csb.TRADE_VALUE*10000 else csb.TRADE_VALUE end) as sumAll
        from
            CQ_SW_BARGAIN_RESULT csb
                left join CQ_SW_ISSUE_BULLETIN csi
                          on csb.BID_CODE = csi.BID_CODE
        where
            csb.TRADE_DATE &gt;= #{resultStartTime} and csb.TRADE_DATE &lt;= #{resultEndTime}
            and
              csi.REGION_CODE like CONCAT(#{regionCode}, '%')
    </select>
</mapper>
