Saltar al contenido

Reporte Productos más vendidos, VB.Net, RDLC, Report Viewer, Capas, POO y SQL Server

Introducción

Hola, en esta ocasión realizaremos un reporte de los productos más vendidos por rango de fechas, por ejemplo, los últimos 7 días, el mes actual, o fechas personalizadas. Usaremos la arquitectura en capas y programación orientada a objetos, con visual basic, Windows Form, el control report viewer, el diseñador de informes local RDLC y SQL Server.

Tutorial

Bien, empecemos a crear la base de datos y aplicación 🙂

Base de Datos

Tenemos 4 tablas relacionadas de una orden de venta  y los miles y miles de inserciones de datos. Puedes descargar el script completo de la base de datos desde el botón de abajo y así puedas realizar la práctica.

Crear Base de Datos Relacional (BDR)

Diagrama

Script

create database BikeStore

create table products
(
	product_id int identity (1,1) primary key,
	product_name varchar (200) NOT NULL,
	model_year smallint NOT NULL,
	price decimal (10, 2) NOT NULL
);
CREATE TABLE customers (
	customer_id INT IDENTITY (1, 1) PRIMARY KEY,
	first_name VARCHAR (255) NOT NULL,
	last_name VARCHAR (255) NOT NULL,
	phone VARCHAR (25),
	email VARCHAR (255) NOT NULL,
	street VARCHAR (255),
	city VARCHAR (50),
	state VARCHAR (25),
	zip_code VARCHAR (5)
);
create table orders
(
	order_id int identity (1,1) primary key,
	customer_id int not null,
	order_date date not null,
	constraint FK_Customer foreign key (customer_id) references customers(customer_id)
);

create table order_items
(
	order_item_id int identity (1,1) primary key,
	order_id int not null,
	product_id INT NOT NULL,
	quantity INT NOT NULL,
	price DECIMAL (10, 2) NOT NULL,
	discount DECIMAL (4, 2) NOT NULL DEFAULT 0,
	constraint fk_Order foreign key (order_id) references orders(order_id),
	constraint fk_Product foreign key (product_id) references products(product_id)
);
Descargar Base de Datos Completo
+ Inserción de registros

Consulta SQL/Procedimiento – Reporte de los Productos más vendidos por Rango de Fechas

En la siguiente consulta, mostraremos el ID del producto, el Nombre del producto, el Precio base unitario del producto, la cantidad vendida y el importe de las ganancias del producto.

Si deseas, puedes crear un procedimiento almacenado para mostrar la consulta ( ver pestaña 2) e invocar el procedimiento desde la capa de acceso a datos de la aplicación. 

select top 10
p.product_id,
p.product_name,
p.price as unitBasePrice,
sum(oi.quantity) as quantitySold,
sum((oi.price*oi.quantity)-oi.discount) as totalAmount
from 
orders o
inner join order_items oi on oi.order_id=o.order_id
inner join products p on p.product_id=oi.product_id
where o.order_date between '01-01-2018' and '30-12-2019 23:59:59'
group by 
p.product_id, p.product_name, p.price
order by quantitySold desc
create proc GetBestSellingProducts
@startDate Date,
@endDate Date
as
select top 10
p.product_id,
p.product_name,
p.price as unitBasePrice,
sum(oi.quantity) as quantitySold,
sum((oi.price*oi.quantity)-oi.discount) as totalAmount
from 
orders o
inner join order_items oi on oi.order_id=o.order_id
inner join products p on p.product_id=oi.product_id
where o.order_date between @startDate and @endDate
group by 
p.product_id, p.product_name, p.price
order by quantitySold desc
go

Aplicación

Una vez creado la base de datos, crear las 4 tablas relacionadas, insertar los registros de la orden de venta, haber probado la consulta anterior para obtener las ventas a detalle o haber creado el procedimiento almacenado, pasemos a estructurar la aplicación.

