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: af4cceeef444deda7fecc5aaf1ccca0e6fb381ce8fa027771a6412d90dd5d7cb
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
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
		$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,




				CASE -- 3 = dynamic asset or liability depending on balance
					WHEN position = 3 AND (debit - 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 (debit - credit) > 0)
					THEN
						debit - credit
					ELSE
						credit - debit
				END AS balance,
				CASE WHEN debit - credit > 0 THEN 1 ELSE 0 END AS is_debt
			FROM (
				SELECT %s t.id_year, a.id, a.label, a.code, a.position, a.type,
					SUM(l.credit) AS credit,
					SUM(l.debit) AS debit
				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
			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['group']) ? 'GROUP BY ' . $parts['group'] : '',
			$order ?? 'code'
		);
	}

	/**
	 * Returns accounts balances according to $criterias







|
>
>
>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<
<







>







>







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













327
328
329
330
331
332
333
334
335
336
337
338
		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 = 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, 'order' => $order, 'inner_where' => $where, 'inner_group' => $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







>
>
>
>
>
>
>
>
>
>
>
>
>
|



|







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