<?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.victorysoft.business.analyze.dao.CustomSearchDao">
  <select id="selectDataSourceById" parameterType="String" resultType="CstDataSource">
    select id, name, table_name, value_column, label_column, order_column, filter_sql
    from cst_data_source
    where id = #{value}
  </select>

  <select id="selectDictData" parameterType="CstDataSource" resultType="CstDictData">
    select ${dataSource.valueColumn} as "value",
      ${dataSource.labelColumn} as label
    from ${dataSource.tableName}
    where 1 = 1
    <if test="searchText != null and searchText != ''">
      and ${dataSource.labelColumn} like '%'||#{searchText}||'%'
    </if>
    <if test="dataSource.filterSql != null and dataSource.filterSql != ''">
      and ${dataSource.filterSql}
    </if>
    <if test="dataSource.orderColumn != null and dataSource.orderColumn != ''">
      order by ${dataSource.orderColumn}
    </if>
  </select>

  <select id="selectTemplates" parameterType="String" resultType="CstTemplate">
    select id, name, unitcode, update_time, use_group, table_type
    from cst_template
    where unitcode = #{value}
    or id in (select template_id from cst_template_share where unitcode = #{value})
    order by name
  </select>

  <select id="selectTemplate" parameterType="String" resultType="CstTemplate">
    select id, name, unitcode, update_time, use_group, table_type
    from cst_template
    where id = #{value}
  </select>

  <delete id="deleteTemplate" parameterType="String">
    delete from cst_template where id = #{value}
  </delete>

  <insert id="insertTemplate" parameterType="CstTemplate">
    insert into cst_template
      (id, name, unitcode, update_time, use_group, table_type)
    values
      (#{id}, #{name}, #{unitcode}, sysdate, #{useGroup}, #{tableType})
  </insert>

  <update id="updateTemplate" parameterType="CstTemplate">
    update cst_template
     set name = #{name},
         unitcode = #{unitcode},
         use_group = #{useGroup},
         table_type = #{tableType},
         update_time = sysdate
    where id = #{id}
  </update>

  <select id="selectTemplateTables" parameterType="String" resultType="CstTemplateTable">
    select ctt.template_id, ctt.table_id, ctt.order_no,
    ct.id "table.id", ct.name "table.name", ct.table_type "table.table_type", ct.unit_condition "table.unit_condition"
    from cst_template_table ctt, cst_table ct
    where ctt.table_id = ct.id
    and ctt.template_id = #{value}
    order by order_no
  </select>

  <delete id="deleteTemplateTables" parameterType="String">
    delete from cst_template_table where template_id = #{value}
  </delete>

  <insert id="insertTemplateTables" parameterType="java.util.List">
    insert into cst_template_table (template_id, table_id, order_no)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT #{item.templateId}, #{item.tableId}, #{item.orderNo}
      FROM dual
    </foreach>
  </insert>

  <select id="selectTemplateIndicators" parameterType="String" resultType="CstTemplateIndicator">
    select cti.id,
           cti.name,
           cti.template_id,
           cti.indicator_id,
           cti.formula,
           cti.formula_text,
           cti.unit,
           cti.order_no,
           cti.group_type,
           ci.id "indicator.id",
           ci.name "indicator.name",
           ci.table_id "indicator.table_id",
           ci.label_column "indicator.label_column",
           ci.data_type_id "indicator.data_type_id",
           ci.formula "indicator.formula",
           ci.unit "indicator.unit",
           ci.data_source_id "indicator.data_source_id",
           ci.value_column "indicator.value_column",
           ci.order_no "indicator.order_no",
           ci.query_type_id "indicator.query_type_id",
           ci.indicator_id "indicator.indicator_id"
      from cst_template_indicator cti, cst_table_indicator ci
     where cti.indicator_id = ci.id(+)
       and cti.template_id = #{value}
     order by cti.order_no
  </select>

  <delete id="deleteTemplateIndicators" parameterType="String">
    delete from cst_template_indicator where template_id = #{value}
  </delete>

  <insert id="insertTemplateIndicators" parameterType="java.util.List">
    insert into cst_template_indicator (template_id, indicator_id, order_no, id, name, formula, formula_text, unit, group_type)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT #{item.templateId}, #{item.indicatorId,jdbcType=VARCHAR}, #{item.orderNo}, #{item.id}, #{item.name,jdbcType=VARCHAR},
       #{item.formula,jdbcType=VARCHAR}, #{item.formulaText,jdbcType=VARCHAR}, #{item.unit,jdbcType=VARCHAR}, #{item.groupType,jdbcType=VARCHAR}
      FROM dual
    </foreach>
  </insert>

  <select id="selectTemplateConditions" parameterType="String" resultType="CstTemplateCondition">
    select ctc.template_id, ctc.indicator_id, ctc.query_type_id, ctc.order_no,
      ci.name indicatorName, cti.data_type_id, cti.data_source_id, ci.id global_indicator_id
    from cst_template_condition ctc, cst_table_indicator cti, cst_indicator ci
    where  ctc.indicator_id = cti.id
    and cti.indicator_id = ci.id
    and template_id = #{value}
    order by ctc.order_no
  </select>

  <select id="selectTemplateCondition" resultType="CstTemplateCondition">
    select ctc.template_id, ctc.indicator_id, ctc.query_type_id, ctc.order_no,
      ci.name indicatorName, cti.data_type_id, cti.data_source_id, ci.id global_indicator_id
    from cst_template_condition ctc, cst_table_indicator cti, cst_indicator ci
    where  ctc.indicator_id = cti.id
    and cti.indicator_id = ci.id
    and template_id = #{templateId}
    and ctc.indicator_id = #{indicatorId}
  </select>

  <delete id="deleteTemplateConditions" parameterType="String">
    delete from cst_template_condition where template_id = #{value}
  </delete>

  <insert id="insertTemplateConditions" parameterType="java.util.List">
    insert into cst_template_condition (template_id, indicator_id, query_type_id, order_no)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT #{item.templateId}, #{item.indicatorId}, #{item.queryTypeId,jdbcType=VARCHAR}, #{item.orderNo}
      FROM dual
    </foreach>
  </insert>

  <select id="selectTemplateShares" parameterType="String" resultType="CstTemplateShare">
    select template_id, unitcode
    from cst_template_share
    where template_id = #{value}
  </select>

  <delete id="deleteTemplateShares" parameterType="String">
    delete from cst_template_share where template_id = #{value}
  </delete>

  <insert id="insertTemplateShares" parameterType="java.util.List">
    insert into cst_template_share  (template_id, unitcode)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT #{item.templateId}, #{item.unitcode}
      FROM dual
    </foreach>
  </insert>

  <select id="selectTables" resultType="CstTable">
    select id, name, table_type, unit_condition
    from cst_table
    order by name
  </select>

  <select id="selectTable" parameterType="String" resultType="CstTable">
    select id, name, table_type, unit_condition
    from cst_table
    where id = #{value}
  </select>

  <select id="selectRelateTables" parameterType="String" resultType="CstTable">
    select id, name, table_type, unit_condition
    from cst_table
    where id in
    (select relation_table_id from cst_table_relation where main_table_id = #{value})
    order by name
  </select>

  <select id="selectTableIndicators" parameterType="String" resultType="CstTableIndicator">
    select id, name, table_id, label_column, data_type_id, formula, unit, data_source_id, value_column,
    order_no, query_type_id, default_indicator, indicator_id
    from cst_table_indicator
    where table_id = #{value}
    order by order_no
  </select>

  <select id="selectTableIndicator" parameterType="String" resultType="CstTableIndicator">
    select id, name, table_id, label_column, data_type_id, formula, unit, data_source_id, value_column,
    order_no, query_type_id, default_indicator, indicator_id
    from cst_table_indicator
    where id = #{value}
  </select>

  <select id="selectQueryTypes" resultType="CstQueryType">
    select cdt.id data_type_id, cqt.id, cqt.name, cdtqt.order_no
      from cst_data_type            cdt,
           cst_data_type_query_type cdtqt,
           cst_query_type           cqt
    where cdt.id = cdtqt.data_type_id
      and cqt.id = cdtqt.query_type_id
    order by cdt.id, cdtqt.order_no
  </select>

  <select id="selectTableRelations" parameterType="java.util.List" resultType="CstTableRelation">
    select main_table_id, relation_table_id, main_table_column, relation_table_column
      from cst_table_relation
    where main_table_id in
    <foreach collection="list" item = "code" open="(" separator="," close=")" index="index">
      #{code}
    </foreach>
    and relation_table_id in
    <foreach collection="list" item = "code" open="(" separator="," close=")" index="index">
      #{code}
    </foreach>
  </select>

  <select id="search" parameterType="String" resultType="Map">
    ${value}
  </select>

  <select id="selectIndicators" resultType="CstIndicator">
    select distinct ci.*
    from cst_table_indicator cti, cst_table ct, cst_indicator ci
    where cti.table_id = ct.id and cti.indicator_id = ci.id
    and (ct.table_type = #{tableType} or ct.table_type = 'STATIC')
    <if test="searchField != null and searchField != ''">
      and ci.name like '%'||#{searchField}||'%'
    </if>
    order by
    <if test="searchField != null and searchField != ''">
      length(replace(ci.name, #{searchField}, '')) nulls first,
    </if>
    nlssort(ci.name, 'NLS_SORT=SCHINESE_PINYIN_M')
  </select>

  <select id="selectByIndicators" resultType="CstTableIndicator">
    select cti.id, ci.name, table_id, label_column, data_type_id, formula, ci.unit, data_source_id,
    value_column, order_no, query_type_id, default_indicator, indicator_id, ct.name table_name
    from cst_table_indicator cti, cst_table ct, cst_indicator ci
    where cti.table_id = ct.id and cti.indicator_id = ci.id
    and (ct.table_type = #{tableType} or ct.table_type = 'STATIC')
    and ci.id in
    <foreach collection="indicators" item = "item" open="(" separator="," close=")" index="index">
      #{item.id}
    </foreach>
    order by nlssort(ci.name, 'NLS_SORT=SCHINESE_PINYIN_M'), cti.default_indicator desc nulls last
  </select>
</mapper>
