Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export
Imports System
Imports System.Data
Imports System.Web.UI
Imports System.Data.SqlClient

Public Class ImportAziende
    Inherits System.Web.UI.Page
    Dim fileName As String
    Dim tmpSQL As String
    Dim connection

    Private Const UploadDirectory As String = "~/UploadFiles/"

    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    '-- AGGIUNGERE REFERENZA DevExpress.Docs.v15.1.dLL
    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Private Property FilePath() As String
        Get
            Return If(Session("FilePath") Is Nothing, String.Empty, Session("FilePath").ToString())
        End Get
        Set(ByVal value As String)
            Session("FilePath") = value
        End Set
    End Property
    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As EventArgs)
        If Not IsPostBack Then
            FilePath = String.Empty
        End If
    End Sub
    Protected Sub ASPxUploadControl_FileUploadComplete(ByVal sender As Object, ByVal e As DevExpress.Web.FileUploadCompleteEventArgs)
        FilePath = Page.MapPath(UploadDirectory) & e.UploadedFile.FileName
        e.UploadedFile.SaveAs(FilePath)
    End Sub
    Private Function GetTableFromExcel() As DataTable
        Dim book As New Workbook()
        'Dim workbook As  Workbook = New Workbook()

        AddHandler book.InvalidFormatException, AddressOf book_InvalidFormatException
        book.LoadDocument(FilePath)
        Dim sheet As DevExpress.Spreadsheet.Worksheet = book.Worksheets.ActiveWorksheet
        Dim range As Range = sheet.GetUsedRange()
        Dim table As DataTable = sheet.CreateDataTable(range, False)
        Dim exporter As DataTableExporter = sheet.CreateDataTableExporter(range, table, False)
        AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
        exporter.Export()
        Return table
    End Function

    Private Sub exporter_CellValueConversionError(ByVal sender As Object, ByVal e As CellValueConversionErrorEventArgs)
        e.Action = DataTableExporterAction.Continue
        e.DataTableValue = Nothing
    End Sub
    Private Sub book_InvalidFormatException(ByVal sender As Object, ByVal e As SpreadsheetInvalidFormatExceptionEventArgs)
        Dim exception As New Exception()
        Throw New Exception(e.Exception.Message, exception)
    End Sub

    Protected Sub Grid_Init(ByVal sender As Object, ByVal e As EventArgs)
        If Not String.IsNullOrEmpty(FilePath) Then
            Grid.DataSource = GetTableFromExcel()
            Grid.DataBind()
        End If
    End Sub

    Sub Elabora_Excel()
        Dim i As Integer

        Dim g_ragionesociale As String
        Dim g_partitaiva As String
        Dim g_codicefiscale As String
        Dim g_indirizzoVia As String
        Dim g_citta As String
        Dim g_cap As String
        Dim g_provincia As String
        Dim g_nazione As String
        Dim g_telefono As String
        Dim g_telefono2 As String
        Dim g_fax As String
        Dim g_email As String
        Dim g_url As String
        Dim g_referente As String
        Dim g_referenteTelefono As String
        Dim g_import As String
        Dim g_ins_dataora As String
        Dim g_ins_utente As String

        Dim saltaRecord As Boolean

        errorLabel.Text = ""

        '-----------------------------------------------------------------------
        '--- Rilegge la griglia per prendere le singole colonne, se non nulle
        '-----------------------------------------------------------------------
        For i = 0 To Grid.VisibleRowCount - 1

            If Not IsDBNull(Grid.GetRowValues(i, "Column1")) Then g_ragionesociale = Grid.GetRowValues(i, "Column1")
            If Not IsDBNull(Grid.GetRowValues(i, "Column2")) Then g_partitaiva = Grid.GetRowValues(i, "Column2")
            If Not IsDBNull(Grid.GetRowValues(i, "Column3")) Then g_codicefiscale = Grid.GetRowValues(i, "Column3")
            If Not IsDBNull(Grid.GetRowValues(i, "Column4")) Then g_indirizzoVia = Grid.GetRowValues(i, "Column4")
            If Not IsDBNull(Grid.GetRowValues(i, "Column5")) Then g_citta = Grid.GetRowValues(i, "Column5")
            If Not IsDBNull(Grid.GetRowValues(i, "Column6")) Then g_cap = Grid.GetRowValues(i, "Column6")
            If Not IsDBNull(Grid.GetRowValues(i, "Column7")) Then g_provincia = Grid.GetRowValues(i, "Column7")
            If Not IsDBNull(Grid.GetRowValues(i, "Column8")) Then g_nazione = Grid.GetRowValues(i, "Column8")
            If Not IsDBNull(Grid.GetRowValues(i, "Column9")) Then g_telefono = Grid.GetRowValues(i, "Column9")
            If Not IsDBNull(Grid.GetRowValues(i, "Column10")) Then g_telefono2 = Grid.GetRowValues(i, "Column10")
            If Not IsDBNull(Grid.GetRowValues(i, "Column11")) Then g_fax = Grid.GetRowValues(i, "Column11")
            If Not IsDBNull(Grid.GetRowValues(i, "Column12")) Then g_email = Grid.GetRowValues(i, "Column12")
            If Not IsDBNull(Grid.GetRowValues(i, "Column13")) Then g_url = Grid.GetRowValues(i, "Column13")
            If Not IsDBNull(Grid.GetRowValues(i, "Column14")) Then g_referente = Grid.GetRowValues(i, "Column14")
            If Not IsDBNull(Grid.GetRowValues(i, "Column15")) Then g_referenteTelefono = Grid.GetRowValues(i, "Column15")


            '-----------------------------------------------------------------------
            '--- Normalizza i campi per evitare che superino il maxlenght
            '-----------------------------------------------------------------------
            g_ragionesociale = "'" + Left(g_ragionesociale, 250).Replace("'", "''") + "'"
            g_partitaiva = "'" + Left(g_partitaiva, 11).Replace("'", "''") + "'"
            g_codicefiscale = "'" + Left(g_codicefiscale, 16).Replace("'", "''") + "'"
            g_indirizzoVia = "'" + Left(g_indirizzoVia, 100).Replace("'", "''") + "'"
            g_citta = "'" + Left(g_citta, 50).Replace("'", "''") + "'"
            g_cap = "'" + Left(g_cap, 50).Replace("'", "''") + "'"
            g_provincia = "'" + Left(g_provincia, 2).Replace("'", "''") + "'"
            g_nazione = "'" + Left(g_nazione, 50).Replace("'", "''") + "'"
            'g_nazione = 1
            g_telefono = "'" + Left(g_telefono, 50).Replace("'", "''") + "'"
            g_telefono2 = "'" + Left(g_telefono2, 50).Replace("'", "''") + "'"
            g_fax = "'" + Left(g_fax, 50).Replace("'", "''") + "'"
            g_email = "'" + Left(g_email, 100).Replace("'", "''") + "'"
            g_url = "'" + Left(g_url, 250).Replace("'", "''") + "'"
            g_referente = "'" + Left(g_referente, 250).Replace("'", "''") + "'"
            g_referenteTelefono = "'" + Left(g_referenteTelefono, 50).Replace("'", "''") + "'"
            g_import = 1
            g_ins_dataora = "'" + Date.Now.ToString("yyyy/MM/dd HH:mm:ss").Replace(".", ":") + "'"
            g_ins_utente = "'" + Session("sUtente_UserName") + "'"


            '-----------------------------------------------------------------------
            '--- Non considera i records SENZA partita iva...
            '-----------------------------------------------------------------------
            If g_partitaiva.Trim = "" Then Continue For

            '-----------------------------------------------------------------------
            '--- Normalizza la Provincia, ricercandola in tabella
            '-----------------------------------------------------------------------
            Dim r_provincia As String
            Try
                connection.Open()

                tmpSQL = "SELECT IDProvincia FROM Province WHERE SiglaAutomobilistica =" & g_provincia
                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim dr = SqlCommand.ExecuteReader()

                If dr.HasRows Then
                    dr.Read()
                    r_provincia = dr.Item("IDProvincia")        '--- prende l'ID della tabella
                Else
                    r_provincia = vbNull
                End If

                connection.Close()
            Catch ex As Exception
                errorLabel.Text = ex.Message
                Response.Write("Errore PR: " & ex.Message)
                connection.Close()
            End Try

            '-----------------------------------------------------------------------
            '--- Normalizza la Nazione, mettendo Italia
            '-----------------------------------------------------------------------
            Dim r_nazione As String
            Try
                connection.Open()
                tmpSQL = "SELECT IDNazione FROM Nazioni WHERE Descrizione = " & g_nazione
                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim dr = SqlCommand.ExecuteReader()

                If dr.HasRows Then
                    dr.Read()
                    r_nazione = dr.Item("IDNazione")        '--- prende l'ID della tabella
                Else
                    r_nazione = vbNull
                End If
                connection.Close()

            Catch ex As Exception
                errorLabel.Text = ex.Message
                Response.Write("Errore NA1: " & ex.Message)
                connection.Close()
            End Try

            '--------------------------------------------------------------------------------------------------------------------
            '--- Se attivo il flag di sovrascrittura, deve prima vedere se esiste il vecchio dato con stessa partita iva
            '--------------------------------------------------------------------------------------------------------------------
            Try
                connection.Open()
                tmpSQL = "SELECT idazienda FROM aziende WHERE partitaiva = " & g_partitaiva
                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim dr = SqlCommand.ExecuteReader()

                saltaRecord = False

                If dr.HasRows Then
                    '--- esiste già un'azienda con quella partita iva...
                    dr.Read()

                    '--- se la deve sovrascrivere, fa l'UPDATE... altrimenti la salta
                    If cbSovrascrivi.Checked Then

                        '-----------------------------------------------------------------------
                        '--- Aggiorna la riga nel database
                        '-----------------------------------------------------------------------
                        Try
                            tmpSQL = "update aziende set "
                            tmpSQL = tmpSQL & " Descrizione = " & g_ragionesociale & ", "
                            tmpSQL = tmpSQL & " partitaiva = " & g_partitaiva & ", "
                            tmpSQL = tmpSQL & " codicefiscale = " & g_codicefiscale & ", "
                            tmpSQL = tmpSQL & " indirizzo = " & g_indirizzoVia & ", "
                            tmpSQL = tmpSQL & " citta = " & g_citta & ", "
                            tmpSQL = tmpSQL & " cap = " & g_cap & ", "
                            tmpSQL = tmpSQL & " idprovincia = " & r_provincia & ", "
                            tmpSQL = tmpSQL & " idnazione = " & r_nazione & ", "
                            tmpSQL = tmpSQL & " telefono = " & g_telefono & ", "
                            tmpSQL = tmpSQL & " telefono2 = " & g_telefono2 & ", "
                            tmpSQL = tmpSQL & " fax = " & g_fax & ", "
                            tmpSQL = tmpSQL & " email = " & g_email & ", "
                            tmpSQL = tmpSQL & " url = " & g_url & ", "
                            tmpSQL = tmpSQL & " referente = " & g_referente & ", "
                            tmpSQL = tmpSQL & " telefonoref = " & g_referenteTelefono & ", "
                            tmpSQL = tmpSQL & " upd_dataora = " & g_ins_dataora & ", "
                            tmpSQL = tmpSQL & " upd_utente = " & g_ins_utente & ""

                            tmpSQL = tmpSQL & " where IDAzienda = " & dr.Item("IDAzienda")

                            dr.Close()

                            '--- esegue il comando ---
                            Dim SqlCommandUpd = New SqlCommand(tmpSQL, connection)
                            Dim numeroRecords = SqlCommandUpd.ExecuteNonQuery

                            '--- ha aggiornato il record quindi deve saltare la fase di inserimento
                            saltaRecord = True

                        Catch ex As Exception
                            Response.Write("Errore AZ2: " & ex.Message)
                        End Try

                    Else
                        '--- se esiste ma NON deve sovrascrivere, la salta
                        saltaRecord = True
                    End If
                End If

                connection.Close()
            Catch ex As Exception
                errorLabel.Text = ex.Message
                Response.Write("Errore AZ1: " & ex.Message)
                connection.Close()
            End Try



            '-----------------------------------------------------------------------
            '--- Inserisce la riga nel database
            '-----------------------------------------------------------------------
            'Dim connection_Ins = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString)
            If saltaRecord = False And g_partitaiva.Trim <> "" Then

                Try
                    connection.Open()
                    tmpSQL = "Insert into aziende (Descrizione, partitaiva, codicefiscale, indirizzo, citta, cap, idprovincia, idnazione, telefono, telefono2, fax, email, url, referente, telefonoref, ins_dataora, ins_utente, import)"
                    tmpSQL = tmpSQL & " VALUES ("
                    tmpSQL = tmpSQL & g_ragionesociale + "," + g_partitaiva + "," + g_codicefiscale + "," + g_indirizzoVia + "," + g_citta + "," + g_cap + "," + r_provincia.ToString + "," + r_nazione.ToString + ","
                    tmpSQL = tmpSQL & g_telefono + "," + g_telefono2 + "," + g_fax + "," + g_email + "," + g_url + "," + g_referente + "," + g_referenteTelefono + ","
                    tmpSQL = tmpSQL & g_ins_dataora + "," + g_ins_utente + "," + g_import + ")"
                    Debug.Print("tmpSQL: " + tmpSQL)

                    '--- esegue il comando ---
                    Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                    Dim numeroRecords = SqlCommand.ExecuteNonQuery

                    'Debug.Print("numeroRecords: " + numeroRecords)
                    connection.Close()
                Catch ex As Exception
                    errorLabel.Text = ex.Message
                    Response.Write("Errore AZ3: " & ex.Message)
                    connection.Close()
                End Try


            End If

        Next
        If errorLabel.Text = "" Then
            errorLabel.Text = "L'IMPORTAZIONE E' STATA COMPLETATA!"
        End If

    End Sub

    Protected Sub importASPxButton_Click(sender As Object, e As EventArgs) Handles importASPxButton.Click

        '--- se il codice captcha è valido, allora può procedere
        If ASPxCaptcha1.IsValid Then
            If cbAzzera.Checked Then
                AzzeraTabella()
            End If
            Elabora_Excel()
        End If

    End Sub

    Function AzzeraTabella()
        '-----------------------------------------------------------------------
        '--- Cancella l'intero contenuto della tabella  
        '-----------------------------------------------------------------------
        'Dim connection_Del = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString)
        Try
            connection.Open()
            tmpSQL = "delete from aziende"

            Dim SqlCommand = New SqlCommand(tmpSQL, connection)
            Dim numeroRecords = SqlCommand.ExecuteNonQuery
            connection.Close()
        Catch ex As Exception
            'Response.Write("Errore: " & ex.Message)
            errorLabel.Text = "Errore DEL: " & ex.Message
            connection.Close()
        End Try


    End Function

    Private Sub ImportAziende_Load(sender As Object, e As EventArgs) Handles Me.Load

        '********************************************************
        '--- Controllo Autenticazione per accesso alla pagina ---
        If Not Session("Autenticato") Then
            Response.Redirect("LoginUser.aspx")
        End If
        '********************************************************

        connection = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString)

    End Sub
End Class