package com.vci.server.omd.common;
|
|
import java.text.DateFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.LinkedHashSet;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
import com.vci.corba.omd.atm.AttribItem;
|
import com.vci.omd.dataType.VTDataType;
|
import com.vci.server.base.utility.AttributeHelper;
|
import com.vci.server.base.utility.OmdHelper;
|
import com.vci.server.cache.OMCacheProvider;
|
|
|
|
public class OmdViewTool {
|
private static final String _TV = "_TV";
|
private static final String _FV = "_FV";
|
private static final String _V = "_V";
|
public static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
|
|
/**
|
* Array ----> ArrayList
|
* @param array
|
* @return
|
*/
|
public static List<String> arrayToList(String[] array){
|
List<String> list = new ArrayList<String>();
|
for(int i = 0; i < array.length; i++){
|
list.add(array[i]);
|
}
|
return list;
|
}
|
|
/**
|
* convert array to String append with ','.
|
* @param array
|
* @return
|
*/
|
public static String arrayToString(String[] array){
|
String str = "";
|
if(array == null){
|
return str;
|
}
|
for(int i = 0; i < array.length; i++){
|
str += array[i];
|
if(i < array.length - 1){
|
str += ",";
|
}
|
}
|
return str;
|
}
|
|
|
|
|
/**
|
* 获取链接类型TO端业务类型视图名
|
* @param ltName
|
* @return
|
*/
|
public static String getToViewName(String ltName){
|
return ltName + _TV;
|
}
|
|
/**
|
* 获取链接类型From端业务类型视图名
|
* @param ltName
|
* @return
|
*/
|
public static String getFromViewName(String ltName){
|
return ltName + _FV;
|
}
|
|
/**
|
* 获取业务类型视图名
|
* @param ltName
|
* @return
|
*/
|
public static String getBtViewName(String btName){
|
return btName + _V;
|
}
|
|
/**
|
* 获取创建链接类型TO端业务类型视图的sql
|
* @param ltName
|
* @return
|
*/
|
public static String getBTSViewSql(String[] btNames, String viewName){
|
if(btNames.length < 1){
|
return "";
|
}
|
StringBuilder stb = new StringBuilder("create or replace view ");
|
try {
|
Set<String> allAttrName = getUserAttrNameSet(btNames);
|
Map<String, StringBuilder> sqlMap = getBTSqlMap(allAttrName, btNames);
|
stb.append(viewName);
|
stb.append(" as ");
|
for(String btName : sqlMap.keySet()){
|
stb.append(sqlMap.get(btName));
|
stb.append(" union all ");
|
}
|
} catch (Throwable e) {
|
e.printStackTrace();
|
}
|
return stb.substring(0, stb.lastIndexOf(" union all "));
|
}
|
|
/**
|
* 获取业务类型属性总集合
|
* @param btNames
|
* @return
|
*/
|
private static Set<String> getUserAttrNameSet(String[] btNames){
|
Set<String> set = new LinkedHashSet<String>();
|
try{
|
for(String btName : btNames){
|
//String[] abNames = BtmHelper.getAbNames(btName);
|
String[] abNames = OMCacheProvider.getBTAttributes(btName);
|
for(String abName : abNames){
|
set.add(abName);
|
}
|
}
|
} catch (Throwable e) {
|
e.printStackTrace();
|
}
|
return set;
|
}
|
|
/**
|
* 获取各个业务类型查询属性总集合的sql
|
* 当业务业务类型中不包含某个属性attrA时, '' as attrA
|
* @param allAttrName
|
* @param btNames
|
* @return
|
*/
|
private static Map<String, StringBuilder> getBTSqlMap(Set<String> allAttrName,
|
String[] btNames) {
|
//记录业务类型和与其对应的属性列表
|
Map<String, List<String>> btAttrMap = new HashMap<String, List<String>>();
|
//记录业务类型和与其对应的查询sql
|
Map<String, StringBuilder> btSqlMap = new HashMap<String, StringBuilder>();
|
//modify by weidy@2021-11-15，在沈飞的发现一个现象是，拆分了的视图可能某个字段全部为null，这样多个视图链接到一起的时候，会出现类型不一致问题
|
String[] sysAbItems = OmdHelper.getBTSysANames();
|
String querySysAttrSql = "select " + arrayToString(sysAbItems);
|
for(String btName : btNames){
|
//String[] abNames = BtmHelper.getAbNames(btName);
|
String[] abNames = OMCacheProvider.getBTAttributes(btName);
|
|
btAttrMap.put(btName, arrayToList(abNames));
|
btSqlMap.put(btName, new StringBuilder(querySysAttrSql));
|
}
|
|
Map<String, String> mapAttrNull = new HashMap<String, String>();
|
|
for(String attrName : allAttrName){
|
for(String btName : btNames){
|
btSqlMap.get(btName).append(",");
|
if(btAttrMap.get(btName).contains(attrName)){
|
btSqlMap.get(btName).append(attrName);
|
}else{
|
String attrNull = "";
|
|
if (mapAttrNull.containsKey(attrName)) {
|
attrNull = mapAttrNull.get(attrName);
|
} else {
|
attrNull = CreateAttrNull(attrName);
|
mapAttrNull.put(attrName, attrNull);
|
}
|
btSqlMap.get(btName).append(attrNull);
|
}
|
}
|
}
|
|
for(String btName : btNames){
|
btSqlMap.get(btName).append(" from " + OmdHelper.getBTTableName(btName));
|
}
|
return btSqlMap;
|
}
|
|
/**
|
* 创建null AS 指定类型属性
|
* @param attrName
|
* @return
|
*/
|
private static String CreateAttrNull(String attrName) {
|
try {
|
|
AttribItem ai = OMCacheProvider.getAttribute(attrName);
|
|
String vtType = ai.vtDataType;
|
int length = 50;
|
String lengthStr = AttributeHelper.getOtherValueByType(ai.other, "length");
|
|
if ((lengthStr != null) && (!lengthStr.equals(""))) {
|
length = Integer.valueOf(lengthStr).intValue();
|
}
|
|
String sqlType = "";
|
|
if (VTDataType.VTSTRING.equalsIgnoreCase(vtType)) {
|
sqlType = "VARCHAR2(" + length + ")";
|
} else if (VTDataType.VTLONG.equalsIgnoreCase(vtType) || VTDataType.VTINTEGER.equalsIgnoreCase(vtType)) {
|
sqlType = "NUMBER";
|
} else if (VTDataType.VTDOUBLE.equalsIgnoreCase(vtType)) {
|
sqlType = "NUMBER";
|
} else if (VTDataType.VTBOOLEAN.equalsIgnoreCase(vtType)) {
|
sqlType = "VARCHAR2(8)";
|
} else if (VTDataType.VTIMAGE.equalsIgnoreCase(vtType)) {
|
sqlType = "VARCHAR2(255)";
|
} else if (VTDataType.VTDATE.equalsIgnoreCase(vtType)) {
|
sqlType = "DATE";
|
} else if (VTDataType.VTDATETIME.equalsIgnoreCase(vtType) || VTDataType.VTTIME.equalsIgnoreCase(vtType)) {
|
sqlType = "TIMESTAMP";
|
} else if (VTDataType.VTFILEPATH.equalsIgnoreCase(vtType) || VTDataType.VTNOTE.equalsIgnoreCase(vtType)) {
|
sqlType = "VARCHAR2(255)";
|
} else if (VTDataType.VTCLOB.equalsIgnoreCase(vtType)) {
|
sqlType = "CLOB";
|
} else {
|
sqlType = "VARCHAR2(" + length + ")";
|
}
|
|
// btSqlMap.get(btName).append("cast(null as " + sqlType + " ) as " + attrName);
|
String attrNull = "cast(null as " + sqlType + " ) as " + attrName;
|
|
return attrNull;
|
} catch (Throwable e) {
|
e.printStackTrace();
|
}
|
|
return "";
|
}
|
|
// public static String getOtherValueByType(String other, String type) {
|
// String[] otherArray = other.split(";");
|
// for (int i = 0; i < otherArray.length; i++) {
|
// String otherValue = otherArray[i];
|
// if (otherValue.contains(type)) {
|
// return otherValue.substring(otherValue.indexOf("=") + 2, otherValue.length());
|
// }
|
// }
|
// return null;
|
// }
|
|
/**
|
* 视图cols
|
* viewName.col as col2
|
* @param ltName
|
* @return
|
*/
|
public static List<String> getViewColsWithAlias(String[] btNames, String viewName) {
|
List<String> cols = new ArrayList<String>();
|
String[] sysAbItems = OmdHelper.getBTSysANames();
|
for(String sysAbItem : sysAbItems){
|
cols.add(viewName + "." + sysAbItem + " AS " + sysAbItem + "2");
|
}
|
Set<String> userAttrNameSet = getUserAttrNameSet(btNames);
|
for(String userAttrName : userAttrNameSet){
|
cols.add(viewName + "." + userAttrName + " AS " + userAttrName + "2");
|
}
|
return cols;
|
}
|
|
}
|
