Overview
Comment:Fix year comparison of projects where the mentioned year does not have anything related to this project
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA3-256: f526f4c6fbc6f14d9036d68878f5ba7c6b9ee26360ac4e30f6b0aed1839efda6
User & Date: bohwaz on 2023-01-07 21:50:40
Other Links: manifest | tags
Context
2023-01-07
21:56
Fix wording in [af9a62852c249e89a98d7ab18c267b6feef54ccb] check-in: 6758017e8a user: bohwaz tags: trunk, stable
21:50
Fix year comparison of projects where the mentioned year does not have anything related to this project check-in: f526f4c6fb user: bohwaz tags: trunk, stable
21:22
Fix appearance of columns when comparing years check-in: f71d7bfa05 user: bohwaz tags: trunk
Changes

Modified src/include/lib/Garradin/Accounting/Reports.php from [31f657f6c2] to [9a3d384a70].

175
176
177
178
179
180
181


182
183
184
185
186
187
188
		}
		else {
			$where = self::getWhereClause($criterias);
			$sql = sprintf('SELECT position, SUM(balance) FROM acc_accounts_balances WHERE %s GROUP BY position;', $where);
		}

		$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, %s, is_debt







>
>







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
		}
		else {
			$where = self::getWhereClause($criterias);
			$sql = sprintf('SELECT position, SUM(balance) FROM acc_accounts_balances WHERE %s GROUP BY position;', $where);
		}

		$balances = DB::getInstance()->getAssoc($sql);

		//var_dump('<pre>', $sql, $balances[Account::REVENUE]); exit;

		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, %s, is_debt
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
			$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
				ORDER BY %s';

			$sql = sprintf($query, $table, $where, $group, $having, $order);
		}








|







273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
			$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 id_year, id, label, code, type, SUM(debit) AS debit, SUM(credit) AS credit, position, SUM(balance) AS balance, is_debt FROM %s
				WHERE %s
				GROUP BY %s %s
				ORDER BY %s';

			$sql = sprintf($query, $table, $where, $group, $having, $order);
		}

300
301
302
303
304
305
306
















307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
		$sql = self::getAccountsBalancesInnerSQL($criterias, $order, $remove_zero);

		// SQLite does not support OUTER JOIN yet :(
		if (isset($criterias['compare_year'])) {
			$criterias2 = array_merge($criterias, ['year' => $criterias['compare_year']]);
			$sql2 = self::getAccountsBalancesInnerSQL($criterias2, $order, true);

















			$sql_union = 'SELECT a.id, a.code AS code, a.label, a.position, a.type, a.debit, a.credit, a.balance, IFNULL(b.balance, 0) AS balance2, IFNULL(a.balance - b.balance, a.balance) AS change
				FROM (%1$s) AS a
				LEFT JOIN %3$s b ON b.code = a.code AND a.position = b.position AND b.id_year = %4$d
				UNION ALL
				-- Select balances of second year accounts that are =zero in first year
				SELECT
					NULL AS id, c.code AS code, c.label, c.position, c.type, c.debit, c.credit, 0 AS balance, c.balance AS balance2, c.balance * -1 AS change
				FROM (%2$s) AS c
				LEFT JOIN %3$s d ON d.code = c.code AND d.balance != 0 AND d.position = c.position AND d.id_year = %5$d
				WHERE d.id IS NULL
				ORDER BY code COLLATE NOCASE;';

			$sql = sprintf($sql_union, $sql, $sql2, 'acc_accounts_balances', $criterias['compare_year'], $criterias['year']);
		}

		$out = $db->get($sql);

		return $out;
	}








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|
|




|
|



|







302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
		$sql = self::getAccountsBalancesInnerSQL($criterias, $order, $remove_zero);

		// SQLite does not support OUTER JOIN yet :(
		if (isset($criterias['compare_year'])) {
			$criterias2 = array_merge($criterias, ['year' => $criterias['compare_year']]);
			$sql2 = self::getAccountsBalancesInnerSQL($criterias2, $order, true);

			// Create temporary tables to store data, so that the request is not too complex
			// and doesn't require to do the same SELECTs twice or more
			$table_name = md5(random_bytes(10));
			$db->begin();
			$db->exec(sprintf('
				CREATE TEMP TABLE acc_compare_a_%1$s (id_year, id, label, code, type, debit, credit, position, balance, is_debt);
				CREATE TEMP TABLE acc_compare_b_%1$s (id_year, id, label, code, type, debit, credit, position, balance, is_debt);
				INSERT INTO acc_compare_a_%1$s %2$s;
				INSERT INTO acc_compare_b_%1$s %3$s;',
				$table_name, $sql, $sql2));
			$db->commit();

			// The magic!
			// Here we are selecting the balances of year A, joining with year B
			// BUT to show the accounts used in year B but NOT in year A, we need to do this
			// UNION ALL to select accounts from year B which are NOT in year A
			$sql_union = 'SELECT a.id, a.code AS code, a.label, a.position, a.type, a.debit, a.credit, a.balance, IFNULL(b.balance, 0) AS balance2, IFNULL(a.balance - b.balance, a.balance) AS change
				FROM acc_compare_a_%1$s AS a
				LEFT JOIN acc_compare_b_%1$s AS b ON b.code = a.code AND a.position = b.position AND b.id_year = %2$d
				UNION ALL
				-- Select balances of second year accounts that are =zero in first year
				SELECT
					NULL AS id, c.code AS code, c.label, c.position, c.type, c.debit, c.credit, 0 AS balance, c.balance AS balance2, c.balance * -1 AS change
				FROM acc_compare_b_%1$s AS c
				LEFT JOIN acc_compare_a_%1$s AS d ON d.code = c.code AND d.balance != 0 AND d.position = c.position AND d.id_year = %3$d
				WHERE d.id IS NULL
				ORDER BY code COLLATE NOCASE;';

			$sql = sprintf($sql_union, $table_name, $criterias['compare_year'], $criterias['year']);
		}

		$out = $db->get($sql);

		return $out;
	}