'Convert TDF to JSON - Console Application
'Copyright (c) 2013 State of Vermont Legislative Joint Fiscal Office.
'Licensed under the Apache License, Version 2.0 (the "License");
'you may not use this file except in compliance with the License.
'You may obtain a copy of the License at
 '      			
'http://www.apache.org/licenses/LICENSE-2.0
'
'Unless required by applicable law or agreed to in writing, software
'distributed under the License is distributed on an "AS IS" BASIS,
'WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
'See the License for the specific language governing permissions and
'limitations under the License.	

Imports System.IO
Imports System.Text.RegularExpressions
Imports System.Net

Module ConvertGrants

    Sub Main(ByVal cmdArgs() As String)
        Dim strFileLocation As String = cmdArgs(0)

        Dim strJSONLocation As String = "C:\codeFolder\mycode.json"

        ReadCSVFile(strFileLocation, strJSONLocation)

    End Sub

    Class GrantsRow
        Public Headers As Boolean
        Public Fields(20) As String
        Public Sub New(HeaderValue)
            Me.Headers = HeaderValue
        End Sub
    End Class


    Function ReadCSVFile(strFileLocation As String, strJSONLocation As String) As Integer
        Dim fs As FileStream
        Dim sReader As StreamReader
        Dim fileReadLine As String

        Dim headers As MatchCollection
        Dim thisLine As MatchCollection

        Dim GrantsList As New ArrayList()


        Try
            fs = File.Open(strFileLocation, FileMode.OpenOrCreate, FileAccess.Read, FileShare.Read)
            sReader = New StreamReader(fs)

            GrantsList.Add(New GrantsRow(True))

            'Initialize the read line and read [mupltiple lines] variables
            fileReadLine = sReader.ReadLine & Chr(13)

            'Dim splitReg = New Regex(Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34) & "|(?<=,|^)([^,]*)(?=,|$)")
            '
            'Dim splitReg = New Regex(Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34) & "|(?<=,|^)([^,]*)(?=,|$)")
            Dim splitReg = New Regex(Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34) & "|(?<=" & Chr(9) & "|^)([^" & Chr(9) & "]*)(?=" & Chr(9) & "|$)")
            'Dim splitReg = New Regex(Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34) & "|(?<=" & Chr(9) & "|^)([^" & Chr(9) & "]*)(?=" & Chr(9) & "?!=" & Chr(34) & "|$)")

            headers = splitReg.Matches(fileReadLine)

            Dim thisIndex As Integer = 0

            Dim i As Integer


            For i = 0 To headers.Count() - 1
                GrantsList(0).Fields(i) = headers.Item(i).Value()
            Next i

            'Console.ReadKey()




            'Read the rest of the file
            While (sReader.EndOfStream = False)
                GrantsList.Add(New GrantsRow(False))

                fileReadLine = sReader.ReadLine & Chr(13)
                thisLine = splitReg.Matches(fileReadLine)

                For i = 0 To thisLine.Count() - 1
                    GrantsList(thisIndex + 1).Fields(i) = thisLine.Item(i).Value()
                Next i


                thisIndex += 1
            End While

            sReader.Close()
            sReader = Nothing

            fs.Close()
            fs = Nothing

            'Console.ReadKey()

            CSVToJSON(GrantsList, strJSONLocation)

        Catch errFileRead As Exception
            'Display file read error and exit the app
            MsgBox("Error reading the CSV file - Program will now exit (see details below)" & Chr(13) & errFileRead.Message)
            End
        End Try


        Return 0
    End Function

    Function CSVToJSON(ByRef GrantsList As ArrayList, ByVal strJSONLocation As String)

        Const strUploadDirectory As String = "ftp://example.com/example/"

        Dim sWriter As StreamWriter
        Dim fs As FileStream

        Dim columnNum As Integer = 0
        Dim countHeaderCol As Integer = 0

        Try
            'Open the file and a stream to write to
            fs = File.Open(strJSONLocation, FileMode.Create, FileAccess.Write, FileShare.Read)
            sWriter = New StreamWriter(fs)

            'Opening JSON brace
            sWriter.Write("{" & Chr(13))


            sWriter.Write(Chr(9) & Chr(34) & "headers" & Chr(34) & ": [" & Chr(13))
            For Each header In GrantsList(0).Fields
                If header = "" Then
                    Exit For
                End If
                columnNum += 1
            Next

            'Write the data labels (headers) to file
            For Each header In GrantsList(0).Fields
                If header = "" Then
                    Exit For
                Else
                    If header.ToString.Substring(header.ToString().Length() - 1) = Chr(13) Then
                        header = header.ToString().Substring(0, header.ToString().Length() - 1)
                    End If
                    sWriter.Write(Chr(9) & Chr(9) & Chr(34) & header & Chr(34))
                    If countHeaderCol < columnNum - 1 Then
                        sWriter.Write(",")
                    End If
                End If
                countHeaderCol += 1
            Next
            sWriter.Write(Chr(9) & "], " & Chr(13))

            'key for aaData, the key that DataTables (jQuery Grid control) uses
            sWriter.Write(Chr(9) & Chr(34) & "aaData" & Chr(34) & ": [" & Chr(13))


            Dim i As Integer = 0
            Dim GLLen As Integer = GrantsList.Count()
            Dim fieldLen As Integer = 0
            Dim csvRow As Object

            For n = 1 To GrantsList.Count() - 1

                csvRow = GrantsList(n)

                sWriter.Write("[" & Chr(13))

                For dataField = 0 To columnNum - 1
                    If csvRow.Fields(dataField) <> Nothing Then
                        fieldLen = csvRow.Fields(dataField).ToString().Length()

                        'Add double quotes to beginning of value, if they're not there already
                        If fieldLen > 0 Then
                            If csvRow.Fields(dataField).ToString().Substring(0, 1) <> Chr(34) Then
                                sWriter.Write(Chr(34))
                            End If

                            'Write the field data to the JSON file
                            sWriter.Write(csvRow.Fields(dataField).ToString().Replace(Chr(13), ""))

                            fieldLen = csvRow.Fields(dataField).ToString().Length()

                            If (fieldLen > 0) Then
                                'Add double quotes to the end of the value, if they're not there already
                                If csvRow.Fields(dataField).ToString().Substring(fieldLen - 1, 1) <> Chr(34) Then
                                    sWriter.Write(Chr(34))
                                End If
                            End If

                            'Add a comma to separate the elements of the list, unless it's the last array element
                            If dataField < columnNum - 1 Then
                                sWriter.Write(",")
                            End If
                        Else
                            'If the field is 0 or 1 characters wide, insert double quotes for an empty value
                            sWriter.Write(Chr(34) & Chr(34))
                        End If
                    Else
                        sWriter.Write(Chr(34) & Chr(34))
                        If dataField < columnNum - 1 Then
                            sWriter.Write(",")
                        End If
                        sWriter.Write(Chr(13))
                    End If

                Next dataField

                If n = GrantsList.Count() - 1 Then
                    'If this is the last row of the table, don't add a comma separator
                    sWriter.Write("] " & Chr(13))
                Else
                    'But if it's not the last one, add a comma
                    sWriter.Write("], " & Chr(13))
                End If
            Next n

            'Closing bracket for the aaData value
            sWriter.Write(Chr(9) & "]" & Chr(13))

            'Closing JSON brace
            sWriter.Write("}" & Chr(13))

            'Close out the write stream and the file stream
            sWriter.Close()
            sWriter = Nothing
            fs.Close()
            fs = Nothing


            Console.WriteLine("Press any key to upload the JSON data file - or close the app to cancel")
            Console.ReadKey()

            UploadSpreadsheet(strUploadDirectory, strJSONLocation)

        Catch errFileWrite As Exception
            'Display file read error and exit the app
            MsgBox("Error writing to the JSON file - Program will now exit (see details below)" & Chr(13) & errFileWrite.Message)
            Return -1

            End
        End Try

        Return 0

    End Function


    Sub UploadSpreadsheet(uriString, fileName)

        ' Create a new WebClient instance. 
        Dim myWebClient As New WebClient()

        myWebClient.Credentials = New NetworkCredential("username", "password")

        ' The 'UploadFile(uriString,fileName)' method implicitly uses HTTP POST method.  
        'NOTE: Since it's an FTP address, HTTP POST is not used
        Dim responseArray As Byte() = myWebClient.UploadFile(uriString, fileName)

    End Sub

