/*
* (non-Javadoc)
*
* @see
* pe.com.viajes.negocio.dao.ServicioNoviosDao#registrarNovios(pe.com
* .logistica.bean.negocio.ProgramaNovios)
*/
@Override
public Integer registrarNovios(ProgramaNovios programaNovios) throws SQLException {
Integer codigoNovios = 0;
Connection conn = null;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresarprogramanovios(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
try {
conn = UtilConexion.obtenerConexion();
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.INTEGER);
cs.setInt(i++, programaNovios.getNovia().getCodigoEntero());
cs.setInt(i++, programaNovios.getNovio().getCodigoEntero());
cs.setInt(i++, programaNovios.getDestino().getCodigoEntero());
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaBoda()));
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaViaje()));
cs.setInt(i++, programaNovios.getMoneda().getCodigoEntero());
cs.setBigDecimal(i++, programaNovios.getCuotaInicial());
cs.setInt(i++, programaNovios.getNroDias());
cs.setInt(i++, programaNovios.getNroNoches());
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaShower()));
if (StringUtils.isNotBlank(programaNovios.getObservaciones())) {
cs.setString(i++, programaNovios.getObservaciones());
} else {
cs.setNull(i++, Types.VARCHAR);
}
cs.setBigDecimal(i++, programaNovios.getMontoTotalServiciosPrograma());
cs.setInt(i++, programaNovios.getIdServicio());
cs.setString(i++, programaNovios.getUsuarioCreacion());
cs.setString(i++, programaNovios.getIpCreacion());
cs.execute();
codigoNovios = cs.getInt(1);
} catch (SQLException e) {
codigoNovios = 0;
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
try {
if (conn != null) {
conn.close();
}
throw new SQLException(e);
} catch (SQLException e1) {
throw new SQLException(e);
}
}
}
return codigoNovios;
}
@SuppressWarnings("deprecation")
protected void spSalFactD(Map params) {
try {
Connection conn = getSession().connection();
CallableStatement cs =
conn.prepareCall("{call SP_HRSAL_FACTDT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
int branchId = ((Integer) params.get("branch.id")).intValue();
String hdNo = (String) params.get("fact.no");
int no = ((Integer) params.get("no")).intValue();
int itemId = ((Integer) params.get("item.id")).intValue();
int departId = ((Integer) params.get("depart.id")).intValue();
int personId = ((Integer) params.get("person.id")).intValue();
double amount = ((Double) params.get("amount")).doubleValue();
Date date = new Date(ObjectUtil.toCalendar(params.get("date")).getTimeInMillis());
String workerId = (String) params.get("person.workerId");
String type = (String) params.get("type");
int index = 1;
cs.setInt(index++, branchId);
cs.setString(index++, hdNo);
cs.setInt(index++, no);
cs.setInt(index++, itemId);
cs.setInt(index++, departId);
cs.setInt(index++, personId);
cs.setDouble(index++, amount);
cs.setDate(index++, date);
cs.setString(index++, workerId);
cs.setString(index++, type);
cs.execute();
} catch (Exception e) {
throw new HibernateException(e);
}
}
public void setDate(String parameterName, Date x, Calendar cal) throws SQLException {
checkOpen();
try {
_stmt.setDate(parameterName, x, cal);
} catch (SQLException e) {
handleException(e);
}
}
public Map<String, String> updateTask(String taskStatus) {
Map<String, String> resultMap = new HashMap<String, String>();
String status = "E";
String message = null;
XxntcCsCalendarActivitiesEOVOImpl vo = getXxntcCsCalendarActivitiesEOVO2Update();
Row row = vo.getCurrentRow();
Date startTime = (Date) row.getAttribute("StartTime");
Date endTime = (Date) row.getAttribute("EndTime");
Number incidentId = (Number) row.getAttribute("IncidentId");
Number resourceId = (Number) row.getAttribute("ResourceId");
_logger.warning("Before Calling API");
_logger.warning("taskStatus:" + taskStatus.toString());
_logger.warning("startTime:" + startTime.toString());
_logger.warning("endTime:" + endTime.toString());
_logger.warning("incidentId:" + incidentId.toString());
_logger.warning("resourceId:" + resourceId.toString());
CallableStatement cs = null;
String statement = "BEGIN XXNTC_CALENDAR_PKG.SCHEDULE_SR_PRC(?, ?, ?, ?, ?, ?, ?); END;";
try {
cs = getDBTransaction().createCallableStatement(statement, 0);
cs.registerOutParameter(6, Types.VARCHAR);
cs.registerOutParameter(7, Types.VARCHAR);
cs.setString(1, taskStatus);
cs.setDate(2, startTime.dateValue());
cs.setDate(3, endTime.dateValue());
cs.setInt(4, incidentId.intValue());
cs.setLong(5, resourceId.longValue());
cs.executeUpdate();
status = cs.getString(6);
message = cs.getString(7);
} catch (SQLException e) {
_logger.severe("Sql Exception during statement call", e);
} finally {
try {
if (cs != null) cs.close();
} catch (SQLException e) {
_logger.severe("Sql Exception While Closing Callable Statement", e);
}
}
resultMap.put("x_status", status);
resultMap.put("x_error_message", message);
_logger.warning("After API is called");
_logger.warning("x_status:" + status);
_logger.warning("x_error_message:" + message);
return resultMap;
}
public void setDate(String parameterName, Date x) throws SQLException {
checkOpen();
try {
((CallableStatement) _stmt).setDate(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
/* (non-Javadoc)
* @see pe.com.logistica.negocio.dao.ReporteVentasDao#reporteGeneralVentas(java.util.Date, java.util.Date)
*/
@Override
public List<DetalleServicioAgencia> reporteGeneralVentas(ReporteVentas reporteVentas)
throws SQLException {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
String sql = "{ ? = call reportes.fn_re_generalventas(?,?,?) }";
List<DetalleServicioAgencia> resultado = null;
try {
conn = UtilConexion.obtenerConexion();
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.OTHER);
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(reporteVentas.getFechaDesde()));
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(reporteVentas.getFechaHasta()));
if (reporteVentas.getVendedor().getCodigoEntero() != null
&& reporteVentas.getVendedor().getCodigoEntero().intValue() != 0) {
cs.setInt(i++, reporteVentas.getVendedor().getCodigoEntero().intValue());
} else {
cs.setNull(i++, Types.INTEGER);
}
cs.execute();
rs = (ResultSet) cs.getObject(1);
resultado = new ArrayList<DetalleServicioAgencia>();
DetalleServicioAgencia detalle = null;
while (rs.next()) {
detalle = new DetalleServicioAgencia();
detalle.getTipoServicio().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idtiposervicio"));
detalle.getTipoServicio().setNombre(UtilJdbc.obtenerCadena(rs, "nombre"));
detalle.setCantidad(UtilJdbc.obtenerNumero(rs, "cantidad"));
detalle.setTotalAgrupados(UtilJdbc.obtenerBigDecimal(rs, "montototal"));
detalle.setMontoComision(UtilJdbc.obtenerBigDecimal(rs, "montocomision"));
resultado.add(detalle);
}
} catch (SQLException e) {
throw new SQLException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
@Override
public boolean updateOrder(
Order order, java.util.Date oldDate, Double oldSubTotal, Double oldTotal) {
boolean updateOrder = false;
try {
cs = bd.getConn().prepareCall("{call updateOrder(?, ?, ?, ?, ?, ?)}");
cs.setDate(1, (Date) order.getDate());
cs.setDouble(2, order.getSubtotal());
cs.setDouble(3, order.getTotal());
cs.setDate(4, (Date) oldDate);
cs.setDouble(5, oldSubTotal);
cs.setDouble(6, oldTotal);
cs.executeUpdate();
updateOrder = true;
} catch (Exception e) {
System.out.println(e);
}
return updateOrder;
}
/** Sets the date */
@Override
public void setDate(String parameterName, Date x, Calendar cal) throws SQLException {
try {
_cstmt.setDate(parameterName, x, cal);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
public static List<String> getNames(Date date) throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:db2://127.0.0.1/test");
CallableStatement statement = connection.prepareCall("call getnames(?)");
statement.setDate(1, date);
ResultSet result = statement.executeQuery();
List<String> resultList = new ArrayList<String>();
while (result.next()) {
resultList.add(result.getString("name"));
}
result.close();
statement.close();
connection.close();
return resultList;
}
@Override
public boolean deleteOrder(Order order) {
boolean completedDelete = false;
try {
cs = bd.getConn().prepareCall("{call deleteOrder(?, ?, ?)}");
cs.setDate(1, (Date) order.getDate());
cs.setDouble(2, order.getSubtotal());
cs.setDouble(3, order.getTotal());
cs.executeUpdate();
completedDelete = true;
} catch (Exception e) {
System.out.println(e);
}
return completedDelete;
}
@Override
public boolean registrarInvitado(Cliente invitado, Integer idnovios, Connection conn)
throws SQLException, Exception {
boolean resultado = false;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresainvitado(?,?,?,?,?,?,?,?,?) }";
try {
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.BOOLEAN);
cs.setString(i++, invitado.getNombres());
cs.setString(i++, invitado.getApellidoPaterno());
cs.setString(i++, invitado.getApellidoMaterno());
if (invitado.getFechaNacimiento() != null) {
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(invitado.getFechaNacimiento()));
} else {
cs.setNull(i++, Types.DATE);
}
cs.setString(i++, invitado.getTelefonoInvitadoNovios());
cs.setString(i++, invitado.getCorreoElectronico());
cs.setInt(i++, idnovios);
cs.setString(i++, invitado.getUsuarioCreacion());
cs.setString(i++, invitado.getIpCreacion());
cs.execute();
resultado = true;
} catch (SQLException e) {
resultado = false;
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
public String getAvaliableViews(Number resourceId, java.sql.Date activeDate) {
String avaliableViews = null;
CallableStatement cs = null;
String statement = "BEGIN ? := XXNTC_CALENDAR_PKG.GET_AVAILABLE_CALENDAR_VIEWS (?, ?); END;";
try {
cs = getDBTransaction().createCallableStatement(statement, 0);
cs.registerOutParameter(1, Types.VARCHAR);
cs.setInt(2, resourceId.intValue());
cs.setDate(3, activeDate);
cs.executeUpdate();
avaliableViews = cs.getString(1);
} catch (SQLException e) {
_logger.severe("SQL Exception getAvaliableViews", e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
_logger.severe("SQL Exception getAvaliableViews", e);
}
}
return avaliableViews;
}
/*
* 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);
}
}
public void setDate(String parameterName, Date x) throws SQLException {
passThru.setDate(parameterName, x);
}
/** @see java.sql.PreparedStatement#setDate(int, java.sql.Date, java.util.Calendar) */
public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {
original.setDate(parameterIndex, x, cal);
}
/** @see java.sql.PreparedStatement#setDate(int, java.sql.Date) */
public void setDate(int parameterIndex, Date x) throws SQLException {
original.setDate(parameterIndex, x);
}
/**
* @see java.sql.CallableStatement#setDate(java.lang.String, java.sql.Date, java.util.Calendar)
*/
public void setDate(String parameterName, Date x, Calendar cal) throws SQLException {
original.setDate(parameterName, x, cal);
}
/** @see java.sql.CallableStatement#setDate(java.lang.String, java.sql.Date) */
public void setDate(String parameterName, Date x) throws SQLException {
original.setDate(parameterName, x);
}
public void setDate(String parameterName, Date x, Calendar cal) throws SQLException {
passThru.setDate(parameterName, x, cal);
}
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);
}
}
