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


SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_ProjectInfo_GetList]'
GO



------------------------------------
--功能：查詢記錄信息
--項目名稱：
--創建人：	Jack
--時間：2014/07/23
------------------------------------
ALTER  PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetList]

@StartRecordIndex int,
@EndRecordIndex int,
@ProjectNumber varchar(50),
@CustomerID nvarchar(50),
@ProjectEName nvarchar(200),
@ProjectCName nvarchar(200),
@OrganizationID             NVarChar(50),         --客戶編號
@ProjectManager nvarchar(50),
@SortExpression nvarchar(500),
@ProjectStatus varchar(5)
 AS 
SELECT
RowId,
ProjectNumber ,--專案編號    
CustomerID    ,--客戶編號    
ProjectEName  ,--專案名稱    
ProjectCName  ,--專案中文名稱
ProjectManager,--專案經理    
PStartDate    ,--預估開始日期
PDeadLine     ,--預估截止日期
ProjectStatus ,--專案狀態
ModifyDate     --最新修改時間
,CustomerName
,OrganizationID
,PrjAvgProgress--平均程式開發進度
,PrjWorkCount  --工作數量
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'CustomerID' THEN 
				(ROW_NUMBER() OVER(Order BY a.CustomerID)) 	
			WHEN N'CustomerID DESC' THEN
				(ROW_NUMBER() OVER(Order BY a.CustomerID DESC)) 
					
			WHEN N'CustomerName' THEN 
				(ROW_NUMBER() OVER(Order BY CustomerName)) 	
			WHEN N'CustomerName DESC' THEN
				(ROW_NUMBER() OVER(Order BY CustomerName DESC))
				
			WHEN N'ProjectCName' THEN 
				(ROW_NUMBER() OVER(Order BY ProjectCName)) 	
			WHEN N'ProjectCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProjectCName DESC))
				
			WHEN N'ProjectEName' THEN 
				(ROW_NUMBER() OVER(Order BY ProjectEName)) 	
			WHEN N'ProjectEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProjectEName DESC))
				
			WHEN N'ProjectManager' THEN 
				(ROW_NUMBER() OVER(Order BY ProjectManager)) 	
			WHEN N'ProjectManager DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProjectManager DESC))
				
			WHEN N'PStartDate' THEN 
				(ROW_NUMBER() OVER(Order BY PStartDate)) 	
			WHEN N'PStartDate DESC' THEN
				(ROW_NUMBER() OVER(Order BY PStartDate DESC))
				
			WHEN N'PDeadLine' THEN 
				(ROW_NUMBER() OVER(Order BY PDeadLine)) 	
			WHEN N'PDeadLine DESC' THEN
				(ROW_NUMBER() OVER(Order BY PDeadLine DESC))
			WHEN N'PrjAvgProgress' THEN 
				(ROW_NUMBER() OVER(Order BY PrjAvgProgress)) 	
			WHEN N'PrjAvgProgress DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjAvgProgress DESC))
			WHEN N'PrjWorkCount' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkCount)) 	
			WHEN N'PrjWorkCount DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkCount DESC))
				
			WHEN N'ProjectStatus' THEN 
				(ROW_NUMBER() OVER(Order BY ProjectStatus)) 	
			WHEN N'ProjectStatus DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProjectStatus DESC))
				
			ELSE
				(ROW_NUMBER() OVER(Order BY a.ModifyDate DESC)) 		
		END AS RowId,
