<?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.shanghaichengdi.ghjgitem.mapper.BoatWaterPollutantAcceptFMapper">

  <resultMap id="BaseResultMap"
    type="com.shanghaichengdi.ghjgitem.domain.BoatWaterPollutantAcceptF">
    <result property="shipName" column="SHIP_NAME" jdbcType="VARCHAR"/>
    <result property="startTime" column="START_TIME" jdbcType="VARCHAR"/>
    <result property="sysid" column="SYSID" jdbcType="VARCHAR"/>
    <result property="entRecevieId" column="ENT_RECEVIE_ID" jdbcType="VARCHAR"/>
    <result property="entApplyNo" column="ENT_APPLY_NO" jdbcType="VARCHAR"/>
    <result property="firstUser" column="FIRST_USER" jdbcType="VARCHAR"/>
    <result property="shipType" column="SHIP_TYPE" jdbcType="VARCHAR"/>
    <result property="receiveNum" column="RECEIVE_NUM" jdbcType="VARCHAR"/>
    <result property="shipId" column="SHIP_ID" jdbcType="VARCHAR"/>
    <result property="unit" column="UNIT" jdbcType="VARCHAR"/>
    <result property="receiveShipId" column="RECEIVE_SHIP_ID" jdbcType="VARCHAR"/>
    <result property="receiveType" column="RECEIVE_TYPE" jdbcType="VARCHAR"/>
    <result property="endTime" column="END_TIME" jdbcType="VARCHAR"/>
    <result property="firstDtm" column="FIRST_DTM" jdbcType="TIMESTAMP"/>
    <result property="modelOfOperation" column="MODEL_OF_OPERATION" jdbcType="VARCHAR"/>
    <result property="sewageType" column="SEWAGE_TYPE" jdbcType="VARCHAR"/>
    <result property="modeOfOperationOld" column="MODE_OF_OPERATION_OLD" jdbcType="VARCHAR"/>
    <result property="ecgNo" column="ECG_NO" jdbcType="VARCHAR"/>
    <result property="entRecevieNo" column="ENT_RECEVIE_NO" jdbcType="VARCHAR"/>
    <result property="entTransportNo" column="ENT_TRANSPORT_NO" jdbcType="VARCHAR"/>
    <result property="lastDtm" column="LAST_DTM" jdbcType="VARCHAR"/>
    <result property="lastUser" column="LAST_USER" jdbcType="VARCHAR"/>
    <result property="state" column="STATE" jdbcType="VARCHAR"/>
    <result property="recevieCom" column="RECEVIE_COM" jdbcType="VARCHAR"/>
    <result property="privateCode" column="PRIVATE_CODE" jdbcType="VARCHAR"/>
    <result property="latitude" column="LATITUDE" jdbcType="VARCHAR"/>
    <result property="longitude" column="LONGITUDE" jdbcType="VARCHAR"/>
    <result property="status" column="STATUS" jdbcType="VARCHAR"/>
    <result property="reveiveUser" column="REVEIVE_USER" jdbcType="VARCHAR"/>
    <result property="bvsNo" column="BVS_NO" jdbcType="VARCHAR"/>
    <result property="beNo" column="BE_NO" jdbcType="VARCHAR"/>
    <result property="picture" column="PICTURE" jdbcType="VARCHAR"/>
    <result property="mmsi" column="MMSI" jdbcType="VARCHAR"/>
    <result property="entApplyId" column="ENT_APPLY_ID" jdbcType="VARCHAR"/>
  </resultMap>

  <sql id="Base_Column_List">
    SHIP_NAME,START_TIME,SYSID,
        ENT_RECEVIE_ID,ENT_APPLY_NO,FIRST_USER,
        SHIP_TYPE,RECEIVE_NUM,SHIP_ID,
        UNIT,RECEIVE_SHIP_ID,RECEIVE_TYPE,
        END_TIME,FIRST_DTM,MODEL_OF_OPERATION,
        SEWAGE_TYPE,MODE_OF_OPERATION_OLD,ECG_NO,
        ENT_RECEVIE_NO,ENT_TRANSPORT_NO,LAST_DTM,
        LAST_USER,STATE,RECEVIE_COM,
        PRIVATE_CODE,LATITUDE,LONGITUDE,
        STATUS,REVEIVE_USER,BVS_NO,
        BE_NO,PICTURE,MMSI,
        ENT_APPLY_ID
  </sql>

  <select id="selectBoatWaterList" resultType="com.shanghaichengdi.ghjgitem.vo.PollutantAcceptVo">
    select a.SHIP_NAME boat,a.FIRST_DTM time,
    decode(a.SEWAGE_TYPE,'oilsewage','含油污水','sewage','生活污水','toxicliquid','洗舱水','badoil','残油废油','船舱垃圾')
    pollutanttype,
    a.RECEIVE_NUM || ' ' || a.UNIT pollutantcount,a.MODEL_OF_OPERATION model,a.RECEVIE_COM receive,
    decode(a.sysid,'dongan','',a.MMSI) mmsi,
    decode(a.sysid,'dongan','上海市内河水域','长江干线') sourceType,
    d.COM_NAME  position
    from
    (
    <foreach item="item" collection="tableNameList" separator="union">
      select * from ${item}
    </foreach>
    ) a
    left join GHJG_basics.D_POLLUTANT_UNIT d on a.RECEVIE_COM = d.com_no
    where 1=1
    <if test="boatName != null and boatName != ''">
      and a.SHIP_NAME = #{boatName}
    </if>
    <if test="mmsi != null and mmsi != ''">
      and a.MMSI = #{mmsi}
    </if>
    <if test="pollutantType != null and pollutantType != ''">
      and a.SEWAGE_TYPE =
      decode(#{pollutantType},'含油污水','oilsewage','生活污水','sewage','洗舱水','toxicliquid','残油废油','badoil','船舱垃圾','rubbish')
    </if>
    <if test="source != 1 and source != '' ">
      and a.sysid != 'dongan'
    </if>
    <if test="source != 2 and source != ''">
      and a.sysid = 'dongan'
    </if>
    order by a.FIRST_DTM desc
  </select>


  <select id="selectWarningInfoList"
    resultType="com.shanghaichengdi.ghjgitem.vo.PollutanWarningInfoVo">
    select * from (select g.code,
    g.BOATNAME,
    g.mmsi,
    g.WARNINGTIME,
    g.DISTRICT,
    decode(g.ISREAD,1,'已查看','未查看') ISREAD,
    g.type,
    g.REPORTCODE,
    g.STARTTIME,
    g.filterdes,
    g.FILTERTIME,
    asw.FIRST_DTM sanitary,
    asl.FIRST_DTM life,
    ayw.FIRST_DTM greasy,
    g.berthname,
    decode(k.SHIP_ID,null,kk.ship_id,k.ship_id) ship_id,
    decode(k.SHIP_REG_NO,null,kk.ship_reg_no,k.ship_reg_no) ship_reg_no,
    b.BID_NAME,b.MOBILE_PHONE,
    decode(bia.supervisiontype,1,'市内短驳船','普通船舶') supervisiontype,
    decode(g.SENDMSG,1,'已发送','未发送') SENDMSG,
    decode(g.RELIEVETYPE,1,'出港',2,'污染物排放',3,'预警超过5天',4,'监管类型变化',5,'人工核对解除',6,'版本切换解除') relieveType ,to_char(g.RELIEVETIME,'yyyy-mm-dd hh24:mi:ss') RELIEVETIME
    from (select * from GHJG_BUSSINESS.BOAT_WATER_POLLUTANT_WARNING_S
    where (FILTERSTATE = 1 or warncount >= 3) and deleteflag = 0 and RELIEVETYPE = 0) g
    left join (
    select a.waningcode,a.supervisiontype from ghjg_dynamic.boat_inport_ais_new a
    where a.deleteflag = 0 and a.waningcode is not null
    )bia on bia.waningcode = g.code
    left join ghjg_basics.boat_short_barge b on b.mmsi = g.mmsi
    left join ghjg_basics.mvw_boat_info k on k.mmsi = g.mmsi
    left join ghjg_basics.mvw_boat_info kk on kk.ship_name_cn = g.boatname
    left join (
    select b1.* from GHJG_BASICS.DICT_DISTRICT_PORT b1
    where b1.valid = '有效'
    ) b on g.berthname = b.BERTHNAME
    left join (
    select acc.SHIP_NAME,acc.FIRST_DTM
    from GHJG_BUSSINESS.BOAT_POLLUTANT_ACCEPT_NEW acc
    where acc.SEWAGE_TYPE = 'sewage'
    ) asw on asw.SHIP_NAME = g.boatname
    left join (
    select acc.SHIP_NAME,acc.FIRST_DTM
    from GHJG_BUSSINESS.BOAT_POLLUTANT_ACCEPT_NEW acc
    where acc.SEWAGE_TYPE = 'rubbish'
    ) asl on asl.SHIP_NAME = g.boatname
    left join (
    select acc.SHIP_NAME,acc.FIRST_DTM
    from GHJG_BUSSINESS.BOAT_POLLUTANT_ACCEPT_NEW acc
    where acc.SEWAGE_TYPE = 'oilsewage'
    ) ayw on ayw.SHIP_NAME = g.boatname
    where g.relievetype = 0 and g.deleteflag = 0
    ) tt
    where 1=1
    <if test="boatName != null and boatName != ''">
      and tt.BOATNAME =  #{boatName}
    </if>
    <if test="mmsi != null and mmsi != ''">
      and tt.mmsi = #{mmsi}
    </if>
    <if test="startTime != null and startTime != ''">
      and tt.WARNINGTIME >= to_date(#{startTime}|| ' 00:00:00','yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="endTime != null and endTime != ''">
      and tt.WARNINGTIME &lt;= to_date(#{endTime}|| ' 23:59:59','yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="jurisdiction != null and jurisdiction != ''">
      and tt.district like #{jurisdiction} || '%'
    </if>
    <if test="warningType != null and warningType != ''">
      and tt.TYPE = #{warningType}
    </if>
    <if test="warningStatus != null and warningStatus != ''">
      and tt.nowStatus = #{warningStatus}
    </if>
    <if test="supervisionType != null and supervisionType != ''">
      and tt.supervisiontype = #{supervisionType}
    </if>
    order by tt.WARNINGTIME desc,tt.CODE desc
  </select>

  <select id="selectWarningHisInfoList"
    resultType="com.shanghaichengdi.ghjgitem.vo.PollutanWarningInfoVo">
    select * from (select g.code,
    g.BOATNAME,
    g.mmsi,
    g.WARNINGTIME,
    g.DISTRICT,
    decode(g.ISREAD,1,'已查看','未查看') ISREAD,
    g.type,
    g.REPORTCODE,
    g.STARTTIME,
    ceil(decode(g.relievetime,null,(sysdate-g.STARTTIME),(g.RELIEVETIME - g.STARTTIME))) totaldays,
    g.berthname,
    g.filterdes,
    g.FILTERTIME,
    decode(k.SHIP_ID,null,kk.ship_id,k.ship_id) ship_id,
    decode(k.SHIP_REG_NO,null,kk.ship_reg_no,k.ship_reg_no) ship_reg_no,
    b.BID_NAME,b.MOBILE_PHONE,
    decode(g.SENDMSG,1,'已发送','未发送') SENDMSG,
    asw.FIRST_DTM sanitary,
    g.relievetime,
    decode(bia.supervisiontype,1,'市内短驳船','普通船舶') supervisiontype,
    decode(g.RELIEVETYPE,1,'离港',2,'已排放',3,'预警超5天',4,'监管类型变化',5,'人工核对解除',6,'版本升级解除','其它')
    RELIEVETYPE
    from (
    select * from (
    select w.*,row_number() over(partition by w.boatname order by w.warningtime desc) num from
    GHJG_BUSSINESS.BOAT_WATER_POLLUTANT_WARNING_S w
    where w.deleteflag = 0 and decode(w.relievetime,null,1,2) = 2 and (w.FILTERSTATE = 1 or
    w.warncount >= 3)
    ) where num = 1
    ) g
    left join ghjg_dynamic.boat_inport_ais_new bia on bia.waningcode = g.code
    left join ghjg_basics.boat_short_barge b on b.mmsi = g.mmsi
    left join ghjg_basics.mvw_boat_info k on k.mmsi = g.mmsi
    left join ghjg_basics.mvw_boat_info kk on kk.ship_name_cn = g.boatname
    left join (
    select b1.* from GHJG_BASICS.DICT_DISTRICT_PORT b1
    where b1.valid = '有效'
    ) b on g.berthname = b.BERTHNAME
    left join (
    select acc.SHIP_NAME,acc.FIRST_DTM
    from GHJG_BUSSINESS.BOAT_POLLUTANT_ACCEPT_NEW acc
    where acc.SEWAGE_TYPE = 'sewage'
    ) asw on asw.SHIP_NAME = g.boatname
    ) tt
    where 1=1
    and tt.totaldays >= 0
    <if test="boatName != null and boatName != ''">
      and tt.BOATNAME =  #{boatName}
    </if>
    <if test="mmsi != null and mmsi != ''">
      and tt.mmsi = #{mmsi}
    </if>
    <if test="startTime != null and startTime != ''">
      and tt.WARNINGTIME >= to_date(#{startTime}|| ' 00:00:00','yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="endTime != null and endTime != ''">
      and tt.WARNINGTIME &lt;= to_date(#{endTime}|| ' 23:59:59','yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="jurisdiction != null and jurisdiction != ''">
      and tt.district like #{jurisdiction} || '%'
    </if>
    <if test="warningType != null and warningType != ''">
      and tt.TYPE = #{warningType}
    </if>
    <if test="warningStatus != null and warningStatus != ''">
      and tt.nowStatus = #{warningStatus}
    </if>
    <if test="supervisionType != null and supervisionType != ''">
      and tt.supervisiontype = #{supervisionType}
    </if>
    order by tt.relievetime desc,tt.CODE desc
  </select>

  <select id="selectWarningInfoListByDate" resultType="com.shanghaichengdi.ghjgitem.vo.PollutanWarningInfoVo">
    select * from (
      select g.code,
      g.BOATNAME,
      g.mmsi,
      g.WARNINGTIME,
      g.DISTRICT,
      g.type,
      g.REPORTCODE,
      g.STARTTIME,
      g.filterdes,
      g.FILTERTIME,
      g.berthname,
      decode(k.SHIP_ID,null,kk.ship_id,k.ship_id) ship_id,
      decode(k.SHIP_REG_NO,null,kk.ship_reg_no,k.ship_reg_no) ship_reg_no,
      b.BID_NAME,b.MOBILE_PHONE
      from (select * from GHJG_BUSSINESS.BOAT_WATER_POLLUTANT_WARNING_S
      where (FILTERSTATE = 1 or warncount >= 3) and deleteflag = 0 and RELIEVETYPE = 0) g
      left join ghjg_basics.boat_short_barge b on b.mmsi = g.mmsi
      left join ghjg_basics.mvw_boat_info k on k.mmsi = g.mmsi
      left join ghjg_basics.mvw_boat_info kk on kk.ship_name_cn = g.boatname
      where g.relievetype = 0 and g.deleteflag = 0
    ) tt
    where 1=1
    <if test="jurisdiction != null and jurisdiction != ''">
      and tt.district like #{jurisdiction} || '%'
    </if>
    <if test="startTime != null and startTime != ''">
      and tt.FILTERTIME >= to_date(#{startTime},'yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="endTime != null and endTime != ''">
      and tt.FILTERTIME &lt;= to_date(#{endTime},'yyyy-mm-dd hh24:mi:ss')
    </if>
    order by tt.WARNINGTIME, tt.CODE desc
  </select>

  <select id="selectBoatWaterListSync" resultType="com.shanghaichengdi.ghjgitem.domain.BoatWaterPollutantAcceptF">
    select a.*
    from
    (
    <foreach item="item" collection="tableNameList" separator="union">
      select * from ${item}
    </foreach>
    ) a
    where 1=1
    <if test="boatName != null and boatName != ''">
      and a.SHIP_NAME like '%' || #{boatName} || '%'
    </if>
    <if test="mmsi != null and mmsi != ''">
      and a.MMSI = #{mmsi}
    </if>
    <if test="pollutantType != null and pollutantType != ''">
      and a.SEWAGE_TYPE =
      decode(#{pollutantType},'含油污水','oilsewage','生活污水','sewage','洗舱水','toxicliquid','残油废油','badoil','船舱垃圾','rubbish')
    </if>
    <if test="source != 1 and source != '' ">
      and a.sysid != 'dongan'
    </if>
    <if test="source != 2 and source != ''">
      and a.sysid = 'dongan'
    </if>
    <if test="startTime != null and startTime != ''">
      and a.FIRST_DTM >= to_date(#{startTime}||' 00:00:00','yyyy-mm-dd hh24:mi:ss')
    </if>
    <if test="endTime != null and endTime != ''">
      and a.FIRST_DTM &lt;= to_date(#{endTime}||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
    </if>
    order by a.FIRST_DTM desc
  </select>


  <select id="selectWarningInfoListSync" resultType="com.shanghaichengdi.ghjgitem.domain.BoatWaterPollutantWarningS">
    select t.CODE,
           t.BOATNAME,
           t.MMSI,
           t.TYPE,
           t.WARNINGTIME,
           t.REPORTCODE,
           t.RELIEVETIME,
           t.WARNINGSTATE,
           t.ISREAD,
           t.BERTHNAME,
           t.STARTTIME,
           t.DELETEFLAG,
           t.RELIEVETYPE,
           t.FILTERSTATE,
           t.FILTERDES,
           t.FILTERTIME,
           t.FILTERPUSHDATE
    from (select t.*
          from (select s.*,
                       row_number() over (partition by s.mmsi,s.warningtime order by s.filtertime nulls last) rn
                from GHJG_BUSSINESS.BOAT_WATER_POLLUTANT_WARNING_S s
                where s.filterstate = 1
                  and s.deleteflag = 0
                  and s.district like '%浦东%'
                  and s.filterpushdate >= to_date(#{startTime}, 'yyyy-mm-dd hh24:mi:ss')
                  and to_date(#{endTime}, 'yyyy-mm-dd hh24:mi:ss') >= s.filterpushdate) t
          where t.rn = 1) t
           left join ghjg_basics.boat_short_barge b on b.mmsi = t.mmsi
           left join ghjg_dynamic.boat_inport_ais_new n on n.waningcode = t.code
  </select>
  <select id="getPollutantWarningByCode" resultType="java.lang.String">
    select REPORTCODE from ghjg_bussiness.boat_water_pollutant_warning_s w where w.code = #{warningCode}
  </select>

  <select id="getBoatReportByWarningCode" resultType="com.shanghaichengdi.ghjgitem.vo.BoatReportVo">
    select t.report_id,t.ship_id,t.ship_name_cn,t.mmsi,t.report_no,t.report_port_code,t.report_port_name,
           t.arrival_or_leave,t.arrival_report_id,t.next_port_code,t.next_port_name,t.bid_name,t.mobile_phone,
           t.berth_code,t.berth_name,t.pre_port_code,t.pre_port_name,
           to_char(t.report_time,'yyyy-mm-dd hh24:mi:ss') report_time
    from GHJG_BASICS.C_CB_SHIP_EEP_REPORT_REC t
    where t.report_no = #{reportCode}
  </select>
</mapper>
