OPMS/02-系統分析、設計階段/DBScript/20141010/20141010_John更新.sql

��

alter table OTB_PRJ_ProgramList add PrjTypeID varchar(50)  null DEFAULT '', PrjWorkTypeID varchar(max)  null DEFAULT '' 
alter table OTB_PRJ_ProjectInfo add PrjTypeID varchar(50)  null DEFAULT '', PrjWorkTypeID varchar(max)  null DEFAULT '' 
alter table OTB_PRJ_Modules add PrjTypeID varchar(50)  null DEFAULT '', PrjWorkTypeID varchar(max)  null DEFAULT '' 
alter table OTB_PRJ_Members  add ProjectWorkType varchar(max)  null DEFAULT ''
alter table OTB_PRJ_Members ALTER COLUMN  ProjectRule  varchar(max)


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



------------------------------------
--�R����gb����Oo`
--��v
T1z�MidWeb
--Create�John
--Bf���2014/07/23
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramName varchar(50),
@ProgramType varchar(20),
@OrganizationID varchar(50),
@SortExpression nvarchar(500)
 AS 
   SELECT   
        RowId
        ,OrganizationID
        ,ProgramID
        ,ProjectNumber
        ,ModuleID
        ,ProgramCode
        ,ProgramName
        ,ProgramType
        ,ProgramVersion
        ,PrgAvgProgress
        ,PrgWorkCount
        ,Memo
        ,ProjectValue
              FROM   
              (  
              SELECT   
              CASE @SortExpression WHEN N'ProjectNumber' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber))   
              	WHEN N'ProjectNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber DESC)) 	  
              	WHEN N'ModuleID' THEN   
              		(ROW_NUMBER() OVER(Order BY ModuleID)) 	  
              	WHEN N'ModuleID DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ModuleID DESC))  
              	WHEN N'ProgramName' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramName)) 	  
              	WHEN N'ProgramName DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramName DESC))   
              	WHEN N'ProgramType' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramType)) 	  
              	WHEN N'ProgramType DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramType DESC))	  
              	WHEN N'ProgramVersion' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramVersion)) 	  
              	WHEN N'ProgramVersion DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramVersion DESC))
              	WHEN N'PrgAvgProgress' THEN   
              		(ROW_NUMBER() OVER(Order BY PrgAvgProgress)) 	  
              	WHEN N'PrgAvgProgress DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PrgAvgProgress DESC))	  
              	WHEN N'PrgWorkCount' THEN   
              		(ROW_NUMBER() OVER(Order BY PrgWorkCount)) 	  
              	WHEN N'PrgWorkCount DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PrgWorkCount DESC))    
              	WHEN N'Memo' THEN   
              		(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo))) 	  
              	WHEN N'Memo DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC)) 
              	else   
              		(ROW_NUMBER() OVER(Order BY ProjectNumber))  
              END AS RowId
              ,OrganizationID
              ,ProgramID
              ,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS ProjectNumber
              ,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS ModuleID
              ,ProgramCode
              ,ProgramName
              ,dbo.OFN_AVA_ArgumentValueByArgumentID(ProgramType,'ProType') AS ProgramType
              ,ProgramVersion
              ,ISNULL(PrgAvgProgress,0) AS PrgAvgProgress
              ,PrgWorkCount
              ,Memo
              ,ProjectNumber AS ProjectValue 
               FROM OTB_PRJ_ProgramList  
               WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
                  AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')    
              	  AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')   
                  AND (ProgramType= @ProgramType OR @ProgramType='') 
                  AND (OrganizationID = @OrganizationID OR @OrganizationID='')
              ) AS AA  
              WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex   
              ORDER BY RowId
              
              SELECT   
              CASE @SortExpression WHEN N'ProjectNumber' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber))   
              	WHEN N'ProjectNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber DESC)) 	  
              	WHEN N'ModuleID' THEN   
              		(ROW_NUMBER() OVER(Order BY ModuleID)) 	  
              	WHEN N'ModuleID DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ModuleID DESC))  
              	WHEN N'ProgramName' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramName)) 	  
              	WHEN N'ProgramName DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramName DESC))   
              	WHEN N'ProgramType' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramType)) 	  
              	WHEN N'ProgramType DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramType DESC))	  
              	WHEN N'ProgramVersion' THEN   
              		(ROW_NUMBER() OVER(Order BY ProgramVersion)) 	  
              	WHEN N'ProgramVersion DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProgramVersion DESC))
              	WHEN N'PrgAvgProgress' THEN   
              		(ROW_NUMBER() OVER(Order BY PrgAvgProgress)) 	  
              	WHEN N'PrgAvgProgress DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PrgAvgProgress DESC))	  
              	WHEN N'PrgWorkCount' THEN   
              		(ROW_NUMBER() OVER(Order BY PrgWorkCount)) 	  
              	WHEN N'PrgWorkCount DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PrgWorkCount DESC))    
              	WHEN N'Memo' THEN   
              		(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo))) 	  
              	WHEN N'Memo DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC)) 
              	else   
              		(ROW_NUMBER() OVER(Order BY ProjectNumber)) 
              END AS '�!k'
              ,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS '\Hh�}_�'
              ,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS '!jD}�}_�'
              ,ProgramName AS 'z_
T1z'
              ,ProgramType AS 'z_^�%R'
              ,ProgramVersion AS 'z_Hr,g'
              ,ProgramDescription AS 'z_��f'
              ,CONVERT(nvarchar(2000), ISNULL(PrgAvgProgress,0))+'%'AS 's^GWz_��|v2��^'
              ,PrgWorkCount AS '�]\Oxeϑ'
	          ,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMinWorkTimeID),'PrgWorkTyp') AS 'z_�c�sIDg\'
	          ,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMaxWorkTimeID),'PrgWorkTyp') AS 'z_�c�sIDg'Y'
              ,Memo   AS '�P;�'
               FROM OTB_PRJ_ProgramList  
               WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
                  AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')    
              	  AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')   
                  AND (ProgramType= @ProgramType OR @ProgramType='') 
                  AND (OrganizationID = @OrganizationID OR @OrganizationID='')
                  ORDER BY '�!k'


GO






/****** Object:  UserDefinedFunction [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]    Script Date: 10/09/2014 14:26:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]
GO



--9h�d\Hh!jD}�}_�rs�S!jD}
T1z
CREATE FUNCTION [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]
(
 @PrjTypeID varchar(50) --�PeQ�vW[&{2N
)
RETURNS   NVARCHAR(4000)
AS
BEGIN 
	 DECLARE @Value nvarchar(max);
	 SET @Value=''
	 SELECT @Value = PrjTypeCName
	 FROM OTB_PRJ_PrjType
	 WHERE  PrjTypeID=@PrjTypeID
  --	 IF @Value=''
		--SET @Value=''
	 --ELSE 			
		--SET @Value=@Value
   RETURN @Value
   END



GO





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





------------------------------------
--�R����gb�nj�e=~F{xe
--��v
T1z�Web_CRM
--Create�John
--Bf���2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetListCount]
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramName varchar(50),
@OrganizationID varchar(50),
@ProgramType varchar(20)
 
 AS  
	
	  SELECT COUNT(0)  
               FROM OTB_PRJ_ProgramList  
               WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
                  AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')    
              	  AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')   
                  AND (ProgramType= @ProgramType OR @ProgramType='') 
                  AND (OrganizationID = @OrganizationID OR @OrganizationID='')


GO




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


------------------------------------
--(u���X�RNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/7/29 
NHS 09:28:52
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_ADD]
@OrganizationID varchar(50),
@ProgramID varchar(50),
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramCode varchar(50),
@ProgramName nvarchar(500),
@ProgramDescription nvarchar(500),
@ProgramType varchar(20),
@ProgramVersion varchar(10),
@PrgAvgProgress int,
@PrgWorkCount int,
@PrgMinWorkTimeID varchar(50),
@PrgMaxWorkTimeID varchar(50),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime,
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)

 AS 
	INSERT INTO [OTB_PRJ_ProgramList](
	[OrganizationID],[ProgramID],[ProjectNumber],[ModuleID],[ProgramCode],[ProgramName],[ProgramDescription],[ProgramType],[ProgramVersion],[PrgAvgProgress],[PrgWorkCount],[PrgMinWorkTimeID],[PrgMaxWorkTimeID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID,PrjWorkTypeID
	)VALUES(
	@OrganizationID,@ProgramID,@ProjectNumber,@ModuleID,@ProgramCode,@ProgramName,@ProgramDescription,@ProgramType,@ProgramVersion,@PrgAvgProgress,@PrgWorkCount,@PrgMinWorkTimeID,@PrgMaxWorkTimeID,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE(),@PrjTypeID,@PrjWorkTypeID
	)

GO




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



------------------------------------
--(u���_0R�[SO�[a��v���~�Oo` 
--y��v
T�y�
--��f�
--�e���2014/7/29 
NHS 09:28:52
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetModel]
@OrganizationID varchar(50),
@ProgramID varchar(50)
 AS 
	SELECT 
	OrganizationID,ProgramID,ProjectNumber,ModuleID,ProgramCode,ProgramName,ProgramDescription,ProgramType,ProgramVersion
	,ISNULL(PrgAvgProgress,0) AS PrgAvgProgress,PrgWorkCount
	,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMinWorkTimeID),'PrgWorkTyp') AS PrgMinWorkTimeID
	,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMaxWorkTimeID),'PrgWorkTyp') AS PrgMaxWorkTimeID
	,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
	 FROM [OTB_PRJ_ProgramList]
	 WHERE OrganizationID=@OrganizationID and ProgramID=@ProgramID 

