package com.vci.server.query.refquery.utils;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.Collections;
|
import java.util.List;
|
|
import com.vci.corba.query.data.RefValue;
|
import com.vci.omd.constants.BusinessConstants;
|
import com.vci.omd.constants.LinkConstants;
|
import com.vci.server.base.utility.OmdHelper;
|
import com.vci.server.base.utility.ServerServiceProvider;
|
import com.vci.server.cache.OMCacheProvider;
|
import com.vci.server.query.refquery.objects.FromItem;
|
import com.vci.server.query.refquery.objects.MetaData;
|
import com.vci.server.query.refquery.objects.PLMRefQueryPath;
|
import com.vci.server.query.refquery.objects.Sequence;
|
import com.vci.corba.common.VCIError;
|
import com.vci.corba.omd.ltm.LinkType;
|
|
public class PLMRefSQLHelper {
|
public static String Space = " ";//
|
|
private static String generateEachOfPathToSubSQL(PLMRefQueryPath refpath,
|
List<FromItem> allfromitems) throws Throwable {
|
List<FromItem> fromitems = getEachOfPathFromItems(refpath, allfromitems);
|
Collections.sort(fromitems, new java.util.Comparator<FromItem>() {
|
@Override
|
public int compare(FromItem o1, FromItem o2) {
|
return o1.getLevel().compareTo(o2.getLevel());
|
}
|
|
});
|
return recursion(refpath, fromitems, 0);
|
}
|
|
private static String recursion(PLMRefQueryPath refpath,
|
List<FromItem> fromitems, int index) throws Throwable {
|
StringBuffer subSQL = new StringBuffer();
|
String priorAlias = "";
|
String curAlias = "";
|
FromItem leafItem = fromitems.get(fromitems.size() - 1);
|
List<String> absList = getAbsList(leafItem);
|
String key = refpath.getPath().path.substring(
|
refpath.getPath().path.lastIndexOf(".") + 1,
|
refpath.getPath().path.length());
|
for (int m = index; m < fromitems.size(); m++) {
|
int level = fromitems.get(m).getLevel();
|
priorAlias = curAlias;
|
if (level == 1) {
|
priorAlias = fromitems.get(m).getAlias();
|
curAlias = fromitems.get(m).getAlias();
|
continue;
|
}
|
if (level >= 2) {
|
curAlias = fromitems.get(m).getAlias();
|
priorAlias = fromitems.get(m - 1).getAlias();
|
// leaf node
|
if ((refpath.getLevel() - 1) == fromitems.get(m).getLevel()) {
|
boolean existed = false;
|
for(String ab : absList) {
|
if(ab.equalsIgnoreCase(key)) {
|
existed = true;
|
break;
|
}
|
}
|
StringBuffer sb = new StringBuffer();
|
sb.append("(");
|
sb.append(" Select ");
|
if(existed) {
|
sb.append(fromitems.get(m).getAlias()).append(".")
|
.append(key).append(" as ");
|
}else{
|
sb.append("'' as ");
|
}
|
sb.append(fromitems.get(m).getAlias()).append("_").append(key);
|
sb.append(" From ");
|
// 业务类型
|
if (fromitems.get(m).getMeta().isType()) {
|
String btmName = fromitems.get(m).getMeta().getRefType().name;
|
String tableName = getBtmTypeViewName(btmName);
|
sb.append(tableName);
|
} else {
|
sb.append(OmdHelper.getLTTableName(
|
fromitems.get(m).getMeta().getRefLink().name));
|
}
|
sb.append(Space).append(fromitems.get(m).getAlias());
|
sb.append(" Where ");
|
sb.append(curAlias)
|
.append(".oid ")
|
.append("=")
|
.append(priorAlias)
|
.append(".")
|
.append(fromitems.get(m).getMeta()
|
.getAttributeDef().name);
|
sb.append(") as ").append(fromitems.get(m).getAlias())
|
.append("_").append(key);
|
return sb.toString();
|
|
}
|
|
subSQL.append(" ( Select ");
|
String sql = recursion(refpath, fromitems, m + 1);
|
subSQL.append(sql);
|
subSQL.append(" From ");
|
// 业务类型
|
if (fromitems.get(m).getMeta().isType()) {
|
subSQL.append(OmdHelper.getBTTableName(
|
fromitems.get(m).getMeta().getRefType().name));
|
} else {
|
subSQL.append(OmdHelper.getLTTableName(
|
fromitems.get(m).getMeta().getRefLink().name));
|
}
|
subSQL.append(Space).append(fromitems.get(m).getAlias());
|
subSQL.append(" Where ");
|
subSQL.append(curAlias)
|
.append(".oid ")
|
.append("=")
|
.append(priorAlias)
|
.append(".")
|
.append(fromitems.get(m).getMeta().getAttributeDef().name);
|
subSQL.append(") as ").append(leafItem.getAlias()).append("_")
|
.append(key);
|
return subSQL.toString();
|
}
|
}
|
return subSQL.toString();
|
|
}
|
|
public static List<FromItem> generateFromItemList(PLMRefQueryPath[] refpaths) {
|
Sequence seq = new Sequence("a_0");
|
List<FromItem> fromitems = generateFromItems(refpaths, seq);
|
return fromitems;
|
}
|
|
public static String generateSQL(PLMRefQueryPath[] refpaths) {
|
Sequence seq = new Sequence("a_0");
|
List<FromItem> fromitems = generateFromItems(refpaths, seq);
|
return generateSQL(refpaths, fromitems);
|
}
|
|
public static String generateSQL(PLMRefQueryPath[] refpaths, List<FromItem> fromitems) {
|
String From = generateRootFrom(fromitems);
|
String Where = generateRootWhere(fromitems);
|
String Select = "";
|
try {
|
Select = generateRootSelect(refpaths, fromitems);
|
} catch (Throwable e) {
|
e.printStackTrace();
|
}
|
StringBuffer sb = new StringBuffer();
|
sb.append(Select);
|
sb.append(Space);
|
sb.append(From);
|
sb.append(Space).append(Where);
|
|
String refSQL = sb.toString();
|
return refSQL;
|
}
|
|
private static String generateRootFrom(List<FromItem> fromitems) {
|
StringBuffer sb = new StringBuffer();
|
sb.append(" From ");
|
for (FromItem item : fromitems) {
|
if (item.getLevel() == 1) {
|
// 判断是业务对象还是link对象
|
if (item.getMeta().isType()) {
|
String btmName = item.getMeta().getRefType().name;
|
String tableName = getBtmTypeViewName(btmName);
|
sb.append(tableName);
|
|
} else {
|
sb.append(OmdHelper.getLTTableName(
|
item.getMeta().getRefLink().name));
|
}
|
sb.append(Space + item.getAlias());
|
sb.append(", ");
|
break;
|
}
|
}
|
return sb.toString().substring(0, sb.lastIndexOf(", "));
|
}
|
|
/**
|
* 获取业务类型对应的视图名称,如果视图不存在则返回表名
|
* @param btmName
|
* @return
|
*/
|
private static String getBtmTypeViewName(String btmName) {
|
String[] childNames = null;
|
try {
|
childNames = ServerServiceProvider.getOMDService().getBTMService().getChildrenNames(btmName);
|
} catch (VCIError e) {
|
e.printStackTrace();
|
}
|
String tableName = "";
|
if (childNames != null && childNames.length != 0) {
|
tableName = OmdHelper.getBtViewName(btmName);
|
} else {
|
tableName = OmdHelper.getBTTableName(btmName);
|
}
|
return tableName;
|
}
|
|
public static String[] generateResultSetMetaData(PLMRefQueryPath[] refpaths) {
|
Sequence seq = new Sequence("a_0");
|
List<FromItem> fromitems = generateFromItems(refpaths, seq);
|
String[] columNames = new String[refpaths.length + 1];
|
FromItem first = null;
|
for (FromItem fitem : fromitems) {
|
if (fitem.getLevel() == 1) {
|
first = fitem;
|
break;
|
}
|
}
|
|
StringBuffer sb = new StringBuffer();
|
sb.append(first.getAlias()).append("_oid");
|
|
columNames[0] = sb.toString().toUpperCase();
|
|
for (int i = 0; i < refpaths.length; i++) {
|
String path = refpaths[i].getPath().path;
|
String key = path.substring(path.lastIndexOf(".") + 1,
|
path.length());
|
String alias = "";
|
if (refpaths[i].getLevel() == 1) {
|
alias = getFromItemAlias(fromitems, path);
|
columNames[i + 1] = (alias + "_" + "OID").toUpperCase();
|
} else {
|
alias = getFromItemAlias(fromitems,
|
path.substring(0, path.lastIndexOf(".")));
|
}
|
|
if (refpaths[i].getLevel() != 1) {
|
columNames[i + 1] = (alias + "_" + key).toUpperCase();
|
}
|
|
}
|
List<String> columns = new ArrayList<String>();
|
for (int j = 0; j < columNames.length; j++) {
|
if (!columns.contains(columNames[j]))
|
columns.add(columNames[j]);
|
}
|
return columns.toArray(new String[columns.size()]);
|
}
|
|
private static String generateRootSelect(PLMRefQueryPath[] refpaths,
|
List<FromItem> fromitems) throws Throwable {
|
FromItem first = null;
|
for (FromItem fitem : fromitems) {
|
if (fitem.getLevel() == 1) {
|
first = fitem;
|
break;
|
}
|
}
|
StringBuffer sb = new StringBuffer();
|
sb.append("Select ");
|
sb.append(first.getAlias()).append(".oid as ").append(first.getAlias())
|
.append("_oid ");
|
sb.append(", ");
|
for (int i = 0; i < refpaths.length; i++) {
|
String path = refpaths[i].getPath().path;
|
String key = path.substring(path.lastIndexOf(".") + 1,
|
path.length());
|
String alias = "";
|
if (refpaths[i].getLevel() == 1) {
|
alias = getFromItemAlias(fromitems, path);
|
} else {
|
alias = getFromItemAlias(fromitems,
|
path.substring(0, path.lastIndexOf(".")));
|
}
|
FromItem currentFitem = getFromItem(fromitems, alias);
|
List<String> absList = getAbsList(currentFitem);
|
if (currentFitem.getLevel() >= 2) {
|
|
String subsql = generateEachOfPathToSubSQL(refpaths[i],
|
fromitems);
|
sb.append(subsql);
|
} else {
|
//参照的第一层不拼接SQL
|
if (refpaths[i].getLevel() != 1) {
|
boolean existed = false;
|
for(String ab : absList) {
|
if(ab.equalsIgnoreCase(key)) {
|
existed = true;
|
break;
|
}
|
}
|
if(existed) {
|
sb.append(alias).append(".").append(key).append(" as ");
|
}else{
|
sb.append("'' as ");
|
}
|
sb.append(alias).append("_").append(key);
|
}
|
}
|
if (refpaths[i].getLevel() != 1) {
|
refpaths[i].setSelectKey(alias + "_" + key);
|
}
|
//参照的第一层不拼接SQL
|
if (refpaths[i].getLevel() != 1) {
|
sb.append(", ");
|
}
|
}
|
return sb.substring(0, sb.lastIndexOf(", "));
|
}
|
|
private static List<String> getAbsList(FromItem item) throws Throwable{
|
List<String> absList = new ArrayList<String>();
|
String[] LinkSysAbs = null;
|
String[] BtmSysAbs = null;
|
LinkSysAbs = LinkConstants.SELECT_CONSTANTS;
|
BtmSysAbs = BusinessConstants.SELECT_CONSTANTS;
|
List<String> linkSysAbsList = Arrays.asList(LinkSysAbs);
|
List<String> BtmSysAbsList = Arrays.asList(BtmSysAbs);
|
|
String name = null;
|
String[] abs = null;
|
if (item.getMeta().isType()) {
|
name = item.getMeta().getRefType().name;
|
//abs = ServerServiceProvider.getOMDService().getBTMService().getBtmApNameArray(name);
|
abs = OMCacheProvider.getBTAttributes(name);
|
absList.addAll(BtmSysAbsList);
|
} else {
|
name = item.getMeta().getRefLink().name;
|
//LinkType linkType = ServerServiceProvider.getOMDService().getLinkTypeService().getLinkType(name);
|
LinkType linkType = OMCacheProvider.getLinkType(name);
|
abs = linkType.attributes;
|
absList.addAll(linkSysAbsList);
|
}
|
absList.addAll(Arrays.asList(abs));
|
|
return absList;
|
}
|
|
private static String getFromItemAlias(List<FromItem> fromitems,
|
String indentifier) {
|
String alias = "";
|
for (FromItem fitem : fromitems) {
|
if (fitem.getIdentifier().equals(indentifier)) {
|
alias = fitem.getAlias();
|
break;
|
}
|
}
|
return alias;
|
|
}
|
|
private static FromItem getFromItem(List<FromItem> fromitems, String alias) {
|
for (FromItem fitem : fromitems) {
|
if (fitem.getAlias().equals(alias)) {
|
return fitem;
|
// break;
|
}
|
}
|
return null;
|
}
|
|
private static String generateRootWhere(List<FromItem> fromitems) {
|
StringBuffer sb = new StringBuffer();
|
sb.append(" Where ");
|
for (FromItem fItem : fromitems) {
|
int level = fItem.getLevel();
|
|
if (level == 1) {
|
sb.append(fItem.getAlias()
|
+ ".oid in "
|
+ getSqlInConditon(fItem.getPath().getPath().values, "'"));
|
// sb.append("order by instr('"
|
// + getSqlInConditon(fItem.getPath().getPath().values, "")
|
// + "'," + fItem.getAlias() + ".oid)");
|
|
}
|
|
}
|
return sb.toString();
|
}
|
|
private static String getSqlInConditon(RefValue[] oids, String invertedComma) {
|
StringBuilder in = new StringBuilder();
|
String value = "";
|
if (oids != null && oids.length > 0) {
|
in.append("(");
|
for (int i = 0; i < oids.length; i++) {
|
in.append(invertedComma);
|
in.append(oids[i].value);
|
in.append(invertedComma);
|
in.append(",");
|
}
|
value = in.substring(0, in.length() - 1);
|
value += ")";
|
}
|
return value;
|
}
|
|
private static List<FromItem> getEachOfPathFromItems(
|
PLMRefQueryPath refpath, List<FromItem> allFormItems) {
|
List<FromItem> fromItems = new ArrayList<FromItem>();
|
int i = refpath.getPath().path.split("\\.").length;
|
for (int j = i - 2; j >= 1; j--) {
|
String identifier = refpath.getlevel(j);
|
for (FromItem f : allFormItems) {
|
if (f.getIdentifier().equals(identifier)) {
|
fromItems.add(f);
|
break;
|
}
|
}
|
}
|
return fromItems;
|
}
|
|
private static List<FromItem> generateFromItems(PLMRefQueryPath[] refpaths,
|
Sequence seq) {
|
int maxPath = getMaxPathLevel(refpaths);
|
List<FromItem> fromItems = new ArrayList<FromItem>();
|
MetaData first = null;
|
PLMRefQueryPath firstRefPath = null;
|
for (PLMRefQueryPath refpathsindex : refpaths) {
|
int level = refpathsindex.getPath().path.split("\\.").length - 1;
|
if (level == 1) {
|
firstRefPath = refpathsindex;
|
break;
|
}
|
}
|
for (int i = 0; i < refpaths.length; i++) {
|
|
if (first == null) {
|
first = refpaths[i].getLevel(1);
|
FromItem item = new FromItem();
|
|
item.setMeta(first);
|
String alias = seq.getNextSequence();
|
item.setAlias(alias);
|
item.setLevel(1);
|
item.setIdentifier(firstRefPath.getlevel(1));
|
item.setPath(firstRefPath);
|
fromItems.add(item);
|
}
|
int n = 2;
|
while (n <= maxPath) {
|
MetaData second = refpaths[i].getLevel(n);
|
String cur = seq.getSequence();
|
if (second != null) {
|
FromItem item = new FromItem();
|
item.setMeta(second);
|
String alias = seq.getNextSequence();
|
item.setAlias(alias);
|
item.setLevel(n);
|
item.setPath(refpaths[i]);
|
item.setIdentifier(refpaths[i].getlevel(n));
|
if (!fromItems.contains(item)) {
|
fromItems.add(item);
|
} else {
|
seq.setSequence(cur);
|
}
|
n++;
|
} else {
|
break;
|
}
|
}
|
}
|
return fromItems;
|
}
|
|
private static int getMaxPathLevel(PLMRefQueryPath[] refPaths) {
|
int max = 0;
|
for (PLMRefQueryPath path : refPaths) {
|
if (path.getLevel() > max) {
|
max = path.getLevel();
|
}
|
}
|
for (PLMRefQueryPath path : refPaths) {
|
path.setMaxpath(max);
|
}
|
return max;
|
|
}
|
|
public static void main(String[] args) {
|
Sequence seq = new Sequence("a_0");
|
int i = 0;
|
while (i < 260) {
|
i++;
|
//System.out.println(seq.getNextSequence());
|
}
|
|
}
|
|
}
|