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



------------------------------------
--功能：查詢記錄信息
--項目名稱：MidWeb
--Create：John
--時間：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 '項次'
              ,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS '專案編號'
              ,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS '模組編號'
              ,ProgramName AS '程式名稱'
              ,ProgramType AS '程式類別'
              ,ProgramVersion AS '程式版本'
              ,ProgramDescription AS '程式說明'
              ,CONVERT(nvarchar(2000), ISNULL(PrgAvgProgress,0))+'%'AS '平均程式開發進度'
              ,PrgWorkCount AS '工作數量'
	          ,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMinWorkTimeID),'PrgWorkTyp') AS '程式排班ID最小'
	          ,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMaxWorkTimeID),'PrgWorkTyp') AS '程式排班ID最大'
              ,Memo   AS '備註'
               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 '項次'


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



--根據專案模組編號獲取模組名稱
CREATE FUNCTION [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]
(
 @PrjTypeID varchar(50) --傳入的字符串
)
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





------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：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


------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/7/29 上午 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



------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/7/29 上午 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



------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/7/29 上午 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




------------------------------------
--功能：獲取人員名稱列表
--項目名稱：MidWeb
--Create：John
--時間：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 ),--組織編號          
@ModuleID         Varchar (50 ),--模組代碼          
@ModuleCode       Varchar (50 ),--模組編號          
@ModuleName       NVarchar(100),--模組名稱          
@ProjectNumber    Varchar (50 ),--專案編號          
@ParentID         Varchar (50 ) ,--上層模組ID        
@ModAvgProgress   Int          ,--平均程式開發進度  
@ModWorkCount     int          ,--工作數量          
@ModMinWorkTimeID Varchar (50 ),--程式排班ID        
@ModMaxWorkTimeID Varchar (50 ),--程式排班ID        
@OrderByValue     Int          ,--排序欄位          
@Memo             Text         ,--備註              
@CreateUser       Varchar (50 ),--建立人員帳號      
@CreateDate       DateTime     ,--建立日期          
@ModifyUser       Varchar (50 ),--修改人員帳號      
@ModifyDate       DateTime  ,     --修改日期 
@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



------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/7/25 下午 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



------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/7/25 下午 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



------------------------------------
--用途：新增一筆資料
--項目名稱：
--創建人：	jACK
--時間：2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_ADD]
@OrganizationID   Varchar (50  ),
@ProjectNumber    Varchar (50  ),--專案編號        
@CustomerID       Varchar (50  ),--客戶編號        
@ProjectEName     Nvarchar(200 ),--專案名稱        
@ProjectCName     Nvarchar(200 ),--專案中文名稱    
@ProjectManager   Varchar (50  ),--專案經理        
@ProjectClass     Varchar (200 ),--專案類別        
@ProgramLanguage  Varchar (200 ),--程式語言        
@ProgramVersion   Varchar (10  ),--程式語言版本    
@Database         Varchar (200 ),--資料庫應用      
@DatabaseVersion  Varchar (10  ),--資料庫版本      
@PStartDate       DateTime      ,   --預估開始日期    
@PDeadLine        DateTime      ,   --預估截止日期    
@ActualStartDate  DateTime      ,     --實際開始日期    
@ActualDeadLine   DateTime      ,     --實際截止日期    
@ProjectStatus    Varchar (5   ),--專案狀態        
@PrjAvgProgress   Int           ,      --平均程式開發進度
@PrjWorkCount     int           ,    --工作數量        
@PrjMinWorkTimeID Varchar (50  ),--程式排班ID      
@PrjMaxWorkTimeID Varchar (50  ),--程式排班ID  
@Notice           Nvarchar(2000),-- 注意項目                    
@Memo             Text          ,  -- 備註                        
@CreateUser       Varchar (50  ),-- 建立人員帳號                
@CreateDate       DateTime      ,  -- 建立日期      
@ModifyUser       Varchar (50  ),-- 修改人員帳號  
@ModifyDate       DateTime ,     -- 修改日期
@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


------------------------------------
--用途：刪除一筆資料
--項目名稱：
--創建人：	jACK
--時間：2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Delete]
 @OrganizationID         Varchar (50  ),--專案編號   
 @ProjectNumber         Varchar (50  )--專案編號   
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


------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/7/9 下午 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



------------------------------------
--用途：更新一筆資料
--項目名稱：
--創建人：	jACK
--時間：2014/07/22
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Update]
@OrganizationID   Varchar (50),
@ProjectNumber    Varchar (50),--專案編號        
@CustomerID       Varchar (50),--客戶編號        
@ProjectEName     Nvarchar(200),--專案名稱        
@ProjectCName     Nvarchar(200),--專案中文名稱    
@ProjectManager   Varchar (50),--專案經理        
@ProjectClass     Varchar (200),--專案類別        
@ProgramLanguage  Varchar (200),--程式語言        
@ProgramVersion   Varchar (10),--程式語言版本    
@Database         Varchar (200),--資料庫應用      
@DatabaseVersion  Varchar (10),--資料庫版本      
@PStartDate       DateTime      ,   --預估開始日期    
@PDeadLine        DateTime      ,   --預估截止日期    
@ActualStartDate  DateTime      ,     --實際開始日期    
@ActualDeadLine   DateTime      ,     --實際截止日期    
@ProjectStatus    Varchar (5),--專案狀態        
@PrjAvgProgress   Int           ,      --平均程式開發進度
@PrjWorkCount     int           ,    --工作數量        
@PrjMinWorkTimeID Varchar (50),--程式排班ID      
@PrjMaxWorkTimeID Varchar (50),--程式排班ID  
@Notice           Nvarchar(2000),-- 注意項目                    
@Memo             Text          ,  -- 備註                        
@CreateUser       Varchar (50),-- 建立人員帳號                
@CreateDate       DateTime      ,  -- 建立日期      
@ModifyUser       Varchar (50),-- 修改人員帳號  
@ModifyDate       DateTime ,     -- 修改日期 
@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



------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/24 下午 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



------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/9/24 下午 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


------------------------------------
--用途：查询记录信息 
--项目名称：
--说明：
--时间：2014/9/24 下午 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