eCoWorksV3.git - Gitblit

fs-danaus

2022-07-16 c6121771842e38e380b2bdd2017b975067875e12

提交 \| 用户 \| age
a6a76f	1	package com.yc.ionic.service;
F	2
571d4b	3	import com.yc.ionic.entity.MessageParameter;
F	4	import com.yc.service.BaseService;
	5	import org.springframework.stereotype.Service;
	6
a6a76f	7	import java.util.List;
F	8	import java.util.Map;
	9
	10	@Service("ionicApp")
	11	public class AppImpl extends BaseService implements AppIfc {
	12
	13	@Override
	14	public Map<String, Object> getDataFromDoccode(String tabName, String fileds, String doccode) {
	15
	16	return this.getSimpleJdbcTemplate().queryForMap("set nocount on \n select " + fileds + " from " + tabName + " where doccode=?", doccode);
	17
	18	}
	19
	20	@Override
	21	public List<Map<String, Object>> getOAButton(int formid, String fieldid) {
	22	String sql = "set nocount on \n select " +
	23	" [buttonID]" +
	24	",[ButtonName] " +
	25	",[formid] " +
	26	",[headflag] " +
	27	",[fieldid] " +
	28	",[docitem] " +
	29	",[ProcName] " +
	30	",[Memo] " +
	31	",[isShowPwdEdit] " +
	32	",[editStatus] " +
	33	",[SelectChecker] " +
	34	",[ReturnCurChecker] " +
	35	",[ReturnCurCheckerName] " +
	36	",[FT] " +
	37	",[FTFormType] " +
	38	",[FK] " +
	39	",[SeekGroupID] " +
	40	",[sPremissField] " +
	41	",[dPremissField] " +
	42	",[FKeFilter] " +
	43	",[isAutoSaved] " +
	44	",[showItemExpression] " +
	45	",[UrlShowLocation] " +
	46	",[ExternalURL] " +
	47	",[isInspection] " +
	48	",[isExchangeDataWithHost]" +
	49	" from gfieldApprovedButton where formId= ? and fieldid=? order by docitem";
	50	return this.jdbcTemplate.queryForList(sql, new Object[]{formid, fieldid});
	51	}
	52
	53	@Override
	54	public List<Map<String, Object>> getMessage() {
	55
	56	String sql = "set nocount on \n" +
	57	" select b.formname,a.usercode,a.formid, a.formtype, a.doccode, a.curformid, a.topic,a.msgfrom, convert(varchar(64),a.UNID ) as UNID ,c.msgCount \n" +
	58	" " +
	59	" from t219001 a with (nolock) join gform b with (nolock) on a.formid = b.formid \n" +
	60	" join (select UserCode,Count(1) as msgCount \n" +
	61	" from t219001 a with (nolock) \n" +
	62	" where curstatus = 'Undone' and ActionType in ('审核','抄送','在线交流','通知')\n" +
	63	" group by UserCode ) c on a.UserCode = c.UserCode \n" +
	64	" where a.curstatus = 'Undone' and isnull(a.isRead,0) = 0 \n" +
	65	" and a.ActionType in ('审核','抄送','在线交流','通知') \n" +
	66	" and DATEDIFF(day,a.inserttime,getdate())=0 and isnull(a.isPush,0) = 0 \n" +
	67	" order by a.inserttime desc\n";
	68	return this.jdbcTemplate.queryForList(sql);
	69	}
	70
	71	@Override
	72	public List<Map<String, Object>> getMessageByUserCodeV2(MessageParameter messageParameter) {
	73	//String where=" where UserCode = '"+messageParameter.getUserCode()+"'";
	74	if ("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 2) {//我发起的
	75	messageParameter.setActionType("提交");
2b4227	76	} else if ("抄送我的".equals(messageParameter.getActionType())\|\|("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 3)) {//抄送我的
a6a76f	77	messageParameter.setActionType("抄送");
F	78	}
	79
	80	// String sql="set nocount on \n select DocStatusName,CurcheckerName,DocType,PeriodType,AppImagePath,formname,PostName,PostDate,SenderName,usercode,msgfrom,formid,formtype,doccode,topicText as topic,isRead,UNID,inserttime,inserttimedesc from f219001('2000-01-01',getdate(),?,?,?,?,?,?) "+where+" order by inserttime desc,isnull(isRead,0) asc ";
	81
	82	String sql = " set nocount on \n declare @UserCode varchar(20) =? , @HrCode varchar(20) = ? ,\n" +
	83	" @SearchKey varchar(50) =?,@ActionType varchar(50)=?,@FilterFormId int =?\n" +
	84	" declare @Limit int = 20 ,@today datetime = getdate()\n" +
	85	" declare @Page int =? \n " +
	86
	87
	88	" select isnull(a.doccode,'') as doccode ,isnull(b.formid,0) as formid,isnull(b.formname,'') as formname,isnull(a.formtype,0) as formtype, \n" +
	89	" '<a onclick=''postT219001(\"'+isnull(b.formname,'')+'\",\"\",\"\",\"\",\"/NeedWork.do?UNID=' + convert(varchar(100),UNID) + \n" +
	90	" '&formId=' + convert(varchar(10),a.formid) + \n" +
	91	" '&doccode='+ isnull(doccode,'') + \n" +
	92	" '&actionType='+ isnull(ActionType,'') + \n" +
	93	" '&formType=' + convert(varchar(10),a.formtype) + \n" +
	94	" '\");'' style=\"text-decoration:underline;cursor: pointer;color: ' + \n" +
	95	" case when isnull(isRead,0) = 1 then ' blue;' else 'red;' end + '\">' +case when isnull(a.ActionType,'') = '在线交流' then convert(varchar(16),a.inserttime,120) +' ' + '【'+isnull(a.SenderName,'') + '】:' else '' end + isnull(topic,'') + '【' + isnull(b.formname,'')+'/' + isnull(a.doccode,'') + '】'+ '</a>' as topicText, \n" +
	96	" isnull(topic,'') as topic ,\n" +
	97	" \n" +
	98	" dbo.WebGetInStr(a.doccode,'219003','22','删除',@usercode+';'+ cast (a.unid as varchar(64)) ,'usercode;unid','1') as DelTodo , \n" +
	99	" a.inserttime ,isnull(a.isRead,0) as isRead,a.UNID,isnull(a.curstatus,'') as curstatus ,\n" +
	100	" isnull(a.usercode,'') as usercode,isnull(a.username,'') as username,isnull(a.msgfrom,0) as msgfrom,\n" +
	101	" case when DATEDIFF(day,a.inserttime,@today) = 0 then '今天'\n" +
	102	" when DATEDIFF(day,a.inserttime,@today) = 1 then '昨天'\n" +
	103	" when DATEDIFF(day,a.inserttime,@today) between 2 and 6 then DATENAME(weekday,a.inserttime) \n" +
	104	" when DATEDIFF(day,a.inserttime,@today) between 7 and 13 then '上周'\n" +
	105	" else '更早'\n" +
	106	" end as inserttimedesc,\n" +
	107	" isnull(a.EnterCode,'') as EnterCode,isnull(a.EnterName,'') as EnterName,a.EnterDate,isnull(a.PostCode,'') as PostCode,isnull(a.PostName,'') as PostName,a.PostDate,\n" +
	108	" isnull(a.SenderCode,'') as SenderCode,isnull(a.SenderName,'') as SenderName,isnull(a.ButtonType,'') as ButtonType,isnull(a.ActionType,'') as ActionType ,isnull(a.CcUserCode,'') as CcUserCode,isnull(a.CcUserName,'') as CcUserName,isnull(c.PeriodType,'') as PeriodType,\n" +
	109	" isnull(a.DocStatus,0) as DocStatus,isnull(a.DocStatusName,'') as DocStatusName,isnull(a.DocType,'') as DocType,isnull(a.Curchecker,'') as Curchecker , isnull(a.CurcheckerName,'') as CurcheckerName,\n" +
571d4b	110	" (select isnull(largImagePath,'') as largImagePath from _sysmenu m where a.formid = m.formid ) as largImagePath \n" +
a6a76f	111	" from t219001 a with (nolock) join gform b with (nolock) on a.formid = b.formid \n" +
F	112	" left join gdoctype c with (nolock) on a.formid = c.formid \n" +
	113	" where UserCode = @UserCode \n";
	114
	115	if (messageParameter.getIsfinshed() == 1) {
	116	sql += " and curstatus = 'Undone' \n";
	117	}
	118	if (messageParameter.getIsfinshed() == 2) {
	119	sql += " and curstatus = 'Finished' \n";
	120	}
	121	if (messageParameter.getIsfinshed() == 3) {
	122	//在线交流，通知
	123	if(messageParameter.getMsgType()==4) {
	124	//未读
	125	sql += " and isnull(a.isRead,0) = 0 \n";
	126	}
	127	else if(messageParameter.getMsgType()==5) {
	128	//已读
	129	sql += " and isnull(a.isRead,0) = 1 \n";
	130	}
	131	}
	132
	133	sql +=
	134	" and (isnull(@SearchKey,'') = ''\n" +
	135	" or a.QueryString like '%' + @SearchKey + '%' \n" +
	136	" or a.topic like '%' + @SearchKey + '%' \n" +
	137	" or a.DocStatusName like '%' + @SearchKey + '%' \n" +
	138	" or a.DocType like '%' + @SearchKey + '%' \n" +
	139	" or a.CurcheckerName like '%' + @SearchKey + '%' )\n" +
	140	" and (isnull(@ActionType,'') = '' or a.ActionType in (select list from GetInStr( @ActionType)) )\n" +
	141	" and (isnull(@FilterFormId,0) = 0 or a.formid = isnull(@FilterFormId,0))\n" +
	142	" order by isnull(isRead,0) asc,inserttime desc\n" +
	143	" OFFSET (isnull(@Page,0) - 1) * isnull(@Limit,0) ROWS \n" +
	144	" FETCH NEXT @Limit ROWS ONLY;\n";
	145
	146
	147	return this.jdbcTemplate.queryForList(sql, messageParameter.getUserCode(), messageParameter.getHrCode(), messageParameter.getSearchKey(), messageParameter.getActionType(), messageParameter.getFormid(), messageParameter.getPageIndex());
	148	}
	149
	150	@Override
	151	public List<Map<String, Object>> getMessageByUserCodeNum(String usercode) {
	152	// String sql = "set nocount on \n select Count(1) as msgCount from t219001 a where curstatus = 'Undone' and ActionType in ('审核','抄送','在线交流','通知') and UserCode =?";
	153	String sql="set nocount on \n" +
	154	" declare @userCode varchar(50)=?\n" +
	155	" declare @table table(ActionType varchar(50),ActionCount int) \n" +
	156	" declare @MsgCount int \n" +
	157	" declare @ActionCount int \n" +
	158	" declare @todoCount int \n" +
	159
	160	" select @ActionCount=count(1) \n" +
	161	" from t219001 a with (nolock) \n" +
	162	" where a.UserCode =@userCode\n" +
	163	" and a.actiontype ='审核' and a.curstatus = 'Undone'\n" +
	164	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	165	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	166	" insert into @table(ActionType,ActionCount)values('审核',@ActionCount)\n" +
	167
	168	" select @ActionCount=count(1) \n" +
	169	" from t219001 a with (nolock) \n" +
	170	" where a.UserCode =@userCode\n" +
c61217	171	" and a.actiontype ='抄送' and a.curstatus = 'Undone'\n" +
F	172	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	173	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	174	" insert into @table(ActionType,ActionCount)values('抄送',@ActionCount)\n" +
	175
	176	" select @ActionCount=count(1)\n" +
	177	" from t219001 a with (nolock)\n" +
	178	" where a.UserCode =@userCode \n" +
	179	" and a.actiontype = '通知' and isnull(a.isRead,0)=0\n" +
	180	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	181	" insert into @table(ActionType,ActionCount)values('通知',@ActionCount)\n" +
	182
	183	" select @ActionCount=count(1)\n" +
	184	" from t219001 a with (nolock)\n" +
	185	" where a.UserCode =@userCode\n" +
	186	" and a.actiontype = '在线交流' and isnull(a.isRead,0)=0\n" +
	187	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	188	" insert into @table(ActionType,ActionCount)values('在线交流',@ActionCount)\n" +
	189
	190	" select @MsgCount as msgCount,@todoCount as todoCount ,a.ActionType,a.ActionCount \n" +
	191	" from @table a ";
	192	return this.jdbcTemplate.queryForList(sql,usercode);
	193	}
	194	@Override
	195	public List<Map<String, Object>> getMessageByUserCodeNumV2(String usercode) {
	196	// String sql = "set nocount on \n select Count(1) as msgCount from t219001 a where curstatus = 'Undone' and ActionType in ('审核','抄送','在线交流','通知') and UserCode =?";
	197	String sql="set nocount on \n" +
	198	" declare @userCode varchar(50)=?\n" +
	199	" declare @table table(ActionType varchar(50),ActionCount int) \n" +
	200	" declare @MsgCount int \n" +
	201	" declare @ActionCount int \n" +
	202	" declare @todoCount int \n" +
	203
	204	" select @ActionCount=count(1) \n" +
	205	" from t219001 a with (nolock) \n" +
	206	" where a.UserCode =@userCode\n" +
	207	" and a.actiontype ='审核' and a.curstatus = 'Undone'\n" +
	208	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	209	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	210	" insert into @table(ActionType,ActionCount)values('审核',@ActionCount)\n" +
	211
	212	" select @ActionCount=count(1) \n" +
	213	" from t219001 a with (nolock) \n" +
	214	" where a.UserCode =@userCode\n" +
2b4227	215	" and a.actiontype ='抄送' \n" +
a6a76f	216	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
F	217	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
2b4227	218	" insert into @table(ActionType,ActionCount)values('抄送我的',@ActionCount)\n" +
a6a76f	219
F	220	" select @ActionCount=count(1)\n" +
	221	" from t219001 a with (nolock)\n" +
	222	" where a.UserCode =@userCode \n" +
	223	" and a.actiontype = '通知' and isnull(a.isRead,0)=0\n" +
	224	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	225	" insert into @table(ActionType,ActionCount)values('通知',@ActionCount)\n" +
	226
	227	" select @ActionCount=count(1)\n" +
	228	" from t219001 a with (nolock)\n" +
	229	" where a.UserCode =@userCode\n" +
	230	" and a.actiontype = '在线交流' and isnull(a.isRead,0)=0\n" +
	231	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	232	" insert into @table(ActionType,ActionCount)values('在线交流',@ActionCount)\n" +
	233
	234	" select @MsgCount as msgCount,@todoCount as todoCount ,a.ActionType,a.ActionCount \n" +
	235	" from @table a ";
	236	return this.jdbcTemplate.queryForList(sql,usercode);
	237	}
	238	@Override
	239	public int updatePush(String uuid) {
	240
	241	return this.jdbcTemplate.queryForObject("set nocount on \n update t219001 set isPush=1 where unid=?;select @@ROWCOUNT", new Object[]{uuid}, Integer.class);
	242	}
	243
	244	@Override
	245	public List<Map<String, Object>> getProcessInfo(String formid, String doccode) {
	246	String sql = "set nocount on \n select username,inserttime,msg,case when buttontype='提交' then case when isnull(nextcheckerCode,'')<>'' then '发起申请' else '已结束审核' end when buttontype='通过' then '已同意' else curstatus end as curstatus, case when len( isnull(username,''))>2 then \n" +
	247	" SUBSTRING(username,len( username)-1, len( username)) \n" +
	248	" else\n" +
	249	" username \n" +
	250	" end \n" +
	251	" as imgName "
	252	+ " from t219002 with (nolock) where doccode=? and formid=? order by doccode,inserttime asc ";
	253	return this.jdbcTemplate.queryForList(sql, doccode, formid);
	254	}
	255
	256	@Override
	257	public String getDomain_Pex(String dbid) {
	258
	259	return this.jdbcTemplate.queryForObject("set nocount on \n select domain from gsystem where id=?", String.class, dbid);
	260	}
	261
	262	public String getPic_Seq(String uuid, int type) {
	263	String tableName = "_sys_Attachment";
	264	if (type == 9) tableName += type;
	265	String sql = "set nocount on \n SELECT DISTINCT STUFF(( SELECT ';'+cast(seq as varchar(10)) FROM [dbo]." + tableName
	266	+ " WHERE unid = A.unid FOR XML PATH('') ) ,1,1,'' )AS seq FROM [dbo]." + tableName
	267	+ " as A where unid=?";
	268	return this.jdbcTemplate.queryForObject(sql, String.class, uuid);
	269	}
	270
	271	@Override
	272	public List<Map<String, Object>> getMessageByType(MessageParameter messageParameter) {
	273	//String where=" where UserCode = '"+messageParameter.getUserCode()+"'";
	274	if ("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 2) {//我发起的
	275	messageParameter.setActionType("提交");
	276	;
2b4227	277	} else if ("抄送我的".equals(messageParameter.getActionType())\|\|("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 3)) {//抄送我的
a6a76f	278	messageParameter.setActionType("抄送");
F	279	}
	280
	281	String sql = " set nocount on \n declare @UserCode varchar(20) =? , @HrCode varchar(20) = ? ,\n" +
	282	" @SearchKey varchar(50) =?,@ActionType varchar(50)=?,@FilterFormId int =?\n" +
	283
	284
	285	" select a.FormId,a.FormName,a.PeriodType,a.FormIdCount,\n" +
571d4b	286	" (select largImagePath from _sysmenu m where a.formid = m.formid ) as largImagePath \n" +
a6a76f	287	" from (\n" +
F	288	" select a.FormId,b.FormName,c.PeriodType,sum(isnull(a.FormIdCount,0)) as FormIdCount\n" +
	289	" from (" +
	290	" select a.FormId,count(1) as FormIdCount\n" +
	291	" from t219001 a with (nolock) \n" +
	292	" where a.UserCode =@UserCode\n" +
	293	" and (isnull(@SearchKey,'') = ''\n" +
	294	" or a.QueryString like '%' + @SearchKey + '%' \n" +
	295	" or a.topic like '%' + @SearchKey + '%' \n" +
	296	" or a.DocStatusName like '%' + @SearchKey + '%' \n" +
	297	" or a.DocType like '%' + @SearchKey + '%' \n" +
	298	" or a.CurcheckerName like '%' + @SearchKey + '%' )\n" +
	299	" and (isnull(@ActionType,'') = '' or a.ActionType in (select list from GetInStr( @ActionType)) )\n" +
	300	" and (isnull(@FilterFormId,0) = 0 or a.formid = isnull(@FilterFormId,0))\n";
	301	if (messageParameter.getIsfinshed() == 1) {
	302	sql += " and curstatus = 'Undone' \n";
	303	}else if (messageParameter.getIsfinshed() == 2) {
	304	sql += " and curstatus = 'Finished' \n";
	305	}else {
	306	//在线交流，通知
	307	if(messageParameter.getMsgType()==4) {
	308	//未读
	309	sql += " and isnull(isRead,0) = 0 \n";
	310	}
	311	else if(messageParameter.getMsgType()==5) {
	312	//已读
	313	sql += " and isnull(isRead,0) = 1 \n";
	314	}
	315
	316	}
	317	sql +=" group by a.FormId \n"+
	318	" ) a \n" +
	319	" join gform b with (nolock) on a.formid = b.formid \n" +
	320	" left join gdoctype c with (nolock) on a.formid = c.formid\n" +
	321	" group by a.FormId,b.FormName,c.PeriodType) a";
	322
	323
	324	return this.jdbcTemplate.queryForList(sql, messageParameter.getUserCode(), messageParameter.getHrCode(), messageParameter.getSearchKey(), messageParameter.getActionType(), messageParameter.getFormid());
	325
	326	}
	327	}