<?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.KxsMachineApplyMapper">

    <resultMap type="com.kxs.store.api.model.KxsMachineApply" id="KxsMachineApplyMap">
        <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="order_sn" property="orderSn" />
        <result column="user_id" property="userId" />
        <result column="brand_id" property="brandId" />
        <result column="brand_name" property="brandName" />
        <result column="warehouse_id" property="warehouseId" />
        <result column="get_mode" property="getMode" />
        <result column="apply_num" property="applyNum" />
        <result column="contacts" property="contacts" />
        <result column="phone" property="phone" />
        <result column="address" property="address" />
        <result column="apply_type" property="applyType" />
        <result column="status" property="status" />
        <result column="top_user_id" property="topUserId" />
        <result column="is_advance" property="isAdvance" />
        <result column="remark" property="remark" />
        <result column="username" property="username" />
        <result column="user_code" property="userCode" />
        <result column="partner_id" property="partnerId" />

    </resultMap>
    <select id="getByPage" resultType="com.kxs.store.api.vo.kxsapp.storeOrder.StoreOrderListVO">
        select a.id, a.create_time, a.order_sn, a.get_mode, a.apply_num, a.contacts, a.phone, a.address, a.apply_type, a.status, a.username, a.user_code from kxs_machine_apply a
        <where>
            <if test="query.brandId != null">
                and a.brand_id = #{query.brandId}
            </if>
            <if test="query.warehouseId != null">
                and a.warehouse_id = #{query.warehouseId}
            </if>
            <if test="query.status != null">
                and a.status = #{query.status}
            </if>
            <if test="query.keyword != null and query.keyword != ''">
                and (a.contacts like concat('%', #{query.keyword}, '%') or a.phone like concat('%', #{query.keyword}, '%'))
            </if>
        </where>
    </select>
    <select id="orderMonthTotal" resultType="com.kxs.store.api.vo.kxsapp.storeOrder.OrderMonthTotalVO">
        select * from
        (
        select sum(apply_num) as totalNumber, DATE_FORMAT(create_time,'%Y-%m') AS `month`
        from kxs_machine_apply a
        <where>
            and a.brand_id = #{query.brandId}
            and a.warehouse_id = #{query.warehouseId}
            and a.status = 1
        </where>
        GROUP BY `month`
        ) as tmp
        ORDER BY `month` desc
    </select>
    <select id="orderMonthTotalDetail" resultType="com.kxs.store.api.vo.kxsapp.storeOrder.OrderMonthTotalDetailVO">
        SELECT *
        FROM (
        SELECT sum(apply_num) as number, DATE_FORMAT(create_time,'%Y-%m-%d') AS `date`
        FROM kxs_machine_apply a
        <where>
            and a.brand_id = #{query.brandId}
            and a.warehouse_id = #{query.warehouseId}
            and a.status = 1
            <if test="query.startDate != null and query.endDate != null">
                and a.create_time between #{query.startDate} and #{query.endDate}
            </if>
        </where>
        GROUP BY `date`
        ) AS tmp
        ORDER BY `date` desc
    </select>
    <select id="userApplyMachineList" resultType="com.kxs.store.api.vo.kxsapp.userOrder.UserApplyMahineListVO">
        select a.id, a.create_time, a.order_sn, a.apply_num, a.address, a.status from kxs_machine_apply a
        <where>
            and user_id = #{userId}
        </where>
        order by a.create_time desc
    </select>

</mapper>
