package com.yem.rf.salescommission;

import com.yem.wm.utils.RequestCmmp;
import com.yem.wm.utils.RptUtil;
import com.yem.wm.utils.YEM;
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.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.QueryServiceHelper;

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

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

/**
 * @Description: TODO
 * @Date: 2024/7/9 9:44
 * @Created: by ZZSLL
 */

public class ITCOrderDetailsReportPlugin extends AbstractReportListDataPlugin {

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

    @Override
    public DataSet query(ReportQueryParam param, Object o) throws Throwable {
        DataSet resDataSet;
        DataSet placeexamine = queryPlaceExamine(param);//已提交下单审批
        DataSet salesorder = querySalesOrder(param);//合同
        DataSet salesorderplanentry = querySalesOrderPlanEntry(param);//合同跟单任务明细
        DataSet storagetrans = queryStorageTrans(param);//订舱
        DataSet tracktaskconsole = queryTrackTaskConsole(param);//跟单任务控制台
        DataSet tracktaskconsoleprocentry = queryTrackTaskConsoleProcEntry(param);//跟单任务控制台 备料分录
        DataSet delivernotice = queryDeliverNotice(param);
        DataSet delivernotice_status = queryDeliverNoticeStatus(param);


        resDataSet = placeexamine.leftJoin(salesorder)
                .on("source_order_id_placeexamine_fk", "order_title_id")
                .select(RptUtil.getDataSetFiledAlias(placeexamine), RptUtil.getDataSetFiledAlias(salesorder))
                .finish();
        resDataSet = resDataSet.leftJoin(salesorderplanentry)
                .on("order_detail_id", "order_detail_id_plan_fk")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(salesorderplanentry))
                .finish();
        resDataSet = resDataSet.leftJoin(storagetrans)
                .on("order_detail_id", "storage_detail_id_order_detail_fk")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(storagetrans))
                .finish();
        resDataSet = resDataSet.leftJoin(tracktaskconsole)
                .on("order_detail_id", "plan_detail_source_id")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(tracktaskconsole))
                .finish();
        resDataSet = resDataSet.leftJoin(tracktaskconsoleprocentry)
                .on("plan_detail_id", "plan_proc_detail_fk")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(tracktaskconsoleprocentry))
                .finish();
        resDataSet = resDataSet.leftJoin(delivernotice)
                .on("order_detail_id", "core_order_id_delivernotice_fk")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(delivernotice))
                .finish();
        resDataSet = resDataSet.leftJoin(delivernotice_status)
                .on("order_detail_id", "core_order_id_dn_status_fk")
                .select(RptUtil.getDataSetFiledAlias(resDataSet), RptUtil.getDataSetFiledAlias(delivernotice_status))
                .finish();

        FilterInfo filter = param.getFilter();

        String fconstractno = filter.getString("yem_fconstractno");
        long foperator = filter.getLong("yem_foperator");//销售员
        long fclientcountry = filter.getLong("yem_fclientcountry");//国家
        long fcustomer = filter.getLong("yem_fcustomer");//客户
        Date forderdateStart = filter.getDate("yem_forderdate_start");
        Date forderdateEnd = filter.getDate("yem_forderdate_end");

        StringBuilder sb = new StringBuilder();
        sb.append("yem_businesssort = 'Z' AND dn_status != 'C'");
        if (YEM.isNotEmpty(fconstractno)) {
            sb.append(" AND yem_constractno like '").append("%").append(fconstractno).append("%").append("'");
        }
        if (YEM.isNotEmpty(foperator)) {
            sb.append(" AND yem_operator = ").append(foperator);
        }
        if (YEM.isNotEmpty(fclientcountry)) {
            sb.append(" AND yem_clientcountry = ").append(fclientcountry);
        }
        if (YEM.isNotEmpty(fcustomer)) {
            sb.append(" AND yem_customer = ").append(fcustomer);
        }
        if (YEM.isNotEmpty(forderdateStart)) {
            sb.append(" AND forderdate >= ").append(String.format("TO_DATE('%s','yyyy-MM-dd')", formatDate(forderdateStart)));
        }
        if (YEM.isNotEmpty(forderdateEnd)) {
            sb.append(" AND forderdate <= ").append(String.format("TO_DATE('%s','yyyy-MM-dd')", formatDate(forderdateEnd)));
        }
        resDataSet = resDataSet.addField("0.0", "yem_inventoryqty");

        DataSet where = resDataSet.where(sb.toString());
        DataSet select = where.select(String.join(",", RptUtil.getDataSetFiledAlias(where)) +
                ", TO_CHAR(forderdate, 'yyyy-MM-dd') yem_orderdate");
        resDataSet = fillWithCMMPQty(select);

        return resDataSet.distinct();
    }

    /**
     * 查询 合同单据头、合同商品明细、整机生产信息
     * @param param
     * @return
     */
    private DataSet querySalesOrder(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter();


        List<String> selectfields = new ArrayList<>();

        selectfields.add("id order_title_id");
        selectfields.add("yem_operator yem_operator");//销售员
        selectfields.add("yem_clientcountry yem_clientcountry");//贸易国别
        selectfields.add("yem_customer yem_customer");//客户
        selectfields.add("billno yem_constractno");
        selectfields.add("yem_reccondition yem_reccondition");//收款条件
        selectfields.add("yem_es_materialinfo.id order_detail_id");
        selectfields.add("yem_es_materialinfo.yem_qty yem_reqqty");//明细.数量
        selectfields.add("yem_es_materialinfo.yem_es_salesorder_z.id order_detail_comp_id");
        selectfields.add("TO_CHAR(yem_es_materialinfo.yem_es_salesorder_z.yem_factputstordate, 'yyyy-MM-dd') yem_accinventorydate");//明细.整机生产信息.实际入库时间
        selectfields.add("yem_es_materialinfo.yem_productmodel yem_productmodel");//明细.基础机型
        selectfields.add("yem_es_materialinfo.yem_resourcenog.id yem_resourcenog");//明细.资源号
        selectfields.add("yem_es_materialinfo.yem_resourcenog.number yem_resourcenumber");//明细.资源号
        selectfields.add("yem_es_materialinfo.yem_orderallocation yem_tconfiguration");//明细.订单配置要求
        selectfields.add("yem_es_materialinfo.yem_es_salesorder_z.yem_vehicleno yem_vehicleno");//明细.整机生产信息.整机编号
        selectfields.add("yem_es_materialinfo.yem_businesssort yem_businesssort");
        selectfields.add("createtime");
        selectfields.add("yem_bd_products");//产品分类

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

    /**
     * 查询合同 跟单任务分录、子分录
     * @param param
     * @return
     */
    private DataSet querySalesOrderPlanEntry(ReportQueryParam param) {

        QFilter qFilter = getBaseQFilter();

        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_shippingplan_entry.id order_plan_id");//跟单任务明细ID
        selectfields.add("yem_shippingplan_entry.yem_plan_date forderdate");//跟单任务.下单日期
        selectfields.add("yem_shippingplan_entry.yem_plandetail_entry.id order_plan_detail_id");//跟单任务明细子分录ID
        selectfields.add("yem_shippingplan_entry.yem_plandetail_entry.yem_srcentryid order_detail_id_plan_fk");

        return ORM.create().queryDataSet(algoKey, "yem_es_salesorder", 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("TO_CHAR(yem_encasemedates, 'yyyy-MM-dd') yem_encasemedates");//装箱时间
        selectfields.add("yem_es_materialinfo.yem_coreentryid storage_detail_id_order_detail_fk");
//        selectfields.add("yem_es_materialinfo.yem_qty yem_qty");//数量
        selectfields.add("yem_iswithaccessories yem_iswithaccessories");//是否随车配件
        selectfields.add("yem_boxvolume1 yem_boxvolume");//箱型箱量X
        selectfields.add("yem_contremark yem_sremark");//合同备注

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

    /**
     * 查询 跟单任务控制台 商品明细
     * @param param
     * @return
     */
    private DataSet queryTrackTaskConsole(ReportQueryParam param) {

        List<String> selectfields = new ArrayList<>();

        selectfields.add("id plan_id");
        selectfields.add("yem_sourcebillid source_order_id");
        selectfields.add("TO_CHAR(yem_acshipdate, 'yyyy-MM-dd') yem_reqinventorydate");//预计需求入库时间
        selectfields.add("yem_es_materialinfo.id plan_detail_id");
        selectfields.add("yem_es_materialinfo.yem_sourceentryid plan_detail_source_id");
//        selectfields.add("yem_es_materialinfo.yem_ifreformg yem_ifreform");//是否改制
        selectfields.add("yem_es_materialinfo.yem_remarks yem_remark");//备注

        return ORM.create().queryDataSet(algoKey, "yem_tracktaskconsole", String.join(",", selectfields), null);
    }

    /**
     * 查询跟单任务控制台 备料（生产、采购）信息
     * @param param
     * @return
     */
    private DataSet queryTrackTaskConsoleProcEntry(ReportQueryParam param) {

        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_es_salesorder_r.yem_entrtyid plan_proc_detail_fk");
        selectfields.add("yem_es_salesorder_r.yem_stockway yem_ifreform");
        selectfields.add("TO_CHAR(yem_es_salesorder_r.yem_yieldetacdate, 'yyyy-MM-dd') yem_prodfeedbackdate");//预计完成时间
        selectfields.add("TO_CHAR(yem_es_salesorder_r.yem_yieldfactacdate, 'yyyy-MM-dd') yem_plancompdate");//实际完成时间
        return ORM.create().queryDataSet(algoKey, "yem_tracktaskconsole", String.join(",", selectfields), null);
    }

    /**
     * 查询已提交下单审批
     * @param param
     * @return
     */
    private DataSet queryPlaceExamine(ReportQueryParam param) {
        QFilter qFilter = getBaseQFilter(new String[]{"B", "C"});

        List<String> selectfields = new ArrayList<>();
        selectfields.add("yem_sourcebillid source_order_id_placeexamine_fk");

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

    /**
     * 查询数量，查询已提交订舱通知、审核的销售出库
     * @param param
     * @return
     */
    private DataSet queryDeliverNotice(ReportQueryParam param) {
        DataSet dataSet;

        QFilter stfilter = getBaseQFilter(new String[]{"B"});
        List<String> stselectfields = new ArrayList<>();
        stselectfields.add("yem_es_materialinfo.yem_sourceentryid st_sourceentryid");//发运明细分录id
        stselectfields.add("yem_es_materialinfo.yem_qty yem_qty");//数量
        stselectfields.add("billstatus storagetrans_status");

        DataSet st = ORM.create().queryDataSet(algoKey, "yem_es_storagetrans", String.join(",", stselectfields), stfilter.toArray());

        QFilter dnfilter = getBaseQFilter(new String[]{"C"});

        List<String> dnselectfields = new ArrayList<>();
        dnselectfields.add("yem_es_materialinfo.yem_coreentryid core_order_id_delivernotice_fk");
        dnselectfields.add("yem_es_materialinfo.yem_sourceentryid dn_sourceentryid");//发运明细分录id
        dnselectfields.add("billstatus delivernotice_status");

        DataSet dn = ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", dnselectfields), dnfilter.toArray());

        dataSet = st.leftJoin(dn)
                .on("st_sourceentryid", "dn_sourceentryid")
                .select(RptUtil.getDataSetFiledAlias(st), RptUtil.getDataSetFiledAlias(dn))
                .finish();

        return dataSet.select("core_order_id_delivernotice_fk", "yem_qty", "delivernotice_status", "storagetrans_status");
    }

    /**
     * 查询已审核销售出库
     * @param param
     * @return
     */
    private DataSet queryDeliverNoticeStatus(ReportQueryParam param) {
        QFilter dnfilter = getBaseQFilter(new String[]{"C"});

        List<String> dnselectfields = new ArrayList<>();
        dnselectfields.add("yem_es_materialinfo.yem_coreentryid core_order_id_dn_status_fk");
        dnselectfields.add("billstatus dn_status");

        return ORM.create().queryDataSet(algoKey, "yem_es_delivernotice", String.join(",", dnselectfields), dnfilter.toArray());
    }

    /**
     * 整机编号查询CMMP获取可用数量
     * @param where
     * @return
     */
    private DataSet fillWithCMMPQty(DataSet where) {
        DynamicObjectCollection collection = ORM.create().toPlainDynamicObjectCollection(where);
        Map<Long, List<String>> map = new HashMap<>();
        for (DynamicObject dynamicObject : collection) {
            String vehicleno = dynamicObject.getString("yem_vehicleno");
            long products = dynamicObject.getLong("yem_bd_products");
            if (YEM.isNotEmpty(vehicleno) && YEM.isNotEmpty(products)) {
                List<String> list;
                if (map.containsKey(products)) {
                    list = map.get(products);
                } else {
                    list = new ArrayList<>();
                }
                list.add(vehicleno);
                map.put(products, list);
            }
        }
        Map<Long, List<Map>> cmmpQtyMap = new HashMap<>();
        for (Map.Entry<Long, List<String>> entry : map.entrySet()) {
            Long key = entry.getKey();
            List<String> list = entry.getValue();

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT sum(canuse_num) AS canuse_num , plan_pin FROM VIEW_MATER_STOCK_WM WHERE plan_pin IN (");
            StringBuilder noStr = new StringBuilder();
            for (String no : list) {
                noStr.append("'").append(no).append("',");
            }
            if (noStr.length() > 0) {
                noStr.deleteCharAt(noStr.length() - 1);
            }
            sql.append(noStr);
            sql.append(") GROUP BY plan_pin");

            List<Map> cmmp = RequestCmmp.getFromCmmp(0L, sql.toString(), key);

            if (YEM.isNotEmpty(cmmp)) {
                cmmpQtyMap.put(key, cmmp);
            }
        }

        for (DynamicObject dynamicObject : collection) {
            long products = dynamicObject.getLong("yem_bd_products");
            String vehicleno = dynamicObject.getString("yem_vehicleno");

            if (cmmpQtyMap.containsKey(products)) {
                List<Map> cmmp = cmmpQtyMap.get(products);
                for (Map qty : cmmp) {
                    String plan_pin = (String) qty.get("plan_pin");
                    BigDecimal canuse_num = (BigDecimal) qty.get("canuse_num");
                    if (YEM.isNotEmpty(plan_pin) && YEM.isNotEmpty(canuse_num) && YEM.isNotEmpty(vehicleno) && vehicleno.equals(plan_pin)) {
                        dynamicObject.set("yem_inventoryqty", canuse_num);
                    }
                }
            }
        }

        for (DynamicObject dynamicObject : collection) {
            String vehicleno = dynamicObject.getString("yem_vehicleno");
            if (YEM.isNotEmpty(vehicleno)) {
                BigDecimal inventoryqty = dynamicObject.getBigDecimal("yem_inventoryqty");
                dynamicObject.set("yem_reqqty", inventoryqty);
            }
        }


        return RptUtil.plainDataSet(algoKey, where, collection);
    }
}
