#C:\PERL\bin\perl.exe -w

#****PACKAGES****#

use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
use XML::Parser;

##****END PACKAGES****#
#****VARIABLES****#

my $username = 'system';
my $password = '';
my $database = "dbi:Oracle:<DB NAME>";
my $oradbh;
my $xmlSQL;
my $sqlXML;
my $xmldatafile = "E:\\<companyname>\\PERLFiles\\trash\\xmldata.xml";
my $datadump = "E:\\<companyname>\\PERLFiles\\trash\\extract.txt";
my $idNum=1;
my $driverCount=0;
my $vehicleCount=0;
my @policyInfo;
my @driversInfo;
my @vehiclesInfo;

#****END VARIABLES****#
#****PROGRAM****#

$oradbh = DBI->connect($database,$username,$password,
                       {RaiseError => 1,
                        PrintError => 0})
        or die ($DBI::errstr, "\n");

#Sets the Long Read Length to accomendate the XML in the RFI_XML column
$oradbh -> {LongReadLen} = 30000*256;

#SQL setup to extract the XML data from the 8i database
$xmlSQL = qq(select rfi_xml from webrater_owner.quote where rownum between 1 and 50);
$sqlXML = $oradbh->prepare($xmlSQL);
$sqlXML -> execute();
my ($xml_sql_data);
$sqlXML -> bind_columns(undef, \$xml_sql_data);

#Write out all the XML data from RFI_XML to a text/xml file
open(FILE,"> $xmldatafile") or die "Unable to open $xmldatafile : $!\n";

while($sqlXML -> fetch())
{
    print FILE $xml_sql_data;
}

close(FILE) or warn "Unable to close $xmldatafile: $!\n";

$sqlXML -> finish(); #Finish the SQL command
$oradbh -> disconnect; #Disconnect from the database

open(FILE, "$xmldatafile") or die "Unable to open $xmldatafile : $!\n";
open(FILE2, "> $datadump") or die "Unable to open $datadump : $!\n";
print FILE2 "IDNum|Quote#|Effective Date|Last Name|First Name|Address|City|State|Zip|Driver0 Last Name|Driver0 First Name|Sex|Marital Status|DOB|License Number|License State|Status|Driver#|Year|Make|Model|VIN|Vehicle#\n";

while(<FILE>)
{
    if (/^<rfi>/)
    {
           #print "new record set\n";
    }  
    elsif(m[(<.*?>)(.*?)(</.*?>)]x) #$1..$3
    {
        if ($1 eq '<WebId>')
        {
            $policyInfo[0] = $2;
        }
        if ($1 eq '<effectiveDate>')
        {
            $policyInfo[1] = $2;
        }
        if ($1 eq '<LastName>')
        {
            $policyInfo[2] = $2;
        }
        if ($1 eq '<FirstName>')
        {
            $policyInfo[3] = $2;
        }
        if ($1 eq '<applicantAddress1>')
        {
            $policyInfo[4] = $2;
        }
        if ($1 eq '<applicantCity>')
        {
            $policyInfo[5] = $2;
        }
        if ($1 eq '<applicantState>')
        {
            $policyInfo[6] = $2;
        }
        if ($1 eq '<applicantZip>')
        {
            $policyInfo[7] = $2;
        }

            *****PROBLEM START*****
            if (/^<driver id=/)  #get driver info
            {
                print "found driver\n";
                if($driverCount > 0)
                {
                    #$driverCount++;
                    @driversInfo = (); #clear the array for driverInfo
                    print "array cleared\n";
                }
            }
            elsif(m[(<.*?>)(.*?)(</.*?>)]x)
            {
                if($1 eq '<lastName>')
                {
                    $driversInfo[0] = $2;
                }
                if($1 eq '<firstName>')
                {
                    $driversInfo[1] = $2;
                }
                if($1 eq '<sex>')
                {
                    if($2 eq 'male')
                    {
                        $driversInfo[2] = 'M';
                    }
                    else
                    {
                        $driversInfo[2] = 'F';
                    }
                }
                if($1 eq '<maritalStatus>')
                {
                    $driversInfo[3] = $2;
                }
                if($1 eq '<Birth-Date>')
                {
                    $driversInfo[4] = $2;
                }
                if($1 eq '<License-No>')
                {
                    $driversInfo[5] = $2;
                }
                if($1 eq '<License-State>')
                {
                    $driversInfo[6] = $2
                }
                if($1 eq '<Rate-Status>')
                {
                    $driversInfo[7] = $2;
                }
            }
            elsif(/<\/driver>/)
            {
                $driverCount++;
                print "New Driver Number $driverCount\n";
            }
            *****PROBLEM END*****

            if(/^<vehicle /)  #Get vehicle information
            {
                #Left blank for a reason
            }
            elsif(m[(<.*?>)(.*?)(</.*?>)]x)
            {
                if($1 eq '<year>')
                {
                    $vehiclesInfo[0] = $2;
                }
                if($1 eq '<make>')
                {
                    $vehiclesInfo[1] = $2;
                }
                if($1 eq '<model>')
                {
                    $vehiclesInfo[2] = $2;
                }
                if($1 eq '<vin>')
                {
                    $vehiclesInfo[3] = $2;
                }
            }
            elsif(/^<\/vehicle>/)
            {
                #Left blank for a reason
            }
            
        
        #  print "$quote|$effectdate|$lastname|$firstname|$appAdd|$appCity|$appState|$appZip\n";
        }
    elsif(/^<\/rfi>/)
    {
        print "\n@policyInfo\n";
        print "Driver Number: $driverCount\n";
        print "@driversInfo\n";
        print "Vehicle Number: $vehicleCount\n";
        print "@vehiclesInfo\n";
        #print "$idNum|$policyInfo[0]|$policyInfo[1]|$policyInfo[2]|$policyInfo[3]|$policyInfo[4]|$policyInfo[5]|$policyInfo[6]|$policyInfo[7]|$driversInfo[0]|$driversInfo[1]|$driversInfo[2]|$driversInfo[3]|$driversInfo[4]|$driversInfo[5]|$driversInfo[6]|$driversInfo[7]|$driverCount|$vehiclesInfo[0]|$vehiclesInfo[1]|$vehiclesInfo[2]|$vehiclesInfo[3]|$vehicleCount\n";
        #$idNum++;


    }   
}

close(FILE);
close(FILE2);

#****END PROGRAM****#