Crear Proyecto en Capas – Visual Studio

  • Creamos una solución en blanco de Visual Studio (Archivo-> Nuevo-> Proyecto-> Plantillas-> Otros tipos de proyectos -> Soluciones de Visual Studio-> Solución en Blanco) y asígnele el nombre que desee.
  • Agregamos un proyecto nuevo de tipo Aplicación de Windows Forms para la Capa de Presentación.
  • Agregamos un proyecto nuevo de tipo Biblioteca de Clases para la Capa de Dominio o Negocio.
  • Agregamos un proyecto nuevo de tipo Biblioteca de Clases para la Capa de Acceso a Datos o Persistencia.

Agregar referencias entre capas (Dependecias)

  • Agregamos las referencias entre capas según la arquitectura tradicional en capas, donde una capa superior sólo conoce la capa inmediatamente debajo de ella.
  • En Capa de Presentación: Referenciamos a la Capa de Dominio.
  • En Capa de Dominio: Referenciamos a la Capa de Acceso a Datos.

Una vez creado y agregar las referencias entre capas, pasemos a codificar las capas.

Capa de acceso a Datos

Clase Conexión (ConnectionSQL.vb)

  • Agreguemos una clase abstracta (MustInherit) para la conexión a SQL Server.
  • Importamos la librería System.Data.SqlClient.
  • Creamos un método protegido de tipo SqlConnection para obtener la conexión.
  • En el método creado, simplemente retornamos una instancia de conexión a sql, como parámetro, enviamos la cadena de conexión, servidor local, indicamos la base de datos, y nos conectamos mediante las credenciales de Windows.
Imports System.Data.SqlClient

Public MustInherit Class ConnectionSQL

    Protected Function GetConnection() As SqlConnection
        Return New SqlConnection("Server=(local); DataBase=BikeStore; integrated security=true")
    End Function

End Class

Clase Objeto de Acceso a Datos Orden (OrderDao.vb)

  • Agregamos otra clase publica para el objeto de acceso a datos de la entidad órdenes de venta.
  • Indicamos que la clase hereda de la clase conexión a SQL.
  • Importamos las librería System.DataSqlClient.
  • Creamos un método de tipo DataTable o List<Object> para obtener los productos mas vendidos por rango de fecha, para ello creamos 2 parámetros,una para la fecha de inicio y la otra para fecha final de la consulta.
  • Agregamos lo siguiente códigos para realizar la consulta.
Imports System.Data.SqlClient

Public Class OrderDao
    Inherits ConnectionSQL

    Public Function GetBestSellingProducts(fromDate As DateTime, toDate As DateTime) As DataTable
        Using connection = GetConnection()
            connection.Open()
            Using command = New SqlCommand()
                command.Connection = connection
                command.CommandText = "GetBestSellingProducts"
                command.Parameters.AddWithValue("@startDate", fromDate)
                command.Parameters.AddWithValue("@endDate", toDate)
                command.CommandType = CommandType.StoredProcedure
                Dim reader = command.ExecuteReader()
                Dim table = New DataTable()
                table.Load(reader)
                reader.Dispose()
                Return table
            End Using
        End Using
    End Function

End Class
Imports System.Data.SqlClient

Public Class OrderDao
    Inherits ConnectionSQL
    Public Function GetBestSellingProducts(fromDate As DateTime, toDate As DateTime) As DataTable
        Using connection = GetConnection()
            connection.Open()
            Using command = New SqlCommand()
                command.Connection = connection
                command.CommandText = "select top(10) oi.product_id, p.product_name,
                                        p.price as unitBasePrice,
                                        sum(quantity) as quantity,
                                        sum((oi.price*oi.quantity)-oi.discount)as amount
                                        from
                                        order_items oi
                                        inner join orders o on o.order_id=oi.order_id
                                        inner join products p on p.product_id=oi.product_id
                                        where o.order_date between @startDate and @endDate
                                        group by 
                                        oi.product_id, p.product_name,p.price
                                        order by quantity desc"
                command.Parameters.Add("@startDate", SqlDbType.Date).Value = fromDate
                command.Parameters.Add("@endDate", SqlDbType.Date).Value = toDate
                command.CommandType = CommandType.Text
                Dim reader = command.ExecuteReader()
                Dim table = New DataTable()
                table.Load(reader)
                reader.Dispose()
                Return table
            End Using
        End Using
    End Function
End Class

Capa de Dominio – Negocio

