Example usage for org.hibernate ScrollableResults get

List of usage examples for org.hibernate ScrollableResults get

Introduction

In this page you can find the example usage for org.hibernate ScrollableResults get.

Prototype

Object get(int i);

Source Link

Document

Get the ith object in the current row of results, without initializing any other results in the row.

Usage

From source file:org.openbravo.event.ProductCharacteristicEventHandler.java

License:Open Source License

private void deleteProductCharacteristicValue(ProductCharacteristic productCharacteristic) {
    ScrollableResults scroll = null;
    try {//from w w  w. j  a  va2 s . co  m
        OBCriteria<ProductCharacteristicValue> criteria = OBDal.getInstance()
                .createCriteria(ProductCharacteristicValue.class);
        criteria.add(Restrictions.eq(ProductCharacteristicValue.PROPERTY_CHARACTERISTIC,
                productCharacteristic.getCharacteristic()));
        criteria.add(Restrictions.eq(ProductCharacteristicValue.PROPERTY_PRODUCT,
                productCharacteristic.getProduct()));
        scroll = criteria.scroll(ScrollMode.FORWARD_ONLY);
        int i = 0;
        while (scroll.next()) {
            ProductCharacteristicValue productCharacteristicValue = (ProductCharacteristicValue) scroll.get(0);
            OBDal.getInstance().remove(productCharacteristicValue);
            i++;
            if (i % 100 == 0) {
                OBDal.getInstance().flush();
                OBDal.getInstance().getSession().clear();
            }
        }
    } finally {
        scroll.close();
    }
}

From source file:org.openbravo.financial.paymentreport.erpCommon.ad_reports.PaymentReportDao.java

License:Open Source License

