jlschuma / databaseproj Goto Github PK

Look at issue 13 (closed) for details

We should have two seperate command queries

JUST MY STORE
SELECT storeID, merchandiseID, quantity, price
FROM Store s, StoreItem si
WHERE s.id = si.storeID AND si.merchandiseID =1 and StoreId=1

ALL Stores
SELECT storeID, merchandiseID, quantity, price
FROM Store s, StoreItem si
WHERE s.id = si.storeID AND si.merchandiseID =1;

Check length of field. If it's 3 chars say, add an extra tab so field line up in DatabaseManager

To discuss as a team.. probably easy to do and we could have 1 store named "Warehouse" to match the spec.

StoreItem;
Merchandise;
Vendor;
Store;
VendorPayment;

Check their Store or another Store's Inventory (a Warehouse is a special type of Store)
Review Bills
Orders
Generate Customer Purchase History
Generate Vendor Purchase History
Review Customer Order
Review Store Inventory
Generate Vendor Purchase History
Show total purchases from a vendor for the store for a time period
Show total purchases from a customer for the store for a time period
Show inventory of all stores summed
Show bills of all stores or a particular store summed
Show vendor bills of all stores or a particular store summed

Needs to be checked on CommandView and in a few other places. Look for arg.setValue

Aka if the paramter type is an Int, should tell user to re-enter if they put "adfsafd" in

If you have a chance please do, otherwise we can do quickly as a group.

You'll get a null pointer exception when using update customer account information if the customer you are trying to update does not have a DOB in the database which isn't required.

I'm thinking this would also be a problem for anything else that's not required also.

Since Jessica created the update back end and knows best how this works I'm assigning to her.

From the Sales, Billing Etc.. Screen Should have a last option for "Exit"

From the "Enter your user id, Should have a "Enter Q to Quit"

I'll do commands these in a transaction, these are a bit more complicated..
SpecialOrderItems;
SpecialOrder;
VendorBillItems;
VendorBill;

CustomerBill;
CustomerBillingCycle;
CustomerBillItems;

Aka don't prompt them for a User if the query should be using theres

This only affects
Insert VendorPayment;
Insert CustomerPayment; - when this one is done it also needs to update the CustomerCycle to Paid --- needs discussion
Select Staffmember doing an inventory check at a particular should check their store
Select Show Employees, should list the employees for the store
Select Show inventory for store should default to user's store

Follow what Insert CustomOrder does

Move from VendorBill

1. Submit all source code (including all the auxiliary files that are needed for a successful standalone compilation of your source code by the course staff) to the submit board
2. Doc with transactions highlighted in code and described
3. highlight high-level design decisions, which are any choices/decisions that you had to make when designing your database and applications. (One example of high-level design decisions that you have taken is what architecture you have chosen for your code.)
4. As part of the documentation submitted, explain who in your team played what functional role (e.g., software engineer, database designer/administrator, etc, see above under Organization) in each part (1 through 3) of the project. Submit the documentation.

In, InsertCustomerBill -- don't use addMonth
also in SendSpecialOrderToCustomer -- don't use addMonth

ShowAllCustomerBills.java
ShowAllCustomerBillsByStore.java
ShowAllVendorBills.java
ShowAllVendorBillsByStore.java

Please test too :-)

Need to modify the InsertVendorBill command to work properly

Like all the other create's

Aka instead of a command number they enter "blah".. app falls over

In DatabaseManager

before all executeUpdate commands print out the PreparedStatement's SQL and Parameters

Example stat.executeQuery();
- we want to see the SQL and parameters
-toString() may do this out of the box. If not try to get the sql and getParametersMetaData() iterate and print the parameter values. If you have issues with this just log the sql and i'll go add the parameters later.

So I had a read through the project narrative ( http://courses.ncsu.edu/csc540/lec/001/wrap/project/narrative.pdf) and I think we're missing the below points:

1. Sales Staff should be able to Update Special Order
2. Sales Staff should be able to Bill a Customer (Take Payment?)
3. Special Order sends to Customers Address
4. Report the purchase history for a given customer and for a certain time period
5. Return information on all the customers a given salesperson assisted during a certain time period
6. Return information on Books-A-Thousand’ staff grouped by their role.iod

Most of these I think should be minor changes, but point 3 raises some questions. I think we can do this in our current model by

1. Making price visable on SpecialOrder
2. Stocking person would enter a CustomerBill when their order comes in, and ship it out
   Thoughts on this?

Also does anyone see other things we may have missed in the narrative or have questions on the ones I pointed out above?

Finishing writing sql file for data for demo.

Adding this to add a command to check whether a certain piece of merchandise will is available at all stores:

SELECT storeID, merchandiseID, quantity, price
FROM Store s, StoreItem si
WHERE s.id = si.storeID AND si.merchandiseID =1

I discussed this in Issue #3 so let me know if there are any questions.

1. Prep the database with the demo-data.sql (make sure it's the demo sql)
2. Follow our doc commands to compile and run the code
3. Run the script (in readme), keeping note of all id's. They will be different because there is more data in the demo. Make sure you do this using the method in 2 (not from eclipse)
4. Submit code and Doc.

Make the command for Inserting a Customer Payment
This is tricky cause it must

1. check to make sure the supplied CustomerBillingCycle exists (as done in update command)
2. Insert into CustomerPayment (as done in Create/Insert commands)
3. Update CustomerBillingCycle to paid (as done in update commands)

You may want to look at how the Update/Insert Commands are done and extend them

Also alter the command for InsertVendorPayment
It must (very similarly)

1. make sure the vendor bill exists
2. Insert into VendorPayment
3. Update VendorBill to paid

Almost identical to InsertSpecial Order

Vendor Inserts into tables
VendorBillItems;
VendorBill;

Customer Inserts into tables
VendorBillItems;
VendorBill;

Follow the structure used in UpdateMerchandise and UpdateAccount

Update Command classes needed for

-Update Bill status
update CustomerBill
set Status='Paid'
where id=3

-Update Billing Cycle
UPDATE CustomerBillingCycle
SET status = 'billed', startdate='01-MAY-12',enddate='30-May-12'
WHERE id = 2;

• Change Status of Special Order
  UPDATE SpecialOrderItems
  SET status = 'canceled'
  WHERE specialOrderID = 3 AND MerchandiseID = 2;
• Update vendor
  UPDATE Vendor
  SET name = ’New Name’, phoneNumber = ’111-111-1111’, address = ’101 Way’
  WHERE id = 1;
• Update an Employee
  UPDATE Staff
  SET storeID = 3, Salary = 1111, phoneNumber = ’919-999-9999’, name = ’New Name’, age = 10, gender = ’F’, title = ’Dr.’, department = ’Manager’, address = ’Somewhere’
  WHERE id = 3;

UPDATE Staff
SET storeID = 3, salary = 1111, phoneNumber = ’919-999-9999’, name = ’New Name’, age = 10, gender = ’F’, title = ’Dr.’, department = ’Billing’, address = ’Somewhere’
WHERE id = 3;

This is the same as Issue #32.

Just making this as a note to myself to test the script since I've made some changes.