Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 28th September 2007, 12:33
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
DEV: Export any Baan table to (proper) Excel

One of the tasks that I concider annoying is to provide table exports in the only tool that anybody can use...Excel.

To come up with a more permanent solution, I developed this session for BaanIV. With a bit of sluething it should work in any Baan version.
This session will export any baan table, optionaly using selection criteria, to an excel XML sheet. Dates, enums and what not are all properly converted.

Oh, the attached screenshots are in Dutch but it has an option for multiple languages and comes in English too.

Enjoy!
Attached Images
File Type: jpg bdet1.jpg (39.9 KB, 840 views)
File Type: jpg bdet2.jpg (38.9 KB, 669 views)
Attached Files
File Type: zip bdet.zip (88.0 KB, 563 views)
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
  #2  
Old 29th September 2007, 10:17
sukesh75's Avatar
sukesh75 sukesh75 is offline
Guru
 
Join Date: Dec 2002
Posts: 1,031
sukesh75 is on a distinguished road
Baan: Baan 4c - DB: SQL Server 2000 - OS: Windows 2003
Has anyone tried this out? I imported this into our environment using Import data dictionary session and copied the forms from its present language to english. Upon running the session "Baan Data Export Tool" i get the error "Error Reading From Dump".

Did i miss anything?
Apart from that, when i click on the edit form button on the Maintain Forms, i just get a blank box with no fields for both the forms in Baan Data Export Tool session.

sk
Reply With Quote
  #3  
Old 2nd October 2007, 16:25
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,934
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I have problems also. I have sent Francesco an email asking for assistance.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #4  
Old 3rd October 2007, 15:20
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,934
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Received a message from Francesco - he will try to retrieve a copy of the correct dump.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #5  
Old 5th October 2007, 11:48
Arthas Arthas is offline
Junior Member
 
Join Date: Jul 2003
Location: Europe
Posts: 28
Arthas is on a distinguished road
Baan: 4-5 - DB: All - OS: All
I sympathise with you......

I can't help raising a wry smile of sympathy with your situation - we know what you're going through.
My record for reverse engineering a VB macro using Safari ODBC drivers into raw informix sql is:
Before: three days
After: seven seconds.
Revenge is sweet.............. and I shall watch this thread with great interest...
Reply With Quote
  #6  
Old 10th October 2007, 09:19
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
Source and missing forms

As pointed out, my dumpfile did not include the source or the forms for this session. Oops, sorry, this must have been an update and not a full dump.