FieldProvider[] getPaymentReport(VariablesSecureApp vars, String strOrg, String strInclSubOrg,
        String strDueDateFrom, String strDueDateTo, String strAmountFrom, String strAmountTo,
        String strDocumentDateFrom, String strDocumentDateTo, String strcBPartnerIdIN, String strcBPGroupIdIN,
        String strcNoBusinessPartner, String strcProjectIdIN, String strfinPaymSt, String strPaymentMethodId,
        String strFinancialAccountId, String strcCurrency, String strConvertCurrency, String strConversionDate,
        String strPaymType, String strOverdue, String strGroupCrit, String strOrdCrit,
        String strInclPaymentUsingCredit, String strPaymentDateFrom, String strPaymentDateTo,
        String strExpectedDateFrom, String strExpectedDateTo, String strOutput) throws OBException {

    StringBuilder hsqlScript = new StringBuilder();
    final java.util.List<Object> parameters = new ArrayList<Object>();

    String dateFormatString = OBPropertiesProvider.getInstance().getOpenbravoProperties()
            .getProperty("dateFormat.java");
    SimpleDateFormat dateFormat = new SimpleDateFormat(dateFormatString);
    Currency transCurrency;/*  w  w  w . j ava  2s  . c om*/
    BigDecimal transAmount = null;
    ConversionRate convRate = null;
    ArrayList<FieldProvider> totalData = new ArrayList<FieldProvider>();
    int numberOfElements = 0;
    int lastElement = 0;
    boolean existsConvRate = false;
    ScrollableResults scroller = null;

    OBContext.setAdminMode(true);
    try {

        hsqlScript.append(" from FIN_Payment_ScheduleDetail as fpsd ");
        hsqlScript.append(" left outer join fpsd.paymentDetails.finPayment pay");
        hsqlScript.append(" left outer join pay.businessPartner paybp");
        hsqlScript.append(" left outer join paybp.businessPartnerCategory paybpc");
        hsqlScript.append(" left outer join fpsd.invoicePaymentSchedule invps");
        hsqlScript.append(" left outer join invps.invoice inv");
        hsqlScript.append(" left outer join inv.businessPartner invbp");
        hsqlScript.append(" left outer join invbp.businessPartnerCategory invbpc");
        hsqlScript.append(" left outer join fpsd.paymentDetails.finPayment.currency paycur");
        hsqlScript.append(" left outer join fpsd.invoicePaymentSchedule.invoice.currency invcur");
        hsqlScript.append(" left outer join pay.project paypro");
        hsqlScript.append(" left outer join inv.project invpro");
        hsqlScript.append(" where (fpsd.");
        hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_PAYMENTDETAILS);
        hsqlScript.append(" is not null or invps is not null ");
        hsqlScript.append(") ");

        hsqlScript.append(" and fpsd.");
        hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
        hsqlScript.append(".id in ");
        hsqlScript.append(concatOrganizations(OBContext.getOBContext().getReadableOrganizations()));

        // organization + include sub-organization
        if (!strOrg.isEmpty()) {
            if (!strInclSubOrg.equalsIgnoreCase("include")) {
                hsqlScript.append(" and fpsd.");
                hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
                hsqlScript.append(".id = '");
                hsqlScript.append(strOrg);
                hsqlScript.append("'");
            } else {
                hsqlScript.append(" and fpsd.");
                hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
                hsqlScript.append(".id in ('");
                Set<String> orgChildTree = OBContext.getOBContext().getOrganizationStructureProvider()
                        .getChildTree(strOrg, true);
                Iterator<String> orgChildTreeIter = orgChildTree.iterator();
                while (orgChildTreeIter.hasNext()) {
                    hsqlScript.append(orgChildTreeIter.next());
                    orgChildTreeIter.remove();
                    hsqlScript.append("'");
                    if (orgChildTreeIter.hasNext())
                        hsqlScript.append(", '");
                }
                hsqlScript.append(")");
            }
        }

        // Exclude payments that use credit payment
        if (!strInclPaymentUsingCredit.equalsIgnoreCase("Y")) {
            hsqlScript.append(" and (not (pay.amount = 0 ");
            hsqlScript.append(" and pay.usedCredit > pay.generatedCredit) or pay is null)");
        }

        // due date from - due date to
        if (!strDueDateFrom.isEmpty()) {
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
            hsqlScript.append(" >= ?");
            parameters.add(FIN_Utility.getDate(strDueDateFrom));
        }
        if (!strDueDateTo.isEmpty()) {
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
            hsqlScript.append(" <= ?");
            parameters.add(FIN_Utility.getDate(strDueDateTo));
        }

        // expected date from - expected date to
        if (!strExpectedDateFrom.isEmpty()) {
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
            hsqlScript.append(" >= ?");
            parameters.add(FIN_Utility.getDate(strExpectedDateFrom));
        }
        if (!strExpectedDateTo.isEmpty()) {
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
            hsqlScript.append(" <= ?");
            parameters.add(FIN_Utility.getDate(strExpectedDateTo));
        }

        // document date from - document date to
        if (!strDocumentDateFrom.isEmpty()) {
            hsqlScript.append(" and coalesce(inv.");
            hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
            hsqlScript.append(", pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
            hsqlScript.append(") >= ?");
            parameters.add(FIN_Utility.getDate(strDocumentDateFrom));
        }
        if (!strDocumentDateTo.isEmpty()) {
            hsqlScript.append(" and coalesce(inv.");
            hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
            hsqlScript.append(", pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
            hsqlScript.append(") <= ?");
            parameters.add(FIN_Utility.getDate(strDocumentDateTo));
        }

        // payment date from - payment date to
        if (!strPaymentDateFrom.isEmpty()) {
            hsqlScript.append(" and ((pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
            hsqlScript.append(" >= ?)  or (pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
            hsqlScript.append(" is null and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
            hsqlScript.append(" >= ?))");
            parameters.add(FIN_Utility.getDate(strPaymentDateFrom));
            parameters.add(FIN_Utility.getDate(strPaymentDateFrom));
        }
        if (!strPaymentDateTo.isEmpty()) {
            hsqlScript.append(" and coalesce(pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
            hsqlScript.append(", invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
            hsqlScript.append(") <= ?");
            parameters.add(FIN_Utility.getDate(strPaymentDateTo));
        }

        // Empty Business Partner included
        if (strcNoBusinessPartner.equals("include")) {

            // business partner
            if (!strcBPartnerIdIN.isEmpty()) {
                hsqlScript.append(" and ((coalesce(pay.");
                hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(", inv.");
                hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(") in ");
                hsqlScript.append(strcBPartnerIdIN);
                hsqlScript.append(") or (pay.");
                hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is null and inv.");
                hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is null))");
            }
            // business partner category
            if (!strcBPGroupIdIN.isEmpty()) {
                hsqlScript.append(" and (coalesce(paybpc, invbpc) = '");
                hsqlScript.append(strcBPGroupIdIN);
                hsqlScript.append("' or (pay.");
                hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is null and inv.");
                hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is null))");
            }

            // Empty Businesss Partner excluded
        } else if (strcNoBusinessPartner.equals("exclude")) {

            // business partner
            if (!strcBPartnerIdIN.isEmpty()) {
                hsqlScript.append(" and coalesce(pay.");
                hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(", inv.");
                hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(") in ");
                hsqlScript.append(strcBPartnerIdIN);
            }

            // business partner category
            if (!strcBPGroupIdIN.isEmpty()) {
                hsqlScript.append(" and coalesce(paybpc, invbpc) = '");
                hsqlScript.append(strcBPGroupIdIN);
                hsqlScript.append("'");
            }
            // exclude empty business partner
            if (strcBPartnerIdIN.isEmpty() && strcBPGroupIdIN.isEmpty()) {
                hsqlScript.append(" and (pay.");
                hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is not null or inv.");
                hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
                hsqlScript.append(" is not null) ");
            }

            // Only Empty Business Partner
        } else {// if ((strcNoBusinessPartner.equals("only")))
            hsqlScript.append(" and pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
            hsqlScript.append(" is null and inv.");
            hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
            hsqlScript.append(" is null ");
        }

        // project
        if (!strcProjectIdIN.isEmpty()) {
            hsqlScript.append(" and coalesce(pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PROJECT);
            hsqlScript.append(", inv.");
            hsqlScript.append(Invoice.PROPERTY_PROJECT);
            hsqlScript.append(") in ");
            hsqlScript.append(strcProjectIdIN);
        }

        // status
        if (!strfinPaymSt.isEmpty() && !strfinPaymSt.equalsIgnoreCase("('')")) {
            hsqlScript.append(" and (pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_STATUS);
            hsqlScript.append(" in ");
            hsqlScript.append(strfinPaymSt);
            if (strfinPaymSt.contains("RPAP")) {
                hsqlScript.append(" or fpsd.");
                hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_PAYMENTDETAILS);
                hsqlScript.append(" is null)");
            } else {
                hsqlScript.append(" )");
            }
        }

        // payment method
        if (!strPaymentMethodId.isEmpty()) {
            hsqlScript.append(" and coalesce(pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTMETHOD);
            hsqlScript.append(", invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_FINPAYMENTMETHOD);
            hsqlScript.append(") = '");
            hsqlScript.append(strPaymentMethodId);
            hsqlScript.append("'");
        }

        // financial account
        if (!strFinancialAccountId.isEmpty()) {
            hsqlScript.append(" and  (pay is not null and ");
            hsqlScript.append(
                    "(select case when trans is not null then trans.account.id else payment.account.id end from FIN_Finacc_Transaction trans right outer join trans.finPayment payment where payment=pay)");

            hsqlScript.append(" = '");
            hsqlScript.append(strFinancialAccountId);
            hsqlScript.append("' or ((pay is null and inv.");
            hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
            hsqlScript.append(" = 'Y'");
            hsqlScript.append(" and invbp.");
            hsqlScript.append(BusinessPartner.PROPERTY_ACCOUNT);
            hsqlScript.append(".id = '");
            hsqlScript.append(strFinancialAccountId);
            hsqlScript.append("')");
            hsqlScript.append(" or (pay is null and inv.");
            hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
            hsqlScript.append(" = 'N'");
            hsqlScript.append(" and invbp.");
            hsqlScript.append(BusinessPartner.PROPERTY_POFINANCIALACCOUNT);
            hsqlScript.append(".id = '");
            hsqlScript.append(strFinancialAccountId);
            hsqlScript.append("')))");
        }

        // currency
        if (!strcCurrency.isEmpty()) {
            hsqlScript.append(" and coalesce(pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_CURRENCY);
            hsqlScript.append(", inv.");
            hsqlScript.append(Invoice.PROPERTY_CURRENCY);
            hsqlScript.append(") = '");
            hsqlScript.append(strcCurrency);
            hsqlScript.append("'");
        }

        // payment type
        if (strPaymType.equalsIgnoreCase("FINPR_Receivables")) {
            hsqlScript.append(" and (pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_RECEIPT);
            hsqlScript.append(" = 'Y'");
            hsqlScript.append(" or inv.");
            hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
            hsqlScript.append(" = 'Y')");
        } else if (strPaymType.equalsIgnoreCase("FINPR_Payables")) {
            hsqlScript.append(" and (pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_RECEIPT);
            hsqlScript.append(" = 'N'");
            hsqlScript.append(" or inv.");
            hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
            hsqlScript.append(" = 'N')");
        }

        // overdue
        if (!strOverdue.isEmpty()) {
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_OUTSTANDINGAMOUNT);
            hsqlScript.append(" != '0'");
            hsqlScript.append(" and invps.");
            hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
            hsqlScript.append(" <  ?");
            parameters.add(DateUtils.truncate(new Date(), Calendar.DATE));
        }

        if ("HTML".equals(strOutput)) {
            int maxRecords = 1000;
            final Session sessionCount = OBDal.getInstance().getSession();
            final Query queryCount = sessionCount.createQuery("select count(*)" + hsqlScript.toString());
            int px = 0;
            for (final Object param : parameters) {
                if (param instanceof BaseOBObject) {
                    queryCount.setEntity(px++, param);
                } else {
                    queryCount.setParameter(px++, param);
                }
            }
            final Long hqlRecordsCount = (Long) queryCount.list().get(0);
            if ((int) (long) hqlRecordsCount > maxRecords) {
                String message = "FINPR_TooManyRecords";
                throw new OBException(message);
            }
        }

        final StringBuilder firstLineQuery = new StringBuilder();
        firstLineQuery.append(
                "select fpsd, (select a.sequenceNumber from ADList a where a.reference.id = '575BCB88A4694C27BC013DE9C73E6FE7' and a.searchKey = coalesce(pay.status, 'RPAP')) as a");
        hsqlScript = firstLineQuery.append(hsqlScript);

        hsqlScript.append(" order by ");

        if (strGroupCrit.equalsIgnoreCase("APRM_FATS_BPARTNER")) {
            hsqlScript.append(" coalesce(paybp.");
            hsqlScript.append(BusinessPartner.PROPERTY_NAME);
            hsqlScript.append(", invbp.");
            hsqlScript.append(BusinessPartner.PROPERTY_NAME);
            hsqlScript.append("), ");
        } else if (strGroupCrit.equalsIgnoreCase("Project")) {
            hsqlScript.append("  coalesce(paypro.");
            hsqlScript.append(Project.PROPERTY_NAME);
            hsqlScript.append(", invpro.");
            hsqlScript.append(Project.PROPERTY_NAME);
            hsqlScript.append("), ");
        } else if (strGroupCrit.equalsIgnoreCase("FINPR_BPartner_Category")) {
            hsqlScript.append("  coalesce(paybpc.");
            hsqlScript.append(Category.PROPERTY_NAME);
            hsqlScript.append(", invbpc.");
            hsqlScript.append(Category.PROPERTY_NAME);
            hsqlScript.append("), ");
        } else if (strGroupCrit.equalsIgnoreCase("INS_CURRENCY")) {
            hsqlScript.append("  coalesce(paycur.");
            hsqlScript.append(Currency.PROPERTY_ISOCODE);
            hsqlScript.append(", invcur.");
            hsqlScript.append(Currency.PROPERTY_ISOCODE);
            hsqlScript.append("), ");
        } else if (strGroupCrit.equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
            hsqlScript.append(" coalesce(");
            hsqlScript.append(
                    " (select trans.account.name from FIN_Finacc_Transaction trans left outer join trans.finPayment payment where payment.id=pay.id),");
            hsqlScript.append(" pay.");
            hsqlScript.append(FIN_Payment.PROPERTY_ACCOUNT);
            hsqlScript.append(".name, 'Awaiting Payment'");
            hsqlScript.append("), ");
        }

        hsqlScript.append(" a, coalesce(pay.");
        hsqlScript.append(FIN_Payment.PROPERTY_STATUS);
        hsqlScript.append(", 'RPAP')");

        if (!strOrdCrit.isEmpty()) {
            String[] strOrdCritList = strOrdCrit.substring(2, strOrdCrit.length() - 2).split("', '");

            for (int i = 0; i < strOrdCritList.length; i++) {
                if (strOrdCritList[i].equalsIgnoreCase("Date")) {
                    hsqlScript.append(", inv.");
                    hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
                }
                if (strOrdCritList[i].contains("Project")) {
                    hsqlScript.append(",  coalesce(paypro.");
                    hsqlScript.append(Project.PROPERTY_NAME);
                    hsqlScript.append(", invpro.");
                    hsqlScript.append(Project.PROPERTY_NAME);
                    hsqlScript.append(")");
                }
                if (strOrdCritList[i].contains("FINPR_BPartner_Category")) {
                    hsqlScript.append(",  coalesce(paybpc.");
                    hsqlScript.append(Category.PROPERTY_NAME);
                    hsqlScript.append(", invbpc.");
                    hsqlScript.append(Category.PROPERTY_NAME);
                    hsqlScript.append(")");
                }
                if (strOrdCritList[i].contains("APRM_FATS_BPARTNER")) {
                    hsqlScript.append(",  coalesce(paybp.");
                    hsqlScript.append(BusinessPartner.PROPERTY_NAME);
                    hsqlScript.append(", invbp.");
                    hsqlScript.append(BusinessPartner.PROPERTY_NAME);
                    hsqlScript.append(")");
                }
                if (strOrdCritList[i].contains("INS_CURRENCY")) {
                    hsqlScript.append(",  coalesce(paycur.");
                    hsqlScript.append(Currency.PROPERTY_ISOCODE);
                    hsqlScript.append(", invcur.");
                    hsqlScript.append(Currency.PROPERTY_ISOCODE);
                    hsqlScript.append(")");
                }
                if (strOrdCritList[i].equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
                    hsqlScript.append(", coalesce(");
                    hsqlScript.append(
                            " (select trans.account.name from FIN_Finacc_Transaction trans left outer join trans.finPayment payment where payment.id=pay.id),");
                    hsqlScript.append(" pay.");
                    hsqlScript.append(FIN_Payment.PROPERTY_ACCOUNT);
                    hsqlScript.append(".name)");
                }
                if (strOrdCritList[i].equalsIgnoreCase("DueDate")) {
                    hsqlScript.append(", invps.");
                    hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
                }
            }
        }

        hsqlScript.append(", fpsd.");
        hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_INVOICEPAYMENTSCHEDULE);
        hsqlScript.append(".");
        hsqlScript.append(FIN_PaymentSchedule.PROPERTY_ID);
        final Session session = OBDal.getInstance().getSession();
        final Query query = session.createQuery(hsqlScript.toString());

        int pos = 0;
        for (final Object param : parameters) {
            if (param instanceof BaseOBObject) {
                query.setEntity(pos++, param);
            } else {
                query.setParameter(pos++, param);
            }
        }

        scroller = query.scroll(ScrollMode.FORWARD_ONLY);

        FIN_PaymentDetail finPaymDetail;
        Boolean mustGroup;
        String previousFPSDInvoiceId = null;
        String previousPaymentId = null;
        BigDecimal amountSum = BigDecimal.ZERO;
        BigDecimal balanceSum = BigDecimal.ZERO;
        FieldProvider previousRow = null;
        FieldProvider lastGroupedDatarow = null;
        ConversionRate previousConvRate = null;
        boolean isReceipt = false;
        boolean isAmtInLimit = false;

        // Before processing the data the Transactions without a Payment associated are recovered
        java.util.List<FIN_FinaccTransaction> transactionsList = getTransactionsList(strInclSubOrg, strOrg,
                strcBPartnerIdIN, strFinancialAccountId, strDocumentDateFrom, strDocumentDateTo,
                strPaymentDateFrom, strPaymentDateTo, strAmountFrom, strAmountTo, strcBPGroupIdIN,
                strcProjectIdIN, strfinPaymSt, strcCurrency, strPaymType, strGroupCrit, strOrdCrit,
                strcNoBusinessPartner, strDueDateFrom, strDueDateTo, strExpectedDateFrom, strExpectedDateTo);

        // There are three variables involved in this loop. The first one is data, wich is the
        // the one the loop processes. Then grouped data is used to group similar data lines into
        // one. Finally total data adds the remaining information that is not in data.
        int i = 0;
        while (scroller.next()) {
            i++;
            // get 1st column (idx=0)
            Object value = scroller.get(0);

            // TODO: rename variable to not have same name as class
            FIN_PaymentScheduleDetail FIN_PaymentScheduleDetail = (FIN_PaymentScheduleDetail) value;

            // make a empty FieldProvider instead of saving link to DAL-object
            FieldProvider data = FieldProviderFactory.getFieldProvider(null);

            if (i % 100 == 0) {
                OBDal.getInstance().getSession().clear();
            }
            OBDal.getInstance().getSession().buildLockRequest(LockOptions.NONE)
                    .lock(FIN_PaymentScheduleDetail.ENTITY_NAME, FIN_PaymentScheduleDetail);

            // search for fin_finacc_transaction for this payment
            FIN_FinaccTransaction trx = null;
            FIN_PaymentDetail detail = FIN_PaymentScheduleDetail.getPaymentDetails();
            if (detail != null) {
                OBCriteria<FIN_FinaccTransaction> trxQuery = OBDal.getInstance()
                        .createCriteria(FIN_FinaccTransaction.class);
                trxQuery.add(
                        Restrictions.eq(FIN_FinaccTransaction.PROPERTY_FINPAYMENT, detail.getFinPayment()));
                // uniqueness guaranteed via unique constraint in db
                trx = (FIN_FinaccTransaction) trxQuery.uniqueResult();
            }
            // If the payment schedule detail has a payment detail, then, the information is taken from
            // the payment. If not, the information is taken from the invoice (the else).
            if (FIN_PaymentScheduleDetail.getPaymentDetails() != null) {
                BusinessPartner bp = getDocumentBusinessPartner(FIN_PaymentScheduleDetail);
                if (bp == null) {
                    FieldProviderFactory.setField(data, "BP_GROUP", "");
                    FieldProviderFactory.setField(data, "BPARTNER", "");
                } else {
                    // bp_group -- bp_category
                    FieldProviderFactory.setField(data, "BP_GROUP", bp.getBusinessPartnerCategory().getName());
                    // bpartner
                    FieldProviderFactory.setField(data, "BPARTNER", bp.getName());
                }

                // transCurrency
                transCurrency = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getCurrency();
                FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());
                // paymentMethod
                FieldProviderFactory.setField(data, "PAYMENT_METHOD", FIN_PaymentScheduleDetail
                        .getPaymentDetails().getFinPayment().getPaymentMethod().getIdentifier());

                // payment
                FieldProviderFactory.setField(data, "PAYMENT",
                        ((FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
                                .getPaymentDate() != null)
                                        ? dateFormat.format(FIN_PaymentScheduleDetail.getPaymentDetails()
                                                .getFinPayment().getPaymentDate())
                                        : "Null")
                                + " - " + FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
                                        .getDocumentNo());
                // payment description
                FieldProviderFactory.setField(data, "PAYMENT_DESC",
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getDescription());
                // payment_id
                FieldProviderFactory.setField(data, "PAYMENT_ID",
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getId().toString());
                // payment_date
                FieldProviderFactory.setField(data, "PAYMENT_DATE",
                        (FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getPaymentDate() != null)
                                ? dateFormat.format(FIN_PaymentScheduleDetail
                                        .getPaymentDetails().getFinPayment().getPaymentDate())
                                : "Null");
                // payment_docNo
                FieldProviderFactory.setField(data, "PAYMENT_DOCNO",
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getDocumentNo());
                // payment yes / no
                FieldProviderFactory.setField(data, "PAYMENT_Y_N", "");
                // financialAccount
                FieldProviderFactory.setField(data, "FINANCIAL_ACCOUNT",
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
                                .getFINFinaccTransactionList().size() != 0
                                        ? FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
                                                .getFINFinaccTransactionList().get(0).getAccount().getName()
                                        : FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
                                                .getAccount().getName());
                // status
                FieldProviderFactory.setField(data, "STATUS", translateRefList(
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus()));
                FieldProviderFactory.setField(data, "STATUS_CODE",
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus());
                // is receipt
                if (FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().isReceipt()) {
                    FieldProviderFactory.setField(data, "ISRECEIPT", "Y");
                    isReceipt = true;
                } else {
                    FieldProviderFactory.setField(data, "ISRECEIPT", "N");
                    isReceipt = false;
                }
                // deposit/withdraw date
                if (trx != null) {
                    FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE",
                            dateFormat.format(trx.getTransactionDate()));
                } else {
                    FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE", "");
                }
            } else {

                // bp_group -- bp_category
                FieldProviderFactory.setField(data, "BP_GROUP",
                        FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice().getBusinessPartner()
                                .getBusinessPartnerCategory().getName());
                // bpartner
                FieldProviderFactory.setField(data, "BPARTNER", FIN_PaymentScheduleDetail
                        .getInvoicePaymentSchedule().getInvoice().getBusinessPartner().getName());
                // transCurrency
                transCurrency = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice()
                        .getCurrency();
                FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());
                // paymentMethod
                FieldProviderFactory.setField(data, "PAYMENT_METHOD", FIN_PaymentScheduleDetail
                        .getInvoicePaymentSchedule().getFinPaymentmethod().getIdentifier());
                // payment
                FieldProviderFactory.setField(data, "PAYMENT", "");
                // payment_id
                FieldProviderFactory.setField(data, "PAYMENT_ID", "");
                // payment_date
                FieldProviderFactory.setField(data, "PAYMENT_DATE", "");
                // payment_docNo
                FieldProviderFactory.setField(data, "PAYMENT_DOCNO", "");
                // payment yes / no
                FieldProviderFactory.setField(data, "PAYMENT_Y_N", "Display:None");
                // financialAccount
                FieldProviderFactory.setField(data, "FINANCIAL_ACCOUNT", "");
                // status
                FieldProviderFactory.setField(data, "STATUS", translateRefList("RPAP"));
                FieldProviderFactory.setField(data, "STATUS_CODE", "RPAP");
                // is receipt
                if (FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice().isSalesTransaction()) {
                    FieldProviderFactory.setField(data, "ISRECEIPT", "Y");
                    isReceipt = true;
                } else {
                    FieldProviderFactory.setField(data, "ISRECEIPT", "N");
                    isReceipt = false;
                }
                // deposit/withdraw date
                FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE", "");
            }

            /*
             * - If the payment schedule detail has an invoice, the line is filled normally.
             * 
             * - If it has a payment it does not have an invoice or it should have entered the first if,
             * thus, it is a credit payment. If it is a credit payment, it is checked whether it pays
             * one or multiple invoices. If it is one, the information of that invoice is provided. If
             * not, it is filled with '**'.
             * 
             * - Otherwise, it is filled empty.
             */
            if (FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
                fillLine(dateFormat, data, FIN_PaymentScheduleDetail,
                        FIN_PaymentScheduleDetail.getInvoicePaymentSchedule(), false);
            } else if (FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment() != null) {
                java.util.List<Invoice> invoices = getInvoicesUsingCredit(
                        FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment());
                if (invoices.size() == 1) {
                    java.util.List<FIN_PaymentSchedule> ps = getInvoicePaymentSchedules(
                            FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment());
                    fillLine(dateFormat, data, FIN_PaymentScheduleDetail, ps.get(0), true);
                } else {
                    // project
                    FieldProviderFactory.setField(data, "PROJECT", "");
                    // salesPerson
                    FieldProviderFactory.setField(data, "SALES_PERSON", "");
                    // invoiceNumber.
                    FieldProviderFactory.setField(data, "INVOICE_NUMBER",
                            invoices.size() > 1 ? "**" + getInvoicesDocNos(invoices) : "");
                    // payment plan id
                    FieldProviderFactory.setField(data, "PAYMENT_PLAN_ID", "");
                    // payment plan yes / no
                    FieldProviderFactory.setField(data, "PAYMENT_PLAN_Y_N",
                            invoices.size() != 1 ? "Display:none" : "");
                    // payment plan yes / no
                    FieldProviderFactory.setField(data, "NOT_PAYMENT_PLAN_Y_N",
                            invoices.size() > 1 ? "" : "Display:none");
                    // invoiceDate
                    FieldProviderFactory.setField(data, "INVOICE_DATE", "");
                    // dueDate.
                    FieldProviderFactory.setField(data, "DUE_DATE", "");
                    // expectedDate.
                    FieldProviderFactory.setField(data, "EXPECTED_DATE", "");
                    // plannedDSO
                    FieldProviderFactory.setField(data, "PLANNED_DSO", "0");
                    // currentDSO
                    FieldProviderFactory.setField(data, "CURRENT_DSO", "0");
                    // daysOverdue
                    FieldProviderFactory.setField(data, "OVERDUE", "0");
                }
            } else {
                // project
                FieldProviderFactory.setField(data, "PROJECT", "");
                // salesPerson
                FieldProviderFactory.setField(data, "SALES_PERSON", "");
                // invoiceNumber.
                FieldProviderFactory.setField(data, "INVOICE_NUMBER", "");
                // payment plan id
                FieldProviderFactory.setField(data, "PAYMENT_PLAN_ID", "");
                // payment plan yes / no
                FieldProviderFactory.setField(data, "PAYMENT_PLAN_Y_N", "Display:none");
                // payment plan yes / no
                FieldProviderFactory.setField(data, "NOT_PAYMENT_PLAN_Y_N", "Display:none");
                // invoiceDate
                FieldProviderFactory.setField(data, "INVOICE_DATE", "");
                // dueDate.
                FieldProviderFactory.setField(data, "DUE_DATE", "");
                // expectedDate.
                FieldProviderFactory.setField(data, "EXPECTED_DATE", "");
                // plannedDSO
                FieldProviderFactory.setField(data, "PLANNED_DSO", "0");
                // currentDSO
                FieldProviderFactory.setField(data, "CURRENT_DSO", "0");
                // daysOverdue
                FieldProviderFactory.setField(data, "OVERDUE", "0");

            }

            // transactional and base amounts
            transAmount = FIN_PaymentScheduleDetail.getAmount();

            Currency baseCurrency = OBDal.getInstance().get(Currency.class, strConvertCurrency);

            boolean sameCurrency = baseCurrency.getISOCode().equalsIgnoreCase(transCurrency.getISOCode());

            if (!sameCurrency) {
                convRate = this.getConversionRate(transCurrency, baseCurrency, strConversionDate);

                if (convRate != null) {
                    final int stdPrecission = convRate.getToCurrency().getStandardPrecision().intValue();
                    if (isReceipt) {
                        FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.toString());
                        FieldProviderFactory.setField(data, "BASE_AMOUNT",
                                transAmount.multiply(convRate.getMultipleRateBy())
                                        .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
                    } else {
                        FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.negate().toString());
                        FieldProviderFactory.setField(data, "BASE_AMOUNT",
                                transAmount.multiply(convRate.getMultipleRateBy())
                                        .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate().toString());
                    }
                } else {
                    String message = transCurrency.getISOCode() + " -> " + baseCurrency.getISOCode() + " "
                            + strConversionDate;
                    throw new OBException(message);
                }
            } else {
                convRate = null;
                if (isReceipt) {
                    FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.toString());
                    FieldProviderFactory.setField(data, "BASE_AMOUNT", transAmount.toString());
                } else {
                    FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.negate().toString());
                    FieldProviderFactory.setField(data, "BASE_AMOUNT", transAmount.negate().toString());
                }
            }

            // currency
            FieldProviderFactory.setField(data, "BASE_CURRENCY", baseCurrency.getISOCode());
            // baseCurrency
            FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());

            // Balance
            String status = "RPAE";
            try {
                status = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus();
            } catch (NullPointerException e) {
            }
            final boolean isCreditPayment = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() == null
                    && FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment() != null;

            BigDecimal balance = BigDecimal.ZERO;
            if (isCreditPayment && status != null && "PWNC RPR RPPC PPM RDNC".indexOf(status) >= 0) {
                balance = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getGeneratedCredit()
                        .subtract(
                                FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getUsedCredit());
                if (isReceipt) {
                    balance = balance.negate();
                }
            } else if (!isCreditPayment && status != null
                    && "PWNC RPR RPPC PPM RDNC RPVOID".indexOf(status) == -1) {
                balance = isReceipt ? transAmount : transAmount.negate();
            }
            if (convRate != null) {
                final int stdPrecission = convRate.getToCurrency().getStandardPrecision().intValue();
                balance = balance.multiply(convRate.getMultipleRateBy()).setScale(stdPrecission,
                        BigDecimal.ROUND_HALF_UP);
            }
            FieldProviderFactory.setField(data, "BALANCE", balance.toString());

            finPaymDetail = FIN_PaymentScheduleDetail.getPaymentDetails();

            // Payment Schedule Detail grouping criteria
            if (finPaymDetail != null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
                mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId)
                        && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId()
                                .equalsIgnoreCase(previousFPSDInvoiceId);
                previousFPSDInvoiceId = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId();
                previousPaymentId = finPaymDetail.getFinPayment().getId();
            } else if (finPaymDetail != null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() == null) {
                mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId)
                        && previousFPSDInvoiceId == null;
                previousPaymentId = finPaymDetail.getFinPayment().getId();
                previousFPSDInvoiceId = null;
            } else if (finPaymDetail == null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
                mustGroup = previousPaymentId == null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule()
                        .getId().equalsIgnoreCase(previousFPSDInvoiceId);
                previousPaymentId = null;
                previousFPSDInvoiceId = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId();
            } else {
                mustGroup = false;
            }

            if (mustGroup) {
                amountSum = amountSum.add(transAmount);
                balanceSum = balanceSum.add(balance);
            } else {
                if (previousRow != null) {
                    // The current row has nothing to do with the previous one. Because of that, the
                    // previous row has to be added to grouped data.
                    if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                        FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString());
                    else
                        FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT",
                                amountSum.negate().toString());
                    FieldProviderFactory.setField(previousRow, "BALANCE", balanceSum.toString());
                    if (previousConvRate == null) {
                        if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                            FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString());
                        else
                            FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
                                    amountSum.negate().toString());
                    } else {
                        final int stdPrecission = previousConvRate.getToCurrency().getStandardPrecision()
                                .intValue();
                        if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                            FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
                                    amountSum.multiply(previousConvRate.getMultipleRateBy())
                                            .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
                        else
                            FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
                                    amountSum.multiply(previousConvRate.getMultipleRateBy())
                                            .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate()
                                            .toString());
                    }

                    if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
                        isAmtInLimit = true;
                    } else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
                        isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
                                .parseDouble(strAmountFrom);
                    } else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) {
                        isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
                                .parseDouble(strAmountTo);
                    } else {
                        isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
                                .parseDouble(strAmountFrom)
                                && Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
                                        .parseDouble(strAmountTo);
                    }
                    if (isAmtInLimit) {
                        lastGroupedDatarow = previousRow;
                        isAmtInLimit = false;
                        numberOfElements++;
                    }
                }
                previousRow = data;
                previousConvRate = convRate;
                amountSum = transAmount;
                balanceSum = balance;
            }

            // group_crit_id this is the column that has the ids of the grouping criteria selected
            if (strGroupCrit.equalsIgnoreCase("APRM_FATS_BPARTNER")) {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("BPARTNER"));
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner");
            } else if (strGroupCrit.equalsIgnoreCase("Project")) {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("PROJECT"));
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Project");
            } else if (strGroupCrit.equalsIgnoreCase("FINPR_BPartner_Category")) {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("BP_GROUP"));
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner Category");
            } else if (strGroupCrit.equalsIgnoreCase("INS_CURRENCY")) {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID",
                        previousRow.getField("TRANS_CURRENCY"));
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Currency");
            } else if (strGroupCrit.equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID",
                        previousRow.getField("FINANCIAL_ACCOUNT"));
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Financial Account");
            } else {
                FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", "");
            }

            // Insert the transactions without payment if necessary
            if (lastElement != numberOfElements) {
                if (transactionsList.size() > 0) {
                    try {
                        existsConvRate = insertIntoTotal(lastGroupedDatarow, transactionsList, totalData,
                                strGroupCrit, strOrdCrit, strConvertCurrency, strConversionDate);
                    } catch (OBException e) {
                        // If there is no conversion rate
                        throw e;
                    }
                }
                totalData.add(lastGroupedDatarow);
                lastElement++;
            }

        }
        if (previousRow != null) {
            // The current row has nothing to do with the previous one. Because of that, the
            // previous row has to be added to grouped data.
            if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString());
            else
                FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.negate().toString());
            FieldProviderFactory.setField(previousRow, "BALANCE", balanceSum.toString());
            if (previousConvRate == null) {
                if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                    FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString());
                else
                    FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.negate().toString());
            } else {
                final int stdPrecission = previousConvRate.getToCurrency().getStandardPrecision().intValue();
                if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
                    FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
                            amountSum.multiply(previousConvRate.getMultipleRateBy())
                                    .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
                else
                    FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
                            amountSum.multiply(previousConvRate.getMultipleRateBy())
                                    .setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate().toString());
            }

            if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
                isAmtInLimit = true;
            } else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
                isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
                        .parseDouble(strAmountFrom);
            } else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) {
                isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
                        .parseDouble(strAmountTo);
            } else {
                isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
                        .parseDouble(strAmountFrom)
                        && Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
                                .parseDouble(strAmountTo);
            }
            if (isAmtInLimit) {
                lastGroupedDatarow = previousRow;
                isAmtInLimit = false;
                numberOfElements++;
            }
        }

        // Insert the transactions without payment if necessary
        if (lastElement != numberOfElements) {
            if (transactionsList.size() > 0) {
                try {
                    existsConvRate = insertIntoTotal(lastGroupedDatarow, transactionsList, totalData,
                            strGroupCrit, strOrdCrit, strConvertCurrency, strConversionDate);
                } catch (OBException e) {
                    // If there is no conversion rate
                    throw e;
                }
            }
            totalData.add(lastGroupedDatarow);
            lastElement++;
        }

        // Insert the remaining transactions wihtout payment if necessary
        while (transactionsList.size() > 0) {
            // throws OBException if there is no conversion rate
            FieldProvider transactionData = createFieldProviderForTransaction(transactionsList.get(0),
                    strGroupCrit, strConvertCurrency, strConversionDate);
            totalData.add(transactionData);
            transactionsList.remove(0);
        }
    } finally {
        if (scroller != null) {
            scroller.close();
        }
        OBContext.restorePreviousMode();
    }
    return totalData.toArray(new FieldProvider[totalData.size()]);
}