ProjectNumber  ,--專案編號    
a.CustomerID  AS  CustomerID ,--客戶編號    
ProjectEName  ,--專案名稱    
ProjectCName  ,--專案中文名稱
b.MemberName AS ProjectManager,--專案經理    
PStartDate    ,--預估開始日期
PDeadLine     ,--預估截止日期
ProjectStatus ,--專案狀態
a.ModifyDate  AS ModifyDate  --最新修改時間
,CustomerName
,a.OrganizationID AS OrganizationID
,PrjAvgProgress--平均程式開發進度
,PrjWorkCount  --工作數量
	 FROM   dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_SYS_Members AS b  ON a.ProjectManager=b.MemberID--AS A ON A.DepartmentID=B.DepartmentID
	 LEFT JOIN dbo.OTB_CRM_Customers AS c  ON a.CustomerID=c.CustomerID
	 --LEFT JOIN dbo.OTB_SYS_Arguments AS e ON d.PrgWorkType=e.ArgumentID 
	 WHERE 
	--(ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
	--	AND (CustomerName LIKE @CustomerID  OR @CustomerID='%%')
	--	AND (ProjectEName  LIKE @ProjectEName  OR @ProjectEName='%%')
	--	AND (ProjectManager = @ProjectManager OR @ProjectManager='')
	--	AND (ProjectCName   LIKE @ProjectCName  OR @ProjectCName='%%')
	--	AND (ProjectStatus   = @ProjectStatus  OR @ProjectCName='')
	--	AND a.OrganizationID=@OrganizationID 
	 (ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
		AND (CustomerName LIKE @CustomerID  OR @CustomerID='%%')
		AND (ProjectEName  LIKE @ProjectEName  OR @ProjectEName='%%')
		AND (ProjectManager = @ProjectManager OR @ProjectManager='')
		AND (ProjectCName   LIKE @ProjectCName  OR @ProjectCName='%%')
		AND (ProjectStatus   = @ProjectStatus  OR @ProjectStatus='')
		AND a.OrganizationID=@OrganizationID 
		--AND (e.ArgumentClassID='PrgWKTP' )
		) AS AA 
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex 
ORDER BY RowId


SELECT

ProjectNumber AS'專案編號' ,--專案編號    
--a.CustomerID  AS  CustomerID ,--客戶編號    
ProjectEName  AS '專案名稱',--專案名稱    
ProjectCName AS '專案中文名稱' ,--專案中文名稱
b.MemberName AS '專案經理',--專案經理    
PStartDate    AS '預估開始日期',--預估開始日期
PDeadLine     AS '預估截止日期',--預估截止日期
a.ActualStartDate    AS '實際開始日期',--預估開始日期
a.ActualDeadLine     AS '實際截止日期',--預估截止日期
ProjectStatus AS '專案狀態',--專案狀態
--a.ModifyDate  AS ModifyDate  --最新修改時間
CustomerName AS '客戶名稱'
--,a.OrganizationID AS OrganizationID
,CONVERT(nvarchar(2000), ISNULL(PrjAvgProgress,0))+'%' AS '平均程式開發進度'--平均程式開發進度
,PrjWorkCount  AS'工作數量'--工作數量

	 FROM   dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_SYS_Members AS b  ON a.ProjectManager=b.MemberID--AS A ON A.DepartmentID=B.DepartmentID
	 LEFT JOIN dbo.OTB_CRM_Customers AS c  ON a.CustomerID=c.CustomerID
	 --LEFT JOIN dbo.OTB_SYS_Arguments AS e ON d.PrgWorkType=e.ArgumentID 
	 WHERE 
	    (ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
		AND (CustomerName LIKE @CustomerID  OR @CustomerID='%%')
		AND (ProjectEName  LIKE @ProjectEName  OR @ProjectEName='%%')
		AND (ProjectManager = @ProjectManager OR @ProjectManager='')
		AND (ProjectCName   LIKE @ProjectCName  OR @ProjectCName='%%')
		AND (ProjectStatus   = @ProjectStatus  OR @ProjectStatus='')
		AND a.OrganizationID=@OrganizationID 
	
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_ProjectInfo_DataExistsInAllTable]'
GO
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_DataExistsInAllTable]
@OrganizationID NVARCHAR(50),
@ProjectNumber NVARCHAR(50)
AS 
--DECLARE @DataCountProgramList INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_ProgramList WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountModules INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_Modules WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountCustomers INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_Customers WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountMeetingRecord INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_MeetingRecord WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountMembers INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_Members WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountModWorkTime INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_ModWorkTime WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountPrgWorkTime INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_PrgWorkTime WHERE ProjectNumber=@ProjectNumber
----DECLARE @DataCountModWorkTime INT 
----SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_PrjRule WHERE ProjectNumber=@ProjectNumber
----DECLARE @DataCountModWorkTime INT 
----SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_PrjType WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountPrjWorkTime INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_PrjWorkTime WHERE ProjectNumber=@ProjectNumber
----DECLARE @DataCountModWorkTime INT 
----SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_PrjWorkType WHERE ProjectNumber=@ProjectNumber
--DECLARE @DataCountRequestOrder INT 
--SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_RequestOrder WHERE ProjectNumber=@ProjectNumber
----DECLARE @DataCountRequestOrder INT 
----SELECT @DataCountProgramList=COUNT(0) FROM  dbo.OTB_PRJ_ROPG WHERE ProjectNumber=@ProjectNumber


SELECT COUNT(0)AS DataCount ,'程式基本資料'AS DelData,'ProgramListMaintain_Upd'AS DataUrl FROM  dbo.OTB_PRJ_ProgramList WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_Modules' AS TableName,'模組基本資料'AS PrjName FROM  dbo.OTB_PRJ_Modules WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_Customers' AS TableName,'專案客戶資料'AS PrjName FROM  dbo.OTB_PRJ_Customers WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_MeetingRecord' AS TableName,'會議記錄'AS PrjName FROM  dbo.OTB_PRJ_MeetingRecord WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_Members' AS TableName,'專案人員'AS PrjName FROM  dbo.OTB_PRJ_Members WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_ModWorkTime' AS TableName,'模組基本資料'AS PrjName FROM  dbo.OTB_PRJ_ModWorkTime WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_PrgWorkTime' AS TableName,'程式基本資料'AS PrjName FROM  dbo.OTB_PRJ_PrgWorkTime WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_PrjWorkTime' AS TableName,'專案基本資料'AS PrjName FROM  dbo.OTB_PRJ_PrjWorkTime WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
--UNION ALL
--SELECT COUNT(0)AS DataCount ,'OTB_PRJ_RequestOrder' AS TableName,'專案需求變更單'AS PrjName FROM  dbo.OTB_PRJ_RequestOrder WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OTB_PRJ_PrjWorkType]'
GO
ALTER TABLE [dbo].[OTB_PRJ_PrjWorkType] ADD
[PrjTypeID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjWorkType_Update]'
GO
------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Update]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@PrjWorkTypeEName nvarchar(20),
@PrjWorkTypeCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@IsAutoTask char(1),
@PrjWorkTypeRange nvarchar(20),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
 AS 
	UPDATE [OTB_PRJ_PrjWorkType] SET 
	[PrjWorkTypeEName] = @PrjWorkTypeEName,[PrjWorkTypeCName] = @PrjWorkTypeCName,[ParentID] = @ParentID,[IsAutoTask] = @IsAutoTask,[PrjWorkTypeRange] = @PrjWorkTypeRange,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID
	WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]'
GO
------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36)
 AS 
	SELECT 
	OrganizationID,PrjWorkTypeID,PrjWorkTypeEName,PrjTypeID,PrjWorkTypeCName,ParentID,IsAutoTask,PrjWorkTypeRange,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
	 FROM [OTB_PRJ_PrjWorkType]
	 WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]'
GO


------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：2014/07/25
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]
@OrganizationID varchar(50),
@PrjWorkTypeEName varchar(50),
@PrjWorkTypeCName varchar(50),
@ParentID varchar(36),
@PrjWorkTypeRange varchar(36)
AS 

	  SELECT COUNT(0)  
	 FROM OTB_PRJ_PrjWorkType
	WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName='%%')
		AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName='%%')
		AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='')
		AND (ParentID = @ParentID OR @ParentID='')
		AND OrganizationID=@OrganizationID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OTB_PRJ_PrjType]'
