Ms Access Invoice Database by xfq12691

VIEWS: 0 PAGES: 5

More Info
									                         ITE-370 Programming Assignment #1
                       Control Break Processing Using ADO.NET
                                  Fall 2004 – Landry
The objective of this assignment is to demonstrate your ability to develop 3GL pseudocode and
code for a control-break problem, using counters, accumulators, and a correct, efficient, control-
break processing algorithm. You are to access a relational database programmatically through
database objects.

Specifications

The control break problem is a classic file processing problem. In this problem, a file ordered by
some field, called the control variable, is processed sequentially. Whenever the control variable
changes, a subtotal value is displayed in a summary row of a report. An example of a control
break problem is printing out a chronological listing of a company’s sales data, with subtotals
printed at the end of each month’s sales transactions.

In this assignment you are to design and implement a control-break solution for a specific
problem, using a database that I will provide. The database, named rental.mdb, is in MS-Access
format and has been uploaded to the landry/ITE-370 folder on the Share Drive.

The context for the assignment involves printing an invoice history report for an imaginary
Orange Beach rental company, Beach Rentals, Inc. Beach Rentals serves the tourist market by
renting recreational equipment, such as boats, bicycles, and skis, used at nearby parks and
beaches. Beach Rentals keeps a database of information on its customers, rental equipment, and
rentals.

A relationship diagram for this database, shown below, indicates the relationships among the
various tables. A reservation is made by a customer for one or more equipment items, each of
which is detailed on a line item of the reservation.

You task is to print out an invoice history report, that is, a report of all the invoices stored in the
database. An invoice is basically a reservation. The invoice number is the ResId field of the
reservation table.

For each invoice, you need to print three sets of data. First, print out a row with the invoice
number, invoice date, and customer first and last name. Next, print the line item details, one-by-
one, for that reservation (invoice). Count the number of rental items on the invoice, and
calculate the total invoice amount (in dollars), as you process the line items. Finally, you are to
print a summary row for each invoice, containing the number of line items and total invoice
amount. When you are done with one invoice, go on to the next one.

At the conclusion of the whole report, print out the number of line items processed and the total
dollar amount of all rental sales in the database.
Make no assumptions about the contents of the actual data. Do not write a for-loop, for example.
Your algorithm should work no matter how many or what rows of data are in the database. I
may test your application with a completely different database (same schema, different data).
Make only one pass through the entire file. Do not use queries or report designers (Crystal
Reports) on this assignment. Do use an implementation of the control break algorithm covered
in class.

Before you write any code, first think about how you would solve the problem. Develop an
algorithm expressed as a psedocode or flowchart solution that you will turn in. Use meaningful
variable names in your pseudo solution. Only when you have written the pseudocode or
developed a flowchart should you proceed to implement your solution in VB code.




                                               2          9c32941c-1245-4aae-96a1-1fa01fa4d182.doc
When you are ready to implement your solution, you are to create a VB.NET Windows
application that writes a report to the output window, using Console.Writeline().

You are to use the DataReader object in ADO.NET to access the results of a query that joins the
various tables together, creating a non-normalized relation to be used in the control-break
algorithm. The query to use is as follows:

SELECT Reservation.ResId, ResDate,
       FirstName + ' ' + LastName AS Customer, EquipType,
       PartNo, Description, DailyRate,
       DateDiff('d', StartDate, EndDate) + 1 AS Days,
       Days * DailyRate As Amount
FROM Customer, Reservation, Equipment, LineItems
WHERE Reservation.ResId=LineItems.ResId AND
      Reservation.CustomerId=Customer.CustomerId AND
      LineItems.EquipId=Equipment.EquipId
ORDER BY Reservation.ResId;

This query may be found in the given database under queries. It is named AllLineItemsDetail.

Database

For testing the program, use the database provided on the Share Drive. This example database
provides an adequate sample to test your application. Design your program for the schema
defined in this database, and do not modify this design. Do not “hardwire” your application to
work with a database with a specific name or in a specific location.

Allow the user to browse and select a database using the Open File Dialog.

The results of the above query should look something like this:

                                       AllLineItemsDetail
                                  Equip                                 Daily
ResId   ResDate     Customer               Part No     Description               Days   Amount
                                  Type                                  Rate
    1 10/15/2002 Steve Young     Boat     C-15     Canoe C-Series       $10.00      5     $50.00
    2 10/18/2002 Dan Marino      Boat     L-13     MichiCraft Double    $10.00      2     $20.00
                                                   Ended Canoe L-
                                                   Series
    2 10/18/2002 Dan Marino      Furniture C8YU    Beach Lounge Chair    $5.00      2     $10.00
    2 10/18/2002 Dan Marino      Swim      C975    Child Mask Fin        $4.00      1      $4.00
                                                   Snorkel Combo
    2 10/18/2002 Dan Marino      Raft      FC256 Super Fat Cat Float     $8.00      1      $8.00
                                                   Tube
    3 10/19/2002 Kenny Stabler   Furniture U9J7    8 Foot Beach          $4.00      2      $8.00
                                                   Umbrella
    3 10/19/2002 Kenny Stabler   ATV       LT-F160 Suzuki ATV           $30.00      1     $30.00
    3 10/19/2002 Kenny Stabler   Bicycle   M900    Cannondale M900      $15.00      4     $60.00
                                                   Mountain Bike
    3 10/19/2002 Kenny Stabler   Boat      L-13    MichiCraft Double    $10.00      3     $30.00
                                                   Ended Canoe L-
                                                   Series


                                                 3         9c32941c-1245-4aae-96a1-1fa01fa4d182.doc
                                             AllLineItemsDetail
                                        Equip                                  Daily
