Utility Functions of the Application

only for RuBoard - do not distribute or recompile

This section lists and describes the code found in comm_utils.c (you can download the code from the www.newriders.com Web site). These are the workhorse functions of the application, much like sesi_utils.c was in Chapter 7.

Header, Logging, and Message Communication Functions

Listing 9.2 is the header section of comm_utils.c after the code has been written and compiled. It contains the #include statements, macro definitions, and such that will be used throughout the code.

Listing 9.2 Header Section from comm_utils.c

 #include <gtk/gtk.h>  #include <mysql.h>  /*  string.h  is needed for the  strlen()  function.   *  time.h  is needed for the date/time functions.   *  stdio.h  is needed for the write-to-file operations. */  #include <string.h>  #include <time.h>  #include <stdio.h>  #include "support.h"  #include "ddc.h"  #define SERVER "einstein"  #define LOGIN "com_user"  #define PASS "syL0U812"  #define DB "commish"  GtkWidget *frm_login;  GtkWidget *frm_table_display;  GtkWidget *frm_commissions;  /* The following generic variables are used in   * many of the functions below. There is no reason they   * HAVE to be global; however, if they aren't global, they   * would have to be declared in each individual function and   * passed in and out of functions repeatedly.   */  MYSQL       *conx;         //Connection to the database.  MYSQL_RES   *result_set;   //The results from the sql query.  MYSQL_ROW   row;           //A single row from result_set.  gchar       *sql;          //Generic variable for a sql string.  gchar       *user_login;   //The name of the user logged in.

Listing 9.3 is the log_event() function. Recall that part of the project specification in Chapter 8 was some sort of simple logging procedure that would enable the database and application administrator to know if and when someone had hacked into the system via the application. Obviously, the hacker would have to have a compiled version of the application to attempt to hack in that way, but it still is important to check.

Listing 9.3 log_event() Function from comm_utils.c

 void log_event(gchar *event_msg)  {  /* This routine will quietly log who attempts to log in,   * as well as other potential security flags.   *   * The one thing that would be important is the machine name   * of the attempting login. However, because that has   * been taken care of with a GRANT statement for each   * machine that can potentially log in, it won't be   * included here.   *   * The  g_get_user_name()  function gets the login   * of the user at the operating system level.   * In this case, the application maintains a separate list   * of logins and passwords; the  g_get_user_name()  function   * is used as a double check on the attempted login   * and may be useful if someone who shouldn't have access   * attempts to log in. Obviously, it could   * also be used to "pass through" the machine login   * to the application, avoiding the need for a separate   * login and password per application.   */  sql = g_strconcat("insert into tbl_logged_events ",                       "(user_login, event) ",                       "values ('", user_login, "', '", event_msg,                       " local login: ", g_get_user_name(), "')",                       0L);  /* The following  g_print()  statements have been commented out;   * because this is a very commonly used function, during debugging,   * it might be helpful to comment them back in.   */  // g_print ("log_event init of mysql...\n");     conx = mysql_init(0L);     if (conx == 0L) g_print("init problem...\n");     // g_print("real connecting...\n");     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);       // g_print("real connect done...\n");     if (conx == 0L) g_print("real connect problem...\n");     // g_print("querying...\n");     mysql_query (conx, sql);     // g_print("closing...\n");     mysql_close (conx);  }

Listing 9.4 is the set_message() function from comm_utils.c . To make things easy, it pushes messages to one widget in each of the three forms. That way it doesn t have to determine which form is currently active.

Listing 9.4 The set_message() Function from comm_utils.c

 void set_message(gchar *msg)  {  /* The application to offers three places   * to communicate with the user:   * the label widget at the bottom of frm_login and the statusbar   * widgets at the bottom of each of the other two forms. To make   * it easy, just push the message to all three.   */  g_print("set_message: %s\n", msg);    gtk_label_set_text(GTK_LABEL(lookup_widget(frm_login,                       "lbl_messages")), msg);    gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_table_display,                                     "statusbar")), 1, msg);    gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_commissions,                                     "statusbar_processing")), 1, msg);  /* Of course, this could also send msg to log_event for   * really detailed logging.   */  g_print("Exiting set_message...\n");  };

Login and Password Functions

This section contains the login and password functions from the application. Recall that the application (that is, the compiled executable) has a login to the MySQL server that it uses to connect and pass information to and from the database. The user login, however, is a set of tables within the database that checks the login and password of the user and maintains the permissions pertaining to what data the logged in user has access to.

Listing 9.5 is the code for the login and password functions from comm_utils.c . The function check_login_and_password() determines if the login and passwords given are correct, and set_new_password() works with check_login_password() to set a new password for the user. Note that both are called from callbacks.c .

