
Imports System
Imports System.Data
Imports System.IO
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Collections
Imports BaseClasses
Imports BaseClasses.Utils
Imports BaseClasses.Data
Imports BaseClasses.Data.SqlProvider
Imports BaseClasses.Web.UI.WebControls

Namespace UseDAL.UI
    ''' <summary>
    ''' Summary description for ImportDataItems
    ''' </summary>
    Public Class ImportDataItems
        Dim NumberOfRecordImported As Long = 0
        Dim NumberOfRecordSkipped As Long = 0
        Dim RowCount As Integer = 1
        Dim ColumnCount As Integer = 1
#Region "Properties"

        ''' <summary>
        ''' The path of the file from which data is to be retrieved for import.
        ''' </summary>
        Private _fPath As String

        Public Property FilePath() As String
            Get
                Return _fPath
            End Get
            Set(ByVal value As String)
                _fPath = value
            End Set
        End Property

        ' <summary>
        ' Type of the file to be imported
        ' </summary>
        Private _fType As Parser.FileTypes

        Public Property FileType() As Parser.FileTypes
            Get
                Return _fType
            End Get
            Set(ByVal value As Parser.FileTypes)
                _fType = value
            End Set
        End Property

        ''' <summary>
        ''' The database table to which the records from CSV file has to be imported.
        ''' </summary>
        Private _tbl As BaseTable

        Private Property DBTable() As BaseTable
            Get
                Return Me._tbl
            End Get
            Set(ByVal value As BaseTable)
                Me._tbl = value
            End Set
        End Property

        ''' <summary>
        ''' Stores the list of column names for which the data from CSV file is to be imported to database table..
        ''' </summary>
        Private _columnNameList As ArrayList

        Private Property ColumnNameList() As ArrayList
            Get
                Return Me._columnNameList
            End Get
            Set(ByVal value As ArrayList)
                Me._columnNameList = value
            End Set
        End Property

        ''' <summary>
        ''' Stores the list of checkboxes which indicate whether corresponding column should be imported or not.
        ''' </summary>
        Private _cbxList As ArrayList

        Private Property ImportList() As ArrayList
            Get
                Return Me._cbxList
            End Get
            Set(ByVal value As ArrayList)
                Me._cbxList = value
            End Set
        End Property
#End Region

#Region "Constructor"
        Public Sub New(ByVal path As String, ByVal type As Parser.FileTypes, ByVal bTable As BaseTable, ByVal ddListSelected As ArrayList, ByVal chkBoxList As ArrayList)
            MyBase.New()
            '
            ' Add constructor logic here
            '
            Me.DBTable = bTable
            Me.ColumnNameList = ddListSelected
            Me.ImportList = chkBoxList
            Me.FilePath = path
            Me.FileType = type
        End Sub

#End Region



#Region "Methods"

        ''' <summary>
        ''' Reads  rows of values for CSV file and import it to Database.
        ''' </summary>
        Public Function ImportRecords(ByVal isImportFirstRowChecked As Boolean) As Long()
            Dim success As Boolean = False
            Dim parsr As Parser = Nothing
            Dim result As Long() = New Long(1) {NumberOfRecordImported, NumberOfRecordSkipped}

            Try
                If ((Me.FilePath Is Nothing) _
                            OrElse ((Me.DBTable Is Nothing) _
                            OrElse ((Me.ColumnNameList Is Nothing) _
                            OrElse (Me.ImportList Is Nothing)))) Then
                    Return result
                End If
                DbUtils.StartTransaction()
                parsr = Parser.GetParser(Me.FilePath, Me.FileType)
                Dim rowValues() As String = parsr.GetNextRow
                ' get the first row
                If (Not isImportFirstRowChecked _
                            AndAlso (Not (rowValues) Is Nothing)) Then
                    Try
                        DoImport(rowValues)
                        RowCount += 1

                    Catch
                        NumberOfRecordSkipped = NumberOfRecordSkipped + 1
                    End Try


                End If

                While (Not (rowValues) Is Nothing)
                    rowValues = parsr.GetNextRow
                    If (Not (rowValues) Is Nothing) Then
                        Try
                            DoImport(rowValues)
                            RowCount += 1

                        Catch
                            NumberOfRecordSkipped = NumberOfRecordSkipped + 1
                        End Try

                    End If

                End While
                DbUtils.CommitTransaction()
                parsr.Close()

                success = True
                ' turn on success only when all the rows are imported ie, saved into database.
            Catch e As Exception
                DbUtils.RollBackTransaction()
                NumberOfRecordImported = 0
                parsr.Close()
                Dim errorMsg As String = e.Message
                If File.Exists(Me.FilePath) Then
                    File.Delete(Me.FilePath)
                End If
                errorMsg = String.Format("- " & errorMsg & Environment.NewLine & "- Import error occurred at Row = {0},Column = {1}", RowCount, ColumnCount)
                Throw New Exception(errorMsg)
            Finally
                DbUtils.EndTransaction()
                If (File.Exists(Me.FilePath)) Then
                    File.Delete(Me.FilePath)
                End If
            End Try
            result(0) = NumberOfRecordImported
            result(1) = NumberOfRecordSkipped
            Return result
        End Function

        ''' <summary>
        ''' Creates a database record and calls UpdateColumnValuesInRecord to set the record values.
        ''' </summary>
        Private Sub DoImport(ByVal rowValues() As String)
            Dim r As IRecord
            r = Me.DBTable.CreateRecord
            If (UpdateColumnValuesInRecord(rowValues, r)) Then
                r.Save()
                NumberOfRecordImported = NumberOfRecordImported + 1
            End If
        End Sub

        ''' <summary>
        ''' Sets a database record values with values retrieved from CSV file.
        ''' </summary>
        ''' <param name="rowValues"></param>
        ''' <param name="record"></param>
        Private Function UpdateColumnValuesInRecord(ByVal rowValues() As String, ByVal record As IRecord) As Boolean
            Dim j As Integer = 0
            Dim isRecordUpdated As Boolean = False
            ColumnCount = 1
            For Each data As String In rowValues
                ColumnCount = ColumnCount + 1
                If j > Me.ImportList.Count - 1 Then
                    Return isRecordUpdated
                End If
                Try


                    If ((Me.ColumnNameList(j).ToString <> "") _
                                AndAlso CType(Me.ImportList(j), CheckBox).Checked) Then
                        Dim currentColumn As BaseColumn = Me.DBTable.TableDefinition.ColumnList.GetByAnyName(CType(Me.ColumnNameList(j), String))
                        Dim fkColumn As ForeignKey = Me.DBTable.TableDefinition.GetForeignKeyByColumnName(currentColumn.InternalName)
                        Dim colValue As String = ""
                        ' Check if the foreign key has DFKA. If so, then check the calue from csv file agains the DFKA column in the parent/foreign key table.
                        ' If a match is found retrieve its ID and set that as value to be insterted in the current table where you are adding records.
                        If (Not (fkColumn) Is Nothing) Then
                            Dim originalTableDef As TableDefinition = fkColumn.PrimaryKeyTableDefinition
                            Dim originalBaseTable As BaseTable = originalTableDef.CreateBaseTable
                            Dim wc As WhereClause = Nothing
                            Dim records As ArrayList = New ArrayList
                            Dim pkColumn As BaseColumn = CType(originalTableDef.PrimaryKey.Columns(0), BaseColumn)
                            'Index is zero because we handle only those tables which has single PK column not composite keys.
                            If ((Not (fkColumn.PrimaryKeyDisplayColumns) Is Nothing) _
                                        AndAlso (fkColumn.PrimaryKeyDisplayColumns <> "")) Then
                                wc = New WhereClause(originalTableDef.ColumnList.GetByAnyName(fkColumn.PrimaryKeyDisplayColumns), BaseFilter.ComparisonOperator.EqualsTo, data)
                            Else
                                ' if the foreign key does not have DFKA then just check in the foreign key table if the id exists. If not create a record with the specified ID
                                ' before adding to current table
                                wc = New WhereClause(pkColumn, BaseFilter.ComparisonOperator.EqualsTo, data)
                            End If
                            records = originalBaseTable.GetRecordList(wc.GetFilter, Nothing, 0, 100)
                            If (records.Count > 0) Then
                                ' take the first record and retrieve its ID.
                                Dim rec As BaseRecord = CType(records(0), BaseRecord)
                                colValue = rec.GetValue(pkColumn).ToString
                            Else
                                '  IF there is not match found then you have to create a record in the foreign key table with DFKA value and then retreive its ID
                                If ((Not (data) Is Nothing) _
                                            And (data <> "")) Then
                                    Dim tempRec As IRecord
                                    If ((Not (fkColumn.PrimaryKeyDisplayColumns) Is Nothing) _
                                                AndAlso (fkColumn.PrimaryKeyDisplayColumns <> "")) Then
                                        tempRec = originalBaseTable.CreateRecord
                                        Dim tableDef As TableDefinition = originalBaseTable.TableDefinition
                                        For Each newCol As BaseColumn In tableDef.Columns
                                            If fkColumn.PrimaryKeyDisplayColumns = newCol.InternalName Then
                                                tempRec.SetValue(data, newCol.UniqueName)
                                            End If
                                        Next
                                    Else
                                        tempRec = originalBaseTable.CreateRecord(data)
                                    End If
                                    tempRec.Save()
                                    colValue = tempRec.GetValue(pkColumn).ToString
                                End If
                                'colValue = data
                            End If
                        Else
                            colValue = data
                        End If
                        ' set the table row's column for value
                        record.SetValue(colValue, currentColumn.UniqueName)
                        isRecordUpdated = True
                    End If
                    j = (j + 1)
                Catch ex As Exception
                    Throw New Exception(ex.InnerException.Message)
                End Try

            Next
            Return isRecordUpdated
        End Function
#End Region
    End Class
End Namespace