<?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="io.renren.modules.levelManage.dao.ProjectAttributesDao">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="io.renren.modules.levelManage.entity.ProjectAttributesEntity" id="projectAttributesMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="type" column="type"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <select id="hasProject" resultType="INTEGER">
        SELECT COUNT(*) FROM information_schema.`TABLES` WHERE table_name="project" AND table_schema=#{schema}
    </select>

    <update id="createTable" parameterType="map">
        CREATE TABLE project
        (
        <foreach collection="cols" item="listItem" separator=",">
            ${listItem.name} ${listItem.type} COMMENT #{listItem.comment}
            <if test="listItem.name == 'id'">
                auto_increment PRIMARY KEY
            </if>
        </foreach>
        );
    </update>

    <select id="listAttribute" resultType="map">
        select name,type,remark from project_attributes;;
    </select>

    <select id="countProject" resultType="INTEGER">
        select count(*) from project;
    </select>

    <select id="listCols" resultType="String">
        SELECT name from project_attributes;
    </select>

    <select id="list" resultType="map">
        SELECT
        <foreach collection="tableCols" item="listItem" separator="," >
            ${listItem}
        </foreach>
        FROM
        project
        limit #{page}, #{size};
    </select>

    <select id="listBoolCols" resultType="String">
        SELECT name from project_attributes where type='bool'
    </select>

    <select id="selectById" resultType="map">
        SELECT
        <foreach collection="tableCols" item="listItem" separator="," >
            ${listItem}
        </foreach>
        FROM
        project
        WHERE id=#{id}
    </select>

    <insert id="saveWithCols" parameterType="MAP">
        INSERT into project
        <foreach collection="cols" item="col" separator="," close=")" open="(">
            ${col.cols}
        </foreach>
        VALUES
        <foreach collection="cols" item="col" separator="," open="(" close=")">
            #{col.value}
        </foreach>
    </insert>

    <select id="selectProjectNameById" resultType="String">
        SELECT
        name
        from
        project
        where
        id=#{id}
    </select>

    <update id="updateWithCols">
        UPDATE project
        SET
        <foreach collection="cols" item="col" separator="," >
            ${col.cols} = #{col.value}
        </foreach>
        WHERE
        id = #{id}
    </update>

    <select id="selectByName" resultType="map">
        SELECT
        <foreach collection="tableCols" item="listItem" separator="," >
            ${listItem}
        </foreach>
        FROM
        project
        WHERE SUBSTR(name,5) like concat('%',#{name},'%')
        limit #{page}, #{size};
    </select>

    <select id="getCountByName" resultType="INTEGER">
        SELECT count(*)
        from project
        WHERE
        SUBSTR(name,5) like concat('%',#{name},'%');
    </select>

    <delete id="removeProjectByIds" parameterType="List">
        DELETE from project where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            ${id}
        </foreach>
        ;
    </delete>

    <update id="dropProject">
        DROP TABLE project;
    </update>
    <update id="truncateProjectAttribute">
        truncate table project_attributes;
    </update>

    <select id="listProjectName" resultType="map">
        SELECT id,name
        FROM
        project
    </select>

    <select id="countProjectLevel" resultType="String">
        SELECT count(*)
        FROM level
        WHERE project_id=#{id}
    </select>

    <select id="selectRootLevel" resultType="Integer">
        SELECT id FROM LEVEL WHERE parent_id=0 AND project_id=#{id}
    </select>

    <select id="listProjectByUid" resultType="map">
        SELECT id,name
        FROM
        project
        WHERE create_user=${uid}
    </select>

</mapper>