Friday, August 11, 2017

Importing CSV via Java to a Notes Database

It's been longer than I intended since my last post, but here it is.

One of the things I've posted has been importing to a Notes database. My first was using LotusScript to import from an Excel file, I've moved to using CSV which does not require an external program. This one uses a Java bean and HashMap to map the field names.

As with the others, this takes two files. First is a simple where the you have the data. The column titles will match up with the same row in a title spreadsheet. The title spreadsheet has two rows. The first has the column titles form the first and the second is the field name you want to import to.

Here is a sample of the import title:

Alpha,Beta,Gamma,Delta,Epsilon
AlphaField,BetaField,GammaField,DeltaField,EpsilonField

Here is a sample of the data file:

Alpha,Beta,Gamma,Delta,Epsilon
First1,Second1,Third1,Forth1,Fifth1
First2,Second2,Third2,Forth2,Fifth2

Here is the bean:

package com.something;

import java.io.*;
import java.util.*;
import javax.faces.context.*;
import lotus.domino.*;
import org.apache.commons.lang.*;

public class ImportMap implements Serializable {

private static final long serialVersionUID = 1L;

public ImportMap() {

}

public void ImportMappedData(String dataFile, String fieldMapFile, String formName) {
try {

BufferedReader fieldMapReader = new BufferedReader(new FileReader(fieldMapFile));
String fieldMapTitleLine = fieldMapReader.readLine();
String fieldMapFieldLine = fieldMapReader.readLine();
// will need to account for different numbers of columns
HashMap hm = new LinkedHashMap();

String[] splitTitle = fieldMapTitleLine.split(",");
String[] splitField = fieldMapFieldLine.split(",");
for (int i = 0; i < splitTitle.length; i++) {
hm.put(splitTitle[i], splitField[i]);
// System.out.println("Title: " + splitTitle[i]);
}
// Get a set of the entries
Set set = hm.entrySet();
// Get an iterator
Iterator i = set.iterator();
// Display elements
while (i.hasNext()) {
Map.Entry me = (Map.Entry) i.next();
}

BufferedReader dataMapReader = new BufferedReader(new FileReader(dataFile));
String dataMapTitleLine = dataMapReader.readLine();
String dataMapDataLine = dataMapReader.readLine();
String[] splitDataTitle = dataMapTitleLine.split(",");

Session session = (Session) getVariableValue("session");
Database db = session.getCurrentDatabase();
lotus.domino.Document importDoc = null;

while (dataMapDataLine != null) {
boolean saveDoc = false;
importDoc = db.createDocument();
importDoc.replaceItemValue("form", formName);
String[] dataMapData = dataMapDataLine.split(",");
for (int j = 0; j < splitDataTitle.length; j++) {
if (hm.containsKey(splitDataTitle[j])) {
try {
saveDoc = true;
importDoc.replaceItemValue(StringUtils.trimToEmpty(hm.get(splitDataTitle[j]).toString()), StringUtils.trimToEmpty(dataMapData[j]));

} catch (Exception Ae) {
// System.out.println("no value to write for " + splitDataTitle[j] );
}

}
}

if (saveDoc) {
importDoc.save();
}

importDoc.recycle();
dataMapDataLine = dataMapReader.readLine();
}

incinerate(importDoc, db);
} catch (Exception e) {
e.printStackTrace();
}
}

private void incinerate(Object... dominoObjects) {
for (Object dominoObject : dominoObjects) {
if (null != dominoObject) {
if (dominoObject instanceof Base) {
try {
((Base) dominoObject).recycle();
} catch (NotesException recycleSucks) {
// optionally log exception
}
}
}
}
}

public static Object getVariableValue(String varName) {
FacesContext context = FacesContext.getCurrentInstance();
return context.getApplication().getVariableResolver().resolveVariable(context, varName);
}
}

This one uses the Apache String Utils to strip out the extra spaces that might be in, and leave an empty string if nothing is found. You can take that out if you don't have the Jar or don't want to use it. My import process doesn't account for commas within a quoted string. I know that should be part of the process but it wasn't needed for my project. I'd like to find a way to address it. Currently I'm just using Split to break each row apart based on commas, but at some point I'd like to create something (possibly using RegEx) that accounts for the commas in a quoted string. I would suggest running something to remove extra line breaks. I had to write a script to remove them from Numbers. Otherwise you may get partial lines trying to import.

Cheers,
Brian

Tuesday, June 21, 2016

HTML5 Canvas to PNG via RPC

Declan Lynch provided a Signature Capture Control on OpenNTF some time ago. I had downloaded and played with it a bit, but hadn't had a production use for it, but it worked just like it said on the tin - drop it in and use it.

