X-Git-Url: https://git.cyclocoop.org/?p=tool%2Fhledger.git;a=blobdiff_plain;f=script%2Fhledger-of-oxygen-csv.pl;fp=script%2Fhledger-of-oxygen-csv.pl;h=194a6128280edc6e58eb965b7c038abf12aac368;hp=0000000000000000000000000000000000000000;hb=707b0a621cd3c97d5d7e22e855272b3f865a2d56;hpb=30e9b83c521f5149836e1ef0b544676370721efe diff --git a/script/hledger-of-oxygen-csv.pl b/script/hledger-of-oxygen-csv.pl new file mode 100755 index 0000000..194a612 --- /dev/null +++ b/script/hledger-of-oxygen-csv.pl @@ -0,0 +1,223 @@ +#!/usr/bin/perl +# DESCRIPTION: import from [Oxygène](http://www.memsoft.fr) to [hledger](http://hledger.org) +# AUTHOR: Julien Moutinho +# LICENSE: [GPLv3+](https://www.gnu.org/licenses/gpl-3.0.html) +# NOTE: should be easily hackable to import from other .csv +# USAGE: +# % hledger-print-csv -f Chart_of_accounts.hledger >Chart_of_accounts.csv +# % iconv -f latin1 -t utf8 EXPORT.oxygen.hledger +# +# FORMAT of EXPORT.oxygen.csv: +# ---- +# NUMJL;LIBJL;DTOPE;NPIEC;NUMCP;LIBCP;CODCP;LIBEC;MTDEB;MTCRE;COTVA;TXTVA +# 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 +# 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 +# 60;Achats;01/01/2012;ACH01/76;6165000;Responsabilité civile;;ASSURANCE LOCAL 2012 VIA REGIEQUARTIER;86,47;0,00;;0,00 +# 60;Achats;01/01/2012;ACH01/76;6140000;Charges locatives et de copropriété;;CHARGES LOCAL 1T 2012;248,19;0,00;;0,00 +# ; ... And so on.. and so forth.. +# ---- +# +# FORMAT of Chart_of_accounts.hledger: +# +# Pattern: +# ---- +# 01/01 +# 0.ZZZ:1.YYY:2.XXX 0; 012. Description +# 0.ZZZ:1.YYY:3.WWW 0; 013. Description +# 0.ZZZ:1.YYY:3.WWW.4.VVV 0; 0134. Description +# ; ... And so on.. and so forth.. +# ---- +# +# For exemple: +# ---- +# 01/01 Plan comptable des associations +# 1.Capital 0 ; 1. COMPTES DE CCOAITAUX +# 1.Capital:0.Fonds 0 ; 10. Fonds associatifs et reserves +# 2.Immobilisation 0 ; 2. COMPTES D'IMMOBILISATIONS +# 2.Immobilisation:1.Corporelle 0 ; 21. Immobilisations corporelles +# 4.Tiers 0 ; 4. COMPTES TIERS +# 4.Tiers:0.Fournisseur 0 ; 40. Fournisseurs et comptes rattachés +# 5.Finance 0 ; 5. COMPTES FINANCIERS +# 5.Finance:1.Etablissement 0 ; 51. Banques, établissements financiers et assimilés +# 5.Finance:1.Etablissement:1.Valeur 0 ; 511. Valeurs à l’encaissement +# 5.Finance:1.Etablissement:1.Valeur:2.Chèque_à_encaisser 0 ; 5112. Chèques à encaisser +# 5.Finance:1.Etablissement:2.Banque:001.Courant 0 ; 512001. Crédit Coopératif - Compte courant +# 5.Finance:1.Etablissement:2.Banque:002.Livret 0 ; 512002. Crédit coopératif - Livret +# 6.Charge 0 ; 6. COMPTES D'ACHATS +# 6.Charge:1.Service 0 ; 61. Services extérieurs +# 6.Charge:2.Autre_service 0 ; 62. Autres services extérieurs +# 6.Charge:3.Impôt 0 ; 63. Impôts, taxes et versements assimilés +# 6.Charge:4.Personnel 0 ; 64. Charges de personnel +# 6.Charge:5.Gestion 0 ; 65. Autres charges de gestion courantes +# 6.Charge:8.Dotation 0 ; 68. Dotations aux amortissements, dépréciations, provisions et engagements +# 7.Produit 0 ; 7. COMPTES DE PRODUITS +# 7.Produit:0.Vente 0 ; 70. ventes de produits finis, prestations de services, marchandises +# ; ... And so on.. and so forth.. +# ---- + +our $VERSION = '2014.07.22'; +use strict; +use warnings FATAL => qw(all); +use utf8; +use open qw/:std :utf8/; +require Data::Dumper; +require Encode; +require IO::Wrap; +require Text::CSV; +#require Text::CSV::Encoded; +require Text::Trim; + +sub parse_date (@) { + ($_) = @_; + 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*$}); + return "20$yy/$mm/$jj"; + } +sub parse_code (@) { + ($_) = @_; + my ($code) = ($_ =~ m{^([0-9]*?)0*$}); + return (defined $code ? $code : $_); + } +sub parse_journal (@) { + ($_) = @_; + $_ = Text::Trim::trim($_); + s/\s/_/g; + return $_; + } + +sub parse_csv_line (@) { + my ($nth, $h, $c) = @_; + #print STDERR ("parse_csv_line: csv_line($nth)=".Data::Dumper::Dumper($c)); + my $date = parse_date($c->{date}); + $h->{$date} = {} + unless defined $h->{$date}; + my $t; + if (exists $h->{$date}->{$c->{transaction}}) { + $t = $h->{$date}->{$c->{transaction}}; + } + else { + $t = + { journal => parse_journal($c->{journal}) + , journal_code => $c->{journal_code} + , postings => [] + }; + $h->{$date}->{$c->{transaction}} = $t; + } + + my $amount; + if (defined $c->{debit} and $c->{debit} eq '0,00') { + $amount = "-$c->{credit}"; + } + elsif (defined $c->{credit} and $c->{credit} eq '0,00') { + $amount = "$c->{debit}"; + } + else { die "ERROR: wrong credit/debit: CSV#$nth: ".Data::Dumper::Dumper($c); } + + push $t->{postings}, + { account => parse_code($c->{account}) + , amount => $amount + , comment => $c->{account_name} + , csv_nth => $nth+2 + } + } +sub print_hledger (@) { + my ($h, $ap) = @_; + foreach my $date (sort {$a cmp $b} (keys %$h)) { + my $transactions = $h->{$date}; + while (my ($transaction, $t) = each %$transactions) { + print STDOUT "$date $transaction ; Journal:$t->{journal}\n"; + my $wmax = 0; + foreach my $a (@{$t->{postings}}) { + if (not defined $a->{account}) { + print STDERR Data::Dumper::Dumper($t); + die "ERROR: wrong account in t=$transaction"; + } + if (defined $ap->{$a->{account}}) { + $a->{account} = $ap->{$a->{account}}->{account} + } + my $w = 0 + length ($a->{account}); + $wmax = $w + if $wmax < $w; + } + my $p = $t->{postings}; + foreach my $a (sort {$a->{account} cmp $b->{account}} @$p) { + print STDOUT "\t$a->{account} $a->{amount} ; $a->{comment} CSV#.$a->{csv_nth}\n"; + } + } + } + } +sub parse_chart_of_accounts (@) { + my ($coa_file) = @_; + my %ap = (); + my $csv = Text::CSV->new + ({binary => 1 + , eol => $/ + , sep_char => ',' + }); + print STDERR ("Chart_of_accounts: ", $coa_file, "\n"); + open (my $COA, "<:encoding(utf8)", $coa_file) + or die "ERROR: opening accounting plan given as first argument"; + #my $coa_in = IO::Wrap::wraphandle($COA); + my $coa_head = $csv->getline($COA); + print STDERR ("coa_head: ", join("|", @$coa_head), "\n"); + $csv->column_names(@$coa_head); + my $nth = 1; + while (my $csv_line = $csv->getline_hr($COA)) { + $nth++; + my $post_cmt = $csv_line->{'posting-comment'}; + die "ERROR: no posting-comment COA#$nth: ".Data::Dumper::Dumper($csv_line) + if not defined $post_cmt; + my ($code, $description) = ($post_cmt =~ m{^([0-9]+)\.\s*(.*)$}); + die "ERROR: cannot extract code in accounting plan: posting-comment COA#$nth: $csv_line->{'posting-comment'}" + if not defined $code; + $ap{$code} = + { account => $csv_line->{account} + , description => $description + }; + } + print STDERR "Chart_of_accounts: ".Data::Dumper::Dumper(\%ap); + return \%ap; + } + +sub main () { + my $ap = parse_chart_of_accounts($ARGV[0]); + my $csv = Text::CSV->new + ({binary => 1 + , eol => $/ + , sep_char => ';' + }); + my $in = IO::Wrap::wraphandle(\*STDIN); + binmode STDOUT, ':utf8'; + my $csv_head = $csv->getline($in); + #print STDERR ("head: ", join("|", @$csv_head), "\n"); + #$csv->column_names(@$csv_head); + $csv->column_names (qw ( + journal_code + journal + date + transaction + account + account_name + account_code + description + debit + credit + COTVA + TXTVA + )); + my $hledger_data = {}; + + my $members = {}; + my $nth = 0; + while (my $csv_line = $csv->getline_hr($in)) { + #print STDERR ("csv_line: ", join("|", @$csv_line), "\n"); + parse_csv_line(2 + $nth++, $hledger_data, $csv_line); + } + #print STDERR "hledger_data=".Data::Dumper::Dumper($hledger_data); + print_hledger($hledger_data, $ap); + #my $out = IO::Wrap::wraphandle(\*STDERR); + } + +main;