package nccloud.openapi.purp.pp;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import nc.bs.dao.BaseDAO;
import nc.bs.framework.common.NCLocator;
import nc.bs.trade.business.HYPubBO;
import nc.itf.so.m30.closemanage.ISaleOrderCloseManageMaintain;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.pubitf.so.m30.api.ISaleOrderQueryAPI;
import nc.vo.so.m30.entity.SaleOrderVO;
import nccloud.api.rest.utils.ResultMessageUtil;
import nccloud.ws.rest.resource.AbstractNCCRestResource;
import org.json.JSONString;

import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Path("/purp/pp")
public class PpResource extends AbstractNCCRestResource {

    public static final BaseDAO BASE_DAO = new BaseDAO();
    @Override
    public String getModule() {
        return "purp";
    }

    @POST
    @Path("query")
    @Consumes({"application/json"})
    @Produces({"application/json"})
    public JSONString CloseSoResource(JSONString json) {
        JSONObject errojson = new JSONObject();
        try {
            JSONObject jObject = JSON.parseObject(json.toJSONString());
            if (jObject == null) {
                return ResultMessageUtil.exceptionToJSON(new NullPointerException("JSONString:null"));
            }
            JSONArray array = jObject.getJSONArray("orgCode");

            String dataSql;
            if (array.isEmpty()) {
                return ResultMessageUtil.exceptionToJSON(new NullPointerException("请选择组织编码"));
            }else
            {
                dataSql="\tSELECT\n" +
                        "\t*\n" +
                        "FROM\n" +
                        "\t(\n" +
                        "\tSELECT\n" +
                        "\t\trow_.*,\n" +
                        "\t\trownum rownumber_\n" +
                        "\tFROM\n" +
                        "\t\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tdp.pk_org_v PK_ORG_V,\n" +
                        "\t\t\tdp.pk_supplier PK_SUPPLIER,\n" +
                        "\t\t\tdp.pk_material PK_SRCMATERIAL,\n" +
                        "\t\t\tdp.castunitid CASTUNITID,\n" +
                        "\t\t\tdp.corigcurrencyid CORIGCURRENCYID,\n" +
                        "\t\t\tdp.bsc BSC,\n" +
                        "\t\t\tdp.ftaxtypeflag FTAXTYPEFLAG,\n" +
                        "\t\t\tdp.ntaxrate NTAXRATE,\n" +
                        "\t\t\tdp.norigprice NORIGPRICE,\n" +
                        "\t\t\tdp.nastorigtaxpricea NASTORIGTAXPRICEA,\n" +
                        "\t\t\tsubstr ( dp.dvaliddatea,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DVALIDDATEA,\n" +
                        "\t\t\tsubstr ( dp.dinvaliddatea,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DINVALIDDATEA,\n" +
                        "\t\t\tdp.norigtaxprice NORIGTAXPRICE,\n" +
                        "\t\t\tsubstr ( dp.dvaliddateb,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DVALIDDATEB,\n" +
                        "\t\t\tsubstr ( dp.dinvaliddateb,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DINVALIDDATEB,\n" +
                        "\t\t\tdp.nastorigtaxpriceh NASTORIGTAXPRICEH,\n" +
                        "\t\t\tsubstr ( dp.dvaliddateh,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DVALIDDATEH,\n" +
                        "\t\t\tsubstr ( dp.dinvaliddateh,\n" +
                        "\t\t\t1,\n" +
                        "\t\t\t10 ) DINVALIDDATEH,\n" +
                        "\t\t\tdp.nastorigtaxpriced NASTORIGTAXPRICED\n" +
                        "\t\tFROM\n" +
                        "\t\t\t(\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\tpk_org_v pk_org_v,\n" +
                        "\t\t\t\tpk_org pk_org,\n" +
                        "\t\t\t\tpk_supplier pk_supplier,\n" +
                        "\t\t\t\tNULL pk_supplier_v,\n" +
                        "\t\t\t\tpk_material pk_material,\n" +
                        "\t\t\t\tcastunitid castunitid,\n" +
                        "\t\t\t\tcorigcurrencyid corigcurrencyid,\n" +
                        "\t\t\t\tbsc bsc,\n" +
                        "\t\t\t\tftaxtypeflag ftaxtypeflag,\n" +
                        "\t\t\t\tntaxrate ntaxrate,\n" +
                        "\t\t\t\tnastorigprice nastorigprice,\n" +
                        "\t\t\t\tnastorigtaxprice nastorigtaxprice,\n" +
                        "\t\t\t\tnorigprice norigprice,\n" +
                        "\t\t\t\tnorigtaxprice norigtaxprice,\n" +
                        "\t\t\t\tnastorigpricea nastorigpricea,\n" +
                        "\t\t\t\tnastorigtaxpricea nastorigtaxpricea,\n" +
                        "\t\t\t\tnorigpricea norigpricea,\n" +
                        "\t\t\t\tnorigtaxpricea norigtaxpricea,\n" +
                        "\t\t\t\tnastorigpriceb nastorigpriceb,\n" +
                        "\t\t\t\tnastorigtaxpriceb nastorigtaxpriceb,\n" +
                        "\t\t\t\tnorigpriceb norigpriceb,\n" +
                        "\t\t\t\tnorigtaxpriceb norigtaxpriceb,\n" +
                        "\t\t\t\tnastorigpriceh nastorigpriceh,\n" +
                        "\t\t\t\tnastorigtaxpriceh nastorigtaxpriceh,\n" +
                        "\t\t\t\tnorigpriceh norigpriceh,\n" +
                        "\t\t\t\tnorigtaxpriceh norigtaxpriceh,\n" +
                        "\t\t\t\tnastorigpriced nastorigpriced,\n" +
                        "\t\t\t\tnastorigtaxpriced nastorigtaxpriced,\n" +
                        "\t\t\t\tnorigpriced norigpriced,\n" +
                        "\t\t\t\tnorigtaxpriced norigtaxpriced,\n" +
                        "\t\t\t\tdinvaliddatea dinvaliddatea,\n" +
                        "\t\t\t\tdvaliddatea dvaliddatea,\n" +
                        "\t\t\t\tdinvaliddateb dinvaliddateb,\n" +
                        "\t\t\t\tdvaliddateb dvaliddateb,\n" +
                        "\t\t\t\tdinvaliddateh dinvaliddateh,\n" +
                        "\t\t\t\tdvaliddateh dvaliddateh\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\t(\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tpk_org_v pk_org_v,\n" +
                        "\t\t\t\t\tpk_supplier pk_supplier,\n" +
                        "\t\t\t\t\tpk_material pk_material,\n" +
                        "\t\t\t\t\tcastunitid castunitid,\n" +
                        "\t\t\t\t\tcorigcurrencyid corigcurrencyid,\n" +
                        "\t\t\t\t\t(\n" +
                        "\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\tWHEN bsc = 'N' THEN '否'\n" +
                        "\t\t\t\t\t\tWHEN bsc = 'Y' THEN '是'\n" +
                        "\t\t\t\t\tEND ) bsc,\n" +
                        "\t\t\t\t\tftaxtypeflag ftaxtypeflag,\n" +
                        "\t\t\t\t\tntaxrate ntaxrate,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigprice,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigprice,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigtaxprice,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigtaxprice,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigprice,\n" +
                        "\t\t\t\t\t'0' ) ) norigprice,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigtaxprice,\n" +
                        "\t\t\t\t\t'0' ) ) norigtaxprice,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigpricea,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigpricea,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigtaxpricea,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigtaxpricea,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigpricea,\n" +
                        "\t\t\t\t\t'0' ) ) norigpricea,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigtaxpricea,\n" +
                        "\t\t\t\t\t'0' ) ) norigtaxpricea,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigpriceb,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigpriceb,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigtaxpriceb,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigtaxpriceb,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigpriceb,\n" +
                        "\t\t\t\t\t'0' ) ) norigpriceb,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigtaxpriceb,\n" +
                        "\t\t\t\t\t'0' ) ) norigtaxpriceb,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigpriceh,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigpriceh,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigtaxpriceh,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigtaxpriceh,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigpriceh,\n" +
                        "\t\t\t\t\t'0' ) ) norigpriceh,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigtaxpriceh,\n" +
                        "\t\t\t\t\t'0' ) ) norigtaxpriceh,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigpriced,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigpriced,\n" +
                        "\t\t\t\t\tmax ( nvl ( nastorigtaxpriced,\n" +
                        "\t\t\t\t\t'0' ) ) nastorigtaxpriced,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigpriced,\n" +
                        "\t\t\t\t\t'0' ) ) norigpriced,\n" +
                        "\t\t\t\t\tmax ( nvl ( norigtaxpriced,\n" +
                        "\t\t\t\t\t'0' ) ) norigtaxpriced,\n" +
                        "\t\t\t\t\tmax ( nvl ( dinvaliddatea,\n" +
                        "\t\t\t\t\t'' ) ) dinvaliddatea,\n" +
                        "\t\t\t\t\tmax ( nvl ( dvaliddatea,\n" +
                        "\t\t\t\t\t'' ) ) dvaliddatea,\n" +
                        "\t\t\t\t\tmax ( nvl ( dinvaliddateb,\n" +
                        "\t\t\t\t\t'' ) ) dinvaliddateb,\n" +
                        "\t\t\t\t\tmax ( nvl ( dvaliddateb,\n" +
                        "\t\t\t\t\t'' ) ) dvaliddateb,\n" +
                        "\t\t\t\t\tmax ( nvl ( dinvaliddateh,\n" +
                        "\t\t\t\t\t'' ) ) dinvaliddateh,\n" +
                        "\t\t\t\t\tmax ( nvl ( dvaliddateh,\n" +
                        "\t\t\t\t\t'' ) ) dvaliddateh,\n" +
                        "\t\t\t\t\torg_orgs.pk_org pk_org,\n" +
                        "\t\t\t\t\tmax ( nvl ( pk_srcmaterial,\n" +
                        "\t\t\t\t\t'' ) ) pk_srcmaterial,\n" +
                        "\t\t\t\t\tmax ( nvl ( pk_dept,\n" +
                        "\t\t\t\t\t'' ) ) pk_dept,\n" +
                        "\t\t\t\t\tmax ( nvl ( pk_dept_v,\n" +
                        "\t\t\t\t\t'' ) ) pk_dept_v,\n" +
                        "\t\t\t\t\tmax ( nvl ( pk_bizpsn,\n" +
                        "\t\t\t\t\t'' ) ) pk_bizpsn\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\t( (\n" +
                        "\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\tbsc bsc,\n" +
                        "\t\t\t\t\t\tcorigcurrencyid corigcurrencyid,\n" +
                        "\t\t\t\t\t\tps.pk_org_v pk_org_v,\n" +
                        "\t\t\t\t\t\tpk_supplier pk_supplier,\n" +
                        "\t\t\t\t\t\tps.pk_material pk_material,\n" +
                        "\t\t\t\t\t\tntaxrate ntaxrate,\n" +
                        "\t\t\t\t\t\tftaxtypeflag ftaxtypeflag,\n" +
                        "\t\t\t\t\t\tcastunitid castunitid,\n" +
                        "\t\t\t\t\t\tnastorigprice nastorigprice,\n" +
                        "\t\t\t\t\t\tnastorigtaxprice nastorigtaxprice,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.bcanorder = 'Y' THEN nastorigtaxprice\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) norigprice,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '28' THEN nastorigtaxprice\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '29'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN nastorigtaxprice\n" +
                        "\t\t\t\t\t\t\tWHEN nvl ( ps.vsrctype,\n" +
                        "\t\t\t\t\t\t\t'~' ) = '~'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN nastorigtaxprice\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) norigtaxprice,\n" +
                        "\t\t\t\t\t\tnastorigprice nastorigpricea,\n" +
                        "\t\t\t\t\t\tnastorigtaxprice nastorigtaxpricea,\n" +
                        "\t\t\t\t\t\tnorigprice norigpricea,\n" +
                        "\t\t\t\t\t\tnorigtaxprice norigtaxpricea,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '28'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN nastorigprice\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) nastorigpriceb,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '28'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN nastorigtaxprice\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) nastorigtaxpriceb,\n" +
                        "\t\t\t\t\t\tnorigprice norigpriceb,\n" +
                        "\t\t\t\t\t\tnorigtaxprice norigtaxpriceb,\n" +
                        "\t\t\t\t\t\tdinvaliddate dinvaliddatea,\n" +
                        "\t\t\t\t\t\tdvaliddate dvaliddatea,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '28'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN dinvaliddate\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) dinvaliddateb,\n" +
                        "\t\t\t\t\t\t(\n" +
                        "\t\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\t\tWHEN ps.vsrctype = '28'\n" +
                        "\t\t\t\t\t\t\tAND ps.bcanorder = 'Y' THEN dvaliddate\n" +
                        "\t\t\t\t\t\t\tELSE NULL\n" +
                        "\t\t\t\t\t\tEND ) dvaliddateb,\n" +
                        "\t\t\t\t\t\tNULL nastorigpriceh,\n" +
                        "\t\t\t\t\t\tNULL nastorigtaxpriceh,\n" +
                        "\t\t\t\t\t\tNULL norigpriceh,\n" +
                        "\t\t\t\t\t\tNULL norigtaxpriceh,\n" +
                        "\t\t\t\t\t\tNULL dinvaliddateh,\n" +
                        "\t\t\t\t\t\tNULL dvaliddateh,\n" +
                        "\t\t\t\t\t\tNULL nastorigpriced,\n" +
                        "\t\t\t\t\t\tNULL nastorigtaxpriced,\n" +
                        "\t\t\t\t\t\tNULL norigpriced,\n" +
                        "\t\t\t\t\t\tNULL norigtaxpriced,\n" +
                        "\t\t\t\t\t\tNULL pk_org,\n" +
                        "\t\t\t\t\t\tNULL pk_srcmaterial,\n" +
                        "\t\t\t\t\t\tNULL pk_dept,\n" +
                        "\t\t\t\t\t\tNULL pk_dept_v,\n" +
                        "\t\t\t\t\t\tNULL pk_bizpsn\n" +
                        "\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\tpurp_supplierprice ps\n" +
                        "\t\t\t\t\tINNER JOIN (\n" +
                        "\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\tps.corigcurrencyid corigcurrencyid1,\n" +
                        "\t\t\t\t\t\t\tps.pk_org_v pk_org_v1,\n" +
                        "\t\t\t\t\t\t\tps.pk_supplier pk_supplier1,\n" +
                        "\t\t\t\t\t\t\tps.pk_material pk_material1,\n" +
                        "\t\t\t\t\t\t\tps.ntaxrate ntaxrate1,\n" +
                        "\t\t\t\t\t\t\tps.ftaxtypeflag ftaxtypeflag1,\n" +
                        "\t\t\t\t\t\t\tps.castunitid castunitid1,\n" +
                        "\t\t\t\t\t\t\tmax (ps.ts) ts1\n" +
                        "\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\tpurp_supplierprice ps\n" +
                        "\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\tps.dr = 0\n" +
//                        "\t\t\t\t\t\t\tAND ps.pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\t\t\t\tAND ( ps.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\t\t\tAND ( ps.dvaliddate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\tAND ( ps.dvaliddate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\tAND ps.pk_material IN (\n" +
                        "\t\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' )\n" +
                        "\t\t\t\t\t\tGROUP BY\n" +
                        "\t\t\t\t\t\t\tps.corigcurrencyid,\n" +
                        "\t\t\t\t\t\t\tps.pk_org_v,\n" +
                        "\t\t\t\t\t\t\tps.pk_supplier,\n" +
                        "\t\t\t\t\t\t\tps.pk_material,\n" +
                        "\t\t\t\t\t\t\tps.ntaxrate,\n" +
                        "\t\t\t\t\t\t\tps.ftaxtypeflag,\n" +
                        "\t\t\t\t\t\t\tps.castunitid ) t_1 ON\n" +
                        "\t\t\t\t\t\tps.corigcurrencyid = t_1.corigcurrencyid1\n" +
                        "\t\t\t\t\t\tAND ps.pk_org_v = t_1.pk_org_v1\n" +
                        "\t\t\t\t\t\tAND ps.pk_supplier = t_1.pk_supplier1\n" +
                        "\t\t\t\t\t\tAND ps.pk_material = t_1.pk_material1\n" +
                        "\t\t\t\t\t\tAND ps.ntaxrate = t_1.ntaxrate1\n" +
                        "\t\t\t\t\t\tAND ps.ftaxtypeflag = t_1.ftaxtypeflag1\n" +
                        "\t\t\t\t\t\tAND ps.castunitid = t_1.castunitid1\n" +
                        "\t\t\t\t\t\tAND ps.ts = t_1.ts1\n" +
                        "\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\tdr = 0\n" +
//                        "\t\t\t\t\t\tAND pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\t\t\tAND ( ps.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\t\tAND ( ps.dvaliddate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\tAND ( ps.dvaliddate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\tAND ps.pk_material IN (\n" +
                        "\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' ) )\n" +
                        "\t\t\tUNION ALL (\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tcp.bsc bsc,\n" +
                        "\t\t\t\t\tcp.corigcurrencyid corigcurrencyid,\n" +
                        "\t\t\t\t\tcp.pk_org_v pk_org_v,\n" +
                        "\t\t\t\t\tcp.cvendorid cvendorid,\n" +
                        "\t\t\t\t\tcpb.pk_material pk_material,\n" +
                        "\t\t\t\t\tcpb.ntaxrate ntaxrate,\n" +
                        "\t\t\t\t\tcpb.ftaxtypeflag ftaxtypeflag,\n" +
                        "\t\t\t\t\tcpb.cqtunitid cqtunitid,\n" +
                        "\t\t\t\t\tNULL nastorigprice,\n" +
                        "\t\t\t\t\tNULL nastorigtaxprice,\n" +
                        "\t\t\t\t\tNULL norigprice,\n" +
                        "\t\t\t\t\tNULL norigtaxprice,\n" +
                        "\t\t\t\t\tNULL nastorigpricea,\n" +
                        "\t\t\t\t\tNULL nastorigtaxpricea,\n" +
                        "\t\t\t\t\tNULL norigpricea,\n" +
                        "\t\t\t\t\tNULL norigtaxpricea,\n" +
                        "\t\t\t\t\tNULL nastorigpriceb,\n" +
                        "\t\t\t\t\tNULL nastorigtaxpriceb,\n" +
                        "\t\t\t\t\tNULL norigpriceb,\n" +
                        "\t\t\t\t\tNULL norigtaxpriceb,\n" +
                        "\t\t\t\t\tNULL dinvaliddatea,\n" +
                        "\t\t\t\t\tNULL dvaliddatea,\n" +
                        "\t\t\t\t\tNULL dinvaliddateb,\n" +
                        "\t\t\t\t\tNULL dvaliddateb,\n" +
                        "\t\t\t\t\tcpb.nqtorigprice nqtorigprice,\n" +
                        "\t\t\t\t\tcpb.nqtorigtaxprice nqtorigtaxprice,\n" +
                        "\t\t\t\t\tcpb.norigprice norigprice,\n" +
                        "\t\t\t\t\tcpb.norigtaxprice norigtaxprice,\n" +
                        "\t\t\t\t\t(\n" +
                        "\t\t\t\t\tCASE\n" +
                        "\t\t\t\t\t\tWHEN cp.fstatusflag = 1\n" +
                        "\t\t\t\t\t\t\tOR cp.fstatusflag = 5 THEN cp.invallidate\n" +
                        "\t\t\t\t\t\t\tWHEN cp.fstatusflag = 6 THEN cp.actualinvalidate\n" +
                        "\t\t\t\t\t\tEND ) dinvaliddateh,\n" +
                        "\t\t\t\t\tcp.actualvalidate actualvalidate,\n" +
                        "\t\t\t\t\tCAST( NULL AS float ),\n" +
                        "\t\t\t\t\tCAST( NULL AS float ),\n" +
                        "\t\t\t\t\tCAST( NULL AS float ),\n" +
                        "\t\t\t\t\tCAST( NULL AS float ),\n" +
                        "\t\t\t\t\tNULL pk_org,\n" +
                        "\t\t\t\t\tNULL pk_srcmaterial,\n" +
                        "\t\t\t\t\tNULL pk_dept,\n" +
                        "\t\t\t\t\tNULL pk_dept_v,\n" +
                        "\t\t\t\t\tNULL pk_bizpsn\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tct_pu cp\n" +
                        "\t\t\t\tINNER JOIN ct_pu_b cpb ON\n" +
                        "\t\t\t\t\tcpb.pk_ct_pu = cp.pk_ct_pu\n" +
                        "\t\t\t\tINNER JOIN (\n" +
                        "\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\tcp.corigcurrencyid corigcurrencyid1,\n" +
                        "\t\t\t\t\t\tcp.pk_org_v pk_org_v1,\n" +
                        "\t\t\t\t\t\tcp.bsc bsc1,\n" +
                        "\t\t\t\t\t\tcp.cvendorid cvendorid1,\n" +
                        "\t\t\t\t\t\tcpb.pk_material pk_material1,\n" +
                        "\t\t\t\t\t\tcpb.ntaxrate ntaxrate1,\n" +
                        "\t\t\t\t\t\tcpb.ftaxtypeflag ftaxtypeflag1,\n" +
                        "\t\t\t\t\t\tcpb.castunitid castunitid1,\n" +
                        "\t\t\t\t\t\tmax (cp.ts) ts1\n" +
                        "\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\tct_pu cp\n" +
                        "\t\t\t\t\tINNER JOIN ct_pu_b cpb ON\n" +
                        "\t\t\t\t\t\tcpb.pk_ct_pu = cp.pk_ct_pu\n" +
                        "\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\tcp.dr = 0\n" +
                        "\t\t\t\t\t\tAND cpb.dr = 0\n" +
                        "\t\t\t\t\t\tAND cp.blatest = 'Y'\n" +
//                        "\t\t\t\t\t\tAND cpb.pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\t\t\tAND cp.fstatusflag IN ( '1', '5', '6' )\n" +
                        "\t\t\t\t\t\t\tAND ( cp.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\t\t\t\tAND ( cp.dbilldate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\t\t\tAND ( cp.dbilldate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\t\t\t\tAND cpb.pk_material IN (\n" +
                        "\t\t\t\t\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' )\n" +
                        "\t\t\t\t\t\t\t\t\tGROUP BY\n" +
                        "\t\t\t\t\t\t\t\t\t\tcp.pk_org_v,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcp.cvendorid,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcpb.pk_material,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcpb.castunitid,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcpb.ntaxrate,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcpb.ftaxtypeflag,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcp.bsc,\n" +
                        "\t\t\t\t\t\t\t\t\t\tcp.corigcurrencyid ) t_2 ON\n" +
                        "\t\t\t\t\tcp.corigcurrencyid = t_2.corigcurrencyid1\n" +
                        "\t\t\t\t\tAND cp.pk_org_v = t_2.pk_org_v1\n" +
                        "\t\t\t\t\tAND cp.bsc = bsc1\n" +
                        "\t\t\t\t\tAND cp.cvendorid = t_2.cvendorid1\n" +
                        "\t\t\t\t\tAND cpb.pk_material = t_2.pk_material1\n" +
                        "\t\t\t\t\tAND cpb.ntaxrate = t_2.ntaxrate1\n" +
                        "\t\t\t\t\tAND cpb.ftaxtypeflag = t_2.ftaxtypeflag1\n" +
                        "\t\t\t\t\tAND cpb.castunitid = t_2.castunitid1\n" +
                        "\t\t\t\t\tAND cp.ts = t_2.ts1\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\tcp.dr = 0\n" +
                        "\t\t\t\t\tAND cpb.dr = 0\n" +
                        "\t\t\t\t\tAND cp.blatest = 'Y'\n" +
//                        "\t\t\t\t\tAND cpb.pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\t\tAND cp.fstatusflag IN ( '1', '5', '6' )\n" +
                        "\t\t\t\t\t\tAND ( cp.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\t\t\tAND ( cp.dbilldate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\t\tAND ( cp.dbilldate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\t\t\tAND cpb.pk_material IN (\n" +
                        "\t\t\t\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' ) )\n" +
                        "\t\tUNION ALL (\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\t'N' bsc,\n" +
                        "\t\t\t\tpob.corigcurrencyid corigcurrencyid,\n" +
                        "\t\t\t\tpob.pk_org_v pk_org_v,\n" +
                        "\t\t\t\tpob.pk_supplier pk_supplier,\n" +
                        "\t\t\t\tpob.pk_material pk_material,\n" +
                        "\t\t\t\tpob.ntaxrate ntaxrate,\n" +
                        "\t\t\t\tpob.ftaxtypeflag ftaxtypeflag,\n" +
                        "\t\t\t\tpob.castunitid castunitid,\n" +
                        "\t\t\t\tNULL nastorigprice,\n" +
                        "\t\t\t\tNULL nastorigtaxprice,\n" +
                        "\t\t\t\tNULL norigprice,\n" +
                        "\t\t\t\tNULL norigtaxprice,\n" +
                        "\t\t\t\tNULL nastorigpricea,\n" +
                        "\t\t\t\tNULL nastorigtaxpricea,\n" +
                        "\t\t\t\tNULL norigpricea,\n" +
                        "\t\t\t\tNULL norigtaxpricea,\n" +
                        "\t\t\t\tNULL nastorigpriceb,\n" +
                        "\t\t\t\tNULL nastorigtaxpriceb,\n" +
                        "\t\t\t\tNULL norigpriceb,\n" +
                        "\t\t\t\tNULL norigtaxpriceb,\n" +
                        "\t\t\t\tNULL dinvaliddatea,\n" +
                        "\t\t\t\tNULL dvaliddatea,\n" +
                        "\t\t\t\tNULL dinvaliddateb,\n" +
                        "\t\t\t\tNULL dvaliddateb,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tNULL,\n" +
                        "\t\t\t\tpob.nqtorignetprice nqtorignetprice,\n" +
                        "\t\t\t\tpob.nqtorigtaxnetprc nqtorigtaxnetprc,\n" +
                        "\t\t\t\tpob.norignetprice norignetprice,\n" +
                        "\t\t\t\tpob.norigtaxnetprice norigtaxnetprice,\n" +
                        "\t\t\t\tNULL pk_org,\n" +
                        "\t\t\t\tNULL pk_srcmaterial,\n" +
                        "\t\t\t\tNULL pk_dept,\n" +
                        "\t\t\t\tNULL pk_dept_v,\n" +
                        "\t\t\t\tNULL pk_bizpsn\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpo_order po\n" +
                        "\t\t\tINNER JOIN po_order_b pob ON\n" +
                        "\t\t\t\tpo.pk_order = pob.pk_order\n" +
                        "\t\t\tINNER JOIN (\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tpob.pk_org_v pk_org_v1,\n" +
                        "\t\t\t\t\tpob.pk_supplier pk_supplier1,\n" +
                        "\t\t\t\t\tpob.pk_material pk_material1,\n" +
                        "\t\t\t\t\tpob.castunitid castunitid1,\n" +
                        "\t\t\t\t\tpob.corigcurrencyid corigcurrencyid1,\n" +
                        "\t\t\t\t\tpob.ftaxtypeflag ftaxtypeflag1,\n" +
                        "\t\t\t\t\tpob.ntaxrate ntaxrate1,\n" +
                        "\t\t\t\t\tmax (po.ts) ts1\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tpo_order po\n" +
                        "\t\t\t\tINNER JOIN po_order_b pob ON\n" +
                        "\t\t\t\t\tpo.pk_order = pob.pk_order\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\tpo.bislatest = 'Y'\n" +
                        "\t\t\t\t\tAND po.dr = 0\n" +
//                        "\t\t\t\t\tAND po.pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\t\tAND ( po.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\t\tAND ( po.dbilldate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\tAND ( po.dbilldate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\t\tAND pob.pk_material IN (\n" +
                        "\t\t\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' )\n" +
                        "\t\t\t\t\t\t\tGROUP BY\n" +
                        "\t\t\t\t\t\t\t\tpob.pk_org_v,\n" +
                        "\t\t\t\t\t\t\t\tpob.pk_supplier,\n" +
                        "\t\t\t\t\t\t\t\tpob.pk_material,\n" +
                        "\t\t\t\t\t\t\t\tpob.castunitid,\n" +
                        "\t\t\t\t\t\t\t\tpob.corigcurrencyid,\n" +
                        "\t\t\t\t\t\t\t\tpob.ftaxtypeflag,\n" +
                        "\t\t\t\t\t\t\t\tpob.ntaxrate ) t_3 ON\n" +
                        "\t\t\t\tpob.corigcurrencyid = t_3.corigcurrencyid1\n" +
                        "\t\t\t\tAND pob.pk_org_v = t_3.pk_org_v1\n" +
                        "\t\t\t\tAND pob.pk_supplier = t_3.pk_supplier1\n" +
                        "\t\t\t\tAND pob.pk_material = t_3.pk_material1\n" +
                        "\t\t\t\tAND pob.ntaxrate = t_3.ntaxrate1\n" +
                        "\t\t\t\tAND pob.ftaxtypeflag = t_3.ftaxtypeflag1\n" +
                        "\t\t\t\tAND pob.castunitid = t_3.castunitid1\n" +
                        "\t\t\t\tAND po.ts = t_3.ts1\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\tpo.dr = 0\n" +
                        "\t\t\t\tAND pob.dr = 0\n" +
                        "\t\t\t\tAND po.bislatest = 'Y'\n" +
//                        "\t\t\t\tAND po.pk_group = '0001A110000000000677'\n" +
                        "\t\t\t\tAND ( po.pk_org = '0001A1100000000026PG' )\n" +
                        "\t\t\t\t\tAND ( po.dbilldate >= '2025-02-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\tAND ( po.dbilldate <= '2025-07-01 09:26:53 ' )\n" +
                        "\t\t\t\t\t\t\tAND pob.pk_material IN (\n" +
                        "\t\t\t\t\t\t\tSELECT\n" +
                        "\t\t\t\t\t\t\t\tbd_material.pk_material pk_material\n" +
                        "\t\t\t\t\t\t\tFROM\n" +
                        "\t\t\t\t\t\t\t\tbd_material bd_material\n" +
                        "\t\t\t\t\t\t\tWHERE\n" +
                        "\t\t\t\t\t\t\t\tbd_material.dr = 0\n" +
                        "\t\t\t\t\t\t\t\tAND bd_material.code = '11021101000113' ) ) ) purp_supplierprice\n" +
                        "\t\t\t\tINNER JOIN org_orgs org_orgs ON\n" +
                        "\t\t\t\t\torg_orgs.pk_vid = purp_supplierprice.pk_org_v\n" +
                        "\t\t\t\tGROUP BY\n" +
                        "\t\t\t\t\tbsc,\n" +
                        "\t\t\t\t\tcorigcurrencyid,\n" +
                        "\t\t\t\t\tpk_org_v,\n" +
                        "\t\t\t\t\torg_orgs.pk_org,\n" +
                        "\t\t\t\t\tpk_supplier,\n" +
                        "\t\t\t\t\tpk_material,\n" +
                        "\t\t\t\t\tntaxrate,\n" +
                        "\t\t\t\t\tftaxtypeflag,\n" +
                        "\t\t\t\t\tcastunitid ) tmp ) dp\n" +
                        "\t\tLEFT OUTER JOIN bd_material bd_material ON\n" +
                        "\t\t\tdp.pk_material = bd_material.pk_material\n" +
//                        "\t\tWHERE\n" +
//                        "\t\t\tdp.pk_org IN ( '0001A1100000000026QF', '0001A1100000000026QI', '0001A110000000002OFF', '0001A1100000000026UO', '0001A1100000000026ID', '0001A1100000000026QL', '0001A1100000004WA8WN', '0001A1100000004WAZZO', '0001A1100000000026QC', '0001A1100000000026R7', '0001A1100000003JJO09', '0001A1100000000026QO', '0001A1100000000026R1', '0001A1100000000026R4', '0001A1100000004T5PVD', '0001A1100000000026RI', '0001A1100000000026IV', '0001A1100000000026RA', '0001A1100000000026RD', '0001A11000000011I0K9', '0001A1100000000026RW', '0001A1100000000026O5', '0001A1100000000026S9', '0001A1100000000026RZ', '0001A1100000000026O8', '0001A1100000004T5785', '0001A1100000000026JG', '0001A11000000000273P', '0001A1100000000026RQ', '0001A1100000000026S3', '0001A1100000000026S6', '0001A1100000000026SI', '0001A1100000000026WN', '0001A1100000000026SL', '0001A1100000004WA8Z2', '0001A1100000000026SC', '0001A1100000004WAZY3', '0001A1100000000026SF', '0001A1100000000026T9', '0001A1100000000026OY', '0001A1100000000026SO', '0001A1100000000026SR', '0001A1100000000026PG', '0001A1100000000026XQ', '0001A1100000000026TC', '0001A1100000000026PC', '0001A1100000000026Q5', '0001A1100000000026PY', '0001A1100000000026U1', '0001A1100000000026Y5', '0001A1100000000026XW', '0001A1100000000026PO', '0001A1100000000026TU', '0001A1100000000026PR' ) " +
                        ") row_ )\n" +
                        "WHERE\n" +
                        "\trownumber_ <= 5000";
            }

            List<String> materialCodes = jObject.getJSONArray("materialCodes").toJavaList(String.class);
            List<String> orgCode = jObject.getJSONArray("orgCode").toJavaList(String.class);
            // 修改1: 检查物料编码是否为空
            if (materialCodes == null || materialCodes.isEmpty()) {
                // 物料编码为空时，移除所有物料编码过滤条件
                dataSql = dataSql
                        .replaceAll("AND ps.pk_material IN \\([\\s\\S]*?bd_material\\.code = '11021101000113' \\)", "")
                        .replaceAll("AND cpb\\.pk_material IN \\([\\s\\S]*?bd_material\\.code = '11021101000113' \\)", "")
                        .replaceAll("AND pob\\.pk_material IN \\([\\s\\S]*?bd_material\\.code = '11021101000113' \\)", "");
            } else {
                // 物料编码不为空时，正常拼接物料编码条件
                String materialCodesStr = materialCodes.stream()
                        .map(code -> "'" + code.replace("'", "''") + "'")
                        .collect(Collectors.joining(","));
                dataSql = dataSql
                        .replaceAll("bd_material.code = '11021101000113'",
                                "bd_material.code IN (" + materialCodesStr + ")");
            }

            String orgCodes = orgCode.stream()
                    .map(id -> "'" + id.replace("'", "''") + "'")  // 防止单引号注入
                    .collect(Collectors.joining(","));

            int recentMonths = jObject.getIntValue("months");  // 新增：获取最近月份数
//            HYPubBO hybo = new HYPubBO();
//            Object orgId= hybo.findColValue("org_purchaseorg", "pk_purchaseorg", "code in (" + orgIdsStr + ")");
            String orgsql="SELECT pk_purchaseorg FROM org_purchaseorg WHERE "+"code in (" + orgCodes + ")";
            List<String> orgids = (List<String>) BASE_DAO.executeQuery(orgsql, new ColumnListProcessor());
            String orgIdsStr = orgids.stream()
                    .map(id -> "'" + id.replace("'", "''") + "'")  // 防止单引号注入
                    .collect(Collectors.joining(","));
// 计算动态时间范围
            Calendar calendar = Calendar.getInstance();
            Date endDate = calendar.getTime();  // 结束时间 = 当前时间
            calendar.add(Calendar.MONTH, -recentMonths);  // 开始时间 = 当前时间 - 月份数
            Date startDate = calendar.getTime();

// 格式化时间（保持原SQL格式）
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String startDateStr = sdf.format(startDate);
            String endDateStr = sdf.format(endDate);


// 修改SQL：替换物料编码和组织ID
            dataSql = dataSql
                    // 替换组织ID条件（改为IN查询）
                    .replaceAll("ps.pk_org = '0001A1100000000026PG'",
                            "ps.pk_org IN (" + orgIdsStr + ")")
                    .replaceAll("cp.pk_org = '0001A1100000000026PG'",
                            "cp.pk_org IN (" + orgIdsStr + ")")
                    .replaceAll("po.pk_org = '0001A1100000000026PG'",
                            "po.pk_org IN (" + orgIdsStr + ")");
            // 替换时间条件 - 所有出现的位置
            dataSql = dataSql
                    .replaceAll("ps.dvaliddate >= '2025-02-01 09:26:53 '", "ps.dvaliddate >= '" + startDateStr + "'")
                    .replaceAll("ps.dvaliddate <= '2025-07-01 09:26:53 '", "ps.dvaliddate <= '" + endDateStr + "'")
                    .replaceAll("cp.dbilldate >= '2025-02-01 09:26:53 '", "cp.dbilldate >= '" + startDateStr + "'")
                    .replaceAll("cp.dbilldate <= '2025-07-01 09:26:53 '", "cp.dbilldate <= '" + endDateStr + "'")
                    .replaceAll("po.dbilldate >= '2025-02-01 09:26:53 '", "po.dbilldate >= '" + startDateStr + "'")
                    .replaceAll("po.dbilldate <= '2025-07-01 09:26:53 '", "po.dbilldate <= '" + endDateStr + "'");
            List<Map<String, Object>> rows = (List<Map<String, Object>>) BASE_DAO.executeQuery(dataSql, new MapListProcessor());
            // 返回表示操作成功的JSON字符串
            return ResultMessageUtil.toJSON(rows);



        } catch (Exception e) {
            errojson.put("status", "0");
            errojson.put("message", e.getMessage());
            errojson.put("codeList", "");
            // 返回表示操作失败的JSON字符串
            return ResultMessageUtil.toJSON(errojson);
        }
    }
}