ResId   ResDate         Customer                 Part No     Description                Days   Amount
                                        Type                                   Rate
    4 10/19/2002 Roger Staubach       Raft      FC256    Super Fat Cat Float    $8.00      1      $8.00
                                                         Tube
    4 10/19/2002 Roger Staubach       Swim       P975    Mask Fin Snorkel       $5.00      1      $5.00
                                                         Combo
    4   10/19/2002   Roger Staubach   Bicycle    M500    Mountain Bike         $15.00      2     $30.00
    5   10/21/2002   John Unitas      Raft       M689    Mustang Float Tube     $8.00      2     $16.00
    5   10/21/2002   John Unitas      Bicycle    M500    Mountain Bike         $15.00      1     $15.00
    5   10/21/2002   John Unitas      Furniture C8YU     Beach Lounge Chair     $5.00      1      $5.00
    6   10/21/2002   Bobby Hebert     Bicycle    M900    Cannondale M900       $15.00      3     $45.00
                                                         Mountain Bike
    7 10/22/2002 Joe Montana          Bicycle    M900    Cannondale M900       $15.00      2     $30.00
                                                         Mountain Bike
    7 10/22/2002 Joe Montana          Furniture U9J7     8 Foot Beach           $4.00      2      $8.00
                                                         Umbrella
    7 10/22/2002 Joe Montana          Boat       T-15    MichiCraft Double     $10.00      1     $10.00
                                                         Ended Canoe T-
                                                         Series
   26 11/25/2002 Archie Manning       Boat       T-15    MichiCraft Double     $10.00      3     $30.00
                                                         Ended Canoe T-
                                                         Series
   26 11/25/2002 Archie Manning       Furniture U9J7     8 Foot Beach           $4.00      1      $4.00
                                                         Umbrella
   28    12/2/2002 Roger Staubach     ATV        LT-F160 Suzuki ATV            $30.00      1     $30.00
   28    12/2/2002 Roger Staubach     Boat       K899    Vagabond Spraydeck     $8.00      2     $16.00
                                                         Touring Kayak
   28    12/2/2002 Roger Staubach     ATV        LT-F160 Suzuki ATV            $30.00      1     $30.00
   30    12/6/2002 Archie Manning     Boat       K899    Vagabond Spraydeck     $8.00      2     $16.00
                                                         Touring Kayak
   30    12/6/2002 Archie Manning     Boat       K899    Vagabond Spraydeck     $8.00      2     $16.00
                                                         Touring Kayak
   30    12/6/2002 Archie Manning     Boat       K899    Vagabond Spraydeck     $8.00      2     $16.00
                                                         Touring Kayak
   31    12/6/2002 Roger Staubach     Boat       K900    Vagabond Spraydeck    $12.00      2     $24.00
                                                         Whitewater Kayak
   32    12/9/2002 Archie Manning     Boat       K899    Vagabond Spraydeck     $8.00     20    $160.00
                                                         Touring Kayak
   32    12/9/2002 Archie Manning     Bicycle    M900    Cannondale M900       $15.00      2     $30.00
                                                         Mountain Bike
   35 12/10/2002 Archie Manning       Watercraft JS750- One-Passenger Jet      $40.00      1     $40.00
                                                 C5      Ski
   35 12/10/2002 Archie Manning       Raft       FC256 Super Fat Cat Float      $8.00      1      $8.00
                                                         Tube



Output

Your results should be displayed in the output window. Your results should follow this format:

Invoice: 1      Invoice Date: 10/15/2002                    Customer: Steve Young

EquipType PartNo          Description                       Rate     Days      Amount
Boat      C-15            Canoe C-Series                    $10.00      5      $50.00

                                                        4        9c32941c-1245-4aae-96a1-1fa01fa4d182.doc
Total Line Items: 1                         Total Invoice Amount: $50.00


Invoice: 2     Invoice Date: 10/18/2002              Customer: Dan Marino

EquipType PartNo Description                         Rate      Days     Amount
Boat        L-13 MichiCraft Double
                 Ended Canoe L-Series                $10.00        2    $20.00
Furniture  C8YU Beach Lounge Chair                   $5.00         2    $10.00
Swim C975 Child Mask Fin Snorkel Combo               $4.00         1     $4.00
Raft FC256 Super Fat Cat Float Tube                  $8.00         1     $8.00

Total Line Items: 4                         Total Invoice Amount: $42.00

…

Grand Total Line Items: 32          Grand Total Invoice Amount: $812.00


Deliverables and Other Guidance

Follow the first version of our “shop standards,” the Visual Basic Programming Guidance. In
addition to these guidelines, you are also to turn in a hand-written version of your pseudocode or
flowchart that represents the control break algorithm you used.

Remember the first syllabus objective, too, about honesty and integrity, and consequences of not
doing your own work.

Due date: Friday, September 17 at the beginning of class
Grading: This assignment will be graded out of a possible 40 points.




                                                5           9c32941c-1245-4aae-96a1-1fa01fa4d182.doc

								
To top