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.
1237 lines
81 KiB
1237 lines
81 KiB
|
|
|
|
|
|
alter table OTB_PRJ_PrjRule add IsLeader char(1) DEFAULT 'N'
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetUserByRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetUserByRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetUserByRuleID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetUserNameByRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetUserNameByRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetUserNameByRuleID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_FAQDetailCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_FAQDetailCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_MNG_FAQDetailCount]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_FAQReadCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_FAQReadCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_MNG_FAQReadCount]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_GetHotProgramCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_GetHotProgramCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_MNG_GetHotProgramCount]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetArgumentValueByArgumentID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetArgumentValueByArgumentID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetArgumentValueByArgumentID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetContacterNameByContacterID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetContacterNameByContacterID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetContacterNameByContacterID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetContacterNameByCustomerID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetContacterNameByCustomerID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetContacterNameByCustomerID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetMemberNameByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetMemberNameByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetMemberNameByMemberID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetProgramListNameByProgramListID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetProgramListNameByProgramListID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetProgramListNameByProgramListID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetValuebyID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetValuebyID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_GetValuebyID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
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
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetDepartByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetDepartByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetDepartByMemberID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetDPTMemberIDByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetDPTMemberIDByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetDPTMemberIDByMemberID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetFilePathByGuid] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetFilePathByGuid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetFilePathByGuid]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetProgramCountByModuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetProgramCountByModuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetProgramCountByModuleID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ArgumentValueByArgumentID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ArgumentValueByArgumentID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_ArgumentValueByArgumentID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ContacterNameByContacterID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ContacterNameByContacterID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_ContacterNameByContacterID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_CustomerNameByCustomerID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_CustomerNameByCustomerID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_CustomerNameByCustomerID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_GetChiefIDBydepartID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_GetChiefIDBydepartID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_GetChiefIDBydepartID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_MemberNameByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_MemberNameByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_MemberNameByMemberID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ModuleNameByModuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ModuleNameByModuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_ModuleNameByModuleID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ProgramNameByProgramID] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ProgramNameByProgramID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_ProgramNameByProgramID]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ProjectNameByProjectNumber] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ProjectNameByProjectNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_ProjectNameByProjectNumber]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_RemoveStringduplicate] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_RemoveStringduplicate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_AVA_RemoveStringduplicate]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetStrArrayStrOfIndex] Script Date: 10/17/2014 16:35:01 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetStrArrayStrOfIndex]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
DROP FUNCTION [dbo].[OFN_SYS_GetStrArrayStrOfIndex]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetStrArrayStrOfIndex] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetStrArrayStrOfIndex]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'------------------------------------
|
|
--功能：資料
|
|
--項目名稱：OPMSDB
|
|
--Create：Jack
|
|
--時間：2014/07/29
|
|
--exec
|
|
------------------------------------
|
|
|
|
CREATE function [dbo].[OFN_SYS_GetStrArrayStrOfIndex]
|
|
(
|
|
@str varchar(1024), --要分割的字符串
|
|
@split varchar(10) --分隔符号
|
|
--,@index int --取第几个元素
|
|
)
|
|
returns varchar(1024)
|
|
as
|
|
begin
|
|
declare @location int
|
|
declare @start int
|
|
declare @next int
|
|
declare @seed int
|
|
|
|
set @str=ltrim(rtrim(@str))
|
|
set @start=1
|
|
set @next=1
|
|
set @seed=len(@split)
|
|
|
|
set @location=charindex(@split,@str)
|
|
while @location<>0 --and @index>@next
|
|
begin
|
|
set @start=@location+@seed
|
|
set @location=charindex(@split,@str,@start)
|
|
set @next=@next+1
|
|
end
|
|
if @location =0 select @location =len(@str)+1
|
|
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
|
|
|
|
return substring(@str,@start,@location-@start)
|
|
end
|
|
--select dbo.Get_StrArrayStrOfIndex(''8,9,4'','','',2)'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_RemoveStringduplicate] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_RemoveStringduplicate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
/*
|
|
------------------------------------
|
|
--用途：得到實體對象的詳細信息
|
|
--項目名稱：
|
|
--Create：Jack
|
|
--時間：2014/07/25
|
|
------------------------------------
|
|
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)
|
|
|
|
set @str1 = ''1,2,3''
|
|
set @str2 = ''1###2###3''
|
|
set @str3 = ''1###2###3###1###2###3###''
|
|
|
|
select [dbo].[OFN_AVA_RemoveStringduplicate](@str1, '','', 1)
|
|
select [dbo].[OFN_AVA_RemoveStringduplicate](@str2, ''###'', 1)
|
|
select [dbo].[OFN_AVA_RemoveStringduplicate](@str3, ''###'', 1)
|
|
*/
|
|
|
|
CREATE function [dbo].[OFN_AVA_RemoveStringduplicate]
|
|
(
|
|
@Input nvarchar(max),
|
|
@Separator nvarchar(max)='','',
|
|
@RemoveEmptyEntries bit=1
|
|
)
|
|
returns nvarchar(max)
|
|
|
|
|
|
as
|
|
begin
|
|
DECLARE @TABLE TABLE
|
|
(
|
|
[Id] int identity(1,1),
|
|
[Value] nvarchar(max)
|
|
)
|
|
declare @Index int, @Entry nvarchar(max),@return nvarchar(max)
|
|
|
|
set @Index = charindex(@Separator,@Input)
|
|
|
|
while (@Index>0)
|
|
begin
|
|
set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
|
|
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'''')
|
|
begin
|
|
insert into @TABLE([Value]) Values(@Entry)
|
|
END
|
|
|
|
|
|
|
|
|
|
set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
|
|
set @Index = charindex(@Separator, @Input)
|
|
end
|
|
|
|
set @Entry=ltrim(rtrim(@Input))
|
|
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'''')
|
|
begin
|
|
insert into @TABLE([Value]) Values(@Entry)
|
|
end
|
|
SET @return=(SELECT DISTINCT [Value]+''|'' FROM @TABLE FOR XML PATH(''''))
|
|
RETURN @return
|
|
end'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ProjectNameByProjectNumber] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ProjectNameByProjectNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
--根據專案編號獲取專案名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_ProjectNameByProjectNumber]
|
|
(
|
|
@ProjectNumber varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ISNULL(ProjectCName,ProjectEName)
|
|
FROM OTB_PRJ_ProjectInfo
|
|
WHERE ProjectNumber=@ProjectNumber
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ProgramNameByProgramID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ProgramNameByProgramID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據專案程式編號獲取專案程式名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_ProgramNameByProgramID]
|
|
(
|
|
@ProgramID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ProgramCode
|
|
FROM OTB_PRJ_ProgramList
|
|
WHERE ProgramID=@ProgramID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
--根據工作項目ID號獲取工作項目
|
|
CREATE FUNCTION [dbo].[OFN_AVA_PrgWorkTypeByPrgWorkTimeID]
|
|
(
|
|
@OrganizationID varchar(50), --傳入的字符串
|
|
@PrgWorkTimeID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = PrgWorkType
|
|
FROM OTB_PRJ_PrgWorkTime
|
|
WHERE PrgWorkTimeID=@PrgWorkTimeID AND OrganizationID=@OrganizationID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ModuleNameByModuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ModuleNameByModuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
--根據專案模組編號獲取模組名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_ModuleNameByModuleID]
|
|
(
|
|
@ModuleID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ModuleName
|
|
FROM OTB_PRJ_Modules
|
|
WHERE ModuleID=@ModuleID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_MemberNameByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_MemberNameByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'--根據參數編號獲取參數名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_MemberNameByMemberID]
|
|
(
|
|
@MemberID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = MemberName
|
|
FROM OTB_SYS_Members
|
|
WHERE MemberID=@MemberID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_GetChiefIDBydepartID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_GetChiefIDBydepartID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
--根據部門獲取部門主管id
|
|
CREATE FUNCTION [dbo].[OFN_AVA_GetChiefIDBydepartID]
|
|
(
|
|
@DepartmentID varchar(50), --傳入的字符串
|
|
@OrganizationID VarChar(50) --組織編號
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
|
|
SELECT @Value=MemberID FROM dbo.OTB_SYS_Members WHERE DepartmentID=@DepartmentID AND JobTitle=(SELECT SettingValue FROM dbo.OTB_SYS_SystemSetting WHERE OrganizationID=@OrganizationID AND SettingItem=''manager'')
|
|
RETURN @Value
|
|
END
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_CustomerNameByCustomerID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_CustomerNameByCustomerID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
--根據客戶代號獲取客戶名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_CustomerNameByCustomerID]
|
|
(
|
|
@CustomerID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value =CustomerName
|
|
FROM OTB_CRM_Customers
|
|
WHERE CustomerID=@CustomerID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ContacterNameByContacterID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ContacterNameByContacterID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據聯絡人代號獲取聯絡人名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_ContacterNameByContacterID]
|
|
(
|
|
@ContacterID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value =ContacterName
|
|
FROM OTB_CRM_Contacters
|
|
WHERE ContacterID=@ContacterID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_AVA_ArgumentValueByArgumentID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_AVA_ArgumentValueByArgumentID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'--根據參數編號獲取參數名稱
|
|
CREATE FUNCTION [dbo].[OFN_AVA_ArgumentValueByArgumentID]
|
|
(
|
|
@ArgumentID varchar(50), --傳入的字符串
|
|
@ArgumentClassID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ArgumentValue
|
|
FROM OTB_SYS_Arguments
|
|
WHERE ArgumentID=@ArgumentID AND ArgumentClassID=@ArgumentClassID
|
|
-- IF @Value=''''
|
|
--SET @Value=''''
|
|
--ELSE
|
|
--SET @Value=@Value
|
|
RETURN @Value
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetProgramCountByModuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetProgramCountByModuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_SYS_GetProgramCountByModuleID]
|
|
(@ModuleID VARCHAR(10)
|
|
,@OrganizationID varchar(50)
|
|
)
|
|
RETURNS INT AS
|
|
BEGIN
|
|
DECLARE @strReturn INT
|
|
SET @strReturn ='''';
|
|
WITH ViewList(ModuleID, ParentID, LEVEL, OrganizationID)
|
|
AS
|
|
(
|
|
SELECT ModuleID, ParentID, 0,OrganizationID
|
|
FROM [OTB_SYS_ModuleList] WHERE (ModuleID = @ModuleID AND OrganizationID=@OrganizationID)
|
|
|
|
UNION ALL
|
|
|
|
SELECT P.ModuleID, P.ParentID, B.Level+1,B.OrganizationID
|
|
FROM [OTB_SYS_ModuleList] P, ViewList B
|
|
WHERE P.ParentID=B.ModuleID AND P.OrganizationID=@OrganizationID
|
|
)
|
|
SELECT @strReturn=COUNT(0)
|
|
FROM ViewList AS VL
|
|
INNER JOIN OTB_SYS_ProgramList AS SPL ON VL.ModuleID = SPL.ModuleID
|
|
AND VL.OrganizationID = SPL.OrganizationID
|
|
|
|
RETURN @strReturn
|
|
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetFilePathByGuid] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetFilePathByGuid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'--SELECT dbo.FN_SYS_GetFilePathByGuid(''016692de-362d-4427-87ca-37754c13d613'')
|
|
CREATE FUNCTION [dbo].[OFN_SYS_GetFilePathByGuid]
|
|
(@TargetRelationID VARCHAR(36))
|
|
|
|
RETURNS NVARCHAR(200) AS
|
|
BEGIN
|
|
|
|
DECLARE @strReturn NVARCHAR(200)
|
|
SET @strReturn = ''''
|
|
|
|
SELECT @strReturn=FilePath
|
|
FROM OTB_SYS_Attachments
|
|
WHERE TargetRelationID=@TargetRelationID
|
|
|
|
RETURN @strReturn
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetDPTMemberIDByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetDPTMemberIDByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_SYS_GetDPTMemberIDByMemberID]
|
|
(
|
|
@MemberID varchar(50),
|
|
@OrganizationID VARCHAR(50)
|
|
)
|
|
RETURNS @TempTable TABLE(MemberID VARCHAR(50))
|
|
AS
|
|
BEGIN
|
|
DECLARE @jobTitle VARCHAR(50);
|
|
DECLARE @DepartmentID NVARCHAR(50);
|
|
SELECT @jobTitle=JobTitle FROM dbo.OTB_SYS_Members WHERE MemberID=@MemberID AND OrganizationID=@OrganizationID
|
|
SELECT @DepartmentID=DepartmentID FROM dbo.OTB_SYS_Members WHERE MemberID=@MemberID AND OrganizationID=@OrganizationID
|
|
|
|
IF @jobTitle!=(SELECT SettingValue FROM dbo.OTB_SYS_SystemSetting WHERE OrganizationID=@OrganizationID AND SettingItem=''manager'' AND Effective=''Y'')
|
|
BEGIN
|
|
INSERT INTO @TempTable
|
|
( MemberID )
|
|
VALUES ( @MemberID -- DepartID - varchar(50)
|
|
)
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
;WITH ViewList(DepartmentID, ParentDepartmentID, Level, AccountNameSort,DelStatus)
|
|
AS
|
|
(SELECT DepartmentID
|
|
, ParentDepartmentID
|
|
, 0
|
|
,DepartmentName
|
|
,DelStatus
|
|
|
|
FROM OTB_SYS_Departments WHERE (DepartmentID =@DepartmentID) AND DelStatus = ''N'' AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
SELECT DepartmentID
|
|
, ParentDepartmentID
|
|
, 0
|
|
,DepartmentName
|
|
,DelStatus
|
|
|
|
FROM OTB_SYS_Departments WHERE (ParentDepartmentID =@DepartmentID) AND DelStatus = ''N'' AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
|
|
SELECT P.DepartmentID
|
|
, P.ParentDepartmentID
|
|
, B.Level+1
|
|
,CONVERT(nVARCHAR(200),B.AccountNameSort + ''-'' + P.DepartmentName)
|
|
,P.DelStatus
|
|
FROM OTB_SYS_Departments P, ViewList B
|
|
WHERE P.ParentDepartmentID=B.DepartmentID AND B.DelStatus = ''N'' AND P.DelStatus = ''N'' AND OrganizationID=@OrganizationID AND p.ParentDepartmentID!=@DepartmentID
|
|
)
|
|
|
|
INSERT INTO @TempTable
|
|
( MemberID )
|
|
SELECT MemberID
|
|
FROM ViewList AS dep
|
|
INNER JOIN dbo.OTB_SYS_Members AS usr ON dep.DepartmentID = usr.DepartmentID
|
|
|
|
RETURN
|
|
|
|
END
|
|
RETURN
|
|
END '
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetDepartByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetDepartByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_SYS_GetDepartByMemberID]
|
|
(
|
|
@DepartmentID varchar(50),
|
|
@OrganizationID VARCHAR(50)
|
|
)
|
|
--RETURNS @TempTable TABLE(DepartID VARCHAR(50))
|
|
RETURNS NVARCHAR(max)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @DepartmentIDList NVARCHAR(max);
|
|
--SELECT @DepartmentID=DepartmentID FROM dbo.OTB_SYS_Members WHERE MemberID=@MemberID AND OrganizationID=@OrganizationID
|
|
WITH ViewList(DepartmentID, ParentDepartmentID, Level, AccountNameSort,DelStatus)
|
|
AS
|
|
(
|
|
SELECT DepartmentID
|
|
, ParentDepartmentID
|
|
, 0
|
|
,DepartmentName
|
|
,DelStatus
|
|
|
|
FROM OTB_SYS_Departments WHERE (ParentDepartmentID IS NULL OR ParentDepartmentID = '''') AND DelStatus = ''N'' AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
|
|
SELECT P.DepartmentID
|
|
, P.ParentDepartmentID
|
|
, B.Level+1
|
|
,CONVERT(nVARCHAR(200),B.AccountNameSort + ''-'' + P.DepartmentName)
|
|
-- ,REPLACE(REPLACE(B.AccountNameSort + ''-'' + CONVERT(nvarchar(128),P.DepartmentName),N''┣收入-'',''''),N''┣費用-'',''''))
|
|
,P.DelStatus
|
|
|
|
FROM OTB_SYS_Departments P, ViewList B
|
|
WHERE P.ParentDepartmentID=B.DepartmentID AND B.DelStatus = ''N'' AND P.DelStatus = ''N'' AND OrganizationID=@OrganizationID AND p.ParentDepartmentID!=@DepartmentID)
|
|
|
|
SELECT @DepartmentIDList=DepartmentID+'';'' FROM ViewList FOR XML PATH('''')
|
|
RETURN @DepartmentIDList
|
|
|
|
end '
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
CREATE FUNCTION [dbo].[OFN_SYS_GetChilDepartmentIdByDepartmentId]
|
|
(
|
|
@OrganizationID varchar(50),
|
|
@Department_ID nvarchar(max)
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
|
|
WITH ViewList(DepartmentID, ParentID, Level)
|
|
AS
|
|
(
|
|
SELECT DepartmentID,ParentDepartmentID, 0
|
|
FROM OTB_SYS_Departments WHERE DepartmentID=@Department_ID AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
SELECT DepartmentID, ParentDepartmentID, 0
|
|
FROM OTB_SYS_Departments WHERE ParentDepartmentID=@Department_ID AND OrganizationID=@OrganizationID
|
|
|
|
UNION ALL
|
|
|
|
SELECT P.DepartmentID, P.ParentDepartmentID, B.Level+1
|
|
FROM OTB_SYS_Departments P, ViewList B
|
|
WHERE P.ParentDepartmentID=B.DepartmentID AND OrganizationID=@OrganizationID
|
|
)
|
|
|
|
SELECT DISTINCT DepartmentID
|
|
FROM ViewList
|
|
)
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_PrjWorkTypeCNameByPrjWorkTypeID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
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
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_PrjTypeCNameByPrjTypeID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT 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'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
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
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetValuebyID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetValuebyID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
CREATE function [dbo].[OFN_PRJ_GetValuebyID]
|
|
(
|
|
@ID nvarchar(max)
|
|
)
|
|
RETURNS NVARCHAR(max)
|
|
BEGIN
|
|
|
|
DECLARE @RETURN NVARCHAR(max) --傳回值
|
|
SET @RETURN=''''
|
|
|
|
BEGIN
|
|
|
|
SELECT @RETURN=@RETURN+ PrjWorkTypeCName+'',''
|
|
FROM OTB_PRJ_PrjWorkType AS A
|
|
INNER JOIN (
|
|
select n.r.value(''.'', ''varchar(50)'') as col
|
|
from (select cast(''<r>''+replace(@ID, '','', ''</r><r>'')+''</r>'' as xml ))
|
|
as s(XMLCol) cross apply s.XMLCol.nodes(''r'') as n(r)
|
|
WHERE n.r.value(''.'', ''varchar(50)'')<>'''' AND n.r.value(''.'', ''varchar(50)'') is NOT NULL
|
|
|
|
) AS B ON A.PrjWorkTypeID=B.col
|
|
--select @RETURN=@RETURN+''*''+@ID
|
|
END
|
|
|
|
|
|
RETURN @RETURN
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetProgramListNameByProgramListID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetProgramListNameByProgramListID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
|
|
|
|
|
|
CREATE function [dbo].[OFN_PRJ_GetProgramListNameByProgramListID]
|
|
(
|
|
@ID nvarchar(max),
|
|
@OrganizationID varchar(50)
|
|
)
|
|
RETURNS NVARCHAR(max)
|
|
BEGIN
|
|
|
|
DECLARE @RETURN NVARCHAR(max) --傳回值
|
|
SET @RETURN=''''
|
|
|
|
BEGIN
|
|
|
|
SELECT @RETURN=@RETURN+ ProgramCode+''-''+ProgramName+'',''
|
|
FROM OTB_PRJ_ProgramList AS A
|
|
INNER JOIN (
|
|
select n.r.value(''.'', ''varchar(50)'') as col
|
|
from (select cast(''<r>''+replace(@ID, '','', ''</r><r>'')+''</r>'' as xml ))
|
|
as s(XMLCol) cross apply s.XMLCol.nodes(''r'') as n(r)
|
|
WHERE n.r.value(''.'', ''varchar(50)'')<>'''' AND n.r.value(''.'', ''varchar(50)'') is NOT NULL
|
|
|
|
) AS B ON A.ProgramID=B.col
|
|
END
|
|
|
|
|
|
RETURN @RETURN
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
|
|
CREATE function [dbo].[OFN_PRJ_GetPRJRuleValuebyRuleID]
|
|
(
|
|
@ID nvarchar(max)
|
|
)
|
|
RETURNS NVARCHAR(max)
|
|
BEGIN
|
|
|
|
DECLARE @RETURN NVARCHAR(max) --傳回值
|
|
SET @RETURN=''''
|
|
|
|
BEGIN
|
|
|
|
SELECT @RETURN=@RETURN+PrjRuleCName+'',''
|
|
FROM OTB_PRJ_PrjRule AS A
|
|
INNER JOIN (
|
|
select n.r.value(''.'', ''varchar(50)'') as col
|
|
from (select cast(''<r>''+replace(@ID, '','', ''</r><r>'')+''</r>'' as xml ))
|
|
as s(XMLCol) cross apply s.XMLCol.nodes(''r'') as n(r)
|
|
WHERE n.r.value(''.'', ''varchar(50)'')<>'''' AND n.r.value(''.'', ''varchar(50)'') is NOT NULL
|
|
|
|
) AS B ON A.PrjRuleID=B.col
|
|
--select @RETURN=@RETURN+''*''+@ID
|
|
END
|
|
|
|
|
|
RETURN @RETURN
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetMemberNameByMemberID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetMemberNameByMemberID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
CREATE FUNCTION [dbo].[OFN_PRJ_GetMemberNameByMemberID]
|
|
(
|
|
@MemberID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = MemberName
|
|
FROM OTB_SYS_Members
|
|
WHERE MemberID=@MemberID
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetContacterNameByCustomerID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetContacterNameByCustomerID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
CREATE FUNCTION [dbo].[OFN_PRJ_GetContacterNameByCustomerID]
|
|
(
|
|
@ContacterID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ContacterName
|
|
FROM OTB_CRM_Contacters
|
|
WHERE ContacterID=@ContacterID
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetContacterNameByContacterID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetContacterNameByContacterID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
--根據專案模組編號獲取模組名稱
|
|
CREATE FUNCTION [dbo].[OFN_PRJ_GetContacterNameByContacterID]
|
|
(
|
|
@ContacterID varchar(50) --傳入的字符串
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Value nvarchar(max);
|
|
SET @Value=''''
|
|
SELECT @Value = ContacterName
|
|
FROM OTB_CRM_Contacters
|
|
WHERE ContacterID=@ContacterID
|
|
RETURN @Value
|
|
END
|
|
|
|
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_PRJ_GetArgumentValueByArgumentID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_PRJ_GetArgumentValueByArgumentID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'
|
|
|
|
|
|
|
|
|
|
CREATE function [dbo].[OFN_PRJ_GetArgumentValueByArgumentID]
|
|
(
|
|
@ID nvarchar(max),
|
|
@ArgumentClassID varchar(50),
|
|
@OrganizationID varchar(50)
|
|
)
|
|
RETURNS NVARCHAR(max)
|
|
BEGIN
|
|
|
|
DECLARE @RETURN NVARCHAR(max) --傳回值
|
|
SET @RETURN=''''
|
|
|
|
BEGIN
|
|
|
|
SELECT @RETURN=@RETURN+ArgumentValue+'',''
|
|
FROM (SELECT * FROM OTB_SYS_Arguments WHERE OrganizationID=@OrganizationID AND ArgumentClassID=@ArgumentClassID) AS A
|
|
INNER JOIN (
|
|
select n.r.value(''.'', ''varchar(50)'') as col
|
|
from (select cast(''<r>''+replace(@ID, '','', ''</r><r>'')+''</r>'' as xml ))
|
|
as s(XMLCol) cross apply s.XMLCol.nodes(''r'') as n(r)
|
|
WHERE n.r.value(''.'', ''varchar(50)'')<>'''' AND n.r.value(''.'', ''varchar(50)'') is NOT NULL
|
|
|
|
) AS B ON A.ArgumentID=B.col
|
|
END
|
|
|
|
|
|
RETURN @RETURN
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_GetHotProgramCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_GetHotProgramCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_MNG_GetHotProgramCount]
|
|
(
|
|
@OrganizationID varchar(50)
|
|
,@MemberID VARCHAR(50)
|
|
,@AddType char(1)
|
|
)
|
|
RETURNS INT AS
|
|
BEGIN
|
|
DECLARE @strReturn INT
|
|
|
|
SELECT @strReturn = COUNT(*) FROM OTB_MNG_HotProgram
|
|
WHERE OrganizationID = @OrganizationID
|
|
AND MemberID = @MemberID
|
|
AND AddType = @AddType
|
|
|
|
RETURN @strReturn
|
|
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_FAQReadCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_FAQReadCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_MNG_FAQReadCount]
|
|
(
|
|
@OrganizationID varchar(50)
|
|
,@FAQHeadID char(36)
|
|
)
|
|
RETURNS INT AS
|
|
BEGIN
|
|
DECLARE @strReturn INT
|
|
|
|
SELECT @strReturn = COUNT(*) FROM OTB_MNG_FAQReadHistory
|
|
WHERE OrganizationID = @OrganizationID
|
|
AND FAQHeadID = @FAQHeadID
|
|
|
|
|
|
RETURN @strReturn
|
|
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_MNG_FAQDetailCount] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_MNG_FAQDetailCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[OFN_MNG_FAQDetailCount]
|
|
(
|
|
@OrganizationID varchar(50)
|
|
,@FAQHeadID char(36)
|
|
)
|
|
RETURNS INT AS
|
|
BEGIN
|
|
DECLARE @strReturn INT
|
|
|
|
SELECT @strReturn = COUNT(*) FROM OTB_MNG_FAQDetail
|
|
WHERE OrganizationID = @OrganizationID
|
|
AND FAQHeadID = @FAQHeadID
|
|
|
|
|
|
RETURN @strReturn
|
|
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetUserNameByRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetUserNameByRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'--Create By Ted
|
|
--時間 2014/05/20
|
|
--SELECT dbo.[FN_SYS_GetUserNameByRuleID](''Admin'')
|
|
--根據角色ID查詢該角色下的所有使用者
|
|
CREATE FUNCTION [dbo].[OFN_SYS_GetUserNameByRuleID]
|
|
(
|
|
@RuleID varchar(20) --傳入的字符串
|
|
, @OrganizationID varchar(50)
|
|
)
|
|
RETURNS NVARCHAR(4000)
|
|
AS
|
|
BEGIN
|
|
DECLARE @str1 varchar(max)
|
|
SET @str1=''''
|
|
SELECT @str1 = @str1+ TBB.MemberName +''|''
|
|
FROM dbo.[OTB_SYS_Rules] AS SR
|
|
LEFT JOIN [OTB_SYS_MembersToRule] AS TSM ON SR.RuleID = TSM.RuleID AND SR.OrganizationID = TSM.OrganizationID
|
|
LEFT JOIN OTB_SYS_Members AS TBB ON TSM.MemberID=TBB.MemberID AND TSM.OrganizationID = TBB.OrganizationID
|
|
WHERE SR.RuleID=@RuleID AND SR.OrganizationID=@OrganizationID
|
|
|
|
IF @str1=''''
|
|
SET @str1=''''
|
|
ELSE
|
|
SET @str1=@str1+'',''
|
|
RETURN @str1
|
|
END
|
|
'
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[OFN_SYS_GetUserByRuleID] Script Date: 10/17/2014 16:35:01 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OFN_SYS_GetUserByRuleID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
|
|
BEGIN
|
|
execute dbo.sp_executesql @statement = N'--Create By Alina
|
|
--時間 2014/08/02
|
|
--SELECT dbo.[FN_SYS_GetUserByRuleID](''Admin'',''AA'')
|
|
--根據角色ID查詢該角色下的所有使用者
|
|
CREATE FUNCTION [dbo].[OFN_SYS_GetUserByRuleID]
|
|
(
|
|
@RuleID varchar(20) --傳入的字符串
|
|
,@OrganizationID varchar(50)
|
|
)
|
|
RETURNS NVARCHAR(max)
|
|
AS
|
|
BEGIN
|
|
DECLARE @str1 NVARCHAR(max)
|
|
SET @str1=''''
|
|
SELECT @str1 = @str1+ Memberid +''|''
|
|
FROM dbo.[OTB_SYS_Rules] AS SR
|
|
LEFT JOIN [OTB_SYS_MembersToRule] AS TSM ON SR.RuleID = TSM.RuleID AND SR.OrganizationID = TSM.OrganizationID
|
|
WHERE SR.RuleID=@RuleID AND SR.OrganizationID=@OrganizationID
|
|
|
|
IF @str1=''''
|
|
SET @str1=''''
|
|
ELSE
|
|
SET @str1=@str1+'',''
|
|
RETURN @str1
|
|
END
|
|
'
|
|
END
|
|
GO
|