GetApprovedSoMlmListForNewSalesSupport

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

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.CriteriaHelper.CriteriaHelperEnum;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.erp.master.entity.Product;
import org.jleaf.erp.master.entity.ProductCatalog;
import org.jleaf.erp.mlm.entity.Ds;
import org.jleaf.erp.mlm.entity.DsArea;
import org.jleaf.erp.sls.SalesMlmConstantsForPaloma;
import org.jleaf.erp.sls.dao.SalesOrderMlmDao;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.erp.sls.entity.SalesOrderBalanceItem;
import org.jleaf.erp.sls.entity.SalesOrderItem;
import org.jleaf.erp.sls.entity.SalesOrderMlm;
import org.jleaf.erp.sls.entity.SalesOrderMlmItem;
import org.jleaf.erp.sls.entity.SoApprovedStatus;
import org.jleaf.erp.sls.entity.SoExt;
import org.jleaf.util.DtoUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Menambahkan union dengan SO Online
 * modified by Adrian
 * Jul 17, 2018
 */

@Service
@InfoIn(value = {
        @Info(name = "tenantLoginId", description = "tenant login id", type = Long.class),
        @Info(name = "partnerId", description = "partner id", type = Long.class, required = false),
        @Info(name = "ouId", description = "ou id", type = Long.class, required = false),
        @Info(name = "startDate", description = "start date", type = String.class, required = false),
        @Info(name = "endDate", description = "end date", type = String.class, required = false),
        @Info(name = "docNo", description = "doc no", type = String.class, required = false),
        @Info(name = "flgPicking", description = "flag Picking", type = String.class, required = false),
        @Info(name = "flgScan", description = "flag Scan", type = String.class, required = false),
        @Info(name = "flgPacking", description = "flag Packing", type = String.class, required = false),
        @Info(name = "flgShipment", description = "flg Shipment", type = String.class, required = false),
        @Info(name = "flgDropship", description = "flg Dropship", type = String.class, required = false),
        @Info(name = "statusSo", description = "status So", type = String.class, required = false),
        @Info(name = "sortById", description = "sort by id", type = Long.class),
        @Info(name = "limit", description = "limit", type = Long.class),
        @Info(name = "offset", description = "offset", type = Long.class)
})
@InfoOut(
        value = { @Info(name = "soMlmList", description = "list so mlm(soId,tenantId,docNo,docDate,partnerId,partnerName,partnerCode,remark,flgPicking,flgScan,flgPacking,flgDelivery,statusDoc, totalHargaKatak,totalHargaKatalogTerkirim, totalHargaMember, totalHargaMemberTerkirim, nilaiKomisi, nilaiKomisiTerkirim,totalPembelian,totalPembelianTerkirim, additionalCost, additionalCostTerkirim, nextRoleForSubmit,autoApprovalTime,autoApprovalName,ssApprovalTime,ssApprovalName,financeApprovalTime,financeApprovalName,lastScanPickingTime,lastScanPickingName, flgSoOnline)", type = List.class) }
)

public class GetApprovedSoMlmListForNewSalesSupport extends AbstractBusinessFunction implements BusinessFunction {

    private static final Logger log = LoggerFactory.getLogger(GetApprovedSoMlmListForNewSalesSupport.class);

    @Autowired
    SalesOrderMlmDao salesOrderMlmDao;

