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