Torna al Thread
Dim codprov, codcom, codalb, codcat, codnaz, codprovMov, codgruppo, partito, camdisp, camocc As Integer
Dim dataArrivo, dataPartenza As Date
Dim nschedina As String
Dim contatore_righe As Integer = 1
If FileUpload1.HasFile Then
REM RICAVO IL VALORE MASSIMO DELLA COLONNA CODICE_SCHEDA DELLA TABELLA ALLOGGIATI
Try
Dim conString = ConfigurationManager.ConnectionStrings("DB_GESTIONE_ALBERGHI")
Dim strConnString As String = conString.ConnectionString
Using db As New SqlConnection(strConnString)
db.Open()
Using sqlcmd As New SqlCommand("Max_Codice_Scheda_Alloggiati", db)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.AddWithValue("@codalbergo", SqlDbType.Int).Value = 71 'codice_albergo
contatore_righe = Convert.ToInt32(sqlcmd.ExecuteScalar())
End Using
End Using
Catch ex As SqlException
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "Attenzione: " & Chr(10) & ex.Message
ImpromptuHelper.ShowPrompt(prompt)
Return
Catch ex As Exception
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "Attenzione: " & Chr(10) & ex.Message
ImpromptuHelper.ShowPrompt(prompt)
Return
End Try
REM-----------------------------------------------------------------------------------
Dim myFile As HttpPostedFile
myFile = FileUpload1.PostedFile
Try
Using sr As New StreamReader(myFile.InputStream)
Dim line As String
Do
line = sr.ReadLine()
If Not (line Is Nothing) Then
codprov = CInt(line.Substring(0, 3)) : codcom = CInt(line.Substring(3, 3)) : codalb = CInt(line.Substring(6, 4))
codcat = CInt(line.Substring(10, 2)) : dataArrivo = CDate(line.Substring(12, 10))
If line.Substring(22, 10) = "0000/00/00" Then
partito = 0
Else
dataPartenza = CDate(line.Substring(22, 10))
partito = 1
End If
codnaz = CInt(line.Substring(32, 3)) : codprovMov = CInt(line.Substring(35, 3))
If line.Substring(38, 2) = " " Then
codgruppo = 1
Else
codgruppo = CInt(line.Substring(38, 2))
End If
nschedina = line.Substring(40, 5) : camdisp = CInt(line.Substring(45, 5)) : camocc = CInt(line.Substring(50, 5))
Dim flagQuery As Boolean = False
Dim flagDisp As Int16 = 0
Dim conString = ConfigurationManager.ConnectionStrings("DB_GESTIONE_ALBERGHI")
Dim strConnString As String = conString.ConnectionString
Using db As New SqlConnection(strConnString)
db.Open()
REM ESEGUO IL CONTROLLO SE LA SCHEDA IMPORTATA ESISTE GIA IN MODO TALE DA GESTIRE UNA INSERT O UPDATE, IN QUESTO SECONDO CASO SOLO DELLA DATA DI PARTENZA
Using sqlcmd As New SqlCommand("Search_Scheda_importata", db)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.AddWithValue("@codalbergo", SqlDbType.Int).Value = 71 'codice_albergo
sqlcmd.Parameters.AddWithValue("@nscheda", SqlDbType.Int).Value = nschedina
If IsDBNull(sqlcmd.ExecuteScalar()) = True Then
flagQuery = False
Else
If Convert.ToInt32(sqlcmd.ExecuteScalar()) = 1 Then
flagQuery = True
End If
End If
End Using
REM se il flagquery = False eseguo una insert perchè il record non esiste altrimenti update sul record esistente
If flagQuery = False Then
Using sqlcmd As New SqlCommand("Insert_Import_C59", db)
sqlcmd.CommandType = CommandType.StoredProcedure
contatore_righe += 1
sqlcmd.Parameters.AddWithValue("@CodiceAlbergo", SqlDbType.Int).Value = codalb
sqlcmd.Parameters.AddWithValue("@CodiceScheda", SqlDbType.Int).Value = contatore_righe
sqlcmd.Parameters.AddWithValue("@DataArrivo", SqlDbType.Date).Value = dataArrivo
sqlcmd.Parameters.AddWithValue("@DataPartenza", SqlDbType.Date).Value = dataPartenza
sqlcmd.Parameters.AddWithValue("@CodiceNazione", SqlDbType.Int).Value = codnaz
sqlcmd.Parameters.AddWithValue("@CodiceProvinciaMov", SqlDbType.Int).Value = codprovMov
sqlcmd.Parameters.AddWithValue("@Partito", SqlDbType.Int).Value = partito
sqlcmd.Parameters.AddWithValue("@CodiceGruppo", SqlDbType.Int).Value = codgruppo
sqlcmd.Parameters.AddWithValue("@NumeroSchedina", SqlDbType.VarChar).Value = nschedina
sqlcmd.Parameters.AddWithValue("@Ncamdisponibili", SqlDbType.Int).Value = camdisp
sqlcmd.Parameters.AddWithValue("@Ncamoccupate", SqlDbType.Int).Value = camocc
sqlcmd.ExecuteNonQuery()
End Using
Else
Using sqlcmd As New SqlCommand("Update_Import_C59", db)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.AddWithValue("@CodiceAlbergo", SqlDbType.Int).Value = codalb
sqlcmd.Parameters.AddWithValue("@NScheda", SqlDbType.VarChar).Value = nschedina
sqlcmd.Parameters.AddWithValue("@DataArrivo", SqlDbType.Date).Value = dataArrivo
sqlcmd.Parameters.AddWithValue("@DataPartenza", SqlDbType.Date).Value = dataPartenza
sqlcmd.Parameters.AddWithValue("@CodiceNazione", SqlDbType.Int).Value = codnaz
sqlcmd.Parameters.AddWithValue("@CodiceProvinciaMov", SqlDbType.Int).Value = codprovMov
sqlcmd.Parameters.AddWithValue("@Partito", SqlDbType.Int).Value = partito
sqlcmd.Parameters.AddWithValue("@CodiceGruppo", SqlDbType.Int).Value = codgruppo
sqlcmd.ExecuteNonQuery()
End Using
End If
REM ESEGUO IL CONTROLLO LA DISPONIBILITA E' GIA' PRESENTE O NO
Using sqlcmd As New SqlCommand("Search_disponibilita_import", db)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.AddWithValue("@codalbergo", SqlDbType.Int).Value = 71 'codice_albergo
sqlcmd.Parameters.AddWithValue("@data", SqlDbType.Date).Value = dataArrivo
If Convert.ToInt32(sqlcmd.ExecuteScalar()) = 1 Then
flagDisp = 1
Else
flagDisp = 0
End If
End Using
REM INSERIMENTO DISPONIBILITA
Using sqlcmd As New SqlCommand("Insert_disponibilita", db)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.AddWithValue("@CodiceAlbergo", SqlDbType.Int).Value = codalb
sqlcmd.Parameters.AddWithValue("@Data", SqlDbType.Date).Value = dataArrivo
sqlcmd.Parameters.AddWithValue("@flagdisp", SqlDbType.Bit).Value = flagDisp
sqlcmd.Parameters.AddWithValue("@Ncamdisponibili", SqlDbType.Int).Value = camdisp
sqlcmd.Parameters.AddWithValue("@Ncamoccupate", SqlDbType.Int).Value = camocc
sqlcmd.ExecuteNonQuery()
End Using
End Using
End If
Loop Until line Is Nothing
End Using
Catch ex As SqlException
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "Attenzione: " & Chr(10) & ex.Message
ImpromptuHelper.ShowPrompt(prompt)
Return
Catch ex As Exception
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "Attenzione: " & Chr(10) & ex.Message
ImpromptuHelper.ShowPrompt(prompt)
Return
Finally
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "<p>Importazione file avvenuta con successo</p>"
ImpromptuHelper.ShowPrompt(prompt)
End Try
Else
prompt = New ImpromptuPrompt
prompt.Options.Buttons.Add("Chiudi", "ok")
prompt.Options.Top = 350
prompt.Options.PromptSpeed = "slow"
prompt.Options.Show = "show"
prompt.Message = "<br>Attenzione!<br><p>Il File selezionato è vuoto</p>"
ImpromptuHelper.ShowPrompt(prompt)
End If