OPMS/02-系統分析、設計階段/DBScript/20141030/Organization_Update.sql

/****** Object:  StoredProcedure [dbo].[OSP_OTB_SYS_Organization_Update]    Script Date: 10/30/2014 10:10:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SYS_Organization_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_SYS_Organization_Update]
GO




------------------------------------
--�γ~�G�ק�����
--���ئW�١G
--Create�GJack
--�ɶ��G2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_Update]
@OrganizationID   Varchar (36 ),--��´�s��    
@OrganizationName NVarchar(200),--��´�W��    
@OwnerName        NVarchar(50 ),--�t�d�H�m�W  
@Email            NVarchar(200),--�ϥΪ�E-Mail
@ContectTEL       Varchar (50 ),--�p���q��    
@ContectExt       Varchar (20 ),--�p������    
@ContectFax       Varchar (50 ),--�p���ǯu    
@ContectCell      Varchar (50 ),--�p������    
@Address          NVarchar(500),--���}        
@Files            Varchar (36 ),--����        
@EffectiveSD      DateTime     ,--�ͮĶ}�l����
@EffectiveED      DateTime     ,--�ͮĵ�������
@Effective        Char    (1  ),--�O�_����    
@Memo             Text         ,--�Ƶ�        
@CreateUser       Varchar (50 ),--�إߤH���b��
@CreateDate       DateTime     ,--�إߤ���    
@ModifyUser       Varchar (50 ),--�ק��H���b��
@ModifyDate       DateTime     ,--�ק����� 
@MemberID         VARCHAR(50),  --�n���b��
@MemberPwd        VARCHAR(50),  --�n���K�X
@ProxyED          DateTime,     --�N�z��������
@ProxySD          DateTime,     --�N�z�}�l����
@ParentID         VARCHAR(50),  --�W�Ų�´
@ModuleIDList     NVarChar(max),--�ҲեN�X
@ProgramIDList    NVarChar(max),--�{���N�X
@IsProxy          CHAR(1),      --�O�_�N�z��
@OrganizationIDOld VarChar(50),   --
@MemberIDNow      VarChar(50),   --���e�n����
@Url              VarChar(200),   --���e�n����
@LoginURL         VarChar(200),   --���e�n����
@Email_PM        NVarchar(200)  --�ϥΪ�E-Mail
AS 
IF @OrganizationID!=@OrganizationIDOld
BEGIN

Begin TRANSACTION
UPDATE dbo.OTB_SYS_Organization SET
OrganizationName=@OrganizationName,
OwnerName       =@OwnerName       ,
Email           =@Email           ,
ContectTEL      =@ContectTEL      ,
ContectExt      =@ContectExt      ,
ContectFax      =@ContectFax      ,
ContectCell     =@ContectCell     ,
Address         =@Address         ,
Files           =@Files           ,
EffectiveSD     =@EffectiveSD     ,
EffectiveED     =@EffectiveED     ,
Effective       =@Effective       ,
Memo            =@Memo            ,
CreateUser      =@CreateUser      ,
CreateDate      =@CreateDate      ,
ModifyUser      =@ModifyUser      ,
ModifyDate      =@ModifyDate      ,
 ProxyED=@ProxyED,
ProxySD=@ProxySD,
ParentID=@ParentID,
IsProxy=@IsProxy,
Url=@Url,
LoginURL=@LoginURL
WHERE 
OrganizationID=@OrganizationID


IF REPLACE(@ProgramIDList,';','')!='' AND  @OrganizationID!=@OrganizationIDOld
BEGIN
DELETE dbo.OTB_SYS_ProgramList WHERE OrganizationID=@OrganizationID
INSERT INTO dbo.OTB_SYS_ProgramList
        ( OrganizationID ,
          ProgramID ,
          LanguageID ,
          ProgramName ,
          ModuleID ,
          FilePath ,
          ImgPath ,
          AllowRight ,
          OrderByValue ,
          ProgramType ,
          BackgroundCSS ,
          GroupTag ,
          Effective ,
          ShowInList ,
          MainTableName ,
          Memo ,
          CreateUser ,
          CreateDate ,
          ModifyUser ,
          ModifyDate
        )
SELECT
          @OrganizationID ,
          ProgramID ,
          LanguageID ,
          ProgramName ,
          ModuleID ,
          FilePath ,
          ImgPath ,
          AllowRight ,
          OrderByValue ,
          ProgramType ,
          BackgroundCSS ,
          GroupTag ,
          Effective ,
          ShowInList ,
          MainTableName ,
          Memo ,
          @CreateUser ,
          GETDATE() ,
          @CreateUser ,
          GETDATE()
FROM dbo.OTB_SYS_ProgramList 
WHERE CHARINDEX(';'+ProgramID+';',@ProgramIDList)>0 AND  OrganizationID=@OrganizationIDOld
END 

IF @MemberPwd!=''
BEGIN
UPDATE dbo.OTB_SYS_Members SET [Password]=@MemberPwd,Email=@Email_PM WHERE MemberID=@MemberID AND OrganizationID=@OrganizationID
END 
ELSE 
 BEGIN
UPDATE dbo.OTB_SYS_Members SET Email=@Email_PM WHERE MemberID=@MemberID AND OrganizationID=@OrganizationID
 END
DELETE dbo.OTB_SYS_Authorize WHERE OrganizationID=@OrganizationID AND RuleID='admin'
IF  REPLACE(@ProgramIDList,';','')!=''
BEGIN 
INSERT INTO dbo.OTB_SYS_Authorize
        ( OrganizationID ,
          RuleID ,
          ProgramID ,
          AllowRight ,
          Memo ,
          CreateUser ,
          CreateDate ,
          ModifyUser ,
          ModifyDate
        )
SELECT
          @OrganizationID ,
          'admin' ,
          ProgramID ,
          (select dbo.OFN_AVA_RemoveStringduplicate((SELECT (SELECT  ltrim(rtrim(AllowRight)) +'|' FROM dbo.OTB_SYS_Authorize WHERE OrganizationID=@OrganizationIDOld 
	AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow AND ProgramID=A.ProgramID) FOR XML PATH(''))),'|',1) )
          ,
           '' ,
          @CreateUser ,
          GETDATE() ,
          @CreateUser ,
           GETDATE()
FROM dbo.OTB_SYS_Authorize  AS A
WHERE OrganizationID=@OrganizationIDOld 
AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow)
 GROUP BY A.ProgramID
ORDER BY ProgramID
    END                     
IF @@error <> 0  
   BEGIN
     ROLLBACK TRANSACTION
   END
ELSE
   BEGIN
     COMMIT TRANSACTION
   END


END 
ELSE 
BEGIN

UPDATE dbo.OTB_SYS_Organization SET
OrganizationName=@OrganizationName,
OwnerName       =@OwnerName       ,
Email           =@Email           ,
ContectTEL      =@ContectTEL      ,
ContectExt      =@ContectExt      ,
ContectFax      =@ContectFax      ,
ContectCell     =@ContectCell     ,
Address         =@Address         ,
Files           =@Files           ,
EffectiveSD     =@EffectiveSD     ,
EffectiveED     =@EffectiveED     ,
Effective       =@Effective       ,
Memo            =@Memo            ,
CreateUser      =@CreateUser      ,
CreateDate      =@CreateDate      ,
ModifyUser      =@ModifyUser      ,
ModifyDate      =@ModifyDate      ,
 ProxyED=@ProxyED,
ProxySD=@ProxySD,
ParentID='',
IsProxy=@IsProxy,
Url=@Url,
LoginURL=@LoginURL
WHERE 
OrganizationID=@OrganizationID
END 
GO