Skip to content
TheChatArchiveThe Chat Archive
o1-preview-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Imports System.IO Imports System.Globalization Imports System.Collections.Generic…

  1. User

    Imports System.IO Imports System.Globalization Imports System.Collections.Generic Imports System.ComponentModel Imports System.Drawing Imports System.Windows.Forms Imports System.Linq ' Added for LINQ usage Imports Microsoft.VisualBasic.FileIO ' For robust CSV parsing

    Public Class frmMain

    #Region "Variables and Constants"

    ' Column names constants
    Private Const ID_COLUMN_NAME As String = "idColumn"
    Private Const TOTALS_FLAG_COLUMN_NAME As String = "totalsFlagColumn"
    
    ' Column indices
    Private idColumnIndex As Integer
    Private totalsFlagColumnIndex As Integer
    Private dateColumnIndex As Integer
    
    ' Variables
    Private clickedRowIndex As Integer = -1
    
    ' Undo and Redo stacks
    Private undoStack As New Stack(Of DataGridViewAction)()
    Private redoStack As New Stack(Of DataGridViewAction)()
    
    ' List to preserve original data for filtering and sorting
    Private originalRows As New List(Of DataRowModel)()
    
    ' Unique ID counter
    Private nextId As Integer = 1
    
    ' Variables to store the last sorted column and sort direction
    Private lastSortColumn As DataGridViewColumn = Nothing
    Private lastSortDirection As ListSortDirection = ListSortDirection.Ascending
    
    ' Variables to store original and grayed images
    Private undoImageOriginal As Image
    Private redoImageOriginal As Image
    Private undoImageGrayed As Image
    Private redoImageGrayed As Image
    
    ' Tooltip for displaying person statistics
    Private toolTip1 As New ToolTip()
    

    #End Region

    #Region "Form Initialization"

    ' Form constructor
    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
    End Sub
    
    ' Event handler for form load
    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Initialize components
        InitializeContextMenu()
        InitializeDataGridView()
        InitializePictureBoxEventHandlers()
    
        ' Store original images and create grayed images
        undoImageOriginal = btnUndo.Image
        redoImageOriginal = btnRedo.Image
    
        ' Create grayed images
        undoImageGrayed = MakeGrayscale(undoImageOriginal)
        redoImageGrayed = MakeGrayscale(redoImageOriginal)
    
        ' Set DateTimePicker properties
        DateTimePicker01.MaxDate = DateTime.Now.AddDays(1)
        DateTimePicker01.Value = DateTime.Now
    
        ' Load data and display it
        LoadData()
        DisplayAllData()
    
        ' Update Undo/Redo menu items
        UpdateUndoRedoMenuItems()
    
        ' Handle CellEndEdit event
        AddHandler DataGridView1.CellEndEdit, AddressOf DataGridView1_CellEndEdit
        ' Handle DateTimePicker ValueChanged event
        AddHandler DateTimePicker01.ValueChanged, AddressOf DateTimePicker01_ValueChanged
    End Sub
    

    #End Region

    #Region "DataGridView Initialization"

    ' Method to initialize DataGridView properties and events
    Private Sub InitializeDataGridView()
        ' Add columns if not added via designer
        SetupDataGridViewColumns()
    
        ' Set AutoSizeColumnsMode to Fill
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    
        ' Set the FillWeight to control the width of each column
        SetupColumnFillWeights()
    
        ' Make Total Time column read-only
        DataGridView1.Columns("totalTimeColumn").ReadOnly = True
    
        ' Disable automatic addition of new rows
        DataGridView1.AllowUserToAddRows = False
    
        ' Enable sorting for all columns
        For Each column As DataGridViewColumn In DataGridView1.Columns
            column.SortMode = DataGridViewColumnSortMode.Automatic
        Next
    
        ' Set ShowCellToolTips to True
        DataGridView1.ShowCellToolTips = True
    
        ' Handle DataGridView events
        AddHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        AddHandler DataGridView1.Sorted, AddressOf DataGridView1_Sorted
        AddHandler DataGridView1.UserDeletingRow, AddressOf DataGridView1_UserDeletingRow
        AddHandler DataGridView1.SelectionChanged, AddressOf DataGridView1_SelectionChanged
        AddHandler DataGridView1.CellBeginEdit, AddressOf DataGridView1_CellBeginEdit
        AddHandler DataGridView1.SortCompare, AddressOf DataGridView1_SortCompare
        AddHandler DataGridView1.ColumnHeaderMouseClick, AddressOf DataGridView1_ColumnHeaderMouseClick
        AddHandler DataGridView1.MouseDown, AddressOf DataGridView1_MouseDown
        AddHandler DataGridView1.CellMouseEnter, AddressOf DataGridView1_CellMouseEnter
        AddHandler DataGridView1.CellMouseLeave, AddressOf DataGridView1_CellMouseLeave
    End Sub
    
    ' Setup columns
    Private Sub SetupDataGridViewColumns()
        ' Add the ID column if not already added
        If Not DataGridView1.Columns.Contains(ID_COLUMN_NAME) Then
            Dim idColumn As New DataGridViewTextBoxColumn() With {
                .Name = ID_COLUMN_NAME,
                .HeaderText = "ID",
                .Visible = False
            }
            DataGridView1.Columns.Insert(0, idColumn)
        End If
    
        ' Add the totalsFlagColumn if not already added
        If Not DataGridView1.Columns.Contains(TOTALS_FLAG_COLUMN_NAME) Then
            Dim totalsFlagColumn As New DataGridViewTextBoxColumn() With {
                .Name = TOTALS_FLAG_COLUMN_NAME,
                .HeaderText = "IsTotalsRow",
                .Visible = False
            }
            DataGridView1.Columns.Insert(1, totalsFlagColumn)
        End If
    
        ' Get column indices
        idColumnIndex = DataGridView1.Columns(ID_COLUMN_NAME).Index
        totalsFlagColumnIndex = DataGridView1.Columns(TOTALS_FLAG_COLUMN_NAME).Index
        dateColumnIndex = DataGridView1.Columns("dateColumn").Index
    End Sub
    
    ' Setup column fill weights
    Private Sub SetupColumnFillWeights()
        DataGridView1.Columns("nameColumn").FillWeight = 10
        DataGridView1.Columns("typeColumn").FillWeight = 10
        DataGridView1.Columns("dateColumn").FillWeight = 10
        DataGridView1.Columns("startTimeColumn").FillWeight = 10
        DataGridView1.Columns("finishTimeColumn").FillWeight = 10
        DataGridView1.Columns("totalTimeColumn").FillWeight = 10
        DataGridView1.Columns("amountColumn").FillWeight = 10
        DataGridView1.Columns("commentColumn").FillWeight = 30 ' Comment section should be the longest
    End Sub
    

    #End Region

    #Region "Context Menu Initialization"

    ' Assign the context menu to the DataGridView
    Private Sub InitializeContextMenu()
        ' Assign context menu to DataGridView
        DataGridView1.ContextMenuStrip = contextMenuMain
    
        ' Initially disable Undo and Redo menu items
        menuItemUndo.Enabled = False
        menuItemRedo.Enabled = False
    
        ' Initially disable Undo and Redo buttons
        btnUndo.Enabled = False
        btnRedo.Enabled = False
    End Sub
    

    #End Region

    #Region "PictureBox Event Handlers"

    ' Method to initialize the PictureBox event handlers
    Private Sub InitializePictureBoxEventHandlers()
        ' Attach event handlers to each PictureBox
        AddHandler btnDelete.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnDelete.MouseLeave, AddressOf PictureBox_MouseLeave
    
        AddHandler btnUndo.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnUndo.MouseLeave, AddressOf PictureBox_MouseLeave
    
        AddHandler btnRedo.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnRedo.MouseLeave, AddressOf PictureBox_MouseLeave
    
        AddHandler btnAdd.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnAdd.MouseLeave, AddressOf PictureBox_MouseLeave
    
        AddHandler btnDuplicate.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnDuplicate.MouseLeave, AddressOf PictureBox_MouseLeave
    
        AddHandler btnFB.MouseEnter, AddressOf PictureBox_MouseEnter
        AddHandler btnFB.MouseLeave, AddressOf PictureBox_MouseLeave
    End Sub
    
    ' Event handler for when the mouse enters the PictureBox
    Private Sub PictureBox_MouseEnter(sender As Object, e As EventArgs)
        Dim pictureBox As PictureBox = DirectCast(sender, PictureBox)
        pictureBox.Padding = New Padding(3) ' Increase padding to show hover effect
        pictureBox.BackColor = Color.LightGray ' Visual feedback
    End Sub
    
    ' Event handler for when the mouse leaves the PictureBox
    Private Sub PictureBox_MouseLeave(sender As Object, e As EventArgs)
        Dim pictureBox As PictureBox = DirectCast(sender, PictureBox)
        pictureBox.Padding = New Padding(0) ' Reset padding to default
        pictureBox.BackColor = Color.Transparent ' Reset visual feedback
    End Sub
    

    #End Region

    #Region "Image Processing"

    ' Function to convert an image to grayscale
    Private Function MakeGrayscale(original As Image) As Image
        Dim newBitmap As New Bitmap(original.Width, original.Height)
        Using g As Graphics = Graphics.FromImage(newBitmap)
            ' Create the grayscale ColorMatrix
            Dim colorMatrix As New Imaging.ColorMatrix(
                New Single()() {
                    New Single() {0.3, 0.3, 0.3, 0, 0},
                    New Single() {0.59, 0.59, 0.59, 0, 0},
                    New Single() {0.11, 0.11, 0.11, 0, 0},
                    New Single() {0, 0, 0, 1, 0},
                    New Single() {0, 0, 0, 0, 1}
                })
            ' Create image attributes and set the color matrix
            Using attributes As New Imaging.ImageAttributes()
                attributes.SetColorMatrix(colorMatrix)
                ' Draw the original image using the grayscale matrix
                g.DrawImage(original, New Rectangle(0, 0, original.Width, original.Height),
                    0, 0, original.Width, original.Height, GraphicsUnit.Pixel, attributes)
            End Using
        End Using
        Return newBitmap
    End Function
    

    #End Region

    #Region "Event Handlers"

    ' DataGridView Mouse Down event to get the row index on right-click
    Private Sub DataGridView1_MouseDown(sender As Object, e As MouseEventArgs)
        If e.Button = MouseButtons.Right Then
            Dim hitTestInfo As DataGridView.HitTestInfo = DataGridView1.HitTest(e.X, e.Y)
            If hitTestInfo.Type = DataGridViewHitTestType.Cell Then
                clickedRowIndex = hitTestInfo.RowIndex
                DataGridView1.ClearSelection()
                DataGridView1.Rows(clickedRowIndex).Selected = True
                DataGridView1.CurrentCell = DataGridView1.Rows(clickedRowIndex).Cells(hitTestInfo.ColumnIndex)
            End If
        End If
    End Sub
    
    ' Store the original value before editing
    Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs)
        If e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
            Dim cell = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex)
            cell.Tag = cell.Value ' Store the original value in the Tag property
        End If
    End Sub
    
    ' Event handler for CellEndEdit to update Total Time
    Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs)
        ' Ensure the edited cell is valid
        If e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
            Dim cell = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex)
            Dim oldValue = cell.Tag ' Retrieve the old value stored in Tag
            Dim newValue = cell.Value
    
            Dim currentRow As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
            If IsTotalsRow(currentRow) Then
                ' Do not process totals row
                Return
            End If
    
            ' Record the cell edit action
            Dim action As New DataGridViewAction With {
                .Type = DataGridViewAction.ActionType.CellEdit,
                .RowIndex = e.RowIndex,
                .ColumnIndex = e.ColumnIndex,
                .OldValue = oldValue,
                .NewValue = newValue,
                .RowId = currentRow.Cells(idColumnIndex).Value.ToString()
            }
            undoStack.Push(action)
            redoStack.Clear() ' Clear redo stack when a new action is performed
            UpdateUndoRedoMenuItems()
    
            ' Update Total Time if Start Time or Finish Time is edited
            If e.ColumnIndex = DataGridView1.Columns("startTimeColumn").Index OrElse e.ColumnIndex = DataGridView1.Columns("finishTimeColumn").Index Then
                UpdateTotalTimeForRow(currentRow)
            End If
    
            ' Update the corresponding data in originalRows
            UpdateOriginalRowFromDataGridViewRow(currentRow)
    
            ' Update totals and save data
            UpdateTotals()
            SaveData()
        End If
    End Sub
    
    ' Event handler for DataGridView CellValueChanged (no implementation needed)
    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs)
        ' No implementation needed since editing is handled in CellEndEdit
    End Sub
    
    ' Event handler for UserDeletingRow to prevent deletion of the totals row
    Private Sub DataGridView1_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs)
        If IsTotalsRow(e.Row) Then
            MessageBox.Show("The totals row cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            e.Cancel = True ' Cancel the deletion
        Else
            ' Record the delete action for Undo
            If e.Row.Index >= 0 AndAlso e.Row.Index < DataGridView1.Rows.Count Then
                Dim rowData As DataGridViewRow = e.Row
                Dim dataRowModel As DataRowModel = ConvertDataGridViewRowToDataRowModel(rowData)
    
                Dim action As New DataGridViewAction With {
                    .Type = DataGridViewAction.ActionType.RowDeleted,
                    .RowId = dataRowModel.Id.ToString(),
                    .DataRow = dataRowModel
                }
                undoStack.Push(action)
                redoStack.Clear() ' Clear redo stack
                UpdateUndoRedoMenuItems()
    
                ' Remove from originalRows
                originalRows.RemoveAll(Function(r) r.Id = dataRowModel.Id)
            End If
        End If
    End Sub
    
    ' Event handler for SelectionChanged to deselect the totals row
    Private Sub DataGridView1_SelectionChanged(sender As Object, e As EventArgs)
        If DataGridView1.CurrentRow IsNot Nothing AndAlso IsTotalsRow(DataGridView1.CurrentRow) Then
            ' Deselect the totals row
            DataGridView1.CurrentCell = Nothing
        End If
    End Sub
    
    ' Event handler for Sorted event
    Private Sub DataGridView1_Sorted(sender As Object, e As EventArgs)
        ' Move totals row to the bottom
        Dim totalsRow As DataGridViewRow = GetTotalsRow()
        If totalsRow IsNot Nothing Then
            DataGridView1.Rows.Remove(totalsRow)
            DataGridView1.Rows.Add(totalsRow)
        End If
    End Sub
    
    ' Custom sorting logic for the DataGridView
    Private Sub DataGridView1_SortCompare(sender As Object, e As DataGridViewSortCompareEventArgs)
        Dim dgv As DataGridView = CType(sender, DataGridView)
        Dim row1 As DataGridViewRow = dgv.Rows(e.RowIndex1)
        Dim row2 As DataGridViewRow = dgv.Rows(e.RowIndex2)
    
        ' Check if one of the rows is the totals row
        Dim isRow1Totals As Boolean = IsTotalsRow(row1)
        Dim isRow2Totals As Boolean = IsTotalsRow(row2)
    
        If isRow1Totals AndAlso Not isRow2Totals Then
            ' Totals row should always come after data rows
            e.SortResult = 1
            e.Handled = True
            Return
        ElseIf Not isRow1Totals AndAlso isRow2Totals Then
            e.SortResult = -1
            e.Handled = True
            Return
        ElseIf isRow1Totals AndAlso isRow2Totals Then
            ' Both rows are totals rows; treat as equal
            e.SortResult = 0
            e.Handled = True
            Return
        End If
    
        ' Now proceed with custom comparison for specific columns
        If e.Column.Name = "dateColumn" Then
            Dim date1, date2 As DateTime
            If DateTime.TryParse(e.CellValue1?.ToString(), date1) AndAlso DateTime.TryParse(e.CellValue2?.ToString(), date2) Then
                e.SortResult = date1.CompareTo(date2)
            Else
                e.SortResult = String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString())
            End If
            e.Handled = True
        ElseIf e.Column.Name = "startTimeColumn" OrElse e.Column.Name = "finishTimeColumn" Then
            Dim time1, time2 As TimeSpan
            If TimeSpan.TryParse(e.CellValue1?.ToString(), time1) AndAlso TimeSpan.TryParse(e.CellValue2?.ToString(), time2) Then
                e.SortResult = time1.CompareTo(time2)
            Else
                e.SortResult = String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString())
            End If
            e.Handled = True
        ElseIf e.Column.Name = "amountColumn" Then
            Dim amount1, amount2 As Decimal
            Dim amountStr1 As String = e.CellValue1?.ToString().Replace("£", "")
            Dim amountStr2 As String = e.CellValue2?.ToString().Replace("£", "")
            If Decimal.TryParse(amountStr1, amount1) AndAlso Decimal.TryParse(amountStr2, amount2) Then
                e.SortResult = amount1.CompareTo(amount2)
            Else
                e.SortResult = String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString())
            End If
            e.Handled = True
        Else
            ' For other columns, use default sorting
            e.Handled = False
        End If
    End Sub
    
    ' Event handler for ColumnHeaderMouseClick to track sort column and direction
    Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs)
        Dim clickedColumn As DataGridViewColumn = DataGridView1.Columns(e.ColumnIndex)
    
        ' Determine the new sort direction
        If lastSortColumn Is clickedColumn Then
            ' Toggle sort direction if the same column is clicked
            If lastSortDirection = ListSortDirection.Ascending Then
                lastSortDirection = ListSortDirection.Descending
            Else
                lastSortDirection = ListSortDirection.Ascending
            End If
        Else
            ' Default to ascending when a new column is clicked
            lastSortDirection = ListSortDirection.Ascending
        End If
    
        lastSortColumn = clickedColumn
    
        ' Apply the sort
        DataGridView1.Sort(clickedColumn, lastSortDirection)
    End Sub
    
    ' Event handler for mouse entering a cell (tooltip)
    Private Sub DataGridView1_CellMouseEnter(sender As Object, e As DataGridViewCellEventArgs)
        If e.RowIndex >= 0 AndAlso e.ColumnIndex = DataGridView1.Columns("nameColumn").Index Then
            Dim name As String = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString()
    
            ' Skip if name is empty or whitespace
            If String.IsNullOrWhiteSpace(name) Then
                Return
            End If
    
            ' Calculate statistics using LINQ
            Dim personData = originalRows.Where(Function(r) r.Name = name)
    
            Dim jobCount As Integer = personData.Count()
            Dim totalDuration As TimeSpan = TimeSpan.FromTicks(personData.Sum(Function(r) r.TotalTime.Ticks))
            Dim totalAmount As Decimal = personData.Sum(Function(r) r.Amount)
    
            ' Build tooltip text
            Dim totalHours As Integer = CInt(Math.Floor(totalDuration.TotalHours))
            Dim totalMinutes As Integer = totalDuration.Minutes
            Dim totalTimeString As String = $"{totalHours}:{totalMinutes:D2}"
    
            ' Build tooltip string
            Dim tooltipText As String = $"{name}" & vbCrLf &
                                        $"Jobs: {jobCount}" & vbCrLf &
                                        $"Hours: {totalTimeString}" & vbCrLf &
                                        $"Total Pay: £{totalAmount.ToString("0.00")}"
    
            ' Show the tooltip near the mouse cursor
            Dim cursorPosition As Point = Control.MousePosition
            Dim relativeCursorPosition As Point = Me.PointToClient(cursorPosition)
            toolTip1.Show(tooltipText, Me, relativeCursorPosition.X + 10, relativeCursorPosition.Y + 10, 3000) ' Offset by 10 pixels
        End If
    End Sub
    
    ' Event handler for mouse leaving a cell
    Private Sub DataGridView1_CellMouseLeave(sender As Object, e As DataGridViewCellEventArgs)
        ' Hide the tooltip when the mouse leaves any cell
        toolTip1.Hide(Me)
    End Sub
    
    ' Event handler for DateTimePicker value changed
    Private Sub DateTimePicker01_ValueChanged(sender As Object, e As EventArgs)
        ' Extract the selected date from DateTimePicker01
        Dim selectedDate As DateTime = DateTimePicker01.Value
        Dim selectedMonth As Integer = selectedDate.Month
        Dim selectedYear As Integer = selectedDate.Year
    
        ' Filter the DataGridView based on the selected month and year
        FilterDataGrid(selectedMonth, selectedYear)
    End Sub
    
    ' Event handler for resetting the DateTimePicker to current date
    Private Sub CTMReset_Click(sender As Object, e As EventArgs) Handles CTMReset.Click
        DateTimePicker01.Value = DateTime.Now
    End Sub
    
    ' Event handler for the btnGo button click to reload data
    Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
        ' Temporarily remove the DateTimePicker ValueChanged event handler
        RemoveHandler DateTimePicker01.ValueChanged, AddressOf DateTimePicker01_ValueChanged
    
        ' Load data
        LoadData()
        ' Display all data
        DisplayAllData()
    
        ' Reset DateTimePicker to current date
        DateTimePicker01.Value = DateTime.Now
    
        ' Re-attach the DateTimePicker ValueChanged event handler
        AddHandler DateTimePicker01.ValueChanged, AddressOf DateTimePicker01_ValueChanged
    End Sub
    

    #End Region

    #Region "Context Menu Event Handlers and Button Clicks"

    ' Event handler for adding a row
    Private Sub menuItemAddRow_Click(sender As Object, e As EventArgs) Handles menuItemAddRow.Click, btnAdd.Click
        ' Capture the DataRowModel returned by AddRow
        Dim dataRow As DataRowModel = AddRow()
    
        ' Record the add row action
        Dim action As New DataGridViewAction With {
            .Type = DataGridViewAction.ActionType.RowAdded,
            .RowId = dataRow.Id.ToString(),
            .DataRow = dataRow
        }
        undoStack.Push(action)
        redoStack.Clear() ' Clear redo stack when a new action is performed
        UpdateUndoRedoMenuItems()
    End Sub
    
    ' Method to add a row to the DataGridView
    Private Function AddRow() As DataRowModel
        ' Create a new DataRowModel with default values
        Dim dataRow As New DataRowModel With {
            .Id = nextId,
            .Name = "",
            .Type = "",
            .DateValue = DateTime.Now,
            .StartTime = TimeSpan.FromHours(DateTime.Now.Hour).Add(TimeSpan.FromMinutes(DateTime.Now.Minute)),
            .FinishTime = TimeSpan.FromHours(DateTime.Now.AddHours(1).Hour).Add(TimeSpan.FromMinutes(DateTime.Now.Minute)),
            .TotalTime = TimeSpan.FromHours(1),
            .Amount = 0D,
            .Comment = ""
        }
        nextId += 1
    
        ' Add the new dataRow to originalRows
        originalRows.Add(dataRow)
    
        ' Refresh the DataGridView display
        DisplayAllData()
    
        ' Update totals
        UpdateTotals()
        SaveData()
    
        ' Return the dataRow for the action
        Return dataRow
    End Function
    
    ' Event handler for deleting a row
    Private Sub menuItemDeleteRow_Click(sender As Object, e As EventArgs) Handles menuItemDeleteRow.Click, btnDelete.Click
        Dim rowIndexToDelete As Integer = GetSelectedRowIndex(sender)
        DeleteRow(rowIndexToDelete)
    End Sub
    
    ' Method to delete a row
    Private Sub DeleteRow(rowIndexToDelete As Integer)
        If rowIndexToDelete >= 0 AndAlso rowIndexToDelete < DataGridView1.Rows.Count Then
            Dim selectedRow As DataGridViewRow = DataGridView1.Rows(rowIndexToDelete)
            If IsTotalsRow(selectedRow) Then
                MessageBox.Show("The totals row cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End If
    
            Dim dataRowModel As DataRowModel = ConvertDataGridViewRowToDataRowModel(selectedRow)
    
            ' Record the delete row action
            Dim action As New DataGridViewAction With {
                .Type = DataGridViewAction.ActionType.RowDeleted,
                .RowId = dataRowModel.Id.ToString(),
                .DataRow = dataRowModel
            }
            undoStack.Push(action)
            redoStack.Clear() ' Clear redo stack when a new action is performed
            UpdateUndoRedoMenuItems()
    
            ' Remove from originalRows
            originalRows.RemoveAll(Function(r) r.Id = dataRowModel.Id)
    
            ' Refresh the DataGridView display
            DisplayAllData()
    
            UpdateTotals() ' Update totals after deletion
            SaveData()
        End If
    End Sub
    
    ' Event handler for duplicating a row
    Private Sub menuItemDuplicateRow_Click(sender As Object, e As EventArgs) Handles menuItemDuplicateRow.Click, btnDuplicate.Click
        Dim rowIndexToDuplicate = GetSelectedRowIndex(sender)
        DuplicateRow(rowIndexToDuplicate)
    End Sub
    
    ' Method to duplicate a row
    Private Sub DuplicateRow(rowIndexToDuplicate As Integer)
        If rowIndexToDuplicate >= 0 AndAlso rowIndexToDuplicate < DataGridView1.Rows.Count Then
            Dim selectedRow As DataGridViewRow = DataGridView1.Rows(rowIndexToDuplicate)
            If IsTotalsRow(selectedRow) Then
                MessageBox.Show("The totals row cannot be duplicated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End If
    
            ' Duplicate the selected row
            Dim originalDataRow As DataRowModel = ConvertDataGridViewRowToDataRowModel(selectedRow)
    
            ' Create a new DataRowModel with a new ID
            Dim duplicatedDataRow As New DataRowModel With {
                .Id = nextId,
                .Name = originalDataRow.Name,
                .Type = originalDataRow.Type,
                .DateValue = originalDataRow.DateValue,
                .StartTime = originalDataRow.StartTime,
                .FinishTime = originalDataRow.FinishTime,
                .TotalTime = originalDataRow.TotalTime,
                .Amount = originalDataRow.Amount,
                .Comment = originalDataRow.Comment
            }
            nextId += 1
    
            ' Add the duplicatedDataRow to originalRows
            originalRows.Add(duplicatedDataRow)
    
            ' Refresh the DataGridView display
            DisplayAllData()
    
            UpdateTotals() ' Update totals after duplication
    
            ' Record the duplicate row action
            Dim action As New DataGridViewAction With {
                .Type = DataGridViewAction.ActionType.RowDuplicated,
                .RowId = duplicatedDataRow.Id.ToString(),
                .DataRow = duplicatedDataRow
            }
            undoStack.Push(action)
            redoStack.Clear() ' Clear redo stack when a new action is performed
            UpdateUndoRedoMenuItems()
    
            SaveData()
        End If
    End Sub
    
    ' Event handler for the Undo menu item
    Private Sub menuItemUndo_Click(sender As Object, e As EventArgs) Handles menuItemUndo.Click, btnUndo.Click
        If undoStack.Count > 0 Then
            Dim action = undoStack.Pop()
            PerformUndo(action)
            redoStack.Push(action)
            UpdateUndoRedoMenuItems()
        End If
    End Sub
    
    ' Event handler for the Redo menu item
    Private Sub menuItemRedo_Click(sender As Object, e As EventArgs) Handles menuItemRedo.Click, btnRedo.Click
        If redoStack.Count > 0 Then
            Dim action = redoStack.Pop()
            PerformRedo(action)
            undoStack.Push(action)
            UpdateUndoRedoMenuItems()
        End If
    End Sub
    

    #End Region

    #Region "Data Management Methods"

    ' Method to load data using DataRowModel
    Private Sub LoadData()
        ' Clear existing rows
        originalRows.Clear()
        nextId = 1 ' Reset ID counter
    
        ' Check if the file exists
        If File.Exists("data.csv") Then
            Using parser As New TextFieldParser("data.csv")
                parser.TextFieldType = FieldType.Delimited
                parser.SetDelimiters(",")
    
                ' Read header line
                If Not parser.EndOfData Then
                    parser.ReadFields()
                End If
    
                ' Read rows
                While Not parser.EndOfData
                    Dim values As String() = parser.ReadFields()
                    If values.Length < 8 Then
                        Continue While ' Skip invalid rows
                    End If
    
                    Try
                        ' Parse the date and time fields safely
                        Dim dateValue As DateTime
                        Dim dateParsed As Boolean = DateTime.TryParse(values(2), dateValue)
    
                        Dim startTime As TimeSpan
                        Dim startTimeParsed As Boolean = TimeSpan.TryParse(values(3), startTime)
    
                        Dim finishTime As TimeSpan
                        Dim finishTimeParsed As Boolean = TimeSpan.TryParse(values(4), finishTime)
    
                        Dim totalTime As TimeSpan
                        Dim totalTimeParsed As Boolean = TimeSpan.TryParse(values(5), totalTime)
    
                        ' Parse amount
                        Dim amountStr As String = values(6).Replace("£", "")
                        Dim amount As Decimal
                        Dim amountParsed As Boolean = Decimal.TryParse(amountStr, amount)
    
                        ' Create a new DataRowModel
                        Dim dataRow As New DataRowModel With {
                        .Id = nextId,
                        .Name = values(0),
                        .Type = values(1),
                        .DateValue = If(dateParsed, dateValue, DateTime.MinValue),
                        .StartTime = If(startTimeParsed, startTime, TimeSpan.Zero),
                        .FinishTime = If(finishTimeParsed, finishTime, TimeSpan.Zero),
                        .TotalTime = If(totalTimeParsed, totalTime, TimeSpan.Zero),
                        .Amount = If(amountParsed, amount, 0D),
                        .Comment = values(7)
                    }
                        nextId += 1
    
                        ' Add to originalRows
                        originalRows.Add(dataRow)
                    Catch ex As Exception
                        ' Handle parsing errors, log the error, and continue loading
                        MessageBox.Show($"Error parsing data row: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try
                End While
            End Using
        End If
    End Sub
    
    ' Method to save data
    Private Sub SaveData()
        Try
            ' Save data to a CSV file
            Using writer As New StreamWriter("data.csv")
                ' Write header
                writer.WriteLine("Name,Type,Date,Start Time,Finish Time,Total Time,Amount,Comment")
    
                ' Write rows from originalRows
                For Each dataRow As DataRowModel In originalRows
                    Dim fields As String() = {
                        dataRow.Name,
                        dataRow.Type,
                        dataRow.DateValue.ToString("d"),
                        dataRow.StartTime.ToString("hh\:mm"),
                        dataRow.FinishTime.ToString("hh\:mm"),
                        dataRow.TotalTime.ToString("hh\:mm"),
                        "£" & dataRow.Amount.ToString("0.00"),
                        dataRow.Comment
                    }
    
                    ' Escape commas by enclosing fields in quotes if necessary
                    For i As Integer = 0 To fields.Length - 1
                        If fields(i).Contains(",") Then
                            fields(i) = $"""{fields(i)}"""
                        End If
                    Next
    
                    writer.WriteLine(String.Join(",", fields))
                Next
            End Using
        Catch ex As IOException
            MessageBox.Show("An error occurred while saving data: " & ex.Message)
        End Try
    End Sub
    
    ' Method to display all data in the DataGridView
    Private Sub DisplayAllData()
        ' Temporarily remove event handlers to prevent unwanted triggers during loading
        RemoveHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        RemoveHandler DataGridView1.CellEndEdit, AddressOf DataGridView1_CellEndEdit
    
        DataGridView1.Rows.Clear()
    
        ' Add the data from originalRows to the DataGridView
        For Each dataRow As DataRowModel In originalRows
            Dim rowValues As Object() = ConvertDataRowModelToObjectArray(dataRow)
            DataGridView1.Rows.Add(rowValues)
        Next
    
        ' Add totals row after loading
        AddTotalsRow()
        UpdateTotals()
    
        ' Re-add event handlers after loading is complete
        AddHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        AddHandler DataGridView1.CellEndEdit, AddressOf DataGridView1_CellEndEdit
    
        ' Reapply the last sort
        ReapplyLastSort()
    End Sub
    
    ' Method to reapply the last sort
    Private Sub ReapplyLastSort()
        If lastSortColumn IsNot Nothing Then
            ' Apply the sort
            DataGridView1.Sort(lastSortColumn, lastSortDirection)
        End If
    End Sub
    
    ' Method to update totals in the totals row
    Private Sub UpdateTotals()
        Dim totalDuration As TimeSpan = TimeSpan.Zero
        Dim totalAmount As Decimal = 0D
    
        For Each dataRow As DataRowModel In originalRows
            totalDuration += dataRow.TotalTime
            totalAmount += dataRow.Amount
        Next
    
        ' Update totals row
        Dim totalsRow As DataGridViewRow = GetTotalsRow()
        If totalsRow IsNot Nothing Then
            ' Display total hours and minutes
            totalsRow.Cells("totalTimeColumn").Value = $"{CInt(Math.Floor(totalDuration.TotalHours))}:{totalDuration.Minutes:D2}"
            totalsRow.Cells("amountColumn").Value = "£" & totalAmount.ToString("0.00")
        End If
    End Sub
    
    ' Method to add a totals row to the DataGridView
    Private Sub AddTotalsRow()
        ' Remove existing totals row if present
        Dim existingTotalsRow As DataGridViewRow = GetTotalsRow()
        If existingTotalsRow IsNot Nothing Then
            DataGridView1.Rows.Remove(existingTotalsRow)
        End If
    
        ' Initialize an array with the correct number of elements
        Dim totalColumns As Integer = DataGridView1.Columns.Count
        Dim totalsRowValues As Object() = New Object(totalColumns - 1) {}
    
        ' Assign values based on column names
        For i As Integer = 0 To totalColumns - 1
            Select Case DataGridView1.Columns(i).Name
                Case "nameColumn"
                    totalsRowValues(i) = "Total"
                Case "totalTimeColumn"
                    totalsRowValues(i) = "0:00"
                Case "amountColumn"
                    totalsRowValues(i) = "£0.00"
                Case TOTALS_FLAG_COLUMN_NAME
                    totalsRowValues(i) = "True"
                Case Else
                    totalsRowValues(i) = ""
            End Select
        Next
    
        DataGridView1.Rows.Add(totalsRowValues)
    
        Dim totalsRowView As DataGridViewRow = DataGridView1.Rows(DataGridView1.Rows.Count - 1)
    
        ' Set every cell in the totals row as read-only
        For Each cell As DataGridViewCell In totalsRowView.Cells
            cell.ReadOnly = True
        Next
    
        totalsRowView.DefaultCellStyle.BackColor = Color.LightGray
        totalsRowView.DefaultCellStyle.Font = New Font(DataGridView1.Font, FontStyle.Bold)
    
        ' Set the totals row flag in the hidden column
        totalsRowView.Cells(totalsFlagColumnIndex).Value = "True"
    End Sub
    
    ' Method to filter DataGridView rows based on selected month and year
    Private Sub FilterDataGrid(selectedMonth As Integer, selectedYear As Integer)
        ' Temporarily remove event handlers to prevent unwanted triggers during filtering
        RemoveHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        RemoveHandler DataGridView1.CellEndEdit, AddressOf DataGridView1_CellEndEdit
    
        DataGridView1.Rows.Clear()
    
        For Each dataRow As DataRowModel In originalRows
            ' Check if the row's date matches the selected month and year
            If dataRow.DateValue.Month = selectedMonth AndAlso dataRow.DateValue.Year = selectedYear Then
                Dim rowValues As Object() = ConvertDataRowModelToObjectArray(dataRow)
                DataGridView1.Rows.Add(rowValues)
            End If
        Next
    
        ' Add totals row after filtering
        AddTotalsRow()
        UpdateTotals()
    
        ' Re-add event handlers after filtering is complete
        AddHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        AddHandler DataGridView1.CellEndEdit, AddressOf DataGridView1_CellEndEdit
    
        ' Reapply the last sort
        ReapplyLastSort()
    End Sub
    
    ' Method to update Total Time for a row
    Private Sub UpdateTotalTimeForRow(currentRow As DataGridViewRow)
        ' Parse Start Time and Finish Time as TimeSpan
        Dim startTimeStr As String = currentRow.Cells("startTimeColumn").Value?.ToString()
        Dim finishTimeStr As String = currentRow.Cells("finishTimeColumn").Value?.ToString()
        Dim startTime As TimeSpan
        Dim finishTime As TimeSpan
    
        ' Try parsing Start Time and Finish Time cell values
        If TimeSpan.TryParse(startTimeStr, startTime) AndAlso TimeSpan.TryParse(finishTimeStr, finishTime) Then
            ' Calculate Total Time
            Dim totalTime As TimeSpan = finishTime - startTime
            If totalTime.TotalMinutes < 0 Then
                ' If totalTime is negative, add 24 hours (handles times past midnight)
                totalTime = totalTime.Add(TimeSpan.FromHours(24))
            End If
            ' Update Total Time cell value
            currentRow.Cells("totalTimeColumn").Value = $"{CInt(Math.Floor(totalTime.TotalHours))}:{totalTime.Minutes:D2}"
    
            ' Update the corresponding data in originalRows
            Dim rowId As Integer = Integer.Parse(currentRow.Cells(idColumnIndex).Value.ToString())
            Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = rowId)
            If dataRow IsNot Nothing Then
                dataRow.TotalTime = totalTime
            End If
        Else
            ' If parsing fails, clear the Total Time cell
            currentRow.Cells("totalTimeColumn").Value = ""
        End If
    End Sub
    

    #End Region

    #Region "Undo and Redo Functionality"

    ' Perform the Undo action
    Private Sub PerformUndo(action As DataGridViewAction)
        ' Ensure we are not affecting totals row
        If action.RowId Is Nothing OrElse action.RowId = "TotalsRowId" Then
            Return
        End If
    
        Select Case action.Type
            Case DataGridViewAction.ActionType.CellEdit
                ' Find the data row
                Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = Integer.Parse(action.RowId))
                If dataRow IsNot Nothing Then
                    SetPropertyValue(dataRow, action.ColumnIndex, action.OldValue)
                End If
    
            Case DataGridViewAction.ActionType.RowAdded
                ' Remove the row from originalRows
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))
    
            Case DataGridViewAction.ActionType.RowDeleted
                ' Re-add the data row to originalRows
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If
    
            Case DataGridViewAction.ActionType.RowDuplicated
                ' Remove the duplicated row from originalRows
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))
        End Select
    
        ' Refresh the DataGridView display
        DisplayAllData()
        SaveData()
        ' Update Undo and Redo buttons and menu items
        UpdateUndoRedoMenuItems()
    End Sub
    
    ' Perform the Redo action
    Private Sub PerformRedo(action As DataGridViewAction)
        ' Ensure we are not affecting totals row
        If action.RowId Is Nothing OrElse action.RowId = "TotalsRowId" Then
            Return
        End If
    
        Select Case action.Type
            Case DataGridViewAction.ActionType.CellEdit
                ' Find the data row
                Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = Integer.Parse(action.RowId))
                If dataRow IsNot Nothing Then
                    SetPropertyValue(dataRow, action.ColumnIndex, action.NewValue)
                End If
    
            Case DataGridViewAction.ActionType.RowAdded
                ' Re-add the data row to originalRows
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If
    
            Case DataGridViewAction.ActionType.RowDeleted
                ' Remove the data row from originalRows
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))
    
            Case DataGridViewAction.ActionType.RowDuplicated
                ' Re-add the duplicated row to originalRows
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If
        End Select
    
        ' Refresh the DataGridView display
        DisplayAllData()
        SaveData()
        ' Update Undo and Redo buttons and menu items
        UpdateUndoRedoMenuItems()
    End Sub
    
    ' Method to update Undo and Redo menu items and buttons
    Private Sub UpdateUndoRedoMenuItems()
        Dim hasData As Boolean = HasDataRows()
    
        Dim canUndo As Boolean = (undoStack.Count > 0) And hasData
        Dim canRedo As Boolean = (redoStack.Count > 0) And hasData
    
        menuItemUndo.Enabled = canUndo
        menuItemRedo.Enabled = canRedo
    
        btnUndo.Enabled = canUndo
        btnRedo.Enabled = canRedo
    
        ' Change images based on enabled state
        btnUndo.Image = If(canUndo, undoImageOriginal, undoImageGrayed)
        btnRedo.Image = If(canRedo, redoImageOriginal, redoImageGrayed)
    End Sub
    

    #End Region

    #Region "Helper Methods"

    ' Helper function to check if there are data rows in the DataGridView
    Private Function HasDataRows() As Boolean
        Return originalRows.Any()
    End Function
    
    ' Helper method to check if a row is the totals row
    Private Function IsTotalsRow(row As DataGridViewRow) As Boolean
        Return row.Cells(totalsFlagColumnIndex).Value IsNot Nothing AndAlso row.Cells(totalsFlagColumnIndex).Value.ToString() = "True"
    End Function
    
    ' Helper method to get the totals row
    Private Function GetTotalsRow() As DataGridViewRow
        For Each row As DataGridViewRow In DataGridView1.Rows
            If IsTotalsRow(row) Then
                Return row
            End If
        Next
        Return Nothing
    End Function
    
    ' Helper method to update a row in originalRows from a DataGridViewRow
    Private Sub UpdateOriginalRowFromDataGridViewRow(dgvRow As DataGridViewRow)
        Dim idValue As Integer = Integer.Parse(dgvRow.Cells(idColumnIndex).Value.ToString())
        Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = idValue)
        If dataRow IsNot Nothing Then
            ' Update properties
            dataRow.Name = dgvRow.Cells("nameColumn").Value?.ToString()
            dataRow.Type = dgvRow.Cells("typeColumn").Value?.ToString()
            DateTime.TryParse(dgvRow.Cells("dateColumn").Value?.ToString(), dataRow.DateValue)
            TimeSpan.TryParse(dgvRow.Cells("startTimeColumn").Value?.ToString(), dataRow.StartTime)
            TimeSpan.TryParse(dgvRow.Cells("finishTimeColumn").Value?.ToString(), dataRow.FinishTime)
            TimeSpan.TryParse(dgvRow.Cells("totalTimeColumn").Value?.ToString(), dataRow.TotalTime)
            Decimal.TryParse(dgvRow.Cells("amountColumn").Value?.ToString().Replace("£", ""), dataRow.Amount)
            dataRow.Comment = dgvRow.Cells("commentColumn").Value?.ToString()
        End If
    End Sub
    
    ' Helper method to get the selected row index
    Private Function GetSelectedRowIndex(sender As Object) As Integer
        Dim rowIndex As Integer = -1
    
        If TypeOf sender Is ToolStripMenuItem Then
            ' Invoked from context menu, use clickedRowIndex
            rowIndex = clickedRowIndex
        ElseIf TypeOf sender Is PictureBox OrElse TypeOf sender Is Button Then
            ' Invoked from button, use the currently selected row or cell
            If DataGridView1.SelectedRows.Count > 0 Then
                rowIndex = DataGridView1.SelectedRows(0).Index
            ElseIf DataGridView1.CurrentCell IsNot Nothing Then
                rowIndex = DataGridView1.CurrentCell.RowIndex
            Else
                MessageBox.Show("Please select a row.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
        End If
    
        Return rowIndex
    End Function
    
    ' Helper method to convert DataGridViewRow to DataRowModel
    Private Function ConvertDataGridViewRowToDataRowModel(row As DataGridViewRow) As DataRowModel
        Dim dataRow As New DataRowModel()
        dataRow.Id = Integer.Parse(row.Cells(idColumnIndex).Value.ToString())
        dataRow.Name = row.Cells("nameColumn").Value?.ToString()
        dataRow.Type = row.Cells("typeColumn").Value?.ToString()
        DateTime.TryParse(row.Cells("dateColumn").Value?.ToString(), dataRow.DateValue)
        TimeSpan.TryParse(row.Cells("startTimeColumn").Value?.ToString(), dataRow.StartTime)
        TimeSpan.TryParse(row.Cells("finishTimeColumn").Value?.ToString(), dataRow.FinishTime)
        TimeSpan.TryParse(row.Cells("totalTimeColumn").Value?.ToString(), dataRow.TotalTime)
        Decimal.TryParse(row.Cells("amountColumn").Value?.ToString().Replace("£", ""), dataRow.Amount)
        dataRow.Comment = row.Cells("commentColumn").Value?.ToString()
        Return dataRow
    End Function
    
    ' Helper method to convert DataRowModel to Object Array for DataGridViewRow
    Private Function ConvertDataRowModelToObjectArray(dataRow As DataRowModel) As Object()
        Dim totalColumns As Integer = DataGridView1.Columns.Count
        Dim rowValues As Object() = New Object(totalColumns - 1) {}
    
        ' Map columns by name
        For Each column As DataGridViewColumn In DataGridView1.Columns
            Select Case column.Name
                Case "nameColumn"
                    rowValues(column.Index) = dataRow.Name
                Case "typeColumn"
                    rowValues(column.Index) = dataRow.Type
                Case "dateColumn"
                    rowValues(column.Index) = dataRow.DateValue.ToString("d")
                Case "startTimeColumn"
                    rowValues(column.Index) = dataRow.StartTime.ToString("hh\:mm")
                Case "finishTimeColumn"
                    rowValues(column.Index) = dataRow.FinishTime.ToString("hh\:mm")
                Case "totalTimeColumn"
                    rowValues(column.Index) = $"{CInt(Math.Floor(dataRow.TotalTime.TotalHours))}:{dataRow.TotalTime.Minutes:D2}"
                Case "amountColumn"
                    rowValues(column.Index) = "£" & dataRow.Amount.ToString("0.00")
                Case "commentColumn"
                    rowValues(column.Index) = dataRow.Comment
                Case TOTALS_FLAG_COLUMN_NAME
                    rowValues(column.Index) = ""
                Case ID_COLUMN_NAME
                    rowValues(column.Index) = dataRow.Id.ToString()
                Case Else
                    rowValues(column.Index) = ""
            End Select
        Next
    
        Return rowValues
    End Function
    
    ' Helper method to set property value by column index
    Private Sub SetPropertyValue(dataRow As DataRowModel, columnIndex As Integer, value As Object)
        Dim columnName As String = DataGridView1.Columns(columnIndex).Name
        Select Case columnName
            Case "nameColumn"
                dataRow.Name = value?.ToString()
            Case "typeColumn"
                dataRow.Type = value?.ToString()
            Case "dateColumn"
                If DateTime.TryParse(value?.ToString(), dataRow.DateValue) Then
                    ' Parsed successfully
                End If
            Case "startTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.StartTime) Then
                    ' Parsed successfully
                End If
            Case "finishTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.FinishTime) Then
                    ' Parsed successfully
                End If
            Case "totalTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.TotalTime) Then
                    ' Parsed successfully
                End If
            Case "amountColumn"
                If Decimal.TryParse(value?.ToString().Replace("£", ""), dataRow.Amount) Then
                    ' Parsed successfully
                End If
            Case "commentColumn"
                dataRow.Comment = value?.ToString()
        End Select
    End Sub
    

    #End Region

    #Region "Data Models"

    ' DataRowModel class to represent a data row
    Public Class DataRowModel
        Public Property Id As Integer
        Public Property Name As String
        Public Property Type As String
        Public Property DateValue As DateTime
        Public Property StartTime As TimeSpan
        Public Property FinishTime As TimeSpan
        Public Property TotalTime As TimeSpan
        Public Property Amount As Decimal
        Public Property Comment As String
    End Class
    
    ' DataGridViewAction Class
    ' Class to represent actions for undo and redo
    Public Class DataGridViewAction
        Public Enum ActionType
            CellEdit
            RowAdded
            RowDeleted
            RowDuplicated
        End Enum
    
        ' Properties for the action
        Public Property Type As ActionType
    
        ' For cell edits
        Public Property RowIndex As Integer
        Public Property ColumnIndex As Integer
        Public Property OldValue As Object
        Public Property NewValue As Object
    
        ' For row addition and deletion
        Public Property DataRow As DataRowModel ' Required for RowDeleted and RowAdded actions
        Public Property RowId As String ' To identify the row in originalRows
    End Class
    
    Private Sub btnFB_Click(sender As Object, e As EventArgs) Handles btnFB.Click
        Dim psi As New System.Diagnostics.ProcessStartInfo()
        psi.FileName = "https://www.facebook.com/profile.php?id=61565337577476"
        psi.UseShellExecute = True
        System.Diagnostics.Process.Start(psi)
    End Sub
    

    #End Region

    End Class

    In the above. Is it possible to get it to connect to my OneDrive and update the CSV file there?

  2. Assistant

    Yes, it is absolutely possible to have your application connect to your OneDrive and update the CSV file there. There are two primary approaches to achieve this:

    1. Using the OneDrive Sync Folder on Your Local Machine
    2. Connecting to OneDrive Programmatically via Microsoft Graph API

    Given your scenario and for simplicity, the first approach is often the most straightforward and requires minimal changes to your existing code. Here's how you can do it:

    Option 1: Using the OneDrive Sync Folder on Your Local Machine

    If you have the OneDrive client installed on your computer, your OneDrive files are typically synced to a folder on your local machine. By saving your CSV file to this folder, it will automatically sync with your OneDrive cloud storage.

    Steps to Modify Your Application:

    1. Determine the OneDrive Folder Path:

      • OneDrive usually syncs files to C:\Users\YourUsername\OneDrive.
      • The exact path can vary, so we'll retrieve it programmatically.
    2. Retrieve the OneDrive Path Programmatically:

      • You can get the OneDrive folder path from the Windows Registry.
    Imports Microsoft.Win32
    
    Private Function GetOneDrivePath() As String
        Dim oneDrivePath As String = ""
        Try
            Using key As RegistryKey = Registry.CurrentUser.OpenSubKey("Software\Microsoft\OneDrive")
                If key IsNot Nothing Then
                    Dim value As Object = key.GetValue("UserFolder")
                    If value IsNot Nothing Then
                        oneDrivePath = value.ToString()
                    End If
                End If
            End Using
        Catch ex As Exception
            MessageBox.Show("Error retrieving OneDrive path: " & ex.Message)
        End Try
        Return oneDrivePath
    End Function
    
    1. Update Your Code to Use the OneDrive Path:
      • Replace instances where "data.csv" is used with the full path to the CSV file in your OneDrive folder.
    ' Store the OneDrive path and data file path
    Private oneDrivePath As String = GetOneDrivePath()
    Private dataFilePath As String = Path.Combine(oneDrivePath, "YourFolderInOneDrive", "data.csv")
    
    ' Update LoadData() method
    Private Sub LoadData()
        ' Use dataFilePath instead of "data.csv"
        If File.Exists(dataFilePath) Then
            ' Rest of your load logic...
        End If
    End Sub
    
    ' Update SaveData() method
    Private Sub SaveData()
        Try
            ' Use dataFilePath for writing
            Using writer As New StreamWriter(dataFilePath)
                ' Rest of your save logic...
            End Using
        Catch ex As IOException
            MessageBox.Show("An error occurred while saving data: " & ex.Message)
        End Try
    End Sub
    
    1. Handle Exceptions:

      • Add error handling to manage cases where OneDrive is not installed or the path cannot be retrieved.
    2. Test Your Application:

      • Ensure that the CSV file is correctly read from and written to the OneDrive folder.
      • Verify that changes sync to your OneDrive cloud storage.

    Benefits of This Approach:

    • Simplicity: Minimal code changes are required.
    • No Authentication Required: Since you're accessing a local folder, no additional authentication is needed.
    • Automatic Syncing: OneDrive client handles syncing between your local folder and the cloud.

    Option 2: Connecting to OneDrive Programmatically via Microsoft Graph API

    If you need your application to access OneDrive files over the network (e.g., when running on a different computer without the OneDrive client), you can use the Microsoft Graph API.

    Steps to Modify Your Application:

    1. Register Your Application with Azure AD:

      • Register your app in the Azure Portal to obtain an Application ID.
      • Configure the required permissions to access OneDrive files.
    2. Add the Microsoft Graph SDK to Your Project:

      • Install the Microsoft Graph SDK via NuGet Package Manager.
    3. Implement Authentication:

      • Use OAuth 2.0 to authenticate users.
      • Implement token caching to maintain session state.
    4. Update Your Code to Use Microsoft Graph API:

      • Replace file I/O operations with Microsoft Graph API calls.
      • Use GraphServiceClient to interact with OneDrive files.
    Imports Microsoft.Graph
    Imports Microsoft.Identity.Client
    
    ' Authentication providers and code to initialize GraphServiceClient would go here.
    
    Private Async Function LoadDataFromOneDriveAsync() As Task
        Try
            Dim fileContent As Stream = Await graphClient.Me.Drive.Special.AppRoot.ItemWithPath("data.csv").Content.Request().GetAsync()
            ' Read from the stream as per your logic
        Catch ex As ServiceException
            MessageBox.Show("Error loading data from OneDrive: " & ex.Message)
        End Try
    End Function
    
    Private Async Function SaveDataToOneDriveAsync() As Task
        Try
            ' Convert your data to a Stream
            Dim dataStream As Stream = ' Your logic to create a stream from data
    
            ' Upload the content to OneDrive
            Await graphClient.Me.Drive.Special.AppRoot.ItemWithPath("data.csv").Content.Request().PutAsync(Of DriveItem)(dataStream)
        Catch ex As ServiceException
            MessageBox.Show("Error saving data to OneDrive: " & ex.Message)
        End Try
    End Function
    
    1. Handle Permissions and Consent:

      • Users will need to consent to the application accessing their OneDrive files.
    2. Test Your Application:

      • Ensure that authentication and file operations work as expected.

    Considerations for This Approach:

    • Complexity: Requires significant changes to your code and understanding of authentication flows.
    • User Experience: Users will need to log in and grant permissions.
    • Security: You must securely handle tokens and sensitive information.

    Recommendation

    For most cases, especially if you're running the application on your own machine, Option 1 is the recommended approach due to its simplicity and minimal impact on your existing codebase. It allows you to leverage OneDrive's syncing capabilities without dealing with authentication and API complexities.

    Summary of Changes Needed

    1. Retrieve OneDrive Path Programmatically:

      • Implement the GetOneDrivePath() function as shown above.
    2. Update File Paths in Your Code:

      • Replace "data.csv" with dataFilePath in both loading and saving methods.
    3. Ensure Proper Error Handling:

      • Add checks and exception handling in case the OneDrive folder is not accessible.
    4. Test Extensively:

      • Verify that your application can read from and write to the CSV file in OneDrive and that the changes sync correctly.

    By following these steps, your application will seamlessly interact with your OneDrive storage, updating the CSV file there as required.