eCoWorksV3.git - Gitblit

fs-danaus

2024-03-16 a2ecbf17d6ba1c7b135fe10bb4cdfefa05b75add

提交 \| 用户 \| age
a6a76f	1	package com.yc.service.grid;
F	2
	3	import com.yc.action.excel.Excel;
	4	import com.yc.action.execProc.ExecuteProcAction;
	5	import com.yc.action.grid.Grid;
	6	import com.yc.action.grid.GridUtils;
2b4227	7	import com.yc.action.grid.PicEntity;
a6a76f	8	import com.yc.action.grid.SqlInfo;
F	9	import com.yc.action.taobao.Taobao2ERP;
	10	import com.yc.action.upload.AttachmentAction;
a1f55f	11	import com.yc.app.v2.entity.GformEntity;
a6a76f	12	import com.yc.entity.TableColumnsDataTypeEntity;
F	13	import com.yc.exception.ApplicationException;
	14	import com.yc.factory.FactoryBean;
	15	import com.yc.open.utils.HttpClientUtil;
	16	import com.yc.sdk.password.action.ChangePassword;
	17	import com.yc.service.BaseService;
	18	import com.yc.service.new38type.Type38Ifc;
	19	import com.yc.service.upload.AttachmentIfc;
	20	import com.yc.utils.*;
795f4b	21	import org.apache.commons.lang.RandomStringUtils;
a6a76f	22	import org.apache.commons.lang.StringUtils;
F	23	import org.apache.http.client.methods.HttpGet;
	24	import org.apache.http.impl.client.CloseableHttpClient;
	25	import org.springframework.beans.factory.annotation.Autowired;
	26	import org.springframework.dao.DataAccessException;
	27	import org.springframework.jdbc.core.BeanPropertyRowMapper;
	28	import org.springframework.jdbc.core.CallableStatementCallback;
	29	import org.springframework.jdbc.core.SqlOutParameter;
	30	import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
	31	import org.springframework.jdbc.support.rowset.SqlRowSet;
	32	import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
	33	import org.springframework.stereotype.Service;
7edaee	34	import org.springframework.transaction.annotation.Transactional;
a6a76f	35
F	36	import javax.servlet.http.HttpServletRequest;
	37	import javax.servlet.http.HttpServletResponse;
	38	import javax.servlet.http.HttpSession;
	39	import java.io.UnsupportedEncodingException;
	40	import java.net.URLEncoder;
2e60c6	41	import java.sql.CallableStatement;
F	42	import java.sql.SQLException;
	43	import java.sql.Types;
a6a76f	44	import java.util.*;
F	45	import java.util.Map.Entry;
	46	import java.util.regex.Matcher;
	47	import java.util.regex.Pattern;
dc1d61	48	import java.util.stream.Collectors;
a6a76f	49
F	50	/**
	51	* gt-grid有关业务处理实现
	52	*
	53	* @author 邓文峰 2010-3-22
	54	*/
	55	@Service
	56	public class GridServiceImpl extends BaseService implements GridServiceIfc {
	57	@Autowired
	58	Type38Ifc type38Ifc;
a2ecbf	59	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 ";//
a6a76f	60
3959cc	61	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 ";
a6a76f	62
d3265b	63	private final String GET_GFORM = " set nocount on; select " + gform + " from gform where formid=? \n";
F	64	private final String GET_GFIELD = " set nocount on; select " + gfield + " from gfield where formid=? order by statisid asc\n";
	65	private final String GET_GFIELD9 = " set nocount on; select " + gfield + " from gfield where formid=? and HeadFlag=? order by statisid asc\n";// 9类型窗体
	66	private final static String GET_WINDOWTYPE = " set nocount on; select FormType from _sysMenu where formid=? \n";
	67	private final static String GET_FTDATA = " set nocount on; select interValue,dictvalue from _sysdict where dictid=? order by sequence asc\n";
a6a76f	68
F	69	// 保存时执行处理
d3265b	70	private final static String SAVE_PROC = " set nocount on; select [formid]\n" +
a6a76f	71	" ,[execSeq]\n" +
F	72	" ,[ProcName]\n" +
	73	" ,[action]\n" +
	74	" ,[objectType]\n" +
	75	" ,[memo]\n" +
d3265b	76	" ,[inactive] from _sys_FormProc where formid=? and isnull(inactive,0)=0 order by execseq asc\n";
a6a76f	77	// 删除前存储过程
d3265b	78	private final static String DEL_PROC = " set nocount on; select execProc from _sysDeleteDoc where formid=? order by seqn asc\n";
a6a76f	79	// 过帐类型
d3265b	80	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"
a6a76f	81	+ " 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"
F	82	+ " left join _sysTransPostCheck c on a.AfterSQLcheckid = c.CheckID \n" + " where a.transgroupcode=? and a.actived=1 order by a.sortid asc \n";
	83	// @Autowired
	84	// PanelServiceIfc panel;
	85	private String FdFilters = "";
	86	@Autowired
	87	Taobao2ERP taobao;
	88	@Autowired
	89	Excel excel;
	90	@Autowired
	91	private AttachmentIfc attachmentIfc;
	92
	93	///// *******************************************************
	94	// 以下部分是DAO操作函数
	95	//// ********************************************************
	96	public String getGET_GFORM() {
	97	return GET_GFORM;
	98	}
	99
	100	public String getGET_GFIELD() {
	101	return gfield;
	102	}
	103
	104	public String getGET_GFIELD9() {
	105	return gfield;
	106	}
1a8ee6	107	@Override
F	108	public String getExcludeTablesWhenSaving(int formid){
	109	return this.jdbcTemplate.queryForObject("select excludeTablesWhenSaving from gform where formid=?", String.class,formid);
	110	}
a6a76f	111	@Override
366271	112	public void doNewTran(Integer formid, String docCode,String userCode,String userName) {
F	113	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 " +
	114	" exec p" + formid + "Save @doccode\n" +
	115	" select @PostFormId = FormId,@DocStatusValue = DocStatus from t" + formid + "H where docCode=@docCode\n" +
	116	" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @PostFormId \n" +
	117	" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0)\n" +
	118	" begin \n" +
	119	" update a set DocStatus = @PostDocStatus,PostCode=@EnterCode,PostName=@EnterName,PostDate=getdate()\n" +
	120	" from t" + formid + "H a where a.DocCode = @docCode \n" +
	121	" exec p" + formid + "Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
	122	" @DocCode = @docCode,@FormId = @PostFormId,\n" +
	123	" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
	124	" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
	125	" end\n";
	126	this.jdbcTemplate.update(sql,docCode,userCode,userName);
	127	}
	128
	129	@Override
a6a76f	130	public SqlRowSet getGfiledByFormID(int formID) {
F	131	return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD, new Object[]{formID});
	132	}
	133
	134	public List getGfiledByFormID9(int formID, int flag) {
	135	// return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD9, new Object[]{formID,flag});
	136	return this.jdbcTemplate.queryForList(this.GET_GFIELD9, new Object[]{formID, flag});
	137	}
	138
	139	@Override
	140	public SqlRowSet getGformByFormID(int formID) {
	141	return this.jdbcTemplate.queryForRowSet(this.GET_GFORM, new Object[]{formID});
	142	}
	143
	144	private String procOrderBy(String order) {// 处理38类会存在group by的情况，所以统一在这里组装不同的情况
	145	// 多种可能
	146	// abc asc,bw desc
	147	// order by abc asc,de desc
	148	// group by aa,cc order by abc
	149	// order by abc group by aa,cc
	150	// group by aa,cc
	151	if ("".equalsIgnoreCase(order)) return "";
	152	if (order.toLowerCase().indexOf("order by") > -1) {// 表示有 order by
	153	return order;
	154	} else {
	155	if (order.toLowerCase().indexOf("group by") > -1) {// 存在有group by
	156	return order;
	157
	158	} else {// 需要加上order by
	159
	160	return " order by " + order;
	161	}
	162
	163	}
	164
	165	}
	166
	167	@Override
	168	public Page loadAll(Page page) {
	169	//处理复单时设置了排除指定功能号
	170	boolean isCp = false;
	171	if (page.getIsCp() == 1) {
	172	String rowcopyformids = null;
	173	String sql = "";
	174	if (page.getWinType().startsWith("15")) {//15类型
d3265b	175	sql = " set nocount on; select rowcopyformids from gform where formid=(select formid from _sysmasterdetail where DetailFormID=?) \n";
a6a76f	176	} else if (page.getWinType().startsWith("49")) {//多表
d3265b	177	sql = " set nocount on; select rowcopyformids from gform where formid=(select mainformid from _sys_TabPageFormid where formid=?) \n";
a6a76f	178	} else {
d3265b	179	sql = " set nocount on; select rowcopyformids from gform where formid=? \n";
a6a76f	180	}
F	181	try {
	182	rowcopyformids = this.getJdbcTemplate().queryForObject(sql, String.class, page.getFormid());
	183	} catch (Exception e) {
	184	}
	185	if (rowcopyformids != null) {
	186	String[] formids = rowcopyformids.split(";");
	187	for (String id : formids) {
	188	if (id.equals(page.getFormid() + "")) {
	189	isCp = true;
	190	break;
	191	}
	192	}
	193	}
	194	}
	195	if (page.getFlag() == 2 \|\| (page.isNull && page.getFlag() != 1) \|\| (page.getFlag() != 1 && page.getWhere().length() == 0) \|\| isCp) {
d3265b	196	page.setWhere(" 1=2 ");
a6a76f	197	}
F	198	if (page.getWhere().trim().indexOf("and") == 0) {
d3265b	199	page.setWhere(" 1=1 " + page.getWhere());
a6a76f	200	}
F	201	page.setTbCols(this.proccTbCols(page));
	202	//----生成10个数组处理参数内容过长，需要截断分组传
	203	String[] tbcols = new String[10];
	204	int len = page.getTbCols().length();
f14719	205	Map tbColsMap = new HashMap();
a6a76f	206	if (len > 0) {
F	207	for (int i = 0; i < 10; i++) {
	208	if (len >= (i + 1) * 3500) {
	209	tbcols[i] = page.getTbCols().substring(i * 3500, (i + 1) * 3500);
f14719	210	tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]);
a6a76f	211	} else {
F	212	tbcols[i] = page.getTbCols().substring(i * 3500, len); //endIndex只能是>=beginIndex
f14719	213	tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]);
a6a76f	214	break;
F	215	}
	216	}
	217	}
	218	//----end
	219	page.setSql(page.getSql().replaceAll("%2F", "/").replaceAll("%2B", "+"));
