<?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.cku.dao.CkuUserPayListMapper" >
  <resultMap id="BaseResultMap" type="com.cku.model.CkuUserPayList" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="user_id" property="userId" jdbcType="VARCHAR" />
    <result column="table_id" property="tableId" jdbcType="INTEGER" />
    <result column="money_type" property="moneyType" jdbcType="VARCHAR" />
    <result column="money_date" property="moneyDate" jdbcType="TIMESTAMP" />
    <result column="money_amount" property="moneyAmount" jdbcType="DECIMAL" />
    <result column="money_way" property="moneyWay" jdbcType="VARCHAR" />
    <result column="year_id" property="yearId" jdbcType="INTEGER" />
    <result column="running_number" property="runningNumber" jdbcType="VARCHAR" />
    <result column="pay_state" property="payState" jdbcType="CHAR" />
    <result column="state_time" property="stateTime" jdbcType="TIMESTAMP" />
    <result column="out_trade_no" property="outTradeNo" jdbcType="VARCHAR" />
    <result column="alipay_s" property="alipayS" jdbcType="VARCHAR" />
    <result column="payout_Remarks" property="payoutRemarks" jdbcType="VARCHAR" />
    <result column="add_time" property="addTime" jdbcType="TIMESTAMP" />
    <result column="edit_time" property="editTime" jdbcType="TIMESTAMP" />
    <result column="del_tag" property="delTag" jdbcType="CHAR" />
    <result column="sysPrice" property="sysprice" jdbcType="DECIMAL" />
    <result column="type" property="type" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.cku.model.CkuUserPayList" extends="BaseResultMap" >
    <result column="operater" property="operater" jdbcType="LONGVARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, user_id, table_id, money_type, money_date, money_amount, money_way, year_id, 
    running_number, pay_state, state_time, out_trade_no, alipay_s, payout_Remarks, add_time, 
    edit_time, del_tag, sysPrice
  </sql>
  <sql id="Blob_Column_List" >
    operater
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from cku_user_payList
    where id = #{id,jdbcType=INTEGER}
  </select>
  <select id="getCkuBalanceInfo" resultMap="BaseResultMap" parameterType="java.lang.Long" >
     SELECT
		   top ${limit} tab.*
		FROM
		   (
		      SELECT
		         patList.id,
		         patList.user_id,
		         patList.table_id,
		         (
		            CASE
		            WHEN patList.money_type = '0' THEN
		               '账户充值'
		            ELSE
		               payType.subject_name
		            END
		         ) money_type,
		         patList.money_type type,
		         patList.money_date,
		         patList.money_amount,
		         (
		            CASE
		            WHEN patList.money_way = 'cku.org' and patList.money_type in (163,84,157 ,158,156,155,148,146,145,143,147,199,144) THEN
		               '退费'
		            WHEN patList.money_way = 'cku.org' THEN
		               '余额支付'
		            WHEN patList.money_way = '1' THEN
		               '支付宝充值'
		            WHEN patList.money_way = '1' THEN
		               '支付宝充值'
		            WHEN patList.money_way = '2' THEN
		               '建设网银充值'
		            WHEN patList.money_way = '6' THEN
		               '兴业银行充值'
		            WHEN patList.money_way = '3' THEN
		               '现金缴纳'
		            WHEN patList.money_way = '21' THEN
		               '比赛现场收入'
		            WHEN patList.money_way = '22' THEN
		               '集中鉴定收入'
		            WHEN patList.money_way = '4' THEN
		               '邮局汇款'
		            WHEN patList.money_way = '5' THEN
		               '挂账转入'
		            WHEN patList.money_way = '8' THEN
		               '巡回收入'
		            WHEN patList.money_way = '9' THEN
		               '系统导入'
		            WHEN patList.money_way = '10' THEN
		               '其他会员转入'
		            WHEN patList.money_way = '20' THEN
		               '财务往来'
		            ELSE
		               '其他'
		            END
		         ) money_way,
		         patList.year_id,
		         patList.running_number,
		         patList.pay_state,
		         patList.state_time,
		         patList.out_trade_no,
		         patList.alipay_s,
		         patList.payout_Remarks,
		         patList.add_time
		      FROM
		         cku_user_paylist patList
		      LEFT JOIN cku_user_payType payType ON patList.money_type = payType.id
		   ) tab
		WHERE
		   id NOT IN (
		      SELECT
		         top ${start} pay.id
		      FROM
		         cku_user_paylist pay
		      WHERE
		         user_id = #{userId}
		      <if test="flag == 'in'">
		      AND money_type = '0'
		      </if>
		      <if test="flag == 'out'">
		      AND money_way = 'cku.org'
		      </if>
		      <if test="flag == 'all'">
		      AND (money_type = '0' or money_way = 'cku.org')
		      </if>
		      ORDER BY
		         id DESC
		   )
		<if test="flag == 'in'">
		AND type = '0'
		</if>
		<if test="flag == 'out'">
		AND (money_way = '余额支付' or money_way = '退费')
		</if>
		<if test="flag == 'all'">
		AND (type = '0' or money_way = '余额支付' or money_way = '退费')
		</if>
		AND user_id = #{userId}
		ORDER BY
		   id DESC
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from cku_user_payList
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cku.model.CkuUserPayList" >
    insert into cku_user_payList (id, user_id, table_id, 
      money_type, money_date, money_amount, 
      money_way, year_id, running_number, 
      pay_state, state_time, out_trade_no, 
      alipay_s, payout_Remarks, add_time, 
      edit_time, del_tag, sysPrice, 
      operater)
    values (#{id,jdbcType=INTEGER}, #{userId,jdbcType=VARCHAR}, #{tableId,jdbcType=INTEGER}, 
      #{moneyType,jdbcType=VARCHAR}, #{moneyDate,jdbcType=TIMESTAMP}, #{moneyAmount,jdbcType=DECIMAL}, 
      #{moneyWay,jdbcType=VARCHAR}, #{yearId,jdbcType=INTEGER}, #{runningNumber,jdbcType=VARCHAR}, 
      #{payState,jdbcType=CHAR}, #{stateTime,jdbcType=TIMESTAMP}, #{outTradeNo,jdbcType=VARCHAR}, 
      #{alipayS,jdbcType=VARCHAR}, #{payoutRemarks,jdbcType=VARCHAR}, #{addTime,jdbcType=TIMESTAMP}, 
      #{editTime,jdbcType=TIMESTAMP}, #{delTag,jdbcType=CHAR}, #{sysprice,jdbcType=DECIMAL}, 
      #{operater,jdbcType=LONGVARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.cku.model.CkuUserPayList" >
    insert into cku_user_payList
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
      <if test="tableId != null" >
        table_id,
      </if>
      <if test="moneyType != null" >
        money_type,
      </if>
      <if test="moneyDate != null" >
        money_date,
      </if>
      <if test="moneyAmount != null" >
        money_amount,
      </if>
      <if test="moneyWay != null" >
        money_way,
      </if>
      <if test="yearId != null" >
        year_id,
      </if>
      <if test="runningNumber != null" >
        running_number,
      </if>
      <if test="payState != null" >
        pay_state,
      </if>
      <if test="stateTime != null" >
        state_time,
      </if>
      <if test="outTradeNo != null" >
        out_trade_no,
      </if>
      <if test="alipayS != null" >
        alipay_s,
      </if>
      <if test="payoutRemarks != null" >
        payout_Remarks,
      </if>
      <if test="addTime != null" >
        add_time,
      </if>
      <if test="editTime != null" >
        edit_time,
      </if>
      <if test="delTag != null" >
        del_tag,
      </if>
      <if test="sysprice != null" >
        sysPrice,
      </if>
      <if test="operater != null" >
        operater,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=VARCHAR},
      </if>
      <if test="tableId != null" >
        #{tableId,jdbcType=INTEGER},
      </if>
      <if test="moneyType != null" >
        #{moneyType,jdbcType=VARCHAR},
      </if>
      <if test="moneyDate != null" >
        #{moneyDate,jdbcType=TIMESTAMP},
      </if>
      <if test="moneyAmount != null" >
        #{moneyAmount,jdbcType=DECIMAL},
      </if>
      <if test="moneyWay != null" >
        #{moneyWay,jdbcType=VARCHAR},
      </if>
      <if test="yearId != null" >
        #{yearId,jdbcType=INTEGER},
      </if>
      <if test="runningNumber != null" >
        #{runningNumber,jdbcType=VARCHAR},
      </if>
      <if test="payState != null" >
        #{payState,jdbcType=CHAR},
      </if>
      <if test="stateTime != null" >
        #{stateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="outTradeNo != null" >
        #{outTradeNo,jdbcType=VARCHAR},
      </if>
      <if test="alipayS != null" >
        #{alipayS,jdbcType=VARCHAR},
      </if>
      <if test="payoutRemarks != null" >
        #{payoutRemarks,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editTime != null" >
        #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="delTag != null" >
        #{delTag,jdbcType=CHAR},
      </if>
      <if test="sysprice != null" >
        #{sysprice,jdbcType=DECIMAL},
      </if>
      <if test="operater != null" >
        #{operater,jdbcType=LONGVARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cku.model.CkuUserPayList" >
    update cku_user_payList
    <set >
      <if test="userId != null" >
        user_id = #{userId,jdbcType=VARCHAR},
      </if>
      <if test="tableId != null" >
        table_id = #{tableId,jdbcType=INTEGER},
      </if>
      <if test="moneyType != null" >
        money_type = #{moneyType,jdbcType=VARCHAR},
      </if>
      <if test="moneyDate != null" >
        money_date = #{moneyDate,jdbcType=TIMESTAMP},
      </if>
      <if test="moneyAmount != null" >
        money_amount = #{moneyAmount,jdbcType=DECIMAL},
      </if>
      <if test="moneyWay != null" >
        money_way = #{moneyWay,jdbcType=VARCHAR},
      </if>
      <if test="yearId != null" >
        year_id = #{yearId,jdbcType=INTEGER},
      </if>
      <if test="runningNumber != null" >
        running_number = #{runningNumber,jdbcType=VARCHAR},
      </if>
      <if test="payState != null" >
        pay_state = #{payState,jdbcType=CHAR},
      </if>
      <if test="stateTime != null" >
        state_time = #{stateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="outTradeNo != null" >
        out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      </if>
      <if test="alipayS != null" >
        alipay_s = #{alipayS,jdbcType=VARCHAR},
      </if>
      <if test="payoutRemarks != null" >
        payout_Remarks = #{payoutRemarks,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        add_time = #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editTime != null" >
        edit_time = #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="delTag != null" >
        del_tag = #{delTag,jdbcType=CHAR},
      </if>
      <if test="sysprice != null" >
        sysPrice = #{sysprice,jdbcType=DECIMAL},
      </if>
      <if test="operater != null" >
        operater = #{operater,jdbcType=LONGVARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.cku.model.CkuUserPayList" >
    update cku_user_payList
    set user_id = #{userId,jdbcType=VARCHAR},
      table_id = #{tableId,jdbcType=INTEGER},
      money_type = #{moneyType,jdbcType=VARCHAR},
      money_date = #{moneyDate,jdbcType=TIMESTAMP},
      money_amount = #{moneyAmount,jdbcType=DECIMAL},
      money_way = #{moneyWay,jdbcType=VARCHAR},
      year_id = #{yearId,jdbcType=INTEGER},
      running_number = #{runningNumber,jdbcType=VARCHAR},
      pay_state = #{payState,jdbcType=CHAR},
      state_time = #{stateTime,jdbcType=TIMESTAMP},
      out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      alipay_s = #{alipayS,jdbcType=VARCHAR},
      payout_Remarks = #{payoutRemarks,jdbcType=VARCHAR},
      add_time = #{addTime,jdbcType=TIMESTAMP},
      edit_time = #{editTime,jdbcType=TIMESTAMP},
      del_tag = #{delTag,jdbcType=CHAR},
      sysPrice = #{sysprice,jdbcType=DECIMAL},
      operater = #{operater,jdbcType=LONGVARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cku.model.CkuUserPayList" >
    update cku_user_payList
    set user_id = #{userId,jdbcType=VARCHAR},
      table_id = #{tableId,jdbcType=INTEGER},
      money_type = #{moneyType,jdbcType=VARCHAR},
      money_date = #{moneyDate,jdbcType=TIMESTAMP},
      money_amount = #{moneyAmount,jdbcType=DECIMAL},
      money_way = #{moneyWay,jdbcType=VARCHAR},
      year_id = #{yearId,jdbcType=INTEGER},
      running_number = #{runningNumber,jdbcType=VARCHAR},
      pay_state = #{payState,jdbcType=CHAR},
      state_time = #{stateTime,jdbcType=TIMESTAMP},
      out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      alipay_s = #{alipayS,jdbcType=VARCHAR},
      payout_Remarks = #{payoutRemarks,jdbcType=VARCHAR},
      add_time = #{addTime,jdbcType=TIMESTAMP},
      edit_time = #{editTime,jdbcType=TIMESTAMP},
      del_tag = #{delTag,jdbcType=CHAR},
      sysPrice = #{sysprice,jdbcType=DECIMAL}
    where id = #{id,jdbcType=INTEGER}
  </update>
  
  <!-- 查询支付方式 -->
  <select id="getMoneyWay" parameterType="java.lang.String" resultType="java.lang.String">
  SELECT
	(
		CASE money_way
		WHEN '1' THEN '支付宝充值'
		WHEN '2' THEN '建行网银充值'
		WHEN '3' THEN '现金缴纳'
		WHEN '4' THEN '邮局汇款'
		WHEN '5' THEN '挂账转入'
		WHEN '6' THEN '兴业银行充值'
		WHEN '8' THEN '巡回收入'
		WHEN '9' THEN '系统导入'
		WHEN '10' THEN '其他会员转入'
		WHEN '20' THEN '财务往来'
		WHEN '21' THEN '比赛现场收入'
		WHEN '22' THEN '财务往来'
		WHEN 'cku.org' THEN '余额支付'
		END
	) AS moneyWay
FROM
	cku_user_payList
WHERE
	running_number = #{runningNumber,jdbcType=VARCHAR}
  </select>
  
</mapper>