You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1636 lines
59 KiB
1636 lines
59 KiB
|
|
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
|