SQL-Ledger REST API: Get GL Transaction Lines

The first route we’ll be working on will be the route to get all general ledger transaction line data. We will be defining the route as

/gl/transactions/lines

This will essential be fetching the data we get in SQL-Ledger from the menu option “General Ledger>Search”. We will be using the same search params available there in our API as well. These will be

1. Reference
2. Description
3. Company Name
4. Department ID
5. Line Item
6. Source
7. accnofrom
8. accnoto
9. memo
10. datefrom
11. dateto
12. amountfrom
13. notes
14. intnotes
15. accno
16. onhold
17. filter_amounts

(For parameter detail and data-types, please refer to the swagger document.)

SQL-Ledger already has an internal module in “GL.pm” in the SL folder called “transactions”. This is the same module that is called by the screen in SQL-Ledger. We will be making use of this module as well. Our API route is essentially just going to be a wrapper around this module.

$api->get('/:client/gl/transactions/lines' => sub {
    my $c      = shift;
    my $params = $c->req->params->to_hash;
    my $client = $c->param('client');

    $c->slconfig->{dbconnect} = "dbi:Pg:dbname=$client";

    my $form = new Form;
    for ( keys %$params ) { $form->{$_} = $params->{$_} if $params->{$_} }
    $form->{category} = 'X';

    GL->transactions( $c->slconfig, $form );

    # Check if the result is undefined, empty, or has no entries
    if (  !defined($form->{GL})
        || ref($form->{GL}) ne 'ARRAY'
        || scalar(@{ $form->{GL} }) == 0 )
    {
        return $c->render(
            status => 404,
            json   => { error => 
            { message => "No transactions found" },
            }
        );
    }

    # Assuming $form->{GL} is an array reference with hash references
    foreach my $transaction ( @{ $form->{GL} } ) {
        delete $transaction->{$_}
          for
          qw(address address1 address2 city country entry_id name name_id zipcode);
    }

    $c->render( status => 200, json => $form->{GL} );
});

We need to assign “X” to a param category since it’s expected by the transactions module. We also remove some unneeded values from our response. Currently we are not parsing any incoming params to handle and provide descriptive errors but I hope to change this when I start working on the front-end.