FAQ Custom reporting script EN - Revision history

Egils at 18:10, 20 March 2020

2020-03-20T18:10:22Z

← Older revision		Revision as of 18:10, 20 March 2020
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:

Egils: snippet

2020-03-20T18:08:30Z

snippet

← Older revision		Revision as of 18:08, 20 March 2020
Line 33:		Line 33:

	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]

Egils at 17:14, 20 March 2020

2020-03-20T17:14:44Z

← Older revision		Revision as of 17:14, 20 March 2020
Line 13:		Line 13:
	DAT="19"		DAT="19"
	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 = \		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 "$~~c3000~~"		$PSQL "$SALES"
	</pre>		</pre>

Egils at 17:13, 20 March 2020

2020-03-20T17:13:40Z

← Older revision		Revision as of 17:13, 20 March 2020
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.

Egils at 17:12, 20 March 2020

2020-03-20T17:12:21Z

← Older revision		Revision as of 17:12, 20 March 2020
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.

	<~~code~~>		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 = '~~3000~~' 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'"
	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"
	</~~code~~>		</pre>

	~~This outputs~~ the ~~total sale of year 2019 before taxes~~.		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.

Egils: 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. `#!/bin/bash..."`


2020-03-20T17:04:16Z
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..."
New page
{{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                                                                     

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 = '3000'  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"

</code>



This outputs the total sale of year 2019 before taxes. 



Adjust short_code_for_sell_journal (defaults to VT) and the DATE/DAT to the year you want.



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

FAQ Custom reporting script EN - Revision history

Egils at 18:10, 20 March 2020

Egils: snippet

Egils at 17:14, 20 March 2020

Egils at 17:13, 20 March 2020

Egils at 17:12, 20 March 2020

Egils: 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. #!/bin/bash..."

Egils: 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. `#!/bin/bash..."`