<?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 = #{dto.userId} and ctu.status = #{dto.status}
        <if test="dto.companyName != null and dto.companyName != '' "> and ct.company_name like concat('%',#{dto.companyName},'%')</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="selectUipIdList" resultType="java.lang.String">-->
<!--        select-->
<!--            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>-->

</mapper>

