balancebymonth.php 5.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137
  1. <?php
  2. /* Copyright (C) 2013-2014 Olivier Geffroy <jeff@jeffinfo.com>
  3. * Copyright (C) 2013-2014 Alexandre Spangaro <aspangaro@open-dsi.fr>
  4. * Copyright (C) 2014 Florian Henry <florian.henry@open-concept.pro>
  5. * Copyright (C) 2015 Jean-François Ferry <jfefe@aternatik.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 3 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 <https://www.gnu.org/licenses/>.
  19. *
  20. */
  21. /**
  22. * \file htdocs/accountancy/bookkeeping/balancebymonth.php
  23. * \ingroup Accountancy (Double entries)
  24. * \brief Balance by month
  25. */
  26. require '../../main.inc.php';
  27. require_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';
  28. require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
  29. // Load translation files required by the page
  30. $langs->loadLangs(array("bills","compta","accountancy","other"));
  31. // Filter
  32. $year = GETPOST("year", 'int');
  33. if ($year == 0) {
  34. $year_current = strftime("%Y", time());
  35. $year_start = $year_current;
  36. } else {
  37. $year_current = $year;
  38. $year_start = $year;
  39. }
  40. /*
  41. * View
  42. */
  43. llxHeader('', $langs->trans("Bookkeeping"));
  44. $textprevyear = '<a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year_current - 1) . '">' . img_previous() . '</a>';
  45. $textnextyear = '&nbsp;<a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year_current + 1) . '">' . img_next() . '</a>';
  46. print load_fiche_titre($langs->trans("AccountBalanceByMonth") . ' ' . $textprevyear . ' ' . $langs->trans("Year") . ' ' . $year_start . ' ' . $textnextyear);
  47. $sql = "SELECT count(*) FROM " . MAIN_DB_PREFIX . "facturedet as fd";
  48. $sql .= " , " . MAIN_DB_PREFIX . "facture as f";
  49. $sql .= " WHERE fd.fk_code_ventilation = 0";
  50. $sql .= " AND f.rowid = fd.fk_facture AND f.fk_statut = 1;";
  51. dol_syslog('accountancy/bookkeeping/balancebymonth.php:: $sql=' . $sql);
  52. $result = $db->query($sql);
  53. if ($result) {
  54. $row = $db->fetch_row($result);
  55. $nbfac = $row[0];
  56. $db->free($result);
  57. }
  58. $y = $year_current;
  59. print '<table class="noborder" width="100%">';
  60. print '<tr class="liste_titre">';
  61. print '<td width=150>' . $langs->trans("Label") . '</td>';
  62. for($i = 1; $i <= 12; $i++)
  63. {
  64. print '<td class="right">' . $langs->trans("MonthShort".sprintf("%02s", $i)) . '</td>';
  65. }
  66. print '<td class="center"><strong>'.$langs->trans("Total").'</strong></td>';
  67. print '</tr>';
  68. $sql = "SELECT bk.numero_compte AS 'compte',";
  69. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=1,bk.montant,0)),2) AS 'Janvier',";
  70. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=2,bk.montant,0)),2) AS 'Fevrier',";
  71. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=3,bk.montant,0)),2) AS 'Mars',";
  72. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=4,bk.montant,0)),2) AS 'Avril',";
  73. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=5,bk.montant,0)),2) AS 'Mai',";
  74. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=6,bk.montant,0)),2) AS 'Juin',";
  75. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=7,bk.montant,0)),2) AS 'Juillet',";
  76. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=8,bk.montant,0)),2) AS 'Aout',";
  77. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=9,bk.montant,0)),2) AS 'Septembre',";
  78. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=10,bk.montant,0)),2) AS 'Octobre',";
  79. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=11,bk.montant,0)),2) AS 'Novembre',";
  80. $sql .= " ROUND(SUM(IF(MONTH(bk.doc_date)=12,bk.montant,0)),2) AS 'Decembre',";
  81. $sql .= " ROUND(SUM(bk.montant),2) as 'Total'";
  82. $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk";
  83. $sql .= " WHERE bk.doc_date >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
  84. $sql .= " AND bk.doc_date <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
  85. $sql .= " GROUP BY bk.numero_compte";
  86. $resql = $db->query($sql);
  87. if ($resql) {
  88. $i = 0;
  89. $num = $db->num_rows($resql);
  90. while ( $i < $num ) {
  91. $row = $db->fetch_row($resql);
  92. print '<tr class="oddeven"><td width="14%">' . length_accountg($row[0]) . '</td>';
  93. print '<td class="right" width="6.5%">' . price($row[1]) . '</td>';
  94. print '<td class="right" width="6.5%">' . price($row[2]) . '</td>';
  95. print '<td class="right" width="6.5%">' . price($row[3]) . '</td>';
  96. print '<td class="right" width="6.5%">' . price($row[4]) . '</td>';
  97. print '<td class="right" width="6.5%">' . price($row[5]) . '</td>';
  98. print '<td class="right" width="6.5%">' . price($row[6]) . '</td>';
  99. print '<td class="right" width="6.5%">' . price($row[7]) . '</td>';
  100. print '<td class="right" width="6.5%">' . price($row[8]) . '</td>';
  101. print '<td class="right" width="6.5%">' . price($row[9]) . '</td>';
  102. print '<td class="right" width="6.5%">' . price($row[10]) . '</td>';
  103. print '<td class="right" width="6.5%">' . price($row[11]) . '</td>';
  104. print '<td class="right" width="6.5%">' . price($row[12]) . '</td>';
  105. print '<td class="right" width="8%"><strong>' . price($row[13]) . '</strong></td>';
  106. print '</tr>';
  107. $i ++;
  108. }
  109. $db->free($resql);
  110. } else {
  111. print $db->lasterror();
  112. }
  113. print "</table>\n";
  114. // End of page
  115. llxFooter();
  116. $db->close();