/*
* InventoryItem.java
*/
import java.sql.*;
/** An object of the InventoryItem class represents a record in the
* inventory table.
*
* @author Timothy Paul Fox
* @version July 25, 2004, 9:47 PM
*/
public class InventoryItem {
private ItemID itemID;
private String catalogID;
private String itemCost;
private java.sql.Date dateAdded;
private java.sql.Date dateRemoved;
private String whyRemoved;
private Statement userStmt;
private Connection dbWire;
private ResultSet rowRS;
private boolean isNewRow;
private String fieldInsertList;
private String valueInsertList;
private String updateList;
private boolean costChanged;
private boolean removedFlag;
private boolean reasonChanged;
{
itemID = null;
catalogID = null;
dateAdded = dateRemoved = null;
itemCost = whyRemoved = null;
userStmt = null;
dbWire = null;
rowRS = null;
isNewRow = false;
resetAllChangeFlags();
}
private void resetAllChangeFlags() {
costChanged = removedFlag = reasonChanged = false;
} // end resetAllChangeFlags
private String quoteForSQL(String s) {
StringBuffer sb = new StringBuffer(s);
int qPos = -1;
int lastQPos = 0;
do { // escape any internal apostrophes
// O'Reilly --> O''Reilly
qPos = sb.indexOf("'", lastQPos);
if (qPos >= 0) {
sb.insert(qPos,'\'');
lastQPos = qPos + 2;
}
} while (qPos >= 0);
sb.insert(0, '\'');
sb.append('\'');
return sb.toString();
} // end quoteForSQL
private void makeUpdateList(boolean changeAll) {
int fieldCount = 0;
StringBuffer sb = new StringBuffer("");
if ((itemCost != null) && (changeAll || costChanged)) {
sb.append("cost = " + quoteForSQL(itemCost));
fieldCount++;
}
if ((dateRemoved != null) && (removedFlag)) {
if (fieldCount > 0)
sb.append(", ");
sb.append("date_removed = " +
quoteForSQL(dateRemoved.toString()) );
fieldCount++;
}
if ((whyRemoved != null) && (changeAll || reasonChanged)) {
if (fieldCount > 0)
sb.append(", ");
sb.append("why_removed = " + quoteForSQL(whyRemoved) );
fieldCount++;
}
updateList = sb.toString();
} // end makeUpdateList
private void makeInsertLists() {
StringBuffer fieldList = new StringBuffer("(");
StringBuffer valueList = new StringBuffer("(");
fieldList.append("item_id");
valueList.append(quoteForSQL(itemID.toString()));
fieldList.append(", ");
valueList.append(", ");
fieldList.append("catalog_id");
valueList.append(quoteForSQL(catalogID.toString()));
fieldList.append(", ");
valueList.append(", ");
fieldList.append("date_added");
valueList.append(quoteForSQL(dateAdded.toString()));
fieldList.append(")");
valueList.append(")");
fieldInsertList = fieldList.toString();
valueInsertList = valueList.toString();
} // end makeInsertLists
/** Creates a new record in the inventory table.
* The record created includes a new ItemID and the current date.
* @param catID the CatalogID of the item to be added to inventory
* @param dummy this parameter does nothing except differentiate this
* constructor from the one that finds an existing inventory record.
* @throws Exception probably SQLException, from a JDBC access attempt
*/
public InventoryItem(String catID, int dummy) throws Exception {
isNewRow = true;
itemID = new ItemID();
try {
CatalogItem testCat = new CatalogItem(catID);
catalogID = testCat.getCatalogID();
}
catch (Exception e) {
throw new IllegalArgumentException(
"InventoryItem(): could not confirm a catalog entry for " +
catID);
}
dateAdded = new java.sql.Date(System.currentTimeMillis());
makeInsertLists();
try {
makeDbConnection("root"); // opens dbWire, userStmt
int result = putToRow(
"INSERT INTO inventory " + fieldInsertList +
" VALUES " + valueInsertList );
if (result != 1)
throw new SQLException(
"InventoryItem: new record not written");
}
finally {
if (userStmt != null) userStmt.close();
if (dbWire != null) dbWire.close();
}
} // end InventoryItem -- new record constructor
/** writes any pending changes to the inventory
* table.
* @throws Exception whatever JDBC throws interacting with the database
*/
public void update() throws Exception {
makeUpdateList(false); // make list for changed fields only
try {
makeDbConnection("root"); // opens dbWire, userStmt
int result = putToRow(
"UPDATE inventory " +
"SET " + updateList +
" WHERE (item_id = " +
quoteForSQL(itemID.toString()) + ")");
if (result != 1)
throw new SQLException(
"InventoryItem: update() error");
resetAllChangeFlags();
}
finally {
if (userStmt != null) userStmt.close();
if (dbWire != null) dbWire.close();
}
} // end update()
/** Creates an InventoryItem object that represents an existing
* record in the inventory table.
* Finds a record in the inventory table whose
* primary key matches knownID, then creates an InventoryItem object
* and copies the record's data to it.
* @param knownID the primary key (ItemID) in an
* inventory record
* @throws Exception from not contacting the database,
* or not finding a matching record
*/
public InventoryItem(String knownID) throws Exception {
itemID = new ItemID(knownID);
try {
makeDbConnection("root");
getFromRow("SELECT * from inventory " +
"WHERE (item_id = " +
quoteForSQL(itemID.toString()) + ")");
rowRS.first();
catalogID = rowRS.getString("catalog_id");
itemCost = rowRS.getString("cost");
dateAdded = rowRS.getDate("date_added");
dateRemoved = rowRS.getDate("date_removed");
whyRemoved = rowRS.getString("why_removed");
}
finally {
if (userStmt != null) userStmt.close();
if (dbWire != null) dbWire.close();
}
} // end InventoryItem -- initializer to find existing record
private void getFromRow(String query) throws Exception {
rowRS = userStmt.executeQuery(query);
}
private void makeDbConnection(String user) throws Exception {
LibManDbConnection plug;
if (dbWire != null) {
dbWire.close();
}
plug = new LibManDbConnection(user);
dbWire = plug.connect();
userStmt = dbWire.createStatement();
} // end makeDbConnection
private int putToRow(String updateSpec) throws Exception{
return userStmt.executeUpdate(updateSpec);
}
/** shows the contents of the InventoryItem object.
* @return The output will be formatted as
* InventoryItem(itemID):
* CatalogItem(catalogID),
* Added (dateAdded)
* Removed (dateRemoved), (whyRemoved)
*
* NOTE: the 'Removed' line will not be present unless
* dateRemoved has been set.
*/
public String toString() {
StringBuffer sb = new StringBuffer("InventoryItem(");
sb.append(itemID.toString() + "): \n");
sb.append("CatalogItem(");
sb.append(catalogID + "), \n");
sb.append("Added " + dateAdded.toString() + "\n");
if (dateRemoved != null) {
sb.append("Removed " + dateAdded.toString());
if (whyRemoved != null)
sb.append(", " + whyRemoved);
}
sb.append("\n");
return sb.toString();
} // end toString()
/** gets the ItemID
* @return The primary key value for this record in the
* inventory table
*/
public String getItemID() {
return itemID.toString();
}
/** gets the CatalogID
* @return The primary key value of the item in the
* catalog tables that describes this
* inventory item.
*/
public String getCatalogID() {
return catalogID;
}
/** gets the cost of this item.
* @return the cost if the cost (an optional property) has been set,
* else returns the empty string "".
*/
public String getCost() {
return (itemCost == null)? "" : itemCost;
}
/** gets the date the item was added to inventory.
* @return the date in yyyy-mm-dd form.
*/
public String getDateAdded() {
return (dateAdded == null)? "" : dateAdded.toString();
}
/** gets the date the item was removed from active inventory.
* @return the date in yyyy-mm-dd form, or the empty string ""
* if the item has not been removed..
*/
public String getDateRemoved() {
return (dateRemoved == null)? "" : dateRemoved.toString();
}
/** gets a brief description of why the item was removed
* @return the reason, or the empty string "" if the item has
* not been removed..
*/
public String getWhyRemoved() {
return (whyRemoved == null)? "" : whyRemoved;
}
/** indicates that the item has been removed from active inventory
* @return true if the dateRemoved property has
* been set, otherwise false
*/
public boolean wasRemoved() /* throws Exception */ {
if (
(!isNewRow) && (dateRemoved != null) &&
(dateAdded != null) &&
(dateAdded.compareTo(dateRemoved) <= 0) // OK to remove same day?
)
return true;
else
return false;
}
/** marks an inventory record as 'removed'. The InventoryItem object
* must be one that was created by reading the
* inventory table.
* @throws Exception any exception from accessing the database, plus
* IllegalArgumentException if remove() is called on a record that
* has already been retired.
* @param why a brief (10 chars max) description of why the item was
* removed. (Examples: "lost", "worn out", "defaced") May be null.
*/
public void remove(String why) throws Exception {
int result;
if (isNewRow)
throw new IllegalArgumentException(
"InventoryItem:Cannot remove() a newly created record!");
if (wasRemoved())
throw new IllegalArgumentException(
"InventoryItem:Cannot remove() a record more than once!");
dateRemoved = new java.sql.Date(System.currentTimeMillis());
removedFlag = true;
setWhyRemoved(why, false);
update();
} // end retire()
/** sets the item's cost property.
* @param newS The Cost string. Maximum storable length is
* 6 characters. Must be non-blank to be stored.
* @param immediate If this is set true, the new
* data will be written immediately to the database.
* If false, the database will not be updated
* until the update() method is called.
* @throws Exception passes along whatever exception the update()
* method encounters when trying to update the database.
*/
public void setCost(String newS, boolean immediate)
throws Exception {
if (newS == null || newS.length() < 1) return;
itemCost = newS;
costChanged = true;
if (immediate) update();
}
/** sets the item's whyRemoved property.
* @param newS a brief description of why the item is being removed.
* Must be non-blank to be stored. Maximum storable length is
* 10 characters.
* @param immediate If this is set true, the new
* data will be written immediately to the database.
* If false, the database will not be updated
* until the update() method is called.
* @throws Exception passes along whatever exception the update()
* method encounters when trying to update the database.
*/
public void setWhyRemoved(String newS, boolean immediate)
throws Exception {
if (newS == null || newS.length() < 1) return;
whyRemoved = newS;
reasonChanged = true;
if (immediate) update();
}
} // end class CatalogItem