Example usage for org.hibernate ScrollableResults getBigDecimal

Introduction

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

Prototype

BigDecimal getBigDecimal(int col);

Source Link

Document

Convenience method to read a BigDecimal.

Usage

From source file:com.opensourcestrategies.financials.reports.FinancialReports.java

License:Open Source License

/**
 * <p>Look over invoice adjustments and transform  them into into sales and tax invoice item facts.
 * Thus an adjustment amount is added into discount column of the fact table and this is only
 * currency column affected.</p>/*w ww.ja va  2s  .c  o m*/
 *
 * @param session Hibernate session
 * @throws GenericEntityException
 */
public static void loadInvoiceAdjustments(Session session, Delegator delegator) throws GenericEntityException {

    Transaction tx = session.beginTransaction();

    // retrieve data as scrollable result set.
    // this is join of InvoiceAdjustment and Invoice entities and each record has all required data
    // to create new fact row
    Query invAdjQry = session.createQuery(
            "select IA.invoiceAdjustmentId, IA.invoiceId, IA.amount, I.partyIdFrom, I.invoiceDate, I.currencyUomId from InvoiceAdjustment IA, Invoice I where IA.invoiceId = I.invoiceId and I.invoiceTypeId = 'SALES_INVOICE' and I.statusId not in ('INVOICE_IN_PROCESS', 'INVOICE_CANCELLED', 'INVOICE_VOIDED', 'INVOICE_WRITEOFF')");
    ScrollableResults adjustments = invAdjQry.scroll();

    // iterate over record set
    while (adjustments.next()) {

        // keep result fields in variables as a matter of convenience
        String invoiceId = adjustments.getString(1);
        String invoiceAdjustmentId = adjustments.getString(0);
        BigDecimal amount = adjustments.getBigDecimal(2);
        String organizationPartyId = adjustments.getString(3);
        Timestamp invoiceDate = (Timestamp) adjustments.get(4);
        String currencyUomId = adjustments.getString(5);

        // lookup date dimension
        DateFormat dayOfMonthFmt = new SimpleDateFormat("dd");
        DateFormat monthOfYearFmt = new SimpleDateFormat("MM");
        DateFormat yearNumberFmt = new SimpleDateFormat("yyyy");

        String dayOfMonth = dayOfMonthFmt.format(invoiceDate);
        String monthOfYear = monthOfYearFmt.format(invoiceDate);
        String yearNumber = yearNumberFmt.format(invoiceDate);

        EntityCondition dateDimConditions = EntityCondition.makeCondition(EntityOperator.AND,
                EntityCondition.makeCondition("dayOfMonth", dayOfMonth),
                EntityCondition.makeCondition("monthOfYear", monthOfYear),
                EntityCondition.makeCondition("yearNumber", yearNumber));
        Long dateDimId = UtilEtl.lookupDimension("DateDim", "dateDimId", dateDimConditions, delegator);

        // lookup currency dimension
        Long currencyDimId = UtilEtl.lookupDimension("CurrencyDim", "currencyDimId",
                EntityCondition.makeCondition("uomId", currencyUomId), delegator);

        // lookup organization dimension
        Long organizationDimId = UtilEtl.lookupDimension("OrganizationDim", "organizationDimId",
                EntityCondition.makeCondition("organizationPartyId", organizationPartyId), delegator);

        // creates rows for both fact tables
        TaxInvoiceItemFact taxFact = new TaxInvoiceItemFact();
        taxFact.setDateDimId(dateDimId);
        taxFact.setStoreDimId(0L);
        taxFact.setTaxAuthorityDimId(0L);
        taxFact.setCurrencyDimId(currencyDimId);
        taxFact.setOrganizationDimId(organizationDimId);
        taxFact.setInvoiceId(invoiceId);
        taxFact.setInvoiceAdjustmentId(invoiceAdjustmentId);
        taxFact.setGrossAmount(BigDecimal.ZERO);
        taxFact.setDiscounts(amount);
        taxFact.setRefunds(BigDecimal.ZERO);
        taxFact.setNetAmount(BigDecimal.ZERO);
        taxFact.setTaxable(BigDecimal.ZERO);
        taxFact.setTaxDue(BigDecimal.ZERO);
        session.save(taxFact);

        SalesInvoiceItemFact salesFact = new SalesInvoiceItemFact();
        salesFact.setDateDimId(dateDimId);
        salesFact.setStoreDimId(0L);
        salesFact.setCurrencyDimId(currencyDimId);
        salesFact.setOrganizationDimId(organizationDimId);
        salesFact.setInvoiceId(invoiceId);
        salesFact.setInvoiceAdjustmentId(invoiceAdjustmentId);
        salesFact.setGrossAmount(BigDecimal.ZERO);
        salesFact.setDiscounts(amount);
        salesFact.setRefunds(BigDecimal.ZERO);
        salesFact.setNetAmount(BigDecimal.ZERO);
        session.save(salesFact);

    }
    adjustments.close();
    tx.commit(); // persist result, don't move this statement upper
}

---