package com.yc.service.datacheck;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.exception.ApplicationException;
|
import com.yc.service.BaseService;
|
import com.yc.service.grid.GridServiceIfc;
|
import com.yc.utils.GTJson;
|
import com.yc.utils.Panel;
|
import org.apache.commons.lang.RandomStringUtils;
|
import org.apache.commons.lang.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.jdbc.support.rowset.SqlRowSet;
|
import org.springframework.stereotype.Service;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.regex.Pattern;
|
|
@Service("DataCheck")
|
public class DataCheckImpl extends BaseService implements DataCheckIfc {
|
|
@Autowired
|
GridServiceIfc mGridServiceIfc;
|
|
/**
|
* 数据范围检查
|
*
|
* @param formid 功能号ID
|
* @param point 检查点：2表示保存时检查，3表示确定时检查
|
* @param check_Table 检查哪里数据(如表头，表格1)
|
* @return
|
*/
|
@Override
|
public List<Map<String, Object>> getSysdataRangeCheck(int formid, int point) {
|
|
|
String temp = "";
|
if (point != 3) {// 这为后考虑，即是3为确定，则保存也得检查，则下语句不要
|
temp = " and a.check_point=" + point;
|
}
|
String fild = "a.alter_sql,a.base_sql,"
|
+ "case when isnull(b.ErrorCode,0)<>0 "
|
+ "then b.errorname else a.alter_message end as alter_message,"
|
+ "a.upper_error_digit,a.lower_error_digit,a.upper_warn_digit,a.lower_warn_digit,a.check_Table ";
|
|
String sql = "\n set nocount on ; select case when isnull(b.ErrorCode,0) = 0 then 0 else 1 end as ok," + fild + ",b.errorname from _sysdata_range_check a left join _sys_DataErrorCode b " +
|
"\n on a.alter_message=cast(b.errorcode as varchar(20)) where isnull(b.inactived,0)=0" +
|
"\n and a.actived=1 and a.formid=" + formid + temp;
|
|
|
return this.jdbcTemplate.queryForList(sql);
|
}
|
|
/**
|
* 单据数据检查
|
*
|
* @param formid 功能号
|
* @param point 检查点：2表示保存时检查，3表示确定时检查
|
* @return
|
*/
|
public List<Map<String, Object>> getSysdatarange_Doccheck(int formid, int point) {
|
String sql1 = " set nocount on ; select formid,Langid,actived,DoccheckView,warningField,denyField,MsgField,check_point,memo from _sysdatarange_Doccheck where formid=" + formid + " and actived=1";// and check_point=" + point;
|
if (point != 3) {// 这为后考虑，即是3为确定，则保存也得检查，则下语句不要
|
sql1 += " and check_point=" + point;
|
}
|
return this.jdbcTemplate.queryForList(sql1);
|
}
|
|
/**
|
* 基本单据数据检查
|
*
|
* @param formid 功能号
|
* @param point 检查点：2表示保存时检查，3表示确定时检查
|
* @return
|
*/
|
public List<Map<String, Object>> getVsysDocDataCheckForm(int formid, int point) {
|
String sql1 = " set nocount on ; select formid,formname,Checkid,CheckName,Langid,actived,DoccheckView,warningField,denyField,MsgField,check_point,memo from vsysDocDataCheckForm where formid=" + formid + " and actived=1";
|
if (point != 3) {// 这为后考虑，即是3为确定，则保存也得检查，则下语句不要
|
sql1 += " and check_point=" + point;
|
}
|
return this.jdbcTemplate.queryForList(sql1);
|
}
|
|
public List<Map<String, Object>> getButtonCheck(int formid, String btnId, int point) {
|
// and isnull(ischeckdata,0)=1 暂时不知道检查什么了 先去掉
|
String sql = " set nocount on ; select buttonID,buttonName,formid,headflag,fieldid,parentdocitem,check_Table,alter_message,eMsgid,actived,alter_sql,\n" +
|
"alter_self_fields,alter_fields,base_sql,base_self_fields,base_fields,check_point,check_inserted,check_modified,\n" +
|
"check_unmodified,include_oldvalue,percent_flag,upper_error_digit,upper_warn_digit,lower_warn_digit,lower_error_digit,\n" +
|
"issum,memo,equal_error_digit,equal_warn_digit from _sys_OAButtonDataRangeCheck where actived=1 and buttonID='" + btnId + "' ";
|
if (point != 3) {// 这为后考虑，即是3为确定，则保存也得检查，则下语句不要
|
sql += " and isnull(check_point,2)=" + point;
|
}
|
return this.jdbcTemplate.queryForList(sql);
|
}
|
|
/**
|
* @param check
|
* @return
|
*/
|
private String getCheck(int check) {// 检查类型
|
String str = "";
|
switch (check) {
|
case 1:// 未改
|
str = " and check_unmodified = 1 ";
|
break;
|
case 2:// 新增
|
str = " and check_inserted = 1 ";
|
break;
|
case 3:// 修改
|
str = " and check_modified = 1 ";
|
break;
|
default:
|
break;
|
}
|
return str;
|
}
|
|
|
/**
|
* 得到错误信息集合
|
*
|
* @param formid 功能号ID
|
* @param check 1：未改 2：新增 3： 修改
|
* @param point 检查点：2表示保存时检查，3表示确定时检查 *
|
* @return
|
* @throws Exception
|
*/
|
@Override
|
public String getErrorList(int formid, int point, GTJson json,
|
String dcPagenum, int cont, HashMap<String, String> sessionClone) throws DataAccessException {
|
StringBuffer sBuffer = new StringBuffer();
|
sBuffer.append(
|
"\r\n ---------start 数据范围检查--------------- " +
|
"\t\n set nocount on ; declare @myTotalCount" + cont + " int ,@ErrorCount" + cont + " int,@warnCount" + cont + " int ");
|
sBuffer.append(
|
"\r\n declare @Table" + cont + " table (warnflag int,errorflag int,infomessage varchar(4000) ) ");
|
sBuffer.append("\r\n declare @ErrMsgs" + cont + " varchar(max) ");
|
//int check_Table = 0;//检查哪个表
|
List<Map<String, Object>> listSaveChecked = null;
|
List<Map<String, Object>> listDoc = null;
|
List<Map<String, Object>> listVDoc = null;
|
List<Map<String, Object>> btnCheck = null;
|
//String sql1 = "";
|
String[] tableNameList;//当前功能号的表名称列表
|
Map<String, String> headMap=json.getPanelRecords().size()>0?json.getPanelRecords().get(0):null;//表头数据
|
|
//1，-----------根据formid,取出对应的表名称
|
|
String sql = " set nocount on ; declare @formid int=?\n";
|
if ("499".equalsIgnoreCase(json.getgType()) || "497".equalsIgnoreCase(json.getgType())) {// 多表
|
sql += " select stuff((SELECT ',' + CONVERT(VARCHAR, isnull(b.hdtable,'')) from _sys_TabPageFormid a left join gform b on a.formid=b.formid where a.mainformid=@formid and a.Actived=1 order by a.SortBy asc FOR XML PATH ('')),1,1,'')";
|
for(Panel panel:json.getPanels()) {
|
if(panel.getIsPrimaryPan()==1) {//取主面板的表头
|
headMap = panel.getPanelRecords();
|
break;
|
}
|
}
|
} else if ("15".equalsIgnoreCase(json.getgType())) {//3表
|
sql += " declare @threeTable varchar(50)\n" +
|
" select @threeTable=b.hdtable from _sysmasterdetail a left join gform b on a.DetailFormID=b.formid where a.formid=@formid\n" +
|
" select stuff((SELECT ',' + CONVERT(VARCHAR, isnull(b.hdtable,''))+','+CONVERT(VARCHAR, isnull(b.dttable,''))+','+CONVERT(VARCHAR, @threeTable) from gform b where formid=@formid FOR XML PATH ('')),1,1,'')\n";
|
} else {//5，9，16，17
|
sql += " select stuff((SELECT ',' + CONVERT(VARCHAR, isnull(b.hdtable,''))+','+CONVERT(VARCHAR, isnull(b.dttable,'')) from gform b where formid=@formid FOR XML PATH ('')),1,1,'')";
|
}
|
String str = this.jdbcTemplate.queryForObject(sql, String.class, formid);
|
tableNameList = str.split(",");
|
|
//2，---------取出数据范围需要检查的sql语句
|
|
listSaveChecked = getSysdataRangeCheck(formid, point);// point=2 保存，point=3，(保存+确定)
|
listDoc = getSysdatarange_Doccheck(formid, point);
|
listVDoc = getVsysDocDataCheckForm(formid, point);
|
if (json.getBtnId() != null && !json.getBtnId().equals("")) {// 按钮检查
|
btnCheck = getButtonCheck(formid, json.getBtnId(), 3);//oa按钮都是用在确认后的操作
|
|
}
|
|
//3,---------替换sql参数
|
String doccode = json.getDoccode();
|
if (("@newDoccode" + cont).equalsIgnoreCase(doccode)||StringUtils.isBlank(doccode)) {
|
doccode = "@newDoccode" + cont;
|
} else {
|
doccode = "'" + doccode + "'";
|
}
|
sBuffer.append(getDataCheck(listSaveChecked, tableNameList, cont, sessionClone, doccode,headMap));// 最常用的数据范围检查
|
sBuffer.append(getDataCheck(btnCheck, tableNameList, cont, sessionClone, doccode,headMap));// OA按钮数据范围检查
|
|
sBuffer.append(getDataCheckByForm(listDoc, tableNameList[0], cont, sessionClone, doccode,headMap));// 表头数据范围检查2
|
sBuffer.append(getDataCheckByForm(listVDoc, tableNameList[0], cont, sessionClone, doccode,headMap));// 表头数据范围检查3
|
|
if (point == 3 ) {// 只有是确认时候才调用这个检查
|
// String jsonDoccode = json.getDoccode();
|
// if (("@newDoccode" + cont).equalsIgnoreCase(jsonDoccode)||"".equalsIgnoreCase(jsonDoccode))
|
// jsonDoccode = "@newDoccode" + cont;
|
// else
|
// jsonDoccode = "'" + jsonDoccode + "'";
|
sBuffer.append("\r\n insert into @table" + cont
|
+ " (warnflag ,ErrorFlag,infoMessage) select warnflag ,ErrorFlag,infoMessage from checkdocpost("
|
+ formid + "," + doccode + ")");// 数据范围检查1
|
}
|
|
int num = 20;// 默认显示条数
|
if (!"".equalsIgnoreCase(dcPagenum) && !"0".equalsIgnoreCase(dcPagenum)) {
|
num = Integer.valueOf(dcPagenum);
|
}
|
sBuffer.append("\n if exists( select top 1 1 from @table" + cont
|
+ " where isnull(warnflag,0) = 1 or isnull(ErrorFlag,0) = 1) ").append(" \n begin");
|
if (json.getOnlinePay() == 1) {
|
//是否是在线支付,是则输出标记给前端，在数据范围检查点“确定"按钮时需要用到 by danaus 2022/3/25 14:25
|
sBuffer.append(" \nselect @onlinePay=case when isnull(OnlinePaymentAmount,0) > 0 then 1 else 0 end from " + json.getTableName() + " where doccode=" + doccode + " \n");
|
}
|
sBuffer.append("\n select @myTotalCount" + cont + " = COUNT(1) from @table" + cont + " ")
|
.append("\n select @ErrorCount" + cont + "=COUNT(1) from @table" + cont + " where ErrorFlag = 1 ")
|
.append("\n select @warnCount" + cont + "= @myTotalCount" + cont + " - @ErrorCount" + cont + " ")
|
.append("\n select @ErrMsgs" + cont + "='datacheckError_'+convert(varchar(10),@onlinePay)+'#t#'+ isnull(@newDoccode" + cont + ",'') "
|
+ "+'#t#'")
|
.append(" \n select @ErrMsgs" + cont + "=@ErrMsgs" + cont
|
+ " + cast(warnflag as varchar) + '#p#' + cast(ErrorFlag as varchar) + '#p#' + isnull(infoMessage,'') + '#e#' ")
|
.append("\n from (select top " + num + " warnflag ,ErrorFlag,infoMessage from @table" + cont
|
+ " where isnull(warnflag,0) = 1 or isnull(ErrorFlag,0) = 1 order by ErrorFlag desc,warnflag desc) as a ")
|
.append("\n if @myTotalCount" + cont + " > " + num).append("\n begin")
|
.append("\n select @ErrMsgs" + cont + " = @ErrMsgs" + cont + " + '#e#' + (case when @ErrorCount"
|
+ cont + " > " + num + " then '1' else '0' end) +'#p#'+ ")
|
.append("\n (case when @ErrorCount" + cont + " - " + num
|
+ " > 0 then '1' else '0' end) +'#p#' +")
|
.append("\n (case when @ErrorCount" + cont + " -" + num
|
+ " > 0 then '还有' + (CAST ((@ErrorCount" + cont + " - " + num
|
+ ") as varchar)) + '条错误未显示... ;' else '' end) +")
|
.append("\n (case when @ErrorCount" + cont + "- " + num + " + @warnCount" + cont + " > 0 ")
|
.append("\n then '还有' + (CAST ((@ErrorCount" + cont + " - " + num + " + @warnCount" + cont
|
+ ") as varchar)) + '条警告未显示... ;' else '' end) ")
|
.append("\n end ")
|
.append("\n raiserror(@ErrMsgs" + cont + ",16,1)")
|
.append("\n return end")
|
.append("\n ------------end 数据范围检查---------------");
|
|
return sBuffer.toString();
|
}
|
private String filterHeadData(Map<String,String> headMap,String sql,int type,int tabIndex){
|
if(tabIndex==0) return sql;//检查表头时不需要用页面数据做替换
|
for(Map.Entry<String,String> entry:headMap.entrySet()){
|
if(StringUtils.isNotBlank(sql)&&!sql.matches("-?[0-9]+.*[0-9]*")&&!sql.contains("&")&&sql.trim().indexOf(" ")<0){
|
//处理只有一个字段且没用&相连的情况
|
sql="&"+sql+"&";
|
}
|
if(sql.contains("&"+entry.getKey().toLowerCase()+"&")){
|
if((!entry.getKey().equalsIgnoreCase("doccode")&&(entry.getValue()==null||!entry.getValue().startsWith("@newDoccode")))
|
&&(!entry.getKey().equalsIgnoreCase("rowid")&&(entry.getValue()==null||!entry.getValue().startsWith("@rowid")))) {//单号,rowid在单据所有表都存在，所以不需要从表头取数
|
if(type==0) {
|
sql = sql.replaceAll("&" + entry.getKey().toLowerCase() + "&", entry.getValue()==null?"":entry.getValue());
|
}else{
|
//提示信息的参数的引号，在替换后需要去掉
|
String filedName="&" + entry.getKey().toLowerCase() + "&";
|
if(sql.contains("'" + filedName+ "'")) {
|
filedName = "'" + filedName+ "'";
|
}
|
sql = sql.replaceAll(filedName, entry.getValue()==null?"":entry.getValue());
|
}
|
// if(StringUtils.isBlank(entry.getValue())){//值为""或null
|
// sql = sql.replaceAll("&" + entry.getKey().toLowerCase() + "&", entry.getValue());
|
// }else {
|
// sql = sql.replaceAll("&" + entry.getKey().toLowerCase() + "&", entry.getValue());
|
// }
|
}
|
}
|
}
|
|
return sql;
|
}
|
|
/**
|
* 处理sql的参数替换
|
*
|
* @param sqlCheckLists sql集合
|
* @param num
|
* @return
|
*/
|
private StringBuffer getDataCheck(List<Map<String, Object>> sqlCheckLists, String[] tableNameList, int num, HashMap<String, String> sessionClone, String docCode,Map<String,String> headMap) {
|
StringBuffer sBuffer = new StringBuffer();
|
if (sqlCheckLists == null || sqlCheckLists.size() == 0) {
|
return sBuffer;
|
}
|
for (Map<String, Object> map : sqlCheckLists) {
|
|
//每条sql都会根据集合多少而执行多次，通过游标实现
|
|
|
|
//select case when cast('&Price&' as money) <> (select stockPrice from t140902 where Matcode='&Matcode&') then 0 else 1 end from t140102D a with (nolock) inner join t140102H b on a.doccode= b.doccode where a.doccode = '&doccode&' and a.matcode='&Matcode&'
|
int tabIndex=GridUtils.prossRowSetDataType_Int(map, "check_Table") - 1;
|
int len=tabIndex-tableNameList.length;
|
if(len>=0){
|
throw new ApplicationException("【9843】定义的检查表对象超出对应功能号类型的表范围【例如16类型功能号只有一个主表，则不能设置检查表体】");
|
}
|
String tableName = tableNameList[tabIndex];//当前对应的表名称
|
List<String> sqlList = new ArrayList<>();
|
|
sqlList.add(filterHeadData(headMap,GridUtils.prossRowSetDataType_String(map, "alter_sql").toLowerCase(),0,tabIndex));
|
|
sqlList.add(filterHeadData(headMap,GridUtils.prossRowSetDataType_String(map, "base_sql").toLowerCase(),0,tabIndex));
|
|
sqlList.add(filterHeadData(headMap,GridUtils.prossRowSetDataType_String(map, "alter_message"),1,tabIndex));
|
|
Map<String, Object> sqlMap = replaceSqlParmInfo(sqlList, tableName, false, sessionClone); // 比较值SQL
|
if (sqlMap != null) {
|
Object[] sql = ((List) sqlMap.get("sql")).toArray();//[alter_sql,base_sql,alter_message]
|
String columName = GridUtils.prossRowSetDataType_String(sqlMap, "columName");//列名
|
String columNameVar = GridUtils.prossRowSetDataType_String(sqlMap, "columNameVar");//列名变量
|
Map<String, String> columnVarMap = (Map<String, String>) sqlMap.get("columnVarMap");
|
String index = RandomStringUtils.randomNumeric(6);
|
if(StringUtils.isBlank(columName)){//表示之前在表头已替换完，这里需要处理下，才能使游标语法成立
|
//取当前表的第一个字段(列名，数据类型，)来填充
|
Map<String, Object> mapType= mGridServiceIfc.getSimpleJdbcTemplate().queryForMap("set nocount on ;select top 1 column_name,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name=?",tableName);
|
columName=GridUtils.prossRowSetDataType_String(mapType,"column_name");
|
columNameVar="@"+columName+"_"+index;
|
String data_type=GridUtils.prossRowSetDataType_String(mapType,"DATA_TYPE");
|
if(data_type.equalsIgnoreCase("varchar")||
|
data_type.equalsIgnoreCase("nvarchar")||
|
data_type.equalsIgnoreCase("char")||
|
data_type.equalsIgnoreCase("nchar")){
|
columnVarMap.put(columName," declare @"+columName+"_"+index+" "+data_type+"("+GridUtils.prossRowSetDataType_Int(mapType,"CHARACTER_MAXIMUM_LENGTH")+")\n");
|
}else{
|
columnVarMap.put(columName," declare @"+columName+"_"+index+" "+data_type+"\n");
|
}
|
|
}
|
|
//------------通过游标方式取集合数据 进行检查
|
sBuffer.append("\n declare @rtnvalue" + index + " money ,@myrowcount_" + index + " int \n declare @myerror_" + index + " int \n declare @myerrorMsg_" + index + " varchar(500) ");//定义输出出错信息
|
for (Map.Entry<String, String> entry : columnVarMap.entrySet()) {//输出从sql取出的参数，拼接成sql变量
|
sBuffer.append(entry.getValue());
|
}
|
sBuffer.append("\n declare mycurPostCur_" + index + " cursor for \n");
|
|
//从这里执行sql检查
|
sBuffer.append("\n select " + this.format(StringUtils.isBlank(columName)?"*":columName) + " from " + tableName + " where doccode=" + docCode);
|
sBuffer.append("\n open mycurPostCur_" + index);
|
sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n");
|
sBuffer.append(this.format(columNameVar));// 给到定义好的变量
|
sBuffer.append("\n while @@FETCH_STATUS = 0 \n begin \n");
|
|
//-----start
|
sBuffer.append(" \nbegin \n select @myerrorMsg_" + index + "=" + sql[2]);//TODO 普通的提示信息 [不能使用非明细科目 &AccountID&]
|
|
sBuffer.append("\r\n select @rtnvalue" + index + " = (isnull((" + sql[0] + "),0) - isnull((" + sql[1] + "),0))");//
|
sBuffer.append("\r\n insert into @table" + num + " (errorflag,warnflag,infomessage) select case when " + getPanDuan("upper_error_digit", "lower_error_digit", map, index) + " then 1 ");
|
sBuffer.append("\r\n else 0 end , case when " + getPanDuan("upper_warn_digit", "lower_warn_digit", map, index) + " then 1 else 0 end ");
|
sBuffer.append("\r\n ,@myerrorMsg_" + index);// 弹出消息,增加#_是为了区分需要替换的id
|
|
sBuffer.append(" \n end ");
|
|
// ------------end
|
sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n");
|
sBuffer.append(this.format(columNameVar));
|
sBuffer.append("\n end ");
|
sBuffer.append("\n close mycurPostCur_" + index);
|
sBuffer.append("\n deallocate mycurPostCur_" + index);
|
|
} else {
|
throw new ApplicationException("替换数据范围检查sql参数生成出错");
|
}
|
}
|
return sBuffer;
|
}
|
|
private StringBuffer getDataCheckByForm(List<Map<String, Object>> sqlCheckLists, String tableName, int num, HashMap<String, String> sessionClone, String docCode,Map<String,String> headMap) {
|
StringBuffer sBuffer = new StringBuffer();
|
if (sqlCheckLists == null || sqlCheckLists.size() == 0) {
|
return sBuffer;
|
}
|
for (Map<String, Object> map : sqlCheckLists) {
|
//每条sql都会根据集合多少而执行多次，通过游标实现
|
|
//"select 1 as warning , 1 as denyfld , '&docdate& 调价单日期不能小于销售开单的日期:'+convert(varchar(10),docdate,120) as infomsg from spickorderhd where doccode ='&refcode&' and docdate > '&docdate&'"
|
String sql1=replaceParm(filterHeadData(headMap,GridUtils.prossRowSetDataType_String(map, "DoccheckView").toLowerCase(),0,1));
|
Map sqlMap = replaceSqlParmInfoByForm(sql1, tableName, false, sessionClone);
|
if (sqlMap != null) {
|
Object[] sql = ((List) sqlMap.get("sql")).toArray();
|
String columName = GridUtils.prossRowSetDataType_String(sqlMap, "columName");//列名
|
String columNameVar = GridUtils.prossRowSetDataType_String(sqlMap, "columNameVar");//列名变量
|
Map<String, String> columnVarMap = (Map<String, String>) sqlMap.get("columnVarMap");
|
String index = RandomStringUtils.randomNumeric(6);
|
//------------通过游标方式取集合数据 进行检查
|
if(StringUtils.isBlank(columName)){//表示之前在表头已替换完，这里需要处理下，才能使游标语法成立
|
//取当前表的第一个字段(列名，数据类型，)来填充
|
Map mapType= mGridServiceIfc.getSimpleJdbcTemplate().queryForMap("select top 1 column_name,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name=?",tableName);
|
columName=GridUtils.prossRowSetDataType_String(mapType,"column_name");
|
columNameVar="@"+columName+"_"+index;
|
String data_type=GridUtils.prossRowSetDataType_String(mapType,"DATA_TYPE");
|
if(data_type.equalsIgnoreCase("varchar")||
|
data_type.equalsIgnoreCase("nvarchar")||
|
data_type.equalsIgnoreCase("char")||
|
data_type.equalsIgnoreCase("nchar")){
|
columnVarMap.put(columName," declare @"+columName+"_"+index+" "+data_type+"("+GridUtils.prossRowSetDataType_Int(mapType,"CHARACTER_MAXIMUM_LENGTH")+")\n");
|
}else{
|
columnVarMap.put(columName," declare @"+columName+"_"+index+" "+data_type+"\n");
|
}
|
|
}
|
for (Map.Entry<String, String> entry : columnVarMap.entrySet()) {//输出从sql取出的参数，拼接成sql变量
|
sBuffer.append(entry.getValue());
|
}
|
sBuffer.append("\n declare mycurPostCur_" + index + " cursor for \n");
|
|
//从这里执行sql检查
|
sBuffer.append("\n select " + ((StringUtils.isBlank(columName)?"*":this.format(columName))) + " from " + tableName + " where doccode=" + docCode);
|
sBuffer.append("\n open mycurPostCur_" + index);
|
sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n");
|
sBuffer.append(this.format(columNameVar));// 给到定义好的变量
|
sBuffer.append("\n while @@FETCH_STATUS = 0 \n" +
|
"begin \n");
|
//-----start
|
|
sBuffer.append(" \n begin ");
|
sBuffer.append("\r\n insert into @table" + num + " (warnflag,errorflag,infomessage) " + sql[0]);
|
sBuffer.append(" \n end ");
|
|
// ------------end
|
sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n");
|
sBuffer.append(this.format(columNameVar));
|
sBuffer.append("\n end ");
|
sBuffer.append("\n close mycurPostCur_" + index);
|
sBuffer.append("\n deallocate mycurPostCur_" + index);
|
|
} else {
|
throw new ApplicationException("替换数据范围检查sql参数生成出错");
|
}
|
}
|
return sBuffer;
|
}
|
|
private String getPanDuan(String chaochu, String diyu, Map<String, Object> map, String num) {
|
String str = "";
|
String chaochuPan = (GridUtils.prossRowSetDataType_String(map, chaochu).equals("0")) ? "0=0" : "1=0";
|
String diyuPan = (GridUtils.prossRowSetDataType_String(map, diyu).equals("0")) ? "0=0" : "1=0";
|
str = "( " + chaochuPan + " and @rtnvalue" + num + " > 0 ) or (" + diyuPan + " and @rtnvalue" + num + " < 0 ) or (1=0 and @rtnvalue" + num + " = 0 )";// 两个等于的情况总是1=0为假
|
return str;
|
}
|
|
|
private String format(String string) {
|
if(StringUtils.isBlank(string)){
|
return "";
|
}else {
|
return (string.lastIndexOf(",") == string.length() - 1) ? string.substring(0, string.length() - 1) : string;
|
}
|
}
|
|
/**
|
* @param sql 四种情况 1直接sql语句 2：字段名 3：sql含有字段名 4： 固定值
|
* @return 放回替换后的sql
|
*/
|
public Map<String, Object> replaceSqlParmInfo(List<String> sqlList, String tableName, boolean isInfo, HashMap<String, String> sessionClone) {
|
String index = RandomStringUtils.randomNumeric(6);
|
|
String st = " set nocount on ; select * from " + tableName + " where 1=2";
|
SqlRowSet row = this.jdbcTemplate.queryForRowSet(st);
|
// -----定义变量，从设置中的sql取得，且用元数据取得相关的数据类型
|
List<Object> returnSql = new ArrayList<>();//保存处理后的sql
|
List<String> parms = new ArrayList<String>();// 保存需要查询时需要的列名
|
Map<String, String> columnVarMap = new HashMap<>();// 保存列变量定义
|
Map<String, Object> returnMap = new HashMap<>();// 返回值
|
String columName = "";
|
String columNameVar = "";
|
int indexd = 0;
|
for (String sql : sqlList) {
|
indexd++;//indexd==3表示是提示信息而不是sql语句，需要处理拼接问题及引号
|
parms = mGridServiceIfc.getParm(sql, parms, row);//取参数
|
String[] str = mGridServiceIfc.prossParm(row, sql, parms, Integer.parseInt(index), columnVarMap, sessionClone, tableName, indexd != 3 ? 0 : 1);
|
if (indexd == 3) {
|
returnSql.add((!str[0].startsWith("'")&&!str[0].startsWith("cast")) ? str[0] = "'" + str[0] : str[0]);//补充开头的引号
|
} else {
|
returnSql.add(str[0]);
|
}
|
columName += str[1];
|
columNameVar += str[2];
|
}
|
returnMap.put("sql", returnSql);
|
returnMap.put("columName", columName);
|
returnMap.put("columNameVar", columNameVar);
|
returnMap.put("columnVarMap", columnVarMap);
|
|
return returnMap;
|
}
|
|
public Map replaceSqlParmInfoByForm(String sql, String tableName, boolean isInfo, HashMap<String, String> sessionClone) {
|
String index = RandomStringUtils.randomNumeric(3);
|
|
String st = " set nocount on ; select * from " + tableName + " where 1=2";
|
SqlRowSet row = this.jdbcTemplate.queryForRowSet(st);
|
// -----定义变量，从设置中的sql取得，且用元数据取得相关的数据类型
|
List<Object> returnSql = new ArrayList<>();//保存处理后的sql
|
List<String> parms = new ArrayList<String>();// 保存需要查询时需要的列名
|
Map<String, String> columnVarMap = new HashMap<>();// 保存列变量定义
|
Map<String, Object> returnMap = new HashMap<>();// 返回值
|
String columName = "";
|
String columNameVar = "";
|
parms = mGridServiceIfc.getParm(sql, parms, row);//取参数
|
String[] str = mGridServiceIfc.prossParm(row, sql, parms, Integer.parseInt(index), columnVarMap, sessionClone, tableName, 0);
|
|
returnSql.add(str[0]);
|
columName += str[1];
|
columNameVar += str[2];
|
returnMap.put("sql", returnSql);
|
returnMap.put("columName", columName);
|
returnMap.put("columNameVar", columNameVar);
|
returnMap.put("columnVarMap", columnVarMap);
|
|
return returnMap;
|
}
|
|
private String replaceParm(String sql){
|
if(StringUtils.isBlank(sql)) return sql;
|
Pattern p = Pattern.compile("'.*?'");
|
java.util.regex.Matcher propsMatcher = p.matcher(sql);
|
while (propsMatcher.find()) {
|
String str=propsMatcher.group();
|
Pattern p4 = Pattern.compile("&.*?&");
|
java.util.regex.Matcher propsMatcher4=p4.matcher(str);
|
while (propsMatcher4.find()) {
|
String sq=propsMatcher4.group();
|
if(sq.length()+2==str.length()){ //不符合条件
|
break;
|
}else{
|
//处理存在多个替换参数 '&docdate& 调价单日期不能小于&docdate&销售开单的日期:'
|
Pattern p41 = Pattern.compile("&.*?&");
|
java.util.regex.Matcher propsMatcher41=p41.matcher(str);
|
String tempStr=str;
|
while (propsMatcher41.find()) {
|
String sqt = propsMatcher41.group();
|
if (str.startsWith("'&")) {//以'&开头
|
|
str = str.replaceFirst("'"+sqt, " cast(" + sqt.replaceAll("&","#") + " as nvarchar(500))+'");//case(&docdate& as nvarchar(500))+'调价单日期不能小于销售开单的日期:'
|
} else if (str.endsWith("&'")) {//字段在结尾处 '调价单日期不能小于销售开单的日期 &docdate&'
|
|
str = str.replaceFirst(sqt+"'", "'+ cast(" + sqt.replaceAll("&","#") + " as nvarchar(500))");//'调价单日期不能小于销售开单的日期:'+case(&docdate& as nvarchar(500))
|
// }
|
} else {//'调价单日期不能 &docdate& 小于销售开单的日期:'
|
str = str.replaceFirst(sqt, "'+ cast(" + sqt.replaceAll("&","#") + " as nvarchar(500))+'");//'调价单日期不能'+case(&docdate& as nvarchar(500))+'小于销售开单的日期:'
|
}
|
}
|
sql=sql.replaceFirst(tempStr,str);
|
}
|
|
}
|
|
}
|
sql=sql.replaceAll("#","&");
|
return sql;
|
}
|
}
|
