i_can_csv

Public Class DbManager
    Private _masterFilePath As String = ""
    Private _slaveFilePath As String = ""
    Private _masterItemsList As New List(Of MasterItem)
    Private _slaveItemList As New List(Of SlaveItem)
    Private _masterFileHeaderText As String = ""
    Private _slaveFileHeaderText As String = ""
    Private Const MASTER_FILE_COLUMN_COUNT As Int32 = 7
    Private Const SLAVE_FILE_COLUMN_COUNT As Int32 = 19
    Private Const SLAVE_FILE_HEADER_TEXT As String = "Item ID,Name,Category,Description,Variant 1 - Name,Variant 1 - Price,Variant 1 - SKU,Variant 2 - Name,Variant 2 - Price,Variant 2 - SKU,Variant 3 - Name,Variant 3 - Price,Variant 3 - SKU,Variant 4 - Name,Variant 4 - Price,Variant 4 - SKU,Variant 5 - Name,Variant 5 - Price,Variant 5 - SKU"



    Public Sub OpenMasterFile(ByVal filePath As String)
        If System.IO.File.Exists(filePath) Then
            _masterItemsList.Clear()
            Dim sr As New System.IO.StreamReader(filePath)
            Dim origLine As String = ""
            Dim tmpLine As String = ""
            Dim splitLines() As String
            Dim tmpItem As New MasterItem
            Dim lineNumber As Int32 = 1
            Dim headerLine As String = sr.ReadLine
            If headerLine.Split(","c).Count() <> MASTER_FILE_COLUMN_COUNT Then
                MessageBox.Show("OpenMasterFile():: Invalid header for Master File on line 1 ! Expected " & MASTER_FILE_COLUMN_COUNT.ToString() & " columns, but got: " & Environment.NewLine _
                                & headerLine & Environment.NewLine & "UNABLE TO CONTINUE!")
                sr.Close() : sr.Dispose()
                Exit Sub
            End If
            _masterFileHeaderText = headerLine ''need to backup for when we write a new output file
            While sr.Peek <> -1
                origLine = sr.ReadLine
                tmpLine = origLine
                splitLines = tmpLine.Split(","c)
                lineNumber += 1
                If splitLines(1).StartsWith("""") Then ''has special ItemName - including commas and double-quote marks
                    tmpItem.InternalID = splitLines(0)
                    Dim specialLine As String = tmpLine.Substring(tmpLine.IndexOf(","c) + 2) ''start of name + trailing info
                    tmpItem.Name = ParseSpecialCSVvalue(specialLine)
                    specialLine = specialLine.Substring(tmpItem.Name.Length + 2) ''+1 get rid of comma after item name
                    If specialLine.StartsWith("""") Then
                        'special VariationName also
                        tmpItem.VariationName = ParseSpecialCSVvalue(specialLine)
                        specialLine = specialLine.Substring(tmpItem.VariationName.Length + 1) ''+1 get rid of comma after variant name
                        splitLines = specialLine.Split(","c)
                        tmpItem.SKU = splitLines(0)
                        tmpItem.AlertThreshold = splitLines(1)
                        tmpItem.CurrentQuantity = splitLines(2)
                        tmpItem.NewQuantity = splitLines(3)
                    Else
                        'normal or no VariationName
                        splitLines = specialLine.Split(","c)
                        tmpItem.VariationName = splitLines(0)
                        tmpItem.SKU = splitLines(1)
                        tmpItem.AlertThreshold = splitLines(2)
                        tmpItem.CurrentQuantity = splitLines(3)
                        tmpItem.NewQuantity = splitLines(4)
                    End If
                ElseIf splitLines.Count = MASTER_FILE_COLUMN_COUNT Then
                    tmpItem.InternalID = splitLines(0)
                    tmpItem.Name = splitLines(1)
                    tmpItem.VariationName = splitLines(2)
                    tmpItem.SKU = splitLines(3)
                    tmpItem.AlertThreshold = splitLines(4)
                    tmpItem.CurrentQuantity = splitLines(5)
                    tmpItem.NewQuantity = splitLines(6)

                Else
                    MessageBox.Show("OpenMasterFile():: Invalid item on line: " & lineNumber.ToString() & ". Not " & MASTER_FILE_COLUMN_COUNT.ToString() & " columns?! SKIPPING this line!" & Environment.NewLine _
                                  & "Actual line: " & Environment.NewLine & tmpLine)
                    Continue While
                End If
                _masterItemsList.Add(tmpItem)
            End While
            sr.Close()
            sr.Dispose()
        Else
            Throw New Exception("DatabaseManager::OpenMasterItemFile() filepath does not exist! Unable to open 'Master Items File'")
        End If
        MsgBox("total items: " & _masterItemsList.Count.ToString())
    End Sub
    Public Sub OpenSlaveFile(ByVal filePath As String)
        If System.IO.File.Exists(filePath) Then
            Dim sr As New System.IO.StreamReader(filePath)
            Dim entireFileText As String = sr.ReadToEnd() : sr.Close() : sr.Dispose()
            Dim tmpItem As New SlaveItem
            Dim errorCount As Int32 = 0
            ''find header text
            Dim splitFileLines() As String = entireFileText.Split(ChrW(Keys.LineFeed))

            If splitFileLines(0) <> SLAVE_FILE_HEADER_TEXT Then ''invalid file?
                MessageBox.Show("OpenSlaveFile():: Invalid header for Slave File!" & Environment.NewLine _
                                 & "Actual value: " & splitFileLines(0) & Environment.NewLine & Environment.NewLine & "UNABLE TO CONTINUE!")
                sr.Close() : sr.Dispose()
                Exit Sub
            End If
            Dim tmpIndex As Int32 = 0
            Dim tmpLine As String = ""
            Dim lineCount As Int32 = 0
            splitFileLines(0) = ""
            For Each fileLine As String In splitFileLines
                If fileLine <> "" Then
                    tmpLine = fileLine
                    tmpIndex = tmpLine.IndexOf(","c)
                    tmpItem.ItemID = tmpLine.Substring(0, tmpIndex)
                    tmpLine = tmpLine.Substring(tmpIndex + 1)
                    ''Name
                    If tmpLine.StartsWith(""""",") Then ''no ItemName!
                        MessageBox.Show("OpenSlaveFile:: Item with no name!" & Environment.NewLine & "Actual line: " & fileLine)
                        tmpItem.ItemName = ""
                        errorCount += 1
                        tmpLine = tmpLine.Substring(3)
                    ElseIf tmpLine(0) = """" Then         ''special ItemName
                        tmpLine = tmpLine.Substring(1)
                        tmpItem.ItemName = ParseSpecialCSVvalue(tmpLine)
                        tmpLine = tmpLine.Substring(tmpItem.ItemName.Length + 2)
                    Else                                  ''regular ItemName
                        tmpItem.ItemName = tmpLine.Substring(0, tmpLine.IndexOf(","c))
                        tmpLine = tmpLine.Substring(tmpItem.ItemName.Length + 1)
                    End If
                    ''Category
                    If tmpLine.StartsWith(""""",") Then
                        tmpItem.Category = "" ''no Category
                        errorCount += 1
                        tmpLine = tmpLine.Substring(3)
                    ElseIf tmpLine(0) = """" Then
                        tmpItem.Category = ParseSpecialCSVvalue(tmpLine) ''special Category
                        tmpLine = tmpLine.Substring(tmpItem.Category.Length + 2)
                    Else
                        tmpItem.Category = tmpLine.Substring(0, tmpLine.IndexOf(","c)) ''normal Category
                        tmpLine = tmpLine.Substring(tmpItem.Category.Length + 1)
                    End If
                    ''Description
                    If tmpLine.StartsWith(""""",") Then ''no Description!
                        tmpItem.Description = ""
                    ElseIf tmpLine(0) = """" Then     ''special Description
                        tmpItem.Description = ParseSpecialCSVvalue(tmpLine)
                        tmpLine = tmpLine.Substring(tmpItem.Description.Length + 1)
                    Else
                        tmpItem.Description = tmpLine.Substring(0, tmpLine.IndexOf(","c))
                    End If
                    ''Variant1to5(name,price,sku)
                    '' HERE:
                    lineCount += 1
                    _slaveItemList.Add(tmpItem)
                End If

            Next
            MessageBox.Show(lineCount.ToString() & " items scanned." & Environment.NewLine _
                          & "Lines containing errors: " & errorCount.ToString, "SLAVE File")
        Else
            Throw New Exception("DatabaseManager::OpenSlaveItemFile() filepath does not exist! Unable to open 'Slave Items File'")
        End If

    End Sub
    Public Structure MasterItem
        Public InternalID As String
        Public Name As String
        Public VariationName As String
        Public SKU As String
        Public AlertThreshold As String
        Public CurrentQuantity As String
        Public NewQuantity As String
        Public Sub New(ByVal newInternalID As String, ByVal newItemName As String, ByVal newVariationName As String, ByVal newSKU As String, ByVal newAlertThreshold As String, ByVal newCurrentQuantity As String, ByVal newNewQuantity As String)
            InternalID = newInternalID
            Name = newItemName
            VariationName = newVariationName
            SKU = newSKU
            AlertThreshold = newAlertThreshold
            CurrentQuantity = newCurrentQuantity
            NewQuantity = newNewQuantity
        End Sub
    End Structure
    Public Structure SlaveItem
        Public ItemID As String
        Public ItemName As String
        Public Category As String
        Public Description As String
        Public Variant1Name, Variant1Price, Variant1SKU As String
        Public Variant2Name, Variant2Price, Variant2SKU As String
        Public Variant3Name, Variant3Price, Variant3SKU As String
        Public Variant4Name, Variant4Price, Variant4SKU As String
        Public Variant5Name, Variant5Price, Variant5SKU As String
    End Structure

    Private Function ParseSpecialCSVvalue(ByVal textLine As String) As String
        For xx As Int32 = 0 To textLine.Length - 2
            If textLine(xx) = """" Then
                If textLine(xx + 1) = """" Then ''double-quote mark inside of item name
                    xx += 1 ''skip the aforementioned double-quote character
                ElseIf textLine(xx + 1) = "," Then
                    Return textLine.Substring(0, xx) ''closing quote
                Else
                    MsgBox("ParseSpecialCSVvalue:: Unknown string literal with double-quote. Actual text:" & Environment.NewLine & Environment.NewLine & textLine) '' unknown string literal beginning with " , or formatting error
                    Exit For
                End If
            End If
        Next
        Return "ERROR"
    End Function
    Public Function GetMasterItemData(ByVal SKU As String) As MasterItem
        For Each itm As MasterItem In _masterItemsList
            If itm.SKU = SKU Then ''found the item
                Return itm
            End If
        Next
        ''if we get here, we never found the item
        Return New MasterItem("error", "error", "error", SKU, "error", "error", "error")
    End Function

End Class