1<?php 2/* Copyright (C) 2005-2009 Regis Houssin <regis.houssin@inodbox.com> 3 * Copyright (C) 2008-2009 Laurent Destailleur (Eldy) <eldy@users.sourceforge.net> 4 * Copyright (C) 2008 Raphael Bertrand (Resultic) <raphael.bertrand@resultic.fr> 5 * Copyright (C) 2015 Marcos García <marcosgdf@gmail.com 6 * Copyright (C) 2016 Frédéric France <frederic.france@free.fr> 7 * 8 * This program is free software; you can redistribute it and/or modify 9 * it under the terms of the GNU General Public License as published by 10 * the Free Software Foundation; either version 3 of the License, or 11 * (at your option) any later version. 12 * 13 * This program is distributed in the hope that it will be useful, 14 * but WITHOUT ANY WARRANTY; without even the implied warranty of 15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 16 * GNU General Public License for more details. 17 * 18 * You should have received a copy of the GNU General Public License 19 * along with this program. If not, see <https://www.gnu.org/licenses/>. 20 */ 21 22/** 23 * \file htdocs/compta/bank/treso.php 24 * \ingroup banque 25 * \brief Page to estimate future balance 26 */ 27 28require '../../main.inc.php'; 29require_once DOL_DOCUMENT_ROOT.'/core/lib/bank.lib.php'; 30require_once DOL_DOCUMENT_ROOT.'/societe/class/societe.class.php'; 31require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php'; 32require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php'; 33require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/chargesociales.class.php'; 34require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php'; 35 36// Load translation files required by the page 37$langs->loadLangs(array('banks', 'categories', 'bills', 'companies')); 38 39// Security check 40if (isset($_GET["account"]) || isset($_GET["ref"])) 41{ 42 $id = isset($_GET["account"]) ? $_GET["account"] : (isset($_GET["ref"]) ? $_GET["ref"] : ''); 43} 44$fieldid = isset($_GET["ref"]) ? 'ref' : 'rowid'; 45if ($user->socid) $socid = $user->socid; 46$result = restrictedArea($user, 'banque', $id, 'bank_account&bank_account', '', '', $fieldid); 47 48 49$vline = isset($_GET["vline"]) ? $_GET["vline"] : $_POST["vline"]; 50$page = isset($_GET["page"]) ? $_GET["page"] : 0; 51 52// Initialize technical object to manage hooks of page. Note that conf->hooks_modules contains array of hook context 53$hookmanager->initHooks(array('banktreso', 'globalcard')); 54 55/* 56 * View 57 */ 58 59$title = $langs->trans("FinancialAccount").' - '.$langs->trans("PlannedTransactions"); 60$helpurl = ""; 61llxHeader('', $title, $helpurl); 62 63$societestatic = new Societe($db); 64$facturestatic = new Facture($db); 65$facturefournstatic = new FactureFournisseur($db); 66$socialcontribstatic = new ChargeSociales($db); 67 68$form = new Form($db); 69 70if ($_REQUEST["account"] || $_REQUEST["ref"]) 71{ 72 if ($vline) 73 { 74 $viewline = $vline; 75 } else { 76 $viewline = 20; 77 } 78 79 $object = new Account($db); 80 if ($_GET["account"]) 81 { 82 $result = $object->fetch($_GET["account"]); 83 } 84 if ($_GET["ref"]) 85 { 86 $result = $object->fetch(0, $_GET["ref"]); 87 $_GET["account"] = $object->id; 88 } 89 90 91 // Onglets 92 $head = bank_prepare_head($object); 93 print dol_get_fiche_head($head, 'cash', $langs->trans("FinancialAccount"), 0, 'account'); 94 95 $linkback = '<a href="'.DOL_URL_ROOT.'/compta/bank/list.php?restore_lastsearch_values=1">'.$langs->trans("BackToList").'</a>'; 96 97 dol_banner_tab($object, 'ref', $linkback, 1, 'ref', 'ref', $morehtmlref, '', 0, '', '', 1); 98 99 print dol_get_fiche_end(); 100 101 print '<br>'; 102 103 $solde = $object->solde(0); 104 if ($conf->global->MULTICOMPANY_INVOICE_SHARING_ENABLED)$colspan = 6; 105 else $colspan = 5; 106 107 // Show next coming entries 108 print '<div class="div-table-responsive">'; 109 print '<table class="noborder centpercent">'; 110 111 // Ligne de titre tableau des ecritures 112 print '<tr class="liste_titre">'; 113 print '<td>'.$langs->trans("DateDue").'</td>'; 114 print '<td>'.$langs->trans("Description").'</td>'; 115 if ($conf->global->MULTICOMPANY_INVOICE_SHARING_ENABLED)print '<td>'.$langs->trans("Entity").'</td>'; 116 print '<td>'.$langs->trans("ThirdParty").'</td>'; 117 print '<td class="right">'.$langs->trans("Debit").'</td>'; 118 print '<td class="right">'.$langs->trans("Credit").'</td>'; 119 print '<td class="right" width="80">'.$langs->trans("BankBalance").'</td>'; 120 print '</tr>'; 121 122 // Current balance 123 print '<tr class="liste_total">'; 124 print '<td class="left" colspan="5">'.$langs->trans("CurrentBalance").'</td>'; 125 print '<td class="nowrap right">'.price($solde).'</td>'; 126 print '</tr>'; 127 128 129 print '<tr class="liste_titre">'; 130 print '<td class="left" colspan="'.$colspan.'">'.$langs->trans("RemainderToPay").'</td>'; 131 print '<td class="nowrap right"> </td>'; 132 print '</tr>'; 133 134 135 // Remainder to pay in future 136 $sqls = array(); 137 138 // Customer invoices 139 $sql = "SELECT 'invoice' as family, f.rowid as objid, f.ref as ref, f.total_ttc, f.type, f.date_lim_reglement as dlr,"; 140 $sql .= " s.rowid as socid, s.nom as name, s.fournisseur"; 141 $sql .= " FROM ".MAIN_DB_PREFIX."facture as f"; 142 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON f.fk_soc = s.rowid"; 143 $sql .= " WHERE f.entity IN (".getEntity('invoice').")"; 144 $sql .= " AND f.paye = 0 AND f.fk_statut = 1"; // Not paid 145 $sql .= " AND (f.fk_account IN (0, ".$object->id.") OR f.fk_account IS NULL)"; // Id bank account of invoice 146 $sql .= " ORDER BY dlr ASC"; 147 $sqls[] = $sql; 148 149 // Supplier invoices 150 $sql = " SELECT 'invoice_supplier' as family, ff.rowid as objid, ff.ref as ref, ff.ref_supplier as ref_supplier, (-1*ff.total_ttc) as total_ttc, ff.type, ff.date_lim_reglement as dlr,"; 151 $sql .= " s.rowid as socid, s.nom as name, s.fournisseur"; 152 $sql .= " FROM ".MAIN_DB_PREFIX."facture_fourn as ff"; 153 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON ff.fk_soc = s.rowid"; 154 $sql .= " WHERE ff.entity = ".$conf->entity; 155 $sql .= " AND ff.paye = 0 AND fk_statut = 1"; // Not paid 156 $sql .= " AND (ff.fk_account IN (0, ".$object->id.") OR ff.fk_account IS NULL)"; // Id bank account of supplier invoice 157 $sql .= " ORDER BY dlr ASC"; 158 $sqls[] = $sql; 159 160 // Social contributions 161 $sql = " SELECT 'social_contribution' as family, cs.rowid as objid, cs.libelle as ref, (-1*cs.amount) as total_ttc, ccs.libelle as type, cs.date_ech as dlr"; 162 $sql .= ", cs.fk_account"; 163 $sql .= " FROM ".MAIN_DB_PREFIX."chargesociales as cs"; 164 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_chargesociales as ccs ON cs.fk_type = ccs.id"; 165 $sql .= " WHERE cs.entity = ".$conf->entity; 166 $sql .= " AND cs.paye = 0"; // Not paid 167 $sql .= " AND (cs.fk_account IN (0, ".$object->id.") OR cs.fk_account IS NULL)"; // Id bank account of social contribution 168 $sql .= " ORDER BY dlr ASC"; 169 $sqls[] = $sql; 170 171 // others sql 172 $parameters = array(); 173 $reshook = $hookmanager->executeHooks('addMoreSQL', $parameters, $object, $action); // Note that $action and $object may have been modified by hook 174 if (empty($reshook) and isset($hookmanager->resArray['sql'])) { 175 $sqls[] = $hookmanager->resArray['sql']; 176 } 177 178 $error = 0; 179 $tab_sqlobjOrder = array(); 180 $tab_sqlobj = array(); 181 182 foreach ($sqls as $sql) { 183 $resql = $db->query($sql); 184 if ($resql) { 185 while ($sqlobj = $db->fetch_object($resql)) { 186 $tab_sqlobj[] = $sqlobj; 187 $tab_sqlobjOrder[] = $db->jdate($sqlobj->dlr); 188 } 189 $db->free($resql); 190 } else { 191 $error++; 192 } 193 } 194 195 // Sort array 196 if (!$error) 197 { 198 array_multisort($tab_sqlobjOrder, $tab_sqlobj); 199 200 // Apply distinct filter 201 foreach ($tab_sqlobj as $key=>$value) { 202 $tab_sqlobj[$key] = "'".serialize($value)."'"; 203 } 204 $tab_sqlobj = array_unique($tab_sqlobj); 205 foreach ($tab_sqlobj as $key=>$value) { 206 $tab_sqlobj[$key] = unserialize(trim($value, "'")); 207 } 208 209 $num = count($tab_sqlobj); 210 211 $i = 0; 212 while ($i < $num) 213 { 214 $ref = ''; 215 $refcomp = ''; 216 $totalpayment = ''; 217 218 $obj = array_shift($tab_sqlobj); 219 220 if ($obj->family == 'invoice_supplier') 221 { 222 $showline = 1; 223 // Uncomment this line to avoid to count suppliers credit note (ff.type = 2) 224 //$showline=(($obj->total_ttc < 0 && $obj->type != 2) || ($obj->total_ttc > 0 && $obj->type == 2)) 225 if ($showline) 226 { 227 $ref = $obj->ref; 228 $facturefournstatic->ref = $ref; 229 $facturefournstatic->id = $obj->objid; 230 $facturefournstatic->type = $obj->type; 231 $ref = $facturefournstatic->getNomUrl(1, ''); 232 233 $societestatic->id = $obj->socid; 234 $societestatic->name = $obj->name; 235 $refcomp = $societestatic->getNomUrl(1, '', 24); 236 237 $totalpayment = -1 * $facturefournstatic->getSommePaiement(); // Payment already done 238 } 239 } 240 if ($obj->family == 'invoice') 241 { 242 $facturestatic->ref = $obj->ref; 243 $facturestatic->id = $obj->objid; 244 $facturestatic->type = $obj->type; 245 $ref = $facturestatic->getNomUrl(1, ''); 246 247 $societestatic->id = $obj->socid; 248 $societestatic->name = $obj->name; 249 $refcomp = $societestatic->getNomUrl(1, '', 24); 250 251 $totalpayment = $facturestatic->getSommePaiement(); // Payment already done 252 $totalpayment += $facturestatic->getSumDepositsUsed(); 253 $totalpayment += $facturestatic->getSumCreditNotesUsed(); 254 } 255 if ($obj->family == 'social_contribution') 256 { 257 $socialcontribstatic->ref = $obj->ref; 258 $socialcontribstatic->id = $obj->objid; 259 $socialcontribstatic->label = $obj->type; 260 $ref = $socialcontribstatic->getNomUrl(1, 24); 261 262 $totalpayment = -1 * $socialcontribstatic->getSommePaiement(); // Payment already done 263 } 264 265 $parameters = array('obj' => $obj, 'ref' => $ref, 'refcomp' => $refcomp, 'totalpayment' => $totalpayment); 266 $reshook = $hookmanager->executeHooks('moreFamily', $parameters, $object, $action); // Note that $action and $object may have been modified by hook 267 if (empty($reshook)) { 268 $ref = isset($hookmanager->resArray['ref']) ? $hookmanager->resArray['ref'] : $ref; 269 $refcomp = isset($hookmanager->resArray['refcomp']) ? $hookmanager->resArray['refcomp'] : $refcomp; 270 $totalpayment = isset($hookmanager->resArray['totalpayment']) ? $hookmanager->resArray['totalpayment'] : $totalpayment; 271 } 272 273 $total_ttc = $obj->total_ttc; 274 if ($totalpayment) $total_ttc = $obj->total_ttc - $totalpayment; 275 $solde += $total_ttc; 276 277 // We discard lines with a remainder to pay to 0 278 if (price2num($total_ttc) != 0) 279 { 280 // Show line 281 print '<tr class="oddeven">'; 282 print '<td>'; 283 if ($obj->dlr) print dol_print_date($db->jdate($obj->dlr), "day"); 284 else print $langs->trans("NotDefined"); 285 print "</td>"; 286 print "<td>".$ref."</td>"; 287 if ($conf->global->MULTICOMPANY_INVOICE_SHARING_ENABLED) { 288 if ($obj->family == 'invoice') { 289 $mc->getInfo($obj->entity); 290 print "<td>".$mc->label."</td>"; 291 } else { 292 print "<td></td>"; 293 } 294 } 295 print "<td>".$refcomp."</td>"; 296 if ($obj->total_ttc < 0) { print '<td class="nowrap right">'.price(abs($total_ttc))."</td><td> </td>"; }; 297 if ($obj->total_ttc >= 0) { print '<td> </td><td class="nowrap right">'.price($total_ttc)."</td>"; }; 298 print '<td class="nowrap right">'.price($solde).'</td>'; 299 print "</tr>"; 300 } 301 302 $i++; 303 } 304 } else { 305 dol_print_error($db); 306 } 307 308 // Other lines 309 $parameters = array('solde' => $solde); 310 $reshook = $hookmanager->executeHooks('printObjectLine', $parameters, $object, $action); // Note that $action and $object may have been modified by hook 311 if (empty($reshook)) { 312 print $hookmanager->resPrint; 313 $solde = isset($hookmanager->resArray['solde']) ? $hookmanager->resArray['solde'] : $solde; 314 } 315 316 // solde 317 print '<tr class="liste_total">'; 318 print '<td class="left" colspan="'.$colspan.'">'.$langs->trans("FutureBalance").' ('.$object->currency_code.')</td>'; 319 print '<td class="nowrap right">'.price($solde, 0, $langs, 0, 0, -1, $object->currency_code).'</td>'; 320 print '</tr>'; 321 322 print "</table>"; 323 print "</div>"; 324} else { 325 print $langs->trans("ErrorBankAccountNotFound"); 326} 327 328// End of page 329llxFooter(); 330$db->close(); 331