#!/usr/local/bin/perl 

use DBI;

my $dsn = "DBI:mysql:compound:localhost";	# data source
my $user_name = "chem";						# user name
my $password = "chem";						# password
my $file = 'temp.mol';
my $smi_file = 'temp.smi';

# connect to database
my $dbh = DBI->connect( $dsn, $user_name, $password,
						{ RaiseError => 1, PrintError => 0 } );

$sth = $dbh->prepare(qq{SELECT mol_id FROM molecule WHERE num_atoms > 0});
$sth->execute();

my $rows = $dbh->do( qq{
					UPDATE molecule
					SET SMILES = NULL } );
					
my $matrix_ref = $sth->fetchall_arrayref();

my $mol_rows = (!defined ($matrix_ref) ? 0 : scalar( @{$matrix_ref}));

$intro = "\n\n\n";
$cnt_line = "  0  0  0  0  0  0  0  0  0";
$atom_line = " 0  0  0  0  0  0  0  0  0  0  0  0";
$bond_line = "  0  0  0  0"; 
$end_line = "M END";

$sth = $dbh->prepare(qq{SELECT mol_id, num_atoms, num_bonds FROM molecule WHERE num_atoms > 0});
$sth->execute();

for( $i = 1; $i<= $mol_rows; $i++ )
{

	my @ary = $sth->fetchrow_array();
	$mol_id=$ary[0]; 
	$num_atoms = $ary[1];
	$num_bonds = $ary[2];
	
	open OUTPUT_FILE, ">$file";
	
	print OUTPUT_FILE $intro;

	printf OUTPUT_FILE "%3d%3d%s\n", $num_atoms, $num_bonds, $cnt_line ;

	my $sth_a = $dbh->prepare(qq{
						SELECT sequence, x, y, z, atom_type FROM atom 
						WHERE mol_id = $mol_id
						ORDER BY sequence });
	$sth_a->execute();

	for( $atom_cnt=1; $atom_cnt<=$num_atoms; $atom_cnt++ )
	{
		my @ary = $sth_a->fetchrow_array();
		my $x = $ary[1];
		my $y = $ary[2];
		my $z = $ary[3];
		my $atom_type = $ary[4];

		printf OUTPUT_FILE "%10.4f%10.4f%10.4f %-3s%s\n", $x, $y, $z, $atom_type, $atom_line;
	}	
	
	my $sth_a = $dbh->prepare(qq{
						SELECT mol_id, atom_1, atom_2, bond_type 
						FROM bond
						WHERE mol_id = $mol_id });
	$sth_a->execute();

	for( $bond_cnt=1; $bond_cnt<=$num_bonds; $bond_cnt++ )
	{
		my @ary = $sth_a->fetchrow_array();
		my $atom_1 = $ary[1];
		my $atom_2 = $ary[2];
		my $bond_type = $ary[3];

		printf OUTPUT_FILE "%3d%3d%3d%s\n", $atom_1, $atom_2, $bond_type, $bond_line;
	}
	
	print OUTPUT_FILE "$end_line\n";
	close(OUTPUT_FILE);

	system( "babel16 -imdl $file -osmiles $smi_file" );

	open INPUT_FILE, "<$smi_file";

	my $smiles = <INPUT_FILE>;
	chomp($smiles);

	$smiles = $dbh->quote($smiles);
	$rows = $dbh->do( qq{
					UPDATE molecule
					SET SMILES = $smiles
					WHERE mol_id = $mol_id } );	

	close(INPUT_FILE);
	print '.';
}

$sth->finish();
$dbh->disconnect();
exit(0);