@Override
public String getSQLWhere() {
StringBuffer sql = new StringBuffer();
if (txtDocumentNo.getText().length() > 0) sql.append(" AND UPPER(o.DocumentNo) LIKE ?");
if (txtDescription.getText().length() > 0) sql.append(" AND UPPER(o.Description) LIKE ?");
if (txtOrderRef.getText().length() > 0) sql.append(" AND UPPER(o.POReference) LIKE ?");
//
if (editorBPartner.getValue() != null) sql.append(" AND o.C_BPartner_ID=?");
//
Date fromDate = null;
Date toDate = null;
try {
fromDate = dateFrom.getValue();
} catch (WrongValueException e) {
}
try {
toDate = dateTo.getValue();
} catch (WrongValueException e) {
}
if (fromDate == null && toDate != null) {
sql.append(" AND TRUNC(o.DateOrdered) <= ?");
} else if (fromDate != null && toDate == null) {
sql.append(" AND TRUNC(o.DateOrdered) >= ?");
} else if (fromDate != null && toDate != null) {
sql.append(" AND TRUNC(o.DateOrdered) BETWEEN ? AND ?");
}
//
Double fromAmount = null;
Double toAmount = null;
if (amountFrom.getText() != null && amountFrom.getText().trim().length() > 0) {
try {
fromAmount = Double.parseDouble(amountFrom.getText());
} catch (NumberFormatException e) {
}
}
if (amountTo.getText() != null && amountTo.getText().trim().length() > 0) {
try {
toAmount = Double.parseDouble(amountTo.getText());
} catch (NumberFormatException e) {
}
}
if (fromAmount == null && toAmount != null) {
sql.append(" AND o.GrandTotal <= ?");
} else if (fromAmount != null && toAmount == null) {
sql.append(" AND o.GrandTotal >= ?");
} else if (fromAmount != null && toAmount != null) {
sql.append(" AND o.GrandTotal BETWEEN ? AND ?");
}
sql.append(" AND o.IsSOTrx=?");
log.finer(sql.toString());
return sql.toString();
}
@Override
protected void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException {
int index = 1;
if (txtDocumentNo.getText().length() > 0) pstmt.setString(index++, getSQLText(txtDocumentNo));
if (txtDescription.getText().length() > 0) pstmt.setString(index++, getSQLText(txtDescription));
if (txtOrderRef.getText().length() > 0) pstmt.setString(index++, getSQLText(txtOrderRef));
//
if (editorBPartner.getValue() != null) {
Integer bp = (Integer) editorBPartner.getValue();
pstmt.setInt(index++, bp.intValue());
log.fine("BPartner=" + bp);
}
//
Date fromD = null;
Date toD = null;
Timestamp from = null;
Timestamp to = null;
try {
if (dateFrom.getValue() != null) {
fromD = dateFrom.getValue();
from = new Timestamp(fromD.getTime());
}
} catch (WrongValueException e) {
}
try {
if (dateTo.getValue() != null) {
toD = dateTo.getValue();
to = new Timestamp(toD.getTime());
}
} catch (WrongValueException e) {
}
log.fine("Date From=" + from + ", To=" + to);
if (from == null && to != null) {
pstmt.setTimestamp(index++, to);
} else if (from != null && to == null) {
pstmt.setTimestamp(index++, from);
} else if (from != null && to != null) {
pstmt.setTimestamp(index++, from);
pstmt.setTimestamp(index++, to);
}
//
BigDecimal fromBD = null;
BigDecimal toBD = null;
Double fromAmt = null;
Double toAmt = null;
if (amountFrom.getText() != null && amountFrom.getText().trim().length() > 0) {
try {
fromAmt = Double.parseDouble(amountFrom.getText());
fromBD = BigDecimal.valueOf(fromAmt);
} catch (Exception e) {
}
}
if (amountTo.getText() != null && amountTo.getText().trim().length() > 0) {
try {
toAmt = Double.parseDouble(amountTo.getText());
toBD = BigDecimal.valueOf(toAmt);
} catch (Exception e) {
}
}
if (fromBD == null && toBD != null) {
pstmt.setBigDecimal(index++, toBD);
} else if (fromBD != null && toBD == null) {
pstmt.setBigDecimal(index++, fromBD);
} else if (fromBD != null && toBD != null) {
pstmt.setBigDecimal(index++, fromBD);
pstmt.setBigDecimal(index++, toBD);
}
pstmt.setString(index++, isSoTrx.isChecked() ? "Y" : "N");
}