<?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="com.rongwei.bscommon.sys.dao.KbKnowledgebaseSearchKeywordsDao">
    <select id="getSearchBasicInfo" resultType="com.rongwei.bsentity.vo.KnowledgeBaseVo">
        SELECT
        ksc.FROMID as pagePartId,
        ksc.MENUID as menuId,
        ksc.MENU as menuName,
        ksc.PID as menuPid,
        ksd.TABLENAME as tableName,
        ksd.TABLECOLUMNNANE as columnName,
        ksd.TABLECOLUMNTYPE as columnType,
        ksd.LABEL as columnDesc,
        ksd.CONTROLTYPE as controlType,
        ksd.ENUMTYPE as enumType,
        ksd.DATAORIGIN as enumContent,
        ksc.TABLESQL as tablesql
        FROM
        kb_subsystem_config ksc
        LEFT JOIN kb_subsystem_detail ksd on ksc.id= ksd.MASTERID
        <where>
            ksc.DELETED='0'
            and ksd.DELETED='0'
            <if test="systemId != 'all'">
                and ksc.PID= #{systemId}
            </if>
        </where>
    </select>
    <select id="getSelectInfo" resultType="com.rongwei.bsentity.vo.KBDictDataVo">
        <foreach collection="datas" separator="union all" item="data" >
            <if test="data.enumType=='dict'">
                select name as label ,value as value,concat(#{data.tableName},"-;-",#{data.columnName}) as dictKey from sys_dict where DELETED='0' and PID != -1 AND DICTTYPE=#{data.enumContent}
            </if>
            <if test="data.enumType=='sql'">
                select label,value,concat(#{data.tableName},"-;-",#{data.columnName}) as dictKey from (${data.enumContent}) a
            </if>
        </foreach>
    </select>
    <sql id="kbSQL">
        SELECT
        kkl.ID AS dataId,
        sm.ID AS menuId,
        sm.NAME AS menuName,
        sm.PID AS systemId,
        '07f7a17323be40bb96c292b80631a5a2' AS pagePartId,
        kkl.CREATEDATE AS createDate,
        '' AS systemName,
        CONCAT( "存放目录: ", ifnull(kkl.REMARK,""), "-@-",
            "标题: ",ifnull(kkl.TITLE,""), "-@-",
            "创建人: ", ifnull(kkl.CREATEUSERNAME,""),"-@-",
            "附件: ", ifnull(kkl.ANNEX,"") ) AS
        searchResult
        FROM
        kb_knowledge_ledger kkl
        INNER JOIN sys_module sm ON sm.ID = 'b03686d4b8b54b96a6d310be8bd925a4'
        WHERE
        kkl.DELETED = '0'
        <bind name="pattern" value="'%' + searchContent + '%'"/>
        and kkl.TITLE like #{pattern}
    </sql>
    <select id="getKbData" resultType="com.rongwei.bsentity.vo.KBResultVo">
        select * from (
        <choose>
            <when test="systemId=='4149efeef04c4e08972dd5516a2dc16f'">
                <include refid="kbSQL"></include>
            </when>
            <otherwise>
                <foreach collection="searchVos" item="searchVo" separator="union all">
                    select
                    ID as dataId,
                    #{searchVo.menuId} AS menuId,
                    #{searchVo.menuName} AS menuName,
                    #{searchVo.menuPid} AS systemId,
                    #{searchVo.pagePartId} as pagePartId,
                    a.CREATEDATE as createDate,
                    '' AS systemName,
                    <foreach collection="searchVo.columnInfoList" item="columnInfo" separator=', "-@-",' open="CONCAT("
                             close=")">
                        <choose>
                            <when test="columnInfo.dictData != null and columnInfo.dictData.size() > 0">
                                #{columnInfo.columnDesc},": ",
                                ifnull(
                                <foreach collection="columnInfo.dictData.keys" item="key">
                                    REPLACE(
                                </foreach>
                                a.${columnInfo.columnName}
                                <foreach collection="columnInfo.dictData.entrySet()" index="key" item="val" separator=")" close=")" >
                                    ,#{key},#{val}
                                </foreach>
                                    ,"")
                            </when>
                            <otherwise>
                                #{columnInfo.columnDesc},": ",ifnull(a.${columnInfo.columnName},"")
                            </otherwise>
                        </choose>
                    </foreach>
                    as searchResult
                    from (${searchVo.tableSql}) a
                    <where>
                        AND DELETED ='0'
                        AND
                        <foreach collection="searchVo.columnInfoList" item="columnInfo" separator=' or ' open="("
                                 close=")">
                            <choose>
                                <when test="columnInfo.columnType=='DATETIME'">
                                    CAST(a.${columnInfo.columnName} AS CHAR) LIKE "%"#{columnInfo.searchValue}"%"
                                </when>
                                <otherwise>
                                    a.${columnInfo.columnName} LIKE "%"#{columnInfo.searchValue}"%"
                                </otherwise>
                            </choose>

                        </foreach>
                    </where>
                </foreach>
                <if test="systemId=='all'">
                    union all
                    <include refid="kbSQL"></include>
                </if>
            </otherwise>
        </choose>
        )b where b.searchResult is not null order by b.createDate ${orderRules} limit #{pageNum},#{pageSize}
    </select>
    <select id="getKbDataLength" resultType="java.lang.Integer">
        select COUNT(*) from (
        <choose>
            <when test="systemId=='4149efeef04c4e08972dd5516a2dc16f'">
                <include refid="kbSQL"></include>
            </when>
            <otherwise>
                <foreach collection="searchVos" item="searchVo" separator="union all">
                    select
                    ID as dataId
                    from (${searchVo.tableSql}) a
                    <where>
                        AND DELETED ='0'
                        AND
                        <foreach collection="searchVo.columnInfoList" item="columnInfo" separator=' or ' open="("
                                 close=")">
                            <bind name="pattern" value="'%' + columnInfo.searchValue + '%'"/>
                            <choose>
                                <when test="columnInfo.columnType=='DATETIME'">
                                    CAST(a.${columnInfo.columnName}  AS CHAR) LIKE #{pattern}
                                </when>
                                <otherwise>
                                    a.${columnInfo.columnName} LIKE #{pattern}
                                </otherwise>
                            </choose>

                        </foreach>
                    </where>
                </foreach>
                <if test="systemId=='all'">
                    union all
                    SELECT
                    kkl.ID AS dataId
                    FROM
                    kb_knowledge_ledger kkl
                    INNER JOIN sys_module sm ON sm.ID = 'b03686d4b8b54b96a6d310be8bd925a4'
                    WHERE
                    kkl.DELETED = '0'
                    <bind name="pattern" value="'%' + searchContent + '%'"/>
                    and kkl.TITLE like #{pattern}
                </if>
            </otherwise>
        </choose>
        )b where b.dataId is not null
    </select>
</mapper>
