#! /usr/local/bin/apl --script
⍝ ********************************************************************
⍝ APL workspace provies a bookkeeping system for a single company
⍝ Copyright (C) 2024 Bill Daly

⍝ This program is free software: you can redistribute it and/or modify
⍝ it under the terms of the GNU General Public License as published by
⍝ the Free Software Foundation, either version 3 of the License, or
⍝ (at your option) any later version.

⍝ This program is distributed in the hope that it will be useful,
⍝ but WITHOUT ANY WARRANTY; without even the implied warranty of
⍝ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
⍝ GNU General Public License for more details.

⍝ You should have received a copy of the GNU General Public License
⍝ along with this program.  If not, see <http://www.gnu.org/licenses/>.

⍝ ********************************************************************

)copy_once 5 SQL
)copy_once 5 DALY/wp
)copy_once 3 DALY/date
)copy_once 5 DALY/cl

⍝ ********************************************************************
⍝ Integrity checking
⍝ ********************************************************************
∇b←handle ctrgl_check_account acct_no;co;acct;cmd
  ⍝ Function confirms an acct is defined in the accounts table.The
  ⍝ right argument is the account number.
  →(~b←utl∆stringp acct_no)/0	⍝ accounts as numbers not acceptable. 
  cmd←'SELECT acct_no from accounts where acct_no = ''',acct_no,''''
  b←0≠1↑⍴cmd SQL∆Select[handle] ⍬
∇

∇b←handle ctrgl_check_period pd;cmd
  ⍝ Function confirms that the period is defined. The right argument
  ⍝ is a nested vector of company and period.
  cmd←'SELECT period from periods where period = ''',pd,''''
  b←0≠1↑⍴cmd SQL∆Select[handle] args
∇

∇b←handle ctrgl_check_journal jrnl;cmd
  ⍝ Function confirms that a journal is defined.
  cmd←'SELECT jrnl FROM journal where jrnl = ''',jrnl,''''
  b←0≠1↑⍴cmd SQL∆Select[handle] ''
∇

∇id←handle ctrgl_check_doc head;cmd;co;nm;pd
  ⍝ Function returns the document id. Documents are
  ⍝ defined by the journal, name, and period.
   jr←1⊃head ◊ nm←2⊃head  ◊ pd←3⊃head
  cmd←'SELECT doc_id FROM document WHERE journal = ? and name = ? and period = ?'
  id←⍬⍴cmd SQL∆Select[handle] co jr nm pd
∇
⍝ ********************************************************************
⍝ Functions to implement the sql interface
⍝ ********************************************************************

∇handle←ctrgl_sql_connect args;constr
  ⍝ Function to connect to the postgres server. Right argument is a
  ⍝ vector of host, user, dbname, and password.
  constr←'host=',(1⊃args),' user=',(2⊃args),' dbname=',(3⊃args),' password=',4⊃args
  handle←'postgresql' SQL∆Connect constr
∇

∇ctrgl_sql_disconnect handle
  ⍝ Function to disconnect from the postgres server
  SQL∆Disconnect handle
∇

∇ctrgl_sql_rollback handle
  ⍝ Functions rolls back the current sql transaction
  SQL∆Rollback handle
∇

∇rs←ctrgl_sql_escape_quotes txt;loc
  ⍝ Function returns the text with single quotes escaped for
  ⍝ postgresql
  rs←txt
  →(∧/~txt='''')/0		⍝ Nothing to do
  loc←+/1,∧\''''≠txt
  rs←(loc↑rs),'''',ctrgl_sql_escape_quotes loc↓txt
∇

