<?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.boman.gen.mapper.GenTableColumnMapper">
    
    <resultMap type="GenTableColumn" id="GenTableColumnResult">
        <result property="id"       column="id"      />
        <result property="tableId"        column="table_id"       />
        <result property="columnName"     column="column_name"    />
        <result property="columnComment"  column="column_comment" />
        <result property="columnType"     column="column_type"    />
        <result property="javaType"       column="java_type"      />
        <result property="javaField"      column="java_field"     />
        <result property="isPk"           column="is_pk"          />
        <result property="isIncrement"    column="is_increment"   />
        <result property="isRequired"     column="is_required"    />
        <result property="isInsert"       column="is_insert"      />
        <result property="isEdit"         column="is_edit"        />
        <result property="isList"         column="is_list"        />
        <result property="isQuery"        column="is_query"       />
        <result property="queryType"      column="query_type"     />
        <result property="htmlType"       column="html_type"      />
        <result property="foreignKey"     column="foreign_key"      />
        <result property="dictType"       column="dict_type"      />
        <result property="sort"           column="sort"           />
        <result property="createBy"       column="create_by"      />
        <result property="createTime"     column="create_time"    />
        <result property="updateBy"       column="update_by"      />
        <result property="updateTime"     column="update_time"    />
        <result property="hrParentId"     column="hr_parent_id"    />
        <result property="mask"     column="mask"    />
        <result property="defaultValue"     column="default_value"    />
        <result property="numRows"     column="num_rows"    />
        <result property="numColumns"     column="num_columns"    />
        <result property="fieldTranslator"     column="field_translator"    />
        <result property="extendedAttributes"     column="extended_attributes"    />
        <result property="isIn"     column="is_in"    />
        <result property="isOut"     column="is_out"    />
        <result property="regular"     column="regular"    />
        <result property="tableColumnName"     column="table_column_name"    />
        <result property="seqName"     column="seq_name"    />
    </resultMap>
	
	<sql id="selectGenTableColumnVo">
        select id, table_id, column_name, column_comment, column_type, java_type, java_field, is_pk, is_increment, is_required, is_insert, is_edit, is_list, is_query, query_type, html_type, foreign_key, dict_type, sort, create_by, create_time, update_by, update_time, hr_parent_id, mask, default_value, num_rows, num_columns, field_translator, extended_attributes, is_in, is_out, regular, table_column_name, seq_name from gen_table_column
    </sql>
	
    <select id="selectGenTableColumnListByTableId" parameterType="GenTableColumn" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where table_id = #{tableId}
        order by sort
    </select>

    <select id="selectGenTableColumnIsListByTableId" parameterType="GenTableColumn" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where table_id = #{tableId} and (is_in = 'Y' or is_out = 'Y')
    </select>

    <select id="selectGenTableColumnList" parameterType="GenTableColumn" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        <where>
            <if test="tableId != null and tableId != ''">
                AND table_id = #{tableId}
            </if>
            <if test="columnName != null and columnName != ''">
                AND lower(column_name) like lower(concat('%', #{columnName}, '%'))
            </if>
            <if test="htmlType != null and htmlType != ''">
                AND html_type = #{htmlType}
            </if>
        </where>
    </select>

    <select id="selectGenTableColumnByColumnId" parameterType="long"  resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where id = #{columnId}
       limit 1
    </select>

    <select id="listByTableIdList" parameterType="list" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where table_id in
        <foreach collection="tableIdList" open="(" close=")" separator="," item="tableId">
            #{tableId}
        </foreach>
        order by sort
    </select>

    <select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
		select column_name, (case when (is_nullable = 'no' <![CDATA[ && ]]> column_key != 'PRI') then '1' else null end) as is_required, (case when column_key = 'PRI' then '1' else '0' end) as is_pk, ordinal_position as sort, column_comment, (case when extra = 'auto_increment' then '1' else '0' end) as is_increment, column_type
		from information_schema.columns where table_schema = (select database()) and table_name = (#{tableName})
		order by ordinal_position
	</select>
    
    <insert id="insertGenTableColumn" parameterType="GenTableColumn" useGeneratedKeys="true" keyProperty="id">
        insert into gen_table_column (
			<if test="tableId != null and tableId != ''">table_id,</if>
			<if test="columnName != null and columnName != ''">column_name,</if>
			<if test="columnComment != null and columnComment != ''">column_comment,</if>
			<if test="columnType != null and columnType != ''">column_type,</if>
			<if test="javaType != null and javaType != ''">java_type,</if>
			<if test="javaField != null  and javaField != ''">java_field,</if>
			<if test="isPk != null and isPk != ''">is_pk,</if>
			<if test="isIncrement != null and isIncrement != ''">is_increment,</if>
			<if test="isRequired != null and isRequired != ''">is_required,</if>
			<if test="isInsert != null and isInsert != ''">is_insert,</if>
			<if test="isEdit != null and isEdit != ''">is_edit,</if>
			<if test="isList != null and isList != ''">is_list,</if>
			<if test="isQuery != null and isQuery != ''">is_query,</if>
			<if test="queryType != null and queryType != ''">query_type,</if>
			<if test="htmlType != null and htmlType != ''">html_type,</if>
			<if test="foreignKey != null ">foreign_key,</if>
			<if test="dictType != null and dictType != ''">dict_type,</if>
			<if test="sort != null">sort,</if>
			<if test="createBy != null and createBy != ''">create_by,</if>
			<if test="hrParentId != null and hrParentId != ''">hr_parent_id,</if>
			<if test="mask != null and mask != ''">mask,</if>
			<if test="defaultValue != null and defaultValue != ''">default_value,</if>
			<if test="numRows != null and numRows != ''">num_rows,</if>
			<if test="numColumns != null and numColumns != ''">num_columns,</if>
			<if test="fieldTranslator != null and fieldTranslator != ''">field_translator,</if>
			<if test="extendedAttributes != null and extendedAttributes != ''">extended_attributes,</if>
			<if test="isIn != null and isIn != ''">is_in,</if>
			<if test="isOut != null and isOut != ''">is_out,</if>
			<if test="regular != null and regular != ''">regular,</if>
			<if test="tableColumnName != null and tableColumnName != ''">table_column_name,</if>
			<if test="seqName != null and seqName != ''">seq_name,</if>
			create_time
         )values(
			<if test="tableId != null and tableId != ''">#{tableId},</if>
			<if test="columnName != null and columnName != ''">#{columnName},</if>
			<if test="columnComment != null and columnComment != ''">#{columnComment},</if>
			<if test="columnType != null and columnType != ''">#{columnType},</if>
			<if test="javaType != null and javaType != ''">#{javaType},</if>
			<if test="javaField != null and javaField != ''">#{javaField},</if>
			<if test="isPk != null and isPk != ''">#{isPk},</if>
			<if test="isIncrement != null and isIncrement != ''">#{isIncrement},</if>
			<if test="isRequired != null and isRequired != ''">#{isRequired},</if>
			<if test="isInsert != null and isInsert != ''">#{isInsert},</if>
			<if test="isEdit != null and isEdit != ''">#{isEdit},</if>
			<if test="isList != null and isList != ''">#{isList},</if>
			<if test="isQuery != null and isQuery != ''">#{isQuery},</if>
			<if test="queryType != null and queryType != ''">#{queryType},</if>
			<if test="htmlType != null and htmlType != ''">#{htmlType},</if>
			<if test="foreignKey != null">#{foreignKey},</if>
			<if test="dictType != null and dictType != ''">#{dictType},</if>
			<if test="sort != null">#{sort},</if>
			<if test="createBy != null and createBy != ''">#{createBy},</if>
			<if test="hrParentId != null and hrParentId != ''">#{hrParentId},</if>
			<if test="mask != null and mask != ''">#{mask},</if>
			<if test="defaultValue != null and defaultValue != ''">#{defaultValue},</if>
			<if test="numRows != null and numRows != ''">#{numRows},</if>
			<if test="numColumns != null and numColumns != ''">#{numColumns},</if>
			<if test="fieldTranslator != null and fieldTranslator != ''">#{fieldTranslator},</if>
			<if test="extendedAttributes != null and extendedAttributes != ''">#{extendedAttributes},</if>
			<if test="isIn != null and isIn != ''">#{isIn},</if>
			<if test="isOut != null and isOut != ''">#{isOut},</if>
			<if test="regular != null and regular != ''">#{regular},</if>
			<if test="tableColumnName != null and tableColumnName != ''">#{tableColumnName},</if>
			<if test="seqName != null and seqName != ''">#{seqName},</if>
			sysdate()
         )
    </insert>
	 
    <update id="updateGenTableColumn" parameterType="GenTableColumn">
        update gen_table_column
        <set>
            <if test="tableId != null and tableId != ''">table_id = #{tableId},</if>
            <if test="columnName != null and columnName != ''">column_name = #{columnName},</if>
            <if test="columnComment != null and columnComment != ''">column_comment = #{columnComment},</if>
            <if test="columnType != null and columnType != ''">column_type = #{columnType},</if>
            <if test="javaType != null and javaType != ''">java_type = #{javaType},</if>
            <if test="javaField != null and javaField != ''">java_field = #{javaField},</if>
            <if test="isPk != null and isPk != ''">is_pk = #{isPk},</if>
            <if test="isIncrement != null and isIncrement != ''">is_increment = #{isIncrement},</if>
            <if test="isRequired != null and isRequired != ''">is_required = #{isRequired},</if>
            <if test="isInsert != null and isInsert != ''">is_insert = #{isInsert},</if>
            <if test="isEdit != null and isEdit != ''">is_edit = #{isEdit},</if>
            <if test="isList != null and isList != ''">is_list = #{isList},</if>
            <if test="isQuery != null and isQuery != ''">is_query = #{isQuery},</if>
            <if test="queryType != null and queryType != ''">query_type = #{queryType},</if>
            <if test="htmlType != null and htmlType != ''">html_type = #{htmlType},</if>
            <if test="foreignKey != null and foreignKey != ''">foreign_key = #{foreignKey},</if>
            <if test="dictType != null and dictType != ''">dict_type = #{dictType},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="createBy != null and createBy != ''">create_by = #{createBy},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="hrParentId != null and hrParentId != ''">hr_parent_id = #{hrParentId},</if>
            <if test="mask != null and mask != ''">mask = #{mask},</if>
            <if test="defaultValue != null and defaultValue != ''">default_value = #{defaultValue},</if>
            <if test="numRows != null and numRows != ''">num_rows = #{numRows},</if>
            <if test="numColumns != null and numColumns != ''">num_columns = #{numColumns},</if>
            <if test="fieldTranslator != null and fieldTranslator != ''">field_translator = #{fieldTranslator},</if>
            <if test="extendedAttributes != null and extendedAttributes != ''">extended_attributes = #{extendedAttributes},</if>
            <if test="isIn != null and isIn != ''">is_in = #{isIn},</if>
            <if test="isOut != null and isOut != ''">is_out = #{isOut},</if>
            <if test="regular != null and regular != ''">regular = #{regular},</if>
            <if test="seqName != null and seqName != ''">seq_name = #{seqName},</if>
            update_time = sysdate()
        </set>
        where id = #{id}
    </update>

    <delete id="deleteGenTableColumnByIds" parameterType="Long">
        delete from gen_table_column where table_id in 
        <foreach collection="array" item="tableId" open="(" separator="," close=")">
            #{tableId}
        </foreach>
    </delete>


    <select id="checkColumnNameUnique" parameterType="GenTableColumn" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where column_name = #{columnName} and table_id = #{tableId} limit 1
    </select>
    
    <delete id="deleteGenTableColumns">
        delete from gen_table_column where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </delete>



    <delete id="deleteGenTableColumnByColumnIds" parameterType="Long">
        delete from gen_table_column where id in
        <foreach collection="array" item="columnId" open="(" separator="," close=")">
            #{columnId}
        </foreach>
    </delete>

</mapper>