    @SuppressWarnings("unchecked")
    public Dto execute(Dto inputDto) throws Exception {
        List<Object[]> result = null;
        //log.info("ini isi input dto di get approved"+inputDto);

        String sortField = inputDto.getString("sortField");
        String sortOrder = inputDto.getString("sortOrder");
        String productCodeName = inputDto.getString("productCodeName");
        String productCatalogCodeName = inputDto.getString("productCatalogCodeName");

        StringBuilder dynamicQuery = new StringBuilder(200);
        //String dynamicQuery = GeneralConstants.EMPTY_VALUE;

        if (sortField.equals(SalesMlmConstantsForPaloma.TOTAL_PEMBELIAN )){
            dynamicQuery.append(" ORDER BY total_pembelian ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.DOC_NO)){
            dynamicQuery.append(" ORDER BY A.doc_no ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.DOC_DATE)){
            dynamicQuery.append(" ORDER BY A.doc_date ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.PARTNER_CODE)){
            dynamicQuery.append(" ORDER BY B.partner_code ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.PARTNER_NAME)){
            dynamicQuery.append(" ORDER BY B.partner_name ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.TOTAL_HARGA_MEMBER)){
            dynamicQuery.append(" ORDER BY total_harga_member ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.NEXT_ROLE_FOR_SUBMIT)){
            dynamicQuery.append(" ORDER BY next_role_for_submit ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.TOTAL_HARGA_MEMBER_TERKIRIM)){
            dynamicQuery.append(" ORDER BY total_harga_member_terkirim ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.TOTAL_PEMBELIAN_TERKIRIM)){
            dynamicQuery.append(" ORDER BY total_pembelian_terkirim ");
        }else if (sortField.equals(SalesMlmConstantsForPaloma.DS_AREA)){
            dynamicQuery.append(" ORDER BY F.area_name ");
        }

        if (sortOrder.equals(SalesMlmConstantsForPaloma.DECENDING)){
            dynamicQuery.append(" DESC ");
        }else if (sortOrder.equals(SalesMlmConstantsForPaloma.ASCENDING)){
            dynamicQuery.append(" ASC ");
        }

        QueryBuilder builder = new QueryBuilder();
        //SO MLM
        builder.add(" ( ")
                .add(" SELECT A.so_mlm_id, A.tenant_id, A.doc_no, ")
                .add(" to_char(to_timestamp(A.doc_date, 'YYYYMMDDHH24MISS'),'DD-MM-YYYY') AS docDate, ")
                .add(" A.partner_id, B.partner_name, B.partner_code, A.remark, A.flg_picking, ")
                .add(" A.flg_scan, A.flg_packing, A.flg_shipment, A.flg_dropship, A.ou_id, A.status_doc, ")
                .add(" CASE WHEN C.last_print_picking_time != '' THEN  to_char(to_timestamp(C.last_print_picking_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END  AS waktu_cetak_so, ")
                .add(" C.last_print_picking_user, ")
                .add(" TRIM(TO_CHAR(D.total_catalog_price_amount, '999G999G999G999G990')) AS total_catalog_price_amount, TRIM(TO_CHAR(D.delivered_total_catalog_price_amount, '999G999G999G999G990')) AS delivered_total_catalog_price_amount, ")
                .add(" TRIM(TO_CHAR(D.total_member_price_amount, '999G999G999G999G990')) AS total_harga_member, TRIM(TO_CHAR(D.delivered_total_member_price_amount, '999G999G999G999G990')) AS total_harga_member_terkirim, ")
                .add(" TRIM(TO_CHAR(D.total_commission_amount, '999G999G999G999G990')) AS total_commission_amount, TRIM(TO_CHAR(D.delivered_total_commission_amount, '999G999G999G999G990')) AS delivered_total_commission_amount, ")
                .add(" TRIM(TO_CHAR(D.total_invoiced_amount, '999G999G999G999G990')) AS total_pembelian, TRIM(TO_CHAR(D.delivered_total_invoiced_amount, '999G999G999G999G990')) AS total_pembelian_terkirim, ")
                .add(" TRIM(TO_CHAR(D.total_additional_cost, '999G999G999G999G990')) AS total_additional_cost, TRIM(TO_CHAR(D.delivered_total_additional_cost, '999G999G999G999G990')) AS delivered_total_additional_cost, ")
                .add(" TRIM(TO_CHAR(D.total_member_price_amount, '999G999G999G999G990')) AS total_harga_netto, TRIM(TO_CHAR((D.delivered_total_member_price_amount - D.delivered_total_commission_amount), '999G999G999G999G990')) AS delivered_total_harga_netto, ")
                .add(" f_get_next_role_for_submit_inquiry_so_mlm_for_paloma(A.tenant_id, A.so_mlm_id) AS next_role_for_submit, ")
                .add(" CASE WHEN C.last_auto_approve_time != '' THEN to_char(to_timestamp(C.last_auto_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS autoApprovalTime, ")
                .add(" COALESCE(C.last_auto_approve_user,'') AS autoApprovalName, ")
                .add(" CASE WHEN C.last_ss_approve_time != '' THEN to_char(to_timestamp(C.last_ss_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS ssApprovalTime, ")
                .add(" COALESCE(C.last_ss_approve_user,'') AS ssApprovalName, ")
                .add(" CASE WHEN C.last_fin_approve_time != '' THEN to_char(to_timestamp(C.last_fin_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS financeApprovalTime,")
                .add(" COALESCE(C.last_fin_approve_user,'') AS financeApprovalName, ")
                .add(" CASE WHEN C.last_scan_picking_time != '' THEN to_char(to_timestamp(C.last_scan_picking_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END  AS lastScanPickingTime, ")
                .add(" COALESCE(C.last_scan_picking_user,'') AS lastScanPickingName, ")
                .add(" A.status_so, A.so_mlm_id, A.version,A.flg_tutup_so, A.workflow_status, F.area_name ")
                .add(" , :NO AS flgSoOnline ")
                .add(" FROM ").add(SalesOrderMlm.TABLE_NAME).add(" A ")
                .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" B on B.partner_id = A.partner_id ")
                .add(" LEFT JOIN ").add(SoApprovedStatus.TABLE_NAME).add(" C ON A.so_mlm_id = C.so_mlm_id ")
                .add(" INNER JOIN sl_so_mlm_totals D ON A.so_mlm_id = D.so_mlm_id ")
                .add(" INNER JOIN ").add(Ds.TABLE_NAME).add(" E ON B.partner_id = E.partner_id ")
                .add(" INNER JOIN ").add(DsArea.TABLE_NAME).add(" F ON E.ds_area_id = F.ds_area_id ")
                .add(" WHERE A.status_so IN ").add(inputDto.getString("statusSo"))
                .add(" AND A.doc_date BETWEEN :startDate AND :endDate ")
                .add(" AND A.ou_id = :ouId ")
                .addIfNotEquals(inputDto.get("tenantLoginId"), GeneralConstants.NULL_REF_VALUE_LONG,  " AND A.tenant_id = "+inputDto.get("tenantLoginId"))
                .addIfNotNull(inputDto.get("docNo")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("docNo"), " A.doc_no ", CriteriaHelperEnum.BOTH))
                .addIfNotNull(inputDto.get("flgDropship")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("flgDropship"), " A.flg_dropship "))
                .addIfNotEquals(inputDto.get("partnerId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND A.partner_id = "+inputDto.getLong("partnerId"))
                .add(" AND NOT EXISTS ( SELECT 1 from sl_so_mlm_cicilan_status Y WHERE Y.so_mlm_id=A.so_mlm_id ) ");

        if(!productCatalogCodeName.equals(GeneralConstants.EMPTY_VALUE) || !productCodeName.equals(GeneralConstants.EMPTY_VALUE)){
        builder.add(" AND EXISTS ( ")
                .add(" SELECT 1 FROM ")
                .add(SalesOrderMlm.TABLE_NAME).add(" D ")
                .add(" INNER JOIN ").add(SalesOrderMlmItem.TABLE_NAME).add(" E ON E.so_mlm_id = D.so_mlm_id")
                .add(" INNER JOIN ").add(ProductCatalog.TABLE_NAME).add(" F ON E.product_catalog_id = F.product_catalog_id")
                .add(" INNER JOIN ").add(Product.TABLE_NAME).add(" G ON F.product_id = G.product_id")
                .add(" WHERE D.so_mlm_id = A.so_mlm_id ")
                .addIfNotEmpty(productCatalogCodeName , new StringBuilder().append(" AND (").append(CriteriaHelper.likeExpressionIgnoreCase(productCatalogCodeName, " F.product_catalog_code "))
                        .append(" OR ").append(CriteriaHelper.likeExpressionIgnoreCase(productCatalogCodeName, " F.product_catalog_name ")).append(")").toString())
                .addIfNotEmpty(productCodeName , new StringBuilder().append(" AND (").append(CriteriaHelper.likeExpressionIgnoreCase(productCodeName, " G.product_code "))
                        .append(" OR ").append(CriteriaHelper.likeExpressionIgnoreCase(productCodeName, " G.product_name ")).append(")").toString())
                .add(" GROUP BY D.so_mlm_id ")
                .add(" ) ");
        }
        builder.add(" GROUP BY A.so_mlm_id, A.tenant_id, A.doc_no, A.doc_date, A.partner_id, B.partner_name, B.partner_code, ")
                .add(" A.remark, A.flg_picking, A.flg_scan, A.flg_packing, A.flg_shipment, A.flg_dropship, A.ou_id, A.status_doc, ")
                .add(" C.last_print_picking_time, C.last_print_picking_user, C.last_fin_approve_time, C.last_fin_approve_user, ")
                .add(" C.last_auto_approve_time, C.last_auto_approve_user, C.last_ss_approve_time, C.last_ss_approve_user,")
                .add(" C.last_scan_picking_time,  C.last_scan_picking_user, D.total_catalog_price_amount, D.delivered_total_catalog_price_amount, ")
                .add(" D.total_member_price_amount, D.delivered_total_member_price_amount, D.total_commission_amount, D.delivered_total_commission_amount, ")
                .add(" D.total_invoiced_amount, D.delivered_total_invoiced_amount, D.total_additional_cost, D.delivered_total_additional_cost, F.area_name ")
                .add(dynamicQuery.toString());

        builder.add(" ) UNION ( ");

        //SO Online
        builder.add(" WITH harga AS ( ")
                .add("SELECT C.so_id, ")
                .add(" SUM(A.qty_so * B.gross_sell_price) AS total_gross_sell_price, ")
                .add(" SUM(A.qty_dlv_int * B.gross_sell_price) AS total_gross_sell_price_delivered ")
                .add(" FROM ")
                .add(SalesOrderBalanceItem.TABLE_NAME)
                .add(" A INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" B ON A.tenant_id = B.tenant_id AND A.so_item_id = B.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" C ON B.tenant_id = C.tenant_id AND B.so_id = C.so_id ")
                .add(" INNER JOIN ")
                .add(SoExt.TABLE_NAME)
                .add(" D ON C.so_id = D.so_id ")
                .add(" WHERE C.doc_type_id = :docTypeSoOnline ")
                .addIfNotEquals(inputDto.get("tenantLoginId"), GeneralConstants.NULL_REF_VALUE_LONG,  " AND A.tenant_id = "+inputDto.get("tenantLoginId"))
                .addIfNotNull(inputDto.get("docNo")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("docNo"), " A.doc_no ", CriteriaHelperEnum.BOTH))
                .addIfNotNull(inputDto.get("flgDropship")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("flgDropship"), " D.flg_dropship "))
                .addIfNotEquals(inputDto.get("partnerId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND A.partner_id = "+inputDto.getLong("partnerId"))
                .add(" GROUP BY C.so_id ")
                .add(" ) ")
                .add(" SELECT A.so_id, A.tenant_id, A.doc_no, ")
                .add(" to_char(to_timestamp(A.doc_date, 'YYYYMMDDHH24MISS'),'DD-MM-YYYY') AS docDate, ")
                .add(" A.partner_id, B.partner_name, B.partner_code, A.remark, D.flg_picking, ")
                .add(" D.flg_scan, D.flg_packing, D.flg_shipment, D.flg_dropship, A.ou_id, A.status_doc, ")
                .add(" CASE WHEN C.last_print_picking_time != '' THEN  to_char(to_timestamp(C.last_print_picking_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END  AS waktu_cetak_so, ")
                .add(" C.last_print_picking_user, ")
                .add(" TRIM(TO_CHAR(E.total_gross_sell_price, '999G999G999G999G990')) AS total_catalog_price_amount, TRIM(TO_CHAR(E.total_gross_sell_price_delivered, '999G999G999G999G990')) AS delivered_total_catalog_price_amount, ")
                .add(" TRIM(TO_CHAR(E.total_gross_sell_price, '999G999G999G999G990')) AS total_harga_member, TRIM(TO_CHAR(E.total_gross_sell_price_delivered, '999G999G999G999G990')) AS total_harga_member_terkirim, ")
                .add(" TRIM(TO_CHAR(0, '999G999G999G999G990')) AS total_commission_amount, TRIM(TO_CHAR(0, '999G999G999G999G990')) AS delivered_total_commission_amount, ")
                .add(" TRIM(TO_CHAR(E.total_gross_sell_price + f_get_so_additional_cost(A.so_id), '999G999G999G999G990')) AS total_pembelian, TRIM(TO_CHAR(E.total_gross_sell_price_delivered + f_get_so_additional_cost(A.so_id), '999G999G999G999G990')) AS total_pembelian_terkirim, ")
                .add(" TRIM(TO_CHAR(f_get_so_additional_cost(A.so_id), '999G999G999G999G990')) AS total_additional_cost, TRIM(TO_CHAR(f_get_so_additional_cost(A.so_id), '999G999G999G999G990')) AS delivered_total_additional_cost, ")
                .add(" TRIM(TO_CHAR(E.total_gross_sell_price, '999G999G999G999G990')) AS total_harga_netto, TRIM(TO_CHAR(E.total_gross_sell_price_delivered, '999G999G999G999G990')) AS delivered_total_harga_netto, ")
                .add(" f_get_next_role_for_submit_inquiry_so_online_for_paloma(A.tenant_id, A.so_mlm_id) AS next_role_for_submit, ")
                .add(" CASE WHEN C.last_auto_approve_time != '' THEN to_char(to_timestamp(C.last_auto_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS autoApprovalTime, ")
                .add(" COALESCE(C.last_auto_approve_user,'') AS autoApprovalName, ")
                .add(" CASE WHEN C.last_ss_approve_time != '' THEN to_char(to_timestamp(C.last_ss_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS ssApprovalTime, ")
                .add(" COALESCE(C.last_ss_approve_user,'') AS ssApprovalName, ")
                .add(" CASE WHEN C.last_fin_approve_time != '' THEN to_char(to_timestamp(C.last_fin_approve_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END AS financeApprovalTime,")
                .add(" COALESCE(C.last_fin_approve_user,'') AS financeApprovalName, ")
                .add(" CASE WHEN C.last_scan_picking_time != '' THEN to_char(to_timestamp(C.last_scan_picking_time, 'YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') ELSE '' END  AS lastScanPickingTime, ")
                .add(" COALESCE(C.last_scan_picking_user,'') AS lastScanPickingName, ")
                .add(" D.status_so, A.so_id, A.version, D.flg_tutup_so, A.workflow_status, :DASH AS areaName ")
                .add(" , :YES AS flgSoOnline ")
                .add(" FROM ").add(SalesOrder.TABLE_NAME).add(" A ")
                .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" B on B.partner_id = A.partner_id ")
                .add(" INNER JOIN sl_so_online_approved_status C ON A.so_id = C.so_id ")
                .add(" INNER JOIN sl_so_ext D ON A.so_id = D.so_id ")
                .add(" INNER JOIN harga E ON A.so_id = E.so_id ")
                .add(" WHERE D.status_so IN ").add(inputDto.getString("statusSo"))
                .add(" AND A.doc_type_id = :docTypeSoOnline ")
                .add(" AND A.doc_date BETWEEN :startDate AND :endDate ")
                .add(" AND A.ou_id = :ouId ")
                .addIfNotEquals(inputDto.get("tenantLoginId"), GeneralConstants.NULL_REF_VALUE_LONG,  " AND A.tenant_id = "+inputDto.get("tenantLoginId"))
                .addIfNotNull(inputDto.get("docNo")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("docNo"), " A.doc_no ", CriteriaHelperEnum.BOTH))
                .addIfNotNull(inputDto.get("flgDropship")," AND "+ CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("flgDropship"), " D.flg_dropship "))
                .addIfNotEquals(inputDto.get("partnerId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND A.partner_id = "+inputDto.getLong("partnerId"));

        if(!productCodeName.equals(GeneralConstants.EMPTY_VALUE)){
            builder.add(" AND EXISTS ( ")
                    .add(" SELECT 1 ")
                    .add(" FROM ").add(SalesOrderItem.TABLE_NAME).add(" G ")
                    .add(" INNER JOIN ").add(Product.TABLE_NAME).add(" H ON G.product_id = H.product_id")
                    .add(" WHERE G.so_id = A.so_id ")
                    .addIfNotEmpty(productCodeName , new StringBuilder().append(" AND (").append(CriteriaHelper.likeExpressionIgnoreCase(productCodeName, " H.product_code "))
                            .append(" OR ").append(CriteriaHelper.likeExpressionIgnoreCase(productCodeName, " H.product_name ")).append(")").toString())
                    .add(" ) ");
        }

        builder.add(" GROUP BY A.so_id, A.tenant_id, A.doc_no, A.doc_date, A.partner_id, B.partner_name, B.partner_code, ")
                .add(" A.remark, D.flg_picking, D.flg_scan, D.flg_packing, D.flg_shipment, D.flg_dropship, A.ou_id, A.status_doc, ")
                .add(" C.last_print_picking_time, C.last_print_picking_user, C.last_fin_approve_time, C.last_fin_approve_user, ")
                .add(" C.last_auto_approve_time, C.last_auto_approve_user, C.last_ss_approve_time, C.last_ss_approve_user,")
                .add(" C.last_scan_picking_time,  C.last_scan_picking_user, E.total_gross_sell_price, E.total_gross_sell_price_delivered ")
                .add(dynamicQuery.toString());

        builder.add(" ) ");


        if(inputDto.getLong("limit")!=null) builder.add(" LIMIT " + inputDto.getLong("limit"));
        if(inputDto.getLong("offset")!=null) builder.add(" OFFSET " + inputDto.getLong("offset"));

        Query query = salesOrderMlmDao.createNativeQuery(builder.toString());
        query.setParameter("startDate", inputDto.get("startDate"));
        query.setParameter("endDate", inputDto.get("endDate"));
        query.setParameter("ouId", inputDto.get("ouId"));
        query.setParameter("docTypeSoOnline", SalesMlmConstantsForPaloma.DOC_TYPE_SALES_ORDER_ONLINE);
        query.setParameter("DASH", SalesMlmConstantsForPaloma.NO_AREA);
        query.setParameter("YES", GeneralConstants.YES);
        query.setParameter("NO", GeneralConstants.NO);

        result = query.getResultList();

        log.debug(" >> result : " + result );
        log.debug(" >> Assign nilai outputDto << ");
        Dto outputDto = new Dto().putList("soMlmList",
                DtoUtil.createDtoListFromArray(result,
                        "soId", "tenantId", "docNo",
                        "docDate",
                        "partnerId", "partnerName", "partnerCode", "remark", "flgPicking",
                        "flgScan", "flgPacking", "flgShipment", "flgDropship", "ouId", "statusDoc",
                        "waktuCetakSo",
                        "userCetakSo",
                        "totalHargaKatalog", "totalHargaKatalogTerkirim", "totalHargaMember", "totalHargaMemberTerkirim",
                        "nilaiKomisi", "nilaiKomisiTerkirim", "totalPembelian", "totalPembelianTerkirim", "additionalCost", "additionalCostTerkirim",
                        "totalHargaNetto", "deliveredTotalHargaNetto", "nextRoleForSubmit",
                        "autoApprovalTime", "autoApprovalName", "ssApprovalTime", "ssApprovalName", "financeApprovalTime", "financeApprovalName",
                        "lastScanPickingTime", "lastScanPickingName", "statusSo", "soMlmId", "soVersion", "statusTutupSo", "workflowStatus",
                        "dsAreaName" ));
        // log.debug(" >> isi outputDto << ");
        // log.debug(" >> outputDto : " + outputDto );

        return outputDto;
    }

    public String getDescription() {
        return "Approved Sales Order Mlm List For NEW Inquiry SO MLM Sales Support";
    }

}