Avatar

building a cost control system with Basic-256 (New Features)

by DickStomp2 @, NL- SASSENHEIM, Sunday, October 12, 2014, 20:36 (1158 days ago)

After all exercises I've build a Cost Control System if I worked with the Old Good COBOL but now using Basic256. Excellent!

First problem is translating the downloads of the bank... CSV-files with repairing of info containing comma's... There are indeed dumps with semi-colons as field separators, that makes work easier by exploding on '";"' after stripping the quotes at begin and end.

Then searching for Cost-accounts by using two tables 1) Creditor-Banknumber or 2) codes in the Remarks field and then converting to Cost-account.
That results in a new file with Cost-account + Date + Amount + Remarks.

Then sorting the file on 1) Cost-account 2) Date

Finally reporting
1) detailed bookings per Cost-account
2) subtotals per Cost-Account
3) final saldo which must positive....!

It looks fine and withstands a review of an accountant.

At last I had to learn to work with CSV-files instead the DA-format with fixed length. No problem, I'm fexible enough.
Converting the bank-dumps into LibreOfficeBase Table format is not easy.
Next, converting tables by using other tables to create new tables is with LibreOfficeBase a crime!
We are missing in LOBase the TableModify and TableCreate possibilities...

So, thanks to Basic-256 the old fashioned way of programming is still the best!

building a cost control system with Basic-256

by BillDee, Saturday, December 20, 2014, 09:14 (1090 days ago) @ DickStomp2

Sounds like a cool project.
Good job.

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 20:21 (1026 days ago) @ DickStomp2

First things First:
The JobControl, Linux Ubuntu, Bash Kosten.sh

# Lees ASN Samen
basic256 -r LeesASNS.kbs # ASNS.csv > ASNS.dta

# Kostenplaats bijzoeken
basic256 -r RekKostToev.kbs # ASNS.dta + RekNaar_Kosten.csv + Tekst_Kosten.csv > ASNSK.csv

# Voorbereiden Kostenrapport, records formeren voor sort: Kostenplaats, Datum
basic256 -r RapKost.kbs # ASNSK.csv > RapKost.csv

# Sorteren op Kostenplaats, Datum
sort RapKost.csv > RapKostS.csv # RapKost.csv > RapKostS.csv

# Rapport opmaken, kosten groeperen en salderen
basic256 -r RapKostBouwen.kbs # RapKostS.csv > RapKostText.rtf

# Rapport tonen met Writer
lowriter RapKostText.rtf # RapKostText.rtf > LibreOffice Writer

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 20:51 (1026 days ago) @ DickStomp2

Reading and Converting Bank Transactions: LeesASNS.kbs

# omzetten van download ASN boekingen, convert bank transaction records
# datum DD-MM-JJJJ > JJJJ-MM-DD
# verwijderen kolommen, die niet ter zake zijn, remove fields not needed
# TYPE ASN-bank-data
# Datum AS STRING * 10 # 0 a DD-MM-YYYY
# RekVan AS STRING * 18 # 1 b IBAN # Account Debet
# RekNaar AS STRING * 18 # 2 c IBAN # Account Credit
# Naam AS STRING * 20 # 3 d Naam # Name
# Adres AS string # 4 e Adres
# Postcode as string * 7 # 5 f 9999 XX
# Plaats as string * 24 # 6 g Plaats
# Valuta as string * 3 # 7 h EUR
# SaldoVoor as string * 14 # 8 i -999999999.99
# ValutaMut as string * 3 # 9 j XXX
# Bedrag AS string * 14 # 10 k -999999999.99 # Amount
# DatumJourn as string * 10 # 11 l dd-mm-jjjj
# DatumValuta as string * 10 # 12 m dd-mm-jjjj
# InternTrans as string * 4 # 13 n 9999
# CodeTrans AS STRING * 3 # 14 o XXX # Transactioncode
# VolgnTrans as string * 8 # 15 p 99999999
# BetalingKmk as string * 16 # 16 q XXXXXXXXXXXXXXXX # Internal billing code
# Omschrijving AS STRING * 98 # 17 r # Description
# AfschriftNr as string * 3 # 18 s 999
# Kostenplaats AS STRING * 5 # nieuw # Budgetcode
#END TYPE
#DIM ASN-record AS ASN-bank-data # for Direct Access Files, not in use...

print currentdir()

invoer = 1 # input
Invoer$ ="ASNS.csv"
telinvoer = 0

uitvoer = 2 # output
Uitvoer$ ="ASNS.dta"
teluitvoer = 0

open (invoer,Invoer$)
if exists (Uitvoer$) then kill (Uitvoer$)
open (uitvoer, Uitvoer$)

while not eof(invoer)
Buffer$ = readline(invoer)
telinvoer = telinvoer + 1
L = length(Buffer$)
if L > 10 then
gosub VerwerkRegel
end if
end while

close (invoer)
close (uitvoer)

print "in: "+telinvoer+" uit: "+teluitvoer

print "klaar" # ready
print "vervolg met RekKostToev" # next program
end

