原文:
构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统（41）-组织架构
这是我的SysUser表
本节开始我们要实现工作流，此工作流可以和之前的所有章节脱离关系，也可以紧密合并。
我们当初设计的项目解决方案就是可伸缩可以拆离，可共享的项目解决方案。所以我们同时要添加App.Flow文件夹
工作流的开始之前，我们必须有一个组织架构，我们做成无限动态级别树，因为之前的模块管理也是无限级别的
知识点：Easyui TreeGrid用法，根据组织架构读取架构下所有用户（with...as....）
穿越到模块管理的源码 有点雷同的Easyui TreeGrid的制作
CREATE TABLE [dbo].[SysStruct]( [Id] [varchar](50) NOT NULL, --主键ID [Name] [varchar](50) NOT NULL, --架构名称 [ParentId] [varchar](50) NOT NULL, --上级ID [Sort] [int] NOT NULL, --排序 [Higher] [varchar](50) NULL, -- 备用 [Enable] [bit] NOT NULL, --是否启用 [Remark] [varchar](500) NULL, --说明 [CreateTime] [datetime] NOT NULL, --创建时间 CONSTRAINT [PK_SysStruct] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SysStruct] WITH NOCHECK ADD CONSTRAINT [FK_SysStruct_SysStruct] FOREIGN KEY([ParentId]) REFERENCES [dbo].[SysStruct] ([Id]) GO ALTER TABLE [dbo].[SysStruct] NOCHECK CONSTRAINT [FK_SysStruct_SysStruct] GO ALTER TABLE [dbo].[SysStruct] ADD CONSTRAINT [DF_SysStruct_Sort] DEFAULT ((0)) FOR [Sort] GO ALTER TABLE [dbo].[SysStruct] ADD CONSTRAINT [DF__SysStruct__Highe__3D2915A8] DEFAULT ((0)) FOR [Higher] GO ALTER TABLE [dbo].[SysStruct] ADD CONSTRAINT [DF_SysStruct_State] DEFAULT ((1)) FOR [Enable] GO ALTER TABLE [dbo].[SysStruct] ADD CONSTRAINT [DF_SysStruct_CreateTime] DEFAULT (getdate()) FOR [CreateTime] GO
最终效果图
理论是无限级别的。这里我只做了3级
接下来我们关联SysUser和SysStruct的关系。并添加存储过程，这个存储过程很有用，sql没有递归，用with...as....语句最适合不过了，貌似2005版本以上才支持
CREATE TABLE [dbo].[SysUser]( [Id] [varchar](50) NOT NULL, [UserName] [varchar](200) NOT NULL, [Password] [varchar](200) NOT NULL, [TrueName] [varchar](200) NULL, [Card] [varchar](50) NULL, [MobileNumber] [varchar](200) NOT NULL, [PhoneNumber] [varchar](200) NULL, [QQ] [varchar](50) NULL, [EmailAddress] [varchar](200) NULL, [OtherContact] [varchar](200) NULL, [Province] [varchar](200) NULL, [City] [varchar](200) NULL, [Village] [varchar](200) NULL, [Address] [varchar](200) NULL, [State] [bit] NOT NULL, [CreateTime] [datetime] NULL, [CreatePerson] [varchar](200) NULL, [Sex] [varchar](10) NULL, [Birthday] [datetime] NULL, [JoinDate] [datetime] NULL, [Marital] [varchar](10) NULL, [Political] [varchar](50) NULL, [Nationality] [varchar](20) NULL, [Native] [varchar](20) NULL, [School] [varchar](50) NULL, [Professional] [varchar](100) NULL, [Degree] [varchar](20) NULL, [DepId] [varchar](50) NOT NULL, [PosId] [varchar](50) NOT NULL, [Expertise] [varchar](3000) NULL, [JobState] [bit] NOT NULL, [Photo] [varchar](200) NULL, [Attach] [varchar](200) NULL, [Lead] [varchar](4000) NULL, [LeadName] [varchar](4000) NULL, [IsSelLead] [bit] NOT NULL, [IsReportCalendar] [bit] NOT NULL, [IsSecretary] [bit] NOT NULL, CONSTRAINT [PK_SysUser] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'MobileNumber' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'婚姻' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Marital' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'党派' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Political' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'民族' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Nationality' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'籍贯' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Native' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'毕业学校' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'School' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'就读专业' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Professional' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Degree' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'DepId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'PosId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'个人简介' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Expertise' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'在职状况1在职，2离职' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'JobState' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Photo' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Attach' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级领导' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Lead' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级领导' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'LeadName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可以自选领导' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'IsSelLead' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日否启动日程汇报是否启用 启用后 他的上司领导将可以看到他的 工作日程汇报.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'IsReportCalendar' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开启 小秘书消息提示(默认每10分钟提示一次) 开启 小秘书消息提示(每2分钟提示一次) 开启 小秘书消息提示(每5分钟提示一次) 开启 小秘书消息提示(每20分钟提示一次) 开启 小秘书消息提示(每30分钟提示一次) 开启 小秘书消息提示(每1小时提示一次) 开启 小秘书消息提示(每2小时提示一次) 禁用 小秘书消息提示(不再提示) ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'IsSecretary' GO
我们以后如果按组织架构流转。比如按总公司流转,那么我们根据总公司的ID就能找到无限树叶的所有用户了。
我以前习惯叫部门，所以存储过程的Dep就是现在的Strcut了
USE [AppDB] GO /****** Object: StoredProcedure [dbo].[P_Sys_GetUserByDepId] Script Date: 03/21/2015 22:08:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[P_Sys_GetUserByDepId] @DepId varchar(50) as begin --读取角色所包含的用户 with CTE_Depart(Id ,Name ,ParentID )as ( select a.Id ,a.Name ,a.Id ParentID from SysStruct a union all select a.Id,a.Name ,b.ParentID from SysStruct a join CTE_Depart b on a.ParentID = b.Id ) select b.*,0 as flag from CTE_Depart a left join SysUser b on a.id = b.DepId where a.ParentID=@DepId and b.Id is not null end
好了。没啥...