From source file:org.openbravo.materialmgmt.VariantChDescUpdateProcess.java

License:Open Source License

/**
 * Method to update the Characteristics Description.
 * // w  w w .ja  v  a  2 s . c  o m
 * @param strProductId
 *          Optional parameter, when given updates only the description of this product.
 * @param strChValueId
 *          Optional parameter, when given updates only products with this characteristic value
 *          assigned.
 */
public void update(String strProductId, String strChValueId) {
    OBContext.setAdminMode(true);
    try {
        if (StringUtils.isNotBlank(strProductId)) {
            Product product = OBDal.getInstance().get(Product.class, strProductId);
            // In some cases product might have been deleted.
            if (product != null) {
                updateProduct(product);
            }
            return;
        }
        StringBuffer where = new StringBuffer();
        where.append(" as p");
        where.append(" where p." + Product.PROPERTY_PRODUCTCHARACTERISTICLIST + " is not empty");
        if (StringUtils.isNotBlank(strChValueId)) {
            where.append(" and exists (select 1 from p." + Product.PROPERTY_PRODUCTCHARACTERISTICVALUELIST
                    + " as chv");
            where.append("    where chv." + ProductCharacteristicValue.PROPERTY_CHARACTERISTICVALUE
                    + ".id = :chvid)");
        }
        OBQuery<Product> productQuery = OBDal.getInstance().createQuery(Product.class, where.toString());
        if (StringUtils.isNotBlank(strChValueId)) {
            productQuery.setNamedParameter("chvid", strChValueId);
        }
        productQuery.setFilterOnReadableOrganization(false);
        productQuery.setFilterOnActive(false);

        ScrollableResults products = productQuery.scroll(ScrollMode.FORWARD_ONLY);
        int i = 0;
        try {
            while (products.next()) {
                Product product = (Product) products.get(0);
                updateProduct(product);

                if ((i % 100) == 0) {
                    OBDal.getInstance().flush();
                    OBDal.getInstance().getSession().clear();
                }
                i++;
            }
        } finally {
            products.close();
        }

    } finally {
        OBContext.restorePreviousMode();
    }
}

