Hello,
we are currently using adobe approval 5.0 to create adobe e-forms, when a user launces a e-form, it uses a javascript to talk to a MS SQl database server via ODBC connection to auto poputae some clinet information based on the client # user entered.
we need to move away from approval 5.0 adobe reader 9 and up, so far we couldn't get adobe reader 9 to talk to a ms sql databse server via ODBC connetion.
I need some diretion here to choose the cost effctive way to create e-forms based on the client information in the database server.
Please help
The js script we are using:
// JScript source code
//// March 17, 2010
global.ConnectionDB=null;
app.IDname = identity.loginName;
//Adding SaveAsPDF to menu
app.addMenuItem({ cName: "SaveAsPDF", cParent: "File",
cExec: "doSaveAsPDF();",
nPos: 0});
//Saving file as PDF with BRN Name Filename format.
function doSaveAsPDF() {
var brnflag = 0 ;
try {
var brn = this.getField("brn").valueAsString;
var temp = brn.replace(/^\s+|\s+$/g,"");
brn = temp;
if(brn.length < 7) {
app.alert("...Please input BRN...Could not Save!...");
brnflag = 1;
}
else { brnflag = 0; }
}
catch(e1) {
app.alert(e1);
}
if(brnflag == 0) {
try {
var brn = this.getField("brn").valueAsString;
var temp = brn.replace(/^\s+|\s+$/g,"");
brn = temp;
var name = this.getField("name").valueAsString;
var f = this.getField("fileName");
var fName = f.userName;
var savePath = "/c/completed forms/" + brn + "-" + fName + " " + name + ".pdf";
if(fName.indexOf('*') != -1) {
fName = fName.split('*');
savePath = "/c/completed forms/" + brn + "-" + fName[0] + " " + name + " " + fName[1] + ".pdf";
}
if(((brn == null)||(brn ==""))&&((name == null)||(name == ""))) {
app.alert("Missing BRN Number/Name...Could not Save!");
}
else {
this.saveAs({
cPath: savePath,
bCopy: false,
});
app.alert({cMsg:"SAVED!", cTitle:"E-Form Save Window",nIcon:2});
}
}
catch(e) {
app.alert(e);
}
}
}
//connecting to Database
function connectForm() {
try {
ConnectionDB = ADBC.newConnection("CHRIS_DB");
if (ConnectionDB == null) throw "Could Not Connect!";
else { this.validateBRN(); }
}
catch(e) {
app.alert(e);
}
}
//validating BRN number
function validateBRN() {
var flag = 0;
var clflag = 0;
try {
statementBRN = ConnectionDB.newStatement();
if (statementBRN == null) throw "Could Not Run Query!";
BRNquery = "SELECT * FROM dbo.AdHoc_Referrals WHERE Reference_Number=" + "'" + this.getField("brn").valueAsString + "'";
statementBRN.execute(BRNquery);
global.forBRN = new Array();
var a = 0;
var ctnId;
try {
for(var a = 0;;) {
if(statementBRN.nextRow()) throw "BRN ERROR!";
rowBRN=statementBRN.getRow();
var aBRN = rowBRN.Client_Id.value;
if(aBRN == null) { aBRN = ""; }
global.forBRN[a] = aBRN;
ctnId = aBRN;
break;
a++;
}
}
catch(e) {}
}
catch(e) {
flag = 1;
}
if(flag!=1) {
try {
ConnectionDB = ADBC.newConnection("CHRIS_DB");
statementCT = ConnectionDB.newStatement();
if (statementCT == null) throw "Could Not Run Query!";
CTquery = "SELECT Client_Name,Client_Number,cast(DOB as char)dob,HCN,HCN_Version, Firstname, Surname, Salutation FROM dbo.AdHoc_Clients WHERE Client_Id=" + "'" + ctnId + "'";
statementCT.execute(CTquery);
if(statementCT.nextRow()) throw "ADDRESS ERROR!";
rowCT=statementCT.getRow();
}
catch(e) { flag = 1; }
}
if(flag!=1) {
try {
ConnectionDB = ADBC.newConnection("CHRIS_DB");
statementADD = ConnectionDB.newStatement();
if (statementADD == null) throw "Could Not Run Query!";
ADDquery = "SELECT * FROM dbo.AdHoc_Client_Addresses WHERE Client_Id=" + "'" + ctnId + "' AND Address_Type_Id = 2 AND Is_Active = 1";
statementADD.execute(ADDquery);
if(statementADD.nextRow()) throw "ADDRESS ERROR!";
rowADD=statementADD.getRow();
}
catch(e) { app.alert("No Home Address Found...Please Enter The Missing Information...");}
}
if(flag!=1) {
try {
ConnectionDB = ADBC.newConnection("CHRIS_DB");
statementPH = ConnectionDB.newStatement();
if (statementPH == null) throw "Could Not Run Query!";
PHquery = "SELECT * FROM dbo.AdHoc_Client_EContacts WHERE Client_Id=" + "'" + ctnId + "' AND EContact_Type LIKE 'Home'";
statementPH.execute(PHquery);
if(statementPH.nextRow()) throw "ADDRESS ERROR!";
rowPH=statementPH.getRow();
}
catch(e) { app.alert("PHONE NUMBER NOT FOUND...Please Enter The Missing Information...");}
}
if(a > 1) {
app.alert("...DUPLICATE BRN FOUND!...PLEASE FILL IN MISSING INFORMATION!...");
}
if(flag == 1) {
app.beep();
nButton=app.alert( {cMsg: "No Matching BRN Found.... Do You Wish To Try Again ?",
cTitle: "Central CCAC E-Form", nIcon: 2, nType: 2});
if(nButton==3) {
try { this.getField("name").value = "";} catch(e) {}
try { this.getField("dob").value = "";} catch(e) {}
try { this.getField("clientNum").value = "";} catch(e) {}
try { this.getField("address").value = "";} catch(e) {}
try { this.getField("city").value = "";} catch(e) {}
try { this.getField("postalCode").value = "";} catch(e) {}
try { this.getField("phoneNum").value = "";} catch(e) {}
try { this.getField("caseLoad").value = "";} catch(e) {}
try { this.getField("healthcard").value = ""; } catch(e) {}
try { this.getField("letter_name").value = ""; } catch(e) {}
try { this.getField("letter_address1").value = ""; } catch(e) {}
try { this.getField("letter_address2").value = ""; } catch(e) {}
try { this.getField("letter_address3").value = ""; } catch(e) {}
// Populating the Case Manager's name
try {
var HasDot = app.IDname;
if(HasDot.indexOf('.') != -1) {
var CM_Name = app.IDname.split(".");
this.getField("Signature").value = CM_Name[0].charAt(0).toUpperCase()+ CM_Name[0].substr(1, CM_Name[0].length) + " " + CM_Name[1].charAt(0).toUpperCase()+ CM_Name[1].substr(1, CM_Name[1].length);
}
else {
this.getField("Signature").value = HasDot.replace(HasDot.charAt(0), HasDot.charAt(0).toUpperCase());
}
} catch(er) {}
// Populating the Dates
var today = new Date();
today = util.printd("dd/mm/yyyy", today);
try { this.getField("Signature date").value = today; } catch(er){}
this.getField("name").setFocus();
}
else {
try { this.getField("name").value = "";} catch(e) {}
try { this.getField("dob").value = "";} catch(e) {}
try { this.getField("clientNum").value = "";} catch(e) {}
try { this.getField("address").value = "";} catch(e) {}
try { this.getField("city").value = "";} catch(e) {}
try { this.getField("postalCode").value = "";} catch(e) {}
try { this.getField("phoneNum").value = "";} catch(e) {}
try { this.getField("caseLoad").value = "";} catch(e) {}
try { this.getField("healthcard").value = ""; } catch(e) {}
try { this.getField("letter_name").value = ""; } catch(e) {}
try { this.getField("letter_address1").value = ""; } catch(e) {}
try { this.getField("letter_address2").value = ""; } catch(e) {}
try { this.getField("letter_address3").value = ""; } catch(e) {}
this.getField("brn").setFocus();
}
return true;
}
else {
this.fillForm();
}
}
//Filling Form with CHRIS info
function fillForm() {
var dt;
var finalAddr = "";
var finalHcn = "";
try { var name = rowCT.Client_Name.value; } catch(e1) { var name =""; }
try { var db = rowCT.dob.value; } catch(e1) { var dt =""; }
try { var ctNum = rowCT.Client_Number.value; } catch(e1) { var ctNum=""; }
try { var addr = rowADD.Street_Name.value; } catch(e1) { addr =""; }
try { var aptNo = rowADD.Suite_Number.value; } catch(e1) { var aptNo =""; }
try { var strNo = rowADD.Street_Number.value; } catch(e1) { var strNo =""; }
try { var pCode = rowADD.Postal_Code.value; } catch(e1) { var pCode =""; }
try { var cty = rowADD.City.value; } catch(e1) { var cty =""; }
try { var cLoad = rowBRN.Caseload.value; } catch(e1) { var cLoad =""; }
try { var hcn = rowCT.HCN.value; } catch(e1) { var hcn =""; }
try { var hcnVer = rowCT.HCN_Version.value; } catch(e1) { var hcnVer =""; }
try { var phone = rowPH.EContact.value; } catch(e1) { var phone =""; }
try { var letter_name = rowCT.Firstname.value + " " + rowCT.Surname.value; } catch(e1) { var letter_name =""; }
try { var letter_room = rowADD.Room_Number.value; } catch(e1) { var letter_room =""; }
try { var letter_add1 = rowADD.Mailing_Line_1.value; } catch(e1) { var letter_add1 =""; }
try { var letter_add2 = rowADD.Mailing_Line_2.value; } catch(e1) { var letter_add2 =""; }
try { var letter_add3 = rowADD.Mailing_Line_3.value; } catch(e1) { var letter_add3 =""; }
var room = "";
if((letter_room==null)||(letter_room=="")) { room =""; }
else { room = ", Room # " + letter_room; }
if(letter_add3==null) letter_add3="";
if(pCode==null) pCode="";
if(cLoad==null) cLoad="";
if(ctNum==null) ctNum="";
if(cty==null) cty="";
if( phone==null) phone="";
if(db == null || db == "") { dt = ""; }
else {
dt2 = util.scand("mmm d yyyy h:MM tt",db);
dt = util.printd("dd-mm-yyyy",dt2);
}
if(((aptNo == null) || (aptNo == "")) && ((strNo == null) || (strNo ==""))) { finalAddr = addr; }
else if((aptNo == null) || (aptNo == "")) { finalAddr = strNo + " " + addr; }
else if((strNo == null) || (strNo == "")) { finalAddr = aptNo + " - " + addr; }
else {
finalAddr = aptNo + " - " + strNo + " " + addr;
}
if((hcnVer == null) || (hcnVer =="")) { finalHcn = hcn; }
else { finalHcn = hcn + " Ver: " + hcnVer; }
try { this.getField("name").value = ""; } catch(e) {}
try { this.getField("dob").value = ""; } catch(e) {}
try { this.getField("clientNum").value = ""; } catch(e) {}
try { this.getField("address").value = ""; } catch(e) {}
try { this.getField("city").value = ""; } catch(e) {}
try { this.getField("postalCode").value = ""; } catch(e) {}
try { this.getField("healthcard").value = ""; } catch(e) {}
try { this.getField("caseLoad").value = ""; } catch(e) {}
try { this.getField("phoneNum").value = ""; } catch(e) {}
//fields for letters
try { this.getField("letter_name").value = ""; } catch(e) {}
try { this.getField("letter_address1").value = ""; } catch(e) {}
try { this.getField("letter_address2").value = ""; } catch(e) {}
try { this.getField("letter_address3").value = ""; } catch(e) {}
try { this.getField("name").value = name; } catch(e) {}
try { this.getField("dob").value = dt; } catch(e) {}
try { this.getField("clientNum").value = ctNum; } catch(e) {}
try { this.getField("address").value = finalAddr; } catch(e) {}
try { this.getField("city").value = cty; } catch(e) {}
try { this.getField("postalCode").value = pCode; } catch(e) {}
try { this.getField("healthcard").value = finalHcn; } catch(e) {}
try { this.getField("caseLoad").value = cLoad; } catch(e) {}
try { this.getField("phoneNum").value = phone; } catch(e) {}
//fields for letters
try { this.getField("letter_name").value = letter_name; } catch(e) {}
try { this.getField("letter_address1").value = letter_add1 + room; } catch(e) {}
try { this.getField("letter_address2").value = letter_add2; } catch(e) {}
try { this.getField("letter_address3").value = letter_add3; } catch(e) {}
// Populating the Case Manager's name
try {
var HasDot = app.IDname;
if(HasDot.indexOf('.') != -1) {
var CM_Name = app.IDname.split(".");
this.getField("Signature").value = CM_Name[0].charAt(0).toUpperCase()+ CM_Name[0].substr(1, CM_Name[0].length) + " " + CM_Name[1].charAt(0).toUpperCase()+ CM_Name[1].substr(1, CM_Name[1].length);
}
else {
this.getField("Signature").value = HasDot.replace(HasDot.charAt(0), HasDot.charAt(0).toUpperCase());
}
} catch(er) {}
// Populating the Dates
var today = new Date();
today = util.printd("dd/mm/yyyy", today);
try { this.getField("Signature date").value = today; } catch(er){}
}
