#ofx2csv.tcl
# -*- tcl -*-
# The next line is executed by /bin/sh, but not tcl \
exec wish "$0" ${1+"$@"}

# This program will process a OFX 1.x file that can typically be
# obtained from a bank or credit card.

# The object of the exercise is to produce a CSV type file from
# the transactions contained in the OFX file.  

# I have modeled the output to be something like I get from
# processing a QIF file from Quicken. ie I have fields

#      Date Amount Tranid Source Memo Status Category

# The first five fields can be generally be extracted from the
# OFX file. Note that the Memo field might be empty or contain
# overfill from the source field or some actual info.
# I always use an "X" for the status to indicate a cleared
# transaction.
# The bank/credit card cannot assign a category so the user
# has to supply this field. Note that the code always uses
# "UNKNOWN" as the initial contents of this field.

# I use the "Category" field as a foreign key when I insert the
# CSV field into my Postgresql Database so it is important that
# the value actually matches an element in the appropriate table.

# To prevent mistakes I have placed all of the choices for the
# category field into a listbox.

# The basic strategy is that the user will double click one of the UNKNOWN
# fields to select it and then double click the appropriate category
# to use from the listbox of choices. This will cause the UNKNOWN
# value to be replaced with the users choice of category.

# After editing the CSV file the user can then save the file and
# later import the CSV file into their favorite database.

# Enjoy!
# Author:  Jerry LeVan
# Env:     Recent TCL/TK
# Date:    Feb 18, 2006
# Version: 0.7
# Version: 0.8 Feb 2, 2007
# This is free ware use as you like (but keep my name...)

# Possible transaction tags
# Value = Enum [CREDIT | DEBIT | INT | DIV | FEE | SRVCHG | DEP
#| ATM | POS | XFER | CHECK | PAYMENT | CASH | DIRECTDEP | DIRECTDEBIT | REPEATPMT | OTHER]

package require Tk
package require Tktable

set version 0.8
set sep |

# items cannot contain white space, hence the quotes
set menuitems {
    Auto
    Auto:Fuel
    Auto:Service
    "Bank Charges"
    Charity
    "Check Deposit"
    "[Checking Bank One]"
    Clothing
    Computer
    "Credit Card"
    "Customer Deposit"
    Dining
    Education
    Entertainment
    Gifts
    Groceries
    "Home Repair"
    "Home Repair:Yard Work"
    Household
    Insurance:Auto
    Insurance:Home
    "Interest Earned"
    "Interest Paid"
    [MasterCard-Marijo]
    Medical
    Miscellaneous
    "Mortgage Int"
    Pension
    Recreation
    Salary
    "[Savings at Bank One]"
    Taxes
    Taxes:Federal
    Taxes:Other
    Taxes:Property
    Taxes:State
    Telephone
    Travel
    Utilities
    "Utilities:Cable TV"
    "Utilities:Gas & Electric"
    Utilities:Water
    "[Visa - Jerry]"
}

# Change values to be more in line with Quicken
proc cleanUp { key value } {
    
 set thevalue $value
 if { $key eq "DTPOSTED" } {
    # fiddle with the date
    set myDate [string range $value 0 7]
    set thevalue ""
    append thevalue [string range $myDate 0 3] - [string range $myDate 4 5] - [string range $myDate 6 7]
 }
 if {$key eq "TRNTYPE" && $value eq "CREDIT" } {set thevalue "DEP"}
 if {$key eq "TRNTYPE" && $value eq "DEBIT" } { set thevalue "WITHD"}
 if {$key eq "TRNAMT" } { set thevalue [format "%.2f" $value]}
     
 return $thevalue
}

# Generate and process one complete transaction
proc processOne { trans  row} {
    global output sep table_data
    array set items {
      date "" amount "" tranid "" source "" memo "NONE" status "X" category "UNKNOWN"
    }
    set items(row) [expr {$row + 1 }]
    foreach obj $trans {
      #puts $obj
      regexp {.*<(.*)>(.*)\}*} $obj all key value
      set value [string trim $value]
      set value [cleanUp $key $value]
      switch -exact -- $key {
        TRNTYPE { set items(tranid) $value }
        DTPOSTED  { set items(date) $value }
        TRNAMT  { set items(amount) $value }
        NAME    { set items(source) $value }
        MEMO    { set items(memo)   $value }
        CHECKNUM { set items(tranid) $value}
      }
    }
    set col -1
    foreach var {row date amount tranid source memo status category} {
      set table_data($row,$col) $items($var)
      incr col
     }
}

