#!/usr/bin/perl 
# Converts an SQL database into a GW file for GeneWeb
#
# Written by Yann Corno - yann.corno@free.fr (feed-back to the author is welcome!)
# Home web site of this GeneWeb tool: http://www.corno.com/geneweb/ (make sure you check for the latest version there!)
#
# Last update: 2006-04-19
#
# Usage:
#
#    perl sql2gw.pl [-o outfile.gw] [-v|--verbose] [--utf8] [-q lastname]
#
#    -o outfile.gw:   name of the output GeneWeb file. If omitted, standard output is used.
#    -v|--verbose:    display progress. Only if -o option is used
#    --utf8:          insert "encoding: utf-8" at the beginning of the GW file. Use it if your SQL database contains UTF-8 data.
#    -q lastname:     output only families where one parent have this last name
#
#
# Example: the following example makes a complete transfer of a SQL database into a 
#          fresh GeneWeb one named "yourGeneWebDB":
#
#    perl sql2gw.pl -o YourGeneWebBase.gw
#    gwc YourGeneWebBase.gw -o yourGeneWebDB
#
# sql2gw converts a genealogical SQL database into a GW file (GeneWeb source file).
# 
# It is designed to work with the SQL table structures created by its counterpart, gw2sql. See below to
# learn about interfacing with your own database.
#
# There are several tables:
#  - gw_family: It contains the references of the husband and wife, and the GeneWeb family information. 
#               Each family is given a unique ID.
#  - gw_person: This contains the details for each person
#  - gw_family_child: This builds the children to family relationship. It contains the child references,
#               and the ID of the family.
#  - gw_family_witness: works exactly like gw_family_child, but for wedding witnesses
#  - gw_aliases: contains the alias, firstname alias, lastname alias, nickname and title of a person. This
#               is stored in a separate table because there can be several ones for the same person.
#  - gw_relationship: contains the relationship between two persons: God Father, Foster Mother, etc.
#  - gw_globaldata: contains misc. data: global notes of the database, etc.
#  - gw_extpages: extended pages
#
# Adapting sql2gw.pl to your own needs and database:
# --------------------------------------------------
#
# There are two things to do, which are performed by editing this script:
#
# 1) Specify your SQL connection parameters:
#    change the values of the $data_source, $data_user, $data_pass variables, so that the script can talk to your database
#
# 2) Most likely, you are using a database structure that is different from the gw2sql one. That's not a problem. Here is
#    how to proceed:
#
#      - Using the other script, gw2sql.pl, build the empty tables in your database:
#
#                  perl gw2sql.pl --nodata | mysql yourDatabase
#
#      - Then use SQL commands to copy data from your database to the gw_ tables. For example, let's suppose
#        that you have a 'persons' table, you can copy them into the 'gw_person' table using a simple INSERT/SELECT:
#
#             INSERT INTO gw_person (lastname, firstname) SELECT last_name, first_name FROM persons;
#
#        Once all your SQL commands are finished and tested, you should put them in a text file to be able to
#        run them automatically next time.
#
#
# Important: About referencing a person: Because the GW format does not provide the person GeneWeb ID, :-(
# we have to rely on the uniqueness of the triplet Lastname + Fistname + Number. Fortunately, GeneWeb
# makes a good job at it in its GW format, so it works pretty well. So the database solution was to create
# a primary key in the gw_person table that combines the 3 values together.
#
# About dates: as you might know if you read the GW documentation, the GeneWeb format for dates is
# quite extensive. To address most uses, the dates fields are doubled. The GeneWeb date is put 'as is'
# in a first field. Then, if the format is a simple and complete one (i.e. dd/mm/yyyy), it is copied
# in SQL format in a second field. See the 'birth_date' and 'birth_date_sql' fields as an example. These
# '*_sql' fields are indexed, and will allow easy searches. If the GeneWeb date could not be converted,
# the SQL field contains NULL.
#
# About unknown persons: GeneWeb uses a question mark '?' to indicate an unknown last name or first name.
# To ensure uniqueness of persons, it is assumed that each such person is a different one from the others.
# Therefore, in the SQL gw_person table, the last name and/or first name are given a unique number after the '?'.
# Such names are converted back into a single '?' in the gw file.
#
#
#
# !!! TO DO !!!
#	- help/usage option
#	- No gender check is not supported for the moment (#noment, #nsck mf or #nsckm ??)
#
# This software can be used under the standard GPL licence. Use at your own risks. 
# http://cristal.inria.fr/%7Eddr/GeneWeb/LICENCE.htm
#
# 2005-10-01: started coding
# 2006-04-12: dropped the metadata system (was too complicated). We will rely on SQL commands, which is much more simple.
# 2006-04-13: first version to pass the gwc test. Released for testing.
# 2006-04-14: added --utf8 and -q options. Fixed placement of #bs tag. Fixed children names with starting with ?.
# 2006-04-15: fixed a side effect of the -q option, which was creating undefined persons in GeneWeb
# 2006-04-16: added support for wedding witnesses. Note: I did not used the "wit m" and "wit f" tags, because it would costs two extra SQL queries. gwc works fine anyway.
# 2006-04-17: added support for persons' notes.
# 2006-04-18: added support for extended pages, global notes ("notes"), Wizard notes ("wizard-note") and db notes ("notes-db")
# 2006-04-19: added gender to witnesses (found a way to specify gender at no extra cost)


