eCoWorksV3.git - Gitblit

xinyb

2024-07-15 7d060da3d4f9fdcea2466e1646e5b5496814f322

提交 \| 用户 \| age
93605b	1	package com.yc.service.customControl;
X	2
97a11f	3	import com.yc.action.grid.ExprotType;
93605b	4	import com.yc.action.grid.GridUtils;
X	5	import com.yc.entity.customControl.CustomAttribute;
	6	import com.yc.entity.customControl.MenuEntity;
	7	import com.yc.service.BaseService;
	8	import org.apache.commons.lang3.StringUtils;
	9	import org.springframework.jdbc.core.BeanPropertyRowMapper;
	10	import org.springframework.stereotype.Service;
	11
	12	import java.util.List;
	13	import java.util.Map;
	14	import java.util.Set;
	15	import java.util.stream.Collectors;
	16
	17	/**
	18	* @BelongsProject: eCoWorksV3
	19	* @BelongsPackage: com.yc.service.controlLayout
	20	* @author: xinyb
	21	* @CreateTime: 2023-06-12 15:59
	22	* @Description:
	23	*/
	24	@Service
	25	public class ControlLayoutImpl extends BaseService implements ControlLayoutIfc {
	26
	27	/**
	28	* 获取功能号的内容信息（功能类型，名称等）
	29	*
	30	* @param formId
	31	* @return
	32	*/
	33	@Override
	34	public List<MenuEntity> getMenu(Integer formId, Integer formType) {
	35	try {
	36	String sql = "set nocount on;\n" +
97a11f	37	"declare @formId int="+formId+",@formType int="+formType+"\n" +
F	38	" declare @table table(formId int,formName varchar(500), formType int,actived int,hdTable varchar(500),dtTable varchar(500),mainFormId int,fk varchar(100),seekGroupId varchar(100),tabId varchar(20))\n" +
	39	"if @formType=8\n" +//8类型
93605b	40	"begin\n" +
97a11f	41	"insert into @table(formId ,formName , formType ,actived ,hdTable,dtTable ,mainFormId ,fk ,seekGroupId,tabId)\n" +
F	42	"select a.formid,a.formname,@formType as formtype,a.actived,g.hdtable,g.dttable,a.formid,g.fk,g.seekGroupId,0 as tabId \n" +
	43	"from _sysMenu a join gform g on a.formid=g.formid where a.formid=@formId\n" +
	44	"insert into @table(formId ,formName , formType ,actived ,hdTable,dtTable ,mainFormId ,fk ,seekGroupId,tabId)\n" +
	45	" select a.[detailformid] as formId,b.formname as formName,1 as formType,\n" +
	46	" 1 as actived,b.hdtable as hdTable,null as dtTable,@FormId as mainFormid,null as fk,null as seekGroupId,0 as tabId from _sysmasterdetail a join gform b on a.detailformid=b.formid where a.formid=@formId\n" +
93605b	47	"end\n" +
97a11f	48	"else if @formType=496 or @formType=498\n" +//多表
F	49	"begin\n" +
44285a	50	"insert into @table(formId ,formName , formType ,actived ,hdTable,dtTable ,mainFormId ,fk ,seekGroupId,tabId)\n" +
X	51	"select a.formId,isnull(a.LabelName,a.formName) as formName,case when a.mainFormId=a.formid then @formType else a.formType end as formType,a.actived,g.hdTable,g.dtTable,a.mainFormId,a.fk,a.seekGroupId,isnull(a.tabId,0) as tabId " +
	52	"from _sys_TabPageFormid a join gform g on a.formid=g.formid where a.mainformid=@formId and a.Actived=1 order by a.tabId,a.SortBy\n" +
97a11f	53	"end\n" +
F	54	"else\n" +//常规
	55	"begin\n" +
	56	"insert into @table(formId ,formName , formType ,actived ,hdTable,dtTable ,mainFormId ,fk ,seekGroupId,tabId)\n" +
	57	"select a.formid,a.formname,@formType as formtype,a.actived,g.hdtable,g.dttable,a.formid,g.fk,g.seekGroupId,0 as tabId \n" +
	58	"from _sysMenu a join gform g on a.formid=g.formid where a.formid=@formId\n" +
	59	"end\n" +
	60	"select * from @table";
93605b	61	return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MenuEntity.class));
X	62	} catch (Exception e) {
	63	throw e;
	64	}
	65	}
	66
	67	/**
	68	* 获取功能号在9802的参数设置
	69	*
	70	* @param userCode
	71	* @param formId
	72	* @return
	73	*/
	74	@Override
	75	public List<CustomAttribute> getCustomAttributes(String userCode, Integer formId, Integer formType) {
	76	try {
7d060d	77	String where = "";
93605b	78	String sql = "set nocount on;\n" +
64e2f8	79	"declare @table table(userCode varchar(50),formId int,formType int,headFlag int,fieldId varchar(50),controlType int,statisId int,fieldName varchar(200),dataType varchar(50),dataTypeLength int,\n" +
93605b	80	"showonGrid int,gridCaption varchar(200),visible int,hideLabel int,rowNo int,colNo int,lengthNum int,heightNum int,masterFieldShowLocation int);\n" +
64e2f8	81	"insert into @table(userCode,formId,formType,headFlag,fieldId,controlType,statisId,fieldName,dataType,dataTypeLength,\n" +
93605b	82	"showonGrid,gridCaption,visible,hideLabel,rowNo,colNo,lengthNum,heightNum,masterFieldShowLocation) \n" +
64e2f8	83	" select g.UserCode,g.FormId,g.FormType,g.HeadFlag,g.FieldId,(select top 1 a.controltype from gField a where a.formid=g.FormId and a.fieldid=g.FieldId and a.headflag=g.HeadFlag) as controlType," +
ca30ee	84	"g.StatisId,g.FieldName,g.DataType,g.DataTypeLength,g.ShowOnGrid,g.GridCaption,g.Visible,g.HideLabel,\n" +
64e2f8	85	" g.RowNo,g.ColNo,g.LengthNum,g.HeightNum,g.MasterFieldShowLocation from gFieldCustomLayout g where g.UserCode='" + userCode + "' and g.FormId=" + formId + " and g.FormType=" + formType +
93605b	86	"\n if @@ROWCOUNT = 0\n" +
X	87	"begin\n" +
64e2f8	88	"insert into @table(userCode,formId,formType,headFlag,fieldId,controlType,statisId,fieldName,dataType,dataTypeLength,\n" +
93605b	89	"showonGrid,gridCaption,visible,hideLabel,rowNo,colNo,lengthNum,heightNum,masterFieldShowLocation) \n" +
64e2f8	90	"select '" + userCode + "',b.formid," + formType + ",b.HeadFlag,b.FieldId,b.controlType,b.StatisId,b.FieldName,b.DataType,b.DataTypeLength,b.ShowOnGrid,b.GridCaption,b.Visible,b.HideLabel,\n" +
93605b	91	" b.RowNo,b.ColNo,b.LengthNum,b.HeightNum,b.MasterFieldShowLocation from gField b " +
X	92	"where b.formid=" + formId;
	93	switch (formType) {
	94	case 9:// 9类型面板
	95	case 15:
	96	case 17:
	97	case 499:
	98	case 497:
97a11f	99	case 1:
F	100	case 3:
	101	sql += " and b.headFlag=0 and b.showOnGrid=1 \n" +//and b.statisFlag=1
93605b	102	" end\n";
7d060d	103	where = "b.headFlag=0 and b.showOnGrid=1";
93605b	104	break;
97a11f	105	case 2:
F	106	case 16:
	107	sql += " and b.headFlag=0 and b.visible=1 \n" +//and b.statisFlag=1
	108	" end\n";
7d060d	109	where = "b.headFlag=0 and b.visible=1";
97a11f	110	break;
F	111	case 7:
	112	case 18:
	113	case 19:
	114	//7,18,19主从表相反
	115	sql += " and ((b.visible=1 and b.headFlag=1) or (b.showOnGrid=1 and b.headFlag=0)) \n" +
	116	" end \n";
7d060d	117	where = "((b.visible=1 and b.headFlag=1) or (b.showOnGrid=1 and b.headFlag=0))";
97a11f	118	break;
93605b	119	default:
97a11f	120	//主从表都需要加载的情况，5,8,496,....
F	121	sql += " and ((b.visible=1 and b.headFlag=0) or (b.showOnGrid=1 and b.headFlag=1)) \n" +
ca30ee	122	" end \n";
7d060d	123	where = "((b.visible=1 and b.headFlag=0) or (b.showOnGrid=1 and b.headFlag=1))";
93605b	124	break;
X	125	}
7d060d	126	sql += "\n else --自定义表有数据时候，需要检查下系统设置表（9802）是否有新设置字段\n" +
X	127	" begin\n" +
	128	" insert into @table(userCode,formId,formType,headFlag,fieldId,controlType,statisId,fieldName,dataType,dataTypeLength,\n" +
	129	"showonGrid,gridCaption,visible,hideLabel,rowNo,colNo,lengthNum,heightNum,masterFieldShowLocation) \n" +
	130	"select '" + userCode + "',b.formid," + formType + ",b.HeadFlag,b.FieldId,b.controlType,b.StatisId,b.FieldName,b.DataType,b.DataTypeLength,b.ShowOnGrid,b.GridCaption,b.Visible,b.HideLabel,\n" +
	131	" b.RowNo,b.ColNo,b.LengthNum,b.HeightNum,b.MasterFieldShowLocation from gField b " +
	132	"where b.formid="+formId+" and "+where+" and b.fieldid not in(select fieldId from @table)\n" +
	133	" -- 如果系统设置表（9802）把显示出来的字段设置成不显示，那这里就删除临时表里对应字段信息 \n"+
	134	" delete a from @table a where a.formType="+formType+" and a.fieldId not in (select b.fieldId from gField b where b.formid="+formId+" and "+where+")\n" +
	135	" end \n";
64e2f8	136	sql += "select a.userCode,a.formId,a.formType,a.headFlag,LOWER(a.fieldId) as fieldId,a.controlType,a.statisId,isnull(a.fieldName,a.fieldId) as fieldName," +
4ef020	137	"a.dataType,a.dataTypeLength,a.showonGrid,a.gridCaption,(case when (a.rowNo > 0 and a.colNo > 0 and a.visible = 1) then 1 else 0 end) as visible,a.hideLabel,a.rowNo," +
93605b	138	" a.colNo,a.lengthNum,a.heightNum,isnull(a.masterFieldShowLocation,0) as masterFieldShowLocation" +
X	139	" from @table a order by a.headFlag, a.statisid";
	140	return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CustomAttribute.class));
	141	} catch (Exception e) {
	142	throw e;
	143	}
	144	}
	145
	146	/**
	147	* 获取对应功能号单据的数据信息（根据where条件查询获取到结果）
	148	*
	149	* @param table
	150	* @param where
	151	* @return
	152	*/
	153	@Override
	154	public Map<String, Object> getFormData(String fieldId, String table, String where) {
	155	try {
	156	String sql = "set nocount on;\n" +
	157	"select " + fieldId.toLowerCase() + " from " + table + " where " + where;
	158	return jdbcTemplate.queryForMap(sql);
	159	} catch (Exception e) {
	160	return null;
	161	}
	162	}
	163
	164	/**
	165	* 保存控件布局信息
	166	*
	167	* @param customAttributes
	168	* @return
	169	*/
	170	@Override
	171	public Integer saveCustomLayout(List<CustomAttribute> customAttributes, Set<Integer> formIds, String userCode) throws Exception {
	172	try {
	173	String sql = "set nocount on;\n" +
	174	"declare @cunt int=0;\n";
	175	String insertSql = "";
	176	String where = "";
	177	for (Integer formId : formIds) {
	178	//得到相同功能号的集合
	179	List<CustomAttribute> custom = customAttributes.stream().filter(c -> c.getFormId().equals(formId)).collect(Collectors.toList());
	180	Set<Integer> headFlag = custom.stream().map(CustomAttribute::getHeadFlag).collect(Collectors.toSet());
	181	if (headFlag.size() == 1 && headFlag.stream().findFirst().get() == 0) {
	182	where = " a.formId=" + formId + " and a.headFlag=0 ";
	183	} else if (headFlag.size() == 1 && headFlag.stream().findFirst().get() == 1) {
	184	where = " a.formId=" + formId + " and a.headFlag=1 ";
	185	} else if (headFlag.size() == 2) {
	186	where = " a.formId=" + formId;
	187	} else {
	188	throw new Exception("检查headFlag属性是否出现不为0或1的值");
	189	}
ca30ee	190	insertSql = "";//清空
93605b	191	//遍历每个控件，组装成插入语句
X	192	for (CustomAttribute c : custom) {
64e2f8	193	where += " and a.formType=" + c.getFormType();
X	194	insertSql += "insert into gFieldCustomLayout(userCode,formId,formType,headFlag,fieldId,statisId,fieldName,dataType,dataTypeLength," +
93605b	195	"showOnGrid,gridCaption,visible,hideLabel,rowNo,colNo,lengthNum,heightNum,masterFieldShowLocation) ";
X	196	insertSql += "values (" + GridUtils.prossSqlParm(StringUtils.isBlank(c.getUserCode()) ? userCode : c.getUserCode()) + "," +
64e2f8	197	c.getFormId() + "," + c.getFormType() + "," + c.getHeadFlag() + "," + GridUtils.prossSqlParm(c.getFieldId()) + "," + c.getStatisId() + "," +
93605b	198	GridUtils.prossSqlParm(c.getFieldName()) + "," + GridUtils.prossSqlParm(c.getDataType()) + "," + c.getDataTypeLength() + "," + c.getShowOnGrid() + "," +
X	199	GridUtils.prossSqlParm(c.getGridCaption()) + "," + c.getVisible() + "," + c.getHideLabel() + "," + c.getRowNo() + "," +
	200	c.getColNo() + "," + c.getLengthNum() + "," + c.getHeightNum() + "," + c.getMasterFieldShowLocation() + "); \n";
	201	}
	202	sql += "select @cunt=count(a.FormId) from gFieldCustomLayout a where " + where +
	203	" and a.userCode=" + GridUtils.prossSqlParm(userCode) + ";\n";
	204	sql += "if @cunt > 0 \n" +
	205	"begin \n" +
	206	"delete a from gFieldCustomLayout a where " + where +
ca30ee	207	" and a.userCode=" + GridUtils.prossSqlParm(userCode) + ";\n" +
93605b	208	"end \n";
97a11f	209	//更新版本号 by danaus 2024-04-01 11:30
F	210	sql += insertSql + "\n exec p9801 @FormId =9802 ,@InstanceFormId =" + formId + ",@DictId =null \n";
93605b	211	}
64e2f8	212	sql += "select @@ROWCOUNT row \n";
X	213	return jdbcTemplate.queryForObject(sql, Integer.class);
93605b	214	} catch (Exception e) {
X	215	throw e;
	216	}
	217	}
