<?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.ruoyi.frequency.auth.mapper.AuthMapper">

    <resultMap type="com.ruoyi.frequency.auth.entity.Auth" id="AuthResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="idFront"    column="id_front"    />
        <result property="idBack"    column="id_back"    />
        <result property="idHandHand"    column="id_hand_hand"    />
        <result property="name"    column="name"    />
        <result property="idcardNo"    column="idcard_no"    />
        <result property="sex"    column="sex"    />
        <result property="practiceDate"    column="practice_date"    />
        <result property="specialityArea"    column="speciality_area"    />
        <result property="result"    column="result"    />
        <result property="bank"    column="bank"    />
        <result property="subbranch"    column="subbranch"    />
        <result property="bankCard"    column="bank_card"    />
        <result property="amount"    column="amount"    />
        <result property="reason"    column="reason"    />
        <result property="storeId"    column="store_id"    />
        <result property="wecomQrcode"    column="wecom_qrcode"    />
        <result property="area"    column="area"    />
        <result property="type"    column="type"    />
        <result property="branchWork"    column="branch_work"    />
    </resultMap>

    <sql id="selectAuthVo">
        SELECT
            t.id,
            t.branch_work,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.id_front,
            t.id_back,
            t.id_hand_hand,
            t.`name`,
            t.idcard_no,
            t.sex,
            t.practice_date,
            t.speciality_area,
            t.result,
            t.bank,
            t.subbranch,
            t.bank_card,
            t.amount,
            t.reason,
            t.store_id,
            t.is_ordinary_pay,
            t.pay_order_no,
            t.pay_status,
            t.pay_type,
            t.ali_business_id,
            s.wecom_qrcode,
            t.area,
            t.type
        FROM
            t_auth AS t
                LEFT JOIN t_store AS s ON s.id = t.store_id
    </sql>

    <select id="selectAuthList" parameterType="com.ruoyi.frequency.auth.entity.Auth" resultMap="AuthResult">
        <include refid="selectAuthVo"/>
        <where> and t.del_flag != 2 and t.pay_status = 2
            <if test="createTime != null "> and t.create_time = #{createTime}</if>
            <if test="idFront != null  and idFront != ''"> and t.id_front = #{idFront}</if>
            <if test="idBack != null  and idBack != ''"> and t.id_back = #{idBack}</if>
            <if test="idHandHand != null  and idHandHand != ''"> and t.id_hand_hand = #{idHandHand}</if>
            <if test="name != null  and name != ''"> and t.name like concat('%', #{name}, '%')</if>
            <if test="idcardNo != null  and idcardNo != ''"> and t.idcard_no like concat('%', #{idcardNo}, '%')</if>
            <if test="sex != null  and sex != ''"> and t.sex = #{sex}</if>
            <if test="practiceDate != null "> and t.practice_date = #{practiceDate}</if>
            <if test="specialityArea != null  and specialityArea != ''"> and t.speciality_area = #{specialityArea}</if>
            <if test="result != null "> and t.result = #{result}</if>
            <if test="bank != null  and bank != ''"> and t.bank = #{bank}</if>
            <if test="subbranch != null  and subbranch != ''"> and t.subbranch = #{subbranch}</if>
            <if test="bankCard != null  and bankCard != ''"> and t.bank_card like concat('%', #{bankCard}, '%')</if>
            <if test="amount != null "> and t.amount = #{amount}</if>
            <if test="reason != null  and reason != ''"> and t.reason = #{reason}</if>
            <if test="storeId != null "> and t.store_id = #{storeId}</if>
            <if test="userInfo != null and userInfo.nickname != null  and userInfo.nickname != '' ">
                and s.nickname like concat('%', #{userInfo.nickname}, '%')
            </if>
            <if test="userInfo != null and  userInfo.mobile != null and  userInfo.mobile != '' ">
                and s.mobile like concat('%', #{userInfo.mobile}, '%')
            </if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>
            <choose>
                <when test="type != null and type != ''">
                    and t.type = #{type}
                </when>
                <otherwise>
                    and t.type = 1
                </otherwise>
            </choose>
        </where>
        order by t.create_time desc
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.auth.entity.Auth" resultType="com.ruoyi.frequency.auth.model.result.AuthCount">
        select (SELECT
        count(distinct t.store_id) AS number
        FROM
        t_auth AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        ) number,
        (SELECT
        count(distinct t.store_id) AS number
        FROM
        t_auth AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        and t.result = 2
        ) passNumber,
        (SELECT
        count(distinct t.store_id) AS number
        FROM
        t_auth AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        and t.result = 3
        ) noPassNumber,
        (SELECT
        count(distinct t.store_id) AS number
        FROM
        t_auth AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        and t.result = 1
        ) auditNumber
    </select>

    <select id="selectAuthById" parameterType="Long" resultMap="AuthResult">
        <include refid="selectAuthVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertAuth" parameterType="com.ruoyi.frequency.auth.entity.Auth">
        insert into t_auth
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="idFront != null">id_front,</if>
            <if test="idBack != null">id_back,</if>
            <if test="idHandHand != null">id_hand_hand,</if>
            <if test="name != null">name,</if>
            <if test="idcardNo != null">idcard_no,</if>
            <if test="sex != null">sex,</if>
            <if test="practiceDate != null">practice_date,</if>
            <if test="specialityArea != null">speciality_area,</if>
            <if test="result != null">result,</if>
            <if test="bank != null">bank,</if>
            <if test="subbranch != null">subbranch,</if>
            <if test="bankCard != null">bank_card,</if>
            <if test="amount != null">amount,</if>
            <if test="reason != null">reason,</if>
            <if test="storeId != null">store_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="idFront != null">#{idFront},</if>
            <if test="idBack != null">#{idBack},</if>
            <if test="idHandHand != null">#{idHandHand},</if>
            <if test="name != null">#{name},</if>
            <if test="idcardNo != null">#{idcardNo},</if>
            <if test="sex != null">#{sex},</if>
            <if test="practiceDate != null">#{practiceDate},</if>
            <if test="specialityArea != null">#{specialityArea},</if>
            <if test="result != null">#{result},</if>
            <if test="bank != null">#{bank},</if>
            <if test="subbranch != null">#{subbranch},</if>
            <if test="bankCard != null">#{bankCard},</if>
            <if test="amount != null">#{amount},</if>
            <if test="reason != null">#{reason},</if>
            <if test="storeId != null">#{storeId},</if>
        </trim>
    </insert>

    <update id="updateAuth" parameterType="com.ruoyi.frequency.auth.entity.Auth">
        update t_auth
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="idFront != null">id_front = #{idFront},</if>
            <if test="idBack != null">id_back = #{idBack},</if>
            <if test="idHandHand != null">id_hand_hand = #{idHandHand},</if>
            <if test="name != null">name = #{name},</if>
            <if test="idcardNo != null">idcard_no = #{idcardNo},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="practiceDate != null">practice_date = #{practiceDate},</if>
            <if test="specialityArea != null">speciality_area = #{specialityArea},</if>
            <if test="result != null">result = #{result},</if>
            <if test="bank != null">bank = #{bank},</if>
            <if test="subbranch != null">subbranch = #{subbranch},</if>
            <if test="bankCard != null">bank_card = #{bankCard},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="reason != null">reason = #{reason},</if>
            <if test="storeId != null">store_id = #{storeId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteAuthById" parameterType="Long">
        delete from t_auth where id = #{id}
    </delete>

    <delete id="deleteAuthByIds" parameterType="String">
        delete from t_auth where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
