md_YOS/src/custom/common/crm/bean/icstockbill.java

package common.crm.bean;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.YosException;
import common.YosLogger;
import common.data.*;
import common.data.db.DBConnect;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import common.crm.bean.core.CrmBase;

import java.util.ArrayList;
import java.util.Calendar;

public class icstockbill extends CrmBase {
    public icstockbill(Logger logger) {
        super(logger);
    }

    /**
     * 超过30天的销售出库单自动收货
     *
     * @throws YosException
     */
    @Override
    public void autoAction() throws YosException {
        //到期自动收货确认
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -30);
        String enddate = getDate_Str(calendar.getTime());

        Rows billRows = dbConnect.runSqlQuery("select t1.st_stockbillid,count(t2.sku) mcodeqty from st_stockbill t1 \n" +
                "inner join st_stockbill_items_sku t2 on t1.st_stockbillid=t2.st_stockbillid\n" +
                "inner join sa_agents t3 on t1.sys_enterpriseid=t3.sys_enterpriseid and t3.type='普通'\n" +
                "where t1.siteid='MD' and t1.type='销售出库'and t1.rb = 1 and t1.isreceiver = 0 and t1.status = '审核' and t1.iswx=0 and t1.checkdate>='2022-01-01' and t1.checkdate<'" + enddate + "' \n" +
                "group by t1.st_stockbillid\n" +
                "order by t1.st_stockbillid desc\n" +
                "limit 100");

        QuerySQL itemQuery = SQLFactory.createQuerySQL(dbConnect, "st_stockbill_items", "st_stockbillid");
        itemQuery.addQueryFields("itemqty", "sum(qty)");
        itemQuery.addGroupBy("st_stockbillid");
        itemQuery.setSiteid("MD").setWhere("skucontrol", true).setWhere("st_stockbillid", billRows.toArrayList("st_stockbillid"));
        RowsMap itemRowsMap = itemQuery.query().toRowsMap("st_stockbillid");

