门户网站数据库设计

1、门户网站数据库设计一、系统规则注意：在书写时大括号之间变量不能有空格时间常规显示格式：日期统一显示格式：YYYY-MM-DD日期时间统一显示格式：YYYY-MM-DDHH:NN:SS时间统一显示格式：HH:NN:SS系统变量：CurrDate）表示：系统当前日期；CurrDateTime）表示：系统当前日期时间；CurrTime）表示：系统当前时间；CurrYear）表示：系统当前年；CurrMonth）表示：系统当前月；CurrDay）表示：系统当前天；CurrHour）表示：系统当前小时；CurrMinute）表示：系统当前分钟；CurrSecond）表示：系统当前秒；CurrUserld

2、）表示：当前用户idCurrUserName）表示：当前用户姓名CurrUserDeptld）表示：当前用户部门idCurrUserDeptName）表示：当前用户部门名称CurrUserSubCompanyld）表示：当前用户所在分部IdCurrUserSubCompanyName）表示：当前用户所在分部名称CurrUserSubCompanyCode）表示：当前用户所在分部的机构编码CurrOnlineUserld）表示：当前系统在线人员id,如多人则用分割对应commonCperties文件各种变量（属于系统级变量）：SysCfgXWFWorkplan）:工作计划；对应

3、项workflow_workplanSysCfgXWFBumfin）:收文流程id;对应项workflow_workplanSysCfgXWFBumfout）:发文流程id;对应项workflow_workplanSysCfgDocAfficheld）:通知公告SysCfgDocNewDynamicld）:新闻动态SysCfgHrmCompanyLeaderLevel）:联社领导层（理事长、监事长、主任、副主任）SysCfgHrmDeptAndSameOrganLeaderLevel）:部门领导层（含信用社领导层）SysCfgRoleCptManager）:对应项workflow_workpl

4、anSysCfgRoleMeetingManager）:对应项workflow_workplanSysCfgRoleDocManager）:对应项workflow_workplan首页提醒变量（仅限制在首页提醒模块）PerRowMainld：每条记录的主键值。PerRowMainSubject：每条记录的主题。PerRowMainDateTime:每条记录的日期时间；显示具体的日期时间需要由用户决定。ThisModuleRows:获得本模块设置的"lngrows”字段值PerRowMainReadFlag:每条记录“阅读"的标记。PageParamenter1：获得本模块设

5、置的“PageParamenter2：获得本模块设置的“PageParamenter3：获得本模块设置的“PageParamenter4：获得本模块设置的“lngrows"字段值。（保留字暂不实现）lngrows"字段值。（保留字暂不实现）lngrows"字段值。（保留字暂不实现）lngrows"字段值。（保留字暂不实现）二、系统模块表SysMainModule首页提醒信息表lngmoduleid:主键strmodulename：名称strurl：本模块显示的信息内容由本地址决定，是对复杂显示效果的扩展。blnfixed:是否固定显示；1是固定，0是可以

6、自定义；lngorder：显示顺序lngrows:显示条数width:显示宽度height:显示高度layoutColumn:所属框架moreurl:更多连接titlelength:标题长度（单位：字数）strsql:被解析的sql;本sql带有模块内容显示的各项信息。newShowIconRule:显示news图标规则；空表示不显示图标；系统判断方法是查询SQL是否有值，若结果集为空则显示图标说明有最新信息,否则不显示。字段为空不做声音提醒。sql示例：selectPerRowMainIdfromtablenamewhereid=PerRowMainIdselectPerRowMainDat

7、efromtablenamewhereCurrDate=PerRowMainDatesoundRemindRule:声音提醒规则；空表示不提醒；系统判断方法是查询SQL是否有值，若集合为空则声音提醒，否则不提醒。字段为空不做声音提醒。sql示例：selectPerRowMainldfromtablenamewhereid=PerRowMainldselectPerRowMainDatefromtablenamewhereCurrDate=PerRowMainDatemoduleTotalSQL：信息统计sql,如果存在多个统计sql请用“分割开；如：待办疣程（待办共1口，办结共5）待办共10,

8、办结共5表示方法是：待办共Aselectcount（*）fromtablename|办结共Aselectcount（*）fromtablenamewhereread='1andhrmid=CurrUserIdpageld:所属页面id（是SysMainPage外键）newlcon：图标显示规则（1结果集有值显示，2结果集空时显示）。soundRemind:声音提醒规则（1结果集有彳1提醒，2结果集空值提醒）判断方式：A）如果strurl不为空程序获得这个地址对应界面的结果作为模块要显示的内容。B）否则如果为空则执行strsql字段值，如果这个字段同时为空则表示此模块为无效模块。C）st

