<?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.device.manage.mapper.DeviceInfoMapper">

    <select id="list" resultType="com.bonus.canteen.core.device.manage.model.DeviceInfo">
        SELECT
        A.device_id,
        A.eff_id,
        A.device_sn,
        A.device_name,
        A.device_num,
        A.device_model,
        A.device_type,
        A.device_pwd,
        A.device_addr,
        A.device_key,
        A.device_parent_id,
        A.device_parent_type,
        A.device_service_ip,
        A.device_service_port,
        A.version_code,
        A.version_name,
        A.online_state,
        A.consum_mode,
        A.device_agreement,
        A.comm_mode,
        A.device_ip,
        A.device_mask,
        A.device_mac,
        A.device_gateway,
        A.first_dns,
        A.next_dns,
        A.if_quota_limit,
        A.if_discount,
        A.if_time_limit,
        A.if_use_call_num,
        A.if_enable,
        A.img_url,
        A.amount_notify,
        A.binding_valid_time,
        A.ord_automatic_pay,
        A.if_off_line_pay,
        A.alarm_time,
        A.weight_rem_alarm,
        A.spoon_weight,
        A.heart_beat_time,
        A.carousel_time,
        A.title_slogan,
        A.font_size,
        A.gui_style,
        A.image_map,
        A.font_color,
        A.line_num,
        A.once_pay_amount,
        A.data_status,
        A.ord_open_limit,
        A.detail_num_limit,
        A.terminal_sn,
        A.terminal_key,
        A.pri_key,
        A.gate_default_amount,
        A.revision,
        A.crby,
        A.crtime,
        A.upby,
        A.uptime,
        A.device_state
        FROM device_info A
        <trim prefix="WHERE" prefixOverrides="AND|OR ">
            <if test="param.deviceName != null and param.deviceName.trim() != ''">
                AND A.device_name = #{param.deviceName}
            </if>
            <if test="param.deviceSearchInfo != null and param.deviceSearchInfo.trim() != ''">
                AND ( A.device_sn like  CONCAT('%', #{param.deviceSearchInfo}, '%')
                OR A.device_name like  CONCAT('%', #{param.deviceSearchInfo}, '%')
                OR A.device_num like  CONCAT('%', #{param.deviceSearchInfo}, '%'))
            </if>

            <if test="param.deviceModel != null and param.deviceModel.trim() != ''">
                AND A.device_model = #{param.deviceModel}
            </if>
            <if test="param.deviceParentId != null">
                AND A.device_parent_id = #{param.deviceParentId}
            </if>
            <if test="param.deviceSnList != null and param.deviceSnList.size() > 0">
                AND A.device_sn IN
                <foreach collection="param.deviceSnList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceNumList != null and param.deviceNumList.size() > 0">
                AND A.device_num IN
                <foreach collection="param.deviceNumList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceTypeList != null and param.deviceTypeList.size() > 0">
                AND A.device_type IN
                <foreach collection="param.deviceTypeList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceIdList != null and param.deviceIdList.size() > 0">
                AND A.device_id IN
                <foreach collection="param.deviceIdList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.needSearchBind">
                AND EXISTS(
                SELECT NULL FROM device_bind IA
                WHERE IA.device_id = A.device_id
                <if test="param.recipeId != null">
                    AND IA.recipe_id = #{param.recipeId}
                </if>
                <if test="param.canteenIdList != null and param.canteenIdList.size() > 0">
                    AND IA.canteen_id IN
                    <foreach collection="param.canteenIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.stallIdList != null and param.stallIdList.size() > 0">
                    AND IA.stall_id IN
                    <foreach collection="param.stallIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.mealLineIdList != null and param.mealLineIdList.size() > 0">
                    AND IA.meal_line_id IN
                    <foreach collection="param.mealLineIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>

                )
            </if>
        </trim>
        ORDER BY A.device_id DESC

    </select>


     <select id="listpermission" resultType="com.bonus.canteen.core.device.manage.model.DeviceInfo">
        SELECT
        A.device_id,
        A.eff_id,
        A.device_sn,
        A.device_name,
        A.device_num,
        A.device_model,
        A.device_type,
        A.device_pwd,
        A.device_addr,
        A.device_key,
        A.device_parent_id,
        A.device_parent_type,
        A.device_service_ip,
        A.device_service_port,
        A.version_code,
        A.version_name,
        A.online_state,
        A.consum_mode,
        A.comm_mode,
        A.device_agreement,
        A.device_ip,
        A.device_mask,
        A.device_mac,
        A.device_gateway,
        A.first_dns,
        A.next_dns,
        A.if_quota_limit,
        A.if_discount,
        A.if_time_limit,
        A.if_use_call_num,
        A.if_enable,
        A.img_url,
        A.amount_notify,
        A.binding_valid_time,
        A.ord_automatic_pay,
        A.if_off_line_pay,
        A.alarm_time,
        A.weight_rem_alarm,
        A.spoon_weight,
        A.heart_beat_time,
        A.carousel_time,
        A.title_slogan,
        A.font_size,
        A.gui_style,
        A.image_map,
        A.font_color,
        A.line_num,
        A.once_pay_amount,
        A.data_status,
        A.ord_open_limit,
        A.detail_num_limit,
        A.terminal_sn,
        A.terminal_key,
        A.pri_key,
        A.gate_default_amount,
        A.revision,
        A.crby,
        A.crtime,
        A.upby,
        A.uptime,
        A.device_state
        FROM device_info A
        <trim prefix="WHERE" prefixOverrides="AND|OR ">
            <if test="param.deviceName != null and param.deviceName.trim() != ''">
                AND A.device_name = #{param.deviceName}
            </if>

            <if test="param.deviceName != null and param.deviceName.trim() != ''">
                AND A.device_name = #{param.deviceName}
            </if>
            <if test="param.onlineState != null">
                AND A.online_state = #{param.onlineState}
            </if>

            <if test="param.deviceSearchInfo != null and param.deviceSearchInfo.trim() != ''">
                AND ( A.device_sn like  CONCAT('%', #{param.deviceSearchInfo}, '%')
                OR A.device_name like  CONCAT('%', #{param.deviceSearchInfo}, '%')
                OR A.device_num like  CONCAT('%', #{param.deviceSearchInfo}, '%'))
            </if>

            <if test="param.deviceModel != null and param.deviceModel.trim() != ''">
                AND A.device_model = #{param.deviceModel}
            </if>
            <if test="param.deviceParentId != null">
                AND A.device_parent_id = #{param.deviceParentId}
            </if>
            <if test="param.ifEnable != null">
                AND A.if_enable = #{param.ifEnable}
            </if>

            <if test="param.deviceSnList != null and param.deviceSnList.size() > 0">
                AND A.device_sn IN
                <foreach collection="param.deviceSnList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceNumList != null and param.deviceNumList.size() > 0">
                AND A.device_num IN
                <foreach collection="param.deviceNumList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceTypeList != null and param.deviceTypeList.size() > 0">
                AND A.device_type IN
                <foreach collection="param.deviceTypeList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceIdList != null and param.deviceIdList.size() > 0">
                AND A.device_id IN
                <foreach collection="param.deviceIdList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.needSearchBind">
                AND EXISTS(
                SELECT NULL FROM device_bind IA
                WHERE IA.device_id = A.device_id
                <if test="param.recipeId != null">
                    AND IA.recipe_id = #{param.recipeId}
                </if>
                <if test="param.canteenIdList != null and param.canteenIdList.size() > 0">
                    AND IA.canteen_id IN
                    <foreach collection="param.canteenIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.stallIdList != null and param.stallIdList.size() > 0">
                    AND IA.stall_id IN
                    <foreach collection="param.stallIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.mealLineIdList != null and param.mealLineIdList.size() > 0">
                    AND IA.meal_line_id IN
                    <foreach collection="param.mealLineIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.canteenStallIdListPermission != null and param.canteenStallIdListPermission.size() > 0">
                    AND (IA.canteen_id IN
                    <foreach collection="param.canteenStallIdListPermission" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    OR IA.stall_id IN
                    <foreach collection="param.canteenStallIdListPermission" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    )
                </if>
                )
            </if>
        </trim>


    </select>


    <select id="listpermissionchupinji" resultType="com.bonus.canteen.core.device.manage.model.DeviceInfo">
        SELECT
        A.device_id,
        A.eff_id,
        A.device_sn,
        A.device_name,
        A.device_num,
        A.device_model,
        A.device_type,
        A.device_pwd,
        A.device_addr,
        A.device_key,
        A.device_parent_id,
        A.device_parent_type,
        A.device_service_ip,
        A.device_service_port,
        A.version_code,
        A.version_name,
        A.online_state,
        A.consum_mode,
        A.device_agreement,
        A.comm_mode,
        A.device_ip,
        A.device_mask,
        A.device_mac,
        A.device_gateway,
        A.first_dns,
        A.next_dns,
        A.if_quota_limit,
        A.if_discount,
        A.if_time_limit,
        A.if_use_call_num,
        A.if_enable,
        A.img_url,
        A.amount_notify,
        A.binding_valid_time,
        A.ord_automatic_pay,
        A.if_off_line_pay,
        A.alarm_time,
        A.weight_rem_alarm,
        A.spoon_weight,
        A.heart_beat_time,
        A.carousel_time,
        A.title_slogan,
        A.font_size,
        A.gui_style,
        A.image_map,
        A.font_color,
        A.line_num,
        A.once_pay_amount,
        A.data_status,
        A.ord_open_limit,
        A.detail_num_limit,
        A.terminal_sn,
        A.terminal_key,
        A.pri_key,
        A.gate_default_amount,
        A.revision,
        A.crby,
        A.crtime,
        A.upby,
        A.uptime,
        A.device_state
        FROM device_info A
        <trim prefix="WHERE" prefixOverrides="AND|OR ">
            <if test="param.deviceName != null and param.deviceName.trim() != ''">
                AND A.device_name = #{param.deviceName}
            </if>

            <if test="param.deviceName != null and param.deviceName.trim() != ''">
                AND A.device_name = #{param.deviceName}
            </if>
            <if test="param.deviceSearchInfo != null and param.deviceSearchInfo.trim() != ''">
                AND (  A.device_name like  CONCAT('%', #{param.deviceSearchInfo}, '%')
                OR A.device_num like  CONCAT('%', #{param.deviceSearchInfo}, '%'))
            </if>

            <if test="param.deviceModel != null and param.deviceModel.trim() != ''">
                AND A.device_model = #{param.deviceModel}
            </if>
            <if test="param.deviceParentId != null">
                AND A.device_parent_id = #{param.deviceParentId}
            </if>

            <if test="param.deviceSnList != null and param.deviceSnList.size() > 0">
                AND A.device_sn IN
                <foreach collection="param.deviceSnList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceNumList != null and param.deviceNumList.size() > 0">
                AND A.device_num IN
                <foreach collection="param.deviceNumList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceTypeList != null and param.deviceTypeList.size() > 0">
                AND A.device_type IN
                <foreach collection="param.deviceTypeList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.deviceIdList != null and param.deviceIdList.size() > 0">
                AND A.device_id IN
                <foreach collection="param.deviceIdList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.needSearchBind">
                AND EXISTS(
                SELECT NULL FROM device_bind IA
                WHERE IA.device_id = A.device_id
                <if test="param.recipeId != null">
                    AND IA.recipe_id = #{param.recipeId}
                </if>
                <if test="param.canteenIdList != null and param.canteenIdList.size() > 0">
                    AND IA.canteen_id IN
                    <foreach collection="param.canteenIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.stallIdList != null and param.stallIdList.size() > 0">
                    AND IA.stall_id IN
                    <foreach collection="param.stallIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.mealLineIdList != null and param.mealLineIdList.size() > 0">
                    AND IA.meal_line_id IN
                    <foreach collection="param.mealLineIdList" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="param.canteenStallIdListPermission != null and param.canteenStallIdListPermission.size() > 0">
                    AND (IA.canteen_id IN
                    <foreach collection="param.canteenStallIdListPermission" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    OR IA.stall_id IN
                    <foreach collection="param.canteenStallIdListPermission" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    )
                </if>
                )
            </if>
        </trim>
<!--        <if test="param.deviceTypeList!=null and param.deviceTypeList.size() >0 and param.asc==false">-->
<!--            ORDER BY A.device_num DESC-->
<!--        </if>-->


    </select>



    <select id="getMaxOrdNum" resultType="java.lang.Integer">
        SELECT ord_open_limit * detail_num_limit
        FROM device_info
        WHERE ord_open_limit != -1
          AND detail_num_limit != -1
          AND device_type = #{deviceType}
          AND device_id = #{deviceId}
            LIMIT 1
    </select>

<!--    <select id="getMaxOrdNumList" resultType="net.xnzn.core.device.manage.vo.DeviceOrdNumVo">-->
<!--        SELECT-->
<!--        ord_open_limit * detail_num_limit AS ordDetailNumMax ,-->
<!--        device_id AS deviceId-->
<!--        FROM device_info-->
<!--        WHERE ord_open_limit != -1-->
<!--        AND detail_num_limit != -1-->
<!--        AND device_type = #{deviceType}-->
<!--        AND device_id IN-->
<!--        <foreach close=")" collection="deviceIds" item="item" open="(" separator=",">-->
<!--            #{item}-->
<!--        </foreach>-->
<!--    </select>-->

<!--    <select id="listTenantDevice" resultType="net.xnzn.core.marketing.v2.vo.MarketDeviceTreeVO">-->
<!--        SELECT-->
<!--            i.device_sn AS id,-->
<!--            IFNULL( b.stall_id, b.canteen_id ) AS parent_id,-->
<!--            5 AS treeType,-->
<!--            i.device_name AS treeName,-->
<!--            1 AS halfSelect,-->
<!--            b.canteen_id,-->
<!--            b.stall_id,-->
<!--            i.device_type,-->
<!--            i.device_sn,-->
<!--            i.device_name-->
<!--        FROM-->
<!--            device_bind b-->
<!--                LEFT JOIN device_info i ON i.device_id = b.device_id-->
<!--        WHERE-->
<!--            i.if_enable = 1-->
<!--            <if test="deviceTypes != null and deviceTypes.size() > 0">-->
<!--                AND i.device_type IN-->
<!--                <foreach collection="deviceTypes" item="deviceType" open="(" separator="," close=")">-->
<!--                    #{deviceType}-->
<!--                </foreach>-->
<!--            </if>-->

<!--            AND ( 1=2-->
<!--                <if test="stallIds != null and stallIds.size() > 0">-->
<!--                    OR b.stall_id IN-->
<!--                    <foreach collection="stallIds" item="stallId" open="(" separator="," close=")">-->
<!--                        #{stallId}-->
<!--                    </foreach>-->
<!--                </if>-->
<!--                <if test="canteenIds != null and canteenIds.size() > 0">-->
<!--                    OR (b.stall_id IS NULL AND b.canteen_id IN-->
<!--                    <foreach collection="canteenIds" item="canteenId" open="(" separator="," close=")">-->
<!--                        #{canteenId}-->
<!--                    </foreach>-->
<!--                        )-->
<!--                </if>-->
<!--                )-->
<!--    </select>-->

    <select id="getDeviceTypeCountMap" resultType="com.bonus.canteen.core.device.manage.dto.DeviceTypeDto">
        select device_type ,count(1) as deviceCount from device_info group by device_type
    </select>
    <select id="listLocaltionDevices" resultType="java.lang.String"
            parameterType="com.bonus.canteen.core.device.manage.dto.DeviceSearchDTO">
        SELECT
        di.device_sn
        FROM
        device_info di
        LEFT JOIN device_bind db ON di.device_id = db.device_id
        WHERE di.if_enable = 1
        <if test="param.canteenId != null">
            AND db.canteen_id = #{param.canteenId}
        </if>
        <if test="param.stallId != null">
            AND db.stall_id = #{param.stallId}
        </if>
        <if test="param.deviceType != null">
            AND di.device_type = #{param.deviceType}
        </if>


    </select>
</mapper>
