Option Public
Option Declare

Sub Initialize
	' used for import/overlay of data from the legacy spreadsheet into this app
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace			' needed for prompts
	Dim askme As Variant							' prompt results
	Dim db As NotesDatabase						' current db
	Dim lupV As NotesView						' lookup view of already existing docs for match by key to be built in csv file
	Dim lupvnm As String						' alias name of lookup view
	Dim lupdocfrmnm As String					' alias name of form for new docs
	Dim importfilepath As String					' import file path including filename to CSV
	Dim filehandlenum As Integer					' file handle
	Dim linetxt As String						' one row/line of the input file for processing
	Dim fldLst() As String						' list of field names in save order as CSV from left to right
	
	On Error Goto ErrorHandler
	
	' ************** CUSTOMIZE BELOW AND IN PROCESSDOC FUNCTION *****************
	lupvnm = "lupMyView"
	lupdocfrmnm = "Doc"
	importfilepath = "c:\temp\import.csv"
	Redim fldLst(1)							' customize the number of fields/columns in the CSV and the field names below
	fldLst(0) = "fldname"
	fldLst(1) = "fldname"
	' **************** end customization ****************************************
	
	' setup enviornment and do tests
	Set db = s.CurrentDatabase
	Set lupV = db.GetView(lupvnm)
	If (lupV Is Nothing) Then
		' abort
		Print |(Initialize) Aborted. Missing lookup view.|
		Exit Sub
	End If
	' prompt for update to location
	askme = w.Prompt (PROMPT_OKCANCELEDIT, _
	"Keyword Import - Proceed?", _
	"This will import new widgets at the filepath below. Update the path to your location as needed. The columns should be: Desc 0, Desc 1. Click cancel to abort. click OK to proceed. “, importfilepath)
	If (Isempty (askme) ) Then
		Messagebox "Import has been cancelled by your request.", , "Import Cancelled"
		Exit Sub
	End If

	' test if file exists
	If (Dir$(importfilepath)="") Then
		' no file at location
		Messagebox "Import cancelled. File was not found at: " & importfilepath & ".", , "Import Cancelled"
		Print |(Initialize) Aborted. No file found at | & importfilepath & |.|
		Exit Sub
	End If
	' get file
	filehandlenum = FileOpenRead(importfilepath)
	If (filehandlenum = 0) Then
		' error occured opening file
		Print |Unabled to Open File. Aborting Export.|
		Exit Sub
	End If
	
	' done field list, ready for import
	Print "Starting Import/Update . . . "
	' get first row, and loop through rows
	Do Until Eof(filehandlenum%)
		Line Input #filehandlenum%, linetxt$
		' add/update doc
		Call ProcessDoc(db, lupV, linetxt, lupdocfrmnm, fldLst() )
		' loop to process rest of rows
	Loop
	Print "Done Import/Update."
	
SExit:
	' done close file
	Call FileCloseRead(filehandlenum)
	Exit Sub
ErrorHandler:
	Print "(Initialize) Unexpected Error. Info: " & Str(Err) & ": " & Error$ & " on line: " & Cstr(Erl)
	Call FileCloseRead(filehandlenum)
	Exit Sub
	
End Sub

Function FileOpenRead(inpfilenm As String) As Integer
	Dim filenum As Integer		' filehandle
	Dim tempdir As String		' temp directory on server
	Dim fname As String		' filename/filepath
	
	On Error Goto FErrorHandler
	
	' setup filepath
	filenum = Freefile
	fname = inpfilenm
	'tempdir = Environ("Temp")
	'fname = tempdir & "\" & inpfilenm & ".csv"
	
	' open up new file for input
	Open fname For Input As filenum
	
	' return success as file handle
	FileOpenRead = filenum
	Exit Function
	
FErrorHandler:
	Print "(FileOpenRead) Error. Info: " & Str(Err) & ": " & Error$ & " on line: " & Cstr(Erl) & "<br>" 
	FileOpenRead =0
	Exit Function
	
End Function

Function FileCloseRead(filehandlenum As Integer) As Integer
	' closes open file
	Close filehandlenum
	
	' return success
	FileCloseRead=1
End Function

Function ProcessDoc(db As NotesDatabase, lupV As NotesView, linetxt As String, formnm As String, fldLst() As String) As Integer
	' adds, or updates existing document with import data
	' db - current app db
	' lupV = view containing docs to update
	' linetxt = row of data (a record) from the input csv file
	' formnm = form name for new docs
	' fldLst() = string list of column fields in doc to update, should be in same order as CSV input file's linetxt
	Dim lupkey As String							' import key for getting matching doc in lupV from current row of sheet
	Dim lupDoc As NotesDocument						' document to update (or new doc to create if not found)
	Dim rowVals As Variant							' split the row into it's parts using comma as delimeter
	Dim rowvalstr As String							' current array member converted to text for test to lupItem.Text
	Dim dosave As Integer							' flag whether to call for save (1 if new doc or if a field value is different)
	Dim fldlstcount As Integer						' counter for looping through fldLst()'s fields
	Dim lupitemnm As String							' current field name being compared to the CSV
	Dim lupItem As NotesItem						' temporary item to get item's text and see if matches CSV import
	Dim tmpstr As String							' temp working variable/String
	
	' start with no save
	dosave =0
	
	' split the row of text into its columns
	rowVals = Split(linetxt, ",")
	
	' build key from columns (customize for key)
	lupkey = rowVals(0)
	' lupkey = rowVals(0) & "~~" & rowVals(5)
	
	' get first document by the key (assumes view has 1st column with same formatted key string)
	Set lupDoc = lupV.GetDocumentByKey(lupkey)			' lookup by text
	If (lupDoc Is Nothing) Then
		' create document
		Set lupDoc = db.CreateDocument()
		Call lupDoc.ReplaceItemValue("Form", formnm)
		
		' change save flag to true
		dosave = 1
		
		' add all fields looping through field list (which should match CSV list)
		For fldlstcount = 0 To Ubound(fldLst)
			Select Case rowVals(fldlstcount)
				' update for case by case modifications on field list
			Case "fldname"
				' pad widget number
				tmpstr = PadString(rowVals(fldlstcount), 2)
				Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), tmpstr )
			Case Else
				' replace field
				Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), rowVals(fldlstcount) )
			End Select
			' loop next field
		Next fldlstcount
		
		' compute with form (since new)
		Call lupDoc.ComputeWithForm(False, False)
	Else
		' existing doc, loop through CSV row values and fields and test for update
		' (which should match CSV list)
		For fldlstcount = 0 To Ubound(fldLst)
			' check field value
			Set lupItem = lupDoc.GetFirstItem(fldLst(fldlstcount) )
			If (lupItem Is Nothing) Then
				' replace as field doesn't exist, add and set save flag
				Select Case rowVals(fldlstcount)
					' update for case by case modifications on field list
				Case "fldname"
					' pad widget number
					tmpstr = PadString(rowVals(fldlstcount), 2)
					Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), tmpstr )
				Case Else
					' replace field
					Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), rowVals(fldlstcount) )
				End Select
				dosave = 1
			Else
				' have field, check value
				Select Case fldLst(fldlstcount)
					' update for case by case modifications on field list
				Case "districtnbr"
					tmpstr = PadString(lupItem.Text, 2)
				Case Else
					tmpstr = lupItem.Text
				End Select
				If (Strcompare( tmpstr, rowVals(fldlstcount), 5) = 0) Then
					' match, skip update/replacing field value
				Else
					' doesn't match, overwrite current field value
					Set lupItem = Nothing
					Select Case rowVals(fldlstcount)
						' update for case by case modifications on field list
					Case "fldname"
						' pad widget number
						tmpstr = PadString(rowVals(fldlstcount), 2)
						Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), tmpstr )
					Case Else
						' replace field
						Call lupDoc.ReplaceItemValue(fldLst(fldlstcount), rowVals(fldlstcount) )
					End Select
					dosave = 1
				End If
			End If
		Next fldlstcount
		' done updating existing document
	End If
	If (dosave =1) Then
		' save doc, either new doc or a field has been updated
		Call lupDoc.Save(True, False)
	End If
	' done doc. return success
	ProcessDoc = 1
FExit:
	Exit Function
	
FErrorHandler:
	ProcessDoc = 0
	Resume FExit
End Function
Function PadString(origstring As String, pads As Integer)
	' this function pads/adds leading 0s to a number string
	Dim padding As String			' string of zeros used for padding
	padding = "00000000000000000000000000000000000000000"
	If Len(pads) = 0 Then
       		' return original string as error
       		PadString = origstring
	End If
	PadString = Right(padding & origstring, pads)
End Function