package com.yem.rf.salescommission;

import com.yem.wm.utils.RptUtil;
import com.yem.wm.utils.YEM;
import com.yem.wm.utils.groupfunc.GroupTopOneFunction;
import kd.bos.algo.DataSet;
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.QCP;
import kd.bos.orm.query.QFilter;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

/**
 * @author ljw
 * @date 2024/7/10 10:51
 * @description SalesOfRoyaltyParts
 */
public class SalesOfRoyaltyParts extends AbstractReportListDataPlugin {
    private static final String algoKey = SalesCommissionReportPlugin.class.getName();
//查询 收款单 group by 以合同为维度，汇总
    //查询后金额对比，对比成功，时间对比，大的赋值
    //
    @Override
    public DataSet query(ReportQueryParam param, Object o) throws Throwable {

        DataSet resDataSet;
        DataSet delivernotice = queryDeliverNotice(param);
        FilterInfo filter = param.getFilter();
        Date start = filter.getDate("yem_ladingdate_start");//提单日期-开始
        Date end = filter.getDate("yem_ladingdate_end");//提单日期-结束
        DataSet storagetrans = queryStorageTrans(param);
        DataSet salesorder = querySalesOrder(param);
//        DataSet gathering = queryGathering(param);
        DataSet queryClaimedAmt = queryClaimedAmt();
//        gathering = gathering.groupBy(new String[]{"salesorderid"}).finish();
        resDataSet = delivernotice.leftJoin(storagetrans)
                .on("delivernoticeid", "storagetransid")
                .select(RptUtil.getDataSetFiledAlias(delivernotice), RptUtil.getDataSetFiledAlias(storagetrans))
                .finish();
        resDataSet = resDataSet.leftJoin(salesorder)
                .on("coreentryid", "salesorderenid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(salesorder))
                .finish();
//        queryGatheringAmt = queryGatheringAmt.select("salesorderid, yem_prodpaydate yem_firstpaydate, yem_endpaydate yem_lastpaydate");
        resDataSet = resDataSet.leftJoin(queryClaimedAmt)
                .on("corebillid", "salesorderid")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(queryClaimedAmt))
                .finish();
        DataSet select = resDataSet.select(String.join(",", RptUtil.getDataSetFiledAlias(resDataSet)) +
                ",yem_actualsalesamount * yem_exchangerate yem_outboundamount" +
                ",CASE WHEN yem_salcontractclaamt >= yem_offeramount  THEN claimstate ELSE '' END yem_payments" +
                ",CASE WHEN yem_sourcebillid = sourcebillid  THEN ladingdatetext ELSE '' END yem_ladingdatetext");
        if (YEM.isNotEmpty(start)||YEM.isNotEmpty(end)){
            return select.where("ladingdatetext is not null");
        }
        return select;
    }

    /**
     * 销售出库
     *
     * @param param 产品分类、客户、合同号、机型、资源号
     * @return
     */
    private DataSet queryDeliverNotice(ReportQueryParam param) {
        FilterInfo filter = param.getFilter();
        String salesorderno = filter.getString("yem_salesorderno");//合同号
        long customer = filter.getLong("yem_bd_customer");//客户
        long producttype = filter.getLong("yem_bd_producttype.id");//产品分类
        QFilter qFilter = getBaseQFilter();
        qFilter.and("yem_es_materialinfo.yem_businesssort", "=", "P");//业务分类
        qFilter.and("billstatus",QCP.equals,"C");
        if (YEM.isNotEmpty(salesorderno)) {
            qFilter.and("yem_contractnum", "like", "%" + salesorderno + "%");//合同号
        }
        if (YEM.isNotEmpty(customer)) {
            qFilter.and("yem_customer.id", "=", customer);//客户
        }
        if (YEM.isNotEmpty(producttype)) {
            qFilter.and("yem_bd_products.id", "=", producttype);//产品分类
        }
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_es_materialinfo.yem_sourceentryid delivernoticeid");
        selectfields.add("yem_es_materialinfo.yem_coreentryid coreentryid");
        selectfields.add("yem_corebillid corebillid");
        selectfields.add("yem_contractnum yem_contractno");//合同号
        selectfields.add("yem_bd_products yem_products");//产品分类
        selectfields.add("yem_customer");//客户
        selectfields.add("yem_es_materialinfo.yem_materiel.id yem_theitemcode");//物料
        selectfields.add("yem_es_materialinfo.yem_qty yem_qty");//数量
        selectfields.add("yem_currency yem_currency");//结算币别
        selectfields.add("yem_exrate yem_exchangerate");//汇率
        selectfields.add("yem_bizdate yem_outbounddate");//出库日期
        selectfields.add("yem_sourcebillid yem_sourcebillid");//来源单据id
        selectfields.add("createtime");

        DataSet rows = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", selectfields), qFilter.toArray())
                .orderBy(new String[]{"createtime DESC"});
        return rows;
    }