f14719	220	page.setTbColsMap(tbColsMap);
800b6a	221	Map<String, Object> map = null;
f14719	222	if ("SP_viewPageV4" .equals(page.getPROC_NAME())) {
F	223	map = this.simpleJdbcCall.withProcedureName("SP_viewPageV4")
	224	.declareParameters(// 定义存储过程参数返回值
	225	new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
	226	.execute(page.getTableName(), page.getSql(),
	227	page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
	228	page.getPageSize(), page.getPageNum(), page.getGroupby(),
	229	tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
	230	tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
	231	page.getFormid(), page.getUserCode(), page.getUserName(),
	232	0, 0, null);
	233	} else {
	234	//--19类型用SP_viewPageV3
	235	map = this.simpleJdbcCallByProc.withProcedureName("SP_viewPageV3")
	236	.declareParameters(// 定义存储过程参数返回值
	237	new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
	238	.execute(page.getTableName(), page.getSql(),
	239	page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
	240	page.getPageSize(), page.getPageNum(), page.getGroupby(),
	241	tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
	242	tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
	243	page.getFormid(), page.getUserCode(), page.getUserName(),
	244	0, 0, null);
a6a76f	245	}
f14719	246	page.setData((List) map.get("#result-set-1"));
F	247	page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount"));
	248	page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount"));
	249	page.setTbColsOut((String) map.get("TotalTbColsOut"));
1efd43	250	if (page.getTbColsOut() != null && !"".equals(page.getTbColsOut())) {//处理返回是0.的情况，在前端会显示NaN
f14719	251	String temp = page.getTbColsOut().replaceAll("\\.#", "#");
F	252	page.setTbColsOut(temp);
1efd43	253	}
F	254	//处理权限表达式为0时，需要加密输出
	255	if (page.getData() != null && page.getData().size() > 0) {
	256	page.getData().parallelStream().forEach(x -> {
	257	Map<String, Object> objectMap = (Map<String, Object>) x;
	258	for (Entry<String, Object> entry : objectMap.entrySet()) {
	259	if (objectMap.containsKey(entry.getKey() + "_expr") &&
	260	objectMap.get(entry.getKey() + "_expr").equals(0)) {
	261	try {
	262	if (entry.getValue() != null) {
	263	objectMap.put(entry.getKey(), ChangePassword.getEncryptPassword(entry.getValue().toString()));
	264	}
	265	} catch (Exception e) {
	266	throw new RuntimeException(e);
	267	}
	268	}
	269	}
	270	});
f14719	271	}
F	272	return page;
a6a76f	273	}
F	274
	275
	276	//2017-9-22复制过来定制页面调用
	277	@Override
	278	public Page loadAlls(Page page, String statisid, int formid) {
	279	String z = page.getWhere().toLowerCase().substring(page.getWhere().length() - 1, page.getWhere().length());
	280
	281	// convert.FromBase64String(page.getWhere());
	282
	283	if (page.getWhere() != null && (page.getWhere().length() / 4 == 0 \|\| "=".equals(z) && page.getWhere().length() > 50)) {
	284	String queryString = page.getWhere();
	285	queryString = queryString.replaceAll("%2B", "+");
	286	queryString = queryString.replaceAll("%2F", "/");
	287	//String t="";
	288	if (queryString.indexOf("?") != -1) {
	289	//t=queryString.substring(0,queryString.indexOf("?")+1);
	290	queryString = queryString.substring(queryString.indexOf("?"));
	291	}
	292	try {
	293	page.setWhere(EncodeUtil.base64Decode(queryString));
	294	} catch (Exception e) {
	295
	296	}
	297	}
	298	if (page.getWhere() != null && !"=".equals(z) && page.getWhere().length() < 50) {
	299	String[] q = page.getWhere().split("=");
	300	if (q.length >= 3 && "9822".equals(q[2])) {
d3265b	301	page.setWhere(" statisid=" + "'" + statisid + "'" + " and formid=" + formid);
a6a76f	302	}
F	303	}
	304
	305	//}
	306	if (page.getWhere().length() < 24) {
bbab7e	307	String[] where = page.getWhere().replace("(","").replace(")","").trim().split(";");
a6a76f	308
F	309	if (Integer.parseInt(where[0]) == 0) {
d3265b	310	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] + " "
a6a76f	311	+ "UNION ALL "
F	312	+ "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,"
	313	+ "displayformat,cellAlign,'' as sumfield,'' as funclinkname,'' as isFilterZero,'' as jionFlag,'' as jionFlagGroup,'' as conFlag,'' as modfvalues,'' as modfvalues2 "
	314	+ "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)"
d3265b	315	+ "order by displayYN DESC,Sequence asc,fieldcaption ASC \n";
a6a76f	316	page.setWhere(sql1);
F	317	}
	318	} else {
	319	//page.setWhere(page.getWhere()+"and displayYN=1");
	320	}
	321
	322	if (page.getWhere().length() > 100) {
	323	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(page.getWhere());
	324	for (int i = 0; i < list.size(); i++) {
	325	list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
	326	}
	327	page.setData(list);
	328	} else {
	329	if (page.getFlag() == 2 \|\| (page.isNull && page.getFlag() != 1) \|\| (page.getFlag() != 1 && page.getWhere().length() == 0))
	330	page.setWhere("1=2");
	331	if (page.getWhere().trim().indexOf("and") == 0) page.setWhere("1=1 " + page.getWhere());
	332
	333	page.setTbCols(this.proccTbCols(page));
	334	//----生成10个数组处理参数内容过长，需要截断分组传
	335	String[] tbcols = new String[10];
	336	int len = page.getTbCols().length();
	337	if (len > 0) {
	338	for (int i = 0; i < 10; i++) {
	339	if (len >= (i + 1) * 3500) {
	340	tbcols[i] = page.getTbCols().substring(i * 3500, (i + 1) * 3500);
	341	} else {
	342	tbcols[i] = page.getTbCols().substring(i * 3500, len); //endIndex只能是>=beginIndex
	343	break;
	344	}
	345	}
	346	}
	347	//----end
	348
	349	Map<String, Object> map = this.simpleJdbcCall.withProcedureName(page.getPROC_NAME())
	350	.declareParameters(// 定义存储过程参数返回值
	351	new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
	352	.execute(page.getTableName(), page.getSql(),
	353	page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
	354	page.getPageSize(), page.getPageNum(), page.getGroupby(),
	355	tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
	356	tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
	357	page.getFormid(), page.getUserCode(), page.getUserName(),
	358	0, 0, null);
	359
	360	List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("#result-set-1");
	361
	362	for (int i = 0; i < list.size(); i++) {
	363	list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
	364	}
	365
	366	/* for(int i =0;i<list.size();i++){
	367	if(list.get(i).get("displayyn").equals(1)){
	368	list1.add(list.get(i));
	369	}
	370	} */
	371
	372	page.setData(list);
	373
	374	page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount"));
	375	page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount"));
	376	page.setTbColsOut((String) map.get("TotalTbColsOut"));
	377	map = null;
	378	}
	379	return page;
	380	}
	381
	382	@Override
	383	public Page loaddata(Page page, int formid) {
d3265b	384	String sql = " set nocount on; select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias,"
a6a76f	385	+ "0 as displayYN,'' as StatisType,0 AS Sequence,'' as DisplayWidth,"
F	386	+ "displayformat,"
d3265b	387	+ "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";
a6a76f	388	List<Map<String, Object>> list = type38Ifc.girddata(sql);
F	389	/* for(int i =0;i<list.size();i++){
	390	list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
	391	}*/
	392	page.setData(list);
	393	page.setTotalRowNum(0);
	394	page.setTotalPageNum(0);
	395	page.setTbColsOut("0");
	396	list = null;
	397	return page;
	398	}
	399
	400	@Override
	401	public synchronized Page loadAllNoPage(Page page) {// 不分页
	402	if (page.getFlag() == 2 \|\| (page.isNull && page.getFlag() != 1) \|\| (page.getFlag() != 1 && page.getWhere().length() == 0))
	403	page.setWhere("1=2");
	404	if (page.getWhere().trim().indexOf("and") == 0) page.setWhere("1=1 " + page.getWhere());
	405	String sql = "select " + page.getSql() + " from " + page.getTableName() + ("".equalsIgnoreCase(page.getWhere()) ? "" : " where " + page.getWhere()) + ("".equalsIgnoreCase(page.getOrderBy()) ? "" : " order by " + page.getOrderBy());
	406	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
	407	page.setData(list);
	408	return page;
	409	}
	410
	411	@Override
	412	public List<TableColumnsDataTypeEntity> getTableColumnsDataTypes(int formid, int isDetailTable, String fieldids) {
d3265b	413	String sql = " set nocount on \n " +
a6a76f	414	" declare @table varchar(2000) ,@formid int =? , @isDetailTable int =?," +
F	415	" @fieldids varchar(max) =? \n" +
	416	" declare @formtype int ,@pos int =0\n" +
	417	" select @formtype = formtype from _sysmenu where formid = @formid \n" +
	418	" select @table = case when isnull(@isDetailTable,0) = 0 and @formtype " +
	419	" not in (18) THEN hdtable else dttable end from gform where formid = @formid\n" +
	420	" select @pos = CHARINDEX( '\|',isnull(@table,'') )\n" +
	421	" if @pos > 0 select @table = SUBSTRING(@table,0,@pos) \n" +
	422	" if isnull(@formtype,0) in (18,19)\n" +
	423	" begin\n" +
	424	" select a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_OCTET_LENGTH \n" +
	425	" from information_schema.ROUTINE_COLUMNS a \n" +
	426	" where a.TABLE_NAME = @table \n" +
	427	" and (isnull(@fieldids,'') = '' or a.COLUMN_NAME in (select list from getinstr(@fieldids)))\n" +
	428	" end \n" +
	429	" else \n" +
	430	" begin \n" +
	431	" select a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_OCTET_LENGTH \n" +
	432	" from INFORMATION_SCHEMA.COLUMNS a \n" +
	433	" where a.TABLE_NAME = @table \n" +
	434	" and (isnull(@fieldids,'') = '' or a.COLUMN_NAME in (select list from getinstr(@fieldids)))\n" +
d3265b	435	" end\n";
a6a76f	436
F	437	List<Map<String, Object>> list = this.getSimpleJdbcTemplate().queryForList(sql, formid, isDetailTable, fieldids);
	438	if (list != null) {
	439	List<TableColumnsDataTypeEntity> tableColumnsDataTypeEntities = new ArrayList<>();
	440	for (Map<String, Object> map : list) {
	441	TableColumnsDataTypeEntity tableColumnsDataTypeEntity = new TableColumnsDataTypeEntity();
	442	tableColumnsDataTypeEntity.setDataLength(GridUtils.prossRowSetDataType_Int(map, "CHARACTER_OCTET_LENGTH"));
	443	tableColumnsDataTypeEntity.setDataType(GridUtils.prossRowSetDataType_String(map, "DATA_TYPE"));
	444	tableColumnsDataTypeEntity.setFieldId(GridUtils.prossRowSetDataType_String(map, "COLUMN_NAME"));
	445	tableColumnsDataTypeEntities.add(tableColumnsDataTypeEntity);
	446	}
	447	return tableColumnsDataTypeEntities;
	448	}
	449	return null;
	450	}
	451
	452	@Override
	453	public SqlRowSetMetaData getMetaData(String tableName) {
d3265b	454	return this.jdbcTemplate.queryForRowSet(" set nocount on; select * from " + tableName + " where 1=2\n").getMetaData();
a6a76f	455	}
F	456
795f4b	457	private String getNewDocCode(Grid grid, HttpServletRequest request, String formid) {
a6a76f	458	String usercode;
F	459	if (request == null)// 不是通过页面提交，像手机或淘宝接口
	460	usercode = grid.getEnv().get(SessionKey.USERCODE);
	461	else usercode = (String) request.getSession().getAttribute(SessionKey.USERCODE);
795f4b	462	return " exec sp_newdoccode " + formid + ",'" + usercode + "',@newDoccode output\n ";
a6a76f	463	}
F	464
795f4b	465	private String getCurNewDocCode(String docPram) {
F	466	return " exec " + docPram.replaceAll("''$", "@newDoccode output\n ");
a6a76f	467	}
F	468
795f4b	469
a6a76f	470
F	471	@Override
795f4b	472	public SqlInfo doExecute(DoExecuteParameter parameterObject, HttpServletRequest request, HttpServletResponse response, List<PicEntity> picList, Grid grid, String formid, String docPram) throws DataAccessException {
a6a76f	473	String parm = parameterObject.getParm();
795f4b	474	String tempCode = parameterObject.getDoccode();// 临时值
a6a76f	475	StringBuffer sql = new StringBuffer();
F	476	SqlInfo info = new SqlInfo();
	477	String dbid = request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + "";
	478	if (grid.isDoccode()) {
795f4b	479	if ("".equalsIgnoreCase(tempCode) \|\| ("@newDoccode").equalsIgnoreCase(tempCode)) {
a6a76f	480	if (docPram != "" && docPram.length() > 0) {// 自定义单号
795f4b	481	sql.append(this.getCurNewDocCode(docPram));
F	482	} else{
	483	sql.append(this.getNewDocCode(grid, request, formid));
	484	}
a6a76f	485	} else {
795f4b	486	info.setDoccode(tempCode);
F	487	sql.append(" set @newDoccode='" + tempCode + "' \n");
a6a76f	488	}
F	489	}
	490	if (!"".equalsIgnoreCase(parameterObject.getStatus()) && !"".equalsIgnoreCase(parameterObject.getTableName())) {
	491	// 判断状态值
795f4b	492	sql.append(checkDocStatus(parameterObject, tempCode));
a6a76f	493	}
F	494
	495	if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getCanelProc())) {// 不需要保存而执行取消确认
	496	// TODO 取消确认功能如何标识需要到时处理
795f4b	497	// tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue);
F	498	sql.append(prossCanelProc(parameterObject, tempCode, 1,dbid));
	499	sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n");
a6a76f	500	info.setSql(sql.toString());
F	501	info.setCanel("canel#");
	502	return info;
	503	}
	504	if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getRevokeProc())) {// 执行撤回
	505	// TODO 取消确认功能如何标识需要到时处理
795f4b	506	// tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue);
F	507	sql.append(prossCanelProc(parameterObject, tempCode, 2, dbid));
	508	sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n");
a6a76f	509	info.setSql(sql.toString());
2bf739	510	info.setCanel("revoke#");//
a6a76f	511	return info;
F	512	}
	513	if (!parameterObject.isFl() && parameterObject.getFormid() != 9646 && parameterObject.getFormid() != 9654) {// 不需要保存而执行审核，通过
	514
2b4227	515	sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(parameterObject.getOaButtonProcParms()).append("\n").toString();
a6a76f	516	info.setSql(sql.toString());
F	517	return info;
	518
	519	}
125603	520
F	521	int in = parameterObject.getDetailSql().size();
	522	int up = parameterObject.getHeadSql().size();
a6a76f	523	int de = parameterObject.getDel().size();
F	524	int to = parameterObject.getTotalProc().size();
	525
	526	String[] list = new String[in + up + de];
125603	527	String[] toBeStored = parameterObject.getHeadSql().toArray(new String[in]);
F	528	String[] toBeStored1 = parameterObject.getDetailSql().toArray(new String[up]);
a6a76f	529	String[] toBeStored_p = parameterObject.getTotalProc().toArray(new String[to]);
F	530	String[] toBeStored2 = parameterObject.getDel().toArray(new String[de]);
1e0051	531	//由于增加了触发器更新版本号功能，所以执行顺序很重要，要按照先主表后从表的顺序组装sql，保证不会出现【当前单据内容已发生变化，请刷新页面然后重试此操作】提示
125603	532	// 1-------表头数据
F	533	if (up > 0) {
	534	System.arraycopy(toBeStored, 0, list, 0, up);
a6a76f	535
F	536	}
125603	537	// 2----------表体数据
F	538	if (in > 0) {
	539	System.arraycopy(toBeStored1, 0, list, up, in);
a6a76f	540	}
F	541	// 3----------删除数据
	542	if (de > 0) {
125603	543	System.arraycopy(toBeStored2, 0, list, up + in, de);
a6a76f	544	}
F	545	// 提交数据
125603	546	for (String str : list) {
a6a76f	547	sql.append(str).append(" \n");
125603	548	}
a6a76f	549	// 有删除图片的在这里执行
F	550	if (picList.size() > 0) {
0d82b7	551	StringBuilder delPicSql=new StringBuilder();
2b4227	552	for (PicEntity picEntity : picList) {
a6a76f	553	String uuid = null;
F	554	String[] splt = null;
2b4227	555	splt = picEntity.getContext().split(";");//分割出uuid和seq，seq可能存在多个的情况，需要处理这种情况,0A283B93-07ED-46B6-A66C-7223A71D94FE;9458;9567
F	556	picEntity.setIp(IPUtil.getIpAddr(request));
	557	picEntity.setUserCode( request.getSession().getAttribute(SessionKey.USERCODE) + "");
	558	picEntity.setUserName(request.getSession().getAttribute(SessionKey.USER_NAME) + "");
	559	picEntity.setType(0);
a6a76f	560	uuid = splt[0];
F	561	final CloseableHttpClient client = HttpClientUtil.getClient();
0d82b7	562	if(delPicSql.length()==0&& org.apache.commons.lang3.StringUtils.isNotBlank(uuid)){
F	563	//第一次且有附件才需要增加变量的定义
	564	delPicSql.append(" \n set nocount on \n " +
	565	" declare @unid_Attachment varchar(50),@seq_Attachment int \n"
	566	+ " declare @AcType_Attachment varchar(50) \n"
	567	+ " declare @usercode_Attachment varchar(50) \n"
	568	+ " declare @username_Attachment varchar(50) \n"
	569	+ " declare @ip_Attachment varchar(50) \n"
	570	+ " declare @mydelPicrowcount_Attachment int = 0 \n");
	571	}
a6a76f	572	for (int i = 1; i < splt.length; i++) {
F	573	//磁盘文件删除，通过httpclient调用提交给附件服务器系统
	574	try {
	575	Integer.parseInt(splt[i]);//不是数字，表示不是seq，跳过
	576	} catch (Exception e) {
	577	continue;
	578	}
	579	try {
	580
	581	StringBuilder url = new StringBuilder();
	582	url.append(AttachmentAction.domain)
	583	.append("/attachment/deleteAttachmentByGrid.do")
	584	.append("?formid=").append(formid)
	585	.append("&unid=").append(uuid)
	586	.append("&seq=").append(splt[i])
	587	.append("&dbid=").append(URLEncoder.encode(ChangePassword.getEncryptPassword(dbid), "utf-8"));
	588	final HttpGet httpGet = HttpClientUtil.CreateHttpGet(url.toString());
	589	client.execute(httpGet);
	590	} catch (Exception e) {
	591	e.printStackTrace();//避免删除文件出错影响主进程操作，不进行处理
	592	}
	593	//数据库内容删除，直接调用本地代码
2b4227	594	picEntity.setUuid(uuid);
F	595	picEntity.setSeq(Integer.parseInt(splt[i]));
0d82b7	596	delPicSql.append(attachmentIfc.getdeleteAttachmentSql(picEntity));
a6a76f	597	}
F	598	}
0d82b7	599	if(delPicSql.length()>0){
F	600	sql.append(delPicSql.toString());
	601	}
a6a76f	602	}
F	603	if ("".equals(excel.getSqlext())) {
	604	StringBuffer sqlext = new StringBuffer();
	605	for (int s = 0; s < list.length; s++) {
	606	sqlext.append(list[s]).append(",");
	607	}
	608	excel.setSqlext(sqlext.toString());
	609	}
342ba7	610	// 5---------9643执行保存时执行存储过程组
a6a76f	611	String p_str = "";//判断是否有相同的，只输出一个
F	612	if (toBeStored_p.length > 0) {
	613	for (String s : toBeStored_p) {
	614	if (!s.equalsIgnoreCase(p_str))
2b4227	615	sql.append(" \n "+s);
342ba7	616	p_str += s + ";";
F	617	}
	618	}