Listing 9.5 Login and Password Functions from comm_utils.c

 gboolean check_login_and_password()  {     g_print("inside check_login_and_password.\n");  /* Take the user login ID and password from the entry   * widgets on frm_login.   */  user_login = gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_login, *entry_name*)),                               0, -1);      g_print("user_login is %s\n", user_login);  /* Create a SQL string that will return a row if the   * user login and password are valid.   */  sql = g_strconcat("select * from ",                        "tbl_security where user_login = '",                        user_login,                        "' and pass__word = password('",                        gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_login,                               "entry_password")),                               0, -1),                        "')", 0L);  /* This application takes a bit of a different approach   * with the database connection mechanism. It will connect   * and close the connection for each database operation   * rather than attempt to maintain an open connection.   *   * Because the following code is the first attempt to connect,   * the error messages will be returned to the user if   * something goes wrong. For later connections in this   * application, it will be assumed that if the application   * gets past these initial checks, the connection is   * available and reliable.   */  g_print("attemtpting init...\n");      if ((conx = mysql_init(0L)) == 0L)         {           set_message("Unable to initialize mysql.");            return FALSE;         }      g_print("init done...\n");      conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);      if (conx == 0L)         {           mysql_close(conx);            set_message("Unable to initialize database connection.");            return FALSE;         }      g_print("real_connect done...\n");      if (mysql_query (conx, sql) != 0)         {           g_print("query failed...\n");            mysql_close(conx);            set_message("Unable to query database.");            return FALSE;         }      set_message("Connected to commissions database...");      result_set = mysql_store_result(conx);  /* If the user login and password are correct, the result set   * should contain exactly one row.   */  if (mysql_num_rows (result_set) == 1)         {            mysql_free_result (result_set);             mysql_close(conx);             set_message("Logged in");  /* As part of the audit trail, you must report who logged in.   * Because the table that holds the logged events has a   * timestamp column, the date and time of the login or   * attempted login is captured.   */  log_event(g_strconcat("Login of ",                                   user_login, 0L));             return TRUE;         }      else         {            mysql_free_result (result_set);             mysql_close(conx);             set_message("Unable to log in. See Administrator.");             log_event(g_strconcat("Failed Login attempt by ",                                   user_login, 0L));             return FALSE;         }  }  gboolean set_new_password()  {    gchar     *str_new_pwd;     gchar     *str_new_again;  /* First, some basic checking. Make sure that the   * new passwords have some length. Are the two   * new passwords the same, and so on?   * Generally, logins are   * not case sensitive but passwords are.   */  g_print("starting set_new_password...\n");     log_event(g_strconcat(user_login, " attempting to set "                           "new password. ", 0L));  /* Get the text the user has entered (twice) as the   * value for the new password.   */  str_new_pwd = gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_login,                              "entry_new_password")),                              0, -1);     str_new_again = gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_login,                              "entry_new_password_again")),                              0, -1);  /* Note that the next function sets the minimum length   * of the password to one. Obviously, change the "0"   * below to alter the minimum length of the password.   */  if (strlen(str_new_pwd) == 0)       {         set_message("New password(s) not of sufficient length.");          return FALSE;       }  /* Here the  strcmp()  function is used instead of the   * g_  strcasecmp()  function. The g_  strcasecmp()  function ignores   * case in its comparison, which is not the desired behavior.   */  if (strcmp(str_new_pwd, str_new_again) == 0)        {          set_message("New passwords matched...\n");           sql = g_strconcat("update tbl_security ",                             "set pass_word = password('",                             str_new_pwd, "') ",                             "where user_login = '", user_login, "'",                             0L);           g_print("sql is %s\n", sql);  /* Because the new password has been typed the same   * twice, consider it "validated" and connect to   * the database to set the new password.   */  conx = mysql_init(0L);           conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);           if (mysql_query (conx, sql) != 0)              {                set_message("Unable to change password. "                             "Database query failure.\n"                             "Press Login to connect with "                             "current name and password.");                 mysql_close(conx);                 return FALSE;              }           else              {                set_message("New password set.");                 mysql_close(conx);                 log_event(g_strconcat(user_login,                           " changed password. ", 0L));                 return TRUE;              }        }     else        {          set_message("New password(s) not matched.\n");           return FALSE;        }     return FALSE;  }

Listing 9.6 is a couple of utility functions for filling in and correctly displaying the tables the user has access to while in this application. get_table_names() queries the database for a list of tables the user is authorized to see, and it returns those values in a Glist. The function(s) in callbacks.c then call fi ll_table_combos() to fill the drop-down lists of the combo boxes that list authorized table views.

Listing 9.6 Authorized Table View Functions from comm_utils.c

 GList *get_table_names()  {  /* Query the database and return a list of tables the   * currently logged in user has authorization to view.   */  GList *list_of_tables = 0L;      conx = mysql_init(0L);      conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);  /* tbl_permissions lists database object permissions   * by login.   */  sql = g_strconcat("select * from tbl_permissions ",                        "where login = '", user_login, "'",                        0L);      if (mysql_query (conx, sql) != 0)          {             set_message("Unable to retrieve list of tables.");              log_event(g_strconcat("Unable to retrieve list of tables",                               " for user ", user_login, 0L));            }      else          {             result_set = mysql_store_result (conx);  /* Now that the database has returned a result set,   * fetch each row and append the value of the second   * column to the GList.   */  while ((row = mysql_fetch_row (result_set)) != 0L)                  {                   if (strcmp(row[1], "process") != 0)                       list_of_tables = g_list_append(list_of_tables,                                          row[1]);                    else                       gtk_widget_show(lookup_widget(frm_table_display,                                  "cmd_process"));                  }          }      mysql_close(conx);  /* Finally, return the list of tables to the calling function,   * fill_  table_combos()  , below.   */  return list_of_tables;  }  void fill_table_combos()  {  /* This function fills the two combo boxes that list   * the tables the user has access to.   */  gtk_combo_set_popdown_strings(GTK_COMBO(lookup_widget(frm_table_display, "cbo_tables")),                       get_table_names());      gtk_combo_set_popdown_strings(GTK_COMBO(lookup_widget                       (frm_commissions, "cbo_table")),                       get_table_names());  }

Primary Data Processing Functions

The next three sections break the complexity of the application down into preparation (where old data is archived and other preparations are made for the incoming data) and the main data processing steps (as defined in Chapter 8). Finally, the application must produce the desired output. In this case, the text file reports for each salesperson and country manager.

Preparation Functions

Listing 9.7 is the pre processing of the data; it prepares the database for the incoming data. frm_commissions has 10 buttons down the left side; Listing 9.7 shows the functions behind the first two of those buttons , as called from callbacks.c of course. The functions in Listing 9.7 occur in the same order as the command buttons on frm_commissions.

Listing 9.7 Data Processing Functions Used by frm_commissions

 void archive_tbl_commissions()  {    gchar       *str_table_name;     time_t      now;     gchar       *rows_before, *rows_after;  /* This function will archive the existing tbl_commissions   * and prepare it for a new round of processing.   *   * The function follows these steps:   *     1. Get a count of the number of records currently   *         in the table.   *     2.  Create a new table with a make table query that   *         transfers the contents of tbl_commissions to the   *         new table. This new table will be the date and   *         time of its creation for archival purposes.   *     3.  Get a count of records in the newly created table   *         and compare it to the original table.   *     4.  If the table record counts match, delete all rows   *         from tbl_commissions and indicate success to the   *         user.   */  log_event("Archiving tbl_commissions. ");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     sql = "select count(*) from tbl_commissions";       if (mysql_query (conx, sql) != 0)          {  /* Query failure... */  set_message("Query Failure in record "                          "count on tbl_commissions.");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                                 "lbl_archive")), "Query Failure in "                                 "record count on "                                 "tbl_commissions.\n Contact "                                 "administrator.");  /* Nothing to do but abort. */  mysql_close(conx);              return;          }     else         {  /* Query Success... */  result_set = mysql_store_result (conx);              row = mysql_fetch_row (result_set);              g_print("tbl_commissions row count is %s...\n", row[0]);              rows_before = row[0];         }  /* Now to create the new table. */  time(&now);     str_table_name = g_strstrip((gchar *) ctime(&now));     str_table_name = g_strdelimit(str_table_name, " :,", '_');     g_print("new table name is %s\n", str_table_name);  /* The command used below is the  CREATE TABLE  command. Although   * there are various ways to use this command, in this case the   * syntax that will give the desired result is   *   *  create table tbl select-statement  *   * where  tbl  is the new table name and  select-statement  is a   * valid sql select statement. This creates a new table from   * the results of a valid select statement, rather than specifying   * the columns explicitly.   */  sql = g_strconcat("create table ", str_table_name,                       " select * from tbl_commissions", 0L);     if (mysql_query (conx, sql) != 0)        {             /* Query failure */              set_message(g_strconcat("Query Failure in ",                        "create table on ",                        str_table_name, 0L));              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                                 "lbl_archive")), "Query Failure "                                 "in create table statement.\n "                                 "Note the date and time "                                 "and contact administrator.");  /* Nothing to do but abort. */  mysql_close(conx);              return;        }  /* Query Success */   /* Now to count the rows in the new table and make sure it   * compares to the number from the original.   */  sql = g_strconcat("select count(*) from ", str_table_name, 0L);     if (mysql_query (conx, sql) != 0)        {  /* Query failure */  set_message(g_strconcat("Query Failure: count of table ",                        str_table_name, 0L));              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                                 "lbl_archive")), "Query Failure "                                 "in count of new table.\n"                                 "Note the date and time "                                 "and contact administrator.");              mysql_close(conx);              return;        }  /* Query Success */  result_set = mysql_store_result (conx);     row = mysql_fetch_row (result_set);     g_print("new table row count is %s...\n", row[0]);     rows_after = row[0];  /* Compare the answers of the two count queries.   * Whether integer or string, the answers should   * be identical. In this case, because of the way   * mysql queries are returned, the results are   * strings.   */  if (g_strcasecmp(rows_after, rows_before) != 0)        {  /* Difference in query results; something   * is wrong.   */  set_message("Failure comparing archived table "                        "to current table.");            gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_archive")), "Query Failure in comparison "                        "of archived and new tables.\n"                        "Note the date and time "                        "and contact administrator.");            mysql_close(conx);            return;        }  /* The number of rows matches up   * At this point, all indications are that   * tbl_commissions has been correctly archived   * and that the program can continue with   * the processing operation.   */  sql = "Delete from tbl_commissions";     if (mysql_query (conx, sql) != 0)        {           /* Query failure */            set_message("Failure deleting from tbl_commissions");            gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_archive")), "Query Failure in delete "                        "of records from tbl_commissions.\n "                        "Note the date and time "                        "and contact administrator.");            mysql_close(conx);            return;        }  /* Query Success.   * Remember, the  mysql_query()  function   * returns an answer that indicates whether or not   * it was given a properly formed SQL statement   * that could be executednot whether or not the   * query actually deleted anything (in this   * case). Therefore, the software needs to   * check the count of rows in the table.   *   * Continue processing   */  sql = "select count(*) from tbl_commissions";     if (mysql_query (conx, sql) != 0)        {  /* Query failure */  set_message("Failure getting count from tbl_commissions");           gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_archive")), "Query Failure in count "                        "of records from tbl_commissions.\n "                        "Note the date and time "                        "and contact administrator.");           mysql_close(conx);           return;        }  /* Query executed okay */  result_set = mysql_store_result (conx);     row = mysql_fetch_row (result_set);     g_print("old table row count is %s...\n", row[0]);     if (g_strcasecmp(row[0], "0") != 0)        {  /* Difference in query results; something   * is wrong. */  set_message("Not 0 rows in tbl_commissions");          gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_archive")), "Query Failure: not 0 "                        "rows in tbl_commissions.\n "                        "Note the date and time "                        "and contact administrator.");          mysql_close(conx);          return;        }     else        {  /* All has gone correctly. */  mysql_close(conx);          gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                                "lbl_archive")), "Archive succeeded.");          log_event("Successfully archived tbl_commissions. ");        }  }  void load_tbl_revenue()  {  /* This function will load the text file into tbl_revenue.   * It does the same thing as Listing 8.1 without the drop table   * and select count(*) statements. In fact, the LOAD   * DATA statement is identical except for the escape characters   * before the double-quotation marks in the first line.   */  log_event("Improrting to tbl_revenue. ");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting import...\n");  /* First, empty tbl_revenue and verify that it is   * empty before you import the text file.   */  sql = "delete from tbl_revenue";     if (mysql_query (conx, sql) != 0)        {  /* Query failure */  mysql_close(conx);           set_message("Query failure: delete from tbl_revenue");           gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                     "lbl_import")), "Query Failure deleteing from "                     "tbl_revenue.\n "                     "Note the date and time "                     "and contact administrator.");           return;        }     else        {          g_print("Query success, delete from tbl_revenue.\n");        }  /* Check to see that there are, in fact, 0 rows in tbl_revenue. */  sql = "select count(*) from tbl_revenue";     if (mysql_query (conx, sql) != 0)        {  /* Query failure */  mysql_close(conx);           set_message("Query failure: count of rows from tbl_revenue");           gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                     "lbl_import")), "Query Failure counting rows from "                     "tbl_revenue.\n "                     "Note the date and time "                     "and contact administrator.");           return;        }  /* Query success. */  result_set = mysql_store_result (conx);     row = mysql_fetch_row (result_set);     g_print("tbl_revenue row count is %s...\n", row[0]);     if (g_strcasecmp(row[0], "0") != 0)        {  /* Not 0 rows in tbl_revenue. Importing now would   * result in incorrect data processing, so exit.   */  set_message("Not ZERO rows in tbl_revenue");          gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_import")), "Query Failure: not ZERO "                        "rows in tbl_revenue.\n "                        "Note the date and time "                        "and contact administrator.");          mysql_close(conx);          return;        }  /* Query okay, so continue */  sql = "LOAD DATA INFILE "           " \"/mnt/DOS_hda2/newriders/book/ch8/tbl_revenue.txt\" "           "INTO TABLE tbl_revenue "           "FIELDS TERMINATED BY \',\' "           "IGNORE 1 LINES "           "(customer_number,"           " customer__name,"           " salesperson,"           " country,"           " city,"           " state,"           " zip,"           " ship__date,"           " item__number,"           " order__quantity,"           " unit__price,"           " net__value,"           " invoice__number,"           " invoice__date,"           " invoice__quantity,"           " invoice__value)";     g_print("sql is: %s\n", sql);  /* Note that in order for this application to   * execute the query currently   * in sql, the following permission must be set. In the mysql   * database (which is actually a database within the mysql server),   * in table 'user', the File_priv must be set to Y for the   * specified user on the specified machine. In this   * case, the user is com_user, and the machine name will be whatever   * machine the user "processor" is on.   *   * Don't forget to give the flush privileges   * command from the mysql> prompt   * to reload the privileges to the database   * (or reboot the machine, which also works).   */  if (mysql_query (conx, sql) != 0)          {             /* Query failure */              g_print("Error number is %i...\n", mysql_errno(conx));              g_print("Error description is %s...\n",                      mysql_error(conx));              mysql_close(conx);              set_message("Query failure in LOAD DATA statement");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_import")), "Query Failure in "                        "LOAD DATA statement.\n "                        "Note the date and time "                        "and contact administrator.");              return;          }             /* Query success */     sql = "select count(*) from tbl_revenue";     if (mysql_query (conx, sql) != 0)          {             /* Query failure */              mysql_close(conx);              set_message("Query failure in select count "                          "from tbl_revenue");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_import")), "Query Failure in "                        "select count from tbl_reveue..\n "                        "Note the date and time "                        "and contact administrator.");              return;          }              /* Query success */     result_set = mysql_store_result (conx);     row = mysql_fetch_row (result_set);     g_print("tbl_revenue row count is %s...\n", row[0]);     if (g_strcasecmp(row[0], "0") != 0)        {  /* tbl_revenue should be populated now. So   * report "success" back to the user.   */  gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_import")), g_strconcat("Import succeeded. ",                        row[0], " rows imported.", 0L));        }     else        {  /* There are 0 rows in the table after the import operation,   * which is incorrect; the table should be populated now.   */  gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_import")), "0 rows in tbl_revenue after "                        "import operation...\n "                        "Note the date and time "                        "and contact administrator.");        }      mysql_close(conx);  }

Six Main Data Processing Steps

The application being built has six primary processing steps, which are the center six command buttons on frm_commissions of the ten down the left side of the form. Listing 9.8 lists the functions that perform those steps.

Listing 9.8 Main Data Processing Functions

 void process_customer_commissions()  {  /* If a row in tbl_revenue has a salesperson number in it,   * that salesperson gets commission on the invoice. There may   * be others that do also, and the salesperson in question may   * also get other commissions, but the salesperson will always   * get commissions for those rows in tbl_revenue where his or her   * salesperson number shows up.   */  log_event("Processing commissions on salepeople in tbl_revenue. ");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting customer processing...\n");     sql = "insert into tbl_commissions "           "(salesperson, invoice_num, commission) "           "select tbl_revenue.salesperson, "           "tbl_revenue.invoice_number, "           "tbl_people.commission * tbl_revenue.invoice_value "           "from tbl_revenue, tbl_people "           "where tbl_revenue.salesperson = tbl_people.salesperson";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure in "                          "process_customer_commissions.");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_customers")), "Query Failure "                        "procssing by saleperson in tbl_revenue. \n "                        "Could not execute Insert query. \n"                        "Note the date and time "                        "and contact administrator.");              return;          }     else          {             gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                  "lbl_customer")), g_strconcat("Customer Succeeded. ",                  g_strdup_printf("%d",(gint) mysql_affected_rows(conx)),                  " rows inserted.", 0L));              mysql_close(conx);              set_message("process_customer_commissions succeeded.");          }  }  void process_worldwide()  {  /* In this step, the software should process commissions for those   * salespeople who are paid a commission based on every invoice   * issued worldwide. This will be a simple operation because there   * are only two people in tbl_people_paid_on_worldwide.   */  log_event("Processing worldwide commissions.");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting worldwide processing...\n");     sql = "insert into tbl_commissions "           "(salesperson, invoice_num, commission) "           "select tbl_people_paid_on_worldwide.salesperson, "           "       tbl_revenue.invoice_number, "           "       tbl_people.commission * tbl_revenue.invoice_value "           "from tbl_people_paid_on_worldwide, tbl_revenue, tbl_people "           "where tbl_people.salesperson = "           "      tbl_people_paid_on_worldwide.salesperson ";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure in process_worldwide.");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_worldwide")), "Query Failure "                        "procssing worldwide. \n "                        "Could not execute Insert query. \n"                        "Note the date and time "                        "and contact administrator.");              return;          }     else          {             gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                 "lbl_worldwide")), g_strconcat("Worldwide Succeeded. ",                 g_strdup_printf("%d",(gint) mysql_affected_rows(conx)),                 " rows inserted.", 0L));              mysql_close(conx);              set_message("process_worldwide succeeded.");          }  }  void process_area(gchar *area)  {  /* This function allows processing of the country and state   * steps. This is due to the similar names of the label   * widgets and MySQL tables. Unfortunately, because cmd_zip and   * lbl_zip are not named cmd_zipcode and lbl_zipcode,   * this proceedure will not work for the zipcode   * processing.   */  log_event(g_strconcat("Processing ", area, " commissions.", 0L));     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting %s processing...\n", area);     sql = g_strconcat("insert into tbl_commissions "           "(salesperson, invoice_num, commission) "           "select tbl_people_paid_on_", area, ".salesperson, "           "       tbl_revenue.invoice_number, "           "       tbl_people.commission * tbl_revenue.invoice_value "           "from tbl_people_paid_on_", area, ", tbl_revenue, tbl_people "           "where tbl_people.salesperson=tbl_people_paid_on_",                  area, ".salesperson"           " and tbl_people_paid_on_", area, ".", area,           "         =tbl_revenue.", area,           0L);     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message(g_strconcat("Query failure in process_",                                      area, ".", 0L));              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        g_strconcat("lbl_", area, 0L))),                        g_strconcat("Query Failure "                        "procssing ", area, ". \n "                        "Could not execute Insert query. \n"                        "Note the date and time "                        "and contact administrator.", 0L));              return;          }     else          {             gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                  g_strconcat("lbl_", area, 0L))),                  g_strconcat(area, " Succeeded. ",                  g_strdup_printf("%d",(gint) mysql_affected_rows(conx)),                  " rows inserted.", 0L));              mysql_close(conx);              set_message(g_strconcat("process ", area,                                     " succeeded.", 0L));          }  }  void process_zipcode()  {  /* Certain people are paid based on the zipcode that is in tbl_revenue   * for that invoice. This function will add those lines to   * tbl_commisisons.   */  log_event("Processing zipcodes.");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting zipcode processing...\n");     sql = "insert into tbl_commissions "           "(salesperson, invoice_num, commission) "           "select tbl_people_paid_on_zipcode.salesperson, "           "       tbl_revenue.invoice_number, "           "       tbl_people.commission * tbl_revenue.invoice_value "           "from tbl_people_paid_on_zipcode, tbl_revenue, tbl_people "           "where tbl_people.salesperson="           "                   tbl_people_paid_on_zipcode.salesperson"           "  and tbl_people_paid_on_zipcode.zipcode=tbl_revenue.zip";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure in process_zipcode");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_zip")), "Query Failure "                        "procssing zipcodes. \n "                        "Could not execute Insert query. \n"                        "Note the date and time "                        "and contact administrator.");              return;         }     else         {             gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                  "lbl_zip")), g_strconcat("Zipcodes Succeeded. ",                  g_strdup_printf("%d",(gint) mysql_affected_rows(conx)),                  " rows inserted.", 0L));                mysql_close(conx);              set_message("process zipcodes succeeded.");          }  }  void process_paid_on_people()  {  /* Some people are paid based on the revenue generated by others; for   * example, a regional manager may get a commission on his   * salespersons' activity. This function inserts those   * rows into tbl_commissions.   */  log_event("Processing people paid on other people.");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting paid-on-others processing...\n");     sql = "insert into tbl_commissions "           "(salesperson, invoice_num, commission) "           "select tbl_people_paid_on_other_people.person_paid, "           "tbl_revenue.invoice_number, "           "tbl_people.commission * tbl_revenue.invoice_value "           "from tbl_revenue, tbl_people,           "     tbl_people_paid_on_other_people "           "where tbl_revenue.salesperson = "           "      tbl_people_paid_on_other_people.paid_on "           " and tbl_people.salesperson = "           "     tbl_people_paid_on_other_people.person_paid";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure in process_paid_on_people");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_people")), "Query Failure "                        "procssing those paid on others. \n "                        "Could not execute Insert query. \n"                        "Note the date and time "                        "and contact administrator.");              return;          }     else          {             gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                 "lbl_people")),                 g_strconcat("People paid on other",                 " People Succeeded. ",                 g_strdup_printf("%d",(gint) mysql_affected_rows(conx)),                 " rows inserted.", 0L));              mysql_close(conx);              set_message("process paid-on-others succeeded.");          }  }

Output Functions

Finally, this application must produce output for it to be useful. As outlined in Chapter 8, it produces two types of output: a text file for each salesperson, and a text file for each country manager. The salesperson s text file summarizes his or her activity for the past month, and the country manager gets a report as to who was paid and how much, converted to the local currency. Listing 9.9 is the code that produces these files.

Listing 9.9 Output Functions from comm_utils.c

 void write_salespeople_statements()  {    gchar *file_name;     FILE *fp;     gchar *current_salesperson = "nobody";     log_event("Writing salespeople statements.");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting write of salespeople statments.\n");     gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_salespeople")), "Retrieving records "                        "from the database...\n "                        "This may take a few minutes...");     gtk_widget_show(lookup_widget(frm_commissions, "lbl_salespeople"));     sql = "select tbl_commissions.salesperson, "           "       tbl_commissions.invoice_num, "           "       tbl_commissions.commission, "             "       tbl_people.first_name, "           "       tbl_people.last_name, "           "       tbl_revenue.customer_name, "           "       tbl_revenue.item_number, "           "       tbl_revenue.order_quantity "           "from tbl_commissions, tbl_people, tbl_revenue "           "where tbl_commissions.salesperson = tbl_people.salesperson "           "  and tbl_commissions.invoice_num ="           "                                tbl_revenue.invoice_number "           "order by tbl_commissions.salesperson, "           "         tbl_commissions.invoice_num";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure: writing "                          "salespeople statements");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_salespeople")), "Query Failure "                        "writing salespeople statements. \n "                        "Note the date and time "                        "and contact administrator.");              return;          }     else          {             result_set = mysql_store_result (conx);              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                   "lbl_salespeople")),                   g_strconcat("Writing salespeople ",                   "statements to disk...\n ",                   "Total Records: ",                   g_strdup_printf("%d",(gint)                   mysql_num_rows(result_set)),                   0L));              set_message("Retrieved records for salesman statements.");  /* The query executed okay, and result_set has returned   * the total number of rows. So it is okay to continue   */  }  /* Now to move on to writing to the file. Because each   * salesperson will have his or her own file, and because   * result_set is organized by salesperson, the software will   * iterate through the rows and create a new file each time   * the value for salesperson changes.   *   * To start, you need a filename.   *   * Fetch the first row to get things started.   */  if ((row = mysql_fetch_row (result_set)) == 0L)        {  /* Unable to get first row. */  set_message("Unable to retrieve first row from result_set.");           return;        }  /* Okay to continue. */   /* To avoid a core dump when the fclose() operation   * is encountered, set up for the first salesperson.   */  current_salesperson = row[0];     file_name = g_strconcat(current_salesperson, "-",                       row[4], ".xls", 0L);     g_print("Current file is %s.\n", file_name);  /* Open the new file. */  if ((fp = fopen (file_name, "w")) == 0L)          {               set_message(g_strconcat("Unable to open first file ",                            file_name,                            " for write operation.", 0L));                return;          }     else          {  /* Write the header information. */  fprintf(fp, "Commissions detail file for ");                fprintf(fp, current_salesperson);                fprintf(fp, ", ");                fprintf(fp, row[3]);  /* The person's first name. */  fprintf(fp, "\n");                fprintf(fp, "All currency amounts are "                            "in US Dollars.\n\n");                fprintf(fp, "Invoice Number\t");                fprintf(fp, "Commission Amt\t");                fprintf(fp, "Customer Name\t");                fprintf(fp, "Item Number\t");                 fprintf(fp, "Order Quantity\n");          }  /* The file will be the salesperson number plus the   * person's last name. Because the salespersons are used   * to getting files that work in Excel, this function   * will write a tab-delimited file.   */  do {         if (g_strcasecmp(row[0], current_salesperson) == 0)            {  /* Still on the same salesperson. */  fprintf(fp, row[1]); fprintf(fp, "\t");               fprintf(fp, row[2]); fprintf(fp, "\t");               fprintf(fp, row[5]); fprintf(fp, "\t");               fprintf(fp, row[6]); fprintf(fp, "\t");               fprintf(fp, row[7]); fprintf(fp, "\n");            }          else            {  /* row now contains a new salesperson.   * Close the file currently open.   */  fclose(fp);  /* Create a new filename. */  current_salesperson = row[0];               file_name = g_strconcat(current_salesperson, "-",                                 row[4], ".xls", 0L);               g_print("Current file is %s.\n", file_name);  /** Open the new file. */  if ((fp = fopen (file_name, "w")) == 0L)                  {  /* Unable to open the file for write operation   * for some reason.   */  set_message(g_strconcat("Unable to open file ",                                 file_name,                                 " for write operation.", 0L));                      return;                  }  /* File opened and ready for write operation. */   /* Write the header information. */  fprintf(fp, "Commissions detail file for ");               fprintf(fp, current_salesperson);               fprintf(fp, " - ");               fprintf(fp, row[4]);  /* The person's last name. */  fprintf(fp, ", ");               fprintf(fp, row[3]);  /* The person's first name. */  fprintf(fp, "\n");               fprintf(fp, "All currency amounts are "                           "in US Dollars.\n\n");               fprintf(fp, "Invoice Number\t");               fprintf(fp, "Commission Amt\t");               fprintf(fp, "Customer Name\t");               fprintf(fp, "Item Number\t");               fprintf(fp, "Order Quantity\n");             }        } while ((row = mysql_fetch_row (result_set)) != 0L);     g_print("Exiting write_salesperson_statements.\n");     mysql_close(conx);  }  void write_country_manager_statements()  {  /* This function produces the statements for the "country   * managers." Each country gets its own statement, which is   * then emailed to the "country manager."   */  gchar *file_name;     FILE *fp;     gchar *current_country = "none";     log_event("Writing country manager statements.");     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("Starting write of country manager statments.\n");     gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_country_managers")), "Retrieving records "                          "from the database...\n "                        "This may take a few minutes...");     sql = "select tbl_people.country, tbl_commissions.salesperson, "           " tbl_commissions.commission, "           " tbl_commissions.commission * tbl_exchange_rates.rate "           "from tbl_commissions, tbl_people, tbl_exchange_rates "           "where tbl_commissions.salesperson = tbl_people.salesperson "           " and tbl_people.country = tbl_exchange_rates.country "           "group by tbl_commissions.salesperson "           "order by tbl_people.country ";     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  mysql_close(conx);              set_message("Query failure: "                          "writing country manager's statements");              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_country_managers")), "Query Failure "                        "writing country manager's statements. \n "                        "Note the date and time "                        "and contact administrator.");              return;          }  else          {             result_set = mysql_store_result (conx);              gtk_label_set_text(GTK_LABEL(lookup_widget(frm_commissions,                        "lbl_country_managers")),                        g_strconcat("Writing country ",                        "manager's statements to disk...\n ",                        "Total Records: ",                        g_strdup_printf("%d",(gint)                        mysql_num_rows(result_set)),                        0L));              set_message("Retrieved records for country "                          "manager's statements.");  /* The query executed okay, and result_set has returned   * the total number of rows. So it is okay to continue.   */  }  /* Fetch the first row to get things started. */  if ((row = mysql_fetch_row (result_set)) == 0L)        {  /* Unable to get first row. */  set_message("Unable to retrieve first row from result_set "                       "for country manager's statements.");           return;        }  /* Okay to continue. */  current_country = row[0];     file_name = g_strconcat(current_country, ".xls", 0L);     g_print("Current file is %s.\n", file_name);  /* Open the new file. */  if ((fp = fopen (file_name, "w")) == 0L)          {               set_message(g_strconcat("Unable to open first ",                            "country file: ",                            file_name,                            ", for write operation.", 0L));                return;          }     else          {  /* Write the header information. */  fprintf(fp, "Commissions detail file for country: ");                fprintf(fp, current_country);                fprintf(fp, "\n");                fprintf(fp, "Salesperson\t");                fprintf(fp, "US Dollars\t");                fprintf(fp, "Local Currency\n");          }     do {         if (g_strcasecmp(row[0], current_country) == 0)            {  /* Still on the same country. */  fprintf(fp, row[1]); fprintf(fp, "\t");               fprintf(fp, row[2]); fprintf(fp, "\t");               fprintf(fp, row[3]); fprintf(fp, "\n");            }          else            {  /* row now contains a new country.   * Close the file currently open.   */  fclose(fp);  /* Create a new filename. */  current_country = row[0];                file_name = g_strconcat(current_country, ".xls", 0L);                g_print("Current file is %s.\n", file_name);  /* Open the new file. */  if ((fp = fopen (file_name, "w")) == 0L)                   {  /* Unable to open the file for write operation   * for some reason.   */  set_message(g_strconcat("Unable to open ",                                   "country file ',                                   file_name,                                   " for write operation.", 0L));                       return;                   }                else                   {  /* File opened and ready for write operation. */  }  /* Write the header information. */  fprintf(fp, "Commissions detail file for country: ");                fprintf(fp, current_country);                fprintf(fp, "\n");                fprintf(fp, "Salesperson\t");                fprintf(fp, "US Dollars\t");                fprintf(fp, "Local Currency\n");             }        } while ((row = mysql_fetch_row (result_set)) != 0L);     g_print("Exiting write_country_manager_statements.\n");     mysql_close(conx);  }

