TexasSwede

Lotus Notes is a great tool, and I enjoy developing applications using the RAD capabilities in Domino Designer. But I also create some web applications on occasion.
This past week, when Hurricane Alex was moving in towards Texas, John, the CIO (and my boss), grabbed me first thing Wednesday morning and asked me to build a webpage. He wanted me to use the Google Earth plugin to consuming some KML files he created from the policy database (built using Visual FoxPro).

As I blogged about a year ago, I wrote some code to get the latitude and longitude for an address, and John had then rewrote the code for FoxPro so he could get the coordinates for the addresses he had in his system. So he could now very easily generate a couple of KML files, one of all our policies and one of the policy holders potentially in the path of the hurricane.
I built the webpage in a Domino database, that is usually the easiest way for me to put up a simple webpage. I added some overlays, the two different KML files that John created. I also found a KML file online with different projected paths, as well as one of the current path of Alex. The users could turn on and off these layers as they wanted. Suddenly it was very easy to see if we had any insured customers in the path, etc.

Of course, the same web page could as easily have been created using Notepad or any other tool, as it was pure HTML. But my next plan is to integrate this page with other Domino data, and we are talking about building a generic reporting tool with all different kind of geographic data. Imagine being able to map every accident/insurance claim, see where the accident happens, where the insured is located, etc. Perhaps run statistics showing the average distance from home the accidents take place, etc.

I think it is critical that I, as a Notes/Domino developer, show the power and business use of Notes by integrating it with different systems. When I first started working at this company, I was tasked with building a claim system, to handle insurance claims. The company had hired a pair of Notes consultants, who set up the environment and attempted to build a claim system. They failed, and they also told everyone that Notes and FoxPro could not talk to each other. Of course, when I came in and sat down with my (then) new boss after a few weeks at the new company, we quickly figured out a way to get the systems to communicate.
It took about 90 minutes to come up with the solution (using COM), write the basic specifications (yes, they changed some over the years, but not much), build the COM object and write some test code in Notes. Today, that COM object is used for all kind of things we never envisioned back then, in 2002. It even sends PDF files into our document imaging system.

Of course, it helps to have a CIO/manager who talks and thinks development. I don't have to explain things that are obvious to me, we can go straight at the business logic or functionality. Something that would take 10-15 minutes to explain to a regular manager just takes seconds or a minute.

My next project, which I will start on Monday, is a database to document the claim system. There are so many design elements, with different functionality and access rights. Some buttons are hidden from certain users, using roles or document status, other design elements are restricted in other ways. Very little of this have been documented over the years, and I need to get all that documented in a good way. That is the power of Notes: I have a need, I build a solution.

Today is Father's Day here in the US. In Sweden it is actually celebrated in November (Nov 14 this year). I am thinking about my dad, who passed away in 2001.
My dad was a very intelligent person. He may not have had a fancy education and degree. As a matter of fact, he just had six years of school back in the 1930's (he was born in 1926), then he had to start working. But he could build pretty much anything. He built us a summer house from scratch. He even did the design and the drawings/blueprints himself. At home he had a real architect drawing table. But he also repaired all kind of things down in the basement. He built his own tools, a powerful electrical motor and some pieces of scrap metal, and he had a lathe. When my sister needed a violin, he simply built one. And yes, it sounded great!
After I moved to the United States in 1998, he got a computer, and at age 70 he started learning how to use it and to send email.



But my dad was not always working. Yes, he did work a lot, both to support the family and to (litterally) put a roof over our heads. But he also had time for me and my sister.
We went fishing, sailing (he got us a small sail boat and put me and my sister in sailing classes), and worked in the vegetable patch together with mom.

There are so many things that make me proud of my dad, I can't list them here. But he was a great role model for me, and I hope my son Erik one day will be able to think at me in a similar way. He was also, for a very brief time, a grandfather. He died in March 2001, but not until after he got to see and hold his first and only grandchild. Erik was born in August 2000, and he (together with my sister) visited us in early November.
I was told that he printed out the pictures I mailed him, and was showing them to all his friends. He was so proud! I wish he would have lived a few more years so he could have seen Erik starting to grow up.

 
This picture, and the two previous ones, were taken with my Nikon D90 fitted with a Tamron 18-270mm lens. No tripod was used. The pictures were taken yesterday evening around 19.30 in the Sundance Square area of Ft Worth.  Click on the images to see the full versions.

 

 

