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.
974 lines
65 KiB
974 lines
65 KiB
|
|
|
|
alter table OTB_PRJ_ProgramList add PrjTypeID varchar(50) null DEFAULT '', PrjWorkTypeID varchar(max) null DEFAULT ''
|
|
alter table OTB_PRJ_ProjectInfo add PrjTypeID varchar(50) null DEFAULT '', PrjWorkTypeID varchar(max) null DEFAULT ''
|
|
alter table OTB_PRJ_Modules add PrjTypeID varchar(50) null DEFAULT '', PrjWorkTypeID varchar(max) null DEFAULT ''
|
|
alter table OTB_PRJ_Members add ProjectWorkType varchar(max) null DEFAULT ''
|
|
alter table OTB_PRJ_Members ALTER COLUMN ProjectRule varchar(max)
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProgramList_GetList] Script Date: 10/10/2014 16:29:05 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProgramList_GetList]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetList]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--功能：查詢記錄信息
|
|
--項目名稱：MidWeb
|
|
--Create：John
|
|
--時間：2014/07/23
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetList]
|
|
@StartRecordIndex int,
|
|
@EndRecordIndex int,
|
|
@ProjectNumber varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ProgramName varchar(50),
|
|
@ProgramType varchar(20),
|
|
@OrganizationID varchar(50),
|
|
@SortExpression nvarchar(500)
|
|
AS
|
|
SELECT
|
|
RowId
|
|
,OrganizationID
|
|
,ProgramID
|
|
,ProjectNumber
|
|
,ModuleID
|
|
,ProgramCode
|
|
,ProgramName
|
|
,ProgramType
|
|
,ProgramVersion
|
|
,PrgAvgProgress
|
|
,PrgWorkCount
|
|
,Memo
|
|
,ProjectValue
|
|
FROM
|
|
(
|
|
SELECT
|
|
CASE @SortExpression WHEN N'ProjectNumber' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber))
|
|
WHEN N'ProjectNumber DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber DESC))
|
|
WHEN N'ModuleID' THEN
|
|
(ROW_NUMBER() OVER(Order BY ModuleID))
|
|
WHEN N'ModuleID DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ModuleID DESC))
|
|
WHEN N'ProgramName' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramName))
|
|
WHEN N'ProgramName DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramName DESC))
|
|
WHEN N'ProgramType' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramType))
|
|
WHEN N'ProgramType DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramType DESC))
|
|
WHEN N'ProgramVersion' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramVersion))
|
|
WHEN N'ProgramVersion DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramVersion DESC))
|
|
WHEN N'PrgAvgProgress' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgAvgProgress))
|
|
WHEN N'PrgAvgProgress DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgAvgProgress DESC))
|
|
WHEN N'PrgWorkCount' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgWorkCount))
|
|
WHEN N'PrgWorkCount DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgWorkCount DESC))
|
|
WHEN N'Memo' THEN
|
|
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
|
|
WHEN N'Memo DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
|
|
else
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber))
|
|
END AS RowId
|
|
,OrganizationID
|
|
,ProgramID
|
|
,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS ProjectNumber
|
|
,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS ModuleID
|
|
,ProgramCode
|
|
,ProgramName
|
|
,dbo.OFN_AVA_ArgumentValueByArgumentID(ProgramType,'ProType') AS ProgramType
|
|
,ProgramVersion
|
|
,ISNULL(PrgAvgProgress,0) AS PrgAvgProgress
|
|
,PrgWorkCount
|
|
,Memo
|
|
,ProjectNumber AS ProjectValue
|
|
FROM OTB_PRJ_ProgramList
|
|
WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
|
|
AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
|
|
AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')
|
|
AND (ProgramType= @ProgramType OR @ProgramType='')
|
|
AND (OrganizationID = @OrganizationID OR @OrganizationID='')
|
|
) AS AA
|
|
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
|
|
ORDER BY RowId
|
|
|
|
SELECT
|
|
CASE @SortExpression WHEN N'ProjectNumber' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber))
|
|
WHEN N'ProjectNumber DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber DESC))
|
|
WHEN N'ModuleID' THEN
|
|
(ROW_NUMBER() OVER(Order BY ModuleID))
|
|
WHEN N'ModuleID DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ModuleID DESC))
|
|
WHEN N'ProgramName' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramName))
|
|
WHEN N'ProgramName DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramName DESC))
|
|
WHEN N'ProgramType' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramType))
|
|
WHEN N'ProgramType DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramType DESC))
|
|
WHEN N'ProgramVersion' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramVersion))
|
|
WHEN N'ProgramVersion DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY ProgramVersion DESC))
|
|
WHEN N'PrgAvgProgress' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgAvgProgress))
|
|
WHEN N'PrgAvgProgress DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgAvgProgress DESC))
|
|
WHEN N'PrgWorkCount' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgWorkCount))
|
|
WHEN N'PrgWorkCount DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY PrgWorkCount DESC))
|
|
WHEN N'Memo' THEN
|
|
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
|
|
WHEN N'Memo DESC' THEN
|
|
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
|
|
else
|
|
(ROW_NUMBER() OVER(Order BY ProjectNumber))
|
|
END AS '項次'
|
|
,dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) AS '專案編號'
|
|
,dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS '模組編號'
|
|
,ProgramName AS '程式名稱'
|
|
,ProgramType AS '程式類別'
|
|
,ProgramVersion AS '程式版本'
|
|
,ProgramDescription AS '程式說明'
|
|
,CONVERT(nvarchar(2000), ISNULL(PrgAvgProgress,0))+'%'AS '平均程式開發進度'
|
|
,PrgWorkCount AS '工作數量'
|
|
,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMinWorkTimeID),'PrgWorkTyp') AS '程式排班ID最小'
|
|
,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMaxWorkTimeID),'PrgWorkTyp') AS '程式排班ID最大'
|
|
,Memo AS '備註'
|
|
FROM OTB_PRJ_ProgramList
|
|
WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
|
|
AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
|
|
AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')
|
|
AND (ProgramType= @ProgramType OR @ProgramType='')
|
|
AND (OrganizationID = @OrganizationID OR @OrganizationID='')
|
|
ORDER BY '項次'
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID] Script Date: 10/09/2014 14:26:09 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]
|
|
GO
|
|
|
|
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
CREATE FUNCTION [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID]
|
|
(
|
|
@PrjTypeID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''
|
|
SELECT @Value = PrjTypeCName
|
|
FROM OTB_PRJ_PrjType
|
|
WHERE PrjTypeID=@PrjTypeID
|
|
-- IF @Value=''
|
|
--SET @Value=''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProgramList_GetListCount] Script Date: 10/10/2014 16:31:59 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProgramList_GetListCount]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetListCount]
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
------------------------------------
|
|
--功能：查詢資料總筆數
|
|
--項目名稱：Web_CRM
|
|
--Create：John
|
|
--時間：2014/07/25
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetListCount]
|
|
@ProjectNumber varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ProgramName varchar(50),
|
|
@OrganizationID varchar(50),
|
|
@ProgramType varchar(20)
|
|
|
|
AS
|
|
|
|
SELECT COUNT(0)
|
|
FROM OTB_PRJ_ProgramList
|
|
WHERE (dbo.OFN_AVA_ProjectNameByProjectNumber(ProjectNumber) LIKE @ProjectNumber OR @ProjectNumber='%%')
|
|
AND (dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) LIKE @ModuleID OR @ModuleID='%%')
|
|
AND (ProgramName LIKE @ProgramName OR @ProgramName='%%')
|
|
AND (ProgramType= @ProgramType OR @ProgramType='')
|
|
AND (OrganizationID = @OrganizationID OR @OrganizationID='')
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProgramList_ADD] Script Date: 10/09/2014 13:59:34 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProgramList_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_ADD]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：增加一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/29 上午 09:28:52
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_ADD]
|
|
@OrganizationID varchar(50),
|
|
@ProgramID varchar(50),
|
|
@ProjectNumber varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ProgramCode varchar(50),
|
|
@ProgramName nvarchar(500),
|
|
@ProgramDescription nvarchar(500),
|
|
@ProgramType varchar(20),
|
|
@ProgramVersion varchar(10),
|
|
@PrgAvgProgress int,
|
|
@PrgWorkCount int,
|
|
@PrgMinWorkTimeID varchar(50),
|
|
@PrgMaxWorkTimeID varchar(50),
|
|
@Memo text,
|
|
@CreateUser varchar(50),
|
|
@CreateDate datetime,
|
|
@ModifyUser varchar(50),
|
|
@ModifyDate datetime,
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
|
|
AS
|
|
INSERT INTO [OTB_PRJ_ProgramList](
|
|
[OrganizationID],[ProgramID],[ProjectNumber],[ModuleID],[ProgramCode],[ProgramName],[ProgramDescription],[ProgramType],[ProgramVersion],[PrgAvgProgress],[PrgWorkCount],[PrgMinWorkTimeID],[PrgMaxWorkTimeID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID,PrjWorkTypeID
|
|
)VALUES(
|
|
@OrganizationID,@ProgramID,@ProjectNumber,@ModuleID,@ProgramCode,@ProgramName,@ProgramDescription,@ProgramType,@ProgramVersion,@PrgAvgProgress,@PrgWorkCount,@PrgMinWorkTimeID,@PrgMaxWorkTimeID,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE(),@PrjTypeID,@PrjWorkTypeID
|
|
)
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProgramList_GetModel] Script Date: 10/09/2014 14:01:51 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProgramList_GetModel]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetModel]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：得到实体对象的详细信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/29 上午 09:28:52
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_GetModel]
|
|
@OrganizationID varchar(50),
|
|
@ProgramID varchar(50)
|
|
AS
|
|
SELECT
|
|
OrganizationID,ProgramID,ProjectNumber,ModuleID,ProgramCode,ProgramName,ProgramDescription,ProgramType,ProgramVersion
|
|
,ISNULL(PrgAvgProgress,0) AS PrgAvgProgress,PrgWorkCount
|
|
,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMinWorkTimeID),'PrgWorkTyp') AS PrgMinWorkTimeID
|
|
,dbo.OFN_AVA_ArgumentValueByArgumentID(dbo.OFN_AVA_PrgWorkTypeByPrgWorkTimeID(OrganizationID,PrgMaxWorkTimeID),'PrgWorkTyp') AS PrgMaxWorkTimeID
|
|
,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
|
|
FROM [OTB_PRJ_ProgramList]
|
|
WHERE OrganizationID=@OrganizationID and ProgramID=@ProgramID
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProgramList_Update] Script Date: 10/09/2014 14:03:12 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProgramList_Update]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_Update]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：修改一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/29 上午 09:28:52
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProgramList_Update]
|
|
@OrganizationID varchar(50),
|
|
@ProgramID varchar(50),
|
|
@ProjectNumber varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ProgramCode varchar(50),
|
|
@ProgramName nvarchar(500),
|
|
@ProgramDescription nvarchar(500),
|
|
@ProgramType varchar(20),
|
|
@ProgramVersion varchar(10),
|
|
@PrgAvgProgress int,
|
|
@PrgWorkCount int,
|
|
@PrgMinWorkTimeID varchar(50),
|
|
@PrgMaxWorkTimeID varchar(50),
|
|
@Memo text,
|
|
@CreateUser varchar(50),
|
|
@CreateDate datetime,
|
|
@ModifyUser varchar(50),
|
|
@ModifyDate datetime,
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
AS
|
|
UPDATE [OTB_PRJ_ProgramList] SET
|
|
[ProjectNumber] = @ProjectNumber,[ModuleID] = @ModuleID,[ProgramCode] = @ProgramCode,[ProgramName] = @ProgramName,
|
|
[ProgramDescription] = @ProgramDescription,[ProgramType] = @ProgramType,[ProgramVersion] = @ProgramVersion,
|
|
[PrgAvgProgress] = @PrgAvgProgress,[PrgWorkCount] = @PrgWorkCount,[PrgMinWorkTimeID] = @PrgMinWorkTimeID,[PrgMaxWorkTimeID] = @PrgMaxWorkTimeID,
|
|
[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID,PrjWorkTypeID=@PrjWorkTypeID
|
|
WHERE OrganizationID=@OrganizationID and ProgramID=@ProgramID
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_Common_GetWorkTypeList] Script Date: 10/09/2014 14:45:08 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_Common_GetWorkTypeList]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_Common_GetWorkTypeList]
|
|
GO
|
|
|
|
|
|
|
|
|
|
------------------------------------
|
|
--功能：獲取人員名稱列表
|
|
--項目名稱：MidWeb
|
|
--Create：John
|
|
--時間：2014/09/30
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_Common_GetWorkTypeList]
|
|
@PrjTypeID varchar(50),
|
|
@OrganizationID NVARCHAR(50)
|
|
AS
|
|
|
|
SELECT DISTINCT PrjWorkTypeID AS ID, PrjWorkTypeCName as NAME
|
|
FROM OTB_PRJ_PrjWorkType
|
|
WHERE PrjTypeID =@PrjTypeID AND OrganizationID=@OrganizationID
|
|
ORDER BY NAME
|
|
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Modules_ADD] Script Date: 10/09/2014 16:01:15 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Modules_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_ADD]
|
|
GO
|
|
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_ADD]
|
|
@OrganizationID Varchar (50 ),--組織編號
|
|
@ModuleID Varchar (50 ),--模組代碼
|
|
@ModuleCode Varchar (50 ),--模組編號
|
|
@ModuleName NVarchar(100),--模組名稱
|
|
@ProjectNumber Varchar (50 ),--專案編號
|
|
@ParentID Varchar (50 ) ,--上層模組ID
|
|
@ModAvgProgress Int ,--平均程式開發進度
|
|
@ModWorkCount int ,--工作數量
|
|
@ModMinWorkTimeID Varchar (50 ),--程式排班ID
|
|
@ModMaxWorkTimeID Varchar (50 ),--程式排班ID
|
|
@OrderByValue Int ,--排序欄位
|
|
@Memo Text ,--備註
|
|
@CreateUser Varchar (50 ),--建立人員帳號
|
|
@CreateDate DateTime ,--建立日期
|
|
@ModifyUser Varchar (50 ),--修改人員帳號
|
|
@ModifyDate DateTime , --修改日期
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
AS
|
|
INSERT INTO dbo.OTB_PRJ_Modules
|
|
(
|
|
OrganizationID ,
|
|
ModuleID ,
|
|
ModuleCode ,
|
|
ModuleName ,
|
|
ProjectNumber ,
|
|
ParentID ,
|
|
ModAvgProgress ,
|
|
ModWorkCount ,
|
|
ModMinWorkTimeID,
|
|
ModMaxWorkTimeID,
|
|
OrderByValue ,
|
|
Memo ,
|
|
CreateUser ,
|
|
CreateDate ,
|
|
ModifyUser ,
|
|
ModifyDate ,
|
|
PrjTypeID,
|
|
PrjWorkTypeID
|
|
|
|
)
|
|
VALUES ( @OrganizationID ,
|
|
@ModuleID ,
|
|
@ModuleCode ,
|
|
@ModuleName ,
|
|
@ProjectNumber ,
|
|
@ParentID ,
|
|
@ModAvgProgress ,
|
|
@ModWorkCount ,
|
|
@ModMinWorkTimeID,
|
|
@ModMaxWorkTimeID,
|
|
@OrderByValue ,
|
|
@Memo ,
|
|
@CreateUser ,
|
|
GETDATE() ,
|
|
@CreateUser ,
|
|
GETDATE() ,
|
|
@PrjTypeID,
|
|
@PrjWorkTypeID
|
|
|
|
)
|
|
GO
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Modules_GetModel] Script Date: 10/09/2014 16:02:55 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Modules_GetModel]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_GetModel]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：得到实体对象的详细信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/25 下午 04:13:33
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_GetModel]
|
|
@OrganizationID varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ProjectNumber varchar(50)
|
|
AS
|
|
SELECT
|
|
OrganizationID,ModuleID,ModuleCode,ModuleName,ProjectNumber,ParentID,ModAvgProgress,ModWorkCount,ModMinWorkTimeID,ModMaxWorkTimeID,OrderByValue,Memo,
|
|
CreateUser,CreateDate,ModifyUser,ModifyDate,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
|
|
FROM [OTB_PRJ_Modules]
|
|
WHERE OrganizationID=@OrganizationID and ModuleID=@ModuleID AND ProjectNumber=@ProjectNumber
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Modules_Update] Script Date: 10/09/2014 16:04:50 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Modules_Update]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_Update]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：修改一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/25 下午 04:13:33
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Modules_Update]
|
|
@OrganizationID varchar(50),
|
|
@ModuleID varchar(50),
|
|
@ModuleCode varchar(50),
|
|
@ModuleName nvarchar(100),
|
|
@ProjectNumber varchar(50),
|
|
@ParentID varchar(50),
|
|
@ModAvgProgress int,
|
|
@ModWorkCount int,
|
|
@ModMinWorkTimeID varchar(50),
|
|
@ModMaxWorkTimeID varchar(50),
|
|
@OrderByValue int,
|
|
@Memo text,
|
|
@CreateUser varchar(50),
|
|
@CreateDate datetime,
|
|
@ModifyUser varchar(50),
|
|
@ModifyDate datetime,
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
AS
|
|
UPDATE [OTB_PRJ_Modules] SET
|
|
[ModuleCode] = @ModuleCode,[ModuleName] = @ModuleName,[ProjectNumber] = @ProjectNumber,[ParentID] = @ParentID,[ModAvgProgress] = @ModAvgProgress,
|
|
[ModWorkCount] = @ModWorkCount,[ModMinWorkTimeID] = @ModMinWorkTimeID,[ModMaxWorkTimeID] = @ModMaxWorkTimeID,[OrderByValue] = @OrderByValue,
|
|
[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID,PrjWorkTypeID=@PrjWorkTypeID
|
|
WHERE OrganizationID=@OrganizationID and ModuleID=@ModuleID
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_ADD] Script Date: 10/09/2014 16:57:23 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_ADD]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：新增一筆資料
|
|
--項目名稱：
|
|
--創建人： jACK
|
|
--時間：2014/07/22
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_ADD]
|
|
@OrganizationID Varchar (50 ),
|
|
@ProjectNumber Varchar (50 ),--專案編號
|
|
@CustomerID Varchar (50 ),--客戶編號
|
|
@ProjectEName Nvarchar(200 ),--專案名稱
|
|
@ProjectCName Nvarchar(200 ),--專案中文名稱
|
|
@ProjectManager Varchar (50 ),--專案經理
|
|
@ProjectClass Varchar (200 ),--專案類別
|
|
@ProgramLanguage Varchar (200 ),--程式語言
|
|
@ProgramVersion Varchar (10 ),--程式語言版本
|
|
@Database Varchar (200 ),--資料庫應用
|
|
@DatabaseVersion Varchar (10 ),--資料庫版本
|
|
@PStartDate DateTime , --預估開始日期
|
|
@PDeadLine DateTime , --預估截止日期
|
|
@ActualStartDate DateTime , --實際開始日期
|
|
@ActualDeadLine DateTime , --實際截止日期
|
|
@ProjectStatus Varchar (5 ),--專案狀態
|
|
@PrjAvgProgress Int , --平均程式開發進度
|
|
@PrjWorkCount int , --工作數量
|
|
@PrjMinWorkTimeID Varchar (50 ),--程式排班ID
|
|
@PrjMaxWorkTimeID Varchar (50 ),--程式排班ID
|
|
@Notice Nvarchar(2000),-- 注意項目
|
|
@Memo Text , -- 備註
|
|
@CreateUser Varchar (50 ),-- 建立人員帳號
|
|
@CreateDate DateTime , -- 建立日期
|
|
@ModifyUser Varchar (50 ),-- 修改人員帳號
|
|
@ModifyDate DateTime , -- 修改日期
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
AS
|
|
INSERT INTO dbo.OTB_PRJ_ProjectInfo
|
|
(
|
|
OrganizationID ,
|
|
ProjectNumber ,
|
|
CustomerID ,
|
|
ProjectEName ,
|
|
ProjectCName ,
|
|
ProjectManager ,
|
|
ProjectClass ,
|
|
ProgramLanguage ,
|
|
ProgramVersion ,
|
|
[Database] ,
|
|
DatabaseVersion ,
|
|
PStartDate ,
|
|
PDeadLine ,
|
|
ActualStartDate ,
|
|
ActualDeadLine ,
|
|
ProjectStatus ,
|
|
PrjAvgProgress ,
|
|
PrjWorkCount ,
|
|
PrjMinWorkTimeID ,
|
|
PrjMaxWorkTimeID ,
|
|
Notice ,
|
|
Memo ,
|
|
CreateUser ,
|
|
CreateDate ,
|
|
ModifyUser ,
|
|
ModifyDate ,
|
|
PrjTypeID,
|
|
PrjWorkTypeID
|
|
|
|
)
|
|
VALUES (
|
|
@OrganizationID ,
|
|
@ProjectNumber ,
|
|
@CustomerID ,
|
|
@ProjectEName ,
|
|
@ProjectCName ,
|
|
@ProjectManager ,
|
|
@ProjectClass ,
|
|
@ProgramLanguage ,
|
|
@ProgramVersion ,
|
|
@Database ,
|
|
@DatabaseVersion ,
|
|
@PStartDate ,
|
|
@PDeadLine ,
|
|
@ActualStartDate ,
|
|
@ActualDeadLine ,
|
|
@ProjectStatus ,
|
|
@PrjAvgProgress ,
|
|
@PrjWorkCount ,
|
|
@PrjMinWorkTimeID ,
|
|
@PrjMaxWorkTimeID ,
|
|
@Notice ,
|
|
@Memo ,
|
|
@ModifyUser ,
|
|
GETDATE() ,
|
|
@ModifyUser ,
|
|
GETDATE() ,
|
|
@PrjTypeID,
|
|
@PrjWorkTypeID
|
|
|
|
)
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_Delete] Script Date: 10/09/2014 17:04:38 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_Delete]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Delete]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：刪除一筆資料
|
|
--項目名稱：
|
|
--創建人： jACK
|
|
--時間：2014/07/22
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Delete]
|
|
@OrganizationID Varchar (50 ),--專案編號
|
|
@ProjectNumber Varchar (50 )--專案編號
|
|
AS
|
|
Begin TRANSACTION
|
|
|
|
DELETE dbo.OTB_PRJ_ProjectInfo
|
|
WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
|
|
--DELETE dbo.OTB_PRJ_PrjWorkTime
|
|
-- WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
|
|
DELETE dbo.OTB_PRJ_Members
|
|
WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
|
|
DELETE dbo.OTB_PRJ_Customers
|
|
WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
|
|
|
|
IF @@error <> 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
COMMIT TRANSACTION
|
|
END
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetModel] Script Date: 10/09/2014 17:05:13 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetModel]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetModel]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：得到实体对象的详细信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/9 下午 07:05:11
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetModel]
|
|
@ProjectNumber varchar(50),
|
|
@OrganizationID varchar(50)
|
|
AS
|
|
SELECT
|
|
a.OrganizationID ,
|
|
ProjectNumber ,
|
|
a.CustomerID ,
|
|
ProjectEName ,
|
|
ProjectCName ,
|
|
ProjectManager ,
|
|
ProjectClass ,
|
|
ProgramLanguage ,
|
|
ProgramVersion ,
|
|
[Database] ,
|
|
DatabaseVersion ,
|
|
PStartDate ,
|
|
PDeadLine ,
|
|
ActualStartDate ,
|
|
ActualDeadLine ,
|
|
ProjectStatus ,
|
|
PrjAvgProgress ,
|
|
PrjWorkCount ,
|
|
PrjMinWorkTimeID ,
|
|
PrjMaxWorkTimeID ,
|
|
Notice ,
|
|
a.Memo ,
|
|
a.CreateUser ,
|
|
a.CreateDate ,
|
|
a.ModifyUser ,
|
|
a.ModifyDate ,
|
|
b.CustomerName ,PrjTypeID,PrjWorkTypeID,dbo.OFN_PRJ_PrjTypeCNameByPrjTypeID(PrjTypeID) AS PrjTypeText
|
|
FROM dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_CRM_Customers AS b
|
|
ON a.CustomerID = b.CustomerID
|
|
WHERE ProjectNumber=@ProjectNumber AND a.OrganizationID=@OrganizationID
|
|
|
|
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_Update] Script Date: 10/09/2014 17:07:06 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_Update]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Update]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：更新一筆資料
|
|
--項目名稱：
|
|
--創建人： jACK
|
|
--時間：2014/07/22
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_Update]
|
|
@OrganizationID Varchar (50),
|
|
@ProjectNumber Varchar (50),--專案編號
|
|
@CustomerID Varchar (50),--客戶編號
|
|
@ProjectEName Nvarchar(200),--專案名稱
|
|
@ProjectCName Nvarchar(200),--專案中文名稱
|
|
@ProjectManager Varchar (50),--專案經理
|
|
@ProjectClass Varchar (200),--專案類別
|
|
@ProgramLanguage Varchar (200),--程式語言
|
|
@ProgramVersion Varchar (10),--程式語言版本
|
|
@Database Varchar (200),--資料庫應用
|
|
@DatabaseVersion Varchar (10),--資料庫版本
|
|
@PStartDate DateTime , --預估開始日期
|
|
@PDeadLine DateTime , --預估截止日期
|
|
@ActualStartDate DateTime , --實際開始日期
|
|
@ActualDeadLine DateTime , --實際截止日期
|
|
@ProjectStatus Varchar (5),--專案狀態
|
|
@PrjAvgProgress Int , --平均程式開發進度
|
|
@PrjWorkCount int , --工作數量
|
|
@PrjMinWorkTimeID Varchar (50),--程式排班ID
|
|
@PrjMaxWorkTimeID Varchar (50),--程式排班ID
|
|
@Notice Nvarchar(2000),-- 注意項目
|
|
@Memo Text , -- 備註
|
|
@CreateUser Varchar (50),-- 建立人員帳號
|
|
@CreateDate DateTime , -- 建立日期
|
|
@ModifyUser Varchar (50),-- 修改人員帳號
|
|
@ModifyDate DateTime , -- 修改日期
|
|
@PrjTypeID varchar(50),
|
|
@PrjWorkTypeID varchar(max)
|
|
AS
|
|
UPDATE dbo.OTB_PRJ_ProjectInfo
|
|
SET
|
|
CustomerID=@CustomerID ,
|
|
ProjectEName=@ProjectEName ,
|
|
ProjectCName=@ProjectCName ,
|
|
ProjectManager=@ProjectManager ,
|
|
ProjectClass=@ProjectClass ,
|
|
ProgramLanguage=@ProgramLanguage ,
|
|
ProgramVersion=@ProgramVersion ,
|
|
[Database]=@Database ,
|
|
DatabaseVersion=@DatabaseVersion ,
|
|
PStartDate=@PStartDate ,
|
|
PDeadLine=@PDeadLine ,
|
|
ActualStartDate=@ActualStartDate ,
|
|
ActualDeadLine=@ActualDeadLine ,
|
|
ProjectStatus=@ProjectStatus ,
|
|
PrjAvgProgress=@PrjAvgProgress ,
|
|
PrjWorkCount=@PrjWorkCount ,
|
|
PrjMinWorkTimeID=@PrjMinWorkTimeID ,
|
|
PrjMaxWorkTimeID=@PrjMaxWorkTimeID ,
|
|
Notice=@Notice ,
|
|
Memo=@Memo ,
|
|
ModifyUser=@ModifyUser ,
|
|
ModifyDate=GETDATE(),
|
|
PrjTypeID=@PrjTypeID,
|
|
PrjWorkTypeID=@PrjWorkTypeID
|
|
|
|
WHERE
|
|
ProjectNumber=@ProjectNumber AND
|
|
OrganizationID=@OrganizationID
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule] Script Date: 10/09/2014 18:33:30 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule]
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule]
|
|
@OrganizationID VARCHAR(50)
|
|
|
|
AS
|
|
WITH ViewList(PrjRuleID, ParentID, Level, AccountNameSort)
|
|
AS
|
|
(
|
|
SELECT PrjRuleID
|
|
, ParentID
|
|
, 0
|
|
,PrjRuleCName
|
|
|
|
FROM OTB_PRJ_PrjRule WHERE (ParentID IS NULL OR ParentID = '') AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
|
|
SELECT P.PrjRuleID
|
|
, P.ParentID
|
|
, B.Level+1
|
|
,CONVERT(nVARCHAR(20),B.AccountNameSort + '-' + P.PrjRuleCName)
|
|
|
|
FROM OTB_PRJ_PrjRule P, ViewList B
|
|
WHERE P.ParentID=B.PrjRuleID AND OrganizationID=@OrganizationID )
|
|
SELECT PrjRuleID,AccountNameSort AS AccountNameSort
|
|
FROM ViewList ORDER BY AccountNameSort
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Members_ADD] Script Date: 10/10/2014 14:15:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Members_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Members_ADD]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：增加一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/24 下午 03:07:36
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_ADD]
|
|
@OrganizationID varchar(50),
|
|
@ProjectNumber varchar(50),
|
|
@MemberID varchar(50),
|
|
@ProjectRule varchar(max),
|
|
@IsProtoType char(1),
|
|
@IsSA char(1),
|
|
@IsSD char(1),
|
|
@IsPG char(1),
|
|
@IsQC char(1),
|
|
@IsTraining char(1),
|
|
@ReceiveMail char(1),
|
|
@WorkTypeID varchar(max)
|
|
|
|
AS
|
|
INSERT INTO [OTB_PRJ_Members](
|
|
[OrganizationID],[ProjectNumber],[MemberID],[ProjectRule],[IsProtoType],[IsSA],[IsSD],[IsPG],[IsQC],[IsTraining],[ReceiveMail],ProjectWorkType
|
|
)VALUES(
|
|
@OrganizationID,@ProjectNumber,@MemberID,@ProjectRule,@IsProtoType,@IsSA,@IsSD,@IsPG,@IsQC,@IsTraining,@ReceiveMail,@WorkTypeID
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Members_Update] Script Date: 10/10/2014 14:25:29 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Members_Update]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Members_Update]
|
|
GO
|
|
|
|
|
|
|
|
------------------------------------
|
|
--用途：修改一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/24 下午 02:55:03
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_Update]
|
|
@OrganizationID varchar(50),
|
|
@ProjectNumber varchar(50),
|
|
@MemberID varchar(50),
|
|
@ProjectRule varchar(max),
|
|
@IsProtoType char(100),
|
|
@IsSA char(10),
|
|
@IsSD char(20),
|
|
@IsPG char(10),
|
|
@IsQC char(20),
|
|
@IsTraining char(200),
|
|
@ReceiveMail char(50),
|
|
@WorkTypeID varchar(max)
|
|
AS
|
|
DECLARE @temp INT
|
|
SELECT @temp=COUNT(0) FROM dbo.OTB_PRJ_Members WHERE OrganizationID=@OrganizationID AND MemberID=@MemberID
|
|
IF @temp>0
|
|
BEGIN
|
|
UPDATE [OTB_PRJ_Members] SET
|
|
[ProjectRule] = @ProjectRule,[IsProtoType] = @IsProtoType,[IsSA] = @IsSA,[IsSD] = @IsSD,[IsPG] = @IsPG,[IsQC] = @IsQC,[IsTraining] = @IsTraining,[ReceiveMail] = @ReceiveMail,ProjectWorkType=@WorkTypeID
|
|
WHERE OrganizationID=@OrganizationID and ProjectNumber=@ProjectNumber and MemberID=@MemberID
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
INSERT INTO [OTB_PRJ_Members](
|
|
[OrganizationID],[ProjectNumber],[MemberID],[ProjectRule],[IsProtoType],[IsSA],[IsSD],[IsPG],[IsQC],[IsTraining],[ReceiveMail],ProjectWorkType
|
|
)VALUES(
|
|
@OrganizationID,@ProjectNumber,@MemberID,@ProjectRule,@IsProtoType,@IsSA,@IsSD,@IsPG,@IsQC,@IsTraining,@ReceiveMail,@WorkTypeID
|
|
)
|
|
END
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Members_GetList] Script Date: 10/10/2014 14:27:26 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Members_GetList]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Members_GetList]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：查询记录信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/24 下午 03:19:59
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Members_GetList]
|
|
@ProjectNumber varchar(50),
|
|
@OrganizationID varchar(50)
|
|
AS
|
|
SELECT
|
|
OrganizationID,ProjectNumber,MemberID,ProjectRule,[dbo].[FN_PRJ_GetPRJRuleValuebyRuleID](ProjectRule) AS ProjectRuleText,IsProtoType,IsSA,IsSD,IsPG,IsQC,IsTraining,ReceiveMail,'' AS [Status],ProjectWorkType,[dbo].[FN_PRJ_GetValuebyID](ProjectWorkType) AS WorkTypeText
|
|
FROM [OTB_PRJ_Members] WHERE OrganizationID=@OrganizationID AND ProjectNumber=@ProjectNumber
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|