Reports, Doubt with sums and dates

Question

Reports, Doubt with sums and dates

Navigation

#1 by (0 votes)

0

For a few days I have had a small problem where the client requested us to change a report that we have in our system written in VB.NET Basically, filter my DataGridView, and through it I'm mounting the report,

With this code I make the filter.

 Dim cn As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    Dim Da As New OleDb.OleDbDataAdapter
    Dim Dt As New DataTable
    Dim dtinicio As String
    Dim dtfim As String
    dtinicio = mskdtcomeco.Text
    dtfim = mskdtfinal.Text
    datainicio3 = mskdtcomeco.Text
    datafim3 = mskdtfinal.Text

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Firebird.mdb"
    cn.Open()
    mskdtcomeco.Text = CDate(mskdtcomeco.Text).ToString("dd/MM/yyyy")
    mskdtfinal.Text = CDate(mskdtfinal.Text).ToString("dd/MM/yyyy")
    Try
        With cmd
            .CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
            cmd.Parameters.AddWithValue("@dtFim", dtfim)
            .CommandText = "SELECT * FROM OS WHERE data between @dtInicio And @dtFim  And NOMEFUNCIONARIO = '" & ComboBox2.Text & "' ORDER BY data"
            .Connection = cn
        End With

        With Da
            .SelectCommand = cmd
            Dt = New DataTable
            .Fill(Dt)
            OSDataGridViewFuncionario.DataSource = Dt

        End With

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    cmd.Parameters.Clear()
    Dim linha As DataGridViewRow

    Dim valor As Double
    Dim valor2 As Double
    Dim valor3 As Double

    For Each linha In OSDataGridViewFuncionario.Rows
        valor = valor + linha.Cells(5).Value ' 
        valor2 = valor2 + linha.Cells(6).Value '
        valor3 = valor3 + linha.Cells(4).Value
    Next
    lbltotfunc.Text = FormatCurrency(valor)
    LBLSALAO.Text = FormatCurrency(valor2)
    lbltotal.Text = FormatCurrency(valoe3)

