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.
381 lines
24 KiB
381 lines
24 KiB
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Customers_Update] Script Date: 10/13/2014 19:20:43 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Customers_Update]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_Customers_Update]
|
|
GO
|
|
|
|
------------------------------------
|
|
--用途：修改一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/24 下午 02:57:54
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_Customers_Update]
|
|
@OrganizationID varchar(50),
|
|
@ProjectNumber varchar(50),
|
|
@CustomerID varchar(50),
|
|
@ReceiveMail char(50),
|
|
@Memo text,
|
|
@CreateUser varchar(50),
|
|
@CreateDate datetime,
|
|
@ModifyUser varchar(50),
|
|
@ModifyDate datetime
|
|
AS
|
|
DECLARE @temp INT
|
|
SELECT @temp=COUNT(0) FROM dbo.[OTB_PRJ_Customers] WHERE OrganizationID=@OrganizationID AND CustomerID=@CustomerID AND ProjectNumber=@ProjectNumber
|
|
IF @temp>0
|
|
BEGIN
|
|
UPDATE [OTB_PRJ_Customers] SET
|
|
[ReceiveMail] = @ReceiveMail,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
|
|
WHERE OrganizationID=@OrganizationID and ProjectNumber=@ProjectNumber and CustomerID=@CustomerID
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO [OTB_PRJ_Customers](
|
|
[OrganizationID],[ProjectNumber],[CustomerID],[ReceiveMail],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
|
|
)VALUES(
|
|
@OrganizationID,@ProjectNumber,@CustomerID,@ReceiveMail,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE()
|
|
)
|
|
END
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Members_Update] Script Date: 10/13/2014 19:21:21 ******/
|
|
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 AND ProjectNumber=@ProjectNumber
|
|
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_ProjectInfo_GetContactersbyCustomerID] Script Date: 10/14/2014 09:22:46 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetContactersbyCustomerID]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetContactersbyCustomerID]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--功能：查詢專案客戶成員
|
|
--Create：John
|
|
--時間：2014/07/15
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetContactersbyCustomerID]
|
|
@OrganizationID varchar(50),
|
|
@CustomerID nvarchar(100)
|
|
AS
|
|
|
|
SELECT ContacterID, ContacterName
|
|
FROM OTB_CRM_Contacters
|
|
WHERE CustomerID=@CustomerID AND OrganizationID=@OrganizationID
|
|
|
|
|
|
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetCustomerAndMemberData] Script Date: 10/13/2014 16:24:54 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetCustomerAndMemberData]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetCustomerAndMemberData]
|
|
GO
|
|
|
|
------------------------------------
|
|
--功能：查詢專案客戶成員
|
|
--Create：John
|
|
--時間：2014/07/15
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetCustomerAndMemberData]
|
|
@OrganizationID varchar(50),
|
|
@ProjectNumber nvarchar(100)
|
|
AS
|
|
SELECT PRJ.CustomerID+';'+CRM.Email AS CustomerID ,dbo.OFN_PRJ_GetContacterNameByContacterID(PRJ.CustomerID) AS CustomerName
|
|
FROM OTB_PRJ_Customers AS PRJ
|
|
INNER JOIN dbo.OTB_CRM_Contacters AS CRM ON PRJ.CustomerID = CRM.ContacterID
|
|
WHERE ProjectNumber=@ProjectNumber AND PRJ.OrganizationID=@OrganizationID
|
|
|
|
SELECT MemberID, dbo.OFN_PRJ_GetMemberNameByMemberID(MemberID) AS MemberName
|
|
FROM OTB_PRJ_Members
|
|
WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
|
|
|
|
SELECT ModuleID, dbo.OFN_AVA_ModuleNameByModuleID(ModuleID) AS ModuleName
|
|
FROM OTB_PRJ_Modules
|
|
WHERE ProjectNumber=@ProjectNumber AND OrganizationID=@OrganizationID
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD] Script Date: 10/14/2014 09:50:46 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：增加一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/29 上午 08:38:59
|
|
------------------------------------
|
|
CREATE 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(),@CreateUser,GETDATE(),@PrjTypeID
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_ADD] Script Date: 10/14/2014 09:51:25 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [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(),@CreateUser,GETDATE()
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_ADD] Script Date: 10/14/2014 09:52:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_ADD]
|
|
GO
|
|
|
|
------------------------------------
|
|
--用途：增加一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/29 上午 08:37:33
|
|
------------------------------------
|
|
CREATE 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(),@CreateUser,GETDATE(),@PrjTypeID
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_Task_ADD] Script Date: 10/14/2014 09:53:18 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_Task_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [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(),@CreateUser,GETDATE()
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_SCM_Skill_ADD] Script Date: 10/14/2014 09:53:52 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SCM_Skill_ADD]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[OSP_OTB_SCM_Skill_ADD]
|
|
GO
|
|
|
|
|
|
------------------------------------
|
|
--用途：增加一条记录
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/9/25 下午 04:31:38
|
|
------------------------------------
|
|
CREATE PROCEDURE [dbo].[OSP_OTB_SCM_Skill_ADD]
|
|
@OrganizationID varchar(50),
|
|
@SkillId varchar(36),
|
|
@SkillEName varchar(50),
|
|
@SkillCName varchar(50),
|
|
@ParentSid varchar(36),
|
|
@Memo ntext,
|
|
@CreateUser varchar(50),
|
|
@CreateDate datetime,
|
|
@ModifyUser varchar(50),
|
|
@ModifyDate datetime
|
|
|
|
AS
|
|
INSERT INTO [OTB_SCM_Skill](
|
|
[OrganizationID],[SkillId],[SkillEName],[SkillCName],[ParentSid],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
|
|
)VALUES(
|
|
@OrganizationID,NEWID(),@SkillEName,@SkillCName,@ParentSid,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE()
|
|
)
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetProjectRule] Script Date: 10/14/2014 13:46:00 ******/
|
|
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]
|
|
@PrjtTypeID VARCHAR(50),
|
|
@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
|
|
|
|
SELECT PrjRuleID,PrjRuleCName AS AccountNameSort FROM OTB_PRJ_PrjRule WHERE OrganizationID=@OrganizationID AND PrjTypeID=@PrjtTypeID
|
|
|
|
|
|
GO
|
|
|
|
|