| | |
| | | package com.yc.service.grid; |
| | | |
| | | import com.alibaba.fastjson.JSON; |
| | | import com.yc.action.excel.Excel; |
| | | import com.yc.action.execProc.ExecuteProcAction; |
| | | import com.yc.action.grid.Grid; |
| | | import com.yc.action.grid.GridUtils; |
| | | import com.yc.action.grid.PicEntity; |
| | | import com.yc.action.grid.SqlInfo; |
| | | import com.yc.action.taobao.Taobao2ERP; |
| | | import com.yc.action.upload.AttachmentAction; |
| | | import com.yc.app.v2.entity.GformEntity; |
| | | import com.yc.entity.TableColumnsDataTypeEntity; |
| | | import com.yc.exception.ApplicationException; |
| | | import com.yc.factory.FactoryBean; |
| | |
| | | import com.yc.service.new38type.Type38Ifc; |
| | | import com.yc.service.upload.AttachmentIfc; |
| | | import com.yc.utils.*; |
| | | import org.apache.commons.lang.RandomStringUtils; |
| | | import org.apache.commons.lang.StringUtils; |
| | | import org.apache.http.client.methods.HttpGet; |
| | | import org.apache.http.impl.client.CloseableHttpClient; |
| | |
| | | import org.springframework.jdbc.support.rowset.SqlRowSet; |
| | | import org.springframework.jdbc.support.rowset.SqlRowSetMetaData; |
| | | import org.springframework.stereotype.Service; |
| | | import org.springframework.transaction.annotation.Transactional; |
| | | |
| | | import javax.servlet.http.HttpServletRequest; |
| | | import javax.servlet.http.HttpServletResponse; |
| | | import javax.servlet.http.HttpSession; |
| | | import java.io.UnsupportedEncodingException; |
| | | import java.net.URLEncoder; |
| | | import java.sql.*; |
| | | import java.sql.CallableStatement; |
| | | import java.sql.SQLException; |
| | | import java.sql.Types; |
| | | import java.util.*; |
| | | import java.util.Map.Entry; |
| | | import java.util.regex.Matcher; |
| | |
| | | public class GridServiceImpl extends BaseService implements GridServiceIfc { |
| | | @Autowired |
| | | Type38Ifc type38Ifc; |
| | | private final String gform = "[formid], [formname],[isused],[formmemo],[optype],[refformid],[hdtable],[dttable],[showdetail],[fetchrecnumber],[queryform],[refreshTime],[refresh_aftersave],[frozencols],[LockGridSort],[rowcopyfields],[rowcopyformids],[rowDelFormids],[index1],[index1primary],[index2],[index2primary],[index3],[index3Primary],[dataformid],[predocstatus],[postdocstatus],[DocStatusName],[transgroupcode],[codelength],[preFixcode],[precodetype],[helpdoc_udl],[checkblncfields],[formalign],[DealAfterDocSave],[AllowEmptyRow],[Busi2fiDataView],[glcodefield],[txtinputfields],[DBCtrlRowCount],[ShowAsDetailMode],[modifytableflag],[applytableflag],[CrossInputType],[MultiTitleType],[datapooled],[glentitycode],[formdatafilters],[ProcGroupafterSavedoc], [TransTypecode],[currencyfld],[chkFormula],[GridFormatFun],[SelectChecker],[ReturnCurChecker],[ReturnCurCheckerName],[FT],[FTFormType],[FK],[SeekGroupID],[sPremissField],[dPremissField],[FKeFilter],[GridRowHeight],[GridHeight], [isShowPwdEdit], [isOpenFuncShowPwdEdit], [isShowOnlineMsg] ,[isShowProcessTracking] ,[PanelLabelLayout],[isTitle], [isTaobao],[cellAlign] ,[pageSize],[version],[reportprompts],[CancelBtnProcName],[CancelBtnExpression],[CancelBtnEditStatus],[isShowCell],[importProcName],[CancelIsSave],[CancelBtnName],[isGantt],[byGroup],[DefaultRowCount],[Expanded], [isFilter], [JNIName],[JNIDataBaseURL], [JNIPort] ,[JNIDataBaseName], [JNIUser], [JNIPwd] ,[JNISql] ,[JNITempTable],[addNewRow],[autopaging],[RevokeBtnProcName],[RevokeBtnExpression],[RevokeBtnEditStatus],[autoOpen],isExchangeDataWithHost,isShowNewDocButton,isShowMemoWhenApprovals,isshowinserialbtn,inserialbtneditstatus,isshowoutserialbtn ,outserialbtneditstatus,isShowOnlinePaymentButton,isShowGridStyleForApp "; |
| | | private final String gform = "[formid], [formname],[isused],[formmemo],[optype],[refformid],[hdtable],[dttable],[showdetail],[fetchrecnumber],[queryform],[refreshTime],[refresh_aftersave],[frozencols],[LockGridSort],[rowcopyfields],[rowcopyformids],[rowDelFormids],[index1],[index1primary],[index2],[index2primary],[index3],[index3Primary],[dataformid],[predocstatus],[postdocstatus],[DocStatusName],[transgroupcode],[codelength],[preFixcode],[precodetype],[helpdoc_udl],[checkblncfields],[formalign],[DealAfterDocSave],[AllowEmptyRow],[Busi2fiDataView],[glcodefield],[txtinputfields],[DBCtrlRowCount],[ShowAsDetailMode],[modifytableflag],[applytableflag],[CrossInputType],[MultiTitleType],[datapooled],[glentitycode],[formdatafilters],[ProcGroupafterSavedoc], [TransTypecode],[currencyfld],[chkFormula],[GridFormatFun],[SelectChecker],[ReturnCurChecker],[ReturnCurCheckerName],[FT],[FTFormType],[FK],[SeekGroupID],[sPremissField],[dPremissField],[FKeFilter],[GridRowHeight],[GridHeight], [isShowPwdEdit], [isOpenFuncShowPwdEdit], [isShowOnlineMsg] ,[isShowProcessTracking] ,[PanelLabelLayout],[isTitle], [isTaobao],[cellAlign] ,[pageSize],[version],[reportprompts],[CancelBtnProcName],[CancelBtnExpression],[CancelBtnEditStatus],[isShowCell],[importProcName],[CancelIsSave],[CancelBtnName],[isGantt],[byGroup],[DefaultRowCount],[Expanded], [isFilter], [JNIName],[JNIDataBaseURL], [JNIPort] ,[JNIDataBaseName], [JNIUser], [JNIPwd] ,[JNISql] ,[JNITempTable],[addNewRow],[autopaging],[RevokeBtnProcName],[RevokeBtnExpression],[RevokeBtnEditStatus],[autoOpen],isExchangeDataWithHost,isShowNewDocButton,isShowMemoWhenApprovals,isshowinserialbtn,inserialbtneditstatus,isshowoutserialbtn ,outserialbtneditstatus,isShowOnlinePaymentButton,isShowGridStyleForApp ,popupWindowsHeightRate,isShowCycleCountSerialBtn,cycleCountSerialBtnEditStatus,excludeTablesWhenSaving,showOnlineMsgExpression,showProcessTrackingExpression,showColsForApp ";// |
| | | |
| | | private final String gfield = "[formid],[headflag], [fieldid],[statisid],[Lblcode],[fieldname],[datatype],[displayformat], [showongrid],[gridcaption], [gridcontroltype],[gridlength],[ft],[ftformtype],[emptyrefdata], [fk],[seekgroupid],[spremissfield],[dpremissfield],[efilter],[return_one_record],[numfieldid], [visible], [hidelabel], [controltype],[rowno],[colno],[lengthnum],[heightnum],[detailrowno],[detailcolno],[detaillength],[detailheight],[statisflag] ,[blcheckauth],[indexfld],[readonly],[datalink],[keyinput],[secretfld],[calcufield],[formula],[sumfield],[funclinkname],[activefuns] ,[initvalue] ,[checkauthmode],[PreLike],[EnterToNextRow],[tabsheetname],[passwordchar],[uppercase] ,[LinkFormDisplayFields], [dyfieldview],[copyfromlastrow],[ftlockconditionflag],[datafilterfield], [HelpKeyID] ,[MultiLangYN], [IMEactive] , [memo], [Hints],[editStatus],[isReader] ,[SqlScript],[MasterFieldShowLocation], [isCustomHTMLComponent] , [showFieldValueExpression],[HyperlinkFT],[HyperlinkFTFormType],[HyperlinkSPremissField], [HyperlinkDPremissField],[HyperlinkEFilter],[Hyperlinkmode],[isAutoSaved],[stylecss],[rowSpan] ,[isImport],[isExport],[fieldtype],[cellAlign], [isCopyExclude],[isAutoRefresh],[isLoad],[Audit],[TipsExpression],[SuggestFileds],[RelationField],[onlyOne],AppColNo,AppHeight,AppHideLabel,AppLength,AppRowNo,AppVisible,ScanCodeField,funclinkname,exportTitle,isAPPExcludeField,SqlWhere,afterBlockDividingLine "; |
| | | private final String gfield = "[formid],[headflag], [fieldid],[statisid],[Lblcode],[fieldname],[datatype],[displayformat], [showongrid],[gridcaption], [gridcontroltype],[gridlength],[ft],[ftformtype],[emptyrefdata], [fk],[seekgroupid],[spremissfield],[dpremissfield],[efilter],[return_one_record],[numfieldid], [visible], [hidelabel], [controltype],[rowno],[colno],[lengthnum],[heightnum],[detailrowno],[detailcolno],[detaillength],[detailheight],[statisflag] ,[blcheckauth],[indexfld],[readonly],[datalink],[keyinput],[secretfld],[calcufield],[formula],[sumfield],[funclinkname],[activefuns] ,[initvalue] ,[checkauthmode],[PreLike],[EnterToNextRow],[tabsheetname],[passwordchar],[uppercase] ,[LinkFormDisplayFields], [dyfieldview],[copyfromlastrow],[ftlockconditionflag],[datafilterfield], [HelpKeyID] ,[MultiLangYN], [IMEactive] , [memo], [Hints],[editStatus],[isReader] ,[SqlScript],[MasterFieldShowLocation], [isCustomHTMLComponent] , [showFieldValueExpression],[HyperlinkFT],[HyperlinkFTFormType],[HyperlinkSPremissField], [HyperlinkDPremissField],[HyperlinkEFilter],[Hyperlinkmode],[isAutoSaved],[stylecss],[rowSpan] ,[isImport],[isExport],[fieldtype],[cellAlign], [isCopyExclude],[isAutoRefresh],[isLoad],[Audit],[TipsExpression],[SuggestFileds],[RelationField],[onlyOne],AppColNo,AppHeight,AppHideLabel,AppLength,AppRowNo,AppVisible,ScanCodeField,funclinkname,exportTitle,isAPPExcludeField,SqlWhere,afterBlockDividingLine,appCellAlign,isSuppressBlankLineForDropDown,qrcodesqlscript,qrcoderefrowid,qrcoderefdigit,qrcoderefformid,isshowqrcodesummary "; |
| | | |
| | | private final String GET_GFORM = "set nocount on; select " + gform + " from gform where formid=? "; |
| | | private final String GET_GFIELD = "set nocount on; select " + gfield + " from gfield where formid=? order by statisid asc"; |
| | | private final String GET_GFIELD9 = "set nocount on; select " + gfield + " from gfield where formid=? and HeadFlag=? order by statisid asc";// 9类型窗体 |
| | | private final static String GET_WINDOWTYPE = "set nocount on; select FormType from _sysMenu where formid=? "; |
| | | private final static String GET_FTDATA = "set nocount on; select interValue,dictvalue from _sysdict where dictid=? order by sequence asc"; |
| | | private final String GET_GFORM = " set nocount on; select " + gform + " from gform where formid=? \n"; |
| | | private final String GET_GFIELD = " set nocount on; select " + gfield + " from gfield where formid=? order by statisid asc\n"; |
| | | private final String GET_GFIELD9 = " set nocount on; select " + gfield + " from gfield where formid=? and HeadFlag=? order by statisid asc\n";// 9类型窗体 |
| | | private final static String GET_WINDOWTYPE = " set nocount on; select FormType from _sysMenu where formid=? \n"; |
| | | private final static String GET_FTDATA = " set nocount on; select interValue,dictvalue from _sysdict where dictid=? order by sequence asc\n"; |
| | | |
| | | // 保存时执行处理 |
| | | private final static String SAVE_PROC = "set nocount on; select [formid]\n" + |
| | | private final static String SAVE_PROC = " set nocount on; select [formid]\n" + |
| | | " ,[execSeq]\n" + |
| | | " ,[ProcName]\n" + |
| | | " ,[action]\n" + |
| | | " ,[objectType]\n" + |
| | | " ,[memo]\n" + |
| | | " ,[inactive] from _sys_FormProc where formid=? and isnull(inactive,0)=0 order by execseq asc"; |
| | | " ,[inactive] from _sys_FormProc where formid=? and isnull(inactive,0)=0 order by execseq asc\n"; |
| | | // 删除前存储过程 |
| | | private final static String DEL_PROC = "set nocount on; select execProc from _sysDeleteDoc where formid=? order by seqn asc"; |
| | | private final static String DEL_PROC = " set nocount on; select execProc from _sysDeleteDoc where formid=? order by seqn asc\n"; |
| | | // 过帐类型 |
| | | private final static String TRANS_GROUP = "set nocount on; select a.reloaddata,a.datatable,a.filterstring,a.updatesql,a.updatesql2, \n" + " b.CheckView as BeforeSQlCheckView,b.EmptyRowRaised as BeforeSQlEmptyRowRaised,b.CheckRaiseMsg as BeforeSQlCheckRaiseMsg, \n" |
| | | private final static String TRANS_GROUP = " set nocount on; select a.reloaddata,a.datatable,a.filterstring,a.updatesql,a.updatesql2, \n" + " b.CheckView as BeforeSQlCheckView,b.EmptyRowRaised as BeforeSQlEmptyRowRaised,b.CheckRaiseMsg as BeforeSQlCheckRaiseMsg, \n" |
| | | + " c.CheckView as AfterSQlCheckView,c.EmptyRowRaised as AfterSQlEmptyRowRaised,c.CheckRaiseMsg as AfterSQlCheckRaiseMsg \n" + " from vsystransgroup a left join _sysTransPostCheck b on a.BeforeSQlcheckid = b.CheckID \n" |
| | | + " left join _sysTransPostCheck c on a.AfterSQLcheckid = c.CheckID \n" + " where a.transgroupcode=? and a.actived=1 order by a.sortid asc \n"; |
| | | // @Autowired |
| | |
| | | public String getGET_GFIELD9() { |
| | | return gfield; |
| | | } |
| | | |
| | | @Override |
| | | public String getExcludeTablesWhenSaving(int formid){ |
| | | return this.jdbcTemplate.queryForObject("select excludeTablesWhenSaving from gform where formid=?", String.class,formid); |
| | | } |
| | | @Override |
| | | public void doNewTran(Integer formid, String docCode,String userCode,String userName) { |
| | | String sql=" set nocount on \n declare @docCode varchar(50)=? ,@PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int ,@EnterCode varchar(50)=?,@EnterName varchar(50)=?,@Memo varchar(100),@LinkDocInfo varchar(100)\n " + |
| | |
| | | public SqlRowSet getGfiledByFormID(int formID) { |
| | | return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD, new Object[]{formID}); |
| | | } |
| | | |
| | | @Override |
| | | public List getGfiledByFormID9(int formID, int flag) { |
| | | // return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD9, new Object[]{formID,flag}); |
| | | return this.jdbcTemplate.queryForList(this.GET_GFIELD9, new Object[]{formID, flag}); |
| | | } |
| | | @Override |
| | | public List getGfiledByFormID9(int formID,int formType, int flag,String userCode) { |
| | | |
| | | List<Map<String, Object>> userList =null; |
| | | List<Map<String, Object>> list9802 =null; |
| | | if(flag!=-1) { |
| | | userList = this.jdbcTemplate.queryForList("select rowno,colno,lengthnum,heightnum,fieldid,statisid,showongrid,headflag,visible from gFieldCustomLayout where usercode=? and formid=? and formType=? and headflag=? order by headflag asc,statisid asc", new Object[]{userCode, formID, formType, flag}); |
| | | list9802 = this.jdbcTemplate.queryForList(this.GET_GFIELD9.toLowerCase(), new Object[]{formID, flag}); |
| | | }else { |
| | | //-1表示主从表都要取 |
| | | userList = this.jdbcTemplate.queryForList("select rowno,colno,lengthnum,heightnum,fieldid,statisid,showongrid,headflag,visible from gFieldCustomLayout where usercode=? and formid=? and formType=? order by headflag asc,statisid asc", new Object[]{userCode, formID,formType}); |
| | | list9802 = this.jdbcTemplate.queryForList(" set nocount on; select " + gfield.toLowerCase() + " from gfield where formid=? order by headflag asc,statisid asc\n", new Object[]{formID}); |
| | | } |
| | | List<Map<String, Object>> newList=new ArrayList<>(); |
| | | if(userList!=null&&userList.size()>0) { |
| | | //---1,把个人数据从总的9802里取匹配项出来放到一个newList同时删除对应项 |
| | | for (Map<String, Object> map : userList) { |
| | | for (Map<String, Object> map2 : list9802) { |
| | | if ((map.get("fieldid")+"").equalsIgnoreCase(map2.get("fieldid")+"") |
| | | &&(map.get("headflag")+"").equalsIgnoreCase(map2.get("headflag")+"") |
| | | ) { |
| | | map2.put("statisid",map.get("statisid"));//更新statisid |
| | | map2.put("showongrid",map.get("showongrid"));//更新字段在表格是否可见 |
| | | map2.put("visible",map.get("visible"));//更新字段在面板是否可见 |
| | | map2.put("rowno",map.get("rowno"));//行号 |
| | | map2.put("colno",map.get("colno"));//列号 |
| | | newList.add(map2); |
| | | list9802.remove(map2); |
| | | break; |
| | | } |
| | | } |
| | | } |
| | | for(int i=0;i<2;i++) { |
| | | final int filterFlag=i; |
| | | Integer lastStatisid = 0; |
| | | //处理主从表各自最大序号 |
| | | Optional<Map<String,Object>> optional=newList.stream().filter(x->((Integer)x.get("headflag")).intValue()==filterFlag).max((o1, o2) -> (Integer) o1.get("statisid") - (Integer) o2.get("statisid")); |
| | | if(optional.isPresent()) { |
| | | lastStatisid= Integer.parseInt(optional.get().get("statisid") + ""); |
| | | } |
| | | if(lastStatisid==0) continue;//不存在自定义设置则跳出 |
| | | for (Map<String, Object> x : list9802) { |
| | | if(((Integer)x.get("headflag")).intValue()==filterFlag){ |
| | | //相同的才处理 |
| | | lastStatisid += 10; |
| | | x.put("statisid", lastStatisid);//更新statisid |
| | | } |
| | | } |
| | | } |
| | | //---2,再把剩余的项都加到newList里面返回 |
| | | newList.addAll(list9802); |
| | | }else { |
| | | newList=list9802; |
| | | } |
| | | return newList; |
| | | } |
| | | |
| | | @Override |
| | |
| | | String rowcopyformids = null; |
| | | String sql = ""; |
| | | if (page.getWinType().startsWith("15")) {//15类型 |
| | | sql = "set nocount on; select rowcopyformids from gform where formid=(select formid from _sysmasterdetail where DetailFormID=?) "; |
| | | sql = " set nocount on; select rowcopyformids from gform where formid=(select formid from _sysmasterdetail where DetailFormID=?) \n"; |
| | | } else if (page.getWinType().startsWith("49")) {//多表 |
| | | sql = " set nocount on; select rowcopyformids from gform where formid=(select mainformid from _sys_TabPageFormid where formid=?) "; |
| | | sql = " set nocount on; select rowcopyformids from gform where formid=(select mainformid from _sys_TabPageFormid where formid=?) \n"; |
| | | } else { |
| | | sql = "set nocount on; select rowcopyformids from gform where formid=? "; |
| | | sql = " set nocount on; select rowcopyformids from gform where formid=? \n"; |
| | | } |
| | | try { |
| | | rowcopyformids = this.getJdbcTemplate().queryForObject(sql, String.class, page.getFormid()); |
| | |
| | | } |
| | | } |
| | | if (page.getFlag() == 2 || (page.isNull && page.getFlag() != 1) || (page.getFlag() != 1 && page.getWhere().length() == 0) || isCp) { |
| | | page.setWhere("1=2"); |
| | | page.setWhere(" 1=2 "); |
| | | } |
| | | if (page.getWhere().trim().indexOf("and") == 0) { |
| | | page.setWhere("1=1 " + page.getWhere()); |
| | | page.setWhere(" 1=1 " + page.getWhere()); |
| | | } |
| | | page.setTbCols(this.proccTbCols(page)); |
| | | //----生成10个数组处理参数内容过长,需要截断分组传 |
| | | String[] tbcols = new String[10]; |
| | | int len = page.getTbCols().length(); |
| | | Map tbColsMap = new HashMap(); |
| | | if (len > 0) { |
| | | for (int i = 0; i < 10; i++) { |
| | | if (len >= (i + 1) * 3500) { |
| | | tbcols[i] = page.getTbCols().substring(i * 3500, (i + 1) * 3500); |
| | | tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]); |
| | | } else { |
| | | tbcols[i] = page.getTbCols().substring(i * 3500, len); //endIndex只能是>=beginIndex |
| | | tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]); |
| | | break; |
| | | } |
| | | } |
| | | } |
| | | //----end |
| | | page.setSql(page.getSql().replaceAll("%2F", "/").replaceAll("%2B", "+")); |
| | | |
| | | page.setTbColsMap(tbColsMap); |
| | | Map<String, Object> map = null; |
| | | try { |
| | | if ("SP_viewPageV4" .equals(page.getPROC_NAME())) { |
| | | map = this.simpleJdbcCall.withProcedureName("SP_viewPageV4") |
| | | .declareParameters(// 定义存储过程参数返回值 |
| | | new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR)) |
| | | .execute(page.getTableName(), page.getSql(), |
| | | page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0, |
| | | page.getPageSize(), page.getPageNum(), page.getGroupby(), |
| | | tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4], |
| | | tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9], |
| | | page.getFormid(), page.getUserCode(), page.getUserName(), |
| | | 0, 0, null); |
| | | } else { |
| | | //--19类型用SP_viewPageV3 |
| | | map = this.simpleJdbcCallByProc.withProcedureName("SP_viewPageV3") |
| | | .declareParameters(// 定义存储过程参数返回值 |
| | | new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR)) |
| | | .execute(page.getTableName(), page.getSql(), |
| | | page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0, |
| | | page.getPageSize(), page.getPageNum(), page.getGroupby(), |
| | | tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4], |
| | | tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9], |
| | | page.getFormid(), page.getUserCode(), page.getUserName(), |
| | | 0, 0, null); |
| | | } |
| | | page.setData((List) map.get("#result-set-1")); |
| | | page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount")); |
| | | page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount")); |
| | | page.setTbColsOut((String) map.get("TotalTbColsOut")); |
| | | if (page.getTbColsOut() != null && !"" .equals(page.getTbColsOut())) {//处理返回是0.的情况,在前端会显示NaN |
| | | String temp = page.getTbColsOut().replaceAll("\\.#", "#"); |
| | | page.setTbColsOut(temp); |
| | | } |
| | | return page; |
| | | } catch (Exception e) { |
| | | |
| | | Map exceptoinMmap = new HashMap(); |
| | | exceptoinMmap.put("@TableName", page.getTableName()); |
| | | exceptoinMmap.put("@FieldList1", page.getSql()); |
| | | exceptoinMmap.put("@Where", page.getWhere()); |
| | | exceptoinMmap.put("@Order", page.getOrderBy()); |
| | | exceptoinMmap.put("@SortType", page.getAutopaging()); |
| | | exceptoinMmap.put("@RecorderCount", 0); |
| | | exceptoinMmap.put("@PageSize", page.getPageSize()); |
| | | exceptoinMmap.put("@PageIndex", page.getPageNum()); |
| | | exceptoinMmap.put("@groupby", page.getGroupby()); |
| | | exceptoinMmap.put("@TotalTbCols", tbcols[0]); |
| | | exceptoinMmap.put("@TotalTbCols2", tbcols[1]); |
| | | exceptoinMmap.put("@TotalTbCols3", tbcols[2]); |
| | | exceptoinMmap.put("@TotalTbCols4", tbcols[3]); |
| | | exceptoinMmap.put("@TotalTbCols5", tbcols[4]); |
| | | exceptoinMmap.put("@TotalTbCols6", tbcols[5]); |
| | | exceptoinMmap.put("@TotalTbCols7", tbcols[6]); |
| | | exceptoinMmap.put("@TotalTbCols8", tbcols[7]); |
| | | exceptoinMmap.put("@TotalTbCols9", tbcols[8]); |
| | | exceptoinMmap.put("@TotalTbCols10", tbcols[9]); |
| | | exceptoinMmap.put("@FormId", page.getFormid()); |
| | | exceptoinMmap.put("@UserCode", page.getUserCode()); |
| | | exceptoinMmap.put("@UserName", page.getUserName()); |
| | | exceptoinMmap.put("@TotalCount", 0); |
| | | exceptoinMmap.put("@TotalPageCount", 0); |
| | | exceptoinMmap.put("@TotalTbColsOut", null); |
| | | throw new ApplicationException(e.getMessage() + ";所传参数【" + JSON.toJSONString(exceptoinMmap) + "】"); |
| | | if ("SP_viewPageV4" .equals(page.getPROC_NAME())) { |
| | | map = this.simpleJdbcCall.withProcedureName("SP_viewPageV4") |
| | | .declareParameters(// 定义存储过程参数返回值 |
| | | new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR)) |
| | | .execute(page.getTableName(), page.getSql(), |
| | | page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0, |
| | | page.getPageSize(), page.getPageNum(), page.getGroupby(), |
| | | tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4], |
| | | tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9], |
| | | page.getFormid(), page.getUserCode(), page.getUserName(), |
| | | 0, 0, null); |
| | | } else { |
| | | //--19类型用SP_viewPageV3 |
| | | map = this.simpleJdbcCallByProc.withProcedureName("SP_viewPageV3") |
| | | .declareParameters(// 定义存储过程参数返回值 |
| | | new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR)) |
| | | .execute(page.getTableName(), page.getSql(), |
| | | page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0, |
| | | page.getPageSize(), page.getPageNum(), page.getGroupby(), |
| | | tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4], |
| | | tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9], |
| | | page.getFormid(), page.getUserCode(), page.getUserName(), |
| | | 0, 0, null); |
| | | } |
| | | page.setData((List) map.get("#result-set-1")); |
| | | page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount")); |
| | | page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount")); |
| | | page.setTbColsOut((String) map.get("TotalTbColsOut")); |
| | | if (page.getTbColsOut() != null && !"".equals(page.getTbColsOut())) {//处理返回是0.的情况,在前端会显示NaN |
| | | String temp = page.getTbColsOut().replaceAll("\\.#", "#"); |
| | | page.setTbColsOut(temp); |
| | | } |
| | | //处理权限表达式为0时,需要加密输出 |
| | | if (page.getData() != null && page.getData().size() > 0) { |
| | | page.getData().parallelStream().forEach(x -> { |
| | | Map<String, Object> objectMap = (Map<String, Object>) x; |
| | | for (Entry<String, Object> entry : objectMap.entrySet()) { |
| | | if (objectMap.containsKey(entry.getKey() + "_expr") && |
| | | objectMap.get(entry.getKey() + "_expr").equals(0)) { |
| | | try { |
| | | if (entry.getValue() != null) { |
| | | objectMap.put(entry.getKey(), ChangePassword.getEncryptPassword(entry.getValue().toString())); |
| | | } |
| | | } catch (Exception e) { |
| | | throw new RuntimeException(e); |
| | | } |
| | | } |
| | | } |
| | | }); |
| | | } |
| | | return page; |
| | | } |
| | | |
| | | |
| | |
| | | if (page.getWhere() != null && !"=".equals(z) && page.getWhere().length() < 50) { |
| | | String[] q = page.getWhere().split("="); |
| | | if (q.length >= 3 && "9822".equals(q[2])) { |
| | | page.setWhere("statisid=" + "'" + statisid + "'" + " and formid=" + formid); |
| | | page.setWhere(" statisid=" + "'" + statisid + "'" + " and formid=" + formid); |
| | | } |
| | | } |
| | | |
| | | //} |
| | | if (page.getWhere().length() < 24) { |
| | | String[] where = page.getWhere().split(";"); |
| | | String[] where = page.getWhere().replace("(","").replace(")","").trim().split(";"); |
| | | |
| | | if (Integer.parseInt(where[0]) == 0) { |
| | | String sql1 = "set nocount on; SELECT fieldid,fieldcaption,fieldalias,displayYN,StatisType,Sequence ,DisplayWidth,displayformat,cellAlign,sumfield,funclinkname,isFilterZero,jionFlag,jionFlagGroup,conFlag,modfvalues,modfvalues2 from _sysStatisDetail where StatisID=" + "'" + where[2] + "'" + " and FormID=" + where[1] + " " |
| | | String sql1 = " set nocount on; SELECT fieldid,fieldcaption,fieldalias,displayYN,StatisType,Sequence ,DisplayWidth,displayformat,cellAlign,sumfield,funclinkname,isFilterZero,jionFlag,jionFlagGroup,conFlag,modfvalues,modfvalues2 from _sysStatisDetail where StatisID=" + "'" + where[2] + "'" + " and FormID=" + where[1] + " " |
| | | + "UNION ALL " |
| | | + "select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias,0 as displayYN,'' as StatisType,0 AS Sequence,'' as DisplayWidth," |
| | | + "displayformat,cellAlign,'' as sumfield,'' as funclinkname,'' as isFilterZero,'' as jionFlag,'' as jionFlagGroup,'' as conFlag,'' as modfvalues,'' as modfvalues2 " |
| | | + "FROM gfield a where formid=" + where[1] + " and headflag = 0 and (isnull(fieldname,'') <> '' or isnull(gridcaption,'') <> '' ) AND not EXISTS(select 1 FROM _sysStatisDetail b WHERE b.StatisID=" + "'" + where[2] + "'" + " and FormID=" + where[1] + " AND a.FieldID=b.FieldID)" |
| | | + "order by displayYN DESC,Sequence asc,fieldcaption ASC"; |
| | | + "order by displayYN DESC,Sequence asc,fieldcaption ASC \n"; |
| | | page.setWhere(sql1); |
| | | } |
| | | } else { |
| | |
| | | } |
| | | page.setData(list); |
| | | } else { |
| | | long s = System.currentTimeMillis(); |
| | | if (page.getFlag() == 2 || (page.isNull && page.getFlag() != 1) || (page.getFlag() != 1 && page.getWhere().length() == 0)) |
| | | page.setWhere("1=2"); |
| | | if (page.getWhere().trim().indexOf("and") == 0) page.setWhere("1=1 " + page.getWhere()); |
| | |
| | | |
| | | @Override |
| | | public Page loaddata(Page page, int formid) { |
| | | String sql = "set nocount on; select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias," |
| | | String sql = " set nocount on; select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias," |
| | | + "0 as displayYN,'' as StatisType,0 AS Sequence,'' as DisplayWidth," |
| | | + "displayformat," |
| | | + "cellAlign,'' as sumfield,'' as funclinkname FROM gfield where formid=" + formid + " and headflag = 0 and (isnull(fieldname,'') <> '' or isnull(gridcaption,'') <> '' ) order by headflag desc ,statisid asc "; |
| | | + "cellAlign,'' as sumfield,'' as funclinkname FROM gfield where formid=" + formid + " and headflag = 0 and (isnull(fieldname,'') <> '' or isnull(gridcaption,'') <> '' ) order by headflag desc ,statisid asc \n"; |
| | | List<Map<String, Object>> list = type38Ifc.girddata(sql); |
| | | /* for(int i =0;i<list.size();i++){ |
| | | list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>"); |
| | |
| | | |
| | | @Override |
| | | public List<TableColumnsDataTypeEntity> getTableColumnsDataTypes(int formid, int isDetailTable, String fieldids) { |
| | | String sql = "set nocount on \n " + |
| | | String sql = " set nocount on \n " + |
| | | " declare @table varchar(2000) ,@formid int =? , @isDetailTable int =?," + |
| | | " @fieldids varchar(max) =? \n" + |
| | | " declare @formtype int ,@pos int =0\n" + |
| | |
| | | " from INFORMATION_SCHEMA.COLUMNS a \n" + |
| | | " where a.TABLE_NAME = @table \n" + |
| | | " and (isnull(@fieldids,'') = '' or a.COLUMN_NAME in (select list from getinstr(@fieldids)))\n" + |
| | | " end"; |
| | | " end\n"; |
| | | |
| | | List<Map<String, Object>> list = this.getSimpleJdbcTemplate().queryForList(sql, formid, isDetailTable, fieldids); |
| | | if (list != null) { |
| | |
| | | |
| | | @Override |
| | | public SqlRowSetMetaData getMetaData(String tableName) { |
| | | return this.jdbcTemplate.queryForRowSet("set nocount on; select * from " + tableName + " where 1=2").getMetaData(); |
| | | return this.jdbcTemplate.queryForRowSet(" set nocount on; select * from " + tableName + " where 1=2\n").getMetaData(); |
| | | } |
| | | |
| | | private String getNewDocCode(Grid grid, HttpServletRequest request, String formid, int cont) { |
| | | private String getNewDocCode(Grid grid, HttpServletRequest request, String formid) { |
| | | String usercode; |
| | | if (request == null)// 不是通过页面提交,像手机或淘宝接口 |
| | | usercode = grid.getEnv().get(SessionKey.USERCODE); |
| | | else usercode = (String) request.getSession().getAttribute(SessionKey.USERCODE); |
| | | return " exec sp_newdoccode " + formid + ",'" + usercode + "',@newDoccode" + cont + " output\n "; |
| | | return " exec sp_newdoccode " + formid + ",'" + usercode + "',@newDoccode output\n "; |
| | | } |
| | | |
| | | private String getCurNewDocCode(String docPram, int cont) { |
| | | return " exec " + docPram.replaceAll("''$", "@newDoccode" + cont + " output\n "); |
| | | private String getCurNewDocCode(String docPram) { |
| | | return " exec " + docPram.replaceAll("''$", "@newDoccode output\n "); |
| | | } |
| | | |
| | | private String getRowidDeclare(int cont) { |
| | | return "\nset nocount on \n declare @rowid" + cont + " varchar(100),@detailrowid" + cont + " varchar(100);\n "; |
| | | } |
| | | |
| | | |
| | | @Override |
| | | public SqlInfo doExecute(DoExecuteParameter parameterObject, HttpServletRequest request, HttpServletResponse response, List<String> picList, Grid grid, String formid, String docPram, int cont) throws DataAccessException { |
| | | public SqlInfo doExecute(DoExecuteParameter parameterObject, HttpServletRequest request, HttpServletResponse response, List<PicEntity> picList, Grid grid, String formid, String docPram) throws DataAccessException { |
| | | String parm = parameterObject.getParm(); |
| | | String temcode = parameterObject.getDoccode();// 临时值 |
| | | int status = -1; |
| | | String tempCode = parameterObject.getDoccode();// 临时值 |
| | | StringBuffer sql = new StringBuffer(); |
| | | sql.append(this.getRowidDeclare(cont)); |
| | | SqlInfo info = new SqlInfo(); |
| | | String dbid = request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + ""; |
| | | // 取单号 |
| | | if (cont == 1) {//导入情况会出现多条 xin 2019-11-1 14:14:25 |
| | | //onlinePay int=-1,onlineRefund int=-1,表示初始值为-1,如果设置了在线支付,退款。则会执行查询,只会是0,1情况。这样返回前端时,就可以判断到不同的情况 |
| | | sql.append(" declare @table table (docCode varchar(20),rowid varchar(50),detailRowid varchar(50),Memo varchar(200),LinkDocInfo varchar(200),onlinePay int,onlineRefund int); \n declare @Memo1 varchar(200),@LinkDocInfo1 varchar(200),@onlinePay int=-1,@onlineRefund int=-1 \n"); |
| | | } |
| | | sql.append(" declare @newDoccode" + cont + " varchar(100); \n"); |
| | | if (grid.isDoccode()) { |
| | | if ("".equalsIgnoreCase(temcode) || ("@newDoccode" + cont).equalsIgnoreCase(temcode)) { |
| | | if ("".equalsIgnoreCase(tempCode) || ("@newDoccode").equalsIgnoreCase(tempCode)) { |
| | | if (docPram != "" && docPram.length() > 0) {// 自定义单号 |
| | | sql.append(this.getCurNewDocCode(docPram, cont)); |
| | | } else sql.append(this.getNewDocCode(grid, request, formid, cont)); |
| | | sql.append(this.getCurNewDocCode(docPram)); |
| | | } else{ |
| | | sql.append(this.getNewDocCode(grid, request, formid)); |
| | | } |
| | | } else { |
| | | info.setDoccode(temcode); |
| | | sql.append(" set @newDoccode" + cont + "='" + temcode + "' \n"); |
| | | info.setDoccode(tempCode); |
| | | sql.append(" set @newDoccode='" + tempCode + "' \n"); |
| | | } |
| | | } |
| | | if (!"".equalsIgnoreCase(parameterObject.getStatus()) && !"".equalsIgnoreCase(parameterObject.getTableName())) { |
| | | |
| | | // 判断状态值 |
| | | sql.append(checkDocStatus(parameterObject, temcode, cont)); |
| | | sql.append(checkDocStatus(parameterObject, tempCode)); |
| | | } |
| | | |
| | | if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getCanelProc())) {// 不需要保存而执行取消确认 |
| | | // TODO 取消确认功能 如何标识需要到时处理 |
| | | // temcode="canel#"+temcode+"#"+(returnValue==null?"":returnValue); |
| | | sql.append(prossCanelProc(parameterObject, temcode, 1, dbid, cont)); |
| | | sql.append(" \n set @Memo1=@Memo \n set @LinkDocInfo1=@LinkDocInfo \n"); |
| | | // tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue); |
| | | sql.append(prossCanelProc(parameterObject, tempCode, 1,dbid)); |
| | | sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n"); |
| | | info.setSql(sql.toString()); |
| | | info.setCanel("canel#"); |
| | | return info; |
| | | } |
| | | if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getRevokeProc())) {// 执行撤回 |
| | | // TODO 取消确认功能 如何标识需要到时处理 |
| | | // temcode="canel#"+temcode+"#"+(returnValue==null?"":returnValue); |
| | | sql.append(prossCanelProc(parameterObject, temcode, 2, dbid, cont)); |
| | | sql.append(" \n set @Memo1=@Memo \n set @LinkDocInfo1=@LinkDocInfo \n"); |
| | | // tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue); |
| | | sql.append(prossCanelProc(parameterObject, tempCode, 2, dbid)); |
| | | sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n"); |
| | | info.setSql(sql.toString()); |
| | | info.setCanel("revoke#");// |
| | | return info; |
| | | } |
| | | if (!parameterObject.isFl() && parameterObject.getFormid() != 9646 && parameterObject.getFormid() != 9654) {// 不需要保存而执行审核,通过 |
| | | |
| | | sql.append(" exec ").append(parameterObject.getOaButtonProcName()).append(parameterObject.getOaButtonProcParms()).append("\n").toString(); |
| | | sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(parameterObject.getOaButtonProcParms()).append("\n").toString(); |
| | | info.setSql(sql.toString()); |
| | | return info; |
| | | |
| | | } |
| | | int in = parameterObject.getInsertsql().size(); |
| | | int up = parameterObject.getUpdatesql().size(); |
| | | |
| | | int in = parameterObject.getDetailSql().size(); |
| | | int up = parameterObject.getHeadSql().size(); |
| | | int de = parameterObject.getDel().size(); |
| | | int to = parameterObject.getTotalProc().size(); |
| | | |
| | | String[] list = new String[in + up + de]; |
| | | String[] toBeStored = parameterObject.getInsertsql().toArray(new String[in]); |
| | | String[] toBeStored1 = parameterObject.getUpdatesql().toArray(new String[up]); |
| | | String[] toBeStored = parameterObject.getHeadSql().toArray(new String[in]); |
| | | String[] toBeStored1 = parameterObject.getDetailSql().toArray(new String[up]); |
| | | String[] toBeStored_p = parameterObject.getTotalProc().toArray(new String[to]); |
| | | String[] toBeStored2 = parameterObject.getDel().toArray(new String[de]); |
| | | // 1-------新增数据 |
| | | if (in > 0) { |
| | | System.arraycopy(toBeStored, 0, list, 0, in); |
| | | //由于增加了触发器更新版本号功能,所以执行顺序很重要,要按照先主表后从表的顺序组装sql,保证不会出现【当前单据内容已发生变化,请刷新页面然后重试此操作】提示 |
| | | // 1-------表头数据 |
| | | if (up > 0) { |
| | | System.arraycopy(toBeStored, 0, list, 0, up); |
| | | |
| | | } |
| | | // 2----------更新数据 |
| | | if (up > 0) { |
| | | System.arraycopy(toBeStored1, 0, list, in, up); |
| | | // 2----------表体数据 |
| | | if (in > 0) { |
| | | System.arraycopy(toBeStored1, 0, list, up, in); |
| | | } |
| | | // 3----------删除数据 |
| | | if (de > 0) { |
| | | System.arraycopy(toBeStored2, 0, list, in + up, de); |
| | | System.arraycopy(toBeStored2, 0, list, up + in, de); |
| | | } |
| | | int[] count = null; |
| | | // if(list.length>0) |
| | | // this.jdbcTemplate.batchUpdate(list);//批量更新数据 |
| | | // |
| | | // 提交数据 |
| | | for (String str : list) |
| | | for (String str : list) { |
| | | sql.append(str).append(" \n"); |
| | | |
| | | } |
| | | // 有删除图片的在这里执行 |
| | | if (picList.size() > 0) { |
| | | |
| | | for (String pic : picList) { |
| | | StringBuilder delPicSql=new StringBuilder(); |
| | | for (PicEntity picEntity : picList) { |
| | | String uuid = null; |
| | | // String seq = null; |
| | | String[] splt = null; |
| | | //String type = null;//单附件,还是多附件 |
| | | // if (pic.indexOf("uuid") > -1) { |
| | | // |
| | | // String typePattern = pic.split("uuid")[0]; |
| | | // Pattern p = Pattern.compile("\\d"); |
| | | // java.util.regex.Matcher propsMatcher = p.matcher(typePattern); |
| | | // while (propsMatcher.find()) { |
| | | // type = propsMatcher.group(); |
| | | // } |
| | | // |
| | | // pic = pic.replaceAll("type=\\w.*=", "").replaceAll("\\|.*?$", ""); |
| | | // |
| | | // } |
| | | splt = pic.split(";");//分割出uuid和seq,seq可能存在多个的情况,需要处理这种情况,0A283B93-07ED-46B6-A66C-7223A71D94FE;9458;9567 |
| | | |
| | | splt = picEntity.getContext().split(";");//分割出uuid和seq,seq可能存在多个的情况,需要处理这种情况,0A283B93-07ED-46B6-A66C-7223A71D94FE;9458;9567 |
| | | picEntity.setIp(IPUtil.getIpAddr(request)); |
| | | picEntity.setUserCode( request.getSession().getAttribute(SessionKey.USERCODE) + ""); |
| | | picEntity.setUserName(request.getSession().getAttribute(SessionKey.USER_NAME) + ""); |
| | | picEntity.setType(0); |
| | | uuid = splt[0]; |
| | | final CloseableHttpClient client = HttpClientUtil.getClient(); |
| | | if(delPicSql.length()==0&& org.apache.commons.lang3.StringUtils.isNotBlank(uuid)){ |
| | | //第一次且有附件才需要增加变量的定义 |
| | | delPicSql.append(" \n set nocount on \n " + |
| | | " declare @unid_Attachment varchar(50),@seq_Attachment int \n" |
| | | + " declare @AcType_Attachment varchar(50) \n" |
| | | + " declare @usercode_Attachment varchar(50) \n" |
| | | + " declare @username_Attachment varchar(50) \n" |
| | | + " declare @ip_Attachment varchar(50) \n" |
| | | + " declare @mydelPicrowcount_Attachment int = 0 \n"); |
| | | } |
| | | for (int i = 1; i < splt.length; i++) { |
| | | //磁盘文件删除,通过httpclient调用提交给附件服务器系统 |
| | | try { |
| | |
| | | e.printStackTrace();//避免删除文件出错影响主进程操作,不进行处理 |
| | | } |
| | | //数据库内容删除,直接调用本地代码 |
| | | attachmentIfc.deleteAttachment(uuid, Integer.parseInt(splt[i]), 0, "删除", request.getSession().getAttribute(SessionKey.USERCODE) + "", request.getSession().getAttribute(SessionKey.USER_NAME) + "", IPUtil.getIpAddr(request)); |
| | | picEntity.setUuid(uuid); |
| | | picEntity.setSeq(Integer.parseInt(splt[i])); |
| | | delPicSql.append(attachmentIfc.getdeleteAttachmentSql(picEntity)); |
| | | } |
| | | } |
| | | if(delPicSql.length()>0){ |
| | | sql.append(delPicSql.toString()); |
| | | } |
| | | } |
| | | if ("".equals(excel.getSqlext())) { |
| | |
| | | } |
| | | excel.setSqlext(sqlext.toString()); |
| | | } |
| | | // 5---------执行保存时执行存储过程组 |
| | | // 5---------9643执行保存时执行存储过程组 |
| | | String p_str = "";//判断是否有相同的,只输出一个 |
| | | if (toBeStored_p.length > 0) { |
| | | for (String s : toBeStored_p) { |
| | | if (!s.equalsIgnoreCase(p_str)) |
| | | sql.append(s); |
| | | p_str = s; |
| | | sql.append(" \n "+s); |
| | | p_str += s + ";"; |
| | | } |
| | | } |
| | | // ---- |
| | | |
| | | // 6---- |
| | | if (parameterObject.isFl()) { |
| | | //下列的执行次序不能乱 |
| | | // ---excel导入 先执行后保存 |
| | | if (!"".equalsIgnoreCase(parameterObject.getExcelProc()) || parameterObject.getExcelProc().length() > 0) { |
| | | sql.append(" \n ").append(parameterObject.getExcelProc()).append("\n"); |
| | | if(parameterObject.getExcelProc().trim().toLowerCase().startsWith("exec")) { |
| | | //处理导入过程不带参数 少了exec关键字 |
| | | sql.append(" \n ").append(parameterObject.getExcelProc()).append("\n"); |
| | | }else { |
| | | sql.append(" \n exec ").append(parameterObject.getExcelProc()).append("\n"); |
| | | } |
| | | } |
| | | // 6----------保存时执行功能 |
| | | // ----------9801保存时执行功能 |
| | | if (!"".equalsIgnoreCase(parameterObject.getStr()) || parameterObject.getStr().length() > 0) { |
| | | sql.append(" \n exec " + parameterObject.getStr() + " " + parm).append("\n"); |
| | | if (!p_str.contains(parameterObject.getStr())) {//保存时执行存储过程组如果已存在,则去重 |
| | | sql.append(" \n exec " + parameterObject.getStr() + " " + parm).append("\n"); |
| | | } |
| | | } |
| | | // ---[-审核,通过]前先保存再执行 |
| | | if (!"".equalsIgnoreCase(parameterObject.getOaButtonProcName()) || parameterObject.getOaButtonProcName().length() > 0) |
| | | if (!"".equalsIgnoreCase(parameterObject.getOaButtonProcName()) || parameterObject.getOaButtonProcName().length() > 0) { |
| | | //---增加处理单据审核后跳转功能 |
| | | StringJoiner procParms=new StringJoiner(","); |
| | | //从后面替换 |
| | | String[] parms=parameterObject.getOaButtonProcParms().split(","); |
| | | for(int i=0;i<parms.length;i++){ |
| | | if(i==parms.length-3){ |
| | | //替换@ExcludeDocCodeWhenSelectNextDocument |
| | | procParms.add("'"+parameterObject.getJson().getExcludeSelectNextDocument()+"'"); |
| | | }else if(i==parms.length-2){ |
| | | //替换@Memo |
| | | procParms.add("@Memo output"); |
| | | }else if(i==parms.length-1){ |
| | | //替换@LinkDocInfo |
| | | procParms.add("@LinkDocInfo output"); |
| | | }else { |
| | | procParms.add(parms[i]); |
| | | } |
| | | } |
| | | |
| | | sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(" ").append(parameterObject.getOaButtonProcParms()).append("\n"); |
| | | sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(" ").append(procParms.toString()).append("\n"); |
| | | } |
| | | // ---[-调用 功能链接]前先保存再执行 |
| | | if (!"".equalsIgnoreCase(parameterObject.getFunLinkProc()) && parameterObject.getFunLinkProc().length() > 0) |
| | | if (!"".equalsIgnoreCase(parameterObject.getFunLinkProc()) && parameterObject.getFunLinkProc().length() > 0) { |
| | | |
| | | sql.append(" \n exec ").append(parameterObject.getFunLinkProc()).append(" ").append(parameterObject.getFunLinkPram()).append("\n"); |
| | | |
| | | } |
| | | // ----淘宝接口 |
| | | if (parameterObject.getJson().getTaobao() != null && parameterObject.getJson().getTaobao().size() > 0) { |
| | | // taobao.prossERP(parameterObject.getJson(),request,response,false); |
| | | |
| | | } |
| | | // ------------- |
| | | } |
| | | if(request.getAttribute("isTran")!=null&&grid.isDoccode()){ |
| | | //表示是确认操作 |
| | | sql.append(" \nupdate " + parameterObject.getTableName() + " set postCode='" +request.getSession().getAttribute(SessionKey.USERCODE)+"',postname='" + request.getSession().getAttribute(SessionKey.USERNAME) + "' where doccode=@newDoccode \n"); |
| | | } |
| | | info.setSql(sql.toString()); |
| | | return info; |
| | |
| | | /** |
| | | * 组装检查当前单号是否已确认 |
| | | */ |
| | | private String checkDocStatus(DoExecuteParameter parameterObject, String temcode, int cont) { |
| | | private String checkDocStatus(DoExecuteParameter parameterObject, String temcode) { |
| | | if ("".equalsIgnoreCase(temcode)) return ""; |
| | | StringBuffer sql = new StringBuffer(); |
| | | |
| | | sql.append(" declare @docstatus" + cont + " int;\n").append("select @docstatus" + cont + "=docstatus from " + parameterObject.getTableName() + " where doccode='" + temcode + "';\n ").append(" if @docstatus" + cont + "<>").append(parameterObject.getStatus()).append(" begin raiserror('") |
| | | sql.append(" select @docstatus=docstatus from " + parameterObject.getTableName() + " where doccode='" + temcode + "';\n ").append(" if @docstatus<>").append(parameterObject.getStatus()).append(" begin raiserror('") |
| | | .append(temcode + "-单据状态已变化,请刷新页面后再操作!',16,1); return end \n"); |
| | | return sql.toString(); |
| | | } |
| | | |
| | | private String prossCanelProc(DoExecuteParameter parameterObject, String temcode, int type, String dbid, int cont) {// 处理取消确认,撤回功能 |
| | | private String prossCanelProc(DoExecuteParameter parameterObject, String tempCode, int type, String dbid) {// 处理取消确认,撤回功能 |
| | | String proc = null; |
| | | if (type == 1) proc = parameterObject.getCanelProc(); |
| | | else proc = parameterObject.getRevokeProc(); |
| | | |
| | | if (type == 1) { |
| | | proc = parameterObject.getCanelProc(); |
| | | }else{ |
| | | proc = parameterObject.getRevokeProc(); |
| | | } |
| | | if (!"".equalsIgnoreCase(proc) && proc.length() > 0) { |
| | | // 取得过程传的参数有哪些是输出参数,以便组装sql |
| | | proc = proc.replaceAll("exec ", "").trim(); |
| | |
| | | String parms = proc.split("\\s+")[1]; |
| | | String[] arrPams = parms.split(","); |
| | | ExecuteProcAction exec = (ExecuteProcAction) FactoryBean.getBean("executeProcAction"); |
| | | String str = "declare @PeriodState" + cont + " varchar(100) exec checkPeriodHasOpen @doccode = '" + temcode + "' , @formid = " + parameterObject.getFormid() + " ,@PeriodState = @PeriodState" + cont + " output\n "; |
| | | // this.jdbcTemplate.execute(str); |
| | | String str = " exec checkPeriodHasOpen @doccode = '" + tempCode + "' , @formid = " + parameterObject.getFormid() + " ,@PeriodState = @PeriodState output\n "; |
| | | String returnValue = exec.spellProcCanel2(proName, arrPams, dbid); |
| | | //去除变量定义,已统一在前面作了定义 |
| | | Pattern p = Pattern.compile("exec.*"); |
| | | java.util.regex.Matcher propsMatcher = p.matcher(returnValue); |
| | | while (propsMatcher.find()) { |
| | | returnValue=propsMatcher.group(); |
| | | } |
| | | return str + " " + returnValue; |
| | | } |
| | | return temcode; |
| | | return tempCode; |
| | | } |
| | | |
| | | /** |
| | |
| | | type = "linkformtype"; |
| | | } |
| | | StringBuilder sql = new StringBuilder(); |
| | | sql.append("select (select hdtable from gform where formid=(select linkformid from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]).append("))") |
| | | .append(" as procs,origfields from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]); |
| | | sql.append(" select (select hdtable from gform where formid=(select linkformid from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]).append("))") |
| | | .append(" as procs,origfields from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]+" \n"); |
| | | return this.jdbcTemplate.queryForList(sql.toString()); |
| | | } |
| | | |
| | | /** |
| | | * 执行自定义生成单号 |
| | | **/ |
| | | private String createDoccode(String docParm) { |
| | | String sql = "declare @curcode varchar(100) exec " + docParm.replaceAll("''$", "@curcode output ") + " select @curcode "; |
| | | return this.jdbcTemplate.queryForObject(sql, String.class); |
| | | } |
| | | |
| | | @Override |
| | |
| | | } |
| | | |
| | | @Override |
| | | public List getDataByAjaxBy42(String tabName, String fields, String where, int limit, int page,String orderField) throws DataAccessException { |
| | | public List getDataByAjaxBy42(String tabName, String fields, String where, int limit, int page,String orderField,int formid) throws DataAccessException { |
| | | boolean isDataType=false; |
| | | HashMap<String, String> map9802 = new HashMap<>(); |
| | | if(tabName.split("\\|").length==2) { |
| | | //--取9802字段的数据类型 |
| | | List<DataTypeEntry> query9802 = this.jdbcTemplate.query("set nocount on\n select FieldID,DataType from gfield where isnull(HeadFlag,0)=1 and formid=" + formid + " and FieldID in( select list from getinstr( '" + tabName.split("\\|")[1] + "'))", new BeanPropertyRowMapper<>(DataTypeEntry.class)); |
| | | query9802.stream().forEach(e -> map9802.put(e.getFieldID().toLowerCase(), e.getDataType())); |
| | | isDataType=true; |
| | | } |
| | | String where_str = null;//针对条件一部分是用作替换参数,@G@后面部分用作sql的where条件 |
| | | if (where.trim().indexOf("@G@") == 0) { |
| | | //只有表格参数值情况,没面板参数值,所以要去掉@G@ |
| | | where = where.replaceAll("@G@", "");// 以|分隔的第二部分是格线需要用的 |
| | | where_str = where; |
| | | } else if (where.trim().indexOf("@G@") > 0) {//表示有参数值 |
| | | //表示有面板和表格的参数值 |
| | | String[] whereSplit = where.trim().split("@G@"); |
| | | if (whereSplit.length > 1) { |
| | | where_str = whereSplit[1]; |
| | | where = whereSplit[0];//参数值 |
| | | where = " ("+whereSplit[0]+") and ("+whereSplit[1]+" )";//参数值 |
| | | } |
| | | } else { |
| | | String[] temp_str = where.trim().split("@G@"); |
| | |
| | | str.append(","); |
| | | } |
| | | for (String parmter : strs) { |
| | | parmter = parmter.toLowerCase(); |
| | | parmter = parmter.toLowerCase().trim(); |
| | | if(parmter.startsWith("(")&&parmter.endsWith(")")) { |
| | | parmter=parmter.substring(1,parmter.length()-1); |
| | | } |
| | | if (parmter.trim().indexOf(toStr) > -1) { |
| | | str.append((parmter.indexOf("=") > -1 ? parmter.split("=") : parmter.split("like"))[1].replaceAll("%20", "").replaceAll("@~", "%")); |
| | | String value=(parmter.indexOf("=") > -1 ? parmter.split("=") : parmter.split("like"))[1].replaceAll("%20", "").replaceAll("@~", "%"); |
| | | if(value!=null&&value.trim().equals("'%%'")){ |
| | | value="''"; |
| | | } |
| | | str.append(value); |
| | | flag = true; |
| | | break; |
| | | } |
| | | } |
| | | if (!flag) {// 表示x没匹配的参数值,则为'' |
| | | str.append("''"); |
| | | if(isDataType) { |
| | | String dataType = map9802.get(toStr); |
| | | if (dataType == null) { |
| | | str.append(toStr); |
| | | } else { |
| | | if (dataType.equals("'")) |
| | | str.append("''"); |
| | | else |
| | | str.append("null"); |
| | | } |
| | | }else { |
| | | str.append("''"); |
| | | } |
| | | } |
| | | index++; |
| | | } |
| | |
| | | fields=fields.replaceAll("'~p~'","','"); |
| | | String orderby=fields.split(",")[0]; |
| | | |
| | | String newSql="set nocount on \n" + |
| | | " declare @Limit int = ? , @Page int = ?,@StartRowNo int ,@EndRowNo int ;" |
| | | String newSql=" set nocount on \n" + |
| | | " declare @Limit int = ? , @Page int = ?,@StartRowNo int ,@EndRowNo int ;\n" |
| | | + " declare @TotalRowCount int ; \n" |
| | | + " select @TotalRowCount = count(1) from "+tabName+" a where \n" +newWhere |
| | | + " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" |
| | |
| | | } |
| | | |
| | | @Override |
| | | public List getDataByAjax(String tabName, String fields, String where) throws DataAccessException { |
| | | public List getDataByAjax(ThreeJSON json, String where) throws DataAccessException { |
| | | String tabName = this.getTableName(json.getFormID(), json.getType() + "|" + 0); |
| | | String fields=json.getParm().replaceAll(" ", " ").replaceAll("&", "").replaceAll("nbsp;", " ") |
| | | .replaceAll("%20", " ").replaceAll(";", ",").replaceAll("\\b_ycid_\\b", "id"); |
| | | HashMap<String, String> map9802 = new HashMap<>(); |
| | | boolean isDataType=false; |
| | | if(tabName.split("\\|").length==2) { |
| | | //--取9802字段的数据类型 |
| | | List<DataTypeEntry> query9802 = this.jdbcTemplate.query("set nocount on\n select FieldID,DataType from gfield where isnull(HeadFlag,0)=1 and formid=" + json.getFormID() + " and FieldID in( select list from getinstr( '" + tabName.split("\\|")[1] + "'))", new BeanPropertyRowMapper<>(DataTypeEntry.class)); |
| | | query9802.stream().forEach(e -> map9802.put(e.getFieldID().toLowerCase(), e.getDataType())); |
| | | isDataType=true; |
| | | } |
| | | String sql = ""; |
| | | String where_str = null;//针对条件一部分是用作替换参数,@G@后面部分用作sql的where条件 |
| | | if (where.trim().indexOf("@G@") == 0) { |
| | |
| | | } |
| | | for (String s : strs) { |
| | | if (s.trim().indexOf(parmter) > -1) { |
| | | str.append((s.indexOf("=") > -1 ? s.split("=") : s.split("like"))[1].replaceAll("@~", "%")); |
| | | String value=(s.indexOf("=") > -1 ? s.split("=") : s.split("like"))[1].replaceAll("@~", "%"); |
| | | if(value!=null&&value.trim().equals("'%%'")){ |
| | | value="''"; |
| | | } |
| | | str.append(value); |
| | | flag = true; |
| | | break; |
| | | } |
| | | } |
| | | if (!flag) {// 表示x没匹配的参数值,则为'' |
| | | str.append("''"); |
| | | if(isDataType) { |
| | | String dataType = map9802.get(parmter); |
| | | if (dataType == null) { |
| | | str.append(parmter); |
| | | } else { |
| | | if (dataType.equals("'")) |
| | | str.append("''"); |
| | | else |
| | | str.append("null"); |
| | | } |
| | | }else { |
| | | str.append("''"); |
| | | } |
| | | } |
| | | index++; |
| | | } |
| | |
| | | //增加排序功能 |
| | | return this.jdbcTemplate.queryForList(sql.replaceAll("\\^", " and ")); |
| | | } |
| | | |
| | | private int proccesSymbol(String str){ |
| | | if (org.apache.commons.lang3.StringUtils.isBlank(str)) {return -1;} |
| | | boolean isSymo=str.indexOf("!")>-1?true:false;//存在!号才处理 |
| | | if(isSymo) { |
| | | Pattern p = Pattern.compile("'.*?'+?");//取出'...',判断存在!则替换,再找出真正18类型!的位置 |
| | | Matcher m = p.matcher(str); |
| | | while (m.find()) {//存在 |
| | | if (m.group().indexOf("!") > -1) { |
| | | str = str.replaceAll(m.group(), m.group().replaceAll("!", "#")); |
| | | } |
| | | } |
| | | return str.indexOf("!"); |
| | | }else { |
| | | return -1; |
| | | } |
| | | } |
| | | @SuppressWarnings("unchecked") |
| | | @Override |
| | | public Page loadAllByFunc(Page page, Map<String, String> env, boolean flg) { |
| | | String s1 = ""; |
| | | String s2 = ""; |
| | | int index = page.getWhere().indexOf("!"); |
| | | //处理参数值存在!号的情况,这样会出现可能会有多个!号,需要确定哪一个!号才是正确要处理 |
| | | |
| | | int index = proccesSymbol(page.getWhere()); |
| | | if (index > -1) {// 增加处理18类型有过滤条件的情况 |
| | | String where_str = page.getWhere().substring(index + 1); |
| | | if (where_str != "" && "1=2".equals(where_str.trim())) { |
| | |
| | | } |
| | | s1 = page.getWhere().substring(0, index); |
| | | s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@_asterisk_@", "*").replaceAll("@~", "%").replaceAll("!", " and ") + page.getDataGroup();// 增加数据组权限 12-06-11 |
| | | //处理可能存在编码了的内容,@_xxxx_@ |
| | | // s2 = GTGrid.proessFilterInfo(s2); |
| | | //替换参数里有会话值的情况 |
| | | s2 =prossFormdatafilters(s2,env); |
| | | } else { |
| | | s1 = page.getWhere(); |
| | | s2 = page.getDataGroup().trim().length() > 0 ? " where " + ((page.getDataGroup().trim().startsWith("and")) ? " 1=1 " + page.getDataGroup() : page.getDataGroup()) : ""; |
| | |
| | | // return this.setPageInfo(list, page); |
| | | // } |
| | | } |
| | | |
| | | private String prossFormdatafilters(String filter, Map<String, String> env) { |
| | | if ("".equals(filter)) return filter; |
| | | Pattern p = Pattern.compile("@.*?\\w+"); |
| | | Matcher m = p.matcher(filter); |
| | | while (m.find()) {//存在 |
| | | filter = filter.replaceAll(m.group(), env.get(m.group().toLowerCase()) + ""); |
| | | } |
| | | return filter; |
| | | } |
| | | public String proccTbCols(Page page) { |
| | | // digit#1:null,totalmoney2#1: |
| | | // 组装统计成:cast(sum(isnull(digit,0)) as nvarchar) +''|''+ cast(sum(isnull(totalmoney2,0)) as nvarchar) |
| | |
| | | public Page loadAllByFunc_for(Page page) { |
| | | String s1 = ""; |
| | | String s2 = ""; |
| | | int index = page.getWhere().indexOf("!"); |
| | | int index = proccesSymbol(page.getWhere()); |
| | | if (index > -1) {// 增加处理18类型有过滤条件的情况 |
| | | s1 = page.getWhere().substring(0, index); |
| | | s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@~", "%") + page.getDataGroup();// 增加数据组权限 12-06-11 |
| | |
| | | s1 = page.getWhere(); |
| | | s2 = " where 1=1 " + page.getDataGroup(); |
| | | } |
| | | String temp = this.getFunctionParm_for(s1); |
| | | HashMap map = new HashMap();//保存有权限表达式为0的字段 |
| | | String[] tempStr = s1.split(",");// 函数后面列出的参数,有可能包括数字,字符及参数名称 |
| | | for (String str : tempStr) { |
| | | if (str.contains("==")) { |
| | | str = str.replace("==", "@E@");//把==替换成@E@,过后再还原回来 |
| | | } |
| | | String[] result = str.split("="); |
| | | if (result.length > 1 && result[0].endsWith("_expr") && "0".equals(result[1])) { |
| | | map.put(result[0], 0); |
| | | } |
| | | } |
| | | String temp = this.getFunctionParm_for(s1, map); |
| | | if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")"; |
| | | String tableName = page.getTableName(); |
| | | if (tableName.indexOf("dbo.") == -1) tableName = "dbo." + tableName; |
| | | |
| | | String sql = "select " + page.getSql() + " from " + tableName + temp + s2 + ((page.getOrderBy() == null || page.getOrderBy().length() == 0) ? "" : " order by " + page.getOrderBy()); |
| | | List list = this.jdbcTemplate.queryForList(sql); |
| | | String sql = " select " + page.getSql() + " from " + tableName + temp + s2 + ((page.getOrderBy() == null || page.getOrderBy().length() == 0) ? "" : " order by " + page.getOrderBy()); |
| | | List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql); |
| | | //--重新加密权限表达式为0的内容 |
| | | if (org.apache.commons.lang3.StringUtils.isNotBlank(page.getFieldsExprs())) { |
| | | String exprKey = "," + page.getFieldsExprs().replaceAll(";", ",") + ","; |
| | | for (Map<String, Object> map1 : list) { |
| | | for (Map.Entry<String, Object> entry : map1.entrySet()) { |
| | | if (exprKey.contains("," + entry.getKey() + ",")) { |
| | | try { |
| | | entry.setValue(ChangePassword.getEncryptPassword(entry.getValue().toString())); |
| | | } catch (Exception e) { |
| | | throw new RuntimeException(e); |
| | | } |
| | | } |
| | | } |
| | | } |
| | | } |
| | | page.setData(list); |
| | | return page; |
| | | } |
| | |
| | | for (String s : temp) { |
| | | String[] t = s.split("="); |
| | | //取参数值 |
| | | String value=t.length == 1 ? "" : t[1]; |
| | | if(!value.matches("\\d")&&!value.startsWith("'")){ |
| | | value="'"+value+"'"; |
| | | String value = t.length == 1 ? "" : t[1]; |
| | | if (!value.matches("\\d") && !value.startsWith("'")) { |
| | | value = "'" + value + "'"; |
| | | } |
| | | par.add( value); |
| | | par.add(value); |
| | | } |
| | | return String.join(",",par); |
| | | return String.join(",", par); |
| | | } |
| | | private String getFunctionParm_for(String st) { |
| | | |
| | | private String getFunctionParm_for(String st, HashMap map) { |
| | | if ("".equalsIgnoreCase(st)) return ""; |
| | | StringBuilder sb = new StringBuilder(); |
| | | StringJoiner sb = new StringJoiner(","); |
| | | String[] temp = st.split(",");// 函数后面列出的参数,有可能包括数字,字符及参数名称 |
| | | int i = 0; |
| | | for (String str : temp) { |
| | | boolean fl = false; |
| | | int index = 0; |
| | | String cv1 = ""; |
| | | String cv2 = ""; |
| | | |
| | | index = str.indexOf("="); |
| | | |
| | | if (index > 0) { |
| | | cv2 = str.substring(index + 1); |
| | | if (i != 0) sb.append(","); |
| | | sb.append(cv2); |
| | | } else { |
| | | if (i != 0) sb.append(","); |
| | | sb.append(str); |
| | | if (str.contains("_expr")) { |
| | | continue; |
| | | } |
| | | i++; |
| | | if (str.contains("==")) { |
| | | str = str.replace("==", "@E@");//处理密文中的==与键值对的=冲突,先把==替换成@E@,过后再还原回来 |
| | | } |
| | | String[] result = str.split("="); |
| | | if (result.length == 2) { |
| | | //判断权限表达式为0 |
| | | if (map.containsKey(result[0] + "_expr") && org.apache.commons.lang3.StringUtils.isNotBlank(result[1]) && !"null".equalsIgnoreCase(result[1])) { |
| | | //解密 |
| | | try { |
| | | String value=null; |
| | | if(org.apache.commons.lang3.StringUtils.isNotBlank(result[1])) { |
| | | value = ChangePassword.getDecryptPassword(EncodeUtil.replaceUrlChar(result[1].replace("@E@", "=="))); |
| | | if (value.equalsIgnoreCase("")) { |
| | | value = null; |
| | | } |
| | | } |
| | | sb.add(value); |
| | | } catch (Exception e) { |
| | | throw new RuntimeException(e); |
| | | } |
| | | } else { |
| | | sb.add(result[1]); |
| | | } |
| | | } else { |
| | | sb.add(str); |
| | | } |
| | | } |
| | | return sb.toString(); |
| | | } |
| | | |
| | | private String getFunctionParm(String st, String parms, Map<String, String> env) { |
| | | public String getFunctionParm(String st, String parms, Map<String, String> env) { |
| | | parms = JOSNUtils.prossBase64(parms); |
| | | if ("".equalsIgnoreCase(st)) return ""; |
| | | if ("".equalsIgnoreCase(parms)) return ""; |
| | |
| | | if (index >= 0) { |
| | | leftValue = value.substring(0, index); |
| | | } |
| | | rightValue = value.substring(index + 1); |
| | | rightValue = value.substring(index + 1).trim().replace("@~","%"); |
| | | if (parameterName.trim().equalsIgnoreCase(leftValue.replaceAll("\\s", ""))) {//匹配到参数 |
| | | if (i != 0) sb.append(","); |
| | | sb.append("'null'".equalsIgnoreCase(rightValue.replaceAll("\\s", "")) ? null : rightValue); |
| | | sb.append("'null'".equalsIgnoreCase(rightValue.replaceAll("\\s", "")) ? null : (!rightValue.trim().startsWith("'")||"''".equalsIgnoreCase(rightValue))?rightValue:GridUtils.prossSqlParm(rightValue.substring(1,rightValue.length()-1))); |
| | | i++; |
| | | isfound = true; |
| | | break; |
| | |
| | | if (vl == null) |
| | | sb.append("null"); |
| | | else |
| | | sb.append("'" + vl + "'"); |
| | | sb.append(GridUtils.prossSqlParm(vl)); |
| | | i++; |
| | | isfound = true; |
| | | break; |
| | |
| | | if (vl == null) |
| | | sb.append("null"); |
| | | else |
| | | sb.append("'" + vl + "'"); |
| | | sb.append(GridUtils.prossSqlParm(vl)); |
| | | i++; |
| | | isfound = true; |
| | | break; |
| | |
| | | } |
| | | |
| | | public List<String> getDelProcGroup(int groupid) { |
| | | return this.jdbcTemplate.queryForList(DEL_PROC, new Object[]{groupid}, String.class); |
| | | return this.jdbcTemplate.queryForList(DEL_PROC, String.class,groupid); |
| | | |
| | | } |
| | | |
| | |
| | | |
| | | boolean isflag = false;// 标记是新单且是直接确认的情况,app使用 |
| | | if ("".equalsIgnoreCase(doccode)) { |
| | | doccode = "@newDoccode1"; |
| | | doccode = "@newDoccode"; |
| | | isflag = true; |
| | | } else doccode = "'" + doccode + "'"; |
| | | } else{ |
| | | doccode = "'" + doccode + "'"; |
| | | } |
| | | //int n = 0; |
| | | StringBuffer tran_sql = new StringBuffer(); |
| | | int counNum = 1; |
| | | for (SqlInfo sq : sqlInfo) { |
| | | tran_sql.append(sq.getSql()); |
| | | |
| | | tran_sql.append(updateDocStatus(doccode, tableName, formid, postCode, postname, counNum)); |
| | | tran_sql.append(updateDocStatus(doccode, tableName, formid, postCode, postname)); |
| | | |
| | | // 再执行业务逻辑 |
| | | List<TranBean> list = this.getTranList(tranid); |
| | | int index = Integer.parseInt(counNum + "" + list.size() + "");//转为字符串,再转为数值 ,保证不会出现相同的数值, 如:"1"+"5"=15 |
| | | for (TranBean tb : list) { |
| | | index++; |
| | | String index = RandomStringUtils.randomNumeric(6);//转为字符串,再转为数值 ,保证不会出现相同的数值, 如:"1"+"5"=15 |
| | | StringBuilder sb = new StringBuilder(); |
| | | String st = "set nocount on; select * from " + tb.getDatatable() + " where 1=2"; |
| | | String st = "\n set nocount on; select * from " + tb.getDatatable() + " where 1=2 \n"; |
| | | SqlRowSet row = this.jdbcTemplate.queryForRowSet(st); |
| | | // ------------组装成游标方式处理 |
| | | // -----定义变量,从设置中的sql取得,且用元数据取得相关的数据类型 |
| | |
| | | |
| | | sb.append("\n " + entry.getValue()); |
| | | } |
| | | sb.append("\n declare @myrowcount_" + index + " int \n declare @myerror_" + index + " int \n declare @errorMsg_" + index + " nvarchar(500) ");//定义输出出错信息 |
| | | sb.append("\n declare mycurPostCur_" + index + " cursor for "); |
| | | sb.append("\n declare mycurPostCur cursor for \n"); |
| | | // 提取设置中的字段名(basedigit,plantid,matcode),拼装下面这条sql |
| | | sb.append("\n select " + this.format(updatesql[1]) + " from " + tb.getDatatable() + " where doccode=" + doccode + ((tb.getFilterstring() != null && tb.getFilterstring().length() > 0) ? " and " + tb.getFilterstring() : "")); |
| | | |
| | | sb.append("\n open mycurPostCur_" + index); |
| | | sb.append("\n fetch next from mycurPostCur_" + index + " into "); |
| | | sb.append("\n open mycurPostCur \n"); |
| | | sb.append("\n fetch next from mycurPostCur into \n"); |
| | | sb.append(this.format(updatesql[2]));// 变量名 |
| | | sb.append("\n while @@FETCH_STATUS = 0 \n begin \n"); |
| | | |
| | | // -----------star |
| | | // 1------更新前检查条件 |
| | | if (tb.getBeforeSQlCheckView() != null && !"".equalsIgnoreCase(tb.getBeforeSQlCheckView())) { |
| | | sb.append("\n if exists(" + beforeView[0] + ")"); |
| | | sb.append("\n if exists(" + beforeView[0] + ")\n"); |
| | | sb.append("\n begin "); |
| | | sb.append("\n select @errorMsg_" + index + "='【'+" + beforeViewMsg[0] + "+'】'"); |
| | | sb.append("\n close mycurPostCur_" + index); |
| | | sb.append("\n deallocate mycurPostCur_" + index); |
| | | sb.append("\n raiserror(@errorMsg_" + index + ",16,1) return "); |
| | | sb.append("\n select @ErrMsgs='【'+" + beforeViewMsg[0] + "+'】'"); |
| | | sb.append("\n close mycurPostCur"); |
| | | sb.append("\n deallocate mycurPostCur"); |
| | | sb.append("\n raiserror(@ErrMsgs,16,1) return "); |
| | | sb.append("\n end \n "); |
| | | } |
| | | |
| | |
| | | flg = true; |
| | | } |
| | | sb.append("\n begin \n" + sql) |
| | | .append("\n select @myrowcount_" + index + "= @@rowcount , @myerror_" + index + " = @@error ") |
| | | .append("\n if @myerror_" + index + " <>0 \n") |
| | | .append("\n select @myrowcount= @@rowcount , @myerror = @@error \n") |
| | | .append("\n if @myerror <>0 \n") |
| | | .append("\n begin ") |
| | | .append("\n select @errorMsg_" + index + "='" + updatesql[0].replaceAll("'", "''") + "【'+" + updatesql[3] + "+'】出错'"); |
| | | .append("\n select @ErrMsgs='" + updatesql[0].replaceAll("'", "''") + "【'+" + updatesql[3] + "+'】出错' \n"); |
| | | if (flg && !"".equalsIgnoreCase(updatesql2[0])) {////表示前一个是过程调用 |
| | | String s = updatesql2[0].replace("!", ""); |
| | | if (s.trim().startsWith("raismyerror")) {//存在加上引号 |
| | |
| | | } |
| | | sb.append("\n exec " + s); |
| | | } else {//为空则用raiserror输出 |
| | | sb.append("\n close mycurPostCur_" + index); |
| | | sb.append("\n deallocate mycurPostCur_" + index); |
| | | sb.append("\n raiserror(@errorMsg_" + index + ",16,1) return "); |
| | | sb.append("\n close mycurPostCur \n"); |
| | | sb.append("\n deallocate mycurPostCur \n"); |
| | | sb.append("\n raiserror(@ErrMsgs,16,1) return \n"); |
| | | } |
| | | sb.append("\n end "); |
| | | sb.append("\n end \n"); |
| | | // 3-------update2 sql |
| | | sql = updatesql2[0]; |
| | | if (!"".equalsIgnoreCase(sql)) { |
| | | |
| | | if (!flg && !"select 1".equalsIgnoreCase(sql.trim())) {//"select 1"不执行 |
| | | if (sql.startsWith("!")) { |
| | | String s = updatesql2[0].replace("!", ""); |
| | |
| | | String str = s.trim().replace("raismyerror", ""); |
| | | s = s.replaceAll(str, " '" + str + "'"); |
| | | } |
| | | sql = "\n exec " + s + " "; |
| | | sql = "\n exec " + s + " \n"; |
| | | } |
| | | sb.append("\n if @myrowcount_" + index + " <=0 \n begin \n")//上面sql执行的更新不成功,则继续执行下面,成功则跳过 |
| | | sb.append("\n if @myrowcount <=0 \n begin \n")//上面sql执行的更新不成功,则继续执行下面,成功则跳过 |
| | | .append(sql) |
| | | .append("\n select @myrowcount_" + index + " = @@rowcount , @myerror_" + index + " = @@error "); |
| | | .append("\n select @myrowcount = @@rowcount , @myerror = @@error \n"); |
| | | if (!updatesql2[0].startsWith("!")) { |
| | | sb.append("\n if @myerror_" + index + " <>0 \n") |
| | | .append("\n begin ") |
| | | .append("\n select @errorMsg_" + index + "='" + updatesql2[0].replaceAll("'", "''") + "【'+" + updatesql2[3] + "+'】出错'") |
| | | .append("\n close mycurPostCur_" + index) |
| | | .append("\n deallocate mycurPostCur_" + index) |
| | | .append("\n raiserror(@errorMsg_" + index + ",16,1) return ") |
| | | .append("\n end "); |
| | | sb.append("\n if @myerror <>0 \n") |
| | | .append("\n begin \n") |
| | | .append("\n select @ErrMsgs='" + updatesql2[0].replaceAll("'", "''") + "【'+" + updatesql2[3] + "+'】出错' \n") |
| | | .append("\n close mycurPostCur \n") |
| | | .append("\n deallocate mycurPostCur \n") |
| | | .append("\n raiserror(@ErrMsgs,16,1) return \n") |
| | | .append("\n end \n"); |
| | | } |
| | | sb.append("\n end "); |
| | | sb.append("\n end \n"); |
| | | } |
| | | } |
| | | sb.append(" \n end "); |
| | | sb.append(" \n end \n"); |
| | | |
| | | // 4------更新后检查条件 |
| | | if (tb.getAfterSQlCheckView() != null && !"".equalsIgnoreCase(tb.getAfterSQlCheckView())) { |
| | | sb.append("\n if exists(" + afterView[0] + ")") |
| | | .append("\n begin ") |
| | | .append("\n select @errorMsg_" + index + "='【'+" + afterViewMsg[0] + "+'】'") |
| | | |
| | | .append("\n close mycurPostCur_" + index); |
| | | sb.append("\n deallocate mycurPostCur_" + index); |
| | | sb.append("\n raiserror(@errorMsg_" + index + ",16,1) return ") |
| | | .append("\n end "); |
| | | .append("\n begin \n") |
| | | .append("\n select @ErrMsgs='【'+" + afterViewMsg[0] + "+'】'") |
| | | .append("\n close mycurPostCur \n"); |
| | | sb.append("\n deallocate mycurPostCur \n" ); |
| | | sb.append("\n raiserror(@ErrMsgs,16,1) return \n") |
| | | .append("\n end \n"); |
| | | } |
| | | // ------------end |
| | | sb.append("\n fetch next from mycurPostCur_" + index + " into \n"); |
| | | sb.append("\n fetch next from mycurPostCur into \n"); |
| | | sb.append(this.format(updatesql[2])); |
| | | sb.append("\n end "); |
| | | sb.append("\n close mycurPostCur_" + index); |
| | | sb.append("\n deallocate mycurPostCur_" + index); |
| | | if (sb.length() > 0) sqllist.add(sb.toString()); |
| | | row = null; |
| | | sb = null; |
| | | sb.append("\n end \n"); |
| | | sb.append("\n close mycurPostCur \n"); |
| | | sb.append("\n deallocate mycurPostCur \n"); |
| | | if (sb.length() > 0){ |
| | | sqllist.add(sb.toString()); |
| | | } |
| | | } |
| | | counNum++; |
| | | for (String str : sqllist) { |
| | | tran_sql.append(str); |
| | | } |
| | | |
| | | |
| | | } |
| | | if (tran_sql.toString().length() > 0) { |
| | | if (isflag) { |
| | | String str = "\n insert into @table (docCode,rowid,detailRowid,Memo,LinkDocInfo) values(isnull(@newDoccode1,''),'','','','')\n" + |
| | | " select docCode,rowid,detailRowid,Memo,LinkDocInfo from @table \n";//设置返回值 |
| | | String str = "\n insert into @returnTable (docCode,rowid,detailRowid,Memo,LinkDocInfo) values(isnull(@newDoccode,''),'','','','')\n" + |
| | | " select docCode,rowid,detailRowid,Memo,LinkDocInfo from @returnTable \n";//设置返回值 |
| | | Map map = this.doSave(tran_sql.toString() + str); |
| | | return GridUtils.prossRowSetDataType_String(map, "docCode"); |
| | | } else { |
| | |
| | | } |
| | | |
| | | @Override |
| | | public String[] prossParm(SqlRowSet row, String temp, List<String> sq, int index, Map<String, String> map, Map<String, String> sessionClone, String tableName, int type) { |
| | | public String[] prossParm(SqlRowSet row, String temp, List<String> sq, String index, Map<String, String> map, Map<String, String> sessionClone, String tableName, int type) { |
| | | String colName = "";// 保存列名称 |
| | | String columVar = "";// 保存列变量 |
| | | StringBuilder errorMessage = new StringBuilder();// 保存出错时需要显示的内容,需要在这里组装变量 |
| | |
| | | |
| | | public void doSaveNoResult(String sql) throws DataAccessException { |
| | | |
| | | this.jdbcTemplate.execute(sql); |
| | | //this.jdbcTemplate.execute(sql); |
| | | //execute执行有时候会出现出错不会回滚,造成事务失效,所以换成下面这种方式 |
| | | this.doBaseExecute(sql); |
| | | } |
| | | |
| | | private String updateDocStatus(String doccode, String tabname, int formid, String postCode, String postname, int num) { |
| | | private String updateDocStatus(String doccode, String tabname, int formid, String postCode, String postname) { |
| | | // 修改单据为确认后状态值 |
| | | String sql = |
| | | // " \n declare @dataformid varchar(500)" |
| | | // " select @dataformid=dataformid from gform where formid="+formid+"\n"+ |
| | | " \n declare @myrowcount_" + num + " int,@myerror_" + num + " int \n" + |
| | | " declare @blclosed_" + num + " int ,@flg_" + num + " int,@refcode_" + num + " varchar(100); " |
| | | + "select @blclosed_" + num + "=blclosed,@refcode_" + num + "=refcode from " + tabname + " where doccode=" + doccode + " \n" |
| | | + " set @myrowcount_" + num + " = @@rowcount \n" |
| | | + " set @myerror_" + num + " = @@error \n" |
| | | + " if @myrowcount_" + num + " = 0 begin raiserror('%s单号不存在',16,1," + doccode + ") return end\n" + |
| | | // " if @blclosed_"+num+"=0 set @flg_"+num+"=1\n"+ |
| | | " if @blclosed_" + num + "=-1 begin " + " if isnull(@refcode_" + num + ",'')='' begin raiserror('冲销单的引用单号(refcode)为空',16,1) return end \n" |
| | | + " update " + tabname + " set cleardoccode=" + doccode + " ,blclosed=1 where doccode=@refcode_" + num + " \n" + |
| | | String sql = "\n select @blclosed=blclosed,@refcode=refcode from " + tabname + " where doccode=" + doccode + " \n" |
| | | + " set @myrowcount = @@rowcount \n" |
| | | + " set @myerror = @@error \n" |
| | | + " if @myrowcount = 0 begin raiserror('%s单号不存在',16,1," + doccode + ") return end\n" + |
| | | " if @blclosed=-1 begin " + " if isnull(@refcode,'')='' begin raiserror('冲销单的引用单号(refcode)为空',16,1) return end \n" |
| | | + " update " + tabname + " set cleardoccode=" + doccode + " ,blclosed=1 where doccode=@refcode \n" + |
| | | // " set @flg_"+num+"=-1\n"+ |
| | | " end\n" + " declare @predocstatus_" + num + " int,@postdocstatus_" + num + " int \n" + |
| | | /* |
| | | * 去掉通过取datafromid取审核状态, by 17-5-31 " if @flg=1 and isnull(@dataformid,'')<>'' begin \n"+ " select @predocstatus=predocstatus,@postdocstatus=postdocstatus from gform where formid in(select list from getinstr(@dataformid))\n"+ |
| | | * " set @myrowcount = @@rowcount \n"+ " set @myerror = @@error \n"+ |
| | | * " if @myrowcount = 0 begin raiserror('%s- dataformid不存在',16,1,@dataformid) return end \n"+ "end else begin \n"+ |
| | | */ |
| | | " end\n" + |
| | | " select @predocstatus=predocstatus,@postdocstatus=postdocstatus from gform where formid=" + formid + "\n" |
| | | + " set @myrowcount = @@rowcount \n" |
| | | + " set @myerror = @@error \n" |
| | | + " if @myrowcount = 0 begin raiserror('%s 功能号不存在',16,1," + formid + ") return end \n" + |
| | | |
| | | " select @predocstatus_" + num + "=predocstatus,@postdocstatus_" + num + "=postdocstatus from gform where formid=" + formid + "\n" |
| | | + " set @myrowcount_" + num + " = @@rowcount \n" |
| | | + " set @myerror_" + num + " = @@error \n" |
| | | + " if @myrowcount_" + num + " = 0 begin raiserror('%s 功能号不存在',16,1," + formid + ") return end \n" + |
| | | // " end \n"+ |
| | | " update " + tabname + " set postdate=getdate(),postCode='" + postCode + "',postname='" + postname + "' \n" |
| | | + " where doccode=" + doccode + " and DocStatus=@predocstatus\n" |
| | | + " set @myrowcount = @@rowcount \n" |
| | | + " set @myerror = @@error \n" |
| | | + " if @myrowcount = 0 begin raiserror('%s-更新出错!',16,1," + doccode + ") return end\n " |
| | | |
| | | " update " + tabname + " set DocStatus=@postdocstatus_" + num + ",postdate=getdate(),postCode='" + postCode + "',postname='" + postname + "' \n" |
| | | + " where doccode=" + doccode + " and DocStatus=@predocstatus_" + num + " \n" |
| | | + " set @myrowcount_" + num + " = @@rowcount \n" |
| | | + " set @myerror_" + num + " = @@error \n" |
| | | + " if @myrowcount_" + num + " = 0 begin raiserror('%s-已经确认!',16,1," + doccode + ") return end \n "; |
| | | |
| | | +" if not exists(select 1 from t111630 where DocCode = '"+formid+"') \n" |
| | | + " begin \n" |
| | | +" update a set DocStatus = @PostDocStatus from "+tabname+" a where a.DocCode = "+doccode+" and a.DocStatus <> isnull(@PostDocStatus,0) \n" |
| | | +" end \n"; |
| | | // 期间是否已关闭,关闭则不能再确认 |
| | | sql += " declare @PeriodState_" + num + " int \n" |
| | | + " exec checkPeriodHasOpen @doccode=" + doccode + ", @formid = " + formid + " ,@PeriodState = @PeriodState_" + num + " output \n" |
| | | + " if @PeriodState_" + num + " = 2 begin raiserror('当前单据%s所属的期间已关闭,请与财务人员联系',16,1," + doccode + ") return end \n" |
| | | + " if @PeriodState_" + num + " = 0 begin raiserror('当前单据%s所属的期间未打开,请与财务人员联系',16,1," + doccode + ") return end \n"; |
| | | sql += "\n exec checkPeriodHasOpen @doccode=" + doccode + ", @formid = " + formid + " ,@PeriodState = @PeriodState output \n" |
| | | + " if @PeriodState = 2 begin raiserror('当前单据%s所属的期间已关闭,请与财务人员联系',16,1," + doccode + ") return end \n" |
| | | + " if @PeriodState = 0 begin raiserror('当前单据%s所属的期间未打开,请与财务人员联系',16,1," + doccode + ") return end \n"; |
| | | return sql; |
| | | } |
| | | |
| | | @Override |
| | | public String replaceStr2(String s, String count) { |
| | | if (s == null || s.length() == 0) return s; |
| | | String kkt = " exec "; |
| | |
| | | if (temp.length > 1 && !temp[1].trim().startsWith("'")) { |
| | | temp[1] = "'" + temp[1] + "'"; |
| | | } |
| | | return "\n if @myrowcount_" + count + " <= 0 \n begin " + kkt + temp[0] + " " + temp[1] + " \n return end \n"; |
| | | return "\n if @myrowcount <= 0 \n begin " + kkt + temp[0] + " " + temp[1] + " \n return end \n"; |
| | | } else { |
| | | ss = s; |
| | | kkt = ""; |
| | | return "\n if @myrowcount_" + count + "<= 0 \n begin " + ss + " \n return end \n"; |
| | | return "\n if @myrowcount<= 0 \n begin " + ss + " \n return end \n"; |
| | | } |
| | | |
| | | } |
| | |
| | | * 取得表中所有计算列字段 |
| | | **/ |
| | | public List getColumnsTypeInfo(String tableName) { |
| | | return this.jdbcTemplate.queryForList("set nocount on; select column_name,data_type from INFORMATION_SCHEMA.columns where table_name =?", new Object[]{tableName}); |
| | | return this.jdbcTemplate.queryForList(" set nocount on; select column_name,data_type from INFORMATION_SCHEMA.columns where table_name =? \n", new Object[]{tableName}); |
| | | |
| | | } |
| | | |
| | |
| | | **/ |
| | | public String getTypeLengthInfo(String tableName) { |
| | | // |
| | | return this.jdbcTemplate.queryForObject("set nocount on; declare @s varchar(max) SELECT @s=(isnull(@s+',','')+ column_name +'-'+ convert(varchar ,character_maximum_length) ) from INFORMATION_SCHEMA.columns where table_name =? and data_type in ('char','nchar','varchar','nvarchar') select @s as s", |
| | | return this.jdbcTemplate.queryForObject(" set nocount on; declare @s varchar(max) SELECT @s=(isnull(@s+',','')+ column_name +'-'+ convert(varchar(100) ,character_maximum_length) ) from INFORMATION_SCHEMA.columns where table_name =? and data_type in ('char','nchar','varchar','nvarchar') \n select @s as s \n", |
| | | String.class, new Object[]{tableName}); |
| | | |
| | | } |
| | |
| | | * 查找表中是否存在这个字段 |
| | | **/ |
| | | public int getColumnInfo(String tableName, String col) { |
| | | List list = this.jdbcTemplate.queryForList("set nocount on; select 1 from INFORMATION_SCHEMA.columns where table_name=? and column_name=? ", new Object[]{tableName, col}); |
| | | List list = this.jdbcTemplate.queryForList(" set nocount on; select 1 from INFORMATION_SCHEMA.columns where table_name=? and column_name=? \n", new Object[]{tableName, col}); |
| | | return list.size() > 0 ? 1 : 0; |
| | | } |
| | | |
| | | public String getAutoCode(int formid, int type, String field, int length, String perc) { |
| | | StringBuilder sb = new StringBuilder(); |
| | | sb.append("SET QUOTED_IDENTIFIER ON;\n" |
| | | + " set nocount on ; \n" |
| | | + " declare @curcode int ,@rntcode varchar(20) , @curFormid varchar(10), @curFormtype int ,@curFieldid varchar(50) ,@codelength int \n") |
| | | .append(" select @curformid = " + formid + " , @curformtype =" + type + " , @curFieldid ='" + field + "',@codelength = " + length + " \n") |
| | | .append(" begin Tran \n" |
| | | + " select @curcode = isnull(curcode,0) from _sysautocode with (updlock) \n" |
| | | + " where formid = @curFormid and formtype = @curFormtype and fieldid = @curFieldid \n") |
| | | .append(" select @curcode =convert(varchar , isnull(@curcode,0) + 1 ) \n") |
| | | .append(" select @rntcode = '" + perc + "' + replicate('0' , @codelength - len(convert(varchar,@curcode ) )) + convert(varchar,@curcode) \n") |
| | | .append(" if exists(select fieldid from _sysautocode where formid = @curFormid and formtype = @curFormtype and Fieldid =@curFieldid ) \n") |
| | | .append(" update _sysautocode set Curcode = @curcode where formid = @curFormid and formtype = @curFormtype and Fieldid =@curFieldid \n") |
| | | .append(" else \n" |
| | | + " insert into _sysautocode (Formid ,formtype,Fieldid,curcode) values(@curFormid, @curFormtype,@curFieldid,@curcode) \n") |
| | | .append(" if @@rowcount <> 0 \n" |
| | | + " begin select @rntcode commit Tran end \n" |
| | | + " else begin select '' rollback tran end \n"); |
| | | return this.jdbcTemplate.queryForObject(sb.toString(), String.class); |
| | | @Transactional |
| | | @Override |
| | | public String getAutoCode(int formid, int formType, String field) { |
| | | String sql =" set nocount on \n" + |
| | | " declare @FormId int = "+formid+",@FormType int = "+formType+",@FieldId nvarchar(100) = "+GridUtils.prossSqlParm(field)+"\n" + |
| | | " declare @CurCode varchar(50),@CurCodeStr varchar(50) \n" + |
| | | " declare @CodeLength int,@PreFixCode nvarchar(100),@PreCodeType int,@SplitChar nvarchar(100)\n" + |
| | | " declare @HDTable nvarchar(100) ,@sql nvarchar(2000) ,@ParmDefinition nvarchar(2000) , @FieldValue nvarchar(200) \n" + |
| | | " declare @TreeFormId int,@NodeId nvarchar(100)\n" + |
| | | " select @PreCodeType = PreCodeType,@PreFixCode = PreFixCode,@SplitChar=PreFixCode,@CodeLength = CodeLength\n" + |
| | | " from gform where FormId = @FormId \n" + |
| | | "\n" + |
| | | "\n" + |
| | | "--select @PreCodeType = 3 ,@PreFixCode = ''\n" + |
| | | " if isnull(@PreCodeType,0) = 2 \n" + |
| | | " begin\n" + |
| | | " set @PreFixCode = @FieldId \n" + |
| | | " end \n" + |
| | | " if isnull(@PreCodeType,0) = 3 \n" + |
| | | " begin\n" + |
| | | " select @TreeFormId = TreeFormId,@NodeId = NodeId from _systreeset \n" + |
| | | " where FormId = @FormId and FormType = @FormType\n" + |
| | | " if @@ROWCOUNT = 0 \n" + |
| | | " begin\n" + |
| | | " raiserror('请在9824功能号中设置树相关的参数',16,1)\n" + |
| | | " return\n" + |
| | | " end \n" + |
| | | " select @HDTable = HDTable from gform where formid = @TreeFormId \n" + |
| | | " if @@ROWCOUNT = 0 \n" + |
| | | " begin\n" + |
| | | " raiserror('相关的树功能号【%d】不存在',16,1,@TreeFormId)\n" + |
| | | " return\n" + |
| | | " end \n" + |
| | | " if isnull(@HDTable,'') = ''\n" + |
| | | " begin\n" + |
| | | " raiserror('相关的树功能号【%d】主表不能为空',16,1,@TreeFormId)\n" + |
| | | " return\n" + |
| | | " end \n" + |
| | | " select @sql = N' set @PreFixCode = '''' ;\n" + |
| | | " with temp ('+isnull(@NodeId,'') + ',RowId,ParentRowId) as \n" + |
| | | " (select '+isnull(@NodeId,'') + ',RowId,ParentRowId from '+isnull(@HDTable,'')+' where '+isnull(@NodeId,'') + ' = @FieldId\n" + |
| | | " union all select a.'+isnull(@NodeId,'') + ',a.RowId,a.ParentRowId from '+isnull(@HDTable,'')+' a \n" + |
| | | " inner join temp b on a.RowId = b.ParentRowId) -- = temp.RowId)\n" + |
| | | " select @PreFixCode = isnull(a.'+isnull(@NodeId,'') + ','''') + case when isnull(@PreFixCode,'''') <> '''' \n" + |
| | | " then isnull(@SplitChar,'''') else '''' end + isnull(@PreFixCode,'''') \n" + |
| | | " from temp a ; \n" + |
| | | " if isnull(@PreFixCode,'''') <> '''' set @PreFixCode = isnull(@PreFixCode,'''') + isnull(@SplitChar,'''') '\n" + |
| | | " set @ParmDefinition = N'@PreFixCode nvarchar(100) output,@SplitChar nvarchar(100),@FieldId nvarchar(100) '\n" + |
| | | " EXEC sp_executesql @sql , @ParmDefinition , @PreFixCode output , @SplitChar,@FieldId \n" + |
| | | "\n" + |
| | | "\t--set @SplitChar = @PreFixCode;\n" + |
| | | "\t--set @PreFixCode = '' ;\n" + |
| | | "\t--with temp (MatGroup,RowId,ParentRowId) as \n" + |
| | | "\t--(select MatGroup,RowId,ParentRowId from t110501 where MatGroup = @FieldId\n" + |
| | | "\t--union all select a.MatGroup,a.RowId,a.ParentRowId from t110501 a inner join temp b on a.RowId = b.ParentRowId) -- = temp.RowId)\n" + |
| | | "\t--select @PreFixCode = isnull(a.MatGroup,'') + case when isnull(@PreFixCode,'') <> '' \n" + |
| | | "\t--\tthen isnull(@SplitChar,'') else '' end + isnull(@PreFixCode,'') \n" + |
| | | "\t--from temp a ;\n" + |
| | | "\t--if isnull(@PreFixCode,'') <> '' set @PreFixCode = isnull(@PreFixCode,'') + isnull(@SplitChar,'')\n" + |
| | | " end \n" + |
| | | "--select @PreFixCode as PreFixCode\n" + |
| | | "\n" + |
| | | " exec p110205 @FormId=@FormId,\n" + |
| | | " @FormType= @FormType,\n" + |
| | | " @Fieldid =@FieldId,\n" + |
| | | " @PreFixCode = @PreFixCode,\n" + |
| | | " @CodeLength =@CodeLength,\n" + |
| | | " @CurCode = @CurCode output,\n" + |
| | | " @CurCodeStr = @CurCodeStr output\n" + |
| | | " select @CurCodeStr as CurCodeStr\n"; |
| | | return this.jdbcTemplate.queryForObject(sql, String.class); |
| | | } |
| | | |
| | | public String getAutoCode2(String rowid, int length, String percode, String perc, String table) { |
| | | StringBuilder sb = new StringBuilder(); |
| | | sb.append(" set nocount on; declare @curcode int ,@rntcode varchar(20) , @Treetable varchar(50) ,@rowid varchar(20),@codelength int ").append(" select @rowid ='" + rowid + "',@codelength = " + length + " ") |
| | | .append(" begin Tran select @curcode = isnull(curcode,0) from " + table + " with (updlock) where rowid = @rowid ").append(" select @curcode =convert(varchar , isnull(@curcode,0) + 1 ) ") |
| | | .append(" select @rntcode = '" + percode + "' + '" + perc + "' + replicate('0' , @codelength - len(convert(varchar,@curcode ) )) + convert(varchar,@curcode) ").append(" update " + table + " set Curcode = @curcode where rowid = @rowid ") |
| | | .append(" begin Tran select @curcode = isnull(curcode,0) from " + table + " with (updlock) where rowid = @rowid ").append(" select @curcode =convert(varchar(100) , isnull(@curcode,0) + 1 ) ") |
| | | .append(" select @rntcode = '" + percode + "' + '" + perc + "' + replicate('0' , @codelength - len(convert(varchar(100),@curcode ) )) + convert(varchar(100),@curcode) ").append(" update " + table + " set Curcode = @curcode where rowid = @rowid ") |
| | | .append("if @@rowcount <> 0 begin select @rntcode commit Tran end else begin select '' rollback tran end "); |
| | | return this.jdbcTemplate.queryForObject(sb.toString(), String.class); |
| | | } |
| | | |
| | | private List getCodeInfo(int formid) { |
| | | String sql = "set nocount on; select g.formid,g.codelength,g.preFixcode,g.precodetype,c.Formtype,c.Curcode,c.Fieldid from gform g,_sysautocode c where g.formid=c.formid and g.formid=?"; |
| | | String sql = " set nocount on; select g.formid,g.codelength,g.preFixcode,g.precodetype,c.Formtype,c.Curcode,c.Fieldid from gform g,_sysautocode c where g.formid=c.formid and g.formid=?"; |
| | | return this.jdbcTemplate.queryForList(sql, new Object[]{formid}); |
| | | } |
| | | |
| | | public String getPeriodID(int formid, String companyid, String docdate) { |
| | | String sql = "set nocount on; select dbo.GetPeriodID(" + formid + ",'" + companyid + "','" + docdate + "')"; |
| | | String sql = " set nocount on; select dbo.GetPeriodID(" + formid + ",'" + companyid + "','" + docdate + "')"; |
| | | return this.jdbcTemplate.queryForObject(sql, String.class); |
| | | } |
| | | |
| | |
| | | } |
| | | |
| | | public String getTreeTable(int formid) {//返回格式:树1的表名;树1功能号,树2表名;树2功能号 |
| | | String sql = "set nocount on; SELECT stuff((SELECT ',' + CONVERT(VARCHAR, g.HDTable)+';'+CONVERT(VARCHAR, g.formid) from _systreeset s,gform g \n" + |
| | | String sql = " set nocount on; SELECT stuff((SELECT ',' + CONVERT(VARCHAR(100), g.HDTable)+';'+CONVERT(VARCHAR(100), g.formid) from _systreeset s,gform g \n" + |
| | | " where s.formid=? and s.treeformid=g.formid FOR XML PATH ('')),1,1,'')"; |
| | | return this.jdbcTemplate.queryForObject(sql, String.class, new Object[]{formid}); |
| | | } |
| | |
| | | * 取得三表设置的相关联信息,9825 |
| | | */ |
| | | public List getThreeTableInfo(int formid) { |
| | | String sql = "set nocount on; select DetailFormID,MasterField,MasterKeys,DetailKeys,MasterSumFields,DetailSumFields,GridHeight from _sysmasterdetail where formid=? and FormID<>DetailFormID"; |
| | | String sql = " set nocount on; select DetailFormID,MasterField,MasterKeys,DetailKeys,MasterSumFields,DetailSumFields,GridHeight from _sysmasterdetail where formid=? ";// 为了新增对不是三表的也支持,去掉这个限制and FormID<>DetailFormID |
| | | return this.jdbcTemplate.queryForList(sql, new Object[]{formid}); |
| | | } |
| | | |
| | |
| | | * 取得功能号的过账类型号 |
| | | **/ |
| | | public int getTranID(int formid) { |
| | | int in = this.jdbcTemplate.queryForObject("set nocount on; declare @a int select @a=isnull(transgroupcode,0) from gform where formid=? select @a", Integer.class, new Object[]{formid}); |
| | | int in = this.jdbcTemplate.queryForObject(" set nocount on; declare @a int select @a=isnull(transgroupcode,0) from gform where formid=? select @a", Integer.class, new Object[]{formid}); |
| | | return in; |
| | | } |
| | | |
| | |
| | | * 取得7类型功能号的过滤条件 |
| | | **/ |
| | | public List<Map<String, Object>> getFilter7(int formid) throws DataAccessException { |
| | | String sql = "set nocount on; select * from _sysCtrlFieldGroupDetail" + " where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldGroup where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldFilter where Formid=?))" + " order by joingroupid asc"; |
| | | String sql = " set nocount on; select * from _sysCtrlFieldGroupDetail" + " where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldGroup where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldFilter where Formid=?))" + " order by joingroupid asc \n"; |
| | | return this.jdbcTemplate.queryForList(sql, formid); |
| | | } |
| | | |
| | |
| | | * 取得功能号的保存时执行及保存时执行存储过程组 |
| | | **/ |
| | | public Map<String, Object> getDealAfterDocSave(int formid) { |
| | | return this.jdbcTemplate.queryForMap("set nocount on; select DealAfterDocSave,ProcGroupafterSavedoc,CancelBtnProcName,RevokeBtnProcName,importProcName from gform where formid=?", formid); |
| | | return this.jdbcTemplate.queryForMap(" set nocount on; select DealAfterDocSave,ProcGroupafterSavedoc,CancelBtnProcName,RevokeBtnProcName,importProcName from gform where formid=? \n", formid); |
| | | } |
| | | |
| | | /** |
| | | * 取得功能号导入时保存时执行存储过程 |
| | | **/ |
| | | public String getImportExcelProc(int formid) { |
| | | return this.jdbcTemplate.queryForObject("set nocount on; select importProcName from gform where formid=?", String.class, new Object[]{formid}); |
| | | return this.jdbcTemplate.queryForObject(" set nocount on; select importProcName from gform where formid=?", String.class, new Object[]{formid}); |
| | | } |
| | | |
| | | /** |
| | |
| | | } |
| | | |
| | | /** |
| | | * 根据表名取得表主键名 |
| | | * 根据表名取得表主键名,查找表结构和9807设置 |
| | | * |
| | | * @return int |
| | | * @throws SQLException |
| | | */ |
| | | @Override |
| | | public List<String> getPrimaryKey(String tableName) throws DataAccessException, SQLException { |
| | | |
| | | Connection con = null; |
| | | ResultSet rs = null; |
| | | List<String> key = new ArrayList<String>(); |
| | | try { |
| | | con = this.jdbcTemplate.getDataSource().getConnection(); |
| | | rs = con.getMetaData().getPrimaryKeys(null, null, tableName); |
| | | while (rs.next()) { |
| | | key.add(rs.getString(4)); |
| | | } |
| | | } catch (Exception e) { |
| | | throw new ApplicationException(e.getMessage()); |
| | | } finally { |
| | | if (rs != null) |
| | | rs.close(); |
| | | if (con != null) |
| | | con.close(); |
| | | String sql=" declare @key varchar(1000),@tableName varchar(100)=?\n" + |
| | | " SELECT @key=stuff((SELECT';' + CONVERT(VARCHAR, column_name)\n" + |
| | | " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC\n" + |
| | | " INNER JOIN\n" + |
| | | " INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU\n" + |
| | | " ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND\n" + |
| | | " TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND \n" + |
| | | " KU.table_name=@tableName\n" + |
| | | " ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION\n" + |
| | | " FOR XML PATH ('')),1,1,'')\n" + |
| | | " if isnull(@key,'')=''\n" + |
| | | " begin\n" + |
| | | " select @key=keyfields from _systablekey where tableid=@tableName\n" + |
| | | " end\n" + |
| | | " select lower(@key) \n"; |
| | | final String result = this.getSimpleJdbcTemplate().queryForObject(sql, String.class, tableName); |
| | | if(result!=null){ |
| | | key=Arrays.asList(result.split(";")); |
| | | } |
| | | return key; |
| | | } |
| | |
| | | } |
| | | |
| | | @Override |
| | | public String getTableNameByType(SqlRowSet gform, String type) { |
| | | public String getTableNameByType(GformEntity gformEntity, String type) { |
| | | String[] temp = type.split("\\|"); |
| | | int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0; |
| | | String table = ""; |
| | | int winType = Integer.parseInt(temp[0]); |
| | | if (!gform.wasNull()) { |
| | | gform.first(); |
| | | FdFilters = gform.getString("formdatafilters"); |
| | | if (gformEntity!=null) { |
| | | FdFilters = gformEntity.getFormdatafilters(); |
| | | table = (winType == 0 || winType == 7 || winType == 1 || winType == 5 || winType == 2 || (winType == 10 && conNum == 1) || |
| | | (winType == 9 && conNum == 0) || winType == 3 || (winType == 4 && conNum == 0) || winType == 17 || |
| | | //(winType == 20 && conNum == 0)||--pc上 20,0是取从表,所以app需要调用winType == 20 & connum=1 |
| | |
| | | //(winType == 30&& conNum == 1)||--app直接调用connum=1就可以,PC端加载或保存时调用conNum=0,取到从表 |
| | | || (winType == 302 && conNum == 0) || winType == 19 || (winType == 499 && conNum == 0) || |
| | | (winType == 497 && conNum == 0) || (winType == 15 && (conNum == 0 || conNum == 2))) |
| | | ? gform.getString("hdtable") : gform.getString("dttable"); |
| | | ? gformEntity.getHdtable() : gformEntity.getDttable(); |
| | | |
| | | |
| | | } |
| | |
| | | |
| | | @Override |
| | | public Map<String,TableMetaData> getTableMetaData(String tableName) { |
| | | List<TableMetaData> list= this.getJdbcTemplate().query("set nocount on; select b.name as columnName, c.name as dataType ,b.iscomputed,b.length\n" + |
| | | List<TableMetaData> list= this.getJdbcTemplate().query(" set nocount on; select b.name as columnName, c.name as dataType ,b.iscomputed,b.length\n" + |
| | | " from sysobjects a \n" + |
| | | " join syscolumns b on a.id = b.id \n" + |
| | | " join systypes c on b.xtype=c.xtype and b.xtype = c.xusertype and c.status = 0 \n" + |
| | | " where a.NAME = ?\n" + |
| | | " and b.iscomputed = 0 --取非计算列\n" + |
| | | " and b.colstat = 0 --取非自增列 \n" + |
| | | " order by b.colorder",new BeanPropertyRowMapper<>(TableMetaData.class), tableName); |
| | | " order by b.colorder \n",new BeanPropertyRowMapper<>(TableMetaData.class), tableName); |
| | | |
| | | HashMap<String,TableMetaData> map=new HashMap<>(); |
| | | list.stream().forEach(x->{ |
| | |
| | | /** |
| | | * 根据功能号取得主从表名称 -----9801信息---增加新窗体类型都需要增加相应判断 |
| | | **/ |
| | | @Override |
| | | public String getTableName(int formid, String type) { |
| | | SqlRowSet gform = this.getGformByFormID(formid); |
| | | return getTableNameV2(gform, type); |
| | | } |
| | | @Override |
| | | public String getTableNameV2(SqlRowSet gform, String type) { |
| | | String[] temp = type.split("\\|"); |
| | | int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0; |
| | | String table = ""; |
| | |
| | | } |
| | | return table; |
| | | } |
| | | |
| | | /** |
| | | * 获得数据组内容,组装条件语句附加到需要查询的地方,形式:"and ccode=12345" |
| | | * |
| | | * @param flag 0 主表,1从表 |
| | | **/ |
| | | public List<Map<String, Object>> getDataGroupInfo(int formid, String usercode, int flag) { |
| | | String sql = "set nocount on; select d.fieldid,b.accessid,b.authobj,b.jionFlaggroup ,b.docitem,b.accessname,b.jionflag,b.conflag,b.modfvalues,b.modfvalues2,b.dspvalues,b.defaultvalue from _sysdataaccess_user u,_sysdataaccessauthobj b ,_sysdataauthobjdetail d " |
| | | + " where b.accessid=u.accessid and u.usercode=? and u.actived=1 " + "and b.authobj=d.authobj and d.formid=? and isnull(d.headflag,0)=? order by b.accessid,b.jionFlaggroup ,b.docitem"; |
| | | String sql = " set nocount on; select d.fieldid,b.accessid,b.authobj,b.jionFlaggroup ,b.docitem,b.accessname,b.jionflag,b.conflag,b.modfvalues,b.modfvalues2,b.dspvalues,b.defaultvalue from _sysdataaccess_user u,_sysdataaccessauthobj b ,_sysdataauthobjdetail d " |
| | | + " where b.accessid=u.accessid and u.usercode=? and u.actived=1 " + "and b.authobj=d.authobj and d.formid=? and isnull(d.headflag,0)=? order by b.accessid,b.jionFlaggroup ,b.docitem \n"; |
| | | return this.jdbcTemplate.queryForList(sql, new Object[]{usercode, formid, flag}); |
| | | |
| | | } |