public boolean insertarAlquiler(
int idtrabajador,
int idCliente,
ArrayList<Detalle_Alquiler> lista_detalle,
int identificador) {
boolean resultado = false;
Connection cnn = null;
CallableStatement cstmt = null;
CallableStatement cstm1 = null;
CallableStatement cstm2 = null;
int id_alquiler = 0;
try {
cnn = BD.getConnection();
cnn.setAutoCommit(false);
String sql = "call spI_Alquiler (?,?,?,?);";
cstmt = cnn.prepareCall(sql);
cstmt.setInt(1, 2);
cstmt.setInt(2, idCliente);
cstmt.setInt(3, idtrabajador);
cstmt.setInt(4, identificador);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
id_alquiler = rs.getInt("int_id");
}
for (int i = 0; i < lista_detalle.size(); i++) {
String sql1 = "call spI_DetalleAlquiler(?,?,?,?,?,?,?);";
cstm1 = cnn.prepareCall(sql1);
cstm1.setInt(1, id_alquiler);
cstm1.setInt(2, lista_detalle.get(i).getMaterial_id());
cstm1.setInt(3, lista_detalle.get(i).getInt_cantidad());
cstm1.setDouble(4, lista_detalle.get(i).getDec_monto());
cstm1.setTimestamp(5, lista_detalle.get(i).getDat_fechfin());
cstm1.setTimestamp(6, lista_detalle.get(i).getDat_fechinicio());
cstm1.setInt(7, lista_detalle.get(i).getInt_horas());
cstm1.execute();
}
// Registrar Pagos
String sql2 = "call spI_Pagos_ByAlquiler(?,?);";
cstm2 = cnn.prepareCall(sql2);
cstm2.setInt(1, 1); // es el codigo del usuario cambiar despues
cstm2.setInt(2, id_alquiler);
cstm2.execute();
cnn.commit();
resultado = true;
} catch (SQLException s) {
try {
cnn.rollback();
} catch (SQLException b) {
}
System.out.println("aquà es :/ " + s);
} finally {
try {
cstmt.close();
cnn.close();
} catch (SQLException ex) {
}
}
return resultado;
}
public void setTimestamp(String parameterName, Timestamp x, Calendar cal) throws SQLException {
checkOpen();
try {
_stmt.setTimestamp(parameterName, x, cal);
} catch (SQLException e) {
handleException(e);
}
}
public void setTimestamp(String parameterName, Timestamp x) throws SQLException {
checkOpen();
try {
((CallableStatement) _stmt).setTimestamp(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
/** Sets the timestamp */
@Override
public void setTimestamp(String parameterName, Timestamp x, Calendar cal) throws SQLException {
try {
_cstmt.setTimestamp(parameterName, x, cal);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
public boolean insertarMovimiento(Movimiento m) {
boolean resultado = true;
Connection cnn = null;
CallableStatement cstmt = null;
try {
cnn = BD.getConnection();
cnn.setAutoCommit(false);
String sql = "call spI_Movimiento(?,?,?,?,?,?,?,?,?);";
cstmt = cnn.prepareCall(sql);
cstmt.setInt(1, m.getUsuario_id());
cstmt.setString(2, m.getVar_concepto());
cstmt.setDouble(3, m.getDec_monto());
cstmt.setInt(4, m.getInt_tipoOperacion());
cstmt.setTimestamp(5, m.getDat_fecregistro());
cstmt.setInt(6, m.getInt_tipoComprobante());
cstmt.setString(7, m.getVar_numeroComprobante());
cstmt.setDouble(8, m.getDec_cantidad());
cstmt.setInt(9, m.getInt_proveedor());
cstmt.execute();
cnn.commit();
} catch (SQLException a) {
try {
cnn.rollback();
} catch (SQLException b) {
System.out.println("" + b.toString());
} finally {
resultado = false;
}
System.out.println("error al registrar movimiento " + a.toString());
} finally {
try {
cstmt.close();
cnn.close();
} catch (SQLException ex) {
System.out.println("" + ex.getMessage());
}
}
return resultado;
}
/** @see java.sql.PreparedStatement#setTimestamp(int, java.sql.Timestamp, java.util.Calendar) */
public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {
original.setTimestamp(parameterIndex, x, cal);
}
/** @see java.sql.PreparedStatement#setTimestamp(int, java.sql.Timestamp) */
public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
original.setTimestamp(parameterIndex, x);
}
/**
* @see java.sql.CallableStatement#setTimestamp(java.lang.String, java.sql.Timestamp,
* java.util.Calendar)
*/
public void setTimestamp(String parameterName, Timestamp x, Calendar cal) throws SQLException {
original.setTimestamp(parameterName, x, cal);
}
/** @see java.sql.CallableStatement#setTimestamp(java.lang.String, java.sql.Timestamp) */
public void setTimestamp(String parameterName, Timestamp x) throws SQLException {
original.setTimestamp(parameterName, x);
}
private static void callSetMethod(CallableStatement cs, int arg, int type, StringBuilder strbuf)
throws Throwable {
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
strbuf.append("setBoolean(" + arg + ", true)");
cs.setBoolean(arg, true);
break;
case Types.TINYINT:
strbuf.append("setByte(" + arg + ", 6)");
cs.setByte(arg, (byte) 6);
break;
case Types.SMALLINT:
strbuf.append("setShort(" + arg + ", 66)");
cs.setShort(arg, (short) 66);
break;
case Types.INTEGER:
strbuf.append("setInt(" + arg + ", 666)");
cs.setInt(arg, 666);
break;
case Types.BIGINT:
strbuf.append("setLong(" + arg + ", 666)");
cs.setLong(arg, 666);
break;
case Types.FLOAT:
case Types.REAL:
strbuf.append("setFLoat(" + arg + ", 666)");
cs.setFloat(arg, 666);
break;
case Types.DOUBLE:
strbuf.append("setDouble(" + arg + ", 666)");
cs.setDouble(arg, 666);
break;
case Types.DECIMAL:
case Types.NUMERIC:
strbuf.append("setBigDecimal(" + arg + ", 666.666)");
BigDecimalHandler.setBigDecimalString(cs, arg, "666.666");
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
strbuf.append("setString(" + arg + ", \"Set via setString()\")");
cs.setString(arg, "Set via setString()");
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
strbuf.append("setBytes(" + arg + ", byte[])");
byte[] myarray = new byte[16];
myarray[0] = (byte) 255;
cs.setBytes(arg, myarray);
break;
case Types.DATE:
strbuf.append("setDate(" + arg + ", Date.valueOf(1999-09-09))");
cs.setDate(arg, Date.valueOf("1999-09-09"));
break;
case Types.TIME:
strbuf.append("setTime(" + arg + ", Time.valueOf(09:09:09))");
cs.setTime(arg, Time.valueOf("09:09:09"));
break;
case Types.TIMESTAMP:
strbuf.append("setTimestamp(" + arg + ", Timestamp.valueOf(1999-09-09 09:09:09.999))");
cs.setTimestamp(arg, Timestamp.valueOf("1999-09-09 09:09:09.999"));
break;
case Types.OTHER:
strbuf.append("setObject(" + arg + ", new BigInteger(666))");
cs.setObject(arg, new BigInteger("666"));
break;
default:
throw new Throwable("TEST ERROR: unexpected type " + type);
}
}
public void setTimestamp(String parameterName, Timestamp x) throws SQLException {
passThru.setTimestamp(parameterName, x);
}
/*
* prepareStatement
* This method follows the logic of preparedDataCommon.prepareStatement() except that since it is only
* called for CFPROCPARAM's it doesn't need to iterate over the data VectorArrayList. Instead it only
* needs to extract the one value.
*/
private void prepareStatement(String paramName, CallableStatement CallStatmt, Connection _conn)
throws dataNotSupportedException, cfmRunTimeException, SQLException {
// Map the CFML type to a JDBC type
int jType = getJdbcType(CallStatmt, cfSqlType);
paramName = paramName.replace("@", "");
if (passAsNull) {
// JDBC drivers don't recognize ORACLE_NCLOB so we need to pass it in as a Types.CHAR
if (jType == ORACLE_NCLOB) CallStatmt.setNull(paramName, Types.CHAR);
else CallStatmt.setNull(paramName, jType);
return;
}
// Get the value associated with this CFPROCPARAM
cfData _data = data.get(0);
switch (jType) {
// for MS SQL Server via JDBC-ODBC Bridge, if you try to use setString()
// instead of setObject(), it will pad VARCHAR columns when it shouldn't
case Types.CHAR:
case Types.VARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.LONGVARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case ORACLE_NCLOB:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
CallStatmt.setObject(paramName, ((cfBinaryData) _data).getByteArray(), jType);
break;
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
if (_data.getNumber().isInt()) {
CallStatmt.setInt(paramName, _data.getInt());
break;
}
// if not an int, fall through to next case
case Types.BIGINT:
double d = _data.getDouble();
if (d <= Long.MAX_VALUE) {
if (isSetLongSupported(_conn)) {
CallStatmt.setLong(paramName, _data.getLong());
} else {
CallStatmt.setDouble(paramName, d);
}
} else {
CallStatmt.setDouble(paramName, d);
}
break;
case Types.DECIMAL:
case Types.NUMERIC:
try {
// NOTE: if a customer is complaining about losing decimal places then make sure they
// are setting the scale properly in cfqueryparam. The default value for scale
// is 0 which causes all decimal places to be removed.
CallStatmt.setBigDecimal(
paramName,
new BigDecimal(_data.getDouble()).setScale(scale, BigDecimal.ROUND_HALF_UP));
break;
} catch (Exception e) {
// fall through to next case
}
case Types.FLOAT:
case Types.DOUBLE:
CallStatmt.setDouble(paramName, _data.getDouble());
break;
case Types.REAL:
CallStatmt.setFloat(paramName, new Float(_data.getDouble()).floatValue());
break;
case Types.DATE:
long date =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setDate(paramName, new java.sql.Date(date));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setDate() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(date, "dd-MMM-yy"));
}
break;
case Types.TIME:
long time =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setTime(paramName, new java.sql.Time(time));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setTime() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(time, "hh:mm aa"));
}
break;
case Types.TIMESTAMP:
long ts =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
CallStatmt.setTimestamp(paramName, new java.sql.Timestamp(ts));
break;
case Types.BIT:
CallStatmt.setBoolean(paramName, _data.getBoolean());
break;
case Types.NULL:
CallStatmt.setNull(paramName, getJdbcType(CallStatmt, cfSqlType));
break;
default:
throw newRunTimeException("Unsupported CFSQLTYPE: " + sqlType);
}
}
private void updateCustomerInfo(ResultSet rs, String mode) throws OptInCustomerException {
String insertSQL =
"INSERT INTO DAOptInCustomer ("
+ " CAPNumber, BusinessName1, Address1, Address2, Address3, "
+ " Address4, City, State, PostalCode, Country, "
+ " Contact, EMailAddress, "
+ " OptInStatus, LastStatusUpdateDt, SiteAdminCount, "
+ " AbeType, LastUpdateUserId, LastUpdateDt, BusinessName2, Optinattempt )"
+ " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'N', getDate(), 0, 'A', 'javaint', getDate(), ?, 0 )";
String updateSQL =
"UPDATE DAOptInCustomer "
+ "SET BusinessName1 = ?, Address1=?, Address2=?, Address3=?, Address4=?, "
+ " City=?, State=?, PostalCode=?, Country=?, Contact=?, EmailAddress=?, "
+ " LastUpdateUserId='javaint', LastUpdateDt=getDate(), BusinessName2=? "
+ " WHERE CAPNumber=? ";
String addressID = null;
String auAddressSQL = null;
String addr1 = null;
String addr2 = null;
String addr3 = null;
String addr4 = null;
String city = null;
String state = null;
String zip = null;
String country = null;
String name = null;
int totalCount = 0;
PreparedStatement pstmt = null;
PreparedStatement pstmtAddr = null;
/* Get Address Details using SP lpp_get_address */
PreparedStatement psLAPAUAddressDetails = null;
ResultSet rsAddress = null;
ResultSet rsSP = null;
CallableStatement cstmt = null;
try {
_appLog.writeAuditLog(
"Retrieving Address details for each AU and Updating DAOptInCustomer table ..");
cstmt = _ifxConn.prepareCall("{call lpp_get_address(?,?,?)}");
if (mode.equalsIgnoreCase("new")) {
pstmt = _webAppConn.prepareStatement(insertSQL);
} else {
pstmt = _webAppConn.prepareStatement(updateSQL);
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
while (rs.next()) {
try {
totalCount++;
// Set the au and CAPID
cstmt.setInt(1, rs.getInt(1));
cstmt.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
cstmt.setString(3, "M");
cstmt.execute();
rsSP = cstmt.getResultSet();
if (totalCount % 2000 == 0) {
System.out.println("Processed " + totalCount + " Rows..");
}
if (rsSP.next()) {
addressID = rsSP.getString(3);
/* Using addressID get address information from ptt_Address table */
auAddressSQL =
"SELECT a.addr_1_t, a.addr_2_t, a.addr_3_t, "
+ " a.addr_4_t, a.city_t, a.state_c, a.zip_t, p.value_t "
+ " FROM ptt_address a, outer ptt_standard_codes p "
+ " WHERE a.country_c = p.key_u and p.table_u = 10 and "
+ " current between p.effective_dt and p.termination_dt and "
+ " a.address_u = "
+ addressID;
pstmtAddr = _ifxConn.prepareStatement(auAddressSQL);
rsAddress = pstmtAddr.executeQuery();
if (rsAddress.next()) {
if (rsAddress.getString(1) != null) {
addr1 = rsAddress.getString(1).trim();
} else {
System.out.println("ADDRESS1 VALUE WAS NULL for address ID: " + addressID);
addr1 = "";
}
if (rsAddress.getString(2) != null) {
addr2 = rsAddress.getString(2).trim();
} else {
System.out.println("ADDRESS2 VALUE WAS NULL for address ID: " + addressID);
addr2 = "";
}
if (rsAddress.getString(3) != null) {
addr3 = rsAddress.getString(3).trim();
} else {
System.out.println("ADDRESS3 VALUE WAS NULL for address ID: " + addressID);
addr3 = "";
}
if (rsAddress.getString(4) != null) {
addr4 = rsAddress.getString(4).trim();
} else {
System.out.println("ADDRESS4 VALUE WAS NULL for address ID: " + addressID);
addr4 = "";
}
if (rsAddress.getString(5) != null) {
city = rsAddress.getString(5).trim();
} else {
System.out.println("CITY VALUE WAS NULL for address ID: " + addressID);
city = "";
}
if (rsAddress.getString(6) != null) {
state = rsAddress.getString(6).trim();
} else {
System.out.println("STATE VALUE WAS NULL for address ID: " + addressID);
state = "";
}
if (rsAddress.getString(7) != null) {
zip = rsAddress.getString(7).trim();
} else {
System.out.println("ZIP VALUE WAS NULL for address ID: " + addressID);
zip = "";
}
if (rsAddress.getString(8) != null) {
country = rsAddress.getString(8).trim();
} else {
country = "United States";
}
// If Country is United States, insert an empty space
if (country.equalsIgnoreCase("united states") || country.equalsIgnoreCase("usa"))
country = "";
// Name Formatting
if (rs.getString(6) != null && rs.getString(5) != null && rs.getString(7) != null) {
if (rs.getString(6).trim().length() > 0)
name =
rs.getString(5).trim()
+ " "
+ rs.getString(6).trim()
+ ". "
+ rs.getString(7).trim();
else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
} else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
// Adding Credentials
if (rs.getString(10) != null) {
if (rs.getString(10).trim().length() > 0)
name = name + ", " + rs.getString(10).trim();
}
updateData.setCAPID(rs.getString(2));
updateData.setAddress1(addr1);
updateData.setAddress2(addr2);
updateData.setAddress3(addr3);
updateData.setAddress4(addr4);
updateData.setCity(city);
updateData.setState(state);
updateData.setZip(zip);
updateData.setCountry(country);
updateData.setCompanyName(rs.getString(3));
updateData.setContactName(name);
updateData.setEmail(rs.getString(8));
updateData.setBusinessName2(rs.getString(9));
updateData.setAbeType("A"); // Not used here
// Insert mode (add new AUs)
if (mode.equalsIgnoreCase("new")) {
System.out.println("Inserting the row for : " + updateData.getCAPID());
pstmt.setString(1, updateData.getCAPID()); // -- CAPID
pstmt.setString(2, updateData.getCompanyName()); // -- CompanyName
pstmt.setString(3, updateData.getAddress1()); // -- Address1
pstmt.setString(4, updateData.getAddress2()); // -- Address2
pstmt.setString(5, updateData.getAddress3()); // -- Address3
pstmt.setString(6, updateData.getAddress4()); // -- Address4
pstmt.setString(7, updateData.getCity()); // -- City
pstmt.setString(8, updateData.getState()); // -- State
pstmt.setString(9, updateData.getZip()); // -- Zip
pstmt.setString(10, updateData.getCountry()); // -- Country
pstmt.setString(11, updateData.getContactName()); // -- Contact Name
pstmt.setString(12, updateData.getEmail()); // -- Email
pstmt.setString(13, updateData.getBusinessName2()); // -- BusinessName2
pstmt.executeUpdate();
}
// Update mode (refresh data for existing AUs)
else {
pstmt.setString(1, updateData.getCompanyName()); // -- Company name
pstmt.setString(2, updateData.getAddress1()); // -- Address1
pstmt.setString(3, updateData.getAddress2()); // -- Address2
pstmt.setString(4, updateData.getAddress3()); // -- Address3
pstmt.setString(5, updateData.getAddress4()); // -- Address4
pstmt.setString(6, updateData.getCity()); // -- City
pstmt.setString(7, updateData.getState()); // -- State
pstmt.setString(8, updateData.getZip()); // -- Zip
pstmt.setString(9, updateData.getCountry()); // -- Country
pstmt.setString(10, updateData.getContactName()); // -- Contact Name
pstmt.setString(11, updateData.getEmail()); // -- Email
pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
pstmt.setString(13, updateData.getCAPID()); // -- CAPID
pstmt.executeUpdate();
}
}
}
} catch (SQLException e) {
if (e.getErrorCode() == 2627) {
// user Exists in the table. Update the record
try {
_appLog.writeAuditLog(
"LAP Customer:Updated the status:" + "CAPID=" + updateData.getCAPID());
pstmt = _webAppConn.prepareStatement(updateSQL);
pstmt.setString(1, updateData.getCompanyName()); // -- Company name
pstmt.setString(2, updateData.getAddress1()); // -- Address1
pstmt.setString(3, updateData.getAddress2()); // -- Address2
pstmt.setString(4, updateData.getAddress3()); // -- Address3
pstmt.setString(5, updateData.getAddress4()); // -- Address4
pstmt.setString(6, updateData.getCity()); // -- City
pstmt.setString(7, updateData.getState()); // -- State
pstmt.setString(8, updateData.getZip()); // -- Zip
pstmt.setString(9, updateData.getCountry()); // -- Country
pstmt.setString(10, updateData.getContactName()); // -- Contact Name
pstmt.setString(11, updateData.getEmail()); // -- Email
pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
pstmt.setString(13, updateData.getCAPID()); // -- CAPID
pstmt.executeUpdate();
// Set the prepared statement back to the new mode
pstmt = _webAppConn.prepareStatement(insertSQL);
// Now Update the AbeType
String sqlString =
"Update dbo.DAOptInCustomer set AbeType='A' where CAPNumber = '"
+ updateData.getCAPID()
+ "'";
PreparedStatement p = _webAppConn.prepareStatement(sqlString);
p.executeUpdate();
p.close();
} catch (SQLException f) {
System.out.println("Error: Updating DAOptInCustomer table....");
f.printStackTrace();
throw new OptInCustomerException(f);
}
} else {
e.printStackTrace();
throw new OptInCustomerException(e);
}
}
}
if (mode.equalsIgnoreCase("refresh"))
_appLog.writeAuditLog(
"Part -- I Complete : Updated address and contact information for "
+ totalCount
+ " AUs in DAOptInCustomer table..");
else
_appLog.writeAuditLog(
"Part -- II Complete : Inserted " + totalCount + " new AUs in DAOptInCustomer table..");
} catch (SQLException e) {
e.printStackTrace();
}
}