hledger-of-oxygen-csv.pl

   1 #!/usr/bin/perl
   2 # DESCRIPTION: import from [Oxygène](http://www.memsoft.fr) to [hledger](http://hledger.org)
   3 # AUTHOR: Julien Moutinho <julm+hledger@autogeree.net>
   4 # LICENSE: [GPLv3+](https://www.gnu.org/licenses/gpl-3.0.html)
   5 # NOTE: should be easily hackable to import from other .csv
   6 # USAGE:
   7 #   % hledger-print-csv -f Chart_of_accounts.hledger >Chart_of_accounts.csv
   8 #   % iconv -f latin1 -t utf8 <EXPORT.oxygen.csv |
   9 #     perl hledger-of-oxygen-csv.pl \
  10 #       Chart_of_accounts.csv \
  11 #       >EXPORT.oxygen.hledger
  12 #
  13 # FORMAT of EXPORT.oxygen.csv:
  14 # ----
  15 # NUMJL;LIBJL;DTOPE;NPIEC;NUMCP;LIBCP;CODCP;LIBEC;MTDEB;MTCRE;COTVA;TXTVA
  16 # 60;Achats;01/01/2012;ACH01/76;401REGIEQUART;REGIE DE QUARTIER;REGIE DE QUARTI;LOYER LOCAL DEC. 2011+1T 2012 REGIE QUAR;0,00;1410,91;;0,00
  17 # 60;Achats;01/01/2012;ACH01/76;6132000;LOYER LOCAL 15 rue P. BONNARD;LOYER BONNARD;LOYER LOCAL DEC. 2011+1T 2012 REGIE QUAR;1076,25;0,00;;0,00
  18 # 60;Achats;01/01/2012;ACH01/76;6165000;Responsabilité civile;;ASSURANCE LOCAL 2012 VIA REGIEQUARTIER;86,47;0,00;;0,00
  19 # 60;Achats;01/01/2012;ACH01/76;6140000;Charges locatives et de copropriété;;CHARGES LOCAL 1T 2012;248,19;0,00;;0,00
  20 # ; ... And so on.. and so forth..
  21 # ----
  22 #
  23 # FORMAT of Chart_of_accounts.hledger:
  24 #
  25 # Pattern:
  26 # ----
  27 # 01/01
  28 #   0.ZZZ:1.YYY:2.XXX        0; 012.  Description
  29 #   0.ZZZ:1.YYY:3.WWW        0; 013.  Description
  30 #   0.ZZZ:1.YYY:3.WWW.4.VVV  0; 0134. Description
  31 # ; ... And so on.. and so forth..
  32 # ----
  33 #
  34 # For exemple:
  35 # ----
  36 # 01/01 Plan comptable des associations
  37 #       1.Capital                                                0 ; 1. COMPTES DE CCOAITAUX
  38 #       1.Capital:0.Fonds                                        0 ; 10. Fonds associatifs et reserves
  39 #       2.Immobilisation                                         0 ; 2. COMPTES D'IMMOBILISATIONS
  40 #       2.Immobilisation:1.Corporelle                            0 ; 21. Immobilisations corporelles
  41 #       4.Tiers                                                  0 ; 4. COMPTES TIERS
  42 #       4.Tiers:0.Fournisseur                                    0 ; 40. Fournisseurs et comptes rattachés
  43 #       5.Finance                                                0 ; 5. COMPTES FINANCIERS
  44 #       5.Finance:1.Etablissement                                0 ; 51. Banques, établissements financiers et assimilés
  45 #       5.Finance:1.Etablissement:1.Valeur                       0 ; 511. Valeurs à l’encaissement
  46 #       5.Finance:1.Etablissement:1.Valeur:2.Chèque_à_encaisser  0 ; 5112. Chèques à encaisser
  47 #       5.Finance:1.Etablissement:2.Banque:001.Courant           0 ; 512001. Crédit Coopératif - Compte courant
  48 #       5.Finance:1.Etablissement:2.Banque:002.Livret            0 ; 512002. Crédit coopératif - Livret
  49 #       6.Charge                                                 0 ; 6. COMPTES D'ACHATS
  50 #       6.Charge:1.Service                                       0 ; 61. Services extérieurs
  51 #       6.Charge:2.Autre_service                                 0 ; 62. Autres services extérieurs
  52 #       6.Charge:3.Impôt                                         0 ; 63. Impôts, taxes et versements assimilés
  53 #       6.Charge:4.Personnel                                     0 ; 64. Charges de personnel
  54 #       6.Charge:5.Gestion                                       0 ; 65. Autres charges de gestion courantes
  55 #       6.Charge:8.Dotation                                      0 ; 68. Dotations aux amortissements, dépréciations, provisions et engagements
  56 #       7.Produit                                                0 ; 7. COMPTES DE PRODUITS
  57 #       7.Produit:0.Vente                                        0 ; 70. ventes de produits finis, prestations de services, marchandises
  58 # ; ... And so on.. and so forth..
  59 # ----
  60
  61 our $VERSION = '2014.07.22';
  62 use strict;
  63 use warnings FATAL => qw(all);
  64 use utf8;
  65 use open qw/:std :utf8/;
  66 require Data::Dumper;
  67 require Encode;
  68 require IO::Wrap;
  69 require Text::CSV;
  70 #require Text::CSV::Encoded;
  71 require Text::Trim;
  72
  73 sub parse_date (@) {
  74         ($_) = @_;
  75         my ($jj,$mm,undef,$yy) = ($_ =~ m{^\s*([0-3]?[0-9])\s*/\s*([0-1]?[0-9])\s*/\s*(20)?([0-9][0-9])\s*$});
  76         return "20$yy/$mm/$jj";
  77  }
  78 sub parse_code (@) {
  79         ($_) = @_;
  80         my ($code) = ($_ =~ m{^([0-9]*?)0*$});
  81         return (defined $code ? $code : $_);
  82  }
  83 sub parse_journal (@) {
  84         ($_) = @_;
  85         $_ = Text::Trim::trim($_);
  86         s/\s/_/g;
  87         return $_;
  88  }
  89
  90 sub parse_csv_line (@) {
  91         my ($nth, $h, $c) = @_;
  92         #print STDERR ("parse_csv_line: csv_line($nth)=".Data::Dumper::Dumper($c));
  93         my $date = parse_date($c->{date});
  94         $h->{$date} = {}
  95                 unless defined $h->{$date};
  96         my $t;
  97         if (exists $h->{$date}->{$c->{transaction}}) {
  98                 $t = $h->{$date}->{$c->{transaction}};
  99          }
 100         else {
 101                 $t =
 102                  { journal      => parse_journal($c->{journal})
 103                  , journal_code => $c->{journal_code}
 104                  , postings     => []
 105                  };
 106                 $h->{$date}->{$c->{transaction}} = $t;
 107          }
 108
 109         my $amount;
 110         if (defined $c->{debit} and $c->{debit} eq '0,00') {
 111                 $amount = "-$c->{credit}";
 112          }
 113         elsif (defined $c->{credit} and $c->{credit} eq '0,00') {
 114                 $amount = "$c->{debit}";
 115          }
 116         else { die "ERROR: wrong credit/debit: CSV#$nth: ".Data::Dumper::Dumper($c); }
 117
 118         push $t->{postings},
 119          { account => parse_code($c->{account})
 120          , amount  => $amount
 121          , comment => $c->{account_name}
 122          , csv_nth => $nth+2
 123          }
 124  }
 125 sub print_hledger (@) {
 126         my ($h, $ap) = @_;
 127         foreach my $date (sort {$a cmp $b} (keys %$h)) {
 128                 my $transactions = $h->{$date};
 129                 while (my ($transaction, $t) = each %$transactions) {
 130                         print STDOUT "$date $transaction ; Journal:$t->{journal}\n";
 131                         my $wmax = 0;
 132                         foreach my $a (@{$t->{postings}}) {
 133                                 if (not defined $a->{account}) {
 134                                         print STDERR Data::Dumper::Dumper($t);
 135                                         die "ERROR: wrong account in t=$transaction";
 136                                  }
 137                                 if (defined $ap->{$a->{account}}) {
 138                                         $a->{account} = $ap->{$a->{account}}->{account}
 139                                  }
 140                                 my $w = 0 + length ($a->{account});
 141                                 $wmax = $w
 142                                         if $wmax < $w;
 143                          }
 144                         my $p = $t->{postings};
 145                         foreach my $a (sort {$a->{account} cmp $b->{account}} @$p) {
 146                                 print STDOUT "\t$a->{account}  $a->{amount} ; $a->{comment}  CSV#.$a->{csv_nth}\n";
 147                          }
 148                  }
 149          }
 150  }
 151 sub parse_chart_of_accounts (@) {
 152         my ($coa_file) = @_;
 153         my %ap = ();
 154         my $csv = Text::CSV->new
 155          ({binary => 1
 156          , eol => $/
 157          , sep_char => ','
 158          });
 159         print STDERR ("Chart_of_accounts: ", $coa_file, "\n");
 160         open (my $COA, "<:encoding(utf8)", $coa_file)
 161                 or die "ERROR: opening accounting plan given as first argument";
 162         #my $coa_in = IO::Wrap::wraphandle($COA);
 163         my $coa_head = $csv->getline($COA);
 164         print STDERR ("coa_head: ", join("|", @$coa_head), "\n");
 165         $csv->column_names(@$coa_head);
 166         my $nth = 1;
 167         while (my $csv_line = $csv->getline_hr($COA)) {
 168                 $nth++;
 169                 my $post_cmt = $csv_line->{'posting-comment'};
 170                 die "ERROR: no posting-comment COA#$nth: ".Data::Dumper::Dumper($csv_line)
 171                         if not defined $post_cmt;
 172                 my ($code, $description) = ($post_cmt =~ m{^([0-9]+)\.\s*(.*)$});
 173                 die "ERROR: cannot extract code in accounting plan: posting-comment COA#$nth: $csv_line->{'posting-comment'}"
 174                         if not defined $code;
 175                 $ap{$code} =
 176                  { account => $csv_line->{account}
 177                  , description => $description
 178                  };
 179          }
 180         print STDERR "Chart_of_accounts: ".Data::Dumper::Dumper(\%ap);
 181         return \%ap;
 182  }
 183
 184 sub main () {
 185         my $ap = parse_chart_of_accounts($ARGV[0]);
 186         my $csv = Text::CSV->new
 187          ({binary => 1
 188          , eol => $/
 189          , sep_char => ';'
 190          });
 191         my $in = IO::Wrap::wraphandle(\*STDIN);
 192         binmode STDOUT, ':utf8';
 193         my $csv_head = $csv->getline($in);
 194         #print STDERR ("head: ", join("|", @$csv_head), "\n");
 195         #$csv->column_names(@$csv_head);
 196         $csv->column_names (qw (
 197                 journal_code
 198                 journal
 199                 date
 200                 transaction
 201                 account
 202                 account_name
 203                 account_code
 204                 description
 205                 debit
 206                 credit
 207                 COTVA
 208                 TXTVA
 209          ));
 210         my $hledger_data = {};
 211
 212         my $members = {};
 213         my $nth = 0;
 214         while (my $csv_line = $csv->getline_hr($in)) {
 215                 #print STDERR ("csv_line: ", join("|", @$csv_line), "\n");
 216                 parse_csv_line(2 + $nth++, $hledger_data, $csv_line);
 217          }
 218         #print STDERR "hledger_data=".Data::Dumper::Dumper($hledger_data);
 219         print_hledger($hledger_data, $ap);
 220         #my $out = IO::Wrap::wraphandle(\*STDERR);
 221  }
 222
 223 main;