eCoWorksV3.git - Gitblit

fs-danaus

2022-07-26 5783692a635adfdd330d23b4eb31df2eca5ceb77

提交 \| 用户 \| 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()+"'";
578369	74	String actionType="";
F	75	if ("抄送我的".equals(messageParameter.getActionType())){
	76	actionType=messageParameter.getActionType();
	77	}
a6a76f	78	if ("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 2) {//我发起的
F	79	messageParameter.setActionType("提交");
2b4227	80	} else if ("抄送我的".equals(messageParameter.getActionType())\|\|("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 3)) {//抄送我的
a6a76f	81	messageParameter.setActionType("抄送");
F	82	}
	83
	84	// 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 ";
	85
	86	String sql = " set nocount on \n declare @UserCode varchar(20) =? , @HrCode varchar(20) = ? ,\n" +
	87	" @SearchKey varchar(50) =?,@ActionType varchar(50)=?,@FilterFormId int =?\n" +
	88	" declare @Limit int = 20 ,@today datetime = getdate()\n" +
	89	" declare @Page int =? \n " +
	90
	91
	92	" select isnull(a.doccode,'') as doccode ,isnull(b.formid,0) as formid,isnull(b.formname,'') as formname,isnull(a.formtype,0) as formtype, \n" +
	93	" '<a onclick=''postT219001(\"'+isnull(b.formname,'')+'\",\"\",\"\",\"\",\"/NeedWork.do?UNID=' + convert(varchar(100),UNID) + \n" +
	94	" '&formId=' + convert(varchar(10),a.formid) + \n" +
	95	" '&doccode='+ isnull(doccode,'') + \n" +
	96	" '&actionType='+ isnull(ActionType,'') + \n" +
	97	" '&formType=' + convert(varchar(10),a.formtype) + \n" +
	98	" '\");'' style=\"text-decoration:underline;cursor: pointer;color: ' + \n" +
	99	" 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" +
	100	" isnull(topic,'') as topic ,\n" +
	101	" \n" +
	102	" dbo.WebGetInStr(a.doccode,'219003','22','删除',@usercode+';'+ cast (a.unid as varchar(64)) ,'usercode;unid','1') as DelTodo , \n" +
	103	" a.inserttime ,isnull(a.isRead,0) as isRead,a.UNID,isnull(a.curstatus,'') as curstatus ,\n" +
	104	" isnull(a.usercode,'') as usercode,isnull(a.username,'') as username,isnull(a.msgfrom,0) as msgfrom,\n" +
	105	" case when DATEDIFF(day,a.inserttime,@today) = 0 then '今天'\n" +
	106	" when DATEDIFF(day,a.inserttime,@today) = 1 then '昨天'\n" +
	107	" when DATEDIFF(day,a.inserttime,@today) between 2 and 6 then DATENAME(weekday,a.inserttime) \n" +
	108	" when DATEDIFF(day,a.inserttime,@today) between 7 and 13 then '上周'\n" +
	109	" else '更早'\n" +
	110	" end as inserttimedesc,\n" +
	111	" 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" +
	112	" 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" +
	113	" 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	114	" (select isnull(largImagePath,'') as largImagePath from _sysmenu m where a.formid = m.formid ) as largImagePath \n" +
a6a76f	115	" from t219001 a with (nolock) join gform b with (nolock) on a.formid = b.formid \n" +
F	116	" left join gdoctype c with (nolock) on a.formid = c.formid \n" +
	117	" where UserCode = @UserCode \n";
	118
	119	if (messageParameter.getIsfinshed() == 1) {
578369	120	sql += " and curstatus = 'Undone' and isnull(a.isRead,0) = 0\n";
a6a76f	121	}
578369	122	else if (messageParameter.getIsfinshed() == 2) {
F	123	sql += " and curstatus = 'Finished' and isnull(a.isRead,0) = 0 \n";
a6a76f	124	}
578369	125	else if (messageParameter.getIsfinshed() == 3\|\|("抄送我的".equals(actionType)&&messageParameter.getIsfinshed()==0)) {
a6a76f	126	//在线交流，通知
F	127	if(messageParameter.getMsgType()==4) {
	128	//未读
	129	sql += " and isnull(a.isRead,0) = 0 \n";
	130	}
	131	else if(messageParameter.getMsgType()==5) {
	132	//已读
	133	sql += " and isnull(a.isRead,0) = 1 \n";
	134	}
	135	}
	136	sql +=
	137	" and (isnull(@SearchKey,'') = ''\n" +
	138	" or a.QueryString like '%' + @SearchKey + '%' \n" +
	139	" or a.topic like '%' + @SearchKey + '%' \n" +
	140	" or a.DocStatusName like '%' + @SearchKey + '%' \n" +
	141	" or a.DocType like '%' + @SearchKey + '%' \n" +
	142	" or a.CurcheckerName like '%' + @SearchKey + '%' )\n" +
	143	" and (isnull(@ActionType,'') = '' or a.ActionType in (select list from GetInStr( @ActionType)) )\n" +
	144	" and (isnull(@FilterFormId,0) = 0 or a.formid = isnull(@FilterFormId,0))\n" +
	145	" order by isnull(isRead,0) asc,inserttime desc\n" +
	146	" OFFSET (isnull(@Page,0) - 1) * isnull(@Limit,0) ROWS \n" +
	147	" FETCH NEXT @Limit ROWS ONLY;\n";
	148
	149
	150	return this.jdbcTemplate.queryForList(sql, messageParameter.getUserCode(), messageParameter.getHrCode(), messageParameter.getSearchKey(), messageParameter.getActionType(), messageParameter.getFormid(), messageParameter.getPageIndex());
	151	}
	152
	153	@Override
	154	public List<Map<String, Object>> getMessageByUserCodeNum(String usercode) {
	155	// String sql = "set nocount on \n select Count(1) as msgCount from t219001 a where curstatus = 'Undone' and ActionType in ('审核','抄送','在线交流','通知') and UserCode =?";
	156	String sql="set nocount on \n" +
	157	" declare @userCode varchar(50)=?\n" +
	158	" declare @table table(ActionType varchar(50),ActionCount int) \n" +
	159	" declare @MsgCount int \n" +
	160	" declare @ActionCount int \n" +
	161	" declare @todoCount int \n" +
	162
	163	" select @ActionCount=count(1) \n" +
	164	" from t219001 a with (nolock) \n" +
	165	" where a.UserCode =@userCode\n" +
	166	" and a.actiontype ='审核' and a.curstatus = 'Undone'\n" +
	167	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	168	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	169	" insert into @table(ActionType,ActionCount)values('审核',@ActionCount)\n" +
	170
	171	" select @ActionCount=count(1) \n" +
	172	" from t219001 a with (nolock) \n" +
	173	" where a.UserCode =@userCode\n" +
c61217	174	" and a.actiontype ='抄送' and a.curstatus = 'Undone'\n" +
F	175	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	176	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	177	" insert into @table(ActionType,ActionCount)values('抄送',@ActionCount)\n" +
	178
	179	" select @ActionCount=count(1)\n" +
	180	" from t219001 a with (nolock)\n" +
	181	" where a.UserCode =@userCode \n" +
	182	" and a.actiontype = '通知' and isnull(a.isRead,0)=0\n" +
	183	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	184	" insert into @table(ActionType,ActionCount)values('通知',@ActionCount)\n" +
	185
	186	" select @ActionCount=count(1)\n" +
	187	" from t219001 a with (nolock)\n" +
	188	" where a.UserCode =@userCode\n" +
	189	" and a.actiontype = '在线交流' and isnull(a.isRead,0)=0\n" +
	190	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	191	" insert into @table(ActionType,ActionCount)values('在线交流',@ActionCount)\n" +
	192
	193	" select @MsgCount as msgCount,@todoCount as todoCount ,a.ActionType,a.ActionCount \n" +
	194	" from @table a ";
	195	return this.jdbcTemplate.queryForList(sql,usercode);
	196	}
	197	@Override
	198	public List<Map<String, Object>> getMessageByUserCodeNumV2(String usercode) {
	199	// String sql = "set nocount on \n select Count(1) as msgCount from t219001 a where curstatus = 'Undone' and ActionType in ('审核','抄送','在线交流','通知') and UserCode =?";
	200	String sql="set nocount on \n" +
	201	" declare @userCode varchar(50)=?\n" +
	202	" declare @table table(ActionType varchar(50),ActionCount int) \n" +
	203	" declare @MsgCount int \n" +
	204	" declare @ActionCount int \n" +
	205	" declare @todoCount int \n" +
	206
	207	" select @ActionCount=count(1) \n" +
	208	" from t219001 a with (nolock) \n" +
	209	" where a.UserCode =@userCode\n" +
	210	" and a.actiontype ='审核' and a.curstatus = 'Undone'\n" +
	211	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	212	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
	213	" insert into @table(ActionType,ActionCount)values('审核',@ActionCount)\n" +
	214
	215	" select @ActionCount=count(1) \n" +
	216	" from t219001 a with (nolock) \n" +
	217	" where a.UserCode =@userCode\n" +
578369	218	" and a.actiontype ='抄送' and isnull(a.isRead,0)=0\n" +
a6a76f	219	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
F	220	" set @todoCount=isnull(@todoCount,0)+isnull(@ActionCount,0)\n" +
2b4227	221	" insert into @table(ActionType,ActionCount)values('抄送我的',@ActionCount)\n" +
a6a76f	222
F	223	" select @ActionCount=count(1)\n" +
	224	" from t219001 a with (nolock)\n" +
	225	" where a.UserCode =@userCode \n" +
	226	" and a.actiontype = '通知' and isnull(a.isRead,0)=0\n" +
	227	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	228	" insert into @table(ActionType,ActionCount)values('通知',@ActionCount)\n" +
	229
	230	" select @ActionCount=count(1)\n" +
	231	" from t219001 a with (nolock)\n" +
	232	" where a.UserCode =@userCode\n" +
	233	" and a.actiontype = '在线交流' and isnull(a.isRead,0)=0\n" +
	234	" set @MsgCount=isnull(@MsgCount,0)+isnull(@ActionCount,0)\n" +
	235	" insert into @table(ActionType,ActionCount)values('在线交流',@ActionCount)\n" +
	236
	237	" select @MsgCount as msgCount,@todoCount as todoCount ,a.ActionType,a.ActionCount \n" +
	238	" from @table a ";
	239	return this.jdbcTemplate.queryForList(sql,usercode);
	240	}
	241	@Override
	242	public int updatePush(String uuid) {
	243
	244	return this.jdbcTemplate.queryForObject("set nocount on \n update t219001 set isPush=1 where unid=?;select @@ROWCOUNT", new Object[]{uuid}, Integer.class);
	245	}
	246
	247	@Override
	248	public List<Map<String, Object>> getProcessInfo(String formid, String doccode) {
	249	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" +
	250	" SUBSTRING(username,len( username)-1, len( username)) \n" +
	251	" else\n" +
	252	" username \n" +
	253	" end \n" +
	254	" as imgName "
	255	+ " from t219002 with (nolock) where doccode=? and formid=? order by doccode,inserttime asc ";
	256	return this.jdbcTemplate.queryForList(sql, doccode, formid);
	257	}
	258
	259	@Override
	260	public String getDomain_Pex(String dbid) {
	261
	262	return this.jdbcTemplate.queryForObject("set nocount on \n select domain from gsystem where id=?", String.class, dbid);
	263	}
	264
	265	public String getPic_Seq(String uuid, int type) {
	266	String tableName = "_sys_Attachment";
	267	if (type == 9) tableName += type;
	268	String sql = "set nocount on \n SELECT DISTINCT STUFF(( SELECT ';'+cast(seq as varchar(10)) FROM [dbo]." + tableName
	269	+ " WHERE unid = A.unid FOR XML PATH('') ) ,1,1,'' )AS seq FROM [dbo]." + tableName
	270	+ " as A where unid=?";
	271	return this.jdbcTemplate.queryForObject(sql, String.class, uuid);
	272	}
	273
	274	@Override
	275	public List<Map<String, Object>> getMessageByType(MessageParameter messageParameter) {
	276	//String where=" where UserCode = '"+messageParameter.getUserCode()+"'";
578369	277	String actionType="";
F	278	if ("抄送我的".equals(messageParameter.getActionType())){
	279	actionType=messageParameter.getActionType();
	280	}
a6a76f	281	if ("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 2) {//我发起的
F	282	messageParameter.setActionType("提交");
	283	;
2b4227	284	} else if ("抄送我的".equals(messageParameter.getActionType())\|\|("审核".equals(messageParameter.getActionType()) && messageParameter.getMsgType() == 3)) {//抄送我的
a6a76f	285	messageParameter.setActionType("抄送");
F	286	}
	287
	288	String sql = " set nocount on \n declare @UserCode varchar(20) =? , @HrCode varchar(20) = ? ,\n" +
	289	" @SearchKey varchar(50) =?,@ActionType varchar(50)=?,@FilterFormId int =?\n" +
	290
	291
	292	" select a.FormId,a.FormName,a.PeriodType,a.FormIdCount,\n" +
571d4b	293	" (select largImagePath from _sysmenu m where a.formid = m.formid ) as largImagePath \n" +
a6a76f	294	" from (\n" +
F	295	" select a.FormId,b.FormName,c.PeriodType,sum(isnull(a.FormIdCount,0)) as FormIdCount\n" +
	296	" from (" +
	297	" select a.FormId,count(1) as FormIdCount\n" +
	298	" from t219001 a with (nolock) \n" +
	299	" where a.UserCode =@UserCode\n" +
	300	" and (isnull(@SearchKey,'') = ''\n" +
	301	" or a.QueryString like '%' + @SearchKey + '%' \n" +
	302	" or a.topic like '%' + @SearchKey + '%' \n" +
	303	" or a.DocStatusName like '%' + @SearchKey + '%' \n" +
	304	" or a.DocType like '%' + @SearchKey + '%' \n" +
	305	" or a.CurcheckerName like '%' + @SearchKey + '%' )\n" +
	306	" and (isnull(@ActionType,'') = '' or a.ActionType in (select list from GetInStr( @ActionType)) )\n" +
	307	" and (isnull(@FilterFormId,0) = 0 or a.formid = isnull(@FilterFormId,0))\n";
	308	if (messageParameter.getIsfinshed() == 1) {
578369	309	sql += " and curstatus = 'Undone' and isnull(a.isRead,0) = 0 \n";
a6a76f	310	}else if (messageParameter.getIsfinshed() == 2) {
578369	311	sql += " and curstatus = 'Finished' and isnull(a.isRead,0) = 0 \n";
F	312	}else if (messageParameter.getIsfinshed() == 3\|\|("抄送我的".equals(actionType)&&messageParameter.getIsfinshed()==0)){
a6a76f	313	//在线交流，通知
F	314	if(messageParameter.getMsgType()==4) {
	315	//未读
	316	sql += " and isnull(isRead,0) = 0 \n";
	317	}
	318	else if(messageParameter.getMsgType()==5) {
	319	//已读
	320	sql += " and isnull(isRead,0) = 1 \n";
	321	}
	322	}
	323	sql +=" group by a.FormId \n"+
	324	" ) a \n" +
	325	" join gform b with (nolock) on a.formid = b.formid \n" +
	326	" left join gdoctype c with (nolock) on a.formid = c.formid\n" +
	327	" group by a.FormId,b.FormName,c.PeriodType) a";
	328
	329
	330	return this.jdbcTemplate.queryForList(sql, messageParameter.getUserCode(), messageParameter.getHrCode(), messageParameter.getSearchKey(), messageParameter.getActionType(), messageParameter.getFormid());
	331
	332	}
	333	}