public static Vector searchInvoices(
String sInvoiceDateBegin,
String sInvoiceDateEnd,
String sInvoiceNr,
String sAmountMin,
String sAmountMax) {
Vector invoices = new Vector();
PreparedStatement ps = null;
ResultSet rs = null;
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
// compose query
String sSql = "SELECT * FROM OC_EXTRAINSURARINVOICES WHERE ";
if (sInvoiceDateBegin.length() > 0) {
sSql += " OC_INSURARINVOICE_DATE >= ? AND";
}
if (sInvoiceDateEnd.length() > 0) {
sSql += " OC_INSURARINVOICE_DATE >= ? AND";
}
if (sInvoiceNr.length() > 0) {
sSql += " OC_INSURARINVOICE_OBJECTID = ? AND";
}
if ((sAmountMin.length() > 0) && (sAmountMax.length() > 0)) {
sSql += " OC_INSURARINVOICE_BALANCE BETWEEN ? AND ? AND";
} else if (sAmountMin.length() > 0) {
sSql += " OC_INSURARINVOICE_BALANCE >= ? AND";
} else if (sAmountMax.length() > 0) {
sSql += " OC_INSURARINVOICE_BALANCE <= ? AND";
}
// remove last AND
if (sSql.endsWith("AND")) {
sSql = sSql.substring(0, sSql.length() - 3);
}
sSql += " ORDER BY OC_INSURARINVOICE_DATE DESC";
ps = oc_conn.prepareStatement(sSql);
// set question marks
int qmIdx = 1;
if (sInvoiceNr.length() > 0) ps.setInt(qmIdx++, Integer.parseInt(sInvoiceNr));
if (sInvoiceDateBegin.length() > 0)
ps.setDate(qmIdx++, ScreenHelper.getSQLDate(sInvoiceDateBegin));
if (sInvoiceDateEnd.length() > 0)
ps.setDate(qmIdx++, ScreenHelper.getSQLDate(sInvoiceDateEnd));
if ((sAmountMin.length() > 0) && (sAmountMax.length() > 0)) {
ps.setDouble(qmIdx++, Double.parseDouble(sAmountMin));
ps.setDouble(qmIdx, Double.parseDouble(sAmountMax));
} else if (sAmountMin.length() > 0) {
ps.setDouble(qmIdx, Double.parseDouble(sAmountMin));
} else if (sAmountMax.length() > 0) {
ps.setDouble(qmIdx, Double.parseDouble(sAmountMax));
}
rs = ps.executeQuery();
ExtraInsurarInvoice invoice;
while (rs.next()) {
invoice = new ExtraInsurarInvoice();
invoice.setUid(
rs.getInt("OC_INSURARINVOICE_SERVERID")
+ "."
+ rs.getInt("OC_INSURARINVOICE_OBJECTID"));
invoice.setDate(rs.getTimestamp("OC_INSURARINVOICE_DATE"));
invoice.setInvoiceUid(rs.getInt("OC_INSURARINVOICE_ID") + "");
invoice.setInsurarUid(rs.getString("OC_INSURARINVOICE_INSURARUID"));
invoice.setCreateDateTime(rs.getTimestamp("OC_INSURARINVOICE_CREATETIME"));
invoice.setUpdateDateTime(rs.getTimestamp("OC_INSURARINVOICE_UPDATETIME"));
invoice.setUpdateUser(rs.getString("OC_INSURARINVOICE_UPDATEUID"));
invoice.setVersion(rs.getInt("OC_INSURARINVOICE_VERSION"));
invoice.setBalance(rs.getDouble("OC_INSURARINVOICE_BALANCE"));
invoice.setStatus(rs.getString("OC_INSURARINVOICE_STATUS"));
invoices.add(invoice);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return invoices;
}
public static Vector getInsurarInvoicesWhereDifferentStatus(String sStatus) {
Vector vInsurarInvoices = new Vector();
PreparedStatement ps = null;
ResultSet rs = null;
String sSelect = "";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
sSelect =
"SELECT * FROM OC_EXTRAINSURARINVOICES WHERE OC_INSURARINVOICE_STATUS not in ("
+ sStatus
+ ")";
ps = oc_conn.prepareStatement(sSelect);
rs = ps.executeQuery();
ExtraInsurarInvoice insurarInvoice;
while (rs.next()) {
insurarInvoice = new ExtraInsurarInvoice();
insurarInvoice.setUid(
rs.getInt("OC_INSURARINVOICE_SERVERID")
+ "."
+ rs.getInt("OC_INSURARINVOICE_OBJECTID"));
insurarInvoice.setDate(rs.getTimestamp("OC_INSURARINVOICE_DATE"));
insurarInvoice.setInvoiceUid(rs.getInt("OC_INSURARINVOICE_ID") + "");
insurarInvoice.setInsurarUid(rs.getString("OC_INSURARINVOICE_INSURARUID"));
insurarInvoice.setCreateDateTime(rs.getTimestamp("OC_INSURARINVOICE_CREATETIME"));
insurarInvoice.setUpdateDateTime(rs.getTimestamp("OC_INSURARINVOICE_UPDATETIME"));
insurarInvoice.setUpdateUser(rs.getString("OC_INSURARINVOICE_UPDATEUID"));
insurarInvoice.setVersion(rs.getInt("OC_INSURARINVOICE_VERSION"));
insurarInvoice.setBalance(rs.getDouble("OC_INSURARINVOICE_BALANCE"));
insurarInvoice.setStatus(rs.getString("OC_INSURARINVOICE_STATUS"));
vInsurarInvoices.add(insurarInvoice);
}
} catch (Exception e) {
e.printStackTrace();
Debug.println(
"OpenClinic => InsurarInvoice.java => getInsurarInvoicesWhereDifferentStatus => "
+ e.getMessage()
+ " = "
+ sSelect);
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vInsurarInvoices;
}
// --- GET -------------------------------------------------------------------------------------
public static ExtraInsurarInvoice get(String uid) {
ExtraInsurarInvoice insurarInvoice = new ExtraInsurarInvoice();
if (uid != null && uid.length() > 0) {
String[] ids = uid.split("\\.");
if (ids.length == 2) {
PreparedStatement ps = null;
ResultSet rs = null;
String sSelect =
"SELECT * FROM OC_EXTRAINSURARINVOICES WHERE OC_INSURARINVOICE_SERVERID = ? AND OC_INSURARINVOICE_OBJECTID = ?";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
ps = oc_conn.prepareStatement(sSelect);
ps.setInt(1, Integer.parseInt(ids[0]));
ps.setInt(2, Integer.parseInt(ids[1]));
rs = ps.executeQuery();
if (rs.next()) {
insurarInvoice.setUid(uid);
insurarInvoice.setDate(rs.getTimestamp("OC_INSURARINVOICE_DATE"));
insurarInvoice.setInvoiceUid(rs.getInt("OC_INSURARINVOICE_ID") + "");
insurarInvoice.setInsurarUid(rs.getString("OC_INSURARINVOICE_INSURARUID"));
insurarInvoice.setCreateDateTime(rs.getTimestamp("OC_INSURARINVOICE_CREATETIME"));
insurarInvoice.setUpdateDateTime(rs.getTimestamp("OC_INSURARINVOICE_UPDATETIME"));
insurarInvoice.setUpdateUser(rs.getString("OC_INSURARINVOICE_UPDATEUID"));
insurarInvoice.setVersion(rs.getInt("OC_INSURARINVOICE_VERSION"));
insurarInvoice.setBalance(rs.getDouble("OC_INSURARINVOICE_BALANCE"));
insurarInvoice.setStatus(rs.getString("OC_INSURARINVOICE_STATUS"));
}
rs.close();
ps.close();
insurarInvoice.debets =
Debet.getFullExtraInsurarDebetsViaInvoiceUid(insurarInvoice.getUid());
insurarInvoice.credits =
InsurarCredit.getInsurarCreditsViaInvoiceUID(insurarInvoice.getUid());
} catch (Exception e) {
Debug.println("OpenClinic => InsurarInvoice.java => get => " + e.getMessage());
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
return insurarInvoice;
}