CUCU2/src/custom/service/DispatchAutoCloseService.java

package service;

import beans.datacontrllog.DataContrlLog;
import beans.parameter.Parameter;
import beans.uploaderpdata.UploadDataToERP_HY;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.ServiceController;
import common.YosException;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import common.parameter.properties;
import org.apache.log4j.Logger;
import restcontroller.webmanage.executorService.Executor;
import utility.ERPDocking;
import utility.tools.WebRequest;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;


/**
 * 发货单自动关闭任务
 */
public class DispatchAutoCloseService extends ServiceController {

    private static Logger logger = Logger.getLogger(DispatchAutoCloseService.class);

    @Override

    public void serviceRun() throws YosException {
        //
        Properties yosProperties = properties.getYosProperties();
        if (yosProperties.getProperty("system.runmode.debug").equalsIgnoreCase("false")) {
            Rows rows = dbConnect.runSqlQuery("select * from sa_dispatch where status='复核' and siteid='CCYOSG'");
            Rows rowsdetail = dbConnect.runSqlQuery("select t1.* from sa_dispatch_items t1 inner join sa_dispatch t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid where t2.status='复核' and t1.siteid='CCYOSG'");
            RowsMap rowsMap = rowsdetail.toRowsMap("sa_dispatchid");
            RowsMap rowsMap1 = rowsdetail.toRowsMap("billno");
            ArrayList<String> sqlList = new ArrayList<>();
            ERPDocking erpDocking = new ERPDocking();
            JSONArray ErpSainvoices = erpDocking.queryErpSainvoices(10000, 1, new JSONArray(), "", "", "", false, false, true);
            JSONArray dispatchids = new JSONArray();
            JSONArray unclosedispatchids = new JSONArray();
            if (!ErpSainvoices.isEmpty()) {
                for (Object ErpSainvoice : ErpSainvoices) {
                    JSONObject erpSainvoice = (JSONObject) ErpSainvoice;
                    String billno = erpSainvoice.getString("finvonum");
                    long sa_dispatchid = 0;
                    if (rowsMap1.containsKey(billno)) {
                        if (rowsMap1.get(billno).isNotEmpty()) {
                            sa_dispatchid = rowsMap1.get(billno).get(0).getLong("sa_dispatchid");
                            dispatchids.add(sa_dispatchid);
                        } else {
                            logger.info("发货单【" + billno + "】不存在:");
                            continue;
                        }
                    } else {
                        logger.info("发货单【" + billno + "】不存在:");
                        continue;
                    }
                    JSONArray jsonArray = erpDocking.queryErpSainvoice(billno);

                    JSONObject object = new JSONObject();
                    JSONObject objectdetail = new JSONObject();
                    object.put("access_token", "basicDrp");
                    object.put("classname", "Sainvoice");
                    object.put("method", "closeSainvoice");
                    objectdetail.put("finvonum", billno);
                    objectdetail.put("isManual", false);
                    object.put("content", objectdetail);

                    HashMap<String, String> map = new HashMap<>();
                    map.put("content-Type", "application/json");
                    String result = new WebRequest().doPost(object.toString(), "http://124.71.196.182:8101/rest/ws_v2/basicDrp", map);
                    JSONObject resultobject = JSONObject.parseObject(result);

                    try {
                        Rows erpupdatelogRows = dbConnect.runSqlQuery(
                                "select * from sys_erpupdatelog where ownertable='sa_dispatch' and type='关闭发货单' and ownerid=" + sa_dispatchid);
                        SQLFactory sqlFactory;
                        if (!erpupdatelogRows.isEmpty()) {
                            sqlFactory = new SQLFactory(new UploadDataToERP_HY(), "erp上传日志更新");
                            sqlFactory.addParameter("sys_erpupdatelogid", erpupdatelogRows.get(0).getLong("sys_erpupdatelogid"));
                        } else {
                            sqlFactory = new SQLFactory(new UploadDataToERP_HY(), "erp上传日志新增");
                            sqlFactory.addParameter("sys_erpupdatelogid", createTableID("sys_erpupdatelog"));
                        }
                        sqlFactory.addParameter("siteid", "CCYOSG");
                        sqlFactory.addParameter("userid", 1);
                        sqlFactory.addParameter("username", "admin");
                        sqlFactory.addParameter("ownerid", sa_dispatchid);
                        sqlFactory.addParameter("ownertable", "sa_dispatch");
                        sqlFactory.addParameter("type", "关闭发货单");
                        sqlFactory.addParameter("request", object.toString());
                        sqlFactory.addParameter("response", resultobject.toJSONString());

                        if ("0".equals(resultobject.getString("errcode"))) {
                            sqlFactory.addParameter("erpbillno", billno);
                            sqlFactory.addParameter("errmsg", "");
                        } else {
                            sqlFactory.addParameter("erpbillno", "");
                            sqlFactory.addParameter("errmsg", resultobject.getString("errmsg"));
                        }
                        sqlFactory.addParameter("status", resultobject.getString("errcode"));
                        sqlList.add(sqlFactory.getSQL());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    if (resultobject.getString("errcode").equals("0")) {
                        sqlList.add("update sa_dispatch set status='关闭',closedate=current_time,closeby='admin' where sa_dispatchid='" + sa_dispatchid + "' and siteid='CCYOSG'");
                        SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                        sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                        sqlFactory.addParameter("ownertable", "sa_dispatch");
                        sqlFactory.addParameter("ownerid", sa_dispatchid);
                        sqlFactory.addParameter("action", "自动关闭");
                        sqlFactory.addParameter("remarks", "发货单自动关闭成功");
                        sqlFactory.addParameter("actionuserid", "1");
                        sqlFactory.addParameter("actionby", "admin");
                        sqlFactory.addParameter("siteid", "CCYOSG");
                        sqlList.add(sqlFactory.getSQL());
                    } else {
                        unclosedispatchids.add(sa_dispatchid);
                        logger.info("发货单自动关闭错误:" + resultobject.getString("errmsg") + "发货单id:" + sa_dispatchid);
                    }
                    if (!jsonArray.isEmpty()) {
                        for (Object object1 : jsonArray) {
                            JSONObject jsonObject = (JSONObject) object1;
                            sqlList.add("update sa_dispatch_items set outwarehousedate=current_time,outwarehouseqty=" + jsonObject.getBigDecimalValue("fqty1") + " where rowno =" + jsonObject.getIntValue("frownum") + " and sa_dispatchid='" + sa_dispatchid + "' and siteid='CCYOSG'");
                        }
                    }


                }
            }
            dbConnect.runSqlUpdate(sqlList);


            //判断当前单据是否已实际发完货，如果发完，则生把订单变为关闭状态
            Rows orderRows = new Rows();
            if (dispatchids.size() > 0) {
                String sql = "select distinct t1.sa_orderid from sa_orderitems t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='CCYOSG' and t2.sa_dispatchid in" + dispatchids.toJSONString();
                sql = sql.replace("[", "(").replace("]", ")");
                logger.info("sql:" + sql);
                orderRows = dbConnect.runSqlQuery(sql);
            }
            //总数量
            Rows totalRows = dbConnect.runSqlQuery("select count(1) count,sa_orderid from sa_orderitems where siteid='CCYOSG' group by sa_orderid");
            RowsMap totalRowsMap = totalRows.toRowsMap("sa_orderid");
            //已发完数量
            Rows deliRows = dbConnect.runSqlQuery("select count(1) count,sa_orderid from sa_orderitems t1 inner join (SELECT  t1.sa_orderitemsid,t1.siteid,SUM(ifnull(t1.outwarehouseqty,0)) outwarehouseqty FROM sa_dispatch_items t1 WHERE t1.siteid='CCYOSG' group by t1.sa_orderitemsid,t1.siteid) t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.qty=t2.outwarehouseqty and t1.siteid='CCYOSG' group by sa_orderid");
            RowsMap deliRowsMap = deliRows.toRowsMap("sa_orderid");
            if (orderRows.size() > 0) {
                for (Row row : orderRows) {
                    logger.info("订单id:" + row.getString("sa_orderid"));
                    if (totalRowsMap.containsKey(row.getString("sa_orderid")) && deliRowsMap.containsKey(row.getString("sa_orderid"))) {
                        if (totalRowsMap.get(row.getString("sa_orderid")).get(0).getLong("count") == deliRowsMap.get(row.getString("sa_orderid")).get(0).getLong("count")) {
                            logger.info("订单关闭:update sa_order set status='关闭',closedate=current_time,closeby='admin' where sa_orderid=" + row.getLong("sa_orderid") + " and siteid='CCYOSG'");
                            dbConnect.runSqlUpdate("update sa_order set status='关闭',closedate=current_time,closeby='admin' where sa_orderid=" + row.getLong("sa_orderid") + " and siteid='CCYOSG'");
                            SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                            sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                            sqlFactory.addParameter("ownertable", "sa_order");
                            sqlFactory.addParameter("ownerid", row.getLong("sa_orderid"));
                            sqlFactory.addParameter("action", "自动关闭");
                            sqlFactory.addParameter("remarks", "订单数量发完自动关闭");
                            sqlFactory.addParameter("actionuserid", "1");
                            sqlFactory.addParameter("actionby", "admin");
                            sqlFactory.addParameter("siteid", "CCYOSG");
                            dbConnect.runSqlUpdate(sqlFactory.getSQL());
                        }
                    }
                }
            }

            //自动关闭erp发货单接口不成功，则手动关闭e订货的发货单（但状态为关闭）
            ArrayList<String> uncloseSqlList = new ArrayList<>();
            if (unclosedispatchids.size() > 0) {
                for (Object object : unclosedispatchids) {
                    Long sa_dispatchid = Long.valueOf(object.toString());
                    String billno = "";
                    Rows dispatchRows = dbConnect.runSqlQuery("select * from sa_dispatch where sa_dispatchid=" + sa_dispatchid + " and siteid='CCYOSG'");
                    if (!dispatchRows.isEmpty()) {
                        billno = dispatchRows.get(0).getString("billno");
                    } else {
                        logger.info("发货单" + sa_dispatchid + "不存在:");
                        continue;
                    }
                    //手工关闭发货单
                    JSONObject jsonobject = new JSONObject();
                    JSONObject objectdetail = new JSONObject();
                    jsonobject.put("access_token", "basicDrp");
                    jsonobject.put("classname", "Sainvoice");
                    jsonobject.put("method", "closeSainvoice");
                    objectdetail.put("finvonum", billno);
                    objectdetail.put("isManual", true);
                    jsonobject.put("content", objectdetail);

                    HashMap<String, String> map = new HashMap<>();
                    map.put("content-Type", "application/json");
                    String result = new WebRequest().doPost(jsonobject.toString(), "http://124.71.196.182:8101/rest/ws_v2/basicDrp", map);
                    JSONObject resultobject = JSONObject.parseObject(result);
                    if (resultobject.getString("errcode").equals("0")) {
                        Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from sa_dispatch_items where sa_dispatchid=" + sa_dispatchid + ")");
                        RowsMap rowsOrderDetailMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
                        Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,sum(t1.outwarehouseqty) outwarehouseqty,t1.sa_orderitemsid,t1.siteid from sa_dispatch_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where ifnull(t1.isclose,0)=0 and t1.sa_dispatchid=" + sa_dispatchid + " group by t1.sa_orderitemsid,t1.siteid");
                        if (!rowsDispatchDetailGroup.isEmpty()) {
                            for (Row row : rowsDispatchDetailGroup) {
                                logger.info("测试bug：" + "update sa_orderitems set undeliqty=" + rowsOrderDetailMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + ",deliedqty=" + rowsOrderDetailMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                                uncloseSqlList.add("update sa_orderitems set undeliqty=" + rowsOrderDetailMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + ",deliedqty=" + rowsOrderDetailMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                            }
                        }
//                    Rows orderRows =dbConnect.runSqlQuery("select distinct t1.sa_orderid from sa_orderitems t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='" + siteid + "' and t2.sa_dispatchid=" + sa_dispatchid);
//                    for (Row row:orderRows) {
//                        sqlList.add("update sa_order set status='审核' where sa_orderid=" + row.getLong("sa_orderid") + " and siteid='" + siteid + "'");
//                    }
                        uncloseSqlList.add("update sa_dispatch set status='关闭',closedate=current_time,closeby='admin' where sa_dispatchid='" + sa_dispatchid + "' and siteid='CCYOSG'");
                        SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                        sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                        sqlFactory.addParameter("ownertable", "sa_dispatch");
                        sqlFactory.addParameter("ownerid", sa_dispatchid);
                        sqlFactory.addParameter("action", "自动关闭");
                        sqlFactory.addParameter("remarks", "发货单自动关闭成功(未全部发完,未发完数量退回订单并自动关闭)");
                        sqlFactory.addParameter("actionuserid", "1");
                        sqlFactory.addParameter("actionby", "admin");
                        sqlFactory.addParameter("siteid", "CCYOSG");
                        uncloseSqlList.add(sqlFactory.getSQL());
                    } else {
                        logger.info("发货单手工关闭错误:" + resultobject.getString("errmsg") + "发货单id:" + sa_dispatchid);
                        Rows erpupdatelogRows = dbConnect.runSqlQuery(
                                "select * from sys_erpupdatelog where ownertable='sa_dispatch' and type='手工关闭发货单' and ownerid=" + sa_dispatchid);
                        SQLFactory sqlFactory;
                        if (!erpupdatelogRows.isEmpty()) {
                            sqlFactory = new SQLFactory(new UploadDataToERP_HY(), "erp上传日志更新");
                            sqlFactory.addParameter("sys_erpupdatelogid", erpupdatelogRows.get(0).getLong("sys_erpupdatelogid"));
                        } else {
                            sqlFactory = new SQLFactory(new UploadDataToERP_HY(), "erp上传日志新增");
                            sqlFactory.addParameter("sys_erpupdatelogid", createTableID("sys_erpupdatelog"));
                        }
                        sqlFactory.addParameter("siteid", "CCYOSG");
                        sqlFactory.addParameter("userid", 1);
                        sqlFactory.addParameter("username", "admin");
                        sqlFactory.addParameter("ownerid", sa_dispatchid);
                        sqlFactory.addParameter("ownertable", "sa_dispatch");
                        sqlFactory.addParameter("type", "手工关闭发货单");
                        sqlFactory.addParameter("request", object.toString());
                        sqlFactory.addParameter("response", resultobject.toJSONString());
                        sqlFactory.addParameter("erpbillno", "");
                        sqlFactory.addParameter("errmsg", resultobject.getString("errmsg"));
                        sqlFactory.addParameter("status", resultobject.getString("errcode"));
                        uncloseSqlList.add(sqlFactory.getSQL());
                    }


                }
                dbConnect.runSqlUpdate(uncloseSqlList);
            }
            for (Object o : dispatchids) {
                Rows rows1 = dbConnect.runSqlQuery("select t2.userid from sa_dispatch t1 inner join sys_enterprise_hr t2 on t1.siteid=t2.siteid and t1.sys_enterpriseid=t2.sys_enterpriseid where sa_dispatchid=" + o);
                HashMap<String, Object> dataextra = new HashMap<>();
                dataextra.put("extraUseridList", rows1.toArrayList("userid"));
                Executor.sendEml(this, "dispatch_close", (Long) o, "CCYOSG", dataextra);
            }

        }
    }


}