package com.sapdev.ws.sps;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;
import javax.transaction.UserTransaction;
import org.apache.commons.lang.StringUtils;
import com.google.gson.Gson;
import com.oracle.xmlns.pcbpel.adapter.sap.sap_sbapp.productionprocess.zpcd4mesadapter.Zpcd4MesAdapterPT;
import com.oracle.xmlns.pcbpel.adapter.sap.sap_sbapp.productionprocess.zpcd4mesadapter.Zpcd4MesAdapterPTBindingQSService;
import com.sap.document.sap.rfc.functions.ZFUNZPCD4MES;
import com.sap.document.sap.rfc.functions.ZFUNZPCD4MESRESPONSE;
import com.sap.document.sap.rfc.functions.ZSPCD;
import com.sap.engine.services.webservices.espbase.configuration.ann.dt.AuthenticationDT;
import com.sap.engine.services.webservices.espbase.configuration.ann.dt.AuthenticationEnumsAuthenticationLevel;
import com.sap.engine.services.webservices.espbase.configuration.ann.rt.AuthenticationRT;
import com.sap.me.extension.Services;
import com.sap.me.frame.Data;
import com.sap.me.frame.SystemBase;
import com.sap.me.frame.Utils;
import com.sap.me.frame.domain.BusinessException;
import com.sap.me.frame.jdbc.DynamicQuery;
import com.sap.me.frame.jdbc.DynamicQueryFactory;
import com.sapdev.service.DataCoreServiceInterface;
import com.sapdev.utils.CommonUtils;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
*
* @author HEXIAO.CHEN
* @author weifeng.sun
* @Description: 167 配车单信息WebService功能1.0 HEXIAO.CHEN 2019-05-13
* @date 2019年5月13日 下午3:13:36
*/
@AuthenticationDT(authenticationLevel = AuthenticationEnumsAuthenticationLevel.NONE)
@AuthenticationRT(AuthenticationMethod = "sapsp:HTTPBasic")
@WebService
public class OrderINFOService {
private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
/**
*
* @Description: 获取配车单信息
* @param order
* @return
* @throws Exception
*/
@WebMethod
public String getOrderInfos(@WebParam(name = "requestParam") String requestParam) throws Exception {
DataCoreServiceInterface dataCoreService = Services.getService("com.sapdev.service", "DataCoreService", "1000");
Map<String, Object> resultMap = new HashMap<String, Object>();
JSONObject jsonObject = JSONObject.fromObject(requestParam);
Map<String, Object> map = (Map<String, Object>) JSONObject.toBean(jsonObject, Map.class);
String listOrder = map.get("LIST_ORDER").toString();
listOrder=listOrder.substring(0,10);
String itemCode = map.get("ITEM_CODE").toString();
String[] itemCodes = itemCode.split("/");
String item = "";
String vendor = "";
// 校验传进来的物料条形码是否符合规定
if (itemCodes.length > 0) {
for (int i = 0; i < itemCodes.length; i++) {
if (i == 1) {
item = itemCodes[i];
}
if (i == 2) {
try {
vendor = itemCodes[i].substring(0, 3);
} catch (Exception e) {
resultMap.put("CODE", 0);
resultMap.put("MESSAGE", "物料条形码有误");
return new Gson().toJson(resultMap);
}
}
}
}
if (item.equals("") || vendor.equals("")) {
resultMap.put("CODE", 0);
resultMap.put("MESSAGE", "物料条形码有误");
return new Gson().toJson(resultMap);
}
UserTransaction userTransaction = CommonUtils.startTransaction();
try {
// 判断配车单在表Z_ORDER_LIST是否存在
SystemBase systemBase = SystemBase.createDefaultSystemBase();
DynamicQuery dynamicQuery = DynamicQueryFactory.newInstance();
dynamicQuery.append(" SELECT ZOL.LIST_ORDER FROM WIP.Z_ORDER_LIST ZOL WHERE ZOL.LIST_ORDER = ").appendString(listOrder);
Data data = systemBase.executeQuery(dynamicQuery);
if (Utils.isEmpty(data)) {
Zpcd4MesAdapterPTBindingQSService zpcd4MesAdapterPTBindingQSService = new Zpcd4MesAdapterPTBindingQSService();
Zpcd4MesAdapterPT zpcd4MesAdapterPT = zpcd4MesAdapterPTBindingQSService.getZpcd4MesAdapterPTBindingQSPort();
ZFUNZPCD4MES ZFUNZPCD4MES = new ZFUNZPCD4MES();
ZFUNZPCD4MES.setEBELN(listOrder);
ZFUNZPCD4MESRESPONSE ZFUNZPCD4MESRESPONSE = zpcd4MesAdapterPT.zfunZPCD4MES(ZFUNZPCD4MES);
List<ZSPCD> erpDataList = ZFUNZPCD4MESRESPONSE.getTOUT().getItem();
for (int i = 0; i < erpDataList.size(); i++) {
String line = erpDataList.get(i).getZZDISPO().equals("") ? "#" : erpDataList.get(i).getZZDISPO();
String operation = erpDataList.get(i).getZZVORNR();
String items = erpDataList.get(i).getMATNR();
String shopOrder = erpDataList.get(i).getAUFNR();
dynamicQuery.clear();
dynamicQuery.append(" SELECT CF.VALUE LINE FROM WIP.SHOP_ORDER SO JOIN WIP.CUSTOM_FIELDS CF ON CF.HANDLE = SO.HANDLE AND CF.\"ATTRIBUTE\" = 'O_PLINE' ");
dynamicQuery.append(" WHERE SO.SHOP_ORDER = ").appendString(shopOrder);
Data lineData = systemBase.executeQuery(dynamicQuery);
if (!Utils.isEmpty(lineData)) {
operation = lineData.getString("LINE", "", 0) + "-OP" + operation;
}
String itemDes = erpDataList.get(i).getMAKTX();
if (items.length() > 8) {
items = items.substring(items.length() - 8);
}
String orderOty = String.valueOf(erpDataList.get(i).getMENGE().intValue());
String vendors = erpDataList.get(i).getLIFNR();
String itemQty = erpDataList.get(i).getBSTMG().substring(0, 1);
String lacation = erpDataList.get(i).getLGPBE();
String sprayPaint = erpDataList.get(i).getZPAINT();
String assemb = erpDataList.get(i).getZSZBS();
String handle = "ORDERBO:" + erpDataList.get(i).getEBELN() + "," + line;
if (i == 0) {
// 更新Z_ORDER_LIST表数据
dynamicQuery.clear();
dynamicQuery.append("INSERT INTO WIP.Z_ORDER_LIST (HANDLE, LIST_ORDER, LINE) VALUES(").appendString(handle).append(",");
dynamicQuery.appendString(listOrder).append(",").appendString(line).append(")");
systemBase.executeUpdate(dynamicQuery);
}
// 更新Z_ITEM_ORDER_LIST表数据
dynamicQuery.clear();
dynamicQuery.append(" INSERT INTO WIP.Z_ITEM_ORDER_LIST (HANDLE, ORDER_BO, OPERATION, ITEM, SHOPORDER, ITEM_DESCRIPTION, ITEM_QTY, ORDER_QTY");
if (!Utils.isBlank(vendors)) {
dynamicQuery.append(", VENDOR");
}
if (!Utils.isBlank(lacation)) {
dynamicQuery.append(", LOCATION");
}
if (!Utils.isBlank(sprayPaint)) {
dynamicQuery.append(", ZPAINT");
}
if (!Utils.isBlank(assemb)) {
dynamicQuery.append(", ZSZBS");
}
dynamicQuery.append(")");
dynamicQuery.append(" VALUES(").appendString(dateFormat.format(new Date()) + String.valueOf((int) (Math.random() * 10000))).append(",");
dynamicQuery.appendString(handle).append(",");
dynamicQuery.appendString(operation).append(",");
dynamicQuery.appendString(items).append(",");
dynamicQuery.appendString(shopOrder).append(",");
dynamicQuery.appendString(itemDes).append(",");
dynamicQuery.appendString(itemQty).append(",");
dynamicQuery.appendString(orderOty);
if (!Utils.isBlank(vendors)) {
dynamicQuery.append(",").appendString(vendors);
}
if (!Utils.isBlank(lacation)) {
dynamicQuery.append(",").appendString(lacation);
}
if (!Utils.isBlank(sprayPaint)) {
dynamicQuery.append(",").appendString(sprayPaint);
}
if (!Utils.isBlank(assemb)) {
dynamicQuery.append(",").appendString(assemb);
}
dynamicQuery.append(")");
systemBase.executeUpdate(dynamicQuery);
}
}
CommonUtils.commitTransaction(userTransaction);
} catch (Exception e) {
CommonUtils.rollBackTransaction(userTransaction);
resultMap.put("CODE", 0);
resultMap.put("MESSAGE", e.getMessage());
return new Gson().toJson(resultMap);
}
// 获取配车单信息
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(" SELECT ZTOL.OPERATION, ZTOL.ITEM, ZTOL.SHOPORDER, ZTOL.ITEM_DESCRIPTION, ");
stringBuffer.append(" ZTOL.ITEM_QTY, ZTOL.ORDER_QTY, ZTOL.VENDOR, ZTOL.LOCATION, ");
stringBuffer.append(" ZSEI.ORD_SEQUENCE,TO_CHAR(ZSEI.GEN_DATE_TIME) GEN_DATE_TIME,ZSEI.QUANTITY_OF_PROD,CF.VALUE STATE_MACHINE,ZTOL.ZPAINT,ZTOL.ZSZBS,ZTOL.INGREDIENTS ");
stringBuffer.append(" FROM WIP.Z_ORDER_LIST ZOL ");
stringBuffer.append(" LEFT JOIN WIP.Z_ITEM_ORDER_LIST ZTOL ON ZOL.HANDLE = ZTOL.ORDER_BO ");
stringBuffer.append(" LEFT JOIN WIP.Z_SO_EXTEND_INFO ZSEI ON ZTOL.SHOPORDER = ZSEI.SHOP_ORDER ");
stringBuffer.append(" LEFT JOIN WIP.SHOP_ORDER SO ON SO.SHOP_ORDER = ZTOL.SHOPORDER AND SO.SITE = '1000' ");
stringBuffer.append(" LEFT JOIN WIP.ITEM I ON I.ITEM = SUBSTR_BEFORE(SUBSTR_AFTER(SO.PLANNED_ITEM_BO, ','), ',') AND I.SITE = '1000' ");
stringBuffer.append(" LEFT JOIN WIP.CUSTOM_FIELDS CF ON CF.HANDLE = I.HANDLE AND CF.\"ATTRIBUTE\" = 'STATE_MACHINE' ");
stringBuffer.append(" WHERE ZOL.LIST_ORDER = '").append(listOrder).append("'").append(" AND ZTOL.ITEM = '").append(item).append("'");
List<Map<String, Object>> orderInfos = dataCoreService.getResultByTSQL(stringBuffer.toString(), new HashMap<String, Object>());
List<Map<String, Object>> orderInfosWithVendor = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> orderInfosWithoutVendor = new ArrayList<Map<String, Object>>();
boolean vendorIsNull = false;
if (orderInfos != null && orderInfos.size() > 0) {
for (Map<String, Object> orderMap : orderInfos) {
if (orderMap.get("VENDOR") != null && !orderMap.get("VENDOR").toString().equals("")) {
vendorIsNull = true;
if (orderMap.get("VENDOR").toString().equals(vendor)) {
orderInfosWithVendor.add(orderMap);
}
} else {
orderInfosWithoutVendor.add(orderMap);
}
}
if (vendorIsNull) {
if (orderInfosWithVendor != null && orderInfosWithVendor.size() > 0) {
resultMap.put("INFO", orderInfosWithVendor);
resultMap.put("CODE", 0);
} else if (orderInfosWithoutVendor != null && orderInfosWithoutVendor.size() > 0) {
resultMap.put("INFO", orderInfosWithoutVendor);
resultMap.put("CODE", 2);
} else {
resultMap.put("INFO", new ArrayList<Map<String, Object>>());
resultMap.put("CODE", 1);
}
} else {
resultMap.put("INFO", orderInfos);
resultMap.put("CODE", 0);
}
} else {
resultMap.put("CODE", 3);
}
return new Gson().toJson(resultMap);
}
/**
*
* @Description: 获取物料描述信息
* @param itemRef
* @return
* @throws Exception
*/
@WebMethod
public String getItemInfos(@WebParam(name = "itemRef") String itemRef) throws Exception {
DataCoreServiceInterface dataCoreService = Services.getService("com.sapdev.service", "DataCoreService", "1000");
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(" SELECT I.DESCRIPTION ");
stringBuffer.append(" FROM WIP.ITEM I WHERE I.CURRENT_REVISION = 'true' AND I.SITE = '1000' AND I.ITEM = '" + itemRef + "'");
List<Map<String, Object>> itemInfos = dataCoreService.getResultByTSQL(stringBuffer.toString(), new HashMap<String, Object>());
JSONObject jsonObject = new JSONObject();
if (!itemInfos.isEmpty()) {
jsonObject.put("ITEM_DESCRIPTION", itemInfos.get(0).get("DESCRIPTION"));
} else {
jsonObject.put("ITEM_DESCRIPTION", "");
}
return jsonObject.toString();
}
/**
*
* @Description: 检验物料是否在配车单中，并修改配车单状态
* @param requestParam
* @return
* @throws Exception
*/
@WebMethod
public String lackItemMark(@WebParam(name = "requestParam") String requestParam) throws Exception {
Map<String, Object> resultMap = new HashMap<String, Object>();
UserTransaction userTransaction = CommonUtils.startTransaction();
try {
JSONArray jsonArray = JSONArray.fromObject(requestParam);
List<Map<String, Object>> list = (List<Map<String, Object>>) jsonArray.toList(jsonArray);
for (int i = 0; i < list.size(); i++) {
JSONObject jso = JSONObject.fromObject(list.get(i));
Map<String, Object> map = (Map<String, Object>) JSONObject.toBean(jso, Map.class);
String item = map.get("ITEM").toString();
String listOrder = map.get("ORDER").toString();
String lackQty = map.get("LACK_QTY").toString();
String createUser = map.get("USER").toString();
SystemBase systemBase = SystemBase.createDefaultSystemBase();
DynamicQuery dynamicQuery = DynamicQueryFactory.newInstance();
Data orderListData = getOrderListByOrder(listOrder, dynamicQuery, systemBase);
String orderListHandle = orderListData.getString("HANDLE", 0);
Data itemOrderListData = getItemOrderListByOrderBOAndItem(orderListHandle, item, dynamicQuery, systemBase);
updateOrderListWhereListOrderEqual(listOrder, dynamicQuery, systemBase);
updateItemOrderListWhereListOrderAndItemEqual(listOrder, item, dynamicQuery, systemBase);
writeOrderLackLog(orderListHandle, itemOrderListData, item, lackQty, createUser, dynamicQuery, systemBase);
}
CommonUtils.commitTransaction(userTransaction);
resultMap.put("CODE", 0);
resultMap.put("MESSAGE", "缺料信息记录成功！");
resultMap.put("DATA", "");
} catch (BusinessException be) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", StringUtils.isEmpty(be.getMessage()) ? be.toString() : be.getMessage());
resultMap.put("DATA", "");
} catch (Exception e) {
CommonUtils.rollBackTransaction(userTransaction);
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "缺料信息记录失败，请检查当前设备网络状态或联系管理员处理！");
resultMap.put("DATA", "");
}
return new Gson().toJson(resultMap);
}
/**
* @description: 根据listOrder获取Z_ORDER_LIST的handle值
* @param listOrder
* @param dynamicQuery
* @param systemBase
* @return
* @throws BusinessException
*/
private Data getOrderListByOrder(String listOrder, DynamicQuery dynamicQuery, SystemBase systemBase) throws BusinessException {
dynamicQuery.clear();
dynamicQuery.append(" SELECT ZOL.HANDLE FROM WIP.Z_ORDER_LIST ZOL WHERE ZOL.LIST_ORDER = ").appendString(listOrder);
Data orderListData = systemBase.executeQuery(dynamicQuery);
if (Utils.isEmpty(orderListData)) {
// 21103.simple = 无法找到满足条件的配车单或物料！
throw new BusinessException(21103);
}
dynamicQuery.flush();
return orderListData;
}
/**
* @description: 根据OrderBO和item 获取Z_ITEM_ORDER_LIST中数据
* @param orderListHandle
* @param item
* @param dynamicQuery
* @param systemBase
* @return
* @throws BusinessException
*/
private Data getItemOrderListByOrderBOAndItem(String orderListHandle, String item, DynamicQuery dynamicQuery, SystemBase systemBase) throws BusinessException {
dynamicQuery.clear();
dynamicQuery.append(" SELECT ZIOL.OPERATION,ZIOL.ORDER_QTY FROM WIP.Z_ITEM_ORDER_LIST ZIOL ");
dynamicQuery.append(" WHERE ZIOL.ORDER_BO = ").appendString(orderListHandle).append(" AND ZIOL.ITEM = ").appendString(item);
Data itemOrderListData = systemBase.executeQuery(dynamicQuery);
if (Utils.isEmpty(itemOrderListData)) {
// 21103.simple = 无法找到满足条件的配车单或物料！
throw new BusinessException(21103);
}
dynamicQuery.flush();
return itemOrderListData;
}
/**
* @description: 更新Z_ORDER_LIST的STATUS值
* @param listOrder
* @param dynamicQuery
* @param systemBase
*/
private void updateOrderListWhereListOrderEqual(String listOrder, DynamicQuery dynamicQuery, SystemBase systemBase) {
dynamicQuery.clear();
dynamicQuery.append("UPDATE WIP.Z_ORDER_LIST ZOL SET STATUS = '1' WHERE ZOL.LIST_ORDER = ").appendString(listOrder);
systemBase.executeUpdate(dynamicQuery);
}
/**
* @description: 更新Z_ITEM_ORDER_LIST的STATUS值
* @param listOrder
* @param dynamicQuery
* @param systemBase
*/
private void updateItemOrderListWhereListOrderAndItemEqual(String listOrder, String item, DynamicQuery dynamicQuery, SystemBase systemBase) {
dynamicQuery.clear();
dynamicQuery.append("UPDATE WIP.Z_ITEM_ORDER_LIST ZIOL SET STATUS='1' WHERE SUBSTR_BEFORE(SUBSTR_AFTER(ZIOL.ORDER_BO, ':'), ',') = ").appendString(listOrder);
dynamicQuery.append(" AND ZIOL.ITEM = ").appendString(item);
systemBase.executeUpdate(dynamicQuery);
}
/**
* @description: 写日志信息
* @param orderListHandle
* @param itemOrderListData
* @param item
* @param lackQty
* @param dynamicQuery
* @param systemBase
*/
private void writeOrderLackLog(String orderListHandle, Data itemOrderListData, String item, String lackQty, String createUser, DynamicQuery dynamicQuery, SystemBase systemBase) {
long date = new Date().getTime();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String itemDescription = getItemDescriptionByItem(item, dynamicQuery, systemBase);
int itemQty = computerItemQTY(itemOrderListData);
dynamicQuery.clear();
dynamicQuery.append(" INSERT INTO WIP.Z_ORDER_LACK_LOG ");
dynamicQuery.append(" (HANDLE,ORDER_BO,OPERATION,ITEM,ITEM_DESCRIPTION,ITEM_QTY,LACK_QTY,CREATED_USER,CREATED_DATE_TIME,SERIAL_NUM) ");
dynamicQuery.append(" VALUES( ").appendString("OrderLackLogBO:" + orderListHandle + "," + item + "," + date);
dynamicQuery.append(",").appendString(orderListHandle);
dynamicQuery.append(",").appendString(itemOrderListData.getString("OPERATION", 0));
dynamicQuery.append(",").appendString(item);
dynamicQuery.append(",").appendString(itemDescription);
dynamicQuery.append(",").appendString(itemQty + "");
dynamicQuery.append(",").appendString(lackQty);
dynamicQuery.append(",").appendString(createUser);
dynamicQuery.append(",").appendString(simpleDateFormat.format(new Date()));
dynamicQuery.append(",").appendString("" + date);
dynamicQuery.append(" ) ");
systemBase.executeUpdate(dynamicQuery);
}
/**
* @description: 获取item的Description
* @param item
* @param dynamicQuery
* @param systemBase
* @return
*/
private String getItemDescriptionByItem(String item, DynamicQuery dynamicQuery, SystemBase systemBase) {
dynamicQuery.clear();
dynamicQuery.append(" SELECT it.DESCRIPTION FROM WIP.ITEM it WHERE UPPER(it.CURRENT_REVISION) = 'TRUE' ");
dynamicQuery.append("AND it.ITEM = ").appendString(item);
Data itemOrderListData = systemBase.executeQuery(dynamicQuery);
dynamicQuery.flush();
String itemDescription = itemOrderListData.getString("DESCRIPTION", 0);
return itemDescription;
}
/**
* @description: 计算ItemQTY
* @param itemOrderListData
* @return
*/
private int computerItemQTY(Data itemOrderListData) {
int sum = 0;
for (int i = 0; i < itemOrderListData.size(); i++) {
int qty = itemOrderListData.getInteger("ORDER_QTY", 0);
sum += qty;
}
return sum;
}
@WebMethod
public String newgetOrderInfos(@WebParam(name = "requestParam") String requestParam) throws Exception {
Map<String, Object> resultMap = new HashMap<String, Object>();
// string转json对象
JSONObject jsonObject = JSONObject.fromObject(requestParam);
// jason对象转map
Map<String, Object> map = (Map<String, Object>) JSONObject.toBean(jsonObject, Map.class);
SystemBase systemBase = SystemBase.createDefaultSystemBase();
DynamicQuery dynamicQuery = DynamicQueryFactory.newInstance();
String listOrder = map.get("LIST_ORDER") == null ? "" : map.get("LIST_ORDER").toString();
listOrder=listOrder.substring(0,10);
String itemCode = map.get("ITEM_CODE") == null ? "" : map.get("ITEM_CODE").toString();
String item = "";
try {
item = itemCode.split("/")[1];
} catch (Exception e) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "外包装条码格式不正确，请检查扫描的条码");
return new Gson().toJson(resultMap);
}
try {
if (Utils.isBlank(listOrder)) {
dynamicQuery.clear();
dynamicQuery.append(" WITH TABLE_A AS (SELECT A1.ORDER_BO,MAX(A1.CREATED_DATE_TIME) DT FROM WIP.Z_ORDER_LACK_LOG A1 WHERE A1.ITEM = ").appendString(item).append(" GROUP BY A1.ORDER_BO)");
dynamicQuery.append(" SELECT A2.*,A3.LACK_QTY FROM TABLE_A A2 ");
dynamicQuery.append(" JOIN WIP.Z_ORDER_LACK_LOG A3 ON A2.ORDER_BO = A3.ORDER_BO AND A2.DT = A3.CREATED_DATE_TIME ORDER BY DT DESC ");
Data orderBoData = systemBase.executeQuery(dynamicQuery);
if (Utils.isEmpty(orderBoData)) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "此物料没有缺料标记或已打印完毕，请检查");
return new Gson().toJson(resultMap);
} else {
String orderBo = "";
for (int i = 0; i < orderBoData.size(); i++) {
if (!"0".equals(orderBoData.getString("LACK_QTY", "", i))) {
orderBo = orderBoData.getString("ORDER_BO", "", i);
break;
}
}
if (Utils.isBlank(orderBo)) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "此物料没有缺料标记或已打印完毕，请检查");
return new Gson().toJson(resultMap);
}
dynamicQuery.clear();
dynamicQuery.append("SELECT A1.LIST_ORDER FROM WIP.Z_ORDER_LIST A1 WHERE A1.HANDLE = ").appendString(orderBo);
listOrder = systemBase.executeQuery(dynamicQuery).getString("LIST_ORDER", "", 0);
}
}
} catch (Exception e) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", e.getMessage());
return new Gson().toJson(resultMap);
}
UserTransaction userTransaction = CommonUtils.startTransaction();
if (!ifExitsInZOrderListWith(listOrder, dynamicQuery, systemBase)) {
try {
Zpcd4MesAdapterPTBindingQSService zpcd4MesAdapterPTBindingQSService = new Zpcd4MesAdapterPTBindingQSService();
Zpcd4MesAdapterPT zpcd4MesAdapterPT = zpcd4MesAdapterPTBindingQSService.getZpcd4MesAdapterPTBindingQSPort();
ZFUNZPCD4MES ZFUNZPCD4MES = new ZFUNZPCD4MES();
ZFUNZPCD4MES.setEBELN(listOrder);
ZFUNZPCD4MESRESPONSE ZFUNZPCD4MESRESPONSE = zpcd4MesAdapterPT.zfunZPCD4MES(ZFUNZPCD4MES);
List<ZSPCD> erpDataList = ZFUNZPCD4MESRESPONSE.getTOUT().getItem();
for (int i = 0; i < erpDataList.size(); i++) {
String line = erpDataList.get(i).getZZDISPO().equals("") ? "#" : erpDataList.get(i).getZZDISPO();
String operation = erpDataList.get(i).getZZVORNR();
String items = erpDataList.get(i).getMATNR();
String shopOrder = erpDataList.get(i).getAUFNR();
dynamicQuery.clear();
dynamicQuery.append(" SELECT CF.VALUE LINE FROM WIP.SHOP_ORDER SO JOIN WIP.CUSTOM_FIELDS CF ON CF.HANDLE = SO.HANDLE AND CF.\"ATTRIBUTE\" = 'O_PLINE' ");
dynamicQuery.append(" WHERE SO.SHOP_ORDER = ").appendString(shopOrder);
Data lineData = systemBase.executeQuery(dynamicQuery);
if (!Utils.isEmpty(lineData)) {
operation = lineData.getString("LINE", "", 0) + "-OP" + operation;
}
String itemDes = erpDataList.get(i).getMAKTX();
if (items.length() > 8) {
items = items.substring(items.length() - 8);
}
String orderOty = String.valueOf(erpDataList.get(i).getMENGE().intValue());
String vendors = erpDataList.get(i).getLIFNR();
String itemQty = erpDataList.get(i).getBSTMG().substring(0, 1);
String lacation = erpDataList.get(i).getLGPBE();
String sprayPaint = erpDataList.get(i).getZPAINT();
String assemb = erpDataList.get(i).getZSZBS();
String handle = "ORDERBO:" + erpDataList.get(i).getEBELN() + "," + line;
if (i == 0) {
// 更新Z_ORDER_LIST表数据
dynamicQuery.clear();
dynamicQuery.append("INSERT INTO WIP.Z_ORDER_LIST (HANDLE, LIST_ORDER, LINE) VALUES(").appendString(handle).append(",");
dynamicQuery.appendString(listOrder).append(",").appendString(line).append(")");
systemBase.executeUpdate(dynamicQuery);
}
// 更新Z_ITEM_ORDER_LIST表数据
dynamicQuery.clear();
dynamicQuery.append(" INSERT INTO WIP.Z_ITEM_ORDER_LIST (HANDLE, ORDER_BO, OPERATION, ITEM, SHOPORDER, ITEM_DESCRIPTION, ITEM_QTY, ORDER_QTY");
if (!Utils.isBlank(vendors)) {
dynamicQuery.append(", VENDOR");
}
if (!Utils.isBlank(lacation)) {
dynamicQuery.append(", LOCATION");
}
if (!Utils.isBlank(sprayPaint)) {
dynamicQuery.append(", ZPAINT");
}
if (!Utils.isBlank(assemb)) {
dynamicQuery.append(", ZSZBS");
}
dynamicQuery.append(")");
dynamicQuery.append(" VALUES(").appendString(dateFormat.format(new Date()) + String.valueOf((int) (Math.random() * 10000))).append(",");
dynamicQuery.appendString(handle).append(",");
dynamicQuery.appendString(operation).append(",");
dynamicQuery.appendString(items).append(",");
dynamicQuery.appendString(shopOrder).append(",");
dynamicQuery.appendString(itemDes).append(",");
dynamicQuery.appendString(itemQty).append(",");
dynamicQuery.appendString(orderOty);
if (!Utils.isBlank(vendors)) {
dynamicQuery.append(",").appendString(vendors);
}
if (!Utils.isBlank(lacation)) {
dynamicQuery.append(",").appendString(lacation);
}
if (!Utils.isBlank(sprayPaint)) {
dynamicQuery.append(",").appendString(sprayPaint);
}
if (!Utils.isBlank(assemb)) {
dynamicQuery.append(",").appendString(assemb);
}
dynamicQuery.append(")");
systemBase.executeUpdate(dynamicQuery);
}
CommonUtils.commitTransaction(userTransaction);
} catch (Exception e) {
CommonUtils.rollBackTransaction(userTransaction);
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", e.getMessage());
return new Gson().toJson(resultMap);
}
}
Data itemOrderData = getItemOrderDataByItemAndOrder(listOrder, item, dynamicQuery, systemBase);
if (Utils.isEmpty(itemOrderData)) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "配车单无此物料信息!");
return new Gson().toJson(resultMap);
}
// 行数据，
List<Map<String, Object>> lineData = new ArrayList<Map<String, Object>>();
resultMap.put("INFO", lineData);
String vendor = itemCode.split("/")[2].substring(0, 3);
// 证明所有数据都没有 vendor
for (int i = 0; i < itemOrderData.size(); i++) {
Map<String, Object> line = new HashMap<String, Object>();
line.put("OPERATION", itemOrderData.getString("OPERATION", "", i));
line.put("ITEM", itemOrderData.getString("ITEM", "", i));
line.put("ITEM_QTY", itemOrderData.getString("ITEM_QTY", "", i));
line.put("ITEM_DESCRIPTION", itemOrderData.getString("ITEM_DESCRIPTION", "", i));
line.put("SHOPORDER", itemOrderData.getString("SHOPORDER", "", i));
line.put("ORDER_QTY", itemOrderData.getString("ORDER_QTY", "", i));
line.put("STATE_MACHINE", itemOrderData.getString("STATE_MACHINE", "", i));
line.put("ORD_SEQUENCE", itemOrderData.getString("ORD_SEQUENCE", "", i));
line.put("QUANTITY_OF_PROD", itemOrderData.getString("QUANTITY_OF_PROD", "", i));
line.put("GEN_DATE_TIME", itemOrderData.getString("GEN_DATE_TIME", "", i));
line.put("LOCATION", itemOrderData.getString("LOCATION", "", i));
line.put("ZPAINT", itemOrderData.getString("ZPAINT", "", i));
line.put("ZSZBS", itemOrderData.getString("ZSZBS", "", i));
line.put("SO_STATUS", itemOrderData.getString("SO_STATUS", "", i));
line.put("VENDOR", itemOrderData.getString("VENDOR", "", i));
lineData.add(line);
}
List<Map<String, Object>> orderInfosWithVendor = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> orderInfosWithoutVendor = new ArrayList<Map<String, Object>>();
DataCoreServiceInterface dataCoreService = Services.getService("com.sapdev.service", "DataCoreService", "1000");
boolean vendorIsNull = false;
if (lineData != null && lineData.size() > 0) {
for (Map<String, Object> orderMap : lineData) {
if (orderMap.get("VENDOR") != null && !orderMap.get("VENDOR").toString().equals("")) {
vendorIsNull = true;
if (orderMap.get("VENDOR").toString().equals(vendor)) {
orderInfosWithVendor.add(orderMap);
}
} else {
orderInfosWithoutVendor.add(orderMap);
}
}
if (vendorIsNull) {
if (orderInfosWithVendor != null && orderInfosWithVendor.size() > 0) {
boolean flag = true;
for (int i = 0; i < orderInfosWithVendor.size(); i++) {
String shoporder = orderInfosWithVendor.get(i).get("SHOPORDER").toString();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(" SELECT A1.ORDER_QTY,SUM(A1.CURRENT_QTY) CURRENT_QTY FROM WIP.Z_LABEL_LIST A1 ");
stringBuffer.append(" JOIN WIP.Z_LABEL_STATUS A2 ON A1.LABEL_ORDER = A2.LABEL_ORDER AND A2.STATUS != '1' ");
stringBuffer.append(" WHERE A1.SHOP_ORDER = '").append(shoporder).append("' AND A1.ITEM = '").append(item);
stringBuffer.append("' AND A1.\"ORDER\" = '").append(listOrder).append("' GROUP BY A1.ORDER_QTY");
List<Map<String, Object>> checklist = dataCoreService.getResultByTSQL(stringBuffer.toString(), new HashMap<String, Object>());
if (checklist != null && checklist.size() > 0 && checklist.get(0).get("ORDER_QTY") != null && checklist.get(0).get("CURRENT_QTY") != null) {
if (!checklist.get(0).get("ORDER_QTY").toString().equals(checklist.get(0).get("CURRENT_QTY").toString())) {
flag = true;
break;
} else {
flag = false;
}
} else {
flag = true;
break;
}
}
if (flag) {
resultMap.put("INFO", orderInfosWithVendor);
resultMap.put("CODE", 0);
} else {
resultMap.put("INFO", orderInfosWithoutVendor);
resultMap.put("CODE", 0);
}
} else if (orderInfosWithoutVendor != null && orderInfosWithoutVendor.size() > 0) {
resultMap.put("INFO", orderInfosWithoutVendor);
resultMap.put("CODE", 0);
} else {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", "物料供应商与指定供应商不一致");
}
} else {
resultMap.put("INFO", lineData);
resultMap.put("CODE", 0);
}
}
resultMap.put("LIST_ORDER", listOrder);
return new Gson().toJson(resultMap);
}
private boolean ifExitsInZOrderListWith(String listOrder, DynamicQuery dynamicQuery, SystemBase systemBase) {
dynamicQuery.clear();
dynamicQuery.append(" SELECT count(zol.LIST_ORDER) AS FLAG FROM WIP.Z_ORDER_LIST zol WHERE zol.LIST_ORDER = ").appendString(listOrder);
Data data = systemBase.executeQuery(dynamicQuery);
String flag = data.getString("FLAG", 0);
if ("0".equals(flag)) {
return false;
}
return true;
}
private Data getItemOrderDataByItemAndOrder(String listOrder, String item, DynamicQuery dynamicQuery, SystemBase systemBase) {
dynamicQuery.clear();
dynamicQuery.append(" SELECT ziol.VENDOR,ziol.OPERATION,ziol.ITEM,ziol.ITEM_QTY,ziol.ITEM_DESCRIPTION,ziol.SHOPORDER, ");
dynamicQuery.append(" ziol.ORDER_QTY,cf.VALUE AS STATE_MACHINE,zsei.ORD_SEQUENCE,zsei.QUANTITY_OF_PROD, ");
dynamicQuery.append(" zsei.GEN_DATE_TIME,ziol.LOCATION,ziol.ZPAINT,ziol.ZSZBS,dfl.DATA_TAG AS SO_STATUS ");
dynamicQuery.append(" FROM WIP.Z_ITEM_ORDER_LIST ziol INNER JOIN WIP.Z_SO_EXTEND_INFO zsei ON (ziol.SHOPORDER = zsei.SHOP_ORDER) ");
dynamicQuery.append(" INNER JOIN WIP.SHOP_ORDER so ON (so.SHOP_ORDER = zsei.SHOP_ORDER) ");
dynamicQuery.append(" INNER JOIN WIP.CUSTOM_FIELDS cf ON (cf.HANDLE = (CASE WHEN SUBSTRING(so.PLANNED_ITEM_BO,length(so.PLANNED_ITEM_BO),length(so.PLANNED_ITEM_BO))='#' THEN SUBSTRING(so.PLANNED_ITEM_BO,0,length(so.PLANNED_ITEM_BO)-1)||'A' ELSE so.PLANNED_ITEM_BO END) AND cf.\"ATTRIBUTE\" = 'STATE_MACHINE') ");
dynamicQuery.append(" LEFT JOIN WIP.Z_SO_STATUS_INFO zssi ON (zssi.SHOP_ORDER = ziol.SHOPORDER) ");
dynamicQuery.append(" INNER JOIN WIP.DATA_FIELD_LIST dfl ON (dfl.DATA_VALUE = IFNULL(zssi.SO_STATUS,'01')) ");
dynamicQuery.append(" INNER JOIN WIP.DATA_FIELD df ON ( df.HANDLE = dfl.DATA_FIELD_BO AND df.DATA_FIELD = 'SO_STATUS' ) ");
dynamicQuery.append(" WHERE ziol.ITEM = ").appendString(item);
dynamicQuery.append(" AND SUBSTR_BEFORE(SUBSTR_AFTER(ziol.ORDER_BO,':'),',') = ").appendString(listOrder);
Data data = systemBase.executeQuery(dynamicQuery);
return data;
}
@WebMethod
public String getShopOrderInfos(@WebParam(name = "requestParam") String requestParam) throws Exception {
Map<String, Object> resultMap = new HashMap<String, Object>();
// string转json对象
JSONObject jsonObject = JSONObject.fromObject(requestParam);
// jason对象转map
Map<String, Object> map = (Map<String, Object>) JSONObject.toBean(jsonObject, Map.class);
DynamicQuery dynamicQuery = DynamicQueryFactory.newInstance();
String listOrder = map.get("LIST_ORDER") == null ? "" : map.get("LIST_ORDER").toString();
String item = map.get("ITEM") == null ? "" : map.get("ITEM").toString();
List<String> shopOrderList = (List<String>) (map.get("SHOPORDER") == null ? new ArrayList<String>() : map.get("SHOPORDER"));
SystemBase systemBase = SystemBase.createDefaultSystemBase();
// 行数据，
List<Map<String, Object>> lineData = new ArrayList<Map<String, Object>>();
resultMap.put("INFO", lineData);
try {
Data data = getReturnDataByItemAndOrderAndShopOrder(listOrder, item, shopOrderList, dynamicQuery, systemBase);
if (!Utils.isEmpty(data)) {
int size = data.size();
int newQty = 0;
List<String> listTemp = new ArrayList<String>();
for (int i = 0; i < size; i++) {
if (!listTemp.contains(data.getString("SHOPORDER", "", i))) {
listTemp.add(data.getString("SHOPORDER", "", i));
newQty += data.getInteger("NEW_QTY", i);
}
}
for (int i = 0; i < size; i++) {
Map<String, Object> line = new HashMap<String, Object>();
line.put("OPERATION", data.getString("OPERATION", "", i));
line.put("ITEM", data.getString("ITEM", "", i));
line.put("ITEM_QTY", data.getString("ITEM_QTY", "", i));
line.put("ITEM_DESCRIPTION", data.getString("ITEM_DESCRIPTION", "", i));
line.put("SHOPORDER", data.getString("SHOPORDER", "", i));
line.put("ORDER_QTY", data.getString("ORDER_QTY", "", i));
line.put("STATE_MACHINE", data.getString("STATE_MACHINE", "", i));
line.put("ORD_SEQUENCE", data.getString("ORD_SEQUENCE", "", i));
line.put("QUANTITY_OF_PROD", data.getString("QUANTITY_OF_PROD", "", i));
line.put("GEN_DATE_TIME", data.getString("GEN_DATE_TIME", "", i));
line.put("LOCATION", data.getString("LOCATION", "", i));
line.put("ZPAINT", data.getString("ZPAINT", "", i));
line.put("ZSZBS", data.getString("ZSZBS", "", i));
line.put("VENDOR", data.getString("VENDOR", "", i));
line.put("NEW_QTY", String.valueOf(newQty));
lineData.add(line);
}
}
} catch (Exception e) {
resultMap.put("CODE", 1);
resultMap.put("MESSAGE", e.getMessage());
return new Gson().toJson(resultMap);
}
resultMap.put("CODE", 0);
return new Gson().toJson(resultMap);
}
private Data getReturnDataByItemAndOrderAndShopOrder(String listOrder, String item, List<String> shopOrderList, DynamicQuery dynamicQuery, SystemBase systemBase) {
StringBuilder shoporders = new StringBuilder("");
for (String ele : shopOrderList) {
shoporders.append("'").append(ele).append("'").append(",");
}
shoporders.setLength(shoporders.length() - 1);
dynamicQuery.clear();
dynamicQuery.append(" WITH SUM_ORDER_QTY AS ( ");
dynamicQuery.append(" SELECT SUM(TO_INT(ziol.ORDER_QTY)) ORDER_QTYS , ").appendString(listOrder);
dynamicQuery.append(" AS LIST_ORDER , ziol.SHOPORDER,ziol.ITEM FROM WIP.Z_ITEM_ORDER_LIST ziol WHERE ziol.SHOPORDER IN ( ");
dynamicQuery.append(" " + shoporders.toString()).append(")");
dynamicQuery.append(" AND SUBSTR_BEFORE(SUBSTR_AFTER(ziol.ORDER_BO,':'),',') = ").appendString(listOrder);
dynamicQuery.append(" AND ziol.ITEM = ").appendString(item);
dynamicQuery.append(" GROUP BY SHOPORDER,ITEM ").append("),");
dynamicQuery.append(" SUM_CURRENT_QTY AS ( ");
dynamicQuery.append(" SELECT SUM(TO_INT(zll.CURRENT_QTY)) CURRENT_QTYS,zll.SHOP_ORDER FROM WIP.Z_LABEL_LIST zll INNER JOIN WIP.Z_LABEL_STATUS zls ON zll.LABEL_ORDER = zls.LABEL_ORDER ");
dynamicQuery.append(" WHERE zls.STATUS = '0' AND zll.SHOP_ORDER in ( ");
dynamicQuery.append(" " + shoporders.toString()).append(")");
dynamicQuery.append(" AND zll.ITEM = ").appendString(item);
dynamicQuery.append(" AND zll.\"ORDER\" = ").appendString(listOrder);
dynamicQuery.append(" GROUP BY SHOP_ORDER) ");
dynamicQuery.append(" SELECT ziol.VENDOR,ziol.OPERATION,ziol.ITEM,ziol.ITEM_QTY,ziol.ITEM_DESCRIPTION,ziol.SHOPORDER, ");
dynamicQuery.append(" ziol.ORDER_QTY,cf.VALUE AS STATE_MACHINE,zsei.ORD_SEQUENCE,zsei.QUANTITY_OF_PROD,zsei.GEN_DATE_TIME, ");
dynamicQuery.append(" ziol.LOCATION , ziol.ZPAINT,ziol.ZSZBS , (t1.ORDER_QTYS - IFNULL(t2.CURRENT_QTYS,0)) AS NEW_QTY ");
dynamicQuery.append(" FROM SUM_ORDER_QTY t1 LEFT JOIN SUM_CURRENT_QTY t2 ON (t1.SHOPORDER = t2.SHOP_ORDER) ");
dynamicQuery.append(" INNER JOIN WIP.Z_ITEM_ORDER_LIST ziol ON (ziol.SHOPORDER=t1.SHOPORDER AND SUBSTR_BEFORE(SUBSTR_AFTER(ziol.ORDER_BO,':'),',') = t1.LIST_ORDER AND ziol.ITEM = t1.ITEM) ");
dynamicQuery.append(" INNER JOIN WIP.Z_SO_EXTEND_INFO zsei ON (zsei.SHOP_ORDER = ziol.SHOPORDER) ");
dynamicQuery.append(" INNER JOIN WIP.SHOP_ORDER so ON (so.SHOP_ORDER = zsei.SHOP_ORDER) ");
dynamicQuery.append(" INNER JOIN WIP.CUSTOM_FIELDS cf ON (cf.HANDLE = (CASE WHEN SUBSTRING(so.PLANNED_ITEM_BO,length(so.PLANNED_ITEM_BO),length(so.PLANNED_ITEM_BO))='#' THEN SUBSTRING(so.PLANNED_ITEM_BO,0,length(so.PLANNED_ITEM_BO)-1)||'A' ELSE so.PLANNED_ITEM_BO END) AND cf.\"ATTRIBUTE\" = 'STATE_MACHINE') ");
dynamicQuery.append(" ORDER BY zsei.GEN_DATE_TIME, zsei.ORD_SEQUENCE");
Data data = systemBase.executeQuery(dynamicQuery);
return data;
}
}