use LWP::Simple;
use HTML::TableExtract;
use DBI;

my $dbh1 = DBI->connect('DBI:ODBC:OptionsDB', 'sa', '') or die "Couldn't connect to database: " . DBI->errstr;
my $sth1 = $dbh1->prepare('INSERT INTO OptionsDB..MyOpt (Security, Volume, Previous, TodayHigh, TodayLow, TodayClose, Change) VALUES (?,?,?,?,?,?,?)') or die "Couldn't prepare statement: " . $dbh1->errstr;

use Data::Dumper;
 
open (CSEFILE, 'SCE.htm');
$/ = undef;
my $response = <CSEFILE>;       
$/ ="\n";
close CSEFILE;

$response =~ s/.*Statistics//s;
$response =~ s/Volume Weighted Average.*//s;
$response =~ s/[\r\n]//gs;
$response =~ s/\t+/ /gs;
$response =~ s/ +/ /gs;

#my $te = HTML::TableExtract->new( headers => ['Time', 'Security', 'Volume', 'Previous', 'High', 'Low', 'Today\'s', 'Change'] );
my $te = HTML::TableExtract->new( headers => ['Security', 'Volume', 'Previous', 'High', 'Low', 'Today\'s', 'Change'] );


#my $content = get("http://www.cse.lk/marketinfo/splsum.jsp");

$te->parse($response);
  foreach $ts ($te->table_states)
    {
      foreach $row ($ts->rows)
       {
       #print Dumper $row;

        #print @{$row},"\n";
        #print $row->[0]."\t".$row->[1]."\t".$row->[2]."\t".$row->[3]."\t".$row->[4]."\t".$row->[5]."\t".$row->[6]."\t".$row->[7]."\n";
        
        $Security = $row->[0];
        $Volume = $row->[1];
        $Previous = $row->[2];
        $TodayHigh = $row->[3];
        $TodayLow = $row->[4];
        $TodayClose = $row->[5];
        $Change = $row->[6];
        
        
        print $row->[0]."\t".$row->[1]."\t".$row->[2]."\t".$row->[3]."\t".$row->[4]."\t".$row->[5]."\t".$row->[6]."\n";
        
        #$sth1->execute($row->[0], $row->[1], $row->[2], $row->[3], $row->[4], $row->[5], $row->[6]) or die "Couldn't execute statement: " . $sth1->errstr;
        $sth1->execute($Security, $Volume, $Previous, $TodayHigh, $TodayLow, $TodayClose, $Change) or die "Couldn't execute statement: " . $sth1->errstr;
       }
}