Overview
Comment:In trial balance, always return accounts where closing sum is zero
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA3-256: 334d67dcae990c704a37637bf5dbc822a7435d6cbb023110c49030074fcb0c84
User & Date: bohwaz on 2021-05-10 15:35:49
Other Links: manifest | tags
Context
2021-05-17
23:29
Fix list of users files check-in: ffe1ddab4e user: bohwaz tags: trunk, stable
2021-05-10
15:35
In trial balance, always return accounts where closing sum is zero check-in: 334d67dcae user: bohwaz tags: trunk, stable
14:16
New stable release check-in: 9396f02f3d user: bohwaz tags: trunk, stable, 1.1.5
Changes

Modified src/include/lib/Garradin/Accounting/Reports.php from [5ea34ddcf7] to [0f2d41f8c0].

221
222
223
224
225
226
227
228
229
230
231
232
233

234
235
236
237
238
239
240
241
242
243

244
245
246
247





248
249
250
251
252
253
254
		$db = DB::getInstance();
		$a = $db->firstColumn($sql, Account::REVENUE);
		$b = $db->firstColumn($sql, Account::EXPENSE);

		return (int)$a - (int)$b * -1;
	}

	static public function getClosingSumsWithAccounts(array $criterias, ?string $order = null, bool $reverse = false): array
	{
		$where = self::getWhereClause($criterias);

		$order = $order ?: 'a.code COLLATE NOCASE';
		$reverse = $reverse ? '* -1' : '';


		// Find sums, link them to accounts
		$sql = sprintf('SELECT a.id, a.code, a.label, a.position, SUM(l.credit) AS credit, SUM(l.debit) AS debit,
			SUM(l.credit - l.debit) %s AS sum
			FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction
			INNER JOIN %s a ON a.id = l.id_account
			WHERE %s
			GROUP BY l.id_account
			HAVING sum != 0

			ORDER BY %s;',
			$reverse, Line::TABLE, Transaction::TABLE, Account::TABLE, $where, $order);
		return DB::getInstance()->getGrouped($sql);
	}






	static public function getBalanceSheet(array $criterias): array
	{
		$out = [
			Account::ASSET => [],
			Account::LIABILITY => [],
			'sums' => [







|





>









<
>

|


>
>
>
>
>







221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243

244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
		$db = DB::getInstance();
		$a = $db->firstColumn($sql, Account::REVENUE);
		$b = $db->firstColumn($sql, Account::EXPENSE);

		return (int)$a - (int)$b * -1;
	}

	static public function getClosingSumsWithAccounts(array $criterias, ?string $order = null, bool $reverse = false, bool $remove_zero = true): array
	{
		$where = self::getWhereClause($criterias);

		$order = $order ?: 'a.code COLLATE NOCASE';
		$reverse = $reverse ? '* -1' : '';
		$remove_zero = $remove_zero ? 'HAVING sum != 0' : '';

		// Find sums, link them to accounts
		$sql = sprintf('SELECT a.id, a.code, a.label, a.position, SUM(l.credit) AS credit, SUM(l.debit) AS debit,
			SUM(l.credit - l.debit) %s AS sum
			FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction
			INNER JOIN %s a ON a.id = l.id_account
			WHERE %s
			GROUP BY l.id_account

			%s
			ORDER BY %s;',
			$reverse, Line::TABLE, Transaction::TABLE, Account::TABLE, $where, $remove_zero, $order);
		return DB::getInstance()->getGrouped($sql);
	}

	static public function getTrialBalance(array $criterias): array
	{
		return self::getClosingSumsWithAccounts($criterias, null, false, false);
	}

	static public function getBalanceSheet(array $criterias): array
	{
		$out = [
			Account::ASSET => [],
			Account::LIABILITY => [],
			'sums' => [

Modified src/www/admin/acc/reports/trial_balance.php from [a483e23c57] to [c1cd5c7e43].

2
3
4
5
6
7
8
9
10
11

namespace Garradin;

use Garradin\Accounting\Reports;

require_once __DIR__ . '/_inc.php';

$tpl->assign('balance', Reports::getClosingSumsWithAccounts($criterias));

$tpl->display('acc/reports/trial_balance.tpl');







|


2
3
4
5
6
7
8
9
10
11

namespace Garradin;

use Garradin\Accounting\Reports;

require_once __DIR__ . '/_inc.php';

$tpl->assign('balance', Reports::getTrialBalance($criterias));

$tpl->display('acc/reports/trial_balance.tpl');