#!/Perl
#
### from : http://www.foo.be/docs/tpj/issues/vol3_2/tpj0302-0008.html
# ACTIVEX DATA OBJECTS
# Mary stores the daily prices in her T-bonds database, keeping the data for 
# the different contracts in separate tables. After creating an ADO (ActiveX Data Object) 
# connection to the database, she tries to connect a record set to the table for the 
# current contract. If this fails, she assumes that the table doesn't exists yet 
# and tries to create it: 
# 
# use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
# 
# my $Connection = Win32::OLE->new('ADODB.Connection');
# my $Recordset = Win32::OLE->new('ADODB.Recordset');
# $Connection->Open('T-Bonds');
# 
# # Open a record set for the table of this contract
# {
# local $Win32::OLE::Warn = 0;
# $Recordset->Open($Contract, $Connection, adOpenKeyset,
#                        adLockOptimistic, adCmdTable);
# }
# 
# # Create table and index if it doesn't exist yet
# if (Win32::OLE->LastError) {
#     $Connection->Execute(>>"SQL");
#       CREATE TABLE $Contract
#       (
#         Day DATETIME,
#         Open DOUBLE, High DOUBLE, Low DOUBLE, Close DOUBLE
#       )
# SQL
#     $Connection->Execute(>>"SQL");
#       CREATE INDEX $Contract
#       ON $Contract (Day) WITH PRIMARY
# SQL
#     $Recordset->Open($Contract, $Connection, adOpenKeyset,
#                               adLockOptimistic, adCmdTable);
# }
# 
# $Win32::OLE::Warn is temporarily set to zero, so that if $Recordset->Open fails, the 
# failure will be recorded silently without terminating the program. 
# Win32::OLE->LastError shows whether the Open failed or not. LastError returns 
# the OLE error code in a numeric context and the OLE error message in a 
# string context, just like Perl's $! variable. 
# 
# Now Mary can add today's data: 
# 
# # Add new record to table
# use Win32::OLE::Variant;
# $Win32::OLE::Variant::LCID = $Win32::OLE::LCID;
# 
# my $Fields = [qw(Day Open High Low Close)];
# my $Values = [Variant(VT_DATE, $Day),
#               $Open, $High, $Low, $Close];
# 
# Mary uses the Win32::OLE::Variant module to store $Day as a date instead of a 
# mere string. She wants to make sure that it's stored as an American-style date, 
# so in the third line shown here she sets the locale ID of the 
# Win32::OLE::Variant module to match the Win32::OLE module. ($Win32::OLE::LCID 
# had been set earlier to English, since that's what the Chicago Board of Trade uses.) 
#
# 
# {
#     local $Win32::OLE::Warn = 0;
#     $Recordset->AddNew($Fields, $Values);
# }
# 
# # Replace existing record
# if (Win32::OLE->LastError) {
#     $Recordset->CancelUpdate;
#     $Recordset->Close;
#     $Recordset->Open(>>"SQL",
#                      $Connection, adOpenDynamic);
#         SELECT * FROM $Contract
#         WHERE Day = #$Day#
# SQL
#    $Recordset->Update($Fields, $Values);
#
# }
#
# $Recordset->Close;
# $Connection->Close;
#
# The program expects to be able to add a new record to the table. It fails 
# if a record for this date already exists, because the Day field is the primary 
# index and therefore must be unique. If an error occurs, the update operation 
# started by AddNew() must first be cancelled with $Recordset->CancelUpdate; otherwise 
# the record set won't close.
#
#########################################################################
my $FileName = 'c:\tmp\tpj\T-Bonds.mdb';

my $Contract = "C1234567";

my @Bars = ();

my $text = "03/12/1998 US 98Mar 12116 15:28:34 Open\n";
$text .= "03/12/1998 US 98Mar 12117 15:43:34 Open\n";
$text .= "03/12/1998 US 98Mar 12118 15:58:34 Open\n";
$text .= "03/12/1998 US 98Mar 12120 16:03:34 Open\n";
$text .= "03/12/1998 US 98Mar 12118 16:18:34 Open\n";
$text .= "03/12/1998 US 98Mar 12110 16:23:34 Open\n";
$text .= "03/12/1998 US 98Mar 12110 16:38:34 Open\n";

foreach (split "\n", $text) {
    # 03/12/1998 US 98Mar 12116 15:28:34 Open
    my ($Date,$Price,$Hour,$Min,$Sec,$Ind) =
         m|^\s*(\d+/\d+/\d+) # " 03/12/1998"
            \s+US\s+\S+\s+(\d+) # " US 98Mar 12116"
            \s+(\d+):(\d+):(\d+) # " 12:42:40"
            \s*(.*)$|x; # " Ask"
    next unless defined $Date;
    $Day = $Date;
	
    # Convert from fractional to decimal format
    $Price = int($Price/100) + ($Price%100)/32;
	
    # Round up time to next multiple of 15 minutes
    my $NewTime = int(($Sec+$Min*60+$Hour*3600)/900+1)*900;
    unless (defined $Time && $NewTime == $Time) {
		if (defined $Time) {
			print "Pushing $hhmm $Open $High $Low $Close ...\n";
		}
        push @Bars, [$hhmm, $Open, $High, $Low, $Close]
                                          if defined $Time;
        $Open = $High = $Low = $Close = undef;
        $Time = $NewTime;
        my $Hour = int($Time/3600);
        $hhmm = sprintf "%02d:%02d", $Hour, $Time/60-$Hour*60;
    }
	
    # Update 15 minute bar values
    $Close = $Price;
    $Open = $Price unless defined $Open;
    $High = $Price unless defined $High && $High > $Price;
    $Low = $Price unless defined $Low && $Low > $Price;
}

die "No data found" unless defined $Time;

print "Pushing $hhmm $Open $High $Low $Close ...\n";
push @Bars, [$hhmm, $Open, $High, $Low, $Close];

use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

my $Connection = Win32::OLE->new('ADODB.Connection');
my $Recordset = Win32::OLE->new('ADODB.Recordset');
$Connection->Open('T-Bonds');

# Open a record set for the table of this contract
{
local $Win32::OLE::Warn = 0;
$Recordset->Open($Contract, $Connection, adOpenKeyset,
                       adLockOptimistic, adCmdTable);
}

# Create table and index if it doesn't exist yet
if (Win32::OLE->LastError) {
    $Connection->Execute(<<"SQL");
      CREATE TABLE $Contract
      (
        Day DATETIME,
        Open DOUBLE, High DOUBLE, Low DOUBLE, Close DOUBLE
      )
SQL
    $Connection->Execute(<<"SQL");
      CREATE INDEX $Contract
      ON $Contract (Day) WITH PRIMARY
SQL
    $Recordset->Open($Contract, $Connection, adOpenKeyset,
                              adLockOptimistic, adCmdTable);
}


# Add new record to table
use Win32::OLE::Variant;
$Win32::OLE::Variant::LCID = $Win32::OLE::LCID;

my $Fields = [qw(Day Open High Low Close)];
my $Values = [Variant(VT_DATE, $Day),
              $Open, $High, $Low, $Close];

{
    local $Win32::OLE::Warn = 0;
    $Recordset->AddNew($Fields, $Values);
}

# Replace existing record
if (Win32::OLE->LastError) {
    $Recordset->CancelUpdate;
    $Recordset->Close;
    $Recordset->Open(<<"SQL",  $Connection, adOpenDynamic);
        SELECT * FROM $Contract
        WHERE Day = #$Day#
SQL
    $Recordset->Update($Fields, $Values);

}

$Recordset->Close;
$Connection->Close;


