<?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.dsk.cscec.mapper.DCustomerMapper">

    <select id="allSearchList" resultType="com.dsk.cscec.domain.vo.DCustomerListVo">
        select
            dc.customer_key, dc.customer_id, dc.customer_code, dc.customer_name, dc.recommend_org, dc.register_region, dc.register_province, dc.register_city, dc.customer_class, dc.primary_business,
            dc.paytax_type, dc.tax_rate, dc.customer_state, dc.leader_name, dc.labor_captain_phone, dc.labor_captain_idcard, dc.service_team_personnum, dc.service_team_speciality,
            dc.construct_job_scope, dc.credential, dc.register_capital, dc.contact_person, dc.contact_phone, dc.approve_date2, dc.credit_level, dc.group_specialty, fsl.fstate badnessState
        from d_customer dc
        left join f_supplier_limited fsl on (fsl.flaowufbid = dc.customer_id
                or fsl.fzhuanyefbid = dc.customer_id or fsl.ffgid = dc.customer_id or fsl.fzlid = dc.customer_id)
        where dc.recommend_org_id = 'F17305B4EA4444CBAB12892C7B99E475'
        <if test="bo.customerName != null and bo.customerName != '' "> and dc.customer_name like concat('%',#{bo.customerName},'%')</if>
        <if test="bo.province != null and bo.province.size > 0 and (bo.city == null or bo.city.size == 0) ">
            and dc.register_province in
            <foreach collection="bo.province" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.city != null and bo.city.size > 0 and (bo.province == null or bo.province.size == 0) ">
            and dc.register_city in
            <foreach collection="bo.city" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.province != null and bo.province.size > 0 and bo.city != null and bo.city.size > 0 " >
            and ( dc.register_province in
            <foreach collection="bo.province" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            or dc.register_city in
            <foreach collection="bo.city" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="bo.customerClass != null and bo.customerClass.size > 0 ">
            and dc.customer_class in
            <foreach collection="bo.customerClass" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.customerState != null and bo.customerState.size > 0 ">
            and dc.customer_state in
            <foreach collection="bo.customerState" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.creditLevel != null and bo.creditLevel.size > 0 ">
            and dc.credit_level in
            <foreach collection="bo.creditLevel" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.credential != null and bo.credential.size > 0 ">
            and dc.credential in
            <foreach collection="bo.credential" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bo.approveDateBegion != null and bo.approveDateBegion != '' ">and dc.approve_date2 &gt;= #{bo.approveDateBegion}</if>
        <if test="bo.approveDateEnd != null and bo.approveDateEnd != '' ">and dc.approve_date2 &lt;= #{bo.approveDateEnd} </if>
        <if test="bo.isTeam != null"> and dc.leader_name is <if test="bo.isTeam == 1">not</if> null </if>
        <if test="bo.badnessState != null and bo.badnessState != ''"> and fsl.fstate = #{bo.badnessState}  </if>
        order by dc.approve_date2 desc
    </select>

    <sql id="allColumn">
        customer_key, customer_id, cusomer_name, fin_customer_code, fin_customer_name, tax_number, isvalid, customer_code,
        customer_name, customer_state, recommend_org_id, recommend_org, approve_date2, version, supplier_id, customer_category_code,
         representative, register_no, unify_social_code, business_license, orgnization_code, register_capital, primary_business,
         credential, brand, address, register_city_id, register_region, register_province, register_city, open_bank, bank_account,
         contact_person, contact_phone, mobile, zipcode, email, website, remark, area_code, currency, orgnization_id, customer_type,
         credit_situation, employee_id, establish_date, phone_code, customer_property, sector_position, scope, fax, customer_class,
         id_card, sub_company, allied_company, turn_over, main_customer, main_resource, main_region, raw_material, productline,
         customer_category_type, customer_firm, customertype_id, customer_category_type_id, register_address, warehouse_address,
         teamwork_product, isfactoryorfranchise, expire_date1, expire_date2, expire_date3, expire_date4, expire_date5, leader_name,
         group_specialty_id, group_specialty, manage_ability, product_quality_ability, construct_job_scope, responsible_person,
         approve_date1, isagree, business_manager, project_manager, purchase_principal, upper_company_code, service_team_logo,
         labor_captain_idcard, labor_captain_phone, service_team_personnum, service_team_speciality, secondary_code, approve_date3,
          approve_date4, flow_state, isenterbjlaborcomany, expire_date6, expire_date7, credit_level, base_logo, recommend_project,
          recommend_project_id, recommend_date, aptitude_card, work_safety_license, master_id, paytax_type, tax_rate, card_type,
          customer_score, customer_name2, customer_name3, customer_name4, country, fin_customer_type, company_code, owner_kind,
          customer_kind, data_source, isowner, iscustomer, abbr_name1, abbr_name2, tax_type, isfreeze, update_date, load_time
    </sql>

    <sql id="columnByType">
        customer_key, customer_id, customer_code, customer_name, customer_state, recommend_org_id, recommend_org,
        register_region,register_province,register_city,group_specialty,representative,paytax_type,tax_rate,
        construct_job_scope,credential,register_capital,contact_person,contact_phone,approve_date2,credit_level,
        secondary_code,unify_social_code,primary_business,leader_name,labor_captain_phone,labor_captain_idcard,
        service_team_personnum,service_team_speciality
    </sql>

    <select id="queryListByType" resultType="com.dsk.cscec.domain.vo.CustomerInfoVo">
        select
            <include refid="columnByType"></include>
        from d_customer
        where
            recommend_org_id = 'F17305B4EA4444CBAB12892C7B99E475'
            <if test="bo.customerClass != null and bo.customerClass != ''">
                and customer_class = #{bo.customerClass}
            </if>
            <if test="bo.serviceTeamLogo != null and bo.serviceTeamLogo == 'Y'.toString()">
                and leader_name is not null
            </if>
            <if test="bo.serviceTeamLogo != null and bo.serviceTeamLogo == 'N'.toString()">
                and leader_name is null
            </if>
            <if test="bo.leaderName != null and bo.leaderName != ''">
                and leader_name like concat('%',#{bo.leaderName},'%')
            </if>
            <if test="bo.customerName != null and bo.customerName != '' ">
                and customer_name like concat('%',#{bo.customerName},'%')
            </if>
            <if test="bo.registerRegion != null and bo.registerRegion.size > 0 or (bo.registerProvince != null and bo.registerProvince.size > 0) or (bo.registerCity != null and bo.registerCity.size > 0)">
                and
                <trim prefix="(" suffix=")" prefixOverrides="or">
                    <if test="bo.registerRegion != null and bo.registerRegion.size > 0 ">
                        or register_region in
                        <foreach collection="bo.registerRegion" item="registerRegion" open="(" close=")" separator=",">
                            #{registerRegion}
                        </foreach>
                    </if>
                    <if test="bo.registerProvince != null and bo.registerProvince.size > 0 ">
                        or register_province in
                        <foreach collection="bo.registerProvince" item="registerProvince" open="(" close=")" separator=",">
                            #{registerProvince}
                        </foreach>
                    </if>
                    <if test="bo.registerCity != null and bo.registerCity.size > 0 ">
                        or register_city in
                        <foreach collection="bo.registerCity" item="registerCity" open="(" close=")" separator=",">
                            #{registerCity}
                        </foreach>
                    </if>
                </trim>
            </if>
            <if test="bo.groupSpecialtyId != null and bo.groupSpecialtyId.size > 0 ">
                <foreach collection="bo.groupSpecialtyId" item="groupSpecialtyId">
                    and find_in_set(#{groupSpecialtyId},group_specialty_id) > 0
                </foreach>
            </if>
            <if test="bo.customerState != null and bo.customerState.size > 0 ">
                and customer_state in
                <foreach collection="bo.customerState" item="customerState" separator="," open="(" close=")">
                    #{customerState}
                </foreach>
            </if>
            <if test="bo.credential != null and bo.credential.size > 0 ">
                and credential in
                <foreach collection="bo.credential" item="credential" separator="," open="(" close=")">
                    #{credential}
                </foreach>
            </if>
            <if test="bo.creditLevel != null and bo.creditLevel.size > 0 ">
                and credit_level in
                <foreach collection="bo.creditLevel" item="creditLevel" separator="," open="(" close=")">
                    #{creditLevel}
                </foreach>
            </if>
            <if test="bo.paytaxType != null and bo.paytaxType.size > 0 ">
                and paytax_type in
                <foreach collection="bo.paytaxType" item="paytaxType" separator="," open="(" close=")">
                    #{paytaxType}
                </foreach>
            </if>
            <if test="bo.taxRate != null and bo.taxRate.size > 0 ">
                and tax_rate in
                <foreach collection="bo.taxRate" item="taxRate" separator="," open="(" close=")">
                    #{taxRate}
                </foreach>
            </if>
            <if test="bo.approveDate2Start != null">
                and approve_date2 &gt;= #{bo.approveDate2Start}
            </if>
            <if test="bo.approveDate2End != null">
                and date(approve_date2) &lt;= #{bo.approveDate2End}
            </if>
        order by approve_date2 desc
    </select>
    <select id="selectBusinessLicense" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_business_license where customer_id =#{customerId}
    </select>
    <select id="selectOrgCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_org_certificate where customer_id =#{customerId}
    </select>
    <select id="selectTaxCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_tax_certificate where customer_id =#{customerId}
    </select>
    <select id="selectQualificationCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_qualification_certificate where customer_id =#{customerId}
    </select>
    <select id="selectSafetyCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_safety_certificate where customer_id =#{customerId}
    </select>
    <select id="selectRatepayerCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_business_license where customer_id =#{customerId}
    </select>
    <select id="selectPerformanceCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_performance_certificate where customer_id =#{customerId}
    </select>
    <select id="selectInspectionCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_inspection_certificate where customer_id =#{customerId}
    </select>
    <select id="selectOtherCertificate" resultType="com.dsk.cscec.domain.vo.CertificateVo">
        select * from d_customer_other_certificate where customer_id =#{customerId}
    </select>

    <select id="allSearchCompanyNameList" resultType="string">
        select
            dc.customer_name
        from
            d_customer dc
        left join f_supplier_limited fsl on
            (fsl.flaowufbid = dc.customer_id
                or fsl.fzhuanyefbid = dc.customer_id
                or fsl.ffgid = dc.customer_id
                or fsl.fzlid = dc.customer_id)
        where
            dc.recommend_org_id = 'F17305B4EA4444CBAB12892C7B99E475'
    </select>
</mapper>