53
edits
FAQ Custom reporting script EN: Difference between revisions
Jump to navigation
Jump to search
no edit summary
(Created page with "{{TemplateDocUser}} Category:FAQ EN The standard reporting in Dolibarr did not suit my needs so I created a custom one using a bash script. <code> #!/bin/bash...") |
No edit summary |
||
| Line 2: | Line 2: | ||
[[Category:FAQ EN]] | [[Category:FAQ EN]] | ||
The standard reporting in Dolibarr did not suit my needs so I created a custom one using a bash script. | The standard reporting in Dolibarr did not suit my needs so I created a custom one using a bash script. To use and adapt this script you should familiarize yourself with the columns of the table llx_accounting_bookkeeping as this is where your ledger is stored. | ||
< | Before running this script, make sure you have committed all your transactions to the journal (Journalization). | ||
This outputs the total sale of year 2019 before taxes: | |||
<pre> | |||
#!/bin/bash | #!/bin/bash | ||
DATE="2019" | DATE="2019" | ||
| Line 10: | Line 14: | ||
PSQL="psql -d dolibarr -U dolibarr -t -c" | PSQL="psql -d dolibarr -U dolibarr -t -c" | ||
c3000="select sum(credit) from llx_accounting_bookkeeping where code_journal = \ | c3000="select sum(credit) from llx_accounting_bookkeeping where code_journal = \ | ||
'short_code_for_sell_journal' and numero_compte = ' | 'short_code_for_sell_journal' and numero_compte = 'sales_account_number' and doc_date <= '12/31/$DAT' and doc_date >= '\ | ||
01/01/$DAT'" | 01/01/$DAT'" | ||
echo Report for year $AAR | echo Report for year $AAR | ||
| Line 16: | Line 20: | ||
echo -n "Total sales before taxes:" | echo -n "Total sales before taxes:" | ||
$PSQL "$c3000" | $PSQL "$c3000" | ||
</ | </pre> | ||
On linux with postgres installed I do this in a terminal: | |||
# login as postgres | |||
sudo su postres | |||
# save the script to a file where postgres can write | |||
cd ~ && nano report.sh (paste the content and adapt to your database) | |||
# give run permissions | |||
chmod +x report.sh | |||
# run the script | |||
./report.sh | |||
Adjust short_code_for_sell_journal (defaults to VT) and the DATE/DAT to the year you want. | Adjust short_code_for_sell_journal (defaults to VT), sales_account_number and the DATE/DAT to the year you want. | ||
This can be expanded very easily to output very long and detailed reports. | This can be expanded very easily to output very long and detailed reports. | ||