ca30ee	218
X	219	@Override
64e2f8	220	public Integer deleteCustomLayout(String userCode, Set<Integer> formId, Set<Integer> formType) {
ca30ee	221	try {
X	222	String sql = "set nocount on;\n";
64e2f8	223	sql += "delete gFieldCustomLayout where userCode='" + userCode + "' and " +
X	224	" formId in(" + StringUtils.join(formId, ",") + ") and " +
	225	" formType in(" + StringUtils.join(formType, ",") + ")\n";
ca30ee	226	sql += "select @@ROWCOUNT row;";
X	227	return jdbcTemplate.queryForObject(sql, Integer.class);
	228	} catch (Exception e) {
	229	throw e;
	230	}
	231	}
	232
	233	@Override
64e2f8	234	public boolean hasControlLayout(String formId, Integer formType, String userCode) {
ca30ee	235	boolean bol = false;
X	236	try {
97a11f	237	/*if(formType==496){
F	238	//496类型需要查找如果主功能号没自定义布局，还要查下子功能号有没的情况
	239	//9771通过主功能号查子功能号
	240	formId= jdbcTemplate.queryForObject(" SELECT stuff((SELECT',' + CONVERT(VARCHAR, formid) FROM _sys_TabPageFormid where mainformid=" + formId + " FOR XML PATH ('')),1,1,'')", String.class);
	241
	242	}*/
64e2f8	243	String sql = "select COUNT(*) as counts from gFieldCustomLayout where formId=" + formId + " and formType=" + formType + " and userCode='" + userCode + "'";
ca30ee	244	Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
X	245	return bol = (count > 0 ? true : bol);
	246	} catch (Exception e) {
	247	return bol;
	248	}
	249	}
