' This file implements the code-behind class for ImportData.aspx.

#region"Imports statements"

Imports System
Imports System.Data
Imports System.Collections
Imports System.ComponentModel
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports BaseClasses
Imports BaseClasses.Utils
Imports BaseClasses.Data
Imports BaseClasses.Data.SqlProvider
Imports BaseClasses.Web.UI.WebControls

Imports FileUploadDownload.Business
Imports FileUploadDownload.Data

Imports System.Text
Imports System.IO



#end region

Namespace FileUploadDownload.UI
    
    
    Class ImportData
        Inherits BaseApplicationPage

#Region "Section 1: Place your customizations here."
        ' Code-behind class for the ImportData page.
        ' Place your customizations in Section 1. Do not modify Section 2.

        Public Sub New()
            MyBase.New()
            Me.Initialize()
            AddHandler Init, AddressOf Me.MyInit
        End Sub

        Public ReadOnly Property AppName() As String
            Get
                Return BaseClasses.Configuration.ApplicationSettings.Current.GetAppSetting(BaseClasses.Configuration.ApplicationSettings.ConfigurationKey.ApplicationName)
            End Get
        End Property

        Public ReadOnly Property TableName() As String
            Get
                Return Me.Decrypt(BaseClasses.Utils.NetUtils.GetUrlParam(Me, "TableName", True))
            End Get
        End Property

        Public ReadOnly Property FilePath() As String
            Get
                Return CType(Session("FilePath"), String)
            End Get
        End Property

        Public ReadOnly Property Table() As String
            Get
                Dim errMsg As String
                Dim TYPE_FORMAT As String = "{0}.{1}{2},{3}"
                Dim tableName As String = ""
                Try
                    ' Try getting from the URL. If not, get it from the session. 
                    tableName = Me.TableName
                    'this.Decrypt(BaseClasses.Utils.NetUtils.GetUrlParam(this, "TableName", true));
                Catch ex As Exception
                    ' Get from the session. This table name already contains the prefix required, so we return directly. 

                End Try
                Try
                    Dim tableType As String = String.Format(TYPE_FORMAT, "FileUploadDownload.Business", tableName, "Table", "App_Code")
                    Type.GetType(tableType, True, True)
                    Return tableType
                Catch ex As Exception
                    errMsg = ex.Message
                End Try
                Try
                    Dim viewType As String = String.Format(TYPE_FORMAT, "FileUploadDownload.Business", tableName, "View", "App_Code")
                    Type.GetType(viewType, True, True)
                    Return viewType
                Catch ex As Exception
                    errMsg = ex.Message
                End Try
                Try
                    Dim queryType As String = String.Format(TYPE_FORMAT, "FileUploadDownload.Business", tableName, "Query", "App_Code")
                    Type.GetType(queryType, True, True)
                    Return queryType
                Catch ex As Exception
                    errMsg = ex.Message
                End Try
                Return ""
            End Get
        End Property

        Public Sub LoadData()
            ' LoadData reads database data and assigns it to UI controls.
            ' Customize by adding code before or after the call to LoadData_Base()
            ' or replace the call to LoadData_Base().
            LoadData_Base()

        End Sub

        <System.Web.Services.WebMethod()> _
        Public Shared Function GetRecordFieldValue(ByVal tableName As String, ByVal recordID As String, ByVal columnName As String, ByVal title As String, ByVal persist As Boolean, ByVal popupWindowHeight As Integer, ByVal popupWindowWidth As Integer, ByVal popupWindowScrollBar As Boolean) As Object()
            ' GetRecordFieldValue gets the pop up window content from the column specified by
            ' columnName in the record specified by the recordID in data base table specified by tableName.
            ' Customize by adding code before or after the call to  GetRecordFieldValue_Base()
            ' or replace the call to  GetRecordFieldValue_Base().
            Return GetRecordFieldValue_Base(tableName, recordID, columnName, title, persist, popupWindowHeight, popupWindowWidth, popupWindowScrollBar)
        End Function

        <System.Web.Services.WebMethod()> _
        Public Shared Function GetImage(ByVal tableName As String, ByVal recordID As String, ByVal columnName As String, ByVal title As String, ByVal persist As Boolean, ByVal popupWindowHeight As Integer, ByVal popupWindowWidth As Integer, ByVal popupWindowScrollBar As Boolean) As Object()
            ' GetImage gets the Image url for the image in the column "columnName" and
            ' in the record specified by recordID in data base table specified by tableName.
            ' Customize by adding code before or after the call to  GetImage_Base()
            ' or replace the call to  GetImage_Base().
            Return GetImage_Base(tableName, recordID, columnName, title, persist, popupWindowHeight, popupWindowWidth, popupWindowScrollBar)
        End Function

        Private Sub MyInit(ByVal sender As Object, ByVal e As System.EventArgs)
            If Not (Session("FilePath") Is Nothing) Then
                ' Retrieve information from session.
                Dim updatedRecordID As String = CType(Session("FilePath"), String)
                ParseCSVFile()
            End If
            Me.Page.Title = Me.GetResourceValue("Import:Step2", Me.AppName)
            Me.ImportFirstRowCheckBox.Checked = True
        End Sub


        Public Sub PreviousButton_Click(ByVal sender As Object, ByVal args As EventArgs)
            ' Click handler for PreviousButton.
            ' Customize by adding code before the call or replace the call to the Base function with your own code.
            'PreviousButton_Click_Base(sender, args);
            If (File.Exists(Me.FilePath)) Then
                File.Delete(Me.FilePath)
            End If
            Me.Page.Response.Redirect(("SelectFileToImport.aspx?TableName=" + Me.Encrypt(Me.TableName)))
            ' pass the table name as it is, without decryption.
            ' NOTE: If the Base function redirects to another page, any code here will not be executed.
        End Sub

        'This is the Import button click
        Public Sub ImportButton_Click(ByVal sender As Object, ByVal args As EventArgs)
            ' Click handler for ImportButton.
            ' Customize by adding code before the call or replace the call to the Base function with your own code.
            'ImportButton_Click_Base(sender, args);
            ImportButton_Click_Base(sender, args)
        End Sub


        Public Function GetTable() As BaseClasses.Data.BaseTable
            Dim tn As String = Me.Table
            Try
                Return BaseClasses.Data.BaseTable.CreateInstance(tn)
            Catch ex As System.Exception
                Return BaseClasses.Data.BaseTable.CreateInstance(tn)
            End Try
        End Function