2b4377	619
F	620	// 6----
a6a76f	621	if (parameterObject.isFl()) {
2b4377	622	//下列的执行次序不能乱
F	623	// ---excel导入先执行后保存
a6a76f	624	if (!"".equalsIgnoreCase(parameterObject.getExcelProc()) \|\| parameterObject.getExcelProc().length() > 0) {
0c3502	625	if(parameterObject.getExcelProc().trim().toLowerCase().startsWith("exec")) {
F	626	//处理导入过程不带参数少了exec关键字
	627	sql.append(" \n ").append(parameterObject.getExcelProc()).append("\n");
	628	}else {
	629	sql.append(" \n exec ").append(parameterObject.getExcelProc()).append("\n");
	630	}
a6a76f	631	}
2b4377	632	// ----------9801保存时执行功能
F	633	if (!"".equalsIgnoreCase(parameterObject.getStr()) \|\| parameterObject.getStr().length() > 0) {
	634	if (!p_str.contains(parameterObject.getStr())) {//保存时执行存储过程组如果已存在，则去重
	635	sql.append(" \n exec " + parameterObject.getStr() + " " + parm).append("\n");
	636	}
	637	}
a6a76f	638	// ---[-审核，通过]前先保存再执行
7aeec6	639	if (!"".equalsIgnoreCase(parameterObject.getOaButtonProcName()) \|\| parameterObject.getOaButtonProcName().length() > 0) {
F	640	//---增加处理单据审核后跳转功能
	641	StringJoiner procParms=new StringJoiner(",");
	642	//从后面替换
	643	String[] parms=parameterObject.getOaButtonProcParms().split(",");
	644	for(int i=0;i<parms.length;i++){
	645	if(i==parms.length-3){
	646	//替换@ExcludeDocCodeWhenSelectNextDocument
	647	procParms.add("'"+parameterObject.getJson().getExcludeSelectNextDocument()+"'");
	648	}else if(i==parms.length-2){
	649	//替换@Memo
795f4b	650	procParms.add("@Memo output");
7aeec6	651	}else if(i==parms.length-1){
F	652	//替换@LinkDocInfo
795f4b	653	procParms.add("@LinkDocInfo output");
7aeec6	654	}else {
F	655	procParms.add(parms[i]);
	656	}
	657	}
a6a76f	658
7aeec6	659	sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(" ").append(procParms.toString()).append("\n");
F	660	}
a6a76f	661	// ---[-调用功能链接]前先保存再执行
7aeec6	662	if (!"".equalsIgnoreCase(parameterObject.getFunLinkProc()) && parameterObject.getFunLinkProc().length() > 0) {
a6a76f	663
F	664	sql.append(" \n exec ").append(parameterObject.getFunLinkProc()).append(" ").append(parameterObject.getFunLinkPram()).append("\n");
7aeec6	665	}
a6a76f	666	// ----淘宝接口
F	667	if (parameterObject.getJson().getTaobao() != null && parameterObject.getJson().getTaobao().size() > 0) {
	668	// taobao.prossERP(parameterObject.getJson(),request,response,false);
	669
	670	}
	671	// -------------
	672	}
5c4e49	673	if(request.getAttribute("isTran")!=null&&grid.isDoccode()){
F	674	//表示是确认操作
d3265b	675	sql.append(" \nupdate " + parameterObject.getTableName() + " set postCode='" +request.getSession().getAttribute(SessionKey.USERCODE)+"',postname='" + request.getSession().getAttribute(SessionKey.USERNAME) + "' where doccode=@newDoccode \n");
5c4e49	676	}
a6a76f	677	info.setSql(sql.toString());
F	678	return info;
	679	}
	680
	681	/**
	682	* 组装检查当前单号是否已确认
	683	*/
795f4b	684	private String checkDocStatus(DoExecuteParameter parameterObject, String temcode) {
a6a76f	685	if ("".equalsIgnoreCase(temcode)) return "";
F	686	StringBuffer sql = new StringBuffer();
	687
795f4b	688	sql.append(" select @docstatus=docstatus from " + parameterObject.getTableName() + " where doccode='" + temcode + "';\n ").append(" if @docstatus<>").append(parameterObject.getStatus()).append(" begin raiserror('")
a6a76f	689	.append(temcode + "-单据状态已变化,请刷新页面后再操作！',16,1); return end \n");
F	690	return sql.toString();
	691	}
	692
795f4b	693	private String prossCanelProc(DoExecuteParameter parameterObject, String tempCode, int type, String dbid) {// 处理取消确认,撤回功能
a6a76f	694	String proc = null;
795f4b	695	if (type == 1) {
F	696	proc = parameterObject.getCanelProc();
	697	}else{
	698	proc = parameterObject.getRevokeProc();
	699	}
a6a76f	700	if (!"".equalsIgnoreCase(proc) && proc.length() > 0) {
F	701	// 取得过程传的参数有哪些是输出参数，以便组装sql
	702	proc = proc.replaceAll("exec ", "").trim();
	703	String proName = proc.split("\\s+")[0];
	704	String parms = proc.split("\\s+")[1];
	705	String[] arrPams = parms.split(",");
	706	ExecuteProcAction exec = (ExecuteProcAction) FactoryBean.getBean("executeProcAction");
795f4b	707	String str = " exec checkPeriodHasOpen @doccode = '" + tempCode + "' , @formid = " + parameterObject.getFormid() + " ,@PeriodState = @PeriodState output\n ";
a6a76f	708	String returnValue = exec.spellProcCanel2(proName, arrPams, dbid);
795f4b	709	//去除变量定义，已统一在前面作了定义
F	710	Pattern p = Pattern.compile("exec.*");
	711	java.util.regex.Matcher propsMatcher = p.matcher(returnValue);
	712	while (propsMatcher.find()) {
	713	returnValue=propsMatcher.group();
	714	}
a6a76f	715	return str + " " + returnValue;
F	716	}
795f4b	717	return tempCode;
a6a76f	718	}
F	719
	720	/**
	721	* 取得功能链接中的过程名与参数
	722	**/
	723	@Override
	724	public List getFunLinkPro(String links) {
	725	String[] temp = links.split(";");
	726	String formid = "origformid";
	727	String type = "origformtype";
	728	if (temp.length == 4 && "taobao".equalsIgnoreCase(temp[3])) {
	729	formid = "linkformid";
	730	type = "linkformtype";
	731	}
	732	StringBuilder sql = new StringBuilder();
d3265b	733	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("))")
F	734	.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");
a6a76f	735	return this.jdbcTemplate.queryForList(sql.toString());
F	736	}
	737
	738	@Override
	739	public int getWindowTypeByGform(int formID) {
	740	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(GET_WINDOWTYPE, formID);
	741	if (list != null) {
	742	Map<String, Object> map = list.get(0);
	743	return GridUtils.prossRowSetDataType_Int(map, "FormType");
	744	} else {
	745	return 0;
	746	}
	747	}
	748
	749	@Override
	750	public SqlRowSet getFTData(int ft) {
	751	return this.jdbcTemplate.queryForRowSet(GET_FTDATA, new Object[]{ft});
	752	}
	753
	754	@Override
e2c4f0	755	public List getDataByAjaxBy42(String tabName, String fields, String where, int limit, int page,String orderField,int formid) throws DataAccessException {
F	756	boolean isDataType=false;
	757	HashMap<String, String> map9802 = new HashMap<>();
	758	if(tabName.split("\\\|").length==2) {
	759	//--取9802字段的数据类型
	760	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));
	761	query9802.stream().forEach(e -> map9802.put(e.getFieldID().toLowerCase(), e.getDataType()));
	762	isDataType=true;
	763	}
a6a76f	764	String where_str = null;//针对条件一部分是用作替换参数，@G@后面部分用作sql的where条件
F	765	if (where.trim().indexOf("@G@") == 0) {
	766	where = where.replaceAll("@G@", "");// 以\|分隔的第二部分是格线需要用的
	767	where_str = where;
	768	} else if (where.trim().indexOf("@G@") > 0) {//表示有参数值
	769	String[] whereSplit = where.trim().split("@G@");
	770	if (whereSplit.length > 1) {
	771	where_str = whereSplit[1];
	772	where = whereSplit[0];//参数值
	773	}
	774	} else {
	775	String[] temp_str = where.trim().split("@G@");
	776	if (temp_str.length > 1) {
	777	where_str = temp_str[1];
	778	}
	779	where = where.replaceAll("@G@", " and ");// 以\|分隔的第二部分是格线需要用的
	780
	781	}
b20b55	782	String newWhere=null;
a6a76f	783	if (tabName.indexOf("\|") > -1) {
F	784	String[] t = tabName.split("\\\|");
	785	String name = "dbo." + t[0];// 18类型
	786	String[] strs = where.split("and");
	787	StringBuilder str = new StringBuilder();
	788	int index = 0;
	789	String[] parmterArry = t[1].split(";");
	790	for (String toStr : parmterArry) {
	791	toStr = toStr.toLowerCase();
	792	boolean flag = false;
	793	if (index > 0) {
	794	str.append(",");
	795	}
	796	for (String parmter : strs) {
	797	parmter = parmter.toLowerCase();
	798	if (parmter.trim().indexOf(toStr) > -1) {
e2c4f0	799	String value=(parmter.indexOf("=") > -1 ? parmter.split("=") : parmter.split("like"))[1].replaceAll("%20", "").replaceAll("@~", "%");
F	800	if(value!=null&&value.trim().equals("'%%'")){
	801	value="''";
	802	}
	803	str.append(value);
a6a76f	804	flag = true;
F	805	break;
	806	}
	807	}
	808	if (!flag) {// 表示x没匹配的参数值，则为''
e2c4f0	809	if(isDataType) {
F	810	String dataType = map9802.get(toStr);
	811	if (dataType == null) {
	812	str.append(toStr);
	813	} else {
	814	if (dataType.equals("'"))
	815	str.append("''");
	816	else
	817	str.append("null");
	818	}
	819	}else {
	820	str.append("''");
	821	}
a6a76f	822	}
F	823	index++;
	824	}
b20b55	825	tabName=name + "( " + str.toString() + ")";
F	826	newWhere=" 1=1" + (where_str != null ?" and " + where_str.replaceAll("@~", "%") : "");
a6a76f	827	} else {
F	828	String resut = "";
	829	if (this.FdFilters != null && !"".equalsIgnoreCase(this.FdFilters)) {
	830
	831	resut += " and " + this.FdFilters;
	832	}
	833	if (!"".equalsIgnoreCase(where)) resut += " and " + where.replaceAll("@~", "%");
b20b55	834	newWhere="1=1 " + resut;
a6a76f	835	}
0b2fcb	836	String orgFields=fields;
10a3d7	837	//取42当前录入所在的字段作排序
757820	838	Pattern p = Pattern.compile("(?i)case\\s* (?i)when.?\\s\\b(?i)as\\b");// 匹配以case when.....as
F	839	java.util.regex.Matcher propsMatcher = p.matcher(orgFields);
	840	while (propsMatcher.find()) {
	841	orgFields=orgFields.replace(propsMatcher.group(),"");
	842	}
0b2fcb	843	fields=fields.replaceAll("','","'~p~'");
10a3d7	844	List<String> list =Arrays.asList(fields.split(","));
0b2fcb	845	fields=list.stream().distinct().collect(Collectors.joining(","));
F	846	fields=fields.replaceAll("'~p~'","','");
1691df	847	String orderby=fields.split(",")[0];
0b2fcb	848
d3265b	849	String newSql=" set nocount on \n" +
F	850	" declare @Limit int = ? , @Page int = ?,@StartRowNo int ,@EndRowNo int ;\n"
b20b55	851	+ " declare @TotalRowCount int ; \n"
F	852	+ " select @TotalRowCount = count(1) from "+tabName+" a where \n" +newWhere
	853	+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
	854	+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
0b2fcb	855	+ " SELECT "+orgFields+",TotalRowCount FROM ( \n"
b20b55	856	+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+orderby+") AS NO,@TotalRowCount as TotalRowCount, \n"
F	857	+ fields+" from "+tabName+" where \n" +newWhere
	858	+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
	859	return this.jdbcTemplate.queryForList(newSql,limit,page);
a6a76f	860	}
F	861
	862	@Override
e2c4f0	863	public List getDataByAjax(ThreeJSON json, String where) throws DataAccessException {
F	864	String tabName = this.getTableName(json.getFormID(), json.getType() + "\|" + 0);
	865	String fields=json.getParm().replaceAll(" ", " ").replaceAll("&", "").replaceAll("nbsp;", " ")
	866	.replaceAll("%20", " ").replaceAll(";", ",").replaceAll("\\b_ycid_\\b", "id");
	867	HashMap<String, String> map9802 = new HashMap<>();
	868	boolean isDataType=false;
	869	if(tabName.split("\\\|").length==2) {
	870	//--取9802字段的数据类型
	871	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));
	872	query9802.stream().forEach(e -> map9802.put(e.getFieldID().toLowerCase(), e.getDataType()));
	873	isDataType=true;
	874	}
a6a76f	875	String sql = "";
F	876	String where_str = null;//针对条件一部分是用作替换参数，@G@后面部分用作sql的where条件
	877	if (where.trim().indexOf("@G@") == 0) {
	878	where = where.replaceAll("@G@", "");// 以\|分隔的第二部分是格线需要用的
	879	where_str = where;
	880	} else {
	881	String[] temp_str = where.trim().split("@G@");
	882	if (temp_str.length > 1) {
	883	where_str = temp_str[1];
	884	}
	885	where = where.replaceAll("@G@", " and ");// 以\|分隔的第二部分是格线需要用的
	886
	887	}
	888	// "d=3 and c=4 \| d=3 and e=5"
	889
	890	if (tabName.indexOf("\|") > -1) {
	891	String[] t = tabName.split("\\\|");
	892	String name = "dbo." + t[0];// 18类型
	893	String[] strs = where.split("and");
	894	// String temp=t[1];
	895	StringBuilder str = new StringBuilder();
	896	// String w[] = null;
	897	// for (String s : strs) {
	898	// w = s.indexOf("=") > -1 ? s.split("=") : s.split("like");
	899	// }
	900	int index = 0;
	901	for (String parmter : t[1].split(";")) {
	902	parmter = parmter.toLowerCase();
	903	boolean flag = false;
	904	if (index > 0) {
	905	str.append(",");
	906	}
	907	for (String s : strs) {
	908	if (s.trim().indexOf(parmter) > -1) {
e2c4f0	909	String value=(s.indexOf("=") > -1 ? s.split("=") : s.split("like"))[1].replaceAll("@~", "%");
F	910	if(value!=null&&value.trim().equals("'%%'")){
	911	value="''";
	912	}
	913	str.append(value);
a6a76f	914	flag = true;
F	915	break;
	916	}
	917	}
	918	if (!flag) {// 表示x没匹配的参数值，则为''
e2c4f0	919	if(isDataType) {
F	920	String dataType = map9802.get(parmter);
	921	if (dataType == null) {
	922	str.append(parmter);
	923	} else {
	924	if (dataType.equals("'"))
	925	str.append("''");
	926	else
	927	str.append("null");
	928	}
	929	}else {
	930	str.append("''");
	931	}
a6a76f	932	}
F	933	index++;
	934	}
	935	sql = " select " + fields + " from " + name + "( " + str.toString() + ") where 1=1 " + (where_str != null ? " and " + where_str.replaceAll("%20", "").replaceAll("@~", "%") : "");
	936	} else {
	937	String resut = "";
	938	if (this.FdFilters != null && !"".equalsIgnoreCase(this.FdFilters)) {
	939
	940	resut += " and " + this.FdFilters;
	941	}
	942	if (!"".equalsIgnoreCase(where)) resut += " and " + where.replaceAll("@~", "%");
	943	sql = " select " + fields + " from " + tabName + " where 1=1 " + resut;
	944	}
	945	//增加排序功能
	946	return this.jdbcTemplate.queryForList(sql.replaceAll("\\^", " and "));
	947	}