9、rsql字段不为空，则开始解析sql;解析原则先解析后填充值。注意：本首页提醒模块中定义的变量支持“系统规则“。SysMainSetting自定义提醒信息表lngsettingid:自定义提醒表主键lngmoduleid:首页提醒表的外键lnghrmid:相关人员lngrows:显示行数width:模块显示宽度height:模块显示高度layoutColumn:所属框架titlelength:主题显示长度（单位：字数）lngorder：顺序SysMainPage自定义页面表id:主键name：页面名称；lngorder：显示顺序pageurl：转向指定页面的链接地址和传参；fixed：是否强制

10、性显示；1是强制显示，0是可以自定义urltype:数据显示类型；1是来自页面模块，2来自外部地址，3来自知识库某文档内容。docid:相关文档idSysMainRelation页面关系表mainid:模块id;hrmid:人员idflag:标记：(0表示mainid是SysMainPage外键，1表示mainid是SysMainModule外键)1权限信息名称所属权限组权限编码描述首页设置首页设置一系统管理员ModulePage:Manage拥有此权限者可以增删改本模块信息模块设置模块设置一系统管理员Module:Manage拥有此权限者可以增删改本模块信息2特殊模块配置信

11、息文档参数：docsubject：主题doccontent：文档内容containreply:maincategory:一级目录subcategory：二级目录seccategory：三级目录docid:文档iddoccreaterid：文档创建者departmentiddoclanguragehrmresidcrmidprojectedfinanceiddocpublishtypedocstatuskeyword:ownerid:创建者isreply:是否有回复docno：文档编号doccreatedatefrom-doccreatedateto:创建文档开始时间与U结束时间通知公告页面地址

12、/docs/docs/DocDsp.jsp?id=PerRowMainId更多链接/docs/docs/DocDsp.jsp?id=PerRowMainId内容显示规则selecttop5idasPerRowMainId,doclastmoddateasPerRowMainDateTime,docsubjectasPerRowMainSubjectfromDocDetailt1,DocShareDetailt2wheret1.replydocid=0andt1.docstatusin('1','2','5')andt1.seccategoryin

13、(SysCfgDocAfficheId)andt1.id=t2.docidandt2.userid=CurrUserIdandt2.usertype=1orderbydoclastmoddatedesc,doclastmodtimedescNews规贝Uselecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrUserId声音提醒规则selecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrUserId信息统计规则共：Ase

14、lectcount(distinctid)fromDocDetailast1,DocShareDetailast2wheredocstatusin('1','2','5')andseccategoryin(SysCfgDocAfficheId)andt1.id=t2.docidandt2.userid=CurrUserIdandt2.usertype=1待办流程页面地址Zworkflow/request/ManageRequest.jsp?requestid=PerRowMainId更多链接/workflow/search/WFSearchRes

15、ult.jsp?start=1&perpage=10内容显示规则selectdistincttopThisModuleRowst1.requestidasPerRowMainId,createdateasPerRowMainDateTime,requestnameasPerRowMainSubject,t1.createtime,t1.requestlevelfromworkflow_requestbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.use

16、rtype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidnotin(SysCfgXWFBumfin,SysCfgXWFBumfout)orderbyt1.createdatedesc,t1.requestleveldesc,t1.createtimedescNews规贝Uselectt1.requestidfromworkflow_requestbaset1,workflow_currentoperatort2wheret1.requ

17、estid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidnotin(SysCfgXWFBumfin,SysCfgXWFBumfout)andcreatedate='CurrDate'声音提醒规则selectt1.requestidfromworkflow_requestbaset1,workflow_currentope

18、ratort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidnotin(SysCfgXWFBumfin,SysCfgXWFBumfout)andcreatedate='CurrDate'信息统计规则待办共：Aselectcount(distinctt1.requestid)fromwor

19、kflow_requestbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidnotin(SysCfgXWFBumfin,SysCfgXWFBumfout)|,办结共：Aselectcount(distinctt1.requestid)from

20、workflow_requestbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype=3andt1.workflowidnotin(SysCfgXWFBumfin,SysCfgXWFBumfout)待办公文页面地址/workflow/request/ManageRequest.jsp?requ

21、estid=PerRowMainId更多链接/workflow/search/WFSearchResult.jsp?start=1&perpage=10内容显示规则selectdistincttopThisModuleRowst1.requestidasPerRowMainId,createdateasPerRowMainDateTime,requestnameasPerRowMainSubject,t1.requestlevel,createtimefromworkflow_requestbaset1,workflow_currentoperatort2wheret1.request

22、id=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidin(SysCfgXWFBumfin,SysCfgXWFBumfout)orderbycreatedatedesc,t1.requestleveldesc,createtimedescNews规贝Uselectdistincttop1t1.requestidfromworkflow_re

