<?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.institutionmanagement.dao.institution.IInstitutionDao">

    <resultMap id="institutionDTO" type="cn.com.tenlion.institutionmanagement.pojo.dtos.institution.InstitutionDTO">
        <result column="institution_id" property="institutionId"/>
        <result column="institution_name" property="institutionName"/>
        <result column="institution_desc" property="institutionDesc"/>
        <result column="institution_image" property="institutionImage"/>
        <result column="institution_address" property="institutionAddress"/>
        <result column="institution_x" property="institutionX"/>
        <result column="institution_y" property="institutionY"/>
        <result column="institution_founding_time" property="institutionFoundingTime"/>
        <result column="institution_principal" property="institutionPrincipal"/>
        <result column="institution_contact" property="institutionContact"/>
        <result column="institution_region_province" property="institutionRegionProvince"/>
        <result column="institution_region_city" property="institutionRegionCity"/>
        <result column="institution_region_area" property="institutionRegionArea"/>
        <result column="institution_region_village" property="institutionRegionVillage"/>
        <result column="institution_region_street" property="institutionRegionStreet"/>
        <result column="institution_type" property="institutionType"/>
        <result column="creator" property="creator"/>
        <result column="gmt_create" property="gmtCreate"/>
    </resultMap>

    <resultMap id="institutionBO" type="cn.com.tenlion.institutionmanagement.pojo.bos.institution.InstitutionBO">
        <result column="institution_id" property="institutionId"/>
        <result column="institution_name" property="institutionName"/>
        <result column="institution_desc" property="institutionDesc"/>
        <result column="institution_image" property="institutionImage"/>
        <result column="institution_address" property="institutionAddress"/>
        <result column="institution_x" property="institutionX"/>
        <result column="institution_y" property="institutionY"/>
        <result column="institution_founding_time" property="institutionFoundingTime"/>
        <result column="institution_principal" property="institutionPrincipal"/>
        <result column="institution_contact" property="institutionContact"/>
        <result column="institution_region_province" property="institutionRegionProvince"/>
        <result column="institution_region_city" property="institutionRegionCity"/>
        <result column="institution_region_area" property="institutionRegionArea"/>
        <result column="institution_region_village" property="institutionRegionVillage"/>
        <result column="institution_region_street" property="institutionRegionStreet"/>
        <result column="institution_type" property="institutionType"/>
        <result column="creator" property="creator"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="modifier" property="modifier"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="is_delete" property="isDelete"/>
    </resultMap>

    <resultMap id="institutionPO" type="cn.com.tenlion.institutionmanagement.pojo.pos.institution.InstitutionPO">
        <result column="institution_id" property="institutionId"/>
        <result column="institution_name" property="institutionName"/>
        <result column="institution_desc" property="institutionDesc"/>
        <result column="institution_image" property="institutionImage"/>
        <result column="institution_address" property="institutionAddress"/>
        <result column="institution_x" property="institutionX"/>
        <result column="institution_y" property="institutionY"/>
        <result column="institution_founding_time" property="institutionFoundingTime"/>
        <result column="institution_principal" property="institutionPrincipal"/>
        <result column="institution_contact" property="institutionContact"/>
        <result column="institution_region_province" property="institutionRegionProvince"/>
        <result column="institution_region_city" property="institutionRegionCity"/>
        <result column="institution_region_area" property="institutionRegionArea"/>
        <result column="institution_region_village" property="institutionRegionVillage"/>
        <result column="institution_region_street" property="institutionRegionStreet"/>
        <result column="institution_type" property="institutionType"/>
        <result column="creator" property="creator"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="modifier" property="modifier"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="is_delete" property="isDelete"/>
    </resultMap>



    <!-- 建表 -->
    <update id="createTable">
        CREATE TABLE IF NOT EXISTS `m_institution` (
          `id` bigint(20) NOT NULL AUTO_INCREMENT,
          `institution_id` char(36) DEFAULT NULL COMMENT '机构ID',
          `institution_name` varchar(255) DEFAULT NULL COMMENT '机构名称',
          `institution_desc` varchar(500) DEFAULT NULL COMMENT '机构描述',
          `institution_image` varchar(500) DEFAULT NULL COMMENT '机构图片',
          `institution_address` varchar(255) DEFAULT NULL COMMENT '机构详情地址',
          `institution_x` varchar(50) DEFAULT NULL COMMENT '机构经度',
          `institution_y` varchar(50) DEFAULT NULL COMMENT '机构纬度',
          `institution_founding_time` varchar(50) DEFAULT NULL COMMENT '机构成立时间',
          `institution_principal` varchar(100) DEFAULT NULL COMMENT '机构负责人',
          `institution_contact` varchar(200) DEFAULT NULL COMMENT '机构联系方式',
          `institution_region_province` varchar(255) DEFAULT NULL COMMENT '行政区划-省',
          `institution_region_city` varchar(255) DEFAULT NULL COMMENT '行政区化-市',
          `institution_region_area` varchar(255) DEFAULT NULL COMMENT '行政区化-区',
          `institution_region_village` varchar(255) DEFAULT NULL COMMENT '行政区化-村',
          `institution_region_street` varchar(255) DEFAULT NULL COMMENT '行政区化-街',
          `institution_type` varchar(255) DEFAULT NULL COMMENT '机构类型',
          `creator` char(36) DEFAULT NULL COMMENT '创建人ID',
          `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
          `modifier` char(36) DEFAULT NULL COMMENT '修改人',
          `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
          `is_delete` int(1) DEFAULT NULL COMMENT '删除状态 0未删除 1以删除',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COMMENT='机构管理';
    </update>






    <!-- 新增机构管理 -->
    <insert id="save" parameterType="map">
        INSERT INTO m_institution(
            institution_id,
            institution_name,
            institution_desc,
            institution_image,
            institution_address,
            institution_x,
            institution_y,
            institution_founding_time,
            institution_principal,
            institution_contact,
            institution_region_province,
            institution_region_city,
            institution_region_area,
            institution_region_village,
            institution_region_street,
            institution_type,
            creator,
            gmt_create,
            modifier,
            gmt_modified,
            is_delete
        ) VALUES(
            #{institutionId},
            #{institutionName},
            #{institutionDesc},
            #{institutionImage},
            #{institutionAddress},
            #{institutionX},
            #{institutionY},
            #{institutionFoundingTime},
            #{institutionPrincipal},
            #{institutionContact},
            #{institutionRegionProvince},
            #{institutionRegionCity},
            #{institutionRegionArea},
            #{institutionRegionVillage},
            #{institutionRegionStreet},
            #{institutionType},
            #{creator},
            #{gmtCreate},
            #{modifier},
            #{gmtModified},
            #{isDelete}
        )
    </insert>

    <!-- 删除机构管理 -->
    <update id="remove" parameterType="map">
        UPDATE
            m_institution
        SET
            gmt_modified = #{gmtModified},
            modifier = #{modifier},
            is_delete = 1
        WHERE
            institution_id IN
        <foreach collection="institutionIds" index="index" open="(" separator="," close=")">
            #{institutionIds[${index}]}
        </foreach>
    </update>

    <!-- 删除机构管理（物理） -->
    <update id="delete" parameterType="map">
        DELETE FROM
            m_institution
        WHERE
            institution_id IN
        <foreach collection="institutionIds" index="index" open="(" separator="," close=")">
            #{institutionIds[${index}]}
        </foreach>
    </update>

    <!-- 修改机构管理 -->
    <update id="update" parameterType="map">
        UPDATE
            m_institution
        SET
        <if test="institutionName != null and institutionName != ''">
            institution_name = #{institutionName},
        </if>
        <if test="institutionType != null and institutionType != ''">
            institution_type = #{institutionType},
        </if>
            institution_desc = #{institutionDesc},
            institution_image = #{institutionImage},
            institution_address = #{institutionAddress},
            institution_x = #{institutionX},
            institution_y = #{institutionY},
            institution_founding_time = #{institutionFoundingTime},
            institution_principal = #{institutionPrincipal},
            institution_contact = #{institutionContact},
            institution_region_province = #{institutionRegionProvince},
            institution_region_city = #{institutionRegionCity},
            institution_region_area = #{institutionRegionArea},
            institution_region_village = #{institutionRegionVillage},
            institution_region_street = #{institutionRegionStreet},
            gmt_modified = #{gmtModified},
            modifier = #{modifier}
        WHERE
            institution_id = #{institutionId}
    </update>

    <!-- 机构管理详情 -->
    <select id="get" parameterType="map" resultMap="institutionDTO">
        SELECT
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.institution_type,
            t1.institution_id
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
            <if test="institutionId != null and institutionId != ''">
        AND
            t1.institution_id = #{institutionId}
            </if>
    </select>

    <!-- 机构管理详情 -->
    <select id="getBO" parameterType="map" resultMap="institutionBO">
        SELECT
            t1.institution_id,
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.institution_type,
            t1.creator,
            t1.gmt_create,
            t1.modifier,
            t1.gmt_modified,
            t1.is_delete
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
            <if test="institutionId != null and institutionId != ''">
        AND
            t1.institution_id = #{institutionId}
            </if>
    </select>

    <!-- 机构管理详情 -->
    <select id="getPO" parameterType="map" resultMap="institutionPO">
        SELECT
            t1.institution_id,
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.institution_type,
            t1.creator,
            t1.gmt_create,
            t1.modifier,
            t1.gmt_modified,
            t1.is_delete
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
            <if test="institutionId != null and institutionId != ''">
        AND
            t1.institution_id = #{institutionId}
            </if>
    </select>

    <!-- 机构管理列表 -->
    <select id="list" parameterType="map" resultMap="institutionDTO">
        SELECT
            t1.institution_id,
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.institution_type,
            t1.creator,
            t1.gmt_create
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
        <if test="keywords != null and keywords != ''">
        AND (
            <!-- 这里添加其他条件 -->
            t1.institution_name 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>
        <if test="institutionIds != null and institutionIds.size > 0">
        AND
            t1.institution_id IN
            <foreach collection="institutionIds" index="index" open="(" separator="," close=")">
                #{institutionIds[${index}]}
            </foreach>
        </if>
         <if test="institutionType != null and institutionType != ''">
            AND
            t1.institution_type = #{institutionType}
          </if>
        ORDER BY t1.levels ASC
    </select>

    <!-- 机构管理列表 -->
    <select id="listBO" parameterType="map" resultMap="institutionBO">
        SELECT
            t1.institution_id,
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.institution_type,
            t1.creator,
            t1.gmt_create,
            t1.modifier,
            t1.gmt_modified,
            t1.is_delete
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
        <if test="keywords != null and keywords != ''">
        AND (
            <!-- 这里添加其他条件 -->
            t1.institution_name 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>
        <if test="institutionIds != null and institutionIds.size > 0">
        AND
            t1.institution_id IN
            <foreach collection="institutionIds" index="index" open="(" separator="," close=")">
                #{institutionIds[${index}]}
            </foreach>
        </if>
        <if test="institutionType != null and institutionType != ''">
            AND
            t1.institution_type = #{institutionType}
        </if>
        ORDER BY t1.gmt_create DESC
    </select>

    <!-- 机构管理列表 -->
    <select id="listPO" parameterType="map" resultMap="institutionPO">
        SELECT
            t1.institution_id,
            t1.institution_name,
            t1.institution_desc,
            t1.institution_image,
            t1.institution_address,
            t1.institution_x,
            t1.institution_y,
            t1.institution_founding_time,
            t1.institution_principal,
            t1.institution_contact,
            t1.institution_region_province,
            t1.institution_region_city,
            t1.institution_region_area,
            t1.institution_region_village,
            t1.institution_region_street,
            t1.creator,
            t1.gmt_create,
            t1.modifier,
            t1.gmt_modified,
            t1.is_delete
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
        <if test="keywords != null and keywords != ''">
        AND (
            <!-- 这里添加其他条件 -->
            t1.institution_name 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>
        <if test="institutionIds != null and institutionIds.size > 0">
        AND
            t1.institution_id IN
            <foreach collection="institutionIds" index="index" open="(" separator="," close=")">
                #{institutionIds[${index}]}
            </foreach>
        </if>
        <if test="institutionType != null and institutionType != ''">
            AND
            t1.institution_type = #{institutionType}
        </if>
        ORDER BY t1.gmt_create DESC
    </select>

    <!-- 机构管理统计  -->
    <select id="count" parameterType="map" resultType="Integer">
        SELECT
            COUNT(*)
        FROM
            m_institution t1
        WHERE
            t1.is_delete = 0
    </select>

</mapper>