' ** AND DELETES DOCS WITH A SSN # NOT FOUND IN THE FILE
' ** 
' ** Dim csv As CSVFile
' ** Dim st_Fields(1 To 3) As String
' ** 
' ** st_Fields(1) = "SSN"
' ** st_Fields(2) = "FIRST"
' ** st_Fields(3) = "LAST"
' ** 
' ** Set csv = New CSVFile("","F_NAME", st_Fields)
' ** 
' ** csv.Synchronize = True
' ** csv.SynchronizeView = "V_LOOKUPBYSSN"
' ** csv.SynchronizeDeletions = True
' **  
' ** Call csv.Import
Class CSVFile
	Public FileName As String
	Public Delimiter As String
	Public ImportDatabase As NotesDatabase
	Public ImportForm As String
	Public ImportFields As Variant   ' ARRAY  OF FIELD NAMES - **INDEX MUST START WITH 1**
	Public RefreshForm As Variant
	Public ShowProgress As Variant
	Public QuoteDelimiter As Variant  ' IS A DOUBLE QUOTE USED TO SEPARATE VALUES WITH COMMAS
	Public Synchronize As Variant     ' SHOULD THE IMPORT SYNCH BASED ON KEY VALUES
	Public NumKeyFields As Integer  ' HOW MANY OF THE FIRST FIELDS ARE KEY VALUES
	Public SynchronizeView As String ' NAME OF VIEW TO USE TO SYNCHRONIZE USING KEY VALUES
	Public SynchronizeDeletions As Variant 
	Public SkipTitleLine As Variant
	Public NumChanges As Integer
	Public NumAdds As Integer
	Public NumDeletes As Integer
	
	Private SynchUNIDS List As String
	
	Sub New(st_FileName As String, st_FormName As String, v_FieldArray As Variant)
		Dim lib_s As New NotesSession
		Dim lib_ws As New NotesUIWorkspace
		Dim v_ReturnValue As Variant
		
		If Trim(st_FileName) = "" Then ' IF NO FILE NAME GIVEN - PROMPT FOR FILE
			v_ReturnValue =  lib_ws.OpenFileDialog(False, "Select a CSV file", "CSV Files|*.CSV", "C:\")
			If Not Isarray(v_ReturnValue) Then Exit Sub
			If v_ReturnValue(0) = "" Then Exit Sub
			st_FileName = v_ReturnValue(0)
		End If
		
		Me.FileName = st_FileName
		Set Me.ImportDatabase = lib_s.CurrentDatabase
		Me.ImportForm = st_FormName
		Me.ImportFields = v_FieldArray
		Me.RefreshForm = True
		Me.Delimiter = ","
		Me.ShowProgress = True
		Me.QuoteDelimiter = True
		Me.Synchronize = False
		Me.SynchronizeDeletions = False
		Me.NumKeyFields = 1 
		Me.SkipTitleLine = False
		Me.NumChanges = 0 
		Me.NumAdds = 0 
		Me.NumDeletes = 0
	End Sub
	
	Sub Import
		Dim i_FileNum As Integer
		Dim st_LineData As String
		Dim i_ImportCount As Integer
		Dim vw_Lookup As NotesView
		Dim doc_List As NotesDocument
		
		If Trim(Me.FileName) = ""  Then 
			Messagebox "No file name specified.", 16, "Error"
			Exit Sub
		End If
		
		If Me.Synchronize Then
			Set vw_Lookup =  Me.ImportDatabase.GetView(Me.SynchronizeView) 
			If vw_Lookup Is Nothing Then
				Messagebox "Invalid synch view name.", 16, "Error"
				Exit Sub  
			End If   
			
			Call vw_Lookup.Refresh ' REFRESH THE VIEW SO IT HAS MOST RECENT DATA
			
			If Me.SynchronizeDeletions Then
    ' BUILD LIST 
				Set doc_List = vw_Lookup.GetFirstDocument
				Do While Not doc_List Is Nothing
					Me.SynchUNIDS(doc_List.UniversalID) = False
					Set doc_List  = vw_Lookup.GetNextDocument(doc_List)
				Loop    
			End If
		End If
		
		If Not Isarray(Me.ImportFields) Then 
			Messagebox "Invalid field name array.", 16, "Error"
			Exit Sub
		End If
		
		i_FileNum = Freefile()
		
		Open Me.FileName For Input As i_FileNum
		
		i_ImportCount = 0 
		
  ' SKIP THE TITLE LINE IF SPECIFIED
		If Not Eof(i_FileNum) And Me.SkipTitleLine Then Line Input #i_FileNum, st_LineData     
		
		Do While Not Eof(i_FileNum) 
			i_ImportCount = i_ImportCount + 1
			If Me.ShowProgress Then Print "Importing record # " & Cstr(i_ImportCount)
			
			Line Input #i_FileNum, st_LineData     ' GRAB A LINE FROM THE FILE
			
			If Not Me.Synchronize Then
				If Not ImportLine(st_LineData) Then
					Messagebox "Error during import.", 16, "Error"
					Close i_FileNum
					Exit Sub
				End If
			Else
				If Not SynchLine(st_LineData) Then
					Messagebox "Error during synchronization.", 16, "Error"
					Close i_FileNum
					Exit Sub
				End If
			End If
		Loop
		
		Close i_FileNum
		
		If Me.Synchronize And Me.SynchronizeDeletions Then
			Forall x In Me.SynchUNIDS
				If x = False Then ' NO MATCH FOUND IN FILE
					Call Me.ImportDatabase.GetDocumentByUNID(Listtag(x)).Remove(True)
					Me.NumDeletes = Me.NumDeletes + 1
				End If
			End Forall   
		End If
		
		Print "Import Complete. " & Cstr(i_ImportCount) & " records processed. " & Cstr(Me.NumAdds) & " added " & Cstr(Me.NumChanges) & " changed " &  Cstr(Me.NumDeletes) & " deleted"  
	End Sub
	
 ' IMPORTS THE DATA - CREATES A NEW DOCUMENT FOR EVERY LINE
	Private Function ImportLine(st_LineData As String) As Variant
		Dim v_ValueArray As Variant
		Dim i As Integer
		Dim doc_Current As NotesDocument
		
		ImportLine = True
		
		v_ValueArray = ReturnValueArray(st_LineData)  ' BREAK UP THE DATA INTO AN ARRAY
		
		Set doc_Current = Me.ImportDatabase.CreateDocument 
		
		For i = Lbound(Me.ImportFields) To Ubound(Me.ImportFields)
			Call doc_Current.ReplaceItemValue(Me.ImportFields(i), v_ValueArray(i))
		Next
		
		doc_Current.Form = Me.ImportForm
		If Me.RefreshForm Then Call doc_Current.ComputeWithForm(False, True)
		Call doc_Current.Save(True, False)
		
	End Function
	
 ' IMPORTS THE DATA - SYNCHRONIZING THE DOCUMENT BASED ON KEY FIELDS
	Private Function SynchLine(st_LineData As String) As Variant
		Dim v_ValueArray As Variant
		Dim v_Key As Variant
		Dim v_TempKey (1 To 10) As Variant
		Dim i As Integer
		Dim doc_Current As NotesDocument
		Dim vw_Lookup As NotesView
		Dim v_FieldChange As Variant
		
		SynchLine = True
		
		v_ValueArray = ReturnValueArray(st_LineData)  ' BREAK UP THE DATA INTO AN ARRAY
		
  ' BUILD THE KEY 
		For i = 1 To Me.NumKeyFields
			v_TempKey(i) = v_ValueArray(i)
		Next
		v_Key = Fulltrim(v_TempKey)
		
  ' INSTANTIATE THE VIEW
		Set vw_Lookup = Me.ImportDatabase.GetView(Me.SynchronizeView)
		
  ' LOOKUP THE DOCUMENT  
		Set doc_Current = vw_Lookup.GetDocumentByKey(v_Key, True)
		
		If doc_Current Is Nothing Then  ' CREATE A BRAND NEW DOCUMENT  
			
			Set doc_Current = Me.ImportDatabase.CreateDocument 
			
			For i = Lbound(Me.ImportFields) To Ubound(Me.ImportFields) 
				Call doc_Current.ReplaceItemValue(Me.ImportFields(i), v_ValueArray(i))
			Next
			
			doc_Current.Form = Me.ImportForm
			If Me.RefreshForm Then Call doc_Current.ComputeWithForm(False, True)
			Call doc_Current.Save(True, False)
			Me.NumAdds = Me.NumAdds + 1
		Else ' FOUND A DOCUMENT WITH THE SAME KEY  - DETERMINE IF NEED TO UPDATE
			
			If Me.SynchronizeDeletions Then
				Me.SynchUNIDS(doc_Current.UniversalID) = True ' TAG THIS DOC SO IT WON'T BE DELETED
			End If
			
			v_FieldChange = False
			
			For i = Lbound(Me.ImportFields) To Ubound(Me.ImportFields) 
				If Cstr(doc_Current.GetItemValue(Me.ImportFields(i))(0)) <> Cstr(v_ValueArray(i)) Then
					v_FieldChange = True
					Call doc_Current.ReplaceItemValue(Me.ImportFields(i), v_ValueArray(i))
				End If
			Next
			
			If v_FieldChange Then
				If Me.RefreshForm Then Call doc_Current.ComputeWithForm(False, True)
				Call doc_Current.Save(True, False)
				Me.NumChanges = Me.NumChanges + 1
			End If
			
		End If
		
	End Function
	
	Private Function ReturnValueArray(st_LineData) As Variant   ' BREAKS UP A LINE OF CSV DATA INTO AN ARRAY
		Dim i As Integer
		Dim doc_Current As NotesDocument
		Dim i_NextDelimiter As Integer
		Dim v_DoubleQuotes As Variant
		Dim v_ValueArray(1 To 150) As Variant
		
		For i = 1 To Ubound(Me.ImportFields)
			v_DoubleQuotes = False
			
			If Me.QuoteDelimiter = True And Left(st_LineData, 1) = """" Then ' FOUND A VALUE IN DOUBLE QUOTES
				i_NextDelimiter = Instr(st_LineData,"""" & Me.Delimiter) ' CLOSING DELIMITER WILL BE ANOTHER SET OF DOUBLE QUOTES + DELIMITER
				v_DoubleQuotes = True
			Else
				i_NextDelimiter = Instr(st_LineData, Me.Delimiter)     
			End If
			
			If i_NextDelimiter = 0 Then '  NO DELIMITER FOUND - SO MUST BE LAST VALUE
				If v_DoubleQuotes Then 
					v_ValueArray(i) = Mid(st_LineData, 2, Len(st_LineData) - 2) ' REMOVE DOUBLE QUOTES
				Else
					v_ValueArray(i) = st_LineData
				End If    
				Exit For
			End If
			If v_DoubleQuotes Then
				v_ValueArray(i) =  Mid(st_LineData, 2, i_NextDelimiter - 2)
			Else
				v_ValueArray(i) =  Trim(Left(st_LineData, i_NextDelimiter - 1))
			End If
			
			st_LineData = Trim(Mid(st_LineData, i_NextDelimiter + 1))
		Next
		
		ReturnValueArray = v_ValueArray
	End Function
End Class