Cotrugli Application Program Interface

Summary

Cotrugli is an experimental accounting application written in GNU-APL that uses PostgreSQL to manage its database.

At this writing, Seventh Month 2021, it will create a trial balance and allow one to adjust that that trial balance using an application program interface (API). Users well versed in GNU-APL can enter and adjust that trial balance by using the API.

There is much work to be done and I am looking for volunteers.

Data Model

Cotrugli stores its data in a PostgreSQL database and uses GNU APL's library 5 SQL workspace. At the start of a session one should make the connection to PostgreSQL and store the database handle against need.

database_handle←ctrgl_sql_connect server username dbname password

The data model consists of six tables:

  • Configuration
  • Journal
  • Company
  • Period
  • Chart of Accounts
  • Documents

    • Configuration

    The configuration table is a collection of name -- value pairs used throughout the system

    Journal

    Accountants use Journals to group entries generated by specific transaction cycles. Examples of such cycles are Sales, Cash Receipts, and Cash Disbursements. In hand posted systems each journal was a spreadsheet organized to simplify entry.

    Although several journals are included in the examples the API supports only the General Journal.

    Fields in the Journal table are:

    Company

    Cotrugli allows recording the transactions of multiple companies and this table defines those companies.

    Fields in the Company table are:

    Periods

    Accounting transactions are stored by period and trial balances are prepared as of the end of a period. Each company has its own set of periods.

    Fields in the Periods table are:

    Chart of accounts

    A chart of accounts is a list of the categories that can be used to accumulate the totals of transactions. Each company has a separate chart of accounts. Each account is defined with four data:

  • Account number
  • Title
  • account type (one of b, r, or i)
  • sign type (one of d or c)

    • Account numbers are used to provide a logical order to the accounts and title describe what should be included in an account.

    An account should be one of three types:

  • Balance sheet or 'b'
  • Income or 'i'
  • Retained Earnings or 'r'

    • The basic accounting equation is Assets are equal to Liabilities plus Equity and a Balance Sheet displays that requirement. That is it first lists and totals the assets, then lists and totals the liabilities, then lists and totals the equity accounts and finally totals liabilities and equity.

    Retained Earnings is a special equity account that is posted as the final document of the year with the total of the income accounts. The income accounts are then reset to zero.

    The balance in each account is displayed as either a positive or negative number based on where it falls in the basic accounting equation.

    Assets show positive balances when the debits exceed the credits. Liabilities and Equity show positive balance when the credits exceed the debits.

    For income accounts revenue (which increases equity) shows a positive credit balance, while expense (which decrease equity) show a positive debit balance.

    This leads us to the account's sign type. It is one of 'd' for debit or 'c' for credit.

    These two flags should therefore be set as follows

    Accounting Equation acct_type sign_type
    Asset b d
    Liability b c
    Equity b c
    Retained earnings r c
    Revenue i c
    Expense i d

    Document

    A document is the basic building block of cotrugli and should be used to record each transaction (a check or an invoice for example) each document consists of a header that has information about the transaction and a body which records the financial effect of the transaction. Cotrugli is a double entry accounting system. That is each document must contain at lease two lines and any amount is identified as either a debit or a credit. The total of the debit lines must equal the total of the credit lines.

    There are in fact two database tables document and doc_lines. Document fields are:

    Doc_lines fields are:

    Reports

    There is a group of functions which return a report. Cotrugli uses APL Library's wp workspace for reports and these function return a wp instance. wp∆txt∆assemble returns a text report suitable for display in a terminal window while wp∆html∆assemble returns an HTML page.

    Reporting functions begin with 'ctrgl_open' and can be listed with

    )fns ctrgl_open ctrgl_open_account ctrgl_open_chart ctrgl_open_company ctrgl_open_config ctrgl_open_journals ctrgl_open_periods ctrgl_open_tb

    ctrgl_open_config

    Function returns a report of configuration name--value pairs

    ⍞←wp∆txt∆assemble ctrgl_open_config database_handle

    ctrgl_open_company

    Functions returns a report of defined companies

    ⍞←wp∆txt∆assemble ctrgl_open_company database_handle

    ctrgl_open_periods

    Function prepares a workpaper showing the periods defined for a company.

    ⍞←wp∆txt∆assemble database_handle ctrgl_open_periods company_code

    ctrgl_open_journals

    Function prepares a workpaper showing the defined periods.

    ⍞←wp∆txt∆assemble ctrgl_open_journals database_handle

    ctrgl_open_chart

    Function returns a workpaper showing a company's chart of accounts.

    ⍞←wp∆txt∆assemble database_handle ctrgl_open_chart company

    ctrgl_open_tb

    Function returns a trial balance workpaper

    ⍞←wp∆txt∆assemble database_handle ctrgl_open_tb company period

    ctrgl_open_account

    Function returns a report showing the transactions that have been posted to an account.

    ⍞←wp∆txt∆assemble database_handle ctrgl_open_account company period acct_no

    Maintenance

    Config table

    ctrgl_config_post

    Function to post a name -- value pair to the config table.

    database_handle ctrgl_config_post name value

    Company table

    ctrgl_company_post

    Function posts a company to the company table

    database_handle ctrgl_company_post company_code company_name

    Period table

    ctrgl_period_post

    Function posts a period to the period table.

    database_handle ctrgl_period_post company period begin_date end_date

    Journal table

    ctrgl_jrnl_post

    Function to create or update a journal.

    database_handle ctrgl_jrnl_post journal_code journal_name

    Chart of Accounts

    ctrgl_chart_fetch

    Function returns a record from the chart of accounts.

    acct←database_handle ctrgl_chart_fetch company acct_no

    ctrgl_chart_post

    Function posts an account to the chart of accounts. Existing account will be overwritten.

    database_handle ctrgl_chart_post company acct_no title acct_type sign_type

    Document

    ctrgl_doc_init

    Function creates a document from a vector of company, journal, name, date, description and period.

    doc←ctrgl_doc_init doc_hd company journal name date description period

    ctrgl_doc_debit

    Function adds or replaces a debit line to a document. The descision to add or replace is based on the account number.

    doc←doc ctrgl_doc_debit acct_no debit

    ctrgl_doc_credit

    Function adds or replaces a credit line to a document.

    doc←doc ctrgl_doc_credit acct_no credit

    ctrgl_doc_newLine

    Function appends a new line or replaces and old line (based on the account account number) in a document. It is called by ctrgl_doc_debit and ctrgl_doc_credit.

    A document line is a vector of doc_id (assigned by the system), line number, account number, debit, and credit.

    ctrgl_doc_newLine may be used in lieu of ctrgl_doc_debit or ctrgl_doc_credit. ctrgl_doc_post will populate the doc_id fields in the database.

    doc←doc ctrgl_doc_newLine 0 line_no acct_no debit credit

    ctrgl_doc_delLine

    Function removes the line in the document for the given account number.

    doc←doc ctrgl_doc_delLine acct_no

    ctrgl_doc_post

    Function posts a document to the database.

    database_handle ctrgl_doc_post doc

    ctrgl_doc_show

    Function returns the document as a text vector suitable for printing. The function looks up the account titles in the database and a handle must be supplied.

    ⍞←database_handle ctrgl_doc_show doc