use Getopt::Long;
use DBI;

#-- Global constants - Change these to your own needs

$data_source = "DBI:mysql:database=test;host=localhost"; # Database location
$data_user = "";		# Database user
$data_pass = "";		# Database user password



#-- Global variables

$encoding = '';				# Use default encoding for now.
%people_described = ();		# This is a list of people we already described fully, so that we do not do it again
$lastname_filter = '';		# Default: no last name filter

#-- Functions

sub printArray
{ # debug proc to print an array
	my (@arr) = @_;
	my $idx = 0;
	foreach (@arr) {
		print "[".$idx."] ".$_."\n";
		$idx++;
	}
}

sub trim
{ # trim string (remove all spaces at the beginning and the end)
	my ($str) = @_;
	$str =~ s/^\s+//;
	$str =~ s/\s+$//;
	return $str;
}

sub replaceQuote
{ # Replace single quotes ' by \', so that they are acceptable by SQL
	my ($str) = @_;
	$str =~ s/'/\\'/g;
	return $str;
}

sub replace_
{ # Replace all underscores '_' by spaces ' ', and single quotes by backslashed ones (like replaceQuote())
	my ($str) = @_;
	$str =~ s/([^\\]|^)_/$1 /g;
	$str =~ s/\\_/_/g;				# We must retain '\_' sequences, and turn them into '_'
	$str =~ s/'/\\'/g;
	return $str;
}

sub replace_unknown
{ # Add an index in the name is '?', so that it becomes unique
	my ($str) = @_;
	if ($str eq '?')
	{
		$str .= $unknownIndex;
		$unknownIndex++;
	}
	return $str;
}

sub replace_space
{ # Replace spaces by underscores; underscores by '\_' and \ by \\
	my ($str) = @_;
	$str =~ s/\\/\\\\/g;
	$str =~ s/_/\\_/g;
	$str =~ s/\s/_/g;
	return $str;
}

sub addslashes
{ # Add slashes for ' for SQL queries
	my ($str) = @_;
	$str =~ s/'/\\'/g;
	return $str;
}

sub bq
{ # Boolean query: return true if the query returned a non-empty parameter
	# Parameters:
	#	$query: string
	my ($query) = @_;
	
	my $sth = $dbh->prepare($query) or die "bq(): Could not prepare query: $query\n";
	$sth->execute();
	if ($sth->fetchrow_array)
	{
		$sth->finish();
		return 1;
	}
	else
	{
		$sth->finish();
		return 0;
	}
}

sub q_singleobj
{ # Single row query. Just take the first record of the query
	# Parameters:
	#	$query: string
	# Return a reference on the resulting hash
	my ($query) = @_;
	
	my $sth = $dbh->prepare($query) or die "q_singleobj(): Could not prepare query: $query\n";
	$sth->execute();
	my $result = $sth->fetchrow_hashref();
	$sth->finish();
	return $result;
}

sub q_singleval
{ # Single value query. Just take the first value of the first record of the query
	# Parameters:
	#	$query: string
	# Return a value
	my ($query) = @_;
	
	my $sth = $dbh->prepare($query) or die "q_singleval(): Could not prepare query: $query\n";
	$sth->execute();
	my $row = $sth->fetchrow_array();
	my $result = $row[0];
	$sth->finish();
	return $result;
}