23、questbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidin(SysCfgXWFBumfin,SysCfgXWFBumfout)andcreatedate='CurrDate'声音提醒规则信息统计规则待办共：Aselect

24、count(distinctt1.requestid)fromworkflow_requestbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype<>3andt1.workflowidin(SysCfgXWFBumfin,SysCfgXWFBumfout)|,办结共Aselectc

25、ount(distinctt1.requestid)fromworkflow_requestbaset1,workflow_currentoperatort2wheret1.requestid=t2.requestidandt2.userid=CurrUserIdandt2.usertype=0andt2.isremarkin('0','1')andt1.deleted=0andt1.currentnodetype=3andt1.workflowidin(SysCfgXWFBumfin,SysCfgXWFBumfout)最新文档页面地址/docs/docs/Do

26、cDsp.jsp?id=PerRowMainId更多链接/docs/search/DocSearchTemp.jsp?list=all内容显示规则selecttop5idasPerRowMainId,docsubjectasPerRowMainSubject,doccreatedateasPerRowMainDateTimefromDocDetailt1,DocShareDetailt2wheret1.replydocid=0andt1.docstatusin('1','2','5')andt1.id=t2.docidandt2.userid=C

27、urrUserIdandseccategorynotin(SysCfgDocAfficheId)andt2.usertype=1orderbydoccreatedatedesc,doccreatetimedescNews规贝Uselecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrUserId声音提醒规则selecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrUserId信息统计规则共：Aselectcount(dist

28、inctid)fromDocDetailast1,DocShareDetailast2wheredocstatusin('1','2','5')andt1.id=t2.docidandt2.userid=CurrUserIdandt2.usertype=1未读邮件页面地址Zfunction/Email.do?method=showEmail&id=PerRowMainId更多链接/function/Email.do?method=showInBoxlist内容显示规则selecttopThisModuleRowsidasPerRowMai

29、nId,senddateasPerRowMainDateTime,titleasPerRowMainSubjectfromEMailListswherefolderid='1'andstatus='0'andhrmid=CurrUserIdorderbyiddescNews规贝Uselecttop1titlefromEMailListswherefolderid='1'andstatus='0'andhrmid=CurrUserId声音提醒规则selecttop1titlefromEMailListswherefolderid=&

30、#39;1'andstatus='0'andhrmid=CurrUserId信息统计规则未读共：Aselectcount(id)fromEMailListswherefolderid='1'andstatus='0'andhrmid=CurrUserId|,已读共：Aselectcount(id)fromEMailListswherefolderid='1'andstatus='1'andhrmid=CurrUserId最新会议页面地址Zmeeting/data/ViewMeeting.jsp?meetin

31、gid=PerRowMainId更多链接Zmeeting/search/SearchResult.jsp?start=1&perpage=15内容显示规则selecta.idasPerRowMainId,asPerRowMainSubject,a.begindate+'('+a.begintime+'-'+a.endtime+')'asPerRowMainDateTimefromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'an

32、db.memberid=CurrUserIdorderbyPerRowMainDateTimedescNews规贝Uselecttop1a.idfromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'andb.memberid=CurrUserIdanda.createdate='CurrDate'声音提醒规则信息统计规则共：Aselectcount(a.id)fromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isappr

33、oved='3'andb.memberid=CurrUserId最新会议页面地址/meeting/data/ViewMeeting.jsp?meetingid=PerRowMainId更多链接/meeting/search/SearchResult.jsp?start=1&perpage=15内容显示规则selecta.idasPerRowMainId,asPerRowMainSubject,a.begindate+'('+a.begintime+'-'+a.endtime+')'asPerRowMainDat

34、eTimefromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'andb.memberid=CurrUserIdorderbyPerRowMainDateTimedescNews规贝Uselecttop1a.idfromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'andb.memberid=CurrUserIdanda.createdate='CurrDate'声音提醒规则信息统

35、计规则共：Aselectcount(a.id)fromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'andb.memberid=CurrUserId我的项目（任务）页面地址/proj/data/ViewProject.jsp?ProjID=PerRowMainId更多链接/proj/search/SearchResult.jsp?start=1&perpage=15内容显示规则selectdistincttopThisModuleRowst1.idasPerRowMainId,t1.na

36、measPerRowMainSubject,t1.createdateasPerRowMainDateTimefromPrj_ProjectInfot1,PrjShareDetailt2wheret1.id=t2.prjidandt2.usertype=1andt2.userid=CurrUserIdorderbyt1.iddescNews规贝Uselectdistincttopt1.idfromPrj_ProjectInfot1,PrjShareDetailt2wheret1.id=t2.prjidandt2.usertype=1andt2.userid=CurrUserIdandt1.cr