        for (Row billRow : billRows) {
            long st_stockbillid = billRow.getLong("st_stockbillid");
            if (itemRowsMap.containsKey(String.valueOf(st_stockbillid)) && itemRowsMap.get(String.valueOf(st_stockbillid)).get(0).getInteger("itemqty") == billRow.getInteger("mcodeqty")) {
                oneAction(st_stockbillid);
            }
        }
    }

    /**
     * 指定单据进行收货确认
     *
     * @param st_stockbillid
     * @throws YosException
     */
    public void oneAction(long st_stockbillid) throws YosException {
        Rows billRows = new DBConnect().runSqlQuery("select * from st_stockbill where st_stockbillid=" + st_stockbillid);
        if (billRows.isEmpty()) {
            throw new YosException("未找到该出库单");
        }
        Row billRow = billRows.get(0);
        String billno = billRow.getString("billno");
        String remarks = billRow.getString("remarks");
        String checkdate = billRow.getString("checkdate");
        String agentnum = billRow.getRow("sa_agents", "sys_enterpriseid=:sys_enterpriseid").getString("agentnum");

        Rows detailRows = dbConnect.runSqlQuery("select itemno as fitemno,itemname as fitemname,model as fmodel,sum(qty)fqty from st_stockbill_items where st_stockbillid=" + st_stockbillid + " group by itemno,itemname,model");
        Rows codeRows = dbConnect.runSqlQuery("select t2.itemno as fitemno,t1.sku as fmachinecode from st_stockbill_items_sku t1 inner join plm_item t2 on t1.itemid=t2.itemid where st_stockbillid=" + st_stockbillid);

        JSONObject dataObject = new JSONObject();
        dataObject.put("icstockbillid", st_stockbillid);
        dataObject.put("fbillnum", billno);
        dataObject.put("fnotes", remarks);
        dataObject.put("fagentnum", agentnum);
        dataObject.put("checkdate", checkdate);
        dataObject.put("details", detailRows.toJsonArray());
        dataObject.put("machinecodes", codeRows.toJsonArray());

        String result = postCRM(dataObject.toJSONString(), "/ext/whicstockbill/confirmReceiveBill/1");
        JSONObject jsonObject = JSONObject.parseObject(result);
        if (jsonObject.getBooleanValue("success")) {
            logger.info("销售出库单" + billno + "自动收货成功");
        } else {
            logger.error("销售出库单" + billno + "自动收货失败；" + jsonObject);
        }
    }

    /**
     * crm到货确认反馈
     *
     * @param fagentnum
     * @param fcrmbillnum
     * @param icstockbillids
     * @return
     */
    public JSONObject confirmReceiveBill(String fagentnum, String fcrmbillnum, String[] icstockbillids) {
        JSONObject resultObject = new JSONObject();
        try {
            QuerySQL billQuery = SQLFactory.createQuerySQL(new DBConnect(), "st_stockbill");
            billQuery.setSiteid("MD").setWhere("st_stockbillid", icstockbillids);
            Rows billRows = billQuery.query();

            RowsMap billRowsMap = billRows.toRowsMap("st_stockbillid");

            Rows agentrows = new DBConnect().runSqlQuery("select sa_agentsid from sa_agents where agentnum='" + fagentnum + "' and siteid='MD'");
            if (agentrows.isEmpty()) {
                resultObject.put("code", 0);
                resultObject.put("status", "err");
                resultObject.put("msg", "找不到编号为" + fagentnum + "的经销商");
                return resultObject;
            }
            Rows stockrows = new DBConnect().runSqlQuery("select stockid from st_stock where stockno='00' and siteid='MD'");
            if (stockrows.isEmpty()) {
                resultObject.put("code", 0);
                resultObject.put("status", "err");
                resultObject.put("msg", "经销商仓00不存在");
                return resultObject;
            }

            SQLDump sqlDump = new SQLDump();
            for (String icstockbillid : icstockbillids) {
                if (!billRowsMap.containsKey(icstockbillid)) {
                    resultObject.put("code", 0);
                    resultObject.put("msg", "找不到ID为" + icstockbillid + "的销售出库单");
                    return resultObject;
                }
                Row billrow = billRowsMap.get(icstockbillid).get(0);
                if (billrow.getBoolean("isreceiver")) {
                    resultObject.put("code", 0);
                    resultObject.put("msg", "销售出库单" + billrow.getString("billno") + "已收货确认，不可重复确认");
                    return resultObject;
                }
                if (!billrow.getString("status").equals("审核")) {
                    resultObject.put("code", 0);
                    resultObject.put("msg", "销售出库单" + billrow.getString("billno") + "处于非审核状态，不可确认");
                    return resultObject;
                }

                QuerySQL itemQuery = SQLFactory.createQuerySQL(new DBConnect(), "st_stockbill_items", "st_stockbillid");
                itemQuery.addQueryFields("itemqty", "sum(qty)");
                itemQuery.setSiteid("MD").setWhere("skucontrol", true).setWhere("st_stockbillid", icstockbillid);

                int qty = new DBConnect().runSqlQuery("select sum(qty) as qty from st_stockbill_items where siteid='MD' and skucontrol=1 and st_stockbillid=" + icstockbillid).get(0).getInteger("qty");
                ArrayList<String> skuList = new DBConnect().runSqlQuery("select sku from st_stockbill_items_sku where siteid='MD' and st_stockbillid=" + icstockbillid).toArrayList("sku");
                if (qty != skuList.size()) {
                    resultObject.put("code", 0);
                    resultObject.put("msg", "销售出库单" + billrow.getString("billno") + ",序列号数量和商品数量不符");
                    return resultObject;
                }
                sqlDump.add(SQLFactory.createUpdateSQL(new DBConnect(), "st_stockbill").setValue("isreceiver", true).setDateValue("receiverdate").setValue("crmbillno", fcrmbillnum).setValue("receiverby", "crm").setSiteid("MD").setWhere("st_stockbillid", icstockbillid));
                sqlDump.add(SQLFactory.createUpdateSQL(new DBConnect(), "sa_itemsku").setValue("sa_agentsid", agentrows.get(0).getLong("sa_agentsid")).setValue("stockid", stockrows.get(0).getLong("stockid")).setSiteid("MD").setWhere("sku", skuList));
            }
            sqlDump.commit();
            resultObject.put("msg", "");
            resultObject.put("code", 1);
        } catch (Exception e) {
            resultObject.put("msg", e.getMessage());
            resultObject.put("code", 0);
        }
        return resultObject;
    }

    /**
     * crm查询未到货确认单
     *
     * @param fagentnum
     * @param fbegdate
     * @param fenddate
     * @param returnMaxCount
     * @return
     */
    public JSONArray getDRPReceiveBill(String fagentnum, String fbegdate, String fenddate, int returnMaxCount) {
        try {
            Rows billRows = dbConnect.runSqlQuery("select t1.st_stockbillid,count(t2.sku) mcodeqty from st_stockbill t1 \n" +
                    "inner join st_stockbill_items_sku t2 on t1.st_stockbillid=t2.st_stockbillid\n" +
                    "inner join sa_agents t3 on t1.sys_enterpriseid=t3.sys_enterpriseid and t3.type='普通' and t3.agentnum='" + fagentnum + "' \n" +
                    "where t1.siteid='MD' and t1.type='销售出库'and t1.rb = 1 and t1.isreceiver = 0 and t1.status = '审核' and t1.iswx=0 and t1.checkdate>='" + fbegdate + "' and t1.checkdate<='" + fenddate + "' \n" +
                    "group by t1.st_stockbillid\n" +
                    "order by t1.st_stockbillid\n" +
                    "limit " + returnMaxCount);

            QuerySQL itemQuery = SQLFactory.createQuerySQL(dbConnect, "st_stockbill_items", "st_stockbillid");
            itemQuery.addQueryFields("itemqty", "sum(qty)");
            itemQuery.addGroupBy("st_stockbillid");
            itemQuery.setSiteid("MD").setWhere("skucontrol", true).setWhere("st_stockbillid", billRows.toArrayList("st_stockbillid"));
            RowsMap itemRowsMap = itemQuery.query().toRowsMap("st_stockbillid");

            JSONArray array = new JSONArray();
            for (Row billRow : billRows) {
                long st_stockbillid = billRow.getLong("st_stockbillid");
                if (itemRowsMap.containsKey(String.valueOf(st_stockbillid)) && itemRowsMap.get(String.valueOf(st_stockbillid)).get(0).getInteger("itemqty") == billRow.getInteger("mcodeqty")) {

                    Row bill = new DBConnect().runSqlQuery("select * from st_stockbill where st_stockbillid=" + st_stockbillid).get(0);

                    String billno = bill.getString("billno");
                    String remarks = bill.getString("remarks");
                    String checkdate = bill.getString("checkdate");
                    String agentnum = bill.getRow("sa_agents", "sys_enterpriseid=:sys_enterpriseid").getString("agentnum");

                    Rows detailRows = dbConnect.runSqlQuery("select itemno as fitemno,itemname as fitemname,model as fmodel,sum(qty)fqty from st_stockbill_items where st_stockbillid=" + st_stockbillid + " group by itemno,itemname,model");
                    Rows codeRows = dbConnect.runSqlQuery("select t2.itemno as fitemno,t1.sku as fmachinecode from st_stockbill_items_sku t1 inner join plm_item t2 on t1.itemid=t2.itemid where st_stockbillid=" + st_stockbillid);

                    JSONObject dataObject = new JSONObject();
                    dataObject.put("icstockbillid", st_stockbillid);
                    dataObject.put("fbillnum", billno);
                    dataObject.put("fnotes", remarks);
                    dataObject.put("fagentnum", agentnum);
                    dataObject.put("checkdate", checkdate);
                    dataObject.put("details", detailRows.toJsonArray());
                    dataObject.put("machinecodes", codeRows.toJsonArray());
                    array.add(dataObject);
                }
            }
            return array;
        } catch (Exception e) {

        }
        return new JSONArray();
    }

    public static void main(String[] args) throws YosException {
        new icstockbill(LogManager.getLogger(YosLogger.class)).oneAction(2973233);
    }
}