I'm enclosing three files 1) test_debug_bug.log is a session log of me testing some new software.
2) ctrgl_revised.sql is a dump of the database that was opened during this session.
3) ctrgl_test_debug_bug.apl is the workspace I was testing.First this demonstrates yet again that I am constitutionally unable to correctly enter any line of code.
Find the function ctrgl_check_period and look at line 4 where I used an non existent variable arg.
The log shows me loading the workspace; opening the database; creating a document, begin; and trying to look at it with function sqlh ctrgl_doc_show begin. I trip across my own typo.
I then edited the workspace file, corrected line 4.You'll see that I reloaded the new revised file and created document begin. I confirmed the revision by displaying the code for ctrgl_check_period and seeing the revised line 4.
I then executed `sqlh ctrgl_doc_show` again and tripped on my now reappeared typo.
I'm hoping thee will have fun with this oneI'll confess that I've struggled with this bug through several test and debut sessions and despaired ever being able to recreate it. I get so wrapped up in the test and debug I can't recreate the environment I was in. It turns out easier than I expected.
w
)load 2 ctrgl_test_debug_bug.apl
DUMPED 2024-01-19 15:41:19 (GMT-5)
NEW )COPY_ONCE workspace: 5 SQL
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/wp
)COPY DALY/wp (file /usr/local/lib/apl/wslib5/DALY/wp) failed: No such file or directory
NEW )COPY_ONCE workspace: 3 DALY/date
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/utl
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 3 DALY/lex
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/cl
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 APLComponentFiles/ComponentFiles.apl
DUMPED 2024-01-06 11:12:23 (GMT-5)
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/prompt
DUMPED 2024-01-06 11:12:23 (GMT-5)
sqlh←ctrgl_sql_connect 'localhost' 'dalyw' 'ctrgl_revised' '1BBmXEc0'
DOMAIN ERROR+
SQL∆Connect[19] Z←type ⎕SQL[1]arg
^ ^
arg
host=localhost user=dalyw dbname=ctrgl_refised password=1BBmXEc0
)sic
sqlh←ctrgl_sql_connect 'localhost' 'dalyw' 'ctrgl_revised' '1BBmXEc0'
begin ← ctrgl_doc_init 'gj' 'begin' '1/1/2023' 'To record opening balances' '2023-01'
begin←begin ctrgl_doc_debit 1010 45000
begin←begin ctrgl_doc_debit 1410 2500
begin←begin ctrgl_doc_debit 1510 1300000
begin←begin ctrgl_doc_debit 1520 755000
begin←begin ctrgl_doc_credit 1590 140400
begin←begin ctrgl_doc_credit 2710 1644000
begin←begin ctrgl_doc_credit 3100 1000
begin←begin ctrgl_doc_credit 3990 275350
sqlh←ctrgl_sql_connect 'localhost' 'dalyw' 'ctrgl_revised' '1BBmXEc0'
sqlh ctrgl_doc_show begin
VALUE ERROR+
ctrgl_check_period[3] b←0≠1↑⍴cmd SQL∆Select[handle]args
^
⎕cr 'ctrgl_check_period'
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
)sic
ctrgl_sql_disconnect sqlh
)load 2 ctrgl_test_debug_bug.apl
)COPY_ONCE table cleared (8 entries)
DUMPED 2024-01-19 15:45:51 (GMT-5)
NEW )COPY_ONCE workspace: 5 SQL
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/wp
)COPY DALY/wp (file /usr/local/lib/apl/wslib5/DALY/wp) failed: No such file or directory
NEW )COPY_ONCE workspace: 3 DALY/date
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/utl
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 3 DALY/lex
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/cl
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 APLComponentFiles/ComponentFiles.apl
DUMPED 2024-01-06 11:12:23 (GMT-5)
DUMPED 2024-01-06 11:12:23 (GMT-5)
NEW )COPY_ONCE workspace: 5 DALY/prompt
DUMPED 2024-01-06 11:12:23 (GMT-5)
⎕cr 'ctrgl_check_period'
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] pd
begin ← ctrgl_doc_init 'gj' 'begin' '1/1/2023' 'To record opening balances' '2023-01'
begin←begin ctrgl_doc_debit 1010 45000
begin←begin ctrgl_doc_debit 1410 2500
begin←begin ctrgl_doc_debit 1510 1300000
begin←begin ctrgl_doc_debit 1520 755000
begin←begin ctrgl_doc_credit 1590 140400
begin←begin ctrgl_doc_credit 2710 1644000
begin←begin ctrgl_doc_credit 3100 1000
begin←begin ctrgl_doc_credit 3990 275350
sqlh←ctrgl_sql_connect 'localhost' 'dalyw' 'ctrgl_revised' '1BBmXEc0'
sqlh ctrgl_doc_show begin
DOMAIN ERROR+
SQL∆Select[15] Z←statement ⎕SQL[3,db]args
^ ^
)si
SQL∆Select[15]
ctrgl_check_period[4]
ctrgl_doc_post_checkHead[12]
ctrgl_doc_show_editchecks[4]
ctrgl_doc_show[2]
⋆
args
2023-01
⎕cr 'ctrgl_check_period'
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] pd
ctrgl_revised.sql
Description: application/sql
#! /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 ∇