37、eatedate='CurrDate'声音提醒规则信息统计规则共:Aselectcount(distincttl.id)fromPrj_ProjectInfot1,PrjShareDetailt2wheretl.id=t2.prjidandt2.usertype=1andt2.userid=CurrUserId我的设备页面地址Zcpt/capital/CptCapital.jsp?id=PerRowMainId更多链接/cpt/search/CptSearchResult.jsp?type=search&isdata=2内容显示规则selecttopThisModule

38、Rowstl.idasPerRowMainId,asPerRowMainSubject,t1.createdateasPerRowMainDateTimefromcptCapitalt1,cptShareDetailt2whereisdata='2'andt1.id=t2.cptidandt2.userid=CurrUserIdandt2.usertype=1andt1.stateidNOTIN(5,-7)orderbyt1.iddescNews规贝U声音提醒规则信息统计规则我的日程页面地址/function/quickplan/QuickPlanEdit.jsp

39、?id=PerRowMainId更多链接/function/quickplan/QuickPlanSearchResult.jsp?resourceid=CurrUserId&type=result内容显示规则selecttopThisModuleRowsidasPerRowMainId,subjectasPerRowMainSubject,begindateasPerRowMainDateTimefromQuickPlanwhereresourceid=CurrUserIdorderbyiddescNews规贝U声音提醒规则信息统计规则我的客户页面地址/CRM/data/ViewCu

40、stomer.jsp?CustomerID=PerRowMainId更多链接/search/SearchResult.jsp?pagenum=1&perpage=10内容显示规则News规贝U声音提醒规则信息统计规则部门文档页面地址/docs/docs/DocDsp.jsp?id=PerRowMainId更多链接/docs/search/DocSearchTemp.jsp?list=all&subcategory=156内容显示规则按所属部门查询selectdistincttopThisModuleRowsidasPerRowMainId,docsubjectasPerRowM

41、ainSubject,doccreatedateasPerRowMainDateTime,doccreatetimefromDocDetailt1,DocShareDetailt2wheret1.replydocid=0andt1.docstatusin('1','2','5')andt1.id=t2.docidandt2.useridin(selectidfromhrmresourcewheredepartmentid=CurrUserDeptId)andsubcategory=156andt2.usertype=1orderbydoccrea

42、tedatedesc,doccreatetimedesc按分部查询selectdistincttopThisModuleRowsidasPerRowMainId,docsubjectasPerRowMainSubject,doccreatedateasPerRowMainDateTime,doccreatetimefromDocDetailt1,DocShareDetailt2wheret1.replydocid=0andt1.docstatusin('1','2','5')andt1.id=t2.docidandt2.useridin(sele

43、ctidfromhrmresourcewheresubcompanyid1=CurrUserSubcCompanyId)andsubcategory=156andt2.usertype=1orderbydoccreatedatedesc,doccreatetimedescNews规贝Uselecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrUserId声音提醒规则selecttop1idfromdocdetaillogwheredocid=PerRowMainIdandoperateUserid=CurrU

44、serId信息统计规则按所属部门查询共：Aselectcount(distinctid)fromDocDetailast1,DocShareDetailast2wheredocstatusin('1','2','5')andt1.id=t2.docidandt2.useridin(selectidfromhrmresourcewheredepartmentid=CurrUserDeptId)andt2.usertype=1andsubcategory=164按分部查询共：Aselectcount(distinctid)fromDocDetaila

45、st1,DocShareDetailast2wheredocstatusin('1','2','5')andt1.id=t2.docidandt2.useridin(selectidfromhrmresourcewheresubcompanyid1=CurrUserSubcCompanyId)andt2.usertype=1andsubcategory=164部门会议页面地址/meeting/data/ViewMeeting.jsp?meetingid=PerRowMainId更多链接/meeting/search/SearchResult.js

46、p?start=1&perpage=15内容显示规则按所属部门查询SelectdistincttopThisModuleRowsa.idasPerRowMainId,asPerRowMainSubject,a.begindate+'('+a.begintime+'-'+a.endtime+')'asPerRowMainDateTimefromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3'andb.memberidin(selec

47、tidfromhrmresourcewheredepartmentid=CurrUserDeptId)orderbyPerRowMainDateTimedesc按分部查询SelectdistincttopThisModuleRowsa.idasPerRowMainId,asPerRowMainSubject,a.begindate+'('+a.begintime+'-'+a.endtime+')'asPerRowMainDateTimefromMeetinga,Meeting_Member2bwherea.id=b.meetingidanda.isapproved='3&