<?xml version="1.0" encoding="UTF-8"?>
<sqls>
	<!-- ***************** 新增部分****************  -->
	<!-- 根节点添加 -->
		<select id="addzero">		
	INSERT INTO t_core_compliance_directory (
    id,
	edition_id,
	fa_tid,
	directory_id,
	directory_name,
	chinese_name,
	foreigh_name,
	abbreviation,
	public_name,
	public_date,
	implementation_date,
	revised_date,
	application_state,
	directory_type,
	create_user,
	create_date,
	note,
	projectid
    )
    VALUES
	(
	   :id,
		:edition_id,
		'全部',
	    '001',
		:text,
		:chinese_name,
		:foreigh_name,
		:abbreviation,
		:public_name,
		:public_date,
		:implementation_date,
		:revised_date,
		:application_state,
		:directory_type,
		:create_user,
		current_timestamp(),
		:note,
		:projectid
	)	
	</select>
	
	
	
	
	<select id="addfa">		
    INSERT INTO t_core_compliance_directory (
    id,
	edition_id,
	fa_tid,
	directory_id,
	directory_name,
	chinese_name,
	foreigh_name,
	abbreviation,
	public_name,
	public_date,
	implementation_date,
	revised_date,
	application_state,
	directory_type,
	create_user,
	create_date,
	note,
	projectid
    )
    VALUES
	(
	   :id,
		:edition_id,
		'全部',
		(
			SELECT
				LPAD(MAX(directory_id) + 1, 3, 0)
			FROM
				t_core_compliance_directory a
			WHERE
				fa_tid = '全部'
		),
		:text,
		:chinese_name,
		:foreigh_name,
		:abbreviation,
		:public_name,
		:public_date,
		:implementation_date,
		:revised_date,
		:application_state,
		:directory_type,
		:create_user,
		current_timestamp(),
		:note,
		:projectid
	)	
    </select>
	
	 <!-- 判断有没有兄弟 -->
	<select id="ishavebro">		
	SELECT
		id
	FROM
		t_core_compliance_directory
	WHERE
		fa_tid = :fa_tid
	</select>		

	<!-- 添加  有兄弟，父id+获取兄弟id最大值，取最后三位+1-->
	<select id="havebro">		
	INSERT INTO t_core_compliance_directory (
		id,
		edition_id,
		fa_tid,
		directory_id,
		directory_name,
		chinese_name,
		foreigh_name,
		abbreviation,
		public_name,
		public_date,
		implementation_date,
		revised_date,
		application_state,
		directory_type,
		create_user,
	    create_date,
	    note,
	    projectid
	)
	VALUES
		(
			:id,
			:edition_id,
			:fa_tid,
			( SELECT MAX(b.num)FROM (SELECT
				LPAD(	MAX(directory_id) + 1,CHARACTER_LENGTH(directory_id),0) as num
				FROM
				t_core_compliance_directory a
				WHERE
				fa_tid = :fa_tid
				AND
				edition_id = :edition_id
				GROUP BY a.directory_id) b
			),
			:text,
			:chinese_name,
			:foreigh_name,
			:abbreviation,
			:public_name,
			:public_date,
			:implementation_date,
			:revised_date,
			:application_state,
			:directory_type,
			:create_user,
			current_timestamp(),
		    :note,
		    :projectid
		)	
			
	</select>	
	
	
	<!-- 添加  无兄弟 父id+001 本身id-->
	<select id="nothavebro">		
	INSERT INTO t_core_compliance_directory (
		id,
		edition_id,
		fa_tid,
		directory_id,
		directory_name,
		chinese_name,
		foreigh_name,
		abbreviation,
		public_name,
		public_date,
		implementation_date,
		revised_date,
		application_state,
		directory_type,
		create_user,
		create_date,
	    note,
	    projectid
	)
	VALUES
		(
			:id,
			:edition_id,
			:fa_tid,
			(
				SELECT
					CONCAT(directory_id, '001')
				FROM
					t_core_compliance_directory a
				WHERE
				tid = :fa_tid
			),
			:text,
			:chinese_name,
			:foreigh_name,
			:abbreviation,
			:public_name,
			:public_date,
			:implementation_date,
			:revised_date,
			:application_state,
			:directory_type,
			:create_user,
			current_timestamp(),
		    :note,
		    :projectid
		)
			
	</select>	



	<!-- ***************** 删除部分**************** -->
	
	<!-- 删除本身及子-->
	<select id="delselfandson">		
	DELETE
	FROM
		t_core_compliance_directory
	WHERE
		directory_id LIKE (CONCAT(:directory_id, '%'))
	</select>	



	
	<!-- 查询本身及子-->
	<select id="getselfandson">		
	SELECT 
	ad.id as id,
	cd.id as attachment_id,
	ad.attachment_name as attachment_name,
	ad.attachment_form as attachment_form
	FROM
		t_core_compliance_directory cd,t_core_attachment_directory ad
	WHERE
		cd.id = ad.attachment_id
		and
		cd.directory_id LIKE (CONCAT(:directory_id, '%'))
	</select>	




	<!-- 查找所有大于自己的兄弟及其子-->
	<select id="findbroandhisson">		
	SELECT
		id,
		fa_tid,
		directory_id as id,
		directory_name	 as text
	FROM
		t_core_compliance_directory
	WHERE
		directory_id LIKE (CONCAT(:fa_id, '%'))
	AND substring(
		directory_id,
		1,
		CHARACTER_LENGTH(:directory_id)
	) > :directory_id
	</select>

	<!-- 如果父id与其相同 更新-->
	<select id="updatebro">		
	UPDATE t_core_compliance_directory
	SET directory_id = LPAD(
		(directory_id - 1),
		CHARACTER_LENGTH(directory_id),
		0
	)
	WHERE
		id = :id
	</select>	
	
	
	
	
    <!-- 如果父id与其不相同 更新-->
	<select id="updatebroson">		
	UPDATE t_core_compliance_directory
	SET directory_id = LPAD(
		CONCAT(
			(
				substring(
					directory_id,
					1,
					CHARACTER_LENGTH(:del_id)
				) - 1
			),
			substring(
				directory_id,
				CHARACTER_LENGTH(:del_id) + 1,
				CHARACTER_LENGTH(directory_id)
			)
		),
		CHARACTER_LENGTH(directory_id),
		0
	),
	 fa_tid = LPAD(
		CONCAT(
			(
				substring(
					fa_tid,
					1,
					CHARACTER_LENGTH(:del_id)
				) - 1
			),
			substring(
				fa_tid,
				CHARACTER_LENGTH(:del_id) + 1,
				CHARACTER_LENGTH(fa_tid)
			)
		),
		CHARACTER_LENGTH(fa_tid),
		0
	)
	WHERE
		id= :id
	</select>


	<!-- 分页查询powercenter数据生成规则 -->
 	<select id="dataScopeGroup">		
		SELECT
		  id,
		  directory_id as id,
		  directory_name as text,
		  fa_tid,
		chinese_name,
		foreigh_name,
		abbreviation,
		public_name,
		public_date,
		implementation_date,
		revised_date,
		application_state,
		directory_type,
          create_user,
          create_date,
          note
		FROM
		  t_core_compliance_directory
		 WHERE
		 edition_id = :edition_id
		 ORDER BY directory_id
	</select>

	<select id="dataScopeGroupByCondition">
		SELECT
		id,
		directory_id as id,
		directory_name as text,
		fa_tid,
		chinese_name,
		foreigh_name,
		abbreviation,
		public_name,
		public_date,
		implementation_date,
		revised_date,
		application_state,
		directory_type,
		create_user,
		create_date,
		note
		FROM
		t_core_compliance_directory
		WHERE
		edition_id = :edition_id
		and
		:condition like (CONCAT(%,:searchVal, '%'))
		ORDER BY directory_id
	</select>
	