97a11f	250
F	251	/**
	252	* 根据formid和formType，headFlag
	253	* 判断需要生成的页面是否有自定义布局
	254	* @param formId
	255	* @param userCode
	256	* @param formType
	257	* @param headFlag
	258	* @param exprotType 指定是处理面板还是表格
	259	* @param isType8_3 是否为8类型的第三表
	260	* @return fale表示没有自定义布局，true表示有自定义布局
	261	*/
	262	@Override
	263	public boolean hasControlLayout(String formId, String userCode, int formType, ExprotType exprotType,boolean isType8_3) {
	264	boolean bol = false;
	265	try {
44285a	266	String sql = "";
97a11f	267	int headFlag=0;
F	268	if(exprotType.equals(ExprotType.Panel)){
	269	//面板
	270	if(formType == 1 \|\|formType == 3\|\|formType == 9\|\|formType == 15\|\|formType == 17\|\|formType == 497\|\|formType == 499){
	271	//这些情况是没有面板，直接返回
	272	return false;
	273	}
	274	if(formType==18\|\|formType==19){
	275	headFlag=1;
	276	}
	277	}else {
	278	//表格
	279	if(formType == 5\|\|formType == 8\|\|formType == 10\|\|formType == 496\|\|formType == 498){
	280	headFlag=1;
	281	}
	282	if(formType==8&&isType8_3){
	283	//第三表
	284	headFlag=0;
	285	}
	286	}
44285a	287	sql = "select COUNT(*) as counts from gFieldCustomLayout where formId=" + formId + " and userCode='" + userCode + "' and formType="+formType+" and headFlag="+headFlag;
97a11f	288	Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
F	289	return bol = (count > 0 ? true : bol);
	290	} catch (Exception e) {
	291	return bol;
	292	}
	293	}
93605b	294	}