Recently I was asked to come up with a way to let people sign into an event using tablets, so a perfect opportunity to pull it out. It was a breeze to add it to the sign-in portion, we display a page on a mobile device and the user can sign in on the canvas. That was the "Wow" part of my initial presentation and his work let it go off not only without a hitch, but with next to no work on my part.

The next phase to come up is to capture the signatures as images so they can be exported and stored. Declan's control saves the co-ordinates in a text field. Here I discovered that the HTML5 canvas (which is what the control uses) has a method, toDataURL, that translates into a base64 string that can then be converted to an image. PNG is the default, but JPG also seems an option (I left it as PNG). I have put that in a CSJS button that calls a RPC that has a function that takes the string and converts it to an image, attaching that image to the document.

A few notes:

  1. I'm "cheating" on using CSJS to get the element in my sample. It's a simple page so the element is always generating the same ID. You will probably want to change that so you can use it anywhere. 
  2. The returned string starts with "data:image/png;base64," so my SSJS function strips that out.
  3. My PRC returns an alert that it is done, you can easily comment that out. 
I've not decided how I'm going to implement this yet, button clicking won't do for my workflow, but I can change that to some other event to trigger the process.

Here is a link to a document with the full XPage and the function I call.  

Cheers,
Brian



Monday, May 23, 2016

Eternal fustrations with IBM "Help" - - this time trying to give them money

So I find I need to purchase a Domino license again, this happens for independent developers. I make my selection and get taken to what IBM is now calling the "Marketplace" to check out. However the option to enter a credit card to actually pay for my purchase is greyed out. So I call in. There is a wait and a lady answers. She asks the typical questions and then for me to send them an email with a screen shot. I ask for a ticket number so I can track this request (my reopened ticket for Bluemix is still sitting there unanswered after several days). She tells me they can't open tickets. I tell her I need one to follow this issue. Three times so far she's put me on hold to come back with the same thing - send in an email. I still want a ticket number.

