package psdi.app.iface.mobile; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.coolservlets.beans.method.CheckMethod; import com.dbms.dbcon.DBCon; import com.dbms.dbcon.QueryResult; import com.dbms.dbcon.ResultRow; import psdi.app.iface.comm.getDataRecord; import psdi.mbo.MboRemote; import psdi.mbo.MboSetRemote; import psdi.security.ConnectionKey; import psdi.server.MXServer; import psdi.util.MXCustomCipher; import psdi.util.MXException; import javax.ws.rs.Consumes; import javax.ws.rs.POST; import javax.ws.rs.Path; import javax.ws.rs.Produces; import javax.ws.rs.core.MediaType; import java.io.UnsupportedEncodingException; import java.rmi.RemoteException; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.Vector; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * 数据方向 EAM--app * 库存查询 * * @author liuxingqiang */ @Path("/common") public class Eam2AppService { @POST @Path("/login") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public JSONObject login(String received) throws Exception { System.out.println("------USER INFO received =" +received.toString()+ "begin login..."); JSONObject result = new JSONObject();//返回值 JSONObject result_data = new JSONObject();//返回值data JSONObject dataObj = JSONObject.parseObject(received);//接收app传过来的参数 System.out.println("------USER json dataObj =" +dataObj.toString()); //JSONObject returnData = new JSONObject();//返回值 String username = (String) dataObj.get("username"); username = (username == null) ? "" : username.trim(); String fusername = username.toUpperCase(); System.out.println("username =" + username); String personid = ""; String defsite = ""; //先判断人员的状态是否活动（INACTIVE） if(username!=null && !"".equals(username)){ String sqlInActive = "SELECT USERID,STATUS FROM MAXUSER WHERE PERSONID ='"+username+"'"; ConnectionKey connectionKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey(); QueryResult InActiveResult = DBCon.runQuery(sqlInActive.toString(), connectionKey); int loopCount = InActiveResult.size(); if (loopCount > 0) { ResultRow resultRow = InActiveResult.getRow(0); String USERID = resultRow.getValue("USERID".toUpperCase()); String STATUS = resultRow.getValue("STATUS".toUpperCase()); if("INACTIVE".equalsIgnoreCase(STATUS)){ result.put("errcode", "600"); result.put("errmsg", "User InActive"); return result; } } } boolean IsTrue = false; try{ //密码验证 if (username.length() > 0) { String password = (String) dataObj.get("password"); password = (password == null) ? "" : password.trim(); //根据username,password去maxuser表验证，中间涉及到MD5加密 if (password.length() > 0) { //String fsql = "select u.PASSWORD,person.DISPLAYNAME,person.DEPARTMENT,dept.DESCRIPTION,s.DESCRIPTION SITEDESC from MAXUSER u,PERSON person,DEPT dept,SITE s where u.USERID ='" + fusername + "' and u.PERSONID =person.PERSONID and dept.DEPTNUM =person.DEPARTMENT and u.DEFSITE = s.SITEID"; String fsql = "SELECT U.PASSWORD,person.DISPLAYNAME,person.DEPARTMENT,person.LOCATIONORG,person.LOCATIONSITE,dept.DESCRIPTION,s.DESCRIPTION SITEDESC FROM MAXUSER U left join PERSON person on U.PERSONID = person.PERSONID left join DEPT dept on dept.DEPTNUM = person.DEPARTMENT left join SITE s on U.DEFSITE = s.SITEID WHERE U.USERID = '"+fusername+"'"; ConnectionKey connectionKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey(); QueryResult fresult = DBCon.runQuery(fsql.toString(), connectionKey); int loopCount = fresult.size(); System.out.println("----loopCount =" + loopCount); if (loopCount > 0) { ResultRow resultRow = fresult.getRow(0); String encdata = resultRow.getValue("PASSWORD".toUpperCase()); encdata = (encdata == null) ? "" : encdata.trim(); //System.out.println("encdata =" + encdata); MXCustomCipher custcipher = new MXCustomCipher(); String decpassword = custcipher.decData(encdata); decpassword = (decpassword == null) ? "" : decpassword.trim(); // System.out.println("decpassword =" + decpassword); if (decpassword.length() > 0) { if (decpassword.equals(password)) { IsTrue = true;//验证密码成功。 //System.out.println("IsTrue true=" + IsTrue); String displayname = resultRow.getValue("DISPLAYNAME".toUpperCase()); displayname = (displayname == null) ? "" : displayname.trim(); String deptdesc = resultRow.getValue("DESCRIPTION".toUpperCase()); deptdesc = (deptdesc == null) ? "" : deptdesc.trim(); String deptid = resultRow.getValue("DEPARTMENT".toUpperCase()); deptid = (deptid == null) ? "" : deptid.trim(); String sitedesc = resultRow.getValue("SITEDESC".toUpperCase()); sitedesc = (sitedesc == null) ? "" : sitedesc.trim(); String LOCATIONORG = resultRow.getValue("LOCATIONORG".toUpperCase()); String LOCATIONSITE = resultRow.getValue("LOCATIONSITE".toUpperCase()); result.put("errcode", "200"); result.put("errmsg", "succeed!"); result_data.put("userid", username);//用户名 result_data.put("name", displayname);//中文名 result_data.put("englishname", "");//英文名 result_data.put("company", sitedesc);//工厂 result_data.put("deptid", deptid);//部门编号 result_data.put("department", deptdesc);//部门 result_data.put("post", "");//岗位 result_data.put("LOCATIONORG", LOCATIONORG);//组织 result_data.put("LOCATIONSITE", LOCATIONSITE);//地点 result.put("result_data", result_data); System.out.println("result =" + result); IsTrue = true; } else { result.put("errcode", "300"); result.put("errmsg", "Username or Password Incorrect"); } }else{ result.put("errcode", "500"); result.put("errmsg", "unsuccessful"); } }else{ result.put("errcode", "500"); result.put("errmsg", "unsuccessful"); } }else{ result.put("errcode", "400"); result.put("errmsg", "No Password"); } }else{ result.put("errcode", "300"); result.put("errmsg", "No User"); } }catch (Exception e){ System.out.println("------->failure Message="+e.getMessage()); result.put("errcode", "300"); result.put("errmsg", "No User"); }finally { return result; /* if (!IsTrue) { result.put("errcode", "500"); result.put("errmsg", "unsuccessful"); }*/ } } //------------------------------------------------------------- @POST @Path("/query/sqlList") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public JSONArray returnAPP(String received) throws Exception { JSONArray dataArr = new JSONArray(); JSONObject jsobj = new JSONObject(); try{ //解析前端传过来的数据----------------------Begin JSONObject dataObj = JSONObject.parseObject(received);//接收app传过来的参数 String appid = (String) dataObj.get("appid"); //应用名 String objectname = (String) dataObj.get("objectname");//数据对象 String orderby = (String) dataObj.get("orderby");//排序条件 String username = (String) dataObj.get("username");//用户名 String curpage = (String) (dataObj.get("curpage")==null?"0":dataObj.get("curpage"));//当前页数 int iCurpage = Integer.parseInt(curpage); String showcount = (String) (dataObj.get("showcount")==null?"":dataObj.get("showcount"));//每页显示的条数 int iShowcount = Integer.parseInt(showcount); String option = (String) (dataObj.get("option")==null?"":dataObj.get("option"));//操作 JSONObject sinorsearch = (JSONObject) dataObj.get("sinorsearch");//模糊查询 Vector vWherefld = new Vector(); Vector vOrderbyfld = new Vector(); String sinorsearchWhere = " "; if (sinorsearch != null) { Iterator sinorsearcIte = sinorsearch.keySet().iterator(); while (sinorsearcIte.hasNext()) { String key = (String) sinorsearcIte.next(); String value = sinorsearch.getString(key); //System.out.println("key="+key +";value="+value); sinorsearchWhere += key + " like '%" + value + "%' or "; vWherefld.addElement(new String(key)); } } String fsinorsearchWhere = sinorsearchWhere.trim(); System.out.println("---------conditon sql="+fsinorsearchWhere); //解析前端传过来的数据---------------------------------End //查询数据库---------------------------------start MboSetRemote mxobjSets = MXServer.getMXServer().getMboSet(""+objectname+"", MXServer.getMXServer().getSystemUserInfo());//查询表 mxobjSets.setWhere("APPID ='" + appid + "' and OBJECTNAME ='" + objectname + "'"); mxobjSets.reset(); }catch (Exception e){ }finally { return dataArr; } } //------------------------------------------------------------ @POST @Path("/query/sql") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public JSONObject GeneralQueryBySql(String received) throws Exception { //基础数据准备 Begin System.out.println("-----------entry query sql start------------"); HashMap hmkeyword = new HashMap<>(); String fsql = "SELECT KEYWORD FROM v$reserved_words m WHERE m.RESERVED='Y' order by m.KEYWORD"; ConnectionKey connectionKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey(); QueryResult fresult = DBCon.runQuery(fsql.toString(), connectionKey); int loopCount = fresult.size(); for (int i = 0; i < loopCount; i++) { ResultRow resultRow = fresult.getRow(i); String keyword = resultRow.getValue("keyword".toUpperCase()); keyword = (keyword == null) ? "" : keyword.trim(); if (keyword.length() == 0) continue; keyword = keyword.toUpperCase(); hmkeyword.put(keyword, keyword); } //基础数据准备 End //解析前端传过来的数据----------------------Begin JSONObject dataObj = JSONObject.parseObject(received);//接收app传过来的参数 String appid = (String) dataObj.get("appid"); //应用名 appid = (appid == null) ? "" : appid.trim(); String objectname = (String) dataObj.get("objectname");//数据对象 objectname = (objectname == null) ? "" : objectname.trim(); String orderby = (String) dataObj.get("orderby");//排序条件 orderby = (orderby == null) ? "" : orderby.trim(); String username = (String) dataObj.get("username");//用户名 username = (username == null) ? "" : username.trim(); String curpage = (String) dataObj.get("curpage");//当前页数 curpage = (curpage == null) ? "0" : curpage.trim(); int iCurpage = Integer.parseInt(curpage); String showcount = (String) dataObj.get("showcount");//每页显示的条数 showcount = (showcount == null) ? "0" : showcount.trim(); int iShowcount = Integer.parseInt(showcount); String option = (String) dataObj.get("option");//操作 option = (option == null) ? "" : option.trim(); JSONObject sinorsearch = (JSONObject) dataObj.get("sinorsearch");//模糊查询 JSONObject condition = (JSONObject) dataObj.get("condition");//精确查询条件 JSONObject reporsearch = (JSONObject) dataObj.get("reporsearch");//模糊查询条件（含关系） String SYNCSHIP = (String) dataObj.get("SYNCSHIP");//用来区分中、英、法语言的 SYNCSHIP = (SYNCSHIP == null || "".equals(SYNCSHIP)) ? "ZH" : SYNCSHIP.trim();//默认是中文 Vector vWherefld = new Vector(); Vector vOrderbyfld = new Vector(); String sinorsearchWhere = " "; if (sinorsearch != null) { Iterator sinorsearcIte = sinorsearch.keySet().iterator(); while (sinorsearcIte.hasNext()) { String key = (String) sinorsearcIte.next(); String value = sinorsearch.getString(key); //-多条件同时查询，中间用“逗号”隔开(中英文逗号都可以)---liuxq-----start----------------------------------------------- if(value!=null && !"".equals(value) && containsChineseOrComma(value)){ // String[] parts = value.split("[，,]"); String[] parts = value.split("[,，\\uFF0C]"); sinorsearchWhere +="("; for (int i=0;i 0) ? sinorsearchWhere.substring(0, sinorsearchWhere.lastIndexOf("and")) : fsinorsearchWhere; sinorsearchWhere +=")"; sinorsearchWhere += " or "; //-多条件同时查询，中间用“逗号”隔开(中英文逗号都可以)---liuxq-----start----------------------------------------------- }else{ sinorsearchWhere += key + " like '%" + value + "%' or "; vWherefld.addElement(new String(key)); } } } String fsinorsearchWhere = sinorsearchWhere.trim(); sinorsearchWhere = (fsinorsearchWhere.length() > 0) ? sinorsearchWhere.substring(0, sinorsearchWhere.lastIndexOf("or")) : fsinorsearchWhere; //System.out.println("----------sinorsearchWhere="+sinorsearchWhere); String conditionWhere = " "; if (condition != null) { Iterator conditionIte = condition.keySet().iterator(); while (conditionIte.hasNext()) { String key = (String) conditionIte.next(); String value = condition.getString(key); //涉及 appname in('UDLLD','UDLLDAPP') 的改造 if("appname".equalsIgnoreCase(key)){ conditionWhere += key + " " + value + " and "; }else if("BWART".equalsIgnoreCase(key)){ conditionWhere += key + " " + value + " and "; }else if("v.CREATEDATE".equalsIgnoreCase(key)){ conditionWhere += " " + value + " and "; }else{ conditionWhere += key + "='" + value + "' and "; } //System.out.println("key="+key +";value="+value); vWherefld.addElement(new String(key)); } } String fconditionWhere = conditionWhere.trim(); conditionWhere = (fconditionWhere.length() > 0) ? conditionWhere.substring(0, conditionWhere.lastIndexOf("and")) : conditionWhere; String sWhere = ""; conditionWhere = ((conditionWhere.trim()).length() == 0) ? "" : conditionWhere.trim(); if (conditionWhere.length() > 0) { sWhere = sWhere + "(" + conditionWhere + ")"; } sinorsearchWhere = ((sinorsearchWhere.trim()).length() == 0) ? "" : "(" + sinorsearchWhere.trim() + ")"; if (sinorsearchWhere.length() > 0) { sWhere = (sWhere.length() > 0) ? sWhere + " and " + sinorsearchWhere : sWhere + sinorsearchWhere; } //解析前端传过来的数据---------------------------------End //查询数据库--------------start MboSetRemote mxobjSets = MXServer.getMXServer().getMboSet("MXOBJCFG", MXServer.getMXServer().getSystemUserInfo()); mxobjSets.setWhere("APPID ='" + appid + "' and OBJECTNAME ='" + objectname + "' and SYNCSHIP='"+SYNCSHIP+"'"); mxobjSets.reset(); String sql = ""; String lwherestr = ""; System.out.println("mxobjSets=" + mxobjSets.getSize()); if (!mxobjSets.isEmpty()) { HashMap hmChildAndMain = new HashMap<>(); int nloop = mxobjSets.count(); //System.out.println("nloop =" + nloop); MboRemote mxobjmbo = mxobjSets.getMbo(0); //System.out.println("mxobjmbo =" + mxobjmbo); sql = mxobjmbo.getString("MEMO"); //查询前端写好的sql语句 sql = (sql == null) ? "" : sql.trim(); // System.out.println("---------Memo="+sql); //System.out.println("sqlstr =" + sqlstr); if (sql.length() == 0) return new JSONObject(); String childAndMain = mxobjmbo.getString("RESTRICTIONS");//查询sql语句中列 childAndMain = (childAndMain == null) ? "" : childAndMain.trim(); //System.out.println("childAndMain =" + childAndMain); if (childAndMain.length() > 0) { String cldAndMainRecord[] = childAndMain.split(",");//查询的列都是通过“逗号”隔开 int uloop = cldAndMainRecord.length; for (int u = 0; u < uloop; u++) { String fldstr = cldAndMainRecord[u]; fldstr = (fldstr == null) ? "" : fldstr.trim(); if (fldstr.length() > 0) { String keyAndVaule[] = fldstr.split("="); String keystr = keyAndVaule[0]; keystr = (keystr == null) ? "" : keystr.trim(); String valuestr = keyAndVaule[1]; valuestr = (valuestr == null) ? "" : valuestr.trim(); if (keystr.length() > 0 && valuestr.length() > 0) { //System.out.println("keystr =" + keystr + ",valuestr =" + valuestr); hmChildAndMain.put(keystr, valuestr); } } } } if (sWhere.length() > 0) { sWhere = sWhere.replaceAll("\\(", " \\( "); sWhere = sWhere.replaceAll("\\)", " \\) "); sWhere = sWhere.replaceAll("=", " = "); String aWhereclause[] = sWhere.split(" "); int kloop = aWhereclause.length; //String lstfix = ""; String oneWhere = ""; for (int k = 0; k < kloop; k++) { String st = aWhereclause[k]; st = (st == null) ? "" : st.trim(); st = (st.length() == 0) ? " " : st; //System.out.println(st); if (st.indexOf("'", 0) > -1) {//具体的值 oneWhere = oneWhere +" "+ st;//liuxq 2023-12-11修改 // oneWhere = oneWhere + st;//原来 } else { if (st.equalsIgnoreCase(" ")) { oneWhere = oneWhere + " "; } else { if (CheckMethod.isNumber(st)) { oneWhere = oneWhere + st; } else { String keys = (String) hmkeyword.get(st.toUpperCase());//保留字 keys = (keys == null) ? "" : keys.trim();//原来的 //System.out.println("values = " + keys); if (keys.length() > 0) {//保留字 oneWhere = oneWhere + " " + st + " "; } else { //System.out.println("st = "+st); String replacest = hmChildAndMain.get(st); replacest = (replacest == null) ? "" : replacest.trim(); if (replacest.length() > 0) { oneWhere = oneWhere + replacest; } else { if(st.contains("qq.")){ oneWhere = oneWhere + st; }else if(st.contains("mm.")){ oneWhere = oneWhere + st; }else if(st.contains("ss.")){ oneWhere = oneWhere + st; }else if(st.contains("zz.")){ oneWhere = oneWhere + st; }else if(st.contains("b.")){ oneWhere = oneWhere + st; }else if(st.contains("v.TO_DATE")){ oneWhere = oneWhere + st.substring(2); }else{ oneWhere = oneWhere + "m." + st; } } //oneWhere = oneWhere + values; } } } } } lwherestr = oneWhere; } if (orderby.length() > 0) { String upOrderby = orderby.toUpperCase(); String aOrderbyfld[] = upOrderby.split(" "); int tloop = aOrderbyfld.length; for (int t = 0; t < tloop; t++) { String ft = aOrderbyfld[t]; ft = (aOrderbyfld == null) ? "" : aOrderbyfld[t].trim(); if (ft.length() == 0 || ft.equalsIgnoreCase("ASC") || ft.equalsIgnoreCase("DESC")) continue; vOrderbyfld.addElement(new String(ft)); } int vloop = vOrderbyfld.size(); //System.out.println("vloop="+vloop); if (vloop > 0) { for (int v = 0; v < vloop; v++) { String keystr = (String) vOrderbyfld.elementAt(v); keystr = (keystr == null) ? "" : keystr.trim(); if (keystr.length() > 0) { String replacest = hmChildAndMain.get(keystr); replacest = (replacest == null) ? "" : replacest.trim(); if (replacest.length() > 0) { //oneWhere = oneWhere + replacest; orderby = orderby.replaceAll(keystr, replacest); } else { //oneWhere = oneWhere + "m." +st; replacest = "m." + keystr; orderby = orderby.replaceAll(keystr, replacest); } } } } } } JSONObject result = new JSONObject();//返回值 if (sql.length() > 0) { getDataRecord getdatarecord = new getDataRecord(); result = getdatarecord.SearchGeneral(username, sql, lwherestr, iCurpage, iShowcount, orderby, connectionKey, "and"); boolean isSuccess = (result == null) ? false : true; if (isSuccess) { result.put("errcode", "200"); result.put("errmsg", "succeed!"); } else { result.put("errcode", "500"); result.put("errmsg", "unsuccessful"); } } return result; } @POST @Path("/query") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public JSONObject GeneralQuery(String received) throws Exception { //基础数据准备 Begin HashMap hmkeyword = new HashMap<>(); String fsql = "SELECT KEYWORD FROM v$reserved_words m WHERE m.RESERVED='Y' order by m.KEYWORD"; ConnectionKey connectionKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey(); QueryResult fresult = DBCon.runQuery(fsql.toString(), connectionKey); int loopCount = fresult.size(); for (int i = 0; i < loopCount; i++) { ResultRow resultRow = fresult.getRow(i); String keyword = resultRow.getValue("keyword".toUpperCase()); keyword = (keyword == null) ? "" : keyword.trim(); if (keyword.length() == 0) continue; keyword = keyword.toUpperCase(); hmkeyword.put(keyword, keyword); } //基础数据准备 End //解析前端传过来的数据 Begin JSONObject dataObj = JSONObject.parseObject(received);//接收app传过来的参数 String appid = (String) dataObj.get("appid"); //应用名 appid = (appid == null) ? "" : appid.trim(); String objectname = (String) dataObj.get("objectname");//数据对象 objectname = (objectname == null) ? "" : objectname.trim(); String orderby = (String) dataObj.get("orderby");//排序条件 orderby = (orderby == null) ? "" : orderby.trim(); String username = (String) dataObj.get("username");//用户名 username = (username == null) ? "" : username.trim(); String curpage = (String) dataObj.get("curpage");//当前页数 curpage = (curpage == null) ? "0" : curpage.trim(); int iCurpage = Integer.parseInt(curpage); String showcount = (String) dataObj.get("showcount");//每页显示的条数 showcount = (showcount == null) ? "0" : showcount.trim(); int iShowcount = Integer.parseInt(showcount); String option = (String) dataObj.get("option");//操作 option = (option == null) ? "" : option.trim(); JSONObject sinorsearch = (JSONObject) dataObj.get("sinorsearch");//模糊查询 JSONObject condition = (JSONObject) dataObj.get("condition");//精确查询条件 JSONObject reporsearch = (JSONObject) dataObj.get("reporsearch");//模糊查询条件（含关系） Vector vWherefld = new Vector(); Vector vOrderbyfld = new Vector(); String sinorsearchWhere = " "; if (sinorsearch != null) { Iterator sinorsearcIte = sinorsearch.keySet().iterator(); while (sinorsearcIte.hasNext()) { String key = (String) sinorsearcIte.next(); String value = sinorsearch.getString(key); //System.out.println("key="+key +";value="+value); sinorsearchWhere += key + " like '%" + value + "%' or "; vWherefld.addElement(new String(key)); } } String fsinorsearchWhere = sinorsearchWhere.trim(); sinorsearchWhere = (fsinorsearchWhere.length() > 0) ? sinorsearchWhere.substring(0, sinorsearchWhere.lastIndexOf("or")) : fsinorsearchWhere; //System.out.println("----------sinorsearchWhere="+sinorsearchWhere); String conditionWhere = " "; if (condition != null) { Iterator conditionIte = condition.keySet().iterator(); while (conditionIte.hasNext()) { String key = (String) conditionIte.next(); String value = condition.getString(key); //System.out.println("key="+key +";value="+value); conditionWhere += key + "='" + value + "' and "; vWherefld.addElement(new String(key)); } } String fconditionWhere = conditionWhere.trim(); conditionWhere = (fconditionWhere.length() > 0) ? conditionWhere.substring(0, conditionWhere.lastIndexOf("and")) : conditionWhere; String sWhere = ""; conditionWhere = ((conditionWhere.trim()).length() == 0) ? "" : conditionWhere.trim(); if (conditionWhere.length() > 0) { sWhere = sWhere + "(" + conditionWhere + ")"; } sinorsearchWhere = ((sinorsearchWhere.trim()).length() == 0) ? "" : "(" + sinorsearchWhere.trim() + ")"; if (sinorsearchWhere.length() > 0) { sWhere = (sWhere.length() > 0) ? sWhere + " and " + sinorsearchWhere : sWhere + sinorsearchWhere; } //System.out.println("sWhere="+sWhere); //解析前端传过来的数据 End JSONObject result = new JSONObject();//返回值 MboSetRemote mxobjSets = MXServer.getMXServer().getMboSet("MXOBJCFG", MXServer.getMXServer().getSystemUserInfo()); //JSONObject dataObj = JSONObject.parseObject(received);//接收app传过来的参数 //String appid = (String) dataObj.get("appid"); //应用名 //String appid = "INVENTORY"; //应用名 //String objectname = "INVENTORY";//数据对象 //System.out.println("appid="+appid); //System.out.println("objectname="+objectname); mxobjSets.setWhere("APPID ='" + appid + "' and OBJECTNAME ='" + objectname + "'"); mxobjSets.reset(); MboSetRemote mxattrSets = MXServer.getMXServer().getMboSet("MXATTRCFG", MXServer.getMXServer().getSystemUserInfo()); mxattrSets.setWhere("APPID ='" + appid + "' and OBJECTNAME ='" + objectname + "'"); mxattrSets.setOrderBy("MXATTRCFGID"); mxattrSets.reset(); MboSetRemote maxrelSets = MXServer.getMXServer().getMboSet("MAXRELATIONSHIP", MXServer.getMXServer().getSystemUserInfo()); if (!mxattrSets.isEmpty()) { int nloop = mxattrSets.count(); String clientfld[] = new String[nloop]; String selstr = "select distinct "; for (int i = 0; i < nloop; i++) { MboRemote mxattrmbo = mxattrSets.getMbo(i); String cfld = mxattrmbo.getString("ALIAS1"); clientfld[i] = cfld; String maxrel = mxattrmbo.getString("HIDDENCONDITION");//关系 maxrel = (maxrel == null) ? "" : maxrel.trim(); String orgattr = mxattrmbo.getString("ORGATTR");//原属性名 orgattr = (orgattr == null) ? "" : orgattr.trim(); String orgtype = mxattrmbo.getString("ORGTYPE");//类型 orgtype = (orgtype == null) ? "" : orgtype.trim(); String defaultval = mxattrmbo.getString("defaultval");//Calculation formula defaultval = (defaultval == null) ? "" : defaultval.trim(); if (maxrel.length() == 0) { boolean isautocal = mxattrmbo.getBoolean("autocal");//自动计算 if (defaultval.length() > 0) { if (isautocal) {//处理自动计算的 //处理自动计算的 String selrel = getAutoCalculate(defaultval, objectname, mxattrSets, maxrelSets, cfld); String calfix = (isNumberType(orgtype)) ? "NVL(" + selrel + ",0)" + " " + cfld : selrel + " " + cfld; selstr = (i < nloop - 1) ? selstr + calfix + "," : selstr + calfix; } else { //处理字段本身有公式的 String calfix = (isNumberType(orgtype)) ? "NVL(" + defaultval + "(m." + orgattr + "),0)" : defaultval + "(m." + orgattr + ")"; selstr = (i < nloop - 1) ? selstr + calfix + " " + cfld + "," : selstr + calfix + " " + cfld;//处理主表中的字段 } } else { String calfix = (isNumberType(orgtype)) ? "NVL(" + "m." + orgattr + ",0)" : "m." + orgattr; selstr = (i < nloop - 1) ? selstr + calfix + " " + cfld + "," : selstr + calfix + " " + cfld;//处理主表中的字段 } } else { if (defaultval.length() > 0) { orgattr = (isNumberType(orgtype)) ? "NVL(" + defaultval + "(" + orgattr + "),0)" : defaultval + "(" + orgattr + ")"; } else { orgattr = (isNumberType(orgtype)) ? "NVL(" + orgattr + ",0)" : "" + orgattr; } //处理关系 maxrelationship 脚本 String child = mxattrmbo.getString("CHILD");//关系 child = (child == null) ? "" : child.trim(); String selrel = getselectfixByMaxRel(maxrel, objectname, cfld, orgattr, child, maxrelSets); String calfix = (isNumberType(orgtype)) ? "NVL(" + selrel + ",0)" + " " + cfld : selrel + " " + cfld; selstr = (i < nloop - 1) ? selstr + calfix + "," : selstr + calfix; } } //System.out.println("selstr="+selstr); //解析 orderby Begin //String aorderbyop[] = {"ASC","DESC"}; String orderbystr = ""; //System.out.println("orderby="+orderby); if (orderby.length() > 0) { String upOrderby = orderby.toUpperCase(); String aOrderbyfld[] = upOrderby.split(" "); int tloop = aOrderbyfld.length; for (int t = 0; t < tloop; t++) { String ft = aOrderbyfld[t]; ft = (aOrderbyfld == null) ? "" : aOrderbyfld[t].trim(); if (ft.length() == 0 || ft.equalsIgnoreCase("ASC") || ft.equalsIgnoreCase("DESC")) continue; vOrderbyfld.addElement(new String(ft)); } int vloop = vOrderbyfld.size(); //System.out.println("vloop="+vloop); if (vloop > 0) { //orderbystr = getSelOrderbyString(vOrderbyfld,mxattrSets,upOrderby,maxrelSets); //System.out.println("upOrderby="+upOrderby); orderbystr = replaceClientFld(vOrderbyfld, mxattrSets, upOrderby, maxrelSets); //System.out.println("orderbystr="+orderbystr); } } //解析 orderby End //解析 sWhere Begin int mloop = vWherefld.size(); //System.out.println("mloop="+mloop); String wherestr = ""; String lwherestr = ""; if (mloop > 0) { //System.out.println("sWhere="+sWhere); wherestr = replaceClientFld(vWherefld, mxattrSets, sWhere, maxrelSets);//测试正确 //System.out.println("wherestr="+wherestr); } //解析 sWhere End //获得 relwhere 通过关系建立的条件 Begin HashMap hmwherefld = new HashMap<>(); int yloop = vOrderbyfld.size(); for (int y = 0; y < yloop; y++) { String st = (String) vOrderbyfld.elementAt(y); st = (st == null) ? "" : st.trim(); hmwherefld.put(st.toUpperCase(), st.toUpperCase()); //vtfld.addElement(new String(st)); } yloop = vWherefld.size(); for (int y = 0; y < yloop; y++) { String st = (String) vWherefld.elementAt(y); st = (st == null) ? "" : st.trim(); hmwherefld.put(st.toUpperCase(), st.toUpperCase()); //vtfld.addElement(new String(st)); } String relwhere = getSelRelwhereString(hmwherefld, mxattrSets, maxrelSets, hmkeyword); relwhere = (relwhere == null) ? "" : relwhere.trim(); relwhere = (relwhere.length() == 0) ? "" : "(" + relwhere + ")"; //System.out.println("relwhere ="+relwhere); lwherestr = (wherestr.length() > 0) ? lwherestr + wherestr : lwherestr; if (relwhere.length() > 0) { lwherestr = (lwherestr.length() > 0) ? lwherestr + " and " + relwhere : relwhere; //System.out.println("lwherestr="+lwherestr); } //获得 relwhere 通过关系建立的条件 End //获得 otherObject 通过关系建立的条件 Begin String otherObject = getSelOtherObject(hmwherefld, mxattrSets); otherObject = (otherObject == null) ? "" : otherObject.trim(); if (otherObject.length() > 0) { otherObject = otherObject.substring(0, otherObject.length() - 1); //System.out.println("otherObject="+otherObject); } //获得 otherObject 通过关系建立的条件 End String sql = (otherObject.length() == 0) ? selstr + " from " + objectname + " m" : selstr + " from " + objectname + " m," + otherObject; //sql = (lwherestr.length() >0)?sql + " where " + lwherestr:sql; //sql = (orderbystr.length() >0)?sql + " order by " + orderbystr:sql; getDataRecord getdatarecord = new getDataRecord(); //System.out.println("sql =" + sql); result = getdatarecord.SearchGeneral(username, sql, lwherestr, iCurpage, iShowcount, orderbystr, connectionKey, "where"); boolean isSuccess = (result == null) ? false : true; if (isSuccess) { result.put("errcode", "200"); result.put("errmsg", "succeed!"); } else { result.put("errcode", "500"); result.put("errmsg", "unsuccessful"); } } return result; } public String getSelOtherObject(HashMap hmwherefld, MboSetRemote mxattrSets) throws UnsupportedEncodingException, RemoteException, MXException { String selOtherObject = ""; int mloop = mxattrSets.count(); for (int i = 0; i < mloop; i++) { MboRemote mxattrmbo = mxattrSets.getMbo(i); String maxrel = mxattrmbo.getString("HIDDENCONDITION"); maxrel = (maxrel == null) ? "" : maxrel.trim(); if (maxrel.length() > 0) { String alias1 = mxattrmbo.getString("ALIAS1");//主对象 alias1 = (alias1 == null) ? "" : alias1.trim(); alias1 = alias1.toUpperCase(); String hmvalues = hmwherefld.get(alias1); hmvalues = (hmvalues == null) ? "" : hmvalues.trim(); if (hmvalues.length() == 0) continue; String child = mxattrmbo.getString("CHILD");//子对象 child = (child == null) ? "" : child.trim(); selOtherObject = child + " " + child.toLowerCase() + ","; } } return selOtherObject; } public String getSelRelwhereString(HashMap hmwherefld, MboSetRemote mxattrSets, MboSetRemote maxrelSets, HashMap hmkeyword) throws UnsupportedEncodingException, RemoteException, MXException { String relwhereString = ""; int mloop = mxattrSets.count(); //System.out.println("mloop =" + mloop); HashMap hmOneWhere = new HashMap<>(); for (int i = 0; i < mloop; i++) { MboRemote mxattrmbo = mxattrSets.getMbo(i); String maxrel = mxattrmbo.getString("HIDDENCONDITION"); maxrel = (maxrel == null) ? "" : maxrel.trim(); if (maxrel.length() > 0) { String alias1 = mxattrmbo.getString("ALIAS1");//主对象 alias1 = (alias1 == null) ? "" : alias1.trim(); alias1 = alias1.toUpperCase(); String hmvalues = hmwherefld.get(alias1); hmvalues = (hmvalues == null) ? "" : hmvalues.trim(); if (hmvalues.length() == 0) continue; String objectname = mxattrmbo.getString("OBJECTNAME");//主对象 objectname = (objectname == null) ? "" : objectname.trim(); String child = mxattrmbo.getString("CHILD");//子对象 child = (child == null) ? "" : child.trim(); maxrelSets.setWhere("NAME ='" + maxrel + "' and PARENT ='" + objectname + "' and CHILD ='" + child + "'"); maxrelSets.reset(); if (!maxrelSets.isEmpty()) { MboRemote maxrelmbo = maxrelSets.getMbo(0); String whereclause = maxrelmbo.getString("WHERECLAUSE");//WHERECLAUSE //System.out.println("whereclause = " + whereclause); whereclause = whereclause.replaceAll("\\(", " \\( "); whereclause = whereclause.replaceAll("\\)", " \\) "); whereclause = whereclause.replaceAll("=", " = "); String aWhereclause[] = whereclause.split(" "); int kloop = aWhereclause.length; //String lstfix = ""; String oneWhere = ""; for (int k = 0; k < kloop; k++) { String st = aWhereclause[k]; st = (st == null) ? "" : st.trim(); st = (st.length() == 0) ? " " : st; if (st.indexOf(":", 0) > -1) { oneWhere = oneWhere + st; } else { if (st.equalsIgnoreCase(" ")) { oneWhere = oneWhere + " "; } else { String values = (String) hmkeyword.get(st.toUpperCase()); values = (values == null) ? "" : values.trim(); //System.out.println("values = " + values); if (values.length() > 0) {//保留字 oneWhere = oneWhere + " " + st + " "; } else { oneWhere = oneWhere + child.toLowerCase() + "." + st; } } } } if (oneWhere.length() > 0) { oneWhere = oneWhere.replaceAll(":", "m."); hmOneWhere.put(oneWhere, oneWhere); } //relwhereString = (oneWhere.length() > 0)?relwhereString + oneWhere + " and ":relwhereString; } } } Iterator> iterator = hmOneWhere.entrySet().iterator(); while (iterator.hasNext()) { Map.Entry entry = iterator.next(); String oneWhere = entry.getKey(); relwhereString = (oneWhere.length() > 0) ? relwhereString + oneWhere + " and " : relwhereString; } relwhereString = (relwhereString.length() > " and ".length()) ? relwhereString.substring(0, relwhereString.length() - " and ".length()) : relwhereString; //System.out.println("relwhereString = " + relwhereString); return relwhereString; } public String replaceClientFld(Vector vOrderbyfld, MboSetRemote mxattrSets, String upOrderby, MboSetRemote maxrelSets) throws UnsupportedEncodingException, RemoteException, MXException { int uloop = vOrderbyfld.size(); //System.out.println("vOrderbyfld.size() =" + vOrderbyfld.size()); for (int u = 0; u < uloop; u++) { String sfld = (String) vOrderbyfld.elementAt(u); sfld = (sfld == null) ? "" : sfld.trim(); sfld = sfld.toUpperCase(); //System.out.println("sfld =" + sfld); if (sfld.length() == 0) continue; String replacefld = ""; int mloop = mxattrSets.count(); //System.out.println("mxattrSets.count() =" + mloop); for (int i = 0; i < mloop; i++) { MboRemote mxattrmbo = mxattrSets.getMbo(i); String tAlias1 = mxattrmbo.getString("ALIAS1"); tAlias1 = (tAlias1 == null) ? "" : tAlias1.trim(); tAlias1 = tAlias1.toUpperCase(); if (tAlias1.equalsIgnoreCase(sfld)) { String maxrel = mxattrmbo.getString("HIDDENCONDITION"); maxrel = (maxrel == null) ? "" : maxrel.trim(); String orgtype = mxattrmbo.getString("ORGTYPE");//类型 orgtype = (orgtype == null) ? "" : orgtype.trim(); String orgattr = mxattrmbo.getString("ORGATTR");//字段 orgattr = (orgattr == null) ? "" : orgattr.trim(); String fdefaultval = mxattrmbo.getString("defaultval");//Calculation formula fdefaultval = (fdefaultval == null) ? "" : fdefaultval.trim(); boolean isautocal = mxattrmbo.getBoolean("autocal");//自动计算 if (maxrel.length() == 0) { replacefld = "m." + orgattr; //System.out.println("replacefld 0 = " + replacefld); if (fdefaultval.length() > 0) { if (!isautocal) {//处理自动计算的 //处理字段本身有公式的 replacefld = (isNumberType(orgtype)) ? "NVL(" + fdefaultval + "(" + replacefld + "),0)" : fdefaultval + "(" + replacefld + ")"; } } else { replacefld = (isNumberType(orgtype)) ? "NVL(" + replacefld + ",0)" : replacefld; } break; } else { String child = mxattrmbo.getString("CHILD");//字段 child = (child == null) ? "" : child.trim(); replacefld = child.toLowerCase() + "." + orgattr; if (fdefaultval.length() > 0) { if (!isautocal) {//处理自动计算的 //处理字段本身有公式的 replacefld = (isNumberType(orgtype)) ? "NVL(" + fdefaultval + "(" + replacefld + "),0)" : fdefaultval + "(" + replacefld + ")"; } } else { replacefld = (isNumberType(orgtype)) ? "NVL(" + replacefld + ",0)" : replacefld; } break; } } } //System.out.println("replacefld=" + replacefld+ ",sfld =" +sfld + ",upOrderby =" + upOrderby); if (replacefld.length() > 0) { upOrderby = upOrderby.replaceAll(sfld, replacefld); } } return upOrderby; } /** * 写数据到外部文件BEGIN */ public String getselectfixByMaxRel(String maxrel, String objectname, String cfld, String orgattr, String child, MboSetRemote maxrelSets) throws UnsupportedEncodingException, RemoteException, MXException { String selectfix = ""; maxrelSets.setWhere("NAME ='" + maxrel + "' and PARENT ='" + objectname + "' and CHILD ='" + child + "'"); maxrelSets.reset(); if (!maxrelSets.isEmpty()) { MboRemote maxrelmbo = maxrelSets.getMbo(0); String whereclause = maxrelmbo.getString("WHERECLAUSE");//WHERECLAUSE String wherestr = whereclause.replaceAll(":", "m."); selectfix = "(select " + orgattr + " from " + child + " where " + wherestr + ")"; } return selectfix; } public String getAutoCalculate(String defaultval, String objectname, MboSetRemote mxattrSets, MboSetRemote maxrelSets, String cfld) throws RemoteException, MXException, UnsupportedEncodingException { defaultval = defaultval.toUpperCase(); String selectfix = ""; String aCalculator[] = {"\\+", "-", "\\*", "/"}; int tloop = aCalculator.length; String sDefaultval = defaultval; for (int t = 0; t < tloop; t++) { String rst = aCalculator[t]; //if(rst.length() == 2) rst = rst.substring(1,2); sDefaultval = sDefaultval.replaceAll(rst, ","); } sDefaultval = sDefaultval + ","; String aCalFld[] = sDefaultval.split(",");//字段分析出来 int nloop = aCalFld.length; for (int n = 0; n < nloop; n++) { String sfld = aCalFld[n]; sfld = (sfld == null) ? "" : sfld.trim(); sfld = sfld.toUpperCase(); //String rfld = ""; //System.out.println("sfld =" + sfld); int mloop = mxattrSets.count(); //System.out.println("mloop =" + mloop); for (int i = 0; i < mloop; i++) { MboRemote mxattrmbo = mxattrSets.getMbo(i); String tAlias1 = mxattrmbo.getString("ALIAS1"); tAlias1 = (tAlias1 == null) ? "" : tAlias1.trim(); tAlias1 = tAlias1.toUpperCase(); //System.out.println("tAlias1 =" + tAlias1); if (tAlias1.equalsIgnoreCase(sfld)) { //if(sfld.equalsIgnoreCase(tAlias1)){ //System.out.println("tAlias1 2 =" + tAlias1); String maxrel = mxattrmbo.getString("HIDDENCONDITION"); maxrel = (maxrel == null) ? "" : maxrel.trim(); String orgtype = mxattrmbo.getString("ORGTYPE");//类型 orgtype = (orgtype == null) ? "" : orgtype.trim(); String orgattr = mxattrmbo.getString("ORGATTR");//字段 orgattr = (orgattr == null) ? "" : orgattr.trim(); String fdefaultval = mxattrmbo.getString("defaultval");//Calculation formula fdefaultval = (fdefaultval == null) ? "" : fdefaultval.trim(); boolean isautocal = mxattrmbo.getBoolean("autocal");//自动计算 if (maxrel.length() == 0) { if (fdefaultval.length() > 0) { if (!isautocal) {//处理自动计算的 //处理字段本身有公式的 String calfix = (isNumberType(orgtype)) ? "NVL(" + fdefaultval + "(m." + orgattr + "),0)" : fdefaultval + "(m." + orgattr + ")"; defaultval = defaultval.replaceAll(sfld, calfix); } } else { String calfix = (isNumberType(orgtype)) ? "NVL(" + "m." + orgattr + ",0)" : "m." + orgattr; //selstr = (i < nloop - 1) ? selstr + calfix + " " + cfld + "," : selstr + calfix + " " + cfld;//处理主表中的字段 defaultval = defaultval.replaceAll(sfld, calfix); } } else { //处理关系 maxrelationship 脚本 if (fdefaultval.length() > 0) { orgattr = (isNumberType(orgtype)) ? "NVL(" + fdefaultval + "(" + orgattr + "),0)" : fdefaultval + "(" + orgattr + ")"; } else { orgattr = (isNumberType(orgtype)) ? "NVL(" + orgattr + ",0)" : "" + orgattr; } String child = mxattrmbo.getString("CHILD");//关系 child = (child == null) ? "" : child.trim(); String selrel = getselectfixByMaxRel(maxrel, objectname, cfld, orgattr, child, maxrelSets); //System.out.println("selrel =" + selrel); String calfix = (isNumberType(orgtype)) ? "NVL(" + selrel + ",0)" : selrel; //selstr = (i < nloop-1)?selstr + calfix + ",":selstr + calfix; defaultval = defaultval.replaceAll(sfld, calfix); } } } } return defaultval; } public boolean isNumberType(String maxtype) throws UnsupportedEncodingException, RemoteException, MXException { boolean isNumberType = false; String aMaxtype[] = {"AMOUNT", "BIGINT", "DECIMAL", "FLOAT", "INTEGER", "SMALLINT"}; int nloop = aMaxtype.length; for (int n = 0; n < nloop; n++) { String st = aMaxtype[n]; if (st.equalsIgnoreCase(maxtype)) { isNumberType = true; break; } } return isNumberType; } public static void main1(String[] args) { try { String aCalculator[] = {"\\+", "-", "\\*", "/"}; int tloop = aCalculator.length; //String sDefaultval = defaultval; for (int t = 0; t < tloop; t++) { String rst = aCalculator[t]; if (rst.length() == 2) rst = rst.substring(1, 2); System.out.println("rst = " + rst); } } catch (Exception ex) { ex.printStackTrace(); } } // 判断字符串是否包含中文或英文逗号 private static boolean containsChineseOrComma(String input) { String regex = ".*[\\u4e00-\\u9fa5，,].*"; Pattern pattern = Pattern.compile(regex); Matcher matcher = pattern.matcher(input); return matcher.matches(); } // 使用正则表达式进行分隔操作 private static String[] splitString(String input) { return input.split("[\\u4e00-\\u9fa5，,]"); } public static void main(String[] args) throws Exception { //String test = "-1+-2.3-2+-2"; //test = test.replaceAll("\\+-", "-"); //System.out.println(test); String aCalculator[] = {"\\+", "-", "\\*", "/"}; int tloop = aCalculator.length; String sDefaultval = "a + b * c"; for (int t = 0; t < tloop; t++) { String rst = aCalculator[t]; //if(rst.length() == 2) rst = rst.substring(1,2); sDefaultval = sDefaultval.replaceAll(rst, ","); } System.out.println(sDefaultval); } }