<?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.wbsite.module.admin.mpr.RulesMapper">

    <sql id="table">"NA_RULES"</sql>
	
    <sql id="entityColumnList">
        "ID","RULE_FOR","RULE_TYPE","RULE_TITLE","RULE_NAME","RULE_VALUE","VALUE_DEFAULT","VALUE_REGEX","VALUE_MAX","RULE_NOTE","ROW_VERSION","IS_DELETED","CREATE_BY","CREATE_TIME","LAST_UPDATE_BY","LAST_UPDATE_TIME"
    </sql>
	
    <resultMap id="rules" type="xyz.wbsite.module.admin.ent.Rules">
        <result column="ID"  jdbcType="BIGINT" property="id"/>
        <result column="RULE_FOR"  jdbcType="VARCHAR" property="ruleFor"/>
        <result column="RULE_TYPE"  jdbcType="VARCHAR" property="ruleType"/>
        <result column="RULE_TITLE"  jdbcType="VARCHAR" property="ruleTitle"/>
        <result column="RULE_NAME"  jdbcType="VARCHAR" property="ruleName"/>
        <result column="RULE_VALUE"  jdbcType="VARCHAR" property="ruleValue"/>
        <result column="VALUE_DEFAULT"  jdbcType="VARCHAR" property="valueDefault"/>
        <result column="VALUE_REGEX"  jdbcType="VARCHAR" property="valueRegex"/>
        <result column="VALUE_MAX"  jdbcType="INTEGER" property="valueMax"/>
        <result column="RULE_NOTE"  jdbcType="VARCHAR" property="ruleNote"/>
        <result column="ROW_VERSION"  jdbcType="BIGINT" property="rowVersion"/>
        <result column="IS_DELETED"  jdbcType="BIT" property="isDeleted"/>
        <result column="CREATE_BY"  jdbcType="BIGINT" property="createBy"/>
        <result column="CREATE_TIME"  jdbcType="TIMESTAMP" property="createTime"/>
        <result column="LAST_UPDATE_BY"  jdbcType="BIGINT" property="lastUpdateBy"/>
        <result column="LAST_UPDATE_TIME"  jdbcType="TIMESTAMP" property="lastUpdateTime"/>
    </resultMap>
	
    <insert id="insert">
        INSERT INTO
        <include refid="table"/>
        (
        <include refid="entityColumnList"/>
        )
        VALUES
        (
        #{request.id},
        #{request.ruleFor,jdbcType=VARCHAR},
        #{request.ruleType,jdbcType=VARCHAR},
        #{request.ruleTitle,jdbcType=VARCHAR},
        #{request.ruleName,jdbcType=VARCHAR},
        #{request.ruleValue,jdbcType=VARCHAR},
        #{request.valueDefault,jdbcType=VARCHAR},
        #{request.valueRegex,jdbcType=VARCHAR},
        #{request.valueMax,jdbcType=INTEGER},
        #{request.ruleNote,jdbcType=VARCHAR},
        0,
        0,
        #{token.userId,jdbcType=NUMERIC},
        datetime('now','localtime'),
        NULL,
        NULL
        )
    </insert>

    <insert id="insertBatch">
        INSERT INTO
        <include refid="table"/>
        (
        <include refid="entityColumnList"/>
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id},
        #{item.ruleFor,jdbcType=VARCHAR},
        #{item.ruleType,jdbcType=VARCHAR},
        #{item.ruleTitle,jdbcType=VARCHAR},
        #{item.ruleName,jdbcType=VARCHAR},
        #{item.ruleValue,jdbcType=VARCHAR},
        #{item.valueDefault,jdbcType=VARCHAR},
        #{item.valueRegex,jdbcType=VARCHAR},
        #{item.valueMax,jdbcType=INTEGER},
        #{item.ruleNote,jdbcType=VARCHAR},
        0,
        0,
        #{token.userId,jdbcType=NUMERIC},
        datetime('now','localtime'),
        NULL,
        NULL
        </foreach >
    </insert>
	
    <update id="delete">
        UPDATE
        <include refid="table"/>
        SET "IS_DELETED" = 1
        WHERE "IS_DELETED" = 0
        AND "ID" = #{id}
    </update>
	
    <update id="deleteBatch">
        UPDATE
        <include refid="table"/>
        SET "IS_DELETED" = 1
        WHERE "IS_DELETED" = 0
        AND "ID" IN
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
		    #{item}
	    </foreach>
    </update>

    <update id="update">
        UPDATE
        <include refid="table"/>
        SET
        RULE_FOR = #{request.ruleFor,jdbcType=VARCHAR},
        RULE_TYPE = #{request.ruleType,jdbcType=VARCHAR},
        RULE_TITLE = #{request.ruleTitle,jdbcType=VARCHAR},
        RULE_NAME = #{request.ruleName,jdbcType=VARCHAR},
        RULE_VALUE = #{request.ruleValue,jdbcType=VARCHAR},
        VALUE_DEFAULT = #{request.valueDefault,jdbcType=VARCHAR},
        VALUE_REGEX = #{request.valueRegex,jdbcType=VARCHAR},
        VALUE_MAX = #{request.valueMax,jdbcType=INTEGER},
        RULE_NOTE = #{request.ruleNote,jdbcType=VARCHAR},
        "ROW_VERSION" = "ROW_VERSION" + 1,
        "LAST_UPDATE_BY" = #{token.userId},
        "LAST_UPDATE_TIME" = datetime('now','localtime')
        WHERE "IS_DELETED" = 0
        AND "ID" = #{request.id}
        AND "ROW_VERSION" = #{request.rowVersion}
    </update>

    <select id="select" resultMap="rules">
        SELECT
        <include refid="entityColumnList"/>
        FROM
        <include refid="table"/>
        WHERE "IS_DELETED" = 0
        <if test="request.ruleFor != null and request.ruleFor != ''">
            AND "RULE_FOR" = #{request.ruleFor}
        </if>
        <if test="request.ruleType != null and request.ruleType != ''">
            AND "RULE_TYPE" = #{request.ruleType}
        </if>
        <if test="request.ruleTitle != null and request.ruleTitle != ''">
            AND "RULE_TITLE" = #{request.ruleTitle}
        </if>
        <if test="request.ruleName != null and request.ruleName != ''">
            AND "RULE_NAME" = #{request.ruleName}
        </if>
        <if test="request.ruleValue != null and request.ruleValue != ''">
            AND "RULE_VALUE" = #{request.ruleValue}
        </if>
        <if test="request.valueDefault != null and request.valueDefault != ''">
            AND "VALUE_DEFAULT" = #{request.valueDefault}
        </if>
        <if test="request.valueRegex != null and request.valueRegex != ''">
            AND "VALUE_REGEX" = #{request.valueRegex}
        </if>
    </select>

    <select id="find" resultMap="rules">
        SELECT
        <include refid="entityColumnList"/>
        FROM
        <include refid="table"/>
        WHERE "IS_DELETED" = 0
        <if test="request.ruleFor != null and request.ruleFor != ''">
            AND "RULE_FOR" = #{request.ruleFor}
        </if>
        <if test="request.ruleType != null and request.ruleType != ''">
            AND "RULE_TYPE" = #{request.ruleType}
        </if>
        <if test="request.ruleTitle != null and request.ruleTitle != ''">
            AND "RULE_TITLE" = #{request.ruleTitle}
        </if>
        <if test="request.ruleTitleLike != null and request.ruleTitleLike != ''">
            AND "RULE_TITLE" LIKE '%'||#{request.ruleTitleLike}||'%'
        </if>
        <if test="request.ruleName != null and request.ruleName != ''">
            AND "RULE_NAME" = #{request.ruleName}
        </if>
        <if test="request.ruleNameLike != null and request.ruleNameLike != ''">
            AND "RULE_NAME" LIKE '%'||#{request.ruleNameLike}||'%'
        </if>
        <if test="request.ruleValue != null and request.ruleValue != ''">
            AND "RULE_VALUE" = #{request.ruleValue}
        </if>
        <if test="request.ruleValueLike != null and request.ruleValueLike != ''">
            AND "RULE_VALUE" LIKE '%'||#{request.ruleValueLike}||'%'
        </if>
        <if test="request.valueDefault != null and request.valueDefault != ''">
            AND "VALUE_DEFAULT" = #{request.valueDefault}
        </if>
        <if test="request.valueRegex != null and request.valueRegex != ''">
            AND "VALUE_REGEX" = #{request.valueRegex}
        </if>
        <if test="request.ruleNoteLike != null and request.ruleNoteLike != ''">
            AND "RULE_NOTE" LIKE '%'||#{request.ruleNoteLike}||'%'
        </if>
        <if test="request.startDate != null">
            AND strftime('%s',"CREATE_TIME", 'utc')*1000 &gt;= #{request.startDate}
        </if>
        <if test="request.endDate != null">
            AND strftime('%s',"CREATE_TIME", 'utc')*1000 &lt;= #{request.endDate}
        </if>
    </select>

    <select id="search" resultMap="rules">
        SELECT
        <include refid="entityColumnList"/>
        FROM
        <include refid="table"/>
        WHERE "IS_DELETED" = 0
    </select>

    <select id="getById" resultMap="rules">
        SELECT
        <include refid="entityColumnList"/>
        FROM
        <include refid="table"/>
        WHERE "IS_DELETED" = 0
        AND "ID" = #{id}
    </select>
	
    <select id="getByIds" resultMap="rules">
        SELECT
        <include refid="entityColumnList"/>
        FROM
        <include refid="table"/>
        WHERE "IS_DELETED" = 0
        AND "ID" IN
        <foreach collection="ids" item="item" index="index" separator="," open="(" close=")">
		    #{item}
	    </foreach>
    </select>
</mapper>