∇data←handle ctrgl_sql_tb period;cmd
  ⍝ Function returns a trial balance array. Right argument is a nested
  ⍝ vector of company and period.
    cmd←'SELECT acct, title, '
  cmd←cmd,'CASE WHEN dr > cr then dr - cr else 0 end as debit, '
  cmd←cmd,'CASE WHEN cr > dr then cr - dr else 0 end as credit, '
  cmd←cmd,'acct_type, sign_type from tb join accounts '
  cmd←cmd,'on tb.co = accounts.company and tb.acct = accounts.acct_no '
  cmd←cmd,'where co = ''',company, ''' and period =''',period,''' order by acct'
  data←cmd SQL∆Select[handle] ''
  ⍎(1=⍴⍴data)/'data←0 6⍴data'
∇

⍝ ********************************************************************
⍝ Maintain the config table
⍝ ********************************************************************

∇ handle ctrgl_config_post args;cmd;name;value;rs
  ⍝ Function to post a name -- value pair to the config table.
  name←1⊃args ◊ value←2⊃args
  cmd←'SELECT EXISTS(SELECT trim(name) FROM config WHERE name = ''',name,''')'
  →('t'=''⍴⊃rs←cmd SQL∆Select[handle] '')/replace
insert:
  cmd←'INSERT INTO config (name,value) VALUES (?,?)'
  cmd SQL∆Exec[handle] name value
  →0
replace:
  cmd←'UPDATE config SET value = ? WHERE name = ?'
  cmd SQL∆Exec[handle] value name
  →0
∇

⍝ ********************************************************************
⍝ Maintain the period table
⍝ ********************************************************************

∇ msg←cth ctrgl_period_post_editchecks pd;name;begin;end;ye;dtest;bv
  ⍝ Function to post a period to the database.
  (name begin end ye)←pd
  msg←''
  ⍎(~utl∆stringp name)/'msg←''The period name must be a character string. ◊ →0'''
  dtest←(⊂date∆US) date∆parse ¨ begin end
  ⍎(∨/bv)/'msg←',((bv←utl∆stringp ¨ dtest)/dtest),' is not a date ◊ →0'
  ⍎(~</date∆lillian ¨ dtest)/'msg←''The begining data must be less than the ending'' ◊ →0'
  ⍎(~utl∆numberp ye)/'msg←''Year-end is either true (1) or false (0).'' ◊ →0'
  ⍎(∧/1 0 ≠ ye←''⍴ye)/'msg←''Year-end is either true (1) or false (0).'' ◊ →0'
∇

∇handle ctrgl_period_post pd;cmd;name;begin;end;ye;msg
⍝ Function fo post a period.  A period is the name, begining
⍝ date, ending date and year end flag.
⍎(0≠⍴msg←handle ctrgl_period_post_editchecks pd)/'⍞←msg,⎕tc[3] ◊ →0'
(name begin end ye)←pd
ye←'FT'[⎕io+ye]
cmd←'SELECT period FROM periods WHERE period = ''',name,''''
→(0≠⍴cmd SQL∆Select[handle] '')/replace
insert:
cmd←'INSERT INTO periods (period, begin_date, end_date, year_end) VALUES ('
cmd←cmd,'''',name,''','
cmd←cmd,'''',begin,''','
cmd←cmd,'''',end,''','
cmd←cmd,'''',ye,''')'
msg←cmd SQL∆Exec[handle] ''
→0
replace:
cmd←'UPDATE periods set begin_date = ''',begin
cmd←cmd,''', end_date = ''',end,''', year_end = ''',ye,''''
cmd←cmd,' WHERE period = ''',name,''''
msg←cmd SQL∆Exec[handle] ''
→0
∇

∇ begin←handle ctrgl_period_begin pd;cmd
  ⍝ Function returns the date a period begins
  cmd←'SELECT begin_date FROM periods WHERE period = ''',pd,''''
  begin←,⊃cmd SQL∆Select[handle] ''
∇

∇ end←handle ctrgl_period_end pd;cmd
  ⍝ Functionreturns the date a period ends
  cmd←'SELECT end_date FROM periods WHERE period = ''',pd,''''
  end←,⊃cmd SQL∆Select[handle] ''
∇

∇last←handle ctrgl_period_prev pd;cmd;rs;begin
  ⍝ Function returns the previous period for the company, period
  ⍝ provided. 
  rs←handle ctrgl_period_begin pd
  begin←¯1 + date∆lillian date∆US date∆parse rs
  begin←date∆US date∆fmt∆3numbers date∆unlillian begin
  cmd←'SELECT period FROM periods where end_date = ''',begin,''''
  last←,⊃cmd SQL∆Select[handle] ''
∇

∇next←handle ctrgl_period_next pd;cmd;raw;begin
  ⍝ Function returns the next period of a company.
  raw←handle ctrgl_period_end pd
  begin←1 + date∆lillian date∆US date∆parse raw
  begin←date∆US date∆fmt∆3numbers date∆unlillian begin
  cmd←'SELECT period FROM periods WHERE begin_date = ''',begin,''''
  next←,⊃cmd SQL∆Select[handle] ''
∇

∇b←handle ctrgl_period_yep pd;cmd
  ⍝ Function returns true if the period is the last in a fiscal
  ⍝ year. The right argument  is a vector of company and period.
  cmd←'SELECT year_end from periods where period = ''',pd,''''
  b←'t'=''⍴⊃cmd SQL∆Select[handle] ''
∇


⍝ *******************************************************************
⍝ Maintain the journal table
⍝ ********************************************************************

∇msg←ctrgl_jrnl_post_editCheck args
  ⍝ Function to confirm journal information is useable
  msg←''
  ⍎(~utl∆stringp 1⊃args)/'msg←''The journal code must be a character string.'''
  ⍎(~utl∆stringp 2⊃args)/'msg←''The journal name must be a character string.'''