11fbc9	948	private int proccesSymbol(String str){
F	949	if (org.apache.commons.lang3.StringUtils.isBlank(str)) {return -1;}
	950	boolean isSymo=str.indexOf("!")>-1?true:false;//存在!号才处理
	951	if(isSymo) {
	952	Pattern p = Pattern.compile("'.*?'+?");//取出'...'，判断存在!则替换，再找出真正18类型!的位置
	953	Matcher m = p.matcher(str);
	954	while (m.find()) {//存在
	955	if (m.group().indexOf("!") > -1) {
	956	str = str.replaceAll(m.group(), m.group().replaceAll("!", "#"));
	957	}
	958	}
	959	return str.indexOf("!");
	960	}else {
	961	return -1;
	962	}
	963	}
a6a76f	964	@SuppressWarnings("unchecked")
F	965	@Override
	966	public Page loadAllByFunc(Page page, Map<String, String> env, boolean flg) {
	967	String s1 = "";
	968	String s2 = "";
11fbc9	969	//处理参数值存在!号的情况，这样会出现可能会有多个!号，需要确定哪一个!号才是正确要处理
F	970
	971	int index = proccesSymbol(page.getWhere());
a6a76f	972	if (index > -1) {// 增加处理18类型有过滤条件的情况
F	973	String where_str = page.getWhere().substring(index + 1);
	974	if (where_str != "" && "1=2".equals(where_str.trim())) {
	975	return page;
	976	}
	977	s1 = page.getWhere().substring(0, index);
	978	s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@_asterisk_@", "*").replaceAll("@~", "%").replaceAll("!", " and ") + page.getDataGroup();// 增加数据组权限 12-06-11
c4c22c	979	//替换参数里有会话值的情况
F	980	s2 =prossFormdatafilters(s2,env);
a6a76f	981	} else {
F	982	s1 = page.getWhere();
	983	s2 = page.getDataGroup().trim().length() > 0 ? " where " + ((page.getDataGroup().trim().startsWith("and")) ? " 1=1 " + page.getDataGroup() : page.getDataGroup()) : "";
	984	}
	985	String temp = this.getFunctionParm(page.getParms(), s1, env);
	986	if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
	987	else temp = "()";
	988
	989	String tableName = page.getTableName();
	990	if (tableName.indexOf("dbo.") == -1 \|\| tableName.indexOf("DBO.") == -1) tableName = "dbo." + tableName;
	991	// if (flg) {// 为了取页数增加的处理
	992	// String sql = "select count(1) as p from " + tableName + temp + s2 + ((page.getOrderBy() == null \|\| page.getOrderBy().length() == 0) ? "" : (page.getOrderBy().toLowerCase().indexOf("order by") > -1) ? page.getOrderBy() : " order by " + page.getOrderBy());
	993	// List list = this.jdbcTemplate.queryForList(sql);
	994	// int num = (Integer) (((Map<String, Object>) list.get(0)).get("p"));
	995	// if (num <= page.getPageSize()) page.setTotalPageNum(1);
	996	// else page.setTotalPageNum((int) Math.ceil((double) num / page.getPageSize()));
	997	// return page;
	998	// } else {
	999	// 修正为18类型也可以只取指定的记录数,通过新的分页函数
	1000	page.setTableName(tableName + temp);
	1001	page.setWhere("".equalsIgnoreCase(s2.replace("where", "")) ? " 1=1 " : s2.replace("where", ""));
	1002	page.setOrderBy(((page.getOrderBy() == null \|\| page.getOrderBy().length() == 0) ? "" : (page.getOrderBy().toLowerCase().indexOf("order by") > -1) ? page.getOrderBy().replace("order by", "") : page.getOrderBy()));
	1003	page.setTbCols(this.proccTbCols(page));
	1004	page.setEnv(env);
	1005	return this.loadAll(page);
	1006	// String sql="select "+page.getSql()+" from "+tableName+temp+s2+((page.getOrderBy()==null\|\|page.getOrderBy().length()==0)?"":(page.getOrderBy().toLowerCase().indexOf("order by")>-1)?page.getOrderBy():" order by "+page.getOrderBy());
	1007	// List list=this.jdbcTemplate.queryForList(sql);
	1008	// return this.setPageInfo(list, page);
	1009	// }
	1010	}
c4c22c	1011	private String prossFormdatafilters(String filter, Map<String, String> env) {
F	1012	if ("".equals(filter)) return filter;
	1013	Pattern p = Pattern.compile("@.*?\\w+");
	1014	Matcher m = p.matcher(filter);
	1015	while (m.find()) {//存在
	1016	filter = filter.replaceAll(m.group(), env.get(m.group().toLowerCase()) + "");
	1017	}
	1018	return filter;
	1019	}
