working with DAL part 1

package org.rahmadnet.learning.query;

import java.sql.Date;
import java.util.HashMap;
import java.util.Map;

import org.junit.Test;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.model.procurement.RequisitionLine;
import org.openbravo.test.base.OBBaseTest;

public class QueryTest extends OBBaseTest
{
    @Test
    public void testQueryOne()
    {

            setUserContext("100");

            // create the where clause stringbuilder
            final StringBuilder whereClause = new StringBuilder();
            // rl is used as the alias for the RequisitionLine
            whereClause.append(" as rl");

            // Initialize the parameter map, will be used to
            // add parameters to the query
            final Map<String, Object> parameters = new HashMap<>(6);


            // do a left outer join on business partner as it can be null
            // in both the requisition line and the requisition
            whereClause.append(" left outer join rl.businessPartner ");
            whereClause.append(" left outer join rl.requisition.businessPartner ");


            // AND COALESCE(M_REQUISITIONLINE.C_BPARTNER_ID,M_REQUISITION.C_BPARTNER_ID) = C_BPARTNER.C_BPARTNER_ID
            // AND C_BPARTNER.PO_PAYMENTTERM_ID IS NOT NULL
            whereClause.append(" where (rl.businessPartner.pOPaymentTerms != null or rl.requisition.businessPartner.pOPaymentTerms != null)");


            // AND M_REQUISITION.ISACTIVE = 'Y'
            whereClause.append(" and rl.requisition.active=true");

            // AND M_REQUISITIONLINE.ISACTIVE = 'Y' <-- is done by the DAL Layer

            // AND M_REQUISITION.DOCSTATUS = 'DR'
            whereClause.append(" and rl.requisition.documentStatus='DR'");

            // AND M_REQUISITIONLINE.REQSTATUS = 'O'
            whereClause.append(" and rl.requisitionLineStatus='O'");

            // AND (M_REQUISITIONLINE.LOCKEDBY IS NULL OR
            // COALESCE (M_REQUISITIONLINE.LOCKDATE, TO_DATE('01-01-1900', 'DD-MM-YYYY')) < (now()-3))
            whereClause.append(" and (rl.lockedBy = null or rl.lockDate<:lockDate or rl.lockDate = null)");
            final long threeDays = 1000 * 3600 * 24 * 3;


            // AND M_REQUISITION.AD_CLIENT_ID IN ? <-- Done by the DAL
            // AND M_REQUISITIONLINE.AD_ORG_ID IN ? <-- Done by the DAL

            // AND M_REQUISITIONLINE.NEEDBYDATE >= ?
            whereClause.append(" and rl.needByDate>=:needByDateFrom");
            // needByDate from, set at 30 days back
            final long thirtyDays = threeDays * 10;
            parameters.put("needByDateFrom", new Date(System.currentTimeMillis() - thirtyDays));

            // AND AND M_REQUISITIONLINE.NEEDBYDATE < ?
            whereClause.append(" and rl.needByDate<:needByDateTo");
            // needByDate to, set at 30 days in the future
            parameters.put("needByDateTo", new Date(System.currentTimeMillis() + thirtyDays));


            // AND M_REQUISITIONLINE.M_PRODUCT_ID = ?
            whereClause.append(" and rl.product.id=:productId");
            parameters.put("productId", "DA7FC1BB3BA44EC48EC1AB9C74168CED");

            // AND M_REQUISITION.AD_USER_ID = TO_CHAR(?)
            whereClause.append(" and rl.requisition.userContact.id=:userId");
            parameters.put("userId", "100");


            // AND ((M_REQUISITIONLINE.C_BPARTNER_ID = ? OR M_REQUISITION.C_BPARTNER_ID = ?) OR
            // (M_REQUISITIONLINE.C_BPARTNER_ID IS NULL AND M_REQUISITION.C_BPARTNER_ID IS NULL))
            // ORDER BY M_REQUISITIONLINE.NEEDBYDATE, M_REQUISITIONLINE.M_PRODUCT_ID,
            // M_REQUISITIONLINE.M_ATTRIBUTESETINSTANCE_ID
            whereClause
                .append(" and ((rl.businessPartner.id = :bpId or rl.requisition.businessPartner.id = :bpId) or "
                    + "(rl.businessPartner = null and rl.requisition.businessPartner = null))");
            parameters.put("bpId", "2E7F35F3ACE94769BAD7A99BF71691B1");

            parameters.put("lockDate", new Date(System.currentTimeMillis() - threeDays));

            final OBQuery<RequisitionLine> obQuery = OBDal.getInstance().createQuery(RequisitionLine.class,
                whereClause.toString());

            obQuery.setNamedParameters(parameters);

            System.out.println(obQuery.list());

            // now print the select clause parts
            for (RequisitionLine requisitionLine : obQuery.list()) {

                // now print the information from the select clause
                // SELECT M_REQUISITIONLINE_ID, M_REQUISITIONLINE.NEEDBYDATE,
                System.err.println(requisitionLine.getId());
                System.err.println(requisitionLine.getNeedByDate());

                // M_REQUISITIONLINE.QTY - M_REQUISITIONLINE.ORDEREDQTY AS QTYTOORDER,
                if (requisitionLine.getOrderQuantity() != null) {
                System.err.println(requisitionLine.getQuantity().min(requisitionLine.getOrderQuantity()));
                }

                // M_REQUISITIONLINE.PRICEACTUAL AS PRICE,
                System.err.println(requisitionLine.getUnitPrice());


                // AD_COLUMN_IDENTIFIER(to_char('C_BPartner'),
                // to_char(COALESCE(M_REQUISITIONLINE.C_BPARTNER_ID, M_REQUISITION.C_BPARTNER_ID)), ?) AS
                // VENDOR,
                if (requisitionLine.getBusinessPartner() != null) {
                  System.err.println(requisitionLine.getBusinessPartner().getIdentifier());
                } else if (requisitionLine.getRequisition().getBusinessPartner() != null) {
                  System.err.println(requisitionLine.getRequisition().getBusinessPartner().getIdentifier());
                }

                // AD_COLUMN_IDENTIFIER(to_char('M_PriceList'),
                // to_char(COALESCE(M_REQUISITIONLINE.M_PRICELIST_ID,
                // M_REQUISITION.M_PRICELIST_ID)), ?) AS PRICELISTID,
                if (requisitionLine.getPriceList() != null) {
                  System.err.println(requisitionLine.getPriceList().getIdentifier());
                } else if (requisitionLine.getRequisition().getPriceList() != null) {
                  System.err.println(requisitionLine.getRequisition().getPriceList().getIdentifier());
                }

                // AD_COLUMN_IDENTIFIER(to_char('M_Product'),
                // to_char(M_REQUISITIONLINE.M_PRODUCT_ID), ?) AS PRODUCT,
                System.err.println(requisitionLine.getProduct().getIdentifier());

                // AD_COLUMN_IDENTIFIER(to_char('M_AttributeSetInstance'),
                // to_char(M_REQUISITIONLINE.M_ATTRIBUTESETINSTANCE_ID), ?) AS ATTRIBUTE,
                if (requisitionLine.getAttributeSetValue() != null) {
                  System.err.println(requisitionLine.getAttributeSetValue().getIdentifier());
                }

                // AD_COLUMN_IDENTIFIER(to_char('AD_User'), to_char(M_REQUISITION.AD_USER_ID), ?) AS REQUESTER
                System.err.println(requisitionLine.getRequisition().getUserContact().getIdentifier());


            }
      }
    }