<?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="xyz.zhouxy.plusone.commons.model.dto.test.AccountQueries">

    <resultMap id="accountVO" type="xyz.zhouxy.plusone.commons.model.dto.test.AccountVO">
        <id column="id" property="id" javaType="java.lang.Long" />
        <result column="username" property="username" />
        <result column="email" property="email" />
        <result column="status" property="status" />
        <result column="created_by" property="createdBy" />
        <result column="create_time" property="createTime" />
        <result column="version" property="version" />
    </resultMap>

    <select id="queryAccountList" resultMap="accountVO">
        SELECT id, username, email, status, created_by, create_time, version
        FROM sys_account a
        <where>
            <if test="query.id != null">
                AND a.id = #{query.id}
            </if>
            <if test="query.username != null">
                AND a.username = #{query.username}
            </if>
            <if test="query.email != null">
                AND a.email = #{query.email}
            </if>
            <if test="query.status != null">
                AND a.status = #{query.status}
            </if>
            <if test="query.createdBy != null">
                AND a.created_by = #{query.createdBy}
            </if>
            <if test="query.createTimeStart != null">
                AND a.create_time &gt;= #{query.createTimeStart}
            </if>
            <if test="query.createTimeEnd != null">
                AND a.create_time &lt; #{query.createTimeEnd}
            </if>
        </where>
        ORDER BY
        <foreach collection="page.orderBy" index="i" item="property">
            ${property.sqlSnippet},
        </foreach>
        id ASC
        LIMIT #{page.size} OFFSET #{page.offset}
    </select>

    <select id="countAccount">
        SELECT count(*)
        FROM sys_account a
        <where>
            <if test="query.id != null">
                AND a.id = #{query.id}
            </if>
            <if test="query.username != null">
                AND a.username = #{query.username}
            </if>
            <if test="query.email != null">
                AND a.email = #{query.email}
            </if>
            <if test="query.status != null">
                AND a.status = #{query.status}
            </if>
            <if test="query.createdBy != null">
                AND a.created_by = #{query.createdBy}
            </if>
            <if test="query.createTimeStart != null">
                AND a.create_time &gt;= #{query.createTimeStart}
            </if>
            <if test="query.createTimeEnd != null">
                AND a.create_time &lt; #{query.createTimeEnd}
            </if>
        </where>
    </select>

</mapper>
