53
edits
FAQ Custom reporting script EN: Difference between revisions
Jump to navigation
Jump to search
no edit summary
No edit summary |
No edit summary |
||
| (3 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
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. | 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). | 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: | This outputs the total sale of year 2019 before taxes: | ||
| Line 13: | Line 13: | ||
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 = 'sales_account_number' and doc_date <= '12/31/$DAT' and doc_date >= '\ | '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'" | ||
| Line 19: | Line 19: | ||
echo | echo | ||
echo -n "Total sales before taxes:" | echo -n "Total sales before taxes:" | ||
$PSQL "$ | $PSQL "$SALES" | ||
</pre> | </pre> | ||
| Line 26: | Line 26: | ||
sudo su postres | sudo su postres | ||
# save the script to a file where postgres can write | # save the script to a file where postgres can write | ||
cd ~ && nano report.sh (paste the content and | 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 | # give run permissions | ||
chmod +x report.sh | chmod +x report.sh | ||
# run the script | # run the script | ||
./report.sh | ./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] | |||