<?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.kxs.store.biz.mapper.KxsMachineAdvanceMapper">

    <resultMap type="com.kxs.store.api.model.KxsMachineAdvance" id="KxsMachineAdvanceMap">
        <result column="id" property="id"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="version" property="version"/>
        <result column="brand_id" property="brandId"/>
        <result column="brand_name" property="brandName"/>
        <result column="send_user_id" property="sendUserId"/>
        <result column="to_user_id" property="toUserId"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="warehouse_name" property="warehouseName"/>

    </resultMap>
    <select id="getByPage" resultType="com.kxs.store.api.vo.kxsapp.store.StoreAdvanceMachineVO">
        select a.id, a.create_time, info.is_use_amount, info.pos_sn, info.is_advance from kxs_machine_advance a
        left join kxs_machine_advance_info info on info.order_id = a.id
        <where>
            and a.del_flag = 0
            and info.is_return = 0
            and info.is_apply = 0
            and a.warehouse_id = #{warehouseId}
            and a.brand_id = #{brandId}
        </where>

    </select>
    <select id="getUserWareHouseList" resultType="com.kxs.store.api.vo.kxsapp.storeAdvance.UserWarehouseListVO">
        select user_id, avatar, user_code, username, phone
        from (select a.to_user_id as user_id, u.avatar, u.user_code, u.username, u.phone
        from kxs_machine_advance a
        left join kxs_user.kxs_user u on a.to_user_id = u.id
        left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0 and a.send_user_id = #{userId}
        <if test="keyword != null and keyword != ''">
            and (u.user_code = #{keyword} or b.pos_sn like concat('%',#{keyword},'%'))
        </if>
        and b.is_apply = 0
        and b.is_return = 0
        group by to_user_id) as warehouse_user


    </select>
    <select id="selectUserByCount" resultType="java.lang.Long">
        select count(b.id)
        from kxs_machine_advance a
                 left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
          and b.is_apply = 0
          and b.is_return = 0
          and a.to_user_id = #{userId}
    </select>
    <select id="selectToUserList" resultType="java.util.Map">
        select a.id,
        a.brand_id as brandId,
        a.brand_name as brandName,
        b.id as infoId,
        b.pos_sn as posSn,
        b.is_use_amount as isUseAmount,
        b.create_time as createTime,
        b.is_confirm as isConfirm,
        b.is_advance as isAdvance
        from kxs_machine_advance a
        left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
        <if test="keyword != null and keyword != ''">
            and b.pos_sn like concat('%',#{keyword},'%')
        </if>
        and a.send_user_id = #{sendUserId}
        and a.to_user_id = #{toUserId}
        and b.is_apply = 0
        and b.is_return = 0
    </select>

    <select id="selectToUserNeedConfirmList" resultType="com.kxs.store.api.vo.kxsapp.userAdvance.AdvanceDevModalVO">

        select a.id,
               a.brand_name,
               a.brand_id,
               a.warehouse_id,
               a.warehouse_name,
               b.pos_sn
        from kxs_machine_advance a
                 left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
          and a.to_user_id = #{toUserId}
          and b.is_apply = 0
          and b.is_return = 0
          and b.is_confirm = 0
    </select>

    <select id="advanceUserList" resultType="com.kxs.store.api.vo.kxsapp.userAdvance.UserAdvanceListVO">
        select a.id,
               a.brand_name,
               a.brand_id,
               b.pos_sn,
               b.create_time,
               b.is_use_amount,
               b.is_advance,
               b.machine_id,
               a.warehouse_name
        from kxs_machine_advance a
        left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
          and a.to_user_id = #{userId}
          and b.is_apply = 0
          and b.is_return = 0
          and b.is_confirm = 1
    </select>

    <select id="selectWarehouseUserCount" resultType="java.lang.Long">
        select count(a.id)
        from kxs_machine_advance a
                 left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
          and a.warehouse_id = #{warehouseId}
          and b.is_apply = 0
          and b.is_return = 0
          and b.is_confirm = 1
    </select>
    <select id="selectMaxDayPreTimeout" resultType="java.lang.Integer">
        select IFNULL(MAX(DATEDIFF(NOW(), a.create_time)), 0) AS day
        from kxs_machine_advance a
                 left join kxs_machine_advance_info b on a.id = b.order_id
        where a.del_flag = 0
          and a.to_user_id = #{userId}
          and b.is_apply = 0
          and b.is_return = 0
          and b.is_confirm = 1
          and b.is_advance = 0
    </select>

</mapper>