GO
CREATE TABLE [dbo].[OTB_PRJ_PrjType]
(
[OrganizationID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrjTypeID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrjTypeEName] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PrjTypeCName] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[ParentID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Memo] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CreateUser] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CreateDate] [datetime] NULL,
[ModifyUser] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[ModifyDate] [datetime] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_OTB_PRJ_PrjtType] on [dbo].[OTB_PRJ_PrjType]'
GO
ALTER TABLE [dbo].[OTB_PRJ_PrjType] ADD CONSTRAINT [PK_OTB_PRJ_PrjType] PRIMARY KEY CLUSTERED  ([OrganizationID], [PrjTypeID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]'
GO


------------------------------------
--用途：查詢資料信息 
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]

@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjWorkTypeEName varchar(50),
@PrjWorkTypeCName varchar(50),
@ParentID varchar(36),
@PrjWorkTypeRange varchar(36),
@SortExpression nvarchar(500)
 AS 
 
SELECT 
	 RowId
	 ,PrjWorkTypeID,PrjWorkTypeEName,PrjTypeID,PrjWorkTypeCName,ParentID,IsAutoTask,PrjWorkTypeRange
FROM 
(
	SELECT 
		CASE @SortExpression WHEN N'PrjWorkTypeEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName)) 
			WHEN N'PrjWorkTypeEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName DESC)) 	
			WHEN N'PrjWorkTypeCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName)) 	
			WHEN N'PrjWorkTypeCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID DESC))
			WHEN N'PrjTypeID' THEN 
				(ROW_NUMBER() OVER(Order BY PrjTypeID)) 	
			WHEN N'PrjTypeID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeID DESC))
			WHEN N'IsAutoTask' THEN 
				(ROW_NUMBER() OVER(Order BY IsAutoTask)) 	
			WHEN N'IsAutoTask DESC' THEN
				(ROW_NUMBER() OVER(Order BY IsAutoTask DESC))
			WHEN N'PrjWorkTypeRange' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeRange)) 	
			WHEN N'PrjWorkTypeRange DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeRange DESC))
			ELSE
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName)) 		
		END AS RowId
		,PrjWorkTypeID,PrjWorkTypeEName,PrjWorkTypeCName
		,(SELECT PrjWorkTypeCName FROM dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.ParentID) AS ParentID,
		IsAutoTask,PrjWorkTypeRange
		,(SELECT PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE  PrjTypeID=a.PrjTypeID) AS PrjTypeID
	 FROM OTB_PRJ_PrjWorkType AS a
	WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName='%%')
		AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName='%%')
		AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='')
		AND (ParentID = @ParentID OR @ParentID='')
		AND OrganizationID=@OrganizationID
		) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex 
ORDER BY RowId


	SELECT 
		CASE @SortExpression WHEN N'PrjWorkTypeEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName)) 
			WHEN N'PrjWorkTypeEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName DESC)) 	
			WHEN N'PrjWorkTypeCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName)) 	
			WHEN N'PrjWorkTypeCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID DESC))
			WHEN N'PrjTypeID' THEN 
				(ROW_NUMBER() OVER(Order BY PrjTypeID)) 	
			WHEN N'PrjTypeID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeID DESC))
			WHEN N'IsAutoTask' THEN 
				(ROW_NUMBER() OVER(Order BY IsAutoTask)) 	
			WHEN N'IsAutoTask DESC' THEN
				(ROW_NUMBER() OVER(Order BY IsAutoTask DESC))
			WHEN N'PrjWorkTypeRange' THEN 
				(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), PrjWorkTypeRange))) 	
			WHEN N'PrjWorkTypeRange DESC' THEN
				(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), PrjWorkTypeRange) DESC))
			ELSE
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName)) 		
		END AS '項次'
		,PrjWorkTypeEName AS '專案工作類型英文'
		,PrjWorkTypeCName AS '專案工作類型中文'
		,(SELECT PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE  PrjTypeID=a.PrjTypeID) AS '專案類型'
		,(select  PrjWorkTypeCName FROM dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.ParentID) AS '父層編號'
		,IsAutoTask AS '是否自動產生任務'
		,PrjWorkTypeRange AS '任務範圍'
	 FROM OTB_PRJ_PrjWorkType AS a
	WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName='%%')
		AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName='%%')
		AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='')
		AND (ParentID = @ParentID OR @ParentID='')
		AND OrganizationID=@OrganizationID
		ORDER BY '項次'

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]'
GO
------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@PrjWorkTypeEName nvarchar(20),
@PrjWorkTypeCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@IsAutoTask char(1),
@PrjWorkTypeRange nvarchar(20),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime

 AS 
	INSERT INTO [OTB_PRJ_PrjWorkType](
	[OrganizationID],[PrjWorkTypeID],[PrjWorkTypeEName],[PrjWorkTypeCName],[ParentID],[IsAutoTask],[PrjWorkTypeRange],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID
	)VALUES(
	@OrganizationID,NEWID(),@PrjWorkTypeEName,@PrjWorkTypeCName,@ParentID,@IsAutoTask,@PrjWorkTypeRange,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),@PrjTypeID
	)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_Update]'
GO
------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Update]
@OrganizationID varchar(50),
@PrjTypeID char(36),
@PrjTypeEName nvarchar(20),
@PrjTypeCName nvarchar(20),
@ParentID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
 AS 
	UPDATE [OTB_PRJ_PrjType] SET 
	[PrjTypeEName] = @PrjTypeEName,[PrjTypeCName] = @PrjTypeCName,[ParentID] = @ParentID,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
	WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_GetModel]'
