<?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.trade.service.enterprise.companyrelated.mapper.ImporterEntityMapper">


    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        ,
        deleted,
        gmt_create,
        gmt_modified,
        customer_user_id, entity_no, region, country, `status`, entity_name, cert_type, cert_no, business_province, business_city, business_address, business_post_code, contact_direct_ind,
        contact_name, contact_mobile, contact_email, vat_no, business_category, trading_life, settlement_period,
        trading_amount_past_year, trading_amount_past_year_currency, amount_to_be_applied, amount_to_be_applied_currency, period_to_be_applied
    </sql>

    <!-- 根据证件号查询企业相关信息 -->
    <select id="getByUserIdAndCertNo" resultType="com.trade.service.enterprise.companyrelated.domain.ImporterEntity">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        importer_entity
        WHERE
        customer_user_id = #{customerUserId}
        and cert_no = #{certNo}
        and deleted = 0
    </select>

    <select id="queryListBySearch" parameterType="com.trade.service.enterprise.companyrelated.domain.vo.ImporterEntitySearch"
            resultType="com.trade.service.enterprise.companyrelated.domain.vo.ImporterEntityVO">
        SELECT
        a.id
        ,
        a.deleted,
        a.gmt_create,
        a.gmt_modified,
        a.customer_user_id, a.entity_no, a.region, a.country, a.`status`, a.entity_name, a.cert_type, a.cert_no, a.business_province, a.business_city, a.business_address, a.business_post_code, a.contact_direct_ind,
        a.contact_name, a.contact_mobile, a.contact_email, a.vat_no, b.name as customer_name, b.customer_identifier_no,
        a.business_category, a.trading_life, a.settlement_period,a.trading_amount_past_year, a.trading_amount_past_year_currency, a.amount_to_be_applied, a.amount_to_be_applied_currency, a.period_to_be_applied
        FROM
        importer_entity a
        left join enterprise b on a.customer_user_id = b.customer_id
        WHERE
        a.deleted = 0
        <if test="customerUserId!= null and customerUserId!= ''">
            and a.customer_user_id = #{customerUserId}
        </if>
        <if test="region!= null and region!= ''">
            and a.region = #{region}
        </if>
        <if test="country!= null and country!= ''">
            and a.country = #{country}
        </if>
        <if test="status!= null and status!= ''">
            and a.status = #{status}
        </if>
        <if test="entityName!= null and entityName!= ''">
            and (a.entity_name like CONCAT('%', #{entityName}, '%') or a.entity_no = #{entityName})
        </if>
        <if test="customerName!= null and customerName!= ''">
            and (b.name like CONCAT('%', #{customerName}, '%') or b.customer_identifier_no = #{customerName})
        </if>
        <if test="createdTimeStart!= null">
            and a.gmt_create &gt;= #{createdTimeStart}
        </if>
        <if test="createdTimeEnd!= null">
            AND a.gmt_create &lt; DATE_FORMAT(DATE_ADD(#{createdTimeEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
    </select>

    <select id="getList4Dict"
            resultType="com.trade.service.enterprise.companyrelated.domain.vo.ImporterEntityDict">
        SELECT id          as importer_id,
               cert_no     as importer_cert_no,
               entity_name as importer_name
        FROM importer_entity
        WHERE deleted = 0
    </select>
    <select id="getVOByEntityId"
            resultType="com.trade.service.enterprise.companyrelated.domain.vo.ImporterEntityVO">
        SELECT
            a.id
                ,
            a.deleted,
            a.gmt_create,
            a.gmt_modified,
            a.customer_user_id, a.entity_no, a.region, a.country, a.`status`, a.entity_name, a.cert_type, a.cert_no, a.business_province, a.business_city, a.business_address, a.business_post_code, a.contact_direct_ind,
            a.contact_name, a.contact_mobile, a.contact_email, a.vat_no, b.name as customer_name, b.customer_identifier_no,
            a.business_category, a.trading_life, a.settlement_period,a.trading_amount_past_year, a.trading_amount_past_year_currency, a.amount_to_be_applied, a.amount_to_be_applied_currency, a.period_to_be_applied
        FROM
        importer_entity a
        left join enterprise b on a.customer_user_id = b.customer_id
        WHERE
        a.deleted = 0
        and a.id = #{entityId}
    </select>
</mapper>