GO



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



------------------------------------
--(u���O9eNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/7/29 
NHS 09:28:52
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_Update]
@OrganizationID varchar(50),
@ProgramID varchar(50),
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramCode varchar(50),
@ProgramName nvarchar(500),
@ProgramDescription nvarchar(500),
@ProgramType varchar(20),
@ProgramVersion varchar(10),
@PrgAvgProgress int,
@PrgWorkCount int,
@PrgMinWorkTimeID varchar(50),
@PrgMaxWorkTimeID varchar(50),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime,
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)
 AS 
	UPDATE [OTB_PRJ_ProgramList] SET 
	[ProjectNumber] = @ProjectNumber,[ModuleID] = @ModuleID,[ProgramCode] = @ProgramCode,[ProgramName] = @ProgramName,
	[ProgramDescription] = @ProgramDescription,[ProgramType] = @ProgramType,[ProgramVersion] = @ProgramVersion,
	[PrgAvgProgress] = @PrgAvgProgress,[PrgWorkCount] = @PrgWorkCount,[PrgMinWorkTimeID] = @PrgMinWorkTimeID,[PrgMaxWorkTimeID] = @PrgMaxWorkTimeID,
	[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID,PrjWorkTypeID=@PrjWorkTypeID
	WHERE OrganizationID=@OrganizationID and ProgramID=@ProgramID 

GO



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




------------------------------------
--�R���rs�S�N�T
T1zRh�
--��v
T1z�MidWeb
--Create�John
--Bf���2014/09/30
------------------------------------
CREATE PROCEDURE [dbo].[OSP_Common_GetWorkTypeList]
@PrjTypeID varchar(50),
@OrganizationID NVARCHAR(50)     
AS 

SELECT DISTINCT PrjWorkTypeID AS ID, PrjWorkTypeCName as NAME 
FROM  OTB_PRJ_PrjWorkType
WHERE   PrjTypeID =@PrjTypeID AND OrganizationID=@OrganizationID
ORDER BY  NAME



GO



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



CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_ADD]
@OrganizationID   Varchar (50 ),--D}T~�}_�          
@ModuleID         Varchar (50 ),--!jD}�N�x          
@ModuleCode       Varchar (50 ),--!jD}�}_�          
@ModuleName       NVarchar(100),--!jD}
T1z          
@ProjectNumber    Varchar (50 ),--\Hh�}_�          
@ParentID         Varchar (50 ) ,--
Nd\!jD}ID        
@ModAvgProgress   Int          ,--s^GWz_��|v2��^  
@ModWorkCount     int          ,--�]\Oxeϑ          
@ModMinWorkTimeID Varchar (50 ),--z_�c�sID        
@ModMaxWorkTimeID Varchar (50 ),--z_�c�sID        
@OrderByValue     Int          ,--�c�^kMO          
@Memo             Text         ,--�P;�              
@CreateUser       Varchar (50 ),--�^�z�N�T3^_�      
@CreateDate       DateTime     ,--�^�z�eg          
@ModifyUser       Varchar (50 ),--�O9e�N�T3^_�      
@ModifyDate       DateTime  ,     --�O9e�eg 
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)        
AS 
INSERT INTO dbo.OTB_PRJ_Modules
        ( 
        OrganizationID  ,
ModuleID        ,
ModuleCode      ,
ModuleName      ,
ProjectNumber   ,
ParentID        ,
ModAvgProgress  ,
ModWorkCount    ,
ModMinWorkTimeID,
ModMaxWorkTimeID,
OrderByValue    ,
Memo            ,
CreateUser      ,
CreateDate      ,
ModifyUser      ,
ModifyDate  ,
PrjTypeID,
PrjWorkTypeID   

        )