<!-- 	zhladd api用 -->
	<select id="complianceDirectory">		
		SELECT
		  id as id,
		  directory_id as tid,
		  directory_name as text,
		  fa_tid,
		  edition_id as edition_id,
		  '5' as type
		FROM
		  t_core_compliance_directory
		 WHERE
		 edition_id = :edition_id
		 ORDER BY directory_id
	</select>
	
	
	<!-- 更新操作 -->
	<select id="updatescope">  
		  	UPDATE t_core_compliance_directory
			SET directory_name = :text,
			chinese_name =:chinese_name,
			foreigh_name=:foreigh_name,
			abbreviation=:abbreviation,
			public_name=:public_name,
			public_date=:public_date,
			implementation_date=:implementation_date,
			revised_date=:revised_date,
			application_state=:application_state,
			directory_type=:directory_type,
		    create_user = :create_user,
            create_date = current_timestamp(),
			note = :note
			WHERE
		    id = :id
	</select>
	
	<!-- 合规目录下拉列表 -->
	<select id="queryComplianceDirectoryList">  
		  select a.id as id, a.directory_name as name from t_core_compliance_directory a
	</select>
	
	<!-- 敏感等级下拉列表 -->
	<select id="getTCoreSensitiveLevelList">  
		  select a.id as id, a.sensitive_name as name from t_core_sensitive_level a
	</select>
	
	<!-- 数据分类下拉列表 -->
	<select id="getCoreDataScopeList">  
		  select a.id as id, a.directory_name as name from t_core_compliance_directory a
	</select>
	
	<!-- 字段法律法规列表 -->
	<select id="getFieldDetail">  
		select * from(
		SELECT a.id,a.fields,a.`names`,a.tschemas,c.directory_name as rulename,'分类分级' as type
		from t_core_table_field a,t_core_scope_field b,t_core_compliance_directory c
		where a.id=b.tablefield_id and b.directory_id=c.id
		UNION
		select d.id,d.fields,d.`names`,d.tschemas,f.directory_name as rulename,'合规目录' as type
		from t_core_table_field d,t_core_field_directory e,t_core_compliance_directory f
		where d.id=e.tablefield_id and e.directory_id=f.id
		UNION
		select g.id,g.fields,g.`names`,g.tschemas,i.sensitive_name as rulename,'敏感等级' as type
		from t_core_table_field g,t_core_field_sensitive h,t_core_sensitive_level i
		where g.id=h.tablefield_id and h.sensitive_id=i.id
		) x
		where x.id=:columnid

	</select>
	
	
	
	
		
	<!-- 更新操作 -->
	<select id="updateattachmentdirectory">  
		  	UPDATE t_core_attachment_directory
			SET 
		    create_user = :create_user,
            create_date = current_timestamp(),
			file_note = :file_note
			WHERE
		    id = :id
	</select>
</sqls>

