public String doDelete() {
String back = "", qq = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
qq = "delete from monitors where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, id);
pstmt.executeUpdate();
message = "Deleted Successfully";
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
public String updateStatus(String new_status) {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq = "update monitors set status=? where id = ? ";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, new_status);
pstmt.setString(2, id);
pstmt.executeUpdate();
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
return back;
}
public String doSelect() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String qq =
"select external_id,device_id,name,asset_num,"
+ " serial_num,screen_size,model,type,"
+ "vertical_resolution,horizontal_resolution,"
+ "manufacturer,"
+ "date_format(received,'%m/%d/%Y'),"
+ "expected_age,status,notes,editable "
+ " from monitors where id=?";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
setExternal_id(rs.getString(1));
setDevice_id(rs.getString(2));
setName(rs.getString(3));
setAsset_num(rs.getString(4));
setSerial_num(rs.getString(5));
setScreen_size(rs.getString(6));
setModel(rs.getString(7));
setType(rs.getString(8));
setVertical_resolution(rs.getString(9));
setHorizontal_resolution(rs.getString(10));
setManufacturer(rs.getString(11));
setReceived(rs.getString(12));
setExpected_age(rs.getString(13));
setStatus(rs.getString(14));
setNotes(rs.getString(15));
setEditable(rs.getString(16));
} else {
return "Record " + id + " Not found";
}
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
public String doPartialUpdate() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq =
"update monitors set "
+ "asset_num=?, screen_size=?,received=?, expected_age=?, "
+ "notes=? "
+ "where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
int jj = 1;
if (asset_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, asset_num);
if (screen_size.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, screen_size);
if (received.equals("")) pstmt.setNull(jj++, Types.DATE);
else pstmt.setDate(jj++, new java.sql.Date(dateFormat.parse(received).getTime()));
pstmt.setString(jj++, "" + expected_age);
if (notes.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, notes);
pstmt.setString(6, id);
pstmt.executeUpdate();
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
if (back.equals("")) {
back = doSelect();
}
return back;
}
public String doSave() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String qq =
"insert into monitors values(0,?," + "?,?,?,?,?," + "?,?,?,?,?," + "?,?,'Active',?,'y')";
editable = "y";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
pstmt = con.prepareStatement(qq);
if (debug) {
logger.debug(qq);
}
back = fillPStatement(pstmt);
if (back.equals("")) {
pstmt.executeUpdate();
//
// get the id of the new record
//
qq = "select LAST_INSERT_ID() ";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
rs = pstmt.executeQuery();
if (rs.next()) {
id = rs.getString(1);
}
message = "Saved Successfully";
}
} catch (Exception ex) {
back += ex;
logger.error(ex);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
public String doUpdate() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq =
"update monitors set external_id=?,device_id=?,"
+ "name=?,asset_num=?,serial_num=?,screen_size=?,model=?,type=?,"
+ "vertical_resolution=?,horizontal_resolution=?,manufacturer=?,"
+ "received=?,expected_age=?, "
+ "notes=? "
+ // we do not change status here
"where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
back = fillPStatement(pstmt);
if (back.equals("")) {
pstmt.setString(15, id); // 17 - 2 (editable)
pstmt.executeUpdate();
}
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
if (back.equals("")) {
back = doSelect(); // to get status
}
return back;
}
public String find() {
String back = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = Helper.getConnection();
String qq =
"select r.id,r.asset_id,r.asset_num,r.type, "
+ " date_format(date,'%m/%d/%Y'),r.location_id,r.weight,r.description "
+ " from recycled_items r ";
String qw = "";
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
if (!location_id.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.location_id = ? ";
}
if (!asset_id.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.asset_id = ? ";
}
if (!type.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.type = ? ";
}
if (!date_from.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.date >= str_to_date('" + date_from + "','%m/%d/%Y')";
}
if (!date_to.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.date <= str_to_date('" + date_to + "','%m/%d/%Y')";
}
if (!qw.equals("")) {
qq = qq + " where " + qw;
}
qq = qq + " order by r.date DESC " + limit;
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
int jj = 1;
if (!location_id.equals("")) {
pstmt.setString(jj++, location_id);
}
if (!asset_id.equals("")) {
pstmt.setString(jj++, asset_id);
}
if (!type.equals("")) {
pstmt.setString(jj++, type);
}
rs = pstmt.executeQuery();
while (rs.next()) {
if (recycledItems == null) recycledItems = new ArrayList<RecycledItem>();
RecycledItem one =
new RecycledItem(
debug,
rs.getString(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
rs.getString(6),
rs.getString(7),
rs.getString(8));
recycledItems.add(one);
}
} catch (Exception ex) {
back += ex + " : " + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
return back;
}
