Difference between revisions of "FAQ Custom reporting script EN"

From Dolibarr ERP CRM Wiki
Jump to navigation Jump to search
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 adapt to your database)
+
   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
 
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.

Revision as of 17:13, 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 outputs the total sale of year 2019 before taxes:

#!/bin/bash                                                                     
DATE="2019"
DAT="19"
PSQL="psql -d dolibarr -U dolibarr -t -c"
c3000="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 "$c3000"

On linux with postgres installed I do this in a terminal:

  1. login as postgres
sudo su postres
  1. 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)
  1. give run permissions
 chmod +x report.sh
  1. run the script
 ./report.sh

This can be expanded very easily to output very long and detailed reports.