From source file:org.openmrs.api.db.hibernate.HibernateContextDAO.java

License:Mozilla Public License

@Override
public void updateSearchIndexForType(Class<?> type) {
    //From http://docs.jboss.org/hibernate/search/3.3/reference/en-US/html/manual-index-changes.html#search-batchindex-flushtoindexes
    FullTextSession session = Search.getFullTextSession(sessionFactory.getCurrentSession());
    session.purgeAll(type);//  w ww .j  a v a 2 s.c o  m

    //Prepare session for batch work
    session.flush();
    session.clear();

    FlushMode flushMode = session.getFlushMode();
    CacheMode cacheMode = session.getCacheMode();
    try {
        session.setFlushMode(FlushMode.MANUAL);
        session.setCacheMode(CacheMode.IGNORE);

        //Scrollable results will avoid loading too many objects in memory
        ScrollableResults results = session.createCriteria(type).setFetchSize(1000)
                .scroll(ScrollMode.FORWARD_ONLY);
        int index = 0;
        while (results.next()) {
            index++;
            session.index(results.get(0)); //index each element
            if (index % 1000 == 0) {
                session.flushToIndexes(); //apply changes to indexes
                session.clear(); //free memory since the queue is processed
            }
        }
        session.flushToIndexes();
        session.clear();
    } finally {
        session.setFlushMode(flushMode);
        session.setCacheMode(cacheMode);
    }
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

/**
 * post-process a list of observations from a criteria; duplicate data FTL
 *
 * @param criteria            the object with data in it
 * @param mohFetchRestriction information for limiting fetch, specifically the size
 * @return a list of processed (cleaned) observations
 *//* w ww .jav  a  2s  .co  m*/
private List<Obs> processObs(Criteria criteria, MohFetchRestriction mohFetchRestriction) {
    List<Obs> observations = new ArrayList<Obs>();

    // TODO: further optimization would be adding start date and end date parameter in the obs restrictions
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);

    Integer size = mohFetchRestriction.getSize();

    // scroll the results
    Obs processedObs = null;
    while (scrollableResults.next() && OpenmrsUtil.compareWithNullAsGreatest(observations.size(), size) == -1) {
        Obs obs = (Obs) scrollableResults.get(0);
        // TODO: thanks to Ampath for the duplicate data, we need to sanitize the query results here
        if (processedObs != null && !obs.isObsGrouping()) {
            if (DateUtils.isSameDay(processedObs.getObsDatetime(), obs.getObsDatetime())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getConcept(), obs.getConcept())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getValueCoded(), obs.getValueCoded())
                    && (OpenmrsUtil.nullSafeEquals(processedObs.getValueNumeric(), obs.getValueNumeric()))) {
                continue;
            }
        }
        processedObs = obs;
        observations.add(obs);
    }
    scrollableResults.close();
    return observations;
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