GO
------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:01
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetModel]
@OrganizationID varchar(50),
@PrjTypeID char(36)
 AS 
	SELECT 
	OrganizationID,PrjTypeID,PrjTypeEName,PrjTypeCName,ParentID,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
	 FROM [OTB_PRJ_PrjType]
	 WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_GetListCount]'
GO


------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetListCount]
@OrganizationID varchar(50),
@PrjTypeEName varchar(50),
@PrjTypeCName varchar(50),
@ParentID varchar(36)
AS 

	  SELECT COUNT(0)  
	 FROM OTB_PRJ_PrjType
	WHERE (ParentID = @ParentID OR @ParentID='')
	   AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName='%%')
		AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName='%%')
		AND OrganizationID=@OrganizationID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_GetList]'
GO


------------------------------------
--用途：查詢資料信息 
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetList]

@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjTypeEName varchar(50),
@PrjTypeCName varchar(50),
@ParentID varchar(36),
@SortExpression nvarchar(500)
 AS 
 
SELECT 
	 RowId
	 ,PrjTypeID,PrjTypeEName,PrjTypeCName,ParentID,Memo
FROM 
(
	SELECT 
		CASE @SortExpression WHEN N'PrjTypeEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeEName)) 
			WHEN N'PrjTypeEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeEName DESC)) 	
			WHEN N'PrjTypeCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjTypeCName)) 	
			WHEN N'PrjTypeCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID 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 PrjTypeEName)) 		
		END AS RowId
		,PrjTypeID,PrjTypeEName,PrjTypeCName,(select  PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.ParentID) AS ParentID,Memo
	 FROM OTB_PRJ_PrjType AS a
	WHERE (ParentID = @ParentID OR @ParentID='')
	   AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName='%%')
		AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName='%%')
		AND OrganizationID=@OrganizationID
		) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex 
ORDER BY RowId


	SELECT 
		CASE @SortExpression WHEN N'PrjTypeEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeEName)) 
			WHEN N'PrjTypeEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeEName DESC)) 	
			WHEN N'PrjTypeCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjTypeCName)) 	
			WHEN N'PrjTypeCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjTypeCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID 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 PrjTypeEName)) 		
		END AS '項次'
		,PrjTypeEName AS '專案類型英文'
		,PrjTypeCName AS '專案類型中文'
		,(select  PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.ParentID) AS '父層編號'
		,Memo AS '備註'
	 FROM OTB_PRJ_PrjType AS a
	WHERE (ParentID = @ParentID OR @ParentID='')
	   AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName='%%')
		AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName='%%')
		AND OrganizationID=@OrganizationID
		ORDER BY '項次'

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_Exists]'
GO
------------------------------------
--用途：是否已经存在 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Exists]
@OrganizationID varchar(50),
@PrjTypeID char(36)
AS
	DECLARE @TempID int
	SELECT @TempID = count(1) FROM [OTB_PRJ_PrjType] WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID 
	IF @TempID = 0
		RETURN 0
	ELSE
		RETURN 1

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_Delete]'
GO
------------------------------------
--用途：删除一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Delete]
@OrganizationID varchar(50),
@PrjTypeID char(36)
 AS 
	DELETE [OTB_PRJ_PrjType]
	 WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjType_ADD]'
GO


------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_ADD]
@OrganizationID varchar(50),
@PrjTypeID char(36),
@PrjTypeEName nvarchar(20),
@PrjTypeCName nvarchar(20),
@ParentID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime

 AS 
	INSERT INTO [OTB_PRJ_PrjType](
	[OrganizationID],[PrjTypeID],[PrjTypeEName],[PrjTypeCName],[ParentID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
	)VALUES(
	@OrganizationID,NEWID(),@PrjTypeEName,@PrjTypeCName,@ParentID,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE()
	)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OTB_PRJ_PrjRuleWorkType]'
GO
CREATE TABLE [dbo].[OTB_PRJ_PrjRuleWorkType]
(
[OrganizationID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrjRuleID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrjWorkTypeID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_OTB_PRJ_PrjRuleWorkType] on [dbo].[OTB_PRJ_PrjRuleWorkType]'
GO
ALTER TABLE [dbo].[OTB_PRJ_PrjRuleWorkType] ADD CONSTRAINT [PK_OTB_PRJ_PrjRuleWorkType] PRIMARY KEY CLUSTERED  ([OrganizationID], [PrjRuleID], [PrjWorkTypeID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]'
GO
------------------------------------
--用途：查询记录信息 
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]
@OrganizationID varchar(50),
@PrjRuleID char(36)
 AS 
	SELECT 
	OrganizationID,PrjRuleID,PrjWorkTypeID,(SELECT  PrjWorkTypeCName FROM  dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.PrjWorkTypeID) AS PrjRuleWorkType
	 FROM [OTB_PRJ_PrjRuleWorkType] AS a WHERE OrganizationID=@OrganizationID AND PrjRuleID=@PrjRuleID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]'
GO
------------------------------------
--用途：删除一条记录 
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]
@OrganizationID varchar(50),
@PrjRuleID char(36)
 AS 
	DELETE [OTB_PRJ_PrjRuleWorkType]
	 WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]'
GO
------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjWorkTypeID char(36)

 AS 
	INSERT INTO [OTB_PRJ_PrjRuleWorkType](
	[OrganizationID],[PrjRuleID],[PrjWorkTypeID]
	)VALUES(
	@OrganizationID,@PrjRuleID,@PrjWorkTypeID
	)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_RequestOrder_GetListByID]'
