<?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.gisbi.template.dao.ext.ExtVisualizationTemplateMapper">

    <resultMap id="BaseResultMap" type="io.gisbi.api.template.vo.VisualizationTemplateVO">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="pid" jdbcType="VARCHAR" property="pid"/>
        <result column="level" jdbcType="INTEGER" property="level"/>
        <result column="dv_type" jdbcType="VARCHAR" property="dvType"/>
        <result column="node_type" jdbcType="VARCHAR" property="nodeType"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="BIGINT" property="createTime"/>
        <result column="template_type" jdbcType="VARCHAR" property="templateType"/>
        <result column="snapshot" jdbcType="LONGVARCHAR" property="snapshot"/>
        <result column="template_style" jdbcType="LONGVARCHAR" property="templateStyle"/>
        <result column="template_data" jdbcType="LONGVARCHAR" property="templateData"/>
        <result column="dynamic_data" jdbcType="LONGVARCHAR" property="dynamicData"/>
    </resultMap>

    <resultMap id="BaseResultMapDTO" type="io.gisbi.api.template.dto.TemplateManageDTO"
               extends="BaseResultMap">
        <result column="recent_use_time" jdbcType="BIGINT" property="recentUseTime"/>
        <result column="label" jdbcType="VARCHAR" property="label"/>
        <result column="childrenCount" jdbcType="VARCHAR" property="childrenCount"/>
    </resultMap>

    <resultMap id="BaseResultMapCollectionDTO" type="io.gisbi.api.template.dto.TemplateManageDTO"
               extends="BaseResultMapDTO">
        <collection property="categories" ofType="String">
            <result column="category_id" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        vt
        .
        id
        , vt.`name`, vt.pid, vt.`level`,vt.`dv_type`, vt.node_type, vt.create_by, vt.create_time, vt.template_type, vt.snapshot
    </sql>
    <sql id="Blob_Column_List">
        ,vt.template_style, vt.template_data, vt.dynamic_data
    </sql>

    <select id="findBaseTemplateList" resultMap="BaseResultMapCollectionDTO">
        SELECT vt.id,
               vt.`name`,
               vt.pid,
               vt.`level`,
               vt.`dv_type`,
               vt.node_type,
               vt.create_by,
               vt.create_time,
               vt.template_type,
               vt.SNAPSHOT,
               vt.use_count,
               vtcm.category_id,
               cor.time as `recent_use_time`
        FROM visualization_template vt
                 LEFT JOIN visualization_template_category_map vtcm ON vt.id = vtcm.template_id
                 left JOIN core_opt_recent cor on cor.resource_type = 6 and vt.id = cor.resource_name
        ORDER BY vt.use_count ,vt.create_time DESC
    </select>

    <select id="findCategories" resultMap="BaseResultMapDTO">
        SELECT id,
               `name`,
               `name` as lable,
               pid,
               `level`,
               `dv_type`,
               node_type,
               create_by,
               create_time,
               template_type,
               SNAPSHOT
        FROM visualization_template_category
        order by visualization_template_category.create_time desc
    </select>

    <select id="findTemplateList" resultMap="BaseResultMapDTO">
        SELECT
        <include refid="Base_Column_List"/>
        <if test='withBlobs == "Y"'>
            <include refid="Blob_Column_List"/>
        </if>
        FROM
        visualization_template vt
        LEFT JOIN visualization_template_category_map vtcm on vt.id = vtcm.template_id
        where vtcm.category_id=#{categoryId}
        order by vt.create_time desc
    </select>

    <select id="checkCategoryMap" resultType="Long">
        SELECT count(1)
        FROM visualization_template_category_map cm
        WHERE cm.category_id = #{categoryId}
    </select>

    <select id="checkRepeatTemplateId" resultType="Long">
        SELECT count(1)
        FROM visualization_template_category_map cm
        where cm.category_id != #{categoryId}
          and cm.template_id =#{templateId}
    </select>

    <select id="checkCategoryTemplateName" resultType="Long">
        SELECT
        count(1)
        FROM
        visualization_template vt
        LEFT JOIN visualization_template_category_map vtcm ON vt.id = vtcm.template_id
        WHERE
        vt.NAME = #{templateName}
        AND vtcm.category_id IN
        <foreach collection="categories" item="categoryId" index="index" open="(" close=")" separator=",">
            #{categoryId}
        </foreach>
    </select>

    <select id="checkCategoryTemplateBatchNames" resultType="Long">
        SELECT
        count(1)
        FROM
        visualization_template vt
        LEFT JOIN visualization_template_category_map vtcm ON vt.id = vtcm.template_id
        WHERE
        vt.NAME in
        <foreach collection="templateNames" item="templateName" index="index" open="(" close=")" separator=",">
            #{templateName}
        </foreach>
        AND vtcm.category_id IN
        <foreach collection="categories" item="categoryId" index="index" open="(" close=")" separator=",">
            #{categoryId}
        </foreach>
        and vt.id not in
        <foreach collection="templateArray" item="templateId" index="index" open="(" close=")" separator=",">
            #{templateId}
        </foreach>
    </select>

    <delete id="deleteCategoryMapByTemplate">
        delete from visualization_template_category_map tcm
        <where>
            <if test="templateName">
                tcm.template_id in (
                select id from visualization_template vt where vt.name = #{templateName})
            </if>

            <if test="templateId">
                and tcm.template_id = #{templateId}
            </if>
        </where>
    </delete>

    <select id="findTemplateCategories" resultType="String">
        select category_id
        from visualization_template_category_map
        where template_id = #{templateId}
    </select>

    <select id="findTemplateArrayCategories" resultType="String">
        SELECT DISTINCT
        GROUP_CONCAT( DISTINCT vtcm.category_id ORDER BY vtcm.category_id ) AS selected_subjects
        FROM
        visualization_template_category_map vtcm
        WHERE
        vtcm.template_id in
        <foreach collection="templateArray" item="templateId" index="index" open="(" close=")" separator=",">
            #{templateId}
        </foreach>
        GROUP BY
        vtcm.template_id
    </select>
    <select id="findAppViewInfo" resultType="io.gisbi.api.visualization.vo.AppCoreChartViewVO">
        SELECT
            core_chart_view.*
        FROM
            core_chart_view
        WHERE
            core_chart_view.id in
        <foreach collection="viewIds" item="viewId" index="index" open="(" close=")" separator=",">
            #{viewId}
        </foreach>
    </select>
    <select id="findAppDatasetGroupInfo" resultType="io.gisbi.api.visualization.vo.AppCoreDatasetGroupVO">
        SELECT
        DISTINCT core_dataset_group.*
        FROM
        	core_dataset_group
        WHERE
        	core_dataset_group.id in
        <foreach collection="dsIds" item="dsId" index="index" open="(" close=")" separator=",">
            #{dsId}
        </foreach>
    </select>
    <select id="findAppDatasetTableInfo" resultType="io.gisbi.api.visualization.vo.AppCoreDatasetTableVO">
        SELECT
        DISTINCT core_dataset_table.*
        FROM
        	core_dataset_table
        WHERE
         	core_dataset_table.dataset_group_id in
         <foreach collection="dsIds" item="dsId" index="index" open="(" close=")" separator=",">
             #{dsId}
         </foreach>
    </select>
    <select id="findAppDatasetTableFieldInfo" resultType="io.gisbi.api.visualization.vo.AppCoreDatasetTableFieldVO">
        SELECT DISTINCT
        	core_dataset_table_field.*
        FROM
        	core_dataset_table_field
        WHERE
        	core_dataset_table_field.dataset_group_id in
         <foreach collection="dsIds" item="dsId" index="index" open="(" close=")" separator=",">
             #{dsId}
         </foreach>
    </select>
    <select id="findAppDatasourceInfo" resultType="io.gisbi.api.visualization.vo.AppCoreDatasourceVO">
        SELECT
        DISTINCT core_datasource.*
        FROM core_datasource inner join
        	core_dataset_table on core_dataset_table.datasource_id = core_datasource.id
        WHERE
         	core_dataset_table.dataset_group_id in
         <foreach collection="dsIds" item="dsId" index="index" open="(" close=")" separator=",">
             #{dsId}
         </foreach>
    </select>
    <select id="findAppDatasourceTaskInfo" resultType="io.gisbi.api.visualization.vo.AppCoreDatasourceTaskVO">
        SELECT
        	core_datasource_task.*
        FROM
        	core_datasource_task
        	INNER JOIN core_datasource ON core_datasource_task.ds_id = core_datasource.id
        	INNER JOIN core_dataset_table ON core_dataset_table.datasource_id = core_datasource.id
        WHERE
         	core_dataset_table.dataset_group_id in
         <foreach collection="dsIds" item="dsId" index="index" open="(" close=")" separator=",">
             #{dsId}
         </foreach>
    </select>

    <select id="findAppLinkageInfo" resultType="io.gisbi.api.visualization.vo.VisualizationLinkageVO">
        SELECT
            visualization_linkage.*
        FROM
            visualization_linkage
        WHERE
            visualization_linkage.dv_id = #{dvId}
    </select>
    <select id="findAppLinkageFieldInfo" resultType="io.gisbi.api.visualization.vo.VisualizationLinkageFieldVO">
        SELECT
        	visualization_linkage_field.*
        FROM
        	visualization_linkage_field
        	INNER JOIN visualization_linkage ON visualization_linkage.id = visualization_linkage_field.linkage_id
        WHERE
        	visualization_linkage.dv_id = #{dvId}
    </select>

    <select id="findAppLinkJumpInfo" resultType="io.gisbi.api.visualization.vo.VisualizationLinkJumpVO">
        SELECT
        	visualization_link_jump.*
        FROM
        	visualization_link_jump
        WHERE
        	visualization_link_jump.source_dv_id = #{dvId}
    </select>

    <select id="findAppLinkJumpInfoInfo" resultType="io.gisbi.api.visualization.vo.VisualizationLinkJumpInfoVO">
        SELECT
        	visualization_link_jump_info.*
        FROM
        	visualization_link_jump_info
        	INNER JOIN visualization_link_jump ON visualization_link_jump.id = visualization_link_jump_info.link_jump_id
        WHERE
        	visualization_link_jump.source_dv_id = #{dvId}
    </select>
    <select id="findAppLinkJumpTargetViewInfoInfo" resultType="io.gisbi.api.visualization.vo.VisualizationLinkJumpTargetViewInfoVO">
        SELECT
        	visualization_link_jump_target_view_info.*
        FROM
        	visualization_link_jump_target_view_info
        	INNER JOIN visualization_link_jump_info ON visualization_link_jump_target_view_info.link_jump_info_id = visualization_link_jump_info.id
        	INNER JOIN visualization_link_jump ON visualization_link_jump.id = visualization_link_jump_info.link_jump_id
        WHERE
        	visualization_link_jump.source_dv_id = #{dvId}
    </select>
</mapper>
