门户网站数据库设计.doc

门户网站数据库设计一、 系统规则注意：在书写时大括号之间变量不能有空格.时间常规显示格式：日期统一显示格式：YYYY-MM-DD日期时间统一显示格式：YYYY-MM-DD HH:NN:SS时间统一显示格式：HH:NN:SS系统变量：CurrDate表示： 系统当前日期；CurrDateTime表示：系统当前日期时间；CurrTime表示：系统当前时间；CurrYear表示： 系统当前年；CurrMonth表示： 系统当前月；CurrDay表示： 系统当前天；CurrHour表示： 系统当前小时；CurrMinute表示： 系统当前分钟；CurrSecond表示： 系统当前秒；CurrUserId表示：当前用户idCurrUserName表示：当前用户姓名CurrUserDeptId表示：当前用户部门idCurrUserDeptName表示：当前用户部门名称CurrUserSubCompanyId表示：当前用户所在分部IdCurrUserSubCompanyName表示：当前用户所在分部名称CurrUserSubCompanyCode表示：当前用户所在分部的机构编码CurrOnlineUserId表示：当前系统在线人员id，如多人则用“,”分割对应commonCperties文件各种变量（属于系统级变量）：SysCfgXWFWorkplan：工作计划；对应项workflow_workplanSysCfgXWFBumfin：收文流程id；对应项workflow_workplanSysCfgXWFBumfout：发文流程id；对应项workflow_workplanSysCfgDocAfficheId：通知公告 SysCfgDocNewDynamicId：新闻动态 SysCfgHrmCompanyLeaderLevel：联社领导层（理事长、监事长、主任、副主任）SysCfgHrmDeptAndSameOrganLeaderLevel ：部门领导层（含信用社领导层）SysCfgRoleCptManager：对应项workflow_workplanSysCfgRoleMeetingManager：对应项workflow_workplanSysCfgRoleDocManager：对应项workflow_workplan首页提醒变量（仅限制在首页提醒模块）PerRowMainId： 每条记录的主键值。PerRowMainSubject： 每条记录的主题。PerRowMainDateTime：每条记录的日期时间； 显示具体的日期时间需要由用户决定。ThisModuleRows：获得本模块设置的“lngrows”字段值PerRowMainReadFlag：每条记录“阅读“的标记。PageParamenter1：获得本模块设置的“lngrows”字段值。（保留字暂不实现） PageParamenter2：获得本模块设置的“lngrows”字段值。（保留字暂不实现） PageParamenter3：获得本模块设置的“lngrows”字段值。（保留字暂不实现） PageParamenter4：获得本模块设置的“lngrows”字段值。（保留字暂不实现）二、 系统模块表1 SysMainModule 首页提醒信息表1.1 lngmoduleid：主键1.2 strmodulename：名称1.3 strurl：本模块显示的信息内容由本地址决定，是对复杂显示效果的扩展。1.4 blnfixed：是否固定显示；1是固定，0是可以自定义；1.5 lngorder：显示顺序1.6 lngrows：显示条数1.7 width：显示宽度1.8 height：显示高度1.9 layoutColumn：所属框架1.10 moreurl：更多连接1.11 titlelength：标题长度（单位：字数）1.12 strsql：被解析的sql；本sql带有模块内容显示的各项信息。 newShowIconRule：显示news图标规则；空表示不显示图标； 系统判断方法是查询SQL是否有值,若结果集为空则显示图标说明有最新信息， 否则不显示。字段为空不做声音提醒。sql示例：select PerRowMainId from tablename where id= PerRowMainIdselect PerRowMainDate from tablename where CurrDate= PerRowMainDate 1.13 soundRemindRule：声音提醒规则；空表示不提醒；系统判断方法是查询SQL是否有值,若集合为空则声音提醒，否则不提醒。字段为空不做声音提醒。sql示例：select PerRowMainId from tablename where id= PerRowMainIdselect PerRowMainDate from tablename where CurrDate= PerRowMainDate 1.14 moduleTotalSQL：信息统计sql，如果存在多个统计sql请用“|”分割开；如：待办共10,办结共5表示方法是：待办共 select count(*) from tablename|办结共 select count(*) from tablename where read=1 and hrmid=CurrUserId1.15 pageId：所属页面id（是SysMainPage外键）1.16 newIcon：图标显示规则（1结果集有值显示，2结果集空时显示）。1.17 soundRemind：声音提醒规则（1结果集有值提醒，2结果集空值提醒）判断方式：A) 如果strurl不为空程序获得这个地址对应界面的结果作为模块要显示的内容。B) 否则如果为空则执行strsql字段值，如果这个字段同时为空则表示此模块为无效模块。C) strsql字段不为空，则开始解析sql；解析原则先解析后填充值。注意：本首页提醒模块中定义的变量支持“系统规则“。2 SysMainSetting 自定义提醒信息表2.1 lngsettingid：自定义提醒表主键2.2 lngmoduleid：首页提醒表的外键2.3 lnghrmid：相关人员2.4 lngrows：显示行数2.5 width：模块显示宽度2.6 height：模块显示高度 2.7 layoutColumn：所属框架2.8 titlelength：主题显示长度（单位：字数）2.9 lngorder：顺序3 SysMainPage 自定义页面表3.1 id：主键3.2 name：页面名称；3.3 lngorder：显示顺序3.4 pageurl：转向指定页面的链接地址和传参；3.5 fixed：是否强制性显示；1是强制显示，0是可以自定义3.6 urltype：数据显示类型；1是来自页面模块，2来自外部地址，3来自知识库某文档内容。3.7 docid：相关文档id3.8 4 SysMainRelation 页面关系表4.1 mainid：模块id；4.2 hrmid：人员id4.3 flag：标记：（0表示mainid是SysMainPage外键，1表示mainid是SysMainModule外键） 4.41 权限信息名称所属权限组权限编码描述首页设置首页设置系统管理员ModulePage:Manage拥有此权限者可以增删改本模块信息模块设置模块设置系统管理员Module:Manage拥有此权限者可以增删改本模块信息2 特殊模块配置信息文档参数：docsubject：主题doccontent：文档内容containreply：maincategory：一级目录subcategory：二级目录seccategory：三级目录docid：文档iddoccreaterid：文档创建者departmentiddoclanguragehrmresidcrmidprojectedfinanceiddocpublishtypedocstatuskeyword：ownerid：创建者isreply：是否有回复docno：文档编号doccreatedatefrom-doccreatedateto ：创建文档开始时间 到 结束时间通知公告页面地址/docs/docs/DocDsp.jsp?id=PerRowMainId更多链接/docs/docs/DocDsp.jsp?id=PerRowMainId内容显示规则select top 5 id as PerRowMainId,doclastmoddate as PerRowMainDateTime,docsubject as PerRowMainSubject from DocDetail t1,DocShareDetail t2 where t1.replydocid=0 and t1.docstatus in (1,2,5) and t1.seccategory in(SysCfgDocAfficheId) and t1.id=t2.docid and t2.userid=CurrUserId and t2.usertype=1 order by doclastmoddate desc, doclastmodtime descNews规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId声音提醒规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId信息统计规则共：select count(distinct id) from DocDetail as t1,DocShareDetail as t2 where docstatus in (1,2,5) and seccategory in(SysCfgDocAfficheId) and t1.id=t2.docid and t2.userid=CurrUserId and t2.usertype=1待办流程页面地址/workflow/request/ManageRequest.jsp?requestid=PerRowMainId更多链接/workflow/search/WFSearchResult.jsp?start=1&perpage=10内容显示规则select distinct top ThisModuleRows t1.requestid as PerRowMainId,createdate as PerRowMainDateTime,requestname as PerRowMainSubject,t1.createtime,t1.requestlevel from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid=t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in( 0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid not in (SysCfgXWFBumfin,SysCfgXWFBumfout) order by t1.createdate desc,t1.requestlevel desc,t1.createtime descNews规则select t1.requestid from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid=t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid not in (SysCfgXWFBumfin,SysCfgXWFBumfout) and createdate=CurrDate声音提醒规则select t1.requestid from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid=t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid not in (SysCfgXWFBumfin,SysCfgXWFBumfout) and createdate=CurrDate信息统计规则待办共:select count(distinct t1.requestid) from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid=t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in( 0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid not in (SysCfgXWFBumfin,SysCfgXWFBumfout)|,办结共:select count(distinct t1.requestid) from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid=t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in( 0,1) and t1.deleted=0 and t1.currentnodetype=3 and t1.workflowid not in (SysCfgXWFBumfin,SysCfgXWFBumfout)待办公文页面地址/workflow/request/ManageRequest.jsp?requestid=PerRowMainId更多链接/workflow/search/WFSearchResult.jsp?start=1&perpage=10内容显示规则select distinct top ThisModuleRows t1.requestid as PerRowMainId,createdate as PerRowMainDateTime,requestname as PerRowMainSubject,t1.requestlevel,createtime from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid = t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid in (SysCfgXWFBumfin,SysCfgXWFBumfout) order by createdate desc,t1.requestlevel desc,createtime descNews规则select distinct top 1 t1.requestid from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid = t2.requestid and t2.userid=CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid in (SysCfgXWFBumfin,SysCfgXWFBumfout) and createdate=CurrDate声音提醒规则信息统计规则待办共:select count( distinct t1.requestid) from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid = t2.requestid and t2.userid =CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype3 and t1.workflowid in (SysCfgXWFBumfin,SysCfgXWFBumfout)|,办结共select count( distinct t1.requestid) from workflow_requestbase t1,workflow_currentoperator t2 where t1.requestid = t2.requestid and t2.userid =CurrUserId and t2.usertype=0 and t2.isremark in(0,1) and t1.deleted=0 and t1.currentnodetype=3 and t1.workflowid in (SysCfgXWFBumfin,SysCfgXWFBumfout)最新文档页面地址/docs/docs/DocDsp.jsp?id=PerRowMainId更多链接/docs/search/DocSearchTemp.jsp?list=all内容显示规则select top 5 id as PerRowMainId,docsubject as PerRowMainSubject,doccreatedate as PerRowMainDateTime from DocDetail t1,DocShareDetail t2 where t1.replydocid=0 and t1.docstatus in (1,2,5) and t1.id=t2.docid and t2.userid=CurrUserId and seccategory not in(SysCfgDocAfficheId) and t2.usertype=1 order by doccreatedate desc,doccreatetime descNews规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId声音提醒规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId信息统计规则共：select count(distinct id) from DocDetail as t1,DocShareDetail as t2 where docstatus in (1,2,5) and t1.id=t2.docid and t2.userid=CurrUserId and t2.usertype=1未读邮件页面地址/function/Email.do?method=showEmail&id=PerRowMainId更多链接/function/Email.do?method=showInBoxlist内容显示规则select top ThisModuleRows id as PerRowMainId,senddate as PerRowMainDateTime,title as PerRowMainSubject from EMailLists where folderid=1 and status=0 and hrmid=CurrUserId order by id descNews规则select top 1 title from EMailLists where folderid=1 and status=0 and hrmid=CurrUserId声音提醒规则select top 1 title from EMailLists where folderid=1 and status=0 and hrmid=CurrUserId信息统计规则未读共:select count(id) from EMailLists where folderid=1 and status=0 and hrmid=CurrUserId|,已读共:select count(id) from EMailLists where folderid=1 and status=1 and hrmid=CurrUserId最新会议页面地址/meeting/data/ViewMeeting.jsp?meetingid=PerRowMainId更多链接/meeting/search/SearchResult.jsp?start=1&perpage=15内容显示规则select a.id as PerRowMainId, as PerRowMainSubject,a.begindate+(+a.begintime+-+a.endtime+) as PerRowMainDateTime from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId order by PerRowMainDateTime descNews规则select top 1 a.id from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId and a.createdate=CurrDate声音提醒规则信息统计规则共:select count(a.id) from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId最新会议页面地址/meeting/data/ViewMeeting.jsp?meetingid=PerRowMainId更多链接/meeting/search/SearchResult.jsp?start=1&perpage=15内容显示规则select a.id as PerRowMainId, as PerRowMainSubject,a.begindate+(+a.begintime+-+a.endtime+) as PerRowMainDateTime from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId order by PerRowMainDateTime descNews规则select top 1 a.id from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId and a.createdate=CurrDate声音提醒规则信息统计规则共:select count(a.id) from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid=CurrUserId我的项目(任务)页面地址/proj/data/ViewProject.jsp?ProjID=PerRowMainId更多链接/proj/search/SearchResult.jsp?start=1&perpage=15内容显示规则select distinct top ThisModuleRows t1.id as PerRowMainId, asPerRowMainSubject,t1.createdate as PerRowMainDateTime from Prj_ProjectInfo t1,PrjShareDetail t2 where t1.id = t2.prjid and t2.usertype=1 and t2.userid=CurrUserId order by t1.id descNews规则select distinct top t1.id from Prj_ProjectInfo t1,PrjShareDetail t2 where t1.id = t2.prjid and t2.usertype=1 and t2.userid=CurrUserId and t1.createdate=CurrDate声音提醒规则信息统计规则共:select count(distinct t1.id) from Prj_ProjectInfo t1,PrjShareDetail t2 where t1.id = t2.prjid and t2.usertype=1 and t2.userid=CurrUserId我的设备页面地址/cpt/capital/CptCapital.jsp?id=PerRowMainId更多链接/cpt/search/CptSearchResult.jsp?type=search&isdata=2内容显示规则select top ThisModuleRows t1.id as PerRowMainId, as PerRowMainSubject,t1.createdate as PerRowMainDateTime from cptCapital t1,cptShareDetail t2 where isdata=2 and t1.id=t2.cptid and t2.userid=CurrUserId and t2.usertype=1 and t1.stateid NOT IN(5,-7) order by t1.id descNews规则声音提醒规则信息统计规则我的日程页面地址/function/quickplan/QuickPlanEdit.jsp?id=PerRowMainId更多链接/function/quickplan/QuickPlanSearchResult.jsp?resourceid=CurrUserId&type=result内容显示规则select top ThisModuleRows id as PerRowMainId,subject as PerRowMainSubject,begindate as PerRowMainDateTime from QuickPlan where resourceid=CurrUserId order by id descNews规则声音提醒规则信息统计规则我的客户页面地址/CRM/data/ViewCustomer.jsp?CustomerID=PerRowMainId更多链接/search/SearchResult.jsp?pagenum=1&perpage=10内容显示规则News规则声音提醒规则信息统计规则部门文档页面地址/docs/docs/DocDsp.jsp?id=PerRowMainId更多链接/docs/search/DocSearchTemp.jsp?list=all&subcategory=156内容显示规则按所属部门查询select distinct top ThisModuleRows id as PerRowMainId,docsubject as PerRowMainSubject,doccreatedate as PerRowMainDateTime,doccreatetime from DocDetail t1,DocShareDetail t2 where t1.replydocid=0 and t1.docstatus in (1,2,5) and t1.id=t2.docid and t2.userid in (select id from hrmresource where departmentid=CurrUserDeptId) and subcategory=156 and t2.usertype=1 order by doccreatedate desc,doccreatetime desc 按分部查询select distinct top ThisModuleRows id as PerRowMainId,docsubject as PerRowMainSubject,doccreatedate as PerRowMainDateTime,doccreatetime from DocDetail t1,DocShareDetail t2 where t1.replydocid=0 and t1.docstatus in (1,2,5) and t1.id=t2.docid and t2.userid in (select id from hrmresource where subcompanyid1=CurrUserSubcCompanyId) and subcategory=156 and t2.usertype=1 order by doccreatedate desc,doccreatetime descNews规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId声音提醒规则select top 1 id from docdetaillog where docid=PerRowMainId and operateUserid=CurrUserId信息统计规则按所属部门查询共：select count(distinct id) from DocDetail as t1,DocShareDetail as t2 where docstatus in (1,2,5) and t1.id=t2.docid and t2.userid in (select id from hrmresource where departmentid=CurrUserDeptId) and t2.usertype=1 and subcategory=164按分部查询共：select count(distinct id) from DocDetail as t1,DocShareDetail as t2 where docstatus in (1,2,5) and t1.id=t2.docid and t2.userid in (select id from hrmresource where subcompanyid1=CurrUserSubcCompanyId) and t2.usertype=1 and subcategory=164部门会议页面地址/meeting/data/ViewMeeting.jsp?meetingid=PerRowMainId更多链接/meeting/search/SearchResult.jsp?start=1&perpage=15内容显示规则按所属部门查询Select distinct top ThisModuleRows a.id as PerRowMainId, as PerRowMainSubject,a.begindate+(+a.begintime+-+a.endtime+) as PerRowMainDateTime from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.isapproved=3 and b.memberid in (select id from hrmresource where departmentid=CurrUserDeptId) order by PerRowMainDateTime desc按分部查询Select distinct top ThisModuleRows a.id as PerRowMainId, as PerRowMainSubject,a.begindate+(+a.begintime+-+a.endtime+) as PerRowMainDateTime from Meeting a,Meeting_Member2 b where a.id=b.meetingid and a.