GO
------------------------------------
--功能：查詢記錄信息
--項目名稱：MidWeb
--Create：John
--時間：2014/07/23
------------------------------------
--EXEC OSP_OTB_CRM_Customers_GetListByID 1,10,'', '', '', '','','origtek',''
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_RequestOrder_GetListByID]
@StartRecordIndex int,
@EndRecordIndex int,
@ECOrderNumber varchar(12),
@OrderType Char(2),
@OrderTitle nvarchar(200),
@Undertaker nvarchar(50),
@OrderStatus VarChar(2),
@SortExpression nvarchar(500),
@OrganizationID VarChar(50),
@ProjectNumber VarChar(50)
 AS 
   SELECT   
        RowId
        ,ECOrderNumber
        ,ProjectNumber
        ,OrderType
        ,OrderTitle
        ,Undertaker
        ,OrderStatus
        ,PStartDate
        ,PDeadLine  
              FROM   
              (  
              SELECT   
              CASE @SortExpression WHEN N'ECOrderNumber' THEN  
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber))   
              	WHEN N'ECOrderNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber DESC)) 	  
              	WHEN N'OrderType' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderType)) 	  
              	WHEN N'OrderType DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderType DESC))   
              	WHEN N'ProjectNumber' THEN   
              		(ROW_NUMBER() OVER(Order BY ProjectNumber)) 	  
              	WHEN N'ProjectNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber DESC))  
              	WHEN N'OrderTitle' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderTitle)) 	  
              	WHEN N'OrderTitle DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderTitle DESC))   
              	WHEN N'Undertaker' THEN   
              		(ROW_NUMBER() OVER(Order BY Undertaker)) 	  
              	WHEN N'Undertaker DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY Undertaker DESC))	  
              	WHEN N'OrderStatus' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderStatus)) 	  
              	WHEN N'OrderStatus DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderStatus DESC))
              	WHEN N'PStartDate' THEN   
              		(ROW_NUMBER() OVER(Order BY PStartDate)) 	  
              	WHEN N'PStartDate DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PStartDate DESC))	  
              	WHEN N'PDeadLine' THEN   
              		(ROW_NUMBER() OVER(Order BY PDeadLine)) 	  
              	WHEN N'PDeadLine DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PDeadLine DESC))  
              	else   
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber))  
              END AS RowId
              ,ECOrderNumber
              ,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS ProjectNumber
              ,dbo.OFN_AVA_ArgumentValueByArgumentID(OrderType,'OrderType') AS OrderType
              ,OrderTitle
              ,dbo.OFN_AVA_MemberNameByMemberID(Undertaker) AS Undertaker
              ,dbo.OFN_AVA_ArgumentValueByArgumentID(OrderStatus,'OrderSts') AS OrderStatus
              ,CONVERT(varchar(100), PStartDate, 111) AS PStartDate
              ,CONVERT(varchar(100), PDeadLine, 111)  AS PDeadLine  
               FROM OTB_PRJ_RequestOrder  
               WHERE (ECOrderNumber LIKE @ECOrderNumber OR @ECOrderNumber IS NULL OR @ECOrderNumber='%%')
                  AND (OrderType = @OrderType OR @OrderType IS NULL OR @OrderType='')    
              	AND (OrderTitle LIKE @OrderTitle OR @OrderTitle IS NULL OR @OrderTitle='%%')  
                  AND (Undertaker = @Undertaker OR @Undertaker IS NULL OR @Undertaker='')  
                  AND (OrderStatus = @OrderStatus OR @OrderStatus IS NULL OR @OrderStatus='') 
                  AND (OrganizationID = @OrganizationID OR @OrganizationID IS NULL OR @OrganizationID='')
                  AND (ProjectNumber = @ProjectNumber OR @ProjectNumber IS NULL OR @ProjectNumber='')
              ) AS AA  
              WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex   
              ORDER BY RowId
              
              SELECT   
              CASE @SortExpression WHEN N'ECOrderNumber' THEN  
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber))   
              	WHEN N'ECOrderNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber DESC)) 	  
              	WHEN N'OrderType' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderType)) 	  
              	WHEN N'OrderType DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderType DESC))  
              	WHEN N'ProjectNumber' THEN   
              		(ROW_NUMBER() OVER(Order BY ProjectNumber)) 	  
              	WHEN N'ProjectNumber DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY ProjectNumber DESC))  
              	WHEN N'OrderTitle' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderTitle)) 	  
              	WHEN N'OrderTitle DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderTitle DESC))   
              	WHEN N'Undertaker' THEN   
              		(ROW_NUMBER() OVER(Order BY Undertaker)) 	  
              	WHEN N'Undertaker DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY Undertaker DESC))	  
              	WHEN N'OrderStatus' THEN   
              		(ROW_NUMBER() OVER(Order BY OrderStatus)) 	  
              	WHEN N'OrderStatus DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY OrderStatus DESC))
              	WHEN N'PStartDate' THEN   
              		(ROW_NUMBER() OVER(Order BY PStartDate)) 	  
              	WHEN N'PStartDate DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PStartDate DESC))	  
              	WHEN N'PDeadLine' THEN   
              		(ROW_NUMBER() OVER(Order BY PDeadLine)) 	  
              	WHEN N'PDeadLine DESC' THEN  
              		(ROW_NUMBER() OVER(Order BY PDeadLine DESC))  
              	else   
              		(ROW_NUMBER() OVER(Order BY ECOrderNumber))  
              END AS '項次' 
              ,ECOrderNumber AS '需求變更單單號'
              ,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS '專案名稱'
              ,dbo.OFN_AVA_ArgumentValueByArgumentID(OrderType,'OrderType') AS '變更單類別'
              ,OrderTitle AS '變更單主旨'
              ,dbo.OFN_AVA_MemberNameByMemberID(Undertaker) AS '負責工程師'
              ,CONVERT(varchar(100), PStartDate, 111) AS '預估開始日期'
              ,CONVERT(varchar(100), PDeadLine, 111)  AS '預估完成日期'  
              ,dbo.OFN_AVA_ArgumentValueByArgumentID(OrderStatus,'OrderSts') AS '變更單狀態'
               FROM OTB_PRJ_RequestOrder  
               WHERE (ECOrderNumber LIKE @ECOrderNumber OR @ECOrderNumber IS NULL OR @ECOrderNumber='%%')
                  AND (OrderType = @OrderType OR @OrderType IS NULL OR @OrderType='')    
              	AND (OrderTitle LIKE @OrderTitle OR @OrderTitle IS NULL OR @OrderTitle='%%')  
                  AND (Undertaker = @Undertaker OR @Undertaker IS NULL OR @Undertaker='')  
                  AND (OrderStatus = @OrderStatus OR @OrderStatus IS NULL OR @OrderStatus='') 
                  AND (OrganizationID = @OrganizationID OR @OrganizationID IS NULL OR @OrganizationID='') 
                  AND (ProjectNumber = @ProjectNumber OR @ProjectNumber IS NULL OR @ProjectNumber='')
                  ORDER BY '項次'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OTB_PRJ_PrjRule]'
