#!/usr/bin/perl
use strict;
use warnings;
use CGI;
use DBI;
use XML::Twig;
use CGI::Carp 'fatalsToBrowser';

my $cgi = CGI->new;
print $cgi->header(-type => "text", -charset => "utf-8");

# change to POSTDATA
my $xml = do { local $/; <DATA> };#$cgi->param("POSTDATA");

my $dashes = '-' x 50 ."\n";
print qq!XML received :
$dashes $xml
$dashes !;

my $username = "user";
my $password = "password";
my $dsn = "dbi:mysql:twig:localhost";
my $dbh = DBI->connect($dsn,$username,$password) or die "Cannot connect to database: $DBI::errstr";

my $sql1 = 'INSERT INTO twig_test(name,ppg,rpg,apg,g,blk) 
	       VALUES(?,?,?,?,?,?)';
my $sth1 = $dbh->prepare($sql1);

my $sql2 = 'INSERT INTO customer(first_name,last_name,dob,email) 
	       VALUES(?,?,?,?)';
my $sth2 = $dbh->prepare($sql2);

my $sql3 = 'INSERT INTO supplier(name,address,tel_no) 
	       VALUES(?,?,?)';
my $sth3 = $dbh->prepare($sql3);

#test
if($dbh){
	print "print successfully connected to the database\n$dashes";
}

my $twig = new XML::Twig(
		twig_handlers =>
		{player => \&player,
		customer => \&customer,
		supplier => \&supplier}
			);

$twig->parse($xml);
#$twig->print;

sub player {
  my ($twig,$player) = @_;
  my @f=();	
  for ('name','ppg','rpg','apg','g','blk'){
    push @f, $player->field($_);
    print "Player    $_ : $f[-1]\n";
  }
  $sth1->execute(@f) or die $DBI::errstr;
  print $dashes;
}

sub customer {
  my ($twig,$customer) = @_;
  my @f=();	
  for ('first_name','last_name','dob','email'){
    push @f, $customer->field($_);
    print "Customer $_ : $f[-1]\n";
  }
  $sth2->execute(@f) or die $DBI::errstr;
  print $dashes;
}

sub supplier {
  my ($twig,$supplier) = @_;
  my @f=();	
  for ('name','address','tel_no'){
    push @f, $supplier->field($_);
    print "Supplier $_ : $f[-1]\n";
  }
	$sth3->execute(@f) or die $DBI::errstr;
  print $dashes;
}

__DATA__
<?xml version="1.0"?>
<root>
<customer>
   <first_name>Frank</first_name>
   <last_name>Sanbeans</last_name>
   <dob>3/10</dob>
   <email>frank@example.com</email>
 </customer>

<stats>
<player><name>Houston,Allan</name><g>69</g><ppg>20.1</ppg><rpg>3.4</rpg><apg>2.8</apg><blk>14</blk></player>
<player><name>Sprewell, Latrell</name><g>69</g><ppg>19.2</ppg><rpg>4.5</rpg><apg>4.0</apg><blk>15</blk></player>
<player><name>Ewing, Patrick</name><g>49</g><ppg>14.6</ppg><rpg>10.0</rpg><apg>1.0</apg><blk>68</blk></player>
</stats>

<supplier-data>
 <supplier>
   <name>Supplier 1</name>
   <address>Address 1</address>
   <tel-no>012345</tel-no>
 </supplier>

</supplier-data>
 </root>