Imports System
Imports System.Data.Common
Imports System.Data
Imports System.IO
Imports System.Configuration
Imports System.Reflection


Public Class DBTable
    Inherits DBsqlce
    Private DBConnStr As String
    Public Sub New(ByVal DBConnStr As String)
        MyBase.New(DBConnStr)
    End Sub
    Public Function TableIndex() As String
        Return ClassName.Substring(4) & "_ID"
    End Function
    Public Function GetDS() As DataSet
        Dim ErrLoc As String = ClassName + ".GetDS"
        Dim SQL As String
        SQL = "SELECT * FROM " + ClassName + " "

        Try
            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)

            Return ds
        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
        End Try
    End Function
    Public Function GetDS(ByRef Row As ValueType) As DataSet
        Dim ErrLoc As String = ClassName + ".GetDS(Row)"
        Dim SQL As String

        Try
            Dim i As Integer
            Dim Seperator As String = ""
            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            SQL = "SELECT * FROM " + ClassName + " WHERE "

            For i = 0 To fields.Length - 1
                If Not fields(i).GetValue(Row) Is Nothing Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row).ToString = "<NOTHING>" Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= SQLStringValue(fields(i).GetValue(Row).ToString)
                                Seperator = "AND "
                            End If
                        Case "System.DateTime"
                            If Not DATE_To_DBSTR(fields(i).GetValue(Row)) = NullDate Then
                                SQL &= Seperator & fields(i).Name & " = "
                                'SQL &= " #" & DATE_To_DBSTR(fields(i).GetValue(Row)) & "# "
                                SQL &= SQLDateValue(fields(i).GetValue(Row))
                                Seperator = "AND "
                            End If
                        Case "System.Boolean"
                            SQL &= Seperator & fields(i).Name & " = "
                            SQL &= SQLBooleanValue(fields(i).GetValue(Row))
                            Seperator = "AND "
                        Case Else
                            If Not fields(i).GetValue(Row) = "0" Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= fields(i).GetValue(Row).ToString & " "
                                Seperator = "AND "
                            End If
                    End Select
                End If
            Next

            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)

            Return ds

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
        End Try

    End Function
    Public Function GetDS(ByRef SearchTextFields As String, ByRef Row As ValueType) As DataSet
        Dim ErrLoc As String = ClassName + ".GetDS(SearchTextFields)"
        Dim SQL As String

        Try
            Dim i, j As Integer
            Dim Seperator As String = ""
            Dim OrSeperator As String = ""
            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            Dim Words() As String
            Words = Split(SearchTextFields, " ")

            SQL = "SELECT * FROM " + ClassName + " WHERE "

            For i = 0 To fields.Length - 1
                If Not fields(i).GetValue(Row) Is Nothing Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            SQL &= OrSeperator & " ( "
                            For j = 0 To Words.Length - 1
                                If Words(j).Substring(0, 1) = "+" Then
                                    SQL &= Seperator & fields(i).Name & " Like "
                                    SQL &= " '%" & DBSTR(Words(j).Substring(1, Words(j).Length - 1)) & "%' "
                                    Seperator = "AND "
                                ElseIf Words(j).Substring(0, 1) = "-" Then
                                    SQL &= Seperator & " NOT " & fields(i).Name & " Like "
                                    SQL &= " '%" & DBSTR(Words(j).Substring(1, Words(j).Length - 1)) & "%' "
                                    Seperator = "AND "
                                Else
                                    SQL &= Seperator & fields(i).Name & " Like "
                                    SQL &= " '%" & DBSTR(Words(j).Substring(1, Words(j).Length - 1)) & "%' "
                                    Seperator = "AND "
                                End If
                            Next
                            Seperator = ""
                            OrSeperator = "OR "
                            SQL &= " ) "
                        Case "System.DateTime"
                        Case "System.Boolean"
                        Case Else
                    End Select
                End If
            Next

            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)

            Return ds

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
        End Try

    End Function

    Public Function DeleteAll() As Boolean
        Dim ErrLoc As String = ClassName + ".DeleteAll"
        Dim SQL As String

        SQL = "Delete from " + ClassName + " "

        Try
            If ExecuteSQL(SQL) Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function
    Public Function Delete(ByRef Row As ValueType) As Boolean
        Dim ErrLoc As String = ClassName + ".Delete"
        Dim SQL As String

        Try
            Dim i As Integer
            Dim Seperator As String = ""
            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            SQL = "DELETE FROM " + ClassName + " WHERE "

            For i = 0 To fields.Length - 1
                If Not fields(i).GetValue(Row) Is Nothing Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row).ToString = "<NOTHING>" Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= SQLStringValue(fields(i).GetValue(Row).ToString)
                                Seperator = "AND "
                            End If
                        Case "System.DateTime"
                            If Not DATE_To_DBSTR(fields(i).GetValue(Row)) = NullDate Then
                                SQL &= Seperator & fields(i).Name & " = "
                                'SQL &= " #" & DATE_To_DBSTR(fields(i).GetValue(Row)) & "# "
                                SQL &= SQLDateValue(fields(i).GetValue(Row))
                                Seperator = "AND "
                            End If
                        Case "System.Boolean"
                            SQL &= Seperator & fields(i).Name & " = "
                            SQL &= SQLBooleanValue(fields(i).GetValue(Row))
                            Seperator = "AND "
                        Case Else
                            If Not fields(i).GetValue(Row) = "0" Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= fields(i).GetValue(Row).ToString & " "
                                Seperator = "AND "
                            End If
                    End Select
                End If
            Next

            If ExecuteSQL(SQL) Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try
    End Function
    Public Function DeleteRecord(ByVal ID As Integer) As Boolean
        Dim ErrLoc As String = ClassName + ".DeleteRecord"
        Dim SQL As String

        SQL = "Delete from " + ClassName + " "
        SQL += "WHERE "
        SQL += TableIndex() + "=" + ID.ToString + " "

        Try
            If ExecuteSQL(SQL) Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try
    End Function

    Public Function ExportXML(ByVal ExportDir As String) As Boolean
        Dim SQL As String
        Dim ErrLoc As String = ClassName + ".ExportXML"
        Dim ds As DataSet

        Try
            REM Get a data of all records 
            ds = GetDS()

            REM If there is an error let us know about it
            If ErrMsg <> "" Then MsgBox(ErrMsg)

            REM Export table to an XML file
            ds.WriteXml(ExportDir + "\" + ClassName + ".xml")
            ds.WriteXmlSchema(ExportDir + "\" + ClassName + ".xsl")

            REM Return successfull
            Return True

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try
    End Function
    Public Function ImportXML(ByVal ImportDir As String, ByRef Row As ValueType) As Boolean
        Dim SQL As String
        Dim ErrLoc As String = ClassName + ".ImportXML"

        Dim ds As New DataSet
        Dim Rows As Integer
        Dim Cols As Integer
        Dim SQLHeader As String
        Dim Seperator As String = ""
        Dim TypeStr As String = ""

        Try
            REM Import table to an XML file
            ds.ReadXml(ImportDir + "\" + ClassName + ".xml")
            'ds.ReadXmlSchema(ImportDir + "\" + ClassName + ".xsl")

            SQLHeader = "INSERT INTO " + ClassName + " ( "
            For Cols = 0 To ds.Tables(ClassName).Columns.Count - 1
                REM biuld sql header
                If Not ds.Tables(ClassName).Columns(Cols).ColumnName = TableIndex() Then
                    SQLHeader &= Seperator & ds.Tables(ClassName).Columns(Cols).ColumnName
                    Seperator = ","
                End If
            Next


            REM Establish connection to database
            objConn = OpenConnection()


            Dim strValue As String
            Dim dValue As Double
            Dim dtValue As DateTime
            Dim Validflg As Boolean


            Dim i As Integer


            REM fill database
            SQLHeader &= ") VALUES ( "
            For Rows = 0 To ds.Tables(ClassName).Rows.Count - 1
                Validflg = True

                SQL = SQLHeader
                Seperator = ""
                For Cols = 0 To ds.Tables(ClassName).Columns.Count - 1