<?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.liquidnet.service.account.wallet.mapper.FinBizTransMapper" >
  <resultMap id="BaseResultMap" type="com.liquidnet.service.account.wallet.entity.FinBizTrans" >
    <result column="FBT0" property="transNo" jdbcType="VARCHAR" />
    <result column="FBT1" property="walletNo" jdbcType="VARCHAR" />
    <result column="FBT2" property="finId" jdbcType="VARCHAR" />
    <result column="FBT3" property="amount" jdbcType="DECIMAL" />
    <result column="FBT4" property="transType" jdbcType="VARCHAR" />
    <result column="FBT5" property="transStatus" jdbcType="INTEGER" />
    <result column="FBT6" property="tradeNo" jdbcType="VARCHAR" />
    <result column="FBT7" property="tracingNo" jdbcType="VARCHAR" />
    <result column="FBT8" property="tracingType" jdbcType="VARCHAR" />
    <result column="FBT9" property="tracingTime" jdbcType="TIMESTAMP" />
    <result column="FBT10" property="tracingTitle" jdbcType="VARCHAR" />
    <result column="FBT11" property="tracingDesc" jdbcType="VARCHAR" />
    <result column="FBT12" property="createTime" jdbcType="TIMESTAMP" />
    <result column="FBT13" property="completeTime" jdbcType="TIMESTAMP" />
    <result column="FBT14" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="FBT15" property="remark" jdbcType="VARCHAR" />
    <result column="FBT16" property="reasons" jdbcType="VARCHAR" />
  </resultMap>
    <resultMap id="BaseTransDetails" type="com.liquidnet.service.account.wallet.bo.FinBizTransDetailBo">
        <id column="FBT0" property="transNo" jdbcType="VARCHAR"/>
        <result column="FBTE2" property="balanceAvailable" jdbcType="DECIMAL"/>
        <result column="FBTE3" property="balancePending" jdbcType="DECIMAL"/>
        <result column="FBTE4" property="balanceFrozen" jdbcType="DECIMAL"/>
        <result column="FBT4" property="transType" jdbcType="VARCHAR"/>
        <result column="FBT5" property="transStatus" jdbcType="INTEGER"/>
        <result column="FBT16" property="reasons" jdbcType="VARCHAR"/>
        <result column="FBT13" property="completeTime" jdbcType="TIMESTAMP"/>
        <result column="FBT1" property="walletNo" jdbcType="VARCHAR"/>
        <result column="FBT3" property="amount" jdbcType="DECIMAL"/>
        <result column="FBT8" property="tracingType" jdbcType="VARCHAR"/>
        <result column="FBT7" property="tracingNo" jdbcType="VARCHAR"/>
        <result column="FBT9" property="tracingTime" jdbcType="TIMESTAMP"/>
        <result column="FBT10" property="tracingTitle" jdbcType="VARCHAR"/>
        <result column="FBT11" property="tracingDesc" jdbcType="VARCHAR"/>
    </resultMap>
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause" >
    <where >
      <foreach collection="example.oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List" >
    FBT0, FBT1, FBT2, FBT3, FBT4, FBT5, FBT6,
    FBT7, FBT8, FBT9, FBT10, FBT11, FBT12, FBT13,
    FBT14, FBT15, FBT16
  </sql>
  <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTransExample" >
    select
    <if test="distinct" >
      distinct
    </if>
    <include refid="Base_Column_List" />
    from FIN_WL_N07
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null" >
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from FIN_WL_N07
    where FBT0 = #{transNo,jdbcType=VARCHAR}
  </select>
  <insert id="insert" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTrans" >
    insert into FIN_WL_N07 (FBT0, FBT1, FBT2,
      FBT3, FBT4, FBT5,
      FBT6, FBT7, FBT8,
      FBT9, FBT10, FBT11,
      FBT12, FBT13, FBT14,
      FBT15, FBT16)
    values (#{transNo,jdbcType=VARCHAR}, #{walletNo,jdbcType=VARCHAR}, #{finId,jdbcType=VARCHAR},
      #{amount,jdbcType=DECIMAL}, #{transType,jdbcType=VARCHAR}, #{transStatus,jdbcType=INTEGER},
      #{tradeNo,jdbcType=VARCHAR}, #{tracingNo,jdbcType=VARCHAR}, #{tracingType,jdbcType=VARCHAR},
      #{tracingTime,jdbcType=TIMESTAMP}, #{tracingTitle,jdbcType=VARCHAR}, #{tracingDesc,jdbcType=VARCHAR},
      #{createTime,jdbcType=TIMESTAMP}, #{completeTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP},
      #{remark,jdbcType=VARCHAR}, #{reasons,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTrans" >
    insert into FIN_WL_N07
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="transNo != null" >
        FBT0,
      </if>
      <if test="walletNo != null" >
        FBT1,
      </if>
      <if test="finId != null" >
        FBT2,
      </if>
      <if test="amount != null" >
        FBT3,
      </if>
      <if test="transType != null" >
        FBT4,
      </if>
      <if test="transStatus != null" >
        FBT5,
      </if>
      <if test="tradeNo != null" >
        FBT6,
      </if>
      <if test="tracingNo != null" >
        FBT7,
      </if>
      <if test="tracingType != null" >
        FBT8,
      </if>
      <if test="tracingTime != null" >
        FBT9,
      </if>
      <if test="tracingTitle != null" >
        FBT10,
      </if>
      <if test="tracingDesc != null" >
        FBT11,
      </if>
      <if test="createTime != null" >
        FBT12,
      </if>
      <if test="completeTime != null" >
        FBT13,
      </if>
      <if test="updateTime != null" >
        FBT14,
      </if>
      <if test="remark != null" >
        FBT15,
      </if>
      <if test="reasons != null" >
        FBT16,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="transNo != null" >
        #{transNo,jdbcType=VARCHAR},
      </if>
      <if test="walletNo != null" >
        #{walletNo,jdbcType=VARCHAR},
      </if>
      <if test="finId != null" >
        #{finId,jdbcType=VARCHAR},
      </if>
      <if test="amount != null" >
        #{amount,jdbcType=DECIMAL},
      </if>
      <if test="transType != null" >
        #{transType,jdbcType=VARCHAR},
      </if>
      <if test="transStatus != null" >
        #{transStatus,jdbcType=INTEGER},
      </if>
      <if test="tradeNo != null" >
        #{tradeNo,jdbcType=VARCHAR},
      </if>
      <if test="tracingNo != null" >
        #{tracingNo,jdbcType=VARCHAR},
      </if>
      <if test="tracingType != null" >
        #{tracingType,jdbcType=VARCHAR},
      </if>
      <if test="tracingTime != null" >
        #{tracingTime,jdbcType=TIMESTAMP},
      </if>
      <if test="tracingTitle != null" >
        #{tracingTitle,jdbcType=VARCHAR},
      </if>
      <if test="tracingDesc != null" >
        #{tracingDesc,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="completeTime != null" >
        #{completeTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="remark != null" >
        #{remark,jdbcType=VARCHAR},
      </if>
      <if test="reasons != null" >
        #{reasons,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTransExample" resultType="java.lang.Integer" >
    select count(*) from FIN_WL_N07
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map" >
    update FIN_WL_N07
    <set >
      <if test="record.transNo != null" >
        FBT0 = #{record.transNo,jdbcType=VARCHAR},
      </if>
      <if test="record.walletNo != null" >
        FBT1 = #{record.walletNo,jdbcType=VARCHAR},
      </if>
      <if test="record.finId != null" >
        FBT2 = #{record.finId,jdbcType=VARCHAR},
      </if>
      <if test="record.amount != null" >
        FBT3 = #{record.amount,jdbcType=DECIMAL},
      </if>
      <if test="record.transType != null" >
        FBT4 = #{record.transType,jdbcType=VARCHAR},
      </if>
      <if test="record.transStatus != null" >
        FBT5 = #{record.transStatus,jdbcType=INTEGER},
      </if>
      <if test="record.tradeNo != null" >
        FBT6 = #{record.tradeNo,jdbcType=VARCHAR},
      </if>
      <if test="record.tracingNo != null" >
        FBT7 = #{record.tracingNo,jdbcType=VARCHAR},
      </if>
      <if test="record.tracingType != null" >
        FBT8 = #{record.tracingType,jdbcType=VARCHAR},
      </if>
      <if test="record.tracingTime != null" >
        FBT9 = #{record.tracingTime,jdbcType=TIMESTAMP},
      </if>
      <if test="record.tracingTitle != null" >
        FBT10 = #{record.tracingTitle,jdbcType=VARCHAR},
      </if>
      <if test="record.tracingDesc != null" >
        FBT11 = #{record.tracingDesc,jdbcType=VARCHAR},
      </if>
      <if test="record.createTime != null" >
        FBT12 = #{record.createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="record.completeTime != null" >
        FBT13 = #{record.completeTime,jdbcType=TIMESTAMP},
      </if>
      <if test="record.updateTime != null" >
        FBT14 = #{record.updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="record.remark != null" >
        FBT15 = #{record.remark,jdbcType=VARCHAR},
      </if>
      <if test="record.reasons != null" >
        FBT16 = #{record.reasons,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map" >
    update FIN_WL_N07
    set FBT0 = #{record.transNo,jdbcType=VARCHAR},
      FBT1 = #{record.walletNo,jdbcType=VARCHAR},
      FBT2 = #{record.finId,jdbcType=VARCHAR},
      FBT3 = #{record.amount,jdbcType=DECIMAL},
      FBT4 = #{record.transType,jdbcType=VARCHAR},
      FBT5 = #{record.transStatus,jdbcType=INTEGER},
      FBT6 = #{record.tradeNo,jdbcType=VARCHAR},
      FBT7 = #{record.tracingNo,jdbcType=VARCHAR},
      FBT8 = #{record.tracingType,jdbcType=VARCHAR},
      FBT9 = #{record.tracingTime,jdbcType=TIMESTAMP},
      FBT10 = #{record.tracingTitle,jdbcType=VARCHAR},
      FBT11 = #{record.tracingDesc,jdbcType=VARCHAR},
      FBT12 = #{record.createTime,jdbcType=TIMESTAMP},
      FBT13 = #{record.completeTime,jdbcType=TIMESTAMP},
      FBT14 = #{record.updateTime,jdbcType=TIMESTAMP},
      FBT15 = #{record.remark,jdbcType=VARCHAR},
      FBT16 = #{record.reasons,jdbcType=VARCHAR}
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTrans">
    update FIN_WL_N07
    <set>
      <if test="walletNo != null">
        FBT1 = #{walletNo,jdbcType=VARCHAR},
      </if>
      <if test="finId != null">
        FBT2 = #{finId,jdbcType=VARCHAR},
      </if>
      <if test="amount != null">
        FBT3 = #{amount,jdbcType=DECIMAL},
      </if>
      <if test="transType != null">
        FBT4 = #{transType,jdbcType=VARCHAR},
      </if>
      <if test="transStatus != null">
        FBT5 = #{transStatus,jdbcType=INTEGER},
      </if>
      <if test="tradeNo != null">
        FBT6 = #{tradeNo,jdbcType=VARCHAR},
      </if>
      <if test="tracingNo != null">
        FBT7 = #{tracingNo,jdbcType=VARCHAR},
      </if>
      <if test="tracingType != null">
        FBT8 = #{tracingType,jdbcType=VARCHAR},
      </if>
      <if test="tracingTime != null">
        FBT9 = #{tracingTime,jdbcType=TIMESTAMP},
      </if>
      <if test="tracingTitle != null">
        FBT10 = #{tracingTitle,jdbcType=VARCHAR},
      </if>
      <if test="tracingDesc != null">
        FBT11 = #{tracingDesc,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null">
        FBT12 = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="completeTime != null">
        FBT13 = #{completeTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null">
        FBT14 = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="remark != null">
        FBT15 = #{remark,jdbcType=VARCHAR},
      </if>
      <if test="reasons != null">
        FBT16 = #{reasons,jdbcType=VARCHAR},
      </if>
    </set>
    where FBT0 = #{transNo,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.liquidnet.service.account.wallet.entity.FinBizTrans">
    update FIN_WL_N07
    set FBT1 = #{walletNo,jdbcType=VARCHAR},
      FBT2 = #{finId,jdbcType=VARCHAR},
      FBT3 = #{amount,jdbcType=DECIMAL},
      FBT4 = #{transType,jdbcType=VARCHAR},
      FBT5 = #{transStatus,jdbcType=INTEGER},
      FBT6 = #{tradeNo,jdbcType=VARCHAR},
      FBT7 = #{tracingNo,jdbcType=VARCHAR},
      FBT8 = #{tracingType,jdbcType=VARCHAR},
      FBT9 = #{tracingTime,jdbcType=TIMESTAMP},
      FBT10 = #{tracingTitle,jdbcType=VARCHAR},
      FBT11 = #{tracingDesc,jdbcType=VARCHAR},
      FBT12 = #{createTime,jdbcType=TIMESTAMP},
      FBT13 = #{completeTime,jdbcType=TIMESTAMP},
      FBT14 = #{updateTime,jdbcType=TIMESTAMP},
      FBT15 = #{remark,jdbcType=VARCHAR},
      FBT16 = #{reasons,jdbcType=VARCHAR}
    where FBT0 = #{transNo,jdbcType=VARCHAR}
  </update>


    <select id="selectTransToBeCalculated" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM FIN_WL_N07 T
        WHERE NOT EXISTS(SELECT TE.FBTE0 FROM FIN_WL_N08 TE WHERE TE.FBTE0 = T.FBT0)
    </select>
    <select id="selectTransDetails" resultMap="BaseTransDetails" parameterType="java.util.Map">
        SELECT T.FBT0,
               TE.FBTE2,
               TE.FBTE3,
               TE.FBTE4,
               T.FBT4,
               T.FBT5,
               T.FBT16,
               T.FBT13,
               T.FBT1,
               T.FBT3,
               T.FBT8,
               T.FBT7,
               T.FBT9,
               T.FBT10,
               T.FBT11
        FROM FIN_WL_N07 T,
             FIN_WL_N08 TE
        <where>
            <if test="transStatusEqualTo != null and transStatusEqualTo != ''">
                AND T.FBT5 = #{transStatusEqualTo,jdbcType=INTEGER}
            </if>
            <if test="receivedEqualTo != null and receivedEqualTo != ''">
                AND TE.FBTE5 = #{receivedEqualTo,jdbcType=INTEGER}
            </if>
            <if test="transNoIn != null and transNoIn.size > 0">
                AND T.FBT0 IN
                <foreach collection="transNoIn" item="obj" open="(" close=")" separator=",">
                    #{obj,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="tracingNoIn != null and tracingNoIn.size > 0">
                AND T.FBT7 IN
                <foreach collection="tracingNoIn" item="obj" open="(" close=")" separator=",">
                    #{obj,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="balanceAvailableIsNotNull != null and balanceAvailableIsNotNull != ''">
                AND TE.FBTE2 IS NOT NULL
            </if>
            <if test="transNoLessThanOrEqualTo != null and transNoLessThanOrEqualTo != ''">
                AND T.FBT0 <![CDATA[<=]]> #{transNoLessThanOrEqualTo,jdbcType=VARCHAR}
            </if>
            <if test="walletNoEqualTo != null and walletNoEqualTo != ''">
                AND T.FBT1 = #{walletNoEqualTo,jdbcType=VARCHAR}
            </if>

            AND T.FBT0 = TE.FBTE0
        </where>
        <if test="orderByClause != null and orderByClause != ''">
            ORDER BY #{orderByClause,jdbcType=VARCHAR}
        </if>
    </select>
    <select id="selectTransBalanceStats" resultType="java.util.Map" parameterType="java.util.Map">
        SELECT unitId AS unitId,
               FBTE2  AS availableBalance,
               FBTE3  AS pendingBalance
        FROM (
                     SELECT T.FBT1,
                            T.FBT9,
                            DATE_FORMAT(T.FBT9, #{groupingDateFormat,jdbcType=VARCHAR}) AS unitId,
                            TE.FBTE2,
                            TE.FBTE3
                     FROM FIN_WL_N07 T,
                          FIN_WL_N08 TE
                     WHERE T.FBT0 = TE.FBTE0
                <if test="tracingTimeGreaterThanOrEqualTo != null">
                    AND T.FBT9 <![CDATA[>=]]> #{tracingTimeGreaterThanOrEqualTo,jdbcType=TIMESTAMP}
                </if>
                <if test="tracingTimeLessThan != null">
                    AND T.FBT9 <![CDATA[<]]> #{tracingTimeLessThan,jdbcType=TIMESTAMP}
                </if>
                <if test="walletNoEqualTo != null and walletNoEqualTo != ''">
                    AND T.FBT1 = #{walletNoEqualTo,jdbcType=VARCHAR}
                </if>
        ORDER BY T.FBT0 DESC, T.FBT9 DESC
        LIMIT 10000
        ) K
        GROUP BY FBT1, unitId
    </select>
</mapper>