VerwerkRegel: #
Buffers$ = mid(Buffer$,3,L-2) # strip quotes
veld$ = explode(Buffer$, '";"')
Record$ = right(veld$[0],4) # YYYY
Record$ = Record$ + mid(veld$[0],5,2) # MM
Record$ = Record$ + mid(veld$[0],2,2) # DD
Record$ = Record$ + veld$[1]+"," # IBANdebet
Record$ = Record$ + right(veld$[2],10)+"," # IBANcredit
Record$ = Record$ + veld$[3]+"," # Naam
Record$ = Record$ + veld$[10]+"," # Bedrag
Record$ = Record$ + veld$[14]+"," # TransCode
Record$ = Record$ + veld$[16]+"," # BillingCode
Record$ = Record$ + veld$[17] # Omschrijving
writeline(uitvoer,Record$)
teluitvoer = teluitvoer + 1
return

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 22:22 (1026 days ago) @ DickStomp2

Next program is adding the BudgetCode
I use budget codes in the range aaaaa thru zzzzz

Popular ones are:
benzi = petrol for the car
ziek = insurance health, the negative result is tax reductable...
tuin = maintenance garden
autov = insurance of the car
autob = tax for using the car
green = the electricity used from the net and generated by a wind-generator to the net
and so on, we have about 140 Budgets to observe...

I'm using two tables:
1) Account Creditor > budgetcode
2) Description containing budgetcode

The program RekKostToev.kbs does the work:

# Kostenplaats bijzoeken
print (currentdir)

gosub Init
gosub LaadCrediteurPlaats
gosub LaadTekstPlaats
gosub BijzoekenKostenplaats
end

Init: #
Rmax = 100
Tmax = 100
return

LaadCrediteurPlaats: #
print "Credit"
dim RK$(Rmax, 3)
Credit$ = "RekNaar_Kosten.csv"
Invoer = 1
open (Invoer, Credit$)
R = 1
while not eof (Invoer)
Invoer$ = readline (Invoer)
if length(Invoer$) > 5 then
Veld$ = explode(Invoer$, ",")
print Veld$[?]
print R+" "+Veld$[0]+" "+Veld$[1]
RK$[R,1] = Veld$[0]
RK$[R,2] = Veld$[1]
R = R + 1
if R > Rmax - 2 then
print "Rekening tabel bijna vol "+Rmax
close (Invoer)
end
end if
end if
end while
Rmax = R - 1
print "Rmax "+Rmax
close (Invoer)
return

LaadTekstPlaats: #
print "Tekst"
dim TK$(Tmax, 4)
Tekst$ = "Tekst_Kosten.csv"
open (Invoer,Tekst$)
T = 1
while not eof (Invoer)
Invoer$ = readline (Invoer)
Veld$ = explode(Invoer$, ",")
L = length(Veld$[0])
TK$[T,0] = left(Veld$[0],L-1)
TK$[T,1] = Veld$[1]
TK$[T,2] = Veld$[2]
T = T + 1
if T > Tmax - 2 then
print "Tekst tabel bijna vol "+Tmax
close (Invoer)
end
end if
end while
Tmax = T - 1
print "Tmax "+Tmax
close (Invoer)
return

BijzoekenKostenplaats: #
print "Zoeken"
Invoer = 1
Invoer$ = "ASNS.dta"
open (Invoer, Invoer$)
Uitvoer = 2
Uitvoer$ = "ASNSK.csv"
if exists (Uitvoer$) then kill (Uitvoer$)
open (Uitvoer, Uitvoer$)

In = 0
Uit = 0
while not eof (Invoer)
InData$ = readline (Invoer)
Kosten$ = "?"
Reden$ = ""
In = In + 1
Veld$ = explode (InData$, ",")
Credit$ = Veld$[1]
gosub ZoekCredit
if Kosten$ = "?" then gosub ZoekTekst
UitData$ = left(InData$, length(InData$)-1) +","+ left(Kosten$, length(Kosten$)) +","+ Reden$
writeline (Uitvoer, UitData$)
Uit = Uit + 1
Kosten$ = ""
Reden$ = ""
end while

close (Invoer)
close (Uitvoer)
print "In "+In+" Uit "+Uit
print "vervolg met RapKost"
return

ZoekCredit: #
For R = 1 to Rmax
if RK$[R,1] = Veld$[2] then
Kosten$ = RK$[R,2]
return
end if
next R
return

ZoekTekst: #
Veld$[6] = lower(Veld$[6]) # omdat TK$ lower is
For T = 1 to Tmax
Plaats = instr(Veld$[6],TK$[T,0])
if Plaats > 0 then
Reden$ = TK$[T,0]
Kosten$ = TK$[T,1]
return
end if
next T
return

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 22:26 (1026 days ago) @ DickStomp2

Sorry for the fact that the layout is a little bit lost, but I expect, that you will align if and endif at the right places...
Perhaps that on this site something can be streamlined...

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 22:34 (1026 days ago) @ DickStomp2

The next step is sorting 1) Budget and 2) Date!
Ordinary step in BASH...
Then follows building the report per Budget...
This is a nice one! I like it! Old fashioned COBOL programming like in the 70ties!!
RapKostBouwen.kbs:

