#!/perl/bin/perl

use strict;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::XPath;
use XML::XPath::XMLParser;

print header();

my ($replication,$part_no,$add_sample_ref,$fname,$carr,$carr_nr,$overlay,$billa,$billb,$percent,$rref);
my (@replication,@part_no,@add_sample_ref,@fname,@carr,@carr_nr,@overlay,@billa,@billb,@percent,@rref);



my $xp = XML::XPath->new(filename => 'template.xml');

foreach my $row ($xp->findnodes('/CITY/replication')) {

 # extracting from the XML 
 $replication = $row->find('//@REFA')->string_value;
 $part_no = $row->find('Part_No')->string_value;

	    # Start
	    my $nodeset = $row->find('AdCar');
	    foreach my $node ( $nodeset->get_nodelist ) {
	    
	       $add_sample_ref = $node->find( '@Ref' )->string_value;
	       $fname = $node->find( 'FName')->string_value;
               
               push @add_sample_ref,$add_sample_ref;
               #push @
               
	    } #End

	    # Start
	    my $node_carr = $row->find('AbCarr');

	    foreach my $node ( $node_carr->get_nodelist ) {

		       $carr = $node->find( '@RefD' )->string_value;
		       $carr_nr = $node->find('Lic')->string_value;
                       
                       #push @
                       #push @
            
            	       # Start
	    	       my $node_overlay = $row->find('AbCarr/Warr');
	    
	    	       foreach my $node ( $node_overlay->get_nodelist ) {
	    
	    			  $overlay = $node->find('@VD')->string_value;    
	    			  $billa = $node->find('Billa1')->string_value;
	    			  $billb = $node->find('Billb2')->string_value;			 
                                   
                                  #push @
                                  #push @
                                  #push @
                                  
                       } # End
                       
                       
		       # Start
		       my $node_percent = $row->find('AbCarr/ADcar');

		       foreach my $node ( $node_percent->get_nodelist ) {

				  $percent = $node->find('@REFI')->string_value;    
				  $rref = $node->find('Carr')->string_value;
				  
				  #push @
				  #push @
		       } #End
            
            



            } #End AbCarr
            
            
push @replication, $replication;
} #End replication



#Get all results from the parsing and input all of it into a DB.

my $table = 'test';
my @fields = qw( replication part_no add_sample_ref fname carr carr_nr overlay billa billb percent rref );

my $fields = join(', ', @fields);

my $values = join(', ', map { $dbh->quote($_) } @all_my_data_here{@fields});

$sql = "INSERT into $table ($fields) values ($values)";

$sth = $dbh->prepare($sql);
$sth->execute();
$sth->finish();