package com.smtaiserver.smtaiserver.javaai.qwen.agent.script;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.HashSet;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Map.Entry;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
import org.mozilla.javascript.Context;
|
import org.mozilla.javascript.ImporterTopLevel;
|
import org.mozilla.javascript.NativeArray;
|
import org.mozilla.javascript.NativeObject;
|
import org.mozilla.javascript.ScriptableObject;
|
|
import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
|
import com.smtaiserver.smtaiserver.core.SMTAIServerRequest;
|
import com.smtaiserver.smtaiserver.javaai.ast.ASTDBMap;
|
import com.smtservlet.util.Json;
|
import com.smtservlet.util.SMTJsonWriter;
|
import com.smtservlet.util.SMTStatic;
|
import com.smtservlet.util.SMTStatic.SMTCalcTime;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBQueryNotify;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecord;
|
|
public class SMTQwenAgentScriptScope extends ImporterTopLevel
|
{
|
private Json _jsonArgs;
|
private ASTDBMap _dbMap;
|
@SuppressWarnings("unused")
|
private SMTQwenAgentScriptJet _scriptJet;
|
private SMTAIServerRequest _tranReq;
|
|
private static Pattern _patTimeStep = java.util.regex.Pattern.compile("(\\d+)\\s+(days|years|months)");
|
|
|
public SMTQwenAgentScriptScope(Json jsonArgs, ASTDBMap dbMap, SMTQwenAgentScriptJet scriptJet, SMTAIServerRequest tranReq)
|
{
|
_dbMap = dbMap;
|
_jsonArgs = jsonArgs;
|
_scriptJet = scriptJet;
|
_tranReq = tranReq;
|
}
|
|
public void __init__(Context cx) throws Exception
|
{
|
// Define some global functions particular to the shell. Note
|
// that these functions are not part of ECMA.
|
initStandardObjects(cx, false);
|
|
String[] funcListArr = new String[]{
|
"getArgValue",
|
"queryRecord",
|
"outputTimeChart",
|
"outputTable",
|
"outputError",
|
"joinRecordset"
|
};
|
defineRunFunctions(this.getClass(), funcListArr);
|
}
|
|
protected void defineRunFunctions(Class<?> clz, String[] names)
|
{
|
defineFunctionProperties(names, clz, ScriptableObject.DONTENUM);
|
}
|
|
public String getArgValue(String key)
|
{
|
Json jsonValue = _jsonArgs.getJson(key);
|
|
return jsonValue.asString();
|
}
|
|
// joinRecordset(记录集1, 记录集2, 对比字段名, 相差的时间范围)
|
// 功能:
|
// 将两个记录集按照对比字段进行合并，合并后的结果是：[[记录集1中相同字段记录, 记录集2中相同字段记录], .....]
|
// 参数:
|
// 记录集1：由queryRecord函数返回的记录集1
|
// 记录集2：由queryRecord函数返回的记录集2
|
// 相差的时间范围 : 如果要join的两组记录并不是来自同一个时间范围的，则需要计算:记录集2的起始时间-记录集1的起始时间，用 1 months, 1 days等形式表示，如果相同则为null
|
// 返回：
|
// 合并后的结果集
|
// 格式:[[记录集1中相同字段记录, 记录集2中相同字段记录], .....]
|
public NativeArray joinRecordset(NativeArray arrRecs1, NativeArray arrRecs2, String keyColumn, String diffTime) throws Exception
|
{
|
Map<String, NativeObject[]> mapKey2Recs = new LinkedHashMap<>();
|
|
int timeStep = 0;
|
SMTCalcTime timeUnit = null;
|
if(!SMTStatic.isNullOrEmpty(diffTime))
|
{
|
Matcher m = _patTimeStep.matcher(diffTime);
|
if(!m.find())
|
throw new Exception("time step error : " + diffTime);
|
|
timeStep = SMTStatic.toInt(m.group(1));
|
String sUnit = m.group(2);
|
if("years".equals(sUnit))
|
timeUnit = SMTCalcTime.ADD_YEAR;
|
else if("days".equals(sUnit))
|
timeUnit = SMTCalcTime.ADD_DATE;
|
else if("months".equals(sUnit))
|
timeUnit = SMTCalcTime.ADD_MONTH;
|
else
|
throw new Exception("time step unsupport : " + diffTime);
|
}
|
|
// 加入第一个记录集
|
for(int i = 0; i < arrRecs1.size(); i ++)
|
{
|
NativeObject nvRec = (NativeObject) SMTAIServerApp.unwrapObject(arrRecs1.get(i));
|
String key = (String) SMTAIServerApp.getJSValue(nvRec, keyColumn);
|
mapKey2Recs.put(key, new NativeObject[] {nvRec, null});
|
}
|
|
// 加入第二个记录集
|
for(int i = 0; i < arrRecs2.size(); i ++)
|
{
|
NativeObject nvRec = (NativeObject) SMTAIServerApp.unwrapObject(arrRecs2.get(i));
|
String key = (String) SMTAIServerApp.getJSValue(nvRec, keyColumn);
|
|
if(timeStep != 0)
|
{
|
key = SMTStatic.toString(SMTStatic.calculateTime(SMTStatic.toDate(key), timeUnit, timeStep));
|
}
|
|
NativeObject[] arrRecs = mapKey2Recs.get(key);
|
if(arrRecs == null)
|
continue;
|
arrRecs[1] = nvRec;
|
}
|
|
// 生成最终结果
|
List<NativeArray> list = new ArrayList<>();
|
for(NativeObject[] recList : mapKey2Recs.values())
|
{
|
if(recList[1] == null)
|
continue;
|
list.add(new NativeArray(recList));
|
}
|
|
|
return new NativeArray(list.toArray(new NativeArray[list.size()]));
|
}
|
|
|
// queryRecord(表名, 字段列表, 时间分组, 排序字段列表, 限制记录信息, 查询过滤条件)
|
// 功能:
|
// 查询带时间范围的SQL语句，返回查询结果集。
|
// 参数:
|
// 表名: 要查询的表名
|
// 字段列表: 要查询的字段名列表: [字段1, 字段2, 字段3... ]，这里只能放字段名，不能放任何运算操作
|
// 时间分组: 用于聚合操作的时间分组。
|
// 格式：{"col":时间字段, "agg":聚合方式(例如:AVG,SUM,MAX,MIN等), "time":分组时间段(例如:1 hours, 1 days，如果问题中未提到时间步长，则不设置。禁止捏造不存在的时间步长)}，如果不存在，则设置为null
|
// 排序字段列表: 用于排序的字段及排序信息。
|
// 格式:[{"col":第一个排序字段名, "order":第一个排序字段顺序(ASC,DESC)], ....] ，如果不存在则设置为null
|
// 限制记录信息: 用于限制输出条目数的配置。
|
// 格式:{"start":限制记录的起始位置，"limit":限制记录的条目数}, 如果不存在则设置为null
|
// 查询过滤条件: 用于过滤查询SQL的条件信息
|
// 格式:[{"col":查询条件字段名1, "op":查询条件操作符1, "value":查询值1}, ....]，如果查询操作符是IN,BETWEEN之类有多个值的，那查询值就是["值1","值2"...]，否者查询值就是一个字符串。如果要查询时间，请直接指定查询时间的范围，不允许吧时间拆成年月日分别判断。
|
// 返回:
|
// 将查询后的结果返回
|
// 格式：[{字段1:值1, 字段2:值2},{字段1:值3, 字段2:值4}...]
|
public NativeArray queryRecord(String tableName, NativeArray arrColumns, NativeObject nvTimeGroup, NativeArray arrOrderColumns, NativeObject nvLimit, NativeArray arrFilters) throws Exception
|
{
|
String sqlColTime = null;
|
if(nvTimeGroup != null)
|
{
|
sqlColTime = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "col");
|
}
|
|
// 加入字段
|
StringBuilder sbColumn = new StringBuilder();
|
java.util.Set<String> setExistCols = new HashSet<>();
|
for(int i = 0; i < arrColumns.size(); i ++)
|
{
|
String colName = SMTStatic.toString(SMTAIServerApp.unwrapObject(arrColumns.get(i)));
|
setExistCols.add(colName.toUpperCase());
|
if(i > 0)
|
sbColumn.append(",");
|
sbColumn.append(colName);
|
}
|
|
if(sqlColTime != null && !setExistCols.contains(sqlColTime.toUpperCase()))
|
{
|
sbColumn.append("," + sqlColTime);
|
}
|
|
// 加入表名
|
StringBuilder sbTable = new StringBuilder();
|
|
boolean isDevice = false;
|
if("dev_names".equalsIgnoreCase(tableName))
|
{
|
tableName =
|
" (SELECT\r\n"
|
+ " '压力计' AS DEVICE_TYPE,\r\n"
|
+ " 'XXX' AS FACTORY,\r\n"
|
+ " N.title AS DEVICE_NAME,\r\n"
|
+ " Q.otype AS QUOTA_TYPE,\r\n"
|
+ " Q.oname AS QUOTA_NAME\r\n"
|
+ " FROM\r\n"
|
+ " (SELECT * FROM sd_names.sd_dev_pressure WHERE ver_timee = '5000-01-01'::timestamp) N\r\n"
|
+ " INNER JOIN\r\n"
|
+ " (SELECT * FROM sd_names.sd_quota_names WHERE ver_timee = '5000-01-01'::timestamp) Q\r\n"
|
+ " ON\r\n"
|
+ " N.otype = Q.ptype AND N.oname = Q.pname"
|
+ ") T";
|
isDevice = true;
|
}
|
else if("quota_value".equalsIgnoreCase(tableName))
|
{
|
tableName = "(SELECT otime, sdval, otype as QUOTA_TYPE, oname as QUOTA_NAME FROM sd_values.sd_raws_match) T";
|
}
|
|
sbTable.append(tableName);
|
|
// 加入过滤条件
|
StringBuilder sbWhere = new StringBuilder();
|
if(arrFilters != null && arrFilters.size() > 0)
|
{
|
for(int i = 0; i < arrFilters.size(); i ++)
|
{
|
if(sbWhere.length() > 0)
|
sbWhere.append(" AND ");
|
|
NativeObject nvFilter = (NativeObject) SMTAIServerApp.unwrapObject(arrFilters.get(i));
|
String colName = (String)SMTAIServerApp.getJSValue(nvFilter, "col");
|
String filterOP = (String)SMTAIServerApp.getJSValue(nvFilter, "op");
|
if("BETWEEN".equalsIgnoreCase(filterOP))
|
{
|
sbWhere.append(colName + " BETWEEN ");
|
NativeArray arrValues = (NativeArray)SMTAIServerApp.getJSValue(nvFilter, "value");
|
sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(0))).replace("'", "''") + "'");
|
sbWhere.append(" AND ");
|
sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(1))).replace("'", "''") + "'");
|
}
|
else
|
{
|
if(isDevice)
|
{
|
if("DEVICE_NAME".equalsIgnoreCase(colName))
|
{
|
if("IN".equalsIgnoreCase(filterOP))
|
{
|
NativeArray arrValues = (NativeArray)SMTAIServerApp.getJSValue(nvFilter, "value");
|
|
sbWhere.append("(");
|
for(int ii = 0; ii < arrValues.size(); ii ++)
|
{
|
if(ii > 0)
|
sbWhere.append(" OR ");
|
|
sbWhere.append("DEVICE_NAME LIKE '%");
|
sbWhere.append(SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(ii))).replace("'", "''"));
|
sbWhere.append("%'");
|
}
|
sbWhere.append(")");
|
}
|
else
|
{
|
sbWhere.append("DEVICE_NAME LIKE '%");
|
sbWhere.append(SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''"));
|
sbWhere.append("%'");
|
}
|
}
|
else if("QUOTA_TYPE".equalsIgnoreCase(colName))
|
{
|
sbWhere.append("QUOTA_TYPE = 'SDVAL_PRESS_W'");
|
}
|
else
|
{
|
sbWhere.append(colName + " " + filterOP + " ");
|
sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''") + "'");
|
}
|
}
|
else
|
{
|
sbWhere.append(colName + " " + filterOP + " ");
|
sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''") + "'");
|
}
|
}
|
sbWhere.append("\n");
|
}
|
}
|
|
// 加入排序信息
|
StringBuilder sbOrder = new StringBuilder();
|
if(arrOrderColumns != null)
|
{
|
for(int i = 0; i < arrOrderColumns.size(); i ++)
|
{
|
if(sbOrder.length() > 0)
|
sbOrder.append(",");
|
|
NativeObject nvOrderColumn = (NativeObject) SMTAIServerApp.unwrapObject(arrOrderColumns.get(i));
|
String colName = (String) SMTAIServerApp.getJSValue(nvOrderColumn, "col");
|
String orderDir= (String) SMTAIServerApp.getJSValue(nvOrderColumn, "order");
|
|
sbOrder.append(colName + " " + orderDir);
|
}
|
}
|
|
// 加入limit
|
StringBuilder sbLimit = new StringBuilder();
|
if(nvLimit != null)
|
{
|
SMTAIServerApp.getApp();
|
Object oLimit = SMTAIServerApp.getJSValue(nvLimit, "limit", null);
|
if(oLimit != null)
|
{
|
int limit = SMTStatic.toInt(oLimit);
|
sbLimit.append("LIMIT " + limit);
|
|
Object oStart = SMTAIServerApp.getJSValue(nvLimit, "start", null);
|
if(oStart != null)
|
sbLimit.append(" OFFSET " + SMTStatic.toInt(oStart));
|
}
|
}
|
|
// 整合SQL
|
StringBuilder sbSQL = new StringBuilder();
|
sbSQL.append("SELECT \n");
|
sbSQL.append(sbColumn.toString() + "\n");
|
sbSQL.append("FROM " + sbTable.toString() + "\n");
|
if(sbWhere.length() > 0)
|
sbSQL.append("WHERE " + sbWhere.toString() + "\n");
|
|
|
String sql = sbSQL.toString();
|
|
// {"col":时间字段, "agg":聚合方式(例如:AVG,SUM,MAX,MIN等), "time":分组时间段(例如:1 hours, 1 days，如果问题中未提到时间步长，则不设置。禁止捏造不存在的时间步长)}，如果不存在，则设置为null
|
if(nvTimeGroup != null)
|
{
|
String colTime = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "col");
|
String funcAgg = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "agg_func");
|
String colAgg = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "agg_col");
|
String aliasName = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "alias_col");
|
String timeStep = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "time", null);
|
|
if(SMTStatic.isNullOrEmpty(timeStep))
|
{
|
sql = "SELECT " + funcAgg + "(" + colAgg + ") AS " + aliasName + " FROM (" + sql + ") T";
|
}
|
else
|
{
|
sql =
|
" SELECT time_bucket('" + timeStep + "', " + colTime + ") AS " + colTime + "," + funcAgg + "(" + colAgg + ") AS " + aliasName
|
+ " FROM (" + sql + ") T"
|
+ " GROUP BY time_bucket('" + timeStep + "', " + colTime + ")"
|
;
|
}
|
sql = "SELECT * FROM (\n" + sql + "\n) T\n";
|
}
|
|
|
if(sbOrder.length() > 0)
|
sql += "ORDER BY " + sbOrder.toString() + "\n";
|
|
if(sbLimit.length() > 0)
|
sql += " " + sbLimit.toString();
|
|
|
SMTDatabase db = _dbMap.getDatabase("DS_43");
|
List<NativeObject> recList = new ArrayList<>();
|
System.out.println("=================>\n" + sql + "\n<====================\n");
|
db.querySQLNotify(sql, null, new DBQueryNotify() {
|
@Override
|
public boolean onNextRecord(DBRecord rec) throws Exception
|
{
|
NativeObject nvRec = new NativeObject();
|
recList.add(nvRec);
|
for(Entry<String, Integer> entry : rec.getFieldMap().entrySet())
|
{
|
SMTAIServerApp.putJSNotNullValue(nvRec,
|
entry.getKey(), rec.getString(entry.getValue()));
|
}
|
return true;
|
}
|
|
});
|
|
return new NativeArray(recList.toArray(new NativeObject[recList.size()]));
|
}
|
|
|
// outputTimeChart(记录集, 名称字段名, 时间字段名, 值字段名,标题名)
|
// 功能:
|
// 以曲线结果记录形式将多组同类型的记录集输出到客户
|
// 参数:
|
// 记录集: 要输出的由queryRecord函数返回的相同类型的记录集
|
// 名称字段名 : 曲线结果中代表指标名称的字段名
|
// 时间字段名 : 用于记录时间的字段名
|
// 值字段名: 用于记录值的字段名
|
// 标题名 : 当前输出曲线的标题名
|
// 返回:
|
// 无
|
public void outputTimeChart(NativeArray arrRecs, String nameColumn, String timeColumn, String valueColumn, String chartTitle) throws Exception
|
{
|
String title = (String) chartTitle;
|
|
|
SMTJsonWriter jsonWrResult = _tranReq.getResultJsonWr();
|
jsonWrResult.addKeyValue("answer_type", "summary");
|
jsonWrResult.beginArray("summary");
|
{
|
jsonWrResult.beginMap(null);
|
{
|
// 加入基本信息
|
jsonWrResult.addKeyValue("type", "recordset");
|
jsonWrResult.addKeyValue("agent_key", "#0/" + SMTStatic.newUUID());
|
jsonWrResult.addKeyValue("chart", "muli_line");
|
jsonWrResult.addKeyValue("title", title);
|
|
// 加入字段名
|
jsonWrResult.beginArray("cols");
|
{
|
jsonWrResult.beginMap(null);
|
{
|
jsonWrResult.addKeyValue("title", "时间");
|
jsonWrResult.addKeyValue("type", "time");
|
}
|
jsonWrResult.endMap();
|
|
jsonWrResult.beginMap(null);
|
{
|
jsonWrResult.addKeyValue("title", "名称");
|
jsonWrResult.addKeyValue("type", "name");
|
}
|
jsonWrResult.endMap();
|
|
jsonWrResult.beginMap(null);
|
{
|
jsonWrResult.addKeyValue("title", "值");
|
jsonWrResult.addKeyValue("type", "value");
|
}
|
jsonWrResult.endMap();
|
}
|
jsonWrResult.endArray();
|
|
// 加入值
|
jsonWrResult.beginArray("values");
|
for(int i = 0; i < arrRecs.size(); i ++)
|
{
|
NativeObject nvRec = (NativeObject)SMTAIServerApp.unwrapObject(arrRecs.get(i));
|
jsonWrResult.beginArray(null);
|
{
|
jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, timeColumn, null));
|
jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, nameColumn, null));
|
jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, valueColumn, null));
|
}
|
jsonWrResult.endArray();
|
}
|
jsonWrResult.endArray();
|
}
|
jsonWrResult.endMap();
|
}
|
jsonWrResult.endArray();
|
|
_tranReq.sendChunkedResultBlock();
|
}
|
|
public void outputError(String message) throws Exception
|
{
|
_tranReq.sendChunkedBlock("begin", message);
|
|
}
|
|
// outputTable(记录集, 表格标题名, 字段名列表, 字段标题列表, 分组字段名列表)
|
// 功能:
|
// 以表格形式输出记录集到客户
|
// 参数:
|
// 记录集: 要输出的由queryRecord函数返回的记录集
|
// 表格标题名 : 表格输出时候定义的名字
|
// 字段名列表 : 要输出的字段名列表，[字段1,字段2,...]
|
// 字段标题列表: 字段名对应的标题列表, 如果标题不存在，则用字段名填充 [标题1, 标题2...]
|
// 分组字段名列表: 如果记录集中包含分组字段，则输出分组字段列表，否则设置为null
|
// 返回:
|
// 无
|
public void outputTable(NativeArray arrRS, String tableTitle, NativeArray arrColNames, NativeArray arrColTitles, NativeArray arrGroupName) throws Exception
|
{
|
String title = tableTitle;
|
NativeArray arrRecs = arrRS;
|
|
SMTJsonWriter jsonWrResult = _tranReq.getResultJsonWr();
|
jsonWrResult.addKeyValue("answer_type", "summary");
|
jsonWrResult.beginArray("summary");
|
{
|
jsonWrResult.beginMap(null);
|
{
|
// 加入基本信息
|
jsonWrResult.addKeyValue("type", "recordset");
|
jsonWrResult.addKeyValue("agent_key", "#0/" + SMTStatic.newUUID());
|
jsonWrResult.addKeyValue("chart", "table");
|
jsonWrResult.addKeyValue("title", title);
|
|
// 加入字段列
|
List<String> listColName = new ArrayList<>();
|
jsonWrResult.beginArray("cols");
|
for(int i = 0; i < arrColNames.size(); i ++)
|
{
|
String colName = (String)SMTAIServerApp.unwrapObject(arrColNames.get(i));
|
String colTitle = arrColTitles == null ? colName : (String)SMTAIServerApp.unwrapObject(arrColTitles.get(i));
|
|
jsonWrResult.beginMap(null);
|
{
|
jsonWrResult.addKeyValue("title", colTitle);
|
jsonWrResult.addKeyValue("name", colName);
|
jsonWrResult.addKeyValue("type", "text");
|
jsonWrResult.addKeyValue("group", false);
|
listColName.add(colName);
|
}
|
jsonWrResult.endMap();
|
}
|
jsonWrResult.endArray();
|
|
// 加入值列
|
jsonWrResult.beginArray("values");
|
for(int i = 0; i < arrRecs.size(); i ++)
|
{
|
NativeObject nvRec = (NativeObject)SMTAIServerApp.unwrapObject(arrRecs.get(i));
|
jsonWrResult.beginArray(null);
|
{
|
for(int j = 0; j < listColName.size(); j ++)
|
{
|
String colName = listColName.get(j);
|
jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, colName, null));
|
}
|
}
|
jsonWrResult.endArray();
|
}
|
jsonWrResult.endArray();
|
}
|
jsonWrResult.endMap();
|
}
|
jsonWrResult.endArray();
|
|
_tranReq.sendChunkedResultBlock();
|
}
|
}
|
|