# Split the list of tranactions and process each one
proc analyzeOneThing { transaction row } {
 # split on new line
 set tranlist [split  $transaction \n ]

 # pitch clutter of first tag, last tag  
 set tranlist [lrange $tranlist 1 end-1]

 foreach item [list $tranlist] {
   processOne $item $row
 }
  
}

# Get the OFX file to process.
proc openFile {} {
  set fileName [tk_getOpenFile -parent .frm.t]
  if { $fileName != "" } {
   set f [open $fileName r]
   set theFile [ read $f ]
   close $f
   return [list $fileName $theFile]
  }
  return ""
}

# Save the contents of the Text Widget to a file.
proc saveFile {} {
 global table_data sep
 
 set fname [tk_getSaveFile -initialdir "~" \
            -defaultextension ".csv"]
 if { $fname != "" } {
    if { [ catch {
     set f [open $fname "w"]
     
     set rows [.frm.t cget -rows]
     set cols [.frm.t cget -cols]
     #puts "$rows   $cols"
     incr rows -2
     incr cols -2
     for {set j 0} {$j<=$rows} {incr j} {
        set line ""
        for { set i 0 } {$i <=$cols } { incr i } {
            if { $i==$cols } {
                 append line $table_data($j,$i) 
            } else {
                append line $table_data($j,$i) $sep
            }
        }
        puts $f $line
     }
     close $f
     tk_messageBox -message "Write Completed Ok." -icon info
    } result] } {    
        tk_messageBox -message "File Save of $fname failed. Reason : $result" -type ok
    }
 }
}

# Get the file to process, extract the transactions and process each one...
proc processFile {} {
 global table_data
set tmp [openFile]
if { $tmp == "" } { exit }

set f [lindex $tmp 0]

set theFile [lindex $tmp 1]

focus -force .frm.t

# Empty the text widget
#.frm.t delete 1.0 end
#
# Break out the list of transactions...
#
  set items [regexp -all -inline {<STMTTRN>.*?</STMTTRN>} $theFile]
  if { $items == "" } {
    tk_messageBox -message "No Transactions Found in $f" -type ok -icon info
  }
  
  set rowCnt [llength $items]
  .frm.t configure -rows [incr rowCnt]
  array set table_data { -1 date -1 amount -1 tranid -1 source -1 memo -1 status -1 category }
  set row 0
  foreach chunk $items {
    analyzeOneThing $chunk $row
    incr row
  }
}

proc make_table { parent } {
    global table_data
    global menuitems
    global colHeaders
  table $parent.t \
    -variable table_data \
    -yscrollcommand "$parent.sy set" \
    -xscrollcommand "$parent.sx set" \
    -titlerows 1 \
    -titlecols 1 \
    -roworigin -1 \
    -colorigin -1 \
    -colstretchmode last \
    -invertselected 0 \
    -selectmode browse \
    -selecttype cell \
    -padx 4 \
    -coltagcommand colproc \
    -autoclear 1 \
    -cols 8 -rows 10
  
   $parent.t config -bg white
   scrollbar $parent.sx -orient horizontal -command [list $parent.t xview]
   scrollbar $parent.sy -command " $parent.t yview"
   set colnum 0
   foreach header $colHeaders {
     set table_data(-1,$colnum) $header
     incr colnum
   }
   
   $parent.t tag config title -bg lightblue
   $parent.t tag config title -fg black 

   $parent.t tag configure sel -bg blue
   $parent.t tag configure sel -fg red
   $parent.t tag config anchorcenter -anchor c
   $parent.t tag config anchorwest -anchor w
   $parent.t tag config anchoreast -anchor e
   $parent.t tag col anchoreast -1
   $parent.t tag row anchorcenter -1
   $parent.t width -1 5
   # build a popup menu...
   menu $parent.t.menu -tearoff 0
   set cnt 0
   foreach thing $menuitems {
    incr cnt
    if { $cnt == 20 } {
        set brk 1 ; set cnt 0
    } else { set brk 0 }
    $parent.t.menu add command -columnbreak $brk -label "$thing" \
       -command [list setItem $thing ]
   }

   return $parent.t
}

proc setItem newString {
    global lastx lasty
    .frm.t set [.frm.t index @$lastx,$lasty] $newString
}

