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: |
f526f4c6fbc6f14d9036d68878f5ba7c |
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 | $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); | | | 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 | $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 | > > > > > > > > > > > > > > > > | | | | | | 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; } |
︙ | ︙ |