export/export-2-promon.pl

   1 #!/usr/bin/perl
   2 #
   3 # Script to convert the default export format for APMS into
   4 # something more or less in line with what Jacks Point have
   5 # requested as an import format.
   6 #
   7 use strict;
   8
   9 use Text::CSV_XS;   # libtext-csv-perl in Debian
  10
  11
  12 my @in;
  13
  14 my $csv = Text::CSV_XS->new();
  15
  16 while( <> ) {
  17   $csv->parse($_);
  18   my ( $batchcode,$doc,$transactioncode,$entitytype,$entitycode,$accountcode,$date,$monthcode,$amount,$reference,$description,$consequenceof,$entityname,$orderno,$invoiceref ) = $csv->fields();
  19
  20   next if ( $batchcode == 'BatchCode' );
  21   # next if ( $reference !~ /^VCHR/ );
  22
  23   my $coding = sprintf("%02d.%7.2lf", $entitycode % 100, $accountcode ) if ( $consequenceof == 0 && $entitytype eq "L" );
  24
  25   $in[$doc]{'no'}          = $doc;
  26   if ( defined($coding) && !defined($in[$doc]{'et'}) ) {
  27     $in[$doc]{'et'} = $entitytype;
  28     $in[$doc]{'ec'} = $entitycode;
  29   }
  30   $in[$doc]{'coding'}      = $coding if ( defined($coding) && !defined($in[$doc]{'coding'}) );
  31   $in[$doc]{'date'}        = $date;
  32   $in[$doc]{'period'}      = $monthcode - 546;
  33   $in[$doc]{'reference'}   = $reference;
  34   $in[$doc]{'invoiceref'}  = $invoiceref;
  35   $in[$doc]{'orderno'}     = $orderno;
  36   $in[$doc]{'description'} = $description if ( !defined($in[$doc]{'description'}) );
  37
  38   my $txn = $transactioncode;
  39   $txn = $consequenceof if ( $consequenceof != 0 ) ;
  40   $in[$doc]{'lines'}[$txn]{'accountcode'} = $coding;
  41   $in[$doc]{'lines'}[$txn]{'amount'}      = $amount;
  42   $in[$doc]{'lines'}[$txn]{'description'} = $description;
  43   $in[$doc]{'lines'}[$txn]{'entityname'}  = $entityname;
  44   $in[$doc]{'lines'}[$txn]{'no'}          = $txn;
  45   $in[$doc]{'lines'}[$txn]{'coding'}      = $coding if ( !defined($in[$doc]{'lines'}[$txn]{'coding'}) );
  46
  47   if ( $entitytype eq "C" ) {
  48     $in[$doc]{'otherparty'}  = $entitytype . sprintf('%05d', $entitycode );
  49     $in[$doc]{'entityname'}  = $entityname;
  50   }
  51   elsif ( $entitytype eq "T" ) {
  52     $in[$doc]{'otherparty'}  = $entitytype . sprintf('%05d', $entitycode );
  53     $in[$doc]{'entityname'}  = $entityname;
  54   }
  55
  56   $in[$doc]{'total'} += $amount if ( $amount > 0 && $entitytype eq "L" );
  57
  58   # printf( qq(%d\t%s\t%s\t%s\t%s\t%.2lf\t%d\n), $doc, $in[$doc]{'accountcode'},
  59             # $date, $reference, $entitytype, $amount, $in[$doc]{'period'} );
  60 }
  61
  62 print "CompanyID,InvoiceDate,Period,InvoiceNumber,Description,InvoiceTotal,BudgetCode,Subtotal,ItemNumber,ItemDescription,APMSReference,SupplierReference,OtherPartyName\n";
  63
  64
  65 foreach my $tx ( @in ) {
  66   next if ( $tx->{'no'} == 0 );
  67   $tx->{'otherparty'} = sprintf('%s%05d', $tx->{'et'}, $tx->{'ec'}) if ( !defined($tx->{'otherparty'}) );
  68   my $lno = 1;
  69   my @lines = @{$tx->{'lines'}};
  70   foreach my $vline ( @lines ) {
  71     next if ( $vline->{'amount'} <= 0 );
  72
  73     my $entityname = ( defined($vline->{'entityname'}) ? $vline->{'entityname'} : $tx->{'entityname'});
  74
  75     printf( qq("%s","%s",%d,"%s","%s",%.2lf,"%s",%.2lf,%d,"%s","%s","%s","%s"\n),
  76         $tx->{'otherparty'},
  77         $tx->{'date'},
  78         $tx->{'period'},
  79         $tx->{'orderno'},
  80         $tx->{'description'},
  81         $tx->{'total'},
  82         $tx->{'coding'},
  83         $vline->{'amount'},
  84         $lno++,
  85         $vline->{'description'},
  86         $tx->{'reference'},
  87         $tx->{'invoiceref'},
  88         $entityname
  89       );
  90   }
  91 }