<?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.crm.ucard.dao.mapper.CardMerchantUserMapper">

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.MerchantUserPageEntity">
        SELECT COUNT(*)
        from ucard_merchant_user um
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) u
        on um.country = u.code and u.card_category = 2
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) uc
        on um.town = uc.code and uc.card_category = 2
        left join ucard_occupations uo
        on uo.occupation_code = um.occupation
        left join ucard_wallet uw on uw.c_id = um.c_id
        left join sys_country sc on sc.code = um.country
        <where>
            <if test="merId!=null">
                and um.`merchant_id` = #{merId}
            </if>
            <if test="mobile!=null and mobile!=''">
                and um.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="email!=null and email!=''">
                and um.`email` like concat(#{email},'%')
            </if>
            <if test="lastName!=null and lastName!=''">
                and um.`last_name` like concat(#{lastName},'%')
            </if>
            <if test="firstName!=null and firstName!=''">
                and um.`first_name` like concat(#{firstName},'%')
            </if>
            <if test="postCode!=null and postCode!=''">
                and um.`post_code` = #{postCode}
            </if>
            <if test="cId!=null">
                and um.`c_id` = #{cId}
            </if>
            <if test="photoStatus != null and photoStatus > 0">
                <choose>
                    <when test="photoStatus == 2">
                        and um.`photo_status` is null
                    </when>
                    <otherwise>
                        and um.`photo_status` = #{photoStatus}
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.MerchantUserDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.MerchantUserPageEntity">
        select
        um.*,u.en_name as country_en_name,uc.en_name as town_en_name,uo.description as occupation_desc,uw.balance,
        COALESCE(sc.name, u.en_name) as country_cn_name
        from ucard_merchant_user um
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) u
        on um.country = u.code and u.card_category = 2
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) uc
        on um.town = uc.code and uc.card_category = 2
        left join ucard_occupations uo
        on uo.occupation_code = um.occupation
        left join ucard_wallet uw on uw.c_id = um.c_id
        left join sys_country sc on sc.code = um.country
        <where>
            <if test="merId!=null">
                and um.`merchant_id` = #{merId}
            </if>
            <if test="mobile!=null and mobile!=''">
                and um.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="email!=null and email!=''">
                and um.`email` like concat(#{email},'%')
            </if>
            <if test="lastName!=null and lastName!=''">
                and um.`last_name` like concat(#{lastName},'%')
            </if>
            <if test="firstName!=null and firstName!=''">
                and um.`first_name` like concat(#{firstName},'%')
            </if>
            <if test="postCode!=null and postCode!=''">
                and um.`post_code` = #{postCode}
            </if>
            <if test="cId!=null">
                and um.`c_id` = #{cId}
            </if>
            <if test="photoStatus != null and photoStatus > 0">
                <choose>
                    <when test="photoStatus == 2">
                        and um.`photo_status` is null
                    </when>
                    <otherwise>
                        and um.`photo_status` = #{photoStatus}
                    </otherwise>
                </choose>
            </if>
        </where>
        order by um.id desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="getCardTypeStatus" resultType="com.crm.rely.backend.core.dto.ucard.CardTypeUserDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardTypeEntity">
        SELECT a.c_id, c.holder_status AS holderStatus, c.`status` AS applyStatus, d.`status` AS cardStatus
        FROM ucard_merchant_user a
        LEFT JOIN ucard_apply c ON a.unique_id = c.unique_id
        LEFT JOIN ucard_card d ON c.card_no = d.card_no
        WHERE c.card_type_id = #{cardTypeId}
          AND a.c_id = #{cId}
          AND NOT (c.trade_type = 2 AND c.trade_status = 2)
        ORDER BY CASE
                     WHEN c.trade_status IN (1, 3) THEN 1
                     WHEN c.trade_type = 1 AND c.trade_status = 2 THEN 2
                     ELSE 3
                     END,
                 CASE
                     WHEN c.trade_type = 1 AND c.trade_status = 2 THEN c.add_time
                     END DESC
        LIMIT 1;
    </select>
    <select id="findAllByAuthSuccess"
            resultType="com.crm.rely.backend.core.dto.ucard.MerchantUserDto">
        select
         mu.`c_id`,
         mu.`unique_id`,
         mu.`area_code`,
         mu.`mobile`,
         mu.`email`,
         mu.`last_name` ,
         mu.`first_name`,
         mu.`birthday`,
         mu.`gender`,
         mu.`town`,
         mu.`address`,
         mu.`post_code`,
         mu.`occupation`,
         mu.`id_type`,
         mu.`id_number`,
         mu.`ssn`,
         u.standard_code as country,
         u.standard_code as nationality,
         u.en_name as country_en_name,
         COALESCE(sc.name, u.en_name) as country_cn_name,
         uc.en_name as town_en_name,uo.description as occupation_desc
        from ucard_merchant_user mu
        left join ucard_kyc_status ks on mu.unique_id = ks.unique_id
        left join (
            select code, en_name, card_category,standard_code
            from ucard_country
            group by code, en_name, card_category
        ) u on mu.country = u.code and u.card_category = 2
        left join sys_country sc on sc.code = mu.country
        left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) uc on mu.town = uc.code and uc.card_category = 2
        left join ucard_occupations uo
        on uo.occupation_code = mu.occupation
        where ks.`status` = 2
    </select>
    <select id="getFirstByCustomId" resultType="com.crm.rely.backend.core.dto.ucard.MerchantUserDto">
        select
            um.*,u.en_name as country_en_name,uc.en_name as town_en_name,uo.description as occupation_desc,uw.balance,
            COALESCE(sc.name, u.en_name) as country_cn_name
        from ucard_merchant_user um
                 left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) u
                           on um.country = u.code and u.card_category = 2
                 left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) uc
                           on um.town = uc.code and uc.card_category = 2
                 left join ucard_occupations uo
                           on uo.occupation_code = um.occupation
                 left join ucard_wallet uw on uw.c_id = um.c_id
                 left join sys_country sc on sc.code = um.country
        where um.custom_id = #{customId}
    </select>

    <select id="findUserDetailsByCId"
            resultType="com.crm.rely.backend.core.dto.ucard.MerchantUserDto">
        select
            mu.`c_id`,
            mu.`unique_id`,
            mu.`area_code`,
            mu.`mobile`,
            mu.`email`,
            mu.`last_name` ,
            mu.`first_name`,
            mu.`birthday`,
            mu.`gender`,
            mu.`town`,
            mu.`address`,
            mu.`post_code`,
            mu.`occupation`,
            mu.`id_type`,
            mu.`id_number`,
            mu.`ssn`,
            u.standard_code as country,
            u.standard_code as nationality,
            u.en_name as country_en_name,
            COALESCE(sc.name, u.en_name) as country_cn_name,
            uc.en_name as town_en_name,uo.description as occupation_desc
        from ucard_merchant_user mu
                 left join (
            select code, en_name, card_category,standard_code
            from ucard_country
            group by code, en_name, card_category
        ) u on mu.country = u.code and u.card_category = 2
                 left join sys_country sc on sc.code = mu.country
                 left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) uc on mu.town = uc.code and uc.card_category = 2
                 left join ucard_occupations uo
                           on uo.occupation_code = mu.occupation
        where mu.c_id = #{cId}
    </select>

</mapper> 