End Module

		  		
'Convert TDF to JSON - Console Application
'Copyright (c) 2013 State of Vermont Legislative Joint Fiscal Office.
'Licensed under the Apache License, Version 2.0 (the "License");
'you may not use this file except in compliance with the License.
'You may obtain a copy of the License at
 '      			
'http://www.apache.org/licenses/LICENSE-2.0
'
'Unless required by applicable law or agreed to in writing, software
'distributed under the License is distributed on an "AS IS" BASIS,
'WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
'See the License for the specific language governing permissions and
'limitations under the License.	

Sub btnExport_Click()
    Const TDFFileLocation = "C:\myFolder\TDF_Data.tdf"
    Const TDF2FileLocation = "C:\myFolder\TDF_Data2.tdf"
    
    Excel.ActiveWorkbook.Save
    
    myRange = Excel.Range("A1", "O1200").Replace(Chr(34), "")
    
    myRange = Excel.Range("A1", "O1200").Replace("’", "'")
    
    Excel.ActiveWorkbook.SaveAs Filename:=TDFFileLocation, FileFormat:=-4158
    Excel.ActiveWorkbook.SaveAs Filename:=TDF2FileLocation, FileFormat:=-4158

    Shell "C:\MyApps\Convert_TDF_To_JSON.exe " & Chr(34) & TDFFileLocation & Chr(34)
    
    Excel.ActiveWorkbook.Close
    
End Sub