dolibarr  7.0.0-beta
lettering.class.php
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 Alexandre Spangaro <alexandre.spangaro@gmail.com>
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 2 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 <http://www.gnu.org/licenses/>.
18  */
19 
25 include_once DOL_DOCUMENT_ROOT . "/accountancy/class/bookkeeping.class.php";
26 include_once DOL_DOCUMENT_ROOT . "/societe/class/societe.class.php";
27 include_once DOL_DOCUMENT_ROOT . "/core/lib/date.lib.php";
28 
32 class lettering extends BookKeeping
33 {
40  public function lettrageTiers($socid)
41  {
42  $error = 0;
43 
44  $object = new Societe($this->db);
45  $object->id = $socid;
46  $object->fetch($socid);
47 
48  if ($object->code_compta == '411CUSTCODE') {
49  $object->code_compta = '';
50  }
51 
52  if ($object->code_compta_fournisseur == '401SUPPCODE') {
53  $object->code_compta_fournisseur = '';
54  }
55 
59  $sql = "SELECT DISTINCT bk.rowid, bk.doc_date, bk.doc_type, bk.doc_ref, bk.thirdparty_code, ";
60  $sql .= " bk.numero_compte , bk.label_compte, bk.debit , bk.credit, bk.montant ";
61  $sql .= " , bk.sens , bk.code_journal , bk.piece_num, bk.date_lettering, bu.url_id , bu.type ";
62  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk";
63  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "bank_url as bu ON(bk.fk_doc = bu.fk_bank AND bu.type IN ('payment', 'payment_supplier') ) ";
64  $sql .= " WHERE ( ";
65  if (! empty($object->code_compta))
66  $sql .= " bk.thirdparty_code = '" . $object->code_compta . "' ";
67  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur))
68  $sql .= " OR ";
69  if (! empty($object->code_compta_fournisseur))
70  $sql .= " bk.thirdparty_code = '" . $object->code_compta_fournisseur . "' ";
71 
72  $sql .= " ) AND (bk.date_lettering ='' OR bk.date_lettering IS NULL) ";
73  $sql .= " AND (bk.lettering_code != '' OR bk.lettering_code IS NULL) ";
74  $sql .= $this->db->order('bk.doc_date', 'DESC');
75 
76  // echo $sql;
77  //
78  $resql = $this->db->query($sql);
79  if ($resql) {
80  $num = $this->db->num_rows($resql);
81 
82  while ($obj = $this->db->fetch_object($resql) ) {
83  $ids = array();
84  $ids_fact = array();
85 
86  if ($obj->type == 'payment_supplier')
87  {
88  $sql = 'SELECT DISTINCT bk.rowid, facf.ref, facf.ref_supplier, payf.fk_bank, facf.rowid as fact_id';
89  $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn facf ";
90  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiementfourn_facturefourn as payfacf ON payfacf.fk_facturefourn=facf.rowid";
91  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiementfourn as payf ON payfacf.fk_paiementfourn=payf.rowid";
92  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON (bk.fk_doc = payf.fk_bank AND bk.code_journal='" . $obj->code_journal . "')";
93  $sql .= " WHERE payfacf.fk_paiementfourn = '" . $obj->url_id . "' ";
94  $sql .= " AND code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=4) ";
95  $sql .= " AND ( ";
96  if (! empty($object->code_compta)) {
97  $sql .= " bk.thirdparty_code = '" . $object->code_compta . "' ";
98  }
99  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
100  $sql .= " OR ";
101  }
102  if (! empty($object->code_compta_fournisseur)) {
103  $sql .= " bk.thirdparty_code = '" . $object->code_compta_fournisseur . "' ";
104  }
105  $sql .= " ) ";
106 
107  $resql2 = $this->db->query($sql);
108  if ($resql2) {
109  while ( $obj2 = $this->db->fetch_object($resql2) ) {
110  $ids[$obj2->rowid] = $obj2->rowid;
111  $ids_fact[] = $obj2->fact_id;
112  }
113  } else {
114  $this->errors[] = $this->db->lasterror;
115  return - 1;
116  }
117  if (count($ids_fact)) {
118  $sql = 'SELECT bk.rowid, facf.ref, facf.ref_supplier ';
119  $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn facf ";
120  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON( bk.fk_doc = facf.rowid AND facf.rowid IN (" . implode(',', $ids_fact) . "))";
121  $sql .= " WHERE bk.code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=3) ";
122  $sql .= " AND ( ";
123  if (! empty($object->code_compta)) {
124  $sql .= " bk.thirdparty_code = '" . $object->code_compta . "' ";
125  }
126  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
127  $sql .= " OR ";
128  }
129  if (! empty($object->code_compta_fournisseur)) {
130  $sql .= " bk.thirdparty_code = '" . $object->code_compta_fournisseur . "' ";
131  }
132  $sql .= " ) ";
133 
134  $resql2 = $this->db->query($sql);
135  if ($resql2) {
136  while ( $obj2 = $this->db->fetch_object($resql2) ) {
137  $ids[$obj2->rowid] = $obj2->rowid;
138  }
139  } else {
140  $this->errors[] = $this->db->lasterror;
141  return - 1;
142  }
143  }
144  } elseif ($obj->type == 'payment') {
145 
146  $sql = 'SELECT DISTINCT bk.rowid, fac.ref, fac.ref, pay.fk_bank, fac.rowid as fact_id';
147  $sql .= " FROM " . MAIN_DB_PREFIX . "facture fac ";
148  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiement_facture as payfac ON payfac.fk_facture=fac.rowid";
149  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "paiement as pay ON payfac.fk_paiement=pay.rowid";
150  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON (bk.fk_doc = pay.fk_bank AND bk.code_journal='" . $obj->code_journal . "')";
151  $sql .= " WHERE payfac.fk_paiement = '" . $obj->url_id . "' ";
152  $sql .= " AND bk.code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=4) ";
153  $sql .= " AND ( ";
154  if (! empty($object->code_compta)) {
155  $sql .= " bk.thirdparty_code = '" . $object->code_compta . "' ";
156  }
157  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
158  $sql .= " OR ";
159  }
160  if (! empty($object->code_compta_fournisseur)) {
161  $sql .= " bk.thirdparty_code = '" . $object->code_compta_fournisseur . "' ";
162  }
163  $sql .= " ) ";
164 
165  $resql2 = $this->db->query($sql);
166  if ($resql2) {
167  while ( $obj2 = $this->db->fetch_object($resql2) ) {
168  $ids[$obj2->rowid] = $obj2->rowid;
169  $ids_fact[] = $obj2->fact_id;
170  }
171  } else {
172  $this->errors[] = $this->db->lasterror;
173  return - 1;
174  }
175  if (count($ids_fact)) {
176  $sql = 'SELECT bk.rowid, fac.ref, fac.ref_supplier ';
177  $sql .= " FROM " . MAIN_DB_PREFIX . "facture fac ";
178  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk ON( bk.fk_doc = fac.rowid AND fac.rowid IN (" . implode(',', $ids_fact) . "))";
179  $sql .= " WHERE code_journal IN (SELECT code FROM " . MAIN_DB_PREFIX . "accounting_journal WHERE nature=2) ";
180  $sql .= " AND ( ";
181  if (! empty($object->code_compta)) {
182  $sql .= " bk.thirdparty_code = '" . $object->code_compta . "' ";
183  }
184  if (! empty($object->code_compta) && ! empty($object->code_compta_fournisseur)) {
185  $sql .= " OR ";
186  }
187  if (! empty($object->code_compta_fournisseur)) {
188  $sql .= " bk.thirdparty_code = '" . $object->code_compta_fournisseur . "' ";
189  }
190  $sql .= " ) ";
191 
192  $resql2 = $this->db->query($sql);
193  if ($resql2) {
194  while ( $obj2 = $this->db->fetch_object($resql2) ) {
195  $ids[$obj2->rowid] = $obj2->rowid;
196  }
197  } else {
198  $this->errors[] = $this->db->lasterror;
199  return - 1;
200  }
201  }
202  }
203 
204  if (count($ids) > 1) {
205  $result = $this->updatelettrage($ids);
206  }
207  }
208  }
209  if ($error) {
210  foreach ( $this->errors as $errmsg ) {
211  dol_syslog(get_class($this) . "::" . __METHOD__ . $errmsg, LOG_ERR);
212  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
213  }
214  return - 1 * $error;
215  } else {
216  return 1;
217  }
218  }
219 
226  public function updateLettrage($ids = array(), $notrigger = false)
227  {
228  $error = 0;
229  $lettre = 'AAA';
230 
231  dol_syslog(get_class($this) . "::" . __METHOD__, LOG_DEBUG);
232 
233  $sql = "SELECT DISTINCT lettering_code FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE ";
234  $sql .= " lettering_code != '' ORDER BY lettering_code DESC limit 1; ";
235 
236  $result = $this->db->query($sql);
237  if ($result) {
238  $obj = $this->db->fetch_object($result);
239  $lettre = (empty($obj->lettering_code) ? 'AAA' : $obj->lettering_code);
240  if (! empty($obj->lettering_code))
241  $lettre++;
242  } else {
243  $this->errors[] = 'Error' . $this->db->lasterror();
244  $error++;
245  }
246 
247  $sql = "SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE ";
248  $sql .= " rowid IN (" . implode(',', $ids) . ") ";
249  $result = $this->db->query($sql);
250  if ($result) {
251  $obj = $this->db->fetch_object($result);
252  if (! (round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
253  $this->errors[] = 'Total not exacts ' . round(abs($obj->deb), 2) . ' vs ' . round(abs($obj->cred), 2);
254  $error++;
255  }
256  } else {
257  $this->errors[] = 'Erreur sql' . $this->db->lasterror();
258  $error++;
259  }
260 
261  // Update request
262 
263  $now = dol_now();
264 
265  if (! $error)
266  {
267  $sql = "UPDATE " . MAIN_DB_PREFIX . "accounting_bookkeeping SET";
268  $sql .= " lettering_code='" . $lettre . "'";
269  $sql .= " , date_lettering = '" . $this->db->idate($now) . "'"; // todo correct date it's false
270  $sql .= " WHERE rowid IN (" . implode(',', $ids) . ") ";
271  $this->db->begin();
272 
273  dol_syslog(get_class($this) . "::update sql=" . $sql, LOG_DEBUG);
274  $resql = $this->db->query($sql);
275  if (! $resql) {
276  $error++;
277  $this->errors[] = "Error " . $this->db->lasterror();
278  }
279  }
280 
281  if (! $error) {
282  if (! $notrigger) {
283  // Uncomment this and change MYOBJECT to your own tag if you
284  // want this action calls a trigger.
285 
286  // // Call triggers
287  // include_once DOL_DOCUMENT_ROOT . '/core/class/interfaces.class.php';
288  // $interface=new Interfaces($this->db);
289  // $result=$interface->run_triggers('MYOBJECT_MODIFY',$this,$user,$langs,$conf);
290  // if ($result < 0) { $error++; $this->errors=$interface->errors; }
291  // // End call triggers
292  }
293  }
294  // Commit or rollback
295  if ($error) {
296  foreach ( $this->errors as $errmsg ) {
297  dol_syslog(get_class($this) . "::update " . $errmsg, LOG_ERR);
298  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
299  }
300  $this->db->rollback();
301  return - 1 * $error;
302  } else {
303  $this->db->commit();
304  return 1;
305  }
306  }
307 }
308 
lettrageTiers($socid)
lettrageTiers
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...)
Class lettering.
dol_now($mode='gmt')
Return date for now.
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:1013
updateLettrage($ids=array(), $notrigger=false)