GetSalesOrderListForInquirySoRabat

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

import java.util.List;

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.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.CriteriaHelper;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.inv.entity.DeliveryOrderReceipt;
import org.jleaf.erp.inv.entity.Inventory;
import org.jleaf.erp.inv.entity.InventorySo;
import org.jleaf.erp.master.MasterConstants;
import org.jleaf.erp.master.entity.Expedition;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.erp.master.entity.PartnerAddress;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.SalesConstantsForDlg;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.entity.DeliveryOrder;
import org.jleaf.erp.sls.entity.DeliveryOrderItem;
import org.jleaf.erp.sls.entity.Dkb;
import org.jleaf.erp.sls.entity.DkbItem;
import org.jleaf.erp.sls.entity.Resi;
import org.jleaf.erp.sls.entity.ResiItem;
import org.jleaf.erp.sls.entity.SalesInvoice;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.erp.sls.entity.SalesOrderItem;
import org.jleaf.erp.sls.entity.SoAdditionalForDlg;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

//@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 = "docDateFrom", description = "periode dari", type = String.class),
        @Info(name = "docDateTo", description = "periode sampai", type = String.class),
        @Info(name = "salesmanCodeName", description = "salesman code name", type = String.class),
        @Info(name = "customerCodeName", description = "customer code name", type = String.class),
        @Info(name = "warehouseId", description = "whs id", type = Long.class),
        @Info(name = "productCodeName", description = "product code name", type = String.class),
        @Info(name = "soNo", description = "nomor so", type = String.class),
        @Info(name = "rgtoNo", description = "nomor rgto", type = String.class),
        @Info(name = "doNo", description = "nomor do", type = String.class),
        @Info(name = "dkbNo", description = "nomor dkb", type = String.class),
        @Info(name = "siNo", description = "nomor sales invoice", type = String.class),
        @Info(name = "dorNo", description = "nomor do receipt", type = String.class),
        @Info(name = "doDocDateFrom", description = "periode do dari", type = String.class),
        @Info(name = "doDocDateTo", description = "periode do sampai", type = String.class),
        @Info(name = "statSo", description = "status so", type = String.class),
        @Info(name = "statCetakSo", description = "status cetak so", type = String.class),
        @Info(name = "statRgto", description = "status rgto", type = String.class),
        @Info(name = "statDo", description = "status do", type = String.class),
        @Info(name = "statDkb", description = "status dkb", type = String.class),
        @Info(name = "statInvoice", description = "status invoice", type = String.class),
        @Info(name = "statDoReceipt", description = "status do receipt", type = String.class),
        @Info(name = "limit", description = "limit", type = Long.class, required=false),
        @Info(name = "offset", description = "offset", type = Long.class, required=false)
})
@InfoOut(value = {
        @Info(name = "list", description = "", type = List.class)
})
//@formatter:on
public class GetSalesOrderListForInquirySoRabat extends AbstractBusinessFunction implements BusinessFunction {
    private final static Logger log = LoggerFactory.getLogger(GetSalesOrderListForInquirySoRabat.class);

    @Autowired
    SalesOrderDao salesOrderDao;