sub has_parents
{ # Check is a person already belongs to a family of not. Use global filter '$lastname_filter'
	# Parameters:
	#	$lastname:
	#	$firstname:
	#	$number
	my ($lastname, $firstname, $number) = @_;
	
	my $hasParents;
	if ($lastname_filter eq '')
	{
		$hasParents = bq("SELECT familyID FROM gw_family_child WHERE lastname='".addslashes($lastname)."' AND firstname='".addslashes($firstname)."' AND number='$number' ");
	}
	else
	{
		$hasParents = bq("SELECT familyID 
			FROM 
				gw_family_child as c,
				gw_family as f
			WHERE 
				lastname='".addslashes($lastname)."' AND firstname='".addslashes($firstname)."' AND number='$number' AND
				f.id=c.familyID AND (h_lastname='".addslashes($lastname_filter)."' OR w_lastname='".addslashes($lastname_filter)."') 
			");
	}
	
	return $hasParents;
}

sub print_person_data
{ # Print a person's data (except his/her name)
	# Parameters: reference to a hash table of the struct_person format
	my ($row_person) = @_;
	my $sth = null;
	
	$sth = $dbh->prepare("SELECT alias_type, alias_value FROM gw_aliases WHERE lastname='".addslashes($row_person->{'lastname'})."' AND firstname='".addslashes($row_person->{'firstname'})."' AND number='".$row_person->{'number'}."' ") or die "print_person_data(): Could not prepare alias query\n";
	$sth->execute();
	while(my $row = $sth->fetchrow_array())
	{
		if ($row[0] eq 'alias')
		{ # Alias: #alias
			print FO ' #alias '.replace_space($row[1]);
		}
		elsif ($row[0] eq 'firstname_alias')
		{ # Firstname alias: {}
			print FO ' {'.replace_space($row[1]).'}';
		}
		elsif ($row[0] eq 'lastname_alias')
		{ # Lastname alias: #salias
			print FO ' #salias '.replace_space($row[1]);
		}
		elsif ($row[0] eq 'nickname')
		{ # Nickname: #nick
			print FO ' #nick '.replace_space($row[1]);
		}
		elsif ($row[0] eq 'title')
		{ # Titles: []
			print FO ' ['.replace_space($row[1]).']';
		}
	} # while
	$sth->finish();
	
	# Public name: ()
	if ($row_person->{'public_name'} ne '')
	{
		print FO ' ('.replace_space($row_person->{'public_name'}).')';
	}
	
	# Image: #image
	if ($row_person->{'image'} ne '')
	{
		print FO ' #image '.replace_space($row_person->{'image'});
	}

	# Access: #apubl, #apriv
	if ($row_person->{'access'} ne '')
	{
		print FO ' #'.replace_space($row_person->{'access'});  # Should contain #apubl or #apriv
	}
	
	# Occupation: #occu
	if ($row_person->{'occupation'} ne '')
	{
		print FO ' #occu '.replace_space($row_person->{'occupation'});
	}
	
	# PersonSource: #src
	if ($row_person->{'source'} ne '')
	{
		print FO ' #src '.replace_space($row_person->{'source'});
	}

	# Birth date
	if ($row_person->{'birth_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
	{
		print FO " $3/$2/$1";
	}
	elsif ($row_person->{'birth_date'} ne '')
	{
		print FO " ".replace_space($row_person->{'birth_date'});
	}
	else
	{
		print FO " 0";	# Minimum: 0
	}

	# BirthPlace: #bp
	if ($row_person->{'birth_place'} ne '')
	{
		print FO ' #bp '.replace_space($row_person->{'birth_place'});
	}

	# BirthSource: #bs
	if ($row_person->{'birth_source'} ne '')
	{
		print FO ' #bs '.replace_space($row_person->{'birth_source'});
	}

	# BaptizeDate: !
	if ($row_person->{'baptize_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
	{
		print FO " !$3/$2/$1";
	}
	elsif ($row_person->{'baptize_date'} ne '')
	{
		print FO " !".replace_space($row_person->{'baptize_date'});
	}

	# BaptizePlace: #pp
	if ($row_person->{'baptize_place'} ne '')
	{
		print FO ' #pp '.replace_space($row_person->{'baptize_place'});
	}

	# BaptizeSource: #ps
	if ($row_person->{'baptize_source'} ne '')
	{
		print FO ' #ps '.replace_space($row_person->{'baptize_source'});
	}

	# Death date
	if ($row_person->{'death_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
	{
		print FO " $3/$2/$1";
	}
	elsif ($row_person->{'death_date'} ne '')
	{
		print FO " ".replace_space($row_person->{'death_date'});
	}

	# PlaceOfDeath: #dp
	if ($row_person->{'death_place'} ne '')
	{
		print FO ' #dp '.replace_space($row_person->{'death_place'});
	}

	# DeathSource: #ds
	if ($row_person->{'death_source'} ne '')
	{
		print FO ' #ds '.replace_space($row_person->{'death_source'});
	}

	# Burial or Cremation: #buri|#crem [Date]
	if ($row_person->{'burial'} ne '')
	{
		print FO ' #'.replace_space($row_person->{'burial'});	# #buri or #crem
		if ($row_person->{'burial_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
		{
			print FO " $3/$2/$1";
		}
		elsif ($row_person->{'burial_date'} ne '')
		{
			print FO " ".replace_space($row_person->{'burial_date'});
		}
	}

	# BurialPlace: #rp
	if ($row_person->{'burial_place'} ne '')
	{
		print FO ' #rp '.replace_space($row_person->{'burial_place'});
	}

	# BurialSource: #rs
	if ($row_person->{'burial_source'} ne '')
	{
		print FO ' #rs '.replace_space($row_person->{'burial_source'});
	}

}

sub print_person
{ # Print a person
	# Parameters:
	#	$lastname:
	#	$firstname:
	#	$number
	#	$isParent:	1 if it should be in parent format, 0 for a child. If the person belongs to another family, only a reference will be outputed
	#	$father_lastname:	if not $isParent, last name of the father (for default naming)
	my ($lastname, $firstname, $number, $isParent, $father_lastname) = @_;
	
	if ($number eq '')
	{
		$number = '0';
	}
	
	my $row_person = q_singleobj("SELECT * FROM gw_person WHERE lastname='".addslashes($lastname)."' AND firstname='".addslashes($firstname)."' AND number='$number' ");
	
	if ($lastname =~ /\?\d+/)
	{ # Unknown person
		$lastname = '?';
	}
	if ($firstname =~ /\?\d+/)
	{ # Unknown person
		$firstname = '?';
	}
	
	if ($isParent)
	{ # Parent format
		print FO replace_space($lastname) . " " . replace_space($firstname);
		if ($number ne '0')
		{
			print FO "." . $number;
		}

		if ($lastname ne '?' && $firstname ne '?')
		{
			if ($row_person && !has_parents($lastname, $firstname, $number) && !$people_described{$lastname.$firstname.'.'.$number}) 
			{ # This parent does not belong to a family, and was not already described, so print all his/her data
				print_person_data($row_person);
				$people_described{$lastname.$firstname.'.'.$number} = 1;	# We keep it in a list, so that we do not do it again
			}
		}
	}
	else
	{ # Child format
		if ($row_person)
		{
			if ($row_person->{'gender'} eq 'male')
			{
				print FO " h";
			}
			elsif ($row_person->{'gender'} eq 'female')
			{
				print FO " f";
			}
		}
		
		print FO " " . replace_space($firstname);
		if ($number ne '0')
		{
			print FO "." . $number;
		}
		if ($father_lastname ne $lastname)
		{
			print FO " ". replace_space($lastname);
		}
		if ($row_person) 
		{
			print_person_data($row_person);
		}
	}
} #print_person

sub print_family_data
{ # Print the family data
	# Parameters:
	#	$row: the database record of the family
	my ($row) = @_;

	print FO " +";
	
	# Wedding date. Right after the "+"
	if ($row->{'wedding_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
	{
		print FO "$3/$2/$1";
	}
	elsif ($row->{'wedding_date'} ne '')
	{
		print FO replace_space($row->{'wedding_date'});
	}
	
	#-- Separated: #sep
	if ($row->{'separated'})
	{
		print FO " #sep";
	}

	#-- Not Married: #nm
	if ($row->{'not_married'})
	{
		print FO " #nm";
	}
	
	#-- Engaged: #eng
	if ($row->{'engaged'})
	{
		print FO " #eng";
	}

	#-- Wedding place: #mp
	if ($row->{'wedding_place'} ne '')
	{
		print FO " #mp " . replace_space($row->{'wedding_place'});
	}
	
	#-- Wedding source: #ms
	if ($row->{'wedding_source'} ne '')
	{
		print FO " #ms " . replace_space($row->{'wedding_source'});
	}

	#-- Divorce: -[date]
	if ($row->{'divorced'})
	{
		print FO " -";
		if ($row->{'divorce_date_sql'} =~ /^(\d+)-(\d+)-(\d+)$/)
		{
			print FO "$3/$2/$1";
		}
		elsif ($row->{'divorce_date'} ne '')
		{
			print FO replace_space($row->{'divorce_date'});
		}
	}
	
	print FO " ";
} #print_family_data


#-- Initializations

my $outfile = '-';	# Default: stdout
my $verbose = 0;	# Default: non verbose mode
my $utf8_mode = 0;	# Default: no utf-8
$lastname_filter = '';	# Default: no last name filter

my %options;
my $result = GetOptions(
						"o=s" => \$outfile,
						"verbose|v" => \$verbose,
						"utf8" => \$utf8_mode,
						"q=s" => \$lastname_filter
						);
if ($outfile eq '-') 
{
	$verbose = 0;	# We do not want to screw up output with verbose info
}

open (FO, ">".$outfile) || die "Cannot open '$outfile' because of $!\n";

if ($utf8_mode)
{
	print FO "encoding: utf-8\n";
}

#-- Connect to DB

$dbh = DBI->connect($data_source, $data_user, $data_pass) or die "Could not connect to DB\n";


#-- Output all families
my $sth;

if ($lastname_filter eq '')
{
	$sth = $dbh->prepare("SELECT * FROM gw_family ORDER BY 'id' ")  or die "Could not prepare query #1\n";
}
else
{ # We are using a last name filter
	$sth = $dbh->prepare("SELECT * FROM gw_family WHERE h_lastname='".addslashes($lastname_filter)."' OR w_lastname='".addslashes($lastname_filter)."' ORDER BY 'id' ")  or die "Could not prepare query #1\n";
}
$sth->execute();
while (my $row = $sth->fetchrow_hashref())
{
	my $familyID = $row->{'id'};
	if ($verbose)
	{
		print "#".$row->{'id'}.": ". $row->{'h_lastname'} ." ". $row->{'h_firstname'} .".". $row->{'h_number'} . " + " . $row->{'w_lastname'} ." ". $row->{'w_firstname'} .".". $row->{'w_number'} ."\n";
	}
	print FO "fam ";
	print_person($row->{'h_lastname'}, $row->{'h_firstname'}, $row->{'h_number'}, 1, '');
	print_family_data($row);
	print_person($row->{'w_lastname'}, $row->{'w_firstname'}, $row->{'w_number'}, 1, '');
	print FO "\n";
	#-- Family Witnesses
	my $sthw = $dbh->prepare("SELECT w.*, p.gender FROM gw_family_witness as w, gw_person as p WHERE w.familyID='$familyID' AND p.lastname=w.lastname AND p.firstname=w.firstname AND p.number=w.number ")  or die "Could not prepare witness query\n";
	$sthw->execute();
	while (my $row_witness = $sthw->fetchrow_hashref())
	{
		for ($row_witness->{'gender'})
		{
			$_ eq 'male' && print FO "wit m: ";
			$_ eq 'female' && print FO "wit f: ";
			$_ eq '' && print FO "wit: ";
		}
		print_person($row_witness->{'lastname'}, $row_witness->{'firstname'}, $row_witness->{'number'}, 1, '');
		print FO "\n";
	}#while
	$sthw->finish();
	#-- Family Source
	if ($row->{'source'} ne '')
	{
		print FO "src " . replace_space($row->{'source'}) . "\n";
	}
	#-- Family Comments
	if ($row->{'comments'} ne '')
	{
		print FO "comm " . $row->{'comments'} . "\n";
	}
	#-- Get children
	my $childrenCount = 0;
	my $sth2 = $dbh->prepare("SELECT * FROM gw_family_child WHERE familyID='$familyID' ")  or die "Could not prepare query #2\n";
	$sth2->execute();
	while (my $row_child = $sth2->fetchrow_hashref())
	{
		if ($childrenCount == 0)
		{
			print FO "beg\n";
		}
		print FO "-";
		print_person($row_child->{'lastname'}, $row_child->{'firstname'}, $row_child->{'number'}, 0, $row->{'h_lastname'});
		print FO "\n";
		$childrenCount++;
	}#while
	$sth2->finish();
	if ($childrenCount>0)
	{
		print FO "end\n";
	}
	
	
	print FO "\n";
}#while
$sth->finish();

#-- Output all relationships
if ($lastname_filter eq '')
{
	$sth = $dbh->prepare("SELECT * FROM gw_relationship ORDER BY lastname, firstname, number")  or die "Could not prepare relationships query #1\n";
}
else
{ # We are using a last name filter
	$sth = $dbh->prepare("SELECT * FROM gw_relationship WHERE lastname='".addslashes($lastname_filter)."' ORDER BY lastname, firstname, number")  or die "Could not prepare relationships query #2\n";
}
$sth->execute();
my $last_person = '';
while (my $row_rel = $sth->fetchrow_hashref())
{
	if ($last_person ne ($row_rel->{'lastname'} . " " . $row_rel->{'firstname'} . "." . $row_rel->{'number'}))
	{
		if ($last_person ne '')
		{
			print FO "end\n\n";
		}
		print FO "rel " . replace_space($row_rel->{'lastname'}) . " " . replace_space($row_rel->{'firstname'});
		if ($row_rel->{'number'} ne '0')
		{
			print FO "." . $row_rel->{'number'};
		}
		print FO "\nbeg\n";
		$last_person = $row_rel->{'lastname'} . " " . $row_rel->{'firstname'} . "." . $row_rel->{'number'};
	}
	print FO "- " . $row_rel->{'rel_type'} . ": ";
	print_person($row_rel->{'rel_lastname'}, $row_rel->{'rel_firstname'}, $row_rel->{'rel_number'}, 1, '');
	print FO "\n";
}#while
if ($last_person ne '')
{
	print FO "end\n\n";
}
$sth->finish();

#-- Output all notes
if ($lastname_filter eq '')
{
	$sth = $dbh->prepare("SELECT * FROM gw_person WHERE notes is not null AND notes != '' ")  or die "Could not prepare notes query #1\n";
}
else
{ # We are using a last name filter
	$sth = $dbh->prepare("SELECT * FROM gw_person WHERE notes is not null AND notes != '' AND lastname='".addslashes($lastname_filter)."' ")  or die "Could not prepare notes query #2\n";
}
$sth->execute();
while (my $row_notes = $sth->fetchrow_hashref())
{
	print FO "notes " . replace_space($row_notes->{'lastname'}) . " " . replace_space($row_notes->{'firstname'});
	if ($row_notes->{'number'} ne '0')
	{
		print FO "." . $row_notes->{'number'};
	}
	print FO "\nbeg\n";
	print FO $row_notes->{'notes'} . "\n";
	print FO "end notes\n\n";
}#while
$sth->finish();

#-- Output Global notes
$sth = $dbh->prepare("SELECT * FROM gw_globaldata WHERE name='Global Notes' ")  or die "Could not prepare Global notes query\n";
$sth->execute();
while (my $row_gnotes = $sth->fetchrow_hashref())
{
	print FO "notes\nbeg\n";
	print FO $row_gnotes->{'data3'} . "\n";
	print FO "end notes\n\n";
}#while
$sth->finish();

#-- Output database notes
$sth = $dbh->prepare("SELECT * FROM gw_globaldata WHERE name='notes-db' ")  or die "Could not prepare Global notes query\n";
$sth->execute();
while (my $row_dbnotes = $sth->fetchrow_hashref())
{
	print FO "notes-db\n";
	print FO $row_dbnotes->{'data3'} . "\n";
	print FO "end notes-db\n\n";
}#while
$sth->finish();

#-- Output Wizard notes
$sth = $dbh->prepare("SELECT * FROM gw_globaldata WHERE name='wizard-note' ")  or die "Could not prepare Global notes query\n";
$sth->execute();
while (my $row_wiznotes = $sth->fetchrow_hashref())
{
	print FO "wizard-note " . replace_space($row_wiznotes->{'data1'}) . "\n";
	print FO $row_wiznotes->{'data3'} . "\n";
	print FO "end wizard-note\n\n";
}#while
$sth->finish();

#-- Output all extended pages
$sth = $dbh->prepare("SELECT * FROM gw_extpage ")  or die "Could not prepare ext pages query\n";
$sth->execute();
while (my $row_page = $sth->fetchrow_hashref())
{
	print FO "page-ext " . replace_space($row_page->{'name'}) . "\n";
	print FO $row_page->{'data'} . "\n";
	print FO "end page-ext\n\n";
}#while
$sth->finish();

#-- Clean-up
$dbh->disconnect();
close FO;