<?xml version="1.0" encoding="UTF-8" ?>
<jsp:root
	xmlns:jsp="http://java.sun.com/JSP/Page"
	xmlns:og="hybs-taglib.tld"
	version="2.0">
<jsp:directive.page language="java" contentType="text/html; charset=UTF-8" />
<jsp:directive.page errorPage="/jsp/common/error.jsp" />
<og:head headType="result" />
<jsp:directive.include file="/jsp/custom/result_info.jsp" />

<og:columnCheck
	command   = "NEW"
/>

<!-- TYPE 定義のソースを取得 -->
<og:query command="NEW" dbid="{@DBID}" debug="{@debug}" maxRowCount="0">
		select A.OBJECT_NAME AS OBJ_NAME,B.LINE AS SEQNO,'' AS CLM ,
				'' AS CLS_NAME,'' AS USE_LENGTH,'' AS CLM_NAME,
				'' AS TABLE_NAME ,'' AS MAX_LENGTH,
				B.TEXT , '{@SYSTEM_ID}' AS SYSTEM_ID,'{@TBLSYU}' AS TBLSYU
		from USER_OBJECTS A inner join USER_SOURCE B
		on    A.OBJECT_NAME = B.NAME
		where A.OBJECT_TYPE = 'TYPE'
		and   B.TYPE        = 'TYPE'
		and   not A.OBJECT_NAME like '%ARRAY'
		order by A.OBJECT_NAME,B.LINE
</og:query>

<!-- テキストから、オブジェクト名、カラム名、クラス、利用桁数を切り出す。 -->
<og:tableFilter classId="DBARG"
		keys="TEXT,OBJ_NAME,SEQNO,CLM,CLS_NAME,USE_LENGTH,CLM_NAME,MAX_LENGTH"
		vals="TEXT,OBJ_NAME,SEQNO,CLM,CLS_NAME,USE_LENGTH,CLM_NAME,MAX_LENGTH" />

<!-- 最も適合すると思われるテーブル名を取得する。 -->
<og:query command="NEW" debug="{@debug}" maxRowCount="0" scope="request">
		select A.OBJ_NAME , B.TABLE_NAME , count(*) as CNT
		from GF81 A inner join GF80 B
			 on A.CLM = B.CLM
			and A.SYSTEM_ID = B.SYSTEM_ID
			and A.TBLSYU    = B.TBLSYU
		where A.SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   A.TBLSYU		=  '{@TBLSYU}'
		and   B.KBCLM		&lt;= '5'
		and   A.FGJ		in  ('0','1')
		and   B.TABLE_NAME IS NOT NULL
		group by A.OBJ_NAME , B.TABLE_NAME
		order by A.OBJ_NAME , CNT DESC , B.TABLE_NAME
</og:query>

<!-- 取り出したテーブル名を、マージする。 -->
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "OBJ_NAME"
	unionClms  = "TABLE_NAME"
/>

<!-- 元の TYPE定義に設定した、テーブル名を取得する。 -->
<!--
<og:query command="NEW" debug="{@debug}" maxRowCount="0" scope="request">
		select OBJ_NAME,CLM ,TABLE_NAME
		from GF81
		where SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   TBLSYU	=  '{@TBLSYU}'
		and   FGJ		in  ('0','1')
		order by OBJ_NAME,CLM,TABLE_NAME
</og:query>
-->
<!-- 取り出したテーブル名を、マージする。 -->
<!--
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "OBJ_NAME,CLM"
	unionClms  = "TABLE_NAME"
/>
-->

<!-- 実スキーマ から、カラムの最大桁数を取得 -->
<og:query command="NEW" dbid="{@DBID}" debug="{@debug}" maxRowCount="0" scope="request">
		select TABLE_NAME , COLUMN_NAME AS CLM ,
			( CASE WHEN DATA_PRECISION IS NULL 
						THEN to_char(DATA_LENGTH)
						ELSE to_char(DATA_PRECISION) ||
								CASE WHEN DATA_SCALE = 0
										THEN NULL
										ELSE ',' || to_char(DATA_SCALE)
								END
				END
			) as MAX_LENGTH
		from USER_TAB_COLUMNS
		where INSTR(TABLE_NAME,'$') &lt; 1
</og:query>

<!-- 取り出した最大桁数を、マージする。 -->
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "TABLE_NAME,CLM"
	unionClms  = "MAX_LENGTH"
/>

