<?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.xxfc.platform.vehicle.mapper.BranchCompanyMapper">
    <resultMap id="BaseResultMap" type="com.xxfc.platform.vehicle.entity.BranchCompany">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" 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" resultType="com.xxfc.platform.vehicle.pojo.CompanySearchVO">
        select *
        <if test="lon != null and lat != null">
            , st_distance_sphere(point(#{lon}, #{lat}), point(longitude, latitude)) as distance
        </if>
        from branch_company
        <where>
            <if test="addrCity != null">
                and addr_city = #{addrCity}
            </if>
            <if test="lon != null and lat != null">
                and longitude is not null and latitude is not null
            </if>
            <if test="state != null">
                and state = #{state}
            </if>
            <if test="isShow != null">
                and is_show = #{isShow}
            </if>
            <if test="isDel != null">
                and is_del = #{isDel}
            </if>
        </where>
        <if test="lon != null and lat != null">
            order by distance asc
        </if>
    </select>
    <select id="selectByZoneId" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.entity.BranchCompany">
        select * from branch_company
        where zone_id = #{zoneId}
        <if test="ids != null and ids.size > 0">
            and id in
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </select>
    <select id="findCompanyIdsByAreaId" resultType="integer">
       select `id` from `branch_company` where `zone_id`=#{areaId}
   </select>

    <select id="findBranchCompanys" resultType="com.xxfc.platform.vehicle.pojo.dto.BranchCompanyListDTO">
        SELECT
        cb.id AS `comnpanyBaseId`,
        cb.province_name AS `provinceName`,
        cb.city_name AS `cityName`,
        cb.town_name AS `townName`,
        bc.addr_detail AS `address`,
        bc.NAME AS `name`,
        cb.name AS `companyName`,
        cb.cover,
        bc.leader,
        bc.leader_contact_info AS `leaderContactInfo`,
        bc.longitude,
        bc.latitude
        FROM
        (SELECT * FROM `branch_company` WHERE `is_del`=0 AND `state`=1
         <if test="name !=null and name !='' ">
             and name like concat('%',#{name},'%')
         </if>
        ) AS `bc`
        INNER JOIN (SELECT * FROM `company_base` WHERE `is_del`=0
        <if test="provinceCode != null">
           AND `addr_province`=#{provinceCode}
        </if>
        <if test="cityCode != null">
           AND  `addr_city`=#{cityCode}
        </if>
        ) AS `cb` ON cb.id = bc.company_base_id ORDER  BY bc.id
    </select>
</mapper>