subroutine getupgradedates() {
call osread(upgradelog, "UPGRADE.CFG");
upgradelog = upgradelog.field(0x1A, 1).trim();
upgradelog.converter("\r\n", FM);
var nn = upgradelog.count(FM) + (upgradelog ne "");
var upgradedata = "";
for (var ii = 1; ii <= nn; ++ii) {
var idate = (upgradelog.a(ii, 1).field(" ", 2, 3)).iconv("D");
//itime=iconv(field(upgradelog,' ',1),'MT')
if (not(upgradedata.locate(idate, xx))) {
upgradedata.r(-1, idate);
}
};//ii;
USER1 = upgradedata;
return;
}
function main(in mode, io ledgercode, in newaccno0, in newaccount, in sortorder, in params0, out msg) {
//y2k
//chartmv.fns must be changed in all places in ABP and ABP2
var interactive = not SYSTEM.a(33);
var newaccno = newaccno0.a(1, 1, 1);
//if newaccno<1,1,2>='' then newaccno<1,1,2>=newaccno<1,1,1>
var newaccname = newaccount.a(1);
var params=params0;
var update = mode == "ADD";
msg = "";
//check does not already exist in any ledger
if (fin.account.read(fin.accounts, "." ^ newaccno)) {
return 0;
}
// msg=''
//preexist:
// msg=quote(newaccno):msg:' ACCOUNT NUMBER ALREADY EXISTS'
// return 0
// end
if (fin.account.read(fin.accounts, newaccno)) {
return 0;
}
// msg=' ORIGINAL'
// goto preexist
// end
//make the ledger code
var origledgercode = ledgercode;
var origparams = params;
gosub expandledgercodeparams(ledgercode, params);
//if chart doesnt exist then walk back through the years
//zzz should walk back through quarters or periods too but isnt implemented
//if by period or quarter will work if all periods/quarters exist in one year
var tt;
if (not(tt.read(fin.charts, ledgercode))) {
var targetledgercode = ledgercode;
//contruct prior year ledgercode
getprevledgercode:
params.r(1, (addcent(params.a(1)) - 1).substr(-2, 2));
ledgercode = origledgercode;
gosub expandledgercodeparams(ledgercode, params);
//just quit if back to original code after searching backwards through 07-06
if (ledgercode == targetledgercode) {
ledgercode = targetledgercode;
}else{
//if prior year chart doesnt exist go back to look for another
var chart;
if (not(chart.read(fin.charts, ledgercode))) {
goto getprevledgercode;
}
//found previous chart so create new chart
//change previous year if in chart name
var tt = chart.a(1);
tt.swapper(addcent(params.a(1)), addcent(origparams.a(1)));
tt.swapper(params.a(1), origparams.a(1));
chart.r(1, tt);
ledgercode = targetledgercode;
//zzz should really lock to prevent create unless exclusive
//but will not really cause a problem probs
gosub createchart(chart, ledgercode);
}
}
//find the highest numbered ledger within that group
var chart = "";
var ledgerno = -1;
origledgercode = ledgercode;
var newaccnumber = newaccno;
if (newaccnumber.match("\"JOB\"0N")) {
newaccnumber.splicer(1, 3, "");
}
if (newaccnumber.match("\"SCH\"0N")) {
newaccnumber.splicer(1, 3, "");
}
nextledgerno:
ledgerno += 1;
var newledgercode = origledgercode ^ ledgerno.oconv("MD00Z");
var nextledgerno = ledgerno + 1;
if (nextledgerno < 2) {
nextledgerno = 2;
}
var nextledgercode = origledgercode ^ nextledgerno;
if (chart.read(fin.charts, newledgercode)) {
/*;
//if numeric account numbers and any account is gt the new account number
// then this is the ledger
if num(newaccnumber) then;
naccs=count(chart<3>,vm)+1;
for accn=naccs to 1 step -1;
accno=chart<10,accn>;
if accno matches '"JOB"0N' then accno[1,3]='';
if accno matches '"SCH"0N' then accno[1,3]='';
if accno then;
if num(accno) and accno>newaccnumber then;
ledgercode=newledgercode;
goto foundledger;
end;
end;
next accn;
end;
*/
ledgercode = newledgercode;
goto nextledgerno;
}
//allow for number :1 missing and jump to :2
if (ledgerno < 2) {
//ledgerno+=1
goto nextledgerno;
}
//foundledger:
//check that the ledger exists and get the chart
if (not chart) {
msg = "Cannot create Account " ^ (DQ ^ (newaccno ^ DQ)) ^ " automatically because|";
msg ^= "ledger " ^ (DQ ^ (ledgercode ^ DQ)) ^ " must be created first";
goto exit;
}
lockit:
//lock and reread the chart for update
if (not(lockrecord("CHARTS", fin.charts, ledgercode, 60))) {
msg = "Cannot create account " ^ (DQ ^ (newaccno ^ DQ)) ^ " automatically because|";
msg ^= "somebody is updating |" ^ (DQ ^ (chart.a(1) ^ DQ));
goto exit;
}
if (not(chart.read(fin.charts, ledgercode))) {
chart = "";
}
//add new chart if existing is too big
//if interactive and len(chart)>32000 and newledgercode then
if (chart.length() > 32000 and newledgercode) {
//unlock the old chart because we are going to create a new one
var xx = unlockrecord("CHARTS", fin.charts, ledgercode);
//switch to the new ledger code
ledgercode = newledgercode;
newledgercode = "";
gosub createchart(chart, ledgercode);
goto lockit;
}
//prevent chartsize exceeding 32KB
//NB "TOO BIG" is a trigger phrase hard coded in IMPORTX so dont change it
msg = "THE|" ^ chart.a(1) ^ " (" ^ ledgercode ^ ")|LEDGER IS GETTING TOO BIG";
msg ^= "||!!! PLEASE OPEN A NEW LEDGER|WITH LEDGER CODE " ^ (DQ ^ (newledgercode ^ DQ)) ^ "|";
if (chart.length() > 36000) {
msg.splicer(1, 0, "!CANNOT OPEN NEW ACCOUNT " ^ (DQ ^ (newaccno ^ DQ)) ^ " BECAUSE ");
msg.swapper("GETTING ", "");
goto exit;
}
//warning if chartsize nearing 32KB
if (chart.length() > 32000) {
//if mode='VALIDATE' then call note(msg)
if (mode == "VALIDATE") {
goto exit;
}
}
msg = "";
///
if (mode == "VALIDATE") {
goto exit;
}
///
//determine the sort key
if (sortorder == "NAME") {
sortkey = newaccname;
}else{
sortkey = newaccname;
}
//calc the number of lines
accs = chart.a(4);
naccs = accs.count(VM) + (accs ne "");
naccs2 = (chart.a(3)).count(VM) + (chart.a(3) ne "");
if (naccs2 > naccs) {
naccs = naccs2;
}
//default to the end of the ledger
vn = naccs + 1;
//find value number to insert at
if (sortorder) {
//insert before the first larger account number or name
for (var accn = 1; accn <= naccs; ++accn) {
if (sortorder == "NAME") {
value = chart.a(3, accn);
}else{
value = chart.a(10, accn);
}
if (value) {
if (value.trimf() > sortkey) {
vn = accn;
accn = naccs;
//insert a blank line
var nn = fin.chartmvfns.dcount(VM);
for (var ii = 1; ii <= nn; ++ii) {
var tt = fin.chartmvfns.a(1, ii);
if (tt) {
chart.inserter(tt, vn, "");
}
};//ii;
}
}
};//accn;
}
//update the chart of accounts
if (vn == 1) {
indent = "";
}else{
//indent like previous line
var temp = chart.a(3, vn - 1);
indent = (temp.length() - (temp.trimf()).length()).space();
//if no account number on previous line then indent by one more
if (chart.a(4, vn - 1) == "") {
indent ^= " ";
}
}
chart.r(3, vn, indent ^ newaccname);
chart.r(4, vn, newaccno);
chart.r(10, vn, newaccno);
//terms
//main currency
//tax code
if (newaccount.a(33)) {
chart.r(33, vn, newaccount.a(33));
}
if (newaccount.a(4)) {
chart.r(7, vn, newaccount.a(4));
}
if (newaccount.a(14)) {
chart.r(14, vn, newaccount.a(14));
}
if (update) {
call cropper(chart);
chart.write(fin.charts, ledgercode);
}
//create the account record
fin.account = newaccount;
fin.account.r(2, ledgercode);
fin.account.r(5, chart.a(6));
fin.account.r(6, chart.a(2, 1, 2));
fin.account.r(8, chart.a(9));
fin.account.r(10, newaccno);
fin.account.r(23, chart.a(5));
//account<26>=
if (update) {
fin.account.write(fin.accounts, newaccno);
}
//create the other account record
fin.account.r(10, newaccno);
if (update) {
fin.account.write(fin.accounts, "." ^ newaccno);
}
exit:
/////
var xx = unlockrecord("CHARTS", fin.charts, ledgercode);
return 0;
}
function main(in mode, in select, in nfilters, dim& filters) {
if (false && mode && select && nfilters && filters(0,0)){};
var filename = SENTENCE.field(" ", 2);
var file;
if (not(file.open(filename))) {
return fsmsg();
}
var sentencex = SENTENCE;
sentencex.converter(" ", VM);
if (sentencex.locate("SELECT", temp, 1)) {
selectx = SENTENCE.field(" ", temp + 1, 9999);
sentencex = SENTENCE.field(" ", 1, temp - 1);
}else{
sentencex = SENTENCE;
selectx = "";
}
var normalise = sentencex.index(" NORMALISE", 1);
if (normalise) {
sentencex.swapper(" NORMALISE", "");
}
var raw = sentencex.index(" RAW", 1);
if (raw) {
sentencex.swapper(" RAW", "");
}
var mvgroupno = "";
var colheaderrow = not sentencex.index(" NOCOLHEADER", 1);
if (not colheaderrow) {
sentencex.swapper(" NOCOLHEADER", "");
}
if (filename.substr(1,4) == "DICT") {
tt = "VOC";
}else{
tt = filename;
}
if (not(DICT.open("dict_"^tt))) {
return fsmsg();
}
// var converter = "";
// if (not((tt!!!).read(DEFINITIONS, "CONVERTER*" ^ filename))) {
// tt!!! = "";
// }
// if (tt!!!.a(1)) {
// converter = "CONVERTER." ^ tt!!!.a(1);
// }
var xx;
if (xx.read(DICT, "AUTHORISED")) {
dicthasauthorised = 1;
}else{
dicthasauthorised = 0;
}
var notexportable = "";
var exportable = sentencex.field(" ", 3, 9999);
if (exportable) {
exportable.converter(" ", FM);
if (exportable.a(1) == "EXCEPT") {
notexportable = exportable.field(FM, 2, 9999);
exportable = "";
}
}
//expand any group fields in notexportable
if (notexportable) {
for (var ii = 1; ii <= notexportable.count(FM) + 1; ++ii) {
var dictrec;
if (dictrec.read(DICT, notexportable.a(ii))) {
if (dictrec.a(1) == "G") {
temp = dictrec.a(3);
temp.converter(VM ^ " ", FM ^ FM);
notexportable.r(ii, temp);
}
}
};//ii;
}
var listkey = var(1000000).rnd();
if (not exportable) {
if (exportable.read(DICT, "exportable")) {
if (exportable.a(1) == "G") {
exportable = exportable.a(3);
exportable.converter(VM ^ " ", FM ^ FM);
}
keyx = exportable.substr(1,exportable.index(FM ^ FM, 1) - 1);
nkeys = keyx.count(FM) + 1;
if (nkeys > 2) {
//call msg('Key field(s) should be followed by a blank line or space in EXPORTABLE')
//stop
nkeys = 0;
}
}else{
exportable = "";
keyx = "";
}
}
var exportable2 = exportable;
//nextmvgroup:
if (mvgroupno) {
tt = keyx ^ FM ^ "LINE_NO" ^ FM;
}else{
tt = "";
}
exportable = tt ^ exportable2.field("%", 1);
exportable2 = exportable2.field("%", 2, 9999);
outfilename = SYSTEM.a(2);
//zzz if mvgroupno then outfilename[8,1]=mvgroupno
if (outfilename.lcase().substr(-4, 4) == ".htm") {
outfilename.splicer(-3, 3, "xls");
SYSTEM.r(2, outfilename);
}
var excel = outfilename.lcase().substr(-3, 3) == "xls";
//retry:
outfilename.osdelete();
if (outfilename.osfile()) {
return exit2("CANNOT EXPORT BECAUSE " ^ outfilename ^ " IS ALREADY|OPEN IN ANOTHER PROGRAM, OR CANNOT BE ACCESSED");
}
//call note2("Exporting " ^ outfilename ^ "||Please wait ...", "UB", buffer, "");
files.redim(255);
filenames.redim(255);
oconvxs.redim(255);
fmtxs.redim(255);
dictrecs.redim(255);
filenames="";
oconvxs="";
fmtxs="";
var nfields = 0;
var selectlist = LISTACTIVE;
if (selectlist) {
savelistactive=LISTACTIVE;
}
if (exportable) {
//call makelist("", exportable, "", "");
//write exportable on lists,listkey
//perform 'GET-LIST ':listkey:' (S)'
//delete lists,listkey
var tt="select dict_" ^ filename ^ " " ^ exportable.swap(FM, "\" \"").quote();
DICT.select(tt);
}else{
if (filename.substr(1,4).lcase() == "dict") {
dict = "";
}else{
dict = "dict_";
}
DICT.select("select " ^ dict ^ filename ^ " BY FMC WITH FMC BETWEEN 1 AND 999999 AND WITH @ID NOT STARTING \'%\' AND WITH MASTER.FLAG (S)");
if (not LISTACTIVE) {
return exit2(dict ^ filename ^ " has no exportable columns");
}
}
dictids.redim(255);
colgroups.redim(255);
dictids = "";
colgroups = "";
var headingx = "";
var coln = 0;
var dictid;
while (DICT.readnext(dictid, MV)) {
if (notexportable.locateusing(dictid, FM, xx)) {
continue;
}
if (dictid[1] == "%") {
continue;
}
if (dictid == "LINE_NO") {
coln += 1;
dictids(coln) = dictid;
headingx.r(coln, dictid);
fmtxs(coln) = "R";
dictrecs(coln) = "";
}else{
if (dict.read(DICT, dictid)) {
// call dicti2a(dict);
coln += 1;
//if dict<2> matches '0N' then
var fn = dict.a(2);
if (fn > nfields) {
nfields = fn;
}
fmtxs(coln) = dict.a(9)[1];
if (raw) {
headingx.r(coln, dictid);
}else{
//extract title
var title = dict.a(3).trim();
title.swapper("<WBR/>", "");
title.swapper("<wbr/>", " ");
title.converter(UPPERCASE ^ "|_" _VM_ "", LOWERCASE ^ " ");
//t=title[1,1]
//convert @lower.case to @upper.case in t
//title[1,1]=t
title = capitalise(title);
title.swapper("(Base)", "(" ^ gen.company.a(3) ^ ")");
//swap ' code' with '' in title
headingx.r(coln, title.trim());
}
//extract file
if (dict.a(11)[1] == "<") {
temp = dict.a(11).substr(2,9999).field(">", 1);
filenames(coln) = temp;
if (not(files(coln).open(temp, ""))) {
return exit2(DQ ^ (temp ^ DQ) ^ " file cannot be found in dict " ^ (DQ ^ (dictid ^ DQ)));
}
var title = headingx.a(coln);
if (title.ucase().substr(-5,5) == " CODE") {
title.splicer(-5, 5, "");
headingx.r(coln, title);
}
}
//extract conversion
if (dict.a(7)) {
var oconvx = dict.a(7);
//force long date format
if (oconvx.index("DATE", 1) or oconvx[1] == "D") {
//if raw then
// oconvx='D4/J'
//end else
if (oconvx == "[SCH.DATES]") {
oconvx = "";
}else{
oconvx = "D4/E";
}
//end
}
oconvxs(coln) = oconvx;
}
colgroups(coln) = dict.a(4)[1] == "M";
dictids(coln) = dictid;
dictrecs(coln) = dict;
}
//end
}
}//nextdict
var ncols = coln;
//if @username='******' then oswrite matunparse(dictids) on 'csv'
if (selectlist) {
LISTACTIVE=savelistactive;
}
call oswrite("", outfilename);
if (not outfile.osopen(outfilename)) {
return exit2(outfilename.quote()^" file cannot be created");
}
var ptr = 0;
//suppress headerrow if not required
if (not colheaderrow) {
headingx = "";
}
if (selectx) {
//selectx:=' AND WITH PERSON_CODE "HARRIS"'
//perform 'SELECT ':filename:' ':selectx
tt = "SELECT " ^ filename ^ " " ^ selectx;
//call xselect(tt);
file.select(tt);
/* if (not LISTACTIVE) {
outfile.osclose();
outfilename.osdelete();
return exit2("No records found");
}
*/
}else{
if (not LISTACTIVE) {
file.select();
}
}
var recn = 0;
rec.redim(ncols);
mvrec.redim(ncols);
/////
//nextrecord:
/////
//get the next key and mv
var mvx;
while (file.readnext(ID, mvx)) {
//user interrupt
if (esctoexit()) {
outfile.osclose();
//osdelete outfilename
return exit2("Interrupted by User");
}
//skip "" key
if (ID == "") {
continue;
}
recn += 1;
//cout << AW.a(30)<< var().cursor(0)<< var().cursor(-4);
//cout << var().cursor(39, _CRTHIGH / 2)<< recn<< ". ";
//get the record
if (not(RECORD.read(file, ID))) {
continue;
}
//skip record if not authorised
if (dicthasauthorised and not calculate("AUTHORISED")) {
continue;
}
//skip zero hours in timesheets
//TODO get this hack into a special dictionary item like LIMIT
if (filename == "TIMESHEETS" and mvx and not RECORD.a(2, mvx)) {
continue;
}
//get the data and work out maximum vn
mvrec = "";
var maxvn=0;
for (var coln = 1; coln <= ncols; ++coln) {
dictid = dictids(coln);
if (dictid eq "LINE_NO")
continue;
MV = mvx;
var cell = calculate(dictid);
if (not cell.length())
continue;
if (oconvxs(coln)) {
cell = cell.oconv(oconvxs(coln));
}
mvrec(coln) = cell;
var temp = cell.dcount(VM);
if (temp > maxvn)
maxvn = temp;
}
//skip if no data
if (not maxvn)
continue;
//normalise the data and output to csv file
//output the lines
for (var vn=1; vn <= maxvn; ++vn) {
rec="";
//conversions
for (var coln = 1; coln <= ncols; ++coln) {
var cell;
if (dictids(coln) == "LINE_NO") {
cell = vn;
//select right multivalue if multivalued field/column
//non-multivalued fields/columns are repeated on every line
}else if (colgroups(coln)) {
cell=mvrec(coln).a(1,vn);
//not multivalued so repeat on every line
} else {
cell = mvrec(coln);
}
//skip empty cells
if (not cell.length())
continue;
//conversions
if (mvx or vn == 1) {
//convert codes to names
if (filenames(coln) and not raw) {
var rec2;
if (rec2.read(files(coln), cell)) {
if (filenames(coln) == "BRANDS") {
cell = rec2.a(2, 1);
}else{
cell = rec2.a(1);
}
}
}
}
//remove any initial + sign - on numbers only
if (cell[1] == "+") {
if ((cell.substr(2,9999)).isnum()) {
cell.splicer(1, 1, "");
}
}
//a single double quote gets changed to ''
if (cell == DQ) {
cell = "\'\'";
}
//swap double quotes for '' unless already double quoted
if (cell[1] ne DQ or cell[-1] ne DQ) {
cell.swapper(DQ, "\'\'");
}
//WARNING prevent cell length more than 255
if (cell.length() > 255) {
cell = cell.substr(1,250) ^ " ...";
}
//double quote non-numerics
if (fmtxs(coln) ne "R") {
//make sure "1-12" is not interpreted as a formula (by prefixing a space?)
if (1 or excel) {
if (var(".-+0123456789").index(cell[1])) {
if (not cell.isnum()) {
cell.splicer(1, 0, " ");
}
}
}
//convert any existing double quotes to '' for T columns which are not already double quoted
if (cell.index(DQ, 1)) {
if (fmtxs(coln) == "T") {
if (cell[1] ne DQ or cell[-1] ne DQ) {
cell.swapper(DQ, "\'\'");
cell = DQ ^ (cell ^ DQ);
}
}
//otherwise just double quote stuff
}else{
cell = DQ ^ (cell ^ DQ);
}
}
rec(coln) = cell;
};//coln;
var line = rec.unparse();
//remove trailing or all tab chars
line.trimmerb(FM);
//suppress output of empty amv rows
if (mvgroupno and nkeys) {
if (line.field(FM, nkeys + 2, 9999) == "") {
line = "";
}
}
//output one line
if (not line.length())
continue;
//remove leading equal signs in order not to confuse Excel
line.swapper(FM ^ "=", FM);
//output header row if first line and not suppressed
if (headingx) {
// if (converter) {
// //headingx will come back converted and maybe as multiple lines
// //converterparams initially contains first line so heading can put some columns into heading if required
// //converterparams comes back with info to speed convertion of lines
// converterparams = line;
// call onverter("HEAD", headingx, converterparams, filename);
// }else{
headingx.converter(FM, var().chr(9));
headingx ^= "\r\n";
// }
osbwrite(headingx, outfile, ptr);
headingx = "";
}
//output line
// if (converter) {
// call @converter("LINE", line, converterparams, filename);
// }else{
line.swapper(FM, var().chr(9));
line ^= "\r\n";
// }
osbwrite(line, outfile, ptr);
}//next vn
}//goto nextrecord
/////
//exit:
/////
outfile.osclose();
// call uconvfile(outfile, "CODEPAGE", "UTF16", result, errors);
//general result code
SYSTEM.r(34, 1);
//exit3:
// if (raw and exportable2) {
// mvgroupno += 1;
// if (mvgroupno == 1) {
// mvgroupno = 2;
// }
// goto nextmvgroup;
// }
return 1;
}
