<?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.github.wxiaoqi.security.admin.mapper.BranchCompanyMapper">
    <resultMap id="BaseResultMap" type="com.github.wxiaoqi.security.admin.entity.BranchCompany">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="short_name" property="shortName" jdbcType="VARCHAR"/>
        <result column="branch_type" property="branchType" jdbcType="INTEGER"/>
        <result column="subordinate_branch" property="subordinateBranch" jdbcType="INTEGER"/>
        <result column="addr_province" property="addrProvince" jdbcType="INTEGER"/>
        <result column="addr_city" property="addrCity" jdbcType="INTEGER"/>
        <result column="addr_town" property="addrTown" jdbcType="INTEGER"/>
        <result column="addr_detail" property="addrDetail" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="vehice_service_phone" property="vehiceServicePhone"/>
        <result column="tour_service_phone" property="tourServicePhone"/>
        <result column="longitude" property="longitude"/>
        <result column="latitude" property="latitude"/>
        <result column="zone_id" property="zoneId"/>
        <result column="state" property="state"/>
    </resultMap>

    <select id="search" parameterType="com.github.wxiaoqi.security.admin.dto.CompanySearchDTO" resultType="com.github.wxiaoqi.security.admin.vo.CompanySearchVO">
        select c.*,i.name as companyName,l.name as levelName
        <if test="lon != null and lat != null">
            , ROUND(( (2 * ASIN( SQRT( POW( SIN((c.latitude * PI() / 180.0
            -${lat} * PI() / 180.0)/2), 2)
            +COS( c.latitude * PI() / 180.0)*COS(${lat} * PI() / 180.0)
            *POW(SIN((c.longitude * PI() / 180.0 - ${lon} * PI() /
            180.0)/2),2))))*6378.137)*10000)/10000 as distance
        </if>
        from branch_company c
        LEFT JOIN  company_info i on  c.company_id=i.id
        LEFT JOIN  branch_company_level l on  c.branch_type=l.level
        <where>
             c.is_del = 0
            <if test="id != null">
                and c.id = #{id}
            </if>
            <if test="addrCity != null">
                and (c.addr_city = #{addrCity} or c.addr_province = #{addrCity} or c.addr_town = #{addrCity})
            </if>
            <if test="goodsType != null">
                and  FIND_IN_SET(#{goodsType},business_scope)
            </if>

            <if test="addrProvince != null">
                and  c.addr_province = #{addrProvince}
            </if>
            <if test="lon != null and lat != null">
                and c.longitude is not null and c.latitude is not null
            </if>
            <if test="state != null">
                and c.state = #{state}
            </if>
            <if test="companyId != null">
                and c.company_id = #{companyId}
            </if>
            <if test="isShow != null">
                and c.is_show = #{isShow}
            </if>
            <if test="name != null and name != '' ">
                and ( c.name like  concat('%',#{name},'%') or  c.short_name like  concat('%',#{name},'%') )
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and c.id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
                and c.id in
                <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
                and c.company_id  in
                <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        GROUP BY c.id
        <choose>
            <when test="lon != null and lat != null">
                order by distance asc
            </when>
            <otherwise>
                order by c.id desc
            </otherwise>
        </choose>
    </select>


</mapper>