So to set things straight, here are the forms (Dutch only, I'm afraid) and the missing source.

Code:
|******************************************************************************
|* Data Export Tool
|* Francesco Frentrop            
|* 2007-07-11
|******************************************************************************
|* Main table , Form Type 4
|******************************************************************************
                                                                                
|****************************** declaration section ***************************
declaration:
	
	table	tttaad100		| Company data
	table	tttadv101		| Modules
	table	tttadv112		| Package VRC's
	table	tttadv130		| Descriptions per language
	table	tttadv140		| Display Labels per PVRC
	table	tttadv400		| Domains
	table	tttadv401		| Enum Values
	table	tttadv402		| Enum Descriptions
	table	tttadv420		| Table Definitions
	table	tttadv421		| Table Indexes
	table	tttadv422		| Table Fields

	domain	tcclan		lang	| Language
	domain	ttaad.pacc	PACC	| Package VRC
	domain	ttadv.vers	version
	domain	ttadv.rele	release
	domain	ttadv.cust	customer

	string	sTABLE(9)		| Table
	string	sVRC(10)		| VRC

	string	expr(5020)		| Expression string
	string	strBuffer	BASED

	long	sql			| SQL ID
	long	exp_id			| Expression ID
	long	app_id			| Applicaion ID
	long	ret
	long	fp			| File Pointer
	long	fp_log			| File Pointer to log file

	string	bdet.author(32)		| Author
	string	bdet.create.date(20)	| Creation Date
	string	bdet.version(7)		| XML version No
	string	datum.style(24)
	string	time.style(24)

	double	width.factor
	long	EXCEL.LIMIT
	long	error.status

	string	local.dir(128)		| Directory for export
	
	string	sdat(12)
	string	syear(4)
	string	smonth(2)
	string	sday(2)
	string	shr(2)
	string	smin(2)
	string	ssec(2)
	string	rkey(12)

	|*********************** Table data  **********************************
	long	field.count
	long	row.count
	long	row.len

	long	format.row.type
	long	format.row.name
	long	format.row.type.len
	long	format.row.name.len
	long	format.row.header
	long	format.row.header.len
	long	format.row.width
	long	format.row.width.len
	long	format.row.cdom
	long	format.row.cdom.len
	long	format.row.cpac
	long	format.row.cpac.len

	string	format.row(101, 1024)

	
	|***********************  Messages  **********************************	
	string	error1(255)
	string	error2(255)
	string	error3(255)
	string	error4(255)
	string	error5(255)
	string	error6(255)
	string	error7(255)
	string	error8(255)
	string	error9(255)
	string	error10(255)

	string	status1(255)
	string	status2(255)
	string	status3(255)
	string	status4(255)
	string	status5(255)
	string	status6(255)
	string	status7(255)
	string	status8(255)
	string	status9(255)

	|********************** form fields ***********************************
	extern	domain	tcmcs.str2     	cpac.f
	extern	domain	tcmcs.str3	cmod.f
	extern	domain	tcmcs.str3	flno.f

	extern	domain	ttyeno        	layout.on
	extern	domain	ttyeno        	limit.on
	extern	domain	ttyeno		timestamp.on

	extern	domain	tcmcs.str50	status
	extern	domain	tcmcs.long	rcd.counter
	extern	domain	tcmcs.long	limit.no
	extern	domain	tcmcs.str80	table.name

	extern	domain	tcmcs.str8	form.index(11)
	extern	domain	tcmcs.str50	value.f(11)
	extern	domain	tcmcs.str50	value.t(11)
	extern	domain	tcmcs.str4	form.from(11)
	extern	domain	tcmcs.str4	form.to(11)
	extern	domain	tcmcs.str16	form.desc(11)
	extern	domain	ttyeno		index.active(11)
		domain	tcmcs.long	form.type(11)

	|********************** includes **************************************
        #pragma used dll ottdllbw

	#include <bic_tt>


Before.Program:

	PACC = curr.pacc$
	lang = language$

	error.status = 0

	format.row.type		= 9
	format.row.name		= 1
	format.row.type.len	= 2
	format.row.name.len	= 8
	format.row.header	= 11
	format.row.header.len	= 70
	format.row.width	= 81
	format.row.width.len	= 7
	format.row.cdom		= 88
	format.row.cdom.len	= 12
	format.row.cpac		= 100
	format.row.cpac.len	= 2

	set.error.messages()
	set.status.messages()

	sdat = dte$()
	syear = "20" & sdat(5;2)
	smonth = sdat(1;2)
	sday = sdat (3;2)
	shr = sdat(7;2)
	smin = sdat(9;2)
	ssec = sdat(11;2)
	
	ret = tt.user(logname$, bdet.author)
	bdet.create.date = syear & "-" & smonth & "-" & sday & "T" & shr & ":" & smin & ":" & ssec & "Z"
	bdet.version = "11.6568"
	datum.style = " ss:StyleID=""s23"""
	time.style = " ss:StyleID=""s24"""
	width.factor = 7.27

	local.dir = "C:\Temp\"

	EXCEL.LIMIT = 65536
	set.status(1)

|****************************** form section **********************************

form.all:
form.1:
init.form:
	limit.on = ttyeno.no
	layout.on = ttyeno.yes
	timestamp.on = ttyeno.no
	refresh()
	display.all()

	|get.screen.defaults()
form.2:
before.form:
	ret = load.indexes()
	if ret = -1 then
		Message("Bestoa nie!!")		| TODO
	endif

|****************************** choice section ********************************

choice.cont.process:
on.choice:
	start.process()


|****************************** field section *********************************

field.cpac.f:
check.input:
if len(strip$(cpac.f)) <> 2 then
	set.input.error(error7, cpac.f)
endif


field.cmod.f:
check.input:
if len(strip$(cmod.f)) <> 3 then
	set.input.error(error7, cmod.f)
endif
before.zoom:
	ttadv101.cpac = cpac.f
field.flno.f:
check.input:
while len(flno.f) < 3
	flno.f = "0" & flno.f
endwhile
before.zoom:
	ttadv420.cpac = cpac.f
	ttadv420.cmod = cmod.f
when.field.changes:
	rkey = cmod.f & flno.f
	determine.vrc()

	select	ttadv130.desc
	from	ttadv130
	where	ttadv130._compnr = 000
	and	ttadv130.cpac = :cpac.f
	and	ttadv130.kdes = ttadv.kdes.table
	and	ttadv130.rkey = :rkey
	and	ttadv130.clan = :lang
	as set with 1 rows
	selectdo
		table.name = ttadv130.desc
		refresh()
		display.all()
	endselect

|****************************** function section ******************************

functions:

function start.process()
{
	string	tmp.file(128)
	string	local.path(128)
	string	file.name(20)

	status = ""
	error.status = 0
	rcd.counter = 0

	determine.vrc()

	tmp.file = creat.tmp.file$( bse.tmp.dir$() )

	fp = seq.open(tmp.file, "a")
	fp_log = seq.open(bse.dir$() & "\log\cccom9000.log", "w")

	set.status(2)

	process.table()

	set.status(8)
	ret = seq.close(fp)
	ret = seq.close(fp_log)

	if not error.status then
		if timestamp.on = ttyeno.yes then
			file.name = sTABLE & dte$()
		else
			file.name = sTABLE
		endif

		if layout.on = ttyeno.yes then
			file.name = file.name & ".xls"
		else
			file.name = file.name & ".csv"
		endif

		local.path = strip$(local.dir) & strip$(file.name)

		ret = server2client(tmp.file, strip$(local.path), 1, 0)
		app_id = app_start("excel.exe " & local.path, "", "", "", "")
		set.status(9)
	else
		Message("No data to be exported. Error Status: %d", error.status)
	endif
}

function void determine.vrc()
{
	long result, sequ
	
	sequ = 1
	result = 0

	while result = 0
	
		select	*
		from	ttadv112
		where	ttadv112._compnr = 000
		and	ttadv112.pacc = :PACC
		and	ttadv112.cpac = :cpac.f
		and	ttadv112.sequ = :sequ
		selectdo
			version = ttadv112.vers
			release = ttadv112.rele
			customer = ttadv112.cust

			select	ttadv420.*
			from	ttadv420
			where   ttadv420._compnr = 000
			and	ttadv420.cpac = :ttadv112.cpac
			and	ttadv420.cmod = :cmod.f
			and	ttadv420.flno = :flno.f
			and	ttadv420.vers = :version
			and	ttadv420.rele = :release
			and	ttadv420.cust = :customer
			and	ttadv420.expi = ttyeno.no
			selectdo
				result = 1
				sTABLE = ttadv420.cpac & ttadv420.cmod & ttadv420.flno
				sVRC   = version & release & customer
			selectempty
				sequ = sequ + 1
			endselect
		selectempty
			result = -1
			message(error4, PACC, cpac.f, sequ)
			error.status = 4
		endselect

	endwhile
}

function process.table()
{
	| I know, select is strictly speaking not necessary here

	select	ttadv420.*
	from	ttadv420
	where   ttadv420._compnr = 000
	and	ttadv420.cpac = :ttadv112.cpac
	and	ttadv420.cmod = :cmod.f
	and	ttadv420.flno = :flno.f
	and	ttadv420.vers = :version
	and	ttadv420.rele = :release
	and	ttadv420.cust = :customer
	and	ttadv420.expi = ttyeno.no
	selectdo
			process.data()
	endselect
}

function void process.data()
{
	long	x
	string	strParse(2048)
	string	from.value(50)
	string	to.value(50)
	
	set.status(3)
		
	ret = get.table.info(sTABLE, field.count, row.count, row.len)
	if ret then
		error.status = 3
		message(error3, ret)
	else
		set.status(4)
		if layout.on = ttyeno.yes then
			create.xml.header()
		endif

		if layout.on = ttyeno.yes then
			for x = 1 to field.count
				ret = seq.puts("   <Column ss:Width=""" & strip$(format.row(format.row.width, x;format.row.width.len)) & """/>", fp)
			endfor
		endif

		write.header()

		free.mem(strBuffer)
		alloc.mem(strBuffer,row.len)

		build.expr()

		ret = seq.puts(expr, fp_log)

		strParse = "select * from " & sTABLE
		ret = 0
		for x = 1 to 11
			if (strip$(form.index(1, x)) <> "") and (strip$(value.t(1, x)) <> "") then
				if ret = 0 then 
					from.value = " where "
					ret = 1
				else
					from.value = " and "
				endif
				on case form.type(x)
				case DB.LONG:
				case DB.DOUBLE:
				case DB.INTEGER:
				case DB.FLOAT:
					if strip$(value.f(1, x)) = "" then
						from.value = from.value & strip$(form.index(1, x)) & " >= " & "0"
					else
						if isdigit(strip$(value.f(1, x))) then
							from.value = from.value & strip$(form.index(1, x)) & " >= " & strip$(value.f(1, x))
						else
							Message(error8, form.index(1, x))
						endif
					endif
					if isdigit(strip$(value.t(1, x))) then 
						to.value = " and " & strip$(form.index(1, x)) & " <= " & strip$(value.t(1, x))
					else
						message(error8, form.index(1, x))
					endif
					break
				case DB.STRING:
				case DB.MULTIBYTE:
					from.value = from.value & strip$(form.index(1, x)) & " >= " & """" & strip$(value.f(1, x)) & """"
					to.value = " and " & strip$(form.index(1, x)) & " <= " & """" & strip$(value.t(1, x)) & """"
					break
				default:
					from.value = ""
					to.value = ""
					Message(error9, form.index(1, x))
				endcase
				strParse = strParse & strip$(from.value) & strip$(to.value)
			endif
		endfor
		if limit.on = ttyeno.yes then
			strParse = strParse & " as set with " & str$(limit.no) & " rows"
		endif

		ret = seq.puts(strParse, fp_log)

		sql = sql.parse(strParse)

		if not sql then
			error.status = 1
			Message(Error1)
			goto SKIP
		endif

		set.status(5)
		sql.exec(sql)
		error.bypass = 1
		while (true)
			ret = sql.fetch(sql)
			on case ret
			case eendfile:
				break
			case enorec:
				error.status = 6
				message(error6, strParse)
			case enotable:
				error.status = 10
				message(error10, sTABLE)
			case 0:
				rcd.counter = rcd.counter + 1
				display("rcd.counter")
				|refresh()
				strBuffer = s.expr$(exp_id)
				write.row()
				if rcd.counter = EXCEL.LIMIT then
					error.status = 5
					message(error5)
					break
				else
					continue
				endif
			default:
				error.status = 2
				message(error2, ret, strParse)
				
			endcase
			break
		endwhile
		error.bypass = 0

		set.status(6)
	
		sql.break(sql)
		sql.close(sql)
		sql = 0

		free.mem(strBuffer)
		expr.free(exp_id)
		expr = ""

		if layout.on = ttyeno.yes then
			create.xml.footer()
		endif
	SKIP:
	endif
}

function long get.table.info(string table.name(8), ref long no_fields, ref long no_rows, ref long row_len)
{	
	long	result
	long	no_keys, no_columns, int_length, real_length
	string	domain_name(14), default_val(1)
	long	offset, size, dept, type, flag
	string	column.name(18)

	string	cpac(2)
	string	cmod(3)
	string	flno(3)
	domain	ttadv.clab	clab

	result = 0
	no_fields = 0

	cpac = table.name(1;2)
	cmod = table.name(3;3)
	flno = table.name(6;3)

	ret = rdi.table(table.name, no_keys, no_columns, int_length, real_length)
	row_len = int_length * 1.1	| Add 10% fudge because it doesn't work @TODO@

	select	*
	from	ttadv422
	where	ttadv422._compnr = 000
	and	ttadv422.cpac = :cpac
	and	ttadv422.cmod = :cmod
	and	ttadv422.flno = :flno
	and	ttadv422.vers = :version
	and	ttadv422.rele = :release
	and	ttadv422.cust = :customer
	order by ttadv422.fdno
	selectdo
		no_fields = no_fields + 1
		type = get.field.type(table.name, ttadv422.fdnm)
		format.row(format.row.name, no_fields) = ttadv422.fdnm
		format.row(format.row.type, no_fields) = str$(type)

		if strip$(ttadv422.clab) <> "" then
			clab = ttadv422.clab
		else
			clab = ttadv422.cpac & ttadv422.cmod & ttadv422.flno & "." & ttadv422.fdnm
		endif

		format.row(format.row.header, no_fields) = get.field.desc(lang, cpac.f, clab)

		column.name = table.name & "." & ttadv422.fdnm
		ret = rdi.column(column.name, domain_name, offset, size, dept, type, flag, default_val)
		if size = 0 then 
			size = 1
		endif
		if type = DB.ENUM then
			size = 12
		endif
		format.row(format.row.width, no_fields) = str$(size * width.factor)

		format.row(format.row.cdom, no_fields) = ttadv422.cdom
		format.row(format.row.cpac, no_fields) = ttadv422.pacd
		if (type = DB.BITSET) or (type = DB.COMBINED) then
			no_fields = no_fields - 1
		endif
	selectempty
		result = 1
	endselect

	return(result)
}

function void write.header()
{
	string	header(2048)
	long	x

	if layout.on = ttyeno.yes then
		ret = seq.puts("   <Row ss:StyleID=""s22"">", fp)
		for x = 1 to field.count
			ret = seq.puts("    <Cell><Data ss:Type=""String"">" & strip$(format.row(format.row.header, x;format.row.header.len)) & "</Data></Cell>", fp)
		endfor
		ret = seq.puts("   </Row>", fp)
	else
		for x = 1 to field.count
			header = header & strip$(format.row(format.row.header, x;format.row.header.len)) & ";"
		endfor
		ret = seq.puts(strip$(header), fp)
	endif
}

function void write.row()
{
	string	tmp.field(1024)
	string	data.type(12)
	string	read.char(1)
	string	cell.style(24)
	string	cdom(12)
	string	cpac(2)
	long	type
	long	x, y
	long	cnst
	long	buffer.len

	if layout.on = ttyeno.yes then
		ret = seq.puts("   <Row>", fp)

		y = 1

		for x = 1 to field.count
			tmp.field = ""
			read.char = ""

			strBuffer = strip$(strBuffer)
			buffer.len = len(strBuffer)
		
			while read.char <> ";" and y <= buffer.len
				read.char = strBuffer(y;1)
				if read.char <> ";" then
					tmp.field = tmp.field & read.char
				endif
				y = y + 1
			endwhile
		
			type = lval(format.row(format.row.type, x;format.row.type.len))
			cdom = format.row(format.row.cdom, x;format.row.cdom.len)
			cpac = format.row(format.row.cpac, x;format.row.cpac.len)

			ret = xml.prep(tmp.field)

			on case type
		        case DB.BYTE:	
				data.type = "Number"
				cell.style = ""
	                	break
			case DB.DATE:
				data.type = "DateTime"
				cell.style = datum.style
				format.date.for.excel(tmp.field, data.type, cell.style)
				break
			case DB.TIME:
				data.type = "DateTime"
				cell.style = time.style
				format.time.for.excel(tmp.field, data.type, cell.style)
				break
			case DB.DOUBLE:
				data.type = "Number"
				cell.style = ""
				break
			case DB.ENUM:
				cnst = lval(tmp.field)
				if cnst <> 0 then
					select	*
					from	ttadv401
					where	ttadv401._compnr = 000
					and	ttadv401.cpac = :cpac
					and	ttadv401.cdom = :cdom
					and	ttadv401.cnst = :cnst
					as set with 1 rows
					selectdo
						select	*
						from	ttadv402
						where	ttadv402._compnr = 000
						and	ttadv402.clan = :lang
						and	ttadv402.cpac = :ttadv401.cpac
						and	ttadv402.cdom = :ttadv401.cdom
						and	ttadv402.vers = :ttadv401.vers
						and	ttadv402.rele = :ttadv401.rele
						and	ttadv402.cust = :ttadv401.cust
						and	ttadv402.ctnm = :ttadv401.ctnm
						as set with 1 rows
						selectdo
							tmp.field = ttadv402.edes
						selectempty
							tmp.field = "#" & ttadv401.ctnm
						endselect
					selectempty
						tmp.field = "##" & cpac & "." & cdom & "." & "##" & tmp.field
					endselect
				else
					tmp.field = ""
				endif
				data.type = "String"
				cell.style = ""
				break
			case DB.FLOAT:
				data.type = "Number"
				cell.style = ""
                		break
			case DB.INTEGER:
				data.type = "Number"
				cell.style = ""
	                	break
			case DB.LONG:
				data.type = "Number"
				cell.style = ""
				break
			case DB.STRING:
				data.type = "String"
				cell.style = ""
				break
			case DB.MULTIBYTE:
				data.type = "String"
				cell.style = ""
				break
			default:
				data.type = "String"
				cell.style = ""
			endcase

			ret = seq.puts("    <Cell" & cell.style & "><Data ss:Type=" & """" & strip$(data.type) & """" & ">" & strip$(tmp.field) & "</Data></Cell>", fp)
		endfor

		ret = seq.puts("   </Row>", fp)
	else
		ret = seq.puts(strBuffer, fp)
	endif	
}

function void format.date.for.excel(ref string num.str, ref string type.str, ref string style.str)
{
	long	date.val

	long	year
	long	month
	long	day

	string	syear(4)
	string	smonth(2)
	string	sday(2)
	
	date.val = lval(num.str)
	if date.val < 693596 then
		num.str = ""
		type.str = "String"
		style.str = ""
	else
		num.to.date(date.val, year, month, day)
		syear = str$(year)
		if month < 10 then
			smonth = "0" & str$(month)
		else
			smonth = str$(month)
		endif
		if day < 10 then
			sday = "0" & str$(day)
		else
			sday = str$(day)
		endif
		
		num.str = syear & "-" & smonth & "-" & sday & "T00:00:00.000"
	endif
}

function void format.time.for.excel(ref string num.str, ref string type.str, ref string style.str)
{
	long	num.val
	long	hrs
	long	mins
	long	secs

	num.val = lval(num.str)

	if num.val < 1 then
		num.str = ""
		type.str = "String"
		style.str = ""
	else
		secs = num.val \ 3600
		mins = (num.val - secs) \ 60
		hrs = (num.val - secs - (mins * 60)) / 60.0
		num.str = "1899-12-31T" & str$(hrs) & ":" & str$(mins) & ":" & str$(secs) & ".000"
	endif
}
function void build.expr()
{
	long	fld.type
	string	fld.name(8)
	long	x

	expr = ""

	for x = 1 to field.count
		fld.name = format.row(format.row.name, x;format.row.name.len)
		fld.type = lval(format.row(format.row.type, x;format.row.type.len))
		add.field.to.row(fld.name, fld.type)
	endfor
	exp_id = expr.compile(expr)
}

function domain ttcdes get.field.desc(domain tclang language, domain ttadv.cpac package, domain ttadv.clab label)
{
	domain	ttcdes		cdes

	select	ttadv140.desc
	from	ttadv140
	where	ttadv140._compnr = 000
	and	ttadv140.clan = :language
	and	ttadv140.cpac = :package
	and	ttadv140.clab = :label
	as set with 1 rows
	selectdo
		cdes = ttadv140.desc
	endselect

	| OR??
	| ret = getlabel (lang, ttadv422.cpac, clab, leng, lhgt, vers, rele, cust)

	return(strip$(cdes))
}

function long get.field.type(string table.name(8), string field.name(8))
{
	long		offset, size, dept, type, flag
	string		domain_name(14), default_val
	
	ret = rdi.column(table.name & "." & field.name, domain_name, offset, size, dept, type, flag, default_val)

	return(type)
}

function add.field.to.row(domain ttadv.fdnm field, long type)
{
	string		action(24)
	string		close.bracket(6)
	
	action = ""
	close.bracket = ")"
	on case type
        case DB.BYTE:	
		action = "str(val("	|-
		close.bracket = "))"
                break
	case DB.INTEGER:		|+
		action = "str("
                break
	case DB.LONG:			|+
		action = "str("
		break
	case DB.FLOAT:
		action = "str("
                break
	case DB.TIME:
		action = "str("
		break
	case DB.DOUBLE:			|+
		action = "str("
		break
	case DB.STRING:			|+
		action = "strip("
		break
	case DB.DATE:			|+
		action = "str("
		break
	case DB.TEXT:
		action = "str("
		break
	case DB.ENUM:			|+
		action = "str("
		break
	case DB.BITSET:
		action = "skip"
		break
	case DB.COMBINED:
		action = "skip"
		break
	case DB.MULTIBYTE:		|+
		action = "strip("
		break
	default:
		action = "skip"
	endcase
	
	if expr <> "" then
		expr = expr & " & "";"" & "
	endif

	if strip$(action) <> "skip" then
		expr = expr & action & sTABLE & "." & strip$(field) & close.bracket
	else
		|expr = expr & """;"""
	endif
}

function void create.xml.header()
{
	ret = seq.puts("<?xml version=""1.0""?>", fp)
	ret = seq.puts("<?mso-application progid=""Excel.Sheet""?>", fp)
	ret = seq.puts("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""", fp)
	ret = seq.puts(" xmlns:o=""urn:schemas-microsoft-com:office:office""", fp)
	ret = seq.puts(" xmlns:x=""urn:schemas-microsoft-com:office:excel""", fp)
	ret = seq.puts(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""", fp)
	ret = seq.puts(" xmlns:html=""http://www.w3.org/TR/REC-html40"">", fp)
	ret = seq.puts(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">", fp)
	ret = seq.puts("  <LastAuthor>" & strip$(bdet.author) & "</LastAuthor>", fp)
	ret = seq.puts("  <Created>" & bdet.create.date & "</Created>", fp)
	ret = seq.puts("  <LastSaved>" & bdet.create.date & "</LastSaved>", fp)
	ret = seq.puts("  <Version>" & bdet.version & "</Version>", fp)
	ret = seq.puts(" </DocumentProperties>", fp)
	ret = seq.puts(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)
	ret = seq.puts("  <WindowHeight>12660</WindowHeight>", fp)
	ret = seq.puts("  <WindowWidth>19020</WindowWidth>", fp)
	ret = seq.puts("  <WindowTopX>120</WindowTopX>", fp)
	ret = seq.puts("  <WindowTopY>120</WindowTopY>", fp)
	ret = seq.puts("  <ProtectStructure>False</ProtectStructure>", fp)
	ret = seq.puts("  <ProtectWindows>False</ProtectWindows>", fp)
	ret = seq.puts(" </ExcelWorkbook>", fp)
	ret = seq.puts(" <Styles>", fp)
	ret = seq.puts("  <Style ss:ID=""Default"" ss:Name=""Normal"">", fp)
	ret = seq.puts("   <Alignment ss:Vertical=""Bottom""/>", fp)
	ret = seq.puts("   <Borders/>", fp)
	ret = seq.puts("   <Font/>", fp)
	ret = seq.puts("   <Interior/>", fp)
	ret = seq.puts("   <NumberFormat/>", fp)
	ret = seq.puts("   <Protection/>", fp)
	ret = seq.puts("  </Style>", fp)
	ret = seq.puts("  <Style ss:ID=""s21"">", fp)
	ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)
	ret = seq.puts("  </Style>", fp)
	ret = seq.puts("  <Style ss:ID=""s22"">", fp)
	ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)
	ret = seq.puts("  </Style>", fp)
	ret = seq.puts("  <Style ss:ID=""s23"">", fp)
	ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)
	ret = seq.puts("   <NumberFormat ss:Format=""[$-413]dd\ mmm\ yyyy;@""/>", fp)
	ret = seq.puts("  </Style>", fp)
	ret = seq.puts("  <Style ss:ID=""s24"">", fp)
	ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)
	ret = seq.puts("   <NumberFormat ss:Format=""Short Time""/>", fp)
	ret = seq.puts("  </Style>", fp)
	ret = seq.puts(" </Styles>", fp)
	ret = seq.puts(" <Worksheet ss:Name=""BDET - " & sTABLE & """>", fp)
	ret = seq.puts("  <Table>", fp)
}

function void create.xml.footer()
{
	ret = seq.puts("  </Table>", fp)
	ret = seq.puts("  <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)
	ret = seq.puts("   <PageSetup>", fp)
	ret = seq.puts("    <PageMargins x:Bottom=""0.984251969"" x:Left=""0.78740157499999996""", fp)
	ret = seq.puts("     x:Right=""0.78740157499999996"" x:Top=""0.984251969""/>", fp)
	ret = seq.puts("   </PageSetup>", fp)
	ret = seq.puts("   <Print>", fp)
	ret = seq.puts("    <ValidPrinterInfo/>", fp)
	ret = seq.puts("    <PaperSizeIndex>9</PaperSizeIndex>", fp)
	ret = seq.puts("    <HorizontalResolution>600</HorizontalResolution>", fp)
	ret = seq.puts("    <VerticalResolution>600</VerticalResolution>", fp)
	ret = seq.puts("   </Print>", fp)
	ret = seq.puts("   <Selected/>", fp)
	ret = seq.puts("   <FreezePanes/>", fp)
	ret = seq.puts("   <FrozenNoSplit/>", fp)
	ret = seq.puts("   <SplitHorizontal>1</SplitHorizontal>", fp)
	ret = seq.puts("   <TopRowBottomPane>2</TopRowBottomPane>", fp)
	ret = seq.puts("   <ActivePane>2</ActivePane>", fp)
	ret = seq.puts("   <Panes>", fp)
	ret = seq.puts("    <Pane>", fp)
	ret = seq.puts("     <Number>3</Number>", fp)
	ret = seq.puts("     <ActiveRow>7</ActiveRow>", fp)
	ret = seq.puts("     <ActiveCol>5</ActiveCol>", fp)
	ret = seq.puts("    </Pane>", fp)
	ret = seq.puts("   </Panes>", fp)
	ret = seq.puts("   <ProtectObjects>False</ProtectObjects>", fp)
	ret = seq.puts("   <ProtectScenarios>False</ProtectScenarios>", fp)
	ret = seq.puts("  </WorksheetOptions>", fp)
	ret = seq.puts(" </Worksheet>", fp)
	ret = seq.puts("</Workbook>", fp)
}

function void set.status(long stat)
{
	on case stat
	case 1:
		status = status1
		break
	case 2:
		status = status2
		break
	case 3:
		status = status3
		break
	case 4:
		status = status4
		break
	case 5:
		status = status5
		break
	case 6:
		status = status6
		break
	case 7:
		status = status7
		break
	case 8:
		status = status8
		break
	case 9:
		status = status9
		break
	endcase

	refresh()
	display.all()
}


function void set.error.messages()
{
	on case lval(lang)
	case 1:
		error1 = "Onjuiste query gegenereerd door BDET"
		error2 = "Error %d heeft zich voorgedaan bij het uitvoeren van de query: %s"
		error3 = "Fout %d bij verzamelen tabelgegevens."
		error4 = "Geen VRK data in %s voor pakket %s, volgnr %d. Tabel bestaat niet."
		error5 = "Tabel heeft meer records dan Excel kan verwerken."
		error6 = "De query '%s' gaf geen records terug."
		error7 = "Invoer niet juist: '%s'"
		error8 = "Index %s heeft een numerieke waarde nodig."
		error9 = "Index %s kan niet worden verwerkt door BDET."
		error10= "Tabel %s bestaat niet in het huidige bedrijf."
		break
	default:
		error1 = "BDET generated illegal query"
		error2 = "Error %d occurred during execution of query: %s"
		error3 = "Error %d collecting table data."
		error4 = "No VRC data in %s for package %s, sequence %d. Table does not exist."
		error5 = "Number of records in table exceed Excel limitation."
		error6 = "The query '%s' did not return any records."
		error7 = "Input not valid: '%s'"
		error8 = "Index %s requires a numeric value."
		error9 = "BDET can not parse index %s."
		error10= "Table %s does not exist in the current company."
		break
	endcase
}

function void set.status.messages()
{
	on case lval(lang)
	case 1:
		status1 = "Initialiseren"
		status2 = ""
		status3 = "Tabelgegevens verzamelen"
		status4 = "Kopgegevens aanmaken"
		status5 = "Regels verwerken"
		status6 = "Voetregel aanmaken"
		status7 = ""
		status8 = "Programma afsluiten"
		status9 = "Gereed"
		break
	default:
		status1 = "Initialising"
		status2 = ""
		status3 = "Collecting table data"
		status4 = "Creating header"
		status5 = "Processing lines"
		status6 = "Creating footer"
		status7 = ""
		status8 = "Closing application"
		status9 = "Ready"
		break
	endcase
}

function long xml.prep(ref string inp.string)
{
	long x, result, a
	string	outp.string(255)
	string	c
	
	result = 0
	outp.string = ""

	for x = 1 to len(inp.string)
		c = inp.string(x; 1)
		a = asc(c)
		if a <> 32 and (a < 39 or (a > 57 and a < 65) or a > 122) then
			outp.string = outp.string & "&#" & str$(a) & ";"
			result = x
		else
			outp.string = outp.string & c
		endif
	endfor
	inp.string = strip$(outp.string)
	return(result)
}

function long load.indexes()
{
	long	rv	| return value
	long	x, y
	
	domain	ttadv.clab	clab

	string	field.name.t(15)
	string	field.name.f(15)

	rv = 0

	for x = 1 to 11
		form.index(1,x) = ""
		value.f(1,x) = ""
		value.t(1,x) = ""
		index.active(x) = ttyeno.no
	endfor
	
	select	*
	from	ttadv421
	where	ttadv421._compnr = 000
	and	ttadv421.cpac = :cpac.f
	and	ttadv421.cmod = :cmod.f
	and	ttadv421.flno = :flno.f
	and	ttadv421.vers = :version
	and	ttadv421.rele = :release
	and	ttadv421.cust = :customer
	and	ttadv421.acti = ttyeno.yes
	order by ttadv421.indn
	selectdo
		rv = 1
		| Add index fields to index array on form
		for x = 1 to 24
			if strip$(ttadv421.part(1, x)) <> "" and ttadv421.part(1,x;3) <> "c00" and ttadv421.part(1,x;3) <> "cmb" then
				for y = 1 to 11
					if form.index(1, y) = ttadv421.part(1, x) then
						y = 12
					else
						if strip$(form.index(1, y)) = "" then
							form.index(1, y) = ttadv421.part(1, x)
							clab = get.label(form.index(1, y))
							form.desc(1, y) = get.field.desc(lang, cpac.f, clab)
							form.type(y) = get.field.type(cpac.f & cmod.f & flno.f, ttadv421.part(1, x))
							y = 12
						endif
					endif
				endfor
			else
				x = 25
			endif
		endfor
	selectempty
		| No index found for this table (or table does not exist)
		rv = -1
	selecteos
	endselect

	for x = 1 to 11
		field.name.f = "value.f(" & str$(x) & ")"
		field.name.t = "value.t(" & str$(x) & ")"
		if strip$(form.index(1, x)) <> "" then
			form.from(1,x) = "From"
			inputfield.visible(field.name.f)
			form.to(1, x) = "To"
			inputfield.visible(field.name.t)
		else
			form.from(1,x) = ""
			inputfield.invisible(field.name.f)
			form.to(1, x) = ""
			inputfield.invisible(field.name.t)
		endif			
	endfor

	refresh()
	display.all()

	return(rv)
}

function domain ttadv.clab get.label(domain ttadv.fdnm field.name)
{
	domain ttadv.clab rv

	select	*
	from	ttadv422
	where	ttadv422._compnr = 000
	and	ttadv422.cpac = :cpac.f
	and	ttadv422.cmod = :cmod.f
	and	ttadv422.flno = :flno.f
	and	ttadv422.vers = :version
	and	ttadv422.rele = :release
	and	ttadv422.cust = :customer
	and	ttadv422.fdnm = :field.name
	selectdo
		if strip$(ttadv422.clab) <> "" then
			rv = ttadv422.clab
		else
			rv = cpac.f & cmod.f & ttadv422.flno & "." & ttadv422.fdnm
		endif
	selectempty
		rv = ""
	endselect
 
	return(rv)
}
Attached Files
File Type: zip miss.obj.zip (9.0 KB, 339 views)
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas

Last edited by Francesco : 10th October 2007 at 09:37.
Reply With Quote
  #7  
Old 10th October 2007, 09:34
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
Translations etc

While I'm at it:

Export tabel = Export table (bet you didn't see that one coming)
Conversie naar Excel XML formaat = Conversion to Excel XML format(1)
Maximaal aantal export regels = Maximum number of export lines (2)
Tijdvermelding op bestandsnaam = Time stamp on file name (3)

Veld = Field
Omschrijving = Description
Criteria = ...

So much for Dutch 1.01. Baan traditionals can change the English any way they like as long as it's no longer comprehensible ;)

(1) Uncheck this box to get a regular fast-n-dirty ASCII dump
(2) large tables can take a few minutes to process. This is the preview button
(3) The temporary file on the client's computer will be re-used every time to save on the clutter. Adding a unique time-stamp allows exports to be re-used or to do multiple exports without having to close excel each time (file in use)
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
  #8  
Old 11th October 2007, 00:06
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,934
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Thanks for the tool and the language lesson. :) I just used the script to rebuild the forms in english. I just tested on small sets.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #9  
Old 31st October 2007, 17:13
steventay steventay is offline
Senior Member
 
Join Date: Jul 2007
Posts: 173
steventay is on a distinguished road
Baan: Baan IVC4 SP10 - DB: SQL 2005 SP2 - OS: Windows 2003 SP2
can post the english copy.. how do i import in...? i am new in baan..
Reply With Quote
Sponsored Links
  #10  
Old 31st October 2007, 19:44
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,934
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Actually if I was you - I would just create a session and then copy the script into the new session. Then from the form picture and the script you can create your own form. The script was the important piece.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #11  
Old 14th November 2007, 13:02
suhas-mahajan's Avatar
suhas-mahajan suhas-mahajan is offline
Guru
 
Join Date: Jan 2003
Location: Pune
Posts: 458
suhas-mahajan is an unknown quantity at this point
Baan: IV C4 IN3, IN5, IS5, LN - DB: Oracle 8.0.5, 9i, Informix - OS: Win NT, 2K, 2K3, AIX
Hi Francesco,

Thanks for useful gift.

I am curious to know, how form two fields will be activated.

Please help.

regards,

-Suhas
Reply With Quote
  #12  
Old 14th November 2007, 15:27
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,934
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
When you click on form2 it does the load.indexes routine. This will load the form 2 fields. On form 2 you should have fields set up as arrays - like the attached (keep in mind I have added a few things). Hope I answered your question.
Attached Images
File Type: jpg Image2.jpg (73.4 KB, 333 views)
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #13  
Old 21st November 2007, 17:32
dthomson's Avatar
dthomson dthomson is offline
Junior Member
 
Join Date: Mar 2002
Location: Scotland
Posts: 12
dthomson is on a distinguished road
Baan: BaaN IVc4 - DB: Informix - OS: HPUX
Nice work, thanks for this. So far it has worked on all the table that I have tried.
__________________
Derek Thomson
Reply With Quote
  #14  
Old 24th July 2008, 21:34
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
This is wonderful.

Francesco: I have a request - where did you find all the documentation related to creating the XML output for EXCEL - is this in a textbook I can buy? Or is it all 'get your hands dirty' type of experience?

Thanks.

Last edited by shah_bs : 25th July 2008 at 16:16.
Reply With Quote
  #15  
Old 29th July 2008, 10:55
manojsharma's Avatar
manojsharma manojsharma is offline
Senior Member
 
Join Date: Sep 2002
Location: Delhi
Posts: 200
manojsharma is on a distinguished road
Baan: 4C4, 5, Baan LN - DB: Oracle,informix - OS: Unix, NT, Windows-2000
Hi

Nice utility but my question is what abt big tables like tfgld418 etc. which have millions of records
__________________
Together we can and we will make a difference
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not authorized to run as user baan positive Tools Administration & Installation 7 29th June 2004 09:56
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 22:55
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 20:44


All times are GMT +2. The time now is 07:12.


©2001-2017 - Baanboard.com - Baanforums.com