分析云 OA中部门分级思路和实现方法

描述：在客户碰到OA中部门分级有如下情况，部门中人员有挂在非末级部门上，如下图：

商务中心是末级部门，**应用事业部为二级部门。由于父子维度要求人员必须挂到末级部门，故使用父子维度无法处理该情况。

处理方式：

此时需要将各级部门都作为末级部门进行考虑处理。

将一级部门、二级部门、三级部门分别取出，并通过合并的方式，按照一级部门、二级部门、三级部门分别形成末级部门的形式，进而实现这种业务。

在做抽取的时候，需考虑将OA中人员单据根据人员ID关联人员和部门表，将部门ID抽取到事实表中，然后将事实表中的部门ID跟部门分级的末级部门ID关联，即实现按照人员的部门进行业务数据统计。

具体参考SQL如下：

 1 --第三级部门
 2 select 
 3 a.SORT_ID,a.path as path,
 4 c.id as dim_id,cast(c.name as nvarchar(200)) as dim_name,
 5 a.id as dim1_id,cast(a.name as nvarchar(200)) as dim1_name,
 6 b.id as dim2_id,cast(b.name as nvarchar(200)) as dim2_name,
 7 c.id as dim3_id,cast(c.name as nvarchar(200)) as dim3_name 
 8 from 
 9 (select u.path,u.id,u.name,u.type ,u.SORT_ID  from org_unit u 
10 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a 
11 left join 
12 (  select u.path,u.id,u.name,u.type,u.SORT_ID   from org_unit u 
13 where u.IS_DELETED =0 and u.is_enable= 1  and len(u.path)=16 and u.path like '00000002%') b 
14 on a.path =left(b.path,12)
15 left join 
16 (  select u.path,u.id,u.name,u.type,u.SORT_ID   from org_unit u 
17 where u.IS_DELETED =0  and u.is_enable= 1 and len(u.path)=20 and u.path like '00000002%') c 
18 on b.path=left(c.path,16)
19 where c.id is not null
20 
21 union all
22     --第二级部门
23 select 
24 b.SORT_ID ,b.path as path, 
25 b.id as dim_id,cast(b.name as nvarchar(200))  as dim_name ,
26 a.id as dim1_id,cast(a.name as nvarchar(200))  as dim1_name,
27 b.id as dim2_id,cast(b.name as nvarchar(200))  as dim2_name,
28 cast('' as int ) as dim3_id,'' as dim3_name
29 from 
30 (select u.path,u.id,u.name,u.type ,u.SORT_ID  from org_unit u 
31 where u.IS_DELETED =0  and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a 
32 left join 
33 (  select u.path,u.id,u.name,u.type,u.SORT_ID   from org_unit u 
34 where u.IS_DELETED =0  and u.is_enable= 1 and len(u.path)=16 and u.path like '00000002%' ) b 
35 on a.path =left(b.path,12)
36 where b.id is not null 
37 
38 union all 
39     --第一级部门
40 select 
41 a.SORT_ID ,a.path as path, 
42 a.id as dim_id,cast(a.name as nvarchar(200)) as dim_name ,
43 a.id as dim1_id,cast(a.name as nvarchar(200)) as dim1_name,
44 cast('' as int ) as dim2_id, '' as dim2_name,
45 cast('' as int ) as dim3_id,'' as dim3_name
46 from 
47 (select u.path,u.id,u.name,u.type,u.SORT_ID   from org_unit u 
48 where u.IS_DELETED =0  and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a  
49 where a.id is not null