a6a76f	1020	public String proccTbCols(Page page) {
F	1021	// digit#1:null,totalmoney2#1:
	1022	// 组装统计成：cast(sum(isnull(digit,0)) as nvarchar) +''\|''+ cast(sum(isnull(totalmoney2,0)) as nvarchar)
	1023	String tb = page.getTbCols();
	1024	if (tb == null \|\| "".equalsIgnoreCase(tb)) return "";
	1025	if (org.apache.commons.codec.binary.Base64.isBase64(tb)) {
	1026	try {
	1027	tb = EncodeUtil.base64Decode(tb);
	1028	tb = tb.replaceAll("%2F", "/").replaceAll("%2B", "+");
	1029	} catch (UnsupportedEncodingException e) {
	1030	// tb = tb;
	1031	} // base64解密所有请求where参数
	1032	} else {//表示之前已解码，不需要再执行一次，直接返回，主要是用在18类型分页返回，会调用多一次做的处理
	1033	return tb.replaceAll("%2F", "/").replaceAll("%2B", "+");
	1034	}
	1035	StringBuffer sb = new StringBuffer();
	1036
	1037	String[] s1 = tb.split(",");
	1038	for (String s : s1) {
	1039	String[] s2 = s.split("#");// 分出每个统计的列
	1040	String[] s3 = s2[1].split(":");// 分离出统计统计和格式，1-汇总，2-计数，3-平均，4-百分比，5-自定义,6最大值,7最小值
	1041	// String format="";
	1042	// if(s3.length==2){//表示有格式
	1043	// if(s3[1]!=null&&"null".equalsIgnoreCase(s3[1])){
	1044	//
	1045	// }
	1046	// }
	1047	// cast(sum(isnull(digit,0)) as nvarchar)
	1048	String filed = s2[0];
	1049
	1050	if (page.getTbExpr() != null && page.getTbExpr().get(s2[0].toLowerCase()) != null
	1051	&& !"".equalsIgnoreCase(page.getTbExpr().get(s2[0].toLowerCase())))
	1052	filed = page.getTbExpr().get(s2[0].toLowerCase());
	1053	//filed=filed.replaceAll("\\/\\\\.?\\\\*/", "");
	1054	//replace(rtrim(replace(convert(varchar,sum(cast(isnull(wshamount,0) as decimal(38,18)) ) ),'0',' ')),' ','0')
	1055	switch (Integer.parseInt(s3[0])) {
	1056	case 1:
	1057	//sb.append(" cast(sum(isnull(").append(filed).append(",0)) as nvarchar )").append("+'#p#'+");
	1058	sb.append(" replace(rtrim(replace(convert(varchar(100),sum(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1059	break;
	1060	case 2:
	1061	sb.append(" cast(count(isnull(").append(filed).append(",0)) as nvarchar )").append("+'#p#'+");
	1062	//sb.append(" replace(rtrim(replace(convert(varchar,count(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1063	break;
	1064	case 3:
	1065	//sb.append(" cast(avg(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
	1066	sb.append(" replace(rtrim(replace(convert(varchar(100),avg(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1067	break;
	1068	case 6:
	1069	//sb.append(" cast(max(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
	1070	sb.append(" replace(rtrim(replace(convert(varchar(100),max(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1071	break;
	1072	case 7:
	1073	//sb.append(" cast(min(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
	1074	sb.append(" replace(rtrim(replace(convert(varchar(100),min(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1075	break;
	1076	case 4:
	1077	//sb.append(" cast((sum(isnull(").append(filed).append(",0))*100.00) as nvarchar)").append("+'#p#'+");
	1078	sb.append(" replace(rtrim(replace(convert(varchar(100),sum(cast(isnull(").append(filed).append(",0) as decimal(38,18)) )*100.00 ),'0',' ')),' ','0')").append("+'#p#'+");
	1079	break;
	1080	case 5:// 需要把后缀转成中缀传给sql查询 !format2 digit price * othermoney + //add 2018-4-14
	1081	if (s3[1] == null \|\| s3[1].length() == 0) throw new ApplicationException(filed + "-没有设置自定义公式");
	1082	String str = "";
	1083	String newString = s3[1].trim().replaceAll("@p@", ",");
	1084	if (newString.startsWith("!format")) {
	1085	str = newString.replaceAll("!\\w+?\\b", "");//去掉!format2取得digit price * othermoney +
	1086	str = PostfixExpression.midToPost(str.trim(), "sum");
	1087
	1088	} else if (newString.startsWith("case when")) {
	1089	str = newString;
	1090	} else {
	1091	throw new ApplicationException(filed + "-汇总自定义公式【" +newString + "】需要以【!format或case when】格式开始");
	1092	}
	1093	//处理被除数为0的情况
	1094	String divisor = "";
	1095	if (str.indexOf("/") > 0) {//有除数
	1096	Pattern p = Pattern.compile("\\/\\w[^\\+\\-\\\\/]+");
	1097	Matcher m = p.matcher(str);
	1098	while (m.find()) {
	1099	String value = m.group();
	1100	if (value.indexOf("(") >= 0) {//有括号"("表示需要取到")"为止
	1101	// 1种情况，括号刚好匹配，： / sum( isnull( totalmoney2, 0 )) else 0 end
	1102	//第2种情况，)多出来，是匹配其他地方的：/sum(isnull(digit,0)),4)))) else 0 end
	1103	//需要做判断
	1104	Pattern p2 = Pattern.compile("[\$\$]");
	1105	Matcher m2 = p2.matcher(value);
	1106	int leftIndex=0;//左括号次数
	1107	int rightIndex=0;//右括号次数
	1108	while (m2.find()) {
	1109	if("(".equals(m2.group())){
	1110	leftIndex++;
	1111	}else {
	1112	rightIndex++;
	1113	}
	1114
	1115	}
	1116	if(rightIndex>=leftIndex){
	1117	for(int i=0;i<leftIndex;i++){//把匹配的括号替换成#
	1118	value=value.replaceFirst("\\)","#");
	1119	}
	1120	}
	1121	value = value.substring(0, value.lastIndexOf("#")+1).replaceAll("#",")");//转回来括号形式
	1122	} else {//后面还有字符的情况，因为没括号的情况下第二空格就代表了结束
	1123	// ： / totalmoney2 else 0 end
	1124	String[] strings=value.split("\\s+");
	1125	if("/".equalsIgnoreCase(strings[0].trim())&&strings.length>1){
	1126	value=strings[1];
	1127	}else{
	1128	value=strings[0];
	1129	}
	1130	}
	1131	divisor += value.replace("/", "") + ",";
	1132	}
	1133	}
	1134	if (StringUtils.isNotBlank(divisor)) {
	1135	str = " case when 0 in(" + divisor.substring(0, divisor.length() - 1) + ") then 0 else " + str + " end ";
	1136	}
	1137	str = PostfixExpression.replaceDotByrecover(str);//还原运算符
	1138	sb.append(" replace(rtrim(replace(convert(varchar(100),cast(isnull(").append(str).append(",0) as decimal(38,18) ) ),'0',' ')),' ','0')").append("+'#p#'+");
	1139	break;
	1140	default:
	1141	break;
	1142	}
	1143
	1144	}
	1145
	1146	return sb.length() > 0 ? sb.toString().substring(0, sb.lastIndexOf("+'#p#'+")) : "";
	1147	}
	1148
	1149	//后缀转中缀
	1150	private String postfix_to_infix(String rpn) {
	1151	if (rpn == null \|\| "".equalsIgnoreCase(rpn)) return "";
	1152	List<String> expr = java.util.Arrays.asList(rpn.split(" "));
	1153	Stack<String> s = new Stack<String>();
	1154	for (String str : expr) {
	1155	// a number
	1156	if ("".equals(str)) continue;
	1157	if (!str.isEmpty() &&
	1158	(
	1159	!"+".equalsIgnoreCase(str) &&
	1160	!"-".equalsIgnoreCase(str) &&
	1161	!"*".equalsIgnoreCase(str) &&
	1162	!"/".equalsIgnoreCase(str)
	1163	)) {
	1164	s.push(str);
	1165	}
	1166	// an operator
	1167	else {
	1168	String second = s.peek();
	1169	s.pop();
	1170	String first = s.peek();
	1171	s.pop();
	1172	s.push(first + str + second);
	1173	}
	1174	}
	1175	return s.peek();
	1176	}
	1177
	1178	public Page loadAllByFunc_for(Page page) {
	1179	String s1 = "";
	1180	String s2 = "";
11fbc9	1181	int index = proccesSymbol(page.getWhere());
a6a76f	1182	if (index > -1) {// 增加处理18类型有过滤条件的情况
F	1183	s1 = page.getWhere().substring(0, index);
	1184	s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@~", "%") + page.getDataGroup();// 增加数据组权限 12-06-11
	1185
	1186	} else {
	1187	s1 = page.getWhere();
	1188	s2 = " where 1=1 " + page.getDataGroup();
	1189	}
1efd43	1190	HashMap map = new HashMap();//保存有权限表达式为0的字段
F	1191	String[] tempStr = s1.split(",");// 函数后面列出的参数，有可能包括数字，字符及参数名称
	1192	for (String str : tempStr) {
	1193	if (str.contains("==")) {
	1194	str = str.replace("==", "@E@");//把==替换成@E@,过后再还原回来
	1195	}
	1196	String[] result = str.split("=");
	1197	if (result.length > 1 && result[0].endsWith("_expr") && "0".equals(result[1])) {
	1198	map.put(result[0], 0);
	1199	}
	1200	}
	1201	String temp = this.getFunctionParm_for(s1, map);
a6a76f	1202	if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
F	1203	String tableName = page.getTableName();
	1204	if (tableName.indexOf("dbo.") == -1) tableName = "dbo." + tableName;
	1205
d3265b	1206	String sql = " select " + page.getSql() + " from " + tableName + temp + s2 + ((page.getOrderBy() == null \|\| page.getOrderBy().length() == 0) ? "" : " order by " + page.getOrderBy());
1efd43	1207	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
F	1208	//--重新加密权限表达式为0的内容
	1209	if (org.apache.commons.lang3.StringUtils.isNotBlank(page.getFieldsExprs())) {
	1210	String exprKey = "," + page.getFieldsExprs().replaceAll(";", ",") + ",";
	1211	for (Map<String, Object> map1 : list) {
	1212	for (Map.Entry<String, Object> entry : map1.entrySet()) {
	1213	if (exprKey.contains("," + entry.getKey() + ",")) {
	1214	try {
	1215	entry.setValue(ChangePassword.getEncryptPassword(entry.getValue().toString()));
	1216	} catch (Exception e) {
	1217	throw new RuntimeException(e);
	1218	}
	1219	}
	1220	}
	1221	}
	1222	}
a6a76f	1223	page.setData(list);
F	1224	return page;
	1225	}
	1226
	1227	@Override
	1228	public Page loadAllByPROC(Page page,Map<String, String> env, boolean flg) {
	1229	String s1 = "";
	1230	String s2 = "";
	1231	int index = page.getWhere().indexOf("!");
	1232	if (index > -1) {// 增加处理19类型有过滤条件的情况
	1233	String where_str = page.getWhere().substring(index + 1);
	1234	if (where_str != "" && "1=2".equals(where_str.trim())) {
	1235	return page;
	1236	}
	1237	s1 = page.getWhere().substring(0, index);
	1238	s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@_asterisk_@", "*").replaceAll("@~", "%").replaceAll("!", " and ") + page.getDataGroup();// 增加数据组权限 12-06-11
	1239	//处理可能存在编码了的内容,@_xxxx_@
	1240	} else {
	1241	s1 = page.getWhere();
	1242	s2 = page.getDataGroup().trim().length() > 0 ? " where " + ((page.getDataGroup().trim().startsWith("and")) ? " 1=1 " + page.getDataGroup() : page.getDataGroup()) : "";
	1243	}
	1244	String temp = this.getProcParameters( s1, env);
	1245	// if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
	1246	//else temp = "()";
	1247
	1248	String tableName = page.getTableName();
	1249	//if (tableName.indexOf("dbo.") == -1 \|\| tableName.indexOf("DBO.") == -1) tableName = "dbo." + tableName;
	1250	page.setTableName("exec "+tableName + " "+temp);
	1251	page.setWhere("".equalsIgnoreCase(s2.replace("where", "")) ? " 1=1 " : s2.replace("where", ""));
	1252	page.setOrderBy(((page.getOrderBy() == null \|\| page.getOrderBy().length() == 0) ? "" : (page.getOrderBy().toLowerCase().indexOf("order by") > -1) ? page.getOrderBy().replace("order by", "") : page.getOrderBy()));
	1253	page.setTbCols(this.proccTbCols(page));
	1254	page.setEnv(env);
	1255	return this.loadAll(page);
	1256
	1257
	1258	}
	1259
	1260	private MapSqlParameterSource getParameterSource(String str) {
	1261	MapSqlParameterSource par = new MapSqlParameterSource();
	1262	String[] temp = str.split("and");
	1263	for (String s : temp) {
	1264	String[] t = s.split("=");
	1265	par.addValue(t[0].trim(), t.length == 1 ? "" : t[1].replaceAll("'", ""));
	1266	}
	1267	return par;
	1268	}
	1269	private String getProcParameters(String str,Map<String, String> env) {
	1270	List<String> par = new ArrayList<>();
	1271	String[] temp = str.split("and");
	1272	for (String s : temp) {
	1273	String[] t = s.split("=");
	1274	//取参数值
1efd43	1275	String value = t.length == 1 ? "" : t[1];
F	1276	if (!value.matches("\\d") && !value.startsWith("'")) {
	1277	value = "'" + value + "'";
a6a76f	1278	}
1efd43	1279	par.add(value);
a6a76f	1280	}
1efd43	1281	return String.join(",", par);
a6a76f	1282	}
1efd43	1283
F	1284	private String getFunctionParm_for(String st, HashMap map) {
a6a76f	1285	if ("".equalsIgnoreCase(st)) return "";
1efd43	1286	StringJoiner sb = new StringJoiner(",");
a6a76f	1287	String[] temp = st.split(",");// 函数后面列出的参数，有可能包括数字，字符及参数名称
F	1288	for (String str : temp) {
1efd43	1289	if (str.contains("_expr")) {
F	1290	continue;
a6a76f	1291	}
1efd43	1292	if (str.contains("==")) {
F	1293	str = str.replace("==", "@E@");//处理密文中的==与键值对的=冲突，先把==替换成@E@,过后再还原回来
	1294	}
	1295	String[] result = str.split("=");
	1296	if (result.length == 2) {
	1297	//判断权限表达式为0
	1298	if (map.containsKey(result[0] + "_expr") && org.apache.commons.lang3.StringUtils.isNotBlank(result[1]) && !"null".equalsIgnoreCase(result[1])) {
	1299	//解密
	1300	try {
	1301	sb.add(ChangePassword.getDecryptPassword(EncodeUtil.replaceUrlChar(result[1].replace("@E@", "=="))));
	1302	} catch (Exception e) {
	1303	throw new RuntimeException(e);
	1304	}
	1305	} else {
	1306	sb.add(result[1]);
	1307	}
	1308	} else {
	1309	sb.add(str);
	1310	}
a6a76f	1311	}
F	1312	return sb.toString();
	1313	}
	1314
d5c2f7	1315	public String getFunctionParm(String st, String parms, Map<String, String> env) {
a6a76f	1316	parms = JOSNUtils.prossBase64(parms);
F	1317	if ("".equalsIgnoreCase(st)) return "";
	1318	if ("".equalsIgnoreCase(parms)) return "";
	1319	if (parms.lastIndexOf("!1=2") > 0) parms = parms.substring(0, parms.length() - 4);//解决在18类型设置不是自动打开时直接导出会出错
	1320	StringBuilder sb = new StringBuilder();
	1321	String[] parmsNames = st.split(";");// 函数后面列出的参数，有可能包括数字，字符及参数名称
	1322	int i = 0;
84518e	1323	String[] parmValues = parms.split("\\s+?and\\s+?");
a6a76f	1324	for (String parameterName : parmsNames) {
F	1325	if (parameterName.matches("\\d")) {// 只是数字
	1326	if (i != 0) sb.append(",");
	1327	sb.append(parameterName);
	1328	continue;
	1329	}
	1330	boolean isfound = false;
	1331	for (String value : parmValues) {
	1332	int index = 0;
	1333	//value : myendday='2019-12-12'
	1334	String leftValue = "";//myendday
	1335	String rightValue = "";//'2019-12-12'
	1336	try {
	1337	index = value.indexOf("=");
	1338	} catch (StringIndexOutOfBoundsException e) {
	1339	index = 0;
	1340	}
	1341	if (index >= 0) {
	1342	leftValue = value.substring(0, index);
	1343	}
9a0d32	1344	rightValue = value.substring(index + 1).trim();
a6a76f	1345	if (parameterName.trim().equalsIgnoreCase(leftValue.replaceAll("\\s", ""))) {//匹配到参数
F	1346	if (i != 0) sb.append(",");
9a0d32	1347	sb.append("'null'".equalsIgnoreCase(rightValue.replaceAll("\\s", "")) ? null : (!rightValue.trim().startsWith("'")\|\|"''".equalsIgnoreCase(rightValue))?rightValue:GridUtils.prossSqlParm(rightValue.substring(1,rightValue.length()-1)));
a6a76f	1348	i++;
F	1349	isfound = true;
	1350	break;
	1351	} else if (parameterName.indexOf("@") > -1) {// 需要从session取值 @usercode\|cltcode
	1352	if (parameterName.indexOf("=") > 0) {
	1353	String[] sts = parameterName.split("=");
	1354
	1355	if (sts != null && sts.length > 1 && sts[1].trim().equalsIgnoreCase(leftValue.replaceAll("\\s", ""))) {
	1356	if (i != 0) sb.append(",");
	1357	String vl = env.get(sts[0]);
	1358	if (vl == null)
	1359	sb.append("null");
	1360	else
9a0d32	1361	sb.append(GridUtils.prossSqlParm(vl));
a6a76f	1362	i++;
F	1363	isfound = true;
	1364	break;
	1365	}
	1366	} else {
	1367	if (i != 0) sb.append(",");
	1368	String vl = env.get(parameterName);
	1369	if (vl == null)
	1370	sb.append("null");
	1371	else
9a0d32	1372	sb.append(GridUtils.prossSqlParm(vl));
a6a76f	1373	i++;
F	1374	isfound = true;
	1375	break;
	1376	}
	1377	}
	1378	}
	1379	if (!isfound) {
	1380	if (i != 0) sb.append(",");
	1381	sb.append("'").append(parameterName).append("'");
	1382	i++;
	1383	}
	1384	}
	1385	return sb.toString();
	1386	}
	1387
	1388	@Override
	1389	public long getMaxID(String tableName, String doccode) {
	1390	if ("''".equalsIgnoreCase("''")) return 0L;
	1391	Long id = this.jdbcTemplate.queryForObject("select max(docitem) from " + tableName + " where " + doccode, Long.class);
	1392	if (id == null) return 0L;
	1393	else return id;
	1394	}
	1395
	1396
	1397	@Override
	1398	public String getRowID() {
	1399	String sql = "{call getXXXX(?)}";
	1400	Map<?, ?> map = (Map<?, ?>) this.jdbcTemplate.execute(sql, new CallableStatementCallback<Object>() {
	1401	public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
	1402	cs.registerOutParameter(1, Types.VARCHAR);// 输出参数
	1403	cs.execute();
	1404	Map<String, String> map = new HashMap<String, String>();
	1405	map.put("rowid", cs.getString(1));
	1406	return map;
	1407	}
	1408	});
	1409	return (String) map.get("rowid");
	1410
	1411	}
	1412
	1413	public List<Map<String, Object>> getSaveProcGroup(int formid) {
	1414	return this.jdbcTemplate.queryForList(SAVE_PROC, new Object[]{formid});
	1415	}
	1416
	1417	public List<String> getDelProcGroup(int groupid) {
9a69d2	1418	return this.jdbcTemplate.queryForList(DEL_PROC, String.class,groupid);
a6a76f	1419
F	1420	}
	1421
	1422	@Override
	1423	public List<TranBean> getTranList(int groupid) {
	1424	List<TranBean> list = new ArrayList<TranBean>();
	1425	SqlRowSet set = this.jdbcTemplate.queryForRowSet(TRANS_GROUP, new Object[]{groupid});
	1426	while (set.next()) {
	1427	TranBean tb = new TranBean();
	1428	tb.setReloaddata(set.getInt("reloaddata"));
	1429	tb.setDatatable(set.getString("datatable"));
	1430	tb.setFilterstring(set.getString("filterstring"));
	1431	tb.setUpdatesql(set.getString("updatesql"));
	1432	tb.setUpdatesql2(set.getString("updatesql2"));
	1433	tb.setBeforeSQlCheckView(set.getString("BeforeSQlCheckView"));
	1434	tb.setAfterSQlCheckView(set.getString("AfterSQlCheckView"));
	1435	tb.setBeforeSQlCheckRaiseMsg(set.getString("BeforeSQlCheckRaiseMsg"));
	1436	tb.setAfterSQlCheckRaiseMsg(set.getString("AfterSQlCheckRaiseMsg"));
	1437
	1438	list.add(tb);
	1439	}
	1440	return list;
	1441	}
	1442
	1443
	1444	/**
	1445	* 执行过账类型，业务逻辑功能
	1446	*
	1447	* @param tranid --业务号
	1448	**/
	1449	public String doTarnType(int tranid, String doccode, String tableName, int formid, String postCode, String postname, HttpSession session, List<SqlInfo> sqlInfo) throws DataAccessException {
	1450	List<String> sqllist = new ArrayList<String>();// 全部的sql语句
	1451
	1452	boolean isflag = false;// 标记是新单且是直接确认的情况,app使用
	1453	if ("".equalsIgnoreCase(doccode)) {
795f4b	1454	doccode = "@newDoccode";
a6a76f	1455	isflag = true;
795f4b	1456	} else{
F	1457	doccode = "'" + doccode + "'";
	1458	}
a6a76f	1459	//int n = 0;
F	1460	StringBuffer tran_sql = new StringBuffer();
	1461	for (SqlInfo sq : sqlInfo) {
	1462	tran_sql.append(sq.getSql());
795f4b	1463	tran_sql.append(updateDocStatus(doccode, tableName, formid, postCode, postname));
a6a76f	1464
F	1465	// 再执行业务逻辑
	1466	List<TranBean> list = this.getTranList(tranid);
	1467	for (TranBean tb : list) {
795f4b	1468	String index = RandomStringUtils.randomNumeric(6);//转为字符串，再转为数值 ,保证不会出现相同的数值，如："1"+"5"=15
a6a76f	1469	StringBuilder sb = new StringBuilder();
d3265b	1470	String st = "\n set nocount on; select * from " + tb.getDatatable() + " where 1=2 \n";
a6a76f	1471	SqlRowSet row = this.jdbcTemplate.queryForRowSet(st);
F	1472	// ------------组装成游标方式处理
	1473	// -----定义变量，从设置中的sql取得，且用元数据取得相关的数据类型
	1474	List<String> parms = new ArrayList<String>();// 保存需要查询时需要的列名
	1475	if(org.apache.commons.lang3.StringUtils.isBlank(tb.getUpdatesql())){
	1476	throw new ApplicationException("请在9859维护【检查SQL】参数设置");
	1477	}
	1478	parms = this.getParm(tb.getUpdatesql(), parms, row);
	1479	parms = this.getParm(tb.getUpdatesql2(), parms, row);
	1480	parms = this.getParm(tb.getBeforeSQlCheckView(), parms, row);
	1481	parms = this.getParm(tb.getBeforeSQlCheckRaiseMsg(), parms, row);
	1482	parms = this.getParm(tb.getAfterSQlCheckView(), parms, row);
	1483	parms = this.getParm(tb.getAfterSQlCheckRaiseMsg(), parms, row);
	1484
	1485	Map<String, String> map = new HashMap<String, String>();// 保存列变量
	1486	HashMap<String, String> sessionClone = (HashMap) GridUtils.getSessionAttributes(session, formid, doccode, false).clone();//clone一份出来，避免串数据
	1487	String[] updatesql = prossParm(row, tb.getUpdatesql(), parms, index, map, sessionClone, tb.getDatatable(), 0);
	1488	String[] updatesql2 = prossParm(row, tb.getUpdatesql2(), parms, index, map, sessionClone, tb.getDatatable(), 0);
	1489	String[] beforeView = prossParm(row, tb.getBeforeSQlCheckView(), parms, index, map, sessionClone, tb.getDatatable(), 0);
	1490	String[] beforeViewMsg = prossParm(row, tb.getBeforeSQlCheckRaiseMsg(), parms, index, map, sessionClone, tb.getDatatable(), 1);
	1491	String[] afterView = prossParm(row, tb.getAfterSQlCheckView(), parms, index, map, sessionClone, tb.getDatatable(), 0);
	1492	String[] afterViewMsg = prossParm(row, tb.getAfterSQlCheckRaiseMsg(), parms, index, map, sessionClone, tb.getDatatable(), 1);
	1493	// --定义变量
	1494	for (Entry<String, String> entry : map.entrySet()) {
	1495
	1496	sb.append("\n " + entry.getValue());
	1497	}
d3265b	1498	sb.append("\n declare mycurPostCur cursor for \n");
a6a76f	1499	// 提取设置中的字段名(basedigit,plantid,matcode),拼装下面这条sql
F	1500	sb.append("\n select " + this.format(updatesql[1]) + " from " + tb.getDatatable() + " where doccode=" + doccode + ((tb.getFilterstring() != null && tb.getFilterstring().length() > 0) ? " and " + tb.getFilterstring() : ""));
	1501
d3265b	1502	sb.append("\n open mycurPostCur \n");
F	1503	sb.append("\n fetch next from mycurPostCur into \n");
a6a76f	1504	sb.append(this.format(updatesql[2]));// 变量名
F	1505	sb.append("\n while @@FETCH_STATUS = 0 \n begin \n");
	1506
	1507	// -----------star
	1508	// 1------更新前检查条件
	1509	if (tb.getBeforeSQlCheckView() != null && !"".equalsIgnoreCase(tb.getBeforeSQlCheckView())) {
d3265b	1510	sb.append("\n if exists(" + beforeView[0] + ")\n");
a6a76f	1511	sb.append("\n begin ");
795f4b	1512	sb.append("\n select @ErrMsgs='【'+" + beforeViewMsg[0] + "+'】'");
F	1513	sb.append("\n close mycurPostCur");
	1514	sb.append("\n deallocate mycurPostCur");
	1515	sb.append("\n raiserror(@ErrMsgs,16,1) return ");
a6a76f	1516	sb.append("\n end \n ");
F	1517	}
	1518
	1519	// 2-------update sql,分二种情况(过程和sql)
	1520	String sql = updatesql[0];
	1521	boolean flg = false;
	1522	if (sql.startsWith("!")) {
	1523	sql = " \n exec " + sql.replace("!", "");
	1524	flg = true;
	1525	}
	1526	sb.append("\n begin \n" + sql)
d3265b	1527	.append("\n select @myrowcount= @@rowcount , @myerror = @@error \n")
795f4b	1528	.append("\n if @myerror <>0 \n")
a6a76f	1529	.append("\n begin ")
d3265b	1530	.append("\n select @ErrMsgs='" + updatesql[0].replaceAll("'", "''") + "【'+" + updatesql[3] + "+'】出错' \n");
a6a76f	1531	if (flg && !"".equalsIgnoreCase(updatesql2[0])) {////表示前一个是过程调用
F	1532	String s = updatesql2[0].replace("!", "");
	1533	if (s.trim().startsWith("raismyerror")) {//存在加上引号
	1534	String str = s.trim().replace("raismyerror", "");
	1535	s = s.replaceAll(str, " '" + str + "'");
	1536	}
	1537	sb.append("\n exec " + s);
	1538	} else {//为空则用raiserror输出
d3265b	1539	sb.append("\n close mycurPostCur \n");
F	1540	sb.append("\n deallocate mycurPostCur \n");
	1541	sb.append("\n raiserror(@ErrMsgs,16,1) return \n");
a6a76f	1542	}
d3265b	1543	sb.append("\n end \n");
a6a76f	1544	// 3-------update2 sql
F	1545	sql = updatesql2[0];
	1546	if (!"".equalsIgnoreCase(sql)) {
	1547	if (!flg && !"select 1".equalsIgnoreCase(sql.trim())) {//"select 1"不执行
	1548	if (sql.startsWith("!")) {
	1549	String s = updatesql2[0].replace("!", "");
	1550	if (s.trim().startsWith("raismyerror")) {//存在加上引号
	1551	String str = s.trim().replace("raismyerror", "");
	1552	s = s.replaceAll(str, " '" + str + "'");
	1553	}
d3265b	1554	sql = "\n exec " + s + " \n";
a6a76f	1555	}
795f4b	1556	sb.append("\n if @myrowcount <=0 \n begin \n")//上面sql执行的更新不成功，则继续执行下面，成功则跳过
a6a76f	1557	.append(sql)
d3265b	1558	.append("\n select @myrowcount = @@rowcount , @myerror = @@error \n");
a6a76f	1559	if (!updatesql2[0].startsWith("!")) {
795f4b	1560	sb.append("\n if @myerror <>0 \n")
d3265b	1561	.append("\n begin \n")
F	1562	.append("\n select @ErrMsgs='" + updatesql2[0].replaceAll("'", "''") + "【'+" + updatesql2[3] + "+'】出错' \n")
	1563	.append("\n close mycurPostCur \n")
	1564	.append("\n deallocate mycurPostCur \n")
	1565	.append("\n raiserror(@ErrMsgs,16,1) return \n")
	1566	.append("\n end \n");
a6a76f	1567	}
d3265b	1568	sb.append("\n end \n");
a6a76f	1569	}
F	1570	}
d3265b	1571	sb.append(" \n end \n");
a6a76f	1572
F	1573	// 4------更新后检查条件
	1574	if (tb.getAfterSQlCheckView() != null && !"".equalsIgnoreCase(tb.getAfterSQlCheckView())) {
	1575	sb.append("\n if exists(" + afterView[0] + ")")
d3265b	1576	.append("\n begin \n")
795f4b	1577	.append("\n select @ErrMsgs='【'+" + afterViewMsg[0] + "+'】'")
d3265b	1578	.append("\n close mycurPostCur \n");
F	1579	sb.append("\n deallocate mycurPostCur \n" );
	1580	sb.append("\n raiserror(@ErrMsgs,16,1) return \n")
	1581	.append("\n end \n");
a6a76f	1582	}
F	1583	// ------------end
795f4b	1584	sb.append("\n fetch next from mycurPostCur into \n");
a6a76f	1585	sb.append(this.format(updatesql[2]));
d3265b	1586	sb.append("\n end \n");
F	1587	sb.append("\n close mycurPostCur \n");
	1588	sb.append("\n deallocate mycurPostCur \n");
795f4b	1589	if (sb.length() > 0){
F	1590	sqllist.add(sb.toString());
	1591	}
a6a76f	1592	}
F	1593	for (String str : sqllist) {
	1594	tran_sql.append(str);
	1595	}
	1596	}
	1597	if (tran_sql.toString().length() > 0) {
	1598	if (isflag) {
795f4b	1599	String str = "\n insert into @returnTable (docCode,rowid,detailRowid,Memo,LinkDocInfo) values(isnull(@newDoccode,''),'','','','')\n" +
F	1600	" select docCode,rowid,detailRowid,Memo,LinkDocInfo from @returnTable \n";//设置返回值
a6a76f	1601	Map map = this.doSave(tran_sql.toString() + str);
F	1602	return GridUtils.prossRowSetDataType_String(map, "docCode");
	1603	} else {
	1604	this.doSaveNoResult(tran_sql.toString());// 最终提交
	1605	return "";
	1606	}
	1607	}
	1608	return "";
	1609	}
	1610
	1611	/**
	1612	* 去掉最后的逗号
	1613	*/
	1614	private String format(String string) {
	1615	return (string.lastIndexOf(",") == string.length() - 1) ? string.substring(0, string.length() - 1) : string;
	1616	}
	1617
	1618	/**
	1619	* 取得不重复的参数
	1620	**/
	1621	@Override
	1622	public List<String> getParm(String sql, List<String> temp, SqlRowSet row) {
	1623	List<String> sq2 = new ArrayList<String>();
	1624	if (sql == null \|\| "".equalsIgnoreCase(sql)) return temp;// 直接返回
	1625	if (sql.startsWith("!")) {
	1626	List<String> pro = this.getProcString(sql);
	1627	for (int i = 1; i < pro.size(); i++) {
	1628	boolean fg = pro.get(i).matches("'.*?'");// 存在''号的情况
	1629	boolean fgno = pro.get(i).matches("'\\s?'");// ''的情况
	1630	String fid = pro.get(i);
	1631	if (fg && !fgno) {
	1632	fid = fid.substring(1, fid.length() - 1);// 取引号里面的值
	1633	}
	1634	String[] names = row.getMetaData().getColumnNames();// 取出表定义的所有列名
	1635	boolean flg = false;
	1636	for (String s : names) {
	1637	if (s.equalsIgnoreCase(fid)) {
	1638	flg = true;
	1639	break;
	1640	}
	1641
	1642	}
	1643	// 当前表所有的列名称，需要查找当前的字段名是否存在，不存在则不需要定义列变量输出
	1644	if (flg) {
	1645	sq2.add(fid);
	1646	}
	1647	}
	1648	} else {
	1649	sq2 = this.getString(sql);
	1650	}
	1651	if (temp.size() > 0) {// 不是第一次调用
	1652	temp.removeAll(sq2);
	1653	sq2.addAll(temp);
	1654	}
	1655	return sq2;
	1656	// ---
	1657	}
	1658
	1659	@Override
795f4b	1660	public String[] prossParm(SqlRowSet row, String temp, List<String> sq, String index, Map<String, String> map, Map<String, String> sessionClone, String tableName, int type) {
a6a76f	1661	String colName = "";// 保存列名称
F	1662	String columVar = "";// 保存列变量
	1663	StringBuilder errorMessage = new StringBuilder();// 保存出错时需要显示的内容，需要在这里组装变量
	1664	boolean flg = true;//ture表示匹配不了结尾的情况，需要加上单引号
	1665	if (temp == null \|\| "".equalsIgnoreCase(temp)) return new String[]{"", "", "", ""};
	1666	temp = temp.toLowerCase().replaceAll("<br>", "");
	1667	int indexd = 0;
	1668	//List<String> colsParm=new ArrayList<>();
	1669	for (String filedName : sq) {
	1670	//替换&...&, @直接取会话值
	1671	String typename = this.getTypeName(row.getMetaData(), filedName.replaceAll("&", ""));
	1672	if (typename == null) {
	1673	throw new ApplicationException(temp + "里面的列【" + filedName + "】在" + tableName + "表中不存在【" + filedName.replaceAll("&", "") + "】");
	1674	}
	1675	if (!map.containsKey(filedName.replaceAll("&", ""))) {// 生成公共的列名，变量
	1676	map.put(filedName.replaceAll("&", ""), "\n declare @" + filedName.replaceAll("&", "") + "_" + index + " " + typename);// 生成
	1677	colName += filedName.replaceAll("&", "") + ",";
	1678	columVar += "@" + filedName.replaceAll("&", "") + "_" + index + ",";
	1679	}
	1680
	1681	if (temp.startsWith("!")) {//存储过程
	1682	//!sp_matdocvaluation doccode,rowid,matcode,batchcode,plantid,stcode,basedigit,netmoney,itemtype,2,periodid
	1683	List<String> pro = getProcString(temp);//pro[0] 是名称
	1684
	1685	temp = temp.replaceAll(";", ",");
	1686	for (int i = 1; i < pro.size(); i++) {//取会话值
	1687	try {
	1688	String name = pro.get(i).replaceAll("'", "");//去除单引号
	1689	if (name.indexOf("@") >= 0) {
	1690	temp = temp.replaceAll(name.toLowerCase(), sessionClone.get(name) + "");
	1691	if (errorMessage.length() == 0) {
	1692	errorMessage.append("'").append(sessionClone.get(name.toLowerCase())).append("'+");
	1693	} else {
	1694	errorMessage.append("',").append(sessionClone.get(name.toLowerCase())).append("'+");
	1695	}
	1696	}
	1697
	1698	} catch (Exception e) {
	1699	throw new ApplicationException(e.getMessage());
	1700	}
	1701	}
	1702	//替换每个参数值
	1703	if ((indexd + 1) == sq.size()) {//到结尾
	1704	if (temp.indexOf(",") < 0 && temp.endsWith(filedName)) {//只有一个参数且是sq.size也是等于1的情况
	1705	temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用【】代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1706	} else {//最后
	1707	if(org.apache.commons.lang3.StringUtils.contains(temp,","+filedName.trim())){
	1708	temp = temp.replace("," + filedName, ",【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1709	}else if(org.apache.commons.lang3.StringUtils.contains(temp,filedName.trim()+",")){
	1710	temp = temp.replace( filedName+",", "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index+",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1711	}else{
	1712	temp = temp.replace( filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1713	}
	1714	}
	1715	} else if (indexd == 0) {//首部
	1716	if (temp.indexOf(",") < 0 && temp.endsWith(filedName)) {//第一个就结束替换，且sq.size不等于1的情况
	1717	temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1718	} else {
	1719	temp = temp.replace(filedName + ",", "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + ",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1720	}
	1721	} else {//中间
	1722	temp = temp.replace("," + filedName + ",", ",【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + ",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
	1723	}
	1724	} else {//普通sql
	1725
	1726	if (type == 1) {//处理检查条件的sql语句
	1727	if (filedName.indexOf("&") == 0) {//普通sql只需要替换有&..&的参数
	1728
	1729	if (temp.indexOf("'" + filedName + "'") > 0) {//表示有单引号
	1730	filedName = "'" + filedName + "'";
	1731	}
	1732	if ((indexd + 1) == sq.size()) {//到结尾
	1733	if (temp.trim().endsWith(filedName)) {//在结尾
	1734	temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))");
	1735	flg = false;
	1736	} else {
	1737	temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'") + "'";
	1738	}
	1739	} else if (indexd == 0) {//首部
	1740	if (temp.trim().startsWith(filedName))//在开头
	1741	temp = temp.replace(filedName, "cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
	1742	else {
	1743	temp = "'" + temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
	1744	}
	1745	} else {
	1746	temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
	1747	}
	1748
	1749
	1750	}
	1751	} else {
	1752	if (filedName.indexOf("&") == 0) {//普通sql只需要替换有&..&的参数
	1753	if (temp.indexOf("'" + filedName + "'") > 0) {//表示有单引号
	1754	filedName = "'" + filedName + "'";
	1755	}
	1756	temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);
	1757	}
	1758	}
	1759
	1760	}
	1761	// 出错信息
	1762	if (errorMessage.length() == 0) {
	1763	errorMessage.append("'" + filedName.replaceAll("'", "").replaceAll("&", "") + "='+cast(@").append(filedName.replaceAll("'", "").replaceAll("&", "")).append("_").append(index).append(" as nvarchar(500))+");
	1764	} else {//','
	1765	errorMessage.append("','+" + "'" + filedName.replaceAll("'", "").replaceAll("&", "") + "='+cast(@").append(filedName.replaceAll("'", "").replaceAll("&", "")).append("_").append(index).append(" as nvarchar(500))+");
	1766	}
	1767	indexd++;
	1768	}
	1769	if (type == 1 && flg) {
	1770	if (!temp.endsWith("'"))
	1771	temp = temp + "'";
	1772	}
	1773	temp = temp.replaceAll("【】", "@");
	1774	return new String[]{temp, colName, columVar, errorMessage.toString()};
	1775	}
	1776
	1777	/**
	1778	* 根据列名查找列的数据类型是否需要去掉双引号，因为客户端传过来的json经过转换都加上双引号，但到写入数据库时需要区分数据类型 <br>
	1779	* 1 --表示数值型<br>
	1780	* 2---表示text,ntext,image之类<br>
	1781	* 3---字符 4---日期
	1782	*/
	1783	private int getType(SqlRowSetMetaData md, String id) {
	1784	int s = 3;
	1785	for (int i = 1; i <= md.getColumnCount(); i++) {
	1786	if (id.equalsIgnoreCase(md.getColumnName(i))) {
	1787	s = md.getColumnType(i);
	1788	break;
	1789	}
	1790	}
	1791	switch (s) {
	1792	case Types.TINYINT:
	1793	case Types.INTEGER:
	1794	case Types.DECIMAL:
	1795	case Types.DOUBLE:
	1796	case Types.FLOAT:
	1797	case Types.NUMERIC:
	1798	case Types.BIGINT:
	1799	case Types.REAL:
	1800	case Types.SMALLINT:
	1801	case 0:
	1802	return 1;
	1803	case Types.BINARY:
	1804	case Types.BLOB:
	1805	case Types.CLOB:
	1806	case Types.NCLOB:
	1807	return 2;
	1808	case Types.DATE:
	1809	case Types.TIME:
	1810	case Types.TIMESTAMP:
	1811	return 4;// 日期
	1812	case Types.BIT:
	1813	return 5;
	1814	case Types.LONGNVARCHAR:// varchar(MAX)
	1815	case Types.LONGVARCHAR:
	1816	return 3;
	1817	default:
	1818	return 3;
	1819	}
	1820	}
	1821
	1822	/**
	1823	* 取得列的数据类型定义
	1824	*/
	1825	private String getTypeName(SqlRowSetMetaData md, String id) {
	1826	// int s = 3;
	1827	// int size=0;
	1828	String name = null;
	1829	for (int i = 1; i <= md.getColumnCount(); i++) {
	1830	if (id.equalsIgnoreCase(md.getColumnName(i))) {
	1831	if ("varchar".equalsIgnoreCase(md.getColumnTypeName(i)) \|\| "nvarchar".equalsIgnoreCase(md.getColumnTypeName(i)) \|\| "char".equalsIgnoreCase(md.getColumnTypeName(i)) \|\| "nchar".equalsIgnoreCase(md.getColumnTypeName(i)))
	1832	name = md.getColumnTypeName(i) + "(" + md.getColumnDisplaySize(i) + ")";
	1833	else name = md.getColumnTypeName(i);
	1834	}
	1835	}
	1836	return name;
	1837	// switch (s) {
	1838	// case Types.TINYINT:
	1839	// return "tinyint";
	1840	// case Types.INTEGER:
	1841	// return "int";
	1842	// case Types.DECIMAL:
	1843	// return "money";
	1844	// case Types.DOUBLE:
	1845	// return "double";
	1846	// case Types.FLOAT:
	1847	// return "float";
	1848	// case Types.NUMERIC:
	1849	// return "numeric";
	1850	// case Types.BIGINT:
	1851	// return "bigint";
	1852	// case Types.REAL:
	1853	// return "real";
	1854	// case Types.SMALLINT:
	1855	// return "smallint";
	1856	// case Types.BINARY:
	1857	// return "smallint";
	1858	// case Types.BLOB:
	1859	// return "smallint";
	1860	// case Types.CLOB:
	1861	// return "smallint";
	1862	// case Types.NCLOB:
	1863	// return "smallint";
	1864	// case Types.CHAR:
	1865	// return "char";
	1866	// case Types.DATE:
	1867	// return "date";
	1868	// case Types.TIME:
	1869	// return "time";
	1870	// case Types.TIMESTAMP:
	1871	// return "datetime";
	1872	//
	1873	// case Types.BIT:
	1874	// return "bit";
	1875	// case Types.LONGNVARCHAR://varchar(MAX)
	1876	// return "nvarchar(max)";
	1877	// case Types.LONGVARCHAR:
	1878	// return "varchar(max)";
	1879	// case Types.VARCHAR:
	1880	// return "varchar";
	1881	// case Types.NVARCHAR:
	1882	// return "nvarchar";
	1883	// default:
	1884	// return "varchar";
	1885	// }
	1886	}
	1887
	1888	public Map doSave(String sql) throws DataAccessException {
	1889
	1890	return this.jdbcTemplate.queryForMap(sql);
	1891	}
	1892
	1893	public void doSaveNoResult(String sql) throws DataAccessException {
	1894
74ae12	1895	//this.jdbcTemplate.execute(sql);
F	1896	//execute执行有时候会出现出错不会回滚，造成事务失效，所以换成下面这种方式
	1897	this.doBaseExecute(sql);
a6a76f	1898	}
F	1899
795f4b	1900	private String updateDocStatus(String doccode, String tabname, int formid, String postCode, String postname) {
a6a76f	1901	// 修改单据为确认后状态值
d3265b	1902	String sql = "\n select @blclosed=blclosed,@refcode=refcode from " + tabname + " where doccode=" + doccode + " \n"
795f4b	1903	+ " set @myrowcount = @@rowcount \n"
F	1904	+ " set @myerror = @@error \n"
	1905	+ " if @myrowcount = 0 begin raiserror('%s单号不存在',16,1," + doccode + ") return end\n" +
	1906	" if @blclosed=-1 begin " + " if isnull(@refcode,'')='' begin raiserror('冲销单的引用单号(refcode)为空',16,1) return end \n"
	1907	+ " update " + tabname + " set cleardoccode=" + doccode + " ,blclosed=1 where doccode=@refcode \n" +
a6a76f	1908	// " set @flg_"+num+"=-1\n"+
795f4b	1909	" end\n" +
F	1910	" select @predocstatus=predocstatus,@postdocstatus=postdocstatus from gform where formid=" + formid + "\n"
	1911	+ " set @myrowcount = @@rowcount \n"
	1912	+ " set @myerror = @@error \n"
	1913	+ " if @myrowcount = 0 begin raiserror('%s 功能号不存在',16,1," + formid + ") return end \n" +
a6a76f	1914
9f3dc3	1915	" update " + tabname + " set postdate=getdate(),postCode='" + postCode + "',postname='" + postname + "' \n"
795f4b	1916	+ " where doccode=" + doccode + " and DocStatus=@predocstatus\n"
F	1917	+ " set @myrowcount = @@rowcount \n"
	1918	+ " set @myerror = @@error \n"
9f3dc3	1919	+ " if @myrowcount = 0 begin raiserror('%s-更新出错！',16,1," + doccode + ") return end\n "
a6a76f	1920
9f3dc3	1921	+" if not exists(select 1 from t111630 where DocCode = '"+formid+"') \n"
F	1922	+ " begin \n"
	1923	+" update a set DocStatus = @PostDocStatus from "+tabname+" a where a.DocCode = "+doccode+" and a.DocStatus <> isnull(@PostDocStatus,0) \n"
	1924	+" end \n";
a6a76f	1925	// 期间是否已关闭，关闭则不能再确认
d3265b	1926	sql += "\n exec checkPeriodHasOpen @doccode=" + doccode + ", @formid = " + formid + " ,@PeriodState = @PeriodState output \n"
795f4b	1927	+ " if @PeriodState = 2 begin raiserror('当前单据%s所属的期间已关闭，请与财务人员联系',16,1," + doccode + ") return end \n"
F	1928	+ " if @PeriodState = 0 begin raiserror('当前单据%s所属的期间未打开，请与财务人员联系',16,1," + doccode + ") return end \n";
a6a76f	1929	return sql;
F	1930	}
795f4b	1931	@Override
a6a76f	1932	public String replaceStr2(String s, String count) {
F	1933	if (s == null \|\| s.length() == 0) return s;
	1934	String kkt = " exec ";
	1935	String ss = "";
	1936	if (s.trim().indexOf("!") == 0) {
	1937	ss = s.trim().substring(1, s.length());
	1938	String[] temp = ss.trim().split("\\s+");
	1939	if (temp.length > 1 && !temp[1].trim().startsWith("'")) {
	1940	temp[1] = "'" + temp[1] + "'";
	1941	}
795f4b	1942	return "\n if @myrowcount <= 0 \n begin " + kkt + temp[0] + " " + temp[1] + " \n return end \n";
a6a76f	1943	} else {
F	1944	ss = s;
	1945	kkt = "";
795f4b	1946	return "\n if @myrowcount<= 0 \n begin " + ss + " \n return end \n";
a6a76f	1947	}
F	1948
	1949	}
	1950
	1951	/**
	1952	* 查找字符串中是否存在指定的内容返回需要替换的字段名
	1953	*/
	1954	@Override
	1955	public List<String> getString(String str) {
	1956	Pattern p = Pattern.compile("&\\w*&");
	1957	List<String> list = new ArrayList<String>();
	1958	java.util.regex.Matcher propsMatcher = p.matcher(str != null ? str.toLowerCase() : "");
	1959	while (propsMatcher.find()) {
	1960	list.add(propsMatcher.group());
	1961	}
	1962	return list;
	1963	}
	1964
	1965	/**
	1966	* 查找以!开头的存储过程中的名字和参数 !upd_SalesOrderBalance doccode;formid 返回upd_SalesOrderBalance 和doccode;formid
	1967	*/
	1968	public List<String> getProcString(String str) {
	1969	List<String> list = new ArrayList<String>();
	1970
	1971	String[] t = str.replaceAll("!", "").trim().split("\\s{1,}");
	1972	String[] t1 = t[1].split(";");
	1973	list.add(t[0]);
	1974	for (String s : t1)
	1975	list.add(s.trim().toLowerCase());
	1976	return list;
	1977	}
	1978
	1979	/**
	1980	* 取得表中所有计算列字段
	1981	**/
	1982	public List<String> getComputedColumns(String tableName) {
98c7bf	1983	String sql = "set nocount on; select column_name from information_schema.columns where " + " table_name=? and (columnproperty(object_id(?),column_name,'IsIdentity')=1" + " or columnproperty(object_id(?),column_name,'IsComputed')=1)";
J	1984	List<String> list = this.jdbcTemplate.queryForList(sql, String.class, new Object[]{tableName, tableName, tableName});
a6a76f	1985	list.add("Deleted");
F	1986	list.add("Added");
	1987	list.add("Changed");
	1988	list.add("Moved");
	1989	list.add("Prev");
	1990	list.add("Next");
	1991	list.add("Panel");
	1992	list.add("Parent");
	1993	list.add("G");// 甘特图
	1994	return list;
	1995	}
	1996
	1997	/**
	1998	* 取得表中所有计算列字段
	1999	**/
	2000	public List getColumnsTypeInfo(String tableName) {
d3265b	2001	return this.jdbcTemplate.queryForList(" set nocount on; select column_name,data_type from INFORMATION_SCHEMA.columns where table_name =? \n", new Object[]{tableName});
a6a76f	2002
F	2003	}
	2004
	2005	@Override
	2006	/**
	2007	*
	2008	* 取得表中列字段的长度(char,nchar,varchar,nvarchar)
	2009	*
	2010	**/
	2011	public String getTypeLengthInfo(String tableName) {
	2012	//
d3265b	2013	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') \n select @s as s \n",
98c7bf	2014	String.class, new Object[]{tableName});
a6a76f	2015
F	2016	}
	2017
	2018	/**
	2019	* 查找表中是否存在这个字段
	2020	**/
	2021	public int getColumnInfo(String tableName, String col) {
d3265b	2022	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});
a6a76f	2023	return list.size() > 0 ? 1 : 0;
F	2024	}
7edaee	2025	@Transactional
F	2026	@Override
	2027	public String getAutoCode(int formid, int formType, String field) {
d3265b	2028	String sql =" set nocount on \n" +
F	2029	" declare @FormId int = "+formid+",@FormType int = "+formType+",@FieldId nvarchar(100) = "+GridUtils.prossSqlParm(field)+"\n" +
	2030	" declare @CurCode varchar(50),@CurCodeStr varchar(50) \n" +
	2031	" declare @CodeLength int,@PreFixCode nvarchar(100),@PreCodeType int,@SplitChar nvarchar(100)\n" +
	2032	" declare @HDTable nvarchar(100) ,@sql nvarchar(2000) ,@ParmDefinition nvarchar(2000) , @FieldValue nvarchar(200) \n" +
	2033	" declare @TreeFormId int,@NodeId nvarchar(100)\n" +
	2034	" select @PreCodeType = PreCodeType,@PreFixCode = PreFixCode,@SplitChar=PreFixCode,@CodeLength = CodeLength\n" +
	2035	" from gform where FormId = @FormId \n" +
7edaee	2036	"\n" +
F	2037	"\n" +
	2038	"--select @PreCodeType = 3 ,@PreFixCode = ''\n" +
d3265b	2039	" if isnull(@PreCodeType,0) = 2 \n" +
F	2040	" begin\n" +
	2041	" set @PreFixCode = @FieldId \n" +
	2042	" end \n" +
	2043	" if isnull(@PreCodeType,0) = 3 \n" +
	2044	" begin\n" +
	2045	" select @TreeFormId = TreeFormId,@NodeId = NodeId from _systreeset \n" +
	2046	" where FormId = @FormId and FormType = @FormType\n" +
	2047	" if @@ROWCOUNT = 0 \n" +
	2048	" begin\n" +
	2049	" raiserror('请在9824功能号中设置树相关的参数',16,1)\n" +
	2050	" return\n" +
	2051	" end \n" +
	2052	" select @HDTable = HDTable from gform where formid = @TreeFormId \n" +
	2053	" if @@ROWCOUNT = 0 \n" +
	2054	" begin\n" +
	2055	" raiserror('相关的树功能号【%d】不存在',16,1,@TreeFormId)\n" +
	2056	" return\n" +
	2057	" end \n" +
	2058	" if isnull(@HDTable,'') = ''\n" +
	2059	" begin\n" +
	2060	" raiserror('相关的树功能号【%d】主表不能为空',16,1,@TreeFormId)\n" +
	2061	" return\n" +
	2062	" end \n" +
	2063	" select @sql = N' set @PreFixCode = '''' ;\n" +
	2064	" with temp ('+isnull(@NodeId,'') + ',RowId,ParentRowId) as \n" +
	2065	" (select '+isnull(@NodeId,'') + ',RowId,ParentRowId from '+isnull(@HDTable,'')+' where '+isnull(@NodeId,'') + ' = @FieldId\n" +
	2066	" union all select a.'+isnull(@NodeId,'') + ',a.RowId,a.ParentRowId from '+isnull(@HDTable,'')+' a \n" +
	2067	" inner join temp b on a.RowId = b.ParentRowId) -- = temp.RowId)\n" +
	2068	" select @PreFixCode = isnull(a.'+isnull(@NodeId,'') + ','''') + case when isnull(@PreFixCode,'''') <> '''' \n" +
	2069	" then isnull(@SplitChar,'''') else '''' end + isnull(@PreFixCode,'''') \n" +
	2070	" from temp a ; \n" +
	2071	" if isnull(@PreFixCode,'''') <> '''' set @PreFixCode = isnull(@PreFixCode,'''') + isnull(@SplitChar,'''') '\n" +
	2072	" set @ParmDefinition = N'@PreFixCode nvarchar(100) output,@SplitChar nvarchar(100),@FieldId nvarchar(100) '\n" +
	2073	" EXEC sp_executesql @sql , @ParmDefinition , @PreFixCode output , @SplitChar,@FieldId \n" +
7edaee	2074	"\n" +
F	2075	"\t--set @SplitChar = @PreFixCode;\n" +
	2076	"\t--set @PreFixCode = '' ;\n" +
	2077	"\t--with temp (MatGroup,RowId,ParentRowId) as \n" +
	2078	"\t--(select MatGroup,RowId,ParentRowId from t110501 where MatGroup = @FieldId\n" +
	2079	"\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" +
	2080	"\t--select @PreFixCode = isnull(a.MatGroup,'') + case when isnull(@PreFixCode,'') <> '' \n" +
	2081	"\t--\tthen isnull(@SplitChar,'') else '' end + isnull(@PreFixCode,'') \n" +
	2082	"\t--from temp a ;\n" +
	2083	"\t--if isnull(@PreFixCode,'') <> '' set @PreFixCode = isnull(@PreFixCode,'') + isnull(@SplitChar,'')\n" +
d3265b	2084	" end \n" +
7edaee	2085	"--select @PreFixCode as PreFixCode\n" +
F	2086	"\n" +
d3265b	2087	" exec p110205 @FormId=@FormId,\n" +
F	2088	" @FormType= @FormType,\n" +
	2089	" @Fieldid =@FieldId,\n" +
	2090	" @PreFixCode = @PreFixCode,\n" +
	2091	" @CodeLength =@CodeLength,\n" +
	2092	" @CurCode = @CurCode output,\n" +
	2093	" @CurCodeStr = @CurCodeStr output\n" +
	2094	" select @CurCodeStr as CurCodeStr\n";
7edaee	2095	return this.jdbcTemplate.queryForObject(sql, String.class);
a6a76f	2096	}
F	2097
	2098	public String getAutoCode2(String rowid, int length, String percode, String perc, String table) {
	2099	StringBuilder sb = new StringBuilder();
98c7bf	2100	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 + " ")
a6a76f	2101	.append(" begin Tran select @curcode = isnull(curcode,0) from " + table + " with (updlock) where rowid = @rowid ").append(" select @curcode =convert(varchar , isnull(@curcode,0) + 1 ) ")
F	2102	.append(" select @rntcode = '" + percode + "' + '" + perc + "' + replicate('0' , @codelength - len(convert(varchar,@curcode ) )) + convert(varchar,@curcode) ").append(" update " + table + " set Curcode = @curcode where rowid = @rowid ")
	2103	.append("if @@rowcount <> 0 begin select @rntcode commit Tran end else begin select '' rollback tran end ");
	2104	return this.jdbcTemplate.queryForObject(sb.toString(), String.class);
	2105	}
	2106
	2107	private List getCodeInfo(int formid) {
d3265b	2108	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=?";
a6a76f	2109	return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
F	2110	}
	2111
	2112	public String getPeriodID(int formid, String companyid, String docdate) {
d3265b	2113	String sql = " set nocount on; select dbo.GetPeriodID(" + formid + ",'" + companyid + "','" + docdate + "')";
a6a76f	2114	return this.jdbcTemplate.queryForObject(sql, String.class);
F	2115	}
	2116
	2117	/**
	2118	* 取得自动编号的类型
	2119	*/
	2120	private List getAutoCodeType(int formid) {
98c7bf	2121	String sql = "set nocount on; select precodetype,codelength,preFixcode from gform where formid=?";
a6a76f	2122	return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
F	2123	}
	2124
	2125	public String getTreeTable(int formid) {//返回格式：树1的表名;树1功能号,树2表名;树2功能号
d3265b	2126	String sql = " set nocount on; SELECT stuff((SELECT ',' + CONVERT(VARCHAR, g.HDTable)+';'+CONVERT(VARCHAR, g.formid) from _systreeset s,gform g \n" +
a6a76f	2127	" where s.formid=? and s.treeformid=g.formid FOR XML PATH ('')),1,1,'')";
98c7bf	2128	return this.jdbcTemplate.queryForObject(sql, String.class, new Object[]{formid});
a6a76f	2129	}
F	2130
	2131	/**
	2132	* 取得三表设置的相关联信息,9825
	2133	*/
	2134	public List getThreeTableInfo(int formid) {
c1439b	2135	String sql = " set nocount on; select DetailFormID,MasterField,MasterKeys,DetailKeys,MasterSumFields,DetailSumFields,GridHeight from _sysmasterdetail where formid=? ";// 为了新增对不是三表的也支持，去掉这个限制and FormID<>DetailFormID
a6a76f	2136	return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
F	2137	}
	2138
	2139	/**
	2140	* 取得功能号的过账类型号
	2141	**/
	2142	public int getTranID(int formid) {
d3265b	2143	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});
a6a76f	2144	return in;
F	2145	}
	2146
	2147	/**
	2148	* 取得7类型功能号的过滤条件
	2149	**/
	2150	public List<Map<String, Object>> getFilter7(int formid) throws DataAccessException {
d3265b	2151	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";
a6a76f	2152	return this.jdbcTemplate.queryForList(sql, formid);
F	2153	}
	2154
	2155	/**
	2156	* 取得功能号的保存时执行及保存时执行存储过程组
	2157	**/
	2158	public Map<String, Object> getDealAfterDocSave(int formid) {
d3265b	2159	return this.jdbcTemplate.queryForMap(" set nocount on; select DealAfterDocSave,ProcGroupafterSavedoc,CancelBtnProcName,RevokeBtnProcName,importProcName from gform where formid=? \n", formid);
a6a76f	2160	}
F	2161
	2162	/**
	2163	* 取得功能号导入时保存时执行存储过程
	2164	**/
	2165	public String getImportExcelProc(int formid) {
d3265b	2166	return this.jdbcTemplate.queryForObject(" set nocount on; select importProcName from gform where formid=?", String.class, new Object[]{formid});
a6a76f	2167	}
F	2168
	2169	/**
	2170	* 根据功能号取得主表名
	2171	**/
	2172	public String getTableName3(int formid, String table) {
98c7bf	2173	return this.jdbcTemplate.queryForObject("set nocount on; select " + table + " from gform where formid=?", String.class, new Object[]{formid});
a6a76f	2174	}
F	2175
	2176	/**
	2177	* 取得与树设置有关的字段名
	2178	**/
	2179	private String getTreeFields(int formid) {
98c7bf	2180	return this.jdbcTemplate.queryForObject("set nocount on; select treefield from _systreeset where formid=?", String.class, new Object[]{formid});
a6a76f	2181	}
F	2182
	2183	@Override
	2184	public String getByProc(final String num) {
	2185	String sql = "{call getXXXXByNum(?,?)}";
	2186	Map map = (Map) this.jdbcTemplate.execute(sql, new CallableStatementCallback() {
	2187	public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
	2188	cs.setInt(1, Integer.parseInt(num));
	2189	cs.registerOutParameter(2, Types.VARCHAR);// 输出参数
	2190	cs.execute();
	2191	Map map = new HashMap();
	2192	map.put("rowids", cs.getString(2));
	2193	return map;
	2194	}
	2195	});
	2196	return (String) map.get("rowids");
	2197	}
	2198
	2199	/**
	2200	* 取得与树设置有关的字段名
	2201	**/
	2202	public String getTableKeyFields(String table) {
98c7bf	2203	List<String> list = this.jdbcTemplate.queryForList("set nocount on; select keyfields from _systablekey where tableid=?", String.class, new Object[]{table});
a6a76f	2204	if (list.size() > 0) return list.get(0);
F	2205	else return "";
	2206	}
	2207
	2208	@Override
	2209	public List<Map<String, Object>> getGlfiledInfo(String s) {
98c7bf	2210	return this.jdbcTemplate.queryForList("set nocount on; select a.*,b.docitem from fcounttype a,FCountItem b where a.cv=b.countitem and b.acctcode=? order by b.docitem", new Object[]{s});
a6a76f	2211	}
F	2212
	2213	@Override
	2214	public List<Integer> getGlfiledInfoCheck(String s) {
98c7bf	2215	return this.jdbcTemplate.queryForList("set nocount on; select docitem from FCountItem where acctcode=? order by docitem asc", Integer.class, new Object[]{s});
a6a76f	2216	}
F	2217
	2218	/**
	2219	* 判断字符串是否为空或null
	2220	**/
	2221	private boolean isNullOrEmptry(String s) {
	2222	return s != null && s != "" && "".equals("") && s.length() != 0 ? false : true;
	2223	}
	2224
	2225	/**
2e60c6	2226	* 根据表名取得表主键名,查找表结构和9807设置
a6a76f	2227	*
F	2228	* @return int
	2229	* @throws SQLException
	2230	*/
