package com.yem.rf.salescommission;

import com.yem.rf.utils.RFUtils;
import com.yem.wm.utils.RptUtil;
import com.yem.wm.utils.YEM;
import com.yem.wm.utils.groupfunc.GroupConcatFunction;
import com.yem.wm.utils.groupfunc.GroupTopOneFunction;
import kd.bos.algo.*;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.isc.util.dt.D;
import kd.bos.orm.ORM;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.fi.fa.report.util.Rpt;

import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.temporal.ChronoUnit;
import java.util.*;

import static com.yem.rf.salescommission.CompleteShipDetailReportPlugin.*;
import static com.yem.rf.utils.RFUtils.getBaseQFilter;
import static kd.fi.er.report.query.TripOrderRpt.algoKey;

/**
 * @author ljw
 * @date 2024/7/29 16:47
 * @description SparepartsPlugin
 */
public class SparepartsPlugin extends AbstractReportListDataPlugin {

    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        DataSet resDataSet;
        DataSet yemEsDeclaredocx = yemEsDeclaredocx();
        DataSet queryDeliverNotice = queryDeliverNotice();
        DataSet yemImEncasements = yemImEncasement();
        DataSet yemContainernumber = yemContainernumber();
        DataSet yemGathering = yemGathering();
        DataSet yemShippingdetails = yemShippingdetails();
        DataSet yemEsDelivernotices = yemEsDelivernotices();
        //销售出库关联外销合同
        resDataSet = queryDeliverNotice.leftJoin(yemEsDelivernotices)
                .on("queryDeliverNoticeid", "yem_corebillids")
                .select(RptUtil.getDataSetFiledAlias(queryDeliverNotice), RptUtil.getDataSetFiledAlias(yemEsDelivernotices))
                .finish();
        //外销合同关联发运明细
        resDataSet = resDataSet.leftJoin(yemShippingdetails)
                .on("queryDeliverNoticeid", "yemShippingdetailsyem_sourcebillid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(yemShippingdetails))
                .finish();
        //发运明细关联订餐通知
        resDataSet = resDataSet.leftJoin(yemContainernumber)
                .on("queryDeliverNoticeid", "yemContainernumberyem_sourcebillid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(yemContainernumber))
                .finish();
        //订舱通知关联装箱单
        resDataSet = resDataSet.leftJoin(yemImEncasements)
                .on("yemContainernumberid", "yemImEncasementyem_sourcebillid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(yemImEncasements))
                .finish();
        //订舱通知关联出口报关
        resDataSet = resDataSet.leftJoin(yemEsDeclaredocx)
                .on("yemContainernumberid", "yemEsDeclaredocxyem_sourcebillid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(yemEsDeclaredocx))
                .finish();
        resDataSet = resDataSet.leftJoin(yemGathering)
                .on("queryDeliverNoticeid", "yem_titleid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(yemGathering))
                .finish();
        resDataSet = resDataSet.distinct();
        resDataSet = RptUtil.zeroNullNums(resDataSet);
        DataSet selected = resDataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(resDataSet)) +
                ",CASE WHEN  (yem_contractamount = yem_salcontractclaamt)  THEN  paymenttime ELSE null END yem_paymenttime" +
//                ",(yem_theamountdue-yem_theamountpaid) yem_theamountowed" +
                ",(case when yem_contractamount = 0 then yem_contractamount else yem_theamountdue /yem_contractamount*yem_interestamt end) yem_interest" +
                ",(case when yem_contractamount = 0 then yem_contractamount else yem_theamountdue /yem_contractamount*yem_creditamt  end) yem_creditpremiums" +
                ",(case when yem_contractamount = 0 then yem_contractamount else yem_theamountdue /yem_contractamount*yem_fundpoolamt  end) yem_discountsaccessor" +
                ",(case when yem_contractamount = 0 then yem_contractamount else yem_theamountdue /yem_contractamount*yem_premiumamt  end) yem_insurance" +
                ",(case when yem_offeramount = 0 then yem_offeramount else yem_theamountdue /yem_offeramount*yem_origcurrepremium  end) yem_actualcreditpremium" +
                ",(case when yem_contractamount = 0 then yem_contractamount else yem_theamountdue /yem_contractamount*(yem_seafreight+yem_landcarrycost+yem_airliftcost+yem_expresscost) end) yem_freight");
        selected = selected.select(String.join(",",RptUtil.getDataSetFiledAlias(selected))+
                ",CASE WHEN  yem_day > 0 AND (yem_paymenttime = null OR yem_duedate < yem_paymenttime) THEN yem_day ELSE '' END yem_daysoverdue");
        FilterInfo filter = reportQueryParam.getFilter();
        String yemLading = filter.getString("yem_exportcontracts");
        String yemContract = filter.getString("yem_billnos");
        DynamicObject yemClient = filter.getDynamicObject("yem_clientname");
        Date yem_shipdatesend = filter.getDate("yem_shipdatesend");
        Date yem_shipdatesdis = filter.getDate("yem_shipdatesdis");


        StringBuilder sb = new StringBuilder();
        sb.append("yem_billno != ''");
        if (YEM.isNotEmpty(yemLading)) {
            sb.append("  AND  yem_exportcontract  like '").append("%").append(yemLading).append("%").append("'");
        }
        if (YEM.isNotEmpty(yemContract)) {
            sb.append("  AND  yem_billno   like '").append("%").append(yemContract).append("%").append("'");
        }
        if (YEM.isNotEmpty(yemClient)) {
            sb.append("  AND  yem_client  = ").append("'" + yemClient.getString("name") + "'");
        }
        if (YEM.isNotEmpty(yem_shipdatesend)) {
            sb.append("  AND  yem_shipdate  >=  '").append("%").append(yem_shipdatesend).append("%").append("'");
        }
        if (YEM.isNotEmpty(yem_shipdatesdis)) {
            sb.append("  AND  yem_shipdate  <=  '").append("%").append(yem_shipdatesdis).append("%").append("'");
        }

        DataSet where = selected.where(sb.toString());

        DataSet distincted = where.distinct();
        List<Map<String, Object>> list = RFUtils.debugDataSet(distincted);
        ArrayList<Long> objects = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            Object o1 = objectMap.get("queryDeliverNoticeid");
            Long l = Long.valueOf(String.valueOf(o1));
            objects.add(l);
        }
        DataSet rows = querySalesOrder_PayRate(objects);
        distincted = distincted.leftJoin(rows).on("queryDeliverNoticeid","main_id_payrate_fk").select(RptUtil.getDataSetFiledAlias(distincted),RptUtil.getDataSetFiledAlias(rows)).finish();
        distincted = RptUtil.zeroNullNums(distincted);

        distincted = distincted.select(String.join(",",RptUtil.getDataSetFiledAlias(distincted))+",yem_theamountdue * prod_rate/100 yem_theamountdueprod_rate" +//下单预收
                ",yem_theamountdue*ship_rate/100 yem_theamountdueship_rate"+//发货预收
                ",yem_theamountdue*final_rate/100 yem_theamountduefinal_rate"+//尾款
                ",yem_theamountdue*prod_rate/100+yem_theamountdue*ship_rate/100 yem_theamountdueprod_ship" +//预收
                ",yem_theamountdue*final_rate/100+yem_theamountdue*prod_rate/100 yem_theamountduefinal_prod" +//下单预收+尾款
                ",yem_theamountdue*ship_rate/100+yem_theamountdue*prod_rate/100 yem_theamountdueship_prod" +//发货预收+尾款
                ",yem_theamountdue*final_rate/100+yem_theamountdue*ship_rate/100+yem_theamountdue*prod_rate/100 yem_theamountduefinal_ship_prod");//预收+尾款


        DataSet claimDate = queryGathering_ClaimDate(objects, rows);

        distincted = distincted.leftJoin(claimDate)
                .on("queryDeliverNoticeid", "main_id_gathering_fk")
                .select(RptUtil.getDataSetFiledAlias(distincted), RptUtil.getDataSetFiledAlias(claimDate))
                .finish();

        distincted = distincted.select(String.join(",", RptUtil.getDataSetFiledAlias(distincted)) +
                ",CASE WHEN yem_prodpaydate IS NOT NULL AND yem_prodpaydate != '' AND (yem_shippaydate IS NULL OR yem_shippaydate = '') AND (yem_endpaydate IS NULL OR yem_endpaydate = '') THEN " +//
                "   yem_theamountdueprod_rate" +
                //下单预收
                " WHEN (yem_prodpaydate IS NULL OR yem_prodpaydate = '') AND yem_shippaydate IS NOT NULL AND yem_shippaydate != '' AND (yem_endpaydate IS NULL OR yem_endpaydate ='' )THEN" +//
                "   yem_theamountdueship_rate" +
                //发货预收
                " WHEN (yem_prodpaydate IS NULL OR yem_prodpaydate = '') AND (yem_shippaydate IS NULL OR yem_shippaydate = '' )AND yem_endpaydate IS NOT NULL AND yem_endpaydate !='' THEN" +//
                "   yem_theamountduefinal_rate" +
                //尾款

                " WHEN yem_prodpaydate IS NOT NULL AND yem_prodpaydate != '' AND yem_shippaydate IS NOT NULL AND yem_shippaydate != '' AND( yem_endpaydate IS NULL OR yem_endpaydate ='') THEN" +//
                "   yem_theamountdueprod_ship" +
                //预收
                " WHEN yem_prodpaydate IS NOT NULL AND yem_prodpaydate != '' AND (yem_shippaydate IS NULL OR yem_shippaydate = '') AND yem_endpaydate IS NOT NULL AND yem_endpaydate !='' THEN" +//
                "   yem_theamountduefinal_prod" +
                //下单预收+尾款
                " WHEN (yem_prodpaydate IS NULL OR yem_prodpaydate = '') AND yem_shippaydate IS NOT NULL AND yem_shippaydate != '' AND yem_endpaydate IS NOT NULL AND yem_endpaydate !='' THEN" +//
                "   yem_theamountdueship_prod" +
                //发货预收+尾款
                " WHEN  yem_endpaydate IS NOT NULL AND yem_endpaydate !='' AND  yem_prodpaydate IS NOT NULL AND yem_prodpaydate !='' AND yem_shippaydate IS NOT NULL AND yem_shippaydate != ''  THEN" +//
                "   yem_theamountduefinal_ship_prod"+
                //预收+尾款
                " ELSE " +
                "   0.0" +
                " END yem_theamountpaid");
        distincted = distincted.select(String.join(",", RptUtil.getDataSetFiledAlias(distincted)) +
                ",yem_theamountdue-yem_theamountpaid yem_theamountowed");
        return distincted.distinct();
    }

    //(CASE WHEN true then 0 else (case where true then 1 else 0 end)end)
    /**
     * 查询收款单
     *
     * @param salesorderIds
     * @param payRate
     * @return
     */
    public static DataSet queryGathering_ClaimDate(ArrayList<Long> salesorderIds, DataSet payRate) {
        DataSet dataSet;
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        qFilter.and("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id", "in", salesorderIds);

        //查询下单发货尾款应收金额
        DataSet prePayAmt = queryGathering_PraPayAmt(salesorderIds, payRate);
        //查询已认领金额合计下单发货尾款
        DataSet claimedAmt = queryGathering_ClaimedAmt(qFilter);

        dataSet = prePayAmt.leftJoin(claimedAmt)
                .on("main_id_g_prepay_amt_fk", "main_id_g_claimed_amt_fk")
                .select(RptUtil.getDataSetFiledAlias(prePayAmt), RptUtil.getDataSetFiledAlias(claimedAmt))
                .finish();
        dataSet = dataSet.addField("1.0", "complete_const");//已认领完成标识
        dataSet = dataSet.addField("0.0", "uncomplete_const");//未认领完成标识
        //计算，标记各个款项性质是否认领完成
        dataSet = dataSet.select(" " +
                "CASE " +
                "    WHEN yem_moneynature = 'D' AND yem_salcontractclaamt >= 0 THEN complete_const " +
                "    WHEN yem_moneynature = 'E' AND yem_salcontractclaamt >= 0 THEN complete_const " +
                "    WHEN yem_moneynature = 'F' AND yem_salcontractclaamt >= 0 THEN complete_const " +
                "" +
                "    ELSE uncomplete_const " +
                "END claim_status, " +
                String.join(",", RptUtil.getDataSetFiledAlias(dataSet)));
        //过滤，只保留认领完成的
        dataSet = dataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(dataSet)))
                .where("claim_status = 1.0")
                .orderBy(new String[]{"yem_bizdate DESC"});
        //取各个款项性质认领完成的收款业务日期未付款到账日
        dataSet = dataSet.select("main_id_g_prepay_amt_fk, " +
                "CASE WHEN yem_moneynature = 'D' THEN yem_bizdate END yem_prodpaydate, " +
                "CASE WHEN yem_moneynature = 'E' THEN yem_bizdate END yem_shippaydate, " +
                "CASE WHEN yem_moneynature = 'F' THEN yem_bizdate END yem_endpaydate");
        //日期转文本
        dataSet = dataSet.select("main_id_g_prepay_amt_fk, " +
                "TO_CHAR(yem_prodpaydate, 'yyyy-MM-dd') yem_prodpaydate, " +
                "TO_CHAR(yem_shippaydate, 'yyyy-MM-dd') yem_shippaydate, " +
                "TO_CHAR(yem_endpaydate, 'yyyy-MM-dd') yem_endpaydate");
        //根据合同合并至同一行
        dataSet = dataSet.groupBy(new String[]{"main_id_g_prepay_amt_fk"})
                .agg(new GroupConcatFunction(), "yem_prodpaydate", "yem_prodpaydate")
                .agg(new GroupConcatFunction(), "yem_shippaydate", "yem_shippaydate")
                .agg(new GroupConcatFunction(), "yem_endpaydate", "yem_endpaydate")
                .finish();