GO
ALTER TABLE [dbo].[OTB_PRJ_PrjRule] ADD
[PrjTypeID] [char] (36) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjRule_Update]'
GO
------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Update]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjRuleEName nvarchar(20),
@PrjRuleCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
 AS 
	UPDATE [OTB_PRJ_PrjRule] SET 
	[PrjRuleEName] = @PrjRuleEName,[PrjRuleCName] = @PrjRuleCName,[ParentID] = @ParentID,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID
	WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjRule_GetModel]'
GO
------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetModel]
@OrganizationID varchar(50),
@PrjRuleID char(36)
 AS 
	SELECT 
	OrganizationID,PrjRuleID,PrjTypeID,PrjRuleEName,PrjRuleCName,ParentID,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
	 FROM [OTB_PRJ_PrjRule]
	 WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjRule_GetList]'
GO


------------------------------------
--用途：查詢資料信息 
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetList]

@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjRuleEName varchar(50),
@PrjRuleCName varchar(50),
@ParentID varchar(36),
@SortExpression nvarchar(500)
 AS 
 
SELECT 
	 RowId
	 ,PrjRuleID,PrjRuleEName,PrjRuleCName,ParentID,Memo
FROM 
(
	SELECT 
		CASE @SortExpression WHEN N'PrjRuleEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleEName)) 
			WHEN N'PrjRuleEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleEName DESC)) 	
			WHEN N'PrjRuleCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjRuleCName)) 	
			WHEN N'PrjRuleCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID 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 PrjRuleEName)) 		
		END AS RowId
		,PrjRuleID,PrjRuleEName,PrjRuleCName,(SELECT PrjRuleCName FROM dbo.OTB_PRJ_PrjRule WHERE PrjRuleID=a.ParentID) AS ParentID,Memo
	 FROM OTB_PRJ_PrjRule AS a
	WHERE (ParentID = @ParentID OR @ParentID='')
	   AND (PrjRuleEName LIKE @PrjRuleEName OR @PrjRuleEName='%%')
		AND (PrjRuleCName LIKE @PrjRuleCName OR @PrjRuleCName='%%')
		AND OrganizationID=@OrganizationID
		) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex 
ORDER BY RowId


	SELECT 
		CASE @SortExpression WHEN N'PrjRuleEName' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleEName)) 
			WHEN N'PrjRuleEName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleEName DESC)) 	
			WHEN N'PrjRuleCName' THEN 
				(ROW_NUMBER() OVER(Order BY PrjRuleCName)) 	
			WHEN N'PrjRuleCName DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjRuleCName DESC))
			WHEN N'ParentID' THEN 
				(ROW_NUMBER() OVER(Order BY ParentID)) 	
			WHEN N'ParentID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ParentID 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 PrjRuleEName)) 		
		END AS '項次'
		,PrjRuleEName AS '專案角色英文'
		,PrjRuleCName AS '專案角色中文'
		,(SELECT PrjRuleCName FROM dbo.OTB_PRJ_PrjRule WHERE PrjRuleID=a.ParentID) AS '父層編號'
		,Memo AS '備註'
	 FROM OTB_PRJ_PrjRule AS a
	WHERE (ParentID = @ParentID OR @ParentID='')
	   AND (PrjRuleEName LIKE @PrjRuleEName OR @PrjRuleEName='%%')
		AND (PrjRuleCName LIKE @PrjRuleCName OR @PrjRuleCName='%%')
		AND OrganizationID=@OrganizationID
		ORDER BY '項次'

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[OSP_OTB_PRJ_PrjRule_ADD]'
GO
------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
ALTER PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_ADD]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjRuleEName nvarchar(20),
@PrjRuleCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime

 AS 
	INSERT INTO [OTB_PRJ_PrjRule](
	[OrganizationID],[PrjRuleID],[PrjRuleEName],[PrjRuleCName],[ParentID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID
	)VALUES(
	@OrganizationID,NEWID(),@PrjRuleEName,@PrjRuleCName,@ParentID,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),@PrjTypeID
	)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OTB_PRJ_Task]'
