package com.yem.rf.salescommission;

import com.yem.tws.common1.BigDecimalUtils;
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.GroupConcatFunctionByComma;
import com.yem.wm.utils.groupfunc.GroupTopOneFunction;
import kd.bos.algo.DataSet;
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.orm.ORM;
import kd.bos.orm.query.QFilter;
import org.apache.commons.compress.utils.Lists;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

import static com.yem.rf.utils.RFUtils.formatDate;
import static com.yem.rf.utils.RFUtils.getBaseQFilter;

/**
 * @Description: 整机发运明细表
 * @Date: 2024/7/26 10:30
 * @Created: by ZZSLL
 */

public class CompleteShipDetailReportPlugin extends AbstractReportListDataPlugin {

    private static final String algoKey = CompleteShipDetailReportPlugin.class.getName();

    @Override
    public DataSet query(ReportQueryParam param, Object o) throws Throwable {

        DataSet dataSet;
        DataSet salesorder = querySalesOrder(param);
        DataSet deliverNotice = queryDeliverNotice(param);
        dataSet = salesorder.leftJoin(deliverNotice)
                .on("main_detail_id", "main_detail_id_dn_fk")
                .select(RptUtil.getDataSetFiledAlias(salesorder), RptUtil.getDataSetFiledAlias(deliverNotice))
                .finish();
        dataSet = dataSet.where("yem_salesorderno IS NOT NULL AND yem_salesorderno != ''");

        DataSet declaredocx = queryDeclareDocx(param);
        dataSet = dataSet.leftJoin(declaredocx)
                .on("main_detail_id", "main_detail_id_declaredocx_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(declaredocx))
                .finish();

        DataSet storagetrans = queryStorageTrans(param);
        dataSet = dataSet.leftJoin(storagetrans)
                .on("ship_detail_id_dn_fk", "ship_detail_id_st_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(storagetrans))
                .finish();

        FilterInfo filter = param.getFilter();
        Date start = filter.getDate("yem_fshipdate_start");
        Date end = filter.getDate("yem_fshipdate_end");

        StringBuilder sb = new StringBuilder();
        sb.append("1=1");
        if (YEM.isNotEmpty(start)) {
            sb.append(" AND TO_DATE(yem_shipdate, 'yyyy-MM-dd') >= ").append(String.format("TO_DATE('%s','yyyy-MM-dd')", formatDate(start)));
        }
        if (YEM.isNotEmpty(end)) {
            sb.append(" AND TO_DATE(yem_shipdate, 'yyyy-MM-dd') <= ").append(String.format("TO_DATE('%s','yyyy-MM-dd')", formatDate(end)));
        }

        dataSet = dataSet.where(sb.toString());

        return dataSet;
    }

    /**
     * 查询合同整机行
     *
     * @param param
     * @return
     */
    private DataSet querySalesOrder(ReportQueryParam param) {

        FilterInfo filter = param.getFilter();
        String forderno = filter.getString("yem_forderno");
        long fcustom = filter.getLong("yem_fcustom");
        long fresourceno = filter.getLong("yem_fresourceno");
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        qFilter.and("yem_signconfirmstatus", "=", "A");//回签确认状态
        if (YEM.isNotEmpty(forderno)) {
            forderno = forderno.trim();
            forderno = "%" + forderno + "%";
            qFilter.and("billno", "like", forderno);
        }

        if (fcustom > 0) {
            qFilter.and("yem_customer.id", "=", fcustom);
        }

        if (fresourceno > 0) {
            qFilter.and("yem_es_materialinfo.yem_resourcenog.id", "=", fresourceno);
        }

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_es_materialinfo.id main_detail_id");
        selectfields.add("id main_id");
        selectfields.add("billno yem_salesorderno");
        selectfields.add("yem_customer yem_custom");
        selectfields.add("yem_currency yem_currency");
        selectfields.add("yem_es_materialinfo.yem_productmodel.yem_products.id yem_product");//产品机型
        selectfields.add("yem_priceitem yem_priceitem");//价格条款-交货方式
        selectfields.add("yem_es_materialinfo.yem_sapricepriceclause yem_price");//销售单价（价格条款）-单价
        selectfields.add("yem_es_materialinfo.yem_qty yem_qty");//数量-台数
        selectfields.add("yem_es_materialinfo.yem_resourcenog yem_resourceno");//资源号-订货号
        selectfields.add("yem_es_materialinfo.yem_sastandardprice yem_fobpriceqd");//销售FOB标配青岛价-标准结算价格
        selectfields.add("yem_es_materialinfo.yem_singlepartgiftmat yem_singlegiftpartamt");//单台配件赠送金额-折扣、赠配件费用
        selectfields.add("yem_es_materialinfo.yem_safobprice yem_fobprice");//销售FOB单价-价格
        selectfields.add("yem_es_materialinfo.yem_e_interestamt yem_bankamt");//银行利息费-利息

        selectfields.add("yem_es_materialinfo.yem_e_creditamt yem_creditamt");//信保费-核算信保费
        selectfields.add("yem_es_materialinfo.yem_e_marinsupreamt yem_insuranceamt");//保险费
        selectfields.add("yem_es_materialinfo.yem_e_seafreight yem_oceanamt");//海运费
        selectfields.add("yem_es_materialinfo.yem_sapricepriceclause yem_totalamt");//销售单价（价格条款）-总价格
        selectfields.add("yem_es_materialinfo.yem_saamtpriceclause yem_ordertotalamt");//销售金额（价格条款）-合同总金额

        selectfields.add("TO_CHAR(yem_acshipdate, 'yyyy-MM-dd') yem_presetshipdate");//预计发货时间

        selectfields.add("yem_clientcountry yem_clientcountry");//贸易国别
        selectfields.add("yem_reccondition yem_reccondition");//收款条件-付款方式
        selectfields.add("yem_billaskfor yem_billaskfor");//单据要求-单据信息
        selectfields.add("yem_company yem_contactitle");//公司抬头-合同卖方公司

        //calculate field
        selectfields.add("yem_commissionamt");//佣金金额
        selectfields.add("yem_exrate");//汇率
        selectfields.add("yem_ordersumnumber");//订单总台数
        selectfields.add("yem_es_materialinfo.yem_dismountingcost yem_dismountingcost");//拆装费用(人民币)
        selectfields.add("yem_es_materialinfo.yem_e_creditfee yem_e_creditfee");//其他费用（认证费）
        selectfields.add("yem_es_materialinfo.yem_e_bankachargesa yem_e_bankachargesa");//银行费用
        selectfields.add("yem_es_materialinfo.yem_degrate yem_degrate");//退税率%
        //calculate field

        //credit field
        selectfields.add("yem_company.yem_erporg yem_company");//公司
        selectfields.add("yem_issinosurecon yem_issinosurecon");//是否信保合同
        //credit field

        //gathering field
        selectfields.add("yem_offeramount yem_amount");//销售金额（价格条款）-应付款金额
        //gathering field

        selectfields.add("createtime order_createtime");

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.copy().and("yem_es_materialinfo.yem_businesssort", "=", "Z").toArray()).orderBy(new String[]{"order_createtime"});

        DataSet aggVehicleNo = aggSalesOrderVehicleNo(qFilter);
        dataSet = dataSet.leftJoin(aggVehicleNo)
                .on("main_detail_id", "main_detail_id_vno_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(aggVehicleNo))
                .finish();
        DataSet aggConfAmt = aggStandardConfigAmt(qFilter);
        dataSet = dataSet.leftJoin(aggConfAmt)
                .on("main_detail_id", "main_detail_id_confamt_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(aggConfAmt))
                .finish();
        DataSet aggPort = aggBaseDataPort(qFilter);
        dataSet = dataSet.leftJoin(aggPort)
                .on("main_id", "main_id_agg_port_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(aggPort))
                .finish();

        DataSet aggSingleCarAmt = aggSingleCarAmt(qFilter);

        dataSet = dataSet.leftJoin(aggSingleCarAmt)
                .on("main_id", "main_id_agg_single_car_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(aggSingleCarAmt))
                .finish();

        DataSet swiftCode = querySettlementMethodAndSwiftCode(qFilter);
        dataSet = dataSet.leftJoin(swiftCode)
                .on("main_id", "main_id_settlementmethod_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(swiftCode))
                .finish();
        //分别计算信保合同和费信保合同
        DataSet is = dataSet.where("yem_issinosurecon = true");
        DataSet not = dataSet.where("yem_issinosurecon = false");

        DataSet creditline = queryCsrCreditLine();
        //join信保额度申请
        is = is.leftJoin(creditline)
                .on("yem_custom", "crs_customer")
                .on("rapstyle", "crs_payment")
                .on("yem_company", "crs_org")
                .on("swiftcode", "crs_swiftcode")
                .select(RptUtil.getDataSetFiledAlias(is), RptUtil.getDataSetFiledAlias(creditline))
                .finish();
        DataSet creditcontact = queryStorageTrans_CreditContact(param);
        //join后付款最迟到账日
        is = is.leftJoin(creditcontact)
                .on("main_id", "main_id_dn_nocredit_fk")
                .select(RptUtil.getDataSetFiledAlias(is), new String[]{"yem_endpaylastdate"})
                .finish();
        ////join信保额度申请
        not = not.leftJoin(creditline)
                .on("yem_custom", "crs_customer")
                .on("rapstyle", "crs_payment")
                .on("yem_company", "crs_org")
                .select(RptUtil.getDataSetFiledAlias(not), RptUtil.getDataSetFiledAlias(creditline))
                .finish();

        DataSet notcreditcontact = queryDeliverNotice_NotCreditContact(param);
        //join后付款最迟到账日
        not = not.leftJoin(notcreditcontact)
                .on("main_id", "main_id_dn_nocredit_fk")
                .select(RptUtil.getDataSetFiledAlias(not), new String[]{"yem_endpaylastdate"})
                .finish();

        //（（销售单价（价格条款）-海运费-保险费-佣金）*汇率-单车费用本位币合计值-拆装费用（人民币）-其他费用（认证费）-银行费用*汇率/订单总台数-信保费 * 汇率）*（1+退税率/100）
        //"((yem_totalamt - yem_oceanamt - yem_insuranceamt - yem_commissionamt) * yem_exrate - yem_cartfulamountcoin - yem_dismountingcost - yem_e_creditfee - yem_e_bankachargesa * yem_exrate / yem_ordersumnumber - yem_creditamt * yem_exrate) * (1 + yem_degrate / 100) yem_exfactoryamt"

        dataSet = is.union(not);

        dataSet = dataSet.addField("0.0", "yem_exfactoryamt");//出厂价（人民币）

        ArrayList<Long> salesorderIds = Lists.newArrayList();
        //计算出厂价（人民币）
        DynamicObjectCollection collection = ORM.create().toPlainDynamicObjectCollection(dataSet.copy());
        for (DynamicObject dynamicObject : collection) {
            long main_id = dynamicObject.getLong("main_id");
            salesorderIds.add(main_id);
            BigDecimal totalamt = dynamicObject.getBigDecimal("yem_totalamt");//销售单价（价格条款）
            BigDecimal oceanamt = dynamicObject.getBigDecimal("yem_oceanamt");//海运费
            BigDecimal insuranceamt = dynamicObject.getBigDecimal("yem_insuranceamt");//保险费
            BigDecimal commissionamt = dynamicObject.getBigDecimal("yem_commissionamt");//佣金
            BigDecimal exrate = dynamicObject.getBigDecimal("yem_exrate");//汇率
            BigDecimal cartfulamount = dynamicObject.getBigDecimal("yem_cartfulamountcoin");//单车费用本位币合计值
            BigDecimal dismamount = dynamicObject.getBigDecimal("yem_dismountingcost");//拆装费用（人民币）
            BigDecimal eCreditFee = dynamicObject.getBigDecimal("yem_e_creditfee");//其他费用（认证费）
            BigDecimal bankachargesa = dynamicObject.getBigDecimal("yem_e_bankachargesa");//银行费用
            BigDecimal ordersumnumber = dynamicObject.getBigDecimal("yem_ordersumnumber");//订单总台数
            BigDecimal creditamt = dynamicObject.getBigDecimal("yem_creditamt");//信保费
            BigDecimal degrate = dynamicObject.getBigDecimal("yem_degrate");//退税率
//            System.out.println("start__mainid " + main_id + "===================================");
//            System.out.println("销售单价（价格条款）: " + totalamt.stripTrailingZeros().toPlainString());
//            System.out.println("海运费: " + oceanamt.stripTrailingZeros().toPlainString());
//            System.out.println("保险费: " + insuranceamt.stripTrailingZeros().toPlainString());
//            System.out.println("佣金: " + commissionamt.stripTrailingZeros().toPlainString());
//            System.out.println("汇率: " + exrate.stripTrailingZeros().toPlainString());
//            System.out.println("单车费用本位币合计值: " + cartfulamount.stripTrailingZeros().toPlainString());
//            System.out.println("拆装费用（人民币）: " + dismamount.stripTrailingZeros().toPlainString());
//            System.out.println("其他费用（认证费）: " + eCreditFee.stripTrailingZeros().toPlainString());
//            System.out.println("银行费用: " + bankachargesa.stripTrailingZeros().toPlainString());
//            System.out.println("订单总台数: " + ordersumnumber.stripTrailingZeros().toPlainString());
//            System.out.println("信保费: " + creditamt.stripTrailingZeros().toPlainString());
//            System.out.println("退税率: " + degrate.stripTrailingZeros().toPlainString());

            BigDecimal part1 = totalamt.subtract(oceanamt).subtract(insuranceamt).subtract(commissionamt);
            BigDecimal part2 = part1.multiply(exrate);
            BigDecimal part3 = part2.subtract(cartfulamount).subtract(dismamount).subtract(eCreditFee);
            BigDecimal part4 = BigDecimalUtils.div(bankachargesa.multiply(exrate), ordersumnumber, 8);
            BigDecimal part5 = part3.subtract(part4);
            BigDecimal part6 = creditamt.multiply(exrate);
            BigDecimal part7 = part5.subtract(part6);
            BigDecimal result = part7.multiply(BigDecimal.ONE.add(BigDecimalUtils.div(degrate, BigDecimal.valueOf(100), 8)));

//            System.out.println("part1: " + part1.stripTrailingZeros().toPlainString());
//            System.out.println("part2: " + part2.stripTrailingZeros().toPlainString());
//            System.out.println("part3: " + part3.stripTrailingZeros().toPlainString());
//            System.out.println("part4: " + part4.stripTrailingZeros().toPlainString());
//            System.out.println("part5: " + part5.stripTrailingZeros().toPlainString());
//            System.out.println("part6: " + part6.stripTrailingZeros().toPlainString());
//            System.out.println("part7: " + part7.stripTrailingZeros().toPlainString());
//            System.out.println("result: " + result.stripTrailingZeros().toPlainString());
//            System.out.println();
//            System.out.println("end__mainid " + main_id + "===================================");

            result = result.setScale(2, RoundingMode.HALF_UP);
            dynamicObject.set("yem_exfactoryamt", result);
        }

        dataSet = RptUtil.plainDataSet(algoKey, dataSet, collection);

        DataSet payRate = querySalesOrder_PayRate(salesorderIds);

        //join下单发货尾零到账日
        DataSet gathering = queryGathering_ClaimDate(salesorderIds, payRate);
        dataSet = dataSet.leftJoin(gathering)
                .on("main_id", "main_id_gathering_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(gathering))
                .finish();

        DataSet prepayamt = queryGathering_PraPayAmt(salesorderIds, payRate);

        //join下单、发货、尾款应收金额
        dataSet = dataSet.leftJoin(prepayamt)
                .on("main_id", "main_id_g_prepay_amt_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(prepayamt))
                .finish();

        dataSet = dataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(dataSet)) + ", " +
                "yem_orderrefundamt yem_prodamt, yem_shiprefundamt yem_shipamt, yem_periodrefundamt yem_lastpayamt");

        //join后付款实收金额
        DataSet claimedAmt = queryGathering_ClaimedAmt(new QFilter("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id", "in", salesorderIds));
        claimedAmt = claimedAmt.where("yem_moneynature = 'F'")
                .select("main_id_g_claimed_amt_fk, yem_salcontractclaamt yem_lastamt");
        dataSet = dataSet.leftJoin(claimedAmt)
                .on("main_id", "main_id_g_claimed_amt_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(claimedAmt))
                .finish();

        dataSet = RptUtil.zeroNullNums(dataSet);
        //计算欠款金额 应付款金额 - 生产预收金额 - 发货预收金额 - 后付款实收金额
        dataSet = dataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(dataSet)) + "," +
                "yem_amount - yem_prodamt - yem_shipamt - yem_lastamt yem_owedamt");
        //计算逾期天数
        dataSet = dataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(dataSet)) + "," +
                "CASE WHEN" +
                "   yem_endpaydate IS NOT NULL AND " +
                "   yem_endpaydate != '' AND " +
                "   yem_endpaylastdate IS NOT NULL AND " +
                "   yem_endpaylastdate != '' AND" +
                "   TO_DATE(yem_endpaydate, 'yyyy-MM-dd') > TO_DATE(yem_endpaylastdate, 'yyyy-MM-dd')" +
                "THEN" +
                "   DATEDIF(TO_DATE(yem_endpaylastdate, 'yyyy-MM-dd'), TO_DATE(yem_endpaydate, 'yyyy-MM-dd'), D)" +
                "ELSE " +
                "   0.0" +
                "END yem_overdueday");
        return dataSet;
    }

    /**
     * 查询销售出库单
     * 非信保合同：取销售出库的业务日期
     * @param param
     * @return
     */
    private DataSet queryDeliverNotice_NotCreditContact(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_es_materialinfo.yem_corebillid_d main_id_dn_nocredit_fk");
        selectfields.add("yem_bizdate");
        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.orderBy(new String[]{"yem_bizdate DESC"});
        dataSet = dataSet.groupBy(new String[]{"main_id_dn_nocredit_fk"})
                .agg(new GroupTopOneFunction(), "yem_bizdate", "yem_bizdate")
                .finish();
        dataSet = dataSet.select("main_id_dn_nocredit_fk, TO_CHAR(yem_bizdate, 'yyyy-MM-dd') yem_endpaylastdate");
        return dataSet;
    }

    /**
     * 查询订舱通知单
     * 信保合同：合同关联订舱的信保明细页签的应收日期，如果关联多张订舱，取最后一次日期
     * @param param
     * @return
     */
    private DataSet queryStorageTrans_CreditContact(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_creditdetail.yem_salecontractno main_id_dn_nocredit_fk");
        selectfields.add("yem_creditdetail.yem_paydate1 yem_paydate1");
        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_storagetrans", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.orderBy(new String[]{"yem_paydate1 DESC"})
                .groupBy(new String[]{"main_id_dn_nocredit_fk"})
                .agg(new GroupTopOneFunction(), "yem_paydate1", "yem_paydate1")
                .finish();
        dataSet = dataSet.select("main_id_dn_nocredit_fk, TO_CHAR(yem_paydate1, 'yyyy-MM-dd') yem_endpaylastdate");
        return dataSet;
    }

    /**
     * 查询合同预收比例
     * @param salesorderIds
     * @return
     */
    public static DataSet querySalesOrder_PayRate(ArrayList<Long> salesorderIds) {
        QFilter qFilter = new QFilter("id", "in", salesorderIds);
        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("id main_id_payrate_fk");
        selectfields.add("yem_es_salescontrac_s.yem_ispre yem_ispre");//预收标志
        selectfields.add("yem_es_salescontrac_s.yem_raprate yem_raprate");//收汇百分比%
        selectfields.add("yem_es_salescontrac_s.yem_isprepayment yem_isprepayment");//是否生产（下单）预收
        selectfields.add("yem_es_salescontrac_s.yem_isshippayment yem_isshippayment");//是否发货预收

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
        //合计下单预收比例
        DataSet prod = dataSet.where("yem_isprepayment = 'A' AND yem_ispre = true");
        prod = prod.groupBy(new String[]{"main_id_payrate_fk"})
                .sum("yem_raprate")
                .finish();

        prod = prod.addField("0.0", "ship_rate");
        prod = prod.select("main_id_payrate_fk, yem_raprate prod_rate, ship_rate");
        //合计发运预收比例
        DataSet ship = dataSet.where("yem_isshippayment = 'A' AND yem_ispre = true");
        ship = ship.groupBy(new String[]{"main_id_payrate_fk"})
                .sum("yem_raprate")
                .finish();
        ship = ship.addField("0.0", "prod_rate");
        ship = ship.select("main_id_payrate_fk, prod_rate, yem_raprate ship_rate");

        //去重合并 下单 发运比例
        DataSet unioDataSet = prod.union(ship);
        prod.close();
        unioDataSet = RptUtil.zeroNullNums(unioDataSet);
        unioDataSet = unioDataSet.groupBy(new String[]{"main_id_payrate_fk"})
                .sum("prod_rate")
                .sum("ship_rate")
                .finish();
        //查询尾款比例（预收为否）
        DataSet finalDataSet = dataSet.where("yem_ispre = false");
        finalDataSet = finalDataSet
                .groupBy(new String[]{"main_id_payrate_fk"})
                .sum("yem_raprate")
                .finish();
        finalDataSet = finalDataSet.addField("0.0", "prod_rate");
        finalDataSet = finalDataSet.addField("0.0", "ship_rate");

        finalDataSet = finalDataSet.select("main_id_payrate_fk, prod_rate, ship_rate, yem_raprate final_rate");

        unioDataSet = unioDataSet.addField("0.0", "final_rate");

        //合计下单发货尾款比例
        dataSet = unioDataSet.union(finalDataSet)
                .groupBy(new String[]{"main_id_payrate_fk"})
                .sum("prod_rate")
                .sum("ship_rate")
                .sum("final_rate")
                .finish();

        return dataSet;
    }

    /**
     * 合同整机生产信息 整机编号
     *
     * @param qFilter
     * @return
     */
    private DataSet aggSalesOrderVehicleNo(QFilter qFilter) {

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_es_materialinfo.id main_detail_id_vno_fk");
        selectfields.add("yem_es_materialinfo.yem_es_salesorder_z.yem_vehicleno yem_vehicleno");


        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.groupBy(new String[]{"main_detail_id_vno_fk"})
                .agg(new GroupConcatFunctionByComma(), "yem_vehicleno", "yem_vehicleno")
                .finish();
        dataSet = dataSet.select("main_detail_id_vno_fk", "yem_vehicleno");
        return dataSet;
    }

    /**
     * 合同多功能配置标准销售加价（结算币别）合计 - 合同配置价格
     *
     * @param qFilter
     * @return
     */
    private DataSet aggStandardConfigAmt(QFilter qFilter) {

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_es_materialinfo.id main_detail_id_confamt_fk");
        selectfields.add("yem_es_materialinfo.yem_multiconfig.yem_marketraiseprice yem_marketraiseprice");
        selectfields.add("yem_es_materialinfo.yem_multiconfig.yem_configurname yem_addconf");


        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.select("main_detail_id_confamt_fk, yem_marketraiseprice, CASE WHEN yem_addconf IS NULL THEN '' ELSE yem_addconf END yem_addconf");
        dataSet = dataSet
                .groupBy(new String[]{"main_detail_id_confamt_fk"})
                .groupConcat("yem_addconf", "yem_addconf", "、")
                .sum("yem_marketraiseprice")
                .finish();
        dataSet = dataSet.select("main_detail_id_confamt_fk", "yem_marketraiseprice yem_contactconfprice", "yem_addconf");
        return dataSet;
    }


    /**
     * 查询多选基础资料、港口
     *
     * @param qFilter
     * @return
     */
    private DataSet aggBaseDataPort(QFilter qFilter) {

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("id main_id_agg_port_fk");
        selectfields.add("yem_shipports.fbasedataid.name yem_shipports");//起运港
        selectfields.add("yem_destports.fbasedataid.name yem_destports");//目的港

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());

        dataSet = dataSet.groupBy(new String[]{"main_id_agg_port_fk"})
                .agg(new GroupConcatFunctionByComma(), "yem_shipports", "yem_shipports")
                .agg(new GroupConcatFunctionByComma(), "yem_destports", "yem_destports")
                .finish();
        return dataSet;
    }


    /**
     * 汇总单车费用金额本位币
     *
     * @param qFilter
     * @return
     */
    private DataSet aggSingleCarAmt(QFilter qFilter) {
        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("id main_id_agg_single_car_fk");
        selectfields.add("yem_costinfor.yem_cartfulamountcoin yem_cartfulamountcoin");
        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.groupBy(new String[]{"main_id_agg_single_car_fk"})
                .sum("yem_cartfulamountcoin")
                .finish();
        dataSet = dataSet.select("main_id_agg_single_car_fk", "yem_cartfulamountcoin");
        return dataSet;

    }

    /**
     * 查询销售出库
     *
     * @param param
     * @return
     */
    private DataSet queryDeliverNotice(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"C", "B"});
        qFilter.and("yem_es_materialinfo.yem_businesssort", "=", "Z");

        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("billno yem_dnbillno");
        selectfields.add("yem_es_materialinfo.id dn_detail_id");
        selectfields.add("yem_es_materialinfo.yem_sourceentryid ship_detail_id_dn_fk");
        selectfields.add("yem_es_materialinfo.yem_coreentryid main_detail_id_dn_fk");
        selectfields.add("yem_bizdate");//发货日期

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", selectfields), qFilter.toArray());

        return dataSet.select("TO_CHAR(yem_bizdate, 'yyyy-MM-dd') yem_shipdate, dn_detail_id, ship_detail_id_dn_fk, main_detail_id_dn_fk, yem_dnbillno");
    }