∇

∇handle ctrgl_jrnl_post args;code;name;cmd;rs;msg
  ⍝ Function to create or update a journal. The right argument is a
  ⍝ nested vector of journal code, journal name.
  ⍎(0≠⍴msg←ctrgl_jrnl_post_editCheck args)/'⍞←msg,⎕tc[3] ◊ ←0'
  code←1⊃args ◊ name←2⊃args 
  ⍝ Test for insertion or replacement
  cmd←'select jrnl from journal where jrnl = ?'
  →(0≠⍴rs←cmd SQL∆Select[handle] ⊂code)/replace
insert:
  cmd←'INSERT INTO journal (jrnl,title) VALUES (?,?)'
  rs←cmd SQL∆Exec[handle] code name 
  →0
replace:
  cmd←'UPDATE journal set title = ''',name,''' WHERE jrnl = ''',code,''''
  rs←cmd SQL∆Exec[handle] ''
  →0
∇

⍝ ********************************************************************
⍝ Functions to maintain the chart of accounts
⍝ ********************************************************************

∇acct←handle ctrgl_chart_fetch args;acctno;cmd
  ⍝ Function returns a record from the chart of accounts. The right
  ⍝ argument is the account number
  cmd←'SELECT title, acct_type, sign_type from accounts',⎕tc[3]
  cmd←cmd,'where acct_no = ''',args,''''
  acct←cmd SQL∆Select[handle] ''
∇

∇msg←handle ctrgl_chart_post_editchecks args;acct_no;title;acct_type;sign_type
  ⍝ Function checks an account entry before posting to the database.
  msg←⍬
  ⍎(4≠⍴args←,args)/'⍞←''The right argument should be acct no, title, acct_type, sign_type''◊→0'
  (acct_no title acct_type sign_type)←args
  msg←'An account consists of a acccount number, title, account type, '
  msg←msg,⎕tc[3],'and sign type'
  →0
more:
  ⍎(0≠⍴⍴acct_type)/'msg←''Account type is one of b, i, or r'' ◊ →0'
  ⍎(~acct_type∊'b' 'i' 'r')/'msg←''Account type is one of b, i, or r'' ◊ →0'
  ⍎(0≠⍴⍴sign_type)/'msg←''Sign type is one of ''d'' or ''c'' ◊ →0'
  ⍎(~sign_type∊'d' 'c')/'msg←''Sign type is one of ''d'' or ''c'' ◊ →0'
  ⍎((utl∆stringp acct_no)∧utl∆numberis acct_no)/'msg←''An account must be the text of a integer'' ◊ →0'
∇

∇ handle ctrgl_chart_post args;acct_no;title;acct_type;sign_type;values
  ⍝ Function posts an account to the chart of accounts. An existing
  ⍝ account will be overwritten. The right argment is a nested vector
  ⍝ of acct_no, title, acct_type, and sign_type
  (acct_no title acct_type sign_type)←args
  ⍎(0=⍴msg← handle ctrgl_chart_post_editchecks args)/'⍞←msg,⎕tc[3] ◊ →0'
  →(handle ctrgl_check_account acct_no)/replace
add:
  values←'(''',acct_no,''',''',title,''',''',acct_type,''',''',sign_type,''')'
  cmd←'INSERT INTO accounts (acct_no, title, acct_type, sign_type) VALUES ',values
  cmd SQL∆Exec[handle] ⍬
  →0
replace:
  cmd←'UPDATE accounts SET title = ''',title,''', acct_type = ''',acct_type,''''
  cmd←cmd,', sign_type = ''',sign_type,''' WHERE acct_no = ''',acct_no,''''
  cmd SQL∆Exec[handle] ⍬
  →0
∇

⍝ ********************************************************************
⍝			      Documents
⍝ ********************************************************************
∇msg←ctrgl_doc_init_editchecks args
  ⍝   ⍝ Function checks the arguments to ctrgl_doc_init and returns an
  ⍝   ⍝ appropriate error message
  msg←⍬
  →(5=⍴args)/more
  msg←'Length error argument must be a five item vector of ',⎕tc[3]
  msg←msg,'journal, name, date, description and period.'
  →0
more:
  ⍎(~utl∆stringp 2⊃args)/'msg←''The journal must be a string of characters.'' ◊ →0'
  ⍎(~utl∆stringp 3⊃args)/'msg←''The document name must be a string of characters.'' ◊ →0'
∇

∇doc← ctrgl_doc_init args;rootNode;flds
  ⍝ Functions creates a document.  args is a vector of five items:
  ⍝ journal, name, date, description, and period.
  utl∆es ctrgl_doc_init_editchecks args
  doc←(⊂0, args),⊂0 5⍴0
∇

⍝ document lines are a vector of document id, line number, account
⍝ number, debit, and credit. 

∇new←doc ctrgl_doc_credit ln
  ⍝ Function to add or replace a line in doc with a debit.
  utl∆es (2≠⍴ln←,ln)/'LENGTH ERROR ARGUMENT MUST BE A TWO ITEM VECTOR'
  new←doc ctrgl_doc_newLine 0 0, ln[1], 0, ln[2]
∇

∇new←doc ctrgl_doc_debit ln
  ⍝ Function to add or replace a line in doc with a debit.
  utl∆es (2≠⍴ln←,ln)/'LENGTH ERROR ARGUMENT MUST BE A TWO ITEM VECTOR'
  new←doc ctrgl_doc_newLine 0 0, ln, 0
∇

∇new←ctrgl_doc_get_description entry
  new←1 6⊃entry
∇

∇new←entry ctrgl_doc_set_description desc;head;body
  (head body)←entry
  head←head[⍳5],⊂desc
  new←head body
  →0
∇

∇new←ctrgl_doc_get_name entry
  new←1 4⊃entry
∇

∇new←entry ctrgl_doc_set_name name;head;body
  (head body)←entry
  head←head[⍳3],(⊂name),head[5 6]
  new←head body
∇

∇new←doc ctrgl_doc_newLine ln;head;docLines;ix;ct
  ⍝ Function appends a new line or replaces and old line (based on
  ⍝ account) in a document. Called by ctrgl_doc_debit and ctrgl_doc_credit.
  (head docLines)←doc
  →(0=ct←1↑⍴docLines)/add
  →(ct<ix←docLines[;3]⍳ln[3])/add
replace:
  ln←ln[1],ix,2↓ln
  docLines←docLines[⍳ix-1;],[1]ln,[1](ix 0)↓docLines
  →end
add:
  ln←ln[1],(1+''⍴⍴docLines),2↓ln
  docLines←docLines,[1]ln
  →end
end:
  new←head docLines
∇

∇msg←lines ctrgl_doc_delLine_editchecks acct
  msg←⍬
  ⍎ (~utl∆stringp acct)/'msg←''ACCOUNT NUMBER IS NOT A CHARACTER STRING'' ◊ →0'
  ⍎ ((1↑⍴acct)<lines[;3] utl∆listSearch acct)/'msg←(⍕acct),'' NOT FOUND'' ◊ →0'
∇

∇doc←old ctrgl_doc_delLine acct;head;lines
  ⍝ Function to delete a line from a document.
  (head lines)←old
  utl∆es lines ctrgl_doc_delLine_editchecks acct
  doc←(⊂head),⊂(~∊{acct utl∆stringEquals ⍵}¨lines[;3])⌿lines
∇

∇wp_head←cfg ctrgl_doc_wp_head doc
  ⍝ Function returns the heading for the document report
  wp_head←(1⊃doc)[2],meta_doc[4],⊂'Period ',5⊃meta_doc
∇

∇dat←handle ctrgl_doc_wp_dat doc;co;lines;cmd
  ⍝ Function returns the array to be displayed in a document workpaper
  co←1 2⊃doc ◊ lines ← 2⊃doc
  cmd←'SELECT trim(acct_no), title from accounts WHERE company = ? and acct_no = ?'
  dat←{,cmd SQL∆Select[handle] ⍵}¨⊂[2](⊂co),[1.1]lines[;3]
  dat←(((⍴dat),2)⍴⊃dat),lines[;4 5]
  ⍝dat←accounts[{accounts[;1] utl∆listSearch ⍵}¨lines[;3];1 2],lines[;4 5]
  dat←dat,[1](⊂''),(⊂'Total'),+⌿lines[;4 5]
∇

∇handle ctrgl_doc_insert doc;head;body;cmd
  ⍝ Function inserts a new document into the database
  head←1⊃doc ◊ body←2⊃doc
  cmd←'INSERT INTO document (doc_id,journal,name,doc_date,description,period) VALUES('
  cmd←cmd,'nextval(''document_doc_id_seq''),'
  cmd←cmd,'''',(2⊃head),''',' 	⍝ journal
  cmd←cmd,'''',(3⊃head),''',' 	⍝ name
  cmd←cmd,'DATE ''',(4⊃head),''','     ⍝ doc_date
  cmd←cmd,'''',(5⊃head),''','	        ⍝ description
  cmd←cmd,'''',(6⊃head),''')'		⍝ period
  cmd SQL∆Exec[handle] ''
  cmd←'SELECT lastval()'
  head[1]←⍬⍴cmd SQL∆Select[handle] ''
  body[;1]←head[1]
  cmd←'INSERT INTO doc_lines (doc_id,line_no,acct_no,debit,credit) VALUES (?,?,?,?,?)'
  cmd SQL∆Exec[handle] body
∇

∇handle ctrgl_doc_replace doc;cmd
  ⍝ Function replaces a document in to database.
  head←1⊃doc ◊ body←2⊃doc
  cmd←'SELECT doc_id FROM document WHERE journal = ? and name = ? and period = ?'
  head[1]←⍬⍴cmd SQL∆Select[handle] head[2 3 6]
  body[;1]←head[1]
  cmd←'UPDATE document set description= ? WHERE doc_id=?'
  cmd SQL∆Exec[handle] head[7 1]
  cmd←'DELETE FROM doc_lines WHERE doc_id = ?'
  cmd SQL∆Exec[handle] head[1]
  cmd←'INSERT INTO doc_lines (doc_id,line_no,acct_no,debit,credit) VALUES (?,?,?,?,?)'
  cmd SQL∆Exec[handle] body
∇

∇handle ctrgl_doc_post doc;cmd;head;body;id;rs
  ⍝ Function posts a document to the database
  head←1⊃doc ◊ body←2⊃doc
  utl∆es handle ctrgl_doc_post_checkHead head
  utl∆es ¨ handle ctrgl_doc_post_checkBody head body
  head[1] ← handle ctrgl_check_doc head[2 3 4 7]
  head[6]←⊂ctrgl_sql_escape_quotes 6⊃head
  SQL∆Begin handle
  →(head[1]≠0)/replace