#End Region

#Region "Section 2: Do not modify this section."

        Private Sub Initialize()
            ' Called by the class constructor to initialize event handlers for Init and Load
            ' You can customize by modifying the constructor in Section 1.
            AddHandler Init, AddressOf Me.Page_InitializeEventHandlers
            AddHandler Load, AddressOf Me.Page_Load
        End Sub

        ' Handles base.Init. Registers event handler for any button, sort or links.
        ' You can add additional Init handlers in Section 1.
        Protected Overridable Sub Page_InitializeEventHandlers(ByVal sender As Object, ByVal e As System.EventArgs)
            ' Register the Event handler for any Events.
            AddHandler ImportButton.Button.Click, AddressOf Me.ImportButton_Click
            AddHandler PreviousButton.Button.Click, AddressOf Me.PreviousButton_Click
        End Sub

        ' Handles base.Load.  Read database data and put into the UI controls.
        ' You can add additional Load handlers in Section 1.
        Protected Overridable Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ' Check if user has access to this page.  Redirects to either sign-in page
            ' or 'no access' page if not. Does not do anything if role-based security
            ' is not turned on, but you can override to add your own security.
            Me.Authorize(Me.GetAuthorizedRoles)
            ' Load data only when displaying the page for the first time
            If Not Me.IsPostBack Then
                ' Setup the header text for the validation summary control.
                Me.ValidationSummary1.HeaderText = GetResourceValue("ValidationSummaryHeaderText", Me.AppName)
                ' Show message on Click

                ' Read the data for all controls on the page.
                ' To change the behavior, override the DataBind method for the individual
                ' record or table UI controls.
                Me.LoadData()

            End If
        End Sub

        Public Shared Function GetRecordFieldValue_Base(ByVal tableName As String, ByVal recordID As String, ByVal columnName As String, ByVal title As String, ByVal persist As Boolean, ByVal popupWindowHeight As Integer, ByVal popupWindowWidth As Integer, ByVal popupWindowScrollBar As Boolean) As Object()
            Dim content As String = NetUtils.EncodeStringForHtmlDisplay(BaseClasses.Utils.MiscUtils.GetFieldData(tableName, recordID, columnName))
            ' returnValue is an array of string values.
            ' returnValue(0) represents title of the pop up window.
            ' returnValue(1) represents content ie, image url.
            ' returnValue(2) represents whether pop up window should be made persistant
            ' or it should close as soon as mouse moves out.
            ' returnValue(3), (4) represents pop up window height and width respectivly
            ' returnValue(5) represents whether pop up window should contain scroll bar.
            ' (0),(2),(3) and (4) is initially set as pass through attribute.
            ' They can be modified by going to Attribute tab of the properties window of the control in aspx page.
            Dim returnValue() As Object = New Object((6) - 1) {}
            returnValue(0) = title
            returnValue(1) = content
            returnValue(2) = persist
            returnValue(3) = popupWindowWidth
            returnValue(4) = popupWindowHeight
            returnValue(5) = popupWindowScrollBar
            Return returnValue
        End Function

        Public Shared Function GetImage_Base(ByVal tableName As String, ByVal recordID As String, ByVal columnName As String, ByVal title As String, ByVal persist As Boolean, ByVal popupWindowHeight As Integer, ByVal popupWindowWidth As Integer, ByVal popupWindowScrollBar As Boolean) As Object()
            Dim content As String = ("<IMG src =" + ("\""../Shared/ExportFieldValue.aspx?Table=" _
                        + (tableName + ("&Field=" _
                        + (columnName + ("&Record=" _
                        + (recordID + "\""/>")))))))
            ' returnValue is an array of string values.
            ' returnValue(0) represents title of the pop up window.
            ' returnValue(1) represents content ie, image url.
            ' returnValue(2) represents whether pop up window should be made persistant
            ' or it should close as soon as mouse moves out.
            ' returnValue(3), (4) represents pop up window height and width respectivly
            ' returnValue(5) represents whether pop up window should contain scroll bar.
            ' (0),(2),(3), (4) and (5) is initially set as pass through attribute.
            ' They can be modified by going to Attribute tab of the properties window of the control in aspx page.
            Dim returnValue() As Object = New Object((6) - 1) {}
            returnValue(0) = title
            returnValue(1) = content
            returnValue(2) = persist
            returnValue(3) = popupWindowWidth
            returnValue(4) = popupWindowHeight
            returnValue(5) = popupWindowScrollBar
            Return returnValue
        End Function

        ' Load data from database into UI controls.
        ' Modify LoadData in Section 1 above to customize.  Or override DataBind() in
        ' the individual table and record controls to customize.
        Public Sub LoadData_Base()
            Me.ImportFirstRowCheckBox.Text = GetResourceValue("Import:FirstRowText", Me.AppName)
        End Sub

        ' Write out event methods for the page events
        ' event handler for Button with Layout
        Public Sub ImportButton_Click_Base(ByVal sender As Object, ByVal args As EventArgs)
            Dim bt As BaseTable = Me.GetTable
            Dim success As Boolean = False
            Dim NumberOfRecordImported As Long() = New Long(1) {}
            Try
                ' Get the column names list and importChkBox list.
                Dim disp As Table = CType(Me.Page.FindControl("DisplayTable"), Table)
                Dim cellcount As Integer = disp.Rows(0).Cells.Count
                Dim ddListSelected As ArrayList = New ArrayList
                Dim chkBoxList As ArrayList = New ArrayList
                Dim rc As Integer = 0
                Do While (rc _
                            < (cellcount - 1))
                    Dim dd As DropDownList = CType(disp.FindControl(("dropDownList" & rc)), DropDownList)
                    If ddListSelected.Contains(dd.SelectedValue) AndAlso dd.SelectedValue <> "" Then
                        MiscUtils.RegisterJScriptAlert(Me, "Duplicate Column", Me.GetResourceValue("Import:DuplicateColumn", Me.AppName).Replace("{ColumnName}", dd.SelectedValue))
                        Return
                    End If
                    ddListSelected.Add(dd.SelectedValue)
                    Dim chkBox As CheckBox = CType(disp.FindControl(("importChkBox" & rc)), CheckBox)
                    chkBoxList.Add(chkBox)
                    'Store the index of the column which matches with the column name selected in the ColumnList drop down list.
                    ' This will be used later to updated a row based on this index
                    'if (dd.SelectedValue == this.ColumnsList.SelectedValue)
                    '    columnIndex = rc;
                    rc = (rc + 1)
                Loop
                ' Validate selected columns.
                If Not validateColumns(ddListSelected, chkBoxList, bt) Then
                    Return
                End If
                Dim imp As ImportDataItems = New ImportDataItems(Me.FilePath, GetFileType(), bt, ddListSelected, chkBoxList)
                NumberOfRecordImported = imp.ImportRecords(Me.ImportFirstRowCheckBox.Checked)
            Catch e As Exception
                ScriptManager.RegisterStartupScript(Me.Page, Page.GetType, "ErrorMsg", ("alert(" _
                                & (BaseClasses.Web.AspxTextWriter.CreateJScriptStringLiteral(e.Message) & ");")), True)
            End Try

            Dim msg As String = Me.GetResourceValue("Import:Success", Me.AppName).Replace("{Records}", NumberOfRecordImported(0).ToString())
            Dim msg2 As String = Me.GetResourceValue("Import:Skipped", Me.AppName).Replace("{Records}", NumberOfRecordImported(1).ToString())
            Dim script As String = ""
            script &= "<script>"
            script &= "CloseWindow('" & msg & "','" & msg2 & "');"
            script &= "</" + "script>"
            ClientScript.RegisterStartupScript(Page.GetType(), "CloseWindow", script)



        End Sub

        ' event handler for Button with Layout
        Public Sub PreviousButton_Click_Base(ByVal sender As Object, ByVal args As EventArgs)
            Try

            Catch ex As Exception
                Me.ErrorOnPage = True
                BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", ex.Message)
            Finally

            End Try
        End Sub
        ''' <summary>
        ''' Parses CSV file and retrieves five rows and populates ASP DataTable.
        ''' </summary>
        Public Sub ParseCSVFile()
            If File.Exists(Me.FilePath) Then
                Dim bt As BaseTable = Me.GetTable
                Dim td As TableDefinition = Nothing
                If (Not (bt) Is Nothing) Then
                    td = bt.TableDefinition
                End If
                Try
                    Dim parser As Parser = parser.GetParser(Me.FilePath, GetFileType())
                    'new CsvParser(path);
                    Dim checkboxChecked As String = CType(Session("checked"), String)
                    Dim columnListAspTableRow As TableRow = New TableRow
                    Dim columnAspTableRow As TableRow = New TableRow
                    Dim importAspTableRow As TableRow = New TableRow
                    ' Add first row with header label
                    Dim columnHeaderTableCell As TableCell = New TableCell
                    Dim importHeaderTableCell As TableCell = New TableCell
                    Dim columnListHeaderTableCell As TableCell = New TableCell
                    Dim columnHeaderLabel As Label = New Label
                    columnHeaderLabel.ID = "columnLabelHeader"
                    columnHeaderLabel.Text = GetResourceValue("Import:Column", Me.AppName) 'Column

                    Dim importLabel As Label = New Label
                    importLabel.ID = "importLabelHeader"
                    importLabel.Text = GetResourceValue("Import:Import", Me.AppName) 'Import

                    Dim columnListLabel As Label = New Label
                    columnListLabel.ID = "columnListLabelHeader"
                    '"Column names list"
                    columnListLabel.Text = GetResourceValue("Import:ColumnNames", Me.AppName)
                    columnHeaderTableCell.Controls.Add(columnHeaderLabel)
                    columnAspTableRow.Cells.Add(columnHeaderTableCell)
                    importHeaderTableCell.Controls.Add(importLabel)
                    importAspTableRow.Cells.Add(importHeaderTableCell)
                    columnListHeaderTableCell.Controls.Add(columnListLabel)
                    columnListHeaderTableCell.Wrap = False
                    columnListAspTableRow.Cells.Add(columnListHeaderTableCell)
                    DisplayTable.Rows.Add(columnAspTableRow)
                    DisplayTable.Rows.Add(importAspTableRow)
                    DisplayTable.Rows.Add(columnListAspTableRow)
                    Dim rowIndex As Integer = 0
                    Dim i As Integer = 0

                    ' Read 5 rows from csv file and display them in a Data Table.

                    While (rowIndex < 5)
                        Dim collValues() As String = parser.GetNextRow()
                        If collValues Is Nothing Then
                            parser.Close()
                            Return
                        End If
                        If collValues(0) Is Nothing Then
                            Continue While
                        End If
                        'Create column names dropdownList, import checkbox and Columns header
                        If (rowIndex = 0) Then

                            For Each value As String In collValues
                                Dim aspTableCell As TableCell = New TableCell
                                Dim x As DropDownList = New DropDownList
                                x.ID = ("dropDownList" & i)
                                x.CssClass = "fili"
                                x.Items.Add("")
                                'this.ColumnsList.Items.Add(DDLIST_TEXT);
                                'Adding column names to drop down list which are retrieved from database
                                For Each column As BaseColumn In td.Columns
                                    Dim item As New ListItem()
                                    item.Text = column.Name
                                    item.Value = column.InternalName
                                    x.Items.Add(item)
                                Next
                                aspTableCell.Controls.Add(x)
                                columnListAspTableRow.Cells.Add(aspTableCell)
                                Dim columnTableCell As TableCell = New TableCell
                                Dim importTableCell As TableCell = New TableCell
                                Dim columnLabel As Label = New Label
                                columnLabel.ID = ("columnLabel" & i)
                                columnLabel.Text = (i + 1).ToString
                                Dim importChkBox As CheckBox = New CheckBox
                                importChkBox.ID = ("importChkBox" & i)
                                ' center align the check boxes and column numbers
                                importTableCell.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center
                                columnTableCell.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center
                                columnTableCell.Controls.Add(columnLabel)
                                columnAspTableRow.Cells.Add(columnTableCell)
                                importTableCell.Controls.Add(importChkBox)
                                importAspTableRow.Cells.Add(importTableCell)
                                If ((Not (value) Is Nothing) _
                                            AndAlso (Not (x.Items.FindByText(value)) Is Nothing)) Then
                                    x.Items.FindByText(value).Selected = True
                                    importChkBox.Checked = True
                                End If
                                i = (i + 1)
                            Next
                        End If
                        ' reads through temp table and creates table cell and rows out of it and adds it to Display table
                        Dim aspTableRow As TableRow = New TableRow
                        ' let the first cell contains row number.
                        Dim firstTableCell As TableCell = New TableCell
                        firstTableCell.Text = (rowIndex + 1).ToString
                        aspTableRow.Cells.Add(firstTableCell)
                        For Each value As String In collValues
                            Dim aspTableCell As TableCell = New TableCell
                            If (value.Length > 100) Then
                                aspTableCell.Text = (value.Substring(0, 100) & "...")
                            Else
                                aspTableCell.Text = value
                            End If
                            aspTableRow.Cells.Add(aspTableCell)
                        Next
                        DisplayTable.Rows.Add(aspTableRow)
                        rowIndex = (rowIndex + 1)

                    End While
                    parser.Close()
                Catch ex As Exception
                    MiscUtils.RegisterJScriptAlert(Me, "Imort_Error_Message", ex.Message)
                    Me.ImportButton.Button.Enabled = False
                End Try
            Else
                BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", "file not found")
            End If
        End Sub
        ''' <summary>
        ''' Perform validation on selected columns
        ''' </summary>
        ''' <param name="ddListSelected"></param>
        ''' <param name="chkBoxList"></param>
        ''' <param name="bTable"></param>
        ''' <returns></returns>
        Public Function validateColumns(ByVal ddListSelected As ArrayList, ByVal chkBoxList As ArrayList, ByVal bTable As BaseTable) As Boolean
            Dim j As Integer = 0

            While (j < ddListSelected.Count)
                If ((ddListSelected(j).ToString <> "") _
                            AndAlso CType(chkBoxList(j), CheckBox).Checked) Then
                    Dim currentColumn As BaseColumn = bTable.TableDefinition.ColumnList.GetByAnyName(CType(ddListSelected(j), String))
                    'Dim fkColumn As ForeignKey = bTable.TableDefinition.GetForeignKeyByColumnName(currentColumn.InternalName)
                    '' 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) _
                    '            AndAlso fkColumn.IsCompositeKey) Then
                    '    Throw New Exception(Me.GetResourceValue("Import:CompositeColumn", Me.AppName))
                    '    Return False
                    'End If
                    'If currentColumn.IsPrimaryKeyElement Then
                    '    MiscUtils.RegisterJScriptAlert(Me, "Imort_Error_Message", Me.GetResourceValue("Import:PrimaryKey", Me.AppName).Replace("{ColumnName}", currentColumn.Name))
                    '    Return False
                    'End If
                    If currentColumn.IsValuesComputed Then
                        MiscUtils.RegisterJScriptAlert(Me, "Imort_Error_Message", Me.GetResourceValue("Import:ReadOnly", Me.AppName).Replace("{ColumnName}", currentColumn.Name))
                        Return False
                    End If

                End If
                j = (j + 1)

            End While
            Return True
        End Function
        Public Function GetFileType() As Parser.FileTypes
            Dim type As String = String.Empty
            If Session("FileType").ToString() <> "" Then
                type = Session("FileType").ToString()
                Select Case type
                    Case "CSV"
                        Return Parser.FileTypes.CSV
                    Case "TAB"
                        Return Parser.FileTypes.TAB
                    Case "XLS"
                        Return Parser.FileTypes.XLS
                    Case "XLSX"
                        Return Parser.FileTypes.XLSX
                    Case "MDB"
                        Me.ImportFirstRowCheckBox.Checked = False
                        Me.ImportFirstRowCheckBox.Enabled = False
                        Return Parser.FileTypes.MDB
                    Case "ACCDB"
                        Me.ImportFirstRowCheckBox.Checked = False
                        Me.ImportFirstRowCheckBox.Enabled = False
                        Return Parser.FileTypes.ACCDB
                End Select
            End If

            Return Parser.FileTypes.CSV

        End Function
        Protected Overrides Sub UpdateSessionNavigationHistory()
            'Do Nothing
        End Sub
#End Region
    End Class
End Namespace

