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




------------------------------------
--用途：修改資料
--項目名稱：
--Create：Jack
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_Update]
@OrganizationID   Varchar (36 ),--組織編號    
@OrganizationName NVarchar(200),--組織名稱    
@OwnerName        NVarchar(50 ),--負責人姓名  
@Email            NVarchar(200),--使用者E-Mail
@ContectTEL       Varchar (50 ),--聯絡電話    
@ContectExt       Varchar (20 ),--聯絡分機    
@ContectFax       Varchar (50 ),--聯絡傳真    
@ContectCell      Varchar (50 ),--聯絡手機    
@Address          NVarchar(500),--住址        
@Files            Varchar (36 ),--附件        
@EffectiveSD      DateTime     ,--生效開始日期
@EffectiveED      DateTime     ,--生效結束日期
@Effective        Char    (1  ),--是否有效    
@Memo             Text         ,--備註        
@CreateUser       Varchar (50 ),--建立人員帳號
@CreateDate       DateTime     ,--建立日期    
@ModifyUser       Varchar (50 ),--修改人員帳號
@ModifyDate       DateTime     ,--修改日期 
@MemberID         VARCHAR(50),  --登錄帳號
@MemberPwd        VARCHAR(50),  --登錄密碼
@ProxyED          DateTime,     --代理結束日期
@ProxySD          DateTime,     --代理開始日期
@ParentID         VARCHAR(50),  --上級組織
@ModuleIDList     NVarChar(max),--模組代碼
@ProgramIDList    NVarChar(max),--程式代碼
@IsProxy          CHAR(1),      --是否代理商
@OrganizationIDOld VarChar(50),   --
@MemberIDNow      VarChar(50),   --當前登錄者
@Url              VarChar(200),   --當前登錄者
@LoginURL         VarChar(200),   --當前登錄者
@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