dolibarr  7.0.0-beta
accountancyexport.class.php
Go to the documentation of this file.
1 <?php
2 /*
3  * Copyright (C) 2007-2012 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2014 Juanjo Menent <jmenent@2byte.es>
5  * Copyright (C) 2015 Florian Henry <florian.henry@open-concept.pro>
6  * Copyright (C) 2015 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
7  * Copyright (C) 2016 Pierre-Henry Favre <phf@atm-consulting.fr>
8  * Copyright (C) 2016-2017 Alexandre Spangaro <aspangaro@zendsi.com>
9  * Copyright (C) 2013-2017 Olivier Geffroy <jeff@jeffinfo.com>
10  * Copyright (C) 2017 Elarifr. Ari Elbaz <github@accedinfo.com>
11  * Copyright (C) 2017 Frédéric France <frederic.france@netlogic.fr>
12 
13  *
14  * This program is free software; you can redistribute it and/or modify
15  * it under the terms of the GNU General Public License as published by
16  * the Free Software Foundation; either version 3 of the License, or
17  * (at your option) any later version.
18  *
19  * This program is distributed in the hope that it will be useful,
20  * but WITHOUT ANY WARRANTY; without even the implied warranty of
21  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22  * GNU General Public License for more details.
23  *
24  * You should have received a copy of the GNU General Public License
25  * along with this program. If not, see <http://www.gnu.org/licenses/>.
26  */
27 
40 require_once DOL_DOCUMENT_ROOT . '/core/lib/functions.lib.php';
41 
43 {
47  public static $EXPORT_TYPE_NORMAL = 1; // Classic CSV
48  public static $EXPORT_TYPE_CEGID = 2;
49  public static $EXPORT_TYPE_COALA = 3;
50  public static $EXPORT_TYPE_BOB50 = 4;
51  public static $EXPORT_TYPE_CIEL = 5;
52  public static $EXPORT_TYPE_QUADRATUS = 6;
53  public static $EXPORT_TYPE_EBP = 7;
54  public static $EXPORT_TYPE_COGILOG = 8;
55  public static $EXPORT_TYPE_AGIRIS = 9;
56  public static $EXPORT_TYPE_CONFIGURABLE = 10;
57 
62  public $errors = array ();
63 
68  public $separator = '';
69 
74  public $end_line = '';
75 
81  public function __construct(DoliDB &$db) {
82  global $conf;
83 
84  $this->db = &$db;
85  $this->separator = $conf->global->ACCOUNTING_EXPORT_SEPARATORCSV;
86  $this->end_line = empty($conf->global->ACCOUNTING_EXPORT_ENDLINE)?"\n":($conf->global->ACCOUNTING_EXPORT_ENDLINE==1?"\n":"\r\n");
87  }
88 
94  public static function getType() {
95  global $langs;
96 
97  return array (
98  self::$EXPORT_TYPE_NORMAL => $langs->trans('Modelcsv_normal'),
99  self::$EXPORT_TYPE_CEGID => $langs->trans('Modelcsv_CEGID'),
100  self::$EXPORT_TYPE_COALA => $langs->trans('Modelcsv_COALA'),
101  self::$EXPORT_TYPE_BOB50 => $langs->trans('Modelcsv_bob50'),
102  self::$EXPORT_TYPE_CIEL => $langs->trans('Modelcsv_ciel'),
103  self::$EXPORT_TYPE_QUADRATUS => $langs->trans('Modelcsv_quadratus'),
104  self::$EXPORT_TYPE_EBP => $langs->trans('Modelcsv_ebp'),
105  self::$EXPORT_TYPE_COGILOG => $langs->trans('Modelcsv_cogilog'),
106  self::$EXPORT_TYPE_AGIRIS => $langs->trans('Modelcsv_agiris'),
107  self::$EXPORT_TYPE_CONFIGURABLE => $langs->trans('Modelcsv_configurable'),
108  );
109  }
110 
116  public static function getTypeConfig() {
117  global $conf, $langs;
118 
119  return array (
120  'param' => array(
121  self::$EXPORT_TYPE_NORMAL => array(
122  'label' => $langs->trans('Modelcsv_normal'),
123  'ACCOUNTING_EXPORT_FORMAT' => empty($conf->global->ACCOUNTING_EXPORT_FORMAT)?'txt':$conf->global->ACCOUNTING_EXPORT_FORMAT,
124  'ACCOUNTING_EXPORT_SEPARATORCSV' => empty($conf->global->ACCOUNTING_EXPORT_SEPARATORCSV)?',':$conf->global->ACCOUNTING_EXPORT_SEPARATORCSV,
125  'ACCOUNTING_EXPORT_ENDLINE' => empty($conf->global->ACCOUNTING_EXPORT_ENDLINE)?1:$conf->global->ACCOUNTING_EXPORT_ENDLINE,
126  'ACCOUNTING_EXPORT_DATE' => empty($conf->global->ACCOUNTING_EXPORT_DATE)?'%d%m%Y':$conf->global->ACCOUNTING_EXPORT_DATE,
127  ),
128  self::$EXPORT_TYPE_CEGID => array(
129  'label' => $langs->trans('Modelcsv_CEGID'),
130  ),
131  self::$EXPORT_TYPE_COALA => array(
132  'label' => $langs->trans('Modelcsv_COALA'),
133  ),
134  self::$EXPORT_TYPE_BOB50 => array(
135  'label' => $langs->trans('Modelcsv_bob50'),
136  ),
137  self::$EXPORT_TYPE_CIEL => array(
138  'label' => $langs->trans('Modelcsv_ciel'),
139  'ACCOUNTING_EXPORT_FORMAT' => 'txt',
140  ),
141  self::$EXPORT_TYPE_QUADRATUS => array(
142  'label' => $langs->trans('Modelcsv_quadratus'),
143  'ACCOUNTING_EXPORT_FORMAT' => 'txt',
144  ),
145  self::$EXPORT_TYPE_EBP => array(
146  'label' => $langs->trans('Modelcsv_ebp'),
147  ),
148  self::$EXPORT_TYPE_COGILOG => array(
149  'label' => $langs->trans('Modelcsv_cogilog'),
150  ),
151  self::$EXPORT_TYPE_AGIRIS => array(
152  'label' => $langs->trans('Modelcsv_agiris'),
153  ),
154  self::$EXPORT_TYPE_CONFIGURABLE => array(
155  'label' => $langs->trans('Modelcsv_configurable'),
156  'ACCOUNTING_EXPORT_FORMAT' => empty($conf->global->ACCOUNTING_EXPORT_FORMAT)?'txt':$conf->global->ACCOUNTING_EXPORT_FORMAT,
157  'ACCOUNTING_EXPORT_SEPARATORCSV' => empty($conf->global->ACCOUNTING_EXPORT_SEPARATORCSV)?',':$conf->global->ACCOUNTING_EXPORT_SEPARATORCSV,
158  'ACCOUNTING_EXPORT_ENDLINE' => empty($conf->global->ACCOUNTING_EXPORT_ENDLINE)?1:$conf->global->ACCOUNTING_EXPORT_ENDLINE,
159  'ACCOUNTING_EXPORT_DATE' => empty($conf->global->ACCOUNTING_EXPORT_DATE)?'%d%m%Y':$conf->global->ACCOUNTING_EXPORT_DATE,
160  ),
161  ),
162  'cr'=> array (
163  '1' => $langs->trans("Unix"),
164  '2' => $langs->trans("Windows")
165  ),
166  'format' => array (
167  'csv' => $langs->trans("csv"),
168  'txt' => $langs->trans("txt")
169  ),
170  );
171  }
172 
178  public static function downloadFile() {
179  global $conf;
180  $journal = 'bookkepping';
181  include DOL_DOCUMENT_ROOT . '/accountancy/tpl/export_journal.tpl.php';
182  }
183 
189  public function export(&$TData) {
190  global $conf, $langs;
191 
192  self::downloadFile();
193 
194  switch ($conf->global->ACCOUNTING_EXPORT_MODELCSV) {
195  case self::$EXPORT_TYPE_NORMAL :
196  $this->exportNormal($TData);
197  break;
198  case self::$EXPORT_TYPE_CEGID :
199  $this->exportCegid($TData);
200  break;
201  case self::$EXPORT_TYPE_COALA :
202  $this->exportCoala($TData);
203  break;
204  case self::$EXPORT_TYPE_BOB50 :
205  $this->exportBob50($TData);
206  break;
207  case self::$EXPORT_TYPE_CIEL :
208  $this->exportCiel($TData);
209  break;
210  case self::$EXPORT_TYPE_QUADRATUS :
211  $this->exportQuadratus($TData);
212  break;
213  case self::$EXPORT_TYPE_EBP :
214  $this->exportEbp($TData);
215  break;
216  case self::$EXPORT_TYPE_COGILOG :
217  $this->exportCogilog($TData);
218  break;
219  case self::$EXPORT_TYPE_AGIRIS :
220  $this->exportAgiris($TData);
221  break;
222  case self::$EXPORT_TYPE_CONFIGURABLE :
223  $this->exportConfigurable($TData);
224  break;
225  default:
226  $this->errors[] = $langs->trans('accountancy_error_modelnotfound');
227  break;
228  }
229  }
230 
238  public function exportNormal($objectLines) {
239  global $conf;
240 
241  foreach ( $objectLines as $line ) {
242  // Std export
243  $date = dol_print_date($line->doc_date, $conf->global->ACCOUNTING_EXPORT_DATE);
244  print $date . $this->separator;
245  print $line->doc_ref . $this->separator;
246  print length_accountg($line->numero_compte) . $this->separator;
247  print length_accounta($line->subledger_account) . $this->separator;
248  print price($line->debit) . $this->separator;
249  print price($line->credit) . $this->separator;
250  print $line->code_journal . $this->separator;
251  print $this->end_line;
252  }
253  }
254 
262  public function exportCegid($objectLines) {
263  foreach ( $objectLines as $line ) {
264  $date = dol_print_date($line->doc_date, '%d%m%Y');
265  $separator = ";";
266  $end_line = "\n";
267 
268  print $date . $separator;
269  print $line->code_journal . $separator;
270  print length_accountg($line->numero_compte) . $separator;
271  print length_accounta($line->subledger_account) . $separator;
272  print $line->sens . $separator;
273  print price($line->montant) . $separator;
274  print $line->label_operation . $separator;
275  print $line->doc_ref;
276  print $end_line;
277  }
278  }
279 
287  public function exportCogilog($objectLines) {
288  foreach ( $objectLines as $line ) {
289  $date = dol_print_date($line->doc_date, '%d%m%Y');
290  $separator = ";";
291  $end_line = "\n";
292 
293  print $line->code_journal . $separator;
294  print $date . $separator;
295  print $line->piece_num . $separator;
296  print length_accountg($line->numero_compte) . $separator;
297  print '' . $separator;
298  print $line->label_operation . $separator;
299  print $date . $separator;
300  if ($line->sens=='D') {
301  print price($line->montant) . $separator;
302  print '' . $separator;
303  }elseif ($line->sens=='C') {
304  print '' . $separator;
305  print price($line->montant) . $separator;
306  }
307  print $line->doc_ref . $separator;
308  print $line->label_operation . $separator;
309  print $end_line;
310  }
311  }
312 
320  public function exportCoala($objectLines) {
321  // Coala export
322  $separator = ";";
323  $end_line = "\n";
324 
325  foreach ( $objectLines as $line ) {
326  $date = dol_print_date($line->doc_date, '%d/%m/%Y');
327  print $date . $separator;
328  print $line->code_journal . $separator;
329  print length_accountg($line->numero_compte) . $separator;
330  print $line->piece_num . $separator;
331  print $line->doc_ref . $separator;
332  print price($line->debit) . $separator;
333  print price($line->credit) . $separator;
334  print 'E' . $separator;
335  print length_accountg($line->subledger_account) . $separator;
336  print $end_line;
337  }
338  }
339 
347  public function exportBob50($objectLines) {
348 
349  // Bob50
350  $separator = ";";
351  $end_line = "\n";
352 
353  foreach ( $objectLines as $line ) {
354  print $line->piece_num . $separator;
355  $date = dol_print_date($line->doc_date, '%d/%m/%Y');
356  print $date . $separator;
357 
358  if (empty($line->subledger_account)) {
359  print 'G' . $separator;
360  print length_accounta($line->numero_compte) . $separator;
361  } else {
362  if (substr($line->numero_compte, 0, 3) == '411') {
363  print 'C' . $separator;
364  }
365  if (substr($line->numero_compte, 0, 3) == '401') {
366  print 'F' . $separator;
367  }
368  print length_accountg($line->subledger_account) . $separator;
369  }
370 
371  print price($line->debit) . $separator;
372  print price($line->credit) . $separator;
373  print dol_trunc($line->label_operation, 32) . $separator;
374  print $end_line;
375  }
376  }
377 
385  public function exportCiel(&$TData) {
386  global $conf;
387 
388  $end_line ="\r\n";
389 
390  $i = 1;
391  $date_ecriture = dol_print_date(dol_now(), $conf->global->ACCOUNTING_EXPORT_DATE); // format must be yyyymmdd
392  foreach ( $TData as $data ) {
393  $code_compta = $data->numero_compte;
394  if (! empty($data->subledger_account))
395  $code_compta = $data->subledger_account;
396 
397  $Tab = array ();
398  $Tab['num_ecriture'] = str_pad($i, 5);
399  $Tab['code_journal'] = str_pad($data->code_journal, 2);
400  $Tab['date_ecriture'] = $date_ecriture;
401  $Tab['date_ope'] = dol_print_date($data->doc_date, $conf->global->ACCOUNTING_EXPORT_DATE);
402  $Tab['num_piece'] = str_pad(self::trunc($data->piece_num, 12), 12);
403  $Tab['num_compte'] = str_pad(self::trunc($code_compta, 11), 11);
404  $Tab['libelle_ecriture'] = str_pad(self::trunc(dol_string_unaccent($data->doc_ref) . dol_string_unaccent($data->label_operation), 25), 25);
405  $Tab['montant'] = str_pad(abs($data->montant), 13, ' ', STR_PAD_LEFT);
406  $Tab['type_montant'] = str_pad($data->sens, 1);
407  $Tab['vide'] = str_repeat(' ', 18);
408  $Tab['intitule_compte'] = str_pad(self::trunc(dol_string_unaccent($data->label_operation), 34), 34);
409  $Tab['end'] = 'O2003';
410 
411  $Tab['end_line'] = $end_line;
412 
413  print implode($Tab);
414  $i ++;
415  }
416  }
417 
425  public function exportQuadratus(&$TData) {
426  global $conf;
427 
428  $end_line ="\r\n";
429 
430  //We should use dol_now function not time however this is wrong date to transfert in accounting
431  //$date_ecriture = dol_print_date(dol_now(), $conf->global->ACCOUNTING_EXPORT_DATE); // format must be ddmmyy
432  //$date_ecriture = dol_print_date(time(), $conf->global->ACCOUNTING_EXPORT_DATE); // format must be ddmmyy
433  foreach ( $TData as $data ) {
434  $code_compta = $data->numero_compte;
435  if (! empty($data->subledger_account))
436  $code_compta = $data->subledger_account;
437 
438  $Tab = array ();
439  $Tab['type_ligne'] = 'M';
440  $Tab['num_compte'] = str_pad(self::trunc($code_compta, 8), 8);
441  $Tab['code_journal'] = str_pad(self::trunc($data->code_journal, 2), 2);
442  $Tab['folio'] = '000';
443 
444  //We use invoice date $data->doc_date not $date_ecriture which is the transfert date
445  //maybe we should set an option for customer who prefer to keep in accounting software the tranfert date instead of invoice date ?
446  //$Tab['date_ecriture'] = $date_ecriture;
447  $Tab['date_ecriture'] = dol_print_date($data->doc_date, '%d%m%y');
448  $Tab['filler'] = ' ';
449  $Tab['libelle_ecriture'] = str_pad(self::trunc(dol_string_unaccent($data->doc_ref) . ' ' . dol_string_unaccent($data->label_operation), 20), 20);
450  $Tab['sens'] = $data->sens; // C or D
451  $Tab['signe_montant'] = '+';
452 
453  //elarifr le montant doit etre en centimes sans point decimal !
454  $Tab['montant'] = str_pad(abs($data->montant*100), 12, '0', STR_PAD_LEFT); // TODO manage negative amount
455  // $Tab['montant'] = str_pad(abs($data->montant), 12, '0', STR_PAD_LEFT); // TODO manage negative amount
456  $Tab['contrepartie'] = str_repeat(' ', 8);
457 
458  // elarifr: date format must be fixed format : 6 char ddmmyy = %d%m%yand not defined by user / dolibarr setting
459  if (! empty($data->date_echeance))
460  //$Tab['date_echeance'] = dol_print_date($data->date_echeance, $conf->global->ACCOUNTING_EXPORT_DATE);
461  $Tab['date_echeance'] = dol_print_date($data->date_echeance, '%d%m%y' ); // elarifr: format must be ddmmyy
462  else
463  $Tab['date_echeance'] = '000000';
464 
465  //elarifr please keep quadra named field lettrage(2) + codestat(3) instead of fake lettrage(5)
466  //$Tab['lettrage'] = str_repeat(' ', 5);
467  $Tab['lettrage'] = str_repeat(' ', 2);
468  $Tab['codestat'] = str_repeat(' ', 3);
469  $Tab['num_piece'] = str_pad(self::trunc($data->piece_num, 5), 5);
470 
471  //elarifr keep correct quadra named field instead of anon filler
472  //$Tab['filler2'] = str_repeat(' ', 20);
473  $Tab['affaire'] = str_repeat(' ', 10);
474  $Tab['quantity1'] = str_repeat(' ', 10);
475  $Tab['num_piece2'] = str_pad(self::trunc($data->piece_num, 8), 8);
476  $Tab['devis'] = str_pad($conf->currency, 3);
477  $Tab['code_journal2'] = str_pad(self::trunc($data->code_journal, 3), 3);
478  $Tab['filler3'] = str_repeat(' ', 3);
479 
480  //elarifr keep correct quadra named field instead of anon filler libelle_ecriture2 is 30 char not 32 !!!!
481  //as we use utf8, we must remove accent to have only one ascii char instead of utf8 2 chars for specials that report wrong line size that will exceed import format spec
482  //todo we should filter more than only accent to avoid wrong line size
483  //TODO: remove invoice number doc_ref in libelle,
484  //TODO: we should offer an option for customer to build the libelle using invoice number / name / date in accounting software
485  //$Tab['libelle_ecriture2'] = str_pad(self::trunc(dol_string_unaccent($data->doc_ref) . ' ' . dol_string_unaccent($data->label_operation), 30), 30);
486  $Tab['libelle_ecriture2'] = str_pad(self::trunc(dol_string_unaccent($data->label_operation), 30), 30);
487  $Tab['codetva'] = str_repeat(' ', 2);
488 
489  //elarifr we need to keep the 10 lastest number of invoice doc_ref not the beginning part that is the unusefull almost same part
490  //$Tab['num_piece3'] = str_pad(self::trunc($data->piece_num, 10), 10);
491  $Tab['num_piece3'] = substr(self::trunc($data->doc_ref, 20), -10);
492  $Tab['filler4'] = str_repeat(' ', 73);
493 
494  $Tab['end_line'] = $end_line;
495 
496  print implode($Tab);
497  }
498  }
499 
500 
508  public function exportEbp($objectLines) {
509 
510  $separator = ',';
511  $end_line = "\n";
512 
513  foreach ( $objectLines as $line ) {
514 
515  $date = dol_print_date($line->doc_date, '%d%m%Y');
516 
517  print $line->id . $separator;
518  print $date . $separator;
519  print $line->code_journal . $separator;
520  print length_accountg($line->numero_compte) . $separator;
521  print substr(length_accountg($line->numero_compte),0,2) . $separator;
522  print '"'.dol_trunc($line->label_operation,40,'right','UTF-8',1).'"' . $separator;
523  print '"'.dol_trunc($line->piece_num,15,'right','UTF-8',1).'"'.$separator;
524  print price2num($line->montant).$separator;
525  print $line->sens.$separator;
526  print $date . $separator;
527  print 'EUR';
528  print $end_line;
529  }
530  }
531 
532 
540  public function exportAgiris($objectLines) {
541 
542  $separator = ';';
543  $end_line = "\n";
544 
545  foreach ( $objectLines as $line ) {
546 
547  $date = dol_print_date($line->doc_date, '%d%m%Y');
548 
549  print $line->piece_num . $separator;
550  print $line->label_operation . $separator;
551  print $date . $separator;
552  print $line->label_operation . $separator;
553 
554  if (empty($line->subledger_account)) {
555  print length_accountg($line->numero_compte) . $separator;
556  } else {
557  print length_accounta($line->subledger_account) . $separator;
558  }
559 
560  print $line->doc_ref . $separator;
561  print price($line->debit) . $separator;
562  print price($line->credit) . $separator;
563  print price($line->montant) . $separator;
564  print $line->sens . $separator;
565  print $line->code_journal;
566  print $end_line;
567  }
568  }
569 
577  public function exportConfigurable($objectLines) {
578  global $conf;
579 
580  foreach ($objectLines as $line) {
581  $tab = array();
582  // export configurable
583  $date = dol_print_date($line->doc_date, $conf->global->ACCOUNTING_EXPORT_DATE);
584  $tab[] = $line->piece_num;
585  $tab[] = $date;
586  $tab[] = $line->doc_ref;
587  $tab[] = $line->label_operation;
588  $tab[] = length_accountg($line->numero_compte);
589  $tab[] = length_accounta($line->subledger_account);
590  $tab[] = price($line->debit);
591  $tab[] = price($line->credit);
592  $tab[] = price($line->montant);
593  $tab[] = $line->code_journal;
594 
595  $separator = $this->separator;
596  print implode($separator, $tab) . $this->end_line;
597  }
598  }
599 
600 
606  public static function trunc($str, $size) {
607  return dol_trunc($str, $size, 'right', 'UTF-8', 1);
608  }
609 }
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '...' if string larger than length.
static downloadFile()
Download the export.
static getTypeConfig()
Array with all export type available (key + label) and parameters for config.
Class to manage Dolibarr database access.
static trunc($str, $size)
__construct(DoliDB &$db)
Constructor.
exportNormal($objectLines)
Export format : Normal.
exportCoala($objectLines)
Export format : COALA.
exportBob50($objectLines)
Export format : BOB50.
exportCogilog($objectLines)
Export format : COGILOG.
static getType()
Array with all export type available (key + label)
exportQuadratus(&$TData)
Export format : Quadratus.
length_accounta($accounta)
Return Auxiliary accounting account of thirdparties with defined length.
exportAgiris($objectLines)
Export format : Agiris Isacompta.
dol_string_unaccent($str)
Clean a string from all accent characters to be used as ref, login or by dol_sanitizeFileName.
dol_now($mode='gmt')
Return date for now.
exportCegid($objectLines)
Export format : CEGID.
exportConfigurable($objectLines)
Export format : Configurable.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages...
dol_print_date($time, $format='', $tzoutput='tzserver', $outputlangs='', $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
print
Draft customers invoices.
Definition: index.php:91
Class AccountancyExport.
exportCiel(&$TData)
Export format : CIEL.
exportEbp($objectLines)
Export format : EBP.
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous) ...
export(&$TData)
Function who chose which export to use with the default config.
price2num($amount, $rounding='', $alreadysqlnb=0)
Function that return a number with universal decimal format (decimal separator is '...