Difference between revisions of "FAQ Custom reporting script EN"
Jump to navigation
Jump to search
(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...") |
|||
| (4 intermediate revisions by the same user not shown) | |||
| 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 script was run on a Dolibarr 9.0.1 install, you might have to adapt it if using another version e.g. if the table or column names have changed. |
| + | |||
| + | This outputs the total sale of year 2019 before taxes: | ||
| + | |||
| + | <pre> | ||
#!/bin/bash | #!/bin/bash | ||
DATE="2019" | DATE="2019" | ||
DAT="19" | DAT="19" | ||
PSQL="psql -d dolibarr -U dolibarr -t -c" | PSQL="psql -d dolibarr -U dolibarr -t -c" | ||
| − | + | SALES="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 | ||
echo | echo | ||
echo -n "Total sales before taxes:" | echo -n "Total sales before taxes:" | ||
| − | $PSQL "$ | + | $PSQL "$SALES" |
| − | </ | + | </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 adjust the short_code_for_sell_journal (defaults to VT), sales_account_number and the DATE/DAT to the year you want) | ||
| + | # give run permissions | ||
| + | chmod +x report.sh | ||
| + | # run the script | ||
| + | ./report.sh | ||
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. | ||
| + | == Links == | ||
| + | * [https://gitlab.com/snippets/1955302 Snippet with a localized script for gathering information for Swedish tax reports] | ||
Latest revision as of 18:10, 20 March 2020
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 script was run on a Dolibarr 9.0.1 install, you might have to adapt it if using another version e.g. if the table or column names have changed.
This outputs the total sale of year 2019 before taxes:
#!/bin/bash DATE="2019" DAT="19" PSQL="psql -d dolibarr -U dolibarr -t -c" SALES="select sum(credit) from llx_accounting_bookkeeping where code_journal = \ 'short_code_for_sell_journal' and numero_compte = 'sales_account_number' and doc_date <= '12/31/$DAT' and doc_date >= '\ 01/01/$DAT'" echo Report for year $AAR echo echo -n "Total sales before taxes:" $PSQL "$SALES"
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 adjust the short_code_for_sell_journal (defaults to VT), sales_account_number and the DATE/DAT to the year you want)
- give run permissions
chmod +x report.sh
- run the script
./report.sh
This can be expanded very easily to output very long and detailed reports.