# Kosten Rapport opbouwen
#1 Start
#2 lees record
#3 noteer Groep
#4 schrijf record
#5 Lees record
#6 Zelfde groep?
# nee: schrijf totaal
# noteer groep
#7 eind bestand?
# nee: ga naar 4
#8 schrijf eind
#9 sluit

gosub init
print currentdir
if exists(uitvoer$) then kill uitvoer$

open (invoer, invoer$)
open (uitvoer, uitvoer$)
write (uitvoer, " "+Koptekst$+chr(13)+chr(10)+chr(10))
regel = 2
gosub LeesInvoer
telin = telin + 1
groep$ = kosten$
telgroep = telgroep + 1
buf = 1

while not eof (invoer)
while groep$ = kosten$ and not eof (invoer)
telin = telin + 1
gosub VulBuffer
gosub LeesInvoer
end while
gosub EindGroep
end while

getal = totaal2
call editnum(ref(getal),ref(getal$))
writeline (uitvoer," " + getal$ +" Totaal Totaal")

close (invoer)
close (uitvoer)
print "In: "+telin+" + Groepen: "+telgroep+" = Uit: "+teluit
print "Klaar"
end

init: #
Koptekst$ = "Grootboek "+day+"-"+(month+1)+"-"+year
regel = 2
invoer = 0
uitvoer = 1
invoer$ = "RapKostS.csv"
uitvoer$ = "RapKostText.rtf"
kosten$ = ""
telin = 0
teluit = 0
telgroep = 0
totaal = 0
totaal2 = 0
dim buffer$(90)
buffermax = 40
buf = 0
regel = 0
regelmax = 63
return

LeesInvoer: #
indata$ = readline (invoer)
if length(indata$)<10 then
return
end if
veld$ = explode(indata$, ",")
kosten$ = left(veld$[0]+" ",5)
datum$ = veld$[1]
bedrag$ = veld$[2]
bedrag = float(bedrag$)
oms$ = left(veld$[5], 50)
if right(oms$,1) = chr(10) then
oms$=left(oms$,length(oms$)-1)
end if
return

EindGroep: #
getal = totaal
call editnum(ref(getal),ref(getal$))
buffer$[buf] = left(groep$ +" ",6)+ getal$+" "+" Totaal"
buf = buf + 1
MaandBedrag = getal / 12
call editnum(ref(MaandBedrag),ref(getal$))
buffer$[buf] = left(groep$ +" ",6)+ getal$+" "+" /Maand"
buf = buf + 1
buffer$ [buf] = " "
bufmax = buf
gosub SchrijfBuffer
groep$ = kosten$
telgroep = telgroep + 1
return

VulBuffer: #
getal = float(bedrag$)
call editnum(ref(getal),ref(getal$))
buffer$[buf] = left(kosten$+" ",6)+getal$+" "+datum$+" "+oms$
buf = buf + 1
totaal = totaal + bedrag
totaal2 = totaal2 + bedrag
return

SchrijfBuffer: #
if regel + bufmax > regelmax then
gosub NieuwBlad
end if
for buf = 1 to bufmax
writeline (uitvoer, buffer$[buf])
teluit = teluit + 1
regel = regel + 1
if regel > regelmax then
gosub NieuwBlad
end if
next buf
totaal = 0
buf = 1
return

NieuwBlad: #
write (uitvoer, chr(12) + " " + Koptekst$ + chr(13) + chr(10) + chr(10)) # FormFeed
regel = 2
return

include "editnum.kbs"

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Saturday, February 21, 2015, 22:42 (1026 days ago) @ DickStomp2

And last but not least:
print the .rtf result with LibreOffice Writer
with an MonoType character (80 char per line)
and see what you saved!
From that savings I could pay this year our 40year wedding dinner!!!
Programming (=planning) makes a lot of money!

Avatar

building a cost control system with Basic-256

by DickStomp2 @, NL- SASSENHEIM, Sunday, February 22, 2015, 07:48 (1026 days ago) @ DickStomp2

And here is the INCLUDE-member: editnum.kbs

# EDITNUM
# omzetten van een bedrag voor afdrukken
# formatting a sum for printing
# invoer decimaal getal van maximaal 10 cijfers
# input decimal number of maximum 10 digits
# uitvoer een string van 11 posities
# output a string of 11 positions
# wordt aangeroepen met call editnum(ref(getal),ref(bedrag$))
# is called with call editnum(ref(number),ref(sum$))
# subroutine editnum(ref(getal),ref(bedrag$))
# subroutine editnum(ref(number),ref(sum$))
# next two lines for testing
#getal = -12345.678
#print getal for testing
I = int(getal)
#print I
I$ = right(" "+string(I),8)
#print I$
D = (getal - I) * 100
if D < 1 then
D = D * -1
end if
#print D
D$ = left(string(D)+"00",2)
#print D$
bedrag$ = I$ + "." + D$
#print bedrag$
end subroutine

RSS Feed of thread
powered by my little forum