<?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.grid.mapper.AnchorMapper">

    <select id="selectAnchorList" resultType="com.ruoyi.ibs.grid.domain.vo.AnchorVo">
        select
        a.id as id,
        a.legal_id as legalId,
        a.cust_id as custId,
        a.anchor_name as anchorName,
        a.belong_business as belongBusiness,
        a.cust_status as custStatus,
        a.cust_address_status as custAddressStatus,
        b.address as address,
        b.source as source,
        b.province as province,
        b.city as city,
        b.county as county,
        b.address_name as addressName,
        b.street as street,
        b.community as community,
        b.address_detail as addressDetail,
        a.status as status,
        a.anchor_remark as anchorRemark,
        b.longitude as longitude,
        b.latitude as latitude
        from ibs_anchor a
        left join
        (select id,
        source,
        address,
        province,
        city,
        county,
        address_name,
        street,
        community,
        address_detail,
        longitude,
        latitude
        from
        ibs_anchor_address
        where address_status=1
        and delete_status = 0) b
        on a.address_id = b.id
        where a.delete_status = 0
        and a.cust_type = #{custType}
        <if test=" isHead == false">and a.update_org = #{deptId}</if>
        <if test=" anchorName != null  and anchorName != ''">and a.anchor_name like
            concat('%',concat(#{anchorName},'%'))
        </if>
        <if test=" street != null  and street != ''">and b.street like concat('%',concat(#{street},'%'))</if>
        <if test=" community != null  and community != ''">and b.community like concat('%',concat(#{community},'%'))
        </if>
        <if test=" source != null  and source != ''">and b.source like concat('%',concat(#{source},'%'))</if>
        <if test=" custAddressStatus != null  and custAddressStatus != ''">and a.cust_address_status =
            #{custAddressStatus}
        </if>
        <if test=" custStatus != null  and custStatus != ''">and a.cust_status = #{custStatus}</if>
        <if test=" status != null  and status != ''">and a.status = #{status}</if>
    </select>
    <select id="selectAnchorList_COUNT" resultType="Long">
        select 999999999
    </select>
    <select id="selectAnchorListWithRegion" resultType="com.ruoyi.ibs.grid.domain.vo.AnchorVo">
        select a.id as id,
        a.anchor_name as anchorName,
        a.belong_business as belongBusiness,
        a.cust_status as cust_status,
        a.cust_address_status,
        b.source as source,,
        b.address_name as addressName,
        b.street as street,
        b.community as community,
        b.address_detail as addressDetail,
        a.status as status,
        a.anchor_remark as anchorRemark,
        b.longitude as longitude,
        b.latitude as latitude
        from ibs_anchor a
        left join
        (select id, address_name,street,community,address_detail,longitude,latitude,region_code,source
        from ibs_anchor_address
        where address_status = 1
        and delete_status = 0 ) b on a.address_id = b.id
        left join grid_region_admin_division_relate c on b.region_code = c.code
        left join grid_region_user_relate d on c.grid_id = d.grid_id
        where d.user_name = #{userid}
        and a.delete_status = 0
        and a.cust_type = #{custType}
        <if test=" anchorName != null  and anchorName != ''">and a.anchor_name like
            concat('%',concat(#{anchorName},'%'))
        </if>
        <if test=" street != null  and street != ''">and street like concat('%',concat(#{street},'%'))</if>
        <if test=" community != null  and community != ''">and community like concat('%',concat(#{community},'%'))</if>
        <if test=" source != null  and source != ''">and a.source like concat('%',concat(#{source},'%'))</if>
        <if test=" custAddressStatus != null  and custAddressStatus != ''">and a.cust_address_status =
            #{custAddressStatus}
        </if>
        <if test=" custStatus != null  and custStatus != ''">and a.cust_status = #{custStatus}</if>
    </select>

    <select id="selectAuditList" resultType="com.ruoyi.ibs.grid.domain.vo.AuditVo">
        select
        a.id as id,
        concat(c.nick_name,'-',c.user_name) as nickName,
        a.update_time as update_time,
        b.anchor_name as anchorName,
        b.belong_business as belongBusiness,
        b.source as source,
        b.cust_status as custStatus,
        b.cust_address_status as custAddressStatus,
        a.type as type,
        a.address_name as addressName,
        a.address as address,
        a.address_now as addressNow,
        a.province_now as provinceNow,
        a.city_now as cityNow,
        a.county_now as countyNow,
        a.street_now as streetNow,
        a.community_now as communityNow,
        a.address_detail_now as addressDetailNow,
        a.address_remark as addressRemark,
        a.region_code as region_code
        from ibs_anchor_address a
        left join ibs_anchor b on a.anchor_id = b.legal_id
        left join sys_user c on a.update_by = c.user_name
        where a.address_status = 0
        and a.delete_status = 0
        and b.delete_status = 0
        and b.cust_type = #{custType}
        <if test=" isHead == false">and b.update_org = #{deptId}</if>
        <if test=" custAddressStatus != null  and custAddressStatus != ''">and a.cust_address_status =
            #{custAddressStatus}
        </if>
        <if test=" custStatus != null  and custStatus != ''">and a.cust_status = #{custStatus}</if>
    </select>
    <select id="selectAuditList_COUNT" resultType="Long">
        select 999999999
    </select>
    <select id="selectIgnoreList" resultType="com.ruoyi.ibs.grid.domain.vo.IgnoreVo">
        select
        a.id as id,
        a.update_time as update_time,
        b.anchor_name as anchorName,
        b.belong_business as belongBusiness,
        a.source as source,
        a.type as type,
        a.address_name as addressName,
        a.address_remark as addressRemark,
        a.address as address,
        a.address_now as addressNow
        from ibs_anchor_address a
        left join ibs_anchor b on a.anchor_id = b.legal_id
        where a.address_status = 2
        and a.delete_status = 0
        and b.delete_status = 0
        and b.cust_type = #{custType}
        <if test=" isHead == false">and b.update_org = #{deptId}</if>
        <if test=" custAddressStatus != null  and custAddressStatus != ''">and a.cust_address_status =
            #{custAddressStatus}
        </if>
        <if test=" custStatus != null  and custStatus != ''">and a.cust_status = #{custStatus}</if>
    </select>

    <select id="selectIgnoreList_COUNT" resultType="Long">
        select 999999999
    </select>

    <update id="delIgnore">
        update ibs_anchor_address set delete_status = 1 where id = #{id}
    </update>

    <update id="delAddressByLegalId">
        update ibs_anchor_address set delete_status = 1 where anchor_id = #{legalId}
    </update>

    <update id="delAnchorByLegalId">
        update ibs_anchor set delete_status = 1 where legal_id = #{legalId}
    </update>
    <update id="delAnchor">
        update ibs_anchor set delete_status = 1 where id = #{id}
    </update>
    <select id="selectAnchorDetail" resultType="com.ruoyi.ibs.grid.domain.vo.AnchorDetail">
        select
        id,
        legal_id,
        anchor_name,
        cust_type,
        belong_business,
        anchor_remark
        from ibs_anchor where id = #{anchorid}
    </select>

    <select id="selectAnchorAddressList" resultType="com.ruoyi.ibs.grid.domain.vo.AddressDetail">
        select
        a.id as id,
        a.address_name as addressName,
        a.address as address,
        a.source as source,
        a.longitude as longitude,
        a.latitude as latitude
        from ibs_anchor_address a
        left join ibs_anchor b on a.anchor_id = b.legal_id
        where  b.id = #{anchorid}
        and a.delete_status = 0
        and a.address_status = 1
    </select>

    <select id="selectCustAddressList" resultType="com.ruoyi.ibs.grid.domain.vo.AddressDetail">
        select
        a.id as id,
        a.address_name as addressName,
        a.address as address,
        a.source as source,
        a.longitude as longitude,
        a.latitude as latitude
        from ibs_anchor_address a
        left join ibs_anchor b on a.anchor_id = b.legal_id
        where  b.legal_id = #{legalId}
        and a.delete_status = 0
        and a.address_status = 1
    </select>


    <select id="selectAnchorAddressListByCustId" resultType="com.ruoyi.ibs.grid.domain.vo.AddressDetail">
        select a.id           as id,
               a.address_name as addressName,
               a.address      as address,
               a.source       as source,
               a.longitude    as longitude,
               a.latitude     as latitude
        from ibs_anchor_address a
                 left join ibs_anchor b on a.anchor_id = b.legal_id
        where b.cust_id = #{custId}
          and a.delete_status = 0
          and a.address_status = 1
    </select>

    <update id="reAudit">
        update ibs_anchor_address set address_status = 0 where id = #{id}
    </update>

    <update id="setIgnore">
        update ibs_anchor_address set address_status = 2 where id = #{id}
    </update>


    <update id="updateAnchor">
        update ibs_anchor
         set
         anchor_name = #{anchorName},
         anchor_remark = #{anchorRemark},
         belong_business = #{belongBusiness},
         cust_type = #{custType}
         where legal_id = #{legalId}
    </update>

    <update id="updateAnchor2">
        update ibs_anchor
         set
         address_id = #{addressid}
         where legal_id = #{legalId}
    </update>

    <select id="selectAddressDetailById" resultType="com.ruoyi.ibs.grid.domain.vo.AddressDetail">
        select
        id,
        address_name,
        address,
        source,
        longitude,
        latitude
        from ibs_anchor_address
        where  id = #{id}
    </select>

    <select id="selectChangeAnchor" resultType="java.lang.String">
        select
        a.id as id
        from ibs_anchor_address a
        left join ibs_anchor b on a.anchor_id = b.legal_id
        where a.address_status = 0
        and a.delete_status = 0
        and b.delete_status = 0
        and b.cust_type = #{custType}
        <if test=" isHead == false ">and a.update_by = #{userid}</if>
    </select>

    <select id="selectLaLu" resultType="com.ruoyi.ibs.grid.domain.vo.LaLuVo">
        select b.latitude as lat,b.longitude as lng from ibs_anchor a
        left join ibs_anchor_address b on a.address_id = b.id
        where cust_id = #{custId}
        and a.delete_status = 0
    </select>

    <select id="selectBusinessCust" resultType="java.lang.String">
        select id from cust_info_business where social_credit_code = #{legal_id}
    </select>

    <select id="selectMerchantCust" resultType="java.lang.String">
        select id from cust_info_merchant where social_credit_code = #{legal_id}
    </select>

    <select id="selectRetailCust" resultType="java.lang.String">
        select id from cust_info_retail where cust_idc = #{legal_id}
    </select>

    <update id="updateAnchorByCust">
        update ibs_anchor
         set
         anchor_name = #{anchorName},
         belong_business = #{belongBusiness}
         where legal_id = #{legalId}
    </update>

    <select id="selectCountCust" resultType="java.lang.Long">
        select count(*) from ibs_anchor
        where delete_status = 0
        and cust_status = 1
        and cust_type = #{custType}
        <if test=" isHead == false ">and update_by = #{userid}</if>
    </select>

    <select id="selectCountCustAddress" resultType="java.lang.Long">
        select count(*) from ibs_anchor
        where delete_status = 0
        and cust_address_status = 1
        and cust_type = #{custType}
        <if test=" isHead == false ">and update_by = #{userid}</if>
    </select>

    <select id="selectAddressByCustIdc" resultType="java.lang.String">
        select b.address from ibs_anchor a left join ibs_anchor_address b on a.address_id = b.id where a.legal_id = #{custIdn}
    </select>
</mapper>