<?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.yami.shop.dao.PointsFailureRecordMapper">


    <select id="groupByCode" resultType="com.yami.shop.bean.vo.PointsFailureRecordVO">
        SELECT
        '导入积分' AS name,
        p.code,
        max(p.create_time) AS createTime,
        p.create_by AS createBy,
        p.channel_name AS channelName,
        COUNT(IF(p.recharge_status = 1, 1, NULL)) AS successStatus,
        COUNT(IF(p.recharge_status = 0, 1, NULL)) AS failureStatus,
        u.username AS createByName,
        r.role_name AS createByRole
        from tz_points_failure_record p
        left join tz_sys_user u on p.create_by = u.user_id
        left join tz_sys_user_role ur on u.user_id = ur.user_id
        left join tz_sys_role r on ur.role_id = r.role_id
        <where>
            <if test="channelId != null">p.channel_id = #{channelId}</if>
        </where>
        GROUP BY p.code, p.create_by, p.channel_name, u.username, r.role_name
    </select>
</mapper>