Four times now....(I'm writing this while being on hold)....Five times. Finally I got a ticket number. It's a bit different from the ones I'm used to from IBM, but here we go.

The lady did actually give me her full name (which is rare), but you have to wonder what customer service expert came up with the idea that people want to be forever told to go elsewhere.

Arrgh,
Brian

Tuesday, May 3, 2016

Simple Example: Bootstrap

The Bootstrap library is a great way to do responsive design, and it's been incorporated into the Extension Library so you can use it "out of the box". The problem I've found is that the samples provided are pretty complex. Not too helpful if you are starting out since you have to try to figure out callbacks and a lot of other stuff to get to the points you want.

I think overly complex starter examples are a waste. They let the creator think they have provided something without actually helping the new person find their way. If you wanted the BootStrap Navbar, for example, it's hard to find that element in a way you can just use it and figure out how to get fancy later.

I've been taking a course that includes Bootstrap, and it's helped me figure out how it works. So I decided to so create a database showing some of the basics so someone moving to Bootstrap (especially from traditional Notes or non-Bootstrap XPage work). It's not designed to show everything, but to help out in showing some of the basics. I think once someone gets these under their belt, the rest will come more easily.

Here is what is in this example, all in separate XPages so you can see just that.

  1. NavBar: the useful top. This one includes some links that collapse to the burger menu when on a mobile device. Also I've included a glyph in the upper right which is common and looks good. 
  2. Jumbotron: The big top on a lot of websites, and with a button to go somewhere. It also shows how to use the Bootstrap styling of a button "btn btn-primary", you can put in "btn btn-success" for the green one, for example. 
  3. Well/InputForm: This is my most complex entry here. This gives the nice appearance where there is  grey box (the "well") with labels and fields and a nice button. 
    1. Put everything in a container, there there is a div for the well, then a div for the form group. These combine for the appearance desired.
    2. I use the a "Display Errors" control and style it with the "alert alert-danger" for the expected Bootstrap validation.
    3. I put in a combobox so you can see how it styles as well
  4. ContexturalBackgrounds: these show the colors behind a paragraph or other elements. I have a variety of them. 
  5. Lists: Also showing different colors for list items, list groups, and divs where you can put other elements.
  6. Offset columns: Bootstrap uses a grid system, and I have a page sampling the width and offset. Offset lets you specify the number of columns on either side of the 'populated' column. These allow the resizing needed going from desktops to mobile devices. 
 In the database, change theme to one of the Bootstrap ones provided, like Bootstrap3.2.0.

If you need to use Themes in your application, you can incorporate this one like this:

<theme extends="Bootstrap3.2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="platform:/plugin/com.ibm.designer.domino.stylekits/schema/stylekit.xsd">
   
        <!-- jQuery -->
    <resource>
        <content-type>application/x-javascript</content-type>
        <href>bower_components/DataTables/media/js/jquery-2.2.0.min.js</href>
    </resource>
....(other resources)
</theme>


It's the first node, "theme extends" that does it, incorporating the Bootstrap theme from the Extension library into the theme you need.

It is my hope that this will be easier for someone to see how Bootstrap works in XPages

Here is the database.

Cheers,
Brian

Thursday, March 24, 2016

Import CSVs into a Notes/XPage database

We have not had a direct way to import into Notes since it became impossible to save a file in .123 format (or .wk4). I loved being able to import from a view. To work around this, some years ago I created an agent that used MSExcel and two files. That worked when I had MSExcel on my machines, but I don't any more, especially for my personal machines where I won't pay for it. So I needed to change to to import CSV files, which I can do via LibreOffice. So I updated what I had posted in 2009.
 
 The "data" file was the data to be imported, with the first row being (as is common) some description of what the column contains (e.g. "Name" "Telephone number", etc.). This first row is copied to the other spreadsheet, and in the second row, below each column is the Notes field name that column should be mapped to.

Here is a sample of data, note the first column has titles.


And here is how I would set up the mapping:



Then we just need to import, and here is the agent:
Sub Initialize
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim doc As NotesDocument
    Dim fileName As String
    Dim lastColumn As Integer
    Dim index As Variant
    Dim lastRow As Integer
    Dim row As Integer
    Set db = session.CurrentDatabase
    Dim fileNum As Integer, cells As Integer, k As Integer
    Dim InputStr As String, delimiter As String
    fileNum% = FreeFile()
    Dim titleFileName As String
    Dim parseSize As Double
    Dim fileDataNum As Integer   
    Const titles = "c:\dxl\ImportTitles.csv"
    Const data = "c:\dxl\ImportData.csv"
    Const formName = "Import Form"       
    Dim q As Double
   
    titleFileName = titles
    'Column titles on first row
    'Notes field names on row 2
    delimiter = "," ' Delimiter of your file
    Dim parseArray As Variant
   
    Dim fieldArray() As String
    ReDim Preserve fieldArray(1, index)
    Open titleFileName For Input As fileNum%
    k = 0
    Do While Not EOF(fileNum%)
        Line Input #1,  InputStr$
        parseArray = Split(InputStr$, ",")
        parseSize = UBound(parseArray)
        If(k = 0) Then
            ReDim Preserve fieldArray(1, parseSize)
            q = 0
            Do Until q = parseSize + 1
                fieldArray(0, q) = parseArray(q)               
                q = q + 1
            loop
        Else
            q = 0
            Do Until q = parseSize + 1
                fieldArray(1, q) = parseArray(q)
                q = q + 1               
            Loop
        End If
        k = k + 1
    Loop
    Close fileNum%
           
    fileDataNum% = FreeFile()
    Dim dataFileName As String
    dataFileName = data
   
    Open data For Input As fileDataNum%
    k = 0
    Do While Not EOF(fileDataNum%)
        Line Input #1, InputStr$
        parseArray = Split(InputStr$, ",")
       
        If(k = 0) Then
            'first row, so the titles
            lastColumn = UBound(parseArray)       
            parseSize = UBound(parseArray)   
            Dim x As Integer, y As Integer
            y = 0
            x = 0
            Do While x < (Ubound(fieldArray, 2) + 1)
                Do While y < lastColumn + 1
                    'this determines what column as what title, therefore needs to be mapped To what Field
                    If (fieldArray(0, x) = parseArray(y)) Then
                        fieldArray(0, x) = y
                        GoTo jump
                    Else
                        y = y + 1
                    End If
                Loop
            jump:
                x = x + 1
                y = 1
            Loop   
            k = k + 1        
        Else
            'we are importing data
            x = 0
            Print k
            Set doc = db.CreateDocument
            doc.Form = formName
            Do While x < (UBound(fieldArray, 2) + 1)
                'for each column in array
                If Not (fieldArray(0,x)) = "" Then
                    y = CInt(fieldArray(0,x))
                    'Below will bring in each column value as mapped to the Field (above)
                    Call doc.ReplaceItemValue(fieldArray(1,x), parseArray(y))
                End If
                x = x + 1
            Loop
            Call doc.Save(True, False)
            k = k + 1
        End If
    Loop
    Close fileNum%
End Sub

(I might need to do a little clean up on it, I think I have a few spare Dims)

The column names on the two spreadsheets do not have to be in the same order, as you can see in the pictures, but the column titles to have to be the same. I have not made any attempt to cast case or anything, so they need to be the same case.

I've not done a lot of testing on this yet, I'm relaying on the fact the process worked great in it's previous incarnation. Hopefully this will help someone else. I have discovered that the CSV needs to be clean.

Hopefully this will help someone.

Cheers,
Brian

Tuesday, February 16, 2016

REST via Service Bean


This is based on Custom REST service in Xpages using a service bean by Stephan Wissel. But it doesn't show actually getting prints from the method sent. My work partner Brian Hester  and I both tried at the same time and ended up getting it at the same time – we actually started IM-ing each other that we had it. And this is the day before Bernd Hort posted his presentation.

So to intercept the REST methods, so you can get prints like this:

02/11/2016 09:06:26 AM HTTP JVM: renderService
02/11/2016 09:06:26 AM HTTP JVM: rType: GET
02/11/2016 09:06:40 AM HTTP JVM: renderService
02/11/2016 09:06:40 AM HTTP JVM: rType: POST

Once you get the method, you can have the bean do whatever you would like. It's nice to have it all in one place.

Here is the bean:
package com.companyname;

import java.io.IOException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ibm.domino.services.ServiceException;
import com.ibm.domino.services.rest.RestServiceEngine;
import com.ibm.xsp.extlib.component.rest.CustomService;
import com.ibm.xsp.extlib.component.rest.CustomServiceBean;

public class DynamicViewService extends CustomServiceBean {

public void DynamicViewService() {

System.out.println("init...");

}

@Override
public void renderService(CustomService service, RestServiceEngine engine) throws ServiceException {
System.out.println("renderService");
HttpServletRequest request = engine.getHttpRequest();
HttpServletResponse response = engine.getHttpResponse();

response.setHeader("Content-Type", "application/json; charset=UTF-8");

// Here goes your code, get the response writer or stream
String rType = request.getMethod();
System.out.println("rType: " + rType);
try {
response.getWriter().write("<html><body>" + rType + "</body></html>");
response.getWriter().close();
return;
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println(e.toString());
return;
}
}

}


And here is the full Xpage:

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core"
xmlns:xe="http://www.ibm.com/xsp/coreex">
<xe:restService id="JSONSearch" pathInfo="json" state="false">
<xe:this.service>
<xe:customRestService
contentType="application/json"
serviceBean="com.randstadusa.DynamicViewService">
</xe:customRestService>
</xe:this.service>
</xe:restService>
<xp:br></xp:br>
<xp:br></xp:br>
</xp:view>

Wednesday, January 13, 2016

Reversing the display order of a Multi-value field (XPages)

Today I needed to reverse the display of a multi-value field. It's a log of actions and we needed to show the most recent on top rather then the first added. JavaScript arrays have a reverse() function but when I took the vector I got back it was coming in as an object not an array. I didn't want to spend more time on it, so I decided to reverse the elements in the vector.  I'm showing the results in a repeat.

So what I decided to do was reverse the vector. Below is my code to do it. It takes one vector and puts all the elements into a new vector and returns that:

  var iVector = new java.util.Vector();
    iVector = SSJSgetItemValueSet(doc, "lastresult", iVector);
    var oVector = new java.util.Vector();
    for(var nV=(iVector.size()-1); nV >= 0; nV--){
        oVector.addElement(iVector.elementAt(nV));
    } 

return oVector;
 
SSJSgetItemValueSet is a function I have in a library to assure that I get a vector from a NotesItem. Here is that function:

   function SSJSgetItemValueSet(iDoc:NotesDocument, iItemName:String, iVector:java.util.Vector) {
    //this is designed to see if there is any value in the field, and if so, to get all of it.
    //if there is only one value, still put it in a vector
    //if null, put null in as the value
    //java.util.Vector.size() is the # of elements in the vector
    //call as: iVector = SSJSgetItemValueSet(nDoc, approvedField, iVector);
    //this overloaded method is for when we want to do this from an XPage, and we can't pass a Notes object (like a Doc) into a bean,
    iVector = null; // always set to null
        try {
            if (iDoc.hasItem(iItemName)) {               
                var iItem:NotesItem = iDoc.getFirstItem(iItemName);
                var passObj = getValueAsVector(iItem.getValues());
                iVector = passObj;
            } else {
                iVector = null;
            }
        } catch (e) {
            e.toString();
        }   
    return iVector;
}
Hopefully this will be useful for someone.

Cheers,
Brian