Ahora codificaremos la capa de dominio o negocio, agregamos las siguientes clases/Objetos para guardar el listado de los productos mas vendidos:

Clase Mejores productos (BestProducts.vb)

Public Class BestProducts
    Public Property ProductId As Integer
    Public Property ProductName As String
    Public Property Price As Double
    Public Property Quantity As Integer
    Public Property Amount As Double
End Class

Clase Reporte de Ventas (SalesReport.vb)

  • Agregamos una clase para el reporte de ventas.
  • Importamos la capa de acceso a datos
  • Declaramos los atributos y propiedades necesarios para el reporte.
  • Declaramos una lista de objetos de tipo mejores productos (BestProducts.vb) para almacenar la consulta de la base de datos.
  • Finalmente creamos un método para crear el reporte e inicializar los objetos.
Imports DataAccess

Public Class SalesReport
    'Attributes
    Private _reportDate As DateTime
    Private _startDate As DateTime
    Private _endDAte As DateTime
    Private _listBestProducts As List(Of BestProducts)
    Private _total As Double

    'Propiertes
    Public Property ReportDate As Date
        Get
            Return _reportDate
        End Get
        Private Set(value As Date)
            _reportDate = value
        End Set
    End Property

    Public Property StartDate As Date
        Get
            Return _startDate
        End Get
        Private Set(value As Date)
            _startDate = value
        End Set
    End Property

    Public Property EndDAte As Date
        Get
            Return _endDAte
        End Get
        Private Set(value As Date)
            _endDAte = value
        End Set
    End Property

    Public Property ListBestProducts As List(Of BestProducts)
        Get
            Return _listBestProducts
        End Get
        Private Set(value As List(Of BestProducts))
            _listBestProducts = value
        End Set
    End Property

    Public Property Total As Double
        Get
            Return _total
        End Get
        Private Set(value As Double)
            _total = value
        End Set
    End Property
    'Constructors

    'Methods
    Public Sub GetBestSellingProductsReport(fromDate As DateTime, toDate As DateTime)
        'implement dates
        ReportDate = Date.Now()
        StartDate = fromDate
        EndDAte = toDate
        'Create list best selling products
        ListBestProducts = New List(Of BestProducts)()
        Dim orderDao = New OrderDao()
        Dim resultTable = orderDao.GetBestSellingProducts(fromDate, toDate)

        For Each row As DataRow In resultTable.Rows
            Dim bestProductModel = New BestProducts() With
                {
            .ProductId = Convert.ToInt32(row(0)),
            .ProductName = Convert.ToString(row(1)),
            .Price = Convert.ToDouble(row(2)),
            .Quantity = Convert.ToInt32(row(3)),
            .Amount = Convert.ToDouble(row(4))
            }
            ListBestProducts.Add(bestProductModel)
            Total += Convert.ToDouble(row(4))
        Next

    End Sub

End Class

Capa de Presentación

Una vez terminado de codificar la capa de dominio, ahora agregaremos los conjuntos de datos para el reporte, crearemos el archivo de reporte y agregaremos un formulario para inicializar el visor de reporte y crear los botones con un rango de fechas en específicos o personalizado.

Generador de Reportes

Puedes utilizar cualquier generador de reportes, ya sea Crystal Reports, Microsoft RDLC Report Designer, Component One, DevExpress, Jasper Reports o cualquier otro, ya que simplemente lo usaremos como diseñador y visor de reportes, y la capa de dominio ya dispone de todos los datos listos para ser mostrados.

En este tutorial utilizaremos Reporting Services con modo de procesamiento local (RDLC) y mostraremos el informe mediante el control Report Viewer. A partir de Visual Studio 2017, este control y los servicios de reporte local ya no están integrados en la instalación por defecto de Visual Studio, tienes que instalarlo desde Nuget o Extensiones de Visual Studio. Puede ver este tutorial para realizar la instalacion.