Class AmountData
    Public lr As Currency        ' Loss Reserve
    Public er As Currency        ' Expense Reserve
    Public lp As Currency        ' Loss Payments
    Public ep As Currency        ' Expense Payments
    Public slp As Currency        ' Supplemental Loss Payments
    Public sep As Currency        ' Supplemental Expense Payments
    Public rec As Currency        ' Recovery amount
    Public lossavail As Currency
    Public expavail As Currency
    
    
    Public Sub New()
        rec = 0
        slp = 0
        sep = 0
        lr = 0
        er = 0
        lp = 0
        ep = 0
        lossavail = -1
        expavail = -1
    End Sub
    
    Public Sub Load(claimnumber As String, claimant As Integer)
        Dim success As Integer
        Dim servaddress As String
        Dim xmldata As String
        Dim claimlink As ClaimLink    ' Object to connect to backend
        
        Set ClaimLink =  New ClaimLink()   ' Create object/class
        If claimant = 0 Then          ' Get total amounts for all claimants    
            success = claimlink.GetClaimStatus(claimnumber, Today())
        Else                          ' Get amounts for selected claimant
            success = claimlink.GetAmountsByClaimant(claimnumber, claimant, Today())
        End If
        If success = True Then        ' Data returned successfully
            xmldata = claimlink.GetDataXML()
            rec = Cstr(Ccur(XMLGetValue(xmldata,"recovery")))    
            slp = Cstr(0-Ccur(XMLGetValue(xmldata,"losssup")))
            sep = Cstr(0-Ccur(XMLGetValue(xmldata,"expsup")))        
            If claimant = 0 Then     ' All claimants
                lr = Cstr(Ccur(XMLGetValue(xmldata,"lossorig")) + Ccur(XMLGetValue(xmldata,"losschg")))
                er = Cstr(Ccur(XMLGetValue(xmldata,"exporig")) + Ccur(XMLGetValue(xmldata,"expchg")))
            Else                     ' Specified claimant
                lr = Cstr(Ccur(XMLGetValue(xmldata,"lossres")) - Ccur(slp) + Ccur(rec))
                er = Cstr(Ccur(XMLGetValue(xmldata,"expres")) - Ccur(sep))
            End If
            lp = Cstr(0-Ccur(XMLGetValue(xmldata,"losspaid")) - Ccur(slp))
            ep = Cstr(0-Ccur(XMLGetValue(xmldata,"exppaid")) - Ccur(sep))
            lossavail = Ccur(lr) - Ccur(lp)
            expavail = Ccur(er) - Ccur(ep)
        End If    
    End Sub
    
End Class

Class ClaimantStatusData
    Public loss As String
    Public expense As String
    Public claimant As String
    
    Public Sub new(doc As NotesDocument)
        If Lcase(doc.LossClosed(0)) = "yes" Then
            Me.loss = "closed"
        Else
            Me.loss = "open"
        End If
        If Lcase(doc.ExpensesClosed(0)) = "yes" Then
            Me.expense = "closed"
        Else
            Me.expense = "open"
        End If
        If Me.expense = "closed" Then
            If Me.loss = "closed" Then
                Me.claimant = "closed"
            Else
                Me.claimant = "open"
            End If
        Else
            Me.claimant = "open"
        End If
    End Sub
End Class
 

