Ledger-Contents

SQL-ledger Queries

Frontend Queries

A number of menu items have the ‘Reports’ sub-menu wherein one can query related transactions. The underlying database is PostgreSQL which frames the kind of query language. Unfortunately, this remarkable feature in the frontend is not well-documented in SQL-ledger but one could experiment with those rules that govern ‘general expressions’.

One of the most useful characters is the percent symbol,‘%’ which acts a wildcard. Most of the ‘Reports’ sub-menus also have a date scope. This writer finds putting in the beginning and enddate most accurate but one can also choose the date choices of ‘current’, ‘month, etc.

Note: Where Invoices/Vendor Bills are concerned, one needs to check the “closed” box as well as “open” box if one wishes have a full query. The queries are case-insensitive.

Here is a partial list of example queries:

The Backend - SQL Query/An Example

As SQL-ledger is based on the powerful PostgreSQL database, it is possible to query the actual database to extract information using a Cpanel such as Webmin. Here below is an example query that extracts detailed sales information that can be copy-pasted into a spreadsheet.

Here below is the two part process: a “view” and a SQL query based on that view to extract sales information.

a. The View “inv_info”

Open Webmin and goto: Servers→ PostgreSQL Database Server

Create view inv_info as
Select distinct a.invnumber as Inv, a.transdate, ct.customernumber as Cust, ct.name, p.partnumber as Part, i.description, i.qty, p.unit, i.sellprice, (i.qty*i.sellprice)::numeric(8,0) as Subtotal
from invoice i, ar a, parts p, customer ct
where a.id = i.trans_id
and p.id = i.parts_id
and a.customer_id = ct.id
and i.sellprice >0;

Here is the above as a text file: Create-View-SQL.txt

Explanation of the View
It has roughly three sections:
The first section (up to the word “from”) states the 9 columns of data that will be display in the rows of the result. Note that one column “Subtotal” is calculated by the invoice item quantity (ĭ.qty) by its sell price (ĭ.sellprice) and is formatted to give a number with a maximum 8 places but no digits.
The second part: the single “from” line queries the following four tables: invoice, ar, parts and customer which are tagged ĭ, a, p and ct respectively. From these tables, data is called from there respective “columns” when associated with the table tags (eg ct.customernumber gets the customer code).
The third section sets the conditions. The tables are joined by three conditions:
where a.id = i.trans_id
and p.id = i.parts_id
and a.customer_id = ct.id
then we eliminate any invoice entries where the sell price is zero or negative:
and i.sellprice >0;
This view is embedded into the database via the SQL-query (Webmin->Servers->PostgreSQL->[Choose the database]->Execute SQL.

Note that the restore process does not preserve views. If a database uses a view, it must be embedded again when restored.

b. The Related SQL Query

Enter the ‘Execute SQL’ space as found in the description above.
Herein is the query base on the view “info_inv”. Change the date accordingly and copy-paste the command below:

select distinct cust, inv, part, qty, unit, subtotal
from inv_info
where transdate between '2020-07-01' and '2020-07-31';

Here is the above as a text file: Monthly-Sales-Query-SQL.txt

Explanation of the Query
Again there are three sections to the query:
The first section (up to the word “from”) specifies the rows of the table results.
The second section calls the view “info_inv” as the basis of the query.
The third section sets the conditions; that is the time period in days for which the query will collect data. The above example is for the month of November 2018.
This query activated via Webmin->Servers->PostgreSQL->[Choose the database]->Execute SQL: paste in query with the desired date period, execute it and a resulting table will be displayed within the browser. This information can be selected/highlighted then copy-pasted into a spreadsheet.