2e60c6	2231	@Override
a6a76f	2232	public List<String> getPrimaryKey(String tableName) throws DataAccessException, SQLException {
F	2233	List<String> key = new ArrayList<String>();
d3265b	2234	String sql=" declare @key varchar(1000),@tableName varchar(100)=?\n" +
2e60c6	2235	" SELECT @key=stuff((SELECT';' + CONVERT(VARCHAR, column_name)\n" +
F	2236	" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC\n" +
	2237	" INNER JOIN\n" +
	2238	" INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU\n" +
	2239	" ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND\n" +
	2240	" TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND \n" +
	2241	" KU.table_name=@tableName\n" +
	2242	" ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION\n" +
	2243	" FOR XML PATH ('')),1,1,'')\n" +
	2244	" if isnull(@key,'')=''\n" +
	2245	" begin\n" +
	2246	" select @key=keyfields from _systablekey where tableid=@tableName\n" +
	2247	" end\n" +
d3265b	2248	" select lower(@key) \n";
2e60c6	2249	final String result = this.getSimpleJdbcTemplate().queryForObject(sql, String.class, tableName);
F	2250	if(result!=null){
	2251	key=Arrays.asList(result.split(";"));
a6a76f	2252	}
F	2253	return key;
	2254	}
	2255
	2256	/**
	2257	* 根据类型号，得到当前功能号是什么类型
	2258	**/
	2259	public int getwinType(int type) {
	2260	switch (type) {
	2261	// 1-4,6是只有主表
	2262	case 1:
	2263	case 2:
	2264	case 3:
	2265	case 4:
	2266	case 6:
	2267	case 38:
	2268	case 7:
	2269	return 1;
	2270	case 5:// 主从表
	2271	case 9:
	2272	return 9;
	2273	case 10:
	2274	return 2;
	2275	case 8:// 三表
	2276	return 3;
	2277	case 18:// 表名为函数
	2278	return 4;
	2279	case 19:// 表名为存储过程
	2280	return 5;
	2281	default:
	2282	return 0;
	2283	}
	2284	}
	2285
	2286	@Override