Class ClaimantData
    Public claimantdoc As NotesDocument
    Public unid As String    ' Store UniversalID of claimant document
    Public parentunid As String        
    Public amounts As AmountData
    Public claimcare As ClaimCareData
    Public status As ClaimantStatusData
    Public Parallel As Integer
    
    Public Sub New(doc As NotesDocument)
        Dim claimnumber As String
        Dim claimant As Integer
        Set claimantdoc = doc
        unid = doc.UniversalID
        parentunid = doc.GetItemValue("ParentUNID")(0)
        claimnumber = doc.GetItemValue("ClaimNumber")(0)
        claimant = Cint(doc.GetItemValue("Claimant_Number")(0))
        If Lcase(doc.GetItemValue("TransferredToParallel")(0)) = "yes" Then
            Parallel = True
        Else
            Parallel = False
        End If
        Set amounts = New Amounts(claimnumber, claimant)
        Set status = New ClaimantStatusData(doc)
    End Sub
    
    
    Public Function GetValue(fieldname) As Variant
        GetValue = claimantdoc.GetItemValue(fieldname)(0)
    End Function
    
    Public Function SetValue(fieldname, Byval value As Variant) As Variant
        Call claimantdoc.ReplaceItemValue(fieldname, value)
    End Function
    
    Public Function GetVariant(fieldname) As Variant
        GetVariant = claimantdoc.GetItemValue(fieldname)
    End Function
    
    Public Sub Save()
        Call claimantdoc.Save(True,True)
    End Sub
    
    Public Function DisplayName() As String
        If Fulltrim(GetValue("Claimant_Name")) = "" Then
            DisplayName = GetValue("Claimant_Company")
        Else
            DisplayName = GetValue("Claimant_Name")
        End If
    End Function
    
    Public Function GetXML()        
        Dim tempxml As String
        If claimantdoc Is Nothing Then
            Msgbox "Claimant document is not loaded, no data returned.",,"ClaimantData.GetXML()"
            GetXML = ""
            Exit Function
        End If    
        tempxml = "<claimntno>" & claimantdoc.GetValue("Claimant_Number") & "</claimntno>" & CRLF    
        tempxml = tempxml + "<extclmntno>" & claimantdoc.getValue("ClaimantNumberExternal") & "</extclmntno>" & CRLF    
        tempxml = tempxml + "<classcode>" & claimantdoc.pClassCode(0) & "</classcode>" & CRLF    
        tempxml = tempxml + "<cvgcode>" & claimantdoc.Claimant_LossType(0) & "</cvgcode>" & CRLF    
        tempxml = tempxml + "<table>" & claimantdoc.pTable(0) & "</table>" & CRLF    
        If claimantdoc.Unknown(0)="Yes" Then
            tempxml = tempxml + "<claimntnam>* unknown *</claimntnam>" & CRLF
        Elseif claimantdoc.Claimant_Name(0)="" Then
            tempxml = tempxml + "<claimntnam>" & dsClaimLink.EncodeXML(claimantdoc.Claimant_Company(0)) & "</claimntnam>" & CRLF
        Else
            tempxml = tempxml + "<claimntnam>" & dsClaimLink.EncodeXML(claimantdoc.Claimant_Name(0)) & "</claimntnam>" & CRLF
        End If    
        ' Check if we have amounts loaded
        If Not amounts Is Nothing Then
            tempxml = tempxml + "<lossamt>" & Format$(amounts.lp,"###0.00") & "</lossamt>" & CRLF    
            tempxml = tempxml + "<expamt>" &  Format$(amounts.ep,"###0.00") & "</expamt>" & CRLF    
        End If
        GetXML = tempxml
    End Function
    
End Class
 

Option Public
Option Declare
Use "Class.ImportCSV"

Sub Initialize
  ' *** Import CSV file and create matching documents in Notes
  ' *** By Karl-Henry Martinsson, April 8, 2010
  Dim session As New NotesSession
  Dim db As NotesDatabase
  Dim doc As NotesDocument
  Dim csvfile As csvFile
  Dim rowcnt As Long
  Dim r As Long
  
  Set db = session.CurrentDatabase  
  Set csvfile = New csvFile("c:\Book1.csv")
  rowcnt = Ubound(csvfile.row) + 1
  ' *** Loop through the rows and create a new document for each
  For r = Lbound(csvfile.row) To Ubound(csvfile.row)
    If (r+1 Mod 10) = 0 Then    ' Update status bar every 10 documents
      Print "Importing " & r+1 & " of " & rowcnt
    End If
    Set doc = New NotesDocument(db)
    Call doc.ReplaceItemValue("Form", "MyFormName")
    ' *** Loop though entries for the row and populate corresponding fields in doc
    Forall e In csvfile.row(r).entry
      Call doc.ReplaceItemValue(Listtag(e), e)
    End Forall
    Call doc.Save(True,False)
  Next
  Print ""    ' Clear status bar
