<?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.lantone.daqe.mapper.ColumnVerifyMapper">

    <select id="getColumnVerifyPage" resultType="com.lantone.daqe.dto.GetColumnVerifyPageDTO">
        SELECT * from(
        SELECT
        a.column_id,
        a.table_cname,
        a.table_ename,
        a.column_cname,
        a.column_ename,
        GROUP_CONCAT(a.isRequired SEPARATOR ';') isRequired,
        GROUP_CONCAT(a.standardvalue SEPARATOR ';') standardvalue,
        GROUP_CONCAT(a.regular SEPARATOR ';') regular,
        a.description,
        a.gmt_create
        FROM
        (
        SELECT
        dcv.column_id,
        dcv.table_cname,
        dcv.table_ename,
        dcv.column_cname,
        dcv.column_ename,
        dcv.type,
        dcv.description,
        dcv.gmt_create,
        CASE dcv.type WHEN 1 THEN dcv.verify_val ELSE NULL END isRequired,
        CASE dcv.type WHEN 2 THEN dcv.verify_val ELSE NULL END standardvalue,
        CASE dcv.type WHEN 3 THEN dcv.verify_val ELSE NULL END regular
        FROM
        dc_column_verify dcv
        WHERE
        dcv.is_deleted = 'N'
        <if test="getColumnVerifyPageVO.tableCname != null and getColumnVerifyPageVO.tableCname != ''">
            AND dcv.table_cname = #{getColumnVerifyPageVO.tableCname}
        </if>
        <if test="getColumnVerifyPageVO.tableEname != null and getColumnVerifyPageVO.tableEname != ''">
            AND dcv.table_ename = #{getColumnVerifyPageVO.tableEname}
        </if>
        <if test="getColumnVerifyPageVO.columnCname != null and getColumnVerifyPageVO.columnCname != ''">
            AND dcv.column_cname = #{getColumnVerifyPageVO.columnCname}
        </if>
        <if test="getColumnVerifyPageVO.columnEname != null and getColumnVerifyPageVO.columnEname != ''">
            AND dcv.column_ename= #{getColumnVerifyPageVO.columnEname}
        </if>
        ) a

        GROUP BY
        a.column_id
        )a
        where
        1=1
        <if test="getColumnVerifyPageVO.isRequired != null and getColumnVerifyPageVO.isRequired != ''">
            AND a.isRequired = #{getColumnVerifyPageVO.isRequired}
        </if>
        <if test="getColumnVerifyPageVO.regularName != null and getColumnVerifyPageVO.regularName != ''">
            AND a.regular = #{getColumnVerifyPageVO.regularName}
        </if>
        <if test="getColumnVerifyPageVO.standardValue != null and getColumnVerifyPageVO.standardValue != ''">
            AND a.standardvalue LIKE CONCAT('%',#{getColumnVerifyPageVO.standardValue},'%')
        </if>
    </select>


    <select id="getByIdColumnVerify" resultType="com.lantone.daqe.dto.GetColumnVerifyDTO">
        SELECT
        dcv.column_id,
        dcv.table_cname,
        dcv.table_ename,
        dcv.column_cname,
        dcv.column_ename,
        dcv.type,
        dcv.description,
        CASE dcv.type WHEN 1 THEN dcv.verify_val ELSE NULL END isRequired,
        CASE dcv.type WHEN 2 THEN dcv.verify_val ELSE NULL END standardvalue,
        CASE dcv.type WHEN 3 THEN dcv.verify_val ELSE NULL END regular
        FROM
        dc_column_verify dcv
        WHERE
        dcv.is_deleted = 'N'
        and dcv.column_id= #{getByIdColumnVerifyVO.columnId}
    </select>

    <select id="getColumn" resultType="com.lantone.daqe.dto.GetColumnDTO">
        SELECT
            COLUMN_NAME columnName,
            COLUMN_COMMENT columnComment
        FROM
            information_schema. COLUMNS
        WHERE
            table_name = #{addColumnVO.tableEname}
        AND table_schema = #{addColumnVO.tableSchema}
    </select>

</mapper>