EgtPHOTOLib/UtilitySlab/ManageDb.vb

Imports System.IO
Imports System.Data.SQLite
Imports EgtUILib
Imports EgtWPFLib5

Public Module ManageDb

#Region "FIELDS & PROPERTIES"

   Public Const DB_FILENAME As String = "OmagPHOTODb.sqlite"
   Public dFindColumnAddedDate As Boolean = False

   Private m_DbConnection As SQLiteConnection
   Public ReadOnly Property DbConnection As SQLiteConnection
      Get
         Return m_DbConnection
      End Get
   End Property

   Private BackUpDbCallBack As New SQLiteBackupCallback(AddressOf BackUpDbCallBackFunction)

#End Region    ' FIELDS & PROPERTIES

#Region "METHODS"

   Friend Sub CreateDbFile()
      SQLiteConnection.CreateFile(MainData.sPhotoDir & "\" & DB_FILENAME)
   End Sub

   Friend Function ConnectToDb() As Boolean
      Dim DbPath As String = MainData.sPhotoDir & "\" & DB_FILENAME
      If Not File.Exists(DbPath) Then Return False
      m_DbConnection = New SQLiteConnection("Data Source = " & DbPath & "; Version = 3;")
      Return Not IsNothing(m_DbConnection)
   End Function

   Public Function ConnectToDb(DbPath As String) As Boolean
      EgtOutLog("Connecting to DB " & DbPath)
      If Not File.Exists(DbPath) Then Return False
      Try
         m_DbConnection = New SQLiteConnection("Data Source = " & DbPath & "; Version = 3;")
         ' Verifica integrità del DB
         m_DbConnection.Open()
         Dim Command As SQLiteCommand = New SQLiteCommand("PRAGMA quick_check;", m_DbConnection)
         Dim Reader As SQLiteDataReader = Command.ExecuteReader()
         Reader.Read()
         Dim sRes As String = Reader(0)
         m_DbConnection.Close()
      Catch ex As Exception
         EgtOutLog(ex.Message)
         Return False
      End Try
      Return Not IsNothing(m_DbConnection)
   End Function

   Friend Sub CreateTable()
      Dim SqlCommand As String = "CREATE TABLE " & Slab.DB_SLABS & " (" & Slab.DB_ID & " TEXT PRIMARY KEY," &
                                                                          Slab.DB_IMAGEPATH & " TEXT," &
                                                                          Slab.DB_STATE & " INTEGER," &
                                                                          Slab.DB_PROJASSIGNEDTO & " TEXT," &
                                                                          Slab.DB_MATERIAL & " TEXT," &
                                                                          Slab.DB_THICKNESS & " REAL," &
                                                                          Slab.DB_WAREHOUSEPOS & " TEXT," &
                                                                          Slab.DB_ADDEDDATE & " INTEGER)"
      ExecuteQuery(SqlCommand)
   End Sub

   Friend Sub CreateDataTimeTable()
      Dim bExisttable As Boolean = False
      ' Creo una nuova tabella
      Dim SqlCommand As String = "CREATE TABLE " & Slab.DB_DATATIME & " (Id TEXT PRIMARY KEY DEFAULT 'MyKey', LastModify INTEGER DEFAULT 0 )"
      Try
         ExecuteQuery(SqlCommand)
      Catch ex As Exception
         EgtOutLog(ex.Message)
         m_DbConnection.Close()
         bExisttable = True
      End Try

      Dim bOkCmd As Boolean = False
      If bExisttable Then
         ' provo ad inserire la nuova riga della tabella
         SqlCommand = "INSERT INTO " & Slab.DB_DATATIME & " (Id, LastModify) VALUES( " & "'" & MainData.sUser & "'" & ", " & "'" & (String.Format("{0:MM/dd/yy H:mm:ss}", DateTime.Now)) & "'" & " );"
         Try
            ExecuteQuery(SqlCommand)
            bOkCmd = True
         Catch ex As Exception
            EgtOutLog(ex.Message)
            m_DbConnection.Close()
            bOkCmd = False
         End Try
         ' se l'inserimento fallisce allora procedo ad aggiornare il valore
         If Not bOkCmd Then
            SqlCommand = "UPDATE " & Slab.DB_DATATIME & " SET " & "LastModify" & " = " & "'" & (String.Format("{0:MM/dd/yy H:mm:ss}", DateTime.Now)) & "'"
            SqlCommand &= " WHERE Id = " & "'" & MainData.sUser & "'" & ";"
            Try
               ExecuteQuery(SqlCommand)
               bOkCmd = True
            Catch ex As Exception
               EgtOutLog(ex.Message)
               m_DbConnection.Close()
               bOkCmd = False
            End Try
         End If
      End If

      If bOkCmd Then Return

      ' inserisco la prima riga della tabella
      SqlCommand = "INSERT INTO " & Slab.DB_DATATIME & " (Id, LastModify) VALUES(0,01/01/00 00:00:00);"
      Try
         ExecuteQuery(SqlCommand)
      Catch ex As Exception
         EgtOutLog(ex.Message)
         m_DbConnection.Close()
      End Try
   End Sub

   Friend Sub AddIsSelectedColumn()
      ' creazione nuovo campo con valore di default 0
      Dim SqlCommand As String = "ALTER TABLE " & Slab.DB_SLABS & " ADD " & Slab.DB_ISSELECTED & " INTEGER DEFAULT " & 0 & ";"
      Try
         ExecuteQuery(SqlCommand)
      Catch ex As Exception
         EgtOutLog(ex.Message)
         m_DbConnection.Close()
      End Try
   End Sub

   Friend Function ExecuteQuery(SqlQuery As String) As Integer
      m_DbConnection.Open()
      Dim Command As SQLiteCommand = New SQLiteCommand(SqlQuery, m_DbConnection)
      Dim ModifiedRowNumber As Integer = Command.ExecuteNonQuery()
      m_DbConnection.Close()
      Return ModifiedRowNumber
   End Function

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

   Friend Function ExecuteSlabReaderQuery(SqlQuery As String) As List(Of Slab)
      Dim SlabList As New List(Of Slab)
      ManageDb.DbConnection.Open()
      Try
         Dim Command As SQLiteCommand = New SQLiteCommand(SqlQuery, m_DbConnection)
         Dim Reader As SQLiteDataReader = Command.ExecuteReader()
         ' ricerco l'indice del campo AddedDate (questo indice è usato in fase di lettura dei campi del DB)
         Dim IndexColumn As Integer = 0
         Dim bFound As Boolean = False
         While Not (dFindColumnAddedDate Or bFound)
            bFound = (Reader.GetName(IndexColumn) = Slab.DB_ADDEDDATE)
            IndexColumn += 1
            If IndexColumn > Reader.FieldCount - 1 Then Exit While
         End While
         ' eseguo l'assegnazione della colonna solo al primo avvio del programma
         If bFound And Not dFindColumnAddedDate Then
            Slab.IndexAddedDate = IndexColumn - 1
            dFindColumnAddedDate = True
         End If
         While Reader.Read()
            SlabList.Add(New Slab(Reader))
         End While
      Catch ex As Exception
      End Try
      ManageDb.DbConnection.Close()
      Return SlabList
   End Function

   Friend Function ExecuteDataTimeReaderQuery(SqlQuery As String) As List(Of DataTimeRecord)
      Dim ListDT As New List(Of DataTimeRecord)
      ManageDb.DbConnection.Open()
      Try
         Dim Command As SQLiteCommand = New SQLiteCommand(SqlQuery, m_DbConnection)
         Dim Reader As SQLiteDataReader = Command.ExecuteReader()
         ' ricerco l'indice del campo AddedDate (questo indice è usato in fase di lettura dei campi del DB)
         Dim IndexColumn As Integer = 0
         Dim bFound As Boolean = False
         While Reader.Read()
            ListDT.Add(New DataTimeRecord(Reader))
         End While
      Catch ex As Exception
      End Try
      ManageDb.DbConnection.Close()
      Return ListDT
   End Function

   Public Function ManageBackUp() As Boolean
      ClearBackUps()
      Dim BackUpDbPath As String = MainData.sBackUpDir & "\" & Path.GetFileNameWithoutExtension(DB_FILENAME) & Date.Now.ToString("yyyy-MM-dd_HH-mm-ss") & ".sqlite"
      SQLiteConnection.CreateFile(BackUpDbPath)
      Using BackUpDbConnection As New SQLiteConnection("Data Source = " & BackUpDbPath & "; Version=3;")
         m_DbConnection.Open()
         BackUpDbConnection.Open()
         m_DbConnection.BackupDatabase(BackUpDbConnection, "main", "main", -1, BackUpDbCallBack, 0)
         m_DbConnection.Close()
         BackUpDbConnection.Close()
      End Using
      LibMap.refStatusBarVM.SetLoadingProgress(100)
      LibMap.refStatusBarVM.SetLoadingProgress_Visibility(False)
      Return True
   End Function

   Private Sub ClearBackUps()
      Try
         Dim bMonthMaintain() As Boolean = {False, False, False, False, False, False, False, False, False, False, False, False}
         Dim bWeekMaintain() As Boolean = {False, False, False, False}
         For Each file As IO.FileInfo In New IO.DirectoryInfo(MainData.sBackUpDir).GetFiles("*.sqlite").OrderBy(Function(f) f.CreationTime)
            If (Now - file.CreationTime).Days > 30 Then
               For I = 12 To 2 Step -1
                  If (Now - file.CreationTime).Days <= 30.5 * I AndAlso (Now - file.CreationTime).Days > 30.5 * (I - 1) Then
                     If Not bMonthMaintain(I - 1) Then
                        bMonthMaintain(I - 1) = True
                        Exit For
                     Else
                        file.Delete()
                        Exit For
                     End If
                  End If
               Next
            ElseIf (Now - file.CreationTime).Days > 28 Then
               file.Delete()
               Continue For
            Else
               For I = 4 To 2 Step -1
                  If (Now - file.CreationTime).Days <= 7 * I AndAlso (Now - file.CreationTime).Days > 7 * (I - 1) Then
                     If Not bWeekMaintain(I - 1) Then
                        bWeekMaintain(I - 1) = True
                        Exit For
                     Else
                        file.Delete()
                        Exit For
                     End If
                  End If
               Next
            End If
         Next
      Catch ex As Exception
         EgtOutLog(ex.Message)
      End Try
   End Sub

   Friend Sub CreateFileToClear()
      Try
         Dim MostRecentFile As IO.FileInfo = Nothing
         For Each CurrFile As IO.FileInfo In New IO.DirectoryInfo(MainData.sBackUpDir).GetFiles("*.sqlite")
            If Not IsNothing(MostRecentFile) Then
               If (MostRecentFile.CreationTime < CurrFile.CreationTime) Then
                  File.Delete(MostRecentFile.FullName)
                  MostRecentFile = CurrFile
               Else
                  File.Delete(CurrFile.FullName)
               End If
            Else
               MostRecentFile = CurrFile
            End If
         Next
         If IsNothing(MostRecentFile) Then Return
         For I = 0 To 365
            Dim CreationDate As Date = Date.Now.AddDays(-I)
            Dim NewFilePath As String = MainData.sBackUpDir & "\" & Path.GetFileNameWithoutExtension(DB_FILENAME) & CreationDate.ToString("yyyy-MM-dd_HH-mm-ss") & ".sqlite"
            File.Copy(MostRecentFile.FullName, NewFilePath)
            File.SetCreationTime(NewFilePath, CreationDate)
            File.SetLastAccessTime(NewFilePath, CreationDate)
            File.SetLastWriteTime(NewFilePath, CreationDate)
         Next
         File.Delete(MostRecentFile.FullName)
      Catch ex As Exception
         EgtOutLog(ex.Message)
      End Try
   End Sub

   Private Function BackUpDbCallBackFunction(source As SQLiteConnection, sourceName As String, destination As SQLiteConnection, destinationName As String, pages As Integer, remainingPages As Integer, totalPages As Integer, retry As Boolean) As Boolean
      LibMap.refStatusBarVM.SetLoadingProgress_Visibility(True)
      LibMap.refStatusBarVM.SetLoadingProgress(100% * (totalPages - remainingPages) / totalPages)
      Return True
   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\BL1018 - 003.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\001.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\002.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\005.jpg",
                                       "c:\EgtData\OmagPHOTO\Data\006.jpg"}
      Dim Text As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      For i = 0 To nRow
         Dim R As New Random()
         Dim Name As String = "QqQ_Lastra" & i
         Dim ImagePath As String = ImagePathList(R.Next(0, ImagePathList.Length))
         Dim SlabState As String = PhotoMap.refOptionPanelVM.StateList(R.Next(0, PhotoMap.refOptionPanelVM.StateList.Count)).Id.ToString
         Dim Material As String = PhotoMap.refOptionPanelVM.MaterialList(R.Next(0, PhotoMap.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 & ", " &
                                                   Slab.DB_ISSELECTED & ")" &
                                  " VALUES ('" & Name & "', " &
                                          "'" & ImagePath & "', " &
                                                SlabState & ", " &
                                          "'" & "C:\EgtData\OmagOFFICE\Progetto" & i & "', " &
                                          "'" & Material & "', " &
                                                SlabThickness.Replace(","c, "."c) & ", " &
                                          "'" & WarehousePosition & "', " &
                                          "Date('" & (String.Format("{0:yyyy-MM-dd}", AddedDate)) & "'), " &
                                          "0" & ")"

         ManageDb.ExecuteQuery(Query)
      Next
   End Sub

   Public Function FindAllMaterialInDB() As List(Of String)
      Dim LocalMaterialLIst As New List(Of String)
      ManageDb.DbConnection.Open()
      Try
         Dim SqlCommand As String = "SELECT DISTINCT " & Slab.DB_MATERIAL & " FROM " & Slab.DB_SLABS & " ORDER BY " & Slab.DB_MATERIAL & " ASC"
         Dim Reader As SQLiteDataReader = ExecuteReaderQuery(SqlCommand)
         While Reader.Read()
            LocalMaterialLIst.Add(Reader(Slab.DB_MATERIAL))
         End While
      Catch ex As Exception
      End Try
      m_DbConnection.Close()
      Return LocalMaterialLIst
   End Function

#End Region    ' METHODS

End Module