End Sub
 

' *** Created by Karl-Henry Martinsson on 2010-04-08
' *** Email: TexasSwede@gmail.com
' *** Blog: http://www.bleedyellow.com/blogs/texasswede
' *** ----------------------------------------------------------
' *** You are free to modify and edit this code, but please keep
' *** all comments intact, and publish any changes you make so
' *** the Lotus community can benefit. You are allowed to use
' *** this code in commercial/closed source products, but are 
' *** encouraged to share your modifications.
' *** Disclaimer: Use this code at your own risk. No warranties
' *** what so ever. Don't run code you don't know what it does.
' *** ----------------------------------------------------------

Class RowData
  Public entry List As String
End Class

Class csvFile
  Public row() As RowData         ' Storing the rows in the imported CSV file
  Public column List As String    ' List containing column labels
  Private fileno As Integer       ' File number
  
  Public Sub new(filename As String)
    Dim temprow As String
    Dim temparr As Variant
    Dim fixedarr() As String
    Dim i As Integer
    Dim flagQuoted As Integer
    
    fileno = Freefile            ' Return an unused file number
    Open filename For Input As fileno
    Redim row(0) As rowdata
    ' *** Get column header
    Do While Not Eof(fileno)
      ' *** Read row and split into cells.
      Line Input #fileno, temprow
      temparr = Split(temprow, ",")
      ' *** Commas within quoted values will cause the value
      ' *** to be split across array items, so we have to fix this.
      flagQuoted = False
      Redim fixedarr(0) As String    ' Size array to just one element, has to be done
      For i = Lbound(temparr) To Ubound(temparr)
        ' *** Check if value start with quote
        If Left$(temparr(i),1) = |"| Then  
          flagQuoted = True
        End If
        If flagQuoted = True Then
          If Left$(temparr(i),1) = |"| Then  
          ' *** For first item, just set array item to value, remove leading quote
            fixedarr(Ubound(fixedarr)) = Right$(temparr(i),Len(temparr(i))-1)
          Else
          ' *** Append value to previous array item with a comma to replace the lost one
            fixedarr(Ubound(fixedarr)) = fixedarr(Ubound(fixedarr)) & "," & temparr(i) 
          End If
          ' *** If it end with a quote, reset flag, rmove trailing quote and increase size of array
          If Right$(temparr(i),1) = |"| Then
            flagQuoted = False
            fixedarr(Ubound(fixedarr)) = Left$(fixedarr(Ubound(fixedarr)), Len(fixedarr(Ubound(fixedarr)))-1)
            Redim Preserve fixedarr(Ubound(fixedarr)+1) As String  
          End If
        Else
          ' *** Set array item to value and increase array size by one
          fixedarr(Ubound(fixedarr)) = temparr(i) 
          Redim Preserve fixedarr(Ubound(fixedarr)+1) As String  
        End If
      Next
      temparr = Fulltrim(fixedarr)    ' Remove empty items in array
      ' *** Check if this is the first row (index 0), i.e. the column header and no object exists  
      If Ubound(row) = 0 And (row(Ubound(row)) Is Nothing) Then
        ' *** Loop through temparr and build list from labels
        For i = Lbound(temparr) To Ubound(temparr)
          column(Cstr(i)) = temparr(i)
        Next
        ' *** Create an object so the code above will not be true again
        Set row(Ubound(row)) = New RowData
      Else
        ' *** Create new row data object, populate with data and increase size of row array
        Set row(Ubound(row)) = New RowData
        For i = Lbound(temparr) To Ubound(temparr)
          row(Ubound(row)).entry(column(Cstr(i))) = temparr(i)
        Next
        Redim Preserve row(Ubound(row)+1) As RowData
      End If
    Loop
    ' *** Remove the last row, as it is always empty
    Redim Preserve row(Ubound(row)-1) As RowData
  End Sub
End Class