<?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.web.modules.merchant.mapper.BizMerchantMapper">

    <resultMap id="BizMerchantResult" type="com.ruoyi.web.modules.merchant.domain.BizMerchant">
        <id property="merchantId" column="merchant_id"/>
        <result property="merchantType" column="merchant_type"/>
        <result property="certStatus" column="cert_status"/>
        <result property="certTime" column="cert_time"/>
        <result property="bizComplete" column="biz_complete"/>
        <result property="shopCount" column="shop_count"/>
        <result property="personName" column="person_name"/>
        <result property="idCardNo" column="id_card_no"/>
        <result property="idCardType" column="id_card_type"/>
        <result property="birthDate" column="birth_date"/>
        <result property="idValidType" column="id_valid_type"/>
        <result property="idValidStart" column="id_valid_start"/>
        <result property="idValidEnd" column="id_valid_end"/>
        <result property="residenceAddress" column="residence_address"/>
        <result property="gender" column="gender"/>
        <result property="idCardFront" column="id_card_front"/>
        <result property="idCardBack" column="id_card_back"/>
        <result property="legalName" column="legal_name"/>
        <result property="legalIdCardNo" column="legal_id_card_no"/>
        <result property="legalIdCardType" column="legal_id_card_type"/>
        <result property="legalBirthDate" column="legal_birth_date"/>
        <result property="legalIdValidType" column="legal_id_valid_type"/>
        <result property="legalIdValidStart" column="legal_id_valid_start"/>
        <result property="legalIdValidEnd" column="legal_id_valid_end"/>
        <result property="legalResidence" column="legal_residence"/>
        <result property="legalGender" column="legal_gender"/>
        <result property="legalIdCardFront" column="legal_id_card_front"/>
        <result property="legalIdCardBack" column="legal_id_card_back"/>
        <result property="legalExtraPhoto" column="legal_extra_photo"/>
        <result property="companyName" column="company_name"/>
        <result property="creditCode" column="credit_code"/>
        <result property="regAddress" column="reg_address"/>
        <result property="regRegionCode" column="reg_region_code"/>
        <result property="regRegionName" column="reg_region_name"/>
        <result property="companyDetailAddress" column="company_detail_address"/>
        <result property="businessScope" column="business_scope"/>
        <result property="licenseValidType" column="license_valid_type"/>
        <result property="licenseValidStart" column="license_valid_start"/>
        <result property="licenseValidEnd" column="license_valid_end"/>
        <result property="merchantName" column="merchant_name"/>
        <result property="servicePhone" column="service_phone"/>
        <result property="bizRegionCode" column="biz_region_code"/>
        <result property="bizRegionName" column="biz_region_name"/>
        <result property="bizDetailAddress" column="biz_detail_address"/>
        <result property="contactName" column="contact_name"/>
        <result property="contactPhone" column="contact_phone"/>
        <result property="contactEmail" column="contact_email"/>
        <result property="bankName" column="bank_name"/>
        <result property="bankBranch" column="bank_branch"/>
        <result property="bankAccount" column="bank_account"/>
        <result property="corpBankAccount" column="corp_bank_account"/>
        <result property="businessLicense" column="business_license"/>
        <result property="accountPermit" column="account_permit"/>
        <result property="delFlag" column="del_flag"/>
        <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="remark" column="remark"/>
    </resultMap>

    <sql id="selectVo">
        select merchant_id, merchant_type, cert_status, cert_time, biz_complete, shop_count,
               person_name, id_card_no, id_card_type, birth_date, id_valid_type, id_valid_start, id_valid_end,
               residence_address, gender, id_card_front, id_card_back,
               legal_name, legal_id_card_no, legal_id_card_type, legal_birth_date, legal_id_valid_type, legal_id_valid_start,
               legal_id_valid_end, legal_residence, legal_gender, legal_id_card_front, legal_id_card_back,
               legal_extra_photo, company_name, credit_code, reg_address, reg_region_code, reg_region_name,
               company_detail_address, business_scope, license_valid_type, license_valid_start, license_valid_end,
               merchant_name, service_phone, biz_region_code, biz_region_name, biz_detail_address,
               contact_name, contact_phone, contact_email, bank_name, bank_branch, bank_account, corp_bank_account,
               business_license, account_permit, del_flag, create_by, create_time, update_by, update_time, remark
        from biz_merchant
    </sql>

    <select id="selectById" resultMap="BizMerchantResult">
        <include refid="selectVo"/>
        where merchant_id = #{merchantId} and del_flag = '0'
    </select>

    <select id="selectList" resultMap="BizMerchantResult">
        <include refid="selectVo"/>
        where del_flag = '0'
        <if test="certStatus != null and certStatus != ''">
            and cert_status = #{certStatus}
        </if>
        <if test="merchantNameQuery != null and merchantNameQuery != ''">
            and (merchant_name like concat('%', #{merchantNameQuery}, '%')
              or person_name like concat('%', #{merchantNameQuery}, '%')
              or company_name like concat('%', #{merchantNameQuery}, '%'))
        </if>
        order by create_time desc
    </select>

    <select id="selectSelectableList" resultMap="BizMerchantResult">
        <include refid="selectVo"/>
        where del_flag = '0' and cert_status = '0' and biz_complete = '1'
        order by create_time desc
    </select>

    <select id="countByCreditCode" resultType="int">
        select count(1) from biz_merchant
        where credit_code = #{creditCode} and del_flag = '0'
        <if test="excludeId != null">and merchant_id != #{excludeId}</if>
    </select>

    <select id="countByIdCardNo" resultType="int">
        select count(1) from biz_merchant
        where id_card_no = #{idCardNo} and del_flag = '0'
        <if test="excludeId != null">and merchant_id != #{excludeId}</if>
    </select>

    <select id="selectByIdCardNo" resultMap="BizMerchantResult">
        <include refid="selectVo"/>
        where id_card_no = #{idCardNo} and del_flag = '0'
        limit 1
    </select>

    <select id="selectByCreditCode" resultMap="BizMerchantResult">
        <include refid="selectVo"/>
        where credit_code = #{creditCode} and del_flag = '0'
        limit 1
    </select>

    <select id="countByMerchantName" resultType="int">
        select count(1) from biz_merchant
        where merchant_name = #{merchantName} and del_flag = '0'
        <if test="excludeId != null">and merchant_id != #{excludeId}</if>
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="merchantId">
        insert into biz_merchant (
            merchant_type, cert_status, cert_time, biz_complete, shop_count,
            person_name, id_card_no, id_card_type, birth_date, id_valid_type, id_valid_start, id_valid_end,
            residence_address, gender, id_card_front, id_card_back,
            legal_name, legal_id_card_no, legal_id_card_type, legal_birth_date, legal_id_valid_type, legal_id_valid_start,
            legal_id_valid_end, legal_residence, legal_gender, legal_id_card_front, legal_id_card_back,
            legal_extra_photo, company_name, credit_code, reg_address, reg_region_code, reg_region_name,
            company_detail_address, business_scope, license_valid_type, license_valid_start, license_valid_end,
            merchant_name, service_phone, biz_region_code, biz_region_name, biz_detail_address,
            contact_name, contact_phone, contact_email, bank_name, bank_branch, bank_account, corp_bank_account,
            business_license, account_permit, del_flag, create_by, create_time, remark
        ) values (
            #{merchantType}, #{certStatus}, #{certTime}, #{bizComplete}, #{shopCount},
            #{personName}, #{idCardNo}, #{idCardType}, #{birthDate}, #{idValidType}, #{idValidStart}, #{idValidEnd},
            #{residenceAddress}, #{gender}, #{idCardFront}, #{idCardBack},
            #{legalName}, #{legalIdCardNo}, #{legalIdCardType}, #{legalBirthDate}, #{legalIdValidType}, #{legalIdValidStart},
            #{legalIdValidEnd}, #{legalResidence}, #{legalGender}, #{legalIdCardFront}, #{legalIdCardBack},
            #{legalExtraPhoto}, #{companyName}, #{creditCode}, #{regAddress}, #{regRegionCode}, #{regRegionName},
            #{companyDetailAddress}, #{businessScope}, #{licenseValidType}, #{licenseValidStart}, #{licenseValidEnd},
            #{merchantName}, #{servicePhone}, #{bizRegionCode}, #{bizRegionName}, #{bizDetailAddress},
            #{contactName}, #{contactPhone}, #{contactEmail}, #{bankName}, #{bankBranch}, #{bankAccount}, #{corpBankAccount},
            #{businessLicense}, #{accountPermit}, '0', #{createBy}, sysdate(), #{remark}
        )
    </insert>

    <update id="update">
        update biz_merchant
        <set>
            <if test="personName != null">person_name = #{personName},</if>
            <if test="idCardNo != null">id_card_no = #{idCardNo},</if>
            <if test="idCardType != null">id_card_type = #{idCardType},</if>
            <if test="birthDate != null">birth_date = #{birthDate},</if>
            <if test="idValidType != null">id_valid_type = #{idValidType},</if>
            <if test="idValidStart != null">id_valid_start = #{idValidStart},</if>
            <if test="idValidEnd != null">id_valid_end = #{idValidEnd},</if>
            <if test="residenceAddress != null">residence_address = #{residenceAddress},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="idCardFront != null">id_card_front = #{idCardFront},</if>
            <if test="idCardBack != null">id_card_back = #{idCardBack},</if>
            <if test="legalName != null">legal_name = #{legalName},</if>
            <if test="legalIdCardNo != null">legal_id_card_no = #{legalIdCardNo},</if>
            <if test="legalIdCardType != null">legal_id_card_type = #{legalIdCardType},</if>
            <if test="legalBirthDate != null">legal_birth_date = #{legalBirthDate},</if>
            <if test="legalIdValidType != null">legal_id_valid_type = #{legalIdValidType},</if>
            <if test="legalIdValidStart != null">legal_id_valid_start = #{legalIdValidStart},</if>
            <if test="legalIdValidEnd != null">legal_id_valid_end = #{legalIdValidEnd},</if>
            <if test="legalResidence != null">legal_residence = #{legalResidence},</if>
            <if test="legalGender != null">legal_gender = #{legalGender},</if>
            <if test="legalIdCardFront != null">legal_id_card_front = #{legalIdCardFront},</if>
            <if test="legalIdCardBack != null">legal_id_card_back = #{legalIdCardBack},</if>
            <if test="legalExtraPhoto != null">legal_extra_photo = #{legalExtraPhoto},</if>
            <if test="companyName != null">company_name = #{companyName},</if>
            <if test="regAddress != null">reg_address = #{regAddress},</if>
            <if test="regRegionCode != null">reg_region_code = #{regRegionCode},</if>
            <if test="regRegionName != null">reg_region_name = #{regRegionName},</if>
            <if test="companyDetailAddress != null">company_detail_address = #{companyDetailAddress},</if>
            <if test="businessScope != null">business_scope = #{businessScope},</if>
            <if test="licenseValidType != null">license_valid_type = #{licenseValidType},</if>
            <if test="licenseValidStart != null">license_valid_start = #{licenseValidStart},</if>
            <if test="licenseValidEnd != null">license_valid_end = #{licenseValidEnd},</if>
            <if test="merchantName != null">merchant_name = #{merchantName},</if>
            <if test="servicePhone != null">service_phone = #{servicePhone},</if>
            <if test="bizRegionCode != null">biz_region_code = #{bizRegionCode},</if>
            <if test="bizRegionName != null">biz_region_name = #{bizRegionName},</if>
            <if test="bizDetailAddress != null">biz_detail_address = #{bizDetailAddress},</if>
            <if test="contactName != null">contact_name = #{contactName},</if>
            <if test="contactPhone != null">contact_phone = #{contactPhone},</if>
            <if test="contactEmail != null">contact_email = #{contactEmail},</if>
            <if test="bankName != null">bank_name = #{bankName},</if>
            <if test="bankBranch != null">bank_branch = #{bankBranch},</if>
            <if test="bankAccount != null">bank_account = #{bankAccount},</if>
            <if test="corpBankAccount != null">corp_bank_account = #{corpBankAccount},</if>
            <if test="businessLicense != null">business_license = #{businessLicense},</if>
            <if test="accountPermit != null">account_permit = #{accountPermit},</if>
            <if test="remark != null">remark = #{remark},</if>
            update_by = #{updateBy},
            update_time = sysdate()
        </set>
        where merchant_id = #{merchantId} and del_flag = '0'
    </update>

    <update id="updateCertStatus">
        update biz_merchant
        set cert_status = #{certStatus}, cert_time = sysdate(), update_time = sysdate()
        where merchant_id = #{merchantId} and del_flag = '0'
    </update>

    <update id="updateBizComplete">
        update biz_merchant set biz_complete = #{bizComplete}, update_time = sysdate()
        where merchant_id = #{merchantId} and del_flag = '0'
    </update>

    <update id="incrementShopCount">
        update biz_merchant set shop_count = ifnull(shop_count, 0) + 1, update_time = sysdate()
        where merchant_id = #{merchantId} and del_flag = '0'
    </update>

    <update id="decrementShopCount">
        update biz_merchant set shop_count = case when ifnull(shop_count, 0) > 0 then shop_count - 1 else 0 end,
            update_time = sysdate()
        where merchant_id = #{merchantId} and del_flag = '0'
    </update>

    <update id="logicDeleteByIds">
        update biz_merchant set del_flag = '2', update_by = #{updateBy}, update_time = sysdate()
        where del_flag = '0' and merchant_id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </update>

</mapper>
