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, 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;
}
function main(in mode, in subject0, in body0, in groupids0, in jobids0, in userids0, in options, io emaillog) {
//options
//R = REPLYTO=@username email address if exists
//W = Groups by Word eg user with dept MEDIA BUYING matches group MEDIA
var interactive = not SYSTEM.a(33);
if (false) print(jobids0);//evade compiler warning on unused argument
if (mode.field(" ", 1) eq "UPGRADE") {
var subject = "NEOSYS Upgrade: " ^ SYSTEM.a(23);
if (SYSTEM.a(17) ne SYSTEM.a(23)) {
subject ^= " (" ^ SYSTEM.a(17) ^ ")";
}
var version = mode.field(" ", 2);
subject ^= version;
var body = "";
body ^= "The NEOSYS system software has been upgraded.";
body ^= VM;
body ^= VM ^ "Before you login to NEOSYS, please follow the instructions at";
body ^= VM ^ "http://userwiki.neosys.com/index.php/cache to avoid errors using NEOSYS.";
body ^= VM;
body ^= VM ^ "Please email [email protected] for any assistance.";
//body:=vm
//body:=vm:'This is an automated email. You cannot reply to it.'
body.converter(VM, var().chr(13));
call emailusers(mode, subject, body, "", "", "", "R", emaillog);
if (not emaillog) {
emaillog = "(nobody)";
}
emaillog = "Upgrade Notification emailed to:" ^ VM ^ emaillog;
emaillog.swapper(VM, var().chr(13));
call sysmsg(emaillog, "Upgrade to version " ^ version);
return 0;
} else if (mode ne "") {
var msg = DQ ^ (mode ^ DQ) ^ " is invalid in EMAILUSERS";
if (interactive) {
call mssg(msg);
}else{
call sysmsg(msg);
}
return 1;
}
//init:
//if target and options='' or index(options,'U',1) then
// end
var groupword = options.index("W", 1);
nsent = 0;
var subject = subject0;
var body = body0;
//read fromuser from users,@username else fromuser=''
var replyto = "";
if (options.index("R", 1)) {
if (USERNAME == "NEOSYS" or USERNAME == "ADAGENCY" or USERNAME == "ACCOUNTS") {
replyto = "*****@*****.**";
}else{
var fromuser = USERNAME.xlate("USERS", "", "X");
replyto = fromuser.a(7);
var fromline = "From " ^ fromuser.a(1);
if (USERNAME ne fromuser.a(1)) {
fromline ^= " (" ^ USERNAME ^ ")";
}
subject.splicer(1, 0, fromline ^ " : ");
}
}
var usercodes = SECURITY.a(1);
var nusers = usercodes.count(VM) + 1;
var usern = 0;
emaillog = "";
var alreadyemailed = "";
body.converter(FM ^ VM, var().chr(13) ^ var().chr(13));
var groupids = groupids0;
groupids.converter(",", VM);
var ngroups = groupids.count(VM) + 1;
var userids = userids0;
userids.converter(",", VM);
toemails = "";
ccemails = "";
var currdept = "";
var users;
if (not(users.open("USERS", ""))) {
call fsmsg();
return 0;
}
for (usern=1; usern<=nusers;++usern) {
//interrupt
if (esctoexit()) {
break;
}
//skip empty users
var usercode = usercodes.a(1, usern);
if (usercode == "") {
continue;
}
//only users on file
var USER;
if (not(USER.read(users, usercode))) {
continue;
}
//not expired users
var expirydate = USER.a(35);
if (expirydate and expirydate <= var().date()) {
continue;
}
//skip users with no email at all
//users may have 0 or more email addresses eg [email protected];[email protected] etc
var emails = USER.a(7);
if (emails == "") {
continue;
}
//always email to self last
if (usercode == USERNAME and replyto) {
continue;
}
var ok = 0;
if (not ok and userids) {
if (userids.locate(usercode, xx, 1)) {
ok = 1;
}
if (not ok and not groupids) {
continue;
}
}
//skip users not of required type (eg FINANCE is ok in FINANCE CONTROLLER)
//could determine user type from what menus they can access eg MEDIA
if (not ok and groupids) {
if (groupword) {
//eq search for MEDIA in user department like MEDIA BUYER
for (var groupn = 1; groupn <= ngroups; ++groupn) {
ok = (USER.a(5)).index(groupids.a(1, groupn), 1);
if (not(not ok))
break;;
};//groupn;
}else{
//exact groups
if (groupids.locate(USER.a(21), xx, 1)) {
ok = 1;
}
}
if (not ok) {
continue;
}
}
//must be last to avoid adding emails to sent list unless actually sent
//remove any emails that have already been emailed before
emails.converter(" ", "");
emails.converter(";", VM);
var nn = emails.count(VM) + (emails ne "");
for (var ii = nn; ii >= 1; --ii) {
var email = emails.a(1, ii);
if (alreadyemailed.locateusing(email, VM)) {
emails.eraser(1, ii);
}else{
alreadyemailed ^= VM ^ email;
}
};//ii;
emails.converter(VM, ";");
//skip users that have already been emailed before
if (emails == "") {
continue;
}
//userinit:
print(usercode, " ");
if (currdept and USER.a(5) ne currdept) {
gosub sendemails(emaillog);
}
currdept = USER.a(5);
if (toemails == "") {
toemails = emails;
//sending to users (or groups and users)
} else if (userids) {
toemails ^= ";" ^ emails;
//sending to groups then to the first and cc the rest
}else{
ccemails ^= ";" ^ emails;
}
emaillog ^= VM ^ usercode ^ " " ^ emails;
}//usern
//exit:
gosub sendemails(emaillog);
//trim trailing . vm and fm
emaillog.trimmerb("."^VM^FM);
//always email to self
if (nsent and replyto) {
toemails = replyto;
body ^= VM ^ VM ^ "-- Sent to --" ^ emaillog;
body.swapper(VM, var().chr(13));
gosub sendemails(emaillog);
}
if (not nsent) {
emaillog = "";
}
//trim trailing . vm and fm
emaillog.trimmerb("."^VM^FM);
return 0;
}