public static boolean setStatusOpen(String sInvoiceID, String UserId) {
PreparedStatement ps = null;
ResultSet rs = null;
boolean okQuery = false;
String sSelect =
"update OC_EXTRAINSURARINVOICES SET OC_INSURARINVOICE_STATUS ='open',OC_INSURARINVOICE_UPDATETIME=?,OC_INSURARINVOICE_UPDATEUID=? WHERE OC_INSURARINVOICE_OBJECTID = ? ";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
ps = oc_conn.prepareStatement(sSelect);
ps.setTimestamp(1, new Timestamp(new java.util.Date().getTime())); // now
ps.setString(2, UserId);
ps.setInt(3, Integer.parseInt(sInvoiceID));
okQuery = (ps.executeUpdate() > 0);
} catch (Exception e) {
Debug.println("OpenClinic => ExtraInsurarInvoice.java => setStatusOpen => " + 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 okQuery;
}
// --- EXISTS ----------------------------------------------------------------------------------
// checks the database for a record with the same UNIQUE KEYS as 'this'.
public String exists() {
if (Debug.enabled) Debug.println("@@@ USERPRODUCT exists ? @@@");
PreparedStatement ps = null;
ResultSet rs = null;
String uid = "";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
String sSelect =
"SELECT OC_PRODUCT_PRODUCTSTOCKUID FROM OC_USERPRODUCTS"
+ " WHERE OC_PRODUCT_USERID = ? AND OC_PRODUCT_PRODUCTUID = ?";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, this.userId);
ps.setString(2, this.productUid);
rs = ps.executeQuery();
if (rs.next()) {
uid = rs.getString("OC_PRODUCT_PRODUCTSTOCKUID");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return uid;
}
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;
}
// --- GET -------------------------------------------------------------------------------------
public static UserProduct get(String userId, String productUid) {
UserProduct userProduct = null;
PreparedStatement ps = null;
ResultSet rs = null;
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
String sSelect =
"SELECT OC_PRODUCT_PRODUCTSTOCKUID FROM OC_USERPRODUCTS"
+ " WHERE OC_PRODUCT_USERID = ? AND OC_PRODUCT_PRODUCTUID = ?";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, userId);
ps.setString(2, productUid);
rs = ps.executeQuery();
// get data from DB
if (rs.next()) {
userProduct = new UserProduct();
// unique key (instead of uid)
userProduct.setUserId(userId);
userProduct.setProductUid(productUid);
// productStock
String productStockUid =
ScreenHelper.checkString(rs.getString("OC_PRODUCT_PRODUCTSTOCKUID"));
if (productStockUid.length() > 0) {
userProduct.setProductStockUid(productStockUid);
}
} else {
throw new Exception("ERROR : USERPRODUCT " + userId + "," + productUid + " NOT FOUND");
}
} catch (Exception e) {
if (e.getMessage().endsWith("NOT FOUND")) {
Debug.println(e.getMessage());
} else {
e.printStackTrace();
}
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return userProduct;
}
// --- GET DEBETS FOR INVOICE ------------------------------------------------------------------
public static Vector getDebetsForInvoiceSortByDate(String sInvoiceUid) {
PreparedStatement ps = null;
ResultSet rs = null;
SortedMap sortedDebets = new TreeMap();
Vector debets = new Vector();
String sSelect = "";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
sSelect =
"SELECT a.lastname, a.firstname, d.*,e.*,c.*"
+ " FROM OC_DEBETS d, OC_EXTRAINSURARINVOICES i, OC_ENCOUNTERS e, AdminView a, OC_PRESTATIONS c"
+ " WHERE d.OC_DEBET_EXTRAINSURARINVOICEUID = ?"
+ " AND i.OC_INSURARINVOICE_OBJECTID = replace(d.OC_DEBET_EXTRAINSURARINVOICEUID,'"
+ MedwanQuery.getInstance().getConfigString("serverId")
+ ".','')"
+ " AND e.OC_ENCOUNTER_OBJECTID = replace(d.OC_DEBET_ENCOUNTERUID,'"
+ MedwanQuery.getInstance().getConfigString("serverId")
+ ".','')"
+ " AND c.OC_PRESTATION_OBJECTID = replace(d.OC_DEBET_PRESTATIONUID,'"
+ MedwanQuery.getInstance().getConfigString("serverId")
+ ".','')"
+ " AND e.OC_ENCOUNTER_PATIENTUID = a.personid"
+ " ORDER BY OC_DEBET_DATE,lastname,firstname,OC_DEBET_PATIENTINVOICEUID";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, sInvoiceUid);
rs = ps.executeQuery();
Debet debet;
while (rs.next()) {
debet = new Debet();
debet.setUid(rs.getInt("OC_DEBET_SERVERID") + "." + rs.getInt("OC_DEBET_OBJECTID"));
debet.setDate(rs.getTimestamp("OC_DEBET_DATE"));
debet.setAmount(rs.getDouble("OC_DEBET_AMOUNT"));
debet.setInsurarAmount(rs.getDouble("OC_DEBET_INSURARAMOUNT"));
debet.setInsuranceUid(rs.getString("OC_DEBET_INSURANCEUID"));
debet.setPrestationUid(rs.getString("OC_DEBET_PRESTATIONUID"));
debet.setEncounterUid(rs.getString("OC_DEBET_ENCOUNTERUID"));
debet.setSupplierUid(rs.getString("OC_DEBET_SUPPLIERUID"));
debet.setPatientInvoiceUid(rs.getString("OC_DEBET_PATIENTINVOICEUID"));
debet.setInsurarInvoiceUid(rs.getString("OC_DEBET_INSURARINVOICEUID"));
debet.setComment(rs.getString("OC_DEBET_COMMENT"));
debet.setCredited(rs.getInt("OC_DEBET_CREDITED"));
debet.setQuantity(rs.getInt("OC_DEBET_QUANTITY"));
debet.setExtraInsurarAmount(rs.getDouble("OC_DEBET_EXTRAINSURARAMOUNT"));
debet.setExtraInsurarInvoiceUid(rs.getString("OC_DEBET_EXTRAINSURARINVOICEUID"));
debet.setExtraInsurarUid(rs.getString("OC_DEBET_EXTRAINSURARUID"));
debet.setServiceUid(rs.getString("OC_DEBET_SERVICEUID"));
debet.setPatientName(rs.getString("lastname") + ", " + rs.getString("firstname"));
// *********************
// add Encounter object
// *********************
Encounter encounter = new Encounter();
encounter.setPatientUID(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_PATIENTUID")));
encounter.setDestinationUID(
ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_DESTINATIONUID")));
encounter.setUid(
ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_SERVERID"))
+ "."
+ ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_OBJECTID")));
encounter.setCreateDateTime(rs.getTimestamp("OC_ENCOUNTER_CREATETIME"));
encounter.setUpdateDateTime(rs.getTimestamp("OC_ENCOUNTER_UPDATETIME"));
encounter.setUpdateUser(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_UPDATEUID")));
encounter.setVersion(rs.getInt("OC_ENCOUNTER_VERSION"));
encounter.setBegin(rs.getTimestamp("OC_ENCOUNTER_BEGINDATE"));
encounter.setEnd(rs.getTimestamp("OC_ENCOUNTER_ENDDATE"));
encounter.setType(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_TYPE")));
encounter.setOutcome(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_OUTCOME")));
encounter.setOrigin(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_ORIGIN")));
encounter.setSituation(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_SITUATION")));
encounter.setCategories(ScreenHelper.checkString(rs.getString("OC_ENCOUNTER_CATEGORIES")));
// Now find the most recent service for this encounter
Encounter.EncounterService encounterService = encounter.getLastEncounterService();
if (encounterService != null) {
encounter.setServiceUID(encounterService.serviceUID);
encounter.setManagerUID(encounterService.managerUID);
encounter.setBedUID(encounterService.bedUID);
}
debet.setEncounter(encounter);
// *********************
// add Prestation object
// *********************
Prestation prestation = new Prestation();
prestation.setUid(
rs.getString("OC_PRESTATION_SERVERID") + "." + rs.getString("OC_PRESTATION_OBJECTID"));
prestation.setCode(rs.getString("OC_PRESTATION_CODE"));
prestation.setDescription(rs.getString("OC_PRESTATION_DESCRIPTION"));
prestation.setPrice(rs.getDouble("OC_PRESTATION_PRICE"));
prestation.setCategories(rs.getString("OC_PRESTATION_CATEGORIES"));
ObjectReference or = new ObjectReference();
or.setObjectType(rs.getString("OC_PRESTATION_REFTYPE"));
or.setObjectUid(rs.getString("OC_PRESTATION_REFUID"));
prestation.setReferenceObject(or);
prestation.setCreateDateTime(rs.getTimestamp("OC_PRESTATION_CREATETIME"));
prestation.setUpdateDateTime(rs.getTimestamp("OC_PRESTATION_UPDATETIME"));
prestation.setUpdateUser(rs.getString("OC_PRESTATION_UPDATEUID"));
prestation.setVersion(rs.getInt("OC_PRESTATION_VERSION"));
prestation.setType(rs.getString("OC_PRESTATION_TYPE"));
debet.setPrestation(prestation);
sortedDebets.put(
new SimpleDateFormat("yyyyMMdd").format(debet.getDate())
+ "."
+ debet.getPatientName()
+ "."
+ debet.getPatientInvoiceUid()
+ "."
+ debet.getUid(),
debet);
}
} 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();
}
}
Iterator iDebets = sortedDebets.keySet().iterator();
while (iDebets.hasNext()) {
debets.add(sortedDebets.get(iDebets.next()));
}
return debets;
}
public boolean store() {
boolean bStored = true;
String ids[];
int iVersion = 1;
PreparedStatement ps = null;
ResultSet rs = null;
String sSelect = "";
Connection oc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
try {
if (this.getUid() != null && this.getUid().length() > 0) {
ids = this.getUid().split("\\.");
if (ids.length == 2) {
sSelect =
"SELECT OC_INSURARINVOICE_VERSION FROM OC_EXTRAINSURARINVOICES WHERE OC_INSURARINVOICE_SERVERID = ? AND OC_INSURARINVOICE_OBJECTID = ?";
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()) {
iVersion = rs.getInt("OC_INSURARINVOICE_VERSION") + 1;
}
rs.close();
ps.close();
sSelect =
"INSERT INTO OC_EXTRAINSURARINVOICES_HISTORY SELECT * FROM OC_EXTRAINSURARINVOICES WHERE OC_INSURARINVOICE_SERVERID = ? AND OC_INSURARINVOICE_OBJECTID = ?";
ps = oc_conn.prepareStatement(sSelect);
ps.setInt(1, Integer.parseInt(ids[0]));
ps.setInt(2, Integer.parseInt(ids[1]));
ps.executeUpdate();
ps.close();
sSelect =
" DELETE FROM OC_EXTRAINSURARINVOICES WHERE OC_INSURARINVOICE_SERVERID = ? AND OC_INSURARINVOICE_OBJECTID = ?";
ps = oc_conn.prepareStatement(sSelect);
ps.setInt(1, Integer.parseInt(ids[0]));
ps.setInt(2, Integer.parseInt(ids[1]));
ps.executeUpdate();
ps.close();
} else {
ids =
new String[] {
MedwanQuery.getInstance().getConfigString("serverId"),
MedwanQuery.getInstance().getOpenclinicCounter("OC_INVOICES") + ""
};
this.setUid(ids[0] + "." + ids[1]);
this.setInvoiceUid(ids[1]);
}
} else {
ids =
new String[] {
MedwanQuery.getInstance().getConfigString("serverId"),
MedwanQuery.getInstance().getOpenclinicCounter("OC_INVOICES") + ""
};
this.setUid(ids[0] + "." + ids[1]);
this.setInvoiceUid(ids[1]);
}
if (ids.length == 2) {
sSelect =
" INSERT INTO OC_EXTRAINSURARINVOICES ("
+ " OC_INSURARINVOICE_SERVERID,"
+ " OC_INSURARINVOICE_OBJECTID,"
+ " OC_INSURARINVOICE_DATE,"
+ " OC_INSURARINVOICE_ID,"
+ " OC_INSURARINVOICE_INSURARUID,"
+ " OC_INSURARINVOICE_CREATETIME,"
+ " OC_INSURARINVOICE_UPDATETIME,"
+ " OC_INSURARINVOICE_UPDATEUID,"
+ " OC_INSURARINVOICE_VERSION,"
+ " OC_INSURARINVOICE_BALANCE,"
+ " OC_INSURARINVOICE_STATUS"
+ ") "
+ " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
ps = oc_conn.prepareStatement(sSelect);
ps.setInt(1, Integer.parseInt(ids[0]));
ps.setInt(2, Integer.parseInt(ids[1]));
ps.setTimestamp(3, new Timestamp(this.getDate().getTime()));
ps.setInt(4, Integer.parseInt(this.getInvoiceUid()));
ps.setString(5, this.getInsurarUid());
if (this.getCreateDateTime() == null) {
this.setCreateDateTime(new Date());
}
ps.setTimestamp(6, new Timestamp(this.getCreateDateTime().getTime()));
ps.setTimestamp(7, new Timestamp(this.getUpdateDateTime().getTime()));
ps.setString(8, this.getUpdateUser());
ps.setInt(9, iVersion);
ps.setDouble(10, this.getBalance());
ps.setString(11, this.getStatus());
ps.executeUpdate();
ps.close();
sSelect =
"UPDATE OC_DEBETS SET OC_DEBET_EXTRAINSURARINVOICEUID = NULL WHERE OC_DEBET_EXTRAINSURARINVOICEUID = ? ";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, this.getUid());
ps.executeUpdate();
ps.close();
Connection loc_conn = MedwanQuery.getInstance().getOpenclinicConnection();
Statement st = loc_conn.createStatement();
boolean hasqueries = false;
if (this.debets != null) {
String sDebetUID;
String[] aDebetUID;
String UIDs = "";
int counter = 0;
for (int i = 0; i < this.debets.size(); i++) {
sDebetUID = ScreenHelper.checkString(((Debet) this.debets.elementAt(i)).getUid());
if (sDebetUID.length() > 0) {
aDebetUID = sDebetUID.split("\\.");
if (aDebetUID.length == 2) {
hasqueries = true;
if (UIDs.length() > 0) {
UIDs += ",";
}
UIDs += aDebetUID[1];
counter++;
}
}
if (counter >= 250) {
st.addBatch(
"UPDATE OC_DEBETS SET OC_DEBET_EXTRAINSURARINVOICEUID = '"
+ this.getUid()
+ "' WHERE OC_DEBET_OBJECTID in ("
+ UIDs
+ ")");
UIDs = "";
counter = 0;
}
}
if (counter > 0) {
st.addBatch(
"UPDATE OC_DEBETS SET OC_DEBET_EXTRAINSURARINVOICEUID = '"
+ this.getUid()
+ "' WHERE OC_DEBET_OBJECTID in ("
+ UIDs
+ ")");
}
}
if (hasqueries) {
st.executeBatch();
}
st.close();
loc_conn.close();
sSelect =
"UPDATE OC_INSURARCREDITS SET OC_INSURARCREDIT_INVOICEUID = NULL WHERE OC_INSURARCREDIT_INVOICEUID = ?";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, this.getUid());
ps.executeUpdate();
ps.close();
if (this.credits != null) {
String sCreditUID;
String[] aCreditUID;
for (int i = 0; i < this.credits.size(); i++) {
sCreditUID = ScreenHelper.checkString((String) this.credits.elementAt(i));
if (sCreditUID.length() > 0) {
aCreditUID = sCreditUID.split("\\.");
if (aCreditUID.length == 2) {
sSelect =
"UPDATE OC_INSURARCREDITS SET OC_INSURARCREDIT_INVOICEUID = ? WHERE OC_INSURARCREDIT_SERVERID = ? AND OC_INSURARCREDIT_OBJECTID = ? ";
ps = oc_conn.prepareStatement(sSelect);
ps.setString(1, this.getUid());
ps.setInt(2, Integer.parseInt(aCreditUID[0]));
ps.setInt(3, Integer.parseInt(aCreditUID[1]));
ps.executeUpdate();
ps.close();
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
bStored = false;
Debug.println(
"OpenClinic => InsurarInvoice.java => store => " + e.getMessage() + " = " + sSelect);
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
oc_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return bStored;
}