Imports MySql.Data.MySqlClient


Module ManageDb

#Region "FIELDS & PROPERTIES"

   Private m_DbPath As String ' String.Empty

   Public Property DbPath As String
      Get
         Return m_DbPath
      End Get
      Set(value As String)
         m_DbPath = value
      End Set
   End Property

   Private m_DbConnection As MySqlConnection = New MySqlConnection(m_DbPath)
   Public Property DbConnection As MySqlConnection
      Get
         Return m_DbConnection
      End Get
      Set(value As MySqlConnection)
         m_DbConnection = value
      End Set
   End Property

#End Region    ' FIELDS & PROPERTIES

#Region "METHODS"

   Friend Function ConnectToDb() As Boolean
      Dim DbPath As String = Map.refMainWindowVM.MainWindowM.sDbConnect
      m_DbPath = DbPath
      m_DbConnection = New MySqlConnection(DbPath)
      Return Not IsNothing(m_DbConnection)
   End Function

   Friend Function ConnectToDb(DbPath As String) As Boolean
      m_DbPath = DbPath
      m_DbConnection = New MySqlConnection(DbPath)
      Return Not IsNothing(m_DbConnection)
   End Function

   Friend Sub CreateTable()

      Dim MySqlCommand As String

      MySqlCommand = "CREATE TABLE " & DB_RESELLER & " (" & DB_RESELLERID & " INTEGER PRIMARY KEY, " &
                                                                        DB_RESELLERNAME & " TEXT)"
      ExecuteQuery(MySqlCommand)

      MySqlCommand = "CREATE TABLE " & DB_CLIENT & " (" & DB_NAME & " TEXT, " &
                                                        DB_RESELLERID & " INTEGER," &
                                                        DB_CLIENTID & " INTEGER PRIMARY KEY, " &
                                                        "FOREIGN KEY (" & DB_RESELLERID & ") REFERENCES " & DB_RESELLER & " (" & DB_RESELLERID & "))"
      ExecuteQuery(MySqlCommand)

      MySqlCommand = "CREATE TABLE " & DB_KEY & " (" & DB_NUMBER & " INTEGER, " &
                                                     DB_CLIENTID & " INTEGER, " &
                                                     DB_ISDONGLE & " INTEGER, " &
                                                     DB_LOCKID & " VARCHAR(45) PRIMARY KEY, " & ' " TEXT PRIMARY KEY, " &
                                                     "FOREIGN KEY (" & DB_CLIENTID & ") REFERENCES " & DB_CLIENT & " (" & DB_CLIENTID & "))"
      ExecuteQuery(MySqlCommand)

      MySqlCommand = "CREATE TABLE " & DB_LICENCE & " (" & DB_LICENCEID & " INTEGER PRIMARY KEY, " &
                                                         DB_PRODUCTID & " INTEGER, " &
                                                         DB_PRODUCTVERSION & " INTEGER, " & ' " TEXT, " &
                                                         DB_PRODUCTLEVEL & " INTEGER, " &
                                                         DB_PRODUCTDEADLINE & " DATE, " & ' " INTEGER, " &
                                                         DB_OPTION1 & " INTEGER, " &
                                                         DB_OPTION2 & " INTEGER, " &
                                                         DB_OPTIONDEADLINE & " DATE, " & ' " INTEGER, " &
                                                         DB_LOCKID & " VARCHAR(45), " & ' " TEXT, " &
                                                         DB_FILE & " VARCHAR(45), " & ' " TEXT, " &
                                                         "FOREIGN KEY (" & DB_LOCKID & ") REFERENCES " & DB_KEY & " (" & DB_LOCKID & "), " &
                                                         "FOREIGN KEY (" & DB_PRODUCTID & ") REFERENCES " & DB_PRODUCT & " (" & DB_PRODUCTID & "))"
      ExecuteQuery(MySqlCommand)

      MySqlCommand = "CREATE TABLE " & DB_PRODUCT & " (" & DB_PRODUCTID & " INTEGER PRIMARY KEY, " &
                                                         DB_PRODUCTNAME & " TEXT, " &
                                                         DB_PRODUCTNUMBER & " INTEGER, " &
                                                         DB_PRODUCTOPTION1 & " INTEGER, " &
                                                         DB_PRODUCTOPTION2 & " INTEGER)"
      ExecuteQuery(MySqlCommand)

      MySqlCommand = "CREATE TABLE " & DB_VERSION & " (" & DB_VERSIONID & " INTEGER PRIMARY KEY, " &
                                                         DB_PRODUCTID & " INTEGER, " &
                                                         DB_VERSIONNUMBER & " INTEGER, " &
                                                         "FOREIGN KEY (" & DB_PRODUCTID & ") REFERENCES " & DB_PRODUCT & " (" & DB_PRODUCTID & "))"
      ExecuteQuery(MySqlCommand)
   End Sub

   Friend Function ExecuteQuery(MySqlQuery As String) As Integer
      Dim ModifiedRowNumber As Integer
      Try
         m_DbConnection.Open()
         Dim Command As MySqlCommand = New MySqlCommand(MySqlQuery, m_DbConnection)
         ModifiedRowNumber = Command.ExecuteNonQuery()
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      Finally
         m_DbConnection.Close()
      End Try
      Return ModifiedRowNumber
   End Function

   Friend Function ExecuteReaderQuery(SqlQuery As String) As MySqlDataReader
      Dim Command As MySqlCommand = New MySqlCommand(SqlQuery, m_DbConnection)
      Dim Reader As MySqlDataReader = Command.ExecuteReader()
      Return Reader
   End Function

   'Friend Function ExecuteVersionQuery(SqlQuery As String) As List(Of Version)
   '   ManageDb.DbConnection.Open()
   '   Dim Command As SQLiteCommand = New SQLiteCommand(SqlQuery, m_DbConnection)
   '   Dim Reader As SQLiteDataReader = Command.ExecuteReader()
   '   Dim VersionList As New List(Of Version)
   '   While Reader.Read()
   '      VersionList.Add(New Version(Reader))
   '   End While
   '   ManageDb.DbConnection.Close()
   '   Return VersionList
   'End Function

   Friend Function ExecuteStringQuery(MySqlQuery As String, ResColumnName As String) As List(Of String)
      Dim StringList As New List(Of String)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     If (Not Reader(ResColumnName).Equals(DBNull.Value)) Then
                        StringList.Add(CType(Reader(ResColumnName), String))
                     End If
                  End While
                  If IsNothing(StringList) Or StringList.Count = 0 Then
                     StringList.Add(String.Empty)
                  End If
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return StringList
   End Function

   Friend Function ExecuteStringDictionaryQuery(MySqlQuery As String) As Dictionary(Of String, String)
      Dim StringDict As New Dictionary(Of String, String)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     For index = 0 To Reader.FieldCount - 1
                        StringDict.Add(Reader.GetName(index), Reader.GetValue(index).ToString())
                     Next
                  End While
                  If IsNothing(StringDict) Or StringDict.Count = 0 Then
                     StringDict.Add(String.Empty, String.Empty)
                  End If
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return StringDict
   End Function

   Friend Function ExecuteIntegerQuery(MySqlQuery As String, ResColumnName As String) As List(Of Integer)
      Dim IntegerList As New List(Of Integer)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     IntegerList.Add(CInt(Reader(ResColumnName)))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return IntegerList
   End Function

   Friend Function ExecuteClientQuery(MySqlQuery As String) As List(Of Client)
      Dim ClientList As New List(Of Client)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ClientList.Add(New Client(Reader))
                  End While
                  If IsNothing(ClientList) Then
                     ClientList.Add(Nothing)
                  End If
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ClientList
   End Function

   Friend Function ExecuteKeyQuery(MySqlQuery As String) As List(Of Key)
      Dim KeyList As New List(Of Key)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     KeyList.Add(New Key(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return KeyList
   End Function

   Friend Function ExecuteProductQuery(MySqlQuery As String) As List(Of Product)
      Dim ProductList As New List(Of Product)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ProductList.Add(New Product(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ProductList
   End Function

   Friend Function ExecuteVersionQuery(MySqlQuery As String) As List(Of Version)
      Dim VersionList As New List(Of Version)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     VersionList.Add(New Version(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return VersionList
   End Function

   Friend Function ExecuteNumberQuery(MySqlQuery As String) As Integer
      Dim Number As Integer
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  Reader.Read()
                  Number = If(Reader(DB_MAXNUMBER) Is DBNull.Value, 0, CInt(Reader(DB_MAXNUMBER)))
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return Number
   End Function

   Friend Function ExecuteResellerQuery(MySqlQuery As String) As List(Of Reseller)
      Dim ResellerList As New List(Of Reseller)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ResellerList.Add(New Reseller(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ResellerList
   End Function

   Friend Function ExecuteSearchClientQuery(MySqlQuery As String) As List(Of SearchClient)
      Dim ClientList As New List(Of SearchClient)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ClientList.Add(New SearchClient(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ClientList
   End Function

   Friend Function ExecuteSearchKeyQuery(MySqlQuery As String) As List(Of SearchKey)
      Dim KeyList As New List(Of SearchKey)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     KeyList.Add(New SearchKey(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return KeyList
   End Function

   Friend Function ExecuteSearchLicenceQuery(MySqlQuery As String) As List(Of SearchLicence)
      Dim LicenceList As New List(Of SearchLicence)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     LicenceList.Add(New SearchLicence(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return LicenceList
   End Function

   Friend Function ExecuteExpiringLicenceQuery(MySqlQuery As String, ProductList As List(Of Product), ClientList As List(Of Client)) As List(Of SearchLicence)
      Dim LicenceList As New List(Of SearchLicence)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     LicenceList.Add(New SearchLicence(Reader, ProductList, ClientList))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return LicenceList
   End Function

   Friend Function ExecuteSearchProductQuery(MySqlQuery As String) As List(Of Product)
      Dim ProductList As New List(Of Product)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ProductList.Add(New Product(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ProductList
   End Function

   Friend Function ExecuteSearchVersionQuery(MySqlQuery As String) As List(Of SearchVersion)
      Dim VersionList As New List(Of SearchVersion)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     VersionList.Add(New SearchVersion(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return VersionList
   End Function

   Friend Function ExecuteSearchResellerQuery(MySqlQuery As String) As List(Of SearchReseller)
      Dim ResellerList As New List(Of SearchReseller)
      Try
         Using DbConnection As New MySqlConnection(m_DbPath)
            DbConnection.Open()
            Using Command As MySqlCommand = New MySqlCommand(MySqlQuery, DbConnection)
               Using Reader As MySqlDataReader = Command.ExecuteReader()
                  While Reader.Read()
                     ResellerList.Add(New SearchReseller(Reader))
                  End While
               End Using
            End Using
            DbConnection.Close()
         End Using
      Catch ex As MySqlException
         MessageBox.Show(ex.Message)
      End Try
      Return ResellerList
   End Function


   Friend Sub AddRandomRows(nRow As Integer)
      Dim ImagePathList() As String = {"c:\EgtData\OmagPHOTO\Data\Lastra15_18.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra18_10.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra313.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra313_b.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra3138bit.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra33-18_234.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra55_33.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Lastra55-23.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Prova1.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Prova2.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Prova3.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\Prova4.jpg"}
      Dim Text As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      For i = 0 To nRow
         Dim R As New Random()
         Dim Name As String = "Lastra" & i
         Dim ImagePath As String = ImagePathList(R.Next(0, ImagePathList.Length))
         'Dim SlabState As String = Map.refOptionPanelVM.StateList(R.Next(0, Map.refOptionPanelVM.StateList.Count)).Id.ToString
         'Dim Material As String = Map.refOptionPanelVM.MaterialList(R.Next(0, Map.refOptionPanelVM.MaterialList.Count))
         Dim SlabThickness As String = (R.Next(200, 700) / 10).ToString
         Dim WarehousePosition As String = Text(R.Next(Text.Length)) & R.Next(1, 100) & "." & R.Next(1, 100)
         Dim AddedDate As DateTime = DateTime.Now - New TimeSpan(R.Next(0, 730), R.Next(0, 24), R.Next(0, 60), R.Next(0, 60))
         ' Aggiungo la nuova lastra al Db
         'Dim Query As String = "INSERT INTO " & Slab.DB_SLABS & " (" & Slab.DB_ID & ", " & Slab.DB_IMAGEPATH & ", " & Slab.DB_STATE & ", " & Slab.DB_PROJASSIGNEDTO & ", " & Slab.DB_MATERIAL & ", " & Slab.DB_THICKNESS & ", " & Slab.DB_WAREHOUSEPOS & ", " & Slab.DB_ADDEDDATE & ")" &
         '                      " VALUES ('" & Name & "', " &
         '                              "'" & ImagePath & "', " &
         '                                    SlabState & ", " &
         '                              "'" & "C:\EgtData\OmagOFFICE\Progetto" & i & "', " &
         '                              "'" & Material & "', " &
         '                                    SlabThickness & ", " &
         '                              "'" & WarehousePosition & "', " &
         '                              "Date('" & (String.Format("{0:yyyy-MM-dd}", AddedDate)) & "'))"
         'ManageDb.ExecuteQuery(Query)
      Next
   End Sub

#End Region    ' METHODS

End Module