<?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.bonus.canteen.core.customer.mapper.CustOrgMapper">
    <resultMap id="custOrgMap" type="com.bonus.canteen.core.customer.model.CustOrg">
        <id property="id" column="id"/>
        <result property="orgId" column="org_id"/>
        <result property="orgNum" column="org_num"/>
        <result property="orgName" column="org_name"/>
        <result property="orgFullName" column="org_full_name"/>
        <result property="orgLevel" column="org_level"/>
        <result property="superId" column="super_id"/>
        <result property="orgHead" column="org_head"/>
        <result property="orgTel" column="org_tel"/>
        <result property="endDate" column="end_date"/>
        <result property="remarks" column="remarks"/>
        <result property="ifDel" column="if_del"/>
        <result property="revision" column="revision"/>
        <result property="crby" column="crby"/>
        <result property="crtime" column="crtime"/>
        <result property="upby" column="upby"/>
        <result property="uptime" column="uptime"/>
    </resultMap>

    <update id="updateChildOrg">
        UPDATE cust_org
        SET
        org_full_name = REPLACE(org_full_name, #{params.oldOrgFullName}, #{params.orgFullName}),
        org_full_id = REPLACE(org_full_id, #{params.oldOrgFullId}, #{params.orgFullId}),
        org_level = org_level + 1,
        end_date = IF(end_date IS NOT NULL &amp;&amp; (#{params.endDate} IS NULL || end_date > #{params.endDate} ), #{params.endDate}, end_date)
        <where>
            org_id IN
            <foreach collection="params.childOrgIdList" item="orgId" separator="," open="(" close=")">
                #{orgId}
            </foreach>
        </where>

    </update>

    <select id="getOrgLazyTree" resultType="com.bonus.canteen.core.customer.dto.CustOrgTreeDTO">
        select *,
        EXISTS(SELECT 1 FROM cust_org WHERE super_id = t.org_id AND if_del = 2) AS hasChildren
        from (
        SELECT c.org_id,
        c.org_num,
        c.org_name,
        c.org_full_name,
        c.org_level,
        c.super_id
        FROM cust_org c
        LEFT JOIN mkt_effective_detail e ON e.org_id = c.org_id
        WHERE 1 = 1
        <![CDATA[ AND e.org_id <> - 1 ]]>
        AND c.if_del = 2
        <if test="effId != -1">
            and e.eff_id = #{effId}
        </if>

        UNION ALL

        SELECT c.org_id,
        c.org_num,
        c.org_name,
        c.org_full_name,
        c.org_level,
        c.super_id
        FROM cust_org c
        WHERE c.super_id = - 1
        AND c.if_del = 2) t where 1 = 1
        <if test="superId != null">
            and t.super_id = #{superId}
        </if>
        order by t.org_num
    </select>

    <select id="listAllOrg" resultType="com.bonus.canteen.core.customer.dto.CustOrgTreeDTO">
        SELECT c.org_id,
               org_num,
               org_name,
               org_full_name,
               org_level,
               super_id,
               sort
        FROM cust_org c
        WHERE c.if_del = 2
    </select>

    <select id="pageCustOrgByParams" resultType="com.bonus.canteen.core.customer.vo.CustOrgVO">
        SELECT *  FROM cust_org org
        <where>
            org.if_del = 2
            <if test="custOrg.keyword != null and custOrg.keyword != ''">
                AND (org.org_name LIKE CONCAT('%',#{custOrg.keyword},'%') OR org.org_num LIKE CONCAT('%',#{custOrg.keyword},'%'))
            </if>
            <if test="custOrg.orgFullId != null and custOrg.orgFullId != ''">
                AND org.org_full_id LIKE concat(#{custOrg.orgFullId}, '%')
            </if>
            <if test="custOrg.orgIdList != null and custOrg.orgIdList.size() > 0">
                AND org.org_id IN
                <foreach collection="custOrg.orgIdList" item="orgId" separator="," open="(" close=")">
                    #{orgId}
                </foreach>
            </if>
            <if test="custOrg.endDate != null">
                AND (org.end_date >= #{custOrg.endDate} OR org.end_date IS NULL)
            </if>
            ORDER BY org.org_level, org.sort
        </where>
    </select>

    <select id="queryPageCustOrg" resultType="com.bonus.canteen.core.customer.vo.CustOrgVO">
        SELECT id,
        c.org_id,
        org_num,
        org_name,
        org_full_name,
        org_level,
        super_id,
        org_head,
        org_tel,
        end_date,
        remarks,
        if_del,
        revision,
        crby,
        crtime,
        upby,
        uptime,
        sort,
        org_head_id,
        dining_place
        FROM
        (
        select *
        from (select org_id,
        half_select,
        @rownum := if(@id = org_id, @rownum := @rownum + 1, 1) as rn,
        @id := org_id                                          as last_id
        from (
        SELECT o.org_id,
        o.half_select
        FROM mgr_role_org o
        LEFT JOIN mgr_role r ON r.role_id = o.role_id
        LEFT JOIN mgr_user_role u ON o.role_id = u.role_id
        WHERE r.del_flag = 2
        AND u.user_id = #{userId}
        ) s,
        (select @rownum := 0, @id := null) c
        ORDER BY org_id, half_select) t
        having rn = 1
        ) t
        LEFT JOIN cust_org c ON c.org_id = t.org_id
        WHERE c.if_del = 2

        <if test="custOrg.orgFullId != null and custOrg.orgFullId != ''">
            and c.org_full_id like concat(#{custOrg.orgFullId}, '%')
        </if>
        <if test="custOrg.orgName != null and custOrg.orgName != ''">
            and c.org_name like concat('%', #{custOrg.orgName}, '%')
        </if>
        <if test="custOrg.orgIdList != null and custOrg.orgIdList.size() > 0">
            and c.org_id in
            <foreach collection="custOrg.orgIdList" item="orgId" separator="," open="(" close=")">
                #{orgId}
            </foreach>
        </if>
        order by org_level, sort
    </select>

    <select id="superInDescendantNode" resultType="java.lang.Boolean">
        SELECT COUNT(id)
        FROM cust_org
        WHERE
            org_id = #{superId}
            AND org_full_id LIKE CONCAT(#{orgFullId},'%')
    </select>
    <select id="queryCustOrgVOList" resultType="com.bonus.canteen.core.customer.vo.CustOrgVO">
        SELECT id, org_id, org_num, org_name, org_full_name, org_level, super_id, org_head, org_tel, end_date, remarks, if_del, revision, crby, crtime, upby, uptime, sort
        FROM cust_org
        WHERE org_id in
        <foreach collection="orgIdList" item="orgId" separator="," open="(" close=")">
            #{orgId}
        </foreach>
    </select>

    <select id="listDescendantOrgIdByOrgFullId" resultType="java.lang.Long">
        SELECT org_id
        FROM cust_org
        WHERE
        org_full_id LIKE CONCAT(#{orgFullId},'_%');
    </select>

    <select id="listChildOrgIdByOrgId" resultType="java.lang.Long">
        SELECT org_id
        FROM cust_org
        WHERE
        org_full_id LIKE CONCAT(
            (
            SELECT org_full_id
            FROM cust_org
            WHERE org_id = #{orgId}
            )
        ,'_%');
    </select>

    <select id="listOrgBasicInfoByParams" resultType="com.bonus.canteen.core.customer.vo.OrgBasicInfoVO">
        SELECT id, org_id, org_num, org_name, org_full_name, super_id, if_del
        FROM cust_org
        <where>

            <if test="params.delFlags != null and params.delFlags.size() > 0">
                if_del IN
                <foreach collection="params.delFlags" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test="params.orgNums != null and params.orgNums.size() > 0">
                AND org_num IN
                <foreach collection="params.orgNums" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test="params.orgIds != null and params.orgIds.size() > 0">
                AND org_id IN
                <foreach collection="params.orgIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test="params.ids != null and params.ids.size() > 0">
                AND id IN
                <foreach collection="params.ids" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test="params.superIds != null and params.superIds.size() > 0">
                AND super_id IN
                <foreach collection="params.superIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>

    <select id="getOrgByCustId" resultType="com.bonus.canteen.core.customer.model.CustOrg">
        SELECT t2.org_id,
        t2.org_full_id
        FROM cust_org t2
        INNER JOIN cust_info t1 ON t1.org_id = t2.org_id
        WHERE t1.cust_id = #{custId}
    </select>

</mapper>
