| | |
|---|
| | | package com.smtaiserver.smtaiserver.javaai.qwen.agent.script;
|
|---|
| | |
|
|---|
| | | import java.util.ArrayList;
|
|---|
| | | import java.util.HashSet;
|
|---|
| | | import java.util.List;
|
|---|
| | | import java.util.Map.Entry;
|
|---|
| | |
|
|---|
| | |
|---|
| | | // 返回:
|
|---|
| | | // 将查询后的结果返回
|
|---|
| | | // 格式：[{字段1:值1, 字段2:值2},{字段1:值3, 字段2:值4}...]
|
|---|
| | | public NativeArray queryRecord(String tableName, NativeArray arrColumns, NativeObject arrTimeGroup, NativeArray arrOrderColumns, NativeObject arrLimit, NativeArray arrFilters) throws Exception
|
|---|
| | | public NativeArray queryRecord(String tableName, NativeArray arrColumns, NativeObject nvTimeGroup, NativeArray arrOrderColumns, NativeObject nvLimit, NativeArray arrFilters) throws Exception
|
|---|
| | | {
|
|---|
| | | StringBuilder sbSQL = new StringBuilder();
|
|---|
| | | sbSQL.append("SELECT ");
|
|---|
| | | 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)
|
|---|
| | | sbSQL.append(",");
|
|---|
| | | sbSQL.append(colName);
|
|---|
| | | 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_pressure".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))
|
|---|
| | | {
|
|---|
| | | 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");
|
|---|
| | | }
|
|---|
| | | }
|
|---|
| | |
|
|---|
| | | |
|---|
| | | return null;
|
|---|
| | | // 加入排序信息
|
|---|
| | | 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");
|
|---|
| | |
|
|---|
| | | if(sbOrder.length() > 0)
|
|---|
| | | sbSQL.append("ORDER BY " + sbOrder.toString() + "\n");
|
|---|
| | | |
|---|
| | | if(sbLimit.length() > 0)
|
|---|
| | | sbSQL.append(" " + sbLimit.toString());
|
|---|
| | | |
|---|
| | | 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 timeStep = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "time", null);
|
|---|
| | | |
|---|
| | | if(SMTStatic.isNullOrEmpty(timeStep))
|
|---|
| | | {
|
|---|
| | | sql = "SELECT " + funcAgg + "(" + colAgg + ") AS " + colAgg + " FROM (" + sql + ") T";
|
|---|
| | | }
|
|---|
| | | else
|
|---|
| | | {
|
|---|
| | | sql = |
|---|
| | | " SELECT time_bucket('" + timeStep + "', " + colTime + ") AS " + colTime + "," + funcAgg + "(" + colAgg + ") AS " + colAgg |
|---|
| | | + " FROM (" + sql + ") T"
|
|---|
| | | + " GROUP BY time_bucket('" + timeStep + "', " + colTime + ")"
|
|---|
| | | ;
|
|---|
| | | }
|
|---|
| | | }
|
|---|
| | | |
|---|
| | | |
|---|
| | | 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.getValue(entry.getValue()));
|
|---|
| | | }
|
|---|
| | | return true;
|
|---|
| | | }
|
|---|
| | | |
|---|
| | | });
|
|---|
| | | |
|---|
| | | return new NativeArray(recList.toArray(new NativeObject[recList.size()]));
|
|---|
| | | }
|
|---|
| | |
|
|---|
| | | public NativeArray queryRecord1(String tableName, NativeArray arrColumns, NativeObject arrTimeGroup, NativeArray arrOrderColumns, NativeObject arrLimit, NativeArray arrFilters) throws Exception
|
|---|
| | | {
|
|---|
| | | if(tableName.startsWith("dev_names"))
|
|---|
| | | {
|
|---|
| | | NativeObject nvRec = new NativeObject();
|
|---|
| | | SMTAIServerApp.putJSNotNullValue(nvRec, "QUOTA_TYPE", "SDVAL_FLOW_W");
|
|---|
| | | SMTAIServerApp.putJSNotNullValue(nvRec, "QUOTA_NAME", "D_QSS130_LL");
|
|---|
| | | return new NativeArray(new Object[] {nvRec});
|
|---|
| | | }
|
|---|
| | | else
|
|---|
| | | {
|
|---|
| | | SMTDatabase db = _dbMap.getDatabase("DS_43");
|
|---|
| | | {
|
|---|
| | | StringBuilder sbSQL = new StringBuilder();
|
|---|
| | | sbSQL.append("SELECT ");
|
|---|
| | | for(int i = 0; i < arrColumns.size(); i ++)
|
|---|
| | | {
|
|---|
| | | String colName = SMTStatic.toString(SMTAIServerApp.unwrapObject(arrColumns.get(i)));
|
|---|
| | | if(i > 0)
|
|---|
| | | sbSQL.append(",");
|
|---|
| | | sbSQL.append(colName);
|
|---|
| | | }
|
|---|
| | | sbSQL.append(" FROM (SELECT otype as quota_type, oname as quota_name, oname as QUOTA_TITLE, OTIME, SDVAL from sd_values.sd_raws_match) T");
|
|---|
| | | |
|---|
| | | if(arrFilters != null && arrFilters.size() > 0)
|
|---|
| | | {
|
|---|
| | | sbSQL.append(" WHERE ");
|
|---|
| | | |
|---|
| | | for(int i = 0; i < arrFilters.size(); i ++)
|
|---|
| | | {
|
|---|
| | | if(i > 0)
|
|---|
| | | sbSQL.append(" AND ");
|
|---|
| | | |
|---|
| | | NativeObject nvFilter = (NativeObject) SMTAIServerApp.unwrapObject(arrFilters.get(i));
|
|---|
| | | |
|---|
| | | String field = (String) SMTAIServerApp.getJSValue(nvFilter, "col");
|
|---|
| | | String op = (String) SMTAIServerApp.getJSValue(nvFilter, "op");
|
|---|
| | | |
|---|
| | | if("BETWEEN".equals(op))
|
|---|
| | | {
|
|---|
| | | NativeArray arrBetween = (NativeArray)SMTAIServerApp.getJSValue(nvFilter, "value");
|
|---|
| | | sbSQL.append(field);
|
|---|
| | | sbSQL.append(" BETWEEN ");
|
|---|
| | | String value1 = (String)SMTAIServerApp.unwrapObject(arrBetween.get(0));
|
|---|
| | | sbSQL.append("'" + value1.replace("'", "''") + "'") ;
|
|---|
| | | |
|---|
| | | sbSQL.append(" AND ");
|
|---|
| | | String value2 = (String)SMTAIServerApp.unwrapObject(arrBetween.get(1));
|
|---|
| | | sbSQL.append("'" + value2.replace("'", "''") + "'") ;
|
|---|
| | | }
|
|---|
| | | else
|
|---|
| | | {
|
|---|
| | | String value = (String)SMTAIServerApp.getJSValue(nvFilter, "value");
|
|---|
| | | sbSQL.append(field);
|
|---|
| | | sbSQL.append(op);
|
|---|
| | | sbSQL.append("'" + value.replace("'", "''") + "'");
|
|---|
| | | }
|
|---|
| | | }
|
|---|
| | | |
|---|
| | | }
|
|---|
| | | |
|---|
| | | List<NativeObject> listRec = new ArrayList<>();
|
|---|
| | | db.querySQLNotify(sbSQL.toString(), null, new DBQueryNotify() |
|---|
| | | {
|
|---|
| | | @Override
|
|---|
| | | public boolean onNextRecord(DBRecord rec) throws Exception |
|---|
| | | {
|
|---|
| | | NativeObject nvRec = new NativeObject();
|
|---|
| | | |
|---|
| | | for(Entry<String, Integer> entry : rec.getFieldMap().entrySet())
|
|---|
| | | {
|
|---|
| | | String value = rec.getString(entry.getValue());
|
|---|
| | | if(value == null)
|
|---|
| | | continue;
|
|---|
| | | SMTAIServerApp.putJSNotNullValue(nvRec, entry.getKey(), value);
|
|---|
| | | }
|
|---|
| | | listRec.add(nvRec);
|
|---|
| | | |
|---|
| | | return true;
|
|---|
| | | }
|
|---|
| | | |
|---|
| | | });
|
|---|
| | | return new NativeArray(listRec.toArray(new NativeObject[listRec.size()]));
|
|---|
| | | }
|
|---|
| | | |
|---|
| | |
|
|---|
| | | }
|
|---|
| | | }
|
|---|
| | |
|
|---|
| | | // outputTimeChart(记录集, 名称字段名, 时间字段名, 值字段名,标题名)
|
|---|
| | | // 功能:
|
|---|
| | |
|---|
| | | for(int i = 0; i < arrColNames.size(); i ++)
|
|---|
| | | {
|
|---|
| | | String colName = (String)SMTAIServerApp.unwrapObject(arrColNames.get(i));
|
|---|
| | | String colTitle = (String)SMTAIServerApp.unwrapObject(arrColTitles.get(i));
|
|---|
| | | String colTitle = arrColTitles == null ? colName : (String)SMTAIServerApp.unwrapObject(arrColTitles.get(i));
|
|---|
| | |
|
|---|
| | | jsonWrResult.beginMap(null);
|
|---|
| | | {
|
|---|
