use dbconnection;
use strict;

open ERRFILE, ">>c:\\dupeerr.txt";
print @ARGV[0],"\n";
print @ARGV[1],"\n";
print @ARGV[2],"\n";
my @tsid;
my $dupecount;
my @temparray = ();

my $connection = new Dbconnection(@ARGV[0], @ARGV[1], @ARGV[2]);

my $sth  = $connection->connection->prepare("select travelersystemid,
remarktypecd, min(remarkseqnbr),remarktext from remarks group by
travelersystemid, remarktypecd, remarktext having count(*) > 1 order by
travelersystemid, remarktypecd, remarktext")or die print ERRFILE $!;
my $rc = $sth->execute or die print ERRFILE $!;

while ( my $row = $sth->fetchrow_arrayref){

push @temparray, @{$row};
push @tsid, @{$row}[0];
}
$dupecount = scalar @tsid;
print "Found $dupecount sets of duplicate records.\n";
print ERRFILE "Found $dupecount sets of duplicate records.\n";
for ( my $i=0; $i < scalar @temparray; $i += 4 ) {
    $sth = $connection->connection->prepare('delete from remarks where
    travelersystemid = CONVERT(int,?) and remarktypecd = ?
    and remarktext = ? and remarkseqnbr > CONVERT(int,?)') or die print ERRFILE $!;
    $sth->bind_param(1, @temparray[$i]);
    $sth->bind_param(2, @temparray[$i+1]);
    $sth->bind_param(3, @temparray[$i+3]);
    $sth->bind_param(4, @temparray[$i+2]);
    my $rc = $sth->execute or die print ERRFILE $!;

}
foreach my $item(@tsid){
    my $type = undef;                                       #reset variable
    my $seqcount = 1;                                       #reset variable
    @temparray = ();                                        #reset array
    $sth = $connection->connection->prepare("select
    travelersystemid, remarktypecd, remarkseqnbr, remarktext from remarks
    where travelersystemid = CONVERT(int, ?) order by remarktypecd,
    remarkseqnbr") or die print ERRFILE $!;                 #get query
    $sth->bind_param(1, $item);                       #bind placeholder
    my $rc = $sth->execute or die print ERRFILE $!;         #execute query
    while ( my $row = $sth->fetchrow_arrayref){

    push @temparray, @{$row};
    }
    $type = @temparray[1];
    my $i;
    for ( $i = 0; $i < scalar @temparray; $i += 4 ) {
        if ( $type ne @temparray[$i+1] ) {
            $seqcount = 1;
            $type = @temparray[$i+1]
        }
        if ( $seqcount != @temparray[$i+2] ) {
                $sth = $connection->connection->prepare(" update remarks
                set remarkseqnbr = CONVERT(int,?) where travelersystemid =
                CONVERT(int, ?) and remarktypecd = ? and remarktext = ?
                and remarkseqnbr = CONVERT(int, ?)") or die print ERRFILE $!;
                $sth->bind_param(1, $seqcount);
                $sth->bind_param(2, @temparray[$i]);
                $sth->bind_param(3, @temparray[$i+1]);
                $sth->bind_param(4, @temparray[$i+3]);
                $sth->bind_param(5, @temparray[$i+2]);
                my $rc = $sth->execute or die print ERRFILE $!;
        }
                $seqcount++;

    }
}
$connection->logout;

close ERRFILE;