<?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.yunfeiyun.agmp.generator.mapper.GenTableMapper">

	<resultMap type="GenTable" id="GenTableResult">
	    <id     property="tblId"        column="tblId"          />
		<result property="tblName"      column="tblName"        />
		<result property="tblComment"   column="tblComment"     />
		<result property="tblSubtablename"   column="tblSubtablename"    />
		<result property="tblSubtablefkname" column="tblSubtablefkname" />
		<result property="tblClassname"      column="tblClassname"        />
		<result property="tblTplcategory"    column="tblTplcategory"      />
		<result property="tblPackagename"    column="tblPackagename"      />
		<result property="tblModulename"     column="tblModulename"       />
		<result property="tblBusinessname"   column="tblBusinessname"     />
		<result property="tblFunctionname"   column="tblFunctionname"     />
		<result property="tblFunctionauthor" column="tblFunctionauthor"   />
		<result property="tblGentype"        column="tblGentype"          />
		<result property="tblGenpath"        column="tblGenpath"          />
		<result property="tblOptions"        column="tblOptions"           />
		<result property="tblCreator"       column="tblCreator"         />
		<result property="tblCreateddate"     column="tblCreateddate"       />
		<result property="tblModifier"     column="tblModifier"         />
		<result property="tblModifieddate"     column="tblModifieddate"       />
		<result property="tblRemark"         column="tblRemark"            />
		<collection  property="columns"  javaType="java.util.List"  resultMap="GenTableColumnResult" />
	</resultMap>
	
	<resultMap type="GenTableColumn" id="GenTableColumnResult">
        <id     property="cmnId"       column="cmnId"      />
        <result property="tblId"        column="tblId"       />
        <result property="cmnName"     column="cmnName"    />
        <result property="cmnComment"  column="cmnComment" />
        <result property="cmnType"     column="cmnType"    />
        <result property="cmnJavatype"       column="cmnJavatype"      />
        <result property="cmnJavafield"      column="cmnJavafield"     />
        <result property="cmnIspk"           column="cmnIspk"          />
        <result property="cmnIsincrement"    column="cmnIsincrement"   />
        <result property="cmnIsrequired"     column="cmnIsrequired"    />
        <result property="cmnIsinsert"       column="cmnIsinsert"      />
        <result property="cmnIsedit"         column="cmnIsedit"        />
        <result property="cmnIslist"         column="cmnIslist"        />
        <result property="cmnIsquery"        column="cmnIsquery"       />
        <result property="cmnQuerytype"      column="cmnQuerytype"     />
        <result property="cmnHtmltype"       column="cmnHtmltype"      />
        <result property="cmnDicttype"       column="cmnDicttype"      />
        <result property="cmnSeq"           column="cmnSeq"           />
        <result property="cmnCreator"       column="cmnCreator"      />
        <result property="cmnCreateddate"     column="cmnCreateddate"    />
        <result property="cmnModifier"       column="cmnModifier"      />
        <result property="cmnModifieddate"     column="cmnModifieddate"    />
    </resultMap>
	
	<sql id="selectGenTableVo">
        select tblId, tblName, tblComment, tblSubtablename, tblSubtablefkname, tblClassname,
         tblTplcategory, tblPackagename, tblModulename, tblBusinessname, tblFunctionname, tblFunctionauthor,
         tblGentype, tblGenpath, tblOptions, tblCreator, tblCreateddate, tblModifier, tblModifieddate, tblRemark from ToolGenTable
    </sql>
    
    <select id="selectGenTableList" parameterType="GenTable" resultMap="GenTableResult">
		<include refid="selectGenTableVo"/>
		<where>
			<if test="tblName != null and tblName != ''">
				AND lower(tblName) like lower(concat('%', #{tblName}, '%'))
			</if>
			<if test="tblComment != null and tblComment != ''">
				AND lower(tblComment) like lower(concat('%', #{tblComment}, '%'))
			</if>
			<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
				AND date_format(tblCreateddate,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
			</if>
			<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
				AND date_format(tblCreateddate,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
			</if>
		</where>
	</select>

	<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
		select table_name as tblName, table_comment as tblComment, create_time as tblCreateddate, update_time as tblModifieddate from information_schema.tables
		where table_schema = (select database())
		AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE '%ToolGen_%'
		AND table_name NOT IN (select table_name from ToolGenTable)
		<if test="tableName != null and tableName != ''">
			AND lower(table_name) like lower(concat('%', #{tableName}, '%'))
		</if>
		<if test="tableComment != null and tableComment != ''">
			AND lower(table_comment) like lower(concat('%', #{tableComment}, '%'))
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
		</if>
		<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
			AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
        order by create_time desc
	</select>
	
	<select id="selectDbTableListByNames" resultMap="GenTableResult">
		select table_name as tblName, table_comment as tblComment, create_time as tblCreateddate, update_time as tblModifieddate from information_schema.tables
		where table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE '%ToolGen_%' and table_schema = (select database())
		and table_name in
	    <foreach collection="array" item="name" open="(" separator="," close=")">
 			#{name}
        </foreach> 
	</select>
	
	<select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
		select table_name, table_comment, create_time, update_time from information_schema.tables
		where table_comment <![CDATA[ <> ]]> '' and table_schema = (select database())
		and table_name = #{tableName}
	</select>
	
	<select id="selectGenTableById" parameterType="String" resultMap="GenTableResult">
	    SELECT t.tblId, t.tblName, t.tblComment, t.tblSubtablename, t.tblSubtablefkname, t.tblClassname,
	     t.tblTplcategory, t.tblPackagename, t.tblModulename, t.tblBusinessname, t.tblFunctionname,
	      t.tblFunctionauthor,
	     t.tblGentype, t.tblGenpath, t.tblOptions, t.tblRemark,
			   c.cmnId, c.cmnName, c.cmnComment, c.cmnType, c.cmnJavatype, c.cmnJavafield, c.cmnIspk, c.cmnIsincrement,
			   c.cmnIsrequired, c.cmnIsinsert, c.cmnIsedit, c.cmnIslist, c.cmnIsquery, c.cmnQuerytype,
			   c.cmnHtmltype, c.cmnDicttype, c.cmnSeq
		FROM ToolGenTable t
			 LEFT JOIN ToolsGenTablecolumn c ON t.tblId = c.tblId
		where t.tblId = #{tableId} order by c.cmnSeq
	</select>
	
	<select id="selectGenTableByName" parameterType="String" resultMap="GenTableResult">
	    SELECT t.tblId, t.tblName, t.tblComment, t.tblSubtablename, t.tblSubtablefkname, t.tblClassname,
	    t.tblTplcategory, t.tblPackagename, t.tblModulename, t.tblBusinessname, t.tblFunctionname, t.tblFunctionauthor,
	    t.tblGentype, t.tblGenpath, t.tblOptions, t.tblRemark,
			   c.cmnId, c.cmnName, c.cmnComment, c.cmnType, c.cmnJavatype, c.cmnJavafield, c.cmnIspk, c.cmnIsincrement,
			    c.cmnIsrequired, c.cmnIsinsert, c.cmnIsedit, c.cmnIslist, c.cmnIsquery, c.cmnQuerytype,
			     c.cmnHtmltype, c.cmnDicttype, c.cmnSeq
		FROM ToolGenTable t
			 LEFT JOIN ToolsGenTablecolumn c ON t.tblId = c.tblId
		where t.tblName = #{tableName} order by c.cmnSeq
	</select>
	
	<select id="selectGenTableAll" parameterType="String" resultMap="GenTableResult">
	    SELECT t.tblId, t.tblName, t.tblComment, t.tblSubtablename, t.tblSubtablefkname, t.tblClassname,
	     t.tblTplcategory, t.tblPackagename, t.tblModulename, t.tblBusinessname, t.tblFunctionname, t.tblFunctionauthor,
	      t.tblOptions, t.tblRemark,
			   c.cmnId, c.cmnName, c.cmnComment, c.cmnType, c.cmnJavatype, c.cmnJavafield, c.cmnIspk, c.cmnIsincrement,
			   c.cmnIsrequired, c.cmnIsinsert, c.cmnIsedit, c.cmnIslist, c.cmnIsquery, c.cmnQuerytype,
			   c.cmnHtmltype, c.cmnDicttype, c.cmnSeq
		FROM ToolGenTable t
			 LEFT JOIN ToolsGenTablecolumn c ON t.tblId = c.tblId
		order by c.cmnSeq
	</select>
	
	<insert id="insertGenTable" parameterType="GenTable" useGeneratedKeys="true" keyProperty="tblId">
        insert into ToolGenTable (
			<if test="tblId != null">tblId,</if>
			<if test="tblName != null">tblName,</if>
			<if test="tblComment != null and tblComment != ''">tblComment,</if>
			<if test="tblClassname != null and tblClassname != ''">tblClassname,</if>
			<if test="tblTplcategory != null and tblTplcategory != ''">tblTplcategory,</if>
			<if test="tblPackagename != null and tblPackagename != ''">tblPackagename,</if>
			<if test="tblModulename != null and tblModulename != ''">tblModulename,</if>
			<if test="tblBusinessname != null and tblBusinessname != ''">tblBusinessname,</if>
			<if test="tblFunctionname != null and tblFunctionname != ''">tblFunctionname,</if>
			<if test="tblFunctionauthor != null and tblFunctionauthor != ''">tblFunctionauthor,</if>
			<if test="tblGentype != null and tblGentype != ''">tblGentype,</if>
			<if test="tblGenpath != null and tblGenpath != ''">tblGenpath,</if>
			<if test="tblRemark != null and tblRemark != ''">tblRemark,</if>
 			<if test="tblCreator != null and tblCreator != ''">tblCreator,</if>
		tblCreateddate
         )values(
			<if test="tblId != null">#{tblId},</if>
			<if test="tblName != null">#{tblName},</if>
			<if test="tblComment != null and tblComment != ''">#{tblComment},</if>
			<if test="tblClassname != null and tblClassname != ''">#{tblClassname},</if>
			<if test="tblTplcategory != null and tblTplcategory != ''">#{tblTplcategory},</if>
			<if test="tblPackagename != null and tblPackagename != ''">#{tblPackagename},</if>
			<if test="tblModulename != null and tblModulename != ''">#{tblModulename},</if>
			<if test="tblBusinessname != null and tblBusinessname != ''">#{tblBusinessname},</if>
			<if test="tblFunctionname != null and tblFunctionname != ''">#{tblFunctionname},</if>
			<if test="tblFunctionauthor != null and tblFunctionauthor != ''">#{tblFunctionauthor},</if>
			<if test="tblGentype != null and tblGentype != ''">#{tblGentype},</if>
			<if test="tblGenpath != null and tblGenpath != ''">#{tblGenpath},</if>
			<if test="tblRemark != null and tblRemark != ''">#{tblRemark},</if>
 			<if test="tblCreator != null and tblCreator != ''">#{tblCreator},</if>
		date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
         )
    </insert>
    
    <update id="updateGenTable" parameterType="GenTable">
        update ToolGenTable
        <set>
            <if test="tblName != null">tblName = #{tblName},</if>
            <if test="tblComment != null and tblComment != ''">tblComment = #{tblComment},</if>
            <if test="tblSubtablename != null">tblSubtablename = #{tblSubtablename},</if>
            <if test="tblSubtablefkname != null">tblSubtablefkname = #{tblSubtablefkname},</if>
            <if test="tblClassname != null and tblClassname != ''">tblClassname = #{tblClassname},</if>
            <if test="tblFunctionauthor != null and tblFunctionauthor != ''">tblFunctionauthor = #{tblFunctionauthor},</if>
            <if test="tblGentype != null and tblGentype != ''">tblGentype = #{tblGentype},</if>
            <if test="tblGenpath != null and tblGenpath != ''">tblGenpath = #{tblGenpath},</if>
            <if test="tblTplcategory != null and tblTplcategory != ''">tblTplcategory = #{tblTplcategory},</if>
            <if test="tblPackagename != null and tblPackagename != ''">tblPackagename = #{tblPackagename},</if>
            <if test="tblModulename != null and tblModulename != ''">tblModulename = #{tblModulename},</if>
            <if test="tblBusinessname != null and tblBusinessname != ''">tblBusinessname = #{tblBusinessname},</if>
            <if test="tblFunctionname != null and tblFunctionname != ''">tblFunctionname = #{tblFunctionname},</if>
            <if test="tblOptions != null and tblOptions != ''">tblOptions = #{tblOptions},</if>
            <if test="tblModifier != null and tblModifier != ''">tblModifier = #{tblModifier},</if>
            <if test="tblRemark != null">tblRemark = #{tblRemark},</if>
			tblModifieddate = date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
        </set>
        where tblId = #{tableId}
    </update>
    
    <delete id="deleteGenTableByIds" parameterType="String">
        delete from ToolGenTable where tblId in
        <foreach collection="array" item="tableId" open="(" separator="," close=")">
            #{tableId}
        </foreach>
    </delete>

</mapper>