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

  <resultMap id="BaseResultMap" type="com.shanghaichengdi.ghjgitem.domain.ShorePowerConnRecordInfo">
    <result property="connrecordseq" column="CONNRECORDSEQ" jdbcType="VARCHAR"/>
    <result property="portid" column="PORTID" jdbcType="VARCHAR"/>
    <result property="portname" column="PORTNAME" jdbcType="VARCHAR"/>
    <result property="enterprisesname" column="ENTERPRISESNAME" jdbcType="VARCHAR"/>
    <result property="enterprisesid" column="ENTERPRISESID" jdbcType="VARCHAR"/>
    <result property="operatorname" column="OPERATORNAME" jdbcType="VARCHAR"/>
    <result property="operatorid" column="OPERATORID" jdbcType="VARCHAR"/>
    <result property="connectorid" column="CONNECTORID" jdbcType="VARCHAR"/>
    <result property="berthid" column="BERTHID" jdbcType="VARCHAR"/>
    <result property="shipname" column="SHIPNAME" jdbcType="VARCHAR"/>
    <result property="shipnumber" column="SHIPNUMBER" jdbcType="VARCHAR"/>
    <result property="shipgrosstonnage" column="SHIPGROSSTONNAGE" jdbcType="DECIMAL"/>
    <result property="shiphomeport" column="SHIPHOMEPORT" jdbcType="VARCHAR"/>
    <result property="userchargetype" column="USERCHARGETYPE" jdbcType="DECIMAL"/>
    <result property="mobilenumber" column="MOBILENUMBER" jdbcType="VARCHAR"/>
    <result property="starttime" column="STARTTIME" jdbcType="TIMESTAMP"/>
    <result property="endtime" column="ENDTIME" jdbcType="TIMESTAMP"/>
    <result property="duration" column="DURATION" jdbcType="DECIMAL"/>
    <result property="elect" column="ELECT" jdbcType="DECIMAL"/>
    <result property="money" column="MONEY" jdbcType="DECIMAL"/>
    <result property="electmoney" column="ELECTMONEY" jdbcType="DECIMAL"/>
    <result property="servicemoney" column="SERVICEMONEY" jdbcType="DECIMAL"/>
    <result property="paymentamount" column="PAYMENTAMOUNT" jdbcType="DECIMAL"/>
    <result property="paytime" column="PAYTIME" jdbcType="TIMESTAMP"/>
    <result property="paychannel" column="PAYCHANNEL" jdbcType="DECIMAL"/>
    <result property="modifytime" column="MODIFYTIME" jdbcType="TIMESTAMP"/>
    <result property="conntype" column="CONNTYPE" jdbcType="VARCHAR"/>
  </resultMap>

  <sql id="Base_Column_List">
    CONNRECORDSEQ,PORTID,PORTNAME,
        ENTERPRISESNAME,ENTERPRISESID,OPERATORNAME,
        OPERATORID,CONNECTORID,BERTHID,
        SHIPNAME,SHIPNUMBER,SHIPGROSSTONNAGE,
        SHIPHOMEPORT,USERCHARGETYPE,MOBILENUMBER,
        STARTTIME,ENDTIME,DURATION,
        ELECT,MONEY,ELECTMONEY,
        SERVICEMONEY,PAYMENTAMOUNT,PAYTIME,
        PAYCHANNEL,MODIFYTIME,CONNTYPE
  </sql>

  <select id="selectConnRecordInfoListSync" resultType="com.shanghaichengdi.ghjgitem.domain.ShorePowerConnRecordInfo">
    SELECT t.CONNRECORDSEQ,
           t.PORTNAME,
           t.ENTERPRISESNAME,
           t.OPERATORNAME,
           t.SHIPNAME,
           t.SHIPNUMBER,
           t.SHIPGROSSTONNAGE,
           t.SHIPHOMEPORT,
           t.USERCHARGETYPE,
           t.MOBILENUMBER,
           t.STARTTIME,
           t.ENDTIME,
           t.DURATION,
           t.ELECT,
           t.money,
           t.ELECTMONEY,
           t.SERVICEMONEY,
           t.PAYMENTAMOUNT,
           t.PAYTIME,
           t.PAYCHANNEL,
           t.MODIFYTIME,
           t.CONNTYPE,
           to_char(BERTHINFO.BERTHNAME) berthName,
           to_char(BERTHTYPE.NAME)      berthType,
           to_char(BERTHINFO.CAPACITY)  capacity,
           CASE
             WHEN T.SHIPGROSSTONNAGE = 1 THEN
               '600吨以上'
             WHEN T.SHIPGROSSTONNAGE = 2 THEN
               '200吨-600吨'
             WHEN T.SHIPGROSSTONNAGE = 3 THEN
               '200吨以下'
             END AS                     shipGrossTonnageName
    FROM GHJG_PORT_MANAGE.SHORE_POWER_CONN_RECORD_INFO T
           LEFT JOIN GHJG_PORT_MANAGE.SHORE_POWER_PORT_INFO_NEW PORT
                     ON PORT.PORTID = T.PORTID
           LEFT JOIN GHJG_PORT_MANAGE.SHORE_POWER_AREA_PORTID_DICT DICT
                     ON PORT.AREA = DICT.AREA_CODE
           LEFT JOIN GHJG_PORT_MANAGE.SHORE_POWER_BERTH_INFO_NEW BERTHINFO
                     ON BERTHINFO.BERTHID = T.BERTHID
           LEFT JOIN GHJG_PORT_MANAGE.SHORE_POWER_BERTH_TYPE_DICT BERTHTYPE
                     ON BERTHINFO.BERTHTYPE = BERTHTYPE.CODE
    WHERE (T.CONNTYPE = '低压(标准化)'
      AND PORT.AREA is not null
      and PORT.AREA != 1
      AND PORT.PORTID != '1146')
      AND ((T.ENDTIME >=
            TO_DATE(#{startTime} || '00:00:00', 'yyyy-MM-dd HH24:mi:ss')
      AND T.ENDTIME &lt;
          to_date(#{endTime} || '23:59:59', 'yyyy-MM-dd HH24:mi:ss'))
      )
      and port.area = 4
  </select>

</mapper>
