<?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="cn.com.tenlion.configtable.dao.configtableoperation.IConfigTableOperationDao">

    <resultMap id="configTableOperationDTO" type="cn.com.tenlion.configtable.pojo.dtos.configtableoperation.ConfigTableOperationDTO"></resultMap>

    <update id="updateCheck" parameterType="map">
        UPDATE
            ${configTableCode}
        SET
            check_status = #{checkStatus},
            check_remark = #{checkRemark}
        WHERE
            uid = #{uid}
    </update>

    <update id="updateTableField" parameterType="map">
        ALTER TABLE ${configTableCode} MODIFY COLUMN
        <!-- 文字、定位、链接 -->
        <if test="field.configTableFieldType == 1 or field.configTableFieldType == 5 or field.configTableFieldType == 6 ">
            `${field.configTableFieldCode}` varchar(${ field.configTableFieldNumber > 1 ? field.configTableFieldNumber : 500}) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 1 ? '文字' : field.configTableFieldType == 5 ? '定位' : '链接'} '
        </if>
        <!-- 照片 -->
        <if test="field.configTableFieldType == 2">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 40) - 1 }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}'
        </if>
        <!-- 音频、视频 -->
        <if test="field.configTableFieldType == 3 or field.configTableFieldType == 4">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 74) - 1  }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}'
        </if>
        <!-- 文章 -->
        <if test="field.configTableFieldType == 7">
            `${field.configTableFieldCode}` text COMMENT '${field.configTableFieldName} #文章'
        </if>
        <!-- 单选 -->
        <if test="field.configTableFieldType == 8">
            `${field.configTableFieldCode}` varchar(36) COMMENT '${field.configTableFieldName} #单选'
        </if>
        <!-- 多选 -->
        <if test="field.configTableFieldType == 9">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 37) - 1 }) COMMENT '${field.configTableFieldName} #多选'
        </if>
    </update>

    <delete id="deleteTableField" parameterType="map">
        ALTER TABLE ${configTableCode} DROP COLUMN `${field.configTableFieldCode}`
    </delete>

    <insert id="saveTableField" parameterType="map">
        ALTER TABLE ${configTableCode} ADD COLUMN
        <!-- 文字、定位、链接 -->
        <if test="field.configTableFieldType == 1 or field.configTableFieldType == 5 or field.configTableFieldType == 6 ">
            `${field.configTableFieldCode}` varchar(${ field.configTableFieldNumber > 1 ? field.configTableFieldNumber : 500}) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 1 ? '文字' : field.configTableFieldType == 5 ? '定位' : '链接'} '
        </if>
        <!-- 照片 -->
        <if test="field.configTableFieldType == 2">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 40) - 1 }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}'
        </if>
        <!-- 音频、视频 -->
        <if test="field.configTableFieldType == 3 or field.configTableFieldType == 4">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 74) - 1  }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}'
        </if>
        <!-- 文章 -->
        <if test="field.configTableFieldType == 7">
            `${field.configTableFieldCode}` text COMMENT '${field.configTableFieldName} #文章'
        </if>
        <!-- 单选 -->
        <if test="field.configTableFieldType == 8">
            `${field.configTableFieldCode}` varchar(36) COMMENT '${field.configTableFieldName} #单选'
        </if>
        <!-- 多选 -->
        <if test="field.configTableFieldType == 9">
            `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 37) - 1 }) COMMENT '${field.configTableFieldName} #多选'
        </if>
        BEFORE check_status
    </insert>

    <delete id="deleteTable" parameterType="java.lang.String">
        DROP TABLE ${configTableCode}
    </delete>

    <!-- 动态表单数据删除 -->
    <delete id="deleteTableFieldData" parameterType="map">
        UPDATE
            ${configTableCode}
        SET
            is_delete = 1
        WHERE
            uid = #{uid}
    </delete>

    <!-- 动态表单数据新增 -->
    <insert id="saveTableFieldData" parameterType="map">
        INSERT INTO ${configTableCode} (
        <foreach item="field" collection="saveList" separator="," >
            <if test="field.name != 'id'">
                `${field.name}`
            </if>
        </foreach>
        ) VALUES(
        <foreach item="field" collection="saveList" separator="," >
            <if test="field.name != 'id'">
                #{field.value}
            </if>
        </foreach>
        )
    </insert>

    <!-- 动态表单数据修改 -->
    <update id="updateTableFieldData" parameterType="map">
        UPDATE
             ${configTableCode}
        SET
            <foreach item="field" collection="updateList" separator="," >
                <if test="field.name != 'id' and field.name != 'creator' and field.name != 'gmt_create' and field.name != 'is_delete' ">
                    `${field.name}` = #{field.value}
                </if>
            </foreach>
        WHERE
            is_delete = 0
        <foreach item="field" collection="updateList" >
            <if test="field.name == 'uid'">
                AND uid = #{field.value}
            </if>
        </foreach>
    </update>

    <!-- 动态表单数据查询 -->
    <select id="getTableFieldData" parameterType="map" resultType="map">
        SELECT
            t1.*
        FROM
            ${configTableCode} t1
        WHERE
            t1.is_delete = 0
            <if test="userId != null and userId != ''">
                AND t1.creator = #{userId}
            </if>
            <if test="uid != null and uid != ''">
                AND t1.uid = #{uid}
            </if>
        ORDER BY
            t1.gmt_create DESC
    </select>

    <!-- 查询动态表单的基础配置信息 -->
    <select id="getTableFieldConfig" parameterType="java.lang.String" resultMap="configTableOperationDTO">
        SELECT
            LOWER(t.column_name) `name`,
            t.column_comment `comment` ,
            t.character_maximum_length maxLength,
            t.data_type columnType
        FROM
            information_schema.`columns` t
        WHERE
            table_name = UPPER('${configTableCode}') AND t.table_schema = (SELECT DATABASE())
        ORDER BY
            t.ordinal_position asc
    </select>

    <!-- 动态创建数据库表 -->
    <update id="saveTable" parameterType="map">
         CREATE TABLE `${configTableCode}` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '${configTableName} 自增主键',
            `uid` char(36) NOT NULL COMMENT '${configTableName} 主键',
            <foreach item="field" collection="configTableFieldList" >
                <!-- 文字、定位、链接 -->
                <if test="field.configTableFieldType == 1 or field.configTableFieldType == 5 or field.configTableFieldType == 6 ">
                    `${field.configTableFieldCode}` varchar(${ field.configTableFieldNumber > 1 ? field.configTableFieldNumber : 500}) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 1 ? '文字' : field.configTableFieldType == 5 ? '定位' : '链接'} ',
                </if>
                <!-- 照片 -->
                <if test="field.configTableFieldType == 2">
                    `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 40) - 1 }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}',
                </if>
                <!-- 音频、视频 -->
                <if test="field.configTableFieldType == 3 or field.configTableFieldType == 4">
                    `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 74) - 1  }) COMMENT '${field.configTableFieldName}#${field.configTableFieldType == 2 ? '照片' : field.configTableFieldType == 3 ? '音频' : '视频'}',
                </if>
                <!-- 文章 -->
                <if test="field.configTableFieldType == 7">
                    `${field.configTableFieldCode}` text COMMENT '${field.configTableFieldName} #文章',
                </if>
                <!-- 单选 -->
                <if test="field.configTableFieldType == 8">
                    `${field.configTableFieldCode}` varchar(36) COMMENT '${field.configTableFieldName} #单选',
                </if>
                <!-- 多选 -->
                <if test="field.configTableFieldType == 9">
                    `${field.configTableFieldCode}` varchar(${ (field.configTableFieldNumber * 37) - 1 }) COMMENT '${field.configTableFieldName} #多选',
                </if>
            </foreach>
            `check_status` char(1) DEFAULT NULL COMMENT '审核状态,默认1(待审核),0(不通过),2(通过)',
            `check_remark` varchar(500) DEFAULT NULL COMMENT '审核结果',
            `creator` char(36) DEFAULT NULL COMMENT '创建人',
            `gmt_create` char(25) DEFAULT NULL  COMMENT '创建时间',
            `modifier` char(36) DEFAULT NULL  COMMENT '修改人',
            `gmt_modified` char(25) DEFAULT NULL  COMMENT '修改时间',
            `is_delete` char(1) DEFAULT NULL  COMMENT '数据状态',
            PRIMARY KEY (`id`,`uid`),
            UNIQUE KEY `id` (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='${configTableName}';
    </update>

    <!-- 列表 -->
    <select id="list" parameterType="map" resultType="map">
        SELECT
            t1.*
        FROM
            ${configTableCode} t1
        WHERE
            t1.is_delete = 0
        <if test="userId != null and userId != ''">
            AND t1.creator = #{userId}
        </if>
        <!-- 不是后台查询,只能加载审核中/审核通过的数据, 后台则可以看全部 -->
        <if test="backSystem == null">
            AND t1.check_status != 0
        </if>
        <if test="check_status != null and check_status != ''">
            AND t1.check_status = #{check_status}
        </if>
        <if test="keywords != null and keywords != ''">
            AND (
                t1.uid LIKE CONCAT('%', #{keywords}, '%')
            )
        </if>
        <if test="startTime != null and startTime != ''">
            AND LEFT(t1.gmt_create, 10) <![CDATA[ >= ]]> #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND LEFT(t1.gmt_create, 10) <![CDATA[ <= ]]> #{endTime}
        </if>
        ORDER BY
            t1.gmt_create DESC
    </select>

    <!-- 判断数据库是否已经存在真实表单 -->
    <select id="getTableExist" parameterType="java.lang.String" resultType="java.lang.Integer">
        SELECT
            count(*)
        FROM
            information_schema.TABLES
        WHERE
            TABLE_NAME = #{tableName}
    </select>
</mapper>