Crear un Tablero de Datos por Rango de Fechas, por Este Mes, Hoy, Últimos 7 Días, Últimos 30 Días o una Fecha Personalizada, utilizando lenguaje de programación C#, base de datos SQL Server, Windows Forms y Programación Orientada a Objetos (POO).
Base de datos
Descarga del script completo de la base de datos en el siguiente botón:
Clase Conexión a SQL
public abstract class DbConnection
{
private readonly string connectionString;
public DbConnection()
{
connectionString = "Server=(local); DataBase=NorthwindStore; Integrated Security=true";
}
protected SqlConnection GetConnection()
{
return new SqlConnection(connectionString);
}
}
Clase Modelo Dashboard
public struct RevenueByDate
{
public string Date { get; set; }
public decimal TotalAmount { get; set; }
}
public class Dashboard : DbConnection
{
//Fields & Properties
private DateTime startDate;
private DateTime endDate;
private int numberDays;
public int NumCustomers { get; private set; }
public int NumSuppliers { get; private set; }
public int NumProducts { get; private set; }
public List<KeyValuePair<string, int>> TopProductsList { get; private set; }
public List<KeyValuePair<string, int>> UnderstockList { get; private set; }
public List<RevenueByDate> GrossRevenueList { get; private set; }
public int NumOrders { get; set; }
public decimal TotalRevenue { get; set; }
public decimal TotalProfit { get; set; }
//Constructor
public Dashboard()
{
}
//Private methods
private void GetNumberItems()
{
using (var connection = GetConnection())
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
//Get Total Number of Customers
command.CommandText = "select count(id) from Customer";
NumCustomers = (int)command.ExecuteScalar();
//Get Total Number of Suppliers
command.CommandText = "select count(id) from Supplier";
NumSuppliers = (int)command.ExecuteScalar();
//Get Total Number of Products
command.CommandText = "select count(id) from Product";
NumProducts = (int)command.ExecuteScalar();
//Get Total Number of Orders
command.CommandText = @"select count(id) from [Order]" +
"where OrderDate between @fromDate and @toDate";
command.Parameters.Add("@fromDate", System.Data.SqlDbType.DateTime).Value = startDate;
command.Parameters.Add("@toDate", System.Data.SqlDbType.DateTime).Value = endDate;
NumOrders = (int)command.ExecuteScalar();
}
}
}
private void GetProductAnalisys()
{
TopProductsList = new List<KeyValuePair<string, int>>();
UnderstockList = new List<KeyValuePair<string, int>>();
using (var connection = GetConnection())
{
connection.Open();
using (var command = new SqlCommand())
{
SqlDataReader reader;
command.Connection = connection;
//Get Top 5 products
command.CommandText = @"select top 5 P.ProductName, sum(OrderItem.Quantity) as Q
from OrderItem
inner join Product P on P.Id = OrderItem.ProductId
inner
join [Order] O on O.Id = OrderItem.OrderId
where OrderDate between @fromDate and @toDate
group by P.ProductName
order by Q desc ";
command.Parameters.Add("@fromDate", System.Data.SqlDbType.DateTime).Value = startDate;
command.Parameters.Add("@toDate", System.Data.SqlDbType.DateTime).Value = endDate;
reader = command.ExecuteReader();
while (reader.Read())
{
TopProductsList.Add(
new KeyValuePair<string, int>(reader[0].ToString(), (int)reader[1]));
}
reader.Close();
//Get Understock
command.CommandText = @"select ProductName, Stock
from Product
where Stock <= 6 and IsDiscontinued = 0";
reader = command.ExecuteReader();
while (reader.Read())
{
UnderstockList.Add(
new KeyValuePair<string, int>(reader[0].ToString(), (int)reader[1]));
}
reader.Close();
}
}
}
private void GetOrderAnalisys()
{
GrossRevenueList = new List<RevenueByDate>();
TotalProfit = 0;
TotalRevenue = 0;
using (var connection = GetConnection())
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = @"select OrderDate, sum(TotalAmount)
from[Order]
where OrderDate between @fromDate and @toDate
group by OrderDate";
command.Parameters.Add("@fromDate", System.Data.SqlDbType.DateTime).Value = startDate;
command.Parameters.Add("@toDate", System.Data.SqlDbType.DateTime).Value = endDate;
var reader = command.ExecuteReader();
var resultTable = new List<KeyValuePair<DateTime, decimal>>();
while (reader.Read())
{
resultTable.Add(
new KeyValuePair<DateTime, decimal>((DateTime)reader[0], (decimal)reader[1])
);
TotalRevenue += (decimal)reader[1];
}
TotalProfit = TotalRevenue * 0.2m;//20%
reader.Close();
//Group by Hours
if (numberDays <= 1)
{
GrossRevenueList = (from orderList in resultTable
group orderList by orderList.Key.ToString("hh tt")
into order
select new RevenueByDate
{
Date = order.Key,
TotalAmount = order.Sum(amount => amount.Value)
}).ToList();
}
//Group by Days
else if (numberDays <= 30)
{
GrossRevenueList = (from orderList in resultTable
group orderList by orderList.Key.ToString("dd MMM")
into order
select new RevenueByDate
{
Date = order.Key,
TotalAmount = order.Sum(amount => amount.Value)
}).ToList();
}
//Group by Weeks
else if (numberDays <= 92)
{
GrossRevenueList = (from orderList in resultTable
group orderList by CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(
orderList.Key, CalendarWeekRule.FirstDay, DayOfWeek.Monday)
into order
select new RevenueByDate
{
Date = "Week " + order.Key.ToString(),
TotalAmount = order.Sum(amount => amount.Value)
}).ToList();
}
//Group by Months
else if (numberDays <= (365 * 2))
{
bool isYear = numberDays <= 365 ? true : false;
GrossRevenueList = (from orderList in resultTable
group orderList by orderList.Key.ToString("MMM yyyy")
into order
select new RevenueByDate
{
Date = isYear ? order.Key.Substring(0, order.Key.IndexOf(" ")) : order.Key,
TotalAmount = order.Sum(amount => amount.Value)
}).ToList();
}
//Group by Years
else
{
GrossRevenueList = (from orderList in resultTable
group orderList by orderList.Key.ToString("yyyy")
into order
select new RevenueByDate
{
Date = order.Key,
TotalAmount = order.Sum(amount => amount.Value)
}).ToList();
}
}
}
}
//Public methods
public bool LoadData(DateTime startDate, DateTime endDate)
{
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day,
endDate.Hour, endDate.Minute, 59);
if (startDate != this.startDate || endDate != this.endDate)
{
this.startDate = startDate;
this.endDate = endDate;
this.numberDays = (endDate - startDate).Days;
GetNumberItems();
GetProductAnalisys();
GetOrderAnalisys();
Console.WriteLine("Refreshed data: {0} - {1}", startDate.ToString(), endDate.ToString());
return true;
}
else
{
Console.WriteLine("Data not refreshed, same query: {0} - {1}", startDate.ToString(), endDate.ToString());
return false;
}
}
}
Diseño del Formulario
Código del Formulario
public partial class Form1 : Form
{
//Fields
private Dashboard model;
//Constructor
public Form1()
{
InitializeComponent();
//Default - Last 7 days
dtpStartDate.Value = DateTime.Today.AddDays(-7);
dtpEndDate.Value = DateTime.Now;
btnLast7Days.Select();
model = new Dashboard();
LoadData();
}
//Private methods
private void LoadData()
{
var refreshData = model.LoadData(dtpStartDate.Value, dtpEndDate.Value);
if (refreshData == true)
{
lblNumOrders.Text = model.NumOrders.ToString();
lblTotalRevenue.Text = "$" + model.TotalRevenue.ToString();
lblTotalProfit.Text = "$" + model.TotalProfit.ToString();
lblNumCustomers.Text = model.NumCustomers.ToString();
lblNumSuppliers.Text = model.NumSuppliers.ToString();
lblNumProducts.Text = model.NumProducts.ToString();
chartGrossRevenue.DataSource = model.GrossRevenueList;
chartGrossRevenue.Series[0].XValueMember = "Date";
chartGrossRevenue.Series[0].YValueMembers = "TotalAmount";
chartGrossRevenue.DataBind();
chartTopProducts.DataSource = model.TopProductsList;
chartTopProducts.Series[0].XValueMember = "Key";
chartTopProducts.Series[0].YValueMembers = "Value";
chartTopProducts.DataBind();
dgvUnderstock.DataSource = model.UnderstockList;
dgvUnderstock.Columns[0].HeaderText = "Item";
dgvUnderstock.Columns[1].HeaderText = "Units";
Console.WriteLine("Loaded view :)");
}
else Console.WriteLine("View not loaded, same query");
}
private void DisableCustomDates()
{
dtpStartDate.Enabled = false;
dtpEndDate.Enabled = false;
btnOkCustomDate.Visible = false;
}
//Event methods
private void btnToday_Click(object sender, EventArgs e)
{
dtpStartDate.Value = DateTime.Today;
dtpEndDate.Value = DateTime.Now;
LoadData();
DisableCustomDates();
}
private void btnLast7Days_Click(object sender, EventArgs e)
{
dtpStartDate.Value = DateTime.Today.AddDays(-7);
dtpEndDate.Value = DateTime.Now;
LoadData();
DisableCustomDates();
}
private void btnLast30Days_Click(object sender, EventArgs e)
{
dtpStartDate.Value = DateTime.Today.AddDays(-30);
dtpEndDate.Value = DateTime.Now;
LoadData();
DisableCustomDates();
}
private void btnThisMonth_Click(object sender, EventArgs e)
{
dtpStartDate.Value = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
dtpEndDate.Value = DateTime.Now;
LoadData();
DisableCustomDates();
}
private void btnCustomDate_Click(object sender, EventArgs e)
{
dtpStartDate.Enabled = true;
dtpEndDate.Enabled = true;
btnOkCustomDate.Visible = true;
}
private void btnOkCustomDate_Click(object sender, EventArgs e)
{
LoadData();
}
}
Descargas
Video Tutorial – Parte 1
Inglés
Español
Video Tutorial – Parte 2/ Personalización
Inglés
Español
No disponible