/**
 * @see MohCoreDAO#getPatientEncounters(Integer, java.util.Map, org.openmrs.module.amrsreports.util.MohFetchRestriction, java.util.Date)
 *///from  www  . j  a v  a2 s.c o m
@Override
public List<Encounter> getPatientEncounters(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions,
        final MohFetchRestriction mohFetchRestriction, final Date evaluationDate) throws DAOException {
    // create a hibernate criteria on the encounter object
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Encounter.class);
    // restrict the encounter that will be returned to specific patient only
    criteria.add(Restrictions.eq("patientId", patientId));

    // default ordering for the returned encounter is descending on encounter datetime
    Order order = Order.desc("encounterDatetime");
    // change the default ordering if the user pass the ascending ordering in the parameters
    if (OpenmrsUtil.nullSafeEquals(MohFetchOrdering.ORDER_ASCENDING, mohFetchRestriction.getFetchOrdering())) {
        order = Order.asc("encounterDatetime");
    }
    // add the ordering object to the criteria
    criteria.addOrder(order);

    // make sure we don't go past the evaluationDate
    criteria.add(Restrictions.le("encounterDatetime", evaluationDate));

    // always get from the first result
    criteria.setFirstResult(0);
    // set the first result to a number if the user pass any value on this parameter (currently not being used)
    if (mohFetchRestriction.getStart() != null) {
        criteria.setFirstResult(mohFetchRestriction.getStart());
    }

    // specify how many records should be returned
    if (mohFetchRestriction.getSize() != null) {
        criteria.setMaxResults(mohFetchRestriction.getSize());
    }

    // create a dummy encounter object
    Encounter encounter = new Encounter();
    // iterate over each property in the restriction map
    for (String property : restrictions.keySet()) {
        // get the actual object that will restrict the encounter. this will contains the list of encounter type or list of location
        // or list of provider (currently not being used) passed from caller
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        // check to make sure the list is not empty and the property is readable. example of the property for encounter are
        // encounterType or location of the encounter
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(encounter, property)) {
            // create a restriction on the property with the above list as the value
            criteria.add(Restrictions.in(property, propertyValues));
            // add ordering on that property to prevent slowness when ordering only on encounter datetime (1.6.x only)
            criteria.addOrder(Order.asc(property));
        }
    }

    // exclude all voided encounters
    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Encounter> encounters = new ArrayList<Encounter>();

    // scroll the results and add them to the above list of encounter
    Integer counter = 0;
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);
    while (scrollableResults.next()) {
        encounters.add((Encounter) scrollableResults.get(0));
    }
    scrollableResults.close();
    return encounters;
}

