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

��
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]    Script Date: 10/14/2014 20:04:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u���O9eNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Update]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@ProgramID varchar(200),
@AllowRight char(200),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
 AS 
	UPDATE [OTB_PRJ_WorkAuthorize] SET 
	[AllowRight] = @AllowRight,[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
	WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 

' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u�����
kP� 
--y��v
T�y�
--��f�Alina
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_PrjCopyAuth]

@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@PrjWorkTypeIDTwo char(36),
@ProjectNumberTwo varchar(50),
@CreateUser varchar(50),
@OrganizationID varchar(50)


 AS 
 
 --*Rd��vj҉r��v
kP��2�bkPK͑�
 DELETE  OTB_PRJ_WorkAuthorize  WHERE PrjWorkTypeID=@PrjWorkTypeIDTwo AND ProjectNumber=@ProjectNumberTwo AND OrganizationID=@OrganizationID
 
 --���
kP�
INSERT INTO 
[OTB_PRJ_WorkAuthorize](
[OrganizationID],[PrjWorkTypeID],[ProjectNumber],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
)
 SELECT 
  @OrganizationID
 ,@PrjWorkTypeIDTwo
 ,@ProjectNumberTwo
 ,[ProgramID]
  ,[AllowRight]
  ,[Memo]
  ,@CreateUser
  ,GETDATE()
  ,@CreateUser
 ,GETDATE()
  FROM [OTB_PRJ_WorkAuthorize]
  WHERE PrjWorkTypeID=@PrjWorkTypeID AND ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
  

' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--(u���N�\Hh҉r�rs�_�]\O��vRh�
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetWorkTypeByPrjRuleID]
@OrganizationID varchar(50),
@PrjRuleID char(36)
 AS 
	SELECT WType.PrjWorkTypeID,PrjWorkTypeCName 
	FROM OTB_PRJ_PrjRuleWorkType AS WType
	INNER JOIN OTB_PRJ_PrjWorkType AS PWType ON WType.OrganizationID = PWType.OrganizationID AND WType.PrjWorkTypeID = PWType.PrjWorkTypeID
	WHERE WType.OrganizationID=@OrganizationID AND PrjRuleID=@PrjRuleID


' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u���gb�nj�e�Oo` 
--��v
T1z�gQ萡{t�|q}
--Create�Alina
--Bf���2014/10/14
-- exec OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID @RuleID,@OrganizationID
------------------------------------
	
CREATE	 PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetProgramListByWorkTypeID]
@PrjWorkTypeID char(36)  --�]\O��v�}_�
,@ProjectNumber varchar(50)--\Hh�}_�
,@OrganizationID VARCHAR(50) --D}T~�}_�
 AS 
