<?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.BusinessInfoMapper">

    <resultMap type="com.dsk.common.core.domain.entity.BusinessInfo" id="BusinessInfoResult">
        <result property="id" column="id"/>
        <result property="projectName" column="project_name"/>
        <result property="investmentAmount" column="investment_amount"/>
        <result property="amountSource" column="amount_source"/>
        <result property="planBidTime" column="plan_bid_time"/>
        <result property="planStartTime" column="plan_start_time"/>
        <result property="projectLevel" column="project_level"/>
        <result property="projectStage" column="project_stage"/>
        <result property="buildProperty" column="build_property"/>
        <result property="planCompleteTime" column="plan_complete_time"/>
        <result property="projectDetails" column="project_details"/>
        <result property="provinceName" column="province_name"/>
        <result property="provinceId" column="province_id"/>
        <result property="cityName" column="city_name"/>
        <result property="cityId" column="city_id"/>
        <result property="districtName" column="district_name"/>
        <result property="districtId" column="district_id"/>
        <result property="projectType" column="project_type"/>
        <result property="projectCategory" column="project_category"/>
        <result property="team" column="team"/>
        <result property="isPrivate" column="is_private"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
        <result property="customerId" column="customer_id"/>
    </resultMap>

    <sql id="selectBusinessInfoVo">
        select id,
               project_name,
               investment_amount,
               amount_source,
               plan_bid_time,
               plan_start_time,
               project_level,
               project_stage,
               build_property,
               plan_complete_time,
               project_details,
               province_name,
               province_id,
               city_name,
               city_id,
               district_name,
               district_id,
               project_type,
               project_category,
               team,
               is_private,
               create_time,
               update_time,
               status,
               customer_id
        from business_info
    </sql>

    <select id="selectBusinessInfoList" resultType="com.dsk.system.domain.vo.BusinessListVo">
        SELECT
        i.id,
        i.project_name projectName,
        i.province_name provinceName,
        i.city_name cityName,
        i.district_name districtName,
        i.investment_amount investmentAmount,
        GROUP_CONCAT(DISTINCT r.company_name) ownerCompany,
        MAX(f.creat_time) followTime,
        u.nick_name nickName,
        GROUP_CONCAT(DISTINCT l.label) label
        FROM business_info i
        LEFT JOIN business_follow_record f on f.business_id = i.id
        LEFT JOIN (SELECT business_id,company_name FROM business_relate_company WHERE company_role LIKE '%业主%' ) r on r.business_id = i.id
        LEFT JOIN business_label l on l.business_id = i.id
        LEFT JOIN business_user bu on bu.business_id = i.id
        LEFT JOIN sys_user u on u.user_id = f.user_id
        <where>
            <if test="provinceId != null">
                and i.province_id = #{provinceId}
            </if>
            <if test="cityId != null">
                and i.city_id = #{cityId}
            </if>
            <if test="districtId != null">
                and i.district_id = #{districtId}
            </if>
            <if test="projectType != null and projectType != ''">
                and i.project_type = #{projectType}
            </if>
            <if test="minAmount != null and minAmount != '' and maxAmount != minAmount">
                and i.investment_amount &gt;= #{minAmount}
            </if>
            <if test="maxAmount != null and maxAmount != '' and maxAmount != minAmount">
                and i.investment_amount &lt;= #{maxAmount}
            </if>
            <if test="minAmount != null and minAmount != '' and maxAmount != null and maxAmount != '' and maxAmount == minAmount">
                and i.investment_amount = #{minAmount}
            </if>
            <if test="projectStage != null and projectStage != ''">
                and i.project_stage = #{projectStage}
            </if>
            <if test="projectName != null and projectName != ''">
                and i.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="ownerCompany != null and ownerCompany != ''">
                or r.company_name like concat('%',#{ownerCompany},'%')
            </if>
            <if test="userId != null">
                and bu.user_id = #{userId}
            </if>
            <if test="deptId != null">
                and bu.dept_id = #{deptId} and i.is_private = 1
            </if>
        </where>
        GROUP BY i.id
        ORDER BY i.create_time DESC
    </select>

    <select id="queryprojectName" resultType="java.lang.String">
        SELECT *
        FROM
        (SELECT project_name as name FROM business_info) AS bu
        <where>
            <if test="projectName !=null and projectName!=''">
                name like concat('%',#{projectName},'%')
            </if>
        </where>
    </select>

    <select id="selectBusinessInfoById" parameterType="Long" resultMap="BusinessInfoResult">
        <include refid="selectBusinessInfoVo"/>
        where id = #{id}
    </select>

    <insert id="insertBusinessInfo" parameterType="com.dsk.common.core.domain.entity.BusinessInfo" useGeneratedKeys="true" keyProperty="id">
        insert into business_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="projectName != null">project_name,</if>
            <if test="investmentAmount != null">investment_amount,</if>
            <if test="amountSource != null">amount_source,</if>
            <if test="planBidTime != null">plan_bid_time,</if>
            <if test="planStartTime != null">plan_start_time,</if>
            <if test="projectLevel != null">project_level,</if>
            <if test="projectStage != null">project_stage,</if>
            <if test="buildProperty != null">build_property,</if>
            <if test="planCompleteTime != null">plan_complete_time,</if>
            <if test="projectDetails != null">project_details,</if>
            <if test="provinceName != null">province_name,</if>
            <if test="provinceId != null">province_id,</if>
            <if test="cityName != null">city_name,</if>
            <if test="cityId != null">city_id,</if>
            <if test="districtName != null">district_name,</if>
            <if test="districtId != null">district_id,</if>
            <if test="projectType != null">project_type,</if>
            <if test="projectCategory != null">project_category,</if>
            <if test="team != null">team,</if>
            <if test="isPrivate != null">is_private,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="status != null">status,</if>
            <if test="customerId != null">customer_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="projectName != null">#{projectName},</if>
            <if test="investmentAmount != null">#{investmentAmount},</if>
            <if test="amountSource != null">#{amountSource},</if>
            <if test="planBidTime != null">#{planBidTime},</if>
            <if test="planStartTime != null">#{planStartTime},</if>
            <if test="projectLevel != null">#{projectLevel},</if>
            <if test="projectStage != null">#{projectStage},</if>
            <if test="buildProperty != null">#{buildProperty},</if>
            <if test="planCompleteTime != null">#{planCompleteTime},</if>
            <if test="projectDetails != null">#{projectDetails},</if>
            <if test="provinceName != null">#{provinceName},</if>
            <if test="provinceId != null">#{provinceId},</if>
            <if test="cityName != null">#{cityName},</if>
            <if test="cityId != null">#{cityId},</if>
            <if test="districtName != null">#{districtName},</if>
            <if test="districtId != null">#{districtId},</if>
            <if test="projectType != null">#{projectType},</if>
            <if test="projectCategory != null">#{projectCategory},</if>
            <if test="team != null">#{team},</if>
            <if test="isPrivate != null">#{isPrivate},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="status != null">#{status},</if>
            <if test="customerId != null">#{customerId},</if>
        </trim>
    </insert>

    <update id="updateBusinessInfo" parameterType="com.dsk.common.core.domain.entity.BusinessInfo">
        update business_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="projectName != null">project_name = #{projectName},</if>
            <if test="investmentAmount != null">investment_amount = #{investmentAmount},</if>
            <if test="amountSource != null">amount_source = #{amountSource},</if>
            <if test="planBidTime != null">plan_bid_time = #{planBidTime},</if>
            <if test="planStartTime != null">plan_start_time = #{planStartTime},</if>
            <if test="projectLevel != null">project_level = #{projectLevel},</if>
            <if test="projectStage != null">project_stage = #{projectStage},</if>
            <if test="buildProperty != null">build_property = #{buildProperty},</if>
            <if test="planCompleteTime != null">plan_complete_time = #{planCompleteTime},</if>
            <if test="projectDetails != null">project_details = #{projectDetails},</if>
            <if test="provinceName != null">province_name = #{provinceName},</if>
            <if test="provinceId != null">province_id = #{provinceId},</if>
            <if test="cityName != null">city_name = #{cityName},</if>
            <if test="cityId != null">city_id = #{cityId},</if>
            <if test="districtName != null">district_name = #{districtName},</if>
            <if test="districtId != null">district_id = #{districtId},</if>
            <if test="projectType != null">project_type = #{projectType},</if>
            <if test="projectCategory != null">project_category = #{projectCategory},</if>
            <if test="team != null">team = #{team},</if>
            <if test="isPrivate != null">is_private = #{isPrivate},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="status != null">status = #{status},</if>
            <if test="customerId != null">customer_id = #{customerId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteBusinessInfoById" parameterType="Long">
        delete
        from business_info
        where id = #{id}
    </delete>

    <delete id="deleteBusinessInfoByIds" parameterType="Long">
        delete from business_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectCountByStatusAndCustomerId" resultType="java.lang.Integer">
        select count(id) from business_info
        <where>
            <if test="status != null "> and `status` = #{status} </if>
            <if test="customerId != null and customerId != ''"> and `customer_id` = #{customerId} </if>
        </where>
    </select>

    <select id="selectCustomerBusinessList" resultType="com.dsk.system.domain.customer.vo.CustomerBusinessListVo">
        select
            bi.id, bi.project_name, pi.investment_amount, pi.project_stage, pi.status
        from business_info bi
        left join business_user bu on bi.id = bu.business_id
        where bi.customer_id = #{customerId} and (bu.user_id = #{userId} or bi.is_private = 1))
        <if test="projectStage != null and projectStage != '' "> and bi.project_stage = #{projectStage}</if>
        <if test="status != null "> and bi.status = #{status}</if>
        order by bi.create_time desc
    </select>
</mapper>