From source file:org.openmrs.module.clinicalsummary.db.hibernate.HibernateCoreDAO.java

License:Open Source License

/**
 * @see CoreDAO#getPatientObservations(Integer, java.util.Map, org.openmrs.module.clinicalsummary.util.FetchRestriction)
 *///from w w  w.  j ava 2 s  . c o  m
@Override
public List<Obs> getPatientObservations(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions, final FetchRestriction fetchRestriction)
        throws DAOException {
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Obs.class);
    criteria.createAlias("person", "person");
    criteria.add(Restrictions.eq("person.personId", patientId));

    criteria.setFirstResult(0);
    if (fetchRestriction.getStart() != null)
        criteria.setFirstResult(fetchRestriction.getStart());

    if (fetchRestriction.getSize() != null && fetchRestriction.getSize() == 1)
        criteria.setMaxResults(fetchRestriction.getSize());

    Order order = Order.desc("obsDatetime");
    if (OpenmrsUtil.nullSafeEquals(FetchOrdering.ORDER_ASCENDING, fetchRestriction.getFetchOrdering()))
        order = Order.asc("obsDatetime");

    criteria.addOrder(order);

    Obs observation = new Obs();
    for (String property : restrictions.keySet()) {
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(observation, property)) {
            criteria.add(Restrictions.in(property, propertyValues));
            criteria.addOrder(Order.asc(property));
        }
    }

    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Obs> observations = new ArrayList<Obs>();

    // TODO: further optimization would be adding start date and end date parameter in the obs restrictions
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);

    Integer size = fetchRestriction.getSize();

    // scroll the results
    Obs processedObs = null;
    while (scrollableResults.next() && OpenmrsUtil.compareWithNullAsGreatest(observations.size(), size) == -1) {
        Obs obs = (Obs) scrollableResults.get(0);
        // TODO: thanks to Ampath for the duplicate data, we need to sanitize the query results here
        if (processedObs != null && !obs.isObsGrouping()) {
            if (DateUtils.isSameDay(processedObs.getObsDatetime(), obs.getObsDatetime())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getConcept(), obs.getConcept())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getValueCoded(), obs.getValueCoded())
                    && (OpenmrsUtil.nullSafeEquals(processedObs.getValueNumeric(), obs.getValueNumeric())))
                continue;
        }
        processedObs = obs;
        observations.add(obs);
    }
    scrollableResults.close();
    return observations;
}

