0 Replies Latest reply: Nov 11, 2010 11:48 AM by Randy70 RSS

    Creating Adobe forms with reader 9 and connecting with database

    Randy70 Community Member

      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){}
      }