Overview
Comment: | Fix "asset or liability" accounts in projects reports |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk | stable |
Files: | files | file ages | folders |
SHA3-256: |
af4cceeef444deda7fecc5aaf1ccca0e |
User & Date: | bohwaz on 2022-04-24 01:21:38 |
Other Links: | manifest | tags |
Context
2022-04-24
| ||
03:07 | Improve assisted reconciliation: create transactions from reconciliation, use specific class check-in: 3ec551b56a user: bohwaz tags: trunk, stable | |
01:21 | Fix "asset or liability" accounts in projects reports check-in: af4cceeef4 user: bohwaz tags: trunk, stable | |
01:00 | Group projects by code, not ID check-in: 361d6987c4 user: bohwaz tags: trunk, stable | |
Changes
Modified src/include/lib/Garradin/Accounting/Reports.php from [7633520d72] to [4754478645].
︙ | ︙ | |||
257 258 259 260 261 262 263 | $balances = DB::getInstance()->getAssoc($sql); return ($balances[Account::REVENUE] ?? 0) - ($balances[Account::EXPENSE] ?? 0); } static public function getBalancesSQL(array $parts = []) { | | > > > > | | | | | | | | | | | | | | | < < < > > | 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 | $balances = DB::getInstance()->getAssoc($sql); return ($balances[Account::REVENUE] ?? 0) - ($balances[Account::EXPENSE] ?? 0); } static public function getBalancesSQL(array $parts = []) { return sprintf('SELECT %s id_year, id, label, code, type, debit, credit, position, balance, is_debt FROM ( SELECT %s t.id_year, a.id, a.label, a.code, a.type, SUM(l.credit) AS credit, SUM(l.debit) AS debit, CASE -- 3 = dynamic asset or liability depending on balance WHEN position = 3 AND SUM(l.debit - l.credit) > 0 THEN 1 -- 1 = Asset (actif) comptes fournisseurs, tiers créditeurs WHEN position = 3 THEN 2 -- 2 = Liability (passif), comptes clients, tiers débiteurs ELSE position END AS position, CASE WHEN position IN (1, 4) -- 1 = asset, 4 = expense OR (position = 3 AND SUM(l.debit - l.credit) > 0) THEN SUM(l.debit - l.credit) ELSE SUM(l.credit - l.debit) END AS balance, CASE WHEN SUM(l.debit - l.credit) > 0 THEN 1 ELSE 0 END AS is_debt FROM acc_transactions_lines l INNER JOIN acc_transactions t ON t.id = l.id_transaction INNER JOIN acc_accounts a ON a.id = l.id_account %s %s GROUP BY %s ) %s %s ORDER BY %s', isset($parts['select']) ? $parts['select'] . ',' : '', isset($parts['inner_select']) ? $parts['inner_select'] . ',' : '', $parts['inner_join'] ?? '', isset($parts['inner_where']) ? 'WHERE ' . $parts['inner_where'] : '', $parts['inner_group'] ?? 'a.id, t.id_year', isset($parts['where']) ? 'WHERE ' . $parts['where'] : '', isset($parts['group']) ? 'GROUP BY ' . $parts['group'] : '', $order ?? 'code' ); } /** * Returns accounts balances according to $criterias |
︙ | ︙ | |||
320 321 322 323 324 325 326 | if (empty($criterias['analytical']) && empty($criterias['user']) && empty($criterias['creator']) && empty($criterias['subscription'])) { $table = 'acc_accounts_balances'; } // Specific queries that can't rely on acc_accounts_balances if (!$table) { | > > > > > > > > > > > > > | | | 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 | if (empty($criterias['analytical']) && empty($criterias['user']) && empty($criterias['creator']) && empty($criterias['subscription'])) { $table = 'acc_accounts_balances'; } // Specific queries that can't rely on acc_accounts_balances if (!$table) { $where = null; // The position if (!empty($criterias['position'])) { $criterias['position'] = (array)$criterias['position']; if (in_array(Account::LIABILITY, $criterias['position']) || in_array(Account::ASSET, $criterias['position'])) { $where = self::getWhereClause(['position' => $criterias['position']]); $criterias['position'][] = Account::ASSET_OR_LIABILITY; } } $inner_where = self::getWhereClause($criterias, 't', 'l', 'a'); $remove_zero = $remove_zero ? ', ' . $remove_zero : ''; $inner_group = empty($criterias['year']) ? 'a.id' : null; $sql = self::getBalancesSQL(['group' => 'code ' . $having] + compact('order', 'inner_where', 'where', 'inner_group')); } else { $where = self::getWhereClause($criterias); $query = 'SELECT *, SUM(credit) AS credit, SUM(debit) AS debit, SUM(balance) AS balance FROM %s WHERE %s GROUP BY %s %s |
︙ | ︙ |