Imports DevExpress.Spreadsheet Imports DevExpress.Spreadsheet.Export Imports System Imports System.Data Imports System.Web.UI Imports System.Data.SqlClient Public Class ImportClienti 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_indirizzoVia As String Dim g_cap As String Dim g_comune As String Dim g_provincia As String Dim g_stato As String Dim g_indirizzoViaDest As String Dim g_capDest As String Dim g_comuneDest As String Dim g_provinciaDest As String Dim g_statoDest As String Dim g_telefono As String Dim g_cellulare As String Dim g_fax As String Dim g_email As String Dim g_email2 As String Dim g_url As String Dim g_partitaiva As String Dim g_codicefiscale As String Dim g_posizione As String Dim g_reparto As String Dim g_skype As String Dim g_twitter As String Dim g_facebook As String Dim g_referente As String Dim g_referenteTelefono As String Dim g_referenteEmail As String Dim fido As Double Dim g_dataAcqCliente 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_indirizzoVia = Grid.GetRowValues(i, "Column2") If Not IsDBNull(Grid.GetRowValues(i, "Column3")) Then g_cap = Grid.GetRowValues(i, "Column3") If Not IsDBNull(Grid.GetRowValues(i, "Column4")) Then g_comune = Grid.GetRowValues(i, "Column4") If Not IsDBNull(Grid.GetRowValues(i, "Column5")) Then g_provincia = Grid.GetRowValues(i, "Column5") If Not IsDBNull(Grid.GetRowValues(i, "Column6")) Then g_stato = Grid.GetRowValues(i, "Column6") If Not IsDBNull(Grid.GetRowValues(i, "Column7")) Then g_indirizzoViaDest = Grid.GetRowValues(i, "Column7") If Not IsDBNull(Grid.GetRowValues(i, "Column8")) Then g_capDest = Grid.GetRowValues(i, "Column8") If Not IsDBNull(Grid.GetRowValues(i, "Column9")) Then g_comuneDest = Grid.GetRowValues(i, "Column9") If Not IsDBNull(Grid.GetRowValues(i, "Column10")) Then g_provinciaDest = Grid.GetRowValues(i, "Column10") If Not IsDBNull(Grid.GetRowValues(i, "Column11")) Then g_statoDest = Grid.GetRowValues(i, "Column11") If Not IsDBNull(Grid.GetRowValues(i, "Column12")) Then g_telefono = Grid.GetRowValues(i, "Column12") If Not IsDBNull(Grid.GetRowValues(i, "Column13")) Then g_cellulare = Grid.GetRowValues(i, "Column13") If Not IsDBNull(Grid.GetRowValues(i, "Column14")) Then g_fax = Grid.GetRowValues(i, "Column14") If Not IsDBNull(Grid.GetRowValues(i, "Column15")) Then g_email = Grid.GetRowValues(i, "Column15") If Not IsDBNull(Grid.GetRowValues(i, "Column16")) Then g_email2 = Grid.GetRowValues(i, "Column16") If Not IsDBNull(Grid.GetRowValues(i, "Column17")) Then g_url = Grid.GetRowValues(i, "Column17") If Not IsDBNull(Grid.GetRowValues(i, "Column18")) Then g_partitaiva = Grid.GetRowValues(i, "Column18") If Not IsDBNull(Grid.GetRowValues(i, "Column19")) Then g_codicefiscale = Grid.GetRowValues(i, "Column19") If Not IsDBNull(Grid.GetRowValues(i, "Column20")) Then g_posizione = Grid.GetRowValues(i, "Column20") If Not IsDBNull(Grid.GetRowValues(i, "Column21")) Then g_reparto = Grid.GetRowValues(i, "Column21") If Not IsDBNull(Grid.GetRowValues(i, "Column22")) Then g_skype = Grid.GetRowValues(i, "Column22") If Not IsDBNull(Grid.GetRowValues(i, "Column23")) Then g_twitter = Grid.GetRowValues(i, "Column23") If Not IsDBNull(Grid.GetRowValues(i, "Column24")) Then g_facebook = Grid.GetRowValues(i, "Column24") If Not IsDBNull(Grid.GetRowValues(i, "Column25")) Then g_referente = Grid.GetRowValues(i, "Column25") If Not IsDBNull(Grid.GetRowValues(i, "Column26")) Then g_referenteTelefono = Grid.GetRowValues(i, "Column26") If Not IsDBNull(Grid.GetRowValues(i, "Column27")) Then g_referenteEmail = Grid.GetRowValues(i, "Column27") 'If Not IsDBNull(Grid.GetRowValues(i, "Column28")) Then fido = Grid.GetRowValues(i, "Column28") 'If Not IsDBNull(Grid.GetRowValues(i, "Column29")) Then g_dataAcqCliente = Grid.GetRowValues(i, "Column29") '----------------------------------------------------------------------- '--- Normalizza i campi per evitare che superino il maxlenght '----------------------------------------------------------------------- g_ragionesociale = "'" + Left(g_ragionesociale, 250).Replace("'", "''") + "'" g_indirizzoVia = "'" + Left(g_indirizzoVia, 100).Replace("'", "''") + "'" g_cap = "'" + Left(g_cap, 5).Replace("'", "''") + "'" g_comune = "'" + Left(g_comune, 100).Replace("'", "''") + "'" g_provincia = "'" + Left(g_provincia, 100).Replace("'", "''") + "'" g_stato = "'" + Left(g_stato, 100).Replace("'", "''") + "'" g_indirizzoViaDest = "'" + Left(g_indirizzoViaDest, 100).Replace("'", "''") + "'" g_capDest = "'" + Left(g_capDest, 5).Replace("'", "''") + "'" g_comuneDest = "'" + Left(g_comuneDest, 100).Replace("'", "''") + "'" g_provinciaDest = "'" + Left(g_provinciaDest, 100).Replace("'", "''") + "'" g_statoDest = "'" + Left(g_statoDest, 100).Replace("'", "''") + "'" g_telefono = "'" + Left(g_telefono, 50).Replace("'", "''") + "'" g_cellulare = "'" + Left(g_cellulare, 50).Replace("'", "''") + "'" g_fax = "'" + Left(g_fax, 50).Replace("'", "''") + "'" g_email = "'" + Left(g_email, 50).Replace("'", "''") + "'" g_email2 = "'" + Left(g_email2, 50).Replace("'", "''") + "'" g_url = "'" + Left(g_url, 250).Replace("'", "''") + "'" g_partitaiva = "'" + Left(g_partitaiva, 11).Replace("'", "''") + "'" g_codicefiscale = "'" + Left(g_codicefiscale, 16).Replace("'", "''") + "'" g_posizione = "'" + Left(g_posizione, 50).Replace("'", "''") + "'" g_reparto = "'" + Left(g_reparto, 50).Replace("'", "''") + "'" g_skype = "'" + Left(g_skype, 50).Replace("'", "''") + "'" g_twitter = "'" + Left(g_twitter, 50).Replace("'", "''") + "'" g_facebook = "'" + Left(g_facebook, 50).Replace("'", "''") + "'" g_referente = "'" + Left(g_referente, 50).Replace("'", "''") + "'" g_referenteTelefono = "'" + Left(g_referenteTelefono, 25).Replace("'", "''") + "'" g_referenteEmail = "'" + Left(g_referenteEmail, 50).Replace("'", "''") + "'" 'fido = "'" + Left(fido, 50).Replace("'", "''") + "'" 'g_dataAcqCliente = "'" + Left(g_dataAcqCliente, 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 Catch ex As Exception errorLabel.Text = ex.Message Response.Write("Errore: " & ex.Message) End Try connection.Close() '----------------------------------------------------------------------- '--- Normalizza la Provincia di destinazione, ricercandola in tabella '----------------------------------------------------------------------- Dim d_provincia As String Try connection.Open() tmpSQL = "SELECT IDProvincia FROM Province WHERE SiglaAutomobilistica =" & g_provinciaDest Dim SqlCommand = New SqlCommand(tmpSQL, connection) Dim dr = SqlCommand.ExecuteReader() If dr.HasRows Then dr.Read() d_provincia = dr.Item("IDProvincia") '--- prende l'ID della tabella Else d_provincia = vbNull End If Catch ex As Exception errorLabel.Text = ex.Message Response.Write("Errore PR: " & ex.Message) End Try connection.Close() '----------------------------------------------------------------------- '--- Normalizza la Nazione, mettendo Italia '----------------------------------------------------------------------- Dim r_nazione As String 'Dim connection2 = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString) Try connection.Open() tmpSQL = "SELECT IDNazione FROM Nazioni WHERE Descrizione = " & g_stato 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 Catch ex As Exception errorLabel.Text = ex.Message Response.Write("Errore NA1: " & ex.Message) End Try connection.Close() '----------------------------------------------------------------------- '--- Normalizza la Nazione di destinazione, mettendo Italia '----------------------------------------------------------------------- Dim d_nazione As String 'Dim connection2 = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString) Try connection.Open() tmpSQL = "SELECT IDNazione FROM Nazioni WHERE Descrizione = " & g_statoDest Dim SqlCommand = New SqlCommand(tmpSQL, connection) Dim dr = SqlCommand.ExecuteReader() If dr.HasRows Then dr.Read() d_nazione = dr.Item("IDNazione") '--- prende l'ID della tabella Else d_nazione = vbNull End If Catch ex As Exception errorLabel.Text = ex.Message Response.Write("NA2: " & ex.Message) End Try connection.Close() '-------------------------------------------------------------------------------------------------------------------- '--- Se attivo il flag di sovrascrittura, deve prima vedere se esiste il vecchio dato con stessa partita iva '-------------------------------------------------------------------------------------------------------------------- 'Dim r_nazione As String 'Dim connection2 = New SqlConnection(ConfigurationManager.ConnectionStrings("pa_data_userConnectionString").ConnectionString) Try connection.Open() tmpSQL = "SELECT idcliente FROM clienti WHERE partitaiva = " & g_partitaiva Dim SqlCommand = New SqlCommand(tmpSQL, connection) Dim dr = SqlCommand.ExecuteReader() saltaRecord = False If dr.HasRows Then '--- esiste già un cliente con quella partita iva... dr.Read() '--- se lo deve sovrascrivere, fa l'UPDATE... altrimenti la salta If cbSovrascrivi.Checked Then '----------------------------------------------------------------------- '--- Aggiorna la riga nel database '----------------------------------------------------------------------- Try tmpSQL = "update clienti set " tmpSQL = tmpSQL & " RagioneSociale = " & g_ragionesociale & ", " tmpSQL = tmpSQL & " IndirizzoVia = " & g_indirizzoVia & ", " tmpSQL = tmpSQL & " IndirizzoCap = " & g_cap & ", " tmpSQL = tmpSQL & " Comune = " & g_comune & ", " tmpSQL = tmpSQL & " Idprovincia = " & r_provincia & ", " tmpSQL = tmpSQL & " idnazione = " & r_nazione & ", " tmpSQL = tmpSQL & " IndirizzoVia2 = " & g_indirizzoViaDest & ", " tmpSQL = tmpSQL & " IndirizzoCap2 = " & g_capDest & ", " tmpSQL = tmpSQL & " Comune2 = " & g_comuneDest & ", " tmpSQL = tmpSQL & " Idprovincia2 = " & d_provincia & ", " tmpSQL = tmpSQL & " idnazione2 = " & d_nazione & ", " tmpSQL = tmpSQL & " telefono = " & g_telefono & ", " tmpSQL = tmpSQL & " cellulare = " & g_cellulare & ", " tmpSQL = tmpSQL & " fax = " & g_fax & ", " tmpSQL = tmpSQL & " email = " & g_email & ", " tmpSQL = tmpSQL & " email2 = " & g_email2 & ", " tmpSQL = tmpSQL & " url = " & g_url & ", " tmpSQL = tmpSQL & " partitaiva = " & g_partitaiva & ", " tmpSQL = tmpSQL & " codicefiscale = " & g_codicefiscale & ", " tmpSQL = tmpSQL & " posizione = " & g_posizione & ", " tmpSQL = tmpSQL & " reparto = " & g_reparto & ", " tmpSQL = tmpSQL & " skype = " & g_skype & ", " tmpSQL = tmpSQL & " twitter = " & g_twitter & ", " tmpSQL = tmpSQL & " facebook = " & g_facebook & ", " tmpSQL = tmpSQL & " referente = " & g_referente & ", " tmpSQL = tmpSQL & " TelefonoReferente = " & g_referenteTelefono & ", " tmpSQL = tmpSQL & " EmailReferente = " & g_referenteEmail & ", " tmpSQL = tmpSQL & " upd_dataora = " & g_ins_dataora & ", " tmpSQL = tmpSQL & " upd_utente = " & g_ins_utente & "" tmpSQL = tmpSQL & " where IDCliente = " & dr.Item("IDCliente") 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 CL2: " & ex.Message) End Try 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_partitaiva.Trim <> "" Then Try connection.Open() tmpSQL = "Insert into clienti (RagioneSociale, IndirizzoVia, IndirizzoCap, Comune, idprovincia, idnazione, IndirizzoVia2, IndirizzoCap2, Comune2, idprovincia2, idnazione2, telefono, cellulare, fax, email, email2, url, partitaiva, codicefiscale, posizione, reparto, skype, twitter, facebook, referente, TelefonoReferente, EmailReferente, ins_dataora, ins_utente, import)" tmpSQL = tmpSQL & " VALUES (" tmpSQL = tmpSQL & g_ragionesociale + "," + g_indirizzoVia + "," + g_cap + "," + g_comune + "," + r_provincia.ToString + "," + r_nazione.ToString + "," tmpSQL = tmpSQL & g_indirizzoViaDest + "," + g_capDest + "," + g_comuneDest + "," + d_provincia.ToString + "," + d_nazione.ToString + "," tmpSQL = tmpSQL & g_telefono + "," + g_cellulare + "," + g_fax + "," + g_email + "," + g_email2 + "," + g_url + "," + g_partitaiva + "," + g_codicefiscale + "," tmpSQL = tmpSQL & g_posizione + "," + g_reparto + "," + g_skype + "," + g_twitter + "," + g_facebook + "," + g_referente + "," + g_referenteTelefono + "," + g_referenteEmail + "," tmpSQL = tmpSQL & g_ins_dataora + "," + g_ins_utente + "," + g_import + ")" '--- esegue il comando --- Dim SqlCommand = New SqlCommand(tmpSQL, connection) Dim numeroRecords = SqlCommand.ExecuteNonQuery Catch ex As Exception errorLabel.Text = ex.Message Response.Write("Errore CL3: " & ex.Message) End Try connection.Close() 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 clienti" 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 ImportCienti_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