OmagPHOTO/Utility/ManageDb.vb

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

Module ManageDb

#Region "FIELDS & PROPERTIES"

   Public Const DB_FILENAME As String = "OmagPHOTODb.sqlite"

   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(Map.refMainWindowVM.MainWindowM.sPhotoDir & "\" & DB_FILENAME)
   End Sub

   Friend Function ConnectToDb() As Boolean
      Dim DbPath As String = Map.refMainWindowVM.MainWindowM.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

   Friend 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 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()
         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 ManageBackUp() As Boolean
      ClearBackUps()
      Dim BackUpDbPath As String = Map.refMainWindowVM.MainWindowM.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
      Map.refStatusBarVM.SetLoadingProgress(100)
      Map.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(Map.refMainWindowVM.MainWindowM.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(Map.refMainWindowVM.MainWindowM.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 = Map.refMainWindowVM.MainWindowM.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
      Map.refStatusBarVM.SetLoadingProgress_Visibility(True)
      Map.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\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