package com.yc.service.grid;
|
|
import com.yc.action.excel.Excel;
|
import com.yc.action.execProc.ExecuteProcAction;
|
import com.yc.action.grid.Grid;
|
import com.yc.action.grid.GridUtils;
|
import com.yc.action.grid.PicEntity;
|
import com.yc.action.grid.SqlInfo;
|
import com.yc.action.taobao.Taobao2ERP;
|
import com.yc.action.upload.AttachmentAction;
|
import com.yc.app.v2.entity.GformEntity;
|
import com.yc.entity.TableColumnsDataTypeEntity;
|
import com.yc.exception.ApplicationException;
|
import com.yc.factory.FactoryBean;
|
import com.yc.open.utils.HttpClientUtil;
|
import com.yc.sdk.password.action.ChangePassword;
|
import com.yc.service.BaseService;
|
import com.yc.service.new38type.Type38Ifc;
|
import com.yc.service.upload.AttachmentIfc;
|
import com.yc.utils.*;
|
import org.apache.commons.lang.RandomStringUtils;
|
import org.apache.commons.lang.StringUtils;
|
import org.apache.http.client.methods.HttpGet;
|
import org.apache.http.impl.client.CloseableHttpClient;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.jdbc.core.CallableStatementCallback;
|
import org.springframework.jdbc.core.SqlOutParameter;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
import org.springframework.jdbc.support.rowset.SqlRowSet;
|
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import javax.servlet.http.HttpSession;
|
import java.io.UnsupportedEncodingException;
|
import java.net.URLEncoder;
|
import java.sql.CallableStatement;
|
import java.sql.SQLException;
|
import java.sql.Types;
|
import java.util.*;
|
import java.util.Map.Entry;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
/**
|
* gt-grid有关业务处理实现
|
*
|
* @author 邓文峰 2010-3-22
|
*/
|
@Service
|
public class GridServiceImpl extends BaseService implements GridServiceIfc {
|
@Autowired
|
Type38Ifc type38Ifc;
|
private final String gform = "[formid], [formname],[isused],[formmemo],[optype],[refformid],[hdtable],[dttable],[showdetail],[fetchrecnumber],[queryform],[refreshTime],[refresh_aftersave],[frozencols],[LockGridSort],[rowcopyfields],[rowcopyformids],[rowDelFormids],[index1],[index1primary],[index2],[index2primary],[index3],[index3Primary],[dataformid],[predocstatus],[postdocstatus],[DocStatusName],[transgroupcode],[codelength],[preFixcode],[precodetype],[helpdoc_udl],[checkblncfields],[formalign],[DealAfterDocSave],[AllowEmptyRow],[Busi2fiDataView],[glcodefield],[txtinputfields],[DBCtrlRowCount],[ShowAsDetailMode],[modifytableflag],[applytableflag],[CrossInputType],[MultiTitleType],[datapooled],[glentitycode],[formdatafilters],[ProcGroupafterSavedoc], [TransTypecode],[currencyfld],[chkFormula],[GridFormatFun],[SelectChecker],[ReturnCurChecker],[ReturnCurCheckerName],[FT],[FTFormType],[FK],[SeekGroupID],[sPremissField],[dPremissField],[FKeFilter],[GridRowHeight],[GridHeight], [isShowPwdEdit], [isOpenFuncShowPwdEdit], [isShowOnlineMsg] ,[isShowProcessTracking] ,[PanelLabelLayout],[isTitle], [isTaobao],[cellAlign] ,[pageSize],[version],[reportprompts],[CancelBtnProcName],[CancelBtnExpression],[CancelBtnEditStatus],[isShowCell],[importProcName],[CancelIsSave],[CancelBtnName],[isGantt],[byGroup],[DefaultRowCount],[Expanded], [isFilter], [JNIName],[JNIDataBaseURL], [JNIPort] ,[JNIDataBaseName], [JNIUser], [JNIPwd] ,[JNISql] ,[JNITempTable],[addNewRow],[autopaging],[RevokeBtnProcName],[RevokeBtnExpression],[RevokeBtnEditStatus],[autoOpen],isExchangeDataWithHost,isShowNewDocButton,isShowMemoWhenApprovals,isshowinserialbtn,inserialbtneditstatus,isshowoutserialbtn ,outserialbtneditstatus,isShowOnlinePaymentButton,isShowGridStyleForApp ,popupWindowsHeightRate,isShowCycleCountSerialBtn,cycleCountSerialBtnEditStatus,excludeTablesWhenSaving ";//
|
|
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 ";
|
|
private final String GET_GFORM = " set nocount on; select " + gform + " from gform where formid=? \n";
|
private final String GET_GFIELD = " set nocount on; select " + gfield + " from gfield where formid=? order by statisid asc\n";
|
private final String GET_GFIELD9 = " set nocount on; select " + gfield + " from gfield where formid=? and HeadFlag=? order by statisid asc\n";// 9类型窗体
|
private final static String GET_WINDOWTYPE = " set nocount on; select FormType from _sysMenu where formid=? \n";
|
private final static String GET_FTDATA = " set nocount on; select interValue,dictvalue from _sysdict where dictid=? order by sequence asc\n";
|
|
// 保存时执行处理
|
private final static String SAVE_PROC = " set nocount on; select [formid]\n" +
|
" ,[execSeq]\n" +
|
" ,[ProcName]\n" +
|
" ,[action]\n" +
|
" ,[objectType]\n" +
|
" ,[memo]\n" +
|
" ,[inactive] from _sys_FormProc where formid=? and isnull(inactive,0)=0 order by execseq asc\n";
|
// 删除前存储过程
|
private final static String DEL_PROC = " set nocount on; select execProc from _sysDeleteDoc where formid=? order by seqn asc\n";
|
// 过帐类型
|
private final static String TRANS_GROUP = " set nocount on; select a.reloaddata,a.datatable,a.filterstring,a.updatesql,a.updatesql2, \n" + " b.CheckView as BeforeSQlCheckView,b.EmptyRowRaised as BeforeSQlEmptyRowRaised,b.CheckRaiseMsg as BeforeSQlCheckRaiseMsg, \n"
|
+ " c.CheckView as AfterSQlCheckView,c.EmptyRowRaised as AfterSQlEmptyRowRaised,c.CheckRaiseMsg as AfterSQlCheckRaiseMsg \n" + " from vsystransgroup a left join _sysTransPostCheck b on a.BeforeSQlcheckid = b.CheckID \n"
|
+ " left join _sysTransPostCheck c on a.AfterSQLcheckid = c.CheckID \n" + " where a.transgroupcode=? and a.actived=1 order by a.sortid asc \n";
|
// @Autowired
|
// PanelServiceIfc panel;
|
private String FdFilters = "";
|
@Autowired
|
Taobao2ERP taobao;
|
@Autowired
|
Excel excel;
|
@Autowired
|
private AttachmentIfc attachmentIfc;
|
|
///// *******************************************************
|
// 以下部分是DAO操作函数
|
//// ********************************************************
|
public String getGET_GFORM() {
|
return GET_GFORM;
|
}
|
|
public String getGET_GFIELD() {
|
return gfield;
|
}
|
|
public String getGET_GFIELD9() {
|
return gfield;
|
}
|
@Override
|
public String getExcludeTablesWhenSaving(int formid){
|
return this.jdbcTemplate.queryForObject("select excludeTablesWhenSaving from gform where formid=?", String.class,formid);
|
}
|
@Override
|
public void doNewTran(Integer formid, String docCode,String userCode,String userName) {
|
String sql=" set nocount on \n declare @docCode varchar(50)=? ,@PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int ,@EnterCode varchar(50)=?,@EnterName varchar(50)=?,@Memo varchar(100),@LinkDocInfo varchar(100)\n " +
|
" exec p" + formid + "Save @doccode\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from t" + formid + "H where docCode=@docCode\n" +
|
" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @PostFormId \n" +
|
" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0)\n" +
|
" begin \n" +
|
" update a set DocStatus = @PostDocStatus,PostCode=@EnterCode,PostName=@EnterName,PostDate=getdate()\n" +
|
" from t" + formid + "H a where a.DocCode = @docCode \n" +
|
" exec p" + formid + "Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end\n";
|
this.jdbcTemplate.update(sql,docCode,userCode,userName);
|
}
|
|
@Override
|
public SqlRowSet getGfiledByFormID(int formID) {
|
return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD, new Object[]{formID});
|
}
|
|
public List getGfiledByFormID9(int formID, int flag) {
|
// return this.jdbcTemplate.queryForRowSet(this.GET_GFIELD9, new Object[]{formID,flag});
|
return this.jdbcTemplate.queryForList(this.GET_GFIELD9, new Object[]{formID, flag});
|
}
|
|
@Override
|
public SqlRowSet getGformByFormID(int formID) {
|
return this.jdbcTemplate.queryForRowSet(this.GET_GFORM, new Object[]{formID});
|
}
|
|
private String procOrderBy(String order) {// 处理38类会存在group by的情况，所以统一在这里组装不同的情况
|
// 多种可能
|
// abc asc,bw desc
|
// order by abc asc,de desc
|
// group by aa,cc order by abc
|
// order by abc group by aa,cc
|
// group by aa,cc
|
if ("".equalsIgnoreCase(order)) return "";
|
if (order.toLowerCase().indexOf("order by") > -1) {// 表示有 order by
|
return order;
|
} else {
|
if (order.toLowerCase().indexOf("group by") > -1) {// 存在有group by
|
return order;
|
|
} else {// 需要加上order by
|
|
return " order by " + order;
|
}
|
|
}
|
|
}
|
|
@Override
|
public Page loadAll(Page page) {
|
//处理复单时设置了排除指定功能号
|
boolean isCp = false;
|
if (page.getIsCp() == 1) {
|
String rowcopyformids = null;
|
String sql = "";
|
if (page.getWinType().startsWith("15")) {//15类型
|
sql = " set nocount on; select rowcopyformids from gform where formid=(select formid from _sysmasterdetail where DetailFormID=?) \n";
|
} else if (page.getWinType().startsWith("49")) {//多表
|
sql = " set nocount on; select rowcopyformids from gform where formid=(select mainformid from _sys_TabPageFormid where formid=?) \n";
|
} else {
|
sql = " set nocount on; select rowcopyformids from gform where formid=? \n";
|
}
|
try {
|
rowcopyformids = this.getJdbcTemplate().queryForObject(sql, String.class, page.getFormid());
|
} catch (Exception e) {
|
}
|
if (rowcopyformids != null) {
|
String[] formids = rowcopyformids.split(";");
|
for (String id : formids) {
|
if (id.equals(page.getFormid() + "")) {
|
isCp = true;
|
break;
|
}
|
}
|
}
|
}
|
if (page.getFlag() == 2 || (page.isNull && page.getFlag() != 1) || (page.getFlag() != 1 && page.getWhere().length() == 0) || isCp) {
|
page.setWhere(" 1=2 ");
|
}
|
if (page.getWhere().trim().indexOf("and") == 0) {
|
page.setWhere(" 1=1 " + page.getWhere());
|
}
|
page.setTbCols(this.proccTbCols(page));
|
//----生成10个数组处理参数内容过长，需要截断分组传
|
String[] tbcols = new String[10];
|
int len = page.getTbCols().length();
|
Map tbColsMap = new HashMap();
|
if (len > 0) {
|
for (int i = 0; i < 10; i++) {
|
if (len >= (i + 1) * 3500) {
|
tbcols[i] = page.getTbCols().substring(i * 3500, (i + 1) * 3500);
|
tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]);
|
} else {
|
tbcols[i] = page.getTbCols().substring(i * 3500, len); //endIndex只能是>=beginIndex
|
tbColsMap.put(("@TotalTbCols" + (i == 0 ? "" : (i + 1))), tbcols[i]);
|
break;
|
}
|
}
|
}
|
//----end
|
page.setSql(page.getSql().replaceAll("%2F", "/").replaceAll("%2B", "+"));
|
page.setTbColsMap(tbColsMap);
|
Map<String, Object> map = null;
|
if ("SP_viewPageV4" .equals(page.getPROC_NAME())) {
|
map = this.simpleJdbcCall.withProcedureName("SP_viewPageV4")
|
.declareParameters(// 定义存储过程参数返回值
|
new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
|
.execute(page.getTableName(), page.getSql(),
|
page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
|
page.getPageSize(), page.getPageNum(), page.getGroupby(),
|
tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
|
tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
|
page.getFormid(), page.getUserCode(), page.getUserName(),
|
0, 0, null);
|
} else {
|
//--19类型用SP_viewPageV3
|
map = this.simpleJdbcCallByProc.withProcedureName("SP_viewPageV3")
|
.declareParameters(// 定义存储过程参数返回值
|
new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
|
.execute(page.getTableName(), page.getSql(),
|
page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
|
page.getPageSize(), page.getPageNum(), page.getGroupby(),
|
tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
|
tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
|
page.getFormid(), page.getUserCode(), page.getUserName(),
|
0, 0, null);
|
}
|
page.setData((List) map.get("#result-set-1"));
|
page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount"));
|
page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount"));
|
page.setTbColsOut((String) map.get("TotalTbColsOut"));
|
if (page.getTbColsOut() != null && !"" .equals(page.getTbColsOut())) {//处理返回是0.的情况，在前端会显示NaN
|
String temp = page.getTbColsOut().replaceAll("\\.#", "#");
|
page.setTbColsOut(temp);
|
}
|
return page;
|
}
|
|
|
//2017-9-22复制过来定制页面调用
|
@Override
|
public Page loadAlls(Page page, String statisid, int formid) {
|
String z = page.getWhere().toLowerCase().substring(page.getWhere().length() - 1, page.getWhere().length());
|
|
// convert.FromBase64String(page.getWhere());
|
|
if (page.getWhere() != null && (page.getWhere().length() / 4 == 0 || "=".equals(z) && page.getWhere().length() > 50)) {
|
String queryString = page.getWhere();
|
queryString = queryString.replaceAll("%2B", "+");
|
queryString = queryString.replaceAll("%2F", "/");
|
//String t="";
|
if (queryString.indexOf("?") != -1) {
|
//t=queryString.substring(0,queryString.indexOf("?")+1);
|
queryString = queryString.substring(queryString.indexOf("?"));
|
}
|
try {
|
page.setWhere(EncodeUtil.base64Decode(queryString));
|
} catch (Exception e) {
|
|
}
|
}
|
if (page.getWhere() != null && !"=".equals(z) && page.getWhere().length() < 50) {
|
String[] q = page.getWhere().split("=");
|
if (q.length >= 3 && "9822".equals(q[2])) {
|
page.setWhere(" statisid=" + "'" + statisid + "'" + " and formid=" + formid);
|
}
|
}
|
|
//}
|
if (page.getWhere().length() < 24) {
|
String[] where = page.getWhere().replace("(","").replace(")","").trim().split(";");
|
|
if (Integer.parseInt(where[0]) == 0) {
|
String sql1 = " set nocount on; SELECT fieldid,fieldcaption,fieldalias,displayYN,StatisType,Sequence ,DisplayWidth,displayformat,cellAlign,sumfield,funclinkname,isFilterZero,jionFlag,jionFlagGroup,conFlag,modfvalues,modfvalues2 from _sysStatisDetail where StatisID=" + "'" + where[2] + "'" + " and FormID=" + where[1] + " "
|
+ "UNION ALL "
|
+ "select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias,0 as displayYN,'' as StatisType,0 AS Sequence,'' as DisplayWidth,"
|
+ "displayformat,cellAlign,'' as sumfield,'' as funclinkname,'' as isFilterZero,'' as jionFlag,'' as jionFlagGroup,'' as conFlag,'' as modfvalues,'' as modfvalues2 "
|
+ "FROM gfield a where formid=" + where[1] + " and headflag = 0 and (isnull(fieldname,'') <> '' or isnull(gridcaption,'') <> '' ) AND not EXISTS(select 1 FROM _sysStatisDetail b WHERE b.StatisID=" + "'" + where[2] + "'" + " and FormID=" + where[1] + " AND a.FieldID=b.FieldID)"
|
+ "order by displayYN DESC,Sequence asc,fieldcaption ASC \n";
|
page.setWhere(sql1);
|
}
|
} else {
|
//page.setWhere(page.getWhere()+"and displayYN=1");
|
}
|
|
if (page.getWhere().length() > 100) {
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(page.getWhere());
|
for (int i = 0; i < list.size(); i++) {
|
list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
|
}
|
page.setData(list);
|
} else {
|
if (page.getFlag() == 2 || (page.isNull && page.getFlag() != 1) || (page.getFlag() != 1 && page.getWhere().length() == 0))
|
page.setWhere("1=2");
|
if (page.getWhere().trim().indexOf("and") == 0) page.setWhere("1=1 " + page.getWhere());
|
|
page.setTbCols(this.proccTbCols(page));
|
//----生成10个数组处理参数内容过长，需要截断分组传
|
String[] tbcols = new String[10];
|
int len = page.getTbCols().length();
|
if (len > 0) {
|
for (int i = 0; i < 10; i++) {
|
if (len >= (i + 1) * 3500) {
|
tbcols[i] = page.getTbCols().substring(i * 3500, (i + 1) * 3500);
|
} else {
|
tbcols[i] = page.getTbCols().substring(i * 3500, len); //endIndex只能是>=beginIndex
|
break;
|
}
|
}
|
}
|
//----end
|
|
Map<String, Object> map = this.simpleJdbcCall.withProcedureName(page.getPROC_NAME())
|
.declareParameters(// 定义存储过程参数返回值
|
new SqlOutParameter("TotalCount", Types.INTEGER), new SqlOutParameter("TotalPageCount", Types.INTEGER), new SqlOutParameter("TotalTbColsOut", Types.VARCHAR))
|
.execute(page.getTableName(), page.getSql(),
|
page.getWhere(), page.getOrderBy(), page.getAutopaging(), 0,
|
page.getPageSize(), page.getPageNum(), page.getGroupby(),
|
tbcols[0], tbcols[1], tbcols[2], tbcols[3], tbcols[4],
|
tbcols[5], tbcols[6], tbcols[7], tbcols[8], tbcols[9],
|
page.getFormid(), page.getUserCode(), page.getUserName(),
|
0, 0, null);
|
|
List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("#result-set-1");
|
|
for (int i = 0; i < list.size(); i++) {
|
list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
|
}
|
|
/* for(int i =0;i<list.size();i++){
|
if(list.get(i).get("displayyn").equals(1)){
|
list1.add(list.get(i));
|
}
|
} */
|
|
page.setData(list);
|
|
page.setTotalRowNum(map.get("TotalCount") == null ? 0 : (Integer) map.get("TotalCount"));
|
page.setTotalPageNum(map.get("TotalPageCount") == null ? 0 : (Integer) map.get("TotalPageCount"));
|
page.setTbColsOut((String) map.get("TotalTbColsOut"));
|
map = null;
|
}
|
return page;
|
}
|
|
@Override
|
public Page loaddata(Page page, int formid) {
|
String sql = " set nocount on; select fieldid,case when isnull(gridcaption,'') <> '' then gridcaption else fieldname end as fieldcaption,'' AS fieldalias,"
|
+ "0 as displayYN,'' as StatisType,0 AS Sequence,'' as DisplayWidth,"
|
+ "displayformat,"
|
+ "cellAlign,'' as sumfield,'' as funclinkname FROM gfield where formid=" + formid + " and headflag = 0 and (isnull(fieldname,'') <> '' or isnull(gridcaption,'') <> '' ) order by headflag desc ,statisid asc \n";
|
List<Map<String, Object>> list = type38Ifc.girddata(sql);
|
/* for(int i =0;i<list.size();i++){
|
list.get(i).put("_YC_option_", "<a onclick='show()' style='color:red'>编辑过滤条件</a>");
|
}*/
|
page.setData(list);
|
page.setTotalRowNum(0);
|
page.setTotalPageNum(0);
|
page.setTbColsOut("0");
|
list = null;
|
return page;
|
}
|
|
@Override
|
public synchronized Page loadAllNoPage(Page page) {// 不分页
|
if (page.getFlag() == 2 || (page.isNull && page.getFlag() != 1) || (page.getFlag() != 1 && page.getWhere().length() == 0))
|
page.setWhere("1=2");
|
if (page.getWhere().trim().indexOf("and") == 0) page.setWhere("1=1 " + page.getWhere());
|
String sql = "select " + page.getSql() + " from " + page.getTableName() + ("".equalsIgnoreCase(page.getWhere()) ? "" : " where " + page.getWhere()) + ("".equalsIgnoreCase(page.getOrderBy()) ? "" : " order by " + page.getOrderBy());
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
|
page.setData(list);
|
return page;
|
}
|
|
@Override
|
public List<TableColumnsDataTypeEntity> getTableColumnsDataTypes(int formid, int isDetailTable, String fieldids) {
|
String sql = " set nocount on \n " +
|
" declare @table varchar(2000) ,@formid int =? , @isDetailTable int =?," +
|
" @fieldids varchar(max) =? \n" +
|
" declare @formtype int ,@pos int =0\n" +
|
" select @formtype = formtype from _sysmenu where formid = @formid \n" +
|
" select @table = case when isnull(@isDetailTable,0) = 0 and @formtype " +
|
" not in (18) THEN hdtable else dttable end from gform where formid = @formid\n" +
|
" select @pos = CHARINDEX( '|',isnull(@table,'') )\n" +
|
" if @pos > 0 select @table = SUBSTRING(@table,0,@pos) \n" +
|
" if isnull(@formtype,0) in (18,19)\n" +
|
" begin\n" +
|
" select a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_OCTET_LENGTH \n" +
|
" from information_schema.ROUTINE_COLUMNS a \n" +
|
" where a.TABLE_NAME = @table \n" +
|
" and (isnull(@fieldids,'') = '' or a.COLUMN_NAME in (select list from getinstr(@fieldids)))\n" +
|
" end \n" +
|
" else \n" +
|
" begin \n" +
|
" select a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_OCTET_LENGTH \n" +
|
" from INFORMATION_SCHEMA.COLUMNS a \n" +
|
" where a.TABLE_NAME = @table \n" +
|
" and (isnull(@fieldids,'') = '' or a.COLUMN_NAME in (select list from getinstr(@fieldids)))\n" +
|
" end\n";
|
|
List<Map<String, Object>> list = this.getSimpleJdbcTemplate().queryForList(sql, formid, isDetailTable, fieldids);
|
if (list != null) {
|
List<TableColumnsDataTypeEntity> tableColumnsDataTypeEntities = new ArrayList<>();
|
for (Map<String, Object> map : list) {
|
TableColumnsDataTypeEntity tableColumnsDataTypeEntity = new TableColumnsDataTypeEntity();
|
tableColumnsDataTypeEntity.setDataLength(GridUtils.prossRowSetDataType_Int(map, "CHARACTER_OCTET_LENGTH"));
|
tableColumnsDataTypeEntity.setDataType(GridUtils.prossRowSetDataType_String(map, "DATA_TYPE"));
|
tableColumnsDataTypeEntity.setFieldId(GridUtils.prossRowSetDataType_String(map, "COLUMN_NAME"));
|
tableColumnsDataTypeEntities.add(tableColumnsDataTypeEntity);
|
}
|
return tableColumnsDataTypeEntities;
|
}
|
return null;
|
}
|
|
@Override
|
public SqlRowSetMetaData getMetaData(String tableName) {
|
return this.jdbcTemplate.queryForRowSet(" set nocount on; select * from " + tableName + " where 1=2\n").getMetaData();
|
}
|
|
private String getNewDocCode(Grid grid, HttpServletRequest request, String formid) {
|
String usercode;
|
if (request == null)// 不是通过页面提交，像手机或淘宝接口
|
usercode = grid.getEnv().get(SessionKey.USERCODE);
|
else usercode = (String) request.getSession().getAttribute(SessionKey.USERCODE);
|
return " exec sp_newdoccode " + formid + ",'" + usercode + "',@newDoccode output\n ";
|
}
|
|
private String getCurNewDocCode(String docPram) {
|
return " exec " + docPram.replaceAll("''$", "@newDoccode output\n ");
|
}
|
|
|
|
@Override
|
public SqlInfo doExecute(DoExecuteParameter parameterObject, HttpServletRequest request, HttpServletResponse response, List<PicEntity> picList, Grid grid, String formid, String docPram) throws DataAccessException {
|
String parm = parameterObject.getParm();
|
String tempCode = parameterObject.getDoccode();// 临时值
|
StringBuffer sql = new StringBuffer();
|
SqlInfo info = new SqlInfo();
|
String dbid = request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + "";
|
if (grid.isDoccode()) {
|
if ("".equalsIgnoreCase(tempCode) || ("@newDoccode").equalsIgnoreCase(tempCode)) {
|
if (docPram != "" && docPram.length() > 0) {// 自定义单号
|
sql.append(this.getCurNewDocCode(docPram));
|
} else{
|
sql.append(this.getNewDocCode(grid, request, formid));
|
}
|
} else {
|
info.setDoccode(tempCode);
|
sql.append(" set @newDoccode='" + tempCode + "' \n");
|
}
|
}
|
if (!"".equalsIgnoreCase(parameterObject.getStatus()) && !"".equalsIgnoreCase(parameterObject.getTableName())) {
|
// 判断状态值
|
sql.append(checkDocStatus(parameterObject, tempCode));
|
}
|
|
if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getCanelProc())) {// 不需要保存而执行取消确认
|
// TODO 取消确认功能 如何标识需要到时处理
|
// tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue);
|
sql.append(prossCanelProc(parameterObject, tempCode, 1,dbid));
|
sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n");
|
info.setSql(sql.toString());
|
info.setCanel("canel#");
|
return info;
|
}
|
if (parameterObject.isFl() && !"".equalsIgnoreCase(parameterObject.getRevokeProc())) {// 执行撤回
|
// TODO 取消确认功能 如何标识需要到时处理
|
// tempCode="canel#"+tempCode+"#"+(returnValue==null?"":returnValue);
|
sql.append(prossCanelProc(parameterObject, tempCode, 2, dbid));
|
sql.append(" \n set @Memo=@Memo \n set @LinkDocInfo=@LinkDocInfo \n");
|
info.setSql(sql.toString());
|
info.setCanel("revoke#");//
|
return info;
|
}
|
if (!parameterObject.isFl() && parameterObject.getFormid() != 9646 && parameterObject.getFormid() != 9654) {// 不需要保存而执行审核，通过
|
|
sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(parameterObject.getOaButtonProcParms()).append("\n").toString();
|
info.setSql(sql.toString());
|
return info;
|
|
}
|
|
int in = parameterObject.getDetailSql().size();
|
int up = parameterObject.getHeadSql().size();
|
int de = parameterObject.getDel().size();
|
int to = parameterObject.getTotalProc().size();
|
|
String[] list = new String[in + up + de];
|
String[] toBeStored = parameterObject.getHeadSql().toArray(new String[in]);
|
String[] toBeStored1 = parameterObject.getDetailSql().toArray(new String[up]);
|
String[] toBeStored_p = parameterObject.getTotalProc().toArray(new String[to]);
|
String[] toBeStored2 = parameterObject.getDel().toArray(new String[de]);
|
//由于增加了触发器更新版本号功能，所以执行顺序很重要，要按照先主表后从表的顺序组装sql，保证不会出现【当前单据内容已发生变化，请刷新页面然后重试此操作】提示
|
// 1-------表头数据
|
if (up > 0) {
|
System.arraycopy(toBeStored, 0, list, 0, up);
|
|
}
|
// 2----------表体数据
|
if (in > 0) {
|
System.arraycopy(toBeStored1, 0, list, up, in);
|
}
|
// 3----------删除数据
|
if (de > 0) {
|
System.arraycopy(toBeStored2, 0, list, up + in, de);
|
}
|
// 提交数据
|
for (String str : list) {
|
sql.append(str).append(" \n");
|
}
|
// 有删除图片的在这里执行
|
if (picList.size() > 0) {
|
StringBuilder delPicSql=new StringBuilder();
|
for (PicEntity picEntity : picList) {
|
String uuid = null;
|
String[] splt = null;
|
splt = picEntity.getContext().split(";");//分割出uuid和seq，seq可能存在多个的情况，需要处理这种情况,0A283B93-07ED-46B6-A66C-7223A71D94FE;9458;9567
|
picEntity.setIp(IPUtil.getIpAddr(request));
|
picEntity.setUserCode( request.getSession().getAttribute(SessionKey.USERCODE) + "");
|
picEntity.setUserName(request.getSession().getAttribute(SessionKey.USER_NAME) + "");
|
picEntity.setType(0);
|
uuid = splt[0];
|
final CloseableHttpClient client = HttpClientUtil.getClient();
|
if(delPicSql.length()==0&& org.apache.commons.lang3.StringUtils.isNotBlank(uuid)){
|
//第一次且有附件才需要增加变量的定义
|
delPicSql.append(" \n set nocount on \n " +
|
" declare @unid_Attachment varchar(50),@seq_Attachment int \n"
|
+ " declare @AcType_Attachment varchar(50) \n"
|
+ " declare @usercode_Attachment varchar(50) \n"
|
+ " declare @username_Attachment varchar(50) \n"
|
+ " declare @ip_Attachment varchar(50) \n"
|
+ " declare @mydelPicrowcount_Attachment int = 0 \n");
|
}
|
for (int i = 1; i < splt.length; i++) {
|
//磁盘文件删除，通过httpclient调用提交给附件服务器系统
|
try {
|
Integer.parseInt(splt[i]);//不是数字，表示不是seq，跳过
|
} catch (Exception e) {
|
continue;
|
}
|
try {
|
|
StringBuilder url = new StringBuilder();
|
url.append(AttachmentAction.domain)
|
.append("/attachment/deleteAttachmentByGrid.do")
|
.append("?formid=").append(formid)
|
.append("&unid=").append(uuid)
|
.append("&seq=").append(splt[i])
|
.append("&dbid=").append(URLEncoder.encode(ChangePassword.getEncryptPassword(dbid), "utf-8"));
|
final HttpGet httpGet = HttpClientUtil.CreateHttpGet(url.toString());
|
client.execute(httpGet);
|
} catch (Exception e) {
|
e.printStackTrace();//避免删除文件出错影响主进程操作，不进行处理
|
}
|
//数据库内容删除，直接调用本地代码
|
picEntity.setUuid(uuid);
|
picEntity.setSeq(Integer.parseInt(splt[i]));
|
delPicSql.append(attachmentIfc.getdeleteAttachmentSql(picEntity));
|
}
|
}
|
if(delPicSql.length()>0){
|
sql.append(delPicSql.toString());
|
}
|
}
|
if ("".equals(excel.getSqlext())) {
|
StringBuffer sqlext = new StringBuffer();
|
for (int s = 0; s < list.length; s++) {
|
sqlext.append(list[s]).append(",");
|
}
|
excel.setSqlext(sqlext.toString());
|
}
|
// 5---------9643执行保存时执行存储过程组
|
String p_str = "";//判断是否有相同的，只输出一个
|
if (toBeStored_p.length > 0) {
|
for (String s : toBeStored_p) {
|
if (!s.equalsIgnoreCase(p_str))
|
sql.append(" \n "+s);
|
p_str += s + ";";
|
}
|
}
|
|
// 6----
|
if (parameterObject.isFl()) {
|
//下列的执行次序不能乱
|
// ---excel导入 先执行后保存
|
if (!"".equalsIgnoreCase(parameterObject.getExcelProc()) || parameterObject.getExcelProc().length() > 0) {
|
if(parameterObject.getExcelProc().trim().toLowerCase().startsWith("exec")) {
|
//处理导入过程不带参数 少了exec关键字
|
sql.append(" \n ").append(parameterObject.getExcelProc()).append("\n");
|
}else {
|
sql.append(" \n exec ").append(parameterObject.getExcelProc()).append("\n");
|
}
|
}
|
// ----------9801保存时执行功能
|
if (!"".equalsIgnoreCase(parameterObject.getStr()) || parameterObject.getStr().length() > 0) {
|
if (!p_str.contains(parameterObject.getStr())) {//保存时执行存储过程组如果已存在，则去重
|
sql.append(" \n exec " + parameterObject.getStr() + " " + parm).append("\n");
|
}
|
}
|
// ---[-审核，通过]前先保存再执行
|
if (!"".equalsIgnoreCase(parameterObject.getOaButtonProcName()) || parameterObject.getOaButtonProcName().length() > 0) {
|
//---增加处理单据审核后跳转功能
|
StringJoiner procParms=new StringJoiner(",");
|
//从后面替换
|
String[] parms=parameterObject.getOaButtonProcParms().split(",");
|
for(int i=0;i<parms.length;i++){
|
if(i==parms.length-3){
|
//替换@ExcludeDocCodeWhenSelectNextDocument
|
procParms.add("'"+parameterObject.getJson().getExcludeSelectNextDocument()+"'");
|
}else if(i==parms.length-2){
|
//替换@Memo
|
procParms.add("@Memo output");
|
}else if(i==parms.length-1){
|
//替换@LinkDocInfo
|
procParms.add("@LinkDocInfo output");
|
}else {
|
procParms.add(parms[i]);
|
}
|
}
|
|
sql.append(" \n exec ").append(parameterObject.getOaButtonProcName()).append(" ").append(procParms.toString()).append("\n");
|
}
|
// ---[-调用 功能链接]前先保存再执行
|
if (!"".equalsIgnoreCase(parameterObject.getFunLinkProc()) && parameterObject.getFunLinkProc().length() > 0) {
|
|
sql.append(" \n exec ").append(parameterObject.getFunLinkProc()).append(" ").append(parameterObject.getFunLinkPram()).append("\n");
|
}
|
// ----淘宝接口
|
if (parameterObject.getJson().getTaobao() != null && parameterObject.getJson().getTaobao().size() > 0) {
|
// taobao.prossERP(parameterObject.getJson(),request,response,false);
|
|
}
|
// -------------
|
}
|
if(request.getAttribute("isTran")!=null&&grid.isDoccode()){
|
//表示是确认操作
|
sql.append(" \nupdate " + parameterObject.getTableName() + " set postCode='" +request.getSession().getAttribute(SessionKey.USERCODE)+"',postname='" + request.getSession().getAttribute(SessionKey.USERNAME) + "' where doccode=@newDoccode \n");
|
}
|
info.setSql(sql.toString());
|
return info;
|
}
|
|
/**
|
* 组装检查当前单号是否已确认
|
*/
|
private String checkDocStatus(DoExecuteParameter parameterObject, String temcode) {
|
if ("".equalsIgnoreCase(temcode)) return "";
|
StringBuffer sql = new StringBuffer();
|
|
sql.append(" select @docstatus=docstatus from " + parameterObject.getTableName() + " where doccode='" + temcode + "';\n ").append(" if @docstatus<>").append(parameterObject.getStatus()).append(" begin raiserror('")
|
.append(temcode + "-单据状态已变化,请刷新页面后再操作！',16,1); return end \n");
|
return sql.toString();
|
}
|
|
private String prossCanelProc(DoExecuteParameter parameterObject, String tempCode, int type, String dbid) {// 处理取消确认,撤回功能
|
String proc = null;
|
if (type == 1) {
|
proc = parameterObject.getCanelProc();
|
}else{
|
proc = parameterObject.getRevokeProc();
|
}
|
if (!"".equalsIgnoreCase(proc) && proc.length() > 0) {
|
// 取得过程传的参数有哪些是输出参数，以便组装sql
|
proc = proc.replaceAll("exec ", "").trim();
|
String proName = proc.split("\\s+")[0];
|
String parms = proc.split("\\s+")[1];
|
String[] arrPams = parms.split(",");
|
ExecuteProcAction exec = (ExecuteProcAction) FactoryBean.getBean("executeProcAction");
|
String str = " exec checkPeriodHasOpen @doccode = '" + tempCode + "' , @formid = " + parameterObject.getFormid() + " ,@PeriodState = @PeriodState output\n ";
|
String returnValue = exec.spellProcCanel2(proName, arrPams, dbid);
|
//去除变量定义，已统一在前面作了定义
|
Pattern p = Pattern.compile("exec.*");
|
java.util.regex.Matcher propsMatcher = p.matcher(returnValue);
|
while (propsMatcher.find()) {
|
returnValue=propsMatcher.group();
|
}
|
return str + " " + returnValue;
|
}
|
return tempCode;
|
}
|
|
/**
|
* 取得功能链接中的过程名与参数
|
**/
|
@Override
|
public List getFunLinkPro(String links) {
|
String[] temp = links.split(";");
|
String formid = "origformid";
|
String type = "origformtype";
|
if (temp.length == 4 && "taobao".equalsIgnoreCase(temp[3])) {
|
formid = "linkformid";
|
type = "linkformtype";
|
}
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select (select hdtable from gform where formid=(select linkformid from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]).append("))")
|
.append(" as procs,origfields from _sysfunclink ").append("where ").append(formid).append("='").append(temp[0]).append("' and ").append(type).append("=").append(temp[1]).append(" and sortid=").append(temp[2]+" \n");
|
return this.jdbcTemplate.queryForList(sql.toString());
|
}
|
|
@Override
|
public int getWindowTypeByGform(int formID) {
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(GET_WINDOWTYPE, formID);
|
if (list != null) {
|
Map<String, Object> map = list.get(0);
|
return GridUtils.prossRowSetDataType_Int(map, "FormType");
|
} else {
|
return 0;
|
}
|
}
|
|
@Override
|
public SqlRowSet getFTData(int ft) {
|
return this.jdbcTemplate.queryForRowSet(GET_FTDATA, new Object[]{ft});
|
}
|
|
@Override
|
public List getDataByAjaxBy42(String tabName, String fields, String where, int limit, int page,String orderField) throws DataAccessException {
|
String where_str = null;//针对条件一部分是用作替换参数，@G@后面部分用作sql的where条件
|
if (where.trim().indexOf("@G@") == 0) {
|
where = where.replaceAll("@G@", "");// 以|分隔的第二部分是格线需要用的
|
where_str = where;
|
} else if (where.trim().indexOf("@G@") > 0) {//表示有参数值
|
String[] whereSplit = where.trim().split("@G@");
|
if (whereSplit.length > 1) {
|
where_str = whereSplit[1];
|
where = whereSplit[0];//参数值
|
}
|
} else {
|
String[] temp_str = where.trim().split("@G@");
|
if (temp_str.length > 1) {
|
where_str = temp_str[1];
|
}
|
where = where.replaceAll("@G@", " and ");// 以|分隔的第二部分是格线需要用的
|
|
}
|
String newWhere=null;
|
if (tabName.indexOf("|") > -1) {
|
String[] t = tabName.split("\\|");
|
String name = "dbo." + t[0];// 18类型
|
String[] strs = where.split("and");
|
StringBuilder str = new StringBuilder();
|
int index = 0;
|
String[] parmterArry = t[1].split(";");
|
for (String toStr : parmterArry) {
|
toStr = toStr.toLowerCase();
|
boolean flag = false;
|
if (index > 0) {
|
str.append(",");
|
}
|
for (String parmter : strs) {
|
parmter = parmter.toLowerCase();
|
if (parmter.trim().indexOf(toStr) > -1) {
|
str.append((parmter.indexOf("=") > -1 ? parmter.split("=") : parmter.split("like"))[1].replaceAll("%20", "").replaceAll("@~", "%"));
|
flag = true;
|
break;
|
}
|
}
|
if (!flag) {// 表示x没匹配的参数值，则为''
|
str.append("''");
|
}
|
index++;
|
}
|
tabName=name + "( " + str.toString() + ")";
|
newWhere=" 1=1" + (where_str != null ?" and " + where_str.replaceAll("@~", "%") : "");
|
} else {
|
String resut = "";
|
if (this.FdFilters != null && !"".equalsIgnoreCase(this.FdFilters)) {
|
|
resut += " and " + this.FdFilters;
|
}
|
if (!"".equalsIgnoreCase(where)) resut += " and " + where.replaceAll("@~", "%");
|
newWhere="1=1 " + resut;
|
}
|
String orgFields=fields;
|
//取42当前录入所在的字段作排序
|
Pattern p = Pattern.compile("(?i)case\\s* (?i)when.*?\\s*\\b(?i)as\\b");// 匹配以case when.....as
|
java.util.regex.Matcher propsMatcher = p.matcher(orgFields);
|
while (propsMatcher.find()) {
|
orgFields=orgFields.replace(propsMatcher.group(),"");
|
}
|
fields=fields.replaceAll("','","'~p~'");
|
List<String> list =Arrays.asList(fields.split(","));
|
fields=list.stream().distinct().collect(Collectors.joining(","));
|
fields=fields.replaceAll("'~p~'","','");
|
String orderby=fields.split(",")[0];
|
|
String newSql=" set nocount on \n" +
|
" declare @Limit int = ? , @Page int = ?,@StartRowNo int ,@EndRowNo int ;\n"
|
+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) from "+tabName+" a where \n" +newWhere
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT "+orgFields+",TotalRowCount FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+orderby+") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ fields+" from "+tabName+" where \n" +newWhere
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
return this.jdbcTemplate.queryForList(newSql,limit,page);
|
}
|
|
@Override
|
public List getDataByAjax(String tabName, String fields, String where) throws DataAccessException {
|
String sql = "";
|
String where_str = null;//针对条件一部分是用作替换参数，@G@后面部分用作sql的where条件
|
if (where.trim().indexOf("@G@") == 0) {
|
where = where.replaceAll("@G@", "");// 以|分隔的第二部分是格线需要用的
|
where_str = where;
|
} else {
|
String[] temp_str = where.trim().split("@G@");
|
if (temp_str.length > 1) {
|
where_str = temp_str[1];
|
}
|
where = where.replaceAll("@G@", " and ");// 以|分隔的第二部分是格线需要用的
|
|
}
|
// "d=3 and c=4 | d=3 and e=5"
|
|
if (tabName.indexOf("|") > -1) {
|
String[] t = tabName.split("\\|");
|
String name = "dbo." + t[0];// 18类型
|
String[] strs = where.split("and");
|
// String temp=t[1];
|
StringBuilder str = new StringBuilder();
|
// String w[] = null;
|
// for (String s : strs) {
|
// w = s.indexOf("=") > -1 ? s.split("=") : s.split("like");
|
// }
|
int index = 0;
|
for (String parmter : t[1].split(";")) {
|
parmter = parmter.toLowerCase();
|
boolean flag = false;
|
if (index > 0) {
|
str.append(",");
|
}
|
for (String s : strs) {
|
if (s.trim().indexOf(parmter) > -1) {
|
str.append((s.indexOf("=") > -1 ? s.split("=") : s.split("like"))[1].replaceAll("@~", "%"));
|
flag = true;
|
break;
|
}
|
}
|
if (!flag) {// 表示x没匹配的参数值，则为''
|
str.append("''");
|
}
|
index++;
|
}
|
sql = " select " + fields + " from " + name + "( " + str.toString() + ") where 1=1 " + (where_str != null ? " and " + where_str.replaceAll("%20", "").replaceAll("@~", "%") : "");
|
} else {
|
String resut = "";
|
if (this.FdFilters != null && !"".equalsIgnoreCase(this.FdFilters)) {
|
|
resut += " and " + this.FdFilters;
|
}
|
if (!"".equalsIgnoreCase(where)) resut += " and " + where.replaceAll("@~", "%");
|
sql = " select " + fields + " from " + tabName + " where 1=1 " + resut;
|
}
|
//增加排序功能
|
return this.jdbcTemplate.queryForList(sql.replaceAll("\\^", " and "));
|
}
|
private int proccesSymbol(String str){
|
if (org.apache.commons.lang3.StringUtils.isBlank(str)) {return -1;}
|
boolean isSymo=str.indexOf("!")>-1?true:false;//存在!号才处理
|
if(isSymo) {
|
Pattern p = Pattern.compile("'.*?'+?");//取出'...'，判断存在!则替换，再找出真正18类型!的位置
|
Matcher m = p.matcher(str);
|
while (m.find()) {//存在
|
if (m.group().indexOf("!") > -1) {
|
str = str.replaceAll(m.group(), m.group().replaceAll("!", "#"));
|
}
|
}
|
return str.indexOf("!");
|
}else {
|
return -1;
|
}
|
}
|
@SuppressWarnings("unchecked")
|
@Override
|
public Page loadAllByFunc(Page page, Map<String, String> env, boolean flg) {
|
String s1 = "";
|
String s2 = "";
|
//处理参数值存在!号的情况，这样会出现可能会有多个!号，需要确定哪一个!号才是正确要处理
|
|
int index = proccesSymbol(page.getWhere());
|
if (index > -1) {// 增加处理18类型有过滤条件的情况
|
String where_str = page.getWhere().substring(index + 1);
|
if (where_str != "" && "1=2".equals(where_str.trim())) {
|
return page;
|
}
|
s1 = page.getWhere().substring(0, index);
|
s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@_asterisk_@", "*").replaceAll("@~", "%").replaceAll("!", " and ") + page.getDataGroup();// 增加数据组权限 12-06-11
|
//替换参数里有会话值的情况
|
s2 =prossFormdatafilters(s2,env);
|
} else {
|
s1 = page.getWhere();
|
s2 = page.getDataGroup().trim().length() > 0 ? " where " + ((page.getDataGroup().trim().startsWith("and")) ? " 1=1 " + page.getDataGroup() : page.getDataGroup()) : "";
|
}
|
String temp = this.getFunctionParm(page.getParms(), s1, env);
|
if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
|
else temp = "()";
|
|
String tableName = page.getTableName();
|
if (tableName.indexOf("dbo.") == -1 || tableName.indexOf("DBO.") == -1) tableName = "dbo." + tableName;
|
// if (flg) {// 为了取页数增加的处理
|
// 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());
|
// List list = this.jdbcTemplate.queryForList(sql);
|
// int num = (Integer) (((Map<String, Object>) list.get(0)).get("p"));
|
// if (num <= page.getPageSize()) page.setTotalPageNum(1);
|
// else page.setTotalPageNum((int) Math.ceil((double) num / page.getPageSize()));
|
// return page;
|
// } else {
|
// 修正为18类型也可以只取指定的记录数,通过新的分页函数
|
page.setTableName(tableName + temp);
|
page.setWhere("".equalsIgnoreCase(s2.replace("where", "")) ? " 1=1 " : s2.replace("where", ""));
|
page.setOrderBy(((page.getOrderBy() == null || page.getOrderBy().length() == 0) ? "" : (page.getOrderBy().toLowerCase().indexOf("order by") > -1) ? page.getOrderBy().replace("order by", "") : page.getOrderBy()));
|
page.setTbCols(this.proccTbCols(page));
|
page.setEnv(env);
|
return this.loadAll(page);
|
// 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());
|
// List list=this.jdbcTemplate.queryForList(sql);
|
// return this.setPageInfo(list, page);
|
// }
|
}
|
private String prossFormdatafilters(String filter, Map<String, String> env) {
|
if ("".equals(filter)) return filter;
|
Pattern p = Pattern.compile("@.*?\\w+");
|
Matcher m = p.matcher(filter);
|
while (m.find()) {//存在
|
filter = filter.replaceAll(m.group(), env.get(m.group().toLowerCase()) + "");
|
}
|
return filter;
|
}
|
public String proccTbCols(Page page) {
|
// digit#1:null,totalmoney2#1:
|
// 组装统计成：cast(sum(isnull(digit,0)) as nvarchar) +''|''+ cast(sum(isnull(totalmoney2,0)) as nvarchar)
|
String tb = page.getTbCols();
|
if (tb == null || "".equalsIgnoreCase(tb)) return "";
|
if (org.apache.commons.codec.binary.Base64.isBase64(tb)) {
|
try {
|
tb = EncodeUtil.base64Decode(tb);
|
tb = tb.replaceAll("%2F", "/").replaceAll("%2B", "+");
|
} catch (UnsupportedEncodingException e) {
|
// tb = tb;
|
} // base64解密所有请求where参数
|
} else {//表示之前已解码，不需要再执行一次，直接返回，主要是用在18类型分页返回，会调用多一次做的处理
|
return tb.replaceAll("%2F", "/").replaceAll("%2B", "+");
|
}
|
StringBuffer sb = new StringBuffer();
|
|
String[] s1 = tb.split(",");
|
for (String s : s1) {
|
String[] s2 = s.split("#");// 分出每个统计的列
|
String[] s3 = s2[1].split(":");// 分离出统计统计和格式，1-汇总 ，2-计数，3-平均，4-百分比，5-自定义,6最大值,7最小值
|
// String format="";
|
// if(s3.length==2){//表示有格式
|
// if(s3[1]!=null&&"null".equalsIgnoreCase(s3[1])){
|
//
|
// }
|
// }
|
// cast(sum(isnull(digit,0)) as nvarchar)
|
String filed = s2[0];
|
|
if (page.getTbExpr() != null && page.getTbExpr().get(s2[0].toLowerCase()) != null
|
&& !"".equalsIgnoreCase(page.getTbExpr().get(s2[0].toLowerCase())))
|
filed = page.getTbExpr().get(s2[0].toLowerCase());
|
//filed=filed.replaceAll("\\/\\*\\*.*?\\*\\*/", "");
|
//replace(rtrim(replace(convert(varchar,sum(cast(isnull(wshamount,0) as decimal(38,18)) ) ),'0',' ')),' ','0')
|
switch (Integer.parseInt(s3[0])) {
|
case 1:
|
//sb.append(" cast(sum(isnull(").append(filed).append(",0)) as nvarchar )").append("+'#p#'+");
|
sb.append(" replace(rtrim(replace(convert(varchar(100),sum(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
case 2:
|
sb.append(" cast(count(isnull(").append(filed).append(",0)) as nvarchar )").append("+'#p#'+");
|
//sb.append(" replace(rtrim(replace(convert(varchar,count(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
case 3:
|
//sb.append(" cast(avg(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
|
sb.append(" replace(rtrim(replace(convert(varchar(100),avg(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
case 6:
|
//sb.append(" cast(max(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
|
sb.append(" replace(rtrim(replace(convert(varchar(100),max(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
case 7:
|
//sb.append(" cast(min(isnull(").append(filed).append(",0)) as nvarchar)").append("+'#p#'+");
|
sb.append(" replace(rtrim(replace(convert(varchar(100),min(cast(isnull(").append(filed).append(",0) as decimal(38,18)) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
case 4:
|
//sb.append(" cast((sum(isnull(").append(filed).append(",0))*100.00) as nvarchar)").append("+'#p#'+");
|
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#'+");
|
break;
|
case 5:// 需要把后缀转成中缀传给sql查询 !format2 digit price * othermoney + //add 2018-4-14
|
if (s3[1] == null || s3[1].length() == 0) throw new ApplicationException(filed + "-没有设置自定义公式");
|
String str = "";
|
String newString = s3[1].trim().replaceAll("@p@", ",");
|
if (newString.startsWith("!format")) {
|
str = newString.replaceAll("!\\w+?\\b", "");//去掉!format2取得digit price * othermoney +
|
str = PostfixExpression.midToPost(str.trim(), "sum");
|
|
} else if (newString.startsWith("case when")) {
|
str = newString;
|
} else {
|
throw new ApplicationException(filed + "-汇总自定义公式【" +newString + "】需要以【!format或case when】格式开始");
|
}
|
//处理被除数为0的情况
|
String divisor = "";
|
if (str.indexOf("/") > 0) {//有除数
|
Pattern p = Pattern.compile("\\/\\w*[^\\+\\-\\*\\/]+");
|
Matcher m = p.matcher(str);
|
while (m.find()) {
|
String value = m.group();
|
if (value.indexOf("(") >= 0) {//有括号"("表示需要取到")"为止
|
// 1种情况，括号刚好匹配，： / sum( isnull( totalmoney2, 0 )) else 0 end
|
//第2种情况，)多出来，是匹配其他地方的：/sum(isnull(digit,0)),4)))) else 0 end
|
//需要做判断
|
Pattern p2 = Pattern.compile("[\\(\\)]");
|
Matcher m2 = p2.matcher(value);
|
int leftIndex=0;//左括号次数
|
int rightIndex=0;//右括号次数
|
while (m2.find()) {
|
if("(".equals(m2.group())){
|
leftIndex++;
|
}else {
|
rightIndex++;
|
}
|
|
}
|
if(rightIndex>=leftIndex){
|
for(int i=0;i<leftIndex;i++){//把匹配的括号替换成#
|
value=value.replaceFirst("\\)","#");
|
}
|
}
|
value = value.substring(0, value.lastIndexOf("#")+1).replaceAll("#",")");//转回来括号形式
|
} else {//后面还有字符的情况，因为没括号的情况下第二空格就代表了结束
|
// ： / totalmoney2 else 0 end
|
String[] strings=value.split("\\s+");
|
if("/".equalsIgnoreCase(strings[0].trim())&&strings.length>1){
|
value=strings[1];
|
}else{
|
value=strings[0];
|
}
|
}
|
divisor += value.replace("/", "") + ",";
|
}
|
}
|
if (StringUtils.isNotBlank(divisor)) {
|
str = " case when 0 in(" + divisor.substring(0, divisor.length() - 1) + ") then 0 else " + str + " end ";
|
}
|
str = PostfixExpression.replaceDotByrecover(str);//还原运算符
|
sb.append(" replace(rtrim(replace(convert(varchar(100),cast(isnull(").append(str).append(",0) as decimal(38,18) ) ),'0',' ')),' ','0')").append("+'#p#'+");
|
break;
|
default:
|
break;
|
}
|
|
}
|
|
return sb.length() > 0 ? sb.toString().substring(0, sb.lastIndexOf("+'#p#'+")) : "";
|
}
|
|
//后缀转中缀
|
private String postfix_to_infix(String rpn) {
|
if (rpn == null || "".equalsIgnoreCase(rpn)) return "";
|
List<String> expr = java.util.Arrays.asList(rpn.split(" "));
|
Stack<String> s = new Stack<String>();
|
for (String str : expr) {
|
// a number
|
if ("".equals(str)) continue;
|
if (!str.isEmpty() &&
|
(
|
!"+".equalsIgnoreCase(str) &&
|
!"-".equalsIgnoreCase(str) &&
|
!"*".equalsIgnoreCase(str) &&
|
!"/".equalsIgnoreCase(str)
|
)) {
|
s.push(str);
|
}
|
// an operator
|
else {
|
String second = s.peek();
|
s.pop();
|
String first = s.peek();
|
s.pop();
|
s.push(first + str + second);
|
}
|
}
|
return s.peek();
|
}
|
|
public Page loadAllByFunc_for(Page page) {
|
String s1 = "";
|
String s2 = "";
|
int index = proccesSymbol(page.getWhere());
|
if (index > -1) {// 增加处理18类型有过滤条件的情况
|
s1 = page.getWhere().substring(0, index);
|
s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@~", "%") + page.getDataGroup();// 增加数据组权限 12-06-11
|
|
} else {
|
s1 = page.getWhere();
|
s2 = " where 1=1 " + page.getDataGroup();
|
}
|
String temp = this.getFunctionParm_for(s1);
|
if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
|
String tableName = page.getTableName();
|
if (tableName.indexOf("dbo.") == -1) tableName = "dbo." + tableName;
|
|
String sql = " select " + page.getSql() + " from " + tableName + temp + s2 + ((page.getOrderBy() == null || page.getOrderBy().length() == 0) ? "" : " order by " + page.getOrderBy());
|
List list = this.jdbcTemplate.queryForList(sql);
|
page.setData(list);
|
return page;
|
}
|
|
@Override
|
public Page loadAllByPROC(Page page,Map<String, String> env, boolean flg) {
|
String s1 = "";
|
String s2 = "";
|
int index = page.getWhere().indexOf("!");
|
if (index > -1) {// 增加处理19类型有过滤条件的情况
|
String where_str = page.getWhere().substring(index + 1);
|
if (where_str != "" && "1=2".equals(where_str.trim())) {
|
return page;
|
}
|
s1 = page.getWhere().substring(0, index);
|
s2 = " where " + page.getWhere().substring(index + 1).replaceAll("@_asterisk_@", "*").replaceAll("@~", "%").replaceAll("!", " and ") + page.getDataGroup();// 增加数据组权限 12-06-11
|
//处理可能存在编码了的内容,@_xxxx_@
|
} else {
|
s1 = page.getWhere();
|
s2 = page.getDataGroup().trim().length() > 0 ? " where " + ((page.getDataGroup().trim().startsWith("and")) ? " 1=1 " + page.getDataGroup() : page.getDataGroup()) : "";
|
}
|
String temp = this.getProcParameters( s1, env);
|
// if (!"".equalsIgnoreCase(temp)) temp = "(" + temp + ")";
|
//else temp = "()";
|
|
String tableName = page.getTableName();
|
//if (tableName.indexOf("dbo.") == -1 || tableName.indexOf("DBO.") == -1) tableName = "dbo." + tableName;
|
page.setTableName("exec "+tableName + " "+temp);
|
page.setWhere("".equalsIgnoreCase(s2.replace("where", "")) ? " 1=1 " : s2.replace("where", ""));
|
page.setOrderBy(((page.getOrderBy() == null || page.getOrderBy().length() == 0) ? "" : (page.getOrderBy().toLowerCase().indexOf("order by") > -1) ? page.getOrderBy().replace("order by", "") : page.getOrderBy()));
|
page.setTbCols(this.proccTbCols(page));
|
page.setEnv(env);
|
return this.loadAll(page);
|
|
|
}
|
|
private MapSqlParameterSource getParameterSource(String str) {
|
MapSqlParameterSource par = new MapSqlParameterSource();
|
String[] temp = str.split("and");
|
for (String s : temp) {
|
String[] t = s.split("=");
|
par.addValue(t[0].trim(), t.length == 1 ? "" : t[1].replaceAll("'", ""));
|
}
|
return par;
|
}
|
private String getProcParameters(String str,Map<String, String> env) {
|
List<String> par = new ArrayList<>();
|
String[] temp = str.split("and");
|
for (String s : temp) {
|
String[] t = s.split("=");
|
//取参数值
|
String value=t.length == 1 ? "" : t[1];
|
if(!value.matches("\\d")&&!value.startsWith("'")){
|
value="'"+value+"'";
|
}
|
par.add( value);
|
}
|
return String.join(",",par);
|
}
|
private String getFunctionParm_for(String st) {
|
if ("".equalsIgnoreCase(st)) return "";
|
StringBuilder sb = new StringBuilder();
|
String[] temp = st.split(",");// 函数后面列出的参数，有可能包括数字，字符及参数名称
|
int i = 0;
|
for (String str : temp) {
|
boolean fl = false;
|
int index = 0;
|
String cv1 = "";
|
String cv2 = "";
|
|
index = str.indexOf("=");
|
|
if (index > 0) {
|
cv2 = str.substring(index + 1);
|
if (i != 0) sb.append(",");
|
sb.append(cv2);
|
} else {
|
if (i != 0) sb.append(",");
|
sb.append(str);
|
}
|
i++;
|
}
|
return sb.toString();
|
}
|
|
public String getFunctionParm(String st, String parms, Map<String, String> env) {
|
parms = JOSNUtils.prossBase64(parms);
|
if ("".equalsIgnoreCase(st)) return "";
|
if ("".equalsIgnoreCase(parms)) return "";
|
if (parms.lastIndexOf("!1=2") > 0) parms = parms.substring(0, parms.length() - 4);//解决在18类型设置不是自动打开时直接导出会出错
|
StringBuilder sb = new StringBuilder();
|
String[] parmsNames = st.split(";");// 函数后面列出的参数，有可能包括数字，字符及参数名称
|
int i = 0;
|
String[] parmValues = parms.split("\\s+?and\\s+?");
|
for (String parameterName : parmsNames) {
|
if (parameterName.matches("\\d")) {// 只是数字
|
if (i != 0) sb.append(",");
|
sb.append(parameterName);
|
continue;
|
}
|
boolean isfound = false;
|
for (String value : parmValues) {
|
int index = 0;
|
//value : myendday='2019-12-12'
|
String leftValue = "";//myendday
|
String rightValue = "";//'2019-12-12'
|
try {
|
index = value.indexOf("=");
|
} catch (StringIndexOutOfBoundsException e) {
|
index = 0;
|
}
|
if (index >= 0) {
|
leftValue = value.substring(0, index);
|
}
|
rightValue = value.substring(index + 1).trim();
|
if (parameterName.trim().equalsIgnoreCase(leftValue.replaceAll("\\s", ""))) {//匹配到参数
|
if (i != 0) sb.append(",");
|
sb.append("'null'".equalsIgnoreCase(rightValue.replaceAll("\\s", "")) ? null : (!rightValue.trim().startsWith("'")||"''".equalsIgnoreCase(rightValue))?rightValue:GridUtils.prossSqlParm(rightValue.substring(1,rightValue.length()-1)));
|
i++;
|
isfound = true;
|
break;
|
} else if (parameterName.indexOf("@") > -1) {// 需要从session取值 @usercode|cltcode
|
if (parameterName.indexOf("=") > 0) {
|
String[] sts = parameterName.split("=");
|
|
if (sts != null && sts.length > 1 && sts[1].trim().equalsIgnoreCase(leftValue.replaceAll("\\s", ""))) {
|
if (i != 0) sb.append(",");
|
String vl = env.get(sts[0]);
|
if (vl == null)
|
sb.append("null");
|
else
|
sb.append(GridUtils.prossSqlParm(vl));
|
i++;
|
isfound = true;
|
break;
|
}
|
} else {
|
if (i != 0) sb.append(",");
|
String vl = env.get(parameterName);
|
if (vl == null)
|
sb.append("null");
|
else
|
sb.append(GridUtils.prossSqlParm(vl));
|
i++;
|
isfound = true;
|
break;
|
}
|
}
|
}
|
if (!isfound) {
|
if (i != 0) sb.append(",");
|
sb.append("'").append(parameterName).append("'");
|
i++;
|
}
|
}
|
return sb.toString();
|
}
|
|
@Override
|
public long getMaxID(String tableName, String doccode) {
|
if ("''".equalsIgnoreCase("''")) return 0L;
|
Long id = this.jdbcTemplate.queryForObject("select max(docitem) from " + tableName + " where " + doccode, Long.class);
|
if (id == null) return 0L;
|
else return id;
|
}
|
|
|
@Override
|
public String getRowID() {
|
String sql = "{call getXXXX(?)}";
|
Map<?, ?> map = (Map<?, ?>) this.jdbcTemplate.execute(sql, new CallableStatementCallback<Object>() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
cs.registerOutParameter(1, Types.VARCHAR);// 输出参数
|
cs.execute();
|
Map<String, String> map = new HashMap<String, String>();
|
map.put("rowid", cs.getString(1));
|
return map;
|
}
|
});
|
return (String) map.get("rowid");
|
|
}
|
|
public List<Map<String, Object>> getSaveProcGroup(int formid) {
|
return this.jdbcTemplate.queryForList(SAVE_PROC, new Object[]{formid});
|
}
|
|
public List<String> getDelProcGroup(int groupid) {
|
return this.jdbcTemplate.queryForList(DEL_PROC, String.class,groupid);
|
|
}
|
|
@Override
|
public List<TranBean> getTranList(int groupid) {
|
List<TranBean> list = new ArrayList<TranBean>();
|
SqlRowSet set = this.jdbcTemplate.queryForRowSet(TRANS_GROUP, new Object[]{groupid});
|
while (set.next()) {
|
TranBean tb = new TranBean();
|
tb.setReloaddata(set.getInt("reloaddata"));
|
tb.setDatatable(set.getString("datatable"));
|
tb.setFilterstring(set.getString("filterstring"));
|
tb.setUpdatesql(set.getString("updatesql"));
|
tb.setUpdatesql2(set.getString("updatesql2"));
|
tb.setBeforeSQlCheckView(set.getString("BeforeSQlCheckView"));
|
tb.setAfterSQlCheckView(set.getString("AfterSQlCheckView"));
|
tb.setBeforeSQlCheckRaiseMsg(set.getString("BeforeSQlCheckRaiseMsg"));
|
tb.setAfterSQlCheckRaiseMsg(set.getString("AfterSQlCheckRaiseMsg"));
|
|
list.add(tb);
|
}
|
return list;
|
}
|
|
|
/**
|
* 执行过账类型，业务逻辑功能
|
*
|
* @param tranid --业务号
|
**/
|
public String doTarnType(int tranid, String doccode, String tableName, int formid, String postCode, String postname, HttpSession session, List<SqlInfo> sqlInfo) throws DataAccessException {
|
List<String> sqllist = new ArrayList<String>();// 全部的sql语句
|
|
boolean isflag = false;// 标记是新单且是直接确认的情况,app使用
|
if ("".equalsIgnoreCase(doccode)) {
|
doccode = "@newDoccode";
|
isflag = true;
|
} else{
|
doccode = "'" + doccode + "'";
|
}
|
//int n = 0;
|
StringBuffer tran_sql = new StringBuffer();
|
for (SqlInfo sq : sqlInfo) {
|
tran_sql.append(sq.getSql());
|
tran_sql.append(updateDocStatus(doccode, tableName, formid, postCode, postname));
|
|
// 再执行业务逻辑
|
List<TranBean> list = this.getTranList(tranid);
|
for (TranBean tb : list) {
|
String index = RandomStringUtils.randomNumeric(6);//转为字符串，再转为数值 ,保证不会出现相同的数值， 如："1"+"5"=15
|
StringBuilder sb = new StringBuilder();
|
String st = "\n set nocount on; select * from " + tb.getDatatable() + " where 1=2 \n";
|
SqlRowSet row = this.jdbcTemplate.queryForRowSet(st);
|
// ------------组装成游标方式处理
|
// -----定义变量，从设置中的sql取得，且用元数据取得相关的数据类型
|
List<String> parms = new ArrayList<String>();// 保存需要查询时需要的列名
|
if(org.apache.commons.lang3.StringUtils.isBlank(tb.getUpdatesql())){
|
throw new ApplicationException("请在9859维护【检查SQL】参数设置");
|
}
|
parms = this.getParm(tb.getUpdatesql(), parms, row);
|
parms = this.getParm(tb.getUpdatesql2(), parms, row);
|
parms = this.getParm(tb.getBeforeSQlCheckView(), parms, row);
|
parms = this.getParm(tb.getBeforeSQlCheckRaiseMsg(), parms, row);
|
parms = this.getParm(tb.getAfterSQlCheckView(), parms, row);
|
parms = this.getParm(tb.getAfterSQlCheckRaiseMsg(), parms, row);
|
|
Map<String, String> map = new HashMap<String, String>();// 保存列变量
|
HashMap<String, String> sessionClone = (HashMap) GridUtils.getSessionAttributes(session, formid, doccode, false).clone();//clone一份出来，避免串数据
|
String[] updatesql = prossParm(row, tb.getUpdatesql(), parms, index, map, sessionClone, tb.getDatatable(), 0);
|
String[] updatesql2 = prossParm(row, tb.getUpdatesql2(), parms, index, map, sessionClone, tb.getDatatable(), 0);
|
String[] beforeView = prossParm(row, tb.getBeforeSQlCheckView(), parms, index, map, sessionClone, tb.getDatatable(), 0);
|
String[] beforeViewMsg = prossParm(row, tb.getBeforeSQlCheckRaiseMsg(), parms, index, map, sessionClone, tb.getDatatable(), 1);
|
String[] afterView = prossParm(row, tb.getAfterSQlCheckView(), parms, index, map, sessionClone, tb.getDatatable(), 0);
|
String[] afterViewMsg = prossParm(row, tb.getAfterSQlCheckRaiseMsg(), parms, index, map, sessionClone, tb.getDatatable(), 1);
|
// --定义变量
|
for (Entry<String, String> entry : map.entrySet()) {
|
|
sb.append("\n " + entry.getValue());
|
}
|
sb.append("\n declare mycurPostCur cursor for \n");
|
// 提取设置中的字段名(basedigit,plantid,matcode),拼装下面这条sql
|
sb.append("\n select " + this.format(updatesql[1]) + " from " + tb.getDatatable() + " where doccode=" + doccode + ((tb.getFilterstring() != null && tb.getFilterstring().length() > 0) ? " and " + tb.getFilterstring() : ""));
|
|
sb.append("\n open mycurPostCur \n");
|
sb.append("\n fetch next from mycurPostCur into \n");
|
sb.append(this.format(updatesql[2]));// 变量名
|
sb.append("\n while @@FETCH_STATUS = 0 \n begin \n");
|
|
// -----------star
|
// 1------更新前检查条件
|
if (tb.getBeforeSQlCheckView() != null && !"".equalsIgnoreCase(tb.getBeforeSQlCheckView())) {
|
sb.append("\n if exists(" + beforeView[0] + ")\n");
|
sb.append("\n begin ");
|
sb.append("\n select @ErrMsgs='【'+" + beforeViewMsg[0] + "+'】'");
|
sb.append("\n close mycurPostCur");
|
sb.append("\n deallocate mycurPostCur");
|
sb.append("\n raiserror(@ErrMsgs,16,1) return ");
|
sb.append("\n end \n ");
|
}
|
|
// 2-------update sql,分二种情况(过程和sql)
|
String sql = updatesql[0];
|
boolean flg = false;
|
if (sql.startsWith("!")) {
|
sql = " \n exec " + sql.replace("!", "");
|
flg = true;
|
}
|
sb.append("\n begin \n" + sql)
|
.append("\n select @myrowcount= @@rowcount , @myerror = @@error \n")
|
.append("\n if @myerror <>0 \n")
|
.append("\n begin ")
|
.append("\n select @ErrMsgs='" + updatesql[0].replaceAll("'", "''") + "【'+" + updatesql[3] + "+'】出错' \n");
|
if (flg && !"".equalsIgnoreCase(updatesql2[0])) {////表示前一个是过程调用
|
String s = updatesql2[0].replace("!", "");
|
if (s.trim().startsWith("raismyerror")) {//存在加上引号
|
String str = s.trim().replace("raismyerror", "");
|
s = s.replaceAll(str, " '" + str + "'");
|
}
|
sb.append("\n exec " + s);
|
} else {//为空则用raiserror输出
|
sb.append("\n close mycurPostCur \n");
|
sb.append("\n deallocate mycurPostCur \n");
|
sb.append("\n raiserror(@ErrMsgs,16,1) return \n");
|
}
|
sb.append("\n end \n");
|
// 3-------update2 sql
|
sql = updatesql2[0];
|
if (!"".equalsIgnoreCase(sql)) {
|
if (!flg && !"select 1".equalsIgnoreCase(sql.trim())) {//"select 1"不执行
|
if (sql.startsWith("!")) {
|
String s = updatesql2[0].replace("!", "");
|
if (s.trim().startsWith("raismyerror")) {//存在加上引号
|
String str = s.trim().replace("raismyerror", "");
|
s = s.replaceAll(str, " '" + str + "'");
|
}
|
sql = "\n exec " + s + " \n";
|
}
|
sb.append("\n if @myrowcount <=0 \n begin \n")//上面sql执行的更新不成功，则继续执行下面，成功则跳过
|
.append(sql)
|
.append("\n select @myrowcount = @@rowcount , @myerror = @@error \n");
|
if (!updatesql2[0].startsWith("!")) {
|
sb.append("\n if @myerror <>0 \n")
|
.append("\n begin \n")
|
.append("\n select @ErrMsgs='" + updatesql2[0].replaceAll("'", "''") + "【'+" + updatesql2[3] + "+'】出错' \n")
|
.append("\n close mycurPostCur \n")
|
.append("\n deallocate mycurPostCur \n")
|
.append("\n raiserror(@ErrMsgs,16,1) return \n")
|
.append("\n end \n");
|
}
|
sb.append("\n end \n");
|
}
|
}
|
sb.append(" \n end \n");
|
|
// 4------更新后检查条件
|
if (tb.getAfterSQlCheckView() != null && !"".equalsIgnoreCase(tb.getAfterSQlCheckView())) {
|
sb.append("\n if exists(" + afterView[0] + ")")
|
.append("\n begin \n")
|
.append("\n select @ErrMsgs='【'+" + afterViewMsg[0] + "+'】'")
|
.append("\n close mycurPostCur \n");
|
sb.append("\n deallocate mycurPostCur \n" );
|
sb.append("\n raiserror(@ErrMsgs,16,1) return \n")
|
.append("\n end \n");
|
}
|
// ------------end
|
sb.append("\n fetch next from mycurPostCur into \n");
|
sb.append(this.format(updatesql[2]));
|
sb.append("\n end \n");
|
sb.append("\n close mycurPostCur \n");
|
sb.append("\n deallocate mycurPostCur \n");
|
if (sb.length() > 0){
|
sqllist.add(sb.toString());
|
}
|
}
|
for (String str : sqllist) {
|
tran_sql.append(str);
|
}
|
}
|
if (tran_sql.toString().length() > 0) {
|
if (isflag) {
|
String str = "\n insert into @returnTable (docCode,rowid,detailRowid,Memo,LinkDocInfo) values(isnull(@newDoccode,''),'','','','')\n" +
|
" select docCode,rowid,detailRowid,Memo,LinkDocInfo from @returnTable \n";//设置返回值
|
Map map = this.doSave(tran_sql.toString() + str);
|
return GridUtils.prossRowSetDataType_String(map, "docCode");
|
} else {
|
this.doSaveNoResult(tran_sql.toString());// 最终提交
|
return "";
|
}
|
}
|
return "";
|
}
|
|
/**
|
* 去掉最后的逗号
|
*/
|
private String format(String string) {
|
return (string.lastIndexOf(",") == string.length() - 1) ? string.substring(0, string.length() - 1) : string;
|
}
|
|
/**
|
* 取得不重复的参数
|
**/
|
@Override
|
public List<String> getParm(String sql, List<String> temp, SqlRowSet row) {
|
List<String> sq2 = new ArrayList<String>();
|
if (sql == null || "".equalsIgnoreCase(sql)) return temp;// 直接返回
|
if (sql.startsWith("!")) {
|
List<String> pro = this.getProcString(sql);
|
for (int i = 1; i < pro.size(); i++) {
|
boolean fg = pro.get(i).matches("'.*?'");// 存在''号的情况
|
boolean fgno = pro.get(i).matches("'\\s?'");// ''的情况
|
String fid = pro.get(i);
|
if (fg && !fgno) {
|
fid = fid.substring(1, fid.length() - 1);// 取引号里面的值
|
}
|
String[] names = row.getMetaData().getColumnNames();// 取出表定义的所有列名
|
boolean flg = false;
|
for (String s : names) {
|
if (s.equalsIgnoreCase(fid)) {
|
flg = true;
|
break;
|
}
|
|
}
|
// 当前表所有的列名称，需要查找当前的字段名是否存在，不存在则不需要定义列变量输出
|
if (flg) {
|
sq2.add(fid);
|
}
|
}
|
} else {
|
sq2 = this.getString(sql);
|
}
|
if (temp.size() > 0) {// 不是第一次调用
|
temp.removeAll(sq2);
|
sq2.addAll(temp);
|
}
|
return sq2;
|
// ---
|
}
|
|
@Override
|
public String[] prossParm(SqlRowSet row, String temp, List<String> sq, String index, Map<String, String> map, Map<String, String> sessionClone, String tableName, int type) {
|
String colName = "";// 保存列名称
|
String columVar = "";// 保存列变量
|
StringBuilder errorMessage = new StringBuilder();// 保存出错时需要显示的内容，需要在这里组装变量
|
boolean flg = true;//ture表示匹配不了结尾的情况，需要加上单引号
|
if (temp == null || "".equalsIgnoreCase(temp)) return new String[]{"", "", "", ""};
|
temp = temp.toLowerCase().replaceAll("<br>", "");
|
int indexd = 0;
|
//List<String> colsParm=new ArrayList<>();
|
for (String filedName : sq) {
|
//替换&...&, @直接取会话值
|
String typename = this.getTypeName(row.getMetaData(), filedName.replaceAll("&", ""));
|
if (typename == null) {
|
throw new ApplicationException(temp + "里面的列【" + filedName + "】在" + tableName + "表中不存在【" + filedName.replaceAll("&", "") + "】");
|
}
|
if (!map.containsKey(filedName.replaceAll("&", ""))) {// 生成公共的列名，变量
|
map.put(filedName.replaceAll("&", ""), "\n declare @" + filedName.replaceAll("&", "") + "_" + index + " " + typename);// 生成
|
colName += filedName.replaceAll("&", "") + ",";
|
columVar += "@" + filedName.replaceAll("&", "") + "_" + index + ",";
|
}
|
|
if (temp.startsWith("!")) {//存储过程
|
//!sp_matdocvaluation doccode,rowid,matcode,batchcode,plantid,stcode,basedigit,netmoney,itemtype,2,periodid
|
List<String> pro = getProcString(temp);//pro[0] 是名称
|
|
temp = temp.replaceAll(";", ",");
|
for (int i = 1; i < pro.size(); i++) {//取会话值
|
try {
|
String name = pro.get(i).replaceAll("'", "");//去除单引号
|
if (name.indexOf("@") >= 0) {
|
temp = temp.replaceAll(name.toLowerCase(), sessionClone.get(name) + "");
|
if (errorMessage.length() == 0) {
|
errorMessage.append("'").append(sessionClone.get(name.toLowerCase())).append("'+");
|
} else {
|
errorMessage.append("',").append(sessionClone.get(name.toLowerCase())).append("'+");
|
}
|
}
|
|
} catch (Exception e) {
|
throw new ApplicationException(e.getMessage());
|
}
|
}
|
//替换每个参数值
|
if ((indexd + 1) == sq.size()) {//到结尾
|
if (temp.indexOf(",") < 0 && temp.endsWith(filedName)) {//只有一个参数且是sq.size也是等于1的情况
|
temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用【】代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
} else {//最后
|
if(org.apache.commons.lang3.StringUtils.contains(temp,","+filedName.trim())){
|
temp = temp.replace("," + filedName, ",【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
}else if(org.apache.commons.lang3.StringUtils.contains(temp,filedName.trim()+",")){
|
temp = temp.replace( filedName+",", "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index+",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
}else{
|
temp = temp.replace( filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
}
|
}
|
} else if (indexd == 0) {//首部
|
if (temp.indexOf(",") < 0 && temp.endsWith(filedName)) {//第一个就结束替换，且sq.size不等于1的情况
|
temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
} else {
|
temp = temp.replace(filedName + ",", "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + ",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
}
|
} else {//中间
|
temp = temp.replace("," + filedName + ",", ",【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + ",");//修改为用#代替，当处理完返回才替换成@, by danaus 2020/4/14 16:18
|
}
|
} else {//普通sql
|
|
if (type == 1) {//处理检查条件的sql语句
|
if (filedName.indexOf("&") == 0) {//普通sql只需要替换有&..&的参数
|
|
if (temp.indexOf("'" + filedName + "'") > 0) {//表示有单引号
|
filedName = "'" + filedName + "'";
|
}
|
if ((indexd + 1) == sq.size()) {//到结尾
|
if (temp.trim().endsWith(filedName)) {//在结尾
|
temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))");
|
flg = false;
|
} else {
|
temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'") + "'";
|
}
|
} else if (indexd == 0) {//首部
|
if (temp.trim().startsWith(filedName))//在开头
|
temp = temp.replace(filedName, "cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
|
else {
|
temp = "'" + temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
|
}
|
} else {
|
temp = temp.replace(filedName, "'+cast(【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index + " as nvarchar(500))+'");
|
}
|
|
|
}
|
} else {
|
if (filedName.indexOf("&") == 0) {//普通sql只需要替换有&..&的参数
|
if (temp.indexOf("'" + filedName + "'") > 0) {//表示有单引号
|
filedName = "'" + filedName + "'";
|
}
|
temp = temp.replace(filedName, "【】" + filedName.replaceAll("'", "").replaceAll("&", "") + "_" + index);
|
}
|
}
|
|
}
|
// 出错信息
|
if (errorMessage.length() == 0) {
|
errorMessage.append("'" + filedName.replaceAll("'", "").replaceAll("&", "") + "='+cast(@").append(filedName.replaceAll("'", "").replaceAll("&", "")).append("_").append(index).append(" as nvarchar(500))+");
|
} else {//','
|
errorMessage.append("','+" + "'" + filedName.replaceAll("'", "").replaceAll("&", "") + "='+cast(@").append(filedName.replaceAll("'", "").replaceAll("&", "")).append("_").append(index).append(" as nvarchar(500))+");
|
}
|
indexd++;
|
}
|
if (type == 1 && flg) {
|
if (!temp.endsWith("'"))
|
temp = temp + "'";
|
}
|
temp = temp.replaceAll("【】", "@");
|
return new String[]{temp, colName, columVar, errorMessage.toString()};
|
}
|
|
/**
|
* 根据列名查找列的数据类型是否需要去掉双引号， 因为客户端传过来的json经过转换都加上双引号，但到写入数据库时需要区分数据类型 <br>
|
* 1 --表示数值型<br>
|
* 2---表示text,ntext,image之类<br>
|
* 3---字符 4---日期
|
*/
|
private int getType(SqlRowSetMetaData md, String id) {
|
int s = 3;
|
for (int i = 1; i <= md.getColumnCount(); i++) {
|
if (id.equalsIgnoreCase(md.getColumnName(i))) {
|
s = md.getColumnType(i);
|
break;
|
}
|
}
|
switch (s) {
|
case Types.TINYINT:
|
case Types.INTEGER:
|
case Types.DECIMAL:
|
case Types.DOUBLE:
|
case Types.FLOAT:
|
case Types.NUMERIC:
|
case Types.BIGINT:
|
case Types.REAL:
|
case Types.SMALLINT:
|
case 0:
|
return 1;
|
case Types.BINARY:
|
case Types.BLOB:
|
case Types.CLOB:
|
case Types.NCLOB:
|
return 2;
|
case Types.DATE:
|
case Types.TIME:
|
case Types.TIMESTAMP:
|
return 4;// 日期
|
case Types.BIT:
|
return 5;
|
case Types.LONGNVARCHAR:// varchar(MAX)
|
case Types.LONGVARCHAR:
|
return 3;
|
default:
|
return 3;
|
}
|
}
|
|
/**
|
* 取得列的数据类型定义
|
*/
|
private String getTypeName(SqlRowSetMetaData md, String id) {
|
// int s = 3;
|
// int size=0;
|
String name = null;
|
for (int i = 1; i <= md.getColumnCount(); i++) {
|
if (id.equalsIgnoreCase(md.getColumnName(i))) {
|
if ("varchar".equalsIgnoreCase(md.getColumnTypeName(i)) || "nvarchar".equalsIgnoreCase(md.getColumnTypeName(i)) || "char".equalsIgnoreCase(md.getColumnTypeName(i)) || "nchar".equalsIgnoreCase(md.getColumnTypeName(i)))
|
name = md.getColumnTypeName(i) + "(" + md.getColumnDisplaySize(i) + ")";
|
else name = md.getColumnTypeName(i);
|
}
|
}
|
return name;
|
// switch (s) {
|
// case Types.TINYINT:
|
// return "tinyint";
|
// case Types.INTEGER:
|
// return "int";
|
// case Types.DECIMAL:
|
// return "money";
|
// case Types.DOUBLE:
|
// return "double";
|
// case Types.FLOAT:
|
// return "float";
|
// case Types.NUMERIC:
|
// return "numeric";
|
// case Types.BIGINT:
|
// return "bigint";
|
// case Types.REAL:
|
// return "real";
|
// case Types.SMALLINT:
|
// return "smallint";
|
// case Types.BINARY:
|
// return "smallint";
|
// case Types.BLOB:
|
// return "smallint";
|
// case Types.CLOB:
|
// return "smallint";
|
// case Types.NCLOB:
|
// return "smallint";
|
// case Types.CHAR:
|
// return "char";
|
// case Types.DATE:
|
// return "date";
|
// case Types.TIME:
|
// return "time";
|
// case Types.TIMESTAMP:
|
// return "datetime";
|
//
|
// case Types.BIT:
|
// return "bit";
|
// case Types.LONGNVARCHAR://varchar(MAX)
|
// return "nvarchar(max)";
|
// case Types.LONGVARCHAR:
|
// return "varchar(max)";
|
// case Types.VARCHAR:
|
// return "varchar";
|
// case Types.NVARCHAR:
|
// return "nvarchar";
|
// default:
|
// return "varchar";
|
// }
|
}
|
|
public Map doSave(String sql) throws DataAccessException {
|
|
return this.jdbcTemplate.queryForMap(sql);
|
}
|
|
public void doSaveNoResult(String sql) throws DataAccessException {
|
|
//this.jdbcTemplate.execute(sql);
|
//execute执行有时候会出现出错不会回滚，造成事务失效，所以换成下面这种方式
|
this.doBaseExecute(sql);
|
}
|
|
private String updateDocStatus(String doccode, String tabname, int formid, String postCode, String postname) {
|
// 修改单据为确认后状态值
|
String sql = "\n select @blclosed=blclosed,@refcode=refcode from " + tabname + " where doccode=" + doccode + " \n"
|
+ " set @myrowcount = @@rowcount \n"
|
+ " set @myerror = @@error \n"
|
+ " if @myrowcount = 0 begin raiserror('%s单号不存在',16,1," + doccode + ") return end\n" +
|
" if @blclosed=-1 begin " + " if isnull(@refcode,'')='' begin raiserror('冲销单的引用单号(refcode)为空',16,1) return end \n"
|
+ " update " + tabname + " set cleardoccode=" + doccode + " ,blclosed=1 where doccode=@refcode \n" +
|
// " set @flg_"+num+"=-1\n"+
|
" end\n" +
|
" select @predocstatus=predocstatus,@postdocstatus=postdocstatus from gform where formid=" + formid + "\n"
|
+ " set @myrowcount = @@rowcount \n"
|
+ " set @myerror = @@error \n"
|
+ " if @myrowcount = 0 begin raiserror('%s 功能号不存在',16,1," + formid + ") return end \n" +
|
|
" update " + tabname + " set postdate=getdate(),postCode='" + postCode + "',postname='" + postname + "' \n"
|
+ " where doccode=" + doccode + " and DocStatus=@predocstatus\n"
|
+ " set @myrowcount = @@rowcount \n"
|
+ " set @myerror = @@error \n"
|
+ " if @myrowcount = 0 begin raiserror('%s-更新出错！',16,1," + doccode + ") return end\n "
|
|
+" if not exists(select 1 from t111630 where DocCode = '"+formid+"') \n"
|
+ " begin \n"
|
+" update a set DocStatus = @PostDocStatus from "+tabname+" a where a.DocCode = "+doccode+" and a.DocStatus <> isnull(@PostDocStatus,0) \n"
|
+" end \n";
|
// 期间是否已关闭，关闭则不能再确认
|
sql += "\n exec checkPeriodHasOpen @doccode=" + doccode + ", @formid = " + formid + " ,@PeriodState = @PeriodState output \n"
|
+ " if @PeriodState = 2 begin raiserror('当前单据%s所属的期间已关闭，请与财务人员联系',16,1," + doccode + ") return end \n"
|
+ " if @PeriodState = 0 begin raiserror('当前单据%s所属的期间未打开，请与财务人员联系',16,1," + doccode + ") return end \n";
|
return sql;
|
}
|
@Override
|
public String replaceStr2(String s, String count) {
|
if (s == null || s.length() == 0) return s;
|
String kkt = " exec ";
|
String ss = "";
|
if (s.trim().indexOf("!") == 0) {
|
ss = s.trim().substring(1, s.length());
|
String[] temp = ss.trim().split("\\s+");
|
if (temp.length > 1 && !temp[1].trim().startsWith("'")) {
|
temp[1] = "'" + temp[1] + "'";
|
}
|
return "\n if @myrowcount <= 0 \n begin " + kkt + temp[0] + " " + temp[1] + " \n return end \n";
|
} else {
|
ss = s;
|
kkt = "";
|
return "\n if @myrowcount<= 0 \n begin " + ss + " \n return end \n";
|
}
|
|
}
|
|
/**
|
* 查找字符串中是否存在指定的内容 返回需要替换的字段名
|
*/
|
@Override
|
public List<String> getString(String str) {
|
Pattern p = Pattern.compile("&\\w*&");
|
List<String> list = new ArrayList<String>();
|
java.util.regex.Matcher propsMatcher = p.matcher(str != null ? str.toLowerCase() : "");
|
while (propsMatcher.find()) {
|
list.add(propsMatcher.group());
|
}
|
return list;
|
}
|
|
/**
|
* 查找以!开头的存储过程中的名字和参数 !upd_SalesOrderBalance doccode;formid 返回upd_SalesOrderBalance 和doccode;formid
|
*/
|
public List<String> getProcString(String str) {
|
List<String> list = new ArrayList<String>();
|
|
String[] t = str.replaceAll("!", "").trim().split("\\s{1,}");
|
String[] t1 = t[1].split(";");
|
list.add(t[0]);
|
for (String s : t1)
|
list.add(s.trim().toLowerCase());
|
return list;
|
}
|
|
/**
|
* 取得表中所有计算列字段
|
**/
|
public List<String> getComputedColumns(String tableName) {
|
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)";
|
List<String> list = this.jdbcTemplate.queryForList(sql, String.class, new Object[]{tableName, tableName, tableName});
|
list.add("Deleted");
|
list.add("Added");
|
list.add("Changed");
|
list.add("Moved");
|
list.add("Prev");
|
list.add("Next");
|
list.add("Panel");
|
list.add("Parent");
|
list.add("G");// 甘特图
|
return list;
|
}
|
|
/**
|
* 取得表中所有计算列字段
|
**/
|
public List getColumnsTypeInfo(String tableName) {
|
return this.jdbcTemplate.queryForList(" set nocount on; select column_name,data_type from INFORMATION_SCHEMA.columns where table_name =? \n", new Object[]{tableName});
|
|
}
|
|
@Override
|
/**
|
*
|
* 取得表中列字段的长度(char,nchar,varchar,nvarchar)
|
*
|
**/
|
public String getTypeLengthInfo(String tableName) {
|
//
|
return this.jdbcTemplate.queryForObject(" set nocount on; declare @s varchar(max) SELECT @s=(isnull(@s+',','')+ column_name +'-'+ convert(varchar ,character_maximum_length) ) from INFORMATION_SCHEMA.columns where table_name =? and data_type in ('char','nchar','varchar','nvarchar') \n select @s as s \n",
|
String.class, new Object[]{tableName});
|
|
}
|
|
/**
|
* 查找表中是否存在这个字段
|
**/
|
public int getColumnInfo(String tableName, String col) {
|
List list = this.jdbcTemplate.queryForList(" set nocount on; select 1 from INFORMATION_SCHEMA.columns where table_name=? and column_name=? \n", new Object[]{tableName, col});
|
return list.size() > 0 ? 1 : 0;
|
}
|
@Transactional
|
@Override
|
public String getAutoCode(int formid, int formType, String field) {
|
String sql =" set nocount on \n" +
|
" declare @FormId int = "+formid+",@FormType int = "+formType+",@FieldId nvarchar(100) = "+GridUtils.prossSqlParm(field)+"\n" +
|
" declare @CurCode varchar(50),@CurCodeStr varchar(50) \n" +
|
" declare @CodeLength int,@PreFixCode nvarchar(100),@PreCodeType int,@SplitChar nvarchar(100)\n" +
|
" declare @HDTable nvarchar(100) ,@sql nvarchar(2000) ,@ParmDefinition nvarchar(2000) , @FieldValue nvarchar(200) \n" +
|
" declare @TreeFormId int,@NodeId nvarchar(100)\n" +
|
" select @PreCodeType = PreCodeType,@PreFixCode = PreFixCode,@SplitChar=PreFixCode,@CodeLength = CodeLength\n" +
|
" from gform where FormId = @FormId \n" +
|
"\n" +
|
"\n" +
|
"--select @PreCodeType = 3 ,@PreFixCode = ''\n" +
|
" if isnull(@PreCodeType,0) = 2 \n" +
|
" begin\n" +
|
" set @PreFixCode = @FieldId \n" +
|
" end \n" +
|
" if isnull(@PreCodeType,0) = 3 \n" +
|
" begin\n" +
|
" select @TreeFormId = TreeFormId,@NodeId = NodeId from _systreeset \n" +
|
" where FormId = @FormId and FormType = @FormType\n" +
|
" if @@ROWCOUNT = 0 \n" +
|
" begin\n" +
|
" raiserror('请在9824功能号中设置树相关的参数',16,1)\n" +
|
" return\n" +
|
" end \n" +
|
" select @HDTable = HDTable from gform where formid = @TreeFormId \n" +
|
" if @@ROWCOUNT = 0 \n" +
|
" begin\n" +
|
" raiserror('相关的树功能号【%d】不存在',16,1,@TreeFormId)\n" +
|
" return\n" +
|
" end \n" +
|
" if isnull(@HDTable,'') = ''\n" +
|
" begin\n" +
|
" raiserror('相关的树功能号【%d】主表不能为空',16,1,@TreeFormId)\n" +
|
" return\n" +
|
" end \n" +
|
" select @sql = N' set @PreFixCode = '''' ;\n" +
|
" with temp ('+isnull(@NodeId,'') + ',RowId,ParentRowId) as \n" +
|
" (select '+isnull(@NodeId,'') + ',RowId,ParentRowId from '+isnull(@HDTable,'')+' where '+isnull(@NodeId,'') + ' = @FieldId\n" +
|
" union all select a.'+isnull(@NodeId,'') + ',a.RowId,a.ParentRowId from '+isnull(@HDTable,'')+' a \n" +
|
" inner join temp b on a.RowId = b.ParentRowId) -- = temp.RowId)\n" +
|
" select @PreFixCode = isnull(a.'+isnull(@NodeId,'') + ','''') + case when isnull(@PreFixCode,'''') <> '''' \n" +
|
" then isnull(@SplitChar,'''') else '''' end + isnull(@PreFixCode,'''') \n" +
|
" from temp a ; \n" +
|
" if isnull(@PreFixCode,'''') <> '''' set @PreFixCode = isnull(@PreFixCode,'''') + isnull(@SplitChar,'''') '\n" +
|
" set @ParmDefinition = N'@PreFixCode nvarchar(100) output,@SplitChar nvarchar(100),@FieldId nvarchar(100) '\n" +
|
" EXEC sp_executesql @sql , @ParmDefinition , @PreFixCode output , @SplitChar,@FieldId \n" +
|
"\n" +
|
"\t--set @SplitChar = @PreFixCode;\n" +
|
"\t--set @PreFixCode = '' ;\n" +
|
"\t--with temp (MatGroup,RowId,ParentRowId) as \n" +
|
"\t--(select MatGroup,RowId,ParentRowId from t110501 where MatGroup = @FieldId\n" +
|
"\t--union all select a.MatGroup,a.RowId,a.ParentRowId from t110501 a inner join temp b on a.RowId = b.ParentRowId) -- = temp.RowId)\n" +
|
"\t--select @PreFixCode = isnull(a.MatGroup,'') + case when isnull(@PreFixCode,'') <> '' \n" +
|
"\t--\tthen isnull(@SplitChar,'') else '' end + isnull(@PreFixCode,'') \n" +
|
"\t--from temp a ;\n" +
|
"\t--if isnull(@PreFixCode,'') <> '' set @PreFixCode = isnull(@PreFixCode,'') + isnull(@SplitChar,'')\n" +
|
" end \n" +
|
"--select @PreFixCode as PreFixCode\n" +
|
"\n" +
|
" exec p110205 @FormId=@FormId,\n" +
|
" @FormType= @FormType,\n" +
|
" @Fieldid =@FieldId,\n" +
|
" @PreFixCode = @PreFixCode,\n" +
|
" @CodeLength =@CodeLength,\n" +
|
" @CurCode = @CurCode output,\n" +
|
" @CurCodeStr = @CurCodeStr output\n" +
|
" select @CurCodeStr as CurCodeStr\n";
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
public String getAutoCode2(String rowid, int length, String percode, String perc, String table) {
|
StringBuilder sb = new StringBuilder();
|
sb.append(" set nocount on; declare @curcode int ,@rntcode varchar(20) , @Treetable varchar(50) ,@rowid varchar(20),@codelength int ").append(" select @rowid ='" + rowid + "',@codelength = " + length + " ")
|
.append(" begin Tran select @curcode = isnull(curcode,0) from " + table + " with (updlock) where rowid = @rowid ").append(" select @curcode =convert(varchar , isnull(@curcode,0) + 1 ) ")
|
.append(" select @rntcode = '" + percode + "' + '" + perc + "' + replicate('0' , @codelength - len(convert(varchar,@curcode ) )) + convert(varchar,@curcode) ").append(" update " + table + " set Curcode = @curcode where rowid = @rowid ")
|
.append("if @@rowcount <> 0 begin select @rntcode commit Tran end else begin select '' rollback tran end ");
|
return this.jdbcTemplate.queryForObject(sb.toString(), String.class);
|
}
|
|
private List getCodeInfo(int formid) {
|
String sql = " set nocount on; select g.formid,g.codelength,g.preFixcode,g.precodetype,c.Formtype,c.Curcode,c.Fieldid from gform g,_sysautocode c where g.formid=c.formid and g.formid=?";
|
return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
|
}
|
|
public String getPeriodID(int formid, String companyid, String docdate) {
|
String sql = " set nocount on; select dbo.GetPeriodID(" + formid + ",'" + companyid + "','" + docdate + "')";
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
/**
|
* 取得自动编号的类型
|
*/
|
private List getAutoCodeType(int formid) {
|
String sql = "set nocount on; select precodetype,codelength,preFixcode from gform where formid=?";
|
return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
|
}
|
|
public String getTreeTable(int formid) {//返回格式：树1的表名;树1功能号,树2表名;树2功能号
|
String sql = " set nocount on; SELECT stuff((SELECT ',' + CONVERT(VARCHAR, g.HDTable)+';'+CONVERT(VARCHAR, g.formid) from _systreeset s,gform g \n" +
|
" where s.formid=? and s.treeformid=g.formid FOR XML PATH ('')),1,1,'')";
|
return this.jdbcTemplate.queryForObject(sql, String.class, new Object[]{formid});
|
}
|
|
/**
|
* 取得三表设置的相关联信息,9825
|
*/
|
public List getThreeTableInfo(int formid) {
|
String sql = " set nocount on; select DetailFormID,MasterField,MasterKeys,DetailKeys,MasterSumFields,DetailSumFields,GridHeight from _sysmasterdetail where formid=? ";// 为了新增对不是三表的也支持，去掉这个限制and FormID<>DetailFormID
|
return this.jdbcTemplate.queryForList(sql, new Object[]{formid});
|
}
|
|
/**
|
* 取得功能号的过账类型号
|
**/
|
public int getTranID(int formid) {
|
int in = this.jdbcTemplate.queryForObject(" set nocount on; declare @a int select @a=isnull(transgroupcode,0) from gform where formid=? select @a", Integer.class, new Object[]{formid});
|
return in;
|
}
|
|
/**
|
* 取得7类型功能号的过滤条件
|
**/
|
public List<Map<String, Object>> getFilter7(int formid) throws DataAccessException {
|
String sql = " set nocount on; select * from _sysCtrlFieldGroupDetail" + " where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldGroup where CtrlGroupid=(select CtrlGroupid from _sysCtrlFieldFilter where Formid=?))" + " order by joingroupid asc \n";
|
return this.jdbcTemplate.queryForList(sql, formid);
|
}
|
|
/**
|
* 取得功能号的保存时执行及保存时执行存储过程组
|
**/
|
public Map<String, Object> getDealAfterDocSave(int formid) {
|
return this.jdbcTemplate.queryForMap(" set nocount on; select DealAfterDocSave,ProcGroupafterSavedoc,CancelBtnProcName,RevokeBtnProcName,importProcName from gform where formid=? \n", formid);
|
}
|
|
/**
|
* 取得功能号导入时保存时执行存储过程
|
**/
|
public String getImportExcelProc(int formid) {
|
return this.jdbcTemplate.queryForObject(" set nocount on; select importProcName from gform where formid=?", String.class, new Object[]{formid});
|
}
|
|
/**
|
* 根据功能号取得主表名
|
**/
|
public String getTableName3(int formid, String table) {
|
return this.jdbcTemplate.queryForObject("set nocount on; select " + table + " from gform where formid=?", String.class, new Object[]{formid});
|
}
|
|
/**
|
* 取得与树设置有关的字段名
|
**/
|
private String getTreeFields(int formid) {
|
return this.jdbcTemplate.queryForObject("set nocount on; select treefield from _systreeset where formid=?", String.class, new Object[]{formid});
|
}
|
|
@Override
|
public String getByProc(final String num) {
|
String sql = "{call getXXXXByNum(?,?)}";
|
Map map = (Map) this.jdbcTemplate.execute(sql, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
cs.setInt(1, Integer.parseInt(num));
|
cs.registerOutParameter(2, Types.VARCHAR);// 输出参数
|
cs.execute();
|
Map map = new HashMap();
|
map.put("rowids", cs.getString(2));
|
return map;
|
}
|
});
|
return (String) map.get("rowids");
|
}
|
|
/**
|
* 取得与树设置有关的字段名
|
**/
|
public String getTableKeyFields(String table) {
|
List<String> list = this.jdbcTemplate.queryForList("set nocount on; select keyfields from _systablekey where tableid=?", String.class, new Object[]{table});
|
if (list.size() > 0) return list.get(0);
|
else return "";
|
}
|
|
@Override
|
public List<Map<String, Object>> getGlfiledInfo(String s) {
|
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});
|
}
|
|
@Override
|
public List<Integer> getGlfiledInfoCheck(String s) {
|
return this.jdbcTemplate.queryForList("set nocount on; select docitem from FCountItem where acctcode=? order by docitem asc", Integer.class, new Object[]{s});
|
}
|
|
/**
|
* 判断字符串是否为空或null
|
**/
|
private boolean isNullOrEmptry(String s) {
|
return s != null && s != "" && "".equals("") && s.length() != 0 ? false : true;
|
}
|
|
/**
|
* 根据表名取得表主键名,查找表结构和9807设置
|
*
|
* @return int
|
* @throws SQLException
|
*/
|
@Override
|
public List<String> getPrimaryKey(String tableName) throws DataAccessException, SQLException {
|
List<String> key = new ArrayList<String>();
|
String sql=" declare @key varchar(1000),@tableName varchar(100)=?\n" +
|
" SELECT @key=stuff((SELECT';' + CONVERT(VARCHAR, column_name)\n" +
|
" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC\n" +
|
" INNER JOIN\n" +
|
" INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU\n" +
|
" ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND\n" +
|
" TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND \n" +
|
" KU.table_name=@tableName\n" +
|
" ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION\n" +
|
" FOR XML PATH ('')),1,1,'')\n" +
|
" if isnull(@key,'')=''\n" +
|
" begin\n" +
|
" select @key=keyfields from _systablekey where tableid=@tableName\n" +
|
" end\n" +
|
" select lower(@key) \n";
|
final String result = this.getSimpleJdbcTemplate().queryForObject(sql, String.class, tableName);
|
if(result!=null){
|
key=Arrays.asList(result.split(";"));
|
}
|
return key;
|
}
|
|
/**
|
* 根据类型号，得到当前功能号是什么类型
|
**/
|
public int getwinType(int type) {
|
switch (type) {
|
// 1-4,6是只有主表
|
case 1:
|
case 2:
|
case 3:
|
case 4:
|
case 6:
|
case 38:
|
case 7:
|
return 1;
|
case 5:// 主从表
|
case 9:
|
return 9;
|
case 10:
|
return 2;
|
case 8:// 三表
|
return 3;
|
case 18:// 表名为函数
|
return 4;
|
case 19:// 表名为存储过程
|
return 5;
|
default:
|
return 0;
|
}
|
}
|
|
@Override
|
public String getTableNameByType(GformEntity gformEntity, String type) {
|
String[] temp = type.split("\\|");
|
int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0;
|
String table = "";
|
int winType = Integer.parseInt(temp[0]);
|
if (gformEntity!=null) {
|
FdFilters = gformEntity.getFormdatafilters();
|
table = (winType == 0 || winType == 7 || winType == 1 || winType == 5 || winType == 2 || (winType == 10 && conNum == 1) ||
|
(winType == 9 && conNum == 0) || winType == 3 || (winType == 4 && conNum == 0) || winType == 17 ||
|
//(winType == 20 && conNum == 0)||--pc上 20，0是取从表，所以app需要调用winType == 20 & connum=1
|
(winType == 301 && conNum == 0) || (winType == 303 && conNum == 0)
|
|| (winType == 304 && conNum == 0)
|
//||(winType == 238 && conNum == 0)||
|
//(winType == 30&& conNum == 1)||--app直接调用connum=1就可以，PC端加载或保存时调用conNum=0，取到从表
|
|| (winType == 302 && conNum == 0) || winType == 19 || (winType == 499 && conNum == 0) ||
|
(winType == 497 && conNum == 0) || (winType == 15 && (conNum == 0 || conNum == 2)))
|
? gformEntity.getHdtable() : gformEntity.getDttable();
|
|
|
}
|
return table;
|
}
|
|
@Override
|
public Map<String,TableMetaData> getTableMetaData(String tableName) {
|
List<TableMetaData> list= this.getJdbcTemplate().query(" set nocount on; select b.name as columnName, c.name as dataType ,b.iscomputed,b.length\n" +
|
" from sysobjects a \n" +
|
" join syscolumns b on a.id = b.id \n" +
|
" join systypes c on b.xtype=c.xtype and b.xtype = c.xusertype and c.status = 0 \n" +
|
" where a.NAME = ?\n" +
|
" and b.iscomputed = 0 --取非计算列\n" +
|
" and b.colstat = 0 --取非自增列 \n" +
|
" order by b.colorder \n",new BeanPropertyRowMapper<>(TableMetaData.class), tableName);
|
|
HashMap<String,TableMetaData> map=new HashMap<>();
|
list.stream().forEach(x->{
|
map.put(x.getColumnName().toLowerCase(),x);
|
});
|
|
return map;
|
|
}
|
|
/**
|
* 根据功能号取得主从表名称 -----9801信息---增加新窗体类型都需要增加相应判断
|
**/
|
@Override
|
public String getTableName(int formid, String type) {
|
SqlRowSet gform = this.getGformByFormID(formid);
|
return getTableNameV2(gform, type);
|
}
|
@Override
|
public String getTableNameV2(SqlRowSet gform, String type) {
|
String[] temp = type.split("\\|");
|
int conNum = temp.length > 1 ? Integer.parseInt(temp[1]) : 0;
|
String table = "";
|
int winType = Integer.parseInt(temp[0]);
|
if (!gform.wasNull()) {
|
gform.first();
|
FdFilters = gform.getString("formdatafilters");
|
table = (winType == 0 || winType == 7 || winType == 1 || winType == 5 || winType == 2 || (winType == 10 && conNum == 1) ||
|
(winType == 9 && conNum == 0) || winType == 3 || (winType == 4 && conNum == 0) || winType == 17 ||
|
//(winType == 20 && conNum == 0)||--pc上 20，0是取从表，所以app需要调用winType == 20 & connum=1
|
(winType == 301 && conNum == 0) || (winType == 303 && conNum == 0)
|
|| (winType == 304 && conNum == 0)
|
//||(winType == 238 && conNum == 0)||
|
//(winType == 30&& conNum == 1)||--app直接调用connum=1就可以，PC端加载或保存时调用conNum=0，取到从表
|
|| (winType == 302 && conNum == 0) || winType == 19 || (winType == 499 && conNum == 0) ||
|
(winType == 497 && conNum == 0) || (winType == 15 && (conNum == 0 || conNum == 2)))
|
? gform.getString("hdtable") : gform.getString("dttable");
|
|
|
}
|
return table;
|
}
|
/**
|
* 获得数据组内容，组装条件语句附加到需要查询的地方，形式："and ccode=12345"
|
*
|
* @param flag 0 主表，1从表
|
**/
|
public List<Map<String, Object>> getDataGroupInfo(int formid, String usercode, int flag) {
|
String sql = " set nocount on; select d.fieldid,b.accessid,b.authobj,b.jionFlaggroup ,b.docitem,b.accessname,b.jionflag,b.conflag,b.modfvalues,b.modfvalues2,b.dspvalues,b.defaultvalue from _sysdataaccess_user u,_sysdataaccessauthobj b ,_sysdataauthobjdetail d "
|
+ " where b.accessid=u.accessid and u.usercode=? and u.actived=1 " + "and b.authobj=d.authobj and d.formid=? and isnull(d.headflag,0)=? order by b.accessid,b.jionFlaggroup ,b.docitem \n";
|
return this.jdbcTemplate.queryForList(sql, new Object[]{usercode, formid, flag});
|
|
}
|
|
/**
|
* 根据功能号查出所有字段，以,分隔返回。是为了给淘宝接口fileds调用
|
***/
|
public String getFileds(String formid) {
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList("set nocount on; select fieldid from gField where formid=? and datalink=1", new Object[]{formid});
|
StringBuffer sb = new StringBuffer();
|
for (Map<String, Object> map : list) {
|
sb.append((String) map.get("fieldid")).append(",");
|
}
|
return sb.length() > 0 ? sb.substring(0, sb.length() - 1) : "";
|
}
|
|
/**
|
* 根据父节点查询下一节点的rowid
|
*
|
* @param parentrowid
|
* @param tablename
|
* @return
|
*/
|
public List<Map<String, Object>> getTreeRowid(String parentrowid, String tablename) {
|
String sql = "set nocount on; select * from " + tablename + " where parentrowid='" + parentrowid + "'";
|
return this.jdbcTemplate.queryForList(sql);
|
}
|
|
@Override
|
public List<Map<String, Object>> getComputedHelp(String formid) {
|
String sql = "set nocount on; select * from _sys_formid_Help where doccode='" + formid + "'";
|
return this.jdbcTemplate.queryForList(sql);
|
}
|
}
|