        dataSet = dataSet.select("main_id_g_prepay_amt_fk main_id_gathering_fk, yem_prodpaydate, yem_shippaydate, yem_endpaydate");
        return dataSet;
    }

        /**
         * 外销合同
         *
         * @return
         */
    private DataSet queryDeliverNotice() {
        QFilter qFilter = getBaseQFilter();
        qFilter.and("yem_es_materialinfo.yem_businesssort",QCP.equals,"P");
        List<String> selectfields = new ArrayList<>();
        selectfields.add("id queryDeliverNoticeid");
        selectfields.add("yem_operator.name yem_salesman");
        selectfields.add("billno yem_exportcontract");
        selectfields.add("yem_billtype.name yem_typeofcontract");
        selectfields.add("yem_interestamt yem_interestamt");
        selectfields.add("yem_creditamt yem_creditamt");
        selectfields.add("yem_fundpoolamt yem_fundpoolamt");
        selectfields.add("yem_premiumamt yem_premiumamt");
        selectfields.add("yem_seafreight yem_seafreight");
        selectfields.add("yem_landcarrycost yem_landcarrycost");
        selectfields.add("yem_airliftcost yem_airliftcost");
        selectfields.add("yem_expresscost yem_expresscost");
        selectfields.add("yem_customer.name yem_client");
        selectfields.add("yem_offeramount yem_contractamount");
        selectfields.add("yem_shipports.fbasedataid.name yem_portofdeparture");
        selectfields.add("yem_destports.fbasedataid.name yem_portdestination");
        selectfields.add("yem_clientcountry.name yem_countryofdestinatio");
        selectfields.add("TO_CHAR(yem_acshipdate, 'yyyy-MM-dd') yem_estimatedshipment");
        selectfields.add("yem_reccondition.name yem_paymentmethods");
//        selectfields.add("yem_es_materialinfo.yem_businesssort yem_customsdeclaration1");
        return ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
    }

    /**
     * 订舱通知单
     *
     * @return
     */
    private DataSet yemContainernumber() {
        QFilter qFilter = getBaseQFilter(new String[]{"B","C"});
        List<String> selectfields = new ArrayList<>();
        selectfields.add("id yemContainernumberid");
        selectfields.add("yem_offeramount yem_offeramount");
        selectfields.add("yem_origcurrepremium yem_origcurrepremium");
        selectfields.add("yem_corebillid yemContainernumberyem_sourcebillid");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_es_storagetrans", String.join(",", selectfields), qFilter.toArray());
        return yemShippingdetails;
    }

    /**
     * 装箱单
     *
     * @return
     */
    private DataSet yemImEncasement() {
        QFilter qFilter = getBaseQFilter();
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_completionstatuspj yem_productstatus");//配件完成状态
        selectfields.add("yem_sourcebillid yemImEncasementyem_sourcebillid");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_im_encasement", String.join(",", selectfields), qFilter.toArray());
        return yemShippingdetails;
    }

    /**
     * 出口报关单
     *
     * @return
     */
    private DataSet yemEsDeclaredocx() {
        QFilter qFilter = getBaseQFilter();
        List<String> selectfields = new ArrayList<>();
        selectfields.add("TO_CHAR(yem_invoicedate, 'yyyy-MM-dd') yem_invoicingdate");//开票日期
        selectfields.add("yem_sourcebillid yemEsDeclaredocxyem_sourcebillid");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_es_declaredocx", String.join(",", selectfields), qFilter.toArray());
        return yemShippingdetails;
    }

    /**
     * 收款单
     *
     * @return 收款单的合同金额
     * 收款单关联收款单
     */
    private DataSet yemGathering() {
        QFilter qFilter = getBaseQFilter();
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id yem_titleid");//收款单的合同号id
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_salcontractclaamt yem_salcontractclaamt");//收款单的合同金额
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_gathering", String.join(",", selectfields), qFilter.toArray());
        yemShippingdetails = yemShippingdetails.groupBy(new String[]{"yem_titleid"})
                .sum("yem_salcontractclaamt")
                .finish();
        List<String> selectfield = new ArrayList<>();
        selectfield.add("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id yem_salcontractnoid");//收款单的合同号id
        selectfield.add("TO_CHAR(yem_bizdate, 'yyyy-MM-dd') paymenttime");//收款单的合同金额 to_char(sysdate,'yyyy')
        DataSet yemShippingdetail = ORM.create().queryDataSet(algoKey, "yem_gathering", String.join(",", selectfield), qFilter.toArray());
        yemShippingdetails =yemShippingdetails.leftJoin(yemShippingdetail)
                .on("yem_titleid","yem_salcontractnoid")
                .select(RptUtil.getDataSetFiledAlias(yemShippingdetails),RptUtil.getDataSetFiledAlias(yemShippingdetail))
                .finish();
        return yemShippingdetails.distinct();
    }

    /**
     * 发运明细单
     *
     * @return
     */
    private DataSet yemShippingdetails() {
        QFilter qFilter = getBaseQFilter();
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_iswithaccessories yem_boardaccessor");
        selectfields.add("yem_transportstyle.name yem_shippingmethod");
        selectfields.add("yem_sourcebillid yemShippingdetailsyem_sourcebillid");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_shippingdetails", String.join(",", selectfields), qFilter.toArray());
        yemShippingdetails = yemShippingdetails.select(String.join(",",RptUtil.getDataSetFiledAlias(yemShippingdetails))+
                ",CASE WHEN yem_boardaccessor = 'A' THEN '是' ELSE '否' END yem_boardaccessory");
        yemShippingdetails = yemShippingdetails
                .groupBy(new String[]{"yemShippingdetailsyem_sourcebillid"})
                .groupConcat("yem_boardaccessory", "yem_boardaccessory", "；")
                .groupConcat("yem_shippingmethod", "yem_shippingmethod", "；")
                .finish();
        return yemShippingdetails;
    }

    /**
     * 销售出库单
     *
     * @return
     */
    private DataSet yemEsDelivernotice() {
        QFilter qFilter = new QFilter("yem_es_materialinfo.yem_businesssort", QCP.equals, "P");
        List<String> selectfields = new ArrayList<>();
        selectfields.add("id yemEsDelivernoticeid");
        selectfields.add("billno yem_billno");
        selectfields.add("billstatus yem_billstatus");
        selectfields.add("TO_CHAR(auditdate, 'yyyy-MM-dd') yem_shipdate");
        selectfields.add("yem_duedate yem_duedate");//yem_daysoverdue
        selectfields.add("yem_priceclause.name yem_deliverymethod");
        selectfields.add("yem_totalallamount yem_theamountdue");//价税合计
        selectfields.add("yem_currency.name yem_fullpaymenttime");
        selectfields.add("yem_companyname yem_contractsellerfirm");
        selectfields.add("yem_bd_products.name yem_fittingtype");
        selectfields.add("yem_sourcebillid yemEsDelivernoticeyem_sourcebillid");
        selectfields.add("yem_corebillid yem_corebillids");
        selectfields.add("'' yem_day");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", selectfields), qFilter.toArray());
        DynamicObjectCollection plainDynamicObjectCollection = ORM.create().toPlainDynamicObjectCollection(yemShippingdetails);
        if (!plainDynamicObjectCollection.isEmpty()){
            for (DynamicObject dynamicObject : plainDynamicObjectCollection) {
                Date yem_duedate = dynamicObject.getDate("yem_duedate");
                Date data = new Date();
                if (yem_duedate!=null){
                    LocalDate date1 = convertDateToLocalDate(yem_duedate);
                    LocalDate date2 = convertDateToLocalDate(data);
                    long Chrono = ChronoUnit.DAYS.between(date1,date2 );
                    dynamicObject.set("yem_day",Chrono);
                }
            }
        }
        yemShippingdetails= RptUtil.plainDataSet(algoKey, yemShippingdetails,plainDynamicObjectCollection );
        return yemShippingdetails;
    }

    public static LocalDate convertDateToLocalDate(Date date) {
        // 将 Date 转换为 Instant
        Instant instant = date.toInstant();

        // 使用系统默认时区（或者你可以指定一个特定的时区）将 Instant 转换为 LocalDate
        // 注意：LocalDate 不包含时区信息，所以这里转换时使用的时区主要是为了确定日期
        ZoneId zoneId = ZoneId.systemDefault();
        return instant.atZone(zoneId).toLocalDate();
    }

    /**
     * 该销售出库单数量的合计值
     *
     * @return
     */
    private DataSet yemEsDelivernotices() {
        QFilter qFilter = new QFilter("yem_es_materialinfo.yem_businesssort", QCP.equals, "P");
        List<String> selectfields = new ArrayList<>();
        selectfields.add("id yemEsDelivernoticesid");
        selectfields.add("yem_es_materialinfo.yem_qty yem_qty");
        DataSet yemShippingdetails = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", selectfields), qFilter.toArray());
        yemShippingdetails = yemShippingdetails.groupBy(new String[]{"yemEsDelivernoticesid"})
                .sum("yem_qty")
                .finish();
        DataSet yemEsDelivernotice = yemEsDelivernotice();
        yemShippingdetails = yemShippingdetails.leftJoin(yemEsDelivernotice)
                .on("yemEsDelivernoticesid", "yemEsDelivernoticeid")
                .select(RptUtil.getDataSetFiledAlias(yemShippingdetails), RptUtil.getDataSetFiledAlias(yemEsDelivernotice))
                .finish();
        return yemShippingdetails;
    }

}
