<?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" />

<og:head headType="init" />
<og:comment
	system	= "GF:エンジンサンプル"
	pgid	= "GF2050"
	title	= "定義ファイル出力"
	date	= "2014/12/19"
	author	= "Hiroki.Nakamura"
	text	= ""
	version	= "001"
/>

<og:columnCheck
	command   = "{@command}"
	columns   = "SYSTEM_ID,TABLE_NAME,CLM"
/>

<!-- TBLSYU='T' は、テンポラリ -->
<og:query command="{@command}" debug="{@debug}" maxRowCount="0">
		delete from GF80
		where  SYSTEM_ID = '{@SYSTEM_ID}'
		and    TBLSYU    = 'T'
		and    FGJ		 = '1'
</og:query>

<!-- DB定義ﾏｽﾀ から、ﾃｰﾌﾞﾙとｶﾗﾑを JOIN して抜き出す。抜出順に、登録する。 -->
<og:query command="{@command}" debug="{@debug}" maxRowCount="0">
		select A.SYSTEM_ID,A.TBLSYU,B.KBTBL,A.TABLE_NAME,B.NAME_JA,
				A.CLM,A.SEQNO,A.NAME_JA AS CLM_NAME,A.CLS_NAME,A.USE_LENGTH,
				A.NOT_NULL,A.RENDERER,A.EDITOR,A.DBTYPE,A.DATA_DEFAULT,A.KBCLM,A.BIKO
		from   GF05 A inner join GF02 B
				on  A.SYSTEM_ID  = B.SYSTEM_ID
				and A.TBLSYU     = B.TBLSYU
				and A.TABLE_NAME = B.TABLE_NAME
				and B.FGJ	= '1'
		where  A.SYSTEM_ID	= '{@SYSTEM_ID}'
		and    A.TBLSYU		= '{@TBLSYU}'
		and    A.FGJ		= '1'
		order by A.SYSTEM_ID,A.TBLSYU,B.SEQNO,A.TABLE_NAME,A.SEQNO
</og:query>

<!-- TBLSYU='T' のテンポラリに、INSERT する。 -->
<og:tableUpdate
	selectedAll = "true"
	command		= "ENTRY"
	queryType	= "JDBCTableUpdate">
		INSERT INTO GF80
			( SYSTEM_ID,TBLSYU,KBTBL,TABLE_NAME,NAME_JA,
				CLM,SEQNO,CLM_NAME,CLS_NAME,USE_LENGTH,
				NOT_NULL,RENDERER,EDITOR,DBTYPE,DATA_DEFAULT,KBCLM,BIKO,
				FGJ,DYSET,DYUPD,USRSET,USRUPD,PGUPD )
		VALUES
			( [SYSTEM_ID],'T',[KBTBL],[TABLE_NAME],[NAME_JA],
				[CLM],[SEQNO],[CLM_NAME],[CLS_NAME],[USE_LENGTH],
				[NOT_NULL],[RENDERER],[EDITOR],[DBTYPE],[DATA_DEFAULT],[KBCLM],[BIKO],
				'1','{@USER.YMDH}','{@USER.YMDH}','{@USER.ID}','{@USER.ID}','{@GUI.KEY}' )
</og:tableUpdate>

<!-- INDEX番号を、上書きする。別の方法を模索する必要あり。 -->
<og:query command="{@command}" debug="{@debug}" maxRowCount="0">
		UPDATE GF80 A
			SET A.UK = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_UQ' OR B.INDEX_NAME = 'PK_' || A.TABLE_NAME )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.LUK = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND   B.INDEX_NAME = A.TABLE_NAME || '_LUQ'
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXA = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SA' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY01' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXB = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SB' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY02' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXC = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SC' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY03' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXD = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SD' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY04' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXE = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SE' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY05' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXF = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SF' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY06' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXG = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SG' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY07' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXH = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SH' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY08' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXI = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SI' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY09' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXJ = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SJ' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY10' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1'),
				A.IXK = (SELECT B.SEQNO FROM GF07 B
							WHERE B.TBLSYU     = '{@TBLSYU}'
							AND   B.SYSTEM_ID  = A.SYSTEM_ID
							AND   B.TABLE_NAME = A.TABLE_NAME
							AND ( B.INDEX_NAME = A.TABLE_NAME || '_SK' OR B.INDEX_NAME = A.TABLE_NAME || '_KEY11' )
							AND   B.CLM = A.CLM
							AND   B.FGJ = '1')
		WHERE A.SYSTEM_ID 	= '{@SYSTEM_ID}'
		AND   A.TBLSYU		= 'T'
</og:query>

<!-- 抜出順に、登録する。→ UNIQキー順になる。 -->
<og:query command="{@command}" debug="{@debug}" maxRowCount="0" >
		select KBTBL,TABLE_NAME,NAME_JA,SEQNO,CLM_NAME,CLM,'' as DTYPE,CLS_NAME,USE_LENGTH,
				UK,LUK,IXA,IXB,IXC,IXD,IXE,IXF,IXG,IXH,IXI,IXJ,IXK,
				NOT_NULL,RENDERER,EDITOR,DBTYPE,DATA_DEFAULT,KBCLM,BIKO,COMMENTS
		from GF80
		where  SYSTEM_ID	= '{@SYSTEM_ID}'
		and    TBLSYU		= 'T'
		and    FGJ			= '1'
		order by UNIQ
</og:query>

<!-- キーの CLS_NAME,USE_LENGTH より、DTYPE の値を設定します。 -->
<!-- CLS_NAME,USE_LENGTH,DTYPE カラムIDが異なる場合は、keys,vals で指定します。 -->
<og:tableFilter classId="DTYPE" />

<!-- Ver 6.1.0.0 より、xlsx 形式で出力します。ただし、xlsx形式は、メモリを多く使います。 -->
<og:writeTable
	writerClass		= "Excel"
	fileURL			= "{@USER.ID}"
	filename		= "fileout.xlsx"
/>

<!--
	useCellStyle	= "true"
	useAutoCellSize	= "true"
	beforeDelete	= "true"
	fontPoint		= "9"
-->

<og:forward page="{@SYS.FILE_OUT_URL}{@USER.ID}/fileout.xlsx" filename="{@SYSTEM_ID}_{@LBL.GF80}.xlsx" />

<jsp:directive.include file="/jsp/common/htmlend.jsp" />
</jsp:root>
