小花爸技術網站

您有與書平電話中討論到A組織要COPY一份當作B組織資料的問題，

原則上您可以準備好A組織的SYN Table資料之後，除了SYN_User不用COPY之外(因為人員資料是組織共用的)，

其他都各自COPY並調整orgId的部分，並確認SYN_Function中isMain的欄位，一個人兼任多部門的狀況下，

就算有跨兩個組織，也只會有一筆資料的isMain是1，其他都是0。

將目前EFGP的A組織資料倒回SYN Table中，可以利用附件的SQL來達到，

然後依照上述的方式將新的SYN Table資料準備好後，在利用SyncTable.bat的程式將資料倒到EFGP組織中即可。

請都先於測試機確認OK後，再來做正式機的部分喔!

--SYN_Org
insert into SYN_Org (orgId, orgName)
select id as orgId, organizationName as orgName from Organization

--SYN_FunctionLevel
insert into SYN_FunctionLevel(levelName,orgId,levelValue)
select
FunctionLevel.functionLevelName as levelName ,
Organization.id as orgId ,
FunctionLevel.levelValue as levelValue
from FunctionLevel,Organization
where FunctionLevel.organizationOID=Organization.OID
--SYN_UNITLEVEL
insert into SYN_UnitLevel(levelName,orgId,levelValue)
select
OrganizationUnitLevel.organizationUnitLevelName as levelName,
Organization.id as orgId,
OrganizationUnitLevel.levelValue as levelValue
from OrganizationUnitLevel,Organization
where OrganizationUnitLevel.organizationOID=Organization.OID
--SYN_FUNCTIONDEFINITION
insert into SYN_FunctionDefinition (orgId, defName)
select
Organization.id as orgId,
FunctionDefinition.functionDefinitionName as defName
from FunctionDefinition,Organization
where Organization.OID=FunctionDefinition.organizationOID

--SYN_USERS
insert into SYN_Users(userId,userName,mailAddress,phoneNumber,languageType,leaveDate,enableSubstitute,ldapid)
select
Users.id as userId,
Users.userName as userName,
Users.mailAddress as mailAddress,
Users.phoneNumber as phoneNumber,
case (Users.localeString) when 'en_US' then 0 when 'zh_TW' then 1 when 'zh_CN' then 2 end as languageType,
Users.leaveDate as leaveDate,
Users.enableSubstitute as enableSubstitute,
Users.ldapid as ldapid
from Users
--SYN_EMPLOYEE
insert into SYN_Employee(userId,orgId,empId)
select
Users.id as userId,
Organization.id as orgId,
Employee.employeeId as empId
from Employee,Organization,Users
where Employee.organizationOID=Organization.OID
and Users.OID=Employee.userOID
--SYN_UNIT
insert into SYN_Unit(unitId,orgId,unitName,unitType,levelName,isValid)
select
OrganizationUnit.id as unitId,
Organization.id as orgId,
OrganizationUnit.organizationUnitName as unitName,
OrganizationUnit.organizationUnitType as organizationUnitType,
OrganizationUnitLevel.organizationUnitLevelName as levelName,
OrganizationUnit.validType as isValid
from OrganizationUnit
inner join Organization on OrganizationUnit.organizationOID=Organization.OID
left join OrganizationUnitLevel on OrganizationUnit.levelOID=OrganizationUnitLevel.OID

--SYN_UnitRelation
insert into SYN_UnitRelation(unitId,parentUnitId,orgId)
select
unit.id as unitId,
parentunit.id as parentUnitId,
Organization.id as orgId
from OrganizationUnit unit, OrganizationUnit parentunit, Organization
where unit.superUnitOID=parentunit.OID
and unit.organizationOID=Organization.OID

--SYN_Functions
insert into SYN_Functions(userId,unitId,orgId,functionName,isMain,levelName,managerId)
select
Occupant.id as userId,
OrganizationUnit.id as unitId,
Organization.id as orgId,
FunctionDefinition.functionDefinitionName as functionName,
Functions.isMain as isMain,
FunctionLevel.functionLevelName as levelName,
Manager.id as managerId
from Functions
inner join Users Occupant on Functions.occupantOID = Occupant.OID
inner join OrganizationUnit inner join Organization on OrganizationUnit.organizationOID=Organization.OID on Functions.organizationUnitOID=OrganizationUnit.OID
inner join FunctionDefinition on Functions.definitionOID=FunctionDefinition.OID
left join FunctionLevel on Functions.approvalLevelOID=FunctionLevel.OID
left join Users Manager on Functions.specifiedManagerOID=Manager.OID

--SYN_UnitManager
insert into SYN_UnitManager(unitId,orgId,managerId)
select
OrganizationUnit.id as unitId,
Organization.id as orgId,
Manager.id as managerId
from OrganizationUnit
inner join Organization on OrganizationUnit.organizationOID=Organization.OID
left join Users Manager on OrganizationUnit.managerOID=Manager.OID
where OrganizationUnit.managerOID is not null
--SYN_Groups
insert into SYN_Groups(groupId,orgId,groupName)
select Groups.id as groupId,
Organization.id as orgId,
Groups.groupName as groupName
from Groups inner join Organization on Groups.organizationOID=Organization.OID

--SYN_Group_User
insert into SYN_Group_User(groupId,userId,orgId)
select Groups.id as groupId,
Users.id as userId,
Organization.id as orgId
from Group_User inner join Groups on Group_User.groupOID=Groups.OID
inner join Users on Group_User.userOID=Users.OID
inner join Organization on Groups.organizationOID=Organization.OID

--SYN_TITLEDEFINITION
insert into SYN_TitleDefinition (defName, orgId)
select titleDefinitionName as defName,
Organization.id
from TitleDefinition inner join Organization on TitleDefinition.organizationOID=Organization.OID

--SYN_Title
insert into SYN_Title(titleName,userId,unitId,orgId)
select titleDefinitionName as titleName,
Users.id as userId,
OrganizationUnit.id as unitId,
Organization.id as orgId
from Title inner join TitleDefinition on Title.definitionOID=TitleDefinition.OID
inner join OrganizationUnit on Title.organizationUnitOID=OrganizationUnit.OID
inner join Users on Title.occupantOID=Users.OID
inner join Organization on TitleDefinition.organizationOID=Organization.OID

--SYN_ROLEEFINITION
insert into SYN_RoleDefinition (defName, orgId)
select roleDefinitionName as defName,
Organization.id
from RoleDefinition inner join Organization on RoleDefinition.organizationOID=Organization.OID

--SYN_Role
insert into SYN_Role(roleName,userId,unitId,orgId)
select roleDefinitionName as roleName,
Users.id as userId,
OrganizationUnit.id as unitId,
Organization.id as orgId
from Role inner join RoleDefinition on Role.definitionOID=RoleDefinition.OID
inner join OrganizationUnit on Role.organizationUnitOID=OrganizationUnit.OID
inner join Users on Role.actorOID=Users.OID
inner join Organization on RoleDefinition.organizationOID=Organization.OID

--SYN_SUBSTITUTEDEFINITION
insert into SYN_SubstituteDefinition (userId, substituteId, substitutiveOrder)
select Users.id as userId,
Substitute.id as substituteId,
DefaultSubstituteDefinition.substitutiveOrder
from DefaultSubstituteDefinition inner join Users on DefaultSubstituteDefinition.ownerOID=Users.OID
inner join Users Substitute on DefaultSubstituteDefinition.substituteOID=Substitute.OID