Action Query Functions

Listing 9.10 contains the data insert, update, and delete functions that are used by processor (the person authorized to make changes to the database). These functions are called using the widgets at the bottom of frm_commissions.

Listing 9.10 Action Query Functions from comm_utils.c

 void insert_row()  {  /* This function inserts a single row into a table in the   * database. Along with the update and delete functions (below),   * it allows the user to modify the data in the database rather   * than having to make requests of the database administrator (dba).   */  gchar *str_table_name;     str_table_name = gtk_entry_get_text(GTK_ENTRY(GTK_COMBO                       (lookup_widget(frm_commissions, "cbo_table"))->entry));     sql = g_strconcat("insert into ", str_table_name,                        "() values()", 0L);     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     g_print("sql is %s\n", sql);     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  g_print("sql not ok in insert_row().\n");              mysql_close(conx);              g_print("connection closed.\n");              set_message(g_strconcat("Query failure: "                          "inserting to table",                           str_table_name, 0L));          }     else          {             g_print("sql formatted ok.\n");              set_message(g_strconcat("New row number is: ",                      g_strdup_printf("%d",(gint) mysql_insert_id(conx)),                      " in table ", str_table_name,                      0L));          }       log_event(g_strconcat("insert query on ", str_table_name, 0L));  }  void update_row()  {    gchar *str_table_name;     str_table_name = gtk_entry_get_text(GTK_ENTRY(GTK_COMBO                      (lookup_widget(frm_commissions, "cbo_table"))->entry));  /* The following sql statement is built assuming that it   * will modify a character column, not a numeric column. Note,   * however, that MySQL will do explicit type conversion   * from char to numeric in this case,   * and it will update numeric columns   * if the conversion succeeds. However, if the user   * attempts to incorrectly update a numeric column type   * to a character value, the value being updated will   * be set to 0.   */  sql = g_strconcat("update ", str_table_name, " set ",                  gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_commissions,                               "entry_column_name")), 0, -1),                  " = '",                  gtk_editable_get_chars(GTK_EDITABLE(lookup_widget(frm_commissions,                               "entry_new_value")), 0, -1),                  "' where line__number = ",                  g_strdup_printf("%d",                  gtk_spin_button_get_value_as_int(GTK_SPIN_BUTTON(lookup_widget(frm_commissions,                          "spinbutton_update_line")))),                  0L);     g_print("sql is: %s\n", sql);     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     if (mysql_query (conx, sql) != 0)          {  /* Query failure */  g_print("sql not ok in update_row().\n");              mysql_close(conx);              g_print("connection closed.\n");              set_message(g_strconcat("Query failure: updating row in ",                           str_table_name, 0L));          }      else          {             g_print("sql formatted ok.\n");              set_message(g_strconcat("Updated row in table ",                      str_table_name, 0L));          }      mysql_close (conx);      log_event(g_strconcat(user_login, " update query: ",                            g_strdelimit(g_strstrip(sql), "*"\"", '^'),                            0L));  }  void delete_row()  {  /* This function deletes a row from the table specified in   * cbo_table.   */  gchar *str_table_name;     str_table_name = gtk_entry_get_text(GTK_ENTRY(GTK_COMBO                      (lookup_widget(frm_commissions, "cbo_table"))->entry));     sql = g_strconcat("delete from ", str_table_name, " where ",                  "line_number = ",                  g_strdup_printf("%d",                  gtk_spin_button_get_value_as_int(GTK_SPIN_BUTTON(lookup_widget(frm_commissions,                          "spinbutton_delete_row")))),                  0L);     g_print("sql is: %s\n", sql);     conx = mysql_init(0L);     conx = mysql_real_connect(conx, SERVER, LOGIN,                               PASS, DB, 0, 0L, 0);     if (mysql_query (conx, sql) != 0)          {             /* Query failure */              g_print("sql not ok in delete_row().\n");              mysql_close(conx);              g_print("connection closed.\n");              set_message(g_strconcat("Query failure: ",                            "deleting row from ",                          str_table_name, 0L));          }      else          {             g_print("sql formatted ok.\n");              set_message(g_strconcat("Deleted row from table ",              str_table_name, 0L));          }      log_event(g_strconcat(user_login, " delete query: ",                            g_strdelimit(g_strstrip(sql), "*"\"", "^"),                            0L));  }

only for RuBoard - do not distribute or recompile