working with DAL part 2

package org.rahmadnet.learning.query;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.query.Query;
import org.junit.Test;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.model.common.order.OrderLine;
import org.openbravo.test.base.OBBaseTest;

public class Query_Test extends OBBaseTest
{
    @Test
    public void testQueryTwo()
    {
        // this sets the user to the Openbravo Admin
        setUserContext("100");

        // create the where clause
        final StringBuilder whereClause = new StringBuilder();
        // set the alias, the OrderLine will be added by the DAL
        whereClause.append(" as ol ");

        // Other filtering options:
        // AND C_ORDER.AD_CLIENT_ID IN ('1') <-- these are done automatically by the dal
        // AND C_ORDER.AD_ORG_ID IN ('1') <-- these are done automatically by the dal

        // AND C_ORDER.ISSOTRX='N'
        whereClause.append(" where ol.salesOrder.salesTransaction=false ");


        // AND C_BPARTNER.C_BPARTNER_ID = ?
        // note the value of the parameter is set below
        whereClause.append(" and ol.salesOrder.businessPartner.id=:bpId ");

        // AND C_ORDER.DOCSTATUS = 'CO'
        whereClause.append(" and ol.salesOrder.documentStatus='CO' ");

        // ORDER BY C_BPARTNER_ID, ID
        whereClause.append(" order by ol.salesOrder.businessPartner.id, ol.id");


        // final Session session = OBDal.getInstance().getSession();
        // session.createQuery(hql.toString());
        final OBQuery<OrderLine> qry = OBDal.getInstance().createQuery(OrderLine.class,
            whereClause.toString());

        // set the business partner parameter
        final Map<String, Object> parameters = new HashMap<>(1);
        parameters.put("bpId", "2E7F35F3ACE94769BAD7A99BF71691B1");
        qry.setNamedParameters(parameters);

        for (OrderLine ol : qry.list()) {

            // C_ORDERLINE.C_ORDERLINE_ID AS ID, C_ORDER.C_ORDER_ID AS C_ORDER_ID
            System.err.println(ol.getId());
            System.err.println(ol.getSalesOrder().getId());

            // C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED,
            System.err.println(ol.getSalesOrder().getDocumentNo());
            System.err.println(ol.getSalesOrder().getOrderDate());

            // C_BPARTNER.C_BPARTNER_ID AS C_BPARTNER_ID, C_BPARTNER.NAME AS PARTNER_NAME,
            System.err.println(ol.getSalesOrder().getBusinessPartner().getId());
            System.err.println(ol.getSalesOrder().getBusinessPartner().getName());

            // AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(C_ORDERLINE.M_PRODUCT_ID),
            // TO_CHAR(?)) AS PRODUCT_NAME,
            System.err.println(ol.getProduct().getIdentifier());
            System.err.println(ol.getProduct().getId());
            System.err.println(ol.getProduct().getName());

            // M_ATTRIBUTESETINSTANCE.DESCRIPTION AS DESCRIPTION,
            if (ol.getAttributeSetValue() != null) {
              System.err.println(ol.getAttributeSetValue().getDescription());
            }

            // C_ORDERLINE.QTYORDERED AS TOTAL_QTY,
            System.err.println(ol.getOrderedQuantity());
            // C_ORDERLINE.QTYORDERED-SUM(COALESCE(M_MATCHPO.QTY,0)) AS QTYORDERED, '-1' AS ISACTIVE
            // todo this we have to repeat the sum query, we use direct hql for this
            final String hql = "select sum(quantity) from ProcurementPOInvoiceMatch "
                + "where goodsShipmentLine is not null and salesOrderLine=:orderLine";
            final Session session = OBDal.getInstance().getSession();
            final Query query = session.createQuery(hql);
            query.setParameter("orderLine", ol);
            final BigDecimal sum = (BigDecimal) query.uniqueResult();
            System.err.println(sum);
      }
    }
}