GO
CREATE TABLE [dbo].[OTB_PRJ_Task]
(
[OrganizationID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrgTaskID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[PrjWorkTypeID] [varchar] (max) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[ProjectNumber] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[ModuleID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[ProgramID] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PrgWorkType] [varchar] (200) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[Title] [nvarchar] (100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Note] [nvarchar] (max) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Memo] [ntext] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CreateUser] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CreateDate] [datetime] NULL,
[ModifyUser] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[ModifyDate] [datetime] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_OTB_PRJ_Task] on [dbo].[OTB_PRJ_Task]'
GO
ALTER TABLE [dbo].[OTB_PRJ_Task] ADD CONSTRAINT [PK_OTB_PRJ_Task] PRIMARY KEY CLUSTERED  ([OrganizationID], [PrgTaskID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_Exists]'
GO
------------------------------------
--用途：是否已经存在 
--项目名称：
--说明：
--时间：2014/9/30 上午 11:40:16
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_Exists]
@OrganizationID varchar(50),
@PrgTaskID varchar(50)
AS
	DECLARE @TempID int
	SELECT @TempID = count(1) FROM [OTB_PRJ_Task] WHERE OrganizationID=@OrganizationID and PrgTaskID=@PrgTaskID 
	IF @TempID = 0
		RETURN 0
	ELSE
		RETURN 1

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_ADD]'
GO
------------------------------------
--用途：增加一条记录 
--项目名称：
--说明：
--时间：2014/9/30 上午 11:40:16
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_ADD]
@OrganizationID varchar(50),
@PrgTaskID varchar(50),
@PrjWorkTypeID varchar(MAX),
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramID varchar(50),
@PrgWorkType varchar(200),
@Title nvarchar(100),
@Note nvarchar(MAX),
@Memo ntext,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime

 AS 
	INSERT INTO [OTB_PRJ_Task](
	[OrganizationID],[PrgTaskID],[PrjWorkTypeID],[ProjectNumber],[ModuleID],[ProgramID],[PrgWorkType],[Title],[Note],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
	)VALUES(
	@OrganizationID,NEWID(),@PrjWorkTypeID,@ProjectNumber,@ModuleID,@ProgramID,@PrgWorkType,@Title,@Note,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE()
	)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_Update]'
GO
------------------------------------
--用途：修改一条记录 
--项目名称：
--说明：
--时间：2014/9/30 上午 11:40:16
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_Update]
@OrganizationID varchar(50),
@PrgTaskID varchar(50),
@PrjWorkTypeID varchar(MAX),
@ProjectNumber varchar(50),
@ModuleID varchar(50),
@ProgramID varchar(50),
@PrgWorkType varchar(200),
@Title nvarchar(100),
@Note nvarchar(MAX),
@Memo ntext,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
 AS 
	UPDATE [OTB_PRJ_Task] SET 
	[PrjWorkTypeID] = @PrjWorkTypeID,[ProjectNumber] = @ProjectNumber,[ModuleID] = @ModuleID,[ProgramID] = @ProgramID,[PrgWorkType] = @PrgWorkType,[Title] = @Title,[Note] = @Note,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
	WHERE OrganizationID=@OrganizationID and PrgTaskID=@PrgTaskID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_Delete]'
GO
------------------------------------
--用途：删除一条记录 
--项目名称：
--说明：
--时间：2014/9/30 上午 11:40:16
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_Delete]
@OrganizationID varchar(50),
@PrgTaskID varchar(50)
 AS 
	DELETE [OTB_PRJ_Task]
	 WHERE OrganizationID=@OrganizationID and PrgTaskID=@PrgTaskID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_GetModel]'
GO
------------------------------------
--用途：得到实体对象的详细信息 
--项目名称：
--说明：
--时间：2014/9/30 上午 11:40:16
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_GetModel]
@OrganizationID varchar(50),
@PrgTaskID varchar(50)
 AS 
	SELECT 
	OrganizationID,PrgTaskID,PrjWorkTypeID,ProjectNumber,ModuleID,ProgramID,PrgWorkType,Title,Note,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
	 FROM [OTB_PRJ_Task]
	 WHERE OrganizationID=@OrganizationID and PrgTaskID=@PrgTaskID 

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID]'
GO


--根據專案模組編號獲取模組名稱
CREATE FUNCTION [dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID]
(
 @PrjWorkTypeID varchar(50) --傳入的字符串
)
RETURNS   NVARCHAR(4000)
AS
BEGIN 
	 DECLARE @Value nvarchar(max);
	 SET @Value=''
	 SELECT @Value =  PrjWorkTypeCName
	 FROM OTB_PRJ_PrjWorkType
	 WHERE  PrjWorkTypeID=@PrjWorkTypeID
  --	 IF @Value=''
		--SET @Value=''
	 --ELSE 			
		--SET @Value=@Value
   RETURN @Value
   END


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_GetList]'
GO



------------------------------------
--用途：查詢資料信息 
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Task_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjWorkTypeID varchar(50),
@PrgWorkType varchar(50),
@SortExpression nvarchar(500),
@ProjectNumber varchar(36),
@ModuleID varchar(36),
@ProgramID varchar(36),
@Title varchar(36)

 AS 
 
SELECT 
	 RowId
	 ,PrgTaskID,PrjWorkTypeID,ProjectNumber,ModuleID,ProgramID,PrgWorkType,Title,Note
