only for RuBoard - do not distribute or recompile |
The fourth and final report for this project is a scatter plot. A scatter plot shows the relationship between two sets of numbers as a series of points. Plotting two datasets is relatively straightforward; the difficult part of this application is making it dynamic so that the user has a choice of multiple values, any of which can be plotted along either axis. Also, the axes are dynamic because each value has a different maximum value. Listing 12.10 is the C code that creates the scatter plot application.
#include <gtk/gtk.h> #include <mysql.h> /* stdlib.h is needed for the atoi/atof call. */ #include <stdlib.h> #include "support.h" #define SERVER "einstein" #define LOGIN "com_user" #define PASS "syL0U812" #define DB "commish" #define HORIZONTAL_LINE 550 //The locations of the horizontal and #define VERTICAL_LINE 40 //vertical axis lines, in pixels from //the top and left, respectively. GtkWidget *frm_scatter; MYSQL *conx; MYSQL_RES *result_set; MYSQL_ROW row; gchar *sql; gint vertical_index; //An integer representing which column gint horizontal_index; //in the mysql result set to plot along //each axis. gint max_vertical = 0; //Variables to hold the max and min values gint max_horizontal = 0; //for each axis. /* The next two variables are the scale multipliers for plotting the * various points. For example, if the maximum value in a dataset * is 600 and the graph has a distance of 540 pixels in which to * represent the value of 600, the multiplier in that case would be .9. * That is, 600 x .9 = 540. */ gdouble vertical_scale_multiplier = 1.0; gdouble horizontal_scale_multiplier = 1.0; void set_indices(); gboolean load_data(); GdkGC *get_gc(gchar *in_color) { /* This is the same get_gc() from the bar/line report. * It returns a graphics context in the color specified * by the parameter in_color . */ GdkGC *gc; GdkColor my_color; gc = gdk_gc_new(GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window); if (!gdk_color_parse(in_color, &my_color)) { g_print("Unable to parse %s.\n", in_color); gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "Unable to parse color.\n"); return 0L; } if (!gdk_colormap_alloc_color(gdk_colormap_get_system(), &my_color, FALSE, TRUE)) { g_print("Unable to allocate colormap for %s.\n", in_color); gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "Unable to allocate colormap.\n"); return 0L; } gdk_gc_set_foreground(gc, &my_color); return gc; } void draw_axis_and_scales() { /* Also similar to the previous examples, this function * plots the horizontal and vertical axis and maximum * values. */ GdkGC *gc; gc = get_gc("Black"); gdk_draw_string( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->style->font, gc, 25, HORIZONTAL_LINE, "0"); gdk_draw_string( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->style->font, gc, 45, HORIZONTAL_LINE + 15, "0"); gdk_draw_line( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, gc, VERTICAL_LINE, 10, VERTICAL_LINE, HORIZONTAL_LINE); gdk_draw_line( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, gc, VERTICAL_LINE, HORIZONTAL_LINE, 875, HORIZONTAL_LINE); } void plot_point(gint from_left, gint from_bottom, gchar *legend) { /* This function plots a single point and the data point * identifier contained in the "legend" parameter. */ gdk_draw_rectangle( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, get_gc("Black"), FALSE, from_left, from_bottom - 3, 3, 3); gdk_draw_string( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->style->font, get_gc("Black"), from_left + 4, from_bottom + 2, legend); } void set_maximums() { /* This function will scan the result set returned by * mysql for the maximum values. These values will then * be used to determine the maximums for the graph axis and * the multiplication factors used to plot the points. * * Reset result_set to point to the first row of the data; * 0 is the first row in a zero-based structure. */ mysql_data_seek(result_set, 0); /* Iterate through the values to find the maximum; that * value will be used to set the vertical axis scale. */ while (( row = mysql_fetch_row (result_set)) != 0) { if (atoi (row[vertical_index]) > max_vertical) { max_vertical = atoi(row[vertical_index]); g_print("max_vertical is now %d\n", max_vertical); } } vertical_scale_multiplier = (gdouble) 540/max_vertical; g_print("vertical_scale_multiplier is %f\n", vertical_scale_multiplier); mysql_data_seek(result_set, 0); /* Now do the same thing with the horizontal axis. */ while (( row = mysql_fetch_row (result_set)) != 0) { if (atoi (row[horizontal_index]) > max_horizontal) { max_horizontal = atoi(row[horizontal_index]); g_print("max_horizontal is now %d\n", max_horizontal); } } horizontal_scale_multiplier = (gdouble) 835/max_horizontal; g_print("horizontal_scale_multiplier is %f\n", horizontal_scale_multiplier); } void plot_maximums() { /* This function will draw the maximum value strings that are * placed in the top left (for the vertical axis) and the bottom * right (for the horizontal axis). */ GdkGC *gc; gc = get_gc("Black"); gdk_draw_string( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->style->font, gc, 0, 10, g_strdup_printf("%d", max_vertical)); gdk_draw_string( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->style->font, gc, 845, HORIZONTAL_LINE + 15, g_strdup_printf("%d", max_horizontal)); } void plot_graph() { /* This function is the workhorse that calls other functions to * redraw and plot the graph. */ gint i = 0; gdk_draw_rectangle( GTK_WIDGET(lookup_widget(frm_scatter, "drawingarea1"))->window, get_gc("Gray"), TRUE, 0, 0, 900, 575); load_data(); draw_axis_and_scales(); set_indices(); set_maximums(); plot_maximums(); mysql_data_seek(result_set, 0); while (( row = mysql_fetch_row (result_set)) != 0) { plot_point( VERTICAL_LINE + atoi(row[horizontal_index])*horizontal_scale_multiplier, HORIZONTAL_LINE - atoi(row[vertical_index])*vertical_scale_multiplier, row[0]); i++; } void set_indices() { /* Set the variables horizontal_index and vertical_index * so that the software knows which columns in result_set to * plot. This is done by reading the combo boxes on frm_scatter . */ GtkCombo *cbo; gchar *cbo_text; cbo = GTK_COMBO(lookup_widget(frm_scatter, "cbo_vertical")); cbo_text = gtk_entry_get_text(GTK_ENTRY(cbo->entry)); g_print("vertical axis is %s\n", cbo_text); if (g_strcasecmp(cbo_text, "Order Quantity") == 0) vertical_index = 1; else if (g_strcasecmp(cbo_text, "Unit Price") == 0) vertical_index = 2; else if (g_strcasecmp(cbo_text, "Net Value") == 0) vertical_index = 3; else if (g_strcasecmp(cbo_text, "Invoice Quantity") == 0) vertical_index = 4; else if (g_strcasecmp(cbo_text, "Invoice Value") == 0) vertical_index = 5; else vertical_index = -1; g_print("vertical_index is %d\n", vertical_index); cbo = GTK_COMBO(lookup_widget(frm_scatter, "cbo_horizontal")); cbo_text = gtk_entry_get_text(GTK_ENTRY(cbo->entry)); g_print("horizontal axis is %s\n", cbo_text); if (g_strcasecmp(cbo_text, "Order Quantity") == 0) horizontal_index = 1; else if (g_strcasecmp(cbo_text, "Unit Price") == 0) horizontal_index = 2; else if (g_strcasecmp(cbo_text, "Net Value") == 0) horizontal_index = 3; else if (g_strcasecmp(cbo_text, "Invoice Quantity") == 0) horizontal_index = 4; else if (g_strcasecmp(cbo_text, "Invoice Value") == 0) horizontal_index = 5; else horizontal_index = -1; g_print("horizontal_index is %d\n", horizontal_index); } gboolean load_data() { /* This function connects to the database and retrieves the * data to plot. In this case, the data is about 4,000 rows. * So once the data has been successfully pulled from the * database, there is no need to pull it again. * * To that end, the first thing the function does is check * to see if it has run successfully before (by setting the * already-loaded variable to TRUE at the end of the function). * * The function returns TRUE if the data has previously been * retrieved or if it can successfully do so this time. */ static gboolean already_loaded = FALSE; if (already_loaded) { g_print("Data has been previously loaded.\n"); return TRUE; } conx = mysql_init((MYSQL *)0L); if (conx == 0L) { gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "mysql_init problem"); return FALSE; } gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "mysql_init ok"); conx = mysql_real_connect(conx, SERVER, LOGIN, PASS, DB, 0, 0L, 0); if (conx == 0L) { gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1,"mysql_real_connect problem"); return FALSE; } gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "mysql_real_connect ok"); sql = g_strconcat("select line_number, order_quantity, ", " unit__price, net_value, ", " invoice__quantity, invoice_value ", " from tbl__revenue ", " order by line__number", 0L); g_print("sql is %s\n", sql); if (mysql_query (conx, sql) != 0) { gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "mysql_query problem"); g_print("mysql_query problem in load_data.\n"); mysql_close(conx); return FALSE; } gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_scatter, "statusbar1")), 1, "mysql_query ok"); result_set = mysql_store_result(conx); mysql_close(conx); already_loaded = TRUE; return TRUE; }
Listing 12.11 is the callbacks.c file for the scatter plot program. It makes a series of calls to the functions in scatter_utils.c (Listing 12.10).
#ifdef HAVE_CONFIG_H # include <config.h> #endif #include <gtk/gtk.h> #include "callbacks.h" #include "interface.h" #include "support.h" #include "scatter_utils.h" gboolean on_frm_scatter_delete_event (GtkWidget *widget, GdkEvent *event, gpointer user_data) { gtk_main_quit(); return FALSE; } void on_frm_scatter_show (GtkWidget *widget, gpointer user_data) { g_print("starting on_frm_scatter_show.\n"); load_data(); draw_axis_and_scales(); plot_graph(); } void on_cmd_redraw_clicked (GtkButton *button, gpointer user_data) { load_data(); draw_axis_and_scales(); plot_graph(); } gboolean on_drawingarea1_event (GtkWidget *widget, GdkEvent *event, gpointer user_data) { g_print("starting on_drawingarea1_event.\n"); draw_axis_and_scales(); plot_graph(); return FALSE; } gboolean on_frm_scatter_event (GtkWidget *widget, GdkEvent *event, gpointer user_data) { g_print("on_frm_scatter_event\n"); return FALSE; }
Finally, Figure 12.6 shows the scatter plot program. Are points 2244, 2086, 2317, and 2560 profitable or not? Why are they out to the right all by themselves ? Does the fact that they have a high invoice value and a low order quantity mean the overhead in filling those orders was lower? Or that those customers are more profitable than others? Or that those products are more profitable?
The type of executive reporting presented in this chapter is a very common require-ment in many corporations around the world. If you are considering embarking on this path , however, you should know that there is always one more question. When one set of data is given some visibility, there will always be someone who will want to know more, faster. Don t say you weren t warned !
only for RuBoard - do not distribute or recompile |