<?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.ruoyi.ibs.custmap.mapper.CustMapMapper">
    <select id="selectCustMapListByCircle" parameterType="CustMapCircleDTO" resultType="CustMapVO">
        SELECT
            id, cust_name, lp_name, start_date, province, city, regist_capi_show, phone_number,
            address_detail, longitude, latitude,social_credit_code,new_company_flag_${dept} as new_company_flag, cust_type,
            ST_Distance_Sphere(
                point(longitude, latitude),
                point(#{targetLng}, #{targetLat})
            ) AS distance
        FROM
            cust_info_radar
        WHERE
            latitude BETWEEN #{targetLat} - #{latRange} AND #{targetLat} + #{latRange}
            AND longitude BETWEEN #{targetLng} - #{lngRange} AND #{targetLng} + #{lngRange}
            <include refid="selectConditions"></include>
        HAVING
            #{dist} * 1000 > distance
        <include refid="orderCondition"></include>
        limit #{pageIndex}, #{size}
    </select>

    <select id="countCustMapListByCircle" parameterType="CustMapCircleDTO" resultType="Long">
        SELECT
            count(distinct cust_id)
        FROM
            cust_info_radar
        WHERE
            latitude BETWEEN #{targetLat} - #{latRange} AND #{targetLat} + #{latRange}
            AND longitude BETWEEN #{targetLng} - #{lngRange} AND #{targetLng} + #{lngRange}
            <include refid="selectConditions"></include>
            AND #{dist} * 1000 > ST_Distance_Sphere(point(longitude, latitude), point(#{targetLng}, #{targetLat}))
    </select>

    <sql id="selectConditions">
        <if test="custName != null and custName != ''">
            AND cust_name LIKE CONCAT('%', #{custName}, '%')
        </if>
        <!-- 机构类型 -->
        <if test="companyTypeList != null and companyTypeList.size() > 0">
            <if test="companyTypeInclude != null and companyTypeInclude">
                AND company_type IN
            </if>
            <if test="companyTypeInclude != null and !companyTypeInclude">
                AND company_type NOT IN
            </if>
            <foreach collection="companyTypeList" item="companyType" open="(" separator="," close=")">
                #{companyType}
            </foreach>
        </if>
        <!-- 企业规模 -->
        <if test="custScaleList != null and custScaleList.size() > 0">
            AND cust_scale IN
            <foreach collection="custScaleList" item="custScale" open="(" separator="," close=")">
                #{custScale}
            </foreach>
        </if>
        <!-- 联系电话 -->
        <if test="isPhoneNumber != null">
            AND ISNULL(phone_number) = #{isPhoneNumber}
        </if>
        <!-- 邮箱 -->
        <if test="isEmail != null">
            AND ISNULL(email) = #{isEmail}
        </if>
        <!-- 省份地区 -->
        <if test="province != null and province != ''">
            AND province = #{province}
        </if>
        <if test="city != null and city != ''">
            AND city = #{city}
        </if>
        <if test="county != null and county != ''">
            AND county = #{county}
        </if>
        <if test="street != null and street != ''">
            AND street = #{street}
        </if>
        <if test="community != null and community != ''">
            AND community = #{community}
        </if>
        <!-- 所在行业 -->
        <if test="belongBusinessList != null and belongBusinessList.size() > 0">
            AND belong_business IN
            <foreach collection="belongBusinessList" item="belongBusiness" open="(" separator="," close=")">
                #{belongBusiness}
            </foreach>
        </if>
        <!-- 成立年限 -->
        <if test="startDateIntervalList != null and startDateIntervalList.size() > 0">
            AND
            <foreach collection="startDateIntervalList" item="param" open="(" separator=" OR " close=")">
                start_date BETWEEN (CURDATE() - INTERVAL #{param.max} MONTH) AND (CURDATE() - INTERVAL #{param.min} MONTH)
            </foreach>
        </if>
        <!-- 经营状态 -->
        <if test="statusList != null and statusList.size() > 0">
            <if test="statusInclude != null and statusInclude">
                AND status IN
            </if>
            <if test="statusInclude != null and !statusInclude">
                AND status NOT IN
            </if>
            <foreach collection="statusList" item="status" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
        <!-- 注册资本 -->
        <if test="registCapiCur != null and registCapiCur != ''">
            AND regist_capi_cur = #{registCapiCur}
        </if>
        <if test="registCapiIntervalList != null and registCapiIntervalList.size() > 0">
            AND
            <foreach collection="registCapiIntervalList" item="param" open="(" separator=" OR " close=")">
                regist_capi_value BETWEEN #{param.min} AND #{param.max}
            </foreach>
        </if>
        <!-- 实缴资本 -->
        <if test="recCapCur != null and recCapCur != ''">
            AND rec_cap_cur = #{recCapCur}
        </if>
        <if test="recCapIntervalList != null and recCapIntervalList.size() > 0">
            AND
            <foreach collection="recCapIntervalList" item="param" open="(" separator=" OR " close=")">
                rec_cap_value BETWEEN #{param.min} AND #{param.max}
            </foreach>
        </if>
        <!-- 养老保险参保人数 -->
        <if test="businessInfoPenInsurIntervalList != null and businessInfoPenInsurIntervalList.size() > 0">
            AND
            <foreach collection="businessInfoPenInsurIntervalList" item="param" open="(" separator=" OR " close=")">
                business_info_pen_insur_num BETWEEN #{param.min} AND #{param.max}
            </foreach>
        </if>
        <!-- 医疗保险参保人数 -->
        <if test="businessInfoMediInsurIntervalList != null and businessInfoMediInsurIntervalList.size() > 0">
            AND
            <foreach collection="businessInfoMediInsurIntervalList" item="param" open="(" separator=" OR " close=")">
                business_info_medi_insur_num BETWEEN #{param.min} AND #{param.max}
            </foreach>
        </if>
        <!-- 有无进出口信息 -->
        <if test="businessInfoImExportFlag != null ">
            AND business_info_im_export_flag = #{businessInfoImExportFlag}
        </if>
        <if test="businessInfoImExportCreditGradeList != null and businessInfoImExportCreditGradeList.size() > 0">
            AND business_info_im_export_credit_grade IN
            <foreach collection="businessInfoImExportCreditGradeList" item="businessInfoImExportCreditGrade" open="(" separator="," close=")">
                #{businessInfoImExportCreditGrade}
            </foreach>
        </if>
        <!-- 有无政府扶持和奖励 -->
        <if test="businessInfoGovSupportFlag != null ">
            AND business_info_gov_support_flag = #{businessInfoGovSupportFlag}
        </if>
        <!-- 有无融资信息 -->
        <if test="businessInfoFinaInfoFlag != null ">
            AND business_info_fina_info_flag = #{businessInfoFinaInfoFlag}
        </if>
        <if test="businessInfoFinaRoundList != null and businessInfoFinaRoundList.size() > 0">
            AND business_info_fina_round IN
            <foreach collection="businessInfoFinaRoundList" item="businessInfoFinaRound" open="(" separator="," close=")">
                #{businessInfoFinaRound}
            </foreach>
        </if>
        <!-- 有无商标信息 -->
        <if test="intelPropRightTrademarkInfoFlag != null ">
            AND intel_prop_right_trademark_info_flag = #{intelPropRightTrademarkInfoFlag}
        </if>
        <!-- 有无专利信息 -->
        <if test="intelPropRightPatentInfoFlag != null ">
            AND intel_prop_right_patent_info_flag = #{intelPropRightPatentInfoFlag}
        </if>
        <!-- 有无软件著作权 -->
        <if test="intelPropRightSoftCopyrightFlag != null ">
            AND intel_prop_right_soft_copyright_flag = #{intelPropRightSoftCopyrightFlag}
        </if>
        <!-- 科技等级-类型 -->
        <if test="technologyLvlTypeList != null and technologyLvlTypeList.size() > 0">
            AND technology_lvl_type IN
            <foreach collection="technologyLvlTypeList" item="technologyLvlType" open="(" separator="," close=")">
                #{technologyLvlType}
            </foreach>
        </if>
        <!-- 科技等级-状态 -->
        <if test="technologyLvlStateList != null and technologyLvlStateList.size() > 0">
            AND technology_lvl_state IN
            <foreach collection="technologyLvlStateList" item="technologyLvlState" open="(" separator="," close=")">
                #{technologyLvlState}
            </foreach>
        </if>
        <!-- 科技等级-级别 -->
        <if test="technologyLvlLevelList != null and technologyLvlLevelList.size() > 0">
            AND technology_lvl_level IN
            <foreach collection="technologyLvlLevelList" item="technologyLvlLevel" open="(" separator="," close=")">
                #{technologyLvlLevel}
            </foreach>
        </if>
        <!-- 有无上市信息 -->
        <if test="capMarketListInfoFlag != null ">
            AND cap_market_list_info_flag = #{capMarketListInfoFlag}
        </if>
        <!-- 有无失信信息 -->
        <if test="riskCharDisInfoFlag != null ">
            AND risk_char_dis_info_flag = #{riskCharDisInfoFlag}
        </if>
        <!-- 有无破产清算 -->
        <if test="riskCharBankruptcyLiquiFlag != null ">
            AND risk_char_bankruptcy_liqui_flag = #{riskCharBankruptcyLiquiFlag}
        </if>
        <!-- 有无变更记录 -->
        <if test="riskCharChangeRecordFlag != null ">
            AND risk_char_change_record_flag = #{riskCharChangeRecordFlag}
        </if>
        <!-- 有无失信被执行人 -->
        <if test="riskCharDisPersonFlag != null ">
            AND risk_char_dis_person_flag = #{riskCharDisPersonFlag}
        </if>
        <!-- 有无严重失信违法 -->
        <if test="riskCharSncFlag != null ">
            AND risk_char_snc_flag = #{riskCharSncFlag}
        </if>
        <!-- 有无限制高消费 -->
        <if test="riskCharRhcFlag != null ">
            AND risk_char_rhc_flag = #{riskCharRhcFlag}
        </if>
        <!-- 有无行政处罚 -->
        <if test="operatRiskAdminPenaltyFlag != null ">
            AND operat_risk_admin_penalty_flag = #{operatRiskAdminPenaltyFlag}
        </if>
        <!-- 有无环保处罚 -->
        <if test="operatRiskEnvirPenaltyFlag != null ">
            AND operat_risk_envir_penalty_flag = #{operatRiskEnvirPenaltyFlag}
        </if>
        <!-- 有无欠税信息 -->
        <if test="operatRiskOweTaxInfoFlag != null ">
            AND operat_risk_owe_tax_info_flag = #{operatRiskOweTaxInfoFlag}
        </if>
        <!-- 有无终末案件 -->
        <if test="operatRiskFinalCaseFlag != null ">
            AND operat_risk_final_case_flag = #{operatRiskFinalCaseFlag}
        </if>
        <!-- 有无未结案件 -->
        <if test="operatRiskUnfinishCaseFlag != null ">
            AND operat_risk_unfinish_case_flag = #{operatRiskUnfinishCaseFlag}
        </if>
        <!-- 新增企业标识 -->
        <if test="newCompanyFlag != null">
            AND new_company_flag_${dept} = #{newCompanyFlag}
        </if>
        <!-- 是否是分支机构 -->
        <if test="isBranchFlag != null">
            AND is_branch_flag = #{isBranchFlag}
        </if>
        <!-- 是否有分支机构 -->
        <if test="isHasBranchFlag != null">
            AND is_has_branch_flag = #{isHasBranchFlag}
        </if>
    </sql>

    <sql id="orderCondition">
        ORDER BY id
        <if test="isAsc != null and isAsc">
            asc
        </if>
        <if test="isAsc != null and !isAsc">
            desc
        </if>
    </sql>

    <select id="selectByCustId" resultType="CustInfoRadar">
        SELECT *, new_company_flag_${dept} AS new_company_flag FROM cust_info_radar WHERE cust_id = #{custId}
    </select>


</mapper>