Friday Aug 03, 2007

PDF Data Entry - Part IV

 

Gums still aching here in stormy Colorado but Im finally bringing things together into a single servlet to receive, process and insert the data. Last time I mentioned that we needed to get an employee id from the appropriate database sequence prior to executing the 'insert' command.


The following code will retrieve the next ID from the employees sequence:


 

    private int getEmpID()
    {
      int empID = 0;
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
        // fetch the report name based on the request id
        PreparedStatement getID = conn.prepareStatement("select employees_seq.nextval from dual");
        
        // get the query result in to a result set and then assign the
        // value to a variable we can pass back to the calling method
        ResultSet idRslt = getID.executeQuery();
        idRslt.next();
        empID = idRslt.getInt(1);
        
        //Clean up 
        idRslt.close();
        getID.close();
        conn.close();
        
      }
      catch (SQLException eSQL)
      {
        System.err.println("Could not create connection");
        eSQL.printStackTrace();
      }
      catch (Exception e)
      {
       System.err.println("Exception thrown");
       e.printStackTrace(); 
      }
       
      return empID;
    } 

 


This assumes we have a method called getConnection() to get a connection to the database. Now we have the employee ID we can correctly construct the sql statement to insert a new employee. Now we can complete the insert statement string by adding the employee id and its value


 

        //Add the emp id column and value
         sqlColsBuffer.append(",EMPLOYEE_ID");
         sqlValsBuffer.append(","+Integer.toString(getEmpID()));
        
        sqlStmtBuffer.append(sqlColsBuffer.append(")").toString());
        sqlStmtBuffer.append(sqlValsBuffer.append(")").toString());
This gets us the followning statement

insert into employees(DEPT_ID,SALARY,EMAIL,LAST_NAME,PHONE_NUMBER,
                                   JOB_ID,HIRE_DATE,FIRST_NAME,EMPLOYEE_ID) 
values ('50','44444','tim.dexter@oracle.com','Dexter','343434343'
                                 ,'ST_MAN','01jan2003','Tim',214)

 


If you look closely you'll see that the hire date is in the wrong format - we need to force the user to enter it in the correct format otherwise our insert will fail. Something else you may have noticed is that DEPT_ID is not the column name in the EMPLOYEES table it should be DEPARTMENT_ID. Watch out! Either match the field names in the PDF form to the column names or do the conversion in your code to the right names. 


Now we can insert the record:


 

    private boolean insertRecord(String sqlStmt){
        boolean insFlg = true;
        Connection conn = getConnection();
        try
        {
        Statement stmt = conn.createStatement();
        stmt.execute(sqlStmt);
        conn.commit();
        stmt.close();
        conn.close();
        
        }
        catch (SQLException sqle){
            sqle.printStackTrace();
            insFlg = false;
        }
    return insFlg;   
    }

 


We pass the SQL statement in as a parameter when we call the insert method:


 

        if (!insertRecord(sqlStmtBuffer.toString())){
            
            out.println("Error: 45500: The employee registry could not be" +
            "updated with your information.<BR>" +
            "Please contact as administrator on x66633 for help.");
        }

 


I have the method returning a boolean so that I can inform the user if the insertion fails and provide a message to them to contact an administrator.
To prove it worked, here I am in the table as a new employee ...


EmpDataForm4:


A nice surprise the date got converted as the record was inserted ... there is a trigger on the table.


Hope you found this series useful or at least interesting, Publisher has a lot to offer to help with data entry via PDF. With some java coding you can build a system quite quickly, I built this demo in about a day. I went for simple db connections and servlets just to allow me to focus on the publisher aspects, you of course can use other methods and be a lot tighter on your server side validation.
The XFDF XML format does have a great advantage in that its a fixed schema so you could conceivably build multiple forms that feed into a single servlet on the server and then it can redirect to table handlers based on some hidden field value.
I have to thank Incheol Kang from the development team for some help along the way and we got 2 enhancement requests out of this exercise so it was useful for us. If you are interested in the source code its all in this zip file.
Happy Data Entering!

 

Wednesday Aug 01, 2007

PDF Data Entry - Part III

[Read More]

Monday Jul 30, 2007

PDF Data Entry - Part II

 

Last time we looked at building and pre-filling the PDF data entry form. We now need to look at how we can allow the user to 'submit' the data they have entered back to the server and to then let them know that their data has been received.


There are a several of ways that Adobe will allow you to post data from a form.

EmpDataForm3:

Im using Adobe 6 you may be on another version, things should be similar thou. The properties dialog, for a button reveals the folloiwn submission methods:



  • FDF - this is Adobe's 'Form Data Format' - you can get more info on this here. It's a proprietory format for sending (and receiving) form data and comments. Im not going to deal with this format.
  • HTML - need I say more except Im not going to deal with this either
  • XFDF - XML Forms Data Format. Yep, you guessed it, it's Adobe's proprietory XML format for the forms data - now this we can and will use. Not the friendliest format but guess what? We have an API for that.
  • PDF - you can just submit the complete PDF form. This is useful if you wanted to store a copy of a form created by a user. Even better if they signed the form with a digital signature - as good as a paper signature. Of course we need to scrape the data out of the form too - yep, we have an API for that too and we're going to try that too.

Now I guess you could use the HTML format and Adobe provides an SDK to parse the FDF format but why bother, Publisher has all you need for for the last two formats and they have enough information.


Using the properties dialog we can choose XFDF or PDF. Initially I chose XFDF, I need to POST the XFDF to some CGI process on a web server. I elected to use a servlet. Looking at the graphic above you'll see I have entered a URL. Therein lies an issue, I have hardcoded the URL on the button - you got it. We have an API for that, now I have to admit this one is hidden and again I lent on Incheol for information.


We have an undocumented class FieldEditor, as the name suggests you can edit fields with it - that includes adding fields too. So, we can  create the button in its entirity and enter the submission destination and format and then put it on the page.