    @Override
    public String getDescription() {
        return "get sales order list for inquiry so rabat";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        log.info("--=inputDto=--"+inputDto);
        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        ValidationUtil.valDtoContainsKey(inputDto, "docDateFrom");
        ValidationUtil.valDtoContainsKey(inputDto, "docDateTo");
        ValidationUtil.valDtoContainsKey(inputDto, "salesmanCodeName");
        ValidationUtil.valDtoContainsKey(inputDto, "customerCodeName");
        ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
        ValidationUtil.valDtoContainsKey(inputDto, "productCodeName");
        ValidationUtil.valDtoContainsKey(inputDto, "soNo");
        ValidationUtil.valDtoContainsKey(inputDto, "rgtoNo");
        ValidationUtil.valDtoContainsKey(inputDto, "doNo");
        ValidationUtil.valDtoContainsKey(inputDto, "dkbNo");
        ValidationUtil.valDtoContainsKey(inputDto, "resiNo");
        ValidationUtil.valDtoContainsKey(inputDto, "siNo");
        ValidationUtil.valDtoContainsKey(inputDto, "dorNo");
        ValidationUtil.valDtoContainsKey(inputDto, "doDocDateFrom");
        ValidationUtil.valDtoContainsKey(inputDto, "doDocDateTo");
        ValidationUtil.valDtoContainsKey(inputDto, "statSo");
        ValidationUtil.valDtoContainsKey(inputDto, "statCetakSo");
        ValidationUtil.valDtoContainsKey(inputDto, "statRgto");
        ValidationUtil.valDtoContainsKey(inputDto, "statDo");
        ValidationUtil.valDtoContainsKey(inputDto, "statDkb");
        ValidationUtil.valDtoContainsKey(inputDto, "statInvoice");
        ValidationUtil.valDtoContainsKey(inputDto, "statDoReceipt");

        Long limit = GeneralConstants.NULL_REF_VALUE_LONG;
        Long offset = GeneralConstants.NULL_REF_VALUE_LONG;

        if(inputDto.getLong("limit")!=null && !GeneralConstants.NULL_REF_VALUE_LONG.equals(inputDto.getLong("limit"))) {
            limit = inputDto.getLong("limit");
        }

        if(inputDto.getLong("offset")!=null && !GeneralConstants.NULL_REF_VALUE_LONG.equals(inputDto.getLong("offset"))) {
            offset = inputDto.getLong("offset");
        }

        //filter SO
        StringBuilder querySo = new StringBuilder();
        if(!inputDto.getString("soNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("soNo") != null){
            querySo.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("soNo"), "A.doc_no"));
        }

        if(inputDto.getString("statSo").equals(MasterConstants.DRAFT)){
            querySo.append(" AND A.status_doc = '"+MasterConstants.DRAFT+"'");
        }else if(inputDto.getString("statSo").equals(MasterConstants.IN_PROGRESS)){
            querySo.append(" AND A.status_doc = '"+MasterConstants.IN_PROGRESS+"'");
        }else if(inputDto.getString("statSo").equals(MasterConstants.RELEASED)){
            querySo.append(" AND A.status_doc = '"+MasterConstants.RELEASED+"'");
        }else if(inputDto.getString("statSo").equals(MasterConstants.FINAL)){
            querySo.append(" AND A.status_doc = '"+MasterConstants.FINAL+"'");
        }else if(inputDto.getString("statSo").equals(MasterConstants.VOID)){
            querySo.append(" AND A.status_doc = '"+MasterConstants.VOID+"'");
        }

        //filter statCetakSo
        StringBuilder queryCetakSo = new StringBuilder();
        if(inputDto.getString("statCetakSo").equals(GeneralConstants.YES)){
            queryCetakSo.append(" AND R.so_id IS NOT NULL ");
        }else if(inputDto.getString("statCetakSo").equals(GeneralConstants.NO)){
            queryCetakSo.append(" AND R.so_id IS NULL ");
        }

