28 require_once DOL_DOCUMENT_ROOT.
'/core/class/commonobject.class.php';
38 public $element =
'accountingbookkeeping';
43 public $table_element =
'accounting_bookkeeping';
53 public $lines = array();
68 public $date_lim_reglement;
93 public $thirdparty_code;
98 public $subledger_account;
103 public $subledger_label;
108 public $numero_compte;
113 public $label_compte;
118 public $label_operation;
150 public $fk_user_author;
160 public $code_journal;
165 public $journal_label;
175 public $date_validation;
185 public $picto =
'generic';
207 global $conf, $langs;
214 if (isset($this->doc_type)) {
215 $this->doc_type = trim($this->doc_type);
217 if (isset($this->doc_ref)) {
218 $this->doc_ref = trim($this->doc_ref);
220 if (isset($this->fk_doc)) {
221 $this->fk_doc = (int) $this->fk_doc;
223 if (isset($this->fk_docdet)) {
224 $this->fk_docdet = (int) $this->fk_docdet;
226 if (isset($this->thirdparty_code)) {
227 $this->thirdparty_code = trim($this->thirdparty_code);
229 if (isset($this->subledger_account)) {
230 $this->subledger_account = trim($this->subledger_account);
232 if (isset($this->subledger_label)) {
233 $this->subledger_label = trim($this->subledger_label);
235 if (isset($this->numero_compte)) {
236 $this->numero_compte = trim($this->numero_compte);
238 if (isset($this->label_compte)) {
239 $this->label_compte = trim($this->label_compte);
241 if (isset($this->label_operation)) {
242 $this->label_operation = trim($this->label_operation);
244 if (isset($this->debit)) {
245 $this->debit = (
float) $this->debit;
247 if (isset($this->credit)) {
248 $this->credit = (
float) $this->credit;
250 if (isset($this->montant)) {
251 $this->montant = (
float) $this->montant;
253 if (isset($this->amount)) {
254 $this->amount = (
float) $this->amount;
256 if (isset($this->sens)) {
257 $this->sens = trim($this->sens);
259 if (isset($this->import_key)) {
260 $this->import_key = trim($this->import_key);
262 if (isset($this->code_journal)) {
263 $this->code_journal = trim($this->code_journal);
265 if (isset($this->journal_label)) {
266 $this->journal_label = trim($this->journal_label);
268 if (isset($this->piece_num)) {
269 $this->piece_num = trim($this->piece_num);
271 if (empty($this->debit)) {
274 if (empty($this->credit)) {
279 if (($this->numero_compte ==
"") || $this->numero_compte ==
'-1' || $this->numero_compte ==
'NotDefined') {
280 $langs->loadLangs(array(
"errors"));
281 if (in_array($this->doc_type, array(
'bank',
'expense_report'))) {
282 $this->errors[] = $langs->trans(
'ErrorFieldAccountNotDefinedForBankLine', $this->fk_docdet, $this->doc_type);
285 $mesg = $this->doc_ref.
', '.$langs->trans(
"AccountAccounting").
': '.$this->numero_compte;
286 if ($this->subledger_account && $this->subledger_account != $this->numero_compte) {
287 $mesg .=
', '.$langs->trans(
"SubledgerAccount").
': '.$this->subledger_account;
289 $this->errors[] = $langs->trans(
'ErrorFieldAccountNotDefinedForLine', $mesg);
297 $this->piece_num = 0;
304 $sql =
"SELECT count(*) as nb";
305 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
306 $sql .=
" WHERE doc_type = '".$this->db->escape($this->doc_type).
"'";
307 $sql .=
" AND fk_doc = ".((int) $this->fk_doc);
308 if (!empty($conf->global->ACCOUNTANCY_ENABLE_FKDOCDET)) {
310 $sql .=
" AND fk_docdet = ".((int) $this->fk_docdet);
312 $sql .=
" AND numero_compte = '".$this->db->escape($this->numero_compte).
"'";
313 $sql .=
" AND label_operation = '".$this->db->escape($this->label_operation).
"'";
314 $sql .=
" AND entity = ".$conf->entity;
316 $resql = $this->db->query(
$sql);
319 $row = $this->db->fetch_object($resql);
322 $sqlnum =
"SELECT piece_num";
323 $sqlnum .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
324 $sqlnum .=
" WHERE doc_type = '".$this->db->escape($this->doc_type).
"'";
325 $sqlnum .=
" AND fk_doc = ".((int) $this->fk_doc);
326 if (!empty($conf->global->ACCOUNTANCY_ENABLE_FKDOCDET)) {
328 $sqlnum .=
" AND fk_docdet = ".((int) $this->fk_docdet);
330 $sqlnum .=
" AND doc_ref = '".$this->db->escape($this->doc_ref).
"'";
331 $sqlnum .=
" AND entity = ".$conf->entity;
333 dol_syslog(get_class($this).
":: create sqlnum=".$sqlnum, LOG_DEBUG);
334 $resqlnum = $this->db->query($sqlnum);
336 $objnum = $this->db->fetch_object($resqlnum);
337 $this->piece_num = $objnum->piece_num;
340 dol_syslog(get_class($this).
":: create this->piece_num=".$this->piece_num, LOG_DEBUG);
341 if (empty($this->piece_num)) {
342 $sqlnum =
"SELECT MAX(piece_num)+1 as maxpiecenum";
343 $sqlnum .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
344 $sqlnum .=
" WHERE entity = " . ((int) $conf->entity);
346 $resqlnum = $this->db->query($sqlnum);
348 $objnum = $this->db->fetch_object($resqlnum);
349 $this->piece_num = $objnum->maxpiecenum;
351 dol_syslog(get_class($this).
":: create now this->piece_num=".$this->piece_num, LOG_DEBUG);
353 if (empty($this->piece_num)) {
354 $this->piece_num = 1;
359 $sql =
"INSERT INTO ".MAIN_DB_PREFIX.$this->table_element.
" (";
361 $sql .=
", date_lim_reglement";
362 $sql .=
", doc_type";
365 $sql .=
", fk_docdet";
366 $sql .=
", thirdparty_code";
367 $sql .=
", subledger_account";
368 $sql .=
", subledger_label";
369 $sql .=
", numero_compte";
370 $sql .=
", label_compte";
371 $sql .=
", label_operation";
376 $sql .=
", fk_user_author";
377 $sql .=
", date_creation";
378 $sql .=
", code_journal";
379 $sql .=
", journal_label";
380 $sql .=
", piece_num";
382 $sql .=
") VALUES (";
383 $sql .=
"'".$this->db->idate($this->doc_date).
"'";
384 $sql .=
", ".(!isset($this->date_lim_reglement) ||
dol_strlen($this->date_lim_reglement) == 0 ?
'NULL' :
"'".$this->db->idate($this->date_lim_reglement).
"'");
385 $sql .=
", '".$this->db->escape($this->doc_type).
"'";
386 $sql .=
", '".$this->db->escape($this->doc_ref).
"'";
387 $sql .=
", ".((int) $this->fk_doc);
388 $sql .=
", ".((int) $this->fk_docdet);
389 $sql .=
", ".(!empty($this->thirdparty_code) ? (
"'".$this->db->escape($this->thirdparty_code).
"'") :
"NULL");
390 $sql .=
", ".(!empty($this->subledger_account) ? (
"'".$this->db->escape($this->subledger_account).
"'") :
"NULL");
391 $sql .=
", ".(!empty($this->subledger_label) ? (
"'".$this->db->escape($this->subledger_label).
"'") :
"NULL");
392 $sql .=
", '".$this->db->escape($this->numero_compte).
"'";
393 $sql .=
", ".(!empty($this->label_compte) ? (
"'".$this->db->escape($this->label_compte).
"'") :
"NULL");
394 $sql .=
", '".$this->db->escape($this->label_operation).
"'";
395 $sql .=
", ".((float) $this->debit);
396 $sql .=
", ".((float) $this->credit);
397 $sql .=
", ".((float) $this->montant);
398 $sql .=
", ".(!empty($this->sens) ? (
"'".$this->db->escape($this->sens).
"'") :
"NULL");
399 $sql .=
", '".$this->db->escape($this->fk_user_author).
"'";
400 $sql .=
", '".$this->db->idate($now).
"'";
401 $sql .=
", '".$this->db->escape($this->code_journal).
"'";
402 $sql .=
", ".(!empty($this->journal_label) ? (
"'".$this->db->escape($this->journal_label).
"'") :
"NULL");
403 $sql .=
", ".((int) $this->piece_num);
404 $sql .=
", ".(!isset($this->entity) ? $conf->entity : $this->entity);
407 $resql = $this->db->query(
$sql);
409 $id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
417 $this->errors[] =
'Error Create Error '.$result.
' lecture ID';
418 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
423 $this->errors[] =
'Error '.$this->db->lasterror();
424 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
429 $this->error =
'BookkeepingRecordAlreadyExists';
430 dol_syslog(__METHOD__.
' '.$this->error, LOG_WARNING);
435 $this->errors[] =
'Error '.$this->db->lasterror();
436 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
451 $this->db->rollback();
469 public function getNomUrl($withpicto = 0, $option =
'', $notooltip = 0, $morecss =
'', $save_lastsearch_value = -1)
471 global $db, $conf, $langs;
472 global $dolibarr_main_authentication, $dolibarr_main_demo;
473 global $menumanager, $hookmanager;
475 if (!empty($conf->dol_no_mouse_hover)) {
482 $label =
'<u>'.$langs->trans(
"Transaction").
'</u>';
484 $label .=
'<b>'.$langs->trans(
'Ref').
':</b> '.$this->piece_num;
486 $url = DOL_URL_ROOT.
'/accountancy/bookkeeping/card.php?piece_num='.$this->piece_num;
488 if ($option !=
'nolink') {
490 $add_save_lastsearch_values = ($save_lastsearch_value == 1 ? 1 : 0);
491 if ($save_lastsearch_value == -1 && preg_match(
'/list\.php/', $_SERVER[
"PHP_SELF"])) {
492 $add_save_lastsearch_values = 1;
494 if ($add_save_lastsearch_values) {
495 $url .=
'&save_lastsearch_values=1';
500 if (empty($notooltip)) {
501 if (!empty($conf->global->MAIN_OPTIMIZEFORTEXTBROWSER)) {
502 $label = $langs->trans(
"ShowTransaction");
503 $linkclose .=
' alt="'.dol_escape_htmltag($label, 1).
'"';
505 $linkclose .=
' title="'.dol_escape_htmltag($label, 1).
'"';
506 $linkclose .=
' class="classfortooltip'.($morecss ?
' '.$morecss :
'').
'"';
508 $linkclose = ($morecss ?
' class="'.$morecss.
'"' :
'');
511 $linkstart =
'<a href="'.$url.
'"';
512 $linkstart .= $linkclose.
'>';
515 $result .= $linkstart;
517 $result .=
img_object(($notooltip ?
'' : $label), ($this->picto ? $this->picto :
'generic'), ($notooltip ? (($withpicto != 2) ?
'class="paddingright"' :
'') :
'class="'.(($withpicto != 2) ?
'paddingright ' :
'').
'classfortooltip"'), 0, 0, $notooltip ? 0 : 1);
519 if ($withpicto != 2) {
520 $result .= $this->piece_num;
526 $hookmanager->initHooks(array($this->element .
'dao'));
527 $parameters = array(
'id'=>$this->
id,
'getnomurl' => &$result);
528 $reshook = $hookmanager->executeHooks(
'getNomUrl', $parameters, $this, $action);
530 $result = $hookmanager->resPrint;
532 $result .= $hookmanager->resPrint;
547 global $conf, $langs;
549 $langs->loadLangs(array(
"accountancy",
"bills",
"compta"));
556 if (isset($this->doc_type)) {
557 $this->doc_type = trim($this->doc_type);
559 if (isset($this->doc_ref)) {
560 $this->doc_ref = trim($this->doc_ref);
562 if (isset($this->fk_doc)) {
563 $this->fk_doc = (int) $this->fk_doc;
565 if (isset($this->fk_docdet)) {
566 $this->fk_docdet = (int) $this->fk_docdet;
568 if (isset($this->thirdparty_code)) {
569 $this->thirdparty_code = trim($this->thirdparty_code);
571 if (isset($this->subledger_account)) {
572 $this->subledger_account = trim($this->subledger_account);
574 if (isset($this->subledger_label)) {
575 $this->subledger_label = trim($this->subledger_label);
577 if (isset($this->numero_compte)) {
578 $this->numero_compte = trim($this->numero_compte);
580 if (isset($this->label_compte)) {
581 $this->label_compte = trim($this->label_compte);
583 if (isset($this->label_operation)) {
584 $this->label_operation = trim($this->label_operation);
586 if (isset($this->debit)) {
587 $this->debit = trim($this->debit);
589 if (isset($this->credit)) {
590 $this->credit = trim($this->credit);
592 if (isset($this->montant)) {
593 $this->montant = trim($this->montant);
595 if (isset($this->amount)) {
596 $this->amount = trim($this->amount);
598 if (isset($this->sens)) {
599 $this->sens = trim($this->sens);
601 if (isset($this->import_key)) {
602 $this->import_key = trim($this->import_key);
604 if (isset($this->code_journal)) {
605 $this->code_journal = trim($this->code_journal);
607 if (isset($this->journal_label)) {
608 $this->journal_label = trim($this->journal_label);
610 if (isset($this->piece_num)) {
611 $this->piece_num = trim($this->piece_num);
613 if (empty($this->debit)) {
616 if (empty($this->credit)) {
619 if (empty($this->montant)) {
623 $this->debit =
price2num($this->debit,
'MT');
624 $this->credit =
price2num($this->credit,
'MT');
625 $this->montant =
price2num($this->montant,
'MT');
630 $this->journal_label = $langs->trans($this->journal_label);
633 $sql =
'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.$mode.
' (';
635 $sql .=
'date_lim_reglement,';
639 $sql .=
'fk_docdet,';
640 $sql .=
'thirdparty_code,';
641 $sql .=
'subledger_account,';
642 $sql .=
'subledger_label,';
643 $sql .=
'numero_compte,';
644 $sql .=
'label_compte,';
645 $sql .=
'label_operation,';
650 $sql .=
'fk_user_author,';
651 $sql .=
'date_creation,';
652 $sql .=
'code_journal,';
653 $sql .=
'journal_label,';
654 $sql .=
'piece_num,';
656 $sql .=
') VALUES (';
657 $sql .=
' '.(!isset($this->doc_date) ||
dol_strlen($this->doc_date) == 0 ?
'NULL' :
"'".$this->db->idate($this->doc_date).
"'").
',';
658 $sql .=
' '.(!isset($this->date_lim_reglement) ||
dol_strlen($this->date_lim_reglement) == 0 ?
'NULL' :
"'".$this->db->idate($this->date_lim_reglement).
"'").
',';
659 $sql .=
' '.(!isset($this->doc_type) ?
'NULL' :
"'".$this->db->escape($this->doc_type).
"'").
',';
660 $sql .=
' '.(!isset($this->doc_ref) ?
'NULL' :
"'".$this->db->escape($this->doc_ref).
"'").
',';
661 $sql .=
' '.(empty($this->fk_doc) ?
'0' : (int) $this->fk_doc).
',';
662 $sql .=
' '.(empty($this->fk_docdet) ?
'0' : (int) $this->fk_docdet).
',';
663 $sql .=
' '.(!isset($this->thirdparty_code) ?
'NULL' :
"'".$this->db->escape($this->thirdparty_code).
"'").
',';
664 $sql .=
' '.(!isset($this->subledger_account) ?
'NULL' :
"'".$this->db->escape($this->subledger_account).
"'").
',';
665 $sql .=
' '.(!isset($this->subledger_label) ?
'NULL' :
"'".$this->db->escape($this->subledger_label).
"'").
',';
666 $sql .=
' '.(!isset($this->numero_compte) ?
'NULL' :
"'".$this->db->escape($this->numero_compte).
"'").
',';
667 $sql .=
' '.(!isset($this->label_compte) ?
'NULL' :
"'".$this->db->escape($this->label_compte).
"'").
',';
668 $sql .=
' '.(!isset($this->label_operation) ?
'NULL' :
"'".$this->db->escape($this->label_operation).
"'").
',';
669 $sql .=
' '.(!isset($this->debit) ?
'NULL' : $this->debit).
',';
670 $sql .=
' '.(!isset($this->credit) ?
'NULL' : $this->credit).
',';
671 $sql .=
' '.(!isset($this->montant) ?
'NULL' : $this->montant).
',';
672 $sql .=
' '.(!isset($this->sens) ?
'NULL' :
"'".$this->db->escape($this->sens).
"'").
',';
673 $sql .=
' '.((int) $user->id).
',';
674 $sql .=
' '.
"'".$this->db->idate($now).
"',";
675 $sql .=
' '.(empty($this->code_journal) ?
'NULL' :
"'".$this->db->escape($this->code_journal).
"'").
',';
676 $sql .=
' '.(empty($this->journal_label) ?
'NULL' :
"'".$this->db->escape($this->journal_label).
"'").
',';
677 $sql .=
' '.(empty($this->piece_num) ?
'NULL' : $this->db->escape($this->piece_num)).
',';
678 $sql .=
' '.(!isset($this->entity) ? $conf->entity : $this->entity);
683 $resql = $this->db->query(
$sql);
686 $this->errors[] =
'Error '.$this->db->lasterror();
687 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
691 $this->
id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element.$mode);
706 $this->db->rollback();
725 public function fetch($id, $ref =
null, $mode =
'')
733 $sql .=
" t.doc_date,";
734 $sql .=
" t.date_lim_reglement,";
735 $sql .=
" t.doc_type,";
736 $sql .=
" t.doc_ref,";
737 $sql .=
" t.fk_doc,";
738 $sql .=
" t.fk_docdet,";
739 $sql .=
" t.thirdparty_code,";
740 $sql .=
" t.subledger_account,";
741 $sql .=
" t.subledger_label,";
742 $sql .=
" t.numero_compte,";
743 $sql .=
" t.label_compte,";
744 $sql .=
" t.label_operation,";
746 $sql .=
" t.credit,";
747 $sql .=
" t.montant as amount,";
749 $sql .=
" t.fk_user_author,";
750 $sql .=
" t.import_key,";
751 $sql .=
" t.code_journal,";
752 $sql .=
" t.journal_label,";
753 $sql .=
" t.piece_num,";
754 $sql .=
" t.date_creation,";
756 if ($mode !=
"_tmp") {
757 $sql .=
" t.date_export,";
759 $sql .=
" t.date_validated as date_validation";
760 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.$mode.
' as t';
761 $sql .=
' WHERE 1 = 1';
762 $sql .=
" AND entity = " . ((int) $conf->entity);
764 $sql .=
" AND t.ref = '".$this->db->escape($ref).
"'";
766 $sql .=
' AND t.rowid = '.((int) $id);
769 $resql = $this->db->query(
$sql);
771 $numrows = $this->db->num_rows($resql);
773 $obj = $this->db->fetch_object($resql);
775 $this->
id = $obj->rowid;
777 $this->doc_date = $this->db->jdate($obj->doc_date);
778 $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
779 $this->doc_type = $obj->doc_type;
780 $this->doc_ref = $obj->doc_ref;
781 $this->fk_doc = $obj->fk_doc;
782 $this->fk_docdet = $obj->fk_docdet;
783 $this->thirdparty_code = $obj->thirdparty_code;
784 $this->subledger_account = $obj->subledger_account;
785 $this->subledger_label = $obj->subledger_label;
786 $this->numero_compte = $obj->numero_compte;
787 $this->label_compte = $obj->label_compte;
788 $this->label_operation = $obj->label_operation;
789 $this->debit = $obj->debit;
790 $this->credit = $obj->credit;
791 $this->montant = $obj->amount;
792 $this->amount = $obj->amount;
793 $this->sens = $obj->sens;
794 $this->fk_user_author = $obj->fk_user_author;
795 $this->import_key = $obj->import_key;
796 $this->code_journal = $obj->code_journal;
797 $this->journal_label = $obj->journal_label;
798 $this->piece_num = $obj->piece_num;
799 $this->date_creation = $this->db->jdate($obj->date_creation);
800 $this->date_export = $this->db->jdate($obj->date_export);
801 $this->date_validation = isset($obj->date_validated) ? $this->db->jdate($obj->date_validated) :
'';
803 $this->db->free($resql);
811 $this->errors[] =
'Error '.$this->db->lasterror();
812 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
832 public function fetchAllByAccount($sortorder =
'', $sortfield =
'', $limit = 0, $offset = 0, array $filter = array(), $filtermode =
'AND', $option = 0, $countonly = 0)
838 $this->lines = array();
843 $sql .=
' COUNT(t.rowid) as nb';
846 $sql .=
" t.doc_date,";
847 $sql .=
" t.doc_type,";
848 $sql .=
" t.doc_ref,";
849 $sql .=
" t.fk_doc,";
850 $sql .=
" t.fk_docdet,";
851 $sql .=
" t.thirdparty_code,";
852 $sql .=
" t.subledger_account,";
853 $sql .=
" t.subledger_label,";
854 $sql .=
" t.numero_compte,";
855 $sql .=
" t.label_compte,";
856 $sql .=
" t.label_operation,";
858 $sql .=
" t.credit,";
859 $sql .=
" t.montant as amount,";
861 $sql .=
" t.multicurrency_amount,";
862 $sql .=
" t.multicurrency_code,";
863 $sql .=
" t.lettering_code,";
864 $sql .=
" t.date_lettering,";
865 $sql .=
" t.fk_user_author,";
866 $sql .=
" t.import_key,";
867 $sql .=
" t.code_journal,";
868 $sql .=
" t.journal_label,";
869 $sql .=
" t.piece_num,";
870 $sql .=
" t.date_creation,";
871 $sql .=
" t.date_export,";
872 $sql .=
" t.date_validated as date_validation,";
873 $sql .=
" t.import_key";
877 if (count($filter) > 0) {
878 foreach ($filter as $key => $value) {
879 if ($key ==
't.doc_date') {
880 $sqlwhere[] = $key.
'=\''.$this->db->idate($value).
'\'';
881 } elseif ($key ==
't.doc_date>=' || $key ==
't.doc_date<=') {
882 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
883 } elseif ($key ==
't.numero_compte>=' || $key ==
't.numero_compte<=' || $key ==
't.subledger_account>=' || $key ==
't.subledger_account<=') {
884 $sqlwhere[] = $key.
'\''.$this->db->escape($value).
'\'';
885 } elseif ($key ==
't.fk_doc' || $key ==
't.fk_docdet' || $key ==
't.piece_num') {
886 $sqlwhere[] = $key.
'='.$value;
887 } elseif ($key ==
't.subledger_account' || $key ==
't.numero_compte') {
888 $sqlwhere[] = $key.
' LIKE \''.$this->db->escapeforlike($this->db->escape($value)).
'%\'';
889 } elseif ($key ==
't.date_creation>=' || $key ==
't.date_creation<=') {
890 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
891 } elseif ($key ==
't.date_export>=' || $key ==
't.date_export<=') {
892 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
893 } elseif ($key ==
't.date_validated>=' || $key ==
't.date_validated<=') {
894 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
895 } elseif ($key ==
't.credit' || $key ==
't.debit') {
897 } elseif ($key ==
't.reconciled_option') {
898 $sqlwhere[] =
't.lettering_code IS NULL';
899 } elseif ($key ==
't.code_journal' && !empty($value)) {
900 if (is_array($value)) {
901 $sqlwhere[] =
natural_search(
"t.code_journal", join(
',', $value), 3, 1);
905 } elseif ($key ==
't.search_accounting_code_in' && !empty($value)) {
906 $sqlwhere[] =
't.numero_compte IN ('.$this->db->sanitize($value, 1).
')';
912 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.
' as t';
913 $sql .=
' WHERE entity = ' . ((int) $conf->entity);
914 if (count($sqlwhere) > 0) {
915 $sql .=
" AND ".implode(
" ".$filtermode.
" ", $sqlwhere);
918 if (!empty($option)) {
919 $sql .=
" AND t.subledger_account IS NOT NULL";
920 $sql .=
" AND t.subledger_account <> ''";
921 $sortfield =
't.subledger_account'.($sortfield ?
','.$sortfield :
'');
922 $sortorder =
'ASC'.($sortfield ?
','.$sortfield :
'');
924 $sortfield =
't.numero_compte'.($sortfield ?
','.$sortfield :
'');
925 $sortorder =
'ASC'.($sortorder ?
','.$sortorder :
'');
929 $sql .= $this->db->order($sortfield, $sortorder);
930 if (!empty($limit)) {
931 $sql .= $this->db->plimit($limit + 1, $offset);
935 $resql = $this->db->query(
$sql);
938 $obj = $this->db->fetch_object($resql);
943 $num = $this->db->num_rows($resql);
946 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
949 $line->id = $obj->rowid;
951 $line->doc_date = $this->db->jdate($obj->doc_date);
952 $line->doc_type = $obj->doc_type;
953 $line->doc_ref = $obj->doc_ref;
954 $line->fk_doc = $obj->fk_doc;
955 $line->fk_docdet = $obj->fk_docdet;
956 $line->thirdparty_code = $obj->thirdparty_code;
957 $line->subledger_account = $obj->subledger_account;
958 $line->subledger_label = $obj->subledger_label;
959 $line->numero_compte = $obj->numero_compte;
960 $line->label_compte = $obj->label_compte;
961 $line->label_operation = $obj->label_operation;
962 $line->debit = $obj->debit;
963 $line->credit = $obj->credit;
964 $line->montant = $obj->amount;
965 $line->amount = $obj->amount;
966 $line->sens = $obj->sens;
967 $line->multicurrency_amount = $obj->multicurrency_amount;
968 $line->multicurrency_code = $obj->multicurrency_code;
969 $line->lettering_code = $obj->lettering_code;
970 $line->date_lettering = $obj->date_lettering;
971 $line->fk_user_author = $obj->fk_user_author;
972 $line->import_key = $obj->import_key;
973 $line->code_journal = $obj->code_journal;
974 $line->journal_label = $obj->journal_label;
975 $line->piece_num = $obj->piece_num;
976 $line->date_creation = $this->db->jdate($obj->date_creation);
977 $line->date_export = $this->db->jdate($obj->date_export);
978 $line->date_validation = $this->db->jdate($obj->date_validation);
979 $line->import_key = $obj->import_key;
981 $this->lines[] = $line;
986 $this->db->free($resql);
990 $this->errors[] =
'Error '.$this->db->lasterror();
991 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1009 public function fetchAll($sortorder =
'', $sortfield =
'', $limit = 0, $offset = 0, array $filter = array(), $filtermode =
'AND', $showAlreadyExportMovements = 1)
1016 $sql .=
' t.rowid,';
1017 $sql .=
" t.doc_date,";
1018 $sql .=
" t.doc_type,";
1019 $sql .=
" t.doc_ref,";
1020 $sql .=
" t.fk_doc,";
1021 $sql .=
" t.fk_docdet,";
1022 $sql .=
" t.thirdparty_code,";
1023 $sql .=
" t.subledger_account,";
1024 $sql .=
" t.subledger_label,";
1025 $sql .=
" t.numero_compte,";
1026 $sql .=
" t.label_compte,";
1027 $sql .=
" t.label_operation,";
1028 $sql .=
" t.debit,";
1029 $sql .=
" t.credit,";
1030 $sql .=
" t.lettering_code,";
1031 $sql .=
" t.date_lettering,";
1032 $sql .=
" t.montant as amount,";
1034 $sql .=
" t.fk_user_author,";
1035 $sql .=
" t.import_key,";
1036 $sql .=
" t.code_journal,";
1037 $sql .=
" t.journal_label,";
1038 $sql .=
" t.piece_num,";
1039 $sql .=
" t.date_creation,";
1040 $sql .=
" t.date_lim_reglement,";
1041 $sql .=
" t.tms as date_modification,";
1042 $sql .=
" t.date_export,";
1043 $sql .=
" t.date_validated as date_validation";
1044 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.
' as t';
1046 $sqlwhere = array();
1047 if (count($filter) > 0) {
1048 foreach ($filter as $key => $value) {
1049 if ($key ==
't.doc_date') {
1050 $sqlwhere[] = $key.
'=\''.$this->db->idate($value).
'\'';
1051 } elseif ($key ==
't.doc_date>=' || $key ==
't.doc_date<=') {
1052 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
1053 } elseif ($key ==
't.numero_compte>=' || $key ==
't.numero_compte<=' || $key ==
't.subledger_account>=' || $key ==
't.subledger_account<=') {
1054 $sqlwhere[] = $key.
'\''.$this->db->escape($value).
'\'';
1055 } elseif ($key ==
't.fk_doc' || $key ==
't.fk_docdet' || $key ==
't.piece_num') {
1056 $sqlwhere[] = $key.
'='.((int) $value);
1057 } elseif ($key ==
't.subledger_account' || $key ==
't.numero_compte') {
1058 $sqlwhere[] = $key.
' LIKE \''.$this->db->escape($value).
'%\'';
1059 } elseif ($key ==
't.date_creation>=' || $key ==
't.date_creation<=') {
1060 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
1061 } elseif ($key ==
't.tms>=' || $key ==
't.tms<=') {
1062 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
1063 } elseif ($key ==
't.date_export>=' || $key ==
't.date_export<=') {
1064 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
1065 } elseif ($key ==
't.date_validated>=' || $key ==
't.date_validated<=') {
1066 $sqlwhere[] = $key.
'\''.$this->db->idate($value).
'\'';
1067 } elseif ($key ==
't.credit' || $key ==
't.debit') {
1069 } elseif ($key ==
't.code_journal' && !empty($value)) {
1070 if (is_array($value)) {
1071 $sqlwhere[] =
natural_search(
"t.code_journal", join(
',', $value), 3, 1);
1080 $sql .=
' WHERE t.entity = ' . ((int) $conf->entity);
1081 if ($showAlreadyExportMovements == 0) {
1082 $sql .=
" AND t.date_export IS NULL";
1084 if (count($sqlwhere) > 0) {
1085 $sql .=
' AND '.implode(
" ".$filtermode.
" ", $sqlwhere);
1087 if (!empty($sortfield)) {
1088 $sql .= $this->db->order($sortfield, $sortorder);
1090 if (!empty($limit)) {
1091 $sql .= $this->db->plimit($limit + 1, $offset);
1093 $this->lines = array();
1095 $resql = $this->db->query(
$sql);
1097 $num = $this->db->num_rows($resql);
1100 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1103 $line->id = $obj->rowid;
1105 $line->doc_date = $this->db->jdate($obj->doc_date);
1106 $line->doc_type = $obj->doc_type;
1107 $line->doc_ref = $obj->doc_ref;
1108 $line->fk_doc = $obj->fk_doc;
1109 $line->fk_docdet = $obj->fk_docdet;
1110 $line->thirdparty_code = $obj->thirdparty_code;
1111 $line->subledger_account = $obj->subledger_account;
1112 $line->subledger_label = $obj->subledger_label;
1113 $line->numero_compte = $obj->numero_compte;
1114 $line->label_compte = $obj->label_compte;
1115 $line->label_operation = $obj->label_operation;
1116 $line->debit = $obj->debit;
1117 $line->credit = $obj->credit;
1118 $line->montant = $obj->amount;
1119 $line->amount = $obj->amount;
1120 $line->sens = $obj->sens;
1121 $line->lettering_code = $obj->lettering_code;
1122 $line->date_lettering = $obj->date_lettering;
1123 $line->fk_user_author = $obj->fk_user_author;
1124 $line->import_key = $obj->import_key;
1125 $line->code_journal = $obj->code_journal;
1126 $line->journal_label = $obj->journal_label;
1127 $line->piece_num = $obj->piece_num;
1128 $line->date_creation = $this->db->jdate($obj->date_creation);
1129 $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1130 $line->date_modification = $this->db->jdate($obj->date_modification);
1131 $line->date_export = $this->db->jdate($obj->date_export);
1132 $line->date_validation = $this->db->jdate($obj->date_validation);
1134 $this->lines[] = $line;
1138 $this->db->free($resql);
1142 $this->errors[] =
'Error '.$this->db->lasterror();
1143 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1160 public function fetchAllBalance($sortorder =
'', $sortfield =
'', $limit = 0, $offset = 0, array $filter = array(), $filtermode =
'AND', $option = 0)
1164 $this->lines = array();
1169 $sql .=
" t.numero_compte,";
1170 $sql .=
" t.label_compte,";
1171 if (!empty($option)) {
1172 $sql .=
" t.subledger_account,";
1173 $sql .=
" t.subledger_label,";
1175 $sql .=
" SUM(t.debit) as debit,";
1176 $sql .=
" SUM(t.credit) as credit";
1177 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.
' as t';
1179 $sqlwhere = array();
1180 if (count($filter) > 0) {
1181 foreach ($filter as $key => $value) {
1182 if ($key ==
't.doc_date') {
1183 $sqlwhere[] = $key.
" = '".$this->db->idate($value).
"'";
1184 } elseif ($key ==
't.doc_date>=' || $key ==
't.doc_date<=' || $key ==
't.doc_date>' || $key ==
't.doc_date<') {
1185 $sqlwhere[] = $key.
"'".$this->db->idate($value).
"'";
1186 } elseif ($key ==
't.numero_compte>=' || $key ==
't.numero_compte<=' || $key ==
't.subledger_account>=' || $key ==
't.subledger_account<=') {
1187 $sqlwhere[] = $key.
"'".$this->db->escape($value).
"'";
1188 } elseif ($key ==
't.fk_doc' || $key ==
't.fk_docdet' || $key ==
't.piece_num') {
1189 $sqlwhere[] = $key.
" = ".((int) $value);
1190 } elseif ($key ==
't.subledger_account' || $key ==
't.numero_compte') {
1191 $sqlwhere[] = $key.
" LIKE '".$this->db->escape($value).
"%'";
1192 } elseif ($key ==
't.subledger_label') {
1193 $sqlwhere[] = $key.
" LIKE '".$this->db->escape($value).
"%'";
1194 } elseif ($key ==
't.code_journal' && !empty($value)) {
1195 if (is_array($value)) {
1196 $sqlwhere[] =
natural_search(
"t.code_journal", join(
',', $value), 3, 1);
1200 } elseif ($key ==
't.reconciled_option') {
1201 $sqlwhere[] =
't.lettering_code IS NULL';
1203 $sqlwhere[] = $key.
" LIKE '%".$this->db->escape($value).
"%'";
1207 $sql .=
' WHERE entity = ' . ((int) $conf->entity);
1208 if (count($sqlwhere) > 0) {
1209 $sql .=
" AND ".implode(
" ".$filtermode.
" ", $sqlwhere);
1212 if (!empty($option)) {
1213 $sql .=
' AND t.subledger_account IS NOT NULL';
1214 $sql .=
' AND t.subledger_account != ""';
1215 $sql .=
' GROUP BY t.numero_compte, t.label_compte, t.subledger_account, t.subledger_label';
1216 $sortfield =
't.subledger_account'.($sortfield ?
','.$sortfield :
'');
1217 $sortorder =
'ASC'.($sortfield ?
','.$sortfield :
'');
1219 $sql .=
' GROUP BY t.numero_compte, t.label_compte';
1220 $sortfield =
't.numero_compte'.($sortfield ?
','.$sortfield :
'');
1221 $sortorder =
'ASC'.($sortorder ?
','.$sortorder :
'');
1224 if (!empty($sortfield)) {
1225 $sql .= $this->db->order($sortfield, $sortorder);
1227 if (!empty($limit)) {
1228 $sql .= $this->db->plimit($limit + 1, $offset);
1231 $resql = $this->db->query(
$sql);
1233 $num = $this->db->num_rows($resql);
1236 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1239 $line->numero_compte = $obj->numero_compte;
1240 $line->label_compte = $obj->label_compte;
1241 $line->subledger_account = $obj->subledger_account;
1242 $line->subledger_label = $obj->subledger_label;
1243 $line->debit = $obj->debit;
1244 $line->credit = $obj->credit;
1246 $this->lines[] = $line;
1250 $this->db->free($resql);
1254 $this->errors[] =
'Error '.$this->db->lasterror();
1255 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1269 public function update(
User $user, $notrigger =
false, $mode =
'')
1276 if (isset($this->doc_type)) {
1277 $this->doc_type = trim($this->doc_type);
1279 if (isset($this->doc_ref)) {
1280 $this->doc_ref = trim($this->doc_ref);
1282 if (isset($this->fk_doc)) {
1283 $this->fk_doc = (int) $this->fk_doc;
1285 if (isset($this->fk_docdet)) {
1286 $this->fk_docdet = (int) $this->fk_docdet;
1288 if (isset($this->thirdparty_code)) {
1289 $this->thirdparty_code = trim($this->thirdparty_code);
1291 if (isset($this->subledger_account)) {
1292 $this->subledger_account = trim($this->subledger_account);
1294 if (isset($this->subledger_label)) {
1295 $this->subledger_label = trim($this->subledger_label);
1297 if (isset($this->numero_compte)) {
1298 $this->numero_compte = trim($this->numero_compte);
1300 if (isset($this->label_compte)) {
1301 $this->label_compte = trim($this->label_compte);
1303 if (isset($this->label_operation)) {
1304 $this->label_operation = trim($this->label_operation);
1306 if (isset($this->debit)) {
1307 $this->debit = trim($this->debit);
1309 if (isset($this->credit)) {
1310 $this->credit = trim($this->credit);
1312 if (isset($this->amount)) {
1313 $this->amount = trim($this->amount);
1315 if (isset($this->sens)) {
1316 $this->sens = trim($this->sens);
1318 if (isset($this->import_key)) {
1319 $this->import_key = trim($this->import_key);
1321 if (isset($this->code_journal)) {
1322 $this->code_journal = trim($this->code_journal);
1324 if (isset($this->journal_label)) {
1325 $this->journal_label = trim($this->journal_label);
1327 if (isset($this->piece_num)) {
1328 $this->piece_num = trim($this->piece_num);
1331 $this->debit =
price2num($this->debit,
'MT');
1332 $this->credit =
price2num($this->credit,
'MT');
1338 $sql =
'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.
' SET';
1339 $sql .=
' doc_date = '.(!isset($this->doc_date) ||
dol_strlen($this->doc_date) != 0 ?
"'".$this->db->idate($this->doc_date).
"'" :
'null').
',';
1340 $sql .=
' doc_type = '.(isset($this->doc_type) ?
"'".$this->db->escape($this->doc_type).
"'" :
"null").
',';
1341 $sql .=
' doc_ref = '.(isset($this->doc_ref) ?
"'".$this->db->escape($this->doc_ref).
"'" :
"null").
',';
1342 $sql .=
' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc :
"null").
',';
1343 $sql .=
' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet :
"null").
',';
1344 $sql .=
' thirdparty_code = '.(isset($this->thirdparty_code) ?
"'".$this->db->escape($this->thirdparty_code).
"'" :
"null").
',';
1345 $sql .=
' subledger_account = '.(isset($this->subledger_account) ?
"'".$this->db->escape($this->subledger_account).
"'" :
"null").
',';
1346 $sql .=
' subledger_label = '.(isset($this->subledger_label) ?
"'".$this->db->escape($this->subledger_label).
"'" :
"null").
',';
1347 $sql .=
' numero_compte = '.(isset($this->numero_compte) ?
"'".$this->db->escape($this->numero_compte).
"'" :
"null").
',';
1348 $sql .=
' label_compte = '.(isset($this->label_compte) ?
"'".$this->db->escape($this->label_compte).
"'" :
"null").
',';
1349 $sql .=
' label_operation = '.(isset($this->label_operation) ?
"'".$this->db->escape($this->label_operation).
"'" :
"null").
',';
1350 $sql .=
' debit = '.(isset($this->debit) ? $this->debit :
"null").
',';
1351 $sql .=
' credit = '.(isset($this->credit) ? $this->credit :
"null").
',';
1352 $sql .=
' montant = '.(isset($this->montant) ? $this->montant :
"null").
',';
1353 $sql .=
' sens = '.(isset($this->sens) ?
"'".$this->db->escape($this->sens).
"'" :
"null").
',';
1354 $sql .=
' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author :
"null").
',';
1355 $sql .=
' import_key = '.(isset($this->import_key) ?
"'".$this->db->escape($this->import_key).
"'" :
"null").
',';
1356 $sql .=
' code_journal = '.(isset($this->code_journal) ?
"'".$this->db->escape($this->code_journal).
"'" :
"null").
',';
1357 $sql .=
' journal_label = '.(isset($this->journal_label) ?
"'".$this->db->escape($this->journal_label).
"'" :
"null").
',';
1358 $sql .=
' piece_num = '.(isset($this->piece_num) ? $this->piece_num :
"null");
1359 $sql .=
' WHERE rowid='.((int) $this->
id);
1363 $resql = $this->db->query(
$sql);
1366 $this->errors[] =
'Error '.$this->db->lasterror();
1367 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1382 $this->db->rollback();
1386 $this->db->commit();
1401 public function updateByMvt($piece_num =
'', $field =
'', $value =
'', $mode =
'')
1407 $sql =
"UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1408 $sql .=
" SET ".$field.
" = ".(is_numeric($value) ? ((
float) $value) :
"'".$this->db->escape($value).
"'");
1409 $sql .=
" WHERE piece_num = ".((int) $piece_num);
1411 $resql = $this->db->query(
$sql);
1415 $this->errors[] =
'Error '.$this->db->lasterror();
1416 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1419 $this->db->rollback();
1423 $this->db->commit();
1437 public function delete(
User $user, $notrigger =
false, $mode =
'')
1456 $sql =
'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1457 $sql .=
' WHERE rowid='.((int) $this->
id);
1459 $resql = $this->db->query(
$sql);
1462 $this->errors[] =
'Error '.$this->db->lasterror();
1463 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1469 $this->db->rollback();
1473 $this->db->commit();
1491 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
1492 $sql .=
" WHERE import_key = '".$this->db->escape($importkey).
"'";
1494 $resql = $this->db->query(
$sql);
1497 $this->errors[] =
"Error ".$this->db->lasterror();
1498 dol_syslog(get_class($this).
"::delete Error ".$this->db->lasterror(), LOG_ERR);
1499 $this->db->rollback();
1503 $this->db->commit();
1518 global $conf, $langs;
1520 if (empty($delyear) && empty($journal)) {
1521 $this->error =
'ErrorOneFieldRequired';
1524 if (!empty($delmonth) && empty($delyear)) {
1525 $this->error =
'YearRequiredIfMonthDefined';
1533 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1534 $sql .=
" WHERE 1 = 1";
1536 if (!empty($journal)) {
1537 $sql .=
" AND code_journal = '".$this->db->escape($journal).
"'";
1539 $sql .=
" AND entity = " . ((int) $conf->entity);
1541 $sql .=
" AND date_validated IS NULL";
1545 $resql = $this->db->query(
$sql);
1548 $this->errors[] =
"Error ".$this->db->lasterror();
1549 foreach ($this->errors as $errmsg) {
1550 dol_syslog(get_class($this).
"::delete ".$errmsg, LOG_ERR);
1551 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
1553 $this->db->rollback();
1557 $this->db->commit();
1575 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
1576 $sql .=
" WHERE piece_num = ".(int) $piecenum;
1577 $sql .=
" AND date_validated IS NULL";
1578 $sql .=
" AND entity = " . ((int) $conf->entity);
1580 $resql = $this->db->query(
$sql);
1583 $this->errors[] =
"Error ".$this->db->lasterror();
1584 foreach ($this->errors as $errmsg) {
1585 dol_syslog(get_class($this).
"::delete ".$errmsg, LOG_ERR);
1586 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
1588 $this->db->rollback();
1592 $this->db->commit();
1613 $object->fetch($fromid);
1621 $object->context[
'createfromclone'] =
'createfromclone';
1622 $result = $object->create($user);
1627 $this->errors = $object->errors;
1628 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1631 unset($object->context[
'createfromclone']);
1635 $this->db->commit();
1639 $this->db->rollback();
1658 $this->doc_date = $now;
1659 $this->doc_type =
'';
1660 $this->doc_ref =
'';
1662 $this->fk_docdet = 0;
1663 $this->thirdparty_code =
'CU001';
1664 $this->subledger_account =
'41100001';
1665 $this->subledger_label =
'My customer company';
1666 $this->numero_compte =
'411';
1667 $this->label_compte =
'Customer';
1668 $this->label_operation =
'Sales of pea';
1669 $this->debit = 99.9;
1670 $this->credit = 0.0;
1671 $this->amount = 0.0;
1673 $this->fk_user_author = $user->id;
1674 $this->import_key =
'20201027';
1675 $this->code_journal =
'VT';
1676 $this->journal_label =
'Journal de vente';
1677 $this->piece_num = 1234;
1678 $this->date_creation = $now;
1692 $sql =
"SELECT piece_num, doc_date,code_journal, journal_label, doc_ref, doc_type,";
1693 $sql .=
" date_creation, tms as date_modification, date_validated as date_validation";
1695 if ($mode !=
"_tmp") {
1696 $sql .=
", date_export";
1698 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1699 $sql .=
" WHERE piece_num = ".((int) $piecenum);
1700 $sql .=
" AND entity = " . ((int) $conf->entity);
1703 $result = $this->db->query(
$sql);
1705 $obj = $this->db->fetch_object($result);
1707 $this->piece_num = $obj->piece_num;
1708 $this->code_journal = $obj->code_journal;
1709 $this->journal_label = $obj->journal_label;
1710 $this->doc_date = $this->db->jdate($obj->doc_date);
1711 $this->doc_ref = $obj->doc_ref;
1712 $this->doc_type = $obj->doc_type;
1713 $this->date_creation = $this->db->jdate($obj->date_creation);
1714 $this->date_modification = $this->db->jdate($obj->date_modification);
1715 if ($mode !=
"_tmp") {
1716 $this->date_export = $this->db->jdate($obj->date_export);
1718 $this->date_validation = $this->db->jdate($obj->date_validation);
1720 $this->error =
"Error ".$this->db->lasterror();
1721 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1738 $sql =
"SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1739 $sql .=
" WHERE entity = " . ((int) $conf->entity);
1741 dol_syslog(get_class($this).
"::getNextNumMvt", LOG_DEBUG);
1743 $result = $this->db->query(
$sql);
1746 $obj = $this->db->fetch_object($result);
1748 $result = $obj->max;
1750 if (empty($result)) {
1755 $this->error =
"Error ".$this->db->lasterror();
1756 dol_syslog(get_class($this).
"::getNextNumMvt ".$this->error, LOG_ERR);
1772 $sql =
"SELECT rowid, doc_date, doc_type,";
1773 $sql .=
" doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1774 $sql .=
" numero_compte, label_compte, label_operation, debit, credit,";
1775 $sql .=
" montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1776 $sql .=
" date_creation, tms as date_modification, date_validated as date_validation";
1778 if ($mode !=
"_tmp") {
1779 $sql .=
", date_export";
1781 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1782 $sql .=
" WHERE piece_num = ".((int) $piecenum);
1783 $sql .=
" AND entity = " . ((int) $conf->entity);
1786 $result = $this->db->query(
$sql);
1788 while ($obj = $this->db->fetch_object($result)) {
1791 $line->id = $obj->rowid;
1793 $line->doc_date = $this->db->jdate($obj->doc_date);
1794 $line->doc_type = $obj->doc_type;
1795 $line->doc_ref = $obj->doc_ref;
1796 $line->fk_doc = $obj->fk_doc;
1797 $line->fk_docdet = $obj->fk_docdet;
1798 $line->thirdparty_code = $obj->thirdparty_code;
1799 $line->subledger_account = $obj->subledger_account;
1800 $line->subledger_label = $obj->subledger_label;
1801 $line->numero_compte = $obj->numero_compte;
1802 $line->label_compte = $obj->label_compte;
1803 $line->label_operation = $obj->label_operation;
1804 $line->debit = $obj->debit;
1805 $line->credit = $obj->credit;
1806 $line->montant = $obj->amount;
1807 $line->amount = $obj->amount;
1808 $line->sens = $obj->sens;
1809 $line->code_journal = $obj->code_journal;
1810 $line->journal_label = $obj->journal_label;
1811 $line->piece_num = $obj->piece_num;
1812 $line->date_creation = $obj->date_creation;
1813 $line->date_modification = $obj->date_modification;
1814 if ($mode !=
"_tmp") {
1815 $line->date_export = $obj->date_export;
1817 $line->date_validation = $obj->date_validation;
1819 $this->linesmvt[] = $line;
1822 $this->error =
"Error ".$this->db->lasterror();
1823 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1842 $sql =
"SELECT rowid, doc_date, doc_type,";
1843 $sql .=
" doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1844 $sql .=
" numero_compte, label_compte, label_operation, debit, credit,";
1845 $sql .=
" montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
1846 $sql .=
" date_validated as date_validation";
1847 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->table_element;
1848 $sql .=
" WHERE entity = " . ((int) $conf->entity);
1850 dol_syslog(get_class($this).
"::export_bookkeeping", LOG_DEBUG);
1852 $resql = $this->db->query(
$sql);
1855 $this->linesexport = array();
1857 $num = $this->db->num_rows($resql);
1858 while ($obj = $this->db->fetch_object($resql)) {
1861 $line->id = $obj->rowid;
1863 $line->doc_date = $this->db->jdate($obj->doc_date);
1864 $line->doc_type = $obj->doc_type;
1865 $line->doc_ref = $obj->doc_ref;
1866 $line->fk_doc = $obj->fk_doc;
1867 $line->fk_docdet = $obj->fk_docdet;
1868 $line->thirdparty_code = $obj->thirdparty_code;
1869 $line->subledger_account = $obj->subledger_account;
1870 $line->subledger_label = $obj->subledger_label;
1871 $line->numero_compte = $obj->numero_compte;
1872 $line->label_compte = $obj->label_compte;
1873 $line->label_operation = $obj->label_operation;
1874 $line->debit = $obj->debit;
1875 $line->credit = $obj->credit;
1876 $line->montant = $obj->amount;
1877 $line->amount = $obj->amount;
1878 $line->sens = $obj->sens;
1879 $line->code_journal = $obj->code_journal;
1880 $line->piece_num = $obj->piece_num;
1881 $line->date_validation = $obj->date_validation;
1883 $this->linesexport[] = $line;
1885 $this->db->free($resql);
1889 $this->error =
"Error ".$this->db->lasterror();
1890 dol_syslog(get_class($this).
"::export_bookkeeping ".$this->error, LOG_ERR);
1910 if ($direction == 0) {
1914 if ($next_piecenum < 0) {
1920 $sql =
'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.
'_tmp WHERE piece_num = '.((int) $piece_num).
' AND entity = ' .((int) $conf->entity).
" AND numero_compte IS NULL AND debit = 0 AND credit = 0";
1921 $resql = $this->db->query(
$sql);
1924 $this->errors[] =
'Error '.$this->db->lasterror();
1925 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1930 $sql =
'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.
' (doc_date, doc_type,';
1931 $sql .=
' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1932 $sql .=
' numero_compte, label_compte, label_operation, debit, credit,';
1933 $sql .=
' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
1934 $sql .=
' SELECT doc_date, doc_type,';
1935 $sql .=
' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1936 $sql .=
' numero_compte, label_compte, label_operation, debit, credit,';
1937 $sql .=
' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).
", '".$this->db->idate($now).
"'";
1938 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.
'_tmp WHERE piece_num = '.((int) $piece_num).
' AND numero_compte IS NOT NULL AND entity = ' .((int) $conf->entity);
1939 $resql = $this->db->query(
$sql);
1942 $this->errors[] =
'Error '.$this->db->lasterror();
1943 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1948 $sql =
'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.
'_tmp WHERE piece_num = '.((int) $piece_num).
' AND entity = ' .((int) $conf->entity);
1949 $resql = $this->db->query(
$sql);
1952 $this->errors[] =
'Error '.$this->db->lasterror();
1953 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1956 } elseif ($direction == 1) {
1958 $sql =
'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.
'_tmp WHERE piece_num = '.((int) $piece_num).
' AND entity = ' .((int) $conf->entity);
1959 $resql = $this->db->query(
$sql);
1962 $this->errors[] =
'Error '.$this->db->lasterror();
1963 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1968 $sql =
'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.
'_tmp (doc_date, doc_type,';
1969 $sql .=
' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1970 $sql .=
' numero_compte, label_compte, label_operation, debit, credit,';
1971 $sql .=
' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1972 $sql .=
' SELECT doc_date, doc_type,';
1973 $sql .=
' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1974 $sql .=
' numero_compte, label_compte, label_operation, debit, credit,';
1975 $sql .=
' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
1976 $sql .=
' FROM '.MAIN_DB_PREFIX.$this->table_element.
' WHERE piece_num = '.((int) $piece_num).
' AND entity = ' .((int) $conf->entity);
1977 $resql = $this->db->query(
$sql);
1980 $this->errors[] =
'Error '.$this->db->lasterror();
1981 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1986 $sql =
'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.
'_tmp WHERE piece_num = '.((int) $piece_num).
' AND entity = ' .((int) $conf->entity);
1987 $resql = $this->db->query(
$sql);
1990 $this->errors[] =
'Error '.$this->db->lasterror();
1991 dol_syslog(__METHOD__.
' '.join(
',', $this->errors), LOG_ERR);
1996 $this->db->commit();
1999 $this->db->rollback();
2027 public function select_account($selectid, $htmlname =
'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase =
'')
2032 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
2036 $sql =
"SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2037 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping as ab";
2038 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.account_number = ab.numero_compte";
2039 $sql .=
" AND aa.active = 1";
2040 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2041 $sql .=
" AND asy.rowid = ".((int) $pcgver);
2042 $sql .=
" AND ab.entity = " . ((int) $conf->entity);
2043 $sql .=
" ORDER BY account_number ASC";
2045 dol_syslog(get_class($this).
"::select_account", LOG_DEBUG);
2046 $resql = $this->db->query(
$sql);
2049 $this->error =
"Error ".$this->db->lasterror();
2050 dol_syslog(get_class($this).
"::select_account ".$this->error, LOG_ERR);
2059 while ($obj = $this->db->fetch_object($resql)) {
2062 $select_value_in = $obj->rowid;
2063 $select_value_out = $obj->rowid;
2065 if ($select_in == 1) {
2066 $select_value_in = $obj->account_number;
2068 if ($select_out == 1) {
2069 $select_value_out = $obj->account_number;
2074 if (($selectid !=
'') && $selectid == $select_value_in) {
2075 $selected = $select_value_out;
2078 $options[$select_value_out] = $label;
2081 $out .=
Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0,
'', 0, 0, 0,
'',
'maxwidth300');
2082 $this->db->free($resql);
2098 $sql =
"SELECT root.rowid, root.account_number, root.label as label,";
2099 $sql .=
" parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2100 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as aa";
2101 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2102 $sql .=
" AND asy.rowid = ".((int) $pcgver);
2103 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2104 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2105 $sql .=
" WHERE aa.account_number = '".$this->db->escape($account).
"'";
2106 $sql .=
" AND aa.entity = " . ((int) $conf->entity);
2108 dol_syslog(get_class($this).
"::select_account", LOG_DEBUG);
2109 $resql = $this->db->query(
$sql);
2112 if ($this->db->num_rows($resql)) {
2113 $obj = $this->db->fetch_object($resql);
2116 $result = array(
'id'=>$obj->rowid,
'account_number'=>$obj->account_number,
'label'=>$obj->label);
2119 $this->error =
"Error ".$this->db->lasterror();
2120 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
2139 $sql =
"SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2140 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as aa ";
2141 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2142 $sql .=
" AND aa.account_number = '".$this->db->escape($account).
"'";
2143 $sql .=
" AND asy.rowid = ".((int) $pcgver);
2144 $sql .=
" AND aa.active = 1";
2145 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2146 $sql .=
" WHERE aa.entity = " . ((int) $conf->entity);
2148 dol_syslog(get_class($this).
"::select_account", LOG_DEBUG);
2149 $resql = $this->db->query(
$sql);
2152 if ($this->db->num_rows($resql)) {
2153 $obj = $this->db->fetch_object($resql);
2155 if (empty($obj->category)) {
2158 return $obj->label.
' ('.$obj->category.
')';
2161 $this->error =
"Error ".$this->db->lasterror();
2162 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
2178 public $doc_date =
'';
2192 public $thirdparty_code;
2193 public $subledger_account;
2194 public $subledger_label;
2195 public $numero_compte;
2196 public $label_compte;
2197 public $label_operation;
2215 public $multicurrency_amount;
2220 public $multicurrency_code;
2226 public $lettering_code;
2227 public $date_lettering;
2232 public $fk_user_author;
2235 public $code_journal;
2236 public $journal_label;
2242 public $date_creation;
2247 public $date_modification;
2252 public $date_export;
2257 public $date_validation;
2262 public $date_lim_reglement;