Retrospective Aged Debtor Report

[Jolo35]

I want to run the aged debtor report as at 31/12/2019 so I can compare Blesta to our accounting system. 

Can anyone tell me how to create this custom report?

[Tyson]

You can take a look at the documentation on generating custom reports in Blesta. Knowledge of SQL will be necessary, as well as the ability to find the information you need from the database schema.

While not the same, the Aging Invoices report could be used to identify clients that are late on payment for 30/60/90+ days.

[Jolo35]

Yes i read the Blesta documentation which is an absolute joke. I am OK with SQL but the documentation does not provide a map of the database tables and fields so is completely useless.

Can you point me in the direction of anything that explains how the database is structured or can anyone share their own sql for this type of report?

 

[Tyson]

The documentation is directed toward user and developer Blesta system documentation rather than database schema documentation. We don't currently share any UML or ER diagrams, but there are certainly tools available for viewing database schemas (e.g. phpmyadmin, MySQL Work Bench) that can be used for that purpose.

[Jolo35]

Then your custom reports function is basically useless then.  

[Bloory]

I'd think, having spent just five minutes looking at the problem, the tables you would need are transactions, transaction_applied, transaction_types, invoices and invoice_lines. Probably some customer data.