From source file:org.openmrs.module.clinicalsummary.db.hibernate.HibernateCoreDAO.java

License:Open Source License

/**
 * @see CoreDAO#getPatientEncounters(Integer, java.util.Map, org.openmrs.module.clinicalsummary.util.FetchRestriction)
 *///from  w w w  . j av  a2 s  .c om
@Override
public List<Encounter> getPatientEncounters(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions, final FetchRestriction fetchRestriction)
        throws DAOException {
    // create a hibernate criteria on the encounter object
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Encounter.class);
    // restrict the encounter that will be returned to specific patient only
    criteria.createAlias("patient", "patient");
    criteria.add(Restrictions.eq("patient.patientId", patientId));

    // default ordering for the returned encounter is descending on encounter datetime
    Order order = Order.desc("encounterDatetime");
    // change the default ordering if the user pass the ascending ordering in the parameters
    if (OpenmrsUtil.nullSafeEquals(FetchOrdering.ORDER_ASCENDING, fetchRestriction.getFetchOrdering()))
        order = Order.asc("encounterDatetime");
    // add the ordering object to the criteria
    criteria.addOrder(order);

    // always get from the first result
    criteria.setFirstResult(0);
    // set the first result to a number if the user pass any value on this parameter (currently not being used)
    if (fetchRestriction.getStart() != null)
        criteria.setFirstResult(fetchRestriction.getStart());

    // specify how many records should be returned
    if (fetchRestriction.getSize() != null)
        criteria.setMaxResults(fetchRestriction.getSize());

    // create a dummy encounter object
    Encounter encounter = new Encounter();
    // iterate over each property in the restriction map
    for (String property : restrictions.keySet()) {
        // get the actual object that will restrict the encounter. this will contains the list of encounter type or list of location
        // or list of provider (currently not being used) passed from caller
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        // check to make sure the list is not empty and the property is readable. example of the property for encounter are
        // encounterType or location of the encounter
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(encounter, property)) {
            // create a restriction on the property with the above list as the value
            criteria.add(Restrictions.in(property, propertyValues));
            // add ordering on that property to prevent slowness when ordering only on encounter datetime (1.6.x only)
            criteria.addOrder(Order.asc(property));
        }
    }

    // exclude all voided encounters
    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Encounter> encounters = new ArrayList<Encounter>();

    // scroll the results and add them to the above list of encounter
    Integer counter = 0;
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);
    while (scrollableResults.next())
        encounters.add((Encounter) scrollableResults.get(0));
    scrollableResults.close();
    return encounters;
}