We first need to instantiate a PDFParser object, this takes the file we want to edit as an input. We then create a FieldEditor object and add the button and then re-generate the form. The addButton method takes a series of parameters.

            addButton(btnName(String),
                      btnLabel(String),
                      btnfontSize(float),
                      btnCode(String),
                      btnCoords(float[],
                      btnPage(int),
                      btnType(int))

Most I hope are self explanatory except maybe:
btnCode - you can create a submit or javascript type button and here is where you pass either the


            //Add the button
            float[] btnCoords = {350f,350f,470f,380f};
            float fontSize = 16f;
            String btnName = "SUBMIT_BTN";
            String btnLabel = "Submit";
            String btnDest = "http://www.oracle.com";
            int btnPg = 0;
            //Create a PDF Parser instance
            PDFParser pdfP = new PDFParser(pdfForm);
            //Create a FieldEditor instance
            FieldEditor fldEd = new FieldEditor(pdfP);
            //Add the button
            fldEd.addButton(btnName,btnLabel,fontSize,btnDest,btnCoords
               ,btnPg,fldEd.BUTTON_ACTIONTYPE_SUBMIT_FORM);
            //Regenrate the form
            fldEd.generatePDF(pdfForm);

Now the only drawback is that the API does not let you influence the look and feel of the button, so you get a flat button ... not exactly a nice beveled 'clickable' affair but it gets the job done. Before you ask, no there is not a method to update a button. We can look into that if enough of you scream loud enough. I also have to admit that we do not give you the control over the posting format of the button e.g. XFDF, PDF, etc. It defaults to HTML ... dang - Im logging an enhancement for this today.


Apologies for this tangent we got on to with buttons that led down a rather disappointing cul-de-sac. Next time, we get into receiving the contents of PDF via a servlet and letting the user know we got their information. 

 

Thursday Jul 26, 2007

PDF Data Entry - Part I

I have been nagged by many folks for a how-to on data entry via PDF forms. A certain competitor provides the ability to create a PDF form and then allow users to enter data via Adobe Acrobat or Reader and push it into the application tables. Well now you can do it with E Business Suite, I should add its not out of the box, there are a few applications teams looking at this and a few have already started to provide this type of functionality. I indebted to Incheol on our development staff for help on some of the finer points ... Publisher is getting so big it's tough to keep up with all its capabilities. 


Some folks will be asking 'whats the point?, there are forms and pages provided by the application for data entry, why not use those?'

It's a fair question but when you think about entering orders or invoices into a system your users may want a faster medium than Oracle Forms to get the data in ... the more obvious choice would be Excel - I'll cover that in another post. How about wanting to provide your customers or suppliers the opportunity to enter data into your system but you do not want them to use the professional apps. Or maybe you want to collect  I-9 information from your new hires and rather than develop a user interface, why not use the govenrment provided I-9 form.  

Here's where a PDF form can be very handy, its a reasonably familiar format for users now, Adobe Reader is almost ubiquitous on the desktop so minimal install for them and you have the power to make the form as simple as you like.


Im going to split this article into a few sections, 1 to save you reading and 2 to save me writing massive amounts of editorial. Im going to start with creating the PDF form itself. Sounds simple enough and ought not to warrant an article to itself I hear you say. But Publisher a few tricks up its sleeve that can prove very handy.


Let's assume you want to collect employee information using a PDF form.


EmpDataForm1:


Building the form is going to require either Adobe Acrobat 5 or higher. If you are building a form from scratch rather than using a 3rd party form e.g. I-9 you typically do not start with a blank page. Use Word, scan in a form or similar to build a layout and then add the data entry fields to the form. 


There may well be some information you already know, in our case, the list of job descriptions, manager and department names. You could hard code these but things change so if you can make it dynamic so much the better. Fortunately publisher can help you here, it has the ability to populate fields and combo boxes for you.

EmpDataForm2:


The table we want to populate is the EMPLOYEES table in the demo HR schema.

Name              Null     Type
----------------- -------- ------------
EMPLOYEE_ID       NOT NULL NUMBER(6) 
FIRST_NAME                 VARCHAR2(20)
LAST_NAME         NOT NULL VARCHAR2(25)
EMAIL             NOT NULL VARCHAR2(25)
PHONE_NUMBER               VARCHAR2(20)
HIRE_DATE         NOT NULL DATE
JOB_ID            NOT NULL VARCHAR2(10)
SALARY                     NUMBER(8,2) 
COMMISSION_PCT             NUMBER(2,2)
MANAGER_ID                 NUMBER(6)
DEPARTMENT_ID              NUMBER(4)


Notice that the table does not store job, manager and department names but rather the relevant IDs. Therefore when we populate the combo boxes we need to extract the value (for the user to select) and the 'id' so that the table can be updated with the 'id' rather than the name.


We can use the FormProcessor API to populate the combo boxes:

    String pdfTemplate = "D:\\Work\\DataEntry\\EmpForm.pdf";
    String pdfData = "D:\\Work\\DataEntry\\Data.xml";
    String pdfForm = "D:\\Work\\DataEntry\\EmpFormEntry.pdf";
    
        try {
         System.out.print("Starting to fill form ...");
           
           FormProcessor fp = new FormProcessor();
           fp.setTemplate(pdfTemplate);
           fp.setOutput(pdfForm);
           fp.setData(pdfData);





           Properties prop = new Properties();
           prop.setProperty("all-field-readonly","false");
           fp.setConfig(prop);            
           fp.setLOV("JOB_ID",getJobHash());
           fp.setLOV("MANAGER_ID",getMgerHash());
           fp.setLOV("DEPT_ID",getDeptHash());

           fp.process();
        } 
        
        catch (Exception ex){
            ex.printStackTrace();
        }

Its pretty straightforward, we need to use some dummy data with the PDF template (form). If you had fields that you could pre-populate you would put this data into the XML. In our case we did not have any. 


The two main things to notice here are:
1. We need to set the "all-field-readonly" property to false. Without this the fields in the resulting form will be set to read only.
2. We are using the setLOV method to populate the combo boxes in the PDF form. It has a couple of signatures but Im using the following:


setLOV(java.lang.String fieldName, java.util.Hashtable values)


I have fields named JOB_ID, MANAGER_ID and DEPT_ID. To populate the hashtables for each I connect to the database and select the appropriate data. For the MANAGER_ID hash I have:

private Hashtable getMgerHash()    {
      int mgerID;
      String mgerName = "";
      //Hsh table for manager information
      Hashtable mgerHash = new Hashtable();
      System.out.println("Loading Managers \n");
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
       // Fetch the emp id and name for those emps that are managers
        PreparedStatement getHash 
                = conn.prepareStatement("select distinct e.employee_id ID," +
                                      "e.first_name||' '||e.last_name NAME \n" + 
                                      "from employees e, employees f\n" +
                                      "where f.manager_id = e.employee_id");
       
        ResultSet locRslt = getHash.executeQuery();
        // loop thru the dataset and load values to the hashtable
        while (locRslt.next()){
        
            mgerID = locRslt.getInt(1);
            mgerName = locRslt.getString(2);
            mgerHash.put(String.valueOf(mgerID),mgerName);
           }
        //Clean up 
        locRslt.close();
        getHash.close();
        conn.close();
      }
      catch (SQLException eSQL)
      {
        System.err.println("Could not create connection");
        eSQL.printStackTrace();
      }
      catch (Exception e)
      {
       System.err.println("Exception thrown");
       e.printStackTrace(); 
      }
       
       return(mgerHash);
    }    

This passes a filled hashtable back to the main code:

           fp.setLOV("MANAGER_ID",getMgerHash());
Once the code completes we have pre filled the form with the latest combo box values for the user to select appropriate values. They will select a value from the list but the id will be stored.
Publisher can even help you inject javascript into the form as well. I have not included a sample here but check out the the JavascriptEditor class in the javadocs. You can inject/replace javascript at document and field level. 
You can get the starting PDFForm, the resulting pre-populated form and the java class here.


You'll have noticed the Submit button ... more on that next.

About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today