第二章 项目搭建
一.数据库部分
1.概念模型——系统管理
(1)人员资料表(Smt_User)
Name Code Data Type 主键 编号 Id Variable characters (80) # 人员账户 UserId Variable characters (40) 人员姓名 UserName Variable characters (30) 密码 Password Variable characters (80) 所属部门 DeptId Variable characters (80) 头像 Photo Image 电话 Mobile Variable characters (50) 邮箱 Email Variable characters (50) 备注 Remark Variable characters (50) 创建人 Creator Variable characters (80) 是否为系统用户 IsSysUser Boolean 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(2)部门资料表(Smt_Dept)
Name Code Data Type 主键 编号 Id Variable characters (80) # 上级部门编号 ParentId Variable characters (80) 部门名称 DeptName Variable characters (50) 负责人 ChargeMan Variable characters (30) 联系人 ContactMan Variable characters (30) 联系人电话 TelNo Variable characters (60) 备注 Remark Variable characters (50) 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(3)系统菜单资料表(Smt_Menu)
Name Code Data Type 主键 菜单编号 Id Variable characters (80) # 上级菜单编号 ParentId Variable characters (80) 菜单名称(系统用户) MenuSName Variable characters (50) 菜单名称(用户) MenuUName Variable characters (50) 页面链接 Href Variable characters (255) 图表名称 IconName Variable characters (30) 序号 OrderId Integer 备注 Remark Variable characters (50) 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(4)系统菜单功能项目表(Smt_Menu_RightItem)
Name Code Data Type 主键 菜单编号 MenuId Variable characters (80) # 功能编号 ItemId Integer 功能名称 ItemName Variable characters (50)
(5)系统角色表(Smt_Role)
Name Code Data Type 主键 角色编号 Id Variable characters (80) # 角色名称 RoleName Variable characters (40) 角色类型 RoleType Integer 备注 Remark Variable characters (50) 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(6)角色权限表(Smt_RoleRight)
Name Code Data Type 主键 角色编号 Id Variable characters (80) # 菜单编号 MenuId Variable characters (80) # 权限值 RightValue Variable characters (50)
(7)用户角色表(Smt_UserRole)
Name Code Data Type 主键 用户编号 UserId Variable characters (80) # 角色编号 RoleID Variable characters (40) # 备注 Remark Variable characters (50) 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(8)用户私有权限表(Smt_UserRight)
Name Code Data Type 主键 用户编号 UserId Variable characters (80) # 菜单编号 MenuId Variable characters (80) # 权限值 RightValue Variable characters (50)
(9)系统日志(Smt_SysLog)
Name Code Data Type 主键 日志编号 Id Variable characters (80) # 用户编号 UserId Variable characters (80) 菜单编号 MenuId Variable characters (80) 操作时间 OperateTime Date & Time IP地址 IPAddress Variable characters (40) 操作内容 Operate Variable characters (255) 浏览器 BrowerVersion Text
2.概念模型——业务管理
(1)图书资料表(Lib_Books)
Name Code Data Type 主键 编号 Id Variable characters (80) # 图书名称 BookName Variable characters (40) 图书ISBN ISBN Variable characters (80) 作者 Writter Variable characters (80) 出版社 Publisher Variable characters (80) 存放位置 BookShelf Variable characters (80) 状态 Status Integer 创建人 Creator Variable characters (80) 最近借出日期 LendData Date & Time 应还日期 ReturnData Date & Time 最长可借天数 LendDays Integer 操作员 EditByUserId Variable characters (80) 操作时间 EditTime Date & Time
(2)图书异动表(Lib_BookChangeList)
Name Code Data Type 主键 编号 Id Variable characters (80) # 图书编号 BookId Variable characters (80) 经办人 Operator Variable characters (80) 异动日期 ChangeData Date & Time 异动方向 InOrOut Integer
3.物理模型创建
(1)将页面停留在概念模型的界面
(2)页面栏Tools——Check Model
检查模型,可以将Check Model
界面的Entity Attribute
和Entity Identifier
勾选掉
(3)点击确定,会开始检查模型,看检查是否报错,若报错,可以根据报错类型,在Check Model
界面将对应报错原因勾选掉
(4)在检查完成后,回到概念模型界面,页面栏Tools——Generate Physical Model
生成物理模型
4.数据库文件创建
(1)首先解决数据库文件转码问题,页面栏Tools——Excute Commands——Edit/Run Script
,复制粘贴以下脚本内容,然后点击run
运行
Option Explicit
ValidationMode = True
InteractiveMode = im_Batch
Dim mdl 'the current model
'get the current active model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
MsgBox "There is no current Model"
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "The current model is not an Physical Data model."
Else
ProcessFolder mdl
End If
'This routine copy name into code for each table, each column and each view
'of the current folder
Private Sub ProcessFolder(folder)
Dim Tab 'running table
For Each Tab In folder.tables
If Not tab.isShortcut Then
tab.comment = tab.name
Dim col 'running column
For Each col In tab.columns
col.comment = col.name
Next
End If
Next
Dim view 'running view
For Each view In folder.Views
If Not view.isShortcut Then
view.comment = view.name
End If
Next
'go into the sub-packages
Dim f 'running folder
For Each f In folder.Packages
If Not f.IsShortcut Then
ProcessFolder f
End If
Next
End Sub
(2)生成Sql脚本,界面停留在物理层界面,页面栏Database——Generate Database
,选择对应路径可以生成Sql文件
(3)转换数据格式,edit
会以记事本格式打开,文件——另存为
,编码选择UTF——8
,选择对应位置保存
5.数据库生成与初始化方法一
(1)打开Navicat,新建一个数据库——打开数据库——右键数据库——运行Sql文件
,找到对应的Sql文件,点击开始执行
(2)初始化数据库,打开数据库后,右键查询——新建查询
,复制粘贴以下代码,点击运行
CREATE DEFINER=`root`@`localhost` PROCEDURE `Smt_CreateMenu`()
BEGIN
declare sUserId varchar(80);
SET SQL_SAFE_UPDATES = 0;
delete from smt_menu;
delete from smt_menu_rightitem;
delete from smt_role where RoleType = 1;
delete from smt_userrole;
delete from smt_roleright;
delete from smt_userright;
delete from smt_user;
/*主模块*/
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('LIB', '###', '业务管理', '业务管理','/LIB',1,'fa fa-tasks');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SMT', '###', '系统管理', '系统管理','/SMT',2,'fa fa-desktop');
/*--系统管理*/
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SmtDept', 'SMT','部门管理', '部门管理','smt/smtDept/index.html',3,'fa fa-sitemap');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SmtUser', 'SMT','人员管理', '人员管理','smt/smtUser/index.html',4,'fa fa-user');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SmtRole', 'SMT','角色管理', '角色管理','smt/smtRole/index.html',5,'fa fa-users');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SmtParamsSet','SMT', '系统设置', '系统设置','smt/SmtParamsSets',6,'fa fa-gear');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('SmtSysLog', 'SMT','日志管理', '日志管理','smt/SmtSysLogs',7,'fa fa-film');
/*业务管理*/
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('LibBookDate', 'LIB','图书预约', '图书预约','lib/libBookDate/index.html',8,'fa fa-send');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('LibBookLend', 'LIB','图书借阅', '图书借阅','lib/libBookLend/index.html',9,'fa fa-send');
insert into smt_menu(Id,ParentId,MenuSName,MenuUName,Href,OrderId,IconName) values ('LibBookReturn', 'LIB','图书归还', '图书归还','lib/libBookReturn/index.html',10,'fa fa-indent');
/*模块权限项目
--系统管理*/
insert into smt_menu_rightitem values ('SmtDept',1,'使用');
insert into smt_menu_rightitem values ('SmtDept',2,'新增');
insert into smt_menu_rightitem values ('SmtDept',3,'编辑');
insert into smt_menu_rightitem values ('SmtDept',4,'删除');
insert into smt_menu_rightitem values ('SmtDept',5,'全部数据');
insert into smt_menu_rightitem values ('SmtUser',1,'使用');
insert into smt_menu_rightitem values ('SmtUser',2,'新增');
insert into smt_menu_rightitem values ('SmtUser',3,'编辑');
insert into smt_menu_rightitem values ('SmtUser',4,'删除');
insert into smt_menu_rightitem values ('SmtUser',5,'权限配置');
insert into smt_menu_rightitem values ('SmtUser',6,'审核注册用户');
insert into smt_menu_rightitem values ('SmtRole',1,'使用');
insert into smt_menu_rightitem values ('SmtRole',2,'新增');
insert into smt_menu_rightitem values ('SmtRole',3,'编辑');
insert into smt_menu_rightitem values ('SmtRole',4,'删除');
insert into smt_menu_rightitem values ('SmtRole',5,'权限配置');
insert into smt_menu_rightitem values ('SmtParamsSet',1,'使用');
insert into smt_menu_rightitem values ('SmtParamsSet',2,'修改');
insert into smt_menu_rightitem values ('SmtSysLog',1,'使用');
insert into smt_menu_rightitem values ('SmtSysLog',2,'删除');
/*业务管理*/
insert into smt_menu_rightitem values ('LibBookDate',1,'使用');
insert into smt_menu_rightitem values ('LibBookDate',2,'新增');
insert into smt_menu_rightitem values ('LibBookDate',3,'编辑');
insert into smt_menu_rightitem values ('LibBookDate',4,'删除');
insert into smt_menu_rightitem values ('LibBookDate',5,'部门数据');
insert into smt_menu_rightitem values ('LibBookDate',6,'全部数据');
insert into smt_menu_rightitem values ('LibBookLend',1,'使用');
insert into smt_menu_rightitem values ('LibBookLend',2,'新增');
insert into smt_menu_rightitem values ('LibBookLend',3,'编辑');
insert into smt_menu_rightitem values ('LibBookLend',4,'删除');
insert into smt_menu_rightitem values ('LibBookLend',5,'部门数据');
insert into smt_menu_rightitem values ('LibBookLend',6,'全部数据');
insert into smt_menu_rightitem values ('LibBookReturn',1,'使用');
insert into smt_menu_rightitem values ('LibBookReturn',2,'新增');
insert into smt_menu_rightitem values ('LibBookReturn',3,'编辑');
insert into smt_menu_rightitem values ('LibBookReturn',4,'删除');
insert into smt_menu_rightitem values ('LibBookReturn',5,'部门数据');
insert into smt_menu_rightitem values ('LibBookReturn',6,'全部数据');
/*初始化系统角色*/
/*E1ADC3949BA59ABBE56E057F2F883E*/
insert into smt_user(Id,UserId,UserName,Password,IsSysUser,EdtByUserId,EdtTime) values ('ADMIN','ADMIN','超级管理员','e10adc3949ba59abbe56e057f20f883e',1,'ADMIN',now());
select Id into sUserId from smt_user where UserId = 'ADMIN';
insert into smt_role(Id,RoleName,RoleType,Remark,EdtByUserId,EdtTime) values ('54C67E41-A7DD-4C5D-BFA2-088E5461A708','系统管理员',1,'负责系统管理工作',sUserId,now());
insert into smt_role(Id,RoleName,RoleType,Remark,EdtByUserId,EdtTime) values ('6FDEBC85-179F-4C4B-BB36-8E57C76F0843','默认注册用户',1,'注册时默认的角色',sUserId,now());
END ;;
(3)打开函数下,新生成的函数Smt_CreateMenu
,点击运行,出现Procedure executed successfully
则运行成功