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

Public Class ImportProdotti
    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_Prodotto As String
        Dim g_Codice As String
        Dim g_CodiceEAN As String
        Dim g_Iva As String
        Dim g_Confezione As String
        Dim g_UnitaMisura As String
        Dim g_Valuta As String
        Dim g_IDListino As Integer
        Dim g_IDProdotto As Integer
        Dim g_import As String
        Dim g_Attivo As String
        Dim g_ins_dataora As String
        Dim g_ins_utente As String

        Dim g_Importo As Double
        Dim g_quantitarelativa As Double
        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_Prodotto = Grid.GetRowValues(i, "Column1")
            If Not IsDBNull(Grid.GetRowValues(i, "Column2")) Then g_Codice = Grid.GetRowValues(i, "Column2")
            If Not IsDBNull(Grid.GetRowValues(i, "Column3")) Then g_CodiceEAN = Grid.GetRowValues(i, "Column3")
            If Not IsDBNull(Grid.GetRowValues(i, "Column4")) Then g_Importo = Grid.GetRowValues(i, "Column4")
            If Not IsDBNull(Grid.GetRowValues(i, "Column5")) Then g_Iva = Grid.GetRowValues(i, "Column5")
            If Not IsDBNull(Grid.GetRowValues(i, "Column6")) Then g_Confezione = Grid.GetRowValues(i, "Column6")
            If Not IsDBNull(Grid.GetRowValues(i, "Column7")) Then g_UnitaMisura = Grid.GetRowValues(i, "Column7")

            '-----------------------------------------------------------------------
            '--- Normalizza i campi per evitare che superino il maxlenght
            '-----------------------------------------------------------------------
            g_Prodotto = "'" + Left(g_Prodotto, 250).Replace("'", "''") + "'"
            g_Codice = "'" + Left(g_Codice, 50).Replace("'", "''") + "'"
            g_CodiceEAN = "'" + Left(g_CodiceEAN, 20).Replace("'", "''") + "'"
            g_Iva = "'" + Left(g_Iva, 50).Replace("'", "''") + "'"
            g_UnitaMisura = "'" + Left(g_UnitaMisura, 10).Replace("'", "''") + "'"
            'fido = "'" + Left(fido, 50).Replace("'", "''") + "'"
            'g_dataAcqCliente = "'" + Left(g_dataAcqCliente, 50).Replace("'", "''") + "'"

            g_import = 1
            g_Attivo = 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 codice...
            '-----------------------------------------------------------------------
            If g_Codice.Trim = "" Then Continue For

            '-----------------------------------------------------------------------
            '--- Normalizza la l'aliquota iva, ricercandola in tabella
            '-----------------------------------------------------------------------
            Dim r_iva As String
            Try
                connection.Open()
                tmpSQL = "SELECT IdAliquotaIva FROM codiciiva WHERE Descrizione =" & g_Iva
                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim dr = SqlCommand.ExecuteReader()

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

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

            '--------------------------------------------------------------------------------------------------------------------
            '--- Se attivo il flag di sovrascrittura, deve prima vedere se esiste il vecchio dato con lo stesso codice
            '--------------------------------------------------------------------------------------------------------------------

            'Dim r_nazione As String
            'Dim connection2 = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString)
            Try
                connection.Open()
                tmpSQL = "SELECT idprodotto FROM prodotti WHERE Codice = " & g_Codice
                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim dr = SqlCommand.ExecuteReader()

                g_IDProdotto = 0

                saltaRecord = False

                If dr.HasRows Then
                    '--- esiste già un prodotto con quel Codice
                    dr.Read()
                    g_IDProdotto = dr.Item("idprodotto")

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

                        '-----------------------------------------------------------------------
                        '--- Aggiorna la riga nel database
                        '-----------------------------------------------------------------------
                        Try
                            tmpSQL = "update prodotti set "
                            tmpSQL = tmpSQL & " Descrizione = " & g_Prodotto & ", "
                            tmpSQL = tmpSQL & " Codice = " & g_Codice & ", "
                            tmpSQL = tmpSQL & " EAN = " & g_CodiceEAN & ", "
                            tmpSQL = tmpSQL & " IdCodiceIva = " & r_iva & ", "
                            tmpSQL = tmpSQL & " QtaConfezione = " & g_Confezione & ", "
                            tmpSQL = tmpSQL & " UnitaMisura = " & g_UnitaMisura & ", "
                            tmpSQL = tmpSQL & " IdValuta = " & Valute.DataValueField.ToString & ", "
                            tmpSQL = tmpSQL & " IdAzienda = " & Aziende.DataValueField.ToString & ", "
                            tmpSQL = tmpSQL & " upd_dataora = " & g_ins_dataora & ", "
                            tmpSQL = tmpSQL & " upd_utente = " & g_ins_utente & ""

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

                            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 Update: " & ex.Message)
                        End Try

                        'connection.Close()

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

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



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

                Try
                    connection.Open()
                    tmpSQL = "Insert into prodotti (Descrizione, Codice, EAN, IDCodiceIva, QtaConfezione, UnitaMisura, IDValuta, IDAzienda,  ins_dataora, ins_utente, import, Attivo)"
                    tmpSQL = tmpSQL & " VALUES ("
                    tmpSQL = tmpSQL & g_Prodotto + "," + g_Codice + "," + g_CodiceEAN + "," + r_iva.ToString + "," + g_Confezione.ToString + "," + g_UnitaMisura.ToString + "," + Valute.SelectedValue.ToString + "," + Aziende.SelectedValue.ToString + ","
                    tmpSQL = tmpSQL & g_ins_dataora + "," + g_ins_utente + "," + g_import + "," + g_Attivo + ")"

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

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

                connection.Close()
            End If

            '--------------------------------------------------------------------------------------------------------------------
            '--- Cancello il listino dell'articolo
            '--------------------------------------------------------------------------------------------------------------------
            Try
                connection.Open()
                tmpSQL = "delete from listini where IDListino = " & Listini.SelectedValue & " and IDProdotto = " & g_IDProdotto

                Dim SqlCommand = New SqlCommand(tmpSQL, connection)
                Dim numeroRecords = SqlCommand.ExecuteNonQuery
            Catch ex As Exception
            End Try
            connection.Close()

            '--------------------------------------------------------------------------------------------------------------------
            '--- Cancello il listino dell'articolo
            '--------------------------------------------------------------------------------------------------------------------
            Try
                g_quantitarelativa = 1
                connection.Open()
                tmpSQL = "Insert into AziendeListino (IDListino, IDProdotto, Importo, ImportoV, QuantitaRelativa, ins_dataora, ins_utente)"
                tmpSQL = tmpSQL & " VALUES ("
                tmpSQL = tmpSQL & Listini.SelectedValue.ToString + "," + g_IDProdotto.ToString + "," + g_Importo.ToString + "," + g_Importo.ToString + "," + g_quantitarelativa.ToString + ","
                tmpSQL = tmpSQL & g_ins_dataora + "," + g_ins_utente + ")"

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

            Catch ex As Exception
                errorLabel.Text = ex.Message
                Response.Write("Errore Insert: " & ex.Message & tmpSQL)
            End Try

            connection.Close()
        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 prodotti"

            Dim SqlCommand = New SqlCommand(tmpSQL, connection)
            Dim numeroRecords = SqlCommand.ExecuteNonQuery

        Catch ex As Exception
            'Response.Write("Errore: " & ex.Message)
            errorLabel.Text = "Errore DEL: " & ex.Message

        End Try

        connection.Close()
    End Function

    Private Sub ImportProdotti_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)

        Aggiornadslistini()

    End Sub


    Private Sub Listini_Load(sender As Object, e As EventArgs) Handles Listini.Load
        Aggiornadslistini()
    End Sub

    Private Sub dsAziende_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles dsAziende.Selecting

    End Sub

    Private Sub dsAziende_Selected(sender As Object, e As SqlDataSourceStatusEventArgs) Handles dsAziende.Selected


    End Sub

    Private Sub Aziende_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Aziende.SelectedIndexChanged
        AggiornadsListini()
        '--- filtra i dati in base al parametro, SE IL PARAMETRO > 0 ---
        'Dim sqlCmd = "SELECT * from listini where idazienda =" & Aziende.SelectedValue.ToString
        'dsListini.SelectCommand = sqlCmd
        'dsListini.DataBind()
        'Listini.DataBind()
    End Sub
    Sub Aggiornadslistini()
        Dim sqlCmd
        If Aziende.SelectedValue.ToString.Trim <> "" Then
            sqlCmd = "SELECT * from listini where idazienda =" & Aziende.SelectedValue.ToString & "order by descrizione"
        Else
            sqlCmd = "SELECT * from listini order by descrizione"
        End If

        dsListini.SelectCommand = sqlCmd
        dsListini.DataBind()
        Listini.DataBind()
    End Sub
    Private Sub dsAziende_Filtering(sender As Object, e As SqlDataSourceFilteringEventArgs) Handles dsAziende.Filtering

    End Sub

    Private Sub dsAziende_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles dsAziende.Inserted

    End Sub

    Private Sub dsAziende_Inserting(sender As Object, e As SqlDataSourceCommandEventArgs) Handles dsAziende.Inserting

    End Sub

    Private Sub dsAziende_Updated(sender As Object, e As SqlDataSourceStatusEventArgs) Handles dsAziende.Updated

    End Sub

    Private Sub dsAziende_Updating(sender As Object, e As SqlDataSourceCommandEventArgs) Handles dsAziende.Updating

    End Sub

    Private Sub dsAziende_PreRender(sender As Object, e As EventArgs) Handles dsAziende.PreRender

    End Sub

    Private Sub dsAziende_Disposed(sender As Object, e As EventArgs) Handles dsAziende.Disposed

    End Sub

    Private Sub Listini_CallingDataMethods(sender As Object, e As CallingDataMethodsEventArgs) Handles Listini.CallingDataMethods

    End Sub

    Private Sub Listini_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Listini.SelectedIndexChanged

    End Sub

    Private Sub Listini_Init(sender As Object, e As EventArgs) Handles Listini.Init

    End Sub
End Class