dolibarr  9.0.0
lettering.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2004-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3  * Copyright (C) 2013 Olivier Geffroy <jeff@jeffinfo.com>
4  * Copyright (C) 2013-2018 Alexandre Spangaro <aspangaro@zendsi.com>
5  * Copyright (C) 2018 Frédéric France <frederic.france@netlogic.fr>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 2 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program. If not, see <http://www.gnu.org/licenses/>.
19  */
20 
27 include_once DOL_DOCUMENT_ROOT . "/accountancy/class/bookkeeping.class.php";
28 include_once DOL_DOCUMENT_ROOT . "/societe/class/societe.class.php";
29 include_once DOL_DOCUMENT_ROOT . "/core/lib/date.lib.php";
30 
34 class Lettering extends BookKeeping
35 {
42  public function letteringThirdparty($socid)
43  {
44  global $conf;
45 
46  $error = 0;
47 
48  $object = new Societe($this->db);
49  $object->id = $socid;
50  $object->fetch($socid);
51 
52 
53  if ($object->code_compta == '411CUSTCODE') {
54  $object->code_compta = '';
55  }
56 
57  if ($object->code_compta_fournisseur == '401SUPPCODE') {
58  $object->code_compta_fournisseur = '';
59  }
60 
64  $sql = "SELECT DISTINCT bk.rowid, bk.doc_date, bk.doc_type, bk.doc_ref, bk.subledger_account, ";
65  $sql .= " bk.numero_compte , bk.label_compte, bk.debit , bk.credit, bk.montant ";
66  $sql .= " , bk.sens , bk.code_journal , bk.piece_num, bk.date_lettering, bu.url_id , bu.type ";
67  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk";
68  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "bank_url as bu ON(bk.fk_doc = bu.fk_bank AND bu.type IN ('payment', 'payment_supplier') ) ";
69  $sql .= " WHERE ( ";
70  if (! empty($object->code_compta))
71  $sql .= " bk.subledger_account = '" . $object->code_compta . "' ";
72  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur))
73  $sql .= " OR ";
74  if (! empty($object->code_compta_fournisseur))
75  $sql .= " bk.subledger_account = '" . $object->code_compta_fournisseur . "' ";
76 
77  $sql .= " ) AND (bk.date_lettering ='' OR bk.date_lettering IS NULL) ";
78  $sql .= " AND (bk.lettering_code != '' OR bk.lettering_code IS NULL) ";
79  $sql .= $this->db->order('bk.doc_date', 'DESC');
80 
81  // echo $sql;
82  //
83  $resql = $this->db->query($sql);
84  if ($resql) {
85  $num = $this->db->num_rows($resql);
86 
87  while ($obj = $this->db->fetch_object($resql) ) {
88  $ids = array();
89  $ids_fact = array();
90 
91  if ($obj->type == 'payment_supplier')
92  {
93  $sql = 'SELECT DISTINCT bk.rowid, facf.ref, facf.ref_supplier, payf.fk_bank, facf.rowid as fact_id';
94  $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn facf ";
95  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiementfourn_facturefourn as payfacf ON payfacf.fk_facturefourn=facf.rowid";
96  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiementfourn as payf ON payfacf.fk_paiementfourn=payf.rowid";
97  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON (bk.fk_doc = payf.fk_bank AND bk.code_journal='" . $obj->code_journal . "')";
98  $sql .= " WHERE payfacf.fk_paiementfourn = '" . $obj->url_id . "' ";
99  $sql .= " AND facf.entity = ".$conf->entity;
100  $sql .= " AND code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=4 AND entity=".$conf->entity.") ";
101  $sql .= " AND ( ";
102  if (! empty($object->code_compta)) {
103  $sql .= " bk.subledger_account = '" . $object->code_compta . "' ";
104  }
105  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
106  $sql .= " OR ";
107  }
108  if (! empty($object->code_compta_fournisseur)) {
109  $sql .= " bk.subledger_account = '" . $object->code_compta_fournisseur . "' ";
110  }
111  $sql .= " ) ";
112 
113  $resql2 = $this->db->query($sql);
114  if ($resql2) {
115  while ( $obj2 = $this->db->fetch_object($resql2) ) {
116  $ids[$obj2->rowid] = $obj2->rowid;
117  $ids_fact[] = $obj2->fact_id;
118  }
119  } else {
120  $this->errors[] = $this->db->lasterror;
121  return - 1;
122  }
123  if (count($ids_fact)) {
124  $sql = 'SELECT bk.rowid, facf.ref, facf.ref_supplier ';
125  $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn facf ";
126  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON( bk.fk_doc = facf.rowid AND facf.rowid IN (" . implode(',', $ids_fact) . "))";
127  $sql .= " WHERE bk.code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=3 AND entity=".$conf->entity.") ";
128  $sql .= " AND facf.entity = ".$conf->entity;
129  $sql .= " AND ( ";
130  if (! empty($object->code_compta)) {
131  $sql .= " bk.subledger_account = '" . $object->code_compta . "' ";
132  }
133  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
134  $sql .= " OR ";
135  }
136  if (! empty($object->code_compta_fournisseur)) {
137  $sql .= " bk.subledger_account = '" . $object->code_compta_fournisseur . "' ";
138  }
139  $sql .= " ) ";
140 
141  $resql2 = $this->db->query($sql);
142  if ($resql2) {
143  while ( $obj2 = $this->db->fetch_object($resql2) ) {
144  $ids[$obj2->rowid] = $obj2->rowid;
145  }
146  } else {
147  $this->errors[] = $this->db->lasterror;
148  return - 1;
149  }
150  }
151  } elseif ($obj->type == 'payment') {
152 
153  $sql = 'SELECT DISTINCT bk.rowid, fac.ref, fac.ref, pay.fk_bank, fac.rowid as fact_id';
154  $sql .= " FROM " . MAIN_DB_PREFIX . "facture fac ";
155  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiement_facture as payfac ON payfac.fk_facture=fac.rowid";
156  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiement as pay ON payfac.fk_paiement=pay.rowid";
157  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON (bk.fk_doc = pay.fk_bank AND bk.code_journal='" . $obj->code_journal . "')";
158  $sql .= " WHERE payfac.fk_paiement = '" . $obj->url_id . "' ";
159  $sql .= " AND bk.code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=4 AND entity=".$conf->entity.") ";
160  $sql .= " AND fac.entity = ".$conf->entity;
161  $sql .= " AND ( ";
162  if (! empty($object->code_compta)) {
163  $sql .= " bk.subledger_account = '" . $object->code_compta . "' ";
164  }
165  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
166  $sql .= " OR ";
167  }
168  if (! empty($object->code_compta_fournisseur)) {
169  $sql .= " bk.subledger_account = '" . $object->code_compta_fournisseur . "' ";
170  }
171  $sql .= " ) ";
172 
173  $resql2 = $this->db->query($sql);
174  if ($resql2) {
175  while ( $obj2 = $this->db->fetch_object($resql2) ) {
176  $ids[$obj2->rowid] = $obj2->rowid;
177  $ids_fact[] = $obj2->fact_id;
178  }
179  } else {
180  $this->errors[] = $this->db->lasterror;
181  return - 1;
182  }
183  if (count($ids_fact)) {
184  $sql = 'SELECT bk.rowid, fac.ref, fac.ref_supplier ';
185  $sql .= " FROM " . MAIN_DB_PREFIX . "facture fac ";
186  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON( bk.fk_doc = fac.rowid AND fac.rowid IN (" . implode(',', $ids_fact) . "))";
187  $sql .= " WHERE code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=2 AND entity=".$conf->entity.") ";
188  $sql .= " AND fac.entity = ".$conf->entity;
189  $sql .= " AND ( ";
190  if (! empty($object->code_compta)) {
191  $sql .= " bk.subledger_account = '" . $object->code_compta . "' ";
192  }
193  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
194  $sql .= " OR ";
195  }
196  if (! empty($object->code_compta_fournisseur)) {
197  $sql .= " bk.subledger_account = '" . $object->code_compta_fournisseur . "' ";
198  }
199  $sql .= " ) ";
200 
201  $resql2 = $this->db->query($sql);
202  if ($resql2) {
203  while ( $obj2 = $this->db->fetch_object($resql2) ) {
204  $ids[$obj2->rowid] = $obj2->rowid;
205  }
206  } else {
207  $this->errors[] = $this->db->lasterror;
208  return - 1;
209  }
210  }
211  }
212 
213  if (count($ids) > 1) {
214  $result = $this->updateLettering($ids);
215  }
216  }
217  }
218  if ($error) {
219  foreach ( $this->errors as $errmsg ) {
220  dol_syslog(get_class($this) . "::" . __METHOD__ . $errmsg, LOG_ERR);
221  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
222  }
223  return - 1 * $error;
224  } else {
225  return 1;
226  }
227  }
228 
235  public function updateLettering($ids = array(), $notrigger = false)
236  {
237  $error = 0;
238  $lettre = 'AAA';
239 
240  dol_syslog(get_class($this) . "::" . __METHOD__, LOG_DEBUG);
241 
242  $sql = "SELECT DISTINCT lettering_code FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE ";
243  $sql .= " lettering_code != '' ORDER BY lettering_code DESC limit 1; ";
244 
245  $result = $this->db->query($sql);
246  if ($result) {
247  $obj = $this->db->fetch_object($result);
248  $lettre = (empty($obj->lettering_code) ? 'AAA' : $obj->lettering_code);
249  if (! empty($obj->lettering_code))
250  $lettre++;
251  } else {
252  $this->errors[] = 'Error' . $this->db->lasterror();
253  $error++;
254  }
255 
256  $sql = "SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE ";
257  $sql .= " rowid IN (" . implode(',', $ids) . ") ";
258  $result = $this->db->query($sql);
259  if ($result) {
260  $obj = $this->db->fetch_object($result);
261  if (! (round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
262  $this->errors[] = 'Total not exacts ' . round(abs($obj->deb), 2) . ' vs ' . round(abs($obj->cred), 2);
263  $error++;
264  }
265  } else {
266  $this->errors[] = 'Erreur sql' . $this->db->lasterror();
267  $error++;
268  }
269 
270  // Update request
271 
272  $now = dol_now();
273 
274  if (! $error)
275  {
276  $sql = "UPDATE " . MAIN_DB_PREFIX . "accounting_bookkeeping SET";
277  $sql .= " lettering_code='" . $lettre . "'";
278  $sql .= " , date_lettering = '" . $this->db->idate($now) . "'"; // todo correct date it's false
279  $sql .= " WHERE rowid IN (" . implode(',', $ids) . ") ";
280  $this->db->begin();
281 
282  dol_syslog(get_class($this) . "::update sql=" . $sql, LOG_DEBUG);
283  $resql = $this->db->query($sql);
284  if (! $resql) {
285  $error++;
286  $this->errors[] = "Error " . $this->db->lasterror();
287  }
288  }
289 
290  if (! $error) {
291  if (! $notrigger) {
292  // Uncomment this and change MYOBJECT to your own tag if you
293  // want this action calls a trigger.
294 
295  // // Call triggers
296  // include_once DOL_DOCUMENT_ROOT . '/core/class/interfaces.class.php';
297  // $interface=new Interfaces($this->db);
298  // $result=$interface->run_triggers('MYOBJECT_MODIFY',$this,$user,$langs,$conf);
299  // if ($result < 0) { $error++; $this->errors=$interface->errors; }
300  // // End call triggers
301  }
302  }
303  // Commit or rollback
304  if ($error) {
305  foreach ( $this->errors as $errmsg ) {
306  dol_syslog(get_class($this) . "::update " . $errmsg, LOG_ERR);
307  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
308  }
309  $this->db->rollback();
310  return - 1 * $error;
311  } else {
312  $this->db->commit();
313  return 1;
314  }
315  }
316 }
317 
Class Lettering.
if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) if(! empty($conf->don->enabled) && $user->rights->societe->lire) if(! empty($conf->tax->enabled) && $user->rights->tax->charges->lire) if(! empty($conf->facture->enabled) &&! empty($conf->commande->enabled) && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1053
letteringThirdparty($socid)
letteringThirdparty
Class to manage Ledger (General Ledger and Subledger)
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
Class to manage third parties objects (customers, suppliers, prospects...)
updateLettering($ids=array(), $notrigger=false)
dol_now($mode='gmt')
Return date for now.