Torna al Thread
Public Class FrmVisualizzaBolle
Dim bolOnLoad As Boolean
Private Sub FrmVisualizzaBolle_Load(sender As Object, e As EventArgs) Handles MyBase.Load
bolOnLoad = True
RadioButton1.Checked = True
Periodo()
CaricaFornitori()
bolOnLoad = False
ComboBoxPeriodoBolla.Text = "Intero Anno"
ComboBoxFornitore.Text = "Tutti i Fornitori"
ComboBoxFornitore.Items.Insert(0, "Tutti i Fornitori")
End Sub
Private Sub CaricaFornitori()
'carico fornitori da mettere nella combo
Dim SQLConnectionString As String
Dim SQLConnection As New SqlConnection
Dim dataset1 As New DataSet
Dim cm As New SqlCommand
Dim Fornitori As String = "ClientiFornitori"
Dim da As New SqlDataAdapter
SQLConnectionString = My.Settings.WeaGestDBConnectionString & "xxxxx"
SQLConnection.ConnectionString = SQLConnectionString
SQLConnection.Open()
cm.CommandText = "SELECT ID_Cliente, R_Cliente FROM ClientiFornitori WHERE R_Tipo = 'F' ORDER BY R_Cliente"
da.SelectCommand = cm
da.SelectCommand.Connection = SQLConnection
da.Fill(dataset1, Fornitori)
ComboBoxFornitore.DataSource = dataset1.Tables(Fornitori)
ComboBoxFornitore.DisplayMember = "R_Cliente"
ComboBoxFornitore.ValueMember = "ID_Cliente"
SQLConnection.Close()
End Sub
Private Sub Periodo()
'carico tutti i mesi dell'anno
Dim intX As Integer
ComboBoxPeriodoBolla.Items.Insert(0, "Intero Anno")
For intX = 1 To 12
ComboBoxPeriodoBolla.Items.Insert(intX, UCase(MonthName(intX)))
Next
End Sub
Private Sub caricabolle()
Try
DataGridViewBolle.DataSource = Nothing
Dim SQLConnectionString As String
Dim SQLConnection As New SqlConnection
Dim dataset1 As New DataSet
SQLConnectionString = My.Settings.WeaGestDBConnectionString & "xxxxx"
SQLConnection.ConnectionString = SQLConnectionString
SQLConnection.Open()
Dim cm As New SqlCommand
Dim sql As String = "SELECT MovimentiMagazzino.ID_Movimenti,MovimentiMagazzino.R_NumeroDocumento,MovimentiMagazzino.R_DataDocumento" & _
",MovimentiMagazzino.R_AnnoDocumento, MovimentiMagazzino.ID_Fornitore,ClientiFornitori.R_Cliente,MovimentiMagazzino.R_NumeroDettagli" & _
",MovimentiMagazzino.R_ImponibileIva,MovimentiMagazzino.R_ImportoIva,MovimentiMagazzino.R_TotaleDocumento,MovimentiMagazzino.R_RifFattura" & _
",MovimentiMagazzino.R_DataFattura,MovimentiMagazzino.R_Saldata FROM MovimentiMagazzino INNER JOIN ClientiFornitori ON ClientiFornitori.ID_Cliente = MovimentiMagazzino.ID_Fornitore"
Dim where As String = ""
If ComboBoxPeriodoBolla.Text <> "Intero Anno" Then
If where.Length > 0 Then where = where & " AND "
where = where & "Month(R_DataDocumento) = " & (ComboBoxPeriodoBolla.SelectedIndex().ToString) & ""
End If
If ComboBoxFornitore.Text <> "Tutti i Fornitori" Then
If where.Length > 0 Then where = where & " AND "
where = where & " MovimentiMagazzino.ID_Fornitore = " & ComboBoxFornitore.ValueMember.ToString() & ""
End If
Dim query As String = sql
If where.Length > 0 Then query = sql & " WHERE " & where
cm.CommandText = query
Dim da As New SqlDataAdapter
da.SelectCommand = cm
da.SelectCommand.Connection = SQLConnection
Dim tablename2 As String = "MovimentiMagazzino"
da.Fill(dataset1, tablename2)
DataGridViewBolle.DataSource = dataset1
DataGridViewBolle.DataMember = tablename2
SQLConnection.Close()
Dim Font As New Font(DataGridViewBolle.DefaultCellStyle.Font.FontFamily, 10, FontStyle.Regular)
Dim FontTestata As New Font(DataGridViewBolle.DefaultCellStyle.Font.FontFamily, 8, FontStyle.Regular)
Me.SuspendLayout()
With Me.DataGridViewBolle
.RowHeadersVisible = False
.Columns("ID_Movimenti").Visible = False
.Columns("R_NumeroDocumento").HeaderText = ("Num Doc")
.Columns("R_NumeroDocumento").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_NumeroDocumento").DefaultCellStyle.ForeColor = Color.White
.Columns("R_NumeroDocumento").DefaultCellStyle.Font = Font
.Columns("R_NumeroDocumento").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_DataDocumento").HeaderText = ("Data Doc")
.Columns("R_DataDocumento").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_DataDocumento").DefaultCellStyle.ForeColor = Color.White
.Columns("R_DataDocumento").DefaultCellStyle.Font = Font
.Columns("R_DataDocumento").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
' .Columns("R_DataOperazione").Visible = False
.Columns("R_AnnoDocumento").HeaderText = ("Anno Doc")
.Columns("R_AnnoDocumento").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_AnnoDocumento").DefaultCellStyle.ForeColor = Color.White
.Columns("R_AnnoDocumento").DefaultCellStyle.Font = Font
.Columns("R_AnnoDocumento").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("ID_Fornitore").Visible = False
.Columns("R_Cliente").HeaderText = ("Fornitore")
.Columns("R_Cliente").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_Cliente").DefaultCellStyle.ForeColor = Color.White
.Columns("R_Cliente").DefaultCellStyle.Font = Font
.Columns("R_NumeroDettagli").HeaderText = ("Num Pezzi")
.Columns("R_NumeroDettagli").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_NumeroDettagli").DefaultCellStyle.ForeColor = Color.White
.Columns("R_NumeroDettagli").DefaultCellStyle.Font = Font
.Columns("R_NumeroDettagli").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
' .Columns("R_Note").Visible = False
.Columns("R_ImponibileIva").HeaderText = ("Imponibile €")
.Columns("R_ImponibileIva").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_ImponibileIva").DefaultCellStyle.ForeColor = Color.White
.Columns("R_ImponibileIva").DefaultCellStyle.Font = Font
.Columns("R_ImponibileIva").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_ImponibileIva").DefaultCellStyle.Format = Format("c")
.Columns("R_ImportoIva").HeaderText = ("Iva €")
.Columns("R_ImportoIva").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_ImportoIva").DefaultCellStyle.ForeColor = Color.White
.Columns("R_ImportoIva").DefaultCellStyle.Font = Font
.Columns("R_ImportoIva").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_ImportoIva").DefaultCellStyle.Format = "c"
.Columns("R_TotaleDocumento").HeaderText = ("Totale €")
.Columns("R_TotaleDocumento").DefaultCellStyle.BackColor = Color.Silver
.Columns("R_TotaleDocumento").DefaultCellStyle.ForeColor = Color.White
.Columns("R_TotaleDocumento").DefaultCellStyle.Font = Font
.Columns("R_TotaleDocumento").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_TotaleDocumento").DefaultCellStyle.Format = "c"
' .Columns("R_RifCliente2").Visible = False
'.Columns("R_Stagione").Visible = False
.Columns("R_RifFattura").HeaderText = ("Rif Fattura")
.Columns("R_RifFattura").DefaultCellStyle.BackColor = Color.LightYellow
.Columns("R_RifFattura").DefaultCellStyle.ForeColor = Color.White
.Columns("R_RifFattura").DefaultCellStyle.Font = Font
.Columns("R_RifFattura").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_DataFattura").HeaderText = ("Data Fattura")
.Columns("R_DataFattura").DefaultCellStyle.BackColor = Color.LightYellow
.Columns("R_DataFattura").DefaultCellStyle.ForeColor = Color.White
.Columns("R_DataFattura").DefaultCellStyle.Font = Font
.Columns("R_DataFattura").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.Columns("R_Saldata").HeaderText = ("Saldata")
.Columns("R_Saldata").DefaultCellStyle.BackColor = Color.LightYellow
.Columns("R_Saldata").DefaultCellStyle.ForeColor = Color.White
.Columns("R_Saldata").DefaultCellStyle.Font = Font
.Columns("R_Saldata").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.ColumnHeadersDefaultCellStyle.Font = FontTestata
End With
'calcola dati totali
Dim Numerobolle As Integer = DataGridViewBolle.RowCount
LabelTotaleDocumenti.Text = Numerobolle
'************************************
'sommo imponibile
Dim totimp As Integer
For Each x As DataGridViewRow In DataGridViewBolle.Rows
totimp = totimp + x.Cells(7).Value
Next
LabelImponibile.Text = FormatCurrency(totimp)
'*************************************
'sommo iva
Dim totiva As Integer
For Each x As DataGridViewRow In DataGridViewBolle.Rows
totiva = totiva + x.Cells(8).Value
Next
LabelIva.Text = FormatCurrency(totiva)
'*************************************
'sommo totale
Dim tot As Integer
For Each x As DataGridViewRow In DataGridViewBolle.Rows
tot = tot + x.Cells(9).Value
Next
LabelTotali.Text = FormatCurrency(tot)
'*************************************
Catch ex As Exception
MessageBox.Show("Errore Visualizzazione Bolle: " & ex.Message)
End Try
End Sub
Private Sub ComboBoxPeriodoBolla_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxPeriodoBolla.SelectedIndexChanged
If bolOnLoad Then Exit Sub
caricabolle()
End Sub
Private Sub ComboBoxFornitore_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxFornitore.SelectedIndexChanged
If bolOnLoad Then Exit Sub
caricabolle()
End Sub
End Class