    /**
     * 查询出口报关
     *
     * @param param
     * @return
     */
    private DataSet queryDeclareDocx(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("yem_es_materialinfo.yem_coreentryid main_detail_id_declaredocx_fk");
        selectfields.add("TO_CHAR(yem_invoicedate, 'yyyy-MM-dd') yem_invoicedate");//开票日期

        return ORM.create().queryDataSet(algoKey, "yem_es_declaredocx", String.join(",", selectfields), qFilter.toArray());
    }

    /**
     * 查询订舱
     *
     * @param param
     * @return
     */
    private DataSet queryStorageTrans(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});

        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_es_materialinfo.yem_sourceentryid ship_detail_id_st_fk");
        selectfields.add("TO_CHAR(yem_tddate, 'yyyy-MM-dd') yem_ladingdate");//提单日期
        selectfields.add("TO_CHAR(yem_arrivaldate, 'yyyy-MM-dd') yem_arrivaldate");//实际到港日


        return ORM.create().queryDataSet(algoKey, "yem_es_storagetrans", String.join(",", selectfields), qFilter.toArray());
    }

    /**
     * 查询外销合同收款方式、SWIFT_CODE
     * @param qFilter
     * @return
     */
    private DataSet querySettlementMethodAndSwiftCode(QFilter qFilter) {
        List<String> selectfields = new ArrayList<>();
        selectfields.add("id main_id_settlementmethod_fk");
        selectfields.add("billno debug_swiftcode_billno");
        selectfields.add("yem_issinosurecon");
        selectfields.add("yem_es_salescontrac_s.yem_issinosure yem_issinosure");
        selectfields.add("yem_es_salescontrac_s.yem_rapstyle yem_rapstyle");
        selectfields.add("yem_es_salescontrac_s.yem_swiftcode yem_swiftcode");

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.select("main_id_settlementmethod_fk, debug_swiftcode_billno, " +
                "CASE WHEN yem_issinosure = 'A' THEN yem_rapstyle ELSE null END rapstyle, " +
                "CASE WHEN yem_issinosure = 'A' AND yem_issinosurecon = true AND yem_swiftcode != null AND yem_swiftcode != '' THEN yem_swiftcode ELSE null END swiftcode");

        dataSet = dataSet.groupBy(new String[]{"main_id_settlementmethod_fk", "debug_swiftcode_billno"})
                .agg(new GroupTopOneFunction(), "rapstyle", "rapstyle")
                .agg(new GroupTopOneFunction(), "swiftcode", "swiftcode")
                .finish();
        return dataSet;
    }

    /**
     * 客户信保额度申请
     *
     * @return
     */
    private DataSet queryCsrCreditLine() {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});

        List<String> selectfields = new ArrayList<>();
        selectfields.add("billno debug_credit_billno");
        selectfields.add("yem_customer crs_customer");
        selectfields.add("yem_payment crs_payment");
        selectfields.add("org crs_org");
        selectfields.add("yem_swiftcode swiftcode");
        selectfields.add("yem_replylimit yem_approvallimit");//批复金额（美元）
        selectfields.add("yem_balance yem_remaininglimit");//剩余限额

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_im_csrcreditline", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.select("crs_customer, crs_payment, crs_org, yem_approvallimit, yem_remaininglimit, debug_credit_billno, " +
                "CASE WHEN swiftcode != null AND swiftcode != '' THEN swiftcode ELSE null END crs_swiftcode");
        return dataSet;
    }

    /**
     * 查询收款单
     *
     * @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 >= yem_orderrefundamt THEN complete_const " +
                "    WHEN yem_moneynature = 'E' AND yem_salcontractclaamt >= yem_shiprefundamt THEN complete_const " +
                "    WHEN yem_moneynature = 'F' AND yem_salcontractclaamt >= yem_periodrefundamt 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
     */
    public static DataSet queryGathering_PraPayAmt(ArrayList<Long> salesorderIds, DataSet payRate) {
        QFilter qFilter = getBaseQFilter();
        qFilter.and("id", "in", salesorderIds);
        ArrayList<String> selectfields = Lists.newArrayList();
        selectfields.add("id main_id_g_prepay_amt_fk");
        selectfields.add("yem_offeramount");

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());

        dataSet = dataSet.leftJoin(payRate)
                .on("main_id_g_prepay_amt_fk", "main_id_payrate_fk")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(payRate))
                .finish();

        dataSet = dataSet.select("main_id_g_prepay_amt_fk, " +
                "yem_offeramount * prod_rate / 100 yem_orderrefundamt, " +
                "yem_offeramount * ship_rate / 100 yem_shiprefundamt, " +
                "yem_offeramount * final_rate / 100 yem_periodrefundamt");

        dataSet = dataSet.select("main_id_g_prepay_amt_fk, yem_orderrefundamt, yem_shiprefundamt, yem_periodrefundamt");
        return dataSet;
    }

    /**
     * 查询合同各个款项性质的已认领金额合计
     *
     * @param qFilter
     * @return
     */
    public static DataSet queryGathering_ClaimedAmt(QFilter qFilter) {
        List<String> selectfields = new ArrayList<>();

        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id main_id_g_claimed_amt_fk");
        selectfields.add("yem_bizdate");
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_moneynature yem_moneynature");//款项性质
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_claimamt yem_salcontractclaamt");//收款认领金额

        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_gathering", String.join(",", selectfields), qFilter.toArray());
        dataSet = dataSet.orderBy(new String[]{"yem_bizdate DESC"})
                .groupBy(new String[]{"main_id_g_claimed_amt_fk", "yem_moneynature"})
                .sum("yem_salcontractclaamt")
                .agg(new GroupTopOneFunction(), "yem_bizdate", "yem_bizdate")
                .finish();

        return dataSet;
    }
}