From source file:org.opentaps.search.IndexingService.java

License:Open Source License

/**
 * Creates the hibernate search index for a given Entity class.
 * @param fullTextSession a <code>FullTextSession</code> value
 * @param entityClass a <code>Class</code> value
 *//*from   w w  w  . j a v  a2s.  co m*/
@SuppressWarnings("unchecked")
private void createIndexForEntity(FullTextSession fullTextSession, Class entityClass) {
    Criteria query = fullTextSession.createCriteria(entityClass)
            //load necessary associations
            .setFetchMode("distributor", FetchMode.JOIN)
            //distinct them (due to collection load)
            .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
            //set flush mode, ensure it will write to disk on commit the transaction
            .setFlushMode(FlushMode.COMMIT)
            //minimize cache interaction
            .setCacheMode(CacheMode.IGNORE).setFetchSize(Session.FETCH_SIZE);
    //scroll in forward only
    ScrollableResults scroll = query.scroll(ScrollMode.FORWARD_ONLY);
    int batch = 0;
    while (scroll.next()) {
        batch++;
        fullTextSession.index(scroll.get(0));
        if (batch % Session.FETCH_SIZE == 0) {
            // batch flush index into session per FETCH_SIZE
            fullTextSession.flushToIndexes();
            fullTextSession.clear();
        }
    }
    // flush last changes
    fullTextSession.flushToIndexes();
    fullTextSession.getSearchFactory().optimize(entityClass);
}

From source file:org.pentaho.reporting.engine.classic.extensions.datasources.hibernate.SimpleHQLDataFactory.java

License:Open Source License

/**
 * Generates a <code>TableModel</code> that gets its contents filled from a <code>ResultSet</code>. The column names
 * of the <code>ResultSet</code> will form the column names of the table model.
 * <p/>/*  w ww . ja v a 2 s.c  o  m*/
 * Hint: To customize the names of the columns, use the SQL column aliasing (done with <code>SELECT nativecolumnname
 * AS "JavaColumnName" FROM ....</code>
 *
 * @param rs           the result set.
 * @param labelMapping defines, whether to use column names or column labels to compute the column index.
 * @return a closeable table model.
 * @throws SQLException if there is a problem with the result set.
 */
public TableModel generateDefaultTableModel(final ScrollableResults rs, final String[] labelMapping)
        throws SQLException {
    final int colcount = labelMapping.length;

    final ArrayList<Object[]> rows = new ArrayList<Object[]>();
    while (rs.next()) {
        final Object[] column = new Object[colcount];
        for (int i = 0; i < colcount; i++) {
            column[i] = rs.get(i);
        }
        rows.add(column);
    }

    final Object[] tempRows = rows.toArray();
    final Object[][] rowMap = new Object[tempRows.length][];
    for (int i = 0; i < tempRows.length; i++) {
        rowMap[i] = (Object[]) tempRows[i];
    }
    return new DefaultTableModel(rowMap, labelMapping);
}