VALUES  ( @OrganizationID  ,
@ModuleID        ,
@ModuleCode      ,
@ModuleName      ,
@ProjectNumber   ,
@ParentID        ,
@ModAvgProgress  ,
@ModWorkCount    ,
@ModMinWorkTimeID,
@ModMaxWorkTimeID,
@OrderByValue    ,
@Memo            ,
@CreateUser      ,
GETDATE()      ,
@CreateUser      ,
GETDATE()    ,
@PrjTypeID,
@PrjWorkTypeID  

        )
GO




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



------------------------------------
--(u���_0R�[SO�[a��v���~�Oo` 
--y��v
T�y�
--��f�
--�e���2014/7/25 NHS 04:13:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_GetModel]
@OrganizationID varchar(50),
@ModuleID varchar(50),
@ProjectNumber varchar(50)
 AS 
	SELECT 
	OrganizationID,ModuleID,ModuleCode,ModuleName,ProjectNumber,ParentID,ModAvgProgress,ModWorkCount,ModMinWorkTimeID,ModMaxWorkTimeID,OrderByValue,Memo,
	CreateUser,CreateDate,ModifyUser,ModifyDate,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
	 FROM [OTB_PRJ_Modules]
	 WHERE OrganizationID=@OrganizationID and ModuleID=@ModuleID AND ProjectNumber=@ProjectNumber

GO



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



------------------------------------
--(u���O9eNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/7/25 NHS 04:13:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_Update]
@OrganizationID varchar(50),
@ModuleID varchar(50),
@ModuleCode varchar(50),
@ModuleName nvarchar(100),
@ProjectNumber varchar(50),
@ParentID varchar(50),
@ModAvgProgress int,
@ModWorkCount int,
@ModMinWorkTimeID varchar(50),
@ModMaxWorkTimeID varchar(50),
@OrderByValue int,
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime,
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)
 AS 
	UPDATE [OTB_PRJ_Modules] SET 
	[ModuleCode] = @ModuleCode,[ModuleName] = @ModuleName,[ProjectNumber] = @ProjectNumber,[ParentID] = @ParentID,[ModAvgProgress] = @ModAvgProgress,
	[ModWorkCount] = @ModWorkCount,[ModMinWorkTimeID] = @ModMinWorkTimeID,[ModMaxWorkTimeID] = @ModMaxWorkTimeID,[OrderByValue] = @OrderByValue,
	[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID,PrjWorkTypeID=@PrjWorkTypeID
	WHERE OrganizationID=@OrganizationID and ModuleID=@ModuleID

GO



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



------------------------------------
--(u���e�XNF{nj�e
--��v
T1z�
--uR�^�N�	jACK
--Bf���2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_ADD]
@OrganizationID   Varchar (50  ),
@ProjectNumber    Varchar (50  ),--\Hh�}_�        
@CustomerID       Varchar (50  ),--�[6b�}_�        
@ProjectEName     Nvarchar(200 ),--\Hh
T1z        
@ProjectCName     Nvarchar(200 ),--\Hh-N�e
T1z    
@ProjectManager   Varchar (50  ),--\Hh�}t        
@ProjectClass     Varchar (200 ),--\Hh^�%R        
@ProgramLanguage  Varchar (200 ),--z_���        
@ProgramVersion   Varchar (10  ),--z_���Hr,g    
@Database         Varchar (200 ),--nj�e�^�a(u      
@DatabaseVersion  Varchar (10  ),--nj�e�^Hr,g      
@PStartDate       DateTime      ,   --�0O���Y�eg    
@PDeadLine        DateTime      ,   --�0O*bbk�eg    
@ActualStartDate  DateTime      ,     --�[�����Y�eg    
@ActualDeadLine   DateTime      ,     --�[��*bbk�eg    
@ProjectStatus    Varchar (5   ),--\Hh�rKa        
@PrjAvgProgress   Int           ,      --s^GWz_��|v2��^
@PrjWorkCount     int           ,    --�]\Oxeϑ        
@PrjMinWorkTimeID Varchar (50  ),--z_�c�sID      
@PrjMaxWorkTimeID Varchar (50  ),--z_�c�sID  
@Notice           Nvarchar(2000),-- �la��v                    
@Memo             Text          ,  -- �P;�                        
@CreateUser       Varchar (50  ),-- �^�z�N�T3^_�                
@CreateDate       DateTime      ,  -- �^�z�eg      
@ModifyUser       Varchar (50  ),-- �O9e�N�T3^_�  
@ModifyDate       DateTime ,     -- �O9e�eg
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)         
AS 
INSERT INTO  dbo.OTB_PRJ_ProjectInfo
        ( 
OrganizationID   ,
ProjectNumber    ,
CustomerID       ,
ProjectEName     ,
ProjectCName     ,
ProjectManager   ,
ProjectClass     ,
ProgramLanguage  ,
ProgramVersion   ,
[Database]         ,
DatabaseVersion  ,
PStartDate       ,
PDeadLine        ,
ActualStartDate  ,
ActualDeadLine   ,
ProjectStatus    ,
PrjAvgProgress   ,
PrjWorkCount     ,
PrjMinWorkTimeID ,
PrjMaxWorkTimeID ,
Notice           ,
Memo             ,
CreateUser       ,
CreateDate       ,
ModifyUser       ,
ModifyDate    ,
PrjTypeID,
PrjWorkTypeID       

        )
VALUES  ( 
 @OrganizationID   ,
@ProjectNumber    ,
@CustomerID       ,
@ProjectEName     ,
@ProjectCName     ,
@ProjectManager   ,
@ProjectClass     ,
@ProgramLanguage  ,
@ProgramVersion   ,
@Database         ,
@DatabaseVersion  ,
@PStartDate       ,
@PDeadLine        ,
@ActualStartDate  ,
@ActualDeadLine   ,
@ProjectStatus    ,
@PrjAvgProgress   ,
@PrjWorkCount     ,
@PrjMinWorkTimeID ,
@PrjMaxWorkTimeID ,
@Notice           ,
@Memo             ,
@ModifyUser       ,
GETDATE()        ,
@ModifyUser       ,
GETDATE()      ,
@PrjTypeID,
@PrjWorkTypeID   
 
        )

GO



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


------------------------------------
--(u��*Rd�NF{nj�e
--��v
T1z�
--uR�^�N�	jACK
--Bf���2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Delete]
 @OrganizationID         Varchar (50  ),--\Hh�}_�   
 @ProjectNumber         Varchar (50  )--\Hh�}_�   
AS 
Begin TRANSACTION

DELETE dbo.OTB_PRJ_ProjectInfo
WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
	--DELETE dbo.OTB_PRJ_PrjWorkTime
	-- WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
	DELETE dbo.OTB_PRJ_Members
	 WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
	DELETE dbo.OTB_PRJ_Customers
	 WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber

IF @@error <> 0  
   BEGIN
     ROLLBACK TRANSACTION
   END
ELSE
   BEGIN
     COMMIT TRANSACTION
   END


GO




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


------------------------------------
--(u���_0R�[SO�[a��v���~�Oo` 
--y��v
T�y�
--��f�
--�e���2014/7/9 NHS 07:05:11
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetModel]
@ProjectNumber varchar(50),
@OrganizationID varchar(50)
 AS 
	SELECT 
a.OrganizationID ,
	           ProjectNumber ,
	           a.CustomerID ,
	           ProjectEName ,
	           ProjectCName ,
	           ProjectManager ,
	           ProjectClass ,
	           ProgramLanguage ,
	           ProgramVersion ,
	           [Database] ,
	           DatabaseVersion ,
	           PStartDate ,
	           PDeadLine ,
	           ActualStartDate ,
	           ActualDeadLine ,
	           ProjectStatus ,
	           PrjAvgProgress ,
	           PrjWorkCount ,
	           PrjMinWorkTimeID ,
	           PrjMaxWorkTimeID ,
	           Notice ,
	           a.Memo ,
	           a.CreateUser ,
	           a.CreateDate ,
	           a.ModifyUser ,
	           a.ModifyDate  ,
	           b.CustomerName ,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
	 FROM  dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_CRM_Customers AS b
	 ON a.CustomerID = b.CustomerID
	 WHERE ProjectNumber=@ProjectNumber AND a.OrganizationID=@OrganizationID
	 
	
GO


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



------------------------------------
--(u���f�eNF{nj�e
--��v
T1z�
--uR�^�N�	jACK
--Bf���2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Update]
@OrganizationID   Varchar (50),
@ProjectNumber    Varchar (50),--\Hh�}_�        
@CustomerID       Varchar (50),--�[6b�}_�        
@ProjectEName     Nvarchar(200),--\Hh
T1z        
@ProjectCName     Nvarchar(200),--\Hh-N�e
T1z    
@ProjectManager   Varchar (50),--\Hh�}t        
@ProjectClass     Varchar (200),--\Hh^�%R        
@ProgramLanguage  Varchar (200),--z_���        
@ProgramVersion   Varchar (10),--z_���Hr,g    
@Database         Varchar (200),--nj�e�^�a(u      
@DatabaseVersion  Varchar (10),--nj�e�^Hr,g      
@PStartDate       DateTime      ,   --�0O���Y�eg    
@PDeadLine        DateTime      ,   --�0O*bbk�eg    
@ActualStartDate  DateTime      ,     --�[�����Y�eg    
@ActualDeadLine   DateTime      ,     --�[��*bbk�eg    
@ProjectStatus    Varchar (5),--\Hh�rKa        
@PrjAvgProgress   Int           ,      --s^GWz_��|v2��^
@PrjWorkCount     int           ,    --�]\Oxeϑ        
@PrjMinWorkTimeID Varchar (50),--z_�c�sID      
@PrjMaxWorkTimeID Varchar (50),--z_�c�sID  
@Notice           Nvarchar(2000),-- �la��v                    
@Memo             Text          ,  -- �P;�                        
@CreateUser       Varchar (50),-- �^�z�N�T3^_�                
@CreateDate       DateTime      ,  -- �^�z�eg      
@ModifyUser       Varchar (50),-- �O9e�N�T3^_�  
@ModifyDate       DateTime ,     -- �O9e�eg 
@PrjTypeID varchar(50),
@PrjWorkTypeID varchar(max)      
AS 
UPDATE dbo.OTB_PRJ_ProjectInfo
SET 
CustomerID=@CustomerID       ,
ProjectEName=@ProjectEName     ,
ProjectCName=@ProjectCName     ,
ProjectManager=@ProjectManager   ,
ProjectClass=@ProjectClass     ,
ProgramLanguage=@ProgramLanguage  ,
ProgramVersion=@ProgramVersion   ,
[Database]=@Database         ,
DatabaseVersion=@DatabaseVersion  ,
PStartDate=@PStartDate       ,
PDeadLine=@PDeadLine        ,
ActualStartDate=@ActualStartDate  ,
ActualDeadLine=@ActualDeadLine   ,
ProjectStatus=@ProjectStatus    ,
PrjAvgProgress=@PrjAvgProgress   ,
PrjWorkCount=@PrjWorkCount     ,
PrjMinWorkTimeID=@PrjMinWorkTimeID ,
PrjMaxWorkTimeID=@PrjMaxWorkTimeID ,
Notice=@Notice           ,
Memo=@Memo             ,
ModifyUser=@ModifyUser       ,
ModifyDate=GETDATE(),
PrjTypeID=@PrjTypeID,
PrjWorkTypeID=@PrjWorkTypeID     

 WHERE      
     ProjectNumber=@ProjectNumber AND 
OrganizationID=@OrganizationID   

GO



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


CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule]
@OrganizationID VARCHAR(50)

 AS 
WITH ViewList(PrjRuleID, ParentID, Level, AccountNameSort) 
AS
(
    SELECT PrjRuleID
    , ParentID
    , 0
    ,PrjRuleCName
  
    FROM OTB_PRJ_PrjRule WHERE  (ParentID IS NULL OR ParentID = '')  AND OrganizationID=@OrganizationID
 
    UNION ALL
 
    SELECT P.PrjRuleID
    , P.ParentID
    , B.Level+1
    ,CONVERT(nVARCHAR(20),B.AccountNameSort + '-' + P.PrjRuleCName)
  
    FROM OTB_PRJ_PrjRule P, ViewList B
    WHERE P.ParentID=B.PrjRuleID   AND OrganizationID=@OrganizationID )
    SELECT PrjRuleID,AccountNameSort AS AccountNameSort
FROM ViewList   ORDER	BY AccountNameSort

GO



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



------------------------------------
--(u���X�RNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/9/24 NHS 03:07:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_ADD]
@OrganizationID varchar(50),
@ProjectNumber varchar(50),
@MemberID varchar(50),
@ProjectRule varchar(max),
@IsProtoType char(1),
@IsSA char(1),
@IsSD char(1),
@IsPG char(1),
@IsQC char(1),
@IsTraining char(1),
@ReceiveMail char(1),
@WorkTypeID varchar(max)

 AS 
	INSERT INTO [OTB_PRJ_Members](
	[OrganizationID],[ProjectNumber],[MemberID],[ProjectRule],[IsProtoType],[IsSA],[IsSD],[IsPG],[IsQC],[IsTraining],[ReceiveMail],ProjectWorkType
	)VALUES(
	@OrganizationID,@ProjectNumber,@MemberID,@ProjectRule,@IsProtoType,@IsSA,@IsSD,@IsPG,@IsQC,@IsTraining,@ReceiveMail,@WorkTypeID
	)


GO




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



------------------------------------
--(u���O9eNag��U_ 
--y��v
T�y�
--��f�
--�e���2014/9/24 NHS 02:55:03
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_Update]
@OrganizationID varchar(50),
@ProjectNumber varchar(50),
@MemberID varchar(50),
@ProjectRule varchar(max),
@IsProtoType char(100),
@IsSA char(10),
@IsSD char(20),
@IsPG char(10),
@IsQC char(20),
@IsTraining char(200),
@ReceiveMail char(50),
@WorkTypeID varchar(max)
 AS 
 DECLARE @temp INT 
 SELECT @temp=COUNT(0) FROM dbo.OTB_PRJ_Members WHERE OrganizationID=@OrganizationID AND MemberID=@MemberID
 IF @temp>0
 BEGIN
	UPDATE [OTB_PRJ_Members] SET 
	[ProjectRule] = @ProjectRule,[IsProtoType] = @IsProtoType,[IsSA] = @IsSA,[IsSD] = @IsSD,[IsPG] = @IsPG,[IsQC] = @IsQC,[IsTraining] = @IsTraining,[ReceiveMail] = @ReceiveMail,ProjectWorkType=@WorkTypeID
	WHERE OrganizationID=@OrganizationID and ProjectNumber=@ProjectNumber and MemberID=@MemberID 

 END 
 ELSE 
 BEGIN
 
	INSERT INTO [OTB_PRJ_Members](
	[OrganizationID],[ProjectNumber],[MemberID],[ProjectRule],[IsProtoType],[IsSA],[IsSD],[IsPG],[IsQC],[IsTraining],[ReceiveMail],ProjectWorkType
	)VALUES(
	@OrganizationID,@ProjectNumber,@MemberID,@ProjectRule,@IsProtoType,@IsSA,@IsSD,@IsPG,@IsQC,@IsTraining,@ReceiveMail,@WorkTypeID
	)
 END 

GO



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


------------------------------------
--(u���g⋰�U_�Oo` 
--y��v
T�y�
--��f�
--�e���2014/9/24 NHS 03:19:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_GetList]
@ProjectNumber varchar(50),
@OrganizationID varchar(50)
 AS 
	SELECT 
	OrganizationID,ProjectNumber,MemberID,ProjectRule,[dbo].[FN_PRJ_GetPRJRuleValuebyRuleID](ProjectRule) AS ProjectRuleText,IsProtoType,IsSA,IsSD,IsPG,IsQC,IsTraining,ReceiveMail,'' AS [Status],ProjectWorkType,[dbo].[FN_PRJ_GetValuebyID](ProjectWorkType) AS WorkTypeText
	 FROM [OTB_PRJ_Members] WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber


GO