a1f55f	2287	public String getTableNameByType(GformEntity gformEntity, String type) {
a6a76f	2288	String[] temp = type.split("\\\|");
F	2289	int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0;
	2290	String table = "";
	2291	int winType = Integer.parseInt(temp[0]);
a1f55f	2292	if (gformEntity!=null) {
F	2293	FdFilters = gformEntity.getFormdatafilters();
a6a76f	2294	table = (winType == 0 \|\| winType == 7 \|\| winType == 1 \|\| winType == 5 \|\| winType == 2 \|\| (winType == 10 && conNum == 1) \|\|
F	2295	(winType == 9 && conNum == 0) \|\| winType == 3 \|\| (winType == 4 && conNum == 0) \|\| winType == 17 \|\|
	2296	//(winType == 20 && conNum == 0)\|\|--pc上 20，0是取从表，所以app需要调用winType == 20 & connum=1
	2297	(winType == 301 && conNum == 0) \|\| (winType == 303 && conNum == 0)
	2298	\|\| (winType == 304 && conNum == 0)
	2299	//\|\|(winType == 238 && conNum == 0)\|\|
	2300	//(winType == 30&& conNum == 1)\|\|--app直接调用connum=1就可以，PC端加载或保存时调用conNum=0，取到从表
	2301	\|\| (winType == 302 && conNum == 0) \|\| winType == 19 \|\| (winType == 499 && conNum == 0) \|\|
	2302	(winType == 497 && conNum == 0) \|\| (winType == 15 && (conNum == 0 \|\| conNum == 2)))
a1f55f	2303	? gformEntity.getHdtable() : gformEntity.getDttable();
a6a76f	2304
F	2305
	2306	}
	2307	return table;
	2308	}
	2309
	2310	@Override
	2311	public Map<String,TableMetaData> getTableMetaData(String tableName) {
d3265b	2312	List<TableMetaData> list= this.getJdbcTemplate().query(" set nocount on; select b.name as columnName, c.name as dataType ,b.iscomputed,b.length\n" +
a6a76f	2313	" from sysobjects a \n" +
F	2314	" join syscolumns b on a.id = b.id \n" +
	2315	" join systypes c on b.xtype=c.xtype and b.xtype = c.xusertype and c.status = 0 \n" +
	2316	" where a.NAME = ?\n" +
	2317	" and b.iscomputed = 0 --取非计算列\n" +
	2318	" and b.colstat = 0 --取非自增列 \n" +
d3265b	2319	" order by b.colorder \n",new BeanPropertyRowMapper<>(TableMetaData.class), tableName);
a6a76f	2320
F	2321	HashMap<String,TableMetaData> map=new HashMap<>();
	2322	list.stream().forEach(x->{
	2323	map.put(x.getColumnName().toLowerCase(),x);
	2324	});
	2325
	2326	return map;
	2327
	2328	}
	2329
	2330	/**
	2331	* 根据功能号取得主从表名称 -----9801信息---增加新窗体类型都需要增加相应判断
	2332	**/
