forked from brmlab/brmbar-github
64 lines
2.8 KiB
Text
64 lines
2.8 KiB
Text
How to "reset" the database - drop all history and keep only accounts with non-zero balance.
|
|
|
|
Legend:
|
|
> - SQL commands
|
|
$ - shell commands
|
|
|
|
Run the (full) inventory.
|
|
|
|
Get number of the first inventory TX.
|
|
|
|
> select id from account_balances where id in (select id from accounts where currency not in (select distinct currency from
|
|
transaction_nicesplits where transaction >= NUMBER_HERE and currency != 1 and memo like '%Inventory fix%') and acctype = 'inventory') and crbalance != 0 \g 'vynulovat'
|
|
$ ./brmbar-cli.py inventory `cat vynulovat | while read x; do echo $x 0; done`
|
|
|
|
Backup the database
|
|
$ pg_dump brmbar > backup.sql
|
|
|
|
Dump "> SELECT * FROM account_balances;" to file N.
|
|
|
|
Dump inventory to file nastavit FIXME.
|
|
|
|
Drop all transactions:
|
|
> delete from transaction_splits;
|
|
> delete from transactions;
|
|
|
|
Restore inventory:
|
|
$ cat nastavit | while read acc p amt; do ./brmbar-cli.py inventory $acc `echo $amt | grep -oE "^[0-9-]+"`; done
|
|
|
|
Restore cash balance:
|
|
$ cat N | grep debt | tr -s " " |cut -d \| -f 2,4 | while read acc p amt; do ./brmbar-cli.py changecredit $acc `echo $amt | grep -oE "^[0-9-]+"`; done
|
|
|
|
Delete zero-balance accounts:
|
|
> delete from accounts where accounts.id not in (select id from account_balances);
|
|
|
|
Delete orphaned barcodes:
|
|
> delete from barcodes where barcodes.account not in (select id from account_balances);
|
|
|
|
Delete orphaned currencies and exchange rates:
|
|
> CREATE OR REPLACE VIEW "a_tmp" AS
|
|
SELECT ts.account AS id, accounts.name, accounts.acctype, accounts.currency AS fff, (- sum(CASE WHEN (ts.side = 'credit'::transaction_split_side) THEN (- ts.amount) ELSE ts.amount END)) AS crbalance FROM (transaction_splits ts LEFT JOIN accounts ON ((accounts.id = ts.account))) GROUP BY ts.account, accounts.name, accounts.id, accounts.acctype ORDER BY (- sum(CASE WHEN (ts.side = 'credit'::transaction_split_side) THEN (- ts.amount) ELSE ts.amount END));
|
|
|
|
> delete from exchange_rates where source not in (select fff from a_tmp);
|
|
> delete from currencies where id not in (select fff from a_tmp);
|
|
|
|
> DROP VIEW "a_tmp";
|
|
|
|
Drop obsolete exchange rates:
|
|
|
|
> delete from exchange_rates where
|
|
valid_since <> (SELECT max(valid_since)
|
|
FROM exchange_rates e
|
|
WHERE e.target = exchange_rates.target and e.source = exchange_rates.source)
|
|
|
|
Restore system accounts:
|
|
> INSERT INTO "accounts" ("name", "currency", "acctype", "active")
|
|
VALUES ('BrmBar Profits', '1', 'income', '1');
|
|
> INSERT INTO "accounts" ("name", "currency", "acctype", "active")
|
|
VALUES ('BrmBar Excess', '1', 'income', '1');
|
|
> INSERT INTO "accounts" ("name", "currency", "acctype", "active")
|
|
VALUES ('BrmBar Deficit', '1', 'expense', '1');
|
|
> INSERT INTO "accounts" ("name", "currency", "acctype", "active")
|
|
VALUES ('BrmBar Cash', '1', 'cash', '1');
|
|
|
|
Restart brmbar.
|