SELECT No,Name,Pass,FK_Dept,SID FROM Port_Emp
SELECT No,Name,ParentNo FROM Port_Dept
SELECT No,Name,FK_StationType FROM Port_Station
SELECT No,Name FROM Port_StationType
SELECT MyPK,FK_Emp,FK_Dept FROM Port_DeptEmp
SELECT MyPK,FK_Emp,FK_Dept,FK_Station FROM Port_DeptEmpStation
--1、设置岗位类别
SELECT * FROM Port_StationType--2、设置岗位
SELECT * FROM Port_Station--3、设置部门
SELECT * FROM Port_Dept--4、设置部门岗位
SELECT * FROM Port_DeptStation--5、新建立人员的时候会在Port_Emp插入部门ID,但是在Port_DeptEmp和Port_DeptEmpStation中没有对应记录
SELECT * FROM Port_Emp --6 在人员台账中设置人员部门岗位的时候会有记录SELECT * FROM Port_DeptEmp--7 人员部门岗位
SELECT * FROM Port_DeptEmpStation--====================模拟数据时,可将CCFLOW自带的下述表重命名。
EXEC SP_RENAME 'PORT_EMP', 'TEST_PORT_EMP'
EXEC SP_RENAME 'PORT_DEPT', 'TEST_PORT_DEPT' EXEC SP_RENAME 'PORT_STATIONTYPE', 'TEST_PORT_STATIONTYPE' EXEC SP_RENAME 'PORT_STATION', 'TEST_PORT_STATION' EXEC SP_RENAME 'PORT_DEPTEMP', 'TEST_DEPTEMP' EXEC SP_RENAME 'PORT_DEPTSTATION', 'TEST_PORT_DEPTSTATION' EXEC SP_RENAME 'PORT_DEPTEMPSTATION', 'TEST_DEPTEMPSTATION'--====================ONEMORE模式============================
--岗位类型/**No Nvarchar(2) 岗位类型编号Name Nvarchar(50) 类型名称(例:高层、中层等)OrgNo Nvarchar(60) 单位集团编号,默认0 CREATE VIEW [DBO].[PORT_STATIONTYPE] AS SELECT NO,NAME,'' ORGNO FROM TEST_PORT_STATIONTYPE select * from BESS_Base_SysRoleType**/ CREATE VIEW [DBO].[PORT_STATIONTYPE] AS SELECT No,Name,0 Idx,'' ORGNO FROM BESS_Base_SysRoleType--岗位
--select * from BESS_Base_Roles/**No Nvarchar(4) 岗位编号Name Nvarchar(100) 名称FK_StationType Nvarchar(100) 岗位类型DutyReq Nvarchar(max) 职位,默认为0Makings Nvarchar(max) 素质要求,为空OrgNo Nvarchar(100) 单位集团编号,为0CREATE VIEW [DBO].[PORT_STATION]
AS SELECT NO,NAME, FK_STATIONTYPE,'' DUTYREQ,''MAKINGS,0 ORGNO FROM TEST_PORT_STATION select * from BESS_Base_Roles**//******************************************* * 第一个版本 * SELECT R.Roles_ID AS No,R.Roles_Name AS Name, R.FK_STATIONTYPE,'' DUTYREQ,''MAKINGS,0 ORGNO FROM BESS_Base_Roles R LEFT JOIN PORT_STATIONTYPE S ON R.FK_StationType = S.NO where R.DeleteMark !=0 *******************************************/ CREATE VIEW [DBO].[PORT_STATION] AS SELECT R.Roles_ID AS No,R.Roles_Name AS Name, R.FK_STATIONTYPE,'' DUTYREQ,''MAKINGS,0 ORGNO FROM BESS_Base_Roles R where R.DeleteMark !=0 --部门 SELECT * FROM BESS_Base_Organization --/**No Nvarchar(50) 部门编号Name Nvarchar(200) 名称ParentNo Nvarchar(100) 父节点(根节点编号,默认为0)NameOfPath Nvarchar(300) 空(一人多部门情况,为所属路径,如:集团总部/销售部 格式)TreeNo Nvarchar(100) 树节点编号 空Leader, Nvarchar(100) 部门领导空或FK_Emp的字段Tel Nvarchar(100) 联系方式 空 Idx Int 顺序号 空IsDir Int 目录空OrgNo Nvarchar(50) 单位集团编号0CREATE VIEW [DBO].[PORT_DEPT]
AS SELECT NO,NAME, PARENTNO,'' NAMEOFPATH, '' TREENO,'' LEADER,'' TEL,0 IDX,0 ISDIR ,0 ORGNO FROM TEST_PORT_DEPT**/CREATE VIEW [DBO].[PORT_DEPT] AS SELECT Organization_ID AS NO,Organization_Name AS NAME, ParentId AS ParentNo, '' NAMEOFPATH, '' TREENO,'' LEADER,'' TEL,0 IDX,0 ISDIR ,0 ORGNO FROM BESS_Base_Organization where DeleteMark !=0--部门岗位【参考自己的业务表,多表联合查询】 SELECT * FROM BESS_Base_Organization --/**FK_Dept Nvarchar(15) 部门编号FK_Station Nvarchar(100) 岗位编号 CREATE VIEW [DBO].[PORT_DEPTSTATION] AS SELECT FK_DEPT,FK_STATION FROM TEST_PORT_DEPTSTATION**/ CREATE VIEW [DBO].[PORT_DEPTSTATION] AS SELECT Organization_ID as FK_DEPT,FK_STATION AS FK_STATION FROM BESS_Base_Organization --人员/**No Nvarchar(20) 登录编号(必须为字符串数据类型!)Name Nvarchar(200) 姓名Pass Nvarchar(100) 密码FK_Dept Nvarchar(100) 所在部门 (一人多部门,则为主部门编号)SID Nvarchar(36) 登录随机码 CREATE VIEW [DBO].[PORT_EMP] AS SELECT NO,NAME,PASS,FK_DEPT,'' SID FROM TEST_PORT_EMP**/ CREATE VIEW [DBO].[PORT_EMP] AS SELECT U.User_Account as NO,U.User_Name as NAME,U.User_Pwd as PASS,S.Organization_ID as FK_DEPT,'' SID FROM BESS_Base_UserInfo U LEFT JOIN BESS_Base_StaffOrganize S ON U.User_ID = S.User_ID where U.DeleteMark !=0 --部门人员【参考自己的业务表,多表联合查询】 /**Mypk Nvarchar(100) 唯一主键,使用GUID动态生成即可FK_Dept Nvarchar(50) 部门主键FK_Emp Nvarchar(50) 人员主键FK_Duty Nvarchar(50) 职位,默认为0DutyLevel int 职位级别,默认为0Leader Nvarchar(50) 领导,默认为空CREATE VIEW [DBO].[PORT_DEPTEMP]
AS SELECT MYPK,FK_DEPT,FK_EMP,'' FK_DUTY,'' DUTYLEVEL,'' LEADER FROM TEST_PORT_DEPTEMP **/ /******************************************* * 第一版 * SELECT OZ.StaffOrganize_Id as MYPK,OZ.Organization_ID as FK_DEPT,UI.User_Account as FK_EMP,UI.User_Name,'' FK_DUTY,'' DUTYLEVEL,'' LEADER FROM BESS_Base_StaffOrganize OZ LEFT JOIN BESS_Base_UserInfo UI ON OZ.User_ID = UI.User_ID*******************************************/
CREATE VIEW [DBO].[PORT_DEPTEMP] AS SELECT StaffOrganize_Id as MYPK,Organization_ID as FK_DEPT,UserAccount as FK_EMP,'' FK_DUTY,'' DUTYLEVEL,'' LEADER FROM BESS_Base_StaffOrganize --部门人员岗位【参考自己的业务表,多表联合查询】/**MYPK Nvarchar(100) 唯一主键,无实际意义 多字段组合FK_Dept Nvarchar(50) 部门编号FK_Station Nvarchar(50) 岗位编号FK_Emp Nvarchar(50) 人员编号CREATE VIEW [DBO].[PORT_DEPTEMPSTATION] AS SELECT MYPK,FK_DEPT,FK_STATION,FK_EMP FROM TEST_PORT_DEPTEMPSTATION**/--人员部门表SELECT * FROM BESS_Base_StaffOrganize--人员岗位表SELECT * FROM BESS_Base_UserRole--人员表SELECT * FROM BESS_Base_UserInfo--第一个版本三表查询
CREATE VIEW [DBO].[PORT_DEPTEMPSTATION] AS SELECT SO.StaffOrganize_Id as MYPK,SO.Organization_ID as FK_DEPT,UR.Roles_ID as FK_STATION,UI.User_ID as FK_EMP FROM BESS_Base_UserInfo UI LEFT JOIN BESS_Base_StaffOrganize SO ON UI.User_ID=SO.User_ID LEFT JOIN BESS_Base_UserRole UR ON SO.User_ID = UR.User_ID--第二个版本二表查询
CREATE VIEW [DBO].[PORT_DEPTEMPSTATION] AS SELECT SO.StaffOrganize_Id as MYPK,SO.Organization_ID as FK_DEPT,UR.Roles_ID as FK_STATION,UR.User_ID as FK_EMP FROM BESS_Base_StaffOrganize SO LEFT JOIN BESS_Base_UserRole UR ON SO.User_ID = UR.User_ID--第三个版本
CREATE VIEW [DBO].[PORT_DEPTEMPSTATION] AS SELECT SO.StaffOrganize_Id as MYPK,SO.Organization_ID as FK_DEPT,UR.Roles_ID as FK_STATION,UR.UserAccount as FK_EMP FROM BESS_Base_StaffOrganize SO LEFT JOIN BESS_Base_UserRole UR ON SO.User_ID = UR.User_ID AND SO.UserAccount = UR.UserAccount