GetSoSettlementItemListByBrandIdForCopySoSettlement

package org.jleaf.erp.sls.bo.sosettlement;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.CoreException;
import org.jleaf.core.CoreExceptionConstants;
import org.jleaf.core.Dto;
import org.jleaf.core.annotation.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.entity.Product;
import org.jleaf.erp.master.entity.ProductCustom;
import org.jleaf.erp.sls.dao.SoSettlementItemDao;
import org.jleaf.erp.sls.entity.SoSettlement;
import org.jleaf.erp.sls.entity.SoSettlementItem;
import org.jleaf.util.DtoUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @author Congky, Mei 04, 2016
 *
 * Get So Settlement Item List By Brand Id For Copy So Settlement
 *
**/

//@formatter:off
@Service
@InfoIn(value = {
        @Info(name = "soSettlementId", description = "so settlement id", type = Long.class),
        @Info(name = "brandId", description = "Brand id", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "soSettlementItemList", description = "list of SO Settlement Item (id, tenantId, brandId, brandCode, brandName, productid, productCode, productName, stock, qtySo, qtySettlement, version, power)", type = List.class)
})
@ErrorList(errorKeys = {
})
//@formatter:on
public class GetSoSettlementItemListByBrandIdForCopySoSettlement extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    private SoSettlementItemDao soSettlementItemDao;

    @Override
    public String getDescription() {
        return "Get So Settlement Item List By Brand Id For Copy So Settlement";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        if (inputDto == null) {
            throw new CoreException(CoreExceptionConstants.DTO_CANNOT_NULL);
        }

        List<Object[]> result = null;

        //@formatter:off
        QueryBuilder builder = new QueryBuilder();
        builder.add("SELECT A.so_settlement_item_id, A.so_settlement_id, A.tenant_id, B.brand_id, ")
               .add(" f_get_brand_code(B.brand_id) AS brand_code, f_get_brand_name(B.brand_id) AS brand_name, ")
               .add(" A.product_id, B.product_code, B.product_name, C.color, C.size, ")
               .add(" f_get_uom_code(B.base_uom_id) AS uom_code, f_get_uom_name(B.base_uom_id) AS uom_name, ")
               .add(" f_get_product_balance_stock_exclude_reserved(A.tenant_id, B.product_id, D.ou_id) AS stock, ")
               .add(" A.qty_so, A.qty_settlement, A.version,  ")
               .add(" CASE WHEN C.size = 'PL' THEN '00.00' ELSE substring(C.size, 1, 1)||lpad(substring(C.size, 2), 5, '0') END AS power_a ")
               .add(" FROM ").add(SoSettlementItem.TABLE_NAME).add(" A ")
               .add(" INNER JOIN ").add(Product.TABLE_NAME).add(" B ON A.product_id = B.product_id ")
               .add(" INNER JOIN ").add(ProductCustom.TABLE_NAME).add(" C ON B.product_id = C.product_id ")
               .add(" INNER JOIN ").add(SoSettlement.TABLE_NAME).add(" D ON A.so_settlement_id = D.so_settlement_id ")
               .add(" WHERE A.so_settlement_id = :soSettlementId AND B.brand_id = :brandId AND A.qty_settlement > 0")
               .add(" ORDER BY product_name COLLATE \"C\" ");
        //@formatter:on

        Query query = soSettlementItemDao.createNativeQuery(builder.toString());
        query.setParameter("soSettlementId", inputDto.getLong("soSettlementId"));
        query.setParameter("brandId", inputDto.getLong("brandId"));

        result = query.getResultList();
        return new Dto().putList("soSettlementItemList", DtoUtil.createDtoListFromArray(result, "id", "soSettlementId",
                "tenantId", "brandId", "brandCode", "brandName", "productId", "productCode", "productName", "color", "size",
                "uomCode", "uomName", "stock", "qtySo", "qtySettlement", "version", "power"));
    }

}