insert:
  ⍝'SQL∆Rollback handle ◊ ⍞←''Database  update failed.'''  ⎕ea '
  handle ctrgl_doc_insert head body
  →cm
replace:
  ⍝'SQL∆Rollback handle ◊ ⍞←''Database  update failed.'''  ⎕ea '
  handle ctrgl_doc_replace head body
  →cm
cm:
  SQL∆Commit handle
  →0
∇

∇msg←handle ctrgl_doc_show_editchecks doc;head;body
  ⍝ Function checks the document for errors.
  head←1⊃doc ◊ body←2⊃doc
  msg←''
  →(0≠1↑⍴msg←handle ctrgl_doc_post_checkHead head)/0
  msg←handle ctrgl_doc_post_checkBody head body
∇

 ∇msg←handle ctrgl_doc_post_checkHead head
  ⍝ Function confirms that a document's head is ok.
  msg←⍬
  ⍝ journal is item 2
  ⍎( ~handle ctrgl_check_journal 2⊃head)/'msg←''',(2⊃head),' is not defined.'''
  ⍝ Document name is item 3
  ⍎(~utl∆stringp 3⊃head)/'msg←''The document name must be a character string.'''
  ⍝ Document date is item 4
  ⍎(∧/'NOT A DATE'=10↑date∆US date∆parse 4⊃head)/'msg←''',(4⊃head),' is not a date'''
  ⍝ Document description is item 5
  ⍎(~utl∆stringp 5⊃head)/'msg←''The document description must be a character string.'''
  ⍝ Period is item 6
  ⍎(~handle ctrgl_check_period 6⊃head)/'msg←''',(6⊃head),' is not a valid period.'''
