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;
  }