| | |
| | | */ |
| | | @Override |
| | | public String getErrorList(int formid, int point, GTJson json, |
| | | String dcPagenum, int cont, HashMap<String, String> sessionClone) throws DataAccessException { |
| | | String dcPagenum, 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) "); |
| | | sBuffer.append("\n ---------start 数据范围检查--------------- \n"); |
| | | //int check_Table = 0;//检查哪个表 |
| | | List<Map<String, Object>> listSaveChecked = null; |
| | | List<Map<String, Object>> listDoc = null; |
| | |
| | | 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,'')"; |
| | |
| | | |
| | | //3,---------替换sql参数 |
| | | String doccode = json.getDoccode(); |
| | | if (("@newDoccode" + cont).equalsIgnoreCase(doccode)||StringUtils.isBlank(doccode)) { |
| | | doccode = "@newDoccode" + cont; |
| | | if (("@newDoccode").equalsIgnoreCase(doccode)||StringUtils.isBlank(doccode)) { |
| | | doccode = "@newDoccode"; |
| | | } else { |
| | | doccode = "'" + doccode + "'"; |
| | | } |
| | | sBuffer.append(getDataCheck(listSaveChecked, tableNameList, cont, sessionClone, doccode,headMap));// 最常用的数据范围检查 |
| | | sBuffer.append(getDataCheck(btnCheck, tableNameList, cont, sessionClone, doccode,headMap));// OA按钮数据范围检查 |
| | | sBuffer.append(getDataCheck(listSaveChecked, tableNameList, sessionClone, doccode,headMap));// 最常用的数据范围检查 |
| | | sBuffer.append(getDataCheck(btnCheck, tableNameList, 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 |
| | | sBuffer.append(getDataCheckByForm(listDoc, tableNameList[0], sessionClone, doccode,headMap));// 表头数据范围检查2 |
| | | sBuffer.append(getDataCheckByForm(listVDoc, tableNameList[0], 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 |
| | | sBuffer.append("\r\n insert into @dataCheckTable(warnflag ,ErrorFlag,infoMessage)\n" + |
| | | " select warnflag ,ErrorFlag,infoMessage from checkdocpost(" |
| | | + formid + "," + doccode + ") \n");// 数据范围检查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") |
| | | .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#t#'+ isnull(@newDoccode" + cont + ",'') " |
| | | sBuffer.append("\n if exists( select top 1 1 from @dataCheckTable" |
| | | + " 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 = COUNT(1) from @dataCheckTable ") |
| | | .append("\n select @ErrorCount=COUNT(1) from @dataCheckTable where ErrorFlag = 1 ") |
| | | .append("\n select @warnCount= @myTotalCount - @ErrorCount ") |
| | | .append("\n select @ErrMsgs='datacheckError_'+convert(varchar(10),@onlinePay)+'#t#'+ isnull(@newDoccode,'') " |
| | | + "+'#t#'") |
| | | .append(" \n select @ErrMsgs" + cont + "=@ErrMsgs" + cont |
| | | .append(" \n select @ErrMsgs=@ErrMsgs" |
| | | + " + 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 |
| | | .append("\n from (select top " + num + " warnflag ,ErrorFlag,infoMessage from @dataCheckTable" |
| | | + " 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 |
| | | .append("\n if @myTotalCount > " + num).append("\n begin") |
| | | .append("\n select @ErrMsgs = @ErrMsgs + '#e#' + (case when @ErrorCount > " + num + " then '1' else '0' end) +'#p#'+ ") |
| | | .append("\n (case when @ErrorCount - " + num |
| | | + " > 0 then '1' else '0' end) +'#p#' +") |
| | | .append("\n (case when @ErrorCount" + cont + " -" + num |
| | | + " > 0 then '还有' + (CAST ((@ErrorCount" + cont + " - " + num |
| | | .append("\n (case when @ErrorCount -" + num |
| | | + " > 0 then '还有' + (CAST ((@ErrorCount - " + num |
| | | + ") as varchar)) + '条错误未显示... ;' else '' end) +") |
| | | .append("\n (case when @ErrorCount" + cont + "- " + num + " + @warnCount" + cont + " > 0 ") |
| | | .append("\n then '还有' + (CAST ((@ErrorCount" + cont + " - " + num + " + @warnCount" + cont |
| | | .append("\n (case when @ErrorCount- " + num + " + @warnCount > 0 ") |
| | | .append("\n then '还有' + (CAST ((@ErrorCount - " + num + " + @warnCount" |
| | | + ") as varchar)) + '条警告未显示... ;' else '' end) ") |
| | | .append("\n end ").append("\n raiserror(@ErrMsgs" + cont + ",16,1)").append("\n return end").append("\n ------------end 数据范围检查---------------"); |
| | | .append("\n end ") |
| | | .append("\n raiserror(@ErrMsgs,16,1)") |
| | | .append("\n return end") |
| | | .append("\n ------------end 数据范围检查---------------\n"); |
| | | |
| | | return sBuffer.toString(); |
| | | } |
| | |
| | | * @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) { |
| | | private StringBuffer getDataCheck(List<Map<String, Object>> sqlCheckLists, String[] tableNameList, HashMap<String, String> sessionClone, String docCode,Map<String,String> headMap) { |
| | | StringBuffer sBuffer = new StringBuffer(); |
| | | if (sqlCheckLists == null || sqlCheckLists.size() == 0) { |
| | | return sBuffer; |
| | |
| | | } |
| | | |
| | | //------------通过游标方式取集合数据 进行检查 |
| | | 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"); |
| | | sBuffer.append("\n declare mycurPostCur 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("\n open mycurPostCur \n"); |
| | | sBuffer.append("\n fetch next from mycurPostCur 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(" \nbegin \n select @myerrorMsg =" + 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("\r\n select @rtnvalue = (isnull((" + sql[0] + "),0) - isnull((" + sql[1] + "),0))");// |
| | | sBuffer.append("\r\n insert into @dataCheckTable (errorflag,warnflag,infomessage) select case when " + getPanDuan("upper_error_digit", "lower_error_digit", map) + " then 1 "); |
| | | sBuffer.append("\r\n else 0 end , case when " + getPanDuan("upper_warn_digit", "lower_warn_digit", map) + " then 1 else 0 end ,@myerrorMsg");// 弹出消息,增加#_是为了区分需要替换的id |
| | | sBuffer.append(" \n end "); |
| | | |
| | | // ------------end |
| | | sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n"); |
| | | sBuffer.append("\n fetch next from mycurPostCur into \n"); |
| | | sBuffer.append(this.format(columNameVar)); |
| | | sBuffer.append("\n end "); |
| | | sBuffer.append("\n close mycurPostCur_" + index); |
| | | sBuffer.append("\n deallocate mycurPostCur_" + index); |
| | | |
| | | sBuffer.append("\n close mycurPostCur"); |
| | | sBuffer.append("\n deallocate mycurPostCur"); |
| | | } 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) { |
| | | private StringBuffer getDataCheckByForm(List<Map<String, Object>> sqlCheckLists, String tableName, HashMap<String, String> sessionClone, String docCode,Map<String,String> headMap) { |
| | | StringBuffer sBuffer = new StringBuffer(); |
| | | if (sqlCheckLists == null || sqlCheckLists.size() == 0) { |
| | | return sBuffer; |
| | |
| | | for (Map.Entry<String, String> entry : columnVarMap.entrySet()) {//输出从sql取出的参数,拼接成sql变量 |
| | | sBuffer.append(entry.getValue()); |
| | | } |
| | | sBuffer.append("\n declare mycurPostCur_" + index + " cursor for \n"); |
| | | sBuffer.append("\n declare mycurPostCur 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("\n open mycurPostCur"); |
| | | sBuffer.append("\n fetch next from mycurPostCur 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("\r\n insert into @dataCheckTable (warnflag,errorflag,infomessage) " + sql[0]); |
| | | sBuffer.append(" \n end "); |
| | | |
| | | // ------------end |
| | | sBuffer.append("\n fetch next from mycurPostCur_" + index + " into \n"); |
| | | sBuffer.append("\n fetch next from mycurPostCur into \n"); |
| | | sBuffer.append(this.format(columNameVar)); |
| | | sBuffer.append("\n end "); |
| | | sBuffer.append("\n close mycurPostCur_" + index); |
| | | sBuffer.append("\n deallocate mycurPostCur_" + index); |
| | | sBuffer.append("\n close mycurPostCur"); |
| | | sBuffer.append("\n deallocate mycurPostCur"); |
| | | |
| | | } else { |
| | | throw new ApplicationException("替换数据范围检查sql参数生成出错"); |
| | |
| | | return sBuffer; |
| | | } |
| | | |
| | | private String getPanDuan(String chaochu, String diyu, Map<String, Object> map, String num) { |
| | | private String getPanDuan(String chaochu, String diyu, Map<String, Object> map) { |
| | | 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为假 |
| | | //equals("1")=true表示需要弹出提示窗口,所以0=0组合条件才能返回1 by danaus 2024-08-23 10:01 |
| | | String chaochuPan = (GridUtils.prossRowSetDataType_String(map, chaochu).equals("1")) ? "0=0" : "1=0"; |
| | | String diyuPan = (GridUtils.prossRowSetDataType_String(map, diyu).equals("1")) ? "0=0" : "1=0"; |
| | | str = "( " + chaochuPan + " and @rtnvalue > 0 ) or (" + diyuPan + " and @rtnvalue < 0 ) or (1=0 and @rtnvalue = 0 )";// 两个等于的情况总是1=0为假 |
| | | return str; |
| | | } |
| | | |
| | |
| | | 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); |
| | | String[] str = mGridServiceIfc.prossParm(row, sql, parms, 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 { |
| | |
| | | String columName = ""; |
| | | String columNameVar = ""; |
| | | parms = mGridServiceIfc.getParm(sql, parms, row);//取参数 |
| | | String[] str = mGridServiceIfc.prossParm(row, sql, parms, Integer.parseInt(index), columnVarMap, sessionClone, tableName, 0); |
| | | String[] str = mGridServiceIfc.prossParm(row, sql, parms, index, columnVarMap, sessionClone, tableName, 0); |
| | | |
| | | returnSql.add(str[0]); |
| | | columName += str[1]; |