FROM 
(
	SELECT 
		CASE @SortExpression WHEN N'PrgTaskID' THEN
				(ROW_NUMBER() OVER(Order BY PrgTaskID)) 
			WHEN N'PrgTaskID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrgTaskID DESC)) 	
			WHEN N'PrjWorkTypeID' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeID)) 	
			WHEN N'PrjWorkTypeID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeID DESC))
			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'ProgramID' THEN 
				(ROW_NUMBER() OVER(Order BY ProgramID)) 	
			WHEN N'ProgramID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProgramID DESC))
			WHEN N'PrgWorkType' THEN 
				(ROW_NUMBER() OVER(Order BY PrgWorkType)) 	
			WHEN N'PrgWorkType DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrgWorkType DESC))
			WHEN N'Title' THEN 
				(ROW_NUMBER() OVER(Order BY Title)) 	
			WHEN N'Title DESC' THEN
				(ROW_NUMBER() OVER(Order BY Title DESC))
			WHEN N'Note' THEN 
				(ROW_NUMBER() OVER(Order BY Note)) 	
			WHEN N'Note DESC' THEN
				(ROW_NUMBER() OVER(Order BY Note DESC))
			ELSE
				(ROW_NUMBER() OVER(Order BY Title)) 		
		END AS RowId
		,PrgTaskID
		,PrjWorkTypeID
		,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS ProjectNumber
		,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS ModuleID
		,dbo.OFN_AVA_ProgramNameByProgramID(ProgramID) AS ProgramID
		,dbo.OFN_AVA_ArgumentValueByArgumentID(PrgWorkType,'PrgWorkTyp') AS PrgWorkType
		,Title
		,Note
	 FROM [OTB_PRJ_Task] AS a
	WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
	    AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
		AND (dbo.OFN_AVA_ProgramNameByProgramID(ProgramID) LIKE @ProgramID OR @ProgramID='%%')
		AND (dbo.OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID(PrjWorkTypeID) LIKE @PrjWorkTypeID OR @PrjWorkTypeID='%%')
		AND (Title LIKE @Title OR @Title='%%')
		AND (PrgWorkType = @PrgWorkType OR @PrgWorkType='')
		AND OrganizationID=@OrganizationID
		) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex 
ORDER BY RowId


	SELECT 
		CASE @SortExpression WHEN N'PrgTaskID' THEN
				(ROW_NUMBER() OVER(Order BY PrgTaskID)) 
			WHEN N'PrgTaskID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrgTaskID DESC)) 	
			WHEN N'PrjWorkTypeID' THEN 
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeID)) 	
			WHEN N'PrjWorkTypeID DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrjWorkTypeID DESC))
			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'ProgramID' THEN 
				(ROW_NUMBER() OVER(Order BY ProgramID)) 	
			WHEN N'ProgramID DESC' THEN
				(ROW_NUMBER() OVER(Order BY ProgramID DESC))
			WHEN N'PrgWorkType' THEN 
				(ROW_NUMBER() OVER(Order BY PrgWorkType)) 	
			WHEN N'PrgWorkType DESC' THEN
				(ROW_NUMBER() OVER(Order BY PrgWorkType DESC))
			WHEN N'Title' THEN 
				(ROW_NUMBER() OVER(Order BY Title)) 	
			WHEN N'Title DESC' THEN
				(ROW_NUMBER() OVER(Order BY Title DESC))
			WHEN N'Note' THEN 
				(ROW_NUMBER() OVER(Order BY Note)) 	
			WHEN N'Note DESC' THEN
				(ROW_NUMBER() OVER(Order BY Note DESC))
			ELSE
				(ROW_NUMBER() OVER(Order BY Title)) 		
		END AS '項次'
		,Title AS '任務標題'
		,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS '專案名稱'
		,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS '模組名稱'
		,dbo.OFN_AVA_ProgramNameByProgramID(ProgramID) AS '程式名稱'
		,PrjWorkTypeID AS '專案工作項目'
		,dbo.OFN_AVA_ArgumentValueByArgumentID(PrgWorkType,'PrgWorkTyp') AS '程式工作項目'
		,Note AS '任務描述'
	 FROM [OTB_PRJ_Task] AS a
	WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
	    AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
		AND (dbo.OFN_AVA_ProgramNameByProgramID(ProgramID) LIKE @ProgramID OR @ProgramID='%%')
		AND (dbo.OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID(PrjWorkTypeID) LIKE @PrjWorkTypeID OR @PrjWorkTypeID='%%')
		AND (Title LIKE @Title OR @Title='%%')
		AND (PrgWorkType = @PrgWorkType OR @PrgWorkType='')
		AND OrganizationID=@OrganizationID
		ORDER BY '項次'


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_Task_GetListCount]'
GO

--獲得程式總數
--項目名稱：OPMSDB
--Create：Alina
--時間：2014/08/02
CREATE PROC [dbo].[OSP_OTB_PRJ_Task_GetListCount]
@OrganizationID varchar(50),
@PrjWorkTypeID varchar(50),
@PrgWorkType varchar(50),
@ProjectNumber varchar(36),
@ModuleID varchar(36),
@ProgramID varchar(36),
@Title varchar(36)
AS
	SELECT COUNT(0)
	 FROM [OTB_PRJ_Task] AS a
	WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
	    AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
		AND (dbo.OFN_AVA_ProgramNameByProgramID(ProgramID) LIKE @ProgramID OR @ProgramID='%%')
		AND (dbo.OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID(PrjWorkTypeID) LIKE @PrjWorkTypeID OR @PrjWorkTypeID='%%')
		AND (Title LIKE @Title OR @Title='%%')
		AND (PrgWorkType = @PrgWorkType OR @PrgWorkType='')
		AND OrganizationID=@OrganizationID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_Common_GetProgramNameListByModuleID]'
GO



------------------------------------
--用途：查詢資料信息 
--項目名稱：內部管理系統
--Create：John
--時間：2014/09/30
-- exec OSP_Common_GetProgramListByRuleId 'admin','AA'
------------------------------------
CREATE PROCEDURE [dbo].[OSP_Common_GetProgramNameListByModuleID]
@ModuleID VARCHAR(50)
,@OrganizationID VARCHAR(50)
 AS 
SELECT  ProgramID,ProgramName FROM OTB_PRJ_ProgramList WHERE ModuleID=@ModuleID AND OrganizationID=@OrganizationID ORDER BY ProgramName



GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[OSP_OTB_PRJ_ProjectInfo_GetWorkTypeByPrjtType]'
GO
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetWorkTypeByPrjtType]
@PrjtTypeID NVARCHAR(50),
@OrganizationID VARCHAR(50)
AS 
SELECT PrjWorkTypeID,PrjWorkTypeEName FROM dbo.OTB_PRJ_PrjWorkType WHERE OrganizationID=@OrganizationID AND PrjTypeID=@PrjtTypeID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO