Note that your solution program will be marked based on whether it

• is logically correct,
• handles exceptional situations reasonably,
• can be easily understood, and,
• properly follows the database application development principles (including the general software development principles).

Assignment Information

For this assignment, you are required to develop a database application program for the Forum Management using the following relational schema (primary keys are underlined) described in Assignment 1.

MemberAccounts(accnum, contactName, email, deliveryAddress, signUpDate, activationDate, status)
Producers(accnum, farmAddress)
FoodItems(productCode, name, unit)
SupplyRecords(accnum, productCode, supplyDate, quantity)
Orders(orderNum, placedBy, placeTime, packageType, expectDate, deliverTime, receivedBarcode, receivedPNum)
Batches(barcode, packingTime, sizeType)
Packages(barcode, pNum)
PackageContent(barcode, product, quantity)

Many more details, including constraints, data types, etc., are captured in the SQL definitions of these tables.

Your database application program must be developed using C++ plus the Oracle CLI occi.

Problem Description:

Develop a database application used by the Food Sharing Program to generate a summary report for a given year and for a given member or producer.

Your program should accept a year number and an account number as its command line arguments, and then retrieve the relevant data from the database to print the summary report to a text file named Report.txt.

Your program should first verify that the year number provided as command line argument is an integer number between 2020 (the year the hypothetical Food Sharing Program started) and 2024 (last full year of the Program) (inclusive). If the year number is invalid, show an appropriate message and terminate your program.

If the account number provided through the command line argument doesn't represent a valid member account, show an appropriate error message and terminate your program. Note that we don't care whether this member is currently an active member.

If the account number given represents a valid member, retrieve the following data and write them to the report file: (Here is a sample report file for a member.)

• the member account's account number;
• the member account's contact name;
• the member account's delivery address;
• the member account's current status;
• the number of different types of packages the member ordered in the given year.

If the given account number not only represents a valid member but also a producer, then on top of the data needed for a regular member, your program needs to retrieve the food contribution data made by this producer in the given year and write them to the report file. (Here is a sample report file for a producer.)

The contribution data should include the sum of the contributions made by the producer for each food item in the given year. If the producer didn't make any contribution for some food items, these food items don't need to be listed in the report.

Since this program will run to generate just one report at a time, it is not necessary to use prepared SQL statements. However, user input (including the command line arguments) must be sanitized if unprepared SQL statements are used.

One of the important principles of developing a database application program is to trust the database management system and minimize the use of customized code as much as possible. One of such trust is to use the database sorting algorithm instead of use your own sorting functions.

Following general software development principles, your source code file should have the proper header and inline comments to help me understand your program logic.

This is a relatively simple program, business logic wise, so it is not necessary to use multiple modules/files. However, I do expect and demand that functions should be used to achieve a proper level of abstraction in your program.

How to Submit

Your submission should include source code file(s) and Makefile. Upon typing make, an executable file named summaryreport should be generated.

If your program can't compile on our csci server (otter), you get 0 for this assignment. So at least compile and test run your program once on otter before submit.

Login to our csci server, in the directory that holds your source code file(s), submit using the following command:

~liuh/bin/submit 370 A8 .

You can use the following command to check what you have submitted to A8:

~liuh/bin/checksubmit 370 A8