After the results listed in the DG, the impression comes.

    Private Sub pdFuncionario_PrintPage(sender As Object, e As Printing.PrintPageEventArgs) Handles pdFuncionario.PrintPage
    Dim g As Graphics = e.Graphics
    Dim Cliente As String = String.Empty
    Dim funcionario As String = String.Empty
    Dim servico As String = String.Empty
    Dim totservico As String = String.Empty
    Dim remum As String = String.Empty
    Dim data As String = String.Empty
    Dim PosicaoLinha As Integer = 40
    Dim LinhasPorPagina As Byte = 45
    'Definições da pagina
    g.PageUnit = GraphicsUnit.Millimeter
    'Desenhar folha
    Dim Titulo As New Font("Times New Roman", 8, FontStyle.Regular Or FontStyle.Bold, GraphicsUnit.Millimeter)

    g.DrawString("Relatorio de Funcionario ", Titulo, Brushes.Black, 65, 7)


    'Ler datagridview
    For L = 0 To LinhasPorPagina - 1
        If LinhaAtual = OSDataGridViewFuncionario.Rows.Count Then Continue For
        For i As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 9

            If Not OSDataGridViewFuncionario.Item(i, LinhaAtual).Value = Nothing Then
                Cliente = OSDataGridViewFuncionario.Item(i, LinhaAtual).Value

            End If

        Next
        For o As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 8
            If Not OSDataGridViewFuncionario.Item(o, LinhaAtual).Value = Nothing Then
                funcionario = OSDataGridViewFuncionario.Item(o, LinhaAtual).Value

            End If
        Next
        For u As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 6
            If Not OSDataGridViewFuncionario.Item(u, LinhaAtual).Value = Nothing Then
                servico = OSDataGridViewFuncionario.Item(u, LinhaAtual).Value

            End If
        Next
        For a As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 4
            If Not OSDataGridViewFuncionario.Item(a, LinhaAtual).Value = Nothing Then
                totservico = OSDataGridViewFuncionario.Item(a, LinhaAtual).Value

            End If
        Next

        For c As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 2

            If Not OSDataGridViewFuncionario.Item(c, LinhaAtual).Value = Nothing Then

                data = OSDataGridViewFuncionario.Item(c, LinhaAtual).Value


            End If

        Next



        'Desenhar conteudo na página
        e.Graphics.DrawString("Cliente", New Font("Verdana", 6), Brushes.Black, 10, 30)
        e.Graphics.DrawString(Cliente, New Font("Verdana", 6), Brushes.Black, 9, PosicaoLinha)
        e.Graphics.DrawString("Funcionario", New Font("Verdana", 6), Brushes.Black, 55, 30)
        e.Graphics.DrawString(funcionario, New Font("Verdana", 6), Brushes.Black, 55, PosicaoLinha)
        e.Graphics.DrawString("Serviço", New Font("Verdana", 6), Brushes.Black, 90, 30)
        e.Graphics.DrawString(servico, New Font("Verdana", 6), Brushes.Black, 90, PosicaoLinha)
        e.Graphics.DrawString("Valor Recebido", New Font("Verdana", 6), Brushes.Black, 130, 30)
        e.Graphics.DrawString("R$ " & totservico, New Font("Verdana", 6), Brushes.Black, 134, PosicaoLinha)
        e.Graphics.DrawString("Data", New Font("Verdana", 6), Brushes.Black, 190, 30)
        e.Graphics.DrawString(data, New Font("Verdana", 6), Brushes.Black, 180, PosicaoLinha)


        PosicaoLinha += 5
        LinhaAtual += 1
        Cliente = String.Empty
        funcionario = String.Empty
        servico = String.Empty
        totservico = String.Empty
        remum = String.Empty
        data = String.Empty

    Next
    'Se linha atual for igual á linhas devidas por página então muda de página
    If LinhaAtual < OSDataGridViewFuncionario.Rows.Count Then
        e.HasMorePages = True
    Else
        e.HasMorePages = False
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("______________________________________________________________________________________________________________________________", New Font("Verdana", 8), Brushes.Black, 0, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("Data Inicial do Relatorio: " & datainicio3 & "                                   Data Final do Relatorio  : " & datafim3, New Font("Verdana", 8), Brushes.Black, 20, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 2
        e.Graphics.DrawString("__________________________________________________________________________________________________________________________________", New Font("Verdana", 8), Brushes.Black, 0, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("Funcionario: " & ComboBox2.Text, New Font("Verdana", 12), Brushes.Black, 20, PosicaoLinha)
        e.Graphics.DrawString("Valor a Receber: ", New Font("Verdana", 12), Brushes.Black, 135, PosicaoLinha)
        e.Graphics.DrawString(lbltotfunc.Text, New Font("Verdana", 12), Brushes.Black, 175, PosicaoLinha)
    End If
End Sub

So when the client does a search with a range of dates Ex: 10/05/2015 - 05/20/2015, the report only shows the total values,

I would like to know if there would be any way, to create a total for each day in this report, that when I pass the line to next date add the daily values so that it can not be filtering day by day ... I thank you for your attention and cooperation. Thank you.

vb.net ms-access

asked by anonymous 08.06.2015 / 14:02

1 answer

Botao Like customized List data in a List View

score 0 · Accepted Answer

First of all: Never ever never construct SQL queries concatenating strings unless you know exactly what you are doing. Do this:

cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
cmd.Parameters.AddWithValue("@dtFim", dtfim)
cmd.Parameters.AddWithValue("@nomeFuncionario", ComboBox2.Text)
.CommandText = "SELECT * FROM OS WHERE data between @dtInicio And @dtFim And NOMEFUNCIONARIO = @nomeFuncionario ORDER BY data"

To generate the added value per day, you can change the SQL query to something type (note the GROUP BY)

SELECT data, SUM(coluna_valores) AS total
FROM OS
WHERE data between @dtInicio And @dtFim AND
      NOMEFUNCIONARIO = @nomeFuncionario
GROUP BY data
ORDER BY data

This will give you an answer like

    data    | total
------------+-------
 2015-06-05 | 3500
 2015-06-08 | 1700
 2015-06-09 | 700

You would have to somehow integrate it into your current report or use it to generate a new report.