        //filter RGTO
        StringBuilder queryRgto = new StringBuilder();
        if(!inputDto.getString("rgtoNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("rgtoNo") != null){
            queryRgto.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("rgtoNo"), "M.doc_no"));
        }
        if(inputDto.getString("statRgto").equals(GeneralConstants.YES)){
            queryRgto.append(" AND M.inventory_id IS NOT NULL ");
        }else if(inputDto.getString("statRgto").equals(GeneralConstants.NO)){
            queryRgto.append(" AND M.inventory_id IS NULL ");
        }

        //filter DO
        StringBuilder queryDo = new StringBuilder();
        if(!inputDto.getString("doNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("doNo") != null){
            queryDo.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("doNo"), "E.doc_no"));
        }
        if(inputDto.getString("statDo").equals(GeneralConstants.YES)){
            queryDo.append(" AND E.do_id IS NOT NULL ");
        }else if(inputDto.getString("statDo").equals(GeneralConstants.NO)){
            queryDo.append(" AND E.do_id IS NULL ");
        }

        //filter DKB
        StringBuilder queryDkb = new StringBuilder();
        if(!inputDto.getString("dkbNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("dkbNo") != null){
            queryDkb.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("dkbNo"), "I.doc_no"));
        }
        if(inputDto.getString("statDkb").equals(GeneralConstants.YES)){
            queryDkb.append(" AND I.dkb_id IS NOT NULL ");
        }else if(inputDto.getString("statDkb").equals(GeneralConstants.NO)){
            queryDkb.append(" AND I.dkb_id IS NULL ");
        }

        //filter SI
        StringBuilder querySi = new StringBuilder();
        if(!inputDto.getString("siNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("siNo") != null){
            querySi.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("siNo"), "K.doc_no"));
        }
        if(inputDto.getString("statInvoice").equals(GeneralConstants.YES)){
            querySi.append(" AND K.invoice_id IS NOT NULL ");
        }else if(inputDto.getString("statInvoice").equals(GeneralConstants.NO)){
            querySi.append(" AND K.invoice_id IS NULL ");
        }

        //filter DOR
        StringBuilder queryDor = new StringBuilder();
        if(!inputDto.getString("dorNo").equals(GeneralConstants.EMPTY_VALUE) && inputDto.getString("dorNo") != null){
            queryDor.append(" AND ")
                    .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("dorNo"), "L.doc_no"));
        }
        if(inputDto.getString("statDoReceipt").equals(GeneralConstants.YES)){
            queryDor.append(" AND L.do_receipt_id IS NOT NULL ");
        }else if(inputDto.getString("statDoReceipt").equals(GeneralConstants.NO)){
            queryDor.append(" AND L.do_receipt_id IS NULL ");
        };

        List<Object[]> result = null;

        QueryBuilder builder = new QueryBuilder();
        builder.add(" WITH audit_report AS( ")
                .add("      SELECT doc_id AS so_id, doc_no AS so_no ")
                .add("      FROM t_audit_report ")
                .add("      WHERE report_code = :reportSo ")
                .add("      GROUP BY so_id, so_no ")
                .add(" ) ")
                .add(" SELECT A.so_id, A.doc_no AS so_no, A.doc_date, ")
                .add(" CASE WHEN D.current_state = :approved ")
                .add(" THEN to_char(to_timestamp(D.update_datetime, 'YYYYMMDDHH24MISS'), 'YYYYMMDD') ")
                .add(" ELSE :emptyValue ")
                .add(" END AS release_so_date, ")
                .add(" A.ext_doc_no, B.partner_id AS customer_id, B.partner_code ||' - '||B.partner_name  AS customer, ")
                .add(" C.partner_id AS salesman_id, C.partner_code ||' - '|| C.partner_name AS salesman_name, A.remark, A.status_doc, ")
                .add(" f_get_role_name(D.current_role_id) AS next_role, ")
                .add(" COALESCE(E.do_id, -99) AS do_id, COALESCE(E.doc_no, '') AS do_no, COALESCE(E.doc_date, '') AS do_date, ")
                .add(" COALESCE(I.dkb_id, -99) AS dkb_id, COALESCE(I.doc_no, '') AS dkb_no, COALESCE(I.doc_date,'') AS dkb_date, ")
                .add(" COALESCE(K.invoice_id, -99) AS invoice_id, COALESCE(K.doc_no, '') AS invoice_no, COALESCE(K.doc_date, '') AS invoice_date,  ")
                .add(" COALESCE(L.do_receipt_id, -99) AS dor_id, COALESCE(L.doc_no, '') AS dor_no, COALESCE(L.doc_date,'') AS dor_date, ")
                .add(" COALESCE(M.inventory_id, -99) AS rgto_id, COALESCE(M.doc_no, '') AS rgto_no, COALESCE(M.doc_date,'') AS rgto_date, ")
                .add(" COALESCE(Q.receipt_no, '') AS receipt_no, COALESCE(Q.receipt_date, '') AS receipt_date, COALESCE(Q.cost_amount, 0) AS nilai_resi, ")
                .add(" ROUND (SUM(O.nett_item_amount + O.tax_amount) - (SUM(O.nett_item_amount + O.tax_amount) * A.regular_discount_percentage / 100.0 )) AS total_amount, ")
                .add(" CASE WHEN R.so_id IS NOT NULL THEN 'Y' ELSE 'N' END AS status_cetak_so, COALESCE(SS.city, :emptyValue) AS city, ")
                .add(" COALESCE(IX.expedition_name, :emptyValue) AS expedition_name, ")
                .add(" COALESCE(SUM(T.item_amount+U.tax_amount-T.regular_disc_amount-T.promo_disc_amount), 0) AS do_amount ")
                .add(" FROM ").add(SalesOrder.TABLE_NAME).add(" A ")
                .add(" INNER JOIN ").add(SoAdditionalForDlg.TABLE_NAME).add(" N ON A.so_id = N.so_id ")
                .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" B ON A.partner_id = B.partner_id ")
                .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" C ON A.salesman_id = C.partner_id ")
                .add(" INNER JOIN ").add(SalesOrderItem.TABLE_NAME).add(" O ON A.so_id = O.so_id ")
                .add(" INNER JOIN awe_currdoc_status D ON A.tenant_id = D.tenant_id AND D.scheme = :scheme AND A.so_id = D.doc_id ")
                .add(" LEFT JOIN ").add(DeliveryOrder.TABLE_NAME).add(" E ON A.so_id = E.ref_id AND E.ref_doc_type_id = :soDocType AND E.status_doc = :released ")
                .add(" LEFT JOIN ").add(DeliveryOrderItem.TABLE_NAME).add(" EX ON E.do_id = EX.do_id AND O.so_item_id = EX.ref_id ")
                .add(" LEFT JOIN ").add(DkbItem.TABLE_NAME).add(" H ON E.do_id = H.ref_id AND H.ref_doc_type_id = :doDocType ")
                .add(" LEFT JOIN ").add(Dkb.TABLE_NAME).add(" I ON H.dkb_id = I.dkb_id AND I.status_doc = :released")
                .add(" LEFT JOIN ").add(Expedition.TABLE_NAME).add(" IX ON I.expedition_id = IX.expedition_id ")
                .add(" LEFT JOIN ").add(ResiItem.TABLE_NAME).add(" P ON E.do_id = P.ref_id AND P.ref_doc_type_id = :doDocType ")
                .add(" LEFT JOIN ").add(Resi.TABLE_NAME).add(" Q ON P.resi_id = Q.resi_id ")
                .add(" LEFT JOIN ").add(SalesInvoice.TABLE_NAME).add(" K ON A.so_id = K.ref_id AND K.ref_doc_type_id = :soDocType AND K.status_doc = :released ")
                .add(" LEFT JOIN ").add(DeliveryOrderReceipt.TABLE_NAME).add(" L ON E.do_id = L.ref_id AND L.ref_doc_type_id = :doDocType AND L.status_doc = :released")
                .add(" LEFT JOIN ").add(InventorySo.TABLE_NAME).add(" S ON A.so_id = S.so_id ")
                .add(" LEFT JOIN ").add(Inventory.TABLE_NAME).add(" M ON S.inventory_id = M.inventory_id AND M.ref_doc_type_id = :soDocType AND M.doc_type_id = :rgtoDocType  AND M.status_doc = :released ")
                .add(" LEFT JOIN audit_report R ON A.so_id = R.so_id AND A.doc_no = R.so_no ")
                .add(" LEFT JOIN ").add(PartnerAddress.TABLE_NAME).add(" SS ON A.partner_id = SS.partner_id AND SS.flg_official = :yes ")
                .add(" LEFT JOIN ").add(SalesConstantsForDlg.TABLE_SO_BALANCE_INVOICE)
                .add("                      T ON E.tenant_id = T.tenant_id AND E.ou_id = T.ou_id AND A.partner_id = T.partner_id ")
                .add("                      AND E.doc_type_id = T.ref_doc_type_id AND E.do_id = T.ref_id AND T.ref_item_id = EX.do_item_id ")
                .add("                      AND T.do_receipt_item_id = :emptyId ")
                .add(" LEFT JOIN ").add(SalesConstantsForDlg.TABLE_SO_BALANCE_INVOICE_TAX)
                .add("                      U ON E.tenant_id = U.tenant_id AND E.ou_id = U.ou_id AND A.partner_id = U.partner_id ")
                .add("                      AND E.doc_type_id = U.ref_doc_type_id AND E.do_id = U.ref_id AND U.ref_item_id = EX.do_item_id ")
                .add("                      AND U.do_receipt_item_id = :emptyId ")
                .add(" WHERE A.tenant_id = :tenantId")
                .addIfNotEquals(inputDto.getLong("ouId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND A.ou_id = "+inputDto.getLong("ouId"))
                .add("      AND A.doc_date BETWEEN :dateFrom AND :dateTo")
                .add("      AND ((E.doc_date BETWEEN :doDateFrom AND :doDateTo) OR E.doc_date IS NULL ) ")
                .addIfNotEmpty(inputDto.getString("salesmanCodeName"), " AND (" + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("salesmanCodeName"), "f_get_partner_code(A.salesman_id)") + " OR " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("salesmanCodeName"), "f_get_partner_name(A.salesman_id)") + ")")
                .addIfNotEmpty(inputDto.getString("customerCodeName"), " AND (" + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("customerCodeName"), "f_get_partner_code(A.partner_id)") + " OR " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("customerCodeName"), "f_get_partner_name(A.partner_id)") + ")")
                .addIfNotEquals(inputDto.getLong("warehouseId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND N.warehouse_id = "+inputDto.getLong("warehouseId"))
                .addIfNotEmpty(inputDto.getString("productCodeName"), " AND (" + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "f_get_product_code(O.product_id)") + " OR " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "f_get_product_name(O.product_id)") + ")")
                .addIfNotEmpty(inputDto.getString("resiNo"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("rgtoNo"), "P.receipt_no"))
                .add(querySo.toString())
                .add(queryCetakSo.toString())
                .add(queryRgto.toString())
                .add(queryDo.toString())
                .add(queryDkb.toString())
                .add(querySi.toString())
                .add(queryDor.toString())
                .add(" AND f_authorize_user_role_policy_ou(:tenantId, :userId, :roleId, A.ou_id) = 1 ")
                .add(" AND f_authorize_user_role_policy_warehouse(:tenantId, :userId, :roleId, N.warehouse_id) = 1 ")
                .add(" GROUP BY A.so_id, B.partner_id, C.partner_id, E.do_id, I.dkb_id, K.invoice_id, L.do_receipt_id, M.inventory_id, Q.resi_id, N.flg_add_discount, D.current_role_id, R.so_id, D.current_state, D.update_datetime, SS.city, IX.expedition_name ")
                .add(" ORDER BY A.doc_no ");
        if(!GeneralConstants.NULL_REF_VALUE_LONG.equals(limit) && !GeneralConstants.NULL_REF_VALUE_LONG.equals(offset)) {
            builder.add(" LIMIT :limit OFFSET :offset ");
        }

        Query q = salesOrderDao.createNativeQuery(builder.toString());
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("soDocType", SalesConstants.DOCUMENT_SALES_ORDER);
        q.setParameter("doDocType", SalesConstants.DOCUMENT_DELIVERY_ORDER);
        q.setParameter("rgtoDocType", SalesConstantsForDlg.DOCUMENT_RGTO_NON_OUTLET);
        q.setParameter("dateFrom", inputDto.getString("docDateFrom"));
        q.setParameter("dateTo", inputDto.getString("docDateTo"));
        q.setParameter("doDateFrom", inputDto.getString("doDocDateFrom"));
        q.setParameter("doDateTo", inputDto.getString("doDocDateTo"));
        q.setParameter("released", MasterConstants.RELEASED);
        q.setParameter("approved", MasterConstants.WORKFLOW_STATUS_APPROVED);
        q.setParameter("userId", inputDto.getLong("userId"));
        q.setParameter("roleId", inputDto.getLong("roleId"));
        q.setParameter("scheme", SalesConstantsForDlg.SCHEME_SALES_ORDER);
        q.setParameter("reportSo", SalesConstantsForDlg.REPORT_SALES_ORDER);
        q.setParameter("yes", GeneralConstants.YES);
        q.setParameter("emptyValue", GeneralConstants.EMPTY_VALUE);
        q.setParameter("emptyId", GeneralConstants.NULL_REF_VALUE_LONG);
        if(!GeneralConstants.NULL_REF_VALUE_LONG.equals(limit) && !GeneralConstants.NULL_REF_VALUE_LONG.equals(offset)) {
            q.setParameter("limit", limit);
            q.setParameter("offset", offset);
        }

        result = q.getResultList();
        return new Dto().putList("list", DtoUtil.createDtoListFromArray(result,
                "soId", "soNo", "soDocDate", "soReleaseDate", "soRefDocNo", "customerId","customer", "salesmanId","salesman", "soRemark", "soStatus",
                "nextRole","doId", "doNo", "doDate", "dkbId", "dkbNo", "dkbDate", "invoiceId", "invoiceNo", "invoiceDate", "dorId",
                "dorNo", "dorDate", "rgtoId", "rgtoNo","rgtoDate", "receiptNo", "receiptDate", "nilaiResi","totalAmount","statusCetakSo", "city",
                "expeditionName", "nilaiDo"));
    }
}