| | |
| | | <result column="umd_updatetime" property="updateTime" jdbcType="TIMESTAMP" /> |
| | | <!-- 老版明细适用该字段 --> |
| | | <result column="state" property="state" jdbcType="INTEGER" /> |
| | | <result column="umd_order_type" property="orderType" jdbcType="INTEGER" /> |
| | | <association property="userInfo" column="umd_uid" |
| | | javaType="com.yeshi.fanli.entity.bus.user.UserInfo"> |
| | | <id column="umd_uid" property="id" jdbcType="BIGINT" /> |
| | | </association> |
| | | |
| | | </resultMap> |
| | | |
| | | |
| | | <resultMap id="UserMonthMoneyMap" type="com.yeshi.fanli.vo.money.UserMonthMoneyVO"> |
| | | <result column="expend" property="expend" jdbcType="DECIMAL" /> |
| | | <result column="income" property="income" jdbcType="DECIMAL" /> |
| | | <result column="dateFormate" property="dateFormate" jdbcType="VARCHAR" /> |
| | | </resultMap> |
| | | |
| | | <sql id="Base_Column_List">umd_id,umd_uid,umd_money,umd_type,umd_title,umd_sub_title,umd_desc_info,umd_source_identify_id,umd_identify_code,umd_beizhu,umd_createtime,umd_updatetime |
| | | <sql id="Base_Column_List">umd_id,umd_uid,umd_money,umd_type,umd_title,umd_sub_title,umd_desc_info,umd_source_identify_id,umd_identify_code,umd_beizhu,umd_createtime,umd_updatetime,umd_order_type |
| | | </sql> |
| | | <select id="selectByPrimaryKey" resultMap="BaseResultMap" |
| | | parameterType="java.lang.Long"> |
| | |
| | | <include refid="Base_Column_List" /> |
| | | from yeshi_ec_user_money_detail where umd_id = #{id,jdbcType=BIGINT} |
| | | </select> |
| | | |
| | | |
| | | <select id="selectByUidWithIndexId" resultMap="BaseResultMap"> |
| | | SELECT |
| | | <include refid="Base_Column_List" /> |
| | | FROM yeshi_ec_user_money_detail d WHERE d.`umd_createtime` |
| | | <![CDATA[ |
| | | FROM yeshi_ec_user_money_detail d WHERE d.`umd_createtime` <![CDATA[ |
| | | <= |
| | | ]]> |
| | | (SELECT |
| | | d.`umd_createtime` FROM yeshi_ec_user_money_detail d WHERE |
| | | (SELECT d.`umd_createtime` FROM yeshi_ec_user_money_detail d WHERE |
| | | d.`umd_id`=#{id}) and d.umd_uid=#{uid} order by d.`umd_createtime` |
| | | desc,d.umd_id desc limit #{count} |
| | | </select> |
| | | |
| | | <select id="selectCountByUid" resultType="java.lang.Long" |
| | | parameterType="java.lang.Long"> |
| | | SELECT count(umd_id) FROM yeshi_ec_user_money_detail |
| | | where umd_uid=#{uid} |
| | | </select> |
| | | |
| | | |
| | | |
| | | parameterType="java.lang.Long">SELECT count(umd_id) FROM yeshi_ec_user_money_detail |
| | | where umd_uid=#{uid}</select> |
| | | <select id="selectByMaxCreateTime" resultMap="BaseResultMap"> |
| | | SELECT |
| | | <include refid="Base_Column_List" /> |
| | | FROM yeshi_ec_user_money_detail d WHERE d.umd_uid=#{uid} and |
| | | d.`umd_createtime` |
| | | <![CDATA[ |
| | | d.`umd_createtime`<![CDATA[ |
| | | <= |
| | | ]]> |
| | | #{date} |
| | | order by d.`umd_createtime` desc,d.umd_id desc limit #{count} |
| | | #{date} order by d.`umd_createtime` desc,d.umd_id desc limit #{count} |
| | | </select> |
| | | |
| | | <select id="selectCountByUidAndMaxCreateTime" resultType="java.lang.Long"> |
| | | SELECT count(umd_id) FROM yeshi_ec_user_money_detail |
| | | where |
| | | umd_uid=#{uid} and `umd_createtime` |
| | | <![CDATA[ |
| | | <select id="selectCountByUidAndMaxCreateTime" resultType="java.lang.Long">SELECT |
| | | count(umd_id) FROM yeshi_ec_user_money_detail where umd_uid=#{uid} and |
| | | `umd_createtime`<![CDATA[ |
| | | <= |
| | | ]]> |
| | | #{date} |
| | | </select> |
| | | |
| | | |
| | | <select id="selectMonthCountByUid" resultType="java.lang.Integer"> |
| | | SELECT COUNT(*) FROM (SELECT * FROM yeshi_ec_user_money_detail d WHERE |
| | | <select id="selectMonthCountByUid" resultType="java.lang.Integer">SELECT |
| | | COUNT(*) FROM (SELECT * FROM yeshi_ec_user_money_detail d WHERE |
| | | d.`umd_uid`=#{uid} and d.`umd_createtime` <![CDATA[<=]]> |
| | | #{date} group by DATE_FORMAT(d.`umd_createtime`,'%y-%m')) a |
| | | </select> |
| | | |
| | | <select id="selectMonthMoneyByUid" resultMap="UserMonthMoneyMap"> |
| | | <foreach collection="dateFormat" index="index" item="item" |
| | | separator="UNION ALL"> |
| | | <trim prefix="(" suffix=")"> |
| | | SELECT |
| | | c.time as dateFormate , if(a.money is null,0,a.money) as |
| | | income ,if(b.money is null,0,b.money) as expend |
| | | FROM |
| | | |
| | | (select #{item} |
| | | as `time`) c |
| | | |
| | | left join |
| | | |
| | | ( |
| | | SELECT |
| | | DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m') |
| | | AS |
| | | `time`,SUM(d.`umd_money`) |
| | | AS money |
| | | FROM |
| | | `yeshi_ec_user_money_detail` |
| | | d |
| | | WHERE d.`umd_uid`=#{uid} |
| | | AND |
| | | d.`umd_money`>=0 AND DATE_FORMAT( |
| | | SELECT c.time as dateFormate , if(a.money is null,0,a.money) as |
| | | income ,if(b.money is null,0,b.money) as expend FROM (select #{item} |
| | | as `time`) c left join ( SELECT DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m') AS `time`,SUM(d.`umd_money`) AS money |
| | | FROM `yeshi_ec_user_money_detail` d WHERE d.`umd_uid`=#{uid} AND |
| | | d.`umd_money`>=0 AND DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m')=#{item} GROUP BY DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m') |
| | | ) a |
| | | |
| | | on a.time=c.time |
| | | |
| | | LEFT JOIN |
| | | ( |
| | | SELECT |
| | | DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m') AS |
| | | `time`,SUM(d.`umd_money`) |
| | | AS money |
| | | FROM |
| | | `yeshi_ec_user_money_detail` |
| | | d |
| | | WHERE d.`umd_uid`=#{uid} |
| | | AND |
| | | d.`umd_money` <![CDATA[<0]]> |
| | | AND DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m')=#{item} GROUP BY |
| | | DATE_FORMAT( |
| | | d.`umd_createtime`,'%Y-%m') |
| | | ) b ON c.time=b.time |
| | | d.`umd_createtime`,'%Y-%m') ) a on a.time=c.time LEFT JOIN ( SELECT |
| | | DATE_FORMAT( d.`umd_createtime`,'%Y-%m') AS |
| | | `time`,SUM(d.`umd_money`) AS money FROM `yeshi_ec_user_money_detail` |
| | | d WHERE d.`umd_uid`=#{uid} AND d.`umd_money`<![CDATA[<0]]> |
| | | AND DATE_FORMAT( d.`umd_createtime`,'%Y-%m')=#{item} GROUP BY |
| | | DATE_FORMAT( d.`umd_createtime`,'%Y-%m') ) b ON c.time=b.time |
| | | </trim> |
| | | </foreach> |
| | | </select> |
| | | |
| | | |
| | | <select id="selectByUidWithState" resultMap="BaseResultMap"> |
| | | SELECT a.* FROM |
| | | ((SELECT d.*,-1 AS state FROM |
| | | `yeshi_ec_user_money_detail` d |
| | | WHERE |
| | | <select id="selectByUidWithState" resultMap="BaseResultMap">SELECT a.* FROM |
| | | ((SELECT d.*,-1 AS state FROM `yeshi_ec_user_money_detail` d WHERE |
| | | d.`umd_uid`=#{uid} AND (d.`umd_type`!='extract' AND |
| | | d.`umd_type`!='extractReject') |
| | | ) |
| | | UNION ALL( |
| | | SELECT d.*,-1 AS |
| | | state |
| | | FROM |
| | | `yeshi_ec_user_money_detail` d LEFT JOIN |
| | | `yeshi_ec_extract` e |
| | | ON |
| | | d.`umd_type`!='extractReject') ) UNION ALL( SELECT d.*,-1 AS state |
| | | FROM `yeshi_ec_user_money_detail` d LEFT JOIN `yeshi_ec_extract` e ON |
| | | e.`id`=d.umd_source_identify_id WHERE d.`umd_uid`=#{uid} AND |
| | | d.umd_type='extractReject' |
| | | ) |
| | | UNION ALL( |
| | | SELECT |
| | | d.*,e.state FROM |
| | | `yeshi_ec_user_money_detail` d LEFT JOIN |
| | | `yeshi_ec_extract` e ON |
| | | d.umd_type='extractReject' ) UNION ALL( SELECT d.*,e.state FROM |
| | | `yeshi_ec_user_money_detail` d LEFT JOIN `yeshi_ec_extract` e ON |
| | | e.`id`=d.`umd_source_identify_id` WHERE d.`umd_uid`=#{uid} AND |
| | | d.umd_type='extract' |
| | | )) a ORDER BY a.umd_createtime DESC LIMIT |
| | | #{start},#{count} |
| | | |
| | | </select> |
| | | |
| | | |
| | | <select id="selectCountByUidWithState" resultType="java.lang.Long"> |
| | | SELECT |
| | | count(*) FROM |
| | | ((SELECT d.*,-1 AS state FROM |
| | | `yeshi_ec_user_money_detail` d |
| | | WHERE |
| | | d.`umd_uid`=#{uid} AND |
| | | (d.`umd_type`!='extract' AND d.`umd_type`!='extractReject') |
| | | ) |
| | | UNION ALL( |
| | | SELECT d.*,-1 AS |
| | | state |
| | | FROM `yeshi_ec_user_money_detail` d LEFT JOIN |
| | | `yeshi_ec_extract` e |
| | | ON |
| | | e.`id`=d.umd_source_identify_id WHERE |
| | | d.`umd_uid`=#{uid} AND |
| | | d.umd_type='extractReject' |
| | | ) |
| | | UNION ALL( |
| | | SELECT |
| | | d.umd_type='extract' )) a ORDER BY a.umd_createtime DESC LIMIT |
| | | #{start},#{count}</select> |
| | | <select id="selectCountByUidWithState" resultType="java.lang.Long">SELECT |
| | | count(*) FROM ((SELECT d.*,-1 AS state FROM |
| | | `yeshi_ec_user_money_detail` d WHERE d.`umd_uid`=#{uid} AND |
| | | (d.`umd_type`!='extract' AND d.`umd_type`!='extractReject') ) UNION |
| | | ALL( SELECT d.*,-1 AS state FROM `yeshi_ec_user_money_detail` d LEFT |
| | | JOIN `yeshi_ec_extract` e ON e.`id`=d.umd_source_identify_id WHERE |
| | | d.`umd_uid`=#{uid} AND d.umd_type='extractReject' ) UNION ALL( SELECT |
| | | d.*,e.state FROM `yeshi_ec_user_money_detail` d LEFT JOIN |
| | | `yeshi_ec_extract` e ON e.`id`=d.`umd_source_identify_id` WHERE |
| | | d.`umd_uid`=#{uid} AND |
| | | d.umd_type='extract' |
| | | )) a |
| | | |
| | | </select> |
| | | |
| | | |
| | | <select id="getTotalMoneyByTypeAndUidWithDate" resultType="java.math.BigDecimal"> |
| | | |
| | | select sum(umd_money) from yeshi_ec_user_money_detail where |
| | | umd_uid=#{uid} and umd_createtime>=#{minDate} and |
| | | umd_createtime<![CDATA[<]]>#{maxDate} |
| | | |
| | | d.`umd_uid`=#{uid} AND d.umd_type='extract' )) a</select> |
| | | <select id="getTotalMoneyByTypeAndUidWithDate" resultType="java.math.BigDecimal">select |
| | | sum(umd_money) from yeshi_ec_user_money_detail where umd_uid=#{uid} |
| | | and umd_createtime>=#{minDate} and umd_createtime<![CDATA[<]]>#{maxDate} |
| | | <foreach collection="typeList" item="item" open=" and (" |
| | | separator=" or " close=")"> |
| | | umd_type=#{item} |
| | | </foreach> |
| | | |
| | | separator=" or " close=")">umd_type=#{item}</foreach> |
| | | </select> |
| | | |
| | | |
| | | |
| | | <select id="selectByTypeAndUidAndIdentifyCode" resultMap="BaseResultMap"> |
| | | select |
| | | <include refid="Base_Column_List" /> |
| | | from yeshi_ec_user_money_detail where |
| | | umd_uid=#{uid} and |
| | | from yeshi_ec_user_money_detail where umd_uid=#{uid} and |
| | | umd_type=#{type} and umd_source_identify_id=#{sourceIdentifyId} |
| | | </select> |
| | | |
| | | |
| | | <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">delete from |
| | | yeshi_ec_user_money_detail where umd_id = #{id,jdbcType=BIGINT} |
| | | </delete> |
| | | <insert id="insert" parameterType="com.yeshi.fanli.entity.money.UserMoneyDetail" |
| | | useGeneratedKeys="true" keyProperty="id">insert into |
| | | yeshi_ec_user_money_detail |
| | | (umd_id,umd_uid,umd_money,umd_type,umd_title,umd_sub_title,umd_desc_info,umd_source_identify_id,umd_identify_code,umd_beizhu,umd_createtime,umd_updatetime) |
| | | (umd_id,umd_uid,umd_money,umd_type,umd_title,umd_sub_title,umd_desc_info,umd_source_identify_id,umd_identify_code,umd_beizhu,umd_createtime,umd_updatetime,umd_order_type) |
| | | values |
| | | (#{id,jdbcType=BIGINT},#{userInfo.id,jdbcType=BIGINT},#{money,jdbcType=DECIMAL},#{type,jdbcType=VARCHAR},#{title,jdbcType=VARCHAR},#{subTitle,jdbcType=VARCHAR},#{descInfo,jdbcType=VARCHAR},#{sourceIdentifyId,jdbcType=BIGINT},#{identifyCode,jdbcType=VARCHAR},#{beiZhu,jdbcType=VARCHAR},#{createTime,jdbcType=TIMESTAMP},#{updateTime,jdbcType=TIMESTAMP}) |
| | | (#{id,jdbcType=BIGINT},#{userInfo.id,jdbcType=BIGINT},#{money,jdbcType=DECIMAL},#{type,jdbcType=VARCHAR},#{title,jdbcType=VARCHAR},#{subTitle,jdbcType=VARCHAR},#{descInfo,jdbcType=VARCHAR},#{sourceIdentifyId,jdbcType=BIGINT},#{identifyCode,jdbcType=VARCHAR},#{beiZhu,jdbcType=VARCHAR},#{createTime,jdbcType=TIMESTAMP},#{updateTime,jdbcType=TIMESTAMP},#{orderType,jdbcType=INTEGER}) |
| | | </insert> |
| | | <insert id="insertSelective" parameterType="com.yeshi.fanli.entity.money.UserMoneyDetail" |
| | | useGeneratedKeys="true" keyProperty="id"> |
| | |
| | | <if test="beiZhu != null">umd_beizhu,</if> |
| | | <if test="createTime != null">umd_createtime,</if> |
| | | <if test="updateTime != null">umd_updatetime,</if> |
| | | <if test="orderType != null">umd_order_type,</if> |
| | | </trim> |
| | | values |
| | | <trim prefix="(" suffix=")" suffixOverrides=","> |
| | |
| | | <if test="beiZhu != null">#{beiZhu,jdbcType=VARCHAR},</if> |
| | | <if test="createTime != null">#{createTime,jdbcType=TIMESTAMP},</if> |
| | | <if test="updateTime != null">#{updateTime,jdbcType=TIMESTAMP},</if> |
| | | <if test="orderType != null">#{orderType,jdbcType=INTEGER}</if> |
| | | </trim> |
| | | </insert> |
| | | <update id="updateByPrimaryKey" parameterType="com.yeshi.fanli.entity.money.UserMoneyDetail">update |
| | |
| | | #{identifyCode,jdbcType=VARCHAR},umd_beizhu = |
| | | #{beiZhu,jdbcType=VARCHAR},umd_createtime = |
| | | #{createTime,jdbcType=TIMESTAMP},umd_updatetime = |
| | | #{updateTime,jdbcType=TIMESTAMP} where umd_id = #{id,jdbcType=BIGINT} |
| | | #{updateTime,jdbcType=TIMESTAMP} ,umd_order_type |
| | | =#{orderType,jdbcType=INTEGER} where umd_id = #{id,jdbcType=BIGINT} |
| | | </update> |
| | | <update id="updateByPrimaryKeySelective" parameterType="com.yeshi.fanli.entity.money.UserMoneyDetail"> |
| | | update yeshi_ec_user_money_detail |
| | |
| | | <if test="beiZhu != null">umd_beizhu=#{beiZhu,jdbcType=VARCHAR},</if> |
| | | <if test="createTime != null">umd_createtime=#{createTime,jdbcType=TIMESTAMP},</if> |
| | | <if test="updateTime != null">umd_updatetime=#{updateTime,jdbcType=TIMESTAMP},</if> |
| | | <if test="orderType !=null">umd_order_type =#{orderType,jdbcType=INTEGER},</if> |
| | | </set> |
| | | where umd_id = #{id,jdbcType=BIGINT} |
| | | </update> |