GetApprovedSoMlmListForNewSalesSupportByArea

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.SalesOrderMlm;
import org.jleaf.erp.sls.entity.SalesOrderMlmItem;
import org.jleaf.erp.sls.entity.SoApprovedStatus;
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;

/**
 *
 * @author Julius, 24 Jan 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, totalHargaNetto, nilaiKomisi, totalPembelian, additionalCost, nextRoleForSubmit,autoApprovalTime,autoApprovalName,ssApprovalTime,ssApprovalName,financeApprovalTime,financeApprovalName,lastScanPickingTime,lastScanPickingName, flgSoOnline)", type = List.class) })

public class GetApprovedSoMlmListForNewSalesSupportByArea extends AbstractBusinessFunction implements
        BusinessFunction {

    @SuppressWarnings("unused")
    private static final Logger log = LoggerFactory.getLogger(GetApprovedSoMlmListForNewSalesSupportByArea.class);

    @Autowired
    SalesOrderMlmDao salesOrderMlmDao;

    public String getDescription() {
        return "sales order mlm approved List";
    }

    @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 I.area_name ");
        }

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

        QueryBuilder builder = new QueryBuilder();
        builder.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, ")
                .add(" 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, I.area_name ")
                .add(" , :YES 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(" H ON A.partner_id = H.partner_id ")
                .add(" INNER JOIN ").add(DsArea.TABLE_NAME).add(" I ON H.ds_area_id = I.ds_area_id ")
                .add(" WHERE A.status_so IN ").add(inputDto.getString("statusSo"))
                .add(" AND f_authorize_user_role_policy_ds_area(:tenantLoginId,:userLoginId,:roleLoginId,H.ds_area_id) = 1 ")
                .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 = :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, I.area_name ")
                .add(dynamicQuery.toString());
                if(inputDto.getLong("limit")!=null) builder.add(" LIMIT " + inputDto.getLong("limit"));
                if(inputDto.getLong("offset")!=null) builder.add(" OFFSET " + inputDto.getLong("offset"));

                //log.debug("querynya:::"+builder.toString());
                //log.info("querynya:::"+builder.toString());

        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("tenantLoginId",inputDto.get("tenantLoginId"));
        query.setParameter("userLoginId",inputDto.get("userLoginId"));
        query.setParameter("roleLoginId",inputDto.get("roleLoginId"));
        query.setParameter("NO", GeneralConstants.NO);

        result = query.getResultList();

        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"));


        return outputDto;
    }


}