∇

∇msg←handle  ctrgl_doc_post_checkBody doc;cmd;head;body;rs
  ⍝ Function confirms the body of a document can be posted.
  head←1⊃doc ◊ body←2⊃doc 
  msg←''
  ⍝ Column 3 is account numbers
  cmd←'SELECT acct_no FROM accounts where company = ? and acct_no = ?'
  rs←{⍬⍴cmd SQL∆Select[handle] ⍵}¨⊂[2](⊂co),[1.1]body[;3]
  ⍝ rs←cmd SQL∆Select[handle] (⊂co),[1.1]body[;3]
  →(∧/rs←utl∆stringp ¨ rs)/m1
  msg←(,⍕(~rs)/body[;3]),' not in chart of accounts.'
  →0
m1:
  →(∧/rs←utl∆numberp ¨,body[;4 5])/m2
  msg←(,⍕rs/,body[;4 5]),' are not numbers.'
  →0
m2:
  →(0=¯2 utl∆round -/+⌿body[;4 5])/0
  msg←'The debits do not equal the credits.'
  →0
∇

∇rpt←handle ctrgl_doc_show doc
  ⍝ Function to display an document in general journal form
  utl∆es handle ctrgl_doc_show_editchecks doc
  rpt←wp∆txt∆assemble handle ctrgl_doc_workpaper doc
∇