proc colproc {col} {
    if {$col ==-1 || $col ==1 } {
        return anchoreast
    } else { return anchorwest }
}
    
# Build the text widget for displaying the results as a CSV file
proc BuildGui {} {
    global menuitems categoryCol lastx lasty version
    # Create a "menu bar"
    menu .menubar
    # make it the menu for the application window
    .  config -menu .menubar
    # create the File menu
     menu .menubar.file -tearoff 0
    # add this menu to the menubar
    .menubar  add cascade -label "File"  -menu .menubar.file
    
    # add the menu items with actions
    
    # add a scripts menu
     menu .menubar.replace -tearoff 0
    .menubar add cascade -label "Category Choices" -underline 0 -menu .menubar.replace
    
    # build a table object and position it in the frame
    set f [ frame .frm ]
    set theTable [make_table $f]
    pack $f -fill both -expand yes
    pack .frm.sx -side bottom -fill x
    pack .frm.sy -side right -fill y
    pack $theTable -side top -fill both -expand yes
   
    # populate the menus
    
    .menubar.replace add command -label "Replace With..." -command showReplaceInfo
    
    .menubar.file add command -label "Open OFX File..." -command processFile
    .menubar.file add command -label "Save As..." -command saveFile
    .menubar.file add command -label "Quit"  -command exit
 #Support the MouseWheel

    bind .frm.t <Button-4> { .frm.t yview scroll -5 units }
    bind .frm.t <Button-5> { .frm.t yview scroll +5 units }
  
  
    bind .frm.t <MouseWheel> {
      if { %D < 0} {
          .frm.t yview scroll +5 units
         } else {
          .frm.t yview scroll -5 units
         }
    }
 # bind the popup menu
     bind .frm.t <Control-Button-1> {
     if { [ .frm.t index @%x,%y col ] != $categoryCol } return
     set lastx %x
     set lasty %y
     tk_popup .frm.t.menu  %X %Y 
     #puts "x=%x, y=%y\nX=%X, Y=%Y\n cell: [.frm.t get @%x,%y]\n @%x,%y\n"
     #puts "row: [.frm.t index @%x,%y]\n"
    }
    
    wm title . "Ofx2Csv $version"
 
}

# Action routine to replace field in Text Widjet with selected
# field in the list box.
proc changeItem {theTable } {
    global table_data
     # Get the selected item
    set tabIndex [$theTable curselection]
    if { $tabIndex == ""} {
         tk_messageBox -title OFX2CSV \
                    -message "No Replacement Selected."  \
                    -type  ok \
                    -icon error
         return
    }
    set theItem [$theTable get $tabIndex]
    
    set theCell [.frm.t tag cell sel ]
    
    if {$theCell ne ""} {
      set table_data($theCell) $theItem
      focus -force .frm.t
    } else {
        tk_messageBox -title OFX2CSV \
                -message "Nothing Selected in CSV File" \
                -type ok -icon error
    }
}

# Build and display the list box which contains all of
# the foreign keys.
# Not really needed anymore...
proc showReplaceInfo {} {

global menuitems

destroy .showList

toplevel .showList

 listbox .showList.choices -width 30 -height 10  \
      -borderwidth 3 \
      -exportselection 0 \
      -relief groove \
      -yscrollcommand {.showList.sy set } 
  scrollbar .showList.sy -command [list .showList.choices yview]
  
  frame .showList.bframe
  
  pack .showList.bframe -side bottom
  
  button .showList.bframe.showtab -text "Edit Item" \
     -command [list changeItem .showList.choices ]
      
  button .showList.bframe.exit -text "Exit Edit Selection" -command {destroy .showList }

  pack .showList.bframe.showtab -side left
  pack .showList.bframe.exit -side left
  pack .showList.choices -side left -expand true -fill both -padx 4 -pady 4
  pack .showList.sy -side right -fill y -padx 4
  bind .showList.choices <Double-1> [list changeItem .showList.choices ]
  # load the listbox
  foreach item $menuitems {
    .showList.choices insert end $item
  }
}
#### Start Here ####

# table headers
set colHeaders { date amount tranid source memo status category }

# This is the column we have to be careful about
# and which the popup menu operates.

set categoryCol 6

# lastx and lasty will be the coordinates of the button click
# that summons the popup menu.
set lastx 0
set lasty 0

# Set up the interface
BuildGui