<?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.dgtis.dorm.manager.db.dao.AdministrativeItemsMapper">

    <sql id="Base_Column_List">
         ai.id,
         ai. NAME,
         ai.parent_id,
         ai.remark,
         ai.icon_storage_id,
         ai.pdf_storage_id,
         ai.table_storage_id,
         ai.qrcode_storage_id,
         ai. LEVEL,
         ai.create_date,
         ai.create_user,
         ai.update_date,
         ai.update_user,
         ai.deflag
    </sql>

    <select id="selectList" parameterType="com.dgtis.dorm.manager.db.param.ItemsQueryParam" resultType="com.dgtis.dorm.manager.db.domain.AdministrativeItems">
        select
        <include refid="Base_Column_List"/>,par.name as parentName,
        icon.old_name as iconStorageOldName,icon.url as iconStorageUrl,
        qrcode.old_name as qrcodeStorageOldName,qrcode.url as qrcodeStorageUrl,

        (select count(guidanceDoc.id) from zhll_items_doc guidanceDoc
        where guidanceDoc.items_id=ai.id and guidanceDoc.type=1) as itemsGuidanceDocCount,

        (select count(tableDoc.id) from zhll_items_doc tableDoc
        where tableDoc.items_id=ai.id and tableDoc.type=2) as itemsTableDocCount

        from zhll_items ai
        left join zhll_items par on par.id=ai.parent_id
        LEFT JOIN zhll_storage icon on icon.key=ai.icon_storage_id
        LEFT JOIN zhll_storage qrcode ON qrcode.KEY = ai.qrcode_storage_id
        WHERE ai.deflag = 0
        <if test="name != null and name != ''">
            and ai.`name` like concat('%',concat(#{name},'%'))
        </if>
        <if test="parentId != null">
            and ai.parent_id = #{parentId}
        </if>
        <if test="level != null">
            and ai.`level` = #{level}
        </if>
        <if test="id != null">
            and ai.id = #{id}
        </if>
        <choose>
            <when test="sort != null and sort.trim() != ''">
                ORDER BY ai.${sort} ${order}
            </when>
            <otherwise>
                ORDER BY ai.level asc,par.name asc,ai.name asc
            </otherwise>
        </choose>
        <if test="offset != null and limit !=null ">
            limit #{offset},#{limit}
        </if>
    </select>

    <select id="count" parameterType="com.dgtis.dorm.manager.db.param.ItemsQueryParam" resultType="java.lang.Long">
        select
        count(ai.id)
        from zhll_items ai
        WHERE ai.deflag = 0
        <if test="name != null and name != ''">
            and ai.`name` like concat('%',concat(#{name},'%'))
        </if>
        <if test="parentId != null">
            and ai.parent_id = #{parentId}
        </if>
        <if test="level != null">
            and ai.`level` = #{level}
        </if>
        <if test="id != null">
            and ai.id = #{id}
        </if>
    </select>


    <select id="selectByPrimaryKey" resultType="com.dgtis.dorm.manager.db.domain.AdministrativeItems">
        select
        <include refid="Base_Column_List"/>,par.name as parentName,
        icon.old_name as iconStorageOldName,icon.url as iconStorageUrl
        from zhll_items ai
        left join zhll_items par on par.id=ai.parent_id
        LEFT JOIN zhll_storage icon on icon.key=ai.icon_storage_id
        where ai.id=#{id}
    </select>


    <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.dgtis.dorm.manager.db.domain.AdministrativeItems">
        insert into zhll_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="parentId != null">
                parent_id,
            </if>
            <if test="remark != null">
                `remark`,
            </if>
            <if test="iconStorageId != null">
                icon_storage_id,
            </if>
            <if test="pdfStorageId != null">
                pdf_storage_id,
            </if>
            <if test="tableStorageId != null">
                table_storage_id,
            </if>
              <if test="qrcodeStorageId != null">
                  qrcode_storage_id,
            </if>
            <if test="level != null">
                level,
            </if>
            <if test="createDate != null">
                create_date,
            </if>
            <if test="createUser != null">
                create_user,
            </if>
            <if test="deflag != null">
                deflag,
            </if>
            <if test="nameSpell != null">
                name_spell
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="parentId != null">
                #{parentId},
            </if>
            <if test="remark != null">
                #{remark},
            </if>
            <if test="iconStorageId != null">
                #{iconStorageId},
            </if>
            <if test="pdfStorageId != null">
                #{pdfStorageId},
            </if>
            <if test="tableStorageId != null">
                #{tableStorageId},
            </if>
            <if test="qrcodeStorageId != null">
                #{qrcodeStorageId},
            </if>
            <if test="level != null">
                #{level},
            </if>
            <if test="createDate != null">
                #{createDate},
            </if>
            <if test="createUser != null">
                #{createUser},
            </if>
            <if test="deflag != null">
                #{deflag},
            </if>
            <if test="nameSpell != null">
                #{nameSpell}
            </if>
        </trim>
    </insert>

    <update id="update" parameterType="com.dgtis.dorm.manager.db.domain.AdministrativeItems">
        update zhll_items
        <set>
            <if test="name != null">
                name=#{name},
            </if>
            <if test="parentId != null">
                parent_id=#{parentId},
            </if>
            <if test="remark != null">
                remark=#{remark},
            </if>
            <if test="iconStorageId != null">
                icon_storage_id=#{iconStorageId},
            </if>
            <if test="pdfStorageId != null">
                pdf_storage_id=#{pdfStorageId},
            </if>
            <if test="tableStorageId != null">
                table_storage_id=#{tableStorageId},
            </if>
             <if test="qrcodeStorageId != null">
                 qrcode_storage_id=#{qrcodeStorageId},
            </if>
            <if test="level != null">
                level=#{level},
            </if>
            <if test="createDate != null">
                create_date=#{createDate},
            </if>
            <if test="createUser != null">
                create_user=#{createUser},
            </if>
            <if test="deflag != null">
                deflag=#{deflag},
            </if>
            <if test="nameSpell != null">
                name_spell=#{nameSpell},
            </if>
        </set>
        where id = #{id}
    </update>

    <update id="deleteByIds">
        update zhll_items
        set deflag=1
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </update>

    <select id="selectByIds" resultType="com.dgtis.dorm.manager.db.domain.AdministrativeItems">
        select
        <include refid="Base_Column_List"/>,par.name as parentName,
        icon.old_name as iconStorageOldName,icon.url as iconStorageUrl
        from zhll_items ai
        left join zhll_items par on par.id=ai.parent_id
        LEFT JOIN zhll_storage icon on icon.key=ai.icon_storage_id
        where ai.id in
        <foreach collection="ids" separator="," open="(" close=")" item="id">
              #{id}
        </foreach>
    </select>

    <select id="selectQrcodeStorageUrlByNames" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT  zs.url from  zhll_storage zs LEFT JOIN zhll_items s on zs.`key` = s.qrcode_storage_id
	    WHERE s.deflag = 0
		and s.parent_id in (
		select id from zhll_items where deflag = 0 and `name_spell` LIKE concat('%',#{oneTitlePY},'%'))
		and s.`name_spell` LIKE concat('%',#{twoTitlePY},'%')
    </select>
</mapper>