public static void refreshLumberLog(LumberLog lumberLog) {
Logger logger = Logger.getLogger("dao");
StringBuilder lumberLogData =
new StringBuilder("select diameter, metric from lumberlog_diameter where lumberlog_id = ")
.append(lumberLog.getId());
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(true);
stm = con.createStatement();
logger.info(lumberLogData.toString());
rs = stm.executeQuery(lumberLogData.toString());
lumberLog.setMediumRadius(new ArrayList<Double>());
while (rs.next()) {
lumberLog.getMediumRadius().add(rs.getDouble(1));
}
} catch (Exception e) {
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
}
}
public List<LumberLog> findAll(LumberLogFilterDTO filter) {
Logger logger = Logger.getLogger("dao");
StringBuilder selectSql =
new StringBuilder(
" select l.*,i.label as plateName,st.name as stackName from lumberlog l left join idplate i on l.idplate = i.id left join lumberstack st on l.stack = st.id join lumberentry_to_lumberlog le on l.id = le.lumberlogid join lumberentry stoe on le.entryid = stoe.id join user u on stoe.user = u.ID");
if (filter != null) {
selectSql.append(" where ");
String operator = "";
if (filter.getStatus() != null) {
selectSql.append(operator).append(" l.Status=").append(filter.getStatus());
operator = " and ";
}
if (filter.getTransportEntryId() != null) {
selectSql
.append(operator)
.append(" l.TransportEntryId=")
.append(filter.getTransportEntryId());
operator = " and ";
}
}
List<LumberLog> result = new ArrayList<>();
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(true);
stm = con.createStatement();
logger.info(selectSql.toString());
rs = stm.executeQuery(selectSql.toString());
while (rs.next()) {
result.add(fromResultSet(rs));
}
} catch (Exception e) {
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
}
return result;
}
public List<LumberLog> getPendingEntryLumberLogs(Long userId) {
Logger logger = Logger.getLogger("dao");
StringBuilder lumberLogData =
new StringBuilder(
" select l.*,i.label as plateName,st.name as stackName from lumberlog l left join idplate i on l.idplate = i.id left join lumberstack st on l.stack = st.id join lumberentry_to_lumberlog le on l.id = le.lumberlogid join lumberentry stoe on le.entryid = stoe.id join user u on stoe.user = u.ID where l.Status=-1 and u.ID=")
.append(userId);
List<LumberLog> result = new ArrayList<>();
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(true);
stm = con.createStatement();
logger.info(lumberLogData.toString());
rs = stm.executeQuery(lumberLogData.toString());
while (rs.next()) {
result.add(fromResultSet(rs));
}
} catch (Exception e) {
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
}
return result;
}
protected LumberLog fromResultSet(ResultSet rs) throws SQLException {
LumberLog lumberLog = new LumberLog();
lumberLog.setId(rs.getLong("id"));
lumberLog.setLength(rs.getDouble("length"));
lumberLog.setRealLength(rs.getLong("reallength"));
lumberLog.setVolume(rs.getDouble("volume"));
lumberLog.setRealVolume(rs.getDouble("realvolume"));
lumberLog.setSmallRadius(rs.getDouble("small_diameter"));
lumberLog.setBigRadius(rs.getDouble("big_diameter"));
lumberLog.setLumberType(rs.getLong("lumbertype"));
lumberLog.setLumberClass(rs.getLong("lumberclass"));
lumberLog.setCutPlanId(rs.getLong("planId"));
LumberStack stack = new LumberStack();
stack.setName(rs.getString("stackName"));
stack.setId(rs.getLong("stack"));
lumberLog.setStack(stack);
IDPlate plate = new IDPlate();
plate.setId(rs.getLong("idplate"));
plate.setLabel(rs.getString("plateName"));
lumberLog.setPlate(plate);
lumberLog.setSupplierId(rs.getLong("SupplierId"));
lumberLog.setTransportCertifiateId(rs.getLong("TransportCertificateId"));
if (rs.wasNull()) {
lumberLog.setTransportCertifiateId(null);
}
lumberLog.setMarginPercent(rs.getInt("Margin"));
lumberLog.setMarginVolume(rs.getDouble("MarginVolume"));
lumberLog.setMarginRealVolume(rs.getDouble("RealMarginVolume"));
return lumberLog;
}
public static boolean saveLumberLog(
LumberLog lumberLog, LumberStack stack, LumberLogEntry stockEntry) {
Logger logger = Logger.getLogger("dao");
boolean status = false;
boolean saveNewEntry = false;
StringBuilder insertStockEntry = null;
if (stockEntry.getId() == null) {
saveNewEntry = true;
insertStockEntry = new StringBuilder("insert into lumberentry(user) values(");
insertStockEntry.append(stockEntry.getUser().getID()).append(")");
}
StringBuilder insertLumberLog =
new StringBuilder(
"insert into lumberlog( "
+ "small_diameter, medium_diameter, big_diameter, length, reallength, volume, realvolume, lumbertype, lumberclass,"
+ " idplate, stack, Status, SupplierId, TransportCertificateId, Margin, MarginVolume, RealMarginVolume, TransportEntryId) values (");
insertLumberLog.append(lumberLog.getSmallRadius()).append(",");
insertLumberLog.append("0").append(",");
insertLumberLog.append(lumberLog.getBigRadius()).append(",");
insertLumberLog.append(lumberLog.getLength()).append(",");
insertLumberLog.append(lumberLog.getRealLength()).append(",");
insertLumberLog.append(lumberLog.getVolume()).append(",");
insertLumberLog.append(lumberLog.getRealVolume()).append(",");
insertLumberLog.append(lumberLog.getLumberType()).append(",");
insertLumberLog.append(lumberLog.getLumberClass()).append(",");
insertLumberLog.append(lumberLog.getPlate().getId()).append(",");
insertLumberLog.append(stack.getId()).append(",").append(lumberLog.getStatus()).append(",");
insertLumberLog.append(lumberLog.getSupplierId()).append(",");
insertLumberLog
.append(
lumberLog.getTransportCertifiateId() != null
? lumberLog.getTransportCertifiateId()
: "NULL")
.append(",");
insertLumberLog.append(lumberLog.getMarginPercent()).append(",");
insertLumberLog.append(lumberLog.getMarginVolume()).append(",");
insertLumberLog.append(lumberLog.getMarginRealVolume()).append(",");
insertLumberLog.append(lumberLog.getTransportEntryId()).append(")");
StringBuilder insertMapEntrySql =
new StringBuilder("insert into lumberentry_to_lumberlog(entryid, lumberlogid) values (");
StringBuilder insertLumberLogDiameter =
new StringBuilder(
"insert into lumberlog_diameter(lumberlog_id, diameter, metric) values(?, ?, 1)");
Connection con = null;
Statement stm = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(false);
stm = con.createStatement();
logger.info(insertLumberLog.toString());
int rez = stm.executeUpdate(insertLumberLog.toString(), Statement.RETURN_GENERATED_KEYS);
if (rez > 0) {
rs = stm.getGeneratedKeys();
if (rs.next()) {
lumberLog.setId(rs.getLong(1));
}
}
if (rs != null) {
rs.close();
}
if (saveNewEntry) {
logger.info(insertStockEntry.toString());
rez = stm.executeUpdate(insertStockEntry.toString(), Statement.RETURN_GENERATED_KEYS);
if (rez > 0) {
rs = stm.getGeneratedKeys();
if (rs.next()) {
stockEntry.setId(rs.getLong(1));
}
}
}
if (lumberLog.getMediumRadius() != null && !lumberLog.getMediumRadius().isEmpty()) {
pstm = con.prepareStatement(insertLumberLogDiameter.toString());
for (Double radius : lumberLog.getMediumRadius()) {
pstm.setLong(1, lumberLog.getId());
pstm.setDouble(2, radius);
pstm.executeUpdate();
}
}
if (lumberLog.getId() != null && stockEntry.getId() != null) {
insertMapEntrySql
.append(stockEntry.getId())
.append(",")
.append(lumberLog.getId())
.append(")");
logger.info(insertMapEntrySql.toString());
rez = stm.executeUpdate(insertMapEntrySql.toString());
if (rez > 0) {
status = true;
IDPlateManager.removeAvailablePlate(lumberLog.getPlate());
}
}
} catch (Exception e) {
try {
con.rollback();
} catch (Exception ex) {
}
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
status = false;
} finally {
if (con != null) {
try {
con.setAutoCommit(true);
} catch (Exception ex) {
}
}
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
}
return status;
}
public static boolean markProcessedLumberLog(LumberLog lumberLog, String message) {
Logger logger = Logger.getLogger("dao");
boolean status = false;
StringBuilder selectSql =
new StringBuilder(
"select id, small_diameter, medium_diameter, big_diameter, length, volume, lumbertype, lumberclass, stack, idplate, planid, reallength, realvolume, status, SupplierId, TransportCertificateId, margin, MarginVolume, RealMarginVolume, TransportEntryId from lumberlog where id = ")
.append(lumberLog.getId());
StringBuilder deleteSql =
new StringBuilder("delete from lumberlog where id = ").append(lumberLog.getId());
StringBuilder insertSql =
new StringBuilder(
"insert into lumberlog_processed(userid, small_diameter, medium_diameter, big_diameter, length, volume, idlumbertype, idlumberclass, idstack, idplate, message, planId, reallength, realvolume, status, SupplierId, TransportCertificateId, Margin, MarginVolume, RealMarginVolume, TransportEntryId) values (");
insertSql.append(ConfigLocalManager.currentUser.getID());
StringBuilder cutPlan =
new StringBuilder(
" select PlanId, Percentage from CutPlanLumberLogDiagram cp inner join CutPlan plan on cp.PlanId = plan.id where plan.status = 0 and cp.LumberLogIDPlate = '")
.append(DataAccess.escapeString(lumberLog.getPlate().getLabel()))
.append("'");
StringBuilder checkLastFromPlan =
new StringBuilder("select count(*) from lumberlog where planId = ");
StringBuilder updateCutPlan = new StringBuilder(" update CutPlan set ");
StringBuilder planPiecesUpdate =
new StringBuilder(
"update cutplanproduct set ProcessedPieces = ProcessedPieces + ? where PlanId= ? and ProductName =?");
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
GeneralResponse diagramResponse = null;
if (lumberLog.getCutPlanId() != null) {
diagramResponse =
CutPlanDAO.getCutDiagram(lumberLog.getPlate(), lumberLog.getCutPlanId().intValue());
}
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(false);
stm = con.createStatement();
logger.info(selectSql.toString());
rs = stm.executeQuery(selectSql.toString());
if (rs.next()) {
insertSql.append(",").append(rs.getDouble(2));
insertSql.append(",").append(rs.getDouble(3));
insertSql.append(",").append(rs.getDouble(4));
insertSql.append(",").append(rs.getDouble(5));
insertSql.append(",").append(rs.getDouble(6));
insertSql.append(",").append(rs.getInt(7));
insertSql.append(",").append(rs.getInt(8));
insertSql.append(",").append(rs.getInt(9));
insertSql.append(",").append(rs.getInt(10));
insertSql.append(",'").append(DataAccess.escapeString(message)).append("'");
int planId = rs.getInt(11);
insertSql.append(",").append(planId);
insertSql.append(",").append(rs.getDouble(12)); // reallength
insertSql.append(",").append(rs.getDouble(13)); // realvolume
insertSql.append(",").append(rs.getInt(14)); // status
insertSql.append(",").append(rs.getLong(15)); // supplierid
insertSql.append(",").append(rs.getLong(16)); // transport certificate id
insertSql.append(",").append(rs.getInt(17)); // margin
insertSql.append(",").append(rs.getDouble(18)); // marginvolume
insertSql.append(",").append(rs.getDouble(19)); // realmarginvolume
insertSql.append(",").append(rs.getLong(20)); // transport entry id
insertSql.append(")");
}
logger.info(insertSql.toString());
int insertRez = stm.executeUpdate(insertSql.toString());
logger.info(deleteSql.toString());
int deleteRez = stm.executeUpdate(deleteSql.toString());
if (insertRez > 0 && deleteRez > 0) {
status = true;
logger.info(cutPlan.toString());
rs = stm.executeQuery(cutPlan.toString());
Integer planId = null;
if (rs.next()) {
planId = rs.getInt(1);
Double percent = rs.getDouble(2);
updateCutPlan
.append("complete = complete + ")
.append(percent)
.append(" where id=" + planId);
}
if (planId != null) {
rs = stm.executeQuery(checkLastFromPlan.append(planId).toString());
if (rs.next()) {
int count = rs.getInt(1);
if (count == 0) {
updateCutPlan.append(",status=1 ").append(" where id = ").append(planId);
}
}
logger.info(updateCutPlan.toString());
stm.executeUpdate(updateCutPlan.toString());
}
IDPlateManager.addAvailablePlate(lumberLog.getPlate());
if (diagramResponse != null) {
if (diagramResponse.getCode() == 200) {
CutDiagram diagram = (CutDiagram) diagramResponse.getData();
PreparedStatement pstm = con.prepareStatement(planPiecesUpdate.toString());
Iterator<Map.Entry<String, Integer>> cutInfo =
diagram.cutInfo.cutPieces.entrySet().iterator();
while (cutInfo.hasNext()) {
Map.Entry<String, Integer> productInfo = cutInfo.next();
String productName = productInfo.getKey();
Integer pieces = productInfo.getValue();
System.out.println(productName + " " + pieces);
pstm.setInt(1, pieces);
pstm.setInt(2, lumberLog.getCutPlanId().intValue());
pstm.setString(3, productName);
pstm.executeUpdate();
}
pstm.close();
}
}
}
con.commit();
} catch (Exception e) {
try {
con.rollback();
} catch (SQLException ex) {
}
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
status = false;
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
if (con != null) {
try {
con.setAutoCommit(true);
} catch (Exception e) {
}
}
}
return status;
}