Problem Definition

only for RuBoard - do not distribute or recompile

The first step in the process is for the Marketing Department to produce a list of invoices that were filled in the previous month; normally this happens by the 5th day of the new month. (For this example, this file is called tbl_revenue.txt , and it is available from the book s Web site, which is located at www.newriders.com). From that list, the FA processing the commissions applies a series of business rules against the data to generate the commissions for each salesperson.

The first two lines from tbl_revenue.txt are shown here:

 customer_number,customer_name,salesperson,country,city,state,zipcode,ship_date,item_  number,order_quantity,unit_price,net_value,invoice_number,invoice_date,  invoice_quantity,invoice_value  250-700,ExecuData,4181,United States,Houston,TX,77001,2/24/2001  0:00:00,A1000050CZ,1,18.75,18.75,64-225904,3/27/2001 0:00:00,1,18.75

As you can see, the first line is the column headers, and the data starts on the second line. From this data file, the FA applies the business rules that determine how the data is processed , and he or she is responsible for producing the following in the form of ASCII text files:

How much each salesperson is to be paid in commission.
A report for each salesperson that details (as best as possible) which invoices they were paid on, what customers those invoices were for, and so on. Therefore, at the end of the processing steps, the FA should have a text file that can be emailed to each salesperson giving the individual a breakdown of his or her activity for the month (for example, which customers bought which products on which invoices ).
A report for each Country Manager as to how much was paid to salespeople in his or her country, both in US dollars and the local currency. The commissions are paid out according to the following rules:

The most common type of commission is paid on a geographic region such as state or country. Some people are paid on all invoices worldwide, and some are paid on everything in a particular country all the way down to a specific zip code (in the US) or an equivalent in another country (Post Code, Cedex, or Post Leitzahl, for example).

The revenue file ( tbl_revenue.txt ) that starts the entire process contains a salesperson field that is tied to the invoice number on that line. Obviously, that salesperson should be paid on the invoice on that line.

Some commissions are paid to managers based on the production of other salespeople. That is, a salesperson may get a commission based on an invoice, but that salesperson s manager may also get a commission based on that invoice.

Finally, a number of one-off rules have to be applied manually, such as Salesperson X is paid on everything in country Y, except customer Z. These rules are not coded into the system; it is cheaper in terms of person-hours to have them entered manually. Additionally, these one-off rules are the business rules that are most likely to change. So keeping them out of the application reduces the likelihood of maintenance coding in the future.

End User Information

Essentially , there are only two kinds of users: those authorized to alter the data and process commissions, and those with read-only access to one or more tables. At any given time, exactly one person will primarily be assigned to processing the data. Once a month this person will open the application, archive the tables from the previous month, and then process the current month s data.

Only a handful of people at any given time will access the database ”perhaps six to eight authorized users. All but one of them will be read-only users.

Security Concerns

Because this application will deal with the confidential subject of payroll and money, there are essentially three security choices for the application:

Server-level security. Users have access to the Linux box via operating system security. After they are logged in, however, there is still the problem of keeping the read-only users in read-only mode.
Database-level security. The users have logins to the MySQL database server.
Application-level security. The application asks for a login and password (or uses the currently logged-on user) and authenticates it against an internal table of users and their permissions.

In the current setup, all the relevant spreadsheets are kept on one FA s desktop PC. His or her login is all the security on the current system.

For this application, the last option (application-level security) will be used. It allows all the login names and other information to be kept in one place and makes it easier for the application to check the validity of the person logging in.

Essentially, the MySQL server will have a new login ”a generic application login that will have full rights within the database. The compiled application will connect to the database via this application login, and then the application itself will query the commish database to see what rights have been granted to the currently logged in user (the commish database will have tables that list access permissions for the users). The application will then control security for the users by presenting or not presenting options to the user. Given the previous security arrangements, this is considered a step up.

Additionally, this application will also need to do some basic logging: who logged in, when they logged in, from where (if possible), who modified which data, when it was modified, what information was viewed , and so on.

Existing Data Information

As with the previous example (the SESI project in Chapter 6 " Order Entry Abstract and Design," and Chapter 7 " Construction of the SESI Order Entry Application "), the existing data is kept in one or more spreadsheets. Again, these will be exported to delimited files, except that this time the files will be comma-separated, rather than tab-separated. These files are available at the Web site for this book; they follow the naming scheme tbl_* .txt where tbl_* represents the matching table in the database. Their names match up with the tables used in the database for this example.

Work Flow Specification

The once-a-month processing of the commissions data should proceed along the following steps. Additionally, keep in mind that while the process is normally run once a month at the beginning of the month, there is no guarantee that it will be run only once a month or that it will be run at the beginning of the month. It could be run at any time during the month to make corrections or additions. However, once it has been run and the data has been sent off to the country managers and payroll (via email), no other changes can be entered until the next month s processing. Thus, the salespeople are paid once a month.

The specific steps in the work flow are outlined here:

Archive the previous month s data. This will involve moving it to a new table. Provide the user the opportunity to name the table dynamically from within the software. Also, clear out any intermediate tables that were used.
Import the revenue file. It tells what was sold to whom, for how much, and so on in the previous calendar month. This will be a comma-separated values (CSV) file that will be received via FTP each month.
Provide a way for the user to manually adjust any values in the revenue table before moving on.
Based on the revenue table, calculate commissions for people, not necessarily in the following order:
- Pay salespeople based on the salesperson s numbers and invoice amounts that were contained in the revenue file ( tbl_revenue.txt ).
- Pay those people who get paid a commission on everything worldwide.
- Pay those people who get paid a commission based on which country the customer was from.
- Pay those people who get paid for sales in a certain state (in the US) or an equivalent locator (in foreign countries ).
- Pay those who get a commission based on a certain ZIP code (in the US) or equivalent locator (in foreign countries).
- Pay those who get paid based on what other people are getting paid.
Allow the user to manually adjust the payments calculated above as necessary (either at any time in the six processing steps above or after all payments have been produced).
Produce a comma-separated values file for each salesperson in the table, showing as much data as possible including a breakdown as to what they were paid on, how much, and so on.
Produce a report for each country showing who was paid and how much ”in both dollars and the local currency.

It is important to remember that these steps will be performed by one person and that all others with access to the database will normally be authorized to view only a subset of the tables.

only for RuBoard - do not distribute or recompile