You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
275 lines
14 KiB
275 lines
14 KiB
package com.fr.plugin.db.procedure.controller; |
|
|
|
import com.fr.base.ParameterMapNameSpace; |
|
import com.fr.base.StoreProcedureParameter; |
|
import com.fr.data.core.DataCoreUtils; |
|
import com.fr.data.core.db.TableProcedure; |
|
import com.fr.data.core.db.dialect.base.key.check.DataBaseDetail; |
|
import com.fr.data.core.db.dialect.base.key.check.DataBaseType; |
|
import com.fr.data.impl.NameDatabaseConnection; |
|
import com.fr.data.impl.storeproc.ProcedureDataModel; |
|
import com.fr.data.impl.storeproc.StoreProcedure; |
|
import com.fr.data.operator.DataOperator; |
|
import com.fr.decision.webservice.Response; |
|
import com.fr.decision.webservice.annotation.LoginStatusChecker; |
|
import com.fr.design.data.datapane.TableDataTreePane; |
|
import com.fr.design.data.datapane.connect.ConnectionTableProcedurePane; |
|
import com.fr.design.data.tabledata.tabledatapane.ProcedureDataPane; |
|
import com.fr.design.mainframe.DesignerFrameFileDealerPane; |
|
import com.fr.file.ConnectionConfig; |
|
import com.fr.json.JSONObject; |
|
import com.fr.log.FineLoggerFactory; |
|
import com.fr.plugin.context.PluginContexts; |
|
import com.fr.plugin.db.procedure.designframe.widgets.ConnectionCombox; |
|
import com.fr.plugin.db.procedure.entity.TemplateProcedureDBAUtil; |
|
import com.fr.plugin.db.procedure.entity.TemplateProcedureEntity; |
|
import com.fr.plugin.db.procedure.stableKey.ExecuteProcedureAction; |
|
import com.fr.script.Calculator; |
|
import com.fr.stable.ArrayUtils; |
|
import com.fr.stable.StringUtils; |
|
import com.fr.third.alibaba.druid.support.json.JSONUtils; |
|
import com.fr.third.springframework.stereotype.Controller; |
|
import com.fr.third.springframework.web.bind.annotation.RequestBody; |
|
import com.fr.third.springframework.web.bind.annotation.RequestMapping; |
|
import com.fr.third.springframework.web.bind.annotation.RequestMethod; |
|
import com.fr.third.springframework.web.bind.annotation.ResponseBody; |
|
import com.fr.web.core.SessionPoolManager; |
|
import com.fr.web.core.WidgetSessionIDInfor; |
|
import com.fr.workspace.WorkContext; |
|
|
|
import javax.servlet.http.HttpServletRequest; |
|
import javax.servlet.http.HttpServletResponse; |
|
import java.util.*; |
|
|
|
// FR.executeProcedure("getDecisionLog",{}) |
|
//http://127.0.0.1:8080/webroot/decision/view/report?viewlet=%E6%A0%87%E5%87%86%E5%9C%B0%E5%9B%BE.frm |
|
@Controller |
|
@LoginStatusChecker( |
|
required = false |
|
) |
|
public class DbController1 { |
|
|
|
|
|
public static String beginExecuteProcedure(HttpReqBean reqBean ) throws Exception { |
|
HashMap jsonObject = new HashMap(); |
|
FineLoggerFactory.getLogger().info("executeProcedure:procedureId="+reqBean.getProcedureId()+",reportSessionID="+reqBean.getReportSessionID()+",otherPara="+reqBean.getOtherPara()); |
|
TemplateProcedureEntity entity = TemplateProcedureDBAUtil.getInstance().getEntityByprocedureId(reqBean.getProcedureId()); |
|
if(entity == null){ |
|
jsonObject.put("errorCode","100000"); |
|
jsonObject.put("errorMsg","没有找到"+reqBean.getProcedureId()+"对应的配置"); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
else{ |
|
FineLoggerFactory.getLogger().info("entity info:"+entity.toString()); |
|
WidgetSessionIDInfor widgetSessionIDInfor = SessionPoolManager.getSessionIDInfor(reqBean.getReportSessionID(), WidgetSessionIDInfor.class); |
|
if(widgetSessionIDInfor == null){ |
|
jsonObject.put("errorCode","100005"); |
|
jsonObject.put("errorMsg","模板session过期了"); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
String templatePath = "reportlets/"+ widgetSessionIDInfor.getRelativePath(); |
|
FineLoggerFactory.getLogger().info("templatePath:"+templatePath); |
|
if(templatePath.equals(entity.getReportPath()) == false){ |
|
jsonObject.put("errorCode","100001"); |
|
jsonObject.put("errorMsg","模板路径对不上"); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
String connectName = entity.getConnectionName(); |
|
FineLoggerFactory.getLogger().info("配置信息为:templatePath="+templatePath+",connectName="+connectName+",procedureName:"+entity.getProcedureName()); |
|
|
|
ArrayList<String> connectNames = ConnectionCombox.getConnectNames(); |
|
boolean isExist = false; |
|
for(String remoteConnectName : connectNames){ |
|
if(remoteConnectName.equals(connectName)){ |
|
isExist = true; |
|
} |
|
} |
|
|
|
FineLoggerFactory.getLogger().info("当初配置的connect现在还存在吗:"+isExist); |
|
if(isExist == false){ |
|
jsonObject.put("errorCode","100002"); |
|
jsonObject.put("errorMsg","数据连接:"+connectName+",已经不存在了"); |
|
return JSONUtils.toJSONString(jsonObject); |
|
|
|
} |
|
com.fr.data.impl.Connection datasource = ConnectionConfig.getInstance().getConnection(connectName); |
|
String[] schemas = DataCoreUtils.getDatabaseSchema(datasource); |
|
boolean isOracleSystemSpace = true;//DesignerEnvManager.getEnvManager().isOracleSystemSpace(); |
|
DataBaseDetail detail = DataOperator.getInstance().getDataBaseDetail(datasource, isOracleSystemSpace); |
|
if (ArrayUtils.isNotEmpty(detail.getSchemas())) { |
|
schemas = detail.getSchemas(); |
|
} |
|
|
|
boolean isOracle = DataBaseType.ORACLE.equals(detail.getType()); |
|
FineLoggerFactory.getLogger().info("isOracle:"+isOracle); |
|
boolean isOracleSysSpace = true;//DesignerEnvManager.getEnvManager().isOracleSystemSpace(); |
|
List sqlTablees = DataCoreUtils.getProcedures(datasource, schemas, isOracle, isOracleSysSpace); |
|
FineLoggerFactory.getLogger().info("sqlTablees length is:"+sqlTablees.size()); |
|
TableProcedure[] tableProcedures = (TableProcedure[])sqlTablees.get(0); |
|
FineLoggerFactory.getLogger().info("tableProcedures length is :"+tableProcedures.length); |
|
TableProcedure tableProcedure = null; |
|
for(TableProcedure p : tableProcedures){ |
|
FineLoggerFactory.getLogger().info("tableProcedure name :"+p.getName()); |
|
if(p.getName().equals(entity.getProcedureName())){ |
|
tableProcedure = p; |
|
} |
|
} |
|
if(tableProcedure == null){ |
|
jsonObject.put("errorCode","100003"); |
|
jsonObject.put("errorMsg","找不到存储过程:"+entity.getProcedureName()); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
|
|
String procedureText = tableProcedure.toString();//DataOperator.getInstance().getProcedureText(connectName, tableProcedure.toString()); |
|
String proceduceContent = DataOperator.getInstance().getProcedureText(connectName, tableProcedure.toString()); |
|
FineLoggerFactory.getLogger().info(entity.getProcedureName()+"的内容为:"+proceduceContent); |
|
String parameterDefaultValue = ""; |
|
if (StringUtils.isNotEmpty(procedureText)) { |
|
int index_begin = procedureText.indexOf("BEGIN"); |
|
String defaulValueStr = index_begin == -1 ? procedureText : procedureText.substring(0, index_begin); |
|
String[] strs = defaulValueStr.split("'"); |
|
parameterDefaultValue = strs.length > 1 ? strs[1] : parameterDefaultValue; |
|
} |
|
|
|
StoreProcedureParameter[] newparameters = DataOperator.getInstance().getStoreProcedureDeclarationParameters(connectName, tableProcedure.toString(), parameterDefaultValue); |
|
MyStoreProcedure sp = new MyStoreProcedure(); |
|
sp.setDatabaseConnection(new NameDatabaseConnection(connectName)); |
|
sp.setParameters(newparameters); |
|
sp.setQuery(procedureText); |
|
sp.setShare(false); |
|
sp.setMaxMemRowCount(200); |
|
sp.setCalculating(false); |
|
|
|
String otherParam = reqBean.getOtherPara(); |
|
LinkedHashMap reqMap = (LinkedHashMap)JSONUtils.parse(otherParam); |
|
Map parameterMap = new HashMap(); |
|
for(StoreProcedureParameter p:newparameters){ |
|
if(reqMap.containsKey(p.getName())){ |
|
parameterMap.put(p.getName(),reqMap.get(p.getName())); |
|
} |
|
else{ |
|
parameterMap.put(p.getName(),p.getValue()); |
|
} |
|
FineLoggerFactory.getLogger().info("存储过程参数:key="+p.getName()+",value="+parameterMap.get(p.getName())); |
|
} |
|
|
|
try{ |
|
//测试连接通不通 |
|
// boolean status = DataOperator.getInstance().testConnection(sp.getDatabaseConnection()); |
|
// if(status){ |
|
// sp.resetDataModelList(); |
|
|
|
// } |
|
// ProcedureDataModel[] dataModels = DesignTableDataManager.createLazyDataModel(sp, true); |
|
|
|
sp.setFirstExpand(false); |
|
Calculator var4 = Calculator.createCalculator(); |
|
ParameterMapNameSpace var5 = ParameterMapNameSpace.create(parameterMap); |
|
var4.pushNameSpace(var5); |
|
ProcedureDataModel[] result = sp.creatDataModel(var4); |
|
if(result != null ){ |
|
if(result.length > 0){ |
|
for(ProcedureDataModel dataModel : result){ |
|
int rowCount = dataModel.getRowCount(); |
|
if(rowCount > 0){ |
|
FineLoggerFactory.getLogger().info("结果数据有"+rowCount+"行"); |
|
int columnCount = dataModel.getColumnCount(); |
|
ArrayList<String> columnNames = new ArrayList(); |
|
for(int columnIndex = 0 ; columnIndex < columnCount;columnIndex++){ |
|
String columnName = dataModel.getColumnName(columnIndex); |
|
columnNames.add(columnName); |
|
FineLoggerFactory.getLogger().info("columnName:"+columnName); |
|
} |
|
|
|
for(int rowIndex = 0 ; rowIndex < rowCount;rowIndex++){ |
|
FineLoggerFactory.getLogger().info("显示第:"+(rowIndex+1)+"行数据"); |
|
String descn = ""; |
|
for(int columnIndex = 0 ; columnIndex < columnCount; columnIndex++){ |
|
|
|
descn+=(columnNames.get(columnIndex)+"="+dataModel.getValueAt(rowIndex,columnIndex)+","); |
|
} |
|
FineLoggerFactory.getLogger().info(descn); |
|
} |
|
} |
|
else{ |
|
FineLoggerFactory.getLogger().info("结果没数据"); |
|
} |
|
} |
|
ProcedureDataModel dataModel = result[0]; |
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result :"+dataModel.getColumnName(0)); |
|
} |
|
else{ |
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result length 0:"); |
|
} |
|
} |
|
|
|
FineLoggerFactory.getLogger().info("存储过程执行结果last result:"+result.length); |
|
|
|
} |
|
catch(Exception ex){ |
|
ex.printStackTrace(); |
|
jsonObject.put("errorCode","100004"); |
|
jsonObject.put("errorMsg",ex.getMessage()); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
// previewProcedureDataModel 的逻辑如下 |
|
// public ProcedureDataModel[] previewProcedureDataModel(StoreProcedure var1, Map var2, int var3) { |
|
// Calculator var4 = Calculator.createCalculator(); |
|
// ParameterMapNameSpace var5 = ParameterMapNameSpace.create(var2); |
|
// var4.pushNameSpace(var5); |
|
// return var1.creatDataModel(var4); |
|
// } |
|
|
|
|
|
// Object[] result = dialect.remoteProcedureCall(connection,entity.getProcedureName()); |
|
|
|
FineLoggerFactory.getLogger().info("result"); |
|
//TemplateFileTree |
|
// DesignerFrameFileDealerPane |
|
// TableDataTreePane |
|
// ConnectionTableProcedurePane |
|
// ProcedureDataPane |
|
// StoreProcedure |
|
//调用存储过程... |
|
} |
|
jsonObject.put("status","ok"); |
|
return JSONUtils.toJSONString(jsonObject); |
|
} |
|
|
|
@RequestMapping( |
|
value = {"/executeProcedure1"}, |
|
method = {RequestMethod.POST} |
|
) |
|
@ResponseBody |
|
public Response executeProcedure1(HttpServletRequest req, HttpServletResponse res, @RequestBody HttpReqBean reqBean ) throws Exception { |
|
if (PluginContexts.currentContext().isAvailable() == false) { |
|
return Response.error("100008","插件需要注册"); |
|
} |
|
if(WorkContext.getCurrent().isLocal()){ |
|
FineLoggerFactory.getLogger().info("本地开始执行存储过程"); |
|
String resultJsonStr = DbController.beginExecuteProcedure(reqBean); |
|
FineLoggerFactory.getLogger().info("本地存储过程执行状态结果:"+resultJsonStr); |
|
return Response.ok(resultJsonStr); |
|
} |
|
else{ |
|
FineLoggerFactory.getLogger().info("远程开始执行存储过程"); |
|
String jsonParam = reqBean.toJson(); |
|
FineLoggerFactory.getLogger().info("jsonParam:"+jsonParam); |
|
String data = WorkContext.getCurrent().get(ExecuteProcedureAction.class).execute(jsonParam); |
|
FineLoggerFactory.getLogger().info("远程存储过程执行状态结果:"+data); |
|
return Response.ok(data); |
|
} |
|
|
|
} |
|
|
|
@RequestMapping( |
|
value = {"/removeAll"}, |
|
method = {RequestMethod.GET} |
|
) |
|
@ResponseBody |
|
public Response removeAll(HttpServletRequest req, HttpServletResponse res) throws Exception { |
|
TemplateProcedureDBAUtil.getInstance().removeAll(); |
|
return Response.ok("ok"); |
|
} |
|
|
|
}
|
|
|