<!-- 実スキーマ から、カラムの最大桁数を取得 -->
<!--
<og:query command="NEW" dbid="{@DBID}" debug="{@debug}" maxRowCount="0" scope="request">
		select COLUMN_NAME AS CLM , DATA_TYPE AS CLS_NAME,
			MAX( CASE WHEN DATA_PRECISION IS NULL 
						THEN to_char(DATA_LENGTH)
						ELSE to_char(DATA_PRECISION) ||
								CASE WHEN DATA_SCALE = 0
										THEN NULL
										ELSE ',' || to_char(DATA_SCALE)
								END
				END
			) as MAX_LENGTH
		from USER_TAB_COLUMNS
		where INSTR(TABLE_NAME,'$') &lt; 1
		group by COLUMN_NAME,DATA_TYPE
</og:query>
-->
<!-- 取り出した最大桁数を、マージする。 -->
<!--
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "CLM,CLS_NAME"
	unionClms  = "MAX_LENGTH"
/>
-->

<!-- ＤＢ定義ＤＢの定義情報から、カラム名称を取得する。 -->
<og:query command="NEW" debug="{@debug}" maxRowCount="0" scope="request">
		select TABLE_NAME,CLM,CLM_NAME ,'1' as RNK
		from GF80
		where SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   TBLSYU	=  '{@TBLSYU}'
		and   KBCLM		&lt;= '5'
		and   FGJ		in  ('0','1')
		and   CLM_NAME	IS NOT NULL
		union
		select TABLE_NAME,CLM,CLM_NAME ,'2' as RNK
		from GF80
		where SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   TBLSYU	!=  '{@TBLSYU}'
		and   KBCLM		&lt;= '5'
		and   FGJ		in  ('0','1')
		and   CLM_NAME	IS NOT NULL
		order by TABLE_NAME,CLM,RNK
</og:query>

<!-- 取り出したカラム名称を、マージする。 -->
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "TABLE_NAME,CLM"
	unionClms  = "CLM_NAME"
/>

<!-- ＤＢ定義ＤＢの定義情報から、カラム名称を取得する。 -->
<!--
<og:query command="NEW" debug="{@debug}" maxRowCount="0" scope="request">
		select CLM,CLM_NAME ,count(*) AS CNT, '1' as RNK
		from GF80
		where SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   TBLSYU	=  '{@TBLSYU}'
		and   KBCLM		&lt;= '5'
		and   FGJ		in  ('0','1')
		and   CLM_NAME	IS NOT NULL
		group by CLM,CLM_NAME
		union
		select CLM,CLM_NAME ,count(*) AS CNT, '2' as RNK
		from GF80
		where SYSTEM_ID	=  '{@SYSTEM_ID}'
		and   TBLSYU	!=  '{@TBLSYU}'
		and   KBCLM		&lt;= '5'
		and   FGJ		in  ('0','1')
		and   CLM_NAME	IS NOT NULL
		group by CLM,CLM_NAME
		order by CLM,RNK,CNT DESC
</og:query>
-->
<!-- 取り出したカラム名称を、マージする。 -->
<!--
<og:tableMerge
	action     = "UNION_CLM"
	slaveKeys  = "CLM"
	unionClms  = "CLM_NAME"
/>
-->

<!-- トランザクション 登録処理 -->
<og:transaction >
	<!-- 元のTYPE定義を削除する。 -->
	<og:query command="NEW" debug="{@debug}" maxRowCount="0" scope="request">
			DELETE FROM GF81
			WHERE SYSTEM_ID = '{@SYSTEM_ID}'
			AND   TBLSYU    = '{@TBLSYU}'
	</og:query>

	<!-- 上で作成した 新しいTYPE定義を、追加する。 -->
	<og:tableUpdate
		command		= "ENTRY"
		queryType	= "JDBCTableUpdate"
		selectedAll = "true"
	>
		INSERT INTO GF81
			(SYSTEM_ID,TBLSYU,OBJ_NAME,SEQNO,CLM ,CLS_NAME,USE_LENGTH,
			 TABLE_NAME,MAX_LENGTH,CLM_NAME,
			 CDKH,FGJ,DYSET,DYUPD,USRSET,USRUPD,PGUPD)
		VALUES
			([SYSTEM_ID],[TBLSYU],[OBJ_NAME],[SEQNO],[CLM],[CLS_NAME],[USE_LENGTH],
			 [TABLE_NAME],[MAX_LENGTH],[CLM_NAME],
			 NULL,'1','{@USER.YMDH}','{@USER.YMDH}','{@USER.ID}','{@USER.ID}','{@GUI.KEY}')
	</og:tableUpdate>
</og:transaction >

<og:forward page="result.jsp" keys="command" vals="RENEW" />
<jsp:directive.include file="/jsp/common/htmlend.jsp" />
</jsp:root>