DECLARE @strSettingValue NVARCHAR(200)
SET @strSettingValue=''''

--\Hhz_!jD}
SELECT @strSettingValue=SettingValue FROM dbo.OTB_SYS_SystemSetting  WHERE SettingItem=''PrjAuthMod'' AND OrganizationID=@OrganizationID;


   WITH ModuleLayer (ParentID,ModuleID,ModuleName,OrderByValue,Module_PATH,Module_LEVEL) AS
            (
            select ParentID,ModuleID,ModuleName,OrderByValue,CAST(ModuleName as varchar(MAX)),0 AS Module_LEVEL
            from OTB_SYS_ModuleList
            where ParentID = '''' AND ModuleID <> ''001'' AND OrganizationID = @OrganizationID --AND ModuleID IN (''DEV'',''PRJ'')
            union all
            select M.ParentID,M.ModuleID,M.ModuleName,M.OrderByValue,CAST(ML.Module_PATH+'' > ''+M.ModuleName as varchar(MAX)),ML.Module_LEVEL+1
            from OTB_SYS_ModuleList M INNER JOIN ModuleLayer ML on M.ParentID=ML.ModuleID AND M.OrganizationID = @OrganizationID
            WHERE CHARINDEX(M.ModuleID+'';'',@strSettingValue) >0
            )
            ,
            kk as (
            select MM.ModuleID as ParentID,MM.ModuleID,'''' as ModuleName, PL.ProgramID,PL.ProgramName,FilePath,AllowRight,AllowRight as CanAllowRight,ProgramType,PL.OrderByValue,PL.Effective,PL.Memo,Module_PATH,Module_LEVEL from ModuleLayer MM
            left join OTB_SYS_ProgramList PL
            on MM.ModuleID = PL.ModuleID 
            where PL.ProgramType <> ''S'' AND PL.OrganizationID = @OrganizationID AND  CHARINDEX(PL.ModuleID+'';'',@strSettingValue) >0
            union all
            select ParentID, ModuleID, ModuleName, '''' as ProgramID, ModuleName as ProgramName, ''#'' as FilePath, '''' as AllowRight, '''' as CanAllowRight, ''M'' ProgramType, OrderByValue, '''' as Effective, '''' as Memo, Module_PATH, Module_LEVEL from ModuleLayer
            )
            
             
            
            
            select ROW_NUMBER() OVER(order by Module_PATH, Module_LEVEL, ProgramType, OrderByValue) as RowId,
             @PrjWorkTypeID as PrjWorkTypeID, --�]\O��v
             @ProjectNumber AS ProjectNumber,--\Hh�}_�
             ParentID,
             ModuleID,
             ModuleName,
             MP.ProgramID,
             ProgramName,
             FilePath,
             AUT.AllowRight,
             MP.AllowRight as CanAllowRight,
             ProgramType as PageType,
             OrderByValue,
             Effective,
             ISNULL(MP.Memo,'''') as Memo,
             Module_PATH,
             Module_LEVEL 
            from kk as MP
            left join OTB_PRJ_WorkAuthorize AUT ON AUT.ProgramID = MP.ProgramID AND AUT.PrjWorkTypeID = @PrjWorkTypeID AND AUT.OrganizationID = @OrganizationID AND ProjectNumber=@ProjectNumber 
            where ParentID <> ''''' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u���N�\Hh^��Wrs�_\Hh҉r�N�b�U
--y��v
T�y�
--��f�Alina
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetPrjRuleListByPrjTypeID]
@OrganizationID varchar(50),
@PrjTypeID char(36)
 AS 
 
SELECT PrjRuleID,PrjRuleCName 
FROM dbo.OTB_PRJ_PrjRule
WHERE OrganizationID=@OrganizationID AND PrjTypeID=@PrjTypeID' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u���_0R�[SO�[a��v���~�Oo` 
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_GetModel]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@ProgramID varchar(200)
 AS 
	SELECT 
	OrganizationID,PrjWorkTypeID,ProjectNumber,ProgramID,AllowRight,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
	 FROM [OTB_PRJ_WorkAuthorize]
	 WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 

' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u��/f&T�]�~X[(W 
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Exists]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@ProgramID varchar(200)
AS
	DECLARE @TempID int
	SELECT @TempID = count(1) FROM [OTB_PRJ_WorkAuthorize] WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 
	IF @TempID = 0
		RETURN 0
	ELSE
		RETURN 1

' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u�� Rd�Nag��U_ 
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_Delete]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@ProgramID varchar(200)
 AS 
	DELETE [OTB_PRJ_WorkAuthorize]
	 WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 

' 
END
GO
/****** Object:  StoredProcedure [dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]    Script Date: 10/14/2014 20:04:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--(u���X�RNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/10/14 
NHS 11:23:20
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_WorkAuthorize_ADD]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@ProjectNumber varchar(50),
@ProgramID varchar(200),
@AllowRight char(200),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime

 AS 
 
  --Add by Alina 20131205 $R�er�F{nj�e/f&TX[(W�X[(W/f�O9e�
NX[(W�v�e�X�
	 DECLARE @TempID int
	SELECT @TempID =count(1) FROM [OTB_PRJ_WorkAuthorize] WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 
IF @TempID = 0  --
NX[(W
		BEGIN

			INSERT INTO [OTB_PRJ_WorkAuthorize](
			[OrganizationID],[PrjWorkTypeID],[ProjectNumber],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
			)VALUES(
			@OrganizationID,@PrjWorkTypeID,@ProjectNumber,@ProgramID,@AllowRight,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE()
			)
	 END
	
ELSE 
	BEGIN
	 UPDATE [OTB_PRJ_WorkAuthorize] SET 
 	[AllowRight] = @AllowRight,[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
	  WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID and ProjectNumber=@ProjectNumber and ProgramID=@ProgramID 
	
	END

' 
END
GO