#!perl -Tw

use strict;

use DBI;
use Win32::OLE;
use XML::Generator;

my $list_id = '40000-1022206382';
my $request_id = 1;

# Be paranoid about error messages
Win32::OLE->Option(Warn => 3);
    
# Start the Processor
my $connection = Win32::OLE->new('QBXMLRP.RequestProcessor');
    
# Name your program
$connection->OpenConnection("qbtmr", "Timer Import");

# Open the currently open QuickBooks file, in "DoNotCare" mode.
my $ticket = $connection->BeginSession('', 2);

my $dbh = DBI->connect("DBI:CSV:") 
    or die "Cannot connect: " . $DBI::errstr;

my $sth = $dbh->prepare("SELECT Customer, Service, Duration, Date FROM data")
    or die "Cannot prepare: " . $dbh->errstr();

$sth->execute() or die "Cannot execute: " . $sth->errstr();

$sth->bind_columns(\my($customer, $service, $duration, $date));

my @items;

while ($sth->fetch) {
    push (@items, {
        date    =>    $date,
        customer=>    $customer,
        service    =>    $service,
        duration=>    $duration
    });
}
$sth->finish();
$dbh->disconnect();

foreach my $item (@items) {
    my $gen = XML::Generator->new(
        escape        =>    'always',
        conformance    =>    'strict',
        pretty        =>    2,
        dtd            =>    [ 'QBXML', 'PUBLIC', "'-//INTUIT//DTD", "QBXML QBD 1.1//EN'"],
        version        =>    '1.0'
    );
    
    my $xml = $gen->xmldecl;
    $xml =    $gen->QBXML(
        $gen->QBXMLMsgsRq(
            {'onError' => 'stopOnError'},
            $gen->TimeTrackingAddRq({requestID => 1},
                $gen->TimeTrackingAdd(
                    $gen->TxnDate($item->{date}),
                    $gen->EntityRef(
                        $gen->ListID($list_id)
                    ),
                    $gen->CustomerRef(
                        $gen->FullName($item->{customer})
                    ),
                    $gen->ItemServiceRef(
                        $gen->FullName($item->{service})
                    ),
                    $gen->Duration($item->{duration}),
                ),
            ),
        ),
    );
    my $xml_req = $gen->xml($xml);
    
    $request_id++;
    
    open (CHANGE, ">change.xml") or die "Can't open change.xml for writing - $!";
        print CHANGE $xml_req;
    close CHANGE;
    my $xml_res= $connection->ProcessRequest($ticket, $xml_req);

}
# Gracefully end everything
$connection->EndSession($ticket);
$connection->CloseConnection;