dolibarr  17.0.3
intracommreport.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2015 ATM Consulting <support@atm-consulting.fr>
3  * Copyright (C) 2019-2020 Open-DSI <support@open-dsi.fr>
4  * Copyright (C) 2020 Frédéric France <frederic.france@netlogic.fr>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  */
19 
27 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
28 
29 
34 {
38  public $element = 'intracommreport';
39 
43  public $table_element = 'intracommreport';
44 
48  public $fk_element = 'fk_intracommreport';
49 
54  public $ismultientitymanaged = 1;
55 
56  public $picto = 'intracommreport';
57 
58 
59  public $label; // ref ???
60 
61  public $period;
62 
63  public $declaration;
64 
68  public $declaration_number;
69 
70  public $type_declaration; // deb or des
71 
72 
76  const TYPE_DEB = 0;
77 
81  const TYPE_DES = 1;
82 
83  public static $type = array(
84  'introduction'=>'Introduction',
85  'expedition'=>'Expédition'
86  );
87 
88 
94  public function __construct(DoliDB $db)
95  {
96  $this->db = $db;
97  $this->exporttype = 'deb';
98  }
99 
107  public function create($user, $notrigger = 0)
108  {
109  return 1;
110  }
111 
118  public function fetch($id)
119  {
120  return 1;
121  }
122 
131  public function delete($id, $user, $notrigger = 0)
132  {
133  return 1;
134  }
135 
144  public function getXML($mode = 'O', $type = 'introduction', $period_reference = '')
145  {
146  global $conf, $mysoc;
147 
148  /**************Construction de quelques variables********************/
149  $party_id = substr(strtr($mysoc->tva_intra, array(' '=>'')), 0, 4).$mysoc->idprof2;
150  $declarant = substr($mysoc->managers, 0, 14);
151  $id_declaration = self::getDeclarationNumber($this->numero_declaration);
152  /********************************************************************/
153 
154  /**************Construction du fichier XML***************************/
155  $e = new SimpleXMLElement('<?xml version="1.0" encoding="utf-8" standalone="yes"?><INSTAT></INSTAT>');
156 
157  $enveloppe = $e->addChild('Envelope');
158  $enveloppe->addChild('envelopeId', $conf->global->INTRACOMMREPORT_NUM_AGREMENT);
159  $date_time = $enveloppe->addChild('DateTime');
160  $date_time->addChild('date', date('Y-m-d'));
161  $date_time->addChild('time', date('H:i:s'));
162  $party = $enveloppe->addChild('Party');
163  $party->addAttribute('partyType', $conf->global->INTRACOMMREPORT_TYPE_ACTEUR);
164  $party->addAttribute('partyRole', $conf->global->INTRACOMMREPORT_ROLE_ACTEUR);
165  $party->addChild('partyId', $party_id);
166  $party->addChild('partyName', $declarant);
167  $enveloppe->addChild('softwareUsed', 'Dolibarr');
168  $declaration = $enveloppe->addChild('Declaration');
169  $declaration->addChild('declarationId', $id_declaration);
170  $declaration->addChild('referencePeriod', $period_reference);
171  if ($conf->global->INTRACOMMREPORT_TYPE_ACTEUR === 'PSI') {
172  $psiId = $party_id;
173  } else {
174  $psiId = 'NA';
175  }
176  $declaration->addChild('PSIId', $psiId);
177  $function = $declaration->addChild('Function');
178  $functionCode = $function->addChild('functionCode', $mode);
179  $declaration->addChild('declarationTypeCode', $conf->global->{'INTRACOMMREPORT_NIV_OBLIGATION_'.strtoupper($type)});
180  $declaration->addChild('flowCode', ($type == 'introduction' ? 'A' : 'D'));
181  $declaration->addChild('currencyCode', $conf->global->MAIN_MONNAIE);
182  /********************************************************************/
183 
184  /**************Ajout des lignes de factures**************************/
185  $res = $this->addItemsFact($declaration, $type, $period_reference);
186  /********************************************************************/
187 
188  $this->errors = array_unique($this->errors);
189 
190  if (!empty($res)) {
191  return $e->asXML();
192  } else {
193  return 0;
194  }
195  }
196 
205  public function getXMLDes($period_year, $period_month, $type_declaration = 'expedition')
206  {
207  global $mysoc;
208 
209  $e = new SimpleXMLElement('<?xml version="1.0" encoding="utf-8" ?><fichier_des></fichier_des>');
210 
211  $declaration_des = $e->addChild('declaration_des');
212  $declaration_des->addChild('num_des', self::getDeclarationNumber($this->numero_declaration));
213  $declaration_des->addChild('num_tvaFr', $mysoc->tva_intra); // /^FR[a-Z0-9]{2}[0-9]{9}$/ // Doit faire 13 caractères
214  $declaration_des->addChild('mois_des', $period_month);
215  $declaration_des->addChild('an_des', $period_year);
216 
217  /**************Ajout des lignes de factures**************************/
218  $res = $this->addItemsFact($declaration_des, $type_declaration, $period_year.'-'.$period_month, 'des');
219  /********************************************************************/
220 
221  $this->errors = array_unique($this->errors);
222 
223  if (!empty($res)) {
224  return $e->asXML();
225  } else {
226  return 0;
227  }
228  }
229 
239  public function addItemsFact(&$declaration, $type, $period_reference, $exporttype = 'deb')
240  {
241  global $conf;
242 
243  require_once DOL_DOCUMENT_ROOT.'/categories/class/categorie.class.php';
244 
245  $sql = $this->getSQLFactLines($type, $period_reference, $exporttype);
246 
247  $resql = $this->db->query($sql);
248 
249  if ($resql) {
250  $i = 1;
251 
252  if ($this->db->num_rows($resql) <= 0) {
253  $this->errors[] = 'No data for this period';
254  return 0;
255  }
256 
257  if ($exporttype == 'deb' && $conf->global->INTRACOMMREPORT_CATEG_FRAISDEPORT > 0) {
258  $categ_fraisdeport = new Categorie($this->db);
259  $categ_fraisdeport->fetch($conf->global->INTRACOMMREPORT_CATEG_FRAISDEPORT);
260  $TLinesFraisDePort = array();
261  }
262 
263  while ($res = $this->db->fetch_object($resql)) {
264  if ($exporttype == 'des') {
265  $this->addItemXMlDes($declaration, $res, $i);
266  } else {
267  if (empty($res->fk_pays)) {
268  // We don't stop the loop because we want to know all the third parties who don't have an informed country
269  $this->errors[] = 'Country not filled in for the third party <a href="'.dol_buildpath('/societe/soc.php', 1).'?socid='.$res->id_client.'">'.$res->nom.'</a>';
270  } else {
271  if ($conf->global->INTRACOMMREPORT_CATEG_FRAISDEPORT > 0 && $categ_fraisdeport->containsObject('product', $res->id_prod)) {
272  $TLinesFraisDePort[] = $res;
273  } else {
274  $this->addItemXMl($declaration, $res, $i, '');
275  }
276  }
277  }
278 
279  $i++;
280  }
281 
282  if (!empty($TLinesFraisDePort)) {
283  $this->addItemFraisDePort($declaration, $TLinesFraisDePort, $type, $categ_fraisdeport, $i);
284  }
285 
286  if (count($this->errors) > 0) {
287  return 0;
288  }
289  }
290 
291  return 1;
292  }
293 
302  public function getSQLFactLines($type, $period_reference, $exporttype = 'deb')
303  {
304  global $mysoc, $conf;
305 
306  if ($type == 'expedition' || $exporttype == 'des') {
307  $sql = "SELECT f.ref as refinvoice, f.total_ht";
308  $table = 'facture';
309  $table_extraf = 'facture_extrafields';
310  $tabledet = 'facturedet';
311  $field_link = 'fk_facture';
312  } else { // Introduction
313  $sql = "SELECT f.ref_supplier as refinvoice, f.total_ht";
314  $table = 'facture_fourn';
315  $table_extraf = 'facture_fourn_extrafields';
316  $tabledet = 'facture_fourn_det';
317  $field_link = 'fk_facture_fourn';
318  }
319  $sql .= ", l.fk_product, l.qty
320  , p.weight, p.rowid as id_prod, p.customcode
321  , s.rowid as id_client, s.nom, s.zip, s.fk_pays, s.tva_intra
322  , c.code
323  , ext.mode_transport
324  FROM ".MAIN_DB_PREFIX.$tabledet." l
325  INNER JOIN ".MAIN_DB_PREFIX.$table." f ON (f.rowid = l.".$this->db->escape($field_link).")
326  LEFT JOIN ".MAIN_DB_PREFIX.$table_extraf." ext ON (ext.fk_object = f.rowid)
327  INNER JOIN ".MAIN_DB_PREFIX."product p ON (p.rowid = l.fk_product)
328  INNER JOIN ".MAIN_DB_PREFIX."societe s ON (s.rowid = f.fk_soc)
329  LEFT JOIN ".MAIN_DB_PREFIX."c_country c ON (c.rowid = s.fk_pays)
330  WHERE f.fk_statut > 0
331  AND l.product_type = ".($exporttype == "des" ? 1 : 0)."
332  AND f.entity = ".((int) $conf->entity)."
333  AND (s.fk_pays <> ".((int) $mysoc->country_id)." OR s.fk_pays IS NULL)
334  AND f.datef BETWEEN '".$this->db->escape($period_reference)."-01' AND '".$this->db->escape($period_reference)."-".date('t')."'";
335 
336  return $sql;
337  }
338 
348  public function addItemXMl(&$declaration, &$res, $i, $code_douane_spe = '')
349  {
350  $item = $declaration->addChild('Item');
351  $item->addChild('itemNumber', $i);
352  $cn8 = $item->addChild('CN8');
353  if (empty($code_douane_spe)) {
354  $code_douane = $res->customcode;
355  } else {
356  $code_douane = $code_douane_spe;
357  }
358  $cn8->addChild('CN8Code', $code_douane);
359  $item->addChild('MSConsDestCode', $res->code); // code iso pays client
360  $item->addChild('countryOfOriginCode', substr($res->zip, 0, 2)); // code iso pays d'origine
361  $item->addChild('netMass', round($res->weight * $res->qty)); // Poids du produit
362  $item->addChild('quantityInSU', $res->qty); // Quantité de produit dans la ligne
363  $item->addChild('invoicedAmount', round($res->total_ht)); // Montant total ht de la facture (entier attendu)
364  // $item->addChild('invoicedNumber', $res->refinvoice); // Numéro facture
365  if (!empty($res->tva_intra)) {
366  $item->addChild('partnerId', $res->tva_intra);
367  }
368  $item->addChild('statisticalProcedureCode', '11');
369  $nature_of_transaction = $item->addChild('NatureOfTransaction');
370  $nature_of_transaction->addChild('natureOfTransactionACode', 1);
371  $nature_of_transaction->addChild('natureOfTransactionBCode', 1);
372  $item->addChild('modeOfTransportCode', $res->mode_transport);
373  $item->addChild('regionCode', substr($res->zip, 0, 2));
374  }
375 
384  public function addItemXMlDes($declaration, &$res, $i)
385  {
386  $item = $declaration->addChild('ligne_des');
387  $item->addChild('numlin_des', $i);
388  $item->addChild('valeur', round($res->total_ht)); // Total amount excl. tax of the invoice (whole amount expected)
389  $item->addChild('partner_des', $res->tva_intra); // Represents the foreign customer's VAT number
390  }
391 
402  public function addItemFraisDePort(&$declaration, &$TLinesFraisDePort, $type, &$categ_fraisdeport, $i)
403  {
404 
405  global $conf;
406 
407  if ($type == 'expedition') {
408  $table = 'facture';
409  $tabledet = 'facturedet';
410  $field_link = 'fk_facture';
411  $more_sql = 'f.ref';
412  } else { // Introduction
413  $table = 'facture_fourn';
414  $tabledet = 'facture_fourn_det';
415  $field_link = 'fk_facture_fourn';
416  $more_sql = 'f.ref_supplier';
417  }
418 
419  foreach ($TLinesFraisDePort as $res) {
420  $sql = "SELECT p.customcode
421  FROM ".MAIN_DB_PREFIX.$tabledet." d
422  INNER JOIN ".MAIN_DB_PREFIX.$table." f ON (f.rowid = d.".$this->db->escape($field_link).")
423  INNER JOIN ".MAIN_DB_PREFIX."product p ON (p.rowid = d.fk_product)
424  WHERE d.fk_product IS NOT NULL
425  AND f.entity = ".((int) $conf->entity)."
426  AND ".$more_sql." = '".$this->db->escape($res->refinvoice)."'
427  AND d.total_ht =
428  (
429  SELECT MAX(d.total_ht)
430  FROM ".MAIN_DB_PREFIX.$tabledet." d
431  INNER JOIN ".MAIN_DB_PREFIX.$table." f ON (f.rowid = d.".$this->db->escape($field_link).")
432  WHERE d.fk_product IS NOT NULL
433  AND ".$more_sql." = '".$this->db->escape($res->refinvoice)."'
434  AND d.fk_product NOT IN
435  (
436  SELECT fk_product
437  FROM ".MAIN_DB_PREFIX."categorie_product
438  WHERE fk_categorie = ".((int) $categ_fraisdeport->id)."
439  )
440  )";
441 
442  $resql = $this->db->query($sql);
443  $ress = $this->db->fetch_object($resql);
444 
445  $this->addItemXMl($declaration, $res, $i, $ress->customcode);
446 
447  $i++;
448  }
449  }
450 
456  public function getNextDeclarationNumber()
457  {
458  $sql = "SELECT MAX(numero_declaration) as max_declaration_number FROM ".MAIN_DB_PREFIX.$this->table_element;
459  $sql .= " WHERE exporttype = '".$this->db->escape($this->exporttype)."'";
460  $resql = $this->db->query($sql);
461  if ($resql) {
462  $res = $this->db->fetch_object($resql);
463  }
464 
465  return ($res->max_declaration_number + 1);
466  }
467 
474  public static function getDeclarationNumber($number)
475  {
476  return str_pad($number, 6, 0, STR_PAD_LEFT);
477  }
478 
484  public function generateXMLFile()
485  {
486  $name = $this->periode.'.xml';
487  $fname = sys_get_temp_dir().'/'.$name;
488  $f = fopen($fname, 'w+');
489  fwrite($f, $this->content_xml);
490  fclose($f);
491 
492  header('Content-Description: File Transfer');
493  header('Content-Type: application/xml');
494  header('Content-Disposition: attachment; filename="'.$name.'"');
495  header('Expires: 0');
496  header('Cache-Control: must-revalidate');
497  header('Pragma: public');
498  header('Content-Length: '.filesize($fname));
499  readfile($fname);
500  exit;
501  }
502 }
db
$conf db
API class for accounts.
Definition: inc.php:41
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:30
IntracommReport\getXMLDes
getXMLDes($period_year, $period_month, $type_declaration='expedition')
Generate XMLDes file.
Definition: intracommreport.class.php:205
IntracommReport
Class to manage intracomm report.
Definition: intracommreport.class.php:33
IntracommReport\generateXMLFile
generateXMLFile()
Generate XML file.
Definition: intracommreport.class.php:484
IntracommReport\getDeclarationNumber
static getDeclarationNumber($number)
Verify declaration number.
Definition: intracommreport.class.php:474
IntracommReport\fetch
fetch($id)
Function fetch.
Definition: intracommreport.class.php:118
Categorie
Class to manage categories.
Definition: categorie.class.php:47
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition: commonobject.class.php:44
IntracommReport\getSQLFactLines
getSQLFactLines($type, $period_reference, $exporttype='deb')
Add invoice line.
Definition: intracommreport.class.php:302
IntracommReport\getXML
getXML($mode='O', $type='introduction', $period_reference='')
Generate XML file.
Definition: intracommreport.class.php:144
IntracommReport\__construct
__construct(DoliDB $db)
Constructor.
Definition: intracommreport.class.php:94
IntracommReport\getNextDeclarationNumber
getNextDeclarationNumber()
Return next reference of declaration not already used (or last reference)
Definition: intracommreport.class.php:456
IntracommReport\TYPE_DEB
const TYPE_DEB
DEB - Product.
Definition: intracommreport.class.php:76
$resql
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
Definition: index.php:745
IntracommReport\addItemXMlDes
addItemXMlDes($declaration, &$res, $i)
Add item for DES.
Definition: intracommreport.class.php:384
IntracommReport\addItemFraisDePort
addItemFraisDePort(&$declaration, &$TLinesFraisDePort, $type, &$categ_fraisdeport, $i)
This function adds an item by retrieving the customs code of the product with the highest amount in t...
Definition: intracommreport.class.php:402
IntracommReport\create
create($user, $notrigger=0)
Function create.
Definition: intracommreport.class.php:107
IntracommReport\TYPE_DES
const TYPE_DES
DES - Service.
Definition: intracommreport.class.php:81
IntracommReport\addItemsFact
addItemsFact(&$declaration, $type, $period_reference, $exporttype='deb')
Add line from invoice.
Definition: intracommreport.class.php:239
IntracommReport\addItemXMl
addItemXMl(&$declaration, &$res, $i, $code_douane_spe='')
Add item for DEB.
Definition: intracommreport.class.php:348