<?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.product.biz.mapper.KxsMachineMapper">

    <resultMap type="com.kxs.product.api.model.KxsMachine" id="KxsMachineMap">
        <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="pos_sn" property="posSn"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="warehouse_name" property="warehouseName"/>
        <result column="user_id" property="userId"/>
        <result column="merchant_id" property="merchantId"/>
        <result column="brand_id" property="brandId"/>
        <result column="recycle_status" property="recycleStatus"/>
        <result column="recycle_end_time" property="recycleEndTime"/>
        <result column="machine_type" property="machineType"/>
        <result column="cash_pledge" property="cashPledge"/>
        <result column="source_pledge" property="sourcePledge"/>
        <result column="allot_path" property="allotPath"/>
        <result column="bind_time" property="bindTime"/>
        <result column="act_time" property="actTime"/>
        <result column="delivery_time" property="deliveryTime"/>
        <result column="box_sn" property="boxSn"/>
        <result column="machine_ratio" property="machineRatio"/>
        <result column="is_act" property="isAct"/>
        <result column="bind_status" property="bindStatus"/>
        <result column="status" property="status"/>
        <result column="is_first" property="isFirst"/>
        <result column="up_fee_flag" property="upFeeFlag"/>
        <result column="down_fee_flag" property="downFeeFlag"/>
        <result column="is_supply" property="isSupply"/>
        <result column="is_op_act" property="isOpAct"/>
        <result column="return_status" property="returnStatus"/>
        <result column="pre_user_id" property="preUserId"/>
        <result column="batch_no" property="batchNo"/>
        <result column="credit_trade" property="creditTrade"/>
        <result column="debit_card_trade" property="debitCardTrade"/>
        <result column="scan_qr_trade" property="scanQrTrade"/>
        <result column="source_pos_sn" property="sourcePosSn"/>
    </resultMap>
    <select id="machineStoreList" resultType="com.kxs.product.api.vo.kxsapp.machine.MachineStoreListVO">
        select create_time, pos_sn from kxs_machine
        <where>
            and del_flag = 0
            and status = 0
            <if test="query.warehouseId != null">
                and warehouse_id = #{query.warehouseId}
            </if>
            <if test="query.brandId != null">
                and brand_id = #{query.brandId}
            </if>
            <if test="query.boxSn != null and query.boxSn != ''">
                and box_sn = #{query.boxSn}
            </if>
            <if test="query.posSn != null and query.posSn != ''">
                and pos_sn like concat('%', #{query.posSn}, '%')
            </if>
        </where>

    </select>
    <select id="recycleMachinesPage" resultType="com.kxs.product.api.vo.kxsapp.machine.RecycleMachinesVO">
        select id,create_time, pos_sn, machine_type, recycle_end_time from kxs_machine
        <where>
            and del_flag = 0
            and recycle_status = 1
            and user_id = #{userId}
            AND brand_id IN
            <foreach collection="brandIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </where>

    </select>
    <select id="machineUnBindListPage" resultType="com.kxs.product.api.vo.kxsapp.machine.MachineUnBindListVO">

        select id,cash_pledge, pos_sn, machine_type, recycle_end_time from kxs_machine
        <where>
            and del_flag = 0
            and bind_status = 0
            and user_id = #{query.userId}
            <if test="query.brandId != null">
                and brand_id = #{query.brandId}
            </if>
            <if test="query.keyword != null and query.keyword != ''">
                and pos_sn like concat('%', #{query.keyword}, '%')
            </if>
        </where>

    </select>
    <select id="machineBindList" resultType="com.kxs.product.api.vo.kxsapp.machine.MachineBindListVO">
        select is_act, pos_sn, machine_type, cash_pledge from kxs_machine
        <where>
            and del_flag = 0
            and bind_status = 1
            and user_id = #{query.userId}
            <if test="query.brandId != null">
                and brand_id = #{query.brandId}
            </if>
            <if test="query.keyword != null and query.keyword != ''">
                and pos_sn like concat('%', #{query.keyword}, '%')
            </if>
        </where>
    </select>
    <select id="getByPage" resultType="com.kxs.product.api.vo.kxsapp.machine.MachineUserListVO">
        select a.is_act, a.pos_sn, a.machine_type, a.cash_pledge, a.delivery_time, a.warehouse_name,
        b.name as brand
        from kxs_machine a
        left join kxs_brand b on a.brand_id = b.id
        <where>
            and a.del_flag = 0
            and a.user_id = #{query.userId}
            <if test="query.brandId != null">
                and a.brand_id = #{query.brandId}
            </if>
            <if test="query.keyword != null and query.keyword != ''">
                and a.pos_sn like concat('%', #{query.keyword}, '%')
            </if>
        </where>
    </select>
    <select id="searchUserMachine" resultType="com.kxs.product.api.vo.kxsapp.machine.SearchUserMachineVO">
        select create_time, pos_sn from kxs_machine
        <where>
            <if test="query.userId != null">
                and user_id = #{query.userId}
            </if>
            <if test="query.boxSn != null and query.boxSn != ''">
                and box_sn = #{query.boxSn}
            </if>
            <if test="query.posSn != null and query.posSn != ''">
                and pos_sn like concat('%', #{query.posSn}, '%')
            </if>
        </where>
    </select>

    <select id="recyclePageList" resultType="com.kxs.product.api.vo.kxsapp.machine.MachineRecycleListVO">
        select recycle_end_time, pos_sn, brand_id from kxs_machine
        where del_flag = 0
          and status = 3
          and user_id = #{query.userId}
        <if test="query.posSn != null and query.posSn != ''">
            and pos_sn like concat('%', #{query.posSn}, '%')
        </if>
        <if test="query.brandId != null and query.brandId != ''">
            and brand_id = #{query.brandId}
        </if>
        order by recycle_end_time desc
    </select>

    <select id="activateMachineMonthTotal"
            resultType="com.kxs.product.api.vo.kxsapp.machine.StoreActMachineMonthTotalVO">
        select * from
        (
        select count(id) as totalNumber, DATE_FORMAT(act_time,'%Y-%m') AS `month`
        from kxs_machine a
        <where>
            and a.brand_id = #{query.brandId}
            and a.warehouse_id = #{query.warehouseId}
            and a.is_act = 1
        </where>
        GROUP BY `month`
        ) as tmp
        ORDER BY `month` desc
    </select>
    <select id="activateMachineMonthTotalDetail"
            resultType="com.kxs.product.api.vo.kxsapp.machine.StoreActMachineDayDetailVO">
        SELECT *
        FROM (
        SELECT count(id) as number, DATE_FORMAT(act_time,'%Y-%m-%d') AS `date`
        FROM kxs_machine a
        <where>
            and a.brand_id = #{query.brandId}
            and a.warehouse_id = #{query.warehouseId}
            and a.is_act = 1
            <if test="query.startDate != null and query.endDate != null">
                and a.act_time between #{query.startDate} and #{query.endDate}
            </if>
        </where>
        GROUP BY `date`
        ) AS tmp
        ORDER BY `date` desc
    </select>
    <select id="selectDeductList" resultMap="KxsMachineMap">
        select a.id, a.user_id, a.pos_sn, a.brand_id, a.recycle_end_time
        from kxs_machine a
                 left join kxs_machine_deduct b on a.pos_sn = b.pos_sn
        where
          b.pos_sn is null
          and a.del_flag = 0
          and a.user_id = #{query.userId}
          and a.status = #{query.status}
          and a.bind_status = #{query.bindStatus}
          and a.recycle_end_time &lt; #{query.recycleEndTime}
    </select>
    <select id="selectExpireDeductList" resultMap="KxsMachineMap">
        select a.id, a.pos_sn, recycle_end_time
        from kxs_machine a
                 left join kxs_machine_deduct b on a.pos_sn = b.pos_sn
        where
          b.pos_sn is null
          and a.del_flag = 0
          and a.status = 1
          and a.user_id > 0
          and a.bind_status = 0
          and a.recycle_end_time &lt; #{endDay}
          order by recycle_end_time desc
          limit 1000
    </select>
    <select id="recycleBrandList" resultType="com.kxs.system.api.vo.kxsapp.RecycleBrandListVO">
        select DISTINCT kb.id,kb.name from kxs_machine km
        left join  kxs_brand kb on km.brand_id = kb.id
        where km.del_flag = 0
        and km.status = 3
        and km.user_id = #{query.userId}
    </select>


</mapper>
