<?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.system.mapper.CustomerMapper">

    <sql id="Base_Bean">
        ct.customer_id, ct.company_id, ct.uip_id, ct.company_name, ct.legal_person, ct.credit_code, ct.register_capital,
        ct.province_id, ct.city_id, ct.district_id, ct.register_address, ct.company_nature, ct.company_level,
        ct.credit_level, ct.super_company, ct.is_on, ct.is_major, ct.company_attribute, ct.main_business,
        ct.business_scope, ct.customer_level, ct.business_characteristic, ct.decision_chain, ct.bid_characteristic,
        ct.performance_characteristic, ct.other_ms_characteistic, ct.create_id, ct.create_time, ct.update_id, ct.update_time
    </sql>

    <select id="selectList" resultType="com.dsk.system.domain.customer.vo.CustomerListVo">
        select
            u.nick_name followUser, bi1.num reserveProject, bi2.num followProject, bi3.num cooperationProject,
            <include refid="Base_Bean"></include>
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        join sys_user u on ctu.user_id = u.user_id
        left join (
            select count(status) num,customer_id from business_info where `status` = 0 group by customer_id
        ) bi1 on bi1.customer_id = ct.customer_id
        left join (
            select count(status) num,customer_id from business_info where `status` = 1 group by customer_id
        ) bi2 on bi2.customer_id = ct.customer_id
        left join (
            select count(status) num,customer_id from business_info where `status` = 2 group by customer_id
        ) bi3 on bi3.customer_id = ct.customer_id
        where ctu.user_id = #{userId} and ctu.status = #{status}
        <if test="companyName != null and companyName != '' "> and ct.company_name like concat('%',#{companyName},'%')</if>
        <if test="isOn != null "> and ct.is_on = #{isOn}</if>
        <if test="provinceIds != null and provinceIds.size > 0 ">
            and ct.province_id in
            <foreach collection="provinceIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="cityIds != null and cityIds.size > 0 ">
            and ct.city_id in
            <foreach collection="cityIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="districtIds != null and districtIds.size > 0 ">
            and ct.district_id in
            <foreach collection="districtIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="companyNatures != null and companyNatures.size > 0 ">
            and ct.company_nature in
            <foreach collection="companyNatures" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="creditLevels != null and creditLevels.size > 0 ">
            and ct.credit_level in
            <foreach collection="creditLevels" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        order by ct.create_time desc
    </select>

    <select id="selectUserList" resultType="com.dsk.system.domain.customer.Customer">
        select
            ct.customer_id, ct.company_name
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        where ctu.user_id = #{userId}
    </select>

    <select id="selectByCompanyNameAndUserId" resultType="com.dsk.system.domain.customer.Customer">
        select
        <include refid="Base_Bean"></include>
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        where ct.company_name = #{companyName} and ctu.user_id = #{userId}
    </select>

    <select id="selectStatusList" resultType="com.dsk.system.domain.customer.vo.CustomerStatusListVo">
        select
            ct.customer_id, ct.uip_id
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        where ctu.user_id = #{userId} and ctu.status = 0 and ct.uip_id in
        <foreach collection="uipIds" item="uipId" open="(" separator="," close=")">
            #{uipId}
        </foreach>
    </select>

    <select id="selectStatusListByCompanyName" resultType="com.dsk.system.domain.customer.vo.CustomerStatusListVo">
        select
            ct.customer_id, ct.company_name, ctu.status
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        where ctu.user_id = #{userId} and ct.company_name in
        <foreach collection="companyNames" item="companyName" open="(" separator="," close=")">
            #{companyName}
        </foreach>
    </select>

    <select id="selectCustomerCount" resultType="java.lang.Integer">
        select
            count(ct.customer_id)
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        where ctu.status = 0 and ctu.user_id = #{userId}
        <if test="companyNature != null and companyNature != ''"> and ct.company_nature = #{companyNature} </if>
    </select>

    <select id="selectCooperationTop" resultType="com.dsk.system.domain.customer.vo.CustomerAnalyzeVo">
        select
            ct.customer_id, ct.company_name, ct.company_id, ct.uip_id, count(bi.id) businessCount,
            ROUND(sum(bi.investment_amount), 4) totalAmount
        from customer ct
        join customer_user ctu on ct.customer_id = ctu.customer_id
        left join business_info bi on (bi.customer_id = ct.customer_id and bi.status = 2)
        where ctu.status = 0 and ctu.user_id = #{userId}
        group by ct.customer_id
        having businessCount > 0
        order by totalAmount desc
        limit 10
    </select>

    <select id="selectCreditLevelGroup" resultType="java.util.Map">
        select ct.* from (
            select
		    	ct.credit_level creditLevel, count(ct.customer_id) customerCount
		    from customer ct
            join customer_user ctu on ct.customer_id = ctu.customer_id
		    where ctu.status = 0 and ctu.user_id = #{userId} and ct.credit_level is not null
		    group by ct.credit_level
        ) ct
        left join sys_dict_data sdd on (sdd.dict_label = ct.creditLevel and sdd.dict_type = 'credit_level_type')
        order by sdd.dict_sort asc
    </select>

</mapper>

