Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package org.jleaf.erp.sls.bo.salesorder;
- import java.text.SimpleDateFormat;
- import java.util.Calendar;
- import javax.persistence.Query;
- import org.jleaf.core.AbstractBusinessFunction;
- import org.jleaf.core.BusinessFunction;
- import org.jleaf.core.Dto;
- import org.jleaf.core.GeneralConstants;
- 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.PartnerType;
- import org.jleaf.erp.sls.SalesConstantsForXcom;
- import org.jleaf.erp.sls.dao.SalesOrderDao;
- import org.jleaf.erp.sls.entity.SalesOrder;
- import org.jleaf.util.Calc;
- import org.jleaf.util.ValidationUtil;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- /**
- *
- * @author Adrian
- * Apr 3, 2017
- */
- //@formatter:off
- @Service
- @InfoIn(value = {
- @Info(name = "tenantId", description = "tenant id", type = Long.class),
- @Info(name = "ouId", description = "ou id", type = Long.class),
- @Info(name = "partnerId", description = "partner id", type = Long.class)
- })
- @InfoOut(value = {
- @Info(name = "amount", description = "remaining credit amount", type = Double.class) })
- @ErrorList(errorKeys = {
- })
- //@formatter:on
- public class GetRemainingCreditAmountForSoApproval extends AbstractBusinessFunction implements BusinessFunction {
- @Autowired
- SalesOrderDao salesOrderDao;
- public String getDescription() {
- // write description of this business function here
- return "get remaining credit amount";
- }
- @SuppressWarnings("unchecked")
- public Dto execute(Dto inputDto) throws Exception {
- // validate inputDto have key in @InfoIn
- ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
- ValidationUtil.valDtoContainsKey(inputDto, "ouId");
- ValidationUtil.valDtoContainsKey(inputDto, "partnerId");
- // @formatter:off
- // create native sql
- QueryBuilder builder = new QueryBuilder();
- builder.add(" WITH os_invoice AS ( ")
- .add(" SELECT COALESCE(SUM(A.amount - A.payment_amount), 0) AS os_amount FROM ")
- .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
- .add(" A LEFT OUTER JOIN ")
- .add(SalesOrder.TABLE_NAME)
- .add(" B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_id ")
- .add(" WHERE A.tenant_id = :tenantId ")
- .add(" AND A.ou_id = :ouId ")
- .add(" AND A.partner_id = :partnerId ")
- .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
- .add(" AND A.flg_payment = :NO )")
- .add(" SELECT (A.amount_limit - B.os_amount) FROM ")
- .add(PartnerType.TABLE_NAME)
- .add(" A, os_invoice B ")
- .add(" WHERE A.partner_id = :partnerId ")
- .add(" AND A.group_partner = :CUSTOMER ");
- // @formatter:on
- // create native query
- Query q = salesOrderDao.createNativeQuery(builder.toString());
- // set query parameter (if any)
- q.setParameter("tenantId", inputDto.getLong("tenantId"));
- q.setParameter("ouId", inputDto.getLong("ouId"));
- q.setParameter("partnerId", inputDto.getLong("partnerId"));
- q.setParameter("startDate", GeneralConstants.START_DATE);
- q.setParameter("endDate", GeneralConstants.END_DATE);
- q.setParameter("NO", GeneralConstants.NO);
- q.setParameter("CUSTOMER", SalesConstantsForXcom.GROUP_PARTNER_CUSTOMER);
- Object amount = q.getSingleResult();
- // @formatter:off
- // create native sql
- QueryBuilder builderTax = new QueryBuilder();
- builderTax.add(" SELECT COALESCE(SUM(A.gov_tax_amount - A.payment_amount), 0) AS os_amount FROM ")
- .add(SalesConstantsForXcom.INVOICE_TAX_AR_BALANCE_TABLE_NAME)
- .add(" A INNER JOIN ")
- .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
- .add(" B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id ")
- .add(" WHERE A.tenant_id = :tenantId ")
- .add(" AND A.ou_id = :ouId ")
- .add(" AND A.partner_id = :partnerId ")
- .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
- .add(" AND A.flg_payment = :NO ");
- // @formatter:on
- // create native query
- Query qTax = salesOrderDao.createNativeQuery(builderTax.toString());
- // set query parameter (if any)
- qTax.setParameter("tenantId", inputDto.getLong("tenantId"));
- qTax.setParameter("ouId", inputDto.getLong("ouId"));
- qTax.setParameter("partnerId", inputDto.getLong("partnerId"));
- qTax.setParameter("startDate", GeneralConstants.START_DATE);
- qTax.setParameter("endDate", GeneralConstants.END_DATE);
- qTax.setParameter("NO", GeneralConstants.NO);
- Object taxAmount = qTax.getSingleResult();
- // return query result
- return new Dto().put("amount", new Calc(Double.valueOf(amount.toString())).subtract(Double.valueOf(taxAmount.toString())).doubleValue());
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement