<?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.thinkgem.jeesite.modules.cms.dao.CategoryDao">
    
	<sql id="cmsCategoryDaoColumns">
		a.id AS "id",
		a.parent_id AS "parent.id",
		a.parent_ids AS "parentIds",
		a.site_id AS "site.id",
		a.office_id AS "office.id",
		a.module AS "module",
		a.name AS "name",
		a.image AS "image",
		a.href AS "href",
		a.target AS "target",
		a.description AS "description",
		a.keywords AS "keywords",
		a.sort AS "sort",
		a.in_menu AS "inMenu",
		a.in_list AS "inList",
		a.show_modes AS "showModes",
		a.allow_comment AS "allowComment",
		a.is_audit AS "isAudit",
		a.custom_list_view AS "customListView",
		a.custom_content_view AS "customContentView",
		a.view_config AS "viewConfig",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		c.name AS "parent.name",
		c.view_config AS "parent.viewConfig",
		o.name AS "office.name",
		s.theme AS "site.theme"
	</sql>
	
	<sql id="cmsCategoryDaoJoins">
	    LEFT JOIN cms_category c ON c.id = a.parent_id
		JOIN sys_office o ON o.id = a.office_id
		JOIN sys_user u ON u.id = a.create_by
		JOIN cms_site s ON a.site_id = s.id
	</sql>
    
	<select id="get" resultType="Category">
		SELECT 
			a.id AS "id",
			a.parent_id AS "parent.id",
			a.parent_ids AS "parentIds",
			a.site_id AS "site.id",
			a.office_id AS "office.id",
			a.module AS "module",
			a.name AS "name",
			a.image AS "image",
			a.href AS "href",
			a.target AS "target",
			a.description AS "description",
			a.keywords AS "keywords",
			a.sort AS "sort",
			a.in_menu AS "inMenu",
			a.in_menu AS "inList",
			a.show_modes AS "showModes",
			a.allow_comment AS "allowComment",
			a.is_audit AS "isAudit",
			a.custom_list_view AS "customListView",
			a.custom_content_view AS "customContentView",
			a.view_config AS "viewConfig",
			a.create_by AS "createBy.id",
			a.create_date AS "createDate",
			a.update_by AS "updateBy.id",
			a.update_date AS "updateDate",
			a.remarks AS "remarks",
			a.del_flag AS "delFlag",
			o.name AS "office.name"
		FROM cms_category a
		JOIN sys_office o ON o.id = a.office_id
		JOIN sys_user u ON u.id = a.create_by
		WHERE a.id = #{id}
	</select>
	
	<select id="find" resultType="Category">
		SELECT 
			<include refid="cmsCategoryDaoColumns"/>
		FROM cms_category a
		<include refid="cmsCategoryDaoJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findList" resultType="Category">
		SELECT 
			<include refid="cmsCategoryDaoColumns"/>
		FROM cms_category a
		<include refid="cmsCategoryDaoJoins"/>
		<where>
			a.del_flag = #{delFlag}
			<if test=" site.id != null and site.id != ''">
				AND a.site_id  = #{site.id}
			</if>
			<if test="parent.id != null and parent.id != ''">
				AND a.parent_id  = #{parent.id}
			</if>
			${sqlMap.dsf}
		</where>		
			ORDER BY a.site_id,a.sort ASC
	</select>
	
	<select id="findModule" resultType="Category">
		SELECT 
			<include refid="cmsCategoryDaoColumns"/>
		FROM cms_category a
		<include refid="cmsCategoryDaoJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test=" site.id != null and site.id != ''">
				AND a.site_id  = #{site.id}
			</if>
			<if test="parent.id != null and parent.id != ''">
				AND a.parent_id  = #{parent.id}
			</if>
			<if test="inMenu != null and inMenu != '' ">
				AND a.in_menu  = #{inMenu}
			</if>
			${sqlMap.dsf}
		</where>		
			ORDER BY a.site_id,a.sort ASC
	</select>
	
	<insert id="insert">
		INSERT INTO cms_category(
			id,
			parent_id,
			parent_ids,
			site_id,
			office_id,
			module,
			name,
			image,
			href,
			target,
			description,
			keywords,
			sort,
			in_menu,
			in_list,
			show_modes,
			allow_comment,
			is_audit,
			custom_list_view,
			custom_content_view,
			view_config,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{parent.id},
			#{parentIds},
			#{site.id},
			#{office.id},
			#{module},
			#{name},
			#{image},
			#{href},
			#{target},
			#{description},
			#{keywords},
			#{sort},
			#{inMenu},
			#{inList},
			#{showModes},
			#{allowComment},
			#{isAudit},
			#{customListView},
			#{customContentView},
			#{viewConfig},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE cms_category SET 	
			
			parent_id = #{parent.id},
			parent_ids = #{parentIds},
			site_id = #{site.id},
			office_id = #{office.id},
			module = #{module},
			name = #{name},
			image = #{image},
			href = #{href},
			target = #{target},
			description = #{description},
			keywords = #{keywords},
			sort = #{sort},
			in_menu = #{inMenu},
			in_list = #{inList},
			show_modes = #{showModes},
			allow_comment = #{allowComment},
			is_audit = #{isAudit},
			custom_list_view = #{customListView},
			custom_content_view = #{customContentView},
			view_config = #{viewConfig},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			del_flag = #{delFlag}
		WHERE id = #{id}
	</update>
	
	<update id="updateParentIds">
		UPDATE cms_category SET 
			parent_id = #{parent.id}, 
			parent_ids = #{parentIds}
		WHERE id = #{id}
	</update>
	
	<update id="delete">
		UPDATE cms_category SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	<select id="findByParentIdAndSiteId" resultType="Category">
		SELECT 
			<include refid="cmsCategoryDaoColumns"/>
		FROM cms_category a
		<include refid="cmsCategoryDaoJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} 
			<if test=" site.id != null and site.id != ''">
				AND a.site_id  = #{site.id}
			</if>
			<if test="parent.id != null and parent.id != ''">
				AND a.parent_id  = #{parent.id}
			</if>
		</where>		
		order by a.site_id, a.sort
	</select>
	<select id="findByParentIdsLike" resultType="Category">
		SELECT 
			<include refid="cmsCategoryDaoColumns"/>
		FROM cms_category a
		<include refid="cmsCategoryDaoJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} 
			AND a.parent_id LIKE 
					<if test="dbName == 'oracle'">'%'||#{id}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT('%', #{id}, '%')</if>
		</where>		
		order by a.site_id, a.sort
	</select>
	
	<select id="findStats" resultType="java.util.Map" parameterType="java.util.Map">
		select max(c.id) as categoryId,
		       max(c.name) as categoryName,
		       max(cp.id) as categoryParentId,
		       max(cp.name) as categoryParentName,
		       count(*) as cnt,
		       sum(a.hits) as hits,
		       max(a.updateDate) as updateDate,
		       max(o.id) as officeId,
		       max(o.name) as officeName,
		  from cms_article a
		  JOIN cms_category c ON c.id = a.category_id
		  JOIN cms_category cp ON c.parent_id = cp.id
		  JOIN sys_office o ON o.id = c.office_id
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} 
			AND a.category_id
			AND c.site_id =
			AND c.id = :id or c.parent_ids LIKE 
					<if test="dbName == 'oracle'">'%'||#{parentIds}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT('%', #{parentIds}, '%')</if>
			group by cp.sort, cp.id, c.sort, c.id
			order by cp.sort, cp.id, c.sort, c.id
		</where>		
	</select>
</mapper>