Imports Finisar.SQLite
Imports System.IO
Imports System.Text
Imports DataGrid_DataSet_DataTable.ExceptionHandler

Module Tables
    'SQLite3 Variables
    Dim AppPath As New StringBuilder

    Dim DBName As String = AppPath.Append(String.Format("{0}\{1}", _
                    Path.GetDirectoryName(Application.ExecutablePath), "")).ToString() & "DatabaseHub.db"
    Dim conn As New SQLiteConnection
    Dim cmd As New SQLiteCommand
    Dim rd As IDataReader 'Cast As ADO.NET DataReader
    'Error Message Information
    Dim ContactTechSupport As String = "Please Contact Tech Support. Phone: (318) 555-1212 or (318) 555-1313"
    Dim ThisModule As String = "SQLite3"
    Public Function CreateTableMemoryObjectNoPK() As DataTable
        Dim ThisSub As String = "CreateTableMemoryObjectNoPK"
        Dim DCol As DataColumn
        Dim dt = New DataTable("BillTasks") 'dt Name = BillTasks

        'Create The Table Object
        Try
            'Create Column And Add To The DataTable
            DCol = New DataColumn("ID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "ID"
            DCol.Caption = "ID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("clecID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "clecID"
            DCol.Caption = "clecID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("ilecID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "ilecID"
            DCol.Caption = "ilecID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Resale", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "Resale"
            DCol.Caption = "Resale"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("UNEP", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "UNEP"
            DCol.Caption = "UNEP"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("TaskOrder", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "TaskOrder"
            DCol.Caption = "TaskOrder"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Task", System.Type.GetType("System.String"))
            'Set Column Properties
            'DCol.ColumnName = "Task"
            DCol.Caption = "Task"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Descr", System.Type.GetType("System.String"))
            'Set Column Properties
            'DCol.ColumnName = "Descr"
            DCol.Caption = "Descr"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Return The DataTable
            CreateTableMemoryObjectNoPK = dt

        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Creating A DataTable With No PK!", _
                "The Application Cannot Continue Without Creating The Data Table.", _
                ContactTechSupport, _
                ThisModule & ThisSub & System.Environment.NewLine, _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException


        Finally
            DCol.Dispose()
            dt.dispose()
        End Try

    End Function


    Public Function CreateTableMemoryObjectPK() As DataTable
        'Create The Table Object
        Dim ThisSub As String = "CreateTableMemoryObjectPK"
        Dim DCol As DataColumn
        Dim dt = New DataTable("BillTasks") 'dt Name = BillTasks

        Try
            'Create PrimaryKey Column And Add To The DataTable

            DCol = New DataColumn("ID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "ID"
            DCol.Caption = "ID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")} 'More Than One Column Can Be Included Here

            'Create Column And Add To The DataTable
            DCol = New DataColumn("clecID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "clecID"
            DCol.Caption = "clecID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("ilecID", System.Type.GetType("System.Int64"))
            'Set Column Properties
            'DCol.ColumnName = "ilecID"
            DCol.Caption = "ilecID"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Resale", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "Resale"
            DCol.Caption = "Resale"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("UNEP", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "UNEP"
            DCol.Caption = "UNEP"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("TaskOrder", System.Type.GetType("System.Int32"))
            'Set Column Properties
            'DCol.ColumnName = "TaskOrder"
            DCol.Caption = "TaskOrder"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Task", System.Type.GetType("System.String"))
            'Set Column Properties
            'DCol.ColumnName = "Task"
            DCol.Caption = "Task"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Create Column And Add To The DataTable
            DCol = New DataColumn("Descr", System.Type.GetType("System.String"))
            'Set Column Properties
            'DCol.ColumnName = "Descr"
            DCol.Caption = "Descr"
            DCol.DefaultValue = 0
            'Add To Columns Collection
            dt.Columns.Add(DCol)

            'Return The DataTable
            CreateTableMemoryObjectPK = dt
        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Creating A DataTable With A PK!", _
                "The Application Cannot Continue Without Creating The Data Table.", _
                ContactTechSupport, _
                ThisModule & ThisSub & System.Environment.NewLine, _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException


        Finally
            DCol.Dispose()
            dt.dispose()
        End Try

    End Function

    Public Function CreateDSFromSQL(ByRef DTbl As DataTable) As DataSet
        'Open The SQLite3 Database File
        Dim DSet As New DataSet 'ADO DataSet
        Dim DRow As DataRow
        Dim Col As DataColumn
        Dim DCol As DataColumn
        Dim RowCtr As Integer
        Dim ColCtr As Integer
        Dim ThisSub As String = "CreateDSFromSQL"

        Try
            conn.ConnectionString = "Data Source=" & DBName & ";New=False;Version=3"
            conn.Open()
            cmd = conn.CreateCommand

            cmd.CommandText = "SELECT ID, clecID, ilecID, Resale, UNEP, TaskOrder, Task, Descr FROM BillTasks ORDER BY ID"
            rd = cmd.ExecuteReader() 'SQLite Supports DataReader Only

            'NOTE - The Columns In The DataReader Do Not Seem To Be In The Same Order As In The Select Statement

            Do While rd.Read()
                ColCtr = 0 'SQLite Column Counter
                DRow = DTbl.NewRow()
                For Each Col In DTbl.Columns 'ADO Column Counter
                    'For Each DCol In DTbl.Columns
                    If Col.DataType() Is System.Type.GetType("System.String") Then
                        DRow(Col) = rd.GetString(rd.Item(ColCtr))
                    ElseIf Col.DataType() Is System.Type.GetType("System.Int64") Then
                        DRow(Col) = rd.GetInt64(rd.Item(ColCtr))
                    ElseIf Col.DataType() Is System.Type.GetType("System.Int32") Then
                        DRow(Col) = rd.GetInt32(rd.Item(ColCtr))
                    End If
                    ColCtr += 1
                Next
                DTbl.Rows.Add(DRow)
            Loop
            DTbl.AcceptChanges()

            'Add The Table To It
            DSet.Tables.Add(DTbl)
            'Return The DataSet
            CreateDSFromSQL = DSet

            rd.Close()
            rd.Dispose()
            cmd.Dispose()
            conn.Close()
        conn.Dispose()
        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Creating A DataSet From SQLite Database!", _
                "The Application Cannot Continue Without Creating The DataSet.", _
                ContactTechSupport, _
                ThisModule & ThisSub & System.Environment.NewLine, _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException
        Finally
            rd.Close()
            rd.Dispose()
            cmd.Dispose()
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function AddDataToDTableFromSQL(ByRef DTbl As DataTable)
        'Open The SQLite3 Database File
        Dim DSet As New DataSet 'ADO DataSet
        Dim DRow As DataRow
        Dim DCol As DataColumn
        Try
            conn.ConnectionString = "Data Source=" & DBName & ";New=False;Version=3"
            conn.Open()
            cmd = conn.CreateCommand

            cmd.CommandText = "SELECT ID, clecID, ilecID, Resale, UNEP, TaskOrder, Task, Descr FROM BillTasks"
            rd = cmd.ExecuteReader() 'SQLite Supports DataReader Only

            Do While rd.Read()
                'ColCtr = 0 'SQLite Column Counter
                DRow = DTbl.NewRow()
                For Each DCol In DTbl.Columns 'ADO Column Counter
                    If DCol.DataType() Is System.Type.GetType("System.String") Then
                        If DCol.ColumnName = "Task" Then
                            DRow(DCol) = rd.GetString(rd.GetOrdinal("Task"))
                        ElseIf DCol.ColumnName = "Descr" Then
                            DRow(DCol) = rd.GetString(rd.GetOrdinal("Descr"))
                        Else
                            MsgBox("ERROR")
                        End If
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int64") Then
                        If DCol.ColumnName = "ID" Then
                            DRow(DCol) = rd.GetInt64(rd.GetOrdinal("ID"))
                        ElseIf DCol.ColumnName = "clecID" Then
                            DRow(DCol) = rd.GetInt64(rd.GetOrdinal("clecID"))
                        ElseIf DCol.ColumnName = "ilecID" Then
                            DRow(DCol) = rd.GetInt64(rd.GetOrdinal("ilecID"))
                        Else
                            MsgBox("ERROR")
                        End If
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int32") Then
                        If DCol.ColumnName = "Resale" Then
                            DRow(DCol) = rd.GetInt32(rd.GetOrdinal("Resale"))
                        ElseIf DCol.ColumnName = "UNEP" Then
                            DRow(DCol) = rd.GetInt32(rd.GetOrdinal("UNEP"))
                        ElseIf DCol.ColumnName = "TaskOrder" Then
                            DRow(DCol) = rd.GetInt32(rd.GetOrdinal("TaskOrder"))
                        Else
                            MsgBox("ERROR")
                        End If
                    End If
                Next
                DTbl.Rows.Add(DRow)
            Loop
            DTbl.AcceptChanges()

        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Adding DQLite Data To Data Table!", _
                "The Application Cannot Continue.", _
                ContactTechSupport, _
                "ThisModule & ThisSub", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException
        Finally
            rd.Close()
            rd.Dispose()
            cmd.Dispose()
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Sub GetILECs(ByRef ILECs() As String)
        'Open The SQLite3 Database File
        Dim NbrRec As Integer
        Try
            conn.ConnectionString = "Data Source=" & DBName & ";New=False;Version=3"
            conn.Open()
            cmd = conn.CreateCommand

            cmd.CommandText = "SELECT COUNT(*) FROM ILECs"
            NbrRec = cmd.ExecuteScalar()

            ReDim ILECs(NbrRec - 1)

            cmd.CommandText = "SELECT ID, Name FROM ILECs"
            rd = cmd.ExecuteReader()
            Dim i As Integer = 0
            While rd.Read()
                ILECs(i) = rd.GetString(rd.GetOrdinal("ID")) & "~" & rd.GetString(rd.GetOrdinal("Name"))
                i += 1
            End While

        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Getting The ILECs Information From SQLite Database!", _
                "The Application Cannot Continue.", _
                ContactTechSupport, _
                "ThisModule & ThisSub", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException
        Finally
            rd.Close()
            rd.Dispose()
            cmd.Dispose()
            conn.Close()
            conn.Dispose()
        End Try

    End Sub

    Public Sub CreateDatabaseHub()

        Try
            conn.ConnectionString = "Data Source=DatabaseHub.db;New=True;Version=3"
            conn.Open()
            cmd = conn.CreateCommand


            cmd.CommandText = "CREATE TABLE TaskStatus (ID int default 0 not null, " & _
                            "TaskID int default 0 not null, " & _
                            "ClientStarted DateTime default "" not null, " & _
                            "ClientFinished DateTime default "" not null, " & _
                            "SentToServer int default 0 not null, " & _
                            "ServerStarted DateTime default "" not null, " & _
                            "ServerFinished DateTime default "" not null)"

            cmd.ExecuteNonQuery()


            cmd.CommandText = "CREATE TABLE BillTasks (id int default 0 not null, " & _
                            "clecID int default 0 not null, " & _
                            "ilecID int default 0 not null, " & _
                            "Resale int default 0 not null, " & _
                            "UNEP int default 0 not null, " & _
                            "TaskOrder int default 0 not null, " & _
                            "Task varchar(100) default '' not null, " & _
                            "Descr varchar(255) default '' not null)"

            cmd.ExecuteNonQuery()

            'BillTasks Values
            Dim BTValues() As String = {3, 2, 1, 1, 0, 1, "AddBSDABResaleUSOCInfo", "Add All USOCs", _
                                        4, 2, 1, 1, 0, 2, "AddBSDABResaleOCCInfo", "Add All Other Charges And Credits", _
                                        5, 2, 1, 1, 0, 3, "AddBSDABResaleLocalUsage", "Add Local Usage Charges", _
                                        6, 2, 1, 1, 0, 4, "AddBSDABResaleItemizedCalls", "Add Itemized Calls Charges", _
                                        7, 2, 1, 1, 0, 5, "AddBSDABResaleDA", "Add Directory Assistance Charges"}
            Dim Row As Integer = 0
            Dim Col As Integer = 0

            cmd.CommandText = "INSERT INTO BillTasks (ID, clecID, ilecID, Resale, UNEP, TaskOrder, Task, Descr) " & _
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
            cmd.CreateAndAddUnnamedParameters()

            Dim Trans As IDbTransaction
            Trans = conn.BeginTransaction() 'Speeds Up Insert Operations
            Do While Row <= 4
                cmd.Parameters(0).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(1).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(2).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(3).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(4).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(5).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(6).Value = BTValues(Col)
                Col += 1
                cmd.Parameters(7).Value = BTValues(Col)
                Col += 1
                cmd.Prepare()
                cmd.ExecuteNonQuery()
                Row += 1
            Loop
            Trans.Commit()


            cmd.CommandText = "CREATE TABLE ilecs (id bigint(20) NOT NULL default 0, " & _
                            "SAGrpID bigint(20) NOT NULL default '0', " & _
                            "Abbr varchar(50) NOT NULL default '', " & _
                            "Name varchar(50) NOT NULL default '', " & _
                            "Created datetime NOT NULL default '0000-00-00 00:00:00')"

            cmd.ExecuteNonQuery()

            Dim IValues() As String = {1, 3, "BST", "BELLSOUTH", "0000-00-00 00:00:00", _
                                        2, 7, "SBC", "Southwestern Bell", "0000-00-00 00:00:00", _
                                        3, 4, "CTel", "CenturyTel", "0000-00-00 00:00:00", _
                                        4, 1, "AllTel", "AllTel", "0000-00-00 00:00:00", _
                                        5, 13, "Valor", "Valor", "0000-00-00 00:00:00", _
                                        6, 2, "AmTch", "AmeriTech", "0000-00-00 00:00:00", _
                                        7, 5, "CinBell", "Cincinnati Bell", "0000-00-00 00:00:00", _
                                        8, 6, "Fmrs", "Farmers", "0000-00-00 00:00:00", _
                                        9, 8, "Sprint", "Sprint", "0000-00-00 00:00:00", _
                                        10, 9, "Vrzn", "Verizon", "0000-00-00 00:00:00", _
                                        11, 10, "TT", "TeleTouch", "0000-00-00 00:00:00", _
                                        12, 11, "SIT", "Stay In Touch", "0000-00-00 00:00:00", _
                                        13, 12, "RR", "Red River", "0000-00-00 00:00:00"}
            Row = 0
            Col = 0

            cmd.CommandText = "INSERT INTO ILECs (ID, SAGrpID, Abbr, Name, Created) " & _
                           "VALUES (?, ?, ?, ?, ?)"
            cmd.CreateAndAddUnnamedParameters()

            Trans = conn.BeginTransaction() 'Speeds Up Insert Operations
            Do While Row <= 12
                cmd.Parameters(0).Value = IValues(Col)
                Col += 1
                cmd.Parameters(1).Value = IValues(Col)
                Col += 1
                cmd.Parameters(2).Value = IValues(Col)
                Col += 1
                cmd.Parameters(3).Value = IValues(Col)
                Col += 1
                cmd.Parameters(4).Value = IValues(Col)
                Col += 1
                cmd.Prepare()
                cmd.ExecuteNonQuery()
                Row += 1
            Loop
            Trans.Commit()

        Catch 'Any Error
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Creating The SQLite Database!", _
                "The Application Cannot Continue.", _
                ContactTechSupport, _
                "ThisModule & ThisSub", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException
        Finally
            cmd.Dispose()
            conn.Close()
            conn.Dispose()
        End Try


    End Sub

End Module