1a8ee6	2333	@Override
a6a76f	2334	public String getTableName(int formid, String type) {
F	2335	SqlRowSet gform = this.getGformByFormID(formid);
1a8ee6	2336	return getTableNameV2(gform, type);
F	2337	}
	2338	@Override
	2339	public String getTableNameV2(SqlRowSet gform, String type) {
a6a76f	2340	String[] temp = type.split("\\\|");
F	2341	int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0;
	2342	String table = "";
	2343	int winType = Integer.parseInt(temp[0]);
	2344	if (!gform.wasNull()) {
	2345	gform.first();
	2346	FdFilters = gform.getString("formdatafilters");
	2347	table = (winType == 0 \|\| winType == 7 \|\| winType == 1 \|\| winType == 5 \|\| winType == 2 \|\| (winType == 10 && conNum == 1) \|\|
	2348	(winType == 9 && conNum == 0) \|\| winType == 3 \|\| (winType == 4 && conNum == 0) \|\| winType == 17 \|\|
	2349	//(winType == 20 && conNum == 0)\|\|--pc上 20，0是取从表，所以app需要调用winType == 20 & connum=1
	2350	(winType == 301 && conNum == 0) \|\| (winType == 303 && conNum == 0)
	2351	\|\| (winType == 304 && conNum == 0)
	2352	//\|\|(winType == 238 && conNum == 0)\|\|
	2353	//(winType == 30&& conNum == 1)\|\|--app直接调用connum=1就可以，PC端加载或保存时调用conNum=0，取到从表
	2354	\|\| (winType == 302 && conNum == 0) \|\| winType == 19 \|\| (winType == 499 && conNum == 0) \|\|
	2355	(winType == 497 && conNum == 0) \|\| (winType == 15 && (conNum == 0 \|\| conNum == 2)))
	2356	? gform.getString("hdtable") : gform.getString("dttable");
	2357
	2358
	2359	}
	2360	return table;
	2361	}
	2362	/**
	2363	* 获得数据组内容，组装条件语句附加到需要查询的地方，形式："and ccode=12345"
	2364	*
	2365	* @param flag 0 主表，1从表
	2366	**/
	2367	public List<Map<String, Object>> getDataGroupInfo(int formid, String usercode, int flag) {
d3265b	2368	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 "
F	2369	+ " 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";
a6a76f	2370	return this.jdbcTemplate.queryForList(sql, new Object[]{usercode, formid, flag});
F	2371
	2372	}
	2373
	2374	/**
	2375	* 根据功能号查出所有字段，以,分隔返回。是为了给淘宝接口fileds调用
	2376	***/
	2377	public String getFileds(String formid) {
98c7bf	2378	List<Map<String, Object>> list = this.jdbcTemplate.queryForList("set nocount on; select fieldid from gField where formid=? and datalink=1", new Object[]{formid});
a6a76f	2379	StringBuffer sb = new StringBuffer();
F	2380	for (Map<String, Object> map : list) {
	2381	sb.append((String) map.get("fieldid")).append(",");
	2382	}
	2383	return sb.length() > 0 ? sb.substring(0, sb.length() - 1) : "";
	2384	}
	2385
	2386	/**
	2387	* 根据父节点查询下一节点的rowid
	2388	*
	2389	* @param parentrowid
	2390	* @param tablename
	2391	* @return
	2392	*/
	2393	public List<Map<String, Object>> getTreeRowid(String parentrowid, String tablename) {
98c7bf	2394	String sql = "set nocount on; select * from " + tablename + " where parentrowid='" + parentrowid + "'";
a6a76f	2395	return this.jdbcTemplate.queryForList(sql);
F	2396	}
	2397
	2398	@Override
	2399	public List<Map<String, Object>> getComputedHelp(String formid) {
98c7bf	2400	String sql = "set nocount on; select * from _sys_formid_Help where doccode='" + formid + "'";
a6a76f	2401	return this.jdbcTemplate.queryForList(sql);
F	2402	}
	2403	}