CRM 7.0SP2，如果调整组织结构，计划和日程会找不到

作者:陈格日期:2010-01-13

字体大小: 小中大

CRM 7.0SP2，如果调整组织结构，计划和日程会找不到

CRM 没有移动部门的这个功能，只能先新增再删除部门。结果导致相关的计划和日程的归属部门都错误了。在限制访问部门的权限设定下，最终导致本人无法查询到本人以前填写的日程。

select p.*,d.name as dname,e.name as ename
from  CRM_MyPlan p
    left join crm_department d on p.department = d.typeid
    left join crm_employee e on p.creatortypeid = e.typeid
where d.typeid is null
order by p.createdate desc

select * into crm_department_20100110 from grasp_crm_temp.dbo.crm_department

--先检查一遍又多少记录是错误的
select p.*,d.name as dname,e.name as ename,d4.name as d4name
from  CRM_MyPlan p
    left join crm_department d on p.department = d.typeid
    left join crm_employee e on p.creatortypeid = e.typeid
    left join (
        select d2.typeid as oldtypeid,d3.typeid as newtypeid
        ,d2.name
        from crm_department_20100110 d2
            inner join crm_department d3 on d2.name =d3.name
        where d3.typeid <>d2.typeid
        ) d4
    on p.department = d4.oldtypeid
where d.typeid is null and d4.name is not null
order by p.createdate desc
--crm_department_20100110 是调整前的组织结构备份表。

--操作前先备份计划表
select * into CRM_MyPlan_201001131347 from  CRM_MyPlan

-- 进行更新
update CRM_MyPlan
set CRM_MyPlan.department = d4.newtypeid
from  CRM_MyPlan as p
    left join crm_department d on p.department = d.typeid
    left join (
        select d2.typeid as oldtypeid,d3.typeid as newtypeid
        ,d2.name
        from crm_department_20100110 d2
            inner join crm_department d3 on d2.name =d3.name
        where d3.typeid <>d2.typeid
        ) d4
    on p.department = d4.oldtypeid

where d.typeid is null and d4.name is not null

--个别修改某些人的计划所属的部门，注意，如果部门名称填写错误，对应的部门ID会被更新为错误的 ID
update CRM_MyPlan set department =
    (select typeid from crm_department where name='3C营运部2')
where creatortypeid =
    (select typeid from crm_employee where name = '陈宪' and isactive=1)

update CRM_MyPlan set department =
    (select typeid from crm_department where name='3C营运部1')
where creatortypeid =
    (select typeid from crm_employee where name = '王光辉' and isactive=1 )

[本日志由陈格于 2010-01-13 02:22 PM 编辑]