场景:集团式架构公司体系,因业务原因各子公司间数据相互独立,集团主管部门可对子公司进行数据监管、统计、分析;
解决办法:采用部门编码权限方式(纯模型,各位看官勿笑,如有好的解决方式请提供)
1、业务数据增加数据维护人员信息。
2、部门增加权限编码,大致方式(父:0001、子一:00010001、子二:00010002)。
3、数据访问根据当前用户所在部门 like 数据维护人员所在部门进行比较。
4、关于父子权限编码问题,涉及部门固有组织形式,所以不采用企业内部编码,对此自己通过后台维护处理。
5、目前采用Oracle中的触发器解决部门权限编码问题。
附两段脚本,分别是Oracle和SQLServer的(俺的新系统框架跨数据库平台哟!):
ORACLE版:
CREATE OR REPLACE TRIGGER TR_TS_DEPARTMENT_BIU
BEFORE INSERT ON TS_DEPARTMENT
FOR EACH ROW
DECLARE
P_CHILDCOUNT INT;
P_PARENTRIGHTCODE VARCHAR(126);
BEGIN
--父部门权限号
SELECT NVL(RIGHTCODE, '')
INTO P_PARENTRIGHTCODE
FROM TS_DEPARTMENT
WHERE DEPARTMENTID = :NEW.PARENTDEPARTMENTID;
--当前部门权限号
SELECT COUNT(1)
INTO P_CHILDCOUNT
FROM TS_DEPARTMENT
WHERE PARENTDEPARTMENTID = :NEW.PARENTDEPARTMENTID;
:NEW.RIGHTCODE := P_PARENTRIGHTCODE ||
LPAD(TO_CHAR(P_CHILDCOUNT), 3, '0');
END;
SQLServer版:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [TR_TS_DEPARTMENT_BIU] ON [dbo].[TS_DEPARTMENT]
instead of INSERT
as
DECLARE
@P_CHILDCOUNT int,
@P_PARENTRIGHTCODE varchar(126),
@DEPARTMENTID char(20),
@PARENTDEPARTMENTID char(20),
@DEPARTMENTCODE varchar(128),
@DEPARTMENTNAME varchar(128),
@ZIPCODE varchar(32),
@TEL1 varchar(32),
@TEL2 varchar(32),
@FAX varchar(32),
@EMAIL varchar(64),
@ADDRESS varchar(128),
@STOPFLAG char(1),
@PROPERTY varchar(128),
@RIGHTCODE varchar(126),
@LASTUPDATEUSER char(20),
@LASTUPDATETIME datetime,
@REMARKS varchar(128);
BEGIN
--父部门权限号
SELECT @DEPARTMENTID = DEPARTMENTID,
@PARENTDEPARTMENTID =PARENTDEPARTMENTID,
@DEPARTMENTCODE = DEPARTMENTCODE,
@DEPARTMENTNAME = DEPARTMENTNAME,
@ZIPCODE = ZIPCODE,
@TEL1 = TEL1,
@TEL2 = TEL2,
@FAX = FAX,
@EMAIL = EMAIL,
@ADDRESS = ADDRESS,
@STOPFLAG =STOPFLAG,
@PROPERTY = PROPERTY,
@RIGHTCODE =RIGHTCODE,
@LASTUPDATEUSER = LASTUPDATEUSER,
@LASTUPDATETIME = LASTUPDATETIME,
@REMARKS = REMARKS
FROM inserted;
--父部门权限号
SELECT @P_CHILDCOUNT = COUNT(1),
@P_PARENTRIGHTCODE = RIGHTCODE
FROM TS_DEPARTMENT
WHERE TS_DEPARTMENT.PARENTDEPARTMENTID = @PARENTDEPARTMENTID
group by RIGHTCODE;
--当前部门权限号
select @RIGHTCODE = @P_PARENTRIGHTCODE + dbo.LPAD(convert(varchar(128),@P_CHILDCOUNT), 3, '0');
INSERT INTO TS_DEPARTMENT
VALUES
(@DEPARTMENTID,
@PARENTDEPARTMENTID,
@DEPARTMENTCODE,
@DEPARTMENTNAME,
@ZIPCODE,
@TEL1,
@TEL2,
@FAX,
@EMAIL,
@ADDRESS,
@STOPFLAG,
@PROPERTY,
@RIGHTCODE,
@LASTUPDATEUSER,
@LASTUPDATETIME,
@REMARKS);
END;