    /**
     * 订舱通知单
     *
     * @param param 提单日期
     * @return
     */
    private DataSet queryStorageTrans(ReportQueryParam param) {
        FilterInfo filter = param.getFilter();
        Date start = filter.getDate("yem_ladingdate_start");//提单日期-开始
        Date end = filter.getDate("yem_ladingdate_end");//提单日期-结束
        QFilter qFilter = getBaseQFilter();
        if (YEM.isNotEmpty(start)) {
            qFilter.and("yem_tddate", QCP.large_equals, start);
        }
        if (YEM.isNotEmpty(end)) {
            qFilter.and("yem_tddate", QCP.less_equals, end);
        }
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_es_materialinfo.yem_sourceentryid storagetransid");
        selectfields.add("yem_sourcebillid sourcebillid");
        selectfields.add("TO_CHAR(yem_tddate, 'yyyy-MM-dd') ladingdatetext");//提单日期
        DataSet yemEsStoragetrans = ORM.create().queryDataSet(algoKey, "yem_es_storagetrans", String.join(",", selectfields), qFilter.toArray());
        return yemEsStoragetrans;
    }

    /**
     * 外销合同
     *
     * @param param
     * @return
     */
    private DataSet querySalesOrder(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter();
        qFilter.and("yem_es_materialinfo.yem_businesssort", QCP.equals, "P");

        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_es_materialinfo.id salesorderenid");
        selectfields.add("yem_clientcountry yem_clientcountry");
        selectfields.add("CASE WHEN yem_es_materialinfo.yem_ifgift = '1' THEN yem_es_materialinfo.yem_fobamount ELSE yem_es_materialinfo.yem_afterfrontdisamt END yem_actualsalesamount");
        selectfields.add("yem_offeramount");
        return ORM.create().queryDataSet(algoKey, "yem_es_salesorder", String.join(",", selectfields), qFilter.toArray());
    }

    /**
     * 查询合同各个款项性质的已认领金额合计
     *
     * @return
     */
    private DataSet queryGathering_ClaimedAmt() {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id salesorderid");
        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
                .groupBy(new String[]{"salesorderid"})
                .sum("yem_salcontractclaamt")
                .finish();
        return dataSet;
    }
    private DataSet queryClaimedAmt() {
        DataSet queryGatheringAmt = queryGathering_ClaimedAmt();
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});
        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_receiptdetail.yem_claimdetail.yem_salcontractno.id salesorderids");
        selectfields.add("TO_CHAR(yem_receiptdetail.yem_claimdetail.yem_claimdate, 'yyyy-MM-dd') claimstates");//提单日期
        DataSet dataSet = ORM.create().queryDataSet(algoKey, "yem_gathering", String.join(",", selectfields), qFilter.toArray());
        dataSet=dataSet.groupBy(new String[]{"salesorderids"}).agg(new GroupTopOneFunction(),"claimstates","claimstate").finish();
        DataSet finish = dataSet.leftJoin(queryGatheringAmt)
                .on("salesorderids", "salesorderid")
                .select(RptUtil.getDataSetFiledAlias(dataSet), RptUtil.getDataSetFiledAlias(queryGatheringAmt))
                .finish();
        return finish;
    }
}
