#!/usr/bin/perl -w
# by Deniss ( deniss at fackoff.com )

use lib '/usr/lib/perl5/i386-linux';
use Paradox;
# can be found at http://els.donpac.ru/~cae/prog/paradox.shtml
#
sub extract_memo {
        my ($dummy) = @_ ;
        my (%memo) ;
        my ($start);
        my ($got);
        my ($dstring);
        my ($dlength);
        my ($bl_leng);
        @memo_data = split (':',$dummy);
        open (MEMO_FILE,"/usr2/jd/DOKUMENT.MB") || die "Cant open file";
        binmode (MEMO_FILE);
        seek (MEMO_FILE,$memo_data[0],0) || die "Cant seek to $memo_data[0]" ;
        read (MEMO_FILE,$got,1);
        if (($memo_data[2]==0xFF) || (unpack ("C",$got)==0x02)) {
                seek (MEMO_FILE,$memo_data[0]+1,0) || die "Cant seek to ($memo_data[0]+1)" ;
                read (MEMO_FILE,$got,6);
                ($bl_leng,$dlength) = unpack ("S L",$got);
                if (($bl_leng*0x1000)>=$dlength) {
                        seek (MEMO_FILE,$memo_data[0]+9,0) || die "Cant seek to ($memo_data[0]+9)" ;
                        read (MEMO_FILE,$dstring,$dlength);
                } else {$dstring="UNKNOWN";};
        } elsif (unpack ("C",$got)==0x03) {
                $memo{'hand_offset'}=($memo_data[0]+12+($memo_data[2]*5));
                seek (MEMO_FILE,$memo{'hand_offset'},0) || die "Cant seek to $memo{hand_offset}" ;
                read (MEMO_FILE,$got,5);
                ($memo{'offset'},$memo{'length_div_16'},$memo{'mod_num'},$memo{'length_mod_16'})= unpack ("C C S C",$got);
                $start = $memo{'offset'} *16 + $memo_data[0];
                $dlength = ($memo{'length_div_16'}-1) * 16 + $memo{'length_mod_16'};
                if ($dlength<0) {$dlength=$memo_data[1]};
                seek (MEMO_FILE,$start,0) || die "Cant seek to $start" ;
                read (MEMO_FILE,$dstring,$dlength);
        } else { $dstring="UNKNOWN";};
        close (MEMO_FILE);
#       $dstring=~ s/"([^"]*)"/''$1''/g;
#       $dstring=~ s/"/&&quot;/g;
        $dstring=~ s/'/&#039;/g;
        chomp($dstring);
        return $dstring;
}
sub extract_memo_poz {
        my ($dummy) = @_ ; my (%memo) ;
        @memo_data = split (':',$dummy);
        return $memo_data[0].":".$memo_data[1].":".$memo_data[2];
}

$d = new Paradox "/usr2/jd/DOKUMENT.DB" ;
$ser=0;
while (@record_data = $d->fetch ()) {
  if ( &extract_memo_poz($record_data[25]) ne "0:0:0") {
        print "INSERT INTO jd VALUES ('$ser','";        # serial
        print $record_data[2],"','";                    # IZD_STRUKTK
        print $record_data[3],"','";                    # DOK_VEIDAK
        print $record_data[7],"','";                    # STATUSAK
        print $record_data[9],"','";                    # NUMURS
        print $record_data[8],"','";                    # PIEN_DAT
        print &extract_memo($record_data[11]),"',";     # NOSAUKUMS
        if ($record_data[12] ne "") {                   # SP_ST_DAT
                print "'",$record_data[12],"',";
        } else {
                print "NULL,";
        };
        if ($record_data[14] ne "") {                   # PUBL_DAT1
                print "'",$record_data[14],"',";
        } else {
                print "NULL,";
        };
        if ($record_data[21] ne "") {                   # ATC_DAT
                print "'",$record_data[21],"',"; 
        } else {
                print "NULL,";
        }; 
        print "'".&extract_memo($record_data[25]),"');\n";

        ++$ser;
  };
}