<?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="isPrivate" column="is_private"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
        <result property="evaluationBidWay" column="evaluation_bid_way"/>
        <result property="bidOpenTime" column="bid_open_time"/>
        <result property="bidOpenPlace" column="bid_open_place"/>
        <result property="earnestMoneyPay" column="earnest_money_pay"/>
        <result property="earnestMoney" column="earnest_money"/>
        <result property="evaluationBidCouncil" column="evaluation_bid_council"/>
        <result property="constructionUnit" column="construction_unit"/>
        <result property="constructionPrincipal" column="construction_principal"/>
        <result property="constructionPhone" column="construction_phone"/>
        <result property="supervisorUnit" column="supervisor_unit"/>
        <result property="supervisorPrincipal" column="supervisor_principal"/>
        <result property="supervisorPhone" column="supervisor_phone"/>
    </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,
               is_private,
               create_time,
               update_time,
               status,
               customer_id,
               evaluation_bid_way,
               bid_open_time,
               bid_open_place,
               earnest_money_pay,
               earnest_money,
               evaluation_bid_council,
               construction_unit,
               construction_principal,
               construction_phone,
               supervisor_unit,
               supervisor_principal,
               supervisor_phone,
               construction_unit_uip_id,
               construction_unit_cid
        from business_info
    </sql>

    <select id="selectBusinessInfoList" resultType="com.dsk.system.domain.business.vo.BusinessListVo">
        SELECT
        i.id,
        i.project_name projectName,
        i.project_type projectType,
        i.province_name provinceName,
        i.city_name cityName,
        i.district_name districtName,
        i.investment_amount investmentAmount,
        i.construction_unit ownerCompany,
        i.construction_unit_uip_id ownerCompanyUipId,
        i.construction_unit_cid ownerCompanyCid,
        MAX(f.creat_time) followTime,
        u.nick_name nickName,
        GROUP_CONCAT(DISTINCT l.label) label
        FROM business_info i
        LEFT JOIN business_user bu on bu.business_id = i.id
        LEFT JOIN business_follow_record f on f.business_id = i.id
        LEFT JOIN business_label l on l.business_id = i.id
        LEFT JOIN sys_user u on u.user_id = bu.user_id
        LEFT JOIN sys_dept d on d.dept_id = u.dept_id
        <where>
            <if test="projectType != null and projectType != ''">
                and i.project_type in
                <foreach collection="projectType" item="projectType" open="(" separator="," close=")">
                    #{projectType}
                </foreach>
            </if>
            <if test="minAmount != null and minAmount != ''"> and i.investment_amount &gt;= #{minAmount} </if>
            <if test="maxAmount != null and maxAmount != ''">  and i.investment_amount &lt;= #{maxAmount}</if>
            <if test="status != null ">  and i.status = #{status}</if>
            <if test="projectStage != null and projectStage != ''">
                and i.project_stage in
                <foreach collection="projectStage" item="projectStage" open="(" separator="," close=")">
                    #{projectStage}
                </foreach>
            </if>
            <if test="projectName != null and projectName != ''">
                and ( i.project_name like concat('%',#{projectName},'%')
                or i.construction_unit like concat('%',#{projectName},'%') )
            </if>
            <if test="provinceId != null and provinceId.size > 0 ">
                and i.province_id in
                <foreach collection="provinceId" item="provinceId" open="(" separator="," close=")">
                    #{provinceId}
                </foreach>
            </if>
            <if test="cityId != null and cityId.size > 0 ">
                and i.city_id in
                <foreach collection="cityId" item="cityId" open="(" separator="," close=")">
                    #{cityId}
                </foreach>
            </if>
            <if test="districtId != null and districtId.size > 0">
                and i.district_id in
                <foreach collection="districtId" item="districtId" open="(" separator="," close=")">
                    #{districtId}
                </foreach>
            </if>
            <choose>
                <when test="isPrivate == 1"> and bu.user_id = #{userId} </when>
                <!-- 数据范围过滤 -->
                <otherwise> and (bu.user_id = #{userId} or i.is_private = 1) ${params.dataScope} </otherwise>
            </choose>
        </where>
        GROUP BY i.id
        ORDER BY i.create_time DESC
    </select>

    <select id="selectProjectName" resultType="com.dsk.system.domain.business.vo.BusinessLikeProjectNameListVo">
        SELECT
            project_name, investment_amount, project_level, project_stage, project_type,
            project_category, status
        FROM business_info
        where project_name like concat('%',#{projectName},'%')
        order by project_name desc
        limit 20
    </select>

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

    <select id="selectTotal" resultType="com.dsk.system.domain.business.vo.BusinessBrowseVo">
        SELECT
            count(DISTINCT ci.id) contactsCount,
            count(DISTINCT f.id) followRecordCount,
            count(DISTINCT b.id) backlogCount,
            count(DISTINCT r.id) relateCompanyCount
        FROM
            business_info i
                LEFT JOIN contact_info ci on ci.business_id = i.id
                LEFT JOIN business_follow_record f on f.business_id = i.id
                LEFT JOIN business_backlog b on b.business_id = i.id
                LEFT JOIN business_relate_company r on r.business_id = i.id
        WHERE i.id = #{business}
    </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="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>
            <if test="evaluationBidWay != null">evaluation_bid_way,</if>
            <if test="bidOpenTime != null">bid_open_time,</if>
            <if test="bidOpenPlace != null">bid_open_place,</if>
            <if test="earnestMoneyPay != null">earnest_money_pay,</if>
            <if test="earnestMoney != null">earnest_money,</if>
            <if test="evaluationBidCouncil != null">evaluation_bid_council,</if>
            <if test="constructionUnit != null">construction_unit,</if>
            <if test="constructionPrincipal != null">construction_principal,</if>
            <if test="constructionPhone != null">construction_phone,</if>
            <if test="supervisorUnit != null">supervisor_unit,</if>
            <if test="supervisorPrincipal != null">supervisor_principal,</if>
            <if test="supervisorPhone != null">supervisor_phone,</if>
            <if test="constructionUnitUipId != null">construction_unit_uip_id,</if>
            <if test="constructionUnitCid != null">construction_unit_cid,</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="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>
            <if test="evaluationBidWay != null">#{evaluationBidWay},</if>
            <if test="bidOpenTime != null">#{bidOpenTime},</if>
            <if test="bidOpenPlace != null">#{bidOpenPlace},</if>
            <if test="earnestMoneyPay != null">#{earnestMoneyPay},</if>
            <if test="earnestMoney != null">#{earnestMoney},</if>
            <if test="evaluationBidCouncil != null">#{evaluationBidCouncil},</if>
            <if test="constructionUnit != null">#{constructionUnit},</if>
            <if test="constructionPrincipal != null">#{constructionPrincipal},</if>
            <if test="constructionPhone != null">#{constructionPhone},</if>
            <if test="supervisorUnit != null">#{supervisorUnit},</if>
            <if test="supervisorPrincipal != null">#{supervisorPrincipal},</if>
            <if test="supervisorPhone != null">#{supervisorPhone},</if>
            <if test="constructionUnitUipId != null">#{constructionUnitUipId},</if>
            <if test="constructionUnitCid != null">#{constructionUnitCid},</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>
            province_name = #{provinceName},
            province_id = #{provinceId},
            city_name = #{cityName},
            city_id = #{cityId},
            district_name = #{districtName},
            district_id = #{districtId},
            construction_unit_uip_id = #{constructionUnitUipId},
            construction_unit_cid = #{constructionUnitCid},
            <if test="projectType != null">project_type = #{projectType},</if>
            <if test="projectCategory != null">project_category = #{projectCategory},</if>
            <if test="isPrivate != null">is_private = #{isPrivate},</if>
            <if test="status != null">status = #{status},</if>
            <if test="customerId != null">customer_id = #{customerId},</if>
            <if test="evaluationBidWay != null">evaluation_bid_way = #{evaluationBidWay},</if>
            <if test="bidOpenTime != null">bid_open_time = #{bidOpenTime},</if>
            <if test="bidOpenPlace != null">bid_open_place = #{bidOpenPlace},</if>
            <if test="earnestMoneyPay != null">earnest_money_pay = #{earnestMoneyPay},</if>
            <if test="earnestMoney != null">earnest_money = #{earnestMoney},</if>
            <if test="evaluationBidCouncil != null">evaluation_bid_council = #{evaluationBidCouncil},</if>
            <if test="constructionUnit != null">construction_unit = #{constructionUnit},</if>
            <if test="constructionPrincipal != null">construction_principal = #{constructionPrincipal},</if>
            <if test="constructionPhone != null">construction_phone = #{constructionPhone},</if>
            <if test="supervisorUnit != null">supervisor_unit = #{supervisorUnit},</if>
            <if test="supervisorPrincipal != null">supervisor_principal = #{supervisorPrincipal},</if>
            <if test="supervisorPhone != null">supervisor_phone = #{supervisorPhone},</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 i,b,f,l,r,u
        from business_info i
        left join business_backlog b on b.business_id = i.id
        left join business_follow_record f on f.business_id = i.id
        left join business_label l on l.business_id = i.id
        left join business_relate_company r on r.business_id = i.id
        left join business_user u on u.business_id = i.id
        where i.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, bi.investment_amount, bi.project_stage, bi.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="projectName != null and projectName != '' "> and bi.project_name like concat('%',#{projectName},'%') </if>
        <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>
    <select id="getConstruction" resultType="com.dsk.common.core.domain.entity.BusinessInfo">
        select
            investment_amount,
            amount_source,
            plan_bid_time,
            plan_start_time,
            plan_complete_time,
            build_property,
            project_details,
            evaluation_bid_way,
            bid_open_time,
            bid_open_place,
            earnest_money_pay,
            earnest_money,
            evaluation_bid_council
        from business_info
        where id = #{id}
    </select>
    <select id="isRepetitionProjectName" resultType="java.lang.Integer">
        select count(i.id)
        from business_info i
        join business_user u on u.business_id = i.id
        left join customer ct on ct.customer_id = i.customer_id
        where  i.project_name = #{projectName}
        and u.user_id = #{userId}
        <if test="companyName != null and companyName != '' "> and ct.company_name =#{companyName}</if>
    </select>

    <sql id="businessSearchSql">
        from business_info bi
        join business_user bu on bu.business_id = bi.id
        left join sys_user u on bu.user_id = u.user_id
        left join sys_dept d on u.dept_id = d.dept_id
        where (bu.user_id = #{userId} or bi.is_private = 1)
        <if test="status != null"> and bi.status = #{status} </if>
        ${params.dataScope}
    </sql>

    <select id="selectCountByStatus" resultType="java.lang.Integer">
        select count(bi.id) <include refid="businessSearchSql"/>
    </select>

    <select id="selectAmountAnalyze" resultType="com.dsk.system.domain.business.vo.BusinessAnalyzeVo">
        select
            bi.amount_source,count(bi.id) businessCount, ROUND(sum(bi.investment_amount), 4) totalAmount
            <include refid="businessSearchSql"/>
        and bi.amount_source is not null
        group by bi.amount_source
        having businessCount > 0
    </select>

    <select id="selectTypeAnalyze" resultType="com.dsk.system.domain.business.vo.BusinessAnalyzeVo">
        select
            bi.project_type,count(bi.id) businessCount, ROUND(sum(bi.investment_amount), 4) totalAmount
        <include refid="businessSearchSql"/>
        and bi.project_type is not null
        group by bi.project_type
        <if test="status != null and status == 0 "> order by businessCount desc </if>
        <if test="status != null and status == 2 "> order by totalAmount desc </if>
    </select>

    <select id="selectCategoryAnalyze" resultType="com.dsk.system.domain.business.vo.BusinessAnalyzeVo">
        select
            bi.project_category,count(bi.id) businessCount, ROUND(sum(bi.investment_amount), 4) totalAmount
        <include refid="businessSearchSql"/>
        and bi.project_category is not null
        group by bi.project_category
        <if test="status != null and status == 0 "> order by businessCount desc </if>
        <if test="status != null and status == 2 "> order by totalAmount desc </if>
    </select>

</mapper>