Archivo de Informes de Ventas (SalesReport.rdlc)

  • Agregamos el archivo / diseñador de reportes local RDLC en blanco, si deseas puedes usar el asistente de informes.
  • Compilamos la capa de presentación para cargar las referencias.
  • Ajustamos el tamaño de pagina del informe (en este caso A4->21cm x 29.7 cm) y el cuerpo del informe (21cm x 15cm, el alto del cuerpo debe ser ajustado a los componentes del informe).
  • Agregamos los objetos (clases) Reporte de ventas (salesReport.vb) y los productos mas vendidos (BestProducts.vb) al conjunto de datos del reporte local.
  • Finalmente diseñamos el informe de la siguiente manera y en ello, agregamos los campos a mostrar (Campos de nuestros objetos de reporte).

Diseño de Formulario

  • Una vez terminado de diseñar el reporte local, agregamos los botones con fechas especificas para el reporte y 2 controles selector de fecha (DateTimePicker) para el reporte de fechas personalizados.
  • Agregamos el control Report Viewer y acoplamos a todo el contenedor.
  • En el visor de reporte, elegimos el archivo del reporte a mostrar, en este caso, el reporte de los productos mas vendidos (BestProductsReport.rdlc)
  • Una vez elegido el reporte, en la parte baja se creará los 2 fuentes enlace a datos para los 2 objetos del conjunto de datos del informe (SalesReportDt y BestProductsDT).

Código de Formulario

  • Importamos la capa de dominio o negocio.
  • Creamos el método cargar reporte de los productos más vendidos (LoadBestProductsReport), con 2 parámetros para la fecha de inicio y final del reporte.
  • En el método, instanciamos a la clase reporte de venta de la capa de dominio e invocamos el método obtener reporte de los productos más vendidos.
  • Inicializamos la fuente de datos del enlace de datos del visor de reporte (SalesReportBindingSource, BestProductsBindingSource) con el objeto reporte y su propiedad (salesOrderModel, salesOrderModel.ListBestProducts).
  • Finalmente invocamos el método anterior desde los botones con los respectivos rango de fechas.
Imports Domain

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    End Sub

    Private Sub LoadBestProductsReport(startDate As DateTime, endDate As DateTime)
        Dim salesOrderModel = New SalesReport()
        salesOrderModel.GetBestSellingProductsReport(startDate, endDate)
        SalesReportBindingSource.DataSource = salesOrderModel
        BestProductsBindingSource.DataSource = salesOrderModel.ListBestProducts
        Me.ReportViewer1.RefreshReport()
    End Sub

    Private Sub btnToday_Click(sender As Object, e As EventArgs) Handles btnToday.Click
        Dim fromDate = DateTime.Today
        Dim toDate = DateTime.Now
        LoadBestProductsReport(fromDate, toDate)
    End Sub

    Private Sub btnLast7Days_Click(sender As Object, e As EventArgs) Handles btnLast7Days.Click
        Dim fromDate = DateTime.Today.AddDays(-7)
        Dim toDate = DateTime.Now
        LoadBestProductsReport(fromDate, toDate)
    End Sub

    Private Sub btnThisMonth_Click(sender As Object, e As EventArgs) Handles btnThisMonth.Click
        Dim fromDate = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
        Dim toDate = DateTime.Now
        LoadBestProductsReport(fromDate, toDate)
    End Sub

    Private Sub btnLast30Days_Click(sender As Object, e As EventArgs) Handles btnLast30Days.Click
        Dim fromDate = DateTime.Today.AddDays(-30)
        Dim toDate = DateTime.Now
        LoadBestProductsReport(fromDate, toDate)
    End Sub

    Private Sub btnThisYear_Click(sender As Object, e As EventArgs) Handles btnThisYear.Click
        Dim fromDate = New DateTime(DateTime.Now.Year, 1, 1)
        Dim toDate = DateTime.Now
        LoadBestProductsReport(fromDate, toDate)
    End Sub

    Private Sub btnApply_Click(sender As Object, e As EventArgs) Handles btnApply.Click
        Dim fromDate = DateTimePickerFromDate.Value
        Dim toDate = DateTimePickerToDate.Value
        LoadBestProductsReport(fromDate, New DateTime(toDate.Year, toDate.Month, toDate.Day, 23, 59, 59))
    End Sub
End Class

Y eso es todo 🙂

Ver Video Tutorial

Descargas

Descargar Base de Datos Orden de Venta + 7K Registros

Los comentarios están cerrados.