Range Bar Chart


Hola amigos, en esta ocasión quiero compartirles como hacer una gráfica de Gantt utilizando el MSChart de .Net Framework.

Tengo un sistema para un restaurante, en este sistema se permite hacer reservaciones. De tal manera que me vi en la necesidad de mostrar gráficamente las reservaciones del día actual.
Diseñé la siguiente tabla para almacenar las reservaciones:

Luego diseñé la siguiente pantalla para mostrar el listado de reservaciones:

El gráfico tiene la siguiente apariencia:


El código es el siguiente:

public void ReservationsList(ListView lvReservationsList, 
    DateTime startDate, DateTime endDate)
{
    OleDbConnection cnn = new OleDbConnection(Class.clsMain.CnnStr);
    try
    {
        lvReservationsList.Clear();
        lvReservationsList.View = View.Details;
        lvReservationsList.FullRowSelect = true;
        lvReservationsList.GridLines = true;
        lvReservationsList.HideSelection = false;
        lvReservationsList.Columns.Add("Mesa", 50, HorizontalAlignment.Center);
        lvReservationsList.Columns.Add("Nombre del cliente", 300, HorizontalAlignment.Left);
        lvReservationsList.Columns.Add("Vigencia", 250, HorizontalAlignment.Left);
        lvReservationsList.Columns.Add("Status", 150, HorizontalAlignment.Left);

        cnn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = cnn;
        cmd.CommandText = "select * from vReservationsList " +
        " where start_date >=@start_date and end_date<=@end_date";
        cmd.Parameters.Add("@start_date", OleDbType.Date).Value = 
            new DateTime(startDate.Year, startDate.Month, startDate.Day);
        cmd.Parameters.Add("@end_date", OleDbType.Date).Value = 
            new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
        int i = 0;
        OleDbDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            lvReservationsList.Items.Add(String.Format("{0}", dr["dining_table_name"]));
            lvReservationsList.Items[i].SubItems.Add(String.Format("{0}", dr["customer_name"]));
            lvReservationsList.Items[i].SubItems.Add(String.Format("De {0:dd/MM/yyyy HH:mm} a {1:dd/MM/yyyy HH:mm}", dr["start_date"], dr["start_date"]));
            if (Convert.ToBoolean(dr["canceled"]))
            {
                lvReservationsList.Items[i].SubItems.Add(String.Format("Cancelada"));
                lvReservationsList.Items[i].ForeColor = Color.Silver;
            }
            else
            {
                if (Convert.ToBoolean(dr["arrived"]))
                {
                    lvReservationsList.Items[i].SubItems.Add(String.Format("Llegó el cliente"));
                    lvReservationsList.Items[i].ForeColor = Color.Green;
                }
                else
                {
                    if (Convert.ToInt32(dr["minutes_to_expire"]) < 0)
                    {
                        lvReservationsList.Items[i].SubItems.Add(String.Format("Expira en {0} min.", 
                            Math.Abs(Convert.ToInt32(dr["minutes_to_expire"]))));

                    }
                    else
                    {
                        lvReservationsList.Items[i].SubItems.Add(String.Format("Expiró"));
                        lvReservationsList.Items[i].ForeColor = Color.Silver;
                    }
                }

            }
            lvReservationsList.Items[i].Tag = dr["id_reservation"];
            i++;
        }
        dr.Close();
    }
    catch (Exception ex) { throw ex; }
    finally { cnn.Close(); }
}
public void DrawChart(Chart chart)
{
    OleDbConnection cnn = new OleDbConnection(Class.clsMain.CnnStr);
    DateTime currentDate = DateTime.Now;
    try
    {
        cnn.Open();

        chart.Series["Reservaciones"].ChartType = SeriesChartType.RangeBar;
        chart.Series["Reservaciones"].XValueType = ChartValueType.DateTime;
        chart.Series["Reservaciones"].YValueType = ChartValueType.Auto;
        chart.Series["Reservaciones"].Points.Clear();

        chart.Series["TimeToExpire"].ChartType = SeriesChartType.RangeBar;
        chart.Series["TimeToExpire"].XValueType = ChartValueType.DateTime;
        chart.Series["TimeToExpire"].YValueType = ChartValueType.Auto;
        chart.Series["TimeToExpire"].Points.Clear();

        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = cnn;
        cmd.CommandText = "select * from dining_tables";
        int i = 0;
        int j = 0;
        OleDbDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            chart.Series["Reservaciones"].Points.AddXY(dr["id_dining_table"], null, null);
            chart.Series["Reservaciones"].Points[i].AxisLabel = dr["dining_table_name"].ToString();


            i++;
        }
        dr.Close();
        cmd.CommandText = "select * from vReservationsList "+
            " where start_date >=@start_date and end_date<=@end_date";
        cmd.Parameters.Add("@start_date", OleDbType.Date).Value = 
            new DateTime(currentDate.Year, currentDate.Month, currentDate.Day);
        cmd.Parameters.Add("@end_date", OleDbType.Date).Value = 
            new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 23, 59, 59);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            chart.Series["Reservaciones"].Points.AddXY(dr["id_dining_table"], 
                Convert.ToDateTime(dr["start_date"]), Convert.ToDateTime(dr["end_date"]));
            chart.Series["Reservaciones"].Points[i].AxisLabel = 
                dr["dining_table_name"].ToString();
            chart.Series["Reservaciones"].Points[i].ToolTip = 
                String.Format("Mesa: {1}\nCliente: {0}\nVigencia: {2:HH:mm} a {3:HH:mm}", 
                dr["customer_name"], dr["dining_table_name"], dr["start_date"], dr["end_date"]);
            if (Convert.ToBoolean(dr["canceled"]))
            {
                chart.Series["Reservaciones"].Points[i].Color = Color.Silver;
                chart.Series["Reservaciones"].Points[i].ToolTip = 
                    String.Format("Mesa: {1}\nCliente: {0}\nVigencia: {2:HH:mm} a {3:HH:mm}\nCANCELADA", 
                    dr["customer_name"], dr["dining_table_name"], dr["start_date"], dr["end_date"]);
            }
            else
            {
                if ((Convert.ToInt32(dr["minutes_to_expire"]) <= 0) 
                    && (Convert.ToDateTime(dr["start_date"]) <= currentDate))
                {
                    chart.Series["TimeToExpire"].Points.AddXY(dr["id_dining_table"], 
                        Convert.ToDateTime(dr["start_date"]), currentDate);
                    chart.Series["TimeToExpire"].Points[j].AxisLabel = 
                        dr["dining_table_name"].ToString();
                    chart.Series["TimeToExpire"].Points[j].ToolTip = 
                        String.Format("Expira en {0} minutos", 
                        Math.Abs(Convert.ToInt32(dr["minutes_to_expire"])));
                    j++;
                }
                if (Convert.ToDateTime(dr["end_date"]) < currentDate)
                {
                    chart.Series["Reservaciones"].Points[i].Color = Color.Silver;
                }
            }
            i++;
        }
        dr.Close();

        chart.ChartAreas["ChartArea1"].AxisY.Minimum = currentDate.AddHours(-12).ToOADate();
        chart.ChartAreas["ChartArea1"].AxisY.Maximum = currentDate.AddHours(12).ToOADate();

        chart.ChartAreas["ChartArea1"].AxisY.LabelStyle.Format = "HH:mm";

        chart.ChartAreas["ChartArea1"].AxisY.StripLines.Clear();
        //Dibujar linea de hora actual
        StripLine sl = new StripLine();
        sl.Interval = 0;
        sl.IntervalOffset = currentDate.ToOADate();
        sl.BorderWidth = 2;
        sl.BorderColor = System.Drawing.Color.Red;
        //sl.Text = String.Format("{0:HH:mm}", currentDate);
        sl.TextLineAlignment = StringAlignment.Far;

        chart.ChartAreas["ChartArea1"].AxisY.StripLines.Add(sl);

        //Dibujar linea de dia siguiente
        sl = new StripLine();
        sl.Interval = 0;
        if (currentDate < new DateTime(currentDate.Year, currentDate.Month, 
            currentDate.Day, 12, 0, 0))
        {
            sl.IntervalOffset = new DateTime(currentDate.Year, 
                currentDate.Month, currentDate.Day).ToOADate();
            sl.Text = String.Format("{0:dd/MM/yyyy}", new DateTime(currentDate.Year, 
                currentDate.Month, currentDate.Day).AddDays(-1));
            sl.TextAlignment = StringAlignment.Far;
        }
        else
        {
            sl.IntervalOffset = new DateTime(currentDate.Year, currentDate.Month, 
                currentDate.Day).AddDays(1).ToOADate();
            sl.Text = String.Format("{0:dd/MM/yyyy}", new DateTime(currentDate.Year, 
                currentDate.Month, currentDate.Day).AddDays(1));
            sl.TextAlignment = StringAlignment.Near;
        }
        sl.BorderWidth = 2;
        sl.BorderColor = System.Drawing.Color.Green;
        sl.TextLineAlignment = StringAlignment.Far;
        chart.ChartAreas["ChartArea1"].AxisY.StripLines.Add(sl);
        chart.Show();

    }
    catch (Exception ex) { throw ex; }
    finally { cnn.Close(); }
}

No hay comentarios:

Publicar un comentario